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(dot)f(at)worldonline(dot)fr>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: jim(at)contactbda(dot)com, pgsql-general(at)postgresql(dot)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 -------