Helo all,

    Please try this script, it will crash the current connection.
    I'm using the 01/18/2001 PostgreSQL v7.1 beta3 snapshot.

-- Script begin -------------------------------------
create table blah(
  var_field varchar(8),
  n1 integer default 23,
  n2 integer,
  arr_str varchar[],
  m money,
  s text
);

create rule blah_update as
 on update to blah
   do
     notify TestEvent;

INSERT INTO blah (var_field, n1, n2, arr_str, m, s) VALUES ('aaa', 1, 2,
NULL, NULL, NULL);
UPDATE blah SET n1=n1+1;  -- Won't crash the connection
UPDATE blah SET n1=2 WHERE var_field='aaa' AND n1=1 AND n2=2 AND arr_str IS
NULL AND m IS NULL; -- Will crash the connection

-- Script end -------------------------------------
    psql will print :

pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#

Any comments ?

I need this kind of code working for a demo for ZeosDBO users...


Best Regards,
Steve Howe



Sun, 21 Jan 2001 03:04:41 -0200
"Steve Howe" <howe@carcass.dhs.org>
This script will crash the connection
"Steve Howe" <howe@carcass.dhs.org> writes:
>     Please try this script, it will crash the current connection.

Crash confirmed.  Thanks for the report --- I'm on it...

			regards, tom lane

Wed, 24 Jan 2001 00:06:27 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: This script will crash the connection 
"Steve Howe" <howe@carcass.dhs.org> writes:
> create rule blah_update as
>  on update to blah
>    do
>      notify TestEvent;

> UPDATE blah SET n1=n1+1;  -- Won't crash the connection
> UPDATE blah SET n1=2 WHERE var_field='aaa' AND n1=1 AND n2=2 AND arr_str IS
> NULL AND m IS NULL; -- Will crash the connection

The problem here is that the query rewriter tries to hang the query's
qualification (WHERE clause) onto the rule's action query, so that
the action query won't be done unless the query finds at least one
row to update.

NOTIFY commands, being utility statements, don't have qualifications.
In 7.0 and before, the qual clause just vanished into the ether, and
so in this example the NOTIFY would execute whether the UPDATE updated
any rows or not.  In 7.1 there is physically noplace to hang the qual
(no jointree) and thus a crash.

Not sure what to do here.  Adding quals to utility statements is right
out, however --- even if we weren't late in beta, the concept doesn't
make any sense to me.  For one reason, utility statements don't have
FROM clauses against which to evaluate the quals.  I am leaning to the
idea that we should forbid NOTIFY in rules altogether.  Jan, what's your
thought?

Steve, your immediate move is to use a trigger rather than a rule to
execute the NOTIFY.  Meanwhile, we have to think about what to do...

			regards, tom lane

Wed, 24 Jan 2001 00:57:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: This script will crash the connection 

Is there a TODO item here, Tom?

> "Steve Howe" <howe@carcass.dhs.org> writes:
> > create rule blah_update as
> >  on update to blah
> >    do
> >      notify TestEvent;
> 
> > UPDATE blah SET n1=n1+1;  -- Won't crash the connection
> > UPDATE blah SET n1=2 WHERE var_field='aaa' AND n1=1 AND n2=2 AND arr_str IS
> > NULL AND m IS NULL; -- Will crash the connection
> 
> The problem here is that the query rewriter tries to hang the query's
> qualification (WHERE clause) onto the rule's action query, so that
> the action query won't be done unless the query finds at least one
> row to update.
> 
> NOTIFY commands, being utility statements, don't have qualifications.
> In 7.0 and before, the qual clause just vanished into the ether, and
> so in this example the NOTIFY would execute whether the UPDATE updated
> any rows or not.  In 7.1 there is physically noplace to hang the qual
> (no jointree) and thus a crash.
> 
> Not sure what to do here.  Adding quals to utility statements is right
> out, however --- even if we weren't late in beta, the concept doesn't
> make any sense to me.  For one reason, utility statements don't have
> FROM clauses against which to evaluate the quals.  I am leaning to the
> idea that we should forbid NOTIFY in rules altogether.  Jan, what's your
> thought?
> 
> Steve, your immediate move is to use a trigger rather than a rule to
> execute the NOTIFY.  Meanwhile, we have to think about what to do...
> 
> 			regards, tom lane
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 24 Jan 2001 07:08:51 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: This script will crash the connection
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is there a TODO item here, Tom?

Hopefully we can just decide what to do and do it.  I'm waiting to hear
Jan's opinion ...

			regards, tom lane

Wed, 24 Jan 2001 11:40:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: This script will crash the connection 
Tom Lane wrote:
> "Steve Howe" <howe@carcass.dhs.org> writes:
> > create rule blah_update as
> >  on update to blah
> >    do
> >      notify TestEvent;
>
> > UPDATE blah SET n1=n1+1;  -- Won't crash the connection
> > UPDATE blah SET n1=2 WHERE var_field='aaa' AND n1=1 AND n2=2 AND arr_str IS
> > NULL AND m IS NULL; -- Will crash the connection
>
> The problem here is that the query rewriter tries to hang the query's
> qualification (WHERE clause) onto the rule's action query, so that
> the action query won't be done unless the query finds at least one
> row to update.
>
> NOTIFY commands, being utility statements, don't have qualifications.
> In 7.0 and before, the qual clause just vanished into the ether, and
> so in this example the NOTIFY would execute whether the UPDATE updated
> any rows or not.  In 7.1 there is physically noplace to hang the qual
> (no jointree) and thus a crash.
>
> Not sure what to do here.  Adding quals to utility statements is right
> out, however --- even if we weren't late in beta, the concept doesn't
> make any sense to me.  For one reason, utility statements don't have
> FROM clauses against which to evaluate the quals.  I am leaning to the
> idea that we should forbid NOTIFY in rules altogether.  Jan, what's your
> thought?
>
> Steve, your immediate move is to use a trigger rather than a rule to
> execute the NOTIFY.  Meanwhile, we have to think about what to do...

    Would be something for a STATEMENT trigger. We don't have 'em
    yet and I'm not sure  what  kind  of  information  they  will
    receive  if we finally implement them. But the number of rows
    affected by the statement is a good candidate.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Wed, 24 Jan 2001 17:35:54 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: This script will crash the connection
Jan Wieck <janwieck@Yahoo.com> writes:
> Tom Lane wrote:
>> The problem here is that the query rewriter tries to hang the query's
>> qualification (WHERE clause) onto the rule's action query, so that
>> the action query won't be done unless the query finds at least one
>> row to update.
>> NOTIFY commands, being utility statements, don't have qualifications.
>> In 7.0 and before, the qual clause just vanished into the ether, and
>> so in this example the NOTIFY would execute whether the UPDATE updated
>> any rows or not.  In 7.1 there is physically noplace to hang the qual
>> (no jointree) and thus a crash.

>     Would be something for a STATEMENT trigger. We don't have 'em
>     yet and I'm not sure  what  kind  of  information  they  will
>     receive  if we finally implement them. But the number of rows
>     affected by the statement is a good candidate.

That's no help for a 7.1 solution however.  We can't start inventing
a new feature at this stage.

What I am inclined to do is have the rewriter reject conditional rules
that contain NOTIFY.  That seems like the minimal restriction that will
prevent a crash or incorrect behavior.  Comments?

			regards, tom lane

Fri, 26 Jan 2001 13:30:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: This script will crash the connection 
> >     Would be something for a STATEMENT trigger. We don't have 'em
> >     yet and I'm not sure  what  kind  of  information  they  will
> >     receive  if we finally implement them. But the number of rows
> >     affected by the statement is a good candidate.
> 
> That's no help for a 7.1 solution however.  We can't start inventing
> a new feature at this stage.
> 
> What I am inclined to do is have the rewriter reject conditional rules
> that contain NOTIFY.  That seems like the minimal restriction that will
> prevent a crash or incorrect behavior.  Comments?

OK, added to TODO:

	* Allow NOTIFY in rules

and removed from open items list.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 26 Jan 2001 15:35:22 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: This script will crash the connection
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, added to TODO:
> 	* Allow NOTIFY in rules

Uh, what does that have to do with the problem?  It's certainly not
an accurate rendering of either the current or proposed status ...

			regards, tom lane

Fri, 26 Jan 2001 16:07:12 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: This script will crash the connection 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > OK, added to TODO:
> > 	* Allow NOTIFY in rules
> 
> Uh, what does that have to do with the problem?  It's certainly not
> an accurate rendering of either the current or proposed status ...

Oops, can you give me a line.  What was the issue?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 26 Jan 2001 23:56:14 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: This script will crash the connection
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, added to TODO:
> * Allow NOTIFY in rules
>> 
>> Uh, what does that have to do with the problem?  It's certainly not
>> an accurate rendering of either the current or proposed status ...

> Oops, can you give me a line.  What was the issue?

"Allow NOTIFY in conditional rules" would be an approximation.  It's
not the whole story though, because presently we also have to fail
if the rule is applied to a query with conditions, even if the rule
itself is unconditional.  As of my last commit:

regression=# create rule r1 as on update to int4_tbl do notify foo;
CREATE
regression=# update int4_tbl set f1 = f1;
UPDATE 5
regression=# update int4_tbl set f1 = f1 where f1 < 0;
ERROR:  Conditional NOTIFY is not implemented

which is pretty ugly but at least it doesn't pretend to do something
it can't, which was the 7.0 behavior.  (In 7.0 you'd have gotten a
NOTIFY whether the update updated any rows or not.)

			regards, tom lane

Sat, 27 Jan 2001 00:31:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: This script will crash the connection 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > OK, added to TODO:
> > * Allow NOTIFY in rules
> >> 
> >> Uh, what does that have to do with the problem?  It's certainly not
> >> an accurate rendering of either the current or proposed status ...
> 
> > Oops, can you give me a line.  What was the issue?
> 
> "Allow NOTIFY in conditional rules" would be an approximation.  It's
> not the whole story though, because presently we also have to fail
> if the rule is applied to a query with conditions, even if the rule
> itself is unconditional.  As of my last commit:
> 
> regression=# create rule r1 as on update to int4_tbl do notify foo;
> CREATE
> regression=# update int4_tbl set f1 = f1;
> UPDATE 5
> regression=# update int4_tbl set f1 = f1 where f1 < 0;
> ERROR:  Conditional NOTIFY is not implemented
> 
> which is pretty ugly but at least it doesn't pretend to do something
> it can't, which was the 7.0 behavior.  (In 7.0 you'd have gotten a
> NOTIFY whether the update updated any rows or not.)

Added to TODO:

	* Allow NOTIFY in rules involving conditionals  

This covers both cases of conditionals in the rule or the query.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 27 Jan 2001 00:40:23 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: This script will crash the connection