Re: [HACKERS] explain and PARAM_EXEC
Robert Haas writes: > On Fri, Feb 19, 2010 at 11:33 PM, Tom Lane wrote: >> We can also fetch that tuple's >> relfilenode and pass it to the subplan, which we do by setting the $0 >> Param value before invoking the subplan. > Are the same tuples in scope when evaluating the expression that sets > $0 as were in scope when evaluating ((b.oid)::integer + 1)? Yes, exactly the same. The parameter-value expression is just like any other scalar expression that could appear where the SubPlan reference is. It doesn't know anything about the subplan, really. 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] explain and PARAM_EXEC
On Fri, Feb 19, 2010 at 11:33 PM, Tom Lane wrote: > Robert Haas writes: >> On Fri, Feb 19, 2010 at 10:22 PM, Tom Lane wrote: >>> Maybe, but the only reasonable place to put it would be within the >>> (SubPlan N) reference, > >> I thought maybe it could do something like this: > >> SubPlan 1 >> Parameters: $0 := b.oid >> -> Index Scan etc. > > No, that's the wrong end of the stick --- that's like trying to annotate > a function definition with the actual parameter values being passed to > it from somewhere else. You haven't got the info there, and even if you > did, it's assuming that there is exactly one call site for any subplan. OK. Will have to think this one over. >> I am under the (perhaps faulty) impression that when evaluating an >> expression there can only ever be three tuples in score: inner, outer, >> and scan. So when we go to evaluate the expression whose result will >> be assigned to $0, where do we get those inner and/or outer and/or >> scan tuples from? IOW, I understand where the subplan is putting its >> OUTPUT, what I don't understand is what context is being used to set >> its input parameters. > > Consider this small mod on your example: > > regression=# explain (verbose) select oid::int + 1,(select oid from pg_class > a where a.oid = b.relfilenode) from pg_class b; > QUERY PLAN > > Seq Scan on pg_catalog.pg_class b (cost=0.00..5573.04 rows=671 width=8) > Output: ((b.oid)::integer + 1), (SubPlan 1) > SubPlan 1 > -> Index Scan using pg_class_oid_index on pg_catalog.pg_class a > (cost=0.00..8.27 rows=1 width=4) > Output: a.oid > Index Cond: (a.oid = $0) > (6 rows) > > When we are evaluating the output targetlist of the seqscan node, we > have a scan tuple of pg_class b in scope. We can fetch that tuple's > oid and use it in the first expression. OK. > We can also fetch that tuple's > relfilenode and pass it to the subplan, which we do by setting the $0 > Param value before invoking the subplan. Are the same tuples in scope when evaluating the expression that sets $0 as were in scope when evaluating ((b.oid)::integer + 1)? > The subplan runs an indexscan > and returns a single scalar value (to wit, a.oid from some row of > pg_class a), which becomes the value of the (SubPlan 1) reference > back at the evaluation of the seqscan's targetlist. This part I get, 100%. ...Robert -- 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] explain and PARAM_EXEC
Robert Haas writes: > On Fri, Feb 19, 2010 at 10:22 PM, Tom Lane wrote: >> Maybe, but the only reasonable place to put it would be within the >> (SubPlan N) reference, > I thought maybe it could do something like this: > SubPlan 1 > Parameters: $0 := b.oid > -> Index Scan etc. No, that's the wrong end of the stick --- that's like trying to annotate a function definition with the actual parameter values being passed to it from somewhere else. You haven't got the info there, and even if you did, it's assuming that there is exactly one call site for any subplan. > I am under the (perhaps faulty) impression that when evaluating an > expression there can only ever be three tuples in score: inner, outer, > and scan. So when we go to evaluate the expression whose result will > be assigned to $0, where do we get those inner and/or outer and/or > scan tuples from? IOW, I understand where the subplan is putting its > OUTPUT, what I don't understand is what context is being used to set > its input parameters. Consider this small mod on your example: regression=# explain (verbose) select oid::int + 1,(select oid from pg_class a where a.oid = b.relfilenode) from pg_class b; QUERY PLAN Seq Scan on pg_catalog.pg_class b (cost=0.00..5573.04 rows=671 width=8) Output: ((b.oid)::integer + 1), (SubPlan 1) SubPlan 1 -> Index Scan using pg_class_oid_index on pg_catalog.pg_class a (cost=0.00..8.27 rows=1 width=4) Output: a.oid Index Cond: (a.oid = $0) (6 rows) When we are evaluating the output targetlist of the seqscan node, we have a scan tuple of pg_class b in scope. We can fetch that tuple's oid and use it in the first expression. We can also fetch that tuple's relfilenode and pass it to the subplan, which we do by setting the $0 Param value before invoking the subplan. The subplan runs an indexscan and returns a single scalar value (to wit, a.oid from some row of pg_class a), which becomes the value of the (SubPlan 1) reference back at the evaluation of the seqscan's targetlist. It's really not much different from a function call with subplans as functions. The PARAM_EXEC stuff looks just like 1950's era non-reentrant function parameter passing mechanisms, back before anybody had thought of recursive functions and they passed a function's parameters in fixed storage locations. It's okay for this because subplan trees are never recursive ... 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] explain and PARAM_EXEC
On Fri, Feb 19, 2010 at 11:05 PM, Robert Haas wrote: > I don't think it's a stretch to say that I'm the one who is confused. > I am under the (perhaps faulty) impression that when evaluating an > expression there can only ever be three tuples in score: inner, outer, s/score/scope. > and scan. So when we go to evaluate the expression whose result will > be assigned to $0, where do we get those inner and/or outer and/or > scan tuples from? IOW, I understand where the subplan is putting its > OUTPUT, what I don't understand is what context is being used to set > its input parameters. ...Robert -- 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] explain and PARAM_EXEC
On Fri, Feb 19, 2010 at 10:22 PM, Tom Lane wrote: > Robert Haas writes: >> So I guess there are two issues here: (1) somehow I feel like we >> should be telling the user what expression is being used to initialize >> $0, $1, etc. when they are PARAM_EXEC parameters; > > Maybe, but the only reasonable place to put it would be within the > (SubPlan N) reference, which is not a place where verboseness would be > appreciated, I think. In principle it could look something like > > (SubPlan N ($0 := b.oid)) > > but with a few parameters and a bunch of other stuff on the same line > that would get out of hand. I thought maybe it could do something like this: SubPlan 1 Parameters: $0 := b.oid -> Index Scan etc. ...but maybe that doesn't work if it can be called with different parameters from different places? Not sure if that's possible. >> and (2) where does >> the output list for the sequential scan "go" when there's only one >> table involved? > > The (SubPlan N) reference is meant to be understood as an expression > element yielding the output of the subplan. One of us is confused, > because I don't see any material difference between your examples; > they're all calling the subplan in the same way. I don't think it's a stretch to say that I'm the one who is confused. I am under the (perhaps faulty) impression that when evaluating an expression there can only ever be three tuples in score: inner, outer, and scan. So when we go to evaluate the expression whose result will be assigned to $0, where do we get those inner and/or outer and/or scan tuples from? IOW, I understand where the subplan is putting its OUTPUT, what I don't understand is what context is being used to set its input parameters. ...Robert -- 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] explain and PARAM_EXEC
Robert Haas writes: > So I guess there are two issues here: (1) somehow I feel like we > should be telling the user what expression is being used to initialize > $0, $1, etc. when they are PARAM_EXEC parameters; Maybe, but the only reasonable place to put it would be within the (SubPlan N) reference, which is not a place where verboseness would be appreciated, I think. In principle it could look something like (SubPlan N ($0 := b.oid)) but with a few parameters and a bunch of other stuff on the same line that would get out of hand. > and (2) where does > the output list for the sequential scan "go" when there's only one > table involved? The (SubPlan N) reference is meant to be understood as an expression element yielding the output of the subplan. One of us is confused, because I don't see any material difference between your examples; they're all calling the subplan in the same way. 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
[HACKERS] explain and PARAM_EXEC
Consider the following (rather lame) query: rhaas=# explain (verbose) select (select oid from pg_class a where a.oid = b.relfilenode) from pg_class b; QUERY PLAN Seq Scan on pg_catalog.pg_class b (cost=0.00..2250.22 rows=271 width=4) Output: (SubPlan 1) SubPlan 1 -> Index Scan using pg_class_oid_index on pg_catalog.pg_class a (cost=0.00..8.27 rows=1 width=4) Output: a.oid Index Cond: (a.oid = $0) It seems odd to me that we don't display any information about where $0 comes from or how it's initialized. Should we? I believe what's happening is that the sequential scan of b kicks out b.oid, and that then gets yanked into $0 when we invoke the subplan. But you can't really see what's happening. Interestingly, if you contrive to make the sequential scan not the toplevel plan node, then you actually do get to see what it's kicking out: rhaas=# explain (verbose) select (select oid from pg_class a where a.oid = b.oid::integer) from pg_class b, generate_series(1,5); QUERY PLAN Nested Loop (cost=0.00..2245943.89 rows=271000 width=4) Output: (SubPlan 1) -> Seq Scan on pg_catalog.pg_class b (cost=0.00..9.71 rows=271 width=4) Output: b.oid -> Function Scan on pg_catalog.generate_series (cost=0.00..10.00 rows=1000 width=0) Output: generate_series.generate_series SubPlan 1 -> Index Scan using pg_class_oid_index on pg_catalog.pg_class a (cost=0.00..8.27 rows=1 width=4) Output: a.oid Index Cond: (a.oid = ($0)::oid) (10 rows) We can even make it kick out two things: rhaas=# explain (verbose) select (select oid from pg_class a where a.oid = b.oid::integer + b.relfilenode::integer) from pg_class b, generate_series(1,5); QUERY PLAN Nested Loop (cost=0.00..2246621.39 rows=271000 width=8) Output: (SubPlan 1) -> Seq Scan on pg_catalog.pg_class b (cost=0.00..9.71 rows=271 width=8) Output: b.oid, b.relfilenode -> Function Scan on pg_catalog.generate_series (cost=0.00..10.00 rows=1000 width=0) Output: generate_series.generate_series SubPlan 1 -> Index Scan using pg_class_oid_index on pg_catalog.pg_class a (cost=0.00..8.27 rows=1 width=4) Output: a.oid Index Cond: (a.oid = ((($0)::integer + ($1)::integer))::oid) (10 rows) But if we drop the generate_series call we're back in the dark - where's the node that's emitting oid and relfilenode? rhaas=# explain (verbose) select (select oid from pg_class a where a.oid = b.oid::integer + b.relfilenode::integer) from pg_class b; QUERY PLAN Seq Scan on pg_catalog.pg_class b (cost=0.00..2250.90 rows=271 width=8) Output: (SubPlan 1) SubPlan 1 -> Index Scan using pg_class_oid_index on pg_catalog.pg_class a (cost=0.00..8.27 rows=1 width=4) Output: a.oid Index Cond: (a.oid = ((($0)::integer + ($1)::integer))::oid) (6 rows) So I guess there are two issues here: (1) somehow I feel like we should be telling the user what expression is being used to initialize $0, $1, etc. when they are PARAM_EXEC parameters; and (2) where does the output list for the sequential scan "go" when there's only one table involved? This is when you all start explaining to me why I'm woefully confused... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Directory fsync and other fun
Hi all, I started setting up some halfway automated method of simulating hard crashes and even while setting those up I found some pretty unsettling results... Now its not unlikely that my testing is flawed but unfortunately I don't see where right now (its 3am now and I have a 8h trainride behind me, so ...) The simple testsetup I have till now: Serverscript: * setup disk * start pg * wait for getting killed * setup disk * start pg Clientside: * CREATE DATABASE ... TEMPLATE crashtemplate * CHECKPOINT * make device readonly not allowing any cache flushes or such (using devicemapper) kill server * connect to database (some of the time it errors here * select * from $every_table (some time here) At first pg survived that nicely without any problems. Then I got to my senses and started adding some background io. Like: dd if=/dev/zero of=/mnt/test/foobar bs=10M count=1000 Thats where things started failing. All are logs from after the crash: 1: FATAL: could not read relation mapping file "base/140883/pg_filenode.map": Interrupted system call DEBUG: autovacuum: processing database "postgres" FATAL: could not read relation mapping file "base/140883/pg_filenode.map": Success DEBUG: autovacuum: processing database "postgres" ... FATAL: could not read relation mapping file "base/58963/pg_filenode.map": No such file or directory 2: FATAL: "base/165459" is not a valid data directory DETAIL: File "base/165459/PG_VERSION" does not contain valid data. HINT: You might need to initdb. 3: You are now connected to database "test". test=# SELECT execute('SELECT * FROM table_'||g.i) FROM generate_series(1, 3000) g(i); ERROR: XX001: could not read block 0 in file "base/124499/11652": read only 0 of 8192 bytes LOCATION: mdread, md.c:656 (that one I did not see with -o data=ordered,barrier=1,commit=300) I tried the following mount options/filesystems so far: -t ext4 -o data=writeback,barrier=1,commit=300,noauto_da_alloc -t ext4 -o data=writeback,barrier=1,commit=300 -t ext4 -o data=writeback,barrier=0,commit=300 -t ext4 -o data=ordered,barrier=0,commit=300,noauto_da_alloc -t ext4 -o data=ordered,barrier=1,commit=300,noauto_da_alloc -t ext4 -o data=ordered,barrier=1,commit=300 The same with s/ext4/ext3/ and with a commit=5. With the latter the errors were way much harder to reproduce (not that surprisingly) but still occured. I attached my preliminary scripts/hacks... They even contain a comment or two. Note though that they are a bit of a loaded gun... I guess it would be sensible trying to do some more extensive tests on a setup like that... All I tested till now was create database :-( Andres pg_crashtest_client.sh Description: application/shellscript pg_crashtest_server.sh Description: application/shellscript pg_createtemplate.sh Description: application/shellscript -- 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] Merge join and index scan strangeness
I wrote: > However, even given that, it's odd that it prefers a plan with two sorts > to a plan with one materialize. Poking around in costsize.c, I think > that the reason for this is that the rescan cost of a sort is estimated > at cpu_operator_cost per tuple, whereas rescanning a materialize node is > being estimated at cpu_tuple_cost per tuple. For a plan where rescan > cost is the dominant factor, that matters. We probably ought to make > those two estimates the same. Since neither plan node type does any > projection or qual checking, the lower number is probably the better > choice. I've done that in HEAD. I'm loath to touch it in the back branches, though, because the logic in that area now is quite different from what it was in 8.4 and earlier. As I said before, I think this isn't too important in cases where you're not forcing a mergejoin, so it seems better to not risk destabilizing plans in released branches. 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] lock_timeout GUC patch
Hi, Tom Lane írta: > Boszormenyi Zoltan writes: > >> You expressed stability concerns coming from this patch. >> Were these concerns because of locks timing out making >> things fragile or because of general feelings about introducing >> such a patch at the end of the release cycle? I was thinking >> about the former, hence this modification. >> > > Indeed, I am *very* concerned about the stability implications of this > patch. I just don't believe that arbitrarily restricting which > processes the GUC applies to will make it any safer. > > regards, tom lane > Okay, here is the rewritten lock_timeout GUC patch that uses setitimer() to set the timeout for lock timeout. I removed the GUC assignment/validation function. I left the current statement timeout vs deadlock timeout logic mostly intact in enable_sig_alarm(), because it's used by a few places. The only change is that statement_fin_time is always computed there because the newly introduced function (enable_sig_alarm_for_lock_timeout()) checks it to see whether the lock timeout triggers earlier then the deadlock timeout. As it was discussed before, this is 9.1 material. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/ diff -dcrpN pgsql.orig/doc/src/sgml/config.sgml pgsql/doc/src/sgml/config.sgml *** pgsql.orig/doc/src/sgml/config.sgml 2010-02-17 10:05:40.0 +0100 --- pgsql/doc/src/sgml/config.sgml 2010-02-19 11:29:18.0 +0100 *** COPY postgres_log FROM '/full/path/to/lo *** 4240,4245 --- 4240,4269 + + lock_timeout (integer) + +lock_timeout configuration parameter + + + + Abort any statement that tries to acquire a heavy-weight lock (e.g. rows, + pages, tables, indices or other objects) and the lock has to wait more + than the specified number of milliseconds, starting from the time the + command arrives at the server from the client. + If log_min_error_statement is set to ERROR or lower, + the statement that timed out will also be logged. A value of zero + (the default) turns off the limitation. + + + + Setting lock_timeout in + postgresql.conf is not recommended because it + affects all sessions. + + + + vacuum_freeze_table_age (integer) diff -dcrpN pgsql.orig/doc/src/sgml/ref/lock.sgml pgsql/doc/src/sgml/ref/lock.sgml *** pgsql.orig/doc/src/sgml/ref/lock.sgml 2009-09-18 08:26:40.0 +0200 --- pgsql/doc/src/sgml/ref/lock.sgml 2010-02-19 11:29:18.0 +0100 *** LOCK [ TABLE ] [ ONLY ] NOWAIT is specified, LOCK TABLE does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an !error is emitted. Once obtained, the lock is held for the !remainder of the current transaction. (There is no UNLOCK TABLE command; locks are always released at transaction end.) --- 39,49 NOWAIT is specified, LOCK TABLE does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an !error is emitted. If lock_timeout is set to a value !higher than 0, and the lock cannot be acquired under the specified !timeout value in milliseconds, the command is aborted and an error !is emitted. Once obtained, the lock is held for the remainder of !the current transaction. (There is no UNLOCK TABLE command; locks are always released at transaction end.) diff -dcrpN pgsql.orig/doc/src/sgml/ref/select.sgml pgsql/doc/src/sgml/ref/select.sgml *** pgsql.orig/doc/src/sgml/ref/select.sgml 2010-02-13 19:44:33.0 +0100 --- pgsql/doc/src/sgml/ref/select.sgml 2010-02-19 11:29:18.0 +0100 *** FOR SHARE [ OF semNum; + + do + { + ImmediateInterruptOK = interruptOK; + CHECK_FOR_INTERRUPTS(); + errStatus = semop(sema->semId, &sops, 1); + ImmediateInterruptOK = false; + } while (errStatus < 0 && errno == EINTR && !lock_timeout_detected); + + if (lock_timeout_detected) + return; + if (errStatus < 0) + elog(FATAL, "semop(id=%d) failed: %m", sema->semId); + } diff -dcrpN pgsql.orig/src/backend/port/win32_sema.c pgsql/src/backend/port/win32_sema.c *** pgsql.orig/src/backend/port/win32_sema.c 2010-01-03 12:54:22.0 +0100 --- pgsql/src/backend/port/win32_sema.c 2010-02-19 21:18:52.0 +0100 *** *** 16,21 --- 16,22 #include "miscadmin.h" #include "storage/ipc.h" #include "st
Re: [HACKERS] Fast or immediate shutdown
On Fri, Feb 19, 2010 at 2:48 PM, Bruce Momjian wrote: > Simon Riggs wrote: >> On Wed, 2009-12-16 at 17:04 +0200, Peter Eisentraut wrote: >> > On tis, 2009-12-15 at 17:19 +, Simon Riggs wrote: >> > > running with log_checkpoints = on >> > > >> > > pg_ctl -D foo -m fast stop >> > > >> > > log says >> > > >> > > LOG: received fast shutdown request >> > > LOG: aborting any active transactions >> > > LOG: shutting down >> > > LOG: restartpoint starting: shutdown immediate >> > > >> > > Some of us know that the "immediate" word refers to the restartpoint >> > > request, though that word causes conceptual conflict with the shutdown >> > > mode, which was fast, not immediate. >> > > >> > > Think we need to change the wording of this >> > > >> > > LOG: restartpoint starting: shutdown immediate >> > > >> > > so it is clearer what we mean >> > >> > We *do* we mean? And why are we logging it? >> >> The words after the colon refer to options sent to RequestCheckpoint and >> it is logged because we asked for it by doing log_checkpoints = on. >> >> I suggest we say "smoothed" when checkpoint option is !immediate. So we >> will remove the word "immediate" from the message. > > Did we decide not the change this? Personally, my opinion is that if we're going to print the message at all, the names used for the message should match the names used in the code. So -1 from me on calling it immediate in the code but smoothed in the message. On the other hand, I have no personal attachment to that message, so if other people feel it's not needed at all, I could see removing it. ...Robert -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Tom Lane writes: > Well, that isn't really going to help us in terms of what to do for 9.0. > But the possibility that something like this might happen in future is > one thing that makes me hesitant about extending CREATE LANGUAGE right > now --- the more bells and whistles we put on it, the harder it will be > to have a clean upgrade to an EXTENSION facility. Agreed, but we could still evolve the command with keeping an eye on the future. As of now I intend to implement what's on this page: http://wiki.postgresql.org/wiki/ExtensionPackaging So maybe a quick glance then some early design approval would make it possible to change the CREATE LANGUAGE in an EXTENSION compatible way. > One thing that strikes me about your proposal is that INSTALL EXTENSION > doesn't sound like a CREATE OR REPLACE operation. It sounds like a > CREATE IF NOT EXISTS operation, because there simply is not a guarantee > that what gets installed is exactly what the user expected --- in > particular, for pg_dump, it isn't guaranteeing that the new version's > extension is exactly like what was in the old database. And that's not > a bad thing, in this context; it's more or less the Whole Point. In fact it's not either one or the other, because the CREATE EXTENSION is providing the meta data, which includes an optional upgrade function. So if you INSTALL EXTENSION over an existing one, and meantime you've been installing the new version (file system install, PGAN or distro packaged or source level install; then the new CREATE EXTENSION which should be given in the foo.sql for the foo EXTENSION), in this case it's an upgrade, and what INSTALL EXTENSION is meant to do is run the upgrade function with as arguments current and new version numbers. It's when the EXTENSION is not providing this upgrade function that the behavior is more CREATE OR REPLACE, because it'd then run the installation script all over again. In case you provided an upgrade function, we're yet to see how to provide facilities to the extensions authors in order to easily address the columns of their data type and the indexes from their operator classes, etc. Regards, -- dim -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Robert Haas writes: > On Fri, Feb 19, 2010 at 2:09 PM, Tom Lane wrote: >> The sticking point for me is still whether or not it's really a good >> idea for pg_dump to be emitting CREATE OR REPLACE LANGUAGE. It does not >> do that for any other object type. On the other hand, we've already >> made languages a special case in pg_dump, since it emits the abbreviated >> form of CREATE LANGUAGE in most cases rather than trying to duplicate >> the existing object definition. Maybe there wouldn't be any bad results >> in practice. > We have all sorts of crufty hacks in pg_dump and the backend to cope > with restoration of older dumps. Compared to some of those, this is > going to be cleaner than newfallen snow. IMHO, anyway. What worries me about it is mainly the prospect that restoring a dump would silently change ownership and/or permissions of a pre-existing language. Maybe we can live with that but it's a bit nervous making. One thing we could do that would help limit the damage is have pg_dump only insert OR REPLACE when it's emitting a parameterless CREATE LANGUAGE, ie, it's already depending on there to be a pg_pltemplate entry. This would guarantee that we aren't changing any of the core properties of the pg_language entry (since, because of the way CREATE LANGUAGE already works, any pre-existing entry must match the pg_pltemplate entry). But there's still ownership and ACL to worry about. 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Fri, Feb 19, 2010 at 2:09 PM, Tom Lane wrote: > David Fetter writes: >> CREATE OR REPLACE LANGUAGE is an even bigger tar pit. >> http://archives.postgresql.org/pgsql-hackers/2009-10/msg00386.php > > The reason that patch got rejected was that it was implementing > CREATE IF NOT EXISTS --- under a false name. The problem with > that is summarized here: > http://archives.postgresql.org/pgsql-patches/2008-03/msg00416.php > > It wouldn't be that hard to implement actual CREATE OR REPLACE > if we decide that's the most useful solution here. The code > would need to be prepared to use heap_update instead of heap_insert, > and to get rid of old dependencies, but there is plenty of precedent > for that. > > The sticking point for me is still whether or not it's really a good > idea for pg_dump to be emitting CREATE OR REPLACE LANGUAGE. It does not > do that for any other object type. On the other hand, we've already > made languages a special case in pg_dump, since it emits the abbreviated > form of CREATE LANGUAGE in most cases rather than trying to duplicate > the existing object definition. Maybe there wouldn't be any bad results > in practice. We have all sorts of crufty hacks in pg_dump and the backend to cope with restoration of older dumps. Compared to some of those, this is going to be cleaner than newfallen snow. IMHO, anyway. ...Robert -- 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] Fast or immediate shutdown
Simon Riggs wrote: > On Wed, 2009-12-16 at 17:04 +0200, Peter Eisentraut wrote: > > On tis, 2009-12-15 at 17:19 +, Simon Riggs wrote: > > > running with log_checkpoints = on > > > > > > pg_ctl -D foo -m fast stop > > > > > > log says > > > > > > LOG: received fast shutdown request > > > LOG: aborting any active transactions > > > LOG: shutting down > > > LOG: restartpoint starting: shutdown immediate > > > > > > Some of us know that the "immediate" word refers to the restartpoint > > > request, though that word causes conceptual conflict with the shutdown > > > mode, which was fast, not immediate. > > > > > > Think we need to change the wording of this > > > > > > LOG: restartpoint starting: shutdown immediate > > > > > > so it is clearer what we mean > > > > We *do* we mean? And why are we logging it? > > The words after the colon refer to options sent to RequestCheckpoint and > it is logged because we asked for it by doing log_checkpoints = on. > > I suggest we say "smoothed" when checkpoint option is !immediate. So we > will remove the word "immediate" from the message. Did we decide not the change this? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Dimitri Fontaine writes: > Not sure how helpful I'll be there, but I can't help placing the > extension's proposal again. > If we had extensions here, plpgsql would be a core maintained extension, > made available by CREATE EXTENSION in the database (which initdb would > do in templates), then have the language installed by means of issuing > an INSTALL EXTENSION command. Well, that isn't really going to help us in terms of what to do for 9.0. But the possibility that something like this might happen in future is one thing that makes me hesitant about extending CREATE LANGUAGE right now --- the more bells and whistles we put on it, the harder it will be to have a clean upgrade to an EXTENSION facility. One thing that strikes me about your proposal is that INSTALL EXTENSION doesn't sound like a CREATE OR REPLACE operation. It sounds like a CREATE IF NOT EXISTS operation, because there simply is not a guarantee that what gets installed is exactly what the user expected --- in particular, for pg_dump, it isn't guaranteeing that the new version's extension is exactly like what was in the old database. And that's not a bad thing, in this context; it's more or less the Whole Point. However it still leaves us with the problem that CINE is underspecified. In particular, since we have already got the notion that languages have owners and ACLs, I'm unsure what the desired state is when pg_dump tries to set the owner and/or ACL for a pre-existing language. I know what is likely to happen if we just drop these concepts into the existing system: restoring a dump will take away ownership from whoever installed the language (extension) previously. That doesn't seem very good, especially if the ownership of any SQL objects contained in the extension doesn't change. 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Tom Lane writes: > Robert Haas writes: >> I am still of the opinion that changing this was a bad idea for >> exactly this reason. We could perhaps ameliorate this problem by >> implementing CREATE OR REPLACE for languages and emitting that >> instead; then the command in the dump would be a noop. > > Not really going to help for existing dumps (nor future dumps made > with pre-9.0 pg_dump versions). > > However, the case that is probably going to be the most pressing is > pg_upgrade, which last I heard insists on no errors during the restore > (and I think that's a good thing). That uses the new version's pg_dump > so a fix involving new syntax would cover it. Not sure how helpful I'll be there, but I can't help placing the extension's proposal again. If we had extensions here, plpgsql would be a core maintained extension, made available by CREATE EXTENSION in the database (which initdb would do in templates), then have the language installed by means of issuing an INSTALL EXTENSION command. Now, what would help would be to have that support and have CREATE LANGUAGE foo; be kept for compatibility only and issue INSTALL EXTENSION foo; instead. For those still with me, the choice to have plpgsql available by default would then boil down to have initdb do the CREATE EXTENSION in the template database, the database owner would still have to run the INSTALL EXTENSION. So now --load-language is INSTALL EXTENSION and just works as intended. And older dumps are doing CREATE LANGUAGE plpgsql; which is converted to INSTALL EXTENSION plpgsql;, which just works only because the extension is made available by default. So that if there's a CREATE LANGUAGE plpythonu, say, installing this extension will only succeed when INSTALL EXTENSION plpythonu; has been done either in the template1 database before creating the target database, or in the target database itself. So now we have "smart" success and failure modes falling from the proposed model. I'll dare not say "Hope This Helps" as I realize I failed to provide any code for implementing the extension management proposal. But got back to acceptable sleeping patterns and should be able to get back on the topic later this year, unless (please) beaten to it :) Regards, -- dim -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
David Fetter writes: > CREATE OR REPLACE LANGUAGE is an even bigger tar pit. > http://archives.postgresql.org/pgsql-hackers/2009-10/msg00386.php The reason that patch got rejected was that it was implementing CREATE IF NOT EXISTS --- under a false name. The problem with that is summarized here: http://archives.postgresql.org/pgsql-patches/2008-03/msg00416.php It wouldn't be that hard to implement actual CREATE OR REPLACE if we decide that's the most useful solution here. The code would need to be prepared to use heap_update instead of heap_insert, and to get rid of old dependencies, but there is plenty of precedent for that. The sticking point for me is still whether or not it's really a good idea for pg_dump to be emitting CREATE OR REPLACE LANGUAGE. It does not do that for any other object type. On the other hand, we've already made languages a special case in pg_dump, since it emits the abbreviated form of CREATE LANGUAGE in most cases rather than trying to duplicate the existing object definition. Maybe there wouldn't be any bad results in practice. 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Fri, Feb 19, 2010 at 1:44 PM, Tom Lane wrote: > Did we have consensus on exactly what CREATE OR REPLACE LANGUAGE would > do? Particularly in cases where the existing definition doesn't match > pg_pltemplate? I am of the opinion that any CREATE OR REPLACE command that completes without error should result in exactly the same final state that would have resulted had the object not existed when the command was issued. ...Robert -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Robert Haas writes: > On Thu, Feb 18, 2010 at 11:38 PM, Tom Lane wrote: >> ... Let me point out that >> choosing to install plpgsql by default has already broken "--single" >> restore of practically every pg_dump out there. Nobody batted an eye >> about that. Why are we suddenly so concerned about its effects on >> unnamed test suites? > I am still of the opinion that changing this was a bad idea for > exactly this reason. We could perhaps ameliorate this problem by > implementing CREATE OR REPLACE for languages and emitting that > instead; then the command in the dump would be a noop. Not really going to help for existing dumps (nor future dumps made with pre-9.0 pg_dump versions). However, the case that is probably going to be the most pressing is pg_upgrade, which last I heard insists on no errors during the restore (and I think that's a good thing). That uses the new version's pg_dump so a fix involving new syntax would cover it. Did we have consensus on exactly what CREATE OR REPLACE LANGUAGE would do? Particularly in cases where the existing definition doesn't match pg_pltemplate? 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] ALTER ROLE/DATABASE RESET ALL versus security
Tom Lane wrote: > Alvaro Herrera writes: > > Tom Lane wrote: > >> It looks to me like the code in AlterSetting() will allow an ordinary > >> user to blow away all settings for himself. Even those that are for > >> SUSET variables and were presumably set for him by a superuser. Isn't > >> this a security hole? I would expect that an unprivileged user should > >> not be able to change such settings, not even to the extent of > >> reverting to the installation-wide default. > > > Yes, it is, but this is not a new hole. This works just fine in 8.4 > > too: > > So I'd argue for changing it in 8.4 too. Understood. I'm starting to look at what this requires. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Fri, Feb 19, 2010 at 01:34:46PM -0500, Robert Haas wrote: > On Thu, Feb 18, 2010 at 11:38 PM, Tom Lane wrote: > > Takahiro Itagaki writes: > >> David Fetter wrote: > >>> support both pre-9.0 and post-9.0 PostgreSQLs. David Wheeler has > >>> suggested that we special-case PL/pgsql for 9.0 and greater, as it's > >>> in template0, where those tests are based. > > > >> +1 for the CREATE LANGUAGE IF NOT EXISTS behavior. > > > >> The regression test in the core is targeting only its version, > >> but some external projects have version-independent tests. > > > > I think it's more like "are under the fond illusion that their > > tests are version-independent". Are we going to back out the next > > incompatible change we choose to make as soon as somebody notices > > that it breaks a third-party test case? I don't think so. Let me > > point out that choosing to install plpgsql by default has already > > broken "--single" restore of practically every pg_dump out there. > > Nobody batted an eye about that. Why are we suddenly so > > concerned about its effects on unnamed test suites? > > I am still of the opinion that changing this was a bad idea for > exactly this reason. We could perhaps ameliorate this problem by > implementing CREATE OR REPLACE for languages and emitting that > instead; then the command in the dump would be a noop. CREATE OR REPLACE LANGUAGE is an even bigger tar pit. For example: http://archives.postgresql.org/pgsql-hackers/2009-10/msg00386.php Please find attached a patch which does this check in pg_regress. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c index 991bb17..45aad00 100644 --- a/src/test/regress/pg_regress.c +++ b/src/test/regress/pg_regress.c @@ -1795,8 +1795,12 @@ create_database(const char *dbname) */ for (sl = loadlanguage; sl != NULL; sl = sl->next) { - header(_("installing %s"), sl->str); - psql_command(dbname, "CREATE LANGUAGE \"%s\"", sl->str); + if ((pg_strncasecmp(sl->str, "plpgsql", sizeof("plpgsql")) != 0) && + (pg_strncasecmp(sl->str, "pl/pgsql", sizeof("pl/pgsql")) != 0)) + { + header(_("installing %s"), sl->str); + psql_command(dbname, "CREATE LANGUAGE \"%s\"", sl->str); + } } } -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Thu, Feb 18, 2010 at 11:38 PM, Tom Lane wrote: > Takahiro Itagaki writes: >> David Fetter wrote: >>> support both pre-9.0 and post-9.0 PostgreSQLs. David Wheeler has >>> suggested that we special-case PL/pgsql for 9.0 and greater, as it's >>> in template0, where those tests are based. > >> +1 for the CREATE LANGUAGE IF NOT EXISTS behavior. > >> The regression test in the core is targeting only its version, >> but some external projects have version-independent tests. > > I think it's more like "are under the fond illusion that their tests are > version-independent". Are we going to back out the next incompatible > change we choose to make as soon as somebody notices that it breaks a > third-party test case? I don't think so. Let me point out that > choosing to install plpgsql by default has already broken "--single" > restore of practically every pg_dump out there. Nobody batted an eye > about that. Why are we suddenly so concerned about its effects on > unnamed test suites? I am still of the opinion that changing this was a bad idea for exactly this reason. We could perhaps ameliorate this problem by implementing CREATE OR REPLACE for languages and emitting that instead; then the command in the dump would be a noop. ...Robert -- 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] ALTER ROLE/DATABASE RESET ALL versus security
Alvaro Herrera writes: > Tom Lane wrote: >> It looks to me like the code in AlterSetting() will allow an ordinary >> user to blow away all settings for himself. Even those that are for >> SUSET variables and were presumably set for him by a superuser. Isn't >> this a security hole? I would expect that an unprivileged user should >> not be able to change such settings, not even to the extent of >> reverting to the installation-wide default. > Yes, it is, but this is not a new hole. This works just fine in 8.4 > too: So I'd argue for changing it in 8.4 too. 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] ALTER ROLE/DATABASE RESET ALL versus security
Tom Lane wrote: > It looks to me like the code in AlterSetting() will allow an ordinary > user to blow away all settings for himself. Even those that are for > SUSET variables and were presumably set for him by a superuser. Isn't > this a security hole? I would expect that an unprivileged user should > not be able to change such settings, not even to the extent of > reverting to the installation-wide default. Yes, it is, but this is not a new hole. This works just fine in 8.4 too: alvherre=# create role foo; CREATE ROLE alvherre=# alter role foo set lc_messages = 'C'; ALTER ROLE alvherre=# set session AUTHORIZATION foo; SET alvherre=> show lc_messages ; lc_messages - es_CL.UTF-8 (1 fila) alvherre=> alter role foo reset all; ALTER ROLE alvherre=> reset session AUTHORIZATION ; RESET alvherre=# set session AUTHORIZATION foo; SET alvherre=> show lc_messages ; lc_messages - es_CL.UTF-8 (1 fila) alvherre=> alter role foo set lc_messages to 'C'; ERROR: se ha denegado el permiso para cambiar la opción «lc_messages» So any user is able to reset settings that were set for him by the superuser. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Avoiding bad prepared-statement plans.
"Pierre C" writes: > Yep, but it's a bit awkward and time-consuming, and not quite suited to > ORM-generated requests since you got to generate all the plan names, when > the SQL query itself would be the most convenient "unique > identifier"... The SHA1 proposal seems better to me. Now you still have to fill the table with statements and names, and I stand on the opinion that having a development-only layer in the ORM to do just that is better. That's supposing you don't generate so many different normalized queries that you can't generate them all in dev or from the unit tests… > A cool hack would be something like that : > > pg_execute( "SELECT ...", arguments... ) > > By inserting a hook which calls a user-specified function on non-existing > plan instead of raising an error, this could work. This I'm not helping with. It's definitely not the same skills and time requirements as offering a simple function based alternative to the table lookup in preprepare, in the "load them all" phase. > However, this wouldn't work as-is since the plan name must be <= > NAMEDATALEN, but you get the idea ;) SHA1 or even MD5 would do, the later having the advantage of being already supported by PostgreSQL core. Regards, -- dim -- 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] Merge join and index scan strangeness
Poking a bit deeper, it *does* think the plan with sorts is cheaper than without. The mergejoin plan it really prefers is: regression=# set enable_hashjoin TO 0; SET regression=# set enable_nestloop TO 0; SET regression=# explain ... QUERY PLAN --- Update (cost=41.69..379234.38 rows=14225817 width=88) -> Merge Join (cost=41.69..379234.38 rows=14225817 width=88) Merge Cond: ((t1.f1 = t2.f1) AND (t1.f2 = t2.f2) AND (t1.f3 = t2.f3) AND (t1.f4 = t2.f4)) -> Index Scan using i1 on t1 (cost=0.00..21198.88 rows=234839 width=65) -> Sort (cost=41.69..43.19 rows=600 width=65) Sort Key: t2.f1, t2.f2, t2.f3, t2.f4 -> Seq Scan on t2 (cost=0.00..14.00 rows=600 width=65) (7 rows) but if you force it to use indexscans: regression=# set enable_seqscan TO 0; SET regression=# explain ... QUERY PLAN --- Update (cost=52001.84..410457.60 rows=14225817 width=88) -> Merge Join (cost=52001.84..410457.60 rows=14225817 width=88) Merge Cond: ((t1.f3 = t2.f3) AND (t1.f1 = t2.f1) AND (t1.f2 = t2.f2) AND (t1.f4 = t2.f4)) -> Sort (cost=51783.56..52370.66 rows=234839 width=65) Sort Key: t1.f3, t1.f1, t1.f2, t1.f4 -> Index Scan using i1 on t1 (cost=0.00..21198.88 rows=234839 width=65) -> Sort (cost=93.12..94.62 rows=600 width=65) Sort Key: t2.f3, t2.f1, t2.f2, t2.f4 -> Index Scan using i2 on t2 (cost=0.00..65.44 rows=600 width=65) (9 rows) and then without sorts: regression=# set enable_sort TO 0; SET regression=# explain ... QUERY PLAN --- Update (cost=0.00..483609.37 rows=14225817 width=88) -> Merge Join (cost=0.00..483609.37 rows=14225817 width=88) Merge Cond: ((t2.f1 = t1.f1) AND (t2.f2 = t1.f2) AND (t2.f3 = t1.f3) AND (t2.f4 = t1.f4)) -> Index Scan using i2 on t2 (cost=0.00..65.44 rows=600 width=65) -> Materialize (cost=0.00..23547.27 rows=234839 width=65) -> Index Scan using i1 on t1 (cost=0.00..21198.88 rows=234839 width=65) (6 rows) Note that the join cost is way higher than the sum of the input costs in all three cases. The reason for that is that it's expecting a whole lot of rescanning of the inner relation due to duplicate merge keys. This means that a "bare" inner indexscan is going to be penalized very heavily for refetches, whereas plans with either sort or materialize in between look better because the refetch cost is very low. So that's how a plan with a sort can be preferred to one without. I think the weird looking choices of sort order may just be randomness because all sort orders cost the same once it decides to sort. However, even given that, it's odd that it prefers a plan with two sorts to a plan with one materialize. Poking around in costsize.c, I think that the reason for this is that the rescan cost of a sort is estimated at cpu_operator_cost per tuple, whereas rescanning a materialize node is being estimated at cpu_tuple_cost per tuple. For a plan where rescan cost is the dominant factor, that matters. We probably ought to make those two estimates the same. Since neither plan node type does any projection or qual checking, the lower number is probably the better choice. BTW, the real bottom line here is that mergejoin is a crummy plan choice when there are so few distinct join key values. The planner would never have picked any of these plans if you hadn't forced it to. So I'm not sure how important this is in the real world. 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Feb 19, 2010, at 7:43 AM, David E. Wheeler wrote: > Usually PGXS loads after setting all the environment variables, though I > suspect that it wouldn't have any side effects to set regress_opts afterward. > Also, there is no MAJORVERSION in earlier versions, so module authors would > have to work around that. > > Basically though, you're asking all third party module authors who depend on > plpgsql in their code and/or tests to modify their makefiles and release new > versions to work around something that pg_regress could have fixed internally > in 1-2 lines of code and be done with it. I'm sure this is bad C and should do a case-insensitive comparison, but this is essentially what I mean: *** a/src/test/regress/pg_regress.c --- b/src/test/regress/pg_regress.c *** create_database(const char *dbname) *** 1795,1802 */ for (sl = loadlanguage; sl != NULL; sl = sl->next) { ! header(_("installing %s"), sl->str); ! psql_command(dbname, "CREATE LANGUAGE \"%s\"", sl->str); } } --- 1795,1804 */ for (sl = loadlanguage; sl != NULL; sl = sl->next) { ! if (sl->str != "plpgsql") { ! header(_("installing %s"), sl->str); ! psql_command(dbname, "CREATE LANGUAGE \"%s\"", sl->str); ! } } } Does that seem unreasonable? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] alpha4 bundled -- please verify
Alpha4 has been bundled and is available at http://developer.postgresql.org/~petere/alpha/ Please check that it is sane. Since I'll be away for the next few days, someone has to take it from here: write announcement, move tarballs, send announcement. -- 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] Merge join and index scan strangeness
On Fri, 19 Feb 2010, Tom Lane wrote: Teodor Sigaev writes: I found something strange with merge join. Let there are two table Sorry, postgresql's version is 8.4 from today CVS Can't reproduce it here, either in HEAD or 8.4. Sure you have a clean build with no local modifications? The outright-incorrect last plan you show seems to indicate something rather badly wrong with pathkey matching. I reproduced on my machine PostgreSQL 8.4.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Ubuntu 4.4.1-4ubuntu9) 4.4.1, 64-bit Notice, plan is different and index scan on t1 uses wrong index. Merge Join (cost=45224.01..251225.22 rows=9760080 width=86) (actual time=1687.545..1687.545 rows=0 loops=1) Merge Cond: ((t2.f1 = t1.f1) AND (t2.f2 = t1.f2) AND (t2.f3 = t1.f3) AND (t2.f4 = t1.f4)) -> Index Scan using i2 on t2 (cost=0.00..65.44 rows=600 width=59) (actual time=0.008..0.179 rows=600 loops=1) -> Sort (cost=45224.01..45811.10 rows=234839 width=69) (actual time=1612.586..1645.436 rows=161842 loops=1) Sort Key: t1.f1, t1.f2, t1.f3, t1.f4 Sort Method: external sort Disk: 20888kB -> Index Scan using i11 on t1 (cost=0.00..24274.83 rows=234839 width=69) (actual time=0.637..137.659 rows=234839 loops=1) Total runtime: 1969.029 ms (8 rows) Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Feb 19, 2010, at 5:36 AM, Alvaro Herrera wrote: >> Because it's a lot easier for `pg_regress --load-language=plpgsql` to mean >> "ensure the language is installed" than it is for 3rd-party test suites to >> detect what version they're being installed against. > > Why doesn't the Makefile running the tests simply avoid adding > --load-language when the version is higher than 9.0? Shouldn't be a > hard test to write. We have $(MAJORVERSION) to help with this. Usually PGXS loads after setting all the environment variables, though I suspect that it wouldn't have any side effects to set regress_opts afterward. Also, there is no MAJORVERSION in earlier versions, so module authors would have to work around that. Basically though, you're asking all third party module authors who depend on plpgsql in their code and/or tests to modify their makefiles and release new versions to work around something that pg_regress could have fixed internally in 1-2 lines of code and be done with it. Best, David -- 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] testing cvs HEAD - HS/SR - PANIC: cannot make new WAL entries during recovery
On Thu, February 18, 2010 23:32, Andres Freund wrote: > On Thursday 18 February 2010 22:25:35 Erik Rijkers wrote: >> localhost:55432 => 8.4.2 instance (ssh tunnel) >> /tmp:7575 => a 9.0devel standby >> >> time pg_dump -h localhost -p 55432 -t public.tab_jobs --clean --no-owner >> --no-privileges ms | psql -q -h /tmp -p 7575 -d replicas >> ERROR: transaction is read-only [...] >> PANIC: cannot make new WAL entries during recovery >> server closed the connection unexpectedly >> This probably means the server terminated abnormally >> before or while processing the request. >> connection to server was lost >> [...] >> STATEMENT: ALTER SEQUENCE tab_jobs_id_seq OWNED BY tab_jobs.id; >> PANIC: cannot make new WAL entries during recovery >> STATEMENT: SELECT pg_catalog.setval('tab_jobs_id_seq', 31907, true); > Hm, yea. setval() simply misses a check there (it was added for nextval > though). > I wonder if there are other functions bypassing the layers like > setval/nextval? > > Trivial patch attached. That worked - and I see has already been committed, thanks. There is another small issue related to this dumping into a readonly slave: With multiline columns (at least, I think that's the reason), there are on STDERR endless occurrences of: invalid command \N and invalid command \ There can be millions of these lines (obviously). On top of that, the logfile gets appended with 'syntax error', follwed by the whole dump: ERROR: syntax error at or near "18726" at character 1 STATEMENT: 18726 16883 [...] [whole dump follows here] This does maybe not really qualify as a bug, but it does seem unnecessary to spam a readonly standby with all this. (come to think of it, 'syntax error' is a bit off in any case) Thanks, Erik Rijkers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Fwd: Re: [HACKERS] Merge join and index scan strangeness
Can't reproduce it here, either in HEAD or 8.4. Sure you have a clean build with no local modifications? The outright-incorrect last plan Sure, just checkout'ed sources. And it's reproduced on current HEAD right now. More info: postgresql.conf changes: shared_buffers=128MB temp_buffers=16MB work_mem=16MB maintenance_work_mem=256MB effective_cache_size=1024MB autovacuum=on log_destination=syslog % pg_config --configure '--enable-depend' '--enable-cassert' '--enable-debug' '--with-perl' 'CFLAGS=-O0' % gcc -v Using built-in specs. Target: i386-undermydesk-freebsd Configured with: FreeBSD/i386 system compiler Thread model: posix gcc version 4.2.1 20070719 [FreeBSD] % uname -a FreeBSD XXX.XXX 8.0-RELEASE-p1 FreeBSD 8.0-RELEASE-p1 #1: Fri Dec 11 19:03:49 MSK 2009 teo...@xxx.xxx:/usr/obj/usr/src/sys/XOR i386 With gcc version 4.4.3 bug is reproduces too. Could you point me a starting point of investigation? -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- 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] Merge join and index scan strangeness
Teodor Sigaev writes: >> I found something strange with merge join. Let there are two table > Sorry, postgresql's version is 8.4 from today CVS Can't reproduce it here, either in HEAD or 8.4. Sure you have a clean build with no local modifications? The outright-incorrect last plan you show seems to indicate something rather badly wrong with pathkey matching. 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] Avoiding bad prepared-statement plans.
On Thu, Feb 18, 2010 at 08:31:05PM -0600, David Christensen wrote: > > On Feb 18, 2010, at 2:19 PM, Pierre C wrote: > >> >>> What about catching the error in the application and INSERT'ing into the >>> current preprepare.relation table? The aim would be to do that in dev or >>> in pre-prod environments, then copy the table content in production. >> >> Yep, but it's a bit awkward and time-consuming, and not quite suited to >> ORM-generated requests since you got to generate all the plan names, when >> the SQL query itself would be the most convenient "unique identifier"... >> >> A cool hack would be something like that : >> >> pg_execute( "SELECT ...", arguments... ) >> >> By inserting a hook which calls a user-specified function on non-existing >> plan instead of raising an error, this could work. >> However, this wouldn't work as-is since the plan name must be <= >> NAMEDATALEN, but you get the idea ;) > > How about the SHA1 hash of the query? Hey, it works for git... :-) > > Regards, > > David > -- > David Christensen > End Point Corporation > da...@endpoint.com > > Hi David, Not to beat out own drum, but we already include a hashing function that can be used for this purpose and is much faster than SHA-1. We would want to use all 64-bits for this use instead of just the 32-bits we currently use for the internal DB hashing. Here is an article comparing the Jenkins' Hash (the one we use) and SHA-1: http://home.comcast.net/~bretm/hash/ Regards, Ken -- 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] Merge join and index scan strangeness
Teodor Sigaev wrote: I found something strange with merge join. Let there are two table Sorry, postgresql's version is 8.4 from today CVS For what it's worth - 8.4.0 gives as expected. aap=# explain UPDATE t1 SET f1 = t1.f1 || t2.f1 FROM t2 WHERE t2.f1 = t1.f1 AND t2.f2 = t1.f2 AND t2.f3 = t1.f3 AND t2.f4 = t1.f4 ; QUERY PLAN - Merge Join (cost=0.00..28522.60 rows=1 width=142) Merge Cond: ((t1.f1 = t2.f1) AND (t1.f2 = t2.f2) AND (t1.f3 = t2.f3) AND (t1.f4 = t2.f4)) -> Index Scan using i1 on t1 (cost=0.00..26090.94 rows=234839 width=110) -> Index Scan using i2 on t2 (cost=0.00..77.25 rows=600 width=104) (4 rows) -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
David E. Wheeler wrote: > On Feb 18, 2010, at 8:38 PM, Tom Lane wrote: > > >> The regression test in the core is targeting only its version, > >> but some external projects have version-independent tests. > > > > I think it's more like "are under the fond illusion that their tests are > > version-independent". Are we going to back out the next incompatible > > change we choose to make as soon as somebody notices that it breaks a > > third-party test case? I don't think so. Let me point out that > > choosing to install plpgsql by default has already broken "--single" > > restore of practically every pg_dump out there. Nobody batted an eye > > about that. Why are we suddenly so concerned about its effects on > > unnamed test suites? > > Because it's a lot easier for `pg_regress --load-language=plpgsql` to mean > "ensure the language is installed" than it is for 3rd-party test suites to > detect what version they're being installed against. Why doesn't the Makefile running the tests simply avoid adding --load-language when the version is higher than 9.0? Shouldn't be a hard test to write. We have $(MAJORVERSION) to help with this. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Merge join and index scan strangeness
I found something strange with merge join. Let there are two table Sorry, postgresql's version is 8.4 from today CVS -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Merge join and index scan strangeness
Hi! I found something strange with merge join. Let there are two table (http://www.sigaev.ru/misc/ex.sql.gz, 360Kb) t1 and t2, both without indexes. Query is: UPDATE t1 SET f1 = t1.f1 || t2.f1 FROM t2 WHERE t2.f1 = t1.f1 AND t2.f2 = t1.f2 AND t2.f3 = t1.f3 AND t2.f4 = t1.f4 ; I forbid everything except merge join and index scan, so explain gives: set enable_hashjoin=off; set enable_nestloop=off; set enable_seqscan=off; set enable_bitmapscan=off; Merge Join (cost=2035240.26..2388197.90 rows=14024070 width=82) Merge Cond: ((t2.f1 = t1.f1) AND (t2.f2 = t1.f2) AND (t2.f3 = t1.f3) AND (t2.f4 = t1.f4)) -> Sort (cost=140.69..142.19 rows=600 width=59) Sort Key: t2.f1, t2.f2, t2.f3, t2.f4 -> Seq Scan on t2 (cost=100.00..113.00 rows=600 width=59) -> Materialize (cost=1035199.57..1038135.06 rows=234839 width=65) -> Sort (cost=1035199.57..1035786.67 rows=234839 width=65) Sort Key: t1.f1, t1.f2, t1.f3, t1.f4 -> Seq Scan on t1 (cost=100.00..1005017.39 rows=234839 width=65) All looks good at this point. Create index on suggested by merge join columns: CREATE INDEX i1 ON t1 (f1, f2, f3, f4); CREATE INDEX i2 ON t2 (f1, f2, f3, f4); And explain: Merge Join (cost=49897.68..402855.32 rows=14024070 width=82) Merge Cond: ((t2.f4 = t1.f4) AND (t2.f1 = t1.f1) AND (t2.f2 = t1.f2) AND (t2.f3 = t1.f3)) -> Sort (cost=90.81..92.31 rows=600 width=59) Sort Key: t2.f4, t2.f1, t2.f2, t2.f3 -> Index Scan using i2 on t2 (cost=0.00..63.13 rows=600 width=59) -> Materialize (cost=49806.86..52742.35 rows=234839 width=65) -> Sort (cost=49806.86..50393.96 rows=234839 width=65) Sort Key: t1.f4, t1.f1, t1.f2, t1.f3 -> Index Scan using i1 on t1 (cost=0.00..19624.68 rows=234839 width=65) Merge join chooses another order of fields! It seems to me that index scan with sort should be slower than pure index scan. Ok, add another indexes with suggested column's order: CREATE INDEX i11 ON t1 (f4, f1, f2, f3); CREATE INDEX i21 ON t2 (f4, f1, f2, f3); Explain: Merge Join (cost=90.81..372665.64 rows=14024070 width=82) Merge Cond: ((t1.f1 = t2.f1) AND (t1.f2 = t2.f2) AND (t1.f3 = t2.f3) AND (t1.f4 = t2.f4)) -> Index Scan using i1 on t1 (cost=0.00..19624.68 rows=234839 width=65) -> Sort (cost=90.81..92.31 rows=600 width=59) Sort Key: t2.f1, t2.f2, t2.f3, t2.f4 -> Index Scan using i21 on t2 (cost=0.00..63.13 rows=600 width=59) Megre join uses index scan but for table t2 it uses wrong index! And again index scan + sort instead of index scan. Am I miss something or misunderstand? -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- 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] testing cvs HEAD - HS/SR - PANIC: cannot make new WAL entries during recovery
On Thu, 2010-02-18 at 23:13 +0100, Erik Rijkers wrote: > I ran this by accident Good test, thanks for running it. -- Simon Riggs www.2ndQuadrant.com -- 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] Re: [COMMITTERS] pgsql: Introduce WAL records to log reuse of btree pages, allowing
On Thu, 2010-02-18 at 14:17 -0500, Tom Lane wrote: > Simon Riggs writes: > > On Thu, 2010-02-18 at 14:23 +0200, Heikki Linnakangas wrote: > >> A straightforward way to fix that is to WAL-log the real xid in the > >> XLOG_BTREE_DELETE_PAGE records, instead of resetting it to > >> FrozenTransactionId. > > > An even simpler way would be to reset the value to latestCompletedXid > > during btree_xlog_delete_page(). That touches less code. I doubt it will > > make much difference to conflict recovery, since if pages are being > > deleted then btree delete records are likely to be frequent and will > > have already killed long running queries. > > I'm a bit concerned about XID wraparound if the value doesn't get reset > to FrozenTransactionId. There's no guarantee the page will get reused > promptly ... I'd be very interested for you to have a look at Hot Standby from a transaction wraparound perspective. There was some code in there to handle anti-wraparound in RecordKnownAssignedTransactionId() but it was removed, though I'm a little hazy on that myself. You've got the best nose for corner cases and risks. In this case, I don't see any problem. The xid after recovery will be a same or higher value than if the crash had never taken place, so I can't see any risk that isn't already addressed. Since we now have to handle cases where blocks have been touched in pre-9.0 code and are in a state they could never get into in 9.0, we do still have to handle a value of btpo.xact == FrozenTransactionId. I will add a special case to the handling of XLOG_BTREE_REUSE_PAGE records also to allow for that. Any similar theoretical issues would be most welcome if reported. -- Simon Riggs www.2ndQuadrant.com -- 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] Streaming replication on win32, still broken
Heikki Linnakangas wrote: > Magnus Hagander wrote: >> Well, it's going to make the process that reads the WAL cause actual >> physical I/O... That'll take a chunk out of your total available I/O, >> which is likely to push you to the limit of your I/O capacity much >> quicker. > > Right, doesn't seem sensible, though it would be nice to see a benchmark > on that. > > Here's a patch to disable O_DIRECT when archiving or streaming is > enabled. This is pretty hard to test, so any extra eyeballs would be nice.. Committed. Can you check that this fixed the PANIC you saw? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Streaming Replication docs
Joshua D. Drake wrote: > On Fri, 2010-02-12 at 10:22 -0800, Josh Berkus wrote: >> In addition to the changes you've proposed, one thing our docs could >> really use is a single reference page which we could go to for all of >> the .conf files. Right now, you need to rely on postgresql.org doc >> search in order to find, for example, pg_hba.conf. >> >> I think it would be good to put into server administration somewhere a >> single page called "Configuration Files" which references: >> postgresql.conf >> pg_hba.conf >> recovery.conf >> pg_ident.conf >> ... hmmm, am I missing one? > > > Seems that should go... under "Reference" Seems like a good idea. Unfortunately my SGML-skills are too weak to do that, so here's a patch to for my original proposal. There's little text changes, mostly just moves sections around. I'm thinking of committing this now; someone else will have to do the above reorganization if we want it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index 2f28da2..02d3765 100644 --- a/doc/src/sgml/backup.sgml +++ b/doc/src/sgml/backup.sgml @@ -955,7 +955,7 @@ SELECT pg_stop_backup(); Create a recovery command file recovery.conf in the cluster - data directory (see ). You might + data directory (see ). You might also want to temporarily modify pg_hba.conf to prevent ordinary users from connecting until you are sure the recovery was successful. @@ -1076,162 +1076,6 @@ restore_command = 'cp /mnt/server/archivedir/%f %p' WAL data need not be scanned again. - - - Recovery Settings - - - These settings can only be made in the recovery.conf - file, and apply only for the duration of the recovery. (A sample file, - share/recovery.conf.sample, exists in the installation's - share/ directory.) They must be - reset for any subsequent recovery you wish to perform. They cannot be - changed once recovery has begun. - The parameters for streaming replication are described in . - - - - - - restore_command (string) - - -The shell command to execute to retrieve an archived segment of -the WAL file series. This parameter is required for archive recovery, -but optional for streaming replication. -Any %f in the string is -replaced by the name of the file to retrieve from the archive, -and any %p is replaced by the copy destination path name -on the server. -(The path name is relative to the current working directory, -i.e., the cluster's data directory.) -Any %r is replaced by the name of the file containing the -last valid restart point. That is the earliest file that must be kept -to allow a restore to be restartable, so this information can be used -to truncate the archive to just the minimum required to support -restarting from the current restore. %r is typically only -used by warm-standby configurations -(see ). -Write %% to embed an actual % character. - - - -It is important for the command to return a zero exit status -only if it succeeds. The command will be asked for file -names that are not present in the archive; it must return nonzero -when so asked. Examples: - -restore_command = 'cp /mnt/server/archivedir/%f "%p"' -restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows - - - - - - - recovery_end_command (string) - - -This parameter specifies a shell command that will be executed once only -at the end of recovery. This parameter is optional. The purpose of the -recovery_end_command is to provide a mechanism for cleanup -following replication or recovery. -Any %r is replaced by the name of the file -containing the last valid restart point. That is the earliest file that -must be kept to allow a restore to be restartable, so this information -can be used to truncate the archive to just the minimum required to -support restart from the current restore. %r would -typically be used in a warm-standby configuration -(see ). -Write %% to embed an actual % character -in the command. - - -If the command returns a non-zero exit status then a WARNING log -message will be written and the database will proceed to start up -anyway. An exception is that if the command was terminated by a -signal, the database will not proceed with startup. - - - - - - recovery_target_time - (timestamp) - - - -This parameter specifies the time stamp up to which recovery -will proceed. -At most one of recovery_target_tim
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
2010/2/19 Tom Lane : > Takahiro Itagaki writes: >> David Fetter wrote: >>> support both pre-9.0 and post-9.0 PostgreSQLs. David Wheeler has >>> suggested that we special-case PL/pgsql for 9.0 and greater, as it's >>> in template0, where those tests are based. > >> +1 for the CREATE LANGUAGE IF NOT EXISTS behavior. > >> The regression test in the core is targeting only its version, >> but some external projects have version-independent tests. > > I think it's more like "are under the fond illusion that their tests are > version-independent". Are we going to back out the next incompatible > change we choose to make as soon as somebody notices that it breaks a > third-party test case? I don't think so. Let me point out that > choosing to install plpgsql by default has already broken "--single" > restore of practically every pg_dump out there. Nobody batted an eye > about that. Why are we suddenly so concerned about its effects on > unnamed test suites? Oh yeah, that one is very annoying, can we go fix that one somehow? I think the use of --single has decreased a lot in favor of parallel restore, but it's certainly not all that uncommon... I think the main reason we haven't heard loads of complains is that it isn't the default.. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers