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
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 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] 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; > !
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 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_day
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)) + #endi
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
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 */
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
[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: [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
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
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] 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: [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: [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