pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting primary key from insert statement
14+ messages / 6 participants
[nested] [flat]
* Getting primary key from insert statement
@ 1999-06-02 21:46 Pham, Thinh <tpham@mail.priority.net>
1999-06-02 22:54 ` Re: [SQL] Getting primary key from insert statement D'Arcy J.M. Cain <darcy@druid.net>
1999-06-06 12:31 ` Re: [SQL] Getting primary key from insert statement Herouth Maoz <herouth@oumail.openu.ac.il>
1999-06-07 07:29 ` Re: [SQL] Getting primary key from insert statement Herouth Maoz <herouth@oumail.openu.ac.il>
1999-06-07 09:51 ` Re: [SQL] Getting primary key from insert statement Emils Klotins <emils@mail.usis.bkc.lv>
0 siblings, 4 replies; 14+ messages in thread
From: Pham, Thinh @ 1999-06-02 21:46 UTC (permalink / raw)
To: pgsql-sql
Hi, is there a way you can get back the primary key (serialized) from an
insert you just do on a table. For example i have a table called "order" and
i want to get the pid of that record so i can use it in the "orderdetail"
table. It would be nice if that same insert statement could also return the
pid.
Table structure:
create table order (pid serial, orddate date);
create table orderdetail (pid serial, orderpid int, name varchar(50));
Insert statement:
insert into order (date) values ('6/2/1999')
order table
pid | orddate
----------------
1 | 6/2/1999
orderdetail table
pid | orderpid | name
--------------------------
1 | 1 | pencil
2 | 1 | pen
I know there must be a way, but i just couldn't find it in either the pgsql
docs or faq. Thanks in advance,
Thinh
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] Getting primary key from insert statement
1999-06-02 21:46 Getting primary key from insert statement Pham, Thinh <tpham@mail.priority.net>
@ 1999-06-02 22:54 ` D'Arcy J.M. Cain <darcy@druid.net>
3 siblings, 0 replies; 14+ messages in thread
From: "D'Arcy" "J.M." Cain @ 1999-06-02 22:54 UTC (permalink / raw)
To: tpham@mail.priority.net; +Cc: pgsql-sql
Thus spake Pham, Thinh
> Hi, is there a way you can get back the primary key (serialized) from an
> insert you just do on a table. For example i have a table called "order" and
> i want to get the pid of that record so i can use it in the "orderdetail"
> table. It would be nice if that same insert statement could also return the
> pid.
>
> Table structure:
> create table order (pid serial, orddate date);
You realize that order is a keyword, right?
> Insert statement:
> insert into order (date) values ('6/2/1999')
Assuming you change order to something else, the insert returns the OID
of the record just added. In psql this appears as something like this.
INSERT 36448 1
So, just read back the record.
SELECT pid FROM table WHERE OID = 36448;
The various interfaces give you methods to get this value back. Read the
docs for whichever interface you are using.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] Getting primary key from insert statement
1999-06-02 21:46 Getting primary key from insert statement Pham, Thinh <tpham@mail.priority.net>
@ 1999-06-06 12:31 ` Herouth Maoz <herouth@oumail.openu.ac.il>
3 siblings, 0 replies; 14+ messages in thread
From: Herouth Maoz @ 1999-06-06 12:31 UTC (permalink / raw)
To: Pham, Thinh <tpham@mail.priority.net>; pgsql-sql
At 00:46 +0300 on 03/06/1999, Pham, Thinh wrote:
> Hi, is there a way you can get back the primary key (serialized) from an
> insert you just do on a table. For example i have a table called "order" and
> i want to get the pid of that record so i can use it in the "orderdetail"
> table. It would be nice if that same insert statement could also return the
> pid.
Yes. You have to know the name of the sequence which the serial type caused
to be created. You are notified of the name when you create the table. It
would be 'order_pid_seq' in the case you described (remember that ORDER is
a reserved word, though).
Anyway, after you make the insert, you can retrieve the recently-created
value of the sequence, using currval( 'order_pid_seq' ).
This returns the value of the last invocation of nextval( 'order_pid_seq' )
in the current session. When you do an insert, it calls nextval internally.
And thus you will get a proper currval.
The question everybody asks is "but what if somebody else manages to insert
another row before I use currval?". This is not a concern, because currval
always returns the number your own session generated, and not anybody else.
Note that currval will not work until somebody does a nextval on the same
sequence.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] Getting primary key from insert statement
1999-06-02 21:46 Getting primary key from insert statement Pham, Thinh <tpham@mail.priority.net>
@ 1999-06-07 07:29 ` Herouth Maoz <herouth@oumail.openu.ac.il>
3 siblings, 0 replies; 14+ messages in thread
From: Herouth Maoz @ 1999-06-07 07:29 UTC (permalink / raw)
To: pgsql-sql
At 12:51 +0300 on 07/06/1999, Emils Klotins wrote:
>
> Wouldn't it be simpler just to SELECT the next value from the sequence
>BEFORE the insert and
> use it in the INSERT statement directly?
Then you have to have a transaction around the two operations. And by the
time you do the select, someone may lock the table where you insert. It's a
potential for deadlock.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] Getting primary key from insert statement
1999-06-02 21:46 Getting primary key from insert statement Pham, Thinh <tpham@mail.priority.net>
@ 1999-06-07 09:51 ` Emils Klotins <emils@mail.usis.bkc.lv>
3 siblings, 0 replies; 14+ messages in thread
From: Emils Klotins @ 1999-06-07 09:51 UTC (permalink / raw)
To: pgsql-sql
[6 Jun 99,, 15:31] Herouth Maoz wrote:
> At 00:46 +0300 on 03/06/1999, Pham, Thinh wrote:
>
>
> > Hi, is there a way you can get back the primary key (serialized) from an
> > insert you just do on a table. For example i have a table called "order" and
[snip]
> Yes. You have to know the name of the sequence which the serial type caused
> to be created. You are notified of the name when you create the table. It
> would be 'order_pid_seq' in the case you described (remember that ORDER is
> a reserved word, though).
[snip]
> Anyway, after you make the insert, you can retrieve the recently-created
> value of the sequence, using currval( 'order_pid_seq' ).
[snip]
> Note that currval will not work until somebody does a nextval on the same
> sequence.
>
Wouldn't it be simpler just to SELECT the next value from the sequence BEFORE the insert and
use it in the INSERT statement directly?
Emils Klotins e-mail: emils@mail.usis.bkc.lv
Systems Manager URL: http://www.usis.bkc.lv/
USIS Riga 7 Smilsu Str., Riga LV1050, LATVIA
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] Getting primary key from insert statement
@ 1999-06-07 20:10 Michael J Davis <michael.j.davis@tvguide.com>
1999-06-08 08:26 ` RE: [SQL] Getting primary key from insert statement Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 1 reply; 14+ messages in thread
From: Michael J Davis @ 1999-06-07 20:10 UTC (permalink / raw)
To: 'Herouth Maoz' <herouth@oumail.openu.ac.il>; emils@mail.usis.bkc.lv; pgsql-sql
I have been using this technique for years in Oracle (select next value from
the primary key sequence and insert using this primary key). I like this
approach. I don't think a transaction is needed. Access97 definitely likes
this approach better because it wants to reselect the newly inserted record.
Without the primary key in the insert statement, the re-select can be very
slow and potentially fail because the re-select is only using values
provided in the insert statement. Retrieving the primary key after the
insert does not help Access97.
> -----Original Message-----
> From: Herouth Maoz [SMTP:herouth@oumail.openu.ac.il]
> Sent: Monday, June 07, 1999 1:29 AM
> To: emils@mail.usis.bkc.lv; pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] Getting primary key from insert statement
>
> At 12:51 +0300 on 07/06/1999, Emils Klotins wrote:
>
>
> >
> > Wouldn't it be simpler just to SELECT the next value from the sequence
> >BEFORE the insert and
> > use it in the INSERT statement directly?
>
> Then you have to have a transaction around the two operations. And by the
> time you do the select, someone may lock the table where you insert. It's
> a
> potential for deadlock.
>
> Herouth
>
> --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] Getting primary key from insert statement
1999-06-07 20:10 RE: [SQL] Getting primary key from insert statement Michael J Davis <michael.j.davis@tvguide.com>
@ 1999-06-08 08:26 ` Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 0 replies; 14+ messages in thread
From: Herouth Maoz @ 1999-06-08 08:26 UTC (permalink / raw)
To: pgsql-sql
At 23:10 +0300 on 07/06/1999, Michael J Davis wrote:
> I have been using this technique for years in Oracle (select next value from
> the primary key sequence and insert using this primary key). I like this
> approach. I don't think a transaction is needed.
I don't know what mechanism Oracle uses. Perhaps even a transaction is not
needed (although logically, the two operations should be one, so as not to
allow the failure of the insertion, if only to save on unused key values).
But doing things like that on the client side means that there is no
logical connection between the sequence and the table. Anybody is free to
enter any primary key, and a mistake in one of the front ends will cause
inconsistency in the database. The theory of databases asserts that you
should try to insert the logic and constraints of the organization's data
into the backend.
I know that this should mean that in fields declared "serial", values other
than the default should not be allowed. Perhaps this should be addressed in
new versions of Postgres somewhere.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] Getting primary key from insert statement
@ 1999-06-07 22:35 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 14+ messages in thread
From: Jackson, DeJuan @ 1999-06-07 22:35 UTC (permalink / raw)
To: Michael J Davis <michael.j.davis@tvguide.com>; 'Herouth Maoz' <herouth@oumail.openu.ac.il>; emils@mail.usis.bkc.lv; pgsql-sql
Have you ever actually looked at what Access97 does as far as SQL is
concerned?
If you ever do then you will realize that arguing that "Access97 like's it"
is no argument at all.
I have implemented your proposed method for insertion before as well. And
A) if you do not want to waste sequences in your primary key
and
B) do not want dead lock conditions showing up at random or aborted inserts
then it's best if you use transactions.
Any counter systems I ever implement in a database I've implemented in a
stored procedure and then called the stored procedure in a transaction to
store the value in a variable for use in an insert within the same
transaction. There is no way around these problems in a multi-user system.
Scenarios:
1) Grab the sequence number and prefill the number for the insert.
User fills out rest of form and submits. Insert data and increment counter.
problems -
a) another user start to insert within the time that the
first insert starts and ends
answer 2) Start Transaction. Lock the sequence table/row. Proceed with
Scenario 1.
(already needs transaction if you don't implement you own locking
system).
problems -
a) the user does nothing for a few hours/days and your
system is useless until you can track down the offending insert and finish
it (cancel)
b) the user's application dies weather through malicious
intent, an honest systems failure, or an honest user mistake; this could be
fine because your transaction might abort for you (I wouldn't count on it).
answer 3) Start Transaction. Lock the sequence table/row. Grab the next
number. Increment the sequence. End Transaction. prefill form. Users enters
data and commits. Insert data.
problems -
a) your data will have holes in it if the user aborts an
insert.
answer ... This could go on forever, but no matter how you approach it
you'll need a Transaction to accomplish what you want in standard SQL.
-DEJ
> -----Original Message-----
> From: Michael J Davis [SMTP:michael.j.davis@tvguide.com]
> Sent: Monday, June 07, 1999 3:10 PM
> To: 'Herouth Maoz'; emils@mail.usis.bkc.lv; pgsql-sql@postgreSQL.org
> Subject: RE: [SQL] Getting primary key from insert statement
>
> I have been using this technique for years in Oracle (select next value
> from
> the primary key sequence and insert using this primary key). I like this
> approach. I don't think a transaction is needed. Access97 definitely
> likes
> this approach better because it wants to reselect the newly inserted
> record.
> Without the primary key in the insert statement, the re-select can be very
> slow and potentially fail because the re-select is only using values
> provided in the insert statement. Retrieving the primary key after the
> insert does not help Access97.
>
> > -----Original Message-----
> > From: Herouth Maoz [SMTP:herouth@oumail.openu.ac.il]
> > Sent: Monday, June 07, 1999 1:29 AM
> > To: emils@mail.usis.bkc.lv; pgsql-sql@postgreSQL.org
> > Subject: Re: [SQL] Getting primary key from insert statement
> >
> > At 12:51 +0300 on 07/06/1999, Emils Klotins wrote:
> >
> >
> > >
> > > Wouldn't it be simpler just to SELECT the next value from the sequence
> > >BEFORE the insert and
> > > use it in the INSERT statement directly?
> >
> > Then you have to have a transaction around the two operations. And by
> the
> > time you do the select, someone may lock the table where you insert.
> It's
> > a
> > potential for deadlock.
> >
> > Herouth
> >
> > --
> > Herouth Maoz, Internet developer.
> > Open University of Israel - Telem project
> > http://telem.openu.ac.il/~herutma
> >
> >
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] Getting primary key from insert statement
@ 1999-06-07 23:14 Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 0 replies; 14+ messages in thread
From: Michael J Davis @ 1999-06-07 23:14 UTC (permalink / raw)
To: 'Jackson, DeJuan' <djackson@cpsgroup.com>; pgsql-sql
As I am sure you are aware, getting the next value in a sequence
automatically increments the sequence. Two users that get the next value
from the same sequence at the same time should get different sequence
numbers regardless of whether transactions are used or not. Neither user
should lock the other user out of the sequence. If one user does manage to
lock a sequence then other users should have to wait or will time out. Even
if you get the next value from a sequence within a transaction, I'll bet the
sequence is incremented even if the transaction is rolled back or aborted.
The only problem with this solution is it creates potential holes in the
primary key, i.e. you loose sequences.
In reference to whether "Access97 likes it", I have monitored the process in
the psql log files of what Access97 does after an insert and can verify that
it does what I suggest. This process (get next sequence and use this as the
primary key in an insert statement) improved the time it takes to do inserts
by minutes (i.e. it shaved several minutes off the time it took Access to
complete one insert). This process also eliminated several problems I was
having with doing an insert followed by Access97 showing a blank record
(this was very disturbing). If refreshed my form, I could see the newly
inserted record (sometimes).
In summary, I don't agree with your assessment of the locking problems
associated with getting the next value of the sequence before the insert. I
also don't agree that a transaction is needed. I do agree that you can
potentially loose sequence numbers. I have been willing to accept this loss
when compared to the problems this fixed in Access97.
Thanks, Michael
> -----Original Message-----
> From: Jackson, DeJuan [SMTP:djackson@cpsgroup.com]
> Sent: Monday, June 07, 1999 4:35 PM
> To: Michael J Davis; 'Herouth Maoz'; emils@mail.usis.bkc.lv;
> pgsql-sql@postgreSQL.org
> Subject: RE: [SQL] Getting primary key from insert statement
>
> Have you ever actually looked at what Access97 does as far as SQL is
> concerned?
> If you ever do then you will realize that arguing that "Access97 like's
> it"
> is no argument at all.
> I have implemented your proposed method for insertion before as well. And
>
> A) if you do not want to waste sequences in your primary key
> and
> B) do not want dead lock conditions showing up at random or aborted
> inserts
> then it's best if you use transactions.
> Any counter systems I ever implement in a database I've implemented in a
> stored procedure and then called the stored procedure in a transaction to
> store the value in a variable for use in an insert within the same
> transaction. There is no way around these problems in a multi-user
> system.
>
>
> Scenarios:
> 1) Grab the sequence number and prefill the number for the insert.
> User fills out rest of form and submits. Insert data and increment
> counter.
> problems -
> a) another user start to insert within the time that the
> first insert starts and ends
> answer 2) Start Transaction. Lock the sequence table/row. Proceed
> with
> Scenario 1.
> (already needs transaction if you don't implement you own locking
> system).
> problems -
> a) the user does nothing for a few hours/days and your
> system is useless until you can track down the offending insert and finish
> it (cancel)
> b) the user's application dies weather through malicious
> intent, an honest systems failure, or an honest user mistake; this could
> be
> fine because your transaction might abort for you (I wouldn't count on
> it).
>
> answer 3) Start Transaction. Lock the sequence table/row. Grab the
> next
> number. Increment the sequence. End Transaction. prefill form. Users
> enters
> data and commits. Insert data.
> problems -
> a) your data will have holes in it if the user aborts an
> insert.
> answer ... This could go on forever, but no matter how you approach it
> you'll need a Transaction to accomplish what you want in standard SQL.
>
> -DEJ
> > -----Original Message-----
> > From: Michael J Davis [SMTP:michael.j.davis@tvguide.com]
> > Sent: Monday, June 07, 1999 3:10 PM
> > To: 'Herouth Maoz'; emils@mail.usis.bkc.lv; pgsql-sql@postgreSQL.org
> > Subject: RE: [SQL] Getting primary key from insert statement
> >
> > I have been using this technique for years in Oracle (select next value
> > from
> > the primary key sequence and insert using this primary key). I like
> this
> > approach. I don't think a transaction is needed. Access97 definitely
> > likes
> > this approach better because it wants to reselect the newly inserted
> > record.
> > Without the primary key in the insert statement, the re-select can be
> very
> > slow and potentially fail because the re-select is only using values
> > provided in the insert statement. Retrieving the primary key after the
> > insert does not help Access97.
> >
> > > -----Original Message-----
> > > From: Herouth Maoz [SMTP:herouth@oumail.openu.ac.il]
> > > Sent: Monday, June 07, 1999 1:29 AM
> > > To: emils@mail.usis.bkc.lv; pgsql-sql@postgreSQL.org
> > > Subject: Re: [SQL] Getting primary key from insert statement
> > >
> > > At 12:51 +0300 on 07/06/1999, Emils Klotins wrote:
> > >
> > >
> > > >
> > > > Wouldn't it be simpler just to SELECT the next value from the
> sequence
> > > >BEFORE the insert and
> > > > use it in the INSERT statement directly?
> > >
> > > Then you have to have a transaction around the two operations. And by
> > the
> > > time you do the select, someone may lock the table where you insert.
> > It's
> > > a
> > > potential for deadlock.
> > >
> > > Herouth
> > >
> > > --
> > > Herouth Maoz, Internet developer.
> > > Open University of Israel - Telem project
> > > http://telem.openu.ac.il/~herutma
> > >
> > >
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] Getting primary key from insert statement
@ 1999-06-07 23:30 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 14+ messages in thread
From: Jackson, DeJuan @ 1999-06-07 23:30 UTC (permalink / raw)
To: Michael J Davis <michael.j.davis@tvguide.com>; pgsql-sql
Sorry, I interpreted your statement as saying that you were implementing
your own counter system, which your response makes clear is not the case.
But I still say that there is a transaction (or it's equivalent) there,
weather or not you are the one initiating it. I don't know who implemented
sequences in the backend but sequence increment-read must be an autonomous
unit to not run into duplicate sequence number problems.
If any experts out there want to prove me wrong, I'm all ears.
My question to you, since you've watched what Access97 generates in SQL, is
why can't it find the row in question after it has been committed to the
database without the primary key? The only possibility that I can think of
is that all of the other data is a duplicate of a previous entry in the
table.
-DEJ
> -----Original Message-----
> From: Michael J Davis [SMTP:michael.j.davis@tvguide.com]
> Sent: Monday, June 07, 1999 6:14 PM
> To: 'Jackson, DeJuan'; pgsql-sql@postgreSQL.org
> Subject: RE: [SQL] Getting primary key from insert statement
>
> As I am sure you are aware, getting the next value in a sequence
> automatically increments the sequence. Two users that get the next value
> from the same sequence at the same time should get different sequence
> numbers regardless of whether transactions are used or not. Neither user
> should lock the other user out of the sequence. If one user does manage
> to
> lock a sequence then other users should have to wait or will time out.
> Even
> if you get the next value from a sequence within a transaction, I'll bet
> the
> sequence is incremented even if the transaction is rolled back or aborted.
> The only problem with this solution is it creates potential holes in the
> primary key, i.e. you loose sequences.
>
> In reference to whether "Access97 likes it", I have monitored the process
> in
> the psql log files of what Access97 does after an insert and can verify
> that
> it does what I suggest. This process (get next sequence and use this as
> the
> primary key in an insert statement) improved the time it takes to do
> inserts
> by minutes (i.e. it shaved several minutes off the time it took Access to
> complete one insert). This process also eliminated several problems I was
> having with doing an insert followed by Access97 showing a blank record
> (this was very disturbing). If refreshed my form, I could see the newly
> inserted record (sometimes).
>
> In summary, I don't agree with your assessment of the locking problems
> associated with getting the next value of the sequence before the insert.
> I
> also don't agree that a transaction is needed. I do agree that you can
> potentially loose sequence numbers. I have been willing to accept this
> loss
> when compared to the problems this fixed in Access97.
>
> Thanks, Michael
>
>
> > -----Original Message-----
> > From: Jackson, DeJuan [SMTP:djackson@cpsgroup.com]
> > Sent: Monday, June 07, 1999 4:35 PM
> > To: Michael J Davis; 'Herouth Maoz'; emils@mail.usis.bkc.lv;
> > pgsql-sql@postgreSQL.org
> > Subject: RE: [SQL] Getting primary key from insert statement
> >
> > Have you ever actually looked at what Access97 does as far as SQL is
> > concerned?
> > If you ever do then you will realize that arguing that "Access97 like's
> > it"
> > is no argument at all.
> > I have implemented your proposed method for insertion before as well.
> And
> >
> > A) if you do not want to waste sequences in your primary key
> > and
> > B) do not want dead lock conditions showing up at random or aborted
> > inserts
> > then it's best if you use transactions.
> > Any counter systems I ever implement in a database I've implemented in a
> > stored procedure and then called the stored procedure in a transaction
> to
> > store the value in a variable for use in an insert within the same
> > transaction. There is no way around these problems in a multi-user
> > system.
> >
> >
> > Scenarios:
> > 1) Grab the sequence number and prefill the number for the insert.
> > User fills out rest of form and submits. Insert data and increment
> > counter.
> > problems -
> > a) another user start to insert within the time that the
> > first insert starts and ends
> > answer 2) Start Transaction. Lock the sequence table/row. Proceed
> > with
> > Scenario 1.
> > (already needs transaction if you don't implement you own locking
> > system).
> > problems -
> > a) the user does nothing for a few hours/days and your
> > system is useless until you can track down the offending insert and
> finish
> > it (cancel)
> > b) the user's application dies weather through malicious
> > intent, an honest systems failure, or an honest user mistake; this could
> > be
> > fine because your transaction might abort for you (I wouldn't count on
> > it).
> >
> > answer 3) Start Transaction. Lock the sequence table/row. Grab the
> > next
> > number. Increment the sequence. End Transaction. prefill form. Users
> > enters
> > data and commits. Insert data.
> > problems -
> > a) your data will have holes in it if the user aborts an
> > insert.
> > answer ... This could go on forever, but no matter how you approach it
> > you'll need a Transaction to accomplish what you want in standard SQL.
> >
> > -DEJ
> > > -----Original Message-----
> > > From: Michael J Davis [SMTP:michael.j.davis@tvguide.com]
> > > Sent: Monday, June 07, 1999 3:10 PM
> > > To: 'Herouth Maoz'; emils@mail.usis.bkc.lv;
> pgsql-sql@postgreSQL.org
> > > Subject: RE: [SQL] Getting primary key from insert statement
> > >
> > > I have been using this technique for years in Oracle (select next
> value
> > > from
> > > the primary key sequence and insert using this primary key). I like
> > this
> > > approach. I don't think a transaction is needed. Access97 definitely
> > > likes
> > > this approach better because it wants to reselect the newly inserted
> > > record.
> > > Without the primary key in the insert statement, the re-select can be
> > very
> > > slow and potentially fail because the re-select is only using values
> > > provided in the insert statement. Retrieving the primary key after
> the
> > > insert does not help Access97.
> > >
> > > > -----Original Message-----
> > > > From: Herouth Maoz [SMTP:herouth@oumail.openu.ac.il]
> > > > Sent: Monday, June 07, 1999 1:29 AM
> > > > To: emils@mail.usis.bkc.lv; pgsql-sql@postgreSQL.org
> > > > Subject: Re: [SQL] Getting primary key from insert statement
> > > >
> > > > At 12:51 +0300 on 07/06/1999, Emils Klotins wrote:
> > > >
> > > >
> > > > >
> > > > > Wouldn't it be simpler just to SELECT the next value from the
> > sequence
> > > > >BEFORE the insert and
> > > > > use it in the INSERT statement directly?
> > > >
> > > > Then you have to have a transaction around the two operations. And
> by
> > > the
> > > > time you do the select, someone may lock the table where you insert.
> > > It's
> > > > a
> > > > potential for deadlock.
> > > >
> > > > Herouth
> > > >
> > > > --
> > > > Herouth Maoz, Internet developer.
> > > > Open University of Israel - Telem project
> > > > http://telem.openu.ac.il/~herutma
> > > >
> > > >
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] Getting primary key from insert statement
@ 1999-06-07 23:36 Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 0 replies; 14+ messages in thread
From: Michael J Davis @ 1999-06-07 23:36 UTC (permalink / raw)
To: 'Jackson, DeJuan' <djackson@cpsgroup.com>; pgsql-sql
Access97 only knows about the fields used in the insert. Once the insert is
completed, Access97 re-selects the record based on the values provided in
the insert statement. It does not know what the primary key of the new
record is unless the primary key is a part of the insert statement. If the
information provided in the insert does not hit a unique record, then the
wrong record could be returned. The re-select is important to make sure the
newly inserted record reflects any updates that may have occurred as a
result of an insert trigger.
Thanks, Michael
> -----Original Message-----
> From: Jackson, DeJuan [SMTP:djackson@cpsgroup.com]
> Sent: Monday, June 07, 1999 5:31 PM
> To: Michael J Davis; pgsql-sql@postgreSQL.org
> Subject: RE: [SQL] Getting primary key from insert statement
>
> Sorry, I interpreted your statement as saying that you were implementing
> your own counter system, which your response makes clear is not the case.
> But I still say that there is a transaction (or it's equivalent) there,
> weather or not you are the one initiating it. I don't know who
> implemented
> sequences in the backend but sequence increment-read must be an autonomous
> unit to not run into duplicate sequence number problems.
> If any experts out there want to prove me wrong, I'm all ears.
> My question to you, since you've watched what Access97 generates in SQL,
> is
> why can't it find the row in question after it has been committed to the
> database without the primary key? The only possibility that I can think
> of
> is that all of the other data is a duplicate of a previous entry in the
> table.
> -DEJ
>
> > -----Original Message-----
> > From: Michael J Davis [SMTP:michael.j.davis@tvguide.com]
> > Sent: Monday, June 07, 1999 6:14 PM
> > To: 'Jackson, DeJuan'; pgsql-sql@postgreSQL.org
> > Subject: RE: [SQL] Getting primary key from insert statement
> >
> > As I am sure you are aware, getting the next value in a sequence
> > automatically increments the sequence. Two users that get the next
> value
> > from the same sequence at the same time should get different sequence
> > numbers regardless of whether transactions are used or not. Neither
> user
> > should lock the other user out of the sequence. If one user does manage
> > to
> > lock a sequence then other users should have to wait or will time out.
> > Even
> > if you get the next value from a sequence within a transaction, I'll bet
> > the
> > sequence is incremented even if the transaction is rolled back or
> aborted.
> > The only problem with this solution is it creates potential holes in the
> > primary key, i.e. you loose sequences.
> >
> > In reference to whether "Access97 likes it", I have monitored the
> process
> > in
> > the psql log files of what Access97 does after an insert and can verify
> > that
> > it does what I suggest. This process (get next sequence and use this as
> > the
> > primary key in an insert statement) improved the time it takes to do
> > inserts
> > by minutes (i.e. it shaved several minutes off the time it took Access
> to
> > complete one insert). This process also eliminated several problems I
> was
> > having with doing an insert followed by Access97 showing a blank record
> > (this was very disturbing). If refreshed my form, I could see the newly
> > inserted record (sometimes).
> >
> > In summary, I don't agree with your assessment of the locking problems
> > associated with getting the next value of the sequence before the
> insert.
> > I
> > also don't agree that a transaction is needed. I do agree that you can
> > potentially loose sequence numbers. I have been willing to accept this
> > loss
> > when compared to the problems this fixed in Access97.
> >
> > Thanks, Michael
> >
> >
> > > -----Original Message-----
> > > From: Jackson, DeJuan [SMTP:djackson@cpsgroup.com]
> > > Sent: Monday, June 07, 1999 4:35 PM
> > > To: Michael J Davis; 'Herouth Maoz'; emils@mail.usis.bkc.lv;
> > > pgsql-sql@postgreSQL.org
> > > Subject: RE: [SQL] Getting primary key from insert statement
> > >
> > > Have you ever actually looked at what Access97 does as far as SQL is
> > > concerned?
> > > If you ever do then you will realize that arguing that "Access97
> like's
> > > it"
> > > is no argument at all.
> > > I have implemented your proposed method for insertion before as well.
> > And
> > >
> > > A) if you do not want to waste sequences in your primary key
> > > and
> > > B) do not want dead lock conditions showing up at random or aborted
> > > inserts
> > > then it's best if you use transactions.
> > > Any counter systems I ever implement in a database I've implemented in
> a
> > > stored procedure and then called the stored procedure in a transaction
> > to
> > > store the value in a variable for use in an insert within the same
> > > transaction. There is no way around these problems in a multi-user
> > > system.
> > >
> > >
> > > Scenarios:
> > > 1) Grab the sequence number and prefill the number for the insert.
> > > User fills out rest of form and submits. Insert data and increment
> > > counter.
> > > problems -
> > > a) another user start to insert within the time that the
> > > first insert starts and ends
> > > answer 2) Start Transaction. Lock the sequence table/row. Proceed
> > > with
> > > Scenario 1.
> > > (already needs transaction if you don't implement you own locking
> > > system).
> > > problems -
> > > a) the user does nothing for a few hours/days and your
> > > system is useless until you can track down the offending insert and
> > finish
> > > it (cancel)
> > > b) the user's application dies weather through malicious
> > > intent, an honest systems failure, or an honest user mistake; this
> could
> > > be
> > > fine because your transaction might abort for you (I wouldn't count on
> > > it).
> > >
> > > answer 3) Start Transaction. Lock the sequence table/row. Grab the
> > > next
> > > number. Increment the sequence. End Transaction. prefill form. Users
> > > enters
> > > data and commits. Insert data.
> > > problems -
> > > a) your data will have holes in it if the user aborts an
> > > insert.
> > > answer ... This could go on forever, but no matter how you approach it
> > > you'll need a Transaction to accomplish what you want in standard SQL.
> > >
> > > -DEJ
> > > > -----Original Message-----
> > > > From: Michael J Davis [SMTP:michael.j.davis@tvguide.com]
> > > > Sent: Monday, June 07, 1999 3:10 PM
> > > > To: 'Herouth Maoz'; emils@mail.usis.bkc.lv;
> > pgsql-sql@postgreSQL.org
> > > > Subject: RE: [SQL] Getting primary key from insert statement
> > > >
> > > > I have been using this technique for years in Oracle (select next
> > value
> > > > from
> > > > the primary key sequence and insert using this primary key). I like
> > > this
> > > > approach. I don't think a transaction is needed. Access97
> definitely
> > > > likes
> > > > this approach better because it wants to reselect the newly inserted
> > > > record.
> > > > Without the primary key in the insert statement, the re-select can
> be
> > > very
> > > > slow and potentially fail because the re-select is only using values
> > > > provided in the insert statement. Retrieving the primary key after
> > the
> > > > insert does not help Access97.
> > > >
> > > > > -----Original Message-----
> > > > > From: Herouth Maoz [SMTP:herouth@oumail.openu.ac.il]
> > > > > Sent: Monday, June 07, 1999 1:29 AM
> > > > > To: emils@mail.usis.bkc.lv; pgsql-sql@postgreSQL.org
> > > > > Subject: Re: [SQL] Getting primary key from insert statement
> > > > >
> > > > > At 12:51 +0300 on 07/06/1999, Emils Klotins wrote:
> > > > >
> > > > >
> > > > > >
> > > > > > Wouldn't it be simpler just to SELECT the next value from the
> > > sequence
> > > > > >BEFORE the insert and
> > > > > > use it in the INSERT statement directly?
> > > > >
> > > > > Then you have to have a transaction around the two operations. And
> > by
> > > > the
> > > > > time you do the select, someone may lock the table where you
> insert.
> > > > It's
> > > > > a
> > > > > potential for deadlock.
> > > > >
> > > > > Herouth
> > > > >
> > > > > --
> > > > > Herouth Maoz, Internet developer.
> > > > > Open University of Israel - Telem project
> > > > > http://telem.openu.ac.il/~herutma
> > > > >
> > > > >
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] Getting primary key from insert statement
@ 1999-06-08 16:23 Michael J Davis <michael.j.davis@tvguide.com>
1999-06-08 16:40 ` RE: [SQL] Getting primary key from insert statement Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 1 reply; 14+ messages in thread
From: Michael J Davis @ 1999-06-08 16:23 UTC (permalink / raw)
To: 'Herouth Maoz' <herouth@oumail.openu.ac.il>; pgsql-sql
Perhaps I need to qualify my approach a little more. Let me give an
example:
create table Status
(
StatusID int4 PRIMARY KEY DEFAULT
nextval('Status_seq'),
StatusName varchar(32) NOT NULL,
);
A normal insert in PostgreSQL would look like this:
Insert into Status (StatusName) values ('name of status');
An insert using Access97 would look like this:
Select nextval('Status_seq') into StatusID_value;
Insert into Status (StatusID, StatusName) values (StatusID_value,
'name of status');
Either way the StatusID or primary key is set the same way using the same
technique. One method lets PostgreSQL determine the primary key while the
other method proactively determines the primary key is before the insert.
I accomplish this by trapping the "Before Insert" event in the form used to
add new Status records (the user is NOT entering the StatusID) and doing the
following:
Form.StatusID = getSeqValue('Status_seq')
Where getSeqValue() is defined below:
Public function getSeqValue(sequence_name as string)
// basically this function does the following:
Select nextval('Status_seq') into StatusID;
End function
-----Original Message-----
From: Herouth Maoz [SMTP:herouth@oumail.openu.ac.il]
Sent: Tuesday, June 08, 1999 2:27 AM
To: pgsql-sql@postgreSQL.org
Subject: RE: [SQL] Getting primary key from insert statement
At 23:10 +0300 on 07/06/1999, Michael J Davis wrote:
> I have been using this technique for years in Oracle (select next
value from
> the primary key sequence and insert using this primary key). I
like this
> approach. I don't think a transaction is needed.
I don't know what mechanism Oracle uses. Perhaps even a transaction
is not
needed (although logically, the two operations should be one, so as
not to
allow the failure of the insertion, if only to save on unused key
values).
But doing things like that on the client side means that there is no
logical connection between the sequence and the table. Anybody is
free to
enter any primary key, and a mistake in one of the front ends will
cause
inconsistency in the database. The theory of databases asserts that
you
should try to insert the logic and constraints of the organization's
data
into the backend.
I know that this should mean that in fields declared "serial",
values other
than the default should not be allowed. Perhaps this should be
addressed in
new versions of Postgres somewhere.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] Getting primary key from insert statement
1999-06-08 16:23 RE: [SQL] Getting primary key from insert statement Michael J Davis <michael.j.davis@tvguide.com>
@ 1999-06-08 16:40 ` Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 0 replies; 14+ messages in thread
From: Herouth Maoz @ 1999-06-08 16:40 UTC (permalink / raw)
To: Michael J Davis <michael.j.davis@tvguide.com>; pgsql-sql
At 19:23 +0300 on 08/06/1999, Michael J Davis wrote:
> I accomplish this by trapping the "Before Insert" event in the form used to
> add new Status records (the user is NOT entering the StatusID) and doing the
> following:
>
> Form.StatusID = getSeqValue('Status_seq')
Thus, you do it in the frontend. Six months later, a programmer writes
another form that accesses the same table, and fails to do so, or leaves it
to the user. Or he decides that he has a better number generator to use.
There are errors. Perhaps his form generates numbers which the sequence has
not yet reached. Conflicts ocuur. Boss unhappy.
That happens because you failed to centralize the logic of your database.
As I said, the "serial" datatype is not a complete fail-safe, because it
still allows entering non-sequence values. But at least it's a step in the
right direction. Putting the logic in the client side is the wrong thing to
do.
To finish in a humorous note, I wouldn't use programming practices
encouraged by Microsoft products as pillars of truth...
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] Getting primary key from insert statement
@ 1999-06-08 17:28 Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 0 replies; 14+ messages in thread
From: Michael J Davis @ 1999-06-08 17:28 UTC (permalink / raw)
To: pgsql-sql
I agree. But, in practice Access97 does a very poor (let me rephrase this,
completely unacceptable) job of handling inserts within forms if the primary
key is not included in the insert statement. In all the discussion about
what is the proper way to this, I have not yet seen an example or suggestion
that "works" in all cases with Access97. I'll bet that many of the other
third party GUI interfaces work pretty much the same way as Access97 (insert
a record and the re-select the record using the values provided in the
insert statement). If the application does not re-select the record then
updates resulting from triggers will not be shown. Most of these
applications are not smart enough to ask for the primary key of the newly
inserted record and then re-select the record based on this key. This is
because the technique for getting the primary is different for each database
if it is even available.
I am sorry you (and others) don't care for this approach, but so far, it is
the only approach that works. As for Microsoft, I dislike (okay, I even
hate some of them) many of the choices they make and I prefer to use other
products when they are available. But for the moment Access97, even with
its inherent draw backs, is one of the best database front ends on the
market for the money.
I really want to start using a real database for the backend. I have
researched many options and feel there are really only two choices worth
considering, MS SQL Server or PostgreSQL. I have chosen PostgreSQL. Please
don't make me regret it :-). Like or not, I am stuck with Access97 and
would really like to use PostgreSQL as the back end. So, we have two
choices, find solutions that work with Access97 or continue to hash out what
is the "best" way to approach these types of situations. I am willing to do
the later only as long it leads to better solutions.
Is it reasonable for me to ask others to provide viable alternatives rather
just complaining about the approaches I have chosen to take :-)? I also
think it is reasonable to expect programmers to know how to properly
interact with the database. I.e. they should know or be trained on the best
way to insert records into the database.
-----Original Message-----
From: Herouth Maoz [SMTP:herouth@oumail.openu.ac.il]
Sent: Tuesday, June 08, 1999 10:40 AM
To: Michael J Davis; pgsql-sql@postgreSQL.org
Subject: RE: [SQL] Getting primary key from insert statement
At 19:23 +0300 on 08/06/1999, Michael J Davis wrote:
> I accomplish this by trapping the "Before Insert" event in the
form used to
> add new Status records (the user is NOT entering the StatusID) and
doing the
> following:
>
> Form.StatusID = getSeqValue('Status_seq')
Thus, you do it in the frontend. Six months later, a programmer
writes
another form that accesses the same table, and fails to do so, or
leaves it
to the user. Or he decides that he has a better number generator to
use.
There are errors. Perhaps his form generates numbers which the
sequence has
not yet reached. Conflicts ocuur. Boss unhappy.
That happens because you failed to centralize the logic of your
database.
As I said, the "serial" datatype is not a complete fail-safe,
because it
still allows entering non-sequence values. But at least it's a step
in the
right direction. Putting the logic in the client side is the wrong
thing to
do.
To finish in a humorous note, I wouldn't use programming practices
encouraged by Microsoft products as pillars of truth...
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~1999-06-08 17:28 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-02 21:46 Getting primary key from insert statement Pham, Thinh <tpham@mail.priority.net>
1999-06-02 22:54 ` D'Arcy J.M. Cain <darcy@druid.net>
1999-06-06 12:31 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-06-07 07:29 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-06-07 09:51 ` Emils Klotins <emils@mail.usis.bkc.lv>
1999-06-07 20:10 RE: [SQL] Getting primary key from insert statement Michael J Davis <michael.j.davis@tvguide.com>
1999-06-08 08:26 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-06-07 22:35 RE: [SQL] Getting primary key from insert statement Jackson, DeJuan <djackson@cpsgroup.com>
1999-06-07 23:14 RE: [SQL] Getting primary key from insert statement Michael J Davis <michael.j.davis@tvguide.com>
1999-06-07 23:30 RE: [SQL] Getting primary key from insert statement Jackson, DeJuan <djackson@cpsgroup.com>
1999-06-07 23:36 RE: [SQL] Getting primary key from insert statement Michael J Davis <michael.j.davis@tvguide.com>
1999-06-08 16:23 RE: [SQL] Getting primary key from insert statement Michael J Davis <michael.j.davis@tvguide.com>
1999-06-08 16:40 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-06-08 17:28 RE: [SQL] Getting primary key from insert statement Michael J Davis <michael.j.davis@tvguide.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox