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
Carlos Henrique Reimer <carlos.reimer@opendb.com.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
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@sss.pgh.pa.us> wrote:
Carlos Henrique Reimer <carlos.reimer@opendb.com.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 typename
"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@opendb.com.br
Carlos Henrique Reimer <carlos.reimer@opendb.com.br> writes:
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?
I think your problem is probably a search_path issue, ie vacuumdb is not
running with the "BRASIL" schema in its path so the column reference
fails to resolve. You should be able to add the schema name to the
%TYPE reference. Or, if you can't make that work, just don't use
%TYPE...
regards, tom lane
Hi,
Yes, once correct schema was included in the search_path, VACUUM and ANALYZE
run fine again.
Thank you!
On Fri, Sep 10, 2010 at 11:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Carlos Henrique Reimer <carlos.reimer@opendb.com.br> writes:
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?I think your problem is probably a search_path issue, ie vacuumdb is not
running with the "BRASIL" schema in its path so the column reference
fails to resolve. You should be able to add the schema name to the
%TYPE reference. Or, if you can't make that work, just don't use
%TYPE...regards, tom lane
--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br
Carlos Henrique Reimer <carlos.reimer@opendb.com.br> writes:
Yes, once correct schema was included in the search_path, VACUUM and ANALYZE
run fine again.
You'd be better advised to fix the function so it works regardless of
caller's search_path. As-is, it's a loaded gun pointed at your foot.
regards, tom lane