Обсуждение: Strange order of execution with rule
Hello, I have something like this: CREATE or replace rule update_rule as on update to aview do instead ( select func_display(new, old); select rubriek('reset', 0, '', 0); ); (Postgres 8.0.3). I tried all kinds of variations (one select with two functions, and two different update rules with a select each) but in _all_ cases the second function is executed before the first, or possibly at the same time (but I didn't think the backend is threaded). Is this known behaviour, and what can I do to change it? Thanks in advance for any suggestions, Han Holl P.S. I don't know if it's relevant but what func_display and rubriek do has nothing to do with the database. Let's say they do some kind of logging.
han.holl@informationslogik.nl writes: > I have something like this: > CREATE or replace rule update_rule as on update > to aview > do instead ( > select func_display(new, old); > select rubriek('reset', 0, '', 0); > ); > I tried all kinds of variations (one select with two functions, and two > different update rules with a select each) but in _all_ cases the second > function is executed before the first, or possibly at the same time (but I > didn't think the backend is threaded). Give us a test case to demonstrate this assertion. It works as expected AFAICT. regression=# create function noti(text) returns int as $$ regression$# begin regression$# raise notice '%', $1; regression$# return 1; regression$# end$$ language plpgsql; CREATE FUNCTION regression=# CREATE or replace rule update_rule as on update to zview regression-# do instead ( regression(# select noti('foo'); regression(# select noti('bar'); regression(# ); CREATE RULE regression=# update zview set q1 = q2; NOTICE: foo NOTICE: bar noti ------ 1 (1 row) regards, tom lane
On Thursday 20 October 2005 23:48, Tom Lane wrote: > Give us a test case to demonstrate this assertion. It works as expected > AFAICT. > Indeed it does. I forgot to mention that the first function to be called was language plruby. Maybe that has anything to do with it. I'll try to come with something more concrete. Thanks, Han Holl
On Thursday 20 October 2005 23:48, Tom Lane wrote: > Give us a test case to demonstrate this assertion. It works as expected > AFAICT. > Ok, it has to do with C functions: #include <postgres.h> #include <fmgr.h> #include <sys/time.h> PG_FUNCTION_INFO_V1(plg_cfie); Datum plg_cfie(PG_FUNCTION_ARGS) { struct timeval before; gettimeofday(&before, 0); elog(NOTICE, "Called cfie %ld.%ld\n",before.tv_sec, before.tv_usec); PG_RETURN_NULL(); } /* end-of-plg_cfie */ gcc -fpic -O2 -Wall -c -I /usr/include/pgsql/server/ plg_cfie.c gcc -shared -o /tmp/plg_cfie.so plg_cfie.o File test: CREATE or replace FUNCTION func_display(udps, udps) RETURNS void AS ' a = Time.now warn sprintf("Called func_display %d.%d", a.tv_sec, a.tv_usec) ' LANGUAGE 'plruby'; create or replace function plg_cfie() returns void as '/tmp/plg_cfie.so' language c immutable; CREATE or replace rule update_rule as on update to udps do instead ( select func_display(new, old); select plg_cfie(); ); update udps set ziekenhuis = 'olvg' where rapport = 'T105-00002'; -- end of file test su postgres -c 'psql palga' <test CREATE FUNCTION CREATE FUNCTION CREATE RULE NOTICE: Called cfie 1129893542.714536 NOTICE: Called func_display 1129893542.732444 plg_cfie ---------- (1 row) It's not a plruby issue, I tested with plpgsql (your noti) as well. Cheers, Han Holl
han.holl@informationslogik.nl writes: > On Thursday 20 October 2005 23:48, Tom Lane wrote: >> Give us a test case to demonstrate this assertion. It works as expected >> AFAICT. > Ok, it has to do with C functions: > create or replace function plg_cfie() returns void as '/tmp/plg_cfie.so' > language c immutable; You should not declare a function "immutable" if it has side effects (such as emitting a message). That entitles the planner to pre-evaluate the function at plan time, which is evidently what's happening here. regards, tom lane