postgresql — PHP (yii2) консольный скрипт перестает работать прямо в середине процесса выполнения
У меня есть консольный скрипт (внутри приложения yii2) для изменения имен пользователей в БД (postgreSQL) и записи данных журнала изменений в csv-файл. Я использую для цикла, чтобы сделать изменения в объемах 100 пользователей через смещение.
public function actionTest()
{
$query = User::find()->where(['username' => '']);
$total = $query->count(); // SQL variant - SELECT COUNT(*) FROM user WHERE username = ''
$data = [];
$filePath = '/path/to/folder/log.csv';
for ($offset = 0; $offset <= $total; $offset += 100) {
/** @var User[] $users */
$users = $query->orderBy(['id' => SORT_ASC])->limit(100)->offset($offset)->all(); // SQL variant - SELECT * FROM user WHERE username = '' ORDER BY id ASC OFFSET 0 LIMIT 100
foreach ($users as $user) {
User::updateAll(['username' => 'newUsername'], ['id' => $user->id]); // SQL variant - UPDATE user SET username = 'newUsername' WHERE id = 1
$data[] = ['username' => 'newUsername']; // collect data to generate csv-file in the future
}
$csvObj = new CSV(); // "mnshankar/csv": "1.8"$csvObj->with($data, false, 'a+')->put($filePath, 'a+');
$data = [];
}
}
Проблема в том, что этот скрипт перестает получать данные из БД прямо в середине общего количества пользователей, поэтому я получаю 0 элементов в массиве $ users.
Например, если у меня $ total = 15000, он перестает работать после изменения с $ offset = 7500,
если $ total = 7500, он перестает работать после изменения с $ offset = 3800, если $ total = 3800, он перестает работать после изменения с $ offset = 1900 и т. д.
Я попытался написать простой тест для этого цикла с функцией pg_ *, и он работает правильно:
public function actionPgTest()
{
$dbConnection = pg_connect("host=localhost port=8080 dbname=user_db user=some_guy password=some_pass");
$total = pg_query($dbConnection,'SELECT COUNT(*) FROM user WHERE username = \'\'');
$total = pg_fetch_array($total)['count'];
for ($offset = 0; $offset <= $total; $offset += 100) {
$query = 'SELECT * FROM user WHERE username = \'\' ORDER BY id ASC LIMIT 100 OFFSET ' . $offset;
$users = pg_query($dbConnection,$query);
$users = pg_fetch_all($users);
sleep(3);
}
pg_close();
}
Также я попытался сделать это с помощью bash-script, и он также работает правильно:
#!/bin/bash count_query="select count(*) FROM \"user\" WHERE username = ''" count=$(echo $count_query | psql -U user -Atq user_db) query_base="select id FROM \"user\" WHERE username = '' LIMIT 100 OFFSET " for offset in $(seq 0 100 $count); do echo $query_base$offset| psql -U user -Atq user_db sleep 3; done;
Кроме того, я попытался запустить скрипт без генерации CSV-файла и получил ту же проблему прямо в середине.
Решение
Он продолжается и возвращает пустые данные, поскольку смещение установлено так же, как и общее количество записей.
Вот если значение смещения из документации postgreSQL:
OFFSET говорит, что пропустить столько строк, прежде чем начать возвращать строки.
OFFSET 0 — это то же самое, что опустить предложение OFFSET. Если оба смещения и
Появляется LIMIT, затем строки OFFSET пропускаются перед началом подсчета
строки LIMIT, которые возвращаются.
Также прочитайте это отсюда: https://www.postgresql.org/docs/8.0/static/queries-limit.html
Другие решения
Решено! Проблема была в OFFSET и LIMIT (комментарий devprashant очень помогает).
Например, у нас есть таблица с 6 элементами с минусом в каждом:
- —
- —
- —
- —
- —
- —
Для 1-го изменения мы имеем OFFSET = 0 и LIMIT = 2 и меняем первые 2 минуса, чтобы получить плюсы:
- +
- +
- —
- —
- —
- —
Второе изменение будет с OFFSET = 2 и LIMIT = 2, и мы получим элементы с id = 5 и id = 6. Благодаря смещению результат запроса начинается с id = 5 и ограничивается для 2 элементов. Итак, мы получаем:
- +
- +
- —
- —
- +
- +
Вот так мы получаем арифметическую прогрессию и не набираем предметы до середины общего количества предметов.
Рабочий раствор:
public function actionTest(){
$query = User::find()->where(['username' => '']);
$idsQuery = clone $query;
$userIds = $idsQuery->select(['id'])->limit(1000000)->asArray(true)->indexBy('id')->all();
$userIds = array_keys($userIds);
asort($userIds);
$total = count($userIds);
$data = [];
$filePath = '/path/to/folder/log.csv';
for ($offset = 0; $offset <= $total; $offset += 100) {
$query = User::find()->where(['id' => array_slice($userIds , $offset, 100)]);
$users = $query->all();
foreach ($users as $user) {
User::updateAll(['username' => 'newUsername'], ['id' => $user->id]); // SQL variant - UPDATE user SET username = 'newUsername' WHERE id = 1
$data[] = ['username' => 'newUsername']; // collect data to generate csv-file in the future
}
$csvObj = new CSV(); // "mnshankar/csv": "1.8"$csvObj->with($data, false, 'a+')->put($filePath, 'a+');
$data = [];
}}
detector