pg_dump:cannot find function for trigger

Started by Claudio Jose Zanardi Grilloover 25 years ago3 messagesgeneral
Jump to latest

Hello:

After creating the tables tb_bombas and tb_abastecimentos
as follows

\connect f
drop table tb_bombas;
CREATE TABLE "tb_bombas" (
"id_bomba" int2 PRIMARY KEY,
"tipo_bomba" char,
"estado_bomba" int2,
"data_atualizacao" datetime);
CREATE TABLE "tb_abastecimentos" (
"id_bomba" int2 references tb_bombas(id_bomba),
"data_abastecimento" datetime NOT NULL,
"volume" int4,
"valor" int4,
"estado" character,
constraint pk_abastecimentos primary
key(id_bomba,data_abastecimento)
);

and using pg_dump , I got the following error message :

-- last builtin oid is 17216
-- reading user-defined types
-- reading user-defined functions
-- reading user-defined aggregates
-- reading user-defined operators
-- reading user-defined tables
-- finding Triggers for relation: 'tb_bombas'
getTables(): relation 'tb_bombas': cannot find function with oid 1654
for trigger RI_ConstraintTrigger_28974

But I can find de procedure with oid 1654 in the pg_proc, and the
reference to this function in pg_trigger:

pg_trigger

tgrelid|tgname
|tgfoid|tgtype|tgenabled|tgisconstraint|tgconstrname|tgconstrrelid|tgdeferrable|tginitdeferred|tgnargs|tgattr|tgargs

-------+--------------------------+------+------+---------+--------------+------------+-------------+------------+--------------+-------+------+--------------------------------------------------------------------------------------

28928|RI_ConstraintTrigger_28974| 1654| 9|t |t
|<unnamed> | 28956|f |f | 6|
|<unnamed>\000tb_abastecimentos\000tb_bombas\000UNSPECIFIED\000id_bomba\000id_bomba\000

(1 row)

pg_proc
proname
|proowner|prolang|proisinh|proistrusted|proiscachable|pronargs|proretset|prorettype|proargtypes|probyte_pct|properbyte_cpu|propercall_cpu|prooutin_ratio|prosrc

|probin
--------------------+--------+-------+--------+------------+-------------+--------+---------+----------+-----------+-----------+--------------+--------------+--------------+--------------------+------

RI_FKey_noaction_del| 40| 11|f |t |f
| 0|f | 0| | 100|
0| 0| 100|RI_FKey_noaction_del|-
(1 row)

I can't dump my database because the error makes pg_dump to exit.
So, i will be grateful for any help.

Thanks.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Claudio Jose Zanardi Grillo (#1)
Re: pg_dump:cannot find function for trigger

Claudio Grillo <cjg@certi.ufsc.br> writes:

getTables(): relation 'tb_bombas': cannot find function with oid 1654
for trigger RI_ConstraintTrigger_28974

But I can find de procedure with oid 1654 in the pg_proc, and the
reference to this function in pg_trigger:

pg_proc
proname
|proowner|prolang|proisinh|proistrusted|proiscachable|pronargs|proretset|prorettype|proargtypes|probyte_pct|properbyte_cpu|propercall_cpu|prooutin_ratio|prosrc

|probin
--------------------+--------+-------+--------+------------+-------------+--------+---------+----------+-----------+-----------+--------------+--------------+--------------+--------------------+------

RI_FKey_noaction_del| 40| 11|f |t |f
| 0|f | 0| | 100|
0| 0| 100|RI_FKey_noaction_del|-
(1 row)

Hmm. Do you have a user with sysid 40 in pg_shadow? pg_dump tends to
get confused by 'owner' links that point to nonexistent users.

regards, tom lane

In reply to: Tom Lane (#2)
Re: pg_dump:cannot find function for trigger

Hi :

I was using the wrong version(6.5.2) of pg_dump,that was
in
my PATH !!! With the correct version(7.0.2) the dump was
OK !!

Thanks !!
Claudio Grillo

Quoting Tom Lane <tgl@sss.pgh.pa.us>:

Claudio Grillo <cjg@certi.ufsc.br> writes:

getTables(): relation 'tb_bombas': cannot find

function with oid 1654

for trigger RI_ConstraintTrigger_28974

But I can find de procedure with oid 1654 in the

pg_proc, and the

reference to this function in pg_trigger:

pg_proc
proname

|proowner|prolang|proisinh|proistrusted|proiscachable|pronargs|proretset|prorettype|proargtypes|probyte_pct|properbyte_cpu|propercall_cpu|prooutin_ratio|prosrc

|probin

--------------------+--------+-------+--------+------------+-------------+--------+---------+----------+-----------+-----------+--------------+--------------+--------------+--------------------+------

RI_FKey_noaction_del| 40| 11|f

|t |f

| 0|f | 0| |

100|

0| 0|

100|RI_FKey_noaction_del|-

(1 row)

Hmm. Do you have a user with sysid 40 in pg_shadow?

pg_dump tends to

get confused by 'owner' links that point to

nonexistent users.

Show quoted text

regards, tom lane