I have a mixed love/hate relationship with tests.
I hate writing them. I hate remembering to add them when I'm in the zone, and application code is flowing freely from the tips of my fingers.
But when I do add them, I absolutely love the ability to twist and replace the most core innards of application, and be able to tell that at least the sanity check of the code passes.
I love them even more when they prevent me for repeating some mistake/bug – i.e. when there is a bug, and I have tests (which clearly failed, as they didn't catch the bug), I add test for this specific bug, so I know that in future it will not happen again.
For a long time I've been fan of stored procedures (or functions) – of course not for everything, but where it makes sense.
Continue reading Test driven development for PostgreSQL
Every now and then there is someone on IRC, mailing lists, or private contact which asks about rules.
My answer virtually always is: don't use rules. If you think that they solve your problem, think again. Why?
Continue reading To rule or not to rule – that is the question
Dynamic updates of fields in NEW in PL/pgSQL
Today, on #postgresql on IRC, strk asked about updating fields in NEW record, in plpgsql, but where name of the field is in variable.
After some time, he sent his question to hackers mailing list. And he got prompt reply that it's not possible.
Well, I dare to disagree.
Continue reading Stupid tricks – Dynamic updates of fields in NEW in PL/pgSQL
On 20th of November Tom Lane committed patch by Takahiro Itagaki which adds interesting functionality:
ADD a WHEN clause TO CREATE TRIGGER, allowing a BOOLEAN expression TO be
checked TO determine whether the TRIGGER should be fired.
FOR BEFORE triggers this IS mostly a matter OF spec compliance; but FOR AFTER
triggers it can provide a noticeable performance improvement, since queuing OF
a DEFERRED TRIGGER event AND re-fetching OF the ROW(s) at END OF statement can
be short-circuited IF the TRIGGER does NOT need TO be fired.
Takahiro Itagaki, reviewed BY KaiGai Kohei.
Continue reading Waiting for 8.5 – conditional triggers
On 14th of October Tome Lane committed new patch from Itagaki Tahahiro:
Support SQL-compliant triggers ON COLUMNS, ie fire ONLY IF certain COLUMNS
are named IN the UPDATE's SET list.
Note: the schema of pg_trigger has not actually changed; we've just started
TO USE a COLUMN that was there ALL along. catversion bumped anyway so that
this commit IS included IN the history OF potentially interesting changes
TO system catalog contents.
Continue reading Waiting for 8.5 – TRIGGERS on columns
another new, cool feature commited by tom lane: “Support statement-level ON TRUNCATE triggers."
original patch was submitted by simon riggs, and tom commited it today/yesterday (depending on time zone).
Continue reading waiting for 8.4