Re: How to keep format of views source code as entered? - Mailing list pgsql-general

From Tom Lane
Subject Re: How to keep format of views source code as entered?
Date
Msg-id 284500.1610120473@sss.pgh.pa.us
Whole thread Raw
In response to RE: How to keep format of views source code as entered?  ("Markhof, Ingolf" <ingolf.markhof@de.verizon.com>)
List pgsql-general
"Markhof, Ingolf" <ingolf.markhof@de.verizon.com> writes:
> I am really surprised that PostgreSQL is unable to keep the source text
> of a view. Honestly, for me the looks like an implementation gap.

Perhaps, but the "gap" is wider than you seem to think.  Consider

CREATE TABLE t1 (f1 int, f2 text);
CREATE VIEW v1 AS SELECT f2 FROM t1;
ALTER TABLE t1 RENAME COLUMN f2 TO zed;
\d+ v1
                            View "public.v1"
 Column | Type | Collation | Nullable | Default | Storage  | Description 
--------+------+-----------+----------+---------+----------+-------------
 f2     | text |           |          |         | extended | 
View definition:
 SELECT t1.zed AS f2
   FROM t1;

At this point the original text of the view is useless; with
another rename or two it could become downright misleading.

Another issue revolves around the fact that a textual SQL statement
is seldom totally unambiguous.  In the above example, the fact that
"t1" refers to public.t1 and not some other t1 depends on the
search_path as it stood at CREATE VIEW time.  If you change your
search_path you might need an explicit schema qualification.
The reverse-parsed view display accounts for that:

# set search_path = pg_catalog;
# \d+ public.v1
                            View "public.v1"
 Column | Type | Collation | Nullable | Default | Storage  | Description 
--------+------+-----------+----------+---------+----------+-------------
 f2     | text |           |          |         | extended | 
View definition:
 SELECT t1.zed AS f2
   FROM public.t1;

but a static source text could not.  In PG this hazard applies to
functions and operators not only tables.  If pg_dump regurgitated
the original view text, there would be trivially-exploitable
security holes that allow some other user to take control of your
view after a dump/reload.

We actually used to store both text and parsed versions of some
sub-constructs, such as CHECK constraints and column default values.
We got rid of the text versions because there was no reasonable way
to keep them up-to-date.  (And, AFAIR, there hasn't been a lot of
push-back about those catalog columns disappearing.)  So I don't
think we'd accept a patch to store the text form of a view, unless
some solution to these issues were provided.

            regards, tom lane



pgsql-general by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: Missing declaration of _PG_init()
Next
From: Tom Lane
Date:
Subject: Re: Missing declaration of _PG_init()