On 21 Jul 2010, at 11:35, [email protected] 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!
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
