Thread: Bug in psql (\dd query)

Hello,

I was checking for way to get object comments, and it seems that \dd has
bug when it comes to extracting descriptions for constraints. Relevant
part of query psql is executing is:

SELECT DISTINCT tt.nspname AS "Schema", tt.name AS "Name", tt.object AS
"Object", d.description AS "Description"
FROM (
   SELECT pgc.oid as oid, pgc.tableoid AS tableoid,
   n.nspname as nspname,
   CAST(pgc.conname AS pg_catalog.text) as name,  CAST('constraint' AS
pg_catalog.text) as object
   FROM pg_catalog.pg_constraint pgc
     JOIN pg_catalog.pg_class c ON c.oid = pgc.conrelid
     LEFT JOIN pg_catalog.pg_namespace n     ON n.oid = c.relnamespace
WHERE n.nspname <> 'pg_catalog'
       AND n.nspname <> 'information_schema'
   AND pg_catalog.pg_table_is_visible(c.oid)
    /* more unions here */
) AS tt
   JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND
tt.tableoid = d.classoid AND d.objsubid = 0)
ORDER BY 1, 2, 3;

obviously it is trying to get description for (table_oid,
constraint_oid, 0), while in fact it should read description for (oid of
pg_catalog.pg_constaint, constraint_oid, 0).

At least last tuple is what comment statement is inserting into
pg_description table

Regards,
Ivan

Ivan Radovanovic <radovanovic@gmail.com> writes:
> I was checking for way to get object comments, and it seems that \dd has
> bug when it comes to extracting descriptions for constraints.

That code looks right to me, and it works according to a simple test:

d1=# create table foo (f1 int primary key);
CREATE TABLE
d1=# comment on constraint foo_pkey on foo is 'here is a comment';
COMMENT
d1=# \dd
                Object descriptions
 Schema |   Name   |   Object   |    Description
--------+----------+------------+-------------------
 public | foo_pkey | constraint | here is a comment
(1 row)

What exactly do you think the problem is?

            regards, tom lane

On 08/21/13 16:03, Tom Lane napisa:
> Ivan Radovanovic<radovanovic@gmail.com>  writes:
>> I was checking for way to get object comments, and it seems that \dd has
>> bug when it comes to extracting descriptions for constraints.
>
> That code looks right to me, and it works according to a simple test:
>
> d1=# create table foo (f1 int primary key);
> CREATE TABLE
> d1=# comment on constraint foo_pkey on foo is 'here is a comment';
> COMMENT
> d1=# \dd
>                  Object descriptions
>   Schema |   Name   |   Object   |    Description
> --------+----------+------------+-------------------
>   public | foo_pkey | constraint | here is a comment
> (1 row)
>
> What exactly do you think the problem is?
>
>             regards, tom lane

Problem is if you create table in schema other than public (I am not
sure if \dd should show comments only for objects in public schema, I
assumed not?)

db=# create schema test;
CREATE SCHEMA
db=# create table test.foo (f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
db=# comment on constraint foo_pkey on test.foo is 'here is a comment';
COMMENT
db=# \dd
          Object descriptions
  Schema | Name | Object | Description
--------+------+--------+-------------
(0 rows)

On 08/21/13 16:34, Ivan Radovanovic napisa:
> On 08/21/13 16:03, Tom Lane napisa:
>> Ivan Radovanovic<radovanovic@gmail.com> writes:
>>> I was checking for way to get object comments, and it seems that \dd has
>>> bug when it comes to extracting descriptions for constraints.
>>
>> That code looks right to me, and it works according to a simple test:
>>
>> d1=# create table foo (f1 int primary key);
>> CREATE TABLE
>> d1=# comment on constraint foo_pkey on foo is 'here is a comment';
>> COMMENT
>> d1=# \dd
>> Object descriptions
>> Schema | Name | Object | Description
>> --------+----------+------------+-------------------
>> public | foo_pkey | constraint | here is a comment
>> (1 row)
>>
>> What exactly do you think the problem is?
>>
>> regards, tom lane
>
> Problem is if you create table in schema other than public (I am not
> sure if \dd should show comments only for objects in public schema, I
> assumed not?)
>
> db=# create schema test;
> CREATE SCHEMA
> db=# create table test.foo (f1 int primary key);
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey"
> for table "foo"
> CREATE TABLE
> db=# comment on constraint foo_pkey on test.foo is 'here is a comment';
> COMMENT
> db=# \dd
> Object descriptions
> Schema | Name | Object | Description
> --------+------+--------+-------------
> (0 rows)
>

Obviously there is optional pattern argument for \dd which would show
comments in different schema, so I it was my mistake after all.

Sorry for false alarm

On Wed, Aug 21, 2013 at 11:34 PM, Ivan Radovanovic
<radovanovic@gmail.com> wrote:
> On 08/21/13 16:03, Tom Lane napisa:
>
>
> Problem is if you create table in schema other than public (I am not sure if
> \dd should show comments only for objects in public schema, I assumed not?)
>
> db=# create schema test;
> CREATE SCHEMA
> db=# create table test.foo (f1 int primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey"
> for table "foo"
> CREATE TABLE
> db=# comment on constraint foo_pkey on test.foo is 'here is a comment';
> COMMENT
> db=# \dd
>
>          Object descriptions
>  Schema | Name | Object | Description
> --------+------+--------+-------------
> (0 rows)
¥dd outputs information of objects of schemas referenced in
search_path. Your example works if you change this parameter
accordingly to your new schema:
=# create schema test;
CREATE SCHEMA
=# create table test.foo (f1 int primary key);
CREATE TABLE
=# comment on constraint foo_pkey on test.foo is 'here is a comment';
COMMENT
=# \dd
         Object descriptions
 Schema | Name | Object | Description
--------+------+--------+-------------
(0 rows)

=# set search_path to 'test';
SET
=# \dd
                Object descriptions
 Schema |   Name   |   Object   |    Description
--------+----------+------------+-------------------
 test   | foo_pkey | constraint | here is a comment
(1 row)

Regards,
--
Michael