My problem is a bit complicated, I will try to simplify it with an example.
Let's say I want to implement a chat system supported by an SQL database. the system consists of one room in which everyone can send messages.
To use chat, you need to install the application (think of a mobile application). This application maintains history on the device and regularly connects to the server to receive only new messages.
The problem I want to solve is how to get only these new messages without losing any of them .
A naive implementation will use a two-column SQL table (id
AUTO_INCREMENT, message). The client connects, receives the story for the first time, and then requests only messages whose identifier is greater than the last identifier that they have.
It seems that in practice this does not work, and you risk losing Messages. I created two programs that use PostgreSQL. One program only does insert into the table, and the other program runs SELECT *regularly and checks that at each iteration there were no new elements with a lower id, the largest identifier of the previous iteration.
In other words, let's say you are SELECT *at one iteration, you will get identifiers 3, 5 and 9. Then at the next iteration you will get 3, 5, 8 and 9. A new message has appeared with a lower id than the largest identifier of the previous iteration. This means that if the client requested all identifiers greater than 9, he would skip 8.
I can provide the sources of the programs, if unclear.
Now, if I run one instance of the verification program and 5 instances of the insert, misses occur 2-3 times per minute.
, ,
. SQL ,
NoSQL. - ,
-, Signal, Whatsapp ,
.
EDIT: , :
create table test (id serial primary key, value int);
:
#include <iostream>
#include <libpq-fe.h>
#include <stdio.h>
#include <stdlib.h>
#include <unordered_set>
int main(int argc, char** argv)
{
auto const conninfo = "dbname = test password = postgres";
PGconn* conn = PQconnectdb(conninfo);
if (PQstatus(conn) != CONNECTION_OK)
{
std::cerr << "error: " << PQerrorMessage(conn) << std::endl;
return 1;
}
std::unordered_set<uint64_t> vals;
uint64_t lastId = 0;
while (true)
{
PGresult* res = PQexec(conn, "SELECT id FROM test ORDER BY id");
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
std::cerr << "error: " << PQerrorMessage(conn) << std::endl;
return 1;
}
for (int i = 0; i < PQntuples(res); i++)
{
auto const id = std::atoll(PQgetvalue(res, i, 0));
if (id <= lastId)
{
if (!vals.count(id))
{
std::cout << id << " was missed" << std::endl;
vals.insert(id);
}
}
else
{
vals.insert(id);
lastId = id;
}
}
PQclear(res);
}
PQfinish(conn);
return 0;
}
:
#include <iostream>
#include <libpq-fe.h>
#include <stdio.h>
#include <stdlib.h>
#include <unordered_set>
int main(int argc, char** argv)
{
auto const conninfo = "dbname = test password = postgres";
PGconn* conn = PQconnectdb(conninfo);
if (PQstatus(conn) != CONNECTION_OK)
{
std::cerr << "error: " << PQerrorMessage(conn) << std::endl;
return 1;
}
while (true)
{
PGresult* res = PQexec(conn, "INSERT INTO test (value) VALUES (1)");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
std::cerr << "error: " << PQerrorMessage(conn) << std::endl;
return 1;
}
PQclear(res);
}
PQfinish(conn);
return 0;
}
5 ( , , -O2), "xxx was missed", , , id .
: psql, 1 2. :
1> create table test (id serial primary key, value int);
1> begin;
1> insert into test (value) values (1);
2> insert into test (value) values (2);
2> select * from test;
-- you see the row (id:2, value:2) in the table
1> commit;
2> select * from test;
-- now you see (id:1, value:1) and (id:2, value:2)
-- an id smaller than 2 has just appeared