BUG #13126: table constraint loses its comment

From: xi(at)resolvent(dot)net
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13126: table constraint loses its comment
Date: 2015-04-22 15:48:50
Message-ID: 20150422154850.5198.26685@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

The following bug has been logged on the website:

Bug reference: 13126
Logged by: Kirill Simonov
Email address: xi(at)resolvent(dot)net
PostgreSQL version: 9.4.1
Operating system: Ubuntu 15.04
Description:

In some circumstances, the comment on a table constraint disappears. Here
is an example:

-- Create a table with a primary key constraint.

CREATE TYPE enum1 AS ENUM ('foo', 'bar');
CREATE TYPE enum2 AS ENUM ('foo', 'bar', 'baz');

CREATE TABLE t (x enum1 NOT NULL);
ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (x);
COMMENT ON CONSTRAINT t_pk ON t IS 'the primary key of table "t"';

-- Find the constraint:
--
-- oid
-- ---------
-- 3400853
-- (1 row)

SELECT c.oid
FROM pg_constraint c
WHERE c.conname = 't_pk';

-- Find the comment on the constraint:
--
-- description
-- ------------------------------
-- the primary key of table "t"
-- (1 row)

SELECT d.description
FROM
pg_description d,
pg_constraint c
WHERE
d.classoid = 'pg_constraint'::regclass AND
c.conname = 't_pk' AND
d.objoid = c.oid;

-- Change the type of the primary key column.

ALTER TABLE t ALTER COLUMN x SET DATA TYPE enum2 USING x::text::enum2;

-- The constraint now has a different OID:
--
-- oid
-- ---------
-- 3400855
-- (1 row)

SELECT c.oid
FROM pg_constraint c
WHERE c.conname = 't_pk';

-- The constraint comment is lost:
--
-- description
-- -------------
-- (0 rows)

SELECT d.description
FROM
pg_description d,
pg_constraint c
WHERE
d.classoid = 'pg_constraint'::regclass AND
c.conname = 't_pk' AND
d.objoid = c.oid;

-- Cleanup.

DROP TABLE t;
DROP TYPE enum1;
DROP TYPE enum2;

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Slavov 2015-04-22 15:49:18 Re: BUG #12910: Memory leak with logical decoding
Previous Message stejsky 2015-04-22 12:38:24 BUG #13125: text bug

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2015-04-22 16:01:13 pgsql: RLS fixes, new hooks, and new test module
Previous Message Stephen Frost 2015-04-22 15:46:22 Re: Authenticating from SSL certificates