RE: [SQL] Getting primary key from insert statement - Mailing list 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
> > > 
> > >