Обсуждение: "JSON does not support infinite date values"
Hi,
As far as I'm aware, JSON has no data types as such, and so why is
Postgres (9.4.1) attempting to impose its own nonsense constraints ?
Surely it should just insert the word 'infinity' into the JSON output,
just like it displays in a simple SQL query ?
create table app_test.foo(a text,b date,c date,d boolean);
create view app_test.bar as select * from app_test.foo where
b<=now()::date and c>=now()::date and d=true;
insert into app_test.foo(a,b,c,d) values ('zzz','2014-12-31','2014-02-01',true);
insert into app_test.foo(a,b,c,d) values ('zzz','2015-02-01','infinity',true);
foobar=> select * from app_test.bar where a='zzz' order by c asc limit 1;
a | b | c | d
-----+------------+----------+---
zzz | 2015-02-01 | infinity | t
(1 row)
CREATE FUNCTION app_test.foobar(p_a text) RETURNS json AS $$
DECLARE
v_row app_test.bar%ROWTYPE;
v_json json;
BEGIN
select * into strict v_row from app_test.bar where a=p_a order by c asc limit 1;
select row_to_json(v_row) into v_json;
return v_json;
END;
$$ LANGUAGE plpgsql;
foobar=> select app_test.foobar('zzz');
ERROR: date out of range
DETAIL: JSON does not support infinite date values.
CONTEXT: SQL statement "select row_to_json(v_row)"
PL/pgSQL function app_test.foobar(text) line 7 at SQL statement
Hi, On 2015-02-26 11:55:20 +0000, Tim Smith wrote: > As far as I'm aware, JSON has no data types as such, and so why is > Postgres (9.4.1) attempting to impose its own nonsense constraints ? "impose its own nonsense constraints" - breathe slowly in, and out, in, and out. It looks to me like ab14a73a6ca5cc4750f0e00a48bdc25a2293034a copied too much code from xml.c - including a comment about XSD... Andrew, was that intentional? Greetings, Andres Freund
On 02/26/2015 03:55 AM, Tim Smith wrote:
> Hi,
>
> As far as I'm aware, JSON has no data types as such, and so why is
> Postgres (9.4.1) attempting to impose its own nonsense constraints ?
> Surely it should just insert the word 'infinity' into the JSON output,
> just like it displays in a simple SQL query ?
It did:
test=> select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.6 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit
(1 row)
test=> select to_json(row('infinity'::date));
to_json
-------------------
{"f1":"infinity"}
(1 row)
seems to have changed:
test=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.1 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit
(1 row)
test=# select to_json(row('infinity'::date));
ERROR: date out of range
DETAIL: JSON does not support infinite date values.
so will have to wait until the question Andres posed is answered.
>
> create table app_test.foo(a text,b date,c date,d boolean);
>
> create view app_test.bar as select * from app_test.foo where
> b<=now()::date and c>=now()::date and d=true;
>
> insert into app_test.foo(a,b,c,d) values ('zzz','2014-12-31','2014-02-01',true);
> insert into app_test.foo(a,b,c,d) values ('zzz','2015-02-01','infinity',true);
>
>
> foobar=> select * from app_test.bar where a='zzz' order by c asc limit 1;
> a | b | c | d
> -----+------------+----------+---
> zzz | 2015-02-01 | infinity | t
> (1 row)
>
>
> CREATE FUNCTION app_test.foobar(p_a text) RETURNS json AS $$
> DECLARE
> v_row app_test.bar%ROWTYPE;
> v_json json;
> BEGIN
> select * into strict v_row from app_test.bar where a=p_a order by c asc limit 1;
> select row_to_json(v_row) into v_json;
> return v_json;
> END;
> $$ LANGUAGE plpgsql;
>
>
> foobar=> select app_test.foobar('zzz');
> ERROR: date out of range
> DETAIL: JSON does not support infinite date values.
> CONTEXT: SQL statement "select row_to_json(v_row)"
> PL/pgSQL function app_test.foobar(text) line 7 at SQL statement
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 02/26/2015 07:02 AM, Andres Freund wrote: > Hi, > > On 2015-02-26 11:55:20 +0000, Tim Smith wrote: >> As far as I'm aware, JSON has no data types as such, and so why is >> Postgres (9.4.1) attempting to impose its own nonsense constraints ? > "impose its own nonsense constraints" - breathe slowly in, and out, in, > and out. > > It looks to me like ab14a73a6ca5cc4750f0e00a48bdc25a2293034a copied too > much code from xml.c - including a comment about XSD... Andrew, was that > intentional? > Possibly too much was copied, I don't recall a reason offhand for excluding infinity. I'm not opposed to changing it (jsonb will have the same issue). We do allow infinity (and NaN etc) when converting numerics to json, so perhaps doing it for dates and timestamps too would be more consistent. cheers andrew
Andres Freund <andres@2ndquadrant.com> writes:
> On 2015-02-26 11:55:20 +0000, Tim Smith wrote:
>> As far as I'm aware, JSON has no data types as such, and so why is
>> Postgres (9.4.1) attempting to impose its own nonsense constraints ?
> "impose its own nonsense constraints" - breathe slowly in, and out, in,
> and out.
> It looks to me like ab14a73a6ca5cc4750f0e00a48bdc25a2293034a copied too
> much code from xml.c - including a comment about XSD... Andrew, was that
> intentional?
Not wanting to put words in Andrew's mouth, but I thought the point of
those changes was that timestamps emitted into JSON should be formatted
per some ISO standard or other, and said standard (almost certainly)
doesn't know what infinity is.
At the same time, there is definitely no such requirement in the JSON spec
itself, so at least the error message is quoting the wrong authority.
regards, tom lane
On 02/26/2015 10:16 AM, Tom Lane wrote: > Andres Freund <andres@2ndquadrant.com> writes: >> On 2015-02-26 11:55:20 +0000, Tim Smith wrote: >>> As far as I'm aware, JSON has no data types as such, and so why is >>> Postgres (9.4.1) attempting to impose its own nonsense constraints ? >> "impose its own nonsense constraints" - breathe slowly in, and out, in, >> and out. >> It looks to me like ab14a73a6ca5cc4750f0e00a48bdc25a2293034a copied too >> much code from xml.c - including a comment about XSD... Andrew, was that >> intentional? > Not wanting to put words in Andrew's mouth, but I thought the point of > those changes was that timestamps emitted into JSON should be formatted > per some ISO standard or other, and said standard (almost certainly) > doesn't know what infinity is. > > At the same time, there is definitely no such requirement in the JSON spec > itself, so at least the error message is quoting the wrong authority. > > Well, we could say that we'll use ISO 8601 format for finite dates and times, and '"infinity"' otherwise. Then if you want to be able to interpret them as ISO 8601 format it will be up to you to ensure that there are no infinite values being converted. cheers andrew
On 2015-02-26 10:16:38 -0500, Tom Lane wrote: > Andres Freund <andres@2ndquadrant.com> writes: > > On 2015-02-26 11:55:20 +0000, Tim Smith wrote: > >> As far as I'm aware, JSON has no data types as such, and so why is > >> Postgres (9.4.1) attempting to impose its own nonsense constraints ? > > > "impose its own nonsense constraints" - breathe slowly in, and out, in, > > and out. > > > It looks to me like ab14a73a6ca5cc4750f0e00a48bdc25a2293034a copied too > > much code from xml.c - including a comment about XSD... Andrew, was that > > intentional? > > Not wanting to put words in Andrew's mouth, but I thought the point of > those changes was that timestamps emitted into JSON should be formatted > per some ISO standard or other, and said standard (almost certainly) > doesn't know what infinity is. To me it looked more about being able to reuse code: "The output is slightly different, but still ISO 8601 compliant". The previous commit, still using to_char, didn't prohibit 'infinity' that way; although I doubt it produced something useful. The copied XSD reference (which is about a XML schema description language) doesn't seem to imply that it really was intended. > At the same time, there is definitely no such requirement in the JSON > spec itself, so at least the error message is quoting the wrong > authority. To me there seems to be very little point in restricing the output that way. Sure, it can easily be worked around by casting before the conversion to json, but it's still somewhat odd to put in restrictions that don't exist in normal json. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Andres Freund <andres@2ndquadrant.com> writes:
> On 2015-02-26 10:16:38 -0500, Tom Lane wrote:
>> At the same time, there is definitely no such requirement in the JSON
>> spec itself, so at least the error message is quoting the wrong
>> authority.
> To me there seems to be very little point in restricing the output that
> way. Sure, it can easily be worked around by casting before the
> conversion to json, but it's still somewhat odd to put in restrictions
> that don't exist in normal json.
Yeah, I think so. The sequence 'infinity'::timestamp to JSON to
ISO-8601-only consumer is going to fail no matter what; there is no
need for us to force a failure at the first step. Especially when
doing so excludes other, perfectly useful use-cases.
So +1 for removing the error and emitting "infinity" suitably quoted.
Andrew, will you do that?
regards, tom lane
On 02/26/2015 10:38 AM, Tom Lane wrote: > > Yeah, I think so. The sequence 'infinity'::timestamp to JSON to > ISO-8601-only consumer is going to fail no matter what; there is no > need for us to force a failure at the first step. Especially when > doing so excludes other, perfectly useful use-cases. > > So +1 for removing the error and emitting "infinity" suitably quoted. > Andrew, will you do that? > > Yeah. cheers andrew
> So +1 for removing the error and emitting "infinity" suitably quoted. > Andrew, will you do that? > +1 here too. Otherwise there's very little point having the "infinity" feature in Postgres if only some of the database functions actually support it without throwing a tantrum. If its a database feature, it should be a database feature throughout.
FYI.... although I remain a +1 on KISS and emitting "infinity", for those of you still yearning after a standards-based implementation, there is a StackOverflow post which hints at sections 3.5 and 3.7 of ISO8601:2004. Unfortunatley I can't find a link to an ISO8601:2004 text, so you'll have to make do with the SO quoted extracts instead.... http://stackoverflow.com/questions/11408249/how-do-you-represent-forever-infinitely-in-the-future-in-iso8601
On 2015-02-26 15:54:37 +0000, Tim Smith wrote: > Otherwise there's very little point having the "infinity" feature in > Postgres if only some of the database functions actually support it > without throwing a tantrum. Seriously? Json not supporting infinity makes it useless. Ok, so it has been useless for the, I don't know, last 10 years? Such hysterical blabla makes it hard to take your point serious. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 02/26/2015 07:54 AM, Tim Smith wrote: >> So +1 for removing the error and emitting "infinity" suitably quoted. >> Andrew, will you do that? >> > > +1 here too. Otherwise there's very little point having the > "infinity" feature in Postgres if only some of the database functions > actually support it without throwing a tantrum. If its a database > feature, it should be a database feature throughout. Personally, I think the worse that can be said is that it is a case of premature optimization. Postgres making you deal with infinity and JSON/Javascript now instead of later. > > -- Adrian Klaver adrian.klaver@aklaver.com
On 02/26/2015 11:03 AM, Tim Smith wrote: > FYI.... although I remain a +1 on KISS and emitting "infinity", for > those of you still yearning after a standards-based implementation, > there is a StackOverflow post which hints at sections 3.5 and 3.7 of > ISO8601:2004. > > Unfortunatley I can't find a link to an ISO8601:2004 text, so you'll > have to make do with the SO quoted extracts instead.... > http://stackoverflow.com/questions/11408249/how-do-you-represent-forever-infinitely-in-the-future-in-iso8601 > If you want to do that then store that in your date/timestamp data and we'll output it. But we're not going to silently convert infinity to anything else: andrew=# select to_json('99999-12-31'::timestamptz); to_json ------------------------------ "99999-12-31T00:00:00-05:00" cheers andrew
> Seriously? Json not supporting infinity makes it useless. Ok, so it has > been useless for the, I don't know, last 10 years? Just face it Andres, it should have never been coded that way in the first place. The fact that it appears that nobody in the last 10 years has used "infinity" in conjunction with JSON functions is your problem, not mine.
> If you want to do that then store that in your date/timestamp data and we'll > output it. But we're not going to silently convert infinity to anything > else: Just for the record, I never said I wanted to do it. I was saying it for the benefit of those people who replied to this thread talking about ISO standards and who suggested that was the reason things were coded as they were.
On 02/26/2015 08:27 AM, Tim Smith wrote: >> Seriously? Json not supporting infinity makes it useless. Ok, so it has >> been useless for the, I don't know, last 10 years? > > Just face it Andres, it should have never been coded that way in the > first place. The fact that it appears that nobody in the last 10 > years has used "infinity" in conjunction with JSON functions is your > problem, not mine. You really need to pay attention to the conversation instead of getting wrapped up in your own drama. As was stated and shown, previously(9.3+) Postgres did what you want. The behavior change you discovered was done as part of the changes in 9.4 to be ISO 8601 compliant: http://www.postgresql.org/docs/9.4/interactive/release-9-4.html When converting values of type date, timestamp or timestamptz to JSON, render the values in a format compliant with ISO 8601 (Andrew Dunstan) Previously such values were rendered according to the current DateStyle setting; but many JSON processors require timestamps to be in ISO 8601 format. If necessary, the previous behavior can be obtained by explicitly casting the datetime value to text before passing it to the JSON conversion function Reading the above gets you: test=# select version(); version ----------------------------------------------------------------------------------------------------------------------------- PostgreSQL 9.4.1 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux) 4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit (1 row) test=# select to_json(row('infinity'::date::text)); to_json ------------------- {"f1":"infinity"} (1 row) Now both a core developer and the developer driving the JSON bus agree with you that the above is probably not the best solution and the behavior should return to its previous state. So please, take yes for an answer and move on. > > -- Adrian Klaver adrian.klaver@aklaver.com
On 02/26/2015 08:27 AM, Tim Smith wrote: >> Seriously? Json not supporting infinity makes it useless. Ok, so it has >> been useless for the, I don't know, last 10 years? > > Just face it Andres, it should have never been coded that way in the > first place. The fact that it appears that nobody in the last 10 > years has used "infinity" in conjunction with JSON functions is your > problem, not mine. > > Aah, in previous post should have been; "As was stated and shown, previously(9.3-) Postgres did what you want." -- Adrian Klaver adrian.klaver@aklaver.com