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

Re: [PATCHES] WITH RECUSIVE patches 0717

From: Tatsuo Ishii <ishii(at)postgresql(dot)org>
To: er(at)xs4all(dot)nl
Cc: ishii(at)postgresql(dot)org, pgsql-patches(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org, y-asaba(at)sraoss(dot)co(dot)jp
Subject: Re: [PATCHES] WITH RECUSIVE patches 0717
Date: 2008-07-20 15:42:16
Message-ID: 20080721.004216.38709223.t-ishii@sraoss.co.jp (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
> This crashes the backend:
> 
> WITH RECURSIVE t(n) AS (
>     VALUES (1)
>   UNION ALL
>     SELECT n+1 FROM t WHERE n < 5 ORDER BY 1
> )
> SELECT n FROM t;
> 
> apparently because of the  ORDER BY 1

Thanks for the report. I think ORDER BY in this case is useless
anyway. ORDER BY affects (VALUES (1) UNION ALL SELECT n+1 FROM t WHERE
n < 5). Since this is a recursive query, value for (VALUES (1) UNION
ALL SELECT n+1 FROM t WHERE n < 5) will not be determined until the
recursion stops. So the meaning of ORDER BY is vague. If caller wants
to get the sorted result of the recursion, he could always write:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 5
)
SELECT n FROM t ORDER BY 1;

Thus I think we should avoid this kind of ORDER BY. Probably we should
avoid LIMIT/OFFSET and FOR UPDATE as well. Included patches add the
checking plus minor error messages clarifications. Also I include new
error cases sql.

> ( ORDER BY t.n  will just error out )
>
> Compiled with:
> 
> 	./configure \
> 	 --prefix=${install_dir} \
> 	 --with-pgport=${pgport} \
> 	 --quiet          \
> 	 --enable-depend  \
> 	 --enable-cassert \
> 	 --enable-debug   \
> 	 --with-openssl
> 
> 
> hth
> 
> Erik Rijkers
> 
> 
> 
> 
> 

Attachment: recursive_query.patch.gz
Description: application/octet-stream (25.1 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-07-20 16:04:37
Subject: Re: temp table problem
Previous:From: Heikki LinnakangasDate: 2008-07-20 15:27:06
Subject: Re: temp table problem

pgsql-patches by date

Next:From: Tom LaneDate: 2008-07-20 16:22:30
Subject: Re: [PATCHES] WITH RECUSIVE patches 0717
Previous:From: Simon RiggsDate: 2008-07-20 06:37:42
Subject: Re: pg_dump additional options for performance

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