Re: Partition column should be part of PK

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



personally, I feel this design is very bad compared to other DB servers.

> If the goal is to make sure there is only one (billing_account_uid, ban) in any partition regardless of date, you'll need to do something more sophisticated to make sure that two sessions don't insert an (billing_account_uid, ban) value into two different partitions.  This isn't a great fit for table partitioning, and you might want to reconsider if partitioning the table is the right answer here.  If you *must* have table partitioning, a possible algorithm is:

yes, this is my use case.

can I use some trigger on the partition table before inserting the call that function this one handle conflict? 


CREATE or replace FUNCTION insert_trigger()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
      conn_name text;
  c_table TEXT;
  t_schema text;
  c_table1 text;
  m_table1 text;
    BEGIN
    c_table1 := TG_TABLE_NAME;
    t_schema := TG_TABLE_SCHEMA;
    m_table1 := t_schema||'.'||TG_TABLE_NAME;
    SELECT conname FROM pg_constraint WHERE conrelid = TG_TABLE_NAME ::regclass::oid and contype = 'u' into conn_name;
    execute 'insert into '|| m_table1 || ' values ' || new.* || ' on conflict on constraint ' || conn_name || ' do nothing -- or somthing';
    RETURN null;
    end;
    $BODY$;

CREATE TRIGGER insert
    BEFORE INSERT
    ON t4
    FOR EACH ROW
    WHEN (pg_trigger_depth() < 1)
    EXECUTE FUNCTION insert_trigger();
    CREATE TRIGGER insert
    BEFORE INSERT
    ON t3
    FOR EACH ROW
    WHEN (pg_trigger_depth() < 1)
    EXECUTE FUNCTION insert_trigger(); .. so on ..
    


https://dbfiddle.uk/?rdbms=postgres_11&fiddle=bcfdfc26685ffb498bf82e6d50da95e3


Please suggest.


Thanks,
Rj

On Thursday, July 8, 2021, 08:52:35 PM PDT, Christophe Pettus <xof@xxxxxxxxxxxx> wrote:




> On Jul 8, 2021, at 20:32, Nagaraj Raj <nagaraj.sf@xxxxxxxxx> wrote:
>
> My apologies for making confusion with new thread. Yes its same issue related to earlier post.
>
> I was trying to figure out  how to ensure unique values for columns (billing_account_guid, ban). If i add partition key to constraint , it wont be possible what im looking for.
>
> My use case as below
>
> INSERT INTO t1 SELECT * from t2 ON CONFLICT (billing_account_guid,ban) DO UPDATE SET something…
>
> Or
>
> INSERT INTO t1 SELECT * from t2 ON CONFLICT constraint (pk or uk)(billing_account_guid,ban) DO UPDATE SET something…

Right now, PostgreSQL does not support unique indexes on partitioned tables (that operate across all partitions) unless the partition key is included in the index definition.  If it didn't have that requirement, it would have to independently (and in a concurrency-supported way) scan every partition individually to see if there is a duplicate key violation in any of the partitions, and the machinery to do that does not exist right now.

If the goal is to make sure there is only one (billing_account_guid, ban, date) combination across the entire partition set, you can create an index unique index on the partitioned set as (billing_account_guid, ban, date), and INSERT ... ON CONFLICT DO NOTHING works properly then.

If the goal is to make sure there is only one (billing_account_uid, ban) in any partition regardless of date, you'll need to do something more sophisticated to make sure that two sessions don't insert an (billing_account_uid, ban) value into two different partitions.  This isn't a great fit for table partitioning, and you might want to reconsider if partitioning the table is the right answer here.  If you *must* have table partitioning, a possible algorithm is:

-- Start a transaction
-- Hash the (billing_account_uid, ban) key into a 64 bit value.
-- Use that 64 bit value as a key to a call to pg_advisory_xact_lock() [1] to, in essence, create a signal to any other transaction attempting to insert that pair that it is being modified.
-- SELECT on that pair to make sure one does not already exist.
-- If one does not, do the INSERT.
-- Commit, which releases the advisory lock.

This doesn't provide quite the same level of uniqueness that a cross-partition index would, but if this is the only code path that does the INSERT, it should keep duplicate from showing up in different partitions.


[1]
https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux