Our Blog

Ongoing observations by End Point people

Postgres pg_dump implicit cast problem patched!

By Greg Sabino Mullane · Monday, February 16, 2015

Tags: database, open-source, postgres

One of the many reasons I love Postgres is the responsiveness of the developers. Last week I posted an article about the dangers of reinstating some implicit data type casts. Foremost among the dangers was the fact that pg_dump will not dump user-created casts in the pg_catalog schema. Tom Lane (eximious Postgres hacker) read this and fixed it up - the very same day! So in git head (which will become Postgres version 9.5 someday) we no longer need to worry about custom casts disappearing with a pg_dump and reload. These same-day fixes are not an unusual thing for the Postgres project.

For due diligence, let's make sure that the casts now survive a pg_dump and reload into a new database via psql:

psql -qc 'drop database if exists casting_test'
psql -qc 'create database casting_test'
psql casting_test -xtc 'select 123::text = 123::int'
ERROR:  operator does not exist: text = integer
LINE 1: select 123::text = 123::int
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
psql casting_test -c 'create function pg_catalog.text(int) returns text immutable language sql as $$select textin(int4out($1))$$'

psql casting_test -c 'create cast (int as text) with function pg_catalog.text(int) as implicit'

psql casting_test -xtc 'select 123::text = 123::int'
 ?column? | t

psql -qc 'drop database if exists casting_test2'
psql -qc 'create database casting_test2'
pg_dump casting_test | psql -q casting_test2
psql casting_test2 -xtc 'select 123::text = 123::int'
 ?column? | t

Yay, it works! Thanks, Tom, for commit 9feefedf9e92066fa6609d1e1e17b4892d81716f). The fix even got back-patches, which means it will appear in Postgres version 9.5, but also versions 9.4.2, 9.3.7, 9.2.11, 9.1.16, and 9.0.20. However, does this mean that pg_dump is logically complete, or are there similar dangers lurking like eels below the water in the source code for pg_dump? You will be happy to learn that I could find no other exceptions inside of src/bin/pg_dump/pg_dump.c. While there are still many place in the code where an object can be excluded, it's all done for valid and expected reasons, such as not dumping a table if the schema it is in is not being dumped as well.