pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger trouble
10+ messages / 4 participants
[nested] [flat]
* Trigger trouble
@ 2006-12-21 10:26 Sandy Spence <axs@aber.ac.uk>
0 siblings, 2 replies; 10+ messages in thread
From: Sandy Spence @ 2006-12-21 10:26 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger trouble
@ 2006-12-21 20:04 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Sandy Spence <axs@aber.ac.uk>
1 sibling, 1 reply; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-21 20:04 UTC (permalink / raw)
To: Sandy Spence <axs@aber.ac.uk>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger trouble
@ 2006-12-22 09:44 Sandy Spence <axs@aber.ac.uk>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Sandy Spence @ 2006-12-22 09:44 UTC (permalink / raw)
To: 'Rajesh Kumar Mallah' <mallah.rajesh@gmail.com>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger trouble
@ 2006-12-22 13:46 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Sandy Spence <axs@aber.ac.uk>
1 sibling, 0 replies; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-22 13:46 UTC (permalink / raw)
To: Sandy Spence <axs@aber.ac.uk>; +Cc: pgsql-admin
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
> >
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger trouble
@ 2006-12-22 14:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sandy Spence <axs@aber.ac.uk>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2006-12-22 14:39 UTC (permalink / raw)
To: Sandy Spence <axs@aber.ac.uk>; +Cc: 'Rajesh Kumar Mallah' <mallah.rajesh@gmail.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger trouble
@ 2006-12-28 09:51 Simon Riggs <simon@2ndquadrant.com>
parent: Sandy Spence <axs@aber.ac.uk>
1 sibling, 0 replies; 10+ messages in thread
From: Simon Riggs @ 2006-12-28 09:51 UTC (permalink / raw)
To: Sandy Spence <axs@aber.ac.uk>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger trouble
@ 2007-01-03 15:20 Sandy Spence <axs@aber.ac.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Sandy Spence @ 2007-01-03 15:20 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: 'Rajesh Kumar Mallah' <mallah.rajesh@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger trouble
@ 2007-01-03 15:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sandy Spence <axs@aber.ac.uk>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2007-01-03 15:39 UTC (permalink / raw)
To: Sandy Spence <axs@aber.ac.uk>; +Cc: 'Rajesh Kumar Mallah' <mallah.rajesh@gmail.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger trouble
@ 2007-01-03 15:51 Sandy Spence <axs@aber.ac.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Sandy Spence @ 2007-01-03 15:51 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: 'Rajesh Kumar Mallah' <mallah.rajesh@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger trouble
@ 2007-01-03 16:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sandy Spence <axs@aber.ac.uk>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2007-01-03 16:03 UTC (permalink / raw)
To: Sandy Spence <axs@aber.ac.uk>; +Cc: 'Rajesh Kumar Mallah' <mallah.rajesh@gmail.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2007-01-03 16:03 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-21 10:26 Trigger trouble Sandy Spence <axs@aber.ac.uk>
2006-12-21 20:04 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-22 09:44 ` Sandy Spence <axs@aber.ac.uk>
2006-12-22 13:46 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-22 14:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-01-03 15:20 ` Sandy Spence <axs@aber.ac.uk>
2007-01-03 15:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-01-03 15:51 ` Sandy Spence <axs@aber.ac.uk>
2007-01-03 16:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-28 09:51 ` Simon Riggs <simon@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox