<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><meta content="text/html;charset=UTF-8" http-equiv="Content-Type"></head><body ><div style='font-size:10pt;font-family:Verdana,Arial,Helvetica,sans-serif;'><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;">Hi,<br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;">we're testing pgpool setup with two servers with streaming replication. Everything works alright, but we've encountered an issue with advisory locks.<br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;">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:<br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><a href="http://www.postgresql.org/docs/9.1/static/sql-begin.html" target="_blank">BEGIN</a>;<br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><a href="http://www.postgresql.org/docs/9.1/static/sql-select.html" target="_blank">SELECT</a><span class="Apple-converted-space"> </span>* FROM pg_locks WHERE locktype = 'advisory';<br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><a href="http://www.postgresql.org/docs/9.1/static/sql-select.html" target="_blank">SELECT</a><span class="Apple-converted-space"> </span>pg_try_advisory_xact_lock(123, 0);<br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><a href="http://www.postgresql.org/docs/9.1/static/sql-select.html" target="_blank">SELECT</a><span class="Apple-converted-space"> </span>* FROM pg_locks WHERE locktype = 'advisory';<br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><a href="http://www.postgresql.org/docs/9.1/static/sql-rollback.html" target="_blank">ROLLBACK</a>;<br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;">where the second select does not return any record in some of the cases.<br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;">The documentation describes the rules pgpool uses to determine which queries can be sent to standby and which must go to primary (<a href="http://www.pgpool.net/docs/latest/pgpool-en.html#load_balance_in_stream_mode" target="_blank">http://www.pgpool.net/docs/latest/pgpool-en.html#load_balance_in_stream_mode</a>). 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?<br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;">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?<br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;">Best regards,<br></div><div style="color: rgb(0, 0, 0); font-family: verdana, arial, helvetica, sans-serif; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;">Štěpán Pilař<br></div></div></body></html>