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

[Tatsuo Ishii]

Sorry for late reply.

> 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)

Yeah, the doc is outdated in this regard.

>     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.

Yes, that is expected behavior if you are using streaming replication
mode. The reason why SELECTs are sent to master after DML issued is,
standby cannot see the modified rows.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> 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/