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

[Tim . Colles]

On Fri, 8 Jan 2021, Karsten Hilbert wrote:


Am Fri, Jan 08, 2021 at 08:38:29AM + schrieb Markhof, Ingolf:


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. Consider software development. You are
writing code in C++ maybe on a UNIX host. And whenever you
feed you source code into the compiler, it will delete it,
keeping the resulting executable, only.


You expect the compiler to keep your source code for you ?

Most certainly, PostgreSQL does not delete your view source
code, just as the compiler does.

I am not so sure that analogy holds up.



The SQL-92 standard requires the source text of a view to be held in
order to provide the "information_schema" "view definition" column which
"contains a representation of the view descriptors” - to me though it is
open to interpretation what that actually means. MariaDB, like
PostgreSQL, does not not store an exact copy of the view source either.

The SQL-92 standard is completely explicit about column expansion: “NOTE
13: Any implicit column references that were contained in the  associated with the  are replaced by
explicit column references in VIEW_DEFINITION.” - so any view definition
that is stored, solely for the purposes of standard compliance, will at
a minimum have to differ from the original source if the source had any
implicit column references (and by association table references as well
I assume).

Arguably if PostgreSQL held an exact copy of the view definition (except
for alterations under Note 13 above) then it should also store exact
copies of other pre-parsed objects, such as DEFAULT on table columns and
WITH on trigger clauses, in order to be useful under the OP's context.

See also:

http://www.postgresql-archive.org/idea-storing-view-source-in-system-catalogs-td1987401.html
http://www.postgresql-archive.org/Preserving-the-source-code-of-views-td5775163.html
--
The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.

odd (maybe) procedure cacheing behaviour

[Tim . Colles]

See noddy example below (v14.6).

Presumably this is a result of procedure cacheing as per docs.  The EXECUTE
plan is being prepared fresh (again as expected from the docs and per the error
message) but is the input argument type of NEW.x for the format() call still
cached? Is altering a table column used in a trigger function something that
can be detected and the cache then invalidated? Also "parameter 14" - is there
a way to correlate back from the error message what that paramater acually
corresponds to for the user?

Tim


=# create table a ( x text );
CREATE TABLE

=# create function a_t () returns trigger language plpgsql as $$ begin execute 
format('select least(%L)', NEW.x); return null; end; $$;
CREATE FUNCTION

=# create trigger a_i after insert on a for each row execute function a_t();
CREATE TRIGGER

=# insert into a values ( '1' );
INSERT 0 1

=# alter table a alter column x type integer using x::integer;
ALTER TABLE

=# insert into a values ( 1 );
ERROR:  type of parameter 14 (integer) does not match that when preparing the 
plan (text)
CONTEXT:  PL/pgSQL function a_t() line 1 at EXECUTE

=# create or replace function a_t () returns trigger language plpgsql as $$ 
begin execute format('select least(%L)', NEW.x); return null; end; $$;
CREATE FUNCTION

=# insert into a values ( 1 );
INSERT 0 1
The University of Edinburgh is a charitable body, registered in Scotland, with 
registration number SC005336. Is e buidheann carthannais a th’ ann an Oilthigh 
Dhùn Èideann, clàraichte an Alba, àireamh clàraidh SC005336.