My very first PL/pgSQL procedure...

you need to use EXECUTE to do the dynamic lock table.

sql = 'LOCK TABLE ' || sequence_name || 'IN ACCESS EXCLUSIVE MODE';
EXECUTE sql;

---------- Original Message -----------
From: Philippe Ferreira <phil.f@worldonline.fr>
To: pgsql-general@postgresql.org
Sent: Wed, 25 Jan 2006 18:37:21 +0100
Subject: [GENERAL] My very first PL/pgSQL procedure...

Hi,

I've got a problem with my very first PL/pgSQL procedure !
I created the following procedure, that should reconfigure a sequence :
---------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION seq_min(sequence_name varchar, minval
integer) RETURNS VOID AS $$
DECLARE
current_seq integer;
BEGIN
LOCK TABLE sequence_name IN ACCESS EXCLUSIVE MODE;
current_seq := last_value FROM sequence_name;
IF current_seq < minval THEN
ALTER SEQUENCE sequence_name RESTART WITH minval;
END IF;
END;
$$ LANGUAGE plpgsql;
---------------------------------------------------------------------------------------------------------------------

I call it from the psql interface by :
SELECT seq_min('seq_mytable', 1029);

But PostgreSQL returns the following error (translated from french) :

ERROR: syntax error on or near �$1� at character 13
QUERY : LOCK TABLE $1 IN ACCESS EXCLUSIVE MODE
CONTEXT : PL/pgSQL function "seq_min" line 4 at SQL statement
LINE 1 : LOCK TABLE $1 IN ACCESS EXCLUSIVE MODE

So it seems that PostgreSQL have troubles handling my variable
"sequence_name"...
Any idea ?

Thank you in advance,
Philippe Ferreira, France.

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

http://www.postgresql.org/docs/faq

------- End of Original Message -------

you need to use EXECUTE to do the dynamic lock table.

sql = 'LOCK TABLE ' || sequence_name || 'IN ACCESS EXCLUSIVE MODE';
EXECUTE sql;

Thank you for your help ;-)
I've been able to rewrite my procedure as follows :

---------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION seq_min(sequence_name varchar, minval
integer) RETURNS VOID AS $$
DECLARE
current_seq integer;
BEGIN
EXECUTE 'LOCK TABLE ' || sequence_name || ' IN ACCESS EXCLUSIVE MODE';
current_seq := last_value FROM sequence_name;
IF current_seq < minval THEN
EXECUTE 'ALTER SEQUENCE ' || sequence_name || ' RESTART WITH ' ||
minval;
END IF;
END;
$$ LANGUAGE plpgsql;
---------------------------------------------------------------------------------------------------------------------

However, when I call : "SELECT seq_min('seq_mytable', 1029);"
I get this other error (translated from french) :

ERROR: �seq_mytable� is not a table
CONTEXT : SQL instruction �LOCK TABLE seq_mytable IN ACCESS EXCLUSIVE
MODE�
PL/pgSQL function "seq_min" line 4 at execute statement

So, it seems that it is impossible to lock a sequence !
If it is the case, how can I achieve the same result without locking the
sequence ?

Thank you again,
Philippe Ferreira.

Hi,

The only solution I've found to get the same reliable result, but
without locking, is the dirty way (loops) :
---------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION seq_min(sequence_name varchar, minval
integer) RETURNS VOID AS $$
DECLARE
sequence_record RECORD;
BEGIN
-- Get the current sequence value :
FOR sequence_record IN EXECUTE 'SELECT last_value FROM ' ||
sequence_name LOOP
NULL;
END LOOP;
-- Loop to bring the sequence to (at least) minval :
WHILE sequence_record.last_value < minval LOOP
-- Increment by 1 the sequence (and get the new value) :
FOR sequence_record IN EXECUTE 'SELECT nextval(''' || sequence_name
|| ''') AS last_value' LOOP
NULL;
END LOOP;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
---------------------------------------------------------------------------------------------------------------------

It gives the result I expect (and it doesn't interfere with concurrent
uses of the sequence), but it is not very optimized !
So, if someone have a better idea, I'm still open !

Thank you,
Philippe Ferreira.

why not just use setval(), see docs for arguments.

---------- Original Message -----------
From: Philippe Ferreira <phil.f@worldonline.fr>
To: pgsql-general@postgresql.org
Sent: Wed, 25 Jan 2006 22:11:11 +0100
Subject: Re: [GENERAL] My very first PL/pgSQL procedure...

Hi,

The only solution I've found to get the same reliable result, but
without locking, is the dirty way (loops) :
---------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION seq_min(sequence_name varchar, minval
integer) RETURNS VOID AS $$
DECLARE
sequence_record RECORD;
BEGIN
-- Get the current sequence value :
FOR sequence_record IN EXECUTE 'SELECT last_value FROM ' ||
sequence_name LOOP
NULL;
END LOOP;
-- Loop to bring the sequence to (at least) minval :
WHILE sequence_record.last_value < minval LOOP
-- Increment by 1 the sequence (and get the new value) :
FOR sequence_record IN EXECUTE 'SELECT nextval(''' || sequence_name
|| ''') AS last_value' LOOP
NULL;
END LOOP;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
---------------------------------------------------------------------------------------------------------------------

It gives the result I expect (and it doesn't interfere with concurrent
uses of the sequence), but it is not very optimized !
So, if someone have a better idea, I'm still open !

Thank you,
Philippe Ferreira.

---------------------------(end of broadcast)---------------------------
TIP 1: 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 Original Message -------

why not just use setval(), see docs for arguments.

I think that "setval('seq_name', xx)" have the same effect than
"SEQUENCE seq_name RESTART WITH xx" (the instruction I
wanted to use in my first function).

But the problem is that in both cases, the sequence should be
locked in order to prevent problems with concurrent transactions.
For example, if I want to raise the sequence value to "1000", while
its current value is "998", I would call :
setval('seq_name', 1000);

But because the sequence could not be locked, some concurrent
transactions could have already raised it's current value in the
meantime to, say, "1002", before the effective execution of setval().

So, instead of raising the value to 1000, my function could have
done the opposite (from 1002 to 1000) ! And the two next "INSERT"
using this sequence would then break !!

The only solution I found to prevent this is with my "loops" !!

Best regards,
Philippe Ferreira.

On Thu, Jan 26, 2006 at 09:04:25PM +0100, Philippe Ferreira wrote:

But because the sequence could not be locked, some concurrent
transactions could have already raised it's current value in the
meantime to, say, "1002", before the effective execution of setval().

Umm, locking sequences won't ever happen. The *whole point* of
sequences is that you get a unique number and you don't have to wait
for it. By locking you'd be telling other people they have to wait.

So, instead of raising the value to 1000, my function could have
done the opposite (from 1002 to 1000) ! And the two next "INSERT"
using this sequence would then break !!

I think you need to provide a rationale why you want to control the
value of the sequence in such a way anyway, because you're trying to do
something that the system isn't likely to support. The numbers are
supposed to be "opaque", the actual values are not supposed to be
relevent.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

I think you need to provide a rationale why you want to control the
value of the sequence in such a way anyway, because you're trying to do
something that the system isn't likely to support. The numbers are
supposed to be "opaque", the actual values are not supposed to be
relevent.

Have a nice day,

Hi,

Actually, I'm building a switchover/failover system (based on WAL) for
my own needs.

There is no problem for the switchover, because the data is properly
synched during
this procedure.

However, when I have to start the failover on a secondary server, some
changes can
be "uncommited" at the time of the failover.
So, as soon as the primary PostgreSQL server is up again, I have to resync
(automatically or manually) these "uncommited" changes to the failover
node, before
switching properly to the primary.

Actually, I've got a sequence which generates order numbers. These codes
will be
always unique, even in case of failover, since I alter the definition of the
sequence depending on the server (primary or secondary), with a suffix
of 'A' or 'B'.
For example, imagine the following scenario :

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

Server A : Server B :
========== ==========
1000A
1001A
1002A
---[SWITCHOVER]-->
1003B
1004B
1005B
1006B
<--[SWITCHOVER]---
1007A
1008A
1009A
1010A [FAILOVER]--> 1010B
1011A 1011B
1012A -
1013A -
[RESYNC]--> 1010A
[RESYNC]--> 1011A
[RESYNC]--> 1012A
[RESYNC]--> 1013A
<--[SWITCHOVER]---
1014A
1015A

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

So, in this example, if I don't increment the last value of my
sequence from 1011 to 1013 (before my last "SWITCHOVER"), it
will output "1012A" (instead of 1014A), and my new record will
conflict with an existing one !!

Thank you for your interest,
Philippe Ferreira.

why not have server a start at 1 and server b start at max int8/2. that will give each server alot of orders before
you have an issue. They will will not have to play games with the sequence value.

Jim

---------- Original Message -----------
From: Philippe Ferreira <phil.f@worldonline.fr>
To: Martijn van Oosterhout <kleptog@svana.org>
Cc: jim@contactbda.com, pgsql-general@postgresql.org
Sent: Sat, 28 Jan 2006 17:46:55 +0100
Subject: Re: [GENERAL] My very first PL/pgSQL procedure...

I think you need to provide a rationale why you want to control the
value of the sequence in such a way anyway, because you're trying to do
something that the system isn't likely to support. The numbers are
supposed to be "opaque", the actual values are not supposed to be
relevent.

Have a nice day,

Hi,

Actually, I'm building a switchover/failover system (based on WAL) for
my own needs.

There is no problem for the switchover, because the data is properly
synched during
this procedure.

However, when I have to start the failover on a secondary server, some
changes can
be "uncommited" at the time of the failover.
So, as soon as the primary PostgreSQL server is up again, I have to resync
(automatically or manually) these "uncommited" changes to the failover
node, before
switching properly to the primary.

Actually, I've got a sequence which generates order numbers. These codes
will be
always unique, even in case of failover, since I alter the definition of the
sequence depending on the server (primary or secondary), with a suffix
of 'A' or 'B'.
For example, imagine the following scenario :

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

Server A : Server B :
========== ==========
1000A
1001A
1002A
---[SWITCHOVER]-->
1003B
1004B
1005B
1006B
<--[SWITCHOVER]---
1007A
1008A
1009A
1010A [FAILOVER]--> 1010B
1011A 1011B
1012A -
1013A -
[RESYNC]--> 1010A
[RESYNC]--> 1011A
[RESYNC]--> 1012A
[RESYNC]--> 1013A
<--[SWITCHOVER]---
1014A
1015A

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

So, in this example, if I don't increment the last value of my
sequence from 1011 to 1013 (before my last "SWITCHOVER"), it
will output "1012A" (instead of 1014A), and my new record will
conflict with an existing one !!

Thank you for your interest,
Philippe Ferreira.

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

------- End of Original Message -------

why not have server a start at 1 and server b start at max int8/2. that will give each server alot of orders before
you have an issue. They will will not have to play games with the sequence value.

Jim

Hi,

Your method is different from mine, but I think that both methods will
face the same constraints in case of failover...

Anyway, my setup is working fine, so I won't change anything !
The only point is that it could have been useful to be able to lock a
sequence...
It seems that it was possible with PostgreSQL 7.0, but was removed in 7.1 :
http://archives.postgresql.org/pgsql-hackers/2001-10/msg00928.php

Best regards,
Philippe Ferreira.