RE: [INTERFACES] locking on database updates - Mailing list pgsql-interfaces

This sounds like a question posted a few months ago (sept/oct 99) in this list. Look at the archive, I'm sure you'll
havean positive answer (AFAIR) on that point.
 

Nicolas Huillard

-----Message d'origine-----
De:    Gary Stainburn [SMTP:gary.stainburn@ringways.co.uk]
Date:    mardi 7 décembre 1999 17:35
À:    'Pgsql Interfaces'
Objet:    RE: [INTERFACES] locking on database updates

Hi all,

I've just read the last message I sent and apologise for the poor English.
Let me put it a little clearer.

I agree that using a type 'serial' will give me what I want 
regarding the key field for my table, and gets round the problem
of concurrent users trying to create records with the same number.

The problem is that this does not actually give me the number that
was just created.  As there is no guarantee that the other fields in
the table are unique (hence the serial field in the first place) I cannot
simply do a select to retrieve it.

Has anyone any ideas?

Thanks,

Gary
-----Original Message-----
From:    Gary Stainburn [SMTP:gary.stainburn@ringways.co.uk]
Sent:    Tuesday, December 07, 1999 12:21 PM
To:    'dougt@mugc.cc.monash.edu.au'
Cc:    'Pgsql Interfaces'
Subject:    RE: [INTERFACES] locking on database updates


-----Original Message-----
From:    Douglas Thomson [SMTP:dougt@mugc.cc.monash.edu.au]
Sent:    Tuesday, December 07, 1999 11:16 AM
To:    Gary Stainburn
Subject:    Re: [INTERFACES] locking on database updates

!> Is there any way to easily retrieve the sequence number just created =
!> without having the same contension problems I originally wanted to =
!> avoid?
!
!Do you need to find out the next value that will be used before you
!use it? If so there may be a problem...
!
!However, if you only need to do inserts and find out afterwards what
!sequence number got used then there is no problem. Postgres looks
!after making sure multiple simultaneous backend processes don't
!duplicate sequence numbers. I think it actually reserves a few
!numbers in the sequence for each process, so it is possible to get
!small gaps in the sequence, or for the sequence numbers to be not
!strictly chronological...
!
!As for the how - well, I simply selected back the row I had just
!inserted to extract the sequence number column :-)
!
!Doug.

It's not important that I know the number beforehand.  However, as 
this is the only field that is guaranteed to be unique, getting the 
sequence number using  a select statement may not work.
It doesn't matter
-----------------------------------------
Gary Stainburn.
Work: http://www.ringways.co.uk gary.stainburn@ringways.co.uk
REVCOM: http://www.revcom.dhs.org http://www.revcom.org.uk gary.stainburn@revcom.org.uk
-----------------------------------------
The nice thing about standards is that there are so many of them to choose from. -- Andrew S. Tanenbaum
-----------------------------------------


************