Re: [PATCHES] final CSVlog patch
to make the
protocol a tiny
! * bit more robust against finding a false double nul byte
prologue. But we
! * still might find it in the len and/or pid bytes unless we're
careful.
*/
#ifdef PIPE_BUF
--- 24,32
* also cope with non-protocol data coming down the pipe, though
we cannot
* guarantee long strings won't get split apart.
*
! * We use non-nul bytes in is_last to make the protocol a tiny bit
! * more robust against finding a false double nul byte prologue. But
! * we still might find it in the len and/or pid bytes unless
we're careful.
I guess there's a distinction between null and nul that I don't yet
understand as the spelling is consistent, but I'm just pointing it
out on the off-chance it's a typo.
Index: src/backend/postmaster/syslogger.c
===
RCS file: /cvsroot/pgsql/src/backend/postmaster/syslogger.c,v
retrieving revision 1.36
diff -c -r1.36 syslogger.c
*** src/backend/postmaster/syslogger.c 4 Aug 2007 01:26:53 - 1.36
--- src/backend/postmaster/syslogger.c 11 Aug 2007 02:01:13 -
*** 1058,1063
--- 1200,1215
Log_filename, (unsigned long) timestamp);
}
+ if (suffix != NULL)
+ {
+ len = strlen(filename);
+ if (len 4 (strcmp(filename+(len-4),.log) == 0))
+ {
+ len -= 4;
+ }
Just a style thing, but did you want to drop the braces since it's
only one line? There were a couple of other places like this, but
they also included comments pertaining to the branch, so I assumed
the braces were appropriate to clarify the branch extent.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PATCHES] Correction of how to use TimeZone by ControlFile(xlog.c)
On Aug 3, 2007, at 10:33 , Tom Lane wrote: People who find the above arguments compelling would certainly be free to set their log_timezone to GMT. Those who don't find them compelling should not be forced to deal in GMT. The fact that Postgres has always logged in system local time, and we've had no complaints about that, suggests to me that most people prefer local-time logging. I've most likely missed something as I'm unfamiliar with this area, but would it make sense to record the time zone offset? Then whether its in local time or UTC, it's always marking a unique instant in time. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] HOT latest patch - version 8
Heikki, Thanks for providing this summary. As someone unfamiliar with the domain (both HOT development specifically and tuple management in general), I found it easy to follow. On Jul 13, 2007, at 8:31 , Heikki Linnakangas wrote: Pruning --- To reclaim the index-visible (i.e. first) tuple in a HOT chain, the line pointer is turned into a redirecting line pointer that points to the line pointer of the next tuple in the chain. To keep track of the space occupied by the dead tuple, so that we can reuse the space, a new line pointer is allocated and marked with LP_DELETE to point to the dead tuple. That means its tid changes, but that's ok since it's dead. Row-level fragmentation --- If there's no LP_DELETEd tuples large enough to fit the new tuple in, the row-level fragmentation is repaired in the hope that some of the slots were actually big enough, but were just fragmented. That's done by mapping the offsets in the page, and enlarging all LP_DELETEd line pointers up to the beginning of the next tuple. Would it make sense to enlarge the LP_DELETEd line pointers up to the beginning of the next tuple at the time the tuple is marked LP_DELETE? Vacuum -- Vacuum prunes all HOT chains, and removes any LP_DELETEd tuples, making the space available for any use. In the case of a fragmented row, am I right to assume vacuum reclaims all space up to the next (live) tuple? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] script binaries renaming
On Jul 7, 2007, at 13:50 , Tom Lane wrote: Zdenek Kotala [EMAIL PROTECTED] writes: Dave Page wrote: This is almost as bad as Magnus agreeing with JD (!), but I agree with Peter :-). After years of typing the current names, changing them does seem somewhat annoying. Worse yet, pg_* is just awkward to type. But these utilities are mostly using in scripts - one type, multi use. According to whom? The ones that are really at issue I think are createuser/createlang/dropuser/droplang, and those seem mainly intended for interactive use. In a script you might as well use psql -c. I'm curious as to know how often these are used at all. I think I may have used createuser once and used to use createlang, but I can't recall ever using the others. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] script binaries renaming
On Jul 6, 2007, at 5:53 , Dave Page wrote: On Fri, July 6, 2007 8:51 am, Peter Eisentraut wrote: Am Mittwoch, 4. Juli 2007 17:04 schrieb Zdenek Kotala: I attach complete patch which renames following binaries createdb createlang createuser dropdb droplang dropuser clusterdb vacuumdb reindexdb I just want to say I dislike this idea. This is almost as bad as Magnus agreeing with JD (!), but I agree with Peter :-). After years of typing the current names, changing them does seem somewhat annoying. Worse yet, pg_* is just awkward to type. While the change might be awkward, the names of these binaries really should be namespaced in some way. The current just too generic to be throwing into a bin/ directory in my opinion. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] script binaries renaming
On Jul 6, 2007, at 11:28 , Joshua D. Drake wrote: Why should they be name spaced? I see zero reason why that should be the case... apache_httpd? gnu_ls? Personally, I think that the Apache daemon *should* be named apached or something along those lines. Compare with postgres, pg_ctl, pg_dump, or pg_config. Albeit postgres is not consistent, they're all easily identifiable with PostgreSQL. In my opinion, postgres, pg_ctl, pg_ccmp, and pg_config are better names than, say, dbmsd, dbms_ctl, db_dump, and db_config. Also, we recently deprecated the use of postmaster (easily confused with mail systems) in favor of postgres. Looking at the binaries that are installed for 8.2: clusterdb createdb createlang createuser dropdb droplang dropuser ecpg initdb ipcclean pg_config pg_controldata pg_ctl pg_dump pg_dumpall pg_resetxlog pg_restore postgres postmaster - postgres psql reindexdb vacuumdb If these are all dumped into /usr/local/bin (as they sometimes are), many of them are not readily identifiable with PostgreSQL. Shouldn't they be? Compare with Subversion: svn svnadmin svndumpfileter svnlook svnserver svnsync svnversion I find these names much more consistent. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Load Distributed Checkpoints, final patch
On Jun 26, 2007, at 13:49 , Heikki Linnakangas wrote: Maximum is 0.9, to leave some headroom for fsync and any other things that need to happen during a checkpoint. I think it might be more user-friendly to make the maximum 1 (meaning as much smoothing as you could possibly get) and internally reserve a certain amount off for whatever headroom might be required. It's more common for users to see a value range from 0 to 1 rather than 0 to 0.9. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Synchronized scans
On Jun 4, 2007, at 15:24 , Heikki Linnakangas wrote: I don't think anyone can reasonably expect to get the same ordering when the same query issued twice in general, but within the same transaction it wouldn't be that unreasonable. The order rows are returned without an ORDER BY clause *is* implementation dependent, and is not guaranteed, at least by the spec. Granted, LIMIT without ORDER BY (and DISTINCT for that matter) brings this into sharp relief. I think the warning on LIMIT without ORDER BY is a good idea, regardless of the synchronized scans patch. I'm not saying this isn't a good idea, but are there other places where there might be gotchas for the unwary, such as DISTINCT without ORDER BY or (for an unrelated example) UNION versus UNION ALL? How many of these types of messages would be useful? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Synchronized scans
On Jun 4, 2007, at 16:34 , Heikki Linnakangas wrote: LIMIT without ORDER BY is worse because it not only returns tuples in different order, but it can return different tuples altogether when you run it multiple times. Wouldn't DISTINCT ON suffer from the same issue without ORDER BY? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Interval input: usec, msec
On May 29, 2007, at 0:06 , Neil Conway wrote: Applied to HEAD, backported to 8.2 and 8.1 One thing I noticed when looking over the patch is that there are a few bare numbers in datetime.c such as 10, 1000, 1e-3, and 1e-6. In timestamp.[hc] we've defined macros for conversions such as #define #define USECS_PER_SEC INT64CONST(100) I'd like to work up a patch that would add similar macros for datetime.c, in particular using the INT64CONST construction where appropriate. Thoughts? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [DOCS] OS/X startup scripts
On May 16, 2007, at 11:08 , Alvaro Herrera wrote: I can't remember if I dropped something else. Thanks, Alvaro. I've installed Hg and pulled from the site you posted. When I get a few spare cycles I'll take a look. Btw, we should look at localization for this in the not too distant future. Or maybe I should learn Spanish :) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [DOCS] OS/X startup scripts
On May 16, 2007, at 9:38 , Alvaro Herrera wrote: That said, check this out: http://www.ubiobio.cl/~gpoo/pgsql/settings/ Sadly, usage instructions are in spanish only currently, but I claim that this script is extremely useful for Pg development (sure, I wrote it and tailored to my needs). If there is interest I can translate the docs, but I have posted the script in these lists before and got no much of an interest. Michael Glaesemann did find it useful when I showed it to him in the summit, though. Ooo! Nifty! I'll have to check out what's changed. I still use this script. I find it very helpful. I can post my tweaks as well, if there's interest. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Enable integer datetimes by default
On May 5, 2007, at 22:28 , Neil Conway wrote: Attached is a patch that enables integer datetimes by default, per recent discussion on -hackers. It makes --enable-integer- datetimes the default, and documents the --disable-integer-datetimes configure option as a means to get the previous default behavior. Would it make more sense to have phrase it in the positive sense? i.e., --enable-floating-point-datetimes? I guess that's a bit longer, but it says what you're doing, rather than what you're *not* doing. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] Constraint trigger doc patch
(Fifth attempt: diff inline)
(And a fourth attempt, from another account...)
(Third time's the charm?)
(Resent as I sent this yesterday but haven't seen it on the list yet
or in the online archives. Apologies if it ends up double-posting.)
Please find attached a doc patch for CREATE CONSTRAINT TRIGGER. The
documentation here has always been sparse, as the command isn't
intended for general use. However, in its current form its a bit
*too* sparse. For example, it mentions constraint attributes but
doesn't say what those might be or where to look for information for
details. The patch lists attribute options and provides references to
where those options are described.
When looking in gram.y while trying to figure out what exactly was
meant by actual constraint specification, I discovered
OptConstrFromTable. I assume this means Optional Constraint From
Table and it looks like it's used to specify the referenced table a
foreign key constraint. I couldn't figure out how to meaningfully use
it and have left the description purposefully vague.
I don't have a working DocBook tool chain on my system, so I haven't
been able to check if it builds properly. I tried to be conscientious
about my formatting, but some SGML bugs may have crept in.
Thanks!
Michael Glaesemann
grzm myrealbox com
Index: doc/src/sgml/ref/create_constraint.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/
create_constraint.sgml,v
retrieving revision 1.14
diff -c -r1.14 create_constraint.sgml
*** doc/src/sgml/ref/create_constraint.sgml 16 Sep 2006 00:30:17
- 1.14
--- doc/src/sgml/ref/create_constraint.sgml 7 Oct 2006 03:53:18 -
***
*** 21,29
refsynopsisdiv
synopsis
CREATE CONSTRAINT TRIGGER replaceable class=parametername/
replaceable
! AFTER replaceable class=parameterevents/replaceable ON
! replaceable class=parametertablename/replaceable
replaceable class=parameterconstraint/replaceable replaceable
class=parameterattributes/replaceable
! FOR EACH ROW EXECUTE PROCEDURE replaceable
class=parameterfuncname/replaceable ( replaceable
class=parameterargs/replaceable )
/synopsis
/refsynopsisdiv
--- 21,32
refsynopsisdiv
synopsis
CREATE CONSTRAINT TRIGGER replaceable class=parametername/
replaceable
! AFTER replaceable class=parameterevent [ OR ... ]/
replaceable
! ON replaceable class=parametertable_name/replaceable
! [ FROM replaceable class=parameterreferenced_table_name/
replaceable ]
! { NOT DEFERRABLE | [ DEFERABBLE ] { INITIALLY IMMEDIATE |
INITIALLY DEFERRED } }
! FOR EACH ROW
! EXECUTE PROCEDURE replaceable class=parameterfuncname/
replaceable ( replaceable class=parameterarguments/replaceable )
/synopsis
/refsynopsisdiv
***
*** 33,102
para
commandCREATE CONSTRAINT TRIGGER/command is used within
commandCREATE TABLE/command/commandALTER TABLE/command
and by
!applicationpg_dump/application to create the special
triggers for
!referential integrity.
It is not intended for general use.
/para
/refsect1
refsect1
!titleParameters/title
!
! variablelist
! varlistentry
! termreplaceable class=PARAMETERname/replaceable/term
! listitem
!para
! The name of the constraint trigger.
!/para
! /listitem
! /varlistentry
!
! varlistentry
! termreplaceable class=PARAMETERevents/replaceable/term
! listitem
!para
! The event categories for which this trigger should be fired.
!/para
! /listitem
! /varlistentry
!
! varlistentry
! termreplaceable class=PARAMETERtablename/replaceable/
term
! listitem
!para
! The name (possibly schema-qualified) of the table in which
! the triggering events occur.
!/para
! /listitem
! /varlistentry
!
! varlistentry
! termreplaceable class=PARAMETERconstraint/
replaceable/term
! listitem
!para
! Actual constraint specification.
!/para
! /listitem
! /varlistentry
!
! varlistentry
! termreplaceable class=PARAMETERattributes/
replaceable/term
! listitem
!para
! The constraint attributes.
!/para
! /listitem
! /varlistentry
!
! varlistentry
! termreplaceable class=PARAMETERfuncname/replaceable
(replaceable class=PARAMETERargs/replaceable)/term
! listitem
!para
! The function to call as part of the trigger processing.
!/para
! /listitem
! /varlistentry
! /variablelist
/refsect1
/refentry
--- 36,128
para
commandCREATE CONSTRAINT TRIGGER/command is used within
commandCREATE TABLE/command/commandALTER TABLE/command
and by
!applicationpg_dump/application to create the special
triggers
[PATCHES] test: please ignore
I've posted a 6.5kB patch (as an attachment) three times over the past few days but haven't seen it hit the lists. Checking to see if this goes through. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Fix PGPORT reassignment in ecpg regression tests
On Sep 5, 2006, at 19:16 , Michael Meskes wrote: On Mon, Sep 04, 2006 at 11:54:42PM +0900, Michael Glaesemann wrote: The pg_regress.sh script for ecpg regression tests checks to make sure the port number is between 1024 and 65535. If it isn't, it uses 65432. (c310-315. This is the same behavior as the standard ... Applied. Thanks! Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Interval aggregate regression failure
On Sep 5, 2006, at 10:14 , Bruce Momjian wrote: Bruce Momjian wrote: OK, updated patch. It will fix the =24:00:00 case because it cascades up if the remainder number of seconds is greater or equal to one day. One open item is that it still might show 24 hours if the seconds computation combined with the remaning seconds 24 hours. Not sure if that should be handled or not. If you fix that, you really are cascading up because the resulting seconds might be less than the computed value, e.g. result is 23:00:00, remainder is 02:00:00, cascade up would be 1 day, 01:00:00. I am unsure we want to do that. Right now, this will show 25:00:00. Updated patch that uses TSROUND for partial month and days. Michael says the tests look good on his system. I think we are done with this bug. :-) Please find attached regression tests for this patch. Michael Glaesemann grzm seespotcode net 17interval_muldiv_0905T2053+0900.diff Description: Binary data ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] Fix PGPORT reassignment in ecpg regression tests
The pg_regress.sh script for ecpg regression tests checks to make sure the port number is between 1024 and 65535. If it isn't, it uses 65432. (c310-315. This is the same behavior as the standard regression tests, I believe.) However, it if does reassign the port number, it was changing it back to the original, supplied port number after creating the installation. This would cause the tests to fail as the tests were run against a different port (the original supplied port) while the server was listening on 65432. This patch removes the subsequent assignment back to the original port number. Passes both the standard regression tests and, more importantly, those for ecpg, with normal and abnormally high port numbers. Michael Glaesemann grzm seespotcode net Index: src/interfaces/ecpg/test/pg_regress.sh === RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/test/ pg_regress.sh,v retrieving revision 1.9 diff -c -r1.9 pg_regress.sh *** src/interfaces/ecpg/test/pg_regress.sh 29 Aug 2006 13:23:26 - 1.9 --- src/interfaces/ecpg/test/pg_regress.sh 4 Sep 2006 14:22:17 - *** *** 644,650 if [ x$temp_install != x ] then do_temp_install - PGPORT=$temp_port; export PGPORT else # not temp-install dont_temp_install fi --- 644,649 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Interval month, week - day
On Sep 1, 2006, at 9:32 , Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: On Sep 1, 2006, at 9:12 , Tom Lane wrote: I agree that this seems like an oversight in the original months/days/seconds patch, rather than behavior we want to keep. But is DecodeInterval the only place with the problem? I'll check on this tonight. Any idea where I might start to look? I'd look at the input routines for all the datetime types and see where they go. It's entirely possible that DecodeInterval is the only place with the problem, but I'd not assume that without looking. AFAICS, DecodeInterval is the only place that needed changing. I've looked through datetime.c, timestamp.c, date.c, and nabstime.c, and don't see anything else. It makes sense, too, as the only place where you could have weeks or non-integer months is during Interval input or interval multiplication/division. The pg_tm struct, which is used in time(stamp)?(tz)?/interval arithmetic only has integral months and no weeks component, so that shouldn't cause any problems. So, I think that's about it. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Interval aggregate regression failure (expected seems
On Sep 3, 2006, at 12:34 , Bruce Momjian wrote: OK, I worked with Michael and I think this is the best we are going to do to fix this. It has one TSROUND call for Powerpc, and that is documented. Applied. As I was working up regression tests, I found a case that this patch doesn't handle. select interval '4 mon' * .3 as product_h; product_h --- 1 mon 5 days 24:00:00 (1 row) This should be 1 mon 6 days. It fails for any number of months greater than 3 that is not evenly divisible by 10, greater than 3 months. Do we need to look at the month remainder separately? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Interval aggregate regression failure (expected seems
On Sep 4, 2006, at 4:45 , Bruce Momjian wrote: Another question. Is this result correct? test= select '999 months 999 days'::interval / 100; ?column? - 9 mons 38 days 40:33:36 (1 row) Should that be: 9 mons 39 days 16:33:36 Yeah, I think it should be. I had been thinking of treating the month and day component as having separate time contributions, but it makes more sense to think of month as a collection of days first, integral or no, and then cascade down the fractional portion of the combined days component to time. I.e., 9.99 mon is 9 mon 29.7 days, rather than 9 mon 29 days 60480 sec. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Interval month, week - day
On Sep 3, 2006, at 20:00 , Michael Glaesemann wrote:
On Sep 1, 2006, at 9:32 , Tom Lane wrote:
Michael Glaesemann [EMAIL PROTECTED] writes:
On Sep 1, 2006, at 9:12 , Tom Lane wrote:
I agree that this seems like an oversight in the original
months/days/seconds patch, rather than behavior we want to keep.
But is DecodeInterval the only place with the problem?
I'll check on this tonight. Any idea where I might start to look?
I'd look at the input routines for all the datetime types and see
where
they go. It's entirely possible that DecodeInterval is the only
place
with the problem, but I'd not assume that without looking.
AFAICS, DecodeInterval is the only place that needed changing. I've
looked through datetime.c, timestamp.c, date.c, and nabstime.c, and
don't see anything else. It makes sense, too, as the only place
where you could have weeks or non-integer months is during Interval
input or interval multiplication/division. The pg_tm struct, which
is used in time(stamp)?(tz)?/interval arithmetic only has integral
months and no weeks component, so that shouldn't cause any
problems. So, I think that's about it.
I realized there might be something in ecpg, and there was. I've
updated the ecpg DecodeInterval to match. However, I haven't been
able to get ecpg make check to work, so that part's untested.
Michael Glaesemann
grzm seespotcode net
Index: src/backend/utils/adt/datetime.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.169
diff -c -r1.169 datetime.c
*** src/backend/utils/adt/datetime.c25 Jul 2006 03:51:21 - 1.169
--- src/backend/utils/adt/datetime.c3 Sep 2006 23:55:34 -
***
*** 2920,2935
tm-tm_mday += val * 7;
if (fval != 0)
{
! int
sec;
!
! fval *= 7 *
SECS_PER_DAY;
! sec = fval;
! tm-tm_sec += sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval - sec) *
100;
#else
! *fsec += fval - sec;
#endif
}
tmask = (fmask DTK_M(DAY)) ?
0 : DTK_M(DAY);
break;
--- 2920,2942
tm-tm_mday += val * 7;
if (fval != 0)
{
! int extra_days;
! fval *= 7;
! extra_days = (int32)
fval;
! tm-tm_mday +=
extra_days;
! fval -= extra_days;
! if (fval != 0)
! {
! int
sec;
! fval *=
SECS_PER_DAY;
! sec = fval;
! tm-tm_sec +=
sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval
- sec) * 100;
#else
! *fsec += fval -
sec;
#endif
+ }
}
tmask = (fmask DTK_M(DAY)) ?
0 : DTK_M(DAY);
break;
***
*** 2938,2953
tm-tm_mon += val;
if (fval != 0)
{
! int
sec;
!
! fval *= DAYS_PER_MONTH
* SECS_PER_DAY;
! sec = fval;
! tm-tm_sec += sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval - sec) *
100;
#else
! *fsec
Re: [PATCHES] Interval month, week - day
On Sep 4, 2006, at 9:41 , Tom Lane wrote: This patch fails to apply --- looks like whitespace got mangled in transit. Please resend as an attachment. Please let me know if you have any problems with this one. Michael Glaesemann grzm seespotcode net 10interval_input_0904T0855+0900.diff Description: Binary data ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Interval aggregate regression failure
On Sep 1, 2006, at 11:31 , Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I am unclear about this report. The patch was not meant to fix every interval issue, but merely to improve multiplication and division computations. Does it do that? According to Michael's last report, your patch fails under --enable-integer-datetimes. Where does it fail? Here? select interval '41 mon 12 days 360:00' * 0.3 as product_a , interval '-41 mon -12 days +360:00' * 0.3 as product_b , interval '-41 mon 12 days 360:00' * 0.3 as product_c , interval '-41 mon -12 days -360:00' * 0.3 as product_d; product_a | product_b | product_c |product_d --+- ++- 1 year 11 days 146:24:00 | -1 years -11 days +69:36:00 | -1 years -5 days +98:24:00 | -1 years -11 days -146:23:60.00 - That is wrong, but I think we need another fix for that. Notice the problem is in minutes/seconds, not hours. I was sure it was more wrong than that the first time I saw it, but looks like I can't be sure of anything today :(. I need more sleep. Sorry for the noise on this one. Off work now, so I'm back at it. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Interval aggregate regression failure
On Sep 1, 2006, at 11:03 , Bruce Momjian wrote: I am unclear about this report. The patch was not meant to fix every interval issue, but merely to improve multiplication and division computations. Does it do that? I think the 23:60 is a time rounding issue that isn't covered in this patch. I am not against fixing it, but does the submitted patch improve things or not? Given we are post-feature freeze, we don't have time to fix all the interval issues. Your patch doesn't fix the things Tom referenced (nor did you intend it to). I just wanted to to collect examples of all the known issues with the interval code in one place. Probably too ambitious for September 1. Is it worth looking into the overflow and subtraction issues for 8.2? It seems to me they're bugs rather than features. Or are these 8.3 since it's so late? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Interval aggregate regression failure
Here's a patch that appears to work. Gives the same output with and without --enable-integer-datetimes. Answers look like they're correct. I'm basically treating the components as three different intervals (with the other two components zero), rounding them each to usecs, and adding them together. While it might be nice to carry a little extra precision around, it doesn't seem to be needed in these cases. If errors do arise, they should be at most 3 usec, which is pretty much noise for the floating point case, I suspect. Bruce, how's it look on your machine? If it looks good, I'll add the examples we've been using to the regression tests. Michael Glaesemann grzm seespotcode net Index: src/backend/utils/adt/timestamp.c === RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v retrieving revision 1.165 diff -c -r1.165 timestamp.c *** src/backend/utils/adt/timestamp.c 13 Jul 2006 16:49:16 - 1.165 --- src/backend/utils/adt/timestamp.c 1 Sep 2006 11:26:12 - *** *** 2494,2511 float8 factor = PG_GETARG_FLOAT8(1); double month_remainder, day_remainder, ! month_remainder_days; Interval *result; result = (Interval *) palloc(sizeof(Interval)); ! month_remainder = span-month * factor; ! day_remainder = span-day * factor; result-month = (int32) month_remainder; result-day = (int32) day_remainder; month_remainder -= result-month; day_remainder -= result-day; /* * The above correctly handles the whole-number part of the month and day * products, but we have to do something with any fractional part --- 2494,2553 float8 factor = PG_GETARG_FLOAT8(1); double month_remainder, day_remainder, ! month_remainder_days, ! month_remainder_time, ! day_remainder_time; Interval *result; result = (Interval *) palloc(sizeof(Interval)); ! ! month_remainder = span-month / factor; ! day_remainder = span-day / factor; result-month = (int32) month_remainder; result-day = (int32) day_remainder; month_remainder -= result-month; day_remainder -= result-day; + month_remainder_days = month_remainder * DAYS_PER_MONTH; + + /* + if month_remainder_days is not an integer, check to see if it's an + integer when converted to SECS or USECS. + If it is, round month_remainder_days to the nearest integer + +*/ + + if (month_remainder_days != (int32)month_remainder_days + TSROUND(month_remainder_days * SECS_PER_DAY) == + rint(month_remainder_days * SECS_PER_DAY)) + month_remainder_days = rint(month_remainder_days); + + result-day += (int32)month_remainder_days; + + #ifdef HAVE_INT64_TIMESTAMP + month_remainder_time = rint((month_remainder_days - + (int32)month_remainder_days) * USECS_PER_DAY); + + day_remainder_time = rint(day_remainder * USECS_PER_DAY); + + + result-time = rint(rint(span-time * factor) + day_remainder_time + + month_remainder_time); + #else + month_remainder_time = rint((month_remainder_days - + (int32)month_remainder_days) * SECS_PER_DAY); + day_remainder_time = rint(day_remainder * SECS_PER_DAY); + + result-time = span-time * factor + day_remainder_time + + month_remainder_time; + #endif + + + day_remainder = span-day * factor; + result-day = (int32) day_remainder; + day_remainder -= result-day; + /* * The above correctly handles the whole-number part of the month and day * products, but we have to do something with any fractional part *** *** 2518,2531 /* fractional months full days into days */ month_remainder_days = month_remainder * DAYS_PER_MONTH; ! result-day += (int32) month_remainder_days; ! /* fractional months partial days into time */ ! day_remainder += month_remainder_days - (int32) month_remainder_days; #ifdef HAVE_INT64_TIMESTAMP ! result-time = rint(span-time * factor + day_remainder * USECS_PER_DAY); #else ! result-time = span-time * factor + day_remainder * SECS_PER_DAY; #endif PG_RETURN_INTERVAL_P(result); --- 2560,2599 /* fractional months full days into days */ month_remainder_days = month_remainder * DAYS_PER_MONTH; ! /* !* The remainders suffer from float
Re: [PATCHES] [HACKERS] Interval aggregate regression failure
Please ignore the patch I just sent. Much too quick with the send button. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] Interval aggregate regression failure
On Sep 1, 2006, at 5:05 , Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Well, the patch only multiplies by 30, so the interval would have to span +5 million years to overflow. I don't see any reason to add rounding until we get an actual query that needs it Have you tried your patch against the various cases that have been discussed in the past? In particular there were several distinct examples of this behavior posted at the beginning of the thread, and I'd not assume that a fix for one handles them all. Yes, it fixes all posted examples, except one that displays 23:60. I cannot reproduce that failure from Powerpc so am waiting for Michael to test it. Here's your patch tested on my machine, both with and without -- enable-integer-datetimes. I've tweaked the ad hoc test suite to include a case where the days and time differ in sign and added a couple of queries to the ad hoc test suite to include the problems Tom referred to--not that this patch will fix them, but to keep the known problems together. I hope to add more to this to test more edge cases. Unfortunately the problem still occur (see product_d), and --enable- integer-datetimes is pretty broken with this patch. Michael Glaesemann grzm seespotcode net -- test queries select interval '41 mon 12 days 360:00' * 0.3 as product_a , interval '-41 mon -12 days +360:00' * 0.3 as product_b , interval '-41 mon 12 days 360:00' * 0.3 as product_c , interval '-41 mon -12 days -360:00' * 0.3 as product_d; select interval '41 mon 12 days 360:00' / 10 as quotient_a , interval '-41 mon -12 days +360:00' / 10 as quotient_b , interval '-41 mon 12 days 360:00' / 10 as quotient_c , interval '-41 mon -12 days -360:00' / 10 as quotient_d; select interval '-12 days' * 0.3; select 1 * '100 hours'::interval as ten billion; set time zone 'EST5EDT'; select '2005-10-29 13:22:00-04'::timestamptz + '1 day'::interval as 2005-01-30 13:22:00-05; select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29 13:22:00-04'::timestamptz as a day; set time zone local; -- end test queries -- without --enable-integer-datetimes select interval '41 mon 12 days 360:00' * 0.3 as product_a , interval '-41 mon -12 days +360:00' * 0.3 as product_b , interval '-41 mon 12 days 360:00' * 0.3 as product_c , interval '-41 mon -12 days -360:00' * 0.3 as product_d; product_a | product_b | product_c |product_d --+- ++- 1 year 11 days 146:24:00 | -1 years -11 days +69:36:00 | -1 years -5 days +98:24:00 | -1 years -11 days -146:23:60.00 (1 row) select interval '41 mon 12 days 360:00' / 10 as quotient_a , interval '-41 mon -12 days +360:00' / 10 as quotient_b , interval '-41 mon 12 days 360:00' / 10 as quotient_c , interval '-41 mon -12 days -360:00' / 10 as quotient_d; quotient_a |quotient_b | quotient_c |quotient_d +--- +---+--- 4 mons 4 days 40:48:00 | -4 mons -4 days +31:12:00 | -4 mons -2 days +40:48:00 | -4 mons -4 days -40:48:00 (1 row) select interval '-12 days' * 0.3; ?column? -- -3 days -14:23:60.00 (1 row) select 1 * '100 hours'::interval as ten billion; ten billion -- 2147483647:00:00 (1 row) set time zone 'EST5EDT'; SET select '2005-10-29 13:22:00-04'::timestamptz + '1 day'::interval as 2005-01-30 13:22:00-05; 2005-01-30 13:22:00-05 2005-10-30 13:22:00-05 (1 row) select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29 13:22:00-04'::timestamptz as a day; a day 1 day 01:00:00 (1 row) set time zone local; SET -- with --enable-integer-datetimes select interval '41 mon 12 days 360:00' * 0.3 as product_a , interval '-41 mon -12 days +360:00' * 0.3 as product_b , interval '-41 mon 12 days 360:00' * 0.3 as product_c , interval '-41 mon -12 days -360:00' * 0.3 as product_d; product_a | product_b | product_c | product_d --+- ++-- 1 year 11 days 146:24:00 | -1 years -11 days +69:36:00 | -1 years -5 days +98:24:00 | -1 years -11 days -146:24:00 (1 row) select interval '41 mon 12 days 360:00' / 10 as quotient_a , interval '-41 mon -12 days +360:00' / 10 as quotient_b , interval '-41 mon 12 days 360:00' / 10 as quotient_c , interval '-41 mon -12 days -360:00' / 10 as quotient_d; quotient_a |quotient_b | quotient_c |quotient_d
Re: [PATCHES] Interval month, week - day
On Sep 1, 2006, at 9:12 , Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: I came across some behavior that seems counterintuitive to me: test=# select '1.5 mon'::interval; interval - 1 mon 360:00:00 (1 row) With the time/day/month interval struct introduced in 8.1, I'd expect this to return '1 mon 15 days'. The reason is that the DecodeInterval converts fractional months to time directly, rather than cascading first to days. I agree that this seems like an oversight in the original months/days/seconds patch, rather than behavior we want to keep. But is DecodeInterval the only place with the problem? My recollection is that there's a certain amount of redundancy in the datetime code ... I'll check on this tonight. Any idea where I might start to look? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] Interval aggregate regression failure (expected seems
On Aug 30, 2006, at 12:50 , Bruce Momjian wrote: Yea, I see that -122:23:60.00. After applying your patch, I believe that on my machine it's the contribution from the day component that is producing the 23:60.00. For example, select interval '-12 days' * 0.3; ?column? -- -3 days -14:23:60.00 (1 row) I think some kind of rounding needs to be done to the day contribution as well. I'm continuing to work on it, but wanted to get this out there. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] Interval aggregate regression failure (expected seems
I think I've got it. I plan to update the regression tests this
evening, but I wanted to post what I believe is a solution.
select '41 mon'::interval / 10;
?column?
---
4 mons 3 days
(1 row)
select '41 mon 360:00'::interval / 10 as pos
, '-41 mon -360:00'::interval / 10 as neg;
pos |neg
+---
4 mons 3 days 36:00:00 | -4 mons -3 days -36:00:00
(1 row)
select '41 mon -360:00'::interval / 10 as pos
, '-41 mon 360:00'::interval / 10 as neg;
pos |neg
-+---
4 mons 3 days -36:00:00 | -4 mons -3 days +36:00:00
(1 row)
If anyone sees anything untoward, please let me know and I'll do my
best to fix it. Also, should the duplicate code in interval_mul and
interval_div be refactored into its own function?
Thanks!
Michael Glaesemann
grzm seespotcode net
---8-
Index: src/backend/utils/adt/timestamp.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.165
diff -c -r1.165 timestamp.c
*** src/backend/utils/adt/timestamp.c 13 Jul 2006 16:49:16 - 1.165
--- src/backend/utils/adt/timestamp.c 29 Aug 2006 06:20:03 -
***
*** 2494,2500
float8 factor = PG_GETARG_FLOAT8(1);
double month_remainder,
day_remainder,
! month_remainder_days;
Interval *result;
result = (Interval *) palloc(sizeof(Interval));
--- 2494,2502
float8 factor = PG_GETARG_FLOAT8(1);
double month_remainder,
day_remainder,
! month_remainder_days,
! month_remainder_day_frac,
! month_remainder_time;
Interval *result;
result = (Interval *) palloc(sizeof(Interval));
***
*** 2519,2526
/* fractional months full days into days */
month_remainder_days = month_remainder * DAYS_PER_MONTH;
result-day += (int32) month_remainder_days;
! /* fractional months partial days into time */
! day_remainder += month_remainder_days - (int32) month_remainder_days;
#ifdef HAVE_INT64_TIMESTAMP
result-time = rint(span-time * factor + day_remainder *
USECS_PER_DAY);
--- 2521,2556
/* fractional months full days into days */
month_remainder_days = month_remainder * DAYS_PER_MONTH;
result-day += (int32) month_remainder_days;
!
! month_remainder_day_frac = month_remainder_days - (int32)
month_remainder_days;
!
! #ifdef HAVE_INT64_TIMESTAMP
! month_remainder_day_frac = month_remainder_days - (int32)
month_remainder_days;
! month_remainder_time = month_remainder_day_frac * USECS_PER_DAY;
! if (rint(month_remainder_time) == USECS_PER_DAY)
! {
! result-day++;
! }
! else if ((rint(month_remainder_time)) == -USECS_PER_DAY)
! {
! result-day--;
! }
! #else
! month_remainder_time = month_remainder_day_frac * SECS_PER_DAY;
! if ((TSROUND(month_remainder_time) == SECS_PER_DAY))
! {
! result-day++;
! }
! else if ((TSROUND(month_remainder_time) == -SECS_PER_DAY))
! {
! result-day--;
! }
! #endif
! else
! {
! /* fractional months partial days into time */
! day_remainder += month_remainder_day_frac;
! }
#ifdef HAVE_INT64_TIMESTAMP
result-time = rint(span-time * factor + day_remainder *
USECS_PER_DAY);
***
*** 2548,2558
float8 factor = PG_GETARG_FLOAT8(1);
double month_remainder,
day_remainder,
! month_remainder_days;
Interval *result;
result = (Interval *) palloc(sizeof(Interval));
if (factor == 0.0)
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
--- 2578,2596
float8 factor = PG_GETARG_FLOAT8(1);
double month_remainder,
day_remainder,
! month_remainder_days,
! month_remainder_day_frac,
! month_remainder_time;
Interval *result;
result = (Interval *) palloc(sizeof(Interval));
+ /*
+ a: (fl) select '41 mon'::interval / 10;
+ *span = { time = 0., day = 0, month = 41 }
+ factor = 10.
+ */
+
if (factor == 0.0)
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
***
*** 2560,2579
month_remainder = span-month / factor;
day_remainder = span-day
Re: [PATCHES] [HACKERS] Interval aggregate regression failure (expected seems
On Aug 30, 2006, at 1:13 , Bruce Momjian wrote: Uh, I came up with a cleaner one, I think. I didn't test --enable-integer-datetimes yet. Cool. It's indeed much cleaner. Thanks, Bruce. I'm about to head to bed, but I'll look at it more closely tomorrow. I also noticed that my regression tests didn't exercise the code I thought it did. If you have a chance before I get to it, you might want to try these as well: select interval '41 mon 12 days 360:00' / 10 as quotient_a , interval '41 mon -12 days -360:00' / 10 as quotient_b , interval '-41 mon 12 days 360:00' / 10 as quotient_c , interval '-41 mon -12 days -360:00' / 10 as quotient_d; quotient_a | quotient_b| quotient_c |quotient_d +- +---+--- 4 mons 4 days 40:48:00 | 4 mons 2 days -40:48:00 | -4 mons -2 days +40:48:00 | -4 mons -4 days -40:48:00 (1 row) select interval '41 mon 12 days 360:00' * 0.3 as product_a , interval '41 mon -12 days -360:00' * 0.3 as product_b , interval '-41 mon 12 days 360:00' * 0.3 as product_c , interval '-41 mon -12 days -360:00' * 0.3 as product_d; product_a | product_b | product_c |product_d --+- +-+- 1 year 12 days 122:24:00 | 1 year 6 days -122:23:60.00 | -1 years -6 days +122:24:00 | -1 years -12 days -122:23:60.00 (1 row) The quotients look fine, but I'm wondering if another set of rounding is needed to bump those -122:23:60.00 to -122:24:00 in product_b and product_d. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] Interval aggregate regression failure (expected seems
+ month_remainder_days = rint(month_remainder_days); Anyway, I'll pound on this some more tonight. Michael Glaesemann grzm seespotcode net Index: src/backend/utils/adt/timestamp.c === RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v retrieving revision 1.165 diff -c -r1.165 timestamp.c *** src/backend/utils/adt/timestamp.c 13 Jul 2006 16:49:16 - 1.165 --- src/backend/utils/adt/timestamp.c 30 Aug 2006 00:48:37 - *** *** 2518,2523 --- 2518,2536 /* fractional months full days into days */ month_remainder_days = month_remainder * DAYS_PER_MONTH; + /* +* The remainders suffer from float rounding, so if they are +* within 0.01 of an integer, we round them to integers. +*/ + if (month_remainder_days != (int32) month_remainder_days + #ifdef HAVE_INT64_TIMESTAMP + rint(month_remainder_days * USECS_PER_DAY) == + (month_remainder_days * USECS_PER_DAY)) + #else + TSROUND(month_remainder_days * SECS_PER_DAY) == + rint(month_remainder_days * SECS_PER_DAY)) + #endif + month_remainder_days = rint(month_remainder_days); result-day += (int32) month_remainder_days; /* fractional months partial days into time */ day_remainder += month_remainder_days - (int32) month_remainder_days; *** *** 2571,2576 --- 2584,2602 /* fractional months full days into days */ month_remainder_days = month_remainder * DAYS_PER_MONTH; + /* +* The remainders suffer from float rounding, so if they are +* within 0.01 of an integer, we round them to integers. +*/ + if (month_remainder_days != (int32) month_remainder_days + #ifdef HAVE_INT64_TIMESTAMP + rint(month_remainder_days * USECS_PER_DAY) == + (month_remainder_days * USECS_PER_DAY)) + #else + TSROUND(month_remainder_days * SECS_PER_DAY) == + rint(month_remainder_days * SECS_PER_DAY)) + #endif + month_remainder_days = rint(month_remainder_days); result-day += (int32) month_remainder_days; /* fractional months partial days into time */ day_remainder += month_remainder_days - (int32) month_remainder_days; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Interval aggregate regression failure (expected seems
On Aug 30, 2006, at 12:50 , Bruce Momjian wrote:
Here is a test program. What does it show for you?
The output for me is:
4.100
2.989
3.000
Here's what I get. Just to make sure I'm doing this right, I'm
including how I compiled it.
$ cat div_test.c
#include stdio.h
int
main(int argc, char *argv[])
{
double x;
x = 41;
x = x / 10.0;
printf(%f\n, x);
x = x - (int)x;
x = x * 30;
printf(%15.15f\n, x);
x = 0.1 * 30;
printf(%15.15f\n, x);
return 0;
}
$ gcc div_test.c -o div_test
$ ./div_test
4.10
2.989
3.000
$
Yea, just an optimization, but I was worried that the computations
might
throw problems for certain numbers, so I figured I would only
trigger it
when necessary.
Thanks for the explanation. Helps me know I might actually be
learning this.
Patch attached. It also fixes a regression test output too.
Thanks for the patch. I'll look at it more closely tonight.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Interval aggregate regression failure (expected seems
On Aug 26, 2006, at 11:40 , Bruce Momjian wrote:
I used your ideas to make a patch to fix your example:
test= select '41 months'::interval / 10;
?column?
---
4 mons 3 days
(1 row)
and
test= select '41 months'::interval * 0.3;
?column?
---
1 year 9 days
(1 row)
The trick was not to play with the division, but to check if the
number
of seconds cascaded into full days and/or months.
While this does provide a fix for the example, I don't believe it's a
complete solution. For example, with your patch, you also get the
following results:
select '41 mon 360:00'::interval / 10 as pos
, '-41 mon -360:00'::interval / 10 as neg;
pos | neg
+--
4 mons 2 days 60:00:00 | -4 mons -2 days -59:59:60.00
(1 row)
If I've done the math right, this should be:
4 mons 3 days 36:00:00 | -4 mons -3 days -36:00:00
select '41 mon -360:00'::interval / 10 as pos
, '-41 mon 360:00'::interval / 10 as neg;
pos |neg
-+---
4 mons 2 days -12:00:00 | -4 mons -2 days +12:00:00
(1 row)
Should be:
4 mons 3 days -36:00:00 | -4 mons -3 days +36:00:00
What we want to do is check just the month contribution to the day
component to see if it is greater than 24 hours. Perhaps the simplest
way to accomplish this is something like (psuedo code):
if (abs(tsround(month_remainder * SECS_PER_DAY)) == SECS_PER_DAY)
{
if (month_remainder 0)
{
result-month++;
}
else
{
result-month--;
}
}
I'm going to try something along these lines this evening.
FWIW, I've included the patch of for what I'm working on. It's pretty
heavily commented right now with expected results as I think through
what the code's doing. (It also includes the DecodeInterval patch I
sent to -patches earlier today.) I'm still getting overflow warnings
in the lines including USECS_PER_DAY and USECS_PER_MONTH, and my
inexperience with C and gdb is getting the best of me right now
(though I'm still plugging away: ).
Michael Glaesemann
grzm seespotcode net
8---
? CONFIGURE_ARGS
? datetime.patch
? timestamp.patch
? src/backend/.DS_Store
? src/include/.DS_Store
? src/test/.DS_Store
Index: src/backend/utils/adt/datetime.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.169
diff -c -r1.169 datetime.c
*** src/backend/utils/adt/datetime.c25 Jul 2006 03:51:21 - 1.169
--- src/backend/utils/adt/datetime.c28 Aug 2006 07:08:46 -
***
*** 2920,2935
tm-tm_mday += val * 7;
if (fval != 0)
{
! int
sec;
!
! fval *= 7 *
SECS_PER_DAY;
! sec = fval;
! tm-tm_sec += sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval - sec) *
100;
#else
! *fsec += fval - sec;
#endif
}
tmask = (fmask DTK_M(DAY)) ?
0 : DTK_M(DAY);
break;
--- 2920,2942
tm-tm_mday += val * 7;
if (fval != 0)
{
! int extra_days;
! fval *= 7;
! extra_days = (int32)
fval;
! tm-tm_mday +=
extra_days;
! fval -= extra_days;
! if (fval != 0)
! {
! int
sec;
! fval *=
SECS_PER_DAY;
! sec = fval;
! tm-tm_sec +=
sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval
- sec) * 100;
#else
[PATCHES] Interval month, week - day
When trying to improve the rounding in interval_div and interval_mul,
I came across some behavior that seems counterintuitive to me:
test=# select '1.5 mon'::interval;
interval
-
1 mon 360:00:00
(1 row)
With the time/day/month interval struct introduced in 8.1, I'd expect
this to return '1 mon 15 days'. The reason is that the DecodeInterval
converts fractional months to time directly, rather than cascading
first to days.
Similar behavior happens with weeks:
select '1.5 week'::interval;
interval
-
7 days 84:00:00
(1 row)
Similarly, I believe should return 10 days 12 hours (7 days + 3.5 days).
I've patched DecodeInterval and the regression tests to check this. I
think tmask lines need to be updated, but I'm not sure how these work
so I've left them as is. I'd appreciate it if someone could look at
these areas in particular.
I think this is a behavior changing bug fix, as it was the intention
of the Interval struct change to treat days and time differently.
This patch brings the DecodeInterval function more in line with that
intention.
Thanks for your consideration.
Michael Glaesemann
grzm seespotcode net
Index: src/backend/utils/adt/datetime.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.169
diff -c -r1.169 datetime.c
*** src/backend/utils/adt/datetime.c25 Jul 2006 03:51:21 - 1.169
--- src/backend/utils/adt/datetime.c27 Aug 2006 23:25:53 -
***
*** 2920,2935
tm-tm_mday += val * 7;
if (fval != 0)
{
! int
sec;
!
! fval *= 7 *
SECS_PER_DAY;
! sec = fval;
! tm-tm_sec += sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval - sec) *
100;
#else
! *fsec += fval - sec;
#endif
}
tmask = (fmask DTK_M(DAY)) ?
0 : DTK_M(DAY);
break;
--- 2920,2942
tm-tm_mday += val * 7;
if (fval != 0)
{
! int extra_days;
! fval *= 7;
! extra_days = (int32)
fval;
! tm-tm_mday +=
extra_days;
! fval -= extra_days;
! if (fval != 0)
! {
! int
sec;
! fval *=
SECS_PER_DAY;
! sec = fval;
! tm-tm_sec +=
sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval
- sec) * 100;
#else
! *fsec += fval -
sec;
#endif
+ }
}
tmask = (fmask DTK_M(DAY)) ?
0 : DTK_M(DAY);
break;
***
*** 2938,2953
tm-tm_mon += val;
if (fval != 0)
{
! int
sec;
!
! fval *= DAYS_PER_MONTH
* SECS_PER_DAY;
! sec = fval;
! tm-tm_sec += sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval - sec) *
100;
#else
! *fsec += fval - sec;
#endif
}
tmask = DTK_M(MONTH);
break
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
On Aug 3, 2006, at 23:58 , Tom Lane wrote: Should we give VALUES its own reference page? That doesn't quite seem helpful either. I think we should go for a separate reference page, as VALUES appears to be expanding quite a bit. Up till now I've thought of VALUES only in conjunction with UPDATE, so perhaps a useful alternative would be to keep all of the information regarding VALUES and its syntax would be as a large part of the UPDATE reference page, though that would imply by placement (even if explained otherwise) that VALUES is only a part of the UPDATE syntax, which it no longer (?) is. That brings me back to the idea of VALUES deserving its own reference page. I wonder how soon pretty much the entire SQL spec will be duplicated in the PostgreSQL documentation. :) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [DOCS] Values list-of-targetlists patch for comments (was Re: [PATCHES]
On Aug 4, 2006, at 9:42 , Gavin Sherry wrote: ... with update? I associate it very closely with INSERT. After all, INSERT is the only statement where we've had VALUES as part of the grammar. Of course! Thanks for catching the glitch. I must have a bad RAM chip. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] CREATE SYNONYM ...
On Mar 7, 2006, at 17:29 , Hans-Jürgen Schönig wrote: this patch implements CREATE SYNONYM snip / This feature is especially important to people who want to port from Oracle to PostgreSQL (almost every customer who ports larger Oracle applications will asked for it). Is this SQL spec or Oracle-specific? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] WIP: further sorting speedup
On Feb 21, 2006, at 3:45 , Simon Riggs wrote: On Sun, 2006-02-19 at 21:40 -0500, Tom Lane wrote: After applying Simon's recent sort patch, I was doing some profiling and noticed that sorting spends an unreasonably large fraction of its time extracting datums from tuples (heap_getattr or index_getattr). The attached patch does something about this by pulling out the leading sort column of a tuple when it is received by the sort code or re-read from a tape. snip / The choice to pull out just the leading column, rather than all columns, is driven by concerns of (a) code complexity and (b) memory space. Having the extra columns pre-extracted wouldn't buy anything anyway in the common case where the leading key determines the result of a comparison. snip / I agree that as long as we are swamped by the cost of heapgetattr, then it does seem likely that first-key extraction (and keeping it with the tuple itself) will be a win in most cases over full-key extraction. Most of this is way above my head, but I'm trying to follow along: when you say first key and full key, are these related to relation keys (e.g., primary key) or attributes that are used in sorting (regardless of whether they're a key or not)? I notice Tom used the term leading [sort] column, which I read to mean the first attribute used to sort the relation (for whichever purpose, e.g., mergejoins, order-by clauses). I'll see if I can't find the Nyberg paper as well to learn a bit more. (I haven't been sleeping well recently.) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] WIP: further sorting speedup
On Feb 21, 2006, at 14:24 , Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: Most of this is way above my head, but I'm trying to follow along: Right, it's whatever is the sort key for this particular sort. Thanks, Tom. I think I may actually be starting to understand this a bit! Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Proposed patch to change missing FROM messages
On Jan 10, 2006, at 8:32 , Tom Lane wrote: Attached is a proposed change to create hopefully-more-useful error messages in the cases where we currently say missing FROM-clause entry. It's good to have these hints for users. Thanks, Tom. Patch: regression=# select * from a,b join c on (a.aa = c.cc); ERROR: invalid reference to FROM-clause entry for table a HINT: There is an entry for a, but it cannot be referenced from this part of the query. For clarity, I'd rewrite this hint as There is an entry for table a, ... Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] drop if exists
On Nov 17, 2005, at 11:51 , Christopher Kings-Lynne wrote: Including objects that already have CREATE OR REPLACE? I assume so - CREATE OR REPLACE doesn't drop things - only creates or replaces them. Of course. Silly me :) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] drop if exists
On Nov 17, 2005, at 11:45 , Christopher Kings-Lynne wrote: I think anything else will have to be done individually, although the pattern can be copied. Perhaps we should take bids on what should/should not be covered. Everything should be covered, otherwise it's just annoying for users... Including objects that already have CREATE OR REPLACE? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] drop if exists
On Nov 14, 2005, at 23:25 , Andrew Dunstan wrote: Ther attached patch is for comment. It implements drop if exists as has recently been discussed. Illustration: Nifty! Thanks for working this up, Andrew! andrew=# drop table blurflx; ERROR: table blurflx does not exist andrew=# drop table if exists blurflx; DROP TABLE I'm not sure what other DBMS' return in this situation (and kindly ignore this suggestion if it's specified or otherwise determined), but perhaps the output could be TABLE blurlx DOES NOT EXIST (without the ERROR) or something more informative, rather than DROP TABLE. It reminds me of the old behavior of outputting COMMIT even in the case of transaction failure. I find the current behavior of outputting ROLLBACK in the case of transaction failure more useful. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] Interval-day docs and regression tests
Please find attached diffs for documentation and simple regression tests for the new interval-day changes. I added tests for justify_hours() and justify_days() to interval.sql, as they take interval input and produce interval output. If there's a more appropriate place for them, please let me know. I've included the diff in the email as well (below) for ease of review. Michael Glaesemann grzm myrealbox com Index: doc/src/sgml/func.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.269 diff -c -r1.269 func.sgml *** doc/src/sgml/func.sgml22 Jul 2005 21:16:14 -1.269 --- doc/src/sgml/func.sgml26 Jul 2005 00:43:49 - *** *** 4903,4908 --- 4903,4926 such pair. /para + para +When adding an typeinterval/type value to (or subtracting an +typeinterval/type value from) a typetimestamp with time zone/type +value, the days component advances (or decrements) the date of the +typetimestamp with time zonetype by the indicated number of days. +Across daylight saving time changes (with the session tiem zone set to a +time zone that recognizes DST), this means literalinterval '1 day'/literal +does not necessarily equal literalinterval '24 hours'/literal. +For example, with the session time zone set to literalCST7CDT/ literal +literaltimestamp with time zone '2005-04-02 12:00-07' + interval '1 day' /literal +will produce literaltimestamp with time zone '2005-04-03 12:00-06'/literal, +while adding literalinterval '24 hours'/literal to the same initial +typetimestamp with time zone/type produces +literaltimestamp with time zone '2005-04-03 13:00-06'/ literal, as there is +a change in daylight saving time at literal2005-04-03 02:00/ literal in time zone +literalCST7CDT/literal. + /para + table id=operators-datetime-table titleDate/Time Operators/title Index: src/test/regress/expected/horology.out === RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/ horology.out,v retrieving revision 1.56 diff -c -r1.56 horology.out *** src/test/regress/expected/horology.out27 May 2005 21:31:23 -1.56 --- src/test/regress/expected/horology.out26 Jul 2005 00:43:49 - *** *** 598,603 --- 598,630 t (1 row) + -- timestamp with time zone, interval arithmetic around DST change + SET TIME ZONE 'CST7CDT'; + SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' as Apr 3, 12:00; + Apr 3, 12:00 + -- + Sun Apr 03 12:00:00 2005 CDT + (1 row) + + SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '24 hours' as Apr 3, 13:00; + Apr 3, 13:00 + -- + Sun Apr 03 13:00:00 2005 CDT + (1 row) + + SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '1 day' as Apr 2, 12:00; + Apr 2, 12:00 + -- + Sat Apr 02 12:00:00 2005 CST + (1 row) + + SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '24 hours' as Apr 2, 11:00; + Apr 2, 11:00 + -- + Sat Apr 02 11:00:00 2005 CST + (1 row) + + RESET TIME ZONE; SELECT timestamptz(date '1994-01-01', time '11:00') AS Jan_01_1994_10am; Jan_01_1994_10am -- Index: src/test/regress/expected/interval.out === RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/ interval.out,v retrieving revision 1.12 diff -c -r1.12 interval.out *** src/test/regress/expected/interval.out20 Jul 2005 16:42:32 -1.12 --- src/test/regress/expected/interval.out26 Jul 2005 00:43:49 - *** *** 228,230 --- 228,243 @ 4541 years 4 mons 4 days 17 mins 31 secs (1 row) + -- test justify_hours() and justify_days() + SELECT justify_hours(interval '6 months 3 days 52 hours 3 minutes 2 seconds') as 6 mons 5 days 4 hours 3 mins 2 seconds; + 6 mons 5 days 4 hours 3 mins 2 seconds + + @ 6 mons 5 days 4 hours 3 mins 2 secs + (1 row) + + SELECT justify_days(interval '6 months 36 days 5 hours 4 minutes 3 seconds') as 7 mons 6 days 5 hours 4 mins 3 seconds; + 7 mons 6 days 5 hours 4 mins 3 seconds + + @ 7 mons 6 days 5 hours 4 mins 3 secs + (1 row) + Index: src/test/regress/sql/horology.sql === RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/horology.sql,v retrieving revision 1.30 diff -c -r1.30 horology.sql *** src/test/regress/sql/horology.sql7 Apr 2005 01:51:41 - 1.30 --- src/test/regress/sql/horology.sql26 Jul 2005 00:43:49 -
Re: [HACKERS] [PATCHES] Dbsize backend integration
On Jul 3, 2005, at 8:35 AM, Bruce Momjian wrote: Andreas Pflug wrote: Dave Page wrote: Yup, attached. Per our earlier conversation, pg_dbfile_size() now returns the size of a table or index, and pg_relation_size() returns the total size of a relation and all associated indexes and toast tables etc. pg_relation_size's name is quite unfortunate, since the 8.0 contrib function does something different. And pg_dbfile_size sounds misleading, suggesting it takes a filename or relfilenode as parameter. Hmm. I don't see how we can call it pg_table_size because people think of tables and indexes, while relation has a more inclusive suggestion. I'm not familiar enough with the backend code to know if there's a semantic difference between how relation and table are treated, so my line of reasoning may be flawed. However, I try to use the term relation when I'm discussing things at a logical level--the predicates the data represents. Indexes and toast tables are implementation details, separate from the predicates the relation represents. The distinction between table and relation is very small, and using both pg_table_size and pg_relation_size but with different meanings is going to have people dependent on the documentation to remember the difference; pg_table_size and pg_relation_size both have the same meaning to me: the size of the table or index. I'd lean towards pg_table_size because this has a looser meaning that more easily includes indexes. An index doesn't really contain predicates and one doesn't store things in them directly. I think what's needed is a term that expresses the more inclusive or implementation-specific nature of the function that returns table + indexes + toast tables + kitchen sink. pg_tableall_size? pg_tablefull_size? pg_tableplus_size? pg_tableandmore_size? pg_tableimplementation_size? pg_tablekitchensink_size? ;) I recognize the desire to have a relatively short name for the functions, but perhaps a longer one is needed to capture the distinction between the two. (Though it's kind of frustrating that none of us have been able to hit on a term that accurately and succinctly describes it.) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Dbsize backend integration
On Jun 30, 2005, at 5:48 PM, Dave Page wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: 29 June 2005 12:46 snip / I have a new idea --- pg_storage_size(). I'm not against that one, but I think Tom's point is vaild. I cannot think of anything better at the moment though (maybe pg_component_size, but that's equally random) :-( Anyone else? Please? Someone? Anyone? :-) I'm still unclear as to what exactly is trying to be captured by the names, so I'll just throw some out and see if they're intuitive to anyone. pg_table_extensions_size() pg_table_support_size() pg_relation_extensions_size() pg_relation_support_size() pg_relation_extended_size() My two yen... if that :) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PATCHES] Interval-day patch
Please find attached a patch which adds a day field to the interval
struct so that we can treat INTERVAL '1 day' differently from
INTERVAL '24 hours' in DST-aware situations. It also includes a
function called interval_simplify() which takes an interval argument
and returns an interval where hours over 24 are promoted to days, e.g.,
template1=# select interval_simplify('3 months -11 days 79 hours 2
minutes'::interval);
interval_simplify
--
3 mons -7 days -16:58:00
(1 row)
If anyone has better ideas for the name of this function, please let
me know.
I've modified the regression tests, but still need to add additional
tests for the interval_simplify function, and I want to add a few
more tests for the new interval behavior. Also, the docs will need to
be updated to mention the new behavior. I plan on doing this in over
the next couple of days.
This is some of the first C I've hacked, and the first patch I've
submitted that's more than a documentation or a simple one-liner (and
even that one got worked over pretty good :) ), so I fully expect
some mistakes to be found. Please let me know and I'll do my best to
fix them.
In timestamp.c, I suspect that AdjustIntervalForTypmod,
interval_scale will need some modifications, though I'm not quite
sure what this code is doing. I've left them as-is. I've made some
changes to interval2tm, but believe that the changes I've made may
not be adequate. Given sufficient instruction, I'll be happy to make
the necessary changes to these functions.
A few things I noticed while I was working:
In interval_mul and interval_div, I'm wondering whether 30.0 and 24.0
shouldn't be substituted for 30 and 24 in the non-integer-timestamp
code path, as these are floats. Perhaps it doesn't make a difference
for multiplication, but I see similar usage in interval_cmp_interval.
I've left the code as-is.
In the deconstruct_array calls in interval_accum and interval_avg,
the size of interval is passed as a magic number (16). I think this
could be abstracted out, such as #define SIZEOF_INTERVAL 16 to make
the code a bit more robust (albeit just a little). Is this a
reasonable change?
Michael Glaesemann
grzm myrealbox com
interval_day.diff
Description: Binary data
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PATCHES] (8.1) to_timestamp correction (epoch to timestamptz)
Bruce, Please note that this patch is a correction and replacement for an earlier patch in the queue. The patch accompanying the message http://candle.pha.pa.us/mhonarc/patches/msg8.html should be removed from the queue and not applied. The one (originally) attached to this message should be applied. Thanks! Michael Glaesemann grzm myrealbox com On Jun 5, 2005, at 9:17 AM, Bruce Momjian wrote: Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. -- - Michael Glaesemann wrote: Note: This patch is intended for 8.1 (as was the original). I believe the previous patch I submitted to convert Unix epoch to timestamptz contains a bug relating to its use of AT TIME ZONE. Please find attached a corrected patch diffed against HEAD, which includes documentation. The original function was equivalent to CREATE FUNCTION to_timestamp (DOUBLE PRECISION) RETURNS timestamptz LANGUAGE SQL AS ' select ( (\'epoch\'::timestamptz + $1 * \'1 second\'::interval) at time zone \'UTC\' ) '; The AT TIME ZONE 'UTC' removes the time zone from the timestamptz, returning timestamp. However, the function is declared to return timestamptz. The original patch appeared to work, but creating this equivalent function fails as it doesn't return the declared datatype. The corrected function restores the time zone with an additional AT TIME ZONE 'UTC': CREATE FUNCTION to_timestamp (double precision) returns timestamptz language sql as ' select ( (\'epoch\'::timestamptz + $1 * \'1 second\'::interval) at time zone \'UTC\' ) at time zone \'UTC\' '; Michael Glaesemann grzm myrealbox com [ Attachment, skipping... ] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PATCHES] (8.1) to_timestamp correction (epoch to timestamptz)
Note: This patch is intended for 8.1 (as was the original). I believe the previous patch I submitted to convert Unix epoch to timestamptz contains a bug relating to its use of AT TIME ZONE. Please find attached a corrected patch diffed against HEAD, which includes documentation. The original function was equivalent to CREATE FUNCTION to_timestamp (DOUBLE PRECISION) RETURNS timestamptz LANGUAGE SQL AS ' select ( (\'epoch\'::timestamptz + $1 * \'1 second\'::interval) at time zone \'UTC\' ) '; The AT TIME ZONE 'UTC' removes the time zone from the timestamptz, returning timestamp. However, the function is declared to return timestamptz. The original patch appeared to work, but creating this equivalent function fails as it doesn't return the declared datatype. The corrected function restores the time zone with an additional AT TIME ZONE 'UTC': CREATE FUNCTION to_timestamp (double precision) returns timestamptz language sql as ' select ( (\'epoch\'::timestamptz + $1 * \'1 second\'::interval) at time zone \'UTC\' ) at time zone \'UTC\' '; Michael Glaesemann grzm myrealbox com to_timestamp-20041212.diff Description: application/text ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] doc cleanup: proper emdashes
On Nov 15, 2004, at 3:35 PM, Neil Conway wrote: (-- might be considered an en dash, but AFAIK it is incorrect to use an en dash to designate a parenthetical comment anyway). Then again, wouldn't parentheses be the appropriate punctuation for a parenthetical comment? :P Michael Glaesemann grzm myrealbox com PS. I think the phrasing you're looking for is amplifying or explanatory text. Em dashes, commas, or parentheses can all serve this purpose. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] to_timestamp overloaded to convert from Unix epoch
On Aug 15, 2004, at 1:19 AM, Tom Lane wrote: There was however another patch submitted recently that seemed to duplicate yours functionally but used a different syntax --- I think the guy had started by looking at extract(epoch from timestamp) rather than to_timestamp. Other than Chris' suggestion of extract(timestamp from epoch)? I did find this documentation patch from December 2003 giving an example of how to convert from Unix epoch to timestamp, but not a function per se. http://archives.postgresql.org/pgsql-patches/2003-12/msg00112.php However, I suspect you may thinking of something else. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] to_timestamp overloaded to convert from Unix epoch
Please find attached a patch (diff -c against cvs HEAD) to add a function that accepts a double precision argument assumed to be a Unix epoch timestamp and returns timestamp with time zone, and accompanying documentation. Usage: test=# select to_timestamp(200120400); to_timestamp 1976-05-05 14:00:00+09 (1 row) If regression tests are required, I will produce some. I'd appreciate any pointers as to what to look for, as they would be my first attempt at writing regression tests. Regards Michael Glaesemann grzm myrealbox com to_timestamp.diff Description: Binary data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] Epoch to timestamp conversion function patch
On Aug 5, 2004, at 2:03 AM, Tom Lane wrote: I'd suggest just one function epoch_to_timestamp that actually yields timestamptz, and then if casting the result to timestamp is needed it'll happen automatically. That makes sense. Chris mentioned the possibility of using the MySQL FROM_UNIXTIME() syntax instead of making something new. http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html I haven't checked the SQL spec, but I suspect they don't specify this function. Might be nice to make it consistent with another implementation rather than making new syntax to do the same thing. I don't know whether Oracle (or DB2?) might have similar functions that might at some time in the future make their way into the spec. Skimming through the Oracle documentation and searching for similar functionality in DB2 and Oracle on the web leads me to think they *don't* currently have a function to do this directly. Anyone familiar with DB2 or Oracle know if this is in fact the case? One drawback would be that people might expect additional functionality. For example, MySQL FROM_UNIXTIME(integer) works similarly to epoch_to_timestamp(double). However, there's also a FROM_UNIXTIME(integer, format) function that I really don't think would be needed. Using a different syntax would call attention to this difference. Another idea would be to overload TO_TIMESTAMP to take a single double precision float parameter rather than two text parameters. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PATCHES] Epoch to timestamp conversion function patch
Please find attached two patches (one for pg_proc.h and another for supporting documentation) for two SQL functions: epoch_to_timestamp(integer) and epoch_to_timestamptz(double precision), which convert from UNIX epoch to the native PostgreSQL timestamp and timestamptz data types. The equivalent SQL code is create function epoch_to_timestamp(integer) returns timestamp language sql as ' select (\'epoch\'::timestamptz + $1 * \'1 second\'::interval)::timestamp '; create function epoch_to_timestamptz(double precision) returns timestamptz language sql as ' select (\'epoch\'::timestamp + $1 * \'1 second\'::interval) at time zone \'UTC\' '; Some very simple tests (all should return TRUE): test=# select epoch_to_timestamp(extract(epoch from current_timestamp)::integer) = current_timestamp::timestamp(0); ?column? -- t (1 row) test=# select epoch_to_timestamptz(extract(epoch from current_timestamp)::integer) = current_timestamp(0); ?column? -- t (1 row) test=# select epoch_to_timestamptz(extract(epoch from current_timestamp)) = current_timestamp; ?column? -- t (1 row) If regression tests are desired, I'll work some up. Any feedback appreciated. Michael Glaesemann grzm myrealbox com func.sgml.diff Description: Binary data pg_proc.h.diff Description: Binary data ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] notice about costly ri checks (2)
On Mar 5, 2004, at 1:49 PM, Bruce Momjian wrote: Agreed. The current text is: NOTICE: costly cross-type foreign key because of component 1 Seems we should say something like: NOTICE: foreign key constraint 'constrname' must use a costly cross-type conversion It seems to me that in some ways this is similar to the situation where indexes are created to enforce a UNIQUE constraint. Indexes also incur additional overhead for inserts and updates, but make no mention of the cost: the DBA is assumed to know that, or they can check the docs if they're interested in why such a notice is being raised. I'd think something as simple as NOTICE: foreign key constraint 'constrname' will require a cross-type conversion similar to NOTICE: CREATE TABLE / UNIQUE will create implicit index foox_interesting_key for table foox Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PATCHES] Linking references in documentation
Below is a patch to provide a few links between the former administrator's guide and appropriate reference pages. Michael Glaesemann grzm myrealbox com Index: backup.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/backup.sgml,v retrieving revision 2.34 diff -c -r2.34 backup.sgml *** backup.sgml 19 Jan 2004 20:12:30 - 2.34 --- backup.sgml 9 Feb 2004 15:39:06 - *** *** 260,266 pg_dump -Fc replaceable class=parameterdbname/replaceable replaceable class=parameterfilename/replaceable /programlisting ! See the applicationpg_dump/ and applicationpg_restore/ reference pages for details. /para /formalpara --- 260,266 pg_dump -Fc replaceable class=parameterdbname/replaceable replaceable class=parameterfilename/replaceable /programlisting ! See the applicationxref linkend=APP-PGDUMP/ and applicationxref linkend=APP-PGRESTORE/ reference pages for details. /para /formalpara *** *** 298,305 /para para ! Please familiarize yourself with the ! citerefentryrefentrytitlepg_dump// reference page. /para /sect2 /sect1 --- 298,305 /para para ! Please familiarize yourself with the xref linkend=APP-PGDUMP ! reference page. /para /sect2 /sect1 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
