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

[Tatsuo Ishii]

> I appreciate your response, it is very helpful. I'd like to ask one more
> question. You referenced '"Frontend/Backend Protocol" section of PostgreSQL
> docs.' I have searched at:
> 
> http://developer.postgresql.org/pgdocs/postgres/protocol.html
> 
> but couldn't find any discussion of this particular message type. Can you
> point me to the proper section or link?

here:
http://developer.postgresql.org/pgdocs/postgres/protocol-message-formats.html

"0[N] 1[C]" in pgpool log referrers to the first byte of the protocol
packet: it is 1 byte character.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Thanks,
> 
> Lou ...
> P Please consider the environment before printing this e-mail or any
> attachments.
> 
> 
> 
> On Tue, Mar 1, 2011 at 4:08 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> > 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
>>