Quick Links

Re: invalid types and tables

Lists:	pgsql-admin

From:	"Sandy Spence" <axs(at)aber(dot)ac(dot)uk>
To:	<pgsql-admin(at)postgresql(dot)org>
Subject:	invalid types and tables
Date:	2006-12-15 11:55:26
Message-ID:	000c01c7203f$e92ac030$520b3cc1@pc2axs
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-admin

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

From:	"Shoaib Mir" <shoaibmir(at)gmail(dot)com>
To:	"Sandy Spence" <axs(at)aber(dot)ac(dot)uk>
Cc:	pgsql-admin(at)postgresql(dot)org
Subject:	Re: invalid types and tables
Date:	2006-12-15 12:34:35
Message-ID:	bf54be870612150434x21f3650dnf3186001d6d00d08@mail.gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-admin

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
>

From:	"Sandy Spence" <axs(at)aber(dot)ac(dot)uk>
To:	"'Shoaib Mir'" <shoaibmir(at)gmail(dot)com>
Cc:	<pgsql-admin(at)postgresql(dot)org>
Subject:	Re: invalid types and tables
Date:	2006-12-15 12:49:14
Message-ID:	001f01c72047$6cf411d0$520b3cc1@pc2axs
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-admin

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

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

---------------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com <http://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

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
<http://archives.postgresql.org>

From:	"Shoaib Mir" <shoaibmir(at)gmail(dot)com>
To:	"Sandy Spence" <axs(at)aber(dot)ac(dot)uk>
Cc:	pgsql-admin(at)postgresql(dot)org
Subject:	Re: invalid types and tables
Date:	2006-12-15 12:58:48
Message-ID:	bf54be870612150458k74c03e9fqa72a4e0eff9edf00@mail.gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-admin

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
> >
>
>

From:	"Sandy Spence" <axs(at)aber(dot)ac(dot)uk>
To:	"'Shoaib Mir'" <shoaibmir(at)gmail(dot)com>
Cc:	<pgsql-admin(at)postgresql(dot)org>
Subject:	Re: invalid types and tables
Date:	2006-12-15 13:34:38
Message-ID:	002a01c7204d$c4afba90$520b3cc1@pc2axs
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-admin

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 ( <http://www.enterprisedb.com> www.enterprisedb.com)

On 12/15/06, Sandy Spence <axs(at)aber(dot)ac(dot)uk> wrote:

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

_____

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

---------------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com <http://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

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
<http://archives.postgresql.org>

From:	"Shoaib Mir" <shoaibmir(at)gmail(dot)com>
To:	"Sandy Spence" <axs(at)aber(dot)ac(dot)uk>
Cc:	pgsql-admin(at)postgresql(dot)org
Subject:	Re: invalid types and tables
Date:	2006-12-15 13:47:59
Message-ID:	bf54be870612150547x7c383c75v6a0e2273dac67803@mail.gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-admin

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
> > >
> >
> >
>

From:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To:	"Shoaib Mir" <shoaibmir(at)gmail(dot)com>
Cc:	"Sandy Spence" <axs(at)aber(dot)ac(dot)uk>, pgsql-admin(at)postgresql(dot)org
Subject:	Re: invalid types and tables
Date:	2006-12-15 15:49:48
Message-ID:	9165.1166197788@sss.pgh.pa.us
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-admin

"Shoaib Mir" <shoaibmir(at)gmail(dot)com> writes:
> 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

No, just create a new user with the same usesysid as is shown as the
owner in pg_class etc. See the SYSID option to CREATE USER.

(FWIW, more recent PG versions prevent you from dropping users that still
own objects.)

regards, tom lane

From:	"Shoaib Mir" <shoaibmir(at)gmail(dot)com>
To:	"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc:	"Sandy Spence" <axs(at)aber(dot)ac(dot)uk>, pgsql-admin(at)postgresql(dot)org
Subject:	Re: invalid types and tables
Date:	2006-12-15 16:21:57
Message-ID:	bf54be870612150821i28129158v6d2780f7fe836910@mail.gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-admin

Agreed, that will be a good solution for this scenario...

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

On 12/15/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Shoaib Mir" <shoaibmir(at)gmail(dot)com> writes:
> > 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
>
> No, just create a new user with the same usesysid as is shown as the
> owner in pg_class etc. See the SYSID option to CREATE USER.
>
> (FWIW, more recent PG versions prevent you from dropping users that still
> own objects.)
>
> regards, tom lane
>

From:	"Sandy Spence" <axs(at)aber(dot)ac(dot)uk>
To:	"'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "'Shoaib Mir'" <shoaibmir(at)gmail(dot)com>
Cc:	<pgsql-admin(at)postgresql(dot)org>
Subject:	Re: invalid types and tables
Date:	2006-12-15 16:30:54
Message-ID:	004701c72066$64238170$520b3cc1@pc2axs
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-admin

Hi Tom and Shoaib,

thanks for the help, one last question I hope, how do I cross reference the
pg_class, pg_user and pg_type so that I can obtain the usesysid of the
missing owner?

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: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: 15 December 2006 15:50
To: Shoaib Mir
Cc: Sandy Spence; pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] invalid types and tables

"Shoaib Mir" <shoaibmir(at)gmail(dot)com> writes:
> 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

No, just create a new user with the same usesysid as is shown as the owner
in pg_class etc. See the SYSID option to CREATE USER.

(FWIW, more recent PG versions prevent you from dropping users that still
own objects.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

From:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To:	"Sandy Spence" <axs(at)aber(dot)ac(dot)uk>
Cc:	"'Shoaib Mir'" <shoaibmir(at)gmail(dot)com>, pgsql-admin(at)postgresql(dot)org
Subject:	Re: invalid types and tables
Date:	2006-12-15 16:51:29
Message-ID:	11297.1166201489@sss.pgh.pa.us
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-admin

"Sandy Spence" <axs(at)aber(dot)ac(dot)uk> writes:
> thanks for the help, one last question I hope, how do I cross reference the
> pg_class, pg_user and pg_type so that I can obtain the usesysid of the
> missing owner?

It's whatever is in the relowner and typowner fields. There is no entry
anymore in pg_user, if there were you wouldn't be having a problem;
so you'll likely have to make up names for the user ids.

regards, tom lane

From:	"Sandy Spence" <axs(at)aber(dot)ac(dot)uk>
To:	"'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "'Shoaib Mir'" <shoaibmir(at)gmail(dot)com>
Cc:	<pgsql-admin(at)postgresql(dot)org>
Subject:	Re: invalid types and tables
Date:	2006-12-18 09:16:24
Message-ID:	001b01c72285$340f4840$520b3cc1@pc2axs
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-admin

Hi Tom and Shoaib,

Thanks for all your help, the penny dropped and I now understand what you
meant by SYSID. I have traced the usename and all is now well.

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: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: 15 December 2006 15:50
To: Shoaib Mir
Cc: Sandy Spence; pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] invalid types and tables

No, just create a new user with the same usesysid as is shown as the owner
in pg_class etc. See the SYSID option to CREATE USER.

(FWIW, more recent PG versions prevent you from dropping users that still
own objects.)

regards, tom lane

From:	"Shoaib Mir" <shoaibmir(at)gmail(dot)com>
To:	"Sandy Spence" <axs(at)aber(dot)ac(dot)uk>
Cc:	"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-admin(at)postgresql(dot)org
Subject:	Re: invalid types and tables
Date:	2006-12-18 09:35:19
Message-ID:	bf54be870612180135r4fe06b7bqf79991ccd326a604@mail.gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-admin

Glad to hear that Sandy :)

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

On 12/18/06, Sandy Spence <axs(at)aber(dot)ac(dot)uk> wrote:
>
> Hi Tom and Shoaib,
>
> Thanks for all your help, the penny dropped and I now understand what you
> meant by SYSID. I have traced the usename and all is now well.
>
> 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: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: 15 December 2006 15:50
> To: Shoaib Mir
> Cc: Sandy Spence; pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] invalid types and tables
>
> "Shoaib Mir" <shoaibmir(at)gmail(dot)com> writes:
> > 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
>
> No, just create a new user with the same usesysid as is shown as the owner
> in pg_class etc. See the SYSID option to CREATE USER.
>
> (FWIW, more recent PG versions prevent you from dropping users that still
> own objects.)
>
> regards, tom lane
>
>