Re: missing toast table for pg_policy

On 02/17/2018 11:39 AM, Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>> Yes, exactly. I'm fine with not backpatching, just wanted to raise the
>> possibility. I will push later today to HEAD (with a catalog version bump).
>
> BTW, I was wondering if it'd be a good idea to try to forestall future
> oversights of this sort by adding a test query in, say, misc_sanity.sql.
> Something like
>
> select relname, attname, atttypid::regtype
> from pg_class c join pg_attribute a on c.oid = attrelid
> where c.oid < 16384 and reltoastrelid=0 and relkind = 'r' and attstorage != 'p'
> order by 1,2;
>
> If you try that you'll see the list is quite long:

<snip>

> I think in most of these cases we've consciously decided not to toast-ify,
> but maybe some of them need a second look.

Is there really a compelling reason to not just create toast tables for
all system catalogs as in the attached? Then we could just check for 0
rows in misc_sanity.sql.

For what its worth:
--------------------
HEAD
--------------------
# du -h --max-depth=1 $PGDATA
[...]
22M /usr/local/pgsql-head/data/base
[...]
584K /usr/local/pgsql-head/data/global
[...]
38M /usr/local/pgsql-head/data

time make check
real 0m16.295s
user 0m3.597s
sys 0m1.465s

--------------------
with patch
--------------------
# du -h --max-depth=1 $PGDATA
[...]
23M /usr/local/pgsql-head/data/base
[...]
632K /usr/local/pgsql-head/data/global
[...]
39M /usr/local/pgsql-head/data

time make check
real 0m16.462s
user 0m3.521s
sys 0m1.531s

select relname, attname, atttypid::regtype
from pg_class c join pg_attribute a on c.oid = attrelid
where c.oid < 16384 and reltoastrelid=0 and relkind = 'r' and attstorage
!= 'p'
order by 1,2;
relname | attname | atttypid
---------+---------+----------
(0 rows)

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development