[pgpool-general: 1241] Re: Dropping template1 through PGPool not possible

[Tatsuo Ishii]

For DMLs such as INSERT/UPDATE/DELETE which are allowed to execute in
a transaction block, pgpool automatically start an explicit
transaction so that it can be rolled back by exiting pgpool child when
command results from each node do not agree.

Unfortunately since DROP DATABASE cannot execute in a transaction
block and pgpool does not start an explicit transaction, DROP DATABASE
may causes inconsistency among backends. I don't think checking DROP
DATABASE can be executed beforehand is easy: there's always a window
between at the time check and actual execution of DROP DATABASE.

BTW If you dislike degrading you can set:

replication_stop_on_mismatch = off
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Hello List,
> 
> on my PGPool 3.2.1 Cluster, replicating with load balanced mode on Postgres
> 9.2 Nodes, I tried to change the template1 database to UTF-8.
> 
> Changeing the database metadate so I can drop the database worked just
> fine, but when dropping the database once, I get a mismatch, when trying to
> drop it twice the cluster just gets completely disconnected(I had four
> online nodes when I started).
> 
> The following is what happened in the psql console:
> 
> 
> postgres=# UPDATE pg_database SET datallowconn = TRUE WHERE datname =
> 'template0';
> UPDATE 1
> postgres=# \c template0
> You are now connected to database "template0" as user "postgres".
> template0=# DROP DATABASE template1;
> ERROR:  cannot drop a template database
> template0=# UPDATE pg_database SET datistemplate = FALSE WHERE datname =
> 'template1';
> UPDATE 1
> template0=# DROP DATABASE template1;
> ERROR:  kind mismatch among backends. Possible last query was: "DROP
> DATABASE template1;" kind details are: 0[C] 1[E: database "template1" is
> being accessed by other users] 2[E: database "template1" is being accessed
> by other users] 3[C]
> HINT:  check data consistency among db nodes
> ERROR:  kind mismatch among backends. Possible last query was: "DROP
> DATABASE template1;" kind details are: 0[C] 1[E: database "template1" is
> being accessed by other users] 2[E: database "template1" is being accessed
> by other users] 3[C]
> HINT:  check data consistency among db nodes
> The connection to the server was lost. Attempting reset: Succeeded.
> template0=# DROP DATABASE template1;
> ERROR:  kind mismatch among backends. Possible last query was: "DROP
> DATABASE template1;" kind details are: 0[E: database "template1" does not
> exist] 1[C] 3[E: database "template1" does not exist]
> HINT:  check data consistency among db nodes
> ERROR:  kind mismatch among backends. Possible last query was: "DROP
> DATABASE template1;" kind details are: 0[E: database "template1" does not
> exist] 1[C] 3[E: database "template1" does not exist]
> HINT:  check data consistency among db nodes
> The connection to the server was lost. Attempting reset: Failed.
> !>
> 
> 
> Shouldn't there be at least some kind of mechanism in PGPool to check, if
> this DROP operation is possible instead of degrading the whole cluster with
> the failure of it?