[pgpool-hackers: 139] Do we need to rephrase the precondition for load-balancing?

[Gurjeet Singh]

Hi,

    The docs [1] say that one of the preconditions for load-balancing is
that

    > The query must not be in an explicitly declared transaction (i.e. not
in a BEGIN ~ END block)

    But I see that this is not a strict statement. I extracted a specific
backend process' log lines (generated using 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 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.

    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.

pid 16880: DB node id: 0 backend pid: 17106 statement: BEGIN
pid 16880: DB node id: 2 backend pid: 30612 statement: BEGIN
pid 16880: DB node id: 0 backend pid: 17106 statement: SELECT count(*) FROM
pg_class AS c, ...
pid 16880: DB node id: 0 backend pid: 17106 statement: SELECT count(*) FROM
pg_class AS c, ...
pid 16880: DB node id: 0 backend pid: 17106 statement: SELECT count(*) FROM
pg_catalog.pg_class AS c...
pid 16880: DB node id: 2 backend pid: 30612 statement: SELECT (1) AS "a"
FROM "photo_thumbnailtocreate" WHERE ...
pid 16880: DB node id: 0 backend pid: 17106 statement: INSERT INTO
"photo_thumbnailtocreate" ("id", ...
pid 16880: pool_send_and_wait: Error or notice message from backend: : DB
node id: 0 backend pid: 17106 statement: INSERT INTO
"photo_thumbnailtocreate" ...
pid 16880: DB node id: 0 backend pid: 17106 statement: SELECT (1) AS "a"
FROM "photo_thumbnailtocreate" WHERE ...
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" ...
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
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
...

[1]
http://www.pgpool.net/docs/latest/pgpool-en.html#condition_for_load_balance

Best regards,
-- 
Gurjeet Singh

http://gurjeet.singh.im/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20121018/bc828be8/attachment.html>