postgres 15 - ghdrako/doc_snipets GitHub Wiki

Partial foreign key updates in referential integrity triggers

support partial foreign key updates in referential integrity triggers!

This is useful for schemas that use a denormalized tenant id across multiple tables, as might be common in a multi-tenant application:

    CREATE TABLE tenants (id serial PRIMARY KEY);
    CREATE TABLE users (
      tenant_id int REFERENCES tenants ON DELETE CASCADE,
      id serial,
      PRIMARY KEY (tenant_id, id),
    );
    CREATE TABLE posts (
        tenant_id int REFERENCES tenants ON DELETE CASCADE,
        id serial,
        author_id int,
        PRIMARY KEY (tenant_id, id),
        FOREIGN KEY (tenant_id, author_id)
          REFERENCES users ON DELETE SET NULL
    );

This schema has a problem. When you delete a user, it will try to set both the tenant_id and author_id columns on the posts table to NULL:

    INSERT INTO tenants VALUES (1);
    INSERT INTO users VALUES (1, 101);
    INSERT INTO posts VALUES (1, 201, 101);
    DELETE FROM users WHERE id = 101;
    ERROR:  null value in column "tenant_id" violates not-null constraint
    DETAIL:  Failing row contains (null, 201, null).

When we delete a user, we really only want to clear the author_id column in the posts table, and we want to leave the tenant_id column untouched. The feature I added is a small syntax extension to support doing exactly this. You can provide an explicit column list to the ON DELETE SET NULL / ON DELETE SET DEFAULT actions:

    CREATE TABLE posts (
        tenant_id int REFERENCES tenants ON DELETE CASCADE,
        id serial,
        author_id int,
        PRIMARY KEY (tenant_id, id),
        FOREIGN KEY (tenant_id, author_id)
          -- Clear only author_id, not tenant_id
          REFERENCES users ON DELETE SET NULL (author_id)
          --                                  ^^^^^^^^^^^
    );

I initially encountered this problem while converting a database to use composite primary keys in preparation for migrating to Citus [2], and it required adding custom triggers for every single foreign key we created. Now it can be handled entirely by Postgres!

Logical replication improvements

MERGE(https://www.postgresql.org/docs/15/sql-merge.html)

The MERGE operation it's replacing is something like "SELECT, followed by UPDATE/INSERT".Implicit in that sequence is transmitting the selected rows over the network, and buffering the rows in-memory on the client side.With MERGE, you eliminate the network stress, and push the burden of managing the rows in-memory onto the postgres server.That's quite nice if you have beefy operations and want to keep the services/jobs running those operations lean. You can do them individually yes, but you can’t do INSERT and UPDATE from the same SELECT CTE.Before, you’d have to either load the data in the client side or duplicate the CTE across two statements in a transaction.

You can, like this:

    WITH results as (
      SELECT ...
    ),

    inserted_rows AS (
      INSERT ...
      SELECT * FROM results
      ...
    ),

    deleted_rows AS (
      DELETE ...
      USING results
      ... 
    )

    UPDATE ...
    FROM results

security invoker views

CREATE VIEW viewname with(security_invoker=true) AS SELECT query;

Sort improvments

Adding CSV and JSON log format

https://www.postgresql.org/docs/15/runtime-config-logging.html

Parralel distinct