pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Trigger 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