Thread: 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.

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/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

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