Re: Lock strategies! - Mailing list pgsql-general

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