[Pgpool-general] vacuum fails due to toast table difference

[Tatsuo Ishii]

> I am using postgres 8.3.5 & pgpool 2.2.2 with:
>     replication_mode = true
>     load_balance_mode = true
>     replicate_select = false
>     reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
>     master_slave_mode = false
>     parallel_mode = false
> 
> I run psql -p9999 -ddmaip -c"vacuum full verbose analyze ;"
> 
> and get the following failure:
> 
> INFO:  vacuuming "pg_toast.pg_toast_58429"
> ERROR:  kind mismatch among backends. Possible last query was: "vacuum full
> verbose analyze ;" kind details are: 0[N] 1[C]
> HINT:  check data consistency among db nodes
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> connection to server was lost
> 
> This is because this toast table is only present in the primary:
> 
> 0: psql -p9999 -ddmaip
> 
> dmaip=# \d pg_toast.pg_toast_58429
> TOAST table "pg_toast.pg_toast_58429"
>    Column   |  Type
> ------------+---------
>  chunk_id   | oid
>  chunk_seq  | integer
>  chunk_data | bytea
> 
> 0: psql -p2345 -ddmaip
> 
> dmaip=# \d pg_toast.pg_toast_58429
> TOAST table "pg_toast.pg_toast_58429"
>    Column   |  Type
> ------------+---------
>  chunk_id   | oid
>  chunk_seq  | integer
>  chunk_data | bytea
> 
> 1: psql -p 2345
> 
> dmaip=# \d pg_toast.pg_toast_58429
> Did not find any relation named "pg_toast.pg_toast_58429".
> 
> Questions:
> 
> 
>    1. Did this happen because the toast tables are not replicated?

Yes. PostgreSQL assigned a toast table name by using OID, which is not
guaranteed to be replicated by pgpool. Toast table names are very
internal ones, and for ordinary SQL commands such as
SELECT/INSERT/UPDATE/DELETE should have no problem.
 
>    2. Is there a way around this without shutting down this production
>    system?

It is not recommended to use VACUUM via pgpool. You should directly
connect to PostgreSQL and issue VACUUM command.

>    3. Is there a way to avoid this in future?

Ditto above.

>    4. Does anyone know what the "N" means in: 0[N] 1[C]?

DB node 0 gave you a NOTICE message, while DB node 1 gave you command
complete response, which is precisely explained in "Frontend/Backend
Protocol" section of PostgreSQL docs.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp