Re: invalid types and tables

"Sandy Spence" <axs@xxxxxxxxxx> · Fri, 15 Dec 2006 13:34:38 -0000

Hi Shoaib,

I took you sql query and tweaked it a little, I have 
produced lists that show that at least one entry for the offending types (e.g. 
one instance of say pga_layout does not have an entry in pg_user.usename, the 
field is blank) appears to be wrong.  Am I correct in my observation, can 
I just delete the offending row/s?

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

From: pgsql-admin-owner@xxxxxxxxxxxxxx 
[mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Shoaib 
Mir
Sent: 15 December 2006 12:59
To: Sandy 
Spence
Cc: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: [ADMIN] 
invalid types and tables

For table ownership you can use the following:

select tablename, tableowner from 
pg_tables;

For types:

select pg_type.typname, pg_user.usename from pg_user, 
pg_type where pg_user.usesysid = pg_type.typowner; 

This is how 
you can get it in 8.1 onwards, haven't used the version you have so not sure if 
the above queries will work in it....

-------------------
Shoaib 
Mir
EnterpriseDB ( 
www.enterprisedb.com)

On 12/15/06, Sandy 
Spence <axs@xxxxxxxxxx> 
wrote:

  Hi 
  Shoaib,

  My problem 
  is to determine which user/s this/these is/are, I believe we have had a 
  pg_dump problem long before I took over the support role, users had been 
  removed on a regular basis.  There has been a pg_dump ERROR that I 
  believe will have halted the backup process.  Is there any way I can get 
  a list of users who have the associated tables and types?

  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

  From: pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Shoaib 
  Mir
Sent: 15 December 2006 12:35
To: Sandy 
  Spence
Cc: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: [ADMIN] 
  invalid types and tables

  It appears to me you dropped the user that was owner of the 
  database objects been warned during dump activity. I guess dropping user was 
  possible in you case as you are using an older release but you cant do the 
  same in 8.1 and onwards.

I will recommend recreating the user that was 
  dropped to get around this problem...

---------------------
Shoaib 
  Mir
EnterpriseDB (www.enterprisedb.com 
  )

  On 12/15/06, Sandy 
  Spence <axs@xxxxxxxxxx> 
  wrote: 
  Hi 
    all admins,

Even though I am new to postgres I have been given the 
    job of administrator,
in at the deep end you might say.

We have a 
    cron job that runs nightly to back up all of our users databases,
the 
    output from the cron job shows the following WARNINGs

pg_dump: 
    WARNING: owner of data type pga_queries appears to be invalid
pg_dump: 
    WARNING: owner of data type pg_toast_557151 appears to be 
    invalid
pg_dump: WARNING: owner of data type pga_forms appears to be 
    invalid 
pg_dump: WARNING: owner of data type pg_toast_557156 appears to 
    be invalid
pg_dump: WARNING: owner of data type pga_scripts appears to be 
    invalid
pg_dump: WARNING: owner of data type pg_toast_557161 appears to 
    be invalid 
pg_dump: WARNING: owner of data type pga_reports appears to 
    be invalid
pg_dump: WARNING: owner of data type pg_toast_557166 appears 
    to be invalid
pg_dump: WARNING: owner of data type pga_schema appears to 
    be invalid 
pg_dump: WARNING: owner of data type pg_toast_557171 appears 
    to be invalid
pg_dump: WARNING: owner of data type pga_layout appears to 
    be invalid
pg_dump: WARNING: owner of data type pg_toast_557176 appears 
    to be invalid 
pg_dump: WARNING: owner of data type adult_results appears 
    to be invalid
pg_dump: WARNING: owner of data type pg_toast_557181 
    appears to be invalid
pg_dump: WARNING: owner of data type child_results 
    appears to be invalid 
pg_dump: WARNING: owner of data type 
    pg_toast_557186 appears to be invalid
pg_dump: WARNING: owner of table 
    "pga_queries" appears to be invalid
pg_dump: WARNING: owner of table 
    "pga_forms" appears to be invalid 
pg_dump: WARNING: owner of table 
    "pga_scripts" appears to be invalid
pg_dump: WARNING: owner of table 
    "pga_reports" appears to be invalid
pg_dump: WARNING: owner of table 
    "pga_schema" appears to be invalid 
pg_dump: WARNING: owner of table 
    "pga_layout" appears to be invalid
pg_dump: WARNING: owner of table 
    "adult_results" appears to be invalid
pg_dump: WARNING: owner of table 
    "child_results" appears to be invalid 
pg_dump: WARNING: owner of data 
    type trigger appears to be invalid

My two questions are:

1: 
    How do I find out which owner is being referenced, when I look at 
    a
number of user accounts (databases) they all appear to have pga_forms, 

pga_reposts etc.

2: How do I display a list of the above 
    mentioned types?

We are running version 7.2.3 (upgrading in the new 
    year)

Thanks in advance for any 
    assistance,

Regards,

Sandy Spence 

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 4: Have you searched our list 
    archives?

    http://archives.postgresql.org 