From: | Stephen Friedrich <stephen(dot)friedrich(at)fortis-it(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Rule problem: return value of insert |
Date: | 2006-01-17 19:31:06 |
Message-ID: | 43CD45FA.3010809@fortis-it.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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();
From | Date | Subject | |
---|---|---|---|
Next Message | Neil Brandt | 2006-01-17 19:36:36 | mac os x compile failure |
Previous Message | Jaime Casanova | 2006-01-17 19:27:39 | Re: PostgreSQL Top 10 Wishlist |