Recursive CTEs in Sync Streams?

[KazimirPodolski]

I'm trying to figure out how to sync what should be a recursive CTE, but Supported SQL doesn't mention if it's supported or not. What's the current status on this?

[simolus3]

We don't currently support recursive CTEs. Do you have an example for a recursive CTE you need? Perhaps we can come up with alternatives. I think we could technically support recursive CTEs in the current architecture, but it would still be a lot of work and it would make it even harder to diagnose too many buckets / parameter issues (which can already be quite tricky to figure out with the current system).

[KazimirPodolski]

Sorry for the slow answer, I have a lot on my plate currently.

I have Markdown-ish documents which can reference each other abritrarily. A graph of references is natural here for e.g. transitive access, cycle prevention and other things.

CREATE TEMPORARY TABLE document (
    id text PRIMARY KEY
);

CREATE TEMPORARY TABLE document_ref (
    id text PRIMARY KEY,
    from_doc_id text REFERENCES document,
    to_doc_id text REFERENCES document
);

INSERT INTO document (id)
VALUES ('A'),
       ('B'),
       ('C');

INSERT INTO document_ref (id, from_doc_id, to_doc_id)
VALUES ('1', 'A', 'B'),
       ('2', 'B', 'C');

Example problem: a user has access to a subset of documents via their JWT and must have an access to the documents referenced from accessible ones too, how to write a sync rule / stream to sync all accessible documents?

My current strategy is to maintain a de-normalized "unwrapping" of the graph so that if A is referenced from B and B is referenced from C, then there is a de-normalized record of A "transitively" referenced from C:

-- this one actually goes into sync rules
CREATE TEMPORARY TABLE document_ref_denormalized (
    id text PRIMARY KEY,
    from_doc_id text REFERENCES document,
    to_doc_id text REFERENCES document
);

INSERT INTO document_ref_denormalized (id, from_doc_id, to_doc_id)
VALUES ('1', 'A', 'B'), -- mirrors '1' from document_ref
       ('2', 'B', 'C'), -- mirrors '2' from document_ref
       ('3', 'A', 'C'); -- transitive thing

But it's super verbose and I'm not confident it won't create problems with maintenance/performance/etc of the perfect de-normalization.

Also I have a filesystem-like structure of documents and folders holding documents and other folders. Same problems there.