<?php

function createSchema(PDO $db) {
    $stmt = $db->prepare("
        CREATE TABLE IF NOT EXISTS queue (
            id     SERIAL PRIMARY KEY,
            status VARCHAR
        );
    ");
    $stmt->execute();
}

function createTestData(PDO $db) {
    $db->prepare("
        INSERT INTO queue (status)
        SELECT 'NEW' FROM generate_series(1,10000)
    ")->execute();
}

function runTestCase(PDO $db) {
    $db->beginTransaction();

    $stmt = $db->prepare("
       SELECT * 
         FROM queue as queue1
         JOIN queue as queue2
           ON queue1.id = queue2.id
        WHERE queue2.status = 'NEW'
     ORDER BY queue1.id
        LIMIT 1
FOR UPDATE OF queue1 SKIP LOCKED
    ");
    $stmt->execute();

    $row = $stmt->fetch();
    if (!$row) { return false; }

    $stmt = $db->prepare("
        UPDATE queue SET status = 'DONE' WHERE id = :id AND status = 'NEW'  
    ");

    $stmt->execute([':id' => $row['id']]);
    if ($stmt->rowCount() == 0) {
        echo 'Error could not update row ' . $row['id'] . ' somebody else has done it for me' . "\n";
    }

    $db->commit();
    return true;
}

$db = new PDO("pgsql:host=db-server;dbname=<insert db name here>", '<insert user name here>', '<insert password here>');

echo "Creating schema\n";
createSchema($db);

echo "Creating test-data\n";
createTestData($db);

echo "Running test thread\n";
while (runTestCase($db));