Re: pg_get_viewdefs() indentation considered harmful

On Fri, Jan 24, 2014 at 6:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> pg_get_viewdefs indents each branch of the union by 8 spaces more than
>> the previous branch.
>
> I think that's because the unions are a nested binary tree so far as the
> parsetree representation goes. We could probably teach ruleutils to
> flatten the display in common cases, but it might be a bit tricky to be
> sure we don't create any lies about unusual nestings.

That may be a worthwhile thing to do.

But it strikes me that pg_dump, at least when not doing an SQL dump,
really has no reason to ask for indentation at all. It's already
asking for non-prettyprinted output, why not make non-prettyprinted
also mean non-indented?

Also, it also seems like a reasonable safeguard that if the underlying
rule is larger than, say, 32kB or maybe 128kB you probably don't care
about indentation. That would cover all the system views except a
handful that seem to have peculiarly large pg_rewrite rules
considering their SQL definition isn't especially large:

daeqck898dvduj=> select ev_class::regclass, length(ev_action)
rewrite_len,length(pg_get_viewdef(ev_class,true)) prettyprint_len,
length(pg_get_viewdef(ev_class,false)) non_prettyprint_len from
pg_rewrite order by 2 desc limit 20;
ev_class | rewrite_len |
prettyprint_len | non_prettyprint_len
--------------------------------------------+-------------+-----------------+---------------------
pg_seclabels | 138565 |
13528 | 13758
information_schema.columns | 126787 |
6401 | 6642
information_schema.usage_privileges | 93863 |
11653 | 11939
information_schema.attributes | 83268 |
4264 | 4417
information_schema.referential_constraints | 81762 |
2527 | 2653
pg_statio_all_tables | 59617 |
1023 | 1061
pg_stats | 59331 |
2803 | 2899
information_schema.domains | 54611 |
3206 | 3320
information_schema.element_types | 53049 |
5608 | 5762
information_schema.routines | 52333 |
7852 | 8089
information_schema.column_privileges | 49229 |
3883 | 3954
pg_indexes | 46717 |
458 | 486
information_schema.check_constraints | 42132 |
1375 | 1443
information_schema.tables | 37458 |
2122 | 2212
pg_stat_all_indexes | 35426 |
508 | 528
pg_statio_all_indexes | 35412 |
490 | 512
information_schema.table_constraints | 31882 |
3098 | 3231
information_schema.column_udt_usage | 31731 |
1034 | 1090
information_schema.parameters | 30497 |
3640 | 3750
pg_stat_all_tables | 27193 |
1367 | 1387
(20 rows)

--
greg