RE: [PHP] Auto-increment value
Hello people,
Thanks for all your help. I guess I'll go with
$oid = pg_last_oid($result);
select id from table where oid = $oid;
If i'm not mistaken, there will no race issue here:
pg_last_oid($result) has already returned me the row
that 'I' have just inserted, based on $result, regardless whether there is
another
user doing an insert. I already have a reference to a row i just inserted.
On the other hand,
BEGIN
INSERT ... whatever
SELECT idno ORDER BY (idno) DESC LIMIT 1;
END;
will have some 'time' between the 'insert' and the 'select'.
There is no reference to a row based on $result.
There would still probably be a race issue here (just my opinion,
not a fact). But if there's anyone could explain the internals,
I'd be glad.
Thanks for all who helped.
Sincerely,
Faisal
-Original Message-
From: Leonid Mamtchenkov [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 5:35 AM
To: Faisal Abdullah
Cc: Marek Kilimajer; PHP
Subject: Re: [PHP] Auto-increment value
Dear Faisal Abdullah,
Once you wrote about "RE: [PHP] Auto-increment value":
> > There is a similar one for postgresql as well: pg_last_oid() .
>
> I tried that. It gives me 24807, instead of 5.
> Or is oid is a reference to something else, which would
> lead me to the '5' i'm looking for?
I'd bet that 24807 is your oid. Check the difference between
"SELECT * FROM table;" and "SELECT oid,* FROM table;".
HTH.
> -- snip snip --
>
> if($result = pg_exec($db, $sql))
> {
> $query = "success";
> echo "oid : " .pg_last_oid($result);
> }
>
> -- snip snip --
--
Best regards,
Leonid Mamtchenkov, RHCE
System Administrator
Francoudi & Stephanou Ltd.
BOFH: somebody was calculating pi on the server
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
Dear Faisal Abdullah,
Once you wrote about "RE: [PHP] Auto-increment value":
> > There is a similar one for postgresql as well: pg_last_oid() .
>
> I tried that. It gives me 24807, instead of 5.
> Or is oid is a reference to something else, which would
> lead me to the '5' i'm looking for?
I'd bet that 24807 is your oid. Check the difference between
"SELECT * FROM table;" and "SELECT oid,* FROM table;".
HTH.
> -- snip snip --
>
> if($result = pg_exec($db, $sql))
> {
> $query = "success";
> echo "oid : " .pg_last_oid($result);
> }
>
> -- snip snip --
--
Best regards,
Leonid Mamtchenkov, RHCE
System Administrator
Francoudi & Stephanou Ltd.
BOFH: somebody was calculating pi on the server
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
From: Marek Kilimajer <[EMAIL PROTECTED]> To: PHP <[EMAIL PROTECTED]> Subject: Re: [PHP] Auto-increment value You encounter a race condition bob parker wrote: >SELECT idno ORDER BY idno DESC LIMIT 1; >assuming idno is what gets auto incremented >bob To avoid the race it is necessary to do the whole thing in a transaction, for postgresql this works BEGIN; INSERT ... whatever SELECT ... the last idno END; The last idno is available to the user inside the transaction. I beleive the terminating ';' are not needed in php generated sql statements but they don't hurt either. bob -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
I tend to (if I can't use mysql_last_id) insert a unique key into the row, and pull the row out that matches that key, in order to get the ID... ensures that it was the right row... then you can delete the unique key. Justin on 18/09/02 10:27 PM, Jon Haworth ([EMAIL PROTECTED]) wrote: > Hi Bob, > >> SELECT idno ORDER BY idno DESC LIMIT 1; >> assuming idno is what gets auto incremented > > That's not the best idea - what happens if two users are inserting records > into the table at nearly-but-not-quite the same time? > > 1. Insert A goes through > 2. Insert B goes through > 3. LastID A returns the ID of B > 4. LastID B returns the ID of B > > Whoever did insert A would receive the wrong ID. > > Cheers > Jon -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
If there is a possibilty of that then BEGIN INSERT ... whatever SELECT idno ORDER BY (idno) DESC LIMIT 1; END; It works, I just tried it in postgresql, the last idno is visible to the user making the transaction, and will be unavailable to others. From [EMAIL PROTECTED] Wed Sep 18 22:34:13 2002 Envelope-to: bob@localhost Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm Precedence: bulk list-help: <mailto:[EMAIL PROTECTED]> list-unsubscribe: <mailto:[EMAIL PROTECTED]> list-post: <mailto:[EMAIL PROTECTED]> Delivered-To: mailing list [EMAIL PROTECTED] From: Jon Haworth <[EMAIL PROTECTED]> To: 'bob parker' <[EMAIL PROTECTED]>, [EMAIL PROTECTED] Date: Wed, 18 Sep 2002 13:27:56 +0100 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: text/plain; charset="ISO-8859-1" Subject: RE: [PHP] Auto-increment value X-UIDL: *ab"!\-5!!-$7!!nk~!! Hi Bob, > SELECT idno ORDER BY idno DESC LIMIT 1; > assuming idno is what gets auto incremented That's not the best idea - what happens if two users are inserting records into the table at nearly-but-not-quite the same time? 1. Insert A goes through 2. Insert B goes through 3. LastID A returns the ID of B 4. LastID B returns the ID of B Whoever did insert A would receive the wrong ID. Cheers Jon -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php bob -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
Hi Bob, > SELECT idno ORDER BY idno DESC LIMIT 1; > assuming idno is what gets auto incremented That's not the best idea - what happens if two users are inserting records into the table at nearly-but-not-quite the same time? 1. Insert A goes through 2. Insert B goes through 3. LastID A returns the ID of B 4. LastID B returns the ID of B Whoever did insert A would receive the wrong ID. Cheers Jon -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
You encounter a race condition bob parker wrote: >SELECT idno ORDER BY idno DESC LIMIT 1; >assuming idno is what gets auto incremented >bob > > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
SELECT idno ORDER BY idno DESC LIMIT 1; assuming idno is what gets auto incremented bob -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
> There is a similar one for postgresql as well: pg_last_oid() .
I tried that. It gives me 24807, instead of 5.
Or is oid is a reference to something else, which would
lead me to the '5' i'm looking for?
-- snip snip --
if($result = pg_exec($db, $sql))
{
$query = "success";
echo "oid : " .pg_last_oid($result);
}
-- snip snip --
Sincerely,
Faisal
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
Dear Faisal Abdullah,
Once you wrote about "RE: [PHP] Auto-increment value":
> Thanks for your reply,
> but I'm using postgresql.
> Mysql is irrelevant for me.
There is a similar one for postgresql as well: pg_last_oid() .
> -Original Message-
> From: Marek Kilimajer [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, September 18, 2002 7:41 PM
> To: PHP
> Subject: Re: [PHP] Auto-increment value
>
> MySQL has |LAST_INSERT_ID() function, try it ("SELECT ||LAST_INSERT_ID()")|
--
Best regards,
Leonid Mamtchenkov, RHCE
System Administrator
Francoudi & Stephanou Ltd.
BOFH: HTTPD Error 666 : BOFH was here
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
Thanks for your reply,
but I'm using postgresql.
Mysql is irrelevant for me.
-Original Message-
From: Marek Kilimajer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 7:41 PM
To: PHP
Subject: Re: [PHP] Auto-increment value
MySQL has |LAST_INSERT_ID() function, try it ("SELECT ||LAST_INSERT_ID()")|
Faisal Abdullah wrote:
>Would a "select last_value from sequence_name" do any good?
>Is there a possibility that I get a value from the outcome of
>a nextval by another session?
>
>Meaning, I do an insert 1, 2, 3, 4
>A 'select last_value from sequence_name' should give me '4'.
>
>But what if another user/session does another insert/nextval(),
>before my select statement finishes processing? Would I get
>a 5?
>
>Sincerely,
>Faisal
>
>-Original Message-
>From: Faisal Abdullah [mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, September 18, 2002 6:37 PM
>To: PHP
>Subject: RE: [PHP] Auto-increment value
>
>
>I tried that. It gives me 24807, instead of 5.
>Or is oid is a reference to something else, which would
>lead me to the '5' i'm looking for?
>
>-- snip snip --
>
>if($result = pg_exec($db, $sql))
>{
> $query = "success";
> echo "oid : " .pg_last_oid($result);
> }
>
>-- snip snip --
>
>Sincerely,
>Faisal
>
>-Original Message-
>From: Marek Kilimajer [mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, September 18, 2002 5:42 PM
>To: PHP
>Subject: Re: [PHP] Auto-increment value
>
>
>As he uses postgresql, he should use *pg_last_oid()*
>
>Scott Houseman wrote:
>
>
>
>>Hi there.
>>
>>You can use the function mysql_insert_id( [link id] ).
>>
>>regards
>>
>>Scott
>>
>>Faisal Abdullah wrote:
>>
>>
>>
>>>Hi people,
>>>
>>>I have a table with a column called ID (auto-increment).
>>>Is it possible to know the value of ID, right after inserting a row?
>>>I'm using postgresql.
>>>
>>>Thanks.
>>>
>>>Sincerely,
>>>Faisal
>>>
>>>__
>>>
>>>
>>>
>>>
>>
>>
>
>
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
MySQL has |LAST_INSERT_ID() function, try it ("SELECT ||LAST_INSERT_ID()")|
Faisal Abdullah wrote:
>Would a "select last_value from sequence_name" do any good?
>Is there a possibility that I get a value from the outcome of
>a nextval by another session?
>
>Meaning, I do an insert 1, 2, 3, 4
>A 'select last_value from sequence_name' should give me '4'.
>
>But what if another user/session does another insert/nextval(),
>before my select statement finishes processing? Would I get
>a 5?
>
>Sincerely,
>Faisal
>
>-Original Message-
>From: Faisal Abdullah [mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, September 18, 2002 6:37 PM
>To: PHP
>Subject: RE: [PHP] Auto-increment value
>
>
>I tried that. It gives me 24807, instead of 5.
>Or is oid is a reference to something else, which would
>lead me to the '5' i'm looking for?
>
>-- snip snip --
>
>if($result = pg_exec($db, $sql))
>{
> $query = "success";
> echo "oid : " .pg_last_oid($result);
> }
>
>-- snip snip --
>
>Sincerely,
>Faisal
>
>-Original Message-
>From: Marek Kilimajer [mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, September 18, 2002 5:42 PM
>To: PHP
>Subject: Re: [PHP] Auto-increment value
>
>
>As he uses postgresql, he should use *pg_last_oid()*
>
>Scott Houseman wrote:
>
>
>
>>Hi there.
>>
>>You can use the function mysql_insert_id( [link id] ).
>>
>>regards
>>
>>Scott
>>
>>Faisal Abdullah wrote:
>>
>>
>>
>>>Hi people,
>>>
>>>I have a table with a column called ID (auto-increment).
>>>Is it possible to know the value of ID, right after inserting a row?
>>>I'm using postgresql.
>>>
>>>Thanks.
>>>
>>>Sincerely,
>>>Faisal
>>>
>>>__
>>>
>>>
>>>
>>>
>>
>>
>
>
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
Would a "select last_value from sequence_name" do any good?
Is there a possibility that I get a value from the outcome of
a nextval by another session?
Meaning, I do an insert 1, 2, 3, 4
A 'select last_value from sequence_name' should give me '4'.
But what if another user/session does another insert/nextval(),
before my select statement finishes processing? Would I get
a 5?
Sincerely,
Faisal
-Original Message-
From: Faisal Abdullah [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 6:37 PM
To: PHP
Subject: RE: [PHP] Auto-increment value
I tried that. It gives me 24807, instead of 5.
Or is oid is a reference to something else, which would
lead me to the '5' i'm looking for?
-- snip snip --
if($result = pg_exec($db, $sql))
{
$query = "success";
echo "oid : " .pg_last_oid($result);
}
-- snip snip --
Sincerely,
Faisal
-Original Message-
From: Marek Kilimajer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 5:42 PM
To: PHP
Subject: Re: [PHP] Auto-increment value
As he uses postgresql, he should use *pg_last_oid()*
Scott Houseman wrote:
> Hi there.
>
> You can use the function mysql_insert_id( [link id] ).
>
> regards
>
> Scott
>
> Faisal Abdullah wrote:
>
>> Hi people,
>>
>> I have a table with a column called ID (auto-increment).
>> Is it possible to know the value of ID, right after inserting a row?
>> I'm using postgresql.
>>
>> Thanks.
>>
>> Sincerely,
>> Faisal
>>
>> __
>>
>>
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
I tried that. It gives me 24807, instead of 5.
Or is oid is a reference to something else, which would
lead me to the '5' i'm looking for?
-- snip snip --
if($result = pg_exec($db, $sql))
{
$query = "success";
echo "oid : " .pg_last_oid($result);
}
-- snip snip --
Sincerely,
Faisal
-Original Message-
From: Marek Kilimajer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 5:42 PM
To: PHP
Subject: Re: [PHP] Auto-increment value
As he uses postgresql, he should use *pg_last_oid()*
Scott Houseman wrote:
> Hi there.
>
> You can use the function mysql_insert_id( [link id] ).
>
> regards
>
> Scott
>
> Faisal Abdullah wrote:
>
>> Hi people,
>>
>> I have a table with a column called ID (auto-increment).
>> Is it possible to know the value of ID, right after inserting a row?
>> I'm using postgresql.
>>
>> Thanks.
>>
>> Sincerely,
>> Faisal
>>
>> __
>>
>>
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
not on postgreSQL :) Justin on 18/09/02 7:24 PM, Scott Houseman ([EMAIL PROTECTED]) wrote: > Hi there. > > You can use the function mysql_insert_id( [link id] ). > > regards > > Scott > > Faisal Abdullah wrote: >> Hi people, >> >> I have a table with a column called ID (auto-increment). >> Is it possible to know the value of ID, right after inserting >> a row? >> >> I'm using postgresql. >> >> Thanks. >> >> Sincerely, >> Faisal >> >> __ >> >> > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
As he uses postgresql, he should use *pg_last_oid()* Scott Houseman wrote: > Hi there. > > You can use the function mysql_insert_id( [link id] ). > > regards > > Scott > > Faisal Abdullah wrote: > >> Hi people, >> >> I have a table with a column called ID (auto-increment). >> Is it possible to know the value of ID, right after inserting a row? >> I'm using postgresql. >> >> Thanks. >> >> Sincerely, >> Faisal >> >> __ >> >> > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
Scott Houseman wrote: > Hi there. > > You can use the function mysql_insert_id( [link id] ). > >> I'm using postgresql. Not if you're using PostGreSQL Grtz Erwin -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
Hi there. You can use the function mysql_insert_id( [link id] ). regards Scott Faisal Abdullah wrote: > Hi people, > > I have a table with a column called ID (auto-increment). > Is it possible to know the value of ID, right after inserting > a row? > > I'm using postgresql. > > Thanks. > > Sincerely, > Faisal > > __ > > -- //// // Scott Houseman // // Jam Warehouse http://www.jamwarehouse.com/ // // Smart Business Innovation // // +27 21 4477440 / +27 82 4918021// //// -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Auto-increment value
Hi people, I have a table with a column called ID (auto-increment). Is it possible to know the value of ID, right after inserting a row? I'm using postgresql. Thanks. Sincerely, Faisal __ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
