Обсуждение: User function canceling VACUUMDB utility

Hi,

 

We are facing the following problem in a PG 8.2 server when trying to vacuum one of our databases:

 

vacuumdb: vacuuming database "reimer"
INFO:  vacuuming "pg_catalog.pg_database"
INFO:  "pg_database": found 0 removable, 6 nonremovable row versions in 1 pages
INFO:  index "pg_database_datname_index" now contains 6 row versions in 2 pages
INFO:  index "pg_database_oid_index" now contains 6 row versions in 2 pages
INFO:  "pg_database": moved 0 row versions, truncated 1 to 1 pages
INFO:  analyzing "pg_catalog.pg_database"
INFO:  "pg_database": scanned 1 of 1 pages, containing 6 live rows and 0 dead rows; 6 rows in sample, 6 estimated total rows
vacuumdb: vacuuming of database "reimer" failed: ERROR:  invalid type name "TT_TIT.SEQCAN%TYPE"
[root@serverdb backup]#

I dumped the database and found the following function using the "TT_TIT.SEQCAN%TYPE" type name.

 

CREATE FUNCTION fn_uq_tit_rec_seqcan(character, character, character, character) RETURNS character
    AS $_$
DECLARE
pFILREC ALIAS FOR $1 ;
pSEQREC ALIAS FOR $2 ;
pPARREC ALIAS FOR $3 ;
pSEQCAN ALIAS FOR $4 ;
output VARCHAR(1000);
SEQCAN TT_TIT.SEQCAN%TYPE;
begin
 IF pSEQCAN IS NULL THEN
   SEQCAN := ProximoCodigo('TT_TIT',pFILREC);   
 END IF;
 output := pFILREC||pSEQREC||pPARREC||COALESCE(pSEQCAN,SEQCAN);
 return (output);
end;
$_$
    LANGUAGE plpgsql IMMUTABLE;

If I drop the function the vacuumdb runs fine but I'm wondering how a funciton can cancel the vacuumdb utility.

 

I'm not a function specialist but is there anything we can try to make vacuumdb run with this function created?

 

Thank you!
 
--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br

Hi,

 

Yes, you're right! I found out a functional index using this function and ANALYZE also cancels.

 

Is there a way to code this function in a way VACUUM/ANALYZE does not cancel?

 

Thank you!

 

brasil=# analyze "BRASIL".tt_tit;
ERROR:  invalid type name "TT_TIT.SEQCAN%TYPE"
CONTEXT:  compile of PL/pgSQL function "fn_uq_tit_rec_seqcan" near line 7
brasil=#

brasil=# \d "BRASIL".tt_tit
                             Table "BRASIL.tt_tit"
 Column |            Type             |                Modifiers
--------+-----------------------------+-----------------------------------------
 codfil | character(3)                | not null
 sequen | character(10)               | not null
 flgest | character(1)                | not null default 'A'::bpchar
 parrec | character(2)                | not null
 subrec | character(2)                | not null
 filrec | character(3)                | not null
 seqrec | character(10)               | not null
 vlrori | numeric(12,2)               | not null
 aceite | character varying(1)        | not null default 'F'::character varying
 coderr | character(1)                |
 digcob | character(1)                |
 codbco | character(3)                |
 numage | character(5)                |
 ctacrr | character(10)               |
 seqcan | character(10)               |
 datdes | timestamp without time zone |
 datmor | timestamp without time zone |
 codbai | numeric(1,0)                |
 coddes | numeric(1,0)                |
 codmor | numeric(1,0)                |
 codprt | numeric(1,0)                |
 przbai | numeric(2,0)                |
 przprt | numeric(2,0)                |
 agecob | numeric(5,0)                |
 codccb | numeric(5,0)                |
 codecb | numeric(5,0)                |
 codrem | numeric(5,0)                |
 vlrabt | numeric(12,4)               |
 vlriof | numeric(12,4)               |
 jurdes | numeric(15,2)               |
 jurmor | numeric(15,2)               |
 dummy  | character varying(1)        |
 nosnum | character varying(20)       |
 datven | timestamp without time zone | not null
 portad | numeric(5,0)                | not null
 vlrpar | numeric(12,2)               | not null
Indexes:
    "pk_tit" PRIMARY KEY, btree (codfil, sequen)
    "i_uq_tit_rec_seqcan" UNIQUE, btree ("BRASIL".fn_uq_tit_rec_seqcan(filrec, s
eqrec, parrec, seqcan))
    "i_fk_tit_ctb" btree (codbco, numage, ctacrr)
    "i_fk_tit_dccb" btree (codccb)
    "i_fk_tit_decb" btree (codecb)
    "i_fk_tit_drem" btree (codrem)
    "i_fk_tit_rec" btree (filrec, seqrec, parrec, subrec)
    "i_fk_tt_tit" btree (filrec, seqrec, parrec, subrec)
    "i_lc_tit_nosnum" btree (nosnum)
    "i_lc_tit_rec_seqcan" btree (filrec, seqrec, parrec, seqcan)
Check constraints:
    "ck_tit_aceite" CHECK (aceite::text = 'T'::character varying::text OR aceite
::text = 'F'::character varying::text)
    "ck_tit_coderr" CHECK (coderr = 'A'::bpchar OR coderr = 'B'::bpchar OR coder
r = 'C'::bpchar OR coderr = 'D'::bpchar OR coderr = 'E'::bpchar OR coderr = 'F':
:bpchar OR coderr = 'G'::bpchar OR coderr = 'H'::bpchar OR coderr = 'I'::bpchar
OR coderr = 'J'::bpchar OR coderr = 'K'::bpchar OR coderr = 'L'::bpchar OR coder
r = 'M'::bpchar OR coderr = 'N'::bpchar OR coderr = 'O'::bpchar OR coderr = 'P':
:bpchar OR coderr = 'Q'::bpchar OR coderr = 'R'::bpchar OR coderr = 'S'::bpchar)
    "ck_tit_flgest" CHECK (flgest = 'A'::bpchar OR flgest = 'C'::bpchar OR flges
t = 'P'::bpchar OR flgest = 'I'::bpchar OR flgest = 'T'::bpchar)
Foreign-key constraints:
    "fk_tit_ctb" FOREIGN KEY (codbco, numage, ctacrr) REFERENCES "BRASIL".tt_ctb
(codbco, numage, ctacrr)
    "fk_tit_dccb" FOREIGN KEY (codccb) REFERENCES "BRASIL".td_ccb(codtab)
    "fk_tit_decb" FOREIGN KEY (codecb) REFERENCES "BRASIL".td_ecb(codtab)
    "fk_tit_drem" FOREIGN KEY (codrem) REFERENCES "BRASIL".td_rem(codtab)
    "fk_tit_rec" FOREIGN KEY (filrec, seqrec, parrec, subrec) REFERENCES "BRASIL
".tt_rec(codfil, sequen, numpar, subpar)
Triggers:
    _testenull_tt_tit BEFORE INSERT OR UPDATE ON "BRASIL".tt_tit FOR EACH ROW EX
ECUTE PROCEDURE "BRASIL".tr_testenull_tt_tit()
    tgtit2 BEFORE INSERT OR DELETE OR UPDATE ON "BRASIL".tt_tit FOR EACH ROW EXE
CUTE PROCEDURE "BRASIL".tgtit2()
    tgtit3 AFTER INSERT OR DELETE OR UPDATE ON "BRASIL".tt_tit FOR EACH ROW EXEC
UTE PROCEDURE "BRASIL".tgtit3()

 

 

Hi,

 

Yes, once correct schema was included in the search_path, VACUUM and ANALYZE run fine again.

 

Thank you!