Re: [HACKERS] proposed community service: make coverage
On Wednesday, October 24, 2012 6:37 AM Alvaro Herrera wrote: Hi, During the course of some discussion, I proposed the possibility of us exposing a continuously updated copy of the make coverage target, after running some standard test suite (possibly some of the check-world targets). This would give everyone some visibility into the code that's being regularly exercised by our test suite. I think apart from above, it can also provide information about coverage of new code getting added w.r.t testcases(existing/new). Right now we have the support, but I haven't heard of anyone actually looking at the results, much less doing anything about them. Does this sound interesting/useful to hackers? With Regards, Amit Kapila. -- 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] splitting *_desc routines
On 24 October 2012 21:44, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Here's a small WIP patch that does the proposed splitting. This is a first step towards the objective of having a separately compilable xlogdump -- more work is needed before that can be made to work fully. Now, per previous discussion, I have split each rmgr's desc function into its own file. This is easiest, but it leaves us with several very small files in some directories; for example we have ./src/backend/access/transam/clog_desc.c ./src/backend/access/transam/xact_desc.c ./src/backend/access/transam/xlog_desc.c ./src/backend/access/transam/mxact_desc.c and also ./src/backend/commands/dbase_desc.c ./src/backend/commands/seq_desc.c ./src/backend/commands/tablespace_desc.c Is people okay with that, or should we consider merging each subdir's files into a single one? (say transam_desc.c and cmds_desc.c). One file per rmgr is the right level of modularity. I'd put these in a separate directory to avoid annoyance. Transam is already too large. src/backend/access/rmgrdesc/xlog_desc.c ... src/backend/access/rmgrdesc/seq_desc.c No difference between commands and other stuff. Just one file per rmgr, using the rmgr name as listed in rmgr.c The other question is whether the function and struct declarations are in the best possible locations considering that we will want the files to be compilable without a backend environment. I am using xlogdump as a testbed to ensure that everything is kosher (it's not yet there for other reasons -- I might end up using something other than xlog_internal.h, for example). -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autovacuum truncate exclusive lock round two
Steven, On 10/24/2012 10:46 PM, Stephen Frost wrote: Jan, * Jan Wieck (janwi...@yahoo.com) wrote: This problem has been discussed before. Those familiar with the subject please skip the next paragraph. Apologies if this was already thought-of and ruled out for some reason, but... Because all the scanning had been done in parallel to normal DB activity, it needs to verify that all those blocks are still empty. Would it be possible to use the FSM to figure out if things have changed since the last scan..? Does that scan update the FSM, which would then be updated by another backend in the event that it decided to write something there? Or do we consider the FSM to be completely untrustworthy wrt this (and if so, I don't suppose there's any hope to using the visibility map...)? I honestly don't know if we can trust the FSM enough when it comes to throwing away heap pages. Can we? The notion of having to double-scan and the AccessExclusiveLock on the relation are telling me this work-around, while completely possible, isn't exactly ideal... Under normal circumstances with just a few pages to trim off the end this is no problem. Those pages were the last pages just scanned by this very autovacuum, so they are found in the shared buffers anyway. All the second scan does in that case is to fetch the page once more from shared buffers to be 100% sure, we are not truncating off new tuples. We definitely need the AccessExclusiveLock to prevent someone from extending the relation at the end between our check for relation size and the truncate. Fetching 50 empty blocks from the buffer cache while at it isn't that big of a deal and that is what it normally looks like. The problem case this patch is dealing with is rolling window tables that experienced some bloat. The typical example is a log table, that has new data constantly added and the oldest data constantly purged out. This data normally rotates through some blocks like a rolling window. If for some reason (purging turned off for example) this table bloats by several GB and later shrinks back to its normal content, soon all the used blocks are at the beginning of the heap and we find tens of thousands of empty pages at the end. Only now does the second scan take more than 1000ms and autovacuum is at risk to get killed while at it. Since we have experienced this problem several times now on our production systems, something clearly needs to be done. But IMHO it doesn't happen often enough to take any risk here. Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- 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] [RFC] CREATE QUEUE (log-only table) for londiste/pgQ ccompatibility
WAL-only tables/queues prohobit none of what you claim above, you just implement in a (loosely) MVCC way by keeping track of what events are processed. Well, per our discussion here in person, I'm not convinced that this buys us anything in the let's replace AMQ case. However, as I pointed out in my last email, this feature doesn't need to replace AMQ to be useful. Let's focus on the original use case of supplying a queue which Londiste and Slony can use, which is a sufficient motivation to push the feature if the Slony and Londiste folks think it's good enough (and it seems that they do). -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] unfixed bugs with extensions
We have a couple of unfixed bugs regarding extensions, for which patches have been proposed but remain unfixed in git. The oldest is bug #6704, for which a proposed fix for the master branch was posted here: http://archives.postgresql.org/message-id/m2zk4e6p7m@2ndquadrant.fr There was some disagreement about the proper way forward, so I hadn't looked at this patch. I just did, and find that even if the approach taken by the patch is the correct one, it needs some polish before it can be committed. The other one was reported by Marko Kreen in http://archives.postgresql.org/message-id/cacmqxcjjauc9jpa64vxskrn67byjuymodz-mgy-_aoz6erg...@mail.gmail.com (the thread talks about 2 bugs, but one of them is just pilot error). Dimitri posted patches to the real bug for the three affected branches; the last one (for master) was in message http://archives.postgresql.org/message-id/m2391yy6ub@2ndquadrant.fr I eyeballed this patch and it seemed to me to fix the problem, but I am unfamiliar enough with this dark corner of pg_dump that I can't be sure that there aren't bad behaviors being introduced inadvertently. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autovacuum truncate exclusive lock round two
Jan Wieck janwi...@yahoo.com writes: On 10/24/2012 10:46 PM, Stephen Frost wrote: Would it be possible to use the FSM to figure out if things have changed since the last scan..? Does that scan update the FSM, which would then be updated by another backend in the event that it decided to write something there? Or do we consider the FSM to be completely untrustworthy wrt this (and if so, I don't suppose there's any hope to using the visibility map...)? I honestly don't know if we can trust the FSM enough when it comes to throwing away heap pages. Can we? No. Backends are under no obligation to update FSM for each individual tuple insertion, and typically don't do so. More to the point, you have to take AccessExclusiveLock *anyway*, because this is interlocking not only against new insertions but plain read-only seqscans: if a seqscan falls off the end of the table it will be very unhappy. So I don't see where we'd buy anything by consulting the FSM. 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] unfixed bugs with extensions
Alvaro Herrera alvhe...@2ndquadrant.com writes: The other one was reported by Marko Kreen in http://archives.postgresql.org/message-id/cacmqxcjjauc9jpa64vxskrn67byjuymodz-mgy-_aoz6erg...@mail.gmail.com (the thread talks about 2 bugs, but one of them is just pilot error). Dimitri posted patches to the real bug for the three affected branches; the last one (for master) was in message http://archives.postgresql.org/message-id/m2391yy6ub@2ndquadrant.fr I eyeballed this patch and it seemed to me to fix the problem, but I am unfamiliar enough with this dark corner of pg_dump that I can't be sure that there aren't bad behaviors being introduced inadvertently. Yeah, I think that one's on my plate. 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] autovacuum truncate exclusive lock round two
On 10/25/2012 9:45 AM, Tom Lane wrote: Jan Wieck janwi...@yahoo.com writes: On 10/24/2012 10:46 PM, Stephen Frost wrote: Would it be possible to use the FSM to figure out if things have changed since the last scan..? Does that scan update the FSM, which would then be updated by another backend in the event that it decided to write something there? Or do we consider the FSM to be completely untrustworthy wrt this (and if so, I don't suppose there's any hope to using the visibility map...)? I honestly don't know if we can trust the FSM enough when it comes to throwing away heap pages. Can we? No. Backends are under no obligation to update FSM for each individual tuple insertion, and typically don't do so. More to the point, you have to take AccessExclusiveLock *anyway*, because this is interlocking not only against new insertions but plain read-only seqscans: if a seqscan falls off the end of the table it will be very unhappy. So I don't see where we'd buy anything by consulting the FSM. Thank you. One thing that I haven't mentioned yet is that with this patch, we could actually insert a vacuum_delay_point() into the loop in count_nondeletable_pages(). We no longer cling to the exclusive lock but rather get out of the way as soon as somebody needs the table. Under this condition we no longer need to do the second scan full bore. Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- 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] autovacuum truncate exclusive lock round two
Jan, * Jan Wieck (janwi...@yahoo.com) wrote: The problem case this patch is dealing with is rolling window tables that experienced some bloat. The typical example is a log table, that has new data constantly added and the oldest data constantly purged out. This data normally rotates through some blocks like a rolling window. If for some reason (purging turned off for example) this table bloats by several GB and later shrinks back to its normal content, soon all the used blocks are at the beginning of the heap and we find tens of thousands of empty pages at the end. Only now does the second scan take more than 1000ms and autovacuum is at risk to get killed while at it. My concern is that this could certainly also happen to a heavily updated table in an OLTP type of environment where the requirement to take a heavy lock to clean it up might prevent it from ever happening.. I was simply hoping we could find a mechanism to lock just those pages we're getting ready to nuke rather than the entire relation. Perhaps we can consider how to make those changes alongside of changes to eliminate or reduce the extent locking that has been painful (for me at least) when doing massive parallel loads into a table. Since we have experienced this problem several times now on our production systems, something clearly needs to be done. But IMHO it doesn't happen often enough to take any risk here. I'm not advocating a 'do-nothing' approach, was just looking for another option that might allow for this work to happen on the heap in parallel with regular access. Since we havn't got any way to do that currently, +1 for moving forward with this as it clearly improves the current situation. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] proposal - assign result of query to psql variable
I gave this a look. I think it needs to be revised by somebody with a better understanding of scanner (flex) than me, but I didn't like the changes in psqlscan.l at all; the new xvl pattern is too unlike the rest of the surrounding patterns, and furthermore it has been placed within the block that says it mirrors the backend scanner, when it obviously has no equivalent there. I assume there's a better way to do this. Hints would be appreciated. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - assign result of query to psql variable
Alvaro Herrera alvhe...@2ndquadrant.com writes:
I gave this a look. I think it needs to be revised by somebody with a
better understanding of scanner (flex) than me, but I didn't like the
changes in psqlscan.l at all; the new xvl pattern is too unlike the
rest of the surrounding patterns, and furthermore it has been placed
within the block that says it mirrors the backend scanner, when it
obviously has no equivalent there.
I assume there's a better way to do this. Hints would be appreciated.
Personally I saw no reason for this patch to touch psqlscan.l in the
first place. Commands such as \set just scan variable names with
psql_scan_slash_option(OT_NORMAL); why shouldn't this act the same?
Moreover, the proposed lexer rules are flat out *wrong*, in that they
insist on the target variable names being {identifier}s, a restriction
not imposed by \set.
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] proposal - assign result of query to psql variable
Tom Lane escribió:
Alvaro Herrera alvhe...@2ndquadrant.com writes:
I gave this a look. I think it needs to be revised by somebody with a
better understanding of scanner (flex) than me, but I didn't like the
changes in psqlscan.l at all; the new xvl pattern is too unlike the
rest of the surrounding patterns, and furthermore it has been placed
within the block that says it mirrors the backend scanner, when it
obviously has no equivalent there.
I assume there's a better way to do this. Hints would be appreciated.
Personally I saw no reason for this patch to touch psqlscan.l in the
first place. Commands such as \set just scan variable names with
psql_scan_slash_option(OT_NORMAL); why shouldn't this act the same?
Moreover, the proposed lexer rules are flat out *wrong*, in that they
insist on the target variable names being {identifier}s, a restriction
not imposed by \set.
Great, thanks for the feedback. Marking as returned in CF. I hope to
see a new version after pgconf.eu.
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2
Comments about the approach or even the general direction of the implementation? Questions? This patch series has gotten serious amount of discussion and useful feedback; even some parts of it have been committed. I imagine lots more feedback, discussion and spawning of new ideas will take place in Prague. I am marking it as Returned with Feedback for now. Updated, rebased, modified versions are expected for the next commitfest. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP fix proposal for bug #6123
Alvaro Herrera wrote: Kevin Grittner escribió: Tom Lane t...@sss.pgh.pa.us wrote: Also, it doesn't appear that we ever got around to preparing documentation updates, but I think we definitely need some if we're going to start throwing errors for things that used to be allowed. Since Kevin has the most field experience with this problem, I'd like to nominate him to write some docs ... OK, will do. The redo the DELETE and RETURN NULL workaround is not at all obvious; we should definitely include an example of that. Any chance this patch could be pushed soon? The problem is that this patch conflicts rather heavily with my FOR KEY SHARE patch. I think it makes sense to commit this one first. To me, it would be good enough that the code changes go in now; the doc patch can wait a little longer. Sorry I just got to this in wading through backlog. Will push today without docs and work on docs soon. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.1] 2 bugs with extensions
Dimitri Fontaine dimi...@2ndquadrant.fr writes:
Alvaro Herrera alvhe...@2ndquadrant.com writes:
No, just remove the RELKIND_UNCATALOGUED case in that switch.
Oh. As in the attached? :)
I don't think you tested this patch in 9.2 or HEAD, because it bleats
like mad. I installed an extension containing
create sequence extseq;
select pg_catalog.pg_extension_config_dump('extseq', '');
into the regression database, and then did:
$ pg_dump -Fc regression r.dump
pg_dump: [archiver] WARNING: archive items not in correct section order
pg_dump: [archiver] WARNING: archive items not in correct section order
pg_dump: [archiver] WARNING: archive items not in correct section order
pg_dump: [archiver] WARNING: archive items not in correct section order
pg_dump: [archiver] WARNING: archive items not in correct section order
pg_dump: [archiver] WARNING: archive items not in correct section order
pg_dump: [archiver] WARNING: archive items not in correct section order
pg_dump: [archiver] WARNING: archive items not in correct section order
The reason is that it calls dumpSequence() to emit the SEQUENCE SET
archive item during table-data dumping, but the archive item gets marked
SECTION_PRE_DATA. As of 9.2 we have to be rigid about keeping those
section markings correct and in-sequence. This is not really right in
9.1 either (wouldn't be surprised if it breaks parallel restore).
The fact that SEQUENCE SET is considered pre-data has bitten us several
times already, eg
http://archives.postgresql.org/pgsql-bugs/2012-05/msg00084.php
I think it may be time to bite the bullet and change that (including
breaking dumpSequence() into two separate functions). I'm a little bit
worried about the compatibility implications of back-patching such a
change, though. Is it likely that anybody out there is depending on the
fact that, eg, pg_dump --section=pre-data currently includes SEQUENCE SET
items? Personally I think it's more likely that that'd be seen as a
bug, but ...
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] proposal - assign result of query to psql variable
2012/10/25 Tom Lane t...@sss.pgh.pa.us:
Alvaro Herrera alvhe...@2ndquadrant.com writes:
I gave this a look. I think it needs to be revised by somebody with a
better understanding of scanner (flex) than me, but I didn't like the
changes in psqlscan.l at all; the new xvl pattern is too unlike the
rest of the surrounding patterns, and furthermore it has been placed
within the block that says it mirrors the backend scanner, when it
obviously has no equivalent there.
I assume there's a better way to do this. Hints would be appreciated.
Personally I saw no reason for this patch to touch psqlscan.l in the
first place. Commands such as \set just scan variable names with
psql_scan_slash_option(OT_NORMAL); why shouldn't this act the same?
it cannot be same, because current scan doesn't know comma as
separator. So if you don't like changes in scanner, than we can't to
use var1, var2, syntax and we can't to use leaky list syntax ,x,
Moreover, the proposed lexer rules are flat out *wrong*, in that they
insist on the target variable names being {identifier}s, a restriction
not imposed by \set.
do you like to support referenced varnames??
postgres=# \varname xxx
Invalid command \varname. Try \? for help.
postgres=# \set varname xxx
postgres=# \set :varname Hello
postgres=# \set
varname = 'xxx'
xxx = 'Hello'
yes, \set support it, but this can be source of strange behave for
some people, because people use :varname like $varname in classic
scripting languages, and it is significantly different - so I didn't
support it as little bit dangerous feature. It is easy support it,
although I am thinking, so it is not good idea, because behave is
really different than users expect and I don't know any use case for
this indirect referencing. But I would to talk about it, and I invite
opinion of others.
Regards
Pavel
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] proposal - assign result of query to psql variable
Pavel Stehule pavel.steh...@gmail.com writes:
2012/10/25 Tom Lane t...@sss.pgh.pa.us:
Personally I saw no reason for this patch to touch psqlscan.l in the
first place. Commands such as \set just scan variable names with
psql_scan_slash_option(OT_NORMAL); why shouldn't this act the same?
it cannot be same, because current scan doesn't know comma as
separator. So if you don't like changes in scanner, than we can't to
use var1, var2, syntax and we can't to use leaky list syntax ,x,
Uh, no, that doesn't follow. It wouldn't be any more code to have
command.c process the commas (or even more likely, just save the \gset
argument(s) as a string, and split on commas after we've done the
command). Even if we wanted to do that in psqlscan.l, this was a pretty
bad/ugly implementation of it.
Moreover, the proposed lexer rules are flat out *wrong*, in that they
insist on the target variable names being {identifier}s, a restriction
not imposed by \set.
yes, \set support it, but this can be source of strange behave for
some people, because people use :varname like $varname in classic
scripting languages, and it is significantly different - so I didn't
support it as little bit dangerous feature.
[ shrug... ] If you want to argue for imposing a restriction on
psql variable names across-the-board, we could have that discussion;
but personally I've not seen even one user complaint that could be
traced to \set's laxity in the matter, so I don't see a need for
a restriction. In any case, having \gset enforce a restriction
that \set doesn't is useless and inconsistent.
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] proposal - assign result of query to psql variable
2012/10/25 Tom Lane t...@sss.pgh.pa.us:
Pavel Stehule pavel.steh...@gmail.com writes:
2012/10/25 Tom Lane t...@sss.pgh.pa.us:
Personally I saw no reason for this patch to touch psqlscan.l in the
first place. Commands such as \set just scan variable names with
psql_scan_slash_option(OT_NORMAL); why shouldn't this act the same?
it cannot be same, because current scan doesn't know comma as
separator. So if you don't like changes in scanner, than we can't to
use var1, var2, syntax and we can't to use leaky list syntax ,x,
Uh, no, that doesn't follow. It wouldn't be any more code to have
command.c process the commas (or even more likely, just save the \gset
argument(s) as a string, and split on commas after we've done the
command). Even if we wanted to do that in psqlscan.l, this was a pretty
bad/ugly implementation of it.
I don't understand, why we have to move lexer work from scanner to
command processing?
then I afraid of another issue - when we do late separation in command
somebody can do
\set targetvars a,b,c
select
\gset x1,x2,:targetvars,x3
We would to do this? Then we moving to TeX liked languages. I am asking.
Moreover, the proposed lexer rules are flat out *wrong*, in that they
insist on the target variable names being {identifier}s, a restriction
not imposed by \set.
yes, \set support it, but this can be source of strange behave for
some people, because people use :varname like $varname in classic
scripting languages, and it is significantly different - so I didn't
support it as little bit dangerous feature.
[ shrug... ] If you want to argue for imposing a restriction on
psql variable names across-the-board, we could have that discussion;
but personally I've not seen even one user complaint that could be
traced to \set's laxity in the matter, so I don't see a need for
a restriction. In any case, having \gset enforce a restriction
that \set doesn't is useless and inconsistent.
ok, it has a sense
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] Re: [WIP] Performance Improvement by reducing WAL for Update Operation
Naturally, there are other compression and delta encoding schemes. Does anyone feel the need to explore further alternatives? We might eventually find the need for multiple, user-selectable, WAL compression strategies. I don't recommend taking that step yet. my currently implemented compression strategy is to run the wal block through gzip in the archive command. compresses pretty nicely and achieved 50%+ in my workload (generally closer to 70) on a multi core system it will take more cpu time but on a different core and not have any effect on tps. General compression should probably only be applied if it have positive gain on tps you could. Jesper -- 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] autovacuum truncate exclusive lock round two
On 10/25/2012 10:12 AM, Stephen Frost wrote: Jan, * Jan Wieck (janwi...@yahoo.com) wrote: The problem case this patch is dealing with is rolling window tables that experienced some bloat. The typical example is a log table, that has new data constantly added and the oldest data constantly purged out. This data normally rotates through some blocks like a rolling window. If for some reason (purging turned off for example) this table bloats by several GB and later shrinks back to its normal content, soon all the used blocks are at the beginning of the heap and we find tens of thousands of empty pages at the end. Only now does the second scan take more than 1000ms and autovacuum is at risk to get killed while at it. My concern is that this could certainly also happen to a heavily updated table in an OLTP type of environment where the requirement to take a heavy lock to clean it up might prevent it from ever happening.. I was simply hoping we could find a mechanism to lock just those pages we're getting ready to nuke rather than the entire relation. Perhaps we can consider how to make those changes alongside of changes to eliminate or reduce the extent locking that has been painful (for me at least) when doing massive parallel loads into a table. I've been testing this with loads of 20 writes/s to that bloated table. Preventing not only the clean up, but the following ANALYZE as well is precisely what happens. There may be multiple ways how to get into this situation, but once you're there the symptoms are the same. Vacuum fails to truncate it and causing a 1 second hiccup every minute, while vacuum is holding the exclusive lock until the deadlock detection code of another transaction kills it. My patch doesn't change the logic how we ensure that we don't zap any data by accident with the truncate and Tom's comments suggest we should stick to it. It only makes autovacuum check frequently if the AccessExclusiveLock is actually blocking anyone and then get out of the way. I would rather like to discuss any ideas how to do all this without 3 new GUCs. In the original code, the maximum delay that autovacuum can cause by holding the exclusive lock is one deadlock_timeout (default 1s). It would appear reasonable to me to use max(deadlock_timeout/10,10ms) as the interval to check for a conflicting lock request. For another transaction that needs to access the table this is 10 times faster than it is now and still guarantees that autovacuum will make some progress with the truncate. The other two GUCs control how often and how fast autovacuum tries to acquire the exclusive lock in the first place. Since we actively release the lock *because someone needs it* it is pretty much guaranteed that the immediate next lock attempt fails. We on purpose do a ConditionalLockRelation() because there is a chance to deadlock. The current code only tries one lock attempt and gives up immediately. I don't know from what to derive a good value for how long to retry, but the nap time in between tries could be a hardcoded 20ms or using the cost based vacuum nap time (which defaults to 20ms). Any other ideas are welcome. Thanks, Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- 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 4/8] add simple xlogdump tool
After some fooling around to provide the discussed backend functionality to xlogdump (StringInfo atop PQExpBuffer and elog_start/elog_finish), the following items still need work: 1. rmgr tables We're linking rmgr.c so that we can obtain the appropriate rm_desc function pointer for each rmgr. However the table also includes the rm_redo, startup, etc function pointers, which the linker wants resolved at xlogdump link time. The idea I have to handle this is to use a macro similar to PG_KEYWORD: at compile time we define it differently on xlogdump than on backend, so that the symbols we don't want are hidden. 2. ereport() functionality Currently the xlogreader.c I'm using (the latest version posted by Andres) has both elog() calls and ereport(). I have provided trivial elog_start and elog_finish implementations, which covers the first. I am not really sure about implementing the whole errstart/errfinish stack, because that'd be pretty duplicative, though I haven't tried. The other alternative suggested elsewhere is to avoid elog/ereport entirely in xlogreader.c and instead pass a function pointer for error reportage. The backend would normally use ereport(), but xlogdump could do something simple with fprintf. I think that would end up being cleaner overall. 3. timestamptz_to_str xact_desc uses this, which involves a couple of messy backend files (because there's palloc in them, among other problems). Alternatively we could tweak xact_desc to use EncodeDateTime (probably through some simple wrapper); given the constraints imposed on the values, that might be simpler, and we can provide a simple implementation of EncodeDateTime or of its hypothetical wrapper in xlogdump. 4. relpathbackend and pfree of its return value This is messy. Maybe we should a caller-supplied buffer instead of palloc to solve this. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autovacuum truncate exclusive lock round two
Jan Wieck wrote: In the original code, the maximum delay that autovacuum can cause by holding the exclusive lock is one deadlock_timeout (default 1s). It would appear reasonable to me to use max(deadlock_timeout/10,10ms) as the interval to check for a conflicting lock request. For another transaction that needs to access the table this is 10 times faster than it is now and still guarantees that autovacuum will make some progress with the truncate. So you would be calling GetCurrentTimestamp() continuously? Since you mentioned adding a vacuum delay point I wonder if it would make sense to test for lockers each time it would consider going to sleep, instead. (One hazard to keep in mind is the case where no vacuum delay is configured.) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: KNN Search should to support DISTINCT clasuse?
Pavel Stehule wrote: 2012/10/22 Tom Lane t...@sss.pgh.pa.us: Perhaps it would be close enough to what you want to use DISTINCT ON: contrib_regression=# explain select distinct on( t - 'foo') *,t - 'foo' from test_trgm order by t - 'foo' limit 10; good tip - it's working If two or more values happen to be at exactly the same distance, wouldn't you just get one of them? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.1] 2 bugs with extensions
I wrote:
The fact that SEQUENCE SET is considered pre-data has bitten us several
times already, eg
http://archives.postgresql.org/pgsql-bugs/2012-05/msg00084.php
I think it may be time to bite the bullet and change that (including
breaking dumpSequence() into two separate functions). I'm a little bit
worried about the compatibility implications of back-patching such a
change, though. Is it likely that anybody out there is depending on the
fact that, eg, pg_dump --section=pre-data currently includes SEQUENCE SET
items? Personally I think it's more likely that that'd be seen as a
bug, but ...
Specifically, I'm thinking this, which looks rather bulky but most of
the diff is from reindenting the guts of dumpSequence().
regards, tom lane
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 4223b415362f4673097b6950c1c1f8b8349ca7d7..82330cbd915d7d23f7976253f5135beeec1abcf9 100644
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*** static void dumpTable(Archive *fout, Tab
*** 192,197
--- 192,198
static void dumpTableSchema(Archive *fout, TableInfo *tbinfo);
static void dumpAttrDef(Archive *fout, AttrDefInfo *adinfo);
static void dumpSequence(Archive *fout, TableInfo *tbinfo);
+ static void dumpSequenceData(Archive *fout, TableDataInfo *tdinfo);
static void dumpIndex(Archive *fout, IndxInfo *indxinfo);
static void dumpConstraint(Archive *fout, ConstraintInfo *coninfo);
static void dumpTableConstraintComment(Archive *fout, ConstraintInfo *coninfo);
*** makeTableDataInfo(TableInfo *tbinfo, boo
*** 1640,1648
/* Skip VIEWs (no data to dump) */
if (tbinfo-relkind == RELKIND_VIEW)
return;
- /* Skip SEQUENCEs (handled elsewhere) */
- if (tbinfo-relkind == RELKIND_SEQUENCE)
- return;
/* Skip FOREIGN TABLEs (no data to dump) */
if (tbinfo-relkind == RELKIND_FOREIGN_TABLE)
return;
--- 1641,1646
*** dumpDumpableObject(Archive *fout, Dumpab
*** 7318,7324
dumpCast(fout, (CastInfo *) dobj);
break;
case DO_TABLE_DATA:
! dumpTableData(fout, (TableDataInfo *) dobj);
break;
case DO_DUMMY_TYPE:
/* table rowtypes and array types are never dumped separately */
--- 7316,7325
dumpCast(fout, (CastInfo *) dobj);
break;
case DO_TABLE_DATA:
! if (((TableDataInfo *) dobj)-tdtable-relkind == RELKIND_SEQUENCE)
! dumpSequenceData(fout, (TableDataInfo *) dobj);
! else
! dumpTableData(fout, (TableDataInfo *) dobj);
break;
case DO_DUMMY_TYPE:
/* table rowtypes and array types are never dumped separately */
*** collectSecLabels(Archive *fout, SecLabel
*** 12226,12238
static void
dumpTable(Archive *fout, TableInfo *tbinfo)
{
! if (tbinfo-dobj.dump)
{
char *namecopy;
if (tbinfo-relkind == RELKIND_SEQUENCE)
dumpSequence(fout, tbinfo);
! else if (!dataOnly)
dumpTableSchema(fout, tbinfo);
/* Handle the ACL here */
--- 12227,12239
static void
dumpTable(Archive *fout, TableInfo *tbinfo)
{
! if (tbinfo-dobj.dump !dataOnly)
{
char *namecopy;
if (tbinfo-relkind == RELKIND_SEQUENCE)
dumpSequence(fout, tbinfo);
! else
dumpTableSchema(fout, tbinfo);
/* Handle the ACL here */
*** findLastBuiltinOid_V70(Archive *fout)
*** 13347,13366
return last_oid;
}
static void
dumpSequence(Archive *fout, TableInfo *tbinfo)
{
PGresult *res;
char *startv,
- *last,
*incby,
*maxv = NULL,
*minv = NULL,
*cache;
char bufm[100],
bufx[100];
! bool cycled,
! called;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
PQExpBuffer labelq = createPQExpBuffer();
--- 13348,13369
return last_oid;
}
+ /*
+ * dumpSequence
+ * write the declaration (not data) of one user-defined sequence
+ */
static void
dumpSequence(Archive *fout, TableInfo *tbinfo)
{
PGresult *res;
char *startv,
*incby,
*maxv = NULL,
*minv = NULL,
*cache;
char bufm[100],
bufx[100];
! bool cycled;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
PQExpBuffer labelq = createPQExpBuffer();
*** dumpSequence(Archive *fout, TableInfo *t
*** 13375,13381
{
appendPQExpBuffer(query,
SELECT sequence_name,
! start_value, last_value, increment_by,
CASE WHEN increment_by 0 AND max_value = %s THEN NULL
WHEN increment_by 0 AND max_value = -1 THEN NULL
ELSE max_value
--- 13378,13384
{
appendPQExpBuffer(query,
SELECT sequence_name,
! start_value, increment_by,
CASE WHEN increment_by 0 AND max_value = %s THEN NULL
WHEN increment_by 0 AND max_value = -1 THEN NULL
ELSE max_value
Re: [HACKERS] ToDo: KNN Search should to support DISTINCT clasuse?
Kevin Grittner kgri...@mail.com writes: Pavel Stehule wrote: 2012/10/22 Tom Lane t...@sss.pgh.pa.us: Perhaps it would be close enough to what you want to use DISTINCT ON: contrib_regression=# explain select distinct on( t - 'foo') *,t - 'foo' from test_trgm order by t - 'foo' limit 10; good tip - it's working If two or more values happen to be at exactly the same distance, wouldn't you just get one of them? Yeah, that is a hazard. I'm not sure whether -'s results are sufficiently quantized to make that a big problem in practice. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Extensions Documentation
Hackers, Any plans to implement a documentation standard for extensions? I would love to see `make install` create the necessary man pages and perhaps even HTML (with a link added in the proper place). Anyone given this any thought? Dim? Thanks, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] sql_implementation_info still contains old value
I noticed this in information_schema; it would seem the 'DBMS VERSION' is still the old value: $ psql psql (9.2.1) Type help for help. # select version(); version --- PostgreSQL 9.2.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.2, 64-bit (1 row) # select * from information_schema.sql_implementation_info where implementation_info_name = 'DBMS VERSION'; -[ RECORD 1 ]+- implementation_info_id | 18 implementation_info_name | DBMS VERSION integer_value| character_value | 09.02. comments | I would expect 9.2.1 to contain '09.02.0001' (not '09.02.'). Unless, of course, I have overlooked some step in the upgrade... Thanks, Erik Rijkers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
