Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 15DA69FA1FD for ; Fri, 15 Dec 2006 09:35:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 41939-03 for ; Fri, 15 Dec 2006 09:35:24 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from cefni.aber.ac.uk (cefni.aber.ac.uk [144.124.16.40]) by postgresql.org (Postfix) with ESMTP id F07CB9FA173 for ; Fri, 15 Dec 2006 09:35:28 -0400 (AST) Received: from localhost ([127.0.0.1] helo=cefni.aber.ac.uk) by cefni.aber.ac.uk with esmtp (Exim 4.54) id 1GvDDi-0004hy-7d; Fri, 15 Dec 2006 13:35:26 +0000 Received: from pc2axs.dcs.aber.ac.uk ([193.60.11.82] helo=pc2axs) by cefni.aber.ac.uk with esmtp (Exim 4.54) id 1GvDCw-0004dF-7x; Fri, 15 Dec 2006 13:34:38 +0000 From: "Sandy Spence" To: "'Shoaib Mir'" Cc: Subject: Re: invalid types and tables Date: Fri, 15 Dec 2006 13:34:38 -0000 Message-ID: <002a01c7204d$c4afba90$520b3cc1@pc2axs> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002B_01C7204D.C4AFBA90" X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 Thread-Index: AccgSSDmlmx+EQJpQlSo1GRJ+rw1BgAA/uuQ In-Reply-To: X-Sophos-Scanned: from axs@aber.ac.uk virus scanned OK X-UWA-Mid: 1GvDCw-0004dF-7x X-UWA-Originating-IP: 193.60.11.82 X-UWA-Bounce-Filter: 8B4gAOMXYx X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.47 tagged_above=0 required=5 tests=AWL, BAYES_40, HTML_40_50, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200612/221 X-Sequence-Number: 24013 This is a multi-part message in MIME format. ------=_NextPart_000_002B_01C7204D.C4AFBA90 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Shoaib Mir Sent: 15 December 2006 12:59 To: Sandy Spence Cc: pgsql-admin@postgresql.org 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 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@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Shoaib Mir Sent: 15 December 2006 12:35 To: Sandy Spence Cc: pgsql-admin@postgresql.org 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 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 ------=_NextPart_000_002B_01C7204D.C4AFBA90 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Hi Shoaib,
 
I took you sql query and tweaked it a little, I = have=20 produced lists that show that at least one entry for the offending types = (e.g.=20 one instance of say pga_layout does not have an entry in = pg_user.usename, the=20 field is blank) appears to be wrong.  Am I correct in my = observation, can=20 I just delete the offending row/s?
 
Regards,
 
Sandy
 

Computer Officer, RA Certification = Manager
Department of=20 Computer Science - UWA
Llandinam Building
Penglais=20 Campus
Aberystwyth
Ceredigion
Wales - UK
SY23 3DB
Tel:=20 (01970)-622433
Fax: (01970)-628536

 


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

For table ownership you can use the following:

select tablename, tableowner from=20 pg_tables;

For types:

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

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

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

On 12/15/06, Sandy=20 Spence <axs@aber.ac.uk>=20 wrote:
Hi=20 Shoaib,
 
My problem=20 is to determine which user/s this/these is/are, I believe we have had = a=20 pg_dump problem long before I took over the support role, users had = been=20 removed on a regular basis.  There has been a pg_dump ERROR that = I=20 believe will have halted the backup process.  Is there any way I = can get=20 a list of users who have the associated tables and = types?
 
Regards,
 
Sandy
 

Computer Officer, RA Certification = Manager
Department of=20 Computer Science - UWA
Llandinam Building
Penglais=20 Campus
Aberystwyth
Ceredigion
Wales - UK
SY23 3DB
Tel:=20 (01970)-622433
Fax: (01970)-628536

 


From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of = Shoaib=20 Mir
Sent: 15 December 2006 12:35
To: Sandy=20 Spence
Cc: pgsql-admin@postgresql.org
Subject: Re: = [ADMIN]=20 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=20 possible in you case as you are using an older release but you cant do = the=20 same in 8.1 and onwards.

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

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

On 12/15/06, Sandy=20 Spence <axs@aber.ac.uk>=20 wrote:=20
Hi=20 all admins,

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

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

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

My two questions = are:

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

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

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

Thanks in advance for any=20 assistance,

Regards,

Sandy Spence

Computer = Officer, RA=20 Certification Manager
Department of Computer Science - = UWA
Llandinam=20 Building
Penglais Campus
Aberystwyth
Ceredigion
Wales -=20 UK
SY23 3DB
Tel: (01970)-622433
Fax: (01970)-628536=20



---------------------------(end of=20 broadcast)---------------------------
TIP 4: Have you searched = our list=20 = archives?

         &n= bsp;    =20 http://archives.postgresql.org=20 =


= ------=_NextPart_000_002B_01C7204D.C4AFBA90--