BUG #14153: Unrecognized node type error when upsert is present in recursive CTE

From: thomas(dot)alton(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14153: Unrecognized node type error when upsert is present in recursive CTE
Date: 2016-05-21 23:28:02
Message-ID: 20160521232802.22598.13537@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

The following bug has been logged on the website:

Bug reference: 14153
Logged by: Thomas Alton
Email address: thomas(dot)alton(at)gmail(dot)com
PostgreSQL version: 9.5.3
Operating system: Ubuntu 14.04.4 LTS
Description:

Simple repro steps that results in an "ERROR: unrecognized node type:
920"

CREATE TABLE foobar (
id TEXT PRIMARY KEY
);
WITH RECURSIVE upserted AS (
INSERT INTO foobar (id) VALUES ('a')
ON CONFLICT (id) DO NOTHING
RETURNING id
)
SELECT id from upserted;

In action:

postgres(at)postgres:/home/moatra$ psql
psql (9.5.3)
Type "help" for help.

postgres=# SELECT version();
version
-------------------------------------------------------------------------------------------------
PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit
(1 row)

postgres=# CREATE DATABASE example;
CREATE DATABASE
postgres=# \c example
You are now connected to database "example" as user "postgres".
example=# CREATE TABLE foobar (
example(# id TEXT PRIMARY KEY
example(# );
CREATE TABLE
example=#
example=# \set VERBOSITY verbose
example=#
example=# WITH RECURSIVE upserted AS (
example(# INSERT INTO foobar (id) VALUES ('a')
example(# ON CONFLICT (id) DO NOTHING
example(# RETURNING id
example(# )
example-# SELECT id FROM upserted;
ERROR: XX000: unrecognized node type: 920
LOCATION: raw_expression_tree_walker, nodeFuncs.c:3410

I expected the query run successfully and return one row with 'a'.

There doesn't even need to be a recursive query in the CTE, just the
RECURSIVE keyword. Removing the "ON CONFLICT ..." clause results in a
success.

example=# WITH RECURSIVE upserted AS (
example(# INSERT INTO foobar (id) VALUES ('a')
example(# RETURNING id
example(# )
example-# SELECT id FROM upserted;
id
----
a
(1 row)

Removing the "RECURSIVE" keyword and leaving the "ON CONFLICT ..." clause is
also successful.

example=# WITH upserted AS (
example(# INSERT INTO foobar (id) VALUES ('a'), ('b')
example(# ON CONFLICT (id) DO NOTHING
example(# RETURNING id
example(# )
example-# SELECT id FROM upserted;
id
----
b
(1 row)

The server was installed by adding the
http://apt.postgresql.org/pub/repos/apt/ repo and using apt for
installation. Originally I installed 9.5.2, but upgraded to 9.5.3 to
re-verify this bug on the latest release. Running uname -a gives:

moatra(at)postgres:~$ uname -a
Linux postgres 3.13.0-85-generic #129-Ubuntu SMP Thu Mar 17 20:50:15 UTC
2016 x86_64 x86_64 x86_64 GNU/Linux

Please let me know if there's anything else I can do to be of assistance.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Geoghegan 2016-05-22 00:03:41 Re: BUG #14153: Unrecognized node type error when upsert is present in recursive CTE
Previous Message Dave Cramer 2016-05-21 22:06:27 Re: [BUGS] BUG #13856: JDBC driver 1207 not picking up properties

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2016-05-22 00:03:41 Re: BUG #14153: Unrecognized node type error when upsert is present in recursive CTE
Previous Message Michael Paquier 2016-05-21 22:47:43 Re: Latent cache flush hazard in RelationInitIndexAccessInfo