Subject: Re:

From: Lana ABADIE <[email-masked]>
Date: Mon, 20 Jan 2025 19:10:15 +0100

Hi all

I bumped into a weird case that i don't really understand...maybe someone in this list could have a clue

We have 2 Postgres databases configured as master/slave replica (Postgresq 12, RHEL8)

We have applications which write data into the master and applications which reads data from the replica.

A group of applications reads data using libpq: it declares a select statement as cursor and then there is fetch which can retrieve at most 25k rows.

The select statement contains a between clause with T1 and T2. T1 is injected via input parameter but T2=floor(extract (epoch from coalesce(pg_last_xact_replay_timestamp(),now())))-120. It is passed directly like that in the query.

In other words we have something like select * from ZZ where ... and timestamp between $T1 and floor(extract (epoch from coalesce(pg_last_xact_replay_timestamp(),now())))-120;

when this query gets executed, from time to time it returns a truncated number of rows.. less than if i was doing between T1 and T1...

T2 being an integer so either T2<T1 in that case i would get a number of rows of zero or T2>=T1 and I would expect at least #rows greater or equal to the number of rows between T1 and T1,

Note that we are talking about  a total number of rows less than 2000.

Then when i fixed T2, in other words i do a query using between $T1 and $T2 (where T2=floor(extract (epoch from coalesce(pg_last_xact_replay_timestamp(),now())))-120) then there is no issues, number of rows are retrieved correctly.

I also confirmed via metrics collection that the data is there when the query is being performed.

I would appreciate any explanations on this behavior, and hoping i'm clear.

Thanks

Doris

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 21 Jan 2025 09:21:12 -0800

On 1/20/25 10:10, Lana ABADIE wrote:
> A group of applications reads data using libpq: it declares a select 
> statement as cursor and then there is fetch which can retrieve at most 
> 25k rows.

Add the complete SELECT and CURSOR code.

> The select statement contains a between clause with T1 and T2. T1 is 
> injected via input parameter but T2=floor(extract (epoch from 
> coalesce(pg_last_xact_replay_timestamp(),now())))-120. It is passed 
> directly like that in the query.

What does T1 represent and how is it derived?

> In other words we have something like select * from ZZ where ... and 
> timestamp between $T1 and floor(extract (epoch from 
> coalesce(pg_last_xact_replay_timestamp(),now())))-120;
> when this query gets executed, from time to time it returns a truncated 
> number of rows.. less than if i was doing between T1 and T1...

I don't understand above, add more complete definition.  Example data 
would be nice.

> T2 being an integer so either T2<T1 in that case i would get a number of 
> rows of zero or T2>=T1 and I would expect at least #rows greater or 
> equal to the number of rows between T1 and T1,
> Then when i fixed T2, in other words i do a query using between $T1 and 
> $T2 (where T2=floor(extract (epoch from 
> coalesce(pg_last_xact_replay_timestamp(),now())))-120) then there is no 
> issues, number of rows are retrieved correctly.

-- 
Adrian Klaver
[email-masked]