Message-ID: <48ee60e4-7ec3-ab21-ff67-e7f9dc2e37b1@enterprisedb.com>
Date: Thu, 21 Apr 2022 13:48:16 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.7.0
Subject: Re: Assert failure in CTE inlining with view and correlated subquery
Content-Language: en-US
To: Richard Guo <guofenglinux@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <29196a1e-ed47-c7ca-9be2-b1c636816183@enterprisedb.com>
 <CAMbWs498tSxj0eiR13JvgYq=hPjwqG086_Fo8X34ESp4OQHDug@mail.gmail.com>
 <CAMbWs48n9MAfokXPoJZ3La8KMru8rJ7bVB31Ywv=x2adtDjCMg@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: 
 <CAMbWs48n9MAfokXPoJZ3La8KMru8rJ7bVB31Ywv=x2adtDjCMg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/48ee60e4-7ec3-ab21-ff67-e7f9dc2e37b1%40enterprisedb.com>
Precedence: bulk

On 4/21/22 10:29, Richard Guo wrote:
> 
> On Thu, Apr 21, 2022 at 3:51 PM Richard Guo <guofenglinux@gmail.com
> <mailto:guofenglinux@gmail.com>> wrote:
> 
> 
>     On Thu, Apr 21, 2022 at 5:33 AM Tomas Vondra
>     <tomas.vondra@enterprisedb.com
>     <mailto:tomas.vondra@enterprisedb.com>> wrote:
> 
> 
>         it seems there's something wrong with CTE inlining when there's
>         a view
>         containing a correlated subquery referencing the CTE. 
> 
> 
>     BTW, seems view is not a necessary condition to reproduce this issue.
>     For instance:
> 
>     create table t (a int, b int);
> 
>     explain (costs off) select a from
>     (
>         with t_cte as (select a, b from t)
>         select
>             a,
>             (select b from t_cte where t_cte.a = t.a) AS t_sub
>         from t
>     ) sub;
> 
> 
> Further debugging shows that in this repro the reference to the CTE is
> removed when generating paths for the subquery 'sub', where we would try
> to remove subquery targetlist items that are not needed. So for the
> items we are to remove, maybe we need to check if they contain CTEs and
> if so decrease cterefcount of the CTEs correspondingly.
> 

Right, at some point we remove the unnecessary targetlist entries, but
that ignores the entry may reference a CTE. That's pretty much what I
meant by the counter being "out of sync".

Updating the counter while removing the entry is one option, but maybe
we could simply delay counting the CTE references until after that?


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company