Fixed: XML data and pg_dump

A few months ago I submitted a bug to the PostgreSQL hackers mailing list... It is already fixed thanks to Chapman Flack and others! While the official bug was recently submitted, users in the PostGIS and QGIS communities have been long-plagued by this issue. If adding the following line of code to your pg_dump file is familiar, an upgrade to PostgreSQL is in your near future!

SET XML OPTION DOCUMENT;

The QGIS documentation has covered this workaround for years, I found it documented at least back through 2014 (5 years ago). That workaround did work for most cases. Until last August when I was fed up by the requirement of using a workaround to save my public.layer_styles data. So, I went digging into the source code and documentation. That quest led me to find a bigger problem, namely the ability to create invalid backup files from pg_dump that the workaround can't even fix. While the bug was reported as a problem with pg_dump, the solution was to improve how PostgreSQL handles XML data.

Fixed in supported versions

This bug fix was included in the latest minor release of all currently supported versions of Postgres. This includes the releases:

• 11.3
• 10.8
• 9.6.13
• 9.5.17
• 9.4.22

Running those versions of PostgreSQL (or newer) should allow normal usage of pg_dump with XML data, including the QGIS layer_styles data without the workaround!

What version of PostgreSQL? walks through my reasoning to upgrade PostgreSQL major versions.

Summary

Every version of PostgreSQL gets better! Postgres is a great platform and has a great, involved community. The ability for me to dive into the source code to investigate a headache helped me find a bug. I was able to personally test the fix for the bug (I reviewed commitfest patches), and that fix will make my life easier. Even better, I know that my (relatively) tiny contribution here will make it easier and better for others in the future.

Need help with your PostgreSQL servers or databases? Contact us to start the conversation!