Re: pg_stat_statement normalization fails due to temporary tables

On 2014-12-02 09:59:00 -0500, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)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