[pgpool-general: 229] Re: More information on relcache_expire

Wed Feb 15 19:52:19 JST 2012

________________________________
 From: Tatsuo Ishii <ishii at postgresql.org>
To: sandeeptt at yahoo.com 
Cc: pgpool-general at pgpool.net 
Sent: Wednesday, February 15, 2012 3:53 PM
Subject: Re: [pgpool-general: 221] Re: More information on relcache_expire

> So, in this case, pgpool does not execute SELECT query internally?

Right.

>  I see some queries to pg_class in the server log.

Probably you issue the next query in different session?  It is
possible that you do not see the relcache effect because 1) relcache
is per session(pgpool child proccess), not whole pgpool instance 2)
there is "session local" type relcache. Typically used for checking if
the table is a temporary one or not. The type of relcache's life is
limited within the same saession.

> I basically wanted to see the effects of relcache_expire. Please let me know how to test it?

1) set relcache_expire to 60(for example)
2) start pgpool
3) SELECT * FROM t1(or whatever table)
4) you see accesses to pg_class
5) within 60 seconds, issue same query as #3
6) this you should not see access to the system catalog

This works as expected. Thanks. The only thing I didn't understand is that if I I alter the structure of the table before I execute #5, and #6 works as expected (it does not access system catalog), then how come the the query at #5 returns the updated table structure? Because within 60 secs, it would not access the system catalogue and access the relation cache, right? This is seen at #6. This would mean tat relation cache had an updated table structure. Question is how? I do all the above steps in a same client connection.

--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> ________________________________
>  From: Tatsuo Ishii <ishii at postgresql.org>
> To: sandeeptt at yahoo.com 
> Cc: pgpool-general at pgpool.net 
> Sent: Wednesday, February 15, 2012 1:22 PM
> Subject: Re: [pgpool-general: 221] Re: More information on relcache_expire
>  
> Relcache only takes care of SELECTs issued by pgpool internaly. So
> your SELECT will not benefit from it.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
> 
>> Thanks Tatsuo!
>> 
>> I have setup a Master and Slave configuration and set relcache_expire as 600 in pgpool.conf. Now I do manual testing within 600 seconds. 
>> I connect to database through pgpool port 9999. At the psql prompt, I created a table with 3 columns:
>> 
>> CREATE TABLE phonebook(phone VARCHAR(32), name VARCHAR(32), address VARCHAR(64));
>> 
>> I inserted proper values and then executed the select query which returns proper values.
>> SELECT * FROM phonebook ORDER BY name;
>> 
>> Now, I alter the table and rename the column 'name' with 'firstname'
>> ALTER TABLE phonebook RENAME COLUMN name TO firstname;
>> 
>> Now, the same select query returns me the ERROR "column "name" does not exist".  I thought it should return fine because relation cache would not have updated table structure, right? Or am I missing something?
>> 
>> 
>> 
>> 
>> ________________________________
>>  From: Tatsuo Ishii <ishii at postgresql.org>
>> To: sandeeptt at yahoo.com 
>> Cc: pgpool-general at pgpool.net 
>> Sent: Tuesday, February 14, 2012 2:52 PM
>> Subject: Re: [pgpool-general: 221] Re: More information on relcache_expire
>>  
>>> right, that makes sense. Thanks!
>>> 
>>> BTW, how do we know that the query result is returned from the cache or not?
>> 
>> Please look into PostgreSQL log. If you see access to pg_class or so
>> before user's query, that is the accessed from pgpool, thus cache is
>> not used.
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>> 
>>> ________________________________
>>>  From: Tatsuo Ishii <ishii at postgresql.org>
>>> To: sandeeptt at yahoo.com 
>>> Cc: pgpool-general at pgpool.net 
>>> Sent: Tuesday, February 14, 2012 2:11 PM
>>> Subject: Re: [pgpool-general: 221] Re: More information on relcache_expire
>>>  
>>>> I mean, let's say I define the value of relcache_expire as "600" seconds. and the TABLE in cache is ALTERED before that. That means my query will produce the wrong result because the relation cache is not yet updated/expired, right?
>>> 
>>> Yes.
>>> 
>>> On what basis one would define the value of relcache_expire in pgpool.conf?
>>> 
>>> I don't think changing table structure so frequently is a real life
>>> usecase. Rather I think it happens during application development:
>>> table structures are not stable.
>>> 
>>> If your customer really wants to change table structure every 5
>>> minutes, probably there's something wrong with user's work flow
>>> anyway.
>>> --
>>> Tatsuo Ishii
>>> SRA OSS, Inc. Japan
>>> English: http://www.sraoss.co.jp/index_en.php
>>> Japanese: http://www.sraoss.co.jp
>>> 
>>>> ________________________________
>>>>  From: Sandeep Thakkar <sandeeptt at yahoo.com>
>>>> To: "pgpool-general at pgpool.net" <pgpool-general at pgpool.net> 
>>>> Sent: Monday, February 13, 2012 1:58 PM
>>>> Subject: [pgpool-general: 220] More information on relcache_expire
>>>>  
>>>> 
>>>> - Add relcache_expire directive to control the expiration of the internal system catalog cache. ALTER TABLE might make these cache values obsoleted and the new directive will make the risk lower(Tatsuo)
>>>>  I would like to know how to make use of this directive? A test case will help.
>>>> 
>>>> Thanks.
>>>> 
>>>> 
>>>> _______________________________________________
>>>> pgpool-general mailing list
>>>> pgpool-general at pgpool.net
>>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120215/308dab59/attachment.html>