Unique Index over postgresql partitions

Question

I have table call it cdrs:

CREATE TABLE cdrs (
    i_cdr bigint NOT NULL,
    i_cdrs_connection bigint NOT NULL,
    i_call bigint NOT NULL,
    customer_name character varying(156) NOT NULL,
    client_name_id character varying(256) NOT NULL,
    connection_name character varying(156) NOT NULL,
    vendor_name_id character varying(256) NOT NULL,    
    setup_time timestamp with time zone NOT NULL,
    c_result_id bigint NOT NULL,
    v_result_id bigint NOT NULL
    );

Now Created partitions using inherit by trigger on insert to parent table and performed update. this functions tries to insert and if desired partition is not there then create it with indexes. we have created Unique Index on i_cdrs_connection for each partion and also created on parent.

CREATE UNIQUE INDEX i_cdrs_connection ON cdrs(i_cdrs_connection)

CREATE OR REPLACE FUNCTION cdrs_insert_trigger() RETURNS TRIGGER AS $$
BEGIN
    EXECUTE 'INSERT INTO cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' SELECT ($1).*'
    USING NEW;
    RETURN NULL;
    EXCEPTION
        WHEN undefined_table THEN
            EXECUTE 'CREATE TABLE IF NOT EXISTS cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (CHECK ( setup_time >= '''|| to_char(NEW.setup_time, 'YYYY-MM-DD 00:00') ||''' AND setup_time < '''|| to_char(NEW.setup_time + INTERVAL '1 day', 'YYYY-MM-DD 00:00') ||''' )) INHERITS (cdrs)';
            EXECUTE 'CREATE UNIQUE INDEX i_cdrs_connection_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' ON cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (i_cdrs_connection)';
            EXECUTE 'CREATE INDEX i_cdr_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' ON cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (i_cdr)';
            EXECUTE 'CREATE INDEX i_call_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' ON cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (i_call)';
            EXECUTE 'CREATE INDEX setup_time_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' ON cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (setup_time)';

        EXECUTE 'INSERT INTO cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' SELECT ($1).*'
        USING NEW;
        RETURN NULL;
END
$$
LANGUAGE plpgsql;


CREATE TRIGGER fk_checkTrigger_cdrs
BEFORE INSERT ON cdrs
FOR EACH ROW
EXECUTE PROCEDURE cdrs_insert_trigger();

Now When I try to insert duplicate i_cdrs_connections in same partition it shows unique key violation but when try same key in other partition inherited from same partition row added without error.

In summery unique index over single partition works fine but on table with more then one partitions value is not unique.

I know about sequence but is use less for given table as this will be copied from another db and we have to remove chances of duplicate insertion.

Answer 1

This is the current behavior of PostgreSQL. Unique indexes address the partition, not the table as a whole. You have a few options:

1. If at all possible, partition your tables so that the key ranges are exclusive in some way. In other words, partition on key data. This is the easiest, most hassle-free approach. Here you are partitioning on non-key data which is a problem.

2. If that doesn't work you can add the partition value to the other side of the join. Note that at this point you need custom fkey triggers.

3. If you really need to you can create a trigger-maintained materialized view of all id's and create a unique index on that.

Answer 2

I read a article about this : http://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html

and he found a real good solution.

so basically the solution is adding locks(Advisory Locks) and add a trigger like :

 CREATE OR REPLACE FUNCTION public.master_id_pkey()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  PERFORM pg_advisory_xact_lock(NEW.id);

  IF count(1) > 1 FROM master WHERE id = NEW.id THEN
     RAISE EXCEPTION 'duplicate key value violates unique constraint "%" ON "%"', 
      TG_NAME, TG_TABLE_NAME 
      USING DETAIL = format('Key (id)=(%s) already exists.', NEW.id);
  END IF;

  RETURN NULL;
END
$function$;