Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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

pgsql-hackers by date

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

pgsql-rrreviewers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group