Thread: Insert Rewrite rules
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
itie
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'::timestampwithout 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?
David wrote:
> I am having problems with the rewrite rules though it seems to be skipping over any rule that has a where statement
init 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'::timestampwithout 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?
>
Have you got a statement trigger or a row trigger?
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au
I dropped the trigger again and now it works... but it is really slow
it was maxing out the cpu doing 26r/s.... not that this is a big
problem because the firewall code wont be reling on the rewrite rules
to put the data in the correct spot as it will just insert it into the
correct table and I have modified my data loader to do the same to get
the 600G of data back in in partitioned tables.
And as far as I know it was a row level trigger but I could be wrong....
On Mon, Apr 21, 2008 at 05:32:29PM +1000, Klint Gore wrote:
> David wrote:
> >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?
> >
> Have you got a statement trigger or a row trigger?
>
> klint.
>
> --
> Klint Gore
> Database Manager
> Sheep CRC
> A.G.B.U.
> University of New England
> Armidale NSW 2350
>
> Ph: 02 6773 3789
> Fax: 02 6773 3266
> EMail: kgore4@une.edu.au
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--