Last inserted row id with complex PK

Hi Nelson:

On Wed, Jan 8, 2014 at 4:54 PM, 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/person combination
increments the sequence, which is not an auto incrementing sequence. Is
there any way to retrieve the 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?

SELECT MAX(sequence) FROM project_log_table WHERE person_num=427 AND
project_num=15

If it's a PK it should have an index and I believe it will be quite fast.

AAMOF, you could even build the above with max+1 into the insert query
for a new entry and have the values returned using a RETURNING clause.

Francisco Olarte.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Francisco Olarte <folarte@peoplecall.com> writes:

Hi Nelson:
On Wed, Jan 8, 2014 at 4:54 PM, 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/person combination
increments the sequence, which is not an auto incrementing sequence. Is
there any way to retrieve the 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?

SELECT MAX(sequence) FROM project_log_table WHERE person_num=427 AND
project_num=15

Note that this will not work terribly well if there are concurrent
insertions for the same person/project. If that's not an issue, though,
it should be fine. It may be OK even if there are sometimes concurrent
insertions, if you are prepared to retry duplicate-key failures.

If it's a PK it should have an index and I believe it will be quite fast.

It will be fast as long as sequence is the low-order column in the index.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jan 8, 2014 at 10:09 AM, Francisco Olarte <folarte@peoplecall.com>wrote:

Hi Nelson:

On Wed, Jan 8, 2014 at 4:54 PM, 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/person combination
increments the sequence, which is not an auto incrementing sequence. Is
there any way to retrieve the 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?

SELECT MAX(sequence) FROM project_log_table WHERE person_num=427 AND
project_num=15

Hi Francisco,

My apologies, I was not completely clear. I will not know any of the
columns in advance. The most recent insert is the result of user input from
a web form, so I won't know what project or what user generated the last
insert. That was why I wandered if that information was stored anywhere in
the system.

On Wed, Jan 8, 2014 at 10:22 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Francisco Olarte <folarte@peoplecall.com> writes:

Hi Nelson:
On Wed, Jan 8, 2014 at 4:54 PM, 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/person combination
increments the sequence, which is not an auto incrementing sequence. Is
there any way to retrieve the 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?

SELECT MAX(sequence) FROM project_log_table WHERE person_num=427 AND
project_num=15

Note that this will not work terribly well if there are concurrent
insertions for the same person/project. If that's not an issue, though,
it should be fine. It may be OK even if there are sometimes concurrent
insertions, if you are prepared to retry duplicate-key failures.

Concurrency is not likely to be an issue for this system, but I would
certainly plan for it since I can not rule it out.

Nelson Green wrote

My apologies, I was not completely clear. I will not know any of the
columns in advance. The most recent insert is the result of user input
from
a web form, so I won't know what project or what user generated the last
insert. That was why I wandered if that information was stored anywhere in
the system.

I'd probably add either (or both) a table-level auto-sequence field and a
"recordcreationdate" default timestamptz field.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Last-inserted-row-id-with-complex-PK-tp5785863p5785901.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jan 8, 2014 at 1:24 PM, David Johnston <polobo@yahoo.com> wrote:

Nelson Green wrote

My apologies, I was not completely clear. I will not know any of the
columns in advance. The most recent insert is the result of user input
from
a web form, so I won't know what project or what user generated the last
insert. That was why I wandered if that information was stored anywhere

in

the system.

I'd probably add either (or both) a table-level auto-sequence field and a
"recordcreationdate" default timestamptz field.

Yeah, default timestamp was the best solution I could come up with, but I
was hoping there was a way to access the actual PK of the most recent
insert.
Looks like timestamp it is.

Thanks everyone!

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/person combination increments the sequence, which is not an auto incrementing sequence. Is there any way to retrieve the 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 such a 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 reliable results looking at the transaction xid’s that the records were created in. There are a number of pitfalls to that approach though, the most obvious one being transaction xid wraparound. Of course, multiple inserts from the same transaction would (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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jan 8, 2014 at 11:54 PM, 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/person combination increments the sequence, which is not an auto
incrementing sequence. Is there any way to retrieve the 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?

Are these
project_num, person_num FK from some other table? If yes then I would first
insert in those tables and use it over here?

Best Regards,
*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD. *101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M : *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

[image: email patch]

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).

On 1/8/2014 10:14 AM, Nelson Green wrote:

On Wed, Jan 8, 2014 at 4:54 PM, Nelson Green
<nelsongreen84@gmail.com <mailto: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/person combination
increments the sequence, which is not an auto incrementing

sequence. Is

there any way to retrieve the 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?

SELECT MAX(sequence) FROM project_log_table WHERE person_num=427 AND
project_num=15

Hi Francisco,

My apologies, I was not completely clear. I will not know any of the
columns in advance. The most recent insert is the result of user input
from a web form, so I won't know what project or what user generated
the last insert. That was why I wandered if that information was
stored anywhere in the system.

wait, so you just want the latest record inserted with -any-
project/person ? add a timestamptz field to your table with default
current_timestamp;

--
john r pierce 37N 122W
somewhere on the middle of the left coast

Hi Nelson:

On Wed, Jan 8, 2014 at 7:14 PM, Nelson Green <nelsongreen84@gmail.com> wrote:

My apologies, I was not completely clear. I will not know any of the columns
in advance. The most recent insert is the result of user input from a web
form, so I won't know what project or what user generated the last insert.
That was why I wandered if that information was stored anywhere in the
system.

You mean someone already inserted a (project, user, sequence) tuple
and you need it? So the problem of calculating the last sequence for a
given ( p, u ) combination has been solved by the inserting web form
and you just need to communicate the result to another process ?

It seems what you need is an auxiliary log table ( serial + (P,U,S) )
which could be managed by a trigger, or made the web form do the
modifications using a procedure. Anyway, a lot more detail will be
needed to solve this.

Francisco Olarte.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jan 8, 2014 at 5:39 PM, Alban Hertroys <haramrae@gmail.com> wrote:

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/person
combination increments the sequence, which is not an auto incrementing
sequence. Is there any way to retrieve the 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.

Hi Alban,

That is almost exactly what I am trying to do (create a FK relation). I am
sorry
I was so vague, that was not intentional. I just have so many disparate
things
going on at once and I just wasn't thinking my question through very
clearly.

I am scripting the input of dummy data, where I have a list of projects and
users associated with those projects. My script chooses one pairing at
"random"
and inserts a dummy log record. I wanted to be able to look at the entry's
in
the order they were inserted in to verify my sequence trigger is working as
intended. The default timestamp gave me that ability, but I think your
suggestion of INSERT ... RETURNING would have been a bit more in line with
my
original thought, where the script was doing an insert to the project table
and
then logging the PK of that last insert to a temp table. The PK of the temp
table is an auto incrementing sequence, but I wasn't sure how to get the PK
of
the log table from the previous INSERT. Since this is a test system I was
able
to add the timestamp and then just perform a query, sorting by that. But,
the
timestamp solution altered the model, the temp table did not.

Regards,
Nelson