PostgreSQL: the server side point of view

-

In this short article we will see how PostgreSQL behaves during write operations, our goal will be to understand how postgresql makes write operations

Requirements:

Let's start now by creating our own test database:

postgres=# create database mydb;
CREATE DATABASE

Let's connect to the database:

postgres=# \c mydb

You are now connected to database "mydb" as user "postgres".

As reported in the wal2json documentation as postgres user, let's create a slot:

pg_recvlogical -d dbname --slot test_slot --create-slot -P wal2json

and then let's start to collect informations from the wals (making sure that the wal2json2sql executable file has execute permissions for the postgres user):

pg_recvlogical -d dbname --slot test_slot --start -o pretty-print=0 -f -|./wal2json2sql > out.sql

At this point the SQL (DML) instructions,  rebuilded starting from the WALs will be reported in the out.sql file.

Let's start with an example:

First, let's create a table (DDL), this operation will not be reported in the out.sql file:

mydb=# create table mytable(id serial not null primary key,field1 varchar(50));

CREATE TABLE

now let's insert a record and see through a tail -f out.sql what PostgreSQL executes:

mydb=# insert into mytable (field1) values ('orange');

INSERT 0 1

on the out.sql file we have:

insert into public.mytable(id,field1) values (1,'orange');

Now let's try to insert two more records:

mydb=# insert into mytable (field1) values ('apple'),('lemon');

INSERT 0 2

On the out.sql file we have:

insert into public.mytable(id,field1) values (2,'apple');

insert into public.mytable(id,field1) values (3,'lemon');

and now let's delete the record with field1='lemon' :

mydb=# delete from mytable where field1 = 'lemon';

DELETE 1

on the out.sql file we have:

delete from public.mytable where id = 3;

here we start to see the first difference: PostgreSQL tracks deletion using the primary key value. Now let's try to perform an UPDATE operation:

mydb=# update mytable set field1 = 'Blueberry' where field1 = 'apple';

UPDATE 1

On the out.sql file we have:

update public.mytable set id = 2,field1 = 'Blueberry' where id = 2;

As we can see in the above example there are differences between what we wrote and what is reported on the WALs.  On the WAls,in the SET part of the UPDATE all the fields are reported and in the WHARE condition we always see only the primary key. Now let's delete all data from the mytable table:

mydb=# delete from mytable;

DELETE 2

As we can see,PostgreSQL does something different from what we did:

delete from public.mytable where id = 1;

delete from public.mytable where id = 2;

Foreign keys e triggers:

Let's try to see what happens if we set a trigger and a foreign key:

create table mytable_child (

  id integer not null references mytable(id) on delete cascade

  , create_time timestamp with time zone not null default now()

  , primary key (id,create_time)

);

create or replace function mytable_child_ins () returns trigger as

$$

begin

  insert into mytable_child (id) values (NEW.id);

  return new;

end;

$$

language 'plpgsql';

create trigger t_mytable_child_ins after insert on mytable for each row execute function mytable_child_ins ();

Now, every time we insert a record on the mytable table,a record will automatically be inserted into the mytable_child table.

Let's try and see how PostgreSQL behaves this time:

mydb=# insert into mytable (field1) values ('orange');

INSERT 0 1

On the out.sql file we also see the insertion performed by the trigger:

insert into public.mytable(id,field1) values (5,'orange');

insert into public.mytable_child(id,create_time) values (5,'2022-11-28 16:27:32.552652+00');

Now let's insert two values ​​in the same transaction:

mydb=# insert into mytable (field1) values ('apple'),('lemon');

INSERT 0 2

This time the internal insertion sequence is different from what we expected:

insert into public.mytable(id,field1) values (6,'apple');

insert into public.mytable(id,field1) values (7,'lemon');

insert into public.mytable_child(id,create_time) values (6,'2022-11-28 16:28:55.222572+00');

insert into public.mytable_child(id,create_time) values (7,'2022-11-28 16:28:55.222572+00');

As we can see, first the two inserts on the mytable table areperformedand and then two inserts on the mytable_child table are performed ; now let's insert another value with field1='apple' :

mydb=# insert into mytable (field1) values ('apple');

INSERT 0 1

and on the out.sql file we have:

insert into public.mytable(id,field1) values (8,'apple');

insert into public.mytable_child(id,create_time) values (8,'2022-11-28 16:31:31.728661+00');

The table mytable now has the values:

mydb=# select * from mytable;

 id | field1

----+--------

  5 | orange

  6 | apple

  7 | lemon

  8 | apple

(4 rows)

Let's try to delete all the records for which field1 = 'apple' and see how the ON DELETE CASCADE is applied by the foreign key:

mydb=# delete from mytable where field1 = 'apple';

DELETE 2

and on the out.sql file we have:

delete from public.mytable where id = 6;

delete from public.mytable where id = 8;

delete from public.mytable_child where id = 6 and create_time = '2022-11-28 16:28:55.222572+00';

delete from public.mytable_child where id = 8 and create_time = '2022-11-28 16:31:31.728661+00';

here the sequence is slightly different from what I would have expected.

I leave you to have fun with other experiments !! :)

Enjoy !!

Popular posts from this blog