> On Jul 11, 2021, at 17:36, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
>
> personally, I feel this design is very bad compared to other DB servers.
Patches accepted. The issue is that in order to have a partition-set-wide unique index, the system would have to lock
theunique index entries in *all* partitions, not just the target one. This logic does not currently exist, and it's not
trivialto implement efficiently.
> can I use some trigger on the partition table before inserting the call that function this one handle conflict?
That doesn't handle the core problem, which is ensuring that two different sessions do not insert the same
(billing_account_uid,ban) into two different partitions. That requires some kind of higher-level lock. The example
yougive isn't required; PostgreSQL will perfectly happily accept a unique constraint on (billing_account_uid, ban) on
eachpartition, and handle attempts to insert a duplicate row correctly (either by returning an error or processing an
ONCONFLICT) clause. What that does not prevent is a duplicate (billing_account_uid, ban) in two different partitions.
There's another issue here, which is this design implies that once a particular (billing_account_uid, ban) row is
createdin the partitioned table, it is never deleted. This means older partitions are never dropped, which means the
numberof partitions in the table will row unbounded. This is not going to scale well as the number of partitions
startsgetting very large.
You might consider, instead, hash-partitioning on one of billing_account_uid or ban, or reconsider if partitioning is
theright solution here.
