Thread: Nextval
I try to do the following:
Fetch a SERIAL field (ie. record_id) and get the
highest value currently stored (ie. 1000), increment
the value by 1 and store a record that will have the
same value (1001) in record_id. So there must be a way
with concurrency control.
I went through the manuals and found the solution with
NEXTVAL('serial'), but you have to create a sequence
for that, and I don't know if it is the right way to
do for what I want.
It is my first PHP project involving a Database, so
it's a newbie question. Have mercy...
Thanks
__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com
On Thu, 11 Oct 2001, Zavier Sheran wrote:
> I try to do the following:
>
> Fetch a SERIAL field (ie. record_id) and get the
> highest value currently stored (ie. 1000), increment
> the value by 1 and store a record that will have the
> same value (1001) in record_id. So there must be a way
> with concurrency control.
>
> I went through the manuals and found the solution with
> NEXTVAL('serial'), but you have to create a sequence
> for that, and I don't know if it is the right way to
> do for what I want.
>
> It is my first PHP project involving a Database, so
> it's a newbie question. Have mercy...
>
> Thanks
>
marco=# create table test_serial ( id serial );
NOTICE: CREATE TABLE will create implicit sequence 'test_serial_id_seq' for SERIAL column 'test_serial.id'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_serial_id_key' for table 'test_serial'
The serial type automagically creates both a sequence and an index:
the sequence is named <table>_<column>_seq and the index
<table>_<column>_key.
You can use nextval() on the sequence if you like, as in:
insert into test_serial values ( nextval('test_serial_id_seq') );
or have the column default to that so that you don't even bother on
inserts.
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Hello,
If I understand right what you wrote,
SERIAL is what you need. This object is intended to
handle concurrency as well.
----- Original Message -----
From: "Zavier Sheran" <zsheran@yahoo.com>
To: <pgsql-php@postgresql.org>
Sent: Friday, October 12, 2001 3:55 AM
Subject: [PHP] Nextval
> I try to do the following:
>
> Fetch a SERIAL field (ie. record_id) and get the
> highest value currently stored (ie. 1000), increment
> the value by 1 and store a record that will have the
> same value (1001) in record_id. So there must be a way
> with concurrency control.
>
> I went through the manuals and found the solution with
> NEXTVAL('serial'), but you have to create a sequence
> for that, and I don't know if it is the right way to
> do for what I want.
>
> It is my first PHP project involving a Database, so
> it's a newbie question. Have mercy...
>
> Thanks
>
> __________________________________________________
> Do You Yahoo!?
> Make a great connection at Yahoo! Personals.
> http://personals.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
Zavier,
In the future, cross-post this sort of request to pgsql-novice. More of
us DB Engine / Query Parser developers subscribe to that list than this
one.
> Fetch a SERIAL field (ie. record_id) and get the
> highest value currently stored (ie. 1000), increment
> the value by 1 and store a record that will have the
> same value (1001) in record_id. So there must be a way
> with concurrency control.
>
> I went through the manuals and found the solution with
> NEXTVAL('serial'), but you have to create a sequence
> for that, and I don't know if it is the right way to
> do for what I want.
Well, that depends on what you want to do, don't it? How about
describing what you're trying to accomplish rather than making us guess?
:-)
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Ummm... no. Cross posting is a BAD thing. Post to one list and if you don't get an answer there, then post to another list. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Josh Berkus" <josh@agliodbs.com> To: "Zavier Sheran" <zsheran@yahoo.com>; <pgsql-php@postgresql.org> Sent: Friday, October 12, 2001 12:49 PM Subject: Re: [PHP] Nextval > Zavier, > > In the future, cross-post this sort of request to pgsql-novice. More of > us DB Engine / Query Parser developers subscribe to that list than this > one. > > > Fetch a SERIAL field (ie. record_id) and get the > > highest value currently stored (ie. 1000), increment > > the value by 1 and store a record that will have the > > same value (1001) in record_id. So there must be a way > > with concurrency control. > > > > I went through the manuals and found the solution with > > NEXTVAL('serial'), but you have to create a sequence > > for that, and I don't know if it is the right way to > > do for what I want. > > Well, that depends on what you want to do, don't it? How about > describing what you're trying to accomplish rather than making us guess? > :-) > > -Josh
On Thu, 11 Oct 2001, Zavier Sheran wrote:
> I try to do the following:
>
> Fetch a SERIAL field (ie. record_id) and get the
> highest value currently stored (ie. 1000), increment
> the value by 1 and store a record that will have the
> same value (1001) in record_id. So there must be a way
> with concurrency control.
>
> I went through the manuals and found the solution with
> NEXTVAL('serial'), but you have to create a sequence
> for that, and I don't know if it is the right way to
> do for what I want.
>
> It is my first PHP project involving a Database, so
> it's a newbie question. Have mercy...
>
> Thanks
>
marco=# create table test_serial ( id serial );
NOTICE: CREATE TABLE will create implicit sequence 'test_serial_id_seq' for SERIAL column 'test_serial.id'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_serial_id_key' for table 'test_serial'
The serial type automagically creates both a sequence and an index:
the sequence is named <table>_<column>_seq and the index
<table>_<column>_key.
You can use nextval() on the sequence if you like, as in:
insert into test_serial values ( nextval('test_serial_id_seq') );
or have the column default to that so that you don't even bother on
inserts.
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Everybody appears to be using nextval() to get next val but there is issues
with people accessing same record at the same time etc, etc...
obviously if you are using serials and you are just creating a new record in
the same table you just let postgres add the next value by default but if
for example you need the id of a newly created record to insert into a new
order record what I do is:
start a transaction
do an insert into customers
use pg_getlastoid() to get last oid and write a little function to do a
select on that oid and return the corresponding id which I insert into the
order table
cofim transaction
as far as I know thats the most reliable way because there wont be problems
with concurrent users plus the whole thing is safeguarded by the transaction
anyway
seems to work nicely
regards, Peter
----- Original Message -----
From: "Marco Colombo" <marco@esi.it>
To: <beloshapka@mnogo.ru>
Cc: <pgsql-php@postgresql.org>
Sent: Friday, October 12, 2001 10:56 PM
Subject: Re: [PHP] Nextval
> On Thu, 11 Oct 2001, Zavier Sheran wrote:
>
> > I try to do the following:
> >
> > Fetch a SERIAL field (ie. record_id) and get the
> > highest value currently stored (ie. 1000), increment
> > the value by 1 and store a record that will have the
> > same value (1001) in record_id. So there must be a way
> > with concurrency control.
> >
> > I went through the manuals and found the solution with
> > NEXTVAL('serial'), but you have to create a sequence
> > for that, and I don't know if it is the right way to
> > do for what I want.
> >
> > It is my first PHP project involving a Database, so
> > it's a newbie question. Have mercy...
> >
> > Thanks
> >
>
> marco=# create table test_serial ( id serial );
> NOTICE: CREATE TABLE will create implicit sequence 'test_serial_id_seq'
for SERIAL column 'test_serial.id'
> NOTICE: CREATE TABLE/UNIQUE will create implicit index
'test_serial_id_key' for table 'test_serial'
>
> The serial type automagically creates both a sequence and an index:
> the sequence is named <table>_<column>_seq and the index
> <table>_<column>_key.
>
> You can use nextval() on the sequence if you like, as in:
> insert into test_serial values ( nextval('test_serial_id_seq') );
>
> or have the column default to that so that you don't even bother on
> inserts.
>
> .TM.
> --
> ____/ ____/ /
> / / / Marco Colombo
> ___/ ___ / / Technical Manager
> / / / ESI s.r.l.
> _____/ _____/ _/ Colombo@ESI.it
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
On Fri, 2001-11-16 at 18:37, Peter wrote:
> Everybody appears to be using nextval() to get next val but there is issues
> with people accessing same record at the same time etc, etc...
> obviously if you are using serials and you are just creating a new record in
> the same table you just let postgres add the next value by default but if
> for example you need the id of a newly created record to insert into a new
> order record what I do is:
> start a transaction
> do an insert into customers
> use pg_getlastoid() to get last oid and write a little function to do a
> select on that oid and return the corresponding id which I insert into the
> order table
> cofim transaction
> as far as I know thats the most reliable way because there wont be problems
> with concurrent users plus the whole thing is safeguarded by the transaction
> anyway seems to work nicely
Be _real_ careful with dealing with OID in this manner as it is not
going to have the same value after a dump -> reload cycle.
Personally I _never_ use OID for anything. Where's the benefit? There
isn't any high-speed access to records by using OID - you still need to
create an index on it (e.g.) if you have a large table that you are
using it as a key for.
The reason everyone is using:
$result = pg_Exec( $dbconn, 'SELECT nextval('my_seq');' );
if ( $result && $pg_NumRows($result) > 0 ) {
$my_new_id = pg_Fetch_Result( $result, 0, 0);
$result = pg_Exec( $dbconn, 'INSERT INTO mytable( myid )
VALUES( $my_new_id );');
}
else {
echo "<p>Drat!</p>";
}
Is because it is _THE_ right way to do it.
A sequence is dealt with slightly specially in PostgreSQL so that it
_does_ work. For example you can't roll back a 'nextval' - each one
will be one more than the last. This is critical to the process of
guaranteeing a different value is passed to each concurrent transaction.
Another important point is that you don't need to vacuum a sequence - no
matter how many nextval()s you do, you won't get deleted tuples. If you
implemented that with a record in your own table you would get a deleted
tuple every time you updated the sequence to say what the last used
value was.
Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267