Re: pg_stat_statements query jumbling question

On Sun, Nov 1, 2015 at 2:03 AM, Julien Rouhaud
<julien(dot)rouhaud(at)dalibo(dot)com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello,
>
> On 10/10/2015 08:46, Satoshi Nagayasu wrote:
>> On 2015/10/03 6:18, Peter Geoghegan wrote:
>>> On Wed, Sep 2, 2015 at 7:41 PM, Satoshi Nagayasu
>>> <snaga(at)uptime(dot)jp> wrote:
>>>> I know this still needs to be discussed, but I would like to
>>>> submit a patch for further discussion at the next CF, 2015-11.
>>>
>>> I think I already expressed this in my explanation of the
>>> current behavior, but to be clear: -1 from me to this proposal.
>>
>> I would like to introduce queryId to pgaudit and sql_firewall
>> extensions to determine query groups. queryId could be useful if
>> available in those modules.
>>
>> I think users may want to do that based on object names, because
>> they issue queries with the object names, not the internal object
>> ids.
>>
>> Changing queryId after re-creating the same table may make the
>> user gets confused, because the query string the user issue is not
>> changed.
>>
>> At least, I would like to give some options to be chosen by the
>> user. Is it possible and/or reasonable?
>>
>
> I'm also rather sceptical about this change.

Hm. Thinking a bit about this patch, it presents the advantage to be
able to track the same queries easily across systems even if those
tables have been created with a different OID. Say for example a
production server and a test server, the test server having a bunch of
other relations that messed up with OID consistency. So you could
compare more easily statistics across those servers with postgres_fdw
for example, and I guess that's what Nagayasu-san had in mind.

> In any case, the change you propose in this patch is not good, as you
> only consider the relation name and ignore the relation's schema. You
> need to also add the namespace name in the jumble state.

Yep, pg_class ensures the uniqueness of relations using (relname,
relnamespace), so we would need as well the namespace. Now, looking at
the code of pg_stat_statements the database OID is not used to
generate the queryid, right, hence don't we need to add as well the
database name of this relation in the set?

Also, isn't this patch actually broken if we rename relations and/or
its namespace? It seems that we would begin to generate inconsistent
query IDs that happens.
--
Michael