Re: some strange bugs related to upgrade from 8.1 to 8.3

Hello

I started upgrade our databases from 8.1 to 8.3. I found two strange
bugs related to tsearch2.

a) server crash after creating tsearch2 function (I use tsearch2
contrib from 8.3)
(gdb) bt
#0 0x0000003838033075 in raise () from /lib64/libc.so.6
#1 0x0000003838034be3 in abort () from /lib64/libc.so.6
#2 0x000000000068db5d in ExceptionalCondition (conditionName=<value
optimized out>, errorType=<value optimized out>, fileName=<value
optimized out>, lineNumber=<value optimized out>) at assert.c:57
#3 0x0000000000a6cc79 in plpgsql_HashTableInit () at pl_comp.c:2016
#4 0x0000000000e95d72 in _PG_init () from /usr/lib64/pgsql/plpgsql.so
#5 0x0000000000693105 in internal_load_library (libname=0x1fd5c70
"/usr/lib64/pgsql/plpgsql.so") at dfmgr.c:296
#6 0x0000000000693506 in load_external_function (filename=<value
optimized out>, funcname=0x1fa8358 "plpgsql_call_handler",
signalNotFound=1 '\001', filehandle=0x7fff8eb5d678) at dfmgr.c:110
#7 0x00000000004bd1b9 in fmgr_c_validator (fcinfo=<value optimized
out>) at pg_proc.c:509
#8 0x00000000006964e6 in OidFunctionCall1 (functionId=<value
optimized out>, arg1=28155) at fmgr.c:1532
#9 0x00000000004bdab0 in ProcedureCreate (procedureName=0x1f6a430
"plpgsql_call_handler", procNamespace=2200, replace=0 '\0',
returnsSet=0 '\0', returnType=2280, languageObjectId=13,
languageValidator=2247, prosrc=0x1f6a640 "plpgsql_call_handler",
probin=0x1f6a608 "/usr/lib64/pgsql/plpgsql", isAgg=0 '\0',
security_definer=0 '\0', isStrict=0 '\0', volatility=118 'v',
parameterTypes=0x1fa7a90, allParameterTypes=0, parameterModes=0,
parameterNames=0, proconfig=0, procost=1, prorows=0) at pg_proc.c:413
#10 0x0000000000502588 in CreateFunction (stmt=0x1f6a890) at functioncmds.c:785
#11 0x00000000005e3da5 in PortalRunUtility (portal=0x1fc3678,
utilityStmt=0x1f6a890, isTopLevel=-1 '�', dest=0x1f6a948,
completionTag=0x7fff8eb5e020 "") at pquery.c:1173
#12 0x00000000005e53d5 in PortalRunMulti (portal=0x1fc3678,
isTopLevel=<value optimized out>, dest=0x1f6a948, altdest=0x1f6a948,
completionTag=0x7fff8eb5e020 "") at pquery.c:1266
#13 0x00000000005e5c1b in PortalRun (portal=0x1fc3678,
count=9223372036854775807, isTopLevel=64 '@', dest=0x1f6a948,
altdest=0x1f6a948, completionTag=0x7fff8eb5e020 "") at pquery.c:813
#14 0x00000000005e0a0c in exec_simple_query (
query_string=0x1f69ae8 "CREATE FUNCTION plpgsql_call_handler()
RETURNS language_handler\n AS '/usr/lib64/pgsql/plpgsql',
'plpgsql_call_handler'\n LANGUAGE c;") at postgres.c:986
#15 0x00000000005e1f67 in PostgresMain (argc=4, argv=<value optimized
out>, username=0x1ed56a8 "pavel") at postgres.c:3572
#16 0x00000000005ae96a in BackendRun () at postmaster.c:3207
#17 BackendStartup () at postmaster.c:2830
#18 ServerLoop () at postmaster.c:1274
#19 0x00000000005af685 in PostmasterMain (argc=3, argv=0x1ed1540) at
postmaster.c:1029
#20 0x000000000055f208 in main (argc=3, argv=0x1ed1540) at main.c:188
(gdb)

ERROR: function public.lexize(text) does not exist
STATEMENT: ALTER FUNCTION public.lexize(text) OWNER TO postgres;
ERROR: could not find function "parse" in file
"/usr/local/pgsql8.3/lib/tsearch2.so"
STATEMENT: CREATE FUNCTION parse(oid, text) RETURNS SETOF tokenout
<------> AS '$libdir/tsearch2', 'parse'
<------> LANGUAGE c STRICT;
ERROR: function public.parse(oid, text) does not exist
STATEMENT: ALTER FUNCTION public.parse(oid, text) OWNER TO postgres;
ERROR: could not find function "parse_byname" in file
"/usr/local/pgsql8.3/lib/tsearch2.so"
STATEMENT: CREATE FUNCTION parse(text, text) RETURNS SETOF tokenout
<------> AS '$libdir/tsearch2', 'parse_byname'
<------> LANGUAGE c STRICT;
ERROR: function public.parse(text, text) does not exist
STATEMENT: ALTER FUNCTION public.parse(text, text) OWNER TO postgres;
ERROR: could not find function "parse_current" in file
"/usr/local/pgsql8.3/lib/tsearch2.so"
STATEMENT: CREATE FUNCTION parse(text) RETURNS SETOF tokenout
<------> AS '$libdir/tsearch2', 'parse_current'
<------> LANGUAGE c STRICT;
ERROR: function public.parse(text) does not exist
STATEMENT: ALTER FUNCTION public.parse(text) OWNER TO postgres;
TRAP: FailedAssertion("!(plpgsql_HashTable == ((void *)0))", File:
"pl_comp.c", Line: 2016)
LOG: server process (PID 4672) was terminated by signal 6: Aborted
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2008-11-04 15:01:14 CET
LOG: database system was not properly shut down; automatic recovery in progress

b) after instalation tsearch2 (8.3) postgresql knows tsvector type

template1=# select 'tsvector'::regtype;
regtype
----------
tsvector
(1 row)

template1=# create table foo(a tsvector);
CREATE TABLE
template1=#

but inside import pg should forget this type and use tsvector only as shell type

ERROR: type "tsvector" is only a shell
STATEMENT: CREATE TYPE tsdebug AS (
<------><------>ts_name text,
<------><------>tok_type text,
<------><------>description text,
<------><------>token text,
<------><------>dict_name text[],
<------><------>tsvector tsvector
<------>);
ERROR: type "public.tsdebug" does not exist
STATEMENT: ALTER TYPE public.tsdebug OWNER TO postgres;
NOTICE: argument type tsvector is only a shell
STATEMENT: CREATE FUNCTION concat(tsvector, tsvector) RETURNS tsvector
<------> AS '$libdir/tsearch2', 'concat'
<------> LANGUAGE c IMMUTABLE STRICT;
NOTICE: argument type tsvector is only a shell
STATEMENT: CREATE FUNCTION concat(tsvector, tsvector) RETURNS tsvector
<------> AS '$libdir/tsearch2', 'concat'

in this moment we cannot do tables
ERROR: type "tsvector" is only a shell at character 1274
STATEMENT: CREATE TABLE "position" (CREATE TYPE tsvector;

<------> id integer NOT NULL,
<------> title character varying(255) NOT NULL,
<------> description text,
<------> work_address character varying(255),
<------> reward character varying(100),
<------> time_info character varying(100),
<------> work_type character varying(80),
<------> date_enter integer NOT NULL,
<------> date_expire integer NOT NULL,
<------> date_expire_original integer NOT NULL,
<------> id_status integer NOT NULL,
<------> submitter_name character varying(50),
<------> submitter_email character varying(80),
<------> submitter_phone character varying(50),
<------> uniq_key character varying(55),
<------> id_svl_work_kind character(3),
<------> submitter_ip character varying(40),
<------> source character(1),
<------> target character varying(2) NOT NULL,
<------> iswarranty character(1),
<------> url_pdjd character varying(255),
<------> g2_id integer,
<------> rejection_description text,
<------> g2_id_personnel integer,
<------> g2_id_company integer,
<------> contact_company_name character varying(100),
<------> contact_company_info character varying(255),
<------> contact_person character varying(50),
<------> contact_phone character varying(50),
<------> contact_email character varying(80),
<------> contact_address character varying(255),
<------> date_enter_modified integer,
<------> ispaid character(1) DEFAULT 0,
<------> date_enter_touched integer,
<------> fulltext_index tsvector
<------>);

build script is in attachement, log too

"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
> I started upgrade our databases from 8.1 to 8.3. I found two strange
> bugs related to tsearch2.

Did you follow the advice here:
http://www.postgresql.org/docs/8.3/static/textsearch-migration.html

> a) server crash after creating tsearch2 function (I use tsearch2
> contrib from 8.3)

I couldn't reproduce that with the script you gave.

regards, tom lane

2008/11/4 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
>> I started upgrade our databases from 8.1 to 8.3. I found two strange
>> bugs related to tsearch2.
>
> Did you follow the advice here:
> http://www.postgresql.org/docs/8.3/static/textsearch-migration.html

yes I did

try: psql83 template1 < db05.sql &> log.txt

I tested it on second computer with same result
You are now connected to database "postgres".
SET
SET
SET
ERROR: role "lmc" already exists
ALTER ROLE
CREATE DATABASE
You are now connected to database "brigady".
SET
SET
SET
SET
SET
CREATE LANGUAGE
ALTER LANGUAGE
SET
CREATE TYPE
ERROR: could not find function "gtsvector_in" in file
"/usr/local/pgsql83/lib/tse
ERROR: function public.gtsvector_in(cstring) does not exist
ERROR: could not find function "gtsvector_out" in file
"/usr/local/pgsql83/lib/ts
ERROR: function public.gtsvector_out(gtsvector) does not exist
ERROR: function gtsvector_in(cstring) does not exist
ALTER TYPE
CREATE TYPE
ERROR: could not find function "tsquery_in" in file
"/usr/local/pgsql83/lib/tsear
ERROR: function public.tsquery_in(cstring) does not exist
ERROR: could not find function "tsquery_out" in file
"/usr/local/pgsql83/lib/tsea
ERROR: function public.tsquery_out(tsquery) does not exist
ERROR: function tsquery_in(cstring) does not exist
ALTER TYPE
CREATE TYPE
ERROR: could not find function "tsvector_in" in file
"/usr/local/pgsql83/lib/tsea
ERROR: function public.tsvector_in(cstring) does not exist
ERROR: could not find function "tsvector_out" in file
"/usr/local/pgsql83/lib/tse
ERROR: function public.tsvector_out(tsvector) does not exist
ERROR: function tsvector_in(cstring) does not exist
ALTER TYPE
CREATE TYPE
ALTER TYPE
CREATE TYPE
ALTER TYPE
CREATE TYPE
ALTER TYPE
ERROR: type "tsvector" is only a shell <<===== this is wrong
ERROR: type "public.tsdebug" does not exist

>
>> a) server crash after creating tsearch2 function (I use tsearch2
>> contrib from 8.3)
>
> I couldn't reproduce that with the script you gave.
>
> regards, tom lane
>

I tested it on fe8, 32 bit without problem, so it's maybe related to 64bit.

Regards
Pavel Stehule

"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
> 2008/11/4 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
>>> a) server crash after creating tsearch2 function (I use tsearch2
>>> contrib from 8.3)
>>
>> I couldn't reproduce that with the script you gave.

> I tested it on fe8, 32 bit without problem, so it's maybe related to 64bit.

Can't reproduce it on 64-bit either. Looking closer, I don't believe
that you were running this script at all --- the crash backtrace
includes

#14 0x00000000005e0a0c in exec_simple_query (
query_string=0x1f69ae8 "CREATE FUNCTION plpgsql_call_handler()
RETURNS language_handler\n AS '/usr/lib64/pgsql/plpgsql',
'plpgsql_call_handler'\n LANGUAGE c;") at postgres.c:986

and there is no such command in this script.

Something else weird I just noticed: in this script, you've got

--
-- Name: position; Type: TABLE; Schema: public; Owner: lmc; Tablespace:
--

CREATE TABLE "position" (
id integer NOT NULL,
title character varying(255) NOT NULL,
description text,
fulltext_index tsvector
);

ALTER TABLE public."position" OWNER TO lmc;

--
-- Name: view_position_uniq; Type: VIEW; Schema: public; Owner: lmc
--

CREATE VIEW view_position_uniq AS
SELECT p.uniq_key FROM "position" p;

The CREATE VIEW fails because there's no uniq_key column in "position".
Either you edited this script before sending it in, or there's something
a bit broken about pg_dump or the database you dumped from.

regards, tom lane

2008/11/5 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2008/11/4 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>>> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
>>>> a) server crash after creating tsearch2 function (I use tsearch2
>>>> contrib from 8.3)
>>>
>>> I couldn't reproduce that with the script you gave.
>
>> I tested it on fe8, 32 bit without problem, so it's maybe related to 64bit.
>
> Can't reproduce it on 64-bit either. Looking closer, I don't believe
> that you were running this script at all --- the crash backtrace
> includes
>
> #14 0x00000000005e0a0c in exec_simple_query (
> query_string=0x1f69ae8 "CREATE FUNCTION plpgsql_call_handler()
> RETURNS language_handler\n AS '/usr/lib64/pgsql/plpgsql',
> 'plpgsql_call_handler'\n LANGUAGE c;") at postgres.c:986
>
> and there is no such command in this script.

I should send complet script on your private address. It has 2MB.

>
> Something else weird I just noticed: in this script, you've got
>
> --
> -- Name: position; Type: TABLE; Schema: public; Owner: lmc; Tablespace:
> --
>
> CREATE TABLE "position" (
> id integer NOT NULL,
> title character varying(255) NOT NULL,
> description text,
> fulltext_index tsvector
> );
>
>

it's strange - you have different error. Script was truncated, but I
have error some time before.

ALTER FUNCTION
SET
SET
ERROR: type "tsvector" is only a shell
LINE 5: fulltext_index tsvector
^
that is wrong. type tsvector is normal type registrated from tsearch2 module.

regards
Pavel Stehule

> ALTER TABLE public."position" OWNER TO lmc;
>
> --
> -- Name: view_position_uniq; Type: VIEW; Schema: public; Owner: lmc
> --
>
> CREATE VIEW view_position_uniq AS
> SELECT p.uniq_key FROM "position" p;
>
>
> The CREATE VIEW fails because there's no uniq_key column in "position".
> Either you edited this script before sending it in, or there's something
> a bit broken about pg_dump or the database you dumped from.
>
> regards, tom lane
>