Rule problem: return value of insert

Поиск
Список
Период
Сортировка
От Stephen Friedrich
Тема Rule problem: return value of insert
Дата
Msg-id 43CD45FA.3010809@fortis-it.de
обсуждение исходный текст
Ответы Re: Rule problem: return value of insert  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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();



В списке pgsql-general по дате отправления:

Предыдущее
От: Jaime Casanova
Дата:
Сообщение: Re: PostgreSQL Top 10 Wishlist
Следующее
От: Neil Brandt
Дата:
Сообщение: mac os x compile failure