[Postgres-xl-general] Recursive CTE on replicated table?

Aaron Jackson ajackson at revionics.com
Tue Sep 23 20:20:55 PDT 2014


For example, in this case, hierarchy.hierarchynode reports as follows:

Distribute By: REPLICATION
Location Nodes: ALL DATANODES

And the following CTE generates the violation.

with recursive hiercte
as
(
   select
 h.hierarchylevelid,
 h.hierarchynodeid,
 h.parenthierarchynodeid,
 h.nodename,
 h.externalidentifier
   from hierarchy.hierarchynode h

   union all

   select
      p.hierarchylevelid,
      p.hierarchynodeid,
      p.parenthierarchynodeid,
      p.nodename,
      p.externalidentifier
   from hiercte c
   join hierarchy.hierarchynode p on c.parenthierarchynodeid = p.hierarchynodeid
)
select * from hiercte


Aaron
________________________________
From: Aaron Jackson [ajackson at revionics.com]
Sent: Tuesday, September 23, 2014 10:13 PM
To: postgres-xl-general at lists.sourceforge.net
Subject: [Postgres-xl-general] Recursive CTE on replicated table?

My colleague wrote a function that employed a recursive CTE.  It failed indicating that "WITH RECURSIVE currently not supported on distributed tables" - I read that as doesn't work on distributed tables but should work on replicated tables.  So we replicated the table and found we got the same error.

If true, it means that recursive CTEs will not work on ANY tables since a table must by definition be distributed (even if by replication).  Are we missing something here?

Aaron
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-general-postgres-xl.org/attachments/20140924/fc4a63b4/attachment.htm>


More information about the postgres-xl-general mailing list