search

postgrespro / pg_pathman

Partitioning tool for PostgreSQL
Other
580 stars 67 forks source link

Foreign keys on partitions #148

Open did16 opened 6 years ago

did16 commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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

funbringer commented 6 years ago

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 commented 6 years ago

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

maksm90 commented 6 years ago

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 commented 6 years ago

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

did16 commented 6 years ago

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

funbringer commented 6 years ago

@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 commented 6 years ago

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 commented 5 years ago

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?