Re: Changing owner of pg_toast tables

Mark Styles-2 wrote:
>
> On Thu, Jan 29, 2009 at 02:11:37PM -0500, Tom Lane wrote:
>> Mark Styles <postgres(at)lambic(dot)co(dot)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.