[postgres-xl-bugs] Are recursive queries supported? Docs says they are, but... did not find '}' at end of input node

Krzysztof Nienartowicz krzysztof.nienartowicz at unige.ch
Thu Mar 1 07:15:23 PST 2018


Recursive queries work well on replicated tables only from what I remember,
we use them in few places.

On Sat, Feb 24, 2018 at 5:14 PM, Rob Stoddard <rob at concertal.com> wrote:

> I really don’t want to make noise unless this isn’t a known issue,
> especially if this is an issue with the way I’ve installed Postgres-XL.
> That’s why a non-email-list bug tracking software would be nice.
>
>
>
> Anyway,  I have a table of IP, which my company calls “Player IP” for
> branding purposes, where each IP belongs to a class of IP:
>
>
>
>
>
> concertal=# \d ipclass
>
>                                  Table "public.ipclass"
>
> Column |       Type        | Collation | Nullable |               Default
>
> --------+-------------------+-----------+----------+--------
> -----------------------------
>
> id     | integer           |           | not null |
> nextval('ipclass_id_seq'::regclass)
>
> parent | integer           |           |          |
>
> name   | character varying |           |          |
>
> Indexes:
>
>     "ipclass_pkey" PRIMARY KEY, btree (id)
>
> Foreign-key constraints:
>
>     "ipclass_parent_fkey" FOREIGN KEY (parent) REFERENCES ipclass(id)
>
> Referenced by:
>
>     TABLE "ipclass" CONSTRAINT "ipclass_parent_fkey" FOREIGN KEY (parent)
> REFERENCES ipclass(id)
>
>     TABLE "playerip" CONSTRAINT "playerip_ipclass_fkey" FOREIGN KEY
> (ipclass) REFERENCES ipclass(id)
>
>
>
>
>
>
>
>
>
> And I try to run a recursive query, explain seems to have no problem but
> when I actually run the query, I get an error:
>
> concertal=# explain WITH RECURSIVE iplist(id) AS (SELECT a.id FROM
> ipclass a WHERE id = 5 UNION SELECT b.id FROM ipclass b, iplist c WHERE
> b.parent = c.id) SELECT count(d.id) FROM playerip d, iplist e WHERE
> ipclass = e.id;
>
>                                               QUERY PLAN
>
> ------------------------------------------------------------
> -------------------------------------------
>
> Aggregate  (cost=373.39..373.40 rows=1 width=8)
>
>    CTE iplist
>
>      ->  Remote Subquery Scan on all (dn1)  (cost=0.15..294.44 rows=601
> width=4)
>
>            ->  Recursive Union  (cost=0.15..294.44 rows=601 width=4)
>
>                  ->  Index Only Scan using ipclass_pkey on ipclass a
> (cost=0.15..8.17 rows=1 width=4)
>
>                        Index Cond: (id = 5)
>
>                 ->  Hash Join  (cost=0.33..27.43 rows=60 width=4)
>
>                        Hash Cond: (b.parent = c.id)
>
>                        ->  Remote Subquery Scan on all (dn1)
> (cost=100.00..137.60 rows=1200 width=8)
>
>                              ->  Seq Scan on ipclass b  (cost=0.00..22.00
> rows=1200 width=8)
>
>                        ->  Hash  (cost=0.20..0.20 rows=10 width=4)
>
>                              ->  WorkTable Scan on iplist c
> (cost=0.00..0.20 rows=10 width=4)
>
>    ->  Hash Join  (cost=19.23..75.87 rows=1232 width=4)
>
>          Hash Cond: (e.id = d.ipclass)
>
>          ->  CTE Scan on iplist e  (cost=0.00..12.02 rows=601 width=4)
>
>          ->  Hash  (cost=119.43..119.43 rows=410 width=8)
>
>                ->  Remote Subquery Scan on all (dn1)  (cost=100.00..119.43
> rows=410 width=8)
>
>                      ->  Seq Scan on playerip d  (cost=0.00..14.10
> rows=410 width=8)
>
> (18 rows)
>
>
>
> concertal=# WITH RECURSIVE iplist(id) AS (SELECT a.id FROM ipclass a
> WHERE id = 5 UNION SELECT b.id FROM ipclass b, iplist c WHERE b.parent =
> c.id) SELECT count(d.id) FROM playerip d, iplist e WHERE ipclass = e.id;
>
> ERROR:  did not find '}' at end of input node
>
>
>
> I use such recursive queries in a few places in my software.  They work
> great on regular PostgreSQL, but not so good on Postgres-XL.
>
>
> Is there a web page that tracks bugs, or is this mailing list it?   I’d
> hate to make noise about a bug that’s been reported already.  Also, I’m not
> subscribed to this list so make sure that any replies keeps my email in the
> loop.
>
>
>
> Thank you
>
>
>
> Rob Stoddard
>
>
>
> _______________________________________________
> postgres-xl-bugs mailing list
> postgres-xl-bugs at lists.postgres-xl.org
> http://lists.postgres-xl.org/listinfo.cgi/postgres-xl-bugs-postgres-xl.org
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-bugs-postgres-xl.org/attachments/20180301/80f6a560/attachment-0001.html>


More information about the postgres-xl-bugs mailing list