Re: invalid types and tables

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 <axs(at)aber(dot)ac(dot)uk> 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:* pgsql-admin-owner(at)postgresql(dot)org [mailto:
> pgsql-admin-owner(at)postgresql(dot)org] *On Behalf Of *Shoaib Mir
> *Sent:* 15 December 2006 12:59
> *To:* Sandy Spence
> *Cc:* pgsql-admin(at)postgresql(dot)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 <axs(at)aber(dot)ac(dot)uk> 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(at)postgresql(dot)org [mailto:
> > pgsql-admin-owner(at)postgresql(dot)org] *On Behalf Of *Shoaib Mir
> > *Sent:* 15 December 2006 12:35
> > *To:* Sandy Spence
> > *Cc:* pgsql-admin(at)postgresql(dot)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 <axs(at)aber(dot)ac(dot)uk> 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
> > >
> >
> >
>