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