Re: List of table names of a DB
От | Mark Kirkwood |
---|---|
Тема | Re: List of table names of a DB |
Дата | |
Msg-id | 54AF7AFC.5000300@catalyst.net.nz обсуждение исходный текст |
Ответ на | Re: List of table names of a DB (Tatsuo Ishii <ishii@postgresql.org>) |
Ответы |
Re: List of table names of a DB
(Mark Kirkwood <mark.kirkwood@catalyst.net.nz>)
|
Список | pgsql-hackers |
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 mastersproject. In order to this, I need to store details(like name) of each table the query uses. In essence, I need tostore 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 gargantuantask 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'tbe 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@gmail.com >> To: in.live.in@live.in >> CC: pgsql-hackers@postgresql.org >> >> On 8 January 2015 at 22:41, Deepak S <in.live.in@live.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. Myplan 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 thateach 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: explainselect * 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@postgresql.org list. >> Regards >> David Rowley > >
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Ashutosh BapatДата:
Сообщение: Re: Transactions involving multiple postgres foreign servers