[pgpool-general: 738] Re: nonlogged-tables / question

[Tatsuo Ishii]

> in our application, we're using a unlogged (9.1 feature) table which
> we join against. The table is used as a cache which can be easily (but
> at some perfomance cost) rebuilt from the real data, so we chose to
> make the table unlogged in order to get the maximum insert
> performance.
> 
> My question now is how pgpool handles this when used with streaming
> replication, as you can't query for unlogged tables on a hot standby
> machine.
> 
> Some naïve testing (sending a read query joinin against that table
> through pgpool over and over) has never shown a problem, so I *think*
> pgpool knows about unlogged tables, but I'd rather have confirmation
> on this :-)

Yes, from 3.1.0 pgpool-II sends queries including unlogged tables to
primary only in streaming replication mode. See:
doc/where_to_send_queries.pdf for more details.

> So the question is: Does pgpool know about unlogged tables? Does it
> send queries joining against unlogged table to master?

Pgpool recursively checks the parse tree of the query to find table
which is an unlogged table. To check whether the table is an unlogged
one, pgpool checks the value of pg_catalog.relpersistence column. See
pool_where_to_send() for more details.

> If not or not always, how can I convince it to do so?

For the checking pgpool uses following query:

SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = pgpool_regclass('%s') AND c.relpersistence = 'u'

where %s is replaced by the table name. Maybe finding this in the
query log help you.

Please note that pgpool caches this kind of query result in per
pgpool's child process cache(called "relcache"). So if you use the
same table in a session several times, you only see the query above
one time unless the cache entry is purged (not enough relcache space)
or the relcache is timed out (check relcache_expire directive in
pgpool.conf).

> As I said, from naïve testing, I would assume that pgpool does it
> right - I would just love to have confirmation.
> 
> Philip
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp