Add event trigger resource management

[Fabianoshz]

I'm opening this as a draft in case anyone wants to give feedback or test the code.

This should allow us to manage event triggers in this provider, here's how the resource would look like:

resource "postgresql_event_trigger" "event_trigger" {
  name = "event_trigger_test"

  database = postgresql_database.database.name
  schema = postgresql_schema.schema.name

  function = postgresql_function.function.name
  on = "ddl_command_end"

  owner = "postgres"

  status = "enable"

  filter {
    variable = "TAG"
    values = [
      "CREATE TABLE",
      "ALTER TABLE",
    ]
  }
}

Closes #398

[Fabianoshz]

@cyrilgdn I've used this to add a few hundred event triggers where I work, our fork has a few more commits with some fixes of things that we found after opening the PR, I can push the fixes to this branch but it's not clear to me if you're interested in that, let me know either way.

[cyrilgdn]

Hi @Fabianoshz ,

Thanks for your work and for opening this PR,
I'm interested yes, I'm just lacking a bit of time to review it properly but will have a look as soon as possible 👍

[Fabianoshz]

Perfect, I take some time this week to push the other fixes we've added on our fork

[cyrilgdn]

Thanks again for you work, here are some comments. Basically:

• The ID used in setID needs to be based on database name / event name and not only event name
• The update function doesn't work well for some cases, I explained how to test it easily in the TestCase directly
• The Exists function can be removed
• A small bug in the read function for the owner
• and a few minor comments

let me know if you need help to fix that.

[cyrilgdn]

Does it work for enable_replica and enable_always ?
it seems to generate ALTER EVENT TRIGGER name enable_replica where the documentation says:

ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]

[cyrilgdn]

I actually checked with your test by adding an extra step like:

            {
                Config: fmt.Sprintf(testAccPostgreSQLEventTriggerConfig, dbName, schemas[0], "enable"),
                Check: resource.ComposeTestCheckFunc(
                    testAccCheckPostgresqlEventTriggerExists("postgresql_event_trigger.event_trigger", dbName),
                ),
            },
            {
                Config: fmt.Sprintf(testAccPostgreSQLEventTriggerConfig, dbName, schemas[0], "enable_always"),
                Check: resource.ComposeTestCheckFunc(
                    testAccCheckPostgresqlEventTriggerExists("postgresql_event_trigger.event_trigger", dbName),
                ),
            },

(and changing the conf definition to take %[3]s instead of "enabled" directly)
so it tries to alter the status of the event and it indeed fails with:

Error: pq: syntax error at or near "enable_always"

Full PG logs being:

ERROR:  syntax error at or near "enable_always" at character 34
STATEMENT:  ALTER EVENT TRIGGER "event_trigger_test" enable_always

[Fabianoshz]

I've added a map to fix this

[cyrilgdn]

You don't manage the renaming of an event properly, similar to the comment on status you can test easily by updating your test case with a new step that rename it, e.g.:

    {
        Config: fmt.Sprintf(testAccPostgreSQLEventTriggerConfig, dbName, schemas[0], "test_event"),
        Check: resource.ComposeTestCheckFunc(
            testAccCheckPostgresqlEventTriggerExists("postgresql_event_trigger.event_trigger", dbName),
        ),
    },
    {
        Config: fmt.Sprintf(testAccPostgreSQLEventTriggerConfig, dbName, schemas[0], "test_event_renamed"),
        Check: resource.ComposeTestCheckFunc(
            testAccCheckPostgresqlEventTriggerExists("postgresql_event_trigger.event_trigger", dbName),
        ),
    },

In this case, this test will fail with:

Error: pq: event trigger "test_event_renamed" does not exist

You can simply check here if the name changed with something like:

	if d.HasChange(eventTriggerNameAttr) {
		old, new := d.GetChange(eventTriggerNameAttr)
		if _, err := txn.Exec(
			fmt.Sprintf("ALTER EVENT TRIGGER %s RENAME TO %s", pq.QuoteIdentifier(old), pq.QuoteIdentifier(new)),
		); err != nil {
			return err
		}
	}

[Fabianoshz]

I've just added a rename, I've also added your step to the tests too.

[Fabianoshz]

@cyrilgdn thanks for throughout review, I will start working on fixing in the next few days

[Fabianoshz]

@cyrilgdn sorry for the delay, I've addressed your requests, let me know if you think I should change anything, I will also test this on my environment in the meantime.

[Fabianoshz]

I've just added a rename, I've also added your step to the tests too.

[Fabianoshz]

I've added a map to fix this

[Temurson]

My organization would also benefit from this feature! @Fabianoshz really appreciate your contribution here.
@cyrilgdn please let me know if I can help in any way to get this feature in!

[retrry]

This would be very nice to have in provider! It would benefit me too.

[zachary-povey]

@cyrilgdn - any chance this could get reviewed again? Happy to help wherever possible, at the minute without it we'd need to combine use of the provider with coupled manual DB management that we're hoping to avoid.

[n-badtke-cg]

If somebody is interested, I have built arm64 and amd64 binaries of the provider: https://github.com/n-badtke-cg/terraform-provider-postgresql/tree/build/postgresql_event_trigger/dist

you could download one of the binaries and use it from local in your tofu/terraform CLI with a .tofurc/.terraformrc config which is redirecting the pull source from internet to local file until this PR is getting merged :) See https://stackoverflow.com/a/73743106