Search code examples
postgresqlcitus

Does CitusDB support `CREATE VIEW`?

Assume we have a distributed CitusDB table named customer_reviews, and we try to create a view on it:

CREATE VIEW book_reviews AS
  (SELECT * FROM customer_reviews WHERE product_group = 'Book');

This appears to work. But if we run:

SELECT COUNT(1) FROM book_reviews;

CitusDB gives the following error:

ERROR:  cannot plan queries that include both regular and partitioned relations

Two questions:

  1. Is there a way to work around this by manually creating the view on all worker nodes?
  2. Is there a way to make CREATE VIEW and DROP VIEW work correctly on the master node, for apps which create and destroy views automatically at runtime?
Solution

  • UPDATE: View support is added to Citus with this PR.

    First of all, I created an issue to track this. Please feel free to add your comments and feedback on that issue.

    Until we implement this feature, I see two workarounds;

    • Using a UDF or a PL/pgSQL function to wrap the view query instead of creating the view. I added specific examples to the GitHub issue

    • Create some UDFs and PL/pgSQL functions to propagate views down to the shards on worker nodes and manipulate metadata to simulate views on the master node. I also added a prototype approach to the GitHub issue

    I think what is best for you depends on your CREATE VIEW queries and your application stack. Could you explain your use-case and how do you plan to use views a bit more?