thoughts on postgres_exporter/common/pg16/setup.sql

[hunleyd]

Describe the enhancement you are proposing:
(from #367 (comment))

sidebar: could we just hash a hash down to a bigint scale and just return decimal value?

[keithf4]

@pgguru Happen to have any good examples of doing this? Or any chance whatever hash algorithm that Postgres uses to generate the queryid in pg_stat_statements is something that's available as a user facing function?

[keithf4]

So I did find these internal hash functions that can generate bigint hash values

https://stackoverflow.com/questions/9809381/hashing-a-string-to-a-numeric-value-in-postgresql

However, it links to a mailing list discussion where core specifically recommends NOT using these functions since their internals may change and "break" the hashing output, citing an actual example where it happened. The recommended usage for generating hashes is the public hash functions (md5, sha, etc).

If you can provide some other means to do this, I'm open to a suggestion. Otherwise, the current method seems to be working fine.

[pgguru]

Heh, considering I have no memory of this issue I can't say I have any strong feelings on this; we could certainly take the md5 and then return the low 8 bytes as a BIGINT, which in all likelihood would be unique. Anyway, if you're happy with how it is currently, I'm happy.

[keithf4]

Heh, considering I have no memory of this issue I can't say I have any strong feelings on this; we could certainly take the md5 and then return the low 8 bytes as a BIGINT, which in all likelihood would be unique. Anyway, if you're happy with how it is currently, I'm happy.

I think there was another complaint from some security scanners about using md5. But it was just a general scan that saw it and said "md5 bad" with no context to see how it was being used. I'm not using it for passwords and returning only the int portion or even using the other int hashes would only increase the collision chances even more. So I'm just going to leave things as they are for now.