Le blog de Jean David TECHER, un Réunionnais à Saint-Priest/Lyon

PgRouting 1.03 est sortie le 4 Septembre 2008. Au niveau des nouveautés:

• Amélioration du temps de création des topologies/graphes des réseaux à partir des fonctions prévues à cet effet;
• meilleur configuration/gestion pour cmake au niveau de la compilation
• meilleur stabilités de l'implémentation des fonctions issues des paquets core et extra.

Exemple de compilation

[root@jdtecher pgrouting]# wget http://files.postlbs.org/pgrouting/source/pgRouting-1.03.tgz
[root@jdtecher pgrouting]# tar xzf pgRouting-1.03.tgz 
[root@jdtecher pgrouting]# cd routing
[root@jdtecher pgrouting]# cmake -DWITH_TSP=ON -DWITH_DD=ON -DPOSTGRESQL_LIBRARIES=/usr/local/pgsql/lib/libpq.so \
-DPOSTGRESQL_INCLUDE_DIR=/usr/local/pgsql/include/server -DBoost_INCLUDE_DIR=/usr/local/include/boost-1_33_1/boost/\
 -DCGAL_INCLUDE_DIR=/usr/local/cgal/include -DCGAL_LIBRARIES=/usr/local/cgal/lib/libCGAL.so  \
-DGAUL_INCLUDE_DIR=/usr/local/include -DGAUL_LIBRARIES=/usr/local/lib/libgaul.so  -DGAUL_UTIL_LIBRARIES=/usr/local/lib/libgaul_util.so
-- Check for working C compiler: /usr/lib64/ccache/gcc
-- Check for working C compiler: /usr/lib64/ccache/gcc -- works
-- Check size of void*
-- Check size of void* - done
-- Check for working CXX compiler: /usr/lib64/ccache/c++
-- Check for working CXX compiler: /usr/lib64/ccache/c++ -- works
Boost headers were found here: /usr/local/include/boost-1_33_1/boost
Output directory for libraries is set to /usr/local/pgsql/lib
-- Found PGROUTING_CORE core: /home/jdtecher/sources/pgrouting/core/src
Installation directory for libraries is set to /usr/local/pgsql/lib and for SQL files is set to /usr/share/postlbs
Installation directory for libraries is set to /usr/local/pgsql/lib
-- Configuring done
-- Generating done
-- Build files have been written to: /home/jdtecher/sources/pgrouting
[root@jdtecher pgrouting]# make
Scanning dependencies of target routing_tsp
[  8%] Building C object extra/tsp/src/CMakeFiles/routing_tsp.dir/tsp.o
[ 16%] Building CXX object extra/tsp/src/CMakeFiles/routing_tsp.dir/tsp_solver.o
Linking CXX shared library ../../../lib/librouting_tsp.so
[ 16%] Built target routing_tsp
Scanning dependencies of target routing_dd
[ 25%] Building C object extra/driving_distance/src/CMakeFiles/routing_dd.dir/alpha.o
[ 33%] Building CXX object extra/driving_distance/src/CMakeFiles/routing_dd.dir/alpha_drivedist.o
[ 41%] Building CXX object extra/driving_distance/src/CMakeFiles/routing_dd.dir/boost_drivedist.o
[ 50%] Building C object extra/driving_distance/src/CMakeFiles/routing_dd.dir/drivedist.o
Linking CXX shared library ../../../lib/librouting_dd.so
[ 50%] Built target routing_dd
Scanning dependencies of target routing
[ 58%] Building C object core/src/CMakeFiles/routing.dir/dijkstra.o
[ 66%] Building C object core/src/CMakeFiles/routing.dir/astar.o
[ 75%] Building C object core/src/CMakeFiles/routing.dir/shooting_star.o
[ 83%] Building CXX object core/src/CMakeFiles/routing.dir/boost_wrapper.o
[ 91%] Building CXX object core/src/CMakeFiles/routing.dir/astar_boost_wrapper.o
[100%] Building CXX object core/src/CMakeFiles/routing.dir/shooting_star_boost_wrapper.o
Linking CXX shared library ../../lib/librouting.so
[100%] Built target routing
[root@jdtecher pgrouting]# make install
[ 16%] Built target routing_tsp
[ 50%] Built target routing_dd
[100%] Built target routing
Linking CXX shared library CMakeFiles/CMakeRelink.dir/librouting_tsp.so
Linking CXX shared library CMakeFiles/CMakeRelink.dir/librouting_dd.so
Install the project...
-- Install configuration: ""
-- Install configuration: ""
-- Install configuration: ""
-- Install configuration: ""
-- Installing /usr/local/pgsql/lib/librouting.so
-- Install configuration: ""
-- Installing /usr/share/postlbs/routing_core.sql
-- Installing /usr/share/postlbs/routing_core_wrappers.sql
-- Installing /usr/share/postlbs/routing_topology.sql
-- Install configuration: ""
-- Install configuration: ""
-- Installing /usr/local/pgsql/lib/librouting_tsp.so
-- Install configuration: ""
-- Installing /usr/share/postlbs/routing_tsp.sql
-- Installing /usr/share/postlbs/routing_tsp_wrappers.sql
-- Install configuration: ""
-- Install configuration: ""
-- Installing /usr/local/pgsql/lib/librouting_dd.so
-- Install configuration: ""
-- Installing /usr/share/postlbs/routing_dd.sql
-- Installing /usr/share/postlbs/routing_dd_wrappers.sql

Chargement des fonctionnalités

createdb testrouting
createlang plpgsql testrouting
psql -d testrouting -f /usr/local/pgsql/share/lwpostgis.sql 
 psql -d testrouting -f /usr/local/pgsql/share/spatial_ref_sys.sql 
for file in $(ls /usr/share/postlbs/*);do psql -d testrouting -f $file;done

pg_filedump est disponible à http://sources.redhat.com/rhdb/utilities.html. Il est notamment util pour pouvoir analyzer les pages de données (tables, index etc...) en offrant diverses informations sur les diverses pages.

compilation

Pour l'installation, il faut les sources. Comme j'ai PostgreSQL avec les sources. On décompresse celà dans postgresql-8.1.4/contrib.

En fonctions des remarques formulées dans README.pg_filedump, il faut modifier le fichier Makefile. Voici le contenu du mien.

# View README.pg_filedump first

CC=gcc
CFLAGS=-g -O -Wall -Wmissing-prototypes -Wmissing-declarations

INCLUDE=/usr/local/pgsql/include/server

# PGSQL MUST POINT TO pgsql SOURCE DIRECTORY
PGSQL=/home/jdtecher/sources/postgresql-8.1.4

CRC_SRC=${PGSQL}/src/backend/utils/hash
CRC_INCLUDE=${PGSQL}/src

all: pg_filedump

pg_filedump: pg_filedump.o pg_crc.o 
	${CC} ${CFLAGS} -o pg_filedump pg_filedump.o pg_crc.o

pg_filedump.o: pg_filedump.c
	${CC} ${CFLAGS} -I${INCLUDE} pg_filedump.c -c

pg_crc.o: ${CRC_SRC}/pg_crc.c
	${CC} ${CFLAGS} -I${CRC_INCLUDE} -I${INCLUDE} ${CRC_SRC}/pg_crc.c -c 

clean:
	rm -rf *.o pg_filedump

Ensuite

make

Une fois compilée, on peut alors l'utiliser

Utilisation

Sur ma base performance, j'ai la table test dont la structure est la suivante

performance=# \d test
      Table « public.test »
 Colonne |  Type   | Modificateurs 
---------+---------+---------------
 id      | integer |

Insérons quelques données au moins 20 tuples

performance=# insert into test (select generate_series(1,20));

On attend quelques minutes le temps que tout soit écrit dans les fichiers de données. Pour continuer, il me faut l'oid de la table et celui de la table. On obtient celà en faisant

performance=# select a.oid as "oid de la base",b.relfilenode as "oid de la table" from pg_database a,pg_class b 
                               where (a.datname,b.relname)=('performance','test');
 oid de la base | oid de la table 
----------------+-----------------
       12745801 |        14111977
(1 ligne)

Et pour localiser la base

performance=# show data_directory;
   data_directory    
---------------------
 /home/postgres/data

Ok. Maintenant on voit pouvoir l'utiliser. Ce qui donne donc

[root@jdtecher pg_filedump-8.1]# ./pg_filedump  -x -S 8192 /home/postgres/data/base/12745801/14111977

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 8.1.1
*
* File: /home/postgres/data/base/12745801/14111977
* Options used: -x -S 8192 
*
* Dump created on: Thu Sep 11 18:08:37 2008
*******************************************************************

Block    0 ********************************************************
 -----
 Block Offset: 0x00000000         Offsets: Lower     100 (0x0064)
 Block: Size 8192  Version    3            Upper    7392 (0x1ce0)
 LSN:  logid      7 recoff 0x2f0e63b0      Special  8192 (0x2000)
 Items:   20                   Free Space: 7292
 Length (including item array): 104

 ------ 
 Item   1 -- Length:   36  Offset: 8152 (0x1fd8)  Flags: USED
 Item   2 -- Length:   36  Offset: 8112 (0x1fb0)  Flags: USED
 Item   3 -- Length:   36  Offset: 8072 (0x1f88)  Flags: USED
 Item   4 -- Length:   36  Offset: 8032 (0x1f60)  Flags: USED
 Item   5 -- Length:   36  Offset: 7992 (0x1f38)  Flags: USED
 Item   6 -- Length:   36  Offset: 7952 (0x1f10)  Flags: USED
 Item   7 -- Length:   36  Offset: 7912 (0x1ee8)  Flags: USED
 Item   8 -- Length:   36  Offset: 7872 (0x1ec0)  Flags: USED
 Item   9 -- Length:   36  Offset: 7832 (0x1e98)  Flags: USED
 Item  10 -- Length:   36  Offset: 7792 (0x1e70)  Flags: USED
 Item  11 -- Length:   36  Offset: 7752 (0x1e48)  Flags: USED
 Item  12 -- Length:   36  Offset: 7712 (0x1e20)  Flags: USED
 Item  13 -- Length:   36  Offset: 7672 (0x1df8)  Flags: USED
 Item  14 -- Length:   36  Offset: 7632 (0x1dd0)  Flags: USED
 Item  15 -- Length:   36  Offset: 7592 (0x1da8)  Flags: USED
 Item  16 -- Length:   36  Offset: 7552 (0x1d80)  Flags: USED
 Item  17 -- Length:   36  Offset: 7512 (0x1d58)  Flags: USED
 Item  18 -- Length:   36  Offset: 7472 (0x1d30)  Flags: USED
 Item  19 -- Length:   36  Offset: 7432 (0x1d08)  Flags: USED
 Item  20 -- Length:   36  Offset: 7392 (0x1ce0)  Flags: USED


*** End of File Encountered. Last Block Read: 0 ***

On fait maintenant une nouvelle insertion

performance=# insert into test (select generate_series(20,256));

Ce qui va donner

root@jdtecher pg_filedump-8.1]# ./pg_filedump  /home/postgres/data/base/12745801/14111977|grep -v Flag

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 8.1.1
*
* File: /home/postgres/data/base/12745801/14111977
* Options used: None
*
* Dump created on: Thu Sep 11 18:21:01 2008
*******************************************************************

Block    0 ********************************************************
 -----
 Block Offset: 0x00000000         Offsets: Lower     760 (0x02f8)
 Block: Size 8192  Version    3            Upper     792 (0x0318)
 LSN:  logid      7 recoff 0x2f0e9150      Special  8192 (0x2000)
 Items:  185                   Free Space:   32
 Length (including item array): 764

 ------ 


Block    1 ********************************************************
 -----
 Block Offset: 0x00002000         Offsets: Lower     308 (0x0134)
 Block: Size 8192  Version    3            Upper    5312 (0x14c0)
 LSN:  logid      7 recoff 0x2f0ea368      Special  8192 (0x2000)
 Items:   72                   Free Space: 5004
 Length (including item array): 312

 ------ 


*** End of File Encountered. Last Block Read: 1 ***

Un avant-dernier insert pour la route

insert into test (select generate_series(257,395));

Ce qui donne donc

[root@jdtecher pg_filedump-8.1]# ./pg_filedump  /home/postgres/data/base/12745801/14111977|grep -v Flag

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 8.1.1
*
* File: /home/postgres/data/base/12745801/14111977
* Options used: None
*
* Dump created on: Thu Sep 11 18:26:32 2008
*******************************************************************

Block    0 ********************************************************
 -----
 Block Offset: 0x00000000         Offsets: Lower     760 (0x02f8)
 Block: Size 8192  Version    3            Upper     792 (0x0318)
 LSN:  logid      7 recoff 0x2f0e9150      Special  8192 (0x2000)
 Items:  185                   Free Space:   32
 Length (including item array): 764

 ------ 


Block    1 ********************************************************
 -----
 Block Offset: 0x00002000         Offsets: Lower     760 (0x02f8)
 Block: Size 8192  Version    3            Upper     792 (0x0318)
 LSN:  logid      7 recoff 0x2f0ecce8      Special  8192 (0x2000)
 Items:  185                   Free Space:   32
 Length (including item array): 764

 ------ 


Block    2 ********************************************************
 -----
 Block Offset: 0x00004000         Offsets: Lower     124 (0x007c)
 Block: Size 8192  Version    3            Upper    7152 (0x1bf0)
 LSN:  logid      7 recoff 0x2f0ed368      Special  8192 (0x2000)
 Items:   26                   Free Space: 7028
 Length (including item array): 128

 ------ 


*** End of File Encountered. Last Block Read: 2 ***

Pour compléter le dernier bloc n°2,

performance=# insert into test (select generate_series(396,554))

car 554=396+158,. On a alors

[root@jdtecher pg_filedump-8.1]# ./pg_filedump  /home/postgres/data/base/12745801/14111977|grep -v Flag

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 8.1.1
*
* File: /home/postgres/data/base/12745801/14111977
* Options used: None
*
* Dump created on: Thu Sep 11 18:30:10 2008
*******************************************************************

Block    0 ********************************************************
 -----
 Block Offset: 0x00000000         Offsets: Lower     760 (0x02f8)
 Block: Size 8192  Version    3            Upper     792 (0x0318)
 LSN:  logid      7 recoff 0x2f0e9150      Special  8192 (0x2000)
 Items:  185                   Free Space:   32
 Length (including item array): 764

 ------ 


Block    1 ********************************************************
 -----
 Block Offset: 0x00002000         Offsets: Lower     760 (0x02f8)
 Block: Size 8192  Version    3            Upper     792 (0x0318)
 LSN:  logid      7 recoff 0x2f0ecce8      Special  8192 (0x2000)
 Items:  185                   Free Space:   32
 Length (including item array): 764

 ------ 


Block    2 ********************************************************
 -----
 Block Offset: 0x00004000         Offsets: Lower     760 (0x02f8)
 Block: Size 8192  Version    3            Upper     792 (0x0318)
 LSN:  logid      7 recoff 0x2f0f00a8      Special  8192 (0x2000)
 Items:  185                   Free Space:   32
 Length (including item array): 764

 ------ 


*** End of File Encountered. Last Block Read: 2 ***

Notre page est donc remplie!

On a pour le moment procéder qu'à des insertions. Et si on faisait des DELETE et UPDATE!!!

Comme 554/2=277, on va donc faire

performance=# delete from test where id<=277;
DELETE 278
performance=# update test set id=-1 where id>=277;
UPDATE 277

Une page prend donc 185 items. Soit pour 277 tuples, il faut une page et demi. On va donc se retrouver avec 5 pages. Ce que confirme al commande suivante

[root@jdtecher pg_filedump-8.1]# ./pg_filedump  /home/postgres/data/base/12745801/14111977|grep -v Flag

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 8.1.1
*
* File: /home/postgres/data/base/12745801/14111977
* Options used: None
*
* Dump created on: Thu Sep 11 18:36:45 2008
*******************************************************************

Block    0 ********************************************************
 -----
 Block Offset: 0x00000000         Offsets: Lower     760 (0x02f8)
 Block: Size 8192  Version    3            Upper     792 (0x0318)
 LSN:  logid      7 recoff 0x2f0f49b8      Special  8192 (0x2000)
 Items:  185                   Free Space:   32
 Length (including item array): 764

 ------ 


Block    1 ********************************************************
 -----
 Block Offset: 0x00002000         Offsets: Lower     760 (0x02f8)
 Block: Size 8192  Version    3            Upper     792 (0x0318)
 LSN:  logid      7 recoff 0x2f0f9840      Special  8192 (0x2000)
 Items:  185                   Free Space:   32
 Length (including item array): 764

 ------ 


Block    2 ********************************************************
 -----
 Block Offset: 0x00004000         Offsets: Lower     760 (0x02f8)
 Block: Size 8192  Version    3            Upper     792 (0x0318)
 LSN:  logid      7 recoff 0x2f0fec80      Special  8192 (0x2000)
 Items:  185                   Free Space:   32
 Length (including item array): 764

 ------ 


Block    3 ********************************************************
 -----
 Block Offset: 0x00006000         Offsets: Lower     760 (0x02f8)
 Block: Size 8192  Version    3            Upper     792 (0x0318)
 LSN:  logid      7 recoff 0x2f0fd288      Special  8192 (0x2000)
 Items:  185                   Free Space:   32
 Length (including item array): 764

 ------ 


Block    4 ********************************************************
 -----
 Block Offset: 0x00008000         Offsets: Lower     388 (0x0184)
 Block: Size 8192  Version    3            Upper    4512 (0x11a0)
 LSN:  logid      7 recoff 0x2f0fec80      Special  8192 (0x2000)
 Items:   92                   Free Space: 4124
 Length (including item array): 392

 ------ 


*** End of File Encountered. Last Block Read: 4 ***

Les blocs 3 et 4 contiennent les UPDATE effectuées. Ce que confirme la requête suivante

performance=# select id,xmin,cmin,ctid from test where id=-1;
 id |   xmin   | cmin |  ctid   
----+----------+------+---------
 -1 | 10405570 |    0 | (3,1)
 -1 | 10405570 |    0 | (3,2)
 -1 | 10405570 |    0 | (3,3)
 -1 | 10405570 |    0 | (3,4)
 -1 | 10405570 |    0 | (3,5)
 -1 | 10405570 |    0 | (3,6)
 -1 | 10405570 |    0 | (3,7)
 -1 | 10405570 |    0 | (3,8)
  ...       ......            .....
  ...       ......            .....
  ...       ......            .....
 -1 | 10405570 |    0 | (3,183)
 -1 | 10405570 |    0 | (3,184)
 -1 | 10405570 |    0 | (3,185)
 -1 | 10405570 |    0 | (4,1)
 -1 | 10405570 |    0 | (4,2)
 -1 | 10405570 |    0 | (4,3)
  ...       ......            .....
  ...       ......            .....
  ...       ......            .....
 -1 | 10405570 |    0 | (4,89)
 -1 | 10405570 |    0 | (4,90)
 -1 | 10405570 |    0 | (4,91)
 -1 | 10405570 |    0 | (4,92)
(277 lignes)

La commande UPDATE d'identifiant de transaction n°10405570 a donc agit sur les blocs 3 et 4 (cf champs ctid).

On a donc fait fait 555 insertions, 277 deletes et 277 updates. Ce que confirme la requête suivante

performance=# select * from pg_stat_user_tables where relname='test';
  relid   | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del 
----------+------------+---------+----------+--------------+----------+---------------+-----------+-----------+-----------
 14111979 | public     | test    |        3 |         1109 |          |               |       555 |       277 |       278
(1 ligne)

On fait un petit vacuum pour la route

performance=# VACUUM ANALYZE verbose test;
INFO:  vacuuming "public.test"
INFO:  "test": removed 555 row versions in 3 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "test": found 555 removable, 277 nonremovable row versions in 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.test"
INFO:  "test": scanned 5 of 5 pages, containing 277 live rows and 0 dead rows; 277 rows in sample, 277 estimated total rows
VACUUM

Et un full donnera

performance=# VACUUM full ANALYZE verbose test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 277 nonremovable row versions in 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 36 to 36 bytes long.
There were 555 unused item pointers.
Total free space (including removable row versions) is 26452 bytes.
3 pages are or will become empty, including 0 at the end of the table.
4 pages containing 26420 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "test": moved 277 row versions, truncated 5 to 2 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.test"
INFO:  "test": scanned 2 of 2 pages, containing 277 live rows and 0 dead rows; 277 rows in sample, 277 estimated total rows
VACUUM

En réutilsiant pg_filedump, on doit avoir récupérer de la place.

[root@jdtecher pg_filedump-8.1]# ./pg_filedump  /home/postgres/data/base/12745801/14111979|grep -v Flag

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 8.1.1
*
* File: /home/postgres/data/base/12745801/14111979
* Options used: None
*
* Dump created on: Thu Sep 11 19:18:05 2008
*******************************************************************

Block    0 ********************************************************
 -----
 Block Offset: 0x00000000         Offsets: Lower     760 (0x02f8)
 Block: Size 8192  Version    3            Upper     792 (0x0318)
 LSN:  logid      7 recoff 0x2f12b168      Special  8192 (0x2000)
 Items:  185                   Free Space:   32
 Length (including item array): 764

 ------ 


Block    1 ********************************************************
 -----
 Block Offset: 0x00002000         Offsets: Lower     760 (0x02f8)
 Block: Size 8192  Version    3            Upper    4512 (0x11a0)
 LSN:  logid      7 recoff 0x2f12cfe8      Special  8192 (0x2000)
 Items:  185                   Free Space: 3752
 Length (including item array): 764

 ------ 


*** End of File Encountered. Last Block Read: 1 ***

C'est exactement ce qu'on voulait.

Question au combien intéressante! On va distinguer le mode mono-transactionnel et multitransactionnel

Hors mode transactionnel

Créons la petite table suivante

CREATE TABLE test (
    id integer,
    data text
);

On insère les données en utilisant la transaction suivante

BEGIN TRANSACTION;
INSERT INTO test (id, data) VALUES (1, 'ligne 1');
INSERT INTO test (id, data) VALUES (2, 'ligne 2');
INSERT INTO test (id, data) VALUES (3, 'ligne 3');
INSERT INTO test (id, data) VALUES (4, 'ligne 4');
INSERT INTO test (id, data) VALUES (5, 'ligne 5');
INSERT INTO test (id, data) VALUES (6, 'ligne 6');
INSERT INTO test (id, data) VALUES (7, 'ligne 7');
INSERT INTO test (id, data) VALUES (8, 'ligne 8');
INSERT INTO test (id, data) VALUES (9, 'ligne 9');
INSERT INTO test (id, data) VALUES (10, 'ligne 10');
END TRANSACTION;

Ce qui donne donc

performance=# SELECT id,data FROM test;
 id |   data   
----+----------
  1 | ligne 1
  2 | ligne 2
  3 | ligne 3
  4 | ligne 4
  5 | ligne 5
  6 | ligne 6
  7 | ligne 7
  8 | ligne 8
  9 | ligne 9
 10 | ligne 10
(10 rows)

A quoi serve xmin et cmin?

La requête suivante le montre

performance=# SELECT xmin,cmin,xmax,cmax,ctid,id,data FROM test ORDER BY 6;
   xmin   | cmin | xmax | cmax |  ctid  | id |   data   
----------+------+------+------+--------+----+----------
 10328502 |    0 |    0 |    0 |  (0,1) |  1 | ligne 1
 10328502 |    1 |    0 |    0 |  (0,2) |  2 | ligne 2
 10328502 |    2 |    0 |    0 |  (0,3) |  3 | ligne 3
 10328502 |    3 |    0 |    0 |  (0,4) |  4 | ligne 4
 10328502 |    4 |    0 |    0 |  (0,5) |  5 | ligne 5
 10328502 |    5 |    0 |    0 |  (0,6) |  6 | ligne 6
 10328502 |    6 |    0 |    0 |  (0,7) |  7 | ligne 7
 10328502 |    7 |    0 |    0 |  (0,8) |  8 | ligne 8
 10328502 |    8 |    0 |    0 |  (0,9) |  9 | ligne 9
 10328502 |    9 |    0 |    0 | (0,10) | 10 | ligne 10
(10 rows)

Donc

• xmin: identifiant de transaction pour la version de ligne au niveau insertion;
• cmin: identifiant d'instruction SQL au sein de la transaction-mère (xmin). Le compteur commence à zéro.

Faisons maintenant des petites 'delete' au sein d'une transaction avortée

BEGIN TRANSACTION;
DELETE FROM test  WHERE id=1;
DELETE FROM test   WHERE id=2;
DELETE FROM test  WHERE id=3;
DELETE FROM test  WHERE id=6;
DELETE FROM test   WHERE id=7;
DELETE FROM test  WHERE id=8;;
ROLLBACK TRANSACTION;

Comme on a fait un ROLLBACK, on a alors:

performance=# SELECT xmin,cmin,xmax,cmax,ctid,id,data FROM test ORDER BY 6;
   xmin   | cmin |   xmax   | cmax |  ctid  | id |   data   
----------+------+----------+------+--------+----+----------
 10328502 |    0 | 10328504 |    0 |  (0,1) |  1 | ligne 1
 10328502 |    1 | 10328504 |    1 |  (0,2) |  2 | ligne 2
 10328502 |    2 | 10328504 |    2 |  (0,3) |  3 | ligne 3
 10328502 |    3 |        0 |    0 |  (0,4) |  4 | ligne 4
 10328502 |    4 |        0 |    0 |  (0,5) |  5 | ligne 5
 10328502 |    5 | 10328504 |    3 |  (0,6) |  6 | ligne 6
 10328502 |    6 | 10328504 |    4 |  (0,7) |  7 | ligne 7
 10328502 |    7 | 10328504 |    5 |  (0,8) |  8 | ligne 8
 10328502 |    8 |        0 |    0 |  (0,9) |  9 | ligne 9
 10328502 |    9 |        0 |    0 | (0,10) | 10 | ligne 10
(10 rows)

En mode transactionnel.

En fait, on voit vraiment les différences en mode transactionnel. Supposons donc deux utilisateurs david et postgres. On va commencer par dropper la table. David (connexion 1) et postgres (connexion 2) se connecre à la base.

David se connecte à la base, détruit la base et reconstruit la table et la recharge

DROP TABLE test;

CREATE TABLE test (
    id integer,
    data text
);

BEGIN TRANSACTION;
INSERT INTO test (id, data) VALUES (1, 'ligne 1');
INSERT INTO test (id, data) VALUES (2, 'ligne 2');
INSERT INTO test (id, data) VALUES (3, 'ligne 3');
INSERT INTO test (id, data) VALUES (4, 'ligne 4');
INSERT INTO test (id, data) VALUES (5, 'ligne 5');
INSERT INTO test (id, data) VALUES (6, 'ligne 6');
INSERT INTO test (id, data) VALUES (7, 'ligne 7');
INSERT INTO test (id, data) VALUES (8, 'ligne 8');
INSERT INTO test (id, data) VALUES (9, 'ligne 9');
INSERT INTO test (id, data) VALUES (10, 'ligne 10');
END TRANSACTION;

Tout est commité sans souci!

postgres se connecte à son tour et fait

performance=# SELECT xmin,cmin,xmax,cmax,ctid,id,data FROM test ORDER BY 6;
   xmin   | cmin | xmax | cmax |  ctid  | id |   data   
----------+------+------+------+--------+----+----------
 10328534 |    0 |    0 |    0 |  (0,1) |  1 | ligne 1
 10328534 |    1 |    0 |    0 |  (0,2) |  2 | ligne 2
 10328534 |    2 |    0 |    0 |  (0,3) |  3 | ligne 3
 10328534 |    3 |    0 |    0 |  (0,4) |  4 | ligne 4
 10328534 |    4 |    0 |    0 |  (0,5) |  5 | ligne 5
 10328534 |    5 |    0 |    0 |  (0,6) |  6 | ligne 6
 10328534 |    6 |    0 |    0 |  (0,7) |  7 | ligne 7
 10328534 |    7 |    0 |    0 |  (0,8) |  8 | ligne 8
 10328534 |    8 |    0 |    0 |  (0,9) |  9 | ligne 9
 10328534 |    9 |    0 |    0 | (0,10) | 10 | ligne 10
(10 rows)

Postgres ouvre maintenant une transaction

begin;

et execute les instructions siuvantes

DELETE FROM test  WHERE id=1;
DELETE FROM test   WHERE id=2;
DELETE FROM test  WHERE id=3;
DELETE FROM test  WHERE id=6;
DELETE FROM test   WHERE id=7;
DELETE FROM test  WHERE id=8;;

Pour le moment postgres n'a pas encore commité. David lance la requête suivante

performance=# SELECT xmin,cmin,xmax,cmax,ctid,id,data FROM test ORDER BY 6;
   xmin   | cmin |   xmax   | cmax |  ctid  | id |   data   
----------+------+----------+------+--------+----+----------
 10328544 |    0 | 10328547 |    0 | (0,1)  |  1 | ligne 1
 10328544 |    1 | 10328547 |    1 | (0,2)  |  2 | ligne 2
 10328544 |    2 | 10328547 |    2 | (0,3)  |  3 | ligne 3
 10328544 |    3 |        0 |    0 | (0,4)  |  4 | ligne 4
 10328544 |    4 |        0 |    0 | (0,5)  |  5 | ligne 5
 10328544 |    5 | 10328547 |    3 | (0,6)  |  6 | ligne 6
 10328544 |    6 | 10328547 |    4 | (0,7)  |  7 | ligne 7
 10328544 |    7 | 10328547 |    5 | (0,8)  |  8 | ligne 8
 10328544 |    8 |        0 |    0 | (0,9)  |  9 | ligne 9
 10328544 |    9 |        0 |    0 | (0,10) | 10 | ligne 10
(10 lignes

David voit donc que les lignes pour lesquels id=1,2,3,6,7,8 sont en cours de suppression au cours de la transaction 10328547 lancée par postgres.

Maintenant postgres commite le tout.

end transaction;

David rejout sa requête

performance=# SELECT xmin,cmin,xmax,cmax,ctid,id,data FROM test ORDER BY 6;
   xmin   | cmin | xmax | cmax |  ctid  | id |   data   
----------+------+------+------+--------+----+----------
 10328544 |    3 |    0 |    0 | (0,4)  |  4 | ligne 4
 10328544 |    4 |    0 |    0 | (0,5)  |  5 | ligne 5
 10328544 |    8 |    0 |    0 | (0,9)  |  9 | ligne 9
 10328544 |    9 |    0 |    0 | (0,10) | 10 | ligne 10
(4 lignes)

Lundi et mardi j'étais sur Montpellier! Connecté en interne à l'appart pour lire mon mail, en essayant d'en envoyer je recevais aussi ce genre de message:

rcpthost rcpt to <...> denied from your location

La solution

Mon IP est 192.168.2.4. Avec Qmail, il suffit d'ajouter la ligne suivante dans le fichier /service/smtpd/tcp

192.168.2.4:allow,RELAYCLIENT="",QMAILQUEUE="/var/qmail/bin/simscan"

Puis on rédémarre Qmail d'abord en le stoppant

svc -d /service/*

et on rallume

svc -u /service/*

N.B: Au lieu de tout redémarrer, on peut se limiter à /service/smtpd