Re: [survey] New "Stable" QueryId based on normalized query text

Hi!
What about adding new column in pg_stat_statements e.g. sql_id it's hash from normalized query. Аnd add function which get that 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(at)hotmail(dot)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