Tag: triggers

How to limit rows to at most N per category – fix

A bit ago I wrote a blog post that was supposed to show how to keep number of rows in table to N per some category.

Unfortunately, I overlooked a problem related to concurrency.

Continue reading How to limit rows to at most N per category – fix

How to limit rows to at most N per category

The question was asked relatively recently on irc. And it proved to be non-trivial.

Surely, if you want to have one row per category (one address per user), it's trivial – add user_id column to addresses, make it unique, and we're done. But what if we want to allow five addresses? Or five thousands?

Let's see.

Continue reading How to limit rows to at most N per category

Waiting for PostgreSQL 14 – Provide the OR REPLACE option for CREATE TRIGGER.

On 14th of November 2020, Tom Lane committed patch:

Provide the OR REPLACE option for CREATE TRIGGER.
 
This is mostly straightforward.  However, we disallow replacing
constraint triggers or changing the is-constraint property; perhaps
that can be added later, but the complexity versus benefit tradeoff
doesn't look very good.
 
Also, no special thought is taken here for whether replacing an
existing trigger should result in changes to queued-but-not-fired
trigger actions.  We just document that if you're surprised by the
results, too bad, don't do that.  (Note that any such pending trigger
activity would have to be within the current session.)
 
Takamichi Osumi, reviewed at various times by Surafel Temesgen,
Peter Smith, and myself
 
Discussion: https://postgr.es/m/0DDF369B45A1B44B8A687ED43F06557C010BC362@G01JPEXMBYT03

Continue reading Waiting for PostgreSQL 14 – Provide the OR REPLACE option for CREATE TRIGGER.

Waiting for PostgreSQL 13 – Enable BEFORE row-level triggers for partitioned tables

On 18th of March 2020, Alvaro Herrera committed patch:

Enable BEFORE row-level triggers for partitioned tables
 
... with the limitation that the tuple must remain in the same
partition.
 
Reviewed-by: Ashutosh Bapat
Discussion: https://postgr.es/m/20200227165158.GA2071@alvherre.pgsql

Continue reading Waiting for PostgreSQL 13 – Enable BEFORE row-level triggers for partitioned tables

Waiting for PostgreSQL 10 – Implement syntax for transition tables in AFTER triggers.

Another one missed, quite a long time ago, too..:

On 4th of November 2016, Kevin Grittner committed patch:

Implement syntax for transition tables in AFTER triggers.
 
 
This is infrastructure for the complete SQL standard feature.  No
support is included at this point for execution nodes or PLs.  The
intent is to add that soon.
 
As this patch leaves things, standard syntax can create tuplestores
to contain old and/or new versions of rows affected by a statement.
References to these tuplestores are in the TriggerData structure.
C triggers can access the tuplestores directly, so they are usable,
but they cannot yet be referenced within a SQL statement.

Continue reading Waiting for PostgreSQL 10 – Implement syntax for transition tables in AFTER triggers.

Waiting for PostgreSQL 10 – Implement table partitioning.

I had two month delay related to some work, but now I can finally write about:

On 7th of December, Robert Haas committed patch:

Implement table partitioning.
 
Table partitioning is like table inheritance and reuses much of the
existing infrastructure, but there are some important differences.
The parent is called a partitioned table and is always empty; it may
not have indexes or non-inherited constraints, since those make no
sense for a relation with no data of its own.  The children are called
partitions and contain all of the actual data.  Each partition has an
implicit partitioning constraint.  Multiple inheritance is not
allowed, and partitioning and inheritance can't be mixed.  Partitions
can't have extra columns and may not allow nulls unless the parent
does.  Tuples inserted into the parent are automatically routed to the
correct partition, so tuple-routing ON INSERT triggers are not needed.
Tuple routing isn't yet supported for partitions which are foreign
tables, and it doesn't handle updates that cross partition boundaries.
 
Currently, tables can be range-partitioned or list-partitioned.  List
partitioning is limited to a single column, but range partitioning can
involve multiple columns.  A partitioning "column" can be an
expression.
 
Because table partitioning is less general than table inheritance, it
is hoped that it will be easier to reason about properties of
partitions, and therefore that this will serve as a better foundation
for a variety of possible optimizations, including query planner
optimizations.  The tuple routing based which this patch does based on
the implicit partitioning constraints is an example of this, but it
seems likely that many other useful optimizations are also possible.
 
Amit Langote, reviewed and tested by Robert Haas, Ashutosh Bapat,
Amit Kapila, Rajkumar Raghuwanshi, Corey Huinker, Jaime Casanova,
Rushabh Lathia, Erik Rijkers, among others.  Minor revisions by me.

Continue reading Waiting for PostgreSQL 10 – Implement table partitioning.

Waiting for 9.5 – Reduce lock levels of some trigger DDL and add FKs

On 5th of April, Simon Riggs committed patch:

Reduce lock levels of some trigger DDL and add FKs
 
Reduce lock levels to ShareRowExclusive for the following SQL
 CREATE TRIGGER (but not DROP or ALTER)
 ALTER TABLE ENABLE TRIGGER
 ALTER TABLE DISABLE TRIGGER
 ALTER TABLE … ADD CONSTRAINT FOREIGN KEY
 
Original work by Simon Riggs, extracted and refreshed by Andreas Karlsson
New test cases added by Andreas Karlsson
Reviewed by Noah Misch, Andres Freund, Michael Paquier and Simon Riggs

Continue reading Waiting for 9.5 – Reduce lock levels of some trigger DDL and add FKs

How I Learned to Stop Worrying and Love the Triggers

Some people are afraid of triggers.

Reasons for this are not really understandable for me, but I guess it stems from the fact that these are usually application developers, and not database admins. Or they encountered some kind of problem with triggers, and now they tend to think that triggers are inherently evil.

But they are not.

As virtually anything, triggers have some benefits, and some drawbacks. With a bit of thinking you can use them to do really cool things. But first you have to understand what exactly trigger is, how it works, and when to use which kind.

Continue reading How I Learned to Stop Worrying and Love the Triggers