Why Atlas doesn't detect PostgreSQL event_trigger objects when the search_path parameter is set
Why does Atlas not detect PostgreSQL event_trigger
objects when using --dev-url
with search_path=public
?
Answer
When running Atlas against a PostgreSQL database with a dev-URL that includes search_path=public
, you may notice that event_trigger
objects are not included in the generated migration. This happens because:
- The
search_path
parameter scopes Atlas to inspect only a specific schema event_trigger
objects (like extensions) are database-level objects, not schema-scoped- As a result, they are excluded when Atlas inspects only within a schema scope
Solution
Remove the search_path
parameter from the --dev-url
to ensure Atlas inspects at the database level. Here are examples of both problematic and correct configurations:
- Correct (database-scoped)
- Problematic (schema-scoped)
# This will detect event_trigger objects
atlas migrate diff --dev-url "docker://postgres/16/dev"
# This will NOT detect event_trigger objects
atlas migrate diff --dev-url "docker://postgres/16/dev?search_path=public"
Configuration File Example
The same principle applies when using an atlas.hcl
configuration file:
- Correct
- Problematic
env "local" {
dev = "docker://postgres/16/dev"
// ... other configuration
}
env "local" {
dev = "docker://postgres/16/dev?search_path=public"
// ... other configuration
}
Why This Matters
Event triggers are powerful PostgreSQL features that execute functions in response to DDL events. For example:
-- Create an event trigger that logs table creation
CREATE OR REPLACE FUNCTION record_table_creation()
RETURNS event_trigger AS $$
BEGIN
RAISE NOTICE 'Table created: %', tg_tag;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER record_table_creation
ON ddl_command_start
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION record_table_creation();
When these objects exist in your database but are not detected by Atlas due to schema scoping, your migration plans will be incomplete, potentially leading to inconsistencies between environments.
Related Information
For more details about database-level vs schema-level objects in PostgreSQL with Atlas, see:
- PostgreSQL Extensions FAQ - Extensions have the same database-level scoping behavior
- Database URL concepts - Understanding URL parameters and their effects