[HACKERS] Implementation of median in PostgreSQL - questions
Hello I am planning to start to implement median function. I wrote some array based implementation - it is fast, but I hope, so can be much faster. The basic question is method of implementation. It can be implemented via a) custum aggregate functions or b) executor node. Adventage of @a variant is simplicity - we need to teach aggregates to ensure ordered input and ensure to use index only (maybe add flag ORDERED INPUT [DESC|ASC]). Now PostgreSQL doesn't use a index for scan to orderd aggregate - it can be a problem for large datasets. I found some missing info in EXPLAIN about ordered aggregates - there are showed nothing about sort pa...@postgres:5432=# explain analyze verbose select array_agg(a order by a) from omega; QUERY PLAN --- Aggregate (cost=1643.00..1643.01 rows=1 width=4) (actual time=555.091..555.092 rows=1 loops=1) Output: array_agg(a ORDER BY a) -> Seq Scan on public.omega (cost=0.00..1393.00 rows=10 width=4) (actual time=0.050..177.547 rows=10 loops=1) Output: a Total runtime: 555.839 ms (5 rows) Probably we have to access a tuple store inside sfunc - when the data size is out of work memory. And for effective evaluating it needs patch https://commitfest.postgresql.org/action/patch_view?id=292 variant @b is more complex - but allows more possibilities - idea: median is one from aggregate executor nodes (theoretically it can call some custom final function in future - but I don't think about it now). It has a few advantages: a) we don't need to modify current aggregates b) for datasets smaller than working_mem can be used a quickselect algorithms - www-stat.stanford.edu/~ryantibs/papers/median.pdf c) for larger datasets we can use integrated external sort with direct reading - we don't need to stack result to array I prefer a variant b. It offers a more possibilities - and there are less chance to break some existing. comments are welcome Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
2010/7/5 Florian Pflug : > On Jul4, 2010, at 13:57 , Pavel Stehule wrote: >>> I don't really buy that argument. By using a psql variable, you simply move >>> the quoting & escaping business from SQL to the shell where psql is called. >>> True, you avoid SQL injectiont, but in turn you make yourself vulnerable to >>> shell injection. >> >> can you show some example of shell injection? For me, this way via >> psql variables is the best. There are clean interface between outer >> and inner space. And I can call simply just psql scripts - without >> external bash. > > Well, on the one hand you have (with your syntax) > echo "DO (a int := $VALUE) $$ ... $$" | psql > which allows sql injection if $VALUE isn't sanitized or quoted & escaped > properly. sure - but it is same for you syntax, isn't it? This is classical dynamic SQL - and more used in from untyped language. > > On the other hand you have > echo "DO (a int := :value) $$ ... $$$ | psql --variable value=$VALUE > which allows at least injection of additional arguments to psql if $VALUE > contains spaces. You might try to avoid that by encoding value=$VALUE in > double quotes, but I doubt that it's 100% safe even then. [pa...@nemesis ~]$ cat y.sh a='some variable with " ajjaja" jjaja' b='other variable with "jaja' c="third 'variable" psql postgres --variable a="$a" --variable b="$b" --variable c="$c" < > The point is that interpolating the value into the command is always risky, > independent from whether it's a shell command or an sql command. > > best regards, > Florian Pflug > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Always truncate segments before unlink
I have a report from an user that postgres server gave up REINDEX commands on the almost-disk-full machine. The disk spaces were filled with old index segments, that should be replaced with re-constructed files made by the REINDEX. In mdunlink(), we truncate the first main fork to zero length and actually unlink at the next checkpoint, but other segments are not truncated and only unlinked. Then, if another backend open the segments, disk spaces occupied by them are not reclaimed until all of the backends close their file descriptors. Longer checkpoint timeout and connection pooling make things worse. I'd like to suggest that we always truncate any segments before unlink them. The truncate-and-unlink hack seems to be developed to avoid reuse of relfilenode: | Leaving the empty file in place prevents that relfilenode | number from being reused. but is also useful to release disk spaces in the early stages. Am I missing something? Comments welcome. Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
On Jul4, 2010, at 13:57 , Pavel Stehule wrote: >> I don't really buy that argument. By using a psql variable, you simply move >> the quoting & escaping business from SQL to the shell where psql is called. >> True, you avoid SQL injectiont, but in turn you make yourself vulnerable to >> shell injection. > > can you show some example of shell injection? For me, this way via > psql variables is the best. There are clean interface between outer > and inner space. And I can call simply just psql scripts - without > external bash. Well, on the one hand you have (with your syntax) echo "DO (a int := $VALUE) $$ ... $$" | psql which allows sql injection if $VALUE isn't sanitized or quoted & escaped properly. On the other hand you have echo "DO (a int := :value) $$ ... $$$ | psql --variable value=$VALUE which allows at least injection of additional arguments to psql if $VALUE contains spaces. You might try to avoid that by encoding value=$VALUE in double quotes, but I doubt that it's 100% safe even then. The point is that interpolating the value into the command is always risky, independent from whether it's a shell command or an sql command. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
2010/7/4 Andres Freund : > On Sun, Jul 04, 2010 at 11:38:47AM -0400, Andrew Dunstan wrote: >> >> >> Pavel Stehule wrote: >> >>BTW, we intentionally didn't put any provision for parameters into DO >> >>originally. What's changed to alter that decision? >> >> >> > >> >It just concept - nothing more. And my instinct speak so inline code >> >block without external parametrization is useless. >> > >> > >> >> You have said this before, IIRC, but frankly your instinct is just >> wrong. It is no more useless than are parameter-less functions, and >> I use those frequently. I used a DO block for some useful testing >> just the other day. > In my opinion its even *more* useful than parameterless > functions. In many cases you will use DO to write upgrade scripts or > ad-hoc code. > In both cases its not really much of diference whether you write the > parameter inside the function or outside (as a parameter to it) and > escaping is not a critical part anyway. > > So maybe I am missing the point of this discussion? when the parameter are not outside, then they are not accessable from psql. psql's variable expansion isn't working inside code literal. So you have not any way to put some external parameters - for example - when I would to prepare scripts for administration of databases for some user - cleaning schema, preparing schema, etc, then I have to write username directly to script. I cannot use a possibility of psql to specify variables. Regards Pavel > > Andres > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
2010/7/4 Tom Lane : > Andrew Dunstan writes: >> This whole proposal strikes me as premature. What we need is some >> experience from the field in using DO before we can sensibly decide how >> it should be extended. And we won't get that until 9.0 has been released >> and used for a while. > > +1. > > What strikes me about this proposal is that there isn't any way to pass > parameter strings without worrying about how to escape them; which means > that the actual functionality gain over 9.0 is at best rather limited. > > Now you could get to that if we had support for utility statements > accepting parameter symbols, ie you could execute > DO ... USING $1, $2 > with out-of-line parameter values passed using the PQexecParams protocol. > So maybe that's an orthogonal feature that should be done as a separate > patch, but without it I'm not sure there's really much point. If I remember well, you wrote so this way isn't directly possible. You have to know a targer datatype - so you have to use syntax DO(target type list) ... USING ... and there have to be mechanisms to put these values to PL. Maybe you think to use only varchar variables and then access to values via array (from PL)? little bit different question - but I hope related to topic. I thinking about CALL statement and "true procedures". There are three request - transaction control, multi record sets, and using IN, OUT parameters (compatibility issue and conformance with standard). Now I don't know - CALL statement have to be util statement or classic plan statement? I inclined to think so util statement can be better. But I would to use a IN and OUT variables too - so some support for PQexecParams protocol can be nice CREATE OR REPLACE PROCEDURE foo(IN a int, IN b int, OUT c int) ... and using from psql CALL foo(10,10, :result); \echo :result Pavel > > IIRC one of the stumbling blocks for parameters in utility statements > is that usually there's no good context for inferring their data types. > If we were to extend DO in the particular way Pavel suggests, then > there would be context for that case, but I'm not sure what we do about > the general case. We'd want to think about that before installing a > special-purpose rule that only works for DO. > > regards, tom lane > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
On Sun, Jul 04, 2010 at 11:38:47AM -0400, Andrew Dunstan wrote: > > > Pavel Stehule wrote: > >>BTW, we intentionally didn't put any provision for parameters into DO > >>originally. What's changed to alter that decision? > >> > > > >It just concept - nothing more. And my instinct speak so inline code > >block without external parametrization is useless. > > > > > > You have said this before, IIRC, but frankly your instinct is just > wrong. It is no more useless than are parameter-less functions, and > I use those frequently. I used a DO block for some useful testing > just the other day. In my opinion its even *more* useful than parameterless functions. In many cases you will use DO to write upgrade scripts or ad-hoc code. In both cases its not really much of diference whether you write the parameter inside the function or outside (as a parameter to it) and escaping is not a critical part anyway. So maybe I am missing the point of this discussion? Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
Andrew Dunstan writes: > This whole proposal strikes me as premature. What we need is some > experience from the field in using DO before we can sensibly decide how > it should be extended. And we won't get that until 9.0 has been released > and used for a while. +1. What strikes me about this proposal is that there isn't any way to pass parameter strings without worrying about how to escape them; which means that the actual functionality gain over 9.0 is at best rather limited. Now you could get to that if we had support for utility statements accepting parameter symbols, ie you could execute DO ... USING $1, $2 with out-of-line parameter values passed using the PQexecParams protocol. So maybe that's an orthogonal feature that should be done as a separate patch, but without it I'm not sure there's really much point. IIRC one of the stumbling blocks for parameters in utility statements is that usually there's no good context for inferring their data types. If we were to extend DO in the particular way Pavel suggests, then there would be context for that case, but I'm not sure what we do about the general case. We'd want to think about that before installing a special-purpose rule that only works for DO. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
2010/7/4 Andrew Dunstan : > > > Pavel Stehule wrote: >>> >>> BTW, we intentionally didn't put any provision for parameters into DO >>> originally. What's changed to alter that decision? >>> >>> >> >> It just concept - nothing more. And my instinct speak so inline code >> block without external parametrization is useless. >> >> >> > > You have said this before, IIRC, but frankly your instinct is just wrong. It > is no more useless than are parameter-less functions, and I use those > frequently. I used a DO block for some useful testing just the other day. > > This whole proposal strikes me as premature. What we need is some experience > from the field in using DO before we can sensibly decide how it should be > extended. And we won't get that until 9.0 has been released and used for a > while. > just we have different opinion Regards Pavel > cheers > > andrew > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
Pavel Stehule wrote: BTW, we intentionally didn't put any provision for parameters into DO originally. What's changed to alter that decision? It just concept - nothing more. And my instinct speak so inline code block without external parametrization is useless. You have said this before, IIRC, but frankly your instinct is just wrong. It is no more useless than are parameter-less functions, and I use those frequently. I used a DO block for some useful testing just the other day. This whole proposal strikes me as premature. What we need is some experience from the field in using DO before we can sensibly decide how it should be extended. And we won't get that until 9.0 has been released and used for a while. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
2010/7/4 Tom Lane : > Pavel Stehule writes: >> my syntax is reflecting fact, so these are not true parameters - it's >> +/- similar to default values of function parameters. > > FWIW, that doesn't seem like a positive to me. > >> You cannot to >> write do (a int := $1) $$ ... $$ - because utils statements hasn't >> have variables. > > Yet. I don't particularly want to relax that either, but the syntax of > this feature shouldn't assume it'll be true forever. > > I think it's better to not confuse these things with default parameters, > so Florian's idea looks better to me. Maybe I am didn't explain well my idea. The most all is modificated named notation enhanced about type info. It isn't default parameter definition - so I use ":=" and not use "=". And it has same advantage like named notation has. Using a keyword "USING" isn't perfectly clean for me - I have a problem with position of parameters - but if other people feel it different, I'll not have a problem. do(a int := 20, b int := 20) $$ ... $$; do (a int, b int) $$ $$ USING 10,20; generally both syntaxes are used now. This patch is just concept - I spoke it, I would to show attractive behave, and Florian showed possible wery nice colaboration shell with psql. I don't want to insult somebody. Regards Pavel Stehule > > BTW, we intentionally didn't put any provision for parameters into DO > originally. What's changed to alter that decision? > > regards, tom lane > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
2010/7/4 Tom Lane : > Pavel Stehule writes: >> my syntax is reflecting fact, so these are not true parameters - it's >> +/- similar to default values of function parameters. > > FWIW, that doesn't seem like a positive to me. > >> You cannot to >> write do (a int := $1) $$ ... $$ - because utils statements hasn't >> have variables. > > Yet. I don't particularly want to relax that either, but the syntax of > this feature shouldn't assume it'll be true forever. > > I think it's better to not confuse these things with default parameters, > so Florian's idea looks better to me. > > BTW, we intentionally didn't put any provision for parameters into DO > originally. What's changed to alter that decision? > > regards, tom lane > It just concept - nothing more. And my instinct speak so inline code block without external parametrization is useless. Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
On Sun, July 4, 2010 9:58 am, Tom Lane wrote: > > BTW, we intentionally didn't put any provision for parameters into DO > originally. What's changed to alter that decision? > Nothing that I know of, I think there is just a little impatience here. I think the consensus was that we needed to get some experience of DO in the field before looking at a parameter mechanism. I still think that's the correct position. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
Pavel Stehule writes: > my syntax is reflecting fact, so these are not true parameters - it's > +/- similar to default values of function parameters. FWIW, that doesn't seem like a positive to me. > You cannot to > write do (a int := $1) $$ ... $$ - because utils statements hasn't > have variables. Yet. I don't particularly want to relax that either, but the syntax of this feature shouldn't assume it'll be true forever. I think it's better to not confuse these things with default parameters, so Florian's idea looks better to me. BTW, we intentionally didn't put any provision for parameters into DO originally. What's changed to alter that decision? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pessimal trivial-update performance
Robert Haas writes: > On Sun, Jul 4, 2010 at 12:11 AM, Tom Lane wrote: >> I believe that none of the dead row versions can be vacuumed during this >> test. > Yep, you seem to be right. The table grows to 802 pages. But why is > it that we can't vacuum them as we go along? Sure. What you'd need is for HeapTupleSatisfiesVacuum to observe that (a) the tuple's xmin and xmax are equal, (b) they're equal to my own transaction's XID, (c) none of the live snapshots in my backend can see cmin but not cmax, (d) cmax < currentCommandId, ensuring that every future snapshot will see cmax too (not quite convinced this is certain to hold). Now that we have a centralized list of all live snapshots, it's at least possible in principle to do (c). (I'm ignoring the possibility that the xmin and xmax are from different subtransactions of my own XID --- that seems to complicate matters greatly in order to handle even-more-cornerish cases.) Of course, you'd also need to get to HeapTupleSatisfiesVacuum in the first place. The complained-of case lacks any VACUUM call. Maybe a HOT cleanup would happen at the right time but I'm not sure. If it doesn't, adding one would represent a significant expenditure that would usually not be repaid. Another issue here is that since xmin is certainly within the GlobalXmin horizon, it would be essential to preserve the update chain ctid links, ie, make the tuple's update predecessor point to its successor. That seems workable for the case of cleaning out an intermediate entry in a HOT chain, but not otherwise. Details left as an exercise for the student. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
2010/7/4 Florian Pflug : > On Jul4, 2010, at 11:59 , Pavel Stehule wrote: >> 2010/7/4 Florian Pflug : >>> On Jul4, 2010, at 08:41 , Pavel Stehule wrote: I enhanced DO statement syntax to allowing a parameters. Syntax is relative simple: do ([varname] vartype := value, ...) $$ ... $$ >>> >>> I think it'd be more useful to put the values at the very end of the >>> statement, not somewhere in the middle. For positional parameters I envision >>> >>> do (vartype, ...) $$ ... $$ using value, ... >>> >>> and for named parameters it'd be >>> >>> do (varname vartype) $$ ... $$ using varname := value, ... > >> Your syntax is longer and less readable (my personal view). With >> proposed syntax it is ensured so every parameter has a value. Next - >> my syntax is reflecting fact, so these are not true parameters - it's >> +/- similar to default values of function parameters. > > Yeah, with your syntax omitting a value is syntactically invalid, while with > mine it'd parse OK and fail later on. But I fail to see the drawback of that. > I do agree that my suggestion is slightly more verbose, but it think thats > compensated by the increase in usefulness. > >> I understand to your motivation - but you can use a printf command and >> do it same work. > > Sure. But by the very same argument, printf makes DO-block parameters > redundant as a whole. > printf isn't nice, agree - it is just workaround for some special case - when you don't store code in variable, then you have not any problems. >> or better and safer - use a psql variables (it is preferred solution) > > I don't really buy that argument. By using a psql variable, you simply move > the quoting & escaping business from SQL to the shell where psql is called. > True, you avoid SQL injectiont, but in turn you make yourself vulnerable to > shell injection. can you show some example of shell injection? For me, this way via psql variables is the best. There are clean interface between outer and inner space. And I can call simply just psql scripts - without external bash. best regards Pavel p.s. theoretically do statement can support both syntax, maybe mix of all. It's only about 20 lines more in parser. But code will be little bit more complex and I am not sure if it is necessary. I dislike the space between variable definition and values - and you have to put param list on the statement end. > > best regards, > Florian Pflug > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
On Jul4, 2010, at 11:59 , Pavel Stehule wrote: > 2010/7/4 Florian Pflug : >> On Jul4, 2010, at 08:41 , Pavel Stehule wrote: >>> I enhanced DO statement syntax to allowing a parameters. Syntax is >>> relative simple: >>> >>> do ([varname] vartype := value, ...) $$ ... $$ >> >> I think it'd be more useful to put the values at the very end of the >> statement, not somewhere in the middle. For positional parameters I envision >> >> do (vartype, ...) $$ ... $$ using value, ... >> >> and for named parameters it'd be >> >> do (varname vartype) $$ ... $$ using varname := value, ... > Your syntax is longer and less readable (my personal view). With > proposed syntax it is ensured so every parameter has a value. Next - > my syntax is reflecting fact, so these are not true parameters - it's > +/- similar to default values of function parameters. Yeah, with your syntax omitting a value is syntactically invalid, while with mine it'd parse OK and fail later on. But I fail to see the drawback of that. I do agree that my suggestion is slightly more verbose, but it think thats compensated by the increase in usefulness. > I understand to your motivation - but you can use a printf command and > do it same work. Sure. But by the very same argument, printf makes DO-block parameters redundant as a whole. > or better and safer - use a psql variables (it is preferred solution) I don't really buy that argument. By using a psql variable, you simply move the quoting & escaping business from SQL to the shell where psql is called. True, you avoid SQL injectiont, but in turn you make yourself vulnerable to shell injection. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
2010/7/4 Florian Pflug : > On Jul4, 2010, at 08:41 , Pavel Stehule wrote: >> I enhanced DO statement syntax to allowing a parameters. Syntax is >> relative simple: >> >> do ([varname] vartype := value, ...) $$ ... $$ > > > I think it'd be more useful to put the values at the very end of the > statement, not somewhere in the middle. For positional parameters I envision > > do (vartype, ...) $$ ... $$ using value, ... > > and for named parameters it'd be > > do (varname vartype) $$ ... $$ using varname := value, ... > > I won't make a difference for your use-case, but it'd make it easier to call > the same DO block with different parameters, like in the following shell > snippet. > > COMMANDS="DO (arg int) $$ ... $$" > (for a in arg1, arg2, arg3, arg4; do > echo "$COMMANDS USING $a;" > done) | psql > Your syntax is longer and less readable (my personal view). With proposed syntax it is ensured so every parameter has a value. Next - my syntax is reflecting fact, so these are not true parameters - it's +/- similar to default values of function parameters. You cannot to write do (a int := $1) $$ ... $$ - because utils statements hasn't have variables. I understand to your motivation - but you can use a printf command and do it same work CMD='do(a int := %s) $$ begin raise notice ''%%'',a; end; $$' for a in $1 $2 $3 $4 do if [ -n "$a" ] then echo `printf "$CMD" $a` | psql postgres fi done; or better and safer - use a psql variables (it is preferred solution) for a in $1 $2 $3 $4 do if [ -n "$a" ] then psql postgres --quiet --variable a=$a < best regards, > Florian Pflug > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proof concept: do statement parametrization
On Jul4, 2010, at 08:41 , Pavel Stehule wrote: > I enhanced DO statement syntax to allowing a parameters. Syntax is > relative simple: > > do ([varname] vartype := value, ...) $$ ... $$ I think it'd be more useful to put the values at the very end of the statement, not somewhere in the middle. For positional parameters I envision do (vartype, ...) $$ ... $$ using value, ... and for named parameters it'd be do (varname vartype) $$ ... $$ using varname := value, ... I won't make a difference for your use-case, but it'd make it easier to call the same DO block with different parameters, like in the following shell snippet. COMMANDS="DO (arg int) $$ ... $$" (for a in arg1, arg2, arg3, arg4; do echo "$COMMANDS USING $a;" done) | psql best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pessimal trivial-update performance
Am 04.07.2010 06:11, wrote Tom Lane: ... but is it representative of real-world cases? regards, tom lane Hi Tom, we do run an application in productive use that suffered from a similar effect. We did not have 100 updates per row, but 10-100 updates per row on about 1-10 million rows of a table. In the end we managed to increase performance by factor of more than two by adding support to the application to track updates internally and only "flush" changes to the database at the (final) application commit. This did cost a lot as now we needed to adjust queries on the table with data stored internally (as not yet reflected in the database). This still is more efficient as updating and performing operation an the database directly. (e.g. an update using the primary key of the table (about 50 million rows total) would have lasted over 3 seconds(!) while initially the very same update was done within far below 1ms). So I think this could qualify as a real world example of that case. Regards, Rainer -- Rainer Pruy Managing Director Acrys Consult GmbH & Co. KG Theodor-Heuss-Str. 53-63, 61118 Bad Vilbel, Germany Phone: +49-6101-98760-0 Fax: +49-6101-98760-50 Web: http://www.acrys.com - Email: off...@acrys.com Registered: Frankfurt am Main, HRA 31151 General partner: Acrys Verwaltungs GmbH Theodor-Heuss-Str. 53-63, D-61118 Bad Vilbel Registered: Frankfurt am Main, HRB 57625 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
