2

I am currently stuck in postgres at creating an updateable MATERIALIZED VIEW.

The MATERIALIZED VIEW consists basically from 2 (foreign) tables.

This is an example for the tables:

id | data
---+------
 1 | foo

id | data
---+------
 1 | bar

A UNION is used to generate the VIEW and also to create unique IDs (but thats a different story):

id | data
---+------
 1 | foo
 2 | bar

I want the materialized VIEW to be updateable, meaning, I want to redirect UPDATEs on the data to the corresponding tables.

So I have a 'regular' VIEW on my MATERIALIZED VIEW with a SELECT * and Triggers/Rules (both worked for my case) which handle INSERT, UPDATE and DELETE.

CREATE OR REPLACE FUNCTION updateOrigTable() RETURNS TRIGGER AS 
$$
  DECLARE
    myID IN;
  BEGIN
    IF myID > (SELECT MAX(table1.id) FROM table1) THEN
      myID := myID - (SELECT MAX(table1.id) FROM table1);
      UPDATE table2 SET dummy_data = NEW.country WHERE table2.id = myID;
    ELSE
      UPDATE table1 SET dummy_data = NEW.country WHERE users1.id = NEW.id;
    END IF;
    RETURN NEW;
  END;
$$
LANGUAGE plpgsql volatile;

CREATE TRIGGER updateTrigger  
  INSTEAD OF UPDATE  
  ON myView
  FOR EACH ROW  
  EXECUTE PROCEDURE updateOrigTable();

I also want to create a TRIGGER, that is triggered on INSERT, UPDATE and DELETE, which performs a REFRESH.

CREATE OR REPLACE FUNCTION matViewRefresh()
RETURNS TRIGGER AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY matView;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER matViewRefresh 
  AFTER INSERT OR UPDATE OR DELETE
  ON table1 FOR EACH STATEMENT
  EXECUTE PROCEDURE matViewRefresh();
  
CREATE TRIGGER matViewRefresh 
  AFTER INSERT OR UPDATE OR DELETE
  ON table2 FOR EACH STATEMENT
  EXECUTE PROCEDURE matViewRefresh();

However, when I try to update myView, it gives me

ERROR: REFRESH MATERIALIZED VIEW with Relation »matView« not possible because it is used by active queries in this session (translated from German)

I do not understand why this blocks. The refresh happens after the update, so the database object from matView should already be unblocked.

Is there a way to create a hardcopy of the database object in my update procedure?

Any other hints?

0
1

This will work if you create the trigger as a deferrable initially deferred constraint trigger. So instead of

CREATE TRIGGER matViewRefresh 
  AFTER INSERT OR UPDATE OR DELETE
  ON table1 FOR EACH STATEMENT
  EXECUTE PROCEDURE matViewRefresh();

do

CREATE CONSTRAINT TRIGGER matViewRefresh
    AFTER INSERT OR DELETE OR UPDATE
    ON table1
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH STATEMENT
    EXECUTE PROCEDURE matViewRefresh();
2
  • can you elaborate or make a fiddle with it
    – nbk
    Jul 22 at 12:02
  • I just updated my answer.
    – beerockxs
    Jul 22 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.