MIME-Version: 1.0
References: <5031a50c-aca7-4512-9478-95583606b5ff@Spark>
In-Reply-To: <5031a50c-aca7-4512-9478-95583606b5ff@Spark>
From: Bernice Southey <bernice.southey@gmail.com>
Date: Sat, 22 Nov 2025 13:39:47 +0000
Message-ID: <CAEDh4nwnPtRLtznUt5-onyJLzt2C9S1Lt0w3tYisQxJLhbDQEg@mail.gmail.com>
Subject: Re: FK from logged to unlogged table?
To: dolan@directdemocracysolutions.com
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAEDh4nwnPtRLtznUt5-onyJLzt2C9S1Lt0w3tYisQxJLhbDQEg%40mail.gmail.com>
Precedence: bulk

<dolan@directdemocracysolutions.com> wrote:
> Is it allowed to key from a logged table to an unlogged table?
I tried and it seems not:
create unlogged table u(i int primary key);
create table l(i int references u);
ERROR: constraints on permanent tables may reference only permanent tables

Interestingly it works in the other direction. I assume it's forbidden
because the loss of the unlogged table will break the constraint. I
expect you could simulate the foreign key with a trigger, but I'm not
sure you should.

> Is there a clean way to protect only the subset of records that are keyed?
I wonder if partitioning could help you. I believe it's possible to
have logged and unlogged partitions in the same table [1].

I'm sure you've explored other options for faster inserts [2].  Most
of the times I've wanted to use an unlogged table, I've decided it's
not worth the rescue effort.

Thanks,
Bernice

[1] https://www.postgresql.org/message-id/flat/ZiiyGFTBNkqcMQi_%40paquier.xyz
[2] https://www.postgresql.org/docs/current/populate.html