Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-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

pgsql-hackers by date

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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group