pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jackson, DeJuan <djackson@cpsgroup.com>
To: Michael J Davis <michael.j.davis@tvguide.com>
To: 'Herouth Maoz' <herouth@oumail.openu.ac.il>
To: emils@mail.usis.bkc.lv
To: pgsql-sql@postgreSQL.org
Subject: RE: [SQL] Getting primary key from insert statement
Date: Mon, 7 Jun 1999 17:35:24 -0500
Message-ID: <D05EF808F2DFD211AE4A00105AA1B5D21F2531@cpsmail> (raw)

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
> > 
> > 



view thread (14+ messages)  latest in thread

Message-ID: <D05EF808F2DFD211AE4A00105AA1B5D21F2531@cpsmail>
Permalink:  ../D05EF808F2DFD211AE4A00105AA1B5D21F2531@cpsmail/
Also on:    postgresql.org/message-id/D05EF808F2DFD211AE4A00105AA1B5D21F2531@cpsmail

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: djackson@cpsgroup.com, michael.j.davis@tvguide.com, herouth@oumail.openu.ac.il, emils@mail.usis.bkc.lv
  Subject: Re: [SQL] Getting primary key from insert statement
  In-Reply-To: <D05EF808F2DFD211AE4A00105AA1B5D21F2531@cpsmail>
  References: <D05EF808F2DFD211AE4A00105AA1B5D21F2531@cpsmail>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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