remote-state/pg: Cannot lock workspace

[hashi825]

Terraform Version

Terrafrom v1.4.5
on linux_amd64

Terraform Configuration Files

Debug Output

Expected Behavior

When using the pg backend terraform should allow locking for states in different schemas without blocking.

Actual Behavior

Currently it appears if two users sharing the same databases but operating on projects in different schemas, terraform is unable to acquire the state lock when someone else is performing a plan/apply on an unrelated project in a different schema.

This can be reproduced by manually running acquiring the lock and running plan/apply

Steps to Reproduce

1. Acquire advisory lock for pg side for test

select try pg_try_advisory_lock(-1);
pg_try_advisory_lock | t

2. Run a plan

terraform plan

Error:  Error acquiring the state lock
Error message: Cannot lock workspace; already locked for workspace creation: default
Lock Info:
ID:        xxx
Path:      
Operation: OperationTypePlan
Who:       xxx
Version:   1.4.5
Created:  xxx
Info:      
Terraform acquires a state lock to protect the state from being written
by multiple users at the same time. Please resolve the issue above and try
again. For most commands, you can disable locking with the "-lock=false"
flag, but this is not recommended.

3. Unlock from pg side

select pg_advisory_unlock(-1);
pg_advisory_unlock | t

4. Terraform plan works successfully

Additional Context

Seems be related to https://github.com/hashicorp/terraform/blob/13cb0d03c7d6cca7b22966428c6032ab1ac4c574/internal/backend/remote-state/pg/client.go#LL92C110-L92C110 which tries to acquire a lock on both the states table id and (-1).

References

No response

[crw]

Just FYI, @remilapeyre

[naustra]

up

[FlawaCLV]

Same issue!

[jnielsendotnet]

I have a proposed fix for this issue in #35069 . Instead of using "-1" for the create lock (which makes it impossible for two different users to plan or apply a new state at the same time, even if they are completely independent), the create lock name is derived from the schema name.