Re: Partitioning/inherited tables vs FKs

On May 6, 2010, at 4:31 AM, Florian Pflug wrote:
>> The use case for this was there were different news items,
>> and there were another table for summaries, that could point
>> to any of the news items table. Another use case could be
>> a large partitioned table with an FK to the main table where
>> the referring table might only contain very few "interesting" data.
>
> Yeah, this is a long-standing issue with inheritance. Table inheritance in postgres isn't much more than an implicit
UNIONdone on selects plus some logic in ALTER TABLE to keep propagate structural changes. Indices and constraints
basicallyalways behave as if ONLY had been specified. I'm not even sure if the ids are globally unique in your example
-it might be that each child's "id serial" column gets its very own sequence. 
>
> One possible workaround is no create a table, say referred_ids, that contains all the ids from parent and all of its
children,kept up-to-date via triggers, and point the FK constraint to that table. That also allows for a global unique
constrainton the ids by definition a suitable unique or primary key constraint on referred_ids. 
>
> What lies at the heart of this problem is the lack of multi-table indices and hence multi-table unique constraints in
postgres.AFAIK with those in place the rest amounts to the removal of ONLY from the constraint check queries plus some
codeto propagate constraint triggers to child tables. 

FWIW, we use inheritance for something other than partitioning, and I created a trigger that provides a crude form of a
foreignkey constraint, as well as one that provides a crude global unique constraint on the PK. Both probably have
holesand race conditions, but I figure they're better than just hoping no one screws something up. 

BTW, my intention is to release all the generic tools we've developed to pgFoundry, it just hasn't happened yet. If
enoughpeople find this stuff interesting I can try and up the priority on getting that done. (And if you're *really*
wantingthis stuff you could pay 2nd Quadrant or CMD to get it for you.) 

test_us@workbook.local=# \df+ payment_instruments.tg_payment_instruments_unique
List of functions
-[ RECORD 1
]-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema              | payment_instruments
Name                | tg_payment_instruments_unique
Result data type    | trigger
Argument data types |
Volatility          | volatile
Owner               | cnuadmin
Language            | plpgsql
Source code         |                    :                    : DECLARE                   : name CONSTANT text :=
'payment_instruments.tg_payment_instruments_unique';                  : c_full_table_name CONSTANT text :=
TG_TABLE_SCHEMA|| '.' || TG_TABLE_NAME;                   : BEGIN                   :     PERFORM tools.assert( TG_WHEN
='BEFORE', TG_NAME || ' ON ' || c_full_table_name ||' must be an BEFORE trigger' );                   :     PERFORM
tools.assert(TG_LEVEL = 'ROW', TG_NAME || ' ON ' || c_full_table_name ||' must be a row-level trigger' );
   :                        :     -- Deleting would break RI, so don't allow it. Granted, this should probably be a
separatetrigger, but...                   :     PERFORM tools.assert(
'payment_instruments__payment_instruments__inherit__no_delete'                  :         , TG_OP != 'DELETE'
       :         , 'DELETEs are not allowed on ' || c_full_table_name || ' (they would break inheritance RI)'
       :     );                   :                    :     RAISE DEBUG '%:                   :         TG_OP = %
            :         TG_TABLE_NAME = %                   :         NEW.payment_instrument_id = %'                   :
      , name                   :         , TG_OP                   :         , TG_TABLE_NAME                   :
, NEW.payment_instrument_id                    :     ;                   :                    :     -- Changing the PK
wouldbreak RI, so we shouldn't allow it. Granted, this should probably be a separate trigger, but...
:    IF TG_OP = 'UPDATE' THEN                   :         PERFORM tools.assert(
'payment_instruments__payment_instruments__inherit__pk_no_change'                  :             ,
NEW.payment_instrument_idIS NOT DISTINCT FROM OLD.payment_instrument_id                   :             , 'Changing
payment_instrument_idon ' || c_full_table_name || ' is not allowed (it would break inheritance RI)'                   :
       );                   :     ELSE                   :         -- Only check for dupes on insert, otherwise we'll
seeour own ID                   :         PERFORM tools.assert(
'payment_instruments__payment_instruments__inherit__unique'                  :             , NOT EXISTS( SELECT * FROM
payment_instruments.payment_instrumentsWHERE payment_instrument_id = NEW.payment_instrument_id )                   :
        , 'duplicate row violation, payment_instrument_id ' || coalesce( NEW.payment_instrument_id::text, '<NULL>' ) ||
'already exists'                   :         );                   :     END IF;                   :
:    RETURN NEW;                   : END;                   :                    :  
Description         | Trigger to try and prevent duplicated payment_instrument_ids. This trigger is in no way perfect
andhas a huge race condition, but generally these IDs should be getting assigned by a sequence, so we should not
normallyhave an issue with duped IDs anyway. 


test_us@workbook.local=# \df+ payment_instruments.tg_payment_instruments_ri
List of functions
-[ RECORD 1
]-------+------------------------------------------------------------------------------------------------------------------------------------------
Schema              | payment_instruments
Name                | tg_payment_instruments_ri
Result data type    | trigger
Argument data types |
Volatility          | volatile
Owner               | cnuadmin
Language            | plpgsql
Source code         |                    :                    : DECLARE                   :     name CONSTANT text :=
'payment_instruments.tg_payment_instruments_ri';                  :     c_full_table_name CONSTANT text :=
TG_TABLE_SCHEMA|| '.' || TG_TABLE_NAME;                   :     v_payment_instrument_type
payment_instruments.payment_instrument_types.payment_instrument_type%TYPE;                  :     v_table_name text;
              :     v_only text := '';                   :     v_result int;                   :     sql text;
        : BEGIN                   :     PERFORM tools.assert( TG_WHEN = 'AFTER', TG_NAME || ' ON ' || c_full_table_name
||'must be an AFTER trigger' );                   :     PERFORM tools.assert( TG_LEVEL = 'ROW', TG_NAME || ' ON ' ||
c_full_table_name||' must be a row-level trigger' );                   :     PERFORM tools.assert( TG_OP IN ( 'INSERT',
'UPDATE'), TG_NAME || ' ON ' || c_full_table_name ||' must be on INSERT or UPDATE' );                   :
    :     -- Th generally won't be allowed, but the trigger should still support it                   :     IF
NEW.payment_instrument_idIS NULL THEN                   :         RAISE DEBUG '%: payment_instrument_id is NULL,
skippingcheck', name;                   :         RETURN NULL;                   :     END IF;                   :
             :     -- If we're updating and we haven't modified payment_instrument_id, just bail                   :
IF TG_OP = 'UPDATE' THEN                   :         IF NEW.payment_instrument_id IS NOT DISTINCT FROM
OLD.payment_instrument_idTHEN                   :             RAISE DEBUG '%: payment_instrument_id ( = % ) is
unchanged,skipping check', name, NEW.payment_instrument_id;                   :             RETURN NULL;
  :         END IF;                   :     END IF;                   :                    :     /*                   :
    * We want to not only check for existence of the desired row, we also want                   :      * to share-lock
it.Unfortunately, sharelocks aren't implemented for                   :      * inherited tables, so we need to find the
recordin the correct table. We                   :      * can do this fairly easily if we can find out what "type" of
recordit is.                   :      */                   :                    :     -- Figure out what type of record
thisis (of course, record might not exist here)                   :     SELECT INTO v_payment_instrument_type
'payment_instruments.'|| payment_instrument_type || 's'                   :         FROM
payment_instruments.payment_instrument_types__get((                   :                     SELECT
payment_instrument_type_id                  :                         FROM payment_instruments.payment_instruments pi
               :                         WHERE pi.payment_instrument_id = NEW.payment_instrument_id                   :
               ) )                   :     ;                   :     IF NOT FOUND OR v_payment_instrument_type IS NULL
THEN                  :         -- There wasn't a record at all                   :         v_only := 'ONLY '; -- FOR
SHAREwon't work if we select from both the parent and the children                   :         v_table_name :=
'payment_instruments.payment_instruments';                  :     ELSE                   :         -- We figured out
whattype of record this is, now try and lock the row in the right table                   :         v_table_name :=
v_payment_instrument_type;                  :     END IF;                   :                    :     sql := 'SELECT 1
FROM' || v_only || v_table_name || '                   :         WHERE payment_instrument_id = ' ||
NEW.payment_instrument_id|| '                   :         FOR SHARE'                   :     ;                   :
RAISEDEBUG '%: Executing SQL %', name, sql;                   :     -- Note that simply using a PERFORM here might get
optimizedout.                   :     EXECUTE sql INTO v_result;                   :                    :     /*
          :      * Normally we should always find an record, but if it was somehow                   :      * put in
thewrong table, or if it was deleted after our initial                   :      * select then we wouldn't have one.
             :      */                   :     IF v_result = 1 THEN                   :         RAISE DEBUG '%: record
forpayment_instrument_id = % found in table %', name, NEW.payment_instrument_id, v_table_name;                   :
  RETURN NULL;                   :     END IF;                   :                    :     RAISE EXCEPTION 'insert on
updateon table "%.%" violates foreign key; payment_instrument_id=(%) is not present in table "%"'                   :
     , TG_TABLE_SCHEMA                   :         , TG_TABLE_NAME                   :         ,
NEW.payment_instrument_id                  :         , v_table_name                   :     ;                   : END;
                :                    :  
Description         | Enables Refferential Integrity at the payment_instruments level (normal RI on a table that is an
inheritanceparent does not really work) 


--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net