Home » Database » DB » Bug #2085

[2004-08-08 01:37 UTC] ylf at xung dot org

Description:
------------
Hi,

I'm using DB 1.6.5

Trying to list the views from my Postgresql (7.2) database, I got some irrelevant views returned (named "pg_*"). These are postgresql internal views.

This actually comes from DB_pgsql::getSpecialQuery which doesn't ignore Postgresql's internal objects for views as it does for tables.

--
Olivier Guilyardi

Reproduce code:
---------------
require_once 'PEAR.php';
require_once 'DB.php';

$db =& DB::connect("pgsql://...");

$db->query("CREATE TABLE a (i INTEGER, j INTEGER)");
$db->query("CREATE view v AS SELECT i FROM a");

$views = $db->getListOf("views");

print_r ($views);

/*
A small patch :

815c815
< return 'SELECT viewname FROM pg_views';
---
> return "SELECT viewname FROM pg_views WHERE viewname !~ '^pg_'";

*/

Expected result:
----------------
Array
(
[0] => v
)

Actual result:
--------------
Array
(
[0] => pg_stat_user_indexes
[1] => pg_statio_all_indexes
[2] => pg_statio_sys_indexes
[3] => pg_statio_user_indexes
[4] => pg_statio_all_sequences
[5] => pg_statio_sys_sequences
[6] => pg_statio_user_sequences
[7] => pg_stat_activity
[8] => pg_stat_database
[9] => v
[10] => pg_user
[11] => pg_rules
[12] => pg_views
[13] => pg_tables
[14] => pg_indexes
[15] => pg_stats
[16] => pg_stat_all_tables
[17] => pg_stat_sys_tables
[18] => pg_stat_user_tables
[19] => pg_statio_all_tables
[20] => pg_statio_sys_tables
[21] => pg_statio_user_tables
[22] => pg_stat_all_indexes
[23] => pg_stat_sys_indexes
)

[2004-08-09 14:48 UTC] ylf at xung dot org

Hi,

No I'm not, please read my bug report properly and give a try to the reproduce code, using either DB 1.6.5 or the CVS.

--
og

[2004-08-10 11:08 UTC] ylf at xung dot org

In Postgresql 7.2, which I used for the repoduce code, all system views have the 'pg_' prefix. I've checked against Postgresql 7.4 and can see the additional (about 30 more) views you're mentioning.

I'm no Postgresql expert either. The point is that whatever you want to list (tables, functions, views, etc...) the postgresql ml archives and other docs always end up refering to the psql command line tool : '\dt' for tables, '\dv' for views, etc...

psql actually translates these short commands into sql statements. But, it looks like it's not very consistant accross versions :

If you happen to read C, I recommend you take a look at these :

Postgresql 7.2 : http://tinyurl.com/5vy2y
Postgresql 7.4 : http://tinyurl.com/594tp

Both of these point at describe.c, which contain the generic function listTables(), which psql calls for listing views among other objects.

Inside of listTables(), there's the "if (showSystem)" statement, which means "do we want to list system tables/views/etc or not".

Now, in 7.2 this is performed with :
c.relname !~ '^pg_'
while, in 7.4, it relies on a join against pg_catalog.pg_namespace (aliased "n" in describe.c).

And... the pg_namespace table does not exist in Postgresql 7.2

As for bugs #682 and #1607 (and my bogus #2084), this is a Postgresql backward compatibility issue, raised with version 7.3 which introduced schemas (this is what pg_namespace is all about).

I believe the _only_ solution is to execute an extra query in DB_pgsql constructor or connect(), to know the Postgresql version and adapt queries accordingly.