Foreign keys on partitions

[did16]

Problem description

What is the availability date of pg_pathman 1.5 release ?. Several projets in my firm do not want to migrate to Postgresql, because Partitioning 10 does not support primary and foreign keys. It is a big missing funtionnality.

Environment

[funbringer]

Hi @did16

We had some plans for 1.5, but, unfortunately, the focus has changed significantly. Our main goal now is to develop native partitioning. This effectively means that all development progress in this repo has been frozen.

However, this is not to say that we're abandoning the project completely. We will fix crucial bugs until pg_pathman becomes obsolete.

[did16]

Hi @funbringer Thank you for your answer. Can you tell me if native partitioning will support primary and foreign keys and in what PostgreSQL release ? Thanks in advance

[funbringer]

Can you tell me if native partitioning will support primary and foreign keys and in what PostgreSQL release?

Both primary and foreign keys require global indexes, which have not been proposed yet. Perhaps @zilder could provide a more meaningful commentary.

[did16]

It is a big lack. So we need to have pg_pathman 1.5 release very soon. How can we speed the process ?

[funbringer]

So we need to have pg_pathman 1.5 release very soon. How can we speed the process?

Basically, you can't. We see no point in further development. Moreover, pg_pathman also requires global indexes. What we wanted to do is provide some basic support for foreign keys on partitioning key.

[funbringer]

You could try rel_future_beta branch, but it's rough around the edges.

[maksm90]

pg_pathman also requires global indexes

@funbringer, did you consider the case when global uniqueness of field that's not related with partitioning key is provided via checks on local unique indexes of partitions?

[funbringer]

@maksm90 Actually, I didn't. Maybe you could shed some light on this case?

[did16]

Hi Can you read that post . It is said that global unique indexes on partitioned tables is possible.

[funbringer]

@did16

It is said that global unique indexes on partitioned tables is possible.

This commit message is not about global indexes; it's all about a special case with partition key.

[zilder]

I haven't seen the whole patch, but ISTM what Alvaro Herrera did is allowed to create unique constraint on partitioned tables when it is built on the same attributes as partitioning key. This doesn't mean they added global index (they use bunch of local indexes to guarantee uniqueness). And it doesn't mean you can use it for foreign key yet. But there is actually one more step left to implement that too.

In pg_pathman we experimented with that and it even worked for single level partitioning (see rel_future_ri branch). But it gets complicated with multilevel. Now we would rather focus on developing patches for postgres core itself as it will replace most of pg_pathman's features.

[tarkhil]

Hi @did16

We had some plans for 1.5, but, unfortunately, the focus has changed significantly. Our main goal now is to develop native partitioning. This effectively means that all development progress in this repo has been frozen.

Will it be available in opensource Postgres, or only in PostgresPro?