[pgpool-general: 4516] pgpool and advisory locks

[Štěpán Pilař]

Hi,



we're testing pgpool setup with two servers with streaming replication. Everything works alright, but we've encountered an issue with advisory locks.



When our application uses advisory locks, in some cases two processes can acquire the same lock (i.e. process A begins a transaction, acquires the lock, process B begins a transaction, acquires the same lock, process A commits, process B runs a query which blows up). We reduced the situation to:



BEGIN;

SELECT * FROM pg_locks WHERE locktype = 'advisory';

SELECT pg_try_advisory_xact_lock(123, 0);

SELECT * FROM pg_locks WHERE locktype = 'advisory';

ROLLBACK;



where the second select does not return any record in some of the cases.



The documentation describes the rules pgpool uses to determine which queries can be sent to standby and which must go to primary (http://www.pgpool.net/docs/latest/pgpool-en.html#load_balance_in_stream_mode). Among those that are always handled by the primary is LOCK command. Advisory locks are, however, acquired through a SELECT. Are we correct to think the problem lies in SELECT being routed to either of those two instances?



If so, is there a simple solution to ensure consistent behavior in our setup? The documentation mentions a possibility to provide rules to route certain queries to specific nodes, but I haven't found an example of such configuration. Are there any resources available?



Best regards,

Štěpán Pilař


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20160304/d35056cf/attachment.html>