ERROR: row is too big: size XXX, maximum size 8160 - related to pg_policies table When trying to CREATE POLICY there is the following error:
ERROR: row is too big: size XXX, maximum size 8160
Is there a size limitation on the pg_policies table?
Can that be enlarged or maybe it was bad practice to write so many conditions with joins?
For now, I solved it by changing the query to fewer characters but how can that be avoided?
Example for an almost too long policy.
        CREATE POLICY check_out_insert ON check_out FOR INSERT
            WITH CHECK ( 
                (user_id = current_setting('app.current_user_id')::bigint AND
                file_version_id = ANY(ARRAY(
                    SELECT file_version.id fv_id FROM file_version 
                        INNER JOIN &quot;file&quot; ON (file_version.file_id = &quot;file&quot;.id)
                        INNER JOIN &quot;file_user&quot; ON (&quot;file&quot;.id = &quot;file_user&quot;.file_id AND file_user.deleted_at IS NULL)
                        WHERE file_user.user_id = current_setting('app.current_user_id')::bigint)
                )
                OR
                true = ANY(
                    ARRAY(SELECT is_owner 
                    FROM &quot;group&quot;
                    WHERE id = current_setting('app.current_user_group_id')::bigint
                    LIMIT 1
                    )
                )
                OR
                file_version_id = ANY( 
                    ARRAY(SELECT DISTINCT file_version.id fv_id FROM file_version 
                        INNER JOIN &quot;file&quot; ON (file_version.file_id = &quot;file&quot;.id)
                        INNER JOIN &quot;sub_folder&quot; ON (file.sub_folder_id = &quot;sub_folder&quot;.id)
                        INNER JOIN &quot;folder&quot; ON (sub_folder.folder_id = &quot;folder&quot;.id)
                        INNER JOIN &quot;folder_group&quot; ON (folder_group.folder_id = &quot;folder&quot;.id AND folder_group.group_id = current_setting('app.current_user_group_id')::bigint)
                        INNER JOIN &quot;project_user&quot; ON (folder.project_id = &quot;project_user&quot;.project_id AND &quot;project_user&quot;.user_id = current_setting('app.current_user_id')::bigint)
                        WHERE folder.type = 'nda'
                    )
                )
            )
        );`);
You are probably running a PostgreSQL version older than v12, because pg_policy has a TOAST table since v12, so that oversized attributes can be stored out of line.
Before that, you were limited to policies where the pg_policy row fits into a single table block. Your policy text is less than that, but PostgreSQL stored the parsed query tree, which can be larger than the text.
Upgrade to a later PostgreSQL release or use several PERMISSIVE policies, which will be connected with OR.