"tuple concurrently updated" error on concurrent GRANT statements #178

Bhashit · 2022-02-02T22:44:11Z

Terraform Version

1.1.1

Affected Resource(s)

postgresql_grant

Terraform Configuration Files

provider "postgresql" {
  host             = var.postgres_host
  port             = var.postgres_port
  username         = var.root_user_name
  password         = var.root_user_password
  expected_version = "12.3"
  superuser        = false
}

resource "postgresql_grant" "connect_db" {
  database    = postgresql_database.db.name
  object_type = "database"
  privileges  = ["CREATE", "CONNECT"]
  role        = postgresql_role.svc_admin.name
}

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

Panic Output

╷
│ Error: could not execute revoke query: pq: tuple concurrently updated
│ 
│   with module.svc.postgresql_grant.use_schema,
│   on .terraform/modules/svc/main.tf line 118, in resource "postgresql_grant" "use_schema":
│  118: resource "postgresql_grant" "use_schema" {
│ 
╵

Expected Behavior

Multiple GRANT statements should get executed correctly.

Actual Behavior

terraform apply fails intermittently when multiple GRANT statements are involved.

Steps to Reproduce

terraform apply with multiple grant statements. You can also try a large number of statements with a for_each to make it more likely that the error will happen.

Important Factoids

Found this threads on postgres/terraform mailing lists:

The "solution" seems to be to run things sequentially. However, ideally, we should be able to handle this at the provider level. For ex. by either locking the table appropriately, or by retrying after a backoff period perhaps before failing.

One interesting thing that happened was that with my terraform apply, when TF exited, it didn't save the state. So, it created some resources, but they weren't tracked in the state. That could be a Terraform bug, but I thought I should at least mention it here.

The text was updated successfully, but these errors were encountered:

JamesTimms · 2022-03-03T11:57:24Z

I think this is a similar problem to the issue/fix in #169

fabiopaiva · 2022-03-14T21:19:30Z

I'm having the same issue, even after upgrading to 1.15 and configuring parallelism=0

phclark · 2022-03-23T15:35:42Z

We were able to fix the issue by setting TF_CLI_ARGS_apply="-parallelism=1" (on provider version 1.15.0), but this certainly isn't ideal. I would love a fix as described above.

iskarbnik · 2022-03-27T09:37:41Z

same issue, using provider version 1.15.0

ConorWyse · 2022-04-19T11:40:12Z

We are experiencing the same problem even after upgrading to v1.15.0. We loop through a list of 3 items, create a Postgres user for each, and grant connect rights to those users. It fails consistently in the grant section.

postgresql_role.user["example"]: Creating...
postgresql_role.user["example_read"]: Creating...
postgresql_role.user["example_emergency"]: Creating...
postgresql_role.user["example_read"]: Creation complete after 1s [id=example_read]
postgresql_role.user["example"]: Creation complete after 1s [id=example]
postgresql_role.user["example_emergency"]: Creation complete after 1s [id=example_emergency]
postgresql_grant.user_db_connect_grants["example_emergency"]: Creating...
postgresql_grant.user_db_connect_grants["example"]: Creating...
postgresql_grant.user_db_connect_grants["example_read"]: Creating...
postgresql_grant.user_db_connect_grants["example"]: Creation complete after 1s [id=example_example_database]
postgresql_grant.user_db_connect_grants["example_read"]: Creation complete after 2s [id=example_read_example_database]
│ 
│ Error: could not execute revoke query: pq: tuple concurrently updated
│ 
│   with postgresql_grant.user_db_connect_grants["example_emergency"],
│   on resources.tf line 106, in resource "postgresql_grant" "user_db_connect_grants":
│  106: resource "postgresql_grant" "user_db_connect_grants" {

Terraform version 1.1.8

Update:
Using the argument -parallelism=1 seems to have solved the problem.

red8888 · 2022-05-02T15:32:26Z

bump. I have many resources in additional to postgres grants in the same workspace. I dont want to have to set -parallelism=1

philip-harvey · 2022-07-25T20:23:51Z

This really needs to be fixed, -parallelism=1 makes Terraform runs take hours....

marcneander · 2022-09-12T15:03:22Z

Would love to see this fixed. A retry approach for this specific error would be sweet!

dpolivaev · 2022-09-13T15:00:44Z

Using postgresql provider parameter max_connections = 1 seems to help as a workaround.
But a real fix would be highly appreciated.

philip-harvey · 2022-12-22T17:30:07Z

The "fix" in #224 has broken the max_connections = 1 setting, so I'm having to pin to provider version 1.1.7

debu99 · 2023-02-17T13:40:23Z

still have this issue in 1.18

philip-harvey · 2023-04-17T16:13:31Z

Any chance the change in #224 can be rolled back? In the past we could set max_connections = 1 and it would work, now it's just totally broken for versions > 1.1.7

kylejohnson · 2023-09-20T19:12:03Z

Hey everyone - I think I have a working fix in #352.

reddragond · 2023-10-06T14:54:37Z

As a workaround you can use -parallelism=1 in your terraform apply/destroy commands. It will run very slowly but it avoids this issue until a fix can be implemented. I've been using it with 1.21.0 for a while now with no issues.

* Using terratest just wasn't working out - it isn't designed for testing a provider * github.com/hashicorp/terraform-plugin-sdk/v2/helper/resource looks like the right option but I don't have the time to learn it right now.

kylejohnson · 2023-11-01T19:56:49Z

Hi everyone,

I went ahead and released a beta version which targets the tuple-concurrently-updated branch used in #352.

Using this beta release, the tuple concurrently updated error has been fixed 100% of the time in my internal usage at $dayjob.

I'd appreciate it if anyone else could test and confirm these results as well.

terraform {
  required_providers {
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "1.21.1-beta.1"
    }
  }
}

kylejohnson · 2023-11-01T21:16:28Z

Oops, didn't mean to close.

rudl002 · 2023-11-28T16:30:57Z

One datapoint: our internal TF module, which creates a DB and executes several GRANTs did just run perfectly on the first try with the version "1.21.1-beta.1" 👍

Previously, the first run was almost guaranteed to trigger the "tuple concurrently updated" condition. We always needed at least 2 (sometimes 3) attempts to run to completion.

Thanks a lot for fixing this!

debu99 · 2023-11-29T01:23:27Z

Really appreciate the work👍

this prevents errors like: could not execute revoke query: pq: tuple concurrently updated error see cyrilgdn/terraform-provider-postgresql#178

joaocc · 2023-12-12T04:27:28Z

Hi. We still face this issue with 1.21.1-beta.1.
Not only that, but when we try to consolidate postgresql_grant and postgresql_grant_role resources into 2 big ones (merging dicts), we get something different:

Error: could not get advisory lock for members of role ROLE_NAME: pq: deadlock detected
...
...
...
Error: pq: grant options cannot be granted back to your own grantor
...
resource "postgresql_grant" "all" {

rmhw · 2024-03-27T12:35:23Z

I'm also facing this issue in 1.22.0.
In case it's relevant, I'm creating grants using a for_each (as below).

resource "postgresql_grant" "grant_all_on_tables" {
  for_each    = toset(var.full_access_users)

  database    = postgresql_database.main.name
  role        = each.value
  schema      = "public"
  object_type = "table"
  privileges  = ["DELETE","INSERT","REFERENCES","SELECT","TRIGGER","TRUNCATE","UPDATE"]
}

nabutbul · 2024-06-20T09:38:02Z

Maybe the owner can merge the branch "1.21.1-beta.1" to master?
It fix the issue with tuple concurrently update and it's not part of 1.22.0

m-barthelemy · 2024-08-09T04:53:17Z

It seems to have fixed our previous issues as well, we've been using this 1.21.1-beta.1 version for the past 3 months.

awoimbee mentioned this issue Apr 6, 2022

Too much parrallelism creates issues with Grant: tuple concurrently updated pulumi/pulumi-postgresql#126

Closed

jsteinich mentioned this issue Apr 13, 2022

Issue with @cdktf/provider-postgresql Grant (Add terraform-parallelism flag) hashicorp/terraform-cdk#1715

Closed

timothegenzmer mentioned this issue Jun 23, 2022

tuple concurrently updated error when granting priviliges on same database for multiple users #223

Closed

woshilapin mentioned this issue Oct 12, 2022

Deadlock on destroy #98

Open

philip-harvey mentioned this issue Nov 30, 2022

Fix concurrency issue when granting privileges on databases #224

Merged

kylejohnson linked a pull request Sep 20, 2023 that will close this issue

Fix for Error: could not execute revoke query: pq: tuple concurrently updated #352

Open

kylejohnson added a commit that referenced this issue Sep 21, 2023

Add initial test coverage for #178

26d632a

kylejohnson closed this as completed Nov 1, 2023

kylejohnson reopened this Nov 1, 2023

rgl added a commit to rgl/terraform-postgres that referenced this issue Dec 10, 2023

upgrade to cyrilgdn/postgresql 1.21.1-beta.1

342b332

this prevents errors like: could not execute revoke query: pq: tuple concurrently updated error see cyrilgdn/terraform-provider-postgresql#178

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

"tuple concurrently updated" error on concurrent GRANT statements #178

"tuple concurrently updated" error on concurrent GRANT statements #178

Bhashit commented Feb 2, 2022

JamesTimms commented Mar 3, 2022

fabiopaiva commented Mar 14, 2022

phclark commented Mar 23, 2022

iskarbnik commented Mar 27, 2022

ConorWyse commented Apr 19, 2022 •

edited

Loading

red8888 commented May 2, 2022

philip-harvey commented Jul 25, 2022

marcneander commented Sep 12, 2022

dpolivaev commented Sep 13, 2022

philip-harvey commented Dec 22, 2022

debu99 commented Feb 17, 2023

philip-harvey commented Apr 17, 2023

kylejohnson commented Sep 20, 2023

reddragond commented Oct 6, 2023

kylejohnson commented Nov 1, 2023

kylejohnson commented Nov 1, 2023

rudl002 commented Nov 28, 2023

debu99 commented Nov 29, 2023

joaocc commented Dec 12, 2023

rmhw commented Mar 27, 2024

nabutbul commented Jun 20, 2024

m-barthelemy commented Aug 9, 2024

"tuple concurrently updated" error on concurrent GRANT statements #178

"tuple concurrently updated" error on concurrent GRANT statements #178

Comments

Bhashit commented Feb 2, 2022

Terraform Version

Affected Resource(s)

Terraform Configuration Files

Panic Output

Expected Behavior

Actual Behavior

Steps to Reproduce

Important Factoids

JamesTimms commented Mar 3, 2022

fabiopaiva commented Mar 14, 2022

phclark commented Mar 23, 2022

iskarbnik commented Mar 27, 2022

ConorWyse commented Apr 19, 2022 • edited Loading

red8888 commented May 2, 2022

philip-harvey commented Jul 25, 2022

marcneander commented Sep 12, 2022

dpolivaev commented Sep 13, 2022

philip-harvey commented Dec 22, 2022

debu99 commented Feb 17, 2023

philip-harvey commented Apr 17, 2023

kylejohnson commented Sep 20, 2023

reddragond commented Oct 6, 2023

kylejohnson commented Nov 1, 2023

kylejohnson commented Nov 1, 2023

rudl002 commented Nov 28, 2023

debu99 commented Nov 29, 2023

joaocc commented Dec 12, 2023

rmhw commented Mar 27, 2024

nabutbul commented Jun 20, 2024

m-barthelemy commented Aug 9, 2024

ConorWyse commented Apr 19, 2022 •

edited

Loading