Re: SEARCH and CYCLE clauses

On 10.10.2020 08:25, Pavel Stehule wrote:
> Hi
>
> pá 9. 10. 2020 v 12:17 odesílatel Pavel Stehule
> <pavel(dot)stehule(at)gmail(dot)com <mailto:pavel(dot)stehule(at)gmail(dot)com>> napsal:
>
>
>
> pá 9. 10. 2020 v 11:40 odesílatel Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)com
> <mailto:peter(dot)eisentraut(at)2ndquadrant(dot)com>> napsal:
>
> On 2020-09-22 20:29, Pavel Stehule wrote:
> > The result is correct. When I tried to use UNION instead
> UNION ALL, the
> > pg crash
>
> I fixed the crash, but UNION [DISTINCT] won't actually work
> here because
> row/record types are not hashable.  I'm leaving the partial
> support in,
> but I'm documenting it as currently not supported.
>
>  I think so UNION is a common solution against the cycles. So
> missing support for this specific case is not a nice thing. How
> much work is needed for hashing rows. It should not be too much code.
>
>
> > looks so clause USING in cycle detection is unsupported for
> DB2 and
> > Oracle - the examples from these databases doesn't work on
> PG without
> > modifications
>
> Yeah, the path clause is actually not necessary from a user's
> perspective, but it's required for internal bookkeeping.  We
> could
> perhaps come up with a mechanism to make it invisible coming
> out of the
> CTE (maybe give the CTE a target list internally), but that
> seems like a
> separate project.
>
> The attached patch fixes the issues you have reported (also
> the view
> issue from the other email).  I have also moved the whole rewrite
> support to a new file to not blow up rewriteHandler.c so much.
>
> --
> Peter Eisentraut http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training &
> Services
>
>
> This patch is based on transformation CYCLE and SEARCH clauses to
> specific expressions - it is in agreement with ANSI SQL
>
> There is not a problem with compilation
> Nobody had objections in discussion
> There are enough regress tests and documentation
> check-world passed
> doc build passed
>
> I'll mark this patch as ready for committer
>
> Possible enhancing for this feature (can be done in next steps)
>
> 1. support UNION DISTINCT
> 2. better compatibility with Oracle and DB2 (USING clause can be optional)
>
> Regards
>
> Pavel
>
>
>
>
Status update for a commitfest entry.
According to cfbot patch no longer applies. So I moved it to waiting on
author.

--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company