Re: Errors creating partitioned tables from existing using (LIKE

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.