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