Thread: Sequence

Sequence

From
Alan Roberto Romaniuc
Date:
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 ....


Re: Sequence

From
Dave Cramer
Date:
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>


Re: Sequence

From
Simon Mitchell
Date:
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
>>
>>



Re: Sequence

From
Dave Cramer
Date:
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>


Re: Sequence

From
Simon Mitchell
Date:
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
>
>
>



Re: Sequence

From
Dave Cramer
Date:
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>


Re: Sequence

From
Dave Cramer
Date:
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>


Re: Sequence

From
"Tim Lucia"
Date:
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
>
>