Thread: Rule problem: return value of insert

Sorry a little long explanation, and probably an advanced problem.
Any help would be much appreciated all the more!!

I am trying to implement table partitioning (postgres 8.1.2).
Basically I am dealing with Call Detail Records (cdrs) (telecom invoices)
where each one contains possibly millions of Calls.
Since most queries deal with calls from exactly one cdr, partitioning seems very
natural.

I managed to create a trigger that creates a separate, inherited calls_<cdr_id>
table for each cdr. Also it creates a rule that redirects inserts into that table.
This is working very nicely and performance is drastically better.

However it broke our hibernate applications, because hibernate checks the result
value of inserts. Because the rule rewrites the insert queries, the result now
always seem to be 0, which makes hibernate throw an exception.

I just removed that check from hibernate and recompiled hibernate to make it work.
However that's not really a desirable solution (to say the least - I will never
manage to get a hacked hibernate version into production).

Is there any way to make the rewritten query return the correct inserted row count?

In fact I have found some related mails in the archive, but I really do not
understand why '0' is returned.
The documentation (34.5. Rules and Command Status) says if I replace an INSERT
with another INSERT the value returned should be from the new INSERT statement?

Here is my sql code:

CREATE OR REPLACE FUNCTION createCallsSubTable() RETURNS TRIGGER AS $cs_subs$
     DECLARE
    createStatement text;
    ruleStatement text;
     BEGIN
    createStatement := 'create table calls_'
        || NEW.id
        || '( check(cdr_id = '
        || NEW.id
        || '::int8)) inherits(calls);';
    EXECUTE createStatement;

    ruleStatement := 'CREATE OR REPLACE RULE calls_insert_'
        || NEW.id
        || ' AS '
        || 'ON INSERT TO calls '
        || 'WHERE cdr_id = '
        || NEW.id
        || ' DO INSTEAD INSERT INTO calls_'
        || NEW.id
        || ' VALUES (new.*)';
    EXECUTE ruleStatement;

         RETURN NULL;
     END;
$cs_subs$ LANGUAGE plpgsql;

CREATE TRIGGER cs_subs AFTER INSERT ON cdrs
    FOR EACH ROW EXECUTE PROCEDURE createCallsSubTable();

Stephen Friedrich <stephen.friedrich@fortis-it.de> writes:
> In fact I have found some related mails in the archive, but I really do not
> understand why '0' is returned.
> The documentation (34.5. Rules and Command Status) says if I replace an INSERT
> with another INSERT the value returned should be from the new INSERT statement?

Not when you're using a pile of conditional rules like that.  The last
one to fire determines the result, so you'll only see a nonzero count
when inserting into the last subtable.

I think you'd be better off to forget the rule approach and instead
put a trigger on the parent table that stores the values into the
appropriate subtable and then returns NULL.  Unfortunately that won't
fix the rowcount problem either (because suppressed inserts won't be
counted), but it will certainly outperform a large collection of rules.

            regards, tom lane

Thanks for the quick answer - even though it does not solve my problem  :-(

Tom Lane wrote:
 > Not when you're using a pile of conditional rules like that.  The last
 > one to fire determines the result, so you'll only see a nonzero count
 > when inserting into the last subtable.

Hm, it fails even if I have only a single inherited table.
Also only one rule will 'fire'. Or do you mean that the last rule that
gets evaluated will determine the result, even if it's where clause evaluates
to false?

 > I think you'd be better off to forget the rule approach and instead
 > put a trigger on the parent table that stores the values into the
 > appropriate subtable and then returns NULL.  Unfortunately that won't
 > fix the rowcount problem either (because suppressed inserts won't be
 > counted), but it will certainly outperform a large collection of rules.

Thanks I'll try that. Yet I have only some dozen cdrs at any time, so
I doubt it will make a big difference?

Stephen Friedrich <stephen.friedrich@fortis-it.de> writes:
> Or do you mean that the last rule that
> gets evaluated will determine the result, even if it's where clause evaluates
> to false?

Right.  In effect, the last one in alphabetical order will always
determine the result, whether it's the one that does the useful insert
or not.

> Thanks I'll try that. Yet I have only some dozen cdrs at any time, so
> I doubt it will make a big difference?

Perhaps not, but it's worth checking.

            regards, tom lane