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

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