pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedCOPY documentation with regard to references constraints
4+ messages / 3 participants
[nested] [flat]
* COPY documentation with regard to references constraints
@ 2024-10-31 11:38 Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 4+ messages in thread
From: Bruno Wolff III @ 2024-10-31 11:38 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
I was unable to find any documentation clarifying if using COPY to load a
table with rows referencing others rows in the same table, whether I
had to worry about ordering of the input.
What I found (in 17) is that even if the references constraint wasn't
set as deferrable, I could have rows for children before their parents
in the COPY data and things still worked.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: COPY documentation with regard to references constraints
@ 2024-10-31 12:15 Dominique Devienne <ddevienne@gmail.com>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 1 reply; 4+ messages in thread
From: Dominique Devienne @ 2024-10-31 12:15 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; pgsql-general@lists.postgresql.org
On Thu, Oct 31, 2024 at 1:04 PM Bruno Wolff III <bruno@wolff.to> wrote:
> I was unable to find any documentation clarifying if using COPY to load a
> table with rows referencing others rows in the same table, whether I
> had to worry about ordering of the input.
> What I found (in 17) is that even if the references constraint wasn't
> set as deferrable, I could have rows for children before their parents
> in the COPY data and things still worked.
AFAIK, DEFERRABLE is for temporary violations crossing statement boundaries,
to postpone the enforcement of FKs at COMMIT time of the transaction.
While a single COPY is just one statement, so whether a temporary violation
for self-referential FKs would occur would be implementation-dependent,
and thus "neither logical" nor predictable.
Applies to other statement types too.
But of course, more authoritative answers will come I'm sure. --DD
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: COPY documentation with regard to references constraints
@ 2024-10-31 12:20 Bruno Wolff III <bruno@wolff.to>
parent: Dominique Devienne <ddevienne@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2024-10-31 12:20 UTC (permalink / raw)
To: Dominique Devienne <ddevienne@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Thu, Oct 31, 2024 at 13:15:34 +0100,
Dominique Devienne <ddevienne@gmail.com> wrote:
>
>AFAIK, DEFERRABLE is for temporary violations crossing statement boundaries,
>to postpone the enforcement of FKs at COMMIT time of the transaction.
>
>While a single COPY is just one statement, so whether a temporary violation
>for self-referential FKs would occur would be implementation-dependent,
>and thus "neither logical" nor predictable.
>
>Applies to other statement types too.
Thanks. I needed to look more carefully under deferrable where it says
the following:
This controls whether the constraint can be deferred. A constraint that is
not deferrable will be checked immediately after every command.
That is pretty clear.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: COPY documentation with regard to references constraints
@ 2024-10-31 13:08 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2024-10-31 13:08 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; pgsql-general@lists.postgresql.org
On Thu, 2024-10-31 at 06:38 -0500, Bruno Wolff III wrote:
> I was unable to find any documentation clarifying if using COPY to load a
> table with rows referencing others rows in the same table, whether I
> had to worry about ordering of the input.
I cannot find it in the documentation either, but since COPY is a single
statement and foreign key constraints are checked at the end of the
statement, it should work fine.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-10-31 13:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-31 11:38 COPY documentation with regard to references constraints Bruno Wolff III <bruno@wolff.to>
2024-10-31 12:15 ` Dominique Devienne <ddevienne@gmail.com>
2024-10-31 12:20 ` Bruno Wolff III <bruno@wolff.to>
2024-10-31 13:08 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox