Refresh materialized views concurrently or with timeouts

[sebschrader]

During a refresh of an materialized view the view is locked exclusively, preventing other queries from accessing the view.

If then in turn the SQL query on the foreign database server hangs, e.g. it waits for locks, the refresh can hang potentially forever.

Since PostgreSQL 9.4 there is an CONCURRENTLY option for REFRESH MATERIALIZED VIEWS. See here.

Adding timeouts to foreign SQL query, the refresh materialized view query, or both is another, potentially better, option.

[sebschrader]

EnterpriseDB/mysql_fdw#47, which has been merged into the development version of mysql_fdw, added an init_command foreign server option, which can be used to set the lock_wait_timeout option for MySQL connections.

[sebschrader]

Somewhere in the 3.x development of freeradius, an open_query option has been added to the rlm_sql module, this option can be used to issue an SET statement_timeout = 5 query. Hades currently generates a config file suitable for freeradius 2.x (as 3.x is not available in Debian yet), so open_query can't be used yet.

[fgrsnau]

How severe is this issue? Does it make sense to target this for v0.3?

Debian Jessie includes Postgres 9.4.

[fgrsnau]

Oh, we are using Postgres 9.4 already. Using CONCURRENTLY to refresh the materialized views is not a big deal. The only thing we need is a UNIQUE INDEX for those views (enforced by CONCURRENTLY refresh).

For Hade 0.3 we can ship this quick fix. (We can leave this open, because the freeradius option is out of reach, currently. I also know nothing about the timeout stuff for foreign SQL queries.)

There is a problem however: The radusergroup table does not include a column for which we can create a UNIQUE INDEX.

CREATE FOREIGN TABLE foreign_radusergroup (
    username character varying(64) NOT NULL,
    {%- if HADES_POSTGRESQL_FOREIGN_TABLE_RADUSERGROUP_NASIPADDRESS_STRING %}
    nasipaddress character varying(15) NOT NULL,
    {%- else %}
    nasipaddress inet NOT NULL,
    {%- endif %}
    nasportid character varying(15) NOT NULL,
    groupname character varying(64) NOT NULL,
    priority integer NOT NULL
)
SERVER {{ server_name }}
OPTIONS (
    {{ table_options(HADES_POSTGRESQL_FOREIGN_TABLE_RADUSERGROUP_OPTIONS) }}
);

Can we add a new column id that acts as primary key for this table in the foreign database (MySQL)?