dynamic variable interpolation on queries in plpgsql functions

#11

I wrote:

Quote:

I guess your approach would work for little more than views that are
inner joins of tables with only simple conditions (no subqueries,
EXISTS or the like) and without grouping or aggregate functions.

And I forgot to mention:
Usually materialized views are useful for views that are very
complex and expensive. For a query that only consists of some
inner joins, why would you need a materialized view?

Yours,
Laurenz Albe

#12

Laurenz Albe ha scritto:

Quote:

Coniglio Sgabbiato wrote:
I don't understand why you want to compare the values in NEW
with other rows in the original table, perhaps I don't understand
your design well enough.
[...]
right, I was trying to write something generalized to tables without a primary key. Actually tables without primary key are
usually found in bad designed databases, so it would be reasonable to admit that tables have always primary keys. I'll explain
better what I am trying to do in my very little spare time. I can automatically create a trigger for eache table involved in the
view I want to materialize:

create or replace function create_matview

[creates a materialized view table and a trigger on every table
that the view that should be materialized depends upon]

the next step it is to retrieve
the records of the view affected by the change, and only those, so I should put in join every record of the trigger with the view,
the trick here (very very dirty) it is to retrieve the definition of the view:

and then replace the name of the table the trigger has been fired from with a subquery involving only the record changed, it
should work only under the condition that views to be materialized MUST use aliases and only aliases for ALL the tables in
definition, below an _idea_ of trigger code:

[...]
what do you think about such a tricky thing?, I know it is a big mess with big limitations (must use aliases for all the tables in
views, it does not work for views upon views, etc etc) but I would like to get it working

Oh, I see.

For your approach to work, the view must meet many more conditions.
It would not work for views defined like this:

.... AS SELECT department, avg(salary) FROM empsal GROUP BY department

because if you run that with only one row of "empsal", you will not
get anything meaningful, or even

.... AS SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON ...

because an insert in tab2 will not only lead to new rows in
the result, but also possibly remove rows from the result
(which have NULL values for tab2 entries).

So it will only work for a small amount of very simple views.
And without deep analysis of the defining query it is hard to say
whether it would work for a certain query or not.

I did not think about these cases, given this, my approach drives to a
complete meaningless implementation

Quote:

Oracle supports something like what you want to implement, called
"on commit fast refresh". I looked at the requirements that a view
must fulfill to be eligible for this, and it is a rather long
and daunting list (if you happen to have Metalink access, that
would be note 222843.1).

I don't think you'll be able to check these conditions automatically,
you'd have to rely on it that the view is "well defined".

I guess your approach would work for little more than views that are
inner joins of tables with only simple conditions (no subqueries,
EXISTS or the like) and without grouping or aggregate functions.

If you *still* think that this is worth your spare time, I guess
that your approach would work.

these limitations are too heavy, I think that such a thing would be so
worthless that it is not the case to work on it

Quote:

You'll have to add some more stuff to support UPDATE:
An update will not only add rows to the materialized view, but
also delete some. Maybe it would be easiest to implement it as
delete followed by insert: create a BEFORE UPDATE OR DELETE
trigger that removes all the OLD rows, and an AFTER INSERT
OR UPDATE trigger to add the NEW rows.

actually the pseudocode I wrote it is intended to do so, you see two
conditional, one "if TG_OP='DELETE' or TG_OP='UPDATE'" followed by one
"if TG_OP='INSERT' or TG_OP='UPDATE'"

Quote:

And yes, if the original tables have no primary keys or the
query "with one row" would otherwise take very long, your
DML statements on the original tables will become quite expensive...
I can't think of a way around that.
But with all the restrictions from above, it would hardly
be a problem to add the additional restriction that all
tables must have primary keys and indexes on rows on which
foreign keys are defined.

so, I'll wait that PG dev group will implement materialized views

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

dynamic variable interpolation on queries in plpgsql functions

comp.databases.postgresql comp.databases.postgresql

Re: automated materialized views - 06-19-2009 , 07:03 AM

Re: automated materialized views - 06-19-2009 , 07:59 AM