[Pgpool-general] vacuum fails due to toast table difference
louis fridkis
loufridkis at gmail.com
Wed Mar 2 01:16:11 UTC 2011
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?
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/20110301/66a792ef/attachment-0001.html>
More information about the Pgpool-general
mailing list