[HACKERS] [tiny doc fix] statistics are not retained across immediate shutdown
Hi, In the following page, statistics are kept across server restarts: http://www.postgresql.org/docs/current/static/monitoring-stats.html "When the server shuts down, a permanent copy of the statistics data is stored in the global subdirectory, so that statistics can be retained across server restarts." However, statistics are not retained after immediate shutdown (pg_ctl stop -mi). You may say "pg_ctl stop -mi is not a shutdown but an abort, so the sentence is not wrong", but it's an "immediate shutdown" and one mode of shutdown. I propose a tiny fix to clarify this. Please find the attached patch. I'd like this to be backported at least 9.2. Thanks. Regards, Takayuki Tsunakawa stats_reset_in_recovery.patch Description: stats_reset_in_recovery.patch -- 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] How can we expand PostgreSQL ecosystem?
> From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Mark Kirkwood > For cloud - in particular Openstack (which I am working with ATM), the > biggest thing would be: > > - multi-master replication > > or failing that: > > - self managing single master failover (voting/quorum etc) > > so that operators can essentially 'set and forget'. We currently use > Mysql+ Galera (multi master) and Mongodb (self managing single master) > and the convenience and simplicity is just so important (Openstack is a > huge complex collection of services - hand holding of any one service is > pretty much a non starter). Yes, I was also asked whether PostgreSQL has any optional functionality like Galera Cluster for MySQL. He was planning a scalable PaaS service which performs heavy reads and writes. Demand exists. Regards Takayuki Tsunakawa -- 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] How can we expand PostgreSQL ecosystem?
Hello, Josh, > From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Josh berkus> > Crossing this over to pgsql-advocacy list where it really belongs. > That's what that list is *for*. > > Especially since the discussion on -hackers has focused on new PostgreSQL > Features, which while also good don't address the general question. > Thank you for pointing me to the correct place. I wondered which list is better, because I thought this topic whould be better discussed among hackers. I'll post subsequent mails only to pgsql-advocacy. Regards Takayuki Tsunakawa -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [bug fix] Cascading standby cannot catch up and get stuck emitting the same message repeatedly
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tsunakawa, > Our customer hit a problem of cascading replication, and we found the cause. > They are using the latest PostgreSQL 9.2.18. The bug seems to have been > fixed in 9.4 and higher during the big modification of xlog.c, but it's > not reflected in older releases. > > The attached patch is for 9.2.18. This just borrows the idea from 9.4 and > higher. > > But we haven't been able to reproduce the problem. Could you review the > patch and help to test it? I would very much appreciate it if you could > figure out how to reproduce the problem easily. We could successfully reproduce the problem and confirm that the patch fixes it. Please use the attached script to reproduce the problem. Place it in an empty directory and just run "./test.sh" with no argument. It creates three database clusters (primary, standby, and cascading standby) in the current directory. Could you review the patch and commit it for the next release? If you think I should register the patch with the CommitFest even if the problem occurs in 9.2 and 9.3, please say so. I'll do so if there's no comment. Regards Takayuki Tsunakawa test.sh Description: test.sh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Supporting SJIS as a database encoding
Hello, I'd like to propose adding SJIS as a database encoding. You may wonder why SJIS is still necessary in the world of Unicode. The purpose is to achieve comparable performance when migrating legacy database systems from other DBMSs without little modification of applications. Recently, we failed to migrate some customer's legacy database from DBMS-X to PostgreSQL. That customer wished for PostgreSQL, but PostgreSQL couldn't meet the performance requirement. The system uses DBMS-X with the database character set being SJIS. The main applications are written in embedded SQL, which require SJIS in their host variables. They insisted they cannot use UTF8 for the host variables because that would require large modification of applications due to character handling. So no character set conversion is necessary between the clients and the server. On the other hand, PostgreSQL doesn't support SJIS as a database encoding. Therefore, character set conversion from UTF-8 to SJIS has to be performed. The batch application runs millions of SELECTS each of which retrieves more than 100 columns. And many of those columns are of character type. If PostgreSQL supports SJIS, PostgreSQL will match or outperform the performance of DBMS-X with regard to the applications. We confirmed it by using psql to run a subset of the batch processing. When the client encoding is SJIS, one FETCH of 10,000 rows took about 500ms. When the client encoding is UTF8 (the same as the database encoding), the same FETCH took 270ms. Supporting SJIS may somewhat regain attention to PostgreSQL here in Japan, in the context of database migration. BTW, MySQL supports SJIS as a database encoding. PostgreSQL used to be the most popular open source database in Japan, but MySQL is now more popular. But what I'm wondering is why PostgreSQL doesn't support SJIS. Was there any technical difficulty? Is there anything you are worried about if adding SJIS? I'd like to write a patch for adding SJIS if there's no strong objection. I'd appreciate it if you could let me know good design information to add a server encoding (e.g. the URL of the most recent patch to add a new server encoding) Regards Takayuki Tsunakawa -- 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] Supporting SJIS as a database encoding
> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii
> > But what I'm wondering is why PostgreSQL doesn't support SJIS. Was there
> any technical difficulty? Is there anything you are worried about if adding
> SJIS?
>
> Yes, there's a technical difficulty with backend code. In many places it
> is assumed that any string is "ASCII compatible", which means no ASCII
> character is used as a part of multi byte string. Here is such a random
> example from src/backend/util/adt/varlena.c:
>
> /* Else, it's the traditional escaped style */
> for (bc = 0, tp = inputText; *tp != '\0'; bc++)
> {
> if (tp[0] != '\\')
> tp++;
>
> Sometimes SJIS uses '\' as the second byte of it.
Thanks, I'll try to understand the seriousness of the problem as I don't have
good knowledge of character sets. But your example seems to be telling
everything about the difficulty...
Before digging into the problem, could you share your impression on whether
PostgreSQL can support SJIS? Would it be hopeless? Can't we find any
direction to go? Can I find relevant source code by searching specific words
like "ASCII", "HIGH_BIT", "\\" etc?
Regards
Takayuki Tsunakawa
--
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] Supporting SJIS as a database encoding
From: Tom Lane [mailto:t...@sss.pgh.pa.us] > "Tsunakawa, Takayuki" writes: > > Before digging into the problem, could you share your impression on > > whether PostgreSQL can support SJIS? Would it be hopeless? > > I think it's pretty much hopeless. Even if we were willing to make every > bit of code that looks for '\' and other specific at-risk characters > multi-byte aware (with attendant speed penalties), we could expect that > third-party extensions would still contain vulnerable code. More, we could > expect that new bugs of the same ilk would get introduced all the time. > Many such bugs would amount to security problems. So the amount of effort > and vigilance required seems out of proportion to the benefits. Hmm, this sounds like a death sentence. But as I don't have good knowledge of character set handling yet, I'm not completely convinced about why PostgreSQL cannot support SJIS. I wonder why and how other DBMSs support SJIS and what's the difference of the implementation. Using multibyte-functions like mb... to process characters would solve the problem? Isn't the current implementation blocking the support of other character sets that have similar characteristics? I'll learn the character set handling... > Most of the recent discussion about allowed backend encodings has run more > in the other direction, ie, "why don't we disallow everything but > UTF8 and get rid of all the infrastructure for multiple backend encodings?". > I'm not personally in favor of that, but there are very few hackers who > want to add any more overhead in this area. Personally, I totally agree. I want non-Unicode character sets to disappear from the world. But the real business doesn't seem to forgive the lack of SJIS... Regards Takayuki Tsunakawa -- 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] Supporting SJIS as a database encoding
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Heikki > But one thing that would help a little, would be to optimize the UTF-8 > -> SJIS conversion. It uses a very generic routine, with a binary search > over a large array of mappings. I bet you could do better than that, maybe > using a hash table or a radix tree instead of the large binary-searched > array. That sounds worth pursuing. Thanks! Regards Takayuki Tsunakawa -- 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] Supporting SJIS as a database encoding
> From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Kyotaro > HORIGUCHI Implementing radix tree code, then redefining the format of mapping table > to suppot radix tree, then modifying mapping generator script are needed. > > If no one oppse to this, I'll do that. +100 Great analysis and your guts. I very much appreciate your trial! Regards Takayuki Tsunakawa -- 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] Supporting SJIS as a database encoding
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Kyotaro > Thanks, by the way, there's another issue related to SJIS conversion. MS932 > has several characters that have multiple code points. By converting texts > in this encoding to and from Unicode causes a round-trop problem. For > example, > > 8754(ROMAN NUMERICAL I in NEC specials) > => U+2160(ROMAN NUMERICAL I) > => FA4A (ROMAN NUMERICA I in IBM extension) > > My counting said that 398 characters are affected by this kind of replacement. > Addition to that, "GAIJI" (Private usage area) is not allowed. Is this meet > your purpose? Supporting GAIJI is not a requirement as far as I know. Thank you for sharing information. # I realize my lack of knowledge about character sets... Regards Takayuki Tsunakawa -- 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] autonomous transactions
From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer > Of course, if we could decrease the startup cost of a bgworker For this use in autonomous tx's we could probably pool workers. Or at least lazily terminate them so that the loop cases work better by re-using an existing bgworker. Though I may say something odd, isn’t the bgworker approach going to increase context switches? I thought PostgreSQL has made efforts to decrease context switches, e.g. * Each backend itself writes WAL to disk unlike Oracle requests LGWR process to write REDO to disk. * Releasing and re-acquiring a lwlock appears to try to avoid context switches. /* * Loop here to try to acquire lock after each time we are signaled by * LWLockRelease. * * NOTE: it might seem better to have LWLockRelease actually grant us the * lock, rather than retrying and possibly having to go back to sleep. But * in practice that is no good because it means a process swap for every * lock acquisition when two or more processes are contending for the same * lock. Since LWLocks are normally used to protect not-very-long * sections of computation, a process needs to be able to acquire and * release the same lock many times during a single CPU time slice, even * in the presence of contention. The efficiency of being able to do that * outweighs the inefficiency of sometimes wasting a process dispatch * cycle because the lock is not free when a released waiter finally gets * to run. See pgsql-hackers archives for 29-Dec-01. */ I’m not sure whether to be nervous about the context switch cost in the use cases of autonomous transactions. Regards Takayuki Tsunakawa
Re: [HACKERS] Supporting SJIS as a database encoding
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Kyotaro > HORIGUCHI > > $ time psql postgres -c 'select t.a from t, generate_series(0, )' > > /dev/null > > real 0m22.696s > user 0m16.991s > sys 0m0.182s> > > Using binsearch the result for the same operation was > real 0m35.296s > user 0m17.166s > sys 0m0.216s > > Returning in UTF-8 bloats the result string by about 1.5 times so it doesn't > seem to make sense comparing with it. But it takes real = 47.35s. Cool, 36% speedup! Does this difference vary depending on the actual characters used, e.g. the speedup would be greater if most of the characters are ASCII? Regards Takayuki Tsunakawa -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Remove the comment on the countereffectiveness of large shared_buffers on Windows
Hello, > From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Magnus Hagander > On Wed, Aug 24, 2016 at 4:35 AM, Tsunakawa, Takayuki > wrote: > As a similar topic, I wonder whether the following still holds true, > after many improvements on shared buffer lock contention. > > https://www.postgresql.org/docs/devel/static/runtime-config-re > source.html > > "The useful range for shared_buffers on Windows systems > is generally from 64MB to 512MB." > > > > > Yes, that may very much be out of date as well. A good set of benchmarks > around that would definitely be welcome. I'd like to propose the above-mentioned comment from the manual. The patch is attached. I ran read-only and read-write modes of pgbench, and could not see any apparent decrease in performance when I increased shared_buffers. The scaling factor is 200, where the database size is roughly 3GB. I ran the benchmark on my Windows 10 PC with 6 CPU cores and 16GB of RAM. The database and WAL is stored on the same HDD. <> @echo off for %%s in (256MB 512MB 1GB 2GB 4GB) do ( pg_ctl -w -o "-c shared_buffers=%%s" start pgbench -c18 -j6 -T60 -S bench >> g:\b.txt 2>&1 pg_ctl -t 3600 stop ) <> shared_buffers tps 256MB 63056 512MB 63918 1GB 65520 2GB 66840 4GB 68270 <> shared_buffers tps 256MB 1138 512MB 1187 1GB 1571 2GB 1650 4GB 1598 Regards Takayuki Tsunakawa win_shrdbuf_perf.patch Description: win_shrdbuf_perf.patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?
Hello, Please let me ask you about possible causes of a certain problem, slow shutdown of postmaster when a backend crashes, and whether to fix PostgreSQL. Our customer is using 64-bit PostgreSQL 9.2.8 on RHEL 6.4. Yes, the PostgreSQL version is rather old but there's no relevant bug fix in later 9.2.x releases. PROBLEM == One backend process (postgres) for an application session crashed due to a segmentation fault and dumped a core file. The cause is a bug of pg_dbms_stats. Another note is that restart_after_crash is off to make failover happen. The problem here is that postmaster took as long as 15 seconds to terminate after it had detected a crashed backend. The messages were output as follows: 20:12：35.004に LOG: server process (PID 31894) was terminated by signal 11: Segmentation fault DETAIL: Failed process was running: DELETE...(snip) LOG: terminating any other active server processes >From 20：12：35.013 to 20：12：39.074, the following message was output 80 times. FATAL: the database system is in recovery mode 20:12:50 The custom monitoring system detected the death of postmaster as a result of running "pg_ctl status". That's it. You may say the following message should also have been emitted, but there's not. This is because we commented out the ereport() call in quickdie() in tcop.c. That ereport() call can hang depending on the timing, which is fixed in 9.4. WARNING: terminating connection because of crash of another server process The customer insists that PostgreSQL takes longer to shut down than expected, which risks exceeding their allowed failover time. CAUSE == There's no apparent evidence to indicate the cause, but I could guess a few reasons. What do you think these are correct and should fix PostgreSQL? (I think so) 1) postmaster should close the listening ports earlier As cited above, for 4 seconds, postmaster created 80 dead-end child processes which just output "FATAL: the database system is in recovery mode". This indicates that postmaster is busy handling re-connection requests from disconnected applications, preventing postmaster from reaping dead children as fast as possible. This is a waste because postmaster will only shut down. I think the listening ports should be closed in HandleChildCrash() when the condition "(RecoveryError || !restart_after_crash)" is true. 2) make stats collector terminate immediately stats collector seems to write the permanent stats file even when it receives SIGQUIT. But it's useless because the stat file is reset during recovery. And Tom claimed that writing stats file can take long: https://www.postgresql.org/message-id/11800.1455135...@sss.pgh.pa.us 3) Anything else? While postmaster is in PM_WAIT_DEAD_END state, it leaves the listening ports open but doesn't call select()/accept(). As a result, incoming connection requests are accumulated in the listen queue of the sockets. Does the OS have any bug to slow the process termination when the listen queue is not empty? Regards Takayuki Tsunakawa -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?
> From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas > On Tue, Sep 20, 2016 at 2:20 AM, Tsunakawa, Takayuki > wrote: > > There's no apparent evidence to indicate the cause, but I could guess > > a few reasons. What do you think these are correct and should fix > > PostgreSQL? (I think so) > > I think that we shouldn't start changing things based on guesses about what > the problem is, even if they're fairly smart guesses. The thing to do would > be to construct a test rig, crash the server repeatedly, and add debugging > instrumentation to figure out where the time is actually going. We have tried to reproduce the problem in the past several days with much more stress on our environment than on the customer's one -- 1,000 tables aiming for a dozens of times larger stats file and repeated reconnection requests from hundreds of clients -- but we could not succeed. > I do think your theory about the stats collector might be worth pursuing. > It seems that the stats collector only responds to SIGQUIT, ignoring SIGTERM. > Making it do a clean shutdown on SIGTERM and a fast exit on SIGQUIT seems > possibly worthwhile. Thank you for giving confidence for proceeding. And I also believe that postmaster should close the listening ports earlier. Regardless of whether this problem will be solved not confident these will solve the, I think it'd be better to fix these two points so that postmaster doesn't longer time than necessary. I think I'll create a patch after giving it a bit more thought. Regards Takayuki Tsunakawa -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC] Change the default of update_process_title to off
> From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Thomas Munro > Another database vendor recommends granting SeLockMemoryPrivilege so that > it can use large pages on Windows when using several GB of buffer pool. > I wonder if that might help Postgres on Windows. This could be useful as > a starting point to test that theory: > > https://www.postgresql.org/message-id/CAEepm%3D075-bgHi_VDt4SCAmt%2Bo_ > %2B1XaRap2zh7XwfZvT294oHA%40mail.gmail.com Sorry for my late reply, and thank you. I've created a patch based on yours, and I'll submit it shortly in a separate thread. Regards Takayuki Tsunakawa -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Supporting huge pages on Windows
Hello, The attached patch implements huge_pages on Windows. I'll add this to the CommitFest. The performance improvement was about 2% with the following select-only pgbench. The scaling factor is 200, where the database size is roughly 3GB. I ran the benchmark on my Windows 10 PC with 6 CPU cores and 16GB of RAM. pgbench -c18 -j6 -T60 -S bench Before running pgbench, I used pg_prewarm to cache all pgbench tables and indexes (excluding the history table) in the 4GB shared buffers. The averages of running pgbench three times are: huge_pages=off: 70412 tps huge_pages=on : 72100 tps The purpose of pg_ctl.c modification is to retain "Lock pages in memory" Windows user right in postgres. That user right is necessary for the large-page support. The current pg_ctl removes all privileges when spawning postgres, which is overkill. The system administrator should be able to assign appropriate privileges to the PostgreSQL service account. Credit: This patch is based on Thomas Munro's one. Regards Takayuki Tsunakawa win_large_page.patch Description: win_large_page.patch -- 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] Supporting huge pages on Windows
> From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas > On Sun, Sep 25, 2016 at 10:45 PM, Tsunakawa, Takayuki > wrote: > > Credit: This patch is based on Thomas Munro's one. > > How are they different? As Thomas mentioned, his patch (only win32_shmem.c) might not have been able to compile (though I didn't try.) And it didn't have error processing or documentation. I added error handling, documentation, comments, and a little bit of structural change. The possibly biggest change, though it's only one-liner in pg_ctl.c, is additionally required. I failed to include it in the first patch. The attached patch includes that. Regards Takayuki Tsunakawa win_large_page_v2.patch Description: win_large_page_v2.patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?
> From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane > Allowing SIGQUIT to prompt fast shutdown of the stats collector seems sane, > though. Try to make sure it doesn't leave partly-written stats files > behind. The attached patch based on HEAD does this. I'd like this to be back-patched because one of our important customers uses 9.2. I didn't remove partially written stat files on SIGQUIT for the following reasons. Is this OK? 1. The recovery at the next restart will remove the stat files. 2. SIGQUIT processing should be as fast as possible. 3. If writing stats files took long due to the OS page cache flushing, removing files might be forced to wait likewise. > FWIW, I'm pretty much -1 on messing with the timing of the socket close > actions. I broke that once within recent memory, so maybe I'm gun-shy, > but I think that the odds of unpleasant side effects greatly outweigh any > likely benefit there. Wasn't it related to TouchSocketFiles()? Can I see the discussion on this ML? I don't see any problem looking at the code... Regards Takayuki Tsunakawa 01_pgstat_avoid_writing_on_sigquit.patch Description: 01_pgstat_avoid_writing_on_sigquit.patch -- 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] Supporting huge pages on Windows
From: Thomas Munro [mailto:thomas.mu...@enterprisedb.com] > > huge_pages=off: 70412 tps > > huge_pages=on : 72100 tps > > Hmm. I guess it could be noise or random code rearrangement effects. I'm not the difference was a random noise, because running multiple set of three runs of pgbench (huge_pages = on, off, on, off, on...) produced similar results. But I expected a bit greater improvement, say, +10%. There may be better benchmark model where the large page stands out, but I think pgbench is not so bad because its random data access would cause TLB cache misses. > I saw your recent post[2] proposing to remove the sentence about the 512MB > effective limit and I wondered why you didn't go to larger sizes with a > larger database and more run time. But I will let others with more > benchmarking experience comment on the best approach to investigate Windows > shared_buffers performance. Yes, I could have gone to 8GB of shared_buffers because my PC has 16GB of RAM, but I felt the number of variations was sufficient. Anyway, positive comments on benchmarking would be appreciated. Regards Takayuki Tsunakawa -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane FWIW, I'm pretty much -1 on messing with the timing of the socket close > actions. I broke that once within recent memory, so maybe I'm gun-shy, > but I think that the odds of unpleasant side effects greatly outweigh any > likely benefit there. I couldn't find any relevant mails in pgsql-hackers. I found no problem with the attached patch. Do you think this is OK? Regards Takayuki Tsunakawa 02_close_listen_ports_early.patch Description: 02_close_listen_ports_early.patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Is the last 9.1 release planned?
Hello, (Please point me to the appropriate ML if this is not the right one.) According to the following mail, I thought one more release for 9.1 (9.1.24) was scheduled in September. Is there any release plan for the 9.1 last release? If there's, I want to wait for it, and apply 9.1.23 otherwise. https://www.postgresql.org/message-id/1470924187.12735.59.ca...@gunduz.org [Excerpt] PostgreSQL version 9.1 will be End-of-Life in September 2016. The project expects to only release one more update for that version. Regards Takayuki Tsunakawa -- 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] Is the last 9.1 release planned?
From: Michael Paquier [mailto:michael.paqu...@gmail.com] > 9.1.24 will be the last in the 9.1 series as far as I know. And it is still > to come at the beginning of November: > https://www.postgresql.org/developer/roadmap/ But the release note for 9.1.23 says: "The PostgreSQL community will stop releasing updates for the 9.1.X release series in September 2016. Users are encouraged to update to a newer release branch soon." OTOH, the 9.0.22 release note said: "The PostgreSQL community will stop releasing updates for the 9.0.X release series in September 2015. Users are encouraged to update to a newer release branch soon." and the 9.0.23, which is the last release for 9.0 said: "This is expected to be the last PostgreSQL release in the 9.0.X series. Users are encouraged to update to a newer release branch soon." and 9.0.23 was released in October 8. So I guessed 9.1.24 will be released in a week or so. Regards Takayuki Tsunakawa -- 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] Is the last 9.1 release planned?
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Jaime Casanova > Well, no. We normally don't give special treatment to any minor release > not even if it is going to die. > What normally happens is that all minor releases are released the same day. > > Taken your example, that same day were released: 9.0.23, 9.1.19, 9.2.14, > 9.3.10 and 9.4.5 > Thanks for clarification. Then, I understood that the expression "stop releases in September" in the release note and a pgsql-announce mail was not correct. Regards Takayuki Tsunakawa -- 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] Switch to unnamed POSIX semaphores as our preferred sema code?
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane > I've gotten a bit tired of seeing "could not create semaphores: No space > left on device" failures in the buildfarm, so I looked into whether we should > consider preferring unnamed POSIX semaphores over SysV semaphores. +100 Wonderful decision and cautious analysis. This will make PostgreSQL more friendly to users, especially newcomers, by eliminating the need to tune kernel resources. I wish other kernel resources (files, procs) will need no tuning like Windows, but that's just a daydream. Regards Takayuki Tsunakawa -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?
From: Robert Haas [mailto:robertmh...@gmail.com] > I have no opinion on this patch, because I haven't reviewed it, but note > recent commit 3b90e38c5d592ea8ec8236287dd5c749fc041728, which appears to > be semi-related. Thank you for interesting information. Maybe Tom-san experienced some trouble in creating this patch. Fortunately, this doesn't appear to be related to my patch, because the patch changed the timing of closing listen ports in postmaster children, whereas my patch explicitly closes listen ports in postmaster. Regards Takayuki Tsunakawa -- 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] Is the last 9.1 release planned?
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Magnus Hagander > On Oct 5, 2016 5:42 AM, "Tsunakawa, Takayuki" > wrote: > > Thanks for clarification. Then, I understood that the expression "stop > releases in September" in the release note and a pgsql-announce mail was > not correct. > > > It basically means stop guaranteeing that we do. As of a couple of days > ago, bug fixes won't necessarily be back ported to 9.1 if they are difficult. > But there will be one wrap-up release in November with any patches that > have already been applied but have not yet been in a release. And after > November, we will stop doing that as well. I see. I simply took the phrases in pgsql-announce "September is EOL" and "only expects one more release" as meaning "only expects one more release in September", because I didn't imagine a minor version is released after EOL. If possible, I was happy if I saw "only expects one more release in November" or "on a regular schedule". Regards Takayuki Tsunakawa -- 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] Supporting huge pages on Windows
From: Thomas Munro [mailto:thomas.mu...@enterprisedb.com] > Your ~2.4% number is similar to what was reported for Linux with 4GB > shared_buffers: > > https://www.postgresql.org/message-id/20130913234125.GC13697%40roobarb > .crazydogs.org I'm relieved to know that a similar figure was gained on Linux. Thanks for the info. > Later in that thread there was a report of a dramatic ~15% increase in "best > result" TPS, but that was with 60GB of shared_buffers on a machine with > 256GB of RAM: > > https://www.postgresql.org/message-id/20131024060313.GA21888%40toroid. > org From: Andres Freund [mailto:and...@anarazel.de] > FWIW, I've seen 2-3x increases with ~60GB of s_b. Wow, nice figures. It's unfortunate that I don't have such a big machine available at hand. Regards Takayuki Tsunakawa -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [RFC] Transaction management overhaul is necessary?
Hello, >From our experience in handling customers' problems, I feel it's necessary to >evolve PostgreSQL's transaction management. The concrete problems are: 1. PostgreSQL cannot end and begin transactions in PL/pgSQL and PL/Java stored functions. This is often the reason people could not migrate to PostgreSQL. 2. PostgreSQL does not support statement-level rollback. When some customer ran a batch app using psqlODBC, one postgres process used dozens of GBs of memory and crashed the OS. The batch app prepares some SQL statements with parameters, execute it five millions of times with different parameter values in a single transaction. They didn't experience a problem with Oracle. This was because psqlODBC starts and ends a subtransaction for each SQL statement by default to implement statement-level rollback. And PostgreSQL creates one CurTransactionContext memory context, which is 8KB, for each subtransaction and retain them until the top transaction ends. The total memory used becomes 40GB (8KB * 5 million subtransactions.) This was avoided by setting the Protocol parameter to 7.4-1, which means transaction-level rollback. The savepoint approach for supporting statement-level rollback is inefficient, because it adds two roundtrips (SAVEPOINT and RELEASE) for each statement. I know autonomous transaction is also discussed, which seems to be difficult, so I hope some kind of transaction management overhaul can be discussed to cover all these transaction-related features. How should I start? I found the following item in the TODO list (but I haven't read it yet.) What other discussions should I look at? -- Implement stored procedures This might involve the control of transaction state and the return of multiple result sets PL/pgSQL stored procedure returning multiple result sets (SELECTs)? Proposal: real procedures again (8.4) http://archives.postgresql.org/pgsql-hackers/2010-09/msg00542.php Gathering specs and discussion on feature (post 9.1) -- Regards Takayuki Tsunakawa -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC] Transaction management overhaul is necessary?
From: Craig Ringer [mailto:cr...@2ndquadrant.com]
> >> This was because psqlODBC starts and ends a subtransaction for each
> >> SQL statement by default to implement statement-level rollback. And
> >> PostgreSQL creates one CurTransactionContext memory context, which is
> >> 8KB, for each subtransaction and retain them until the top transaction
> ends.
>
> Surely that's where to start then. Find a way to pool and re-use, fully
> release, or otherwise be done with transaction contexts for released
> savepoints.
Yes, I'll investigate this. Any reference information would be appreciated on
why the CurTransactionContexts had to be retained, and whether it's difficult
to circumvent.
> You can control transaction level rollback in psqlODBC directly. You do
> not need to fall back to the old protocol. Check the driver options.
That driver option is Protocol=7.4-1. The name is misleading, as the driver
now ignores version part (7.4), and interprets 1 as transaction-rollback.
> Right. We can't just fire off each statement wrapped in SAVEPOINT and RELEASE
> SAVEPOINT because we need to get the result of the statement and decide
> whether to ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT. It only requires
> two round trips if you shove the SAVEPOINT in with the intended statement,
> but it's still messy.
>
> I'd like to see an alternative statement with semantics more akin to COMMIT
> - which automatically into ROLLBACK if the tx is aborted.
> COMMIT SAVEPOINT would be too confusing since it's not truly committed.
> I don't know what to call it. But basically something that does RELEASE
> SAVEPOINT [named savepoint] unless the subxact is in aborted state, in which
> case it does ROLLBACK TO [named savepoint].
> Bonus points for letting it remember the last savepoint created and use
> that.
>
> Furthermore, we should really add it on the protocol level so drivers can
> send subtransaction control messages more compactly, without needing to
> go through the parser etc, and without massively spamming the logs. For
> this purpose savepoint names would be internally generated so the driver
> wouldn't have to send them. We'd log savepoint boundaries when transaction
> logging was enabled. Since the client would send the first such protocol
> request we could do it on the sly without a protocol version bump; clients
> could just check server version and not use the new messages for older
> servers. If they send it to an older server they get a protocol error, which
> is fine.
I'm simply thinking of proposing a new GUC, something like "SET auto_rollback =
{none | statement | transaction}", where none is the default and traditional
behavior.
> > You should to implement a CALL statement - that can be independent on
> > outer transaction. The behave inside procedure called by CALL
> > statement should be same like client side - and there you can controll
> > transactions explicitly without nesting.
>
> I agree that'd be desirable. Top level "procedures" are necessary for this,
> really.
>
> This would also enable us to return multiple result sets.
>
> We'd probably have to start at least one small read-only tx for the initial
> cache access to look up the proc and set everything up, but if we don't
> allocate xids local transactions are super cheap.
OK, that would be a very big challenge... I can't imagine how difficult it will
be now. But supporting the stored procedure with CALL statement would be a
wall to overcome.
> However, I think trying to tackle the memory context bloat reported upthread
> would be a more effective starting point since it immediately targets the
Yes, I think I'll address this. Maybe I'll start different threads for each
topic:
1. Memory context bloat
2. Statement-level rollback
3. Stored procedures where transactions can be ended and started
Regards
Takayuki Tsunakawa
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [bug fix] ECPG: fails to recognize embedded parameters
Hello, This is an actual problem that our customer hit. In ECPG, opening a cursor fails which is declared as follows: EXEC SQL DECLARE cur CURSOR FOR SELECT oid, datname FROM pg_database WHERE datname LIKE 'post%' ESCAPE '\' AND datconnlimit = :connlimit; sqlstate: 07001 sqlerrm.sqlerrmc: too many arguments on line 30 The cause is that next_insert() in ecpglib unconditionally skips the next character after the backslash. Could you review and commit the attached patch? I also attached the test program for convenience. Regards Takayuki Tsunakawa ecpg_escape_string.patch Description: ecpg_escape_string.patch escape.ec Description: escape.ec -- 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] Remove secondary checkpoint
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane > Doesn't it also make crash recovery less robust? The whole point > of that mechanism is to be able to cope if the latest checkpoint > record is unreadable. If the latest checkpoint record is unreadable (the WAL segment/block/record is corrupt?), recovery from the previous checkpoint would also stop at the latest checkpoint. And we don't need to replay the WAL records between the previous checkpoint and the latest one, because their changes are already persisted when the latest checkpoint was taken. So, the user should just do pg_resetxlog and start the database server when the recovery cannot find the latest checkpoint record and PANICs? Regards Takayuki Tsunakawa -- 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] Remove secondary checkpoint
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier > On Tue, Oct 24, 2017 at 5:58 PM, Tsunakawa, Takayuki > wrote: > > If the latest checkpoint record is unreadable (the WAL > segment/block/record is corrupt?), recovery from the previous checkpoint > would also stop at the latest checkpoint. And we don't need to replay the > WAL records between the previous checkpoint and the latest one, because > their changes are already persisted when the latest checkpoint was taken. > So, the user should just do pg_resetxlog and start the database server when > the recovery cannot find the latest checkpoint record and PANICs? > > Not necessarily. If a failure is detected when reading the last checkpoint, > as you say recovery would begin at the checkpoint prior that and would stop > when reading the record of last checkpoint, still one could use a > recovery.conf with restore_command to fetch segments from a different > source, like a static archive, as only the local segment may be corrupted. Oh, you are right. "If the crash recovery fails, perform recovery from backup." Anyway, I agree that the secondary checkpoint isn't necessary. Regards Takayuki Tsunakawa -- 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] Remove secondary checkpoint
From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Simon Riggs
> This
> * reduces disk space requirements on master
> * removes a minor bug in fast failover
> * simplifies code
I welcome this patch. I was wondering why PostgreSQL retains the previous
checkpoint. (Although unrelated to this, I've also been wondering why
PostgreSQL flushes WAL to disk when writing a page in the shared buffer,
because PostgreSQL doesn't use WAL for undo.)
Here are my review comments.
(1)
-* a) we keep WAL for two checkpoint cycles, back to the "prev"
checkpoint.
+* a) we keep WAL for only one checkpoint cycle (prior to PG11 we kept
+*WAL for two checkpoint cycles to allow us to recover from the
+*secondary checkpoint if the first checkpoint failed, though we
+*only did this on the master anyway, not on standby. Keeping just
+*one checkpoint simplifies processing and reduces disk space in
+*many smaller databases.)
/*
-* The last valid checkpoint record required for a
streaming
-* recovery exists in neither standby nor the primary.
+* We used to attempt to go back to a secondary
checkpoint
+* record here, but only when not in standby_mode. We
now
+* just fail if we can't read the last checkpoint
because
+* this allows us to simplify processing around
checkpoints.
*/
if (fast_promote)
{
- checkPointLoc = ControlFile->prevCheckPoint;
+ /*
+* It appears to be a bug that we used to use
prevCheckpoint here
+*/
+ checkPointLoc = ControlFile->checkPoint;
- XLByteToSeg(PriorRedoPtr, _logSegNo, wal_segment_size);
+ /* Trim from the last checkpoint, not the last - 1 */
+ XLByteToSeg(RedoRecPtr, _logSegNo, wal_segment_size);
I suggest to remove references to the secondary checkpoint (the old behavior)
from the comments. I'm afraid those could confuse new developers.
(2)
@@ -8110,10 +8100,6 @@ ReadCheckpointRecord(XLogReaderState *xlogreader,
XLogRecPtr RecPtr,
ereport(LOG,
(errmsg("invalid primary
checkpoint link in control file")));
break;
@@ -8135,10 +8121,6 @@ ReadCheckpointRecord(XLogReaderState *xlogreader,
XLogRecPtr RecPtr,
ereport(LOG,
(errmsg("invalid primary
checkpoint record")));
break;
@@ -8154,10 +8136,6 @@ ReadCheckpointRecord(XLogReaderState *xlogreader,
XLogRecPtr RecPtr,
ereport(LOG,
(errmsg("invalid resource
manager ID in primary checkpoint record")));
break;
etc, etc.
"primary checkpoint" should be just "checkpoint", now that the
primary/secondary concept will disappear.
(3)
Should we change the default value of max_wal_size from 1 GB to a smaller size?
I vote for "no" for performance.
Regards
Takayuki Tsunakawa
--
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 fix] ECPG: fails to recognize embedded parameters
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Meskes > Thanks for spotting and fixing. I just committed your patch to master and > backported to 9.4, 9.5, 9.6 and 10. It doesn't apply cleanly to 9.3. But > then it might not be important enough to investigate and backported to this > old a version. Thanks. I'm OK with 9.3. Regards Takayuki Tsunakawa -- 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] Remove secondary checkpoint
From: Alvaro Herrera [mailto:alvhe...@alvh.no-ip.org] > Tsunakawa, Takayuki wrote: > > > (Although unrelated to this, I've also been wondering why PostgreSQL > > flushes WAL to disk when writing a page in the shared buffer, because > > PostgreSQL doesn't use WAL for undo.) > > The reason is that if the system crashes after writing the data page to > disk, but before writing the WAL, the data page would be inconsistent with > data in pages that weren't flushed, since there is no WAL to update those > other pages. Also, if the system crashes after partially writing the page > (say it writes the first 4kB) then the page is downright corrupted with > no way to fix it. > > So there has to be a barrier that ensures that the WAL is flushed up to > the last position that modified a page (i.e. that page's LSN) before actually > writing that page to disk. And this is why we can't use mmap() for shared > buffers -- there is no mechanism to force the WAL down if the operation > system has the liberty to flush pages whenever it likes. I see. The latter is a torn page problem, which is solved by a full page image WAL record. I understood that an example of the former problem is the inconsistency between a table page and an index page -- if an index page is flushed to disk without slushing the WAL and the corresponding table page, an index entry would point to a wroing table record after recovery. Thanks, my long-standing question has beenn solved. Regards Takayuki Tsunakawa -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [bug fix] postgres.exe crashes with access violation on Windows while starting up
Hello, We encountered a rare and hard-to-investigate problem on Windows, which one of our customers reported. Please find the attached patch to fix that. I'll add this to the next CF. PROBLEM == PostgreSQL sometimes crashes with the following messages. This is infrequent (but frequent for the customer); it occurred about 10 times in the past 5 months. LOG: server process (PID 2712) was terminated by exception 0xC005 HINT: See C include file "ntstatus.h" for a description of the hexadecimal value. LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing "server process" shows that an client backend crashed. The above messages indicate that the process was not running an SQL command. PostgreSQL runs as a Windows service. No crash dump was produced anywhere, despite the facts: - /crashdumps folder exists and is writable by the PostgreSQL user account (which is the user postgres.exe runs as) - The Windows registry configuration allows dumping the crash dump CAUSE == We believe WSAStartup() in main.c failed. The only conceivable error is: WSAEPROCLIM 10067 Too many processes. A Windows Sockets implementation may have a limit on the number of applications that can use it simultaneously. WSAStartup may fail with this error if the limit has been reached. But I couldn't find what the limit is and whether we can tune it. We couldn't reproduce the problem. When I pretend that WSAStartup() failed while a client backend is starting up, I could see the same phenomenon as the customer. This problem only occurs when PostgreSQL runs as a Windows service. The bug is in write_eventlog(). It calls pgwin32_message_to_utf16() which in turn calls palloc(), which requires the memory management system to be set up (CurrentMemoryContext != NULL). FIX == Add the check "CurrentMemoryContext != NULL" in write_eventlog() as in write_console(). NOTE == The reason is for not outputing the crash dump is a) the crash occurred before installing the Windows exception handler (pgwin32_install_crashdump_handler() call) and b) the effect of the following call in postmaster is inherited in the child process. /* In case of general protection fault, don't show GUI popup box */ SetErrorMode(SEM_FAILCRITICALERRORS | SEM_NOGPFAULTERRORBOX); But I'm not sure in what order we should do pgwin32_install_crashdump_handler(), startup_hacks() and steps therein, MemoryContextInit(). I think that's another patch. Regards Takayuki Tsunakawa write_eventlog_crash.patch Description: write_eventlog_crash.patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [bug fix] postgres.exe crashes with access violation on Windows while starting up
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tsunakawa, > Takayuki > The reason is for not outputing the crash dump is a) the crash occurred > before installing the Windows exception handler > (pgwin32_install_crashdump_handler() call) and b) the effect of the > following call in postmaster is inherited in the child process. > > /* In case of general protection fault, don't show GUI popup > box */ > SetErrorMode(SEM_FAILCRITICALERRORS | > SEM_NOGPFAULTERRORBOX); > > But I'm not sure in what order we should do > pgwin32_install_crashdump_handler(), startup_hacks() and steps therein, > MemoryContextInit(). I think that's another patch. Just installing the handler at the beginning of main() seems fine. Patch attached. Regards Takayuki Tsunakawa crash_dump_before_installing_handler.patch Description: crash_dump_before_installing_handler.patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: schema variables
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Pavel Stehule > I propose a new database object - a variable. The variable is persistent > object, that holds unshared session based not transactional in memory value > of any type. Like variables in any other languages. The persistence is > required for possibility to do static checks, but can be limited to session > - the variables can be temporal. > > > My proposal is related to session variables from Sybase, MSSQL or MySQL > (based on prefix usage @ or @@), or package variables from Oracle (access > is controlled by scope), or schema variables from DB2. Any design is coming > from different sources, traditions and has some advantages or disadvantages. > The base of my proposal is usage schema variables as session variables for > stored procedures. It should to help to people who try to port complex > projects to PostgreSQL from other databases. Very interesting. I hope I could join the review and testing. How do you think this would contribute to easing the port of Oracle PL/SQL procedures? Would the combination of orafce and this feature promote auto-translation of PL/SQL procedures? I'm curious what will be the major road blocks after adding the schema variable. Regards Takayuki Tsunakawa -- 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 fix] postgres.exe crashes with access violation on Windows while starting up
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier > So you are basically ready to lose any message that could be pushed > here if there is no memory context? That does not sound like a good > trade-off to me. A static buffer does not look like the best idea > either to not truncate message, so couldn't we envisage to just use > malloc? pgwin32_message_to_UTF16() is called in two places in elog.c, > and there is a full control on the error code paths. Thank you for reviewing a rare bug fix on Windows that most people wouldn't be interested in. When CurrentMemoryContext is NULL, the message is logged with ReportEventA(). This is similar to write_console(). Regards Takayuki Tsunakawa -- 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 fix] postgres.exe crashes with access violation on Windows while starting up
From: Michael Paquier [mailto:michael.paqu...@gmail.com] > On Tue, Oct 31, 2017 at 6:59 AM, Tsunakawa, Takayuki > wrote: > > When CurrentMemoryContext is NULL, the message is logged with > ReportEventA(). This is similar to write_console(). > > My point is that as Postgres is running as a service, isn't it wrong to > write a message to stderr as a fallback if the memory context is not set? > You would lose a message. It seems to me that for an operation that can > happen at a low-level like the postmaster startup, we should really use > a low-level operation as well. I'm sorry I may not have been clear. With this patch, write_eventlog() outputs the message to event log with ReportEventA() when CurrentMemoryContext is NULL. It doesn't write to stderr. So the message won't be lost. Regards Takayuki Tsunakawa -- 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] Statement-level rollback
From: Peter Eisentraut [mailto:peter.eisentr...@2ndquadrant.com] > The difference is how error recovery works. So this will necessarily be > tied to how the client code or other surrounding code is structured or what > the driver or framework is doing in the background to manage transactions. > It would also be bad if client code was not prepared for this new behavior, > reported the transaction as complete while some commands in the middle were > omitted. > > Drivers can already achieve this behavior and do do that by issuing savepoint > commands internally. The point raised in this thread was that that creates > too much network overhead, so a backend-based solution would be preferable. > We haven't seen any numbers or other evidence to quantify that claim, so > maybe it's worth looking into that some more. > > In principle, a backend-based solution that drivers just have to opt into > would save a lot of duplication. But the drivers that care or require it > according to their standards presumably already implement this behavior > in some other way, so it comes back to whether there is a performance or > other efficiency gain here. > > Another argument was that other SQL implementations have this behavior. > This appears to be the case. But as far as I can tell, it is also tied > to their particular interfaces and the structure and flow control they > provide. So a client-side solution like psql already provides or something > in the various drivers would work just fine here. > > So my summary for the moment is that a GUC or similar run-time setting might > be fine, with appropriate explanation and warnings. But it's not clear > whether it's worth it given the existing alternatives. I can think of four reasons why the server-side support is necessary or desirable. First, the server log could be filled with SAVEPOINT and RELEASE lines when you need to investigate performance or audit activity. Second, the ease of use for those who migrate from other DBMSs. With the server-side support, only the DBA needs to be aware of the configuration in postgresql.conf. Other people don't need to be aware of the client-side parameter when they deploy applications. Third, lack of server-side support causes trouble to driver developers. In a recent discussion with the psqlODBC committer, he had some trouble improving or fixing the statement-rollback support. Npgsql doesn't have the statement-rollback yet. PgJDBC has supported the feature with autosave parameter only recently. Do the drivers for other languages like Python, Go, JavaScript have the feature? We should reduce the burdon on the driver developers. Fourth, the runtime performance. In a performance benchmark of one of our customers, where a batch application ran 1.5 or 5 million small SELECTs with primary key access, the execution time of the whole batch became shorter by more than 30% (IIRC) when the local connection was used instead of the remote TCP/IP one. The communication overhead is not small. Also, in the PostgreSQL documentation, the communication overhead is treated seriously as follows: https://www.postgresql.org/docs/devel/static/plpgsql-overview.html#plpgsql-advantages [Excerpt] -- That means that your client application must send each query to the database server, wait for it to be processed, receive and process the results, do some computation, then send further queries to the server. All this incurs interprocess communication and will also incur network overhead if your client is on a different machine than the database server. With PL/pgSQL you can group a block of computation and a series of queries inside the database server, thus having the power of a procedural language and the ease of use of SQL, but with considerable savings of client/server communication overhead. •Extra round trips between client and server are eliminated •Intermediate results that the client does not need do not have to be marshaled or transferred between server and client •Multiple rounds of query parsing can be avoided This can result in a considerable performance increase as compared to an application that does not use stored functions. -- Craig reports the big communication overhead: PATCH: Batch/pipelining support for libpq https://www.postgresql.org/message-id/CAMsr+YFUjJytRyV4J-16bEoiZyH=4nj+sQ7JP9ajwz=b4dm...@mail.gmail.com#CAMsr+YFUjJytRyV4J-16bEoiZyH=4nj+sQ7JP9ajwz=b4dm...@mail.gmail.com Re: foreign table batch insert https://www.postgresql.org/message-id/CAMsr+YFgDUiJ37DEfPRk8WDBuZ58psdAYJd8iNFSaGxtw=w...@mail.gmail.com [Excerpt] -- The time difference for 10k inserts on the local host over a unix socket shows a solid improvement: batch insert elapsed: 0.244293s sequential insert elapsed: 0.375402s ... but over, say, a connection to a random AW
Re: [HACKERS] Statement-level rollback
From: Craig Ringer [mailto:cr...@2ndquadrant.com] > The example often cited is some variant of > > BEGIN; > CREATTE TABLE t2 AS SELECT * FROM t1; > DROP TABLE t1; > ALTER TABLE t2 RENAME TO t1; > COMMIT; > > Right now, we do the right thing here. With default statement level rollback, > you just dropped t1 and all your data. oops. That's a horrible example. So I think the default behavior should be what it is now for existing PostgreSQL users. > On a related note, psql's -v ON_ERROR_STOP=1 is horrible and hard to discover > UI, and one of the top FAQs on Stack Overflow is some variant of "I'm getting > random and incomprehensible errors restoring a dump, wtf?". So I'd really > love to make it the default, but we'd face similar issues where a SQL script > that's currently correct instead produces dangerously wrong results with > ON_ERROR_STOP=1 . Yes. And although unrelated, psql's FETCH_SIZE is also often invisible to users. They report out-of-memory trouble when they do SELECT on a large table with psql. > What about if we add protocol-level savepoint support? Two new messages: > > BeginUnnamedSavepoint > > and > > EndUnnamedSavepoint > > where the latter does a rollback-to-last-unnamed-savepoint if the txn state > is bad, or a release-last-unnamed-savepoint if the txn state is ok. That > means the driver doesn't have to wait for the result of the statement. It > knows the conn state and query outcome from our prior messages, and knows > that as a result of this message any failed state has been rolled back. > > This would, with appropriate libpq support, give people who want statement > level error handling pretty much what they want. And we could expose it > in psql too. No GUCs needed, no fun surprises for apps. psqlODBC could adopt > it to replace its current slow and super-log-spammy statement rollback > model. > > Downside is that it needs support in each client driver. Yes, I believe we should avoid the downside. It's tough to develop and maintain a client driver, so we should minimize the burdon with server-side support. Regards Takayuki Tsunakawa -- 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] Statement-level rollback
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Vladimir > Sitnikov > Tsunakawa> PgJDBC has supported the feature with autosave parameter only > Tsunakawa> recently > > PgJDBC has the implementation for more than a year (REL9.4.1210, 2016-09-07, > see https://github.com/pgjdbc/pgjdbc/pull/477 ) And I heard from someone in PgJDBC community that the autosave parameter was not documented in the manual for a while, which I confirmed. So the statement-level rollback is newer to users, isn't it? > The performance overhead for "SELECT" statement (no columns, just select) > statement over localhost is 36±4 us vs 38±3 us (savepoint is pipelined along > with user-provided query). That is network overhead is close to negligible. That's good news, because it also means that the overhead of creating a savepoint is negligible. > As far as I understand, the main problem with savepoints is they would > consume memory even in case the same savepoint is reassigned again and again. > In other words, "savepoint; insert;savepoint; insert;savepoint; > insert;savepoint; insert;savepoint; insert;" would allocate xids and might > blow up backend's memory. > I see no way driver can workaround that, so it would be great if backend > could release memory or provide a way to do so. Doesn't PgJDBC execute RELEASE after each SQL statement? That said, even with RELEASE, the server memory bloat is not solved. The current server implementation allocates a memory chunk of 8KB called CurTranContext for each subtransaction, and retains them until the end of top-level transaction. That's another (separate) issue to address. Regards Takayuki Tsunakawa -- 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] Statement-level rollback
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Simon Riggs > A backend-based solution is required for PL procedures and functions. > > We could put this as an option into PL/pgSQL, but it seems like it is > a function of the transaction manager rather than the driver. Exactly. Thanks. Regards Takayuki Tsunakawa -- 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] Planning counters in pg_stat_statements
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Thomas Munro > I have often wanted $SUBJECT and was happy to find that Fujii-san had posted > a patch five years ago[1]. The reception then seemed positive. > So here is a refurbished and (hopefully) improved version of his patch with > a new column for the replan count. Thoughts? That's a timely proposal. I sometimes faced performance problems where the time pg_stat_statements shows is much shorter than the application perceives. The latest experience was that the execution time of a transaction, which consists of dozens of DMLs and COMMIT, was about 200ms from the application's perspective, while pg_stat_statements showed only about 10ms in total. The network should not be the cause because the application ran on the same host as the database server. I wanted to know how long the parsing and planning time was. BTW, the current pg_stat_statement shows unexpected time for COMMIT. I expect it to include the whole COMMIT processing, including the long WAL flush and sync rep wait. However, it only shows the time for the transaction state change in memory. Regards Takayuki Tsunakawa -- 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] Supporting huge pages on Windows
Hi Thomas, Magnus From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Thomas Munro > Since it only conflicts with c7b8998e because of pgindent whitespace > movement, I applied it with "patch -p1 --ignore-whitespace" and created > a new patch. See attached. Thanks, Thomas. I've added your name in the CF entry so that your name will also be listed on the release note, because my patch is originally based on your initial try. Please remove your name just in case you mind it. BTW, your auto-reviewer looks very convenient. Thank you again for your great work. Magnus, it would be grateful if you could review and commit the patch while your memory is relatively fresh. I've been in a situation which keeps me from doing development recently, but I think I can gradually rejoin the community activity soon. Regards Takayuki Tsunakawa -- 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 fix] Savepoint-related statements terminates connection
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane > The originally reported bug is fixed. Not making any claims about other > bugs ... I'm sorry I couldn't reply to you. I've recently been in a situation where I can't use my time for development. I think I'll be able to rejoin the community activity soon. I confirmed your patch fixed the problem. And the code looks perfect. Thank you very much. Regards Takayuki Tsunakawa -- 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] Supporting huge pages on Windows
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Ashutosh Sharma > I have once again tested the latest patch (v14 patch) on Windows and the > results looked fine to me. Basically I have repeated the test cases which > I had done earlier on v8 patch. For more details, on the tests that i have > re-executed, please refer to - [1]. Thanks. Thanks so much. I'm relieved to know that the patch still works. Regards Takayuki Tsunakawa -- 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 10 release notes
It's embarrassing to ask about such a trivial thing, but I noticed the following line was missing in the latest release note, which was originally in Bruce's website: Remove documented restriction about using large shared buffers on Windows (Takayuki Tsunakawa) Is this intended? Regards Takayuki Tsunakawa -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [bug fix] PG10: libpq doesn't connect to alternative hosts when some errors occur
Hello, Robert, Noah From: Robert Haas [mailto:robertmh...@gmail.com] > On Fri, Jul 28, 2017 at 1:30 AM, Noah Misch wrote: > > We've reached that period. If anyone is going to push for a change > > here, now is the time. Absent such arguments, the behavior won't change. > > Well, I started out believing that the current behavior was for the best, > and then completely reversed my position and favored the OP's proposal. > Nothing has really happened since then to change my mind, so I guess I'm > still in that camp. But do we have any new data points? Have any > beta-testers tested this and what do they think? > The only non-developer (i.e. person not living in an ivory tower) who has > weighed in here is Tels, who favored reversing the original decision and > adopting Tsunakawa-san's position, and that was 2 months ago. > > I am pretty reluctant to tinker with this at this late date and in the face > of several opposing votes, but I do think that we bet on the wrong horse. Sorry again, but how can we handle this? A non-PG-developer, Tels (and possibly someone else, IIRC) claimed that the behavior be changed during the beta period. Why should we do nothing? Regards Takayuki Tsunakawa -- 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] Process startup infrastructure is a mess
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Andres Freund > I think we should seriously consider doing a larger refactoring of this > soon. I've some ideas about what to do, but I'd welcome some thoughts on > whether others consider this a serious problem or not, and what they think > we should do about this, first. Thank you for raising this. I think so, too. Some other things that quickly come to mind are: * The signal handlers are similar in many processes and redundant. It's not easy to see how the processes respond to a particular signal and what signal can be used for new things such as dumping the stack trace in the server log. Maybe we can have an array of process attributes that specify the signal handlers, startup/shutdown order, etc. Then the common process management code handles processes based on the information in the array. * postmaster.c is very large (more than 6,000 lines) and hard to read. * It may be easy to forget adding initialization code in the Windows-specific path (SubPostmasterMaain). * It's confusing to find out which process counts toward max_connections, MaxBackends, the count of auxiliary processes. As a user, I'm sometimes confused about the relationship between max_connections, autovacuum_max_workers, max_wal_senders, max_worker_processes. Regards Takayuki Tsunakawa -- 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: [bug fix] PG10: libpq doesn't connect to alternative hosts when some errors occur
Hello Tom, Michael, Robert, Noah From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas > On Thu, Sep 14, 2017 at 3:23 AM, Tsunakawa, Takayuki > wrote: > > Sorry again, but how can we handle this? A non-PG-developer, Tels (and > possibly someone else, IIRC) claimed that the behavior be changed during > the beta period. Why should we do nothing? > > Because we do not have consensus on changing it. I've decided that you're > right, but several other people are saying "no". I can't just go change > it in the face of objections. How are things decided in cases like this? Does the RMT team usually do some kind of poll? So far, there are four proponents (Tels (non-PG-developer), David J., Robert and me), and two opponents (Tom and Michael). Regards Takayuki Tsunakawa -- 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] sync process names between ps and pg_stat_activity
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Peter Eisentraut > > Personally, I prefer "wal writer", "wal sender" and "wal receiver" > > that separate words as other process names. But I don't mind leaving > > them as they are now. > > If we were to change those, that would break existing queries for > pg_stat_activity. That's new in PG10, so we could change it if we were > really eager. But it's probably not worth bothering. Then again, there > is pg_stat_wal_receiver. So it's all totally inconsistent. Not sure > where to go. OK, I'm comfortable with as it is now. I made this ready for committer. You can fix the following and commit the patch. Thank you. > > * To achieve that, we pass "wal sender process" as username and > > username > > good catch Regards Takayuki Tsunakawa -- 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 10 release notes
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane > TBH, I think that's another reason for not release-noting it. There's no > concrete change to point to, and so it's hard to figure out what to say. > I'm not even very sure that we should be encouraging people to change > existing shared_buffers settings; the experiments that Takayuki-san did > were only on Windows 10, so we don't really know that changing that would > be a good idea on older Windows versions. In fact, when I ran pgbench on Windows 2008 for some other unrelated reason, I found larger shared buffers (4GB, 8GB) was effective, too. I didn't know pure documentation changes are not listed on the release notes. But I suggest listing them (of course, excluding mere typos), because the documentation is also important for users as well as programs. The documentation is also part of software product. If the documented behavior and the actual one differs and the user is wondering which is correct, he can know the answer only from the release note when the mismatch is fixed. I think the documentation changes are more useful for users than, for example, the following items: E.1.3.11. Source Code Improve behavior of pgindent (Piotr Stefaniak, Tom Lane) Allow WaitLatchOrSocket() to wait for socket connection on Windows (Andres Freund) Overhaul documentation build process (Alexander Lakhin) Use XSLT to build the PostgreSQL documentation (Peter Eisentraut) Regards Takayuki Tsunakawa -- 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] list of credits for release notes
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Peter Eisentraut > At the PGCon Developer Meeting it was agreed[0] to add a list of credits > to the release notes, including everyone who was mentioned in a commit > message. I have now completed that list. Thank you for your hard work. "Daisuke Higuchi" and "Higuchi Daisuke" are the same person (my colleague). Please use the former. Regards Takayuki Tsunakawa -- 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: Configurable file mode mask
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of David Steele > Sure, but having the private key may allow them to get new data from the > server as well as the data from the backup. You are right. My rough intent was that the data is stolen anyway. So, I thought it might not be so bad to expect to be able to back up the SSL key file in $PGDATA together with the database. If it's bad, then the default value of ssl_key_file (=$PGDATA/ssl.key) should be disallowed. > > https://www.postgresql.org/docs/devel/static/ssl-tcp.html > > > > "On Unix systems, the permissions on server.key must disallow any access > to world or group; achieve this by the command chmod 0600 server.key. > Alternatively, the file can be owned by root and have group read access > (that is, 0640 permissions). That setup is intended for installations where > certificate and key files are managed by the operating system. The user > under which the PostgreSQL server runs should then be made a member of the > group that has access to those certificate and key files." > > > > In the latter case, the file owner is root and the permission is 0640. > At first I was a bit confused and misunderstood that the PostgreSQL user > account and the backup OS user needs to belong to the same OS group. But > that's not the case. The group of the key file can be, for example, > "ssl_cert", the PostgreSQL user account belongs to the OS group "ssl_cert" > and "dba", and the backup OS user only belongs to "backup." This can prevent > the backup OS user from reading the key file. I think it would be better > to have some explanation with examples in the above section. > > If the backup user is in the same group as the postgres user and in the > ssl_cert group then backups of the certs would be possible using group reads. > Restores will be a little tricky, though, because of the need to set > ownership to root. The restore would need to be run as root or the > permissions fixed up after the restore completes. Yes, but I thought, from the following message, that you do not recommend that the backup user be able to read the SSL key file. So, I proposed to describe the example configuration to achieve that -- postgres user in dba and ssl_cert group, and a separate backup user in only dba group. > >> It seems to me that it would be best to advise in the docs that these > >> files should be relocated if they won't be readable by the backup user. > >> In any event, I'm not convinced that backing up server private keys > >> is a good idea. > To be clear, the default for this patch is to leave permissions exactly > as they are now. It also provides alternatives that may or not be useful > in all cases. So you think there are configurations that may be useful or not, don't you? Adding a new parameter could possibly complicate what users have to consider. Maximal flexibility could lead to insecure misuse. So I think it would be better to describe secure and practical configuration examples in the SSL section and/or the backup chapter. The configuration factor includes whether the backup user is different from the postgres user, where the SSL key file is placed, the owner of the SSL key file, whether the backup user can read the SSL key file. Regards Takayuki Tsunakawa -- 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: Configurable file mode mask
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of David Steele > > But it might be worth thinking about whether we want to encourage > > people to do manual chmod's at all; that's fairly easy to get wrong, > > particularly given the difference in X bits that should be applied to > > files and directories. Another approach that could be worth > > considering is a PGC_POSTMASTER GUC with just two states (group access > > or not) and make it the postmaster's responsibility to do the > > equivalent of chmod -R to make the file tree match the GUC. I think > > we do a tree scan anyway for other purposes, so correcting any wrong > > file permissions might not be much added work in the normal case. > > The majority of scanning is done in recovery (to find and remove unlogged > tables) and I'm not sure we would want to add that overhead to normal startup. I'm on David's side, too. I don't postmaster to always scan all files at startup. On the other hand, just doing "chmod -R $PGDATA" is not enough, because chmod doesn't follow the symbolic links. Symbolic links are used for pg_tblspc/* and pg_wal at least. FYI, MySQL's manual describes the pithole like this: https://dev.mysql.com/doc/refman/8.0/en/changing-mysql-user.html 2. Change the database directories and files so that user_name has privileges to read and write files in them (you might need to do this as the Unix root user): shell> chown -R user_name /path/to/mysql/datadir If you do not do this, the server will not be able to access databases or tables when it runs as user_name. If directories or files within the MySQL data directory are symbolic links, chown -R might not follow symbolic links for you. If it does not, you will also need to follow those links and change the directories and files they point to. I think we also need to describe the procedure carefully. That said, it would be best to make users aware of a configuration alternative (group access) with enough documentation when they first build the database or upgrade the database cluster. Just describing the alternative only in initdb reference page would result in being unaware of the better configuration, like --data-checksum. Regards Takayuki Tsunakawa -- 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] Defaulting psql to ON_ERROR_ROLLBACK=interactive
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Peter van > Hardenberg > I suggest we update the default of ON_ERROR_ROLLBACK to interactive for > 10.0. +1 Regards Takayuki Tsunakawa -- 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] Crash on promotion when recovery.conf is renamed
From: David Steele [mailto:da...@pgmasters.net] > Any idea when you'll have a chance to review? I'll do it by early next week. Regards Takayuki Tsunakawa -- 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: Make pg_stop_backup() archive wait optional
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of David Steele > The attached patch udpates the docs per your suggestion and has been rebased > on master at d69fae2. I made this ready for committer. The patch applied except for catversion.h, the patch content looks good, and the target test passed as follows: I set archive_command to 'sleep 10'. pg_stop_backup() with archive wait took about 10 seconds, emitting NOTICE messages. postgres=# select pg_stop_backup(false, true); NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to be archived NOTICE: pg_stop_backup complete, all required WAL segments have been archived pg_stop_backup --- (0/BF8,"START WAL LOCATION: 0/B28 (file 0001000B)+ CHECKPOINT LOCATION: 0/B60 + BACKUP METHOD: streamed + BACKUP FROM: master + START TIME: 2017-03-17 13:26:47 JST + LABEL: a + ","") (1 row) pg_stop_backup() without archive wait returned immediately without displaying any NOTICE messages. postgres=# select pg_stop_backup(false, false); pg_stop_backup --- (0/D000130,"START WAL LOCATION: 0/D28 (file 0001000D)+ CHECKPOINT LOCATION: 0/D60 + BACKUP METHOD: streamed + BACKUP FROM: master + START TIME: 2017-03-17 13:29:46 JST + LABEL: a + ","") (1 row) BTW, does the developer of each feature have to modify the catalog version in catversion.h? It's a bit annoying to see the patch application failure on catversion.h. Isn't it enough to modify the catalog version only when alpha/beta/RC/final versions are released? Regards Takayuki Tsunakawa -- 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: Make pg_stop_backup() archive wait optional
> From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier > On Fri, Mar 17, 2017 at 1:47 PM, Tsunakawa, Takayuki > wrote: > > BTW, does the developer of each feature have to modify the catalog version > in catversion.h? It's a bit annoying to see the patch application failure > on catversion.h. > > Committers take care of this part. I understood the committer modifies the catalog version based on the patch content, so the patch submitter doesn't have to modify it. I'm relieved. > > Isn't it enough to modify the catalog version only when > alpha/beta/RC/final versions are released? > > That's useful at least for developers to bump it even during a development > cycle as you can notice with a hard failure at startup if a data folder > you have created is compatible with the new binaries or not. Oh, you're absolutely right. Regards Takayuki Tsunakawa -- 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] Potential data loss of 2PC files
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Ashutosh Bapat > The scope of this work has expanded, since last time I reviewed and marked > it as RFC. Right now I am busy with partition-wise joins and do not have > sufficient time to take a look at the expanded scope. > However, I can come back to this after partition-wise join, but that may > stretch to the end of the commitfest. Sorry. I marked this as ready for committer. The code looks good, make check passed, ran read/write pgbench for some period to cause checkpoint with WAL file removal, and pg_ctl stop. The checkpoint emitted the following message, and there were no message like "could not ..." that indicates a file unlink or directory sync failure. LOG: checkpoint complete: wrote 1835 buffers (11.2%); 0 transaction log file(s) added, 1 removed, 0 recycled; write=0.725 s, sync=0.002 s, total=0.746 s; s\ ync files=9, longest=0.002 s, average=0.000 s; distance=16381 kB, estimate=16381 kB Regards Takayuki Tsunakawa -- 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: Make pg_stop_backup() archive wait optional
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tsunakawa, > Takayuki > I made this ready for committer. The patch applied except for catversion.h, > the patch content looks good, and the target test passed as follows: Sorry, I reverted this to waiting for author, because make check failed. src/test/regress/expected/opr_sanity.out seems to need revision because pg_stop_backup() was added. Regards Takayuki Tsunakawa -- 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: Make pg_stop_backup() archive wait optional
From: David Steele [mailto:da...@pgmasters.net] > Well, that's embarrassing. When I recreated the function to add defaults > I messed up the AS clause and did not pay attention to the results of the > regression tests, apparently. > > Attached is a new version rebased on 88e66d1. Catalog version bump has > also been omitted. I was late to reply because yesterday was a national holiday in Japan. I marked this as ready for committer. The patch applied cleanly and worked as expected. Regards Takayuki Tsunakawa -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Do we create a new roadmap page for development?
Hello, I'd like to share our roadmap for PostgreSQL development, as other companies and individuals do in the following page. But this page is for PostgreSQL 10. PostgreSQL10 Roadmap https://wiki.postgresql.org/wiki/PostgreSQL10_Roadmap Should I create a page for PostgreSQL 11 likewise? Or, do you want a more stable page named "PostgreSQL Roadmap" instead of "PostgreSQL11 Roadmap" and attach the target version to each feature as in "Feature X (V11)", so that we can represent more longer-term goals? And, why don't we add a link to the above roadmap page in the "Development information" page? Development information https://wiki.postgresql.org/wiki/Development_information Regards Takayuki Tsunakawa -- 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] Do we create a new roadmap page for development?
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas > On Tue, Mar 21, 2017 at 2:36 AM, Tsunakawa, Takayuki > wrote: > > Should I create a page for PostgreSQL 11 likewise? Or, do you want a > more stable page named "PostgreSQL Roadmap" instead of "PostgreSQL11 > Roadmap" and attach the target version to each feature as in "Feature X > (V11)", so that we can represent more longer-term goals? > > I think a page that's just called PostgreSQL Roadmap will get out of date > pretty quickly. Ultimately it'll end up like the TODO list, which is not > really a list of things TO DO. The advantage of the version-specific pages > is that old information kind of ages its way out of the system. Maybe so. I created a page for PostgreSQL 11 and add a link to our roadmap: https://wiki.postgresql.org/wiki/PostgreSQL11_Roadmap Please add your roadmaps when you can. > > And, why don't we add a link to the above roadmap page in the "Development > information" page? > > > > Development information > > https://wiki.postgresql.org/wiki/Development_information > > I'm sure nobody will object to you doing that. It's a wiki, and intended > to be edited. Thanks, done. Also, I moved the existing the link to "Development projects" from " Past PgCon Developer Meeting Notes" to the new header "Roadmaps and Projects", because the development projects page doesn't appear to fit the PGCON notes. Regards Takayuki -- 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] Crash on promotion when recovery.conf is renamed
From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> Moved to CF 2017-03. Both patches still apply.
Sorry to be late for reviewing this, but done now. The patch applied, make
check passed, and the code looks almost good. I could successfully perform a
simple archive recovery. Finally, I broke the 2pc state file while the server
is down, and could confirm that the server failed to start as expected,
emitting a FATAL message. Worked nicely.
Just two cosmetic points:
(1)
Other places use "two-phase state file", not "two-phase file".
(2)
All other places in twophase.c and most places in other files put ereport() and
errmsg() on separate lines. I think it would be better to align with
surrounding code.
+ ereport(FATAL, (errmsg("corrupted two-phase
file \"%s\"",
Regards
Takayuki Tsunakawa
--
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] Potential data loss of 2PC files
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier > Do you think that this qualifies as a bug fix for a backpatch? I would think > so, but I would not mind waiting for some dust to be on it before considering > applying that on back-branches. Thoughts from others? I think so, too. As change from rename() to durable_rename() was applied to all releases, maybe we can follow that. In addition, keeping code differences as few as possible would make it easier for committers to apply other bug fixes to all versions. Regards Takayuki Tsunakawa -- 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] Crash on promotion when recovery.conf is renamed
From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane
> "Tsunakawa, Takayuki" writes:
> > All other places in twophase.c and most places in other files put ereport()
> and errmsg() on separate lines. I think it would be better to align with
> surrounding code.
>
> > + ereport(FATAL, (errmsg("corrupted
> two-phase file \"%s\"",
>
> Actually, the *real* problem with that coding is it lacks a SQLSTATE (errcode
> call). The only places where it's acceptable to leave that out are for
> internal "can't happen" cases, which this surely isn't.
Oh, I overlooked it. But...
> Yup. Just remember that the default is
> XX000EERRCODE_INTERNAL_ERROR internal_error
>
> If that's not how you want the error case reported, you need an errcode()
> call.
>
> We might need more ERRCODEs than are there now, if none of the existing
> ones seem to fit these cases. There's already ERRCODE_DATA_CORRUPTED and
> ERRCODE_INDEX_CORRUPTED; maybe we need ERRCODE_WAL_CORRUPTED, for
> example?
I'd be always happy if the error code is more specific, but maybe that would be
a separate patch. WAL corruption message so far doesn't accompany a specific
error code like this in xlog.c:
/*
* We only end up here without a message when
XLogPageRead()
* failed - in that case we already logged something. In
* StandbyMode that only happens if we have been
triggered, so we
* shouldn't loop anymore in that case.
*/
if (errormsg)
ereport(emode_for_corrupt_record(emode,
RecPtr ? RecPtr : EndRecPtr),
(errmsg_internal("%s", errormsg) /* already
translated */ ));
Regards
Takayuki Tsunakawa
--
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 How To Shorten the Steep Learning Curve Towards PG Hacking...
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Kang Yuzhe > 1. Prepare Hands-on with PG internals > > > For example, a complete Hands-on with SELECT/INSERT SQL Standard PG > internals. The point is the experts can pick one fairly complex feature > and walk it from Parser to Executor in a hands-on manner explaining step > by step every technical detail. I sympathize with you. What level of detail do you have in mind? The query processing framework is described in the manual: Chapter 50. Overview of PostgreSQL Internals https://www.postgresql.org/docs/devel/static/overview.html More detailed source code analysis is provided for very old PostgreSQL 7.4, but I guess it's not much different now. The document is in Japanese only: http://ikubo.x0.com/PostgreSQL/pg_source.htm Are you thinking of something like this? MySQL Internals Manual https://dev.mysql.com/doc/internals/en/ Regards Takayuki Tsunakawa -- 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] Crash on promotion when recovery.conf is renamed
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier > While I agree with that in general, we are taking about 2PC files that are > on disk in patch 0001, so I would think that in this case > ERRCODE_DATA_CORRUPTED is the most adapted (or > ERRCODE_TWOPHASE_CORRUPTED?). > > The other WARNING messages refer to stale files of already committed > transactions, which are not actually corrupted. What about in this case > having a ERRCODE_TWOPHASE_INVALID? > > Updated patches are attached, I did not change the WARNING portion though > as I am not sure what's the consensus on the matter. > I get the impression that DATA_CORRUPTED means the table data is corrupted, because there's an error code named INDEX_CORRUPTED. Anyway, I don't think this patch needs to attach an error code because: * Currently, other interlal files (not tables or indexes) seem to use INTERNAL_ERROR (XX000). For example, see ReadControlFile() in xlog.c and pgstat_read_statsfiles() in pgstat.c. * It doesn't seem that the user needs distinction. I don't object to providing a specific error code for this case, but if the patch needs a specific error code to be committed, I'd like to know how that's useful (e.g. how it affects the recovery action the user takes.) So, I'd like to mark the patch as ready for committer when ereport() and errmsg() are on separate lines and the message changes to "two-phase state file." Regards Takayuki Tsunakawa -- 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] Crash on promotion when recovery.conf is renamed
From: Michael Paquier [mailto:michael.paqu...@gmail.com] > Okay. I got the message, and I agree with what you say here. You are right > by the way, the error messages just use "two-phase file" and not "two-phase > STATE file", missed that previously. > -- Thanks, marked as ready for committer, as the code is good and Alexander reported the test success. Regards Takayuki Tsunakawa -- 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] Supporting huge pages on Windows
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of David Steele > It's not clear to me what state this patch should be in. Is there more > review that needs to be done or is it ready for a committer? I would most appreciate it if Magnus could review the code, because he gave me the most critical comment that the value of huge_page variable should not be changed on the fly, and I did so. I hope that will be the final review. Regards Takayuki Tsunakawa -- 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] Allow interrupts on waiting standby
Hi, Michael,
From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> What do you think about the updated version attached?
I reviewed this patch. Here are some comments and questions:
(1) monitoring.sgml
The new row needs to be placed in the "Timeout" group. The current patch puts
the row at the end of IO group. Please insert the new row according to the
alphabetical order.
In addition, "morerows" attribute of the following line appears to be increased.
Timeout
By the way, doesn't this wait event belong to IPC wait event type, because the
process is waiting for other conflicting processes to terminate the conflict
conditions? Did you choose Timeout type because max_standby_{archive |
streaming}_delay relates to this wait? I'm not confident which is appropriate,
but I'm afraid users can associate this wait with a timeout.
(2) standby.c
Do we need to specify WL_LATCH_SET? Who can set the latch? Do the backends
who ended the conflict set the latch?
(3) standby.c
+ if (rc & WL_LATCH_SET)
+ ResetLatch(MyLatch);
+
+ /* emergency bailout if postmaster has died */
+ if (rc & WL_POSTMASTER_DEATH)
+ proc_exit(1);
I thought the child processes have to terminate as soon as postmaster vanishes.
So, it would be better for the order of the two if statements above to be
reversed. proc_exit() could be exit(), as some children do in postmaster/*.c.
(4) standby.c
The latch is not reset when the wait timed out. The next WaitLatch() would
return immediately.
Regards
Takayuki Tsunakawa
--
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] Allow interrupts on waiting standby
(4) standby.c > The latch is not reset when the wait timed out. The next WaitLatch() would > return immediately. Sorry, let me withdraw this. This is my misunderstanding. OTOH, when is the latch reset before the wait? Is there an assumption that MyLatch has been in reset state since it was initialized? Is it safe to use MyLatch here, not the special latch like something used in xlog.c? Regards Takayuki Tsunakawa -- 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] Allow interrupts on waiting standby
From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> > By the way, doesn't this wait event belong to IPC wait event type, because
> the process is waiting for other conflicting processes to terminate the
> conflict conditions? Did you choose Timeout type because
> max_standby_{archive | streaming}_delay relates to this wait? I'm not
> confident which is appropriate, but I'm afraid users can associate this
> wait with a timeout.
>
> Actually I think that this event belongs to the timeout category, because
> we wait until the timeout has finished, the latch being here to be sure
> that the process is more responsive in case of a postmaster death.
OK. I confirmed the doc is fixed.
> > (2) standby.c
> > Do we need to specify WL_LATCH_SET? Who can set the latch? Do the
> backends who ended the conflict set the latch?
>
> This makes the process able to react on SIGHUP. That's useful for
> responsiveness.
Oh, I see. But how does the startup process respond quickly? It seems that
you need to call HandleStartupProcInterrupts() instead of
CHECK_FOR_INTERRUPTS(). But I'm not sure whether HandleStartupProcInterrupts()
can be called here.
> > (3) standby.c
> > + if (rc & WL_LATCH_SET)
> > + ResetLatch(MyLatch);
> > +
> > + /* emergency bailout if postmaster has died */
> > + if (rc & WL_POSTMASTER_DEATH)
> > + proc_exit(1);
> >
> > I thought the child processes have to terminate as soon as postmaster
> vanishes. So, it would be better for the order of the two if statements
> above to be reversed. proc_exit() could be exit(), as some children do
> in postmaster/*.c.
>
> OK, reversed this order.
I think exit() instead of proc_exit() better represents what the code wants to
do -- terminate the process ASAP without cleaning up. Many other background
children do so.
Regards
Takayuki Tsunakawa
--
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] Allow interrupts on waiting standby
Hi Michael, Simon,
From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> > Oh, I see. But how does the startup process respond quickly? It seems
> that you need to call HandleStartupProcInterrupts() instead of
> CHECK_FOR_INTERRUPTS(). But I'm not sure whether
> HandleStartupProcInterrupts() can be called here.
>
> Bah. Of course you are right. We don't care about SetLatch() here as signals
> are processed with a different code path than normal backends.
So, I understood you agreed that CHECK_FOR_INTERRUPTS() here does nothing. But
your patch still calls it:
+ /* An interrupt may have occurred while waiting */
+ CHECK_FOR_INTERRUPTS();
I got confused because the problem is not defined in this thread. What problem
does this patch address? These ones?
* The startup process terminates as soon as postmaster dies.
* pg_stat_activity does not show the wait event of startup process waiting for
a recovery conflict resolution.
My guess about why you decided to not call HandleStartupProcInterrupts() here
is:
* Respond to postmaster death here.
* No need to reload config file here because there's no parameter to affect
this conflict wait. But max_standby_{archive | streaming}_delay seems to
affect the wait period.
* No need to handle SIGTERM and exit here, because the startup process doesn't
wait for a conflict resolution here when he can terminate.
I think you can call HandleStartupProcInterrupts() here, instead of checking
postmaster death. Did you perform tests?
Did Simon's committed patch solve the problem as expected?
Regards
Takayuki Tsunakawa
--
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] Supporting huge pages on Windows
From: Amit Kapila [mailto:amit.kapil...@gmail.com]
> The latest patch looks good to me apart from one Debug message, so I have
> marked it as Ready For Committer.
Thank you so much!
> + ereport(DEBUG1,
> + (errmsg("disabling huge pages")));
>
> I think this should be similar to what we display in sysv_shmem.c as below:
>
> elog(DEBUG1, "mmap(%zu) with MAP_HUGETLB failed, huge pages disabled: %m",
> allocsize);
I understood you suggested this to make the reason clear for disabling huge
pages. OK, done as follows.
+ elog(DEBUG1, "CreateFileMapping(%llu) with SEC_LARGE_PAGES
failed "
+"due to insufficient large pages, huge pages disabled",
+size);
I hope this will be committed soon.
Regards
Takayuki Tsunakawa
win_large_pages_v10.patch
Description: win_large_pages_v10.patch
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [bug fix] Savepoint-related statements terminates connection
Hello, I found a trivial bug that terminates the connection. The attached patch fixes this. PROBLEM Savepoint-related statements in a multi-command query terminates the connection unexpectedly, as follows. $ psql -d postgres -c "SELECT 1; SAVEPOINT sp" FATAL: DefineSavepoint: unexpected state STARTED server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost CAUSE 1. In exec_simple_query(), isTopLevel is set to false. isTopLevel = (list_length(parsetree_list) == 1); Then it is passed to PortalRun(). (void) PortalRun(portal, FETCH_ALL, isTopLevel, receiver, receiver, completionTag); 2. The isTopLevel flag is passed through ProcessUtility() to RequireTransactionChain(). RequireTransactionChain(isTopLevel, "SAVEPOINT"); 3. CheckTransactionChain() returns successfully here: /* * inside a function call? */ if (!isTopLevel) return; 4. Finally, unexpectedly called DefineSavepoint() reports invalid transaction block state. /* These cases are invalid. */ case TBLOCK_DEFAULT: case TBLOCK_STARTED: ... elog(FATAL, "DefineSavepoint: unexpected state %s", BlockStateAsString(s->blockState)); SOLUTION The manual page says "Savepoints can only be established when inside a transaction block." So just check for it. https://www.postgresql.org/docs/devel/static/sql-savepoint.html RESULT AFTER FIX $ psql -d postgres -c "SELECT 1; SAVEPOINT sp" ERROR: SAVEPOINT can only be used in transaction blocks Regards Takayuki Tsunakawa savept-in-multi-cmd.patch Description: savept-in-multi-cmd.patch -- 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] Allow interrupts on waiting standby
From: Michael Paquier [mailto:michael.paqu...@gmail.com]
> Oops, sorry for that, I quite mess up with this patch. The WaitLatch() call
> should still have WL_POSTMASTER_DEATH so as it can leave earlier, but yes
> I agree with your analysis that HandleStartupProcInterrupts() as this is
> part of the redo work.
Thank you, but did you remove WL_LATCH_SET from WaitLatch() intentionally? I
understood you added it for startup process to respond quickly to events other
than the postmaster death. Why don't we restore WL_LATCH_SET? I won't object
to not adding the flag if there's a reason.
I'll mark this as ready for committer when I see WL_LATCH_SET added (optional)
and you have reported that you did the following test cases:
* Startup process vanishes immediately after postmaster dies, while it is
waiting for a recovery conflict to be resolved.
* Startup process vanishes immediately after "pg_ctl stop -m fast", while it is
waiting for a recovery conflict to be resolved.
* Startup process resumes WAL application when max_standby_{archive |
streaming}_delay is changed from the default -1 to a short period, e.g. 10s,
and "pg_ctl reload" is performed, while it is waiting for a recovery conflict
to be resolved.
> > Did Simon's committed patch solve the problem as expected?
>
> Does not seem so, I'll let Simon comment on this matter...
Agreed. I guess his patch for earlier releases should work if
CHECK_FOR_INTERRUPTS() is replaced with HandleStartupProcInterrupts().
Regards
Takayuki Tsunakawa
--
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] Supporting huge pages on Windows
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Amit Kapila > You should use %zu instead of %llu to print Size type of variable. I did so at first, but it didn't work with Visual Studio 2010 at hand. It doesn't support %z which is added in C99. But "I" (capital i) instead of "ll" seems appropriate as the following page says. I chose this. https://en.wikipedia.org/wiki/Printf_format_string I For signed integer types, causes printf to expect ptrdiff_t-sized integer argument; for unsigned integer types, causes printf to expect size_t-sized integer argument. Commonly found in Win32/Win64 platforms. Regards Takayuki Tsunakawa win_large_pages_v11.patch Description: win_large_pages_v11.patch -- 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 fix] Savepoint-related statements terminates connection
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Alvaro Herrera > Ashutosh Bapat wrote: > > Please add this to the next commitfest. > > If this cannot be reproduced in 9.6, then it must be added to the Open Items > wiki page instead. I added this in next CF. Regards Takayuki Tsunakawa -- 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] Supporting huge pages on Windows
From: Michael Paquier [mailto:michael.paqu...@gmail.com] > Amit is right, you had better use %zu as we do in all the other places of > the code dealing with Size variables that are printed. When compiling with > Visual Studio, Postgres falls back to the implementation of sprintf in > src/port/snprintf.c so that's not something to worry about. Thanks, fixed. Regards Takayuki Tsunakawa win_large_pages_v12.patch Description: win_large_pages_v12.patch -- 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] Statement timeout behavior in extended queries
Hello,
I've reviewed the patch. My understanding is as follows. Please correct me if
I'm wrong.
* The difference is that the Execute message stops the statement_timeout timer,
so that the execution of one statement doesn't shorten the timeout period of
subsequent statements when they are run in batch followed by a single Sync
message.
* This patch is also necessary (or desirable) for the feature "Pipelining/batch
mode support for libpq," which is being developed for PG 10. This patch
enables correct timeout handling for each statement execution in a batch.
Without this patch, the entire batch of statements is subject to
statement_timeout. That's different from what the manual describes about
statement_timeout. statement_timeout should measure execution of each
statement.
Below are what I found in the patch.
(1)
+static bool st_timeout = false;
I think the variable name would better be stmt_timeout_enabled or
stmt_timer_started, because other existing names use stmt to abbreviate
statement, and thhis variable represents a state (see xact_started for example.)
(2)
+static void enable_statement_timeout(void)
+{
+static void disable_statement_timeout(void)
+{
"static void" and the remaining line should be on different lines, as other
functions do.
(3)
+ /*
+* Sanity check
+*/
+ if (!xact_started)
+ {
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+errmsg("Transaction must have
been already started to set statement timeout")));
+ }
I think this fragment can be deleted, because enable/disable_timeout() is used
only at limited places in postgres.c, so I don't see the chance of misuse.
Regards
Takayuki Tsunakawa
--
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] wait event documentation
From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas
> Instead of continuing to repair this every time it gets broken, I propose
> that we break this into one table that lists all the wait_event_type values
> -- LWLock, Lock, BufferPin, Activity, Client, Extension, IPC, Timeout, and
> IO -- and then a second table for each type that has multiple wait events
> listing all of the wait events for that type.
>
> I also propose hoisting this out of section 28.2.3 - Viewing Statistics
> - and making it a new toplevel section of chapter 28. So between the current
> "28.3 Viewing Locks" and the current "28.4 Progress Reporting" we'd add
> a new section "Wait Events" and link to that from 28.2.3. That would also
> give us a place to include any general text that we want to have regarding
> wait events, apart from the tables.
+1
I'm visually impaired and using screen reader software which reads text by
Synthetic speech. It was not easy for me to navigate a large table to find the
first row of a particular wait event type. With your proposal, I'll be able to
move among tables with a single key press ("T" and "Shift + T".) I'd also be
happy about separating the section for wait events as you propose, because
navigating in a long section is cumbersome.
Regards
Takayuki Tsunakawa
--
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] Statement timeout behavior in extended queries
From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii
> No. Parse, bind and Execute message indivually stops and starts the
> statement_timeout timer with the patch. Unless there's a lock conflict,
> parse and bind will take very short time. So actually users could only
> observe the timeout in execute message though.
Where is the statement_timeout timer stopped when processing Parse and Bind
messages? Do you mean the following sequence of operations are performed in
this patch?
Parse(statement1)
start timer
stop timer
Bind(statement1, portal1)
start timer
stop timer
Execute(portal1)
start timer
stop timer
Sync
It looks like the patch does the following. I think this is desirable, because
starting and stopping the timer for each message may be costly as Tom said.
Parse(statement1)
start timer
Bind(statement1, portal1)
Execute(portal1)
stop timer
Sync
> > (3)
> > + /*
> > +* Sanity check
> > +*/
> > + if (!xact_started)
> > + {
> > + ereport(ERROR,
> > +
> (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> > +errmsg("Transaction
> must have been already started to set statement timeout")));
> > + }
> >
> > I think this fragment can be deleted, because enable/disable_timeout()
> is used only at limited places in postgres.c, so I don't see the chance
> of misuse.
>
> I'd suggest leave it as it is. Because it might be possible that the function
> is used in different place in the future. Or at least we should document
> the pre-condition as a comment.
OK, I favor documenting to reduce code, but I don't have a strong opinion. I'd
like to leave this to the committer. One thing to note is that you can remove
{ and } in the following code like other places.
+ if (!xact_started)
+ {
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+errmsg("Transaction must have
been already started to set statement timeout")));
+ }
Regards
Takayuki Tsunakawa
--
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] Statement timeout behavior in extended queries
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii > Actually the statement timer is replaced with new statement timer value > in enable_statement_timeout(). The patch doesn't seem to behave like that. The Parse message calls start_xact_command() -> enable_statement_timeout() -> enable_timeout(), and set stmt_timer_started to true. Subsequent Bind and Execute messages call enable_statement_timeout(), but enable_statement_timeout() doesn't call enable_timeout() because stmt_timer_started is already true. > > It looks like the patch does the following. I think this is desirable, > because starting and stopping the timer for each message may be costly as > Tom said. > > Parse(statement1) > > start timer > > Bind(statement1, portal1) > > Execute(portal1) > > stop timer > > Sync I ran one INSERT statement using JDBC with log_min_messages = DEBUG3, and the server log shows what I said. DEBUG: parse : insert into a values(2) DEBUG: Set statement timeout LOG: duration: 0.431 ms parse : insert into a values(2) DEBUG: bind to LOG: duration: 0.127 ms bind : insert into a values(2) DEBUG: Disable statement timeout LOG: duration: 0.184 ms execute : insert into a values(2) DEBUG: snapshot of 1+0 running transaction ids (lsn 0/163BF28 oldest xid 561 latest complete 560 next xid 562) > This doesn't work in general use cases. Following pattern appears frequently > in applications. > > Parse(statement1) > Bind(statement1, portal1) > Execute(portal1) > Bind(statement1, portal1) > Execute(portal1) > : > : > Sync It works. The first Parse-Bind-Execute is measured as one unit, then subsequent Bind-Execute pairs are measured as other units. That's because each Execute ends the statement_timeout timer and the Bind message starts it again. I think this is desirable, so the current patch looks good. May I mark this as ready for committer? FYI, make check-world passed successfully. > Also what would happen if client just send a parse message and does nothing > after that? It's correct to trigger the statement timeout in this case, because the first SQL statement started (with the Parse message) and its execution is not finished (with Execute message.) Regards Takayuki Tsunakawa -- 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] Supporting huge pages on Windows
From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Andres Freund
> I don't think the errdetail is quite right - OpenProcessToken isn't really
> a syscall, is it? But then it's a common pattern already in wind32_shmem.c...
Yes, "Win32 API function" would be correct, but I followed the existing code...
> > +errdetail("Failed system call was %s.",
> > +"LookupPrivilegeValue")));
>
> Other places in the file actually log the arguments to the function...
The only place is CreateFileMapping. Other places (DuplicateHandle and
MapViewOfFileEx) don't log arguments. I guess the original developer thought
that size and name arguments to CreateFileMapping() might be useful for
troubleshooting.
> Wonder if we should quote "Lock Pages in Memory" or add dashes, to make
> sure it's clear that that's the right?
I saw several Microsoft pages, including a page someone introduced me here, and
they didn't quote the user right. I'm comfortable with the current code, but I
don't mind if the committer adds some quotation.
> > + flProtect = PAGE_READWRITE | SEC_COMMIT |
> SEC_LARGE_PAGES;
>
> Why don't we just add the relevant flag, instead of overwriting the previous
> contents?
I don't have strong opinion on this...
> Uh - isn't that a behavioural change? Was this discussed?
Yes, I described this in the first mail. Magnus asked about this later and I
replied.
Regards
Takayuki Tsunakawa
--
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] Statement timeout behavior in extended queries
From: Tatsuo Ishii [mailto:is...@sraoss.co.jp] > It's too late. Someone has already moved the patch to the next CF (for > PostgreSQL 11). Yes, but this patch will be necessary by the final release of PG 10 if the libpq batch/pipelining is committed in PG 10. I marked this as ready for committer in the next CF, so that some committer can pick up this patch and consider putting it in PG 10. If you decide to modify the patch, please change the status. Regards Takayuki Tsunakawa -- 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] Statement timeout behavior in extended queries
From: Andres Freund [mailto:and...@anarazel.de] Given the concern raised in > http://archives.postgresql.org/message-id/12207.1491228316%40sss.pgh.p > a.us > I don't see this being ready for committer. If what Tatsuo-san said to Tom is correct (i.e. each Parse/Bind/Execute starts and stops the timer), then it's a concern and the patch should not be ready for committer. However, the current patch is not like that -- it seems to do what others in this thread are expecting. Regards Takayuki Tsunakawa -- 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] Statement timeout behavior in extended queries
From: Andres Freund [mailto:and...@anarazel.de] > Looks to me like npgsql doesn't do that either. None of libpq, pgjdbs and > npgsql doing it seems like some evidence that it's ok. And psqlODBC now uses always libpq. Now time for final decision? Regards Takayuki Tsunakawa -- 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] Statement timeout behavior in extended queries
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii > Hmm. IMO, that could happen even with the current statement timeout > implementation as well. > > Or we could start/stop the timeout in exec_execute_message() only. This > could avoid the problem above. Also this is more consistent with > log_duration/log_min_duration_statement behavior than now. I think it's better to include Parse and Bind as now. Parse or Bind could take long time when the table has many partitions, the query is complex and/or very long, some DDL statement is running against a target table, or the system load is high. Firing statement timeout during or after many Parses is not a problem, because the first Parse started running some statement and it's not finished. Plus, Andres confirmed that major client drivers don't use such a pattern. There may be an odd behavior purely from the perspective of E.Q.P, that's a compromise, which Andres meant by "not perfect but." Regards Takayuki Tsunakawa -- 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] Supporting huge pages on Windows
From: Craig Ringer [mailto:craig.rin...@2ndquadrant.com]
> TBH, anyone who cares about security and runs Win7 or Win2k8 or newer should
> be using virtual service accounts and managed service accounts.
>
> https://technet.microsoft.com/en-us/library/dd548356
>
>
> Those are more like Unix service accounts. Notably they don't need a password,
> getting rid of some of the management pain that led us to abandon the
> 'postgres' system user on Windows.
>
> Now that older platforms are EoL and even the oldest that added this feature
> are also near EoL or in extended maintenance, I think installers should
> switch to these by default instead of using NETWORK SERVICE.
>
> Then the issue of priv dropping would be a lesser concern anyway.
Good point! And I said earlier in this thread, I think managing privileges
(adding/revoking privileges from the user account) is the DBA's or sysadmin's
duty, and PG's removing all privileges feels overkill.
OTOH, I tried again to leave the DISABLE_MAX_PRIVILEGE as is and add Lock Pages
in Memory, using the attached pg_ctl.c. Please see EnableLockPagesPrivilege()
and its call site. But pg_ctl -w start fails emitting the following message:
error code 1300
waiting for server to startFATAL: could not enable "Lock pages in memory"
privilege
HINT: Assign "Lock pages in memory" privilege to the Windows user account
which runs PostgreSQL.
LOG: database system is shut down
stopped waiting
pg_ctl: could not start server
Examine the log output.
error code 1300 is ERROR_NOT_ALL_ASSIGNED, which means AdjustTokenPrivileges()
cound not enable Lock Pages in Memory privilege. It seems that the privilege
cannot be enabled once it was removed with
CreateRestrictedToken(DISABLE_MAX_PRIVILEGE).
Regards
Takayuki Tsunakawa
/*-
*
* pg_ctl --- start/stops/restarts the PostgreSQL server
*
* Portions Copyright (c) 1996-2016, PostgreSQL Global Development Group
*
* src/bin/pg_ctl/pg_ctl.c
*
*-
*/
#ifdef WIN32
/*
* Need this to get defines for restricted tokens and jobs. And it
* has to be set before any header from the Win32 API is loaded.
*/
#define _WIN32_WINNT 0x0501
#endif
#include "postgres_fe.h"
#include "libpq-fe.h"
#include "pqexpbuffer.h"
#include
#include
#include
#include
#include
#include
#include
#include
#ifdef HAVE_SYS_RESOURCE_H
#include
#include
#endif
#include "getopt_long.h"
#include "miscadmin.h"
/* PID can be negative for standalone backend */
typedef long pgpid_t;
typedef enum
{
SMART_MODE,
FAST_MODE,
IMMEDIATE_MODE
} ShutdownMode;
typedef enum
{
NO_COMMAND = 0,
INIT_COMMAND,
START_COMMAND,
STOP_COMMAND,
RESTART_COMMAND,
RELOAD_COMMAND,
STATUS_COMMAND,
PROMOTE_COMMAND,
KILL_COMMAND,
REGISTER_COMMAND,
UNREGISTER_COMMAND,
RUN_AS_SERVICE_COMMAND
} CtlCommand;
#define DEFAULT_WAIT60
static bool do_wait = false;
static bool del_service_rid = false;
static bool wait_set = false;
static int wait_seconds = DEFAULT_WAIT;
static bool wait_seconds_arg = false;
static bool silent_mode = false;
static ShutdownMode shutdown_mode = FAST_MODE;
static int sig = SIGINT; /* default */
static CtlCommand ctl_command = NO_COMMAND;
static char *pg_data = NULL;
static char *pg_config = NULL;
static char *pgdata_opt = NULL;
static char *post_opts = NULL;
static const char *progname;
static char *log_file = NULL;
static char *exec_path = NULL;
static char *event_source = NULL;
static char *register_servicename = "PostgreSQL"; /* FIXME: +
version ID? */
static char *register_username = NULL;
static char *register_password = NULL;
static char *argv0 = NULL;
static bool allow_core_files = false;
static time_t start_time;
static char postopts_file[MAXPGPATH];
static char version_file[MAXPGPATH];
static char pid_file[MAXPGPATH];
static char backup_file[MAXPGPATH];
static char recovery_file[MAXPGPATH];
static char promote_file[MAXPGPATH];
#ifdef WIN32
static DWORD pgctl_start_type = SERVICE_AUTO_START;
static SERVICE_STATUS status;
static SERVICE_STATUS_HANDLE hStatus = (SERVICE_STATUS_HANDLE) 0;
static HANDLE shutdownHandles[2];
static pid_t postmasterPID = -1;
#define shutdownEvent shutdownHandles[0]
#define postmasterProcess shutdownHandles[1]
#endif
static void write_stderr(const char *fmt,...) pg_attribute_printf(1, 2);
static void do_advice(void);
static void do_help(void);
static void set_mode(char *modeopt);
static void set_sig(char *signame);
static void do_init(void);
static void do_start(void);
static void do_stop(void);
static void do_restart(void);
static void do_reload(void);
static void do_status(void);
static void do_promote(void);
static void do_kill(pgpid_t pid);
static void print_msg(const char *msg);
static void adjust_data
Re: [HACKERS] Supporting huge pages on Windows
From: Craig Ringer [mailto:craig.rin...@2ndquadrant.com] > On 5 April 2017 at 10:37, Tsunakawa, Takayuki > wrote: > > OTOH, I tried again to leave the DISABLE_MAX_PRIVILEGE as is and add Lock > Pages in Memory, using the attached pg_ctl.c. Please see > EnableLockPagesPrivilege() and its call site. But pg_ctl -w start fails > emitting the following message: > > That won't work. You'd have to pass 0 to the flags of CreateRestrictedToken > and instead supply a PrivilegesToDelete array. > You'd probably GetTokenInformation and AND with a mask of ones you wanted > to retain. Uh, that's inconvenient. We can't determine what privileges to delete, and we must be aware of new privileges added in the future version of Windows. Then, I have to say the last patch (v12) is the final answer. Regards Takayuki Tsunakawa -- 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] Statement timeout behavior in extended queries
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of 'Andres Freund' > Attached. I did not like that the previous patch had the timeout handling > duplicated in the individual functions, I instead centralized it into > start_xact_command(). Given that it already activated the timeout in the > most common cases, that seems to make more sense to me. In your version > we'd have called enable_statement_timeout() twice consecutively (which > behaviourally is harmless). > > What do you think? I've not really tested this with the extended protocol, > so I'd appreciate if you could rerun your test from the older thread. The patch looks good and cleaner. It looks like the code works as expected. As before, I ran one INSERT statement with PgJDBC, with gdb's breakpoints set on enable_timeout() and disable_timeout(). I confirmed that enable_timeout() is called just once at Parse message, and disable_timeout() is called just once at Execute message. I'd like to wait for Tatsuo-san's thorough testing with pgproto. Regards Takayuki Tsunakawa -- 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] Statement timeout behavior in extended queries
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii > I have done tests using pgproto. One thing I noticed a strange behavior. > Below is an output of pgproto. The test first set the timeout to 3 seconds, > and parse/bind for "SELECT pg_sleep(2)" then set timeout to 1 second using > extended query. Subsequent Execute emits a statement timeout error as > expected, but next "SELECT pg_sleep(2)" > call using extended query does not emit a statement error. The test for > this is "007-timeout-twice". Attached is the test cases including this. What's the handling of transactions like in pgproto? I guess the first statement timeout error rolled back the effect of "SET statement_timeout = '1s'", and the timeout reverted to 3s or some other value. Regards Takayuki Tsunakawa -- 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] Statement timeout behavior in extended queries
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii > Since pgproto is a dumb protocol machine, it does not start a transaction > automatically (user needs to explicitly send a start transaction command > via either simple or extended query). In this particular case no explicit > transaction has started. > Then, the following sequence should have occurred. The test result is valid. # Execute statement which takes 2 seconds. 'P' "S1""SELECT pg_sleep(2)"0 -> start transaction T1 'B' "S2""S1"0 0 0 'P' "" "SET statement_timeout = '1s'" 0 'B' "" "" 0 0 0 'E' "" 0 # Execute statement which takes 2 seconds (statement timeout expected). 'E' "S2"0 -> timeout error occurred, T1 aborted # Issue Sync message 'S' -> rolled back T1, so statement_timeout reverted to 3s # Receive response from backend 'Y' # Execute statement which takes 2 seconds (statement timeout expected). 'P' "S3""SELECT pg_sleep(2)"0 -> start transaction T2 'B' "S2""S3"0 0 0 'E' "S2"0 # Issue Sync message 'S' -> committed T2 Regards Takayuki Tsunakawa -- 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] Supporting huge pages on Windows
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Andres Freund > As I asked before, why can't we delete all privs and add the explicitly > needed once back (using AdjustTokenPrivileges)? I tried it with pg_ctl.c attached to an earlier mail today, i.e. delete all privs with CreateRestrictedToken(DISABLE_ALL_PRIVILEGE) and enable Lock Pages in Memory with AdjustTokenPrivileges(). But it didn't work; AdjustTokenPrivileges() failed to enable the priv. It's probably that CreateRestrictedToken() deletes (unassigns?) the privs from the access token, so subsequent AdjustTokenPrivileges() can no longer enable the priv. Regards Takayuki Tsunakawa -- 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] Supporting huge pages on Windows
Hello, Magnus cc: Andres From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Magnus Hagander > I think what you'd need to do is enumerate what privileges the user has > *before* calling CreateRestrictedToken(), using GetTokenInformation(). > And then pass those into PrivilegesToDelete (except for > SeChangeNotifyPrivilege) in the call to CreateRestrictedToken(), instead > of using DISABLE_MAX_PRIVILEGE. (and add the privilege needed for huge pages > before you start that whole process -- that needs to be added in the token > used *before* we create the restricted one). > > At least that's my guess from reading the docs and trying to remember :) Oh, I got it now. Thanks. The revised patch is attached. The only modified file is pg_ctl.c. The patch worked as expected. It is regrettable that I could not make it in time for PG 10, but I'd appreciate it if you could review and commit this patch early in PG 11 while our memory is fresh. Thank you for your patience. I'll create an entry in the next CF soon. Regards Takayuki Tsunakawa win_large_pages_v13.patch Description: win_large_pages_v13.patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
