Thread: Hashagg planning bug (8.0.1)

It would seem that the planner does not take into account whether the
datatypes involved have the capability to use hash aggregates or not.

sdb=# explain select query_start, current_query    from pg_locks join stat_activity on pid = procpid where granted =
trueand transaction in (select transaction from pg_locks where granted = false);
 
ERROR:  could not find hash function for hash operator 716373

ssdb=# set enable_hashagg = off;
SET

ssdb=# explain select query_start, current_query from pg_locks join stat_activity on pid = procpid where granted = true
andtransaction in (select transaction from pg_locks where granted = false);
  QUERY PLAN
 
------------------------------------------------------------------------------------------------------------Hash Join
(cost=60.79..159.12rows=1244 width=40)  Hash Cond: ("outer".procpid = "inner".pid)  ->  Function Scan on stat_activity
(cost=0.00..15.00rows=995 width=44)        Filter: (current_query <> '<IDLE>'::text)  ->  Hash  (cost=60.16..60.16
rows=250width=4)        ->  Hash Join  (cost=40.16..60.16 rows=250 width=4)              Hash Cond:
("outer"."transaction"= "inner"."transaction")              ->  Function Scan on pg_lock_status l  (cost=0.00..15.00
rows=500width=8)                    Filter: (granted = true)              ->  Hash  (cost=39.91..39.91 rows=100
width=4)                   ->  Unique  (cost=37.41..39.91 rows=100 width=4)                          ->  Sort
(cost=37.41..38.66rows=500 width=4)                                Sort Key: l."transaction"
   ->  Function Scan on pg_lock_status l  (cost=0.00..15.00 rows=500 width=4)
Filter:(granted = false)
 
(15 rows)

-- 

Rod Taylor <pg@rbt.ca> writes:
> It would seem that the planner does not take into account whether the
> datatypes involved have the capability to use hash aggregates or not.

> sdb=# explain select query_start, current_query    from pg_locks join stat_activity on pid = procpid where granted =
trueand transaction in (select transaction from pg_locks where granted = false);
 
> ERROR:  could not find hash function for hash operator 716373

What's stat_activity?  I thought you meant pg_stat_activity, but that
works fine here.
        regards, tom lane

On Tue, 2005-05-10 at 12:11 -0400, Tom Lane wrote:
> Rod Taylor <pg@rbt.ca> writes:
> > It would seem that the planner does not take into account whether the
> > datatypes involved have the capability to use hash aggregates or not.
> 
> > sdb=# explain select query_start, current_query    from pg_locks join stat_activity on pid = procpid where granted
=true and transaction in (select transaction from pg_locks where granted = false);
 
> > ERROR:  could not find hash function for hash operator 716373
> 
> What's stat_activity?  I thought you meant pg_stat_activity, but that
> works fine here.

Oh, stat_activity is a view which removes idle connections from
displaying and allows non-privileged users to see everything that's
going on within the DB.

CREATE OR REPLACE FUNCTION stat_activity() RETURNS setof pg_stat_activity SECURITY DEFINER AS 'select * from
pg_stat_activity;' language sql;
 

CREATE OR REPLACE VIEW stat_activity ASSELECT stat_activity.procpid, stat_activity.usename,
stat_activity.query_start::timestamp(0) without time zone AS
query_start, stat_activity.current_query  FROM stat_activity() WHERE stat_activity.current_query <> '<IDLE>'::text;

-- 

Rod Taylor <pg@rbt.ca> writes:
> Oh, stat_activity is a view which removes idle connections from
> displaying and allows non-privileged users to see everything that's
> going on within the DB.

Still works fine for me.  Do you even have an operator 716373?
If so what is it?
        regards, tom lane

On Tue, 2005-05-10 at 12:50 -0400, Tom Lane wrote:
> Rod Taylor <pg@rbt.ca> writes:
> > Oh, stat_activity is a view which removes idle connections from
> > displaying and allows non-privileged users to see everything that's
> > going on within the DB.
> 
> Still works fine for me.  Do you even have an operator 716373?
> If so what is it?

It's the = operator that Slony adds for xxid comparisons. I didn't even
think of changes Slony would have made.

ssdb=# select * from pg_operator where oid = 716373;oprname | oprnamespace | oprowner | oprkind | oprcanhash | oprleft
|oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | oprltcmpop | oprgtcmpop |    oprcode    |
oprrest|  oprjoin
 

---------+--------------+----------+---------+------------+---------+----------+-----------+--------+-----------+------------+------------+------------+------------+---------------+---------+-----------=
     |         2200 |      588 | b       | t          |  716353 |   716353 |        16 | 716373 |    716372 |
716371|     716371 |     716371 |     716369 | _ssrep.xxideq | eqsel   | eqjoinsel
 
(1 row)

-- 

Rod Taylor <pg@rbt.ca> writes:
> It's the = operator that Slony adds for xxid comparisons. I didn't even
> think of changes Slony would have made.

> ssdb=# select * from pg_operator where oid = 716373;
>  oprname | oprnamespace | oprowner | oprkind | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate |
oprlsortop| oprrsortop | oprltcmpop | oprgtcmpop |    oprcode    | oprrest |  oprjoin
 
>
---------+--------------+----------+---------+------------+---------+----------+-----------+--------+-----------+------------+------------+------------+------------+---------------+---------+-----------
>  =       |         2200 |      588 | b       | t          |  716353 |   716353 |        16 | 716373 |    716372 |
716371|     716371 |     716371 |     716369 | _ssrep.xxideq | eqsel   | eqjoinsel
 
> (1 row)

I think you need to have a word with the Slony boys.  They shouldn't be
marking the operator oprcanhash if they aren't providing a valid hash
opclass for the datatype.  Per the manual:

: To be marked HASHES, the join operator must appear in a hash index
: operator class. This is not enforced when you create the operator, since
: of course the referencing operator class couldn't exist yet. But
: attempts to use the operator in hash joins will fail at runtime if no
: such operator class exists. The system needs the operator class to find
: the data-type-specific hash function for the operator's input data
: type. Of course, you must also supply a suitable hash function before
: you can create the operator class.
        regards, tom lane

On T, 2005-05-10 at 13:17 -0400, Tom Lane wrote:
> Rod Taylor <pg@rbt.ca> writes:
> > It's the = operator that Slony adds for xxid comparisons. I didn't even
> > think of changes Slony would have made.
> 
> > ssdb=# select * from pg_operator where oid = 716373;
> >  oprname | oprnamespace | oprowner | oprkind | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate |
oprlsortop| oprrsortop | oprltcmpop | oprgtcmpop |    oprcode    | oprrest |  oprjoin
 
> >
---------+--------------+----------+---------+------------+---------+----------+-----------+--------+-----------+------------+------------+------------+------------+---------------+---------+-----------
> >  =       |         2200 |      588 | b       | t          |  716353 |   716353 |        16 | 716373 |    716372 |
 716371 |     716371 |     716371 |     716369 | _ssrep.xxideq | eqsel   | eqjoinsel
 
> > (1 row)
> 
> I think you need to have a word with the Slony boys.  They shouldn't be
> marking the operator oprcanhash if they aren't providing a valid hash
> opclass for the datatype.  Per the manual:

Why does slony use its own transaction id type (xxid) in the first
place, why can't we just use standard xid ?

Also, perhaps we could get the getcurrentxid() function accepted in
postgresql core, maybe as pg_get_current_xid(), perhaps together with
pg_oldest_running_xid() and pg_oldest_visible_xid() for determining if
there is any benefit from running vacuum.

I think that knowing current xid is something other applications besides
slony can benefit from.

-- 
Hannu Krosing <hannu@skype.net>