Subject: Re: SEARCH and CYCLE clauses
To: Vik Fearing <vik@postgresfriends.org>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <db80ceee-6f97-9b4a-8ee8-3ba0c58e5be2@2ndquadrant.com>
 <54e533c5-09e5-7f41-9892-d4e0f7c62598@postgresfriends.org>
 <b267acb8-edc0-31a8-0645-2992d1f0f393@postgresfriends.org>
 <1cfc9079-9595-d167-00e6-5014438cc3e2@2ndquadrant.com>
 <9ce4d370-048c-1d67-fffb-a86f17120132@postgresfriends.org>
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Organization: 2ndQuadrant
Message-ID: <d9f5c4ff-723a-2eae-2985-02bb11ae5e05@2ndquadrant.com>
Date: Fri, 22 May 2020 14:32:41 +0200
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:52.0)
 Gecko/20100101 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <9ce4d370-048c-1d67-fffb-a86f17120132@postgresfriends.org>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 2020-05-22 12:40, Vik Fearing wrote:
>> If you specify null, then the cycle check expression will always fail,
>> so it will abort after the first row.  (We should perhaps prohibit
>> specifying null, but see below.)
> 
> I would rather make the cycle check expression work with null.

It works correctly AFAICT.  What is your expectation?

>> This is something we need to think about.  If we want to check at parse
>> time whether the two values are not the same (and perhaps not null),
>> then we either need to restrict the generality of what we can specify,
>> or we need to be prepared to do full expression evaluation in the
>> parser.  A simple and practical way might be to only allow string and
>> boolean literal.  I don't have a strong opinion here.
> 
> 
> I'm with Pavel on this one.  Why does it have to be a parse-time error?
>   Also, I regularly see people write functions as sort of pauper's
> variables, but a function call isn't allowed here.

If not parse-time error, at what time do you want to check it?

> As an improvement over the spec, I think the vast majority of people
> will be using simple true/false values.  Can we make that optional?
> 
>      CYCLE f, t SET is_cycle USING path
> 
> would be the same as
> 
>      CYCLE f, t SET is_cycle TO true DEFAULT false USING path

I was also considering that.  It would be an easy change to make.

(Apparently, in DB2 you can omit the USING path clause.  Not sure how to 
make that work, however.)

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services