News

Welcome to End Point’s blog

Ongoing observations by End Point people

Postgres pg_dump implicit cast problem patched!

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))$$'
CREATE FUNCTION

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

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.

4 comments:

beldmit said...

Pg_dump ignores at least set search_path when you dump the database. I think patch will be rather small.

Greg Sabino Mullane said...

beldmit: I am not sure what you are saying - do you see it as a problem that search_path is not used?

Felix Ostmann said...

Nice blogpost and exactly my current problem. But the solution is not working for me, because we are using schemas. I can't find a way to create the CAST in the schema testschema, so that a pg_dump for that schema will get the CAST to :-/

$ pg_dump -s --schema testschema testdatabase | grep CAST
$ pg_dump -s testdatabase | grep CAST
-- Name: CAST (text AS testschema.enum_users_title); Type: CAST; Schema: pg_catalog; Owner:
CREATE CAST (text AS testschema.enum_users_title) WITH FUNCTION testschema.cast_users_title(text) AS IMPLICIT;


Greg Sabino Mullane said...

Felix, that does seem like a bug at first glance. Maybe run it up the flagpole via pgsql-bugs@postgresql.org and see if there is a workaround or if someone will fix it?