azurerm_postgresql_flexible_server_database collaction valiation error

[lupa95]

Is there an existing issue for this?

• I have searched the existing issues

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment and review the contribution guide to help.

Terraform Version

1.5.4

AzureRM Provider Version

3.69.0

Affected Resource(s)/Data Source(s)

azurerm_postgresql_flexible_server_database

Terraform Configuration Files

resource "azurerm_postgresql_flexible_server_database" "example" {
  name      = "mydb"
  server_id = azurerm_postgresql_flexible_server.example.id
  collation = "de_DE.UTF8"
  charset   = "UTF8"
}

Debug Output/Panic Output

│ Error: collation contains unknown collation de_DE.UTF8
│ 
│   with azurerm_postgresql_flexible_server_database.wis_db,
│   on databases.tf line 4, in resource "azurerm_postgresql_flexible_server_database" "wis_db":
│    4:   collation = "de_DE.UTF8"
│

Expected Behaviour

The provider version 3.68.0 allowed the collation de_DE.UTF8, but after upgrading to 3.69.0 a validation error occurs.

Updating the collation to de_DE.utf8 fixes the validation error, but forces a replacement on the resource, which is not a viable thing to do for a database.

The provider should accept the collation de_DE.UTF8 to be backwards compatible.

Actual Behaviour

Provider does not accept collation de_DE.UTF8.

Steps to Reproduce

1. run terraform validate --> validation error
2. Replace de_DE.UTF8 with de_DE.utf8 in the configuration
3. Run terraform plan --> shows force replacement on database resource

Important Factoids

No response

References

There seem to be more people with similar issues, after the validation got changed. #22689

[neil-yechenwei]

Thanks for raising this issue. I assume it's expected behavior since "de_DE.utf8" is valid value. Suggest to update it to "de_DE.utf8" in tf config. Thanks.

Doc Reference: https://www.postgresql.org/docs/current/collation.html

[lupa95]

You are right, that "de_DE.utf8" is indeed the correct value. However, the older provider version also allowed "de_DE.UTF8" and that's what I have used to deploy the infrastructure. If I update the value to lower case, terraform will try to replace the resource because it thinks the collation changes.

# azurerm_postgresql_flexible_server_database.example must be replaced
-/+ resource "azurerm_postgresql_flexible_server_database" "example" {
      ~ collation = "de_DE.UTF8" -> "de_DE.utf8" # forces replacement
      ~ id        = "/subscriptions/XXX/resourceGroups/XXX/providers/Microsoft.DBforPostgreSQL/flexibleServers/XXX/databases/XXX" -> (known after apply)
        name      = "example"
        # (2 unchanged attributes hidden)
    }

Since we are talking about a database resource here, I can't just do that.

[tombuildsstuff]

Duplicate of #22907 which was fixed by #22928 which reworks this validation / will ship in this weeks release - but you should be able to workaround this in the interim either using ignore_changes on this field or by pinning to version 3.68.0 until 3.70.0 is available.

[lupa95]

I am sorry, but 3.70.0 didn't fix the issue. Terraform still does not honor the previously valid collation with upper case.

Is using lifecycle ignore_changes the only fix for this?

[t3pco]

@tombuildsstuff
sadly still not working with 3.70.0+ - still wants to replace the db.

terraform plan outout:

Installed hashicorp/azurerm v3.71.0 (signed by HashiCorp)
...
-/+ resource "azurerm_postgresql_flexible_server_database" "db" {
~ charset = "UTF8" -> "utf8"
~ collation = "en_US.UTF8" -> "en_US.utf8" # forces replacement
~ id = "/subscriptions/..." -> (known after apply)
name = "..."
# (1 unchanged attribute hidden)
}`

[tadeusz-delphai]

@tombuildsstuff

I can reproduce this with azurerm 3.75.0. My database was originally created with version 3.46.0. Terraform plans to recreate the database from scratch to fix the collation en_US.UTF8 -> en_US.utf8, and claims that en_US.UTF8 is no longer a valid collation name.

The database exists with the collation en_US.UTF8

$> az postgres flexible-server db list --server-name […] --resource-group […]
[
    {…},
    {
    "charset": "UTF8",
    "collation": "en_US.UTF8",
    "id": "/subscriptions/…",
    "name": "example_db",
    "resourceGroup": "example_rg",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/databases"
  }
]

Internally, the PostgreSQL engine uses the lower-case utf8.

example_db=> select * from pg_collation where lower(collname)='en_us.utf8';
  oid  |  collname  | collnamespace | collowner | collprovider | collisdeterministic | collencoding | collcollate | collctype  | collversion 
-------+------------+---------------+-----------+--------------+---------------------+--------------+-------------+------------+-------------
 12485 | en_US.utf8 |            11 |        10 | c            | t                   |            6 | en_US.utf8  | en_US.utf8 | 2.27
(1 row)

As an attempted unsuccessful workaround I tried to create an upper-case copy of that collation

example_db=> create collation "en_US.UTF8" from "en_US.utf8";
CREATE COLLATION

example_db=> select * from pg_collation where lower(collname)='en_us.utf8';
  oid  |  collname  | collnamespace | collowner | collprovider | collisdeterministic | collencoding | collcollate | collctype  | collversion 
-------+------------+---------------+-----------+--------------+---------------------+--------------+-------------+------------+-------------
 12485 | en_US.utf8 |            11 |        10 | c            | t                   |            6 | en_US.utf8  | en_US.utf8 | 2.27
 24881 | en_US.UTF8 |          2200 |     24822 | c            | t                   |            6 | en_US.utf8  | en_US.utf8 | 2.27
(2 rows)

but that didn't help either. The provider validates the collation name on its own without checking which collations actually do exist in the pg_collation table.

[thomeu]

@tadeusz-delphai Did you manage to update version without recreating the database? thanks

[tadeusz-delphai]

@tadeusz-delphai Did you manage to update version without recreating the database? thanks

@thomeu
I just tested the azurerm provider version 3.85.0.

No change in behavior since the collation name validator was changed in 3.69.0. The upper-case collation name en_US.UTF8 crashes with an error collation contains unknown collation as described above, switching to a lower-case en_US.utf8 wants to force-recreate the PostgreSQL resource, which is a very invasive operation. The attempted workaround of manually creating different collation objects inside the PostgreSQL cluster didn't make any difference.

For now, I just pin a lower version of the azurerm provider.

[green-munkey]

I have also ran into this issue with 3.96.0 when migrating from 3.65.0. In our situation, we can't move to another clean DB. I have also tried using the ignore_changes setting without any luck, so I am checking for any advice on how to move forward in ignoring this setting as we have other components we are trying to fix with the later version of Terraform, so we are stuck in a pickle.

collation = "en_US.UTF8"
charset = "UTF8"

lifecycle {
prevent_destroy = true
ignore_changes = [
collation,
charset
]
}

[green-munkey]

I have also done the following actions and have yet to be successful.

1. remove azurerm_postgresql_flexible_server_database from the state using terraform state rm
2. then re-import the resource using terraform import

3. then, tried to add a new resource to verify the terraform plan command with the same problem

[github-actions]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.
If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.