After my last email, I added the rest of the rule actions. So the
relevant part of the schema now looks like this:
CREATE RULE update_msg_stats
AS ON INSERT TO messages DO (
UPDATE users SET num_posts = num_posts + 1
WHERE users.id = new.poster;
UPDATE threads SET num_posts = num_posts + 1
WHERE threads.id = new.thread;
UPDATE forums SET num_posts = forums.num_posts + 1 FROM threads t
WHERE t.id = new.thread AND forums.id = t.forum;
UPDATE threads SET last_post = timestamp('now')
WHERE threads.id = new.thread;
UPDATE forums SET last_post = timestamp('now') FROM threads t
WHERE t.id = new.thread AND forums.id = t.forum;
);
(I'll just provide the definition of the rule -- if you need all the
tables it effects, just tell me.)
I reran the 3000-inserts test case with the more complex rule in place
- after 1 hour, and canceled it. It had inserted about 2000 of the
3000 rows (judging by the value of messages_id_seq). As before,
inserting these rows took < 10 seconds without the rules.
On Wed, Oct 18, 2000 at 10:50:43PM -0400, Tom Lane wrote:
> > 1) Are rules really this slow?
>
> Seems like they shouldn't be. Could you recompile with PROFILE=-pg
> and get a gprof profile on the 3000-inserts test case?
I tried rebuilding Postgres, but 'gmake' fails with:
gmake[3]: Leaving directory `/usr/home/nconway/pgsql-20000924/src/backend/utils'
gcc -I../../src/include -O2 -m486 -pipe -Wall -Wmissing-prototypes
-Wmissing-declarations -pg -o postgres access/SUBSYS.o bootstrap/SUBSYS.o
catalog/SUBSYS.o parser/SUBSYS.o commands/SUBSYS.o executor/SUBSYS.o lib/SUBSYS.o
libpq/SUBSYS.o main/SUBSYS.o nodes/SUBSYS.o optimizer/SUBSYS.o port/SUBSYS.o
postmaster/SUBSYS.o regex/SUBSYS.o rewrite/SUBSYS.o storage/SUBSYS.o tcop/SUBSYS.o
utils/SUBSYS.o -lz -lcrypt -lcompat -lln -lm -lutil -lreadline -ltermcap -lncurses
-export-dynamic -pg
/usr/libexec/elf/ld: cannot find -lc_p
gmake[2]: *** [postgres] Error 1
gmake[2]: Leaving directory `/usr/home/nconway/pgsql-20000924/src/backend'
gmake[1]: *** [all] Error 2
gmake[1]: Leaving directory `/usr/home/nconway/pgsql-20000924/src'
gmake: *** [all] Error 2
After it has been compiling for a while. I'm running FreeBSD 4.1-STABLE on
x86 with gcc 2.95.2 . I tried compiling 3 nightly snapshots (Oct 18, Oct 19,
and Sept. 24), and none of them worked. They all compile properly without
profiling.
--
Neil Conway <[EMAIL PROTECTED]>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed
Four stages of acceptance:
i) this is worthless nonsense;
ii) this is an interesting, but perverse, point of view;
iii) this is true, but quite unimportant;
iv) I always said so.
-- J. B. S. Haldane in Journal of Genetics 58:464 (1963).
PGP signature
