Bug #71176 [Opn->Nab]: Updating records within a limit/offset loop misses records

Edit report at https://bugs.php.net/bug.php?id=71176&edit=1 ID: 71176 Updated by: requinix@php.net Reported by: espadav8 at gmail dot com Summary: Updating records within a limit/offset loop misses records -Status: Open +Status: Not a bug Type: Bug Package: PDO PgSQL Operating System: OS X and Linux PHP Version: 7.0.1 Block user comment: N Private report: N New Comment: http://www.postgresql.org/docs/current/static/queries-limit.html > When using LIMIT, it is important to use an ORDER BY clause that constrains the > result rows into a unique order. Otherwise you will get an unpredictable subset > of the query's rows. You might be asking for the tenth through twentieth rows, > but tenth through twentieth in what ordering? The ordering is unknown, unless > you specified ORDER BY. Previous Comments: ------------------------------------------------------------------------ [2015-12-20 23:27:37] espadav8 at gmail dot com Description: ------------ When looping over all records in the database using 'limit X offset Y' to update a column, the postgres PDO driver will start to return the same records again for different offset values. It gets worse the smaller the limit size compared to the dataset size. Test script: --------------- https://github.com/EspadaV8/laravel-chunking-non-persistance/blob/develop/public/pg-pdo-test.php Script assumes that there is a database with a table called example with columns id (serial), name (text) and name-new (text). Expected result: ---------------- ID: 72 - Name: 5677360fde707 - Name new: 5677360fde707-qwe ID: 69 - Name: 5677360fdde80 - Name new: 5677360fdde80-qwe ID: 70 - Name: 5677360fde241 - Name new: 5677360fde241-qwe ID: 71 - Name: 5677360fde4c3 - Name new: 5677360fde4c3-qwe Actual result: -------------- Depending on the limit to dataset size (limit 1, 4 records) ID: 62 - Name: 567735dbccacd - Name new: 567735dbccacd ID: 64 - Name: 567735dbccffc - Name new: 567735dbccffc ID: 63 - Name: 567735dbccd4c - Name new: 567735dbccd4c-qwe ID: 61 - Name: 567735dbcc64b - Name new: 567735dbcc64b-qwe-qwe-qwe or (limit 3, 4 records) ID: 72 - Name: 5677360fde707 - Name new: 5677360fde707 ID: 69 - Name: 5677360fdde80 - Name new: 5677360fdde80-qwe ID: 70 - Name: 5677360fde241 - Name new: 5677360fde241-qwe ID: 71 - Name: 5677360fde4c3 - Name new: 5677360fde4c3-qwe-qwe ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=71176&edit=1