Re: User function canceling VACUUMDB utility

[Carlos Henrique Reimer <carlos.reimer@xxxxxxxxxxxxx>]

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()

 

 

On Thu, Sep 9, 2010 at 10:46 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Carlos Henrique Reimer <carlos.reimer@xxxxxxxxxxxxx> writes:
> We are facing the following problem in a PG 8.2 server when trying to vacuum
> one of our databases:

> vacuumdb: vacuuming of database "reimer" failed: ERROR:  invalid type name
> "TT_TIT.SEQCAN%TYPE"

> [ which seems to be coming from out-of-date code in a function ]

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

Perhaps you have a functional index that calls that function?  If
so, ANALYZE would probably try to call the function too.

                       regards, tom lane

-- 
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@xxxxxxxxxxxxx