Last ID Problem

Hi,

$id = "SELECT currval('cust_id')"; // used in an
attempt to get last id (colum 'cust id')entered into
db.

$result = $db->Execute($sql); // works fine.

$id_result = $db->execute($id); // $id_result has no
value.

This is because currval fetched data during a transaction process and
not after the process is complete.

the best way I suggest is

$id = "SELECT cust_id from customer where customer_name ='$cust'
order by cust_id desc limit1" ; // used in an

--
With Best Regards,
Vishal Kashyap.
Lead Software Developer,
http://saihertz.com,
http://vishalkashyap.tk

Vishal,

will your suggestion protect my db if I have two
customers with the same name? does it always return
the highest cust_id and is that *always* the last
cust_id entered?

tia...

--- "Vishal Kashyap @ [SaiHertz]"
<vishalonlist@gmail.com> wrote:

Hi,

$id = "SELECT currval('cust_id')"; // used in an
attempt to get last id (colum 'cust id')entered

into

db.

$result = $db->Execute($sql); // works fine.

$id_result = $db->execute($id); // $id_result has

no

value.

This is because currval fetched data during a
transaction process and
not after the process is complete.

the best way I suggest is

$id = "SELECT cust_id from customer where
customer_name ='$cust'
order by cust_id desc limit1" ; // used in an

--
With Best Regards,
Vishal Kashyap.
Lead Software Developer,
http://saihertz.com,
http://vishalkashyap.tk

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Mon, 31 Jan 2005 11:13:58 -0800 (PST),
operationsengineer1@yahoo.com <operationsengineer1@yahoo.com> wrote:

-----------------------

$cust = $_POST['cust']; // data entered
$cust = addslashes($cust); // take care of slashes

$db = &ADONewConnection('postgres');
$db -> Connect($db_string,$db_owner,$db_pw,$db_name);

$sql = "INSERT INTO customer (customer_name) VALUES
('$cust')"; // query to insert data - works fine.

$id = "SELECT currval('cust_id')"; // used in an
attempt to get last id (colum 'cust id')entered into
db.

$result = $db->Execute($sql); // works fine.

$id_result = $db->execute($id); // $id_result has no
value.

------------------------------

Why not first get the current value from the sequence, use it for your
INSERT statement, and then have it handy for the rest of the script?

http://phplens.com/lens/adodb/docs-adodb.htm#inserted_id

-- Mitch

Hi ,

will your suggestion protect my db if I have two
customers with the same name?

Yes , it would with the consideration that the sequence used to create
the cust_id is increasing by some number , customer may have same
name but in all means the customer name which is having the MAXIMUM
cust_id is ur need.

does it always return
the highest cust_id and is that *always* the last
cust_id entered?

did i said I gave you a query that would run faster you may alternatively use

$id = "SELECT max(cust_id) from customer where customer_name ='$cust' " ;

Use explain in psql and u will know why I suggested the previous query.

--
With Best Regards,
Vishal Kashyap.
Lead Software Developer,
http://saihertz.com,
http://vishalkashyap.tk

mitch, i tried insert_id(), however, the following...

print "Query Success! The new row has an id of: " .
$db->Insert_Id();

produced...

"Query Success! The new row has an id of: 0"

every time.

reading your suggestion, though, leads me to believe
that geting insert_id() BEFORE running the the query
may impact the results.

can you point me to a simple code example of the whole
process?

also, i recall reading something about insert_id() not
working if the db connection wasn't persistent.

--- Mitch Pirtle <mitch.pirtle@gmail.com> wrote:

On Mon, 31 Jan 2005 11:13:58 -0800 (PST),
operationsengineer1@yahoo.com
<operationsengineer1@yahoo.com> wrote:

-----------------------

$cust = $_POST['cust']; // data entered
$cust = addslashes($cust); // take care of slashes

$db = &ADONewConnection('postgres');
$db ->

Connect($db_string,$db_owner,$db_pw,$db_name);

$sql = "INSERT INTO customer (customer_name)

VALUES

('$cust')"; // query to insert data - works fine.

$id = "SELECT currval('cust_id')"; // used in an
attempt to get last id (colum 'cust id')entered

into

db.

$result = $db->Execute($sql); // works fine.

$id_result = $db->execute($id); // $id_result has

no

value.

------------------------------

Why not first get the current value from the
sequence, use it for your
INSERT statement, and then have it handy for the
rest of the script?

http://phplens.com/lens/adodb/docs-adodb.htm#inserted_id

-- Mitch

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the
unregister command
(send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)

__________________________________
Do you Yahoo!?
Yahoo! Mail - now with 250MB free storage. Learn more.
http://info.mail.yahoo.com/mail_250

This is the easiest way to do it:

http://ask.slashdot.org/article.pl?sid=05/01/31/1441200&amp;from=rss

This is using plain old SQL the PostgreSQL way ;-)

Basically you:

1) get the next number from the sequence
2) do the update
3) use that number for related table insterts

For an ADOdb example, this thread:

http://www.phparch.com/discuss/index.php/t/372/0/

Says to use this syntax:

$insert_id = $db->getone("select currval('sequence_name')");

-- Mitch

thanks mitch...

i ahve the following code...

$cust = $_POST['cust'];
$cust = addslashes($cust);
$db = &ADONewConnection('postgres');
$db -> Connect($db_string,$db_owner,$db_pw,$db_name);
$sql = "INSERT INTO customer (customer_name) VALUES
('$cust')";
$result = $db->Execute($sql);
$insert_id = $db->getone("select currval('cust_id')");

if ($result === false)
{
print $db->ErrorMsg();
exit();
}
else
{
$dbreturn = 'Passed';
print $dbreturn;
print $insert_id;
exit();
}

it prints $dbreturn as "Passed", but it does not print
any value for insert_id. i'm trying to see this value
and verify it is working correctly before trying
anything more complex.

--- Mitch Pirtle <mitch.pirtle@gmail.com> wrote:

This is the easiest way to do it:

http://ask.slashdot.org/article.pl?sid=05/01/31/1441200&amp;from=rss

This is using plain old SQL the PostgreSQL way ;-)

Basically you:

1) get the next number from the sequence
2) do the update
3) use that number for related table insterts

For an ADOdb example, this thread:

http://www.phparch.com/discuss/index.php/t/372/0/

Says to use this syntax:

$insert_id = $db->getone("select
currval('sequence_name')");

-- Mitch

---------------------------(end of
broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose
an index scan if your
joining column's datatypes do not match

__________________________________
Do you Yahoo!?
Yahoo! Mail - now with 250MB free storage. Learn more.
http://info.mail.yahoo.com/mail_250

On Mon, 31 Jan 2005 15:33:02 -0800 (PST),
operationsengineer1@yahoo.com <operationsengineer1@yahoo.com> wrote:

thanks mitch...

i ahve the following code...

$cust = $_POST['cust'];
$cust = addslashes($cust);
$db = &ADONewConnection('postgres');
$db -> Connect($db_string,$db_owner,$db_pw,$db_name);
$sql = "INSERT INTO customer (customer_name) VALUES
('$cust')";
$result = $db->Execute($sql);
$insert_id = $db->getone("select currval('cust_id')");

if ($result === false)
{
print $db->ErrorMsg();
exit();
}
else
{
$dbreturn = 'Passed';
print $dbreturn;
print $insert_id;
exit();
}

it prints $dbreturn as "Passed", but it does not print
any value for insert_id. i'm trying to see this value
and verify it is working correctly before trying
anything more complex.

That is because you are doing it out of order. First, you get the
sequence id, and THEN you use that number for your INSERT statement:

$cust = $_POST['cust'];
$cust = addslashes($cust);
$db = &ADONewConnection('postgres');
$db -> Connect($db_string,$db_owner,$db_pw,$db_name);
// get the insert id FIRST
$insert_id = $db->getone("select currval('cust_id')");
// THEN issue the INSERT statement
$sql = 'INSERT INTO customer (id, customer_name) VALUES
(' . $id . ', ' . $db->qstr( $cust ) . ')';

if ( $db->Execute( $sql ) === false ){
print $db->ErrorMsg();
} else {
$dbreturn = 'Passed';
print $dbreturn;
print $insert_id;
}

I also changed around the format of your SQL statement, as it makes
sense to quote your $cust before adding to the database. So so you see
the difference? You need to get the sequence number first, and then
use it in your queries. The exit() statements were not needed, and I
wanted to show a different way of nesting your IF statement.

Note that an INSERT statement doesn't return a resultset, just a
success or fail. John's way of doing it (at least for the
documentation) are found here:

http://phplens.com/lens/adodb/docs-adodb.htm#ex3

It is a good example, as it quotes strings and uses time() as well.

-- Mitch

On Mon, Jan 31, 2005 at 03:33:02PM -0800, operationsengineer1@yahoo.com wrote:

$cust = $_POST['cust'];
$cust = addslashes($cust);
$db = &ADONewConnection('postgres');
$db -> Connect($db_string,$db_owner,$db_pw,$db_name);
$sql = "INSERT INTO customer (customer_name) VALUES
('$cust')";
$result = $db->Execute($sql);
$insert_id = $db->getone("select currval('cust_id')");

If cust_id was defined as a serial type then you should be calling
currval() with the sequence name, not the column name. Look at the
table definition (e.g., run "\d customer" in psql) and see what the
sequence name is. It's probably customer_cust_id_seq; if so, then
following should work:

$insert_id = $db->getone("select currval('customer_cust_id_seq')");

Contrary to what another message in this thread says, it is indeed
common practice to do the insert first and call currval() afterwards
to find out what value you got from the sequence. And no, this
doesn't introduce a race condition -- currval() returns the last
value obtained from the sequence in the current session.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Mon, Jan 31, 2005 at 07:58:42PM -0500, Mitch Pirtle wrote:

That is because you are doing it out of order. First, you get the
sequence id, and THEN you use that number for your INSERT statement:

Common practice when using a sequence in PostgreSQL is to do the
INSERT first, then call currval() to find out what value you got.
If you want to obtain the sequence value first then use nextval(),
not currval() as your code showed. Calling currval() before any
calls to nextval() should fail with an error like the following:

currval of sequence "customer_cust_id_seq" is not yet defined in this session

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Mon, 31 Jan 2005 18:38:55 -0700, Michael Fuhr <mike@fuhr.org> wrote:

Contrary to what another message in this thread says, it is indeed
common practice to do the insert first and call currval() afterwards
to find out what value you got from the sequence. And no, this
doesn't introduce a race condition -- currval() returns the last
value obtained from the sequence in the current session.

Tell that to the maintainers of PEAR's DB, which is installed by
default with all recent versions of PHP (that would be all of them). I
felt the exact same way as you did, and spent an afternoon
rediscovering the joys of sequence values until one of the maintainers
pointed out that behavior. I even tried to convince them that this was
a bug ('inappropriate behavior' was the term IIRC)...

'Common', unfortunately, is relative; and in this matter might only
apply to ADOdb ;-)

-- Mitch, getting his PHP database classes all mixed up *gasp*

On Mon, Jan 31, 2005 at 08:55:22PM -0500, Mitch Pirtle wrote:

On Mon, 31 Jan 2005 18:38:55 -0700, Michael Fuhr <mike@fuhr.org> wrote:

Contrary to what another message in this thread says, it is indeed
common practice to do the insert first and call currval() afterwards
to find out what value you got from the sequence. And no, this
doesn't introduce a race condition -- currval() returns the last
value obtained from the sequence in the current session.

Tell that to the maintainers of PEAR's DB, which is installed by
default with all recent versions of PHP (that would be all of them). I
felt the exact same way as you did, and spent an afternoon
rediscovering the joys of sequence values until one of the maintainers
pointed out that behavior. I even tried to convince them that this was
a bug ('inappropriate behavior' was the term IIRC)...

I don't use DB so I can't comment on what its maintainers should
or shouldn't be doing. Abstraction layers sometimes do things in
ways that are easy to implement across multiple systems, so the
maintainers might have portability concerns.

I'm not saying that doing the INSERT first and then calling currval()
is the "right" way, just that it's a common way, one that's often
suggested on the PostgreSQL mailing lists. One argument in its
favor is that you can use the same INSERT statement regardless of
whether you need the sequence number or not, so that's one less
thing to maintain if your needs change in that respect.

'Common', unfortunately, is relative; and in this matter might only
apply to ADOdb ;-)

The world's bigger than a couple of PHP modules :-) Calling currval()
after an INSERT is a common way to get the sequence value when using
PostgreSQL, regardless of the programming language or API being
used.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr <mike@fuhr.org> writes:

On Mon, Jan 31, 2005 at 08:55:22PM -0500, Mitch Pirtle wrote:

'Common', unfortunately, is relative; and in this matter might only
apply to ADOdb ;-)

The world's bigger than a couple of PHP modules :-) Calling currval()
after an INSERT is a common way to get the sequence value when using
PostgreSQL, regardless of the programming language or API being
used.

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.

(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...)

regards, tom lane

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.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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?

regards, tom lane

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

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)

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?

Yea, that'd work. As long as you can get an arbitrary column back out, 'as it was at the time it was committed'.

Since not everything runs in a transaction,. And someone might have modified the row by the time you get to fetching it back out....

Or in terms of tuples,. No longer exist, if vacuum full have run...

... JOhn

"John Hansen" <john@geeknet.com.au> writes:

Since OID's are now deprecated, and will eventually disappear,

No one has stated that they will 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?

Yea, that'd work.

You could only trust it for the duration of the inserting or updating
transaction. Which might be enough ... but changing it would certainly
break all existing apps that use this feature.

I think the correct solution is not to mess with what's admittedly a
legacy aspect of our client API. Instead we should invent the "INSERT
RETURNING" and "UPDATE RETURNING" commands that have been discussed
repeatedly (see the pghackers archives). That would allow people to get
what they want, and do so in only one network round trip, without any
artificial dependencies on OIDs or TIDs or anything else. It'd be
unportable, but surely no more so than relying on OIDs or TIDs ...

regards, tom lane

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

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

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

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

With a default to return the primary key?

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

No one has stated that they will disappear.

Ohh,... just the impression I've been getting when speaking with people.

... John

Hi Tom and others,

I think the correct solution is not to mess with what's admittedly a

legacy aspect of

our client API. Instead we should invent the "INSERT RETURNING" and

"UPDATE RETURNING"

commands that have been discussed repeatedly (see the pghackers archives).

That would

allow people to get what they want, and do so in only one network round

trip, without

any artificial dependencies on OIDs or TIDs or anything else. It'd be

unportable, but

surely no more so than relying on OIDs or TIDs ...

Just off the top of my head, would it not be feasible to add a column to
pg_class called lastinsert that points to the OID of the pg_attribute column
to return after an insert? It could be changed using something similar to
"ALTER TABLE x SET LASTINSERT TO y", but by default it would be set to the
OID of the primary key of the table if the table specified WITHOUT OIDS at
creation time, or the first column of the table otherwise. After the INSERT
command, the value of the resulting is column is passed back to the client.

I see that INSERT...RETURNING is a solution to the problem, but it seems
somewhat strange to have to use an unportable command just to be able to
return an identifier for the last inserted record...

Kind regards,

Mark.

------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT

T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk

"Mark Cave-Ayland" <m.cave-ayland@webbased.co.uk> writes:

Just off the top of my head, would it not be feasible to add a column to
pg_class called lastinsert that points to the OID of the pg_attribute column
to return after an insert?

No. The thing everyone is ignoring here is that the INSERT command tag
format is not something we can just go and change. You certainly could
not put anything in it that wasn't an integer, and I'm not sure it would
even be safe to put a bigint. So most of the cases you might actually
want (timestamp, bigserial, etc) would be ruled out. Hardly worth
inventing such a feature.

I see that INSERT...RETURNING is a solution to the problem, but it seems
somewhat strange to have to use an unportable command just to be able to
return an identifier for the last inserted record...

How is what you're suggesting more portable?

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

How is what you're suggesting more portable?

Well, the driver would be free to implement $sth->last_insert_id() using
whatever proprietary extensions it has available. The non-portableness would
at least be hidden in the driver layer. Switch out the driver and the right
thing would happen.

"INSERT/UPDATE ... RETURNING" isn't something a driver can take advantage of.
It would require it to modify your statements which it can't do safely. So
your application would have such non-portable SQL code written into it. Switch
databases and your application code needs to be ported.

--
greg

Greg Stark <gsstark@mit.edu> writes:

Tom Lane <tgl@sss.pgh.pa.us> writes:

How is what you're suggesting more portable?

Well, the driver would be free to implement $sth->last_insert_id() using
whatever proprietary extensions it has available. The non-portableness would
at least be hidden in the driver layer.

Are you asserting that last_insert_id() is a portable function? I doubt
it.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:
"INSERT/UPDATE ... RETURNING" isn't something a driver can take

advantage

of.
It would require it to modify your statements which it can't do

safely. So

your application would have such non-portable SQL code written into

it.

Switch
databases and your application code needs to be ported.

I really don't think it matters. Currently, in PostgreSQL, there is
only 'one true way' to have a real unique identifier for any given tuple
that is persistent across queries and this is a sequence. Since
sequences are basically managed by the app, your driver (I'm assuming)
can't reliably use them.

This is kind of similar to the issues being talked about wrt user locks.
Because the lack of a true persistent tuple identifier, they require
some data to be passed to them from the app (not really a big deal for
them, however).

From the point of view of your driver, the real solution is to bump oid
to 64 bits and un-deprecate it.

Merlin

Tom Lane wrote:

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?

TODO update:

* Add RESET CONNECTION command to reset all session state

This would include resetting of all variables (RESET ALL), dropping of
all temporary tables, removal of any NOTIFYs, cursors, prepared
queries(?), currval()s, etc. This could be used for connection pooling.
We could also change RESET ALL to have this functionality.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom Lane <tgl@sss.pgh.pa.us> writes:

Greg Stark <gsstark@mit.edu> writes:

Tom Lane <tgl@sss.pgh.pa.us> writes:

How is what you're suggesting more portable?

Well, the driver would be free to implement $sth->last_insert_id() using
whatever proprietary extensions it has available. The non-portableness would
at least be hidden in the driver layer.

Are you asserting that last_insert_id() is a portable function? I doubt
it.

Well I'm not sure what you mean by "portable". It's part of the DBI driver
definition, so in theory it is. Not all drivers will implement it though, or
implement it properly, and for some it may be more efficient than others.

For postgres it looks like currently it requires you to pass in the table and
field might even need a "driver-specific hint" telling it the sequence name.

At least an application using it has a hope of working on a new driver. An
application using RETURNING will only work on Oracle and one day Postgres.

So it would be nice if the Postgres driver could efficiently implement it
without having to do a second SELECT and without having to know out of band
info like a sequence name.

This is from the DBI documentation -- that is, the non-driver-specific
abstract interface documentation.

"last_insert_id"
$rv = $dbh->last_insert_id($catalog, $schema, $table, $field);
$rv = $dbh->last_insert_id($catalog, $schema, $table, $field, \%attr);

Returns a value 'identifying' the row just inserted, if possible.
Typically this would be a value assigned by the database server to
a column with an auto_increment or serial type. Returns undef if
the driver does not support the method or can't determine the
value.

The $catalog, $schema, $table, and $field parameters may be
required for some drivers (see below). If you don't know the
parameter values and your driver does not need them, then use
"undef" for each.

There are several caveats to be aware of with this method if you
want to use it for portable applications:

* For some drivers the value may only available immediately after
the insert statement has executed (e.g., mysql, Informix).

* For some drivers the $catalog, $schema, $table, and $field
parameters are required (e.g., Pg), for others they are ignored
(e.g., mysql).

* Drivers may return an indeterminate value if no insert has been
performed yet.

* For some drivers the value may only be available if placeholders
have not been used (e.g., Sybase, MS SQL). In this case the value
returned would be from the last non-placeholder insert statement.

* Some drivers may need driver-specific hints about how to get the
value. For example, being told the name of the database
'sequence' object that holds the value. Any such hints are passed
as driver-specific attributes in the \%attr parameter.

* If the underlying database offers nothing better, then some
drivers may attempt to implement this method by executing
""select max($field) from $table"". Drivers using any approach
like this should issue a warning if "AutoCommit" is true because
it is generally unsafe - another process may have modified the
table between your insert and the select. For situations where
you know it is safe, such as when you have locked the table, you
can silence the warning by passing "Warn" => 0 in \%attr.

* If no insert has been performed yet, or the last insert failed,
then the value is implementation defined.

Given all the caveats above, it's clear that this method must be
used with care.

The "last_insert_id" method was added in DBI 1.38.

--
greg

Tom Lane <tgl@sss.pgh.pa.us> writes:

Greg Stark <gsstark@mit.edu> writes:

Tom Lane <tgl@sss.pgh.pa.us> writes:

How is what you're suggesting more portable?

For postgres it looks like currently it requires you to pass in the

table

and
field might even need a "driver-specific hint" telling it the sequence
name.

That is a shortcoming of the DBD::pg driver which really should be
returning a key (comprised of columns, some or none of which may be
defaulted by the server). The 'database supplied' integer assumption is
bad, bad, bad. In fairness, getting the last returned key is a
catastrophic limitation of sql that we must all work around (itself
being a specific annoyance of that tricky devil, default columns). :-)

The only thing that is going to meet your requirements is a system wide
(well, at least table wide, but system wide would be better) 64 bit oid,
which doesn't exist right now. Sequences (or more generally, defaulted
columns) are application managed and difficult to deal with at the
driver level.

Merlin

Tom Lane wrote:

Greg Stark <gsstark@mit.edu> writes:

Tom Lane <tgl@sss.pgh.pa.us> writes:

How is what you're suggesting more portable?

Well, the driver would be free to implement $sth->last_insert_id() using
whatever proprietary extensions it has available. The non-portableness would
at least be hidden in the driver layer.

Are you asserting that last_insert_id() is a portable function? I doubt
it.

I'm not familiar with the Perl interface, but JDBC has a standardized
interface for this:

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String,%20int)
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#getGeneratedKeys()

I tend to agree that a protocol-level change is easier to support in a
driver. If it's done by extending INSERT/UPDATE, the driver will need to
parse and modify queries which is hairy at the best of times.

-O

Greg Stark <gsstark@mit.edu> writes:

This is from the DBI documentation -- that is, the non-driver-specific
abstract interface documentation.

Returns a value 'identifying' the row just inserted, if possible.
Typically this would be a value assigned by the database server to
a column with an auto_increment or serial type.

Aside from the numerous serious problems pointed out in the
documentation, this has an even more fatal objection, which is that it's
unspecified what the result value is and thus there is no portable way
of *using* the result after you have it. (If the PG driver returns an
OID you certainly couldn't use that the same way as some other driver
that returns a primary key ... especially a multicolumn primary key ...)

This "portable" function is so unportable that I see no reason to
accept it as precedent.

regards, tom lane

This "portable" function is so unportable that I see no reason to
accept it as precedent.

Hm. Instead of altering the syntax, what slipping in the last
inserted/updated tuple into the PQResult object? Maybe is a protocol
level option? Now everybody gets to use it with minimal muss.

Merlin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Merlin Moncur wrote:

That is a shortcoming of the DBD::pg driver which really should be
returning a key (comprised of columns, some or none of which may be
defaulted by the server).

Actually, the spec comes from DBI, not DBD::Pg, and is inspired by
MySQL's last_insert_id function. It is a poorly-speced function,
but we've done our best in the upcoming version of DBD::Pg,
which will support it.

Greg Stark wrote:

For postgres it looks like currently it requires you to pass in
the table and field might even need a "driver-specific hint" telling
it the sequence name.

For the record, the only required field for DBD::Pg will be the table
name, although the name of the sequence is highly encouraged. Here's
the docs for the next version, the first which supports lii:

last_insert_id

$rv = $dbh->last_insert_id($catalog, $schema, $table, $field);
$rv = $dbh->last_insert_id($catalog, $schema, $table, $field, \%attr);

Attempts to return the id of the last value to be inserted into a table.
You can either provide a sequence name (preferred) or provide a table
name with optional schema. The $catalog and $field arguments are always ignored.
The current value of the sequence is returned by a call to the
'currval' PostgreSQL function. This will fail if the sequence has not yet
been used in the current database connection.

If you do not know the name of the sequence, you can provide a table name and
DBD::Pg will attempt to return the correct value. To do this, there must be at
least one column in the table with a C<NOT NULL> constraint, that has a unique
constraint, and which uses a sequence as a default value. If more than one column
meets these conditions, the primary key will be used. This involves some
looking up of things in the system table, so DBD::Pg will cache the sequence
name for susequent calls. If you need to disable this caching for some reason,
you can control it via the 'pg_cache' attribute.

Please keep in mind that this method is far from foolproof, so make your
script use it properly. Specifically, make sure that it is called
immediately after the insert, and that the insert does not add a value
to the column that is using the sequence as a default value.

Some examples:

$dbh->do("CREATE SEQUENCE lii_seq START 1");
$dbh->do("CREATE TABLE lii (
foobar INTEGER NOT NULL UNIQUE DEFAULT nextval('lii_seq'),
baz VARCHAR)");
$SQL = "INSERT INTO lii(baz) VALUES (?)";
$sth = $dbh->prepare($SQL);
for (qw(uno dos tres quattro)) {
$sth->execute($_);
my $newid = $dbh->last_insert_id(C<undef>,undef,undef,undef,{sequence=>'lii_seq'});
print "Last insert id was $newid\n";
}

If you did not want to worry about the sequence name:

$dbh->do("CREATE TABLE lii2 (
foobar SERIAL UNIQUE,
baz VARCHAR)");
$SQL = "INSERT INTO lii2(baz) VALUES (?)";
$sth = $dbh->prepare($SQL);
for (qw(uno dos tres quattro)) {
$sth->execute($_);
my $newid = $dbh->last_insert_id(undef,undef,"lii2",undef);
print "Last insert id was $newid\n";
}

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200502022110
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCAYgSvJuQZxSWSsgRAgg3AJ4id98pta0CQR2w3xgwkxnph7qW4wCeMAJH
g/eXhtcmvXei9mESDDXg/s8=
=QaUa
-----END PGP SIGNATURE-----

Attempts to return the id of the last value to be inserted into a table.
You can either provide a sequence name (preferred) or provide a table
name with optional schema. The $catalog and $field arguments are always ignored.
The current value of the sequence is returned by a call to the
'currval' PostgreSQL function. This will fail if the sequence has not yet
been used in the current database connection.

This suffers from the same problems that currval does when using
connection pools tho.

I previously suggested a function similar to last_insert_id in behaviour,
and have attached it to this email for reference.

Even so, this also suffers from the same problems when using a connection pool.

The solution I proposed, namely having the tuple returned by
inserts/updates (perhaps even deletes?) would only mean changing the
client library to handle this, and as an example, libpg could easily
figure out the OID of said tuple and return that if it's present for
PQExec() (for backwards compatibility just as it does today,) and add a
separate PQExecSelect() that instead returns the tuple(s) as if they had
been SELECTed.

--
John Hansen <john@geeknet.com.au>
GeekNET

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This suffers from the same problems that currval does when using
connection pools tho.

I still don't see this as much of a real world problem however,
more of a "doctor, it hurts when I do this" variety. As the DBD::Pg docs
point out, you should not separate the calls to insert and currval
far apart, and certainly not across connections. I have a hard time
visualizing a case where an app would ever need to worry about the
problem anyway, unless they were using pooling in a strange way and
had a very poorly written application.

The solution I proposed, namely having the tuple returned by
inserts/updates (perhaps even deletes?) would only mean changing the
client library to handle this, and as an example, libpg could easily
figure out the OID of said tuple and return that if it's present for
PQExec() (for backwards compatibility just as it does today,) and add a
separate PQExecSelect() that instead returns the tuple(s) as if they had
been SELECTed.

There's a few issues with the above, however, the most important of which
is that OIDs are going away, and then what do you use? Also, it does not
handle cases where the insert necessarily happened with a direct INSERT
via PQexec: the insert could have happened inside of a called function, or
a trigger could have inserted into three different tables. Truth be told, I
don't think the whole last_insert_id() in DBI is a very useful function
anyway. It's mainly (at this point) a quick abstraction of a nextval call
between Oracle and PostgreSQL. We do go out of our way to be more compatible
to MySQL by accepting just a table name, but one should really use the
sequence directly, IMO.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200502030012
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFCAbQRvJuQZxSWSsgRArYMAKC4Kgsv153HHbC05AtraAh4O7oL9wCgtDmR
zoucziPs5cyC1at00M8MC9w=
=PDUD
-----END PGP SIGNATURE-----

Hi Tom,

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 02 February 2005 15:35
To: Mark Cave-Ayland
Cc: 'Alvaro Herrera'; 'Michael Fuhr'; 'Mitch Pirtle'; 'Tatsuo
Ishii'; pgsql-hackers@postgresql.org; operationsengineer1@yahoo.com
Subject: Re: [NOVICE] Last ID Problem

(cut)

No. The thing everyone is ignoring here is that the INSERT
command tag format is not something we can just go and
change. You certainly could not put anything in it that
wasn't an integer, and I'm not sure it would even be safe to
put a bigint. So most of the cases you might actually want
(timestamp, bigserial, etc) would be ruled out. Hardly worth
inventing such a feature.

OK, I didn't realise it was the command tag that was the issue here. I took
a look at the libpq source to see how the INSERT tag works and it looks like
it currently assumes a string of numbers. So as a minimum it would need some
form of protocol extension to get this work (which I see from the archives
that you were not keen to pursue).

How is what you're suggesting more portable?

I was hoping that it would only require minimal change (but obviously that
is not the case). I think, out of the remaining options, that keeping with
currval() is going to be the best approach - I can't really see the benefit
of using a non-SQL standard command just for SQL insertion, mainly as you
and others have suggested for portability reasons :(

Kind regards,

Mark.

------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT

T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk

That is because you are doing it out of order.
First, you get the
sequence id, and THEN you use that number for your
INSERT statement:

$cust = $_POST['cust'];
$cust = addslashes($cust);
$db = &ADONewConnection('postgres');
$db ->
Connect($db_string,$db_owner,$db_pw,$db_name);
// get the insert id FIRST
$insert_id = $db->getone("select
currval('cust_id')");
// THEN issue the INSERT statement
$sql = 'INSERT INTO customer (id, customer_name)
VALUES
(' . $id . ', ' . $db->qstr( $cust ) . ')';

if ( $db->Execute( $sql ) === false ){
print $db->ErrorMsg();
} else {
$dbreturn = 'Passed';
print $dbreturn;
print $insert_id;
}

I also changed around the format of your SQL
statement, as it makes
sense to quote your $cust before adding to the
database. So so you see
the difference? You need to get the sequence number
first, and then
use it in your queries. The exit() statements were
not needed, and I
wanted to show a different way of nesting your IF
statement.

Note that an INSERT statement doesn't return a
resultset, just a
success or fail. John's way of doing it (at least
for the
documentation) are found here:

http://phplens.com/lens/adodb/docs-adodb.htm#ex3

It is a good example, as it quotes strings and uses
time() as well.

-- Mitch

mitch and all, i've developed a simple little script
in order to test the "last id" methodology mitch
suggested.

it looks like this... php and adodb include excluded
for brevity...

-----
$db = &ADONewConnection('postgres7');
$db -> Connect($db_string,$db_owner,$db_pw,$db_name);
$insert_id = $db->getone("select
nextval('public.customer_cust_id_seq')");

print 'The ID is ' . $insert_id;
-----

my sequence name is 'public.customer_cust_id_seq'
(found this in pgadmin3).

the last id number in my table is 65. when i use
nextval(), i get a result of 66 for $insert_id - which
is the value that i would want to then perform and
insert.

however, when i use currval(), as recommended, i get
no result. i probably get an error, but i haven't
checked for that yet.

is it OK to use nextval() to get the next id value in
the sequence before doing an insert? how come
currval() doesn't work.

thanks to all for any guidance here.

__________________________________
Do you Yahoo!?
Yahoo! Mail - now with 250MB free storage. Learn more.
http://info.mail.yahoo.com/mail_250

mitch and all, i've developed a simple little script
in order to test the "last id" methodology mitch
suggested.

it looks like this... php and adodb include
excluded
for brevity...

-----
$db = &ADONewConnection('postgres7');
$db ->
Connect($db_string,$db_owner,$db_pw,$db_name);
$insert_id = $db->getone("select
nextval('public.customer_cust_id_seq')");

print 'The ID is ' . $insert_id;
-----

my sequence name is 'public.customer_cust_id_seq'
(found this in pgadmin3).

the last id number in my table is 65. when i use
nextval(), i get a result of 66 for $insert_id -
which
is the value that i would want to then perform and
insert.

however, when i use currval(), as recommended, i get
no result. i probably get an error, but i haven't
checked for that yet.

is it OK to use nextval() to get the next id value
in
the sequence before doing an insert? how come
currval() doesn't work.

thanks to all for any guidance here.

mitch and all, i noticed that if i keep refreshing my
page that the $insert_id keeps growing... 66 then 67
then 68 then 69.

i guess this makes sense, after all, the "next value"
is always and icnrement higher. this makes me a
little nervous, though.

i only want one value... the next id i should use to
perform an insert.

i'm hoping currval() does the trick, however, i'm
getting no result.

do i need to instruct the sequence to go to it last
value before calling currval()?

tia...

__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail

mitch and all, i noticed that if i keep refreshing
my
page that the $insert_id keeps growing... 66 then
67
then 68 then 69.

i guess this makes sense, after all, the "next
value"
is always and icnrement higher. this makes me a
little nervous, though.

i only want one value... the next id i should use
to
perform an insert.

i'm hoping currval() does the trick, however, i'm
getting no result.

do i need to instruct the sequence to go to it last
value before calling currval()?

tia...

another point of interest. now that i've been
refreshing my nextval() statement, my highest cust_id
value is 65, but nextval() keeps incrementing from
where it was before. reading through the manual, this
is designed in behavior.

it looks like i could reset the value using setval().

i also noticed that currval() returns the value of the
last nextval(). this infers that i must call
nextval() prior to being able to get currval(), yet
nextval() was not included in the original suggestion.

am i missing something here?

tia...

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Wed, Feb 09, 2005 at 08:10:57AM -0800, operationsengineer1@yahoo.com wrote:

however, when i use currval(), as recommended, i get
no result. i probably get an error, but i haven't
checked for that yet.

Error checking is A Good Thing.

When I suggested using currval() I wasn't necessarily recommending
it over nextval() (although I usually prefer it); I was pointing
out that nextval() isn't the only method and that currval() is
common practice. Not that common practice makes it a good idea,
but rather that it's common practice because it works if used
properly and it can be convenient. Depending on what you're doing,
you can use currval() in a subsequent INSERT or UPDATE without
having to fetch the ID into the client code at all:

INSERT INTO foo (name) VALUES ('some name');
INSERT INTO log (fooid) VALUES (currval('foo_fooid_seq'));

is it OK to use nextval() to get the next id value in
the sequence before doing an insert?

Yes. If you defined a SERIAL column, that's what the column's
default expression does.

how come currval() doesn't work.

You didn't show your currval() code so we can't say for sure why
it doesn't work. Did you call currval() *after* the INSERT? Did
you call currval() in the same connection as the INSERT? As has
been pointed out, if you use connection pooling and your call to
currval() ends up in a different connection than the INSERT, then
it won't work.

The following works for me:

$db = ADONewConnection($driver);
# $db->debug = true;
$db->Connect($connectstr);
$db->Execute("INSERT INTO foo (name) VALUES ('test')");
$id = $db->getone("SELECT currval('foo_id_seq')");
print "last insert id = $id<br>\n";

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

another point of interest. now that i've been
refreshing my nextval() statement, my highest
cust_id
value is 65, but nextval() keeps incrementing from
where it was before. reading through the manual,
this
is designed in behavior.

it looks like i could reset the value using
setval().

i also noticed that currval() returns the value of
the
last nextval(). this infers that i must call
nextval() prior to being able to get currval(), yet
nextval() was not included in the original
suggestion.

am i missing something here?

tia...

actually, michael fuhr addressed this issue in this
group on 1/31/05. not sure why it didn't register.

so, i should use nextval() if i use mitch's
methodology of calling the id first then performing
the insert.

giving that nextval increments on a refresh, is there
anything i need to worry about?

or is the worst case scenario a gap in unique ids?
this shouldn't matter as far as i can tell - as long
as the numbers are unique.

tia...

__________________________________
Do you Yahoo!?
Yahoo! Mail - now with 250MB free storage. Learn more.
http://info.mail.yahoo.com/mail_250

On Wed, Feb 09, 2005 at 08:53:18AM -0800, operationsengineer1@yahoo.com wrote:

so, i should use nextval() if i use mitch's
methodology of calling the id first then performing
the insert.

Right. You can call nextval() first and then explicitly insert the
value you obtained, or you can do the insert first and let the
serial column's default expression call nextval() automatically,
and you can then find out the value it used with a subsequent call
to currval().

giving that nextval increments on a refresh, is there
anything i need to worry about?

nextval() increments the sequence each time it's called. If you're
using the sequence values as keys, then that's what you need.
Presumably you'd only call nextval() when you're going to insert a
new record, so what's the concern?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/