python - postgresql 10 on FreeBSD sporadically returns incorrect float values running the exact same query

Question

We've been running postgresql server 9.6 on FreeBSD 11.2 so far and tried to upgrade to version 10.4 last month. This week we tried again to upgrade to 10.5 hoping the issue might be solved.

After the upgrade, we experience the following behavior running this select query:

SELECT assets_daily.close 
FROM assets_daily 
  INNER JOIN assets_daily_conditions ON (assets_daily.id = assets_daily_conditions.daily_id) 
WHERE (assets_daily.asset_id = 139 AND assets_daily_conditions.condition_id = 117 AND assets_daily.close_diff_10 IS NOT NULL) 
ORDER BY assets_daily.date DESC

Using either python libraries or just using the psql command client tool that comes with postgresql we will sporadically get different assets_daily.close values. So instead of the correct value, 10.8 we would get 0 or even more strange >.8

Running the same query again and again, this happens about every 10000 runs. Sometimes right after 50, sometimes after 20000 or even more. So it's very sporadic.

Here's some more information: assets_daily.close is a float8 field. The tables are not small (assets_daily has 8 million records, assets_daily_conditions about 45 million records). There are no other clients or processes running that are accessing the database at the same time. We updated FreeBSD to the latest 11.2R and tried both installing postgresql-server10 using the binary and the ports versions. The server logs show no errors and there's plenty of free memory.

We first tried to upgrade using pg_upgrade but after we got the errors also tried to pg_dump from 9.6 and re-import from scratch into 10.5. Same results.

I know this is hard/impossible to reproduce unless we provide a really detailed example with a dataset etc. But my hope is that maybe someone out there has similar issues and maybe even a solution or has some hints/ideas in what direction we could start looking for one.

标签： pythonpostgresqlfreebsdpostgresql-10