Re: [HACKERS] top-level DML under CTEs

From: "Erik Rijkers" <er(at)xs4all(dot)nl>
To: "Hitoshi Harada" <umi(dot)tanuki(at)gmail(dot)com>
Cc: "Marko Tiikkaja" <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>, pgsql-rrreviewers(at)postgresql(dot)org
Subject: Re: [HACKERS] top-level DML under CTEs
Date: 2010-10-04 11:46:23
Message-ID: d8995140658fbb519ce558c0e1196275.squirrel@webmail.xs4all.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers 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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Craig Ringer 2010-10-04 11:50:23 Proposed Windows-specific change: Enable crash dumps (like core files)
Previous Message Fujii Masao 2010-10-04 11:38:50 Re: ALTER DATABASE RENAME with HS/SR

Browse pgsql-rrreviewers by date

  From Date Subject
Next Message Fujii Masao 2010-10-04 13:48:29 Re: Review: Patch for Synchronous Replication
Previous Message Dean Rasheed 2010-10-04 06:58:44 Re: wip: functions median and percentile