Thread: BUG #6051: wCTE query fail with wrong error text on a table with rules

The following bug has been logged online:

Bug reference:      6051
Logged by:          Jehan-Guillaume (ioguix) de Rorthais
Email address:      jgdr@dalibo.com
PostgreSQL version: 9.1beta1
Operating system:   Linux x86_64
Description:        wCTE query fail with wrong error text on a table with
rules
Details:

While testing wCTE, I tried to empty a parent table, feeding its child using
rules with this kind of query:

  WITH t1 AS (
    DELETE FROM ONLY test RETURNING *
  )
  INSERT INTO test SELECT * FROM t1;

It works perfectly on a table without rules, but fail with what seems like a
non related error message if there is a rule on INSERT on this table:

  test=# WITH t1 AS (
    DELETE FROM ONLY test RETURNING *
  )
  INSERT INTO test SELECT * FROM t1;
  ERROR:  could not find CTE "t1"

I was expecting this query either to work or raise a comprehensive error
message.

Here is a simple script to reproduce this behaviour:

======================>
$ createdb test

$ psql test
psql (9.1beta1)
Type "help" for help.

test=# SELECT version();
                                                  version

----------------------------------------------------------------------------
--------------------------------
 PostgreSQL 9.1beta1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.6.0 20110513 (prerelease), 64-bit
(1 row)

test=# CREATE table test AS select i from generate_series(1,3) as t(i);
SELECT 3
test=# SELECT * FROM test;
 i
---
 1
 2
 3
(3 rows)

test=# WITH t1 AS (
test(# DELETE FROM ONLY test RETURNING *
test(# )
test-# INSERT INTO test SELECT * FROM t1;
INSERT 0 3
test=# SELECT * FROM test;
 i
---
 1
 2
 3
(3 rows)

test=# CREATE TABLE test2 () inherits (test);
CREATE TABLE
test=# CREATE RULE test_ins AS ON INSERT TO test DO INSTEAD INSERT INTO
test2 VALUES (NEW.i);
CREATE RULE
test=# WITH t1 AS (
DELETE FROM ONLY test RETURNING *
)
INSERT INTO test SELECT * FROM t1;
ERROR:  could not find CTE "t1"
<======================

On Fri, Jun 3, 2011 at 10:42 AM, Jehan-Guillaume (ioguix) de Rorthais
<jgdr@dalibo.com> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A06051
> Logged by: =A0 =A0 =A0 =A0 =A0Jehan-Guillaume (ioguix) de Rorthais
> Email address: =A0 =A0 =A0jgdr@dalibo.com
> PostgreSQL version: 9.1beta1
> Operating system: =A0 Linux x86_64
> Description: =A0 =A0 =A0 =A0wCTE query fail with wrong error text on a ta=
ble with
> rules
> Details:
>
> While testing wCTE, I tried to empty a parent table, feeding its child us=
ing
> rules with this kind of query:
>
> =A0WITH t1 AS (
> =A0 =A0DELETE FROM ONLY test RETURNING *
> =A0)
> =A0INSERT INTO test SELECT * FROM t1;
>
> It works perfectly on a table without rules, but fail with what seems lik=
e a
> non related error message if there is a rule on INSERT on this table:
>
> =A0test=3D# WITH t1 AS (
> =A0 =A0DELETE FROM ONLY test RETURNING *
> =A0)
> =A0INSERT INTO test SELECT * FROM t1;
> =A0ERROR: =A0could not find CTE "t1"
>
> I was expecting this query either to work or raise a comprehensive error
> message.
>
> Here is a simple script to reproduce this behaviour:
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>
> $ createdb test
>
> $ psql test
> psql (9.1beta1)
> Type "help" for help.
>
> test=3D# SELECT version();
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0version
>
> -------------------------------------------------------------------------=
---
> --------------------------------
> =A0PostgreSQL 9.1beta1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
> 4.6.0 20110513 (prerelease), 64-bit
> (1 row)
>
> test=3D# CREATE table test AS select i from generate_series(1,3) as t(i);
> SELECT 3
> test=3D# SELECT * FROM test;
> =A0i
> ---
> =A01
> =A02
> =A03
> (3 rows)
>
> test=3D# WITH t1 AS (
> test(# DELETE FROM ONLY test RETURNING *
> test(# )
> test-# INSERT INTO test SELECT * FROM t1;
> INSERT 0 3
> test=3D# SELECT * FROM test;
> =A0i
> ---
> =A01
> =A02
> =A03
> (3 rows)
>
> test=3D# CREATE TABLE test2 () inherits (test);
> CREATE TABLE
> test=3D# CREATE RULE test_ins AS ON INSERT TO test DO INSTEAD INSERT INTO
> test2 VALUES (NEW.i);
> CREATE RULE
> test=3D# WITH t1 AS (
> DELETE FROM ONLY test RETURNING *
> )
> INSERT INTO test SELECT * FROM t1;
> ERROR: =A0could not find CTE "t1"
> <=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

IIRC the fact that rules don't play nice with wCTE was brought up
several times during the implementation discussions.  I'm not saying
the error message is great, but you can pretty much add this to the
giant pile of reasons not to use rules at all (particularly in 9.1
with the view triggers).

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> On Fri, Jun 3, 2011 at 10:42 AM, Jehan-Guillaume (ioguix) de Rorthais
> <jgdr@dalibo.com> wrote:
>> test=# CREATE RULE test_ins AS ON INSERT TO test DO INSTEAD INSERT INTO
>> test2 VALUES (NEW.i);
>> CREATE RULE
>> test=# WITH t1 AS (
>> DELETE FROM ONLY test RETURNING *
>> )
>> INSERT INTO test SELECT * FROM t1;
>> ERROR:  could not find CTE "t1"

> IIRC the fact that rules don't play nice with wCTE was brought up
> several times during the implementation discussions.  I'm not saying
> the error message is great, but you can pretty much add this to the
> giant pile of reasons not to use rules at all (particularly in 9.1
> with the view triggers).

There are definitely cases that don't work, but I had thought we at
least threw an intelligible "not implemented" error for all of them.
This one seems to be an oversight: specifically, rewriteRuleAction()
isn't considering the possibility that the rewritten rule action will
need to make use of CTEs from the original query.

We could paste a copy of the original's cteList into the rule action,
but there are still issues:

* If there's more than one rule action, we could end up executing
multiple copies of the same CTE query; which breaks the expectation
of single evaluation for a CTE.

* If there are CTEs attached to the rule action, as well as to the
original query, and there is a conflict of CTE names between them,
we can't handle that AFAICS.  (The planner expects to look up entries
in a cteList by name...)

* Maybe some other things that aren't obvious yet.

I don't particularly mind throwing a not-implemented error for the first
case (ie, just say multiple rule actions don't mix with CTE queries);
but the second case seems seriously annoying, since there's no way for
someone to write a CTE-containing rule action without risking a
conflict.  Ideas anybody?

            regards, tom lane

On Sat, Jun 04, 2011 at 05:24:22PM -0400, Tom Lane wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
> > On Fri, Jun 3, 2011 at 10:42 AM, Jehan-Guillaume (ioguix) de Rorthais
> > <jgdr@dalibo.com> wrote:
> >> test=# CREATE RULE test_ins AS ON INSERT TO test DO INSTEAD INSERT INTO
> >> test2 VALUES (NEW.i);
> >> CREATE RULE
> >> test=# WITH t1 AS (
> >> DELETE FROM ONLY test RETURNING *
> >> )
> >> INSERT INTO test SELECT * FROM t1;
> >> ERROR:  could not find CTE "t1"
>
> > IIRC the fact that rules don't play nice with wCTE was brought up
> > several times during the implementation discussions.  I'm not saying
> > the error message is great, but you can pretty much add this to the
> > giant pile of reasons not to use rules at all (particularly in 9.1
> > with the view triggers).
>
> There are definitely cases that don't work, but I had thought we at
> least threw an intelligible "not implemented" error for all of them.
> This one seems to be an oversight: specifically, rewriteRuleAction()
> isn't considering the possibility that the rewritten rule action will
> need to make use of CTEs from the original query.
>
> We could paste a copy of the original's cteList into the rule action,
> but there are still issues:
>
> * If there's more than one rule action, we could end up executing
> multiple copies of the same CTE query; which breaks the expectation
> of single evaluation for a CTE.
>
> * If there are CTEs attached to the rule action, as well as to the
> original query, and there is a conflict of CTE names between them,
> we can't handle that AFAICS.  (The planner expects to look up entries
> in a cteList by name...)
>
> * Maybe some other things that aren't obvious yet.
>
> I don't particularly mind throwing a not-implemented error for the first
> case (ie, just say multiple rule actions don't mix with CTE queries);

Great :)

> but the second case seems seriously annoying, since there's no way
> for someone to write a CTE-containing rule action without risking a
> conflict.  Ideas anybody?

Is there some way to throw "not implemented" there, too?  It seems to
me that making accommodations for the user-modifiable part of our
rewrite rules isn't the right direction to go, as we're well on our
way to phasing the user-modifiable part out.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david@fetter.org> writes:
> On Sat, Jun 04, 2011 at 05:24:22PM -0400, Tom Lane wrote:
>> We could paste a copy of the original's cteList into the rule action,
>> but there are still issues:
>>
>> * If there's more than one rule action, we could end up executing
>> multiple copies of the same CTE query; which breaks the expectation
>> of single evaluation for a CTE.
>>
>> * If there are CTEs attached to the rule action, as well as to the
>> original query, and there is a conflict of CTE names between them,
>> we can't handle that AFAICS.  (The planner expects to look up entries
>> in a cteList by name...)
>>
>> * Maybe some other things that aren't obvious yet.
>>
>> I don't particularly mind throwing a not-implemented error for the first
>> case (ie, just say multiple rule actions don't mix with CTE queries);

> Great :)

>> but the second case seems seriously annoying, since there's no way
>> for someone to write a CTE-containing rule action without risking a
>> conflict.  Ideas anybody?

> Is there some way to throw "not implemented" there, too?  It seems to
> me that making accommodations for the user-modifiable part of our
> rewrite rules isn't the right direction to go, as we're well on our
> way to phasing the user-modifiable part out.

Yes, we certainly can throw an error for that, it's just going to be a
pain from a usability standpoint.  But I agree that the number of people
affected might be small, so it's not worth doing a significant rewrite
for right now.

I've committed a patch along these lines.

            regards, tom lane