Reject unsupported COPY FROM targets before analyzing WHERE clause

From: Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Reject unsupported COPY FROM targets before analyzing WHERE clause
Date: 2026-06-24 07:34:07
Message-ID: 84873FFF-4C50-4C85-9A36-62A01B860FF1@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

While testing “[21c69dc73] Disallow system columns in COPY FROM WHERE conditions”, I noticed an issue. This is not a correctness bug, but I think the user experience is not good.

See this repro:
```
evantest=# create sequence s;
CREATE SEQUENCE
evantest=# copy s from stdin where tableoid is null;
ERROR: system columns are not supported in COPY FROM WHERE conditions
DETAIL: Column "tableoid" is a system column.
```

Here, it reports an error about the WHERE clause. Okay, then I remove the WHERE clause:
```
evantest=# copy s from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> ddd
>> ^CERROR: cannot copy to sequence "s"
```

It only fails with the root problem, “cannot copy to sequence”, after I type in the content I want to copy. I think that is too late. A better user experience would be to raise the root error before checking the WHERE clause and before waiting for any input.

Similar masking can also happen with other checks, such as generated columns in COPY FROM WHERE conditions (v19 new) and COPY FROM on tables with row-level security.

I tried a solution that splits the target relation pre-checks out of CopyFrom() and calls the pre-check earlier. With this patch, if a user copies to an unsupported target, such as a sequence, the command fails immediately without analyzing the WHERE clause or waiting for input.

See the attached patch for details.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

Attachment Content-Type Size
v1-0001-Reject-unsupported-COPY-FROM-targets-before-WHERE.patch application/octet-stream 9.0 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2026-06-24 07:40:11 Re: Fix publisher-side sequence permission reporting
Previous Message vignesh C 2026-06-24 07:30:25 Re: Include sequences in publications created by pg_createsubscriber