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

[louis fridkis]

OK, I understand it now.

Byte1('N')

Identifies the message as a notice.
refers to [N] in the message.

Thanks again, you have been very helpful,

Lou ...
P Please consider the environment before printing this e-mail or any
attachments.



On Tue, Mar 1, 2011 at 5:28 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> > 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
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20110302/d9f8b3d9/attachment.html>