Re: [HACKERS] insert throw error when year field len 4 for timestamptz datatype
On Tue, Oct 8, 2013 at 09:05:37AM -0400, Bruce Momjian wrote:
On Tue, Oct 8, 2013 at 05:08:17PM +0530, Rushabh Lathia wrote:
This
might be a case where throwing an error is actually better than trying
to make sense of the input.
I don't feel super-strongly about this, but I offer it as a question
for reflection.
At the same time I do agree fixing this kind of issue in postgres datetime
module
is bit difficult without some assumption. Personally I feel patch do add
some
value but not fully compatible with all kind of year field format.
Bruce,
Do you have any thought/suggestion ?
I think Robert is asking the right question: Is it better to accept
5-digit years, or throw an error? Doing anything new with 6-digit years
is going to break the much more common use of YMD or HMS.
The timestamp data type only supports values to year 294276, so the full
6-digit range isn't even supported. ('DATE' does go higher.)
The entire date/time processing allows imprecise input, so throwing an
error on clear 5-digit years seems wrong. Basically, we have gone down
the road of interpreting date/time input liberally, so throwing an error
on a clear 5-digit year seems odd.
On the other hand, this has never come up before because no one cared
about 5-digit years, so you could argue that 5-digit years require
precise specification, which would favor throwing an error.
Patch applied to support 5+ digit years in non-ISO timestamp/date
strings, where appropriate.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
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] insert throw error when year field len 4 for timestamptz datatype
On Tue, Oct 8, 2013 at 1:34 AM, Robert Haas robertmh...@gmail.com wrote:
On Mon, Oct 7, 2013 at 12:41 AM, Rushabh Lathia
rushabh.lat...@gmail.com wrote:
Hmm right it has some inconsistency when year length is 6. But the patch
is based on assumption that 5-digit number is a year, because YMD and HMS
require at least six digits. Now Year with 6-digit number its getting
conflict with
YMD and HMS, that the reason its ending up with error. So with patch
approach
that's an expected behaviour for me.
I spent good amount of time on thinking how we can improve the
behaviour, or
how can be change the assumption about the year field, YMD and HMS. At
current point of time it seems difficult to me because postgres date
module
is tightly build with few assumption and changing that may lead to big
project.
Not sure but personally I feel that patch which was submitted earlier was
definitely good improvement.
Any other suggestion or thought for improvement ?
I'm not entirely convinced that this patch is heading in the right
direction. The thing is, it lets you use 5-digit years always and
longer years only in some contexts. So I'm not sure this is really
good enough for unambiguous date input. If you want that, you should
probably be using trusty YYY-MM-DD format. But if you don't
need that, then isn't a five-digit year most likely a typo?
Do agree with you in certain extent.
But there are already ambiguity when it comes to postgres date module:
For example:
-- Doing select with year field 4
edb=# select '10-10-2'::timestamp;
timestamp
---
Thu Oct 10 00:00:00 2
(1 row)
edb=# create table test ( a timestamp );
CREATE TABLE
-- When try to insert it throw an error
edb=# insert into test values ('Thu Oct 10 00:00:00 2');
ERROR: invalid input syntax for type timestamp: Thu Oct 10 00:00:00 2
LINE 1: insert into test values ('Thu Oct 10 00:00:00 2');
^
Of course user can use the specific format and then this kind of date
can be used.
This
might be a case where throwing an error is actually better than trying
to make sense of the input.
I don't feel super-strongly about this, but I offer it as a question
for reflection.
At the same time I do agree fixing this kind of issue in postgres datetime
module
is bit difficult without some assumption. Personally I feel patch do add
some
value but not fully compatible with all kind of year field format.
Bruce,
Do you have any thought/suggestion ?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Rushabh Lathia
Re: [HACKERS] insert throw error when year field len 4 for timestamptz datatype
On Tue, Oct 8, 2013 at 05:08:17PM +0530, Rushabh Lathia wrote:
This
might be a case where throwing an error is actually better than trying
to make sense of the input.
I don't feel super-strongly about this, but I offer it as a question
for reflection.
At the same time I do agree fixing this kind of issue in postgres datetime
module
is bit difficult without some assumption. Personally I feel patch do add some
value but not fully compatible with all kind of year field format.
Bruce,
Do you have any thought/suggestion ?
I think Robert is asking the right question: Is it better to accept
5-digit years, or throw an error? Doing anything new with 6-digit years
is going to break the much more common use of YMD or HMS.
The timestamp data type only supports values to year 294276, so the full
6-digit range isn't even supported. ('DATE' does go higher.)
The entire date/time processing allows imprecise input, so throwing an
error on clear 5-digit years seems wrong. Basically, we have gone down
the road of interpreting date/time input liberally, so throwing an error
on a clear 5-digit year seems odd.
On the other hand, this has never come up before because no one cared
about 5-digit years, so you could argue that 5-digit years require
precise specification, which would favor throwing an error.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] insert throw error when year field len 4 for timestamptz datatype
On Mon, Oct 7, 2013 at 12:41 AM, Rushabh Lathia rushabh.lat...@gmail.com wrote: Hmm right it has some inconsistency when year length is 6. But the patch is based on assumption that 5-digit number is a year, because YMD and HMS require at least six digits. Now Year with 6-digit number its getting conflict with YMD and HMS, that the reason its ending up with error. So with patch approach that's an expected behaviour for me. I spent good amount of time on thinking how we can improve the behaviour, or how can be change the assumption about the year field, YMD and HMS. At current point of time it seems difficult to me because postgres date module is tightly build with few assumption and changing that may lead to big project. Not sure but personally I feel that patch which was submitted earlier was definitely good improvement. Any other suggestion or thought for improvement ? I'm not entirely convinced that this patch is heading in the right direction. The thing is, it lets you use 5-digit years always and longer years only in some contexts. So I'm not sure this is really good enough for unambiguous date input. If you want that, you should probably be using trusty YYY-MM-DD format. But if you don't need that, then isn't a five-digit year most likely a typo? This might be a case where throwing an error is actually better than trying to make sense of the input. I don't feel super-strongly about this, but I offer it as a question for reflection. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] insert throw error when year field len 4 for timestamptz datatype
On Fri, Oct 4, 2013 at 11:35 PM, Bruce Momjian br...@momjian.us wrote: On Fri, Oct 4, 2013 at 10:19:38AM +, Haribabu kommi wrote: On 03 October 2013 19:30 Bruce Momjian wrote: On Thu, Oct 3, 2013 at 11:54:14AM +0530, Rushabh Lathia wrote: Thanks Bruce. Yes for me main problem was to make assumption that a 5-digit number is a year, as was bit worried about side effect of that assumption in the date/time module. I did tested patch shared by you with various test and so far it looks good to me. I would like reviewer to review/test the patch and share his comments. Attaching the git patch again with this mail. Assigning to Reviewer. Oh, great. If everyone likes it I can apply it. With Year length of 6 digits has some inconsistency problem, The tests are carried out on a default configuration. The general limitation we have is that while we know 5-digit numbers can't be YMD or HMS, we don't know that for 6-digit values, so we require that the string contain _a_ date and _a_ time specification before we consider a six-digit number as a year. I don't see how we can do any better than that. Your results below show that behavior. Do you have a suggestion for improvement? Hmm right it has some inconsistency when year length is 6. But the patch is based on assumption that 5-digit number is a year, because YMD and HMS require at least six digits. Now Year with 6-digit number its getting conflict with YMD and HMS, that the reason its ending up with error. So with patch approach that's an expected behaviour for me. I spent good amount of time on thinking how we can improve the behaviour, or how can be change the assumption about the year field, YMD and HMS. At current point of time it seems difficult to me because postgres date module is tightly build with few assumption and changing that may lead to big project. Not sure but personally I feel that patch which was submitted earlier was definitely good improvement. Any other suggestion or thought for improvement ? --- select timestamptz '199910108 01:01:01 IST'; -- works select timestamptz '19991 01 08 01:01:01 IST'; -- works select timestamptz '1999100108 01:01:01 IST'; -- works select timestamptz '199910 01 08 01:01:01 IST'; -- Not working select timestamptz 'January 8, 19991 01:01:01 IST'; -- works select timestamptz 'January 8, 199910 01:01:01 IST'; -- Not working CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz); INSERT INTO TIMESTAMPTZ_TST VALUES(1, '10312 23:58:48 IST'); -- works INSERT INTO TIMESTAMPTZ_TST VALUES(2, '1 03 12 23:58:48 IST'); -- works INSERT INTO TIMESTAMPTZ_TST VALUES(3, '100312 23:58:48 IST'); -- works INSERT INTO TIMESTAMPTZ_TST VALUES(4, '10 03 12 23:58:48 IST'); -- Not working please correct me if anything wrong in the tests. Regards, Hari babu. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Rushabh Lathia
Re: [HACKERS] insert throw error when year field len 4 for timestamptz datatype
On 03 October 2013 19:30 Bruce Momjian wrote: On Thu, Oct 3, 2013 at 11:54:14AM +0530, Rushabh Lathia wrote: Thanks Bruce. Yes for me main problem was to make assumption that a 5-digit number is a year, as was bit worried about side effect of that assumption in the date/time module. I did tested patch shared by you with various test and so far it looks good to me. I would like reviewer to review/test the patch and share his comments. Attaching the git patch again with this mail. Assigning to Reviewer. Oh, great. If everyone likes it I can apply it. With Year length of 6 digits has some inconsistency problem, The tests are carried out on a default configuration. select timestamptz '199910108 01:01:01 IST'; -- works select timestamptz '19991 01 08 01:01:01 IST'; -- works select timestamptz '1999100108 01:01:01 IST'; -- works select timestamptz '199910 01 08 01:01:01 IST'; -- Not working select timestamptz 'January 8, 19991 01:01:01 IST'; -- works select timestamptz 'January 8, 199910 01:01:01 IST'; -- Not working CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz); INSERT INTO TIMESTAMPTZ_TST VALUES(1, '10312 23:58:48 IST'); -- works INSERT INTO TIMESTAMPTZ_TST VALUES(2, '1 03 12 23:58:48 IST'); -- works INSERT INTO TIMESTAMPTZ_TST VALUES(3, '100312 23:58:48 IST'); -- works INSERT INTO TIMESTAMPTZ_TST VALUES(4, '10 03 12 23:58:48 IST'); -- Not working please correct me if anything wrong in the tests. Regards, Hari babu. -- 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] insert throw error when year field len 4 for timestamptz datatype
On Fri, Oct 4, 2013 at 10:19:38AM +, Haribabu kommi wrote: On 03 October 2013 19:30 Bruce Momjian wrote: On Thu, Oct 3, 2013 at 11:54:14AM +0530, Rushabh Lathia wrote: Thanks Bruce. Yes for me main problem was to make assumption that a 5-digit number is a year, as was bit worried about side effect of that assumption in the date/time module. I did tested patch shared by you with various test and so far it looks good to me. I would like reviewer to review/test the patch and share his comments. Attaching the git patch again with this mail. Assigning to Reviewer. Oh, great. If everyone likes it I can apply it. With Year length of 6 digits has some inconsistency problem, The tests are carried out on a default configuration. The general limitation we have is that while we know 5-digit numbers can't be YMD or HMS, we don't know that for 6-digit values, so we require that the string contain _a_ date and _a_ time specification before we consider a six-digit number as a year. I don't see how we can do any better than that. Your results below show that behavior. Do you have a suggestion for improvement? --- select timestamptz '199910108 01:01:01 IST'; -- works select timestamptz '19991 01 08 01:01:01 IST'; -- works select timestamptz '1999100108 01:01:01 IST'; -- works select timestamptz '199910 01 08 01:01:01 IST'; -- Not working select timestamptz 'January 8, 19991 01:01:01 IST'; -- works select timestamptz 'January 8, 199910 01:01:01 IST'; -- Not working CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz); INSERT INTO TIMESTAMPTZ_TST VALUES(1, '10312 23:58:48 IST'); -- works INSERT INTO TIMESTAMPTZ_TST VALUES(2, '1 03 12 23:58:48 IST'); -- works INSERT INTO TIMESTAMPTZ_TST VALUES(3, '100312 23:58:48 IST'); -- works INSERT INTO TIMESTAMPTZ_TST VALUES(4, '10 03 12 23:58:48 IST'); -- Not working please correct me if anything wrong in the tests. Regards, Hari babu. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] insert throw error when year field len 4 for timestamptz datatype
Thanks Bruce. Yes for me main problem was to make assumption that a 5-digit number is a year, as was bit worried about side effect of that assumption in the date/time module. I did tested patch shared by you with various test and so far it looks good to me. I would like reviewer to review/test the patch and share his comments. Attaching the git patch again with this mail. Assigning to Reviewer. Regards, Rushabh On Wed, Oct 2, 2013 at 9:34 PM, Bruce Momjian br...@momjian.us wrote: On Wed, Oct 2, 2013 at 11:00:30AM -0400, Robert Haas wrote: On Tue, Oct 1, 2013 at 7:52 PM, Bruce Momjian br...@momjian.us wrote: On Fri, Sep 27, 2013 at 10:42:17AM +, Haribabu kommi wrote: If the changes are very high to deal all scenarios, I feel it is better do it only in scenarios where the use cases needs it, until it is not confusing users. The rest can be documented. Any other opinions/suggestions welcome. I have reviewed this patch and it is good. The problem is guessing if a number with 5+ digits is YMD, HMS, or a year. I have created a modified patch, attached, assumes a 5-digit number is a year, because YMD and HMS require at least six digits, and used your date/time test to control the other cases. I also added a few more regression tests. In an ideal world the interpretation of the tokens wouldn't depend on the order in which they appear. But we don't live in an ideal world, so maybe this is fine. Yes, earlier in the thread the original patch poster questioned whether he was going in the right direction, given the unusual hacks needed, but such hacks are standard operating procedure for date/time stuff. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Rushabh Lathia timestamptz_fix_with_testcase_v3.patch Description: Binary data -- 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] insert throw error when year field len 4 for timestamptz datatype
On Thu, Oct 3, 2013 at 11:54:14AM +0530, Rushabh Lathia wrote: Thanks Bruce. Yes for me main problem was to make assumption that a 5-digit number is a year, as was bit worried about side effect of that assumption in the date/time module. I did tested patch shared by you with various test and so far it looks good to me. I would like reviewer to review/test the patch and share his comments. Attaching the git patch again with this mail. Assigning to Reviewer. Oh, great. If everyone likes it I can apply it. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] insert throw error when year field len 4 for timestamptz datatype
On Tue, Oct 1, 2013 at 7:52 PM, Bruce Momjian br...@momjian.us wrote: On Fri, Sep 27, 2013 at 10:42:17AM +, Haribabu kommi wrote: If the changes are very high to deal all scenarios, I feel it is better do it only in scenarios where the use cases needs it, until it is not confusing users. The rest can be documented. Any other opinions/suggestions welcome. I have reviewed this patch and it is good. The problem is guessing if a number with 5+ digits is YMD, HMS, or a year. I have created a modified patch, attached, assumes a 5-digit number is a year, because YMD and HMS require at least six digits, and used your date/time test to control the other cases. I also added a few more regression tests. In an ideal world the interpretation of the tokens wouldn't depend on the order in which they appear. But we don't live in an ideal world, so maybe this is fine. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] insert throw error when year field len 4 for timestamptz datatype
On Wed, Oct 2, 2013 at 11:00:30AM -0400, Robert Haas wrote: On Tue, Oct 1, 2013 at 7:52 PM, Bruce Momjian br...@momjian.us wrote: On Fri, Sep 27, 2013 at 10:42:17AM +, Haribabu kommi wrote: If the changes are very high to deal all scenarios, I feel it is better do it only in scenarios where the use cases needs it, until it is not confusing users. The rest can be documented. Any other opinions/suggestions welcome. I have reviewed this patch and it is good. The problem is guessing if a number with 5+ digits is YMD, HMS, or a year. I have created a modified patch, attached, assumes a 5-digit number is a year, because YMD and HMS require at least six digits, and used your date/time test to control the other cases. I also added a few more regression tests. In an ideal world the interpretation of the tokens wouldn't depend on the order in which they appear. But we don't live in an ideal world, so maybe this is fine. Yes, earlier in the thread the original patch poster questioned whether he was going in the right direction, given the unusual hacks needed, but such hacks are standard operating procedure for date/time stuff. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] insert throw error when year field len 4 for timestamptz datatype
On Fri, Sep 27, 2013 at 10:42:17AM +, Haribabu kommi wrote:
If the changes are very high to deal all scenarios,
I feel it is better do it only in scenarios where the use cases needs it,
until
it is not confusing users.
The rest can be documented.
Any other opinions/suggestions welcome.
I have reviewed this patch and it is good. The problem is guessing if a
number with 5+ digits is YMD, HMS, or a year. I have created a modified
patch, attached, assumes a 5-digit number is a year, because YMD and HMS
require at least six digits, and used your date/time test to control the
other cases. I also added a few more regression tests.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
new file mode 100644
index f39353f..48bf3db
*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
*** DecodeDateTime(char **field, int *ftype,
*** 1161,1167
if (dterr 0)
return dterr;
}
! else if (flen 4)
{
dterr = DecodeNumberField(flen, field[i], fmask,
tmask, tm,
--- 1161,1177
if (dterr 0)
return dterr;
}
! /*
! * Is this a YMD or HMS specification, or a year number?
! * YMD and HMS are required to be six digits or more, so
! * if it is 5 digits, it is a year. If it is six or more
! * more digits, we assume it is YMD or HMS unless no date
! * and no time values have been specified. This forces
! * 6+ digit years to be at the end of the string, or to use
! * the ISO date specification.
! */
! else if (flen = 6 (!(fmask DTK_DATE_M) ||
! !(fmask DTK_TIME_M)))
{
dterr = DecodeNumberField(flen, field[i], fmask,
tmask, tm,
*** DecodeNumberField(int len, char *str, in
*** 2647,2675
/* No decimal point and no complete date yet? */
else if ((fmask DTK_DATE_M) != DTK_DATE_M)
{
! /* mmdd? */
! if (len == 8)
! {
! *tmask = DTK_DATE_M;
!
! tm-tm_mday = atoi(str + 6);
! *(str + 6) = '\0';
! tm-tm_mon = atoi(str + 4);
! *(str + 4) = '\0';
! tm-tm_year = atoi(str + 0);
!
! return DTK_DATE;
! }
! /* yymmdd? */
! else if (len == 6)
{
*tmask = DTK_DATE_M;
! tm-tm_mday = atoi(str + 4);
! *(str + 4) = '\0';
! tm-tm_mon = atoi(str + 2);
! *(str + 2) = '\0';
! tm-tm_year = atoi(str + 0);
! *is2digits = TRUE;
return DTK_DATE;
}
--- 2657,2676
/* No decimal point and no complete date yet? */
else if ((fmask DTK_DATE_M) != DTK_DATE_M)
{
! if (len = 6)
{
*tmask = DTK_DATE_M;
! /*
! * Start from end and consider first 2 as Day, next 2 as Month,
! * and the rest as Year.
! */
! tm-tm_mday = atoi(str + (len - 2));
! *(str + (len - 2)) = '\0';
! tm-tm_mon = atoi(str + (len - 4));
! *(str + (len - 4)) = '\0';
! tm-tm_year = atoi(str);
! if ((len - 4) == 2)
! *is2digits = TRUE;
return DTK_DATE;
}
*** DecodeNumberField(int len, char *str, in
*** 2686,2692
*(str + 4) = '\0';
tm-tm_min = atoi(str + 2);
*(str + 2) = '\0';
! tm-tm_hour = atoi(str + 0);
return DTK_TIME;
}
--- 2687,2693
*(str + 4) = '\0';
tm-tm_min = atoi(str + 2);
*(str + 2) = '\0';
! tm-tm_hour = atoi(str);
return DTK_TIME;
}
*** DecodeNumberField(int len, char *str, in
*** 2697,2703
tm-tm_sec = 0;
tm-tm_min = atoi(str + 2);
*(str + 2) = '\0';
! tm-tm_hour = atoi(str + 0);
return DTK_TIME;
}
--- 2698,2704
tm-tm_sec = 0;
tm-tm_min = atoi(str + 2);
*(str + 2) = '\0';
! tm-tm_hour = atoi(str);
return DTK_TIME;
}
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
new file mode 100644
index 6581b5e..9f4f7a4
*** a/src/test/regress/expected/timestamptz.out
--- b/src/test/regress/expected/timestamptz.out
*** SELECT '' AS to_char_11, to_char(d1, 'FM
*** 1675,1677
--- 1675,1699
| 2001 1 1 1 1 1 1
(66 rows)
+ CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
+ -- Test year field value with len 4
+ INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 12 23:58:48 1 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 12 23:58:48 10 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(3, '1 Mar 12 23:58:48 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(4, '10312 23:58:48 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(4, '100312 23:58:48 IST');
+ --Verify data
+ SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
+ a | b
+
Re: [HACKERS] insert throw error when year field len 4 for timestamptz datatype
Sorry for delay in reply.
On Tue, Sep 17, 2013 at 6:23 PM, Haribabu kommi
haribabu.ko...@huawei.comwrote:
On Tue, 17 September 2013 14:33 Rushabh Lathia wrote:
On Mon, Sep 16, 2013 at 7:22 PM, Haribabu kommi
haribabu.ko...@huawei.com wrote:
***On *14 August 2013 Rushabh Lathia wrote:
postgres=# create table test ( a timestamptz);
CREATE TABLE
-- Date with year 1000
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');*
***
INSERT 0 1
-- Now try with year 1 it will return error
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1 IST');
ERROR: invalid input syntax for type timestamp with time zone: Sat
Mar 11 23:58:48 1 IST
LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 1 IST');
Patch applies cleanly to HEAD. As this patch tries to improve in
inserting the date of the year value to be more than 4 in length.
But it didn’t solve all the ways to insert the year field more than 4
in length. Please check the following test.
** **
postgres=# insert into test values ('10001010 10:10:10 IST');
INSERT 0 1
postgres=# insert into test values ('100011010 10:10:10 IST');
ERROR: invalid input syntax for type timestamp with time zone:
100011010 10:10:10 IST at character 26
STATEMENT: insert into test values ('100011010 10:10:10 IST');
ERROR: invalid input syntax for type timestamp with time zone:
100011010 10:10:10 IST
LINE 1: insert into test values ('100011010 10:10:10 IST');
^
I feel it is better to provide the functionality of inserting year
field more than 4 in length in all flows.
** **
+1. Nice catch.
** **
Here is the latest version of patch which handles the functionality in
all flows.
Could you test it and share you comments.
** **
I am getting some other failures with the updated patch also, please check
the following tests.
** **
select date 'January 8, 19990';
select timestamptz 'January 8, 199910 01:01:01 IST';
INSERT INTO TIMESTAMPTZ_TST VALUES(4, '10001 SAT 8 MAR 10:10:10 IST');
** **
you can get the test scripts from regress test files of date.sql,
timetz.sql, timestamp.sql and timestamptz.sql
and modify according to the patch for verification.
** **
I feel changing the year value to accept the length (4) is not simple. **
**
So many places the year length crossing more than length 4 is not
considered.
Search in the code with “” and correct all related paths.
Right, changing the year value to accept the length (4) is not simple
because so
many places the year length crossing plus most of the please having
assumption
that it will be always 4.
Tried to fix issue more couple of places but I don't feeling like its
always going
to be safe to assume that we covered all path.
Still looking and wondering if we can do change in any simple place or
whether
we can find any other smarter way to fix the issue.
** **
Regards,
Hari babu.
** **
--
Rushabh Lathia
Re: [HACKERS] insert throw error when year field len 4 for timestamptz datatype
On 27 September 2013 15:04 Rushabh Lathia wrote: On Tue, Sep 17, 2013 at 6:23 PM, Haribabu kommi haribabu.ko...@huawei.commailto:haribabu.ko...@huawei.com wrote: I feel changing the year value to accept the length (4) is not simple. So many places the year length crossing more than length 4 is not considered. Search in the code with and correct all related paths. Right, changing the year value to accept the length (4) is not simple because so many places the year length crossing plus most of the please having assumption that it will be always 4. Tried to fix issue more couple of places but I don't feeling like its always going to be safe to assume that we covered all path. Still looking and wondering if we can do change in any simple place or whether we can find any other smarter way to fix the issue. If the changes are very high to deal all scenarios, I feel it is better do it only in scenarios where the use cases needs it, until it is not confusing users. The rest can be documented. Any other opinions/suggestions welcome. Regards, Hari babu.
Re: [HACKERS] insert throw error when year field len 4 for timestamptz datatype
On Mon, Sep 16, 2013 at 7:22 PM, Haribabu kommi
haribabu.ko...@huawei.comwrote:
*On *14 August 2013 Rushabh Lathia wrote:**
** **
postgres=# create table test ( a timestamptz);
CREATE TABLE
** **
-- Date with year 1000
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');***
*
INSERT 0 1
** **
-- Now try with year 1 it will return error
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1 IST');**
**
ERROR: invalid input syntax for type timestamp with time zone: Sat Mar
11 23:58:48 1 IST
LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 1 IST');
** **
here error coming from timestamptz_in() - datefields_to_timestamp() -**
**
DecodeDateTime() stack.
** **
Looking more at the DecodeDateTime() function, here error coming while
trying
to Decode year field which is 1 in the our test. For year field
ftype is
DTK_NUMBER, and under DTK_NUMBER for this case if drop in to following
condition:
** **
else if (flen 4)
{
dterr = DecodeNumberField(flen, field[i], fmask,
tmask, tm,
fsec, is2digits);
if (dterr 0)
return dterr;
}
** **
because flen in out case flen is 5 (1).
** **
As per the comment above DecodeNumberField(), it interpret numeric
string as a
concatenated date or time field. So ideally we should be into
DecodeNumberField
function only with (fmask DTK_DATE_M) == 0 or (fmask DTK_TIME_M) ==
0,
right ??
** **
So, I tried the same and after that test working fine.
** **
PFA patch and share your input/suggestions.
** **
Patch applies cleanly to HEAD. As this patch tries to improve in inserting
the date of the year value to be more than 4 in length.
But it didn’t solve all the ways to insert the year field more than 4 in
length. Please check the following test.
** **
** **
postgres=# insert into test values ('10001010 10:10:10 IST');
INSERT 0 1
postgres=# insert into test values ('100011010 10:10:10 IST');
ERROR: invalid input syntax for type timestamp with time zone: 100011010
10:10:10 IST at character 26
STATEMENT: insert into test values ('100011010 10:10:10 IST');
ERROR: invalid input syntax for type timestamp with time zone: 100011010
10:10:10 IST
LINE 1: insert into test values ('100011010 10:10:10 IST');
^
** **
I feel it is better to provide the functionality of inserting year field
more than 4 in length in all flows.
+1. Nice catch.
Here is the latest version of patch which handles the functionality in all
flows.
Could you test it and share you comments.
Thanks,
Rushabh Lathia
www.EnterpriseDB.com
timestamptz_fix_with_testcase_v2.patch
Description: Binary data
--
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] insert throw error when year field len 4 for timestamptz datatype
On Tue, 17 September 2013 14:33 Rushabh Lathia wrote:
On Mon, Sep 16, 2013 at 7:22 PM, Haribabu kommi
haribabu.ko...@huawei.commailto:haribabu.ko...@huawei.com wrote:
On 14 August 2013 Rushabh Lathia wrote:
postgres=# create table test ( a timestamptz);
CREATE TABLE
-- Date with year 1000
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');
INSERT 0 1
-- Now try with year 1 it will return error
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1 IST');
ERROR: invalid input syntax for type timestamp with time zone: Sat Mar 11
23:58:48 1 IST
LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 1 IST');
Patch applies cleanly to HEAD. As this patch tries to improve in inserting
the date of the year value to be more than 4 in length.
But it didn't solve all the ways to insert the year field more than 4 in
length. Please check the following test.
postgres=# insert into test values ('10001010 10:10:10 IST');
INSERT 0 1
postgres=# insert into test values ('100011010 10:10:10 IST');
ERROR: invalid input syntax for type timestamp with time zone: 100011010
10:10:10 IST at character 26
STATEMENT: insert into test values ('100011010 10:10:10 IST');
ERROR: invalid input syntax for type timestamp with time zone: 100011010
10:10:10 IST
LINE 1: insert into test values ('100011010 10:10:10 IST');
^
I feel it is better to provide the functionality of inserting year field
more than 4 in length in all flows.
+1. Nice catch.
Here is the latest version of patch which handles the functionality in all
flows.
Could you test it and share you comments.
I am getting some other failures with the updated patch also, please check the
following tests.
select date 'January 8, 19990';
select timestamptz 'January 8, 199910 01:01:01 IST';
INSERT INTO TIMESTAMPTZ_TST VALUES(4, '10001 SAT 8 MAR 10:10:10 IST');
you can get the test scripts from regress test files of date.sql, timetz.sql,
timestamp.sql and timestamptz.sql
and modify according to the patch for verification.
I feel changing the year value to accept the length (4) is not simple.
So many places the year length crossing more than length 4 is not considered.
Search in the code with and correct all related paths.
Regards,
Hari babu.
Re: [HACKERS] insert throw error when year field len 4 for timestamptz datatype
On 14 August 2013 Rushabh Lathia wrote:
postgres=# create table test ( a timestamptz);
CREATE TABLE
-- Date with year 1000
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');
INSERT 0 1
-- Now try with year 1 it will return error
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1 IST');
ERROR: invalid input syntax for type timestamp with time zone: Sat Mar 11
23:58:48 1 IST
LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 1 IST');
here error coming from timestamptz_in() - datefields_to_timestamp() -
DecodeDateTime() stack.
Looking more at the DecodeDateTime() function, here error coming while trying
to Decode year field which is 1 in the our test. For year field ftype is
DTK_NUMBER, and under DTK_NUMBER for this case if drop in to following
condition:
else if (flen 4)
{
dterr = DecodeNumberField(flen, field[i], fmask,
tmask, tm,
fsec, is2digits);
if (dterr 0)
return dterr;
}
because flen in out case flen is 5 (1).
As per the comment above DecodeNumberField(), it interpret numeric string as a
concatenated date or time field. So ideally we should be into DecodeNumberField
function only with (fmask DTK_DATE_M) == 0 or (fmask DTK_TIME_M) == 0,
right ??
So, I tried the same and after that test working fine.
PFA patch and share your input/suggestions.
Patch applies cleanly to HEAD. As this patch tries to improve in inserting the
date of the year value to be more than 4 in length.
But it didn't solve all the ways to insert the year field more than 4 in
length. Please check the following test.
postgres=# insert into test values ('10001010 10:10:10 IST');
INSERT 0 1
postgres=# insert into test values ('100011010 10:10:10 IST');
ERROR: invalid input syntax for type timestamp with time zone: 100011010
10:10:10 IST at character 26
STATEMENT: insert into test values ('100011010 10:10:10 IST');
ERROR: invalid input syntax for type timestamp with time zone: 100011010
10:10:10 IST
LINE 1: insert into test values ('100011010 10:10:10 IST');
^
I feel it is better to provide the functionality of inserting year field more
than 4 in length in all flows.
Regards,
Hari babu.
Re: [HACKERS] insert throw error when year field len 4 for timestamptz datatype
On Thu, Aug 15, 2013 at 1:08 AM, Tom Lane t...@sss.pgh.pa.us wrote: Rushabh Lathia rushabh.lat...@gmail.com writes: PFA patch and share your input/suggestions. I think this needs review. Please add it to the next commitfest. Done. Here is latest patch with testcase added to regression. regards, tom lane Regards, Rushabh Lathia www.EnterpriseDB.com timestamptz_fix_with_testcase.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] insert throw error when year field len 4 for timestamptz datatype
Hi,
While working on something I come across this issue. Consider following
test:
postgres=# select version();
version
-
PostgreSQL 9.4devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
(1 row)
postgres=# create table test ( a timestamptz);
CREATE TABLE
-- Date with year 1000
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');
INSERT 0 1
-- Now try with year 1 it will return error
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1 IST');
ERROR: invalid input syntax for type timestamp with time zone: Sat Mar 11
23:58:48 1 IST
LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 1 IST');
here error coming from timestamptz_in() - datefields_to_timestamp() -
DecodeDateTime() stack.
Looking more at the DecodeDateTime() function, here error coming while
trying
to Decode year field which is 1 in the our test. For year field ftype is
DTK_NUMBER, and under DTK_NUMBER for this case if drop in to following
condition:
else if (flen 4)
{
dterr = DecodeNumberField(flen, field[i], fmask,
tmask, tm,
fsec, is2digits);
if (dterr 0)
return dterr;
}
because flen in out case flen is 5 (1).
As per the comment above DecodeNumberField(), it interpret numeric string
as a
concatenated date or time field. So ideally we should be into
DecodeNumberField
function only with (fmask DTK_DATE_M) == 0 or (fmask DTK_TIME_M) == 0,
right ??
So, I tried the same and after that test working fine.
Another fix could be to modify DecodeNumberField() to only check for the
date and time when (fmask DTK_DATE_M) == 0 and (fmask DTK_TIME_M) == 0.
And if DecodeNumberField() returns error then call DecodeNumber() to check
the year possibility. But I didn't
Results after fix:
postgres=# select * from test;
a
--
1000-03-12 03:52:16+05:53:28
1-03-12 03:28:48+05:30
(2 rows)
PFA patch and share your input/suggestions.
(With patch make check running fine without additional failures)
Regards,
Rushabh Lathia
www.EnterpriseDB.com
timestamptz_fix.patch
Description: Binary data
--
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] insert throw error when year field len 4 for timestamptz datatype
Rushabh Lathia rushabh.lat...@gmail.com writes: PFA patch and share your input/suggestions. I think this needs review. Please add it to the next commitfest. 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
