Refreshing a materialized view with SQLAlchemy

Question

I have the following setup:

Postgres

In Postgres (Aurora), I've got a materialised view set up. As only the owner of the view is allowed to refresh it, I've created the following function as well to allow other users to refresh it:

CREATE OR REPLACE FUNCTION refresh_views()
    RETURNS void
    SECURITY DEFINER
AS
$$
BEGIN
    REFRESH MATERIALIZED VIEW my_schema.my_view with data;
    RETURN;
END;
$$ LANGUAGE plpgsql;

When I go to a SQL terminal and run the following line, the view refreshes as expected:

SELECT refresh_views();

Python

We use Python with SQLAlchemy/Pandas, and for the most part that works really well. However, I can't seem to get this working properly from Python. Both versions below run without issue and take roughly the same time (~1 minute), but the view is not updated afterwards.

cxn.execute(text("SELECT refresh_views();"))
pd.read_sql(text("SELECT refresh_views();"), cxn)

Any idea why this is not working?

Answer 1

You should call commit at the end of the connection. Command SQL terminal is in autcommit=True mode, while a session has default autocommit=False.

This post is about your problem: PostgreSQL materialized view not refreshing from Python