Re: Last inserted row id with complex PK

On 08 Jan 2014, at 16:54, Nelson Green <nelsongreen84@gmail.com> wrote:

> I have a projects log table with a three column PK, project_num, person_num, and sequence, where each new entry for a
project/personcombination increments the sequence, which is not an auto incrementing sequence. Is there any way to
retrievethe last entry to the table? For instance, if the last entry for person 427 on project 15 was sequence number
125,is that information available to me anywhere? 

I think the answer rather depends on what you mean with this last inserted row and for what purpose you need it.

If you want that information right after it’s been inserted (for example because you need that information in an FK
relation),you can use INSERT..RETURNING to return the values of the relevant PK fields. 

If that’s not what you’re after, then what is it that determines which record is the “last one”? If you can’t identify
sucha record from your data while you need that information, then something is missing from your model. 

If you’re planning to add such information to your model based on your current data, you might be able to get somewhat
reliableresults looking at the transaction xid’s that the records were created in. There are a number of pitfalls to
thatapproach though, the most obvious one being transaction xid wraparound. Of course, multiple inserts from the same
transactionwould (originally) have the same xid, so you wouldn’t be able to determine which one of those would be the
latest(unless they’re for the same person/project, obviously). 
Such information could then be used to add a field with, for example, an incrementing sequence.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.