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

Aaron Jackson ajackson at revionics.com
Wed Sep 24 06:16:42 PDT 2014

I also looked through the code and noticed the limitation on CTEs.  The problem however is given the way they are written, I can't see a single recursive CTE that would work.  In particular, the moment you query against or join against the CTE, the parse node generates a node with a relkind that isn't of type RTE_JOIN or RTE_RELATION and fails.

That in and of itself makes me wonder what recursive functionality one could come up with.

Seems like the alternative is to write a recursive function ... much like the functions outlined in this link.


I am curious on an academic level.  What makes the recursive CTE that I presented earlier an issue?

From: Nikhil Sontakke [nikhils at translattice.com]
Sent: Wednesday, September 24, 2014 8:02 AM
To: Pavan Deolasee
Cc: Aaron Jackson; postgres-xl-general at lists.sourceforge.net
Subject: Re: [Postgres-xl-general] Recursive CTE on replicated table?

On Wed, Sep 24, 2014 at 8:43 AM, Aaron Jackson <ajackson at revionics.com<mailto:ajackson at revionics.com>> wrote:
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.

I could be wrong, but looking at the code it seems  WITH RECURSIVE support is quite limited in XL right now. That error message is also misleading because WITH RECURSIVE seems to be blocked for subqueries, function scans, value scan etc. In fact, I wonder if recursive queries work at all right now. WITH queries (i.e. without recursion) should work for much better though.

AFAIR, we support a subset of the entire WITH RECURSIVE functionality.

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?
Like I said, the error message is misleading because even for subqueries, the same error message is printed, We should at least fix the error message in the next release.

Yeah, we have added error messages to avoid misleading results due to missing functionality in some cases.



Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
Postgres-xl-general mailing list
Postgres-xl-general at lists.sourceforge.net<mailto:Postgres-xl-general at lists.sourceforge.net>

TransLattice - http://www.translattice.com
Distributed and Clustered Database Solutions
Postgres-XL Support

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

More information about the postgres-xl-general mailing list