Re: Copy function for logical replication slots

On Tue, Feb 19, 2019 at 1:28 AM Andres Freund wrote: > > Hi, > > On 2019-02-18 16:57:07 +0900, Masahiko Sawada wrote: > > > Stupid question, but couldn't we optimize this to something like: > > > > > > /* > > > * First copy current data of the slot. Then install those in the > > > *

Re: Problems with plan estimates in postgres_fdw

(2019/02/18 23:21), Antonin Houska wrote: Etsuro Fujita wrote: (2019/02/15 21:46), Antonin Houska wrote: ok, I understand now. I assume that the patch https://www.postgresql.org/message-id/5C66A056.60007%40lab.ntt.co.jp obsoletes the code snippet we discussed above. Sorry, I don't understa

Re: Speed up transaction completion faster after many relations are accessed in a transaction

On Tue, 19 Feb 2019 at 00:20, Andres Freund wrote: > On 2019-02-18 19:13:31 -0500, Tom Lane wrote: > > Andres Freund writes: > > > On 2019-02-18 19:01:06 -0500, Tom Lane wrote: > > >> Mmm ... AIUI, the patches currently proposed can only help for what > > >> David called "point lookup" queries.

Re: Reaping Temp tables to avoid XID wraparound

On Mon, Feb 18, 2019 at 2:31 AM Michael Paquier wrote: > On Sun, Feb 17, 2019 at 05:47:09PM +0100, Magnus Hagander wrote: > > We could I guess add a field specifically for temp_namespace_xid or such. > > The question is if it's worth the overhead to do that. > > That would mean an extra 4 bytes i

Re: [Bug Fix] ECPG: could not use set xxx to default statement

On Tue, 2019-02-19 at 00:05 -0500, Tom Lane wrote: > I wrote: > > "Higuchi, Daisuke" writes: > > > [ missing semicolon in gram.y breaks ecpg parsing of same > > > construct ] > > That's pretty nasty. The fix in gram.y is certainly needed, but > > I'm > > unexcited by the regression test additions

Re: Prepared transaction releasing locks before deregistering its GID

On 19.02.2019 7:44, Michael Paquier wrote: On Tue, Feb 19, 2019 at 01:07:06AM -0300, Alvaro Herrera wrote: On 2019-Feb-19, Michael Paquier wrote: extern GlobalTransaction MarkAsPreparing(TransactionId xid, const char *gid, Hmm, ABI break ... Well, sure. I always post patches for HEAD fi

RE: [Bug Fix] ECPG: could not use set xxx to default statement

Hi, > I think we need to fix that script to either cope with missing semicolons, > or at least complain about them. Too tired to look into how, right now. I attached the patch which cope with missing semicolons. Previous parse.pl find semicolon and dump data to buffer. When attached patch's p

Re: Prepared transaction releasing locks before deregistering its GID

On Tue, Feb 19, 2019 at 12:26:04PM +0300, Konstantin Knizhnik wrote: > May be I missed something, but why it is not possible just to move removing > 2PC GXact before releasing transaction locks: Because we need to keep the 2PC reference in shared memory when releasing the locks before removing the

Re: Prepared transaction releasing locks before deregistering its GID

Hi, Michael Paquier wrote: > On Mon, Feb 18, 2019 at 05:05:13PM +0100, Oleksii Kliukin wrote: >> That looks like a race condition to me. What happens is that another >> transaction with the name identical to the running one can start and proceed >> to the prepare phase while the original one com

Re: [Bug Fix] ECPG: could not use set xxx to default statement

Higuchi-san, > I attached the patch which cope with missing semicolons. > Previous parse.pl find semicolon and dump data to buffer. When > attached patch's parse.pl find new tokens before finding a semicolon, > it also dumps data to buffer. Now this seems to be much easier than I expected. Thank

Re: Conflict handling for COPY FROM

On Mon, Feb 4, 2019 at 9:06 AM Michael Paquier wrote: > On Wed, Dec 19, 2018 at 02:48:14PM +0300, Surafel Temesgen wrote: > > Thank you for informing, attach is rebased patch against current > > master > > copy.c conflicts on HEAD, please rebase. I am moving the patch to > next CF, waiting on au

Re: Conflict handling for COPY FROM

On Sat, Feb 16, 2019 at 8:24 AM Andres Freund wrote: > Hi, > > On 2018-08-23 17:11:04 +0300, Surafel Temesgen wrote: > > COPY ... WITH ON CONFLICT LOG maximum_error, LOG FILE NAME '…'; > > This doesn't seem to address Robert's point that a log file requires to > be super user only, which seems to

Re: [Bug Fix] ECPG: could not use set xxx to default statement

Higuchi-san, > I attached the patch which cope with missing semicolons. > Previous parse.pl find semicolon and dump data to buffer. When > attached patch's parse.pl find new tokens before finding a semicolon, > it also dumps data to buffer. It just occurred to me that check_rules.pl probably use

Re: Protect syscache from bloating with negative cache entries

On 2/19/19 12:43 AM, Tsunakawa, Takayuki wrote: > Hi Horiguchi-san, > > I've looked through your patches. This is the first part of my review > results. Let me post the rest after another work today. > > BTW, how about merging 0003 and 0005, and separating and deferring 0004 in > another th

Re: Prepared transaction releasing locks before deregistering its GID

On Tue, Feb 19, 2019 at 10:59:33AM +0100, Oleksii Kliukin wrote: > Michael Paquier wrote: >> If you run two transactions in parallel using your script, the second >> transaction would wait at LOCK time until the first transaction >> releases its locks with the COMMIT PREPARED. > > That is the des

Re: Speed up transaction completion faster after many relations are accessed in a transaction

On 2/12/19 7:33 AM, Tsunakawa, Takayuki wrote: > ... > > This problem was uncovered while evaluating partitioning performance. > When the application PREPAREs a statement once and then > EXECUTE-COMMIT repeatedly, the server creates a generic plan on the > 6th EXECUTE. Unfortunately, creation

Re: SQL statement PREPARE does not work in ECPG

> I think SQL statement PREPARE *without* parameter is supported, > but one with parameter is not supported (or has some fatal bugs). It surely should be supported. >> I wrote the source code as follows. >> >> >> EXEC SQL PREPARE test_prep (int) AS SELECT id from tes

Re: Conflict handling for COPY FROM

On February 19, 2019 3:05:37 AM PST, Surafel Temesgen wrote: >On Sat, Feb 16, 2019 at 8:24 AM Andres Freund >wrote: > >> Hi, >> >> On 2018-08-23 17:11:04 +0300, Surafel Temesgen wrote: >> > COPY ... WITH ON CONFLICT LOG maximum_error, LOG FILE NAME '…'; >> >> This doesn't seem to address Robe

Re: [Bug Fix] ECPG: could not use set xxx to default statement

On 2/19/19 6:21 AM, Michael Meskes wrote: > Higuchi-san, > >> I attached the patch which cope with missing semicolons. >> Previous parse.pl find semicolon and dump data to buffer. When >> attached patch's parse.pl find new tokens before finding a semicolon, >> it also dumps data to buffer. > It

Re: [Bug Fix] ECPG: could not use set xxx to default statement

Andrew Dunstan writes: > I just looked at the bison manual at gnu.org and also at `info bison` on > my local machine, and couldn't see any reference to semicolons being > optional at the end of a rule. Under the heading "Syntax of Grammar > Rules" it says this: > A Bison grammar rule has the f

Re: [Bug Fix] ECPG: could not use set xxx to default statement

On 2/19/19 9:29 AM, Tom Lane wrote: > Andrew Dunstan writes: >> I just looked at the bison manual at gnu.org and also at `info bison` on >> my local machine, and couldn't see any reference to semicolons being >> optional at the end of a rule. Under the heading "Syntax of Grammar >> Rules" it say

Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove pgbench "progress" test pending solution of its timing is (fwd)

Hello Tom, Unfortunately, there was no activity over the last few commitfests and the proposed patch pgbench-tap-progress-6 can't be applied anymore without conflicts. Fabien, what are your plans about it, could you please post a rebased version? Here it is. I'm confused about the intende

Re: unconstify equivalent for volatile

On 2019-02-18 21:25, Andres Freund wrote: > ISTM this one should rather be solved by removing all volatiles from > latch.[ch]. As that's a cross-process concern we can't rely on it > anyway (and have placed barriers a few years back to allay concerns / > bugs due to reordering). Aren't the volatil

Re: CPU costs of random_zipfian in pgbench

Hello Peter, My 0.02€: I'm not quite interested in maintaining a tool for *one* benchmark, whatever the benchmark, its standardness or quality. What I like in "pgbench" is that it is both versatile and simple so that people can benchmark their own data with their own load and their own quer

Re: Progress reporting for pg_verify_checksums

Hallo Michael, New patch attached. Patch applies cleanly. Compiles, "make check" ok. doc build is also ok. There are no tests, which is probably fine for such an interactive feature. Docs look okay to me. Clear and to the point. About : total_percent = total_size ? (int64) ((current_s

Re: Allowing extensions to find out the OIDs of their member objects

I wrote: > [ discussion about ways to let extension C code find out object OIDs ] I wanted to close out this thread, for the time being, by saying that I'm not expecting to get anything done about it for v12. It seems pretty late in the dev cycle to be proposing any major new user-visible functio

Re: Some thoughts on NFS

On Tue, Feb 19, 2019 at 2:03 AM Thomas Munro wrote: > How can we achieve that, without writing our > own NFS client? Instead of writing our own NFS client, how about writing our own network storage protocol? Imagine a stripped-down postmaster process running on the NFS server that essentially

Re: restrict pg_stat_ssl to superuser?

On 2019-02-18 04:58, Michael Paquier wrote: > On Fri, Feb 15, 2019 at 02:04:59PM +0100, Peter Eisentraut wrote: >> We could remove default privileges from pg_stat_get_activity(). Would >> that be a problem? > > I don't think so, still I am wondering about the impact that this > could have for mon

Re: Some thoughts on NFS

On Tue, Feb 19, 2019 at 4:46 PM Robert Haas wrote: > On Tue, Feb 19, 2019 at 2:03 AM Thomas Munro > wrote: > > How can we achieve that, without writing our > > own NFS client? > > > You'll need it :) Instead of writing our own NFS client, how about writing our own > network storage protocol?

Re: FOP warnings about id attributes in title tags

On 2019-02-18 16:37, Peter Eisentraut wrote: >> It appears that these are due to title elements having id tags. At >> is says: >> >> When adding an |id| or |xml:id| attribute, put it on the element >> itself, not the |title|. >> >> So mayb

Re: unconstify equivalent for volatile

Hi, On February 19, 2019 7:00:58 AM PST, Peter Eisentraut wrote: >On 2019-02-18 21:25, Andres Freund wrote: >> ISTM this one should rather be solved by removing all volatiles from >> latch.[ch]. As that's a cross-process concern we can't rely on it >> anyway (and have placed barriers a few years

Re: Some thoughts on NFS

On 2/19/19 10:59 AM, Magnus Hagander wrote: > On Tue, Feb 19, 2019 at 4:46 PM Robert Haas > wrote: > > On Tue, Feb 19, 2019 at 2:03 AM Thomas Munro > wrote: > > How can we achieve that, without writing our > > own NFS c

Re: Allowing extensions to find out the OIDs of their member objects

On Mon, Jan 21, 2019 at 9:46 PM Tom Lane wrote: > Perhaps this also gives some impetus to the lets-use-identifiers- > not-numbers approach that Andrew was pushing. I didn't care for > that too much so far as an extension's own internal references > are concerned, but for cross-extension reference

Re: Some thoughts on NFS

Greetings, * Robert Haas (robertmh...@gmail.com) wrote: > On Tue, Feb 19, 2019 at 2:03 AM Thomas Munro wrote: > > How can we achieve that, without writing our > > own NFS client? > > > > Instead of writing our own NFS client, how about writing our own > network storage protocol? Imagine a str

Re: Some thoughts on NFS

On Tue, Feb 19, 2019 at 10:59 AM Magnus Hagander wrote: > The only case I've run into people wanting to use postgres on NFS, the NFS > server is a big filer from netapp or hitachi or whomever. And you're not > going to be able to run something like that on top of it. Yeah. :-( It seems, howev

Re: Some thoughts on NFS

On 2/19/19 5:20 PM, Robert Haas wrote: > On Tue, Feb 19, 2019 at 10:59 AM Magnus Hagander wrote: >> The only case I've run into people wanting to use postgres on NFS, >> the NFS server is a big filer from netapp or hitachi or whomever. And >> you're not going to be able to run something like tha

Re: Some thoughts on NFS

## Magnus Hagander (mag...@hagander.net): > You'd solve more > of that by having the middle layer speak "raw device" underneath and be > able to sit on top of things like iSCSI (yes, really). Back in ye olden days we called these middle layers "kernel" and "filesystem" and had that maintained by

Re: PGAdmin 4 don't refresh server info after restarting

On Mon, Feb 18, 2019 at 1:20 AM Andrey Klychkov wrote: > Hello, > We've noticed that pgadmin 3.x / 4.x doesn't refresh server info like server > version after restarting. It makes people confused. > For example, > 1. PgAdmin was rinning > 2. We upgraded postgres from 11.1 to 11.2 > 3. PgAdmin was

Re: unconstify equivalent for volatile

Andres Freund writes: > The real reason why variables commonly need to be volatile when used in > signal handlers is not the signal handler side, but the normal code flow > side. Yeah, exactly. You have not explained why it'd be safe to ignore that. regards, tom lane

Re: Some thoughts on NFS

Hi, On 2019-02-19 20:03:05 +1300, Thomas Munro wrote: > The first is practical. Running out of diskspace (or quota) is not > all that rare (much more common that EIO from a dying disk, I'd > guess), and definitely recoverable by an administrator: just create > more space. It would be really nice

Re: unconstify equivalent for volatile

Hi, On 2019-02-19 11:48:16 -0500, Tom Lane wrote: > Andres Freund writes: > > The real reason why variables commonly need to be volatile when used in > > signal handlers is not the signal handler side, but the normal code flow > > side. > > Yeah, exactly. You have not explained why it'd be safe

Re: speeding up planning with partitions

Amit Langote writes: > On 2019/02/19 4:42, Tom Lane wrote: >> I don't much care for re-calling build_base_rel_tlists to add extra >> Vars to the appropriate relations; 99% of the work it does will be >> wasted, and with enough child rels you could run into an O(N^2) >> problem. Maybe you could ca

Re: Delay locking partitions during INSERT and UPDATE

On Fri, Feb 15, 2019 at 9:22 PM Andres Freund wrote: > On 2019-01-31 13:46:33 -0500, Robert Haas wrote: > > I have reviewed this patch and I am in favor of it. I think it likely > > needs minor rebasing because of the heap_open -> table_open renaming. > > I also agree that it's worth taking some

Re: Delay locking partitions during INSERT and UPDATE

On Mon, Feb 18, 2019 at 6:15 PM Tom Lane wrote: > I'm inclined to think that if we already have lock on the parent > partitioned table (thereby, IIUC, guaranteeing that its partitioning > info can't change) that the order in which we acquire the same lock > level on its partition(s) isn't very imp

Re: restrict pg_stat_ssl to superuser?

On Thu, Feb 7, 2019 at 3:30 AM Peter Eisentraut wrote: > As discussed in [0], should we restrict access to pg_stat_ssl to > superusers (and an appropriate pg_ role)? > > If so, is there anything in that view that should be made available to > non-superusers? If not, then we could perhaps do this

Re: CPU costs of random_zipfian in pgbench

On Sun, Feb 17, 2019 at 11:02:37PM +0100, Tomas Vondra wrote: > On 2/17/19 6:33 PM, David Fetter wrote: > > On Sun, Feb 17, 2019 at 11:09:27AM -0500, Tom Lane wrote: > >> Fabien COELHO writes: > I'm trying to use random_zipfian() for benchmarking of skewed data sets, > and I ran head-fi

Re: Some thoughts on NFS

On Tue, Feb 19, 2019 at 5:33 PM Tomas Vondra wrote: > > On 2/19/19 5:20 PM, Robert Haas wrote: > > On Tue, Feb 19, 2019 at 10:59 AM Magnus Hagander > wrote: > > >> There might be a use-case for the split that you mention, > >> absolutely, but it's not going to solve the people-who-want-NFS > >>

Re: Some thoughts on NFS

On Tue, Feb 19, 2019 at 5:38 PM Christoph Moench-Tegeder wrote: > ## Magnus Hagander (mag...@hagander.net): > > > You'd solve more > > of that by having the middle layer speak "raw device" underneath and be > > able to sit on top of things like iSCSI (yes, really). > > Back in ye olden days we c

Re: Some thoughts on NFS

Hi, On 2019-02-19 16:59:35 +0100, Magnus Hagander wrote: > There might be a use-case for the split that you mention, absolutely, but > it's not going to solve the people-who-want-NFS situation. You'd solve more > of that by having the middle layer speak "raw device" underneath and be > able to sit

Re: Some thoughts on NFS

On Tue, Feb 19, 2019 at 1:17 PM Andres Freund wrote: > On 2019-02-19 16:59:35 +0100, Magnus Hagander wrote: > > There might be a use-case for the split that you mention, absolutely, but > > it's not going to solve the people-who-want-NFS situation. You'd solve more > > of that by having the middle

Re: WAL insert delay settings

On Fri, Feb 15, 2019 at 1:42 PM Andres Freund wrote: > I think it'd not be insane to add two things: > - WAL write rate limiting, independent of the vacuum stuff. It'd also be > used by lots of other bulk commands (CREATE INDEX, ALTER TABLE > rewrites, ...) > - Account for WAL writes in the cu

Re: Some thoughts on NFS

Hi, On 2019-02-19 13:21:21 -0500, Robert Haas wrote: > On Tue, Feb 19, 2019 at 1:17 PM Andres Freund wrote: > > On 2019-02-19 16:59:35 +0100, Magnus Hagander wrote: > > > There might be a use-case for the split that you mention, absolutely, but > > > it's not going to solve the people-who-want-NF

Re: WAL insert delay settings

Hi, On 2019-02-19 13:28:00 -0500, Robert Haas wrote: > On Fri, Feb 15, 2019 at 1:42 PM Andres Freund wrote: > > I think it'd not be insane to add two things: > > - WAL write rate limiting, independent of the vacuum stuff. It'd also be > > used by lots of other bulk commands (CREATE INDEX, ALTER

Row Level Security − leakproof-ness and performance implications

Hello In order to increase our security, we have started deploying row-level security in order to add another safety net if any issue was to happen in our applications. After a careful monitoring of our databases, we found out that a lot of queries started to go south, going extremely slow. The

Re: WAL insert delay settings

On 2/19/19 7:35 PM, Andres Freund wrote: > Hi, > > On 2019-02-19 13:28:00 -0500, Robert Haas wrote: >> On Fri, Feb 15, 2019 at 1:42 PM Andres Freund wrote: >>> I think it'd not be insane to add two things: >>> - WAL write rate limiting, independent of the vacuum stuff. It'd also be >>> used

Re: Some thoughts on NFS

On Tue, Feb 19, 2019 at 1:29 PM Andres Freund wrote: > > Is that a new thing? I ran across PostgreSQL-over-iSCSI a number of > > years ago and the evidence strongly suggested that it did not reliably > > report disk errors back to PostgreSQL, leading to corruption. > > How many years ago are we t

Re: WAL insert delay settings

On Tue, Feb 19, 2019 at 1:35 PM Andres Freund wrote: > I still don't *AT ALL* buy Stephen and Tomas' argument that it'd be > confusing that when both VACUUM and WAL cost limiting are active, the > lower limit takes effect. I think you guys may all be in vigorous -- not to say mortal -- agreement.

Re: WAL insert delay settings

On 2019-02-19 19:43:14 +0100, Tomas Vondra wrote: > > > On 2/19/19 7:35 PM, Andres Freund wrote: > > Hi, > > > > On 2019-02-19 13:28:00 -0500, Robert Haas wrote: > >> On Fri, Feb 15, 2019 at 1:42 PM Andres Freund wrote: > >>> I think it'd not be insane to add two things: > >>> - WAL write rate

Re: Some thoughts on NFS

Hi, On 2019-02-19 13:45:28 -0500, Robert Haas wrote: > On Tue, Feb 19, 2019 at 1:29 PM Andres Freund wrote: > > And I think it's not that likely that we'd not screw up a > > number of times implementing iSCSI ourselves - not to speak of the fact > > that that seems like an odd place to focus deve

Re: Some thoughts on NFS

On Tue, Feb 19, 2019 at 1:56 PM Andres Freund wrote: > My point is that for iSCSC to be performant we'd need *all* the > infrastructure we also need for direct IO *and* a *lot* more. And that > it seems insane to invest very substantial resources into developing our > own iSCSI client when we don'

Re: WAL insert delay settings

On 2/19/19 7:50 PM, Andres Freund wrote: > On 2019-02-19 19:43:14 +0100, Tomas Vondra wrote: >> >> >> On 2/19/19 7:35 PM, Andres Freund wrote: >>> Hi, >>> >>> On 2019-02-19 13:28:00 -0500, Robert Haas wrote: On Fri, Feb 15, 2019 at 1:42 PM Andres Freund wrote: > I think it'd not be insane

Re: Some thoughts on NFS

On Tue, Feb 19, 2019 at 7:58 PM Robert Haas wrote: > On Tue, Feb 19, 2019 at 1:56 PM Andres Freund wrote: > > My point is that for iSCSC to be performant we'd need *all* the > > infrastructure we also need for direct IO *and* a *lot* more. And that > > it seems insane to invest very substantial

Re: Some thoughts on NFS

On Tue, Feb 19, 2019 at 2:05 PM Magnus Hagander wrote: > C'mon Robert. > > Surely you know that such patches should be landed on *Fridays*, not > Thursdays. Oh, right. And preferably via airplane wifi from someplace over the Atlantic ocean, right? -- Robert Haas EnterpriseDB: http://www.enter

Re: Prepared transaction releasing locks before deregistering its GID

Hi, Oleksii Kliukin wrote: > > The approach looks good to me. Surprisingly, I saw no stalled backends > because of the double acquisition of lock at TwoPhaseGetGXact once I put a > simple TwoPhaseStateLock right before the "gxact->valid = false” line; I > will test your patch and post the outcom

Re: propagating replica identity to partitions

On Mon, Feb 4, 2019 at 11:30 AM Alvaro Herrera wrote: > If you do ALTER TABLE .. REPLICA IDENTITY to a partitioned table, the > command operates on the parent table itself and does not propagate to > partitions. Why is this? Maybe not recursing was the right call when > we only had regular inher

Re: WAL insert delay settings

On 2/19/19 7:28 PM, Robert Haas wrote: > On Fri, Feb 15, 2019 at 1:42 PM Andres Freund wrote: >> I think it'd not be insane to add two things: >> - WAL write rate limiting, independent of the vacuum stuff. It'd also be >> used by lots of other bulk commands (CREATE INDEX, ALTER TABLE >> rew

Re: WAL insert delay settings

On 2019-02-19 20:02:32 +0100, Tomas Vondra wrote: > Let's do a short example. Assume the default vacuum costing parameters > > vacuum_cost_limit = 200 > vacuum_cost_delay = 20ms > cost_page_dirty = 20 > > and for simplicity we only do writes. So vacuum can do ~8MB/s of writes. > > Now,

Re: WAL insert delay settings

On 2/19/19 8:22 PM, Andres Freund wrote: > On 2019-02-19 20:02:32 +0100, Tomas Vondra wrote: >> Let's do a short example. Assume the default vacuum costing parameters >> >> vacuum_cost_limit = 200 >> vacuum_cost_delay = 20ms >> cost_page_dirty = 20 >> >> and for simplicity we only do

Re: WAL insert delay settings

Hi, On 2019-02-19 20:34:25 +0100, Tomas Vondra wrote: > On 2/19/19 8:22 PM, Andres Freund wrote: > > And my main point is that even if you implement a proper bytes/sec limit > > ONLY for WAL, the behaviour of VACUUM rate limiting doesn't get > > meaningfully more confusing than right now. > > >

Re: proposal: pg_restore --convert-to-text

Em seg, 18 de fev de 2019 às 19:21, Tom Lane escreveu: > > Euler Taveira writes: > > Since no one has stepped up, I took a stab at it. It will prohibit > > standard output unless '-f -' be specified. -l option also has the > > same restriction. > > Hm, don't really see the need to break -l usage

Re: propagating replica identity to partitions

On 2019-Feb-19, Robert Haas wrote: > It's not unreasonable to use the parent's REPLICA IDENTITY setting as > the default for new partitions, much as we now do for the TABLESPACE, > because the parent's replica identity is otherwise without meaning. > But I'm less convinced that it's reasonable to

Re[2]: PGAdmin 4 don't refresh server info after restarting

> You should report this on a pgAdmin mailing list rather than a > PostgreSQL mailing list. Hi, of course, I made a mistake. Thank you for showing me >Вторник, 19 февраля 2019, 19:41 +03:00 от Robert Haas : > >On Mon, Feb 18, 2019 at 1:20 AM Andrey Klychkov < aaklych...@mail.ru > wrote: >> Hello

Re: propagating replica identity to partitions

On Tue, Feb 19, 2019 at 3:40 PM Alvaro Herrera wrote: > Maybe we should be using the inheritance marker in the command to note > whether to recurse to partitions? That is, if you say > ALTER TABLE ONLY parent SET REPLICA IDENTITY > then we don't recurse and just change the parent table and futu

Re: WAL insert delay settings

On Wed, 20 Feb 2019 at 07:28, Robert Haas wrote: > Or maybe we should just blow up the current vacuum cost delay stuff > and replace it with something that is easier to tune. For example, we > could just have one parameter that sets the maximum read rate in kB/s > and another that sets the maximu

Re: speeding up planning with partitions

I wrote: > OK, I'll make another pass over 0001 today. So I started the day with high hopes for this, but the more I looked at it the less happy I got, and finally I ran into something that looks to be a complete show-stopper. Namely, that the patch does not account for the possibility of an inhe

Re: Delay locking partitions during INSERT and UPDATE

On Wed, 20 Feb 2019 at 06:36, Robert Haas wrote: > > On Mon, Feb 18, 2019 at 6:15 PM Tom Lane wrote: > > I'm inclined to think that if we already have lock on the parent > > partitioned table (thereby, IIUC, guaranteeing that its partitioning > > info can't change) that the order in which we acqu

Another way to fix inherited UPDATE/DELETE

While contemplating the wreckage of https://commitfest.postgresql.org/22/1778/ I had the beginnings of an idea of another way to fix that problem. The issue largely arises from the fact that for UPDATE, we expect the plan tree to emit a tuple that's ready to be stored back into the target rel ...

Re: Some thoughts on NFS

On Tue, Feb 19, 2019 at 8:03 PM Thomas Munro wrote: > A theoretical question I thought of is whether there are any > interleavings of operations that allow a checkpoint to complete > bogusly, while a concurrent close() in a regular backend fails with > EIO for data that was included in the checkpo

Re: Some thoughts on NFS

On Wed, Feb 20, 2019 at 5:52 AM Andres Freund wrote: > > 1. Figure out how to get the ALLOCATE command all the way through the > > stack from PostgreSQL to the remote NFS server, and know for sure that > > it really happened. On the Debian buster Linux 4.18 system I checked, > > fallocate() repo

Re: Some thoughts on NFS

Hi, On 2019-02-20 11:25:22 +1300, Thomas Munro wrote: > This seems to make sense, and has the advantage that it uses > interfaces that exist right now. But it seems a bit like we'll have > to wait for them to finish building out the errseq_t support for NFS > to avoid various races around the map

Re: propagating replica identity to partitions

On 2019-Feb-19, Robert Haas wrote: > On Tue, Feb 19, 2019 at 3:40 PM Alvaro Herrera > wrote: > > Maybe we should be using the inheritance marker in the command to note > > whether to recurse to partitions? That is, if you say > > ALTER TABLE ONLY parent SET REPLICA IDENTITY > > then we don't

Re: Another way to fix inherited UPDATE/DELETE

Hi, On 2019-02-19 16:48:55 -0500, Tom Lane wrote: > I have no idea how this might play with the pluggable-storage work. I don't think it'd have a meaningful impact, except for needing changes to an overlapping set of lines. But given the different timeframes, I'd not expect a problem with that.

Re: Another way to fix inherited UPDATE/DELETE

On Wed, 20 Feb 2019 at 10:49, Tom Lane wrote: > What if we dropped that idea, and instead defined the plan tree as > returning only the columns that are updated by SET, plus the row > identity? It would then be the ModifyTable node's job to fetch the > original tuple using the row identity (which

Re: Some thoughts on NFS

On Wed, Feb 20, 2019 at 7:58 AM Robert Haas wrote: > On Tue, Feb 19, 2019 at 1:56 PM Andres Freund wrote: > > My point is that for iSCSC to be performant we'd need *all* the > > infrastructure we also need for direct IO *and* a *lot* more. And that > > it seems insane to invest very substantial r

Re: Compressed TOAST Slicing

On Sat, Feb 16, 2019 at 7:25 AM Simon Riggs wrote: > Could we get an similarly optimized implementation of -> operator for JSONB > as well? > Are there any other potential uses? Best to fix em all up at once and then > move on to other things. Thanks. Oddly enough, I couldn't find many/any thi

More smarts about CoerceViaIO, and less stupidity about ArrayCoerceExpr

I poked into a recent complaint[1] about PG not being terribly smart about whether an IS NOT NULL index predicate is implied by a WHERE clause, and determined that there are a couple of places where we are being less bright than we could be about CoerceViaIO semantics. CoerceViaIO is strict indepen

Re: WAL insert delay settings

On 2/19/19 8:40 PM, Andres Freund wrote: > Hi, > > On 2019-02-19 20:34:25 +0100, Tomas Vondra wrote: >> On 2/19/19 8:22 PM, Andres Freund wrote: >>> And my main point is that even if you implement a proper bytes/sec limit >>> ONLY for WAL, the behaviour of VACUUM rate limiting doesn't get >>> m

Re: Row Level Security − leakproof-ness and performance implications

Pierre Ducroquet wrote: > In order to increase our security, we have started deploying row-level > security in order to add another safety net if any issue was to happen in our > applications. > After a careful monitoring of our databases, we found out that a lot of > queries started to go south

RE: Protect syscache from bloating with negative cache entries

>From: Ideriha, Takeshi [mailto:ideriha.take...@jp.fujitsu.com] >But at the same time, I did some benchmark with only hard limit option enabled >and >time-related option disabled, because the figures of this case are not >provided in this >thread. >So let me share it. I'm sorry but I'm taking ba

ZRE: Protect syscache from bloating with negative cache entries

From: Tomas Vondra [mailto:tomas.von...@2ndquadrant.com] > 0.7% may easily be just a noise, possibly due to differences in layout > of the binary. How many runs? What was the variability of the results > between runs? What hardware was this tested on? 3 runs, with the variability of about +-2%. L

Using old master as new replica after clean switchover

Hello Postgres Gurus, After searching (on www.postgresql.org/Google) I found that the following steps can be used to perform a switchover in Postgres (version 9.3): *Step 1.* Do clean shutdown of Primary (-m fast or smart). *Step 2. *Check for sync status and recovery status of Standby before prom

RE: Protect syscache from bloating with negative cache entries

From: Ideriha, Takeshi [mailto:ideriha.take...@jp.fujitsu.com] > number of tables | 100 |1000|1 > --- > TPS (master) |10966 |10654 |9099 > TPS (patch)| 11137 (+1%) |10710 (+0%) |772 (-91%) > > It seems that before ca

Re: Prepared transaction releasing locks before deregistering its GID

On Tue, Feb 19, 2019 at 08:17:14PM +0100, Oleksii Kliukin wrote: > I gave it a spin on the same VM host as shown to constantly reproduce the > issue and observed neither 'identifier already in use' nor any locking > issues over a few dozens of runs, so it looks good to me. Thanks for the confirmat

Re: Using old master as new replica after clean switchover

On Tue, Feb 19, 2019 at 04:27:02PM -0800, RSR999GMAILCOM wrote: > So wanted to clarify if this procedure really requires the WAL archive > location on a shared storage ? Shared storage for WAL archives is not a requirement. It is perfectly possible to use streaming replication to get correct WAL

Re: CPU costs of random_zipfian in pgbench

On Tue, Feb 19, 2019 at 7:14 AM Fabien COELHO wrote: > What I like in "pgbench" is that it is both versatile and simple so that > people can benchmark their own data with their own load and their own > queries by writing a few lines of trivial SQL and psql-like slash command > and adjusting a few

RE: SQL statement PREPARE does not work in ECPG

Hi Meskes-san, Thank you for your replying. > Please try this instead: > > EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id > = $1; > EXEC SQL EXECUTE test_prep using 2; > > This should work. I tried as follows. EXEC SQL PREPARE test_pr

Re: Another way to fix inherited UPDATE/DELETE

Hi, On 2019/02/20 6:48, Tom Lane wrote: > While contemplating the wreckage of > https://commitfest.postgresql.org/22/1778/ > I had the beginnings of an idea of another way to fix that problem. > > The issue largely arises from the fact that for UPDATE, we expect > the plan tree to emit a tuple th

Re: Another way to fix inherited UPDATE/DELETE

On 2019/02/20 10:55, Amit Langote wrote: > Maybe I should've mentioned that on this thread at some point. I meant the other thread where we're discussing my patches. Thanks, Amit

Re: Reaping Temp tables to avoid XID wraparound

On Tue, Feb 19, 2019 at 09:56:28AM +0100, Magnus Hagander wrote: > 2. Or probably even better, just put it in PgBackendStatus? Overhead here > is a lot cheaper than PGPROC. > > ISTM 2 is probably the most reasonable option here? Yes, I forgot this one. That would be more consistent, even if the

Re: Delay locking partitions during INSERT and UPDATE

On Tue, Feb 19, 2019 at 4:07 PM David Rowley wrote: > I'd say that here we should only discuss what this patch is doing, not > anything else that's in flight that you're concerned will conflict > with the ATTACH/DETACH PARTITION CONCURRENTLY patch. > > During INSERT and UPDATE, not all partitions

  1   2   >