Re: Lock strategies! - Mailing list pgsql-general

It seems to me there is a confusion about identifiers. There is the primary
key of the table which should be a sequence and may have holes. Seperate
from that is the CustomerFriendlyID which is an ID you can assign and
reassign at your leasure. For a bank, the statement numbers all start from one
for each customer so they're not useful as a global identifier anyway.

In your case below, once you've signed the paper-work, the policy is a legal
document and would need to be kept even if it never was activated.

IMHO, most people looking for no-hole-sequences are using the primary keys
for Bad Things (tm).

Hope this helps,

On Tue, Nov 25, 2003 at 10:19:20PM -0500, Dave Cramer wrote:
> 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
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato