Re: [GENERAL] Bug in psql (\dd query)
On Wed, Aug 21, 2013 at 11:34 PM, Ivan Radovanovic radovano...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bug in psql (\dd query)
Ivan Radovanovic radovano...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bug in psql (\dd query)
On 08/21/13 16:03, Tom Lane napisa: Ivan Radovanovicradovano...@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) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bug in psql (\dd query)
On 08/21/13 16:34, Ivan Radovanovic napisa: On 08/21/13 16:03, Tom Lane napisa: Ivan Radovanovicradovano...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
