Google+ Followers

woensdag 23 december 2015

Python multithreading in een notendop

Een van de vele redenen waarom ik PHP heb laten vallen voor Python is dat dingen die in PHP praktisch onmogelijk waren in Python een peulenschil zijn.

Een van die dingen is multithreading; een taak verdelen over meerdere CPU's.

Onderstaande is een tekstboek voorbeeld van multithreading in Python voor Python3. Knipt en plak en roep aan met python3.4 geplaktscript.py


#!python3

"""
Multi-threading werkt met een thread pool en een data-queue.
De thread pool wordt gevuld met een aantal 'workers' die het echte werk doen,
zij halen de data waar ze mee moeten werken uit een 'queue'.
De workers gaan door tot ze geen data meer in de queue zien en als alle workers klaar zijn
stopt het script.

Zoals het script nu is maakt het vier workers aan en vult het de queue met 20 items.
De 'do_work' method wacht .1 seconde dus de twintig items in de queue worden uitgevoerd in (20/4)*.1=0.5 seconde.
Verander het aantal workers van 4 naar 10 en zie dat het script nu klaar is in 0.2 seconde (20/10)*.1=0.2

Noot: je kunt dit niet ongestraft blijven opschalen. Als het werk dat je doet erg CPU-intensief is dan kun je niet meer workers opstarten
dan je cores hebt, anders gaan de taken alsnog elkaar in de weg zitten en dan helpt het niet meer.
Op een dedicated machine kun je gaan tot cores-1 workers, zodat er een één core vrij blijft voor het moederprocess en overige taken.

"""
import threading
from queue import Queue
import time

q = Queue()

# Maak een lock object om de uitvoer te synchroniseren, is alleen nodig voor dit voorbeeld.
lock = threading.Lock()


def do_work(item):
    """
    De do_work method simuleert het verwerken van de data uit de queue
    Hij wacht nu alleen 0.1 seconde.
    """
    time.sleep(.1)

    # Zorg dat de threads op elkaar wachten bij het printen.
    with lock:
        print(threading.current_thread().name, item)


def worker():
    """
    De worker method haalt data uit de queue en laat de data verwerken door de 'do_work' method,
    dat zou dus ook een method in een class van je applicatie kunnen zijn.
    """
    # Ga door tot de queue leeg is.
    while True:
        # Haal de volgende taak uit de wachtrij
        item = q.get()

        # Verwerk de data.
        do_work(item)

        # Sluit de taak.
        q.task_done()


def main():
    # Maak een thread-pool met een aantal threads die de 'worker' method gaan aanroepen.
    for i in range(4):
        t = threading.Thread(target=worker)
        t.daemon = True  # thread stopt als het moederproces stopt.
        t.start()

    # Vul de queue met data waar de workers mee gaan werken.
    for item in range(200):
        q.put(item)

    # Noteer de huidige tijd
    start_time = time.perf_counter()
    # Start de thread-pool
    q.join()
    # Print de verstreken tijd
    print('time:', time.perf_counter() - start_time)


if __name__ == '__main__':
    main()

woensdag 9 december 2015

CSV inlezen met Python

Python heeft natuurlijk CSV functies, maar in tegenstelling tot PHP heeft Python ook standaard een Sniffer class die het formaat van een CSV bestand kan analyseren. Dat betekent dat je voor goed-geformatteerde CSV bestanden niet meer hoeft op te  geven welke scheidingsteken en welke quote er wordt gebruikt.

Een voorbeeldje:

import csv


class MyCSVReader():
    @staticmethod
    def read(filename):
        with open(filename, 'r') as csvfile:
            # Detecteer het scheidingsteken en de gebruikte quotes.
            dialect = csv.Sniffer().sniff(csvfile.read(1024))

            # Breng de filepointer van het bestand terug naar het begin
            # zodat het niet opnieuw geopend hoeft te worden.
            csvfile.seek(0)

            # Maak een csv reader met het gevonden dialect
            my_reader = csv.reader(csvfile, dialect)

            # Sla de header over
            next(my_reader)

            # Lees de rest van het bestand.
            rows = []
            for row in my_reader:
                # Print de eerste twee kolommen en vat kolommen 3 t/m 6 (2 t/m 5) samen in een string die PostgreSQL begrijpt als een "int[][]".
                rows.append([row[0], row[1], '{' + ','.join(row[2:5]) + '}'])
            return rows


def main():
    print('-'*20)
    rows = MyCSVReader.read('komma_tick.csv')
    for r in rows:
        print(r)

    print('-'*20)
    rows = MyCSVReader.read('puntkomma_dubbelquote.csv')
    for r in rows:
        print(r)


# Vertel de commandline-interpreter van Python dat hij moet beginnen met uitvoeren bij de main() functie.
if __name__ == '__main__':
    main()


"""
Inhoud van "puntkomma_doublequote.csv":
"greeting";"test number";"value a";"value b";"value c";"value d"
"hello";1;7;9;6;4
"hi";1;5;8
"goodbye";87;8;7;9;2

Inhoud van "komma_tick.csv"
'greeting','test number','value a','value b','value c','value d'
'hello',1,7,9,6,4
'hi',1,5,8
'goodbye',87,8,7,9,2
"""

donderdag 12 november 2015

Why will OR DIE() not just... well, die?

"or die()" is possibly the stupidest thing in PHP. Every experienced PHP programmer will tell you that it's a bad idea and that PHP supports exceptions. Pretty much every other language has a structure for stopping a script with some sort of ternary OR operator, but you rarely see it used.

The problem with "or die()" is of course that 'die()' literally stops the script, right there and then. But in just about every case I've ever seen, the programmer did not want that, he wanted to stop further processing.

What's the difference? When you "stop further processing", you simply skip that part of the script that you cannot execute because of the error. All the code that is not affected by the error is still executed. This is important because "the rest of the code" includes the code that reports the error that was encoutered. If you just stop the script then no output is generated and your visitor will see a blank page that does not even tell him that there was an error. He cant report the error to you, he can't continue to a different page, he will press F5 to see if the page just failed to load, and when it doesn't he will go to a different website.

Not stopping the script also prevents you from corrupting your data. For example; if your script opens a file to write new content to it, you will typically (not correctly, but typically) open the file, reset it and then start processing and writing new content. If the orocessing fails and you stop the script outright, you will be left with an incomplete file, which breaks other processes. Brilliant.

When you stop further processing, you abort the processing and writing of the new content, and you will logically come to a point where you wonder ök, so what do I do with the fileif I can't update it?" and then you take the proper route where you write the new content to a new file and do a delete/rename once you have confirmed that the new file is correct.

There is no need for or-die() and it really doesn't matter if you think that it's easy to sue during debugging, because you *will* forget a couple of these statements and the *will* break your data.

Yeah, it's won't hapen to you, of course not, untill it does.

Use exceptions. Start your script with proper error handling, it will save your bacon.

woensdag 11 november 2015

PHP forums, worden ze ooit volwassen?

PHPHulp.nl:

Iemand stelt een vraag: "Als ik een selectbox maak met de namen van mijn gebruikers erin dan worden de spaties vervangen door underscores, waardoor ik na het submitten de namen niet meer kan terugvinden in de database. Hoe komt dat?"

De oplossing die het PHP forum voorstelt: "dan moet je het id gebruiken, niet de naam".

Huh? Als spaties niet goed doorkomen moet je iets gebruiken dat geen spaties bevat? Waarom niet gewoon uitzoeken naar waarom die spaties worden vervangen? Want dat is blijkbaar niet verwacht en niet gewenst. *Dat* is dus het probleem.


Maar moet je opletten wat er wordt gezegd als ik dat voorstel: "dankzij de tips die hij van ons heeft gekregen is hij nu wel een stuk verder".

Ja, een stuk verder in de problemen want de data wordt nog steeds verkeerd doorgegeven en de enige indicatie die hij had dat het *fout gaat* is door jullie brilliante voorstel nu onzichtbaar geworden. Goed werk hoor!


Dit oogkleppen, kop in het zand, als ik het niet zie dan is het er niet, gedrag is kenmerkend voor de PHP community. Nadenken lijkt verboden, oorzaken mogen niet worden aangepakt, elk probleem moet worden opgelost via een vorm van symptoombestrijding. Heb je een undefined-variable warning? Dan zet je gewoon je error_reporting lager, want wat voor kwaad kan een undefined variable nou helemaal? Kan je datase iets niet? Dan doe je het gewoon in een scriptje, dat is toch sneller.


"Werken met het id is de netste manier".

Nee, het is de manier die jij hebt aangeleerd gekregen van iemand anders die het zo deed. Een natural-primary-key is het netst en dat deed de poster ook; hij werkte een gebruiker bij aan de hand van zijn gebruikersnaam, zodat je altijd zeker weet dat je de juiste gebruiker aanspreekt. Een surrogate primary key kan op elk moment veranderen, zie de boeken van Joe Celko. Ik heb dat zelf ook meegemaakt bij webshop waarvan een van de leveranciers die een dump/restore had gedaan van zijn database, en bij de restore  de id kolom van de producttabel had overgeslagen zodat alle producten een nieuw id kregen. Gelukkig synchroniseerde de webshop op EAN code plus SKU, maar blijkbaar hebben andere webshops dagenlang de producten verkocht tegen verkeerde prijzen.


Natuurlijk, een surrogate primary key heeft zo z'n voordelen en in de alledaagse praktijk van de gemiddelde website zul je er geen last van hebben, maar *dat was het probleem niet*.

Als je auto niet wil starten als het koud is dan ga je toch ook niet met de fiets tot het weer zomer is?

Klinkt dit gefrustreerd? Vastwel, want ik wordt kenttergek van die houding.

Tuurlijk PHP is bij uitstek een taal voor beginners, maar als je constant wordt omringd door mensen die het zelf ook niet begrijpen en je voorzien van de domste adviezen, dan kom je ook nooit ergens.

vrijdag 6 november 2015

Waarom ik PostgreSQL leuker vindt dan MySQL: Snel testdata genereren.

Stel je wilt een query testen en je hebt een tabel nodig met 40.000 records. In MySQL kun je dat het snelst doen door een tabel te maken met één record en vervolgens dat record te kopieren via INSERT INTO tabel SELECT * FROM tabel; wat twee records oplevert en als je het nog eens doet 4 dan 8,16 etc, tot je na 15 keer op 32.000 zit. Probleem: dan heb je 32.000 dezelfde records, dus je moet in je SELECT ook nog iets doen om de inhoud van de data te veranderen zodat je data een beetje zinnig is voor een performance test. In elk van de 15 iteraties moet je die data weer bijstellen en gedoe gedoe gedoe. Het komt dan ook veel voor dat programmeurs een scriptje schrijven dat 40.000 rijen aanmaakt, wat tijd kost om te schrijven en uit te voeren.

In PostgreSQL kan het allemaal een stuk eenvoudiger, zoals gewoonlijk, via GENERATE_SERIES().

De GENERATE_SERIES() functie doet wat de naam zegt; het genereert een serie. Dat kan een serie getallen zijn:

SELECT GENERATE_SERIES(5, 20, 1);

Genereert een serie van 5 tot en met 10  in stapjes van 1.


Het kan ook met datums:

SELECT GENERATE_SERIES('2010-01-01'::timestamptz, '2010-01-10'::timestamptz, interval '1 hour');

Dat genereert een lijst van datums beginnende op 2010-01-01 00:00:00 en eindigend op 2010-01-10 00:00:00, in stapjes van één uur.


Het aanmaken van 40.000 records is hiermee ineens kinderspel:

INSERT INTO tabel SELECT id  FROM GENERATE_SERIES(1,40000) AS id;

En als elk record een eigen datum moet krijgen dan kun je die ook laten berekenen via het id:

INSERT INTO tabel SELECT id, '2010-01-01'::TIMESTAMP + id * INTERVAL '1 hour'
FROM GENERATE_SERIES(1,40000) AS id;

Om het iets realistischer te maken kun je er met RANDOM() nog wat willekeur in brengen:
INSERT INTO tabel SELECT id, '2010-01-01'::TIMESTAMP + RANDOM() * INTERVAL '1 month'
FROM GENERATE_SERIES(1,40000) AS id;

Deze vult de 40.000 records met willekeurige datums die vallen tussen 2010-01-01 00:00:00 en 2010-02-01 00:00:00.


Willekeurige strings toevoegen gaat kan natuurlijk ook. Je kunt bijvoorbeeld volledig willekeurige strings opbouwen uit een characterset:

SELECT id
, '2010-01-01'::TIMESTAMP + RANDOM() * INTERVAL '1 month'
, (SELECT string_agg(x, '')
  FROM (
    SELECT substr('          abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', floor(random() * 46)::integer,1)
    FROM generate_series(1, (140*random())::int + id * 0)
  ) AS y(x)) as g
FROM GENERATE_SERIES(1,40000) AS id;

Dit ziet er ingewikkeld uit maar de substr() pakt gewoon 1 letter uit de lange string waar het hele alphabet in staat, met tien spaties om wat meer kans op een spate te geven. De 140*random() zorg voor een willekeurige lengte en de 'id*0' is een truukje om te zorgen dat de subquery niet wordt weg-geoptimaliseerd door de queryplanner.

Het uitvoeren van die laatste query duurt op mijn Intel -i7 laptop 1.8 seconden, dus snel genoeg om onderdeel te maken van een unittest.

donderdag 5 november 2015

Input validatie en query-escaping

Onlangs had ik op phphulp een discussie met iemand die de uitspraak deed dat "een waarde die door je input-validatie heen komt in principe niet ge-escaped hoeft te worden in de query" .


Wat hij bedoelde, zo bleek later, was dat getallen geen tekens bevatten die behandeld moeten worden om in een query gebruikt te kunnen worden. Ergo; als je een input hebt kunnen valideren als zijnde een getal, dan is escapen voor die waarde niet strikt noodzakelijk.


Strict genomen is dat waar; je hoeft getallen niet te bewerken om ze in een query te zetten.
Maar wat heb je aan deze kennis?

Helemaal niets.






Zie hoe ik het woord getallen overal benadruk: de stelling gaat alleen op voor
getallen, niet voor strings of expressies die uit meer dan alleen cijfers bestaan. Het is niet mogelijk om een achternaam zo te valideren dat er alleen waarden uit komen die veilig zijn in een query. Dat zou Jaques 'd Ancona niet leuk vinden.


Daarbij valideer je de data voordat je de data gaat bewerken en tijdens die bewerking kan de gevalideerde data zo worden aangepast dat hij niet meer veilig is voor een query.

Tot slot is er altijd de mogelijkheid dat de data die de query ingeduwd wordt niet eens uit de validatie vandaankomt, een simpele tikfout in de naam van een variabele en je zit ongevalideerde data in te voeren.


In de praktijk zul je dus altijd moeten escapen, ongeacht welk type data je validatie zegt te hebben gevonden. Om die reden hebben databases het principe van het prepared-statement bedacht. Bij een prepared statement geef je de query syntax en de waarden die je in de query wilt gebruiken apart op, zodat de queryparser niet zelf hoeft uit te zoeken waar de data begint en eindigt.

"Jamaar, hij zegt toch ook 'in principe'"?

Klopt, en of je begrijpt wat hij daarmee bedoelt hangt helemaal van jou af. Er zwerven op het net heel wat PHP-classes rond die de data die naar een query wordt gestuurd door dingen als is_numeric heen halen om te bepalen of ze wel of niet moeten escapen en quoten. Waarom? Omdat ze ooit hebben gehoord dat getallen "in principe" niet ge-escapet of gequote hoeven te worden. Er worden potentiele beveiligingsgaten ingebouwd door dit soort slechtgeformuleerde uitspraken.

"Wat is dan het nut van input-validatie?"

Het nut van input validatie staat buiten kijf, het is *de* manier om te zorgen dat de data die je krijgt aangeleverd zinnig is. Als je een postcode verwacht en er staat "hallo hier Hilversum" in, dan kun je de verdere verwerking overslaan want de data is onzin.

Maar input validatie heeft *niets* te maken met queries of escaping. het is puur en alleen bedoeld als eerste controle om te zien of je data is wat je verwacht.

woensdag 28 oktober 2015

Echte wereld PostgreSQL trigger recursie

Elke zoveel tijd kom je in de situatie waarin een trigger op een tabel een update moet doen van de tabel zelf. Gevolg: de trigger triggert zichzelf en de boel komt in een oneindige lus.

Gelukkig is daar pg_trigger_depth(), die simpelweg de waarde nul heeft als de trigger afgaat door een direct query op de tabel. Als de trigger wordt aangeroepen door een actie die is veroorzaakt door een trigger, dan wordt pg_trigger_depth() verhoogd met de nesting van de triggers.

Deze controle kun je in een WHEN clausule zetten zodat de hele trigger alleen wordt uitgevoerd als pg_trigger_depth() daadwerkelijk nul is:

CREATE TRIGGER trg_product_update
  BEFORE UPDATE
  ON table_x FOR EACH ROW
  WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE trigger_function_foo();

Als de procedure 'trigger_function_foo()' een update op table_x veroorzaakt dan is pg_trigger_depth() niet meer nul en doet de trigger niets. Recursie getopt.

zaterdag 12 september 2015

Why I like PostgreSQL more than MySQL: OFFSET can use any expression

In MySQL, a regular query cannot use anything other than a hardcoded integer value for OFFSET.

This means that you cannot OFFSET by any value that comes from the database itself, without fetching that value separately and injecting it into a new query.

For example; if you want to fetch a quote-of-the-day you would order the records by some value and then just SELECT with an OFFSET that is de modulo of the day-of-year and the number of quotes in the table.

In MySQL that requires two queries.

In PostgreSQL you can just do what you need to do:

-- Create a table to play with
DROP TABLE IF EXISTS foo;
CREATE TEMPORARY TABLE foo (spreuk text);
INSERT INTO foo VALUES ('beter een lege dop dan helemaal geen dop'), ('beter een vogel in de hand, dan de lucht van tien.'),('Wie het laatst lacht heeft het minst gevoel voor humor.');

-- Fetch the quote!
SELECT * FROM foo OFFSET FLOOR( EXTRACT('doy' FROM now())::bigint % (SELECT COUNT(*) FROM foo)) LIMIT 1;

zondag 30 augustus 2015

Echte wereld PostgfreSQL: sorteer-kolom updaten vanuit een array.

Om gebruikers de mogelijkheid te geven om zelf de sorteervolgorde van records te bepalen kun je een integer kolom toevoegen waar je dr volgorde in zet:

DROP TABLE IF EXISTS foo;

CREATE TABLE foo (id int, naam text, volgorde int);
INSERT INTO foo VALUES (1,'hoofdstuk 4',1),(2,'hoofdstuk 5',2),(3,'hoofdstuk 2',3),(4,'hoofdstuk 3',4),(5,'hoofdstuk 1',5);
SELECT * FROM foo ORDER BY volgorde ASC;

Als je ze nu opvraagt dan klopt er niets van de hoofdstuk volgorde:

SELECT * ROM foo ORDER BY volgorde:
id - naam - volgorde
5;"hoofdstuk 1";1
3;"hoofdstuk 2";2
4;"hoofdstuk 3";3
1;"hoofdstuk 4";4
2;"hoofdstuk 5";5

Om de volgorde aan te passen kun je iets als een drag-drop interface maken en wanneer je die data submit dan krijgt je een array met de nieuwe volgorde van de id's: "5,3,4,1,2".

En nu komt de lol: PostgreSQL kan zon string zelf'verwerken, door het met UNNEST() om te zetten naar een setje records met één integer waarde:

SELECT * FROM UNNEST(5,3,4,1,2);

Maar dan weet je alleen de id's die moeten worden aangepast, hoe weet je dat record 5 een volgorde van 1 moet krijgen? Daar is windowing voor. Met ROW_NUMBER() krijg je votweg een 1 voor het eerste resultaat van UNNEST, en 2 voor het tweede, etc:

SELECT reorder_id , ROW_NUMBER() OVER () AS nieuwe_volgorde FROM unnest(ARRAY[5,3,4,1,2])

En door dat te joinen met de foo tabel kun je alles in één query doen:
WITH reorder AS (SELECT reorder_id , ROW_NUMBER() OVER () AS new_order FROM unnest(ARRAY[5,3,4,1,2]) AS reorder_id)  UPDATE foo SET volgorde=new_order FROM reorder WHERE id=reorder_id;

Zo simpel is het met PostgreSQL.

donderdag 30 juli 2015

Why I like PostgreSQL more than MySQL: PGStrom

MySQL has no custom extensions, which sucks because it limit's it's functionality to the things the development team think to be important, which upto now has been pretty much nothing. Yes, I hate the limts MySQL forces on you, deal with it.

PostgreSQL on the other hand welcomes new development and it's extension features mean that anyone can create an extension that every PostgreSQL user can deploy.

This gives rise to a lot of exciting features, like PGStrom

PGStrom connects postgresql's queryparser to your GPU to offload work to it, increasing the processing speed through parallelization, and freeing up precious CPU resources. Complex JOIN operations that basically require endless looping through data can be sped up by using a GPU that hasa much higher memory-bandwidth, and which has been designed to do simple boring tasks.

This means that you can increase the performance of a single server, saving you the hassle of scaling out to multiple servers or increasing the number of CPU's per server (which requires different hardware, more memory, more everything per server).

donderdag 9 juli 2015

Why I like PostgreSQL more than MySQL: Ctrl-C stops mysql-cli

If you type a query into MySQL's commandline tool and you mess up the quotes or something, the cursor will move to the next line and tell you that it's waiting for an ending-quote or whatever. The only way to get out of that is to complete the query syntax (which you cannot do because the first part is already wrong) or by pressing Ctrl-C which quits the tool altogether.

In PostgreSQL's commandline tool pressint Ctrl-C abandons the current query without executing it, so you can go into your query-history and fix the error.
To quit the tool, you must enter Ctrl-D, or the \d command.

zondag 24 mei 2015

Why I like PostgreSQL more than MySQL: NULLS LAST

Sometimes you want to sort values in c olumn that contains NULL values, and the NULL's appear at the wrong end of your list.
For example; sorting by date descending will put the null values first and then the dates starting at the newest date.

In MySQL you can "solve" this by ordering by the inverse of the value: ORDER BY -datefield, which means it can't use a index.

In PostgreSQL you can just ask for what you want: ORDER BY datefield NULLS LAST

Just one of many seemingly insicgnificant features that make life with your database so much simpler....

dinsdag 17 maart 2015

Waarom ik Python leuker vind dan PHP: zoek de oudste X bestanden.

Hoe haal je in PHP alle bestanden behalve de nieuwsste X op? Dat word een lus om door de lijst heen te wandelen en hem te sorteren, en een array_slice() om alles behalve de laatsste X terug te geven. Al met al een hoop gedoe voor iets wat zo simpel te definieren is.

In Python is het één regel:
files = sorted(glob.glob('*'), key=os.path.getctime)[:-1]
glob haalt een lijst van bestanden die voldoen aan een patroon (b.v. '*.jpg'). sorted() sorteert die lijst op basis van de inhoud van de getctime van elk item en de [:-1] geeft alles behalve het laatste item terug.


zaterdag 28 februari 2015

Why I like Python better than PHP: Bingo!

From the real world: How do you create a 6x6 array where every row contains six random numbers  in  the range 1-19, 20-29, etc.

In Python you pretty much do what the problem description says:
for each set of ten numbers pick a sorted set of 6 random numbers:

import random
data=[]
for t in range(10, 70, 10):
data.append(sorted(random.sample(range(t, 9+t), 6)))


In PHP things are much more complicated because the endless list of inconsistent behaviour.
range() creates an array of values, that's ok.
array_rand() picks random values from the array? No, it picks random KEYS from the array, which means you have to loop through the keys to get the values, which is bull. You can flip the array, but wtf?
So you shuffle the array, but shuffle modifies an array, it does not return a new array, so the rnge() must be materialized into a variable.
Ditto for the sorting.

Put it all together and you get this:

<?php
$data = [];
foreach (range(10, 70, 10) as $t) {
$tmp = range($t, $t+9);
shuffle($tmp);
$tmp = array_slice($tmp, 0, 6);
sort($tmp);
$data[] = $tmp;
}
?>

Ouch.

vrijdag 13 februari 2015

A lecture on NoSQL, by Eric Redmond

A lengthy talk that explains the differences between NoSQL and SQL, and the FUD that exists around them.



donderdag 12 februari 2015

Why I like PostgreSQL more than MySQL: DELIMITER ;

MySQL supports triggers, and that's good.

One common use for triggers is to copy data from a table to an audit table to track changes made to the table. Something like:

CREATE TRIGGER `trg_member_audit` AFTER UPDATE ON `member` FOR EACH ROW BEGIN
INSERT INTO member_audit
(sql_action, id, created, name, email)
VALUES
('UPDATE', NEW.id, NOW(), NEW.name, NEW.email);
END;


Great! Except, that doesn't work: syntax error at or near '' at line 6.

What that means is: "There is something missing on that line".

The missing bit is the END statement, but that's not  missing, it's right there on the next line. Yes, but the semicolon that ends the INSERT also ends the CREATE statement. MySQL can see the CREATE and the BEGIN, but it is not smart enough to look for the END.

So how can you solve this problem? Well it's simple really; you just have to change the default query delimiter from a semicolon to something else that doesn't appear anywhere in your query. A double dollar sign for example. (sound familiar?) Then you replace the semicolon that ends the CREATE with the new delimiter, and then reset the delimiter back to a semicolon:






DELIMITER $$
CREATE TRIGGER `trg_member_audit` AFTER UPDATE ON `member` FOR EACH ROW BEGIN
INSERT INTO member_audit
(sql_action, id, created, name, email)
VALUES
('UPDATE', NEW.id, NOW(), NEW.name, NEW.email);
END$$
DELIMITER ;

Note that the DELIMITER statement does not end with a delimiter and that the delimiter is not quoted, that would be crazy...

How does PostgreSQL do this?
In PostgreSQL a trigger always executes a stored function and the CREATE FUNCTION statement has solved the problem by simply forcing you to put a delimiter at the beginning and at the end of the SQL that is part of the function:

CREATE FUNCTION myfunc RETURNS trigger AS
$$
BEGIN
  RAISE EXCEPTION 'This triggerfunction is empty' ;
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

The delimiter is usually just two dollar signs, but if your code itself contains a double dolllar sign you can add text between the dollarsigns, the interpreter will read the first delimiter and keep processing until it find the exact same delimiter again.

CREATE FUNCTION myfunc RETURNS trigger AS
$my-code$
BEGIN
  RAISE EXCEPTION 'This trigger has no $$ sign!' ;
  RETURN NEW;
END;
$my-code$
LANGUAGE plpgsql;

woensdag 28 januari 2015

Why I like PostgreSQL more than MySQL: conditional NOT NULL constraints

Sometimes you want some fields of a record to be filled out, but only if some condition is met. For example, if a user registers as a company, you'll want his company name.

In MySQL you can do this using a trigger, which works just fine but in PostgreSQL you can do it in a much more compact way, using CHECK constraints.

In this example, all records added to foo must meet the requirement that is_admin must be false if the firstname or lastname are left empty. If is_admin is true then the firstname and lastname may not be null.

DROP TABLE IF EXISTS foo;
CREATE TABLE foo (id INTEGER, firstname VARCHAR(100), lastname VARCHAR(100), is_admin BOOLEAN NOT NULL DEFAULT 'f');
ALTER TABLE foo ADD CONSTRAINT adminfields CHECK (is_admin = 'f' OR (is_admin='t' and firstname IS NOT NULL AND lastname IS NOT NULL));

These inserts meet the requirements:

INSERT INTO foo (id) VALUES (1);
INSERT INTO foo (id, firstname, lastname, is_admin) VALUES (1,'kees','pekinees','t');






And this one will fail:
INSERT INTO foo (id, is_admin) VALUES (2, 't');

You can expand on this by making sure the name is not just NOT NULL, but actually contains sane data, using a regexp.

The major advantage of CHECK over triggers is that the CHECK shows up in the CREATE statement of the table, giving you single overview of the table's behaviour.

Again; there is nothing really wrong with triggers, this is just more convenient.

zaterdag 24 januari 2015

Why I like PostgreSQL (more than MySQL): memcache

Memcache is a fast in-memory key/value store. It is often used as a cache for data that is slow to generate and that needs to be served often. Given that it's usually the database that is the "slowest" part of the system (I use quotes because it the database is usually also the part of the system that does most of the work, people tend to forget that in their hunt for "performance". I use quotes here because the word performance is very relative and incudes much more than getting a reply quickly)

But, saving database results anywhere other than the database creates an instant race condition. The data in memcached is must be considered outdated the moment it is stored, because from that moment on the data will not change when the source changes.

Enter PgMemcached.

PgMemcached is a loadable extension for PostgreSQL that enables access to a memcached server from within a query. It allows you to do something like:

SELECT memcache_server_add('127.0.0.1:11211');
SELECT memcache_set('foo','bar');
SELECT memcache_get('foo');

Which will return 'bar'.

But because it is a regular memcached server, you can now do this:

> telnet localhost 11211
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
> get foo
VALUE foo 0 3
bar
END

So, PostgreSQL can now read and write directly to your memcached server and share the data that your application is storing there. That opens up the next step:

DROP TABLE IF EXISTS tmp_news CASCADE;
CREATE TEMPORARY TABLE tmp_news (id INTEGER, title VARCHAR(100), body TEXT);
INSERT INTO tmp_news VALUES (1, 'Webber wins'),(2,'Alonso loses'),(3, 'Gingers have souls');
CREATE OR REPLACE FUNCTION update_news_cache() RETURNS TRIGGER AS 
$$
DECLARE
f TEXT;
junk RECORD;
BEGIN
WITH json_rows AS (SELECT ROW_TO_JSON(tmp_news) AS r FROM tmp_news ORDER BY id DESC LIMIT 10)
SELECT memcache_set('news_cache', JSON_AGG(r)::TEXT) FROM json_rows INTO junk;
  RETURN NEW;
END
$$
LANGUAGE PLPGSQL;

CREATE TRIGGER trg_update_news_cache AFTER INSERT ON tmp_news FOR EACH ROW EXECUTE PROCEDURE update_news_cache();

INSERT INTO tmp_news VALUES (4,'UFO sighted at Malibu');

SELECT memcache_get('news_cache');

So, adding a news item to tmp_news automaticallty updates a memcached value with a JSON array of the news items, for use in your application.
Critically, with this in place your news cache is updated regardles of how the newsitems were changed. It can be done by your application, or by a manual query, whatever changes the news will force an update of the cache. This in turn means that you don't have to check the validity of the cache anymore. if the value exists then it is up-to-date by definition (well, iunless you've changed it by other means...)


woensdag 21 januari 2015

Derived data: to store or not to store...

In database terminology, derived data are results that can be obtained by processing other data that is already in the database. Storing that data in the database is technically redundant and creates a race condition where the source data can have changed but the drived data has not yet been updated.

So the case seems simple: don't store derived data.

But of course it's not as simple as that.
Calculating the derived data takes time. It may not be much but it doesn't take much to be slower than reading a record and that slowdown is often compensated by caching, which is effectively just saving the data, but some in a volatile location instead of the database. That still leaves quite a lot of recalculation to be done.

Recalculation can also be enforced using triggers, so race conditions are in fact a rare occurance. If you design a model for a darts championship, do you store the name of the winner for each leg, or do you only store the score per dart and sum that every time you want to know the name of the winner of the tournament?

Re-calculating could also actually create a variation on a race condition, where a bugfix in the calculation can change the outcome. Why can that be a problem? Think of orders in a shop; the total value can always be calculated from the orderlines, but it must never ever change because that would give you a serious problem with the customer and lateron with the tax people. So if you find a bug in the calculation you should definately fix it for new orders, but you must keep a copy of the old calculaton around for the older orders. That does not sound like a good plan.

So in reality, a lot of the derived data is not actually derived data, it is just data that happens to have been calculated from other data. You also simply cannot always afford to calculate things in realtime because of performance or consistency reasons.

You really have to think about how the data is used and what the consequences would be of saving the data versus reacalculating it.

dinsdag 6 januari 2015

Why I like PostgrteSQL more than MySQL: transactions and magic commit.

MySQL's BEGIN command is actually a COMMIT;BEGIN; which means that you can commit a transaction without ever executing a COMMIT. That by itself is... well... not very good, but the problems don't end there.

The only reason why you use transactions is so that you can revert the data when you notice that you cannot complete the work. So, you'd expect the database to do a rollback whenever it encounters a problem through which it cannot complete the transaction.

MySQL does not support DDL inside transactions. You cannot create or drop tables for example. That by itself is not a problem, it's a complex piece of MVCC that is not easily implemented. But, instead of aborting the transaction and doing a rollback whenever the user does something that breaks the transaction, MySQL will actuallt commit the transaction. Yes, commit. So, you begin work, you do some things that work, you do something that cannot be handled, and suddenly your half-completed work is committed.

Tell me, in which universe is that a good idea?

And don't give me the "users wanted it this way" excuse, nobody wants a commit when there is an error, and if they did, people which database knowledge, which I thought MySQL's developers would have, should tell them no, and implement a rollback instead.

PostgreSQL will actually force an abort on error, you cannot even choose to continue after an error. PostgreSQL also supports DDL in MVCC, which is extremely handy when doing migrations and unittests.

maandag 5 januari 2015

Your database knows your data. Use it!

Case in point: an application that prints invoices. Some of these invoices have not been paid before the expiration date and must be printed in red.

That's easy enough; just write an IF statement in the application to compare the date the invoice was sent to the date it should have been paid on, and voila.

Except; that requires that the application has a function that can compare the dates that the database puts put. The database returns strings, so you must either SELECT the dates in a format that is purely numeric and in UTC, or one that requires no effort to convert into the programming language's objects.

Alternatively, you could use the query to *ASK* the database if the invoice has expired. No, not by running a separate query, but by augmenting the data that you already have in the table.

For example; if your table holds an id, price, paid, date_sent, and date_expired, you can do something like:

SELECT id, price,
  CASE WHEN NOW() > date_expired AND paid = FALSE
    THEN 1
    ELSE 0
  END AS expired
FROM ....

Now the data about the invoice contains a virtual-column called 'expired' which holds '1' for invoices that have expired, or '0' if they have not.

Stick this in a (materialized) view, and your application does not even need to know the businessrules about when and how invoices expire.


PS: Yes, of course, you could also run a cronjob at 00:00:00 that does this check and modifies the status of the invoice. That will work just fine if performance is an issue.