Thread: Simple problem?
Hi there,
I must be confusing my syntax somehow, because I'm having trouble doing a
simple update with returned id... As in.... I have two tables: an order
table and an orderitems table. I need to write the order record, then use
the order_id from the order record in the insert to the orderitems record.
1. Can I use a transaction begin/end for the entire transaction if I need
to get the order_id in between the two writes?
2. How do I get the order_id from the record I just wrote?
thanks in advance.
-sj
On Wed, 1 Mar 2000, Stan Jacobs wrote: > I must be confusing my syntax somehow, because I'm having trouble doing a > simple update with returned id... As in.... I have two tables: an order > table and an orderitems table. I need to write the order record, then use > the order_id from the order record in the insert to the orderitems record. I assume you're using serial columns here. Table schemas always help ... > 1. Can I use a transaction begin/end for the entire transaction if I need > to get the order_id in between the two writes? Sure. > 2. How do I get the order_id from the record I just wrote? You have to select it back out. A select on max(order_id) might do, but you might get caught if someone else does updates on your table as well. I am personally not so fond of serial columns because of this problem. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
On Thu, 2 Mar 2000, Peter Eisentraut wrote: > On Wed, 1 Mar 2000, Stan Jacobs wrote: > > > 2. How do I get the order_id from the record I just wrote? > > You have to select it back out. A select on max(order_id) might do, but > you might get caught if someone else does updates on your table as well. I > am personally not so fond of serial columns because of this problem. what? if you serial type, you can get it by curval(seqname) (see doc for details). Peter, how can You make such a mistake :-) no sleep for a week?
On Thu, Mar 02, 2000 at 04:17:30PM +0100, Peter Eisentraut wrote:
> On Wed, 1 Mar 2000, Stan Jacobs wrote:
>
> > I must be confusing my syntax somehow, because I'm having trouble doing a
> > simple update with returned id... As in.... I have two tables: an order
> > table and an orderitems table. I need to write the order record, then use
> > the order_id from the order record in the insert to the orderitems record.
>
> I assume you're using serial columns here. Table schemas always help ...
>
> > 1. Can I use a transaction begin/end for the entire transaction if I need
> > to get the order_id in between the two writes?
>
> Sure.
>
> > 2. How do I get the order_id from the record I just wrote?
>
> You have to select it back out. A select on max(order_id) might do, but
> you might get caught if someone else does updates on your table as well. I
> am personally not so fond of serial columns because of this problem.
Peter, I was _sure_ you were around for the last few times this has come up.
PostgreSQL's 'serial' pseudo type (convenience type?) is multiuser safe.
Stan, there's a couple approaches to solving your problem:
Some (such as Tom) prefer the programmatic solution: Create a sequence
(either manually or automatically by using a 'serial' type), select
the nextval() from the sequence yourself, then insert it, rather than
depending on the default. However, it is also possible to do:
SELECT currval('table_field_seq');
Note that you're passing a string constant to the function currval,
and that constant happens to be the name of the sequence to act on. If
you sequence has MultiCaps (like if you used quoted "InitCaps" in table
or field names), you'll need the double quotes, inside the single quotes:
SELECT currval('"Table_Field_seq"');
This is multiuser safe, since currval is defined for each SQL session
(basically, from open of database to close of connection.) The only
problem with it is that currval() for a sequence is undefined in
that session until a nextval() has happened. Here's a transcript,
demonstrating the problem, as well as the multiuser safety: (NOTICEs
wrapped for readability)
test=> create table test (s serial, t text);
NOTICE: CREATE TABLE will create implicit sequence 'test_s_seq' for
SERIAL column 'test.s'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_s_key'
for table 'test'
CREATE
test=> select currval('test_s_seq');
ERROR: test_s_seq.currval is not yet defined in this session
test=> insert into test (t) values ('some text');
INSERT 883807 1
test=> select currval('test_s_seq');
currval
-------
1
(1 row)
--------- open another session in a seperate xterm -----
test=> select currval('test_s_seq');
ERROR: test_s_seq.currval is not yet defined in this session
test=> insert into test (t) values ('some other text');
INSERT 883808 1
test=> select currval('test_s_seq');
currval
-------
2
(1 row)
test=>
--------- back to first session -----------------------
test=> select currval('test_s_seq');
currval
-------
1
(1 row)
test=>
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
You can do a SELECT curval('sequence_name'); to get the value you were just
assigned without worrying about other people updating, as curval gets the
current value within the current transaction (or within the current
session, either way, it works.)
Alternately, you can get the nextval before doing either of the inserts
with a select statement, and then use the returned value in you SQL.
Ex:
$OrderID=SELECT nextval('seq_order_id') AS order_id;
INSERT INTO orders (order_id, blah, blah) VALUES ($OrderID, blah, blah);
INSERT INTO items (order_id, blah, blah) VALUES ($OrderID, blah, blah);
At 11:17 AM 3/2/00, Peter Eisentraut wrote:
>On Wed, 1 Mar 2000, Stan Jacobs wrote:
>
> > I must be confusing my syntax somehow, because I'm having trouble doing a
> > simple update with returned id... As in.... I have two tables: an order
> > table and an orderitems table. I need to write the order record, then use
> > the order_id from the order record in the insert to the orderitems record.
>
>I assume you're using serial columns here. Table schemas always help ...
>
> > 1. Can I use a transaction begin/end for the entire transaction if I need
> > to get the order_id in between the two writes?
>
>Sure.
>
> > 2. How do I get the order_id from the record I just wrote?
>
>You have to select it back out. A select on max(order_id) might do, but
>you might get caught if someone else does updates on your table as well. I
>am personally not so fond of serial columns because of this problem.
>
>--
>Peter Eisentraut Sernanders väg 10:115
>peter_e@gmx.net 75262 Uppsala
>http://yi.org/peter-e/ Sweden
>
>
>************
************
You can do a SELECT curval('sequence_name'); to get the value you were just
assigned without worrying about other people updating, as curval gets the
current value within the current transaction (or within the current
session, either way, it works.)
Alternately, you can get the nextval before doing either of the inserts
with a select statement, and then use the returned value in you SQL.
Ex:
$OrderID=SELECT nextval('seq_order_id') AS order_id;
INSERT INTO orders (order_id, blah, blah) VALUES ($OrderID, blah, blah);
INSERT INTO items (order_id, blah, blah) VALUES ($OrderID, blah, blah);
At 11:17 AM 3/2/00, Peter Eisentraut wrote:
>On Wed, 1 Mar 2000, Stan Jacobs wrote:
>
> > I must be confusing my syntax somehow, because I'm having trouble doing a
> > simple update with returned id... As in.... I have two tables: an order
> > table and an orderitems table. I need to write the order record, then use
> > the order_id from the order record in the insert to the orderitems record.
>
>I assume you're using serial columns here. Table schemas always help ...
>
> > 1. Can I use a transaction begin/end for the entire transaction if I need
> > to get the order_id in between the two writes?
>
>Sure.
>
> > 2. How do I get the order_id from the record I just wrote?
>
>You have to select it back out. A select on max(order_id) might do, but
>you might get caught if someone else does updates on your table as well. I
>am personally not so fond of serial columns because of this problem.
>
>--
>Peter Eisentraut Sernanders väg 10:115
>peter_e@gmx.net 75262 Uppsala
>http://yi.org/peter-e/ Sweden
>
>
>************
************
On Thu, 2 Mar 2000, Ross J. Reedstrom wrote: > Peter, I was _sure_ you were around for the last few times this has come up. > PostgreSQL's 'serial' pseudo type (convenience type?) is multiuser safe. I was sure of that too but I can't remember this solution. How can sequences be "multiuser safe" if you can't roll them back? Okay, maybe that doesn't strictly qualify, but personally, I don't like sequences very much. But I'm sorry I misguided people. One never stops learning ... -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
> You can do a SELECT curval('sequence_name'); to get the value you were
just
> assigned without worrying about other people updating, as curval gets the
> current value within the current transaction (or within the current
> session, either way, it works.)
Or, my prefered method, create the table like so..
create table MyTable
(
MyField int4 default nextval('SomeSequence'),
...
);
That gives you the flexibility to select the nextval of the sequence and
insert it yourself (if you need it for further processing), or to simply
let the database take care of it for itself.
steve
Thanks to everyone for their help with this-- These solutions worked
beautifully!
The only thing better than a rock-solid OpenSource database is the
support of the community behind it.... :-) Thanks again.
"Ross J. Reedstrom" wrote:
>
> On Thu, Mar 02, 2000 at 04:17:30PM +0100, Peter Eisentraut wrote:
> > On Wed, 1 Mar 2000, Stan Jacobs wrote:
> >
> > > I must be confusing my syntax somehow, because I'm having trouble doing a
> > > simple update with returned id... As in.... I have two tables: an order
> > > table and an orderitems table. I need to write the order record, then use
> > > the order_id from the order record in the insert to the orderitems record.
> >
> > I assume you're using serial columns here. Table schemas always help ...
> >
> > > 1. Can I use a transaction begin/end for the entire transaction if I need
> > > to get the order_id in between the two writes?
> >
> > Sure.
> >
> > > 2. How do I get the order_id from the record I just wrote?
> >
> > You have to select it back out. A select on max(order_id) might do, but
> > you might get caught if someone else does updates on your table as well. I
> > am personally not so fond of serial columns because of this problem.
>
> Peter, I was _sure_ you were around for the last few times this has come up.
> PostgreSQL's 'serial' pseudo type (convenience type?) is multiuser safe.
>
> Stan, there's a couple approaches to solving your problem:
>
> Some (such as Tom) prefer the programmatic solution: Create a sequence
> (either manually or automatically by using a 'serial' type), select
> the nextval() from the sequence yourself, then insert it, rather than
> depending on the default. However, it is also possible to do:
>
> SELECT currval('table_field_seq');
>
> Note that you're passing a string constant to the function currval,
> and that constant happens to be the name of the sequence to act on. If
> you sequence has MultiCaps (like if you used quoted "InitCaps" in table
> or field names), you'll need the double quotes, inside the single quotes:
>
> SELECT currval('"Table_Field_seq"');
>
> This is multiuser safe, since currval is defined for each SQL session
> (basically, from open of database to close of connection.) The only
> problem with it is that currval() for a sequence is undefined in
> that session until a nextval() has happened. Here's a transcript,
> demonstrating the problem, as well as the multiuser safety: (NOTICEs
> wrapped for readability)
>
> test=> create table test (s serial, t text);
> NOTICE: CREATE TABLE will create implicit sequence 'test_s_seq' for
> SERIAL column 'test.s'
> NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_s_key'
> for table 'test'
> CREATE
> test=> select currval('test_s_seq');
> ERROR: test_s_seq.currval is not yet defined in this session
> test=> insert into test (t) values ('some text');
> INSERT 883807 1
> test=> select currval('test_s_seq');
> currval
> -------
> 1
> (1 row)
>
> --------- open another session in a seperate xterm -----
>
> test=> select currval('test_s_seq');
> ERROR: test_s_seq.currval is not yet defined in this session
> test=> insert into test (t) values ('some other text');
> INSERT 883808 1
> test=> select currval('test_s_seq');
> currval
> -------
> 2
> (1 row)
>
> test=>
>
> --------- back to first session -----------------------
>
> test=> select currval('test_s_seq');
> currval
> -------
> 1
> (1 row)
>
> test=>
>
> Ross
>
> --
> Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
> NSBRI Research Scientist/Programmer
> Computer and Information Technology Institute
> Rice University, 6100 S. Main St., Houston, TX 77005
--
Stan Jacobs
Managing Director
JTEK Interactive
jacobs@jtek.com