Thread: Changing owner of pg_toast tables
Hi, I'm trying to drop a role that is no longer being used. However the role has 4 dependencies which are all pg_toast tables. How can I change the owner of those pg_toast tables so I can drop the role? -- Mark http://www.lambic.co.uk
Attachment
Mark Styles <postgres@lambic.co.uk> writes:
> I'm trying to drop a role that is no longer being used. However the role
> has 4 dependencies which are all pg_toast tables. How can I change the
> owner of those pg_toast tables so I can drop the role?
I guess the interesting question to me is what happened to the tables
those toast tables are/were attached to? They should have the same
owners as their parent tables. What PG version is this exactly?
regards, tom lane
On Thu, Jan 29, 2009 at 10:46:08AM -0500, Tom Lane wrote: > Mark Styles <postgres@lambic.co.uk> writes: > > I'm trying to drop a role that is no longer being used. However the role > > has 4 dependencies which are all pg_toast tables. How can I change the > > owner of those pg_toast tables so I can drop the role? > > I guess the interesting question to me is what happened to the tables > those toast tables are/were attached to? They should have the same > owners as their parent tables. They did have the same owner, I changed the owner to postgres so I could drop the role, but the corresponding pg_toast tables did not change. > What PG version is this exactly? 8.1.11 -- Mark http://www.lambic.co.uk
Attachment
Mark Styles <postgres@lambic.co.uk> writes:
> On Thu, Jan 29, 2009 at 10:46:08AM -0500, Tom Lane wrote:
>> I guess the interesting question to me is what happened to the tables
>> those toast tables are/were attached to? They should have the same
>> owners as their parent tables.
> They did have the same owner, I changed the owner to postgres so I could
> drop the role, but the corresponding pg_toast tables did not change.
Well, that's just weird. Can you reproduce such a behavior? In my
tests 8.1 definitely does change the toast table's owner along with the
parent. One can imagine that step failing, but if so the whole
ALTER OWNER transaction should roll back.
As for getting out of your immediate problem, I think what you'd need to
do is manually adjust the pg_class.relowner fields for those toast
tables, and then get rid of the pg_shdepend entries that claim they
depend on the old role. (You don't need to put back new entries
claiming they depend on postgres.) But I'd sure like to find out what
happened. We've heard a few reports before of toast tables not getting
dropped when their parents were, and I wonder if this is related.
regards, tom lane
On Thu, Jan 29, 2009 at 12:29:07PM -0500, Tom Lane wrote: > Mark Styles <postgres@lambic.co.uk> writes: > > On Thu, Jan 29, 2009 at 10:46:08AM -0500, Tom Lane wrote: > >> I guess the interesting question to me is what happened to the tables > >> those toast tables are/were attached to? They should have the same > >> owners as their parent tables. > > > They did have the same owner, I changed the owner to postgres so I could > > drop the role, but the corresponding pg_toast tables did not change. > > Well, that's just weird. Can you reproduce such a behavior? In my > tests 8.1 definitely does change the toast table's owner along with the > parent. One can imagine that step failing, but if so the whole > ALTER OWNER transaction should roll back. Actually, pgadmin3 may have given me an error on that operation (which I ignored, it did what I wanted, I thought), I believe it was something like 'OID not found'. I have to do something similar for another role so I'll pay more attention then. > As for getting out of your immediate problem, I think what you'd need to > do is manually adjust the pg_class.relowner fields for those toast > tables, and then get rid of the pg_shdepend entries that claim they > depend on the old role. (You don't need to put back new entries > claiming they depend on postgres.) But I'd sure like to find out what > happened. We've heard a few reports before of toast tables not getting > dropped when their parents were, and I wonder if this is related. Thanks, I managed to clear out the offending dependencies. relowner was actually set correctly, but the pg_shdepend records were wrong. -- Mark http://www.lambic.co.uk
Attachment
Mark Styles wrote: > On Thu, Jan 29, 2009 at 12:29:07PM -0500, Tom Lane wrote: > > Mark Styles <postgres@lambic.co.uk> writes: > > > On Thu, Jan 29, 2009 at 10:46:08AM -0500, Tom Lane wrote: > > >> I guess the interesting question to me is what happened to the tables > > >> those toast tables are/were attached to? They should have the same > > >> owners as their parent tables. > > > > > They did have the same owner, I changed the owner to postgres so I could > > > drop the role, but the corresponding pg_toast tables did not change. > > > > Well, that's just weird. Can you reproduce such a behavior? In my > > tests 8.1 definitely does change the toast table's owner along with the > > parent. One can imagine that step failing, but if so the whole > > ALTER OWNER transaction should roll back. > > Actually, pgadmin3 may have given me an error on that operation (which I > ignored, it did what I wanted, I thought), I believe it was something > like 'OID not found'. I agree with Tom -- this is pretty weird. If it failed, it should have failed all the way and rollback the other changes. On other news, I noticed the other day while playing with reloptions that if you run an ALTER TABLE command that rewrites the table, the toast table seems to be misnamed, i.e. the same bug we fixed on CLUSTER not long ago. I very much doubt this explains your problem, but still ... -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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?
Given the way the code works, this could be explained by a corrupt index
for pg_shdepend (which could cause it to fail to find the entries it
should've deleted). But I don't see how that would lead to an "OID not
found" type of message. Also, if you did get an error, that should've
rolled back the whole thing. So I'm still baffled.
regards, tom lane
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. -- Mark http://www.lambic.co.uk
Attachment
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.
Glen Jarvis <glen@glenjarvis.com> writes: > I found this thread online because I have the same problem. > <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 think this is a known issue: http://archives.postgresql.org/pgsql-general/2009-02/msg01021.php http://archives.postgresql.org/pgsql-committers/2009-02/msg00224.php If you're not running a very recent minor release then you'd be subject to the bug, and even if you were this could be leftover damage from a previous episode. If your situation doesn't fit the summary in the second message cited above, we'd be interested to have more details. regards, tom lane