Re: INSERT RETURNING and partitioning

On 21 Jul 2010, at 11:35, pdovera(at)tiscali(dot)it wrote:

>> Yes, Jan's right. You're effectively overriding the return values
>> with NULL.
>>
>> Although I think I know why you're doing it, because you want to
>> redirect the value to the child table so that it doesn't get inserted
>> into the parent table as that would cause duplicate values being
>> displayed in the parent table.
>>
>> Unfortunately, you can't use RULEs as an alternative as they won't
>> allow returning values if they have conditions on them.
>>
>> Thom
>>
> I prefer to avoid duplicated rows because that is not the idea of
> partitioning
>
> Paolo

Well, you didn't insert anything in the parent table after all, did you? So the database isn't giving you the "wrong" answer, just not the one you hoped for.

The usual advice in these cases is to insert into the child tables directly. That's not really a pretty solution, especially if you tend to insert multiple rows at once that could end up in different partitions.

This problem pops up often enough that it would be worth putting some effort into a solution. A simple library that allows you to make a copy of the diagnostics, keeps reference counts to that copy (so that you can create and free it from your application without causing memory leaks) and allows you to retrieve and update it would help a lot already I think. That should be doable with just libPQ and passing that struct along with the context.

Your trigger function would need a little change in that case (pseudo-code):

CREATE OR REPLACE FUNCTION support.master_insert()
RETURNS trigger AS
$BODY$
BEGIN

-- Retrieve row-count from context

IF ( NEW.a = 1) THEN INSERT INTO support.partitionA VALUES (NEW.
*);
ELSIF ( NEW.a = 2) THEN INSERT INTO support.partitionB VALUES (NEW.*);
ELSIF ( NEW.a = 3) THEN INSERT INTO support.partitionC VALUES (NEW.*);
ELSIF ( NEW.a = 4) THEN INSERT INTO support.partitionD VALUES (NEW.*);
ELSE RAISE EXCEPTION 'A (%)is out of range ',NEW.a;
END IF;

-- Update row-count in context

RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql';

In your application you would do something like:

newRowcount(context);
execute(context, "INSERT INTO parent_table (...) SELECT ...");
count = getRowCount(context);

Maybe you could even override the database's internal diagnostics ROW_COUNT value (after taking the sum of the results of inserting into each child table) and have that "faked" result available after the insert into the parent table finishes. Probably not though, seeing you have to return NULL at the end of that before INSERT trigger...

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4c46cca5286211533124439!