Re: SEARCH and CYCLE clauses

From: Vik Fearing <vik(at)postgresfriends(dot)org>
To: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: SEARCH and CYCLE clauses
Date: 2020-05-20 19:28:04
Message-ID: b267acb8-edc0-31a8-0645-2992d1f0f393@postgresfriends.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 5/20/20 3:04 PM, Vik Fearing wrote:

> I'm looking forward to reviewing this.
A few quick things I've noticed so far:

1)
There are some smart quotes in the comments that should be converted to
single quotes.

2)
This query is an infinite loop, as expected:

with recursive a as (select 1 as b union all select b from a)
table a;

But it becomes an error when you add a cycle clause to it:

with recursive a as (select 1 as b union all table a)
cycle b set c to true default false using p
table a;

ERROR: each UNION query must have the same number of columns

The same error occurs with a search clause.

3)
If I take the same infinite loop query but replace the TABLE syntax with
a SELECT and add a cycle clause, it's not an infinite loop anymore.

with recursive a as (select 1 as b union all select b from a)
cycle b set c to true default false using p
table a;

b | c | p
---+---+-----------
1 | f | {(1)}
1 | t | {(1),(1)}
(2 rows)

Why does it stop? It should still be an infinite loop.

4)
If I use NULL instead of false, I only get one row back.

with recursive a as (select 1 as b union all select b from a)
cycle b set c to true default false using p
table a;

b | c | p
---+---+-------
1 | | {(1)}
(1 row)

5)
I can set both states to the same value.

with recursive a as (select 1 as b union all select b from a)
cycle b set c to true default true using p
table a;

b | c | p
---+---+-------
1 | t | {(1)}
(1 row)

This is a direct violation of 7.18 SR 2.b.ii.3 as well as common sense.
BTW, I applaud your decision to violate the other part of that rule and
allowing any data type here.

5)
The same rule as above says that the value and the default value must be
literals but not everything that a human might consider a literal is
accepted. In particular:

with recursive a as (select 1 as b union all select b from a)
cycle b set c to 1 default -1 using p
table a;

ERROR: syntax error at or near "-"

Can we just accept a full a_expr here instead of AexprConst? Both
DEFAULT and USING are fully reserved keywords.

That's all for now; will test more later.
Thanks for working on this!
--
Vik Fearing

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Dilger 2020-05-20 20:18:01 Re: factorial function/phase out postfix operators?
Previous Message Tom Lane 2020-05-20 18:24:43 Re: factorial function/phase out postfix operators?