Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Tom Lane wrote: Ron Mayer [EMAIL PROTECTED] writes: Brendan Jurd wrote: ...I did notice one final ... Just checked in a fix to that one; and updated my website at http://0ape.com/postgres_interval_patches/ and pushed it to my (hopefully fixed now) git server. Applied with some revisions: I changed the rule for negating input fields in SQL_STANDARD style as we discussed, made IntervalStyle into an enum GUC, and did some pretty heavy editorialization on the docs changes. Cool. Thanks! Brendan and anyone else looking at these, I've done an initial merge between the new CVS head and my other interval patches and pushed them to my website link mentioned above; but I probably still need to cleanup some of the docs merges. I'm a bit tied up today; so will probably cleanup the merge and study Tom's changes and post an update when the other patches are ready to continue reviewing, probably late tomorrow. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Tom Lane wrote:
Ron Mayer [EMAIL PROTECTED] writes:
Rather than forcing Postgres mode; couldn't it put a
set intervalstyle = [whatever the current interval style is]
in the dump file?
This would work for loading into a PG = 8.4 server, and fail miserably
for loading into pre-8.4 servers. Even though we don't guarantee
backwards compatibility of dump files, I'm loath to adopt a solution
that will successfully load wrong data into an older server.
How is the case different from standard_conforming_strings; where ISTM
depending on postgresql.conf 8.4 will happily dump either
SET standard_conforming_strings = off;
...
INSERT INTO dumptest VALUES ('');
or
SET standard_conforming_strings = on;
...
INSERT INTO dumptest VALUES ('\\');
and AFAICT the latter will happily load wrong data into 8.1 with
only the error message
ERROR: parameter standard_conforming_strings cannot be changed
I imagine the use-case for standard_conforming_strings = 0 and
intervalstyle = sql_standard are petty similar as well.
I wonder if the best solution is that any dump file with
standard_conforming_strings=on include some SQL that will refuse
to load in pre-8.2 systems; and that any dump file with
intervalstyle=sql_standard refuse to load in pre-8.4 systems.
It seems pretty easy to add a sql fragment that checks version()
and put that in the beginning of a dump file that uses these GUCs
to enforce this.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ron Mayer [EMAIL PROTECTED] writes: So the options seem to be: (1) Don't output a SQL-standard interval literal for the value negative one days and negative one hours; perhaps by sticking an extra '+' sign in there? This is pretty much what the postgres style does... (2) Force pg_dump to a non-standard mode, at least until 8.3's deprecated in many years? IOW, same as above. (3) Put something into the dump file that will make the old server reject the file rather than successfully loading wrong data? (Some if intervalstyle==std and version8.3 abort loading the restore logic?) There isn't any way to do that, unless you have a time machine in your hip pocket. The trouble with putting set intervalstyle = something; into the dump script is that older servers will (by default) report an error on that line and keep right on chugging. The same is true of standard_conforming_strings BTW, which is one of the reasons why that's not a very good solution. But at least you're reasonably likely to get additional errors later in the dump if you try to load it into a server that doesn't handle standard_conforming_strings. What's scaring me about the interval stuff is that it will *silently* adopt the wrong reading of ambiguous interval strings. A DBA who missed seeing that one bleat early in the restore would not know anything was wrong. You're right that we don't have to be frozen into this forever, but I fear that any change is going to be a long way off. We couldn't really change pg_dump's output style until we have obsoleted all pre-8.4 releases. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
BTW, I just noticed that CVS HEAD has a bug in reading negative SQL-spec literals: regression=# select interval '-2008-10'; interval -- -2008 years -10 mons (1 row) regression=# select interval '--10'; interval -- 10 mons (1 row) Surely the latter must mean -10 months. This is orthogonal to the current patch ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Tom Lane wrote: Ron Mayer [EMAIL PROTECTED] writes: (3) Put something into the dump file that will make the old server reject the file rather than successfully loading wrong data? (Some if intervalstyle==std and version8.3 abort loading the restore logic?) There isn't any way to do that, unless you have a time machine in your hip pocket. The trouble with putting set intervalstyle = something; into the dump script is that older servers will (by default) report an error on that line and keep right on chugging. Not necessarily. Couldn't we put select * from (select substring(version() from '[0-9\.]+') as version) as a join (select generate_series(0,1000)) as b on(version'8.4'); set intervalstyle = something; Or something similar in the dump file. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ron Mayer [EMAIL PROTECTED] writes: Tom Lane wrote: There isn't any way to do that, unless you have a time machine in your hip pocket. The trouble with putting set intervalstyle = something; into the dump script is that older servers will (by default) report an error on that line and keep right on chugging. Not necessarily. Couldn't we put select * from (select substring(version() from '[0-9\.]+') as version) as a join (select generate_series(0,1000)) as b on(version'8.4'); set intervalstyle = something; Or something similar in the dump file. [ shrug... ] It's still just one easily missable bleat. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Tom Lane wrote: Ron Mayer [EMAIL PROTECTED] writes: Tom Lane wrote: The trouble is that older servers will (by default) report an error on that line and keep right on chugging. Not necessarily. Couldn't we put select * from (select substring(version() from '[0-9\.]+') as version) as a join (select generate_series(0,1000)) as b on(version'8.4'); set intervalstyle = something; [ shrug... ] It's still just one easily missable bleat. Not here. On my system it hangs forever on 8.3 or less and proceeds harmlessly with 8.4. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ron Mayer [EMAIL PROTECTED] writes: select * from (select substring(version() from '[0-9\.]+') as version) as a join (select generate_series(0,1000)) as b on(version'8.4'); set intervalstyle = something; [ shrug... ] It's still just one easily missable bleat. Not here. On my system it hangs forever on 8.3 or less and proceeds harmlessly with 8.4. Oh, I see what you're trying to do. The answer is no. We're not going to totally destroy back-portability of dumps, especially not for a problem that won't even affect most people (negative intervals are hardly common). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Tom Lane wrote: Oh, I see what you're trying to do. The answer is no. We're not going to totally destroy back-portability of dumps, especially not for a problem that won't even affect most people (negative intervals are hardly common). Similarly I wonder if pg_dump should add a fail if version 8.2 right before it outputs SET standard_conforming_strings = on; which IMHO is far more common than negative intervals and AFAICT has the same risk. For intervals, we would only add the fail code if intervalstyle was set to one of the new interval styles (if the ISO8601 interval's accepted it'll have the problem too). For backward compatible patches, they could still have their GUC settingse specify standard_conforming_strings and interval_style values that are supported by whichever versions they want to support. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Tom Lane wrote: BTW, I just noticed that CVS HEAD has a bug in reading negative SQL-spec literals: regression=# select interval '-2008-10'; regression=# select interval '--10'; Surely the latter must mean -10 months. This is orthogonal to the current patch ... Perhaps the below patch fixes that? (though line numbers probably won't match since this was based off of the patched version) *** a/src/backend/utils/adt/datetime.c --- b/src/backend/utils/adt/datetime.c *** *** 2879,2885 DecodeInterval(char **field, int *ftype, int nf, int range, if (*cp != '\0') return DTERR_BAD_FORMAT; type = DTK_MONTH; ! if (val 0) val2 = -val2; val = val * MONTHS_PER_YEAR + val2; fval = 0; --- 2879,2885 if (*cp != '\0') return DTERR_BAD_FORMAT; type = DTK_MONTH; ! if (field[0][0] == '-') val2 = -val2; val = val * MONTHS_PER_YEAR + val2; fval = 0; [5]lt:/home/ramayer/proj/pg/postgresql/src/backend/utils/adt% -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Another thought here ... I'm looking at the sign hack
+ if (IntervalStyle == INTSTYLE_SQL_STANDARD
+ field[0][0] == '-' i == 1
+ field[i][0] != '-' field[i][0] != '+')
+ {
+ /*--
+ * The SQL Standard defines the interval literal
+ * '-1 1:00:00'
+ * to mean negative 1 days and negative one hours
+ * while Postgres traditionally treated this as
+ * meaning negative 1 days and positive one hours.
+ * In SQL_STANDARD style, flip the sign to conform
+ * to the standard's interpretation.
and not liking it very much. Yes, it does the intended thing for strict
SQL-spec input, but it seems to produce a bunch of weird corner cases
for non-spec input. Consider
-1 1:00:00 flips the sign
- 1 1:00:00 doesn't flip the sign
-1 day 1:00:00 doesn't flip the sign
-2008-10 1:00:00flips the sign
-2008-10 1 doesn't flip the sign
-2008 years 1:00:00 doesn't flip the sign
If the rule were that it never flipped the sign for non-SQL-spec input
then I think that'd be okay, but case 4 here puts the lie to that.
I'm also not entirely sure if case 2 is allowed by SQL spec or not,
but if it is then we've got a problem with that; and even if it isn't
it's awfully hard to explain why it's treated differently from case 1.
I'm inclined to think we need a more semantically-based instead of
syntactically-based rule. For instance, if first field is negative and
no other field has an explicit sign, then force all fields to be = 0.
This would probably have to be applied at the end of DecodeInterval
instead of on-the-fly within the loop.
Thoughts?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Tom Lane wrote: Another thought here ... I'm looking at the sign hack + if (IntervalStyle == INTSTYLE_SQL_STANDARD and not liking it very much. Yes, it does the intended thing for strict SQL-spec input, but it seems to produce a bunch of weird corner cases for non-spec input. Consider [... many examples ...] I'm inclined to think we need a more semantically-based instead of syntactically-based rule. For instance, if first field is negative and no other field has an explicit sign, then force all fields to be = 0. This would probably have to be applied at the end of DecodeInterval instead of on-the-fly within the loop. Thoughts? I'll take a step back and think about that Yes, at first glance I think that approach is better; but we'd need to make sure not to apply the rule too enthusiastically on traditional postgres intervals; or worse, ones that mix sql standardish and postgres values For example dish=# select interval '-1 1:1 1 years'; interval -- 1 year -1 days +01:01:00 (1 row) that 8.3 accepts. (or do we not care about those)? In some ways I wonder if we should have 2 totally separate parsing one for the SQL standard ones, and one for the postgres. That would avoid some other confusing inputs like: select interval '-00-01 1 years'; select interval '1-1 hours'; select interval '1:1 years'; select interval '1 hours 1-1 1 years' that are currently accepted. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ron Mayer [EMAIL PROTECTED] writes: Tom Lane wrote: BTW, I just noticed that CVS HEAD has a bug in reading negative SQL-spec literals: Perhaps the below patch fixes that? Actually I think it should be if (*field[i] == '-') as in the comparable case for fractional seconds just below. Will apply. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ron Mayer [EMAIL PROTECTED] writes: Yes, at first glance I think that approach is better; but we'd need to make sure not to apply the rule too enthusiastically on traditional postgres intervals; Well, of course we'd only apply it in SQL_STANDARD mode. The idea here is that intervalstyle helps us resolve an ambiguity about what the signs are, more or less independently of syntactic details. If you consider that the issue is sql standard: leading sign applies to all fields postgres: each field is independently signed this applies perfectly well without any restrictions on syntax. In some ways I wonder if we should have 2 totally separate parsing one for the SQL standard ones, and one for the postgres. No, I think we want the style to be a hint for resolving ambiguous cases, not to cause complete failure if the input doesn't conform to the style. That's certainly how DateStyle has always worked. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
I wrote: ... Consider -1 1:00:00 flips the sign - 1 1:00:00 doesn't flip the sign -1 day 1:00:00 doesn't flip the sign -2008-10 1:00:00flips the sign -2008-10 1 doesn't flip the sign -2008 years 1:00:00 doesn't flip the sign If the rule were that it never flipped the sign for non-SQL-spec input then I think that'd be okay, but case 4 here puts the lie to that. I'm also not entirely sure if case 2 is allowed by SQL spec or not, but if it is then we've got a problem with that; and even if it isn't it's awfully hard to explain why it's treated differently from case 1. Actually case 2 is a red herring --- I now see that ParseDateTime() collapses out the whitespace and makes it just like case 1. So never mind that. Case 4 is still bogus though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Doesn't ANSI standard interval syntax have the minus sign before the quotes? Select interval -'2008-10'; ??? -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Saturday, November 08, 2008 11:39 AM To: Ron Mayer Cc: Brendan Jurd; Kevin Grittner; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle BTW, I just noticed that CVS HEAD has a bug in reading negative SQL- spec literals: regression=# select interval '-2008-10'; interval -- -2008 years -10 mons (1 row) regression=# select interval '--10'; interval -- 10 mons (1 row) Surely the latter must mean -10 months. This is orthogonal to the current patch ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Chuck McDevitt [EMAIL PROTECTED] writes: Doesn't ANSI standard interval syntax have the minus sign before the quotes? Select interval -'2008-10'; They allow it either there or inside the quotes. We can't support outside-the-quotes unless we make INTERVAL a fully reserved word (and even then there are syntactic problems :-(). Putting the minus sign before INTERVAL works fine btw ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ron Mayer [EMAIL PROTECTED] writes: Brendan Jurd wrote: The changes to the documentation all look good. I did notice one final typo that I think was introduced in the latest version. doc/src/sgml/datatype.sgml:2270 has Nonstandardrd instead of Nonstandard. Just checked in a fix to that one; and updated my website at http://0ape.com/postgres_interval_patches/ and pushed it to my (hopefully fixed now) git server. Applied with some revisions: I changed the rule for negating input fields in SQL_STANDARD style as we discussed, made IntervalStyle into an enum GUC, and did some pretty heavy editorialization on the docs changes. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ron Mayer [EMAIL PROTECTED] writes: Brendan Jurd wrote: The changes to the documentation all look good. I did notice one final typo that I think was introduced in the latest version. doc/src/sgml/datatype.sgml:2270 has Nonstandardrd instead of Nonstandard. Just checked in a fix to that one; and updated my website at http://0ape.com/postgres_interval_patches/ and pushed it to my (hopefully fixed now) git server. If this'll be the final update to this patch should I be posting it to the mailing list too for the archives? Since it's only one line different from your previous, probably no need. I've started reviewing this patch for commit, and I find myself a bit disturbed by its compatibility properties. The SQL_STANDARD output style is simply ambiguous: what is meant by -1 1:00:00 ? What you get from that will depend on the intervalstyle setting at the recipient. Either of the traditional Postgres styles are non-ambiguous and will be interpreted correctly regardless of receiver's intervalstyle --- in particular, Postgres mode always puts an explicit sign on the time part if the days or months part was negative. What this means is that SQL_STANDARD mode is unsafe for dumping data, and *pg_dump had better force Postgres mode*. We can certainly do that with a couple more lines added to the patch, but it's a bit troublesome that we are boxed into using a nonstandard dump-data format until forever. I don't immediately see any way around that, though. Anyone have a bright idea? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Tom Lane wrote: I've started reviewing this patch for commit, and I find myself a bit disturbed by its compatibility properties. The SQL_STANDARD output style is simply ambiguous: what is meant by -1 1:00:00 ? What you get from that will depend on the intervalstyle setting at the recipient. Nope. The SQL Standard style avoids the ambiguity by following the SQL Standard's rules when the input value complied with the standard's restrictions on intervals. For example - given the sql standard compliant value of negative one days and negative one hours you get -1 1;00:00. If you give it a non-sql-standard-compliant value like negative one days plus one hours it will force outputting all the signs both positive and negative: regression=# select interval '-1 days +1 hours'; interval -- +0-0 -1 +1:00:00 (1 row) I agree that there's an ambiguity on input - in much the same way that date order can affect ambiguous inputs. Either of the traditional Postgres styles are non-ambiguous and will be interpreted correctly regardless of receiver's intervalstyle --- in particular, Postgres mode always puts an explicit sign on the time part if the days or months part was negative. What this means is that SQL_STANDARD mode is unsafe for dumping data, and So long as the SQL Standard style is chosen both on dumping and loading, I think it will preserve any values given to it. *pg_dump had better force Postgres mode*. We can certainly do that with a couple more lines added to the patch, but it's a bit troublesome that we are boxed into using a nonstandard dump-data format until forever. I don't immediately see any way around that, though. Anyone have a bright idea? Are you concerned about someone dumping in SQL_STANDARD mode and then importing in POSTGRES mode? If so, how's the similar case handled with date order? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ron Mayer wrote: Tom Lane wrote: *pg_dump had better force Postgres mode*. We can certainly do that with a couple more lines added to the patch, but it's a bit troublesome that we are boxed into using a nonstandard dump-data format until forever. Ok. I see that is the concern.. Rather than forcing Postgres mode; couldn't it put a set intervalstyle = [whatever the current interval style is] in the dump file? That doesn't force us to using a nonstandard dump-data format (except for the nonstandard set intervalstyle line). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ron Mayer [EMAIL PROTECTED] writes: Tom Lane wrote: I've started reviewing this patch for commit, and I find myself a bit disturbed by its compatibility properties. The SQL_STANDARD output style is simply ambiguous: what is meant by -1 1:00:00 ? What you get from that will depend on the intervalstyle setting at the recipient. Nope. The SQL Standard style avoids the ambiguity by following the SQL Standard's rules when the input value complied with the standard's restrictions on intervals. You're missing the point: the same string can mean different things to different recipients depending on their intervalstyle setting. This means it's unsafe to use that representation in pg_dump output, unless we take steps to force the interpretation. Are you concerned about someone dumping in SQL_STANDARD mode and then importing in POSTGRES mode? Exactly. If so, how's the similar case handled with date order? ISO date format is read the same regardless of recipient's datestyle, so pg_dump solves this by forcing the dump to be made in ISO style. The corresponding solution for intervals will be to dump in POSTGRES style, not SQL_STANDARD style, which seems a bit unfortunate. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ron Mayer [EMAIL PROTECTED] writes: Rather than forcing Postgres mode; couldn't it put a set intervalstyle = [whatever the current interval style is] in the dump file? This would work for loading into a PG = 8.4 server, and fail miserably for loading into pre-8.4 servers. Even though we don't guarantee backwards compatibility of dump files, I'm loath to adopt a solution that will successfully load wrong data into an older server. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Tom Lane wrote: ISO date format is read the same regardless of recipient's datestyle, so pg_dump solves this by forcing the dump to be made in ISO style. The corresponding solution for intervals will be to dump in POSTGRES style, not SQL_STANDARD style, which seems a bit unfortunate. [reading pg_dump.c now] I wonder if it could be similar to standard_conforming_strings where it appears to be reading the current value and setting it to whatever the user chose in the beginning of pg_dump. Then we could dump in whichever intervalstyle the user prefers. Or, for 8.4+ dumps we could even force set intervalstyle = sql_standard; in the top of the dump file. For dumps of 8.3 or less we'd need the non-standard style anyway it seems. If this seems sane, I can try experimenting with it tonight. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Tom Lane wrote: Ron Mayer [EMAIL PROTECTED] writes: Rather than forcing Postgres mode; couldn't it put a set intervalstyle = [whatever the current interval style is] in the dump file? This would work for loading into a PG = 8.4 server, and fail miserably for loading into pre-8.4 servers. Even though we don't guarantee backwards compatibility of dump files, I'm loath to adopt a solution that will successfully load wrong data into an older server. 'k. So the options seem to be: (1) Don't output a SQL-standard interval literal for the value negative one days and negative one hours; perhaps by sticking an extra '+' sign in there? (2) Force pg_dump to a non-standard mode, at least until 8.3's deprecated in many years? After that, pg_dump can use any intervalstyle so long as it says which one it uses. (3) Put something into the dump file that will make the old server reject the file rather than successfully loading wrong data? (Some if intervalstyle==std and version8.3 abort loading the restore logic?) I don't much like the first one, because it seems we're oh-so-close to meeting the standard. I don't know how to do the third one; but if pg_dump had an assert(version=8.4) feature it might be useful if we ever had other non-backward-compatible changes. pg_dump would only put that assert-like-thing in the dump file if the sql_standard mode (or iso mode, if that gets approved) was chosen. The second one doesn't really seem that scary to me; since the uglyness can go away when we eventually stop restoring into 83. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Brendan Jurd wrote: On Wed, Nov 5, 2008 at 7:34 AM, Ron Mayer [EMAIL PROTECTED] wrote: Brendan Jurd wrote: ...new interval Review of the other two patches coming soon to a mail client near you. Oh - and for review of the next patch, ISO 8601's spec would no doubt be useful. I think this is the right place to get it: http://isotc.iso.org/livelink/livelink/4021199/ISO_8601_2004_E.zip?func=doc.Fetchnodeid=4021199 Is there anywhere in the comments, docs, or wiki where such links and/or excerpts from specs belong? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
I think we need to distinguish between patches that are clearly not going in, and patches that are not going in in their present form but might be able to be reworked. My suggestion would be that only the first category be moved to the Returned with feedback section, and the others just have their status changed to Waiting for new version or similar. Alternatively, we could create a separate section for patches in this category. ...Robert On Tue, Nov 4, 2008 at 12:22 AM, Brendan Jurd [EMAIL PROTECTED] wrote: On Thu, Sep 18, 2008 at 6:03 AM, Ron Mayer [EMAIL PROTECTED] wrote: The attached patch (1) adds a new GUC called IntervalStyle that decouples interval output from the DateStyle GUC, and (2) adds a new interval style that will match the SQL standards for interval literals when given interval data that meets the sql standard (year-month or date-time only; and no mixed sign). Hi Ron, I've been assigned to do an initial review of your interval patches. I'm going to be reviewing them one at a time, starting with this one (the introduction of the new IntervalStyle GUC). I grabbed the latest version of the patch from the URL posted up on the CF wiki page: http://0ape.com/postgres_interval_patches/stdintervaloutput.patch Nice site you've got set up for the patches, BTW. It certainly makes it all a lot more approachable. On with the review then ... The patch applied cleanly to the latest version of HEAD in the git repository. I was able to build both postgres and the documentation without complaint on x86_64 gentoo. When I ran the regression tests, I got one failure in the new interval tests. Looks like the nonstandard extended format gets a bit confused when the seconds are negative: *** /home/direvus/src/postgres/src/test/regress/expected/interval.out Tue Nov 4 14:46:34 2008 --- /home/direvus/src/postgres/src/test/regress/results/interval.out Tue Nov 4 15:19:53 2008 *** *** 629,634 - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds'; interval | ?column? --+-- ! +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:06.789 (1 row) --- 629,634 - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds'; interval | ?column? --+-- ! +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:-6.789 (1 row) Otherwise, the feature seemed to behave as advertised. I tried throwing a few bizarre intervals at it, but didn't manage to break anything. The C code has some small stylistic inconsistencies; in some cases the spaces around binary operators are missing (e.g., (fsec0)). See src/backend/utils/adt/datetime.c lines 3691, 3694, 3697, 3729-3731. There are also a lot of function calls missing the space after the argument separator (e.g., sprintf(cp,%d %d:%02d:,mday,hour,min)). Apart from not merging well with the style of the surrounding code, I respectfully suggest that omitting the spaces really does make the code harder to read. The new documentation is good in terms of content, but there are some minor stylistic and spelling cleanups I would suggest. The standard is referred to variously as SQL standard, SQL-standard and SQL Standard in the patch. The surrounding documentation seems to use SQL standard, so that's probably the way to go. These sentences in datatype.sgml are a bit awkward: The postgres style will output intervals that match the style PostgreSQL 8.3 outputed when the DateStyle parameter was set to ISO. The postgres_verbose style will output intervals that match the style PostgreSQL 8.3 outputed when the DateStyle parameter was set to SQL. As far as I know, outputed isn't a word, and singling out 8.3 in particular is a bit misleading, since the statement applies to earlier versions as well. I would go with something more along the lines of: The postgres style will output intervals matching those output by PostgreSQL prior to version 8.4, with the DateStyle parameter set to ISO. Likewise in config.sgml, the patch has: The value postgres will output intervals in a format that matches what old releases had output when the DateStyle was set to 'ISO'. The value postgres_verbose will output intervals in a format that matches what old releases had output when the DateStyle was set to 'SQL'. I don't think old releases is specific enough. Most folks reading the documentation aren't going to know what is meant by old. Better to be precise. Again I would suggest phrasing like ... releases prior to 8.4, with the DateStyle set to That's all the feedback I have for the moment. I hope you found my comments helpful. I'll be setting the status of this patch to Returned with Feedback and wait for your reponses before I move forward with reviewing the other patches. Cheers, BJ -- Sent via
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Brendan Jurd wrote: ...Sep 18, 2008... Ron Mayer [EMAIL PROTECTED] wrote: The attached patch (1) adds a new GUC called IntervalStyle that decouples interval output from the DateStyle GUC, and (2) adds a new interval style that will match the SQL standards for interval literals when given interval data that meets the sql standard (year-month or date-time only; and no mixed sign). I've been assigned to do an initial review of your interval patches. I'm going to be reviewing them one at a time, starting with this one (the introduction of the new IntervalStyle GUC). Great! Thanks much! I grabbed the latest version of the patch from the URL posted up on the CF wiki page: http://0ape.com/postgres_interval_patches/stdintervaloutput.patch Nice site you've got set up for the patches, BTW. It certainly makes it all a lot more approachable. Ah. If you're using GIT, you might find it more convenient to pull/merge from http://git.0ape.com/postgresql/ or browse through gitweb: http://git.0ape.com/?p=postgresql;a=shortlog;h=refs/heads/cleanup http://git.0ape.com/git-browser/by-commit.html?r=postgresql though this is the first time I've set up gitweb so it might have rough edges. The patch applied cleanly to the latest version of HEAD in the git repository. I was able to build both postgres and the documentation without complaint on x86_64 gentoo. When I ran the regression tests, I got one failure in the new interval tests. Looks like the nonstandard extended format gets a bit confused when the seconds are negative: Ah yes. Let me guess, HAVE_INT64_TIMESTAMP was defined. I believe the later refactoring patch also avoids that bug; but yes, I obviously should have had it working in this patch. This fix was simple (can be seen on gitweb here: http://tinyurl.com/5fxeyw) and I think I've pushed the updated patches to my website. Once I fix the stylistic points you mentioned below I'll post the resulting patch to the mailing list. Otherwise, the feature seemed to behave as advertised. I tried throwing a few bizarre intervals at it, but didn't manage to break anything. The C code has some small stylistic inconsistencies ...documentation...some minor stylistic and spelling cleanups I would suggest. Totally agree with all your style suggestions. Will send an update a bit later today. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ah. And one final question regarding functionality. It seems to me that the last remaining place where we input a SQL-2008 standard literal and do something different from what the standard suggests is with the string: '-1 2:03:04' The standard seems to say that the - affects both the days and hour/min/sec part; while PostgreSQL historically, and the patch as I first submitted it only apply the negative sign to the days part. IMHO when the IntervalStyle GUC is set to sql_standard, it'd be better if the parsing of this literal matched the standard. We already have the precedent where DateStyle is used to interpret otherwise ambiguous output. If the IntervalStyle is set to anything other than sql_standard we'll keep parsing them the old way; so I think backwards compatibility issues would be minimized. And those using the sql_standard mode are likely to be standards fanatics anyway, and would probably appreciate following the standard rather than the backward compatible mode. Thoughts? I have a version of each alternative working here, and I'd be happy to submit the final patch either way. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ron Mayer [EMAIL PROTECTED] writes: Ah. And one final question regarding functionality. It seems to me that the last remaining place where we input a SQL-2008 standard literal and do something different from what the standard suggests is with the string: '-1 2:03:04' The standard seems to say that the - affects both the days and hour/min/sec part; while PostgreSQL historically, and the patch as I first submitted it only apply the negative sign to the days part. IMHO when the IntervalStyle GUC is set to sql_standard, it'd be better if the parsing of this literal matched the standard. Then how would you input a value that had different signs for the day and the h/m/s? I don't think you can't is an acceptable answer there, because it would mean that interval_out has to fail on such values when IntervalStyle is sql_standard. Which is very clearly not gonna do. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Tom Lane wrote: Ron Mayer [EMAIL PROTECTED] writes: Ah. And one final question regarding functionality. It seems to me that the last remaining place where we input a SQL-2008 standard literal and do something different from what the standard suggests is with the string: '-1 2:03:04' The standard seems to say that the - affects both the days and hour/min/sec part; while PostgreSQL historically, and the patch as I first submitted it only apply the negative sign to the days part. IMHO when the IntervalStyle GUC is set to sql_standard, it'd be better if the parsing of this literal matched the standard. Then how would you input a value that had different signs for the day and the h/m/s? I don't think you can't is an acceptable answer there, because it would mean that interval_out has to fail on such values when IntervalStyle is sql_standard. Which is very clearly not gonna do. In the patch I submitted: -1 +2:03:04 always means negative day, positive hours/min/sec +1 -2:03:04 always means positive day, negative hours/min/sec When given a non-standard interval value, EncodeInterval is always outputting all the signs (+ and -) to force it to be unambiguous. -- test a couple non-standard interval values too SELECT interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds', - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds'; interval | ?column? --+-- +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:06.789 (1 row) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Brendan Jurd wrote:
...Sep 18, 2008...Ron Mayer [EMAIL PROTECTED] wrote:
(1) ...GUC called IntervalStyle...
(2) ...interval style that will match the SQL standards...
...an initial review...
When I ran the regression tests, I got one failure in the new interval
Fixed, and I did a bit more testing both with and without HAVE_INT64_TIMESTAMP.
The C code has some small stylistic inconsistencies; ...
... spaces around binary operators are missing (e.g., (fsec0)).
Thanks. Fixed these.
...function calls missing the space after the argument separator...
I think I fixed all these now too.
The new documentation is good in terms of content, but there are some
minor stylistic and spelling cleanups I would suggest.
...variously...SQL standard, SQL-standard and SQL Standard...
Got it. There are a few inconsistencies elsewhere in the file
talking about other data types. I wonder if I should fix those
as well.
These sentences in datatype.sgml are a bit awkward ...
I would go with something more along the lines of...
Yes. Thanks for the better wording.
I don't think old releases is specific enough.
Yup - fixed that too.
That's all the feedback I have for the moment. I hope you found my
comments helpful. I'll be setting the status of this patch to
Returned with Feedback and wait for your responses before I move
forward with reviewing the other patches.
Great. I've tried to update the style on my remaining patches as well.
In addition, I've added to the docs describing how I use
explicit '+' and '-' signs to disambiguate the mixed-sign
non-standard intervals when in the sql_standard mode.
As before the 3 patches are at: http://0ape.com/postgres_interval_patches/
and http://git.forensiclogic.com/postgresql/ and
http://git.forensiclogic.com/?p=postgresql;a=shortlog;h=refs/heads/cleanup
I'm attaching the patch dealing with sql standard intervals here for the
archives.
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***
*** 4013,4018 SET XML OPTION { DOCUMENT | CONTENT };
--- 4013,4056
/listitem
/varlistentry
+ varlistentry id=guc-intervalstyle xreflabel=IntervalStyle
+ termvarnameIntervalStyle/varname (typestring/type)/term
+ indexterm
+primaryvarnameIntervalStyle/ configuration parameter/primary
+ /indexterm
+ listitem
+para
+ Sets the display format for interval values.
+ The value literalsql_standard/ will produce
+ output matching acronymSQL/acronym standard
+ interval literals for values that conform to the
+ acronymSQL/acronym standard (either year-month
+ only or date-time only; and no mixing of positive
+ and negative components).
+
+ The value literalpostgres/ will produce output
+ matching PostgreSQL releases prior to 8.4
+ when the xref linkend=guc-datestyle
+ parameter was set to literalISO/.
+
+ The value literalpostgres_verbose/ will produce output
+ matching PostgreSQL releases prior to 8.4
+ when the xref linkend=guc-datestyle
+ parameter was set to literalSQL/.
+/para
+para
+ The IntervalStyle GUC also affects the interpretation
+ of one ambiguous interval literal input. In SQL 2008
+ the negative sign in the interval literal '-1 2:03:04'
+ applies to both the days and hour/minute/second parts.
+ PostgreSQL traditionally only applied the negative
+ sign to the days part. If IntervalStyle is set to
+ literalsql_standard/literal it will follow the standard
+ otherwise it uses the traditional postgres interpretation.
+/para
+ /listitem
+ /varlistentry
+
varlistentry id=guc-timezone xreflabel=timezone
termvarnametimezone/varname (typestring/type)/term
indexterm
*** a/doc/src/sgml/datatype.sgml
--- b/doc/src/sgml/datatype.sgml
***
*** 1962,1968 January 8 04:05:06 1999 PST
a combination of years and months can be specified with a dash;
for example literal'200-10'/ is read the same as literal'200 years
10 months'/. (These shorter forms are in fact the only ones allowed
! by the SQL standard.)
/para
para
--- 1962,1968
a combination of years and months can be specified with a dash;
for example literal'200-10'/ is read the same as literal'200 years
10 months'/. (These shorter forms are in fact the only ones allowed
! by the acronymSQL/acronym standard.)
/para
para
***
*** 2213,2218 January 8 04:05:06 1999 PST
--- 2213,2310
/para
/sect2
+sect2 id=interval-output
+ titleInterval Output/title
+
+ indexterm
+ primaryinterval/primary
+ secondaryoutput format/secondary
+ seealsoformatting/seealso
+ /indexterm
+
+ para
+
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
On Wed, Nov 5, 2008 at 7:34 AM, Ron Mayer [EMAIL PROTECTED] wrote: Brendan Jurd wrote: When I ran the regression tests, I got one failure in the new interval Fixed, and I did a bit more testing both with and without HAVE_INT64_TIMESTAMP. Confirmed, all regression tests now pass on my system with the updated patch. The C code has some small stylistic inconsistencies; ... ... spaces around binary operators are missing (e.g., (fsec0)). Thanks. Fixed these. ...function calls missing the space after the argument separator... I think I fixed all these now too. Awesome. As far as I can tell, you got them all. I don't have any further nits to pick about the code style. The changes to the documentation all look good. I did notice one final typo that I think was introduced in the latest version. doc/src/sgml/datatype.sgml:2270 has Nonstandardrd instead of Nonstandard. But, apart from that I have no further feedback. I will sign off on this one and mark it Ready for committer in the commitfest. Review of the other two patches coming soon to a mail client near you. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Brendan Jurd wrote: The changes to the documentation all look good. I did notice one final typo that I think was introduced in the latest version. doc/src/sgml/datatype.sgml:2270 has Nonstandardrd instead of Nonstandard. Just checked in a fix to that one; and updated my website at http://0ape.com/postgres_interval_patches/ and pushed it to my (hopefully fixed now) git server. If this'll be the final update to this patch should I be posting it to the mailing list too for the archives? But, apart from that I have no further feedback. I will sign off on this one and mark it Ready for committer in the commitfest. Cool. I'm not sure if anyone still wants to weigh in on the approach I took for mixed-sign intervals, where '-1 2:03:04' gets interpreted differently depending on the date style, and '-1 +2:03:04' and '-1 -2:03:04' are the way I'm using to disambiguate them. Review of the other two patches coming soon to a mail client near you. Feel free to do them one-at-a-time too; since no doubt any issues with the first one will probably affect the second one too. I think I updated the other patches for the missing whitespace style issues my first patch had; but no doubt there could be other bad habits I have as well. Ron -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
On Thu, Sep 18, 2008 at 6:03 AM, Ron Mayer [EMAIL PROTECTED] wrote: The attached patch (1) adds a new GUC called IntervalStyle that decouples interval output from the DateStyle GUC, and (2) adds a new interval style that will match the SQL standards for interval literals when given interval data that meets the sql standard (year-month or date-time only; and no mixed sign). Hi Ron, I've been assigned to do an initial review of your interval patches. I'm going to be reviewing them one at a time, starting with this one (the introduction of the new IntervalStyle GUC). I grabbed the latest version of the patch from the URL posted up on the CF wiki page: http://0ape.com/postgres_interval_patches/stdintervaloutput.patch Nice site you've got set up for the patches, BTW. It certainly makes it all a lot more approachable. On with the review then ... The patch applied cleanly to the latest version of HEAD in the git repository. I was able to build both postgres and the documentation without complaint on x86_64 gentoo. When I ran the regression tests, I got one failure in the new interval tests. Looks like the nonstandard extended format gets a bit confused when the seconds are negative: *** /home/direvus/src/postgres/src/test/regress/expected/interval.out Tue Nov 4 14:46:34 2008 --- /home/direvus/src/postgres/src/test/regress/results/interval.out Tue Nov 4 15:19:53 2008 *** *** 629,634 - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds'; interval | ?column? --+-- ! +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:06.789 (1 row) --- 629,634 - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds'; interval | ?column? --+-- ! +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:-6.789 (1 row) Otherwise, the feature seemed to behave as advertised. I tried throwing a few bizarre intervals at it, but didn't manage to break anything. The C code has some small stylistic inconsistencies; in some cases the spaces around binary operators are missing (e.g., (fsec0)). See src/backend/utils/adt/datetime.c lines 3691, 3694, 3697, 3729-3731. There are also a lot of function calls missing the space after the argument separator (e.g., sprintf(cp,%d %d:%02d:,mday,hour,min)). Apart from not merging well with the style of the surrounding code, I respectfully suggest that omitting the spaces really does make the code harder to read. The new documentation is good in terms of content, but there are some minor stylistic and spelling cleanups I would suggest. The standard is referred to variously as SQL standard, SQL-standard and SQL Standard in the patch. The surrounding documentation seems to use SQL standard, so that's probably the way to go. These sentences in datatype.sgml are a bit awkward: The postgres style will output intervals that match the style PostgreSQL 8.3 outputed when the DateStyle parameter was set to ISO. The postgres_verbose style will output intervals that match the style PostgreSQL 8.3 outputed when the DateStyle parameter was set to SQL. As far as I know, outputed isn't a word, and singling out 8.3 in particular is a bit misleading, since the statement applies to earlier versions as well. I would go with something more along the lines of: The postgres style will output intervals matching those output by PostgreSQL prior to version 8.4, with the DateStyle parameter set to ISO. Likewise in config.sgml, the patch has: The value postgres will output intervals in a format that matches what old releases had output when the DateStyle was set to 'ISO'. The value postgres_verbose will output intervals in a format that matches what old releases had output when the DateStyle was set to 'SQL'. I don't think old releases is specific enough. Most folks reading the documentation aren't going to know what is meant by old. Better to be precise. Again I would suggest phrasing like ... releases prior to 8.4, with the DateStyle set to That's all the feedback I have for the moment. I hope you found my comments helpful. I'll be setting the status of this patch to Returned with Feedback and wait for your reponses before I move forward with reviewing the other patches. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Tom Lane wrote:
In fact, given that we are now
somewhat SQL-compliant on interval input, a GUC that selected
PG traditional, SQL-standard, or ISO 8601 interval output format seems
like it could be a good idea.
Short summary:
The attached patch
(1) adds a new GUC called IntervalStyle that decouples interval
output from the DateStyle GUC, and
(2) adds a new interval style that will match the SQL standards
for interval literals when given interval data that meets the
sql standard (year-month or date-time only; and no mixed sign).
Background:
Currently Postgres outputs Intervals in one of two formats
depending on DateStyle. When DateStyle is 'ISO', it outputs
intervals like '1 year 2 mons 3 days -04:05:06.78' (though I
know of no ISO interval standards that look like that). When
DateStyle is 'SQL' it outputs intervals like
'@ 1 year 2 mons 3 days -4 hours -5 mins -6.78 secs' (though
I know of no SQL interval standards that look like that).
The feature:
The SQL standard only specifies interval literal strings
for the two kinds of intervals (year-month and day-time)
that are defined by the SQL spec. It also doesn't account
for postgres's intervals that can have mixed-sign components.
I tried to make the output here be a logical extension
of the spec, concatenating the year-month and day-time
interval strings and forcing signs in the output that could
otherwise have been ambiguous.
This little table shows an example of the output of this
new style compared to the existing postgres output for
(a) a year-month interval, (b) a day-time interval, and
(c) a not-quite-standard postgres interval with year-month
and day-time components of varying signs.
'1-2' | '@ 1 year 2 mons'
'3 4:05:06.78'| '@ 3 days 4 hours 5 mins 6.78 secs'
'+1-2 -3 +4:05:06.78' | '@ 1 year 2 mons -3 days 4 hours 5 mins 6.78 secs'
The patch:
Seems to work for me; and I believe I updated the docs.
Many regression tests fail, though, because they assume
the existing coupling of DateStyle and interval output
styles. If people like where this is going I can update
those regression tests and add ones to test this new style.
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***
*** 4090,4095 SET XML OPTION { DOCUMENT | CONTENT };
--- 4090,4117
/listitem
/varlistentry
+ varlistentry id=guc-intervalstyle xreflabel=IntervalStyle
+ termvarnameIntervalStyle/varname (typestring/type)/term
+ indexterm
+primaryvarnameIntervalStyle/ configuration parameter/primary
+ /indexterm
+ listitem
+para
+ Sets the display format for interval values.
+ The value literalsql_standard/ will output SQL Standard
+ strings when given intervals that conform to the SQL
+ standard (either year-month only or date-time only; and no
+ mixing of positive and negative components).
+ The value literalpostgres/ will output intervals in
+ a format that matches what old releases had output when
+ the DateStyle was set to literal'ISO'/.
+ The value literalpostgres_verbose/ will output intervals in
+ a format that matches what old releases had output when
+ the DateStyle was set to literal'SQL'/.
+/para
+ /listitem
+ /varlistentry
+
varlistentry id=guc-timezone xreflabel=timezone
termvarnametimezone/varname (typestring/type)/term
indexterm
*** a/doc/src/sgml/datatype.sgml
--- b/doc/src/sgml/datatype.sgml
***
*** 2213,2218 January 8 04:05:06 1999 PST
--- 2213,2305
/para
/sect2
+sect2 id=interval-output
+ titleInterval Output/title
+
+ indexterm
+ primaryinterval/primary
+ secondaryoutput format/secondary
+ seealsoformatting/seealso
+ /indexterm
+
+ para
+ The output format of the interval types can be set to one of the four
+ styles literalsql_standard/,
+ literalpostgres/, or literalpostgres_verbose/.The default
+ is the literalpostgres/ format.
+ xref
+ linkend=interval-style-output-table shows examples of each
+ output style.
+ /para
+
+ para
+ The literalsql_standard/ style will output SQL standard
+ interval literal strings where the value of the interval
+ value consists of only a year-month component or a datetime
+ component (as required by the sql standard). For an interval
+ containing both a year-month and a datetime component, the
+ output will be a SQL Standard unquoted year-month literal
+ string joined to a SQL Standard unquoted datetime literal
+ string with a space in between.
+ /para
+
+ para
+ The literalpostgres/ style will output intervals that match
+ the style PostgreSQL 8.3 outputed when the xref
