<html><body><div style="color:#000; background-color:#fff; font-family:times new roman, new york, times, serif;font-size:10pt"><br><br><div style="font-family: times new roman, new york, times, serif; font-size: 10pt;"> <div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"> <div dir="ltr"> <font size="2" face="Arial"> <hr size="1"> <b><span style="font-weight:bold;">From:</span></b> Tatsuo Ishii <ishii@postgresql.org><br> <b><span style="font-weight: bold;">To:</span></b> sandeeptt@yahoo.com <br><b><span style="font-weight: bold;">Cc:</span></b> pgpool-general@pgpool.net <br> <b><span style="font-weight: bold;">Sent:</span></b> Wednesday, February 15, 2012 3:53 PM<br> <b><span style="font-weight: bold;">Subject:</span></b> Re: [pgpool-general: 221] Re: More information on relcache_expire<br> </font> </div> <br>
> So, in this case, pgpool does not execute SELECT query internally?<br><br>Right.<br><br>> I see some queries to pg_class in the server log.<br><br>Probably you issue the next query in different session? It is<br>possible that you do not see the relcache effect because 1) relcache<br>is per session(pgpool child proccess), not whole pgpool instance 2)<br>there is "session local" type relcache. Typically used for checking if<br>the table is a temporary one or not. The type of relcache's life is<br>limited within the same saession.<br><br>> I basically wanted to see the effects of relcache_expire. Please let me know how to test it?<br><br>1) set relcache_expire to 60(for example)<br>2) start pgpool<br>3) SELECT * FROM t1(or whatever table)<br>4) you see accesses to pg_class<br>5) within 60 seconds, issue same query as #3<br>6) this you should not see access to the system catalog<br><br>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.<br><br>--<br>Tatsuo Ishii<br>SRA OSS, Inc. Japan<br>English: http://www.sraoss.co.jp/index_en.php<br>Japanese: http://www.sraoss.co.jp<br><br>> ________________________________<br>> From: Tatsuo Ishii <<a ymailto="mailto:ishii@postgresql.org" href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>> To: <a ymailto="mailto:sandeeptt@yahoo.com" href="mailto:sandeeptt@yahoo.com">sandeeptt@yahoo.com</a> <br>> Cc: <a
ymailto="mailto:pgpool-general@pgpool.net" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a> <br>> Sent: Wednesday, February 15, 2012 1:22 PM<br>> Subject: Re: [pgpool-general: 221] Re: More information on relcache_expire<br>> <br>> Relcache only takes care of SELECTs issued by pgpool internaly. So<br>> your SELECT will not benefit from it.<br>> --<br>> Tatsuo Ishii<br>> SRA OSS, Inc. Japan<br>> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>> <br>>> Thanks Tatsuo!<br>>> <br>>> 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. <br>>> I connect to database through pgpool port 9999. At the psql prompt, I created a table with 3
columns:<br>>> <br>>> CREATE TABLE phonebook(phone VARCHAR(32), name VARCHAR(32), address VARCHAR(64));<br>>> <br>>> I inserted proper values and then executed the select query which returns proper values.<br>>> SELECT * FROM phonebook ORDER BY name;<br>>> <br>>> Now, I alter the table and rename the column 'name' with 'firstname'<br>>> ALTER TABLE phonebook RENAME COLUMN name TO firstname;<br>>> <br>>> 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?<br>>> <br>>> <br>>> <br>>> <br>>> ________________________________<br>>> From: Tatsuo Ishii <<a ymailto="mailto:ishii@postgresql.org" href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>>> To: <a
ymailto="mailto:sandeeptt@yahoo.com" href="mailto:sandeeptt@yahoo.com">sandeeptt@yahoo.com</a> <br>>> Cc: <a ymailto="mailto:pgpool-general@pgpool.net" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a> <br>>> Sent: Tuesday, February 14, 2012 2:52 PM<br>>> Subject: Re: [pgpool-general: 221] Re: More information on relcache_expire<br>>> <br>>>> right, that makes sense. Thanks!<br>>>> <br>>>> BTW, how do we know that the query result is returned from the cache or not?<br>>> <br>>> Please look into PostgreSQL log. If you see access to pg_class or so<br>>> before user's query, that is the accessed from pgpool, thus cache is<br>>> not used.<br>>> --<br>>> Tatsuo Ishii<br>>> SRA OSS, Inc. Japan<br>>> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>>> Japanese: <a
href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>>> <br>>>> ________________________________<br>>>> From: Tatsuo Ishii <<a ymailto="mailto:ishii@postgresql.org" href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>>>> To: <a ymailto="mailto:sandeeptt@yahoo.com" href="mailto:sandeeptt@yahoo.com">sandeeptt@yahoo.com</a> <br>>>> Cc: <a ymailto="mailto:pgpool-general@pgpool.net" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a> <br>>>> Sent: Tuesday, February 14, 2012 2:11 PM<br>>>> Subject: Re: [pgpool-general: 221] Re: More information on relcache_expire<br>>>> <br>>>>> 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?<br>>>> <br>>>> Yes.<br>>>> <br>>>> On what basis one would define the value of relcache_expire in pgpool.conf?<br>>>> <br>>>> I don't think changing table structure so frequently is a real life<br>>>> usecase. Rather I think it happens during application development:<br>>>> table structures are not stable.<br>>>> <br>>>> If your customer really wants to change table structure every 5<br>>>> minutes, probably there's something wrong with user's work flow<br>>>> anyway.<br>>>> --<br>>>> Tatsuo Ishii<br>>>> SRA OSS, Inc. Japan<br>>>> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>>>> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>>>> <br>>>>>
________________________________<br>>>>> From: Sandeep Thakkar <<a ymailto="mailto:sandeeptt@yahoo.com" href="mailto:sandeeptt@yahoo.com">sandeeptt@yahoo.com</a>><br>>>>> To: "<a ymailto="mailto:pgpool-general@pgpool.net" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a>" <<a ymailto="mailto:pgpool-general@pgpool.net" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a>> <br>>>>> Sent: Monday, February 13, 2012 1:58 PM<br>>>>> Subject: [pgpool-general: 220] More information on relcache_expire<br>>>>> <br>>>>> <br>>>>> - 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)<br>>>>> I would like to know how to make use of this directive? A test case
will help.<br>>>>> <br>>>>> Thanks.<br>>>>> <br>>>>> <br>>>>> _______________________________________________<br>>>>> pgpool-general mailing list<br>>>>> <a ymailto="mailto:pgpool-general@pgpool.net" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>>>>> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br><br><br> </div> </div> </div></body></html>