Re: FK from logged to unlogged table?

<dolan(at)directdemocracysolutions(dot)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