Hibernate / Postgresql -协议版本3/2准备好的语句/参数

Question

向我的Postgres db(9.2.3)(协议版本3)发出以下hibernate hql:

select  count(*)  , obj1.type , (obj1.creationTime + :p1 )  
from fr.xxx.WorkflowPojo obj1 
group by obj1.type  , (obj1.creationTime + :p1 )  
order by  (obj1.creationTime + :p1 ) asc

get: ERROR: column "workflow.creation_time" must appear in the GROUP BY clause or be used in an aggregate function

而我得到的协议版本2没有异常

在pgAdmin以下请求是ok的，所以我猜这不是PgSql问题:

PREPARE test2(varchar) as 
select  count(*)  , 
workflow.optype, (workflow.creation_time + $1::integer )
from workflow
group by workflow.optype, (workflow.creation_time + $1::integer )
order by  (workflow.creation_time + $1::integer ) asc 

谁有一个想法如何解决这个问题，如果我想使用PostgreSQL协议版本3?

edit:谢谢Craig的帮助。这是我从postgreSQL的角度得到的:

VERSION3:

    STATEMENT:  select count(*) as col_0_0_, workflowdd0_.optype as col_1_0_,       workflowdd0_.creation_time+$1 as col_2_0_ 
    from workflow_ddc workflowdd0_ 
    group by workflowdd0_.optype , workflowdd0_.creation_time+$2 
    order by workflowdd0_.creation_time+$3

VERSION 2:

    LOG:  statement: select count(*) as col_0_0_, workflowdd0_.optype as col_1_0_, workflowdd0_.creation_time+3600 as col_2_0_ 
    from workflow_ddc workflowdd0_ 
    group by workflowdd0_.optype , workflowdd0_.creation_time+3600 
    order by workflowdd0_.creation_time+3600 asc

从hibernate的角度:

    Hibernate: select count(*) as col_0_0_, workflowdd0_.optype as col_1_0_, workflowdd0_.creation_time+? as col_2_0_ 
    from workflow_ddc workflowdd0_ 
    group by workflowdd0_.optype , workflowdd0_.creation_time+? 
    order by workflowdd0_.creation_time+? asc

对于版本3的服务器端准备语句，它将所有的$1替换为$1 $2 $3 $4…在版本2中，它取代了客户端字符串。

可能是jdbc驱动程序的bug ?它应该在任何地方都是1美元……

谢谢Christophe

Answer 1

这看起来像是JDBC API限制与PostgreSQL对GROUP BY子句相当严格的结合。

关键的区别在于您的手动PgAdmin测试使用一个单个参数，并在查询中使用它两次。相比之下，Hibernate查询将该值作为两个单独的参数传递两次。在PREPARE时间，PostgreSQL不能证明$1总是等于$2，即使在实践中你知道他们会，所以PostgreSQL拒绝计划查询。

问题的演示

演示设置:

CREATE TABLE somedemo( x integer, y integer );
INSERT INTO somedemo(x,y) SELECT a,a from generate_series(1,15) a;

演示1，文本替换，工作良好:

SELECT x, (y+1) FROM somedemo GROUP BY x, y+1;

演示2，单参数，工作良好，因为Pg可以证明一个地方的(y+$1)总是等于另一个地方的(y+$1):

PREPARE preptest1(integer) AS select x, (y+$1) from somedemo GROUP BY x, y+$1;
EXECUTE preptest1(1);

Demo 3，两个参数。失败，因为Pg不能证明(y+$1)在PREPARE时刻等于(y+$2):

regress=> PREPARE preptest2(integer,integer) AS SELECT x, (y+$1) FROM somedemo GROUP BY x, y+$2;
ERROR:  column "somedemo.y" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: PREPARE preptest2(integer,integer) AS SELECT x, (y+$1) FROM ...
                                                         ^

当您强制协议级别2时，此操作有效，因为JDBC驱动程序替换了服务器端参数。

其他语言如何处理

在Python+psycopg2或其他具有更复杂数据库驱动程序的语言中，我将使用命名或位置参数来处理此问题:

$ python
Python 2.7.3 (default, Aug  9 2012, 17:23:57) 
>>> import psycopg2
>>> conn = psycopg2.connect('')
>>> curs = conn.cursor()
>>> curs.execute("SELECT x, (y+%(parm)s) FROM somedemo GROUP BY x, y+%(parm)s", { 'parm': 1 })
>>> curs.fetchall()
[(15, 16), (3, 4), (12, 13), (14, 15), (10, 11), (11, 12), (8, 9), (5, 6), (13, 14), (1, 2), (2, 3), (4, 5), (7, 8), (9, 10), (6, 7)]
>>> 

不幸的是，JDBC似乎只支持CallableStatement中的命名参数;我们又一次看到Java遗留问题的痛苦来咬我们。

为什么修复它不简单

为了处理这个服务器端，PostgreSQL必须延迟规划这些语句，直到它得到参数，然后将其作为常规的临时查询执行。目前还不支持这样做，尽管已经通过引入预处理语句重规划奠定了一些基础。

我们不清楚如何在JDBC驱动端透明地处理它。即使我们延迟发送准备好的语句，直到我们得到第一组参数，我们也不知道"$1"总是等于"$2"(可以组合)，仅仅因为它们在第一次执行时是相等的…

Hibernate不能修复这个问题;它知道:p1在所有三个位置都是相同的参数，但是由于JDBC位置参数接口的限制，它没有办法告诉PostgreSQL。它可以将所有参数替换为查询文本，但这几乎总是错误的，这是一个相当不寻常的角落案例。

我看到的唯一可靠的修复是PgJDBC使用命名或顺序参数扩展JDBC，如?:p1或?:1。然后Hibernate的PostgreSQL方言可以被扩展来支持它们。为了避免兼容性问题，有必要设置一个连接参数，以便启用扩展参数语法。这一切似乎都很痛苦，所以我宁愿等到JDBC规范添加了真正的命名参数支持(即:不要屏住呼吸，你的孙子可能会看到它发生)，或者只是解决这个问题。

工作区

我认为最好的选择是使用子查询生成一个具有生成值的虚拟表，然后在外部查询中按它分组。执行此操作的SQL如下所示:

SELECT x, y_plus FROM (
  SELECT x, (y+?) FROM somedemo
) temptable(x,y_plus)
GROUP BY x, y_plus;

这个短语只需要对参数进行一次引用。将其翻译为HQL留给读者作为练习;-)。

PostgreSQL的查询优化器通常会将此转换为与简单的字符串替换形式一样有效的计划，如下所示:

regress=> PREPARE preptest5(integer) AS SELECT x, y_plus FROM (SELECT x, (y+$1) FROM somedemo) temptable(x,y_plus) GROUP BY x, y_plus;
regress=> explain EXECUTE preptest5(1);
                          QUERY PLAN                           
---------------------------------------------------------------
 HashAggregate  (cost=1.26..1.45 rows=15 width=8)
   ->  Seq Scan on somedemo  (cost=0.00..1.19 rows=15 width=8)
(2 rows)
regress=> explain SELECT x, y+1 FROM somedemo GROUP BY x, y+1;
                          QUERY PLAN                           
---------------------------------------------------------------
 HashAggregate  (cost=1.26..1.45 rows=15 width=8)
   ->  Seq Scan on somedemo  (cost=0.00..1.19 rows=15 width=8)
(2 rows)

对于非性能关键的临时函数或不经常使用的函数，您可以通过编写一个本机查询来解决这个问题，该查询在CTE VALUES子句中传递一次参数，例如:

PREPARE preptest3(integer) AS 
WITH params(a) AS (VALUES($1)) 
SELECT x, (y+a) FROM somedemo CROSS JOIN params GROUP BY x, y+a;
EXECUTE preptest3(1);

不用说，这是笨拙的，可能不会表现得特别好，但它在必须在许多不同的上下文中引用参数的情况下是有效的。

如果您不能使用HQL中先前设置的子查询表方法，那么一个更好的替代方法是将查询封装在SQL函数中，然后从JDBC调用该函数，例如:

-- Define this in your database schema or run it on app startup:
CREATE OR REPLACE FUNCTION test4(integer) RETURNS TABLE (x integer, y integer) AS $$
    SELECT x, (y+$1) FROM somedemo GROUP BY x, y+$1;
$$ LANGUAGE sql;
-- then in JDBC prepare a simple "SELECT * FROM test4(?)", resulting in:
PREPARE preptest4(integer) AS SELECT * FROM test4($1);
EXECUTE preptest4(1);

Answer 2

说明，最新版本的postgresql不再支持V2协议。参数preferQueryMode可以用来强制jdbc驱动程序"inline"发送值，而不是指定参数