Jan wrote:

The only things not working for copy are rewrite rules. But I
think we should restrict rules to the view handling in the
future and move forward by implementing a pure and really
powerful procedural language.

Hm, it looks like you are not really a fan of the rewrite system,
eventhough you seem to have the most insight in these matters. I wonder why?

Why I like the rewrite system is:
1. select rewrite -- select trigger would be no good (optimizer)
2. The client can be really dumb, like MS Access or some other
standard ODBC tool
which does not know anything about funcs procs and the like
(even without using passthrough)
3. it is a lot more powerful than views
4. it allows the optimizer to get involved (this is where triggers
fail per definition)
5. once understood it is very easy to use
easier than trigger with c stored procedure at least

I guess if triggers could also trigger simple select statements, I could do
most of what I want using triggers except of course the select stuff.
But as I said I like the rules system very much, especially after your
recent
fixes Jan :-) So please stick to supporting all 3: triggers, views and
rules. Wow :-)

Andreas

Search Discussions

  • Jan Wieck at Feb 20, 1998 at 4:46 pm

    Andreas wrote:
    Jan wrote:

    The only things not working for copy are rewrite rules. But I
    think we should restrict rules to the view handling in the
    future and move forward by implementing a pure and really
    powerful procedural language.

    Hm, it looks like you are not really a fan of the rewrite system,
    eventhough you seem to have the most insight in these matters. I wonder why?
    Confusing - eh? Well I know much about the internals of the
    postgres rule system and due to this I know where the limits
    are. Especially in the case of qualifications it somtimes
    gets totally confused about what to compare against what. Try
    to add a delete rule on a view that is simply a select * from
    another table and then delete some tuples :-)
    Why I like the rewrite system is:
    1. select rewrite -- select trigger would be no good (optimizer)
    Exactly that's what is done if you create a view. Postgres
    creates a regular table (look at pg_class and into the
    database directory) and then sets up a relation level instead
    rewrite rule on select.
    2. The client can be really dumb, like MS Access or some other
    standard ODBC tool
    which does not know anything about funcs procs and the like
    (even without using passthrough)
    Yupp - the client must not know why and how and where the
    data is left and coming from. But that's true in any case - a
    trigger for each row on insert can do anything different and
    push the data wherever it wants.
    3. it is a lot more powerful than views
    As said - views are only one special rule case in Postgres.
    4. it allows the optimizer to get involved (this is where triggers
    fail per definition)
    5. once understood it is very easy to use
    easier than trigger with c stored procedure at least
    Optimizing again and again. If the rules aren't instead, the
    querytree get's additional queries for every rule appended.
    Have a table field that references an entry in another table
    and this entry should have a refcount. So on update you must
    decrease the refcount from the old ref and increase it on the
    new. You create two rules so the UPDATE will result in 1
    scan and 2 nestloops with scans inside - really optimized if
    the referenced value doesn't change. And don't think that a
    rule qual of NEW != CURRENT might help - that will result in
    2 mergejoins where the scanned tuples are compared.

    BTW, this sample doesn't work currently because the rules
    queries are appended at the end of the querytree, thus the
    decrement scan having the same qual will not find the old
    tuple at all because it's already outdated
    (command_counter_increment between processing the queries).
    Referencing CURRENT in a rule is not what most people think
    it is.

    The old 4.2 postgres had a second, instance level rule system
    (prs2 stubs) that fired the rules actions when actually the
    old tuple and the new projected tuple where handy. There you
    could have made also things like 'UPDATE NEW SET a = 4' that
    really modified the in memory tuple in the executors
    expression context. Who the hell removed all that? It was so
    nice :-(

    A really simple to write trigger can compare old != new and
    only if send down the other two queries. This time they wont
    be nestloops, they are simple scans. And the trigger can
    arrange that the queries it uses are only parsed on it's
    first of all calls and store the generated execution plans
    permanently for quick execution (look at SPI_prepare).

    For the stored C procedures you're totally right. I don't
    like the C functions because it requires postgres superuser
    rights to develop them and thus I created PL/Tcl where joe
    user can hack around without having complete access to the
    whole database (look at src/pl/tcl). And someday after 6.3
    release I'll really start on a plain PL/pgSQL implementation
    that would give a normal user the opportunity to create
    functions and triggers on a high level. There is light at the
    end of the tunnel - hope that it isn't the coming train :-)
    I guess if triggers could also trigger simple select statements, I could do
    most of what I want using triggers except of course the select stuff.
    But as I said I like the rules system very much, especially after your
    recent
    fixes Jan :-) So please stick to supporting all 3: triggers, views and
    rules. Wow :-)
    Well - a trigger cannot build a view. The relation underlying
    the view doesn't contain any tuples and a select trigger will
    never be fired. As long as there is no possibility to return
    tuple sets from non-SQL functions. But a trigger can do
    things like the pg_hide_passwd stuff much more powerful. You
    could define the trigger so that it checks if the user is a
    superuser and overwrite the passwd value only in the case
    where he/she isn't. If fired at the right place it would too
    work for things like the copy command etc.

    We must stay with all 3 features. And I will take a look at
    the INSERT ... SELECT view problem really soon as it is a
    rule system problem that breaks views. But this is only the
    SELECT rewriting part of the rule system which I really like
    (optimizable). The other areas (insert, update, delete) of
    the rule system are dangerous and I really think a powerful
    PL/pgSQL language could make them obsolete.


    Jan

    --

    #======================================================================#
    # It's easier to get forgiveness for being wrong than for being right. #
    # Let's break this rule - forgive me. #
    #======================================== jwieck@debis.com (Jan Wieck) #

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedFeb 20, '98 at 2:47p
activeFeb 20, '98 at 4:46p
posts2
users2
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase