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