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?
CodePudding user response:
You should call commit at the end of connection. Command SQL terminal is in autcommit=True
mode, while session has default autocommit=False
.
This post is about your problem: PostgreSQL materialized view not refreshing from Python