Foreign Key to partitioned table – part 2

Previously I wrote about how to create foreign key pointing to partitioned table.

Final solution in there required four separate functions and four triggers for each key between two tables.

Let's see how fast it is, and if it's possible to make it simpler.

Continue reading Foreign Key to partitioned table – part 2

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

Waiting for 9.4 – ALTER TABLE … ALTER CONSTRAINT for FKs

On 28th of June, Simon Riggs committed patch:

ALTER TABLE ... ALTER CONSTRAINT for FKs
 
Allow constraint attributes to be altered,
so the default setting of NOT DEFERRABLE
can be altered to DEFERRABLE and back.
 
Review by Abhijit Menon-Sen

Continue reading Waiting for 9.4 – ALTER TABLE … ALTER CONSTRAINT for FKs

Waiting for 9.1 – INVALID FOREIGN KEYS

On 8th of February, Simon Riggs committed patch:

Extend ALTER TABLE TO allow FOREIGN KEYS TO be added WITHOUT initial validation.
FK constraints that are marked NOT VALID may later be VALIDATED, which uses an
ShareUpdateExclusiveLock ON CONSTRAINT TABLE AND RowShareLock ON referenced
TABLE. Significantly reduces LOCK strength AND duration WHEN adding FKs.
NEW state visible FROM psql.
 
Simon Riggs, WITH reviews FROM Marko Tiikkaja AND Robert Haas

Continue reading Waiting for 9.1 – INVALID FOREIGN KEYS

Performance gains from using foreign keys

Foreign keys are known for couple of things, but speeding up your system is not one of them. But sometimes, having them in place lets you make queries significantly faster.

How? Let me show you example I have seen lately (well, it's simplified example based on something much more convoluted, and definitely longer):

Continue reading Performance gains from using foreign keys