Re: Errors creating partitioned tables from existing using (LIKE <table>) after renaming table constraints

Hi,

On 2018/12/13 5:00, Stuart wrote:
> ousa_new=# \d+ knowledge_vectors_old
> Table "public.knowledge_vectors_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