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

2018-07-12 Thread Fabien COELHO
I don't understand the 0.5 second rule. For the tests, we only need to ensure that at least one progress report is printed, right? [...] I still don't understand. Let's look at the code: if (progress && thread->tid == 0) { ... if (last_report == thread_start || now -

Re: assert in nested SQL procedure call in current HEAD

2018-07-12 Thread Peter Eisentraut
On 12.06.18 18:47, Andrew Gierth wrote: > While testing this, I ran into another semi-related issue: > shmem_exit_inprogress isn't ever being cleared in the postmaster, which > means that if you ever have a crash-restart, any attempt to do a > rollback in a procedure will then crash or get some

Re: requested timeline ... does not contain minimum recovery point ...

2018-07-12 Thread Christophe Pettus
> On Jul 12, 2018, at 10:29, Andres Freund wrote: > > This needs a lot more information before somebody can reasonably act on > it. Happy to provide, of course! The two relevant hosts are "Ash" and "Chi". As mentioned, they've been flipped back and forth repeatedly using pg_rewind: One

Re: [WIP] [B-Tree] Retail IndexTuple deletion

2018-07-12 Thread Peter Geoghegan
On Tue, Jul 3, 2018 at 5:17 AM, Andrey V. Lepikhov wrote: > Done. > Attachment contains an update for use v.2 of the 'Ensure nbtree leaf tuple > keys are always unique' patch. My v3 is still pending, but is now a lot better than v2. There were bugs in v2 that were fixed. One area that might be

Re: [HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option for constraints

2018-07-12 Thread Robbie Harwood
Nico Williams writes: > Attached is an additional patch, as well as a new, rebased patch. > > This includes changes responsive to Álvaro Herrera's commentary about > the SET CONSTRAINTS manual page. This patch looks good to me. +1; Álvaro, please update the CF entry when you're also satisfied.

Re: When use prepared protocol, transaction will hold backend_xmin until the end of the transaction.

2018-07-12 Thread Tom Lane
chenhj writes: > When execute sql with prepared protocol, read committed transaction will hold > backend_xmin until the end of the transaction. No, just till the active portal is dropped. In the case you show, the issue is that libpq doesn't bother to issue an explicit Close Portal message,

Re: Cannot dump foreign key constraints on partitioned table

2018-07-12 Thread Alvaro Herrera
On 2018-Jul-12, Michael Paquier wrote: > Changing pg_class.relhastriggers is out of scope because as far as I > know partitioned tables have no triggers, so the current value is > correct, and that would be a catalog change at this stage which would > cause any existing deployments of v11 to

Re: Failed assertion due to procedure created with SECURITY DEFINER option

2018-07-12 Thread Jonathan S. Katz
> On Jul 4, 2018, at 3:43 AM, Peter Eisentraut > wrote: > > On 03.07.18 19:20, Andres Freund wrote: >> On 2018-06-29 10:19:17 -0700, Andres Freund wrote: >>> Hi, >>> >>> On 2018-06-29 13:56:12 +0200, Peter Eisentraut wrote: On 6/29/18 13:07, amul sul wrote: > This happens because of

Re: Temporary WAL segments files not cleaned up after an instance crash

2018-07-12 Thread Michael Paquier
On Thu, Jul 12, 2018 at 03:40:43PM +0300, Heikki Linnakangas wrote: > Sure. Thanks for the reviews, I have pushed the patch after moving the elog() call and changing the logs to mention "WAL segments" instead of "WAL files". -- Michael signature.asc Description: PGP signature

Re: Cache invalidation after authentication (on-the-fly role creation)

2018-07-12 Thread Tom Lane
Thomas Munro writes: > On Thu, Jul 5, 2018 at 9:35 AM, Tom Lane wrote: >> That seems like a *really* ad-hoc place to put it. Why should it be >> there, and not (say) somewhere inside InitializeSessionUserId, or maybe >> (also?) inside PerformAuthentication? Why do the existing call sites for

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

2018-07-12 Thread Fabien COELHO
Indeed… but then throttling would not be tested:-) The point of the test is to exercise all time-related options, including throttling with a reasonable small value. Ok. I don't think that's really worthwhile. If we add some code that only runs in testing, then we're not really testing the

RE: Locking B-tree leafs immediately in exclusive mode

2018-07-12 Thread Imai, Yoshikazu
On Mon, July 9, 2018 at 5:25 PM, Simon Riggs wrote: > Please can you check insertion with the index on 2 keys > 1st key has 10,000 values > 2nd key has monotonically increasing value from last 1st key value > > So each session picks one 1st key value > Then each new INSERTion is a higher value of

Re: requested timeline ... does not contain minimum recovery point ...

2018-07-12 Thread Christophe Pettus
> On Jul 12, 2018, at 17:52, Michael Paquier wrote: > Wild guess: you did not issue a checkpoint on the promoted standby > before running pg_rewind. I don't believe a manual checkpoint was done on the target (promoted standby, new master), but it did one as usual during startup after the

Re: pg_create_logical_replication_slot returns text instead of name

2018-07-12 Thread Masahiko Sawada
On Fri, Jul 13, 2018 at 11:22 AM, Michael Paquier wrote: > On Fri, Jul 13, 2018 at 11:14:01AM +0900, Masahiko Sawada wrote: >> Hmm, I'm also not sure about the policy of usage of name data type for >> columns that show an object identifier on external servers. There is a >> similar case; we have

How to make partitioning scale better for larger numbers of partitions

2018-07-12 Thread Kato, Sho
Hi, I benchmarked on a RANGE partitioned table with 1.1k leaf partitions and no sub-partitioned tables. But, statement latencies on a partitioned table is much slower than on a non-partitioned table. UPDATE latency is 210 times slower than a non-partitioned table. SELECT latency is 36 times

Re: Shared buffer access rule violations?

2018-07-12 Thread Tom Lane
Asim R P writes: > On Tue, Jul 10, 2018 at 8:33 PM, Tom Lane wrote: >> Asim R P writes: >>> One can find several PageInit() calls with no content lock held. See, >>> for example: >>> fill_seq_with_data() >> That would be for a relation that no one else can even see yet, no? > Yes, when the

Re: ON CONFLICT DO NOTHING on pg_dump

2018-07-12 Thread Thomas Munro
On Fri, Jul 13, 2018 at 12:33 PM, Ideriha, Takeshi wrote: >>+Add ON CONFLICT DO NOTHING clause in the INSERT commands. >> >>I think this would be better as: Add ON CONFLICT DO >>NOTHING to >>INSERT commands. > > Agreed. > >>+printf(_(" --on-conflict-do-nothing dump data as

Re: pg_create_logical_replication_slot returns text instead of name

2018-07-12 Thread Masahiko Sawada
On Fri, Jul 13, 2018 at 9:48 AM, Michael Paquier wrote: > On Thu, Jul 12, 2018 at 10:18:53PM +0900, Michael Paquier wrote: >> That's a small thing, but I agree with you. As far as I can see slot >> names are always mapped with the name type. I'll push that tomorrow if >> there are no

Re: Concurrency bug in UPDATE of partition-key

2018-07-12 Thread Amit Kapila
On Thu, Jul 12, 2018 at 10:14 PM, Andres Freund wrote: > On 2018-07-11 09:16:33 -0400, Alvaro Herrera wrote: >> On 2018-Jul-11, Amit Kapila wrote: >> >> > Attached, please find an updated patch based on comments by Alvaro. >> > See, if this looks okay to you guys. >> >> LGTM as far as my previous

Re: Cannot dump foreign key constraints on partitioned table

2018-07-12 Thread Michael Paquier
On Thu, Jul 12, 2018 at 02:45:37PM -0400, Alvaro Herrera wrote: > Thanks, looks good. I propose to add following pg_dump test to ensure > this stays fixed. Thanks for adding the test. I was looking at a good way to add a test but could not come up with something which can be summed up with one

Re: requested timeline ... does not contain minimum recovery point ...

2018-07-12 Thread Andres Freund
On 2018-07-12 19:22:50 -0700, Christophe Pettus wrote: > > > On Jul 12, 2018, at 17:52, Michael Paquier wrote: > > Wild guess: you did not issue a checkpoint on the promoted standby > > before running pg_rewind. > > I don't believe a manual checkpoint was done on the target (promoted standby,

Re: [PATCH] Timestamp for a XLOG_BACKUP_END WAL-record

2018-07-12 Thread Andrey V. Lepikhov
On 10.07.2018 22:26, Fujii Masao wrote: On Tue, Jul 10, 2018 at 6:41 PM, Andrey V. Lepikhov wrote: On 10.07.2018 06:45, Andres Freund wrote: Hi, On 2018-07-10 06:41:32 +0500, Andrey V. Lepikhov wrote: This functionality is needed in practice when we have to determine a recovery time

Re: Vacuum: allow usage of more than 1GB of work mem

2018-07-12 Thread Alvaro Herrera
On 2018-Jul-12, Andrew Dunstan wrote: > I fully understand. I think this needs to go back to "Waiting on Author". Why? Heikki's patch applies fine and passes the regression tests. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA,

RE: ON CONFLICT DO NOTHING on pg_dump

2018-07-12 Thread Ideriha, Takeshi
Hi, thanks for the revision. > >+Add ON CONFLICT DO NOTHING clause in the INSERT commands. > >I think this would be better as: Add ON CONFLICT DO NOTHING >to >INSERT commands. Agreed. >+printf(_(" --on-conflict-do-nothing dump data as INSERT >commands with ON CONFLICT DO

Re: Vacuum: allow usage of more than 1GB of work mem

2018-07-12 Thread Andrew Dunstan
On 07/12/2018 12:38 PM, Claudio Freire wrote: On Thu, Jul 12, 2018 at 10:44 AM Andrew Dunstan wrote: On 04/06/2018 08:00 PM, Claudio Freire wrote: On Fri, Apr 6, 2018 at 5:25 PM, Claudio Freire wrote: On Fri, Apr 6, 2018 at 10:39 AM, Heikki Linnakangas wrote: On 06/04/18 01:59,

Re: pg_create_logical_replication_slot returns text instead of name

2018-07-12 Thread Michael Paquier
On Thu, Jul 12, 2018 at 10:18:53PM +0900, Michael Paquier wrote: > That's a small thing, but I agree with you. As far as I can see slot > names are always mapped with the name type. I'll push that tomorrow if > there are no objections. Pushed, with a catalog version bump. While double-checking

Re: [HACKERS] WAL logging problem in 9.4.3?

2018-07-12 Thread Michael Paquier
On Thu, Jul 12, 2018 at 05:12:21PM +0300, Heikki Linnakangas wrote: > Doesn't have to be a trigger, could be a CHECK constraint, datatype input > function, etc. Admittedly, having a datatype input function that inserts to > the table is worth a "huh?", but I'm feeling very confident that we can >

Re: pg_create_logical_replication_slot returns text instead of name

2018-07-12 Thread Michael Paquier
On Fri, Jul 13, 2018 at 11:14:01AM +0900, Masahiko Sawada wrote: > Hmm, I'm also not sure about the policy of usage of name data type for > columns that show an object identifier on external servers. There is a > similar case; we have the pubname in pg_subscritpion as name type > whereas the

Re: patch to allow disable of WAL recycling

2018-07-12 Thread Thomas Munro
On Thu, Jul 12, 2018 at 10:52 PM, Tomas Vondra wrote: > I don't follow Alvaro's reasoning, TBH. There's a couple of things that > confuse me ... > > I don't quite see how reusing WAL segments actually protects against full > filesystem? On "traditional" filesystems I would not expect any

Re: requested timeline ... does not contain minimum recovery point ...

2018-07-12 Thread Christophe Pettus
> On Jul 12, 2018, at 19:22, Christophe Pettus wrote: > > >> On Jul 12, 2018, at 17:52, Michael Paquier wrote: >> Wild guess: you did not issue a checkpoint on the promoted standby >> before running pg_rewind. > > I don't believe a manual checkpoint was done on the target (promoted standby,

RE: ON CONFLICT DO NOTHING on pg_dump

2018-07-12 Thread Ideriha, Takeshi
>I noticed one more thing: pg_dumpall.c doesn't really need to prohibit >--on-conflict-do-nothing without --insert. Its existing validation rejects >illegal >combinations of the settings that are *not* passed on to pg_dump. It seems OK >to >just pass those on and let pg_dump complain. For

Re: Vacuum: allow usage of more than 1GB of work mem

2018-07-12 Thread Andrew Dunstan
On 07/12/2018 06:34 PM, Alvaro Herrera wrote: On 2018-Jul-12, Andrew Dunstan wrote: I fully understand. I think this needs to go back to "Waiting on Author". Why? Heikki's patch applies fine and passes the regression tests. Well, I understood Claudio was going to do some more work

Re: requested timeline ... does not contain minimum recovery point ...

2018-07-12 Thread Michael Paquier
On Thu, Jul 12, 2018 at 02:26:17PM -0700, Christophe Pettus wrote: > What surprises me about the error is that while the recovery point > seems reasonable, it shouldn't be on timeline 103, but on timeline > 105. Wild guess: you did not issue a checkpoint on the promoted standby before running

Re: [PATCH] Timestamp for a XLOG_BACKUP_END WAL-record

2018-07-12 Thread Michael Paquier
On Fri, Jul 13, 2018 at 08:13:39AM +0500, Andrey V. Lepikhov wrote: > Timestamp in a backup history file not correspond to any WAL record and > can't be bind with a time of backup exactly. > In my opinion, keeping timestamp in XLOG_BACKUP_END is more reliable, safe > and easy way for recovering a

RE: How to make partitioning scale better for larger numbers of partitions

2018-07-12 Thread Tsunakawa, Takayuki
From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] > For SELECT/UPDATE/DELETE, overhead of partitioning in the planning phase > is pretty significant and gets worse as the number of partitions grows. > I > had intended to fix that in PG 11, but we could only manage to get part > of > that

Re: [PATCH] pg_hba.conf : new auth option : clientcert=verify-full

2018-07-12 Thread Thomas Munro
On Sat, Apr 14, 2018 at 3:48 AM, Julian Markwort wrote: > [a patch] Hello Julian, Could you please post a rebased patch? I haven't reviewed or tested any code yet, but here's some proof-reading: + This behaviour is similar to the cert autentication method "behavior" (our manual is

Re: Cache invalidation after authentication (on-the-fly role creation)

2018-07-12 Thread Thomas Munro
On Fri, Jul 13, 2018 at 6:52 AM, Tom Lane wrote: > Thomas Munro writes: >> I suppose the call to AcceptInvalidationMessages() could go at the end >> of ClientAuthentication(). That'd be closer to the code that creates >> the negative entry and immediately after the code that might modify >> the

Re: Temporary tables prevent autovacuum, leading to XID wraparound

2018-07-12 Thread Michael Paquier
On Tue, Mar 13, 2018 at 08:08:48AM +, Tsunakawa, Takayuki wrote: > From: Tom Lane [mailto:t...@sss.pgh.pa.us] >> On the whole, my vote is to fix and apply step 2, and leave it at that. Yeah, I have been thinking about the idea 1 mentioned above, or in short clean up the temporary namespace at

RE: How to make partitioning scale better for larger numbers of partitions

2018-07-12 Thread Kato, Sho
>I wondered if you compared to PG10 or to inheritence-partitioning (parent with >relkind='r' and either trigger or rule or >INSERT/UPDATE directly into child) ? Thank you for your reply. I compared to PG11beta2 with non-partitioned table. Non-partitioned table has 1100 records in one table.

Re: How to make partitioning scale better for larger numbers of partitions

2018-07-12 Thread Amit Langote
Kato-san, On 2018/07/13 11:58, Kato, Sho wrote: > Hi, > > I benchmarked on a RANGE partitioned table with 1.1k leaf partitions and no > sub-partitioned tables. Thanks for sharing the results. > But, statement latencies on a partitioned table is much slower than on a > non-partitioned table.

Re: Checkpoint not retrying failed fsync?

2018-07-12 Thread Thomas Munro
On Tue, Jun 12, 2018 at 3:31 PM, Thomas Munro wrote: > I was about to mark this patch "rejected" and forget about it, since > Craig's patch makes it redundant. But then I noticed that Craig's > patch doesn't actually remove the retry behaviour completely: it > promotes only EIO and ENOSPC to

Re: Cannot dump foreign key constraints on partitioned table

2018-07-12 Thread Alvaro Herrera
On 2018-Jul-13, Michael Paquier wrote: > On Thu, Jul 12, 2018 at 02:45:37PM -0400, Alvaro Herrera wrote: > > Thanks, looks good. I propose to add following pg_dump test to ensure > > this stays fixed. > > Thanks for adding the test. I was looking at a good way to add a test > but could not

Re: Cannot dump foreign key constraints on partitioned table

2018-07-12 Thread Michael Paquier
On Thu, Jul 12, 2018 at 11:34:43PM -0400, Alvaro Herrera wrote: > I'm not sure what to *do* with the partition, though :-) I don't think > there's a nice way to verify that the FK actually exists, or that > catalog rows are set in such-and-such way, after restoring this. > The pg_restore tests are

Re: file cloning in pg_upgrade and CREATE DATABASE

2018-07-12 Thread Thomas Munro
On Wed, Feb 21, 2018 at 4:00 PM, Peter Eisentraut wrote: > - XFS has (optional) reflink support. This file system is probably more > widely used than Btrfs. > > - Linux and glibc have a proper function to do this now. > > - APFS on macOS supports file cloning. TIL that Solaris 11.4 (closed) ZFS

Re: [PG-11] Potential bug related to INCLUDE clause of CREATE INDEX

2018-07-12 Thread Yugo Nagata
On Tue, 10 Jul 2018 20:37:49 +0400 Andrey Borodin wrote: > Hi! > > > 10 июля 2018 г., в 17:54, Tom Lane написал(а): > > > > Aditya Toshniwal writes: > >> I am working on a feature to support INCLUDE clause of index in PG-11. As > >> per the documentation

Re: [HACKERS] WIP Patch: Pgbench Serialization and deadlock errors

2018-07-12 Thread Marina Polyakova
On 11-07-2018 22:34, Fabien COELHO wrote: can we try something like this? PGBENCH_ERROR_START(DEBUG_FAIL) { PGBENCH_ERROR("client %d repeats the failed transaction (try %d", Argh, no? I was thinking of something much more trivial: pgbench_error(DEBUG, "message format %d %s...",

Re: Negotiating the SCRAM channel binding type

2018-07-12 Thread Heikki Linnakangas
On 12/07/18 12:06, Michael Paquier wrote: On Thu, Jul 12, 2018 at 11:26:30AM +0300, Heikki Linnakangas wrote: It seems that all implementations can support tls-server-end-point, after all, so I'm not too worried about this anymore. The spec says that it's the default, but I don't actually see

Re: Temporary WAL segments files not cleaned up after an instance crash

2018-07-12 Thread Yugo Nagata
On Mon, 14 May 2018 14:49:55 +0900 Michael Paquier wrote: > Hi all, > > While playing with a standby as follows I noticed that xlogtemp.* > generated in pg_wal may stay around when entering crash recovery. The > test I was conducting is pretty simple: > - Use a primary and a standby. > - Run

Re: Possible bug in logical replication.

2018-07-12 Thread Michael Paquier
On Wed, Jul 04, 2018 at 10:50:28AM +0900, Michael Paquier wrote: > On Tue, Jul 03, 2018 at 01:17:48AM -0400, Alvaro Herrera wrote: > > Let me review tomorrow. > > Of course, please feel free. Alvaro, are you planning to look at that to close the loop? The latest version is here:

Re: [PATCH] Add missing type conversion functions for PL/Python

2018-07-12 Thread Haozhou Wang
Hi Heikki, Thank you very much for your review! I will prepare a new patch and make it ready soon. Regards, Haozhou On Thu, Jul 12, 2018 at 2:03 AM Heikki Linnakangas wrote: > On 26/03/18 19:07, Nikita Glukhov wrote: > > Attached fixed 3th version of the patch: > > Thanks, I'm reviewing this

Re: Problem on pg_dump RANGE partition with expressions

2018-07-12 Thread Amit Langote
Nagata-san, On 2018/07/12 16:59, Yugo Nagata wrote: > Hi, > > During looking into other thread[1], I found a problem on pg_dump of range > partition table using expressions. When we create a range partitioned table, > we cannot use a column more than once in the partition key. > > postgres=#

Re: [PG-11] Potential bug related to INCLUDE clause of CREATE INDEX

2018-07-12 Thread Yugo Nagata
On Thu, 12 Jul 2018 15:58:08 +0900 Yugo Nagata wrote: > Yes, more simplly, the following query also works; > > CREATE INDEX ON test((i)) INCLUDE (i); > > However, a problem is that when we use pg_dump for the database, this > generate the following query > > CREATE INDEX test_i_i1_idx ON

Re: In pageinspect, perform clean-up after testing gin-related functions

2018-07-12 Thread Amit Kapila
On Wed, Jul 11, 2018 at 11:04 PM, Tom Lane wrote: > Andres Freund writes: >> On 2018-07-11 12:56:49 +0530, Amit Kapila wrote: >>> Yeah, it is good practice to drop the objects at the end. It is >>> strange that original commit adfb81d9e1 has this at the end of the >>> test, but a later commit

Problem on pg_dump RANGE partition with expressions

2018-07-12 Thread Yugo Nagata
Hi, During looking into other thread[1], I found a problem on pg_dump of range partition table using expressions. When we create a range partitioned table, we cannot use a column more than once in the partition key. postgres=# create table t (i int) partition by range(i,i); ERROR: column

Re: [HACKERS] WIP Patch: Pgbench Serialization and deadlock errors

2018-07-12 Thread Marina Polyakova
On 11-07-2018 21:04, Alvaro Herrera wrote: Just a quick skim while refreshing what were those error reporting API changes about ... Thank you! On 2018-May-21, Marina Polyakova wrote: v9-0001-Pgbench-errors-use-the-RandomState-structure-for-.patch - a patch for the RandomState structure

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2018-07-12 Thread Pavel Stehule
Hi 2018-07-10 12:01 GMT+02:00 Peter Eisentraut < peter.eisentr...@2ndquadrant.com>: > On 23.01.18 17:08, Pavel Stehule wrote: > > attached updated patch > > This appears to be the patch of record in this thread. I think there is > general desire for adding a setting like this, and the

Re: no partition pruning when partitioning using array type

2018-07-12 Thread Amit Langote
On 2018/07/12 2:33, Alvaro Herrera wrote: > On 2018-Jul-11, Amit Langote wrote: > >> On 2018/07/11 13:12, Alvaro Herrera wrote: >>> On 2018-Jul-11, Amit Langote wrote: >>> What's the solution here then? Prevent domains as partition key? >>> >>> Maybe if a domain is used in a partition key

Re: [HACKERS] WIP Patch: Pgbench Serialization and deadlock errors

2018-07-12 Thread Marina Polyakova
On 11-07-2018 20:49, Alvaro Herrera wrote: On 2018-Jul-11, Marina Polyakova wrote: can we try something like this? PGBENCH_ERROR_START(DEBUG_FAIL) { PGBENCH_ERROR("client %d repeats the failed transaction (try %d", st->id, st->retries + 1); if

Re: BUG #15212: Default values in partition tables don't work as expected and allow NOT NULL violation

2018-07-12 Thread Amit Langote
Thanks Ashutosh. On 2018/07/10 22:50, Ashutosh Bapat wrote: > I didn't see any hackers thread linked to this CF entry. Hence sending this > mail through CF app. Hmm, yes. I hadn't posted the patch to -hackers. > The patch looks good to me. It applies cleanly, compiles cleanly and make >

Re: Negotiating the SCRAM channel binding type

2018-07-12 Thread Michael Paquier
On Thu, Jul 12, 2018 at 11:26:30AM +0300, Heikki Linnakangas wrote: > It seems that all implementations can support tls-server-end-point, after > all, so I'm not too worried about this anymore. The spec says that it's the > default, but I don't actually see any advantage to using it over >

Re: partition pruning doesn't work with IS NULL clause in multikey range partition case

2018-07-12 Thread Dilip Kumar
On Thu, Jul 12, 2018 at 11:10 AM, Amit Langote wrote: > On 2018/07/12 14:32, Amit Langote wrote: >> Thanks Ashutosh for reporting and Dilip for the analysis and the patch. >> >> I think your fix is correct. I slightly modified it along with updating >> nearby comments and added regression

Re: Temporary WAL segments files not cleaned up after an instance crash

2018-07-12 Thread Michael Paquier
On Thu, Jul 12, 2018 at 03:35:53PM +0900, Yugo Nagata wrote: > I think it makes sense to remove unnecessary temporary WAL files although > I'm not sure how high the risk of ENOSPC is. It depends on how close to the partition size limit max_wal_size is set, and how much a system is unstable.

Re: Negotiating the SCRAM channel binding type

2018-07-12 Thread Heikki Linnakangas
On 12/07/18 07:14, Michael Paquier wrote: On Wed, Jul 11, 2018 at 03:01:03PM +0300, Heikki Linnakangas wrote: I started digging into this more closely, and ran into a little problem. If channel binding is not used, the client sends a flag to the server to indicate if it's because the client

Re: ON CONFLICT DO NOTHING on pg_dump

2018-07-12 Thread Thomas Munro
On Wed, Jul 11, 2018 at 2:20 PM, Ideriha, Takeshi wrote: > I did regression tests (make check-world) and > checked manually pg_dump --on-conflict-do-nothing works properly. > Also it seems to me the code has no problem. > This feature has advantage to some users with small code change. > > So I

Re: make installcheck-world in a clean environment

2018-07-12 Thread Alexander Lakhin
Hello Tom, 11.07.2018 23:15, Tom Lane wrote: > >> /make clean/ >> # Also you can just install binary packages to get the same state. >> make installcheck-world >> # This check fails. > I do not think that should be expected to work. It would require that > "make installcheck" first invoke "make

Re: Temporary WAL segments files not cleaned up after an instance crash

2018-07-12 Thread Heikki Linnakangas
On 12/07/18 15:38, Michael Paquier wrote: On Thu, Jul 12, 2018 at 01:15:03PM +0300, Heikki Linnakangas wrote: On 12/07/18 10:44, Michael Paquier wrote: + snprintf(path, MAXPGPATH, XLOGDIR "/%s", xlde->d_name); + elog(DEBUG2, "removed temporary WAL file \"%s\"",

Re: partition pruning doesn't work with IS NULL clause in multikey range partition case

2018-07-12 Thread Ashutosh Bapat
On Thu, Jul 12, 2018 at 11:10 AM, Amit Langote wrote: >> >> I think your fix is correct. I slightly modified it along with updating >> nearby comments and added regression tests. > > I updated regression tests to reduce lines. There is no point in > repeating tests like v2 patch did. + * +

Re: Negotiating the SCRAM channel binding type

2018-07-12 Thread Michael Paquier
On Thu, Jul 12, 2018 at 12:34:51PM +0300, Heikki Linnakangas wrote: > Meh. We're not going implement tls-unique, anyway, in some of the upcoming > non-OpenSSL TLS implementations that don't support it. True enough. Only GnuTLS supports it:

Re: Negotiating the SCRAM channel binding type

2018-07-12 Thread Michael Paquier
On Wed, Jul 11, 2018 at 04:00:47PM +0300, Heikki Linnakangas wrote: > Looking at the GnuTLS docs, I believe it has everything we need. > gnutls_certificate_get_peers() and gnutls_certificate_get_ours() can be used > to get the certificate, and gnutls_x509_crt_get_signature_algorithm() gets > the

Re: Temporary WAL segments files not cleaned up after an instance crash

2018-07-12 Thread Michael Paquier
On Thu, Jul 12, 2018 at 01:15:03PM +0300, Heikki Linnakangas wrote: > On 12/07/18 10:44, Michael Paquier wrote: > > + snprintf(path, MAXPGPATH, XLOGDIR "/%s", xlde->d_name); > > + elog(DEBUG2, "removed temporary WAL file \"%s\"", path); > > + unlink(path); > > The

Re: [HACKERS] WAL logging problem in 9.4.3?

2018-07-12 Thread Andrew Dunstan
On 07/10/2018 11:32 PM, Michael Paquier wrote: On Tue, Jul 10, 2018 at 05:35:58PM +0300, Heikki Linnakangas wrote: Thanks for picking this up! (I hope this gets through the email filters this time, sending a shell script seems to be difficult. I also trimmed the CC list, if that helps.) On

Re: Copy function for logical replication slots

2018-07-12 Thread Masahiko Sawada
On Mon, Jul 9, 2018 at 10:34 AM, Michael Paquier wrote: > On Mon, Jul 09, 2018 at 10:06:00AM +0900, Masahiko Sawada wrote: >> I think that this patch might be splitted but I will be able to send >> an updated patch in the next week. As you suggestion this patch needs >> more careful thoughts.

Re: Binary difference in pg_internal.init after running pg_initdb multiple times

2018-07-12 Thread Tomas Vondra
On 07/12/2018 10:08 AM, samuel.coulee wrote: Hi, In the PG source code function "write_relcache_init_file()", I found that the whole 'Relation' structs were directly written into the file 'pg_internal.init'. This brings some binary differences of that file, if we run pg_initdb multiple times,

Re: partition pruning doesn't work with IS NULL clause in multikey range partition case

2018-07-12 Thread Ashutosh Bapat
I think we should add this to open items list so that it gets tracked. On Thu, Jul 12, 2018 at 6:31 PM, Ashutosh Bapat wrote: > On Thu, Jul 12, 2018 at 11:10 AM, Amit Langote > wrote: >>> >>> I think your fix is correct. I slightly modified it along with updating >>> nearby comments and added

Re: pg_create_logical_replication_slot returns text instead of name

2018-07-12 Thread Michael Paquier
On Thu, Jul 12, 2018 at 07:00:48PM +0900, Masahiko Sawada wrote: > The documentation[1] says that both pg_create_logical_replication_slot > and pg_create_physical_replication_slot returns slot_name as a name > type. But only pg_create_logical_replication_slot returns it as text > type. I think

Re: Binary difference in pg_internal.init after running pg_initdb multiple times

2018-07-12 Thread Tom Lane
"samuel.coulee" <313914...@qq.com> writes: > In the PG source code function "write_relcache_init_file()", I found that > the whole 'Relation' structs were directly written into the file > 'pg_internal.init'. This brings some binary differences of that file, if we > run pg_initdb multiple times,

Re: buildfarm vs code

2018-07-12 Thread Andrew Dunstan
On 07/12/2018 05:54 AM, Heikki Linnakangas wrote: On 02/07/18 10:57, Peter Eisentraut wrote: On 05.06.18 18:09, Andrew Dunstan wrote: The first should be simple and non-controversial. It allows src/tools/msvc/build.pl to be called in such a way that it only creates the project files and

Re: Vacuum: allow usage of more than 1GB of work mem

2018-07-12 Thread Andrew Dunstan
On 04/06/2018 08:00 PM, Claudio Freire wrote: On Fri, Apr 6, 2018 at 5:25 PM, Claudio Freire wrote: On Fri, Apr 6, 2018 at 10:39 AM, Heikki Linnakangas wrote: On 06/04/18 01:59, Claudio Freire wrote: The iteration interface, however, seems quite specific for the use case of vacuumlazy,

Re: [HACKERS] WAL logging problem in 9.4.3?

2018-07-12 Thread Heikki Linnakangas
On 12/07/18 16:51, Andrew Dunstan wrote: On 07/10/2018 11:32 PM, Michael Paquier wrote: On Tue, Jul 10, 2018 at 05:35:58PM +0300, Heikki Linnakangas wrote: Thanks for picking this up! (I hope this gets through the email filters this time, sending a shell script seems to be difficult. I also

Re: _isnan() on Windows

2018-07-12 Thread Tom Lane
Michael Paquier writes: > On Wed, Jul 11, 2018 at 09:13:40AM -0400, Alvaro Herrera wrote: >> I just pushed it before seeing your message. > Fine as well, thanks for picking this up. The buildfarm shows no > failures about this patch. I scraped all the compiler warnings from the buildfarm this

Re: buildfarm vs code

2018-07-12 Thread Heikki Linnakangas
On 02/07/18 10:57, Peter Eisentraut wrote: On 05.06.18 18:09, Andrew Dunstan wrote: The first should be simple and non-controversial. It allows src/tools/msvc/build.pl to be called in such a way that it only creates the project files and then stops. This is a one line addition to the script and

Binary difference in pg_internal.init after running pg_initdb multiple times

2018-07-12 Thread samuel.coulee
Hi, In the PG source code function "write_relcache_init_file()", I found that the whole 'Relation' structs were directly written into the file 'pg_internal.init'. This brings some binary differences of that file, if we run pg_initdb multiple times, because the struct 'Relation' contains some

Re: patch to allow disable of WAL recycling

2018-07-12 Thread Tomas Vondra
On 07/12/2018 02:25 AM, David Pacheco wrote: On Tue, Jul 10, 2018 at 1:34 PM, Alvaro Herrera mailto:alvhe...@2ndquadrant.com>> wrote: On 2018-Jul-10, Jerry Jelinek wrote: > 2) Disabling WAL recycling reduces reliability, even on COW filesystems. I think the problem here is

Re: Preferring index-only-scan when the cost is equal

2018-07-12 Thread Tomas Vondra
On 07/12/2018 03:44 AM, Yugo Nagata wrote: On Wed, 11 Jul 2018 14:37:46 +0200 Tomas Vondra wrote: On 07/11/2018 01:28 PM, Ashutosh Bapat wrote: I don't think we should change add_path() for this. We will unnecessarily check that condition even for the cases where we do not create index

Re: missing toast table for pg_policy

2018-07-12 Thread Peter Eisentraut
On 10.07.18 03:29, Michael Paquier wrote: > On Mon, Jul 09, 2018 at 09:19:35PM -0400, Joe Conway wrote: >> If you can wait for the next commitfest (the original one I put the >> patch into, i.e. September) I am committed to making it happen. But if >> you are anxious to getting this into the

pg_create_logical_replication_slot returns text instead of name

2018-07-12 Thread Masahiko Sawada
Hi, The documentation[1] says that both pg_create_logical_replication_slot and pg_create_physical_replication_slot returns slot_name as a name type. But only pg_create_logical_replication_slot returns it as text type. I think these should be united as the name type. Attached small patch fixes it.

Re: Temporary WAL segments files not cleaned up after an instance crash

2018-07-12 Thread Heikki Linnakangas
On 12/07/18 10:44, Michael Paquier wrote: On Thu, Jul 12, 2018 at 03:35:53PM +0900, Yugo Nagata wrote: One little thing I noticed is the function name "RemoveXLogTempFiles". Other similar functions are named as RemoveOldXlogFiles or RemoveXlogFile (using Xlog not XLog), so it seem to me more

Re: [HACKERS] plpgsql - additional extra checks

2018-07-12 Thread Pavel Stehule
Hi 2018-07-09 21:44 GMT+02:00 Alvaro Herrera : > > + ereport(errlevel, > > (errcode(ERRCODE_TOO_MANY_ > ROWS), > >errmsg("query returned > more than one row"), > > -

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

2018-07-12 Thread Heikki Linnakangas
On 18/01/18 12:26, Fabien COELHO wrote: Hm. Could we get somewhere by making the test look for that, and adjusting the loop logic inside pgbench so that (maybe only with the tested switch values) it's guaranteed to print at least one progress output regardless of timing, because it won't check

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2018-07-12 Thread Tatsuro Yamada
On 2018/07/12 18:12, Pavel Stehule wrote: Hi 2018-07-10 12:01 GMT+02:00 Peter Eisentraut mailto:peter.eisentr...@2ndquadrant.com>>: On 23.01.18 17:08, Pavel Stehule wrote: > attached updated patch This appears to be the patch of record in this thread.  I think there is

Re: Expression errors with "FOR UPDATE" and postgres_fdw with partition wise join enabled.

2018-07-12 Thread Etsuro Fujita
(2018/07/12 13:38), Ashutosh Bapat wrote: On Thu, Jul 12, 2018 at 9:02 AM, Etsuro Fujita wrote: (2018/07/11 20:02), Ashutosh Bapat wrote: On Wed, Jul 11, 2018 at 1:23 PM, Etsuro Fujita wrote: Actually, even if we could create such an index on the child table and the targetlist had the

Re: cost_sort() improvements

2018-07-12 Thread Teodor Sigaev
One more thought about estimating the worst case - I wonder if simply multiplying the per-tuple cost by 1.5 is the right approach. It does not seem particularly principled, and it's trivial simple to construct counter-examples defeating it (imagine columns with 99% of the rows having the same

Re: Failure assertion in GROUPS mode of window function in current HEAD

2018-07-12 Thread Tom Lane
Masahiko Sawada writes: > I think we also can update the doc about that GROUPS offset mode > requires ORDER BY clause. Thoughts? Attached patch updates it. Ooops, I forgot to check the docs. This isn't quite the right fix though --- the problem is that there's a sentence at the end of that para

Re: _isnan() on Windows

2018-07-12 Thread Andrew Dunstan
On 07/12/2018 10:38 AM, Tom Lane wrote: Andrew Dunstan writes: On 07/12/2018 10:20 AM, Tom Lane wrote: bowerbird and hamerkop have some gripes like this: bowerbird | c:\perl64\lib\core\win32.h(218): warning C4005: 'isnan' : macro redefinition (src/pl/plperl/SPI.c)

Re: Failure assertion in GROUPS mode of window function in current HEAD

2018-07-12 Thread Masahiko Sawada
On Fri, Jul 13, 2018 at 12:17 AM, Tom Lane wrote: > Masahiko Sawada writes: >> I think we also can update the doc about that GROUPS offset mode >> requires ORDER BY clause. Thoughts? Attached patch updates it. > > Ooops, I forgot to check the docs. This isn't quite the right fix > though ---

Re: _isnan() on Windows

2018-07-12 Thread Andrew Dunstan
On 07/12/2018 10:20 AM, Tom Lane wrote: Michael Paquier writes: On Wed, Jul 11, 2018 at 09:13:40AM -0400, Alvaro Herrera wrote: I just pushed it before seeing your message. Fine as well, thanks for picking this up. The buildfarm shows no failures about this patch. I scraped all the

Re: cost_sort() improvements

2018-07-12 Thread Teodor Sigaev
V8 contains fixes of Tomas Vondra complaints: - correct usage of comparison_cost - remove uneeded check of sortop -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ diff --git

Re: Postgres 11 release notes

2018-07-12 Thread Tom Lane
"Jonathan S. Katz" writes: > On Jun 30, 2018, at 5:52 PM, Tom Lane wrote: >> Mmm, yeah, I suppose it should say "all framing options" rather than >> implying that we've implemented every other window-related frammish >> there is in the spec. > +1. Attached patch that does exactly that. Pushed,

Re: cost_sort() improvements

2018-07-12 Thread Teodor Sigaev
At least [1] and [2] hit into to that issues and have an objections/questions about correctness of cost sort estimation. Suggested patch tries to improve current estimation and solve that issues. Sorry for long delay but issue was even more complicated than I thought. When I tried to add

Re: _isnan() on Windows

2018-07-12 Thread Tom Lane
Andrew Dunstan writes: > On 07/12/2018 10:20 AM, Tom Lane wrote: >> bowerbird and hamerkop have some gripes like this: >> >> bowerbird | c:\perl64\lib\core\win32.h(218): warning C4005: 'isnan' : >> macro redefinition (src/pl/plperl/SPI.c) >>

  1   2   >