Thread: Re: [NOVICE] Last ID Problem

Re: [NOVICE] Last ID Problem

From
"John Hansen"
Date:
Tom Lane Writes:
> Michael Fuhr <mike@fuhr.org> writes:
> > On Tue, Feb 01, 2005 at 12:56:20AM -0500, Tom Lane wrote:
> >> His point stands though: if you are accessing Postgres
> through some
> >> kind of connection-pooling software, currval() cannot be trusted
> >> across transaction boundaries, since the pool code might give your
> >> connection to someone else.  In this situation the
> >> nextval-before-insert paradigm is the only way.
>
> > I don't disagree with that; if the thread mentioned
> connection pooling
> > then I must have overlooked it.
>
> >> (But in most of the applications I can think of, your uses
> of currval
> >> subsequent to an INSERT ought to be in the same transaction as the
> >> insert, so are perfectly safe.  If your connection pooler takes
> >> control away from you within a transaction block, you need a less
> >> broken
> >> pooler...)
>
> > That's the common situation I was talking about: doing an
> INSERT and
> > immediately calling currval(), presumably in the same transaction.
> > I should have been more clear about that and warned what
> could happen
> > in other situations.  Thanks.
>
> Apropos to all this: Tatsuo recently proposed a RESET
> CONNECTION command that could be used to reset a connection
> between pooling assignments, so as to be sure that different
> pooled threads wouldn't see state that changes depending on
> what some other thread did.  It seems like RESET CONNECTION
> ought to reset all currval() states to the "error, currval
> not called yet" condition.  Comments?


I have a suggestion...

For libpq:

Since OID's are now deprecated, and will eventually disappear,
wouldn't it be a good idea, to have INSERT and UPDATE return
a copy of the tuple that was inserted/updated?

This way, you could have a funtion to fetch an arbitrary named
column from that tuple.
Like: last_insert_value(tuple,'column_name')

... John


Re: [NOVICE] Last ID Problem

From
Alvaro Herrera
Date:
On Tue, Feb 01, 2005 at 06:31:30PM +1100, John Hansen wrote:

> Since OID's are now deprecated, and will eventually disappear,
> wouldn't it be a good idea, to have INSERT and UPDATE return
> a copy of the tuple that was inserted/updated?

How about the TID?

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"El destino baraja y nosotros jugamos" (A. Schopenhauer)

Re: [NOVICE] Last ID Problem

From
"Joshua D. Drake"
Date:
>
>
>
>I have a suggestion...
>
>For libpq:
>
>Since OID's are now deprecated, and will eventually disappear,
>wouldn't it be a good idea, to have INSERT and UPDATE return
>a copy of the tuple that was inserted/updated?
>
>This way, you could have a funtion to fetch an arbitrary named
>column from that tuple.
>Like: last_insert_value(tuple,'column_name')
>
>

With a default to return the primary key?

Sincerely,

Joshua D. Drake



>... John
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL


Attachment

Re: [NOVICE] Last ID Problem

From
Neil Conway
Date:
On Tue, 2005-02-01 at 11:24 -0300, Alvaro Herrera wrote:
> How about the TID?

That wouldn't be sufficiently stable for use by client applications, I
believe: a concurrent VACUUM FULL could mean your TID no longer points
at what you think it does.

-Neil



Re: [NOVICE] Last ID Problem

From
Tom Lane
Date:
Neil Conway <neilc@samurai.com> writes:
> On Tue, 2005-02-01 at 11:24 -0300, Alvaro Herrera wrote:
>> How about the TID?

> That wouldn't be sufficiently stable for use by client applications, I
> believe: a concurrent VACUUM FULL could mean your TID no longer points
> at what you think it does.

It'd be safe enough within the same transaction, since VACUUM can't kill
a tuple inserted by an open transaction; nor could VACUUM FULL touch the
table at all, since you'll be holding at least a writer's lock on the
table.

But this is all moot since INSERT/UPDATE RETURNING is really the way to
go, on grounds of functionality, speed, and not breaking backward
compatibility for existing client code.

            regards, tom lane

Re: [NOVICE] Last ID Problem

From
Neil Conway
Date:
On Tue, 2005-02-01 at 17:50 -0500, Tom Lane wrote:
> It'd be safe enough within the same transaction, since VACUUM can't kill
> a tuple inserted by an open transaction; nor could VACUUM FULL touch the
> table at all, since you'll be holding at least a writer's lock on the
> table.

True, but it still seems rather fragile -- it would be quite easy for
people to get this wrong and not realize it (and then wonder why their
application is silently corrupting data at odd times). Also, it might
constrain out ability to improve how we garbage collect expired tuples
in the future, although that's less of a concern.

> But this is all moot since INSERT/UPDATE RETURNING is really the way to
> go, on grounds of functionality, speed, and not breaking backward
> compatibility for existing client code.

Agreed. Also, I believe we could do this without needing a protocol
version bump.

-Neil



Re: [NOVICE] Last ID Problem

From
John Hansen
Date:
> With a default to return the primary key?


Of course, that would be ideal ... :)