search

stripe / pg-schema-diff

Go library for diffing Postgres schemas and generating SQL migrations
MIT License
278 stars 20 forks source link

Add lock timeout modifier #61

Closed bplunkett-stripe closed 11 months ago

bplunkett-stripe commented 11 months ago

Description

Add lock timeouts at the statement level. This is pretty much overkill, but it doesn't hurt to have more granularity. This will be particularly useful for concurrent index builds, where the statement should take a long time to execute but it shouldn't spend a long time waiting to acquire the AccessExclusiveLock required to modify the table metadata

Motivation

Fixes #60

Follow up work: Insert statement needs to be parameterized with lock timeout. I will do that by converting the parameters over to logfmt

Testing

CLI Apply Testing:

go run ./cmd/pg-schema-diff apply --dsn "postgres://postgres:postgres@localhost:5432/postgres" --schema-dir ~/stripe/temp/examplesql  --lock-timeout-modifier "DROP INDEX=25m" --insert-statement "1 5m: SELECT pg_sleep(5)" --allow-hazards INDEX_DROPPED,IS_USER_GENERATED
################################## Review plan ##################################
1. DROP INDEX CONCURRENTLY "some_idx";
        -- Statement Timeout: 20m0s
        -- Hazard INDEX_DROPPED: Dropping this index means queries that use this index might perform worse because they will no longer will be able to leverage it.

2.  SELECT pg_sleep(5);
        -- Statement Timeout: 5m0s
        -- Hazard IS_USER_GENERATED: This statement is user-generated

✔ Yes
############################# Executing statement 1 #############################
DROP INDEX CONCURRENTLY "some_idx";
        -- Statement Timeout: 20m0s
        -- Hazard INDEX_DROPPED: Dropping this index means queries that use this index might perform worse because they will no longer will be able to leverage it.

Finished executing statement. Duration: 6.571917ms
############################# Executing statement 2 #############################
 SELECT pg_sleep(5);
        -- Statement Timeout: 5m0s
        -- Hazard IS_USER_GENERATED: This statement is user-generated

Finished executing statement. Duration: 5.014829958s
################################### Complete ###################################
Schema applied successfully