This question was asked at least twice on some support channel. Getting a row is trivial: select * from table where id = ?. But what about dependencies – the rows that this exported row references?
Decided to take a look at this task.
Continue reading How to get a row, and all of it's dependencies?
On 3rd of April 2019, Alvaro Herrera committed patch:
Support foreign keys that reference partitioned tables
Previously, while primary keys could be made on partitioned tables, it
was not possible to define foreign keys that reference those primary
keys. Now it is possible to do that.
Author: Álvaro Herrera
Continue reading Waiting for PostgreSQL 12 – Support foreign keys that reference partitioned tables
Previously I tested performance of pl/PgSQL coded foreign keys to partitioned table.
Now, let's see if I can make creation of them a bit easier.
Continue reading Foreign Key to partitioned table – part 3
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
One of the long standing limitations of partitions is that you can't have foreign keys pointing to them.
Let's see if I can make it possible to have some kind of constraint that would do the same thing as fkey.
Continue reading Foreign Key to partitioned table
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
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
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
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