Deadlock on destroy

[jerome-quere]

Hello,

I triggered a deadlock when I try to run terraform destroy. I try to isolate as much as possible by reducing my TF file. With the provided file, it's a 50% chance of deadlock when trying to destroy.

Terraform Version

$> terraform -v                                                                                                                                                                                        Terraform v1.0.1
on darwin_amd64
+ provider registry.terraform.io/cyrilgdn/postgresql v1.13.0

Affected Resource(s)

• postgresql_database

Terraform Configuration Files

terraform {
  required_providers {
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "1.13.0"
    }
  }
}

provider "postgresql" {
  host            = "localhost"
  port            = 5432
  username        = "terraform"
  password        = "terraform"
  sslmode         = "disable"
  superuser       = false
  max_connections = 1
}

resource "postgresql_database" "test" {
  name = "test"
}

resource "postgresql_role" "test" {
  name        = "test"
  login       = true
  password    = "test"
  search_path = ["test"]
}

resource "postgresql_schema" "test" {
  name         = postgresql_database.test.name
  database     = postgresql_database.test.name
  drop_cascade = true
}

Expected Behavior

Should destroy all ressources

Actual Behavior

postgresql_database.test: Still destroying... [id=test, 30m0s elapsed]

Steps to Reproduce

$> terrafrom apply -auto-approve 
$> terraform destroy     

Sometime it take 2 or 3 times to trigger the dead lock

Other informations

SELECT version();
                                         version                                          
------------------------------------------------------------------------------------------
 PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by Debian clang version 10.0.1 , 64-bit

 SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;

  locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid  |      mode       | granted | fastpath | datid | datname  | pid  | leader_pid | usesysid |  usename  |  application_name  | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | wait_event_type | wait_event |        state        | backend_xid | backend_xmin |                                                            query                                                            |  backend_type  
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+-----------------+---------+----------+-------+----------+------+------------+----------+-----------+--------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+------------+---------------------+-------------+--------------+-----------------------------------------------------------------------------------------------------------------------------+----------------
 relation   |    16547 |    12250 |      |       |            |               |         |       |          | 5/9186             | 8375 | AccessShareLock | t       | t        | 16547 | test     | 8375 |            |    16441 | terraform | psql               |             |                 |          -1 | 2021-06-30 17:41:57.416802+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479557+00 |                 |            | active              |             |         9866 | SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;                                               | client backend
 relation   |    16547 |    12141 |      |       |            |               |         |       |          | 5/9186             | 8375 | AccessShareLock | t       | t        | 16547 | test     | 8375 |            |    16441 | terraform | psql               |             |                 |          -1 | 2021-06-30 17:41:57.416802+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479557+00 |                 |            | active              |             |         9866 | SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;                                               | client backend
 virtualxid |          |          |      |       | 5/9186     |               |         |       |          | 5/9186             | 8375 | ExclusiveLock   | t       | t        | 16547 | test     | 8375 |            |    16441 | terraform | psql               |             |                 |          -1 | 2021-06-30 17:41:57.416802+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479557+00 |                 |            | active              |             |         9866 | SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;                                               | client backend
 relation   |    14040 |    12087 |      |       |            |               |         |       |          | 6/9483             | 8345 | AccessShareLock | t       | t        | 14040 | postgres | 8345 |            |    16441 | terraform | Terraform provider |             |                 |          -1 | 2021-06-30 17:40:35.42992+00  | 2021-06-30 17:40:35.47188+00  | 2021-06-30 17:40:35.552341+00 | 2021-06-30 17:40:35.552367+00 | Client          | ClientRead | idle in transaction |             |         9850 | SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1 | client backend
 virtualxid |          |          |      |       | 6/9483     |               |         |       |          | 6/9483             | 8345 | ExclusiveLock   | t       | t        | 14040 | postgres | 8345 |            |    16441 | terraform | Terraform provider |             |                 |          -1 | 2021-06-30 17:40:35.42992+00  | 2021-06-30 17:40:35.47188+00  | 2021-06-30 17:40:35.552341+00 | 2021-06-30 17:40:35.552367+00 | Client          | ClientRead | idle in transaction |             |         9850 | SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1 | client backend
 relation   |        0 |     2965 |      |       |            |               |         |       |          | 6/9483             | 8345 | AccessShareLock | t       | f        | 14040 | postgres | 8345 |            |    16441 | terraform | Terraform provider |             |                 |          -1 | 2021-06-30 17:40:35.42992+00  | 2021-06-30 17:40:35.47188+00  | 2021-06-30 17:40:35.552341+00 | 2021-06-30 17:40:35.552367+00 | Client          | ClientRead | idle in transaction |             |         9850 | SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1 | client backend
 relation   |        0 |     1261 |      |       |            |               |         |       |          | 6/9483             | 8345 | AccessShareLock | t       | f        | 14040 | postgres | 8345 |            |    16441 | terraform | Terraform provider |             |                 |          -1 | 2021-06-30 17:40:35.42992+00  | 2021-06-30 17:40:35.47188+00  | 2021-06-30 17:40:35.552341+00 | 2021-06-30 17:40:35.552367+00 | Client          | ClientRead | idle in transaction |             |         9850 | SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1 | client backend
 relation   |        0 |     2676 |      |       |            |               |         |       |          | 5/9186             | 8375 | AccessShareLock | t       | f        | 16547 | test     | 8375 |            |    16441 | terraform | psql               |             |                 |          -1 | 2021-06-30 17:41:57.416802+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479557+00 |                 |            | active              |             |         9866 | SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;                                               | client backend
 relation   |        0 |     1260 |      |       |            |               |         |       |          | 6/9483             | 8345 | AccessShareLock | t       | f        | 14040 | postgres | 8345 |            |    16441 | terraform | Terraform provider |             |                 |          -1 | 2021-06-30 17:40:35.42992+00  | 2021-06-30 17:40:35.47188+00  | 2021-06-30 17:40:35.552341+00 | 2021-06-30 17:40:35.552367+00 | Client          | ClientRead | idle in transaction |             |         9850 | SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1 | client backend
 relation   |        0 |     2672 |      |       |            |               |         |       |          | 5/9186             | 8375 | AccessShareLock | t       | f        | 16547 | test     | 8375 |            |    16441 | terraform | psql               |             |                 |          -1 | 2021-06-30 17:41:57.416802+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479557+00 |                 |            | active              |             |         9866 | SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;                                               | client backend
 advisory   |    14040 |          |      |       |            |               |       0 | 16441 |        1 | 6/9483             | 8345 | ExclusiveLock   | t       | f        | 14040 | postgres | 8345 |            |    16441 | terraform | Terraform provider |             |                 |          -1 | 2021-06-30 17:40:35.42992+00  | 2021-06-30 17:40:35.47188+00  | 2021-06-30 17:40:35.552341+00 | 2021-06-30 17:40:35.552367+00 | Client          | ClientRead | idle in transaction |             |         9850 | SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1 | client backend
 relation   |        0 |     2695 |      |       |            |               |         |       |          | 6/9483             | 8345 | AccessShareLock | t       | f        | 14040 | postgres | 8345 |            |    16441 | terraform | Terraform provider |             |                 |          -1 | 2021-06-30 17:40:35.42992+00  | 2021-06-30 17:40:35.47188+00  | 2021-06-30 17:40:35.552341+00 | 2021-06-30 17:40:35.552367+00 | Client          | ClientRead | idle in transaction |             |         9850 | SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1 | client backend
 relation   |        0 |     2677 |      |       |            |               |         |       |          | 5/9186             | 8375 | AccessShareLock | t       | f        | 16547 | test     | 8375 |            |    16441 | terraform | psql               |             |                 |          -1 | 2021-06-30 17:41:57.416802+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479557+00 |                 |            | active              |             |         9866 | SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;                                               | client backend
 relation   |        0 |     2676 |      |       |            |               |         |       |          | 6/9483             | 8345 | AccessShareLock | t       | f        | 14040 | postgres | 8345 |            |    16441 | terraform | Terraform provider |             |                 |          -1 | 2021-06-30 17:40:35.42992+00  | 2021-06-30 17:40:35.47188+00  | 2021-06-30 17:40:35.552341+00 | 2021-06-30 17:40:35.552367+00 | Client          | ClientRead | idle in transaction |             |         9850 | SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1 | client backend
 relation   |        0 |     1262 |      |       |            |               |         |       |          | 5/9186             | 8375 | AccessShareLock | t       | f        | 16547 | test     | 8375 |            |    16441 | terraform | psql               |             |                 |          -1 | 2021-06-30 17:41:57.416802+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479557+00 |                 |            | active              |             |         9866 | SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;                                               | client backend
 relation   |        0 |     2694 |      |       |            |               |         |       |          | 6/9483             | 8345 | AccessShareLock | t       | f        | 14040 | postgres | 8345 |            |    16441 | terraform | Terraform provider |             |                 |          -1 | 2021-06-30 17:40:35.42992+00  | 2021-06-30 17:40:35.47188+00  | 2021-06-30 17:40:35.552341+00 | 2021-06-30 17:40:35.552367+00 | Client          | ClientRead | idle in transaction |             |         9850 | SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1 | client backend
 relation   |        0 |     1260 |      |       |            |               |         |       |          | 5/9186             | 8375 | AccessShareLock | t       | f        | 16547 | test     | 8375 |            |    16441 | terraform | psql               |             |                 |          -1 | 2021-06-30 17:41:57.416802+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479557+00 |                 |            | active              |             |         9866 | SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;                                               | client backend
 relation   |        0 |     2671 |      |       |            |               |         |       |          | 5/9186             | 8375 | AccessShareLock | t       | f        | 16547 | test     | 8375 |            |    16441 | terraform | psql               |             |                 |          -1 | 2021-06-30 17:41:57.416802+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479555+00 | 2021-06-30 17:41:57.479557+00 |                 |            | active              |             |         9866 | SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;                                               | client backend
 relation   |        0 |     2964 |      |       |            |               |         |       |          | 6/9483             | 8345 | AccessShareLock | t       | f        | 14040 | postgres | 8345 |            |    16441 | terraform | Terraform provider |             |                 |          -1 | 2021-06-30 17:40:35.42992+00  | 2021-06-30 17:40:35.47188+00  | 2021-06-30 17:40:35.552341+00 | 2021-06-30 17:40:35.552367+00 | Client          | ClientRead | idle in transaction |             |         9850 | SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1 | client backend
 relation   |        0 |     2677 |      |       |            |               |         |       |          | 6/9483             | 8345 | AccessShareLock | t       | f        | 14040 | postgres | 8345 |            |    16441 | terraform | Terraform provider |             |                 |          -1 | 2021-06-30 17:40:35.42992+00  | 2021-06-30 17:40:35.47188+00  | 2021-06-30 17:40:35.552341+00 | 2021-06-30 17:40:35.552367+00 | Client          | ClientRead | idle in transaction |             |         9850 | SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1 | client backend
(20 rows)

Thank you for your work !

[jan-mrm]

It seems like that we are experiencing the same issue. Sometimes the deletion never finishes and sometimes it does finish within some minutes.
We could prevent the issue by removing the max_connections = 1 from the provider config. A bigger value than 1 might do the trick as well, if there is no reason for it to be 1.

[woshilapin]

Not sure that it is the same instance of the same issue, but we are observing a very similar problem on a different situation.

We have a postgresql_database resource that has already been deployed via Terraform. For migration reasons, we manually drop the DB (meaning, the Terraform state doesn't know the resource is missing at this point).

Then we do a terraform apply to recreate all the PostgreSQL resources properly (clean slate). It seems that this apply works once in a while (we also observed 2 to 3 retry before it works). Note that we do also have a max_connections = 1 (when removed, we did not notice the problem anymore... but we need it for other reasons).

[woshilapin]

Some information about how we implement a workaround. First, some context.

Context

We have multiple grant to deploy and we did trigger #178. The solution was to add max_connections = 1 which in turn trigger the current bug (#98). And a workaround for the current bug is to remove max_connections = 1.

Workaround

The workaround is to use 2 different Terraform providers postgresql, something along these lines.

 provider "postgresql" {
   scheme           = "postgres"
   host             = "http://localhost:5432"
   username         = "postgres"
   password         = "postgres"
   # Force sequential creation since 'postgresql' provider
   # does not handle correctly concurrency calls to the DB
   # https://github.com/cyrilgdn/terraform-provider-postgresql/issues/178
   max_connections = 1
 }

 provider "postgresql" {
   alias            = "database"
   scheme           = "postgres"
   host             = "http://localhost:5432"
   username         = "postgres"
   password         = "postgres"
   # Do not put 'max_connections = 1` for database because of the following bug
   # https://github.com/cyrilgdn/terraform-provider-postgresql/issues/98
   # This is the only reason of existence of this provider,
   # used for creating the only 'postgresql_database' resource (see alias).
   # max_connections = 1
 }
resource "random_password" "pg" {
  length  = 32
  special = false
}

resource "postgresql_role" "me" {
  name            = "me"
  login           = true
  password        = random_password.pg.result
}

resource "postgresql_database" "mydb" {
  # Here, we force the use of the second provider, only for this resource
  provider   = postgresql.database
  owner      = postgresql_role.me.name
  name       = "mydb"
}

resource "postgresql_grant" "database" {
  database    = postgresql_database.mydb.name
  role        = postgresql_role.me.name
  object_type = "database"
  privileges = [
    "CONNECT",
    "CREATE",
  ]
}

resource "postgresql_grant" "schema" {
  database    = postgresql_database.mydb.name
  role        = postgresql_role.me.name
  schema      = "public"
  object_type = "schema"
  privileges = [
    "CREATE",
    "USAGE",
  ]
}

resource "postgresql_grant" "table" {
  database    = postgresql_database.mydb.name
  role        = postgresql_role.me.name
  schema      = "public"
  object_type = "table"
  privileges = [
    "DELETE",
    "INSERT",
    "REFERENCES",
    "SELECT",
    "TRUNCATE",
    "UPDATE",
  ]
}

Hope this helps.