pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRules and sequences
4+ messages / 3 participants
[nested] [flat]
* Rules and sequences
@ 2010-05-26 18:16 Ben Morrow <ben@morrow.me.uk>
2010-05-27 00:04 ` Re: Rules and sequences Tom Lane <tgl@sss.pgh.pa.us>
2010-05-27 16:28 ` Re: Rules and sequences Ben Morrow <ben@morrow.me.uk>
0 siblings, 2 replies; 4+ messages in thread
From: Ben Morrow @ 2010-05-26 18:16 UTC (permalink / raw)
To: pgsql-sql
I am trying to implement a fairly standard 'audit table' setup, but
using rules instead of triggers (since it should be more efficient).
However, I'm running into problems when one of the audited tables has a
'serial' column that is allowed to default:
create table foo (id serial, bar text);
create table audit (ix bigserial, rec text);
create rule audit_insert as on insert to foo do also
insert into audit (rec) values ((new.*)::text);
insert into foo (bar) values ('baz');
select * from foo;
id | bar
----+-----
1 | baz
(1 row)
select * from audit;
ix | rec
----+---------
1 | (2,baz)
(1 row)
I can see why this is happening (the rule is essentially a macro, so the
NEW expression gets expanded twice, including the nextval call, so the
sequence is incremented twice), but is there any way to prevent it? Some
way of 'materialising' the NEW row so it is just plain values rather
than a list of expressions?
Ben
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rules and sequences
2010-05-26 18:16 Rules and sequences Ben Morrow <ben@morrow.me.uk>
@ 2010-05-27 00:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-27 06:20 ` Re: Rules and sequences A. Kretschmer <andreas.kretschmer@schollglas.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2010-05-27 00:04 UTC (permalink / raw)
To: Ben Morrow <ben@morrow.me.uk>; +Cc: pgsql-sql
Ben Morrow <ben@morrow.me.uk> writes:
> I am trying to implement a fairly standard 'audit table' setup, but
> using rules instead of triggers (since it should be more efficient).
Rules are sufficiently tricky that I would never, ever rely on them for
auditing. Use a simple AFTER trigger instead.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rules and sequences
2010-05-26 18:16 Rules and sequences Ben Morrow <ben@morrow.me.uk>
2010-05-27 00:04 ` Re: Rules and sequences Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-05-27 06:20 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 4+ messages in thread
From: A. Kretschmer @ 2010-05-27 06:20 UTC (permalink / raw)
To: pgsql-sql
In response to Tom Lane :
> Ben Morrow <ben@morrow.me.uk> writes:
> > I am trying to implement a fairly standard 'audit table' setup, but
> > using rules instead of triggers (since it should be more efficient).
>
> Rules are sufficiently tricky that I would never, ever rely on them for
> auditing. Use a simple AFTER trigger instead.
There are a ready solution:
http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rules and sequences
2010-05-26 18:16 Rules and sequences Ben Morrow <ben@morrow.me.uk>
@ 2010-05-27 16:28 ` Ben Morrow <ben@morrow.me.uk>
1 sibling, 0 replies; 4+ messages in thread
From: Ben Morrow @ 2010-05-27 16:28 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; pgsql-sql
Quoth tgl@sss.pgh.pa.us (Tom Lane):
> Ben Morrow <ben@morrow.me.uk> writes:
> > I am trying to implement a fairly standard 'audit table' setup, but
> > using rules instead of triggers (since it should be more efficient).
>
> Rules are sufficiently tricky that I would never, ever rely on them for
> auditing. Use a simple AFTER trigger instead.
OK, thanks.
Ben
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-05-27 16:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-26 18:16 Rules and sequences Ben Morrow <ben@morrow.me.uk>
2010-05-27 00:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-27 06:20 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2010-05-27 16:28 ` Ben Morrow <ben@morrow.me.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox