Re: Problem while updating a foreign table pointing to a partitioned table on foreign server

On Wed, Apr 18, 2018 at 9:43 AM, Kyotaro HORIGUCHI wrote: > > Anyway I think we should warn or error out if one nondirect > update touches two nor more tuples in the first place. > > =# UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a > = 1; > ERROR: updated 2 rows for a

Re: Should we add GUCs to allow partition pruning to be disabled?

On Wed, Apr 18, 2018 at 5:37 AM, David Rowley wrote: > In PG10 the planner's partition pruning could be disabled by changing > the constraint_exclusion GUC to off. This is still the case for PG11, > but only for UPDATE and DELETE queries. There is currently no way to > disable partition pruning f

Re: Double-writes, take two?

Bonjour Michaël, - double-write buffers use a pre-decided numbers of pages (32 for the checkpointer, 128 divided into 4 buckets for the backends), which are synced into disk once each batch is full. - The double-write file of the checkpointer uses ordering of pages using blocks number and fi

Re: Fix for documentation of Covering Indexes

On 11/04/18 04:20, Michael Paquier wrote: Hi all, The documentation of covering indexes is incorrect for CREATE and ALTER TABLE: - ALTER TABLE's page is missing the call. - Exclusion constraints can use INCLUDE clauses. In order to simplify the documentation, please let me suggest the attached

Re: PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS

On Tue, Apr 17, 2018 at 02:34:53PM -0700, Andres Freund wrote: > On 2018-04-17 17:29:17 -0400, Bruce Momjian wrote: > > Also, if we are relying on WAL, we have to make sure WAL is actually > > safe with fsync, and I am betting only the O_DIRECT methods actually > > are safe: > > > > #wal_sync_

Re: PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS

On 18 April 2018 at 05:19, Bruce Momjian wrote: > On Tue, Apr 10, 2018 at 05:54:40PM +0100, Greg Stark wrote: >> On 10 April 2018 at 02:59, Craig Ringer wrote: >> >> > Nitpick: In most cases the kernel reserves disk space immediately, >> > before returning from write(). NFS seems to be the main e

VM map freeze corruption

pg_check_frozen() reports corrupted VM freeze state. Found with one of my stress tests. Simplified to the repro below. The reason for the 33 rows/pages is that I wanted to test if a 2nd vacuum freeze repaired the situation. I was confounded till I discovered SKIP_PAGES_THRESHOLD was 32. My

Re: Built-in connection pooling

On 17.04.2018 20:09, Nikolay Samokhvalov wrote: Understood. One more question. Have you considered creation of pooling tool as a separate, not built-in tool, but being shipped with Postgres — like psql is shipped in packages usually called “postgresql-client-XX” which makes psql the default t

Re: Setting rpath on llvmjit.so?

> Well, before it does everything, there's little point in reviewing > whether it's mergeable or not. For this significant case, it's not working as you expect. First, Postgres community should find consensus about migration to CMake (or alternative). Now, this project too huge to work on it wit

Re: PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS

On 10 April 2018 at 20:15, Craig Ringer wrote: > On 10 April 2018 at 14:10, Michael Paquier wrote: > >> Well, I think that there is place for improving reporting of failure >> in file_utils.c for frontends, or at worst have an exit() for any kind >> of critical failures equivalent to a PANIC. > >

Re: Boolean partitions syntax

Horiguchi-san, Thank you for updating the patch. On 2018/04/16 16:17, Kyotaro HORIGUCHI wrote: > the attached v6 patch differs only in gram.y since v5. Patch fails to compile, because it adds get_partition_col_collation to rel.h instead of partcache.h: src/include/utils/rel.h: In function ‘get_

de-deduplicate code in DML execution hooks in postgres_fdw

Hi, While working on a fix related to non-direct DML [1], I noticed that postgresExecForeignInsert(), postgresExecForeignUpdate() and postgresExecForeignDelete() functions are almost identical except that postgresExecForeignInsert() doesn't require ctid. The fix that I was working is applicable to

Re: Built-in connection pooling

On 18/04/18 06:10, Konstantin Knizhnik wrote: But there are still use cases which can not be covered y external connection pooler. Can you name some? I understand that the existing external connection poolers all have their limitations. But are there some fundamental issues that can *only* be

Re: VM map freeze corruption

On Wed, Apr 18, 2018 at 7:37 AM, Wood, Dan wrote: > > > My analysis is that heap_prepare_freeze_tuple->FreezeMultiXactId() > returns FRM_NOOP if the MultiXACT locked rows haven't committed. This > results in changed=false and totally_frozen=true(as initialized). When > this returns to lazy_scan

hostorder and failover_timeout for libpq

Hello hackers, Couple of years ago Victor Wagner presented a patch [1] that introduced multiple hosts capability and also hostorder and failover_timeout parameters for libpq. Subsequently multi-host feature was reimplemented by Robert Haas and committed. Later target_session_attrs parameter wa

Re: [HACKERS] proposal: schema variables

On Tue, Apr 17, 2018 at 06:28:19PM +0200, Pavel Stehule wrote: > I though about it, and I am inclined to prefer pg_class instead separate > tables. > > It true, so there are lot of "unused" attributes for this purpose, but > there is lot of shared attributes, and lot of shared code. Semantically,

Re: PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS

On Wed, Apr 18, 2018 at 06:04:30PM +0800, Craig Ringer wrote: > On 18 April 2018 at 05:19, Bruce Momjian wrote: > > On Tue, Apr 10, 2018 at 05:54:40PM +0100, Greg Stark wrote: > >> On 10 April 2018 at 02:59, Craig Ringer wrote: > >> > >> > Nitpick: In most cases the kernel reserves disk space imm

Re: Built-in connection pooling

On 18.04.2018 13:36, Heikki Linnakangas wrote: On 18/04/18 06:10, Konstantin Knizhnik wrote: But there are still use cases which can not be covered y external connection pooler. Can you name some? I understand that the existing external connection poolers all have their limitations. But are

Re: PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS

On Tue, Apr 17, 2018 at 02:41:42PM -0700, Andres Freund wrote: > On 2018-04-17 17:32:45 -0400, Bruce Momjian wrote: > > On Mon, Apr 9, 2018 at 03:42:35PM +0200, Tomas Vondra wrote: > > > That doesn't seem like a very practical way. It's better than nothing, > > > of course, but I wonder how would

Re: PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS

wrOn 18 April 2018 at 19:46, Bruce Momjian wrote: > So, if sync mode passes the write to NFS, and NFS pre-reserves write > space, and throws an error on reservation failure, that means that NFS > will not corrupt a cluster on out-of-space errors. Yeah. I need to verify in a concrete test case.

Re: Oddity in tuple routing for foreign partitions

(2018/04/18 14:44), Amit Langote wrote: On 2018/04/17 16:41, Etsuro Fujita wrote: In the INSERT/COPY-tuple-routing case, as explained by Amit, the RTE at that position in the EState's range table is the one for the partitioned table of a given partition, so the statement would be true. BUT in th

Re: Built-in connection pooling

On 18 April 2018 at 19:52, Konstantin Knizhnik wrote: > As far as I know most of DBMSes have some kind of internal connection > pooling. > Oracle, for example, you can create dedicated and non-dedicated backends. > I wonder why we do not want to have something similar in Postgres. I want to, and

Re: Built-in connection pooling

On Wed, Apr 18, 2018 at 02:52:39PM +0300, Konstantin Knizhnik wrote: > Yandex team is following this approach with theirOdysseus > (multithreaded version of pgbouncer with many of pgbouncer issues > fixed). Have they opened the source to Odysseus? If not, do they have plans to? Best, David. --

Re: Built-in connection pooling

On 18.04.2018 16:09, Craig Ringer wrote: On 18 April 2018 at 19:52, Konstantin Knizhnik wrote: As far as I know most of DBMSes have some kind of internal connection pooling. Oracle, for example, you can create dedicated and non-dedicated backends. I wonder why we do not want to have somethin

Re: Built-in connection pooling

On 18.04.2018 16:24, David Fetter wrote: On Wed, Apr 18, 2018 at 02:52:39PM +0300, Konstantin Knizhnik wrote: Yandex team is following this approach with theirOdysseus (multithreaded version of pgbouncer with many of pgbouncer issues fixed). Have they opened the source to Odysseus? If not, d

Re: VM map freeze corruption

Pavan Deolasee wrote: > On Wed, Apr 18, 2018 at 7:37 AM, Wood, Dan wrote: > > My analysis is that heap_prepare_freeze_tuple->FreezeMultiXactId() > > returns FRM_NOOP if the MultiXACT locked rows haven't committed. This > > results in changed=false and totally_frozen=true(as initialized). When >

Re: ON CONFLICT DO UPDATE for partitioned tables

Amit Langote wrote: > On 2018/04/18 0:04, Alvaro Herrera wrote: > > Amit Langote wrote: > > > >> I just confirmed my hunch that this wouldn't somehow do the right thing > >> when the OID system column is involved. Like this case: > > > > This looks too big a patch to pursue now. I'm inclined to

Re: Built-in connection pooling

On 18/04/18 07:52, Konstantin Knizhnik wrote: On 18.04.2018 13:36, Heikki Linnakangas wrote: On 18/04/18 06:10, Konstantin Knizhnik wrote: But there are still use cases which can not be covered y external connection pooler. Can you name some? I understand that the existing external connecti

Re: Built-in connection pooling

On 18.04.2018 16:41, Heikki Linnakangas wrote: On 18/04/18 07:52, Konstantin Knizhnik wrote: On 18.04.2018 13:36, Heikki Linnakangas wrote: On 18/04/18 06:10, Konstantin Knizhnik wrote: But there are still use cases which can not be covered y external connection pooler. Can you name some

Re: Postgres stucks in deadlock detection

On 16.04.2018 14:11, Konstantin Knizhnik wrote: On 14.04.2018 10:09, Юрий Соколов wrote: пт, 13 апр. 2018 г., 21:10 Andres Freund >: Hi, On 2018-04-13 19:13:07 +0300, Konstantin Knizhnik wrote: > On 13.04.2018 18:41, Andres Freund wrote: > > On 20

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Wed, Apr 18, 2018 at 7:46 AM, Andres Freund wrote: > On 2018-04-18 10:46:51 +0900, Michael Paquier wrote: >> On Tue, Apr 17, 2018 at 06:13:31PM -0700, Andres Freund wrote: >> > Not sure what you mean? >> >> Do you need help on it? I suggest that I could undertake the proposed >> patch and subm

Re: Built-in connection pooling

> 18 апр. 2018 г., в 16:24, David Fetter написал(а): > > On Wed, Apr 18, 2018 at 02:52:39PM +0300, Konstantin Knizhnik wrote: >> Yandex team is following this approach with theirOdysseus >> (multithreaded version of pgbouncer with many of pgbouncer issues >> fixed). > > Have they opened the so

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Tue, Apr 17, 2018 at 11:05 PM, Fujii Masao wrote: > Hi, > > I'd like to propose to add $SUBJECT for performance improvement. > > When VACUUM tries to truncate the trailing empty pages, it scans > shared_buffers > to invalidate the pages-to-truncate during holding an AccessExclusive lock > on >

Re: remove quoting hacks and simplify bootscanner.l

John Naylor writes: > For the bootstrap data conversion, it was desirable for postgres.bki > to remain unchanged, so some ugly quoting hacks were added to > genbki.pl to match the quoting conventions in the DATA() lines. At > this point, it's possible (and worthwhile I think) to remove those, > an

Re: Documentation for bootstrap data conversion

John Naylor writes: > On 4/9/18, Tom Lane wrote: >> Meh, I think either is fine really. I do recall changing something >> in bki.sgml that referred to both "bootstrap relations" and "bootstrap >> catalogs" in practically the same sentence. I think that *is* confusing, >> because it's not obviou

Re: Setting rpath on llvmjit.so?

On 4/17/18 16:14, Andres Freund wrote: > I still think cmake is the least unreasonable path going forward. I would rather try to make Meson work and if needed add features back into Meson. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote D

Re: Problem while setting the fpw with SIGHUP

On Fri, Apr 13, 2018 at 10:36 PM, Robert Haas wrote: > On Thu, Apr 12, 2018 at 9:29 PM, Michael Paquier wrote: >> Still does it matter when the change is effective? > > I don't really care deeply about when the change takes effect, but I > do care about whether the time when the system *says* the

Re: Problem while setting the fpw with SIGHUP

On Wed, Apr 18, 2018 at 10:37 AM, Amit Kapila wrote: > On Fri, Apr 13, 2018 at 10:36 PM, Robert Haas wrote: >> On Thu, Apr 12, 2018 at 9:29 PM, Michael Paquier wrote: >>> Still does it matter when the change is effective? >> >> I don't really care deeply about when the change takes effect, but I

Re: Setting rpath on llvmjit.so?

Peter Eisentraut writes: > On 4/17/18 16:14, Andres Freund wrote: >> I still think cmake is the least unreasonable path going forward. > I would rather try to make Meson work and if needed add features back > into Meson. I'm wondering whether that will result in expending a lot of effort to move

Re: Setting rpath on llvmjit.so?

On Tue, Apr 17, 2018 at 4:13 PM, Andres Freund wrote: > I'd not advocate for this solely based on the age of autoconf. But the > separate windows buildsystem which makes it very hard to build > extensions separately is a good reason on its own. As is the fact that > recursive make as we're using i

Re: pruning disabled for array, enum, record, range type partition keys

Amit Langote wrote: > On 2018/04/18 7:11, Alvaro Herrera wrote: > > @@ -1546,8 +1546,8 @@ match_clause_to_partition_key(RelOptInfo *rel, >case PARTITION_STRATEGY_HASH: > cmpfn = get_opfamily_proc(part_scheme->partopfamily[partkeyidx], > - op_righttype, op_r

Re: pruning disabled for array, enum, record, range type partition keys

On Thu, Apr 19, 2018 at 12:01 AM, Alvaro Herrera wrote: > Amit Langote wrote: > >> On 2018/04/18 7:11, Alvaro Herrera wrote: >> >> @@ -1546,8 +1546,8 @@ match_clause_to_partition_key(RelOptInfo *rel, >>case PARTITION_STRATEGY_HASH: >> cmpfn = get_opfamily_proc(part_scheme->partopfamily[p

Re: [HACKERS] Runtime Partition Pruning

On Mon, Apr 16, 2018 at 10:46 PM, David Rowley wrote: > I did go and start working on a patch to test how possible this would > be and came up with the attached. I've left a stray > MemoryContextStatsDetail call in there which does indicate that > something is not being freed. I'm just not sure wh

Re: pgindent run soon?

On Tue, Apr 17, 2018 at 12:57 PM, Tom Lane wrote: > Now that feature freeze is past, I wonder if it's time to run pgindent. +1 -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Deadlock in multiple CIC.

I wrote: >> It's still not entirely clear what's happening on okapi, ... okapi has now passed two consecutive runs with elog(LOG) messages in place between DefineIndex's snapmgr calls. Considering that it had failed 37 of 44 test runs since 47a3a13 went in, I think two successive passes is suffic

Query is over 2x slower with jit=on

Hi all.   I don't know whether this is expected or not but I'm experiencing over 2x slowdown on a large query in PG-11 with JIT=on.   (query is a prepared statement executed with "explain analyze execute myprepared(arg1, arg2, ..., argn)")   After 10 executions these are the results (the first 5

Re: Deadlock in multiple CIC.

Tom Lane wrote: > Anyway, at this point I'm going to give up on the debug logging, revert > 9.4 to its prior state, and then see if the transaction-restart patch > makes the problem go away. Agreed, thanks. > >> (A couple of the other isolation tests do fail reliably under this > >> scenario; is

Re: Deadlock in multiple CIC.

I wrote: >>> (A couple of the other isolation tests do fail reliably under this >>> scenario; is it worth hardening them?) >> Yes, I think it's worth making them pass somehow -- see commits >> f18795e7b74c, a0eae1a2eeb6. > Will look into that too. I'm not sure that adding extra expected > output

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On 17 April 2018 at 20:09, Tom Lane wrote: > Alvaro Herrera writes: >> Andres was working on a radix tree structure to fix this problem, but >> that seems to be abandoned now, and it seems a major undertaking. While >> I agree that the proposed solution is a wart, it seems much better than >> no

Re: [FEATURE PATCH] pg_stat_statements with plans (v02)

On Fri, 2018-04-06 at 13:57 -0700, legrand legrand wrote: > At startup time there are 2 identical plans found in the view > I thought it should have be only one: the "initial" one > (as long as there is no "good" or "bad" one). Yes, those are 'remnants' from the time where I had two columns, one

Re: Query is over 2x slower with jit=on

On 2018-04-18 17:35:31 +0200, Andreas Joseph Krogh wrote: > With jit=on: > https://explain.depesz.com/s/vYB > Planning Time: 0.336 ms > JIT: >  Functions: 716 >  Generation Time: 78.404 ms >  Inlining: false >  Inlining Time: 0.000 ms >  Optimization: false >  Optimization Time: 43.916 ms >

Re: Setting rpath on llvmjit.so?

Current autoconf system not working on Windows at all, what we talk about? On Wed, 18 Apr 2018, 23:57 Robert Haas, wrote: > On Tue, Apr 17, 2018 at 4:13 PM, Andres Freund wrote: > > I'd not advocate for this solely based on the age of autoconf. But the > > separate windows buildsystem which mak

Re: Setting rpath on llvmjit.so?

On 2018-04-18 15:54:59 +, Yuriy Zhuravlev wrote: > Current autoconf system not working on Windows at all, what we talk about? We generate windows project files. See src/tools/msvc/ - Andres

Re: Setting rpath on llvmjit.so?

> > I also politely decline the offer to be forced to use XCode on mac. Why? We supporting MSVC and not nmake, what difference with xcode? Also, it's just extra benefit from cmake/meson.

Re: Setting rpath on llvmjit.so?

I talked about autoconf build system, /tools/msvc it's extra home build system. On Thu, 19 Apr 2018, 00:58 Andres Freund, wrote: > On 2018-04-18 15:54:59 +, Yuriy Zhuravlev wrote: > > Current autoconf system not working on Windows at all, what we talk > about? > > We generate windows project

Sv: Re: Query is over 2x slower with jit=on

På onsdag 18. april 2018 kl. 17:50:55, skrev Andres Freund mailto:and...@anarazel.de>>: On 2018-04-18 17:35:31 +0200, Andreas Joseph Krogh wrote: > With jit=on: > https://explain.depesz.com/s/vYB > Planning Time: 0.336 ms >  JIT: >   Functions: 716 >   Generation Time: 78.404 ms >   Inlining

Re: Query is over 2x slower with jit=on

On 18 April 2018 at 16:50, Andres Freund wrote: > On 2018-04-18 17:35:31 +0200, Andreas Joseph Krogh wrote: >> With jit=on: >> https://explain.depesz.com/s/vYB >> Planning Time: 0.336 ms >> JIT: >> Functions: 716 >> Generation Time: 78.404 ms >> Inlining: false >> Inlining Time: 0.000 ms

Re: Deadlock in multiple CIC.

Tom Lane wrote: > *** /home/postgres/pgsql/src/test/isolation/expected/lock-update-delete_1.out > Mon Feb 12 14:53:46 2018 > --- > /home/postgres/pgsql/src/test/isolation/output_iso/results/lock-update-delete.out >Wed Apr 18 11:30:23 2018 > *** > *** 150,156 > > t

Re: Query is over 2x slower with jit=on

On 04/18/2018 12:27 PM, Simon Riggs wrote: > Please change the name of the "JIT" parameter to something meaningful > to humans before this gets too far into the wild. > > SSL is somewhat understandable because its not a Postgres-private term. JIT is hardly a Postgres-private term. It's a familia

Re: Speedup of relation deletes during recovery

On Wed, Apr 18, 2018 at 10:44 AM, Michael Paquier wrote: > On Wed, Apr 18, 2018 at 12:46:58AM +0900, Fujii Masao wrote: >> Yes, I think. And, I found that smgrdounlinkfork() is also dead code. >> Per the discussion [1], this unused function was left intentionally. >> But it's still dead code since

Re: Query is over 2x slower with jit=on

On April 18, 2018 9:50:48 AM PDT, Chapman Flack wrote: >On 04/18/2018 12:27 PM, Simon Riggs wrote: > >> Please change the name of the "JIT" parameter to something meaningful >> to humans before this gets too far into the wild. >> >> SSL is somewhat understandable because its not a Postgres-priv

Re: WIP: Covering + unique indexes.

I mostly agree with your patch, nice work, but I have some notices for your patch: 1) bt_target_page_check(): if (!P_RIGHTMOST(topaque) && !_bt_check_natts(state->rel, state->target, P_HIKEY)) Seems not very obvious: it looks like we don't need to check nattrs on rightmost page. Ok

Re: pgindent run soon?

If there are large refactoring or bug-fix patches that haven't landed yet, then it'd be appropriate to wait for those to get in, but I'm not aware of such at the moment. Pls, wait https://www.postgresql.org/message-id/9c63951d-7696-ecbb-b832-70db7ed3f39b%40sigaev.ru Thank you. -- Teodor Sigaev

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Wed, Apr 18, 2018 at 11:29 PM, Pavan Deolasee wrote: > > > On Tue, Apr 17, 2018 at 11:05 PM, Fujii Masao wrote: >> >> Hi, >> >> I'd like to propose to add $SUBJECT for performance improvement. >> >> When VACUUM tries to truncate the trailing empty pages, it scans >> shared_buffers >> to invali

Re: pgindent run soon?

Teodor Sigaev writes: >> If there are large refactoring or bug-fix patches that haven't landed >> yet, then it'd be appropriate to wait for those to get in, but I'm not >> aware of such at the moment. > Pls, wait > https://www.postgresql.org/message-id/9c63951d-7696-ecbb-b832-70db7ed3f39b%40sigae

Re: WIP: Covering + unique indexes.

(() On Wed, Apr 18, 2018 at 10:10 AM, Teodor Sigaev wrote: > I mostly agree with your patch, nice work, but I have some notices for your > patch: Thanks. > 1) > bt_target_page_check(): > if (!P_RIGHTMOST(topaque) && > !_bt_check_natts(state->rel, state->target, P_HIKEY)) > > Seems no

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Wed, Apr 18, 2018 at 10:50 PM, Fujii Masao wrote: > > > I'm not sure if it's safe to cancel forcibly VACUUM's truncation during > scaning shared_buffers. That scan happens after WAL-logging and before > the actual truncation. > > Ah ok. I misread your proposal. This is about the shared_buffers

Truncation failure in autovacuum results in data corruption (duplicate keys)

Hello, It seems to me that our customer might have hit an unresolved data corruption issue which is already known in this ML, but I can't figure out why this happened. I'd appreciate if you could give me your thoughts. Depending on the severity of this issue and the customer's request, I think I

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

Pavan Deolasee writes: > What if we remember the buffers as seen by count_nondeletable_pages() and > then just discard those specific buffers instead of scanning the entire > shared_buffers again? That's an idea. > Surely we revisit all to-be-truncated blocks before > actual truncation. So we al

Re: Deadlock in multiple CIC.

Alvaro Herrera writes: > Tom Lane wrote: >> Will look into that too. I'm not sure that adding extra expected >> outputs is sane, though --- might be best to just force the intended >> isolation level within those tests. > As I recall (not much, admittedly) that was one of the options we > consid

Re: Proposal: Adding json logging

On Sun, Apr 15, 2018 at 1:07 PM, Christophe Pettus wrote: >> On Apr 15, 2018, at 09:51, David Arnold wrote: >> 1. Throughout this vivid discussion a good portion of support has already >> been manifested for the need of a more structured (machine readable) logging >> format. There has been no s

Re: Truncation failure in autovacuum results in data corruption (duplicate keys)

"MauMau" writes: > However, I have a question. How does the truncation failure in > autovacuum lead to duplicate keys? The failed-to-be-truncated pages > should only contain dead tuples, so pg_dump's table scan should ignore > dead tuples in those pages. [ re-reads thread... ] The extra assump

Re: Proposal: Adding json logging

> On Apr 18, 2018, at 11:59, Robert Haas wrote: > > I'm not sure exactly how you intended to this comment, but it seems to > me that whether CSV is ease or hard to parse, somebody might > legitimately find JSON more convenient. Of course. The specific comment I was replying to made a couple of

Re: Query is over 2x slower with jit=on

On Wed, Apr 18, 2018 at 11:50 AM, Andres Freund wrote: > JIT has cost, and sometimes it's not beneficial. Here our heuristics > when to JIT appear to be a bit off. In the parallel world it's worse > because the JITing is duplicated for parallel workers atm. It seems like you're describing it as i

Re: Query is over 2x slower with jit=on

On April 18, 2018 12:16:35 PM PDT, Robert Haas wrote: >On Wed, Apr 18, 2018 at 11:50 AM, Andres Freund >wrote: >> JIT has cost, and sometimes it's not beneficial. Here our heuristics >> when to JIT appear to be a bit off. In the parallel world it's worse >> because the JITing is duplicated for

Re: Proposal: Adding json logging

Excellent phrasing (thanks to Christophe!): "There is a large class of log analysis tool out there that has trouble with multiline formats and we should be good ecosystem players" > I'm much less fussed by this in contrib/ (with the same concern you noted), at a minimum as an example of how to do

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

I wrote: > Pavan Deolasee writes: >> What if we remember the buffers as seen by count_nondeletable_pages() and >> then just discard those specific buffers instead of scanning the entire >> shared_buffers again? > That's an idea. BTW, before pushing too hard on any of this, we need to think about

Re: Proposal: Adding json logging

John W Higgins wrote: > On Sun, Apr 15, 2018 at 11:08 AM, David Arnold wrote: > > > >This would appear to solve multiline issues within Fluent. > > >https://docs.fluentd.org/v0.12/articles/parser_multiline > > > > I definitely looked at that, but what guarantees do I have that the > > sequenc

Re: Truncation failure in autovacuum results in data corruption (duplicate keys)

From: Tom Lane [ re-reads thread... ] The extra assumption you need in order to have trouble is that the blocks in question are dirty in shared buffers and have never been written to disk since their rows were deleted. Then the situation is that the page image on disk shows the rows as live, whil

Re: Truncation failure in autovacuum results in data corruption (duplicate keys)

"MauMau" writes: > I'd like to continue to think of a solution and create a patch, based > on the severity and how the customer will respond to our answer. I > have a feeling that we have to say it's a bit serious, since it > requires recovery from a base backup, not just rebuilding indexes. > Th

Re: WIP: Covering + unique indexes.

I don't understand. We do check the number of attributes on rightmost pages, but we do so separately, in the main loop. For every item that isn't the high key. Comment added, pls, verify. And refactored _bt_check_natts(), I hope, now it's a bit more readable. 4) BTreeTupSetNAtts - seems, it's

Re: WIP: Covering + unique indexes.

On Wed, Apr 18, 2018 at 1:32 PM, Teodor Sigaev wrote: >> I don't understand. We do check the number of attributes on rightmost >> pages, but we do so separately, in the main loop. For every item that >> isn't the high key. > > Comment added, pls, verify. And refactored _bt_check_natts(), I hope, n

Re: Truncation failure in autovacuum results in data corruption (duplicate keys)

I wrote: > Relation truncation throws away the page image in memory without ever > writing it to disk. Then, if the subsequent file truncate step fails, > we have a problem, because anyone who goes looking for that page will > fetch it afresh from disk and see the tuples as live. > There are WAL

Re: Should we add GUCs to allow partition pruning to be disabled?

On 18 April 2018 at 21:36, Ashutosh Bapat wrote: > On Wed, Apr 18, 2018 at 5:37 AM, David Rowley >> a) Disable run-time pruning during execution. >> b) Disable run-time pruning during planning. >> c) Both of the above. >> >> The differentiation of the above is important when you consider >> PREPAR

Re: pruning disabled for array, enum, record, range type partition keys

Amit Langote wrote: > On Thu, Apr 19, 2018 at 12:01 AM, Alvaro Herrera > wrote: > > Makes sense. Still, I was expecting that pruning of hash partitioning > > would also work for pseudotypes, yet it doesn't. > > It does? Aha, so it does. While staring at this new code, I was confused as to why

Re: WIP: Covering + unique indexes.

On Wed, Apr 18, 2018 at 1:45 PM, Peter Geoghegan wrote: > I suggest committing this patch as-is. Actually, I see one tiny issue with extra '*' characters here: > +* The number of attributes won't be explicitly represented if the > +* negative infinity tuple was generated

Re: Query is over 2x slower with jit=on

On Wed, Apr 18, 2018 at 3:29 PM, Andres Freund wrote: > Not convinced that that is true - the issue is more likely that JIT work in > workers is counted as execute time... Gotta add that somehow, not sure what > the best way would be. Oh, that does seem like something that should be fixed. If

Re: pruning disabled for array, enum, record, range type partition keys

Alvaro Herrera writes: > I now wonder if there's anything else that equivclass.c or indxpath.c > can teach us on this topic. I've been meaning to review this but have been a bit distracted. Will try to look tomorrow. regards, tom lane

Re: PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS

On 19/04/18 00:45, Craig Ringer wrote: I guarantee you that when you create a 100GB EBS volume on AWS EC2, you don't get 100GB of storage preallocated. AWS are probably pretty good about not running out of backing store, though. Some db folks (used to anyway) advise dd'ing to your freshly a

Adding an LWLockHeldByMe()-like function that reports if any buffer content lock is held

During recent review of the INCLUDE covering index patch, I pushed to formalize the slightly delicate assumptions that we make around how index_truncate_tuple() is called. It's natural to call index_truncate_tuple() during a page split, when a buffer lock is held. This is what we actually do in mos

Re: [HACKERS] Runtime Partition Pruning

On 19 April 2018 at 03:13, Robert Haas wrote: > On Mon, Apr 16, 2018 at 10:46 PM, David Rowley > wrote: >> I did go and start working on a patch to test how possible this would >> be and came up with the attached. I've left a stray >> MemoryContextStatsDetail call in there which does indicate tha

Re: [HACKERS] Runtime Partition Pruning

On 19 April 2018 at 12:04, David Rowley wrote: > insert into p select x,x from generate_Series(1,1000) x; > insert into t1 select x from generate_series(1,1000) x; Correction. These were meant to read: insert into p select x,x from generate_Series(1,1000) x; insert into t1 select x from gene

Re: Fix for documentation of Covering Indexes

On Wed, Apr 18, 2018 at 05:52:01AM -0400, Heikki Linnakangas wrote: > Committed, thanks! Thanks for the commit. -- Michael signature.asc Description: PGP signature

Re: Proposal: Adding json logging

On Wed, Apr 18, 2018 at 02:59:26PM -0400, Robert Haas wrote: > > Note that logging_collector should be enabled in postgresql.conf to > ensure consistent log outputs. As JSON strings are longer than normal > logs generated by PostgreSQL, this module increases the odds of malformed > log entrie

Re: Proposal: Adding json logging

On Wed, Apr 18, 2018 at 12:10:47PM -0700, Christophe Pettus wrote: > On Apr 18, 2018, at 11:59, Robert Haas wrote: >> For the record, I'm tentatively in favor of including something like >> this in contrib. > > I'm much less fussed by this in contrib/ (with the same concern you > noted), at minim

Re: PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS

On 19 April 2018 at 07:31, Mark Kirkwood wrote: > On 19/04/18 00:45, Craig Ringer wrote: > >> >> I guarantee you that when you create a 100GB EBS volume on AWS EC2, >> you don't get 100GB of storage preallocated. AWS are probably pretty >> good about not running out of backing store, though. >> >>

Re: Adding an LWLockHeldByMe()-like function that reports if any buffer content lock is held

On Wed, Apr 18, 2018 at 04:53:29PM -0700, Peter Geoghegan wrote: > It occurred to me that it would be nice to be able to > Assert(!AnyBufferLockHeldByMe()) at a certain point within > index_form_tuple(), to make sure that our assumptions hold. If > index_truncate_tuple() (or any other function) eve

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Wed, Apr 18, 2018 at 07:41:44PM +0530, Amit Kapila wrote: > I think it makes sense to pursue that approach, but it might be worth > considering some alternative till we have it. I remember last time > (in 2015) we have discussed some another solution [1] to this problem > (or similar) and we ha

Re: Adding an LWLockHeldByMe()-like function that reports if any buffer content lock is held

On Wed, Apr 18, 2018 at 5:46 PM, Michael Paquier wrote: > Personally, I favor approaches like that, because it allows to catch up > problems in using some APIs when people working on a patch miss any kind > of warning comments at the top of the function or within it which > summarize the condition

Re: Problem while setting the fpw with SIGHUP

On Wed, Apr 18, 2018 at 10:52:51AM -0400, Robert Haas wrote: > I would just document the risks. If the documentation says that you > can't rely on the value until after the next checkpoint, or whatever > the rule is, then I think we're fine. I don't think that we really > have the infrastructure

  1   2   >