Thread: This script will crash the connection
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 ason 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
"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
"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
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, Pennsylvania19026
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
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
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
> > 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, Pennsylvania19026
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
> 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, Pennsylvania19026
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
> 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, Pennsylvania19026