Re: JSON for PG 9.2

Where are we with adding JSON for Postgres 9.2? We got bogged down in
the data representation last time we discussed this.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Mon, Dec 5, 2011 at 3:12 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Where are we with adding JSON for Postgres 9.2?  We got bogged down in
> the data representation last time we discussed this.

We're waiting for you to send a patch that resolves all
previously-raised issues. :-)

In all seriousness, I think the right long-term answer here is to have
two data types - one that simply validates JSON and stores it as text,
and the other of which uses some binary encoding. The first would be
similar to our existing xml datatype and would be suitable for cases
when all or nearly all of your storage and retrieval operations will
be full-column operations, and the json types is basically just
providing validation. The second would be optimized for pulling out
(or, perhaps, replacing) pieces of arrays or hashes, but would have
additional serialization/deserialization overhead when working with
the entire value. As far as I can see, these could be implemented
independently of each other and in either order, but no one seems to
have yet found the round tuits.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Dec 12, 2011 at 7:58 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Dec 5, 2011 at 3:12 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> Where are we with adding JSON for Postgres 9.2?  We got bogged down in
>> the data representation last time we discussed this.
>
> We're waiting for you to send a patch that resolves all
> previously-raised issues.  :-)
>
> In all seriousness, I think the right long-term answer here is to have
> two data types - one that simply validates JSON and stores it as text,
> and the other of which uses some binary encoding.  The first would be
> similar to our existing xml datatype and would be suitable for cases
> when all or nearly all of your storage and retrieval operations will
> be full-column operations, and the json types is basically just
> providing validation.  The second would be optimized for pulling out
> (or, perhaps, replacing) pieces of arrays or hashes, but would have
> additional serialization/deserialization overhead when working with
> the entire value.  As far as I can see, these could be implemented
> independently of each other and in either order, but no one seems to
> have yet found the round tuits.

Rather than fuss with specific data formats, why not implement
something a little more useful?

At present we can have typmods passed as a cstring, so it should be
possible to add typmods onto the TEXT data type.

e.g. TEXT('JSON'), TEXT('JSONB')

We then invent a new catalog table called pg_text_format which has
oid PRIMARY KEY
textformatname UNIQUE
textformatvalidationproc
textformatstorageproc

The typmod must reduce to a single integer, so we just store the
integer. If no typmod, we store 0, so we have a fastpath for normal
TEXT datatypes.

This would then allow people to have variations of the TEXT type that
supports conversions, casts, indexing etc without additional fuss and
without everything else outside the database breaking because it
doesn't know that datatype name.

We could then support JSON (both kinds), YAML, etc
as well as providing a way to add validation into the datatype itself.
We can replace citext with TEXT('CASE_INSENSITIVE')

Think of this as using the object-relational capabilities of Postgres
to extend the TEXT data type.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Dec 12, 2011 at 3:38 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Rather than fuss with specific data formats, why not implement
> something a little more useful?
>
> At present we can have typmods passed as a cstring, so it should be
> possible to add typmods onto the TEXT data type.
>
> e.g. TEXT('JSON'), TEXT('JSONB')
>
> We then invent a new catalog table called pg_text_format which has
> oid PRIMARY KEY
> textformatname UNIQUE
> textformatvalidationproc
> textformatstorageproc
>
> The typmod must reduce to a single integer, so we just store the
> integer. If no typmod, we store 0, so we have a fastpath for normal
> TEXT datatypes.
>
> This would then allow people to have variations of the TEXT type that
> supports conversions, casts, indexing etc without additional fuss and
> without everything else outside the database breaking because it
> doesn't know that datatype name.
>
> We could then support JSON (both kinds), YAML, etc
> as well as providing a way to add validation into the datatype itself.
> We can replace citext with TEXT('CASE_INSENSITIVE')
>
> Think of this as using the object-relational capabilities of Postgres
> to extend the TEXT data type.

Well, it's arguable that text-format JSON and YAML and our existing
XML datatype ought to share some structure with text, but
binary-format JSON is a different animal altogether; you might as well
propose having text('int8'). In any case, I doubt that trying to make
the typmod provide subclassing behavior is going to work out very
well. There are way too many places that assume that the typmod can
just be discarded. I don't think that's going to fly, because
=(text,text) probably has different semantics from =(json,json).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Dec 12, 2011 at 8:54 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> There are way too many places that assume that the typmod can
> just be discarded.

If true, that probably ought to be documented cos it sounds fairly important.

Where and when is it true?

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Dec 12, 2011 at 4:08 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Mon, Dec 12, 2011 at 8:54 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> There are way too many places that assume that the typmod can
>> just be discarded.
>
> If true, that probably ought to be documented cos it sounds fairly important.
>
> Where and when is it true?

I'm not going to go compile an exhaustive list, since that would take
a week and I don't have any particular desire to invest that much time
in it, but just to take a couple of simple examples:

rhaas=# create or replace function wuzzle(numeric(5,2)) returns int as
$$select 1$$ language sql;
CREATE FUNCTION
rhaas=# \df wuzzle
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------+------------------+---------------------+--------
public | wuzzle | numeric | | normal
public | wuzzle | integer | numeric | normal
(2 rows)

rhaas=# select pg_typeof(1.23::numeric(5,2));
pg_typeof
-----------
numeric
(1 row)

There are a very large number of others. Possibly grepping for places
where we do getBaseType() rather than getBaseTypeAndTypmod() would be
a way to find some of them.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On mån, 2011-12-12 at 21:08 +0000, Simon Riggs wrote:
> On Mon, Dec 12, 2011 at 8:54 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> > There are way too many places that assume that the typmod can
> > just be discarded.
>
> If true, that probably ought to be documented cos it sounds fairly important.
>
> Where and when is it true?

Function arguments and return values, for example.

On Dec 12, 2011, at 12:54 PM, Robert Haas wrote:

> I don't think that's going to fly, because
> =(text,text) probably has different semantics from =(json,json).

No question:

david=# select '{"foo": 1, "bar": 2}'::json = '{"bar": 2, "foo": 1}'::json;
?column?
----------
t
(1 row)

Best,

David

On 12/12/2011 03:54 PM, Robert Haas wrote:
> On Mon, Dec 12, 2011 at 3:38 PM, Simon Riggs<simon(at)2ndquadrant(dot)com> wrote:
>> Rather than fuss with specific data formats, why not implement
>> something a little more useful?
>>
>> At present we can have typmods passed as a cstring, so it should be
>> possible to add typmods onto the TEXT data type.
>>
>> e.g. TEXT('JSON'), TEXT('JSONB')
>>

[...]
> There are way too many places that assume that the typmod can
> just be discarded. I don't think that's going to fly, because
> =(text,text) probably has different semantics from =(json,json).
>

And certain places where they are not allowed at all, I think (unless I
am misremembering the early debates about enum types and output functions).

cheers

andrew

Bruce,

I thought that Joseph Adams was still working on this, sponsored by
Heroku. Joseph?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

We reached out to Joseph to see if we could help sponsor the project,
but never really heard back from him.

Because we haven't heard from him in a while we've been using PL/V8 to
validate a JSON datatype simulated by a DOMAIN with a simple
acceptance function. (See below.) This is not ideally performant but
thanks to V8's JIT the JSON parser is actually reasonably good.

I think releasing something simple and non-performant with reasonable
semantics would be the best next step. If it were up to me, I'd
probably even try to just land PL/V8 as PL/JavaScript for 9.2 if the
crash bugs and deal breakers can be sifted out.

PL/V8 is fast, it's sandboxed, and while it doesn't provide GIN or
GIST operators out of the box, maybe those could be motivated by its
inclusion.

Andrew, you've been down in the guts here, what do you think?

-pvh

(Code sample.)

create or replace function valid_json(json text)
returns bool as $$
try { JSON.parse(json); return true }
catch(e) { return false}
$$ LANGUAGE plv8 IMMUTABLE STRICT;

select valid_json('{"key": "value"}'), valid_json('lol');
valid_json | t
valid_json | f
Time: 0.283 ms

create domain json
as text check(valid_json(VALUE));
create table jsononly(data json);

insert into jsononly values 'lol';
ERROR: syntax error at or near "'lol'"
LINE 1: insert into jsononly values 'lol';

insert into jsononly
values ('{"ok": true}');
INSERT 0 1

-p

On Mon, Dec 12, 2011 at 1:34 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Bruce,
>
> I thought that Joseph Adams was still working on this, sponsored by
> Heroku.  Joseph?
>
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt." -- Kurt Vonnegut

On Dec 12, 2011, at 4:51 PM, Peter van Hardenberg wrote:

> Because we haven't heard from him in a while we've been using PL/V8 to
> validate a JSON datatype simulated by a DOMAIN with a simple
> acceptance function. (See below.) This is not ideally performant but
> thanks to V8's JIT the JSON parser is actually reasonably good.
>
> I think releasing something simple and non-performant with reasonable
> semantics would be the best next step. If it were up to me, I'd
> probably even try to just land PL/V8 as PL/JavaScript for 9.2 if the
> crash bugs and deal breakers can be sifted out.

Note that Claes Jakobsson has been working on a JSON data type using the Jansson JSON library.

http://pgxn.org/dist/pg-json/

I’ve submitted a pull request renaming it to jansson-json (though the data type is still "json"):

https://github.com/theory/pg-json/tree/pgxn

Anyway, it seems like a decent start to an extensible type implemented entirely as an extension. Claes tells me he plans to add index support soonish, so it could get to be pretty robust before long.

Just another stab at the problem to alert folks to.

Best,

David

On 12/12/2011 07:51 PM, Peter van Hardenberg wrote:
> We reached out to Joseph to see if we could help sponsor the project,
> but never really heard back from him.
>
> Because we haven't heard from him in a while we've been using PL/V8 to
> validate a JSON datatype simulated by a DOMAIN with a simple
> acceptance function. (See below.) This is not ideally performant but
> thanks to V8's JIT the JSON parser is actually reasonably good.
>
> I think releasing something simple and non-performant with reasonable
> semantics would be the best next step. If it were up to me, I'd
> probably even try to just land PL/V8 as PL/JavaScript for 9.2 if the
> crash bugs and deal breakers can be sifted out.
>
> PL/V8 is fast, it's sandboxed, and while it doesn't provide GIN or
> GIST operators out of the box, maybe those could be motivated by its
> inclusion.
>
> Andrew, you've been down in the guts here, what do you think?

The trouble with using JSON.parse() as a validator is that it's probably
doing way too much work. PLV8 is cool, and I keep trying to get enough
time to work on it more, but I don't think it's a substitute for a JSON
type with a purpose built validator and some native operations. I think
these efforts can continue in parallel.

cheers

andrew

On Mon, Dec 12, 2011 at 4:51 PM, Peter van Hardenberg <pvh(at)pvh(dot)ca> wrote:>
> PL/V8 is fast, it's sandboxed, and while it doesn't provide GIN or
> GIST operators out of the box, maybe those could be motivated by its
> inclusion.

I also feel that a big problem with JSON as a data type is that there
is not a powerful, common navigation method. JSON path is basically
pretty obscure by comparison to XPath. As a result, the common
approach to navigation in a JSON structure is basically "write
procedures". And that is only perfectly supported by a full-blown
interpreter.

So that's why I'm personally more inclined to lend my attention to
embedding JavaScript entirely. Not to say there aren't areas ripe for
improvement:

* It'd be nice to pass intermediate in-memory representations rather
than calling JSON.parse all the time, similar to OPAQUE except sound
(so bogus pointers cannot be passed). Basically, an "ephemeral type".
It could save a lot of when composing operators. I've needed this
for other projects, but for much the same reason.

* It'd be nice to be able to safely define indexes in a trusted
language somehow, writing the penalty and split functions, et al.
Right now it's my recollection that defining GiST operators in a naive
port to Javascript would give you the power to return garbage that is
not merely wrong, but could also crash Postgres if it uses a bogus
indexes. Ready and willing to be corrected*

* Some kind of partial toasting of large datums (I think Simon Riggs
briefly glossed over such an idea when we were talking about this
general use case)

But nothing I can quickly identify in the Postgres as-is is opposed to
any of these improvements at a design level, so they can be chipped
off into incremental work in the future.

--
fdr

* Madness, you say? http://bellard.org/jslinux/, if your browser is
new enough. The relevant spec:
https://www.khronos.org/registry/typedarray/specs/latest/

On Mon, Dec 12, 2011 at 5:36 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> The trouble with using JSON.parse() as a validator is that it's probably
> doing way too much work. PLV8 is cool, and I keep trying to get enough time
> to work on it more, but I don't think it's a substitute for a JSON type with
> a purpose built validator and some native operations. I think these efforts
> can continue in parallel.

Hmm. Maybe? While I'm sure things could be faster, we've had results
that are fast enough to be usable even with constant reparsing. Here
are some microbenchmarks I did some time ago where I tried to find the
overhead of calling JSON.parse and doing some really simple stuff in
V8 that I thought would maximize the amount of constant-time overhead:

https://gist.github.com/1150804

On my workstation, one core was able to do 130,000 JSON.parses + other
stuff necessary to create an index per second. One could maybe try to
improve the speed and memory footprint on large documents by having
validators that don't actually build the V8 representation and
possibly defining a space of operators that are known to build, by
induction, valid JSON without rechecks.

But in the end, I think there's already a class of problem where the
performance plv8 provides is already quite sufficient, and provides a
much more complete and familiar approach to the problem of how people
choose to navigate, project, and manipulate JSON documents.

I also haven't tried this for larger documents, as I was trying to get
a sense of how much time was spent in a few primitive operations, and
not testing performance with regard to document length.

--
fdr

On 12/12/2011 08:46 PM, Daniel Farina wrote:
> On Mon, Dec 12, 2011 at 5:36 PM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>> The trouble with using JSON.parse() as a validator is that it's probably
>> doing way too much work. PLV8 is cool, and I keep trying to get enough time
>> to work on it more, but I don't think it's a substitute for a JSON type with
>> a purpose built validator and some native operations. I think these efforts
>> can continue in parallel.
> Hmm. Maybe? While I'm sure things could be faster, we've had results
> that are fast enough to be usable even with constant reparsing. Here
> are some microbenchmarks I did some time ago where I tried to find the
> overhead of calling JSON.parse and doing some really simple stuff in
> V8 that I thought would maximize the amount of constant-time overhead:
>
> https://gist.github.com/1150804
>
> On my workstation, one core was able to do 130,000 JSON.parses + other
> stuff necessary to create an index per second. One could maybe try to
> improve the speed and memory footprint on large documents by having
> validators that don't actually build the V8 representation and
> possibly defining a space of operators that are known to build, by
> induction, valid JSON without rechecks.
>
> But in the end, I think there's already a class of problem where the
> performance plv8 provides is already quite sufficient, and provides a
> much more complete and familiar approach to the problem of how people
> choose to navigate, project, and manipulate JSON documents.
>
> I also haven't tried this for larger documents, as I was trying to get
> a sense of how much time was spent in a few primitive operations, and
> not testing performance with regard to document length.
>

Yes, I didn't mean to say it's not fast. For many cases I agree it is
probably fast enough.

But in the end PLV8 is likely to remain an addon - nice as it can be I
doubt the core team will want to add another PL to the core code,
especially one written in C++. If we want a JSON type built in, which
many people seem to want, we'll need to do it without the support of
PLV8, I think.

cheers

andrew

Excerpts from Daniel Farina's message of lun dic 12 22:37:13 -0300 2011:

> * It'd be nice to pass intermediate in-memory representations rather
> than calling JSON.parse all the time, similar to OPAQUE except sound
> (so bogus pointers cannot be passed). Basically, an "ephemeral type".
> It could save a lot of when composing operators. I've needed this
> for other projects, but for much the same reason.

I remember there was the idea of doing something like this for regexes
-- have a specialized data type that saves the trouble of parsing it.
I imagine this is pretty much the same.

Nobody got around to doing anything about it though.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 12/12/2011 03:54 PM, Robert Haas wrote:
>> There are way too many places that assume that the typmod can
>> just be discarded. I don't think that's going to fly, because
>> =(text,text) probably has different semantics from =(json,json).

> And certain places where they are not allowed at all, I think (unless I
> am misremembering the early debates about enum types and output functions).

Yeah. The current system design assumes that typmod specifies a
constraint of some sort. It is not possible to use it to change the
semantics of the datatype. The most obvious way in which this is true
is that selection of which operators and functions to apply to values
does not consider typmod of the values. This is not something we should
lightly revisit. We don't even have a handle on how to make domains
behave differently from their underlying datatypes, and those *do* have
their own type OIDs. Injecting typmod into the algorithm seems like a
disaster from here.

regards, tom lane

On mån, 2011-12-12 at 16:51 -0800, Peter van Hardenberg wrote:
> Because we haven't heard from him in a while we've been using PL/V8 to
> validate a JSON datatype simulated by a DOMAIN with a simple
> acceptance function. (See below.) This is not ideally performant but
> thanks to V8's JIT the JSON parser is actually reasonably good.
>
> I think releasing something simple and non-performant with reasonable
> semantics would be the best next step. If it were up to me, I'd
> probably even try to just land PL/V8 as PL/JavaScript for 9.2 if the
> crash bugs and deal breakers can be sifted out.

You don't need a new PL to do that. The existing PLs can also parse
JSON. So that's not nearly enough of a reason to consider adding this
new PL.

On Mon, Dec 12, 2011 at 7:37 PM, Daniel Farina <daniel(at)heroku(dot)com> wrote:
> On Mon, Dec 12, 2011 at 4:51 PM, Peter van Hardenberg <pvh(at)pvh(dot)ca> wrote:>
>> PL/V8 is fast, it's sandboxed, and while it doesn't provide GIN or
>> GIST operators out of the box, maybe those could be motivated by its
>> inclusion.
>
> I also feel that a big problem with JSON as a data type is that there
> is not a powerful, common navigation method.  JSON path is basically
> pretty obscure by comparison to XPath.  As a result, the common
> approach to navigation in a JSON structure is basically "write
> procedures".  And that is only perfectly supported by a full-blown
> interpreter.

This. For me, postgres xml extensions is 'a whole bunch of extra
stuff that comes with the xpath function'. How you get data into and
out of json is much more interesting than how the type is set up
internally or how it's parsed.

There must be some way to avoid iterative set up and tear down of json
objects (maybe as a cast?) -- postgres arrays of composites can set up
data in a way that feels very much like json in it's construction.
One big reason why people might go to server side json is to try and
avoid tedious marshaling of data between client and server. The xpath
function has had its warts, but it offers very tight coupling between
your database and your documents. In the case of json, I think you
can go even further.

merlin

----- Original message -----
> On Dec 12, 2011, at 4:51 PM, Peter van Hardenberg wrote:
>
> > Because we haven't heard from him in a while we've been using PL/V8 to
> > validate a JSON datatype simulated by a DOMAIN with a simple
> > acceptance function. (See below.) This is not ideally performant but
> > thanks to V8's JIT the JSON parser is actually reasonably good.
>
> Note that Claes Jakobsson has been working on a JSON data type using the
> Jansson JSON library.
>
>    http://pgxn.org/dist/pg-json/

We recently needed to store/valisate JSON data and be able to do some trivial extraction of values from it and went with pg-json.

The great benefit of having JSON as an extension type is being able to use an external library (Jansson is very small, MIT licensed, looks really well written and has been actively maintaied for years) and not being tied to a yearly release cycle.

Postgres jumps through a lot of hoops to be extensible and I think a JSON type is a kind of thing that fits the bill of an extension perfectly.

Cheers,
Jan

On Tue, Dec 13, 2011 at 5:22 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> On 12/12/2011 03:54 PM, Robert Haas wrote:
>>> There are way too many places that assume that the typmod can
>>> just be discarded.  I don't think that's going to fly, because
>>> =(text,text) probably has different semantics from =(json,json).
>
>> And certain places where they are not allowed at all, I think (unless I
>> am misremembering the early debates about enum types and output functions).
>
> Yeah.  The current system design assumes that typmod specifies a
> constraint of some sort.  It is not possible to use it to change the
> semantics of the datatype.  The most obvious way in which this is true
> is that selection of which operators and functions to apply to values
> does not consider typmod of the values.  This is not something we should
> lightly revisit.  We don't even have a handle on how to make domains
> behave differently from their underlying datatypes, and those *do* have
> their own type OIDs.  Injecting typmod into the algorithm seems like a
> disaster from here.

I'm glad I didn't think of doing that before then.

Can we agree some wording to put into the docs? Sounds like some clear
warnings are needed.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Dec 12, 2011 at 9:25 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On mån, 2011-12-12 at 16:51 -0800, Peter van Hardenberg wrote:
> You don't need a new PL to do that.  The existing PLs can also parse
> JSON.  So that's not nearly enough of a reason to consider adding this
> new PL.

PL/V8 is interesting because it is very fast, sandboxed, and well
embedded with little overhead.

My experience with PL/Python and PL/Perl has not been thus, and
although they are handy if you want to break out and run system work,
they're not the kind of thing I'd consider for defining performant
operators with.

I feel PL/V8 has promise in that area.

--
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt." -- Kurt Vonnegut

On Mon, Dec 5, 2011 at 3:12 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Where are we with adding JSON for Postgres 9.2?  We got bogged down in
> the data representation last time we discussed this.

We should probably have a wiki page titled "JSON datatype status" to
help break the cycle we're in:

* Someone asks about the status of JSON

* Various ideas are suggested

* Patches are posted (maybe)

* More discussion about fundamental issues ensues

* Nothing is accomplished (as far as adding JSON to Postgres core)

There are several JSON implementations for Postgres floating around, including:

* http://pgxn.org/dist/pg-json/ : Mentioned in previous posts; a JSON
library based on Jansson supporting path subscript and equality
testing

* http://git.postgresql.org/gitweb/?p=json-datatype.git;a=summary :
The JSON datatype I implemented for Google Summer of Code 2010. It
has the most features of any implementation I'm aware of, but:

* Is in the form of a contrib module

* Preserves input text verbatim, a guarantee that will be broken
by more efficient implementations

* http://git.postgresql.org/gitweb/?p=json-datatype.git;a=shortlog;h=refs/heads/json2
: My rewrite of the JSON module that condenses input (but still
stores it as text) and addresses the issue of JSON when either the
server or client encoding is not UTF-8. Needs more features and
documentation, but like my other implementation, may not be quite what
we want.

Issues we've encountered include:

* Should JSON be stored as binary or as text?

* How do we deal with Unicode escapes and characters if the server or
client encoding is not UTF-8? Some (common!) character encodings have
code points that don't map to Unicode. Also, the charset conversion
modules do not provide fast entry points for converting individual
characters; each conversion involves a funcapi call.

---

In an application I'm working on, I store JSON-encoded objects in a
PostgreSQL database (using TEXT). I do so because it allows me to
store non-relational data that is easy for my JavaScript code to work
with.

However, I fail to see much benefit of a JSON type. When I need to
work with the data in PHP, C, or Haskell, I use JSON parsing libraries
available in each programming language. Although being able to
transform or convert JSON data within SQL might be convenient, I can't
think of any compelling reason to do it in my case.

Can someone clarify why a JSON type would be useful, beyond storage
and validation? What is a real-world, *concrete* example of a problem
where JSON manipulation in the database would be much better than:

* Using the application's programming language to manipulate the data
(which it does a lot already) ?

* Using CouchDB or similar instead of PostgreSQL?

- Joey

On 12/13/2011 03:15 AM, Joey Adams wrote:
> We should probably have a wiki page titled "JSON datatype status" to
> help break the cycle we're in
>

I was about to point you to
http://wiki.postgresql.org/wiki/JSON_API_Brainstorm , only to realize
you created that thing in the first place. There's
http://wiki.postgresql.org/wiki/JSON_datatype_GSoC_2010 too. I don't
think it's completely stuck in a cycle yet--every pass around seems to
accumulate some better informed ideas than the last still.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

On Tue, Dec 13, 2011 at 12:25 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On mån, 2011-12-12 at 16:51 -0800, Peter van Hardenberg wrote:
>> Because we haven't heard from him in a while we've been using PL/V8 to
>> validate a JSON datatype simulated by a DOMAIN with a simple
>> acceptance function. (See below.) This is not ideally performant but
>> thanks to V8's JIT the JSON parser is actually reasonably good.
>>
>> I think releasing something simple and non-performant with reasonable
>> semantics would be the best next step. If it were up to me, I'd
>> probably even try to just land PL/V8 as PL/JavaScript for 9.2 if the
>> crash bugs and deal breakers can be sifted out.
>
> You don't need a new PL to do that.  The existing PLs can also parse
> JSON.  So that's not nearly enough of a reason to consider adding this
> new PL.

Just because all our languages are Turing-complete doesn't mean they
are all equally well-suited to every task. Of course, that doesn't
mean we'd add a whole new language just to get a JSON parser, but I
don't think that's really what Peter was saying. Rather, I think the
point is that embedded Javascript is *extremely* popular, lots and
lots of people are supporting it, and we ought to seriously consider
doing the same. It's hard to think of another PL that we could add
that would give us anywhere near the bang for the buck that Javascript
would.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2011/12/13 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Tue, Dec 13, 2011 at 12:25 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> On mån, 2011-12-12 at 16:51 -0800, Peter van Hardenberg wrote:
>>> Because we haven't heard from him in a while we've been using PL/V8 to
>>> validate a JSON datatype simulated by a DOMAIN with a simple
>>> acceptance function. (See below.) This is not ideally performant but
>>> thanks to V8's JIT the JSON parser is actually reasonably good.
>>>
>>> I think releasing something simple and non-performant with reasonable
>>> semantics would be the best next step. If it were up to me, I'd
>>> probably even try to just land PL/V8 as PL/JavaScript for 9.2 if the
>>> crash bugs and deal breakers can be sifted out.
>>
>> You don't need a new PL to do that.  The existing PLs can also parse
>> JSON.  So that's not nearly enough of a reason to consider adding this
>> new PL.
>
> Just because all our languages are Turing-complete doesn't mean they
> are all equally well-suited to every task.  Of course, that doesn't
> mean we'd add a whole new language just to get a JSON parser, but I
> don't think that's really what Peter was saying.  Rather, I think the
> point is that embedded Javascript is *extremely* popular, lots and
> lots of people are supporting it, and we ought to seriously consider
> doing the same.  It's hard to think of another PL that we could add
> that would give us anywhere near the bang for the buck that Javascript
> would.

it is true - but there is a few questions

* will be JSON supported from SQL?
* what Javascript engine will be supported?
* will be integrated JSON supported from PLPerl?

I like to see Javacript's in pg, but I don't like Javascript just for
JSON. JSON should be independent on javascript.

Regards

Pavel

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

On 12/13/2011 08:44 AM, Robert Haas wrote:
> Rather, I think the point is that embedded Javascript is *extremely*
> popular, lots and
> lots of people are supporting it, and we ought to seriously consider
> doing the same. It's hard to think of another PL that we could add
> that would give us anywhere near the bang for the buck that Javascript
> would.
>

Quite. I hate Javascript with a passion, wish it would just go away and
stop meddling with my life. And even with that context, I think in-core
PL/V8 would be a huge advocacy win. PostgreSQL has this great
developer-oriented PL interface, it just doesn't work out of the box
with any of the "pop" languages right now.

Personal story on this. When my book came out, I was trying to take the
#1 spot on Packt's bestseller list, even if it was just for a day.
Never made it higher than #2. The #1 spot the whole time was "jQuery
1.4 Reference Guide", discussing the most popular JavaScript library out
there. And you know what? Over a year later, it's *still there*. At
no point did it over drop out of that top spot. The number of people
who would consider server-side programming suddenly feasible if PL/V8
were easy to do is orders of magnitude larger than the current
PostgreSQL community.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

On Tue, Dec 13, 2011 at 8:11 AM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> On 12/13/2011 08:44 AM, Robert Haas wrote:
>>
>> Rather, I think the point is that embedded Javascript is *extremely*
>> popular, lots and
>> lots of people are supporting it, and we ought to seriously consider
>> doing the same.  It's hard to think of another PL that we could add
>> that would give us anywhere near the bang for the buck that Javascript
>> would.
>>
>
>
> Quite.  I hate Javascript with a passion, wish it would just go away and
> stop meddling with my life.  And even with that context, I think in-core
> PL/V8 would be a huge advocacy win.  PostgreSQL has this great
> developer-oriented PL interface, it just doesn't work out of the box with
> any of the "pop" languages right now.
>
> Personal story on this.  When my book came out, I was trying to take the #1
> spot on Packt's bestseller list, even if it was just for a day.  Never made
> it higher than #2.  The #1 spot the whole time was "jQuery 1.4 Reference
> Guide", discussing the most popular JavaScript library out there.  And you
> know what?  Over a year later, it's *still there*.  At no point did it over
> drop out of that top spot.  The number of people who would consider
> server-side programming suddenly feasible if PL/V8 were easy to do is orders
> of magnitude larger than the current PostgreSQL community.

Yeah -- javascript is making strides server-side with technologies
like node.js. Like you I have really mixed feelings about javascript
-- there's a lot of nastiness but the asynchronous style of coding
javascript developers tend to like is a great fit for postgres both
inside the backend and in database clients. This is on top of the
already nifty type system synergy I mentioned upthread.

Postgres would in fact make a wonderful 'nosql' backend with some
fancy json support -- document style transmission to/from the backend
without sacrificing relational integrity in storage. Properly done
this would be a fabulous public relations coup (PostgreSQL = better
nosql).

merlin

On 12/13/2011 09:11 AM, Greg Smith wrote:
> On 12/13/2011 08:44 AM, Robert Haas wrote:
>> Rather, I think the point is that embedded Javascript is *extremely*
>> popular, lots and
>> lots of people are supporting it, and we ought to seriously consider
>> doing the same. It's hard to think of another PL that we could add
>> that would give us anywhere near the bang for the buck that Javascript
>> would.
>
> Quite. I hate Javascript with a passion, wish it would just go away
> and stop meddling with my life. And even with that context, I think
> in-core PL/V8 would be a huge advocacy win. PostgreSQL has this great
> developer-oriented PL interface, it just doesn't work out of the box
> with any of the "pop" languages right now.
>
> Personal story on this. When my book came out, I was trying to take
> the #1 spot on Packt's bestseller list, even if it was just for a
> day. Never made it higher than #2. The #1 spot the whole time was
> "jQuery 1.4 Reference Guide", discussing the most popular JavaScript
> library out there. And you know what? Over a year later, it's *still
> there*. At no point did it over drop out of that top spot. The
> number of people who would consider server-side programming suddenly
> feasible if PL/V8 were easy to do is orders of magnitude larger than
> the current PostgreSQL community.

I think your passion is probably somewhat misdirected. I've long thought
JS would be a good fit for Postgres. It's naturally sandboxed and its
type system fits ours quite well. And, as you say, it's massively
popular and getting a major second wind thanks to things like JQuery,
Ext-JS and node.js. This last one has certainly convinced lots of people
that JS is not just for browsers any more.

Having said that, don't underestimate the complexity of trying to build
in PLV8. In its current incarnation the interface is written in C++ (and
of course so is the underlying V8 engine). I have been doing some
development work for it, even though my C++ is rather rusty (that's an
understatement, folks), which is why I haven't got a lot more done - I'm
just having to go slowly and with reference books by my side. So either
we'd need to rewrite the glue code entirely in C (and it's littered with
C++isms) and handle the difficulties of embedding a C++ library, or we'd
have a major new build infrastructure dependency which could well give
us a major case of developmental indigestion.

cheers

andrew

On Tue, Dec 13, 2011 at 8:44 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Rather, I think the
> point is that embedded Javascript is *extremely* popular, lots and
> lots of people are supporting it, and we ought to seriously consider
> doing the same.  It's hard to think of another PL that we could add
> that would give us anywhere near the bang for the buck that Javascript
> would.

+1 to that.

I'm not a huge fan of JS; wish that one of the Scheme variations had
"made it" instead.

But it's clear that a LOT of fairly successful work has gone into
making JS implementations performant, and it's clearly heavily used.
JS+hstore would probably draw in a bunch of users, and tempt them to
the "SQL dark side" :-).

Wanting a JSON processor isn't quite a good enough reason to add C++
support in order to draw in a JS interpreter. But I don't imagine
things are restricted to just 1 JS implementation, and JSON isn't the
only reason to do so.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On Dec 12, 2011, at 7:42 PM, Alvaro Herrera wrote:

> I remember there was the idea of doing something like this for regexes
> -- have a specialized data type that saves the trouble of parsing it.
> I imagine this is pretty much the same.
>
> Nobody got around to doing anything about it though.

(regex data type)++

David

On tis, 2011-12-13 at 00:06 -0800, Peter van Hardenberg wrote:
> On Mon, Dec 12, 2011 at 9:25 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> > On mån, 2011-12-12 at 16:51 -0800, Peter van Hardenberg wrote:
> > You don't need a new PL to do that. The existing PLs can also parse
> > JSON. So that's not nearly enough of a reason to consider adding this
> > new PL.
>
> PL/V8 is interesting because it is very fast, sandboxed, and well
> embedded with little overhead.
>
> My experience with PL/Python and PL/Perl has not been thus, and
> although they are handy if you want to break out and run system work,
> they're not the kind of thing I'd consider for defining performant
> operators with.

Some performance numbers comparing a valid_json() functions implemented
in different ways would clarify this. I wouldn't be surprised if PL/V8
won, but we need to work with some facts.

On tis, 2011-12-13 at 09:11 -0500, Greg Smith wrote:
> Personal story on this. When my book came out, I was trying to take
> the #1 spot on Packt's bestseller list, even if it was just for a day.
> Never made it higher than #2. The #1 spot the whole time was "jQuery
> 1.4 Reference Guide", discussing the most popular JavaScript library
> out there. And you know what? Over a year later, it's *still
> there*.

I would guess that that's largely because there are a lot more people
developing web sites than people tuning databases, and also because the
on-board documentation of javascript and jquery is poor, at least for
their audience.

On Tue, Dec 13, 2011 at 2:33 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On tis, 2011-12-13 at 09:11 -0500, Greg Smith wrote:
>> Personal story on this.  When my book came out, I was trying to take
>> the #1 spot on Packt's bestseller list, even if it was just for a day.
>> Never made it higher than #2.  The #1 spot the whole time was "jQuery
>> 1.4 Reference Guide", discussing the most popular JavaScript library
>> out there.  And you know what?  Over a year later, it's *still
>> there*.
>
> I would guess that that's largely because there are a lot more people
> developing web sites than people tuning databases, and also because the
> on-board documentation of javascript and jquery is poor, at least for
> their audience.

jquery being used in as much as 40%+ of all websites by some estimates
is surely a contributing factor.

merlin

On tis, 2011-12-13 at 08:44 -0500, Robert Haas wrote:
> Just because all our languages are Turing-complete doesn't mean they
> are all equally well-suited to every task. Of course, that doesn't
> mean we'd add a whole new language just to get a JSON parser, but I
> don't think that's really what Peter was saying.

That was in fact what I was saying.

> Rather, I think the
> point is that embedded Javascript is *extremely* popular, lots and
> lots of people are supporting it, and we ought to seriously consider
> doing the same. It's hard to think of another PL that we could add
> that would give us anywhere near the bang for the buck that Javascript
> would.

If JavaScript (trademark of Oracle, btw.; be careful about calling
anything PL/JavaScript) had a near-canonical implementation with a
stable shared library and a C API, then this might be a no-brainer. But
instead we have lots of implementations, and the one being favored here
is written in C++ and changes the soname every 3 months. I don't think
that's the sort of thing we want to carry around.

The way forward here is to maintain this as an extension, provide debs
and rpms, and show that that is maintainable. I can see numerous
advantages in maintaining a PL outside the core; especially if you are
still starting up and want to iterate quickly.

Robert Haas wrote:
> On Mon, Dec 12, 2011 at 4:08 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > On Mon, Dec 12, 2011 at 8:54 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> >> There are way too many places that assume that the typmod can
> >> just be discarded.
> >
> > If true, that probably ought to be documented cos it sounds fairly important.
> >
> > Where and when is it true?
>
> I'm not going to go compile an exhaustive list, since that would take
> a week and I don't have any particular desire to invest that much time
> in it, but just to take a couple of simple examples:
>
> rhaas=# create or replace function wuzzle(numeric(5,2)) returns int as
> $$select 1$$ language sql;
> CREATE FUNCTION
> rhaas=# \df wuzzle
> List of functions
> Schema | Name | Result data type | Argument data types | Type
> --------+--------+------------------+---------------------+--------
> public | wuzzle | numeric | | normal
> public | wuzzle | integer | numeric | normal
> (2 rows)
>
> rhaas=# select pg_typeof(1.23::numeric(5,2));
> pg_typeof
> -----------
> numeric
> (1 row)
>
> There are a very large number of others. Possibly grepping for places
> where we do getBaseType() rather than getBaseTypeAndTypmod() would be
> a way to find some of them.

I think the most common one I see is with concatentation:

test=> select 'abc'::varchar(3) || 'def'::varchar(3);
?column?
----------
abcdef
(1 row)

It is not really clear how the typmod length should be passed in this
example, but passing it unchanged seems wrong:

test=> select ('abc'::varchar(3) || 'def'::varchar(3))::varchar(3);
varchar
---------
abc
(1 row)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Tue, Dec 13, 2011 at 2:41 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On tis, 2011-12-13 at 08:44 -0500, Robert Haas wrote:
>> Just because all our languages are Turing-complete doesn't mean they
>> are all equally well-suited to every task.  Of course, that doesn't
>> mean we'd add a whole new language just to get a JSON parser, but I
>> don't think that's really what Peter was saying.
>
> That was in fact what I was saying.
>
>> Rather, I think the
>> point is that embedded Javascript is *extremely* popular, lots and
>> lots of people are supporting it, and we ought to seriously consider
>> doing the same.  It's hard to think of another PL that we could add
>> that would give us anywhere near the bang for the buck that Javascript
>> would.
>
> If JavaScript (trademark of Oracle, btw.; be careful about calling
> anything PL/JavaScript) had a near-canonical implementation with a
> stable shared library and a C API, then this might be a no-brainer.  But
> instead we have lots of implementations, and the one being favored here
> is written in C++ and changes the soname every 3 months.  I don't think
> that's the sort of thing we want to carry around.

Mozilla SpiderMonkey seems like a good fit: it compiles to a
dependency free .so, has excellent platform support, has a stable C
API, and while it's C++ internally makes no use of exceptions (in
fact, it turns them off in the c++ compiler). ISTM to be a suitable
foundation for an external module, 'in core' parser, pl, or anything
really.

merlin

On Tue, Dec 13, 2011 at 1:13 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> Mozilla SpiderMonkey seems like a good fit: it compiles to a
> dependency free .so, has excellent platform support, has a stable C
> API, and while it's C++ internally makes no use of exceptions (in
> fact, it turns them off in the c++ compiler).  ISTM to be a suitable
> foundation for an external module, 'in core' parser, pl, or anything
> really.

When I started to think about PL/js, I compared three of SpiderMonkey,
SquirrelFish, and V8. SpiderMonkey at that time (around 2009) was
not-fast, not-small in memory while what you raise, as well as its
advanced features like JS1.7 (pure yield!), was attractive. Also
SpiderMonkey was a little harder to build in arbitrary platform
(including Windows) than v8. SquirrelFish was fastest of three, but
yet it's sticky with Webkit and also hard to build itself. Dunno how
they've changed since then.

Thanks,
--
Hitoshi Harada

On Tue, Dec 13, 2011 at 8:15 AM, Joey Adams <joeyadams3(dot)14159(at)gmail(dot)com> wrote:

> Issues we've encountered include:
>
>  * Should JSON be stored as binary or as text?

Text

>  * How do we deal with Unicode escapes and characters if the server or
> client encoding is not UTF-8?  Some (common!) character encodings have
> code points that don't map to Unicode.  Also, the charset conversion
> modules do not provide fast entry points for converting individual
> characters; each conversion involves a funcapi call.

Make JSON datatypes only selectable if client encoding is UTF-8.

Lets JFDI

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 12/15/2011 01:34 PM, Simon Riggs wrote:
> On Tue, Dec 13, 2011 at 8:15 AM, Joey Adams<joeyadams3(dot)14159(at)gmail(dot)com> wrote:
>
>> Issues we've encountered include:
>>
>> * Should JSON be stored as binary or as text?
> Text

Works for me.

>> * How do we deal with Unicode escapes and characters if the server or
>> client encoding is not UTF-8? Some (common!) character encodings have
>> code points that don't map to Unicode. Also, the charset conversion
>> modules do not provide fast entry points for converting individual
>> characters; each conversion involves a funcapi call.
> Make JSON datatypes only selectable if client encoding is UTF-8.

Yuck. Do we have this sort of restriction for any other data type?

ISTM that the encoding problem is at least as likely to be the reverse
of what's above - i.e. that there's a code point in the stored JSON
that's not represented in the client encoding.

cheers

andrew

On Thu, Dec 15, 2011 at 4:47 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> Make JSON datatypes only selectable if client encoding is UTF-8.
>
> Yuck. Do we have this sort of restriction for any other data type?

No, and I don't think it's necessary to do it here, either. Nor would
it be a good idea, because then the return value of EXPLAIN (FORMAT
JSON) couldn't unconditionally be json. But I think the important
point is that this is an obscure corner case. Let me say that one
more time: obscure corner case!

The only reason JSON needs to care about this at all is that it allows
\u1234 to mean Unicode code point 0x1234. But for that detail, JSON
would be encoding-agnostic. So I think it's sufficient for us to
simply decide that that particular feature may not work (or even, will
not work) for non-ASCII characters if you use a non-UTF8 encoding.
There's still plenty of useful things that can be done with JSON even
if that particular feature is not available; and that way we don't
have to completely disable the data type just because someone wants to
use EUC-JP or something.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Dec 16, 2011 at 1:52 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Dec 15, 2011 at 4:47 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>> Make JSON datatypes only selectable if client encoding is UTF-8.
>>
>> Yuck. Do we have this sort of restriction for any other data type?
>
> No, and I don't think it's necessary to do it here, either.  Nor would
> it be a good idea, because then the return value of EXPLAIN (FORMAT
> JSON) couldn't unconditionally be json.  But I think the important
> point is that this is an obscure corner case.  Let me say that one
> more time: obscure corner case!
>
> The only reason JSON needs to care about this at all is that it allows
> \u1234 to mean Unicode code point 0x1234.  But for that detail, JSON
> would be encoding-agnostic.  So I think it's sufficient for us to
> simply decide that that particular feature may not work (or even, will
> not work) for non-ASCII characters if you use a non-UTF8 encoding.
> There's still plenty of useful things that can be done with JSON even
> if that particular feature is not available; and that way we don't
> have to completely disable the data type just because someone wants to
> use EUC-JP or something.

Completely agree. I was going to write almost exactly this in reply.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Fri, Dec 16, 2011 at 8:52 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> But I think the important point is that this is an obscure corner case. Let me say that one
more time: obscure corner case!

+1

> The only reason JSON needs to care about this at all is that it allows
> \u1234 to mean Unicode code point 0x1234.  But for that detail, JSON
> would be encoding-agnostic.  So I think it's sufficient for us to
> simply decide that that particular feature may not work (or even, will
> not work) for non-ASCII characters if you use a non-UTF8 encoding.
> There's still plenty of useful things that can be done with JSON even
> if that particular feature is not available; and that way we don't
> have to completely disable the data type just because someone wants to
> use EUC-JP or something.

So, if the server encoding is not UTF-8, should we ban Unicode escapes:

"\u00FCber"

or non-ASCII characters?

"über"

Also:

* What if the server encoding is SQL_ASCII?

* What if the server encoding is UTF-8, but the client encoding is
something else (e.g. SQL_ASCII)?

- Joey

On Tue, Dec 13, 2011 at 1:13 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Tue, Dec 13, 2011 at 2:41 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> On tis, 2011-12-13 at 08:44 -0500, Robert Haas wrote:
>>> Just because all our languages are Turing-complete doesn't mean they
>>> are all equally well-suited to every task.  Of course, that doesn't
>>> mean we'd add a whole new language just to get a JSON parser, but I
>>> don't think that's really what Peter was saying.
>>
>> That was in fact what I was saying.
>>
>>> Rather, I think the
>>> point is that embedded Javascript is *extremely* popular, lots and
>>> lots of people are supporting it, and we ought to seriously consider
>>> doing the same.  It's hard to think of another PL that we could add
>>> that would give us anywhere near the bang for the buck that Javascript
>>> would.
>>
>> If JavaScript (trademark of Oracle, btw.; be careful about calling
>> anything PL/JavaScript) had a near-canonical implementation with a
>> stable shared library and a C API, then this might be a no-brainer.  But
>> instead we have lots of implementations, and the one being favored here
>> is written in C++ and changes the soname every 3 months.  I don't think
>> that's the sort of thing we want to carry around.
>
> Mozilla SpiderMonkey seems like a good fit: it compiles to a
> dependency free .so, has excellent platform support, has a stable C
> API, and while it's C++ internally makes no use of exceptions (in
> fact, it turns them off in the c++ compiler).  ISTM to be a suitable
> foundation for an external module, 'in core' parser, pl, or anything
> really.

To the best of my knowledge:

libv8 is also exception-free, and compiled with exceptions off. plv8
does make use of exceptions, though, something that gave me pause when
reading it. At first I thought it was to integrate with libv8, but
that did not seem to be the case, so it probably could learn to use
return codes instead. libv8 also has a light dependency list:

ldd /usr/lib/libv8.so (/lib/ entries and linux omitted)

libicuuc.so.44 => /usr/lib/libicuuc.so.44 (0x00007fc838459000)
libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007fc838151000)
libicudata.so.44 => /usr/lib/libicudata.so.44 (0x00007fc836aed000)

So ICU and C++.

In addition, more projects have been successful in embedding libv8;
right now it has the entrenchment advantage over libmozjs in
applications that are not closely tied to XUL/Mozilla, although that
could change in a few years. Institutionally Mozilla has not
historically been quick to prioritize anything not essential to
shipping Firefox, and I would imagine V8 is in a similar situation,
even though they occasionally make concessions for non-browsing use
cases (ex: multi-gigabyte heap sizes).

I would regard either choice as at least equally risky in this way,
given what I know (refinements welcome).

Both libv8 and libmozjs are maintained in Debian, and are parts of at
least one stable release.

In spite of the hazard posed by the aggressive releases and
non-general-purpose focus of the maintainers of both of these runtimes
at this time, I am still in favor of having a binding to at least one
of them into mainline, with the ability to get new or alternative
versions via extensions. If extensions were already pervasive and
everyone was installing them everywhere I'd think otherwise (just
leave it as an extension), but the cost of not being able to index and
manipulate JSON efficiently and with a trusted language is just too
huge to let slide.

Right now the perception of Postgres...actually, databases in general,
including virtually all of the newcomers -- is that they are
monolithic systems, and for most people either "9.3" will "have"
javascript and indexing of JSON documents, or it won't. In most cases
I would say "meh, let them eat cake until extensions become so
apparently dominant that we can wave someone aside to extension-land",
but in this case I think that would be a strategic mistake.

--
fdr

On 12/16/2011 05:39 PM, Daniel Farina wrote:
> To the best of my knowledge:
>
> libv8 is also exception-free, and compiled with exceptions off. plv8
> does make use of exceptions, though, something that gave me pause when
> reading it. At first I thought it was to integrate with libv8, but
> that did not seem to be the case, so it probably could learn to use
> return codes instead.

Yeah. We should look at that.

cheers

andrew

On Fri, Dec 16, 2011 at 12:13 PM, Joey Adams <joeyadams3(dot)14159(at)gmail(dot)com> wrote:
> So, if the server encoding is not UTF-8, should we ban Unicode escapes:
>
>    "\u00FCber"
>
> or non-ASCII characters?
>
>    "über"

The former. Refusing the escapes makes sense, because it's totally
unclear how we ought to interpret them. Refusing the characters would
be just breaking something for no particular reason. Right now, for
example, EXPLAIN (FORMAT JSON) could easily end up returning non-ASCII
characters in whatever the database encoding happens to be. That
command would be unusable if we arbitrarily chucked an error every
time a non-ASCII character showed up and the database encoding wasn't
UTF-8.

> Also:
>
>  * What if the server encoding is SQL_ASCII?
>
>  * What if the server encoding is UTF-8, but the client encoding is
> something else (e.g. SQL_ASCII)?

It's not clear to me why these cases would require any special handling.

In the spirit of Simon's suggestion that we JFDI, I cooked up a patch
today that JFDI. See attached. This lacks any form of
canonicalization and therefore doesn't support comparison operators.
It also lacks documentation, regression testing, and probably an
almost uncountable number of other bells and whistles that people
would like to have. This is more or less a deliberate decision on my
part: I feel that the biggest problem with this project is that we've
spent far too much time dithering over what the exactly perfect set of
functionality set would be, and not enough time getting good basic
functionality committed. So this is as basic as it gets. It does
exactly one thing: validation. If people are happy with it, we can
extend from here incrementally.

Thoughts?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Sat, Dec 17, 2011 at 2:26 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> In the spirit of Simon's suggestion that we JFDI, I cooked up a patch
> today that JFDI.  See attached.

Which looks very good.

Comments
* Comment for IDENTIFICATION of json.c says contrib/json/json.c
* json.c contains a duplicate of a line from header file "extern Datum
json_in(PG_FUNCTION_ARGS);"

And additionally, a quote from our fine manual...

"Caution: Some XML-related functions may not work at all on non-ASCII
data when the server encoding is not UTF-8. This is known to be an
issue for xpath() in particular." .... so I think this approach works
for JSON too.

Adding tests and docs is a must, nothing else is right now. Once we
have this, others can add the bells and whistles, possibly in 9.2

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Hi,

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> The way forward here is to maintain this as an extension, provide debs
> and rpms, and show that that is maintainable. I can see numerous
> advantages in maintaining a PL outside the core; especially if you are
> still starting up and want to iterate quickly.

I'd like to add some confusion on the implementation choice, because it
looks damn too easy now… Guile 2.0 offers an implementation of the
ECMAscript language and plscheme already exists as a PostgreSQL PL
extension for integrating with Guile.

http://plscheme.projects.postgresql.org/

http://wingolog.org/archives/2009/02/22/ecmascript-for-guile
http://packages.debian.org/sid/guile-2.0

http://www.gnu.org/software/guile/

Guile is an extension language platform

Guile is an efficient virtual machine that executes a portable
instruction set generated by its optimizing compiler, and integrates
very easily with C and C++ application code. In addition to Scheme,
Guile includes compiler front-ends for ECMAScript and Emacs Lisp
(support for Lua is underway), which means your application can be
extended in the language (or languages) most appropriate for your user
base. And Guile's tools for parsing and compiling are exposed as part
of its standard module set, so support for additional languages can be
added without writing a single line of C.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Sat, Dec 17, 2011 at 5:02 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> I'd like to add some confusion on the implementation choice, because it
> looks damn too easy now… Guile 2.0 offers an implementation of the
> ECMAscript language and plscheme already exists as a PostgreSQL PL
> extension for integrating with Guile.

It seems like the licensing there could potentially be problematic.
It's GPL with a linking exception. Not sure we want to go there.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Dec 17, 2011, at 3:53 AM, Simon Riggs wrote:

> Which looks very good.

Love having the start here. I forwarded this message to Claes Jakobsson, creator of the jansson-using pg-json extension. He’s a bit less supportive. He gave me permission to quote him here:

> Frankly I see the inclusion of a JSON datatype in core as unnecessary. Stuff should be moved out of core rather than in, as we do in Perl. Also, does this patch mean that the 'json' type is forever claimed and can't be replaced by extensions?
>
> There's little reason to reimplement JSON parsing, comparision and other routines when there's a multitude of already good libraries.

Personally, I think that there really should be a core key/value-type data type, and json is probably the best possible choice, absent a SQL-standard-mandated type (which would probably suck anyway). But I think it worthwhile to hear alternate points of view, and this isn't far from what Jan said last week.

Best,

David

On Sat, Dec 17, 2011 at 7:50 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> Love having the start here. I forwarded this message to Claes Jakobsson, creator of the jansson-using pg-json extension. He’s a bit less supportive. He gave me permission to quote him here:
>
>> Frankly I see the inclusion of a JSON datatype in core as unnecessary. Stuff should be moved out of core rather than in, as we do in Perl. Also, does this patch mean that the 'json' type is forever claimed and can't be replaced by extensions?
>>
>> There's little reason to reimplement JSON parsing, comparision and other routines when there's a multitude of already good libraries.

That's fair enough, but we've had *many* requests for this
functionality in core, I don't see what we lose by having at least
some basic functionality built in. There is always room for people to
provide extensions and add-ons that build on whatever core support we
provide. There must be an order of magnitude more demand for this
data type than there is for any other potential new in-core data type.
Off the top of my head, I can't think of anything else that's even in
the same league; can you?

As for whether to use somebody else's implementation or roll our own,
I'm not convinced there's any value in reusing somebody else's
implementation. Consider a library like json-c, just the first thing
I happened to download. The license is compatible, so that's good.
But the coding style is completely different from ours, the memory
management is not compatible with ours (it uses calloc and it's not
pluggable), the error messages don't follow our style guidelines and
won't work with our translation infrastructure, it has its own
printfbuf which is redundant with our StringInfoData, it has its own
hash table implementation which is also redundant with code we already
have, and of course it won't contain anything like
CHECK_FOR_INTERRUPTS() any place that might be needed. Now, sure,
all of those problems are fixable. But by the time you get done it's
not any less work than writing your own, and probably not as well
adapted to our particular needs.

The jansson library has a pluggable memory allocator, but most of the
other complaints above still apply; and I see, for example, that it
contains its own UTF-8 validator and locale conversion routines, which
is undoubtedly not what we want. jansson also interprets numeric
values as either a native integer or floating point values, which
limits the amount of precision available and means that values may be
output in a manner quite different from how they were put in.
Although this is probably legal, since RFC4627 states that JSON
parsers may set limits on the range of numbers, I think it's an
unnecessary and undesirable limitation. I have always enjoyed the
ability to -- for example -- SELECT 400! in PostgreSQL and get an
exact answer, and I think it would be nice if I could store the result
in a JSON object. I am also quite certain that someone will propose
(or, perhaps, actually write) a function to convert a record to a JSON
object, and I think it would be mighty nice if numeric, int4, and int8
could be transformed into JSON numbers rather than JSON strings, which
isn't going to work - at least for numeric - if there are significant
range or precision limitations.

For XML, it makes a lot of sense for us to integrate with an external
library. Consider libxml2, the library we actually do integrate with.
The root directory has over 275,000 lines of code in .c and .h files.
Obviously, it's worth suffering through some pain (and we definitely
have suffered through some pain) to avoid having to rewrite some
substantial portion of that code. Providing some basic JSON support
figures to require about two orders of magnitude less code, which IMHO
makes the calculation completely different. The reason there are so
many JSON libraries out there is because it only takes a day to write
one. If you look at a couple of JSON parsers written by other people
and don't find exactly what you're looking for, you just go write one
of your own. You then have the option to hang out a shingle and
critique the next three people who come along and do the same thing,
but is that really justified? Odds are good that their reasons for
rolling their own were just as good as yours.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sat, Dec 17, 2011 at 7:50 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
>> Love having the start here. I forwarded this message to Claes Jakobsson, creator of the jansson-using pg-json extension. Hes a bit less supportive. He gave me permission to quote him here:

>>> Frankly I see the inclusion of a JSON datatype in core as unnecessary. Stuff should be moved out of core rather than in, as we do in Perl. Also, does this patch mean that the 'json' type is forever claimed and can't be replaced by extensions?
>>> There's little reason to reimplement JSON parsing, comparision and other routines when there's a multitude of already good libraries.

> That's fair enough, but we've had *many* requests for this
> functionality in core, I don't see what we lose by having at least
> some basic functionality built in. There is always room for people to
> provide extensions and add-ons that build on whatever core support we
> provide.

Well, I think that that's exactly the question here: if we do something
in core, will it foreclose options for people who want to do add-ons?

The main thing that's troubling me there is the issue of plain text
representation versus something precompiled (and if the latter, what
exactly). I don't see how you "build on" a core datatype that makes a
decision different from what you wanted for that.

I'm also +1 to Claes' opinion that this can be perfectly well managed as
an add-on. We've sweated blood over many years to make PG extensions
work nicely, and they now work better than they ever have. It's not
clear to me why the push to make something core when it can obviously be
an extension.

regards, tom lane

On Dec 17, 2011, at 7:40 PM, Tom Lane wrote:

> Well, I think that that's exactly the question here: if we do something
> in core, will it foreclose options for people who want to do add-ons?

Why would it? They would just have to use a different name.

Best,

David

On Sat, Dec 17, 2011 at 10:42 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> On Dec 17, 2011, at 7:40 PM, Tom Lane wrote:
>> Well, I think that that's exactly the question here: if we do something
>> in core, will it foreclose options for people who want to do add-ons?
>
> Why would it? They would just have to use a different name.

Yeah, exactly. Or for that matter, the same name in a different
schema. And as for the question of text vs. binary, that's going to
be two separate data types whether it gets done in core or elsewhere.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 18/12/11 04:21, Robert Haas wrote:
> On Sat, Dec 17, 2011 at 7:50 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
>> Love having the start here. I forwarded this message to Claes Jakobsson, creator of the jansson-using pg-json extension. He’s a bit less supportive. He gave me permission to quote him here:
>>
>>> Frankly I see the inclusion of a JSON datatype in core as unnecessary. Stuff should be moved out of core rather than in, as we do in Perl. Also, does this patch mean that the 'json' type is forever claimed and can't be replaced by extensions?
>>>
>>> There's little reason to reimplement JSON parsing, comparision and other routines when there's a multitude of already good libraries.
>
> That's fair enough, but we've had *many* requests for this
> functionality in core, I don't see what we lose by having at least
> some basic functionality built in.

I think having a JSON data type in core would drastically limit the
exposure third-party JSON extensions would get and that's bad. There are
tons of interesting features a JSON type could have and tying its
development to a one year release cycle might be a disservice both for
people who are willing to provide these features earlier, the users
which are faced with a choice between a fast-moving third-party addon
and a blessed core type and would cause overall confusion.

How about we try the tsearch way and let JSON extensions live outside
core for some time and perhaps if one emerges dominant and would benefit
from inclusion then consider it?

If we keep treating extensions as second-class citizens, they'll never
get the mindshare and importance we seem to want for them (or otherwise
why go through all the trouble to provide an infrastructure for them).

Cheers,
Jan

2011/12/18 Jan Urbański <wulczer(at)wulczer(dot)org>:
> On 18/12/11 04:21, Robert Haas wrote:
>> On Sat, Dec 17, 2011 at 7:50 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
>>> Love having the start here. I forwarded this message to Claes Jakobsson, creator of the jansson-using pg-json extension. He’s a bit less supportive. He gave me permission to quote him here:
>>>
>>>> Frankly I see the inclusion of a JSON datatype in core as unnecessary. Stuff should be moved out of core rather than in, as we do in Perl. Also, does this patch mean that the 'json' type is forever claimed and can't be replaced by extensions?
>>>>
>>>> There's little reason to reimplement JSON parsing, comparision and other routines when there's a multitude of already good libraries.
>>
>> That's fair enough, but we've had *many* requests for this
>> functionality in core, I don't see what we lose by having at least
>> some basic functionality built in.
>
> I think having a JSON data type in core would drastically limit the
> exposure third-party JSON extensions would get and that's bad. There are
> tons of interesting features a JSON type could have and tying its
> development to a one year release cycle might be a disservice both for
> people who are willing to provide these features earlier, the users
> which are faced with a choice between a fast-moving third-party addon
> and a blessed core type and would cause overall confusion.
>
> How about we try the tsearch way and let JSON extensions live outside
> core for some time and perhaps if one emerges dominant and would benefit
> from inclusion then consider it?

it should be contrib modules

Pavel

>
> If we keep treating extensions as second-class citizens, they'll never
> get the mindshare and importance we seem to want for them (or otherwise
> why go through all the trouble to provide an infrastructure for them).
>
> Cheers,
> Jan
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sat, Dec 17, 2011 at 5:02 PM, Dimitri Fontaine
> <dimitri(at)2ndquadrant(dot)fr> wrote:
>> I'd like to add some confusion on the implementation choice, because it
>> looks damn too easy now… Guile 2.0 offers an implementation of the
>> ECMAscript language and plscheme already exists as a PostgreSQL PL
>> extension for integrating with Guile.
>
> It seems like the licensing there could potentially be problematic.
> It's GPL with a linking exception. Not sure we want to go there.

It's LGPL so it's compatible (only the readline part is subject to GPL,
we're familiar enough with that though).

http://www.gnu.org/software/guile/docs/docs-2.0/guile-ref/Guile-License.html

The Guile library (libguile) and supporting files are published under
the terms of the GNU Lesser General Public License version 3 or later.
See the files COPYING.LESSER and COPYING.

C code linking to the Guile library is subject to terms of that
library. Basically such code may be published on any terms, provided
users can re-link against a new or modified version of Guile.

Scheme level code written to be run by Guile (but not derived from
Guile itself) is not restricted in any way, and may be published on
any terms. We encourage authors to publish on Free terms.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Sun, Dec 18, 2011 at 10:49, Jan Urbański <wulczer(at)wulczer(dot)org> wrote:
> On 18/12/11 04:21, Robert Haas wrote:
>> On Sat, Dec 17, 2011 at 7:50 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
>>> Love having the start here. I forwarded this message to Claes Jakobsson, creator of the jansson-using pg-json extension. He’s a bit less supportive. He gave me permission to quote him here:
>>>
>>>> Frankly I see the inclusion of a JSON datatype in core as unnecessary. Stuff should be moved out of core rather than in, as we do in Perl. Also, does this patch mean that the 'json' type is forever claimed and can't be replaced by extensions?
>>>>
>>>> There's little reason to reimplement JSON parsing, comparision and other routines when there's a multitude of already good libraries.
>>
>> That's fair enough, but we've had *many* requests for this
>> functionality in core, I don't see what we lose by having at least
>> some basic functionality built in.
>
> I think having a JSON data type in core would drastically limit the
> exposure third-party JSON extensions would get and that's bad. There are

The same way that having replication in core is bad for the rest of
the replication engines? While it has certainly decreased the usage of
for example Slony, I don't think anybody can say it's a bad thing that
we have this in core...

And of course, *not* having it in core, we didn't have people claiming
for many years that "postgres has no replication" or anything like
that... The fact is that a *lot* of our users, particularly in large
companies, will never install an extension that's not part of core.
Just look at other discussions about it even being a problem with it
being in *contrib*, which is still maintained and distributed by the
same developers.

We can hopefully get around this for the extensions in contrib (and
reasonably well has already), but few large companies are going to be
happy to go to pgxn and download an extension that has a single
maintainer (not "the team", and in most cases not even "a team"),
usually no defined lifecycle, no support, etc. (I'm pretty sure you
won't get support included for random pgxn modules when you buy a
contract from EDB, or CMD, or us, or PGX, or anybody really - wheras
if it the datatype is in core, you *will* get this)

So I'm not sure it would really lessen the exposure much at all -
those that are willing to install such extensions already, are surely
capable of finding it themselves (using pgxn for example - or even
google)

> tons of interesting features a JSON type could have and tying its
> development to a one year release cycle might be a disservice both for
> people who are willing to provide these features earlier, the users
> which are faced with a choice between a fast-moving third-party addon
> and a blessed core type and would cause overall confusion.

And the other option would be to *only* have a fast-moving third-party
addon, which simply disqualifies it completely in many environments.

Keeping it as a third party addon is better for the developer. Keeping
it in core is better for the user (if the user is a large company -
not a hacker).

If we can find a way to have a stable part in core and then have
addons that can provide these "tons of interesting features" (which I
agree there are) until such time that they can be considered stable
enough for core, I think that's the best compromise.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Sat, Dec 17, 2011 at 4:02 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:> to add some confusion on the
implementation choice, because it
> looks damn too easy now… Guile 2.0 offers an implementation of the> ECMAscript language and plscheme already exists as a PostgreSQL PL> extension for integrating with Guile.

TBH, I think that's PFC (pretty cool).
On Sun, Dec 18, 2011 at 6:41 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> We can hopefully get around this for the extensions in contrib (and
> reasonably well has already), but few large companies are going to be
> happy to go to pgxn and download an extension that has a single
> maintainer (not "the team", and in most cases not even "a team"),
> usually no defined lifecycle, no support, etc. (I'm pretty sure you
> won't get support included for random pgxn modules when you buy a
> contract from EDB, or CMD, or us, or PGX, or anybody really - wheras
> if it the datatype is in core, you *will* get this)

100% agree on all points. with the new extension system, contrib
modules that are packaged with the core system can be considered to be
in core because they are:
*) documented in standard docs
*) supported and bugfixed with postgresql releases
*) ready to be used without compiler support or even shell access
through most binary distributions

One small note about the json type being an extension -- this probably
means the json type oid won't be fixed -- not a huge deal but it could
affect some corner cases with binary format consumers.

merlin

On 12/18/2011 12:17 PM, Merlin Moncure wrote:
> One small note about the json type being an extension -- this probably
> means the json type oid won't be fixed -- not a huge deal but it could
> affect some corner cases with binary format consumers.
>
>

Why would that matter more for JSON than for any other non-core type?

cheers

andrew

On Sun, Dec 18, 2011 at 11:21 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>
> On 12/18/2011 12:17 PM, Merlin Moncure wrote:
>>
>> One small note about the json type being an extension -- this probably
>> means the json type oid won't be fixed -- not a huge deal but it could
>> affect some corner cases with binary format consumers.
>
> Why would that matter more for JSON than for any other non-core type?

well, it's a minor headache for all the oid-isn't-in-pgtypes.h types,
and only then for high traffic types (which presumably json will be).
a while back we coded up a reworked dblink that was variadic and
could optionally transfer data between database with the binary wire
format. any container of a user defined (by oid) type had to be sent
strictly as text which is a big performance hit for certain types.
recent postgres has an undocumented facility to force type oids to a
particular value, but the type definition being inside the create
extension script makes this problematic.

this is a pretty far out objection though, and I could certainly work
around the problem if necessary, but there is some dependency on
pg_types.h in the wild.

merlin

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> Why would that matter more for JSON than for any other non-core type?
>
> well, it's a minor headache for all the oid-isn't-in-pgtypes.h types,
> and only then for high traffic types (which presumably json will be).

Extensions are going to be more and more used and “pervasive” in next
years, and binary wire transfers is a good goal. What about creating
something like the PostgreSQL types IANA?

New type authors would register their OID and as a benefit would get
listed on some public reference sheet, and we could add some mechanism
so that default CREATE TYPE calls will not use reserved OID numbers.

Then it would be all cooperative only, so not a security thing, just a
way to ease binary and extension co-existence.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Sun, Dec 18, 2011 at 12:26 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>>> Why would that matter more for JSON than for any other non-core type?
>>
>> well, it's a minor headache for all the oid-isn't-in-pgtypes.h types,
>> and only then for high traffic types (which presumably json will be).
>
> Extensions are going to be more and more used and “pervasive” in next
> years, and binary wire transfers is a good goal.  What about creating
> something like the PostgreSQL types IANA?
>
> New type authors would register their OID and as a benefit would get
> listed on some public reference sheet, and we could add some mechanism
> so that default CREATE TYPE calls will not use reserved OID numbers.
>
> Then it would be all cooperative only, so not a security thing, just a
> way to ease binary and extension co-existence.

I think that's a fabulous idea,although we're drifting off the stated
topic here.

Getting back on point, I'm curious about your statement: "without
writing a single line of C". I took a look at the pl/scheme docs and
was pretty impressed -- what exactly would be involved to get a
guile-based ECMAscript working over the pl/scheme implementation? How
would that interact exactly with the stated topic -- JSON support? Do
you even need a json type if you have strong library based parsing and
composition features?

merlin

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> Getting back on point, I'm curious about your statement: "without
> writing a single line of C". I took a look at the pl/scheme docs and
> was pretty impressed -- what exactly would be involved to get a
> guile-based ECMAscript working over the pl/scheme implementation? How
> would that interact exactly with the stated topic -- JSON support? Do
> you even need a json type if you have strong library based parsing and
> composition features?

My understanding is that JSON is a subset of ECMAscript, so if you get
the latter you already have the former. Now, someone would have to
check if plscheme still build with guile-2.0, and given that, how
exactly you get pl/ecmascript (or pl/js) out of that.

I won't be in a position to spend time on that this year…

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Dec 18, 2011, at 4:41 AM, Magnus Hagander wrote:

> We can hopefully get around this for the extensions in contrib (and
> reasonably well has already), but few large companies are going to be
> happy to go to pgxn and download an extension that has a single
> maintainer (not "the team", and in most cases not even "a team"),
> usually no defined lifecycle, no support, etc. (I'm pretty sure you
> won't get support included for random pgxn modules when you buy a
> contract from EDB, or CMD, or us, or PGX, or anybody really - wheras
> if it the datatype is in core, you *will* get this)

I support having a JSON type in core, but question the assertions here. *Some* organizations won’t use PGXN, usually because they require things through a different ecosystem (RPMs, .debs, StackBuilder, etc.). But many others will. There are a *lot* of companies out there that use CPAN, easy_install, and Gem. The same sorts of places will use PGXN.

Oh, and at PGX, we’ll happily provide support for random modules, so long as you pay for our time. We’re not picky (and happy to send improvements back upstream), though we might recommend you switch to something better. But such evaluations are based on quality, not simply on what ecosystem it came from.

> If we can find a way to have a stable part in core and then have
> addons that can provide these "tons of interesting features" (which I
> agree there are) until such time that they can be considered stable
> enough for core, I think that's the best compromise.

+1, though I think the core type will at least need some basic operators and indexing support.

Best,

David

On Dec 19, 2011, at 2:49 AM, Dimitri Fontaine wrote:

> My understanding is that JSON is a subset of ECMAscript

Well, no, JSON is formally “a lightweight data-interchange format.” It’s derived from JavaScript syntax, but it is not a programming language, so I wouldn’t say it was accurate to describe it as a subset of JS or ECMAScript.

http://json.org/

IOW, one does not need a new PL to get this type.

Best,

David

On Dec 19, 2011, at 3:39 PM, David E. Wheeler wrote:

> Well, no, JSON is formally “a lightweight data-interchange format.” It’s derived from JavaScript syntax, but it is not a programming language, so I wouldn’t say it was accurate to describe it as a subset of JS or ECMAScript.

Bah, it says “It is based on a subset of the JavaScript Programming Language, Standard ECMA-262 3rd Edition - December 1999.” But my point still holds: it is not a programming language, and one does not need a PL to have a JSON data type.

Best,

David

On Mon, Dec 19, 2011 at 6:26 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> +1, though I think the core type will at least need some basic operators and indexing support.

And I'm willing to do that, but I thought it best to submit a bare
bones patch first, in the hopes of minimizing the number of
objectionable things therein. For example, if you want to be able to
index a JSON column, you have to decide on some collation order that
is consistent with JSON's notion of equality, and it's not obvious
what is most logical. Heck, equality itself isn't 100% obvious. If
there's adequate support for including JSON in core, and nobody
objects to my implementation, then I'll throw some ideas for those
things up against the wall and see what sticks.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Dec 20, 2011 at 00:26, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> On Dec 18, 2011, at 4:41 AM, Magnus Hagander wrote:
>
>> We can hopefully get around this for the extensions in contrib (and
>> reasonably well has already), but few large companies are going to be
>> happy to go to pgxn and download an extension that has a single
>> maintainer (not "the team", and in most cases not even "a team"),
>> usually no defined lifecycle, no support, etc. (I'm pretty sure you
>> won't get support included for random pgxn modules when you buy a
>> contract from EDB, or CMD, or us, or PGX, or anybody really - wheras
>> if it the datatype is in core, you *will* get this)
>
> I support having a JSON type in core, but question the assertions here. *Some* organizations won’t use PGXN, usually because they require things through a different ecosystem (RPMs, .debs, StackBuilder, etc.). But many others will. There are a *lot* of companies out there that use CPAN, easy_install, and Gem. The same sorts of places will use PGXN.

Yes, that's why I said "few" not "none".

Though in my experience, most companies are a lot more restrictive
about addons to their database than addons to their development
environments.

And note that it's not PGXN that's the problem I'm pointing at,
neither is it CPAN or easy_install or gem. The problem is the
vulnerability of the addon, and the maintenance. Meaning if it has a
single maintainer, that's a whole different thing from being
maintained by the PGDG.

> Oh, and at PGX, we’ll happily provide support for random modules, so long as you pay for our time. We’re not picky (and happy to send improvements back upstream), though we might recommend you switch to something better. But such evaluations are based on quality, not simply on what ecosystem it came from.

I think we're talking about different things here. While we can
certainly provide support on specific modules, after that is entered
into the agreement with the customer, we won't support a customer who
just calls up and says "hey, I'm using module xyz which you've never
heard of, and it crashes my database, please come fix it now". Are you
saying you do that - providing SLAs, 24/7 and similar things, on
modules you didn't even know the customer was using?

And FWIW, I'm talking about the quality, and not the ecosystem as
well. I'm just saying it takes a lot more work to verify the quality
and maintenance of an external module - if it's part of postgresql,
you have *already* got a quality stamp and a maintenance promise from
that.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Tue, Dec 20, 2011 at 06:00, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Dec 19, 2011 at 6:26 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
>> +1, though I think the core type will at least need some basic operators and indexing support.
>
> And I'm willing to do that, but I thought it best to submit a bare
> bones patch first, in the hopes of minimizing the number of
> objectionable things therein.  For example, if you want to be able to
> index a JSON column, you have to decide on some collation order that
> is consistent with JSON's notion of equality, and it's not obvious
> what is most logical.  Heck, equality itself isn't 100% obvious.  If
> there's adequate support for including JSON in core, and nobody
> objects to my implementation, then I'll throw some ideas for those
> things up against the wall and see what sticks.

+1 for getting the basics in first, and then adding more to it later.
There's still a fair amount of time to do that for 9.2, but not if we
get stuck bikeshedding again...

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

"David E. Wheeler" <david(at)justatheory(dot)com> writes:
> holds: it is not a programming language, and one does not need a PL to have
> a JSON data type.

Exactly. That does not contradict the fact that if you have
pl/ecmascript you already have JSON. And that we might as well have had
the ecmascript PL for some time now, we just need to check about that.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Tuesday, December 20, 2011 07:23:43 PM Dimitri Fontaine wrote:
> "David E. Wheeler" <david(at)justatheory(dot)com> writes:
> > holds: it is not a programming language, and one does not need a PL to
> > have a JSON data type.
> Exactly. That does not contradict the fact that if you have
> pl/ecmascript you already have JSON. And that we might as well have had
> the ecmascript PL for some time now, we just need to check about that.
Not really. You need to be able to "evaluate" json without it possibly
executing code. Many js implementations are likely to have such a feature
though.

Andres

On Dec 20, 2011, at 12:39 AM, David E. Wheeler wrote:
> On Dec 19, 2011, at 2:49 AM, Dimitri Fontaine wrote:
>
>> My understanding is that JSON is a subset of ECMAscript
>
> Well, no, JSON is formally “a lightweight data-interchange format.” It’s derived from JavaScript syntax, but it is not a programming language, so I wouldn’t say it was accurate to describe it as a subset of JS or ECMAScript.
>
> http://json.org/

Are people explicitly asking for a) *JSON* datatype or b) a type that lets you store arbitrary complex semi-untyped data structures?

if b) then this might get a lot more interesting

Cheers,
Claes

On Mon, Dec 19, 2011 at 5:49 AM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> My understanding is that JSON is a subset of ECMAscript, so if you get
> the latter you already have the former.  Now, someone would have to
> check if plscheme still build with guile-2.0, and given that, how
> exactly you get pl/ecmascript (or pl/js) out of that.

I don't think so.

I checked it out (still on pgfoundry, still on CVS, and code hasn't
been touched since 2008), and run into some issues.

- It looks for libguile.h
#include "libguile.h"
which, on 2.0, has shifted around from /usr/include/libguile.h (1.8)
to /usr/include/guile/2.0/libguile.h

It's not doing enough indirections internally; there is a guile-config
that is analogous to pg_config

postgres(at)cbbrowne [03:48:43] [~/PostgreSQL/plscheme]
-> % guile-config compile
-pthread -I/usr/include/guile/2.0
postgres(at)cbbrowne [03:48:45] [~/PostgreSQL/plscheme]
-> % guile-config link
-lguile-2.0 -lgc

It looks like there's something PG-related as a next issue:

-> % ./install.sh
pg_config : /var/lib/postgresql/dbs/postgresql-HEAD/bin/pg_config
module-dir : /var/lib/postgresql/dbs/postgresql-HEAD/lib
max-cache-size: 64
dbname : postgres
safe-r5rs : NO
dbacreate : NO
PSQL : /var/lib/postgresql/dbs/postgresql-HEAD/bin/psql postgres
CPPFLAGS : -g -Wall -fpic -c
-I/var/lib/postgresql/dbs/postgresql-HEAD/include/server
-I/usr/include/guile/2.0
LDFLAGS : -shared -lguile

Compiling... failed!

plscheme.c: In function '_PG_init':
plscheme.c:647:2: warning: implicit declaration of function
'DefineCustomStringVariable' [-Wimplicit-function-declaration]
plscheme.c:650:30: error: 'PGC_BACKEND' undeclared (first use in this function)
plscheme.c:650:30: note: each undeclared identifier is reported only
once for each function it appears in
plscheme.c:652:2: warning: implicit declaration of function
'DefineCustomIntVariable' [-Wimplicit-function-declaration]
plscheme.c: In function 'plscheme_func_handler':
plscheme.c:742:2: warning: implicit declaration of function
'GetTopTransactionId' [-Wimplicit-function-declaration]
plscheme.c: In function 'parse_trig_args':
plscheme.c:1623:44: error: dereferencing pointer to incomplete type
plscheme.c:1628:38: error: dereferencing pointer to incomplete type
...
(See error.log for details.)

I'm not sure to what degree this is bitrot relating to:
a) Postgres changes
b) Guile changes
but there's doubtless a bit of both.

I'd think it interesting to get this back to working order, whether
it's useful for JavaScript or not.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On Dec 19, 2011, at 9:00 PM, Robert Haas wrote:

>> +1, though I think the core type will at least need some basic operators and indexing support.
>
> And I'm willing to do that, but I thought it best to submit a bare
> bones patch first, in the hopes of minimizing the number of
> objectionable things therein. For example, if you want to be able to
> index a JSON column, you have to decide on some collation order that
> is consistent with JSON's notion of equality, and it's not obvious
> what is most logical. Heck, equality itself isn't 100% obvious. If
> there's adequate support for including JSON in core, and nobody
> objects to my implementation, then I'll throw some ideas for those
> things up against the wall and see what sticks.

+1 Sounds good to me.

David

On Dec 20, 2011, at 2:13 AM, Magnus Hagander wrote:

> Yes, that's why I said "few" not "none".
>
> Though in my experience, most companies are a lot more restrictive
> about addons to their database than addons to their development
> environments.

Yeah, we’re getting off-topic here, so I’ll just say something we can agree on: We’ll see.

I do still want to see some processes for getting PGXN distributions into RPM/.deb/StackBuilder, though.

Best,

David

On Dec 20, 2011, at 10:39 AM, Claes Jakobsson wrote:

> Are people explicitly asking for a) *JSON* datatype or b) a type that lets you store arbitrary complex semi-untyped data structures?

Yes.

> if b) then this might get a lot more interesting

JSON is the most popular/likely way to represent that, I think.

David

Let me mention another lightweight data-interchange format.

At http://www.janestreet.com we have developed a small c module to deal
with S-expressions (sexp) as a way to store arbitrary data. As we write
most of our code in OCaml sexps are a natural way for us to store data.
http://hg.ocaml.info/release/sexplib/ provides automatic ways to convert
"any" ocaml value into a sexp).

The extension is still pretty new but we use it successfully on a daily
basis. After we have upgraded to 9.x we will pack it as an extension
and releast it opensource.

API wise the module at the moment offers the following:

sexp_validate(text) returns boolean
Validate that the passed in text is a valid s expression.

create domain sexp as text check (sexp_validate(value));

BTW: It is a PITA that arrays of domains are not valid types.

And several functions to manipulate take apart sexp's or modify sexp's
using a path into the sexp (similar to what xpath does for xml).

Such as:

sexp_get(sexp, text) returns sexp
Get the sub sexp of sexp identified by the path.
Returns NULL if path is not a valid path in sexp.
Example:
path=.a space.b.[1].x
((ignore this) ("a space" ((b (0 ((also ignored) (x "The Value")) )) )))
-> "The Value"

And sexp_get_atom(sexp, text) returns text
Get the sub atom of sexp identified by the path.
Returns NULL if path is not a valid path in sexp or
does not identify an atom.
Example:
path=.a space.b.[1].x
((ignore this) ("a space" ((b (0 ((also ignored) (x "The Value")) )) )))
^^^^^^^^^
-> The Value

Cheers,

Bene

On 20/12/11 19:39, Claes Jakobsson wrote:
> On Dec 20, 2011, at 12:39 AM, David E. Wheeler wrote:
> > On Dec 19, 2011, at 2:49 AM, Dimitri Fontaine wrote:
> >
> >> My understanding is that JSON is a subset of ECMAscript
> >
> > Well, no, JSON is formally “a lightweight data-interchange format.” It’s derived from JavaScript syntax, but it is not a programming language, so I wouldn’t say it was accurate to describe it as a subset of JS or ECMAScript.
> >
> > http://json.org/
>
> Are people explicitly asking for a) *JSON* datatype or b) a type that lets you store arbitrary complex semi-untyped data structures?
>
> if b) then this might get a lot more interesting
>
> Cheers,
> Claes
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Dec 20, 2011 at 9:06 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> On Dec 20, 2011, at 10:39 AM, Claes Jakobsson wrote:
>> Are people explicitly asking for a) *JSON* datatype or b) a type that lets you store arbitrary complex semi-untyped data structures?
>
> Yes.
>
>> if b) then this might get a lot more interesting
>
> JSON is the most popular/likely way to represent that, I think.

On that note, here's an updated version of the patch I posted
upthread, with some regression tests and minimal documentation.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2012/1/11 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Tue, Dec 20, 2011 at 9:06 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
>> On Dec 20, 2011, at 10:39 AM, Claes Jakobsson wrote:
>>> Are people explicitly asking for a) *JSON* datatype or b) a type that lets you store arbitrary complex semi-untyped data structures?
>>
>> Yes.
>>
>>> if b) then this might get a lot more interesting
>>
>> JSON is the most popular/likely way to represent that, I think.
>
> On that note, here's an updated version of the patch I posted
> upthread, with some regression tests and minimal documentation.

I like this patch and this feature.

I see only one issue - there is not functionality that helps generate
JSON in pg.

What do you think about functions: array_to_json(anyarray),
row_to_json(any) and format_json(text, text, ...)

Regards

Pavel

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On 01/11/2012 01:18 AM, Pavel Stehule wrote:
>
> I like this patch and this feature.

I'm about to read the patch in detail - I certainly like the feature.

>
> I see only one issue - there is not functionality that helps generate
> JSON in pg.
>
> What do you think about functions: array_to_json(anyarray),
> row_to_json(any) and format_json(text, text, ...)
>

Actually, more than these, I (and at least one very interested client)
want query_to_json, which would do something like:

# select q2json('select $$a$$ || x as b, y as c from generate_series(1,3) x, generate_series(4,5) y');
q2json
---------------------------------------------------------------------------------------------------------
[{"b":"a1","c":4},{"b":"a1","c":5},{"b":"a2","c":4},{"b":"a2","c":5},{"b":"a3","c":4},{"b":"a3","c":5}]

No doubt several variants are possible such as returning a setof json,
one per row, instead of a single json, and allowing query parameters as
separate arguments (maybe just using variadic functions), but probably
for a first go just something as simple as this would meet the case.

Given the short time span available before patches must be in, I am
prepared to work on this ASAP.

cheers

andrew

2012/1/11 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> On 01/11/2012 01:18 AM, Pavel Stehule wrote:
>>
>>
>> I like this patch and this feature.
>
>
> I'm about to read the patch in detail - I certainly like the feature.
>
>
>>
>> I see only one issue - there is not functionality that helps generate
>> JSON in pg.
>>
>> What do you think about functions: array_to_json(anyarray),
>> row_to_json(any) and format_json(text, text, ...)
>>
>
> Actually, more than these, I (and at least one very interested client) want
> query_to_json, which would do something like:
>
>   # select q2json('select $$a$$ || x as b, y as c from generate_series(1,3)
> x, generate_series(4,5) y');
>                                                     q2json
>
> ---------------------------------------------------------------------------------------------------------
>
> [{"b":"a1","c":4},{"b":"a1","c":5},{"b":"a2","c":4},{"b":"a2","c":5},{"b":"a3","c":4},{"b":"a3","c":5}]
>

we have a query_to_xml - so there should similar query_to_json. But
this is not enough for usage from SP. What about two rich functions

* query_to_json - by your proposal
* array_to_json - with possibility to serialize array of records

This can be a basic set

Regards

Pavel

>
> No doubt several variants are possible such as returning a setof json, one
> per row, instead of a single json, and allowing query parameters as separate
> arguments (maybe just using variadic functions), but probably for a first go
> just something as simple as this would meet the case.

>
> Given the short time span available before patches must be in, I am prepared
> to work on this ASAP.
>
> cheers
>
> andrew
>

On Wed, Jan 11, 2012 at 1:18 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2012/1/11 Robert Haas <robertmhaas(at)gmail(dot)com>:
>> On Tue, Dec 20, 2011 at 9:06 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
>>> On Dec 20, 2011, at 10:39 AM, Claes Jakobsson wrote:
>>>> Are people explicitly asking for a) *JSON* datatype or b) a type that lets you store arbitrary complex semi-untyped data structures?
>>>
>>> Yes.
>>>
>>>> if b) then this might get a lot more interesting
>>>
>>> JSON is the most popular/likely way to represent that, I think.
>>
>> On that note, here's an updated version of the patch I posted
>> upthread, with some regression tests and minimal documentation.
>
> I like this patch and this feature.
>
> I see only one issue - there is not functionality that helps generate
> JSON in pg.
>
> What do you think about functions: array_to_json(anyarray),
> row_to_json(any) and format_json(text, text, ...)

I think we might want all of that stuff, but I doubt there is time to
do it for 9.2.

Actually, I think the next logical step would be to define equality
(is there an official definition of that for JSON?) and build a btree
opclass. I believe the code I've already written could be extended to
construct an abstract syntax tree for those operations that need it.
But we need to make some decisions first. A btree opclass requires a
total ordering, so we have to arbitrarily define whether 1 < true, 1 <
[1], 1 < "1", etc.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2012/1/11 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Wed, Jan 11, 2012 at 1:18 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2012/1/11 Robert Haas <robertmhaas(at)gmail(dot)com>:
>>> On Tue, Dec 20, 2011 at 9:06 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
>>>> On Dec 20, 2011, at 10:39 AM, Claes Jakobsson wrote:
>>>>> Are people explicitly asking for a) *JSON* datatype or b) a type that lets you store arbitrary complex semi-untyped data structures?
>>>>
>>>> Yes.
>>>>
>>>>> if b) then this might get a lot more interesting
>>>>
>>>> JSON is the most popular/likely way to represent that, I think.
>>>
>>> On that note, here's an updated version of the patch I posted
>>> upthread, with some regression tests and minimal documentation.
>>
>> I like this patch and this feature.
>>
>> I see only one issue - there is not functionality that helps generate
>> JSON in pg.
>>
>> What do you think about functions: array_to_json(anyarray),
>> row_to_json(any) and format_json(text, text, ...)
>
> I think we might want all of that stuff, but I doubt there is time to
> do it for 9.2.
>
> Actually, I think the next logical step would be to define equality
> (is there an official definition of that for JSON?) and build a btree
> opclass.  I believe the code I've already written could be extended to
> construct an abstract syntax tree for those operations that need it.
> But we need to make some decisions first.  A btree opclass requires a
> total ordering, so we have to arbitrarily define whether 1 < true, 1 <
> [1], 1 < "1", etc.
>

I don't understand why we have to do it?

We don't support similar functionality for XML, so why for JSON?

Pavel

> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

On Wed, Jan 11, 2012 at 8:38 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> I don't understand why we have to do it?
>
> We don't support similar functionality for XML, so why for JSON?

Hrm. Well, that's an interesting point. Maybe we don't. I assumed
that people would eventually want to optimize queries of the form
SELECT whatever FROM tab WHERE jsoncol = 'constant'. If that's a
sufficiently marginal use case that we don't care, then fine.

One difference between JSON and XML is that XML really has no
well-defined comparison semantics. For example, consider:

<foo><bar>1.0</bar></foo>
<foo><bar>1.0</bar> </foo>

If the XML is being used as a transport mechanism, then the extra
space is semantically insignificant, but if this is markup, then it
might matter a lot. Also, consider:

<foo><bar>1.00</bar></foo>

That one might be equal if we think 1.0 is intended to be a number,
but if it's intended as a string then it's not. We could perhaps do
comparisons in XML relative to some DTD or schema if those provide
details about what the values mean, but in a vacuum it's not
well-defined. On the other hand, in JSON, it's pretty clear that { 1,
2, 3 } is the same value as {1,2,3} but "1,2,3" is different from "1,
2, 3". There are some borderline cases that might need some sweat,
like whether 1 = 1.0 = 1.00 = 1e0, but in general the level of
ambiguity seems to me to be much less, making it more feasible here
than it would be for XML.

That having been said, uncertainties about whether we want this at all
(and if so in what form) are exactly why I didn't include this kind of
stuff in the patch to begin with, and I think that if we get this much
committed for 9.2 we'll be doing pretty well. If we can agree on and
do more, great; if not, we'll at least have this much, which IMHO
would be an improvement over what we have now.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2012/1/11 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Wed, Jan 11, 2012 at 8:38 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> I don't understand why we have to do it?
>>
>> We don't support similar functionality for XML, so why for JSON?
>
> Hrm.  Well, that's an interesting point.  Maybe we don't.  I assumed
> that people would eventually want to optimize queries of the form
> SELECT whatever FROM tab WHERE jsoncol = 'constant'.  If that's a
> sufficiently marginal use case that we don't care, then fine.
>
> One difference between JSON and XML is that XML really has no
> well-defined comparison semantics.  For example, consider:
>
> <foo><bar>1.0</bar></foo>
> <foo><bar>1.0</bar> </foo>
>
> If the XML is being used as a transport mechanism, then the extra
> space is semantically insignificant, but if this is markup, then it
> might matter a lot.  Also, consider:
>
> <foo><bar>1.00</bar></foo>
>
> That one might be equal if we think 1.0 is intended to be a number,
> but if it's intended as a string then it's not.  We could perhaps do
> comparisons in XML relative to some DTD or schema if those provide
> details about what the values mean, but in a vacuum it's not
> well-defined.  On the other hand, in JSON, it's pretty clear that { 1,
> 2, 3 } is the same value as {1,2,3} but "1,2,3" is different from "1,
> 2, 3".  There are some borderline cases that might need some sweat,
> like whether 1 = 1.0 = 1.00 = 1e0, but in general the level of
> ambiguity seems to me to be much less, making it more feasible here
> than it would be for XML.
>
> That having been said, uncertainties about whether we want this at all
> (and if so in what form) are exactly why I didn't include this kind of
> stuff in the patch to begin with, and I think that if we get this much
> committed for 9.2 we'll be doing pretty well.  If we can agree on and
> do more, great; if not, we'll at least have this much, which IMHO
> would be an improvement over what we have now.
>

I understand it now. My opinion is so some operators and index search
can be in 9.2 - so use a JSON just as communication format now.

* we need to build JSON
* we need to check if some is valid JSON
* we need to store JSON

other steps should be (9.2)
* basic operators eq, neeq
* some tool like XQuery - simple query on JSON document available from
SQL that can be used for functional indexes.

> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

On Wed, Jan 11, 2012 at 9:41 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> I understand it now. My opinion is so some operators and index search
> can be in 9.2 - so use a JSON just as communication format now.
>
> * we need to build JSON
> * we need to check if some is valid JSON
> * we need to store JSON
>
> other steps should be (9.2)
> * basic operators eq, neeq
> * some tool like XQuery - simple query on JSON document available from
> SQL that can be used for functional indexes.

That would be nice, but let's not let the perfect be the enemy of the
good. We don't have a lot of time here.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2012/1/11 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Wed, Jan 11, 2012 at 9:41 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> I understand it now. My opinion is so some operators and index search
>> can be in 9.2 - so use a JSON just as communication format now.
>>
>> * we need to build JSON
>> * we need to check if some is valid JSON
>> * we need to store JSON
>>
>> other steps should be (9.2)
>> * basic operators eq, neeq
>> * some tool like XQuery - simple query on JSON document available from
>> SQL that can be used for functional indexes.
>
> That would be nice, but let's not let the perfect be the enemy of the
> good.  We don't have a lot of time here.
>

sorry - replace 9.2 by 9.3 - I am sorry

I am able to write array_to_json fce and Andrew can write query_to_json

Pavel

> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

On 01/11/2012 10:21 AM, Pavel Stehule wrote:
> 2012/1/11 Robert Haas<robertmhaas(at)gmail(dot)com>:
>> On Wed, Jan 11, 2012 at 9:41 AM, Pavel Stehule<pavel(dot)stehule(at)gmail(dot)com> wrote:
>>> I understand it now. My opinion is so some operators and index search
>>> can be in 9.2 - so use a JSON just as communication format now.
>>>
>>> * we need to build JSON
>>> * we need to check if some is valid JSON
>>> * we need to store JSON
>>>
>>> other steps should be (9.2)
>>> * basic operators eq, neeq
>>> * some tool like XQuery - simple query on JSON document available from
>>> SQL that can be used for functional indexes.
>> That would be nice, but let's not let the perfect be the enemy of the
>> good. We don't have a lot of time here.
>>
> sorry - replace 9.2 by 9.3 - I am sorry
>
> I am able to write array_to_json fce and Andrew can write query_to_json

For those who want to play along, see
<https://bitbucket.org/adunstan/pgdevel> which has Robert's patch and my
additions to it.

I'm actually half way through writing an array_to_json function, since
it it necessary anyway for query_to_json. I hope to have a fairly
complete working function in about 24 hours.

cheers

andrew

I wrote an array_to_json function during GSoC 2010:

http://git.postgresql.org/gitweb/?p=json-datatype.git;a=blob;f=json_io.c#l289

It's not exposed as a procedure called array_to_json: it's part of the
to_json function, which decides what to do based on the argument type.

- Joey

On 01/12/2012 09:00 AM, Joey Adams wrote:
> I wrote an array_to_json function during GSoC 2010:
>
> http://git.postgresql.org/gitweb/?p=json-datatype.git;a=blob;f=json_io.c#l289
>
> It's not exposed as a procedure called array_to_json: it's part of the
> to_json function, which decides what to do based on the argument type.
>

Excellent, this is just the point at which I stopped work last night, so
with your permission I'll steal this and it will save me a good chunk of
time.

cheers

andrew

2012/1/12 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> On 01/12/2012 09:00 AM, Joey Adams wrote:
>>
>> I wrote an array_to_json function during GSoC 2010:
>>
>>
>> http://git.postgresql.org/gitweb/?p=json-datatype.git;a=blob;f=json_io.c#l289
>>
>> It's not exposed as a procedure called array_to_json: it's part of the
>> to_json function, which decides what to do based on the argument type.
>>
>
>
> Excellent, this is just the point at which I stopped work last night, so
> with your permission I'll steal this and it will save me a good chunk of
> time.
>

this should be little bit more enhanced to support a row arrays - it
can be merged with some routines from pst tool
http://okbob.blogspot.com/2010/11/new-version-of-pst-collection-is.html

Regards

Pavel

> cheers
>
> andrew
>

On 01/12/2012 10:44 AM, Pavel Stehule wrote:
> 2012/1/12 Andrew Dunstan<andrew(at)dunslane(dot)net>:
>>
>> On 01/12/2012 09:00 AM, Joey Adams wrote:
>>> I wrote an array_to_json function during GSoC 2010:
>>>
>>>
>>> http://git.postgresql.org/gitweb/?p=json-datatype.git;a=blob;f=json_io.c#l289
>>>
>>> It's not exposed as a procedure called array_to_json: it's part of the
>>> to_json function, which decides what to do based on the argument type.
>>>
>>
>> Excellent, this is just the point at which I stopped work last night, so
>> with your permission I'll steal this and it will save me a good chunk of
>> time.
>>
> this should be little bit more enhanced to support a row arrays - it
> can be merged with some routines from pst tool
> http://okbob.blogspot.com/2010/11/new-version-of-pst-collection-is.html
>
>

I will be covering composites.

cheers

andrew

On Thu, Jan 12, 2012 at 9:51 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> On 01/12/2012 10:44 AM, Pavel Stehule wrote:
>> this should be little bit more enhanced to support a row arrays - it
>> can be merged with some routines from pst tool
>> http://okbob.blogspot.com/2010/11/new-version-of-pst-collection-is.html
>
> I will be covering composites.

curious, will your function work on unregistered composites? What
would this do?

select array_to_json(array[row('a', 1), row('b', 2)]);

merlin

2012/1/12 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On Thu, Jan 12, 2012 at 9:51 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> On 01/12/2012 10:44 AM, Pavel Stehule wrote:
>>> this should be little bit more enhanced to support a row arrays - it
>>> can be merged with some routines from pst tool
>>> http://okbob.blogspot.com/2010/11/new-version-of-pst-collection-is.html
>>
>> I will be covering composites.
>
> curious, will your function work on unregistered composites?  What
> would this do?
>
> select array_to_json(array[row('a', 1), row('b', 2)]);

it can do it - but it has to use some defaults for names.

Pavel

>
> merlin

On 01/12/2012 11:21 AM, Merlin Moncure wrote:
> On Thu, Jan 12, 2012 at 9:51 AM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>> On 01/12/2012 10:44 AM, Pavel Stehule wrote:
>>> this should be little bit more enhanced to support a row arrays - it
>>> can be merged with some routines from pst tool
>>> http://okbob.blogspot.com/2010/11/new-version-of-pst-collection-is.html
>> I will be covering composites.
> curious, will your function work on unregistered composites? What
> would this do?
>
> select array_to_json(array[row('a', 1), row('b', 2)]);
>

Expected behaviour is something like this:

andrew=# select q2json('
select $$a$$ || x as b,
y as c,
array[row(x.*,array[1,2,3]),
row(y.*,array[4,5,6])] as z
from generate_series(1,2) x,
generate_series(4,5) y');

[{"b":"a1","c":4,"z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]},
{"b":"a1","c":5,"z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]},
{"b":"a2","c":4,"z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]},
{"b":"a2","c":5,"z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}]

cheers

andrew

On 01/12/2012 10:51 AM, Andrew Dunstan wrote:
>
>
> On 01/12/2012 10:44 AM, Pavel Stehule wrote:
>> 2012/1/12 Andrew Dunstan<andrew(at)dunslane(dot)net>:
>>>
>>> On 01/12/2012 09:00 AM, Joey Adams wrote:
>>>> I wrote an array_to_json function during GSoC 2010:
>>>>
>>>>
>>>> http://git.postgresql.org/gitweb/?p=json-datatype.git;a=blob;f=json_io.c#l289
>>>>
>>>>
>>>> It's not exposed as a procedure called array_to_json: it's part of the
>>>> to_json function, which decides what to do based on the argument type.
>>>>
>>>
>>> Excellent, this is just the point at which I stopped work last
>>> night, so
>>> with your permission I'll steal this and it will save me a good
>>> chunk of
>>> time.
>>>
>> this should be little bit more enhanced to support a row arrays - it
>> can be merged with some routines from pst tool
>> http://okbob.blogspot.com/2010/11/new-version-of-pst-collection-is.html
>>
>>
>
> I will be covering composites.
>
>

OK, here's a patch that does both query_to_json and array_to_json, along
with docs and regression tests. It include Robert's original patch,
although I can produce a differential patch if required. It can also be
pulled from <https://bitbucket.org/adunstan/pgdevel>

A couple of things to note. First, the problem about us losing column
names that I noted a couple of months ago and Tom did a bit of work on
is exercised by this. We really need to fix it. Example:

andrew=# select array_to_json(array_agg(row(z.*)))
from (select $$a$$ || x as b,
y as c,
array[row(x.*,array[1,2,3]),
row(y.*,array[4,5,6])] as z
from generate_series(1,1) x,
generate_series(4,4) y) z;
array_to_json
-------------------------------------------------------------------------
[{"f1":"a1","f2":4,"f3":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}]
(1 row)

Here we've lost b, c and z as column names.

Second, what should be do when the database encoding isn't UTF8? I'm
inclined to emit a \unnnn escape for any non-ASCII character (assuming
it has a unicode code point - are there any code points in the
non-unicode encodings that don't have unicode equivalents?). The
alternative would be to fail on non-ASCII characters, which might be
ugly. Of course, anyone wanting to deal with JSON should be using UTF8
anyway, but we still have to deal with these things. What about
SQL_ASCII? If there's a non-ASCII sequence there we really have no way
of telling what it should be. There at least I think we should probably
error out.

cheers

andrew

2012/1/14 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> On 01/12/2012 10:51 AM, Andrew Dunstan wrote:
>>
>>
>>
>> On 01/12/2012 10:44 AM, Pavel Stehule wrote:
>>>
>>> 2012/1/12 Andrew Dunstan<andrew(at)dunslane(dot)net>:
>>>>
>>>>
>>>> On 01/12/2012 09:00 AM, Joey Adams wrote:
>>>>>
>>>>> I wrote an array_to_json function during GSoC 2010:
>>>>>
>>>>>
>>>>>
>>>>> http://git.postgresql.org/gitweb/?p=json-datatype.git;a=blob;f=json_io.c#l289
>>>>>
>>>>> It's not exposed as a procedure called array_to_json: it's part of the
>>>>> to_json function, which decides what to do based on the argument type.
>>>>>
>>>>
>>>> Excellent, this is just the point at which I stopped work last night, so
>>>> with your permission I'll steal this and it will save me a good chunk of
>>>> time.
>>>>
>>> this should be little bit more enhanced to support a row arrays - it
>>> can be merged with some routines from pst tool
>>> http://okbob.blogspot.com/2010/11/new-version-of-pst-collection-is.html
>>>
>>>
>>
>> I will be covering composites.
>>
>>
>
> OK, here's a patch that does both query_to_json and array_to_json, along
> with docs and regression tests. It include Robert's original patch, although
> I can produce a differential patch if required. It can also be pulled from
> <https://bitbucket.org/adunstan/pgdevel>
>
> A couple of things to note. First, the problem about us losing column names
> that I noted a couple of months ago and Tom did a bit of work on is
> exercised by this. We really need to fix it. Example:
>

support SELECT ROW (x AS "real name", y AS "real name") is good idea
and should be used more time than only here.

Regards

Pavel

>   andrew=#  select array_to_json(array_agg(row(z.*)))
>       from (select $$a$$ || x as b,
>
>                y as c,
>                array[row(x.*,array[1,2,3]),
>                      row(y.*,array[4,5,6])] as z
>             from generate_series(1,1) x,
>                  generate_series(4,4) y) z;
>                                  array_to_json
>   -------------------------------------------------------------------------
>     [{"f1":"a1","f2":4,"f3":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}]
>   (1 row)
>
>
> Here we've lost b, c and z as column names.
>
> Second, what should be do when the database encoding isn't UTF8? I'm
> inclined to emit a \unnnn escape for any non-ASCII character (assuming it
> has a unicode code point - are there any code points in the non-unicode
> encodings that don't have unicode equivalents?). The alternative would be to
> fail on non-ASCII characters, which might be ugly. Of course, anyone wanting
> to deal with JSON should be using UTF8 anyway, but we still have to deal
> with these things. What about SQL_ASCII? If there's a non-ASCII sequence
> there we really have no way of telling what it should be. There at least I
> think we should probably error out.
>
> cheers
>
> andrew
>
>
>

On Sat, Jan 14, 2012 at 3:06 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Second, what should be do when the database encoding isn't UTF8? I'm
> inclined to emit a \unnnn escape for any non-ASCII character (assuming it
> has a unicode code point - are there any code points in the non-unicode
> encodings that don't have unicode equivalents?). The alternative would be to
> fail on non-ASCII characters, which might be ugly. Of course, anyone wanting
> to deal with JSON should be using UTF8 anyway, but we still have to deal
> with these things. What about SQL_ASCII? If there's a non-ASCII sequence
> there we really have no way of telling what it should be. There at least I
> think we should probably error out.

I don't think there is a satisfying solution to this problem. Things
working against us:

* Some server encodings support characters that don't map to Unicode
characters (e.g. unused slots in Windows-1252). Thus, converting to
UTF-8 and back is lossy in general.

* We want a normalized representation for comparison. This will
involve a mixture of server and Unicode characters, unless the
encoding is UTF-8.

* We can't efficiently convert individual characters to and from
Unicode with the current API.

* What do we do about \u0000 ? TEXT datums cannot contain NUL characters.

I'd say just ban Unicode escapes and non-ASCII characters unless the
server encoding is UTF-8, and ban all \u0000 escapes. It's easy, and
whatever we support later will be a superset of this.

Strategies for handling this situation have been discussed in prior
emails. This is where things got stuck last time.

- Joey

On 01/14/2012 06:11 PM, Joey Adams wrote:
> On Sat, Jan 14, 2012 at 3:06 PM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>> Second, what should be do when the database encoding isn't UTF8? I'm
>> inclined to emit a \unnnn escape for any non-ASCII character (assuming it
>> has a unicode code point - are there any code points in the non-unicode
>> encodings that don't have unicode equivalents?). The alternative would be to
>> fail on non-ASCII characters, which might be ugly. Of course, anyone wanting
>> to deal with JSON should be using UTF8 anyway, but we still have to deal
>> with these things. What about SQL_ASCII? If there's a non-ASCII sequence
>> there we really have no way of telling what it should be. There at least I
>> think we should probably error out.
> I don't think there is a satisfying solution to this problem. Things
> working against us:
>
> * Some server encodings support characters that don't map to Unicode
> characters (e.g. unused slots in Windows-1252). Thus, converting to
> UTF-8 and back is lossy in general.
>
> * We want a normalized representation for comparison. This will
> involve a mixture of server and Unicode characters, unless the
> encoding is UTF-8.
>
> * We can't efficiently convert individual characters to and from
> Unicode with the current API.
>
> * What do we do about \u0000 ? TEXT datums cannot contain NUL characters.
>
> I'd say just ban Unicode escapes and non-ASCII characters unless the
> server encoding is UTF-8, and ban all \u0000 escapes. It's easy, and
> whatever we support later will be a superset of this.
>
> Strategies for handling this situation have been discussed in prior
> emails. This is where things got stuck last time.
>

Well, from where I'm coming from, nuls are not a problem. But
escape_json() is currently totally encoding-unaware. It produces \unnnn
escapes for low ascii characters, and just passes through characters
with the high bit set. That's possibly OK for EXPLAIN output - we really
don't want don't want EXPLAIN failing. But maybe we should ban JSON
output for EXPLAIN if the encoding isn't UTF8.

Another question in my mind is what to do when the client encoding isn't
UTF8.

None of these is an insurmountable problem, ISTM - we just need to make
some decisions.

cheers

andrew

I am very interested in experimenting with functional indexes into JSON
structures. I think this could be very powerful combined with full text
search as well as constraints. It would allow for using postgres as an
unstructured data store without sacrificing the powerful indexing features,
durability, and transactional semantics that comes with using postgres or
RDBMSes in general.

One use case in particular I have been trying to solve for lately is
persisting and synchronizing client-side state (in a mobile application)
with a server. It would be nice to have a flat, schemaless table (maybe a
table that's like (id, type, owner, data) where data would be a JSON blob).
I could do this now without JSON support, but I think indexing inside that
JSON blob and having validation database side is valuable as well. And as
I mentioned before, i'd rather not throw out the baby with the bathwater by
using a different type of database because ACID, replication, and
constraints are also very important to this. As is being consistent with
the rest of our technology stack. (I'd essentially be using a relational
database to persist an object database)

I'm also not too concerned about storage consumption with this (even though
columnar compression would help a lot in the future) since it's easily
partitionable by user ID.

For my case the equivalent of postgres's XPath would work. Also having it
as a maintained contrib module would be sufficient, although it being part
of core as XPath is would be even better.

Just my $0.02... even if I'm a bit late to the conversation.

Thanks!
Mike

On 01/14/2012 03:06 PM, Andrew Dunstan wrote:
>
>
>
> OK, here's a patch that does both query_to_json and array_to_json,
> along with docs and regression tests. It include Robert's original
> patch, although I can produce a differential patch if required. It can
> also be pulled from <https://bitbucket.org/adunstan/pgdevel>
>
>

Here's an update that adds row_to_json, plus a bit more cleanup. Example:

andrew=# SELECT row_to_json(q)
FROM (SELECT $$a$$ || x AS b,
y AS c,
ARRAY[ROW(x.*,ARRAY[1,2,3]),
ROW(y.*,ARRAY[4,5,6])] AS z
FROM generate_series(1,2) x,
generate_series(4,5) y) q;
row_to_json
--------------------------------------------------------------------
{"b":"a1","c":4,"z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}
{"b":"a1","c":5,"z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}
{"b":"a2","c":4,"z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}
{"b":"a2","c":5,"z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}
(4 rows)

(This might be more to Robert's taste than query_to_json() :-) )

cheers

andrew

On Sat, Jan 14, 2012 at 3:06 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Second, what should be do when the database encoding isn't UTF8? I'm
> inclined to emit a \unnnn escape for any non-ASCII character (assuming it
> has a unicode code point - are there any code points in the non-unicode
> encodings that don't have unicode equivalents?). The alternative would be to
> fail on non-ASCII characters, which might be ugly. Of course, anyone wanting
> to deal with JSON should be using UTF8 anyway, but we still have to deal
> with these things. What about SQL_ASCII? If there's a non-ASCII sequence
> there we really have no way of telling what it should be. There at least I
> think we should probably error out.

I don't see any reason to escape anything more than the minimum
required by the spec, which only requires it for control characters.
If somebody's got a non-ASCII character in there, we can simply allow
it to be represented by itself. That's almost certainly more compact
(and very possibly more readable) than emitting \uXXXX for each such
instance, and it also matches what the current EXPLAIN (FORMAT JSON)
output does.

In other words, let's decree that when the database encoding isn't
UTF-8, *escaping* of non-ASCII characters doesn't work. But
*unescaped* non-ASCII characters should still work just fine.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 01/19/2012 03:49 PM, Robert Haas wrote:
>
> In other words, let's decree that when the database encoding isn't
> UTF-8, *escaping* of non-ASCII characters doesn't work. But
> *unescaped* non-ASCII characters should still work just fine.

The spec only allows unescaped Unicode chars (and for our purposes that
means UTF8). An unescaped non-ASCII character in, say, ISO-8859-1 will
result in something that's not legal JSON. See
<http://www.ietf.org/rfc/rfc4627.txt?number=4627> section 3.

cheers

andrew

On Thu, Jan 19, 2012 at 4:07 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> On 01/19/2012 03:49 PM, Robert Haas wrote:
>> In other words, let's decree that when the database encoding isn't
>> UTF-8, *escaping* of non-ASCII characters doesn't work.  But
>> *unescaped* non-ASCII characters should still work just fine.
>
> The spec only allows unescaped Unicode chars (and for our purposes that
> means UTF8). An unescaped non-ASCII character in, say, ISO-8859-1 will
> result in something that's not legal JSON. See
> <http://www.ietf.org/rfc/rfc4627.txt?number=4627> section 3.

I understand. I'm proposing that we not care. In other words, if the
server encoding is UTF-8, it'll really be JSON. But if the server
encoding is something else, it'll be almost-JSON. And specifically,
the \uXXXX syntax won't work, and there might be some non-Unicode
characters in there. If that's not the behavior you want, then use
UTF-8.

It seems pretty clear that we're going to have to make some trade-off
to handle non-UTF8 encodings, and I think what I'm suggesting is a lot
less painful than disabling high-bit characters altogether. If we do
that, then what happens if a user runs EXPLAIN (FORMAT JSON) and his
column label has a non-Unicode character in there? Should we say, oh,
sorry, you can't explain that in JSON format? That is mighty
unfriendly, and probably mighty complicated and expensive to figure
out, too. We *do not support* mixing encodings in the same database,
and if we make it the job of this patch to fix that problem, we're
going to be in the same place for 9.2 that we have been for the last
several releases: nowhere.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 01/19/2012 04:12 PM, Robert Haas wrote:
> On Thu, Jan 19, 2012 at 4:07 PM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>> On 01/19/2012 03:49 PM, Robert Haas wrote:
>>> In other words, let's decree that when the database encoding isn't
>>> UTF-8, *escaping* of non-ASCII characters doesn't work. But
>>> *unescaped* non-ASCII characters should still work just fine.
>> The spec only allows unescaped Unicode chars (and for our purposes that
>> means UTF8). An unescaped non-ASCII character in, say, ISO-8859-1 will
>> result in something that's not legal JSON. See
>> <http://www.ietf.org/rfc/rfc4627.txt?number=4627> section 3.
> I understand. I'm proposing that we not care. In other words, if the
> server encoding is UTF-8, it'll really be JSON. But if the server
> encoding is something else, it'll be almost-JSON. And specifically,
> the \uXXXX syntax won't work, and there might be some non-Unicode
> characters in there. If that's not the behavior you want, then use
> UTF-8.
>
> It seems pretty clear that we're going to have to make some trade-off
> to handle non-UTF8 encodings, and I think what I'm suggesting is a lot
> less painful than disabling high-bit characters altogether. If we do
> that, then what happens if a user runs EXPLAIN (FORMAT JSON) and his
> column label has a non-Unicode character in there? Should we say, oh,
> sorry, you can't explain that in JSON format? That is mighty
> unfriendly, and probably mighty complicated and expensive to figure
> out, too. We *do not support* mixing encodings in the same database,
> and if we make it the job of this patch to fix that problem, we're
> going to be in the same place for 9.2 that we have been for the last
> several releases: nowhere.

OK, then we need to say that very clearly and up front (including in the
EXPLAIN docs.)

Of course, for data going to the client, if the client encoding is UTF8,
they should get legal JSON, regardless of what the database encoding is,
and conversely too, no?

cheers

andrew
>

On Thu, Jan 19, 2012 at 5:59 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> OK, then we need to say that very clearly and up front (including in the
> EXPLAIN docs.)

Can do.

> Of course, for data going to the client, if the client encoding is UTF8,
> they should get legal JSON, regardless of what the database encoding is, and
> conversely too, no?

Well, that would be nice, but I don't think it's practical. It will
certainly be the case, under the scheme I'm proposing, or probably any
other sensible scheme also, that if a client whose encoding is UTF-8
gets a value of type json back fro the database, it's strictly valid
JSON. But it won't be possible to store every legal JSON value in the
database if the database encoding is anything other than UTF-8, even
if the client encoding is UTF-8. The backend will get the client's
UTF-8 bytes and transcode them to the server encoding before calling
the type-input function, so if there are characters in there that
can't be represented in UTF-8, then we'll error out before the JSON
data type ever gets control. In theory, it would be possible to
accept such strings if the client chooses to represent them using a
\uXXXX sequence, but I'm unexcited about doing the work required to
make that happen, because it will still be a pretty half-baked: we'll
be able to accept some representations of the same JSON constant but
not others.

I think the real fix for this problem is to introduce an
infrastructure inside the database that allows us to have different
columns stored in different encodings. People use bytea for that
right now, but that's pretty unfriendly: it would be nice to have a
better system. However, I expect that to take a lot of work and break
a lot of things, and until we do it I don't feel that compelled to
provide buggy and incomplete support for it under the guise of
implementing a JSON datatype.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Jan 19, 2012, at 4:27 PM, Robert Haas wrote:

> I think the real fix for this problem is to introduce an
> infrastructure inside the database that allows us to have different
> columns stored in different encodings. People use bytea for that
> right now, but that's pretty unfriendly: it would be nice to have a
> better system. However, I expect that to take a lot of work and break
> a lot of things, and until we do it I don't feel that compelled to
> provide buggy and incomplete support for it under the guise of
> implementing a JSON datatype.

+1 This seems like a reasonable compromise and course of action, especially if someone is interested in taking on column-level encodings at some point in the next year or two.

Best,

David

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 01/19/2012 04:12 PM, Robert Haas wrote:
>> On Thu, Jan 19, 2012 at 4:07 PM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>>> The spec only allows unescaped Unicode chars (and for our purposes that
>>> means UTF8). An unescaped non-ASCII character in, say, ISO-8859-1 will
>>> result in something that's not legal JSON.

>> I understand. I'm proposing that we not care. In other words, if the
>> server encoding is UTF-8, it'll really be JSON. But if the server
>> encoding is something else, it'll be almost-JSON.

> Of course, for data going to the client, if the client encoding is UTF8,
> they should get legal JSON, regardless of what the database encoding is,
> and conversely too, no?

Yes. I think this argument has been mostly theologizing, along the
lines of how many JSON characters can dance on the head of a pin.
>From a user's perspective, the database encoding is only a constraint on
which characters he can store. He does not know or care what the bit
representation is inside the server. As such, if we store a non-ASCII
character in a JSON string, it's valid JSON as far as the user is
concerned, so long as that character exists in the Unicode standard.
If his client encoding is UTF8, the value will be letter-perfect JSON
when it gets to him; and if his client encoding is not UTF8, then he's
already pretty much decided that he doesn't give a fig about the
Unicode-centricity of the JSON spec, no?

So I'm with Robert: we should just plain not care. I would further
suggest that maybe what we should do with incoming JSON escape sequences
is convert them to Unicode code points and then to the equivalent
character in the database encoding (or throw error if there is none).

regards, tom lane

On Fri, Jan 20, 2012 at 12:07 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> On 01/19/2012 04:12 PM, Robert Haas wrote:
>>> On Thu, Jan 19, 2012 at 4:07 PM, Andrew Dunstan<andrew(at)dunslane(dot)net>  wrote:
>>>> The spec only allows unescaped Unicode chars (and for our purposes that
>>>> means UTF8). An unescaped non-ASCII character in, say, ISO-8859-1 will
>>>> result in something that's not legal JSON.
>
>>> I understand.  I'm proposing that we not care.  In other words, if the
>>> server encoding is UTF-8, it'll really be JSON.  But if the server
>>> encoding is something else, it'll be almost-JSON.
>
>> Of course, for data going to the client, if the client encoding is UTF8,
>> they should get legal JSON, regardless of what the database encoding is,
>> and conversely too, no?
>
> Yes.  I think this argument has been mostly theologizing, along the
> lines of how many JSON characters can dance on the head of a pin.
> From a user's perspective, the database encoding is only a constraint on
> which characters he can store.

Bingo.

> He does not know or care what the bit
> representation is inside the server.  As such, if we store a non-ASCII
> character in a JSON string, it's valid JSON as far as the user is
> concerned, so long as that character exists in the Unicode standard.
> If his client encoding is UTF8, the value will be letter-perfect JSON
> when it gets to him; and if his client encoding is not UTF8, then he's
> already pretty much decided that he doesn't give a fig about the
> Unicode-centricity of the JSON spec, no?

Also agreed. Personally, I think it may not have been a great idea to
tie the JSON spec so closely to Unicode, but I understand that it
would have been difficult to define an encoding-agnostic equivalent of
\uXXXX, since it's hard to know for sure whether an arbitrary encoding
even has a (sensible?) definition of code points, and they probably
wanted to avoid ambiguity. But, it's bound to cause problems for any
system that runs in some other encoding, which, when so requested, we
do. Even if we had the ability to support multiple encodings in the
same database, I'm not sure I'd be very excited about insisting that
JSON data always be stored in UTF-8, because that would introduce a
lot of unnecessary transcoding for people using other encodings and
basically unnecessarily handicap the functionality provided by the
datatype. But at least if we had that, people would have the *option*
to use JSON with UTF-8 and get the fully spec-compliant behavior. As
it is, they don't; the system we have forces the database encoding on
all datatypes whether they like it or not, and that ain't changing for
9.2.

> So I'm with Robert: we should just plain not care.  I would further
> suggest that maybe what we should do with incoming JSON escape sequences
> is convert them to Unicode code points and then to the equivalent
> character in the database encoding (or throw error if there is none).

The code I've written so far does no canonicalization of the input
value of any kind, just as we do for XML. I'm inclined to leave it
that way. Eventually, we might want to make the JSON datatype support
equality comparisons and so on, and that will require the system to
knowing that the letter r can be encoded as some \uXXXX sequence and
that the escape \r is equivalent to some other escape \uXXXX, but
right now all the code does is try to validate that the JSON is legal,
NOT second-guess the user's choice about how to spell things or where
to insert whitespace. I think that's a good choice because (1) AFAIK,
there's no official canonicalization method for JSON, so whatever we
pick will be something we think is best, not an official method
sanction by the spec, (2) users might prefer the way they chose to
represent a given value over the way we choose to represent it, and
(3) by simply validating and storing the JSON object, rather than
doing any canonicalization, the input function avoids the need to do
any data copying, hopefully maximizing speed. Canonicalization can be
added on top of what I've done here and people who want or need it can
use it; I have some ideas around how to make that leverage the
existing code that I intend to pursue for 9.3, but right now I'd
rather not go there.

So, given that framework, what the patch does is this: if you're using
UTF-8, then \uXXXX is accepted, provided that XXXX is something that
equates to a legal Unicode code point. It isn't converted to the
corresponding character: it's just validated. If you're NOT using
UTF-8, then it allows \uXXXX for code points up through 127 (which we
assume are the same in all encodings) and anything higher than that is
rejected. If someone knows an easy way to check whether a \uXXXX
sequence for XXXX > 007F is a legal Unicode code point that has an
equivalent in the current server encoding, then we can add logic to
allow that case also, but personally I'm not that excited about it.
Anyone who is using \uXXXX escapes with a non-Unicode coding is
probably hoping that we can store arbitrary code points, not just the
ones that happen to exist in the server encoding, so they're probably
going to be disappointed whether or not we spend time eating away at
the edges of the problem.

It's awfully interesting to think about how we could actually make
cross-encoding stuff work for real. Would we make it a separate
property that can be associated with each column, like we did for
collations? Or would we handle it more like range types - give me an
encoding, and I'll give you a new type OID that represents text stored
in that encoding? But I guess that's a question for another day.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> The code I've written so far does no canonicalization of the input
> value of any kind, just as we do for XML.

Fair enough.

> So, given that framework, what the patch does is this: if you're using
> UTF-8, then \uXXXX is accepted, provided that XXXX is something that
> equates to a legal Unicode code point. It isn't converted to the
> corresponding character: it's just validated. If you're NOT using
> UTF-8, then it allows \uXXXX for code points up through 127 (which we
> assume are the same in all encodings) and anything higher than that is
> rejected.

This seems a bit silly. If you're going to leave the escape sequence as
ASCII, then why not just validate that it names a legal Unicode code
point and be done? There is no reason whatever that that behavior needs
to depend on the database encoding.

regards, tom lane

On Fri, Jan 20, 2012 at 10:27 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> The code I've written so far does no canonicalization of the input
>> value of any kind, just as we do for XML.
>
> Fair enough.
>
>> So, given that framework, what the patch does is this: if you're using
>> UTF-8, then \uXXXX is accepted, provided that XXXX is something that
>> equates to a legal Unicode code point.  It isn't converted to the
>> corresponding character: it's just validated.  If you're NOT using
>> UTF-8, then it allows \uXXXX for code points up through 127 (which we
>> assume are the same in all encodings) and anything higher than that is
>> rejected.
>
> This seems a bit silly.  If you're going to leave the escape sequence as
> ASCII, then why not just validate that it names a legal Unicode code
> point and be done?  There is no reason whatever that that behavior needs
> to depend on the database encoding.

Mostly because that would prevent us from adding canonicalization in
the future, AFAICS, and I don't want to back myself into a corner.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 01/20/2012 09:19 AM, Robert Haas wrote:
> On Fri, Jan 20, 2012 at 12:07 AM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Andrew Dunstan<andrew(at)dunslane(dot)net> writes:
>>> On 01/19/2012 04:12 PM, Robert Haas wrote:
>>>> On Thu, Jan 19, 2012 at 4:07 PM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>>>>> The spec only allows unescaped Unicode chars (and for our purposes that
>>>>> means UTF8). An unescaped non-ASCII character in, say, ISO-8859-1 will
>>>>> result in something that's not legal JSON.
>>>> I understand. I'm proposing that we not care. In other words, if the
>>>> server encoding is UTF-8, it'll really be JSON. But if the server
>>>> encoding is something else, it'll be almost-JSON.
>>> Of course, for data going to the client, if the client encoding is UTF8,
>>> they should get legal JSON, regardless of what the database encoding is,
>>> and conversely too, no?
>> Yes. I think this argument has been mostly theologizing, along the
>> lines of how many JSON characters can dance on the head of a pin.
>> From a user's perspective, the database encoding is only a constraint on
>> which characters he can store.
> Bingo.
>
>> He does not know or care what the bit
>> representation is inside the server. As such, if we store a non-ASCII
>> character in a JSON string, it's valid JSON as far as the user is
>> concerned, so long as that character exists in the Unicode standard.
>> If his client encoding is UTF8, the value will be letter-perfect JSON
>> when it gets to him; and if his client encoding is not UTF8, then he's
>> already pretty much decided that he doesn't give a fig about the
>> Unicode-centricity of the JSON spec, no?
> Also agreed. Personally, I think it may not have been a great idea to
> tie the JSON spec so closely to Unicode, but I understand that it
> would have been difficult to define an encoding-agnostic equivalent of
> \uXXXX, since it's hard to know for sure whether an arbitrary encoding
> even has a (sensible?) definition of code points, and they probably
> wanted to avoid ambiguity. But, it's bound to cause problems for any
> system that runs in some other encoding, which, when so requested, we
> do. Even if we had the ability to support multiple encodings in the
> same database, I'm not sure I'd be very excited about insisting that
> JSON data always be stored in UTF-8, because that would introduce a
> lot of unnecessary transcoding for people using other encodings and
> basically unnecessarily handicap the functionality provided by the
> datatype. But at least if we had that, people would have the *option*
> to use JSON with UTF-8 and get the fully spec-compliant behavior. As
> it is, they don't; the system we have forces the database encoding on
> all datatypes whether they like it or not, and that ain't changing for
> 9.2.
>
>> So I'm with Robert: we should just plain not care. I would further
>> suggest that maybe what we should do with incoming JSON escape sequences
>> is convert them to Unicode code points and then to the equivalent
>> character in the database encoding (or throw error if there is none).
> The code I've written so far does no canonicalization of the input
> value of any kind, just as we do for XML. I'm inclined to leave it
> that way. Eventually, we might want to make the JSON datatype support
> equality comparisons and so on, and that will require the system to
> knowing that the letter r can be encoded as some \uXXXX sequence and
> that the escape \r is equivalent to some other escape \uXXXX, but
> right now all the code does is try to validate that the JSON is legal,
> NOT second-guess the user's choice about how to spell things or where
> to insert whitespace. I think that's a good choice because (1) AFAIK,
> there's no official canonicalization method for JSON, so whatever we
> pick will be something we think is best, not an official method
> sanction by the spec, (2) users might prefer the way they chose to
> represent a given value over the way we choose to represent it, and
> (3) by simply validating and storing the JSON object, rather than
> doing any canonicalization, the input function avoids the need to do
> any data copying, hopefully maximizing speed. Canonicalization can be
> added on top of what I've done here and people who want or need it can
> use it; I have some ideas around how to make that leverage the
> existing code that I intend to pursue for 9.3, but right now I'd
> rather not go there.
>
> So, given that framework, what the patch does is this: if you're using
> UTF-8, then \uXXXX is accepted, provided that XXXX is something that
> equates to a legal Unicode code point. It isn't converted to the
> corresponding character: it's just validated. If you're NOT using
> UTF-8, then it allows \uXXXX for code points up through 127 (which we
> assume are the same in all encodings) and anything higher than that is
> rejected. If someone knows an easy way to check whether a \uXXXX
> sequence for XXXX> 007F is a legal Unicode code point that has an
> equivalent in the current server encoding, then we can add logic to
> allow that case also, but personally I'm not that excited about it.
> Anyone who is using \uXXXX escapes with a non-Unicode coding is
> probably hoping that we can store arbitrary code points, not just the
> ones that happen to exist in the server encoding, so they're probably
> going to be disappointed whether or not we spend time eating away at
> the edges of the problem.
>
> It's awfully interesting to think about how we could actually make
> cross-encoding stuff work for real. Would we make it a separate
> property that can be associated with each column, like we did for
> collations? Or would we handle it more like range types - give me an
> encoding, and I'll give you a new type OID that represents text stored
> in that encoding? But I guess that's a question for another day.

(thinks out loud)

XML's &#nnnn; escape mechanism is more or less the equivalent of JSON's
\unnnn. But XML documents can be encoded in a variety of encodings,
including non-unicode encodings such as Latin-1. However, no matter what
the document encoding, &#nnnn; designates the character with Unicode
code point nnnn, whether or not that is part of the document encoding's
charset.

Given that precedent, I'm wondering if we do need to enforce anything
other than that it is a valid unicode code point.

Equivalence comparison is going to be difficult anyway if you're not
resolving all \unnnn escapes. Possibly we need some sort of
canonicalization function to apply for comparison purposes. But we're
not providing any comparison ops today anyway, so I don't think we need
to make that decision now. As you say, there doesn't seem to be any
defined canonical form - the spec is a bit light on in this respect.

cheers

andrew

On Fri, Jan 20, 2012 at 10:45 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> XML's &#nnnn; escape mechanism is more or less the equivalent of JSON's
> \unnnn. But XML documents can be encoded in a variety of encodings,
> including non-unicode encodings such as Latin-1. However, no matter what the
> document encoding, &#nnnn; designates the character with Unicode code point
> nnnn, whether or not that is part of the document encoding's charset.

OK.

> Given that precedent, I'm wondering if we do need to enforce anything other
> than that it is a valid unicode code point.
>
> Equivalence comparison is going to be difficult anyway if you're not
> resolving all \unnnn escapes. Possibly we need some sort of canonicalization
> function to apply for comparison purposes. But we're not providing any
> comparison ops today anyway, so I don't think we need to make that decision
> now. As you say, there doesn't seem to be any defined canonical form - the
> spec is a bit light on in this respect.

Well, we clearly have to resolve all \uXXXX to do either comparison or
canonicalization. The current patch does neither, but presumably we
want to leave the door open to such things. If we're using UTF-8 and
comparing two strings, and we get to a position where one of them has
a character and the other has \uXXXX, it's pretty simple to do the
comparison: we just turn XXXX into a wchar_t and test for equality.
That should be trivial, unless I'm misunderstanding. If, however,
we're not using UTF-8, we have to first turn \uXXXX into a Unicode
code point, then covert that to a character in the database encoding,
and then test for equality with the other character after that. I'm
not sure whether that's possible in general, how to do it, or how
efficient it is. Can you or anyone shed any light on that topic?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Jan 19, 2012, at 9:07 PM, Tom Lane wrote:

> If his client encoding is UTF8, the value will be letter-perfect JSON
> when it gets to him; and if his client encoding is not UTF8, then he's
> already pretty much decided that he doesn't give a fig about the
> Unicode-centricity of the JSON spec, no?

Don’t entirely agree with this. Some folks are stuck with other encodings and cannot change them for one reason or another. That said, they can convert JSON from their required encoding into UTF-8 on the client side, so there is a workaround.

Not that this changes anything, and I agree with the overall direction of the discussion here. I just want to make sure we keep in mind folks who don’t necessarily have the freedom to switch to UTF-8. (And I say this as someone who *always* uses UTF-8!)

David

On Jan 20, 2012, at 8:58 AM, Robert Haas wrote:

> If, however,
> we're not using UTF-8, we have to first turn \uXXXX into a Unicode
> code point, then covert that to a character in the database encoding,
> and then test for equality with the other character after that. I'm
> not sure whether that's possible in general, how to do it, or how
> efficient it is. Can you or anyone shed any light on that topic?

If it’s like the XML example, it should always represent a Unicode code point, and *not* be converted to the other character set, no?

At any rate, since the JSON standard requires UTF-8, such distinctions having to do with alternate encodings are not likely to be covered, so I suspect we can do whatever we want here. It’s outside the spec.

Best,

David

On Fri, Jan 20, 2012 at 09:12:13AM -0800, David E. Wheeler wrote:
> On Jan 19, 2012, at 9:07 PM, Tom Lane wrote:
>
> > If his client encoding is UTF8, the value will be letter-perfect JSON
> > when it gets to him; and if his client encoding is not UTF8, then he's
> > already pretty much decided that he doesn't give a fig about the
> > Unicode-centricity of the JSON spec, no?
>
> Don’t entirely agree with this. Some folks are stuck with other encodings and
> cannot change them for one reason or another. That said, they can convert
> JSON from their required encoding into UTF-8 on the client side, so there is
> a workaround.

Perhaps in addition to trying to just 'do the right thing by default',
it makes sense to have a two canonicalization functions?

Say: json_utf8() and json_ascii().

They could give the same output no matter what encoding was set?

json_utf8 would give nice output where characters were canonicalized to
native utf8 characters and json_ascii() would output only non-control
ascii characters literally and escape everything else or something
like that?

Garick

On 01/20/2012 11:58 AM, Robert Haas wrote:
> On Fri, Jan 20, 2012 at 10:45 AM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>> XML's&#nnnn; escape mechanism is more or less the equivalent of JSON's
>> \unnnn. But XML documents can be encoded in a variety of encodings,
>> including non-unicode encodings such as Latin-1. However, no matter what the
>> document encoding,&#nnnn; designates the character with Unicode code point
>> nnnn, whether or not that is part of the document encoding's charset.
> OK.
>
>> Given that precedent, I'm wondering if we do need to enforce anything other
>> than that it is a valid unicode code point.
>>
>> Equivalence comparison is going to be difficult anyway if you're not
>> resolving all \unnnn escapes. Possibly we need some sort of canonicalization
>> function to apply for comparison purposes. But we're not providing any
>> comparison ops today anyway, so I don't think we need to make that decision
>> now. As you say, there doesn't seem to be any defined canonical form - the
>> spec is a bit light on in this respect.
> Well, we clearly have to resolve all \uXXXX to do either comparison or
> canonicalization. The current patch does neither, but presumably we
> want to leave the door open to such things. If we're using UTF-8 and
> comparing two strings, and we get to a position where one of them has
> a character and the other has \uXXXX, it's pretty simple to do the
> comparison: we just turn XXXX into a wchar_t and test for equality.
> That should be trivial, unless I'm misunderstanding. If, however,
> we're not using UTF-8, we have to first turn \uXXXX into a Unicode
> code point, then covert that to a character in the database encoding,
> and then test for equality with the other character after that. I'm
> not sure whether that's possible in general, how to do it, or how
> efficient it is. Can you or anyone shed any light on that topic?

We know perfectly well how to turn two strings from encoding x to utf8
(see mb_utils.c::pg_do_encoding_conversion() ). Once we've done that
ISTM we have reduced this to the previous problem, as the mathematicians
like to say.

cheers

andrew

On Fri, Jan 20, 2012 at 12:14 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> On Jan 20, 2012, at 8:58 AM, Robert Haas wrote:
>
>> If, however,
>> we're not using UTF-8, we have to first turn \uXXXX into a Unicode
>> code point, then covert that to a character in the database encoding,
>> and then test for equality with the other character after that.  I'm
>> not sure whether that's possible in general, how to do it, or how
>> efficient it is.  Can you or anyone shed any light on that topic?
>
> If it’s like the XML example, it should always represent a Unicode code point, and *not* be converted to the other character set, no?

Well, you can pick which way you want to do the conversion. If the
database encoding is SJIS, and there's an SJIS character in a string
that gets passed to json_in(), and there's another string which also
gets passed to json_in() which contains \uXXXX, then any sort of
canonicalization or equality testing is going to need to convert the
SJIS character to a Unicode code point, or the Unicode code point to
an SJIS character, to see whether they match.

Err, actually, now that I think about it, that might be a problem:
what happens if we're trying to test two characters for equality and
the encoding conversion fails? We really just want to return false -
the strings are clearly not equal if either contains even one
character that can't be converted to the other encoding - so it's not
good if an error gets thrown in there anywhere.

> At any rate, since the JSON standard requires UTF-8, such distinctions having to do with alternate encodings are not likely to be covered, so I suspect we can do whatever we want here. It’s outside the spec.

I agree.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company