[Dbix-class] Issues with postgres timestamp in a primary key

[Todd Rinaldo]

On Tue, Jul 22, 2008 at 10:23 PM, Matt S Trout <dbix-class at trout.me.uk> wrote:
> Note: Don't top post. Don't send with windows line endings.
>
> On Tue, Jul 22, 2008 at 03:00:18AM -0500, todd.e.rinaldo at jpmorgan.com wrote:
>> Nope. We had to add date to assure uniqueness.... We're doing a funky
>> expire the row by making a newer one thing so we have access to
>> historical.
>
> Oh. My.
>
> Add an auto-inc PK instead then. Your database design will break if two
> changes happen below the resolution of the timestamp.
>
> A timestamp is not unique, it does not count, and your design is broken.
>
> Also, I'd suggest you consider a second table for the expired copy; expiring
> stuff tends to result in SELECTs on the main table getting prohibitively
> slow after a while.
>
> Ok, now we've got the sensible solution out of the way, I'm going to assume
> that you've had this broken design forced on you and discuss what you do
> to work around it (first, get a statement in writing that you have to do
> this so when it does break it isn't your fault).
>
> The reason DBIC throws an exception is that it doesn't know how to figure
> out what timestamp the record just got inserted with, and it expects that
> at the end of insert() it should have the whole primary key, because if it
> doesn't then the object can't be reliably updated/etc.
>
> I'd say that using DBIx::Class::TimeStamp and set_on_create is probably
> the easiest solution.
>
> Oterwise, you'd need to either
>
> (a) figure out how to get back the timestamp value that was inserted and
> patch Storage::DBI::Pg to handle it
>
> (b) add an option to insert() to say "I know my object is going to be
> broken after I call this method and I don't care".
>
> And I don't think (a) is possible. I'd be happy enough to take a patch for
> either or both on the ground of pragmatism over perfection :)

Matt, thanks for the advice, this pushed us into the right direction
for thinking about this. We're just going to be explicit about the
date stamp for now. My team mate may be getting back to the list with
a patch for this. He's determined that this should work. I suspect
it's going to have to be a DBD patch though.

I have a third suggestion to solve this problem: Do you think it would
be worth while to offer a create option where you would tell DBIC not
to try to find the pkey when it's done? In this case, we didn't
actually need the row back. We just needed to be sure it was inserted.