Re: checking update/insert return

On Tue, 2004-01-06 at 11:40, Marcus Andree S. Magalhaes wrote:
> > Hi,
> >
> > Why do you not use the serial data type : SERIAL ?
> >
>
> I must guarantee to the client-side that the ID field
> has no holes....

If you _really_ have to do that, then the only way [I have thought of
over the years] to do it in a transaction safe manner is to pre-allocate
numbers, creating records (just the ID number) in a second table.  Like
pulling raffle tickets out of a book.

Then, when you want a number, you:

BEGIN
  SELECT first unused pre-allocated number FOR UPDATE
  DELETE the pre-allocated number
  INSERT empty 'it-didn't-happen-yet' record with pre-allocated number
COMMIT

BEGIN
  ...
  do other important stuff
  ...
COMMIT

Then, if you roll back the first transaction, the DELETE never happened,
and the INSERT never happened, so the next transaction comes along and
uses that code.

If you roll back the second transaction, then you are still left with an
'empty' record in your table, but there _is_ a record there.  You could
either (a) leave it like that, and be happy, or (b) have a process which
goes along later and removes them while re-pre-allocating the number,
which I would say is probably more pain than it's worth.  Of course the
whole thing is more pain than it's worth, really :-)

Of course something this does not address is _ordering_ of these
records, but you can't guarantee ordering on a multi-user system anyway
unless you make it stop being multi-user for a while.

In the past when people have asked me for this "every code is used"
approach (usually accountants) I've told them "No!", and then baffled
them with justification about how hard it is until their eyes glazed
over.

Then, of course, when I've got them off my back I just can't help
worrying at the problem until I get some sort of half-assed solution
together :-)

Cheers,
                    Andrew McMillan.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053,  Manners St,  Wellington
WEB: http://catalyst.net.nz/             PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201       MOB: +64(21)635-694      OFFICE: +64(4)499-2267
                     The Killer Ducks are coming!!!

-------------------------------------------------------------------------