Chris Campbell <chris@bignerdranch.com> writes:

> What do you think? Is this an interesting feature? Is this the right way to go
> about it, or should I try to get the planner to see through SQL function
> boundaries

The "right" way to go about this in the original abstract set-theoretic
mindset of SQL is to code the view to retrieve all the rows and then apply
further WHERE clause restrictions to the results of the view.


So for example this:

> CREATE VIEW sales_figures($1, $2) AS
> SELECT ... FROM ... WHERE purchase_date BETWEEN $1 AND $2;

Becomes:

CREATE VIEW sales_figures AS SELECT ... FROM ...

And then you query it with

SELECT * FROM sales_figures WHERE purchase_date BETWEEN $1 AND $2

sales_figures could have any number of joins and complex where clauses
built-in. It could even be an aggregate grouped by some column (like
purchase_date).

This relies on the SQL optimizer to push the WHERE clause down into the view
to the appropriate depth. Postgres isn't always capable of doing so but it
does a pretty decent job.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Feb 2, 2006, at 23:33, Greg Stark wrote:

> The "right" way to go about this in the original abstract set-
> theoretic
> mindset of SQL is to code the view to retrieve all the rows and
> then apply
> further WHERE clause restrictions to the results of the view.
>
> So for example this:
>
>> CREATE VIEW sales_figures($1, $2) AS
>> SELECT ... FROM ... WHERE purchase_date BETWEEN $1 AND $2;
>
> Becomes:
>
> CREATE VIEW sales_figures AS SELECT ... FROM ...
>
> And then you query it with
>
> SELECT * FROM sales_figures WHERE purchase_date BETWEEN $1 AND $2

That was a very simplistic example and didn't illustrate my point --
I apologize. I was trying to think of something succinct and
illustrative for a quick mailing list post but came up short.

Maybe a better example would be a situation where you want to do
substitutions in places other than the WHERE clause? There's no way
to "push" that out to the calling query. But even in this simple
case, the easier-to-grok syntax of making a view look like a function
(and codifying the options for restricting the results as arguments
to the view) is a nice win in terms of readability and maintainability.

I was hoping that people would overlook my bad example because
they've had the need for a "view with arguments" tool in their own
work, and the conversation would just be about how it could be
implemented.

I'll try to distill a better example from some of the projects I'm
working on.

Thanks!

- Chris


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Chris Campbell schrieb:
....
> That was a very simplistic example and didn't illustrate my point -- I
> apologize. I was trying to think of something succinct and illustrative
> for a quick mailing list post but came up short.
>
> Maybe a better example would be a situation where you want to do
> substitutions in places other than the WHERE clause? There's no way to
> "push" that out to the calling query. But even in this simple case, the
> easier-to-grok syntax of making a view look like a function (and
> codifying the options for restricting the results as arguments to the
> view) is a nice win in terms of readability and maintainability.

Well if the view does not suit your needs, why dont you use an
set returnung function instead? Inside it you can do all the magic
you want and still use it similar to a table or view.

Regards
Tino

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Feb 3, 2006, at 02:09, Tino Wildenhain wrote:

> Well if the view does not suit your needs, why dont you use an
> set returnung function instead? Inside it you can do all the magic
> you want and still use it similar to a table or view.

That's what I'm currently doing (as explained in the first message in
the thread). But the function is a "black box" to the planner when
the query is executed -- I'd like the planner to be able to combine
the query inside the function with the outer calling query and plan
it as one big query. Like it does with views. Thus, "views with
arguments."

We're certainly not deficient in this area (set-returning functions
fill the need quite well), but a feature like this would go even
further in terms of ease-of-use and performance.

Benefits of "views with arguments" versus functions:

* Better query execution performance because the planner can plan the
whole query (rewriting the original query to replace references to
the view with the view's definition -- this is how views work today)

* PostgreSQL-tracked dependancies: views create dependencies on the
relations they reference -- functions do not

* Don't have to manually maintain a composite type for the return value

Basically, better performance and easier administration.

Thanks!

- Chris


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Fri, Feb 03, 2006 at 08:33:23AM -0500, Chris Campbell wrote:
> On Feb 3, 2006, at 02:09, Tino Wildenhain wrote:
>
> >Well if the view does not suit your needs, why dont you use an
> >set returnung function instead? Inside it you can do all the magic
> >you want and still use it similar to a table or view.
>
> That's what I'm currently doing (as explained in the first message in
> the thread). But the function is a "black box" to the planner when
> the query is executed -- I'd like the planner to be able to combine
> the query inside the function with the outer calling query and plan
> it as one big query. Like it does with views. Thus, "views with
> arguments."
>
> We're certainly not deficient in this area (set-returning functions
> fill the need quite well), but a feature like this would go even
> further in terms of ease-of-use and performance.

Hmm, we actually do inline SQL functions under certain situations, but
only for "simple" queries (see inline_function in
optimizer/util/clauses.c). One approach would be to expand that
function to inline more complicated things.

> Benefits of "views with arguments" versus functions:
>
> * Better query execution performance because the planner can plan the
> whole query (rewriting the original query to replace references to
> the view with the view's definition -- this is how views work today)

Well, the inlining would acheive the same effect.

> * PostgreSQL-tracked dependancies: views create dependencies on the
> relations they reference -- functions do not

Technically a bug. We should be tracking dependancies for functions
anyway.

> * Don't have to manually maintain a composite type for the return value

This is a good point. Though with syntactic sugar you could work around
this too...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFD41/AIB7bNG8LQkwRArYhAJ9RxPIYXnuE7e/aE2gSpcLkppUKXQCdFTI+
41wzBnCFhF/9hA/kzVN+PlY=
=L1sp
-----END PGP SIGNATURE-----

On Feb 3, 2006, at 08:50, Martijn van Oosterhout wrote:

> Hmm, we actually do inline SQL functions under certain situations, but
> only for "simple" queries (see inline_function in
> optimizer/util/clauses.c). One approach would be to expand that
> function to inline more complicated things.

>> * Better query execution performance because the planner can plan the
>> whole query (rewriting the original query to replace references to
>> the view with the view's definition -- this is how views work today)
>
> Well, the inlining would acheive the same effect.

So you think approaching it from the "beefing up functions" side
would be better than the "beefing up views" side?

>> * PostgreSQL-tracked dependancies: views create dependencies on the
>> relations they reference -- functions do not
>
> Technically a bug. We should be tracking dependancies for functions
> anyway.

Well, it's impossible to track dependencies for all functions, since
they're just strings (or compiled code in shared libraries) until
they're executed. But maybe SQL language functions could be special-
cased? Do you think it would be easier to add dependancy-tracking for
functions, or would it be easier to implement this functionality
using the more-restrictive-language but better-dependency-tracking
view system? When you add dependencies for things that didn't have
dependencies before (like non-SQL functions), you create all sorts of
backwards-compatibility problems due to the ordering that things need
to be dumped and created, and circular dependancies.

For example, this works:

CREATE FUNCTION foo(INTEGER) RETURNS INTEGER AS 'BEGIN RETURN bar
($1-1); END;' LANGUAGE plpgsql;

CREATE FUNCTION bar(INTEGER) RETURNS INTEGER AS 'BEGIN IF $1 < 0
THEN RETURN $1; ELSE RETURN foo($1); END IF; END;' LANGUAGE plpgsql;

But it wouldn't work if PostgreSQL tracked and enforced dependancies.
But it could probably be done with SQL-language functions only. I
don't know if we'd want to add dependancy tracking for functions if
it only works for SQL-language functions, though.

> This is a good point. Though with syntactic sugar you could work
> around
> this too...

Basically, how views do it? By auto-creating a table with the
proper columns (for a function, that would be an auto-created type).

I'm looking for a function/view hybrid, taking features from each. It
seems to me that views have most of the features I want (only missing
the ability to pass arguments), so it's a shorter distance to the
goal than by starting with functions.

Thanks!

- Chris


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Fri, Feb 03, 2006 at 09:18:51AM -0500, Chris Campbell wrote:
> So you think approaching it from the "beefing up functions" side
> would be better than the "beefing up views" side?

Well yes, I think you're underestimating the issues with trying to
extend views.

> >Technically a bug. We should be tracking dependancies for functions
> >anyway.
>
> Well, it's impossible to track dependencies for all functions, since
> they're just strings (or compiled code in shared libraries) until

<snip>

Sure, for most procedural languages you can't do much. But we do do
syntax checking already and checking that the necessary functions exist
can be considered part of that. It' s not terribly important though.

> Basically, how views do it? By auto-creating a table with the
> proper columns (for a function, that would be an auto-created type).
>
> I'm looking for a function/view hybrid, taking features from each. It
> seems to me that views have most of the features I want (only missing
> the ability to pass arguments), so it's a shorter distance to the
> goal than by starting with functions.

Ok, here's the deal. A view is nothing more than a RULE. Creating a
view does this automatically:

CREATE RULE blah AS ON SELECT TO myview DO INSTEAD <select statement>;

Now, say you wanted to add parameters to this, would you restrict it to
SELECT rules, what about UPDATE or DELETE rules?

UPDATE myview(3,4) SET ...;

The other issue is that currently you can tell from looking at a
statement whether something is a function or a table (is it followed by
an open parenthesis?). With the above change you can't anymore, which
might mean you can't have functions and tables with the same names
because they'd be ambiguous.

On the whole, I think allowing the server to inline SRFs would be a far
better way to go...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFD43XuIB7bNG8LQkwRAvNkAJ9wOou/sKNb+HYf1DMNoaBXluMQaACeKAmm
H1wm5RZgILXYt/zteZ1HD+g=
=I4K2
-----END PGP SIGNATURE-----

On Feb 3, 2006, at 10:25, Martijn van Oosterhout wrote:

> Sure, for most procedural languages you can't do much. But we do do
> syntax checking already and checking that the necessary functions
> exist
> can be considered part of that. It' s not terribly important though.

Dependancy tracking needs to be more than a "best effort." If you
can't do it accurately and completely, then I don't think it's worth
doing at all.

But I agree with you that syntax checking is probably sufficient. We
don't need true dependancy tracking for functions.

The only failure case (where syntax checking won't help you) is
deleting (or renaming, or modifying) a table that a function was
using. If you were to run or re-define the function, you'd learn
about the missing (or renamed, or modified) table, whereas the
dependancy-tracking system would prevent you from making the changes
to the referenced table in the first place.

> Ok, here's the deal. A view is nothing more than a RULE. Creating a
> view does this automatically

Technically, it's a table and a rule, both of which are created by
the CREATE VIEW command. We were talking about syntactic sugar, and
CREATE VIEW is syntactic sugar for doing a CREATE TABLE and a CREATE
RULE. That was my comparison. I'm aware of how views work. Here's the
deal: I want to beef up rules versus beefing up functions. Maybe
that's not the way to go; I'm enjoying this discussion and your
insights.

> CREATE RULE blah AS ON SELECT TO myview DO INSTEAD <select statement>;
>
> Now, say you wanted to add parameters to this, would you restrict
> it to
> SELECT rules, what about UPDATE or DELETE rules?

I don't see a huge use case for anything but SELECT rules, but I
think it could be extended to any rule type. Maybe the CREATE RULE
syntax could be something like:

CREATE RULE blah AS ON SELECT(INTEGER, INTEGER, DATE) TO myview
DO INSTEAD SELECT * FROM sale WHERE sale_date = $3;

> The other issue is that currently you can tell from looking at a
> statement whether something is a function or a table (is it
> followed by
> an open parenthesis?). With the above change you can't anymore, which
> might mean you can't have functions and tables with the same names
> because they'd be ambiguous.

Right. I said that my example syntax was deficient in this regard in
the first message in this thread. And I solicited ideas for a better
(unambiguous) syntax. I'm sure we would be able to come up with
something. Maybe using square brackets instead of parentheses? Curly
braces? "myview->(1, 2, 3)" notation? Since views are tables (when
parsing the query, at least) we'd have to allow this syntax for any
table reference, but throw an error (or silently discard the
arguments) if the table didn't have a rule matching the argument types?

> On the whole, I think allowing the server to inline SRFs would be a
> far
> better way to go...

Maybe, but the highly-structured view definition syntax and
everything that comes with it (dependancy tracking primarily) is so
tasty. I think a little grammar hacking and a couple extra columns in
pg_rewrite (nargs and argtypes) would get us most of the way there.

I would much rather put more stringent requirements on the programmer
when defining his query (like a view), versus letting him submit any
old string as a function (like a function). The database can do so
much more when it's able to work with a better representation of the
computation.

At the core, I want query rewriting with arguments. That sounds like
a better fit for views/rules than functions, so that's why I keep
coming back to it.

Thanks!

- Chris


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Chris Campbell <chris@bignerdranch.com> writes:
> I want to beef up rules versus beefing up functions.

Martijn didn't present a very convincing argument why this is a bad
idea, but I agree with him that it is. The problem to me is that a
"view with arguments" is fundamentally wrong --- a view is a virtual
table and there is no such thing as a table with arguments. The whole
notion distorts the relational data model beyond recognition.

The SRF concept captures what you want a whole lot better. If the
implementation isn't up to snuff, we should improve it, not warp other
pieces of the system.

Martijn mentioned the idea of inlining SQL functions that return sets
--- this is something I've toyed with too, but not got round to looking
at seriously. AFAICS it would accomplish everything that you could do
with parameters in ON SELECT rules, considering the existing
restrictions on what can be in an ON SELECT rule. And it wouldn't
require any new concepts at all, just a few(?) pages of code.

As for the dependency issue, one man's bug is another man's feature.
I think the fact that we don't track the internal dependencies of
functions is not all bad. We've certainly seen plenty of complaints
about how you can't easily change tables that a view is depending on
because the view dependencies block it...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Feb 3, 2006, at 11:21, Tom Lane wrote:

> The SRF concept captures what you want a whole lot better. If the
> implementation isn't up to snuff, we should improve it, not warp other
> pieces of the system.

Point taken. The rewriting concept is what I'm after; if that can be
done pre-planning with SQL functions, I'm all for it. I just thought
that since rules already do rewriting, that's the best thing to start
building on.

> Martijn mentioned the idea of inlining SQL functions that return sets
> --- this is something I've toyed with too, but not got round to
> looking
> at seriously. AFAICS it would accomplish everything that you could do
> with parameters in ON SELECT rules, considering the existing
> restrictions on what can be in an ON SELECT rule. And it wouldn't
> require any new concepts at all, just a few(?) pages of code.

True, as long as there's a hook to do the inlining/rewriting before
the query's planned. I guess we can see function calls at the parse
stage, check to see if they're SQL functions or not, grab the prosrc,
do the substitution, then re-parse?

I guess I can live without the dependancy tracking. I can always dump
and reload my database to re-parse all the functions. Maybe we could
have a RELOAD FUNCTION command that would just re-parse an existing
function, so I don't have to dump and reload?

What about auto-creating a composite type for the function's return
type based on the query definition? (Like how CREATE VIEW creates an
appropriate table definition.) Do you see a way for CREATE FUNCTION
to do that? The problem is that you have to specify a return type in
CREATE FUNCTION.

Maybe an extension to CREATE FUNCTION as a shorthand for set-
returning SQL functions? Like:

CREATE SQL FUNCTION sales_figures(DATE) AS SELECT ... FROM ...
WHERE sale_date <= $1;

It would (1) automatically create a composite type (newtype) for the
return value, and (2) do a

CREATE FUNCTION sales_figures(DATE) RETURNS SETOF newtype AS
'...' LANGUAGE sql.

How much do I have to justify a patch for non-standard "RELOAD
FUNCTION" and "CREATE SQL FUNCTION" commands (as described) in the
grammar?

Thanks!

- Chris


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match