Tag: trigger

Waiting for 9.3 – Event triggers

On 20th of July, Robert Haas committed patch:

Make new event trigger facility actually do something.
 
Commit 3855968f328918b6cd1401dd11d109d471a54d40 added syntax, pg_dump,
psql support, and documentation, but the triggers didn't actually fire.
With this commit, they now do.  This is still a pretty basic facility
overall because event triggers do not get a whole lot of information
about what the user is trying to do unless you write them in C; and
there's still no option to fire them anywhere except at the very
beginning of the execution sequence, but it's better than nothing,
and a good building block for future work.
 
Along the way, add a regression test for ALTER LARGE OBJECT, since
testing of event triggers reveals that we haven't got one.
 
Dimitri Fontaine and Robert Haas

This was preceded (two days earlier) by commit, also by Robert Haas, which stated:

Syntax support and documentation for event triggers.
 
They don't actually do anything yet; that will get fixed in a
follow-on commit.  But this gets the basic infrastructure in place,
including CREATE/ALTER/DROP EVENT TRIGGER; support for COMMENT,
SECURITY LABEL, and ALTER EXTENSION .. ADD/DROP EVENT TRIGGER;
pg_dump and psql support; and documentation for the anticipated
initial feature set.
 
Dimitri Fontaine, with review and a bunch of additional hacking by me.
Thom Brown extensively reviewed earlier versions of this patch set,
but there's not a whole lot of that code left in this commit, as it
turns out.

Continue reading Waiting for 9.3 – Event triggers

Waiting for 9.2 – Trigger Depth

On 25th of January, Alvaro Herrera committed patch:

Add pg_trigger_depth() function
 
This reports the depth level of triggers currently in execution, or zero
if not called from inside a trigger.
 
No catversion bump in this patch, but you have to initdb if you want
access to the new function.
 
Author: Kevin Grittner

Continue reading Waiting for 9.2 – Trigger Depth

Pagination with fixed order

Some time ago I wrote about getting fast pagination. While fast, it had some problems which made it unusable for some. Specifically – you couldn't get page count, and easily jump to page number N.

I did some thinking on the subject, and I think I found a way to make it all work. Quite fast. And with not big overhead. Let me show you.

Continue reading Pagination with fixed order

Waiting for 9.1 – triggers on views

On 10th of October, Tom Lane committed patch by Deal Rasheed, which adds triggers on views:

Support triggers on views.
 
This patch adds the SQL-standard concept of an INSTEAD OF trigger, which
is fired instead of performing a physical insert/update/delete.  The
trigger function is passed the entire old and/or new rows of the view,
and must figure out what to do to the underlying tables to implement
the update.  So this feature can be used to implement updatable views
using trigger programming style rather than rule hacking.
 
In passing, this patch corrects the names of some columns in the
information_schema.triggers view.  It seems the SQL committee renamed
them somewhere between SQL:99 and SQL:2003.
 
Dean Rasheed, reviewed by Bernd Helmle; some additional hacking by me.

Continue reading Waiting for 9.1 – triggers on views

Waiting for 9.1 – Reduced lock levels for ALTER TABLE

On 28th of July, Simon Riggs committed patch which:

Log Message:
-----------
Reduce lock levels of CREATE TRIGGER and some ALTER TABLE, CREATE RULE actions.
Avoid hard-coding lockmode used for many altering DDL commands, allowing easier
future changes of lock levels. Implementation of initial analysis on DDL
sub-commands, so that many lock levels are now at ShareUpdateExclusiveLock or
ShareRowExclusiveLock, allowing certain DDL not to block reads/writes.
First of number of planned changes in this area; additional docs required
when full project complete.

Continue reading Waiting for 9.1 – Reduced lock levels for ALTER TABLE

Test driven development for PostgreSQL

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

Profiling stored procedures/functions

One database that I am monitoring uses a lot of stored procedures. Some of them are fast, some of them are not so fast. I thought – is there a sensible way to diagnose which part of stored procedure take the most time?

I mean – I could just put the logic into application, and then every query would have it's own timing in Pg logs, but this is not practical. And I also believe that using stored procedures/functions is way better than using plain SQL due to a number of reasons.

So, I'm back to question – how to check which part of function takes most of the time?

Continue reading Profiling stored procedures/functions

Stupid tricks – Dynamic updates of fields in NEW in PL/pgSQL

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

CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – UPDATED 2010-03-03

UPDATE

Please read also about this change in Pg 9.1, and this change in Pg 9.2 posts, as they explain that since Pg 9.1 some of the limitations listed in this post are no longer there.

END OF UPDATE

Fight!

But more seriously – people tend to use various data types, and there have been some myths about them, so let's see how it really boils down.

First of all – All those data types are internally saved using the same C data structure – varlena.

Thanks to this we can be nearly sure that there are no performance differences. Are there no performance differences in reality? Let's test.

Continue reading CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – UPDATED 2010-03-03