Re: [HACKERS] Re: patch review : Add ability to constrain backend temporary file space
On Wed, Jun 1, 2011 at 6:35 PM, Mark Kirkwood wrote: > On 01/06/11 09:24, Cédric Villemain wrote: >> >> Submission review >> >> >> * The patch is not in context diff format. >> * The patch apply, but contains some extra whitespace. >> * Documentation is here but not explicit about 'temp tables', >> maybe worth adding that this won't limit temporary table size ? >> * There is no test provided. One can be expected to check that the >> feature work. >> > > I've created a new patch (attached) Hi Mark, A few comments: - why only superusers can set this? if this is a per-backend setting, i don't see the problem in allowing normal users to restrict their own queries - why the calculations are done as double? + if (temporary_files_size / 1024.0 > (double)work_disk) - the patch adds this to serial_schedule but no test has been added... diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index bb654f9..325cb3d 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -127,3 +127,4 @@ test: largeobject test: with test: xml test: stats +test: resource -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] is there any reason why don't create context info for RAISE EXCEPTION?
Hello I am playing with context callback functions. I found so we leave early this function, when exception is raised by RAISE statement. I can understand it when level is NOTICE, but it is strange for other levels. More we can emulate any exception now, but these exceptions are hidden in context. Is there some reason why we do it? I am thinking so more practical is setting estate->err_text = raise_skip_message only for level NOTICE. Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cube Index Size
2011/6/1 Alexander Korotkov > 2011/6/1 Teodor Sigaev >> >> One of idea is add sorting of Datums to be splitted by cost of insertion. >> It's implemented in intarray/tsearch GiST indexes. >> > > Yes, it's a good compromise between linear and quadratic entries > distribution algorithms. In quadratic algorithm each time entry with maximal > difference of inserion cost is inserted. Quadratic algorithm runs slowly > than sorting one, but on my tests it shows slightly better results. > > > Can we figure out some information about index i.e. whet is the height of index tree, how many values are placed in one leaf node and one non leaf level node? Regards, Nick
[HACKERS] Re: pg_terminate_backend and pg_cancel_backend by not administrator user
On Wed, Jun 01, 2011 at 10:26:34PM -0400, Josh Kupershmidt wrote: > On Wed, Jun 1, 2011 at 5:55 PM, Noah Misch wrote: > > On Sun, May 29, 2011 at 10:56:02AM -0400, Josh Kupershmidt wrote: > >> Looking around, I see there were real problems[1] with sending SIGTERM > >> to individual backends back in 2005 or so, and pg_terminate_backend() > >> was only deemed safe enough to put in for 8.4 [2]. So expanding > >> pg_terminate_backend() privileges does make me a tad nervous. > > > > The documentation for the CREATE USER flag would boil down to "omit this > > flag > > only if you're worried about undiscovered PostgreSQL bugs in this area". > > ?I'd > > echo Tom's sentiment from the first thread, "In any case I think we have to > > solve it, not create new mechanisms to try to ignore it." > > I do agree with Tom's sentiment from that thread. But, if we are > confident that pg_terminate_backend() is safe enough to relax > permissions on, then I take it you agree we should plan to extend this > power to all users? Yes; that's what I was trying to say. Having thought about this some more, I do now see a risk. Currently, a SECURITY DEFINER function (actually any function, but that's where it matters) can trap query_canceled. By doing so, the author can ensure that only superusers and crashes may halt the function during a section protected in this way. One might use it to guard a series of updates made over dblink. pg_terminate_backend() breaks this protection. I've never designed something this way; it only suffices when you merely sort-of-care about transactional integrity. Perhaps it's an acceptable loss for this feature? > And if so, is this patch a good first step on that path? Yes. > >> Reading through those old threads made me realize this patch would > >> give database owners the ability to kill off autovacuum workers. Seems > >> like we'd want to restrict that power to superusers. > > > > Would we? ?Any old user can already stifle VACUUM by holding a transaction > > open. > > This is true, though it's possible we might at some point want a > backend process which really shouldn't be killable by non-superusers > (if vacuum/autovacuum isn't one already.) Actually, I could easily > imagine a superuser running an important query on a database getting > peeved if a non-superuser were allowed to cancel/terminate his > queries. That's really a different level of user isolation than we have. If your important query runs on a database owned by someone else, calls functions owned by someone else, or reads tables owned by someone else, you're substantially at the mercy of those object owners. That situation probably is unsatisfactory to some folks. Adding the possibility that a database owner could cancel your query seems like an extension of that codependency more than a new exposure. Thanks, nm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: patch review : Add ability to constrain backend temporary file space
On 02/06/11 11:35, Mark Kirkwood wrote: On 01/06/11 09:24, Cédric Villemain wrote: Simple Feature test == either explain buffers is wrong or the patch is wrong: cedric=# explain (analyze,buffers) select * from foo order by 1 desc ; QUERY PLAN - Sort (cost=10260.02..10495.82 rows=94320 width=4) (actual time=364.373..518.940 rows=10 loops=1) Sort Key: generate_series Sort Method: external merge Disk: 1352kB Buffers: local hit=393, temp read=249 written=249 -> Seq Scan on foo (cost=0.00..1336.20 rows=94320 width=4) (actual time=0.025..138.754 rows=10 loops=1) Buffers: local hit=393 Total runtime: 642.874 ms (7 rows) cedric=# set max_temp_files_size to 1900; SET cedric=# explain (analyze,buffers) select * from foo order by 1 desc ; ERROR: aborting due to exceeding max temp files size STATEMENT: explain (analyze,buffers) select * from foo order by 1 desc ; ERROR: aborting due to exceeding max temp files size Do you have some testing method I can apply to track that without explain (analyze, buffers) before going to low-level monitoring ? We're looking at this... Arg - I was being dense. FileWrite can write *anywhere* in the file, so need to be smart about whether to add to the total filesize or not. I'll update the Commitfest page as soon as it sends me my password reset mail... Cheers Mark temp-files-v4.patch.gz Description: GNU Zip compressed 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] pg_terminate_backend and pg_cancel_backend by not administrator user
On Wed, Jun 1, 2011 at 5:55 PM, Noah Misch wrote: > On Sun, May 29, 2011 at 10:56:02AM -0400, Josh Kupershmidt wrote: >> Looking around, I see there were real problems[1] with sending SIGTERM >> to individual backends back in 2005 or so, and pg_terminate_backend() >> was only deemed safe enough to put in for 8.4 [2]. So expanding >> pg_terminate_backend() privileges does make me a tad nervous. > > The documentation for the CREATE USER flag would boil down to "omit this flag > only if you're worried about undiscovered PostgreSQL bugs in this area". I'd > echo Tom's sentiment from the first thread, "In any case I think we have to > solve it, not create new mechanisms to try to ignore it." I do agree with Tom's sentiment from that thread. But, if we are confident that pg_terminate_backend() is safe enough to relax permissions on, then I take it you agree we should plan to extend this power to all users? And if so, is this patch a good first step on that path? >> Reading through those old threads made me realize this patch would >> give database owners the ability to kill off autovacuum workers. Seems >> like we'd want to restrict that power to superusers. > > Would we? Any old user can already stifle VACUUM by holding a transaction > open. This is true, though it's possible we might at some point want a backend process which really shouldn't be killable by non-superusers (if vacuum/autovacuum isn't one already.) Actually, I could easily imagine a superuser running an important query on a database getting peeved if a non-superuser were allowed to cancel/terminate his queries. Josh -- 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] storing TZ along timestamps
On Wed, Jun 1, 2011 at 8:18 PM, Alvaro Herrera wrote: > Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011: >> On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote: >> > Hi, >> > >> > One of our customers is interested in being able to store original >> > timezone along with a certain timestamp. >> >> I assume that you're talking about a new data type, not augmenting the >> current types, correct? > > Yes why not use a composite type for that? performance maybe? 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] creating CHECK constraints as NOT VALID
Excerpts from Thom Brown's message of mié jun 01 19:48:44 -0400 2011: > Is this expected? > [ pg_dump fails to preserve not-valid status of constraints ] Certainly not. > Shouldn't the constraint be dumped as not valid too?? Sure, I'll implement that tomorrow. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] storing TZ along timestamps
On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote: > Hi, > > One of our customers is interested in being able to store original > timezone along with a certain timestamp. I assume that you're talking about a new data type, not augmenting the current types, correct? 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] storing TZ along timestamps
Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011: > On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote: > > Hi, > > > > One of our customers is interested in being able to store original > > timezone along with a certain timestamp. > > I assume that you're talking about a new data type, not augmenting the > current types, correct? Yes -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgpool versus sequences
I wrote: > Please note also that what pgpool users have got right now is a time > bomb, which is not better than immediately-visible breakage. BTW, so far as that goes, I suggest that we tweak nextval() and setval() to force the sequence tuple's xmax to zero. That will provide a simple recovery path for anyone who's at risk at the moment. Of course, this has to go hand-in-hand with the change to forbid SELECT FOR UPDATE, else those operations would risk breaking active tuple locks. 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] pgpool versus sequences
Alvaro Herrera writes: > Excerpts from Tatsuo Ishii's message of mié jun 01 19:08:16 -0400 2011: >> What pgpool really wanted to do was locking sequence tables, not >> locking rows in sequences. I wonder why the former is not allowed. > Yeah -- why is LOCK SEQUENCE foo_seq not allowed? Seems a simple thing > to have. I don't see any particular reason to continue to disallow it, but does that actually represent a workable solution path for pgpool? Switching over to that would fail on older servers. 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] pgpool versus sequences
>> Yeah -- why is LOCK SEQUENCE foo_seq not allowed? Seems a simple thing >> to have. > > I don't see any particular reason to continue to disallow it, but does > that actually represent a workable solution path for pgpool? Switching > over to that would fail on older servers. pgpool will provide following method for older version of PostgreSQL. > Probably creating a "secret" relation and acquire table locking > on it is the way to go. This is essentially a dirty alternative for > sequence table locking. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] creating CHECK constraints as NOT VALID
On 1 June 2011 23:47, Alvaro Herrera wrote: > > Here's a complete patch with all this stuff, plus doc additions and > simple regression tests for the new ALTER DOMAIN commands. > > Enable CHECK constraints to be declared NOT VALID > > This means that they can initially be added to a large existing table > without checking its initial contents, but new tuples must comply to > them; a separate pass invoked by ALTER TABLE / VALIDATE can verify > existing data and ensure it complies with the constraint, at which point > it is marked validated and becomes a normal part of the table ecosystem. > > This patch also enables domains to have unvalidated CHECK constraints > attached to them as well by way of ALTER DOMAIN / ADD CONSTRAINT / NOT > VALID, which can later be validated with ALTER DOMAIN / VALIDATE > CONSTRAINT. Is this expected? postgres=# CREATE TABLE a (num INT); CREATE TABLE postgres=# INSERT INTO a (num) VALUES (90); INSERT 0 1 postgres=# ALTER TABLE a ADD CONSTRAINT meow CHECK (num < 20) NOT VALID; ALTER TABLE postgres=# CREATE DATABASE test; CREATE DATABASE postgres=# \q postgresql thom$ pg_dump -f /tmp/test.sql postgres postgresql thom$ psql test < /tmp/test.sql SET SET SET SET SET COMMENT CREATE EXTENSION COMMENT SET SET SET CREATE TABLE ALTER TABLE ERROR: new row for relation "a" violates check constraint "meow" CONTEXT: COPY a, line 1: "90" STATEMENT: COPY a (num) FROM stdin; ERROR: new row for relation "a" violates check constraint "meow" CONTEXT: COPY a, line 1: "90" REVOKE REVOKE GRANT GRANT Shouldn't the constraint be dumped as not valid too?? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: 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] pgpool versus sequences
Excerpts from Tatsuo Ishii's message of mié jun 01 19:08:16 -0400 2011: > What pgpool really wanted to do was locking sequence tables, not > locking rows in sequences. I wonder why the former is not allowed. Yeah -- why is LOCK SEQUENCE foo_seq not allowed? Seems a simple thing to have. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: patch review : Add ability to constrain backend temporary file space
On 01/06/11 09:24, Cédric Villemain wrote: Submission review * The patch is not in context diff format. * The patch apply, but contains some extra whitespace. * Documentation is here but not explicit about 'temp tables', maybe worth adding that this won't limit temporary table size ? * There is no test provided. One can be expected to check that the feature work. I've created a new patch (attached) with 'git diff -c' so hopefully the format is ok now. I've added a paragraph about how temporary *table* storage is not constrained, only temp work files. I made the point that the *query* that creates a temp table will have its work files constrained too. I've added a test too. The major visible change is that the guc has been renamed to 'work_disk', to gel better with the idea that it is the disk spill for 'work_mem'. Code review = * in fd.c, I think that "temporary_files_size -= (double)vfdP->fileSize;" should be done later in the function once we have successfully unlink the file, not before. Agreed, I've moved it. * I am not sure it is better to add a fileSize like you did or use relationgetnumberofblock() when file is about to be truncated or unlinked, this way the seekPos should be enough to increase the global counter. The temp files are not relations so I'd have to call stat I guess. Now truncate/unlink can happen quite a lot (e.g hash with many files) and I wanted to avoid adding too many library calls to this code for performance reasons, so on balance I'm thinking it is gonna be more efficient to remember the size in the Vfd. * temporary_files_size, I think it is better to have a number of pages à la postgresql than a kilobyte size The temp file related stuff is worked on in bytes or kbytes in the fd.c and other code (e.g log_temp_files), so it seems more natural to stay in kbytes. Also work_disk is really only a spillover from work_mem (in kbytes) so seems logical to match its units. * max_temp_files_size, I'll prefer an approach like shared_buffers GUC: you can use pages, or KB, MB, ... We can use KB, MB, GB - just not pages, again like work_mem. These files are not relations so I'm not sure pages is an entirely appropriate unit for them. Simple Feature test == either explain buffers is wrong or the patch is wrong: cedric=# explain (analyze,buffers) select * from foo order by 1 desc ; QUERY PLAN - Sort (cost=10260.02..10495.82 rows=94320 width=4) (actual time=364.373..518.940 rows=10 loops=1) Sort Key: generate_series Sort Method: external merge Disk: 1352kB Buffers: local hit=393, temp read=249 written=249 -> Seq Scan on foo (cost=0.00..1336.20 rows=94320 width=4) (actual time=0.025..138.754 rows=10 loops=1) Buffers: local hit=393 Total runtime: 642.874 ms (7 rows) cedric=# set max_temp_files_size to 1900; SET cedric=# explain (analyze,buffers) select * from foo order by 1 desc ; ERROR: aborting due to exceeding max temp files size STATEMENT: explain (analyze,buffers) select * from foo order by 1 desc ; ERROR: aborting due to exceeding max temp files size Do you have some testing method I can apply to track that without explain (analyze, buffers) before going to low-level monitoring ? We're looking at this... Architecture review == max_temp_files_size is used for the global space used per backend. Based on how work_mem work I expect something like "work_disk" to limit per file and maybe a backend_work_disk (and yes maybe a backend_work_mem ?!) per backend. So I propose to rename the current GUC to something like backend_work_disk. Done - 'work_disk' it is to match 'work_mem'. Patch is not large and easy to read. I like the idea and it sounds useful. Great! Cheers Mark temp-files-v3.patch.gz Description: GNU Zip compressed 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] Another issue with invalid XML values
On Jun2, 2011, at 00:02 , Noah Misch wrote: > On Wed, Jun 01, 2011 at 06:16:21PM +0200, Florian Pflug wrote: >> Anyway, I'll try to come up with a patch that replaces >> xmlSetGenericErrorFunc() with xmlSetStructuredErrorFunc(). > > Sounds sensible. Will this impose any new libxml2 version dependency? xmlSetStructuredErrorFunc() seems to be available starting with libxml 2.6.0, release on Oct 20, 2003. Since we already require the version to be >= 2.6.23, we should be OK. I won't have access to my PC the next few days, but I'll try to come up with a patch some time next week. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgpool versus sequences
> Maybe. How hard would it be to fix that so it doesn't blow up? What > I don't like about the proposed solution is that it will cause very > user-visible breakage as a result of a minor release upgrade, for > anyone using pgpool, which is a lot of people; unless pgpool is > upgraded to a sufficiently new version first. Thanks for concerning pgpool and pgpool users. BTW, there two pgpool-II versions: - pgpool-II 2.x. uses table lock. has conflict problem with autovacuum if the target table is fairly large. - pgpool-II 3.x. uses sequence row lock to avoid the autovacuum problem. However now it has XID-wrapwround problem and Tom's fix. So both versions are having problem at this point. Yesterday advisory locking was suggested, but after thinking while, it seems using advisory locking make fragile. So I'm still looking for other ways. Probably creating a "secret" relation and acquire table locking on it is the way to go. This is essentially a dirty alternative for sequence table locking. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] pgpool versus sequences
> If we're going to try to retroactively make the world safe for pgpool > doing what it's doing, the only way is to start including sequences in > the set of objects that are vacuumed and included in > relfrozenxid/datfrozenxid bookkeeping. Which is a lot more overhead > than I think is justified to clean up after a bad decision. I'm not > even terribly sure that it would work, since nobody has ever looked at > what would happen if nextval executed concurrently with vacuum doing > something to a sequence. The relfrozenxid logic might have some > difficulty with sequences that have zero relfrozenxid to start with, > too. What pgpool really wanted to do was locking sequence tables, not locking rows in sequences. I wonder why the former is not allowed. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Bad UI design: pg_ctl and data_directory
Alvaro Herrera writes: > Excerpts from Robert Haas's message of mié jun 01 18:22:56 -0400 2011: >> ISTM that it would be useful to run postgres in a mode where it >> doesn't actually try to start up the database, but parses >> postgresql.conf and then exits, perhaps printing out the value of a >> certain GUC as it does so. In this case, data_directory. > I had the same thought, and wondered if we could use the feature > elsewhere. This was suggested quite some time ago, IIRC, but we never got round to it. The main problem in the current context is that it only fixes the issue so long as you ignore the possibility that relevant values were specified on the command line or via environment variables, rather than coming directly from the config file. PGDATA is thus a particular hazard here: all you need is to be running with a different PGDATA setting in your environment than was used when "pg_ctl start" was issued, and you're hosed. 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] Bad UI design: pg_ctl and data_directory
Excerpts from Robert Haas's message of mié jun 01 18:22:56 -0400 2011: > ISTM that it would be useful to run postgres in a mode where it > doesn't actually try to start up the database, but parses > postgresql.conf and then exits, perhaps printing out the value of a > certain GUC as it does so. In this case, data_directory. I had the same thought, and wondered if we could use the feature elsewhere. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgpool versus sequences
Robert Haas writes: > On Wed, Jun 1, 2011 at 6:04 PM, Tom Lane wrote: >> * Does anyone want to argue for not forbidding SELECT FOR UPDATE on >> toast tables? > Maybe. How hard would it be to fix that so it doesn't blow up? What > I don't like about the proposed solution is that it will cause very > user-visible breakage as a result of a minor release upgrade, for > anyone using pgpool, which is a lot of people; unless pgpool is > upgraded to a sufficiently new version first. I think you are answering a different question than what I asked. I was asking about the not-strictly-necessary forbidding of SFU on toast tables, not sequences. If we're going to try to retroactively make the world safe for pgpool doing what it's doing, the only way is to start including sequences in the set of objects that are vacuumed and included in relfrozenxid/datfrozenxid bookkeeping. Which is a lot more overhead than I think is justified to clean up after a bad decision. I'm not even terribly sure that it would work, since nobody has ever looked at what would happen if nextval executed concurrently with vacuum doing something to a sequence. The relfrozenxid logic might have some difficulty with sequences that have zero relfrozenxid to start with, too. Please note also that what pgpool users have got right now is a time bomb, which is not better than immediately-visible breakage. I would prefer to try to get this change out ahead of widespread adoption of the broken pgpool version. 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] [PERFORM] Hash Anti Join performance degradation
I'd like to thank you all for getting this analyzed, especially Tom! Your rigor is pretty impressive. Seems like otherwise it'd impossible to maintain a DBS, though. In the end, I know a lot more of postgres internals and that this idiosyncrasy (from a user perspective) could happen again. I guess it is my first time where I actually encountered an unexpected worst case scenario like this... Seems it is up to me know to be a bit more creative with query optimzation. And in the end, it'll turn out to require an architectural change... As the only thing to achieve is in fact to obtain the last id (currently still with the constraint that it has to happen in an isolated subquery), i wonder whether this requirement (obtaining the last id) is worth a special technique/instrumentation/strategy ( lacking a good word here), given the fact that this data has a full logical ordering (in this case even total) and the use case is quite common I guess. Some ideas from an earlier post: panam wrote: > > ... > This also made me wonder how the internal plan is carried out. Is the > engine able to leverage the fact that a part/range of the rows ["/index > entries"] is totally or partially ordered on disk, e.g. using some kind of > binary search or even "nearest neighbor"-search in that section (i.e. a > special "micro-plan" or algorithm)? Or is the speed-up "just" because > related data is usually "nearby" and most of the standard algorithms work > best with clustered data? > If the first is not the case, would that be a potential point for > improvement? Maybe it would even be more efficient, if there were some > sort of constraints that guarantee "ordered row" sections on the disk, > i.e. preventing the addition of a row that had an index value in between > two row values of an already ordered/clustered section. In the simplest > case, it would start with the "first" row and end with the "last" row (on > the time of doing the equivalent of "cluster"). So there would be a small > list saying rows with id x - rows with id y are guaranteed to be ordered > on disk (by id for example) now and for all times. > Maybe I am completely off the mark but what's your conclusion? To much effort for small scenarios? Nothing that should be handled on a DB level? A try to battle the laws of thermodynamics with small technical dodge? Thanks again panam -- View this message in context: http://postgresql.1045698.n5.nabble.com/Re-PERFORM-Hash-Anti-Join-performance-degradation-tp4443803p4446629.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] vacuum and row type
Teodor Sigaev writes: > I found problem while vacuuming with composite type (version 9.0.4). It's not > so > easy to reproduce, but it's clear what happens. > CREATE TYPE mytype AS (p point, r float8); > CREATE TABLE mytable (mt mytype); > -- create opclass fir GiST > CREATE INDEX myidx ON mytable USING gist (mt); > And vacuum fails with message: > ERROR: could not identify a comparison function for type point It's worse than that, actually: you'll get the same failure from ANALYZE even without the GIST index, as long as there's some data in the column. And even if you try to make ANALYZE back off to use compute_minimal_stats, it still fails, because there's no btree equality for type point either. We've also seen similar failures in respect to things like the planner trying to use sorting with unsortable composite types. So this issue isn't really specific to ANALYZE. I'm inclined to think that the most reasonable fix is to make get_sort_group_operators() and related functions recursively verify whether the component types can be compared before they claim that record_eq, array_eq, etc can be used. So that would require special cases for composites and arrays in those functions, but at least we'd not need to hack up all their callers. 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] pgpool versus sequences
On Wed, Jun 1, 2011 at 6:04 PM, Tom Lane wrote: > I wrote: > I think the most appropriate solution may be to disallow SELECT FOR > UPDATE/SHARE on sequences ... so if you have a good reason why we > shouldn't do so, please explain it. > > Attached is a proposed patch to close off this hole. I found that > somebody had already inserted code to forbid the case for foreign > tables, so I just extended that idea a bit (by copying-and-pasting > CheckValidResultRel). Questions: > > * Does anyone want to bikeshed on the wording of the error messages? Not particularly. > * Does anyone want to argue for not forbidding SELECT FOR UPDATE on > toast tables? Maybe. How hard would it be to fix that so it doesn't blow up? What I don't like about the proposed solution is that it will cause very user-visible breakage as a result of a minor release upgrade, for anyone using pgpool, which is a lot of people; unless pgpool is upgraded to a sufficiently new version first. -- 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] Bad UI design: pg_ctl and data_directory
> No, it isn't. You're making way too many assumptions about where things > really were and what arguments were given to pg_ctl start. We went > around on this before, which is why it's not "fixed" already. What should I search on? I can't find the relevant discussion. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad UI design: pg_ctl and data_directory
On Wed, Jun 1, 2011 at 5:10 PM, Tom Lane wrote: > Josh Berkus writes: >> pg_ctl -D means different things depending on whether you are calling >> "start" or "stop". For "start", pg_ctl wants the directory >> postgresql.conf is in, and for "stop" it wants the directory >> postmaster.pid is in. This means that if your .conf files are not in >> the same directory as data_directory, you have to write special-case >> code for start and stop. > > Well, the entire business of allowing the config files to be outside the > data directory is bad design/poor UI. It's not pg_ctl that's the main > problem here. > >> Given that having the .conf files in /etc is the default configuration >> for both Red Hat and Debian, this seems like really poor UI design on >> our part. > > I can't speak for Debian, but the above statement is 100% false for Red > Hat. In any case, no RH system has ever expected users to issue pg_ctl > start/stop directly, and I think the same is true for Debian, so the > bizarre design wouldn't matter to us even if the case did apply. > >> It actually seems relatively easy to fix this without breaking >> backwards-compatibility. > > No, it isn't. You're making way too many assumptions about where things > really were and what arguments were given to pg_ctl start. We went > around on this before, which is why it's not "fixed" already. ISTM that it would be useful to run postgres in a mode where it doesn't actually try to start up the database, but parses postgresql.conf and then exits, perhaps printing out the value of a certain GUC as it does so. In this case, data_directory. -- 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 predicate locking on heap -- tuple or row?
Heikki Linnakangas wrote: > On 30.05.2011 17:10, Kevin Grittner wrote: >> This optimization is an original one, not yet appearing in any >> academic papers; Dan and I are both convinced it is safe, and in >> off-list correspondence with Michael Cahill after he left Oracle, >> he said that he discussed this with Alan Fekete and they both >> concur that it is a safe and good optimization. >> >> This optimization is mentioned in the README-SSI file in the >> "Innovations" section. Do you think that file needs to have more on >> the topic? > > Oh, I see this: > >> o We can avoid ever rolling back a transaction when the >> transaction on the conflict *in* side of the pivot is explicitly or >> implicitly READ ONLY unless the transaction on the conflict *out* >> side of the pivot committed before the READ ONLY transaction >> acquired its snapshot. (An implicit READ ONLY transaction is one >> which committed without writing, even though it was not >> explicitly declared to be READ ONLY.) > > Since this isn't coming from the papers, it would be nice to > explain why that is safe. I see that this issue first surfaced on the Wiki page 2 April, 2010, and was never really described in detail on the -hackers list. To ensure that it has some documentation here (in case of any possible IP controversy), I will describe a proof now. For earlier references one could dig around in Wiki history, posted patches during CFs, and the git repository history. I have kept a copy of the repo before the official conversion from CVS. >From many academic papers, there is well-established proof that serialization anomalies can only occur under snapshot isolation when there is a cycle in the graph of apparent order of execution of the transactions, and that in such a cycle the following pattern of rw-dependencies always occurs: Tin - - -> Tpivot - - -> Tout A rw-dependency (also called a rw-conflict) exists when a read by one transaction doesn't see the write of another transaction because the two transactions overlap, regardless of whether the read or the write actually happens first. Since the reader doesn't see the work of the writer, the reader appears to have executed first, regardless of the actual order of snapshot acquisition or commits. The arrows show the apparent order of execution of the transactions -- Tin first, Tout last. Published papers have further proven that the transaction which appears to have executed last of these three must actually commit before either of the others for an anomaly to occur. Tin and Tout may be the same transaction (as in the case of simple write skew), or two distinct transactions. SSI relies on recognition of this "dangerous structure" to decide when to cancel a transaction to preserve data integrity. No attempt is made to complete the cycle from Tout back to Tin. Partly this is because of the expense of doing so -- there could be a long chain of rw-dependencies, wr-dependencies (where the reader *can* see the work of another transaction because it *did* commit first), and ww-dependencies (where the writer is updating a row version left by another transaction, which must have committed first). There is also the uncomfortable possibility that a client application *outside* the database ran a transaction which made some change and, after observing the successful commit of this transaction, is proceeding with the knowledge that it ran before the next transaction is submitted. The novel optimization we've used in the PostgreSQL implementation of SSI is based on the fact that of these four ways of determining which transaction appears to have executed first, all but the rw-dependency are based on the transaction which appears to have executed first committing before the apparent later transaction acquires its snapshot. When you combine this with the fact that the transaction which appears to execute later in a rw-dependency is the one which performed the write, you have the basis of an interesting optimization for READ ONLY transactions. In the above diagram, if Tin is READ ONLY, it cannot have a rw-dependency *in* from some other transaction. The only way Tout can directly appear to have executed before Tin is if Tout committed before Tin acquired its snapshot, so that Tin can read something Tout wrote, or an external application can know that it successfully committed Tout before beginning Tin. The published conditions must all still hold -- Tin and Tout must both overlap Tpivot, the same rw-dependencies must exist, and Tout must still commit first of the three; but when Tin doesn't write, Tout must actually commit *even earlier* -- before Tin gets started -- to have an anomaly. For a proof the question becomes: If Tin does not write and Tin and Tout overlap, can a dependency or chain of dependencies develop which makes it appear that Tout executed before Tin? If this can't happen without creating a dangerous structure around a different pivot, then
Re: [HACKERS] Another issue with invalid XML values
On Wed, Jun 01, 2011 at 06:16:21PM +0200, Florian Pflug wrote: > On Jun1, 2011, at 03:17 , Florian Pflug wrote: > > My nagging suspicion is that libxml reports errors like there via some > > callback function, and only returns a non-zero result if there are > > structural errors in the XML. But my experience with libxml is pretty > > limited, so maybe someone with more experience in this area can shed some > > light on this... > > As it turns out, this is actually the case. Thanks for getting to the bottom of this. I had wondered why, for some versions of libxml2, xml_in would accept things that xmllint rejected. Sounds like the list of errors that actually affect the return code has changed over time. > Anyway, I'll try to come up with a patch that replaces > xmlSetGenericErrorFunc() with xmlSetStructuredErrorFunc(). Sounds sensible. Will this impose any new libxml2 version dependency? -- 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] pgpool versus sequences
I wrote:
I think the most appropriate solution may be to disallow SELECT FOR
UPDATE/SHARE on sequences ... so if you have a good reason why we
shouldn't do so, please explain it.
Attached is a proposed patch to close off this hole. I found that
somebody had already inserted code to forbid the case for foreign
tables, so I just extended that idea a bit (by copying-and-pasting
CheckValidResultRel). Questions:
* Does anyone want to bikeshed on the wording of the error messages?
* Does anyone want to argue for not forbidding SELECT FOR UPDATE on
toast tables?
regards, tom lane
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 620efda838421a94a9835a7e8db1d8fa4b50e1ea..2d491fe6c876e34bd757ab271fada1f959707447 100644
*** a/src/backend/executor/execMain.c
--- b/src/backend/executor/execMain.c
*** ExecutorCheckPerms_hook_type ExecutorChe
*** 74,79
--- 74,80
/* decls for local routines only used within this module */
static void InitPlan(QueryDesc *queryDesc, int eflags);
+ static void CheckValidMarkRel(Relation rel, RowMarkType markType);
static void ExecPostprocessPlan(EState *estate);
static void ExecEndPlan(PlanState *planstate, EState *estate);
static void ExecutePlan(EState *estate, PlanState *planstate,
*** InitPlan(QueryDesc *queryDesc, int eflag
*** 837,848
break;
}
! /* if foreign table, tuples can't be locked */
! if (relation && relation->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("SELECT FOR UPDATE/SHARE cannot be used with foreign table \"%s\"",
! RelationGetRelationName(relation;
erm = (ExecRowMark *) palloc(sizeof(ExecRowMark));
erm->relation = relation;
--- 838,846
break;
}
! /* Check that relation is a legal target for marking */
! if (relation)
! CheckValidMarkRel(relation, rc->markType);
erm = (ExecRowMark *) palloc(sizeof(ExecRowMark));
erm->relation = relation;
*** InitPlan(QueryDesc *queryDesc, int eflag
*** 977,982
--- 975,982
* In most cases parser and/or planner should have noticed this already, but
* let's make sure. In the view case we do need a test here, because if the
* view wasn't rewritten by a rule, it had better have an INSTEAD trigger.
+ *
+ * Note: when changing this function, see also CheckValidMarkRel.
*/
void
CheckValidResultRel(Relation resultRel, CmdType operation)
*** CheckValidResultRel(Relation resultRel,
*** 1048,1053
--- 1048,1104
}
/*
+ * Check that a proposed rowmark target relation is a legal target
+ *
+ * In most cases parser and/or planner should have noticed this already, but
+ * they don't cover all cases.
+ */
+ static void
+ CheckValidMarkRel(Relation rel, RowMarkType markType)
+ {
+ switch (rel->rd_rel->relkind)
+ {
+ case RELKIND_RELATION:
+ /* OK */
+ break;
+ case RELKIND_SEQUENCE:
+ /* Must disallow this because we don't vacuum sequences */
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot lock rows in sequence \"%s\"",
+ RelationGetRelationName(rel;
+ break;
+ case RELKIND_TOASTVALUE:
+ /* We could allow this, but there seems no good reason to */
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot lock rows in TOAST relation \"%s\"",
+ RelationGetRelationName(rel;
+ break;
+ case RELKIND_VIEW:
+ /* Should not get here */
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot lock rows in view \"%s\"",
+ RelationGetRelationName(rel;
+ break;
+ case RELKIND_FOREIGN_TABLE:
+ /* Perhaps we can support this someday, but not today */
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot lock rows in foreign table \"%s\"",
+ RelationGetRelationName(rel;
+ break;
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot lock rows in relation \"%s\"",
+ RelationGetRelationName(rel;
+ break;
+ }
+ }
+
+ /*
* Initialize ResultRelInfo data for one result relation
*
* Caution: before Postgres 9.1, this function included the relkind checking
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: pg_terminate_backend and pg_cancel_backend by not administrator user
On Sun, May 29, 2011 at 10:56:02AM -0400, Josh Kupershmidt wrote: > On Sun, May 29, 2011 at 5:04 AM, Noah Misch wrote: > > What risks arise from unconditionally allowing these calls for the same > > user's > > backends? ?`pg_cancel_backend' ought to be safe enough; the user always has > > access to the standard cancellation protocol, making the SQL interface a > > mere > > convenience (albeit a compelling one). ?`pg_terminate_backend' does open up > > access to a new behavior, but no concrete risks come to mind. > > Looking around, I see there were real problems[1] with sending SIGTERM > to individual backends back in 2005 or so, and pg_terminate_backend() > was only deemed safe enough to put in for 8.4 [2]. So expanding > pg_terminate_backend() privileges does make me a tad nervous. The documentation for the CREATE USER flag would boil down to "omit this flag only if you're worried about undiscovered PostgreSQL bugs in this area". I'd echo Tom's sentiment from the first thread, "In any case I think we have to solve it, not create new mechanisms to try to ignore it." > Reading through those old threads made me realize this patch would > give database owners the ability to kill off autovacuum workers. Seems > like we'd want to restrict that power to superusers. Would we? Any old user can already stifle VACUUM by holding a transaction open. -- 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] [PERFORM] Hash Anti Join performance degradation
On Wed, Jun 01, 2011 at 04:58:36PM -0400, Robert Haas wrote: > On Wed, Jun 1, 2011 at 4:47 PM, Tom Lane wrote: > > Robert Haas writes: > >> I guess the real issue here is that m1.id < m2.id has to be evaluated > >> as a filter condition rather than a join qual. > > > > Well, if you can invent an optimized join technique that works for > > inequalities, go for it ... but I think you should get at least a > > PhD thesis out of that. > > Sounds good, except that so far NOT getting a PhD seems like a much > better financial prospect. :-) Yeah, last time I heard of some Uni being so impressed by independent work that they just sort of handed out a Ph.D. it involved a Swiss patent clerk ... Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer & Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- 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] Bad UI design: pg_ctl and data_directory
On Wed, Jun 1, 2011 at 4:10 PM, Tom Lane wrote: > > I can't speak for Debian, but the above statement is 100% false for Red > Hat. In any case, no RH system has ever expected users to issue pg_ctl > start/stop directly, and I think the same is true for Debian, so the > bizarre design wouldn't matter to us even if the case did apply. > that's right, the debian way is pg_ctlcluster -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- 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] Bad UI design: pg_ctl and data_directory
Josh Berkus writes: > pg_ctl -D means different things depending on whether you are calling > "start" or "stop". For "start", pg_ctl wants the directory > postgresql.conf is in, and for "stop" it wants the directory > postmaster.pid is in. This means that if your .conf files are not in > the same directory as data_directory, you have to write special-case > code for start and stop. Well, the entire business of allowing the config files to be outside the data directory is bad design/poor UI. It's not pg_ctl that's the main problem here. > Given that having the .conf files in /etc is the default configuration > for both Red Hat and Debian, this seems like really poor UI design on > our part. I can't speak for Debian, but the above statement is 100% false for Red Hat. In any case, no RH system has ever expected users to issue pg_ctl start/stop directly, and I think the same is true for Debian, so the bizarre design wouldn't matter to us even if the case did apply. > It actually seems relatively easy to fix this without breaking > backwards-compatibility. No, it isn't. You're making way too many assumptions about where things really were and what arguments were given to pg_ctl start. We went around on this before, which is why it's not "fixed" already. 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] [PERFORM] Hash Anti Join performance degradation
On Wed, Jun 1, 2011 at 4:47 PM, Tom Lane wrote: > Robert Haas writes: >> I guess the real issue here is that m1.id < m2.id has to be evaluated >> as a filter condition rather than a join qual. > > Well, if you can invent an optimized join technique that works for > inequalities, go for it ... but I think you should get at least a > PhD thesis out of that. Sounds good, except that so far NOT getting a PhD seems like a much better financial prospect. :-) -- 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] [PERFORM] Hash Anti Join performance degradation
=?ISO-8859-1?Q?C=E9dric_Villemain?= writes: > exact, thanks to your last email I read more the code and get the same > conclusion and put it in a more appropriate place : before > ExecScanHashBucket. > I was about sending it, so it is attached. Applied with cosmetic adjustments. 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] PQdeleteTuple function in libpq
On 6/1/2011 11:43 AM, Pavel Golub wrote: Hello. I'm some kind of PQdeleteTuple function will be very usefull in libpq. Because right now after deleting some record I need refetch result set, or mark tuple as deleted and this is headache for me. IMHO, this should be handled by the application. You could track tuples removed in an int[] or copy the result set into an application defined array of C structures. I've always been under the impression that PGresult objects are immutable once delivered to the application. -- 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] [PERFORM] Hash Anti Join performance degradation
Robert Haas writes: > I guess the real issue here is that m1.id < m2.id has to be evaluated > as a filter condition rather than a join qual. Well, if you can invent an optimized join technique that works for inequalities, go for it ... but I think you should get at least a PhD thesis out of that. 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] [PERFORM] Hash Anti Join performance degradation
On Wed, Jun 1, 2011 at 4:35 PM, Tom Lane wrote: > Robert Haas writes: >> On Wed, Jun 1, 2011 at 4:25 PM, Tom Lane wrote: >>> Because of the way that a bitmap heap scan works, the rows are >>> guaranteed to be loaded into the hash table in physical order, which >>> means (in the fast case) that the row with the largest "id" value gets >>> loaded last. And because ExecHashTableInsert pushes each row onto the >>> front of its hash chain, that row ends up at the front of the hash >>> chain. Net result: for all the outer rows that aren't the one with >>> maximum id, we get a joinqual match at the very first entry in the hash >>> chain. Since it's an antijoin, we then reject that outer row and go >>> on to the next. The join thus ends up costing us only O(N) time. > >> Ah! Make sense. If I'm reading your explanation right, this means >> that we could have hit a similar pathological case on a nestloop as >> well, just with a data ordering that is the reverse of what we have >> here? > > Yeah. It's just chance that this particular data set, with this > particular ordering, happens to work well with a nestloop version > of the query. On average I'd expect nestloop to suck even more, > because of more per-inner-tuple overhead. I guess the real issue here is that m1.id < m2.id has to be evaluated as a filter condition rather than a join qual. That tends to perform poorly in general, which is why rewriting this using min() or max() or ORDER BY .. LIMIT 1 was elsewhere recommended. I've occasionally had cause to join on something other than equality in cases not susceptible to such rewriting, so it would be neat to improve this case, but it's not likely to make it to the top of my list any time soon. -- 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] [PERFORM] Hash Anti Join performance degradation
Robert Haas writes: > On Wed, Jun 1, 2011 at 4:25 PM, Tom Lane wrote: >> Because of the way that a bitmap heap scan works, the rows are >> guaranteed to be loaded into the hash table in physical order, which >> means (in the fast case) that the row with the largest "id" value gets >> loaded last. And because ExecHashTableInsert pushes each row onto the >> front of its hash chain, that row ends up at the front of the hash >> chain. Net result: for all the outer rows that aren't the one with >> maximum id, we get a joinqual match at the very first entry in the hash >> chain. Since it's an antijoin, we then reject that outer row and go >> on to the next. The join thus ends up costing us only O(N) time. > Ah! Make sense. If I'm reading your explanation right, this means > that we could have hit a similar pathological case on a nestloop as > well, just with a data ordering that is the reverse of what we have > here? Yeah. It's just chance that this particular data set, with this particular ordering, happens to work well with a nestloop version of the query. On average I'd expect nestloop to suck even more, because of more per-inner-tuple overhead. 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] [PERFORM] Hash Anti Join performance degradation
2011/6/1 Tom Lane :
> We do need to look into putting a CHECK_FOR_INTERRUPTS call in here
> somewhere, though. I'm inclined to think that right before the
> ExecScanHashBucket is the best place. The reason that nest and merge
> joins don't show a comparable non-responsiveness to cancels is that they
> always call a child plan node at the equivalent place, and ExecProcNode
> has got a CHECK_FOR_INTERRUPTS. So we ought to check for interrupts
> at the point of "fetching a tuple from the inner child plan", and
> ExecScanHashBucket is the equivalent thing in this logic. Cedric's
> suggestion of putting it before the switch would get the job done, but
> it would result in wasting cycles during unimportant transitions from
> one state machine state to another.
exact, thanks to your last email I read more the code and get the same
conclusion and put it in a more appropriate place : before
ExecScanHashBucket.
I was about sending it, so it is attached.
>
> regards, tom lane
>
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
commit 597f6857ecb98572171a96c08a3be434362ac70f
Author: Cédric Villemain
Date: Wed Jun 1 21:30:56 2011 +0200
Add a CHECK_FOR_INTERRUPTS() in the hash(anti)join
The CHECK is in one place where the loop happens really.
It looks enough to break if it is signaled while not executed for each case
in the upper for(;;)
diff --git a/src/backend/executor/nodeHashjoin.c b/src/backend/executor/nodeHashjoin.c
index 26da3b2..cef19d8 100644
--- a/src/backend/executor/nodeHashjoin.c
+++ b/src/backend/executor/nodeHashjoin.c
@@ -14,6 +14,7 @@
*/
#include "postgres.h"
+#include "miscadmin.h"
#include "executor/executor.h"
#include "executor/hashjoin.h"
@@ -264,7 +265,9 @@ ExecHashJoin(HashJoinState *node)
/*
* Scan the selected hash bucket for matches to current outer
+ * And let the backend give up if it has been signaled.
*/
+CHECK_FOR_INTERRUPTS();
if (!ExecScanHashBucket(node, econtext))
{
/* out of matches; check for possible outer-join fill */
--
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] [BUGS] BUG #6034: pg_upgrade fails when it should not.
Florian Pflug writes: > On Jun1, 2011, at 20:28 , Peter Eisentraut wrote: >> Well, initdb still succeeds if you give it an invalid locale name. It >> warns, but that can easily be missed if initdb is hidden behind a few >> other layers. If you then run pg_upgrade, you get a hosed instance. > Whats the rational behind that behaviour? Wouldn't it be more user-friendly > if initdb failed outright? It'd also be consistent with CREATE DATABASE... I think we were being conservative about whether initdb would get it right. Might be time to stiffen our spines a bit, now that the logic has been through a few release cycles. 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] [PERFORM] Hash Anti Join performance degradation
On Wed, Jun 1, 2011 at 4:25 PM, Tom Lane wrote: > Because of the way that a bitmap heap scan works, the rows are > guaranteed to be loaded into the hash table in physical order, which > means (in the fast case) that the row with the largest "id" value gets > loaded last. And because ExecHashTableInsert pushes each row onto the > front of its hash chain, that row ends up at the front of the hash > chain. Net result: for all the outer rows that aren't the one with > maximum id, we get a joinqual match at the very first entry in the hash > chain. Since it's an antijoin, we then reject that outer row and go > on to the next. The join thus ends up costing us only O(N) time. Ah! Make sense. If I'm reading your explanation right, this means that we could have hit a similar pathological case on a nestloop as well, just with a data ordering that is the reverse of what we have here? -- 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] [PERFORM] Hash Anti Join performance degradation
Robert Haas writes: > On Tue, May 31, 2011 at 11:47 PM, Tom Lane wrote: >> I'd just write it off as being a particularly stupid way to find the >> max(), except I'm not sure why deleting just a few thousand rows >> improves things so much. It looks like it ought to be an O(N^2) >> situation, so the improvement should be noticeable but not amazing. > Yeah, this is what I was getting at, though perhaps I didn't say it > well. If the last 78K rows were particularly pathological in some > way, that might explain something, but as far as one can see they are > not a whole heck of a lot different from the rest of the data. Well, I wasted a lot more time on this than I should have, but the answer is: it's a pathological data ordering. The given data file loads the message rows approximately in "id" order, and in fact once you lop off the ones with id > 255, it's sufficiently in order that the highest id is also physically last in the table, at least for all of the box_ids that have large numbers of entries. Now, the tested query plan loads the hash table like this: -> Hash (cost=13685.86..13685.86 rows=28511 width=16) (actual time=176.286..176.286 rows=211210 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 9901kB -> Bitmap Heap Scan on message m2 (cost=537.47..13685.86 rows=28511 width=16) (actual time=23.204..124.624 rows=211210 loops=1) Recheck Cond: (box_id = $1) -> Bitmap Index Scan on "message_box_Idx" (cost=0.00..530.34 rows=28511 width=0) (actual time=21.974..21.974 rows=211210 loops=1) Index Cond: (box_id = $1) Because of the way that a bitmap heap scan works, the rows are guaranteed to be loaded into the hash table in physical order, which means (in the fast case) that the row with the largest "id" value gets loaded last. And because ExecHashTableInsert pushes each row onto the front of its hash chain, that row ends up at the front of the hash chain. Net result: for all the outer rows that aren't the one with maximum id, we get a joinqual match at the very first entry in the hash chain. Since it's an antijoin, we then reject that outer row and go on to the next. The join thus ends up costing us only O(N) time. However, with the additional rows in place, there are a significant number of outer rows that don't get a match at the first hash chain entry, and we're spending more like O(N^2) time. I instrumented things for the specific case of box_id = 69440, which is the most common box_id, and got these results: 2389 got match of join quals at probe 208077 1 got match of join quals at probe 1 175 got match of join quals at probe 208077 273 got match of join quals at probe 1 21 got match of join quals at probe 208077 1 got match of join quals at probe 1 24 got match of join quals at probe 208077 6 got match of join quals at probe 1 157 got match of join quals at probe 208077 1 got match of join quals at probe 1 67 got match of join quals at probe 208077 18 got match of join quals at probe 1 1 generate null-extended tuple after 211211 probes 208075 got match of join quals at probe 1 1 got match of join quals at probe 208077 (This is a "uniq -c" summary of a lot of printfs, so the first column is the number of consecutive occurrences of the same printout.) Even though a large majority of the outer rows still manage to find a match at the first probe, there remain about 2800 that don't match there, because they've got pretty big ids, and so they traipse through the hash chain until they find the genuinely largest id, which is unfortunately way down there the 208077'th chain entry in fact. That results in about half a billion more ExecScanHashBucket and ExecQual calls than occur in the easy case (and that's just for this one box_id). So it's not that the full data set is pathologically bad, it's that the reduced set is pathologically good. O(N^2) performance is what you should expect for this query, and that's what you're actually getting with the full data set. Also, I noted earlier that performance seemed a good deal better with a NestLoop plan. The reason for that is that NestLoop doesn't have the reversal of inner row ordering that's caused by prepending entries to the hash chain, so the very largest row id isn't 208077 entries into the list for it, but only 211211-208077 = 3134 entries in. But it still manages to eliminate most outer rows at the first probe, because there's a fairly large value at that end of the dataset too. I don't see anything much that we could or should do about this. It just depends on the order in which things appear in the hash chain, and even if we fooled around with that ordering, we'd only be moving the improbably-lucky behavior from one case to some other case. We do need to look into putting a CHECK_FOR_INTERRUPTS call in here somewhere, though. I'm inclined to think that right before the
[HACKERS] BLOB support
Hello, I partialy implemented following missing LOBs types. Requirement for this was to give ability to create (B/C)LOB columns and add casting functionality e.g. SET my_clob = 'My long text'. Idea is as follow: 0. Blob is two state object: 1st in memory contains just bytea, serialized contains Oid of large object. 1. Each type has additional boolean haslobs, which is set recursivly. 2. Relation has same bool haslobs (used to speed up tables without LOBs) 3. When data are inserted/updated then "special" function is called and tuple is modified in this way all LOBs are serialized to (old) LOB table and just Oid is stored. 4. When removed LOB is removed from (old) LOB table. I have implemented: - 0. - 1. and 2. without altering relations and types. - 3. Inserts only, Updates need polishing. No recursion (no support for BLOBs in composites or arrays). Current patch is here (many changes to pg_types.h - new column haslobs added), it's in _early_ stage for those who wants to look inside it. Any ideas or suggestions? Regards, Radek P. S. I'm during removal, and I have limited access to Internet. blob_20110601.patch.bz2 Description: application/bzip -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bad UI design: pg_ctl and data_directory
All, pg_ctl -D means different things depending on whether you are calling "start" or "stop". For "start", pg_ctl wants the directory postgresql.conf is in, and for "stop" it wants the directory postmaster.pid is in. This means that if your .conf files are not in the same directory as data_directory, you have to write special-case code for start and stop. Given that having the .conf files in /etc is the default configuration for both Red Hat and Debian, this seems like really poor UI design on our part. Also, it makes the "data_directory" parameter somewhat disingenous, because effectively that parameter doesn't work all the time. It actually seems relatively easy to fix this without breaking backwards-compatibility. We should add this logic to "pg_ctl stop": 1) if postmaster.pid is in -D, shut down that pid. 2) if postmaster.pid is not in -D, look for postgresql.conf 3) if postgresql.conf is found, look in data_directory for postmaster.pid This would end the pg_ctl breakage on our most popular platforms, while not changing any functionality for those already installed. Objections? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #6034: pg_upgrade fails when it should not.
On Jun1, 2011, at 20:28 , Peter Eisentraut wrote: > Well, initdb still succeeds if you give it an invalid locale name. It > warns, but that can easily be missed if initdb is hidden behind a few > other layers. If you then run pg_upgrade, you get a hosed instance. Whats the rational behind that behaviour? Wouldn't it be more user-friendly if initdb failed outright? It'd also be consistent with CREATE DATABASE... template1=# create database test lc_collate 'invalid' ; ERROR: invalid locale name invalid best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #6034: pg_upgrade fails when it should not.
On ons, 2011-06-01 at 13:21 -0400, Tom Lane wrote: > Peter Eisentraut writes: > >>> I think you misread what I wrote, or I misexplained it, but never > >>> mind. Matching locale names case-insensitively sounds reasonable to > >>> me, unless someone has reason to believe it will blow up. > > > On FreeBSD, locale names appear to be case-sensitive: > > > $ LC_ALL=en_US.UTF-8 locale charmap > > UTF-8 > > > $ LC_ALL=en_US.utf-8 locale charmap > > US-ASCII > > Hm, surely the latter result indicates that "en_US.utf-8" is not in fact > a valid locale name? > > It would only be a problem if different case-foldings of the same name > represented valid but different locales on some platform, and that seems > rather hard to believe (it would be a pretty foolish choice no?). Well, initdb still succeeds if you give it an invalid locale name. It warns, but that can easily be missed if initdb is hidden behind a few other layers. If you then run pg_upgrade, you get a hosed instance. -- 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] storing TZ along timestamps
On 05/28/2011 02:58 PM, Peter Eisentraut wrote:
On fre, 2011-05-27 at 16:57 -0700, Steve Crawford wrote:
And the second case is already well handled. In fact calendaring is a
great example. I enter the time for the teleconference and PG nicely
uses my default timezone to store the point-in-time. When you
retrieve
it, it is shown in your timezone and we both pick up the phone at the
correct time. And if I know I'll be somewhere else at that time, I
just
ask for the data in that zone. Altering the data type gains nothing.
How about a recurring appointment that happens every Tuesday whenever it
is 9:00am in California, independent of DST (in California or where ever
the participant actually is). I'm not sure how to solve that within the
SQL framework. You might need to use time with time zone with a
placeholder timezone, and then a rule that date + time with time zone
creates a timestamp with time zone that resolves the time zone for that
particular day.
Interval math is pretty smart about that:
select '2011-05-31 09:00'::timestamp at time zone 'PST8PDT' + ('7
days'::interval * generate_series(1,60));
2011-06-07 09:00:00-07
2011-06-14 09:00:00-07
2011-06-21 09:00:00-07
2011-06-28 09:00:00-07
2011-07-05 09:00:00-07
2011-07-12 09:00:00-07
2011-07-19 09:00:00-07
2011-07-26 09:00:00-07
2011-08-02 09:00:00-07
2011-08-09 09:00:00-07
2011-08-16 09:00:00-07
2011-08-23 09:00:00-07
2011-08-30 09:00:00-07
2011-09-06 09:00:00-07
2011-09-13 09:00:00-07
2011-09-20 09:00:00-07
2011-09-27 09:00:00-07
2011-10-04 09:00:00-07
2011-10-11 09:00:00-07
2011-10-18 09:00:00-07
2011-10-25 09:00:00-07
2011-11-01 09:00:00-07
2011-11-08 09:00:00-08
2011-11-15 09:00:00-08
2011-11-22 09:00:00-08
2011-11-29 09:00:00-08
2011-12-06 09:00:00-08
2011-12-13 09:00:00-08
2011-12-20 09:00:00-08
2011-12-27 09:00:00-08
2012-01-03 09:00:00-08
2012-01-10 09:00:00-08
2012-01-17 09:00:00-08
2012-01-24 09:00:00-08
2012-01-31 09:00:00-08
2012-02-07 09:00:00-08
2012-02-14 09:00:00-08
2012-02-21 09:00:00-08
2012-02-28 09:00:00-08
2012-03-06 09:00:00-08
2012-03-13 09:00:00-07
2012-03-20 09:00:00-07
2012-03-27 09:00:00-07
2012-04-03 09:00:00-07
2012-04-10 09:00:00-07
2012-04-17 09:00:00-07
2012-04-24 09:00:00-07
2012-05-01 09:00:00-07
2012-05-08 09:00:00-07
2012-05-15 09:00:00-07
2012-05-22 09:00:00-07
2012-05-29 09:00:00-07
...
Or if you have to call in from London (notice the blips between 4pm and
5pm due to London and California switching to/from DST on different dates):
select ('2011-05-31 09:00'::timestamp at time zone 'PST8PDT' + ('7
days'::interval * generate_series(1,60))) at time zone 'Europe/London';
-
2011-06-07 17:00:00
2011-06-14 17:00:00
2011-06-21 17:00:00
2011-06-28 17:00:00
2011-07-05 17:00:00
2011-07-12 17:00:00
2011-07-19 17:00:00
2011-07-26 17:00:00
2011-08-02 17:00:00
2011-08-09 17:00:00
2011-08-16 17:00:00
2011-08-23 17:00:00
2011-08-30 17:00:00
2011-09-06 17:00:00
2011-09-13 17:00:00
2011-09-20 17:00:00
2011-09-27 17:00:00
2011-10-04 17:00:00
2011-10-11 17:00:00
2011-10-18 17:00:00
2011-10-25 17:00:00
2011-11-01 16:00:00
2011-11-08 17:00:00
2011-11-15 17:00:00
2011-11-22 17:00:00
2011-11-29 17:00:00
2011-12-06 17:00:00
2011-12-13 17:00:00
2011-12-20 17:00:00
2011-12-27 17:00:00
2012-01-03 17:00:00
2012-01-10 17:00:00
2012-01-17 17:00:00
2012-01-24 17:00:00
2012-01-31 17:00:00
2012-02-07 17:00:00
2012-02-14 17:00:00
2012-02-21 17:00:00
2012-02-28 17:00:00
2012-03-06 17:00:00
2012-03-13 16:00:00
2012-03-20 16:00:00
2012-03-27 17:00:00
2012-04-03 17:00:00
2012-04-10 17:00:00
2012-04-17 17:00:00
2012-04-24 17:00:00
2012-05-01 17:00:00
2012-05-08 17:00:00
...
Cheers,
Steve
--
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 for new feature: Buffer Cache Hibernation
On Wed, Jun 1, 2011 at 8:58 AM, Jeff Janes wrote: > In the latter case, wouldn't we just trigger the same inefficient > scattered read of the data that normal database operation would > trigger, taking about the same amount of time to reach cache-warmth? If you have a system where you're bandwidth-constrained and processing queries as fast as you can then yes. But if you have an OLTP system where queries come in at a fixed rate and it's latency that matters then there's a big difference. It might take you hours to prime the cache at the rate that queries come in organically and for that whole time every query requires multiple cache misses and multiple seeks and random access reads. Once it's all primed your whole database might actually fit in RAM and require no i/o to serve requests. And it's possible that your system is architected on the assumption that that's the case and performance is inadequate until the whole database is read in. Actually in that extreme case you can probably get away with a few dd commands or perhaps an sql select count(*) on startup. I'm not sure in practice how wide the use case is in the gap between that extreme case and more average cases where the difference isn't so catastrophic. I'm sure there will be people who will say it's big but I would like to see numbers. And I'm not just talking about the usual knee-jerk "lets' see the benchmarks" response. I would love to see metrics on a live database showing users how much of their response time depends on the cache and how that performance varies as the cache gets warmer. Right now I think users are kind of in the dark on cache effectiveness and latency numbers. -- greg -- 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] creating CHECK constraints as NOT VALID
Excerpts from Thom Brown's message of mar may 31 20:18:18 -0400 2011: > test=# CREATE DOMAIN things AS INT CHECK (VALUE > 5); > CREATE DOMAIN > test=# CREATE TABLE abc (id SERIAL, stuff things); > NOTICE: CREATE TABLE will create implicit sequence "abc_id_seq" for > serial column "abc.id" > CREATE TABLE > test=# INSERT INTO abc (stuff) VALUES (55); > INSERT 0 1 > test=# ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11) NOT VALID; > ERROR: column "stuff" of table "abc" contains values that violate the > new constraint > STATEMENT: ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11) > NOT VALID; Okay, fixed that and added ALTER DOMAIN VALIDATE CONSTRAINT too. Thanks for the review. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support 0001-Make-NOT-VALID-constraints-work-on-domains-too.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: [DOCS] [HACKERS] DOCS: SGML identifier may not exceed 44 characters
On 06/01/2011 12:55 PM, Robert Haas wrote: On Wed, Jun 1, 2011 at 3:31 AM, Albe Laurenz wrote: Peter Eisentrautwrote: Excerpts from Brendan Jurd's message of mar may 31 02:17:22 -0400 2011: openjade:information_schema.sgml:828:60:Q: length of name token must not exceed NAMELEN (44) But it seems like no one else has seen this problem yet, so it's quite suspicious, since surely people have built the documentation in the last few months. I have and I encountered the same problem. I didn't bother to report it because my machine and openjade are quite old: The machine is Red Hat Enterprise Linux ES release 3 (Taroon Update 9), and docbook.dcl is from the docbook-dtds-1.0-17.2 RPM. Maybe that really only affects quite old versions, but I think it wouldn't hurt to shorten the tag in question to avoid the problem. +1. Is there no way we can ourselves override this setting if we run into it? If it's possible that seems to me like a much better way to go. 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] [BUGS] BUG #6034: pg_upgrade fails when it should not.
Peter Eisentraut writes: >>> I think you misread what I wrote, or I misexplained it, but never >>> mind. Matching locale names case-insensitively sounds reasonable to >>> me, unless someone has reason to believe it will blow up. > On FreeBSD, locale names appear to be case-sensitive: > $ LC_ALL=en_US.UTF-8 locale charmap > UTF-8 > $ LC_ALL=en_US.utf-8 locale charmap > US-ASCII Hm, surely the latter result indicates that "en_US.utf-8" is not in fact a valid locale name? It would only be a problem if different case-foldings of the same name represented valid but different locales on some platform, and that seems rather hard to believe (it would be a pretty foolish choice no?). So I'm inclined to leave the code alone. 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] patch for new feature: Buffer Cache Hibernation
On Wed, Jun 1, 2011 at 11:58 AM, Jeff Janes wrote: > On Sun, May 15, 2011 at 11:19 AM, Robert Haas wrote: >> I don't think there's any need for this to get data into >> shared_buffers at all. Getting it into the OS cache oughta be plenty >> sufficient, no? >> >> ISTM that a very simple approach here would be to save the contents of >> each shared buffer on clean shutdown, and to POSIX_FADV_WILLNEED those >> buffers on startup. > > Do you mean to save the contents of the buffer pages themselves into a > hibernation file, or to save just the identities (relation/fork/block > number) of the buffers? The latter. > In the first case, getting them into the OS cache would not help > because the kernel would not recognize that data as being equivalent > to the block it is a copy of. > > In the latter case, wouldn't we just trigger the same inefficient > scattered read of the data that normal database operation would > trigger, taking about the same amount of time to reach cache-warmth? > Or is POSIX_FADV_WILLNEED going to be clever about reordering and > coalescing reads? It would be nice if POSIX_FADV_WILLNEED is clever enough to reorder and coalesce, but even if it isn't, we can help it along by doing all the reads from any given file one after another and in increasing block number order. -- 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: [DOCS] [HACKERS] DOCS: SGML identifier may not exceed 44 characters
On Wed, Jun 1, 2011 at 3:31 AM, Albe Laurenz wrote: > Peter Eisentrautwrote: > Excerpts from Brendan Jurd's message of mar may 31 02:17:22 -0400 2011: >> openjade:information_schema.sgml:828:60:Q: length of name token must >> not exceed NAMELEN (44) >> >> But it seems like no one else has seen this problem yet, so it's quite >> suspicious, since surely people have built the documentation in the last >> few months. > > I have and I encountered the same problem. > I didn't bother to report it because my machine and openjade are quite old: > > The machine is Red Hat Enterprise Linux ES release 3 (Taroon Update 9), > and docbook.dcl is from the docbook-dtds-1.0-17.2 RPM. > > Maybe that really only affects quite old versions, but I think it wouldn't > hurt to shorten the tag in question to avoid the problem. +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] Any idea for serializing INSERTING SERIAL column?
On Wed, Jun 1, 2011 at 1:30 AM, Tatsuo Ishii wrote: >> Problem with the advisory lock is, it will not work if the target >> table is empty. > > Oops. I was wrong. the key for advisory lock needs to be a unique > value, but not necessarily a row value in a table. Seems this is the > way I should go(though need to be carefull since the lock is not > released even after a transaction ends). Thanks! 9.1 has a transactional advisory lock (fyi). I'd still consider keeping to the old method because presumably you want the lock to be as short as possible. Another big headache with advisory locks unfortunately is lockspace conflicts between user code and library (you) code -- I've actually seen this in the wild once. Maybe you could use the double int4 version and allow the users to supply the other optional int4? 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] PQdeleteTuple function in libpq
2011/6/1 Pavel Golub :
> Hello.
>
> I'm some kind of PQdeleteTuple function will be very usefull in libpq.
> Because right now after deleting some record I need refetch result
> set, or mark tuple as deleted and this is headache for me.
>
> So I checked fe-exec.c sources and wrote this:
>
> int PQdeleteTuple(PGresult *src, int tup_num)
> {
> if (!src)
> return NULL;
>
> int i,
> field;
>
> /* Invalid tup_num, must be < ntups */
> if (tup_num < 0 || tup_num >= src->ntups)
> return FALSE;
>
> free(src->tuples[tup_num]);
>
> for (i = tup_num; i < src->ntups - 1; i++)
> {
> src->tuples[i] = src->tuples[i + 1];
> }
> src->ntups--;
> return TRUE;
> }
>
> But I'm pretty sure, that "free(src->tuples[tup_num])" is bullshit!
> Because memory is allocated by pqResultAlloc, which in turn plays with
> memory blocks and so on...
>
> Can anyone help me in this?
>
> PS I'm not a C guru, so don't please kick me hard. :)
well, you have PQaddTuple, but this was exposed mainly for the purpose
of building a PQresult from outside the libpq library -- not so much
to remove the 'constness' property of the PGResult. I have no
philosophical objection to making the PGresult able to be manipulated
in that fashion (although others might). You could maybe just NULL
out tuples[i] and add some logic to various places to check that, like
in PQgetvalue.
But before going down that road you need to make the case why this
should be handled in the library and not in your code -- PGresult
memory is slab allocated and therefore can only grow in size -- not
shrink and as such is not so much designed as a general purpose client
side dataset in the high level sense.
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] Another issue with invalid XML values
On Jun1, 2011, at 03:17 , Florian Pflug wrote: > My nagging suspicion is that libxml reports errors like there via some > callback function, and only returns a non-zero result if there are structural > errors in the XML. But my experience with libxml is pretty limited, so maybe > someone with more experience in this area can shed some light on this... As it turns out, this is actually the case. libxml reports some errors (like invalid xmlns attributes) via the error handler set using xmlSetGenericErrorFunc() but still returns zero (indicating success) from xmlCtxtReadDoc() and xmlParseBalancedChunkMemory(). If I modify xml_parse() to complain not only if one of these functions return non-zero, but also if xml_err_buf has non-zero length, invalid xmlns attributes are reported correctly. However, the error function set using xmlSetGenericErrorFunc() cannot distinguish between error and warnings, so doing this causes XMLPARSE() to also complain about things like non-absolute namespace URIs (which are allowed but deprecated as far as I understand). To fix that, xmlSetGenericErrorFunc() would probably have to be replace by xmlSetStructuredErrorFunc(). Structured error functions receive a pointer to an xmlError structore which, amongst other things, contains an xmlErrorLevel (NONE, WARNING, ERROR, FATAL). While digging through the code in src/backend/utils/adt/xml.c, I also noticed that we set a global error handler instead of a per-context one. I guess this is because xmlParseBalancedChunkMemory(), which we use to parse XML fragments, doesn't provide a way to pass in a context but rather creates it itself. Still, I wonder if there isn't some other API which we could use which does allow us to specify a context. Again, it'd be nice if someone more familiar with this code could explain the reasons behind the current design. Anyway, I'll try to come up with a patch that replaces xmlSetGenericErrorFunc() with xmlSetStructuredErrorFunc(). best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch for new feature: Buffer Cache Hibernation
On Sun, May 15, 2011 at 11:19 AM, Robert Haas wrote: > I don't think there's any need for this to get data into > shared_buffers at all. Getting it into the OS cache oughta be plenty > sufficient, no? > > ISTM that a very simple approach here would be to save the contents of > each shared buffer on clean shutdown, and to POSIX_FADV_WILLNEED those > buffers on startup. Do you mean to save the contents of the buffer pages themselves into a hibernation file, or to save just the identities (relation/fork/block number) of the buffers? In the first case, getting them into the OS cache would not help because the kernel would not recognize that data as being equivalent to the block it is a copy of. In the latter case, wouldn't we just trigger the same inefficient scattered read of the data that normal database operation would trigger, taking about the same amount of time to reach cache-warmth? Or is POSIX_FADV_WILLNEED going to be clever about reordering and coalescing reads? Cheers, Jeff -- 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] [BUGS] BUG #6034: pg_upgrade fails when it should not.
On tis, 2011-05-24 at 15:59 -0400, Bruce Momjian wrote: > > I think you misread what I wrote, or I misexplained it, but never > > mind. Matching locale names case-insensitively sounds reasonable to > > me, unless someone has reason to believe it will blow up. > > OK, that's what I needed to hear. I have applied the attached patch, > but only to 9.1 because of the risk of breakage. (This was only the > first bug report of this, and we aren't 100% certain about the case > issue.) On FreeBSD, locale names appear to be case-sensitive: $ LC_ALL=en_US.UTF-8 locale charmap UTF-8 $ LC_ALL=en_US.utf-8 locale charmap US-ASCII $ LC_ALL=de_DE.UTF-8 date Mi 1 Jun 2011 18:22:24 EEST $ LC_ALL=de_DE.utf-8 date Wed Jun 1 18:22:47 EEST 2011 So I think that change needs to be reverted. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PQdeleteTuple function in libpq
Hello.
I'm some kind of PQdeleteTuple function will be very usefull in libpq.
Because right now after deleting some record I need refetch result
set, or mark tuple as deleted and this is headache for me.
So I checked fe-exec.c sources and wrote this:
int PQdeleteTuple(PGresult *src, int tup_num)
{
if (!src)
return NULL;
int i,
field;
/* Invalid tup_num, must be < ntups */
if (tup_num < 0 || tup_num >= src->ntups)
return FALSE;
free(src->tuples[tup_num]);
for (i = tup_num; i < src->ntups - 1; i++)
{
src->tuples[i] = src->tuples[i + 1];
}
src->ntups--;
return TRUE;
}
But I'm pretty sure, that "free(src->tuples[tup_num])" is bullshit!
Because memory is allocated by pqResultAlloc, which in turn plays with
memory blocks and so on...
Can anyone help me in this?
PS I'm not a C guru, so don't please kick me hard. :)
Thanks.
--
With best wishes,
Pavel mailto:pa...@gf.microolap.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] [COMMITTERS] pgsql: Improve corner cases in pg_ctl's new wait-for-postmaster-startup
Fujii Masao writes: > On Wed, Jun 1, 2011 at 12:30 PM, Tom Lane wrote: >>> To address this corner >>> case, we should check whether postmaster is really running by sending >>> the signal 0 after we read postmater.pid file? Attached patch does that. >> I find myself unimpressed by this approach, because it supposes that the >> postmaster got as far as creating postmaster.pid. > Sorry, I could not understand the reason why you were unimpressed. > Could you explain it in a little more detail? [ thinks some more... ] Actually, there's more merit to your suggestion than I saw at first, but it's still got an issue. We can divide postmaster failures into four cases: 1. postmaster fails before creating postmaster.pid, and there was no pre-existing postmaster.pid file 2. postmaster fails before creating postmaster.pid, but there was a pre-existing postmaster.pid file 3. postmaster fails after creating postmaster.pid, and successfully removes postmaster.pid 4. postmaster fails after creating postmaster.pid, and fails to remove postmaster.pid The current HEAD code will detect 1 and 3 (after 5 seconds), and will detect case 2 by virtue of noticing a stale timestamp in the old pidfile; but it will wait till timeout in case 4. If we add your suggestion to what's there now, it will cover case 4. It doesn't cover case 1, and might not cover case 3 (if the pidfile was there for so short a time that we never saw it) but that really isn't a problem because the existing timeout logic handles those cases. The problem I've got with the proposed change is that it's brittle against case 2: it might pick up a PID from a stale pidfile and then conclude that the postmaster died, when actually the postmaster hasn't yet written a new pidfile. However, the existing code is also brittle in this case, because when it sees that the pidfile is stale, it immediately fails. I think we can make it better by simply ignoring a pidfile with a stale timestamp (hoping for it to be overwritten), and remembering the PID to try kill(pid, 0) on from the first time we successfully parse the file. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_listener in 9.0
On 11-06-01 09:30 AM, Christopher Browne wrote: On Wed, Jun 1, 2011 at 8:29 AM, Dave Page wrote: On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstan wrote: The whole point of the revamp was that pg_listener was a major performance bottleneck and needed to go, and without it being gone we would not have got notification payloads. Yeah, I know why it was replaced. That doesn't mean we cannot provide an alternative interface to the same info though (other things might of course). I suspect you're pretty much out of luck. Not me - our users. Note that in Slony 2.1, there's a table called sl_components, which is used to capture the state of the various database connections, checking in as the various threads do their various actions. Also, slon and slonik try to report their respective application, so it can be reported on pg_stat_activity. Slony 2.1 also sets application_name. If this were a big deal for pgAdmin we could consider backporting the application_name change to 2.0.x for users running against 9.0. Slony also has a table called sl_nodelock that each slon process writes adds a row for on startup. This includes the backend pid() for one of the connections. Slony 1.2, 2.0 and 2.1 all use sl_nodelock -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_listener in 9.0
On Wed, Jun 1, 2011 at 5:09 AM, Dave Page wrote: > The pg_listener table was removed in 9.0 in the revamp of > LISTEN/NOTIFY. In pgAdmin we used to perform a number of selects from > the table to get information about Slony clusters - for example, the > PID of the slon process or to check if a process is listening for a > specific notification. This allows the app to indicate to the user if > there is something wrong with their replication cluster. > > I can't find any way to get that information now - any ideas? Although it might not be helpful in your case, you can emulate certain aspects of this with an advisory lock...you can query the lock table for specific locks, and it goes away when the connection dies. 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] pg_listener in 9.0
On Wed, Jun 1, 2011 at 8:29 AM, Dave Page wrote: > On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstan wrote: >> >> The whole point of the revamp was that pg_listener was a major performance >> bottleneck and needed to go, and without it being gone we would not have got >> notification payloads. > > Yeah, I know why it was replaced. That doesn't mean we cannot provide > an alternative interface to the same info though (other things might > of course). > >> I suspect you're pretty much out of luck. > > Not me - our users. Note that in Slony 2.1, there's a table called sl_components, which is used to capture the state of the various database connections, checking in as the various threads do their various actions. Also, slon and slonik try to report their respective application, so it can be reported on pg_stat_activity. -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?" -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_listener in 9.0
On Wed, Jun 1, 2011 at 12:45 PM, Greg Sabino Mullane wrote: > > -BEGIN PGP SIGNED MESSAGE- > Hash: RIPEMD160 > > >> The pg_listener table was removed in 9.0 in the revamp of >> LISTEN/NOTIFY. In pgAdmin we used to perform a number of selects from >> the table to get information about Slony clusters - for example, the >> PID of the slon process or to check if a process is listening for a >> specific notification. This allows the app to indicate to the user if >> there is something wrong with their replication cluster. >> >> I can't find any way to get that information now - any ideas? > > Nope, you are out of luck: the information is locked away and cannot > be seen by other processes. I'm sure of this because Bucardo > went through the same questioning some time ago. We basically rewrote > the app a bit to use the on-disk PID files to replace some of the > lost functionality, and sucked up the rest. :) :-( -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: 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] Cube Index Size
2011/6/1 Teodor Sigaev > > One of idea is add sorting of Datums to be splitted by cost of insertion. > It's implemented in intarray/tsearch GiST indexes. > Yes, it's a good compromise between linear and quadratic entries distribution algorithms. In quadratic algorithm each time entry with maximal difference of inserion cost is inserted. Quadratic algorithm runs slowly than sorting one, but on my tests it shows slightly better results. -- With best regards, Alexander Korotkov.
Re: [HACKERS] pg_listener in 9.0
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > The pg_listener table was removed in 9.0 in the revamp of > LISTEN/NOTIFY. In pgAdmin we used to perform a number of selects from > the table to get information about Slony clusters - for example, the > PID of the slon process or to check if a process is listening for a > specific notification. This allows the app to indicate to the user if > there is something wrong with their replication cluster. > > I can't find any way to get that information now - any ideas? Nope, you are out of luck: the information is locked away and cannot be seen by other processes. I'm sure of this because Bucardo went through the same questioning some time ago. We basically rewrote the app a bit to use the on-disk PID files to replace some of the lost functionality, and sucked up the rest. :) - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201106010838 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk3mNEAACgkQvJuQZxSWSsh8LQCeKD/ot4mvXXd5Lgk4sIHwV0D2 CKsAn3Ub9Bdh0Fuyc0rDZr/OiSD8tkXq =cdCn -END PGP SIGNATURE- -- 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] [PERFORM] Hash Anti Join performance degradation
Tom Lane-2 wrote: > > It looks like it ought to be an O(N^2) > situation, so the improvement should be noticeable but not amazing. > Hm, the performance was reasonable again when doing a cluster... So I believe this should be more a technical than an algorithmical/complexity issue. Maybe it is the way the hashtable is built and that order makes a difference in that case? In short: Why is clustered data not affected? Regards, panam -- View this message in context: http://postgresql.1045698.n5.nabble.com/Re-PERFORM-Hash-Anti-Join-performance-degradation-tp4443803p4445123.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Cube Index Size
Ok, I can reproduce the issue with that. The index is only 4MB in size when I populate it with random data (vs. 15 MB with your data). The command I used is: INSERT INTO cubtest SELECT cube(random(), random()) FROM generate_series(1,2); My guess is that the picksplit algorithm performs poorly with that data. Unfortunately, I have no idea how to improve that. One of idea is add sorting of Datums to be splitted by cost of insertion. It's implemented in intarray/tsearch GiST indexes. Although I'm not sure that it will help but our researches on Guttman's picksplit algorimth show significant improvements. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_listener in 9.0
On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstan wrote: > > The whole point of the revamp was that pg_listener was a major performance > bottleneck and needed to go, and without it being gone we would not have got > notification payloads. Yeah, I know why it was replaced. That doesn't mean we cannot provide an alternative interface to the same info though (other things might of course). > I suspect you're pretty much out of luck. Not me - our users. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: 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] vacuum and row type
Hi! I found problem while vacuuming with composite type (version 9.0.4). It's not so easy to reproduce, but it's clear what happens. CREATE TYPE mytype AS (p point, r float8); CREATE TABLE mytable (mt mytype); -- create opclass fir GiST CREATE INDEX myidx ON mytable USING gist (mt); And vacuum fails with message: ERROR: could not identify a comparison function for type point I added an assert to all such error and got a backtrace: #0 0x000800de8fcc in kill () from /lib/libc.so.7 (gdb) bt #0 0x000800de8fcc in kill () from /lib/libc.so.7 #1 0x000800de7dcb in abort () from /lib/libc.so.7 #2 0x007bb05f in ExceptionalCondition (conditionName=Could not find the frame base for "ExceptionalCondition". ) at assert.c:57 #3 0x0073839a in record_cmp (fcinfo=0x7fffcb80) at rowtypes.c:910 #4 0x00739005 in btrecordcmp (fcinfo=0x7fffcb80) at rowtypes.c:1236 #5 0x007eb63b in myFunctionCall2 (flinfo=0x7fffd170, arg1=34521714600, arg2=34521722960) at tuplesort.c:2506 #6 0x007eb598 in inlineApplySortFunction ( sortFunction=0x7fffd170, sk_flags=0, datum1=34521714600, isNull1=0 '\0', datum2=34521722960, isNull2=0 '\0') at tuplesort.c:2546 #7 0x007eb50a in ApplySortFunction (sortFunction=0x7fffd170, sortFlags=0, datum1=34521714600, isNull1=0 '\0', datum2=34521722960, isNull2=0 '\0') at tuplesort.c:2565 #8 0x0055694f in compare_scalars (a=0x809a9f038, b=0x809a9f048, arg=0x7fffd150) at analyze.c:2702 #9 0x007fd2cc in qsort_arg (a=0x809a9f038, n=611, es=16, cmp=0x5568e0 , arg=0x7fffd150) at qsort_arg.c:129 #10 0x00555bb6 in compute_scalar_stats (stats=0x809a06ca0, fetchfunc=0x554920 , samplerows=611, totalrows=611) at analyze.c:2298 #11 0x0055279a in compute_index_stats (onerel=0x8011ac828, totalrows=611, indexdata=0x8011e10e8, nindexes=1, rows=0x809a0c038, numrows=611, col_context=0x8011ceed8) at analyze.c:764 #12 0x00551eb8 in do_analyze_rel (onerel=0x8011ac828, vacstmt=0x7fffd880, inh=0 '\0') at analyze.c:501 #13 0x00551437 in analyze_rel (relid=16483, vacstmt=0x7fffd880, bstrategy=0x80117c588) at analyze.c:217 #14 0x005b0b52 in vacuum (vacstmt=0x7fffd880, relid=16483, do_toast=0 '\0', bstrategy=0x80117c588, for_wraparound=0 '\0', isTopLevel=1 '\001') at vacuum.c:246 #15 0x00674f06 in autovacuum_do_vac_analyze (tab=0x80117cf88, bstrategy=0x80117c588) at autovacuum.c:2692 #16 0x00674403 in do_autovacuum () at autovacuum.c:2262 So, I think, std_typanalyze() does wrong choice between compute_minimal_stats() and compute_scalar_stats() because row type has defined comparison function ( btrecordcmp() ) but searching of actual set of comparisons functions per row's columns occurs too late - when btrecordcmp() is already started. I don't have in idea how to fix it without massive refactoring. std_typanalyze() should be a bit clever to dig possibility of comparison or compute_scalar_stats() should switch to compute_minimal_stats() if underlying functions fail with such error. Obviously, workaround is a adding dummy comparison function for points. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_listener in 9.0
On 06/01/2011 08:04 AM, Dave Page wrote: On Wed, Jun 1, 2011 at 11:27 AM, Heikki Linnakangas wrote: On 01.06.2011 13:09, Dave Page wrote: The pg_listener table was removed in 9.0 in the revamp of LISTEN/NOTIFY. In pgAdmin we used to perform a number of selects from the table to get information about Slony clusters - for example, the PID of the slon process or to check if a process is listening for a specific notification. This allows the app to indicate to the user if there is something wrong with their replication cluster. I can't find any way to get that information now - any ideas? Hmm, my first thought was that we should add a view to display that information, but that's not possible, because we don't have that information in shared memory. The information on what channels are being listened on is now backend-local. Does the slon process set application_name? You could query pg_stat_activity with that. I don't think so (though I might be wrong), but even if it did, it wouldn't tell us what cluster it was running against (we figure that out by looking at what it's listening for). We also do the same check in reverse, to check there is something listening for specific notifications. The whole point of the revamp was that pg_listener was a major performance bottleneck and needed to go, and without it being gone we would not have got notification payloads. I suspect you're pretty much out of luck. 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] Cube Index Size
On Wed, Jun 1, 2011 at 3:37 PM, Heikki Linnakangas < heikki.linnakan...@enterprisedb.com> wrote: > My guess is that the picksplit algorithm performs poorly with that data. > Unfortunately, I have no idea how to improve that. Current cube picksplit function have no storage utilization guarantees, while original Guttman's picksplit has them (if one of group size reaches some threshold, then all other entries go to another group). Also, current picksplit is mix of Guttman's linear and quadratic algorithms. It picks seeds quadratically, but distributes entries linearly. I see following ways of solving picksplit problem for cube: 1) Add storage utilization guarantees to current picksplit. It may cause increase of overlaps, but should descrease index size. 2) Add storage utilization guarantees to current picksplit and replace entries distribution algorithm to the quadratic one. Picksplit will take more time, but it should give more stable and predictable result. 3) I had some experiments with my own picksplit algorithm, which showed pretty good results on tests which I've run. But current implementation is dirty and it's require more testing. -- With best regards, Alexander Korotkov.
Re: [HACKERS] pg_listener in 9.0
On Wed, Jun 1, 2011 at 11:27 AM, Heikki Linnakangas wrote: > On 01.06.2011 13:09, Dave Page wrote: >> >> The pg_listener table was removed in 9.0 in the revamp of >> LISTEN/NOTIFY. In pgAdmin we used to perform a number of selects from >> the table to get information about Slony clusters - for example, the >> PID of the slon process or to check if a process is listening for a >> specific notification. This allows the app to indicate to the user if >> there is something wrong with their replication cluster. >> >> I can't find any way to get that information now - any ideas? > > Hmm, my first thought was that we should add a view to display that > information, but that's not possible, because we don't have that information > in shared memory. The information on what channels are being listened on is > now backend-local. > > Does the slon process set application_name? You could query pg_stat_activity > with that. I don't think so (though I might be wrong), but even if it did, it wouldn't tell us what cluster it was running against (we figure that out by looking at what it's listening for). We also do the same check in reverse, to check there is something listening for specific notifications. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: 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] [PERFORM] Hash Anti Join performance degradation
On Tue, May 31, 2011 at 11:47 PM, Tom Lane wrote: > Robert Haas writes: >> With respect to the root of the issue (why does the anti-join take so >> long?), my first thought was that perhaps the OP was very unlucky and >> had a lot of values that hashed to the same bucket. But that doesn't >> appear to be the case. > > Well, yes it is. Notice what the subquery is doing: for each row in > "box", it's pulling all matching "box_id"s from message and running a > self-join across those rows. The hash join condition is a complete > no-op. And some of the box_ids have hundreds of thousands of rows. > > I'd just write it off as being a particularly stupid way to find the > max(), except I'm not sure why deleting just a few thousand rows > improves things so much. It looks like it ought to be an O(N^2) > situation, so the improvement should be noticeable but not amazing. Yeah, this is what I was getting at, though perhaps I didn't say it well. If the last 78K rows were particularly pathological in some way, that might explain something, but as far as one can see they are not a whole heck of a lot different from the rest of the data. -- 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] Cube Index Size
On 01.06.2011 10:48, Nick Raj wrote: On Tue, May 31, 2011 at 12:46 PM, Heikki Linnakangas< heikki.linnakan...@enterprisedb.com> wrote: If not, please post a self-contained test case to create and populate the table, so that others can easily try to reproduce it. I have attached .sql file that having 2 tuples Table creation - create table cubtest(c cube); Index creation - create index t on cubtest using gist(c); Ok, I can reproduce the issue with that. The index is only 4MB in size when I populate it with random data (vs. 15 MB with your data). The command I used is: INSERT INTO cubtest SELECT cube(random(), random()) FROM generate_series(1,2); My guess is that the picksplit algorithm performs poorly with that data. Unfortunately, I have no idea how to improve that. -- 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] pg_listener in 9.0
On 01.06.2011 13:09, Dave Page wrote: The pg_listener table was removed in 9.0 in the revamp of LISTEN/NOTIFY. In pgAdmin we used to perform a number of selects from the table to get information about Slony clusters - for example, the PID of the slon process or to check if a process is listening for a specific notification. This allows the app to indicate to the user if there is something wrong with their replication cluster. I can't find any way to get that information now - any ideas? Hmm, my first thought was that we should add a view to display that information, but that's not possible, because we don't have that information in shared memory. The information on what channels are being listened on is now backend-local. Does the slon process set application_name? You could query pg_stat_activity with that. -- 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] Getting a bug tracker for the Postgres project
On 05/31/2011 05:41 PM, Alvaro Herrera wrote: Excerpts from Josh Berkus's message of mar may 31 17:05:23 -0400 2011: BTW, we talked to Debian about debbugs ages ago, and the Debian project said that far too much of debbugs was not portable to other projects. The good news is that the GNU folk proved them wrong, as evidenced elsewhere in the thread. What happened is that one of the authors got motivated (not sure why/how) to put a major amount of work into making the code portable so that sites other than Debian could use it. So past perceptions about it being really hard were correct, that's just been fixed since then. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_listener in 9.0
The pg_listener table was removed in 9.0 in the revamp of LISTEN/NOTIFY. In pgAdmin we used to perform a number of selects from the table to get information about Slony clusters - for example, the PID of the slon process or to check if a process is listening for a specific notification. This allows the app to indicate to the user if there is something wrong with their replication cluster. I can't find any way to get that information now - any ideas? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: 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] patch for new feature: Buffer Cache Hibernation
On 06/01/2011 03:03 AM, Tatsuo Ishii wrote: Also I really want to see the performance comparison between these two approaches in the real world database. Well, tell me how big of a performance improvement you want PgFincore to win by, and I'll construct a benchmark where it does that. If you pick a database size that fits in the OS cache, but is bigger than shared_buffers, the difference between the approaches is huge. The opposite--trying to find a case where this hibernation approach wins--is extremely hard to do. Anyway, further discussion of this patch is kind of a waste right now. We've never gotten the patch actually sent to the list to establish a proper contribution (just pointers to a web page), and no feedback on that or other suggestions for redesign (extension repackaging, GUC renaming, removing unused code, and a few more). Unless the author shows up again in the next two weeks, this is getting bounced back with no review as code we can't use. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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 for new feature: Buffer Cache Hibernation
2011/6/1 Tatsuo Ishii : >> Yeah, I'm pretty well convinced this whole approach is a dead end. >> Priming the OS buffer cache seems way more useful. I also think >> saving the blocks to be read rather than the actual blocks makes a lot >> more sense. > > Well, his proposal works on any platforms PostgreSQL supports. On the > other hand PgFincore works on Linux only. Who wants Linux only tool be > in core? I don't want to compete the features here. Just for the completeness: PgFincore 'snapshot' is possible on any platform supporting mincure() (most support it, for widows alternatives exists). For restoring, it can be a ReadBuffer for postgresql cache; for OS it can be an open(),read(X), read (Y), close() *or* posix_fadvise() which can be less destructive (I did only via posix_fadv but nothing prevent to change that when posix support is not present). And we already have linux-only feature in-core, fortunately because it is usefull feature and I really like to add more posix_fadvise call (*this* will really help read and cache strategy more than any hack we can do to try to workaround kernel decisions) Note that BSD developers can change that and make posix_fadvise work: it has been sitting in their TODO list since some years now. Anyway we need this patch on-list to go ahead. > > Also I really want to see the performance comparison between these two > approaches in the real world database. > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > English: http://www.sraoss.co.jp/index_en.php > Japanese: http://www.sraoss.co.jp > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Getting a bug tracker for the Postgres project
On Wed, Jun 1, 2011 at 10:43, Dimitri Fontaine wrote: > Alvaro Herrera writes: >>> http://archives.beccati.org/ >>> >>> It uses AOX (http://aox.org/) and as such is baked by a PostgreSQL >>> database. The mails threading view is even a CTE. >> >> Yeah, it's great. Last time I heard, though, Mateo wasn't open to doing >> any more work on it (including fixing a bunch of bugs we found) until >> the web migration to the Django stuff materialized. > > Yeah, given the amount of work that already went into this prototype, I > guess I would have reacted about the same. I'm not sure that's the only > project stuck behind the new platform migration. How can we help with > this new infrastructure thing ? Actually, given a new box deployed by stefan just two or three days ago, the infrastructure side is ready. What would help at this point would be if at least one oft he *many* different people who promised to do some code review on the new website code would, you know, actually do that. (git.postgresql.org, project pgweb and pgweb-static for those interested) And of course, code improvements, not just review, is also always welcome. -- 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] Getting a bug tracker for the Postgres project
Alvaro Herrera writes: >> http://archives.beccati.org/ >> >> It uses AOX (http://aox.org/) and as such is baked by a PostgreSQL >> database. The mails threading view is even a CTE. > > Yeah, it's great. Last time I heard, though, Mateo wasn't open to doing > any more work on it (including fixing a bunch of bugs we found) until > the web migration to the Django stuff materialized. Yeah, given the amount of work that already went into this prototype, I guess I would have reacted about the same. I'm not sure that's the only project stuck behind the new platform migration. How can we help with this new infrastructure thing ? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [DOCS] [HACKERS] DOCS: SGML identifier may not exceed 44 characters
Peter Eisentrautwrote: Excerpts from Brendan Jurd's message of mar may 31 02:17:22 -0400 2011: > openjade:information_schema.sgml:828:60:Q: length of name token must > not exceed NAMELEN (44) > > But it seems like no one else has seen this problem yet, so it's quite > suspicious, since surely people have built the documentation in the last > few months. I have and I encountered the same problem. I didn't bother to report it because my machine and openjade are quite old: The machine is Red Hat Enterprise Linux ES release 3 (Taroon Update 9), and docbook.dcl is from the docbook-dtds-1.0-17.2 RPM. Maybe that really only affects quite old versions, but I think it wouldn't hurt to shorten the tag in question to avoid the problem. Yours, Laurenz Albe -- 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] pgsql: Protect GIST logic that assumes penalty values can't be negative
On Wed, Jun 1, 2011 at 3:57 AM, Greg Stark wrote: > I guess it was obvious but that was "expect the *penalty* function to > obey the triangle inequality" > Actually, penalty function for boxes is even not commutative. Fox example: A = ((0,0)-(1,1)) B = ((0,0)-(2,2)) penalty(A,B) = 3 penalty(B,A) = 0 -- With best regards, Alexander Korotkov.
Re: [HACKERS] patch for new feature: Buffer Cache Hibernation
> Yeah, I'm pretty well convinced this whole approach is a dead end. > Priming the OS buffer cache seems way more useful. I also think > saving the blocks to be read rather than the actual blocks makes a lot > more sense. Well, his proposal works on any platforms PostgreSQL supports. On the other hand PgFincore works on Linux only. Who wants Linux only tool be in core? Also I really want to see the performance comparison between these two approaches in the real world database. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
