PostgreSQL: een inleiding

PostgreSQL (spreek uit op zn engels: “post-gress-QL”) is net als MySQL een open source databasesysteem gebaseerd op SQL. PostgreSQL lijkt veel meer op het bekende systeem Oracle en wordt door heel veel professionals gebruikt vanwege de betrouwbaarheid en mogelijkheden. De fouten en beperkingen in MySQL zijn in PostgreSQL tenminste weggewerkt. Door de meeste intensieve databasegebruikers wordt PostgreSQL gezien als betrouwbaarder en flexibeler. Het is over het algemeen wel iets gecompliceerder dan MySQL.

Op http://www.postgresql.org kun je het systeem downloaden. Let er op dat het bij Windows alleen draait op een NTFS bestandssysteem, dus ga het nu niet proberen op FAT32. Je gaat nu beginnen met een echte database!

In principe werkt dit allemaal praktisch hetzelfde als met MySQL. Hieronder zal ik een aantal voorbeelden geven om snel aan de slag te kunnen. Vergeet niet de PostgreSQL module in php.ini aan te zetten (puntkomma weghalen vóór de modulefile inclusion en bestand plaatsen).

resource pg_connect ( string connection_string)
resource pg_query ( resource connection, string query)
array pg_fetch_array ( resource result [, int row [, int result_type]])
object pg_fetch_object ( resource result [, int row [, int result_type]])

Connectie maken
<?php
$conn_string = "host=localhost port=5423 dbname=DATABASENAAM user=USERNAME password=PASSWORD";
$db = pg_connect($conn_string) or die (pg_last_error());
?>

Query uitvoeren
<?php
$result = pg_query($db, "SELECT blaat FROM blaat");
?>

Inhoud terugkrijgen
<?php
$arr = pg_fetch_array($result, 0, PGSQL_ASSOC);
echo $arr["fieldname"];
?>

of:

<?php
for ($i=0; $arr = pg_fetch_array($result, $i, PGSQL_ASSOC); $i++) {
echo $arr["fieldname"];
}
?>

of:

<?php
while ($data = pg_fetch_object($result)) {
echo $data->fieldname;
}
?>

Bij elkaar
<?php
$connection_string = "host=localhost port=5423 dbname=DATABASENAAM user=USERNAME password=PASSWORD";
$db = pg_connect($conn_string) or die (pg_last_error());
$result = pg_query($db, "SELECT blaat FROM blaat");
while ($data = pg_fetch_object($result)) {
echo $data->fieldname;
}
?>

Voor het gemak ga ik enkel de vergelijking met MySQL aan. Oracle is wat mij betreft minstens zo goed. ODBC is van Microsoft, dus daar ben ik sowieso niet blij mee.

Wat kan MySQL nu niet wat PostgreSQL wel kan? De meeste MySQL databases draaien op MyISAM. Het verschil tussen MyISAM en de PostgreSQL engine zit in de volgende vier punten:
1. PostgreSQL heeft ondersteuning voor transactions
2. PostgreSQL heeft ondersteuning voor row-locking
3. PostgreSQL heeft ondersteuning voor stored procedures
4. PostgreSQL heeft ondersteuning voor FOREIGN KEYs

De transactions en row-locking vergroten de betrouwbaarheid van een databasesysteem bij veel query’s (of een grote database) enorm. Bij een grote database moet je eigenlijk niet eens aan MyISAM denken als er veel geINSERT of geUPDATE wordt. Stored procedures zijn stukken code die de database uitvoert bij een bepaalde event. FOREIGN KEYs hoor je naar mijn mening te kennen van bijvoorbeeld InnoDB: een link tussen een INDEX en PRIMARY KEY om een verband aan te geven (en foutmelding te geven als de waarde niet bestaat in de PRIMARY KEY) (http://www.phphulp.nl/php/tutorials/3/274/504/).

De engine InnoDB van MySQL is op zich ook redelijk goed, maar blijkt een stuk trager te zijn dan PostgreSQL en heeft minder andere opties waar ik nu even niet op doorga.

In PostgreSQL zijn de datatypes iets anders. Het meest interessante verschil om te weten is dat "int auto_increment" nu "serial" is geworden, en "bigint auto_increment" nu "bigserial" is geworden. Ook kun je geen getal meer achter INT of BIGINT zetten. Dus INT(11) is fout. Een integer is namelijk altijd 4 bytes (-2147483648 tot +2147483647), een bigint 8 bytes (-9223372036854775808 tot 9223372036854775807); het aantal karakters is niet interessant.

Floats heten nu real's en double's; die herken je misschien aan de php functies is_real() en is_double(); synoniemen voor is_float().

In bijvoorbeeld phpPgAdmin (de "phpMyAdmin" van PostgreSQL) vind je de volgende types in het dropdown lijstje. Ik weet het, dat is irritant, je ziet liever gewoon "varchar" in plaats van "character varying(n)", en liever "int" dan "integer", maar het is niet anders, wen er maar aan:



Name Aliases Description
bigint int8 signed eight-byte integer
bigserial serial8 autoincrementing eight-byte integer
bit fixed-length bit string
bit varying(n) varbit(n) variable-length bit string
boolean bool logical Boolean (true/false)
box rectangular box in the plane
bytea binary data
character varying(n) varchar(n) variable-length character string
character(n) char(n) fixed-length character string
cidr IPv4 or IPv6 network address
circle circle in the plane
date calendar date (year, month, day)
double precision float8 double precision floating-point number
inet IPv4 or IPv6 host address
integer int, int4 signed four-byte integer
interval(p) time span
line infinite line in the plane (not fully implemented)
lseg line segment in the plane
macaddr MAC address
money currency amount
numeric [ (p, s) ] decimal [ (p, s) ] exact numeric with selectable precision
path open and closed geometric path in the plane
point geometric point in the plane
polygon closed geometric path in the plane
real float4 single precision floating-point number
smallint int2 signed two-byte integer
serial serial4 autoincrementing four-byte integer
text variable-length character string
time [ (p) ] time of day
[ without time zone ]
time [ (p) ] time of day, including time zone
with time zone timetz
timestamp [ (p) ] timestamp date and time
[ without time zone ]
timestamp timestamptz date and time, including time zone
[ (p) ] with
time zone

http://www.postgresql.org/docs/7.4/interactive/datatype.html

MyISAM is bij minder-zwaar gebruikte databses een stuk sneller dan PostgreSQL. Echter zou ik MyISAM alleen aanraden als je voornamelijk met SELECTs op INDEXes werkt. De gemiddelde programmeur is helaas nog niet slim genoeg om zijn INDEXes goed te bepalen (terwijl dit een snelheidsvoordeel op kan leveren van, wat ik tot nu toe heb gezien, factor 300.000). De snelheid van MyISAM heb je in principe toch weinig aan; bij kleine websites maakt het niet uit dat de ene engine iets sneller is dan de ander, want je merkt het toch pas bij hevig gebruik.

Met transactions kun je meerdere query’s tegelijk uitvoeren en het resultaat pas zichbaar laten zijn (voor andere query’s en processen) als de query’s allemaal zijn uitgevoerd. Zo worden de query’s niet uitgevoerd als er onderweg iets mis gaat met bijvoorbeeld de twee query’s.

Even een citaat uit een ander PHPhulp artikel:

Het klassieke voorbeeld is die van de bank:

Stel dat ik 100EUR wil overmaken van mijn rekening naar die van jou; je zou dan het volgende kunnen uitvoeren in SQL:

UPDATE rekening SET saldo=saldo-100 WHERE naam=’Remco’
UPDATE rekening SET saldo=saldo+100 WHERE naam=’Jouw naam’

Maar.....stel nu dat er tussen die 2 queries iets misgaat, waardoor de 2e query niet wordt uitgevoerd, ik ben dan 100EUR lichter, maar jij hebt je geld niet. Het geld is dus verdwenen.

Met transactions zou het zo gebeuren:

## Start de transactie
START TRANSACTION;
UPDATE rekening SET saldo=saldo-100 WHERE naam=’Remco’;
UPDATE rekening SET saldo=saldo+100 WHERE naam=’Jouw naam’;

## Als we hier komen zijn beide queries uitgevoerd
## Maak de wijzigingen permanent
COMMIT;

De query “START TRANSACTION” geeft aan dat er een transactie begint. Vervolgens worden er 2 queries uitgevoerd en volgt uiteindelijk de “COMMIT”, als er tussendoor iets fout gaat zal de COMMIT niet worden uitgevoerd, de queries zullen dus niet worden verwerkt. Uiteraard is dit geheel afhankelijk van hoe je script in elkaar steekt.

http://www.phphulp.nl/php/tutorials/3/274/503/

Als je een grote of veelgebruikte database draait dat kan het voorkomen dat twee mensen op hetzelfde moment een row updaten of deleten. Dit kan tot grote problemen leiden. Daarom kun je in MySQL een tabel locken. Maar dan kan de hele tabel niet meer beschreven worden totdat de query is uitgevoerd. PostgreSQL staat standaard zo ingesteld dat de row die jij aan het updaten of deleten bent altijd gelockt wordt en je dus zeker bent dat de data niet corrup wordt. Zo hoef je niet een hele tabel te locken, maar lock je slechts de relevante rij!

Je begrijpt dat dit bij databases die zeer betrouwbaar moeten zijn een heel groot voordeel ten opzichte van MySQL is.

Ik verwijs je naar de PostgreSQL handleiding voor meer informatie over het instellen van deze locks. Voor de meeste mensen zal dit waarschijnlijk niet interessant zijn omdat het al goed ingesteld staat.

http://www.postgresql.org/docs/7.2/static/locking-tables.html

Stored procedures zijn ‘opgeslagen procedures’. Het zijn stukjes programmatuur die je opslaat in je databasesysteem en die uitgevoerd worden als er een event plaatsvindt. Zo kun je een procedure maken die bij elke INSERT gebruikt wordt. Dan wordt er bijvoorbeeld gecontroleerd of een gegeven klopt en de afhandeling geregeld. Het voordeel is dat je dit niet meer in je applicatie hoeft te verwerken en dat het meestal snelheidvoordeel oplevert.

Een procedure kan in verschillende Procedure Languages geschreven worden. Veelgebruikt bij PostgreSQL is PL/pgSQL.

Voor de syntax en werking van stored procedures verwijs ik je naar:

http://www.postgresql.org/docs/8.1/interactive/plpgsql.html

- De datatypes wijken iets af. De datatypes die PostgreSQL gebruikt worden echter ook gebruikt door bijvoorbeeld Oracle, dus eigenlijk wijkt MySQL af. Je hebt geen (#) achter INTs en BIGINTs, omdat deze toch altijd uit hetzelfde aantal bytes bestaat.
- Gebruik geen backticks om je kolom- en tabelnamen (`tabel`), dit is slechts een dom cadeautje van MySQL
- Gebruik geen != in je query’s, maar gewoon <>
- PostgreSQL geeft (gelukkig) een error terug als je bijvoorbeeld een string van 40 karakters in een VARCHAR(32) probeert te laden.

Veel informatie die ook opgaat voor PostgreSQL kun je vinden in het artikel op PHPhulp van Remco van Arkelen over InnoDB: http://www.phphulp.nl/php/tutorials/3/274/


Succes met PostgreSQL!