Search Postgresql Archives

Re: How to keep format of views source code as entered?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"Markhof, Ingolf" <ingolf.markhof@xxxxxxxxxxxxxx> 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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux