Re: [PATCHES] From latin9 to sql_ascii??
--- Jaime Casanova [EMAIL PROTECTED] escribió: --- Tom Lane [EMAIL PROTECTED] escribió: Jaime Casanova [EMAIL PROTECTED] writes: = select to_ascii('Jiménez'); will retrieve 'Jimenez' at least it works on Latin1 encoding. Why it not work on Latin9, Probably because it hasn't got a table for Latin9. Feel free to contribute one --- see src/backend/utils/adt/ascii.c. This page shows the differences between Latin1 Latin9: http://www.cs.tut.fi/~jkorpela/latin9.html The diffs are: 164: the euro symbol. (sql_ascii = 'E')??? 166: an S with a symbol above (sql_ascii = 'S') 168: the same but lower case(sql_ascii = 's') 180: an Z with a symbol above (sql_ascii = 'Z') 184: the same but lower case(sql_ascii = 'z') 188: it's an O merge with an E (sql_ascii = '')??? 189: the same but lower case(sql_ascii = '')??? 190: an Y with a 2 points above (sql_ascii = 'Y') Comments? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ascii.patch Description: ascii.patch ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] From latin9 to sql_ascii??
--- Peter Eisentraut [EMAIL PROTECTED] escribió: Jaime Casanova wrote: 188: it's an O merge with an E (sql_ascii = '')??? 189: the same but lower case(sql_ascii = '')??? 'OE' and 'oe', most likely, but someone more familiar with French typography might correct me. Something like that, i really doesn't know how to convert to sql_ascii that. Maybe just blank like Tom suggest about the euro symbol regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] From latin9 to sql_ascii??
--- Tom Lane [EMAIL PROTECTED] escribió: Jaime Casanova [EMAIL PROTECTED] writes: Why it not work on Latin9, Probably because it hasn't got a table for Latin9. Feel free to contribute one --- see src/backend/utils/adt/ascii.c. This page shows the differences between Latin1 Latin9: http://www.cs.tut.fi/~jkorpela/latin9.html The diffs are: 164: the euro symbol. (sql_ascii = 'E')??? 166: an S with a symbol above (sql_ascii = 'S') 168: the same but lower case(sql_ascii = 's') 180: an Z with a symbol above (sql_ascii = 'Z') 184: the same but lower case(sql_ascii = 'z') 188: it's an O merge with an E (sql_ascii = '')??? 189: the same but lower case(sql_ascii = '')??? 190: an Y with a 2 points above (sql_ascii = 'Y') Comments? Works for me. Anyone feel this is too big a change to push into 8.0? Strictly speaking it's a new feature, but it sure looks harmless from here. You guys have the code, you guys have the power. I don't think it can cause any problem. :) Personally I'd say that the euro symbol should map to ' ' not 'E', but am not set on that. Maybe, someone that uses the euro symbol can comment?? if not, and you said that we can just map that symbol to ' '. Here's the *fixed* patch it's up to you wich one to use. regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com*** src/backend/utils/adt/ascii.c.orig 2004-08-29 00:06:49.0 -0500 --- src/backend/utils/adt/ascii.c 2004-12-17 23:02:01.0 -0500 *** *** 53,58 --- 53,66 ascii = A L LS \SSTZ-ZZ a,l'ls ,sstz\zzRLCCCIIDDNNxRYTBrlccciiddnn/ryt.; range = RANGE_160; } + else if (enc == PG_LATIN9) + { + /* +* ISO-8859-15 range: 160 -- 255 +*/ + ascii = cL YS sCa -R Zu .z EeY?AAAC NOxOYTBaaac no/oyty; + range = RANGE_160; + } else if (enc == PG_WIN1250) { /* ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PATCHES] Remove second argument from textToQualifiedNameList
Hi, i found out that the function textToQualifiedNameList doesn't use the second argument it receive (caller). i suppose in the past was used and now it is useless, if that is the case here is a patch removing. Or are any reasons to keep that argument? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) diff -cr pgsql_head/contrib/dblink/dblink.c pgsql_args/contrib/dblink/dblink.c *** pgsql_head/contrib/dblink/dblink.c 2005-05-25 22:14:11.0 -0500 --- pgsql_args/contrib/dblink/dblink.c 2005-05-25 22:21:39.0 -0500 *** *** 1971,1977 Relationrel; Oid relid; ! relvar = makeRangeVarFromNameList(textToQualifiedNameList(relname_text, get_relid_from_relname)); rel = heap_openrv(relvar, AccessShareLock); relid = RelationGetRelid(rel); relation_close(rel, AccessShareLock); --- 1971,1977 Relationrel; Oid relid; ! relvar = makeRangeVarFromNameList(textToQualifiedNameList(relname_text)); rel = heap_openrv(relvar, AccessShareLock); relid = RelationGetRelid(rel); relation_close(rel, AccessShareLock); diff -cr pgsql_head/contrib/dbsize/dbsize.c pgsql_args/contrib/dbsize/dbsize.c *** pgsql_head/contrib/dbsize/dbsize.c 2005-05-25 22:14:12.0 -0500 --- pgsql_args/contrib/dbsize/dbsize.c 2005-05-25 22:22:15.0 -0500 *** *** 290,297 Oid relnodeOid; Oid tblspcOid; ! relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname, ! relation_size)); relation = relation_openrv(relrv, AccessShareLock); tblspcOid = relation-rd_rel-reltablespace; --- 290,296 Oid relnodeOid; Oid tblspcOid; ! relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname)); relation = relation_openrv(relrv, AccessShareLock); tblspcOid = relation-rd_rel-reltablespace; diff -cr pgsql_head/contrib/pgstattuple/pgstattuple.c pgsql_args/contrib/pgstattuple/pgstattuple.c *** pgsql_head/contrib/pgstattuple/pgstattuple.c2005-05-25 22:14:13.0 -0500 --- pgsql_args/contrib/pgstattuple/pgstattuple.c2005-05-25 22:22:54.0 -0500 *** *** 63,70 Datum result; /* open relation */ ! relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname, ! pgstattuple)); rel = heap_openrv(relrv, AccessShareLock); result = pgstattuple_real(rel); --- 63,69 Datum result; /* open relation */ ! relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname)); rel = heap_openrv(relrv, AccessShareLock); result = pgstattuple_real(rel); diff -cr pgsql_head/src/backend/catalog/pg_conversion.c pgsql_args/src/backend/catalog/pg_conversion.c *** pgsql_head/src/backend/catalog/pg_conversion.c 2005-05-25 22:17:33.0 -0500 --- pgsql_args/src/backend/catalog/pg_conversion.c 2005-05-25 22:24:10.0 -0500 *** *** 298,304 *(str + len) = '\0'; /* Look up the conversion name */ ! parsed_name = textToQualifiedNameList(conv_name, convert_using); convoid = FindConversionByName(parsed_name); if (!OidIsValid(convoid)) ereport(ERROR, --- 298,304 *(str + len) = '\0'; /* Look up the conversion name */ ! parsed_name = textToQualifiedNameList(conv_name); convoid = FindConversionByName(parsed_name); if (!OidIsValid(convoid)) ereport(ERROR, diff -cr pgsql_head/src/backend/commands/sequence.c pgsql_args/src/backend/commands/sequence.c *** pgsql_head/src/backend/commands/sequence.c 2005-05-25 22:17:34.0 -0500 --- pgsql_args/src/backend/commands/sequence.c 2005-05-25 22:26:35.0 -0500 *** *** 387,394 rescnt = 0; boollogit = false; ! sequence = makeRangeVarFromNameList(textToQualifiedNameList(seqin, ! nextval)); /* open and AccessShareLock sequence */ init_sequence(sequence, elm, seqrel); --- 387,393 rescnt = 0; boollogit = false; ! sequence = makeRangeVarFromNameList(textToQualifiedNameList(seqin)); /* open and AccessShareLock sequence */ init_sequence(sequence, elm, seqrel); *** *** 579,586 Relationseqrel; int64 result
Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT
On 7/29/05, Tom Lane [EMAIL PROTECTED] wrote: Matt Miller [EMAIL PROTECTED] writes: This patch implements an optional EXACT keyword after the INTO keyword of the PL/pgSQL SELECT INTO command. The motivation is to come closer to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, raise an exception and leave the targets untouched if the query does not return exactly one row. This patch does not go so far as to raise an exception, but it can simplify porting efforts from PL/SQL. Uh, what's the point of being only sort-of compatible? Why not throw the exception? I dislike the choice of EXACT, too, as it (a) adds a new reserved word and (b) doesn't seem to convey quite what is happening anyway. Not sure about a better word though ... anyone? regards, tom lane just wonder, why that is not the default behavior of the SELECT INTO? at least, the first time i think the function was right until i found that the first row of a set of rows was assigned... i mean, when you do that code you are expecting just one row from your query, doesn't you? -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] running script on server shutdown (TODO)
On 12/10/05, Tom Lane [EMAIL PROTECTED] wrote: Andrew Dunstan [EMAIL PROTECTED] writes: This is what bothers me about having such an informal TODO list. There is a danger that people will work in items only to have them shot down, which is a great way to turn off developers. And there is also a danger that other people will think that the todo item is likely to be accepted at some stage. I've complained to Bruce about that before --- there are a number of items on TODO that only one person thinks is a good idea. Perhaps some sort of [controversial] marker would be useful to warn people that the item needs more discussion before going off in a corner and trying to implement it. regards, tom lane Actually some items are marked with a '?' that shows that that item needs discussion... although that it's not clearly stated in no where in the TODO... Maybe be explicit about what the '?' mark means and mark every new item with it until there is concensus a on it -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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] Inconsistent syntax in GRANT
On 1/6/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Jaime Casanova wrote: On 1/6/06, Tom Lane [EMAIL PROTECTED] wrote: Marko Kreen [EMAIL PROTECTED] writes: But my question is rather - is there any scenario where setval() should go with nextval()? It seems that their pairing is an accident and should be fixed. I think the original argument for the current design was that with enough nextval's you can duplicate the effect of a setval. This is only strictly true if the sequence is CYCLE mode, and even then it'd take a whole lot of patience to wrap an int8 sequence around ... but the distinction between them is not so large as you make it out to be. In any case I think we are wasting our time discussing it, and instead should be looking through the SQL2003 spec to see what it requires. 5WD-02-Foundation-2003-09.pdf look at: 4.34.2 Privileges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 and 12.3 privileges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 739 this is taken from the 12.3 3) If object name specifies a domain name, collation name, character set name, transliteration name, schema-resolved user-defined type name, or sequence generator name, then privileges may specify USAGE. Otherwise, USAGE shall not be specified. Yes, I saw that, but how does that hook into nextval/setval/currval()? I think I see it in 6.13: a) If next value expression is contained in a schema definition, then the applicable privileges for the authorization identifier that owns the containing schema shall include USAGE privilege on the sequence generator identified by sequence generator name. Is that it? USAGE is the only privilege that the standard consider applicable for the owner? it covers all operations in sequences... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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] message for constraint
On 1/16/06, Bruce Momjian pgman@candle.pha.pa.us wrote: I have added the following documentation addition to suggest this usage for constraint names. --- --- 293,304 termliteralCONSTRAINT replaceable class=PARAMETERconstraint_name/replaceable/literal/term listitem para ! An optional name for a column or table constraint. If the ! constraint is violated, the constraint name is present in error messages, ! so constraint names like literalcol must be positive/ can be used ! to communicate helpful constraint information to client applications. ! (Double-quotes are required for constraint names that contain spaces.) ! If not specified, the system generates a name. /para /listitem /varlistentry a lot better... thanx... what about lower the context part of the messages for plpgsql functions? that seems debug info for me... just an idea ;) pruebas=# select prueba(); ERROR: new row for relation foo violates check constraint foo_fld_check CONTEXT: SQL statement insert into foo values (-1) PL/pgSQL function prueba line 2 at SQL statement -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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] message for constraint
On 1/16/06, Alvaro Herrera [EMAIL PROTECTED] wrote: Jaime Casanova wrote: On 1/16/06, Bruce Momjian pgman@candle.pha.pa.us wrote: what about lower the context part of the messages for plpgsql functions? that seems debug info for me... just an idea ;) pruebas=# select prueba(); ERROR: new row for relation foo violates check constraint foo_fld_check CONTEXT: SQL statement insert into foo values (-1) PL/pgSQL function prueba line 2 at SQL statement It's not debug info in the sense of the DEBUG error levels. These are meant as debug info of the server itself, like, say, a transaction started. The CONTEXT line is exactly that, context information. ok... maybe notice? log? i suppose that the ERROR line is error level so the idea is lowering the context so i can put client_min_messages='error' and see just what the user can understand... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] ignore_killed_tuples is always true
On 2/10/06, Tom Lane [EMAIL PROTECTED] wrote: ITAGAKI Takahiro [EMAIL PROTECTED] writes: I found IndexScanDesc-ignore_killed_tuples is always true. Is this still needed? What is the point of removing it? You cannot argue that saving one if-test per tuple is a worthwhile speedup. regards, tom lane to clean code? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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] allow select from void function in void sql function
On 3/16/06, Pavel Stehule [EMAIL PROTECTED] wrote: Hello this small patch allow use sql for creating wrappers others void functions. Currently void SQL function have to be empty. create or replace function foo(int) returns void as ' ','' language c ..; create or replace function foo() returns void as $$ select foo(10); $$language sql; regards Pavel i'm not clear on what the use case of this can be... of course, my opinion is not very important :) -- regards, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz ---(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] fori stmt with by keyword was:(Re: [HACKERS] for statement, adding a STEP clause?)
On 4/29/06, Andrew Dunstan [EMAIL PROTECTED] wrote: Tom Lane wrote: Jaime Casanova [EMAIL PROTECTED] writes: there is a chance to add a STEP clause to the FOR statement in plpgsql? This is not free: it'd require making STEP a reserved word (at least within plpgsql) which is contrary to spec. I think you need to make a pretty good case why the value of the feature outweighs breaking applications that have perfectly-legally used step as an identifier. This isn't available in PL/SQL, is it? That doesn't mean we shouldn't do it, of course, but it might lessen any perceived imperative. Maybe using BY instad of STEP as the keyword would make it easier, since its occurrence in SQL makes it less likely to be used as a variable. cheers andrew Hi, i make a little patch using BY instead of STEP per Tom's complaint and Andrew's suggestion. the patch is not ready yet because i can't figure out how to make the BY optional and that is mandatory because backward compatibility... the problem is how to manage it in gram.y perhaps someone with more expertise in gram.y can make suggestions? also, you can review the patch and say if it will be added if i can solve the optional BY problem... then i can start working in fixing the docs -- regards, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz diff -rciEb pgsql-8.2dev/src/pl/plpgsql/src/gram.y pgsql-8.2fori/src/pl/plpgsql/src/gram.y *** pgsql-8.2dev/src/pl/plpgsql/src/gram.y 2006-04-30 09:45:12.0 -0500 --- pgsql-8.2fori/src/pl/plpgsql/src/gram.y 2006-04-30 09:49:05.0 -0500 *** *** 143,148 --- 143,149 %token K_ALIAS %token K_ASSIGN %token K_BEGIN + %token K_BY %token K_CLOSE %token K_CONSTANT %token K_CONTINUE *** *** 930,935 --- 931,937 { /* Saw .., so it must be an integer loop */ PLpgSQL_expr *expr2; + PLpgSQL_expr *expr_by; PLpgSQL_var *fvar; PLpgSQL_stmt_fori *new; char*varname; *** *** 937,943 /* First expression is well-formed */ check_sql_expr(expr1-query); ! expr2 = plpgsql_read_expression(K_LOOP, LOOP); /* should have had a single variable name */ plpgsql_error_lineno = $2.lineno; --- 939,951 /* First expression is well-formed */ check_sql_expr(expr1-query); ! expr2 = read_sql_construct(K_BY, K_LOOP, ! BY|LOOP, ! SELECT , true, ! false, tok); ! ! if (tok = K_BY) ! expr_by = plpgsql_read_expression(K_LOOP, LOOP); /* should have had a single variable name */ plpgsql_error_lineno = $2.lineno; *** *** 965,970 --- 973,979 new-reverse = reverse; new-lower = expr1; new-upper = expr2; + new-by = expr_by; $$ = (PLpgSQL_stmt *) new; } diff -rciEb pgsql-8.2dev/src/pl/plpgsql/src/pl_exec.c pgsql-8.2fori/src/pl/plpgsql/src/pl_exec.c *** pgsql-8.2dev/src/pl/plpgsql/src/pl_exec.c 2006-04-30 09:45:12.0 -0500 --- pgsql-8.2fori/src/pl/plpgsql/src/pl_exec.c 2006-04-30 09:49:54.0 -0500 *** *** 1346,1352 /* -- * exec_stmt_fori Iterate an integer variable ! * from a lower to an upper value. * Loop can be left with exit. * -- */ --- 1346,1353 /* -- * exec_stmt_fori Iterate an integer variable ! * from a lower to an upper value ! * incrementing or decrementing in BY value * Loop can be left with exit. * -- */ *** *** 1355,1360 --- 1356,1362 { PLpgSQL_var *var; Datum value; + Datum by_value; Oid valtype; bool isnull; bool found = false; *** *** 1393,1398 --- 1395,1414 exec_eval_cleanup(estate); /* + * Get the by value + */ + by_value = exec_eval_expr(estate, stmt-by, isnull, valtype); + by_value = exec_cast_value(by_value, valtype, var-datatype-typoid, + (var-datatype-typinput), + var-datatype-typioparam, + var-datatype-atttypmod, isnull); + + /* If there is no BY, then we assume 1 */ + if (isnull) + by_value = (Datum) 1; + exec_eval_cleanup(estate); + + /* * Now do the loop */ for (;;) *** *** 1468,1476 * Increase/decrease loop var */ if (stmt-reverse) ! var-value--; else ! var-value++; } /* --- 1484,1492 * Increase/decrease loop var */ if (stmt-reverse) ! var-value -= by_value; else ! var-value += by_value; } /* diff -rciEb pgsql-8.2dev/src/pl/plpgsql/src/plpgsql.h pgsql-8.2fori/src/pl/plpgsql/src/plpgsql.h *** pgsql-8.2dev/src/pl/plpgsql/src
Re: [PATCHES] fori stmt with by keyword was:(Re: [HACKERS] for statement, adding a STEP clause?)
On 4/30/06, Jaime Casanova [EMAIL PROTECTED] wrote: On 4/29/06, Andrew Dunstan [EMAIL PROTECTED] wrote: Tom Lane wrote: Jaime Casanova [EMAIL PROTECTED] writes: there is a chance to add a STEP clause to the FOR statement in plpgsql? This is not free: it'd require making STEP a reserved word (at least within plpgsql) which is contrary to spec. I think you need to make a pretty good case why the value of the feature outweighs breaking applications that have perfectly-legally used step as an identifier. This isn't available in PL/SQL, is it? That doesn't mean we shouldn't do it, of course, but it might lessen any perceived imperative. Maybe using BY instad of STEP as the keyword would make it easier, since its occurrence in SQL makes it less likely to be used as a variable. cheers andrew Hi, i make a little patch using BY instead of STEP per Tom's complaint and Andrew's suggestion. the patch is ready, at least it seems to me... also i have added some lines to the docs... let me know what your decision is about this... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook diff -rcEib pgsql-8.2dev/doc/src/sgml/plpgsql.sgml pgsql-8.2fori/doc/src/sgml/plpgsql.sgml *** pgsql-8.2dev/doc/src/sgml/plpgsql.sgml 2006-05-01 08:49:20.0 -0500 --- pgsql-8.2fori/doc/src/sgml/plpgsql.sgml 2006-05-05 17:31:36.0 -0500 *** *** 1960,1966 synopsis optional lt;lt;replaceablelabel/replaceablegt;gt; /optional ! FOR replaceablename/replaceable IN optional REVERSE /optional replaceableexpression/replaceable .. replaceableexpression/replaceable LOOP replaceablestatements/replaceable END LOOP optional replaceablelabel/replaceable /optional; /synopsis --- 1960,1966 synopsis optional lt;lt;replaceablelabel/replaceablegt;gt; /optional ! FOR replaceablename/replaceable IN optional REVERSE /optional replaceableexpression/replaceable .. replaceableexpression/replaceable optional BY replaceableexpression/replaceable /optional LOOP replaceablestatements/replaceable END LOOP optional replaceablelabel/replaceable /optional; /synopsis *** *** 1973,1980 definition of the variable name is ignored within the loop). The two expressions giving the lower and upper bound of the range are evaluated once when entering ! the loop. The iteration step is normally 1, but is -1 when literalREVERSE/ is ! specified. /para para --- 1973,1982 definition of the variable name is ignored within the loop). The two expressions giving the lower and upper bound of the range are evaluated once when entering ! the loop. If the literalBY/ clause isn't specified the iteration ! step is 1 otherwise it's the value specified in the literalBY/ ! clause. If literalREVERSE/ is specified then the step value is ! considered negative. /para para *** *** 1988,1993 --- 1990,2000 FOR i IN REVERSE 10..1 LOOP -- some computations here END LOOP; + + FOR i IN REVERSE 10..1 BY 2 LOOP + -- some computations here + RAISE NOTICE 'i is %', i; + END LOOP; /programlisting /para diff -rcEib pgsql-8.2dev/src/pl/plpgsql/src/gram.y pgsql-8.2fori/src/pl/plpgsql/src/gram.y *** pgsql-8.2dev/src/pl/plpgsql/src/gram.y 2006-05-01 08:49:39.0 -0500 --- pgsql-8.2fori/src/pl/plpgsql/src/gram.y 2006-05-05 17:42:08.0 -0500 *** *** 143,148 --- 143,149 %token K_ALIAS %token K_ASSIGN %token K_BEGIN + %token K_BY %token K_CLOSE %token K_CONSTANT %token K_CONTINUE *** *** 930,935 --- 931,937 { /* Saw .., so it must be an integer loop */ PLpgSQL_expr *expr2; + PLpgSQL_expr *expr_by; PLpgSQL_var *fvar; PLpgSQL_stmt_fori *new; char*varname; *** *** 937,943 /* First expression is well-formed */ check_sql_expr(expr1-query); ! expr2 = plpgsql_read_expression(K_LOOP, LOOP); /* should have had a single variable name */ plpgsql_error_lineno = $2.lineno; --- 939,968 /* First expression is well-formed */ check_sql_expr(expr1-query); ! ! expr2 = read_sql_construct(K_BY, ! K_LOOP, ! LOOP, ! SELECT , ! true, ! false, ! tok); ! ! if (tok == K_BY) ! expr_by = plpgsql_read_expression(K_LOOP, LOOP); ! else ! { ! /* ! * If there is no BY clause we will assume 1
Re: [PATCHES] fori stmt with by keyword was:(Re: [HACKERS] for statement,
On 5/30/06, Bruce Momjian pgman@candle.pha.pa.us wrote: I went to test this patch and got the attached regression failures. Please repair and resubmit. Thanks. did it. sorry for the delay, i was busy this week. -- Atentamente, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook fori.patch Description: Binary data ---(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] Mark change-on-restart-only values in postgresql.conf
I removed comments about commenting out behavior too, because patch now waiting for commit (or reject?). Zdenek not a big deal but, i think, you should remove this in the patch that implements that behavior rather than in a newer one that still must be discussed. BTW, you have a typo in this line + # All file locations settings change require server restart to tak effect. -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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] Patch for updatable views
On 7/25/06, Bernd Helmle [EMAIL PROTECTED] wrote: Hi folks, please find attached an implementation for updatable views. Included are support for pg_dump and information_schema, regression test and documentation are missing. Also, there's currently no upgrade path for older PostgreSQL versions and user defined rules on views. i'm testing the functionality... seems good to me... i will work on docs and regress if no one objects and bernd is not doing it... AFAICS, the view will not be updateable if there are casts in the select list (seems fair to let that to future revisions), but i think we must say it. One thing to think of: create table testing_serial (id serial primary key, name text); CREATE TABLE create view vtest_serial as select * from testing_serial; CREATE VIEW insert into vtest_serial values (default, 'name1'); psql:../view_test.sql:81: ERROR: null value in column id violates not-null constraint insert into vtest_serial(name) values ('name2'); psql:../view_test.sql:82: ERROR: null value in column id violates not-null constraint i still think that in updateable views we need to inherit the defaut value of the base table, i still see this code commented in rewriteHandler.c psql:../view_test.sql:73: ERROR: cannot insert into a view HINT: You need an unconditional ON INSERT DO INSTEAD rule. BTW, we must change this message for something more like 'cannot insert into a non updateable view' - + /* +* I will do this only in case of relkind == RELKIND_VIEW. +* This is the last attempt to get a value for expr before we +* consider that expr must be NULL. +*/ + /*if (expr == NULL rel-rd_rel-relkind == RELKIND_VIEW) */ + /*{ */ + /*expr = (Node *)makeNode(SetToDefault); */ + /*return expr; */ + /*}*/ + if this functionality will be accepted this is the time to discuss it otherwise drop this comment. With this code we still can create a different default for the view with ALTER TABLE ADD DEFAULT I have some code which drops the implicit created rules silently if someone wants to have its own rule, but this needs some discussion, i think. + #if 0 + /* +* Implicit rules should be dropped automatically when someone +* wants to have its *own* rules on the view. is_implicit is set +* to NO_OPTION_EXCPLICIT in this case so we drop all implicit +* rules on the specified event type immediately. +* +* ???FIXME: do we want this behavior??? + */ + + if ( ev_kind == NO_OPTION_EXPLICIT ) +deleteImplicitRulesOnEvent(event_relation, event_type); + #endif This is a must for compatibility with older versions. Otherwise we will have views with user defined rules and implicit rules that will have an unexpected behaviour. The patch covers the whole SQL92 functionality and doesn't create any rules, if a given view is considered not to be compatible with SQL92 definitions. I think is necessary to send some NOTICE when we can't create rules at all or when we can't create one of them (insert rules are not always created because they need all not-null without defaults columns to be in the select list) The supported syntax is CREATE VIEW foo AS [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] The check option is implemented as a conditional rule with a simple system function, which checks the given expression tree to be true or false and raises an error in the latter case. the check option is working for all cases i'm trying... Also, i have dropped support for updatable views which contains indexed array fields of tables (like SELECT foo[3], foo[2] FROM bar). These are treated non-updatable and someone needs his own rules here. -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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
On 8/24/06, Tom Lane [EMAIL PROTECTED] wrote: Bernd Helmle [EMAIL PROTECTED] writes: While working on Alvaro's suggestions to fix the code i got the opinion that we need to reject any attempts to name a user defined rule as _INSERT _NOTHING_INSERT _DELETE _NOTHING_DELETE _UPDATE _NOTHING_UPDATE If the code is dependent on recognizing names to know what it's doing, then I'd say you have a fundamentally broken approach. Consider adding a flag column to pg_rewrite to distinguish these rules, instead. Actually the code delete implicit rules based on a field added to pg_rewrite but that catalog has a unique index on ev_class, rulename: pg_rewrite_rel_rulename_index UNIQUE, btree (ev_class, rulename) i guess bernd's comment is about this index giving an error if we try to insert the new rule with the same name on the same event... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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] Tablespace for temporary objects and sort files
On 10/24/06, Albert Cervera Areny [EMAIL PROTECTED] wrote: Hi, I'm trying to introduce myself into postgresql development and I'm working on the tablespace for temporary objects and sort files TODO item. some comments from a non-hacker: your patch isn't doing nothing at all for temporary indexes... a quick search for GetDefaultTablespace() shows this places... [EMAIL PROTECTED]:~/PG_RELEASES/pgsql$ grep -lR GetDefaultTablespace * src/backend/commands/indexcmds.c src/backend/commands/tablecmds.c src/backend/commands/tablespace.c src/backend/executor/execMain.c src/include/commands/tablespace.h Now a question, why not using the same GetDefaultTablespace() with an argument indicating if the object is temporary, if it is get the default tablespace for temp objects else get the default tablespace for permanent object... just an idea... How can I test that the tablespace is correctly used for sort files? Is there an easy way? Or should I reduce work_mem to a minimum, populate the database with data and try an ORDER BY? yes, that seems to work... i reduce, just in case, work_mem, shared_buffers and temp_buffers... Now, PG_TEMP_FILES_DIR seems to add just pgsql_temp to the filename. i think you the function you have to modify here is make_database_relative() that adds base/#database_oid# at the beginning of the path of the file. Hope the diff and idents are ok. Please let me know if there's something wrong with them. diff -c is the way -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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] Tablespace for temporary objects and sort files
On 10/24/06, Albert Cervera Areny [EMAIL PROTECTED] wrote: Hi, I'm trying to introduce myself into postgresql development and I'm working on the tablespace for temporary objects and sort files TODO item. The attached patch shows what I've already done. The GUC is currently called temp_tablespaces. hi albert. are you working on this? i'm willing to help... if you are not working on this, i will make a try... comments on the original patch? AFAIR, it fails the regrress tests when executing initdb... more here http://archives.postgresql.org/pgsql-patches/2006-10/msg00141.php -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Tablespace for temporary objects and sort files
On 12/24/06, Albert Cervera Areny [EMAIL PROTECTED] wrote: Hi, yes I'm working on it. I've got a more recent version that doesn't crash on initdb and works with sort files. I've got a couple of things pending and will post it as soon as I can. If you want I could make a diff and send it to you in case you wanted to test/improve the patch. Refering to some of the comment I never answered (sorry about that) I prefer to use GetTempTablespace() as I think it's easier to read/understand than GetDefaultTablespace(true). And thanks for pointing to temporary indexes. There seems not to be temporary indexes but indexes of temporary tables, which could use GetTempTablespace() too... yeah, it was late and i was almost asleep... i was thinking in temporary sequences, but indexes on temporary tables it's not a bad idea too... that was the reason i think it's better to use the same GetDefaultTablespace() function it's less intrussive and is not directed to one particular object but all temp objects can benefit... i will wait your patch when you think is ready for discussion... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Tablespace for temporary objects and sort files
On 12/27/06, Albert Cervera Areny [EMAIL PROTECTED] wrote: Hi, here's a new version of the patch against HEAD with both, table and sort files working correctly for me. Regression tests work too. ok, i will test it a little ... what about temp tables created by SELECT INTO TEMP? look at src/backend/executor/execMain.c:OpenIntoRel() what about other temporary objects? I'd like to ask again the question I made on the first post as no answer was given at that time: The GetTempTablespace function correctly returns a different tablespace each time is called, but I store the position of the last tablespace used with an integer and iterate through the list of tablespaces each time. I tried to keep the iterator from call to call but I got a segfault, I imagine due to the memory context. Should I try to keep the iterator? How can I do it? i didn't read this last time, i will take a look at it now... when you post for the first time hackers where busy releasing 8.2.0, maybe they will pay more atention now :) -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Tablespace for temporary objects and sort files
On 12/27/06, Albert Cervera Areny [EMAIL PROTECTED] wrote: Hi, here's a new version of the patch against HEAD with both, table and sort files working correctly for me. Regression tests work too. I'd like to ask again the question I made on the first post as no answer was given at that time: The GetTempTablespace function correctly returns a different tablespace each time is called, but I store the position of the last tablespace used with an integer and iterate through the list of tablespaces each time. I tried to keep the iterator from call to call but I got a segfault, I imagine due to the memory context. Should I try to keep the iterator? How can I do it? Now I'm working on some regression tests that could be added to tablespace.source using something like: SET temp_tablespaces='testspace'; CREATE TEMP TABLE temp_foo(a VARCHAR); SELECT COUNT(pg_ls_dir('pg_tblspc/' || (SELECT oid FROM pg_catalog.pg_tablespace WHERE spcname='testspace' ) seems is working fine... i actually looked for the files in the tablespace directory... have you looked my past comments? -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Tablespace for temporary objects and sort files
On 1/8/07, Jaime Casanova [EMAIL PROTECTED] wrote: maybe once this patch is applied you can think on make indexes and [temp] sequences on temp tables use the same temp_tablespace that table is using... actually, the index part is easy... do you want to do it? -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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] Tablespace for temporary objects and sort files
On 1/9/07, Albert Cervera Areny [EMAIL PROTECTED] wrote: I don't have much time lately so if you're willing to work on it, please do. after doing the index part i revisited the patch again and saw that there is something fundamentally wrong here (sorry for no noticing that before :( ) your are using local backend variables for the iterator so two different backends will use to different variables... because of that if you have 3 temp tablespaces in your temp_tablespaces guc and start 100 backend and every one of them create just one temp table those 100 temp tables will be in the first temp tablespace... :( i will try to fix that as well... unless you want to do it, just tell me... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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: [pgsql-patches] [PATCHES] Tablespace for temporary objects and sort files
On 1/11/07, Albert Cervera Areny [EMAIL PROTECTED] wrote: Please, go on with that, I hadn't seen that problem. Indeed, I read Andrew answer to your question and I think it's a nice solution. yes... i'm always trying to kill flies with tanks... ;) i will use Andrew's suggestion... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [pgsql-patches] [PATCHES] Tablespace for temporary objects and sort files
On 1/11/07, Joshua D. Drake [EMAIL PROTECTED] wrote: On Thu, 2007-01-11 at 21:05 -0500, Jaime Casanova wrote: On 1/11/07, Albert Cervera Areny [EMAIL PROTECTED] wrote: Please, go on with that, I hadn't seen that problem. Indeed, I read Andrew answer to your question and I think it's a nice solution. yes... i'm always trying to kill flies with tanks... ;) Isn't that a little expensive on gas? maybe... but... what a beatiful explosion we can get... ;) seriously, attached's a new version of the patch... the patch use temp tablespaces for: - temp tables - temp files (generated by sorts and such) - indexes on temp tables the temp_tablespaces GUC still cannot be set from postgresql.conf, i will keep working on that but i have to understand the code... any comments on this patch? although it's not ready for apply it yet, i think Albert made a good work on it... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [pgsql-patches] [PATCHES] Tablespace for temporary objects and sort files
Sorry, patch attached this time... On 1/12/07, Jaime Casanova [EMAIL PROTECTED] wrote: On 1/11/07, Joshua D. Drake [EMAIL PROTECTED] wrote: On Thu, 2007-01-11 at 21:05 -0500, Jaime Casanova wrote: On 1/11/07, Albert Cervera Areny [EMAIL PROTECTED] wrote: Please, go on with that, I hadn't seen that problem. Indeed, I read Andrew answer to your question and I think it's a nice solution. yes... i'm always trying to kill flies with tanks... ;) Isn't that a little expensive on gas? maybe... but... what a beatiful explosion we can get... ;) seriously, attached's a new version of the patch... the patch use temp tablespaces for: - temp tables - temp files (generated by sorts and such) - indexes on temp tables the temp_tablespaces GUC still cannot be set from postgresql.conf, i will keep working on that but i have to understand the code... any comments on this patch? although it's not ready for apply it yet, i think Albert made a good work on it... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook -- Atentamente, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook Index: src/backend/commands/indexcmds.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/indexcmds.c,v retrieving revision 1.152 diff -c -r1.152 indexcmds.c *** src/backend/commands/indexcmds.c9 Jan 2007 02:14:11 - 1.152 --- src/backend/commands/indexcmds.c12 Jan 2007 05:08:35 - *** *** 209,215 } else { ! tablespaceId = GetDefaultTablespace(); /* note InvalidOid is OK in this case */ } --- 209,221 } else { ! /* !* if the target table is temporary then use a temp_tablespace !*/ ! if (!rel-rd_istemp) ! tablespaceId = GetDefaultTablespace(); ! else ! tablespaceId = GetTempTablespace(); /* note InvalidOid is OK in this case */ } Index: src/backend/commands/tablecmds.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v retrieving revision 1.210 diff -c -r1.210 tablecmds.c *** src/backend/commands/tablecmds.c5 Jan 2007 22:19:26 - 1.210 --- src/backend/commands/tablecmds.c12 Jan 2007 05:08:47 - *** *** 334,339 --- 334,343 errmsg(tablespace \%s\ does not exist, stmt-tablespacename))); } + else if (stmt-relation-istemp) + { + tablespaceId = GetTempTablespace(); + } else { tablespaceId = GetDefaultTablespace(); Index: src/backend/commands/tablespace.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablespace.c,v retrieving revision 1.40 diff -c -r1.40 tablespace.c *** src/backend/commands/tablespace.c 5 Jan 2007 22:19:26 - 1.40 --- src/backend/commands/tablespace.c 12 Jan 2007 05:08:49 - *** *** 65,73 #include utils/lsyscache.h ! /* GUC variable */ char *default_tablespace = NULL; static bool remove_tablespace_directories(Oid tablespaceoid, bool redo); static void set_short_version(const char *path); --- 65,76 #include utils/lsyscache.h ! /* GUC variables */ char *default_tablespace = NULL; + char *temp_tablespaces = NULL; + int next_temp_tablespace; + int num_temp_tablespaces; static bool remove_tablespace_directories(Oid tablespaceoid, bool redo); static void set_short_version(const char *path); *** *** 930,935 --- 933,1069 return result; } + /* + * Routines for handling the GUC variable 'temp_tablespaces'. + */ + + /* assign_hook: validate new temp_tablespaces, do extra actions as needed */ + const char * + assign_temp_tablespaces(const char *newval, bool doit, GucSource source) + { + char *rawname; + List *namelist; + ListCell *l; + + /* Need a modifiable copy of string */ + rawname = pstrdup(newval); + + /* Parse string into list of identifiers */ + if (!SplitIdentifierString(rawname, ',', namelist)) + { + /* syntax error in name list
Re: [pgsql-patches] [PATCHES] Tablespace for temporary objects and sort files
On 1/13/07, Albert Cervera Areny [EMAIL PROTECTED] wrote: It was already possible to set the guc on postgresql.conf when I posted the patch... ok... fixed... the problem was that this code only let num_temp_tablespaces be greater than zero when we are in an interactive command (eg. a SET command) but setting the guc from postgresql.conf at startup time is not interactive so num_temp_tablespaces is zero and when i try to get the first temp tablespace to use (MyProcPid % num_temp_tablespaces) causes a floatin exception (division by zero). + if (source = PGC_S_INTERACTIVE IsTransactionState()) + { + /* +* Verify that all the names are valid tablspace names +* We do not check for USAGE rights should we? +*/ + foreach(l, namelist) + { + char *curname = (char *) lfirst(l); + + if (get_tablespace_oid(curname) == InvalidOid) + ereport((source == PGC_S_TEST) ? NOTICE : ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg(tablespace \%s\ does not exist, curname))); + + num_temp_tablespaces++; + } + } new patch added, with that piece of code refactored to let num_temp_tablespaces get a value greater than zero always that the guc is setted, i also add some docs. the patch passes all 104 regression tests and all my tests as well... i think the patch is ready to be applied to HEAD, any committer want to review it? -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook Index: doc/src/sgml/config.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.101 diff -c -B -b -r1.101 config.sgml *** doc/src/sgml/config.sgml9 Jan 2007 22:16:46 - 1.101 --- doc/src/sgml/config.sgml15 Jan 2007 04:02:13 - *** *** 3398,3403 --- 3398,3432 /listitem /varlistentry + varlistentry id=guc-temp-tablespaces xreflabel=temp_tablespaces + termvarnametemp_tablespaces/varname (typestring/type)/term + indexterm +primaryvarnametemp_tablespaces/ configuration parameter/primary + /indexterm + indextermprimarytablespace/secondarytemp// + listitem +para + This variable specifies tablespaces in which to create temp + objects (temp tables and indexes on temp tables) when a + commandCREATE/ command does not explicitly specify a tablespace + and temp files when necessary (eg. for sorting operations). +/para + +para + The value is either a list of names of tablespaces, or an empty + string to specify using the default tablespace of the current database. + If the value does not match the name of any existing tablespace, + productnamePostgreSQL/ will automatically use the default + tablespace of the current database. +/para + +para + For more information on tablespaces, + see xref linkend=manage-ag-tablespaces. +/para + /listitem + /varlistentry + varlistentry id=guc-check-function-bodies xreflabel=check_function_bodies termvarnamecheck_function_bodies/varname (typeboolean/type)/term indexterm Index: src/backend/commands/indexcmds.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/indexcmds.c,v retrieving revision 1.152 diff -c -B -b -r1.152 indexcmds.c *** src/backend/commands/indexcmds.c9 Jan 2007 02:14:11 - 1.152 --- src/backend/commands/indexcmds.c15 Jan 2007 04:02:17 - *** *** 209,215 --- 209,221 } else { + /* +* if the target table is temporary then use a temp_tablespace +*/ + if (!rel-rd_istemp) tablespaceId = GetDefaultTablespace(); + else + tablespaceId = GetTempTablespace(); /* note InvalidOid is OK in this case */ } Index: src/backend/commands/tablecmds.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v retrieving revision 1.210 diff -c -B -b -r1.210 tablecmds.c *** src/backend/commands/tablecmds.c5 Jan 2007 22:19:26 - 1.210 --- src/backend/commands/tablecmds.c15 Jan 2007 04:02:28 - *** *** 334,339 --- 334,343 errmsg(tablespace \%s\ does not exist, stmt
Re: [PATCHES] Re: [HACKERS] [COMMITTERS] pgsql: Add GUC temp_tablespaces to provide a default location for
On 4/2/07, Bruce Momjian [EMAIL PROTECTED] wrote: This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold mmm... sorry, i have been busy... how many time we have? i can send something for friday... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] [WIP] GUC for temp_tablespaces
On 3/17/07, Tom Lane [EMAIL PROTECTED] wrote: Jaime Casanova [EMAIL PROTECTED] writes: On 3/5/07, Tom Lane [EMAIL PROTECTED] wrote: In the second place, it's a serious violation of what little modularity and layering we have for fd.c to be calling into commands/tablespace.c. This is not merely cosmetic but has real consequences: one being that it's now unsafe to call OpenTemporaryFile outside a transaction. ok, you are right... what do you suggest? maybe move the GetTempTablespace function to somewhere in src/backend/utils? You missed the point entirely. Relocating the code to some other file wouldn't change the objection: the problem is that fd.c mustn't invoke any transactional facilities such as catalog lookups. It's too low level for that. You could perhaps do it the other way around: some transactional code (eg the assign hook for a GUC variable) tells fd.c to save some private state controlling future temp file creations. ok. i have done that. I know this is not the time i told you but i was busy at job. i haven't did anything about RemovePgTempFiles() yet, because i want to know of the posibility of getting this on 8.3 -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook Index: doc/src/sgml/config.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.122 diff -c -r1.122 config.sgml *** doc/src/sgml/config.sgml20 Apr 2007 02:37:37 - 1.122 --- doc/src/sgml/config.sgml3 May 2007 06:12:31 - *** *** 3479,3484 --- 3479,3513 /listitem /varlistentry + varlistentry id=guc-temp-tablespaces xreflabel=temp_tablespaces + termvarnametemp_tablespaces/varname (typestring/type)/term + indexterm +primaryvarnametemp_tablespaces/ configuration parameter/primary + /indexterm + indextermprimarytablespace/secondarytemp// + listitem +para + This variable specifies tablespaces in which to create temp + objects (temp tables and indexes on temp tables) when a + commandCREATE/ command does not explicitly specify a tablespace + and temp files when necessary (eg. for sorting operations). +/para + +para + The value is either a list of names of tablespaces, or an empty + string to specify using the default tablespace of the current database. + If the value does not match the name of any existing tablespace, + productnamePostgreSQL/ will automatically use the default + tablespace of the current database. +/para + +para + For more information on tablespaces, + see xref linkend=manage-ag-tablespaces. +/para + /listitem + /varlistentry + varlistentry id=guc-check-function-bodies xreflabel=check_function_bodies termvarnamecheck_function_bodies/varname (typeboolean/type)/term indexterm Index: src/backend/commands/indexcmds.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/indexcmds.c,v retrieving revision 1.158 diff -c -r1.158 indexcmds.c *** src/backend/commands/indexcmds.c2 May 2007 21:08:45 - 1.158 --- src/backend/commands/indexcmds.c3 May 2007 06:12:34 - *** *** 208,214 } else { ! tablespaceId = GetDefaultTablespace(); /* note InvalidOid is OK in this case */ } --- 208,220 } else { ! /* !* if the target table is temporary then use a temp_tablespace !*/ ! if (!rel-rd_istemp) ! tablespaceId = GetDefaultTablespace(); ! else ! tablespaceId = GetTempTablespace(); /* note InvalidOid is OK in this case */ } Index: src/backend/commands/tablecmds.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v retrieving revision 1.219 diff -c -r1.219 tablecmds.c *** src/backend/commands/tablecmds.c8 Apr 2007 01:26:32 - 1.219 --- src/backend/commands/tablecmds.c3 May 2007 06:12:45 - *** *** 333,338 --- 333,342 errmsg(tablespace \%s\ does not exist, stmt-tablespacename))); } + else if (stmt-relation-istemp) + { + tablespaceId = GetTempTablespace(); + } else
Re: [PATCHES] [WIP] GUC for temp_tablespaces
On 5/3/07, Bruce Momjian [EMAIL PROTECTED] wrote: Your patch has been added to the PostgreSQL unapplied patches list at: This is an updated version of the patch. Tom objections: - fd.c is too low level for calling code from commands/tablespace.c. This was fixed adding a second parameter to BufFileCreateTemp() to send the tblspcOid (this function is called from executor/nodeHashJoin.c, utils/sort/logtape.c and utils/sort/tuplestore.c). Are these places ok? - RemovePgTempFilesInDir() has no support for removing temp files from strange locations. Per Tom suggestion temp files are now created in: base/pgsql_tmp and pg_tblspc/$oid_tblspc/pgsql_tmp. So i just refactor RemovePgTempFiles() to call RemovePgTempFilesInDir() with base and pg_tblspc/$oid_tblspc's pgsql_tmp Other changes in code: fd.c: functions make_database_relative() and FileNameOpenFile() were marked as NOT_USED. objections to simply delete them? also added OpenTempFileInTblspc() to create the tempfilepath and call to PathNameOpenFile() buffile.c: also added a new tblspcOid field to BufFile struct to use it in extendBufFile() Problems: While the patch passes all the regression tests i still have a problem when doin this: sgerp=# set temp_tablespaces = ''; ERROR: tablespace does not exist note that setting temp_tablespaces = '' from postgresql.conf works well. maybe this is silly but it's too late for me... i will keep trying tomorrow unless someone else has fixed it. comments? -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ? postgresql-8.3devel Index: doc/src/sgml/config.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.122 diff -c -r1.122 config.sgml *** doc/src/sgml/config.sgml20 Apr 2007 02:37:37 - 1.122 --- doc/src/sgml/config.sgml5 May 2007 05:27:03 - *** *** 3479,3484 --- 3479,3513 /listitem /varlistentry + varlistentry id=guc-temp-tablespaces xreflabel=temp_tablespaces + termvarnametemp_tablespaces/varname (typestring/type)/term + indexterm +primaryvarnametemp_tablespaces/ configuration parameter/primary + /indexterm + indextermprimarytablespace/secondarytemp// + listitem +para + This variable specifies tablespaces in which to create temp + objects (temp tables and indexes on temp tables) when a + commandCREATE/ command does not explicitly specify a tablespace + and temp files when necessary (eg. for sorting operations). +/para + +para + The value is either a list of names of tablespaces, or an empty + string to specify using the default tablespace of the current database. + If the value does not match the name of any existing tablespace, + productnamePostgreSQL/ will automatically use the default + tablespace of the current database. +/para + +para + For more information on tablespaces, + see xref linkend=manage-ag-tablespaces. +/para + /listitem + /varlistentry + varlistentry id=guc-check-function-bodies xreflabel=check_function_bodies termvarnamecheck_function_bodies/varname (typeboolean/type)/term indexterm Index: src/backend/commands/indexcmds.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/indexcmds.c,v retrieving revision 1.158 diff -c -r1.158 indexcmds.c *** src/backend/commands/indexcmds.c2 May 2007 21:08:45 - 1.158 --- src/backend/commands/indexcmds.c5 May 2007 05:27:06 - *** *** 208,214 } else { ! tablespaceId = GetDefaultTablespace(); /* note InvalidOid is OK in this case */ } --- 208,220 } else { ! /* !* if the target table is temporary then use a temp_tablespace !*/ ! if (!rel-rd_istemp) ! tablespaceId = GetDefaultTablespace(); ! else ! tablespaceId = GetTempTablespace(); /* note InvalidOid is OK in this case */ } Index: src/backend/commands/tablecmds.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v retrieving revision 1.219 diff -c -r1.219 tablecmds.c *** src/backend/commands/tablecmds.c8 Apr 2007 01:26:32 - 1.219 --- src/backend/commands/tablecmds.c5 May 2007 05:27:17 - *** *** 333,338
Re: [PATCHES] [WIP] GUC for temp_tablespaces
On 5/5/07, Bruce Momjian [EMAIL PROTECTED] wrote: Your patch has been added to the PostgreSQL unapplied patches list at: This is final version of the patch (i hope), at least it fixes the problem i had yesterday. -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ? postgresql-8.3devel Index: doc/src/sgml/config.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.122 diff -c -r1.122 config.sgml *** doc/src/sgml/config.sgml20 Apr 2007 02:37:37 - 1.122 --- doc/src/sgml/config.sgml5 May 2007 14:29:36 - *** *** 3479,3484 --- 3479,3513 /listitem /varlistentry + varlistentry id=guc-temp-tablespaces xreflabel=temp_tablespaces + termvarnametemp_tablespaces/varname (typestring/type)/term + indexterm +primaryvarnametemp_tablespaces/ configuration parameter/primary + /indexterm + indextermprimarytablespace/secondarytemp// + listitem +para + This variable specifies tablespaces in which to create temp + objects (temp tables and indexes on temp tables) when a + commandCREATE/ command does not explicitly specify a tablespace + and temp files when necessary (eg. for sorting operations). +/para + +para + The value is either a list of names of tablespaces, or an empty + string to specify using the default tablespace of the current database. + If the value does not match the name of any existing tablespace, + productnamePostgreSQL/ will automatically use the default + tablespace of the current database. +/para + +para + For more information on tablespaces, + see xref linkend=manage-ag-tablespaces. +/para + /listitem + /varlistentry + varlistentry id=guc-check-function-bodies xreflabel=check_function_bodies termvarnamecheck_function_bodies/varname (typeboolean/type)/term indexterm Index: src/backend/commands/indexcmds.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/indexcmds.c,v retrieving revision 1.158 diff -c -r1.158 indexcmds.c *** src/backend/commands/indexcmds.c2 May 2007 21:08:45 - 1.158 --- src/backend/commands/indexcmds.c5 May 2007 14:29:40 - *** *** 208,214 } else { ! tablespaceId = GetDefaultTablespace(); /* note InvalidOid is OK in this case */ } --- 208,220 } else { ! /* !* if the target table is temporary then use a temp_tablespace !*/ ! if (!rel-rd_istemp) ! tablespaceId = GetDefaultTablespace(); ! else ! tablespaceId = GetTempTablespace(); /* note InvalidOid is OK in this case */ } Index: src/backend/commands/tablecmds.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v retrieving revision 1.219 diff -c -r1.219 tablecmds.c *** src/backend/commands/tablecmds.c8 Apr 2007 01:26:32 - 1.219 --- src/backend/commands/tablecmds.c5 May 2007 14:29:51 - *** *** 333,338 --- 333,342 errmsg(tablespace \%s\ does not exist, stmt-tablespacename))); } + else if (stmt-relation-istemp) + { + tablespaceId = GetTempTablespace(); + } else { tablespaceId = GetDefaultTablespace(); Index: src/backend/commands/tablespace.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablespace.c,v retrieving revision 1.45 diff -c -r1.45 tablespace.c *** src/backend/commands/tablespace.c 22 Mar 2007 19:51:44 - 1.45 --- src/backend/commands/tablespace.c 5 May 2007 14:29:53 - *** *** 65,73 #include utils/lsyscache.h ! /* GUC variable */ char *default_tablespace = NULL; static bool remove_tablespace_directories(Oid tablespaceoid, bool redo); static void set_short_version(const char *path); --- 65,76 #include utils/lsyscache.h ! /* GUC variables */ char *default_tablespace = NULL; + char *temp_tablespaces = NULL; + int next_temp_tablespace; + int num_temp_tablespaces; static bool
Re: [PATCHES] [WIP] GUC for temp_tablespaces
On 5/8/07, Peter Eisentraut [EMAIL PROTECTED] wrote: Am Samstag, 5. Mai 2007 16:40 schrieb Jaime Casanova: On 5/5/07, Bruce Momjian [EMAIL PROTECTED] wrote: Your patch has been added to the PostgreSQL unapplied patches list at: This is final version of the patch (i hope), at least it fixes the problem i had yesterday. What I have been missing all along in these patches is an explanation for what it means to list multiple temporary tablespaces. Are they used in order, or the first one that exists, or what? http://archives.postgresql.org/pgsql-hackers/2007-01/msg00531.php http://archives.postgresql.org/pgsql-patches/2007-01/msg00282.php in src/backend/commands/tablespace.c:assign_temp_tablespaces(): /* * Select the first tablespace to use */ Assert(num_temp_tablespaces = 0); if (num_temp_tablespaces != 0) next_temp_tablespace = MyProcPid % num_temp_tablespaces; -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [WIP] GUC for temp_tablespaces
On 5/9/07, Peter Eisentraut [EMAIL PROTECTED] wrote: Am Mittwoch, 9. Mai 2007 02:21 schrieb Jaime Casanova: What I have been missing all along in these patches is an explanation for what it means to list multiple temporary tablespaces. Are they used in order, or the first one that exists, or what? http://archives.postgresql.org/pgsql-hackers/2007-01/msg00531.php http://archives.postgresql.org/pgsql-patches/2007-01/msg00282.php Those are discussions of possible ideas, not an acceptable documentation of this feature. ahh... ok, obviously a misunderstood you... what you were asking for is user visible documentation, isn't it? what the patch does is to select the first tablespace from the list pseudo-randomicaly (MyProcPid % num_temp_tablespaces) and then cycle in order through the list every time we call GetTempTablespace(). Every backend will start (hopefully) in a different tablespace and will keep its own iterator for the list. A BufFile will use the same tablespace for every file it has. If we can't create the file in the selected tablespace we fall into $PGDATA/base/pgsql_tmp, now that i think on it we should be sending a warning that the file couldn't be created. About the docs, what about something along the lines, in config.sgml: The first tablespace that will be used is choosen randomly from the list, starting from that with cycle through the list in order. -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook Index: doc/src/sgml/config.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.122 diff -c -r1.122 config.sgml *** doc/src/sgml/config.sgml20 Apr 2007 02:37:37 - 1.122 --- doc/src/sgml/config.sgml10 May 2007 05:12:33 - *** *** 3479,3484 --- 3479,3518 /listitem /varlistentry + varlistentry id=guc-temp-tablespaces xreflabel=temp_tablespaces + termvarnametemp_tablespaces/varname (typestring/type)/term + indexterm +primaryvarnametemp_tablespaces/ configuration parameter/primary + /indexterm + indextermprimarytablespace/secondarytemp// + listitem +para + This variable specifies tablespaces in which to create temp + objects (temp tables and indexes on temp tables) when a + commandCREATE/ command does not explicitly specify a tablespace + and temp files when necessary (eg. for sorting operations). +/para + +para + The value is either a list of names of tablespaces, or an empty + string to specify using the default tablespace of the current database. + If the value does not match the name of any existing tablespace, + productnamePostgreSQL/ will automatically use the default + tablespace of the current database. +/para + + para + The first tablespace that will be used is choosen randomly from the + list, starting from that we cycle through the list in order. + /para + +para + For more information on tablespaces, + see xref linkend=manage-ag-tablespaces. +/para + /listitem + /varlistentry + varlistentry id=guc-check-function-bodies xreflabel=check_function_bodies termvarnamecheck_function_bodies/varname (typeboolean/type)/term indexterm Index: src/backend/commands/indexcmds.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/indexcmds.c,v retrieving revision 1.158 diff -c -r1.158 indexcmds.c *** src/backend/commands/indexcmds.c2 May 2007 21:08:45 - 1.158 --- src/backend/commands/indexcmds.c10 May 2007 05:12:37 - *** *** 208,214 } else { ! tablespaceId = GetDefaultTablespace(); /* note InvalidOid is OK in this case */ } --- 208,220 } else { ! /* !* if the target table is temporary then use a temp_tablespace !*/ ! if (!rel-rd_istemp) ! tablespaceId = GetDefaultTablespace(); ! else ! tablespaceId = GetTempTablespace(); /* note InvalidOid is OK in this case */ } Index: src/backend/commands/tablecmds.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v retrieving revision 1.219 diff -c -r1.219 tablecmds.c *** src/backend/commands/tablecmds.c8 Apr 2007 01:26:32 - 1.219
Re: [PATCHES] Updateable cursors patch
On 4/4/07, FAST PostgreSQL [EMAIL PROTECTED] wrote: Attached is a working updateable cursors patch. The core functionality has been implemented and the patch also contains the regression tests and documentation. this one doesn't apply cleanly to HEAD because of the changes in http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Updateable cursors patch
On 5/17/07, FAST PostgreSQL [EMAIL PROTECTED] wrote: No. It works with scrollable cursors. It will work for cursors/selects which does not put the results in some store, such as WITH hold/group by/order by etc But most of these restrictions apply for normal 'Select for update' anyway. (With the order by clause, the implementation is as per the sql standards.) your patch doesn't work with updatable views because they don't have ctid columns ERROR: column ctid does not exist STATEMENT: update vfoo set des_cta = des_cta || ' - prueba' where current of foo; ERROR: current transaction is aborted, commands ignored until end of transaction block is this sane behavior? to accept create cursors for update on views and then failing to update where current of and rollback the entire transaction? comments? -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook tests.sql Description: Binary data ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Updateable cursors patch
On 5/17/07, Jaime Casanova [EMAIL PROTECTED] wrote: On 5/17/07, FAST PostgreSQL [EMAIL PROTECTED] wrote: No. It works with scrollable cursors. It will work for cursors/selects which does not put the results in some store, such as WITH hold/group by/order by etc But most of these restrictions apply for normal 'Select for update' anyway. (With the order by clause, the implementation is as per the sql standards.) your patch doesn't work with updatable views because they don't have ctid columns ERROR: column ctid does not exist STATEMENT: update vfoo set des_cta = des_cta || ' - prueba' where current of foo; ERROR: current transaction is aborted, commands ignored until end of transaction block is this sane behavior? to accept create cursors for update on views and then failing to update where current of and rollback the entire transaction? comments? sorry, reattaching the test script -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook tests.sql Description: Binary data ---(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] Maintaining cluster order on insert
On 5/16/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: Jim C. Nasby wrote: What about adding the ability to ask the FSM for a page that's near a given page? That way if you did have to go to the FSM you could at least try and insert close to the page you originally wanted. Yeah, there's always room for improvement. I made the patch when I was working on clustered indexes, and was mostly concerned about getting inserts to the same page as other tuples with similar values so that the clustered index stays clustered. the patch doesn't apply in cvs... you'll need to update it... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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] Maintaining cluster order on insert
On 5/17/07, Jaime Casanova [EMAIL PROTECTED] wrote: On 5/16/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: Jim C. Nasby wrote: What about adding the ability to ask the FSM for a page that's near a given page? That way if you did have to go to the FSM you could at least try and insert close to the page you originally wanted. Yeah, there's always room for improvement. I made the patch when I was working on clustered indexes, and was mostly concerned about getting inserts to the same page as other tuples with similar values so that the clustered index stays clustered. the patch doesn't apply in cvs... you'll need to update it... cvs i wrote? head i was meaning... sorry, too late for me =) -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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] Updateable cursors patch
On 5/17/07, Jaime Casanova [EMAIL PROTECTED] wrote: On 5/17/07, FAST PostgreSQL [EMAIL PROTECTED] wrote: No. It works with scrollable cursors. It will work for cursors/selects which does not put the results in some store, such as WITH hold/group by/order by etc But most of these restrictions apply for normal 'Select for update' anyway. (With the order by clause, the implementation is as per the sql standards.) your patch doesn't work with updatable views because they don't have ctid columns ERROR: column ctid does not exist STATEMENT: update vfoo set des_cta = des_cta || ' - prueba' where current of foo; ERROR: current transaction is aborted, commands ignored until end of transaction block is this sane behavior? to accept create cursors for update on views and then failing to update where current of and rollback the entire transaction? comments? maybe just send a better error message -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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] GUC temp_tablespaces (updated)
Hi, This is an updated patch based on conversation on -hackers, basically it adds a hint to the error thrown by DROP TABLESPACE when it's not empty and there are temp files older than postmaster startup. i had to remove PgRemoveTempFiles() to test the patch (what i dificult task is to crash the server ;) btw, I tried to send it to the same conversation but it never arrives so i'm sending it to -patches. -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook Index: doc/src/sgml/config.sgml === RCS file: /home/postgres/PG_RELEASES/pg_repository/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.124 diff -c -r1.124 config.sgml *** doc/src/sgml/config.sgml17 May 2007 23:36:04 - 1.124 --- doc/src/sgml/config.sgml29 May 2007 01:15:44 - *** *** 3485,3490 --- 3485,3524 /listitem /varlistentry + varlistentry id=guc-temp-tablespaces xreflabel=temp_tablespaces + termvarnametemp_tablespaces/varname (typestring/type)/term + indexterm +primaryvarnametemp_tablespaces/ configuration parameter/primary + /indexterm + indextermprimarytablespace/secondarytemp// + listitem +para + This variable specifies tablespaces in which to create temp + objects (temp tables and indexes on temp tables) when a + commandCREATE/ command does not explicitly specify a tablespace + and temp files when necessary (eg. for sorting operations). +/para + +para + The value is either a list of names of tablespaces, or an empty + string to specify using the default tablespace of the current database. + If the value does not match the name of any existing tablespace, + productnamePostgreSQL/ will automatically use the default + tablespace of the current database. +/para + + para + The first tablespace that will be used is choosen randomly from the + list, starting from that we cycle through the list in order. + /para + +para + For more information on tablespaces, + see xref linkend=manage-ag-tablespaces. +/para + /listitem + /varlistentry + varlistentry id=guc-check-function-bodies xreflabel=check_function_bodies termvarnamecheck_function_bodies/varname (typeboolean/type)/term indexterm Index: src/backend/commands/indexcmds.c === RCS file: /home/postgres/PG_RELEASES/pg_repository/pgsql/src/backend/commands/indexcmds.c,v retrieving revision 1.158 diff -c -r1.158 indexcmds.c *** src/backend/commands/indexcmds.c2 May 2007 21:08:45 - 1.158 --- src/backend/commands/indexcmds.c29 May 2007 01:15:44 - *** *** 208,214 } else { ! tablespaceId = GetDefaultTablespace(); /* note InvalidOid is OK in this case */ } --- 208,220 } else { ! /* !* if the target table is temporary then use a temp_tablespace !*/ ! if (!rel-rd_istemp) ! tablespaceId = GetDefaultTablespace(); ! else ! tablespaceId = GetTempTablespace(); /* note InvalidOid is OK in this case */ } Index: src/backend/commands/tablecmds.c === RCS file: /home/postgres/PG_RELEASES/pg_repository/pgsql/src/backend/commands/tablecmds.c,v retrieving revision 1.225 diff -c -r1.225 tablecmds.c *** src/backend/commands/tablecmds.c18 May 2007 23:19:41 - 1.225 --- src/backend/commands/tablecmds.c29 May 2007 01:15:44 - *** *** 331,336 --- 331,340 errmsg(tablespace \%s\ does not exist, stmt-tablespacename))); } + else if (stmt-relation-istemp) + { + tablespaceId = GetTempTablespace(); + } else { tablespaceId = GetDefaultTablespace(); Index: src/backend/commands/tablespace.c === RCS file: /home/postgres/PG_RELEASES/pg_repository/pgsql/src/backend/commands/tablespace.c,v retrieving revision 1.45 diff -c -r1.45 tablespace.c *** src/backend/commands/tablespace.c 22 Mar 2007 19:51:44 - 1.45 --- src/backend/commands/tablespace.c 30 May 2007 23:31:14 - *** *** 65,75 #include utils
Re: [PATCHES] Maintaining cluster order on insert
On 5/27/07, Jim C. Nasby [EMAIL PROTECTED] wrote: On Mon, May 21, 2007 at 10:48:59AM +0100, Heikki Linnakangas wrote: IOW it's working as designed. But maybe it's not the desired behavior. Should we have a special case and always respect the fillfactor when inserting to the last page of the heap? I think that would be following with least surprise. What's the status of this patch? are we waiting an update? AFAIU, it's not fair to say that the patch maintain cluster order... it just try to keep similar rows on the same page if possible (it's not the same thing)... if it can't then it simply insert at the last page as usual but we have wasted time in the try... so the real question is if there is any performance win on this... have you some numbers? another question: if the fillfactor is 100% then is a complete waste of time to look for a suggested block. maybe we could check for that? -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Maintaining cluster order on insert
On 6/16/07, Tom Lane [EMAIL PROTECTED] wrote: Jaime Casanova [EMAIL PROTECTED] writes: another question: if the fillfactor is 100% then is a complete waste of time to look for a suggested block. maybe we could check for that? No, it isn't, since the page might have been vacuumed since it was last filled up. regards, tom lane ahh... that vacuum thing!!! yeah!!! ;) -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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] remove unused caller arg from stringToQualifiedNameList
On 6/25/07, Alvaro Herrera [EMAIL PROTECTED] wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: This function seems to have an argument that is no longer used (probably leftover from when it used to report an error message?). Yeah, I recall having left the argument in place because it seemed like we might want it again someday. But that was for 7.4 which was awhile ago. There's probably not a strong argument to keep it, but on the other hand is there a strong argument to remove it? Other than removing cruft, nope, I don't see any ... In any case, this is an exported symbol so maybe it's not a good idea to mess with it. OTOH I checked PL/R and PL/php and neither uses it, so this may not be a problem at all. FWIW, we remove the second argument in textToQualifiedNameList() two years ago so i think this is just finishing what we already started, IMHO anyway http://archives.postgresql.org/pgsql-committers/2005-05/msg00318.php -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] script binaries renaming
On 7/7/07, Michael Glaesemann [EMAIL PROTECTED] wrote: 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. i used to use createlang before the pg_pltemplate exists (because i hate to have to create those pl handlers at hand). after that the only script that has a real use to me is initdb and pg_ctl what makes me wonder why doesn't exist pg_ctl init -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support
On 7/10/07, Bruce Momjian [EMAIL PROTECTED] wrote: Neil Conway wrote: Attached is a revised version of this patch. Sorry for taking so long to make progress on this: my new job been keeping my busy, and I've recently been ill. Illness only counts as an excuse if you _don't_ recover. If you recover, you weren't sick enough. ;-) LOL uh! that sounds like my boss talking! -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] HOT version 18
On 9/17/07, Tom Lane [EMAIL PROTECTED] wrote: I have completed a first review pass over all of the HOT patch, and am now reasonably satisfied with coding style, APIs, etc. There are still two or three areas that need discussion, which I'll start a separate thread for on -hackers. In the meantime, here's some code for people to test and poke at ... regards, tom lane this sql scripts make current cvs + patch to crash with this message in the logs: LOG: statement: update test set c2 = c2 -15 where current of q1; TRAP: FailedAssertion(!(tuple_tableoid == table_oid), File: execCurrent.c, Line: 128) LOG: server process (PID 27599) was terminated by signal 6: Aborted LOG: terminating any other active server processes -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook test-hot.sql Description: Binary data ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] HOT version 18
On 9/18/07, Zoltan Boszormenyi [EMAIL PROTECTED] wrote: Hi, Pavan Deolasee írta: On 9/18/07, *Jaime Casanova* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: this sql scripts make current cvs + patch to crash with this message in the logs: Can you please check if the attached patch fixes the issue for you ? It sets t_tableOid before returning a HOT tuple to the caller. Thanks, Pavan I can confirm that the script crashed HOT v18 and your patch fixes it. yeah! it works fine for me now... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] 'Waiting on lock'
On 6/19/07, Simon Riggs [EMAIL PROTECTED] wrote: related TODO items: - add a WAIT n clause in same SQL locations as NOWAIT - add a lock_wait_timeout (USERSET), default = 0 (unlimited waiting) to provide better control over lock waits. are these actual TODO items? i can't find them on the TODO list and i don't remember any discussion nor patch about this -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] 'Waiting on lock'
On 9/24/07, Simon Riggs [EMAIL PROTECTED] wrote: On Sat, 2007-09-22 at 23:49 -0500, Jaime Casanova wrote: On 6/19/07, Simon Riggs [EMAIL PROTECTED] wrote: related TODO items: - add a WAIT n clause in same SQL locations as NOWAIT - add a lock_wait_timeout (USERSET), default = 0 (unlimited waiting) to provide better control over lock waits. are these actual TODO items? i can't find them on the TODO list and i don't remember any discussion nor patch about this They are my proposals for TODO items to assist with application development. while i'm not at all comfortable with the idea of a GUC for this, the WAIT clause seems to be useful. just out of curiosity, why the NOWAIT patch wasn't do it that way in first place, i mean like a WAIT clause and when receiving NOWAIT transform it in WAIT 0? maybe dicussion? there's concensus in adding a WAIT clause? -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] 'Waiting on lock'
On 9/25/07, Simon Riggs [EMAIL PROTECTED] wrote: On Tue, 2007-09-25 at 09:16 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: SQLServer and DB2 have more need of this than PostgreSQL, but we do still need it. Why? What does it do that statement_timeout doesn't do better? If the execution time is negligible, then setting statement_timeout is the same thing as setting a lock timeout. If execution time is not negligible, then you may want to tell the difference between waiting for completion against waiting forever without doing anything useful at all. [...thinking on this a bit...] mmm... i think we can emulate WAIT number_of_seconds using the NOWAIT and a bit of logic... point for tom Plus, if applications are written using these concepts it is easier to port them to PostgreSQL. no words... point for simon... Not planning to work on this myself, but I think it is a valid TODO. i will make a try for 8.4 -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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] too many variants of relation_open
Hi, I can understand why we have relation_openrv and try_relation_open, but relation_open_nowait can be merged with relation_open. Or there is something i'm missing? attached is a patch that do the merge. -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook Index: contrib/dblink/dblink.c === RCS file: /home/postgres/cvsrepo/pgsql/contrib/dblink/dblink.c,v retrieving revision 1.65 diff -c -r1.65 dblink.c *** contrib/dblink/dblink.c 27 Aug 2007 01:24:50 - 1.65 --- contrib/dblink/dblink.c 28 Sep 2007 02:26:18 - *** *** 1690,1696 AclResult aclresult; /* open relation using relid, check permissions, get tupdesc */ ! rel = relation_open(relid, AccessShareLock); aclresult = pg_class_aclcheck(RelationGetRelid(rel), GetUserId(), ACL_SELECT); --- 1690,1696 AclResult aclresult; /* open relation using relid, check permissions, get tupdesc */ ! rel = relation_open(relid, AccessShareLock, true); aclresult = pg_class_aclcheck(RelationGetRelid(rel), GetUserId(), ACL_SELECT); *** *** 1819,1825 /* * Open relation using relid */ ! rel = relation_open(relid, AccessShareLock); tupdesc = rel-rd_att; natts = tupdesc-natts; --- 1819,1825 /* * Open relation using relid */ ! rel = relation_open(relid, AccessShareLock, true); tupdesc = rel-rd_att; natts = tupdesc-natts; *** *** 1902,1908 /* * Open relation using relid */ ! rel = relation_open(relid, AccessShareLock); tupdesc = rel-rd_att; natts = tupdesc-natts; --- 1902,1908 /* * Open relation using relid */ ! rel = relation_open(relid, AccessShareLock, true); tupdesc = rel-rd_att; natts = tupdesc-natts; *** *** 1954,1960 /* * Open relation using relid */ ! rel = relation_open(relid, AccessShareLock); tupdesc = rel-rd_att; natts = tupdesc-natts; --- 1954,1960 /* * Open relation using relid */ ! rel = relation_open(relid, AccessShareLock, true); tupdesc = rel-rd_att; natts = tupdesc-natts; *** *** 2098,2104 /* * Open relation using relid */ ! rel = relation_open(relid, AccessShareLock); tupdesc = CreateTupleDescCopy(rel-rd_att); relation_close(rel, AccessShareLock); --- 2098,2104 /* * Open relation using relid */ ! rel = relation_open(relid, AccessShareLock, true); tupdesc = CreateTupleDescCopy(rel-rd_att); relation_close(rel, AccessShareLock); Index: contrib/pgstattuple/pgstattuple.c === RCS file: /home/postgres/cvsrepo/pgsql/contrib/pgstattuple/pgstattuple.c,v retrieving revision 1.30 diff -c -r1.30 pgstattuple.c *** contrib/pgstattuple/pgstattuple.c 20 Sep 2007 17:56:30 - 1.30 --- contrib/pgstattuple/pgstattuple.c 28 Sep 2007 02:22:00 - *** *** 183,189 (errmsg(must be superuser to use pgstattuple functions; /* open relation */ ! rel = relation_open(relid, AccessShareLock); PG_RETURN_DATUM(pgstat_relation(rel, fcinfo)); } --- 183,189 (errmsg(must be superuser to use pgstattuple functions; /* open relation */ ! rel = relation_open(relid, AccessShareLock, true); PG_RETURN_DATUM(pgstat_relation(rel, fcinfo)); } Index: src/backend/access/heap/heapam.c === RCS file: /home/postgres/cvsrepo/pgsql/src/backend/access/heap/heapam.c,v retrieving revision 1.242 diff -c -r1.242 heapam.c *** src/backend/access/heap/heapam.c 21 Sep 2007 21:25:42 - 1.242 --- src/backend/access/heap/heapam.c 28 Sep 2007 02:51:24 - *** *** 819,824 --- 819,827 * obtained on the relation. (Generally, NoLock should only be * used if the caller knows it has some appropriate lock on the * relation already.) + * + * if wait is false open but don't wait for lock, instead throw an error + * when the requested lock is not immediately obtainable. * * An error is raised if the relation does not exist. * *** *** 827,833 * */ Relation ! relation_open(Oid relationId, LOCKMODE lockmode) { Relation r; --- 830,836 * */ Relation ! relation_open(Oid relationId, LOCKMODE lockmode, bool wait) { Relation r; *** *** 835,842 /* Get the lock before trying to open the relcache entry */ if (lockmode != NoLock) ! LockRelationOid(relationId, lockmode); ! /* The relcache does all the real work... */ r = RelationIdGetRelation
Re: [PATCHES] 2WRS [WIP]
On Feb 7, 2008 6:04 AM, Manolo _ [EMAIL PROTECTED] wrote: HI. I send you the diff of my code against the current CVS TIP. Please tell me if it's what you were asking for. not actually, because your patch removes an improvement that was included in 8.3... what you will have to do (if someone has a better solution feel free to comment on this) is to manually merge your 8.2's patch into the 8.3's source and then generate a diff another sugestion is to comment a little more your code. simply put a mark where you modify something is not a comment, specially if you can get that info from a simple cvs diff -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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] 2WRS [WIP]
On Thu, Feb 21, 2008 at 6:44 AM, [EMAIL PROTECTED] wrote: Hi. That's the last release and refers to 8.3.0 and not to 8.2.5 as before. Hope you can tell me if I created it correctly please. no, it doesn't... ! /* GUC variables */ #ifdef TRACE_SORT booltrace_sort = false; #endif - #ifdef DEBUG_BOUNDED_SORT - booloptimize_bounded_sort = true; - #endif it's seems you're removing something added in 8.3 -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] plpgsql CASE statement - last version
On Thu, May 1, 2008 at 7:59 AM, Pavel Stehule [EMAIL PROTECTED] wrote: Hello 2008/5/1 Jaime Casanova [EMAIL PROTECTED]: On Sat, Apr 5, 2008 at 6:57 AM, Pavel Stehule [EMAIL PROTECTED] wrote: Hello I found some bugs when I used base_lexer, so I returned back own lexer. It's only little bit longer, but simpler. you really compile this one? i get a complain because read_sql_construct is called with 8 arguments and it should have only 7.. yes, I did it. 8 arguments are from EXECUTE USING patch http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/gram.y.diff?r1=1.108;r2=1.109;f=h so, i need to see that patch first? patch that doesn't apply because of changes in files (specially definitions moved to other files, but i haven't checked all the .rej yet) -- regards, Jaime Casanova Soporte de PostgreSQL Guayaquil - Ecuador Cel. (593) 087171157 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] plpgsql CASE statement - last version
On Thu, May 1, 2008 at 8:11 AM, Jaime Casanova [EMAIL PROTECTED] wrote: On Thu, May 1, 2008 at 7:59 AM, Pavel Stehule [EMAIL PROTECTED] wrote: Hello 2008/5/1 Jaime Casanova [EMAIL PROTECTED]: On Sat, Apr 5, 2008 at 6:57 AM, Pavel Stehule [EMAIL PROTECTED] wrote: Hello I found some bugs when I used base_lexer, so I returned back own lexer. It's only little bit longer, but simpler. you really compile this one? i get a complain because read_sql_construct is called with 8 arguments and it should have only 7.. yes, I did it. 8 arguments are from EXECUTE USING patch http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/gram.y.diff?r1=1.108;r2=1.109;f=h so, i need to see that patch first? patch that doesn't apply because of changes in files (specially definitions moved to other files, but i haven't checked all the .rej yet) sorry, you mean already applied RETURN QUERY, right? i was thinking in pending patch RETURN QUERY EXECUTE... i will check again my files but i'm sure i have updated tu CVS TIP before try your patch -- Atentamente, Jaime Casanova Soporte de PostgreSQL Guayaquil - Ecuador Cel. (593) 087171157 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] plpgsql CASE statement - last version
On Thu, May 1, 2008 at 8:14 AM, Jaime Casanova [EMAIL PROTECTED] wrote: On Thu, May 1, 2008 at 8:11 AM, Jaime Casanova [EMAIL PROTECTED] wrote: On Thu, May 1, 2008 at 7:59 AM, Pavel Stehule [EMAIL PROTECTED] wrote: Hello 2008/5/1 Jaime Casanova [EMAIL PROTECTED]: On Sat, Apr 5, 2008 at 6:57 AM, Pavel Stehule [EMAIL PROTECTED] wrote: Hello I found some bugs when I used base_lexer, so I returned back own lexer. It's only little bit longer, but simpler. you really compile this one? i get a complain because read_sql_construct is called with 8 arguments and it should have only 7.. yes, I did it. 8 arguments are from EXECUTE USING patch http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/gram.y.diff?r1=1.108;r2=1.109;f=h so, i need to see that patch first? patch that doesn't apply because of changes in files (specially definitions moved to other files, but i haven't checked all the .rej yet) sorry, you mean already applied RETURN QUERY, right? i was thinking in pending patch RETURN QUERY EXECUTE... i will check again my files but i'm sure i have updated tu CVS TIP before try your patch ok, you're right... sorry for the noise... i will try it again -- regards, Jaime Casanova Soporte de PostgreSQL Guayaquil - Ecuador Cel. (593) 087171157 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] plpgsql CASE statement - last version
On Sat, Apr 5, 2008 at 6:57 AM, Pavel Stehule [EMAIL PROTECTED] wrote: Hello I found some bugs when I used base_lexer, so I returned back own lexer. It's only little bit longer, but simpler. you really compile this one? i get a complain because read_sql_construct is called with 8 arguments and it should have only 7... + expr = read_sql_construct(',', K_THEN, 0, THEN, + SELECT , true, true, tok); gram.y: In function 'plpgsql_yyparse': gram.y:1697: warning: passing argument 5 of 'read_sql_construct' makes integer from pointer without a cast gram.y:1697: warning: passing argument 7 of 'read_sql_construct' makes pointer from integer without a cast gram.y:1697: error: too many arguments to function 'read_sql_construct' -- regards, Jaime Casanova Soporte de PostgreSQL Guayaquil - Ecuador Cel. (593) 087171157 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Extending grant insert on tables to sequences
On Thu, May 22, 2008 at 1:18 PM, Jaime Casanova [EMAIL PROTECTED] wrote: Hi, The idea of this patch is to avoid the need to make explicit grants on sequences owned by tables. I've noted that the patch i attached is an older version that doesn't compile because of a typo... Re-attaching right patch and fix documentation to indicate the new behaviour... we need an user visible message to indicate this implicit grant on the sequences? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 087171157 Index: doc/src/sgml/ref/grant.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v retrieving revision 1.68 diff -c -r1.68 grant.sgml *** doc/src/sgml/ref/grant.sgml 5 May 2008 01:21:03 - 1.68 --- doc/src/sgml/ref/grant.sgml 24 May 2008 04:46:36 - *** *** 387,396 /para para ! Granting permission on a table does not automatically extend ! permissions to any sequences used by the table, including ! sequences tied to typeSERIAL/ columns. Permissions on ! sequence must be set separately. /para para --- 387,395 /para para ! Granting permission on a table automatically extend ! permissions to any sequences owned by the table, including ! sequences tied to typeSERIAL/ columns. /para para Index: src/backend/catalog/aclchk.c === RCS file: /projects/cvsroot/pgsql/src/backend/catalog/aclchk.c,v retrieving revision 1.146 diff -c -r1.146 aclchk.c *** src/backend/catalog/aclchk.c12 May 2008 00:00:46 - 1.146 --- src/backend/catalog/aclchk.c24 May 2008 04:46:45 - *** *** 360,365 --- 360,402 } ExecGrantStmt_oids(istmt); + + /* +* If the objtype is a relation and the privileges includes INSERT, UPDATE +* or SELECT then extends the GRANT/REVOKE to the sequences owned by the +* relation +*/ + if ((istmt.objtype == ACL_OBJECT_RELATION) + (istmt.privileges (ACL_INSERT | ACL_UPDATE | ACL_SELECT))) + { + AclMode priv; + foreach(cell, istmt.objects) + { + InternalGrant istmt_seq; + + istmt_seq.is_grant = istmt.is_grant; + istmt_seq.objtype = ACL_OBJECT_SEQUENCE; + istmt_seq.grantees = istmt.grantees; + istmt_seq.grant_option = istmt.grant_option; + istmt_seq.behavior = istmt.behavior; + + istmt_seq.all_privs = false; + istmt_seq.privileges = ACL_NO_RIGHTS; + + istmt_seq.objects = getOwnedSequences(lfirst_oid(cell)); + if (istmt_seq.objects != NIL) + { + if (istmt.privileges (ACL_INSERT)) + istmt_seq.privileges |= ACL_USAGE; + else if (istmt.privileges (ACL_UPDATE)) + istmt_seq.privileges |= ACL_UPDATE; + else if (istmt.privileges (ACL_SELECT)) + istmt_seq.privileges |= ACL_SELECT; + + ExecGrantStmt_oids(istmt_seq); + } + } + } } /* Index: src/test/regress/expected/dependency.out === RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/dependency.out,v retrieving revision 1.6 diff -c -r1.6 dependency.out *** src/test/regress/expected/dependency.out5 May 2008 01:21:03 - 1.6 --- src/test/regress/expected/dependency.out24 May 2008 04:46:59 - *** *** 16,22 DETAIL: access to table deptest DROP GROUP regression_group; ERROR: role regression_group cannot be dropped because some objects depend on it ! DETAIL: access to table deptest -- if we revoke the privileges we can drop the group REVOKE SELECT ON deptest FROM GROUP regression_group; DROP GROUP regression_group; --- 16,23 DETAIL: access to table deptest DROP GROUP regression_group; ERROR: role regression_group cannot be dropped because some objects depend on it ! DETAIL: access to sequence deptest_f1_seq ! access to table deptest -- if we revoke the privileges we can drop the group REVOKE SELECT ON deptest FROM GROUP regression_group; DROP GROUP regression_group; -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Extending grant insert on tables to sequences
On Sat, May 24, 2008 at 12:09 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Please add the patch to the commitfest page, Ah! I forgot we have a new process now... patch added to the commitfest page... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 087171157 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Extending grant insert on tables to sequences
On 5/29/08, Robert Treat [EMAIL PROTECTED] wrote: On Saturday 24 May 2008 01:19:05 Jaime Casanova wrote: On Sat, May 24, 2008 at 12:09 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Please add the patch to the commitfest page, Ah! I forgot we have a new process now... patch added to the commitfest page... What's the use case for extending SELECT on table to SELECT on sequence ? Just to be consistent -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 087171157 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
[PATCHES] make some internal SRF functions use output parameters
Hi, attached the patch i offer to make some internal SRF functions use output parameters. -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. (593) 87171157 Index: src/backend/catalog/system_views.sql === RCS file: /var/lib/postgresql/CVSREPO/pgsql/src/backend/catalog/system_views.sql,v retrieving revision 1.53 diff -c -r1.53 system_views.sql *** src/backend/catalog/system_views.sql 14 Jul 2008 00:51:45 - 1.53 --- src/backend/catalog/system_views.sql 25 Aug 2008 05:28:14 - *** *** 143,179 CREATE VIEW pg_locks AS SELECT * ! FROM pg_lock_status() AS L ! (locktype text, database oid, relation oid, page int4, tuple int2, ! virtualxid text, transactionid xid, classid oid, objid oid, objsubid int2, ! virtualtransaction text, pid int4, mode text, granted boolean); CREATE VIEW pg_cursors AS SELECT C.name, C.statement, C.is_holdable, C.is_binary, C.is_scrollable, C.creation_time ! FROM pg_cursor() AS C ! (name text, statement text, is_holdable boolean, is_binary boolean, ! is_scrollable boolean, creation_time timestamptz); CREATE VIEW pg_prepared_xacts AS SELECT P.transaction, P.gid, P.prepared, U.rolname AS owner, D.datname AS database FROM pg_prepared_xact() AS P - (transaction xid, gid text, prepared timestamptz, ownerid oid, dbid oid) LEFT JOIN pg_authid U ON P.ownerid = U.oid LEFT JOIN pg_database D ON P.dbid = D.oid; CREATE VIEW pg_prepared_statements AS SELECT P.name, P.statement, P.prepare_time, P.parameter_types, P.from_sql ! FROM pg_prepared_statement() AS P ! (name text, statement text, prepare_time timestamptz, ! parameter_types regtype[], from_sql boolean); CREATE VIEW pg_settings AS SELECT * ! FROM pg_show_all_settings() AS A ! (name text, setting text, unit text, category text, short_desc text, extra_desc text, ! context text, vartype text, source text, min_val text, max_val text, enumvals text); CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings --- 143,169 CREATE VIEW pg_locks AS SELECT * ! FROM pg_lock_status() AS L; CREATE VIEW pg_cursors AS SELECT C.name, C.statement, C.is_holdable, C.is_binary, C.is_scrollable, C.creation_time ! FROM pg_cursor() AS C; CREATE VIEW pg_prepared_xacts AS SELECT P.transaction, P.gid, P.prepared, U.rolname AS owner, D.datname AS database FROM pg_prepared_xact() AS P LEFT JOIN pg_authid U ON P.ownerid = U.oid LEFT JOIN pg_database D ON P.dbid = D.oid; CREATE VIEW pg_prepared_statements AS SELECT P.name, P.statement, P.prepare_time, P.parameter_types, P.from_sql ! FROM pg_prepared_statement() AS P; CREATE VIEW pg_settings AS SELECT * ! FROM pg_show_all_settings() AS A; CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings Index: src/include/catalog/pg_proc.h === RCS file: /var/lib/postgresql/CVSREPO/pgsql/src/include/catalog/pg_proc.h,v retrieving revision 1.511 diff -c -r1.511 pg_proc.h *** src/include/catalog/pg_proc.h 23 Aug 2008 20:31:37 - 1.511 --- src/include/catalog/pg_proc.h 25 Aug 2008 05:28:14 - *** *** 3157,3167 DESCR(SHOW X as a function); DATA(insert OID = 2078 ( set_config PGNSP PGUID 12 1 0 0 f f f f v 3 25 25 25 16 _null_ _null_ _null_ set_config_by_name _null_ _null_ _null_ )); DESCR(SET X as a function); ! DATA(insert OID = 2084 ( pg_show_all_settings PGNSP PGUID 12 1 1000 0 f f t t s 0 2249 _null_ _null_ _null_ show_all_settings _null_ _null_ _null_ )); DESCR(SHOW ALL as a function); ! DATA(insert OID = 1371 ( pg_lock_status PGNSP PGUID 12 1 1000 0 f f t t v 0 2249 _null_ _null_ _null_ pg_lock_status _null_ _null_ _null_ )); DESCR(view system lock information); ! DATA(insert OID = 1065 ( pg_prepared_xact PGNSP PGUID 12 1 1000 0 f f t t v 0 2249 _null_ _null_ _null_ pg_prepared_xact _null_ _null_ _null_ )); DESCR(view two-phase transactions); DATA(insert OID = 2079 ( pg_table_is_visible PGNSP PGUID 12 1 0 0 f f t f s 1 16 26 _null_ _null_ _null_ pg_table_is_visible _null_ _null_ _null_ )); --- 3157,3167 DESCR(SHOW X as a function); DATA(insert OID = 2078 ( set_config PGNSP PGUID 12 1 0 0 f f f f v 3 25 25 25 16 _null_ _null_ _null_ set_config_by_name _null_ _null_ _null_ )); DESCR(SET X as a function); ! DATA(insert OID = 2084 ( pg_show_all_settings PGNSP PGUID 12 1 1000 0 f f t t s 0 2249 {25,25,25,25,25,25,25,25,25,25,25,25} {o,o,o,o,o,o,o,o,o,o,o,o} {name,setting,unit,category,short_desc,extra_desc,context,vartype,source,min_val,max_val,enumvals} show_all_settings _null_ _null_ _null_ )); DESCR(SHOW ALL
Re: [PATCHES] [PgFoundry] Unsigned Data Types [1 of 2]
On Sun, Aug 31, 2008 at 3:35 PM, Ryan Bradetich [EMAIL PROTECTED] wrote: Hello all, a few comments. - i think you have to add some more comments in uint.c file and maybe a header indicating this is part of the postgresql project or that is intended to use with postgres or something of the like - what is uint1? i know int, int2, int4, int8 so i think we should have uint, uint2, uint4 (maybe uint8?) uint-base.tar.bz2 -- The core of the unsigned integer type. seems there is something wrong in the unlikely macro (i'm using GCC 4.2.3 in Ubuntu 4.2.3-2ubuntu7 with amd64) postgres=# select -256::uint1; ERROR: uint1 out of range STATEMENT: select -256::uint1; ERROR: uint1 out of range postgres=# select -255::uint1; ?column? -- -255 (1 row) postgres=# select -2::uint1; ?column? -- -2 (1 row) postgres=# select -5::uint1 + 30::uint1; ?column? -- 25 (1 row) uint-tests.tar.bz2 -- The regression tests. here failed two regression tests but that is because the path * Converted build system to use PGXS (more portable). the Makefile doesn't work here... i have installed postgres 8.3.3 from ubuntu package and the test env i compile manually (the uint module tried to install in the ubuntu location while it should in the env location) attached a Makefile that fix that i still have to make some more test... -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. (593) 87171157 Makefile Description: Binary data regression.diffs Description: Binary data -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [PgFoundry] Unsigned Data Types [1 of 2]
On Sat, Sep 6, 2008 at 3:57 PM, Tom Lane [EMAIL PROTECTED] wrote: Jaime Casanova [EMAIL PROTECTED] writes: seems there is something wrong in the unlikely macro (i'm using GCC 4.2.3 in Ubuntu 4.2.3-2ubuntu7 with amd64) postgres=# select -256::uint1; ERROR: uint1 out of range No, that's just because this is parsed as -(256::uint1) actually, i thought that case is right but the -255::uint1 returning a negative number (aka -255) is what bothers me -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. (593) 87171157 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [PgFoundry] Unsigned Data Types [1 of 2]
On Sat, Sep 6, 2008 at 3:57 PM, Tom Lane [EMAIL PROTECTED] wrote: Jaime Casanova [EMAIL PROTECTED] writes: seems there is something wrong in the unlikely macro (i'm using GCC 4.2.3 in Ubuntu 4.2.3-2ubuntu7 with amd64) postgres=# select -256::uint1; ERROR: uint1 out of range No, that's just because this is parsed as -(256::uint1) ah! ok, i see the point... postgres=# select 256::uint1; ERROR: uint1 out of range but is right that way of parsing? so i get a negative number instead of an error? -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. (593) 87171157 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [PgFoundry] Unsigned Data Types [1 of 2]
On Sat, Sep 6, 2008 at 7:10 PM, Tom Lane [EMAIL PROTECTED] wrote: Now, if (-255)::uint1 fails to throw error, that would be a bug IMHO. Casting any negative value to uint ought to fail, no? then the patch is right but it seems to me like that is broking the law of less surprise i expected -2::uint1 to be equivalent to (-2)::uint1 that should be at least documented, no? -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. (593) 87171157 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [PgFoundry] Unsigned Data Types [1 of 2]
On Sat, Sep 6, 2008 at 3:41 PM, Jaime Casanova [EMAIL PROTECTED] wrote: i still have to make some more test... why i need the cast in this case? even if the cast is really necesary (the message seems realy ugly) contrib_regression=# select * from t1 where f1 35; ERROR: unsupported type: 16486 contrib_regression=# select * from t1 where f1 35::uint4; f1 - 36 37 38 -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. (593) 87171157 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [PgFoundry] Unsigned Data Types [1 of 2]
On Sun, Sep 7, 2008 at 2:41 AM, Tom Lane [EMAIL PROTECTED] wrote: Jaime Casanova [EMAIL PROTECTED] writes: contrib_regression=# select * from t1 where f1 35; ERROR: unsupported type: 16486 That obviously isn't supposed to happen. Where's it coming from exactly? convert_numeric_to_scalar() in src/backend/utils/adt/selfuncs.c the problem seems to be that we are asking for each type of numeric and of course that doesn't know nothing about unsigned integers so its treating it as a non-numeric. don't know what to suggest here? a column in pg_type that identifies it? a hook? switch (typid) { case BOOLOID: return (double) DatumGetBool(value); case INT2OID: return (double) DatumGetInt16(value); case INT4OID: return (double) DatumGetInt32(value); case INT8OID: return (double) DatumGetInt64(value); case FLOAT4OID: return (double) DatumGetFloat4(value); case FLOAT8OID: return (double) DatumGetFloat8(value); case NUMERICOID: /* Note: out-of-range values will be clamped to +-HUGE_VAL */ return (double) DatumGetFloat8(DirectFunctionCall1(numeric_float8_no_overflow, value)); case OIDOID: case REGPROCOID: case REGPROCEDUREOID: case REGOPEROID: case REGOPERATOROID: case REGCLASSOID: case REGTYPEOID: case REGCONFIGOID: case REGDICTIONARYOID: /* we can treat OIDs as integers... */ return (double) DatumGetObjectId(value); } /* * Can't get here unless someone tries to use scalarltsel/scalargtsel on * an operator with one numeric and one non-numeric operand. */ elog(ERROR, unsupported type: %u, typid); return 0; -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. (593) 87171157 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [PgFoundry] Unsigned Data Types [1 of 2]
On Mon, Sep 8, 2008 at 1:14 AM, Ryan Bradetich [EMAIL PROTECTED] wrote: If you read the comments around that stuff it leaves quite a lot to be desired, but I don't really have better ideas at the moment. The best near-term solution for the uint module is probably not to rely on scalarltsel/scalargtsel for uint comparisons, but to make its own selectivity functions that know the uint types plus whatever standard types you want to have comparisons with. Ok. Looks like I need to review these functions and develop new functions specific for the unsigned type. the same problem happens in joins, unions, hash, etc... so you have to look at those functions as well PS: Jaime not Jamie :) -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. (593) 87171157 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [PgFoundry] Unsigned Data Types [1 of 2]
On Mon, Sep 8, 2008 at 10:08 PM, Ryan Bradetich [EMAIL PROTECTED] wrote: Can you send me the test case that generates this error? My regression tests do not include a table t1 so I was not able to reproduce this error directly. yeah! that table is mine! here are the scripts... contrib_regression=# select f1 from t1 where f1 8; f1 9 10 (2 rows) My testing shows this is working correctly. mmm... i rebuild my test env and it works for me this time... until i execute an analyze. I guess autovacuum executed an auto analyze last time... -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. (593) 87171157 drop table t1_int4; create table t1_int4 (f1 int4 primary key); insert into t1_int4 select generate_series(1, 255); select * from t1_int4, generate_series(1, 10) as foo where t1_int4.f1 = foo; drop table t1_uint4; create table t1_uint4 (f1 uint4 primary key); insert into t1_uint4 select generate_series(1, 255); select * from t1_uint4, generate_series(1, 10) as foo where t1_uint4.f1 = foo; drop table if exists t1_int4; create table t1_int4 (f1 int4 primary key); insert into t1_int4 select generate_series(1, 255); analyze; select * from t1_int4 where f1 30; drop table if exists t1_uint4; create table t1_uint4 (f1 uint4 primary key); insert into t1_uint4 select generate_series(1, 255); analyze; select * from t1_uint4 where f1 30; explain analyze select * from t1_uint4 where f1 30; -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [PgFoundry] Unsigned Data Types [1 of 2]
On 9/15/08, Ryan Bradetich [EMAIL PROTECTED] wrote: Hello Jaime, I have the code and regression tests updated to solve the problems you initially discovered. great, i will test during this week... -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. (593) 87171157 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [PgFoundry] Unsigned Data Types [1 of 2]
On Mon, Sep 15, 2008 at 9:45 PM, Ryan Bradetich [EMAIL PROTECTED] wrote: I have the code and regression tests updated to solve the problems you initially discovered. After code reading, stepping through with the debugger, and help from RhodiumToad on irc I was able to implement new restrict selective estimation functions for the uint4 vs int4 data types. The uint1 vs int4 and uint2 vs int4 data types did not require a custom restrict selective estimation function. i'm still seeing the failures in the copy commands (the ones about the paths) i'm not really sure if this matters. contrib_regression=# select 256::int2::int4; int4 -- 256 (1 row) contrib_regression=# select 256::uint2::int4; int4 -- 256 (1 row) contrib_regression=# select 256::int2::uint4; ERROR: cannot cast type smallint to uint4 at character 17 STATEMENT: select 256::int2::uint4; ERROR: cannot cast type smallint to uint4 LINE 1: select 256::int2::uint4; otherwise seems fine -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches