为何PostgreSQL预处理语句中无效ORDER BY占位符未触发报错？

测试环境准备 #

先创建测试表并插入数据：

test# create table t (c int);
CREATE TABLE
test# insert into t (c) values (1), (3), (2);
INSERT 0 3

直接执行含无效ORDER BY的SQL会报错 #

直接执行带字符串常量的ORDER BY语句，触发报错：

test# select c from t order by 'c' desc;
ERROR:  non-integer constant in ORDER BY
LINE 1: select c from t order by 'c' desc;

对应的报错日志：

2024-02-14 11:26:31.734 GMT test user [5047]LOG:  statement: select c from t order by 'c' desc;
2024-02-14 11:26:31.734 GMT test user [5047]ERROR:  non-integer constant in ORDER BY at character 26
2024-02-14 11:26:31.734 GMT test user [5047]STATEMENT:  select c from t order by 'c' desc;

预处理语句绑定无效值无报错，返回无序结果 #

使用服务端预处理语句时，绑定字符串'c'作为ORDER BY参数，执行无报错，但结果未排序：

test# prepare q (varchar) as select c from t order by $1;
PREPARE
test# execute q ('c');
 c 
═══
 1
 3
 2
(3 rows)

对应的日志（无报错信息）：

2024-02-14 15:18:17.626 GMT test user [5047]LOG:  statement: prepare q (varchar) as select c from t order by $1;
2024-02-14 15:18:29.834 GMT test user [5047]LOG:  statement: execute q ('c');
2024-02-14 15:18:29.834 GMT test user [5047]DETAIL:  prepare: prepare q (varchar) as select c from t order by $1;

使用psql的\bind元命令测试，结果一致：

test# select c from t order by $1 desc \bind 'c' \g
 c 
═══
 1
 3
 2
(3 rows)

日志内容：

2024-02-14 15:22:31.123 GMT test user [5047]LOG:  execute <unnamed>: select c from t order by $1 desc 
2024-02-14 15:22:31.123 GMT test user [5047]DETAIL:  parameters: $1 = 'c'

Python驱动的差异现象 #

在Python的PostgreSQL驱动中：

• psycopg2采用客户端参数绑定，执行语句cursor.execute("SELECT * FROM cars ORDER BY %s DESC;", (k,))时，会触发和直接执行SQL一样的报错；
• psycopg3采用服务端参数绑定，执行相同语句时不会报错，但返回未排序的结果。

这两种执行方式的核心差异在于PostgreSQL对ORDER BY子句中常量和参数的处理逻辑不同：

1. 直接执行SQL时的检查：
   当直接执行ORDER BY 'c'时，PostgreSQL解析SQL时会识别到'c'是字符串常量。ORDER BY子句中如果是常量，只能是整数（用于指代列的位置，比如ORDER BY 1表示按第一列排序），非整数常量会直接触发语法校验错误，这就是直接执行时报错的原因。

2. 预处理语句的处理逻辑：
   服务端预处理语句中，$1是参数占位符，PostgreSQL在预处理阶段无法确定参数的具体值，只会做语法结构校验，不会对参数值的合法性做严格校验。当执行阶段传入字符串'c'时，PostgreSQL会将其视为一个常量表达式，所有行的这个表达式值都是相同的'c'，因此排序时所有行的排序键一致，最终返回的结果是无序的（实际上是按数据存储的物理顺序返回），不会触发报错。

3. psycopg2和psycopg3的差异根源：

   • psycopg2的客户端绑定是直接将参数值拼接到SQL语句中，生成的SQL相当于ORDER BY 'c'，所以会触发和直接执行一样的语法错误；
   • psycopg3的服务端绑定是将参数发送给PostgreSQL服务端，由服务端在执行阶段替换占位符，本质和使用PREPARE/EXECUTE语句一致，因此不会报错，返回无序结果。

内容的提问来源于stack exchange，提问作者snakecharmerb