Обсуждение: BUG #3774: create table like including index doesn't update pg_constraints with primary key

Поиск
Список
Период
Сортировка

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

От
"guillaume (ioguix) de Rorthais"
Дата:
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 ?
Hi,
 

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.

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 ?

This can be handled by setting index->isconstraint appropriately inside generateClonedIndexStmt().
 
The fundamental question though is should we allow primary, unique CONSTRAINTS which use the index mechanism just as an implementation to be created using the "INCLUDING INDEXES" mechanism.

As per the discussion here:

http://www.nabble.com/Re%3A-CREATE-TABLE-LIKE-INCLUDING-INDEXES-support-p10683716.html

maybe we should not?

In other words "INCLUDING INDEXES" should only create those indexes which do not have isconstraint set to TRUE.

Comments?

Regards,
Nikhils
--
EnterpriseDB               http://www.enterprisedb.com
NikhilS <nikkhils@gmail.com> writes:
> This can be handled by setting index->isconstraint appropriately inside
> generateClonedIndexStmt().

Done.

> The fundamental question though is should we allow primary, unique
> CONSTRAINTS which use the index mechanism just as an implementation to be
> created using the "INCLUDING INDEXES" mechanism.

Yeah, this bizarreness was foreseen and agreed to back when we set up
LIKE INCLUDING CONSTRAINTS the way it was defined (ie, copying only
CHECK constraints and not other things called constraints).  I was never
very thrilled with that definition myself, but it's a bit too late to
revisit it.
        regards, tom lane


Hi,

> The fundamental question though is should we allow primary, unique
> CONSTRAINTS which use the index mechanism just as an implementation to be
> created using the "INCLUDING INDEXES" mechanism.

Yeah, this bizarreness was foreseen and agreed to back when we set up
LIKE INCLUDING CONSTRAINTS the way it was defined (ie, copying only
CHECK constraints and not other things called constraints).  I was never
very thrilled with that definition myself, but it's a bit too late to
revisit it.
 
Yeah this is all confusing. I believe we should remove the following TODO now that the above has been checked in:
 
 
Regards,
Nikhils
--
EnterpriseDB               http://www.enterprisedb.com

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

От
Bruce Momjian
Дата:
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. +

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

От
Bruce Momjian
Дата:
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. +
Bruce Momjian <bruce@momjian.us> writes:
> Tom, did your commit to clean up LIKE ... INCLUDING INDEXES fix
> this?

Yes, see 2007-12-01 commit.

            regards, tom lane