Thread: Sequence
I have a table with sequence field (id). I insert a new row in this table, and I would like to get the sequence number that postgresql assign to id. How can I get it??? ... Max function is not nice....... jdbc2 ....
Alan, You can't, get the sequence before and insert it. Dave On Thu, 2003-01-09 at 14:35, Alan Roberto Romaniuc wrote: > I have a table with sequence field (id). > > I insert a new row in this table, and I would like to get the sequence > number that postgresql assign to id. > > How can I get it??? ... Max function is not nice....... > > jdbc2 .... > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Dave Cramer <Dave@micro-automation.net>
Alan,
Just doing some test from psql prompt.
After your insert into the table the id sequence is available by
select currval('table_id_seq');
currval
---------
5006
(1 row)
If your session has not done an insert you should get an error.
select currval('table_id_seq');
ERROR: table_id_seq.currval is not yet defined in this session
Do a describe (\d) on your table to check seq name.
So this is equivalent to MySql > last_insert_id() .
***************************************************
If you wont the last value of the sequence (which could be your current
session or another session) you can select it with
SELECT last_value FROM table_id_seq;
last_value
------------
5006
(1 row)
**This is not transaction safe as another session could have done an
insert. Please use select currval('table_id_seq');**
It is just nice to know.
***************************************************
Simon
Dave Cramer wrote:
>Alan,
>
>You can't, get the sequence before and insert it.
>
>Dave
>On Thu, 2003-01-09 at 14:35, Alan Roberto Romaniuc wrote:
>
>
>>I have a table with sequence field (id).
>>
>>I insert a new row in this table, and I would like to get the sequence
>>number that postgresql assign to id.
>>
>>How can I get it??? ... Max function is not nice.......
>>
>>jdbc2 ....
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>subscribe-nomail command to majordomo@postgresql.org so that your
>>message can get through to the mailing list cleanly
>>
>>
Alan, Simon,
You can't do this, at least not safely.
Sequences can't be rolled back and are visible across transactions. In
other words if thread 1 inserted a row, and before you read the sequence
thread b inserted a row, you would get the same value for both threads.
The only way I know is to get the sequence before hand and insert it.
The overhead is the same.
Dave
On Sat, 2003-01-11 at 20:19, Simon Mitchell wrote:
> Alan,
> Just doing some test from psql prompt.
>
> After your insert into the table the id sequence is available by
>
> select currval('table_id_seq');
> currval
> ---------
> 5006
> (1 row)
>
>
> If your session has not done an insert you should get an error.
> select currval('table_id_seq');
> ERROR: table_id_seq.currval is not yet defined in this session
>
> Do a describe (\d) on your table to check seq name.
>
> So this is equivalent to MySql > last_insert_id() .
>
> ***************************************************
> If you wont the last value of the sequence (which could be your current
> session or another session) you can select it with
>
> SELECT last_value FROM table_id_seq;
> last_value
> ------------
> 5006
> (1 row)
>
> **This is not transaction safe as another session could have done an
> insert. Please use select currval('table_id_seq');**
> It is just nice to know.
> ***************************************************
>
> Simon
>
>
>
>
>
>
> Dave Cramer wrote:
>
> >Alan,
> >
> >You can't, get the sequence before and insert it.
> >
> >Dave
> >On Thu, 2003-01-09 at 14:35, Alan Roberto Romaniuc wrote:
> >
> >
> >>I have a table with sequence field (id).
> >>
> >>I insert a new row in this table, and I would like to get the sequence
> >>number that postgresql assign to id.
> >>
> >>How can I get it??? ... Max function is not nice.......
> >>
> >>jdbc2 ....
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 3: if posting/reading through Usenet, please send an appropriate
> >>subscribe-nomail command to majordomo@postgresql.org so that your
> >>message can get through to the mailing list cleanly
> >>
> >>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Dave Cramer <Dave@micro-automation.net>
Hi,
I am look at usings sequences for transaction.
Testing from psql -
If I START TRANSACTION, INSERT and ROLLBACK the sequence is
not rolled back.
This make sense, so my id column will have gaps if there is a
ROLLBACK or a transaction failure.
If another thread/session does an insert it will get the next
sequence.
psql=> commit;
COMMIT
psql=> start transaction;
START TRANSACTION
psql=> select currval('test_id_seq');
currval
---------
5063
(1 row)
psql=> insert into test (query) values('xyz');
INSERT 89646 1
psql=> select currval('test_id_seq');
currval
---------
5064
(1 row)
psql=> rollback;
ROLLBACK
psql=> select currval('test_id_seq');
currval
---------
5064
(1 row)
psql=> commit;
WARNING: COMMIT: no transaction in progress
COMMIT
psql=> select max(id) from test;
max
------
5063
(1 row)
psql=> insert into test (query) values('xyz');
INSERT 89647 1
psql=> select max(id) from test;
max
------
5065
(1 row)
Regards,
Simon
Ross J. Reedstrom wrote:
>On Sat, Jan 11, 2003 at 08:41:31PM -0500, Dave Cramer wrote:
>
>
>>Alan, Simon,
>>
>>You can't do this, at least not safely.
>>
>>Sequences can't be rolled back and are visible across transactions. In
>>other words if thread 1 inserted a row, and before you read the sequence
>>thread b inserted a row, you would get the same value for both threads.
>>The only way I know is to get the sequence before hand and insert it.
>>The overhead is the same.
>>
>>
>
>Dave -
>You really should test these things before stating with such assurance
>what will happen. Yes, sequences are outside transactions, but they
>_do_ honor connections. So, if your two hypothetical threads are
>using seperate connections (which they _must_ do, BTW), each can use
>the currval(seqname) to retrieve the value used in that connection,
>regardless of what happens in the other.
>
>Ross
>
>
>
Ross, Damn, you're right. Still I prefer to get the id first, but ya, I shoulda checked. Thanks for catching that. Dave On Sun, 2003-01-12 at 00:07, Ross J. Reedstrom wrote: > On Sat, Jan 11, 2003 at 08:41:31PM -0500, Dave Cramer wrote: > > Alan, Simon, > > > > You can't do this, at least not safely. > > > > Sequences can't be rolled back and are visible across transactions. In > > other words if thread 1 inserted a row, and before you read the sequence > > thread b inserted a row, you would get the same value for both threads. > > The only way I know is to get the sequence before hand and insert it. > > The overhead is the same. > > Dave - > You really should test these things before stating with such assurance > what will happen. Yes, sequences are outside transactions, but they > _do_ honor connections. So, if your two hypothetical threads are > using seperate connections (which they _must_ do, BTW), each can use > the currval(seqname) to retrieve the value used in that connection, > regardless of what happens in the other. > > Ross -- Dave Cramer <Dave@micro-automation.net>
Yes, the other session gets the next sequence value, try opening two
psql's and see what happens
It seems that currval() remembers the last value of all sequence's
altered in this session; probably for the express purpose of relating
tables.
Dave
On Sun, 2003-01-12 at 01:06, Simon Mitchell wrote:
> Hi,
> I am look at usings sequences for transaction.
>
> Testing from psql -
>
> If I START TRANSACTION, INSERT and ROLLBACK the sequence is
> not rolled back.
> This make sense, so my id column will have gaps if there is a
> ROLLBACK or a transaction failure.
> If another thread/session does an insert it will get the next
> sequence.
>
>
> psql=> commit;
> COMMIT
> psql=> start transaction;
>
> START TRANSACTION
> psql=> select currval('test_id_seq');
> currval
> ---------
> 5063
> (1 row)
>
> psql=> insert into test (query) values('xyz');
> INSERT 89646 1
> psql=> select currval('test_id_seq');
> currval
> ---------
> 5064
> (1 row)
>
> psql=> rollback;
> ROLLBACK
> psql=> select currval('test_id_seq');
> currval
> ---------
> 5064
> (1 row)
>
> psql=> commit;
> WARNING: COMMIT: no transaction in progress
> COMMIT
> psql=> select max(id) from test;
> max
> ------
> 5063
> (1 row)
>
> psql=> insert into test (query) values('xyz');
> INSERT 89647 1
> psql=> select max(id) from test;
> max
> ------
> 5065
> (1 row)
>
> Regards,
> Simon
>
>
>
>
> Ross J. Reedstrom wrote:
>
> >On Sat, Jan 11, 2003 at 08:41:31PM -0500, Dave Cramer wrote:
> >
> >
> >>Alan, Simon,
> >>
> >>You can't do this, at least not safely.
> >>
> >>Sequences can't be rolled back and are visible across transactions. In
> >>other words if thread 1 inserted a row, and before you read the sequence
> >>thread b inserted a row, you would get the same value for both threads.
> >>The only way I know is to get the sequence before hand and insert it.
> >>The overhead is the same.
> >>
> >>
> >
> >Dave -
> >You really should test these things before stating with such assurance
> >what will happen. Yes, sequences are outside transactions, but they
> >_do_ honor connections. So, if your two hypothetical threads are
> >using seperate connections (which they _must_ do, BTW), each can use
> >the currval(seqname) to retrieve the value used in that connection,
> >regardless of what happens in the other.
> >
> >Ross
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Dave Cramer <Dave@micro-automation.net>
I am using the method Dave recommends successfully - do a select nextval('table_id_seq') and then use that value in
yourinsert.
Tim
> -----Original Message-----
> From: Dave Cramer [mailto:Dave@micro-automation.net]
> Sent: Saturday, January 11, 2003 8:42 PM
> To: Simon Mitchell
> Cc: Alan Roberto Romaniuc; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Sequence
>
>
> Alan, Simon,
>
> You can't do this, at least not safely.
>
> Sequences can't be rolled back and are visible across transactions. In
> other words if thread 1 inserted a row, and before you read
> the sequence
> thread b inserted a row, you would get the same value for
> both threads.
> The only way I know is to get the sequence before hand and insert it.
> The overhead is the same.
>
> Dave
>
> On Sat, 2003-01-11 at 20:19, Simon Mitchell wrote:
> > Alan,
> > Just doing some test from psql prompt.
> >
> > After your insert into the table the id sequence is available by
> >
> > select currval('table_id_seq');
> > currval
> > ---------
> > 5006
> > (1 row)
> >
> >
> > If your session has not done an insert you should get an error.
> > select currval('table_id_seq');
> > ERROR: table_id_seq.currval is not yet defined in this session
> >
> > Do a describe (\d) on your table to check seq name.
> >
> > So this is equivalent to MySql > last_insert_id() .
> >
> > ***************************************************
> > If you wont the last value of the sequence (which could be
> your current
> > session or another session) you can select it with
> >
> > SELECT last_value FROM table_id_seq;
> > last_value
> > ------------
> > 5006
> > (1 row)
> >
> > **This is not transaction safe as another session could
> have done an
> > insert. Please use select currval('table_id_seq');**
> > It is just nice to know.
> > ***************************************************
> >
> > Simon
> >
> >
> >
> >
> >
> >
> > Dave Cramer wrote:
> >
> > >Alan,
> > >
> > >You can't, get the sequence before and insert it.
> > >
> > >Dave
> > >On Thu, 2003-01-09 at 14:35, Alan Roberto Romaniuc wrote:
> > >
> > >
> > >>I have a table with sequence field (id).
> > >>
> > >>I insert a new row in this table, and I would like to get
> the sequence
> > >>number that postgresql assign to id.
> > >>
> > >>How can I get it??? ... Max function is not nice.......
> > >>
> > >>jdbc2 ....
> > >>
> > >>
> > >>---------------------------(end of
> broadcast)---------------------------
> > >>TIP 3: if posting/reading through Usenet, please send an
> appropriate
> > >>subscribe-nomail command to majordomo@postgresql.org so that your
> > >>message can get through to the mailing list cleanly
> > >>
> > >>
> >
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> --
> Dave Cramer <Dave@micro-automation.net>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>