Thread: cursor and update + view
Hi all, I would like to greet averybody on the list; this is my first mail. Anyway... I need to use the UPDATE ... WHERE CURRENT OF ... clausule, but it doesn't work: oeptest=> update shorttest set b='blahblah' where current of c1; ERROR: CURRENT used in non-rule query What does it mean? IT would be urgent... My other problem is with the views: it seems not work if there is an aggregate command in the SELECT. It's only my experience, or others noticed it also? (The details are on the pgsql-novice (subject: view and aggregate command problem)) Thanks, and sorry, if the questions were answered an hour ago; I have subscribed to the list 5 minutes ago... :-) Circum __ @ / \ _ _ Engard Ferenc l | ( \ / | | (\/) mailto:s-fery@kkt.sote.hu \__/ | | \_ \_/ I I http://pons.sote.hu/~s-fery
> My other problem is with the views: it seems not work if there is an
> aggregate command in the SELECT. It's only my experience, or others
> noticed it also? (The details are on the pgsql-novice (subject: view
> and aggregate command problem))
Aggregates in view's are still buggy. It is possible to
change the grouping when doing a join from such a view and
another relation in a way, that crashes the backend.
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) #
Jan Wieck wrote: > > > My other problem is with the views: it seems not work if there is an > > aggregate command in the SELECT. It's only my experience, or others > > noticed it also? (The details are on the pgsql-novice (subject: view > > and aggregate command problem)) > > Aggregates in view's are still buggy. It is possible to > change the grouping when doing a join from such a view and > another relation in a way, that crashes the backend. We'll have to implement subqueries in FROM someday - this would allow handle aggregates in views... Vadim
Vadim wrote:
>
> Jan Wieck wrote:
> >
> > > My other problem is with the views: it seems not work if there is an
> > > aggregate command in the SELECT. It's only my experience, or others
> > > noticed it also? (The details are on the pgsql-novice (subject: view
> > > and aggregate command problem))
> >
> > Aggregates in view's are still buggy. It is possible to
> > change the grouping when doing a join from such a view and
> > another relation in a way, that crashes the backend.
>
> We'll have to implement subqueries in FROM someday - this would
> allow handle aggregates in views...
You're right. In the current implementation, any rangetable
entry (RTE) that is finally (after rewrite) used in any Var
node of the querytree results in a scan of that relation.
Having an RTE that contains its own subquery would
materialize the view internally and help us out.
This kind of subquery RTE will also be the base for functions
that return result sets (SETOF complex type). These are
broken too.
It will be a little tricky to pull out the qualifications
that restrict the subquery RTE, so the view must not entirely
get materialized. At least any constant expression compared
against one of the views attributes must go into.
There is another thing that I would like to have. The current
rule system tries to turn a qualification that uses an
aggregate column of a view into a subquery. This is because
the planner doesn't support plain aggregate expressions in
the qualification. If it would be possible to have another
type of Var node that points to a targetlist entry, we could
put the aggregates from the qualification into junk TLE's.
TGL - are you listening - I think it's your code I'm ulyfying
here :-).
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) #
Jan Wieck wrote: > > There is another thing that I would like to have. The current > rule system tries to turn a qualification that uses an > aggregate column of a view into a subquery. This is because > the planner doesn't support plain aggregate expressions in > the qualification. If it would be possible to have another > type of Var node that points to a targetlist entry, we could > put the aggregates from the qualification into junk TLE's. This thing would also handled by subqueries in FROM! Having support in planner/executor for queries like this: select * from A, (select c, max(d) as m from B group by c) SQ where SQ.c = A.x and SQ.m = A.y rule system will be able to put _any_ VIEW' query into FROM clause... Vadim
Vadim wrote:
> This thing would also handled by subqueries in FROM!
> Having support in planner/executor for queries like this:
>
> select * from A, (select c, max(d) as m from B group by c) SQ
> where SQ.c = A.x and SQ.m = A.y
>
> rule system will be able to put _any_ VIEW' query into
> FROM clause...
Possible - but IMHO the wrong thing to do. As it is now for a
view that has no aggregate, the rule system rewrites the
query to something that is the same as if the user resolved
the view definition by hand and used all the real tables
instead. Have the following:
CREATE TABLE t1 (a int4, b int4);
CREATE TABLE t2 (a int4, c int4);
CREATE TABLE t3 (a int4);
CREATE VIEW v1 AS SELECT t1.a, t1.b, t2.c
FROM t1, t2 WHERE t1.a = t2.a;
Now do a
SELECT t3.a, v1.b, v1.c FROM t3, v1
WHERE t3.a = v1.a;
The current rewrite system builds a querytree that is exactly
that what would have been produced by the parser if you had
typed
SELECT t3.a, t1.b, t2.c FROM t3, t1, t2
WHERE t3.a = t1.a AND t1.a = t2.a;
Now the planner/optimizer has _ALL_ the tables that need to
be scanned and _ALL_ the qualifications in _ONE_ querytree.
It is the job of the optimizer to decide which is the best
join path for this access. To make a good decision, it needs
all this information plus the VACUUM statistics.
If we put any view into a subquery RTE, we force the planner
to materialize the view and do a nestloop over t3 and
materialized v1 where possibly using t1 or t2 as the
outermost scanrelation would be better.
Stonebraker & Co where absolutely right when they spoke about
productional rule systems. And what PostgreSQL does now is
how I understood them.
"Production rule systems are conceptually simple, but
there are many subtle points involved in actually using
them."
-- Stonebraker
I think the different grouping requirements for subsets of
data when using aggregate columns in views is just one of the
the problems he addressed with the above statement.
We should build a subquery RTE only if Query->hasAggs is
true.
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) #
Jan Wieck wrote:
>
> > rule system will be able to put _any_ VIEW' query into
> > FROM clause...
>
> Possible - but IMHO the wrong thing to do. As it is now for a
"Will be able" doesn't mean "will DO" -:))
I just said that subqueries in FROM will resolve all
problems with aggregates in VIEWs.
> If we put any view into a subquery RTE, we force the planner
> to materialize the view and do a nestloop over t3 and
^^^^^^^^^^^
Do you mean creating some tmp table etc?
No - it's not required.
> materialized v1 where possibly using t1 or t2 as the
> outermost scanrelation would be better.
SELECT t3.a, v1.b, v1.c
FROM t3,
(SELECT t1.a, t1.b, t2.c FROM t1, t2 WHERE t1.a = t2.a) v1
WHERE t3.a = v1.a;
can be planned as
Nestloop
SubPlan
...what is costless for subquery...
Seq/Index scan on t3
- no materialization...
On the other hand, as we talk about query optimization - why
rule system should do optimizer' work? Why not just put
_any_ VIEW' query into FROM and let optimizer decide
could query be rewritten as join or not? Ppl do strange
things sometimes -:) Sometimes they use subqueries in
WHERE while joins could be used and our optimizer don't
try to catch this. I know that Sybase does.
And, imho, we should implement this ... sometime -:))
Vadim
Vadim wrote:
>
> Jan Wieck wrote:
> >
> > If we put any view into a subquery RTE, we force the planner
> > to materialize the view and do a nestloop over t3 and
> ^^^^^^^^^^^
> Do you mean creating some tmp table etc?
> No - it's not required.
Sometimes a sortset is required (grouping, nesting etc.).
With materialize I meant the same thing the executor does for
a scan, merge or iter node. They return in memory tuples from
a relation or a temp file. In our new case it's mostly the
same as a scan node that does the view selection inside. And
it returs the same tuples as a SELECT * from the view would.
That's internal, on the fly materialization of the view.
> On the other hand, as we talk about query optimization - why
> rule system should do optimizer' work? Why not just put
> _any_ VIEW' query into FROM and let optimizer decide
> could query be rewritten as join or not? Ppl do strange
> things sometimes -:) Sometimes they use subqueries in
> WHERE while joins could be used and our optimizer don't
> try to catch this. I know that Sybase does.
> And, imho, we should implement this ... sometime -:))
Depends on where the optimization is done. If we do it on the
parsetree (Query struct), it's the job of the rule system.
The optimizer does not have to modify the parsetree. If it is
done on the way from the parsetree to the plan, it is the job
of the optimizer.
If it is possible to do it on the parsetree, I would do it
there.
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) #