Thread: The comment of Primary key
Hi Dave.
I am distracted!
Though a command is psql, it is the problem that it happens in pgAdmin3.
See this sample below.
---------------------------------------------------------------
CREATE TABLE "MyTransaction"
(
"XID" int4 NOT NULL,
"LastSeqId" int4 NOT NULL,
"HostId" int4 NOT NULL,
CONSTRAINT "MyTransaction_pkey" PRIMARY KEY ("XID", "HostId")
) WITH OIDS;
COMMENT ON CONSTRAINT "MyTransaction_pkey" ON "MyTransaction" IS 'What happens to this?';
----------------------------------------------------------------
saito=# SELECT cls.oid, cls.relname as idxname, indrelid, indkey, indisclustered, indisunique, indisprimary, n.nspname,
indnatts, tab.relname as tabname, indclass, description,
pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname
FROM pg_index idx
JOIN pg_class cls ON cls.oid=indexrelid
JOIN pg_class tab ON tab.oid=indrelid
JOIN pg_namespace n ON n.oid=tab.relnamespace
JOIN pg_am am ON am.oid=cls.relam
LEFT OUTER JOIN pg_description des ON (des.objoid=cls.oid AND des.objsubid = 0)
LEFT OUTER JOIN pg_constraint con ON con.conrelid=indrelid AND conname=cls.relname
WHERE cls.relname = 'MyTransaction_pkey';
oid | idxname | indrelid | indkey | indisclustered | indisunique | indisprimary | nspname | indnatts |
tabname
| indclass | description | indconstraint | contype | condeferrable | condeferred | amname
--------+--------------------+----------+--------+----------------+-------------+--------------+---------+----------+-----------
----+-----------+-------------+---------------+---------+---------------+-------------+--------
518874 | MyTransaction_pkey | 518872 | 1 3 | f | t | t | saito | 2 |
MyTransaction | 1978 1978 | | | p | f | f | btree
(1 row)
*Why?*
saito=# SELECT * FROM pg_description WHERE objoid >= 518874::OID;
objoid | classoid | objsubid | description
--------+----------+----------+-----------------------
518875 | 16386 | 0 | What happens to this?
(1 row)
Hmm... 518875?
Do I have misunderstanding?
Regards,
Hiroshi Saito
It's rumoured that Hiroshi Saito once said: > ---------------------------------------------------------------- > saito=# SELECT cls.oid, cls.relname as idxname, indrelid, indkey, > indisclustered, indisunique, indisprimary, n.nspname, > indnatts, tab.relname as tabname, indclass, description, > pg_get_expr(indpred, indrelid, true) as indconstraint, contype, > condeferrable, condeferred, amname > FROM pg_index idx > JOIN pg_class cls ON cls.oid=indexrelid > JOIN pg_class tab ON tab.oid=indrelid > JOIN pg_namespace n ON n.oid=tab.relnamespace > JOIN pg_am am ON am.oid=cls.relam > LEFT OUTER JOIN pg_description des ON (des.objoid=cls.oid AND > des.objsubid = 0) LEFT OUTER JOIN pg_constraint con ON > con.conrelid=indrelid AND conname=cls.relname > WHERE cls.relname = 'MyTransaction_pkey'; Hi Hiroshi, I don't have a system to test this on at the moment so I may have missed something... You are joining to pg_description using pg_class.oid. This will return the comment for the table. Try joining to pg_constraint.oid so it uses the pkey records oid. Regards, Dave.
Hi Dave. Hmm.. pg_dumpall can't take it.... I had never used this comment sentence until now. Did anyone use this? Regards, Hiroshi Saito > Hi Hiroshi, > > I don't have a system to test this on at the moment so I may have missed > something... > You are joining to pg_description using pg_class.oid. This will return the > comment for the table. Try joining to pg_constraint.oid so it uses the > pkey records oid. > Regards, Dave.
> -----Original Message----- > From: Hiroshi Saito [mailto:saito@inetrt.skcapi.co.jp] > Sent: 19 September 2003 03:24 > To: Dave Page > Cc: pgadmin-hackers@postgresql.org > Subject: Re: [pgadmin-hackers] The comment of Primary key > > > Hi Dave. > > Hmm.. > pg_dumpall can't take it.... > I had never used this comment sentence until now. > Did anyone use this? What pg_dumpall doesn't handle constraint comments? If that is so, it should be reported to the pgsql-bugs@postgresql.org list. I don't think it's a reason for us not to allow such comments though. Regards, Dave.
Hi Dave. I am sorry that work is late. It was tested last night in this patch. Is it all right with the thing before a release? Include it if it is possible. Regards, Hiroshi Saito From: "Dave Page" <dpage@vale-housing.co.uk> > -----Original Message----- > From: Hiroshi Saito [mailto:saito@inetrt.skcapi.co.jp] > Sent: 19 September 2003 03:24 > To: Dave Page > Cc: pgadmin-hackers@postgresql.org > Subject: Re: [pgadmin-hackers] The comment of Primary key > > > Hi Dave. > > Hmm.. > pg_dumpall can't take it.... > I had never used this comment sentence until now. > Did anyone use this? What pg_dumpall doesn't handle constraint comments? If that is so, it should be reported to the pgsql-bugs@postgresql.org list. I don't think it's a reason for us not to allow such comments though. Regards, Dave. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Attachment
Hi Hiroshi, Patch applied - it made it!! Regards, Dave. > -----Original Message----- > From: Hiroshi Saito [mailto:saito@inetrt.skcapi.co.jp] > Sent: 26 September 2003 03:43 > To: Dave Page > Cc: pgadmin-hackers@postgresql.org > Subject: Re: [pgadmin-hackers] The comment of Primary key > > > Hi Dave. > > I am sorry that work is late. > It was tested last night in this patch. > Is it all right with the thing before a release? > Include it if it is possible. > > Regards, > Hiroshi Saito > > From: "Dave Page" <dpage@vale-housing.co.uk> > > -----Original Message----- > > From: Hiroshi Saito [mailto:saito@inetrt.skcapi.co.jp] > > Sent: 19 September 2003 03:24 > > To: Dave Page > > Cc: pgadmin-hackers@postgresql.org > > Subject: Re: [pgadmin-hackers] The comment of Primary key > > > > > > Hi Dave. > > > > Hmm.. > > pg_dumpall can't take it.... > > I had never used this comment sentence until now. > > Did anyone use this? > > What pg_dumpall doesn't handle constraint comments? If that > is so, it should be reported to the pgsql-bugs@postgresql.org > list. I don't think it's a reason for us not to allow such > comments though. > > Regards, Dave. > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org >