Re: Fwd: [HACKERS] patch: make_timestamp function
2014-03-06 21:06 GMT+01:00 Robert Haas : > On Thu, Mar 6, 2014 at 1:26 AM, Pavel Stehule > wrote: > > 2014-03-05 16:22 GMT+01:00 Alvaro Herrera : > > > >> 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
On Thu, Mar 6, 2014 at 1:26 AM, Pavel Stehule wrote: > 2014-03-05 16:22 GMT+01:00 Alvaro Herrera : > >> 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-05 16:22 GMT+01:00 Alvaro Herrera : > 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ó: > 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
Hi
I hope, so this patch fix it
Regards
Pavel
2014-03-04 21:00 GMT+01:00 Pavel Stehule :
>
>
>
> 2014-03-04 20:20 GMT+01:00 Alvaro Herrera :
>
> Pavel Stehule escribió:
>> > 2014-03-04 19:12 GMT+01:00 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.
>> >
>> > It looks well
>>
>> Coypu is showing a strange failure though:
>>
>>
>> http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=coypu&dt=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
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-04 20:20 GMT+01:00 Alvaro Herrera : > Pavel Stehule escribió: > > 2014-03-04 19:12 GMT+01:00 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. > > > > It looks well > > Coypu is showing a strange failure though: > > > http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=coypu&dt=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
Pavel Stehule escribió: > 2014-03-04 19:12 GMT+01:00 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. > > It looks well Coypu is showing a strange failure though: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=coypu&dt=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 19:12 GMT+01:00 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. > 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ó: > 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
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 :
> 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
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})');
+ make_interval
+
+
+
+ make_interval(years int DEFAULT 0,
+ months int DEFAULT 0,
+ weeks int DEFAULT 0,
+ days int DEFAULT 0,
+ hours int DEFAULT 0,
+ mins int DEFAULT 0,
+ secs double precision DEFAULT 0.0)
+
+
+
+interval
+
+ Create interval from years, months, weeks, days, hours, minutes and
+ seconds fields
+
+make_interval(days := 10)
+10 days
+
+
+
+
+
make_time
@@ -6744,6 +6770,57 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
+ make_timestamp
+
+
+
+ make_timestamp(year int,
+ month int,
+ day int,
+ hour int,
+ min int,
+ sec double precision)
+
+
+
+timestamp
+
+ Create timestamp from year, month, day, hour, minute and seconds fields
+
+make_timestamp(1-23, 7, 15, 8, 15, 23.5)
+2013-07-15 08:15:23.5
+
+
+
+
+
+ make_timestamptz
+
+
+
+ make_timestamptz(year int,
+ month int,
+ day int,
+ hour int,
+ min int,
+ sec double precision,
+timezone text )
+
+
+
+timestamp with time zone
+
+ Create timestamp with time zone from year, month, day, hour, minute
+ and seconds fields. When timezone is not specified,
+ then current time zone is used.
+
+make_timestamp(1-23, 7, 15, 8, 15, 23.5)
+2013-07-15 08:15:23.5+01
+
+
+
+
+
now
now()
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)
+ RETURNS interval STRICT IMMUTABLE LANGUAGE internal AS 'make_interval';
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 06cc0cd..404cc79 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -1106,6 +1106,7 @@ time_in(PG_FUNCTION_ARGS)
static int
tm2time(struct pg_tm * tm, fsec_t fsec, TimeADT *result)
{
+ /* this should match make_time_internal and make_timestamp_internal */
#ifdef HAVE_INT64_TIMESTAMP
*result = tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec)
* USECS_PER_SEC) + fsec;
@@ -1244,14 +1245,11 @@ timetypmodout(PG_FUNCTION_ARGS)
}
/*
- * make_time - time constructor
+ * time constructor used for make_time and make_timetz
*/
-Datum
-make_time(PG_FUNCTION_ARGS)
+static TimeADT
+make_time_internal(int tm_hour, int tm_min, double sec)
{
- int tm_hour = PG_GETARG_INT32(0);
- int tm_min = PG_GETARG_INT32(1);
- double sec = PG_GETARG_FLOAT8(2);
TimeADT time;
/* This should match the checks in DecodeTimeOnly */
@@ -1273,9 +1271,24 @@ make_time(PG_FUNCTION_ARGS)
time = ((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE) + sec;
#endif
- PG_RETURN_TIMEADT(time);
+ return time;
}
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
2014-02-27 20:10 GMT+01:00 Alvaro Herrera :
> 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ó: > 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. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services trytz.sql Description: application/sql SELECT * FROM trytz(1000) AS (a int, b timestamptz, c interval); a | b| c ---++--- -1000 | 1987-02-14 19:25:00-03 | 10:00:00 -959 | 1987-02-14 19:24:00-03 | 09:59:00 -958 | 1987-02-14 19:23:00-03 | 09:58:00 -957 | 1987-02-14 19:22:00-03 | 09:57:00 -956 | 1987-02-14 19:21:00-03 | 09:56:00 -955 | 1987-02-14 19:20:00-03 | 09:55:00 -954 | 1987-02-14 19:19:00-03 | 09:54:00 -953 | 1987-02-14 19:18:00-03 | 09:53:00 -952 | 1987-02-14 19:17:00-03 | 09:52:00 -951 | 1987-02-14 19:16:00-03 | 09:51:00 -950 | 1987-02-14 19:15:00-03 | 09:50:00 -949 | 1987-02-14 19:14:00-03 | 09:49:00 -948 | 1987-02-14 19:13:00-03 | 09:48:00 -947 | 1987-02-14 19:12:00-03 | 09:47:00 -946 | 1987-02-14 19:11:00-03 | 09:46:00 -945 | 1987-02-14 19:10:00-03 | 09:45:00 -944 | 1987-02-14 19:09:00-03 | 09:44:00 -943 | 1987-02-14 19:08:00-03 | 09:43:00 -942 | 1987-02-14 19:07:00-03 | 09:42:00 -941 | 1987-02-14 19:06:00-03 | 09:41:00 -940 | 1987-02-14 19:05:00-03 | 09:40:00 -939 | 1987-02-14 19:04:00-03 | 09:39:00 -938 | 1987-02-14 19:03:00-03 | 09:38:00 -937 | 1987-02-14 19:02:00-03 | 09:37:00 -936 | 1987-02-14 19:01:00-03 | 09:36:00 -935 | 1987-02-14 19:00:00-03 | 09:35:00 -934 | 1987-02-14 18:59:00-03 | 09:34:00 -933 | 1987-02-14 18:58:00-03 | 09:33:00 -932 | 1987-02-14 18:57:00-03 | 09:32:00 -931 | 1987-02-14 18:56:00-03 | 09:31:00 -930 | 1987-02-14 18:55:00-03 | 09:30:00 -929 | 1987-02-14 18:54:00-03 | 09:29:00 -928 | 1987-02-14 18:53:00-03 | 09:28:00 -927 | 1987-02-14 18:52:00-03 | 09:27:00 -926 | 1987-02-14 18:51:00-03 | 09:26:00 -925 | 1987-02-14 18:50:00-03 | 09:25:00 -924 | 1987-02-14 18:49:00-03 | 09:24:00 -923 | 1987-02-14 18:48:00-03 | 09:23:00 -922 | 1987-02-14 18:47:00-03 | 09:22:00 -921 | 1987-02-14 18:46:00-03 | 09:21:00 -920 | 1987-02-14 18:45:00-03 | 09:20:00 -919 | 1987-02-14 18:44:00-03 | 09:19:00 -918 | 1987-02-14 18:43:00-03 | 09:18:00 -917 | 1987-02-14 18:42:00-03 | 09:17:00 -916 | 1987-02-14 18:41:00-03 | 09:16:00 -915 | 1987-02-14 18:40:00-03 | 09:15:00 -914 | 1987-02-14 18:39:00-03 | 09:14:00 -913 | 1987-02-14 18:38:00-03 | 09:13:00 -912 | 1987-02-14 18:37:00-03 | 09:12:00 -911 | 1987-02-14 18:36:00-03 | 09:11:00 -910 | 1987-02-14 18:35:00-03 | 09:10:00 -909 | 1987-02-14 18:34:00-03 | 09:09:00 -908 | 1987-02-14 18:33:00-03 | 09:08:00 -907 | 1987-02-14 18:32:00-03 | 09:07:00 -906 | 1987-02-14 18:31:00-03 | 09:06:00 -905 | 1987-02-14 18:30:00-03 | 09:05:00 -904 | 1987-02-14 18:29:00-03 | 09:04:00 -903 | 1987-02-14 18:28:00-03 | 09:03:00 -902 | 1987-02-14 18:27:00-03 | 09:02:00 -901 | 1987-02-14 18:26:00-03 | 09:01:00 -900 | 1987-02-14 18:25:00-03 | 09:00:00 -859 | 1987-02-14 18:24:00-03 | 08:59:00 -858 | 1987-02-14 18:23:00-03 | 08:58:00 -857 | 1987-02-14 18:22:00-03 | 08:57:00 -856 | 1987-02-14 18:21:00-03 | 08:56:00 -855 | 1987-0
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 :
> 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
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})');
+ make_interval
+
+
+
+ make_interval(years int DEFAULT 0,
+ months int DEFAULT 0,
+ weeks int DEFAULT 0,
+ days int DEFAULT 0,
+ hours int DEFAULT 0,
+ mins int DEFAULT 0,
+ secs double precision DEFAULT 0.0)
+
+
+
+interval
+
+ Create interval from years, months, weeks, days, hours, minutes and
+ seconds fields
+
+make_interval(days := 10)
+10 days
+
+
+
+
+
make_time
@@ -6746,6 +6772,57 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
+ make_timestamp
+
+
+
+ make_timestamp(year int,
+ month int,
+ day int,
+ hour int,
+ min int,
+ sec double precision)
+
+
+
+timestamp
+
+ Create timestamp from year, month, day, hour, minute and seconds fields
+
+make_timestamp(1-23, 7, 15, 8, 15, 23.5)
+2013-07-15 08:15:23.5
+
+
+
+
+
+ make_timestamptz
+
+
+
+ make_timestamptz(year int,
+ month int,
+ day int,
+ hour int,
+ min int,
+ sec double precision,
+timezone text )
+
+
+
+timestamp with time zone
+
+ Create timestamp with time zone from year, month, day, hour, minute
+ and seconds fields. When timezone is not specified,
+ then current time zone is used.
+
+make_timestamp(1-23, 7, 15, 8, 15, 23.5)
+2013-07-15 08:15:23.5+01
+
+
+
+
+
now
now()
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index f02efec..d0852f4 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -819,3 +819,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)
+ RETURNS interval STRICT IMMUTABLE LANGUAGE internal AS 'make_interval';
diff --
Re: Fwd: [HACKERS] patch: make_timestamp function
Alvaro Herrera 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
Dne 19. 2. 2014 21:20 "Alvaro Herrera" 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
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 19:01 GMT+01:00 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 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 19:01 GMT+01:00 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. > 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
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})');
+ make_interval
+
+
+
+ make_interval(years int DEFAULT 0,
+ months int DEFAULT 0,
+ weeks int DEFAULT 0,
+ days int DEFAULT 0,
+ hours int DEFAULT 0,
+ mins int DEFAULT 0,
+ secs double precision DEFAULT 0.0)
+
+
+
+interval
+
+ Create interval from years, months, weeks, days, hours, minutes and
+ seconds fields
+
+make_interval(days := 10)
+10 days
+
+
+
+
+
make_time
@@ -6746,6 +6772,81 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
+ make_timetz
+
+
+
+ make_timetz(hour int,
+ min int,
+ sec double precision,
+timezone text )
+
+
+
+time with time zone
+
+ Create time with time zone from hour, minute and seconds fields. When
+ timezone is not specified, then current time zone
+ is used.
+
+make_timetz(8, 15, 23.5)
+08:15:23.5+01
+
+
+
+
+
+ make_timestamp
+
+
+
+ make_timestamp(year int,
+ month int,
+ day int,
+ hour int,
+ min int,
+ sec double precision)
+
+
+
+timestamp
+
+ Create timestamp from year, month, day, hour, minute and seconds fields
+
+make_timestamp(1-23, 7, 15, 8, 15, 23.5)
+2013-07-15 08:15:23.5
+
+
+
+
+
+ make_timestamptz
+
+
+
+ make_timestamptz(year int,
+ month int,
+ day int,
+ hour int,
+ min int,
+ sec double precision,
+timezone text )
+
+
+
+timestamp with time zone
+
+ Create timestamp with time zone from year, month, day, hour, minute
+ and seconds fields. When timezone is not specified,
+ then current time zone is used.
+
+make_timestamp(1-23, 7, 15, 8, 15, 23.5)
+2013-07-15 08:15:23.5+01
+
+
+
+
+
now
now()
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index f02efec..d0852f4 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -819,3 +819,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)
+ RETURNS interval STRICT IMMUTABLE LANGUAGE internal AS 'make_interval';
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 06cc0cd..ba801e4 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -1106,6 +1106,7 @@ time_in(PG_FUNCTION_ARGS)
static int
tm2time(struct p
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
Fwd: [HACKERS] patch: make_timestamp function
Hello
2014/1/11 Tomas Vondra
> 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
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})');
+ make_interval
+
+
+
+ make_interval(years int DEFAULT 0,
+ months int DEFAULT 0,
+ weeks int DEFAULT 0,
+ days int DEFAULT 0,
+ hours int DEFAULT 0,
+ mins int DEFAULT 0,
+ secs double precision DEFAULT 0.0)
+
+
+
+interval
+
+ Create interval from years, months, weeks, days, hours, minutes and
+ seconds fields
+
+make_interval(days := 10)
+10 days
+
+
+
+
+
make_time
@@ -6735,6 +6761,81 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
+ make_timetz
+
+
+
+ make_timetz(hour int,
+ min int,
+ sec double precision,
+timezone text )
+
+
+
+time with time zone
+
+ Create time with time zone from hour, minute and seconds fields. When
+ timezone is not specified, then current time zone
+ is used.
+
+make_timetz(8, 15, 23.5)
+08:15:23.5+01
+
+
+
+
+
+ make_timestamp
+
+
+
+ make_timestamp(year int,
+ month int,
+ day int,
+ hour int,
+ min int,
+ sec double precision)
+
+
+
+timestamp
+
+ Create timestamp from year, month, day, hour, minute and seconds fields
+
+make_timestamp(1-23, 7, 15, 8, 15, 23.5)
+2013-07-15 08:15:23.5
+
+
+
+
+
+ make_timestamptz
+
+
+
+ make_timestamptz(year int,
+ month int,
+ day int,
+ hour int,
+ min int,
+ sec double precision,
+timezone text )
+
+
+
+timestamp with time zone
+
+ Create timestamp with time zone from year, month, day,
