On Mon, 2 Sept 2024 at 19:13, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:
On Sun, 2024-09-01 at 01:32 +0530, veem v wrote:
> due to postgres limitations we are unable to have this unique constraint or primary key
> only on the transaction_id column, we have to include transaction_timestamp with it as
> a composite key. So I want to understand from experts if there is any possible way to
> satisfy both partitioning on transaction_timestamp column and unique key or pk just on
> transaction_id only?
No, you cannot have both.
Usually the solution is to *not* create a primary key on the partitioned table
and instead create a primary key on each partition.
That won't guarantee global uniqueness (and there is no way to do that), but it
goes a long way by ensuring that the column is unique within each partition.
Yours,
Laurenz Albe
Thank you so much.
So it means in our case the existing PK on table level on column (txn_id and txn_timestamp), we should drop that and create a unique index on each partition level and also the same way the foreign key also maps to the parent table partitions. And in that case , can we follow this as best practices to not have the primary keys defined at the tabe level at all, but on the partition level only, or there exist any down side to it too? Please suggest.
Also then what I see is, it will make the data load query fail which uses "insert on conflict" to insert data into the table and that requires the primary key on both the columns to have on table level. Also the partition maintenance job which uses partman extension uses the template table which in turn uses table level properties for creating new partitions and they will not have these unique indexes created for the new partitions as because the unique index property is not on the table level but partition level. Can you share your thoughts on these?
