2

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?
metdos
  • 13,411
  • 17
  • 77
  • 120
emk
  • 60,150
  • 6
  • 45
  • 50
  • A quick update, we (Citus) have seen this question and will get a response to you soon. CREATE VIEW doesn't work as you've written it, but there's a workaround which takes a little time to write up. – num1 Apr 12 '16 at 06:52

1 Answers1

3

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?

metdos
  • 13,411
  • 17
  • 77
  • 120