pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Fix failure for generated column with a not-null domain constrai 5+ messages / 1 participants [nested] [flat]
* pgsql: Fix failure for generated column with a not-null domain constrai @ 2025-04-15 16:08 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2025-04-15 16:08 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix failure for generated column with a not-null domain constraint. If a GENERATED column is declared to have a domain data type where the domain's constraints disallow null values, INSERT commands failed because we built a targetlist that included coercing a null constant to the domain's type. The failure occurred even when the generated value would have been perfectly OK. This is adjacent to the issues fixed in 0da39aa76, but we didn't notice for lack of testing a domain with such a constraint. We aren't going to use the result of the targetlist entry for the generated column --- ExecComputeStoredGenerated will overwrite it. So it's not really necessary that it have the exact datatype of the generated column. This patch fixes the problem by changing the targetlist entry to be a null Const of the domain's base type, which should be sufficiently legal. (We do have to tweak ExecCheckPlanOutput to accept the situation, though.) This has been broken since we implemented generated columns. However, this patch only applies easily as far back as v14, partly because I (tgl) only carried 0da39aa76 back that far, but mostly because v14 significantly refactored the handling of INSERT/UPDATE targetlists. Given the lack of field complaints and the short remaining support lifetime of v13, I judge the cost-benefit ratio not good for devising a version that would work in v13. Reported-by: jian he <jian.universality@gmail.com> Author: jian he <jian.universality@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/CACJufxG59tip2+9h=rEv-ykOFjt0cbsPVchhi0RTij8bABBA0Q@mail.gmail.com Backpatch-through: 14 Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/3c39c000c85957940d4c4b30639447246b2595c0 Modified Files -------------- src/backend/executor/nodeModifyTable.c | 44 +++++++++++++++++++-------- src/backend/optimizer/prep/preptlist.c | 53 +++++++++++++++++++++++---------- src/test/regress/expected/generated.out | 5 ++++ src/test/regress/sql/generated.sql | 5 ++++ 4 files changed, 80 insertions(+), 27 deletions(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
* pgsql: Fix failure for generated column with a not-null domain constrai @ 2025-04-15 16:08 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2025-04-15 16:08 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix failure for generated column with a not-null domain constraint. If a GENERATED column is declared to have a domain data type where the domain's constraints disallow null values, INSERT commands failed because we built a targetlist that included coercing a null constant to the domain's type. The failure occurred even when the generated value would have been perfectly OK. This is adjacent to the issues fixed in 0da39aa76, but we didn't notice for lack of testing a domain with such a constraint. We aren't going to use the result of the targetlist entry for the generated column --- ExecComputeStoredGenerated will overwrite it. So it's not really necessary that it have the exact datatype of the generated column. This patch fixes the problem by changing the targetlist entry to be a null Const of the domain's base type, which should be sufficiently legal. (We do have to tweak ExecCheckPlanOutput to accept the situation, though.) This has been broken since we implemented generated columns. However, this patch only applies easily as far back as v14, partly because I (tgl) only carried 0da39aa76 back that far, but mostly because v14 significantly refactored the handling of INSERT/UPDATE targetlists. Given the lack of field complaints and the short remaining support lifetime of v13, I judge the cost-benefit ratio not good for devising a version that would work in v13. Reported-by: jian he <jian.universality@gmail.com> Author: jian he <jian.universality@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/CACJufxG59tip2+9h=rEv-ykOFjt0cbsPVchhi0RTij8bABBA0Q@mail.gmail.com Backpatch-through: 14 Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/97d671672393683d8f3c43ef0324be2cb80d6276 Modified Files -------------- src/backend/executor/nodeModifyTable.c | 44 +++++++++++++++++++-------- src/backend/optimizer/prep/preptlist.c | 53 +++++++++++++++++++++++---------- src/test/regress/expected/generated.out | 5 ++++ src/test/regress/sql/generated.sql | 5 ++++ 4 files changed, 80 insertions(+), 27 deletions(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
* pgsql: Fix failure for generated column with a not-null domain constrai @ 2025-04-15 16:08 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2025-04-15 16:08 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix failure for generated column with a not-null domain constraint. If a GENERATED column is declared to have a domain data type where the domain's constraints disallow null values, INSERT commands failed because we built a targetlist that included coercing a null constant to the domain's type. The failure occurred even when the generated value would have been perfectly OK. This is adjacent to the issues fixed in 0da39aa76, but we didn't notice for lack of testing a domain with such a constraint. We aren't going to use the result of the targetlist entry for the generated column --- ExecComputeStoredGenerated will overwrite it. So it's not really necessary that it have the exact datatype of the generated column. This patch fixes the problem by changing the targetlist entry to be a null Const of the domain's base type, which should be sufficiently legal. (We do have to tweak ExecCheckPlanOutput to accept the situation, though.) This has been broken since we implemented generated columns. However, this patch only applies easily as far back as v14, partly because I (tgl) only carried 0da39aa76 back that far, but mostly because v14 significantly refactored the handling of INSERT/UPDATE targetlists. Given the lack of field complaints and the short remaining support lifetime of v13, I judge the cost-benefit ratio not good for devising a version that would work in v13. Reported-by: jian he <jian.universality@gmail.com> Author: jian he <jian.universality@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/CACJufxG59tip2+9h=rEv-ykOFjt0cbsPVchhi0RTij8bABBA0Q@mail.gmail.com Backpatch-through: 14 Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/f04e0faa3702edcc3bebefcb0a21be53ee442e59 Modified Files -------------- src/backend/executor/nodeModifyTable.c | 44 +++++++++++++++++++-------- src/backend/optimizer/prep/preptlist.c | 53 +++++++++++++++++++++++---------- src/test/regress/expected/generated.out | 5 ++++ src/test/regress/sql/generated.sql | 5 ++++ 4 files changed, 80 insertions(+), 27 deletions(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
* pgsql: Fix failure for generated column with a not-null domain constrai @ 2025-04-15 16:08 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2025-04-15 16:08 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix failure for generated column with a not-null domain constraint. If a GENERATED column is declared to have a domain data type where the domain's constraints disallow null values, INSERT commands failed because we built a targetlist that included coercing a null constant to the domain's type. The failure occurred even when the generated value would have been perfectly OK. This is adjacent to the issues fixed in 0da39aa76, but we didn't notice for lack of testing a domain with such a constraint. We aren't going to use the result of the targetlist entry for the generated column --- ExecComputeStoredGenerated will overwrite it. So it's not really necessary that it have the exact datatype of the generated column. This patch fixes the problem by changing the targetlist entry to be a null Const of the domain's base type, which should be sufficiently legal. (We do have to tweak ExecCheckPlanOutput to accept the situation, though.) This has been broken since we implemented generated columns. However, this patch only applies easily as far back as v14, partly because I (tgl) only carried 0da39aa76 back that far, but mostly because v14 significantly refactored the handling of INSERT/UPDATE targetlists. Given the lack of field complaints and the short remaining support lifetime of v13, I judge the cost-benefit ratio not good for devising a version that would work in v13. Reported-by: jian he <jian.universality@gmail.com> Author: jian he <jian.universality@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/CACJufxG59tip2+9h=rEv-ykOFjt0cbsPVchhi0RTij8bABBA0Q@mail.gmail.com Backpatch-through: 14 Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/4604928edd7236587fcd27ee2ecc0c33124cbbfe Modified Files -------------- src/backend/executor/nodeModifyTable.c | 44 +++++++++++++++++++-------- src/backend/optimizer/prep/preptlist.c | 53 +++++++++++++++++++++++---------- src/test/regress/expected/generated.out | 5 ++++ src/test/regress/sql/generated.sql | 5 ++++ 4 files changed, 80 insertions(+), 27 deletions(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
* pgsql: Fix failure for generated column with a not-null domain constrai @ 2025-04-15 16:08 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2025-04-15 16:08 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix failure for generated column with a not-null domain constraint. If a GENERATED column is declared to have a domain data type where the domain's constraints disallow null values, INSERT commands failed because we built a targetlist that included coercing a null constant to the domain's type. The failure occurred even when the generated value would have been perfectly OK. This is adjacent to the issues fixed in 0da39aa76, but we didn't notice for lack of testing a domain with such a constraint. We aren't going to use the result of the targetlist entry for the generated column --- ExecComputeStoredGenerated will overwrite it. So it's not really necessary that it have the exact datatype of the generated column. This patch fixes the problem by changing the targetlist entry to be a null Const of the domain's base type, which should be sufficiently legal. (We do have to tweak ExecCheckPlanOutput to accept the situation, though.) This has been broken since we implemented generated columns. However, this patch only applies easily as far back as v14, partly because I (tgl) only carried 0da39aa76 back that far, but mostly because v14 significantly refactored the handling of INSERT/UPDATE targetlists. Given the lack of field complaints and the short remaining support lifetime of v13, I judge the cost-benefit ratio not good for devising a version that would work in v13. Reported-by: jian he <jian.universality@gmail.com> Author: jian he <jian.universality@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/CACJufxG59tip2+9h=rEv-ykOFjt0cbsPVchhi0RTij8bABBA0Q@mail.gmail.com Backpatch-through: 14 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/7c872849407730fa01e2c13b2d47483bc3ff6e7e Modified Files -------------- src/backend/executor/nodeModifyTable.c | 44 ++++++++++++++------ src/backend/optimizer/prep/preptlist.c | 53 ++++++++++++++++++------- src/test/regress/expected/generated_stored.out | 5 +++ src/test/regress/expected/generated_virtual.out | 5 +++ src/test/regress/sql/generated_stored.sql | 5 +++ src/test/regress/sql/generated_virtual.sql | 5 +++ 6 files changed, 90 insertions(+), 27 deletions(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-04-15 16:08 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-04-15 16:08 pgsql: Fix failure for generated column with a not-null domain constrai Tom Lane <tgl@sss.pgh.pa.us> 2025-04-15 16:08 pgsql: Fix failure for generated column with a not-null domain constrai Tom Lane <tgl@sss.pgh.pa.us> 2025-04-15 16:08 pgsql: Fix failure for generated column with a not-null domain constrai Tom Lane <tgl@sss.pgh.pa.us> 2025-04-15 16:08 pgsql: Fix failure for generated column with a not-null domain constrai Tom Lane <tgl@sss.pgh.pa.us> 2025-04-15 16:08 pgsql: Fix failure for generated column with a not-null domain constrai Tom Lane <tgl@sss.pgh.pa.us>
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