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

[Tatsuo Ishii]

>> 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.

User's query itself does not use relcache inside pgpool at
all. Relcache are only used for determing how to control user's query:
for example whether to send to primary or master, whether to issune
lock command or not etc. User's query always accesses real tables.
--
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