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

[Sandeep Thakkar]

So, in this case, pgpool does not execute SELECT query internally? I see some queries to pg_class in the server log. I basically wanted to see the effects of relcache_expire. Please let me know how to test it?



________________________________
 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/566c2b20/attachment-0001.html>