Re: DBD::Pg - insert_id
Not sure if postgres is similar to oracle, in this respect, but in Oracle,
it's illegal to do a currval call until nextval has been called at least
once on a sequence. Something that may come to bite you...
Take care,
Mark.
On Mon, 23 Jun 2003, Toby Corkindale wrote:
Date: Mon, 23 Jun 2003 08:50:41 -0700
From: Toby Corkindale [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: DBD::Pg - insert_id
On Fri, Jun 20, 2003 at 10:53:47AM +0100, Chisel Wright wrote:
One piece of functionality I can't find in the postgres database
interface is the equivalent of:
$sth-{'mysql_insertid'}
It would just be really nice to be able to do something like:
$sql = q[INSERT INTO foo (foofield1) VALUES (?)];
$sth = $dbh-prepare($sql);
$sth-execute($barbaz);
return $sth-{'pg_insertid'};
Can anyone offer some clues?
as above, except then
return last_inserted_id($tablename);
Note that this is quite safe and the value returned is the current value for
YOUR session only; so no need to worry about someone else inserting something
or other such race conditions. :)
# Returns the last ID resulting from an INSERT command
sub last_inserted_id
{
my $self = shift;
my $table = shift; #should be safe, but check anyway
if ($table =~ /(\w[\w\d\_]+)/) {
$table = $1;
}
else {
die(LBHF.pm/last_inserted_id: Invalid table name: $table\n);
}
my $query = $self-{db}-prepare(SELECT currval(' . $table . _id_seq'));
$query-execute
or mydie($self, 'SQL Error occured:' . $self-{db}-errstr);
return($query-fetchrow_arrayref-[0]);
}
hope this helps,
Toby
--
Turning and turning in the widening gyre
The falcon cannot hear the falconer;
Things fall apart, the centre cannot hold;
Mere anarchy is loosed upon the world.
RE: DBD::Pg - insert_id
Postgres uses 'oid' as a reference to the last row inserted/updated, which
would compare to MySQLs 'insertid', hence use:
$oid_status = $sth-{pg_oid_status}
from DBD::Pg' man:
=
pg_size
Returns a reference to an array of integer values for each column. The
integer shows the storage (not display) size of the column in bytes.
Variable length columns are indicated by -1.
pg_type
Returns a reference to an array of strings for each column. The string shows
the name of the data type.
pg_oid_status
Returns the OID of the last INSERT command.
pg_cmd_status
Returns the name of the last command type. Possible types are: INSERT,
DELETE, UPDATE, SELECT.
===
-Original Message-
From: Mark Morgan [mailto:[EMAIL PROTECTED]
Sent: 25 June 2003 18:13
To: [EMAIL PROTECTED]
Subject: Re: DBD::Pg - insert_id
Not sure if postgres is similar to oracle, in this respect, but in Oracle,
it's illegal to do a currval call until nextval has been called at least
once on a sequence. Something that may come to bite you...
Take care,
Mark.
On Mon, 23 Jun 2003, Toby Corkindale wrote:
Date: Mon, 23 Jun 2003 08:50:41 -0700
From: Toby Corkindale [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: DBD::Pg - insert_id
On Fri, Jun 20, 2003 at 10:53:47AM +0100, Chisel Wright wrote:
One piece of functionality I can't find in the postgres database
interface is the equivalent of:
$sth-{'mysql_insertid'}
It would just be really nice to be able to do something like:
$sql = q[INSERT INTO foo (foofield1) VALUES (?)];
$sth = $dbh-prepare($sql);
$sth-execute($barbaz);
return $sth-{'pg_insertid'};
Can anyone offer some clues?
as above, except then
return last_inserted_id($tablename);
Note that this is quite safe and the value returned is the current value
for
YOUR session only; so no need to worry about someone else inserting
something
or other such race conditions. :)
# Returns the last ID resulting from an INSERT command
sub last_inserted_id
{
my $self = shift;
my $table = shift; #should be safe, but check anyway
if ($table =~ /(\w[\w\d\_]+)/) {
$table = $1;
}
else {
die(LBHF.pm/last_inserted_id: Invalid table name: $table\n);
}
my $query = $self-{db}-prepare(SELECT currval(' . $table .
_id_seq'));$query-execute
or mydie($self, 'SQL Error occured:' . $self-{db}-errstr);
return($query-fetchrow_arrayref-[0]);
}
hope this helps,
Toby
--
Turning and turning in the widening gyre
The falcon cannot hear the falconer;
Things fall apart, the centre cannot hold;
Mere anarchy is loosed upon the world.
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
Re: DBD::Pg - insert_id
On Wed, Jun 25, 2003 at 06:13:27PM +0100, Mark Morgan wrote: Not sure if postgres is similar to oracle, in this respect, but in Oracle, it's illegal to do a currval call until nextval has been called at least once on a sequence. Something that may come to bite you... That is the case; I'm aware of it. Not quite sure where it would be a problem though? (Assuming better error checking and handling than in the quick examples below :) cheers, Toby
Re: DBD::Pg - insert_id
On Fri, Jun 20, 2003 at 10:53:47AM +0100, Chisel Wright wrote:
One piece of functionality I can't find in the postgres database
interface is the equivalent of:
$sth-{'mysql_insertid'}
It would just be really nice to be able to do something like:
$sql = q[INSERT INTO foo (foofield1) VALUES (?)];
$sth = $dbh-prepare($sql);
$sth-execute($barbaz);
return $sth-{'pg_insertid'};
Can anyone offer some clues?
as above, except then
return last_inserted_id($tablename);
Note that this is quite safe and the value returned is the current value for
YOUR session only; so no need to worry about someone else inserting something
or other such race conditions. :)
# Returns the last ID resulting from an INSERT command
sub last_inserted_id
{
my $self = shift;
my $table = shift; #should be safe, but check anyway
if ($table =~ /(\w[\w\d\_]+)/) {
$table = $1;
}
else {
die(LBHF.pm/last_inserted_id: Invalid table name: $table\n);
}
my $query = $self-{db}-prepare(SELECT currval(' . $table . _id_seq'));
$query-execute
or mydie($self, 'SQL Error occured:' . $self-{db}-errstr);
return($query-fetchrow_arrayref-[0]);
}
hope this helps,
Toby
--
Turning and turning in the widening gyre
The falcon cannot hear the falconer;
Things fall apart, the centre cannot hold;
Mere anarchy is loosed upon the world.
Re: DBD::Pg - insert_id
Toby Corkindale wrote:
On Fri, Jun 20, 2003 at 10:53:47AM +0100, Chisel Wright wrote:
One piece of functionality I can't find in the postgres database
interface is the equivalent of:
$sth-{'mysql_insertid'}
It would just be really nice to be able to do something like:
$sql = q[INSERT INTO foo (foofield1) VALUES (?)];
$sth = $dbh-prepare($sql);
$sth-execute($barbaz);
return $sth-{'pg_insertid'};
Can anyone offer some clues?
as above, except then
return last_inserted_id($tablename);
Note that this is quite safe and the value returned is the current value for
YOUR session only; so no need to worry about someone else inserting something
or other such race conditions. :)
Doesn't that depend on whether you're in a transaction or not? I would
have thought that you're liable to have it updated if you've got
AutoCommit turned on.
I don't know this, I'm just making assumptions about how sequences work. :-)
# Returns the last ID resulting from an INSERT command
sub last_inserted_id
{
my $self = shift;
my $table = shift; #should be safe, but check anyway
if ($table =~ /(\w[\w\d\_]+)/) {
$table = $1;
}
else {
die(LBHF.pm/last_inserted_id: Invalid table name: $table\n);
}
my $query = $self-{db}-prepare(SELECT currval(' . $table . _id_seq'));
$query-execute
Hmmm, big assumptions about sequence names here. I merely point this
out because I'm continually getting annoyed at postgres for truncating
min to 32 chars. This is because I include the table name in my primary
key column name though...
or mydie($self, 'SQL Error occured:' . $self-{db}-errstr);
return($query-fetchrow_arrayref-[0]);
}
hope this helps,
-Dom
--
| Semantico: creators of major online resources |
| URL: http://www.semantico.com/ |
| Tel: +44 (1273) 72 |
| Address: 33 Bond St., Brighton, Sussex, BN1 1RD, UK. |
Re: DBD::Pg - insert_id
On Mon, Jun 23, 2003 at 05:16:51PM +0100, Dominic Mitchell wrote: Toby Corkindale wrote: Note that this is quite safe and the value returned is the current value for YOUR session only; so no need to worry about someone else inserting something or other such race conditions. :) Doesn't that depend on whether you're in a transaction or not? I would have thought that you're liable to have it updated if you've got AutoCommit turned on. No. It is per _session_, not per transaction. It would be fairly useless otherwise. Roger
Re: DBD::Pg - insert_id
On Mon, Jun 23, 2003 at 05:16:51PM +0100, Dominic Mitchell wrote:
Note that this is quite safe and the value returned is the current value
for YOUR session only; so no need to worry about someone else inserting
something or other such race conditions. :)
Doesn't that depend on whether you're in a transaction or not? I would have
thought that you're liable to have it updated if you've got AutoCommit
turned on.
No, it's still correct even outside of sequences. the current value returned
is always the value for your session.
I don't know this, I'm just making assumptions about how sequences work. :-)
tut tut ;)
# Returns the last ID resulting from an INSERT command
sub last_inserted_id
{
my $self = shift;
my $table = shift; #should be safe, but check anyway
if ($table =~ /(\w[\w\d\_]+)/) {
$table = $1;
}
else {
die(LBHF.pm/last_inserted_id: Invalid table name: $table\n);
}
my $query = $self-{db}-prepare(SELECT currval(' . $table .
_id_seq'));$query-execute
Hmmm, big assumptions about sequence names here. I merely point this
out because I'm continually getting annoyed at postgres for truncating
min to 32 chars. This is because I include the table name in my primary
key column name though...
True, but (currently) that is the way Postgresql generates sequence names from
serials. Using this assumption works well enough for my internal code.
tjc
Re: DBD::Pg - insert_id
On Fri, Jun 20, 2003 at 01:22:43PM +0100, Roger Burton West wrote: Not if the program has been written by someone vaguely competent. Big if... -- Lusercop.net - LARTing Lusers everywhere since 2002
DBD::Pg - insert_id
Sorry to pollute the list with a non-Buffy post, I'm a bit stumped.
I regularly work with mysql and postgres databases.
I use the DBD::mysql and DBD::Pg modules for this work, via DBI.
One piece of functionality I can't find in the postgres database
interface is the equivalent of:
$sth-{'mysql_insertid'}
I've searched the web, and as many postgres documents as I can find, but
I'm still unsure how to find the id of an inserted row when using
postgres.
I'm aware of:
$sth-{'pg_oid_status'}
but I'm not sure how to get from an OID to an insertid.
It would just be really nice to be able to do something like:
$sql = q[INSERT INTO foo (foofield1) VALUES (?)];
$sth = $dbh-prepare($sql);
$sth-execute($barbaz);
return $sth-{'pg_insertid'};
The only way I can think of doing this is to use something along these
lines:
...
$oid = $sth-{'pg_oid_status'}
($tablename) = ($sql =~ m{^insert\s+into\s+(.+?)\s+\(/}i);
$sth = $dbh-prepare(SELECT id FROM $tablename WHERE oid = $oid);
...
Which seems really groady, and prone to breakage.
Can anyone offer some clues?
--
Chisel Wright
Re: DBD::Pg - insert_id
Chisel Wright wrote:
The only way I can think of doing this is to use something along these
lines:
...
$oid = $sth-{'pg_oid_status'}
($tablename) = ($sql =~ m{^insert\s+into\s+(.+?)\s+\(/}i);
$sth = $dbh-prepare(SELECT id FROM $tablename WHERE oid = $oid);
...
Which seems really groady, and prone to breakage.
Can anyone offer some clues?
You probably want to look at postgres sequences. If you create a table
with the primary key as SERIAL, it will create a sequence for you.
It'll be autogenerated based on the name of the table and the primary
column.
Then, you can do select nextval(sequence), use that to insert a new
row, and you've got a handy copy of the primary key. You'll want to do
this in a transaction of course.
You might also want to look at
http://www.postgresql.org/docs/faqs/FAQ.html#4.15, which describes
this area some more.
-Dom
--
| Semantico: creators of major online resources |
| URL: http://www.semantico.com/ |
| Tel: +44 (1273) 72 |
| Address: 33 Bond St., Brighton, Sussex, BN1 1RD, UK. |
Re: DBD::Pg - insert_id
I haven't used postgres for a while, but I used to use something like
select(next_val(my_sequence)) and then insert this in or something along
those lines. If you find the sp I mean, which grabs the next id in the
sequence id, it'll work for you..
It's very annoying, that there doesn't appear to be a standard for this.
In Sybase I now have to do a select(@@identity). Which I had to look up
the first time I used it. Makes me wonder how platform independent code
can be.
R
Chisel Wright said:
Sorry to pollute the list with a non-Buffy post, I'm a bit stumped.
I regularly work with mysql and postgres databases.
I use the DBD::mysql and DBD::Pg modules for this work, via DBI.
One piece of functionality I can't find in the postgres database
interface is the equivalent of:
$sth-{'mysql_insertid'}
I've searched the web, and as many postgres documents as I can find, but
I'm still unsure how to find the id of an inserted row when using
postgres.
I'm aware of:
$sth-{'pg_oid_status'}
but I'm not sure how to get from an OID to an insertid.
It would just be really nice to be able to do something like:
$sql = q[INSERT INTO foo (foofield1) VALUES (?)];
$sth = $dbh-prepare($sql);
$sth-execute($barbaz);
return $sth-{'pg_insertid'};
The only way I can think of doing this is to use something along these
lines:
...
$oid = $sth-{'pg_oid_status'}
($tablename) = ($sql =~ m{^insert\s+into\s+(.+?)\s+\(/}i);
$sth = $dbh-prepare(SELECT id FROM $tablename WHERE oid = $oid);
...
Which seems really groady, and prone to breakage.
Can anyone offer some clues?
--
Chisel Wright
Re: DBD::Pg - insert_id
On Fri, 20 Jun 2003, Raf wrote: I haven't used postgres for a while, but I used to use something like select(next_val(my_sequence)) and then insert this in or something along those lines. If you find the sp I mean, which grabs the next id in the sequence id, it'll work for you.. How safe is this in environments where lots of separate processes, using separate connections to the database, are updating the same table? With the MySQL insert_id method you get the result of a completed insertion so you know for certain what it is. If you grab the number in advance isn't there a risk that another process will insert a record first and get the sequence number you're expecting? Jason Clifford -- UKFSN.ORG Finance Free Software while you surf the 'net http://www.ukfsn.org/ Sign up now
Re: DBD::Pg - insert_id
On Fri, Jun 20, 2003 at 01:12:39PM +0100, Jason Clifford wrote: On Fri, 20 Jun 2003, Raf wrote: I haven't used postgres for a while, but I used to use something like select(next_val(my_sequence)) and then insert this in or something along those lines. If you find the sp I mean, which grabs the next id in the sequence id, it'll work for you.. How safe is this in environments where lots of separate processes, using separate connections to the database, are updating the same table? That's the point of sequences. They are guaranteed to work. The procedore of getting a new number and incrementing the counter for the next process tha wants a new number is atomic. If you grab the number in advance isn't there a risk that another process will insert a record first and get the sequence number you're expecting? Not if the program has been written by someone vaguely competent. Roger
Re: DBD::Pg - insert_id
Jason Clifford wrote: On Fri, 20 Jun 2003, Raf wrote: I haven't used postgres for a while, but I used to use something like select(next_val(my_sequence)) and then insert this in or something along those lines. If you find the sp I mean, which grabs the next id in the sequence id, it'll work for you.. [How safe is this ... race conditions, etc] Much of this seems to be addressed in the FAQ url that Dominic posted. http://www.postgresql.org/docs/faqs/FAQ.html#4.15 (I only skimmed it, mind). These things can be done atomically - a database that made it difficult to do stuff atomically would be really really really unhelpful. I'm not sure it was entirely helpful of Postgres to go Hmm, well Informix and MySQL and probably Oracle all do it the same way - let's only provide that facility obscurely, if at all. Grr. -- ti'
Re: DBD::Pg - insert_id
On Fri, 20 Jun 2003, Roger Burton West wrote: How safe is this in environments where lots of separate processes, using separate connections to the database, are updating the same table? That's the point of sequences. They are guaranteed to work. The procedore of getting a new number and incrementing the counter for the next process tha wants a new number is atomic. Wouldn't that result is latency serving application database requests in a busy environment? In the case where an application is written poorly so that it grabs the sequence number and spends time on $OTHER_TASK before performing the actual insert other database clients will suffer delays wont they or does the method used allow breaks in the sequence? If you grab the number in advance isn't there a risk that another process will insert a record first and get the sequence number you're expecting? Not if the program has been written by someone vaguely competent. There is a little voice in my head shouting about how many programs I see are written by the incompetent. Jason Clifford -- UKFSN.ORG Finance Free Software while you surf the 'net http://www.ukfsn.org/ Sign up now
Re: DBD::Pg - insert_id
On Fri, 20 Jun 2003, Jason Clifford wrote: In the case where an application is written poorly so that it grabs the sequence number and spends time on $OTHER_TASK before performing the actual insert other database clients will suffer delays wont they or does the method used allow breaks in the sequence? I'll get my coat - a quick check of the FAQ informs me that sequence breaks are indeed the solution used for this. For some applications that would be a bad thing but not that many I suppose. Jason Clifford -- UKFSN.ORG Finance Free Software while you surf the 'net http://www.ukfsn.org/ Sign up now
Re: DBD::Pg - insert_id
Jason Clifford wrote: On Fri, 20 Jun 2003, Raf wrote: How safe is this in environments where lots of separate processes, using separate connections to the database, are updating the same table? AFAIK, this is the safest method of updating the same table using different processes. The sequence never offers the same value for two different requests, even when they're parallel. With the MySQL insert_id method you get the result of a completed insertion so you know for certain what it is. MySQL don't implement transactions. When you commit your transaction, you're sure that everything is fine. And then you could get the current value from the sequence using SELECT sequence_name.currval (...) or something like this. I don't remember exactly the attribute name, please take a look at your manual. If you grab the number in advance isn't there a risk that another process will insert a record first and get the sequence number you're expecting? Never. PG handles this concurrency graciously for you. Have a nice day. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Luis Campos de Carvalho Analista de sistemas Intergal Sistemas http://www.integral.com.br =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Re: DBD::Pg - insert_id
On Friday, June 20, 2003 13:25 +0100 Tim Sweetman [EMAIL PROTECTED] wrote: I'm not sure it was entirely helpful of Postgres to go Hmm, well Informix and MySQL and probably Oracle all do it the same way - let's only provide that facility obscurely, if at all. Grr. Postgres can do SEQUENCEs in just the same way as Orrible. It might have some other way of doing it as well, but I've copied code directly from an Oracle system to a PG system, and it Just Worked. -- David Cantrell
Re: DBD::Pg - insert_id
On Fri, 20 Jun 2003, Luis Campos de Carvalho wrote: AFAIK, this is the safest method of updating the same table using different processes. The sequence never offers the same value for two different requests, even when they're parallel. Unless you have multiple servers accepting inserts to the same database with a unique record id (a terrible idea, I know) parallel isn't a problem as the database should handle that (MySQL certainly does). With the MySQL insert_id method you get the result of a completed insertion so you know for certain what it is. MySQL don't implement transactions. Yes it does. As others keep pointing out it's just a matter of table type with current version of MySQL. When you commit your transaction, you're sure that everything is fine. And then you could get the current value from the sequence using SELECT sequence_name.currval (...) or something like this. I don't remember exactly the attribute name, please take a look at your manual. I know it just seems an expensive way of doing it. Jason Clifford -- UKFSN.ORG Finance Free Software while you surf the 'net http://www.ukfsn.org/ Sign up now
Re: DBD::Pg - insert_id
Jason Clifford said: On Fri, 20 Jun 2003, Raf wrote: I haven't used postgres for a while, but I used to use something like select(next_val(my_sequence)) and then insert this in or something How safe is this in environments where lots of separate processes, using separate connections to the database, are updating the same table? Very. If you grab the number in advance isn't there a risk that another process will insert a record first and get the sequence number you're expecting? Whether or not you do it in a single transaction, the next_val, actually increments the sequence on your query. Thus that id is unique and only ever used or not used by you. The id then is not reliable for determining insert order, but it shouldn't be in the frist place. R.
Re: DBD::Pg - insert_id
Luis Campos de Carvalho said: Jason Clifford wrote: On Fri, 20 Jun 2003, Raf wrote: ^^^ Lies! I did not write that! It's just poor indentation. How safe is this in environments where lots of separate processes, using separate connections to the database, are updating the same table? If you grab the number in advance isn't there a risk that another process will insert a record first and get the sequence number you're expecting? Never. PG handles this concurrency graciously for you. Have a nice day. I 'know' that. I feel misquoted. :)
Re: DBD::Pg - insert_id
Raf sent the following bits through the ether: It's very annoying, that there doesn't appear to be a standard for this. FWIW it's been brought up on dbi-dev recently and DBI may wrap all the methods someday. Can I be any more vague? Possibly. Leon -- Leon Brocard.http://www.astray.com/ scribot.http://www.scribot.com/ ... Sucks Syntax
Re: DBD::Pg - insert_id
Raf said: Luis Campos de Carvalho said: Jason Clifford wrote: On Fri, 20 Jun 2003, Raf wrote: ^^^ Lies! I did not write that! It's just poor indentation. How safe is this in environments where lots of separate processes, using separate connections to the database, are updating the same table? If you grab the number in advance isn't there a risk that another process will insert a record first and get the sequence number you're expecting? Never. PG handles this concurrency graciously for you. Have a nice day. I 'know' that. I feel misquoted. :) But there is nothing in the above message that says you did write it. We can tell that Jason wrote it. The problem, if there is one, is that Luis left in the attribution line without quoting you at all. -- Paul Johnson - [EMAIL PROTECTED] http://www.pjcj.net
