I'm trying to compare schemas between 2 (identical) databases using pg_dump --schema-only
in PostgreSQL 9.3.9, but the diff of the output shows the ordering is inconsistent for two triggers:
2896c2896
< CREATE TRIGGER audit_trigger_row AFTER INSERT OR DELETE OR UPDATE ON model_run FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func('true');
---
> CREATE TRIGGER audit_trigger_row AFTER INSERT OR DELETE OR UPDATE ON model FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func('true');
2900c2900
< CREATE TRIGGER audit_trigger_row AFTER INSERT OR DELETE OR UPDATE ON model FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func('true');
---
> CREATE TRIGGER audit_trigger_row AFTER INSERT OR DELETE OR UPDATE ON model_run FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func('true');
It seems the ordering is getting mixed up between model
and model_run
because of the underscore character. A previous question/answer here seems to indicate the collate
value may be the culprit. A post on nabble.com makes me think this is a somewhat-known issue.
- Does the
collate
value of the DB affect pg_dump's sorting of the schema output (ie, not just the data)? - If I re-create the DBs using
lc_collate='C'
will that force a standard ordering for non-alphanumeric characters?
Below is the current list of the 2 databases:
gcis=# \l+ gcis_21*
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
--------------+----------+----------+-------------+-------------+-------------------+-------+------------+-------------
gcis_2180 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 35 MB | pg_default |
gcis_2190 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 35 MB | pg_default |
Footnote: The schema comparison is actually being done automatically on the fly through Module::Build::Database from CPAN. The full command being used is
pg_dump -xOs -E utf8 -n $database_schema $database_name
Though utf8
is explicitly passed as the encoding here, the database itself
is also currently UTF8.
pg_dump
should be usingCOLLATE "C"
in queries. What's the collation of each database? Please show output of\l+ thedatabasename
inpsql
for both.\l+ thedatabasename
output you suggested.