From: Lana ABADIE <clipperDB(at)gmx(dot)fr>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject:
Date: 2025-01-20 18:10:15
Message-ID: trinity-b4157c97-6383-4d11-b32a-e6f48a7b1baa-1737396615352@3c-app-mailcom-bs15
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hi all&nbsp;</div>

<div>I bumped into a weird case that i don&#39;t really understand...maybe someone in this list could have a clue</div>

<div>We have 2 Postgres databases configured as master/slave replica&nbsp;(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 &#36;T1 and&nbsp;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&nbsp;an integer so either T2&lt;T1 in that case i would get a number of rows of zero or T2&gt;=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&nbsp; a total number of rows less than 2000.</div>

<div>Then when i fixed T2, in other words i do a query using between &#36;T1 and &#36;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&#39;m clear.</div>

<div>Thanks</div>

<div>Doris</div></div></body></html>

Attachment Content-Type Size
unknown_filename text/html 2.0 KB

Responses

  • Re: at 2025-01-21 17:21:12 from Adrian Klaver

Browse pgsql-general by date

  From Date Subject
Next Message Durgamahesh Manne 2025-01-21 12:08:38 Records count mismatch with logical replication
Previous Message Andreas Froede 2025-01-20 14:30:50 Re: could not open file "base/XX/XX": Interrupted system call