| Subject: | Quoting numeric default values in PostgreSQL is ++ungood |
This is an issue I encountered with generating schemas from
DBIx::Class::Schema::Loader but it turns out the root of the problem is
in SQL::Translator.
We are using DBIC::Schema::Loader to rebuild classes for a
rapidly-changing development database, then occasionally rebuilding the
entire database with DBIC::Schema->deploy (to test final installation
and conversion from a MySQL database) and continuing development on the
rebuilt schema.
Reproducing the issue -- Create a table in PostgreSQL with a default on
a numeric column:
CREATE TABLE foo (
data bigint DEFAULT 0
);
And then use sqlt to extract a schema file for it:
/usr/bin/sqlt -f DBI -t PostgreSQL --dsn ...
CREATE TABLE foo (
data int8 DEFAULT '0'
);
Rebuild the database using the sqlt output.
Now generate a new sqlt schema file from the rebuilt schema. You'll get
this:
CREATE TABLE foo (
data int8 DEFAULT '0::bigint'
);
which is unusable:
ERROR: invalid input syntax for integer: "0::bigint"
PostgreSQL sees DEFAULT '0' as an explicit cast and records it in the
catalog that way, causing all sorts of havoc down the road. (The
argument can be made that PostgreSQL's behavior here is itself broken,
but that is a story for another bugtracker.)
The problem can be solved simply by preventing
SQL::Translator::Producer::_apply_default_value from quoting something
that looks_like_number.
(There is a second issue with reading in tables that already have
default casts, but it is presumably safe to assume that no one is stupid
enough to do this intentionally, and anyone that is deserves whatever
they get. The issue only cropped up with our odd cycle of rebuilding
the database with DBIC::Schema->deploy and then regenerating the DBIC
classes from that.)