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 UNION done on selects plus some logic in ALTER TABLE to keep propagate structural changes. Indices and constraints basically always 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 constraint on 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 code to 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 foreign key constraint, as well as one that provides a crude global unique constraint on the PK. Both probably have holes and 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 enough people find this stuff interesting I can try and up the priority on getting that done. (And if you're *really* wanting this stuff you could pay 2nd Quadrant or CMD to get it for you.)

test_us(at)workbook(dot)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 separate trigger, 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 would break 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_id IS NOT DISTINCT FROM OLD.payment_instrument_id
: , 'Changing payment_instrument_id on ' || c_full_table_name || ' is not allowed (it would break inheritance RI)'
: );
: ELSE
: -- Only check for dupes on insert, otherwise we'll see our own ID
: PERFORM tools.assert( 'payment_instruments__payment_instruments__inherit__unique'
: , NOT EXISTS( SELECT * FROM payment_instruments.payment_instruments WHERE 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 and has a huge race condition, but generally these IDs should be getting assigned by a sequence, so we should not normally have an issue with duped IDs anyway.

test_us(at)workbook(dot)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_id IS NULL THEN
: RAISE DEBUG '%: payment_instrument_id is NULL, skipping check', 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_id THEN
: 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 record in the correct table. We
: * can do this fairly easily if we can find out what "type" of record it is.
: */
:
: -- Figure out what type of record this is (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 SHARE won't work if we select from both the parent and the children
: v_table_name := 'payment_instruments.payment_instruments';
: ELSE
: -- We figured out what type 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'
: ;
: RAISE DEBUG '%: Executing SQL %', name, sql;
: -- Note that simply using a PERFORM here might get optimized out.
: EXECUTE sql INTO v_result;
:
: /*
: * Normally we should always find an record, but if it was somehow
: * put in the wrong table, or if it was deleted after our initial
: * select then we wouldn't have one.
: */
: IF v_result = 1 THEN
: RAISE DEBUG '%: record for payment_instrument_id = % found in table %', name, NEW.payment_instrument_id, v_table_name;
: RETURN NULL;
: END IF;
:
: RAISE EXCEPTION 'insert on update on 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 inheritance parent does not really work)

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