Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kXUgr-0001bL-Ae for pgsql-hackers@arkaria.postgresql.org; Tue, 27 Oct 2020 19:31:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kXUgp-0007ts-Rw for pgsql-hackers@arkaria.postgresql.org; Tue, 27 Oct 2020 19:31:27 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kXUgp-0007ti-Cq for pgsql-hackers@lists.postgresql.org; Tue, 27 Oct 2020 19:31:27 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kXUgk-0003pj-1A for pgsql-hackers@postgresql.org; Tue, 27 Oct 2020 19:31:26 +0000 Received: from [192.168.1.54] (unknown [46.39.228.149]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 6231B21C424D; Tue, 27 Oct 2020 22:31:19 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1603827079; bh=jALfWVaomEcEmwHv8CJF6jvzJCU784HuixsPl4WUCbg=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=fCoWYT1aCmx0kSD3cMJB6kwGNoLJz730oyKfjn+TNi5EMfm+ibf7IUtvTcBEL4jyj Z2GuPfPvAI2ckF9nNW7ihJeB0OiU0VoRGXlQOBQ3qeS5NqP6Y09Vysr+/rxZyktdVL IycroAQNnCNG1YOPhiWM3wTowWf92xJUzojQ6UDc= Subject: Re: SEARCH and CYCLE clauses To: Pavel Stehule , Peter Eisentraut Cc: pgsql-hackers References: <52beaf44-ccc3-0ba1-45c7-74aa251cd6ab@2ndquadrant.com> From: Anastasia Lubennikova Message-ID: <29af2c49-5f85-a7d4-7d5b-e35aab1c5357@postgrespro.ru> Date: Tue, 27 Oct 2020 22:31:19 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------59CB8299342E11C3C00885A3" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------59CB8299342E11C3C00885A3 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 10.10.2020 08:25, Pavel Stehule wrote: > Hi > > pá 9. 10. 2020 v 12:17 odesílatel Pavel Stehule > > napsal: > > > > pá 9. 10. 2020 v 11:40 odesílatel Peter Eisentraut > > 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 --------------59CB8299342E11C3C00885A3 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 10.10.2020 08:25, Pavel Stehule wrote:
Hi

pá 9. 10. 2020 v 12:17 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:


pá 9. 10. 2020 v 11:40 odesílatel Peter Eisentraut <peter.eisentraut@2ndquadrant.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
--------------59CB8299342E11C3C00885A3--