BUG #3774: create table like including index doesn't update pg_constraints with primary key

From: "guillaume (ioguix) de Rorthais" <ioguix(at)free(dot)fr>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3774: create table like including index doesn't update pg_constraints with primary key
Date: 2007-11-22 17:47:50
Message-ID: 200711221747.lAMHloZK074899@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers


The following bug has been logged online:

Bug reference: 3774
Logged by: guillaume (ioguix) de Rorthais
Email address: ioguix(at)free(dot)fr
PostgreSQL version: 8.3 beta3
Operating system: mac os x 10.4.10
Description: create table like including index doesn't update
pg_constraints with primary key
Details:

When creating a table using the "create table ... (like ... inluding
indexes...)" syntaxe, pg_catalog.pg_constraint is not updated with the PK
constraints which actually is setted in pg_index.

Here is my test script :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
pagila=# --the original table

\d city



Table "public.city"
Column | Type | Modifiers

-------------+-----------------------------+--------------------------------
------------------------
city_id | integer | not null default
nextval('city_city_id_seq'::regclass)
city | character varying(50) | not null
country_id | smallint | not null
last_update | timestamp without time zone | not null default now()
Indexes:
"city_pkey" PRIMARY KEY, btree (city_id)
"idx_fk_country_id" btree (country_id)
Foreign-key constraints:
"city_country_id_fkey" FOREIGN KEY (country_id) REFERENCES
country(country_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
last_updated BEFORE UPDATE ON city FOR EACH ROW EXECUTE PROCEDURE
last_updated()

pagila=# -- its pk constraint in pg_constraint

SELECT relname,
conname, contype

FROM pg_class cl


JOIN pg_constraint co ON (cl.oid=co.conrelid)


JOIN pg_namespace n ON (cl.relnamespace=n.oid)

WHERE
cl.relname='city' AND n.nspname='public' AND contype='p';
relname | conname | contype
---------+-----------+---------
city | city_pkey | p
(1 row)

pagila=# -- create the new table citylike like city

CREATE TABLE
citylike (LIKE city INCLUDING INDEXES INCLUDING DEFAULTS);
CREATE TABLE
pagila=# --the citylike table

\d citylike
Table "public.citylike"
Column | Type | Modifiers

-------------+-----------------------------+--------------------------------
------------------------
city_id | integer | not null default
nextval('city_city_id_seq'::regclass)
city | character varying(50) | not null
country_id | smallint | not null
last_update | timestamp without time zone | not null default now()
Indexes:
"citylike_pkey" PRIMARY KEY, btree (city_id)
"citylike_country_id_key" btree (country_id)

pagila=# -- citylike constraints'
pagila=# SELECT relname, conname, contype

FROM
pg_class cl

JOIN pg_constraint co
ON (cl.oid=co.conrelid)

JOIN pg_namespace n ON
(cl.relnamespace=n.oid)

WHERE cl.relname='citylike' AND
n.nspname='public' AND contype='p';
relname | conname | contype
---------+---------+---------
(0 rows)

pagila=#
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I'm not sure if this issue is actually a bug or if there a logic behind
this, but as the primary key is a constraint, I would expect it to be setted
in pg_constraint, shouldn't it ?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Heikki Linnakangas 2007-11-22 17:59:01 Re: BUG #3773: psql segfault on exit
Previous Message guillaume 'ioguix' de Rorthais 2007-11-22 17:34:24 BUG #3773: psql segfault on exit

Browse pgsql-hackers by date

  From Date Subject
Next Message Jonah H. Harris 2007-11-22 17:48:37 Re: 8.3devel slower than 8.2 under read-only load
Previous Message Guillaume Smet 2007-11-22 17:45:22 Re: 8.3devel slower than 8.2 under read-only load