Re: List of table names of a DB

Actually, code has moved to:

https://github.com/snaga/pqc

On 09/01/15 19:53, Mark Kirkwood wrote:
> Also see:
>
> https://code.google.com/p/pqc/
>
> A project to implement a query cache using pgpool code, probably lots of
> good ideas there.
>
> Cheers
>
> Mark
>
> On 09/01/15 19:38, Tatsuo Ishii wrote:
>> Hi,
>>
>> pgpool-II (pgpool.net) does exactly the same thing.
>>
>> It receive SELECT query from clients, 1) parse it to find table names,
>> and 2) gets the oids (unique identifier in the PostgreSQL system
>> catalog) to recognize them. when the SELECT succeeds , it store the
>> query result (cache) on either shared memory or memcached according to
>> user's choice. For query cache invalidation, pgpool-II remembers all
>> oids related to the SELECTs which are source of query cache. If one of
>> tables get updated, pgpoool-II invalidates all of cache using the oid.
>>
>> For #1, pgpool-II has a query parser copied from PostgreSQL.
>>
>> pgpool-II is an open source project, so you could get some idea to
>> implement your own tool.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
>>> Sorry, it's not about querying. I am implementing an invalidation
>>> mechanism for Postgres Query Cache as part of my masters project. In
>>> order to this, I need to store details(like name) of each table the
>>> query uses. In essence, I need to store the table names of the cached
>>> queries.
>>> Initially, I thought of writing a code that could extract the table
>>> names but later discovered that it is a gargantuan task as I shall
>>> have to include around 600 production rules as was hinted in a
>>> Stackoverflow Exchange post. Hence, I thought of getting hold of the
>>> data structure used for storing table names of a DB but I couldn't
>>> get it.
>>> Sorry for the long post but do you know where these tables
>>> information of a DB gets stored? Or can you suggest me a
>>> mechanism(needn't be fully perfect) to extract the table names? I
>>> went through the parser of postgres but it was confusing.
>>> Thanks
>>>
>>> Date: Fri, 9 Jan 2015 00:46:30 +1300
>>> Subject: Re: [HACKERS] List of table names of a DB
>>> From: dgrowleyml(at)gmail(dot)com
>>> To: in(dot)live(dot)in(at)live(dot)in
>>> CC: pgsql-hackers(at)postgresql(dot)org
>>>
>>> On 8 January 2015 at 22:41, Deepak S <in(dot)live(dot)in(at)live(dot)in> wrote:
>>>
>>>
>>>
>>> Hello, I am unable to find the function which retrieves the 'list of
>>> names of the tables' used in a DB.
>>>
>>> You may find what you want in: select table_name from
>>> information_schema.tables;
>>> http://www.postgresql.org/docs/9.4/static/infoschema-tables.html
>>>
>>> Reason: I need a mechanism by which I can extract the names of the
>>> tables used in a query which has been parsed. My plan is to check for
>>> a match of each word in the query with a list of names of the tables
>>> used in the current DB so that each hit confirms a 'table name' in
>>> the query in most cases.
>>>
>>> This sounds rather flaky.
>>> Maybe it would be better to just EXPLAIN the query and see if you get
>>> error code 42P01postgres=# \set VERBOSITY verbosepostgres=# explain
>>> select * from doesnotexist;ERROR: 42P01: relation "doesnotexist"
>>> does not existLINE 1: explain select * from doesnotexist;
>>> Unfortunately this won't help you much if you want to know all of the
>>> tables that don't exist.
>>> Also, just for the future, a question like this might be more suited
>>> for the pgsql-general(at)postgresql(dot)org list.
>>> Regards
>>> David Rowley
>>
>>
>