[PATCHES] list of scalars for fors and fore stms II
Hello I cut controvers support for assign stmt. This patch is only enhancing fore and fors stmts. Regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ diff -c -r pgsql/doc/src/sgml/plpgsql.sgml pgsql.new/doc/src/sgml/plpgsql.sgml *** pgsql/doc/src/sgml/plpgsql.sgml 2005-12-08 19:02:04.0 +0100 --- pgsql.new/doc/src/sgml/plpgsql.sgml 2005-12-26 08:41:20.0 +0100 *** *** 2008,2018 accordingly. The syntax is: synopsis optional lt;lt;replaceablelabel/replaceablegt;gt; /optional ! FOR replaceablerecord_or_row/replaceable IN replaceablequery/replaceable LOOP replaceablestatements/replaceable END LOOP optional replaceablelabel/replaceable /optional; /synopsis ! The record or row variable is successively assigned each row resulting from the replaceablequery/replaceable (which must be a commandSELECT/command command) and the loop body is executed for each row. Here is an example: --- 2008,2020 accordingly. The syntax is: synopsis optional lt;lt;replaceablelabel/replaceablegt;gt; /optional ! FOR replaceabletarget/replaceable IN replaceablequery/replaceable LOOP replaceablestatements/replaceable END LOOP optional replaceablelabel/replaceable /optional; /synopsis ! replaceableTarget/replaceable is a record variable, row variable, ! or a comma-separated list of simple variables and record/row fields ! which is successively assigned each row resulting from the replaceablequery/replaceable (which must be a commandSELECT/command command) and the loop body is executed for each row. Here is an example: *** *** 2047,2053 rows: synopsis optional lt;lt;replaceablelabel/replaceablegt;gt; /optional ! FOR replaceablerecord_or_row/replaceable IN EXECUTE replaceabletext_expression/replaceable LOOP replaceablestatements/replaceable END LOOP optional replaceablelabel/replaceable /optional; /synopsis --- 2049,2055 rows: synopsis optional lt;lt;replaceablelabel/replaceablegt;gt; /optional ! FOR replaceabletarget/replaceable IN EXECUTE replaceabletext_expression/replaceable LOOP replaceablestatements/replaceable END LOOP optional replaceablelabel/replaceable /optional; /synopsis *** *** 2067,2073 literalIN/ and literalLOOP/. If literal../ is not seen then the loop is presumed to be a loop over rows. Mistyping the literal../ is thus likely to lead to a complaint along the lines of ! quoteloop variable of loop over rows must be a record or row variable/, rather than the simple syntax error one might expect to get. /para /note --- 2069,2075 literalIN/ and literalLOOP/. If literal../ is not seen then the loop is presumed to be a loop over rows. Mistyping the literal../ is thus likely to lead to a complaint along the lines of ! quoteloop variable of loop over rows must be a record or row or scalar variable/, rather than the simple syntax error one might expect to get. /para /note diff -c -r pgsql/src/pl/plpgsql/src/gram.y pgsql.new/src/pl/plpgsql/src/gram.y *** pgsql/src/pl/plpgsql/src/gram.y 2005-10-13 17:34:19.0 +0200 --- pgsql.new/src/pl/plpgsql/src/gram.y 2005-12-26 12:29:10.0 +0100 *** *** 58,64 static void plpgsql_sql_error_callback(void *arg); static void check_labels(const char *start_label, const char *end_label); ! %} %union { --- 58,66 static void plpgsql_sql_error_callback(void *arg); static void check_labels(const char *start_label, const char *end_label); ! static PLpgSQL_row *make_scalar_list1(const char *name, ! PLpgSQL_datum *variable); ! %} %union { *** *** 76,81 --- 78,84 int lineno; PLpgSQL_rec *rec; PLpgSQL_row *row; + PLpgSQL_datum *scalar; } forvariable; struct { *** *** 884,893 new-rec = $2.rec; else if ($2.row) new-row = $2.row; else { plpgsql_error_lineno = $1; ! yyerror(loop variable of loop over rows must be a record or row variable); } new-query = expr; --- 887,898 new-rec = $2.rec; else if ($2.row) new-row = $2.row; + else if ($2.scalar) + new-row = make_scalar_list1($2.name, $2.scalar); else { plpgsql_error_lineno = $1; ! yyerror(loop variable of loop over rows must be a record or row or scalar variable); } new-query = expr; *** *** 942,947 --- 947,961 expr2 = plpgsql_read_expression(K_LOOP, LOOP); + /* T_SCALAR identifier waits
Re: [PATCHES] [BUGS] BUG #2114: (patch) COPY FROM ... end of copy
Bruce Momjian wrote: The big problem is that \. is also a valid CSV data value (though not a valid non-CSV data value). So, the solution we came up with was to require \. to appear alone on a line in CSV mode for it to be treated as end-of-copy. According to the docs, that's the way to specify EOD in both text and CSV mode: End of data can be represented by a single line containing just backslash-period. Your analysis regarding line_buf.len seems correct. We probably should have a regression test with \. in a CSV field. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [BUGS] BUG #2114: (patch) COPY FROM ... end of copy marker
Andrew Dunstan wrote: Bruce Momjian wrote: The big problem is that \. is also a valid CSV data value (though not a valid non-CSV data value). So, the solution we came up with was to require \. to appear alone on a line in CSV mode for it to be treated as end-of-copy. According to the docs, that's the way to specify EOD in both text and CSV mode: End of data can be represented by a single line containing just backslash-period. Right, but in non-CSV mode, we allow \. at the end of any line because it is unique so I kept that behavior. That is not documented however. Your analysis regarding line_buf.len seems correct. We probably should have a regression test with \. in a CSV field. Agreed. My test for CSV was simple, just try loading: x\. x\.b \.c all should load literally, but they fail. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [BUGS] BUG #2114: (patch) COPY FROM ... end of copy marker
Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: The big problem is that \. is also a valid CSV data value (though not a valid non-CSV data value). So, the solution we came up with was to require \. to appear alone on a line in CSV mode for it to be treated as end-of-copy. According to the docs, that's the way to specify EOD in both text and CSV mode: End of data can be represented by a single line containing just backslash-period. Right, but in non-CSV mode, we allow \. at the end of any line because it is unique so I kept that behavior. That is not documented however. Your analysis regarding line_buf.len seems correct. We probably should have a regression test with \. in a CSV field. Agreed. My test for CSV was simple, just try loading: x\. x\.b \.c all should load literally, but they fail. OK, original patch applied to HEAD and smaller version to 8.1.X, and regression test added, now attached. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: src/test/regress/expected/copy2.out === RCS file: /cvsroot/pgsql/src/test/regress/expected/copy2.out,v retrieving revision 1.22 diff -c -c -r1.22 copy2.out *** src/test/regress/expected/copy2.out 26 Jun 2005 03:04:18 - 1.22 --- src/test/regress/expected/copy2.out 27 Dec 2005 18:19:36 - *** *** 194,199 --- 194,202 --test that we read consecutive LFs properly CREATE TEMP TABLE testnl (a int, b text, c int); COPY testnl FROM stdin CSV; + -- test end of copy marker + CREATE TEMP TABLE testeoc (a text); + COPY testeoc FROM stdin CSV; DROP TABLE x, y; DROP FUNCTION fn_x_before(); DROP FUNCTION fn_x_after(); Index: src/test/regress/sql/copy2.sql === RCS file: /cvsroot/pgsql/src/test/regress/sql/copy2.sql,v retrieving revision 1.13 diff -c -c -r1.13 copy2.sql *** src/test/regress/sql/copy2.sql 26 Jun 2005 03:04:37 - 1.13 --- src/test/regress/sql/copy2.sql 27 Dec 2005 18:19:36 - *** *** 139,144 --- 139,153 inside,2 \. + -- test end of copy marker + CREATE TEMP TABLE testeoc (a text); + + COPY testeoc FROM stdin CSV; + a\. + \.b + c\.d + \. + DROP TABLE x, y; DROP FUNCTION fn_x_before(); ---(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] [BUGS] BUG #2114: (patch) COPY FROM ... end of
Bruce, On 12/27/05 10:20 AM, Bruce Momjian pgman@candle.pha.pa.us wrote: OK, original patch applied to HEAD and smaller version to 8.1.X, and regression test added, now attached. Great, good catch. Have you tested performance, before and after? The only good way to test performance is using a fast enough I/O subsystem that you are CPU-bound, which means 60MB/s of write speed. I'd be happy to get you an account on one. - Luke ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [BUGS] BUG #2114: (patch) COPY FROM ... end of
Luke Lonergan wrote: Bruce, On 12/27/05 10:20 AM, Bruce Momjian pgman@candle.pha.pa.us wrote: OK, original patch applied to HEAD and smaller version to 8.1.X, and regression test added, now attached. Great, good catch. Have you tested performance, before and after? The only good way to test performance is using a fast enough I/O subsystem that you are CPU-bound, which means 60MB/s of write speed. I'd be happy to get you an account on one. I don't need to test performance because it is the same code, just with macros and the two functions merged. I do have an optimization for that loop but I saw no improvement so I didn't apply it. It was basically to advance the pointer in a tight look just checking for \r, \n, and \\, but it seems the larger loop isn't much slower than a tight one. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [BUGS] BUG #2114: (patch) COPY FROM ... end of
pgman wrote: Luke Lonergan wrote: Bruce, On 12/27/05 10:20 AM, Bruce Momjian pgman@candle.pha.pa.us wrote: OK, original patch applied to HEAD and smaller version to 8.1.X, and regression test added, now attached. Great, good catch. Have you tested performance, before and after? The only good way to test performance is using a fast enough I/O subsystem that you are CPU-bound, which means 60MB/s of write speed. I'd be happy to get you an account on one. I don't need to test performance because it is the same code, just with macros and the two functions merged. I do have an optimization for that loop but I saw no improvement so I didn't apply it. It was basically to advance the pointer in a tight look just checking for \r, \n, and \\, but it seems the larger loop isn't much slower than a tight one. Here is the patch I was testing. I don't see any win on my hardware, but it isn't a huge machine, so you might see an improvement on your hardware, and if you do, we can consider applying it. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: src/backend/commands/copy.c === RCS file: /cvsroot/pgsql/src/backend/commands/copy.c,v retrieving revision 1.257 diff -c -c -r1.257 copy.c *** src/backend/commands/copy.c 28 Dec 2005 03:25:32 - 1.257 --- src/backend/commands/copy.c 28 Dec 2005 03:39:42 - *** *** 2207,2212 --- 2207,2224 need_data = false; } + if (!cstate-encoding_embeds_ascii !cstate-csv_mode) + { + char *p = copy_raw_buf + raw_buf_ptr; + char *e = copy_raw_buf + copy_buf_len; + + while (p e *p != '\r' *p != '\n' *p != '\\') + p++; + prev_raw_ptr = raw_buf_ptr = p - copy_raw_buf; + IF_NEED_REFILL_AND_NOT_EOF_CONTINUE(0); + IF_NEED_REFILL_AND_EOF_BREAK(0); + } + /* OK to fetch a character */ prev_raw_ptr = raw_buf_ptr; c = copy_raw_buf[raw_buf_ptr++]; ---(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