Search code examples
partitioningpostgresql-9.3

Postgres: BEFORE UPDATE trigger

Description

In our environment (Postgres 9.3) we use extensive partitioning on dates. Additionally we use redirects to redirect INSERTs in the 'main' table to the corresponding child table (so due note that there actually is no data in the main table, all the data is in the child tables.

Problem

One of the processes is executing an UPDATE on the main table, but how could I redirect one of these UPDATEs to the correct child table?

So for instance if I say something simple as:

UPDATE transactions SET text = 'new text' WHERE id = 1 AND date = 201601;

That I redirect this UPDATE to the transactions_201601 partition?

Solution

  • Apparently it is not common practice to redirect your UPDATE queries to the correct table.

    By ensuring that the INDEXes are setup in a correct way you make sure that the UPDATE is executed on the right partition table. So all in all setting up correct indexes is the solution.