pgsql: Fix incorrect accessing of pfree'd memory in Memoize

2024-03-10 Thread David Rowley
Fix incorrect accessing of pfree'd memory in Memoize

For pass-by-reference types, the code added in 0b053e78b, which aimed to
resolve a memory leak, was overly aggressive in resetting the per-tuple
memory context which could result in pfree'd memory being accessed
resulting in failing to find previously cached results in the hash
table.

What was happening was prepare_probe_slot() was switching to the
per-tuple memory context and calling ExecEvalExpr().  ExecEvalExpr() may
have required a memory allocation.  Both MemoizeHash_hash() and
MemoizeHash_equal() were aggressively resetting the per-tuple context
and after determining the hash value, the context would have gotten reset
before MemoizeHash_equal() was called.  This could have resulted in
MemoizeHash_equal() looking at pfree'd memory.

This is less likely to have caused issues on a production build as some
other allocation would have had to have reused the pfree'd memory to
overwrite it.  Otherwise, the original contents would have been intact.
However, this clearly caused issues on MEMORY_CONTEXT_CHECKING builds.

Author: Tender Wang, Andrei Lepikhov
Reported-by: Tender Wang (using SQLancer)
Reviewed-by: Andrei Lepikhov, Richard Guo, David Rowley
Discussion: 
https://postgr.es/m/cahewxnnt6n6ujkya0z-jlfzvxcwgferqsfhiwa+nylg-x8i...@mail.gmail.com
Backpatch-through: 14, where Memoize was added

Branch
--
REL_14_STABLE

Details
---
https://git.postgresql.org/pg/commitdiff/72b8507db2cc24810a29153838a62777d32f412f

Modified Files
--
src/backend/executor/nodeMemoize.c| 15 +++
src/test/regress/expected/memoize.out | 31 ++-
src/test/regress/sql/memoize.sql  | 23 ++-
3 files changed, 63 insertions(+), 6 deletions(-)



pgsql: Fix incorrect accessing of pfree'd memory in Memoize

2024-03-10 Thread David Rowley
Fix incorrect accessing of pfree'd memory in Memoize

For pass-by-reference types, the code added in 0b053e78b, which aimed to
resolve a memory leak, was overly aggressive in resetting the per-tuple
memory context which could result in pfree'd memory being accessed
resulting in failing to find previously cached results in the hash
table.

What was happening was prepare_probe_slot() was switching to the
per-tuple memory context and calling ExecEvalExpr().  ExecEvalExpr() may
have required a memory allocation.  Both MemoizeHash_hash() and
MemoizeHash_equal() were aggressively resetting the per-tuple context
and after determining the hash value, the context would have gotten reset
before MemoizeHash_equal() was called.  This could have resulted in
MemoizeHash_equal() looking at pfree'd memory.

This is less likely to have caused issues on a production build as some
other allocation would have had to have reused the pfree'd memory to
overwrite it.  Otherwise, the original contents would have been intact.
However, this clearly caused issues on MEMORY_CONTEXT_CHECKING builds.

Author: Tender Wang, Andrei Lepikhov
Reported-by: Tender Wang (using SQLancer)
Reviewed-by: Andrei Lepikhov, Richard Guo, David Rowley
Discussion: 
https://postgr.es/m/cahewxnnt6n6ujkya0z-jlfzvxcwgferqsfhiwa+nylg-x8i...@mail.gmail.com
Backpatch-through: 14, where Memoize was added

Branch
--
REL_15_STABLE

Details
---
https://git.postgresql.org/pg/commitdiff/74530804fcb7a36175fa1268928cba98aa9e6cff

Modified Files
--
src/backend/executor/nodeMemoize.c| 15 +++
src/test/regress/expected/memoize.out | 31 ++-
src/test/regress/sql/memoize.sql  | 23 ++-
3 files changed, 63 insertions(+), 6 deletions(-)



pgsql: Fix incorrect accessing of pfree'd memory in Memoize

2024-03-10 Thread David Rowley
Fix incorrect accessing of pfree'd memory in Memoize

For pass-by-reference types, the code added in 0b053e78b, which aimed to
resolve a memory leak, was overly aggressive in resetting the per-tuple
memory context which could result in pfree'd memory being accessed
resulting in failing to find previously cached results in the hash
table.

What was happening was prepare_probe_slot() was switching to the
per-tuple memory context and calling ExecEvalExpr().  ExecEvalExpr() may
have required a memory allocation.  Both MemoizeHash_hash() and
MemoizeHash_equal() were aggressively resetting the per-tuple context
and after determining the hash value, the context would have gotten reset
before MemoizeHash_equal() was called.  This could have resulted in
MemoizeHash_equal() looking at pfree'd memory.

This is less likely to have caused issues on a production build as some
other allocation would have had to have reused the pfree'd memory to
overwrite it.  Otherwise, the original contents would have been intact.
However, this clearly caused issues on MEMORY_CONTEXT_CHECKING builds.

Author: Tender Wang, Andrei Lepikhov
Reported-by: Tender Wang (using SQLancer)
Reviewed-by: Andrei Lepikhov, Richard Guo, David Rowley
Discussion: 
https://postgr.es/m/cahewxnnt6n6ujkya0z-jlfzvxcwgferqsfhiwa+nylg-x8i...@mail.gmail.com
Backpatch-through: 14, where Memoize was added

Branch
--
REL_16_STABLE

Details
---
https://git.postgresql.org/pg/commitdiff/348233cb128d32fd3a61f4473eda9564efdcd29c

Modified Files
--
src/backend/executor/nodeMemoize.c| 15 +++
src/test/regress/expected/memoize.out | 31 ++-
src/test/regress/sql/memoize.sql  | 23 ++-
3 files changed, 63 insertions(+), 6 deletions(-)



pgsql: Fix incorrect accessing of pfree'd memory in Memoize

2024-03-10 Thread David Rowley
Fix incorrect accessing of pfree'd memory in Memoize

For pass-by-reference types, the code added in 0b053e78b, which aimed to
resolve a memory leak, was overly aggressive in resetting the per-tuple
memory context which could result in pfree'd memory being accessed
resulting in failing to find previously cached results in the hash
table.

What was happening was prepare_probe_slot() was switching to the
per-tuple memory context and calling ExecEvalExpr().  ExecEvalExpr() may
have required a memory allocation.  Both MemoizeHash_hash() and
MemoizeHash_equal() were aggressively resetting the per-tuple context
and after determining the hash value, the context would have gotten reset
before MemoizeHash_equal() was called.  This could have resulted in
MemoizeHash_equal() looking at pfree'd memory.

This is less likely to have caused issues on a production build as some
other allocation would have had to have reused the pfree'd memory to
overwrite it.  Otherwise, the original contents would have been intact.
However, this clearly caused issues on MEMORY_CONTEXT_CHECKING builds.

Author: Tender Wang, Andrei Lepikhov
Reported-by: Tender Wang (using SQLancer)
Reviewed-by: Andrei Lepikhov, Richard Guo, David Rowley
Discussion: 
https://postgr.es/m/cahewxnnt6n6ujkya0z-jlfzvxcwgferqsfhiwa+nylg-x8i...@mail.gmail.com
Backpatch-through: 14, where Memoize was added

Branch
--
master

Details
---
https://git.postgresql.org/pg/commitdiff/e629846472255c7a636c453a522755ef05489c90

Modified Files
--
src/backend/executor/nodeMemoize.c| 15 +++
src/test/regress/expected/memoize.out | 31 ++-
src/test/regress/sql/memoize.sql  | 23 ++-
3 files changed, 63 insertions(+), 6 deletions(-)



pgsql: Doc: Warn about two_phase when altering a subscription's slot na

2024-03-10 Thread Amit Kapila
Doc: Warn about two_phase when altering a subscription's slot name.

We expect the 'two_phase' and 'failover' properties to match between the
slot on the publisher and a subscription option on the subscriber.
Otherwise, the slot on the publisher may behave differently from what the
subscription's failover option says.

Author: Bertrand Drouvot
Reviewed-by: Peter Smith, Tristen Raab, Amit Kapila
Discussion: 
https://postgr.es/m/zbkyrlphh+rxp...@ip-10-97-1-34.eu-west-3.compute.internal

Branch
--
master

Details
---
https://git.postgresql.org/pg/commitdiff/21e3a8bc3544a1cfcff85933bc9c0664af32a8b8

Modified Files
--
doc/src/sgml/ref/alter_subscription.sgml | 16 
1 file changed, 8 insertions(+), 8 deletions(-)



pgsql: Backpatch missing check_stack_depth() to some recursive function

2024-03-10 Thread Alexander Korotkov
Backpatch missing check_stack_depth() to some recursive functions

Backpatch changes from d57b7cc333, 75bcba6cbd to all supported branches per
proposal of Egor Chindyaskin.

Discussion: https://postgr.es/m/DE5FD776-A8CD-4378-BCFA-3BF30F1F6D60%40mail.ru

Branch
--
REL_15_STABLE

Details
---
https://git.postgresql.org/pg/commitdiff/84788ee5b4ee5a7ee7543c8dce9f270101f352f1

Modified Files
--
src/backend/catalog/dependency.c  |  7 +++
src/backend/catalog/heap.c|  3 +++
src/backend/commands/tablecmds.c  | 13 +
src/backend/optimizer/util/clauses.c  |  4 
src/backend/utils/adt/jsonpath_exec.c |  3 +++
5 files changed, 30 insertions(+)



pgsql: Backpatch missing check_stack_depth() to some recursive function

2024-03-10 Thread Alexander Korotkov
Backpatch missing check_stack_depth() to some recursive functions

Backpatch changes from d57b7cc333, 75bcba6cbd to all supported branches per
proposal of Egor Chindyaskin.

Discussion: https://postgr.es/m/DE5FD776-A8CD-4378-BCFA-3BF30F1F6D60%40mail.ru

Branch
--
REL_16_STABLE

Details
---
https://git.postgresql.org/pg/commitdiff/7607671826dd3050b2656700cf1a9cc99a73a4df

Modified Files
--
src/backend/catalog/dependency.c  |  7 +++
src/backend/catalog/heap.c|  3 +++
src/backend/commands/tablecmds.c  | 13 +
src/backend/optimizer/util/clauses.c  |  4 
src/backend/utils/adt/jsonpath_exec.c |  3 +++
5 files changed, 30 insertions(+)



pgsql: Backpatch missing check_stack_depth() to some recursive function

2024-03-10 Thread Alexander Korotkov
Backpatch missing check_stack_depth() to some recursive functions

Backpatch changes from d57b7cc333, 75bcba6cbd to all supported branches per
proposal of Egor Chindyaskin.

Discussion: https://postgr.es/m/DE5FD776-A8CD-4378-BCFA-3BF30F1F6D60%40mail.ru

Branch
--
REL_14_STABLE

Details
---
https://git.postgresql.org/pg/commitdiff/84cc1a5527564beef86ba500d8318e2f659f6969

Modified Files
--
src/backend/catalog/dependency.c  |  7 +++
src/backend/catalog/heap.c|  3 +++
src/backend/commands/tablecmds.c  | 13 +
src/backend/optimizer/util/clauses.c  |  4 
src/backend/utils/adt/jsonpath_exec.c |  3 +++
5 files changed, 30 insertions(+)



pgsql: Backpatch missing check_stack_depth() to some recursive function

2024-03-10 Thread Alexander Korotkov
Backpatch missing check_stack_depth() to some recursive functions

Backpatch changes from d57b7cc333, 75bcba6cbd to all supported branches per
proposal of Egor Chindyaskin.

Discussion: https://postgr.es/m/DE5FD776-A8CD-4378-BCFA-3BF30F1F6D60%40mail.ru

Branch
--
REL_13_STABLE

Details
---
https://git.postgresql.org/pg/commitdiff/445c7e38f65f4abee1e55b31a849c31b18d99d4b

Modified Files
--
src/backend/catalog/dependency.c  |  7 +++
src/backend/catalog/heap.c|  3 +++
src/backend/commands/tablecmds.c  | 13 +
src/backend/optimizer/util/clauses.c  |  4 
src/backend/utils/adt/jsonpath_exec.c |  3 +++
5 files changed, 30 insertions(+)



pgsql: Backpatch missing check_stack_depth() to some recursive function

2024-03-10 Thread Alexander Korotkov
Backpatch missing check_stack_depth() to some recursive functions

Backpatch changes from d57b7cc333, 75bcba6cbd to all supported branches per
proposal of Egor Chindyaskin.

Discussion: https://postgr.es/m/DE5FD776-A8CD-4378-BCFA-3BF30F1F6D60%40mail.ru

Branch
--
REL_12_STABLE

Details
---
https://git.postgresql.org/pg/commitdiff/98bfb7558a1ab236670f1e6309f336b7d1aafafa

Modified Files
--
src/backend/catalog/dependency.c  |  7 +++
src/backend/catalog/heap.c|  3 +++
src/backend/commands/tablecmds.c  | 13 +
src/backend/optimizer/util/clauses.c  |  4 
src/backend/utils/adt/jsonpath_exec.c |  3 +++
5 files changed, 30 insertions(+)



pgsql: Improve consistency of replication slot statistics

2024-03-10 Thread Michael Paquier
Improve consistency of replication slot statistics

The replication slot stats stored in shared memory rely on an internal
index number.  Both pgstat_reset_replslot() and pgstat_fetch_replslot()
lacked some LWLock protections with ReplicationSlotControlLock while
operating on these index numbers.  This issue could cause these two
functions to potentially operate on incorrect slots when taken in
isolation in the event of slots dropped and/or re-created concurrently.

Note that pg_stat_get_replication_slot() is called once per slot when
querying pg_stat_replication_slots, meaning that the stats are retrieved
across multiple ReplicationSlotControlLock acquisitions.  So, while this
commit improves more consistency, it may still be possible that
statistics are not completely consistent for a single scan of
pg_stat_replication_slots under concurrent replication slot drop or
creation activity.

The issue should unlikely be a problem in practice, causing the report
of inconsistent stats or or the stats reset of an incorrect slot, so no
backpatch is done.

Author: Bertrand Drouvot
Reviewed-by: Heikki Linnakangas, Shveta Malik, Michael Paquier
Discussion: 
https://postgr.es/m/zegq1hdwfflkj...@ip-10-97-1-34.eu-west-3.compute.internal

Branch
--
master

Details
---
https://git.postgresql.org/pg/commitdiff/b36fbd9f8da140f5b0ef9f7daa6b3cb4cae8a69b

Modified Files
--
src/backend/utils/activity/pgstat_replslot.c | 42 +---
1 file changed, 25 insertions(+), 17 deletions(-)



pgsql: Add some checkpoint and redo LSNs to a couple of recovery errors

2024-03-10 Thread Michael Paquier
Add some checkpoint and redo LSNs to a couple of recovery errors

Two FATALs and one PANIC gain details about the LSNs they fail at:
- When restoring from a backup_label, the FATAL log generated when not
finding the checkpoint record now reports its LSN.
- When restoring from a backup_label, the FATAL log generated when not
finding the redo record referenced by a checkpoint record now shows both
the redo and checkpoint record LSNs.
- When not restoring from a backup_label, the PANIC error generated when
not finding the checkpoint record now reports its LSN.

This information is useful when debugging corruption issues, and these
LSNs may not show up in the logs depending on the level of logging
configured in the backend.

Author: David Steele
Discussion: 
https://postgr.es/m/0e90da89-77ca-4ccf-872c-9626d755e...@pgmasters.net

Branch
--
master

Details
---
https://git.postgresql.org/pg/commitdiff/f500ba07fa9d00eaa2d26ea66401e62f950163ae

Modified Files
--
src/backend/access/transam/xlogrecovery.c | 9 ++---
1 file changed, 6 insertions(+), 3 deletions(-)



pgsql: Improve support for ExplainOneQuery() hook

2024-03-10 Thread Michael Paquier
Improve support for ExplainOneQuery() hook

There is a hook called ExplainOneQuery_hook that gives modules the
possibility to plug into this code path, but, like utility.c for utility
statement execution, there is no corresponding "standard" routine in
the case of EXPLAIN executed for one Query.

This commit adds a new standard_ExplainOneQuery() in explain.c, which is
able to run explain on a non-utility Query without calling its hook.

Per the feedback received from a couple of hackers, this change gives
the possibility to cut a few hundred lines of code in some of the
popular out-of-core modules as these maintained a copy of
ExplainOneQuery(), adding custom extra information at the beginning or
the end of the EXPLAIN output.

Author: Mats Kindahl
Reviewed-by: Aleksander Alekseev, Jelte Fennema-Nio, Andrei Lepikhov
Discussion: 
https://postgr.es/m/ca+14427v_b4eaoc_o-iyyucrdmsotfpuh9k-qbexffy1hyj...@mail.gmail.com

Branch
--
master

Details
---
https://git.postgresql.org/pg/commitdiff/a04ddd077e61096da660e1c2f205e0c8277f2dcd

Modified Files
--
src/backend/commands/explain.c | 106 +++--
src/include/commands/explain.h |   4 ++
2 files changed, 63 insertions(+), 47 deletions(-)



pgsql: Fix deparsing of Consts in postgres_fdw ORDER BY

2024-03-10 Thread David Rowley
Fix deparsing of Consts in postgres_fdw ORDER BY

For UNION ALL queries where a union child query contained a foreign
table, if the targetlist of that query contained a constant, and the
top-level query performed an ORDER BY which contained the column for the
constant value, then postgres_fdw would find the EquivalenceMember with
the Const and then try to produce an ORDER BY containing that Const.

This caused problems with INT typed Consts as these could appear to be
requests to order by an ordinal column position rather than the constant
value.  This could lead to either an error such as:

ERROR:  ORDER BY position  is not in select list

or worse, if the constant value is a valid column, then we could just
sort by the wrong column altogether.

Here we fix this issue by just not including these Consts in the ORDER
BY clause.

In passing, add a new section for testing ORDER BY in the postgres_fdw
tests and move two existing tests which were misplaced in the WHERE
clause testing section into it.

Reported-by: Michał Kłeczek
Reviewed-by: Ashutosh Bapat, Richard Guo
Bug: #18381
Discussion: 
https://postgr.es/m/0714C8B8-8D82-4ABB-9F8D-A0C3657E7B6E%40kleczek.org
Discussion: https://postgr.es/m/18381-137456acd168bf93%40postgresql.org
Backpatch-through: 12, oldest supported version

Branch
--
REL_12_STABLE

Details
---
https://git.postgresql.org/pg/commitdiff/9301e0f416d1b92299d06786ac28d14885884dd3

Modified Files
--
contrib/postgres_fdw/deparse.c | 25 +--
contrib/postgres_fdw/expected/postgres_fdw.out | 93 +++---
contrib/postgres_fdw/sql/postgres_fdw.sql  | 32 +++--
3 files changed, 114 insertions(+), 36 deletions(-)



pgsql: Fix deparsing of Consts in postgres_fdw ORDER BY

2024-03-10 Thread David Rowley
Fix deparsing of Consts in postgres_fdw ORDER BY

For UNION ALL queries where a union child query contained a foreign
table, if the targetlist of that query contained a constant, and the
top-level query performed an ORDER BY which contained the column for the
constant value, then postgres_fdw would find the EquivalenceMember with
the Const and then try to produce an ORDER BY containing that Const.

This caused problems with INT typed Consts as these could appear to be
requests to order by an ordinal column position rather than the constant
value.  This could lead to either an error such as:

ERROR:  ORDER BY position  is not in select list

or worse, if the constant value is a valid column, then we could just
sort by the wrong column altogether.

Here we fix this issue by just not including these Consts in the ORDER
BY clause.

In passing, add a new section for testing ORDER BY in the postgres_fdw
tests and move two existing tests which were misplaced in the WHERE
clause testing section into it.

Reported-by: Michał Kłeczek
Reviewed-by: Ashutosh Bapat, Richard Guo
Bug: #18381
Discussion: 
https://postgr.es/m/0714C8B8-8D82-4ABB-9F8D-A0C3657E7B6E%40kleczek.org
Discussion: https://postgr.es/m/18381-137456acd168bf93%40postgresql.org
Backpatch-through: 12, oldest supported version

Branch
--
REL_13_STABLE

Details
---
https://git.postgresql.org/pg/commitdiff/20b85b3da6f8d885a980a6d7003fe5535686a5cb

Modified Files
--
contrib/postgres_fdw/deparse.c | 25 +--
contrib/postgres_fdw/expected/postgres_fdw.out | 93 +++---
contrib/postgres_fdw/sql/postgres_fdw.sql  | 32 +++--
3 files changed, 114 insertions(+), 36 deletions(-)



pgsql: Fix deparsing of Consts in postgres_fdw ORDER BY

2024-03-10 Thread David Rowley
Fix deparsing of Consts in postgres_fdw ORDER BY

For UNION ALL queries where a union child query contained a foreign
table, if the targetlist of that query contained a constant, and the
top-level query performed an ORDER BY which contained the column for the
constant value, then postgres_fdw would find the EquivalenceMember with
the Const and then try to produce an ORDER BY containing that Const.

This caused problems with INT typed Consts as these could appear to be
requests to order by an ordinal column position rather than the constant
value.  This could lead to either an error such as:

ERROR:  ORDER BY position  is not in select list

or worse, if the constant value is a valid column, then we could just
sort by the wrong column altogether.

Here we fix this issue by just not including these Consts in the ORDER
BY clause.

In passing, add a new section for testing ORDER BY in the postgres_fdw
tests and move two existing tests which were misplaced in the WHERE
clause testing section into it.

Reported-by: Michał Kłeczek
Reviewed-by: Ashutosh Bapat, Richard Guo
Bug: #18381
Discussion: 
https://postgr.es/m/0714C8B8-8D82-4ABB-9F8D-A0C3657E7B6E%40kleczek.org
Discussion: https://postgr.es/m/18381-137456acd168bf93%40postgresql.org
Backpatch-through: 12, oldest supported version

Branch
--
REL_14_STABLE

Details
---
https://git.postgresql.org/pg/commitdiff/628c3f2e17fef9cee9329f8d142db6a69105c681

Modified Files
--
contrib/postgres_fdw/deparse.c | 25 +--
contrib/postgres_fdw/expected/postgres_fdw.out | 93 +++---
contrib/postgres_fdw/sql/postgres_fdw.sql  | 32 +++--
3 files changed, 114 insertions(+), 36 deletions(-)



pgsql: Fix deparsing of Consts in postgres_fdw ORDER BY

2024-03-10 Thread David Rowley
Fix deparsing of Consts in postgres_fdw ORDER BY

For UNION ALL queries where a union child query contained a foreign
table, if the targetlist of that query contained a constant, and the
top-level query performed an ORDER BY which contained the column for the
constant value, then postgres_fdw would find the EquivalenceMember with
the Const and then try to produce an ORDER BY containing that Const.

This caused problems with INT typed Consts as these could appear to be
requests to order by an ordinal column position rather than the constant
value.  This could lead to either an error such as:

ERROR:  ORDER BY position  is not in select list

or worse, if the constant value is a valid column, then we could just
sort by the wrong column altogether.

Here we fix this issue by just not including these Consts in the ORDER
BY clause.

In passing, add a new section for testing ORDER BY in the postgres_fdw
tests and move two existing tests which were misplaced in the WHERE
clause testing section into it.

Reported-by: Michał Kłeczek
Reviewed-by: Ashutosh Bapat, Richard Guo
Bug: #18381
Discussion: 
https://postgr.es/m/0714C8B8-8D82-4ABB-9F8D-A0C3657E7B6E%40kleczek.org
Discussion: https://postgr.es/m/18381-137456acd168bf93%40postgresql.org
Backpatch-through: 12, oldest supported version

Branch
--
REL_15_STABLE

Details
---
https://git.postgresql.org/pg/commitdiff/ab64b275ad8f3bd15a3aa77308620fad80e07b62

Modified Files
--
contrib/postgres_fdw/deparse.c | 25 +--
contrib/postgres_fdw/expected/postgres_fdw.out | 93 +++---
contrib/postgres_fdw/sql/postgres_fdw.sql  | 32 +++--
3 files changed, 114 insertions(+), 36 deletions(-)



pgsql: Fix deparsing of Consts in postgres_fdw ORDER BY

2024-03-10 Thread David Rowley
Fix deparsing of Consts in postgres_fdw ORDER BY

For UNION ALL queries where a union child query contained a foreign
table, if the targetlist of that query contained a constant, and the
top-level query performed an ORDER BY which contained the column for the
constant value, then postgres_fdw would find the EquivalenceMember with
the Const and then try to produce an ORDER BY containing that Const.

This caused problems with INT typed Consts as these could appear to be
requests to order by an ordinal column position rather than the constant
value.  This could lead to either an error such as:

ERROR:  ORDER BY position  is not in select list

or worse, if the constant value is a valid column, then we could just
sort by the wrong column altogether.

Here we fix this issue by just not including these Consts in the ORDER
BY clause.

In passing, add a new section for testing ORDER BY in the postgres_fdw
tests and move two existing tests which were misplaced in the WHERE
clause testing section into it.

Reported-by: Michał Kłeczek
Reviewed-by: Ashutosh Bapat, Richard Guo
Bug: #18381
Discussion: 
https://postgr.es/m/0714C8B8-8D82-4ABB-9F8D-A0C3657E7B6E%40kleczek.org
Discussion: https://postgr.es/m/18381-137456acd168bf93%40postgresql.org
Backpatch-through: 12, oldest supported version

Branch
--
REL_16_STABLE

Details
---
https://git.postgresql.org/pg/commitdiff/6a9e2cb2b457d962bc3952931a05ad7d5b5be252

Modified Files
--
contrib/postgres_fdw/deparse.c | 25 +--
contrib/postgres_fdw/expected/postgres_fdw.out | 93 +++---
contrib/postgres_fdw/sql/postgres_fdw.sql  | 32 +++--
3 files changed, 114 insertions(+), 36 deletions(-)



pgsql: Fix deparsing of Consts in postgres_fdw ORDER BY

2024-03-10 Thread David Rowley
Fix deparsing of Consts in postgres_fdw ORDER BY

For UNION ALL queries where a union child query contained a foreign
table, if the targetlist of that query contained a constant, and the
top-level query performed an ORDER BY which contained the column for the
constant value, then postgres_fdw would find the EquivalenceMember with
the Const and then try to produce an ORDER BY containing that Const.

This caused problems with INT typed Consts as these could appear to be
requests to order by an ordinal column position rather than the constant
value.  This could lead to either an error such as:

ERROR:  ORDER BY position  is not in select list

or worse, if the constant value is a valid column, then we could just
sort by the wrong column altogether.

Here we fix this issue by just not including these Consts in the ORDER
BY clause.

In passing, add a new section for testing ORDER BY in the postgres_fdw
tests and move two existing tests which were misplaced in the WHERE
clause testing section into it.

Reported-by: Michał Kłeczek
Reviewed-by: Ashutosh Bapat, Richard Guo
Bug: #18381
Discussion: 
https://postgr.es/m/0714C8B8-8D82-4ABB-9F8D-A0C3657E7B6E%40kleczek.org
Discussion: https://postgr.es/m/18381-137456acd168bf93%40postgresql.org
Backpatch-through: 12, oldest supported version

Branch
--
master

Details
---
https://git.postgresql.org/pg/commitdiff/c399248b3b44bd6cece1ada47eee346b67a88a0a

Modified Files
--
contrib/postgres_fdw/deparse.c | 25 +--
contrib/postgres_fdw/expected/postgres_fdw.out | 93 +++---
contrib/postgres_fdw/sql/postgres_fdw.sql  | 32 +++--
3 files changed, 114 insertions(+), 36 deletions(-)