Accueil / Blog / Métier / Archives / Cheap debugging of PostgreSQL triggers in Django

Cheap debugging of PostgreSQL triggers in Django

Par Mathieu Leplatre publié 22/10/2012

Lately, we were hacking on PostgreSQL (PostGIS) triggers, and we quickly felt like debugging our code... Here is a cheap and quick way of printing out triggers variables and context through Django.

PostgreSQL server configuration

In postgresql.conf, adjust the minimum level of notice sent to the client :

client_min_messages = log

Note that this does not affect logging verbosity on server.

Catch messages in Django

For a specific model :

from django.db import connection

def save(self, *args, **kwargs):
    before = len(connection.connection.notices)
    try:
        super(Model, self).save(*args, **kwargs)
    finally:
        for notice in connection.connection.notices[before:]:
            print notice

Or globally, using post_save signals (can be verbose):

from django.db import connection
from django.db.models.signals import post_save

def show_notices(sender, instance, created, **kwargs):
    for notice in connection.connection.notices:
        print notice
post_save.connect(show_notices)

Let your trigger be talkative

You can basically print out values, arrays, functions results, records...

RAISE LOG '% has geom %', NEW.id, ST_AsEWKT(NEW.geom);

Will output something like LOG:  3 has geom SRID=4326;POINT(0 0).

FOR record IN SELECT * FROM table
LOOP
    RAISE LOG 'Found %', record;
END LOOP;

Will output something like LOG:  Found (a,b,c).

intersections_on_new := ARRAY[]::float[];
FOR pk IN SELECT ST_Line_Locate_Point(NEW.geom, (ST_Dump(ST_Intersection(other.geom, NEW.geom))).geom)
LOOP
    intersections_on_new := array_append(intersections_on_new, pk);
END LOOP;
RAISE LOG 'Intersects at %', intersections_on_new;

Will output something like LOG:  Intersects at {0.5,0.3}.

One more thing...

If you load your triggers source file through Django (like a post_migrate signal or so), and thus with psycopg2, you might face that nasty internal quirck :

postgresql_psycopg2/base.py", line 52, in execute
    return self.cursor.execute(query, args)
IndexError: tuple index out of range

This is due to % characters, that you have to escape, replacing them with %%.

ABONNEZ-VOUS À LA NEWSLETTER !
Voir aussi
Presentation of the latest Django CMS: WagtailPresentation of the latest Django CMS: Wagtail 22/07/2016

Wagtail is a quite recent Django CMS. However, its young age does not keep it from having a lot of ...

Wagtail : Utiliser le modèle Page ainsi que son Manager (partie 2)Wagtail : Utiliser le modèle Page ainsi que son Manager (partie 2) 12/07/2016

Le modèle Page contient plusieurs méthodes spécifiques à l'outil Wagtail. C'est également le ...

Wagtail : Créer ses modèles de type de contenu (partie 1)Wagtail : Créer ses modèles de type de contenu (partie 1) 05/07/2016

On commence à avoir l'habitude d'initialiser son CMS directement depuis une interface web, cela ...

Wagtail : Comment écrire les templates (partie 3)Wagtail : Comment écrire les templates (partie 3) 18/07/2016

Il n'y a pas de vue à proprement parlé dans Wagtail. Tout est en fait géré dans le modèle. ...

Générer des tuiles vectorielles sur mesure avec DjangoGénérer des tuiles vectorielles sur mesure avec Django 02/06/2016

Dans cet article nous allons voir comment générer dynamiquement des tuiles vectorielles ...