Mailing List Archive

aggregate id_list
I'm running

perl inst/upgrade/1.11.0/change_aggregate_name_Pg.pl

and it gives an error:

######################################################################
ERROR: DATABASE UPDATE FAILED!

The database was not affected. Please address this issue before continuing.

The error encountered was:

DBD::Pg::st execute failed: ERROR: aggregate id_list(integer) does not exist
[for Statement "DROP AGGREGATE id_list (INTEGER)"] at
/usr/lib/perl5/site_perl/5.8.0/Bric/Util/DBI.pm line 1030.
Unable to execute SQL statement: DBD::Pg::st execute failed: ERROR: aggregate
id_list(integer) does not exist [for Statement "DROP AGGREGATE id_list
(INTEGER)"] at /usr/lib/perl5/site_perl/5.8.0/Bric/Util/DBI.pm line 1030.

[/usr/lib/perl5/site_perl/5.8.0/Bric/Util/DBI.pm:1031]
[/services/webit/trunk-r8432/inst/upgrade/1.11.0/../lib/bric_upgrade.pm:320]
[inst/upgrade/1.11.0/change_aggregate_name_Pg.pl:11]


In fact, this is NUMERIC, not INTEGER:

# \da id_list
List of aggregate functions
Schema | Name | Argument data types | Description
--------+---------+---------------------+-------------
public | id_list | numeric |


It seems to've been set that way in 1.7.3/id_list_aggregate.pl :

q{CREATE AGGREGATE id_list (
SFUNC = append_id,
BASETYPE = NUMERIC(10, 0),
STYPE = TEXT,
INITCOND = ''
)},


No comprendo.
Re: aggregate id_list [ In reply to ]
On Tue, 3 Mar 2009, Scott Lanning wrote:
> I'm running
>
> perl inst/upgrade/1.11.0/change_aggregate_name_Pg.pl
>
> and it gives an error:
>
> ######################################################################
> ERROR: DATABASE UPDATE FAILED!
>
> The database was not affected. Please address this issue before continuing.
>
> The error encountered was:
>
> DBD::Pg::st execute failed: ERROR: aggregate id_list(integer) does not exist
> [for Statement "DROP AGGREGATE id_list (INTEGER)"] at
> /usr/lib/perl5/site_perl/5.8.0/Bric/Util/DBI.pm line 1030.
> Unable to execute SQL statement: DBD::Pg::st execute failed: ERROR:
> aggregate id_list(integer) does not exist [for Statement "DROP AGGREGATE
> id_list (INTEGER)"] at /usr/lib/perl5/site_perl/5.8.0/Bric/Util/DBI.pm line
> 1030.
>
> [/usr/lib/perl5/site_perl/5.8.0/Bric/Util/DBI.pm:1031]
> [/services/webit/trunk-r8432/inst/upgrade/1.11.0/../lib/bric_upgrade.pm:320]
> [inst/upgrade/1.11.0/change_aggregate_name_Pg.pl:11]
>
>
> In fact, this is NUMERIC, not INTEGER:
>
> # \da id_list
> List of aggregate functions
> Schema | Name | Argument data types | Description
> --------+---------+---------------------+-------------
> public | id_list | numeric |
>
>
> It seems to've been set that way in 1.7.3/id_list_aggregate.pl :
>
> q{CREATE AGGREGATE id_list (
> SFUNC = append_id,
> BASETYPE = NUMERIC(10, 0),
> STYPE = TEXT,
> INITCOND = ''
> )},
>
>
> No comprendo.

When I drop id_list(NUMERIC) instead, it gives another error:

DBD::Pg::st execute failed: ERROR: function append_id(text, numeric) requires
run-time type coercion [for Statement "CREATE AGGREGATE group_concat (
SFUNC = append_id,
BASETYPE = INTEGER,
STYPE = TEXT,
INITCOND = ''
)
"] at /usr/lib/perl5/site_perl/5.8.0/Bric/Util/DBI.pm line 1030.
Unable to execute SQL statement: DBD::Pg::st execute failed: ERROR: function
append_id(text, numeric) requires run-time type coercion [for Statement "CREATE
AGGREGATE group_concat (
SFUNC = append_id,
BASETYPE = INTEGER,
STYPE = TEXT,
INITCOND = ''
)
"] at /usr/lib/perl5/site_perl/5.8.0/Bric/Util/DBI.pm line 1030.


This is another one that the scripts pretend is INTEGER,
but it was NUMERIC in inst/upgrade/1.7.3/id_list_aggregate.pl :

q{CREATE FUNCTION append_id(TEXT, NUMERIC(10,0))
RETURNS TEXT AS '
SELECT CASE WHEN $2 = 0 THEN
$1
ELSE
$1 || '' '' || CAST($2 AS TEXT)
END;'
LANGUAGE 'sql'
WITH (ISCACHABLE, ISSTRICT)},


Ah, damn, it should've been fixed with inst/upgrade/1.9.0/no_more_numeric.pl.
I had to hack that all to hell to get it to "work"..........
Ah well, I'll figure something out.
Re: aggregate id_list [ In reply to ]
On Mar 3, 2009, at 8:31 AM, Scott Lanning wrote:

> It seems to've been set that way in 1.7.3/id_list_aggregate.pl :
>
> q{CREATE AGGREGATE id_list (
> SFUNC = append_id,
> BASETYPE = NUMERIC(10, 0),
> STYPE = TEXT,
> INITCOND = ''
> )},

Huh. That should be integer. In fact, I think id_list is gone; it's
not in Bric.sql.

Best,

David
Re: aggregate id_list [ In reply to ]
On Tue, 3 Mar 2009, David E. Wheeler wrote:
> On Mar 3, 2009, at 8:31 AM, Scott Lanning wrote:
>> It seems to've been set that way in 1.7.3/id_list_aggregate.pl :
>>
>> q{CREATE AGGREGATE id_list (
>> SFUNC = append_id,
>> BASETYPE = NUMERIC(10, 0),
>> STYPE = TEXT,
>> INITCOND = ''
>> )},
>
> Huh. That should be integer. In fact, I think id_list is gone; it's not in
> Bric.sql.

It's renamed by the script I was running. (FWIW)

It turns out I have a lot of work to do
converting a bunch of numeric to integer..
Although I spent like a week or two hacking the no_more_numeric.pl
script for the 1.10 upgrade, I seem to have badly
misread what all it does since I practically only
updated 'active' and a few others.

I did find a better way (I think, at least for postgres 8.2)
to find all the numeric columns:

select table_name,column_name,data_type,numeric_precision,column_default from
information_schema.columns where table_schema='public' and data_type='numeric'
order by table_name,column_name ;

I seem to have 366 remaining numeric columns to update here....

[.the rest might not make much sense, since it depends
on what I did here locally]

So I'll need variations of the subroutine
that I used in place of no_more_numeric.pl before:

sub alter_column_type {
my ($dbh, $constraint, $table, $column, $default) = @_;

$dbh->begin_work();

# This doesn't work because of the 'active' part, I assume,
# so drop the index and recreate it afterwards
# CREATE UNIQUE INDEX udx_usr__login ON usr USING btree
(lower(("login")::text)) WHERE (active = (1)::numeric)
if ($table eq 'usr') {
my $sql = sprintf "DROP INDEX udx_usr__login";
print "SQL: $sql\n";
do_or_die($dbh, $sql);
}

# Drop the "CHECK that it's 0 or 1" constraint
my $sql = sprintf 'ALTER TABLE %s DROP CONSTRAINT %s', $table, $constraint;
do_or_die($dbh, $sql);

# This is because when you alter the type the booleans
# don't work with numeric (or vice versa), so I drop the default
# and then re-add it afterward.
$sql = sprintf 'ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT', $table,
$column;
do_or_die($dbh, $sql);

# Actually alter the type here
$sql = sprintf 'ALTER TABLE %s ALTER COLUMN %s TYPE bool USING CASE %s WHEN
0 THEN false ELSE true END', $table, $column, $column;
do_or_die($dbh, $sql);

# Re-add the default
$sql = sprintf 'ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s', $table,
$column, $default;
do_or_die($dbh, $sql);

# and add back the index, with boolean 'active' instead of numeric
if ($table eq 'usr') {
my $sql = 'CREATE UNIQUE INDEX udx_usr__login ON usr USING btree
(lower(("login")::text)) WHERE (active = true)';
print "SQL: $sql\n";
do_or_die($dbh, $sql);
}

$dbh->commit();
}


I have to figure out which (kind of) columns can be altered directly,
and which need to have their constraints and/or defaults dropped
temporarily. And ideally I have to do that by today.. :/

# select table_name,column_name,data_type,numeric_precision from
information_schema.columns where table_schema='public' and data_type='numeric'
order by table_name,column_name ;
table_name | column_name | data_type |
numeric_precision
------------------------------+---------------------+-----------+-------------------
action | action_type__id | numeric |
10
action | id | numeric |
10
action | ord | numeric |
3
action | server_type__id | numeric |
10
action_type | id | numeric |
10
action_type__media_type | action_type__id | numeric |
10
action_type__media_type | media_type__id | numeric |
10
addr | id | numeric |
10
[....]
Re: aggregate id_list [ In reply to ]
On Mar 4, 2009, at 1:08 AM, Scott Lanning wrote:

> I did find a better way (I think, at least for postgres 8.2)
> to find all the numeric columns:
>
> select
> table_name,column_name,data_type,numeric_precision,column_default
> from information_schema.columns where table_schema='public' and
> data_type='numeric' order by table_name,column_name ;

Yes, I've been learning a lot about the system catalogs (and
information_schema) in writing pgTAP.

> I seem to have 366 remaining numeric columns to update here....

Yow.

> [.the rest might not make much sense, since it depends
> on what I did here locally]
>
> So I'll need variations of the subroutine
> that I used in place of no_more_numeric.pl before:
>
> sub alter_column_type {
> my ($dbh, $constraint, $table, $column, $default) = @_;
>
> $dbh->begin_work();
>
> # This doesn't work because of the 'active' part, I assume,
> # so drop the index and recreate it afterwards
> # CREATE UNIQUE INDEX udx_usr__login ON usr USING btree
> (lower(("login")::text)) WHERE (active = (1)::numeric)

Yeah, change that to "WHERE ACTIVE = '1'".

> if ($table eq 'usr') {
> my $sql = sprintf "DROP INDEX udx_usr__login";
> print "SQL: $sql\n";
> do_or_die($dbh, $sql);
> }

So you'll need to be sure to recreate that index, of course, with the
WHERE clause I mention above.


> # Drop the "CHECK that it's 0 or 1" constraint
> my $sql = sprintf 'ALTER TABLE %s DROP CONSTRAINT %s', $table,
> $constraint;
> do_or_die($dbh, $sql);

Yes, good.

> # This is because when you alter the type the booleans
> # don't work with numeric (or vice versa), so I drop the default
> # and then re-add it afterward.
> $sql = sprintf 'ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT',
> $table, $column;
> do_or_die($dbh, $sql);

You can just change the default to "'1'" or "'0'" as appropriate.

> # Actually alter the type here
> $sql = sprintf 'ALTER TABLE %s ALTER COLUMN %s TYPE bool USING
> CASE %s WHEN 0 THEN false ELSE true END', $table, $column, $column;
> do_or_die($dbh, $sql);

Yes, good, that will also update your indexes and stuff automatically.

> # Re-add the default
> $sql = sprintf 'ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
> $table, $column, $default;
> do_or_die($dbh, $sql);

Good.

> # and add back the index, with boolean 'active' instead of numeric
> if ($table eq 'usr') {
> my $sql = 'CREATE UNIQUE INDEX udx_usr__login ON usr USING
> btree (lower(("login")::text)) WHERE (active = true)';
> print "SQL: $sql\n";
> do_or_die($dbh, $sql);
> }
>
> $dbh->commit();
> }

That looks pretty good.

> I have to figure out which (kind of) columns can be altered directly,
> and which need to have their constraints and/or defaults dropped
> temporarily. And ideally I have to do that by today.. :/

As I said, you can change the default to use the textual
representations of truth, "'1'" or "'0'", rather than drop the
defaults. Most of the constraints, IIRC, can simply be dropped
altogether.

Best,

David
Re: aggregate id_list [ In reply to ]
On Wed, 4 Mar 2009, David E. Wheeler wrote:
[...]

Since you analyzed so meticulously the script
I ran for the 1.10 upgrade last year,
below is what I came up with yesterday to convert
the rest of the numeric types,
though it probably won't be relevant for anyone else.
(This is not normal, but was necessary because
it was not possible to pg_dump our database.)

FWIW, the conversion from numeric seems to have
shaved a few gigabytes off the database size on the disk.

(Some wrapping below...)


#!/usr/bin/perl
# this is mostly equivalent to inst/upgrade/1.9.0/no_more_numeric.pl
# except for the "old boolean CHECKs" like for 'active' columns
# which I'd taken care of previously

use strict;
use warnings;
use DBI;

main();
exit;

sub main {
$|++;

my $dbh = get_dbh();

replace_functions($dbh);
drop_constraints($dbh);

my $cols = get_columns($dbh);
foreach my $table (sort keys %$cols) {
print "table: $table\n";
foreach my $col (sort keys %{ $cols->{$table} }) {
print "column: $col\n";

alter_column_type($dbh, $table, $col, $cols->{$table}{$col});
}
}

replace_constraints($dbh);

$dbh->disconnect();
}

sub alter_column_type {
my ($dbh, $table, $column, $col_meta) = @_;

my $precision = $col_meta->{numeric_precision};
my $default = $col_meta->{column_default};

if ($column =~
/^(uri_case|tplate_type|ord|priority|place|burner|uri_case|tries|type)$/
&& $precision <= 3)
{
do_or_die($dbh,
sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE smallint',
$table, $column));
}
elsif ($precision >= 2 && $precision <= 4) {
do_or_die($dbh,
sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE smallint',
$table, $column));
}
elsif ($precision == 10) {
do_or_die($dbh,
sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE integer',
$table, $column));
}
elsif ($precision == 1) {
if (defined($default) && $default > 1) {
do_or_die($dbh,
sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE smallint',
$table, $column));
}
else {
if (defined $default) {
do_or_die($dbh,
sprintf('ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT',
$table, $column));
}

do_or_die($dbh,
sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE boolean USING
CASE %s WHEN 1 THEN true ELSE false END',
$table, $column, $column));

if (defined $default) {
do_or_die($dbh,
sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT
%s',
$table, $column, ($default ? 'true' :
'false')));
}
}
}
else {
die "WOAH: $table, $column\n";
}
}

sub drop_constraints {
my ($dbh) = @_;

# column 'executing' numeric(1,0) --> boolean
do_or_die($dbh, 'ALTER TABLE job DROP CONSTRAINT ck_job__executing');

# publish_status numeric --> boolean
do_or_die($dbh, 'ALTER TABLE media DROP CONSTRAINT
ck_media__publish_status');
do_or_die($dbh, 'ALTER TABLE story DROP CONSTRAINT
ck_story__publish_status');
}

sub replace_constraints {
my ($dbh) = @_;

# publish_status numeric --> boolean
my $sql = 'ALTER TABLE media ADD CONSTRAINT ck_media__publish_status CHECK
(((((publish_status = false) AND (publish_date IS NULL)) AND (first_publish_date
IS NULL)) OR (((publish_status = true) AND (publish_date IS NOT NULL)) AND
(first_publish_date IS NOT NULL))))';
do_or_die($dbh, $sql);
$sql = 'ALTER TABLE story ADD CONSTRAINT ck_story__publish_status CHECK
(((((publish_status = false) AND (publish_date IS NULL)) AND (first_publish_date
IS NULL)) OR (((publish_status = true) AND (publish_date IS NOT NULL)) AND
(first_publish_date IS NOT NULL))))';
do_or_die($dbh, $sql);
}

sub replace_functions {
my ($dbh) = @_;

# there's already a function lower_text_num(text, numeric),
# but it's okay to overload them in postgresql
# (and it causes an error trying to drop it for now)
# lower_text_num(text, numeric) could be run after alter_column_type runs
my $sql = <<'SQL';
CREATE FUNCTION lower_text_num(text, integer) RETURNS text
AS $_$SELECT LOWER($1) || to_char($2, '|FM9999999999')$_$
LANGUAGE sql IMMUTABLE;
SQL
do_or_die($dbh, $sql);

}

sub get_columns {
my ($dbh) = @_;
my %columns = ();

# get info for each numeric column
my $sql = <<'SQL';
SELECT table_name, column_name, numeric_precision, column_default
FROM information_schema.columns
WHERE table_schema='public' and data_type='numeric' and column_name not like
'pg_%'
ORDER BY table_name, column_name
SQL

my $sth = $dbh->prepare($sql);
$sth->execute();
while (my ($table, $col, $num_prec, $def) = $sth->fetchrow_array()) {
$columns{$table}{$col} = {
numeric_precision => $num_prec,
column_default => $def,
};
}
$sth->finish();

return \%columns;
}

sub do_or_die {
my ($dbh, $sql) = @_;

print $sql,$/;

unless ($dbh->do($sql)) {
my $err = $dbh->errstr;
$dbh->rollback();
die "Rolling back. The error follows:\n$err (for SQL=$sql)\n"
}
}

sub get_dbh {
my $host = '';
my $db = '';
my $user = '';
my $pass = '';
my $port = '';
my $dsn = "dbi:Pg:dbname=$db;host=$host;port=$port";

return DBI->connect($dsn, $user, $pass) or die $DBI::errstr;
}

__END__

[notes for alter_column_type:]

"the old boolean checks" are taken care of, except:

CONSTRAINT ck_job__executing CHECK ((executing = ANY (ARRAY[(1)::numeric,
(0)::numeric])))

-- publish_status = 0 or 1 should be bool in table 'media'
CONSTRAINT ck_media__publish_status CHECK (((((publish_status =
(0)::numeric) AND (publish_date IS NULL)) AND (first_publish_date IS NULL)) OR
(((publish_status = (1)::numeric) AND (publish_date IS NOT NULL)) AND
(first_publish_date IS NOT NULL)))),
-- publish_status = 0 or 1 should be bool in table 'story'
CONSTRAINT ck_story__publish_status CHECK (((((publish_status =
(0)::numeric) AND (publish_date IS NULL)) AND (first_publish_date IS NULL)) OR
(((publish_status = (1)::numeric) AND (publish_date IS NOT NULL)) AND
(first_publish_date IS NOT NULL)))),


-------

FUNCTION lower_text_num(text, numeric) --> lower_text_num(text, integer)

-----------

(uri_case|tplate_type|ord|priority|place|burner|uri_case|tries|"type")
numeric([123],0)
--> $1 smallint

numeric([234],0) --> smallint

-----------

numeric(10,0) --> integer

-----------

numeric(1,0)
default [2-9] --> smallint
default [01] --> boolean (and change default 0 --> false, 1 --> true)

-----------

otherwise, ::numeric --> ::integer





This similar script is what I ran last time
(alter_column_type that you analyzed was from this):

#!/usr/bin/perl
# Scripts used during upgrade from 1.8.3 to 1.10.3,
# in place of inst/upgrade/1.9.0/no_more_numeric.pl;
# it avoids pg_dumping the database, munging the output,
# then reloading it.

use strict;
use warnings;

use DBI;

main();

sub main {
my $dbh = get_dbh();

my $constraints = get_constraints($dbh);

print `date`;

foreach my $constraint (sort keys %$constraints) {
my $table = $constraints->{$constraint}{table};
my $column = $constraints->{$constraint}{column};

next if (($table eq 'story' or $table eq 'media') and $column eq
'publish_status');

print "Changing type for constraint '$constraint' on column '$column' in
table '$table'\n";

my $default = get_default($dbh, $table, $column);
alter_column_type($dbh, $constraint, $table, $column, $default);
}

print `date`;

$dbh->disconnect();
}

sub get_constraints {
my ($dbh) = @_;

my %constraints = ();

# List of CHECK constraints looking something like
# (((active = (0)::numeric) OR (active = (1)::numeric))).
# At least that is what it looks like in version 8.2.
# These are what we're converting from numeric to boolean.
# There's a join to get the table and column name of the constraint.
# Fear the backslashes, I know.

my $sql = <<'SQL';
SELECT ccu.table_name, ccu.column_name, ccu.constraint_name
FROM information_schema.check_constraints cc,
information_schema.constraint_column_usage ccu
WHERE cc.constraint_name = ccu.constraint_name
AND ccu.constraint_catalog='bricolage' AND ccu.constraint_name LIKE
'ck_%'
AND (cc.check_clause ~
E'\\(\\(\\(\\w+\\s*=\\s*\\([01]\\)::numeric\\)\\s*OR\\s*\\(\\w+\\s*=\\s*\\([01]\\)::numeric\\)\\)\\)')
SQL

my $sth = $dbh->prepare($sql);
$sth->execute();
while (my ($table, $col, $con) = $sth->fetchrow_array()) {
$constraints{$con} = {'table' => $table, 'column' => $col};
}
$sth->finish();
return \%constraints;
}

sub get_default {
my ($dbh, $table, $column) = @_;

# The default value for a particular column in a table,
# in particular 1 or 0 (true or false)
my $sql = <<'SQL';
SELECT adsrc as default_value
FROM pg_attrdef pad, pg_attribute pat, pg_class pc
WHERE pc.relname = ? AND pc.oid = pat.attrelid AND pat.attname = ?
AND pat.attrelid = pad.adrelid AND pat.attnum = pad.adnum
SQL

my $sth = $dbh->prepare_cached($sql);
$sth->execute($table, $column);
my ($default) = $sth->fetchrow_array();
$sth->finish();
return $default ? 'true' : 'false';
}

sub alter_column_type {
my ($dbh, $constraint, $table, $column, $default) = @_;

$dbh->begin_work();

# This doesn't work because of the 'active' part, I assume,
# so drop the index and recreate it afterwards
# CREATE UNIQUE INDEX udx_usr__login ON usr USING btree
(lower(("login")::text)) WHERE (active = (1)::numeric)
if ($table eq 'usr') {
my $sql = sprintf "DROP INDEX udx_usr__login";
print "SQL: $sql\n";
do_or_die($dbh, $sql);
}

# Drop the "CHECK that it's 0 or 1" constraint
my $sql = sprintf 'ALTER TABLE %s DROP CONSTRAINT %s', $table, $constraint;
do_or_die($dbh, $sql);

# This is because when you alter the type the booleans
# don't work with numeric (or vice versa), so I drop the default
# and then re-add it afterward.
$sql = sprintf 'ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT', $table,
$column;
do_or_die($dbh, $sql);

# Actually alter the type here
$sql = sprintf 'ALTER TABLE %s ALTER COLUMN %s TYPE bool USING CASE %s WHEN
0 THEN false ELSE true END', $table, $column, $column;
do_or_die($dbh, $sql);

# Re-add the default
$sql = sprintf 'ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s', $table,
$column, $default;
do_or_die($dbh, $sql);

# and add back the index, with boolean 'active' instead of numeric
if ($table eq 'usr') {
my $sql = 'CREATE UNIQUE INDEX udx_usr__login ON usr USING btree
(lower(("login")::text)) WHERE (active = true)';
print "SQL: $sql\n";
do_or_die($dbh, $sql);
}


$dbh->commit();
}

sub do_or_die {
my ($dbh, $sql) = @_;

#print $sql,$/;
#return;

unless ($dbh->do($sql)) {
my $err = $dbh->errstr;
$dbh->rollback();
die "The error follows:\n$err (for SQL=$sql)\n"
}
}

sub get_dbh {
my $host = '';
my $db = '';
my $user = '';
my $pass = '';
my $port = '';
my $dsn = "dbi:Pg:dbname=$db;host=$host;port=$port";

return DBI->connect($dsn, $user, $pass) or die $DBI::errstr;
}
Re: aggregate id_list [ In reply to ]
On Mar 5, 2009, at 1:50 AM, Scott Lanning wrote:

> Since you analyzed so meticulously the script
> I ran for the 1.10 upgrade last year,
> below is what I came up with yesterday to convert
> the rest of the numeric types,
> though it probably won't be relevant for anyone else.
> (This is not normal, but was necessary because
> it was not possible to pg_dump our database.)

Looks pretty good. The thing to do, I think, is to export the schema
only from that database, then do the same with a clean build from
source, then diff them. There'll be some differences in the ordering
of columns in tables, but all else should be the same.

> FWIW, the conversion from numeric seems to have
> shaved a few gigabytes off the database size on the disk.

That's pretty impressive. You might want to run `vacuum full` on this
sucker when you do the production migration. It'll take a while to
run, but you'll reclaim a lot of disk space and generally make things
better for your DB.

Best,

David