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

[Matt S Trout]

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 S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/