Lint for non-immutable check constraints

[darora]

A common failure mode of restores or other similar workflows is for a database to contain check constraints that are not immutable. This is not supported by Postgres, but not flagged when such a constraint is created: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS

It would be great if we can flag the existence of such constraints.

[olirice]

Looked into this

I don't think it's going to be possible to do this 100% reliably

There is no stored volatility category for the expressions stored in a check constraint so we'd have to do something like:

SELECT
    conname AS constraint_name,
    conrelid::regclass AS table_name,
    consrc AS constraint_definition,
    CASE
        WHEN consrc IS NULL THEN 'Unknown'
        ELSE
            CASE
                WHEN consrc ~* '\b(random|clock_timestamp|now|current_timestamp|current_time|current_date|localtimestamp|localtime|transaction_timestamp|statement_timestamp|timeofday|pg_backend_pid|pg_postmaster_start_time|pg_conf_load_time|pg_is_in_recovery|pg_is_xlog_replay_paused|pg_is_wal_replay_paused|pg_last_xact_replay_timestamp|pg_current_xlog_location|pg_current_wal_location|pg_last_xlog_receive_location|pg_last_wal_receive_location|pg_last_xact_insert_timestamp|pg_last_wal_replay_timestamp|pg_last_xlog_replay_timestamp|pg_stat_get_db_xact_commit|pg_stat_get_db_xact_rollback|pg_stat_get_db_blocks_fetched|pg_stat_get_db_blocks_hit|pg_stat_get_db_tuples_returned|pg_stat_get_db_tuples_fetched|pg_stat_get_db_tuples_inserted|pg_stat_get_db_tuples_updated|pg_stat_get_db_tuples_deleted|pg_stat_get_db_conflict_.*|pg_stat_get_bgwriter_|pg_stat_get_archiver_|pg_stat_get_slru_|pg_stat_get_|pg_stat|current_setting|set_config)\b'
                THEN 'Mutable'
                ELSE 'Immutable'
            END
    END AS immutability
FROM
    pg_constraint
WHERE
    contype = 'c';

The most likely we are to see are the timestamp ones like now e.g. check (schedule_run_at > now()) or users doing crazy stuff with current_setting

good enough, or did you have something else in mind?

[darora]

Hmm I'm OK to start with this, and then we can improve on this as we run into other instances that might be generalizable?

[olirice]

Thought about this some more and I don't think we should do it

This problem goes away with physical backups for all

As described It'd be a nasty implementation based on string matching the constraint src because pg_catalog doesn't provide the info we need to do it properly

closing for now but push back if you disagree