Postgres PL/Java警告 [英] Postgres pl/java caveats

问题描述

我非常希望用Java编写数据库端代码，但想知道与pl/pgsql相比，这样做是否有任何缺点(特别是与性能和错误处理有关).

I would very much prefer writing DB side code in Java but would like to know if doing so has any disadvantages compared to pl/pgsql (especially performance & error handling related).

推荐答案

根据我的经验，pl/java存在一些主要问题:

As of my expirience pl/java has some major issues:

1. 很难将其安装到postgresql服务器中.即使您找到适合您的Postgresql版本的二进制版本(这也很困难)，它也需要一些配置和库路径处理.
2. 首次调用java存储过程将导致一个新的JVM进程. JVM进程受连接作用域的限制，并且需要一定数量的内存用于Java堆，因此，如果使用连接池，最终将导致10-20个JVM启动并在大多数时间未使用，这会消耗服务器RAM.
3. pl/java可以使用自制的JDBC驱动程序与PostgreSQL数据库进行通信，该驱动程序可以模拟常见的JDBC使用，但是存在一些实现问题，这可能会让您感到惊讶.尤其是如果您想使用JDBC cusror或其他不太常见的东西.
4. pl/java日志记录非常特殊-这是由于postgresql内部错误处理实现.例如-如果您在ERROR日志级别使用Java日志记录api记录内容-它将终止您的服务器连接.
5. pl/java与基于应用程序服务器的java逻辑相比具有非常好的数据处理性能，但是它是完全不可扩展的-pl/java过程是完全单线程的-postgresql禁止使用多线程过程
6. 如果您使用内部JDBC驱动程序，并且您的SQL语句包含错误-您将在postgresql日志中得到神秘的错误消息-完全与实际问题无关.

1. It's difficult to install it into a postgresql server. Even if you find binary build for your postgresql version (which is difficult too) - it needs some config and library path juggling.
2. First call to java stored procedure will result in a new JVM process. JVM processes are connection-scoped and require some amount of memory for java heap, so if you use connection pool you'll end up with 10-20 JVMs started and unused most of time, consuming your server RAM
3. pl/java can communicate with postgresql database using self-made JDBC driver which emulates common JDBC usage, but has some implementation issues which may surprise you. Especially if you want to use JDBC cusrors or other not-so-common things.
4. pl/java logging is rather special - it's because of postgresql internal error handling implementation. For example - if you log something with java logging api at ERROR log level - it will terminate your server connection.
5. pl/java has very good data processing performance compared to application-server based java logic, but it's totally unscalable - pl/java procedures are fully single-threaded - postgresql forbids multi-threaded procedures
6. If you use internal JDBC driver and your SQL statement contains errors - you'll end up with cryptic error message in postgresql log - totally unrelated to real problem.

结果-您可以成功使用pl/java过程，但是您需要非常小心，并且可能需要考虑改善应用程序设计.

As result - you can use pl/java procedures with some success but you need to do it very carefully and probably you need to think about improving your application design.

这篇关于Postgres PL/Java警告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！