[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