Well I will not recommend deleting rows like this from the meta data, but if I will rather try using ALTER TABLE and ALTER TYPE to assign an owner for the missing user referenced type or table
For example --> ALTER TYPE name OWNER TO new_owner ---------------- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 12/15/06, Sandy Spence <[EMAIL PROTECTED]> wrote:
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:* [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] *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 <[EMAIL PROTECTED]> 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:* [EMAIL PROTECTED] [mailto: > [EMAIL PROTECTED] *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 <[EMAIL PROTECTED]> 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 > > > >
