bug: ERROR: syntax error at or near "TRIGGER"

[torreslucas13]

Describe the bug

Hey I'm trying to run the tool for the first time pointing to an external postgres database. Even after all the steps in the database-related documentation, I came across this error.

SQL Error [42601]: ERROR: syntax error at or near "TRIGGER"
  Position: 23

Error position: line: 1 pos: 22

This error happens when we the migration 20231002123723_notify_delete_config.up.sql is executed.

happens because of incorrect usage of double quotes (") around the trigger name in the CREATE TRIGGER statement.

This way works fine

CREATE FUNCTION notify_global_setting_delete()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify('notify_global_setting_change', OLD.name::text);
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_global_setting_delete
AFTER DELETE ON global_settings
FOR EACH ROW
EXECUTE FUNCTION notify_global_setting_delete();

To reproduce

1. Point the windmill to use an external db (https://www.windmill.dev/docs/advanced/self_host#use-an-external-database)
2. Try to up the service

Expected behavior

Migrations executed without error

Screenshots

No response

Browser information

No response

Application version

CE v1.491.5-17-g6e3613641

Additional Context

No response

[rubenfiszel]

@torreslucas13 what external postgres is it on and what version of postgres is it? That migration is valid for postgres 11+ and windmill require postgres 14+

[torreslucas13]

Hey @rubenfiszel thanks for the quick reply, PostgreSQL 13.18 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 9.5.0, 64-bit