public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Jevon Cowell <[email protected]>
To: [email protected]
Subject: Re: pg_stat_statements has duplicate entries for the same query & queryId
Date: Sun, 18 May 2025 12:29:05 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAMAx+cJM2Z32xvVbduGsF7rgOL6HuRcqq2Pob6w33wS1q8U0hw@mail.gmail.com>
References: <CAMAx+cJM2Z32xvVbduGsF7rgOL6HuRcqq2Pob6w33wS1q8U0hw@mail.gmail.com>

On 5/18/25 12:20, Jevon Cowell wrote:
> Hi Folks!
> Let me know if there's a better mailing list to ask this in.
> 
> I have a statistics collector that collects data from various postgres 
> statistics tables, pg_stat_statements being one of them. This is done on 
> an entire fleet of Postgres databases. From the collected data we record 
> the timestamp of each collection in the query itself as extract(epoch 
> from now()) as ts. What I'm seeing is that for the same query 
> /and/ query id, there are two rows with different statistics data /at 
> the same time/. For example one row can have 2 calls while another can 
> have 4. Anyone else run into this or have any idea why this can occur?

 From here:

https://www.postgresql.org/docs/current/pgstatstatements.html

queryid bigint

Hash code to identify identical normalized queries.

query text

Text of a representative statement



 From here:

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

"now() is a traditional PostgreSQL equivalent to transaction_timestamp()"

and

"transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is 
named to clearly reflect what it returns."

Therefore now() is pinned to the time the transaction started. 
Consequently it is conceivable that the queries actually ran at 
different times but got stamped with an identical timestamp via 
extract(epoch from now()) as ts.

> 
> Regards,
> Jevon Cowell

-- 
Adrian Klaver
[email protected]

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: pg_stat_statements has duplicate entries for the same query & queryId
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox