Re: [HACKERS] insert throw error when year field len 4 for timestamptz datatype

2013-10-16 Thread Bruce Momjian
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

2013-10-08 Thread Rushabh Lathia
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

2013-10-08 Thread Bruce Momjian
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

2013-10-07 Thread Robert Haas
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

2013-10-06 Thread Rushabh Lathia
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

2013-10-04 Thread Haribabu kommi

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

2013-10-04 Thread Bruce Momjian
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

2013-10-03 Thread Rushabh Lathia
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

2013-10-03 Thread Bruce Momjian
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

2013-10-02 Thread Robert Haas
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

2013-10-02 Thread Bruce Momjian
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

2013-10-01 Thread Bruce Momjian
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

2013-09-27 Thread Rushabh Lathia
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

2013-09-27 Thread Haribabu kommi
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

2013-09-17 Thread Rushabh Lathia
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

2013-09-17 Thread Haribabu kommi
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

2013-09-16 Thread Haribabu kommi
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

2013-08-16 Thread Rushabh Lathia
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


Re: [HACKERS] insert throw error when year field len 4 for timestamptz datatype

2013-08-14 Thread Tom Lane
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