Date: Thu, 25 Oct 2018 23:16:55 +0200
From: "Jehan-Guillaume (ioguix) de Rorthais" <ioguix@free.fr>
To: legrand legrand <legrand_legrand@hotmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Is there a work around for partition key needed for constraint
Message-ID: <20181025231655.615d80c2@firost>
In-Reply-To: <1540501366915-0.post@n3.nabble.com>
References: 
 <CACut7uTMN=E-OK+gdvUU3BMn+O+OAbA9nd3R=W9wLfnvv2TaHg@mail.gmail.com>
	<1540407880009-0.post@n3.nabble.com>
	<CACut7uS5pRV1=Y-p2q2-udF8BB8xjf4SaHj0BkYWDLBbM6yeRA@mail.gmail.com>
	<1540501366915-0.post@n3.nabble.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Precedence: bulk

On Thu, 25 Oct 2018 14:02:46 -0700 (MST)
legrand legrand <legrand_legrand@hotmail.com> wrote:

> Maybe there is a solution equivalent to ON CONFLICT DO NOTHING,
> with some BEFORE INSERT TRIGGERS returning NULL when row already exists.
> 
> Something like:
> 
> create table tabpart (account_id integer not null, customer_id integer not
> null, date_added date ) partition by list (date_added);
> create table tabpart1  partition of tabpart for values in ('2018-10-24');
> create table tabpart2  partition of tabpart for values in ('2018-10-25');
> ...
> create index tabpart_index_id on tabpart(account_id,customer_id);
> 
> CREATE OR REPLACE FUNCTION f_check_pkey() RETURNS TRIGGER AS $$
> 	BEGIN
> 	  IF count(1) >= 1 FROM tabpart WHERE account_id  = NEW.account_id 
>                                    AND customer_id = NEW.customer_id 
>           THEN
> 	        RETURN NULL;
> 	  ELSE
> 		RETURN NEW;
> 	  END IF;
> 	END;
> $$ LANGUAGE plpgsql ;

It seems to me you lack some locking to avoid duplicates. cf. my blog post we
discussed few weeks ago.

(btw, sorry, I somehow missed your answers on previous thread b/c holidays).