Re: User function canceling VACUUMDB utility

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Carlos Henrique Reimer <carlos(dot)reimer(at)opendb(dot)com(dot)br> 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(dot)reimer(at)opendb(dot)com(dot)br