[Go] Mistake while implementing BATCH API

While working CRUD operations using Go, I have an endpoint named /v1/person for creating a record in database and to keep it simple, let it accepts the person’s first name and last name as a JSON body, and return 200 on successful creation.

POST — /v1/person — calls a function named h.CreatePerson from handler.

BODY -

{
  "first_name": "Jane",
  "last_name": "Doe"
}

And, it successfully created a record in database. The table persons has columns:

id uuid primary key, 
first_name varchar, 
last_name varchar, 
created_at timestampz NOT NULL

And, then there is a query to fetch the records with offset and limits.

GET — /v1/persons?offset=x&limit=y — calls a function named h.ListPersons

SQL query:

select * from persons order by created_at offset $1 limit $2;

Everything till now was working fine, and then I have implemented a batch request to add multiple persons at same time.

POST — /v1/persons/batch — h.CreatePersons

and, here I have done a mistake, the time we used to create the multiple records was same, so the created_at has same value for all the created persons, and that lead to serious problems while listing the persons.

As the number of records were around 100, and using limit & offset like 15, 20 is working fine most of the times, because we were facing issue when 10 records were created and offset fall into those records. Then postgres is unable to sort based on created_at field as all those records has same value. Let me explain with below example, suppose we have 10 records with entry:

id               first_name last_name created_at
xxx-yyyy-zzz-111 Jane       Doe       2024-05-22 16:28:50.986 +0530
xxx-yyyy-zzz-111 John       Doe       2024-05-23 11:28:50.986 +0530
xxx-yyyy-zzz-111 Mili       Doe       2024-05-24 12:28:50.986 +0530
xxx-yyyy-zzz-111 Foo        Doe       2024-05-25 10:28:50.986 +0530
xxx-yyyy-zzz-111 Bar        Doe       2024-05-25 10:28:50.986 +0530
xxx-yyyy-zzz-111 Baz        Doe       2024-05-25 10:28:50.986 +0530
xxx-yyyy-zzz-111 Bill       Doe       2024-05-25 10:28:50.986 +0530
xxx-yyyy-zzz-111 Max        Doe       2024-05-25 10:28:50.986 +0530
xxx-yyyy-zzz-111 Will       Doe       2024-05-26 16:28:50.986 +0530
xxx-yyyy-zzz-111 Rose       Doe       2024-05-26 16:28:50.986 +0530

Now, if we do query like this:

select * from persons order by created_at asc limit 3 offset 2;

Then the expected result is:

id               first_name last_name created_at
xxx-yyyy-zzz-111 Mili       Doe       2024-05-24 12:28:50.986 +0530
xxx-yyyy-zzz-111 Foo        Doe       2024-05-25 10:28:50.986 +0530
xxx-yyyy-zzz-111 Bar        Doe       2024-05-25 10:28:50.986 +0530

However, the above may come, or it may come as below

id               first_name last_name created_at
xxx-yyyy-zzz-111 Mili       Doe       2024-05-24 12:28:50.986 +0530
xxx-yyyy-zzz-111 Baz        Doe       2024-05-25 10:28:50.986 +0530
xxx-yyyy-zzz-111 Bill       Doe       2024-05-25 10:28:50.986 +0530

Because, while sorting operation with created_at postgres find the same date for these records. All unit tests were passing and finding this issue took half a day for us as I had written unit test based on London school of testing, and then I moved to Chicago school unit testing. The details can be found here about the testing methodologies. And, to fix it, I have changed it to insert the record one by one and using time.Now()

Thank you for reading. I hope it is an helpful article.