[pgpool-general: 1322] Re: pgpool having trouble parsing a query

[Tatsuo Ishii]

> I'm sending this rather complicated but still valid query to pgpool
> 
> with missing_caches as (
>     select 369 as uid, cids as cid
>     from unnest(array[9089, 9088, 8664, 8665, 1118]) as cids
>       left join ptplsets_count_cache on (uid = 369 and cid = cids)
>     where cid is null
> )
> insert into ptplsets_count_cache (uid, cid, count)
>     select missing_caches.uid, missing_caches.cid,
> count(ptplsets.prodid) as count
>     from missing_caches
>     left join ptplsets on (ptplsets.cid = missing_caches.cid)
>     left join materialized_user_sortiments on
> (materialized_user_sortiments.uid = missing_caches.uid and
> materialized_user_sortiments.prodid = ptplsets.prodid and
> sortiment_status > 0)
> where
>     ptplsets.deleted is null or not ptplsets.deleted
> group by missing_caches.cid, missing_caches.uid
> 
> While I get the correct result, I also get a line in syslog
> 
> Jan 16 14:06:59 tranquillity pgpool[20813]: SimpleQuery: Unable to
> parse the query: "..."
> 
> Thankfully pgpool (latest git HEAD from V3_2_STABLE) seems to just
> forward the query to master in this case, so the outcome is correct,
> but the error message still concerns me a bit, especially considering
> that this is running quite often.
> 
> In general, I've not yet seen pgpool having issues with CTEs, so if I
> had to guess, I would assume it doesn't like the call to unnest().
> 
> Any idea how I can help pgpool here? Get rid of the unnest? I still
> think pgpool should be able to parse what ever postgres can parse
> though.

Yeah, it should be. Pgpool steals PostgreSQL's parser and that was
PostgreSQL 9.0 days(as of pgpool-II 3.1 or higher). My guess is
updatable CTE in your query cannot be parsed by PostgreSQL 9.0
parser. Apparently we need to imports newer parser from PostgreSQL but
it's not a trivial job.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp