Hashagg planning bug (8.0.1)

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.

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 AS
SELECT 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>