<div dir="ltr">Hi,<br><br> The docs [1] say that one of the preconditions for load-balancing is that<br><br> > The query must not be in an explicitly declared transaction (i.e. not in a BEGIN ~ END block)<br><br> But I see that this is not a strict statement. I extracted a specific backend process' log lines (generated using <span style="border-collapse:collapse;color:rgb(34,34,34);font-size:13px">log_per_node_statement), and I could see that on line 6, the SELECT query was load-balanced to a replica (node id 2) and the </span>next INSERT statement was correctly sent to the master. But any SELECT after that INSERT was not sent to the replica, and only to the master.<br>
<br> So can we say that load balancing _does_ occur in an explicitly declared transaction, but as soon as a DML operation is perfored, any subsequent SELECT queries will be sent only to master.<br><br>pid 16880: DB node id: 0 backend pid: 17106 statement: BEGIN<br>
pid 16880: DB node id: 2 backend pid: 30612 statement: BEGIN<br>pid 16880: DB node id: 0 backend pid: 17106 statement: SELECT count(*) FROM pg_class AS c, ...<br>pid 16880: DB node id: 0 backend pid: 17106 statement: SELECT count(*) FROM pg_class AS c, ...<br>
pid 16880: DB node id: 0 backend pid: 17106 statement: SELECT count(*) FROM pg_catalog.pg_class AS c...<br>pid 16880: DB node id: 2 backend pid: 30612 statement: SELECT (1) AS "a" FROM "photo_thumbnailtocreate" WHERE ...<br>
pid 16880: DB node id: 0 backend pid: 17106 statement: INSERT INTO "photo_thumbnailtocreate" ("id", ...<br>pid 16880: pool_send_and_wait: Error or notice message from backend: : DB node id: 0 backend pid: 17106 statement: INSERT INTO "photo_thumbnailtocreate" ...<br>
pid 16880: DB node id: 0 backend pid: 17106 statement: SELECT (1) AS "a" FROM "photo_thumbnailtocreate" WHERE ...<br>pid 16880: pool_send_and_wait: Error or notice message from backend: : DB node id: 0 backend pid: 17106 statement: SELECT (1) AS "a" FROM "photo_thumbnailtocreate" ...<br>
pid 16880: DB node id: 0 backend pid: 17106 statement: SELECT "django_site"."id", "django_site"."domain", "django_site"."name" FROM "django_site" WHERE "django_site"."id" = 1 <br>
pid 16880: pool_send_and_wait: Error or notice message from backend: : DB node id: 0 backend pid: 17106 statement: SELECT "django_site"."id", "django_site"."domain", "django_site"."name" FROM "django_site" WHERE "django_site"."id" = 1 message: current transaction is aborted, commands ignored until end of transaction block<br>
...<br clear="all"><br>[1] <a href="http://www.pgpool.net/docs/latest/pgpool-en.html#condition_for_load_balance">http://www.pgpool.net/docs/latest/pgpool-en.html#condition_for_load_balance</a><br><br>Best regards,<br>-- <br>
<div dir="ltr">Gurjeet Singh<br><br><a href="http://gurjeet.singh.im/" target="_blank">http://gurjeet.singh.im/</a><br></div><br>
</div>