Re: [GENERAL] [BUGS] INSTEAD rule bug?

#1

Dmitry Tkach <dmitry (AT) openratings (DOT) com> writes:

Quote:

I know... That was a typo in my sql :-)
But for this example it doesn't matter - that view/table is only needed
to illustrate the rules behaviour on insert.

Oh, I see what you're on about. Sorry, a "DO INSTEAD NOTHING" only
suppresses the original command, it does not suppress other rules.
I think what you want is to make the insert_test rule conditional
on x being not null.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#2

Tom Lane wrote:

Quote:

Oh, I see what you're on about. Sorry, a "DO INSTEAD NOTHING" only
suppresses the original command, it does not suppress other rules.
I think what you want is to make the insert_test rule conditional
on x being not null.

Yeah... that's what I was afraid of :-(

The problem is that in the 'real life' situation the condition is a lot
more complicated than this simple is null test... I hate having to
duplicate it, and I hate even more having to evaluate it twice on every
insert :-(

Dima

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#3

Tom Lane wrote:

Quote:

Dmitry Tkach <dmitry (AT) openratings (DOT) com> writes:

The problem is that in the 'real life' situation the condition is a lot
more complicated than this simple is null test... I hate having to
duplicate it, and I hate even more having to evaluate it twice on every
insert :-(

Why evaluate it twice? The DO INSTEAD NOTHING rule should be
unconditional.

Right. But the problem is I don't want to discard the invalid entries

completely...
So, it would have to be *three* rules, not just two - like:

create rule skip_null as on insert to test_view where x is null do instead
insert into invalid_entries ('NULL DATA', new.*);
create rule insert_test as on insert to test_view where is is not null
do instead
insert into test values (new.*);
create rule dummy_insert as on insert to test_view do instead nothing;

.... so x is null ends up being evaluated twice...

Dima

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#4

Dmitry Tkach <dmitry (AT) openratings (DOT) com> writes:

Quote:

Ok... What's wrong with this one then

testdb=# insert into test values (null, null);
ERROR: ExecInsert: Fail to add null value in not null attribute x

Try inserting into test_view ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#5

Dmitry Tkach <dmitry (AT) openratings (DOT) com> writes:

Quote:

But what the hell is my problem then??? I swear, I do insert into the
view there :-)
It's a really huge view, looking at a whole bunch of different tables...
I'd hate having to post the whole thing...

All I can guess is a bug (or pilot error) that's triggered by the more
complex view. I think you'll just have to try to whittle down the
failure to something you can post.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#6

Aha!

I got it.
This generates the 'cannot insert null...' error:

create table test (x int not null, y int not null);
create table test_reject (x int, y int, reason text);

create view test_view as select * from test;

create rule reject_x as on insert to test_view where new.x is null do
instead insert into test_reject values (new.*, 'NULL x');

create rule reject_y as on insert to test_view where new.y is null do
instead insert into test_reject values (new.*, 'NULL y');

create rule insert_test as on insert to test_view where new.x is not
null and new.y is not null do instead
(
insert into test
select new.* union
select new.*;
);

create rule insert_dummy as on insert to test_view do instead nothing;

-- insert into test_reject values (new.*,
-- case when new.x is null then 'NULL x' else 'NULL y' end);

insert into test_view values (null, null);

It looks like the UNION in the 'not null' rule is the problem.
If I change it to just insert ... select (without the union), or to two
inserts, then it works.
But union always fails, even if I add a 'where false' to the end, so
that it only returns one row...

Dima

Tom Lane wrote:

Quote:

Dmitry Tkach <dmitry (AT) openratings (DOT) com> writes:

But what the hell is my problem then??? I swear, I do insert into the
view there :-)
It's a really huge view, looking at a whole bunch of different tables...
I'd hate having to post the whole thing...

All I can guess is a bug (or pilot error) that's triggered by the more
complex view. I think you'll just have to try to whittle down the
failure to something you can post.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#7

Dmitry Tkach <dmitry (AT) openratings (DOT) com> writes:

Quote:

create rule insert_test as on insert to test_view where new.x is not
null and new.y is not null do instead
(
insert into test
select new.* union
select new.*;
);

Mmm. In CVS tip that throws

ERROR: UNION/INTERSECT/EXCEPT member statement may not refer to other relations of same query level

which was a check added as a result of this discussion thread:
http://archives.postgresql.org/pgsql...2/msg00693.php

I am sure you are running into some misbehavior associated with the
fact that the rule transformation generates a bogusly-structured SQL
query, and 7.2 isn't noticing.

I'd like to support this case someday, but it's not clear how...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#8

Tom Lane wrote:

Quote:

Dmitry Tkach <dmitry (AT) openratings (DOT) com> writes:

create rule insert_test as on insert to test_view where new.x is not
null and new.y is not null do instead
(
insert into test
select new.* union
select new.*;
);

Mmm. In CVS tip that throws

ERROR: UNION/INTERSECT/EXCEPT member statement may not refer to other relations of same query level

Actually, I just used that new.* as an example (if I understand this

error message correctly, that's what it refers to, right?)
Something like
insert into test
select null,null union select 1,2 where false

has the same problem... and it doesn't refer to any relations.

Quote:

which was a check added as a result of this discussion thread:
http://archives.postgresql.org/pgsql...2/msg00693.php

I'll take a look at that thread, thanks!

Quote:

I am sure you are running into some misbehavior associated with the
fact that the rule transformation generates a bogusly-structured SQL
query, and 7.2 isn't noticing.

Not just 7.2... I was testing this in 7.3 - it has the same problem

Dima

Quote:

I'd like to support this case someday, but it's not clear how...

I don't know if it helps, but somehow if I do

insert into test select * from (select null,null union select 1,2 where
false) as dummy

.... that works fine.

Thanks!

Dima

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#9

Dmitry Tkach <dmitry (AT) openratings (DOT) com> writes:

Quote:

Something like
insert into test
select null,null union select 1,2 where false
has the same problem... and it doesn't refer to any relations.

But that's parsed as

insert into test
(select null,null) union (select 1,2 where false)

so I'd expect it to bomb if test has NOT NULL constraints.

Quote:

Not just 7.2... I was testing this in 7.3 - it has the same problem

Yeah, the change is post-7.3.

Quote:

insert into test select * from (select null,null union select 1,2 where
false) as dummy
... that works fine.

I get
ERROR: ExecInsert: Fail to add null value in not null attribute x
which is what I'd expect.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#10

Dmitry Tkach <dmitry (AT) openratings (DOT) com> writes:

Quote:

Sure, but it is inside the rule that has 'where x is not null and y is
not null' on it as a qualifier, so
with my test example it should just never get executed in the first place.

You're confusing rules with triggers. The INSERT *will* get executed;
the rule's qualifier gets moved to the WHERE of the INSERT...SELECT,
and the way you get no effect is for the qual to fail on every row the
SELECT generates.

One way to think about the problem (though I'm not sure this is right in
detail) is that there's no place to hang a top-level WHERE on a UNION.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org