Sea battle in PostgreSQL

There is a fierce debate among programmers about the dangers and benefits of stored procedures in databases. Today we will digress from them and again do the incredible in impossible conditions.



Today, developers try to avoid building business logic in databases whenever possible. Nevertheless, there are enthusiasts who challenge themselves and create, for example, an exchange matcher , and sometimes entire companies transfer the server side to database stored procedures. The authors of such projects argue that you can do anything on databases if you want.



Here I involuntarily recall the "sea battle" over BGP . Is it possible to make this game in SQL? To answer this question, we will use PostgreSQL 12 services, as well as PLpgSQL. For those who can't wait to look "under the hood", a link to the repository .



The sea battle game requires constant input from the user throughout the game. The easiest way to interact with a database user is a command line client.

Data input

Getting data from the user is the most difficult task in this project. The easiest way from a development point of view is to ask the user to write correct SQL queries to insert the necessary information into a specially prepared table. This method is relatively slow and requires the user to repeat the request over and over again. I would like to be able to retrieve data without writing an SQL query.



PostgreSQL suggests using COPY… FROM STDIN to save data from standard input to a table. But this solution has two drawbacks.



First, the COPY operator cannot be limited by the amount of information uploaded. The COPY statement terminates only when it receives an end-of-file sign. Thus, the user will additionally have to enter EOF to indicate the completion of information entry.



Secondly, there are no stdin and stdout files in stored procedures and functions. Standard input and output streams are available when executing regular SQL queries through the client, but loops are not available there. Thus, you cannot run the game in one SQL command. This could have been the end of the story, but a cunning solution was found.



PostgreSQL has the ability to logall requests, including incorrect ones. Moreover, logging can be in CSV format, and the COPY operator can work with this format. Let's configure logging in the postgresql.conf configuration file:

log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql.log'
log_min_error_statement = error
log_statement = 'all'

The postgresql.csv file will now record all SQL queries that are executed in PostgreSQL. The documentation, in the Using CSV-Format Log Output section , describes a way to load csv-logs with rotation enabled. We are interested in loading logs with an interval of one second.



Since it is impractical to rotate the logs every second, we will load the log file over and over again, adding to the table with logs. A straightforward solution from one COPY operator will work only the first time, and then it will display an error due to primary key conflicts. This problem is solved by using a staging table and the ON CONFLICT DO NOTHING clause .





You can also add a filter when migrating data from a temporary table to postgres_log, reducing the amount of unnecessary information in the log table. Since we do not plan to receive correct SQL queries from the user, we can restrict ourselves to queries where there is a query text and the command tag is idle.



Unfortunately, PostgreSQL does not have a scheduler that runs a routine on a schedule. Since the problem is located in the "server" part of the game, it can be solved by writing a shell script that will call the stored procedure for loading logs every second.



Any string entered by the user that is not a valid SQL query will now appear in the postgres_log table. Although this method requires the mandatory semicolon separator, it is much easier than sending EOF.



The attentive reader will note that during the execution of a stored procedure or function, the command line client will not process commands and will be absolutely correct. For this solution to work, two clients are required: a "screen" and a "keyboard".



^{Screen client (left) and keyboard client (right)}

To "pair" the keyboard, the screen generates a pseudo-random sequence of characters that must be entered on the client keyboard. The "screen" identifies the keyboard by the unique identifier of the client's session (session_id) and then selects from the log table only rows with the required session identifier.



It is easy to see that the client-keyboard output is not useful, and the input to the client-screen is limited to a single procedure call. For ease of use, you can send the "screen" to the background, and extinguish the output of the "keyboard":

psql <<<'select keyboard_init()' & psql >/dev/null 2>&1

We now have the ability to enter information from standard input into the database and use stored procedures.

Game loop

^{The active part of the game}

The game is conditionally divided into the following phases:

• interface of the screen client with the keyboard client;
• creating a lobby or connecting to an existing one;
• placement of ships;
• the active part of the game.

The game consists of five tables:

• visual display of the field, two tables;
• list of ships and their condition, two tables;
• list of events in the game.

During lobby creation, player A, the server, creates all tables and populates them with initial values. To make it possible to play several games in parallel, all tables in the title have a ten-digit lobby identifier, which is generated pseudo-randomly at the start of the game.



Development of game logic is generally very similar to development in traditional programming languages ​​and differs mostly in syntax and lack of a library for nice formatting. For output, the RAISE operator is used, which for psql displays a message with a log level prefix. You won't be able to get rid of him, but this does not interfere with the game.



There are design differences too, and they make the brain boil.

Commit time

All game logic is launched by the screen client, that is, one procedure is executed from start to finish. Moreover, for one transaction, if the COMMIT operator is not explicitly specified.



This means that new tables and new data in existing tables will not change for the second player until the transaction is complete. Moreover, when working with time, it is important to remember that the now () function returns the current time at the time the transaction started .



Making a commit is not as easy as it sounds. They are only allowed in procedures . An attempt to commit a transaction in a function will result in an error, since it operates within a transaction external to the function.

Running the game

^{Starting the game}

We do not recommend running such a game in a real environment. Fortunately, it is possible to quickly and easily deploy a database with a game. In the repository, you can find a Dockerfile that will build an image with PostgreSQL 12.4 and the necessary configuration. Build and run the image:

docker build -t sql-battleships .
docker run -p 5432:5432 sql-battleships

Connecting to the database in the image:

psql -U postgres <<<'call screen_loop()' & psql -U postgres

Note that PostgreSQL in the container uses the trust authentication policy, that is, it allows all connections without a password. Don't forget to unplug the container after completing all games!

Conclusion

The use of special tools for other purposes often causes negative feedback from professionals. However, solving meaningless but interesting tasks trains lateral thinking and allows you to explore the tool from different points of view in search of a suitable solution.



Today we have once again confirmed that you can write anything you want in SQL if you want. Nevertheless, we recommend using the tools in production for their intended purpose, and doing such fun exclusively as small home projects.