Re: pg_dump issue : Cannot drop a non-existent(?) trigger

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I've Google'd for the situation below and could not find any solution.

We are using PostgreSQL 8.0.1, installed using PGDG RPMs on RHEL ES 3.0:

=======================================================================
prod=# SELECT version();
version
- --------------------------------------------------------------------------------------------------------------
PostgreSQL 8.0.1 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
3.2.3 20030502 (Red Hat Linux 3.2.3-42)
=======================================================================

We wanted to take a full backup, so ran pg_dump,but got an error:

=======================================================================
$ pg_dump prod -U postgres > all.pgdump
pg_dump: invalid argument string (firma_moduller_firma_fkey1) for trigger
"RI_ConstraintTrigger_39053" on table "t_firma_moduller"
=======================================================================

Looking at the details, we saw the trigger there:

=======================================================================
prod=# \d t_firma_moduller
Table
"public.t_firma_moduller"
Column | Type |
Modifiers
- ------------------+-----------------------------+--------------------------------------------------------------------------
firma_no | character varying(10) | not null
modul_adi | character varying(20) | not null
last_update_date | timestamp without time zone |
last_update_user | character varying(45) |
kod | integer | not null default
fn_get_seq_value('t_firma_moduller'::character varying)
Indexes:
"t_firma_moduller_pkey" PRIMARY KEY, btree (firma_no, modul_adi)
"t_firma_moduller_kodu_ukey" UNIQUE, btree (kod)
Triggers:
"RI_ConstraintTrigger_39053" AFTER INSERT OR UPDATE ON
t_firma_moduller FROM t_firmalar NOT DEFERRABLE INITIALLY IMMEDIATE FOR
EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins"('firma_moduller_firma_fkey1', 't_firma_moduller',
't_firmalar', 'UNSPECIFIED', 'firma_no', 'no')
Tablespace: "data_ts"
=======================================================================

However, pgadmin3 can't see that. That is more possible since we can't
drop them:

prod=# DROP TRIGGER RI_ConstraintTrigger_39053 ON t_firma_moduller;
ERROR: trigger "ri_constrainttrigger_39053" for table "t_firma_moduller"
does not exist

Is this a bug or something? There should not be a trigger there.

Regards,
- --
Devrim GUNDUZ
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFCQ+17tl86P3SPfQ4RAhZKAKCTEzLTizwyt+Zomgv3ikiussyaSQCgtDdD
Q3Ua6lJChMT43p2x+bo0rPY=
=s4g2
-----END PGP SIGNATURE-----

On Fri, 25 Mar 2005, Devrim GUNDUZ wrote:

> - --------------------------------------------------------------------------------------------------------------
> PostgreSQL 8.0.1 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
> 3.2.3 20030502 (Red Hat Linux 3.2.3-42)
> =======================================================================
>
> We wanted to take a full backup, so ran pg_dump,but got an error:
>
> =======================================================================
> $ pg_dump prod -U postgres > all.pgdump
> pg_dump: invalid argument string (firma_moduller_firma_fkey1) for trigger
> "RI_ConstraintTrigger_39053" on table "t_firma_moduller"
> =======================================================================
>
> Looking at the details, we saw the trigger there:
>
> =======================================================================
> prod=# \d t_firma_moduller
> Table
> "public.t_firma_moduller"
> Column | Type |
> Modifiers
> - ------------------+-----------------------------+--------------------------------------------------------------------------
> firma_no | character varying(10) | not null
> modul_adi | character varying(20) | not null
> last_update_date | timestamp without time zone |
> last_update_user | character varying(45) |
> kod | integer | not null default
> fn_get_seq_value('t_firma_moduller'::character varying)
> Indexes:
> "t_firma_moduller_pkey" PRIMARY KEY, btree (firma_no, modul_adi)
> "t_firma_moduller_kodu_ukey" UNIQUE, btree (kod)
> Triggers:
> "RI_ConstraintTrigger_39053" AFTER INSERT OR UPDATE ON
> t_firma_moduller FROM t_firmalar NOT DEFERRABLE INITIALLY IMMEDIATE FOR
> EACH ROW EXECUTE PROCEDURE
> "RI_FKey_check_ins"('firma_moduller_firma_fkey1', 't_firma_moduller',
> 't_firmalar', 'UNSPECIFIED', 'firma_no', 'no')

Odd, why is this not showing up as a foreign key constraint? Has this
database been upgraded (no matter through how many different upgrades)
from an old version (7.1 or earlier I think)?

> prod=# DROP TRIGGER RI_ConstraintTrigger_39053 ON t_firma_moduller;
> ERROR: trigger "ri_constrainttrigger_39053" for table "t_firma_moduller"
> does not exist

You'd need to quote the trigger name because it's mixed case.

> Is this a bug or something? There should not be a trigger there.

Well, it really should be showing up as something like:
CONSTRAINT firma_moduller_firma_fkey1 FOREIGN KEY (firma_no)
REFERENCES t_firmalar(no)

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> Odd, why is this not showing up as a foreign key constraint? Has this
> database been upgraded (no matter through how many different upgrades)
> from an old version (7.1 or earlier I think)?

And the other odd thing is that it fails to dump it as a trigger either.
The "invalid argument string" complaint indicates that pg_dump didn't
find the right number of "\000" sequences in the tgargs value; but
pg_get_triggerdef evidently is finding the right number of zero bytes.
It almost looks like the tgargs value got coerced from bytea to text
somewhere between the catalogs and pg_dump, but how'd that happen?

It'd be good to look at the trigger row directly:
select * from pg_trigger where tgname = 'RI_ConstraintTrigger_39053';

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

On Fri, 25 Mar 2005, Stephan Szabo wrote:

>> =======================================================================
>> prod=# \d t_firma_moduller
>> Table
>> "public.t_firma_moduller"
>> Column | Type |
>> Modifiers
>> - ------------------+-----------------------------+--------------------------------------------------------------------------
>> firma_no | character varying(10) | not null
>> modul_adi | character varying(20) | not null
>> last_update_date | timestamp without time zone |
>> last_update_user | character varying(45) |
>> kod | integer | not null default
>> fn_get_seq_value('t_firma_moduller'::character varying)
>> Indexes:
>> "t_firma_moduller_pkey" PRIMARY KEY, btree (firma_no, modul_adi)
>> "t_firma_moduller_kodu_ukey" UNIQUE, btree (kod)
>> Triggers:
>> "RI_ConstraintTrigger_39053" AFTER INSERT OR UPDATE ON
>> t_firma_moduller FROM t_firmalar NOT DEFERRABLE INITIALLY IMMEDIATE FOR
>> EACH ROW EXECUTE PROCEDURE
>> "RI_FKey_check_ins"('firma_moduller_firma_fkey1', 't_firma_moduller',
>> 't_firmalar', 'UNSPECIFIED', 'firma_no', 'no')
>
> Odd, why is this not showing up as a foreign key constraint? Has this
> database been upgraded (no matter through how many different upgrades)
> from an old version (7.1 or earlier I think)?

No, this is a fresh install. The database went live about 1 month ago.

>> prod=# DROP TRIGGER RI_ConstraintTrigger_39053 ON t_firma_moduller;
>> ERROR: trigger "ri_constrainttrigger_39053" for table "t_firma_moduller"
>> does not exist
>
> You'd need to quote the trigger name because it's mixed case.

:-( I should have thought it. Thanks.

>> Is this a bug or something? There should not be a trigger there.
>
> Well, it really should be showing up as something like:
> CONSTRAINT firma_moduller_firma_fkey1 FOREIGN KEY (firma_no)
> REFERENCES t_firmalar(no)

The problem is:

* We ran pg_dump about 15 days before with no problem. Neither any data
nor any relation was dropped since the cluster was initialized. If the
trigger was there, how could we run pg_dump successfully? If it isn't
there, why does it prevent pg_dump?

Regards,
- --
Devrim GUNDUZ
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFCRCpPtl86P3SPfQ4RAjJCAJ9RuDrfeS78cNlJABvybhori6Cm6ACgtaxr
p79z+3ty4SFTDnlaUK+ZdUM=
=vaRH
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi Tom,

On Fri, 25 Mar 2005, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
>> Odd, why is this not showing up as a foreign key constraint? Has this
>> database been upgraded (no matter through how many different upgrades)
>> from an old version (7.1 or earlier I think)?
>
> And the other odd thing is that it fails to dump it as a trigger either.
> The "invalid argument string" complaint indicates that pg_dump didn't
> find the right number of "\000" sequences in the tgargs value; but
> pg_get_triggerdef evidently is finding the right number of zero bytes.
> It almost looks like the tgargs value got coerced from bytea to text
> somewhere between the catalogs and pg_dump, but how'd that happen?
>
> It'd be good to look at the trigger row directly:
> select * from pg_trigger where tgname = 'RI_ConstraintTrigger_39053';

Here it is:

prod=# select * from pg_trigger where tgname = 'RI_ConstraintTrigger_39053';
- -[ RECORD 1 ]--+---------------------------
tgrelid | 37564
tgname | RI_ConstraintTrigger_39053
tgfoid | 1644
tgtype | 21
tgenabled | t
tgisconstraint | t
tgconstrname | firma_moduller_firma_fkey1
tgconstrrelid | 37577
tgdeferrable | f
tginitdeferred | f
tgnargs | 6
tgattr |
tgargs | firma_moduller_firma_fkey1

Regards,
- --
Devrim GUNDUZ
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFCRCsQtl86P3SPfQ4RAij+AJ4+p+Q9WrVTIo8xWR2YMWlgj9EoXgCgvhK8
n7CpjIeR54dcOyMg8hlb6w0=
=efYi
-----END PGP SIGNATURE-----

Devrim GUNDUZ <devrim(at)gunduz(dot)org> writes:
> prod=# select * from pg_trigger where tgname = 'RI_ConstraintTrigger_39053';
> - -[ RECORD 1 ]--+---------------------------
> tgrelid | 37564
> tgname | RI_ConstraintTrigger_39053
> tgfoid | 1644
> tgtype | 21
> tgenabled | t
> tgisconstraint | t
> tgconstrname | firma_moduller_firma_fkey1
> tgconstrrelid | 37577
> tgdeferrable | f
> tginitdeferred | f
> tgnargs | 6
> tgattr |
> tgargs | firma_moduller_firma_fkey1

Well, that's consistent with what pg_dump said the tgargs value is;
but it ought to look like
firma_moduller_firma_fkey1\000t_firma_moduller\000t_firmalar\000UNSPECIFIED\000firma_no\000no\000
and the output from \d proves that that data is actually stored. So why
isn't it being displayed??

Does "\d pg_trigger" show that the tgargs column is of type bytea?

Also, get the OID for this pg_trigger row and see if it shows up in
objid or refobjid of any rows of pg_depend.

It certainly seems that your system catalogs have been clobbered in
some odd way :-(

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

On Fri, 25 Mar 2005, Tom Lane wrote:

> Devrim GUNDUZ <devrim(at)gunduz(dot)org> writes:
>> prod=# select * from pg_trigger where tgname = 'RI_ConstraintTrigger_39053';
>> - -[ RECORD 1 ]--+---------------------------
>> tgrelid | 37564
>> tgname | RI_ConstraintTrigger_39053
>> tgfoid | 1644
>> tgtype | 21
>> tgenabled | t
>> tgisconstraint | t
>> tgconstrname | firma_moduller_firma_fkey1
>> tgconstrrelid | 37577
>> tgdeferrable | f
>> tginitdeferred | f
>> tgnargs | 6
>> tgattr |
>> tgargs | firma_moduller_firma_fkey1
>
> Well, that's consistent with what pg_dump said the tgargs value is;
> but it ought to look like
> firma_moduller_firma_fkey1\000t_firma_moduller\000t_firmalar\000UNSPECIFIED\000firma_no\000no\000
> and the output from \d proves that that data is actually stored. So why
> isn't it being displayed??
>
> Does "\d pg_trigger" show that the tgargs column is of type bytea?

Umm no:

tgnargs | smallint | not null

> Also, get the OID for this pg_trigger row and see if it shows up in
> objid or refobjid of any rows of pg_depend

Yes it is there

prod=# SELECT * from pg_depend WHERE objid =39053;
- -[ RECORD 1 ]------
classid | 16412
objid | 39053
objsubid | 0
refclassid | 1259
refobjid | 37564
refobjsubid | 0
deptype | a
- -[ RECORD 2 ]------
classid | 16412
objid | 39053
objsubid | 0
refclassid | 1259
refobjid | 37577
refobjsubid | 0
deptype | a

So... Is it with tgnargs issue? :(

Regards,

- --
Devrim GUNDUZ
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFCRFYUtl86P3SPfQ4RAk1LAJ4wW13o7n29Fr52SY5/EbqNysh3PACgznyh
6RLdbTufPwP+vuHXc3+h1WE=
=AOBb
-----END PGP SIGNATURE-----

Devrim GUNDUZ <devrim(at)gunduz(dot)org> writes:
> On Fri, 25 Mar 2005, Tom Lane wrote:
>> Does "\d pg_trigger" show that the tgargs column is of type bytea?

> Umm no:

> tgnargs | smallint | not null

tgargs, not tgnargs.

>> Also, get the OID for this pg_trigger row and see if it shows up in
>> objid or refobjid of any rows of pg_depend

> Yes it is there

> prod=# SELECT * from pg_depend WHERE objid =39053;
> - -[ RECORD 1 ]------
> classid | 16412
> objid | 39053
> objsubid | 0
> refclassid | 1259
> refobjid | 37564
> refobjsubid | 0
> deptype | a
> - -[ RECORD 2 ]------
> classid | 16412
> objid | 39053
> objsubid | 0
> refclassid | 1259
> refobjid | 37577
> refobjsubid | 0
> deptype | a

Hmph. Those should be 'i' references to the foreign key constraint,
not 'a' references to the relations. I suspect this database was
carried forward from an ancient (pre-7.3) dump that defined the triggers
by "CREATE CONSTRAINT TRIGGER" instead of "ALTER ADD FOREIGN KEY".
Have you ever run contrib/adddepend to update the definitions to be
proper constraints?

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

On Fri, 25 Mar 2005, Tom Lane wrote:

>>> Does "\d pg_trigger" show that the tgargs column is of type bytea?
>
>> Umm no:
>
>> tgnargs | smallint | not null
>
> tgargs, not tgnargs.

Ooops, sorry. Ok, tgargs is of type bytea.

>>> Also, get the OID for this pg_trigger row and see if it shows up in
>>> objid or refobjid of any rows of pg_depend
>
>> Yes it is there
>
>> prod=# SELECT * from pg_depend WHERE objid =39053;
>> - -[ RECORD 1 ]------
>> classid | 16412
>> objid | 39053
>> objsubid | 0
>> refclassid | 1259
>> refobjid | 37564
>> refobjsubid | 0
>> deptype | a
>> - -[ RECORD 2 ]------
>> classid | 16412
>> objid | 39053
>> objsubid | 0
>> refclassid | 1259
>> refobjid | 37577
>> refobjsubid | 0
>> deptype | a
>
> Hmph. Those should be 'i' references to the foreign key constraint,
> not 'a' references to the relations. I suspect this database was
> carried forward from an ancient (pre-7.3) dump that defined the triggers
> by "CREATE CONSTRAINT TRIGGER" instead of "ALTER ADD FOREIGN KEY".

I haven't coded the application but AFAIKit was developed using 7.4 and
7.5(CVS); and we installed database on 8.0.1... This is a new app.

As a reminder, we ran pg_dump successfully before. After then we did
nothing on schemas, we didn't upgrade db server, etc.

> Have you ever run contrib/adddepend to update the definitions to be
> proper constraints?

Now I did, but the found constraints are not related to our problem... :(

I'll try to drop trigger on Monday night and see what will happen. We have
no up2date backup, except WAL logs... :(

Regards,

- --
Devrim GUNDUZ
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFCRTYGtl86P3SPfQ4RAjn7AKDrz+t6gsc53EAQ9UZAfAmgpZUwVACg532w
7c61IvIL5e2AjRg+5jV1BVw=
=Um2T
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

On Sat, 26 Mar 2005, Devrim GUNDUZ wrote:

>> Hmph. Those should be 'i' references to the foreign key constraint,
>> not 'a' references to the relations. I suspect this database was
>> carried forward from an ancient (pre-7.3) dump that defined the triggers
>> by "CREATE CONSTRAINT TRIGGER" instead of "ALTER ADD FOREIGN KEY".
>
> I haven't coded the application but AFAIKit was developed using 7.4 and
> 7.5(CVS); and we installed database on 8.0.1... This is a new app.

Ok, sorry for the wrong info. I learned that they had begun coding the app
on 7.2, then moved to 7.3->7.4->8.0... You are right.

>> Have you ever run contrib/adddepend to update the definitions to be
>> proper constraints?

Ok, running adddepend solved our problem (in fact it produced some erros
related to perl but we ran the queries inside psql and fixed the problem).
Now we can run pg_dump.

Thanks all.

Regards,
- --
Devrim GUNDUZ
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFCRwoItl86P3SPfQ4RApMRAKCv7EZ1yd9UwBMLbLTBgju3pqcBzACfSYqI
z2m1MsjhQWzeezM3WNKADgQ=
=c3Fv
-----END PGP SIGNATURE-----