> On Jul 11, 2021, at 17:36, Nagaraj Raj <nagaraj...@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 the unique index entries in *all*
partitions, not just the target one. This logic does not currently exist, and
it's not trivial to 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 you
give isn't required; PostgreSQL will perfectly happily accept a unique
constraint on (billing_account_uid, ban) on each partition, and handle attempts
to insert a duplicate row correctly (either by returning an error or processing
an ON CONFLICT) 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 created in the partitioned table, it is never
deleted. This means older partitions are never dropped, which means the number
of partitions in the table will row unbounded. This is not going to scale well
as the number of partitions starts getting very large.
You might consider, instead, hash-partitioning on one of billing_account_uid or
ban, or reconsider if partitioning is the right solution here.
