MIME-Version: 1.0
Message-ID: <trinity-b4157c97-6383-4d11-b32a-e6f48a7b1baa-1737396615352@3c-app-mailcom-bs15>
From: Lana ABADIE <clipperDB@gmx.fr>
To: pgsql-general@lists.postgresql.org
Content-Type: text/html; charset=UTF-8
Date: Mon, 20 Jan 2025 19:10:15 +0100
Importance: normal
Sensitivity: Normal
UI-OutboundReport: notjunk:1;M01:P0:RZA6NrYUehY=;v1OpxmbgBjy/j+qf5gA1nAxkVOP
 3GfrSapF/mYfC9mJWFtkeJMmPnOComCfdB/7zLCd9XGiqsenvycrD6M093du0PCqQJflckg4t
 PF1z8Vn7kuFl4DIOtxX85oeJS+LQzcbxkLQ4zX3zW74lem4nUeesN6bF10ZeUZQ/CJpOGyeLD
 BjURCi9+fdYQpdi5lzKuiJZemoWEkooTpy/U0WjrssVc2ITWAA7w6GsXQjm0RaA+LNFbR8SRD
 Qlyl0fvjYrXLv01DuVCKkeleWgz7OTBz5HreSDVpITmVyvPX2h2ENPHKL1F27CvkRoiU/znNw
 2fCr1DfgtTSRZl4gXHRwtA1j6SpWOiB1P9Un2bR9erV0MHksclu80HMqPxJblYxB6CQW0AMte
 /pUb9DCbX6la0WxUneimTYiQI13cjY0qL3dVT6Of4d9dMEGjx/l+eozx9y+ree03lwt1NRWx2
 e1PUiKEf1JYqNRRwVuveE2gquikv/sLMo31bxqTMjV9Ut9ER2dxWi2itGn8PO1GId8JJ08mSf
 jXlkPgTRIjj+/AhXezAr57gdXyR2799pbDibdIAUFkDkvctlHfjjPrq7ffDF3/YUfOXCZTN62
 GGzpDeCBsEOcnbh08oAoiCFCitmls50ypdL8rzsMMNVyJsejGPGy3Zx2elKZ5ExxZgrP2mEks
 SXre6KTZHdhfxZ0vx6V2fecV9HPVtK09ZC4q5i/Jg/zWXijxyX49wEmqSwx7AVaWgjRYjiaEy
 aRQl9KiCyGMynm1egaYwtAw589SknES2wlNbEeZ3qaOkWqrVI5gXX0+OkWhCEcaWyUX6umGlk
 wemxggmcj6adEZo2hpRoGJLRTbGBCIGGeFoqc0mDbgK9ucZa7K4GRv2ul6CTm7YDLEu/fHfJG
 7HhEiVqG5SQlpzaSJhPRnmAwoXWy6Gu7FloyEqhZnzhfAweJM7zplHIOjLODWXPwDbTGqwvOg
 YbuUYdMlywn0a/+jlJZrj9wY4Ia+n4cCmZIdSGCYPg/7ejoGXbis3tBtHW/TZsj7nP57S4lg0
 INQDSkG6/v1/lr/BsM=
Archived-At: <https://www.postgresql.org/message-id/trinity-b4157c97-6383-4d11-b32a-e6f48a7b1baa-1737396615352%403c-app-mailcom-bs15>
Precedence: bulk

<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>