[Pgpool-general] Pgpool-II and Slony-I : query lag, sequences and stored procedures need help

[Andrea Moretto]

Hello there!

I am currently evaluating the best solution for a high-availability,  
load balanced cluster.
Currently I am testing this environment : postgres 8.3.1, pgpool-II  
2.1, Slony-I 1.2.14.
I've setup a master server to replicate all tables with Slony-I to a  
slave, and a frontend with pgpool
that load-balances the queries (load_balance=true,  
replication_mode=false, parallel_query=false,
enable_query_cache=false).

There is a problem : a stored procedure that returns a sequence value  
(a global unique ID, used as primary key in INSERTs)
returns the same values in successive calls, under certain conditions.  
This is clearly due to the replication lag led by Slony-I.
I would call it a race condition.

Now the question : is there a way to force pgpool to redirect all  
queries that involves a specific sequence or a stored procedure
to a specific backend? I tried to setup query partitioning, but only  
tables are supported so far.

I know that all queries belonging to a single transaction should be  
redirected to the same backend, but I would like to
find out a solution working on the backend, avoiding to check a huge  
amount of code that works. ;)
I also know that using the pgpool replication mode will solve the  
issue, but it can lead to downtime when adding new backends,
since a synch operation is required. Slony-I implement replication in  
a more suitable way from this point of view.

Thanks in advance!

Regards,

   Andrea Moretto

Andrea Moretto
moretto.andrea at gmail.com
-------------------------------------------------------
CONFIDENTIALITY NOTICE
This message and its attachments are addressed solely to the persons
above and may contain confidential information. If you have received
the message in error, be informed that any use of the content hereof
is prohibited. Please return it immediately to the sender and delete
the message.
-------------------------------------------------------