Re: My very first PL/pgSQL procedure...

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