Re: [HACKERS] top-level DML under CTEs - Mailing list pgsql-rrreviewers

On Sun, October 3, 2010 15:47, Hitoshi Harada wrote:

[...]

> ...and attached is the latest patch. It contains LIMIT etc. bug of
> INSERT fixes and I confirmed the barrule case correctly in this
> version.
>

(HEAD from git://git.postgresql.org/git/postgresql.git)

The patch applies only with error.
If that error is ignored, the regression 'with' test failes.
If that is also ignored, it runs.

I thought I'd give you the errors anyway:

patch --strip=1 < toplevel-dml-cte.20101003.diff
patching file doc/src/sgml/ref/delete.sgml
patching file doc/src/sgml/ref/insert.sgml
patching file doc/src/sgml/ref/update.sgml
patching file src/backend/nodes/copyfuncs.c
patching file src/backend/nodes/equalfuncs.c
patching file src/backend/parser/analyze.c
Hunk #2 FAILED at 350.
Hunk #3 succeeded at 397 (offset 9 lines).
Hunk #5 succeeded at 630 (offset 9 lines).
Hunk #7 succeeded at 1800 (offset 9 lines).
1 out of 7 hunks FAILED -- saving rejects to file src/backend/parser/analyze.c.rej
patching file src/backend/parser/gram.y
patching file src/backend/parser/parse_utilcmd.c
patching file src/backend/rewrite/rewriteManip.c
patching file src/backend/utils/adt/ruleutils.c
patching file src/include/nodes/parsenodes.h
patching file src/include/rewrite/rewriteManip.h
patching file src/test/regress/expected/with.out
patching file src/test/regress/sql/with.sql
-------------------8<-------------------
***************
*** 343,354 ****
      qry->commandType = CMD_INSERT;
      pstate->p_is_insert = true;

      /*
       * We have three cases to deal with: DEFAULT VALUES (selectStmt == NULL),
-      * VALUES list, or general SELECT input.  We special-case VALUES, both for
-      * efficiency and so we can handle DEFAULT specifications.
       */
-     isGeneralSelect = (selectStmt && selectStmt->valuesLists == NIL);

      /*
       * If a non-nil rangetable/namespace was passed in, and we are doing
--- 350,375 ----
      qry->commandType = CMD_INSERT;
      pstate->p_is_insert = true;

+     /* process the WITH clause independently of all else */
+     if (stmt->withClause)
+     {
+         qry->hasRecursive = stmt->withClause->recursive;
+         qry->cteList = transformWithClause(pstate, stmt->withClause);
+     }
+
      /*
       * We have three cases to deal with: DEFAULT VALUES (selectStmt == NULL),
+      * simple VALUES list, or general SELECT input including complex VALUES.
+      * We special-case VALUES, both for efficiency and so we can handle
+      * DEFAULT specifications. In a complex VALUES case, which means the list
+      * has any of ORDER BY, OFFSET, LIMIT or WITH, we don't accept DEFAULT
+      * in it; The spec may require it but for now we reject it from point of
+      * code base and expected use cases.
       */
+     isGeneralSelect = (selectStmt &&
+         (selectStmt->valuesLists == NIL ||
+          selectStmt->sortClause || selectStmt->limitOffset ||
+          selectStmt->limitCount || selectStmt->withClause));

      /*
       * If a non-nil rangetable/namespace was passed in, and we are doing
-------------------8<-------------------


Continuing after that error:
make OK;
make check:

[...]
     largeobject              ... ok
     with                     ... FAILED
     xml                      ... ok
[...]



regression.diffs:

*** /var/data1/pg_stuff/pg_sandbox/pgsql.dml_cte/src/test/regress/expected/with.out    2010-10-04
13:25:26.000000000 +0200
--- /var/data1/pg_stuff/pg_sandbox/pgsql.dml_cte/src/test/regress/results/with.out    2010-10-04
13:28:20.000000000 +0200
***************
*** 747,783 ****
  )
  INSERT INTO y
  SELECT a+20 FROM t RETURNING *;
!  a
! ----
!  21
!  22
!  23
!  24
!  25
!  26
!  27
!  28
!  29
!  30
! (10 rows)
!
  WITH t AS (
      SELECT a FROM y
  )
  UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a;
!  a
! ----
!  11
!  12
!  13
!  14
!  15
!  16
!  17
!  18
!  19
!  20
! (10 rows)

  WITH RECURSIVE t(a) AS (
      SELECT 11
--- 747,762 ----
  )
  INSERT INTO y
  SELECT a+20 FROM t RETURNING *;
! ERROR:  relation "t" does not exist
! LINE 5: SELECT a+20 FROM t RETURNING *;
!                          ^
  WITH t AS (
      SELECT a FROM y
  )
  UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a;
!  a
! ---
! (0 rows)

  WITH RECURSIVE t(a) AS (
      SELECT 11
***************
*** 785,803 ****
      SELECT a+1 FROM t WHERE a < 50
  )
  DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
!  a
! ----
!  11
!  12
!  13
!  14
!  15
!  16
!  17
!  18
!  19
!  20
! (10 rows)

  SELECT * FROM y;
   a
--- 764,772 ----
      SELECT a+1 FROM t WHERE a < 50
  )
  DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
!  a
! ---
! (0 rows)

  SELECT * FROM y;
   a

======================================================================


hth,


Erik Rijkers