Re: [HACKERS] bug of recovery?
On 26.09.2011 21:06, Simon Riggs wrote: On Mon, Sep 26, 2011 at 10:50 AM, Fujii Masaomasao.fu...@gmail.com wrote: Currently, if a reference to an invalid page is found during recovery, its information is saved in hash table invalid_page_tab. Then, if such a reference is resolved, its information is removed from the hash table. If there is unresolved reference to an invalid page in the hash table at the end of recovery, PANIC error occurs. What I'm worried about is that the hash table is volatile. If a user restarts the server before reaching end of recovery, any information in the hash table is lost, and we wrongly miss the PANIC error case because we cannot find any unresolved reference. That is, even if database is corrupted at the end of recovery, a user might not be able to notice that. This looks like a serious problem. No? To prevent the above problem, we should write the contents of the hash table to the disk for every restartpoints, I think. Then, when the server starts recovery, it should reload the hash table from the disk. Thought? Am I missing something? That doesn't happen because the when we stop the server it will restart from a valid restartpoint - one where there is no in-progress multi-phase operation. So when it replays it will always replay both parts of the operation. I think you're mixing this up with the multi-page page split operations in b-tree. This is different from that. What the invalid_page_tab is for is the situation where you for example, insert to a page on table X, and later table X is dropped, and then you crash. On WAL replay, you will see the insert record, but the file for the table doesn't exist, because the table was dropped. In that case we skip the insert, note what happened in invalid_page_tab, and move on with recovery. When we see the later record to drop the table, we know it was OK that the file was missing earlier. But if we don't see it before end of recovery, we PANIC, because then the file should've been there. -- 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] bug of recovery?
On 27.09.2011 00:28, Florian Pflug wrote: On Sep26, 2011, at 22:39 , Tom Lane wrote: It might be worthwhile to invoke XLogCheckInvalidPages() as soon as we (think we) have reached consistency, rather than leaving it to be done only when we exit recovery mode. I believe we also need to prevent the creation of restart points before we've reached consistency. Seems reasonable. We could still allow restartpoints when the hash table is empty, though. And once we've reached consistency, we can throw an error immediately in log_invalid_page(), instead of adding the entry in the hash table. -- 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] bug of recovery?
On Tue, Sep 27, 2011 at 6:54 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I think you're mixing this up with the multi-page page split operations in b-tree. This is different from that. What the invalid_page_tab is for is the situation where you for example, insert to a page on table X, and later table X is dropped, and then you crash. On WAL replay, you will see the insert record, but the file for the table doesn't exist, because the table was dropped. In that case we skip the insert, note what happened in invalid_page_tab, and move on with recovery. When we see the later record to drop the table, we know it was OK that the file was missing earlier. But if we don't see it before end of recovery, we PANIC, because then the file should've been there. OK, yes, I see. Thanks. -- 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] [v9.2] Fix Leaky View Problem
2011/9/26 Robert Haas robertmh...@gmail.com: On Mon, Sep 12, 2011 at 3:31 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: The Part-2 tries to tackles a leaky-view scenarios by functions with very tiny cost estimation value. It was same one we had discussed in the commitfest-1st. It prevents to launch functions earlier than ones come from inside of views with security_barrier option. The Part-3 tries to tackles a leaky-view scenarios by functions that references one side of join loop. It prevents to distribute qualifiers including functions without leakproof attribute into relations across security-barrier. I took a little more of a look at this today. It has major problems. First, I get compiler warnings (which you might want to trap in the future by creating src/Makefile.custom with COPT=-Werror when compiling). Second, the regression tests fail on the select_views test. Third, it appears that the part2 patch works by adding an additional traversal of the entire query tree to standard_planner(). I don't think we want to add overhead to the common case where no security views are in use, or at least it had better be very small - so this doesn't seem acceptable to me. The reason why I put a walker routine on the head of standard_planner() was that previous revision of this patch tracked strict depth of sub-queries, not a number of times to go through security barrier. The policy to count-up depth of qualifier was changed according to Noad's suggestion is commit-fest 1st, however, the suitable position to mark the depth value was kept. I'll try to revise the suitable position to track the depth value. It seems to me one candidate is pull_up_subqueries during its recursive call, because this patch originally set FromExpr-security_barrier here. In addition to the two points you mentioned above, I'll update this patch as follows: * Use CREATE [SECURITY] VIEW statement, instead of reloptions. the flag shall be stored within a new attribute of pg_class, and it shall be kept when an existing view getting replaced. * Utilize RangeTblEntry-relid, instead of rte-security_barrier, and the flag shall be pulled from the catalog on planner stage. * Documentation and Regression test. Thanks, -- KaiGai Kohei kai...@kaigai.gr.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] contrib/sepgsql regression tests are a no-go
2011/9/26 Tom Lane t...@sss.pgh.pa.us: Kohei KaiGai kai...@kaigai.gr.jp writes: How about this fix on regression test of sepgsql? IMO, the fundamental problem with the sepgsql regression tests is that they think they don't need to play by the rules that apply to every other PG regression test. I don't think this patch is fixing that problem; it's just coercing pgxs.mk to assist in not playing by the rules, and adding still more undocumented complexity to the PGXS mechanisms in order to do so. If we have to have a nonstandard command for running the sepgsql regression tests, as it seems that we do, we might as well just make that an entirely local affair within contrib/sepgsql. Are you suggesting to make a command/script to check OS environment and run its own regression test without touching existing pg_regress framework, even if it just utilizes existing options? It seems to me re-inventment of a wheel Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Postgresql parser
Hello, Currently I am working on Postgresql... I need to study the gram.y and scan.l parser files...since I want to do some qery modification. Can anyone please help me to understand the files. What should I do ? Is there any documentation available ? Regards, Aditi. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Postgresql-parser-tp4844522p4844522.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] unite recovery.conf and postgresql.conf
On Mon, Sep 26, 2011 at 7:45 PM, Peter Eisentraut pete...@gmx.net wrote: On sön, 2011-09-25 at 12:58 -0400, Tom Lane wrote: And it's not like we don't break configuration file contents in most releases anyway, so I really fail to see why this one has suddenly become sacrosanct. Well, there is a slight difference. Changes in postgresql.conf parameter names and settings are adjusted automatically for me by my package upgrade script. If we, say, changed the names of recovery.conf parameters, I'd have to get a new version of my $SuperReplicationTool. That tool could presumably look at PG_VERSION and put a recovery.conf with the right spellings in the right place. But if we completely change the way the replication configuration interacts, it's not clear that a smooth upgrade is possible without significant effort. That said, I don't see why it wouldn't be possible, but let's design with upgradability in mind, instead of claiming that we have never supported upgrades of this kind anyway. Currently recovery.conf has two roles: #1. recovery.conf is used as a trigger file to enable archive recovery. At the end of recovery, recovery.conf is renamed to recovery.done. #2. recovery.conf is used as a configuration file for recovery parameters. Which role do you think we should support in 9.2 because of the backward compatibility? Both? Unless I misunderstand the discussion so far, Tom and Robert (and I) agree to get rid of both. Simon seems to agree to remove only the former role, but not the latter. How about you? If you agree to remove the former, too, let's focus on the discussion about whether the latter role should be supported in 9.2. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Postgresql parser
On Tue, Sep 27, 2011 at 11:44, andurkar andurkarad10.c...@coep.ac.in wrote: Hello, Currently I am working on Postgresql... I need to study the gram.y and scan.l parser files...since I want to do some qery modification. Can anyone please help me to understand the files. What should I do ? Is there any documentation available ? Regards, Aditi. What kind of modifications do you want to do? regards, Kerem KAT -- 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] fix for pg_upgrade
Hi Bruce, here is the whole dump (old DB): http://postgresql.1045698.n5.nabble.com/file/n4844725/dump.txt dump.txt Regards, panam -- View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4844725.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] bug of recovery?
On Sep27, 2011, at 07:59 , Heikki Linnakangas wrote: On 27.09.2011 00:28, Florian Pflug wrote: On Sep26, 2011, at 22:39 , Tom Lane wrote: It might be worthwhile to invoke XLogCheckInvalidPages() as soon as we (think we) have reached consistency, rather than leaving it to be done only when we exit recovery mode. I believe we also need to prevent the creation of restart points before we've reached consistency. Seems reasonable. We could still allow restartpoints when the hash table is empty, though. And once we've reached consistency, we can throw an error immediately in log_invalid_page(), instead of adding the entry in the hash table. That mimics the way the rm_safe_restartpoint callbacks work, which is good. Actually, why don't we use that machinery to implement this? There's currently no rm_safe_restartpoint callback for RM_XLOG_ID, so we'd just need to create one that checks whether invalid_page_tab is empty. 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] Postgresql parser
On Sep27, 2011, at 10:44 , andurkar wrote: Currently I am working on Postgresql... I need to study the gram.y and scan.l parser files...since I want to do some qery modification. Can anyone please help me to understand the files. What should I do ? Is there any documentation available ? scan.l defines the lexer, i.e. the algorithm that splits a string (containing an SQL statement) into a stream of tokens. A token is usually a single word (i.e., doesn't contain spaces but is delimited by spaces), but can also be a whole single or double-quoted string for example. The lexer is basically defined in terms of regular expressions which describe the different token types. gram.y defines the grammar (the syntactical structure) of SQL statements, using the tokens generated by the lexer as basic building blocks. The grammar is defined in BNF notation. BNF resembles regular expressions but works on the level of tokens, not characters. Also, patterns (called rules or productions in BNF) are named, and may be recursive, i.e. use themselves as sub-patters. The actual lexer is generated from scan.l by a tool called flex. You can find the manual at http://flex.sourceforge.net/manual/ The actual parser is generated from gram.y by a tool called bison. You can find the manual at http://www.gnu.org/s/bison/. Beware, though, that you'll have a rather steep learning curve ahead of you if you've never used flex or bison before. 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] heap_update temporary release of buffer lock
On Tue, Sep 20, 2011 at 3:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mar sep 20 16:04:03 -0300 2011: On 20.09.2011 20:42, Alvaro Herrera wrote: I notice that heap_update releases the buffer lock, after checking the HeapTupleSatifiesUpdate result, and before marking the tuple as updated, to pin the visibility map page -- heapam.c lines 2638ff in master branch. The easiest fix seems to be (as you suggest) to add goto l2 after reacquiring the lock. Can we get away with (and is there any benefit to) doing that only if xmax has changed? Hmm ... I think that works, and it would suit my purposes too. Note this means you have to recheck infomask too (otherwise consider that IS_MULTI could be set the first time, and not set the second time, and that makes the Xmax have a different meaning.) OTOH if you just do it always, it is simpler. Yeah, I think a goto l2 is correct and sufficient. As the comment already notes, this need not be a high-performance path, so why spend extra code (with extra risk of bugs)? Done. -- 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] [v9.1] sepgsql - userspace access vector cache
On Fri, Sep 2, 2011 at 12:38 PM, Kohei Kaigai kohei.kai...@emea.nec.com wrote:
I've committed this, but I still think it would be helpful to revise
that comment. The turn boosted up is not very precise or
informative. Could you submit a separate, comment-only patch to
improve this?
I tried to put more detailed explanation about the logic of do { ... } while
loop of sepgsql_avc_check_valid and the cache field of new security label to
be switched on execution of the procedure. Is it helpful?
I edited this and committed it along with a bunch of further
wordsmithing on the comments in that file. Please let me know if you
see any isuses.
Thanks,
--
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] CUDA Sorting
Hey hackers, I'm still having problems reading the values of the columns in tuplesort.c, in order to understand how to port this to CUDA. Should I use the heap_getattr macro to read them? 2011/9/24 Hannu Krosing ha...@krosing.net On Mon, 2011-09-19 at 10:36 -0400, Greg Smith wrote: On 09/19/2011 10:12 AM, Greg Stark wrote: With the GPU I'm curious to see how well it handles multiple processes contending for resources, it might be a flashy feature that gets lots of attention but might not really be very useful in practice. But it would be very interesting to see. The main problem here is that the sort of hardware commonly used for production database servers doesn't have any serious enough GPU to support CUDA/OpenCL available. The very clear trend now is that all systems other than gaming ones ship with motherboard graphics chipsets more than powerful enough for any task but that. I just checked the 5 most popular configurations of server I see my customers deploy PostgreSQL onto (a mix of Dell and HP units), and you don't get a serious GPU from any of them. Intel's next generation Ivy Bridge chipset, expected for the spring of 2012, is going to add support for OpenCL to the built-in motherboard GPU. We may eventually see that trickle into the server hardware side of things too. I've never seen a PostgreSQL server capable of running CUDA, and I don't expect that to change. CUDA sorting could be beneficial on general server hardware if it can run well on multiple cpus in parallel. GPU-s being in essence parallel processors on fast shared memory, it may be that even on ordinary RAM and lots of CPUs some CUDA algorithms are a significant win. and then there is non-graphics GPU availabe on EC2 Cluster GPU Quadruple Extra Large Instance 22 GB of memory 33.5 EC2 Compute Units (2 x Intel Xeon X5570, quad-core “Nehalem” architecture) 2 x NVIDIA Tesla “Fermi” M2050 GPUs 1690 GB of instance storage 64-bit platform I/O Performance: Very High (10 Gigabit Ethernet) API name: cg1.4xlarge It costs $2.10 per hour, probably a lot less if you use the Spot Instances. -- 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] the big picture for index-only scans
On Sun, Aug 21, 2011 at 3:13 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: PS. Robert, the LOCKING section in the header comment of visibilitymap.c is out-of-date: it claims that the VM bit is cleared after releasing the lock on the heap page. Fixed, along with your other observation a couple of emails upthread. -- 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] Support UTF-8 files with BOM in COPY FROM
On mån, 2011-09-26 at 21:49 +0300, Peter Eisentraut wrote: If I store a BOM in row 1, column 1 of my table, because, well, maybe it's an XML document or something, then it needs to be able to survive a copy out and in. The only way we could proceed with this would be if we prohibited BOMs in all user-data. Alternative consideration: We could allow this in CSV format if we made users quote the first value if it starts with a BOM. This might be a reasonable way to get MS compatibility. -- 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] Postgresql parser
Excerpts from Florian Pflug's message of mar sep 27 08:28:00 -0300 2011: On Sep27, 2011, at 10:44 , andurkar wrote: Currently I am working on Postgresql... I need to study the gram.y and scan.l parser files...since I want to do some qery modification. Can anyone please help me to understand the files. What should I do ? Is there any documentation available ? scan.l defines the lexer, i.e. the algorithm that splits a string (containing an SQL statement) into a stream of tokens. A token is usually a single word (i.e., doesn't contain spaces but is delimited by spaces), but can also be a whole single or double-quoted string for example. The lexer is basically defined in terms of regular expressions which describe the different token types. Seemed a good answer so I added it to the developer's faq http://wiki.postgresql.org/wiki/Developer_FAQ#I_need_to_do_some_changes_to_query_parsing._Can_you_succintly_explain_the_parser_files.3F Feel free to edit. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Support UTF-8 files with BOM in COPY FROM
Peter Eisentraut pete...@gmx.net writes: Alternative consideration: We could allow this in CSV format if we made users quote the first value if it starts with a BOM. This might be a reasonable way to get MS compatibility. I don't think we can get away with a retroactive restriction on the contents of data files. If we're going to do this at all, I still think an explicit BOM option for COPY, to either eat (and require) a BOM on input or emit a BOM on output, would be the sanest way. None of the automatic approaches seem safe to me. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Addition of some trivial auto vacuum logging
Hi all, I spent a bit of today diagnosing a problem where long held transactions were preventing auto vacuum from doing its job. Eventually I had set log_autovacuum_min_duration to 0 to see what was going on. Unfortunately it wasn't until I tried a VACUUM VERBOSE that I found there were dead tuples not being removed. Had the unremoveable tuple count been included in the autovacuum log message life would have been a tiny bit easier. I've been meaning for a while to dabble in postgres, so I thought this might be a good trivial thing for me to improve. The attached patch adds extra detail the the existing autovacuum log message that is emitted when the log_autovacuum_min_duration threshold is met, exposing the unremovable dead tuple count similar to what you get from VACUUM VERBOSE. Sample log output (my addition in bold): LOG: automatic vacuum of table test.public.test: index scans: 0 pages: 0 removed, 5 remain tuples: 0 removed, 1000 remain, 999 dead but not removable system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec My patch adds another member to the LVRelStats struct named undeleteable_dead_tuples. lazy_scan_heap() sets undeleteable_dead_tuples to the value of lazy_scan_heap()'s local variable nkeep, which is the same variable that is used to emit the VACUUM VERBOSE unremoveable dead row count. As this is my first patch to postgresql, I'm expecting I've done something wrong. Please if you want me to fix something up, or just go away please say so ;) I appreciate that this is a trivial patch, and perhaps doesn't add value except for my very specific use case… feel free to ignore it =) --Royce diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c index cf8337b..12f03d7 100644 --- a/src/backend/commands/vacuumlazy.c +++ b/src/backend/commands/vacuumlazy.c @@ -91,6 +91,7 @@ typedef struct LVRelStats double scanned_tuples; /* counts only tuples on scanned pages */ double old_rel_tuples; /* previous value of pg_class.reltuples */ double new_rel_tuples; /* new estimated total # of tuples */ + double undeleteable_dead_tuples; /* count of dead tuples not yet removeable */ BlockNumber pages_removed; double tuples_deleted; BlockNumber nonempty_pages; /* actually, last nonempty page + 1 */ @@ -256,7 +257,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt, ereport(LOG, (errmsg(automatic vacuum of table \%s.%s.%s\: index scans: %d\n pages: %d removed, %d remain\n - tuples: %.0f removed, %.0f remain\n + tuples: %.0f removed, %.0f remain, %.0f dead but not removable\n system usage: %s, get_database_name(MyDatabaseId), get_namespace_name(RelationGetNamespace(onerel)), @@ -266,6 +267,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt, vacrelstats-rel_pages, vacrelstats-tuples_deleted, new_rel_tuples, + vacrelstats-undeleteable_dead_tuples, pg_rusage_show(ru0; } } @@ -829,6 +831,7 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats, /* save stats for use later */ vacrelstats-scanned_tuples = num_tuples; vacrelstats-tuples_deleted = tups_vacuumed; + vacrelstats-undeleteable_dead_tuples = nkeep; /* now we can compute the new value for pg_class.reltuples */ vacrelstats-new_rel_tuples = vac_estimate_reltuples(onerel, false,
Re: [HACKERS] [PATCH] Addition of some trivial auto vacuum logging
Royce Ausburn royce...@inomial.com writes: The attached patch adds extra detail the the existing autovacuum log message that is emitted when the log_autovacuum_min_duration threshold is met, exposing the unremovable dead tuple count similar to what you get from VACUUM VERBOSE. Sample log output (my addition in bold): LOG: automatic vacuum of table test.public.test: index scans: 0 pages: 0 removed, 5 remain tuples: 0 removed, 1000 remain, 999 dead but not removable system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec This proposal seems rather ill-designed. In the first place, these numbers are quite unrelated to vacuum duration, and in the second place, most people who might need the info don't have that setting turned on anyway. I wonder whether it wouldn't be more helpful to have a pg_stat_all_tables column that reports the number of unremovable tuples as of the last vacuum. I've been known to object to more per-table stats counters in the past on the basis of space required, but perhaps this one would be worth its keep. 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] Addition of some trivial auto vacuum logging
Royce Ausburn royce...@inomial.com wrote: As this is my first patch to postgresql, I'm expecting I've done something wrong. Please if you want me to fix something up, or just go away please say so ;) I appreciate that this is a trivial patch, and perhaps doesn't add value except for my very specific use case* feel free to ignore it =) Thanks for offering this to the community. I see you've already gotten feedback on the patch, with a suggestion for a different approach. Don't let that discourage you -- very few patches get in without needing to be modified based on review and feedback. If you haven't already done so, please review this page and its links: http://www.postgresql.org/developer/ Of particular interest is the Developer FAQ: http://wiki.postgresql.org/wiki/Developer_FAQ You should also be aware of the development cycle, which (when not in feature freeze for beta testing) involves alternating periods of focused development and code review (the latter called CommitFests): http://wiki.postgresql.org/wiki/CommitFest We are now in the midst of a CF, so it would be great if you could join in that as a reviewer. Newly submitted patches should be submitted to the open CF: http://commitfest.postgresql.org/action/commitfest_view/open You might want to consider what Tom said and submit a modified patch for the next review cycle. Welcome! -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] random isolation test failures
Excerpts from Tom Lane's message of mar sep 27 01:11:39 -0300 2011: Alvaro Herrera alvhe...@commandprompt.com writes: I just tweaked isolationtester so that it collects the error messages and displays them all together at the end of the test. After seeing it run, I didn't like it -- I think I prefer something more local, so that in the only case where we call try_complete_step twice in the loop, we report any errors in either. AFAICS this would make both expected cases behave identically in test output. Hmm, is that really an appropriate fix? I'm worried that it might mask event-ordering differences that actually are significant. In the attached, it only affects the case where there is one blocking command and another command that unblocks it; this is only exercised by the much-beaten fk-deadlock cases. If either of the steps fails with a deadlock error, it is reported identically, i.e. the error message is emitted as error in s1u1 s2u1: ERROR: deadlock detected So the deadlock could have been detected in either s1u1 or s2u1; we don't really care. The way error messages are reported in all the other cases is not changed, and these do not have a prefix; so if anything were to behave differently, we would find out because a spurious prefix would appear. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support isolation-fix-2.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib/sepgsql regression tests are a no-go
I wrote:
I think it should be possible to still use all the existing testing
infrastructure if the check/test script does something like
make REGRESS=label dml misc check
I've now worked through the process of actually running the sepgsql
regression tests, and I must say that I had no idea how utterly invasive
they were --- the idea that they could ever be part of a default make
check sequence is even more ridiculous than I thought before.
Accordingly, the attached patch does what I suggested above, namely dike
out the Makefile's knowledge of how to run the regression tests and put
it into the chkselinuxenv script. It would be appropriate to rename that
script to something like test_sepgsql, but I didn't do that yet to
reduce the displayed size of the patch.
I have not touched the documentation, either. One thing I'd like to do
is adjust both the SGML documentation and the hints printed by the
script to uniformly use sudo ...root-privileged-command... rather than
recommending use of su. I don't like the latter because it makes it
less than clear exactly which commands require root, encourages you to
forget to switch out of root mode, and IMO is against local policy on
any well-run box. I recognize however that that might be mostly my
own preferences showing --- what do others think?
Comments?
regards, tom lane
diff --git a/contrib/sepgsql/Makefile b/contrib/sepgsql/Makefile
index 033c41a..140419a 100644
*** a/contrib/sepgsql/Makefile
--- b/contrib/sepgsql/Makefile
*** OBJS = hooks.o selinux.o uavc.o label.o
*** 5,15
database.o schema.o relation.o proc.o
DATA_built = sepgsql.sql
! REGRESS = label dml misc
! REGRESS_PREP = check_selinux_environment
! REGRESS_OPTS = --launcher $(top_builddir)/contrib/sepgsql/launcher
!
! EXTRA_CLEAN = -r tmp *.pp sepgsql-regtest.if sepgsql-regtest.fc
ifdef USE_PGXS
PG_CONFIG = pg_config
--- 5,13
database.o schema.o relation.o proc.o
DATA_built = sepgsql.sql
! # Note: because we don't tell the Makefile there are any regression tests,
! # we have to clean those result files explicitly
! EXTRA_CLEAN = -r $(pg_regress_clean_files) tmp *.pp sepgsql-regtest.if sepgsql-regtest.fc
ifdef USE_PGXS
PG_CONFIG = pg_config
*** include $(top_srcdir)/contrib/contrib-gl
*** 23,28
endif
SHLIB_LINK += -lselinux
-
- check_selinux_environment:
- @$(top_builddir)/contrib/sepgsql/chkselinuxenv $(bindir) $(datadir)
--- 21,23
diff --git a/contrib/sepgsql/chkselinuxenv b/contrib/sepgsql/chkselinuxenv
index a7c81b2..2eeeb67 100755
*** a/contrib/sepgsql/chkselinuxenv
--- b/contrib/sepgsql/chkselinuxenv
***
*** 1,11
#!/bin/sh
#
! # SELinux environment checks to ensure configuration of the operating system
! # satisfies prerequisites to run regression test.
! # If incorrect settings are found, this script suggest user a hint.
#
! PG_BINDIR=$1
! PG_DATADIR=$2
echo
echo == checking selinux environment ==
--- 1,18
#!/bin/sh
#
! # Run the sepgsql regression tests, after making a lot of environmental checks
! # to try to ensure that the SELinux environment is set up appropriately and
! # the database is configured correctly.
#
! # Note that this must be run against an installed Postgres database.
! # There's no equivalent of make check, and that wouldn't be terribly useful
! # since much of the value is in checking that you installed sepgsql into
! # your database correctly.
! #
! # This must be run in the contrib/sepgsql directory of a Postgres build tree.
! #
!
! PG_BINDIR=`pg_config --bindir`
echo
echo == checking selinux environment ==
*** fi
*** 224,230
echo found ${NUM}
#
! # check complete -
#
! echo
! exit 0
--- 231,242
echo found ${NUM}
#
! # checking complete - let's run the tests
#
!
! echo
! echo == running sepgsql regression tests ==
!
! make REGRESS=label dml misc REGRESS_OPTS=--launcher ./launcher installcheck
!
! # exit with the exit code provided by make
--
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] random isolation test failures
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Tom Lane's message of mar sep 27 01:11:39 -0300 2011: Hmm, is that really an appropriate fix? I'm worried that it might mask event-ordering differences that actually are significant. In the attached, it only affects the case where there is one blocking command and another command that unblocks it; this is only exercised by the much-beaten fk-deadlock cases. If either of the steps fails with a deadlock error, it is reported identically, i.e. the error message is emitted as error in s1u1 s2u1: ERROR: deadlock detected So the deadlock could have been detected in either s1u1 or s2u1; we don't really care. Hmm. For the case of deadlock detected, we actually don't *want* to care because the infrastructure is such that either process might report it. So I agree that this is a good fix for that case. I'm just worried whether it will obscure other situations where it's important to know which command failed. But if you're convinced there aren't any, fine. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib/sepgsql regression tests are a no-go
On Tue, Sep 27, 2011 at 3:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: I think it should be possible to still use all the existing testing infrastructure if the check/test script does something like make REGRESS=label dml misc check I've now worked through the process of actually running the sepgsql regression tests, and I must say that I had no idea how utterly invasive they were --- the idea that they could ever be part of a default make check sequence is even more ridiculous than I thought before. Accordingly, the attached patch does what I suggested above, namely dike out the Makefile's knowledge of how to run the regression tests and put it into the chkselinuxenv script. It would be appropriate to rename that script to something like test_sepgsql, but I didn't do that yet to reduce the displayed size of the patch. Seems fine. The rename is definitely needed. We may want to back-patch this into 9.1 to avoid the headache of dealing with this for 5 years. I have not touched the documentation, either. One thing I'd like to do is adjust both the SGML documentation and the hints printed by the script to uniformly use sudo ...root-privileged-command... rather than recommending use of su. I don't like the latter because it makes it less than clear exactly which commands require root, encourages you to forget to switch out of root mode, and IMO is against local policy on any well-run box. I recognize however that that might be mostly my own preferences showing --- what do others think? I think that's your own preference showing. How about just telling people to run the commands as root without specifying how they should accomplish that? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Log crashed backend's query v2
On Sat, Sep 24, 2011 at 22:57, Marti Raudsepp ma...@juffo.org wrote:
However, I now realize that it does make sense to write a separate
simpler function for the crashed backend case with no
vbeentry-st_changecount check loops, no checkUser, etc. That would be
more robust and easier to review.
I implemented this now, but I'm not convinced anymore that it's the
right way to go. I'm duplicating some amount of code that could be
subject to bitrot in the future since this code path won't be
excercised often. But I'll let the reviewers decide.
Is there a sane way to regression-test backend crashes?
I propsed replacing non-ASCII characters with '?' earlier.
This is also in. I created a new function in
backend/utils/adt/ascii.c. It didn't quite fit in because all other
functions in this file are dealing with Datums, but I couldn't find a
better place.
(I'm still not sure what adt means)
Regards,
Marti
From 0f46bb1357bafbe940e7df8fce38c01e2237f57e Mon Sep 17 00:00:00 2001
From: Marti Raudsepp ma...@juffo.org
Date: Wed, 28 Sep 2011 00:46:48 +0300
Subject: [PATCH] Log crashed backend's query (activity string)
The crashing query is often a good starting point in debugging the
cause, and much more easily accessible than core dumps.
We're extra-paranoid in reading the activity buffer since it might be
corrupt. All non-ASCII characters are replaced with '?'
Example output:
LOG: server process (PID 31451) was terminated by signal 9: Killed
DETAIL: Running query: DO LANGUAGE plpythonu 'import os;os.kill(os.getpid(),9)'
---
src/backend/postmaster/pgstat.c | 59 +++
src/backend/postmaster/postmaster.c | 17 +++---
src/backend/utils/adt/ascii.c | 31 ++
src/include/pgstat.h|1 +
src/include/utils/ascii.h |1 +
5 files changed, 104 insertions(+), 5 deletions(-)
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index eb9adc8..812bf04 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -58,6 +58,7 @@
#include storage/pg_shmem.h
#include storage/pmsignal.h
#include storage/procsignal.h
+#include utils/ascii.h
#include utils/guc.h
#include utils/memutils.h
#include utils/ps_status.h
@@ -2747,6 +2748,64 @@ pgstat_get_backend_current_activity(int pid, bool checkUser)
return backend information not available;
}
+/* --
+ * pgstat_get_backend_current_activity() -
+ *
+ * Return a string representing the current activity of the backend with
+ * the specified PID. Like the function above, but reads shared memory with
+ * the expectation that it may be corrupt.
+ *
+ * This function is only intended to be used by postmaster to report the
+ * query that crashed the backend. In particular, no attempt is made to
+ * follow the correct concurrency protocol when accessing the
+ * BackendStatusArray. But that's OK, in the worst case we get a corrupted
+ * message. We also must take care not to trip on ereport(ERROR).
+ *
+ * Note: return strings for special cases match pg_stat_get_backend_activity.
+ * --
+ */
+const char *
+pgstat_get_crashed_backend_activity(int pid)
+{
+ volatile PgBackendStatus *beentry;
+ int i;
+
+ beentry = BackendStatusArray;
+ for (i = 1; i = MaxBackends; i++)
+ {
+ if (beentry-st_procpid == pid)
+ {
+ /* Read pointer just once, so it can't change after validation */
+ const char *activity = beentry-st_activity;
+ char *buffer;
+
+ /*
+ * We can't access activity pointer before we verify that it
+ * falls into BackendActivityBuffer. To make sure that the
+ * string's ending is contained within the buffer, the string
+ * can start at offset (MaxBackends - 1) at most.
+ */
+ if (activity BackendActivityBuffer ||
+activity (BackendActivityBuffer +
+ (MaxBackends - 1) * pgstat_track_activity_query_size))
+return command string corrupt;
+
+ if (*(activity) == '\0')
+return command string not enabled;
+
+ buffer = (char *) palloc(pgstat_track_activity_query_size);
+ ascii_safe_strncpy(buffer, activity,
+ pgstat_track_activity_query_size);
+
+ return buffer;
+ }
+
+ beentry++;
+ }
+
+ /* PID not found */
+ return backend information not available;
+}
/*
* Local support functions follow
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index 94b57fa..9ba622c 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -2763,6 +2763,8 @@ HandleChildCrash(int pid, int exitstatus, const char *procname)
static void
LogChildExit(int lev, const char *procname, int pid, int exitstatus)
{
+ const char *activity = pgstat_get_crashed_backend_activity(pid);
+
if (WIFEXITED(exitstatus))
ereport(lev,
@@ -2770,7 +2772,8 @@ LogChildExit(int lev, const char *procname, int pid, int exitstatus)
translator: %s is a noun
Re: [HACKERS] contrib/sepgsql regression tests are a no-go
Robert Haas robertmh...@gmail.com writes: On Tue, Sep 27, 2011 at 3:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Accordingly, the attached patch does what I suggested above, namely dike out the Makefile's knowledge of how to run the regression tests and put it into the chkselinuxenv script. Seems fine. The rename is definitely needed. We may want to back-patch this into 9.1 to avoid the headache of dealing with this for 5 years. I'm definitely gonna back-patch it, because otherwise I'll be carrying it as a RHEL and Fedora patch for that long ;-) I have not touched the documentation, either. One thing I'd like to do is adjust both the SGML documentation and the hints printed by the script to uniformly use sudo ...root-privileged-command... rather than recommending use of su. I think that's your own preference showing. How about just telling people to run the commands as root without specifying how they should accomplish that? Well, maybe, but it seems hard to do without being verbose. If you just say $ sudo blah blah blah the meaning is obvious (or if it isn't, you got no business playing with SELinux anyway), and you can easily include, or not, the sudo part when copying and pasting the command. Right now we've got things like $ cd .../contrib/sepgsql $ make -f /usr/share/selinux/devel/Makefile $ su # semodule -u sepgsql-regtest.pp # semodule -l | grep sepgsql sepgsql-regtest 1.03 What I'd prefer is $ cd .../contrib/sepgsql $ make -f /usr/share/selinux/devel/Makefile $ sudo semodule -u sepgsql-regtest.pp $ sudo semodule -l | grep sepgsql sepgsql-regtest 1.03 If I have to break up the recipe with annotations like run this part as root and then these commands no longer need root, I don't think that's going to be an improvement over either of the above. 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] Hot Backup with rsync fails at pg_clog if under load
On Sep23, 2011, at 21:10 , Robert Haas wrote: So the actual error message in the last test was: 2011-09-21 13:41:05 CEST FATAL: could not access status of transaction 1188673 ...but we can't tell if that was before or after nextXid, which seems like it would be useful to know. If Linas can rerun his experiment, but also capture the output of pg_controldata before firing up the standby for the first time, then we'd able to see that information. Hm, wouldn't pg_controldata quite certainly show a nextId beyond the clog if copied after pg_clog/*? Linas, could you capture the output of pg_controldata *and* increase the log level to DEBUG1 on the standby? We should then see nextXid value of the checkpoint the recovery is starting from. FWIW, I've had a few more theories about what's going on, but none survived after looking at the code. My first guess was that there maybe are circumstances under which the nextId from the control file, instead of the one from the pre-backup checkpoint, ends up becoming the standby's nextXid. But there doesn't seem to be a way for that to happen. My next theory was that something increments nextIdx before StartupCLOG(). The only possible candidate seems to be PrescanPreparedTransactions(), which does increment nextXid if it's smaller than some sub-xid of a prepared xact. But we only call that before StartupCLOG() if we're starting from a shutdown checkpoint, which shouldn't be the case for the OP. I also checked what rsync does when a file vanishes after rsync computed the file list, but before it is sent. rsync 3.0.7 on OSX, at least, complains loudly, and doesn't sync the file. It BTW also exits non-zero, with a special exit code for precisely that failure case. 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] Log crashed backend's query v2
On Sep28, 2011, at 00:19 , Marti Raudsepp wrote: (I'm still not sure what adt means) I always assumed it stood for abstract data type. Most of the files in this directory seem to correspond to an SQL-level data type like intX, varchar, tsquery, ..., and contain the I/O functions for that type, plus some supporting operations and functions. Over time, it seems that this directory was also used for SQL-level functions not directly related to a single type, like windowfuncs.c and pgstatfuncs.c. The fact that ri_triggers.c lives there also might be a relict from times where you'd create FK constraint with CREATE CONSTRAINT TRIGGER and specified one of the functions from ri_triggers.c as the procedure to execute. 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] Online base backup from the hot-standby
On 11-09-26 10:56 PM, Fujii Masao wrote:
Looks weired. Though the WAL record starting from 0/6000298 was read
successfully, then re-fetch of the same record fails at the end of recovery.
One possible cause is the corruption of archived WAL file. What
restore_command on the standby and archive_command on the master
are you using? Could you confirm that there is no chance to overwrite
archive WAL files in your environment?
I tried to reproduce this problem several times, but I could not. Could
you provide the test case which reproduces the problem?
This is the test procedure I'm trying today, I wasn't able to reproduce
the crash. What I was doing the other day was similar but I can't speak
to unintentional differences.
I have my master server
data
port=5439
wal_level=hot_standby
archive_mode=on
archive_command='cp -i %p /usr/local/pgsql92git/archive/%f'
hot_standby=on
I then run
select pg_start_backup('foo');
$ rm -r ../data2
$ cp -r ../data ../data2
$ rm ../data2/postmaster.pid
select pg_stop_backup();
I edit data2/postgresql.conf so
port=5438
I commented out archive_mode and archive_command (or at least today I did)
recovery.conf is
standby_mode='on'
primary_conninfo='host=127.0.0.1 port=5439 user=ssinger dbname=test'
restore_command='cp /usr/local/pgsql92git/archive/%f %p'
I then start up the second cluster. On it I run
select pg_start_backup('1');
$ rm -r ../data3
$ rm -r ../archive2
$ cp -r ../data2 ../data3
$ cp ../data2/global/pg_control ../data3/global
select pg_stop_backup();
I edit ../data2/postgresql.conf
port=5437
archive_mode=on
# (change requires restart)
archive_command='cp -i %p /usr/local/pgsql92git/archive2/%f'
recovery.conf is
standby_mode='on'
primary_conninfo='host=127.0.0.1 port=5439 user=ssinger dbname=test'
restore_command='cp /usr/local/pgsql92git/archive/%f %p'
trigger_file='/tmp/3'
$ postgres -D ../data3
The first time I did this postgres came up quickly.
$ touch /tmp/3
worked fine.
I then stopped data3
$ rm -r ../data3
on data 2 I run
pg_start_backup('1')
$ cp -r ../data2 ../data3
$ cp ../data2/global/pg_control ../data3/global
select pg_stop_backup() # on data2
$ rm ../data3/postmaster.pid
vi ../data3/postgresql.conf # same changes as above for data3
vi ../data3/recovery.conf # same as above for data 3
postgres -D ../data3
This time I got
./postgres -D ../data3
LOG: database system was interrupted while in recovery at log time
2011-09-27 22:04:17 GMT
HINT: If this has occurred more than once some data might be corrupted
and you might need to choose an earlier recovery target.
LOG: entering standby mode
cp: cannot stat
`/usr/local/pgsql92git/archive/0001000C': No such file
or directory
LOG: redo starts at 0/C20
LOG: record with incorrect prev-link 0/958 at 0/CB0
cp: cannot stat
`/usr/local/pgsql92git/archive/0001000C': No such file
or directory
LOG: streaming replication successfully connected to primary
FATAL: the database system is starting up
FATAL: the database system is starting up
LOG: consistent recovery state reached at 0/CE8
LOG: database system is ready to accept read only connections
In order to get the database to come in read only mode I manually issued
a checkpoint on the master (data) shortly after the checkpoint command
the data3 instance went to read only mode.
then
touch /tmp/3
trigger file found: /tmp/3
FATAL: terminating walreceiver process due to administrator command
cp: cannot stat
`/usr/local/pgsql92git/archive/0001000C': No such file
or directory
LOG: record with incorrect prev-link 0/9000298 at 0/C0002F0
cp: cannot stat
`/usr/local/pgsql92git/archive/0001000C': No such file
or directory
LOG: redo done at 0/C000298
cp: cannot stat
`/usr/local/pgsql92git/archive/0001000C': No such file
or directory
cp: cannot stat `/usr/local/pgsql92git/archive/0002.history': No
such file or directory
LOG: selected new timeline ID: 2
cp: cannot stat `/usr/local/pgsql92git/archive/0001.history': No
such file or directory
LOG: archive recovery complete
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
It looks like data3 is still pulling files with the recovery command
after it sees the touch file (is this expected behaviour?)
$ grep archive ../data3/postgresql.conf
#wal_level = minimal# minimal, archive, or hot_standby
#archive_mode = off# allows archiving to be done
archive_mode=on
archive_command='cp -i %p /usr/local/pgsql92git/archive2/%f'
I have NOT been able to make postgres crash during a recovery (today).
It is *possible* that on some of my runs the other day I had skipped
changing the archive command on data3 to write to archive2 instead of
archive.
I have also today not been able to get it to attempt to restore the same
WAL file twice.
If a base backup is in progress on a recovery database
[HACKERS] pg_upgrade - add config directory setting
It would perhaps be useful to add optional --old-confdir and --new-confdir parameters to pg_upgrade. If these parameters are absent then pg_upgrade would work as it does now and assume that the config files are in the datadir. The reason for this suggestion is that packages for Ubuntu (and I suppose Debian and possibly others) place the config files in a different directory than the data files. The Ubuntu packaging, for example, puts all the configuration files in /etc/postgresql/VERSION/main/. If I set the data-directories to /var/lib/postgresql/VERSION/main then pg_upgrade complains about missing config files. If I set the data directories to /etc/postgresql/VERSION/main/ then pg_upgrade complains that the base subdirectory is missing. Temporarily symlinking postgresql.conf and pg_hba.conf from the config directory to the data directory allowed the upgrade to run successfully but is a bit more kludgey and non-obvious. 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
[HACKERS] Mismatch of relation names: pg_toast.pg_toast_nnn during pg_upgrade from 8.4 to 9.1
Hi - I've had no problem upgrading copies our qa databases (which are backed up and restored with pg_dump/pg_restore) but have run into the same problem each time I try to upgrade a copy of our production database (backed up and restored via PITR). After verifying a successful restore and vacuum analyze, I ran a successful pg_upgrade check run with: pg_upgrade -b /usr/local/pgsql-8.4.2/bin -B /usr/local/pgsql-9.1.0/bin -c -d /data/pgsql/prod-84 -D /data/pgsql/prod-91 -G /home/postgres/pg_upgrade_prod.check.debug -k -l /home/postgres/pg_upgrade_prod.check.log -p 5435 -P 5436 -v Then when I ran the actual pg_upgrade: pg_upgrade -b /usr/local/pgsql-8.4.2/bin -B /usr/local/pgsql-9.1.0/bin -d /data/pgsql/prod-84 -D /data/pgsql/prod-91 -G /home/postgres/pg_upgrade_prod.debug -k -l /home/postgres/pg_upgrade_prod.log -p 5435 -P 5436 -v It fails at this stage: Restoring user relation files linking /data/pgsql/prod-84/base/11564/2613 to /data/pgsql/prod-91/base/12698/12570 linking /data/pgsql/prod-84/base/11564/2683 to /data/pgsql/prod-91/base/12698/12572 Mismatch of relation names: database prod1, old rel pg_toast.pg_toast_54542379, new rel pg_toast.pg_toast_16735 Failure, exiting These are the log files generated: -rw-rw-r-- 1 postgres postgres 0 Sep 27 12:47 pg_upgrade_prod.check.debug -rw-rw-r-- 1 postgres postgres 8524 Sep 27 13:27 pg_upgrade_prod.check.log -rw-rw-r-- 1 postgres postgres 0 Sep 27 13:31 pg_upgrade_prod.debug -rw-rw-r-- 1 postgres postgres 2374 Sep 27 14:12 pg_upgrade_dump_globals.sql -rw-rw-r-- 1 postgres postgres 257696 Sep 27 14:12 pg_upgrade_dump_db.sql -rw-rw-r-- 1 postgres postgres 260092 Sep 27 14:12 pg_upgrade_dump_all.sql -rw-rw-r-- 1 postgres postgres 75413 Sep 27 14:12 pg_upgrade_prod.log I don't see any helpful (to me) explanation within them, but after some searching ran across this comment in the relevant section of source: 00059 /* 00060 * In pre-8.4, TOAST table names change during CLUSTER; in = 8.4 00061 * TOAST relation names always use heap table oids, hence we cannot 00062 * check relation names when upgrading from pre-8.4. 00063 */ At this point I still don't understand the underlying issue. Our databases were migrated in place (linked) with pg_migrator from 8.3 to 8.4 could that be the cause or related? Where would you recommend I look to discover a cause and hopefully a solution? Thanks in advance, Jamie -- 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] Addition of some trivial auto vacuum logging
Thanks for the tips guys. Just a question: is the utility great enough to warrant me working further on this? I have no real desire to implement this particular feature -- I simply saw an opportunity to cut my teeth on something easy. I'd be happy to find something on the TODO list instead if this feature isn't really worthwhile. Tom's suggestion looks like it's less trivial that I can do just yet, but I'll take a look and ask for help if I need it. Cheers! --Royce On 28/09/2011, at 4:42 AM, Kevin Grittner wrote: Royce Ausburn royce...@inomial.com wrote: As this is my first patch to postgresql, I'm expecting I've done something wrong. Please if you want me to fix something up, or just go away please say so ;) I appreciate that this is a trivial patch, and perhaps doesn't add value except for my very specific use case* feel free to ignore it =) Thanks for offering this to the community. I see you've already gotten feedback on the patch, with a suggestion for a different approach. Don't let that discourage you -- very few patches get in without needing to be modified based on review and feedback. If you haven't already done so, please review this page and its links: http://www.postgresql.org/developer/ Of particular interest is the Developer FAQ: http://wiki.postgresql.org/wiki/Developer_FAQ You should also be aware of the development cycle, which (when not in feature freeze for beta testing) involves alternating periods of focused development and code review (the latter called CommitFests): http://wiki.postgresql.org/wiki/CommitFest We are now in the midst of a CF, so it would be great if you could join in that as a reviewer. Newly submitted patches should be submitted to the open CF: http://commitfest.postgresql.org/action/commitfest_view/open You might want to consider what Tom said and submit a modified patch for the next review cycle. Welcome! -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] [PATCH] Addition of some trivial auto vacuum logging
* Royce Ausburn (royce...@inomial.com) wrote: Just a question: is the utility great enough to warrant me working further on this? I have no real desire to implement this particular feature -- I simply saw an opportunity to cut my teeth on something easy. I'd be happy to find something on the TODO list instead if this feature isn't really worthwhile. Seeing as how it's already got one committer willing to consider it (and that one tends to go the other direction on new features..), I'd definitely say it's worthwhile. That doesn't mean it's guaranteed to get in, but I'd put the probability above 75% given that feedback. That's pretty good overall. :) Tom's suggestion looks like it's less trivial that I can do just yet, but I'll take a look and ask for help if I need it. Don't let the notion of fiddling with the catalogs (system tables) discourage you.. It's really not all *that* bad. What you will need to figure out (and which I don't recall offhand..) is if you can just update those catalogs directly from VACUUM or if you need to go through the statistics collecter (which involves a bit of UDP communication, but hopefully we've abstracted that out enough that you won't have to deal with it directly really..). Looking at an existing example case where VACUUM is doing something that updates the stat tables (such as under the 'ANALYZE' option) will help out a lot, I'm sure. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] Addition of some trivial auto vacuum logging
Excerpts from Royce Ausburn's message of mar sep 27 21:28:26 -0300 2011: Thanks for the tips guys. Just a question: is the utility great enough to warrant me working further on this? I have no real desire to implement this particular feature -- I simply saw an opportunity to cut my teeth on something easy. I'd be happy to find something on the TODO list instead if this feature isn't really worthwhile. First patches are always going to be small things. If you try to tackle something too large, chances are you'll never finish, despair utterly and throw yourself off a nearby bridge. Surely it's better to set realistic goals, start small and build slowly from there. Tom's suggestion looks like it's less trivial that I can do just yet, but I'll take a look and ask for help if I need it. It's not that difficult. Just do a search on git log src/backend/postmaster/pgstat.c for patches that add a new column somewhere. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib/sepgsql regression tests are a no-go
On Tue, Sep 27, 2011 at 6:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: I have not touched the documentation, either. One thing I'd like to do is adjust both the SGML documentation and the hints printed by the script to uniformly use sudo ...root-privileged-command... rather than recommending use of su. I think that's your own preference showing. How about just telling people to run the commands as root without specifying how they should accomplish that? Well, maybe, but it seems hard to do without being verbose. If you just say $ sudo blah blah blah the meaning is obvious (or if it isn't, you got no business playing with SELinux anyway), and you can easily include, or not, the sudo part when copying and pasting the command. Right now we've got things like $ cd .../contrib/sepgsql $ make -f /usr/share/selinux/devel/Makefile $ su # semodule -u sepgsql-regtest.pp # semodule -l | grep sepgsql sepgsql-regtest 1.03 What I'd prefer is $ cd .../contrib/sepgsql $ make -f /usr/share/selinux/devel/Makefile $ sudo semodule -u sepgsql-regtest.pp $ sudo semodule -l | grep sepgsql sepgsql-regtest 1.03 If I have to break up the recipe with annotations like run this part as root and then these commands no longer need root, I don't think that's going to be an improvement over either of the above. Fair enough, I'm not going to get bent out of shape about it. There's some aesthetic value in the way you're proposing, and anyone who is doing this ought to know enough to make the details of how you write it out mostly irrelevant. -- 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] [PATCH] Addition of some trivial auto vacuum logging
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Royce Ausburn's message of mar sep 27 21:28:26 -0300 2011: Tom's suggestion looks like it's less trivial that I can do just yet, but I'll take a look and ask for help if I need it. It's not that difficult. Just do a search on git log src/backend/postmaster/pgstat.c for patches that add a new column somewhere. Yeah, I was just about to say the same thing. Find a previous patch that adds a feature similar to what you have in mind, and crib like mad. We've added enough stats counters over time that you should have several models to work from. 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_upgrade - add config directory setting
On Tue, Sep 27, 2011 at 04:13:41PM -0700, Steve Crawford wrote:
It would perhaps be useful to add optional --old-confdir and
--new-confdir parameters to pg_upgrade. If these parameters are absent
then pg_upgrade would work as it does now and assume that the config
files are in the datadir.
The reason for this suggestion is that packages for Ubuntu (and I
suppose Debian and possibly others) place the config files in a
different directory than the data files.
The Ubuntu packaging, for example, puts all the configuration files in
/etc/postgresql/VERSION/main/.
If I set the data-directories to /var/lib/postgresql/VERSION/main then
pg_upgrade complains about missing config files.
If I set the data directories to /etc/postgresql/VERSION/main/ then
pg_upgrade complains that the base subdirectory is missing.
Temporarily symlinking postgresql.conf and pg_hba.conf from the config
directory to the data directory allowed the upgrade to run successfully
but is a bit more kludgey and non-obvious.
Cheers,
Steve
I was just about to submit this suggestion. We do the same on Gentoo, as a
default anyway. (Users can pick their own locations for the configuration files
and data directories.) It would simplify the upgrade process by eliminating two
to four steps. (Symlink/copy configuration files in /etc/postgresql-${SLOT}
to /var/lib/postgresql-${SLOT}, same to $version++, pg_upgrade, remove
symlinks.)
--
Mr. Aaron W. Swenson
Pseudonym: TitanOfOld
Gentoo Developer
pgpA97Tt6jV2d.pgp
Description: PGP signature
Re: [HACKERS] [PATCH] Log crashed backend's query v2
Florian Pflug f...@phlo.org writes: On Sep28, 2011, at 00:19 , Marti Raudsepp wrote: (I'm still not sure what adt means) I always assumed it stood for abstract data type. Yeah, that's what I think too. Over time it's been used to hold most code that is a SQL-callable function, many of which are not directly connected to any SQL datatype. Not sure if it's worth trying to clean that up. Another annoying thing is that adt should probably have been directly under src/backend/ --- dropping it under utils/ seems just weird for a category that is going to hold a ton of code. (I had once had some hope that git would allow us to move code around more easily, but my experiments with back-patching after code movement have convinced me that it doesn't work any better for that than CVS. So I'm not in favor of massive code rearrangements just to make the source tree structure cleaner.) 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] Online base backup from the hot-standby
On Wed, Sep 28, 2011 at 8:10 AM, Steve Singer ssinger...@sympatico.ca wrote: This is the test procedure I'm trying today, I wasn't able to reproduce the crash. What I was doing the other day was similar but I can't speak to unintentional differences. Thanks for the info! I tried your test case three times, but was not able to reproduce the issue, too. BTW, I created the shell script (attached) which runs your test scenario and used it for the test. If the issue will happen again, please feel free to share the information about it. I will diagnose it. It looks like data3 is still pulling files with the recovery command after it sees the touch file (is this expected behaviour?) Yes, that's expected behavior. After the trigger file is found, PostgreSQL tries to replay all available WAL files in pg_xlog directory and archive one. So, if there is unreplayed archived WAL file at that time, PostgreSQL tries to pull it by calling the recovery command. And, after WAL replay is done, PostgreSQL tries to re-fetch the last replayed WAL record in order to identify the end of replay location. So, if the last replayed record is included in the archived WAL file, it's pulled by the recovery command. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center test.sh Description: Bourne shell script -- 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_upgrade automatic testing
Bruce Momjian wrote:
Peter Eisentraut wrote:
8.4 - master upgrade fails like this:
Restoring user relation files
Mismatch of relation names in database regression: old name
pg_toast.pg_toast_27437, new name pg_toast.pg_toast_27309
Failure, exiting
This has been 100% reproducible for me.
I can now reproduce this failure and will research the cause, probably
not before next week though. :-( What is interesting is that loading
the regression tests from an SQL dump does not show the failure, but
running the regression tests and then upgrading does.
OK, I found time to research this and I think I have a fix. The problem
is caused by an ALTER TABLE in 8.4 not preserving a toast table name
that matches the heap oid. Below you can see that 8.4 does not preserve
this, while 9.0 does:
8.4
---
test= CREATE TABLE test5(aa TEXT, bb TEXT);
CREATE TABLE
test= INSERT INTO test5 VALUES ('123', '323');
INSERT 0 1
test= ALTER TABLE test5 ALTER COLUMN aa TYPE INTEGER USING
bit_length(aa);
ALTER TABLE
test= SELECT oid, reltoastrelid FROM pg_class WHERE relname = 'test5';
oid | reltoastrelid
---+---
--- 16406 | 16415
(1 row)
test= SELECT relname FROM pg_class WHERE oid = 16415;
relname
pg_toast_16412 ---
(1 row)
9.0
---
test= CREATE TABLE test5(aa TEXT, bb TEXT);
CREATE TABLE
test= INSERT INTO test5 VALUES ('123', '323');
INSERT 0 1
test= ALTER TABLE test5 ALTER COLUMN aa TYPE INTEGER USING
bit_length(aa);
ALTER TABLE
test= SELECT oid, reltoastrelid FROM pg_class WHERE relname = 'test5';
oid | reltoastrelid
---+---
--- 16409 | 16418
(1 row)
test= SELECT relname FROM pg_class WHERE oid = 16418;
relname
pg_toast_16409 ---
(1 row)
We must have fixed this in 9.0 and I missed it. Anyway, the pg_upgrade
code already assumes pre-8.4 doesn't have stable toast names:
/*
* In pre-8.4, TOAST table names change during CLUSTER; in = 8.4
* TOAST relation names always use heap table oids, hence we cannot
* check relation names when upgrading from pre-8.4.
*/
if (strcmp(old_rel-nspname, new_rel-nspname) != 0 ||
((GET_MAJOR_VERSION(old_cluster.major_version) = 804 ||
strcmp(old_rel-nspname, pg_toast) != 0)
strcmp(old_rel-relname, new_rel-relname) != 0))
pg_log(PG_FATAL, Mismatch of relation names in database \%s\:
old name \%s.%s\, new name \%s.%s\\n,
old_db-db_name, old_rel-nspname, old_rel-relname,
new_rel-nspname, new_rel-relname);
Looking at this code now, I realize it is wrong even without the 8.4
ALTER issue. If someone uses pg_upgrade to go from 8.3 to 8.4, they
would then still have the toast table name mismatch when going to 9.0,
so the test in itself is wrong anyway. I propose I just remove the 8.4
test and always allow toast table names not to match --- the oids are
still checked and are preserved.
The current code is just too conservative and throws an error during
upgrade (but not during check mode) when it shouldn't. This code only
exists in 9.1 and HEAD.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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_upgrade automatic testing
Bruce Momjian br...@momjian.us writes: I propose I just remove the 8.4 test and always allow toast table names not to match --- the oids are still checked and are preserved. +1. You'll still make the check for non-toast tables, of course? 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] fix for pg_upgrade
panam wrote:
Hi Bruce,
here is the whole dump (old DB):
http://postgresql.1045698.n5.nabble.com/file/n4844725/dump.txt dump.txt
Wow, that is interesting. I see this in the dump output:
-- For binary upgrade, must preserve relfilenodes
SELECT
binary_upgrade.set_next_heap_relfilenode('465783'::pg_catalog.oid);
SELECT
binary_upgrade.set_next_toast_relfilenode('465786'::pg_catalog.oid);
SELECT
binary_upgrade.set_next_index_relfilenode('465788'::pg_catalog.oid);
CREATE TABLE accounts (
guid character varying(32) NOT NULL,
name character varying(2048) NOT NULL,
account_type character varying(2048) NOT NULL,
commodity_guid character varying(32),
commodity_scu integer NOT NULL,
non_std_scu integer NOT NULL,
parent_guid character varying(32),
code character varying(2048),
description character varying(2048),
hidden integer,
placeholder integer
);
and it is clearly saying the oid/relfilenode should be 465783, but your
9.1 query shows:
C:\Program Files\PostgreSQL\9.1\binpsql -c select * from pg_class
where oid = 465783 or oid = 16505; -p 5433 -U postgres
relname | relnamespace | reltype | reloftype | relowner | relam |
relfilenode | reltablespace | relpages | reltuples | reltoastrelid |
reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts
| relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers |
relhassubclass | relfrozenxid | relacl | reloptions
--+--+-+---+--+---+-+---+--+---+---+---+-+-++-+--+---+++-+++--++
accounts | 2200 | 16507 | 0 |16417 | 0 |
16505 | 0 |0 | 0 | 16508 | 0
| t | f | p | r | 11 | 0 |
f | t | f | f | f |
3934366 ||
(1 row)
and 9.0 says correctly 465783:
C:\Program Files\PostgreSQL\9.0\binpsql -c select * from pg_class
where oid = 465783 or oid = 16505; -p 5432 -U postgres
relname | relnamespace | reltype | reloftype | relowner | relam |
relfilenode | reltablespace | relpages | reltuples | reltoastrelid |
reltoastidxid | relhasindex | relisshared | relistemp | relkind | relnatts |
relchecks | relhasoids | relhaspkey | relhasexclusion | relhasrules |
relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions
--+--+-+---+--+---+-+---+--+---+---+---+-+-+---+-+--+---+++-+-+++--++
accounts | 465781 | 465785 | 0 | 456619 | 0 |
465783 | 0 |3 | 122 |465786 | 0
| t | f | f | r | 11 | 0 | f
| t | f | f | f | f
| 3934366 ||
(1 row)
It is as though the system ignoring the set_next_heap_relfilenode()
call, but I don't see how that could happen. I don't see any other
'accounts' table in that dump.
My only guess at this point is that somehow the -b/IsBinaryUpgrade flag
is not being processed or regognized, and hence the binary_upgrade 'set'
routines are not working.
Is this 9.1 final or later? Can you turn on debug mode and send me the
pg_upgrade log file that is generated? I am going go look for the
pg_ctl -o '-b' flag. Are all databases/objects failing or just this
one?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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_upgrade automatic testing
Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
I propose I just remove the 8.4
test and always allow toast table names not to match --- the oids are
still checked and are preserved.
+1. You'll still make the check for non-toast tables, of course?
Yes, only toast tables will skip the check. Proposed patch attached.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c
new file mode 100644
index e41ab2b..7b1ab36
*** a/contrib/pg_upgrade/info.c
--- b/contrib/pg_upgrade/info.c
*** gen_db_file_maps(DbInfo *old_db, DbInfo
*** 57,69
old_db-db_name, old_rel-reloid, new_rel-reloid);
/*
! * In pre-8.4, TOAST table names change during CLUSTER; in = 8.4
! * TOAST relation names always use heap table oids, hence we cannot
! * check relation names when upgrading from pre-8.4.
*/
if (strcmp(old_rel-nspname, new_rel-nspname) != 0 ||
! ((GET_MAJOR_VERSION(old_cluster.major_version) = 804 ||
! strcmp(old_rel-nspname, pg_toast) != 0)
strcmp(old_rel-relname, new_rel-relname) != 0))
pg_log(PG_FATAL, Mismatch of relation names in database \%s\:
old name \%s.%s\, new name \%s.%s\\n,
--- 57,73
old_db-db_name, old_rel-reloid, new_rel-reloid);
/*
! * TOAST table names initially match the heap pg_class oid.
! * However, in pre-8.4, TOAST table names change during CLUSTER, and
! * in pre-9.0, TOAST table names change during ALTER TABLE. Because
! * an 8.3 or 8.4 system might be upgraded to 9.0 and then 9.1 (and
! * still have a mismatch between toast table name and heap oid),
! * we can't use the old cluster version to know if all toast
! * table names match. Hence we don't check a match of toast table
! * names.
*/
if (strcmp(old_rel-nspname, new_rel-nspname) != 0 ||
! (strcmp(old_rel-nspname, pg_toast) != 0
strcmp(old_rel-relname, new_rel-relname) != 0))
pg_log(PG_FATAL, Mismatch of relation names in database \%s\:
old name \%s.%s\, new name \%s.%s\\n,
--
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_regress input/output directory option
Hi all, Why are there no options in_regress to specify the directory where input and output data are located? Such options would bring more flexibility when running regressions without make check/installcheck for an external application. Is there a particular reason for that? Or do you think that pg_regress should be only used with make check? Regards, -- Michael Paquier http://michael.otacoo.com
