Re: Fwd: [HACKERS] patch: make_timestamp function

2014-03-06 Thread Robert Haas
On Thu, Mar 6, 2014 at 1:26 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
 2014-03-05 16:22 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:

 Pavel Stehule escribió:
  Hi
 
  I hope, so this patch fix it

 wtf?


 I tried to fix
 http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f1ba94bcd9717b94b36868d6905547e313f3a359

 Tom did it better than me.

The patch you attached was one from Heikki, not anything you wrote for
yourself, and utterly unrelated to the topic of this thread.

-- 
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: Fwd: [HACKERS] patch: make_timestamp function

2014-03-06 Thread Pavel Stehule
2014-03-06 21:06 GMT+01:00 Robert Haas robertmh...@gmail.com:

 On Thu, Mar 6, 2014 at 1:26 AM, Pavel Stehule pavel.steh...@gmail.com
 wrote:
  2014-03-05 16:22 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:
 
  Pavel Stehule escribió:
   Hi
  
   I hope, so this patch fix it
 
  wtf?
 
 
  I tried to fix
 
 http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f1ba94bcd9717b94b36868d6905547e313f3a359
 
  Tom did it better than me.

 The patch you attached was one from Heikki, not anything you wrote for
 yourself, and utterly unrelated to the topic of this thread.


yes, sorry - it is some git issue on my side (I had to use  wrong hash). I
did changes similar to Tom fix, but patch was some other than I did.

Regards

Pavel





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



Re: Fwd: [HACKERS] patch: make_timestamp function

2014-03-05 Thread Pavel Stehule
Hi

I hope, so this patch fix it

Regards

Pavel


2014-03-04 21:00 GMT+01:00 Pavel Stehule pavel.steh...@gmail.com:




 2014-03-04 20:20 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:

 Pavel Stehule escribió:
  2014-03-04 19:12 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:
 
   Pavel Stehule escribió:
Hello
   
updated version - a precheck is very simple, and I what I tested it
 is
enough
  
   Okay, thanks.  I pushed it after some more editorialization.  I don't
   think I broke anything, but please have a look.
 
  It looks well

 Coypu is showing a strange failure though:


 http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=coypudt=2014-03-04%2018%3A22%3A31
   select make_interval(secs := 'inf');
 ! make_interval
 ! -
 !  @ 0.01 secs ago
 ! (1 row)

 I realize that we have some hacks in float4in and float8in to deal with
 these portability issues ...  Maybe the fix is just take out the test.


 I have no idea, how to fix it now and have to leave a office. Tomorrow
 I'll try to fix it.

 Regards

 Pavel



 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services



commit 956685f82b6983ff17e6a39bd386b11f554715a8
Author: Heikki Linnakangas heikki.linnakan...@iki.fi
Date:   Wed Mar 5 14:41:55 2014 +0200

Do wal_level and hot standby checks when doing crash-then-archive recovery.

CheckRequiredParameterValues() should perform the checks if archive recovery
was requested, even if we are going to perform crash recovery first.

Reported by Kyotaro HORIGUCHI. Backpatch to 9.2, like the crash-then-archive
recovery mode.

diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index e3d5e10..cdbe305 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -6187,7 +6187,7 @@ CheckRequiredParameterValues(void)
 	 * For archive recovery, the WAL must be generated with at least 'archive'
 	 * wal_level.
 	 */
-	if (InArchiveRecovery  ControlFile-wal_level == WAL_LEVEL_MINIMAL)
+	if (ArchiveRecoveryRequested  ControlFile-wal_level == WAL_LEVEL_MINIMAL)
 	{
 		ereport(WARNING,
 (errmsg(WAL was generated with wal_level=minimal, data may be missing),
@@ -6198,7 +6198,7 @@ CheckRequiredParameterValues(void)
 	 * For Hot Standby, the WAL must be generated with 'hot_standby' mode, and
 	 * we must have at least as many backend slots as the primary.
 	 */
-	if (InArchiveRecovery  EnableHotStandby)
+	if (ArchiveRecoveryRequested  EnableHotStandby)
 	{
 		if (ControlFile-wal_level  WAL_LEVEL_HOT_STANDBY)
 			ereport(ERROR,

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: Fwd: [HACKERS] patch: make_timestamp function

2014-03-05 Thread Alvaro Herrera
Pavel Stehule escribió:
 Hi
 
 I hope, so this patch fix it

wtf?

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: Fwd: [HACKERS] patch: make_timestamp function

2014-03-05 Thread Pavel Stehule
2014-03-05 16:22 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:

 Pavel Stehule escribió:
  Hi
 
  I hope, so this patch fix it

 wtf?


I tried to fix
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f1ba94bcd9717b94b36868d6905547e313f3a359

Tom did it better than me.

Regards

Pavel



 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services



Re: Fwd: [HACKERS] patch: make_timestamp function

2014-03-04 Thread Alvaro Herrera
Pavel Stehule escribió:
 Hello
 
 updated version - a precheck is very simple, and I what I tested it is
 enough

Okay, thanks.  I pushed it after some more editorialization.  I don't
think I broke anything, but please have a look.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: Fwd: [HACKERS] patch: make_timestamp function

2014-03-04 Thread Pavel Stehule
2014-03-04 19:12 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:

 Pavel Stehule escribió:
  Hello
 
  updated version - a precheck is very simple, and I what I tested it is
  enough

 Okay, thanks.  I pushed it after some more editorialization.  I don't
 think I broke anything, but please have a look.


It looks well

Thank you very much

Pavel



 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services



Re: Fwd: [HACKERS] patch: make_timestamp function

2014-03-04 Thread Alvaro Herrera
Pavel Stehule escribió:
 2014-03-04 19:12 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:
 
  Pavel Stehule escribió:
   Hello
  
   updated version - a precheck is very simple, and I what I tested it is
   enough
 
  Okay, thanks.  I pushed it after some more editorialization.  I don't
  think I broke anything, but please have a look.
 
 It looks well

Coypu is showing a strange failure though:

http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=coypudt=2014-03-04%2018%3A22%3A31
  select make_interval(secs := 'inf');
! make_interval
! -
!  @ 0.01 secs ago
! (1 row)

I realize that we have some hacks in float4in and float8in to deal with
these portability issues ...  Maybe the fix is just take out the test.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: Fwd: [HACKERS] patch: make_timestamp function

2014-03-04 Thread Pavel Stehule
2014-03-04 20:20 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:

 Pavel Stehule escribió:
  2014-03-04 19:12 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:
 
   Pavel Stehule escribió:
Hello
   
updated version - a precheck is very simple, and I what I tested it
 is
enough
  
   Okay, thanks.  I pushed it after some more editorialization.  I don't
   think I broke anything, but please have a look.
 
  It looks well

 Coypu is showing a strange failure though:


 http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=coypudt=2014-03-04%2018%3A22%3A31
   select make_interval(secs := 'inf');
 ! make_interval
 ! -
 !  @ 0.01 secs ago
 ! (1 row)

 I realize that we have some hacks in float4in and float8in to deal with
 these portability issues ...  Maybe the fix is just take out the test.


I have no idea, how to fix it now and have to leave a office. Tomorrow I'll
try to fix it.

Regards

Pavel



 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services



Re: Fwd: [HACKERS] patch: make_timestamp function

2014-03-02 Thread Pavel Stehule
Hello

updated version - a precheck is very simple, and I what I tested it is
enough

Regards

Pavel




2014-02-28 15:11 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:

 Pavel Stehule escribió:

  so still I prefer to allow numeric time zones.
 
  What I can:
 
  a) disallow numeric only timezone without prefix + or -
 
  or
 
  b) add + prefix to time zone, when number is possitive.
 
  I prefer @a.

 I can live with (a) too.  But I wonder if we should restrict the allowed
 tz even further, for example to say that there must always be either 2
 digits (no colon) or 4 digits, with or without a colon.

 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services

commit a9acac784c64fdba144ea0ae3a817fbc8cf4fa45
Author: Pavel Stehule pavel.steh...@gooddata.com
Date:   Sun Mar 2 10:55:37 2014 +0100

fix make_timestamptz

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index ff50328..ce6d00e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6723,6 +6723,32 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
row
 entry
  indexterm
+  primarymake_interval/primary
+ /indexterm
+ literal
+  function
+   make_interval(parameteryears/parameter typeint/type DEFAULT 0,
+   parametermonths/parameter typeint/type DEFAULT 0,
+   parameterweeks/parameter typeint/type DEFAULT 0,
+   parameterdays/parameter typeint/type DEFAULT 0,
+   parameterhours/parameter typeint/type DEFAULT 0,
+   parametermins/parameter typeint/type DEFAULT 0,
+   parametersecs/parameter typedouble precision/type DEFAULT 0.0)
+  /function
+ /literal
+/entry
+entrytypeinterval/type/entry
+entry
+ Create interval from years, months, weeks, days, hours, minutes and
+ seconds fields
+/entry
+entryliteralmake_interval(days := 10)/literal/entry
+entryliteral10 days/literal/entry
+   /row
+
+   row
+entry
+ indexterm
   primarymake_time/primary
  /indexterm
  literal
@@ -6744,6 +6770,57 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
row
 entry
  indexterm
+  primarymake_timestamp/primary
+ /indexterm
+ literal
+  function
+   make_timestamp(parameteryear/parameter typeint/type,
+   parametermonth/parameter typeint/type,
+   parameterday/parameter typeint/type,
+   parameterhour/parameter typeint/type,
+   parametermin/parameter typeint/type,
+   parametersec/parameter typedouble precision/type)
+  /function
+ /literal
+/entry
+entrytypetimestamp/type/entry
+entry
+ Create timestamp from year, month, day, hour, minute and seconds fields
+/entry
+entryliteralmake_timestamp(1-23, 7, 15, 8, 15, 23.5)/literal/entry
+entryliteral2013-07-15 08:15:23.5/literal/entry
+   /row
+
+   row
+entry
+ indexterm
+  primarymake_timestamptz/primary
+ /indexterm
+ literal
+  function
+   make_timestamptz(parameteryear/parameter typeint/type,
+   parametermonth/parameter typeint/type,
+   parameterday/parameter typeint/type,
+   parameterhour/parameter typeint/type,
+   parametermin/parameter typeint/type,
+   parametersec/parameter typedouble precision/type,
+   optional parametertimezone/parameter typetext/type /optional)
+  /function
+ /literal
+/entry
+entrytypetimestamp with time zone/type/entry
+entry
+ Create timestamp with time zone from year, month, day, hour, minute
+ and seconds fields. When parametertimezone/parameter is not specified,
+ then current time zone is used.
+/entry
+entryliteralmake_timestamp(1-23, 7, 15, 8, 15, 23.5)/literal/entry
+entryliteral2013-07-15 08:15:23.5+01/literal/entry
+   /row
+
+   row
+entry
+ indexterm
   primarynow/primary
  /indexterm
  literalfunctionnow()/function/literal
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 04dfbb0..59a6f85 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -822,3 +822,9 @@ CREATE OR REPLACE FUNCTION
 CREATE OR REPLACE FUNCTION
   json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
   RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100  AS 'json_populate_recordset';
+
+CREATE OR REPLACE FUNCTION
+  make_interval(years int4 DEFAULT 0, months int4 DEFAULT 0, weeks int4 DEFAULT 0,
+days int4 DEFAULT 0, hours int4 DEFAULT 0, mins int4 DEFAULT 0,
+secs double precision DEFAULT 0.0)
+ 

Re: Fwd: [HACKERS] patch: make_timestamp function

2014-02-28 Thread Pavel Stehule
2014-02-27 20:10 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:

 Pavel Stehule escribió:
  Hello
 
  updated patch without timetz support

 Great, thanks.

 While testing, I noticed something strange regarding numeric timezone
 specification.  Basically the way any particular value is handled is
 underspecified, or maybe just completely wacko.  Consider the attached
 function, which will try to construct a timestamptz value with all
 possible values for timezone in the -1000 to 1000 range, ignoring those
 that cause errors for whatever reason, and then subtract the obtained
 timestamptz from the base value.  The output is also attached.

 First of all you can see that there are plenty of values for which the
 constructor will simply fail.

 Second, the way signs are considered or not seems arbitrary.  Note that
 if you say either '-2' or '2', you will end up with the same timestamptz
 value.  But at -16 the value jumps to the opposite sign.

 For negative values, this continues up to -99; but at -100, apparently
 it stops considering the value a number of hours, and it considers
 hours-and-minutes with a missing colon separator.  Which works up to
 -159; at -160 and up to -167 it uses a different interpretation again
 (not sure what).  Then values -168 and below are not valid; -200 is
 valid again (2 hours)  For the rest of the interval,

 For positive values, apparently there's no funny interpretation; the
 number is taken to be a number of hours up to 167.  There's no valid
 value above that.  However, if you prepend a plus sign, the result is
 completely different and there are valid values up to +1559.  The funny
 behavior in +160 through +167 is there too.

 Not sure what to make of this; certainly it's not my interest to fix it.
 However I wonder if we should really offer the capability to pass
 numeric timezone values.  Seems it'd be saner to allow just symbolic
 names, either abbreviations or full names.


I found a small issue. Routines for parsing time zone expects so time zone
starts with '+' or '-'. When this symbol is missing, then it use '-' as
default.

That is pretty stupid - probably it expects check in preprocessing

postgres=# select make_timestamptz(2014, 12, 10, 10, 10, 10, '-1');
make_timestamptz

 2014-12-10 12:10:10+01
(1 row)

postgres=# select make_timestamptz(2014, 12, 10, 10, 10, 10, '1');
make_timestamptz

 2014-12-10 12:10:10+01
(1 row)

postgres=# select make_timestamptz(2014, 12, 10, 10, 10, 10, '+1');
make_timestamptz

 2014-12-10 10:10:10+01
(1 row)

When I fix this, then make_timestamptz produce same results as timestamptz
input function.

CREATE OR REPLACE FUNCTION public.tryt1(integer)
 RETURNS TABLE (tz int, tm01 timestamptz, tm02 timestamptz, diff interval)
 LANGUAGE plpgsql
AS $function$
declare
tz int;
begin
for tz in - $1 .. $1 loop
begin
tryt1.tz = tz;
tm01 := format('1987-02-14 12:25:00 %s%s', CASE WHEN tz  0 THEN
'+' ELSE '' END, tz)::timestamptz;
tm02 := make_timestamptz(1987, 2, 14, 12, 25, 00, CASE WHEN tz  0
THEN '+' ELSE '' END || tz::text);
diff := tm02 - tm01;
return next;
exception when others then null;
  raise notice 'error %s: %', SQLERRM, tz;
end;
end loop;
end;
$function$;

A allowed (or disallowed) numeric zones are little bit strange - but it is
different issue not related to this patch.

so still I prefer to allow numeric time zones.

What I can:

a) disallow numeric only timezone without prefix + or -

or

b) add + prefix to time zone, when number is possitive.


I prefer @a.

What do you thinking?

Regards

Pavel



 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services



Re: Fwd: [HACKERS] patch: make_timestamp function

2014-02-28 Thread Alvaro Herrera
Pavel Stehule escribió:

 so still I prefer to allow numeric time zones.
 
 What I can:
 
 a) disallow numeric only timezone without prefix + or -
 
 or
 
 b) add + prefix to time zone, when number is possitive.
 
 I prefer @a.

I can live with (a) too.  But I wonder if we should restrict the allowed
tz even further, for example to say that there must always be either 2
digits (no colon) or 4 digits, with or without a colon.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: Fwd: [HACKERS] patch: make_timestamp function

2014-02-20 Thread Pavel Stehule
Hello

updated patch without timetz support

Regards

Pavel


2014-02-19 21:20 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:

 Pavel Stehule escribió:

  I though about it, and now I am thinking so timezone in format
  'Europe/Prague' is together with time ambiguous
 
  We can do it, but we have to expect so calculation will be related to
  current date - and I am not sure if it is correct, because someone can
  write some like
 
  make_date(x,x,x) + make_timetz(..) - and result will be damaged.

 Hmm, I see your point --- the make_timetz() call would use today's
 timezone displacement, which might be different from the one used in the
 make_date() result.  That would result in a botched timestamptz
 sometimes, but it might escape testing because it's subtle and depends
 on the input data.

 However, your proposal is to use an abbreviation timezone, thereby
 forcing the user to select the correct timezone i.e. the one that
 matches the make_date() arguments.  I'm not sure this is much of an
 improvement, because then the user is faced with the difficult problem
 of figuring out the correct abbreviation in the first place.

 I think there is little we can do to solve the problem at this level; it
 seems to me that the right solution here is to instruct users to use
 make_date() only in conjunction with make_time(), that is, produce a
 timezone-less timestamp; and then apply a AT TIME ZONE operator to the
 result.  That could take a full timezone name, and that would always
 work correctly.

 My conclusion here is that the time with time zone datatype is broken
 in itself, because of this kind of ambiguity.  Maybe we should just
 avoid offering more functionality on top of it, that is get rid of
 make_timetz() in this patch?

 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services

commit 49ef9ee84560c8fe8ea4d98704df2197fd8a1546
Author: Pavel Stehule pavel.steh...@gooddata.com
Date:   Thu Feb 20 09:15:49 2014 +0100

timetz removed

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index be548d7..bd3bbdb 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6725,6 +6725,32 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
row
 entry
  indexterm
+  primarymake_interval/primary
+ /indexterm
+ literal
+  function
+   make_interval(parameteryears/parameter typeint/type DEFAULT 0,
+   parametermonths/parameter typeint/type DEFAULT 0,
+   parameterweeks/parameter typeint/type DEFAULT 0,
+   parameterdays/parameter typeint/type DEFAULT 0,
+   parameterhours/parameter typeint/type DEFAULT 0,
+   parametermins/parameter typeint/type DEFAULT 0,
+   parametersecs/parameter typedouble precision/type DEFAULT 0.0)
+  /function
+ /literal
+/entry
+entrytypeinterval/type/entry
+entry
+ Create interval from years, months, weeks, days, hours, minutes and
+ seconds fields
+/entry
+entryliteralmake_interval(days := 10)/literal/entry
+entryliteral10 days/literal/entry
+   /row
+
+   row
+entry
+ indexterm
   primarymake_time/primary
  /indexterm
  literal
@@ -6746,6 +6772,57 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
row
 entry
  indexterm
+  primarymake_timestamp/primary
+ /indexterm
+ literal
+  function
+   make_timestamp(parameteryear/parameter typeint/type,
+   parametermonth/parameter typeint/type,
+   parameterday/parameter typeint/type,
+   parameterhour/parameter typeint/type,
+   parametermin/parameter typeint/type,
+   parametersec/parameter typedouble precision/type)
+  /function
+ /literal
+/entry
+entrytypetimestamp/type/entry
+entry
+ Create timestamp from year, month, day, hour, minute and seconds fields
+/entry
+entryliteralmake_timestamp(1-23, 7, 15, 8, 15, 23.5)/literal/entry
+entryliteral2013-07-15 08:15:23.5/literal/entry
+   /row
+
+   row
+entry
+ indexterm
+  primarymake_timestamptz/primary
+ /indexterm
+ literal
+  function
+   make_timestamptz(parameteryear/parameter typeint/type,
+   parametermonth/parameter typeint/type,
+   parameterday/parameter typeint/type,
+   parameterhour/parameter typeint/type,
+   parametermin/parameter typeint/type,
+   parametersec/parameter typedouble precision/type,
+   optional parametertimezone/parameter typetext/type /optional)
+  /function
+ /literal
+/entry
+entrytypetimestamp with time zone/type/entry
+entry
+ Create timestamp with time zone from year, month, day, hour, minute
+ and 

Re: Fwd: [HACKERS] patch: make_timestamp function

2014-02-19 Thread Alvaro Herrera
Pavel Stehule escribió:

  7) Why do the functions accept only the timezone abbreviation, not the
 full name? I find it rather confusing, because the 'timezone' option
 uses the full name, and we're using this as the default. But doing
 'show timestamp' and using the returned value fails. Is it possible
 to fix this somehow?
 
 A only abbreviation is allowed for timetz type. Timestamp can work with
 full time zone names. A rules (behave) should be same as input functions
 for types: timestamptz and timetz.
 
 postgres=# select '10:10:10 CET'::timetz;
timetz
 ─
  10:10:10+01
 (1 row)
 
 postgres=# select '10:10:10 Europe/Prague'::timetz;
 ERROR:  invalid input syntax for type time with time zone: 10:10:10
 Europe/Prague
 LINE 1: select '10:10:10 Europe/Prague'::timetz;
^
 
 This limit is due used routines limits.

I think this is a strange limitation, and perhaps it should be fixed
rather than inflicting the limitation on the new function.

I tweaked your patch a bit, attached; other than defining what to do
about full TZ names in timetz, this seems ready to commit.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index be548d7..69eb45f 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6725,6 +6725,32 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
row
 entry
  indexterm
+  primarymake_interval/primary
+ /indexterm
+ literal
+  function
+   make_interval(parameteryears/parameter typeint/type DEFAULT 0,
+   parametermonths/parameter typeint/type DEFAULT 0,
+   parameterweeks/parameter typeint/type DEFAULT 0,
+   parameterdays/parameter typeint/type DEFAULT 0,
+   parameterhours/parameter typeint/type DEFAULT 0,
+   parametermins/parameter typeint/type DEFAULT 0,
+   parametersecs/parameter typedouble precision/type DEFAULT 0.0)
+  /function
+ /literal
+/entry
+entrytypeinterval/type/entry
+entry
+ Create interval from years, months, weeks, days, hours, minutes and
+ seconds fields
+/entry
+entryliteralmake_interval(days := 10)/literal/entry
+entryliteral10 days/literal/entry
+   /row
+
+   row
+entry
+ indexterm
   primarymake_time/primary
  /indexterm
  literal
@@ -6746,6 +6772,81 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
row
 entry
  indexterm
+  primarymake_timetz/primary
+ /indexterm
+ literal
+  function
+   make_timetz(parameterhour/parameter typeint/type,
+   parametermin/parameter typeint/type,
+   parametersec/parameter typedouble precision/type,
+   optional parametertimezone/parameter typetext/type /optional)
+  /function
+ /literal
+/entry
+entrytypetime with time zone/type/entry
+entry
+ Create time with time zone from hour, minute and seconds fields. When
+ parametertimezone/parameter is not specified, then current time zone
+ is used.
+/entry
+entryliteralmake_timetz(8, 15, 23.5)/literal/entry
+entryliteral08:15:23.5+01/literal/entry
+   /row
+
+   row
+entry
+ indexterm
+  primarymake_timestamp/primary
+ /indexterm
+ literal
+  function
+   make_timestamp(parameteryear/parameter typeint/type,
+   parametermonth/parameter typeint/type,
+   parameterday/parameter typeint/type,
+   parameterhour/parameter typeint/type,
+   parametermin/parameter typeint/type,
+   parametersec/parameter typedouble precision/type)
+  /function
+ /literal
+/entry
+entrytypetimestamp/type/entry
+entry
+ Create timestamp from year, month, day, hour, minute and seconds fields
+/entry
+entryliteralmake_timestamp(1-23, 7, 15, 8, 15, 23.5)/literal/entry
+entryliteral2013-07-15 08:15:23.5/literal/entry
+   /row
+
+   row
+entry
+ indexterm
+  primarymake_timestamptz/primary
+ /indexterm
+ literal
+  function
+   make_timestamptz(parameteryear/parameter typeint/type,
+   parametermonth/parameter typeint/type,
+   parameterday/parameter typeint/type,
+   parameterhour/parameter typeint/type,
+   parametermin/parameter typeint/type,
+   parametersec/parameter typedouble precision/type,
+   optional parametertimezone/parameter typetext/type /optional)
+  /function
+ /literal
+/entry
+entrytypetimestamp with time zone/type/entry
+entry
+ Create timestamp with time zone from year, 

Re: Fwd: [HACKERS] patch: make_timestamp function

2014-02-19 Thread Pavel Stehule
2014-02-19 19:01 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:

 Pavel Stehule escribió:

   7) Why do the functions accept only the timezone abbreviation, not the
  full name? I find it rather confusing, because the 'timezone' option
  uses the full name, and we're using this as the default. But doing
  'show timestamp' and using the returned value fails. Is it possible
  to fix this somehow?
 
  A only abbreviation is allowed for timetz type. Timestamp can work with
  full time zone names. A rules (behave) should be same as input functions
  for types: timestamptz and timetz.
 
  postgres=# select '10:10:10 CET'::timetz;
 timetz
  ─
   10:10:10+01
  (1 row)
 
  postgres=# select '10:10:10 Europe/Prague'::timetz;
  ERROR:  invalid input syntax for type time with time zone: 10:10:10
  Europe/Prague
  LINE 1: select '10:10:10 Europe/Prague'::timetz;
 ^
 
  This limit is due used routines limits.

 I think this is a strange limitation, and perhaps it should be fixed
 rather than inflicting the limitation on the new function.

 I tweaked your patch a bit, attached; other than defining what to do
 about full TZ names in timetz, this seems ready to commit.


I have not a objection - thank you

Pavel



 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services



Re: Fwd: [HACKERS] patch: make_timestamp function

2014-02-19 Thread Pavel Stehule
2014-02-19 19:01 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:

 Pavel Stehule escribió:

   7) Why do the functions accept only the timezone abbreviation, not the
  full name? I find it rather confusing, because the 'timezone' option
  uses the full name, and we're using this as the default. But doing
  'show timestamp' and using the returned value fails. Is it possible
  to fix this somehow?
 
  A only abbreviation is allowed for timetz type. Timestamp can work with
  full time zone names. A rules (behave) should be same as input functions
  for types: timestamptz and timetz.
 
  postgres=# select '10:10:10 CET'::timetz;
 timetz
  ─
   10:10:10+01
  (1 row)
 
  postgres=# select '10:10:10 Europe/Prague'::timetz;
  ERROR:  invalid input syntax for type time with time zone: 10:10:10
  Europe/Prague
  LINE 1: select '10:10:10 Europe/Prague'::timetz;
 ^
 
  This limit is due used routines limits.

 I think this is a strange limitation, and perhaps it should be fixed
 rather than inflicting the limitation on the new function.


I though about it, and now I am thinking so timezone in format
'Europe/Prague' is together with time ambiguous

We can do it, but we have to expect so calculation will be related to
current date - and I am not sure if it is correct, because someone can
write some like

make_date(x,x,x) + make_timetz(..) - and result will be damaged.




 I tweaked your patch a bit, attached; other than defining what to do
 about full TZ names in timetz, this seems ready to commit.

 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services



Re: Fwd: [HACKERS] patch: make_timestamp function

2014-02-19 Thread Alvaro Herrera
Pavel Stehule escribió:

 I though about it, and now I am thinking so timezone in format
 'Europe/Prague' is together with time ambiguous
 
 We can do it, but we have to expect so calculation will be related to
 current date - and I am not sure if it is correct, because someone can
 write some like
 
 make_date(x,x,x) + make_timetz(..) - and result will be damaged.

Hmm, I see your point --- the make_timetz() call would use today's
timezone displacement, which might be different from the one used in the
make_date() result.  That would result in a botched timestamptz
sometimes, but it might escape testing because it's subtle and depends
on the input data.

However, your proposal is to use an abbreviation timezone, thereby
forcing the user to select the correct timezone i.e. the one that
matches the make_date() arguments.  I'm not sure this is much of an
improvement, because then the user is faced with the difficult problem
of figuring out the correct abbreviation in the first place.

I think there is little we can do to solve the problem at this level; it
seems to me that the right solution here is to instruct users to use
make_date() only in conjunction with make_time(), that is, produce a
timezone-less timestamp; and then apply a AT TIME ZONE operator to the
result.  That could take a full timezone name, and that would always
work correctly.

My conclusion here is that the time with time zone datatype is broken
in itself, because of this kind of ambiguity.  Maybe we should just
avoid offering more functionality on top of it, that is get rid of
make_timetz() in this patch?

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: Fwd: [HACKERS] patch: make_timestamp function

2014-02-19 Thread Pavel Stehule
Dne 19. 2. 2014 21:20 Alvaro Herrera alvhe...@2ndquadrant.com napsal(a):

 Pavel Stehule escribió:

  I though about it, and now I am thinking so timezone in format
  'Europe/Prague' is together with time ambiguous
 
  We can do it, but we have to expect so calculation will be related to
  current date - and I am not sure if it is correct, because someone can
  write some like
 
  make_date(x,x,x) + make_timetz(..) - and result will be damaged.

 Hmm, I see your point --- the make_timetz() call would use today's
 timezone displacement, which might be different from the one used in the
 make_date() result.  That would result in a botched timestamptz
 sometimes, but it might escape testing because it's subtle and depends
 on the input data.

 However, your proposal is to use an abbreviation timezone, thereby
 forcing the user to select the correct timezone i.e. the one that
 matches the make_date() arguments.  I'm not sure this is much of an
 improvement, because then the user is faced with the difficult problem
 of figuring out the correct abbreviation in the first place.

 I think there is little we can do to solve the problem at this level; it
 seems to me that the right solution here is to instruct users to use
 make_date() only in conjunction with make_time(), that is, produce a
 timezone-less timestamp; and then apply a AT TIME ZONE operator to the
 result.  That could take a full timezone name, and that would always
 work correctly.

 My conclusion here is that the time with time zone datatype is broken
 in itself, because of this kind of ambiguity.  Maybe we should just
 avoid offering more functionality on top of it, that is get rid of
 make_timetz() in this patch?


+1

Pavel
 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


Re: Fwd: [HACKERS] patch: make_timestamp function

2014-02-19 Thread Tom Lane
Alvaro Herrera alvhe...@2ndquadrant.com writes:
 My conclusion here is that the time with time zone datatype is broken
 in itself, because of this kind of ambiguity.

That's the conclusion that's been arrived at by pretty much everybody
who's looked at it with any care.

 Maybe we should just
 avoid offering more functionality on top of it, that is get rid of
 make_timetz() in this patch?

+1.  We don't need to encourage people to use that type.

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: Fwd: [HACKERS] patch: make_timestamp function

2014-01-25 Thread Marko Tiikkaja

Looks good to me.


Regards,
Marko Tiikkaja


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Fwd: [HACKERS] patch: make_timestamp function

2014-01-12 Thread Pavel Stehule
Hello


2014/1/11 Tomas Vondra t...@fuzzy.cz

 Hi,

 I've done a quick review of this patch:

 1) patch applies fine to the current HEAD, with a few hunks offset
by a few lines

 2) the compilation fails because of duplicate OIDs in pg_proc, so
I had to change 3969-3975 to 4033-4039, then it compiles fine


fixed



 3) make installcheck works fine

 4) No regression tests for make_time / make_date.

 5) The documentation is incomplete - make_date / make_time are missing.


two previous points are done by
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f901bb50e33ad95593bb68f7b3b55eb2e47607dccommits.
This patch try to complete a ToDo entry.



 6) The documentation should mention that when the 'timezone' parameter
is not set explicitly, the current timezone is used.


fixed



 7) Why do the functions accept only the timezone abbreviation, not the
full name? I find it rather confusing, because the 'timezone' option
uses the full name, and we're using this as the default. But doing
'show timestamp' and using the returned value fails. Is it possible
to fix this somehow?


A only abbreviation is allowed for timetz type. Timestamp can work with
full time zone names. A rules (behave) should be same as input functions
for types: timestamptz and timetz.

postgres=# select '10:10:10 CET'::timetz;
   timetz
─
 10:10:10+01
(1 row)

postgres=# select '10:10:10 Europe/Prague'::timetz;
ERROR:  invalid input syntax for type time with time zone: 10:10:10
Europe/Prague
LINE 1: select '10:10:10 Europe/Prague'::timetz;
   ^

This limit is due used routines limits.

postgres=# select make_timestamptz(2014, 12, 10, 10, 10, 10,
'America/Vancouver');
make_timestamptz

 2014-12-10 19:10:10+01
(1 row)

Time: 0.829 ms
postgres=# select '2014-12-10 10:10:10
America/Vancouver'::timestamptz;  timestamptz

 2014-12-10 19:10:10+01
(1 row)

Time: 0.753 ms

I enhanced a regress tests. I found so work with time zones is not strongly
consistent in different use cases. Operator AT TIME ZONE is more tolerant,
but I use a routines used in input functions and my target was consistent
behave (and results) with input functions.

Regards

Pavel





 regards
 Tomas


 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers

commit 0ede2fcd1034a8d34fdfd82105e32a36c287ca6f
Author: Pavel Stehule pavel.steh...@gmail.com
Date:   Sun Jan 12 13:05:06 2014 +0100

enhanced doc and tests

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 8579bdd..13d3d89 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6714,6 +6714,32 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
row
 entry
  indexterm
+  primarymake_interval/primary
+ /indexterm
+ literal
+  function
+   make_interval(parameteryears/parameter typeint/type DEFAULT 0,
+   parametermonths/parameter typeint/type DEFAULT 0,
+   parameterweeks/parameter typeint/type DEFAULT 0,
+   parameterdays/parameter typeint/type DEFAULT 0,
+   parameterhours/parameter typeint/type DEFAULT 0,
+   parametermins/parameter typeint/type DEFAULT 0,
+   parametersecs/parameter typedouble precision/type DEFAULT 0.0)
+  /function
+ /literal
+/entry
+entrytypeinterval/type/entry
+entry
+ Create interval from years, months, weeks, days, hours, minutes and
+ seconds fields
+/entry
+entryliteralmake_interval(days := 10)/literal/entry
+entryliteral10 days/literal/entry
+   /row
+
+   row
+entry
+ indexterm
   primarymake_time/primary
  /indexterm
  literal
@@ -6735,6 +6761,81 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
row
 entry
  indexterm
+  primarymake_timetz/primary
+ /indexterm
+ literal
+  function
+   make_timetz(parameterhour/parameter typeint/type,
+   parametermin/parameter typeint/type,
+   parametersec/parameter typedouble precision/type,
+   optional parametertimezone/parameter typetext/type /optional)
+  /function
+ /literal
+/entry
+entrytypetime with time zone/type/entry
+entry
+ Create time with time zone from hour, minute and seconds fields. When
+ parametertimezone/parameter is not specified, then current time zone
+ is used.
+/entry
+entryliteralmake_timetz(8, 15, 23.5)/literal/entry
+entryliteral08:15:23.5+01/literal/entry
+   /row
+
+   row
+entry
+ indexterm
+  primarymake_timestamp/primary
+ /indexterm
+ literal
+  function
+