Autocommit for REFRESH MATERIALIZED VIEW

[Frazer McLean]

I would expect that REFRESH MATERIALIZED VIEW on PostgreSQL is treated similarly to UPDATE/INSERT/CREATE/DELETE/DROP/ALTER.

Failing MCVE:

from sqlalchemy import Column, Integer, MetaData, Table, create_engine, text

metadata = MetaData()
foo = Table('foo', metadata, Column('id', Integer, primary_key=True))

engine = create_engine('postgres://scott:tiger@localhost/test')

foo.create(bind=engine)

engine.execute('''
    CREATE MATERIALIZED VIEW IF NOT EXISTS bar AS (
        SELECT id FROM foo
    )
''')

engine.execute(foo.insert())

assert engine.scalar('SELECT count(*) FROM bar') == 0

engine.execute('REFRESH MATERIALIZED VIEW bar')
# engine.execute(text('REFRESH MATERIALIZED VIEW bar').execution_options(autocommit=True))

assert engine.scalar('SELECT count(*) FROM bar') == 1

[Mike Bayer]

Please send a pull request that adds a new method to lib/sqlalchemy/postgresql/base.py -> PGDialect -> should_autocommit_text(), calls down to the superclass should_Autocommit_text() first then tests for additional regular expressions. Including tests would be helpful as well.

in the interim, please the autocommit execution option which is designed to allow for autocommit wherever needed (which in theory would have prevented me from chasing down every possible keyword here, but that does not seem to be catching on).

[Mike Bayer]

Add new DDL autocommit expressions for Postgresql

Added regular expressions for the "IMPORT FOREIGN SCHEMA", "REFRESH MATERIALIZED VIEW" Postgresql statements so that they autocommit when invoked via a connection or engine without an explicit transaction. Pull requests courtesy Frazer McLean and Paweł Stiasny.

Fixes: #3840 Co-authored-by: Frazer McLean Co-authored-by: Paweł Stiasny Change-Id: I92b2b61683d29d57fa23a66a3559120cb1241c2f Pull-request: https://github.com/zzzeek/sqlalchemy/pull/323

→ <<cset 3b6004e6ab3f>>