github.com/blend/go-sdk@v1.20220411.3/examples/db/statement-timeout/README.md (about)

     1  # Capping Query Running Time with Statement Timeout
     2  
     3  ## Prerequisites
     4  
     5  Set some common environment variables (we `export` here to make running
     6  the Go script a bit simpler, but these can be local in a shell or local
     7  to a command)
     8  
     9  ```
    10  export DB_HOST=localhost
    11  export DB_PORT=30071
    12  export DB_USER=superuser
    13  export DB_NAME=superuser_db
    14  export DB_PASSWORD=testpassword_superuser
    15  export DB_SSLMODE=disable
    16  ```
    17  
    18  and make sure a local `postgres` server is running
    19  
    20  ```
    21  docker run \
    22    --detach \
    23    --hostname "${DB_HOST}" \
    24    --publish "${DB_PORT}:5432" \
    25    --name dev-postgres-statement-timeout \
    26    --env "POSTGRES_DB=${DB_NAME}" \
    27    --env "POSTGRES_USER=${DB_USER}" \
    28    --env "POSTGRES_PASSWORD=${DB_PASSWORD}" \
    29    postgres:10.6-alpine
    30  ```
    31  
    32  ## Intentional Timeout
    33  
    34  In order to simulate a long-running query we run
    35  
    36  ```sql
    37  SELECT id, pg_sleep(...) FROM might_sleep WHERE id = 1337;
    38  ```
    39  
    40  where `...` is the configured `pg_sleep`.
    41  
    42  ## Let `postgres` Cancel Via `statement_timeout`
    43  
    44  ```
    45  $ go run .
    46  0.000055 ==================================================
    47  0.000090 Configured statement timeout: 10ms
    48  0.000095 Configured pg_sleep:          200ms
    49  0.000098 Configured context timeout:   400ms
    50  0.000124 ==================================================
    51  0.015619 DSN="postgres://superuser:testpassword_superuser@localhost:30071/superuser_db?connect_timeout=5&sslmode=disable&statement_timeout=10ms"
    52  0.015629 ==================================================
    53  0.016960 statement_timeout=10ms
    54  0.024797 ==================================================
    55  0.024812 Starting query
    56  0.036470 ***
    57  0.036478 Error(s):
    58  0.036488 - Message: "SELECT id, pg_sleep(0.200000) FROM might_sleep WHERE id = 1337;"
    59  0.036529 - &pq.Error{Severity:"ERROR", Code:"57014", Message:"canceling statement due to statement timeout", Detail:"", Hint:"", Position:"", InternalPosition:"", InternalQuery:"", Where:"", Schema:"", Table:"", Column:"", DataTypeName:"", Constraint:"", File:"postgres.c", Line:"2996", Routine:"ProcessInterrupts"}
    60  ```
    61  
    62  From [Appendix A. PostgreSQL Error Codes][1]:
    63  
    64  ```
    65  Class 57 - Operator Intervention
    66  ---------+-----------------------
    67     57014 | query_canceled
    68  ```
    69  
    70  ## Cancel Query via Go `context` Cancelation
    71  
    72  ```
    73  $ VIA_GO_CONTEXT=true go run .
    74  0.000081 ==================================================
    75  0.000116 Configured statement timeout: 10s
    76  0.000120 Configured pg_sleep:          200ms
    77  0.000133 Configured context timeout:   100ms
    78  0.000163 ==================================================
    79  0.014563 DSN="postgres://superuser:testpassword_superuser@localhost:30071/superuser_db?connect_timeout=5&sslmode=disable&statement_timeout=10000ms"
    80  0.014575 ==================================================
    81  0.016120 statement_timeout=10s
    82  0.023707 ==================================================
    83  0.023729 Starting query
    84  0.106258 ***
    85  0.106272 Error(s):
    86  0.106309 - Message: "SELECT id, pg_sleep(0.200000) FROM might_sleep WHERE id = 1337;"
    87  0.106341 - &pq.Error{Severity:"ERROR", Code:"57014", Message:"canceling statement due to user request", Detail:"", Hint:"", Position:"", InternalPosition:"", InternalQuery:"", Where:"", Schema:"", Table:"", Column:"", DataTypeName:"", Constraint:"", File:"postgres.c", Line:"3026", Routine:"ProcessInterrupts"}
    88  ```
    89  
    90  ## `psql` Does **NOT** Support `statement_timeout` in DSN
    91  
    92  See `libpq` [Parameter Key Words][2]
    93  
    94  ```
    95  $ psql "postgres://superuser:testpassword_superuser@localhost:30071/superuser_db?connect_timeout=5&sslmode=disable&statement_timeout=10ms"
    96  psql: error: could not connect to server: invalid URI query parameter: "statement_timeout"
    97  $
    98  $
    99  $ psql "postgres://superuser:testpassword_superuser@localhost:30071/superuser_db?connect_timeout=5&sslmode=disable"
   100  ...
   101  superuser_db=# SHOW statement_timeout;
   102   statement_timeout
   103  -------------------
   104   0
   105  (1 row)
   106  
   107  superuser_db=# \q
   108  $
   109  $
   110  $ PGOPTIONS="-c statement_timeout=5500ms" psql "postgres://superuser:testpassword_superuser@localhost:30071/superuser_db?connect_timeout=5&sslmode=disable"
   111  ...
   112  superuser_db=# SHOW statement_timeout;
   113   statement_timeout
   114  -------------------
   115   5500ms
   116  (1 row)
   117  
   118  superuser_db=# \q
   119  ```
   120  
   121  ## Clean Up
   122  
   123  ```
   124  docker rm --force dev-postgres-statement-timeout
   125  ```
   126  
   127  [1]: https://www.postgresql.org/docs/10/errcodes-appendix.html
   128  [2]: https://www.postgresql.org/docs/10/libpq-connect.html#LIBPQ-PARAMKEYWORDS