Short description of a problem

I need to build a partitioned table "users" with 2 partitions located on separate servers (Moscow and Hamburg), each partition is table with columns:
id - integer primary key with auto increment
region - smallint partition key, which equals either 100 for Hamburg or 200 for Moscow
login - unique character varying with length of 100.

I intended to make sequences for id as n*1000 100 for Hamburg, and n*1000 200 for Moscow, so just looking on primary key I will know which partition it belongs to.

region is intended to be read only and never change after creation, so no records will move between partitions.

SELECT queries must be able to return records from all partitions and UPDATE queries must be able to modify records on all partitions, INSERT/DELETE queries must be able to add/delete records only to local partition, so data stored in them is not completely isolated.

What was done

Using pgAdmin4

1. I created a "test" table on Hamburg server, added all column info, marked it as partitioned table with partition key region and partition type List.
2. I created a "hamburg" partition in this table, adding primary key constraint as id,region and unique key constraint as login,region.
3. I created a "moscow" table on Moscow server with the same column info as "test"
4. I added postgres_fdw extension to Hamburg server, created Foreign server pointing to DB on Moscow server and User mapping.
5. I added "moscow" foreign table to Hamburg server pointing to "moscow" table on Moscow server.

What is my problem

I couldn't figure out how to attach this foreign table as second partition to "test" table.

When I tried to attach partition through pgAdmin dialog in "test" table partitions properties it shows me an error: cannot unpack non-iterable Response object

When I tried to add partition with query as follows:

ALTER TABLE public.test ATTACH PARTITION public.moscow FOR VALUES IN (200);

It shows me an error:

ERROR:  cannot attach foreign table "moscow" as partition of partitioned table "test"
DETAIL:  Table "test" contains unique indexes.
SQL state: 42809

I removed unique constraint from login column but it shows the same error.

When I make partitioned table with the same properties and both partitions initially located on the same server all works well, except for postgres watch for login uniqueness per-partition rather than in whole table, but I suggest this is its limitation.

So, how can I attach a table located on the second server as partition to partitioned table located on the first one?

CodePudding user response：

The error message is pretty clear: Since you cannot create an index on a partitioned table, PostgreSQL cannot create a partition of the unique index. But the unique index is required to implement the constraint.

See this source comment:

/*
 * If we're attaching a foreign table, we must fail if any of the indexes
 * is a constraint index; otherwise, there's nothing to do here.  Do this
 * before starting work, to avoid wasting the effort of building a few
 * non-unique indexes before coming across a unique one.
 */

Either drop the unique constraint or don't use foreign tables as partitions.

CodePudding user response：

Ok, I was finally able to add a foreign table as partition to partitioned table.

1. It was necessary to drop primary key property on id and unique property on login columns for partitioned table
2. After that I was able to attach foreign table as partition to partitioned table
3. Later I have added primary key property on id and unique property on login columns for each local partition.

So in the end I have unique global id as it is generated by sequences for each DB with never intersected values. For login uniqueness I have to manually check global table if there is any record with it before inserting.

P.S. Hopefully, this partitioning mechanism in postgres is suitable for geographically distant regions.