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_class
where oid in (
select attrelid
from pg_attribute
where (attname like '%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