Thread: function depend on view

salah jubeh <s_jubeh@yahoo.com> wrote:

> Hello Guys,
>
> I am having a scenario close to the one below, I have defined a function which
> depends on a view. I am able to drop the view, but my server did not complain
> about the dependency.
>
> In the scenario  below, one can drop the views a2 and a1 respectively, and when
> executing a3(), certainly an exception will be raised. I think there should be
> an entry for this dependency in the pg_depend table with dependency type normal

I think, there is no real problem, it's (for me) the same as:

test=# select max(id) from table_that_does_not_exist;
ERROR:  relation "table_that_does_not_exist" does not exist
LINE 1: select max(id) from table_that_does_not_exist;


The database can't know and analyse the function-body.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On Mon, Aug 20, 2012 at 9:37 AM, salah jubeh <s_jubeh@yahoo.com> wrote:
> Hello Andreas,
>
> Thanks for the reply,  The example I have posted is very simple and you are
> right it is very similar to select max (id) from table_that_does_not_exist;
> But there are more here, for example imagine I have something like
>
> CREATE VIEW a4 as select .... from  a3(), .... ;
>
> In my opinion, this might leads to many problems such as
>
> 1.  A lot of garbage in the database including functions refers to non
> existing objects and views defined over these functions.
> 2.  This might also lead to some logical errors; especially, if you have a
> view defined over such functions and used in external applications.
> 3.  You will get also a broken dependency graph, in the above example it is
> obvious that a4 depends on a3 which depends on a2.

If that bothers you, organize views and functions into scripts so that
you can rebuild the entire suite at will.  In practice, I find the
problem of tables that functions depend on either A. disappearing or
B. structurally changing so that the fundamental behavior of the
function is broken to be a fairly rare problem.  It's much more common
to have to add fields, change types, etc.

merlin

salah jubeh <s_jubeh@yahoo.com> wrote:

> Hello Andreas,
>
> Thanks for the reply,  The example I have posted is very simple and you are
> right it is very similar to select max (id) from table_that_does_not_exist; But
> there are more here, for example imagine I have something like
>
> CREATE VIEW a4 as select .... from  a3(), .... ;
>
> In my opinion, this might leads to many problems such as

Again, the functions doesn't depend on the table, other example:

test=# select * from foo;
 id | val
----+-----
  1 | 100
  2 | 200
  3 | 300
(3 rows)

Time: 0,235 ms
test=*# create or replace function fail(int) returns int as $$declare r
int; begin execute 'select val from foo where id = ' || $1 || ';' into
r; return r;end; $$language plpgsql;
CREATE FUNCTION
Time: 0,547 ms
test=*# select * from fail(1);
 fail
------
  100
(1 row)

Time: 0,473 ms
test=*# drop table foo;
DROP TABLE
Time: 0,387 ms
test=*# select * from fail(1);
ERROR:  relation "foo" does not exist
LINE 1: select val from foo where id = 1;
                        ^
QUERY:  select val from foo where id = 1;
CONTEXT:  PL/pgSQL function "fail" line 1 at EXECUTE statement


In this case, the query inside the function is a dynamic query. So what?



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On Mon, Aug 20, 2012 at 9:59 AM, Andreas Kretschmer
<akretschmer@spamfence.net> wrote:
> salah jubeh <s_jubeh@yahoo.com> wrote:
>
>> Hello Andreas,
>>
>> Thanks for the reply,  The example I have posted is very simple and you are
>> right it is very similar to select max (id) from table_that_does_not_exist; But
>> there are more here, for example imagine I have something like
>>
>> CREATE VIEW a4 as select .... from  a3(), .... ;
>>
>> In my opinion, this might leads to many problems such as
>
> Again, the functions doesn't depend on the table, other example:
>
> test=# select * from foo;
>  id | val
> ----+-----
>   1 | 100
>   2 | 200
>   3 | 300
> (3 rows)
>
> Time: 0,235 ms
> test=*# create or replace function fail(int) returns int as $$declare r
> int; begin execute 'select val from foo where id = ' || $1 || ';' into
> r; return r;end; $$language plpgsql;
> CREATE FUNCTION
> Time: 0,547 ms
> test=*# select * from fail(1);
>  fail
> ------
>   100
> (1 row)
>
> Time: 0,473 ms
> test=*# drop table foo;
> DROP TABLE
> Time: 0,387 ms
> test=*# select * from fail(1);
> ERROR:  relation "foo" does not exist
> LINE 1: select val from foo where id = 1;
>                         ^
> QUERY:  select val from foo where id = 1;
> CONTEXT:  PL/pgSQL function "fail" line 1 at EXECUTE statement
>
>
> In this case, the query inside the function is a dynamic query. So what?

Another aspect that makes inside the body function dependency checking
really difficult is search_path dependencies.

merlin