CREATE EXTENSION statements in the change script

[disssid]

OS - Linux Postgres - 12.7 Extensions - timescaledb dbtoyaml, yamltodb - v0.9.1

When creating change scripts using yamltodb, it generates CREATE EXTENSION statements in the script even though nothing is changed related to them. The rest of the script is fine.

Here is the script

CREATE EXTENSION plpgsql
 VERSION '1.0';

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

CREATE EXTENSION timescaledb
 VERSION '2.3.0';

COMMENT ON EXTENSION timescaledb IS 'Enables scalable inserts and complex queries for time-series data';

-- Expected in the script
ALTER TABLE public.bug DROP CONSTRAINT bug_pkey;

DROP TABLE public.bug;

Please let me know if you need any further information.

Thank you for the help.

[jmafc]

This may be related to #218 or #209 or maybe it's just a side effect of the YAML input file (which I assume was created using dbtoyaml) not having a complete snapshot of the original database catalogs (see #185). Is this extra extension stuff always present, i.e., if you, say, create a new table in the source database, run dbtoyaml and run yamltodb again against the target, does it generate the CREATE EXTENSION statements again, even though the target database presumably already has the timescaledb extension? Furthermore, is the timescaledb extension present in the YAML input file, and if it isn't (and it was created by dbtoyaml) then the question (or problem) is in dbtoyaml, not in yamltodb.

[disssid]

Yes the YAML input file(s) was created using dbtoyaml. Apart from the metadata/schema.public/<table_names>, we have extensions.yaml which has the extensions.

Is this extra extension stuff always present, i.e., if you, say, create a new table in the source database, run dbtoyaml and run yamltodb again against the target, does it generate the CREATE EXTENSION statements again, even though the target database presumably already has the timescaledb extension?

Yes the extension is already present and when we do dbtoyaml it generates the extensions.yaml with the extensions and when we do yamltodb it generates the CREATE EXTENSIONS statements again.

[jmafc]

I guess the first thing to try would be to see if this happens with another extension. We have some tests that check extensions (pg_trm, for example). If it doesn't happen with pg_trm or another one of the contributed extensions, then of course, we'd have to investigate what's different about timescaledb.

[disssid]

We have plpgsql as well and the CREATE EXTENSION statements for that are generated as well despite being exported in extensions.yaml.

One thing I noticed was that plpgsql is part of pg_catalog schema, where as timescaledb is part of public schema. When using dbtoyaml we did mention schema explicitly to public. So, I would've understood why plpgsql showed up in the scripts but not sure why timescaledb did.

Does owner of the extension and the user used for dbtoyaml/yamltodb have to do anything with this issue?

[jmafc]

plpgsql and other languages are special because they were originally not extensions.

The owner/user may affect the handling of extensions. Is the owner of your timescaledb extension postgres or some other user?