Thread: [HACKERS] Add TOAST to system tables with ACL?
Hi!
This topic was already discussed (at least one time) in 2011. See [1] for details. I'd like to raise that again.
Currently, we have table in system catalog with ACL, but without TOAST. Thus, it might happen that we can't fit new ACL item, because row becomes too large for in-line storage.
You can easily reproduce this situation in psql.
create table t (col int);
\copy (select 'create user u' || i || ';' from generate_series(1,10000) i) to 'script.sql'
\i script.sql
\copy (select 'grant all on t to u' || i || ';' from generate_series(1,10000) i) to 'script.sql'
\i script.sql
Eventually GRANT statements start to raise error.
psql:script.sql:2447: ERROR: row is too big: size 8168, maximum size 8160
I understand that we shouldn't endorse users to behave like this. We should rather advise them to evade adding too many ACL items to single object by using roles. And that would be way easier to manage too.
However, current PostgreSQL behavior is rather unexpected and undocumented. Thus, I would say it's a bug. This bug would be nice to fix even if long ACL lists would work slower than normal.
In the discussion to the post [1] Tom comments that he isn't excited about out-of-line ACL entry unless it's proven that performance doesn't completely tank in this case.
I've done some basic experiments in this field on my laptop. Attached draft patch adds TOAST to all system catalog tables with ACL. I've run pgbench with custom script "SELECT * FROM t;" where t is empty table with long ACL entry. I've compared results with 1000 ACL items (in-line storage) and 10000 ACL items (out-of-line storage).
Also, I've notice performance degradation of GRANT statements themselves. 1000 GRANT statements are executed in 1.5 seconds while 10000 GRANT statements are executed in 42 seconds. In average single GRANT statements becomes 2.8 times slower. That's significant degradation, but it doesn't seem to be fatal degradation for me.
Results of pgbench are presented in following table.
Number of ACL items | -M simple | -M prepared
----------------------------+-
1000 (in-line storage) | 6623 | 7242
10000 (out-of-line storage) | 14498 | 17827
So, it's 2.1-2.4 times degradation in this case. That's very significant degradation, but I wouldn't day that "performance completely tank".
Any thoughts? Should we consider TOASTing ACL entries or should we give up with this?
Links:
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachment
Alexander Korotkov <a.korotkov@postgrespro.ru> writes:
> This topic was already discussed (at least one time) in 2011. See [1] for
> details. I'd like to raise that again.
I'm a bit worried about adding a toast table to pg_class, and more so
about pg_database, because both of those have to be accessed in situations
where it's not clear that we could successfully fetch from a toast table,
because too little of the catalog access infrastructure is alive.
pg_class is probably all right as long as only the ACL field could ever
get toasted, since it's unlikely that any low-level accesses would be
paying attention to that field anyway.
For pg_database, you'd have to make sure that the startup-time check of
database CONNECT privilege still works if the ACL's been pushed out of
line.
> Also, I've notice performance degradation of GRANT statements themselves.
> 1000 GRANT statements are executed in 1.5 seconds while 10000 GRANT
> statements are executed in 42 seconds. In average single GRANT statements
> becomes 2.8 times slower. That's significant degradation, but it doesn't
> seem to be fatal degradation for me.
Seems all right, since we could just say "we don't really recommend that
usage pattern".
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Oct 3, 2017 at 9:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Yes, sure.
Alexander Korotkov <a.korotkov@postgrespro.ru> writes:
> This topic was already discussed (at least one time) in 2011. See [1] for
> details. I'd like to raise that again.
I'm a bit worried about adding a toast table to pg_class, and more so
about pg_database, because both of those have to be accessed in situations
where it's not clear that we could successfully fetch from a toast table,
because too little of the catalog access infrastructure is alive.
pg_class is probably all right as long as only the ACL field could ever
get toasted, since it's unlikely that any low-level accesses would be
paying attention to that field anyway.
For pg_database, you'd have to make sure that the startup-time check of
database CONNECT privilege still works if the ACL's been pushed out of
line.
Thank you for pointing. I'll check this.
> Also, I've notice performance degradation of GRANT statements themselves.
> 1000 GRANT statements are executed in 1.5 seconds while 10000 GRANT
> statements are executed in 42 seconds. In average single GRANT statements
> becomes 2.8 times slower. That's significant degradation, but it doesn't
> seem to be fatal degradation for me.
Seems all right, since we could just say "we don't really recommend that
usage pattern".
The Russian Postgres Company
On 2017-10-03 14:19:09 -0400, Tom Lane wrote: > Alexander Korotkov <a.korotkov@postgrespro.ru> writes: > > This topic was already discussed (at least one time) in 2011. See [1] for > > details. I'd like to raise that again. > > I'm a bit worried about adding a toast table to pg_class, and more so > about pg_database, because both of those have to be accessed in situations > where it's not clear that we could successfully fetch from a toast table, > because too little of the catalog access infrastructure is alive. > > pg_class is probably all right as long as only the ACL field could ever > get toasted, since it's unlikely that any low-level accesses would be > paying attention to that field anyway. I think relpartbound, reloptions are pretty likely to be toasted too if the pg_class tuple is wide enough due to acls. But that seems ok. It'd be a lot easier to test if there were an easier way to force columns to be toasted. Sometimes I wonder about making TOAST_TUPLE_THRESHOLD configurable... > For pg_database, you'd have to make sure that the startup-time check of > database CONNECT privilege still works if the ACL's been pushed out of > line. Yep. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Oct 3, 2017 at 9:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
For pg_database, you'd have to make sure that the startup-time check of
database CONNECT privilege still works if the ACL's been pushed out of
line.
I've tried this case.
At first, make database temp with no connect privilege from public and 10000 users.
create database temp;
revoke connect on database temp from public;
\copy (select 'create user u' || i || ';' from generate_series(1,10000) i) to 'script.sql'
\i script.sql
I've checked that user u10000 can't login to database temp.
$ psql temp -U u10000
psql: FATAL: permission denied for database "temp"
DETAIL: User does not have CONNECT privilege.
Than I grant connect privilege to all that 10000 users.
\copy (select 'grant connect on database temp to u' || i || ';' from generate_series(1,10000) i) to 'script.sql'
\i script.sql
Then user u10000 can login successfully.
$ psql temp -U u10000
psql (11devel)
Type "help" for help.
u10000@temp=#
Thus, in this simple case database CONNECT privilege works with out-of-line ACL for me.
------