Re: Changing owner of pg_toast tables - Mailing list pgsql-general

Mark Styles-2 wrote:
>
> On Thu, Jan 29, 2009 at 02:11:37PM -0500, Tom Lane wrote:
>> Mark Styles <postgres@lambic.co.uk> writes:
>> > Thanks, I managed to clear out the offending dependencies. relowner was
>> > actually set correctly, but the pg_shdepend records were wrong.
>>
>> Hmm ... what actually was in the pg_shdepend entries?
>
> I guess I should've noted that down eh? From memory, the classid was
> the oid of the pg_toast object, the refobjid was the oid of the role,
> the deptype was 'o', I don't recall what the other values were.
>
> I'll keep my eye out for more problems as I work through tidying this
> database.
>


I found this thread online because I have the same problem. So, I thought
I'd share what I've discovered. I could not drop a role. pg_dumpall doesn't
show any dependencies to this toast table. Here is output (with some
information <snipped> to protect the privacy of the company I am working
for):

<snipped>=# drop role <snipped>;
ERROR:  role "<snipped>" cannot be dropped because some objects depend on it
DETAIL:  owner of type pg_toast.pg_toast_51797
1 objects in database <snipped>

I found the role in question (with oid = 1237) from pg_roles. Then, I was
able to find a list of dependencies:

postgres=# select * from pg_shdepend where refobjid=17158;
 dbid  | classid | objid | refclassid | refobjid | deptype
-------+---------+-------+------------+----------+---------
 16388 |    1247 | 51802 |       1260 |    17158 | o
 52721 |    1247 | 51802 |       1260 |    17158 | o


pfacts003=# select * from pg_class where oid = 1247;
 relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers |
relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass |    relacl

---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+---------------
 pg_type |           11 |      71 |       10 |     0 |        1247 |
0 |        8 |       329 |             0 |             0 | t           | f
| r       |       23 |         0 |           0 |        0 |        0 |
0 | t          | f          | f           | f              | {=r/postgres}
(1 row)


pfacts003=# select * from pg_class where oid = 1260;
  relname  | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers |
relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass |           relacl

-----------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+-----------------------------
 pg_authid |           11 |   10281 |       10 |     0 |        1260 |
1664 |        1 |        12 |         10290 |             0 | t           |
t           | r       |       11 |         0 |           1 |        0 |
0 |       0 | t          | f          | f           | f              |
{postgres=arwdRxt/postgres}
(1 row)


This may help explain what happened. I can't give any history of the
situation since I inherited this database. But, I think the above should be
somewhat helpful. Is it possible that the person who first "needed" a toast
table gets the type build by default and therefore owns it?

--
View this message in context: http://www.nabble.com/Changing-owner-of-pg_toast-tables-tp21728869p25048954.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.