ALTER TABLE / ADD CONSTRAINT USING INDEX is not supported on partitioned tables (PostgreSQL 12)

[crimean-celica]

Seems, you have some issues with partitioned tables (PostgreSQL 12.7)

On DB "postgres":

create database f1 ENCODING = 'UTF8';
create database f2 ENCODING = 'UTF8';

On DB "f1":

create table t1 ( id int , doy int ) partition by list ( doy )
create table t1_1 partition of t1 for values in ( 1 )
create table t1_2 partition of t1 for values in ( 2 )
alter table t1 add constraint pk_t1 primary key ( id, doy )

$ migra --unsafe postgresql:///f2 postgresql:///f1 > /tmp/t1.sql

t1.sql:

create table "public"."t1" (
"id" integer not null,
"doy" integer not null
) partition by LIST (doy);

create table "public"."t1_1" partition of "public"."t1" FOR VALUES IN (1);

create table "public"."t1_2" partition of "public"."t1" FOR VALUES IN (2);

CREATE UNIQUE INDEX pk_t1 ON ONLY public.t1 USING btree (id, doy);

CREATE UNIQUE INDEX t1_1_pkey ON public.t1_1 USING btree (id, doy);

CREATE UNIQUE INDEX t1_2_pkey ON public.t1_2 USING btree (id, doy);

alter table "public"."t1" add constraint "pk_t1" PRIMARY KEY using index "pk_t1";

alter table "public"."t1_1" add constraint "t1_1_pkey" PRIMARY KEY using index "t1_1_pkey";

alter table "public"."t1_2" add constraint "t1_2_pkey" PRIMARY KEY using index "t1_2_pkey";

$ psql -d f2 -f /tmp/t1.sql > /tmp/t1.log
psql:/tmp/t1.sql:19: ERROR: index "pk_t1" is not valid
LINE 1: alter table "public"."t1" add constraint "pk_t1" PRIMARY KEY...

Example (1):

create table t1 ( id int , doy int ) partition by list ( doy );
create table t1_1 partition of t1 for values in ( 1 );
create table t1_2 partition of t1 for values in ( 2 );
create unique index ix1 on ONLY t1 ( id, doy );
alter table t1 add constraint pk_t1 primary key using index ix1;

ERROR: index "ix1" is not valid
LINE 1: alter table t1 add constraint pk_t1 primary key using index ...

Example (2):

create table t1 ( id int , doy int ) partition by list ( doy );
create table t1_1 partition of t1 for values in ( 1 );
create table t1_2 partition of t1 for values in ( 2 );
create unique index ix1 on t1 ( id, doy );
alter table t1 add constraint pk_t1 primary key using index ix1;

ERROR: ALTER TABLE / ADD CONSTRAINT USING INDEX is not supported on partitioned tables

p.s.

"pg_dump" copies such schema (partitioned table + primary key) between DBs f1 and f2 with no issues

[alexander-smyslov]

Same behavior on PostgreSQL 13.3

[jgnieuwhof]

running into this issue as well, postgres 14.

did either of you find a solution or workaround?

[sgtsquiggs]

I wrote a python script to "fix" this. I would not recommend using this as-is because it only covers my use case.

import re
import sys
from argparse import ArgumentParser
from pathlib import Path
from typing import List

if __name__ == "__main__":
    # parse filename argument using argparse
    parser = ArgumentParser(
        description="Make migra migrations work with partitioned tables!"
    )
    parser.add_argument("filename", nargs="+", type=Path, help="path to file(s)")
    args = parser.parse_args()

    filenames: List[Path] = args.filename

    for filename in filenames:
        print("Processing file: " + str(filename), file=sys.stderr)

        with open(filename, "r") as f:
            sql = f.read()
            lines = sql.splitlines()

            tables = []

            create_table_start_re = re.compile(r"^create table (.+) \($")
            create_table_end_re = re.compile(r"^\)")
            for idx, line in enumerate(lines):
                match = create_table_start_re.match(line)
                if match:
                    table_name = match.group(1)
                    # find end
                    for sidx, line in enumerate(lines[idx:]):
                        if create_table_end_re.match(line):
                            tables.append(
                                {
                                    "name": table_name,
                                    "start": idx,
                                    "end": idx + sidx,
                                }
                            )
                            break

            # find pkey columns per table
            for table in tables:
                create_unique_index_re = re.compile(
                    r"^create unique index .+_pkey on (?:only )?"
                    + re.escape(re.sub('"', "", table["name"]))
                    + r" using .+ (\(.+\)).*$"
                )
                for line in lines:
                    match = create_unique_index_re.match(line)
                    if match:
                        table["pkeys"] = match.group(1)

            # any table with pkeys is removed
            tables = [table for table in tables if "pkeys" in table]

            # insert line to create pk when table is created
            shift = 0
            for table in tables:
                # apply shift
                table["start"] += shift
                table["end"] += shift
                # add comma to last column line
                lines[table["end"] - 1] += ","
                # insert primary key line
                lines.insert(table["end"], "    primary key " + table["pkeys"])
                # update table end
                table["end"] += 1
                # increment shift due to added line
                shift += 1

            sql = "\n".join(lines)

            # drop index lines
            for table in tables:
                create_unique_index_re = re.compile(
                    r"^create unique index .+_pkey on (?:only )?"
                    + re.escape(re.sub('"', "", table["name"]))
                    + r" using .+ (\(.+\)).*\n\n",
                    flags=re.MULTILINE,
                )
                alter_table_re = re.compile(
                    r"^alter table "
                    + re.escape(table["name"])
                    + r" add constraint \".+_pkey\".*\n\n",
                    flags=re.MULTILINE,
                )
                sql = re.sub(create_unique_index_re, "", sql)
                sql = re.sub(alter_table_re, "", sql)

            # remove "only" from create indexes
            sql = re.sub("on only", "on", sql)

            # remove "not valid" on foreign keys
            sql = re.sub(" not valid", "", sql)

            # remove "validate constraint" on foreign keys
            sql = re.sub(r"^.*validate constraint.*\n$", "", sql, flags=re.MULTILINE)

        with open(filename, "w") as f:
            f.write(sql)

[joshainglis]

Try this branch and see if it works for you: #237
Pretty sure I’ve handled the partition edge cases in there.

[sgtsquiggs]

Issue still remains - when it generates code for a partitioned table it adds the primary key via

create table "schemaname"."tablename" (
    "id" text not null
) partition by range (id);

create unique index tablename_pkey on schemaname.tablename using btree (id);

alter table "schemaname"."tablename" add constraint "tablename_pkey" primary key using index "tablename_pkey";

pg16 will throw error ERROR: ALTER TABLE / ADD CONSTRAINT USING INDEX is not supported on partitioned tables when executing this.

This works though:

create table "schemaname"."tablename" (
    "id" text not null
) partition by range (id);

alter table only "schemaname"."tablename" add constraint "tablename_pkey" primary key (id);