• Home

  • Custom Ecommerce
  • Application Development
  • Database Consulting
  • Cloud Hosting
  • Systems Integration
  • Legacy Business Systems
  • Security & Compliance
  • GIS

  • Expertise

  • About Us
  • Our Team
  • Clients
  • Blog
  • Careers

  • CasePointer

  • VisionPort

  • Contact
  • Our Blog

    Ongoing observations by End Point Dev people

    Postgres pg_dump implicit cast problem patched!

    Greg Sabino Mullane

    By Greg Sabino Mullane
    February 16, 2015

    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.

    database open-source postgres