Обсуждение: ExecEvalExpr: unknown expression type 108

Поиск
Список
Период
Сортировка

ExecEvalExpr: unknown expression type 108

От
"SZŰCS Gábor"
Дата:
Dear Gurus,

What does the following error message mean? I couldn't find anything about
it in the docs.

  ExecEvalExpr: unknown expression type 108

Thanks in advance,
G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------


Re: ExecEvalExpr: unknown expression type 108

От
"Hegyvari Krisztian"
Дата:
Dear Gabor,

I am far from being a guru, so I typed "ExecEvalExpr: unknown expression type 108"
into google and it came up with some seemingly useful hits.

Cheers,

Hegyvari Krisztian


>>> "SZŰCS Gábor" <surrano@mailbox.hu> 12/10/02 01:05pm >>>
Dear Gurus,

What does the following error message mean? I couldn't find anything about
it in the docs.

  ExecEvalExpr: unknown expression type 108

Thanks in advance,
G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------


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


Re: ExecEvalExpr: unknown expression type 108

От
"SZŰCS Gábor"
Дата:
Thanks Krisztian,

I checked google and most of the pgsql archive messages it pointed to, but I
found that this message is commonly related to subselects in table
constraints.

If I didn't misunderstand it, Tom once titled this as "probably solved in
6.5" or such, back in 1999.

However, my problem is different, and even if it's not vital now, I'd like
to get some suggestions where to search if the problem persists.

I use 7.2.1 and met the problem BUT ONCE, after changing a field definition
in a view from a function call to the explicit content of the function, like
this:

  -- t and x are business files,
  -- with items referring to t in t_item
  -- and their N:M relations with x and x_item (not important now) in t_x.
  -- I want to select the latest x.id from the relations table:
  --
  -- myfunc(t.id, t_item.no) AS x, -- this was the original
  (SELECT x FROM t_x
   WHERE t_x.t = t.id AND t_x.t_item = t_item.no
   ORDER BY tstamp DESC LIMIT 1
  ) AS x,

The error message occured after I recreated the view (and its rule), but
when I tried to reproduce it to send a usable mail, it evilly refused to
fail and works correctly ever since ;) I fear for the worst, that the
problem comes to life again when it goes from test to live use.

Well, even if it comes to life, I can get back to calling that function; the
problem is, that if I wish to JOIN with table x using this field of the
view, the planner makes a seq scan on table x, unlike when I write the
function body in the view definition.

Since one produces 17sec and the other 0.11sec as total time for the same
query, I think the planner doesn't deliberately choose seq scan; it can't
choose index scan. So, I helped it to see what I want in depth, and that
caused the error -- I repeat, it caused the error but once.

G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------
----- Original Message -----
From: "Hegyvari Krisztian" <Hegyvari.Krisztian@ardents.hu>
Sent: Tuesday, December 10, 2002 5:12 PM


Dear Gabor,

I am far from being a guru, so I typed "ExecEvalExpr: unknown expression
type 108"
into google and it came up with some seemingly useful hits.



Planner weakness (was: Re: ExecEvalExpr: unknown expression type 108)

От
"SZŰCS Gábor"
Дата:
Dear Gurus,

I think I have something to aid you (a probable bug). Please read the
abstract at least and decide whether to continue with the "In Short" part.

If it's not clear enough (I stripped as much as possible to avoid being
lost) I'll take the pain and try to reproduce the phenomenon in a smaller,
working example and send a dump to those willing to help.

ABSTRACT:
1. I managed to reproduce the error I mentioned in a former mail, and it's
based on different forms of the same query (subselect or join).
??? Is it normal for a subselect not to work in a query where a join (that
produces the same result) works?

2. I managed to create a fast and exactly working query but I think I did
some of the job the planner would have.
??? Is it common when developing in PostgreSQL, or is it considered a
"to-be-improved" thing or even a bug?

IN SHORT:
#1. the planner seems to be unable to optimize functions in view
definitions.
#2. putting the subquery in the view def and then calling a subselect on it
causes an "ExecEvalExpr: unknown expression type 108"
#3. Commenting the subselect line works but I need that field :)
#4. joining instead of subselect works good, but
#5. if the join is done with a subquery, no matter how trivial, maddens the
planner, as in "549.2msec vs 9.5msec"

DETAILS:
Here are my original queries (stripped of things I thought irrelevant) and
their explain analyzes. First is #2, throwing error. Second is #4, third is
#5. See the relevant part of the view's structure in my previous mail,
included at the end of this mail.

G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------
-- #2. This throws an error:
tir=> (SELECT
tir(>     (SELECT az_jel from x where az=t.x) as x_az_jel
tir(> FROM t_item t
tir(> WHERE -- snip...
tir-> ORDER BY t.az_jel, t.teljesites;
ERROR: ExecEvalExpr: unknown expression type 108
---------------------------- cut here ------------------------------
---------------------------- cut here ------------------------------
---------------------------- cut here ------------------------------
-- #4. The faster solution. This requires relation prefixes to all fields in
-- SELECT, WHERE and ORDER BY since most are common in
-- view t and table x:

tir=> (SELECT
tir(>     x.az_jel
tir(> FROM t_item t LEFT OUTER JOIN
tir(>      x ON (x.az=t.x)
tir(> WHERE -- snip...
tir-> ORDER BY t.az_jel, t.teljesites;
 az_jel
--------

(1 row)
tir=> explain analyze (SELECT
tir(>     x.az_jel
tir(> FROM t_item t LEFT OUTER JOIN
tir(>      x ON (x.az=t.x)
tir(> WHERE -- snip...
tir-> ORDER BY t.az_jel, t.teljesites;
NOTICE:  QUERY PLAN:

Sort  (cost=82.26..82.26 rows=1 width=66) (actual time=8.43..8.43 rows=1
loops=1)
  ->  Nested Loop  (cost=0.00..82.25 rows=1 width=66) (actual
time=3.75..8.02 rows=1 loops=1)
        ->  Nested Loop  (cost=0.00..66.11 rows=1 width=50) (actual
time=3.23..7.50 rows=1 loops=1)
              ->  Nested Loop  (cost=0.00..50.07 rows=1 width=33) (actual
time=2.02..5.14 rows=28 loops=1)
                    ->  Index Scan using szlltlvl_tljsts on szallitolevel b
(cost=0.00..43.68 rows=1 width=19) (actual time=0.16..1.49 rows=17 loops=1)
                    ->  Index Scan using szallitolevel_modositasa_pkey on
szallitolevel_modositasa m  (cost=0.00..6.38 rows=1 width=14) (actual
time=0.16..0.18 rows=2 loops=17)
              ->  Index Scan using szallitolevel_szallitolevel_key on
szallitolevel_tetele t  (cost=0.00..14.50 rows=1 width=17) (actual
time=0.07..0.07 rows=0 loops=28)
        ->  Index Scan using x_az_key on x  (cost=0.00..10.86 rows=1
width=16) (actual time=0.01..0.01 rows=0 loops=1)
              SubPlan
                ->  Limit  (cost=5.27..5.27 rows=1 width=12) (actual
time=0.46..0.46 rows=0 loops=1)
                      ->  Sort  (cost=5.27..5.27 rows=1 width=12) (actual
time=0.45..0.45 rows=0 loops=1)
                            ->  Index Scan using szlltlvl_szmlzs_szlltlvl on
t_x i  (cost=0.00..5.26 rows=1 width=12) (actual time=0.06..0.06 rows=0
loops=1)
                ->  Limit  (cost=5.27..5.27 rows=1 width=12)
                      ->  Sort  (cost=5.27..5.27 rows=1 width=12)
                            ->  Index Scan using szlltlvl_szmlzs_szlltlvl on
t_x i  (cost=0.00..5.26 rows=1 width=12)
Total runtime: 9.47 msec

---------------------------- cut here ------------------------------
---------------------------- cut here ------------------------------
---------------------------- cut here ------------------------------
-- #5. The slower solution. This doesn't require relation prefixes
-- since the subquery in the join does the job. Not a big deal.
-- However, it maddens the planner:

tir=> (SELECT
tir(>     sz_az_jel
tir(> FROM t_item t LEFT OUTER JOIN
tir(>     (select az as sz_az, az_jel as sz_az_jel from x) as x
           ON (x.sz_az=t.x)
tir(> WHERE -- snip...
tir-> ORDER BY az_jel, teljesites;
 sz_az_jel
-----------

(1 row)

tir=> explain analyze (SELECT
tir(>     sz_az_jel
tir(> FROM t_item t LEFT OUTER JOIN
tir(>     (select az as sz_az, az_jel as sz_az_jel from x) as x
          ON (x.sz_az=t.x)
tir(> WHERE -- snip...
tir-> ORDER BY az_jel, teljesites;
NOTICE:  QUERY PLAN:

Sort  (cost=6857.66..6857.66 rows=6 width=122) (actual time=548.34..548.34
rows=1 loops=1)
  ->  Nested Loop  (cost=0.00..6857.58 rows=6 width=122) (actual
time=543.59..547.96 rows=1 loops=1)
        ->  Nested Loop  (cost=0.00..66.11 rows=1 width=50) (actual
time=3.19..7.55 rows=1 loops=1)
              ->  Nested Loop  (cost=0.00..50.07 rows=1 width=33) (actual
time=2.00..5.21 rows=28 loops=1)
                    ->  Index Scan using szlltlvl_tljsts on szallitolevel b
(cost=0.00..43.68 rows=1 width=19) (actual time=0.15..1.52 rows=17 loops=1)
                    ->  Index Scan using szallitolevel_modositasa_pkey on
szallitolevel_modositasa m  (cost=0.00..6.38 rows=1 width=14) (actual
time=0.16..0.18 rows=2 loops=17)
              ->  Index Scan using szallitolevel_szallitolevel_key on
szallitolevel_tetele t  (cost=0.00..14.50 rows=1 width=17) (actual
time=0.07..0.07 rows=0 loops=28)
        ->  Subquery Scan x  (cost=0.00..54.75 rows=1275 width=16) (actual
time=0.03..35.83 rows=1275 loops=1)
              ->  Seq Scan on x  (cost=0.00..54.75 rows=1275 width=16)
(actual time=0.02..25.22 rows=1275 loops=1)
        SubPlan
          ->  Limit  (cost=5.27..5.27 rows=1 width=12) (actual
time=0.38..0.38 rows=0 loops=1275)
                ->  Sort  (cost=5.27..5.27 rows=1 width=12) (actual
time=0.37..0.37 rows=0 loops=1275)
                      ->  Index Scan using szlltlvl_szmlzs_szlltlvl on t_x i
(cost=0.00..5.26 rows=1 width=12) (actual time=0.05..0.05 rows=0 loops=1275)
Total runtime: 549.20 msec

EXPLAIN
---------------------------- cut here ------------------------------


----- Original Message -----
From: "SZŰCS Gábor" <surrano@mailbox.hu>
Sent: Wednesday, December 11, 2002 4:25 PM
Subject: Re: [GENERAL] ExecEvalExpr: unknown expression type 108


> Thanks Krisztian,
>
> I checked google and most of the pgsql archive messages it pointed to, but
I
> found that this message is commonly related to subselects in table
> constraints.
>
> If I didn't misunderstand it, Tom once titled this as "probably solved in
> 6.5" or such, back in 1999.
>
> However, my problem is different, and even if it's not vital now, I'd like
> to get some suggestions where to search if the problem persists.
>
> I use 7.2.1 and met the problem BUT ONCE, after changing a field
definition
> in a view from a function call to the explicit content of the function,
like
> this:
>
>   -- t and x are business files,
>   -- with items referring to t in t_item
>   -- and their N:M relations with x and x_item (not important now) in t_x.
>   -- I want to select the latest x.id from the relations table:
>   --
>   -- myfunc(t.id, t_item.no) AS x, -- this was the original
>   (SELECT x FROM t_x
>    WHERE t_x.t = t.id AND t_x.t_item = t_item.no
>    ORDER BY tstamp DESC LIMIT 1
>   ) AS x,
>
> The error message occured after I recreated the view (and its rule), but
> when I tried to reproduce it to send a usable mail, it evilly refused to
> fail and works correctly ever since ;) I fear for the worst, that the
> problem comes to life again when it goes from test to live use.
>
> Well, even if it comes to life, I can get back to calling that function;
the
> problem is, that if I wish to JOIN with table x using this field of the
> view, the planner makes a seq scan on table x, unlike when I write the
> function body in the view definition.
>
> Since one produces 17sec and the other 0.11sec as total time for the same
> query, I think the planner doesn't deliberately choose seq scan; it can't
> choose index scan. So, I helped it to see what I want in depth, and that
> caused the error -- I repeat, it caused the error but once.


Re: Planner weakness (was: Re: ExecEvalExpr: unknown expression type 108)

От
Tom Lane
Дата:
"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.hu> writes:
> -- #2. This throws an error:
> tir=> (SELECT
> tir(>     (SELECT az_jel from x where az=t.x) as x_az_jel
> tir(> FROM t_item t
> tir(> WHERE -- snip...
> tir-> ORDER BY t.az_jel, t.teljesites;
> ERROR: ExecEvalExpr: unknown expression type 108

You have not mentioned what PG version you are using, but if it's 7.3,
this is a known bug already fixed for 7.3.1.  The patch was posted here
a week ago.

            regards, tom lane

Re: Planner weakness (was: Re: ExecEvalExpr: unknown expression type 108)

От
"SZŰCS Gábor"
Дата:
Dear Tom,

Thanks for the answer! Sorry, I forgot to repeat the version no. It's 7.2.1,
but we plan to upgrade to 7.3... or 7.3.1 if it's on the horizon :)

How about the planner part of my mail? I'm eager to compare the fixed
version with my two query fixes, one producing 9.47msec, the other producing
549msec :)

G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
Sent: Thursday, December 12, 2002 3:19 PM


> You have not mentioned what PG version you are using, but if it's 7.3,
> this is a known bug already fixed for 7.3.1.  The patch was posted here
> a week ago.



Re: Planner weakness (was: Re: ExecEvalExpr: unknown expression type

От
Joseph Shraibman
Дата:
Tom Lane wrote:

>
> You have not mentioned what PG version you are using, but if it's 7.3,
> this is a known bug already fixed for 7.3.1.  The patch was posted here
> a week ago.
What exactly is the bug?  Does it affect all subselects?