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

Поиск
Список
Период
Сортировка
От guillaume (ioguix) de Rorthais
Тема BUG #3774: create table like including index doesn't update pg_constraints with primary key
Дата
Msg-id 200711221747.lAMHloZK074899@wwwmaster.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #3774: create table like including index doesn't update pg_constraints with primary key  (Bruce Momjian <bruce@momjian.us>)
Список pgsql-bugs
The following bug has been logged online:

Bug reference:      3774
Logged by:          guillaume (ioguix) de Rorthais
Email address:      ioguix@free.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 ?

В списке pgsql-bugs по дате отправления:

Предыдущее
От: "guillaume 'ioguix' de Rorthais"
Дата:
Сообщение: BUG #3773: psql segfault on exit
Следующее
От: Heikki Linnakangas
Дата:
Сообщение: Re: BUG #3773: psql segfault on exit