pg_stat_statement normalization fails due to temporary tables

Andres Freund <andres@2ndquadrant.com> writes:

pg_stat_statement's query normalization fails when temporary tables are
used in a query. That's because JumbleRangeTable() uses the relid in the
RTE to build the query fingerprint. I think in this case pgss from 9.1
actually would do better than 9.2+ as the hash lookup previously didn't
use the relid.

I don't really have a good idea about fixing this though. The best thing
that comes to mind is simply use eref->aliasname for the
disambiguation...

Hmm ... by "fails" I suppose you mean "doesn't treat two different
instances of the same temp table name as the same"? I'm not sure
that's a bug.

If we go over to using the aliasname then "select x from foo a" and
"select x from bar a" would be treated as the same query, which
clearly *is* a bug. More generally, I don't think that schema1.tab
and schema2.tab should be considered the same table for this purpose.
So it's hard to see how to do much better than using the OID.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2014-12-02 09:59:00 -0500, Tom Lane wrote:

Andres Freund <andres@2ndquadrant.com> writes:

pg_stat_statement's query normalization fails when temporary tables are
used in a query. That's because JumbleRangeTable() uses the relid in the
RTE to build the query fingerprint. I think in this case pgss from 9.1
actually would do better than 9.2+ as the hash lookup previously didn't
use the relid.

I don't really have a good idea about fixing this though. The best thing
that comes to mind is simply use eref->aliasname for the
disambiguation...

Hmm ... by "fails" I suppose you mean "doesn't treat two different
instances of the same temp table name as the same"? I'm not sure
that's a bug.

Well, it did work < 9.2...

If we go over to using the aliasname then "select x from foo a" and
"select x from bar a" would be treated as the same query, which
clearly *is* a bug.

Yep. The fully qualified name + alias would probably work - but IIRC
isn't available in the RTE without further syscache lookups...

More generally, I don't think that schema1.tab
and schema2.tab should be considered the same table for this purpose.
So it's hard to see how to do much better than using the OID.

Well, from a practical point of view it's highly annoying if half of
pg_stat_statement suddenly consists out of the same query, just issued
in a different session.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers