public inbox for [email protected]help / color / mirror / Atom feed
(no subject) 2+ messages / 2 participants [nested] [flat]
* (no subject) @ 2025-01-20 18:10 Lana ABADIE <[email protected]> 2025-01-21 17:21 ` Re: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 2+ messages in thread From: Lana ABADIE @ 2025-01-20 18:10 UTC (permalink / raw) To: [email protected] <html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hi all </div> <div>I bumped into a weird case that i don't really understand...maybe someone in this list could have a clue</div> <div>We have 2 Postgres databases configured as master/slave replica (Postgresq 12, RHEL8)</div> <div>We have applications which write data into the master and applications which reads data from the replica.</div> <div>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.</div> <div>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.</div> <div>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;</div> <div>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...</div> <div>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,</div> <div>Note that we are talking about a total number of rows less than 2000.</div> <div>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.</div> <div>I also confirmed via metrics collection that the data is there when the query is being performed.</div> <div>I would appreciate any explanations on this behavior, and hoping i'm clear.</div> <div>Thanks</div> <div>Doris</div></div></body></html> ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: 2025-01-20 18:10 (no subject) Lana ABADIE <[email protected]> @ 2025-01-21 17:21 ` Adrian Klaver <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Adrian Klaver @ 2025-01-21 17:21 UTC (permalink / raw) To: Lana ABADIE <[email protected]>; [email protected] On 1/20/25 10:10, Lana ABADIE wrote: > 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. 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, > 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 -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-01-21 17:21 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-01-20 18:10 Lana ABADIE <[email protected]> 2025-01-21 17:21 ` Re: Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox