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

[Itamar - IspBrasil]

why you don't use autoincrement / serial ?


On 9/16/2008 10:46 AM, Andrea Moretto wrote:
> 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.
> -------------------------------------------------------
>
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general
>
>
>