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_legr...@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