Re: Errors creating partitioned tables from existing using (LIKE - Mailing list pgsql-bugs

On 2018 Zvita 13, China 12:03:35 +03 Amit Langote wrote:
> Hi,
>
> On 2018/12/13 5:00, Stuart wrote:
> > ousa_new=# \d+ knowledge_vectors_old
> >
> >                                                     Table
> >                                                     "public.knowledge_vec
> >                                                     tors_old"
> >
> >     Column     |            Type             | Collation | Nullable |
> >           Default             | Storage  | Stats target | Description>
> > ---------------+-----------------------------+-----------+----------+-----
> > ---------------------------+----------+--------------+------------->
> >  entry_date    | timestamp(3) with time zone |           | not null |
> >  now()                          | plain    |              | revision_date
> >  | timestamp(3) with time zone |           |          | NULL::timestamp
> >  with time zone | plain    |              | entered_by    | text
> >                |           | not null | "current_user"()               |
> >  extended |              | revised_by    | text                        |
> >           |          | ''::text                       | extended |
> >        | source_id     | bigint                      |           |
> >    |                                | plain    |              | object_id
> >      | bigint                      |           | not null |
> >                   | plain    |              | description   | text
> >                  |           |          | ''::text
> >  | extended |              | vectors       | tsvector
> >  |           | not null |                                | extended |
> >           |>
> > Indexes:
> >     "knowledgevectorsold_pk" PRIMARY KEY, btree (object_id), tablespace
> >     "pgindex" "knowledgevectorsold_vector_idx" gin (vectors), tablespace
> >     "pgindex">
> > Check constraints:
> >     "knowledgevectors_vectors_ck" CHECK (vectors <> ''::tsvector)
> >
> > Foreign-key constraints:
> >     "knowledgevectorsold_objectid_fk" FOREIGN KEY (object_id) REFERENCES
> >     knowledge(object_id)>
> > Triggers:
> >     knowledgevectors_revision_tr BEFORE UPDATE ON knowledge_vectors_old
> >     FOR EACH ROW EXECUTE PROCEDURE revised()>
> > Inherits: ousa_objects
> >
> > ousa_new=# alter table knowledge_vectors_old rename constraint
> > knowledgevectors_vectors_ck to knowledgevectorsold_vectors_ck ; ALTER
> > TABLE
> >
> > ousa_new=# create table knowledge_vectors (like knowledge_vectors_old
> > INCLUDING ALL ) PARTITION BY RANGE ( object_id ) ; ERROR:  constraint
> > "knowledgevectors_vectors_ck" for table "knowledge_vectors_old" does not
> > exist
> >
> > Only after I dropped the new constraint and recreated it, did the create
> > table (like <table>) work.
> Thanks for the report.
>
> There is a bug here, but it's not of CREATE TABLE.  It is rather of ALTER
> TABLE RENAME CONSTRAINT, which fails to reflect the changed constraint
> name in the target table's relation info cache.  Here is another
> reproducer of this behavior:
>
> create table foo (a int, constraint check_a check (a > 0));
> alter table foo rename CONSTRAINT check_a to check_a_gt_zero;
> -- in the same session
> create table bar (like foo including all);
> ERROR:  constraint "check_a" for table "foo" does not exist
>
> What might be worse is that if you specify INCLUDING CONSTRAINTS (not
> ALL), it proceeds with creating the constraint with the outdated name:
>
> create table bar (like foo including constraints);
> \d bar
>                 Table "public.bar"
>  Column │  Type   │ Collation │ Nullable │ Default
> ────────┼─────────┼───────────┼──────────┼─────────
>  a      │ integer │           │          │
> Check constraints:
>     "check_a" CHECK (a > 0)
>
> What's happening here is that when the ALTER TABLE RENAME CONSTRAINT is
> followed by CREATE TABLE (LIKE .. INCLUDING ALL) in the same session, the
> latter is referring to *stale* information about constraints of the source
> table.  You said it works correctly after you drop and re-create the
> constraint, but that's only because ALTER TABLE DROP/ADD CONSTRAINT will
> correctly invalidate the cached information, so that subsequent CREATE
> TABLE sees the correct information from the updated cache.  The way to fix
> it is to teach ALTER TABLE RENAME CONSTRAINT to reset the cached
> information.
>
> Attached a patch which does that.  With the patch:
>
> create table foo (a int, constraint check_a check (a > 0));
> alter table foo rename CONSTRAINT check_a to check_a_gt_zero;
> create table bar (like foo including all);
> \d bar
>                 Table "public.bar"
>  Column │  Type   │ Collation │ Nullable │ Default
> ────────┼─────────┼───────────┼──────────┼─────────
>  a      │ integer │           │          │
> Check constraints:
>     "check_a_gt_zero" CHECK (a > 0)
>
> -- INCLUDING CONSTRAINTS works correctly too
> drop table bar;
> create table bar (like foo including constraints);
> \d bar
>                 Table "public.bar"
>  Column │  Type   │ Collation │ Nullable │ Default
> ────────┼─────────┼───────────┼──────────┼─────────
>  a      │ integer │           │          │
> Check constraints:
>     "check_a_gt_zero" CHECK (a > 0)
>
> Thanks,
> Amit

Thanks Amit.