Re: Lock strategies! - Mailing list pgsql-general

How can you avoid holes?

Unless you void policies that people cancel halfway through the process
? How is that different than rollback?

Lets say that the customer goes through the motions and after signing
the papers, and then during the cooling off period (mandatory in Canada)
decides he really doesn't want the policy (rollback). A policy number
must have been assigned. So now we have a hole ?

Dave

On Tue, 2003-11-25 at 19:07, Uwe C. Schroeder wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Obviously depends on the carrier. Lloyds for example doesn't allow numbering
> gaps. But as said: doing it in a fully isolated stored proc usually works.
> The stp I use also assembles the alpha part, so I end up with something like
> AA-0001234 in a fixed width format.
>
>
> On Tuesday 25 November 2003 08:40 am, Marc A. Leith wrote:
> > Actually, in practice Policy & Certificate 'numbers' only need to be
> > unique. Insurance companies (at least those we deal with) have no
> > restriction that there can be no holes. In fact, one of our clients has a
> > huge gap in the sequence.
> >
> > Likewise - they aren't usually strictly numeric, consisting of ALPHA and
> > NUMERIC components. Ie. AA000001 AA000002 ... AA999999 AB000001.
> >
> > A better example - is Invoice Numbers. Accountants hate the gaps, since
> > they leave room for fraud and make collection difficult.
> >
> > That said - our implementation for unique ids is either use sequences or to
> > encapsulate the logic in a Stored Proc. and ensure that these tranasactions
> > are fully isolated.
> >
> > Marc A. Leith
> > redboxdata inc.
> >
> > E-mail:mleith@redboxdata.com
> >
> > Quoting "Uwe C. Schroeder" <uwe@oss4u.com>:
> > > -----BEGIN PGP SIGNED MESSAGE-----
> > > Hash: SHA1
> > >
> > > On Monday 24 November 2003 08:01 am, Dave Cramer wrote:
> > > > Marcelo,
> > > >
> > > > You are asking for the impossible.
> > > >
> > > > In order for sequences to work reliably they have to exist outside of a
> > > > transaction, and be atomic. If two transactions asked for a sequence
> > > > simultaneously, what number would you give them? If the first one gets
> > > > 1, and the second gets 2 how do you roll back the first one and then
> > > > give the second one 1?
> > > >
> > > > And it gets worse, what happens if 10 connections ask for one
> > > > simultaneously and then connection 3 7 rollback?
> > > >
> > > > I don't know how to say this gently, but usually this requirement
> > > > suggests that more thinking is required on the application end.
> > >
> > > Well, there are cases where you have to have the numbers without holes -
> > > no matter what. It's not even a matter of the application. Go check your
> > > insurance policy: the policy numbers are sequential without holes.
> > > Actually you can make that work via stored procedures. But you'd have to
> > > lock the table exclusive to avoid duplicates. This still might produce
> > > numbering gaps,
> > >
> > > but you can have the application compensate for that, i.e. if you have a
> > > rollback remember the number someplace else and reuse it for the next
> > > record.
> > >
> > > > Dave
> > > >
> > > > On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote:
> > > > > Dave,
> > > > >
> > > > > I actually use just the sequence, as you wrote!
> > > > >
> > > > > The biggest problem it that I *can't* have holes in
> > > > > that column, so it was because I used id (serial) and
> > > > > forn_id (integer).
> > > > >
> > > > > All other tables use only the sequence by itself, but
> > > > > this one, especially, CAN'T have holes! It is the
> > > > > problem!!! ;-)
> > > > >
> > > > > So, if I rollback or whatever, the ID will be
> > > > > populated with the sequence values, but the forn_id
> > > > > must increase in a controled way, ie, without holes!
> > > > >
> > > > > Advices??????
> > > > >
> > > > > Regards!
> > > > >
> > > > > Marcelo
> > > > >
> > > > >  --- Dave Cramer <pg@fastcrypt.com> escreveu: >
> > > > > Marceio
> > > > >
> > > > > > The sequence logic takes care of it. try it yourself
> > > > > >
> > > > > > open two connections with psql
> > > > > >
> > > > > > on one do a
> > > > > > begin;
> > > > > > insert into table
> > > > > > select curval('forn_id_seq');
> > > > > >
> > > > > > on the other
> > > > > >
> > > > > > do a
> > > > > > begin
> > > > > > insert into table
> > > > > > select curval('forn_id_seq');
> > > > > >
> > > > > >
> > > > > > You will see that they both increment the sequence
> > > > > > number
> > > > > >
> > > > > > you will also see how to get the current value as
> > > > > > well.
> > > > > >
> > > > > > Note, no locking is actually required, you can do
> > > > > > this without the
> > > > > > transaction stuff, it is there just so you can see
> > > > > > it in two sessions at
> > > > > > the same time.
> > > > > >
> > > > > > Also note that a rollback will NOT roll back the
> > > > > > sequence number, this
> > > > > > will end up with holes but sequences are not
> > > > > > guaranteed to not have
> > > > > > holes.
> > > > > >
> > > > > > Why do you have two columns, id, and forn_id, you
> > > > > > only need one.
> > > > > >
> > > > > > and then do an
> > > > > >
> > > > > > insert into forn (descrip) values ( 'some
> > > > > > description' );
> > > > > > then select curval('forn_id_seq');
> > > > > >
> > > > > > forn_id will be populated for you with the value
> > > > > > from curval.
> > > > > >
> > > > > >
> > > > > > Dave
> > > > > >
> > > > > > On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote:
> > > > > > > Hi guys,
> > > > > > >
> > > > > > > I have a simple table:
> > > > > > >
> > > > > > > teste=# \d forn
> > > > > > >                            Table "public.forn"
> > > > > > >  Column  |  Type   |
> > > > > >
> > > > > > Modifiers
> > >
> > > ---------+---------+-----------------------------------------------------
> > >
> > > > >-
> > > > >
> > > > > > >  id      | integer | not null default
> > > > > > > nextval('public.forn_id_seq'::text)
> > > > > > >  forn_id | integer |
> > > > > > >  descrip | text    |
> > > > > > >
> > > > > > > Ok! The forn_id is supposed to be sequencial and
> > > > > > > without holes (if someone perform a DELETE or
> > > > > >
> > > > > > UPDATE,
> > > > > >
> > > > > > > so there will be a hole... no problem if the hole
> > > > > > > happens in this case!).
> > > > > > >
> > > > > > > Well, to know the next value of the forn_id
> > > > > >
> > > > > > column, it
> > > > > >
> > > > > > > was planned to be done like this:
> > > > > > >
> > > > > > > teste=# INSERT INTO forn (forn_id,descrip) VALUES
> > > > > > > ((SELECT max(forn_id) FROM forn),'descrip1');
> > > > > > >
> > > > > > > It will cause a huge delay in case this table
> > > > > >
> > > > > > became
> > > > > >
> > > > > > > huge, because the forn_id isn't an indexed column
> > > > > >
> > > > > > (but
> > > > > >
> > > > > > > I would index it! The problem I am talking about
> > > > > >
> > > > > > is
> > > > > >
> > > > > > > ONLY about the sequence of numbers).
> > > > > > >
> > > > > > > As a way to be sure it will not another other
> > > > > >
> > > > > > client
> > > > > >
> > > > > > > getting the exact value as the max(forn_id), there
> > > > > >
> > > > > > was
> > > > > >
> > > > > > > a dirty thing:
> > > > > > >
> > > > > > > teste=# BEGIN;
> > > > > > > teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE;
> > > > > > > teste=# INSERT INTO ...
> > > > > > > teste=# COMMIT;
> > > > > > >
> > > > > > > Well, I really think it is not the best way to do
> > > > > >
> > > > > > that
> > > > > >
> > > > > > > and I am asking you for advices!
> > > > > > >
> > > > > > > 1) Is it (... max(forn_id)... ) the best way to
> > > > > >
> > > > > > get
> > > > > >
> > > > > > > the next value to be inserted in the table?
> > > > > > >
> > > > > > > 2) Is there a automatic way to do that?
> > > > > > >
> > > > > > > Thanks in advance and
> > > > > > > Best Regards,
> > > > > > >
> > > > > > > Marcelo
> > > > >
> > > > > _____________________________________________________________________
> > > > >_
> > > > >
> > > > > > > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito!
> > > > > >
> > > > > > Crie sua conta agora:
> > > > > > > http://mail.yahoo.com.br
> > > > > > >
> > > > > > > ---------------------------(end of
> > > > > >
> > > > > > broadcast)---------------------------
> > > > > >
> > > > > > > TIP 4: Don't 'kill -9' the postmaster
> > > > >
> > > > > _____________________________________________________________________
> > > > >_
> > > > >
> > > > > Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta
> > > > > agora: http://mail.yahoo.com.br
> > > >
> > > > ---------------------------(end of
> > > > broadcast)--------------------------- TIP 4: Don't 'kill -9' the
> > > > postmaster
> > >
> > > - --
> > >     UC
> > >
> > > - --
> > > Open Source Solutions 4U, LLC    2570 Fleetwood Drive
> > > Phone:  +1 650 872 2425        San Bruno, CA 94066
> > > Cell:   +1 650 302 2405        United States
> > > Fax:    +1 650 872 2417
> > > -----BEGIN PGP SIGNATURE-----
> > > Version: GnuPG v1.2.1 (GNU/Linux)
> > >
> > > iD8DBQE/w0/bjqGXBvRToM4RAvZJAJ4980r/Cp+jWSTrHpq7kBRiPpUTIwCfcTUF
> > > It3bBNKywCxc3FzOzr7FSyA=
> > > =TSWf
> > > -----END PGP SIGNATURE-----
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> - --
>     UC
>
> - --
> Open Source Solutions 4U, LLC    2570 Fleetwood Drive
> Phone:  +1 650 872 2425        San Bruno, CA 94066
> Cell:   +1 650 302 2405        United States
> Fax:    +1 650 872 2417
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.1 (GNU/Linux)
>
> iD8DBQE/w+7EjqGXBvRToM4RAvyFAJ4m5WghKaTVdoUDBm4S56HhcgYlJACfVBiu
> V/hGezsXsywrsaNdWvrzp1g=
> =2S5x
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>