Re: BUG #3774: create table like including index doesn't update pg_constraints with primary key - Mailing list pgsql-bugs

Tom, did your recent commit to clean up LIKE ... INCLUDING INDEXES fix
this?

---------------------------------------------------------------------------

guillaume (ioguix) de Rorthais wrote:
>
> 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 ?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +