Lock strategies!

[MaRcElO PeReIrA]

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

[Marc A. Leith]

I think that defining forn_id as "serial" is what you are looking for.

This will handle the assignment of unique numbers to the id for you (it creates
a sequence table).

The locking stategy is fraught with danger... and unnecessary.

Marc A. Leith
redboxdata inc.

E-mail:ml****@redboxdata.com
Quoting MaRcElO PeReIrA <ga********@yahoo.com.br>:

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Jeff]

On Mon, 24 Nov 2003 10:20:07 -0300 (ART)
MaRcElO PeReIrA <ga********@yahoo.com.br> 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 |

Why not make forn_id a sequence as well?
then you simply call nextval('forn_id_seq')
--
Jeff Trout <je**@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Dave Cramer]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Jeff]

On Mon, 24 Nov 2003 12:48:26 -0300 (ART)
MaRcElO PeReIrA <ga********@yahoo.com.br> 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).

Well, if you cannot use a sequence you will have no choice but to use
locking.

don't use max - it isn't fast on PG use select forn_id from thetable
order by fornid desc limit 1. You'll need an index on forn_id or
performance will suffer.

--
Jeff Trout <je**@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Björn Lundin]

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

You could maintain some sort of systemnumber table yourself

create table sysnum (
first int not null,
next int not null,
last int not null
latest_updater text not null,
the_time timestamp? not null);
and get your serial number from the next column.

However, this strategy demands the same logic from
all programs using the table:

pseudo Ada code

loop
begin transaction
select * from sysnum into some Adarecord;
update sysnum
set next=next+1
latest_updater = The_pid_or_name_of_your_process_or_thread
the_time=now (with good enough acurracy)
where
latest_updater = Adarecord.latest_updater and
The_time = Adarecord.The_time;

if Rows_Affected = 0 then
Rollback transaction;
else
commit transaction:
exit
end if;
(perhaps a small delay, say 0.05 sec?)
end loop;
you can get Rows_affected from PQ_Cmd_Tuples

if Rows_affected is 0 then you have a transaction conflict,
and must start all over again, to get a unique value.

What this does to performance, I don't know, but I do know it works,
IF AND ONLY IF all processes follow the same rule.

There should proberly be some code to handle when
you fall over the edge, ie next > last => next = first

/Björn