| CC: | p.makeev [...] sumtel.ru |
| Subject: | bug in table_info sub in DBD::Pg |
| Date: | Wed, 18 Apr 2007 12:36:36 +0400 |
| To: | bug-dbd-pg [...] rt.cpan.org |
| From: | "Sergey A.Eremenko" <cae [...] cae.pp.ru> |
Hello, bug-dbd-pg.
There is bug in table_info sub (REMARKS reading)
You do next in table_info:
========================
$tbl_sql = qq{
SELECT NULL::text AS "TABLE_CAT"
, $showschema AS "TABLE_SCHEM"
, quote_ident(c.relname) AS "TABLE_NAME"
, CASE
WHEN c.relkind = 'v' THEN
CASE WHEN $schemacase ~ '^pg_' THEN 'SYSTEM VIEW' ELSE 'VIEW' END
ELSE
CASE WHEN $schemacase ~ '^pg_' THEN 'SYSTEM TABLE' ELSE 'TABLE' END
END AS "TABLE_TYPE"
, d.description AS "REMARKS" $showtablespace
FROM ${DBD::Pg::dr::CATALOG}pg_class AS c
LEFT JOIN ${DBD::Pg::dr::CATALOG}pg_description AS d
ON (c.relfilenode = d.objoid $has_objsubid)
$schemajoin $tablespacejoin
WHERE $whereclause
ORDER BY "TABLE_TYPE", "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME"
};
========================
But pg_description.objoid reference to pg_class.oid, not a
pg_class.relfilenode!
See psql/describe.c:
========================
*
* Get object comments
*
* \dd [foo]
*
* Note: This only lists things that actually have a description. For complete
* lists of things, there are other \d? commands.
*/
bool
objectDescription(const char *pattern)
.........
appendPQExpBuffer(&buf,
") AS tt\n"
" JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");
========================
Therefore need next patch for Pg.pm:
========================
*** Pg.pm.orig 2006-05-05 19:20:15.000000000 +0400
--- Pg.pm 2007-04-18 11:57:47.000000000 +0400
***************
*** 983,989 ****
, d.description AS "REMARKS" $showtablespace
FROM ${DBD::Pg::dr::CATALOG}pg_class AS c
LEFT JOIN ${DBD::Pg::dr::CATALOG}pg_description AS d
! ON (c.relfilenode = d.objoid $has_objsubid)
$schemajoin $tablespacejoin
WHERE $whereclause
ORDER BY "TABLE_TYPE", "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME"
--- 983,989 ----
, d.description AS "REMARKS" $showtablespace
FROM ${DBD::Pg::dr::CATALOG}pg_class AS c
LEFT JOIN ${DBD::Pg::dr::CATALOG}pg_description AS d
! ON (c.oid = d.objoid AND c.tableoid = d.classoid $has_objsubid)
$schemajoin $tablespacejoin
WHERE $whereclause
ORDER BY "TABLE_TYPE", "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME"
========================
In attach dump of pgsql database prebilling and my sample perl code,
using DBD::Pg, it showing errors.
========================
mv /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/Pg.pm /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/Pg.pm.new
mv /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/Pg.pm.orig /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/Pg.pm
./test_table_info.pl
merastat table_info:
$VAR1 = {
'REMARKS' => undef,
'pg_tablespace_location' => undef,
'TABLE_NAME' => 'merastat',
'TABLE_CAT' => undef,
'TABLE_SCHEM' => 'public',
'pg_tablespace_name' => undef,
'TABLE_TYPE' => 'TABLE'
};
clients table_info:
$VAR1 = {
'REMARKS' => 'Таблица клиентов',
'pg_tablespace_location' => undef,
'TABLE_NAME' => 'clients',
'TABLE_CAT' => undef,
'TABLE_SCHEM' => 'public',
'pg_tablespace_name' => undef,
'TABLE_TYPE' => 'TABLE'
};
mv /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/Pg.pm /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/Pg.pm.orig
mv /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/Pg.pm.new /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/Pg.pm
./test_table_info.pl
merastat table_info:
$VAR1 = {
'REMARKS' => 'Статистика по Мерам',
'pg_tablespace_location' => undef,
'TABLE_NAME' => 'merastat',
'TABLE_CAT' => undef,
'TABLE_SCHEM' => 'public',
'pg_tablespace_name' => undef,
'TABLE_TYPE' => 'TABLE'
};
clients table_info:
$VAR1 = {
'REMARKS' => 'Таблица клиентов',
'pg_tablespace_location' => undef,
'TABLE_NAME' => 'clients',
'TABLE_CAT' => undef,
'TABLE_SCHEM' => 'public',
'pg_tablespace_name' => undef,
'TABLE_TYPE' => 'TABLE'
};
========================
Please, fix it in future versions DBD::Pg. Thanks
--
Regards,
Sergey mailto:cae@cae.pp.ru