Thread: Trigger trouble
Hi All,
I get the following error when performing a pg_dump
dumping database "ltt"...
pg_dump: WARNING: owner of data type trigger appears to be invalid
the owner and database exists, I have run the following select statement,
select pg_class.oid, pg_trigger.tgrelid from pg_trigger left join pg_class
on pg_trigger.tgrelid=pg_class.oid;
oid | tgrelid
---------+---------
1260 | 1260
6081779 | 6081779
6081981 | 6081981
6081779 | 6081779
6081779 | 6081779
6081981 | 6081981
6081975 | 6081975
6081975 | 6081975
6082027 | 6082027
6081779 | 6081779
6081779 | 6081779
6082027 | 6082027
6121607 | 6121607
6121268 | 6121268
6121268 | 6121268
so it appears that it is not a dropped table causing the warning, is there
any other way I can try to determine how this warning is being produced, is
there a way of viewing the contents of the trigger?
Thanks in advance,
Sandy
Computer Officer, RA Certification Manager
Department of Computer Science - UWA
Llandinam Building
Penglais Campus
Aberystwyth
Ceredigion
Wales - UK
SY23 3DB
Tel: (01970)-622433
Fax: (01970)-628536
Sandy, SELECT typowner from pg_type where typname='trigger'; check if typeowner exists in the column usesysid of pg_user by doing SELECT * from pg_user ; if typeowner does not exists there is a problem. regds On 12/21/06, Sandy Spence <axs@aber.ac.uk> wrote: > Hi All, > > I get the following error when performing a pg_dump > > dumping database "ltt"... > pg_dump: WARNING: owner of data type trigger appears to be invalid > > the owner and database exists, I have run the following select statement, > > select pg_class.oid, pg_trigger.tgrelid from pg_trigger left join pg_class > on pg_trigger.tgrelid=pg_class.oid; > > oid | tgrelid > ---------+--------- > 1260 | 1260 > 6081779 | 6081779 > 6081981 | 6081981 > 6081779 | 6081779 > 6081779 | 6081779 > 6081981 | 6081981 > 6081975 | 6081975 > 6081975 | 6081975 > 6082027 | 6082027 > 6081779 | 6081779 > 6081779 | 6081779 > 6082027 | 6082027 > 6121607 | 6121607 > 6121268 | 6121268 > 6121268 | 6121268 > > so it appears that it is not a dropped table causing the warning, is there > any other way I can try to determine how this warning is being produced, is > there a way of viewing the contents of the trigger? > > Thanks in advance, > > Sandy > > Computer Officer, RA Certification Manager > Department of Computer Science - UWA > Llandinam Building > Penglais Campus > Aberystwyth > Ceredigion > Wales - UK > SY23 3DB > Tel: (01970)-622433 > Fax: (01970)-628536 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate >
Hi Rajesh, I ran the select query SELECT typowner from pg_type where typname='trigger'; with the following results select typowner from pg_type where typname='trigger'; typowner ---------- (0 rows) I then ran the second select query SELECT * from pg_user; (I also changed slightly select usename, usesysid from pg_user where usename='ltt';) usename | usesysid ---------+---------- ltt | 3517 (1 row) user/database is where the warning message for the trigger originates from. Is there something significant in the first select statement. Regards, Sandy Computer Officer, RA Certification Manager Department of Computer Science - UWA Llandinam Building Penglais Campus Aberystwyth Ceredigion Wales - UK SY23 3DB Tel: (01970)-622433 Fax: (01970)-628536 -----Original Message----- From: Rajesh Kumar Mallah [mailto:mallah.rajesh@gmail.com] Sent: 21 December 2006 20:04 To: Sandy Spence Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Trigger trouble Sandy, SELECT typowner from pg_type where typname='trigger'; check if typeowner exists in the column usesysid of pg_user by doing SELECT * from pg_user ; if typeowner does not exists there is a problem. regds On 12/21/06, Sandy Spence <axs@aber.ac.uk> wrote: > Hi All, > > I get the following error when performing a pg_dump > > dumping database "ltt"... > pg_dump: WARNING: owner of data type trigger appears to be invalid > > the owner and database exists, I have run the following select > statement, > > select pg_class.oid, pg_trigger.tgrelid from pg_trigger left join > pg_class on pg_trigger.tgrelid=pg_class.oid; > > oid | tgrelid > ---------+--------- > 1260 | 1260 > 6081779 | 6081779 > 6081981 | 6081981 > 6081779 | 6081779 > 6081779 | 6081779 > 6081981 | 6081981 > 6081975 | 6081975 > 6081975 | 6081975 > 6082027 | 6082027 > 6081779 | 6081779 > 6081779 | 6081779 > 6082027 | 6082027 > 6121607 | 6121607 > 6121268 | 6121268 > 6121268 | 6121268 > > so it appears that it is not a dropped table causing the warning, is > there any other way I can try to determine how this warning is being > produced, is there a way of viewing the contents of the trigger? > > Thanks in advance, > > Sandy > > Computer Officer, RA Certification Manager Department of Computer > Science - UWA Llandinam Building Penglais Campus Aberystwyth > Ceredigion Wales - UK > SY23 3DB > Tel: (01970)-622433 > Fax: (01970)-628536 > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate >
On 12/22/06, Sandy Spence <axs@aber.ac.uk> wrote:
> Hi Rajesh,
>
> I ran the select query
> SELECT typowner from pg_type where typname='trigger';
In my installation \dT reports the type trigger
does it do so in yours? If it does not i am afraid
someone else shall be able to help you better.
regds
mallah.
psql>\dT *.trigger
List of data types
+------------+-----------+-------------+
| Schema | Name | Description |
+------------+-----------+-------------+
| pg_catalog | "trigger" | |
+------------+-----------+-------------+
(1 row)
Regds
mallah.
>
> with the following results
>
>
> select typowner from pg_type where typname='trigger';
> typowner
> ----------
> (0 rows)
>
> I then ran the second select query
> SELECT * from pg_user; (I also changed slightly select usename, usesysid
> from pg_user where usename='ltt';)
> usename | usesysid
> ---------+----------
> ltt | 3517
> (1 row)
>
> user/database is where the warning message for the trigger originates from.
>
> Is there something significant in the first select statement.
>
> Regards,
>
> Sandy
>
>
> Computer Officer, RA Certification Manager
> Department of Computer Science - UWA
> Llandinam Building
> Penglais Campus
> Aberystwyth
> Ceredigion
> Wales - UK
> SY23 3DB
> Tel: (01970)-622433
> Fax: (01970)-628536
>
>
> -----Original Message-----
> From: Rajesh Kumar Mallah [mailto:mallah.rajesh@gmail.com ]
> Sent: 21 December 2006 20:04
> To: Sandy Spence
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Trigger trouble
>
> Sandy,
>
> SELECT typowner from pg_type where typname='trigger';
>
> check if typeowner exists in the column usesysid of pg_user by doing
>
> SELECT * from pg_user ;
>
> if typeowner does not exists there is a problem.
>
> regds
>
> On 12/21/06, Sandy Spence <axs@aber.ac.uk> wrote:
> > Hi All,
> >
> > I get the following error when performing a pg_dump
> >
> > dumping database "ltt"...
> > pg_dump: WARNING: owner of data type trigger appears to be invalid
> >
> > the owner and database exists, I have run the following select
> > statement,
> >
> > select pg_class.oid, pg_trigger.tgrelid from pg_trigger left join
> > pg_class on pg_trigger.tgrelid=pg_class.oid;
> >
> > oid | tgrelid
> > ---------+---------
> > 1260 | 1260
> > 6081779 | 6081779
> > 6081981 | 6081981
> > 6081779 | 6081779
> > 6081779 | 6081779
> > 6081981 | 6081981
> > 6081975 | 6081975
> > 6081975 | 6081975
> > 6082027 | 6082027
> > 6081779 | 6081779
> > 6081779 | 6081779
> > 6082027 | 6082027
> > 6121607 | 6121607
> > 6121268 | 6121268
> > 6121268 | 6121268
> >
> > so it appears that it is not a dropped table causing the warning, is
> > there any other way I can try to determine how this warning is being
> > produced, is there a way of viewing the contents of the trigger?
> >
> > Thanks in advance,
> >
> > Sandy
> >
> > Computer Officer, RA Certification Manager Department of Computer
> > Science - UWA Llandinam Building Penglais Campus Aberystwyth
> > Ceredigion Wales - UK
> > SY23 3DB
> > Tel: (01970)-622433
> > Fax: (01970)-628536
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 7: You can help support the PostgreSQL project by donating at
> >
> > http://www.postgresql.org/about/donate
> >
>
>
"Sandy Spence" <axs@aber.ac.uk> writes:
> select typowner from pg_type where typname='trigger';
> typowner
> ----------
> (0 rows)
Um ... what PG version is this exactly? ("select version()" if you're
not sure.)
It's real hard to see why pg_dump would complain about a type that's not
there at all. You sure you checked this in the same database that's
being dumped?
regards, tom lane
On Thu, 2006-12-21 at 10:26 +0000, Sandy Spence wrote: > I get the following error when performing a pg_dump > > dumping database "ltt"... > pg_dump: WARNING: owner of data type trigger appears to be invalid > > the owner and database exists, I have run the following select statement, > > select pg_class.oid, pg_trigger.tgrelid from pg_trigger left join pg_class > on pg_trigger.tgrelid=pg_class.oid; From the error message it appears you have a data type called "trigger", which appears to be invalidated. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Hi Tom,
Thanks for the reply, sorry for the delay in responding a Scotsman and
Hogmanay are never parted ;~}
The version of postgres we are currently running is PostgreSQL 7.2.3
I did a Drop type trigger on the users database adding a tab at the end and
came up with the list below
RI_ConstraintTrigger_6081987 RI_ConstraintTrigger_6081995
RI_ConstraintTrigger_6082037 bestworkbehave
RI_ConstraintTrigger_6081989 RI_ConstraintTrigger_6081997
RI_ConstraintTrigger_6121610 pg_sync_pg_pwd
RI_ConstraintTrigger_6081991 RI_ConstraintTrigger_6082033
RI_ConstraintTrigger_6121612 tidyup
RI_ConstraintTrigger_6081993 RI_ConstraintTrigger_6082035
RI_ConstraintTrigger_6121614
as you can see there appears to be an instance of a trigger that is not
connected to anything, is there a table that has an entry where I can remove
the offending trigger?
Cheers,
Sandy
Computer Officer, RA Certification Manager
Department of Computer Science - UWA
Llandinam Building
Penglais Campus
Aberystwyth
Ceredigion
Wales - UK
SY23 3DB
Tel: (01970)-622433
Fax: (01970)-628536
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: 22 December 2006 14:39
To: Sandy Spence
Cc: 'Rajesh Kumar Mallah'; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Trigger trouble
"Sandy Spence" <axs@aber.ac.uk> writes:
> select typowner from pg_type where typname='trigger'; typowner
> ----------
> (0 rows)
Um ... what PG version is this exactly? ("select version()" if you're not
sure.)
It's real hard to see why pg_dump would complain about a type that's not
there at all. You sure you checked this in the same database that's being
dumped?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
"Sandy Spence" <axs@aber.ac.uk> writes:
> The version of postgres we are currently running is PostgreSQL 7.2.3
You sure it's not something newer? There wasn't any built-in type named
"trigger" in 7.2 --- there is in 7.3 and up.
Assuming it is 7.3, dropping the type would be a really bad idea. Could
we see the results of "select * from pg_type where typname = 'trigger'"?
I'm guessing that either that row is corrupt, or you dropped the
original superuser account (in which case the gripe about type trigger
is just the tip of the iceberg).
regards, tom lane
Hi Tom,
when I run the select query I get the following output,
ltt=> select * from pg_type where typname='trigger';
typname | typowner | typlen | typprtlen | typbyval | typtype | typisdefined
| typdelim | typrelid | typelem | typinput | typoutput
| typreceive | typsend | typalign | typstorage | typdefault
---------+----------+--------+-----------+----------+---------+-------------
-+----------+----------+---------+----------+----------
-+------------+---------+----------+------------+------------
trigger | 0 | 0 | 0 | f | | f
| | 0 | 0 | - | -
| - | - | i | p | trigger
(1 row)
I have a feeling that the database owner (ltt) has created their own type
'trigger' and am guessing that at some other time dropped an item (table
maybe) that uses or used an instance of the type trigger. I have about 100
plus users databases on the same system and it only appears that this user
is causing the pg_dump warning message.
Cheers,
Sandy
Computer Officer, RA Certification Manager
Department of Computer Science - UWA
Llandinam Building
Penglais Campus
Aberystwyth
Ceredigion
Wales - UK
SY23 3DB
Tel: (01970)-622433
Fax: (01970)-628536
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 03 January 2007 15:40
To: Sandy Spence
Cc: 'Rajesh Kumar Mallah'; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Trigger trouble
"Sandy Spence" <axs@aber.ac.uk> writes:
> The version of postgres we are currently running is PostgreSQL 7.2.3
You sure it's not something newer? There wasn't any built-in type named
"trigger" in 7.2 --- there is in 7.3 and up.
Assuming it is 7.3, dropping the type would be a really bad idea. Could we
see the results of "select * from pg_type where typname = 'trigger'"?
I'm guessing that either that row is corrupt, or you dropped the original
superuser account (in which case the gripe about type trigger is just the
tip of the iceberg).
regards, tom lane
"Sandy Spence" <axs@aber.ac.uk> writes:
> when I run the select query I get the following output,
> ltt=> select * from pg_type where typname='trigger';
> typname | typowner | typlen | typprtlen | typbyval | typtype | typisdefined
> | typdelim | typrelid | typelem | typinput | typoutput
> | typreceive | typsend | typalign | typstorage | typdefault
> ---------+----------+--------+-----------+----------+---------+-------------
> -+----------+----------+---------+----------+----------
> -+------------+---------+----------+------------+------------
> trigger | 0 | 0 | 0 | f | | f
> | | 0 | 0 | - | -
> | - | - | i | p | trigger
> (1 row)
OK, given that column set for pg_type I guess it really is 7.2 ... you
need to think about an update real soon, because there are a whole lot
of known bugs in the version you are running. At the very least you
should get on to the last release of the 7.2 series, which I think was
7.2.8. But we stopped maintaining 7.2.x at all some years ago. If you
were to move to 8.1 or 8.2 you'd find it a whole lot faster and more
stable.
Anyway, what you seem to have here is a "shell type". It's not doing
anything useful --- you can probably just do a "DROP TYPE trigger".
Or you could just ignore the warning; it's pretty harmless.
regards, tom lane