Re: [HACKERS] GSoC 2017: weekly progress reports (week 4) and patch for hash index

On Sat, Apr 7, 2018 at 7:47 PM, Teodor Sigaev wrote: > Thanks to everyone, pushed > Thanks! -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com

Re: Problem while setting the fpw with SIGHUP

At Fri, 6 Apr 2018 17:59:58 +0530, Amit Kapila wrote in > On Fri, Apr 6, 2018 at 1:50 PM, Kyotaro HORIGUCHI > wrote: > > Hello. > > > > At Wed, 04 Apr 2018 17:26:46 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI > > wrote in > > <20180404.172646.238325981.horiguchi.kyot...@lab.ntt.co.jp> >

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

On 8 April 2018 at 22:47, Anthony Iliopoulos wrote: > On Sun, Apr 08, 2018 at 10:23:21PM +0100, Greg Stark wrote: >> On 8 April 2018 at 04:27, Craig Ringer wrote: >> > On 8 April 2018 at 10:16, Thomas Munro > > The question is, what should the kernel and application do in cases > where this is s

Re: Verbosity of genbki.pl

On 4/9/18, Teodor Sigaev wrote: >> 1. Print nothing at all. That's more in keeping with our modern >> build practices, but maybe it's too big a change? >> >> 2. Print just one message like "Generating postgres.bki and related >> files", and I guess a second one for fmgroids.h and related files. >

Re: [sqlsmith] Failed assertion on pfree() via perform_pruning_combine_step

Hi Andreas. On 2018/04/08 3:33, Andreas Seltenreich wrote: > Hi, > > testing with master at 039eb6e92f yielded another query triggering an > assertion. Thanks for the report. > Backtrace and query against the regression database below. > #0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/

Re: [sqlsmith] Failed assertion on pfree() via perform_pruning_combine_step

On 2018/04/09 17:50, Amit Langote wrote: > Attached fixes that. I see that Michael Paquier has added this to the > open items list. Thanks, Michael. > > https://wiki.postgresql.org/wiki/PostgreSQL_11_Open_Items#Open_Issues Oops, it was Tom who added that. Thank you! Regards, Amit

Re: [HACKERS] path toward faster partition pruning

Hi David. On 2018/04/09 12:48, David Rowley wrote: > While looking at the docs in [1], I saw that we still mention: > > 4. Ensure that the constraint_exclusion configuration parameter is not > disabled in postgresql.conf. If it is, queries will not be optimized > as desired. > > This is no longe

Re: Documentation for bootstrap data conversion

On 4/7/18, Tom Lane wrote: > John and I are probably both too close to the patch to be able to > review this documentation for clarity and usefulness, so if anyone > else wants to have a look, please comment. No argument there, but I did want to note some minor details: 1. reformat_dat_

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

Hi. I noticed that the newly added pruning does not work if the partition key is of one of the types that have a corresponding pseudo-type. -- array type list partition key create table arrpart (a int[]) partition by list (a); create table arrpart1 partition of arrpart for values in ('{1}'); crea

Re: postgres_fdw: perform UPDATE/DELETE .. RETURNING on a join directly

(2018/04/07 4:17), Andres Freund wrote: On 2018-03-05 17:07:10 -0500, Tom Lane wrote: Meanwhile, I'm back to wondering what could possibly have affected the planner's estimates, if pg_proc and pg_statistic didn't change. I confess bafflement ... but we've now eliminated the autovacuum- did-it th

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

On 2018/04/09 19:14, Amit Langote wrote: > Hi. > > I noticed that the newly added pruning does not work if the partition key > is of one of the types that have a corresponding pseudo-type. > > -- array type list partition key > create table arrpart (a int[]) partition by list (a); > create table

Re: Optimizing nested ConvertRowtypeExpr execution

At Fri, 6 Apr 2018 08:37:57 +0200, Pavel Stehule wrote in > 2018-04-06 8:21 GMT+02:00 Ashutosh Bapat : > > > On Tue, Apr 3, 2018 at 10:48 AM, Ashutosh Bapat > > wrote: > > >> > > >> Why is this done appropriately at ExecInitExpr() time, rather than at > > >> plan time? Seems like eval_const_ex

Re: [HACKERS] Add support for tuple routing to foreign partitions

(2018/04/07 8:25), Robert Haas wrote: On Thu, Apr 5, 2018 at 6:21 AM, Etsuro Fujita wrote: Attached is an updated version of the patch set plus the patch in [1]. Patch 0003_foreign-routing-fdwapi-6.patch can be applied on top of patch 0001_postgres-fdw-refactoring-6.patch and 0002_copy-from-ch

Re: Optimizing nested ConvertRowtypeExpr execution

On Mon, Apr 9, 2018 at 3:49 PM, Kyotaro HORIGUCHI wrote: > > I don't think it is not only on constatns. With the patch, > non-constants are .. getting a rather strange conversion. > > >> =# explain verbose select (a, b, c)::t1p1p1::t1p1::t1 from (select i, i * 2, >> i * 3 from generate_series(0,

Fixing a trivial typo in comment in rewriteManip.c

While I was looking on some patch, I happened to notice that there's a trivial typo in rewriteManip.c > a ConvertRowTypeExpr to map back to the rowtype expected by the expression. The correct name for "ConvertRowTypeExpr" is "ConvertRowtypeExpr" regards, -- Kyotaro Horiguchi NTT Open Source So

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

On 28/03/18 19:53, Teodor Sigaev wrote: Hi! I slightly modified test for clean demonstration of difference between fastupdate on and off. Also I added CheckForSerializableConflictIn() to fastupdate off codepath, but only in case of non-empty pending list. The next question what I see: why do no

Re: Optimizing nested ConvertRowtypeExpr execution

On Mon, Apr 9, 2018 at 3:53 PM, Ashutosh Bapat wrote: > On Mon, Apr 9, 2018 at 3:49 PM, Kyotaro HORIGUCHI > wrote: >> >> I don't think it is not only on constatns. With the patch, >> non-constants are .. getting a rather strange conversion. >> >> >>> =# explain verbose select (a, b, c)::t1p1p1::

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

On Mon, Apr 09, 2018 at 09:45:40AM +0100, Greg Stark wrote: > On 8 April 2018 at 22:47, Anthony Iliopoulos wrote: > > On Sun, Apr 08, 2018 at 10:23:21PM +0100, Greg Stark wrote: > >> On 8 April 2018 at 04:27, Craig Ringer wrote: > >> > On 8 April 2018 at 10:16, Thomas Munro > > > > The question

Re: Optimizing nested ConvertRowtypeExpr execution

At Mon, 9 Apr 2018 15:53:04 +0530, Ashutosh Bapat wrote in > On Mon, Apr 9, 2018 at 3:49 PM, Kyotaro HORIGUCHI > wrote: > > > > I don't think it is not only on constatns. With the patch, > > non-constants are .. getting a rather strange conversion. > > > > > >> =# explain verbose select (a, b

Re: Optimizing nested ConvertRowtypeExpr execution

At Mon, 9 Apr 2018 16:07:33 +0530, Ashutosh Bapat wrote in > On Mon, Apr 9, 2018 at 3:53 PM, Ashutosh Bapat > wrote: > > On Mon, Apr 9, 2018 at 3:49 PM, Kyotaro HORIGUCHI > > wrote: > >> > >> I don't think it is not only on constatns. With the patch, > >> non-constants are .. getting a rathe

Re: Optimizing nested ConvertRowtypeExpr execution

On Mon, Apr 9, 2018 at 4:29 PM, Kyotaro HORIGUCHI wrote: > At Mon, 9 Apr 2018 16:07:33 +0530, Ashutosh Bapat > wrote in > >> On Mon, Apr 9, 2018 at 3:53 PM, Ashutosh Bapat >> wrote: >> > On Mon, Apr 9, 2018 at 3:49 PM, Kyotaro HORIGUCHI >> > wrote: >> >> >> >> I don't think it is not only on

Re: Fixing a trivial typo in comment in rewriteManip.c

On 09/04/18 13:26, Kyotaro HORIGUCHI wrote: While I was looking on some patch, I happened to notice that there's a trivial typo in rewriteManip.c a ConvertRowTypeExpr to map back to the rowtype expected by the expression. The correct name for "ConvertRowTypeExpr" is "ConvertRowtypeExpr" Fix

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

Attached is a test case that demonstrates a case where we miss a serialization failure, when fastupdate is turned on concurrently. It works on v10, but fails to throw a serialization error on v11. Thank you for reserching! Proof of concept: diff --git a/src/backend/commands/tablecmds.c b/src/ba

Re: Commit 4dba331cb3 broke ATTACH PARTITION behaviour.

Added to the open items list. https://wiki.postgresql.org/wiki/PostgreSQL_11_Open_Items#Open_Issues On Tue, Apr 3, 2018 at 2:52 PM, Amit Langote wrote: > On 2018/04/03 14:45, Kyotaro HORIGUCHI wrote: > > Hello. > > > > At Mon, 2 Apr 2018 16:11:12 -0300, Alvaro Herrera < > alvhe...@alvh.no-ip.or

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

Ugh, I miss your last email where you another locking protocol. Reading. Teodor Sigaev wrote: Attached is a test case that demonstrates a case where we miss a serialization failure, when fastupdate is turned on concurrently. It works on v10, but fails to throw a serialization error on v11. Tha

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

On 9 April 2018 at 11:50, Anthony Iliopoulos wrote: > What you seem to be asking for is the capability of dropping > buffers over the (kernel) fence and idemnifying the application > from any further responsibility, i.e. a hard assurance > that either the kernel will persist the pages or it will

Re: Optimizing nested ConvertRowtypeExpr execution

At Mon, 9 Apr 2018 16:43:22 +0530, Ashutosh Bapat wrote in > On Mon, Apr 9, 2018 at 4:29 PM, Kyotaro HORIGUCHI > wrote: > > At Mon, 9 Apr 2018 16:07:33 +0530, Ashutosh Bapat > > wrote in > > > >> On Mon, Apr 9, 2018 at 3:53 PM, Ashutosh Bapat > >> wrote: > >> > On Mon, Apr 9, 2018 at 3:49

Re: Fixing a trivial typo in comment in rewriteManip.c

At Mon, 9 Apr 2018 14:22:33 +0300, Heikki Linnakangas wrote in <4ee784f4-a7c6-dced-0d4c-0a2ac5b87...@iki.fi> > On 09/04/18 13:26, Kyotaro HORIGUCHI wrote: > > While I was looking on some patch, I happened to notice that > > there's a trivial typo in rewriteManip.c > > > >> a ConvertRowTypeExpr t

Re: Optimizing nested ConvertRowtypeExpr execution

On Mon, Apr 9, 2018 at 5:34 PM, Kyotaro HORIGUCHI wrote: > > The new code doesn't seem to work as written. > >> arg = eval_const_expressions_mutator((Node *) cre->arg, >>context); >> >> /* >>* In case of a nested ConvertRowtypeExpr, we can convert th

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

On 9 April 2018 at 18:50, Anthony Iliopoulos wrote: > > There is a clear responsibility of the application to keep > its buffers around until a successful fsync(). The kernels > do report the error (albeit with all the complexities of > dealing with the interface), at which point the application

Re: [sqlsmith] Failed assertion in create_gather_path

On Sun, Apr 8, 2018 at 1:04 PM, Jeevan Chalke wrote: > Hi, > > At some places, I have observed that we are adding a partial path even when > rel's consider_parallel is false. Due to this, the partial path added has > parallel_safe set to false and then later in create_gather_path() assertion > fai

Re: [sqlsmith] Failed assertion in create_gather_path

On Mon, Apr 9, 2018 at 5:52 PM, Amit Kapila wrote: > On Sun, Apr 8, 2018 at 1:04 PM, Jeevan Chalke > wrote: >> Hi, >> >> At some places, I have observed that we are adding a partial path even when >> rel's consider_parallel is false. Due to this, the partial path added has >> parallel_safe set to

Re: Boolean partitions syntax

On 4/7/18 11:16, Jonathan S. Katz wrote: > The last line yielding: > >     ERROR:  syntax error at or near "TRUE" >     LINE 3: FOR VALUES IN (TRUE); > > [Omitted from example: the “records_active” partition] > > I’m glad to see this was added to the open items. I would strongly > suggest fixing

Re: pgsql: Store 2PC GID in commit/abort WAL recs for logical decoding

On 28/03/18 19:47, Simon Riggs wrote: Store 2PC GID in commit/abort WAL recs for logical decoding This forgot to update the comments in xl_xact_commit and xl_xact_abort, for the new fields. + + if (parsed->xinfo & XACT_XINFO_HAS_GID) + { + i

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

On Mon, Apr 09, 2018 at 01:03:28PM +0100, Geoff Winkless wrote: > On 9 April 2018 at 11:50, Anthony Iliopoulos wrote: > > > What you seem to be asking for is the capability of dropping > > buffers over the (kernel) fence and idemnifying the application > > from any further responsibility, i.e. a

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

On Mon, Apr 09, 2018 at 08:16:38PM +0800, Craig Ringer wrote: > > I'd like a middle ground where the kernel lets us register our interest and > tells us if it lost something, without us having to keep eight million FDs > open for some long period. "Tell us about anything that happens under > pgdat

Re: Transform for pl/perl

Peter Eisentraut writes: > On 3/15/18 03:46, Pavel Stehule wrote: >> It looks well >> >> the patch has tests and doc, >> there are not any warnings or compilation issues >> all tests passed >> >> I'll mark this patch as ready for commiter > > committed I played around with this a bit, and noti

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

On 04/09/2018 02:31 PM, Anthony Iliopoulos wrote: > On Mon, Apr 09, 2018 at 01:03:28PM +0100, Geoff Winkless wrote: >> On 9 April 2018 at 11:50, Anthony Iliopoulos wrote: >> >>> What you seem to be asking for is the capability of dropping >>> buffers over the (kernel) fence and idemnifying the app

Re: Boolean partitions syntax

> On Apr 9, 2018, at 8:28 AM, Peter Eisentraut > wrote: > > On 4/7/18 11:16, Jonathan S. Katz wrote: >> The last line yielding: >> >> ERROR: syntax error at or near "TRUE" >> LINE 3: FOR VALUES IN (TRUE); >> >> [Omitted from example: the “records_active” partition] >> >> I’m glad to

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

On 04/09/2018 12:29 AM, Bruce Momjian wrote: > > An crazy idea would be to have a daemon that checks the logs and > stops Postgres when it seems something wrong. > That doesn't seem like a very practical way. It's better than nothing, of course, but I wonder how would that work with containers (

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

At 2018-04-09 15:42:35 +0200, tomas.von...@2ndquadrant.com wrote: > > On 04/09/2018 12:29 AM, Bruce Momjian wrote: > > > > An crazy idea would be to have a daemon that checks the logs and > > stops Postgres when it seems something wrong. > > > > That doesn't seem like a very practical way. Not

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

On 04/09/2018 04:00 AM, Craig Ringer wrote: > On 9 April 2018 at 07:16, Andres Freund > wrote: >   > > > I think the danger presented here is far smaller than some of the > statements in this thread might make one think. > > > Clearly it's not happening a hug

Re: [sqlsmith] Failed assertion on pfree() via perform_pruning_combine_step

Hello, Amit Langote wrote: > I have reproduced this and found that the problem is that > perform_pruning_combine_step forgets to *copy* the bitmapset of the first > step in the handling of an COMBINE_INTERSECT step. Pushed, thanks Amit and Andreas! -- Álvaro Herrerahttps://www.

Re: Transform for pl/perl

ilm...@ilmari.org (Dagfinn Ilmari =?utf-8?Q?Manns=C3=A5ker?=) writes: > I tried fixing this by adding an 'if (SvUV(in))' clause to > SV_to_JsonbValue, but I couldn't find a function to create a numeric > value from an uint64. If it's not possible, should we error on UVs > greater than PG_INT64_MAX

Re: Boolean partitions syntax

"Jonathan S. Katz" writes: > +1 based on running the above scenario on my 10.3 instance and > receiving the same error. Is there a chance the fix could make it into > 10.4 then? It's premature to discuss whether this could be back-patched when we haven't got an acceptable patch yet.

RE: WIP: Covering + unique indexes.

Hi, I tested this feature and found a document shortage in the columns added to the pg_constraint catalog. The attached patch will add the description of the 'conincluding' column to the manual of the pg_constraint catalog. Regards, Noriyoshi Shinoda -Original Message- From: Teodor Si

Re: Boolean partitions syntax

> On Apr 9, 2018, at 10:06 AM, Tom Lane wrote: > > "Jonathan S. Katz" writes: >> +1 based on running the above scenario on my 10.3 instance and >> receiving the same error. Is there a chance the fix could make it into >> 10.4 then? > > It's premature to discuss whether this could be back-patc

Re: Documentation for bootstrap data conversion

John Naylor writes: > On 4/7/18, Tom Lane wrote: >> reformat_dat_file.pl preserves blank lines >> and comment lines as-is. > As it is now, it will actually collapse consecutive blank lines into > one. I maintain that was necessary during conversion to get some > semblance of co

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

On Mon, Apr 09, 2018 at 03:33:18PM +0200, Tomas Vondra wrote: > > We already have dirty_bytes and dirty_background_bytes, for example. I > don't see why there couldn't be another limit defining how much dirty > data to allow before blocking writes altogether. I'm sure it's not that > simple, but yo

Re: WIP: Covering + unique indexes.

Hi! On Mon, Apr 9, 2018 at 5:07 PM, Shinoda, Noriyoshi < noriyoshi.shin...@hpe.com> wrote: > I tested this feature and found a document shortage in the columns added > to the pg_constraint catalog. > The attached patch will add the description of the 'conincluding' column > to the manual of the p

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

Amit Langote writes: > I noticed that the newly added pruning does not work if the partition key > is of one of the types that have a corresponding pseudo-type. While I don't recall the details due to acute caffeine shortage, there are specific coding patterns that are used in the planner (e.g. i

RE: WIP: Covering + unique indexes.

Hi! Thank you for your response. I think that it is good with your proposal. Regards, Noriyoshi Shinoda From: Alexander Korotkov [mailto:a.korot...@postgrespro.ru] Sent: Monday, April 9, 2018 11:22 PM To: Shinoda, Noriyoshi Cc: PostgreSQL Hackers ; Teodor Sigaev ; Peter Geoghegan ; Jeff Janes

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

Hi! 1. Why do we lock all posting tree pages, even though they all represent the same value? Isn't it enough to lock the root of the posting tree? 2. Why do we lock any posting tree pages at all, if we lock the entry tree page anyway? Isn't the lock on the entry tree page sufficient to cover t

Re: WIP: Covering + unique indexes.

Thanks to both of you, pushed Shinoda, Noriyoshi wrote: Hi! Thank you for your response. I think that it is good with your proposal. Regards, Noriyoshi Shinoda *From:*Alexander Korotkov [mailto:a.korot...@postgrespro.ru] *Sent:* Monday, April 9, 2018 11:22 PM *To:* Shinoda, Noriyoshi *Cc:*

Re: lazy detoasting

On 04/08/2018 02:01 AM, Andrew Gierth wrote: > Chapman> (d) some other reason I haven't thought of ? > > It has to be pushed as the active snapshot so that it's the snapshot > that the executor uses to run the query to populate the tuplestore which > becomes the "held" portal content. That seem

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

Hi! Thank you for taking a look at this patch. I really appreciate your attention over complex subjects like this. On Mon, Apr 9, 2018 at 1:33 PM, Heikki Linnakangas wrote: > On 28/03/18 19:53, Teodor Sigaev wrote: > >> As I understand, scan should lock any visited page, but now it's true >> o

Re: Transform for pl/perl

Tom Lane writes: > ilm...@ilmari.org (Dagfinn Ilmari =?utf-8?Q?Manns=C3=A5ker?=) writes: >> I tried fixing this by adding an 'if (SvUV(in))' clause to >> SV_to_JsonbValue, but I couldn't find a function to create a numeric >> value from an uint64. If it's not possible, should we error on UVs >>

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

Heikki Linnakangas wrote: > Remember, the purpose of predicate locks is to lock key ranges, not physical > pages or tuples in the index. We use leaf pages as handy shortcut for "any > key value that would belong on this page", but it is just an implementation > detail. Hmm ... so, thinking about

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

> 9 апр. 2018 г., в 19:50, Teodor Sigaev написал(а): >> >> 3. Why do we *not* lock the entry leaf page, if there is no match? We still >> need a lock to remember that we probed for that value and there was no >> match, so that we conflict with a tuple that might be inserted later. >> At least

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

Alvaro Herrera wrote: Heikki Linnakangas wrote: Remember, the purpose of predicate locks is to lock key ranges, not physical pages or tuples in the index. We use leaf pages as handy shortcut for "any key value that would belong on this page", but it is just an implementation detail. Hmm ...

Re: Transform for pl/perl

ilm...@ilmari.org (Dagfinn Ilmari =?utf-8?Q?Manns=C3=A5ker?=) writes: > Tom Lane writes: >> I think you'd have to convert to text and back. That's kind of icky, >> but it beats failing. > I had a look, and that's what the PL/Python transform does. Attached is > a patch that does that for PL/Per

Re: [HACKERS] path toward faster partition pruning

On Fri, Apr 6, 2018 at 11:41 PM, Tom Lane wrote: > David Rowley writes: >> Sounds like you're saying that if we have too many alternative files >> then there's a chance that one could pass by luck. > > Yeah, exactly: it passed, but did it pass for the right reason? > > If there's just two expecte

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

On 9 April 2018 at 15:22, Anthony Iliopoulos wrote: > On Mon, Apr 09, 2018 at 03:33:18PM +0200, Tomas Vondra wrote: >> > Sure, there could be knobs for limiting how much memory such "zombie" > pages may occupy. Not sure how helpful it would be in the long run > since this tends to be highly applic

Re: [HACKERS] [PATCH] Incremental sort

On Sat, Apr 7, 2018 at 11:57 PM, Tomas Vondra wrote: > On 04/07/2018 06:23 PM, Tom Lane wrote: > > Teodor Sigaev writes: > >>> I dunno, how would you estimate whether this is actually a win or not? > >>> I don't think our model of sort costs is anywhere near refined enough > >>> or accurate enou

Re: [HACKERS] Runtime Partition Pruning

On Sat, Apr 7, 2018 at 5:13 PM, Alvaro Herrera wrote: > I had reservations about a relation_open() in the new executor code. It > seemed a bit odd; we don't have any other relation_open in the executor > anywhere. However, setting up the pruneinfo needs some stuff from > relcache that I don't se

Re: [WIP PATCH] Index scan offset optimisation using visibility map

Hi! The work on the patch goes on, where was some discussion of this patch off-list with author. Advise-request is still actual. I think that we should move this patch to next CF. So I'm marking patch as needs review. Best regards, Andrey Borodin.

Optimization of range queries

Hi hackers, Postgres optimizer is not able to build efficient execution plan for the following query: explain select * from  people_raw where not ("ID"<2068113880 AND "INN" is not null) and "ID"<=2068629726 AND "INN" is not null; QUERY PLAN --

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

On Mon, Apr 9, 2018 at 8:16 AM, Craig Ringer wrote: > In the mean time, I propose that we fsync() on close() before we age FDs out > of the LRU on backends. Yes, that will hurt throughput and cause stalls, but > we don't seem to have many better options. At least it'll only flush what we > actuall

Re: Optimization of range queries

Hi! 12 years ago I proposed patch to which could "union" OR clauses into one range clause if it's possible. In that time pgsql could not use IS NULL as index clause, so patch doesn't support that https://www.postgresql.org/message-id/flat/45742C51.9020602%40sigaev.ru option number 4), all ot

Re: pgsql: Merge catalog/pg_foo_fn.h headers back into pg_foo.h headers.

Michael Paquier writes: > On Sun, Apr 08, 2018 at 11:05:09PM -0400, Tom Lane wrote: >> Hm. I'd tested "make -j all", but not going directly to "install". >> Does it help if you add >> $(SUBDIRS:%=install-%-recurse): | submake-generated-headers >> to src/Makefile? > That takes care of the problem

Re: Online enabling of checksums

On Sat, Apr 7, 2018 at 6:22 PM, Andres Freund wrote: > Hi, > > On 2018-04-07 08:57:03 +0200, Magnus Hagander wrote: > > Note however that I'm sans-laptop until Sunday, so I will revert it then > or > > possibly Monday. > > I'll deactive the isolationtester tests until then. They've been > intermi

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

On 09/04/18 18:04, Alvaro Herrera wrote: Heikki Linnakangas wrote: Remember, the purpose of predicate locks is to lock key ranges, not physical pages or tuples in the index. We use leaf pages as handy shortcut for "any key value that would belong on this page", but it is just an implementation

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

On 04/09/2018 09:45 AM, Robert Haas wrote: On Mon, Apr 9, 2018 at 8:16 AM, Craig Ringer wrote: In the mean time, I propose that we fsync() on close() before we age FDs out of the LRU on backends. Yes, that will hurt throughput and cause stalls, but we don't seem to have many better options. At

Re: Documentation for bootstrap data conversion

On 4/9/18, Tom Lane wrote: >> As it is now, it will actually collapse consecutive blank lines into >> one. I maintain that was necessary during conversion to get some >> semblance of consistency, but now it may not be a good idea to tie >> developers' hands in surprising ways if they want double b

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

On 09. 04. 2018 15:42, Tomas Vondra wrote: > On 04/09/2018 12:29 AM, Bruce Momjian wrote: >> An crazy idea would be to have a daemon that checks the logs and >> stops Postgres when it seems something wrong. >> > That doesn't seem like a very practical way. It's better than nothing, > of course, but

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

On 09/04/18 18:21, Andrey Borodin wrote: 9 апр. 2018 г., в 19:50, Teodor Sigaev написал(а): 3. Why do we *not* lock the entry leaf page, if there is no match? We still need a lock to remember that we probed for that value and there was no match, so that we conflict with a tuple that might be

Re: Verbosity of genbki.pl

Hi, On 2018-04-08 13:33:42 -0400, Tom Lane wrote: > Traditionally genbki.pl has printed "Writing foo" for every file > it writes out. > 2. Print just one message like "Generating postgres.bki and related > files", and I guess a second one for fmgroids.h and related files. +0.5. - Andres

Re: [HACKERS] Runtime Partition Pruning

Robert Haas wrote: > On Sat, Apr 7, 2018 at 5:13 PM, Alvaro Herrera > wrote: > > I had reservations about a relation_open() in the new executor code. It > > seemed a bit odd; we don't have any other relation_open in the executor > > anywhere. However, setting up the pruneinfo needs some stuff f

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

> On Apr 9, 2018, at 10:26 AM, Joshua D. Drake wrote: > We have plenty of YEARS of people not noticing this issue I disagree. I have noticed this problem, but blamed it on other things. For over five years now, I have had to tell customers not to use thin provisioning, and I have had to add co

Re: [HACKERS] Runtime Partition Pruning

On Mon, Apr 9, 2018 at 2:28 PM, Alvaro Herrera wrote: > Robert Haas wrote: >> On Sat, Apr 7, 2018 at 5:13 PM, Alvaro Herrera >> wrote: >> > I had reservations about a relation_open() in the new executor code. It >> > seemed a bit odd; we don't have any other relation_open in the executor >> > a

Re: [HACKERS] Runtime Partition Pruning

Robert Haas wrote: > On Mon, Apr 9, 2018 at 2:28 PM, Alvaro Herrera > wrote: > > Robert Haas wrote: > >> I don't get this. The executor surely had to (and did) open all of > >> the relations somewhere even before this patch. > > > > I was worried that this coding could be seen as breaking modul

Re: Documentation for bootstrap data conversion

John Naylor writes: > On 4/9/18, Tom Lane wrote: >> I did note that in some internal comments, but forgot it when writing >> this. I agree that now that the conversion is done, it'd be better >> to remove that special case. Would you send a patch for that? > Sure, attached. Pushed, thanks. I

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

On Mon, Apr 9, 2018 at 12:45 PM, Robert Haas wrote: > Ouch. If a process exits -- say, because the user typed \q into psql > -- then you're talking about potentially calling fsync() on a really > large number of file descriptor flushing many gigabytes of data to > disk. And it may well be that y

Re: Verbosity of genbki.pl

Andres Freund writes: > On 2018-04-08 13:33:42 -0400, Tom Lane wrote: >> Traditionally genbki.pl has printed "Writing foo" for every file >> it writes out. >> 2. Print just one message like "Generating postgres.bki and related >> files", and I guess a second one for fmgroids.h and related files.

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

Hi, On 2018-04-09 15:02:11 -0400, Robert Haas wrote: > I think the simplest technological solution to this problem is to > rewrite the entire backend and all supporting processes to use > O_DIRECT everywhere. To maintain adequate performance, we'll have to > write a complete I/O scheduling system

Re: using index or check in ALTER TABLE SET NOT NULL

Hello I notice my patch does not apply again. Here is update to current HEAD regards, Sergeidiff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index bd22627..db98a98 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -215,8 +21

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

On 04/09/2018 08:29 PM, Mark Dilger wrote: > >> On Apr 9, 2018, at 10:26 AM, Joshua D. Drake wrote: > >> We have plenty of YEARS of people not noticing this issue > > I disagree. I have noticed this problem, but blamed it on other things. > For over five years now, I have had to tell custome

Re: Fix pg_rewind which can be run as root user

On Mon, Apr 9, 2018 at 7:11 AM, Michael Paquier wrote: > Hi all, > > I was just going through pg_rewind's code, and noticed the following > pearl: > /* > * Don't allow pg_rewind to be run as root, to avoid overwriting the > * ownership of files in the data directory. We need only ch

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

On Mon, Apr 9, 2018 at 12:13 PM, Andres Freund wrote: > Let's lower the pitchforks a bit here. Obviously a grand rewrite is > absurd, as is some of the proposed ways this is all supposed to > work. But I think the case we're discussing is much closer to a near > irresolvable corner case than anyt

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

On Mon, Apr 09, 2018 at 04:29:36PM +0100, Greg Stark wrote: > Honestly I don't think there's *any* way to use the current interface > to implement reliable operation. Even that embedded database using a > single process and keeping every file open all the time (which means > file descriptor limits

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

On 2018-04-09 21:26:21 +0200, Anthony Iliopoulos wrote: > What about having buffered IO with implied fsync() atomicity via > O_SYNC? You're kidding, right? We could also just add sleep(30)'s all over the tree, and hope that that'll solve the problem. There's a reason we don't permanently fsync e

Re: Fix pg_rewind which can be run as root user

On Mon, Apr 9, 2018 at 12:23 PM, Magnus Hagander wrote: > Seems simple enough and the right hting to do, but I wonder if we should > really backpatch it. Yes, the behaviour is not great now, but there is also > a non-zero risk of breaking peoples automated failover scripts of we > backpatch it, is

Re: Fix pg_rewind which can be run as root user

Magnus Hagander writes: > Seems simple enough and the right hting to do, but I wonder if we should > really backpatch it. Yes, the behaviour is not great now, but there is also > a non-zero risk of breaking peoples automated failover scripts of we > backpatch it, isn't it? Yeah, I'd vote against

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

On April 9, 2018 12:26:21 PM PDT, Anthony Iliopoulos wrote: >I honestly do not expect that keeping around the failed pages will >be an acceptable change for most kernels, and as such the >recommendation >will probably be to coordinate in userspace for the fsync(). Why is that required? You cou

Re: Fix pg_rewind which can be run as root user

On Mon, Apr 9, 2018 at 9:31 PM, Tom Lane wrote: > Magnus Hagander writes: > > Seems simple enough and the right hting to do, but I wonder if we should > > really backpatch it. Yes, the behaviour is not great now, but there is > also > > a non-zero risk of breaking peoples automated failover scri

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

On Mon, Apr 09, 2018 at 09:31:56AM +0800, Craig Ringer wrote: > You could make the argument that it's OK to forget if the entire file > system goes away. But actually, why is that ok? I was going to say that it'd be okay to clear error flag on umount, since any opened files would prevent unmountin

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

On Mon, Apr 09, 2018 at 12:29:16PM -0700, Andres Freund wrote: > On 2018-04-09 21:26:21 +0200, Anthony Iliopoulos wrote: > > What about having buffered IO with implied fsync() atomicity via > > O_SYNC? > > You're kidding, right? We could also just add sleep(30)'s all over the > tree, and hope tha

Re: Warnings and uninitialized variables in TAP tests

On Mon, Apr 9, 2018 at 3:15 AM, Michael Paquier wrote: > Hi all, > > While looking at the output of the TAP tests, I have seen warnings like > the following: > Use of uninitialized value $target_lsn in concatenation (.) or string at > /home/foo/git/postgres/src/bin/pg_rewind/../../../src/ > test/

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

On 04/09/2018 04:22 PM, Anthony Iliopoulos wrote: > On Mon, Apr 09, 2018 at 03:33:18PM +0200, Tomas Vondra wrote: >> >> We already have dirty_bytes and dirty_background_bytes, for example. I >> don't see why there couldn't be another limit defining how much dirty >> data to allow before blocking wr

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

On Mon, Apr 09, 2018 at 12:37:03PM -0700, Andres Freund wrote: > > > On April 9, 2018 12:26:21 PM PDT, Anthony Iliopoulos > wrote: > > >I honestly do not expect that keeping around the failed pages will > >be an acceptable change for most kernels, and as such the > >recommendation > >will prob

  1   2   3   4   >