plv8 disadvantages or limitations?

Question

I'm playing around with PLV8 to write trigger and stored procedures for PostgreSQL. So far I don't really see disadvantages compared to PLPGSQL. Especially if working with JSON it seems even smarter then PLPGSQL. Are there known disadvantages or limitations if using PLV8? Can PLV8 be a full replacement for PLPGSQL? It would be great if somebody could share his experience on this.

Answer 1

The advantages and disadvantages of PLV8 are same as advantages and disadvantages of PLPerl, PLPython and other PL languages.

• It is not integrated with PostgreSQL engine - the processing SQL statements result can be slower. PLpgSQL is fully integrated to PostgreSQL engine.
• SQL is not integrated to language - isn't possible to do static analyse of embedded SQL. It is possible with PLpgSQL - see plpgsql_check.
• Can do better expensive mathematical calculations, a manipulations with strings and arrays are usually faster than in PLpgSQL.
• Can use libraries developed for languages - Perl - CPAN, ...
• JavaScript, Perl, Python are generic languages - so any generic tasks are implemented there well.
• PLpgSQL is mature language designed for manipulation with data in relation database environment. Almost all what developer needs for data work with data is there. The iteration over result, taking data from database needs less more readable code.

PLpgSQL is perfect language for data manipulation via SQL language. Other PL is better for anything else - IO, Network, special formatting, slow numeric calculations, ...

Answer 2

To list some more disadvantages of PLpgSQL, mostly in the area of dev velocity:

• Syntax errors are difficult to decipher, of the form mismatched parentheses at or near ";". I'm not getting a line number or any useful context.
• No lint, prettier, editor support or other useful utilities that most languages have.
• Yet another language to learn with lots of idiosyncrasies.
• Trying to create a JSON object to call an external API is very difficult and error-prone. Many specialized functions like json_build_object, json_build_array, etc. Instead of just {a: 2, b: 'foo', c} in JS.

Answer 3

UPDATE: since PLV8 2.3.3 (2018-04-27) supports kill signals and works pretty good. Disadvantage - you have to build it yourself. We have not migrated our code to python as we had no issues for the last 3 years.

A little bit late, but you cannot kill query currently running plv8 script, the only way is to restart the whole postgresql server. It is a huge disadvantage and our team is thinking about migrating to PLpgPython.