Optimizing JSON Launch in PostgreSQL 9.0

Question

I am currently using this PostgreSQL JSON acceleration feature as support for future JSON support. Although it works, it also limits the performance of our systems. How can I optimize it? Maybe some sort of search array?

CREATE OR REPLACE FUNCTION escape_json(i_text TEXT) RETURNS TEXT AS $body$ DECLARE idx INTEGER; text_len INTEGER; cur_char_unicode INTEGER; rtn_value TEXT := i_text; BEGIN -- $Rev: $ -- text_len = LENGTH(rtn_value); idx = 1; WHILE (idx <= text_len) LOOP cur_char_unicode = ASCII(SUBSTR(rtn_value, idx, 1)); IF cur_char_unicode > 255 THEN rtn_value = OVERLAY(rtn_value PLACING (E'\\u' || LPAD(UPPER(TO_HEX(cur_char_unicode)),4,'0')) FROM idx FOR 1); idx = idx + 5; text_len = text_len + 5; ELSE /* is the current character one of the following: " \ / bs ff nl cr tab */ IF cur_char_unicode IN (34, 92, 47, 8, 12, 10, 13, 9) THEN rtn_value = OVERLAY(rtn_value PLACING (E'\\' || (CASE cur_char_unicode WHEN 34 THEN '"' WHEN 92 THEN E'\\' WHEN 47 THEN '/' WHEN 8 THEN 'b' WHEN 12 THEN 'f' WHEN 10 THEN 'n' WHEN 13 THEN 'r' WHEN 9 THEN 't' END) ) FROM idx FOR 1); idx = idx + 1; text_len = text_len + 1; END IF; END IF; idx = idx + 1; END LOOP; RETURN rtn_value; END; $body$ LANGUAGE plpgsql; 

Answer 1

All my approaches boil down to "do it differently":

• Write it down in some other language, for example. use pl / perl, pl / python, pl / ruby
• Write a wrapper around some external JSON library written in C
• Running JSON in the client, not in the request (assuming your client has good support for JSON acceleration)

In my experience, pl / pgsql does not work so fast in this area - its strength lies in its inherent support for exchanging data with the database, and not as a general-purpose programming language.

Example:

 create or replace function escape_json_perl(text) returns text strict immutable language plperlu as $$ use JSON; return JSON->new->allow_nonref->encode($_[0]); $$; 

A quick test suggests that it is about 15 times faster than the plpgsql function (although it returns quotes around the value that you probably want to remove)

Answer 2

Confession: I am a Google Summer of Code 2010 student who tried to bring JSON support for PostgreSQL 9.1. Although my code was pretty full-featured, it wasn’t completely upstream ready, and the PostgreSQL developer community was considering some alternative implementations. However, with the spring transition, I hope to finish my rewrite and give it one last push this week.

At the same time, you can download and install the incomplete JSON module of the data type , which should work on PostgreSQL 8.4. 0 and above. This is the PGXS module, so you can compile and install it without compiling the entire PostgreSQL. However, you will need the PostgreSQL server development headers.

Installation takes place like this:

 git clone git://git.postgresql.org/git/json-datatype.git cd json-datatype/ USE_PGXS=1 make sudo USE_PGXS=1 make install psql -f json.sql <DBNAME1> # requires database superuser privileges 

Although you only need to complete the assembly and installation once, json.sql needs to be run in every database on which you intend to use the JSON data type.

With the program installed, you can run:

 => SELECT to_json(E'"quotes and \n newlines"\n'::TEXT); to_json -------------------------------- "\"quotes and \n newlines\"\n" (1 row) 

Note that this does not escape characters other than ASCII.

Answer 3

I found the PostgreSQL function implemented in C here: http://code.google.com/p/pg-to-json-serializer/

I did not compare it with your PLSQL method, but should be faster than any interpreted language.

Another one: http://miketeo.net/wp/index.php/projects/json-functions-for-postgresql