Re: [HACKERS] reindex creates predicate lock on index root
On 08.06.2011 06:37, Kevin Grittner wrote: You're right; that one was a false alarm. While REINDEX was reading the heap to build the index it got an SIREAD lock on a *heap* page. We never take locks on heap pages directly, so it must've been a promotion from heap tuple locks. While that could arguably be avoided, even though the heap is being read in a serializable transaction, I'm not inclined to get really excited about it. If someone wants to dodge it, they can always run the REINDEX in READ COMMITTED or REPEATABLE READ mode. Maybe 9.2 material if there's nothing to do that matters more than that. That should be pretty easy to avoid, though. I think we should fix it now. -- 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] [PATCH] Bug in XPATH() if expression returns a scalar value
On Jun6, 2011, at 14:56 , Peter Eisentraut wrote: On tis, 2011-05-31 at 16:19 +0200, Florian Pflug wrote: If people deem this to be a problem, we could instead add a separate function XPATH_VALUE() that returns VARCHAR, and make people use that for scalar-value-returning expressions. Why not replicate what contrib/xml2 provides, namely xpath_string() xpath_number() xpath_bool() That way, types are preserved. But then you lose the ability to evaluate user-supplied XPath expressions, because there's no way of telling which of these function to use. Since XPATH_BOOL() can be emulated by doing XPATH(...)::text::boolean if XPATH() implements the more lenient behaviour I proposed, that seems like a bad tradeoff overall. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SSI heap_insert and page-level predicate locks
heap_insert() calls CheckForSerializableConflictIn(), which checks if there is a predicate lock on the whole relation, or on the page we're inserting to. It does not check for tuple-level locks, because there can't be any locks on a tuple that didn't exist before. AFAICS, the check for page lock is actually unnecessary. A page-level lock on a heap only occurs when tuple-level locks are promoted. It is just a coarser-grain representation of holding locks on all tuples on the page, *that exist already*. It is not a gap lock like the index locks are, it doesn't need to conflict with inserting new tuples on the page. In fact, if heap_insert chose to insert the tuple on some other heap page, there would have been no conflict. -- 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] reindex creates predicate lock on index root
On Tue, Jun 07, 2011 at 10:14:30PM -0400, Tom Lane wrote: Do you mean page zero, as in the metapage (for most index types), or do you mean the root page? If the former, how is that not an outright bug, since it corresponds to no data? If the latter, how is that not a serious performance problem, since it corresponds to locking the entire index? Any way you slice it, it sounds like a pretty bad bug. It's a moot point since that isn't actually happening, but FYI, we only acquire and check for locks on b-tree leaf pages so locking the root wouldn't have any effect. (This won't be true for other index types; GIST comes to mind.) It's not apparent to me why an index build (regular or reindex) should create any predicate locks at all, ever. Surely it's a basically nontransactional operation that SSI should keep its fingers out of. It shouldn't. What's happening is that when IndexBuildHeapScan reads the heap tuples, heap_getnext takes a lock if it's running inside a serializable transaction. It doesn't (yet) know that it doesn't need the locks for an index build. We could set a flag in the HeapScanDesc to indicate this. Actually, setting rs_relpredicatelocked has exactly that effect, so we ought to be able to use that (but might want to change the name). Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- 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] [Pgbuildfarm-members] CREATE FUNCTION hang on test machine polecat on HEAD
On Jun 7, 2011, at 12:42 PM, Alex Hunsaker bada...@gmail.com wrote: On Tue, Jun 7, 2011 at 12:22, Tom Lane t...@sss.pgh.pa.us wrote: Alex Hunsaker bada...@gmail.com writes: Im looking at the raw perl 5.10.0 source... I wonder if apple is shipping a modified version? You could find out by digging around at http://www.opensource.apple.com/ polecat appears to be running OSX 10.6.7, so this is what you want: http://www.opensource.apple.com/tarballs/perl/perl-63.tar.gz Thanks! Another question worth asking here is whether PG is picking up perl 5.10.0 or 5.8.9, both of which are shipped in this OSX release. I was looking at http://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=polecatdt=2011-06-07%2015%3A23%3A34stg=config which seems to point at 5.10.0. Robert: perl -V might be useful Hmm... This might be a problem: which perl /opt/local/bin/perl type -a perl /opt/local/bin/perl /usr/bin/perl /opt/local/bin/perl -V This is perl, v5.8.9 built for darwin-2level Copyright 1987-2008, Larry Wall Perl may be copied only under the terms of either the Artistic License or the GNU General Public License, which may be found in the Perl 5 source kit. Complete documentation for Perl, including FAQ lists, should be found on this system using man perl or perldoc perl. If you have access to the Internet, point your browser at http://www.perl.org/, the Perl Home Page. /usr/bin/perl -V Summary of my perl5 (revision 5 version 10 subversion 0) configuration: Platform: osname=darwin, osvers=10.0, archname=darwin-thread-multi-2level uname='darwin neige.apple.com 10.0 darwin kernel version 10.0.0d8: tue may 5 19:29:59 pdt 2009; root:xnu-1437.2~2release_i386 i386 ' config_args='-ds -e -Dprefix=/usr -Dccflags=-g -pipe -Dldflags= -Dman3ext=3pm -Duseithreads -Duseshrplib -Dinc_version_list=none -Dcc=gcc-4.2' hint=recommended, useposix=true, d_sigaction=define useithreads=define, usemultiplicity=define useperlio=define, d_sfio=undef, uselargefiles=define, usesocks=undef use64bitint=define, use64bitall=define, uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='gcc-4.2', ccflags ='-arch x86_64 -arch i386 -arch ppc -g -pipe -fno-common -DPERL_DARWIN -fno-strict-aliasing -I/usr/local/include', optimize='-Os', cppflags='-g -pipe -fno-common -DPERL_DARWIN -fno-strict-aliasing -I/usr/local/include' ccversion='', gccversion='4.2.1 (Apple Inc. build 5646)', gccosandvers='' intsize=4, longsize=8, ptrsize=8, doublesize=8, byteorder=12345678 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16 ivtype='long', ivsize=8, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8 alignbytes=8, prototype=define Linker and Libraries: ld='gcc-4.2 -mmacosx-version-min=10.6', ldflags ='-arch x86_64 -arch i386 -arch ppc -L/usr/local/lib' libpth=/usr/local/lib /usr/lib libs=-ldbm -ldl -lm -lutil -lc perllibs=-ldl -lm -lutil -lc libc=/usr/lib/libc.dylib, so=dylib, useshrplib=true, libperl=libperl.dylib gnulibc_version='' Dynamic Linking: dlsrc=dl_dlopen.xs, dlext=bundle, d_dlsymun=undef, ccdlflags=' ' cccdlflags=' ', lddlflags='-arch x86_64 -arch i386 -arch ppc -bundle -undefined dynamic_lookup -L/usr/local/lib' Characteristics of this binary (from libperl): Compile-time options: MULTIPLICITY PERL_DONT_CREATE_GVSV PERL_IMPLICIT_CONTEXT PERL_MALLOC_WRAP USE_64_BIT_ALL USE_64_BIT_INT USE_ITHREADS USE_LARGE_FILES USE_PERLIO USE_REENTRANT_API Locally applied patches: /Library/Perl/Updates/version comes before system perl directories installprivlib and installarchlib points to the Updates directory Built under darwin Compiled at Jun 24 2009 00:35:27 @INC: /Library/Perl/Updates/5.10.0/darwin-thread-multi-2level /Library/Perl/Updates/5.10.0 /System/Library/Perl/5.10.0/darwin-thread-multi-2level /System/Library/Perl/5.10.0 /Library/Perl/5.10.0/darwin-thread-multi-2level /Library/Perl/5.10.0 /Network/Library/Perl/5.10.0/darwin-thread-multi-2level /Network/Library/Perl/5.10.0 /Network/Library/Perl /System/Library/Perl/Extras/5.10.0/darwin-thread-multi-2level /System/Library/Perl/Extras/5.10.0 .
Re: [HACKERS] smallserial / serial2
Yup- it's attached. Mike From: Brar Piening [mailto:b...@gmx.de] Sent: Tuesday, June 07, 2011 6:58 PM To: Mike Pultz Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] smallserial / serial2 On Wed, 20 Apr 2011 21:27:27 -0400, Mike Pultz mailto:m...@mikepultz.com m...@mikepultz.com wrote: Can this be added? Probably not - since it's not a complete patch ;-) I tried to test this one but was unable to find a complete version of the patch in my local mail archives and in the official archives (http://archives.postgresql.org/message-id/023001cbffc3$46f77840$d4e668c0$@m ikepultz.com) Could you please repost it for testing? Regards, Brar 20110607_serial2.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] [Pgbuildfarm-members] CREATE FUNCTION hang on test machine polecat on HEAD
On Jun 7, 2011, at 3:01 PM, Tom Lane wrote: Robert Creager robert.crea...@oracle.com writes: The configure log mentioned upthread says it's finding /usr/bin/perl, so apparently the buildfarm is running with a different PATH than you're using here. But that log also shows configure:7158: checking for flags to link embedded Perl configure:7174: result: -L/usr/local/lib -L/System/Library/Perl/5.10.0/darwin-thread-multi-2level/CORE -lperl -ldl -lm -lutil -lc If there's anything perl-related in /usr/local/lib (not /opt/local/lib), that could be confusing matters. That's what I get for doing this in a meeting, doing both poorly. run_build.pl: #!/usr/bin/perl No perl in /usr/local/lib. Rob -- inline: oracle_sig_logo.gif Robert Creager, Principal Software Engineer Oracle Server Technologies 500 Eldorado Blvd, Bldg 5 Broomfield, CO, 80021 Phone: 303-272-6830 Email: robert.crea...@oracle.com inline: green-for-email-sig_0.gifOracle is committed to developing practices and products that help protect the environment -- 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 heap_insert and page-level predicate locks
On Wed, Jun 08, 2011 at 11:23:48AM +0300, Heikki Linnakangas wrote: AFAICS, the check for page lock is actually unnecessary. A page-level lock on a heap only occurs when tuple-level locks are promoted. It is just a coarser-grain representation of holding locks on all tuples on the page, *that exist already*. It is not a gap lock like the index locks are, it doesn't need to conflict with inserting new tuples on the page. In fact, if heap_insert chose to insert the tuple on some other heap page, there would have been no conflict. Yes, it's certainly unnecessary now, given that we never explicitly take heap page locks, just tuple- or relation-level. The only thing I'd be worried about is that at some future point we might add heap page locks -- say, for sequential scans that don't read the entire relation -- and expect inserts to be tested against them. I'm not sure whether we'd actually do this, but we wanted to keep the option open during development. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- 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 heap_insert and page-level predicate locks
On 08.06.2011 12:36, Dan Ports wrote: On Wed, Jun 08, 2011 at 11:23:48AM +0300, Heikki Linnakangas wrote: AFAICS, the check for page lock is actually unnecessary. A page-level lock on a heap only occurs when tuple-level locks are promoted. It is just a coarser-grain representation of holding locks on all tuples on the page, *that exist already*. It is not a gap lock like the index locks are, it doesn't need to conflict with inserting new tuples on the page. In fact, if heap_insert chose to insert the tuple on some other heap page, there would have been no conflict. Yes, it's certainly unnecessary now, given that we never explicitly take heap page locks, just tuple- or relation-level. The only thing I'd be worried about is that at some future point we might add heap page locks -- say, for sequential scans that don't read the entire relation -- and expect inserts to be tested against them. I'm not sure whether we'd actually do this, but we wanted to keep the option open during development. I think that is only relevant for queries like SELECT * FROM table WHERE ctid = '(12,34)'. You might expect that we take a lock on that physical part of the heap, so that an INSERT that falls on that slot would conflict, but one that falls elsewhere does not. At the moment, a TidScan only takes a predicate lock tuples that exist, it doesn't try to lock the range like an IndexScan would. The physical layout of the table is an implementation detail that the application shouldn't care about, so I don't feel sorry for anyone doing that. Maybe it's worth mentioning somewhere in the docs, though. -- 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
[HACKERS] .gitignore for some of cygwin files
After compilation I got big list of differences about binaries and other files. You may see it in patch. Regards, Radekdiff --git a/.gitignore b/.gitignore old mode 100644 new mode 100755 index 81c4d5e..75e1d9a --- a/.gitignore +++ b/.gitignore @@ -20,6 +20,14 @@ lcov.info *.vcproj win32ver.rc +# Defs from gcc in Windows +*dll.def +/src/backend/postgres.def + +# Windows executables, unless you don't keep file with this extension in +# repository global ignore is quite enaugh +*.exe + # Local excludes in root directory /GNUmakefile /config.log @@ -28,3 +36,4 @@ win32ver.rc /pgsql.sln.cache /Debug/ /Release/ +/nbproject/private/ \ No newline at end of file -- 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] SIREAD lock versus ACCESS EXCLUSIVE lock
On 08.06.2011 03:16, Kevin Grittner wrote: + /* +* It's OK to remove the old lock first because of the ACCESS +* EXCLUSIVE lock on the heap relation when this is called. It is +* desirable to do so because it avoids any chance of running out +* of lock structure entries for the table. +*/ That assumption is wrong, for REINDEX. REINDEX INDEX holds an AccessExclusive on the index, but only a ShareLock on the heap. The code is still OK, though. As long as you hold an AccessExclusiveLock on the index, no-one will care about predicate locks on the index, so we can remove them before acquiring the lock on the heap. And we hold lwlocks on all the predicate lock partitions, so all the operations will appear atomic to any outside observer anyway. Committed after adjusting that comment. I did a lot of other cosmetic changes too, please double-check that I didn't screw up anything. I also made rewriting ALTER TABLE to behave like CLUSTER, by adding a call to TransferPredicateLocksToHeapRelation() there. I just looked back at your old email where you listed the different DDL operations, and notice that we missed VACUUM FULL as well (http://archives.postgresql.org/message-id/4dbd7e9102250003d...@gw.wicourts.gov). I'll look into that. ALTER INDEX was also still an open question in that email. BTW, truncating the tail of a heap in vacuum probably also should drop any locks on the truncated part of the heap. Or is it not possible any such locks to exist, because none of the tuples are visible to anyone anymore? A comment in lazy_truncate_heap() might be in order. -- 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] SIREAD lock versus ACCESS EXCLUSIVE lock
On 08.06.2011 14:18, Heikki Linnakangas wrote: I just looked back at your old email where you listed the different DDL operations, and notice that we missed VACUUM FULL as well (http://archives.postgresql.org/message-id/4dbd7e9102250003d...@gw.wicourts.gov). I'll look into that. Never mind that, VACUUM FULL uses cluster_rel(), so that's covered already. ALTER INDEX was also still an open question in that email. None of the variants of ALTER INDEX do anything that affects predicate locks, so that's OK too. -- 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] SIREAD lock versus ACCESS EXCLUSIVE lock
(sorry for repeatedly replying to self. I'll go for a coffee after this...) On 08.06.2011 14:18, Heikki Linnakangas wrote: Committed after adjusting that comment. I did a lot of other cosmetic changes too, please double-check that I didn't screw up anything. Also, it would be nice to have some regression tests for this. I don't think any of the existing tests exercise these new functions (except for the fast-exit, which isn't very interesting). -- 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] WALInsertLock contention
On Wed, Jun 8, 2011 at 1:59 AM, Merlin Moncure mmonc...@gmail.com wrote: There's probably an obvious explanation that I'm not seeing, ... Yep. :-) but if you're not delegating the work of writing the buffers out to someone else, why do you need to lock the per backend buffer at all? That is, why does it have to be in shared memory? Suppose that if the following are true: *) Writing qualifying data (non commit, non switch) *) There is room left in whatever you are copying to you could trylock WalInsertLock, and if failing to get it, just copy qualifying data into a private buffer and punt if the following are true...otherwise just do the current behavior. And here it is: Writing a buffer requires a write fsync of WAL through the buffer LSN. If the WAL for the buffers were completely inaccessible to other backends, then those buffers would be pinned in shared memory. Which would make things very difficult at buffer eviction time, or for checkpoints. At any rate, even if it were possible to make it work, it'd be a misplaced optimization. It isn't touching shared memory - or even touching the LWLock - that's expensive; it's the LWLock contention that kills you, either because stuff blocks, or just because the CPUs burn a lot of cycles fighting over cache lines. An LWLock that is typically taken by only one backend at a time is pretty cheap. I suppose I couldn't afford to be so blasé if we were trying to scale to 2048-core systems where even inserting a memory barrier is expensive enough to worry about, but we've got a ways to go before we need to start worrying about that. [...snip...] A further refinement would be to try to jigger things so that as a backend fills up per-backend WAL buffers, it somehow throws them over the fence to one of the background processes to write out. For short-running transactions, that won't really make any difference, since the commit will force the per-backend buffers out to the main buffers anyway. But for long-running transactions it seems like it could be quite useful; in essence, the task of assembling the final WAL stream from the WAL output of individual backends becomes a background activity, and ideally the background process doing the work is the only one touching the cache lines being shuffled around. Of course, to make this work, backends would need a steady supply of available per-backend WAL buffers. Maybe shared buffers could be used for this purpose, with the buffer header being marked in some special way to indicate that this is what the buffer's being used for. That seems complicated -- plus I think the key is to distribute as much of the work as possible. Why would the forward lateral to the background processor not require a similar lock to WalInsertLock? Well, that's the problem. It would. Now, in an ideal world, you might still hope to get some benefit: only the background writer would typically be writing to the real WAL stream, so that's not contended. And the contention between the background writer and the individual backends is only two-way. There's no single point where you have every process on the system piling on to a single lock. But I'm not sure we can really make it work well enough to do more than nibble around at the edges of the problem. Consider: INSERT INTO foo VALUES (1,2,3); This is going to generate XLOG_HEAP_INSERT followed by XLOG_XACT_COMMIT. And now it wants to flush WAL. So now you're pretty much forced to have it go perform the serialization operation itself, and you're right back in contention soup. Batching two records together and inserting them in one operation is presumably going to be more efficient than inserting them one at a time, but not all that much more efficient; and there are bookkeeping and memory bandwidth costs to get there. If we are dealing with long-running transactions, or asynchronous commit, then this approach might have legs -- but I suspect that in real life most transactions are small, and the default configuration is synchronous_commit=on. -- 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
[HACKERS] Autoanalyze and OldestXmin
Hi All, I was running some pgbench tests and observed this phenomenon. This might be a known issue, but I thought its nevertheless worth mentioning. Auto-analyze process grabs a MVCC snapshot. If it runs on a very large table, it may take considerable time and would stop the OldestXmin from advancing. During that time, if there are highly updated small tables, those would bloat a lot. For example, in the attached log snippet (and the HEAD is patched a bit to produce more information than what you would otherwise see), for a scale factor of 50 and 50 clients: branches and tellers tables, which had a stable size of around 65 and 90 pages respectively, bloat to 402 and 499 pages respectively when accounts table is being analyzed. The accounts table analyze takes around 5 mins on my decent server and the branches and tellers tables keep bloating during that time. If these small tables are very actively accessed, vacuum may not be able to even truncate them later, once OldestXmin advances at the end of auto analyze. I understand analyze needs snapshot to run index predicate functions, but is there something we can do ? There is a PROC_IN_ANALYZE flag, but we don't seem to be using it anywhere. Since acquire_sample_rows() returns palloced tuples, can't we let OldestXmin advance after scanning a page by ignoring procs with the flag set, just like we do for PROC_IN_VACUUM ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com 7457:2006-07-07 09:46:05.829 ISTLOG: automatic analyze of table postgres.public.pgbench_branches total rows 50, dead rows 2919, system usage: CPU 0.00s/0.00u sec elapsed 0.03 sec 7457:2006-07-07 09:46:05.874 ISTLOG: index pgbench_tellers_pkey now contains 500 row versions in 9 pages 7457:2006-07-07 09:46:05.874 ISTDETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.01 sec. 7457:2006-07-07 09:46:05.874 ISTLOG: automatic vacuum of table postgres.public.pgbench_tellers: index scans: 0 pages: 0 removed, 90 remain tuples: 4977 removed, 853 remain index pointers: 0 removed total cost delay: 0 system usage for first pass CPU 0.00s/0.00u sec elapsed 0.00 sec systema usage for second pass CPU 0.00s/0.00u sec elapsed 0.00 sec system usage for index scan CPU 0.00s/0.00u sec elapsed 0.00 sec total system usage: CPU 0.00s/0.00u sec elapsed 0.02 sec 7457:2006-07-07 09:46:05.899 ISTLOG: automatic analyze of table postgres.public.pgbench_tellers total rows 500, dead rows 357, system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec 7462:2006-07-07 09:47:19.309 ISTLOG: index pgbench_branches_pkey now contains 50 row versions in 2 pages 7462:2006-07-07 09:47:19.309 ISTDETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 56.13 sec. 7462:2006-07-07 09:47:19.309 ISTLOG: automatic vacuum of table postgres.public.pgbench_branches: index scans: 0 pages: 0 removed, 65 remain tuples: 5220 removed, 350 remain index pointers: 0 removed total cost delay: 20 system usage for first pass CPU 0.18s/0.18u sec elapsed 0.18 sec systema usage for second pass CPU 0.00s/0.00u sec elapsed 0.00 sec system usage for index scan CPU 0.00s/0.00u sec elapsed 0.00 sec total system usage: CPU 0.00s/0.00u sec elapsed 56.31 sec 7462:2006-07-07 09:47:19.341 ISTLOG: automatic analyze of table postgres.public.pgbench_branches total rows 50, dead rows 4238, system usage: CPU 0.00s/0.00u sec elapsed 0.02 sec 7462:2006-07-07 09:47:19.809 ISTLOG: index pgbench_tellers_pkey now contains 500 row versions in 9 pages 7462:2006-07-07 09:47:19.809 ISTDETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.03 sec. 7462:2006-07-07 09:47:19.810 ISTLOG: automatic vacuum of table postgres.public.pgbench_tellers: index scans: 0 pages: 0 removed, 90 remain tuples: 5442 removed, 770 remain index pointers: 0 removed total cost delay: 40 system usage for first pass CPU 0.38s/0.38u sec elapsed 0.38 sec systema usage for second pass CPU 0.00s/0.00u sec elapsed 0.00 sec system usage for index scan CPU 0.00s/0.00u sec elapsed 0.00 sec total system usage: CPU 0.00s/0.00u sec elapsed 0.41 sec 7462:2006-07-07 09:47:19.858 ISTLOG: automatic analyze of table postgres.public.pgbench_tellers total rows 500, dead rows 390, system usage: CPU 0.00s/0.00u sec elapsed 0.02 sec 7467:2006-07-07 09:47:22.994 ISTLOG: automatic analyze of table postgres.public.pgbench_branches total rows 50, dead rows 4644, system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec 7473:2006-07-07 09:48:48.390 ISTLOG: index pgbench_branches_pkey now contains 50 row versions in 2 pages
Re: [HACKERS] reindex creates predicate lock on index root
On Wed, Jun 8, 2011 at 4:59 AM, Dan Ports d...@csail.mit.edu wrote: On Tue, Jun 07, 2011 at 10:14:30PM -0400, Tom Lane wrote: Do you mean page zero, as in the metapage (for most index types), or do you mean the root page? If the former, how is that not an outright bug, since it corresponds to no data? If the latter, how is that not a serious performance problem, since it corresponds to locking the entire index? Any way you slice it, it sounds like a pretty bad bug. It's a moot point since that isn't actually happening, but FYI, we only acquire and check for locks on b-tree leaf pages so locking the root wouldn't have any effect. (This won't be true for other index types; GIST comes to mind.) It's not apparent to me why an index build (regular or reindex) should create any predicate locks at all, ever. Surely it's a basically nontransactional operation that SSI should keep its fingers out of. It shouldn't. What's happening is that when IndexBuildHeapScan reads the heap tuples, heap_getnext takes a lock if it's running inside a serializable transaction. It doesn't (yet) know that it doesn't need the locks for an index build. We could set a flag in the HeapScanDesc to indicate this. Actually, setting rs_relpredicatelocked has exactly that effect, so we ought to be able to use that (but might want to change the name). I'm wondering if this shouldn't be linked to whether the scan is using an MVCC snapshot, rather than inserting exceptions for specific operations. -- 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] SSI heap_insert and page-level predicate locks
On Wed, Jun 8, 2011 at 5:36 AM, Dan Ports d...@csail.mit.edu wrote: On Wed, Jun 08, 2011 at 11:23:48AM +0300, Heikki Linnakangas wrote: AFAICS, the check for page lock is actually unnecessary. A page-level lock on a heap only occurs when tuple-level locks are promoted. It is just a coarser-grain representation of holding locks on all tuples on the page, *that exist already*. It is not a gap lock like the index locks are, it doesn't need to conflict with inserting new tuples on the page. In fact, if heap_insert chose to insert the tuple on some other heap page, there would have been no conflict. Yes, it's certainly unnecessary now, given that we never explicitly take heap page locks, just tuple- or relation-level. The only thing I'd be worried about is that at some future point we might add heap page locks -- say, for sequential scans that don't read the entire relation -- and expect inserts to be tested against them. I'm not sure whether we'd actually do this, but we wanted to keep the option open during development. I don't think this is the right time to be rejiggering this stuff anyway. Our bug count is -- at least to outward appearances -- remarkably low at the moment, considering how much stuff we jammed into this release. Let's not hastily change things we might later regret having changed. -- 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] WALInsertLock contention
On Wed, Jun 8, 2011 at 7:44 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jun 8, 2011 at 1:59 AM, Merlin Moncure mmonc...@gmail.com wrote: There's probably an obvious explanation that I'm not seeing, ... Yep. :-) but if you're not delegating the work of writing the buffers out to someone else, why do you need to lock the per backend buffer at all? That is, why does it have to be in shared memory? Suppose that if the following are true: *) Writing qualifying data (non commit, non switch) *) There is room left in whatever you are copying to you could trylock WalInsertLock, and if failing to get it, just copy qualifying data into a private buffer and punt if the following are true...otherwise just do the current behavior. And here it is: Writing a buffer requires a write fsync of WAL through the buffer LSN. If the WAL for the buffers were completely inaccessible to other backends, then those buffers would be pinned in shared memory. Which would make things very difficult at buffer eviction time, or for checkpoints. Well, (bear with me here) I'm not giving up that easy. Pinning a judiciously small amount buffers into shared memory so you can recuce congestion on the insert lock might be an acceptable trade-off in high contention scenarios...in fact I assumed that was the whole point of your original idea, which I still think has tremendous potential. Obviously, you wouldn't want more than a very small percentage of shared buffers overall (say 1-10% max) to be pinned in this way. The trylock is an attempt to cap the downside case so that you aren't unnecessarily pinning buffers in say, long running i/o bound transactions where insert lock contention is low. Maybe you could experiment with very small private insert buffer sizes (say 64 kb) that would hopefully provide some of the benefits (if there are in fact any) and mitigate potential costs. Another tweak you could make is that, once having trylocked and failed in a transaction and failed acquirement, you always punt from there on in until you fill up or need to block per ordering requirements. Or maybe the whole thing doesn't help at all...just trying to understand the problem better. At any rate, even if it were possible to make it work, it'd be a misplaced optimization. It isn't touching shared memory - or even touching the LWLock - that's expensive; it's the LWLock contention that kills you, either because stuff blocks, or just because the CPUs burn a lot of cycles fighting over cache lines. An LWLock that is typically taken by only one backend at a time is pretty cheap. I suppose I couldn't afford to be so blasé if we were trying to scale to 2048-core systems where even inserting a memory barrier is expensive enough to worry about, but we've got a ways to go before we need to start worrying about that. Right -- although it isn't so much of an optimization (although you still want to do everything reasonable to keep work under the lock as light as possible, and shm-shm copy is going to be slower than mem-shm) as a simplification trade-off. You don't have to worry about deadlocks messing around with your per backend buffers during your internal 'flush', and it's generally just easier messing around with private memory (less code, less locking, less everything). One point i'm missing though. Getting back to your original idea, how does writing to shmem prevent you from having to keep buffers pinned? I'm reading your comment here: Those buffers are stamped with a fake LSN that points back to the per-backend WAL buffer, and they can't be written until the WAL has been moved from the per-backend WAL buffers to the main WAL buffers. That suggests to me that you have to keep them pinned anyways. I'm still a bit fuzzy on how the per-backend buffers being in shm conveys any advantage. IOW, (trying not to be obtuse) under what circumstances would backend A want to read from or (especially) write to backend B's wal buffer? 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] SIREAD lock versus ACCESS EXCLUSIVE lock
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: (sorry for repeatedly replying to self. I'll go for a coffee after this...) That's so nice of you to try to make me feel better for the serious brain fade I suffered yesterday. ;-) On 08.06.2011 14:18, Heikki Linnakangas wrote: Committed after adjusting that comment. I did a lot of other cosmetic changes too, please double-check that I didn't screw up anything. On a first read-through, all your changes look like improvements to me. I'll do some testing and give it a closer read. Also, it would be nice to have some regression tests for this. I don't think any of the existing tests exercise these new functions (except for the fast-exit, which isn't very interesting). I'll see about posting regression tests for this, as well as looking into the questions in your earlier posts -- particularly about the heap truncation in vacuum. I'm pretty sure that vacuum can't clean up anything where we're still holding predicate locks because of visibility rules, but I'll take another look to be sure. I hope to do all that today, but I don't want to push to the point where I'm making dumb mistakes again. -Kevin -- 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] reindex creates predicate lock on index root
Robert Haas robertmh...@gmail.com wrote: I'm wondering if this shouldn't be linked to whether the scan is using an MVCC snapshot, rather than inserting exceptions for specific operations. Yeah, that was raised before somewhere and I spaced it. Grabbing predicate locks for non-MVCC snapshots is nonsense, and the fix is a one-line addition to the SkipSerialization macro defined and used in predicate.c. Patch attached. I agree with your other post that changes which are in the nature of improving performance (which for SSI includes changes which reduce the number of false positive serialization failures for serializable transactions) should be viewed very cautiously in terms of 9.1 inclusion. We're already at a point where a DBT-2 benchmark only shows a 2% hit for SSI overhead, including transaction restarts for serialization failures. I'd love to get that down to 1% or lower, but I don't want to create any risk of introducing bugs in 9.1 at this point. I think this one-liner might be worth considering, since it is very low risk and fixes an undesirable behavior which some (Tom, at least, it would appear) would have no trouble categorizing as a bug. The attached patch has not yet been tested, but I'll test it today along with the latest committed code. -Kevin *** a/src/backend/storage/lmgr/predicate.c --- b/src/backend/storage/lmgr/predicate.c *** *** 274,279 --- 274,280 #define SkipSerialization(relation) \ ((!IsolationIsSerializable()) \ || ((MySerializableXact == InvalidSerializableXact)) \ + || (!IsMVCCSnapshot(GetActiveSnapshot())) \ || ReleasePredicateLocksIfROSafe() \ || SkipPredicateLocksForRelation(relation)) -- 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] Error in PQsetvalue
On Mon, Jun 6, 2011 at 1:42 PM, Merlin Moncure mmonc...@gmail.com wrote: On Mon, Jun 6, 2011 at 7:09 AM, Pavel Golub pa...@microolap.com wrote: Sorry for delay in answer. Yeah, I'm glad to. Should I apply this patch by myself? sure, run it against your test case and make sure it works. if so, we can pass it up the chain of command (hopefully as context diff). I went ahead and tested andrew's second patch -- can we get this reviewed and committed? 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] WALInsertLock contention
On Wed, Jun 8, 2011 at 10:18 AM, Merlin Moncure mmonc...@gmail.com wrote: One point i'm missing though. Getting back to your original idea, how does writing to shmem prevent you from having to keep buffers pinned? I'm reading your comment here: Those buffers are stamped with a fake LSN that points back to the per-backend WAL buffer, and they can't be written until the WAL has been moved from the per-backend WAL buffers to the main WAL buffers. That suggests to me that you have to keep them pinned anyways. I'm still a bit fuzzy on how the per-backend buffers being in shm conveys any advantage. IOW, (trying not to be obtuse) under what circumstances would backend A want to read from or (especially) write to backend B's wal buffer? If backend A needs to evict a buffer with a fake LSN, it can go find the WAL that needs to be serialized, do that, flush WAL, and then evict the buffer. IOW, backend A's private WAL buffer will not be completely private. Only A will write to the buffer, but we don't know who will remove WAL from the buffer and insert it into the main stream. -- 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] reindex creates predicate lock on index root
Kevin Grittner kevin.gritt...@wicourts.gov writes: *** a/src/backend/storage/lmgr/predicate.c --- b/src/backend/storage/lmgr/predicate.c *** *** 274,279 --- 274,280 #define SkipSerialization(relation) \ ((!IsolationIsSerializable()) \ || ((MySerializableXact == InvalidSerializableXact)) \ + || (!IsMVCCSnapshot(GetActiveSnapshot())) \ || ReleasePredicateLocksIfROSafe() \ || SkipPredicateLocksForRelation(relation)) While I agree with the goal here, this implementation seems fairly dangerous. The recommendation was to check *the snapshot being used in the scan*, and I think you have to do it that way. This macro isn't necessarily checking the right snapshot. What's more, if it's ever used in a place where there is no active snapshot, it'd dump core outright. I think you probably need to add the snapshot as an explicit parameter to the macro if you're going to do this. BTW, am I reading the function names right to suspect that ReleasePredicateLocksIfROSafe might be something with side-effects? Yuck. 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] reindex creates predicate lock on index root
On 08.06.2011 18:09, Kevin Grittner wrote: Robert Haasrobertmh...@gmail.com wrote: I'm wondering if this shouldn't be linked to whether the scan is using an MVCC snapshot, rather than inserting exceptions for specific operations. Yeah, that was raised before somewhere and I spaced it. Grabbing predicate locks for non-MVCC snapshots is nonsense, and the fix is a one-line addition to the SkipSerialization macro defined and used in predicate.c. Patch attached. I agree with your other post that changes which are in the nature of improving performance (which for SSI includes changes which reduce the number of false positive serialization failures for serializable transactions) should be viewed very cautiously in terms of 9.1 inclusion. We're already at a point where a DBT-2 benchmark only shows a 2% hit for SSI overhead, including transaction restarts for serialization failures. I'd love to get that down to 1% or lower, but I don't want to create any risk of introducing bugs in 9.1 at this point. I think this one-liner might be worth considering, since it is very low risk and fixes an undesirable behavior which some (Tom, at least, it would appear) would have no trouble categorizing as a bug. I also think this should be fixed. The attached patch has not yet been tested, but I'll test it today along with the latest committed code. You can't use GetActiveSnapshot() for this. You can have one snapshot pushed to the active snapshot stack, and do a DDL operation like reindex using a different snapshot. You'll have to check the snapshot in the HeapScanDesc. -- 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] Error in PQsetvalue
Merlin Moncure mmonc...@gmail.com writes: I went ahead and tested andrew's second patch -- can we get this reviewed and committed? Add it to the upcoming commitfest. 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] Autoanalyze and OldestXmin
On Jun 8, 2011 1:49 PM, Pavan Deolasee pavan.deola...@gmail.com wrote: Hi All, There is a PROC_IN_ANALYZE flag, but we don't seem to be using it anywhere. Since acquire_sample_rows() returns palloced tuples, can't we let OldestXmin advance after scanning a page by ignoring procs with the flag set, just like we do for PROC_IN_VACUUM ? I don't even think the pallocing of tuples is a necessary condition. The key requirement is that this process will not access any other tables in this snapshot. In which case we don't need to take it into account when vacuuming other tables. It's not safe to vacuum tuples from the table being analyzed because the vacuum could get ahead of the analyze. This is kind of like the other property it would be nice to know about transactions: that they've locked all the tables they're going to lock. That would be sufficient but overly strong test. It's possible to know that if other tables are accessed they'll be with a brand new snapshot.
Re: [HACKERS] SSI heap_insert and page-level predicate locks
Excerpts from Heikki Linnakangas's message of mié jun 08 05:45:35 -0400 2011: On 08.06.2011 12:36, Dan Ports wrote: The only thing I'd be worried about is that at some future point we might add heap page locks -- say, for sequential scans that don't read the entire relation -- and expect inserts to be tested against them. I'm not sure whether we'd actually do this, but we wanted to keep the option open during development. I think that is only relevant for queries like SELECT * FROM table WHERE ctid = '(12,34)'. You might expect that we take a lock on that physical part of the heap, so that an INSERT that falls on that slot would conflict, but one that falls elsewhere does not. At the moment, a TidScan only takes a predicate lock tuples that exist, it doesn't try to lock the range like an IndexScan would. The physical layout of the table is an implementation detail that the application shouldn't care about, so I don't feel sorry for anyone doing that. Maybe it's worth mentioning somewhere in the docs, though. What about UPDATE WHERE CURRENT OF? Also, people sometimes use CTID to eliminate duplicate rows. -- Álvaro Herrera alvhe...@commandprompt.com 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] reindex creates predicate lock on index root
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: The attached patch has not yet been tested, but I'll test it today along with the latest committed code. You can't use GetActiveSnapshot() for this. Yeah, it didn't take much testing to find that out. I had a naive assumption that the GetActiveSnapshot would return whatever snapshot was in use at the point of the call. You can have one snapshot pushed to the active snapshot stack, and do a DDL operation like reindex using a different snapshot. You'll have to check the snapshot in the HeapScanDesc. Will look at that. Do you think it makes more sense to pass in the snapshot on all these calls and test it within predicate.c, or condition the calls on this? -Kevin -- 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] reducing the overhead of frequent table locks - now, with WIP patch
On Jun 7, 2011, at 8:24 AM, Stephen Frost wrote: * Alvaro Herrera (alvhe...@commandprompt.com) wrote: I note that if 2nd Quadrant is interested in having a game-changing platform without having to wait a full year for 9.2, they can obviously distribute a modified version of Postgres that integrates Robert's patch. Having thought about this, I've got to agree with Alvaro on this one. The people who need this patch are likely to pull it down and patch it in and use it, regardless of if it's in a release or not. My money is that Treat's already got it running on some massive prod system that he supports ( ;) ). If we get it into the first CF of 9.2 then people are going to be even more likely to pull it down and back-patch it into 9.1. As soon as we wrap up CF1 and put out our first alpha, the performance testers will have something to point at and say look! PG scales *even better* now! and they're not going to particularly care that it's an alpha and the blog-o-sphere isn't going to either, especially if we can say and it'll be in the next release which is scheduled for May. From the Thinking Outside The Box dept.: Also, if the performance gains prove to be as earth-shattering as initial results indicate, there's nothing that says we *have* to wait until the middle of next year to get this out. We could push to get 9.2 out with fewer other features, or possibly even break with tradition and backport this to 9.1 (or perhaps have a fork of 9.1 that we only support until 9.2 is out). Obviously, those options all involve serious time commitments and the community will have to weigh those carefully. And we'd have to have very strong evidence of the benefits before even having that discussion, because the discussion itself will likely be resource intensive. But the option *is* there, should we decide to pursue it. This means that this patch is too important to wait another 12 months isn't really a valid point: it only has to wait 12 months if thats what the community thinks is best; otherwise it could miss 9.1 *and* be out significantly before 12 months from now. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] reindex creates predicate lock on index root
Kevin Grittner kevin.gritt...@wicourts.gov writes: Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: You can have one snapshot pushed to the active snapshot stack, and do a DDL operation like reindex using a different snapshot. You'll have to check the snapshot in the HeapScanDesc. Will look at that. Do you think it makes more sense to pass in the snapshot on all these calls and test it within predicate.c, or condition the calls on this? I'd vote for passing in the snapshot. I can foresee wanting to know exactly which snapshot is in question in this code, 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] Error in PQsetvalue
On Wed, Jun 8, 2011 at 10:18 AM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: I went ahead and tested andrew's second patch -- can we get this reviewed and committed? Add it to the upcoming commitfest. It's a client crashing bug in PQsetvalue that goes back to 9.0 :(. In short (apologies for the non-context diff), PQsetvalue was inappropriately using libpq tuple slots as a check to see if it should allocate the per row tuple datums, and borked when setting values on a non copied result (libpqtypes always copies results) because the regular pqAddTuple does not null out the slots like copy result does. The whole mechanism was basically unnecessary, so it was removed, fixing the bug. 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] Range Types and extensions
On Tue, 2011-06-07 at 10:20 -0700, Jeff Davis wrote:
BTW, Jeff, have you worked out the implications of collations for
textual range types?
Well, it seems to work is about as far as I've gotten.
As far as the implications, I'll need to do a little more research and
thinking. But I don't immediately see anything too worrisome.
I take that back :(
It looks like the type input function may be a problem, because it
doesn't look like it knows what the collation is yet. In other words,
PG_GET_COLLATION() is zero for the type input function.
But I need to do a comparison to find out if the range is valid or not.
For instance:
'[a, Z)'::textrange
is valid in en_US but not C.
The range constructor:
range('a', 'Z')
is fine though.
Not sure what to do here.
Regards,
Jeff Davis
--
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 heap_insert and page-level predicate locks
Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Heikki Linnakangas's message of mié jun 08 05:45:35 -0400 2011: On 08.06.2011 12:36, Dan Ports wrote: The only thing I'd be worried about is that at some future point we might add heap page locks -- say, for sequential scans that don't read the entire relation -- and expect inserts to be tested against them. I'm not sure whether we'd actually do this, but we wanted to keep the option open during development. I think that is only relevant for queries like SELECT * FROM table WHERE ctid = '(12,34)'. You might expect that we take a lock on that physical part of the heap, so that an INSERT that falls on that slot would conflict, but one that falls elsewhere does not. At the moment, a TidScan only takes a predicate lock tuples that exist, it doesn't try to lock the range like an IndexScan would. The physical layout of the table is an implementation detail that the application shouldn't care about, so I don't feel sorry for anyone doing that. Maybe it's worth mentioning somewhere in the docs, though. Agreed. I'll add it to my list. What about UPDATE WHERE CURRENT OF? That doesn't currently lock anything except rows actually read, does it? If not, that has no bearing on the suggested change. Also, any row read through any *other* means during the same transaction would already have a predicate lock which would cover the tuple, so any case where you read the TID from a tuple and then use that to re-visit the tuple during the same transaction would not be affected. We're talking about whether it makes any sense to blindly read a TID, and if it's not found, to remember the fact that that particular TID *wasn't* there, and generate a rw-conflict if an overlapping transaction does something which *creates* a tuple with that TID. That does seem to be an unlikely use case. If we decide not to support SSI conflict detection in that usage, we can save some CPU time, reduce LW lock contention, and reduce the false positive rate for serialization failures. Also, people sometimes use CTID to eliminate duplicate rows. Again, I presume that if they want transactional behavior on that, they would read the duplicates and do the delete within the same transaction? If so there's no chance of a problem. If not, we're talking about wanting to create a rw-conflict with an overlapping transaction which reused the same TID, which I'm not sure is even possible, or that it makes sense to care about the TID matching anyway. -Kevin -- 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] Error in PQsetvalue
Merlin Moncure mmonc...@gmail.com writes: On Wed, Jun 8, 2011 at 10:18 AM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: I went ahead and tested andrew's second patch -- can we get this reviewed and committed? Add it to the upcoming commitfest. It's a client crashing bug in PQsetvalue that goes back to 9.0 :(. I was under the impression that this was extending PQsetvalue to let it be used in previously unsupported ways, ie, to modify a server-returned PGresult. That's a feature addition, not a bug fix. I'm not even sure it's a feature addition I approve of. I think serious consideration ought to be given to locking down returned results so PQsetvalue refuses to touch them, instead. Otherwise we're likely to find ourselves unable to make future optimizations because we have to support this barely-used-by-anybody corner case. 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] Range Types and extensions
Jeff Davis pg...@j-davis.com writes: It looks like the type input function may be a problem, because it doesn't look like it knows what the collation is yet. In other words, PG_GET_COLLATION() is zero for the type input function. Yeah, we've assumed that I/O functions do not need to know collation. 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] reindex creates predicate lock on index root
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: *** a/src/backend/storage/lmgr/predicate.c --- b/src/backend/storage/lmgr/predicate.c *** *** 274,279 --- 274,280 #define SkipSerialization(relation) \ ((!IsolationIsSerializable()) \ || ((MySerializableXact == InvalidSerializableXact)) \ +|| (!IsMVCCSnapshot(GetActiveSnapshot())) \ || ReleasePredicateLocksIfROSafe() \ || SkipPredicateLocksForRelation(relation)) BTW, am I reading the function names right to suspect that ReleasePredicateLocksIfROSafe might be something with side-effects? Yuck. I'm open to other suggestions on this. Let me explain what's up here. A READ ONLY transaction can have a safe snapshot, in which case it doesn't need to acquire predicate locks or participate in dangerous structure detection. We check for this at transaction startup and start the transaction with MySerializableXact set to InvalidSerializableXact in that case, so it would never make it past that test. We also have the new DEFERRABLE transaction property which causes transaction startup to *wait* for a safe snapshot. (SIREAD locks never block anything; this request for a SERIALIZABLE READ ONLY DEFERRABLE transaction is the only blocking introduced in SSI.) But a READ ONLY transaction's snapshot can *become* safe while it is running. We felt it was worthwhile to watch for this and flag the transaction as safe, to minimize predicate lock space used, CPU consumed, LW lock contention, and false positive serialization failures. While this is actually detected, and the transaction flagged as safe, during commit or rollback of a READ WRITE transaction, proper cleanup can only be done (at least without a lot of new mechanism and locking) by the now-safe transaction's process. The points where it makes sense to check this and do the cleanup correspond exactly to the places where this macro is called. We could take ReleasePredicateLocksIfROSafe() out of the SkipSerialization(relation) macro, but we'd need to ensure that these macros are always called as a pair, and even then we wouldn't be calling it in the place where it makes the most sense. So, while this construction does make one squirm a little, it seems a sane accommodation to reality. If anyone can spot a cleaner way to do it, that'd be great. -Kevin -- 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] reducing the overhead of frequent table locks - now, with WIP patch
On Wed, Jun 8, 2011 at 5:19 AM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Mon, Jun 6, 2011 at 10:49 AM, Simon Riggs si...@2ndquadrant.com wrote: My point was that we have in the past implemented performance changes to increase scalability at the last minute, and also that our personal risk perspectives are not always set in stone. Robert has highlighted the value of this change and its clearly not beyond our wit to include it, even if it is beyond our will to do so. So, at the risk of totally derailing this thread -- what this boils down to is a philosophical disagreement. It seems to me (and, I think, to Tom and Heikki and others as well) that it's not possible to keep on making changes to the release right up until the last minute and then expect the release to be of high quality. If we keep committing new features, then we'll keep introducing new bugs. The only hope of making the bug count go down at some point is to stop making changes that aren't bug fixes. We could come up with some complex procedure for determining whether a patch is important enough and non-invasive enough to bypass the normal deadline, but that would probably lead to a lot more arguing about procedure, and realistically, it's still going to increase the bug count at least somewhat. IMHO, it's better to just have a deadline, and stuff either makes it or it doesn't. I realize we haven't always adhered to the principle in the past, but at least IMV that's not a mistake we want to continue repeating. Simon is right that we slipped the vxid patch into 8.3 when a Postgres user I talked to at Linuxworld mentioned high vacuum freeze activity and simple calculations showed the many read-only queries could cause high xid usage. Fortunately we already had a patch available and Tom applied it during beta. It was an existing patch that took on new urgency during beta. Robert's point above is that it isn't so much making the decision of whether something should slip past the deadline, but the time-sapping discussion of whether something should slip, and the frankly disturbing behavior of some in this group to not accept a clear consensus, therefore prolonging the discussion of slippage far longer than necessary. Basically, if you propose something, and it gets shot down due to procedure, accept that unless you have some very good _new_ reason for continuing the discussion. If you don't like that, then you are not going to do well in our group and maybe this isn't the group for you. I think we are going to need to be much more forceful about this, and if the threat that someone has commit rights and therefore we can't ignore them, we will have to reconsider who can commit to this project. Do I need to be any clearer? You are very clear, but as to why, I am not sure. On Monday, realising that Robert had discovered something of massive potential benefit to the community, I asked Tom to take a look at the patch to see if I could get his interest in including it in this release. I did that out of pure altruism; how could I possibly benefit from highlighting the work of another person, another company? Tom has agreed with me that making tuning proposals during beta is acceptable. In this case, he thinks it is too risky to apply. In fact, I agreed, having reviewed the patch myself, suggesting a much simpler, non-invasive patch instead (a new reason, as you say). I then immediately accepted his decision to exclude any patch involving locking from further consideration. Given the level of potential benefit, I don't have a problem tapping Tom on the shoulder to review it and see if it is tweakable. At no point have I discussed applying the patch myself, nor have I ever even considered it. The main point is that in his hands a task can be done in days, not the months others have quoted. You can read that as respect and optimism, or you can see chaos and disrespect, but that is all in the eye of the beholder. As a result of this, I've been insulted, told I have no respect for process and even suggested there was a threat of patch war. None of that is reasonable or anywhere close to truth. If there has been a time sapping discussion, it is because people have jumped to conclusions and responded irrationally. To be honest, I'm completely surprised by all of that. I had no idea that me asking Tom a question was perceived as a denial of service attack on the community, nor that it would result in the comments made to me and about me. As long as I am allowed the freedom to speak in this forum then I will speak up for PostgreSQL users, committer or not. As long as I'm a committer, I will take responsibility for the code and seek to improve it and fix it according to the community process. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list
Re: [HACKERS] [Pgbuildfarm-members] CREATE FUNCTION hang on test machine polecat on HEAD
On Tue, Jun 7, 2011 at 12:42, Alex Hunsaker bada...@gmail.com wrote: On Tue, Jun 7, 2011 at 12:22, Tom Lane t...@sss.pgh.pa.us wrote: Alex Hunsaker bada...@gmail.com writes: Im looking at the raw perl 5.10.0 source... I wonder if apple is shipping a modified version? You could find out by digging around at http://www.opensource.apple.com/ polecat appears to be running OSX 10.6.7, so this is what you want: http://www.opensource.apple.com/tarballs/perl/perl-63.tar.gz Thanks! Hrm they don't seem to touch util.c where PL_get_hash_seed lives at all :-(. I also looked at 5.8.9 and Perl_get_hash_seed looks the same as in 5.10.0. -- 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] reducing the overhead of frequent table locks - now, with WIP patch
On Wed, Jun 8, 2011 at 11:39 AM, Jim Nasby j...@nasby.net wrote: On Jun 7, 2011, at 8:24 AM, Stephen Frost wrote: * Alvaro Herrera (alvhe...@commandprompt.com) wrote: I note that if 2nd Quadrant is interested in having a game-changing platform without having to wait a full year for 9.2, they can obviously distribute a modified version of Postgres that integrates Robert's patch. Having thought about this, I've got to agree with Alvaro on this one. The people who need this patch are likely to pull it down and patch it in and use it, regardless of if it's in a release or not. My money is that Treat's already got it running on some massive prod system that he supports ( ;) ). If we get it into the first CF of 9.2 then people are going to be even more likely to pull it down and back-patch it into 9.1. As soon as we wrap up CF1 and put out our first alpha, the performance testers will have something to point at and say look! PG scales *even better* now! and they're not going to particularly care that it's an alpha and the blog-o-sphere isn't going to either, especially if we can say and it'll be in the next release which is scheduled for May. From the Thinking Outside The Box dept.: Also, if the performance gains prove to be as earth-shattering as initial results indicate, there's nothing that says we *have* to wait until the middle of next year to get this out. We could push to get 9.2 out with fewer other features, or possibly even break with tradition and backport this to 9.1 (or perhaps have a fork of 9.1 that we only support until 9.2 is out). Obviously, those options all involve serious time commitments and the community will have to weigh those carefully. And we'd have to have very strong evidence of the benefits before even having that discussion, because the discussion itself will likely be resource intensive. But the option *is* there, should we decide to pursue it. This means that this patch is too important to wait another 12 months isn't really a valid point: it only has to wait 12 months if thats what the community thinks is best; otherwise it could miss 9.1 *and* be out significantly before 12 months from now. Right. The community gets to decide when the community wants to release, and with what features. Right now, the consensus is that we want to finish up 9.1 and release it. It doesn't seem impossible that we could manage to do that before this patch is ready for commit, which is why I don't want to try to slip this into 9.1 no matter how valuable it is. I also feel that the fundamental thing we need in order to have better releases is more developers spending more time developing cool stuff. That is why I am somewhat dismayed to see this discussion veer off on what I consider to be a tangent about release scheduling. It took me about 3 days to write the patch. I've now spent the better part of a day on this scheduling discussion. I would rather have spent that time improving the patch. Or working on some other patch. Or getting 9.1 out the door. Now, mind you, I think release scheduling is important. I believe in the value of good project management. But if we make every cool patch that comes along into an opportunity to fight about the release schedule, that's not productive. Already, I feel that any hope I might have had of getting useful technical feedback on this patch anytime in the near future has been basically obliterated. What a bummer. As for the 9.2 schedule, I'm actually hoping that 9.2 will be a big release for performance, sorta like 8.3 was. I think that to make that happen, we're going to need more than one good patch. This patch can be part of that picture, but there are many users who derive no benefit or only a small benefit from it. Of course, there are some who will get a big benefit, and I'm as excited about that as everyone else, but if we can broaden the aperture a bit and come up with a variety of improvements that hit on a variety of use cases, then we'll really have something to brag about. -- 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] Error in PQsetvalue
On Wed, Jun 8, 2011 at 11:03 AM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: On Wed, Jun 8, 2011 at 10:18 AM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: I went ahead and tested andrew's second patch -- can we get this reviewed and committed? Add it to the upcoming commitfest. It's a client crashing bug in PQsetvalue that goes back to 9.0 :(. I was under the impression that this was extending PQsetvalue to let it be used in previously unsupported ways, ie, to modify a server-returned PGresult. That's a feature addition, not a bug fix. It's neither -- it's documented libpq behavior: The function will automatically grow the result's internal tuples array as needed. However, the tup_num argument must be less than or equal to PQntuples, meaning this function can only grow the tuples array one tuple at a time. But any field of any existing tuple can be modified in any order. Andrew was briefly flirting with a proposal to tweak this behavior, but withdrew the idea. it's a feature addition I approve of. I think serious consideration ought to be given to locking down returned results so PQsetvalue refuses to touch them, instead. Otherwise we're likely to find ourselves unable to make future optimizations because we have to support this barely-used-by-anybody corner case. I think that's debatable, but I'm not going to argue this yea or nea. But I will say that maybe we shouldn't confuse behavior issues with bug fix either way...patch the bug, and we can work up a patch to lock down the behavior and the docs if you want it that way, but maybe we could bikeshed a bit on that point. 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] reducing the overhead of frequent table locks - now, with WIP patch
On Wed, Jun 8, 2011 at 6:02 AM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: Simon is right that we slipped the vxid patch into 8.3 when a Postgres user I talked to at Linuxworld mentioned high vacuum freeze activity and simple calculations showed the many read-only queries could cause high xid usage. Fortunately we already had a patch available and Tom applied it during beta. It was an existing patch that took on new urgency during beta. Just to set the record straight on this ... the vxid patch went in on 2007-09-05: http://archives.postgresql.org/pgsql-committers/2007-09/msg00026.php which was a day shy of a month before we wrapped 8.3beta1: http://archives.postgresql.org/pgsql-committers/2007-10/msg00089.php so it was during alpha phase not beta. And 8.3RC1 was stamped on 2008-01-03. So Simon's assertion that this was days before we produced a release candidate is correct, if you take days as 4 months. The patch went in slightly more than 6 months after feature freeze, even though it was written by a summer student and did not even pass review by the student's mentor (me). The patch is invasive, involving core changes to the transaction infrastructure and touching the more than 30 files. It was a brilliant contribution from Florian. I take it as an example of * what you can do when you set your mind to it, given sufficient cause and a good starting point * how people can propose things of value to the community even at a late stage * how I have respected the process at other times -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] reducing the overhead of frequent table locks - now, with WIP patch
On Wed, Jun 8, 2011 at 5:33 AM, Bruce Momjian br...@momjian.us wrote: One more thing --- when Tom applied that patch during 8.3 beta it was with everyone's agreement, so the policy should be that if we are going to break the rules, everyone has to agree --- if anyone disagrees, the rules stand. I spoke against applying the patch, and to my knowledge was the only person to have reviewed it at that stage. I was happy that Tom applied it, but I would not have done so myself then, nor would I do so now. I would trust only Tom to do that, which is why I proposed to Tom that he look at Robert's patch. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] reducing the overhead of frequent table locks - now, with WIP patch
On Wed, Jun 8, 2011 at 12:25 PM, Simon Riggs si...@2ndquadrant.com wrote: As a result of this, I've been insulted, told I have no respect for process and even suggested there was a threat of patch war. Well, you've pretty much said flat out you don't like the process, and you don't agree with having a firm feature freeze. I think it's a perfectly legitimate question to ask whether we're going to have to continually relitigate that point. This is at least the second major dust-up on this point since the end of 9.1CF4, and there were some smaller ones, too. -- 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] Error in PQsetvalue
On 6/8/2011 12:03 PM, Tom Lane wrote: Merlin Moncuremmonc...@gmail.com writes: On Wed, Jun 8, 2011 at 10:18 AM, Tom Lanet...@sss.pgh.pa.us wrote: Merlin Moncuremmonc...@gmail.com writes: I went ahead and tested andrew's second patch -- can we get this reviewed and committed? Add it to the upcoming commitfest. It's a client crashing bug in PQsetvalue that goes back to 9.0 :(. I was under the impression that this was extending PQsetvalue to let it be used in previously unsupported ways, ie, to modify a server-returned Well, it was supposed to support that but the implementation is busted (sorry) and core dumps instead. PGresult. That's a feature addition, not a bug fix. I'm not even sure it's a feature addition I approve of. I think serious consideration ought to be given to locking down returned results so PQsetvalue refuses I don't disagree at all, but I do think this is a bit more involved of a change. Several functions like PQmakeEmptyPGresult, PQsetvalue, PQcopyResult (one used by libpqtypes), the PGresult object needs a bool member and probably others things all must be aware of the distinction bwteen client and server generated results. That is a little tricky because both use PQmakeEmptyPGresult that has no argument to indicate that. Since libpqtypes only calls PQcopyResult, you could just set a flag on the result in that function that PQsetvalue uses as a guard. However, this hard codes knowledge about the libpqtypes calling pattern which is rather weak. Maybe it would be better to just apply the patch (since it also removes duplicate code from pqAddTuple in addition to fixing a crash) and update the docs to say this is an unsupported feature, don't do it. If it happens to work forever or just for a while, than so be it. -- Andrew Chernow eSilo, LLC global backup http://www.esilo.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] reducing the overhead of frequent table locks - now, with WIP patch
On Wed, Jun 8, 2011 at 5:32 PM, Robert Haas robertmh...@gmail.com wrote: It took me about 3 days to write the patch. I've now spent the better part of a day on this scheduling discussion. I would rather have spent that time improving the patch. Or working on some other patch. Or getting 9.1 out the door. Sync Rep took 6 days to write initially and about 6 months to discuss it, so you have a long way to go before your experience matches mine. Sometimes people side track you onto things you think are pointless, and sometimes you voice the opinion that they shouldn't have done so. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Domains versus polymorphic functions, redux
I wrote: Anyway, I think we're out of time to do anything about the issue for 9.1. I think what we'd better do is force a downcast in the context of matching to an ANYARRAY parameter, and leave the other cases to revisit later. Attached is a draft patch to do the above. It's only lightly tested, and could use some regression test additions, but it seems to fix Regina's complaint. Applied with regression-test additions. 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] Autoanalyze and OldestXmin
On Wed, Jun 8, 2011 at 9:03 PM, Greg Stark st...@mit.edu wrote: On Jun 8, 2011 1:49 PM, Pavan Deolasee pavan.deola...@gmail.com wrote: Hi All, There is a PROC_IN_ANALYZE flag, but we don't seem to be using it anywhere. Since acquire_sample_rows() returns palloced tuples, can't we let OldestXmin advance after scanning a page by ignoring procs with the flag set, just like we do for PROC_IN_VACUUM ? I don't even think the pallocing of tuples is a necessary condition. The key requirement is that this process will not access any other tables in this snapshot. In which case we don't need to take it into account when vacuuming other tables. I first thought that analyze and vacuum can not run concurrently on the same table since they take a conflicting lock on the table. So even if we ignore the analyze process while calculating the OldestXmin for vacuum, we should be fine since we know they are working on different tables. But I see analyze also acquires sample rows from the inherited tables with a non-conflicting lock. I probably do not understand the analyze code well, but is that the reason why we can't ignore analyze snapshot while determining OldestXmin for vacuum ? It's not safe to vacuum tuples from the table being analyzed because the vacuum could get ahead of the analyze. What can go wrong if that happens ? Is the worry that we might get stale analyze result or are there more serious issues to deal with ? This is kind of like the other property it would be nice to know about transactions: that they've locked all the tables they're going to lock. That would be sufficient but overly strong test. It's possible to know that if other tables are accessed they'll be with a brand new snapshot. I definitely do that understand this :-) Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
[HACKERS] [PATCH] Allow star syntax in GROUP BY, as a shorthand for all table columns
Hi,
This patch enables the syntax GROUP BY tablename.* in cases where
earlier you'd get the error field must appear in the GROUP BY clause
or be used in an aggregate function
I've often needed to write queries like this:
SELECT a.x, a.y, a.z, sum(b.w) FROM a JOIN b USING (a_id) GROUP BY
a.x, a.y, a.z;
Now this becomes:
SELECT a.x, a.y, a.z, sum(b.w) FROM a JOIN b USING (a_id) GROUP BY a.*;
The patch is so trivial that I'm wondering why it hasn't been
implemented before. I couldn't think of any assumptions being broken
by using row comparison instead of comparing each field separately.
But maybe I'm missing something.
If this patch looks reasonable, I guess the obvious next step is to
expand the a.* reference to the table's primary key columns and fill
in context-func_grouped_rels
Regards,
Marti
From 36aa45fddae0623db4049484ac75533901bc69c9 Mon Sep 17 00:00:00 2001
From: Marti Raudsepp ma...@juffo.org
Date: Wed, 8 Jun 2011 19:21:16 +0300
Subject: [PATCH] Allow star syntax in GROUP BY, as a shorthand for all table
columns
Marti Raudsepp
---
src/backend/catalog/pg_constraint.c |2 +-
src/backend/parser/parse_agg.c |2 +-
src/test/regress/expected/aggregates.out | 21 +
src/test/regress/sql/aggregates.sql |6 ++
4 files changed, 29 insertions(+), 2 deletions(-)
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 6997994..105e724 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -875,7 +875,7 @@ check_functional_grouping(Oid relid,
if (IsA(gvar, Var)
gvar-varno == varno
gvar-varlevelsup == varlevelsup
- gvar-varattno == attnum)
+ (gvar-varattno == attnum || gvar-varattno == 0))
{
found_col = true;
break;
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 8356133..c75edab 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -623,7 +623,7 @@ check_ungrouped_columns_walker(Node *node,
if (IsA(gvar, Var)
gvar-varno == var-varno
- gvar-varattno == var-varattno
+ (gvar-varattno == var-varattno || gvar-varattno == 0)
gvar-varlevelsup == 0)
return false; /* acceptable, we're okay */
}
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 4861006..418edc0 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1061,3 +1061,24 @@ select string_agg(distinct f1::text, ',' order by f1::text) from varchar_tbl; -
a,ab,abcd
(1 row)
+-- test GROUP BY using table/star syntax
+select sin(b), * from aggtest group by aggtest.*;
+sin | a |b
++-+-
+ 0.0954644005855398 | 0 | 0.09561
+ -0.93056589608634 | 42 | 324.78
+ 0.998543355665767 | 56 | 7.8
+ -0.990653162615688 | 100 | 99.097
+(4 rows)
+
+select count(*), t1.* from aggtest t1 cross join aggtest t2 group by t1;
+ count | a |b
+---+-+-
+ 4 | 0 | 0.09561
+ 4 | 42 | 324.78
+ 4 | 56 | 7.8
+ 4 | 100 | 99.097
+(4 rows)
+
+select * from aggtest t1 cross join aggtest t2 group by t1.*;
+ERROR: column t2.a must appear in the GROUP BY clause or be used in an aggregate function
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 04ec67b..dc59750 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -416,3 +416,9 @@ select string_agg(distinct f1, ',' order by f1) from varchar_tbl; -- ok
select string_agg(distinct f1::text, ',' order by f1) from varchar_tbl; -- not ok
select string_agg(distinct f1, ',' order by f1::text) from varchar_tbl; -- not ok
select string_agg(distinct f1::text, ',' order by f1::text) from varchar_tbl; -- ok
+
+-- test GROUP BY using table/star syntax
+select sin(b), * from aggtest group by aggtest.*;
+select count(*), t1.* from aggtest t1 cross join aggtest t2 group by t1;
+select * from aggtest t1 cross join aggtest t2 group by t1.*;
+
--
1.7.5.4
--
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] Autoanalyze and OldestXmin
Pavan Deolasee pavan.deola...@gmail.com writes: I first thought that analyze and vacuum can not run concurrently on the same table since they take a conflicting lock on the table. So even if we ignore the analyze process while calculating the OldestXmin for vacuum, we should be fine since we know they are working on different tables. But I see analyze also acquires sample rows from the inherited tables with a non-conflicting lock. I probably do not understand the analyze code well, but is that the reason why we can't ignore analyze snapshot while determining OldestXmin for vacuum ? The reason why we can't ignore that snapshot is that it's being set for the use of user-defined functions, which might do practically anything. They definitely could access tables other than the one under analysis. (I believe that PostGIS does such things, for example --- it wants to look at its auxiliary tables for metadata.) Also keep in mind that we allow ANALYZE to be run inside a transaction block, which might contain other operations sharing the same snapshot. 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] reducing the overhead of frequent table locks - now, with WIP patch
On Wed, Jun 8, 2011 at 5:44 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jun 8, 2011 at 12:25 PM, Simon Riggs si...@2ndquadrant.com wrote: As a result of this, I've been insulted, told I have no respect for process and even suggested there was a threat of patch war. Well, you've pretty much said flat out you don't like the process, and you don't agree with having a firm feature freeze. I think it's a perfectly legitimate question to ask whether we're going to have to continually relitigate that point. This is at least the second major dust-up on this point since the end of 9.1CF4, and there were some smaller ones, too. Why do you address this to me? Many others have been committing patches against raised issues well after feature freeze. You do not wish to stop all patches, only those you disagree with. How would I know you disagree with a patch without discussing it? I note that you've claimed *everything* I have discussed is a new feature, whereas everything you or others have done is an open item. You can claim that everything I suggest is a dust-up if you wish, but who makes it a dust up and why? The point I have made is that I disagree with a feature freeze date fixed ahead of time without regard to the content of the forthcoming release. I've not said I disagree with feature freezes altogether, which would be utterly ridiculous. Fixed dates are IMHO much less important than a sensible and useful feature set for our users. MySQL repeatedly delivered releases with half-finished features and earned much disrespect. We have never done that previously and I am against doing so in the future. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] [PATCH] Allow star syntax in GROUP BY, as a shorthand for all table columns
Marti Raudsepp ma...@juffo.org writes: This patch enables the syntax GROUP BY tablename.* in cases where earlier you'd get the error field must appear in the GROUP BY clause or be used in an aggregate function Is this really necessary now that we know about GROUP BY primary key? The patch is so trivial that I'm wondering why it hasn't been implemented before. Probably because it's a nonstandard kluge ... 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] reducing the overhead of frequent table locks - now, with WIP patch
Simon, The point I have made is that I disagree with a feature freeze date fixed ahead of time without regard to the content of the forthcoming release. I've not said I disagree with feature freezes altogether, which would be utterly ridiculous. Fixed dates are IMHO much less important than a sensible and useful feature set for our users. This is such a non-argument it's silly. We have so many new major features for 9.1 that I'm having trouble writing sensible press releases which don't sound like a laundry list. MySQL repeatedly delivered releases with half-finished features and earned much disrespect. We have never done that previously and I am against doing so in the future. This is also total BS. I worked on the MySQL team. Before Sun/Oracle, MySQL specifically had feature-driven releases, where Marketing decided what features 5.0, 5.1 and 5.2 would have. They also accepted new features during beta if Marketing liked them enough. This resulted in the 5.1 release being *three years late*, and 5.3 being cancelled altogether. And let's talk about the legendary instability of 5.0, because they decided that they couldn't cancel partitioning and stored procedures, whether they were ready for prime time or not and because they kept changing the API during beta. MySQL never had time-based releases before Oracle took them over. And Oracle has been having feature-free releases because they're trying to work through MySQL's list of thousands of unfixed bugs which dates back to 2003. An argument for feature-driven releases is in fact an argument for the MySQL AB development model. And that's not a company I want to emulate. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com San Francisco -- 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] Postmaster holding unlinked files for pg_largeobject table
Excerpts from Tom Lane's message of mar jun 07 12:26:34 -0400 2011: It's not *that* many levels: in fact, I think md.c is the only level that would just have to pass it through without doing anything useful. I think that working from there is a saner and more efficient approach than what you're sketching. If you want a concrete design sketch, consider this: Okay, here's a patch implementing this idea. It seems to work quite well, and it solves the problem in a limited testing scenario -- I haven't yet tested on the customer machines. This customer is running on 8.4 so I started from there; should I backpatch this to 8.2, or not at all? (I have all the branches ready anyway.) -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support smgr-transient-files.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] reducing the overhead of frequent table locks - now, with WIP patch
On 06/07/2011 11:55 AM, Tom Lane wrote: Simon Riggssi...@2ndquadrant.com writes: Before you arrived, it was quite normal to suggest tuning patches after feature freeze. *Low risk* tuning patches make sense at this stage, yes. Fooling with the lock mechanisms doesn't qualify as low risk in my book. The probability of undetected subtle problems is just too great. regards, tom lane I would like to see us continue on the path of release not destabilization. Any patch that breaks into core feature mechanisms (like locking) is bound to have something unsuspecting in the wings. +1 for submitting for 9.2. +1 for not comitting to 9.1. Sincerely, Joshua D. Drake -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] gcc 4.6 and hot standby
So I've been delaying moving some production boxes over to 9.0.4 from 9.0.2 because hot standby fails with: (this is on the hot standby machine that connects to the master) 2011-06-08 11:40:48 MDT [6072]: [2-1] user= LOG: entering standby mode 2011-06-08 11:40:48 MDT [6072]: [3-1] user= DEBUG: checkpoint record is at 86/E5D725F0 2011-06-08 11:40:48 MDT [6072]: [4-1] user= DEBUG: redo record is at 86/E39E8248; shutdown FALSE 2011-06-08 11:40:48 MDT [6072]: [5-1] user= DEBUG: next transaction ID: 0/35456371; next OID: 34090526 2011-06-08 11:40:48 MDT [6072]: [6-1] user= DEBUG: next MultiXactId: 523; next MultiXactOffset: 1046 2011-06-08 11:40:48 MDT [6072]: [7-1] user= DEBUG: oldest unfrozen transaction ID: 654, in database 1 2011-06-08 11:40:48 MDT [6072]: [8-1] user= DEBUG: transaction ID wrap limit is 2147484301, limited by database with OID 1 2011-06-08 11:40:48 MDT [6072]: [9-1] user= DEBUG: initializing for hot standby 2011-06-08 11:40:48 MDT [6072]: [10-1] user= LOG: redo starts at 86/E39E8248 2011-06-08 11:40:48 MDT [6072]: [11-1] user= LOG: invalid record length at 86/E39F2010 2011-06-08 11:40:48 MDT [6074]: [1-1] user= LOG: streaming replication successfully connected to primary 2011-06-08 11:40:49 MDT [6072]: [12-1] user= LOG: invalid record length at 86/E3A16010 2011-06-08 11:40:49 MDT [6074]: [2-1] user= FATAL: terminating walreceiver process due to administrator command 2011-06-08 11:40:49 MDT [6072]: [13-1] user= LOG: invalid record length at 86/E3A3C010 2011-06-08 11:40:53 MDT [6072]: [14-1] user= LOG: invalid record length at 86/E3A54010 2011-06-08 11:40:53 MDT [6075]: [1-1] user= FATAL: terminating walreceiver process due to administrator command 2011-06-08 11:40:53 MDT [6072]: [15-1] user= LOG: invalid record length at 86/E3A74010 2011-06-08 11:40:58 MDT [6076]: [1-1] user= LOG: streaming replication successfully connected to primary 2011-06-08 11:40:59 MDT [6072]: [16-1] user= LOG: invalid record length at 86/E3AC6010 2011-06-08 11:40:59 MDT [6076]: [2-1] user= FATAL: terminating walreceiver process due to administrator command 2011-06-08 11:40:59 MDT [6072]: [17-1] user= LOG: invalid record length at 86/E3ACC010 2011-06-08 11:41:03 MDT [6072]: [18-1] user= LOG: invalid record length at 86/E3B32010 2011-06-08 11:41:03 MDT [6078]: [1-1] user= FATAL: terminating walreceiver process due to administrator command [ repeats... ] Originally I thought there might be some corner case bug in 9.0.3 or 9.0.4. However after recompiling 9.0.2 with gcc 4.6 and hitting the same problem-- I tried compiling 9.0.4 with gcc 4.5 and it seemed to work great. I then tired various optimization levels on 4.6: -O0: works -O1: works -O2: fails -Os: works I suppose the next step is to narrow it down to a specific flag -O2 uses... But I thought I would post here first-- maybe someone else has hit this? Or maybe someone has a bright idea on how to narrow this down? # linux 2.6.39.1 x86_64 AMD opteron box $ gcc -v Using built-in specs. COLLECT_GCC=gcc COLLECT_LTO_WRAPPER=/usr/lib/gcc/x86_64-unknown-linux-gnu/4.6.0/lto-wrapper Target: x86_64-unknown-linux-gnu Configured with: /build/src/gcc-4.6-20110603/configure --prefix=/usr --libdir=/usr/lib --libexecdir=/usr/lib --mandir=/usr/share/man --infodir=/usr/share/info --with-bugurl=https://bugs.archlinux.org/ --enable-languages=c,c++,ada,fortran,go,lto,objc,obj-c++ --enable-shared --enable-threads=posix --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-clocale=gnu --enable-gnu-unique-object --enable-linker-build-id --with-ppl --enable-cloog-backend=isl --enable-lto --enable-gold --enable-ld=default --enable-plugin --with-plugin-ld=ld.gold --disable-multilib --disable-libstdcxx-pch --enable-checking=release Thread model: posix gcc version 4.6.0 20110603 (prerelease) (GCC) -- 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] reducing the overhead of frequent table locks - now, with WIP patch
On Wed, Jun 8, 2011 at 1:10 PM, Simon Riggs si...@2ndquadrant.com wrote: Why do you address this to me? Many others have been committing patches against raised issues well after feature freeze. No one other than you has proposed committing anything nearly as invasive as this, and the great majority of what we've committed has been targeted at new regressions in 9.1. There is a difference between a feature and a bug fix. Sometimes the distinction is arguable, but this isn't one of those cases. A feature freeze does not mean an absolute code freeze; it means a freeze on *features*. You do not wish to stop all patches, only those you disagree with. How would I know you disagree with a patch without discussing it? I note that you've claimed *everything* I have discussed is a new feature, whereas everything you or others have done is an open item. You can claim that everything I suggest is a dust-up if you wish, but who makes it a dust up and why? I think the people, including me, who feel that it's not a good idea to commit new features have been very clear about the reasons for their position - namely, (1) the desire to get the release out the door in a timely fashion, and (2) the desire to treat everyone's patches in a fair and even-handed way rather than privileging some over others. I'm just as much against committing my own features, or Tom's features, or Alvaro's features as I am against committing your features - not because I don't like the features (I do) but because I want to release 9.1 in about a month. The point I have made is that I disagree with a feature freeze date fixed ahead of time without regard to the content of the forthcoming release. I've not said I disagree with feature freezes altogether, which would be utterly ridiculous. Fixed dates are IMHO much less important than a sensible and useful feature set for our users. MySQL repeatedly delivered releases with half-finished features and earned much disrespect. We have never done that previously and I am against doing so in the future. So am I. But apparently, we have very different ideas of what that means. I thought that making the server shuts down properly, even if you are using sync rep was a clear-cut case of correcting a half-finished feature, but you argued against that change. And I think that revamping the locking mechanism so it's faster is clearly a new feature, not a repair to something half-finished. I don't expect it's very realistic to think that everyone is going to agree on every patch, but we can't agree that bug fixes and features should be treated differently, or if we can't agree at least in most cases on what the difference is between one and the other, then we will spend a lot of time talking past each other. -- 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] Postmaster holding unlinked files for pg_largeobject table
Alvaro Herrera alvhe...@commandprompt.com writes: Okay, here's a patch implementing this idea. It seems to work quite well, and it solves the problem in a limited testing scenario -- I haven't yet tested on the customer machines. This seems mostly sane, except I think you have not considered the issue of when to clear the smgr_transient flag on an existing SMgrRelation: if it starts getting used for normal accesses after having by chance been used for a blind write, we don't want the transient marking to persist. That's why I suggested having smgropen always clear it. Likewise, I think the FD_XACT_TRANSIENT flag on a VFD needs to go away at some point, probably once it's actually been closed at EOXACT, though there's doubtless more than one way to handle that. This customer is running on 8.4 so I started from there; should I backpatch this to 8.2, or not at all? I'm not excited about back-patching it... 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] Another pgindent run before beta2?
Should we consider doing $SUBJECT? Given the plan to branch off 9.1 after we make the beta release, I think it's now or never for a second pgindent run for 9.1. 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 pgindent run before beta2?
On Wed, Jun 8, 2011 at 2:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Should we consider doing $SUBJECT? Given the plan to branch off 9.1 after we make the beta release, I think it's now or never for a second pgindent run for 9.1. +1. -- 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] Another pgindent run before beta2?
On Wed, Jun 8, 2011 at 20:32, Robert Haas robertmh...@gmail.com wrote: On Wed, Jun 8, 2011 at 2:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Should we consider doing $SUBJECT? Given the plan to branch off 9.1 after we make the beta release, I think it's now or never for a second pgindent run for 9.1. +1. +1. -- 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
Re: [HACKERS] gcc 4.6 and hot standby
Alex Hunsaker bada...@gmail.com writes: So I've been delaying moving some production boxes over to 9.0.4 from 2011-06-08 11:41:03 MDT [6078]: [1-1] user= FATAL: terminating walreceiver process due to administrator command [ repeats... ] I suppose the next step is to narrow it down to a specific flag -O2 uses... But I thought I would post here first-- maybe someone else has hit this? Or maybe someone has a bright idea on how to narrow this down? Maybe using a prerelease gcc version isn't such a hot idea for production. It's very, very, very difficult to see how the behavior you describe isn't a compiler bug. (Well, I could also believe that something external is repeatedly hitting the walreceiver with a SIGTERM, but it's hard to square that with the behavior changing when you recompile with different -O levels ...) It might be useful to strace the postmaster and walreceiver processes just to see if any signal is actually being sent or received. 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] gcc 4.6 and hot standby
On Wed, Jun 8, 2011 at 12:12, Alex Hunsaker bada...@gmail.com wrote: So I've been delaying moving some production boxes over to 9.0.4 from 9.0.2 because hot standby fails with: (this is on the hot standby machine that connects to the master) [ ...] 2011-06-08 11:41:03 MDT [6072]: [18-1] user= LOG: invalid record length at 86/E3B32010 2011-06-08 11:41:03 MDT [6078]: [1-1] user= FATAL: terminating walreceiver process due to administrator command [ repeats... ] [...] I then tired various optimization levels on 4.6: -O0: works -O1: works -O2: fails -Os: works So I tracked it down to -fgcse, that is CFLAGS=-O2 -fno-gcse ./configure works. I then took a few guesses and compiled all of postgres with -O2, then manually recompiled xlog.c with -f-no-gcse. that combination seems to work. [ One thing im not sure is why -Os works, I tried -O2 and added all the -fno-XXX options it says -Os adds. I suppose its either they turn off/on other optimizations the man page does not mention, or I guess thats compiler bugs for ya ] -- 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] gcc 4.6 and hot standby
On Wed, Jun 8, 2011 at 12:49, Tom Lane t...@sss.pgh.pa.us wrote: Alex Hunsaker bada...@gmail.com writes: So I've been delaying moving some production boxes over to 9.0.4 from 2011-06-08 11:41:03 MDT [6078]: [1-1] user= FATAL: terminating walreceiver process due to administrator command [ repeats... ] I suppose the next step is to narrow it down to a specific flag -O2 uses... But I thought I would post here first-- maybe someone else has hit this? Or maybe someone has a bright idea on how to narrow this down? Maybe using a prerelease gcc version isn't such a hot idea for production. It's very, very, very difficult to see how the behavior you describe isn't a compiler bug. Yeah :-). However ill note it looks like its the default compiler for fedora 15, ubuntu natty and debian sid. It might be useful to strace the postmaster and walreceiver processes just to see if any signal is actually being sent or received. Will do. -- 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] gcc 4.6 and hot standby
Alex Hunsaker bada...@gmail.com writes: So I tracked it down to -fgcse, that is CFLAGS=-O2 -fno-gcse ./configure works. I then took a few guesses and compiled all of postgres with -O2, then manually recompiled xlog.c with -f-no-gcse. that combination seems to work. Huh, interesting. So the bug must be lurking somewhere around the logic that deals with failedSources: somehow we're getting to the ShutdownWalRcv call in XLogPageRead. 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] contrib/citext versus collations
I wrote: On further reflection, I'm wondering exactly how much goodness to chop off there. What I'd originally been thinking was to just lobotomize the case-folding step, and allow citext's comparison operators to still respond to input collation when comparing the folded strings. However, I can imagine that some combinations of languages might produce pretty weird results if we do that. Should we lobotomize the comparisons too? Or is the ability to affect the sort order valuable enough to put up with whatever corner-case funnies there might be? For lack of any comment on this point, I went with the first approach. Patch is committed. 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] could not truncate directory pg_serial: apparent wraparound
On 08.06.2011 03:28, Kevin Grittner wrote: We had a report of the subject message during testing a while back and attempted to address the issue. It can result in a LOG level message and the accumulation of files in the pg_serial SLRU subdirectory. We haven't seen a recurrence, until I hit it during testing of the just-posted patch for SSI DDL. I re-read the code and believe that the attached is the correct fix. Doesn't this patch bring back the issue mentioned in the comment: the slru page might not get used again until we wrap-around? -- 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] Another pgindent run before beta2?
On 06/08/2011 02:29 PM, Tom Lane wrote: Should we consider doing $SUBJECT? Given the plan to branch off 9.1 after we make the beta release, I think it's now or never for a second pgindent run for 9.1. OK, I have made sure the list on the buildfarm is up to date. 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] could not truncate directory pg_serial: apparent wraparound
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 08.06.2011 03:28, Kevin Grittner wrote: We had a report of the subject message during testing a while back and attempted to address the issue. It can result in a LOG level message and the accumulation of files in the pg_serial SLRU subdirectory. We haven't seen a recurrence, until I hit it during testing of the just-posted patch for SSI DDL. I re-read the code and believe that the attached is the correct fix. Doesn't this patch bring back the issue mentioned in the comment: the slru page might not get used again until we wrap-around? In the previous attempt I thought it was looking at the allocated files to assess that it was approaching wraparound. In looking at the SLRU code last night, it seemed that it was really using the last zeroed page as the comparison point, and wanted the truncation point to precede that. Given that last night didn't seem to be my sharpest hour, it would be wise to confirm this. This code is pretty hard to test, since it only comes into play on overflow of the predicate locking shared memory structures, so desk-checking is important. So, to directly address your question, if we don't overflow again and go to the SLRU summary data, one file could sit in the pg_serial directory indefinitely; but we should avoid the LOG message and the accumulation of large numbers of such files -- if I'm reading the SLRU code correctly -Kevin -- 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 for 9.2: enhanced errors
Hello
Attached patch implements a new erros's fields that describes table,
colums related to error. This enhanced info is limited to constraints
and RI.
example:
postgres=# create table omega(a int unique not null check (a 10));
NOTICE: 0: CREATE TABLE / UNIQUE will create implicit index
omega_a_key for table omega
LOCATION: DefineIndex, indexcmds.c:389
CREATE TABLE
Time: 106.867 ms
postgres=# \set VERBOSITY verbose
postgres=# insert into omega values(0);
ERROR: 23514: new row for relation omega violates check constraint
omega_a_check
LOCATION: ExecConstraints, execMain.c:1547
CONSTRAINT: omega_a_check
SCHEMA: public
TABLE: omega
COLUMNS: a
postgres=# insert into omega values(null);
ERROR: 23502: null value in column a violates not-null constraint
LOCATION: ExecConstraints, execMain.c:1519
CONSTRAINT: not_null_constraint
SCHEMA: public
TABLE: omega
COLUMNS: a
postgres=# insert into omega values(20);
INSERT 0 1
Time: 60.588 ms
postgres=# insert into omega values(20);
ERROR: 23505: duplicate key value violates unique constraint omega_a_key
DETAIL: Key (a)=(20) already exists.
LOCATION: _bt_check_unique, nbtinsert.c:432
CONSTRAINT: omega_a_key
SCHEMA: public
TABLE: omega
COLUMNS: a
postgres=#
This is base for support variables CONSTRAINT_NAME, SCHEMA_NAME and
TABLE_NAME for GET DIAGNOSTICS statement.
All regress tests was successfully passed
Regards
Pavel Stehule
*** ./src/backend/access/nbtree/nbtinsert.c.orig 2011-04-27 23:17:22.0 +0200
--- ./src/backend/access/nbtree/nbtinsert.c 2011-06-08 21:57:45.616691664 +0200
***
*** 23,28
--- 23,30
#include storage/lmgr.h
#include storage/predicate.h
#include utils/inval.h
+ #include utils/builtins.h
+ #include utils/syscache.h
#include utils/tqual.h
***
*** 83,88
--- 85,121
/*
+ * Returns a parent relation of index
+ */
+ Relation
+ pg_get_indrelation(Relation idxrel)
+ {
+ HeapTuple ht_idx;
+ Form_pg_index idxrec;
+ Oid indrelid;
+ Relation r;
+
+ /*
+ * Fetch the pg_index tuple by the Oid of the index
+ */
+ ht_idx = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(RelationGetRelid(idxrel)));
+ if (!HeapTupleIsValid(ht_idx))
+ elog(ERROR, cache lookup failed for index %u, RelationGetRelid(idxrel));
+ idxrec = (Form_pg_index) GETSTRUCT(ht_idx);
+
+ indrelid = idxrec-indrelid;
+ r = RelationIdGetRelation(indrelid);
+
+ if (!RelationIsValid(r))
+ elog(ERROR, could not open relation with OID %u, indrelid);
+
+ /* Clean up */
+ ReleaseSysCache(ht_idx);
+
+ return r;
+ }
+
+ /*
* _bt_doinsert() -- Handle insertion of a single index tuple in the tree.
*
* This routine is called by the public interface routines, btbuild
***
*** 394,400
RelationGetRelationName(rel)),
errdetail(Key %s already exists.,
BuildIndexValueDescription(rel,
! values, isnull;
}
}
else if (all_dead)
--- 427,436
RelationGetRelationName(rel)),
errdetail(Key %s already exists.,
BuildIndexValueDescription(rel,
! values, isnull)),
! errconstrname(RelationGetRelationName(rel)),
! errrel(pg_get_indrelation(rel)),
! errcolnames(pg_get_indexdef_columns(RelationGetRelid(rel), true;
}
}
else if (all_dead)
***
*** 534,540
RelationGetRelationName(rel)),
errhint(Values larger than 1/3 of a buffer page cannot be indexed.\n
Consider a function index of an MD5 hash of the value,
! or use full text indexing.)));
/*--
* If we will need to split the page to put the item on this page,
--- 570,578
RelationGetRelationName(rel)),
errhint(Values larger than 1/3 of a buffer page cannot be indexed.\n
Consider a function index of an MD5 hash of the value,
! or use full text indexing.),
! errconstrname(RelationGetRelationName(rel)),
! errrel(pg_get_indrelation(rel;
/*--
* If we will need to split the page to put the item on this page,
*** ./src/backend/executor/execMain.c.orig 2011-04-27 23:17:22.0 +0200
--- ./src/backend/executor/execMain.c 2011-06-08 18:51:19.492670762 +0200
***
*** 1433,1439
/*
* ExecRelCheck --- check that tuple meets constraints for result relation
*/
! static const char *
ExecRelCheck(ResultRelInfo *resultRelInfo,
TupleTableSlot *slot, EState *estate)
{
--- 1433,1439
/*
* ExecRelCheck --- check that tuple meets constraints for result relation
*/
! static ConstrCheck *
ExecRelCheck(ResultRelInfo *resultRelInfo,
TupleTableSlot *slot, EState *estate)
{
***
*** 1485,1491
* ExecQual to return TRUE for NULL.
*/
if (!ExecQual(qual, econtext, true))
! return check[i].ccname;
}
/* NULL result means no error */
--- 1485,1491
* ExecQual to
Re: [HACKERS] gcc 4.6 and hot standby
Alex Hunsaker bada...@gmail.com writes: On Wed, Jun 8, 2011 at 12:49, Tom Lane t...@sss.pgh.pa.us wrote: It might be useful to strace the postmaster and walreceiver processes just to see if any signal is actually being sent or received. Find it attached. Well, the trace shows exactly what I thought was happening: each time the startup process hits one of these: 2011-06-08 14:01:22 MDT [27781]: [12-1] user= LOG: invalid record length at 86/F4E82010 it sends a SIGTERM to kill the walreceiver, because it thinks this indicates a walreceiver problem. Then we launch another one and manage to process a few more WAL records, lather rinse repeat. So it's interesting that this only happens with a particular gcc version, because it's not apparent to me why it works properly for anybody. Isn't hitting a zero record length an expected case when we run ahead of the amount of WAL produced by the master? 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] tuning autovacuum
Hi, There are some releases that autovacuum was enabled by default and, up to now there is an easy way to estimate the number of autovacuum workers. I tune it observing if the number of slots are saturated for a period of time. I'm having a hard time trying to do this. I want to add a LOG message such as LOG: maximum number of autovacuum workers reached HINT: Consider increasing autovacuum_max_workers (currently 5). And also a view (say pg_stat_autovacuum) to expose some autovacuum information such as (i) number of autovacuum workers (ii) number of tables that needs analyze/vacuum and are scheduled to (iii) number of autovacuum count (iv) number of autoanalyze count. While I am in this topic, it would be nice to expose the analyze/vacuum count and threshold per table. This information should go to pg_stat_*_tables but it already has too much fields. Maybe it is time to split autovacuum information into another statistic view? Comments? -- 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] reducing the overhead of frequent table locks - now, with WIP patch
Simon Riggs si...@2ndquadrant.com writes: On Wed, Jun 8, 2011 at 6:02 AM, Tom Lane t...@sss.pgh.pa.us wrote: Just to set the record straight on this ... the vxid patch went in on 2007-09-05: http://archives.postgresql.org/pgsql-committers/2007-09/msg00026.php which was a day shy of a month before we wrapped 8.3beta1: http://archives.postgresql.org/pgsql-committers/2007-10/msg00089.php so it was during alpha phase not beta. And 8.3RC1 was stamped on 2008-01-03. So Simon's assertion that this was days before we produced a release candidate is correct, if you take days as 4 months. The patch went in slightly more than 6 months after feature freeze, even though it was written by a summer student and did not even pass review by the student's mentor (me). I'm not sure why you're having such a hard time distinguishing before beta from after beta, but in any case please notice that you're describing a cycle where we spent nine months in feature freeze. Nobody else here is going to hold that up as an example of sound project management that we ought to repeat. And the way to not repeat it is to not accept risky new patches late in the cycle. (This may be something of an apples-to-oranges comparison, though, since as best I can tell from a quick look in the archives, we were not then using the term feature freeze the same as we are now --- 2007-04-01 seems to have been the point that we would now call beginning of the last CF, ie, all feature patches for 8.3 were supposed to have been *submitted*, not necessarily committed. And we had a lot of them pending at that point, because of lack of the CF process to get things in earlier.) 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] tuning autovacuum
On Jun 9, 2011 12:01 AM, Euler Taveira de Oliveira eu...@timbira.com wrote: Hi, There are some releases that autovacuum was enabled by default and, up to now there is an easy way to estimate the number of autovacuum workers. I tune it observing if the number of slots are saturated for a period of time. I'm having a hard time trying to do this. I want to add a LOG message such as LOG: maximum number of autovacuum workers reached HINT: Consider increasing autovacuum_max_workers (currently 5). That would be very useful. And also a view (say pg_stat_autovacuum) to expose some autovacuum information such as (i) number of autovacuum workers (ii) number of tables that needs analyze/vacuum and are scheduled to (iii) number of Part of that is on my personal todo already, so I'd be happy to review that :) autovacuum count (iv) number of autoanalyze count. While I am in this topic, it would be nice to expose the analyze/vacuum count and threshold per table. This information should go to pg_stat_*_tables but it already has too much fields. Maybe it is time to split autovacuum information into another statistic view? That is configuration information and not statistics, so IMHO it does not belong in pg_stat_*. And if relation parameters are to be exposed more than they are now it should be done for all, not just autovacuum. /Magnus
Re: [HACKERS] reducing the overhead of frequent table locks - now, with WIP patch
Joshua Berkus j...@agliodbs.com writes: Simon, The point I have made is that I disagree with a feature freeze date fixed ahead of time without regard to the content of the forthcoming release. I've not said I disagree with feature freezes altogether, which would be utterly ridiculous. Fixed dates are IMHO much less important than a sensible and useful feature set for our users. This is such a non-argument it's silly. Perhaps more to the point, we've tried that approach in the past, repeatedly, and it's been a scheduling disaster every single time. Slipping the release date in order to get in newly-written features, no matter *how* attractive they are, does not work. Maybe there are people who can make it work, but not us. 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] gcc 4.6 and hot standby
On 09/06/11 06:58, Alex Hunsaker wrote: Yeah :-). However ill note it looks like its the default compiler for fedora 15, ubuntu natty and debian sid. FWIW Ubuntu natty uses gcc 4.5.2, probably just as as well in the light of your findings :-) Cheers Mark -- 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 heap_insert and page-level predicate locks
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: heap_insert() calls CheckForSerializableConflictIn(), which checks if there is a predicate lock on the whole relation, or on the page we're inserting to. It does not check for tuple-level locks, because there can't be any locks on a tuple that didn't exist before. AFAICS, the check for page lock is actually unnecessary. A page-level lock on a heap only occurs when tuple-level locks are promoted. It is just a coarser-grain representation of holding locks on all tuples on the page, *that exist already*. It is not a gap lock like the index locks are, it doesn't need to conflict with inserting new tuples on the page. In fact, if heap_insert chose to insert the tuple on some other heap page, there would have been no conflict. Absolutely correct. Patch attached. -Kevin *** a/src/backend/access/heap/heapam.c --- b/src/backend/access/heap/heapam.c *** *** 1916,1926 heap_insert(Relation relation, HeapTuple tup, CommandId cid, InvalidBuffer, options, bistate); /* !* We're about to do the actual insert -- check for conflict at the !* relation or buffer level first, to avoid possibly having to roll back !* work we've just done. */ ! CheckForSerializableConflictIn(relation, NULL, buffer); /* NO EREPORT(ERROR) from here till changes are logged */ START_CRIT_SECTION(); --- 1916,1929 InvalidBuffer, options, bistate); /* !* We're about to do the actual insert -- check for conflict first, to !* avoid possibly having to roll back work we've just done. !* !* NOTE: For a tuple insert, we only need to check for table locks, since !* predicate locking at the index level will cover ranges for anything !* except a table scan. Therefore, only provide the relation. */ ! CheckForSerializableConflictIn(relation, NULL, InvalidBuffer); /* NO EREPORT(ERROR) from here till changes are logged */ START_CRIT_SECTION(); -- 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] smallserial / serial2
On Tue, 7 Jun 2011 20:35:12 -0400, Mike Pultz m...@mikepultz.com wrote:
New patch attached.
Review for '20110607_serial2_v2.diff':
Submission review
- Is the patch in context diff format?
Yes.
- Does it apply cleanly to the current git master?
Yes.
- Does it include reasonable tests, necessary doc patches, etc?
It doesn't have any test but as it is really tiny and relies on the same
longstanding principles as serial and bigserial that seems ok.
It has documentation in the places where I'd expect it.
Usability review
- Does the patch actually implement that?
Yes.
- Do we want that?
Well, it depends whom you ask ;-)
Cons
Tom Lane: A sequence that can only go to 32K doesn't seem all that
generally useful
Pros
Mike Pultz (patch author): since serial4 and serial8 are simply
pseudo-types- effectively there for convenience, I’d argue that it
should simply be there for completeness
Robert Haas: We should be trying to put all types on equal footing,
rather than artificially privilege some over others.
Brar Piening (me): I'm with the above arguments. In addition I'd like
to mention that other databases have it too so having it improves
portability. Especially when using ORM.
Perhaps we can get some more opinions...
- Do we already have it?
No.
- Does it follow SQL spec, or the community-agreed behavior?
It has consistent behavior with the existing pseudo-types serial and
bigserial
- Does it include pg_dump support (if applicable)?
Not applicable.
- Are there dangers?
Not for the code base. One could consider it as a foot gun to allow
autoincs that must not exceed 32K but other databases offer even smaller
autoinc types (tinyint identity in MSSQL is a byte).
- Have all the bases been covered?
I guess so. A quick grep for bigint shows that it covers the same areas.
Feature test
- Does the feature work as advertised?
Yes.
- Are there corner cases the author has failed to consider?
Probably not.
- Are there any assertion failures or crashes?
No.
Performance review
- Does the patch slow down simple tests?
No.
- If it claims to improve performance, does it?
It doesn't claim anything about performance.
- Does it slow down other things?
No.
Coding review
- Does it follow the project coding guidelines?
Im not an expert in the project coding guidelines but it maches the
style of the surrounding code.
- Are there portability issues?
Probably not. At least not more than for smallint or serial.
- Will it work on Windows/BSD etc?
Yes.
- Are the comments sufficient and accurate?
Self explanatory - no comments needed.
- Does it do what it says, correctly?
Yes.
- Does it produce compiler warnings?
No.
- Can you make it crash?
No
Architecture review
- Is everything done in a way that fits together coherently with other
features/modules?
Yes.
- Are there interdependencies that can cause problems?
Interdependencies exist with sequences and the smallint type. No
problems probable.
Review review
- Did the reviewer cover all the things that kind of reviewer is
supposed to do?
I tried to look at everything and cover everthing but please consider
that this is my first review so please have a second look at it!
Regards,
Brar
CREATE DATABASE smallserial_test_db;
\connect smallserial_test_db
CREATE TABLE test_smallserial
(
id smallserial NOT NULL PRIMARY KEY,
val integer NOT NULL
);
CREATE TABLE test_smallserial2
(
id serial2 NOT NULL PRIMARY KEY,
val integer NOT NULL
);
\d
DROP TABLE test_smallserial2;
\d test_smallserial
INSERT INTO test_smallserial (val)
VALUES(1),(2),(3);
SELECT * FROM test_smallserial;
TRUNCATE TABLE test_smallserial;
SELECT setval('test_smallserial_id_seq', 1, false);
INSERT INTO test_smallserial (val)
SELECT * FROM generate_series(1,32767);
SELECT * FROM test_smallserial LIMIT ALL OFFSET 32764;
TRUNCATE TABLE test_smallserial;
SELECT setval('test_smallserial_id_seq', 1, false);
INSERT INTO test_smallserial (val)
SELECT * FROM generate_series(1,32768);
\connect postgres
DROP database smallserial_test_db;
--
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] Autoanalyze and OldestXmin
On Jun 8, 2011, at 10:33 AM, Greg Stark wrote: This is kind of like the other property it would be nice to know about transactions: that they've locked all the tables they're going to lock. That sounds like something I've wanted for a very long time: the ability for a transaction to say exactly what tables it's going to access. Presumably disallowing it from taking out any more table locks (anything you do on a table needs at least a share lock, right?) would take care of that. If we had that information vacuum could ignore the old snapshots on those tables, so long as it ensures that the vacuum process itself can't read anything from those tables (handling the functional index issue Tom mentioned). -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SSI work for 9.1
From a review of recent emails I've put together a list of what I'm going to try to do this evening, in order of attack. It's ambitious and I may well not get to the end tonight, but I wanted to get the issues on record in list form. If someone spots one I'm missing or thinks I should change the order I go at them, please say something. Based on the feedback, I suspect that (1) is a beta2 blocker. I don't think the rest are, although it would sure be nice to get them in now. If anyone else wants to jump on something on this list, let me know -- I'm happy to share. (1) Pass snapshot in to some predicate.c functions. The particular functions have yet to be determined, but certainly any which acquire predicate locks, and probably all which are guarded by the SkipSerialization() macro. Skip processing for non-MVCC snapshots. The goal here is to reduce false positive serialization failures and avoid confusion about locks showing in the pg_locks view which are hard to explain. (2) Check on heap truncation from vacuum. On the face of it this seems unlikely to be a problem since we make every effort to clean up predicate locks as soon as there is no transaction which can update what a transaction has read, but it merits a re-check. Once confirmed, add a note to lazy_truncate_heap about why it's not an issue. (3) Add regression tests for SSI/DDL interaction. (4) Add a comment to the docs about how querying tuples by TID doesn't lock not-found gaps the way an indexed access would. (5) I've also had a note to myself to add a couple more bullets to the performance notes at the bottom of this section: http://developer.postgresql.org/pgdocs/postgres/transaction-iso.html#XACT-SERIALIZABLE - If you're having a lot of rollbacks due to table-scan plans being used, you might want to try reducing random_page_cost and/or boosting cpu_tuple_cost. - If you're having a lot of rollbacks because multiple locks are being combined into relation locks, you might want to increase max_pred_locks_per_transaction. -Kevin -- 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] WALInsertLock contention
On Jun 8, 2011, at 10:15 AM, Robert Haas wrote: That suggests to me that you have to keep them pinned anyways. I'm still a bit fuzzy on how the per-backend buffers being in shm conveys any advantage. IOW, (trying not to be obtuse) under what circumstances would backend A want to read from or (especially) write to backend B's wal buffer? If backend A needs to evict a buffer with a fake LSN, it can go find the WAL that needs to be serialized, do that, flush WAL, and then evict the buffer. Isn't the only time that you'd need to evict if you ran out of buffers? If the buffer was truly private, would that still be an issue? Perhaps the only way to make that work is multiple WAL streams, as was originally suggested... -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] gcc 4.6 and hot standby
On Wed, Jun 8, 2011 at 16:20, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: On 09/06/11 06:58, Alex Hunsaker wrote: Yeah :-). However ill note it looks like its the default compiler for fedora 15, ubuntu natty and debian sid. FWIW Ubuntu natty uses gcc 4.5.2, probably just as as well in the light of your findings :-) Yeah I was just looking at distrowatch, its snapshot natty that uses 4.6.0. ubuntu 11.04 uses 4.5.2 like you said. http://distrowatch.com/table.php?distribution=ubuntu -- 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] smallserial / serial2
On Jun 8, 2011, at 5:36 PM, Brar Piening wrote: Pros Mike Pultz (patch author): since serial4 and serial8 are simply pseudo-types- effectively there for convenience, I’d argue that it should simply be there for completeness Robert Haas: We should be trying to put all types on equal footing, rather than artificially privilege some over others. Brar Piening (me): I'm with the above arguments. In addition I'd like to mention that other databases have it too so having it improves portability. Especially when using ORM. Perhaps we can get some more opinions... We have some dynamic lookup table metacode that sets up all the infrastructure for a table that normalizes text values to a serial/int. But in many cases, it's a safe bet that we would never need more than 32k (or at worst, 64k) values. Right now it would be difficult to benefit from the 2 byte savings, but if Postgres was ever able to order fields on disk in the most efficient possible format (something we're willing to sponsor, hint hint ;) then this would be beneficial for us. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] smallserial / serial2
On Wed, Jun 8, 2011 at 6:36 PM, Brar Piening b...@gmx.de wrote: New patch attached. Review for '20110607_serial2_v2.diff': I see you added this review to the CommitFest application - excellent. You should also change the status to either Waiting on Author or Ready for Committer based on the content of the review. I think the latter would be appropriate since your review seems to have been favorable. -- 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] tuning autovacuum
On Wed, Jun 8, 2011 at 5:54 PM, Euler Taveira de Oliveira eu...@timbira.com wrote: LOG: maximum number of autovacuum workers reached HINT: Consider increasing autovacuum_max_workers (currently 5). Comments? Is the hint correct? I mean, what if there were 100 small tables that needed vacuuming all at the same time. We'd hit this limit no matter how high you set autovacuum_max_workers, but it wouldn't be right to set it to 101 just because every once in a blue moon you might trip over the limit. I think it'd be really useful to expose some more data in this area though. One random idea is - remember the time at which a table was first observed to need vacuuming. Clear the timestamp when it gets vacuumed. Then you can do: SELECT blahblah FROM wumpity WHERE time_at_which_we_first_noticed_it_needed_vacuuming now() - '1 hour'::interval; ...or something of the sort. That way you can alert if autovacuum starts to fall too far behind, but you get to pick the definition of too far behind. -- 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] Proposal: Another attempt at vacuum improvements
On Wed, Jun 8, 2011 at 1:19 AM, Pavan Deolasee pavan.deola...@gmail.com wrote: I went on to create a WIP patch based on our discussion. There are couple of issues that I stumbled upon while testing it. 1. The start-of-index-vacuum LSN that we want to track must be noted even before the heap scan is started. This is because we must be absolutely sure that the index vacuum removes index pointers to all dead line pointers generated by any operation with LSN less than the start-of-index-vacuum LSN. If we don't remember the LSN before heap scan starts and rather delay it until the start of the index vacuum, new dead line pointers may get generated on a page which is already scanned by the heap scan but before the start of the index scan. Since the index pointers to these new dead line pointers haven't been vacuumed, we should really not be removing them. But as a consequence of using a LSN from the start of the heap scan, at the end of vacuum, all pruned pages will have vacuum LSN greater than the index vacuum LSN that we are going to remember in the pg_class. And by our design, we can't remove dead line pointers on those pages because we don't know if the index pointers have been vacuumed or not. We might not be able to reclaim any dead line pointers, if the page is again HOT pruned before the next vacuum cycle because that will overwrite the page vacuum LSN with a newer value. Oh. That sucks. I think we definitely need to track the dead line pointers that a heap scan has collected. The index pointers to them will be removed if the vacuum completes successfully. That gets us back to the original idea that we had discussed a while back about marking such dead line pointers as LP_DEAD_RECLAIMED or something like that. When vacuum runs heap scan, it would collect all dead line pointers and mark them dead-reclaimed and also store an identifier of the vacuum operation that would remove the associated index pointers. During HOT cleanup or the next vacuum, we can safely remove the LP_DEAD_RECLAIMED line pointers if we can safely check if the vacuum completed successfully or not. We don't have any free flags in ItemIdData, but we can use special lp_off to recognize a dead and dead-reclaimed line pointer. The identifier itself can either be an LSN or XID or anything else. Also, since we just need one identifier, I think this technique would work for unlogged and temp relations, with little adjustments. OK. So we have a Boolean some place. At the beginning of VACUUM, we read and remember the old value, and set it to false. At the end of VACUUM, after everything has succeeded, we set it to true. During HOT cleanup, we can free dead-reclaimed line pointers if the value is currently true. During VACUUM, we can free dead-reclaimed line pointers if the value was true when we started. The name dead-reclaimed doesn't inspire me very much. Dead vs. dead-vacuumed? Morbid vs. dead? 2. Another issue is with analyze counting dead line pointers as dead rows. While its correct in principle because a vacuum is needed to remove these dead line pointers, the overhead of having a dead line pointer is much lesser than a dead tuple. Also, with single pass vacuum, there will be many dead line pointers waiting to be cleaned up in the next vacuum or HOT-prune. We should not really count them as dead rows because they don't require a vacuum per se and counting them as dead will force more vacuum cycles than required. If we go by the idea described above, we can definitely skip the dead-reclaimed line pointers, definitely when we know that index vacuum was completed successfully. Thoughts ? I think we should count both the dead line pointers and dead tuples separately, but have two separate counters. I agree that a dead line pointer is a lot less expensive than a dead tuple, but it's not free either. -- 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] SSI work for 9.1
On Wed, Jun 08, 2011 at 05:48:26PM -0500, Kevin Grittner wrote: (1) Pass snapshot in to some predicate.c functions. The particular functions have yet to be determined, but certainly any which acquire predicate locks, and probably all which are guarded by the SkipSerialization() macro. Skip processing for non-MVCC snapshots. The goal here is to reduce false positive serialization failures and avoid confusion about locks showing in the pg_locks view which are hard to explain. I assume you've already started on this one; let me know if you have a patch I should take a look at or hit any snags. (2) Check on heap truncation from vacuum. On the face of it this seems unlikely to be a problem since we make every effort to clean up predicate locks as soon as there is no transaction which can update what a transaction has read, but it merits a re-check. Once confirmed, add a note to lazy_truncate_heap about why it's not an issue. I assume you are worried here that there may be SIREAD locks remaining on truncated pages/tuples, and these would cause false positives if those pages are reused? I don't believe this can happen, because a vacuum will only delete a formerly-visible dead tuple if its xmax is earlier than OldestXmin. We remove all SIREAD locks on anything older than GlobalSxactXmin, which won't be less than OldestXmin. (4) Add a comment to the docs about how querying tuples by TID doesn't lock not-found gaps the way an indexed access would. I can take care of this one and some other README-SSI changes. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- 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] tuning autovacuum
Robert Haas robertmh...@gmail.com writes: I think it'd be really useful to expose some more data in this area though. One random idea is - remember the time at which a table was first observed to need vacuuming. Clear the timestamp when it gets vacuumed. Then you can do: As far as I recall that logic, there is no delay between when we know that a table needs vacuumed and when we do it. I don't see the point of introducing any such delay, 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] tuning autovacuum
On Wed, Jun 8, 2011 at 9:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I think it'd be really useful to expose some more data in this area though. One random idea is - remember the time at which a table was first observed to need vacuuming. Clear the timestamp when it gets vacuumed. Then you can do: As far as I recall that logic, there is no delay between when we know that a table needs vacuumed and when we do it. I don't see the point of introducing any such delay, either. Well, if there are more tables that need vacuuming than there are workers available at any given time, there will be a delay. We probably don't keep track of that delay at present, but we could. -- 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] SSI work for 9.1
Dan Ports wrote: On Wed, Jun 08, 2011 at 05:48:26PM -0500, Kevin Grittner wrote: (1) Pass snapshot in to some predicate.c functions. The particular functions have yet to be determined, but certainly any which acquire predicate locks, and probably all which are guarded by the SkipSerialization() macro. Skip processing for non-MVCC snapshots. The goal here is to reduce false positive serialization failures and avoid confusion about locks showing in the pg_locks view which are hard to explain. I assume you've already started on this one; let me know if you have a patch I should take a look at or hit any snags. A patch is attached which just covers the predicate lock acquisition, where a snapshot is available without too much pain. There are two functions which acquire predicate locks where a snapshot was not readily available: _bt_search() and _bt_get_endpoint(). Not only was it not clear how to get a snapshot in, it was not entirely clear from reading the code that we need to acquire predicate locks here. Now, I suspect that we probably do, because I spent many long hours stepping through gdb to pick the spots where they are, but that was about a year ago and my memory of the details has faded. FWIW, this patch not only passes the usual battery of regression tests that I run, but the test which was showing REINDEX acquiring predicate locks on the heap runs without that happening. I'm posting this because I think it's the most important area to cover, and in a pinch might satisfy people for 9.1; but more importantly to give me a good place to step back to in case I muck things up moving forward from this point. (2) Check on heap truncation from vacuum. On the face of it this seems unlikely to be a problem since we make every effort to clean up predicate locks as soon as there is no transaction which can update what a transaction has read, but it merits a re-check. Once confirmed, add a note to lazy_truncate_heap about why it's not an issue. I assume you are worried here that there may be SIREAD locks remaining on truncated pages/tuples, and these would cause false positives if those pages are reused? I don't believe this can happen, because a vacuum will only delete a formerly-visible dead tuple if its xmax is earlier than OldestXmin. We remove all SIREAD locks on anything older than GlobalSxactXmin, which won't be less than OldestXmin. That was my first thought, too. But, the question being raised, I thought a quick double-check that there weren't any corner cases where this could occur was reasonable. (4) Add a comment to the docs about how querying tuples by TID doesn't lock not-found gaps the way an indexed access would. I can take care of this one and some other README-SSI changes. OK, I'll stay away from any doc items for now. Those are all yours until we agree otherwise. :-) I really don't think I'm going to get past the snapshot guard issue tonight. :-/ -Kevin ssi-predlock-snapshot-1.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] WALInsertLock contention
On Wed, Jun 8, 2011 at 6:49 PM, Jim Nasby j...@nasby.net wrote: If backend A needs to evict a buffer with a fake LSN, it can go find the WAL that needs to be serialized, do that, flush WAL, and then evict the buffer. Isn't the only time that you'd need to evict if you ran out of buffers? Sure, but that happens all the time. See pg_stat_bgwriter.buffers_backend. If the buffer was truly private, would that still be an issue? I'm not sure if you mean make the buffer private or make the WAL storage arena private, but I'm pretty well convinced that neither one can work. Perhaps the only way to make that work is multiple WAL streams, as was originally suggested... Maybe... but I hope not. I just found an academic paper on this subject, about which I will post shortly. -- 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] gcc 4.6 and hot standby
On Thu, Jun 9, 2011 at 5:31 AM, Tom Lane t...@sss.pgh.pa.us wrote: So it's interesting that this only happens with a particular gcc version, because it's not apparent to me why it works properly for anybody. Isn't hitting a zero record length an expected case when we run ahead of the amount of WAL produced by the master? At least while walreceiver is running, recovery doesn't go ahead of the last receive location. So that's not an expected case. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tuning autovacuum
Em 08-06-2011 20:35, Robert Haas escreveu: Is the hint correct? I mean, what if there were 100 small tables that needed vacuuming all at the same time. We'd hit this limit no matter how high you set autovacuum_max_workers, but it wouldn't be right to set it to 101 just because every once in a blue moon you might trip over the limit. I think so. You are picturing a scene with only one message. It is the same case of the too-frequent-checkpoint messages; i.e., you should look if those messages have some periodicity. I think it'd be really useful to expose some more data in this area though. One random idea is - remember the time at which a table was first observed to need vacuuming. Clear the timestamp when it gets vacuumed. Then you can do: Hmmm. But this fine grained information alone doesn't help tuning the number of autovacuum workers. I consider counters easier to implement and simpler to analyze. But the timestamp idea has its merit because we already have a similar statistic (last timestamp table was vacuumed or analyzed). -- 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] WALInsertLock contention
On Wed, Jun 8, 2011 at 10:21 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jun 8, 2011 at 6:49 PM, Jim Nasby j...@nasby.net wrote: If backend A needs to evict a buffer with a fake LSN, it can go find the WAL that needs to be serialized, do that, flush WAL, and then evict the buffer. Isn't the only time that you'd need to evict if you ran out of buffers? Sure, but that happens all the time. See pg_stat_bgwriter.buffers_backend. If the buffer was truly private, would that still be an issue? I'm not sure if you mean make the buffer private or make the WAL storage arena private, but I'm pretty well convinced that neither one can work. You're probably right. I think though there is enough hypothetical upside to the private buffer case that it should be attempted just to see what breaks. The major tricky bit is dealing with the new pin/unpin mechanics. I'd like to give it the 'college try'. (being typically vain and attention seeking, this is right up my alley) :-D. Perhaps the only way to make that work is multiple WAL streams, as was originally suggested... If this was an easy way out all high performance file systems would have multiple journals which you could write to concurrently (which they don't afaik). Maybe... but I hope not. I just found an academic paper on this subject, about which I will post shortly. I'm thinking that as long as your transactions have to be rigidly ordered you have a fundamental bottleneck you can't really work around. One way to maybe get around this is to try and work out on the fly if transaction 'A' functionally independent from transaction 'B' -- maybe then you could try and write them concurrently to pre-allocated space on the log, or to separate logs maintained for that purpose. Good luck with that...even if you could somehow get it to work, you would still have degenerate cases (like, 99% of real world cases) to contend with. Another thing you could try is to keep separate logs for rigidly ordered data (commits, xlog switch, etc) and non rigidly ordered data (everything else). On the non rigidly ordered side, you can pre-allocate log space and write to it. This is more or less a third potential route (#1 and #2 being the shared/private buffer approaches) of leveraging the fact that some of the data does not have to be rigidly ordered. Ultimately though even that could only get you so far, because it incurs other costs and even contention on the lock for inserting the commit records could start to bottleneck you. If something useful squirts out of academia, I'd sure like to see it :-). merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] literature on write-ahead logging
I did a brief literature search for papers on breaking the WAL-serialization bottleneck today and hit upon this: Aether: A Scalable Approach to Logging, Ryan Johnson, Ippokratis Pandis, et al. http://infoscience.epfl.ch/record/149436/files/vldb10aether.pdf Section 5 appears to be the most relevant to our problems with WALInsertLock. They reject the approach that I proposed, wherein WAL is generated by individual backends in their own queues and serialized later: While a total ordering is not technically required for correctness, valid partial orders tend to be too complex and interdependent to be worth pursuing as a performance optimization; see also appendix A.5, which may be succinctly summarized as no one does that. Instead, they propose two other optimizations: 1. Subdivide XLOG insertion into three operations: (1) allocate space in the log buffer, (2) copy the log records into the allocated space, and (3) release the space to the buffer manager for eventual write to disk. AIUI, WALInsertLock currently covers all three phases of this operation, but phase 2 can proceed in parallel. It's pretty easy to imagine maintain one pointer that references the next available byte of log space (let's call this the next insert pointer), and a second pointer that references the byte following the last byte known to be written (let's call this the insert done pointer). When a backend wants to insert data, it locks the next insert pointer, advances it, releases the lock, and starts copying data into the buffer. That part is simple enough. Then we need to update the insert done pointer, which is fine if it happens to point to the beginning of our record - but it may be that we were busy inserting a short record while the guy ahead of us was inserting a long one (an FPW, for example), in which case it gets complicated. We now need to sleep until everyone ahead of us is done, then wake up and finish the operation. You can end up with a chain of waiters that looks like this: A - B - C - D - E. To avoid the scenario where A finishes its operation and then wakes up B, which must finish its operation and then wake up C, which must finish its operation and then wake up D, and so on, the paper proposes a further optimization: have A finish up all the pending work for B, C, D, and E and then wake them all up at once. I guess the operations are so short here that it's more efficient for A to just do all the work (within some reasonable bounds), so that C, D, and E don't have to wait for multiple context switches. 2. If a process attempts to allocate space in the log buffer and finds the lock contended, it instead attempts to lock a so-called consolidation buffer, where it buddies up with some other processes having the same problem. One of them calculated the space requirement for the whole group, allocates that amount of space, and the parcels it out to the group members. The details are complex, but the basic idea makes a lot of sense, because if incrementing next insert pointer is a hotspot, it clearly makes sense to do one big increment rather than a series of smaller ones. In the interest of full disclosure, I'm rather badly misrepresenting the paper in the above discussion. First, as they present it, these two optimizations are independent of each other, whereas I have not described them so. This is probably a lack of adequate understanding on my part. Second, they aren't really using locks, unless you count bus locks - they appear to have implemented most or all of it via CAS-based lock-free algorithms, which is probably well-justified optimization effort. Anyway, I think this bears more looking into, and will try to find time to do so. The performance results they cite are quite impressive. -- 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] WALInsertLock contention
On Wed, Jun 8, 2011 at 11:20 PM, Merlin Moncure mmonc...@gmail.com wrote: You're probably right. I think though there is enough hypothetical upside to the private buffer case that it should be attempted just to see what breaks. The major tricky bit is dealing with the new pin/unpin mechanics. I'd like to give it the 'college try'. (being typically vain and attention seeking, this is right up my alley) :-D. Well, I think it's fairly clear what will break: - If you make the data-file buffer completely private, then what will happen when some other backend needs to read or write that buffer? - If you make the XLOG spool private, you will not be able to checkpoint. But I just work here. Feel free to hit your head on that brick wall all you like. If you manage to make a hole (in the wall, not your head), I'll be as happy as anyone to climb through...! -- 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] SSI work for 9.1
Kevin Grittner wrote: A patch is attached which just covers the predicate lock acquisition This patch rolls that up with snapshot checking in the conflict detection function called on read. The only other two functions which use that macro check for conflicts on write, and I can't see why snapshot checking would make sense there. The write isn't into a particular snapshot, it's in the context of the transaction; so unless someone can make a good case for why snapshot checking makes sense there, I think this is far as it makes sense to take this. Except of course for those two functions in the btree AM which didn't have snapshot available. I'm not sure what needs to be done there. Thoughts? -Kevin ssi-predlock-snapshot-2.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] SSI work for 9.1
On Wed, Jun 08, 2011 at 09:17:04PM -0500, Kevin Grittner wrote:
A patch is attached which just covers the predicate lock acquisition,
where a snapshot is available without too much pain. There are two
functions which acquire predicate locks where a snapshot was not
readily available: _bt_search() and _bt_get_endpoint(). Not only was
it not clear how to get a snapshot in, it was not entirely clear from
reading the code that we need to acquire predicate locks here. Now,
I suspect that we probably do, because I spent many long hours
stepping through gdb to pick the spots where they are, but that was
about a year ago and my memory of the details has faded.
For _bt_search(), the lock calls should move to _bt_first() where the
ScanDesc is available. This also keeps us from trying to take locks
during _bt_pagedel(), which is only called during vacuum and recovery.
The call in _bt_get_endpoint() seems unnecessary, because after it
returns, _bt_endpoint() takes the same lock. The only other callers of
_bt_get_endpoint() are _bt_pagedel() and _bt_insert_parent(), neither
of which should take predicate locks.
I've updated the patch, attached.
Dan
--
Dan R. K. Ports MIT CSAILhttp://drkp.net/
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 6ac25af..bf75ace 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -274,7 +274,7 @@ heapgetpage(HeapScanDesc scan, BlockNumber page)
else
valid = HeapTupleSatisfiesVisibility(loctup, snapshot, buffer);
- CheckForSerializableConflictOut(valid, scan-rs_rd, loctup, buffer);
+ CheckForSerializableConflictOut(valid, scan-rs_rd, loctup, buffer, snapshot);
if (valid)
scan-rs_vistuples[ntup++] = lineoff;
@@ -469,7 +469,7 @@ heapgettup(HeapScanDesc scan,
snapshot,
scan-rs_cbuf);
-CheckForSerializableConflictOut(valid, scan-rs_rd, tuple, scan-rs_cbuf);
+CheckForSerializableConflictOut(valid, scan-rs_rd, tuple, scan-rs_cbuf, snapshot);
if (valid key != NULL)
HeapKeyTest(tuple, RelationGetDescr(scan-rs_rd),
@@ -478,7 +478,7 @@ heapgettup(HeapScanDesc scan,
if (valid)
{
if (!scan-rs_relpredicatelocked)
- PredicateLockTuple(scan-rs_rd, tuple);
+ PredicateLockTuple(scan-rs_rd, tuple, snapshot);
LockBuffer(scan-rs_cbuf, BUFFER_LOCK_UNLOCK);
return;
}
@@ -747,7 +747,7 @@ heapgettup_pagemode(HeapScanDesc scan,
if (valid)
{
if (!scan-rs_relpredicatelocked)
- PredicateLockTuple(scan-rs_rd, tuple);
+ PredicateLockTuple(scan-rs_rd, tuple, scan-rs_snapshot);
scan-rs_cindex = lineindex;
return;
}
@@ -755,7 +755,7 @@ heapgettup_pagemode(HeapScanDesc scan,
else
{
if (!scan-rs_relpredicatelocked)
- PredicateLockTuple(scan-rs_rd, tuple);
+ PredicateLockTuple(scan-rs_rd, tuple, scan-rs_snapshot);
scan-rs_cindex = lineindex;
return;
}
@@ -1470,9 +1470,9 @@ heap_fetch(Relation relation,
valid = HeapTupleSatisfiesVisibility(tuple, snapshot, buffer);
if (valid)
- PredicateLockTuple(relation, tuple);
+ PredicateLockTuple(relation, tuple, snapshot);
- CheckForSerializableConflictOut(valid, relation, tuple, buffer);
+ CheckForSerializableConflictOut(valid, relation, tuple, buffer, snapshot);
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
@@ -1588,11 +1588,11 @@ heap_hot_search_buffer(ItemPointer tid, Relation relation, Buffer buffer,
/* If it's visible per the snapshot, we must return it */
valid = HeapTupleSatisfiesVisibility(heapTuple, snapshot, buffer);
- CheckForSerializableConflictOut(valid, relation, heapTuple, buffer);
+ CheckForSerializableConflictOut(valid, relation, heapTuple, buffer, snapshot);
if (valid)
{
ItemPointerSetOffsetNumber(tid, offnum);
- PredicateLockTuple(relation, heapTuple);
+ PredicateLockTuple(relation, heapTuple, snapshot);
if (all_dead)
*all_dead = false;
return true;
@@ -1750,7 +1750,7 @@ heap_get_latest_tid(Relation relation,
* result candidate.
*/
valid = HeapTupleSatisfiesVisibility(tp, snapshot, buffer);
- CheckForSerializableConflictOut(valid, relation, tp, buffer);
+ CheckForSerializableConflictOut(valid, relation, tp, buffer, snapshot);
if (valid)
*tid = ctid;
diff --git a/src/backend/access/index/indexam.c b/src/backend/access/index/indexam.c
index 27c37d6..294ab45 100644
--- a/src/backend/access/index/indexam.c
+++ b/src/backend/access/index/indexam.c
@@ -126,7 +126,7 @@ do { \
} while(0)
static IndexScanDesc index_beginscan_internal(Relation indexRelation,
- int nkeys, int norderbys);
+ int nkeys, int norderbys, const Snapshot snapshot);
/*
@@ -234,7 +234,7 @@ index_beginscan(Relation heapRelation,
{
IndexScanDesc scan;
- scan = index_beginscan_internal(indexRelation, nkeys, norderbys);
+ scan =
Re: [HACKERS] tuning autovacuum
On Wed, Jun 8, 2011 at 10:55 PM, Euler Taveira de Oliveira eu...@timbira.com wrote: Em 08-06-2011 20:35, Robert Haas escreveu: Is the hint correct? I mean, what if there were 100 small tables that needed vacuuming all at the same time. We'd hit this limit no matter how high you set autovacuum_max_workers, but it wouldn't be right to set it to 101 just because every once in a blue moon you might trip over the limit. I think so. You are picturing a scene with only one message. It is the same case of the too-frequent-checkpoint messages; i.e., you should look if those messages have some periodicity. Yeah, maybe. I'm just not sure there would be an easy way for users to judge when they should or should not make a change. I think it'd be really useful to expose some more data in this area though. One random idea is - remember the time at which a table was first observed to need vacuuming. Clear the timestamp when it gets vacuumed. Then you can do: Hmmm. But this fine grained information alone doesn't help tuning the number of autovacuum workers. I consider counters easier to implement and simpler to analyze. But the timestamp idea has its merit because we already have a similar statistic (last timestamp table was vacuumed or analyzed). Well, it won't directly tell you how many you need. But certainly if you see things getting further and further behind, you know you need more. Or, alternatively, you need to reduce vacuum_cost_delay. IME, that's actually the most common cause of this problem. -- 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] WALInsertLock contention
On Wed, Jun 8, 2011 at 11:27 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jun 8, 2011 at 11:20 PM, Merlin Moncure mmonc...@gmail.com wrote: You're probably right. I think though there is enough hypothetical upside to the private buffer case that it should be attempted just to see what breaks. The major tricky bit is dealing with the new pin/unpin mechanics. I'd like to give it the 'college try'. (being typically vain and attention seeking, this is right up my alley) :-D. Well, I think it's fairly clear what will break: - If you make the data-file buffer completely private, then what will happen when some other backend needs to read or write that buffer? The private wal buffer? The whole point (maybe impossible) is to try and engineer it so that the other backends *never* have to read and write it -- from their point of view, it hasn't happened yet (even though it has been written into some heap buffers). Since all data action on ongoing transactions can happen at any time, moving wal inserts into the private buffer is delaying its entry into the log so you can avoid taking locks for pre-commit heap activity. Doing this allows the backends doing that to pretend they are actually did write data out into the log without breaking the 'wal before data' rule which is effected by keeping the pin on pages with your magic LSN (which I'm starting to wonder if it should be a flag like BM_DEFERRED_WAL). We essentially are moving xlog activity as far ahead in time as possible (although in a very limited time space) in order to combine locks and hopefully gain efficiency. It all comes down to which rules you can bend and which you can break. The heap pages that have been marked this way may or may not have to be off limits from the backend other than the one that did the marking, and if they have to be off limits logically, there may be no realistic path to make them so. I just don't know...I'm learning as I go. At the end of the day, it's all coming off as pretty fragile if it even works, but it's fun to think about. Anyways, I'm inclined to experiment. - If you make the XLOG spool private, you will not be able to checkpoint. Correct -- but I don't think this problem is intractable, and is really a secondary issue vs making sure the wal/heap/mvcc/backend interactions 'work'. The intent here is to spool only a relatively small amount of uncommitted transaction data for a short period of time, like 5-10 seconds. Maybe you bite the bullet and tell everyone to flush private WAL at checkpoint time via signal or something. Maybe you bend the some rules on checkpoints. 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] Invalid byte sequence for encoding UTF8, caused due to non wide-char-aware downcase_truncate_identifier() function on WINDOWS
On Wed, Jun 8, 2011 at 6:22 AM, Robert Haas robertmh...@gmail.com wrote: 2011/6/7 Jeevan Chalke jeevan.cha...@enterprisedb.com: since we smash the identifier to lower case using downcase_truncate_identifier() function, the solution is to make this function should be wide-char aware, like LOWER() function functionality. I see some discussion related to downcase_truncate_identifier() and wide-char aware function, but seems like we lost somewhere. (http://archives.postgresql.org/pgsql-hackers/2010-11/msg01385.php) This invalid byte sequence issue seems like a more serious issue, because it might lead e.g to pg_dump failures. It's a problem, but without an efficient algorithm for Unicode case folding, any fix we attempt to implement seems like it'll just be moving the problem around. Agree. I read on other mail thread that str_tolower() is a wide-character-aware lower function but it is also a collation-aware and hence might change its behaviour wrt change in locale. However, Tom suggested that we need to have non-locale-dependent case folding algorithm. But still for same locale on same machine, where we can able to create a table, insert some data, we cannot retrieve it. Don't you think it is more serious and we need a quick solution here? As said earlier it may even lead to pg_dump failures. Given that str_tolower() functionality is locale dependent but still it will resolve this particular issue. Not sure, there might be a performance issue but at-least we are not giving an error. Please excuse me, if community already had a lot of discussion and kept this behaviour intentionally knowing all these errors and serious issues. Thanks -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Jeevan B Chalke Senior Software Engineer, RD EnterpriseDB Corporation The Enterprise PostgreSQL Company Phone: +91 20 30589500 Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
