[Pgpool-general] Does pgdumpall make writes to the database?

[J. Carlos Muro]

Afaik, pg_dumpall in fact uses pg_dump internally, so YES pg_dumpall makes
writes to the database.. It means that getting dumps straight from a
production backend is forbidden.
I fixed the database, then I found that one of the developers was using
pgAdmin (i think that i read somewhere it is not allowed as it states many
queries together separated ';'):

    Jul 17 18:07:11 vapp1a pgpool: 2009-07-17 18:07:08 DEBUG: pid 30187:
statement2: SET DateStyle=ISO;SELECT oid, pg_encoding_to_char(encoding) AS
encoding, datlastsysoid

I instructed them not to use pgAdmin. I fixed again the database. The
backends where filesystem copies of postgresql's datadir. Healthcheck is
enabled, and pgpool logs show a normal startup with no problems of database
differences *before* client applications start to send queries. I start
applications. Now I get another error:

    org.postgresql.util.PSQLException: ERROR: pgpool detected difference of
the number of inserted, updated or deleted tuples. Possible last query was:
" DISCARD ALL"

It is Hibernate who sends lots of those queries continuously, so it is going
to be tricky to catch exactly the point where the fist exception happens. I
guess I have to activate "query debug" (send queries to logs) on both pgpool
and backends, then rely on timestamps from the error log of JBoss... I will
try today to reproduce the problem as the most possible simple case. Btw,
are there known success stories of JBoss (+hibernate)/pgpool/PostgreSQL
projects?

Thanks!
J. Carlos Muro


2009/7/18 Tatsuo Ishii <ishii at sraoss.co.jp>

> > Hi! I have stopped pgpool, then gone to backend0, executed a pgdumpall.
> > When I started pgpool back, I started register the next error in a
> exception
> > from a JDBC application:
> >   org.postgresql.util.PSQLException: ERROR: pgpool detected difference of
> > the number of inserted, updated or deleted tuples. Possible last query
> > was: " DISCARD ALL"
> > In the logs of pgpool I hvae found:
> >   Jul 16 14:49:16 vapp1a pgpool: 2009-07-16 14:49:16 ERROR: pid 901: kind
> > mismatch among backends. Possible last query was: " DISCARD ALL" kind
> > details are: 0[D] 1[C]
> >
> > Is it known that pgdumpall would modify data in backend0?
> > Thanks!
>
> I would think that it would be possible for pg_dump creates a temp
> table. If so, the oid counter would be different among DB nodes.
>
> Can you identify what SQL your application sends to pgpool?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20090720/030ff5bb/attachment.html>