Re: [HACKERS] cheaper snapshots redux
Robert, On 08/25/2011 04:59 AM, Robert Haas wrote: > True; although there are some other complications. With a > sufficiently sophisticated allocator you can avoid mutex contention > when allocating chunks, but then you have to store a pointer to the > chunk somewhere or other, and that then requires some kind of > synchronization. Hm.. right. > One difference with snapshots is that only the latest snapshot is of > any interest. Theoretically, yes. But as far as I understood, you proposed the backends copy that snapshot to local memory. And copying takes some amount of time, possibly being interrupted by other backends which add newer snapshots... Or do you envision the copying to restart whenever a new snapshot arrives? Regards Markus -- 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] cheaper snapshots redux
On Wed, Aug 24, 2011 at 4:30 AM, Markus Wanner wrote: > I'm in respectful disagreement regarding the ring-buffer approach and > think that dynamic allocation can actually be more efficient if done > properly, because there doesn't need to be head and tail pointers, which > might turn into a point of contention. True; although there are some other complications. With a sufficiently sophisticated allocator you can avoid mutex contention when allocating chunks, but then you have to store a pointer to the chunk somewhere or other, and that then requires some kind of synchronization. > As a side note: that I've been there with imessages. Those were first > organized as a ring-bufffer. The major problem with that approach was > the imessages were consumed with varying delay. In case an imessage was > left there for a longer amount of time, it blocked creation of new > imessages, because the ring-buffer cycled around once and its head > arrived back at the unconsumed imessage. > > IIUC (which might not be the case) the same issue applies for snapshots. One difference with snapshots is that only the latest snapshot is of any interest. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] skip WAL on COPY patch
On Tue, Aug 23, 2011 at 4:51 PM, Alvaro Herrera wrote: > Excerpts from Robert Haas's message of mar ago 23 17:43:13 -0300 2011: >> On Tue, Aug 23, 2011 at 4:17 PM, Tom Lane wrote: >> > Robert Haas writes: >> >> What I think would be really interesting is a way to make this work >> >> when the table *isn't* empty. In other words, have a COPY option that >> >> (1) takes an exclusive lock on the table, (2) writes the data being >> >> inserted into new pages beyond the old EOF, and (3) arranges for crash >> >> recovery or transaction abort to truncate the table back to its >> >> previous length. Then you could do fast bulk loads even into a table >> >> that's already populated, so long as you don't mind that the table >> >> will be excusive-locked and freespace within existing heap pages won't >> >> be reused. >> > >> > What are you going to do with the table's indexes? >> >> Oh, hmm. That's awkward. > > If you see what I proposed, it's simple: you can scan the new segment(s) > and index the tuples found there (maybe in bulk which would be even > faster). You can do that much even if you just append to the file - you don't need variable-length segments to make that part work. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] pg_restore --no-post-data and --post-data-only
On 08/24/2011 08:43 PM, Josh Berkus wrote: On 8/23/11 1:30 PM, Andrew Dunstan wrote: Attached is an undocumented patch that allows pg_restore to omit post-data items or omit all but post-data items. This has been discussed before, and Simon sent in a patch back on 2008, which has bitrotted some. I'm not sure why it was dropped at the time, but I think it's time to do this. This patch relies on some infrastructure that was added since Simon's patch, so it works a bit differently (and more simply). If it's not clear from Andrew's description, the purpose of this patch is to allow dividing your pgdump into 3 portions: 1. schema 2. data 3. constraints/indexes This allows users to implement a number of custom solutions for ad-hoc parallel dump, conditional loading, data munging and sampled databases. While doing so was possible before using the manifest from pg_restore -l, the manifest approach has been complex to automate and relies on obscure knowledge. I have immediate production use for this patch and may be backporting it. It's already backported, at least as far as 8.4. Check your email :-) 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] SSI 2PC coverage
On Wed, Aug 24, 2011 at 9:11 PM, Tom Lane wrote: > Alvaro Herrera writes: >> After having to play with this, I didn't like it very much, because >> regression.diffs gets spammed with the (rather massive and completely >> useless) diff in that test. For the xml tests, rather than ignoring it >> fail on an installation without libxml, we use an alternative output. > >> Unless there are objections, I will commit the alternative file proposed >> by Dan. > > +1 ... "ignore" is a pretty ugly hack here. > > Eventually we need some way of detecting that specific tests should be > skipped because they're irrelevant to the current system configuration. > contrib/sepgsql is already doing something of the sort, but it's rather > crude ... I'm fairly unhappy with the fact that we don't have a better way of deciding whether we should even *build* sepgsql. The --with-selinux flag basically doesn't do anything except enable the compile of that contrib module, and that's kind of a lame use of a configure flag. Not that I have a better idea... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [pgsql-advocacy] [HACKERS] blog post on ancient history
FYI, I was just checking out the contributors page and noticed that he's listed under Past Contributors. http://www.postgresql.org/community/contributors/ On Fri, Jul 8, 2011 at 1:55 PM, Bruce Momjian wrote: > Tom Lane wrote: > > Robert Haas writes: > > >> Anyone feels in mood for a comment? > > > > > I see our mailing list archives for pgsql-hackers only go back to > > > 1997, so it's hard to track down what was going on in 1996. But as > > > for why no one remembers the guy, it's probably because we've had > > > nearly 100% churn in the set of people who are involved. Tom Lane > > > isn't mentioned in the commit log until 1998. We could see if Bruce > > > or Marc remember him, but just to put this in perspective, the guy > > > made 6 commits out of almost 900 that year. > > > > According to the logs there was a seventh patch committed for him by > > Marc, but still: seven patches, touching only libpq and psql (not by any > > means as "internal" as this blogger thinks), committed over a period of > > about a month. That's not exactly a large or sustained contribution. > > Is it surprising that everyone had forgotten it a few years later? > > > > > I don't think we had the > > > same standards for granting commit access back then that we do now. > > > > Yeah, the only thing that's even mildly surprising is that he seems to > > have been given commit privileges after only one patch. However, > > there's an indication in one of the commit messages that he'd previously > > contributed to the code while Berkeley had it: > > > > 1996-07-25 02:46 julian > > > > * src/bin/psql/psql.c: Large re-write/enhancement. In pg-101 Jolly > > only included a smaller part of my (proff) patch. This is the rest > > of it, with a few, mainly aesthetic changes. I've removed a lot of > > redundency from the original code, added support for the new > > PQprint() routines in libpq, expanded tables, and a few generally > > nifty ways of massaging data in and out of the backend. Still needs > > some good stress testing. > > > > so maybe that history had something to do with it. > > The spring/summer of 1996 was a time when we were trying to gather all > the scattered work of people who had created patches to Postgres95 but > had not been integrated by Jolly. Seems Julian had been in that group > so his patches were quickly applied. If he had asked for commit, I > would have given it to him because he had a history of contributing to > the project (which I could not confirm). > > FYI, I do think I have an archive of much of the pg95-...@ki.net on a > DAT tape in my basement, and I have an unpowered computer down there > with a DAT tape drive ... hmmm. > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + > > -- > Sent via pgsql-advocacy mailing list (pgsql-advoc...@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-advocacy >
Re: [HACKERS] SSI 2PC coverage
Alvaro Herrera writes: > After having to play with this, I didn't like it very much, because > regression.diffs gets spammed with the (rather massive and completely > useless) diff in that test. For the xml tests, rather than ignoring it > fail on an installation without libxml, we use an alternative output. > Unless there are objections, I will commit the alternative file proposed > by Dan. +1 ... "ignore" is a pretty ugly hack here. Eventually we need some way of detecting that specific tests should be skipped because they're irrelevant to the current system configuration. contrib/sepgsql is already doing something of the sort, but it's rather crude ... 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] pg_restore --no-post-data and --post-data-only
On 8/23/11 1:30 PM, Andrew Dunstan wrote: > > Attached is an undocumented patch that allows pg_restore to omit > post-data items or omit all but post-data items. This has been discussed > before, and Simon sent in a patch back on 2008, which has bitrotted > some. I'm not sure why it was dropped at the time, but I think it's time > to do this. This patch relies on some infrastructure that was added > since Simon's patch, so it works a bit differently (and more simply). If it's not clear from Andrew's description, the purpose of this patch is to allow dividing your pgdump into 3 portions: 1. schema 2. data 3. constraints/indexes This allows users to implement a number of custom solutions for ad-hoc parallel dump, conditional loading, data munging and sampled databases. While doing so was possible before using the manifest from pg_restore -l, the manifest approach has been complex to automate and relies on obscure knowledge. I have immediate production use for this patch and may be backporting it. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] pg_dump --exclude-table-data
On Wed, Aug 24, 2011 at 2:01 PM, Josh Berkus wrote: > > FWIW, I have immediate use for this in creating cut-down versions of > databases for testing purposes. It'll eliminate a couple pages of shell > scripts for me. Speaking of "cut-down versions", I have recently been using pg_sample, and been happy with the resulting subset database. I created a db <10 GB in size from a source db ~600Gb in a few minutes. https://github.com/mla/pg_sample -- 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] SSI 2PC coverage
Excerpts from Heikki Linnakangas's message of jue ago 18 09:57:34 -0400 2011: > Committed. I removed the second expected output file, and marked the > prepared-transactions tests in the schedule as "ignore" instead. That > way if max_prepared_transactions=0, you get a notice that the test case > failed, but pg_regress still returns 0 as exit status. After having to play with this, I didn't like it very much, because regression.diffs gets spammed with the (rather massive and completely useless) diff in that test. For the xml tests, rather than ignoring it fail on an installation without libxml, we use an alternative output. Unless there are objections, I will commit the alternative file proposed by Dan. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. 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] SSI 2PC coverage
Excerpts from Kevin Grittner's message of mar ago 23 15:07:33 -0300 2011: > Heikki Linnakangas wrote: > > I did change the lexer slightly, to trim whitespace from the > > beginning and end of SQL blocks. This cuts the size of expected > > output a bit, and makes it look nicer anyway. > > OK. You missed the alternative outputs for a couple files. These > are needed to get successful tests with > default_transaction_isolation = 'serializable' or 'repeatable read'. > Patch attached. Thanks, applied. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. 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] pg_dump --exclude-table-data
> For those who are (like my clients :-) ) anxious to get their hands on > this immediately, a backport patch is also attached which applies to 9.0 > sources, and applies with offsets to 8.4 sources. FWIW, I have immediate use for this in creating cut-down versions of databases for testing purposes. It'll eliminate a couple pages of shell scripts for me. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] is there somebody with access to db2?
Hello, I am interesting about a CONTINUE HANDLER behave, please can you check, and send a result of following function? CREATE FUNCTION REVERSE(INSTR VARCHAR(4000)) RETURNS INT DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL BEGIN DECLARE RES INT; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET RES = 10; BEGIN SIGNAL '3'; SET RES = 30; END; RETURN RES; END; Thank you 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] Should I implement DROP INDEX CONCURRENTLY?
On Wed, Aug 24, 2011 at 12:38 PM, Tom Lane wrote: > Merlin Moncure writes: >> On Wed, Aug 24, 2011 at 1:24 PM, Daniel Farina wrote: >>> At Heroku we use CREATE INDEX CONCURRENTLY with great success, but >>> recently when frobbing around some indexes I realized that there is no >>> equivalent for DROP INDEX, and this is a similar but lesser problem >>> (as CREATE INDEX takes much longer), as DROP INDEX takes an ACCESS >>> EXCLUSIVE lock on the parent table while doing the work to unlink >>> files, which nominally one would think to be trivial, but I assure you >>> it is not at times for even indexes that are a handful of gigabytes >>> (let's say ~=< a dozen). > >> Are you sure that you are really waiting on the time to unlink the >> file? there's other stuff going on in there like waiting for lock, >> plan invalidation, etc. Point being, maybe the time consuming stuff >> can't really be deferred which would make the proposal moot. > > Assuming the issue really is the physical unlinks (which I agree I'd > like to see some evidence for), I wonder whether the problem could be > addressed by moving smgrDoPendingDeletes() to after locks are released, > instead of before, in CommitTransaction/AbortTransaction. There does > not seem to be any strong reason why we have to do that before lock > release, since incoming potential users of a table should not be trying > to access the old physical storage after that anyway. Alright, since this concern about confirming the expensive part of index dropping has come up a few times but otherwise the waters are warm, I'll go ahead and do some work to pin things down a bit before we continue working on those assumptions. -- fdr -- 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] Should I implement DROP INDEX CONCURRENTLY?
Merlin Moncure writes: > On Wed, Aug 24, 2011 at 1:24 PM, Daniel Farina wrote: >> At Heroku we use CREATE INDEX CONCURRENTLY with great success, but >> recently when frobbing around some indexes I realized that there is no >> equivalent for DROP INDEX, and this is a similar but lesser problem >> (as CREATE INDEX takes much longer), as DROP INDEX takes an ACCESS >> EXCLUSIVE lock on the parent table while doing the work to unlink >> files, which nominally one would think to be trivial, but I assure you >> it is not at times for even indexes that are a handful of gigabytes >> (let's say ~=< a dozen). > Are you sure that you are really waiting on the time to unlink the > file? there's other stuff going on in there like waiting for lock, > plan invalidation, etc. Point being, maybe the time consuming stuff > can't really be deferred which would make the proposal moot. Assuming the issue really is the physical unlinks (which I agree I'd like to see some evidence for), I wonder whether the problem could be addressed by moving smgrDoPendingDeletes() to after locks are released, instead of before, in CommitTransaction/AbortTransaction. There does not seem to be any strong reason why we have to do that before lock release, since incoming potential users of a table should not be trying to access the old physical storage after that anyway. 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] Should I implement DROP INDEX CONCURRENTLY?
On Wed, Aug 24, 2011 at 2:24 PM, Daniel Farina wrote: > Could I make the ACCESS EXCLUSIVE section just long enough to commit > catalog updates, and then have the bulk of the work happen afterwards? > > The general idea is: > > 1) set an index as "invalid", to ensure no backend will use it in planning > 2) wait for the xmin horizon to advance to ensure no open snapshots > that may not see the invalidation of the index are gone (is there a > way to tighten that up? although even this conservative version would > be 80-90% of the value for us...) > 3) then use performDeletions without taking a lock on the parent > table, similar to what's in tablecmds.c already. > > A DROP INDEX CONCURRENTLY may leave an invalid index if aborted > instead of waiting for statement confirmation, just like CREATE INDEX > CONCURRENTLY. This might be a dumb idea, but could we rearrange CommitTransaction() so that smgrDoPendingDeletes() happens just a bit further down, after those ResourceOwnerRelease() calls? It seems like that might accomplish what you're trying to do here without needing a new command. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] "make -j4 world" falls over
Peter Eisentraut writes: > On tor, 2011-08-18 at 18:56 -0400, Tom Lane wrote: >> I ran into $SUBJECT whilst doing trial RPM packaging of 9.1. > Fixed. Works for me --- thanks! 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] patch to slightly improve clarity of a comment in postgresql.conf.sample
The attached change to postgresql.conf.sample makes it more clear at a glance that the default value of listen_addresses is 'localhost', not 'localhost, *'. This would have saved a friend an hour or two of fiddling tonight. Thanks, Dougal doc-clarity.patch Description: Binary data -- 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] "make -j4 world" falls over
On tor, 2011-08-18 at 18:56 -0400, Tom Lane wrote: > I ran into $SUBJECT whilst doing trial RPM packaging of 9.1. The problem > is that make starts building contrib modules before errcodes.h has been > made, leading to failures such as > > In file included from ../../src/include/postgres.h:48:0, > from auth_delay.c:12: > ../../src/include/utils/elog.h:69:28: fatal error: utils/errcodes.h: No such > file or directory > compilation terminated. > In file included from ../../src/include/postgres.h:48:0, > from adminpack.c:15: > ../../src/include/utils/elog.h:69:28: fatal error: utils/errcodes.h: No such > file or directory > compilation terminated. > > I can work around this for the moment, but I think it's a "must fix" > before release. Fixed. -- 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] synchronized snapshots
On lör, 2011-08-20 at 09:56 -0400, Bruce Momjian wrote: > Peter Eisentraut wrote: > > On tis, 2011-08-16 at 20:35 -0400, Tom Lane wrote: > > > In fact, now that I think about it, setting the transaction snapshot > > > from a utility statement would be functionally useful because then you > > > could take locks beforehand. > > > > Another issue is that in some client interfaces, BEGIN and COMMIT are > > hidden behind API calls, which cannot easily be changed or equipped with > > new parameters. So in order to have this functionality available > > through those interfaces, we'd need a separately callable command. > > How do they set a transaction to SERIALIZABLE? Seem the same syntax > should be used here. The API typically has parameters to set the isolation level, since that's a standardized property. -- 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] Should I implement DROP INDEX CONCURRENTLY?
On Wed, Aug 24, 2011 at 1:24 PM, Daniel Farina wrote: > Hello list, > > At Heroku we use CREATE INDEX CONCURRENTLY with great success, but > recently when frobbing around some indexes I realized that there is no > equivalent for DROP INDEX, and this is a similar but lesser problem > (as CREATE INDEX takes much longer), as DROP INDEX takes an ACCESS > EXCLUSIVE lock on the parent table while doing the work to unlink > files, which nominally one would think to be trivial, but I assure you > it is not at times for even indexes that are a handful of gigabytes > (let's say ~=< a dozen). By non-trivial, I mean it can take 30+ > seconds, but less than a couple of minutes. The storage layer > (starting from the higher levels of abstraction) are XFS, a somewhat > trivial lvm setup, mdraid (8-ways), Amazon EBS (NBD?). Are you sure that you are really waiting on the time to unlink the file? there's other stuff going on in there like waiting for lock, plan invalidation, etc. Point being, maybe the time consuming stuff can't really be deferred which would make the proposal moot. merlin -- 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] REGRESS_OPTS default
On tis, 2011-08-23 at 21:17 -0400, Tom Lane wrote: > There are at least two ways we could fix this: change > earthdistance/Makefile to do this: > > REGRESS_OPTS = --extra-install=contrib/cube --dbname=$(CONTRIB_TESTDB) > > or change pgxs.mk to do this: > > REGRESS_OPTS += --dbname=$(CONTRIB_TESTDB) > > I'm leaning towards the latter as being less prone to mistakes of > omission. If there's some reason for a pgxs-using makefile to > override > the target DB name, it can set CONTRIB_TESTDB instead of messing with > REGRESS_OPTS. I like the latter solution as well. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Should I implement DROP INDEX CONCURRENTLY?
Hello list, At Heroku we use CREATE INDEX CONCURRENTLY with great success, but recently when frobbing around some indexes I realized that there is no equivalent for DROP INDEX, and this is a similar but lesser problem (as CREATE INDEX takes much longer), as DROP INDEX takes an ACCESS EXCLUSIVE lock on the parent table while doing the work to unlink files, which nominally one would think to be trivial, but I assure you it is not at times for even indexes that are a handful of gigabytes (let's say ~=< a dozen). By non-trivial, I mean it can take 30+ seconds, but less than a couple of minutes. The storage layer (starting from the higher levels of abstraction) are XFS, a somewhat trivial lvm setup, mdraid (8-ways), Amazon EBS (NBD?). I was poking around at tablecmds and index.c and wonder if a similar two-pass approach as used by CREATE INDEX CONCURRENTLY can be used to create a DROP INDEX CONCURRENTLY, and if there would be any interest in accepting such a patch. Quoth index.c: /* * To drop an index safely, we must grab exclusive lock on its parent * table. Exclusive lock on the index alone is insufficient because * another backend might be about to execute a query on the parent table. * If it relies on a previously cached list of index OIDs, then it could * attempt to access the just-dropped index. We must therefore take a * table lock strong enough to prevent all queries on the table from * proceeding until we commit and send out a shared-cache-inval notice * that will make them update their index lists. */ Could I make the ACCESS EXCLUSIVE section just long enough to commit catalog updates, and then have the bulk of the work happen afterwards? The general idea is: 1) set an index as "invalid", to ensure no backend will use it in planning 2) wait for the xmin horizon to advance to ensure no open snapshots that may not see the invalidation of the index are gone (is there a way to tighten that up? although even this conservative version would be 80-90% of the value for us...) 3) then use performDeletions without taking a lock on the parent table, similar to what's in tablecmds.c already. A DROP INDEX CONCURRENTLY may leave an invalid index if aborted instead of waiting for statement confirmation, just like CREATE INDEX CONCURRENTLY. -- fdr -- 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] Windows env returns error while running "select pgstatindex"
Robert Haas writes: > On Wed, Aug 24, 2011 at 11:45 AM, Tom Lane wrote: >> I kinda suspect that the NaN behavior was not designed but accidental. >> What I'm wondering is whether it's really the "right", sensible, >> behavior. > On a blank slate, I might choose to do it differently, but considering > that we have numerous releases out in the field that return NaN, I > think we should stick with that rather than using this minor bug as an > excuse to change the answer on platforms where this isn't already > broken. [ pokes at it... ] Hmm, you're right, everything back to 8.2 produces NaNs on this test case (at least on IEEE-compliant platforms). I yield to the "let's emit NaN" viewpoint. 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] Windows env returns error while running "select pgstatindex"
On Wed, Aug 24, 2011 at 11:45 AM, Tom Lane wrote: > Euler Taveira de Oliveira writes: >> Em 24-08-2011 11:27, Tom Lane escreveu: >>> Hmm. I agree we need to avoid executing 0/0 here, but should we force >>> the result to 0, or to NaN? > >> If it returns NaN on other platforms, let's be consistent. > > I kinda suspect that the NaN behavior was not designed but accidental. > What I'm wondering is whether it's really the "right", sensible, > behavior. > > On reflection I suspect it isn't --- it'd bollix sum() or avg() > calculations over the function's results, for instance. But now > I'm not sure zero is the right thing to put in, either. It's not very sensible to sum() or avg() such values from different tables, but if you did wish to do so it would be easy enough to shove a CASE statement in there to filter out the NaN results. On a blank slate, I might choose to do it differently, but considering that we have numerous releases out in the field that return NaN, I think we should stick with that rather than using this minor bug as an excuse to change the answer on platforms where this isn't already broken. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Windows env returns error while running "select pgstatindex"
Euler Taveira de Oliveira writes: > Em 24-08-2011 11:27, Tom Lane escreveu: >> Hmm. I agree we need to avoid executing 0/0 here, but should we force >> the result to 0, or to NaN? > If it returns NaN on other platforms, let's be consistent. I kinda suspect that the NaN behavior was not designed but accidental. What I'm wondering is whether it's really the "right", sensible, behavior. On reflection I suspect it isn't --- it'd bollix sum() or avg() calculations over the function's results, for instance. But now I'm not sure zero is the right thing to put in, either. 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] Another extensions bug
Dimitri Fontaine writes: > Tom Lane writes: >> Hence, proposed patch attached (which also improves some of the related >> comments). > +1 on the idea, although I'm not in a position to further review or play > with the patch today. Further testing shows that this patch still doesn't make things really work properly. There's yet *another* bug with extension-dropping: given a case such as types cube[] and cube both belonging to extension cube, the end result is that type cube[] never gets dropped at all! This is because, since cube[] has both INTERNAL and EXTENSION dependencies, it's impossible for findDependentObjects to get past its first loop when cube[] is the target. It will always recurse to the other owning object. Infinite recursion is avoided because it finds the other owning object already on the stack, but we never get to the point of deciding that it's okay to delete type cube[]. So: test=# create extension cube; CREATE EXTENSION test=# \dT List of data types Schema | Name | Description +--+ - public | cube | multi-dimensional cube '(FLOAT-1, FLOAT-2, ..., FLOAT-N), (FLOA T-1, FLOAT-2, ..., FLOAT-N)' (1 row) test=# drop extension cube; DROP EXTENSION test=# \dT List of data types Schema | Name | Description +---+- public | ???[] | (1 row) test=# This is another bug that was arguably latent in the original coding, but could not manifest as long as there was only one INTERNAL-like dependency per object. I think the right fix is to replace the simple "is the referenced object on top of the stack?" check at lines 600ff with a check for "is the referenced object anywhere in the stack?". It's also becoming brutally obvious that we need some regression tests checking extension drop scenarios. I'll work on that today 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] Windows env returns error while running "select pgstatindex"
Em 24-08-2011 11:27, Tom Lane escreveu: Hmm. I agree we need to avoid executing 0/0 here, but should we force the result to 0, or to NaN? If it returns NaN on other platforms, let's be consistent. -- Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento -- 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] Windows env returns error while running "select pgstatindex"
Rushabh Lathia writes: > After debugging I noticed that "0/0" returning NaN on linux but it returns > "-1.#JIND" on windows. [ rolls eyes ] > I added to check into pgstatindex() to avoid "0/0" situation and issue got > fixed. Hmm. I agree we need to avoid executing 0/0 here, but should we force the result to 0, or to NaN? 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] WIP: Fast GiST index build
I've added some testing results to the wiki page: http://wiki.postgresql.org/wiki/Fast_GiST_index_build_GSoC_2011 There are not all the results I planned for the first chunk because it takes more time than I expect. Some notes about it. Now I see two causes which accelerate regular build of GiST indexes: 1) As it was noted before regular index build of pretty ordered dataset is fast. 2) I found that worse index is faster to build. I mean worse index is index with higher overlaps. Function gistchoose selects the first index tuple with zero penalty if any. Thus, with higher overlap in root page only few index tuples of it will be choosed for insert. And, recursively, only small part of the tree will be used for actual inserts. And that part of tree can easier fit to the cache. Thus, high overlaps makes inserts cheaper as much as searches expensiver. In the tests on the first version of patch I found index quality of regular build much better than it of buffering build (without neighborrelocation). Now it's similar, though it's because index quality of regular index build become worse. There by in current tests regular index build is faster than in previous. I see following possible causes of it: 1) I didn't save source random data. So, now it's a new random data. 2) Some environment parameters of my test setup may alters, though I doubt. Despite these possible explanation it seems quite strange for me. In order to compare index build methods on more qualitative indexes, I've tried to build indexes with my double sorting split method (see: http://syrcose.ispras.ru/2011/files/SYRCoSE2011_Proceedings.pdf#page=36). So on uniform dataset search is faster in about 10 times! And, as it was expected, regular index build becomes much slower. It runs more than 60 hours and while only 50% of index is complete (estimated by file sizes). Also, automatic switching to buffering build shows better index quality results in all the tests. While it's hard for me to explain that. -- With best regards, Alexander Korotkov.
Re: [HACKERS] the big picture for index-only scans
> > > There are extensive comments on this in visibilitymap.c and, in > heapam.c, heap_xlog_visible(). > > I went through the design again and again. I am convinced that this should not have any functional bugs and should not cause much performance issues. Nice thoughts on bypassing the WAL Logging. Gokul.
[HACKERS] Windows env returns error while running "select pgstatindex"
Description:
===
Error Message " invalid input syntax for type double precision: -1#I" is
displayed while running "select pgstatindex"
Issue only getting reproduce on windows environment.
Analysis:
=
Consider the following testcase to reproduce the issue on windows:
create table test (a int primary key );
Windows Output:
==
psql>select pgstatindex('public.test_pkey');
ERROR: invalid input syntax for type double precision: "-1.#J"
Linux output:
==
psql=# select pgstatindex('public.test_pkey');
pgstatindex
---
(2,0,0,0,0,0,0,0,NaN,NaN)
(1 row)
here when we run the select on linux its returning proper result and on
windows error coming from float8in() while trying to work for the NaN
values.
After debugging I noticed that "0/0" returning NaN on linux but it returns
"-1.#JIND" on windows. Now when float8in() getting call for such value
on windows it ending up with error "invalid input syntax for type double
precision:" as strtod() not able to understand such values.
I added to check into pgstatindex() to avoid "0/0" situation and issue got
fixed.
PFA patch for the same.
Thanks,
Rushabh Lathia
EnterpriseDB Corporation
The Enterprise Postgres Company
Website: http://www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb
win_pgstat_fix.patch
Description: Binary data
--
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] cheaper snapshots redux
Robert, Jim, thanks for thinking out loud about dynamic allocation of shared memory. Very much appreciated. On 08/23/2011 01:22 AM, Robert Haas wrote: > With respect to a general-purpose shared memory allocator, I think > that there are cases where that would be useful to have, but I don't > think there are as many of them as many people seem to think. I > wouldn't choose to implement this using a general-purpose allocator > even if we had it, both because it's undesirable to allow this or any > subsystem to consume an arbitrary amount of memory (nor can it fail... > especially in the abort path) and because a ring buffer is almost > certainly faster than a general-purpose allocator. I'm in respectful disagreement regarding the ring-buffer approach and think that dynamic allocation can actually be more efficient if done properly, because there doesn't need to be head and tail pointers, which might turn into a point of contention. As a side note: that I've been there with imessages. Those were first organized as a ring-bufffer. The major problem with that approach was the imessages were consumed with varying delay. In case an imessage was left there for a longer amount of time, it blocked creation of new imessages, because the ring-buffer cycled around once and its head arrived back at the unconsumed imessage. IIUC (which might not be the case) the same issue applies for snapshots. Regards Markus Wanner -- 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] cheaper snapshots redux
Hello Dimitri, On 08/23/2011 06:39 PM, Dimitri Fontaine wrote: > I'm far from familiar with the detailed concepts here, but allow me to > comment. I have two open questions: > > - is it possible to use a distributed algorithm to produce XIDs, >something like Vector Clocks? > >Then each backend is able to create a snapshot (well, and XID) on its >own, and any backend is still able to compare its snapshot to any >other snapshot (well, XID) Creation of snapshots and XID assignment are not as related as you imply here. Keep in mind that a read-only transaction have a snapshot, but no XID. (Not sure if it's possible for a transaction to have an XID, but no snapshot. If it only touches system catalogs with SnapshotNow, maybe? Don't think we support that, ATM). > - is it possible to cache the production of the next snapshots so that >generating an XID only means getting the next in a pre-computed >vector? The way I look at it, what Robert proposed can be thought of as "cache the production of the next snapshot", with a bit of a stretch of what a cache is, perhaps. I'd rather call it "early snapshot creation", maybe "look-ahead something". ATM backends all scan ProcArray to generate their snapshot. Instead, what Robert proposes would - sometimes, somewhat - move that work from snapshot creation time to commit time. As Tom points out, the difficulty lies in the question of when it's worth doing that: if you have lots of commits in a row, and no transaction ever uses the (pre generated) snapshots of the point in time in between, then those were wasted. OTOH, if there are just very few COMMITs spread across lots of writes, the read-only backends will re-create the same snapshots, over and over again. Seems wasteful as well (as GetSnapshotData popping up high on profiles confirms somewhat). Hope to have cleared up things a bit. Regards Markus -- 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] Another extensions bug
Tom Lane writes: > On further reflection, it seems more in keeping with the coding > elsewhere in this module to treat this as a distinct dependency type, > instead of confusing it with a NORMAL dependency. There's no actual > functional difference at the moment, but more info is better than less. Seems better indeed. In my first implementation, we had no EXTENSION kind of dependency and used only INTERNAL, which IIRC reads reverse than the other ones. Having to finally have EXTENSION and REVERSE kinds of dependencies here is not that surprising. > Hence, proposed patch attached (which also improves some of the related > comments). +1 on the idea, although I'm not in a position to further review or play with the patch today. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
