public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bernice Southey <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: Re: FK from logged to unlogged table?
Date: Sat, 22 Nov 2025 13:39:47 +0000
Message-ID: <CAEDh4nwnPtRLtznUt5-onyJLzt2C9S1Lt0w3tYisQxJLhbDQEg@mail.gmail.com> (raw)
In-Reply-To: <5031a50c-aca7-4512-9478-95583606b5ff@Spark>
References: <5031a50c-aca7-4512-9478-95583606b5ff@Spark>

<[email protected]> 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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: FK from logged to unlogged table?
  In-Reply-To: <CAEDh4nwnPtRLtznUt5-onyJLzt2C9S1Lt0w3tYisQxJLhbDQEg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox