Table Inheritance Creation is out of Order (regression of #99)

[autumnjolitz]

First off, thank you for adding inheritance migration and closing #99 ! 😃

Problem:

Migra issues the CREATE TABLE statements out of order. In this case, the CREATE TABLE for the root "ratings" table is not issued until AFTER the child table is created with inheritance from the (nonexistent) root table.

Steps to reproduce:

Uses:

- docker
- fresh virtual env (not required, just there to prove its wholly PyPI reproducable)

Terminal 1

(cpython36) Fateweaver:~$ docker run --rm -p5432:5432 -it postgres:10-alpine

Terminal 2

(cpython36) Fateweaver:~$ docker run --rm -p5433:5433 -it postgres:10-alpine -p 5433

Terminal 3

(cpython36) Fateweaver:~$ psql postgres://postgres@localhost:5432/postgres
psql (10.11, server 10.10)
Type "help" for help.

postgres=# create schema new_schema;
CREATE SCHEMA
postgres=#
postgres=# create sequence "ratings_id_seq";
CREATE SEQUENCE
postgres=#
postgres=# create table "new_schema"."ratings" (
postgres(#    "tenant_id" bigint not null,
postgres(#    "id" bigint not null default nextval('ratings_id_seq'::regclass),
postgres(#    "something_else_id" bigint not null,
postgres(#    "something_else_type" text not null,
postgres(#    "score" numeric(7,6) not null,
postgres(#    "adate" timestamp without time zone not null default timezone('UTC'::text, CURRENT_TIMESTAMP),
postgres(#    "bdate" timestamp without time zone not null default timezone('UTC'::text, CURRENT_TIMESTAMP)
postgres(# );
CREATE TABLE
postgres=#
postgres=# CREATE TABLE new_schema.comment_ratings (
postgres(#     UNIQUE(tenant_id, something_else_id, something_else_type),
postgres(#     CONSTRAINT "comment bindings require something_else_type comment!" CHECK(something_else_type = 'comment')
postgres(# ) INHERITS (new_schema.ratings)
postgres-# ;
CREATE TABLE
postgres=# \q
(test-migra) Fateweaver:~$ mkvirtualenv test-migra
(test-migra) Fateweaver:~$ python -m pip install migra
(test-migra) Fateweaver:~$ python -m pip install psycopg2-binary
(test-migra) Fateweaver:~$ migra --with-privileges --unsafe postgres://postgres@localhost:5433/postgres postgres://postgres@localhost:5432/postgres
create schema if not exists "new_schema";

create sequence "public"."ratings_id_seq";

create table "new_schema"."comment_ratings" (
    "tenant_id" bigint not null,
    "id" bigint not null default nextval('ratings_id_seq'::regclass),
    "something_else_id" bigint not null,
    "something_else_type" text not null,
    "score" numeric(7,6) not null,
    "adate" timestamp without time zone not null default timezone('UTC'::text, CURRENT_TIMESTAMP),
    "bdate" timestamp without time zone not null default timezone('UTC'::text, CURRENT_TIMESTAMP)
) inherits ("new_schema"."ratings");


create table "new_schema"."ratings" (
    "tenant_id" bigint not null,
    "id" bigint not null default nextval('ratings_id_seq'::regclass),
    "something_else_id" bigint not null,
    "something_else_type" text not null,
    "score" numeric(7,6) not null,
    "adate" timestamp without time zone not null default timezone('UTC'::text, CURRENT_TIMESTAMP),
    "bdate" timestamp without time zone not null default timezone('UTC'::text, CURRENT_TIMESTAMP)
);


CREATE UNIQUE INDEX comment_ratings_tenant_id_something_else_id_something_else__key ON new_schema.comment_ratings USING btree (tenant_id, something_else_id, something_else_type);

alter table "new_schema"."comment_ratings" add constraint "comment bindings require something_else_type comment!" CHECK ((something_else_type = 'comment'::text));

alter table "new_schema"."comment_ratings" add constraint "comment_ratings_tenant_id_something_else_id_something_else__key" UNIQUE using index "comment_ratings_tenant_id_something_else_id_something_else__key";

(test-migra) Fateweaver:~$

[LydiaF]

Hey, we are also experiencing this issue :)

[mingfang]

The workaround is to manually edit the generated migration sql file and replace all inherits clause from all the create table statements with alter table <> inhert <> statements.

In the example above, the workaround would look like this.

create table "new_schema"."comment_ratings" (
    "tenant_id" bigint not null,
    "id" bigint not null default nextval('ratings_id_seq'::regclass),
    "something_else_id" bigint not null,
    "something_else_type" text not null,
    "score" numeric(7,6) not null,
    "adate" timestamp without time zone not null default timezone('UTC'::text, CURRENT_TIMESTAMP),
    "bdate" timestamp without time zone not null default timezone('UTC'::text, CURRENT_TIMESTAMP)
);


create table "new_schema"."ratings" (
    "tenant_id" bigint not null,
    "id" bigint not null default nextval('ratings_id_seq'::regclass),
    "something_else_id" bigint not null,
    "something_else_type" text not null,
    "score" numeric(7,6) not null,
    "adate" timestamp without time zone not null default timezone('UTC'::text, CURRENT_TIMESTAMP),
    "bdate" timestamp without time zone not null default timezone('UTC'::text, CURRENT_TIMESTAMP)
);

alter table "new_schema"."comment_ratings" inherit "new_schema"."ratings";