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

Tom, did your 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. +
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

--
  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. +