Thread: toast tables on system catalogs
Hi, Someone on IRC recently noticed that you can't grant USAGE privileges on a table to a large number of roles. (My experiment says 2466 works, 2467 doesn't). Of course, this is wrong and all that. I wrote a blog article about this: http://www.commandprompt.com/blogs/alvaro_herrera/2011/03/grant_schema_usage_to_2500_users_no_can_do/ The reason for this is that pg_namespace doesn't have a toast table; and neither do other 10 catalogs that have "acl" columns: pg_attribute pg_default_acl pg_largeobject_metadata pg_pltemplate pg_tablespace pg_class pg_foreign_data_wrapper pg_namespace pg_foreign_server pg_user_mapping pg_language select relname, reltoastrelid from pg_classwhere oid in ( select attrelid from pg_attribute where (attnamelike '%acl' and atttypid = 'aclitem[]'::regtype) or (attname like '%options' and atttypid = 'text[]'::regtype)) and relkind = 'r'; Strangely, we made pg_database have a toast table, and the only reason for this is datacl. Should we create toast tables for the remaining catalogs? -- Álvaro Herrera <alvherre@alvh.no-ip.org>
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Strangely, we made pg_database have a toast table, and the only reason
> for this is datacl. Should we create toast tables for the remaining
> catalogs?
As I commented on your blog, this is nonsense. pg_database has a TOAST
table becase we thought it might need one for datconfig[]. Now that
that's gone, it'd be consistent to remove the toast table, but it didn't
occur to us to do that.
aclitem entries wide enough to need toasting are going to suck for all
sorts of reasons (IIRC there are some O(N^2) algorithms in there, not
to mention the cost of pulling in entries from a toast table on every
access) so I am not excited about encouraging people to use them.
regards, tom lane
Excerpts from Tom Lane's message of mar mar 01 19:03:35 -0300 2011: > Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > > Strangely, we made pg_database have a toast table, and the only reason > > for this is datacl. Should we create toast tables for the remaining > > catalogs? > > As I commented on your blog, this is nonsense. pg_database has a TOAST > table becase we thought it might need one for datconfig[]. Now that > that's gone, it'd be consistent to remove the toast table, but it didn't > occur to us to do that. Yeah, it occured to me to troll the git logs just after sending the email and I promptly noticed the bug in my conclusion -- there was no datacl back then; and pg_db_role_settings is very new. > aclitem entries wide enough to need toasting are going to suck for all > sorts of reasons (IIRC there are some O(N^2) algorithms in there, not > to mention the cost of pulling in entries from a toast table on every > access) so I am not excited about encouraging people to use them. I agree on not supporting large numbers of privileges, though the error message leaves a bit to be desired. Should we remove the toast table declaration for pg_database? (BTW with the relmapper mechanism, do we still need to declare the toast table OIDs?) -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Excerpts from Tom Lane's message of mar mar 01 19:03:35 -0300 2011: > > Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > > > Strangely, we made pg_database have a toast table, and the only reason > > > for this is datacl. Should we create toast tables for the remaining > > > catalogs? > > > > As I commented on your blog, this is nonsense. pg_database has a TOAST > > table becase we thought it might need one for datconfig[]. Now that > > that's gone, it'd be consistent to remove the toast table, but it didn't > > occur to us to do that. > > Yeah, it occured to me to troll the git logs just after sending the > email and I promptly noticed the bug in my conclusion -- there was no > datacl back then; and pg_db_role_settings is very new. > > > aclitem entries wide enough to need toasting are going to suck for all > > sorts of reasons (IIRC there are some O(N^2) algorithms in there, not > > to mention the cost of pulling in entries from a toast table on every > > access) so I am not excited about encouraging people to use them. > > I agree on not supporting large numbers of privileges, though the error > message leaves a bit to be desired. > > Should we remove the toast table declaration for pg_database? > > (BTW with the relmapper mechanism, do we still need to declare the toast > table OIDs?) Did we decide on this? Is it a TODO? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Mon, Sep 5, 2011 at 1:01 PM, Bruce Momjian <bruce@momjian.us> wrote: > Alvaro Herrera wrote: >> Excerpts from Tom Lane's message of mar mar 01 19:03:35 -0300 2011: >> > Alvaro Herrera <alvherre@alvh.no-ip.org> writes: >> > > Strangely, we made pg_database have a toast table, and the only reason >> > > for this is datacl. Should we create toast tables for the remaining >> > > catalogs? >> > >> > As I commented on your blog, this is nonsense. pg_database has a TOAST >> > table becase we thought it might need one for datconfig[]. Now that >> > that's gone, it'd be consistent to remove the toast table, but it didn't >> > occur to us to do that. >> >> Yeah, it occured to me to troll the git logs just after sending the >> email and I promptly noticed the bug in my conclusion -- there was no >> datacl back then; and pg_db_role_settings is very new. >> >> > aclitem entries wide enough to need toasting are going to suck for all >> > sorts of reasons (IIRC there are some O(N^2) algorithms in there, not >> > to mention the cost of pulling in entries from a toast table on every >> > access) so I am not excited about encouraging people to use them. >> >> I agree on not supporting large numbers of privileges, though the error >> message leaves a bit to be desired. >> >> Should we remove the toast table declaration for pg_database? >> >> (BTW with the relmapper mechanism, do we still need to declare the toast >> table OIDs?) > > Did we decide on this? Is it a TODO? Uh, maybe. It's not really clear that there's enough benefit here to justify someone spending time on it. If no one is feeling motivated maybe we should just let it go... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company