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

Sandeep Thakkar sandeeptt at yahoo.com
Wed Feb 22 14:53:49 JST 2012



 


________________________________
 From: Tatsuo Ishii <ishii at postgresql.org>
To: sandeeptt at yahoo.com 
Cc: pgpool-general at pgpool.net 
Sent: Thursday, February 16, 2012 7:16 AM
Subject: Re: [pgpool-general: 221] Re: More information on relcache_expire
 
>> 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.

->okay. How the obsolete contents of relcache affects the user then? I thought if the table is altered by the admin and user executes the same query, he will get the old table structure because relcache is not updated. and this was the reason relcache_expire was added. How can I test this ALTER TABLE scenario?

--
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/20120221/7575b509/attachment.html>


More information about the pgpool-general mailing list