MATERIALIZED VIEW on pg_shard

[shankar]

Hi,

I created a setup using AWS cloud formation script (https://citus-deployment.s3.amazonaws.com/aws/2015-03-17/cloudformation/citusdb.json) given in the downloads page https://www.citusdata.com/resources/downloads.

Then created and populated distributed table as per the document in https://www.citusdata.com/documentation/citusdb-documentation/examples/id_querying_raw_data.html page.

While creating materialized view it is not getting created and error message given below.

postgres=# CREATE MATERIALIZED VIEW testmview AS select * from github_events where event_id=2489368089;

ERROR:  cannot create temporary table within security-restricted operation

Appreciate your help on this to create materialized view.

Thanks

Shankar

[Marco Slot]

Hi Shankar,

If you want to create a local materialized view from a distributed table on the master node, then you can use a table instead:

CREATE TABLE testmview AS select * from github_events where event_id=2489368089;

In this case to refresh the view you could to do something like this:

BEGIN;
CREATE TABLE prepare_view AS select * from github_events where event_id=2489368089;

DROP TABLE IF EXISTS testmview;

ALTER TABLE prepare_view RENAME TO testmview;

COMMIT;

There is no notion of a "distributed materialized view" right now. Technically, it is possible to create a distributed table in which the shards are materialized views on shards, but you'd have to configure the shard metadata manually.

Hope this helps,

Marco

[shankar]

Hi Macro,

Thank you for the explanation.

Regards

Shankar