Обсуждение: 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