1

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).

Improve this question

2 Answers 2

Reset to default
5

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

  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.

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You can actually get away with multi-threaded PL/Java, but you have to be incredibly careful never to call into any PostgreSQL APIs - even logging - from multiple threads. So in practice, yeah, single threaded.
3

Usually the only major difference is with round-trips. If your Java code has a low-latency connection to the DB, you should find client-side logic to perform quite well.

Update: Ian says you mean PL/Java, i.e in-database execution. If that's what you meant, I would not recommend PL/Java. It is nearly unmaintained; also, the Java model (heavy on threads, big expensive startup) isn't a great match for PostgreSQL's (processes not threads, very lightweight startup).

It'd be great to have people committing work to PL/Java, but right now there's not much happening.

Improve this answer

2 Comments

He is talking about pl/java so it is on DB side. No RTT.
Ah. Different interpretation of "DB side".

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.