Hi!
What about adding new column in pg_stat_statements e.g. sql_id it's hash from normalized query. Аnd add function which
getthat hash (using raw_parser, raw_expression_tree_walker) for any query
`
postgres=# select get_queryid('select 1');
get_queryid
-------------
680388963
(1 row)
`
that function can be used on pg_stat_activity(query) for join pg_stat_statements if it need.
12.08.2019, 14:51, "legrand legrand" <legrand_legrand@hotmail.com>:
> Hi Jim,
>
> Its never too later, as nothing has been concluded about that survey ;o)
>
> For information, I thought It would be possible to get a more stable
> QueryId,
> by hashing relation name or fully qualified names.
>
> With the support of Julien Rouhaud, I tested with this kind of code:
>
> case RTE_RELATION:
> if (pgss_queryid_oid)
> {
> APP_JUMB(rte->relid);
> }
> else
> {
> rel = RelationIdGetRelation(rte->relid);
> APP_JUMB_STRING(RelationGetRelationName(rel));
> APP_JUMB_STRING(get_namespace_name(get_rel_namespace(rte->relid)));
> RelationClose(rel);
> {
>
> thinking that 3 hash options would be interesting in pgss:
> 1- actual OID
> 2- relation names only (for databases WITHOUT distinct schemas contaning
> same tables)
> 3- fully qualified names schema.relname (for databases WITH distinct schemas
> contaning same tables)
>
> but performances where quite bad (it was a few month ago, but I remenber
> about a 1-5% decrease).
> I also remenber that's this was not portable between distinct pg versions
> 11/12
> and also not sure it was stable between windows / linux ports ...
>
> So I stopped here ... Maybe its time to test deeper this alternative
> (to get fully qualified names hashes in One call) knowing that such
> transformations
> will have to be done for all objects types (not only relations) ?
>
> I'm ready to continue testing as it seems the less impacting solution to
> keep actual pgss ...
>
> If this doesn't work, then trying with a normalized query text (associated
> with search_path) would be the
> other alternative, but impacts on actual pgss would be higher ...
>
> Regards
> PAscal
>
> --
> Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
--------
Efimkin Evgeny
