Thread: Fixing invalid owners on pg_toast tables in 8.3.5
We're running 8.3.5 on RHEL4 x86_64. We removed a user yesterday and were greeted with warnings from pg_dump this morning. :) pg_dump: WARNING: owner of data type "pg_toast_80075" appears to be invalid The usual archives and google searches produced mainly 8.0 and earlier incidents and suggested resolving this by re-creating a user with that sysid. Since you can no longer specify a SYSID when creating a user (despite what \h in psql says), we gave ALTER type/table a shot with no luck. The owner of the actual table and index is correct, only the type has an invalid owner. I have thus far avoided the temptation to try a manual update... Is there a recommended procedure for resolving this safely? Since Postgres now prevents you from dropping users owning objects, is this a bug, or does it fall into a gray area? Thanks!
On Fri, 2009-02-20 at 12:01 -0700, Cott Lang wrote: > We're running 8.3.5 on RHEL4 x86_64. > > We removed a user yesterday and were greeted with warnings from pg_dump > this morning. :) > > pg_dump: WARNING: owner of data type "pg_toast_80075" appears to be > invalid > > The usual archives and google searches produced mainly 8.0 and earlier > incidents and suggested resolving this by re-creating a user with that > sysid. Since you can no longer specify a SYSID when creating a user > (despite what \h in psql says), we gave ALTER type/table a shot with no > luck. > Something isn't right: postgres=# create user foobar superuser; CREATE ROLE postgres=# set role foobar; SET postgres=# create type typetext AS (bar text); CREATE TYPE postgres=# select current_user; current_user -------------- foobar (1 row) postgres=# set role postgres; SET postgres=# drop type typetest; ERROR: type "typetest" does not exist postgres=# drop user foobar; ERROR: role "foobar" cannot be dropped because some objects depend on it DETAIL: owner of type typetext > The owner of the actual table and index is correct, only the type has an > invalid owner. I have thus far avoided the temptation to try a manual > update... > > Is there a recommended procedure for resolving this safely? > You can use alter type to change the owner of the type to a valid user but see above. Something is wrong. Joshua D. Drake -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
Cott Lang <cott@internetstaff.com> writes:
> The owner of the actual table and index is correct, only the type has an
> invalid owner. I have thus far avoided the temptation to try a manual
> update...
That's probably your best bet.
> Since Postgres now prevents you from dropping users owning objects, is
> this a bug, or does it fall into a gray area?
Can you show us how to reproduce it? We've seen occasional reports of
similar things but no one ever managed to produce a test case.
regards, tom lane
On Fri, 2009-02-20 at 11:25 -0800, Joshua D. Drake wrote: > > > > You can use alter type to change the owner of the type to a valid user > but see above. Something is wrong. That's what I thought too, but we tried that first with these results: # alter type pg_toast.pg_toast_80075 OWNER TO postgres; ERROR: pg_toast.pg_toast_80075 is a table's row type HINT: Use ALTER TABLE instead. # alter table pg_toast.pg_toast_80075 OWNER TO postgres; ERROR: "pg_toast_80075" is not a table, view, or sequence ... that's when I decided this was post-worthy. :)
Tom, Thanks for the inspiration - I've fixed them manually. I spent a few minutes trying to recreate the obvious test case, and it all works as designed. I reviewed our logs from the user removal yesterday, and the tables linked to these toast tables did not have ownership changed yesterday, so I did more digging. I found a handful of other pg_types with an unusual owner and in every case, the toast type is owned by the user that created the database via full pg_restore some months ago. pg_restore should have created the table as the user running it, and immediately done an ALTER TABLE .. OWNER TO. So does the pg_toast type take ownership from the owner of the originating table, or the user id inserting data that forces a toast table creation ? Either way, it's inconsistent - it's only a very small percentage of tables that would have had toast tables created at restore time that are affected. Seems like two possible issues - 1. Postgres isn't checking type owners before allowing a user drop. 2. Toast type ownership doesn't always change on table ownership change. I can recreate #1 by manually updating pg_type and dropping the user, but I don't know if that's a valid test. If #2 should never happen, I can see why #1 wouldn't be much of an issue. I can't recreate #2. On Fri, 2009-02-20 at 14:32 -0500, Tom Lane wrote: > Cott Lang <cott@internetstaff.com> writes: > > The owner of the actual table and index is correct, only the type has an > > invalid owner. I have thus far avoided the temptation to try a manual > > update... > > That's probably your best bet. > > > Since Postgres now prevents you from dropping users owning objects, is > > this a bug, or does it fall into a gray area? > > Can you show us how to reproduce it? We've seen occasional reports of > similar things but no one ever managed to produce a test case. > > regards, tom lane
Cott Lang <cott@internetstaff.com> writes:
> I found a handful of other pg_types with an unusual owner and in every
> case, the toast type is owned by the user that created the database via
> full pg_restore some months ago.
> pg_restore should have created the table as the user running it, and
> immediately done an ALTER TABLE .. OWNER TO. So does the pg_toast type
> take ownership from the owner of the originating table, or the user id
> inserting data that forces a toast table creation ?
> Either way, it's inconsistent - it's only a very small percentage of
> tables that would have had toast tables created at restore time that are
> affected.
The toast table would be created immediately. ALTER TABLE OWNER is
supposed to update ownership of any attached toast table, too, and that
should propagate to the pg_type row as well. There isn't supposed to be
any way for a toast table to have different ownership from its parent,
let alone a composite-type pg_type row have different ownership from the
associated pg_class row.
We have seen a small number of reports that sometimes toast tables fail
to track parent-table updates, fail to get dropped when the parent is,
etc. Nobody knows how to reproduce that though :-(. I was hoping you
might find some tidbit that would provide the missing link, but no luck
yet.
Can you see any pattern or common characteristic to the tables whose
toast pg_type rows failed to change owner? I'm not sure what to look
for exactly, but similarities in the column contents might be a
possibility. Also, can you tell if the problem tables were adjacent
in the dump that was restored?
regards, tom lane
On Fri, 2009-02-20 at 16:35 -0500, Tom Lane wrote:
> Can you see any pattern or common characteristic to the tables whose
> toast pg_type rows failed to change owner? I'm not sure what to look
> for exactly, but similarities in the column contents might be a
> possibility. Also, can you tell if the problem tables were adjacent
> in the dump that was restored?
I fiddled around a while, found the problem, and I can repeat it at
will. It's actually related to type changes we've made over the months
since the restore.
Scenario:
1. Create a table as user A.
create table toaster (
bread varchar(8000)
);
2. Check typowner of toast type
# select typowner from pg_type join pg_class on (typname=relname) where
pg_class.oid in (select reltoastrelid from pg_class where
relname='toaster');
typowner
----------
16388
3. Alter the field type
alter table toaster ALTER bread type varchar(9000);
4. Check the typowner of toast type
# select typowner from pg_type join pg_class on (typname=relname) where
pg_class.oid in (select reltoastrelid from pg_class where
relname='toaster');
typowner
----------
3555301
The table and toast table owners are not affected:
# select relowner from pg_class where relname='toaster';
relowner
----------
16388
# select relowner from pg_class where oid in (select reltoastrelid from
pg_class where relname='toaster');
relowner
----------
16388
Hope this helps.
Cott
Cott Lang <cott@internetstaff.com> writes:
> I fiddled around a while, found the problem, and I can repeat it at
> will. It's actually related to type changes we've made over the months
> since the restore.
[ scratches head... ] That example fails to misbehave for me in 8.3.6,
and I'm pretty certain there have been no relevant bugfixes since 8.3.5.
Can anyone else duplicate a change of toast table owner?
Are you by any chance using a nondefault block_size? It strikes me that
at block_size 32K, the example would involve enlarging the varchar's
width across the boundary where a toast table would be created. However
that still doesn't explain the results, since in that case the first
"select typowner" shouldn't have found any row.
Are both instances of the select finding the same pg_type row? You've
phrased the select to hide all the evidence, but what we'd need to
figure out is whether reltoastrelid changed or the pg_type row itself
got updated.
regards, tom lane
I wrote:
> [ scratches head... ] That example fails to misbehave for me in 8.3.6,
Oh, wait, you omitted a step from the example: the ALTER has to be done
as somebody other than the table owner (eg a superuser, else you'd not
have the needed permissions).
regression=# create user a;
CREATE ROLE
regression=# \c - a
psql (8.4devel)
You are now connected to database "regression" as user "a".
regression=> create table toaster (
bread varchar(7000));
CREATE TABLE
regression=> select typowner from pg_type join pg_class on (typname=relname) where
pg_class.oid in (select reltoastrelid from pg_class where
relname='toaster');
typowner
----------
63977
(1 row)
regression=> \c - postgres
psql (8.4devel)
You are now connected to database "regression" as user "postgres".
regression=# alter table toaster ALTER bread type varchar(9000);
ALTER TABLE
regression=# select typowner from pg_type join pg_class on (typname=relname) where
pg_class.oid in (select reltoastrelid from pg_class where
relname='toaster');
typowner
----------
10
(1 row)
Yeah, that's a bug. It probably hasn't got anything to do with the
other toast-table weirdnesses we've heard about, but it's a bug.
regards, tom lane