lannings at who
Mar 5, 2009, 1:50 AM
Post #6 of 7
(1765 views)
Permalink
|
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;
}
|
