Re: [GENERAL] Questions regarding JSON processing

[Pavel Stehule]

2017-04-26 15:06 GMT+02:00 Glen Huang :

> @Pavel
>
> Thanks for bringing PLV8 to my attention. Wasn't aware of it. Sounds like
> the right tool to for the job. I'll try it out. Do you think it makes sense
> to use PLV8 to also generate JSON? Can it beat SQL?
>

Hard to say - probably it depends on actual case. I have not any
benchmarks.

Regards

Pavel


>
> Good to know functions are executed under transaction, I think that should
> be enough for me.
>
> @John
>
> Only data is inside JSON, but it does have keys like "added", "updated"
> that contain objected to be added and updated inside it. I think this kind
> of branching should be safe though?
>
> On Wed, Apr 26, 2017 at 12:41 PM, Pavel Stehule 
> wrote:
>
>>
>>
>> 2017-04-26 6:21 GMT+02:00 Glen Huang :
>>
>>> Hi all,
>>>
>>> I have a RESTful API server that sends and receives JSON strings. I'm
>>> wondering what might be the best way to leverage PostgreSQL's JSON
>>> capability.
>>>
>>> For sending JSON responses to clients. I believe the best way is to ask
>>> PostgreSQL to generate the JSON string and then pass that directly to
>>> clients, instead of making multiple queries to construct the JSON and then
>>> send it,  which doesn't seem optimal. Is that the case?
>>>
>>> For updating db using JSON requests from clients, that I'm not so sure.
>>> Should I directly pass the request JSON to PostgreSQL and ask it to parse
>>> this JSON and execute a transaction all by itself, or should I parse it in
>>> the server and generate the transaction SQL and execute that on PostgreSQL?
>>> The former sounds optimal, but I'm not sure if PostgreSQL is able to walk a
>>> JSON structure and run a transaction along the way? Should I do it with
>>> PL/pgSQL? It seems functions can't execute a transaction?
>>>
>>
>> The PLpgSQL is static language and is good for static processing JSON
>> doc, but it is unfit for iteration over any generic nested document. You
>> can use PLPerlu, PLPythonu. Lot of people uses PLV8 for JSON processing.
>>
>> The functions in PostgreSQL are executed under transaction - you cannot
>> to explicitly control transaction, but there are possibility to implicitly
>> handle transactions with exception handling. There is workaround via dblink
>> to emulate autonomous transactions.
>>
>> Regards
>>
>> Pavel
>>
>>
>>>
>>> Would like to hear some thoughts on this. Thanks.
>>>
>>> Glen
>>>
>>
>>
>

Re: [GENERAL] Questions regarding JSON processing

[Glen Huang]

@Pavel

Thanks for bringing PLV8 to my attention. Wasn't aware of it. Sounds like the 
right tool to for the job. I'll try it out. Do you think it makes sense to use 
PLV8 to also generate JSON? Can it beat SQL?

Good to know functions are executed under transaction, I think that should be 
enough for me.

@John

Only data is inside JSON, but it does have keys like "added", "updated" that 
contain objected to be added and updated inside it. I think this kind of 
branching should be safe though?

On Wed, Apr 26, 2017 at 12:41 PM, Pavel Stehule > wrote:


2017-04-26 6:21 GMT+02:00 Glen Huang >:
Hi all,

I have a RESTful API server that sends and receives JSON strings. I'm wondering 
what might be the best way to leverage PostgreSQL's JSON capability.

For sending JSON responses to clients. I believe the best way is to ask 
PostgreSQL to generate the JSON string and then pass that directly to clients, 
instead of making multiple queries to construct the JSON and then send it,  
which doesn't seem optimal. Is that the case?

For updating db using JSON requests from clients, that I'm not so sure. Should 
I directly pass the request JSON to PostgreSQL and ask it to parse this JSON 
and execute a transaction all by itself, or should I parse it in the server and 
generate the transaction SQL and execute that on PostgreSQL? The former sounds 
optimal, but I'm not sure if PostgreSQL is able to walk a JSON structure and 
run a transaction along the way? Should I do it with PL/pgSQL? It seems 
functions can't execute a transaction?

The PLpgSQL is static language and is good for static processing JSON doc, but 
it is unfit for iteration over any generic nested document. You can use 
PLPerlu, PLPythonu. Lot of people uses PLV8 for JSON processing.

The functions in PostgreSQL are executed under transaction - you cannot to 
explicitly control transaction, but there are possibility to implicitly handle 
transactions with exception handling. There is workaround via dblink to emulate 
autonomous transactions.

Regards

Pavel
 

Would like to hear some thoughts on this. Thanks.

Glen

Re: [GENERAL] Questions regarding JSON processing

[John R Pierce]

On 4/25/2017 9:21 PM, Glen Huang wrote:
For updating db using JSON requests from clients, that I'm not so 
sure. Should I directly pass the request JSON to PostgreSQL and ask it 
to parse this JSON and execute a transaction all by itself, or should 
I parse it in the server and generate the transaction SQL and execute 
that on PostgreSQL? The former sounds optimal, but I'm not sure 
if PostgreSQL is able to walk a JSON structure and run a transaction 
along the way? Should I do it with PL/pgSQL? It seems functions can't 
execute a transaction?


what does "walk a JSON structure and run a transaction along the way"   
actual entail?Normally, the application starts a transaction, 
executes one or more SQL commands, then does a commit or rollback.   any 
JSON would be within this transaction. functions are called within a 
transaction.


If your JSON includes instructions as well as data, I'd be rather 
cautious of letting a remote client send that directly to the database 
server unless you can ensure that nothing hostile can be done with it, 
or completely trust all clients.



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Questions regarding JSON processing

[Pavel Stehule]

2017-04-26 6:21 GMT+02:00 Glen Huang :

> Hi all,
>
> I have a RESTful API server that sends and receives JSON strings. I'm
> wondering what might be the best way to leverage PostgreSQL's JSON
> capability.
>
> For sending JSON responses to clients. I believe the best way is to ask
> PostgreSQL to generate the JSON string and then pass that directly to
> clients, instead of making multiple queries to construct the JSON and then
> send it,  which doesn't seem optimal. Is that the case?
>
> For updating db using JSON requests from clients, that I'm not so sure.
> Should I directly pass the request JSON to PostgreSQL and ask it to parse
> this JSON and execute a transaction all by itself, or should I parse it in
> the server and generate the transaction SQL and execute that on PostgreSQL?
> The former sounds optimal, but I'm not sure if PostgreSQL is able to walk a
> JSON structure and run a transaction along the way? Should I do it with
> PL/pgSQL? It seems functions can't execute a transaction?
>

The PLpgSQL is static language and is good for static processing JSON doc,
but it is unfit for iteration over any generic nested document. You can use
PLPerlu, PLPythonu. Lot of people uses PLV8 for JSON processing.

The functions in PostgreSQL are executed under transaction - you cannot to
explicitly control transaction, but there are possibility to implicitly
handle transactions with exception handling. There is workaround via dblink
to emulate autonomous transactions.

Regards

Pavel


>
> Would like to hear some thoughts on this. Thanks.
>
> Glen
>

[GENERAL] Questions regarding JSON processing

[Glen Huang]

Hi all,

I have a RESTful API server that sends and receives JSON strings. I'm
wondering what might be the best way to leverage PostgreSQL's JSON
capability.

For sending JSON responses to clients. I believe the best way is to ask
PostgreSQL to generate the JSON string and then pass that directly to
clients, instead of making multiple queries to construct the JSON and then
send it,  which doesn't seem optimal. Is that the case?

For updating db using JSON requests from clients, that I'm not so sure.
Should I directly pass the request JSON to PostgreSQL and ask it to parse
this JSON and execute a transaction all by itself, or should I parse it in
the server and generate the transaction SQL and execute that on PostgreSQL?
The former sounds optimal, but I'm not sure if PostgreSQL is able to walk a
JSON structure and run a transaction along the way? Should I do it with
PL/pgSQL? It seems functions can't execute a transaction?

Would like to hear some thoughts on this. Thanks.

Glen