Fix stale column context after COPY FROM conversion errors

Started by Chao Li17 days ago5 messageshackers
Jump to latest
#1Chao Li
li.evan.chao@gmail.com

Hi,

While working on patch [1]/messages/by-id/2da457c8-a9a1-49b1-a89f-719190dff7ae@data-bene.io, I noticed that COPY FROM emits confusing error messages, for example:
```
evantest=# CREATE TABLE t (a int);
CREATE TABLE
evantest=#
evantest=# COPY t FROM STDIN WITH (FORMAT csv, ON_ERROR ignore);
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.

bad
1,2
\.

ERROR: extra data after last expected column
CONTEXT: COPY t, line 2, column a: "1"
```
The context line shows column a: "1", which does not identify the actual problem where an extra field (2) was supplied.

The problem is that CopyFromTextLikeOneRow() only clears the current column information at the end of each loop iteration, but bypasses this cleanup when input conversion fails:
```
if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
return true;
else if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
continue;
}

cstate->cur_attname = NULL;
cstate->cur_attval = NULL;
}
```

So the fix is simple: we can add the cleanup in the COPY_ON_ERROR_IGNORE branch and remove the COPY_ON_ERROR_SET_NULL branch, letting it fall through so that the cleanup is performed.

With the fix, the same test shows the correct context:
```
evantest=# COPY t FROM STDIN WITH (FORMAT csv, ON_ERROR ignore);
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.

bad
1,2
\.

ERROR: extra data after last expected column
CONTEXT: COPY t, line 2: “1,2"
```

[1]: /messages/by-id/2da457c8-a9a1-49b1-a89f-719190dff7ae@data-bene.io

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

Attachments:

t254571_1
v1-0001-Fix-stale-column-context-after-COPY-FROM-conversi.patchapplication/octet-stream; name=v1-0001-Fix-stale-column-context-after-COPY-FROM-conversi.patch; x-unix-mode=0644Download+25-5
#2Michael Paquier
michael@paquier.xyz
In reply to: Chao Li (#1)
Re: Fix stale column context after COPY FROM conversion errors

On Thu, Nov 05, 2026 at 02:43:54PM +0800, Chao Li wrote:

So the fix is simple: we can add the cleanup in the
COPY_ON_ERROR_IGNORE branch and remove the COPY_ON_ERROR_SET_NULL
branch, letting it fall through so that the cleanup is performed.

I've also wondered exactly about that a couple of hours ago while
looking at a different bug in the same area of the code.. Interesting
timing.

I don't buy that the current coding on HEAD is correct, based on what
I can see here from this test:
* SQL input:
CREATE TABLE check_ign_err (n int, m int[], k int);
COPY check_ign_err FROM STDIN WITH (on_error ignore);
bad {1} 3
1 {1} 3 abc
\.

* HEAD:
ERROR: 22P04: extra data after last expected column
CONTEXT: COPY check_ign_err, line 2, column n: "1"
* with your patch:
ERROR: 22P04: extra data after last expected column
CONTEXT: COPY check_ign_err, line 2: "1 {1} 3 4"

Complaining about the first column on the second line is incorrect and
gets fed from the first incorrect line: we have extra fields on this
line, they should be mentioned.
--
Michael

#3Yilin Zhang
jiezhilove@126.com
In reply to: Michael Paquier (#2)
Re: Fix stale column context after COPY FROM conversion errors

At 2026-11-05 16:04:17, "Michael Paquier" <michael@paquier.xyz> wrote:

I've also wondered exactly about that a couple of hours ago while
looking at a different bug in the same area of the code.. Interesting
timing.

I don't buy that the current coding on HEAD is correct, based on what
I can see here from this test:
* SQL input:
CREATE TABLE check_ign_err (n int, m int[], k int);
COPY check_ign_err FROM STDIN WITH (on_error ignore);
bad {1} 3
1 {1} 3 abc
\.

* HEAD:
ERROR: 22P04: extra data after last expected column
CONTEXT: COPY check_ign_err, line 2, column n: "1"
* with your patch:
ERROR: 22P04: extra data after last expected column
CONTEXT: COPY check_ign_err, line 2: "1 {1} 3 4"

Complaining about the first column on the second line is incorrect and
gets fed from the first incorrect line: we have extra fields on this
line, they should be mentioned.
--

Michael

Hi,
Indeed, as you mentioned, the error on the second line points to some abnormal data in the first column.
That said, we should also consider the following:
When the COPY rows used in current tests are small enough, the attribute_buf has sufficient capacity.
Since resetStringInfo reuses the same memory block, the leftover cur_attval remains readable.
This only results in garbled error messages.
If we construct two extremely large COPY rows that both trigger repalloc,
the old address will be munmapped while processing the second row.
When the error callback invokes strlen(cur_attval), it accesses unmapped memory,
which causes a deterministic SIGSEGV.
I have reproduced this issue in my local environment.
Therefore, I believe this bug deserves higher priority for fixing.
Chao Li's v1 patch already resolves this problem, LGTM.

Best regards,
Yilin Zhang

#4Chao Li
li.evan.chao@gmail.com
In reply to: Michael Paquier (#2)
Re: Fix stale column context after COPY FROM conversion errors

On Nov 5, 2026, at 16:04, Michael Paquier <michael@paquier.xyz> wrote:

On Thu, Nov 05, 2026 at 02:43:54PM +0800, Chao Li wrote:

So the fix is simple: we can add the cleanup in the
COPY_ON_ERROR_IGNORE branch and remove the COPY_ON_ERROR_SET_NULL
branch, letting it fall through so that the cleanup is performed.

I've also wondered exactly about that a couple of hours ago while
looking at a different bug in the same area of the code.. Interesting
timing.

I don't buy that the current coding on HEAD is correct, based on what
I can see here from this test:
* SQL input:
CREATE TABLE check_ign_err (n int, m int[], k int);
COPY check_ign_err FROM STDIN WITH (on_error ignore);
bad {1} 3
1 {1} 3 abc
\.

* HEAD:
ERROR: 22P04: extra data after last expected column
CONTEXT: COPY check_ign_err, line 2, column n: "1"
* with your patch:
ERROR: 22P04: extra data after last expected column
CONTEXT: COPY check_ign_err, line 2: "1 {1} 3 4"

Complaining about the first column on the second line is incorrect and
gets fed from the first incorrect line: we have extra fields on this
line, they should be mentioned.
--
Michael

Hi Michael,

I just saw that you pushed 23e2a332dd, which resolves the "ON_ERROR ignore" issue, but I think this patch is still needed to resolve the "ON_ERROR set_null" problem. If I revert this patch's code changes and only add the new test case, "make check" fails:
```
# diff -U3 /Users/chaol/Documents/code/postgresql/src/test/regress/expected/copy2.out /Users/chaol/Documents/code/postgresql/src/test/regress/results/copy2.out
# --- /Users/chaol/Documents/code/postgresql/src/test/regress/expected/copy2.out 2026-11-06 10:06:21
# +++ /Users/chaol/Documents/code/postgresql/src/test/regress/results/copy2.out 2026-11-06 10:11:55
# @@ -829,7 +829,7 @@
# -- fail, less data after a conversion error.
# COPY t_on_error_null FROM STDIN WITH (delimiter ',', on_error set_null);
# ERROR: missing data for column "c"
# -CONTEXT: COPY t_on_error_null, line 1: "1,bad"
# +CONTEXT: COPY t_on_error_null, line 1, column b: "bad"
# -- fail, extra data.
# COPY t_on_error_null FROM STDIN WITH (delimiter ',', on_error set_null);
# ERROR: extra data after last expected column
not ok 201 + copy2
```

This patch also clears the current attribute in CopyFromTextLikeOneRow() before returning for ON_ERROR ignore. I thought that might be redundant with the clearing done by 23e2a332dd, so I tried removing it. The test then failed against this patch's updated expected output:
```
# diff -U3 /Users/chaol/Documents/code/postgresql/src/test/regress/expected/copy2.out /Users/chaol/Documents/code/postgresql/src/test/regress/results/copy2.out
# --- /Users/chaol/Documents/code/postgresql/src/test/regress/expected/copy2.out 2026-11-06 10:06:21
# +++ /Users/chaol/Documents/code/postgresql/src/test/regress/results/copy2.out 2026-11-06 10:21:09
# @@ -905,7 +905,7 @@
# -- tests for reject_limit option
# COPY check_ign_err FROM STDIN WITH (on_error ignore, reject_limit 3);
# ERROR: skipped more than REJECT_LIMIT (3) rows due to data type incompatibility
# -CONTEXT: COPY check_ign_err, line 5: ""
# +CONTEXT: COPY check_ign_err, line 5, column n: ""
# COPY check_ign_err FROM STDIN WITH (on_error ignore, reject_limit 4);
# NOTICE: 4 rows were skipped due to data type incompatibility
```

Then I realized that the REJECT_LIMIT error is raised before the next call to NextCopyFrom(), so clearing the attribute in NextCopyFrom() doesn’t cover the REJECT_LIMIT path.

With this patch's cleanup in place, I think we can remove the clearing from NextCopyFrom(). I haven't done so in this patch. I'd leave that decision to you.

V2 is a rebased version.

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

Attachments:

t254571_4
v2-0001-Fix-stale-column-context-after-COPY-FROM-conversi.patchapplication/octet-stream; name=v2-0001-Fix-stale-column-context-after-COPY-FROM-conversi.patch; x-unix-mode=0644Download+23-5
#5shihao zhong
zhong950419@gmail.com
In reply to: Chao Li (#4)
Re: Fix stale column context after COPY FROM conversion errors

Hi Chao,

Thanks for working on this.

2 interesting points.

1. V2 also fixes the crash Gowdham reported [1]/messages/by-id/1a181ff6be8.6d14feb261368.4036709598649280477@zohocorp.com. The committed fix
got reverted recently [2]https://git.postgresql.org/pg/commitdiff/c07545dfe1a.

2. A CHECK error after set_null names the nulled column instead of
the row.

CREATE TABLE r3 (a int CHECK (a > 0), b int);
COPY r3 FROM STDIN WITH (delimiter ',', on_error set_null);
-1,bad

Without v2 CONTEXT: COPY r3, line 1, column b: "bad"
With v2 CONTEXT: COPY r3, line 1: "-1,bad"

[1]: /messages/by-id/1a181ff6be8.6d14feb261368.4036709598649280477@zohocorp.com
/messages/by-id/1a181ff6be8.6d14feb261368.4036709598649280477@zohocorp.com
[2]: https://git.postgresql.org/pg/commitdiff/c07545dfe1a

Thanks,
Shihao