Re: [HACKERS] could not adopt C locale failure at startup on Windows
Noah Misch n...@leadboat.com writes: On Wed, Jun 10, 2015 at 10:09:38AM -0400, Tom Lane wrote: Hm. I could understand getting encoding difficulties in that environment, but it's hard to see why they'd manifest like this. Can you trace through pg_perm_setlocale and figure out why it's reporting failure? A faster test is to set LC_CTYPE=C in the environment and run postgres --version. The root cause is a bug my commit 5f538ad introduced at the start of the 9.4 cycle. pg_perm_setlocale() now calls pg_bind_textdomain_codeset(), which calls setlocale(LC_CTYPE, NULL). POSIX permits that to clobber all previous setlocale() return values, which it did here[1]. Ah-hah. While Windows was the bellwether, harm potential is greater on non-Windows systems. pg_perm_setlocale() sets the LC_CTYPE environment variable to help PL/Perl avoid clobbering the process locale; see plperl_init_interp() comments. However, that function has bespoke code for Windows, on which setting the environment variable doesn't help. I don't know which other platforms invalidate previous setlocale() return values on setlocale(LC_CTYPE, NULL). Therefore, I propose committing the attached diagnostic patch and reverting it after about one buildfarm cycle. It will make affected configurations fail hard, and then I'll have a notion about the prevalence of damage to expect in the field. I doubt this will teach us anything; if any buildfarm systems were exhibiting the issue, they'd have been failing all along, no? This should break at least the bootstrap/initdb case on any affected system. The actual fix is trivial, attached second. This is for back-patch to 9.4. Looks sane 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
Re: [HACKERS] query execution time faster with geqo on than off: bug?
On Sun, Jun 14, 2015 at 6:43 PM, David Kamholz lautges...@gmail.com wrote: I've encountered a query with 11 joins whose execution time (i.e., the time not taken up by planning) is significantly faster with geqo on rather than off. This is surprising to me and seems like it might be a bug in the planner, so I am posting it here rather than to -performance. The query is below, along with EXPLAIN ANALYZE results with geqo on and off. The server version is 9.4.4. The various geqo options are all set to the default. join_collapse_limit is set to 12 (the query is much slower with it set to the default of 8). Let me know what other information might be helpful in debugging this further. Thanks! Well, for starters you're looking at an estimation miss. The exhaustive search found the 'cheaper' plan than what geqo came up with, but that did not correlate to execution time. This is a common and frustrating problem. Generally to try and avoid it it's good to avoid things in tables and queries that the database has difficulty planning or to crank statistics in specific cases. Anyways, In the non geqo plan, I see lines like this: - Nested Loop (cost=0.76..107.61 rows=27 width=20) (actual time=0.116..797.027 rows=1047967 loops=1) ...that suggest any good result is a matter of luck, more or less; a 5 order of magnitude miss into a nestloop is fodder for unpleasant results because that error is carried into the estimate itself. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] 9.4.1 - 9.4.2 problem: could not access status of transaction 1
Andres Freund wrote: A first version to address this problem can be found appended to this email. Basically it does: * Whenever more than MULTIXACT_MEMBER_SAFE_THRESHOLD are used, signal autovacuum once per members segment * For both members and offsets, once hitting the hard limits, signal autovacuum everytime. Otherwise we loose the information when restarting the database, or when autovac is killed. I ran into this a bunch of times while testing. Sounds reasonable. I see another hole in this area. See do_start_worker() -- there we only consider the offsets limit to determine a database to be in almost-wrapped-around state (causing emergency attention). If the database in members trouble has no pgstat entry, it might get completely ignored. I think the way to close this hole is to find_multixact_start() in the autovac launcher for the database with the oldest datminmxid, to determine whether we need to activate emergency mode for it. (Maybe instead of having this logic in autovacuum, it should be a new function that receives database datminmulti and returns a boolean indicating whether the database is in trouble or not.) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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
[HACKERS] fmgr.h
... has this comment (fmgr.h:506): /* These are for invocation of a function identified by OID with a * directly-computed parameter list. Note that neither arguments nor result * are allowed to be NULL. These are essentially FunctionLookup() followed * by FunctionCallN(). If the same function is to be invoked repeatedly, * do the FunctionLookup() once and then use FunctionCallN(). */ However, there doesn't appear to be any such animal as FunctionLookup(). Shouldn't it say fmgr_info() instead? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Tab completion for CREATE SEQUENCE
While reviewing the seqam patches, I noticed that psql has tab completion for ALTER SEQUENCE, but not for CREATE SEQUENCE. The attached trivial patch fixes that. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support *** a/src/bin/psql/tab-complete.c --- b/src/bin/psql/tab-complete.c *** *** 2445,2450 psql_completion(const char *text, int start, int end) --- 2445,2471 pg_strcasecmp(prev_wd, TO) == 0) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL); + /* CREATE SEQUENCE name */ + else if (pg_strcasecmp(prev3_wd, CREATE) == 0 + pg_strcasecmp(prev2_wd, SEQUENCE) == 0) + { + static const char *const list_CREATESEQUENCE[] = + {INCREMENT, MINVALUE, MAXVALUE, RESTART, NO, CACHE, CYCLE, + SET SCHEMA, OWNED BY, OWNER TO, RENAME TO, NULL}; + + COMPLETE_WITH_LIST(list_CREATESEQUENCE); + } + /* CREATE SEQUENCE name NO */ + else if (pg_strcasecmp(prev4_wd, CREATE) == 0 + pg_strcasecmp(prev3_wd, SEQUENCE) == 0 + pg_strcasecmp(prev_wd, NO) == 0) + { + static const char *const list_CREATESEQUENCE2[] = + {MINVALUE, MAXVALUE, CYCLE, NULL}; + + COMPLETE_WITH_LIST(list_CREATESEQUENCE2); + } + /* CREATE SERVER name */ else if (pg_strcasecmp(prev3_wd, CREATE) == 0 pg_strcasecmp(prev2_wd, SERVER) == 0) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] query execution time faster with geqo on than off: bug?
Well, for starters you're looking at an estimation miss. The exhaustive search found the 'cheaper' plan than what geqo came up with, but that did not correlate to execution time. This is a common and frustrating problem. Generally to try and avoid it it's good to avoid things in tables and queries that the database has difficulty planning or to crank statistics in specific cases. RhodiumToad on #postgresql thinks it may be a different issue -- namely, the fact that there are various estimates of rows=1 when the actual number is higher. Increasing default_statistics_target to 1000 and 1 seems to confirm this -- if anything, the query runs slower, and the plans look to be about the same. So I'm not convinced yet that it's not a bug. The better performance with geqo on is pretty consistent. I recognize that the query is complex, and I can try to simplify it, but it would be nice if its performance were not a matter of luck. I've attached a file containing the original query and the EXPLAIN ANALYZE results for geqo on and default_statistics_target 100, geqo off and default_statistics_target 100, geqo on and default_statistics_target 1, and geqo off and default_statistics_target 1, showing that the increased statistics target doesn't help. (I figured it would be easier to read as an attachment because my email client automatically wraps long lines.) Dave SELECT ex.ex, ex.lv, ex.tt, ex.td, dnsrc.ex AS trex, ((uiuq_score(array_agg(ap.ui), array_agg(ap.uq)) + uiuq_score(array_agg(apsrc.ui), array_agg(apsrc.uq))) / 2) AS trq FROM ex INNER JOIN lv ON (lv.lv = ex.lv) INNER JOIN dn ON (dn.ex = ex.ex) INNER JOIN mn ON (mn.mn = dn.mn) INNER JOIN ap ON (ap.ap = mn.ap) INNER JOIN dn AS dn2 ON (dn2.mn = dn.mn) INNER JOIN dn AS dn3 ON (dn3.ex = dn2.ex) INNER JOIN dn AS dnsrc ON (dnsrc.mn = dn3.mn) INNER JOIN mn AS mnsrc ON (mnsrc.mn = dnsrc.mn) INNER JOIN ap AS apsrc ON (apsrc.ap = mnsrc.ap) INNER JOIN ex AS exsrc ON (exsrc.ex = dnsrc.ex) INNER JOIN lv AS lvsrc ON (lvsrc.lv = exsrc.lv) WHERE dn.ex != dn2.ex AND dn3.ex != dnsrc.ex AND mn.ap != mnsrc.ap AND dn.ex != dnsrc.ex AND lcvc(lv.lc, lv.vc) IN ('zul-000') AND lcvc(lvsrc.lc, lvsrc.vc) IN ('gle-000') AND exsrc.tt IN ('doras') GROUP BY ex.ex, dnsrc.ex ORDER BY trq desc LIMIT 2000; EXPLAIN ANALYZE with geqo on and default_statistics_target = 100: QUERY PLAN - Limit (cost=3603.82..3603.82 rows=2 width=57) (actual time=674.046..674.050 rows=31 loops=1) - Sort (cost=3603.82..3603.82 rows=2 width=57) (actual time=674.044..674.046 rows=31 loops=1) Sort Key: (((uiuq_score(array_agg(ap.ui), array_agg(ap.uq)) + uiuq_score(array_agg(apsrc.ui), array_agg(apsrc.uq))) / 2)) Sort Method: quicksort Memory: 27kB - HashAggregate (cost=3603.60..3603.82 rows=2 width=57) (actual time=672.799..674.008 rows=31 loops=1) Group Key: ex.ex, dnsrc.ex - Nested Loop (cost=3075.23..3603.59 rows=2 width=57) (actual time=8.048..671.384 rows=766 loops=1) - Nested Loop (cost=3075.17..3603.43 rows=2 width=55) (actual time=8.036..669.963 rows=766 loops=1) Join Filter: (mn.ap mnsrc.ap) Rows Removed by Join Filter: 3793 - Hash Join (cost=3075.08..3603.21 rows=2 width=63) (actual time=8.012..660.229 rows=4559 loops=1) Hash Cond: (dn.ex = ex.ex) - Nested Loop (cost=3.04..510.58 rows=20564 width=26) (actual time=0.939..553.677 rows=1047985 loops=1) Join Filter: (dn.ex dnsrc.ex) Rows Removed by Join Filter: 5396 - Nested Loop (cost=2.92..277.03 rows=118 width=22) (actual time=0.934..120.245 rows=40543 loops=1) - Nested Loop (cost=2.87..267.76 rows=118 width=16) (actual time=0.921..62.140 rows=40543 loops=1) - Hash Join (cost=2.76..266.08 rows=1 width=12) (actual time=0.899..6.318 rows=1254 loops=1) Hash Cond: (exsrc.lv = lvsrc.lv) - Nested Loop (cost=0.43..263.08 rows=871 width=16) (actual time=0.173..5.788 rows=1516 loops=1) - Nested Loop (cost=0.34..164.79 rows=871 width=20) (actual time=0.142..1.239 rows=1516 loops=1)
Re: [HACKERS] [Proposal] More Vacuum Statistics
Hi, Thank you for comments. and Sorry for my late response. pg_stat_vacuum view I understand it is not good to simply add more counters in pg_stat_*_tables. For now, I'd like to suggest an extension which can confirm vacuum statistics like pg_stat_statements. Similar feature has been already provided by pg_statsinfo package. But it is a full-stack package for PG-stats and it needs to redesign pg_log and design a repository database for introduce. And it is not a core-extension for PostgreSQL. (I don't intend to hate pg_statsinfo, I think this package is a very convinient tool) Everyone will be able to do more easily tuning of VACUUM. That's all I want. I'm still wondering whether these stats will really make the tuning any easier. What I do right now is looking at pg_stat_all_tables.n_deat_tup and if it exceeds some threshold, it's a sign that vacuum may need a bit of tuning. Sometimes it really requires tuning vacuum itself, but more often than not it's due to something else (a large bulk delete, autovacuum getting stuck on another table, ...). I don't see how the new stats would make this any easier. Can you give some examples on how the new stats might be used (and where the current stats are insufficient)? What use cases do you imagine for those stats? pg_stat_vacuum can keep histories of vacuum statistics for each tables/indices into shared memory.(They are not only last vacuum. This is already able to confirm using pg_stat_all_tables.) It makes easier analysis of vacuum histories because this view can sort or aggregate or filter. My use cases for those stats are following. - examine TRANSITION of vacuum execution time on any table (you can predict the future vacuum execution time) - examine EXECUTION INTERVAL of vacuum for each table (if too frequent, it should make vacuum-threshold tuning to up) - examine REST of dead-tuples just after vacuum (if dead-tuples remain, it may be due to any idle in transaction sessions) It might help differentiate the autovacuum activity from the rest of the system (e.g. there's a lot of I/O going on - how much of that is coming from autovacuum workers?). This would however require a more fine-grained reporting, because often the vacuums run for a very long time, especially on very large tables (which is exactly the case when this might be handy) - I just had a VACUUM that ran for 12 hours. These jobs should report the stats incrementally, not just once at the very end, because that makes it rather useless IMNSHO. +1 Certainly, VACUUM have often much execution time, I just had too. At present, we cannot predict when this vacuum finishes, what this vacuum is doing now, and whether this vacuum have any problem or not. Maybe, For DBAs, It might be better to show vacuum progress in pg_stat_activity. (if we'd do, add a free-style column like progress ?) This column might also be able to use for other long time commands like ANALYZE, CREATE/RE INDEX and COPY. To realize this feature, we certainly need to properly change pgstat_report_activity, use it more and add a new track-activity parameter. Regards, Anzai Naoya --- Naoya Anzai Engineering Department NEC Solution Inovetors, Ltd. E-Mail: nao-an...@xc.jp.nec.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] does tuple store subtransaction id in it?
In XidInMVCCSnapshot, it will check xid from tuple if is in snapshot-subxip. It means tuple store subtransaction? But in PushTransaction, I see TransactionState.subTransaction will assign currentSubTransactionId, currentSubTransactionId will reinitialize in StartTransaction. So I think tuple should not store subtransaction id. I am confuse about this. If subtransaction id will reinitialize every start time. How to judge it is a subtransaction from xid in tuple? -- 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] Auto-vacuum is not running in 9.1.12
Hi, @Avaro Herrera, Thanks for quick reply. I was on leave and hence not able to reply soon. This issue was observed on customer site. However after long discussion and digging into what happened around the date 2nd May 2015, we got to know that NTP server suddenly went back in time to 1995. It remained there for some time until it is noticed and corrected. So after correcting NTP server time the whole cluster is synced to current date. After this change in time the auto-vacuum stopped. Since auto-vacuuming is triggered periodically, I doubt if this time change has affected any timer! So I suspect the time change is the root cause! It would be great if someone can clarify if this is the root cause for auto-vacuum stopped. On Wed, Jun 10, 2015 at 8:19 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Prakash Itnal wrote: Hello, Recently we encountered a issue where the disc space is continuously increasing towards 100%. Then a manual vacuum freed the disc space. But again it is increasing. When digged more it is found that auto-vacuuming was not running or it is either stucked/hanged. Hm, we have seen this on Windows, I think. Is the stats collector process running? Is it stuck? If you attach to process 6504 (autovac launcher), what's the backtrace? 4) Last run auto-vacuum: SELECT now(), schemaname, relname, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count FROM pg_stat_user_tables; now | schemaname |relname| last_vacuum |last_autovacuum| vacuum_count | autovacuum_count ---++---+-+---+--+-- 2015-06-10 01:03:03.574212+02 | public | abcd | | 2015-04-18 00:52:35.008874+02 |0 |2 2015-06-10 01:03:03.574212+02 | public | xyz | | 2015-05-02 06:01:35.220651+02 |0 | 20 NOTE: I changed the relname for above two tables due to confidentiality. Are there dead tuples in tables? Maybe vacuums are getting executed and these values are not updated, for instance? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Cheers, Prakash
Re: [HACKERS] Fix pgbench --progress report under (very) low rate
v3 rebase (after pgbench moved to src/bin) and minor style tweaking. v4 adds a fix to another progress timing issue: Currently if pgbench/postgres get stuck somewhere, the report catches up by repeating progresses several time in a row, which looks like that: progress: 10.0 s ... progress: 11.0 s ... stuck... progress: 14.2 s catchup for 11.0 - 14.2 progress: 14.2 s stupid data progress: 14.2 s stupid data progress: 15.0 s ... progress: 16.0 s ... The correction removes the stupid data lines which compute a reports on a very short time, including absurd tps figures. Yet again, shame on me in the first place for this behavior. -- Fabien.diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c index 6f35db4..0d71173 100644 --- a/src/bin/pgbench/pgbench.c +++ b/src/bin/pgbench/pgbench.c @@ -3639,6 +3639,28 @@ threadRun(void *arg) maxsock = sock; } + /* also meet the next progress report time if needed */ + if (progress min_usec 0 +#if !defined(PTHREAD_FORK_EMULATION) + thread-tid == 0 +#endif /* !PTHREAD_FORK_EMULATION */ + ) + { + /* get current time if needed */ + if (now_usec == 0) + { +instr_time now; + +INSTR_TIME_SET_CURRENT(now); +now_usec = INSTR_TIME_GET_MICROSEC(now); + } + + if (now_usec = next_report) +min_usec = 0; + else if ((next_report - now_usec) min_usec) +min_usec = next_report - now_usec; + } + if (min_usec 0 maxsock != -1) { int nsocks; /* return from select(2) */ @@ -3744,7 +3766,13 @@ threadRun(void *arg) last_lags = lags; last_report = now; last_skipped = thread-throttle_latency_skipped; -next_report += (int64) progress *100; + +/* Ensure that the next report is in the future, in case + * pgbench/postgres got stuck somewhere... + */ +do { + next_report += (int64) progress * 100; +} while (now = next_report); } } #else @@ -3808,7 +3836,13 @@ threadRun(void *arg) last_lags = lags; last_report = now; last_skipped = thread-throttle_latency_skipped; -next_report += (int64) progress *100; + +/* Ensure that the next report is in the future, in case + * pgbench/postgres got stuck somewhere... + */ +do { + next_report += (int64) progress * 100; +} while (now = next_report); } } #endif /* PTHREAD_FORK_EMULATION */ -- 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] Function to get size of notification queue?
Thanks for the timely response! On Mon, Jun 15, 2015 at 2:07 PM Brendan Jurd [via PostgreSQL] ml-node+s1045698n5853928...@n5.nabble.com wrote: Hi Kevin, I never found a direct solution to this problem. I still feel that a function to find the size of the notification queue would be a handy feature to have, and I would be willing to take a shot at writing such a feature. However, given the tumbleweed/ response to my original email, it's likely that effort would be a waste of time. Cheers, BJ On Tue, 16 Jun 2015 at 03:40 kjsteuer [hidden email] http:///user/SendEmail.jtp?type=nodenode=5853928i=0 wrote: Hi BJ, What approach did you end up using? Thanks, Kevin -- View this message in context: http://postgresql.nabble.com/Function-to-get-size-of-notification-queue-tp5738461p5853923.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list ([hidden email] http:///user/SendEmail.jtp?type=nodenode=5853928i=1) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers If you reply to this email, your message will be added to the discussion below: http://postgresql.nabble.com/Function-to-get-size-of-notification-queue-tp5738461p5853928.html To unsubscribe from Function to get size of notification queue?, click here http://postgresql.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5738461code=a2pzdGV1ZXJAZ21haWwuY29tfDU3Mzg0NjF8MTAxMjU3MTk4 . NAML http://postgresql.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml -- View this message in context: http://postgresql.nabble.com/Function-to-get-size-of-notification-queue-tp5738461p5853930.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Re: [HACKERS] FIX : teach expression walker about RestrictInfo
On 04/29/15 18:33, Tomas Vondra wrote: OK, I do understand that. So what about pull_varnos_walker and pull_varattnos_walker - what about teaching them about RestrictInfos? Attached is a patch fixing the issue by handling RestrictInfo in pull_varnos_walker and pull_varattnos_walker. -- Tomas Vondra http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services From 2dc79b914c759d31becd8ae670b37b79663a595f Mon Sep 17 00:00:00 2001 From: Tomas Vondra to...@pgaddict.com Date: Tue, 28 Apr 2015 19:56:33 +0200 Subject: [PATCH 1/6] teach pull_(varno|varattno)_walker about RestrictInfo otherwise pull_varnos fails when processing OR clauses --- src/backend/optimizer/util/var.c | 16 1 file changed, 16 insertions(+) diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c index 773e7b2..221b031 100644 --- a/src/backend/optimizer/util/var.c +++ b/src/backend/optimizer/util/var.c @@ -197,6 +197,13 @@ pull_varnos_walker(Node *node, pull_varnos_context *context) context-sublevels_up--; return result; } + if (IsA(node, RestrictInfo)) + { + RestrictInfo *rinfo = (RestrictInfo*)node; + context-varnos = bms_add_members(context-varnos, + rinfo-clause_relids); + return false; + } return expression_tree_walker(node, pull_varnos_walker, (void *) context); } @@ -245,6 +252,15 @@ pull_varattnos_walker(Node *node, pull_varattnos_context *context) return false; } + if (IsA(node, RestrictInfo)) + { + RestrictInfo *rinfo = (RestrictInfo *)node; + + return expression_tree_walker((Node*)rinfo-clause, + pull_varattnos_walker, + (void*) context); + } + /* Should not find an unplanned subquery */ Assert(!IsA(node, Query)); -- 1.9.3 -- 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: [COMMITTERS] pgsql: Map basebackup tablespaces using a tablespace_map file
On Thu, Jun 11, 2015 at 9:55 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Wed, Jun 10, 2015 at 12:09 PM, Fujii Masao masao.fu...@gmail.com wrote: On Tue, Jun 9, 2015 at 3:29 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Tue, Jun 9, 2015 at 10:56 AM, Fujii Masao masao.fu...@gmail.com wrote: Or what about removing tablespace_map file at the beginning of recovery whenever backup_label doesn't exist? Yes, thats another way, but is it safe to assume that user won't need that file, Is there really case where tablespace_map is necessary even though backup_label doesn't exist? If not, it seems safe to get rid of the file when backup_label is not present. I mean in the valid scenario (where both backup_label and tablespace_map are present and usable) also, we rename them to *.old rather than deleting it. Yep, I'm OK to make the recovery rename the file to *.old rather than delete it. This sounds safe to me, unless anybody else has different opinion I will write a patch to fix this way. Attached patch provides a fix as per above discussion. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com rename_mapfile_if_backupfile_not_present_v1.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] Function to get size of notification queue?
Hi Kevin, I never found a direct solution to this problem. I still feel that a function to find the size of the notification queue would be a handy feature to have, and I would be willing to take a shot at writing such a feature. However, given the tumbleweed/ response to my original email, it's likely that effort would be a waste of time. Cheers, BJ On Tue, 16 Jun 2015 at 03:40 kjsteuer kjste...@gmail.com wrote: Hi BJ, What approach did you end up using? Thanks, Kevin -- View this message in context: http://postgresql.nabble.com/Function-to-get-size-of-notification-queue-tp5738461p5853923.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] Function to get size of notification queue?
Brendan Jurd wrote: Hi Kevin, I never found a direct solution to this problem. I still feel that a function to find the size of the notification queue would be a handy feature to have, and I would be willing to take a shot at writing such a feature. However, given the tumbleweed/ response to my original email, it's likely that effort would be a waste of time. I think tumbleweed responses are more in line with hmm, this guy might well be right, but I don't know right now. next email. When people come up with really useless proposals, they tend to figure out pretty quickly. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] could not adopt C locale failure at startup on Windows
Noah Misch n...@leadboat.com writes: A faster test is to set LC_CTYPE=C in the environment and run postgres --version. The root cause is a bug my commit 5f538ad introduced at the start of the 9.4 cycle. pg_perm_setlocale() now calls pg_bind_textdomain_codeset(), which calls setlocale(LC_CTYPE, NULL). POSIX permits that to clobber all previous setlocale() return values, which it did here[1]. After further thought, ISTM that this bug is evidence that 5f538ad was badly designed, and the proposed fix has blinkers on. If pg_bind_textdomain_codeset() is looking at the locale environment, we should not be calling it from inside pg_perm_setlocale() at all, but from higher level code *after* we're done setting up the whole libc locale environment --- thus, after the unsetenv(LC_ALL) call in main.c, and somewhere near the bottom of CheckMyDatabase() in postinit.c. It's mere chance that the order of calls to pg_perm_setlocale() is such that the code works now; and it's not hard to imagine future changes in gettext, or reordering of our own code, that would break it. So I think having to duplicate that call is a reasonable price to pay for not having surprising entanglements in what should be a very thin wrapper around setlocale(3). 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] fmgr.h
Andrew Dunstan and...@dunslane.net writes: However, there doesn't appear to be any such animal as FunctionLookup(). Shouldn't it say fmgr_info() instead? Hmmm ... I think there may have been at the time the comment was written, but yeah, please change that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function to get size of notification queue?
Hi BJ, What approach did you end up using? Thanks, Kevin -- View this message in context: http://postgresql.nabble.com/Function-to-get-size-of-notification-queue-tp5738461p5853923.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] Function to get size of notification queue?
On Tue, 16 Jun 2015 at 05:36 Merlin Moncure mmonc...@gmail.com wrote: On Mon, Jun 15, 2015 at 2:12 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Brendan Jurd wrote: However, given the tumbleweed/ response to my original email, it's likely that effort would be a waste of time. I think tumbleweed responses are more in line with hmm, this guy might well be right, but I don't know right now. next email. When people come up with really useless proposals, they tend to figure out pretty quickly. +1 It took me a lot longer than it should have to figure this out, but lack of comment does not in any way indicate a response is bad. Most commonly it means, interesting idea, why don't you code it up and see what happens?. Suggestions, even very good ones (except when related to bona fide bugs) are remarkably unlikely to elicit, good idea, let's do that!. Álvaro, Merlin, Thanks for your comments. I understand what you're saying, and I do agree for the most part. However I've also seen the downside of this, where nobody comments much on the original proposal, and only after sinking substantial effort into creating a patch do others appear to forcefully oppose the idea that led to the patch. I do understand why it happens this way, but that doesn't make it any less of a deterrent. If you see a proposal on the list and you think interesting idea, why don't you code it up and see what happens, I would humbly and respectfully encourage you to type exactly those words in to your email client and let the author of the proposal know. None of us are telepaths, silence is ambiguous, and sometimes even a very small encouragement is all that is needed to provoke action. Back to the $subject at hand -- I have had a quick look into async.c and can see that the logic to test for queue size in asyncQueueFillWarning() could easily be factored out and exposed via an SQL function. My original idea was to have the function return the number of notifications in the queue, but in fact given the way notifications are stored, it would be much easier to return a float showing the fraction of the maximum queue size that is currently occupied. This would actually be more useful for the use-case I described, where I am wanting to monitor for rogue processes filling up the queue. I will take Merlin's advice, code something up and see what happens. Cheers, BJ
Re: [HACKERS] [GENERAL] 9.4.1 - 9.4.2 problem: could not access status of transaction 1
On Fri, Jun 12, 2015 at 7:27 PM, Steve Kehlet steve.keh...@gmail.com wrote: Just wanted to report that I rolled back my VM to where it was with 9.4.2 installed and it wouldn't start. I installed 9.4.4 and now it starts up just fine: 2015-06-12 16:05:58 PDT [6453]: [1-1] LOG: database system was shut down at 2015-05-27 13:12:55 PDT 2015-06-12 16:05:58 PDT [6453]: [2-1] LOG: MultiXact member wraparound protections are disabled because oldest checkpointed MultiXact 1 does not exist on disk 2015-06-12 16:05:58 PDT [6457]: [1-1] LOG: autovacuum launcher started 2015-06-12 16:05:58 PDT [6452]: [1-1] LOG: database system is ready to accept connections done server started And this is showing up in my serverlog periodically as the emergency autovacuums are running: 2015-06-12 16:13:44 PDT [6454]: [1-1] LOG: MultiXact member wraparound protections are disabled because oldest checkpointed MultiXact 1 does not exist on disk **Thank you Robert and all involved for the resolution to this.** With the fixes introduced in this release, such a situation will result in immediate emergency autovacuuming until a correct oldestMultiXid value can be determined Okay, I notice these vacuums are of the to prevent wraparound type (like VACUUM FREEZE), that do hold locks preventing ALTER TABLEs and such. Good to know, we'll plan our software updates accordingly. Is there any risk until these autovacuums finish? As long as you see only a modest number of files in pg_multixact/members, you're OK. But in theory, until that emergency autovacuuming finishes, there's nothing keeping that directory from wrapping around. -- 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] Function to get size of notification queue?
On Mon, Jun 15, 2015 at 2:12 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Brendan Jurd wrote: Hi Kevin, I never found a direct solution to this problem. I still feel that a function to find the size of the notification queue would be a handy feature to have, and I would be willing to take a shot at writing such a feature. However, given the tumbleweed/ response to my original email, it's likely that effort would be a waste of time. I think tumbleweed responses are more in line with hmm, this guy might well be right, but I don't know right now. next email. When people come up with really useless proposals, they tend to figure out pretty quickly. +1 It took me a lot longer than it should have to figure this out, but lack of comment does not in any way indicate a response is bad. Most commonly it means, interesting idea, why don't you code it up and see what happens?. Suggestions, even very good ones (except when related to bona fide bugs) are remarkably unlikely to elicit, good idea, let's do that!. A lot of this has to do with years of micro-optimization in terms of handling email and some gentle subtle nudges to do more of the homework yourself. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] last_analyze/last_vacuum not being updated
On 6/8/15 3:16 PM, Peter Eisentraut wrote: I'm looking at a case on 9.4.1 where the last_analyze and last_vacuum stats for a handful of tables seem stuck. They don't update after running an ANALYZE or VACUUM command, and they don't react to pg_stat_reset_single_table_counters(). All of the affected tables are system catalogs, some shared, some not. Other system catalogs and other tables have their statistics updated normally. Any ideas (before I try to blow it away)? This issue somehow went away before I had time to analyze it further, which is weird in itself. But now I have seen a segfault on a completely different 9.4 instance while querying pg_stat_databases. Could be bad luck. But if others are seeing weird stats collector behavior in 9.4, please share. -- 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] Function to get size of notification queue?
On Mon, Jun 15, 2015 at 3:16 PM, Brendan Jurd dire...@gmail.com wrote: On Tue, 16 Jun 2015 at 05:36 Merlin Moncure mmonc...@gmail.com wrote: On Mon, Jun 15, 2015 at 2:12 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Brendan Jurd wrote: However, given the tumbleweed/ response to my original email, it's likely that effort would be a waste of time. I think tumbleweed responses are more in line with hmm, this guy might well be right, but I don't know right now. next email. When people come up with really useless proposals, they tend to figure out pretty quickly. +1 It took me a lot longer than it should have to figure this out, but lack of comment does not in any way indicate a response is bad. Most commonly it means, interesting idea, why don't you code it up and see what happens?. Suggestions, even very good ones (except when related to bona fide bugs) are remarkably unlikely to elicit, good idea, let's do that!. Álvaro, Merlin, Thanks for your comments. I understand what you're saying, and I do agree for the most part. However I've also seen the downside of this, where nobody comments much on the original proposal, and only after sinking substantial effort into creating a patch do others appear to forcefully oppose the idea that led to the patch. I do understand why it happens this way, but that doesn't make it any less of a deterrent. If you see a proposal on the list and you think interesting idea, why don't you code it up and see what happens, I would humbly and respectfully encourage you to type exactly those words in to your email client and let the author of the proposal know. None of us are telepaths, silence is ambiguous, and sometimes even a very small encouragement is all that is needed to provoke action. It goes back to the adage, 'Everyone wants to be an author but nobody wants to write'. -hackers are busy with release schedules, multi-xact bugs, bidirectional replication and who knows what else. It's definitely upon you to do the homework getting patch together, and you absolutely must be prepared to do that understanding the tough road most patches have in order to get accepted. The archives clearly note your suggestion; even if the work gets shelved it can be referred to by future coders or used as evidence by others to advance work. For posterity, I think your idea is pretty good, especially if the current slru based implementation supports it without a lot of extra work. Adding a new built-in function is not free though so I think to move forwards with this you'd also have to show some more justification. Perhaps a real world example demonstrating the problem reduced down to an executable case. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] On columnar storage
http://vldb.org/pvldb/vol5/p1790_andrewlamb_vldb2012.pdf In sketch: There is the concept of a Write-Optimized-Store (WOS) and Read-optimized-store (ROS), and a TupleMover that moves records from WOS to ROS (some what like vacuum), and from ROS to WOS for updates. It seems to me that heap is naturally a WOS, and only vacuuming for a column-backed heap table would move records from the heap into the column store. Of course, there would need to be a deeper vacuum when the column store itself needs to be vacuumed. When a record in column store needs to be updated, a top-level transaction moves the record into the heap by marking the row as deleted in the column store and inserting the record into the heap store. The updates could then proceed according to the current heap transactional logic. I am not sure if this makes sense, but it seems plausible and 1/ retains the heap transactional logic code which is very hard to get right 2/ makes column store essentially a storage optimization that users do not need to be too concerned with; heap is kept small and old data are moved into column store automatically 3/ no need to keep 20+bytes of visibility info on the rows in column store 4/ instead of column store, this could be a heap (without visibility) store if you prefer row I haven't thought about the indexing aspect of this. From a DW angle, I am more interested in a heap store that is backed by multiple column stores via partition keys. Regards, -cktan On Mon, Jun 15, 2015 at 12:02 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Fri, Jun 12, 2015 at 10:58 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Amit Kapila wrote: On Fri, Jun 12, 2015 at 4:33 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: One critical detail is what will be used to identify a heap row when talking to a CS implementation. There are two main possibilities: 1. use CTIDs 2. use some logical tuple identifier Using CTIDs is simpler. One disadvantage is that every UPDATE of a row needs to let the CS know about the new location of the tuple, so that the value is known associated with the new tuple location as well as the old. This needs to happen even if the value of the column itself is not changed. Isn't this somewhat similar to index segment? Not sure what you mean with index segment. The part similar to index segment is reference to heap for visibility information and tuple id (TID). Have I misunderstood something? Will the column store obey snapshot model similar to current heap tuples, if so will it derive the transaction information from heap tuple? Yes, visibility will be tied to the heap tuple -- a value is accessed only when its corresponding heap row has already been determined to be visible. Won't it possible that all columns of a table belong to column-store? I think for such a case heap will just be used to store transaction information (visibility info) for a column store tuple and depending on how the column-store is organized, the reference to this information needs to be stored in column-store (the same row reference might need to be stored for each column value). Also any write operation could lead to much more I/O because of updation at 2 different locations (one in column-store and other in heap). One interesting point that raises from this is about vacuum: when are we able to remove a value from the store? Yes, that could also be quite tricky to handle, may be one naive way could be to make list of all TID's from heap that needs to be expired and then search for references of all those TID's in column-store. I understand your point for re-using the existing transaction infrastructure for column-store by keeping that information in heap as it is done now, but I think that won't be free either. Another point to consider here is does the column-store needs transactional consistency, do other commercial/opensource column-store implementation's are transactional consistent and if yes, then can't we think of doing it in a way where data could be present both in heap as well as in column-store (I understand that it could lead to duplicate data, OTOH, such an implementation anyway eliminates the need for indexes, so may be worth considering). With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] could not adopt C locale failure at startup on Windows
On Wed, Jun 10, 2015 at 10:09:38AM -0400, Tom Lane wrote: Noah Misch n...@leadboat.com writes: I can reproduce this with initdb --locale=C, postgresql-9.4.3-1-windows-binaries.zip (32-bit), Windows 7 x64, and the Windows ANSI code page set to CP936. (Choose Chinese (Simplified, PRC) in Control Panel - Region and Language - Administrative - Language for non-Unicode programs.) It is neither necessary nor sufficient to change Control Panel - Region and Language - Formats - Format. Binaries from postgresql-9.4.3-1-windows-x64-binaries.zip do not exhibit the problem. Note that CP936 is a PG_ENCODING_IS_CLIENT_ONLY() encoding. Hm. I could understand getting encoding difficulties in that environment, but it's hard to see why they'd manifest like this. Can you trace through pg_perm_setlocale and figure out why it's reporting failure? A faster test is to set LC_CTYPE=C in the environment and run postgres --version. The root cause is a bug my commit 5f538ad introduced at the start of the 9.4 cycle. pg_perm_setlocale() now calls pg_bind_textdomain_codeset(), which calls setlocale(LC_CTYPE, NULL). POSIX permits that to clobber all previous setlocale() return values, which it did here[1]. The ensuing putenv(LC_CTYPE=garbage bytes) at the end of pg_perm_setlocale() fails under Windows ANSI code page 936, because the garbage bytes often aren't a valid CP936 string. I would expect the same symptom on other multibyte Windows locales. While Windows was the bellwether, harm potential is greater on non-Windows systems. pg_perm_setlocale() sets the LC_CTYPE environment variable to help PL/Perl avoid clobbering the process locale; see plperl_init_interp() comments. However, that function has bespoke code for Windows, on which setting the environment variable doesn't help. I don't know which other platforms invalidate previous setlocale() return values on setlocale(LC_CTYPE, NULL). Therefore, I propose committing the attached diagnostic patch and reverting it after about one buildfarm cycle. It will make affected configurations fail hard, and then I'll have a notion about the prevalence of damage to expect in the field. The actual fix is trivial, attached second. This is for back-patch to 9.4. [1] It does so in 32-bit release (non-debug), NLS builds done under Visual Studio 2012 and Visual Studio 2013. The official binaries used VS2013. The symptoms are slightly different under VS2012. I did not test earlier versions. Debug builds and 64-bit builds were unaffected. diff --git a/src/backend/utils/adt/pg_locale.c b/src/backend/utils/adt/pg_locale.c index 4be735e..d33081b 100644 --- a/src/backend/utils/adt/pg_locale.c +++ b/src/backend/utils/adt/pg_locale.c @@ -58,6 +58,7 @@ #include catalog/pg_collation.h #include catalog/pg_control.h #include mb/pg_wchar.h +#include utils/builtins.h #include utils/hsearch.h #include utils/memutils.h #include utils/pg_locale.h @@ -148,6 +149,7 @@ pg_perm_setlocale(int category, const char *locale) char *result; const char *envvar; char *envbuf; + charorig_result[LC_ENV_BUFSIZE]; #ifndef WIN32 result = setlocale(category, locale); @@ -173,6 +175,7 @@ pg_perm_setlocale(int category, const char *locale) if (result == NULL) return result; /* fall out immediately on failure */ + strlcpy(orig_result, result, sizeof(orig_result)); /* * Use the right encoding in translated messages. Under ENABLE_NLS, let @@ -231,6 +234,15 @@ pg_perm_setlocale(int category, const char *locale) } snprintf(envbuf, LC_ENV_BUFSIZE - 1, %s=%s, envvar, result); + if (strcmp(orig_result, result) != 0) + { + charhex[2 * LC_ENV_BUFSIZE + 1]; + + hex_encode(result, Min(1 + strlen(result), LC_ENV_BUFSIZE), hex); + hex[sizeof(hex) - 1] = '\0'; + elog(FATAL, setlocale() result %s clobbered to 0x%s, +orig_result, hex); + } if (putenv(envbuf)) return NULL; diff --git a/src/backend/utils/adt/pg_locale.c b/src/backend/utils/adt/pg_locale.c index 4be735e..84215e0 100644 --- a/src/backend/utils/adt/pg_locale.c +++ b/src/backend/utils/adt/pg_locale.c @@ -183,6 +183,12 @@ pg_perm_setlocale(int category, const char *locale) */ if (category == LC_CTYPE) { + static char save_lc_ctype[LC_ENV_BUFSIZE]; + + /* copy setlocale() return value before callee invokes it again */ + strlcpy(save_lc_ctype, result, sizeof(save_lc_ctype)); + result = save_lc_ctype; + #ifdef ENABLE_NLS SetMessageEncoding(pg_bind_textdomain_codeset(textdomain(NULL))); #else -- 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] Function to get size of notification queue?
On Tue, 16 Jun 2015 at 07:52 Merlin Moncure mmonc...@gmail.com wrote: It goes back to the adage, 'Everyone wants to be an author but nobody wants to write'. A more accurate version would be Everyone wants to be an author, some want to write, but nobody likes being rejected by publishers. For posterity, I think your idea is pretty good, especially if the current slru based implementation supports it without a lot of extra work. Thank you for saying so, and yes, adding the function is pretty much trivial. I already have a patch that works, and will submit it once I've added docs and tests. Adding a new built-in function is not free though so I think to move forwards with this you'd also have to show some more justification. Perhaps a real world example demonstrating the problem reduced down to an executable case. Well the docs already describe this situation. The notification queue is finite, listening clients with long-running transactions could cause it to blow out, and if it does blow out, Bad Things will ensue. At the moment, there is no good way to find out whether this is happening. From SQL Commands / NOTIFY / Notes: There is a queue that holds notifications that have been sent but not yet processed by all listening sessions. If this queue becomes full, transactions calling NOTIFY will fail at commit. The queue is quite large (8GB in a standard installation) and should be sufficiently sized for almost every use case. However, no cleanup can take place if a session executes LISTEN and then enters a transaction for a very long time. Once the queue is half full you will see warnings in the log file pointing you to the session that is preventing cleanup. In this case you should make sure that this session ends its current transaction so that cleanup can proceed. So, it's straightorward to simulate the problem scenario. Make two client connections A and B to the same server. Client A executes LISTEN a;, then BEGIN;. Client B submits some notifications on channel a, e.g., SELECT pg_notify('a', 'Test queue saturation ' || s::text) FROM generate_series(1, 1) s;. The queue will start filling up, and will never reduce unless and until client A ends its transaction. If client B keeps on submitting notifications, the queue will eventually fill completely and then client B's session will ERROR out. Cheers, BJ
Re: [HACKERS] Collection of memory leaks for ECPG driver
On Mon, Jun 15, 2015 at 9:33 PM, Michael Meskes mes...@postgresql.org wrote: On Sun, Jun 14, 2015 at 08:43:13PM +0900, Michael Paquier wrote: point out that there is still a leak in connect.c. Per se the attached patch, that does not check for a NULL pointer before ecpg_free because other code paths in the routine patched don't do so. So you get something locally consistent ;) Thanks, committed. Thanks. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers