3

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.

  • Huh, yeah. I think pg_dump should be using COLLATE "C" in queries. What's the collation of each database? Please show output of \l+ thedatabasename in psql for both. – Craig Ringer Dec 15 '15 at 5:17
  • @CraigRinger I was just looking back at this question, and realized I never pinged you back after I added the \l+ thedatabasename output you suggested. – Randall Aug 2 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.