Hacker News Reader

Comment by pdntspa | Hacker News Reader

I can't believe that at time of this writing, only a few other comments are mentioning VIEWs! Like that is this dude's exact complaint!

smh...

View on HN

Views, especially Materialized Views if you’re on Postgres, are about as close as you can come to a “one neat trick that solves all your problems” in modern software development imo.

I'd love to use materialised views more often but my experience is that they're full of annoying limitations, typically around functions that feel like they should be immutable but aren't.

Right, I think the timestamp with timezone type is a great example of one of these short comings.

I’ve generally used it as a step in a data pipeline, where some intermittent state or aggregation where being stale/stable for a period of time is ok. Did this recently for a daily trends report on some news data - live querying the data is slow, but baking it off into a MV makes it much faster and keeps the query result from changing too rapidly to new data.

Materialized views + pg_cron = chef's kiss for simple yet performant report generation.

Let's say you have 14 derived facts (eg aggregations, computations involving joined tables, etc) about a SalesPerson. Do create 14 single-column views? Or have one view with 14 columns? Either way the composability is terrible. If you go with 14 views, the syntax is awful and not very resistant to refactoring. If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization).

You can SELECT just the columns you want with one view, and IIRC that will exclude other columns from being retrieved unless they are the source of a derived value.

> If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization).

This is outdated; most modern DBs treat views as they would CTEs. If you don’t reference a defined field downstream, it doesn’t need to get calculated.

It's more about the fact that a join can change the shape of the result set. Even if the columns being projected aren't surfaced, the DB still has to process the joins to make sure the result set has the correct shape. For example, a human might know that a join will always be one:one or one:zero-or-one, but the DB has no choice but to make sure. Perhaps using subqueries instead of joins would work, but that gets ugly too.

(Or maybe my knowledge is outdated and the optimizers have gotten way better than they were 3-4 years ago.)

At least PostgreSQL's query optimizer can and will drop `LEFT JOIN` clauses if the data is not actually being used. It can't do that for `INNER JOIN` because it must verify that a matching row exists.

For LEFT join it would also need to know the combination of columns you are joining by are unique in the right table, which will be the case in many scenarios (joining on a primary key) but not in the general case.

That's an excellent point. I hadn't thought of it because the joins where I witnessed this were all on primary keys, as $DIETY intended. (That's a joke.)

With most engines, this can be optimized with indexing (or indexed views) very easily to the extent it would be negligible.

I'll add that MSSQL at least since 2019 will automatically modify the execution plan to avoid this by the second time the query is executed.

As others have said, any decent optimiser will prune out values that aren't SELECT'ed. I don't think it's that complex either - find the unused columns, remove them from subviews, recurse all the way down. Try it on MSSQL/Postgres and you can see it being done.

The bigger problem is views are tightly coupled to the underlying data.

Right, there's a big difference between views and the kind of generic abstraction that I think the original poster seeks. You do need something like macros or generated SQL to build a library of algorithms you can apply to data.

Imagine wanting something as simple as "find_percentile_value(table, column, percentile)". There is no portable and standard way to write this parametric abstraction and then reuse it on any column source you need to interrogate later.

Please give a simple example of what your second para means. Perhaps find_% of all values in the row vs the referenced column, in the referenced table.

Now, ... reuse. Surely you write the result to another table and then refer to that.

The detail of the function isn't that important, but I was thinking of something like the numpy percentile function as an example of a simple functional abstraction. You give it an array representing the population of values and a desired percentile (0..100), and it returns the value corresponding to that desired percentile. Calling it for percentile 50 would return the median value. Other options might select alternative methods for tie-breaking or interpolation between population samples.

Note, this was just meant as an example where a view is not helpful. We don't always desire particular calculated data to be named for reuse. Rather, we want a reusable method we can apply to any data. In my earlier post, I tried to address the topic of macros or generated SQL, and so suggested it take a table and column name as input to expand a query idiom for a set of values stored in a table. But, that's not how I'd really want to do it. The rest of this comment will veer into a more elaborate perspective on real functional libraries, rather than mere macros...

Today, with dialect-specific mechanisms, I can try to define window functions or ordered-set aggregate functions. It would be possible to solve the percentile problem this way. In fact, many SQL dialects have built-in functions that can help with percentiles, since this is recognized as such a basic statistical concept. But, I only chose percentile as an accessible yet concrete example. These built-in solutions are not the answer to my question. My question is how can user programmers extend the system with their own abstractions.

Writing aggregate and window functions requires a big mental shift for the programmer. You usually need to define a state variable and provide a set of input/output/accumulator functions and register all that as an aggregate function. This is because of the way this calling convention is meant to be integrated into query plans in a streaming fashion. It's a bit like forcing a programmer to learn some async or co-routine calling convention without offering a simpler synchronous option as a starting point. It's an obstacle.

The application of window or ordered-set aggregate functions also require a lot of verbose syntax to wire up columns or expressions to function inputs and to configure the windows and ordering modes. There isn't really any support to abstract these details away behind the named function, such that the naive user could just call it without knowing how it works internally to solve the whole problem. In other words, having a functional abstraction which can encapsulate data-preconditioning behind an easier interface.

Finally, this approach only addresses the case of reducing a set to a scalar or assigning new scalars for every row in a set. To match the generality of the numpy API would require functions that take sets as input and produce arbitrarily different sets as output. It also needs the host language to allow low-syntax manipulation of intermediate data when composing functions.

So, for full generality, a data transform function might take one set as input and produce another set as output. We would also want things like: set-based function interface typing and function overloading; functions that can setup their own desired traversals of input data including sorting and filtering; functions that can allocate (potentially) large intermediate data using sets or other common data structure idioms; and complementary call-site syntax to easily compose functions with little syntactic boilerplate but also with low marginal syntax overhead to customize and restructure data passed between functions.

>functions that take sets as input and produce arbitrarily different sets as output

It is possible to return arbitrary sets from functions in many systems-- recent versions of the SQL standard even specify table functions (with syntax "RETURNS TABLE(columns)")[0] as well as polymorphic table functions (PTFs)[1]. And in addition to implementations of those standards, extensions like PL/[pg]SQL also have a few other ways of effecting table-like returns (parameter modes, composite type returns).

>function overloading

Not standard, but some systems support it[2].

[0] https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC...

[1] https://share.ansi.org/Shared%20Documents/News%20and%20Publi...

[2] https://www.postgresql.org/docs/current/xfunc-overload.html

Right, I'm aware of table-returning functions and the general idea of functions as table sources in a FROM clause. I think the biggest gap is not having complementary table-consuming function signatures and ways to use functions as table sinks other than aggregate function calls.

I'd also want to allow a function to be both a table source and a table sink, which I think means that you might need a sub-query like syntax to supply the source that will be consumed by the function. This is a vague idea and not a well-defined language proposal:

    CREATE OR REPLACE FUNCTION
    -- invent a table-sink func signature?
    func1( TABLE t1(a int, b text, c text) )
    RETURNS TABLE (x int, y boolean) AS $$
      -- access input set via declared name
      SELECT 
        a, b < c
      FROM t1
    $$ LANGUAGE SQL;

    CREATE OR REPLACE FUNCTION
    func2( TABLE t1(a int, b boolean) )
    RETURNS int AS $$
      SELECT a WHERE b
    $$ LANGUAGE SQL;

    -- implicit positional matching of func input cols?
    SELECT f1r.x, f1r.y
    FROM func1( SELECT e1, e2, e3 FROM ...) AS f1r;

    -- explicit input mapping to override positions?
    SELECT f1r.x, f1r.y
    FROM func1( SELECT e1, e2, e3 INTO b, a, c FROM ...) AS f1r;

    -- function composition
    SELECT func2(func1( SELECT e1, e2, e3 FROM ...));

Oh good, it’s not just me. As soon as I read his post I said to myself, “It sounds like he hasn’t heard of views.” But the first comment didn’t mention views and I was so confused that I started wondering if I misunderstood the post.

I disagree. This is a problem with filtering data. Creating a view every time you want 1 row from each group based on some calculation is beyond cumbersome IMHO.

But OP says every single time in their post which nicely fits a view as building block for more readable and simpler sql. Their issue seems not a one of filtering problem.

You can't build a view that allows you to do that query with an arbitrary function instead of MAX.

It's _every single time_ you need to do this kind of trivial querying you have to write this convoluted mess of SQL.

>with an arbitrary function instead of MAX

Many systems support user-defined aggregates[0][1]

[0] https://www.postgresql.org/docs/current/sql-createaggregate....

[1] https://docs.oracle.com/en/database/oracle/oracle-database/2...

There are situations where you can't use the views. For example I'm writing software which integrates with $vendor app. Not only do I have restricted select access, I have no influence on the schema at all. By definition, I have to send a complete query every time. And there are many similar scenarios.

Not SQL's fault. They probably wouldn't let you use macros either.

It would be nice to have a more composable solution though. Macros etc. don't have to be permanent. On the other hand, CTEs are pretty strictly tied to the structure for the current query.

Views.

https://news.ycombinator.com/item?id=32803396

They did mention they've only been using it 3 years.

It's been an ANSI standard for 36 years.

Reply on news.ycombinator.com