Re: [HACKERS] List of table names of a DB
On Fri, Jan 9, 2015 at 7:14 AM, Deepak S wrote: > 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. > For prototyping you might also find https://github.com/pganalyze/pg_query useful. Its a Ruby-based library that makes the Postgres parser easier to access from the outside, getting a list of tables from a query is trivial - but if you need the oids you'll have to do it like pgpool does. (feel free to ping me off-list about this) Best, -- Lukas Fittl Skype: lfittl Phone: +43 6991 2770651
Re: [HACKERS] 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: dgrowle...@gmail.com To: in.live...@live.in CC: pgsql-hackers@postgresql.org On 8 January 2015 at 22:41, Deepak S 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-gene...@postgresql.org list. Regards David Rowley -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] List of table names of a DB
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: dgrowle...@gmail.com To: in.live...@live.in CC: pgsql-hackers@postgresql.org On 8 January 2015 at 22:41, Deepak S 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-gene...@postgresql.org list. Regards David Rowley -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] List of table names of a DB
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: dgrowle...@gmail.com > To: in.live...@live.in > CC: pgsql-hackers@postgresql.org > > On 8 January 2015 at 22:41, Deepak S 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-gene...@postgresql.org list. > Regards > David Rowley > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] List of table names of a DB
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: dgrowle...@gmail.com To: in.live...@live.in CC: pgsql-hackers@postgresql.org On 8 January 2015 at 22:41, Deepak S 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-gene...@postgresql.org list. Regards David Rowley
[HACKERS] List of table names of a DB
Hello, I am unable to find the function which retrieves the 'list of names of the tables' used in a DB. 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. Kindly help.Thanks in advance. Deepak
Re: [HACKERS] List of table names of a DB
On 8 January 2015 at 22:41, Deepak S 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 42P01 postgres=# \set VERBOSITY verbose postgres=# explain select * from doesnotexist; ERROR: 42P01: relation "doesnotexist" does not exist LINE 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-gene...@postgresql.org list. Regards David Rowley
[HACKERS] List of table names of a DB
Hello, I am unable to find the function which retrieves the 'list of names of the tables' used in a DB. 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. Kindly help.Thanks in advance. Deepak
