Insert Rewrite rules

From: David <david(at)vanlaatum(dot)id(dot)au>
To: pgsql-general(at)postgresql(dot)org
Subject: Insert Rewrite rules
Date: 2008-04-21 06:51:41
Message-ID: 20080421065141.GA32018@daboyz.dyndns.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am currently attempting to migrate one of our customers databases to
partitioned tables. This database is used to store firewall logs and
is currently in the range of 600GB (thats 90 days worth).

I am having problems with the rewrite rules though it seems to be skipping over any rule that has a where statement in it ie

CREATE OR REPLACE RULE firewall_test AS ON INSERT TO firewall DO INSTEAD INSERT INTO firewall_y2008m04d21 VALUES(NEW."time");
INSERT INTO firewall (time) VALUES('2008-04-21 12:00:00');
INSERT 1029459 1

works but

CREATE OR REPLACE RULE firewall_test AS ON INSERT TO firewall WHERE TRUE DO INSTEAD INSERT INTO firewall_y2008m04d21 VALUES(NEW."time");
INSERT INTO firewall (time) VALUES('2008-04-21 12:00:00');
ERROR: No inserting to firewall please

doesn't. I have placed a trigger on the table to prevent anything from inserting into the top level table hence the error.

an example of the full rule we are trying to use that doesn't work is

firewall_y2008m04d21_insert AS
ON INSERT TO firewall
WHERE new."time" >= '2008-04-21 00:00:00'::timestamp without time zone AND new."time" < '2008-04-22 00:00:00'::timestamp without time zone DO INSTEAD INSERT INTO firewall_y2008m04d21 ("time", firewall, pri, sent, rcvd, lsent, lrcvd, duration, src, dst, arg, msg, ref, dstname, srcname, agent, server, srcclass, dstclass, rule, username, proto, op, result, vpn, type, cache, direction, content, fwdfor, coninfo, tcpflags, method, action, policy, service, engine, state, fwid, block, authprofile, summarised, realm, clientmac, account, count, interface)
VALUES (new."time", new.firewall, new.pri, new.sent, new.rcvd, new.lsent, new.lrcvd, new.duration, new.src, new.dst, new.arg, new.msg, new.ref, new.dstname, new.srcname, new.agent, new.server, new.srcclass, new.dstclass, new.rule, new.username, new.proto, new.op, new.result, new.vpn, new.type, new.cache, new.direction, new.content, new.fwdfor, new.coninfo, new.tcpflags, new.method, new.action, new.policy, new.service, new.engine, new.state, new.fwid, new.block, new.authprofile, new.summarised, new.realm, new.clientmac, new.account, new.count, new.interface)

There is one of these for each day with only the times changing.

Am I missing something or is this just broken?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kris Jurka 2008-04-21 07:11:25 Re: JDBC addBatch more efficient?
Previous Message laser 2008-04-21 06:04:04 PostgreSQL over HTTP/REST interface