Possible regression on resource postgresql_function, suddenly causing errors related to database not found.

[dmaspataud]

Hi there,

Thank you for opening an issue. Please provide the following information:

Terraform Version

Terraform v1.4.2 on darwin_amd64

• provider registry.terraform.io/cyrilgdn/postgresql v1.19.0
• provider registry.terraform.io/hashicorp/aws v4.59.0
• provider registry.terraform.io/hashicorp/random v3.4.3

Affected Resource(s)

postgresql_function as far as I can tell.

Terraform Configuration Files

Abridged version of our terraform files containing only the impacted resource (function).

variable "datadog_user" {
  type        = string
  description = "The user used by Datadog checks to log onto AWS RDS instance for PostgreSQL monitoring."
  default     = "datadog"
}

variable "database" {
  type        = string
  description = "The name of the default database."
  default     = "postgres"
}

resource "postgresql_schema" "this" {
  name     = var.datadog_user
  database = var.database
  owner    = var.datadog_user
}

resource "postgresql_function" "datadog_explain_statement" {
  database = var.database
  schema   = postgresql_schema.this.name
  name     = "datadog.explain_statement"
  arg {
    name = "l_query"
    type = "TEXT"
  }

  returns = "SETOF JSON "
  body    = file("${path.module}/function.sql")
}

function.sql

AS 
$$
DECLARE
curs REFCURSOR;
plan JSON;

BEGIN
   OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query);
   FETCH curs INTO plan;
   CLOSE curs;
   RETURN QUERY SELECT plan;
END;
$$
LANGUAGE 'plpgsql'
RETURNS NULL ON NULL INPUT
SECURITY DEFINER;

Expected Behavior

This stack was already applied, so I expected

No changes. Your infrastructure matches the configuration.

Actual Behavior

Planning failed. Terraform encountered an error while generating this plan.

╷
│ Error: error detecting capabilities: error PostgreSQL version: pq: database ""datadog"" does not exist
│
│   with module.rds_user_datadog[0].postgresql_function.datadog_explain_statement,
│   on .terraform/modules/rds_user_datadog/main.tf line 52, in resource "postgresql_function" "datadog_explain_statement":
│   52: resource "postgresql_function" "datadog_explain_statement" {
│

Steps to Reproduce

1. terraform apply should do the trick.

Important Factoids

The issue disappeared once I forced the use of version 1.18 instead of 1.19:

postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "1.18"
}

instead of :

postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "~> 1.18"
}

References

Could be related to this MR:

https://github.com/cyrilgdn/terraform-provider-postgresql/pull/275/

[dmaspataud]

The issue seems to come from the fact that in 1.18, you could create Postgres function with a . in the name. By doing so, it was then stored in the tfstate as such:

"id": "\"datadog\".\"datadog.explain_statement\"(TEXT)",

id: <schema>.<function name>

Version 1.19 introduced a new parsing function to enable resource importing, under the form:

id: <database>.<schema>.<function name>

However, if one of your function contained a ., it will be misinterpreted.

In my case, to refresh its state, it tries to get :

function explain_statement in schema datadog in database datadog, when the function exists as datadog. explain_statement in schema datadog in database postgres.

For the sake of argument, note that the function should have been created as explain_statement in schema datadog in database postgres, it was a mistake on my side, but the point still stands, if anyone had function with a . in the function name, this change would be a breaking change.

[soudaburger]

I had to remove the offending datadog.explain_statement from state. Then import with database:postgres, schema:datadog, name:explain_statement and it appears to be happy now.

[kylejohnson]

Not clear to me - Since datadog expects the name of the function to be datadog.explain_statement, but that format (with the .) is not compatible with 1.19, is there a workaround?

[kylejohnson]

I just noticed that datadog has a way to override the function name: explain_function. For now I think I'll leave the function name as datadog_explain_statement, and just update that option in my datadog config.

[kylefuhrmanncalm]

The issue seems to come from the fact that in 1.18, you could create Postgres function with a . in the name. By doing so, it was then stored in the tfstate as such:

"id": "\"datadog\".\"datadog.explain_statement\"(TEXT)",

id: <schema>.<function name>

Version 1.19 introduced a new parsing function to enable resource importing, under the form:

id: <database>.<schema>.<function name>

However, if one of your function contained a ., it will be misinterpreted.

In my case, to refresh its state, it tries to get :

function explain_statement in schema datadog in database datadog, when the function exists as datadog. explain_statement in schema datadog in database postgres.

For the sake of argument, note that the function should have been created as explain_statement in schema datadog in database postgres, it was a mistake on my side, but the point still stands, if anyone had function with a . in the function name, this change would be a breaking change.

This was helpful. We were running into an issue where Terraform was trying to create new function resources even though they already existed causing it to fail out. Ended up modifying state and updating the id to prepend the database name (default: postgres). Note: If you try to push the state back up with the escaped quotes, it will end up failing. You'd want to use: "dbname.datadog.explain_statement(TEXT)", instead of "\"dbname\".\"datadog.explain_statement\"(TEXT)", following the example above of database.schema.function_name