andy <[EMAIL PROTECTED]> writes:
> I'd like to offer: "example usage" and "Upgrading".
I did some hacking on your perl script to make it a bit more
bulletproof; I was worried about removing any function named 'concat'
for instance. Attached is what it looks like now.
I'm not entirely sure what to do with the script --- perhaps we should
put it into contrib/tsearch2 and have it get installed from there?
regards, tom lane
#! /usr/bin/perl
# syntax: removets2 dumpfile.dump [schema] > newtoc
# The dumpfile must have been made with pg_dump -Fc or -Ft
# If the tsearch2 objects were placed in some schema other than public,
# specify that schema's name as the second argument.
#
# pg_restore must be in your PATH
#
# We use pg_restore to get the dumpfile's table of contents (--list) and
# emit a new table of contents with all the tsearch2 objects stripped out.
# You can then do
# pg_restore -L newtoc dumpfile.dump ...
# to load your dump into Postgres 8.3.
use strict;
my $dumpfile = $ARGV[0];
my $schema = $ARGV[1] || 'public';
my %list = (
'FUNCTION gtsq_in(cstring)' => 1,
'FUNCTION gtsq_out(gtsq)' => 1,
'TYPE gtsq' => 1,
'FUNCTION gtsvector_in(cstring)' => 1,
'FUNCTION gtsvector_out(gtsvector)' => 1,
'TYPE gtsvector' => 1,
'FUNCTION tsquery_in(cstring)' => 1,
'FUNCTION tsquery_out(tsquery)' => 1,
'TYPE tsquery' => 1,
'FUNCTION tsvector_in(cstring)' => 1,
'FUNCTION tsvector_out(tsvector)' => 1,
'TYPE tsvector' => 1,
'TYPE statinfo' => 1,
'TYPE tokenout' => 1,
'TYPE tokentype' => 1,
'TYPE tsdebug' => 1,
'FUNCTION _get_parser_from_curcfg()' => 1,
'FUNCTION concat(tsvector, tsvector)' => 1,
'FUNCTION dex_init(internal)' => 1,
'FUNCTION dex_lexize(internal, internal, integer)' => 1,
'FUNCTION exectsq(tsvector, tsquery)' => 1,
'FUNCTION get_covers(tsvector, tsquery)' => 1,
'FUNCTION gin_extract_tsquery(tsquery, internal, internal)' => 1,
'FUNCTION gin_extract_tsvector(tsvector, internal)' => 1,
'FUNCTION gin_ts_consistent(internal, internal, tsquery)' => 1,
'FUNCTION gtsq_compress(internal)' => 1,
'FUNCTION gtsq_consistent(gtsq, internal, integer)' => 1,
'FUNCTION gtsq_decompress(internal)' => 1,
'FUNCTION gtsq_penalty(internal, internal, internal)' => 1,
'FUNCTION gtsq_picksplit(internal, internal)' => 1,
'FUNCTION gtsq_same(gtsq, gtsq, internal)' => 1,
'FUNCTION gtsq_union(bytea, internal)' => 1,
'FUNCTION gtsvector_compress(internal)' => 1,
'FUNCTION gtsvector_consistent(gtsvector, internal, integer)' => 1,
'FUNCTION gtsvector_decompress(internal)' => 1,
'FUNCTION gtsvector_penalty(internal, internal, internal)' => 1,
'FUNCTION gtsvector_picksplit(internal, internal)' => 1,
'FUNCTION gtsvector_same(gtsvector, gtsvector, internal)' => 1,
'FUNCTION gtsvector_union(internal, internal)' => 1,
'FUNCTION headline(oid, text, tsquery, text)' => 1,
'FUNCTION headline(oid, text, tsquery)' => 1,
'FUNCTION headline(text, text, tsquery, text)' => 1,
'FUNCTION headline(text, text, tsquery)' => 1,
'FUNCTION headline(text, tsquery, text)' => 1,
'FUNCTION headline(text, tsquery)' => 1,
'FUNCTION length(tsvector)' => 1,
'FUNCTION lexize(oid, text)' => 1,
'FUNCTION lexize(text, text)' => 1,
'FUNCTION lexize(text)' => 1,
'FUNCTION numnode(tsquery)' => 1,
'FUNCTION parse(oid, text)' => 1,
'FUNCTION parse(text, text)' => 1,
'FUNCTION parse(text)' => 1,
'FUNCTION plainto_tsquery(oid, text)' => 1,
'FUNCTION plainto_tsquery(text, text)' => 1,
'FUNCTION plainto_tsquery(text)' => 1,
'FUNCTION prsd_end(internal)' => 1,
'FUNCTION prsd_getlexeme(internal, internal, internal)' => 1,
'FUNCTION prsd_headline(internal, internal, internal)' => 1,
'FUNCTION prsd_lextype(internal)' => 1,
'FUNCTION prsd_start(internal, integer)' => 1,
'FUNCTION querytree(tsquery)' => 1,
'FUNCTION rank(real[], tsvector, tsquery)' => 1,
'FUNCTION rank(real[], tsvector, tsquery, integer)' => 1,
'FUNCTION rank(tsvector, tsquery)' => 1,
'FUNCTION rank(tsvector, tsquery, integer)' => 1,
'FUNCTION rank_cd(real[], tsvector, tsquery)' => 1,
'FUNCTION rank_cd(real[], tsvector, tsquery, integer)' => 1,
'FUNCTION rank_cd(tsvector, tsquery)' => 1,
'FUNCTION rank_cd(tsvector, tsquery, integer)' => 1,
'FUNCTION reset_tsearch()' => 1,
'FUNCTION rewrite(tsquery, text)' => 1,
'FUNCTION rewrite(tsquery, tsquery, tsquery)' => 1,
'FUNCTION rewrite_accum(tsquery, tsquery[])' => 1,
'FUNCTION rewrite_finish(tsquery)' => 1,
'FUNCTION rexectsq(tsquery, tsvector)' => 1,
'FUNCTION set_curcfg(integer)' => 1,
'FUNCTION set_curcfg(text)' => 1,
'FUNCTION set_curdict(integer)' => 1,
'FUNCTION set_curdict(text)' => 1,
'FUNCTION set_curprs(integer)' => 1,
'FUNCTION set_curprs(text)' => 1,
'FUNCTION setweight(tsvector, "char")' => 1,
'FUNCTION show_curcfg()' => 1,
'FUNCTION snb_en_init(internal)' => 1,
'FUNCTION snb_lexize(internal, internal, integer)' => 1,
'FUNCTION snb_ru_init_koi8(internal)' => 1,
'FUNCTION snb_ru_init_utf8(internal)' => 1,
'FUNCTION spell_init(internal)' => 1,
'FUNCTION spell_lexize(internal, internal, integer)' => 1,
'FUNCTION stat(text)' => 1,
'FUNCTION stat(text, text)' => 1,
'FUNCTION strip(tsvector)' => 1,
'FUNCTION syn_init(internal)' => 1,
'FUNCTION syn_lexize(internal, internal, integer)' => 1,
'FUNCTION thesaurus_init(internal)' => 1,
'FUNCTION thesaurus_lexize(internal, internal, integer, internal)' => 1,
'FUNCTION to_tsquery(oid, text)' => 1,
'FUNCTION to_tsquery(text, text)' => 1,
'FUNCTION to_tsquery(text)' => 1,
'FUNCTION to_tsvector(oid, text)' => 1,
'FUNCTION to_tsvector(text, text)' => 1,
'FUNCTION to_tsvector(text)' => 1,
'FUNCTION token_type(integer)' => 1,
'FUNCTION token_type(text)' => 1,
'FUNCTION token_type()' => 1,
'FUNCTION ts_debug(text)' => 1,
'FUNCTION tsearch2()' => 1,
'FUNCTION tsq_mcontained(tsquery, tsquery)' => 1,
'FUNCTION tsq_mcontains(tsquery, tsquery)' => 1,
'FUNCTION tsquery_and(tsquery, tsquery)' => 1,
'FUNCTION tsquery_cmp(tsquery, tsquery)' => 1,
'FUNCTION tsquery_eq(tsquery, tsquery)' => 1,
'FUNCTION tsquery_ge(tsquery, tsquery)' => 1,
'FUNCTION tsquery_gt(tsquery, tsquery)' => 1,
'FUNCTION tsquery_le(tsquery, tsquery)' => 1,
'FUNCTION tsquery_lt(tsquery, tsquery)' => 1,
'FUNCTION tsquery_ne(tsquery, tsquery)' => 1,
'FUNCTION tsquery_not(tsquery)' => 1,
'FUNCTION tsquery_or(tsquery, tsquery)' => 1,
'FUNCTION tsvector_cmp(tsvector, tsvector)' => 1,
'FUNCTION tsvector_eq(tsvector, tsvector)' => 1,
'FUNCTION tsvector_ge(tsvector, tsvector)' => 1,
'FUNCTION tsvector_gt(tsvector, tsvector)' => 1,
'FUNCTION tsvector_le(tsvector, tsvector)' => 1,
'FUNCTION tsvector_lt(tsvector, tsvector)' => 1,
'FUNCTION tsvector_ne(tsvector, tsvector)' => 1,
'AGGREGATE rewrite(tsquery[])' => 1,
'OPERATOR CLASS gin_tsvector_ops' => 1,
'OPERATOR CLASS gist_tp_tsquery_ops' => 1,
'OPERATOR CLASS gist_tsvector_ops' => 1,
'OPERATOR CLASS tsquery_ops' => 1,
'OPERATOR CLASS tsvector_ops' => 1,
'TABLE pg_ts_cfg' => 1,
'TABLE pg_ts_cfgmap' => 1,
'TABLE pg_ts_dict' => 1,
'TABLE pg_ts_parser' => 1,
'TABLE DATA pg_ts_cfg' => 1,
'TABLE DATA pg_ts_cfgmap' => 1,
'TABLE DATA pg_ts_dict' => 1,
'TABLE DATA pg_ts_parser' => 1,
'CONSTRAINT pg_ts_cfg_pkey' => 1,
'CONSTRAINT pg_ts_cfgmap_pkey' => 1,
'CONSTRAINT pg_ts_dict_pkey' => 1,
'CONSTRAINT pg_ts_parser_pkey' => 1
);
my @list = qx/pg_restore --list $dumpfile/;
foreach (@list)
{
if (/(FUNCTION|AGGREGATE)\s+($schema)\s+(.*?)\)/)
{
my $key = $1 . ' ' . $3 . ')';
#print "$key\n";
if (!exists($list{$key}))
{
print;
}
}
elsif (/COMMENT\s+($schema)\s+(FUNCTION)\s+(.*?)\)/)
{
my $key = $2 . ' ' . $3 . ')';
#print "$key\n";
if (!exists($list{$key}))
{
print;
}
}
elsif (/(TYPE|OPERATOR CLASS|CONSTRAINT|TABLE|TABLE
DATA)\s+($schema)\s+(.*?)\s/)
{
my $key = $1 . ' ' . $3;
#print "$key\n";
if (!exists($list{$key}))
{
print;
}
}
elsif (/(OPERATOR)\s+($schema)\s+(.*?)\s/)
{
# We have to look into the operator definition to be
# reasonably sure about what it is. We suppress an
# operator if its underlying function belongs to tsearch2.
open(F, ">item$$.tmp") or die;
print F $_;
close(F);
my $buff = qx/pg_restore -L item$$.tmp $dumpfile/;
unlink("item$$.tmp");
#print "operator definition: $buff";
my $key = 'FUNCTION ';
if ($buff =~ /PROCEDURE = ([a-z0-9_.]*)/)
{
$key .= $1 . '(';
}
else
{
print STDERR "no PROCEDURE in OPERATOR item\n";
$key = '';
}
if ($buff =~ /LEFTARG = ([a-z0-9_.]*)/)
{
$key .= $1 . ', ';
}
else
{
# this is not an error case: tsearch2 has prefix oprs
#print STDERR "no LEFTARG in OPERATOR item\n";
}
if ($buff =~ /RIGHTARG = ([a-z0-9_.]*)/)
{
$key .= $1 . ')';
}
else
{
# tsearch2 has no postfix oprs, but user might
#print STDERR "no RIGHTARG in OPERATOR item\n";
$key = '';
}
#print "$key\n";
if (!exists($list{$key}))
{
print;
}
}
else {
print;
}
}
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
