Prev: [HACKERS] Partitioning/inherited tables vs FKs
Next: LD_LIBRARY_PATH versus rpath
From: "Kevin Grittner" on 11 May 2010 10:07
Nicolas Barbier <nicolas.barbier(a)gmail.com> wrote:

>>>>>>> Switch to T1:
>
> 1> COMMIT; -- Commit the insertion...
> COMMIT
>
>>>>>>> T2 continues:
>
> DELETE 1
> T2> COMMIT; -- Commit the deletion of a with i = 1.
> COMMIT
> T2> SELECT * FROM b EXCEPT SELECT * FROM a;
> a_id
> ------
> 1
> (1 Zeile)
>
> Woops.

This is exactly the sort of issue for which true serializable
behavior will provide a solution. I will be offering a patch to
implement that for 9.1 once 9.0 settles down. FWIW when you commit
T1, the patched code rolls back T2 with this message:

T2> DELETE FROM a WHERE i = 1;
ERROR: could not serialize access due to read/write dependencies
among transactions
HINT: The transaction might succeed if retried.

Thanks for the example; I will it to the others.

-Kevin

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Jim Nasby on 17 May 2010 13:45
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(a)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 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(a)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_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(a)nasby.net
512.569.9461 (cell) http://jim.nasby.net



--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Greg Stark on 18 May 2010 06:20
On Thu, May 6, 2010 at 10:38 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote:
> * the index grows as the size of the total data set, it's not limited
> by partition size
>
> * can't cheaply drop one partition any more, you have to vacuum the
> (big) index first

So I wholeheartedly agree with the general sentiment that if you need
global indexes then partitioning just isn't really the right tool for
you.

But it occurs to me that we could defer the vacuum safely. I'm
assuming a index heap-tid pointer for a global index would include a
relid or some other identifier to specify which partition the tuple is
in. If you drop that partition those can all just be left as dangling
pointers as long as we don't reuse that id. So all we would need is
some way to leave a catalog entry reserving that id. The data files
can be truncated and deleted normally and whenever vacuum does run
against the index it can clean up the catalog entries for the deleted
partitions.

But I would rather work on having unique and foreign key constraints
that work on keys which include the partition key than work on global
indexes.
--
greg

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

First  |  Prev  | 
Pages: 1 2 3 4
Prev: [HACKERS] Partitioning/inherited tables vs FKs
Next: LD_LIBRARY_PATH versus rpath