CODEWITHSUNDEEP
postgresql
Anton
Anton

Reputation: 41

ERROR: row is too big: size 8168, maximum size 8164

I'm using PostgreSql 9.2 And i have a lot of roles, and all of them have been granted USAGE privileges on a schema. And when i try to create new user and grant privilege i receive an error:

CREATE ROLE my_user; GRANT USAGE on schema my_schema to my_user; ERROR: row is too big: size 8168, maximum size 8164

I have read a lot about how to fix it, but it was an really old articles. Is there any new version of PostgreSql (9.6.8 +) where this bug is fixed?

Upvotes: 4

Views: 5245

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246133

This is because each new permission extends the nspacl column of the pg_namespace entry for this schema.

Now table blocks in PostgreSQL have a size of 8kB, and each table row has to fit into one block, hence the limit.

In normal tables that would not be a problem, because PostgreSQL stores oversized fields of variable length “out of line” in so-called TOAST tables. But there are no TOAST tables for system catalogs, so this escape route is closed.

I'd say that you are running into this limit because of a bad design. Rather than granting each of the roles access to the schema individually, use a hierarchy of roles, assign your many roles to groups and grant schema permissions on the group level.

Upvotes: 3

Related Questions