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: [HACKERS] [PATCHES] log_statement output for protocol
Bruce, I made a few tests here and the backend terminates with a SIG11 when a parameter has the NULL value (it was logged as (null) before). I suspect the new code broke something (perhaps it's due to the escaping). -- Guillaume ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] log_statement output for protocol
On 8/29/06, Bruce Momjian [EMAIL PROTECTED] wrote: DETAIL: prepare: SELECT $1; bind: $1 = 'a''b' I attached a trivial patch to add a dash between the prepare part and the bind part. People usually don't finish their queries with a semi colon so it's more readable with a separator. DETAIL: prepare: SELECT $1 bind: $1 = 'a''b' becomes DETAIL: prepare: SELECT $1 - bind: $1 = 'a''b' -- Guillaume Index: src/backend/tcop/postgres.c === RCS file: /projects/cvsroot/pgsql/src/backend/tcop/postgres.c,v retrieving revision 1.501 diff -c -r1.501 postgres.c *** src/backend/tcop/postgres.c 29 Aug 2006 02:32:41 - 1.501 --- src/backend/tcop/postgres.c 29 Aug 2006 11:46:15 - *** *** 1782,1788 *portal_name ? portal_name : ), errdetail(prepare: %s%s%s, sourceText, /* optionally print bind parameters */ ! bindText ? bind: : , bindText ? bindText : ))); BeginCommand(portal-commandTag, dest); --- 1782,1788 *portal_name ? portal_name : ), errdetail(prepare: %s%s%s, sourceText, /* optionally print bind parameters */ ! bindText ? - bind: : , bindText ? bindText : ))); BeginCommand(portal-commandTag, dest); *** *** 1896,1902 *portal_name ? portal_name : ), errdetail(prepare: %s%s%s, sourceText, /* optionally print bind parameters */ ! bindText ? bind: : , bindText ? bindText : ))); } } --- 1896,1902 *portal_name ? portal_name : ), errdetail(prepare: %s%s%s, sourceText, /* optionally print bind parameters */ ! bindText ? - bind: : , bindText ? bindText : ))); } } ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Performance testing of COPY (SELECT)
Bruce Momjian írta: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Zoltan Boszormenyi wrote: My v8 had the syntax support for COPY (SELECT ...) (col1, col2, ...) TO and it was actually working. In your v9 you rewrote the syntax parsing so that feature was lost in translation. Interesting. I didn't realize this was possible -- obviously I didn't test it (did you have a test for it in the regression tests? I may have missed it). In fact, I deliberately removed the column list from the grammar, because it can certainly be controlled inside the SELECT, so I thought there was no reason the support controlling it in the COPY column list. I would vote against allowing a column list here, because it's useless and it strikes me as likely to result in strange syntax error messages if the user makes any little mistake. What worries me is that the above looks way too nearly like a function call, which means that for instance if you omit a right paren somewhere in the SELECT part, you're likely to get a syntax error that points far to the right of the actual mistake. The parser could also mistake the column list for a table-alias column list. Specifying a column list with a view name is useful, of course, but what is the point when you are writing out a SELECT anyway? If you don't support COPY view TO, at least return an error messsage that suggests using COPY (SELECT * FROM view). And if you support COPY VIEW, you are going to have to support a column list for that. Is that additional complexity in COPY? If so, it might be a reason to just throw an error on views and do use COPY SELECT. No, it oes not have any additional complexity, it uses the same code COPY tablename TO uses. Seeing that COPY VIEW only supports TO, not FROM, and COPY SELECT support only TO, not FROM, it seems logical for COPY to just support relations, and COPY SELECT to be used for views, if we can throw an error on COPY VIEW to tell people to use COPY SELECT. The additional hint would be enough if the VIEW case is not supported. ---(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
Michael Glaesemann wrote: On Aug 29, 2006, at 15:38 , Michael Glaesemann wrote: 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. I've cleaned up the patch a bit in terms of whitespace, comments, and parens. I've also updated the interval and horology regression tests. The horology tests needed updating because I added 5 rows to INTERVAL_TBL. I didn't check the math for every row of time(tz | stamp | stamptz)/interval arithmetic in the horology tests as I think problems in this area would have shown up before. Does that make sense or it just rationalization on my part? Both with and without --enable-integer-datetimes pass the regression tests. Uh, I came up with a cleaner one, I think. I didn't test --enable-integer-datetimes yet. I tested a few of your examples: test= select '41 mon 10:00:00'::interval / 10 as pos; pos 4 mons 3 days 01:00:00 (1 row) It basically rounds the remainders to full values if they are close to full (+/- 0.01). -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: src/backend/utils/adt/timestamp.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v retrieving revision 1.165 diff -c -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 16:06:49 - *** *** 2505,2510 --- 2505,2513 result-day = (int32) day_remainder; month_remainder -= result-month; day_remainder -= result-day; + if (day_remainder != (int32)day_remainder + TSROUND(day_remainder) == rint(day_remainder)) + day_remainder = rint(day_remainder); /* * The above correctly handles the whole-number part of the month and day *** *** 2518,2523 --- 2521,2529 /* fractional months full days into days */ month_remainder_days = month_remainder * DAYS_PER_MONTH; + if (month_remainder_days != (int32)month_remainder_days + TSROUND(month_remainder_days) == rint(month_remainder_days)) + 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; *** *** 2564,2569 --- 2570,2578 result-day = (int32) day_remainder; month_remainder -= result-month; day_remainder -= result-day; + if (day_remainder != (int32)day_remainder + TSROUND(day_remainder) == rint(day_remainder)) + day_remainder = rint(day_remainder); /* * Handle any fractional parts the same way as in interval_mul. *** *** 2571,2576 --- 2580,2588 /* fractional months full days into days */ month_remainder_days = month_remainder * DAYS_PER_MONTH; + if (month_remainder_days != (int32)month_remainder_days + TSROUND(month_remainder_days) == rint(month_remainder_days)) + 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 6: explain analyze is your friend
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] Updatable views
Bernd Helmle wrote: --On Donnerstag, August 24, 2006 22:25:46 +0200 Bernd Helmle [EMAIL PROTECTED] wrote: --On Montag, August 21, 2006 02:07:41 -0400 Alvaro Herrera [EMAIL PROTECTED] wrote: If someone wants to look at the current updatable view patch, please look at this current version. I did some more rework based on additional suggestions from Alvaro, so please find the current updatable view patch attached. I've rewritten functions to use expression_tree_walker() and added additional comments. If you find anything more to improve, feel free to drop your comments. Thanks to Alvarro for its comments again. Note that pg_rewrite.h does not match catalogs.sgml. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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
[PATCHES] Changes to epgc test
I'm not at all sure that these are the right changes to apply; it somewhat appears to me as though ecpg is supposed to be able to cope with the omissions. In any case, CVS HEAD is breaking on AIX 5.3 with GCC 4.1.1, and these are the places where it's breaking. Index: test1.pgc.in === RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/test/connect/test1.pgc.in,v retrieving revision 1.3 diff -u -r1.3 test1.pgc.in --- test1.pgc.in29 Aug 2006 12:24:51 - 1.3 +++ test1.pgc.in29 Aug 2006 18:42:25 - @@ -26,25 +26,25 @@ exec sql connect to [EMAIL PROTECTED] as main; exec sql disconnect main; - exec sql connect to @localhost as main; + exec sql connect to [EMAIL PROTECTED] as main; exec sql disconnect main; exec sql connect to [EMAIL PROTECTED]:@TEMP_PORT@ as main; exec sql disconnect main; - exec sql connect to @localhost:@TEMP_PORT@ as main; + exec sql connect to [EMAIL PROTECTED]:@TEMP_PORT@ as main; exec sql disconnect main; exec sql connect to connectdb:@TEMP_PORT@ as main; exec sql disconnect main; - exec sql connect to :@TEMP_PORT@ as main; + exec sql connect to connectdb:@TEMP_PORT@ as main; exec sql disconnect main; exec sql connect to tcp:postgresql://localhost:@TEMP_PORT@/connectdb user connectuser identified by connectpw; exec sql disconnect; - exec sql connect to tcp:postgresql://localhost:@TEMP_PORT@/ user connectdb; + exec sql connect to tcp:postgresql://localhost:@TEMP_PORT@/ user connectdb; exec sql disconnect; strcpy(pw, connectpw); Index: test5.pgc === RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/test/connect/test5.pgc,v retrieving revision 1.2 diff -u -r1.2 test5.pgc --- test5.pgc 29 Aug 2006 12:24:51 - 1.2 +++ test5.pgc 29 Aug 2006 18:42:25 - @@ -37,7 +37,7 @@ exec sql connect to 'connectdb' as main; exec sql disconnect main; - exec sql connect to as main user connectdb; + exec sql connect to connectdb as main user connectdb; exec sql disconnect main; exec sql connect to connectdb as main user connectuser/connectdb; @@ -55,7 +55,7 @@ exec sql connect to unix:postgresql://200.46.204.71/connectdb as main user connectuser; exec sql disconnect main; - exec sql connect to unix:postgresql://localhost/ as main user connectdb; + exec sql connect to unix:postgresql://localhost/ as main user connectdb; exec sql disconnect main; /* connect twice */ -- output = reverse(gro.mca @ enworbbc) http://cbbrowne.com/info/linuxxian.html The only ``intuitive'' interface is the nipple. After that, it's all learned. -- Bruce Ediger, [EMAIL PROTECTED] on X interfaces. ---(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: [HACKERS] [PATCHES] log_statement output for protocol
Guillaume Smet wrote:
On 8/29/06, Bruce Momjian [EMAIL PROTECTED] wrote:
DETAIL: prepare: SELECT $1; bind: $1 = 'a''b'
I attached a trivial patch to add a dash between the prepare part and
the bind part. People usually don't finish their queries with a semi
colon so it's more readable with a separator.
DETAIL: prepare: SELECT $1 bind: $1 = 'a''b'
becomes
DETAIL: prepare: SELECT $1 - bind: $1 = 'a''b'
Good point. I thought it was clear enough, but obviously not. I had a
similar case with bind, and used a comma to separate them:
LOG: statement: prepare sel1, SELECT $1;
LOG: statement: bind sel1, $1 = 'a''b'
I am concerned a dash isn't clear enough, and a semicolon is confusing.
Using a comma the new output is:
LOG: duration: 0.023 ms execute sel1
DETAIL: prepare: SELECT $1;, bind: $1 = 'a''b'
or with no semicolon:
LOG: duration: 0.023 ms execute sel1
DETAIL: prepare: SELECT $1, bind: $1 = 'a''b'
Is that OK? Patch attached and committed. I also fixed the null bind
parameter bug. It now displays $1 = NULL (no quotes used). Other
suggestions?
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/backend/tcop/postgres.c
===
RCS file: /cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.501
diff -c -c -r1.501 postgres.c
*** src/backend/tcop/postgres.c 29 Aug 2006 02:32:41 - 1.501
--- src/backend/tcop/postgres.c 29 Aug 2006 19:54:08 -
***
*** 1539,1555
-1);
/* Save the parameter values */
! appendStringInfo(bind_values_str, %s$%d = ',
bind_values_str.len ? , : ,
paramno + 1);
! for (p = pstring; *p; p++)
{
! if (*p == '\'') /* double single quotes */
appendStringInfoChar(bind_values_str, *p);
! appendStringInfoChar(bind_values_str, *p);
}
! appendStringInfoChar(bind_values_str, '\'');
!
/* Free result of encoding conversion, if any */
if (pstring pstring != pbuf.data)
pfree(pstring);
--- 1539,1561
-1);
/* Save the parameter values */
! appendStringInfo(bind_values_str, %s$%d = ,
bind_values_str.len ? , : ,
paramno + 1);
! if (pstring)
{
! appendStringInfoChar(bind_values_str, '\'');
! for (p = pstring; *p; p++)
! {
! if (*p == '\'') /* double single quotes */
! appendStringInfoChar(bind_values_str, *p);
appendStringInfoChar(bind_values_str, *p);
! }
! appendStringInfoChar(bind_values_str, '\'');
}
! else
! appendStringInfo(bind_values_str, NULL);
!
/* Free result of encoding conversion, if any */
if (pstring pstring != pbuf.data)
pfree(pstring);
***
*** 1782,1788
*portal_name ? portal_name : ),
errdetail(prepare: %s%s%s, sourceText,
/* optionally print bind parameters */
! bindText ? bind: : ,
bindText ? bindText : )));
BeginCommand(portal-commandTag, dest);
--- 1788,1794
*portal_name ? portal_name : ),
errdetail(prepare: %s%s%s, sourceText,
/* optionally print bind parameters */
! bindText ? , bind: : ,
bindText ? bindText : )));
BeginCommand(portal-commandTag, dest);
***
*** 1896,1902
*portal_name ? portal_name : ),
errdetail(prepare: %s%s%s, sourceText,
/* optionally print bind parameters */
! bindText ? bind: : ,
bindText ? bindText : )));
}
}
--- 1902,1908
*portal_name ? portal_name : ),
errdetail(prepare: %s%s%s, sourceText,
/* optionally print bind parameters */
! bindText ? , bind: : ,
bindText ? bindText : )));
}
}
---(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
Michael Glaesemann wrote: 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. Here are the results using my newest patch: test= 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) test= 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:24:00 | -1 years -6 days +122:24:00 | -1 years -12 days -122:24:00 (1 row) I see no 23:60 entries. I realize the problem with my first patch. I was rounding at the 'seconds' level, but that is too late in the process. The rounding has to happen right after the division. In fact the only rounding problem I can find is with month_remainder_days, because of a division by factor, and a multiplication to convert it to days. The combination of steps is where the rounding problem is happening. The patch is even smaller now. The code assume if it is within 0.01 of a whole number, it should be rounded to a whole number. Patch attached with comments added. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: src/backend/utils/adt/timestamp.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v retrieving revision 1.165 diff -c -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 22:04:41 - *** *** 2518,2523 --- 2518,2530 /* 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 + TSROUND(month_remainder_days) == rint(month_remainder_days)) + month_remainder_days = rint(month_remainder_days); result-day += (int32) month_remainder_days; /* fractional months partial days into time */ day_remainder += month_remainder_days -
Re: [PATCHES] updated patch for selecting large results sets in psql using cursors
[EMAIL PROTECTED] writes: here comes the latest version (version 7) of the patch to handle large result sets with psql. As previously discussed, a cursor is used for SELECT queries when \set FETCH_COUNT some_value 0 Applied with revisions ... I didn't like the fact that the code was restricted to handle only unaligned output format, so I fixed print.c to be able to deal with emitting output in sections. This is not ideal for aligned output mode, because we compute column widths separately for each FETCH group, but all the other output modes work nicely. I also did a little hacking to make \timing and pager output work as expected. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Interval aggregate regression failure (expected seems
On Aug 30, 2006, at 7:12 , Bruce Momjian wrote: Here are the results using my newest patch: test= 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) test= 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:24:00 | -1 years -6 days +122:24:00 | -1 years -12 days -122:24:00 (1 row) I see no 23:60 entries. Using Bruce's newest patch, I still get the 23:60 entries on my machine (no integer-datetimes) select version(); version - PostgreSQL 8.2devel on powerpc-apple-darwin8.7.0, compiled by GCC powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5341) (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 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 code assume if it is within 0.01 of a whole number, it should be rounded to a whole number. Patch attached with comments added. /* 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 + TSROUND(month_remainder_days) == rint(month_remainder_days)) + month_remainder_days = rint(month_remainder_days); result-day += (int32) month_remainder_days; Don't we want to be checking for rounding at the usec level rather than 0.01 of a day? I think this should be 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); Another question I have concerns the month_remainder_days != (int32) month_remainder_days comparison. If I understand it correctly, if the TSROUND == rint portion is true, the first part is true. Or is this just a quick, fast check to see if it's necessary to do a more computationally intensive check? TSROUND isn't defined for HAVE_INT64_TIMESTAMP. My first attempt at performing a corresponding comparison doesn't work: + 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 +
Re: [PATCHES] [HACKERS] Interval aggregate regression failure
Michael Glaesemann wrote:
On Aug 30, 2006, at 7:12 , Bruce Momjian wrote:
Here are the results using my newest patch:
test= 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)
test= 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:24:00 | -1 years -6
days +122:24:00 | -1 years -12 days -122:24:00
(1 row)
I see no 23:60 entries.
Using Bruce's newest patch, I still get the 23:60 entries on my
machine (no integer-datetimes)
Strange, I do not see that here. Is there something wrong with our
hour/minute display? Someone posted a patch a few days ago for that.
Here is a test program. What does it show for you?
#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;
}
The output for me is:
4.100
2.989
3.000
select version();
version
-
PostgreSQL 8.2devel on powerpc-apple-darwin8.7.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
build 5341)
(1 row)
Powerpc. Hmmm. I am on Intel.
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)
Yea, I see that -122:23:60.00.
The code assume if it is within 0.01 of a whole number, it
should be
rounded to a whole number. Patch attached with comments added.
/* 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
+ TSROUND(month_remainder_days) == rint(month_remainder_days))
+ month_remainder_days = rint(month_remainder_days);
result-day += (int32) month_remainder_days;
Don't we want to be checking for rounding at the usec level rather
than 0.01 of a day? I think this should be
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 =
Re: [PATCHES] Interval month, week - day
The masks don't need changing.
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:
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.c 25 Jul 2006 03:51:21 - 1.169
--- src/backend/utils/adt/datetime.c 27 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;
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] Performance testing of COPY (SELECT) TO
Hi, as per your suggestion, the COPY view TO support was cut and a hint was added. Please, review. Best regards, Zoltán Böszörményi pgsql-copyselect-12.patch.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
