Re: file cloning in pg_upgrade and CREATE DATABASE

2018-07-16 Thread Michael Paquier
On Wed, Jun 06, 2018 at 11:58:14AM -0400, Peter Eisentraut wrote: > > The setting always requires the use of relinks. If > they are not supported, the pg_upgrade run > will abort. Use this in production to limit the upgrade run time. > The setting auto uses reflinks when available, > otherw

Re: missing toast table for pg_policy

2018-07-16 Thread Michael Paquier
On Sat, Jul 14, 2018 at 03:47:38PM +0700, John Naylor wrote: > I hope you don't mind, but since it might be tedious to piece together > the addenda I left behind in this thread, I thought it might be useful > to update Joe's patch. The attached was rebased over the new > regression test, passes the

Re: Add SKIP LOCKED to VACUUM and ANALYZE

2018-07-16 Thread Michael Paquier
On Thu, Jul 12, 2018 at 02:37:28PM +0900, Michael Paquier wrote: > On Wed, Jun 13, 2018 at 08:29:12PM +, Bossart, Nathan wrote: >> Previous thread: >> https://postgr.es/m/4BC0F3CD-F4B5-4F23-AADB-80607F9E4B4E%40amazon.com >> >> This is a new thread for tracking the work to add SKIP LOCKED to >

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

2018-07-16 Thread Kato, Sho
On 2018/07/16 13:16, Tsunakawa-san wrote: >Thanks. And what does the comparison look like between the unpartitioned case >and various partition counts? What's the performance characteristics in terms >of the latency and partition count? I thought that's what you tried to >reveal first? In u

untrusted PLs should be GRANTable

2018-07-16 Thread Craig Ringer
Hi all A user has raised the point that our refusal to GRANT rights to untrusted PLs is counterproductive and inconsistent with how we behave elsewhere. Yes, untrusted PLs can be escaped to gain superuser rights, often trivially. But we allow this: CREATE ROLE superme SUPERUSER NOINHERIT; GRANT

Re: pgsql: Allow UNIQUE indexes on partitioned tables

2018-07-16 Thread Amit Langote
On 2018/07/17 13:57, Alvaro Herrera wrote: > On 2018-Feb-19, David G. Johnston wrote: > >> As an aside, adding a link to "Data Definiton/Table Partitioning" from at >> least CREATE TABLE ... PARTITION BY; and swapping "PARTITION BY" and >> "PARTITION OF" in the Parameters section of that page - on

Re: pgsql: Allow UNIQUE indexes on partitioned tables

2018-07-16 Thread Alvaro Herrera
On 2018-Feb-19, David G. Johnston wrote: > As an aside, adding a link to "Data Definiton/Table Partitioning" from at > least CREATE TABLE ... PARTITION BY; and swapping "PARTITION BY" and > "PARTITION OF" in the Parameters section of that page - one must partition > by a table before one can parti

Re: [HACKERS] Restricting maximum keep segments by repslots

2018-07-16 Thread Masahiko Sawada
On Fri, Jul 13, 2018 at 5:40 PM, Kyotaro HORIGUCHI wrote: > Hello. > > At Wed, 11 Jul 2018 15:09:23 +0900, Masahiko Sawada > wrote in >> On Mon, Jul 9, 2018 at 2:47 PM, Kyotaro HORIGUCHI >> wrote: > .. >> Here is review comments of v4 patches. >> >> + if (minKeepLSN) >> + { >> +

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

2018-07-16 Thread Amit Langote
On 2018/07/17 12:14, Ashutosh Bapat wrote: > On Tue, Jul 17, 2018 at 8:31 AM, Kato, Sho wrote: >> On 2018/07/17 10:49, Amit Langote wrote: >>> Perhaps, Kato-san only intended to report that the time that planner spends >>> for a partitioned table with 1100 partitions is just too high compared to

Re: Refactor documentation for wait events (Was: pgsql: Add wait event for fsync of WAL segments)

2018-07-16 Thread Michael Paquier
On Mon, Jul 16, 2018 at 11:22:07AM -0400, Robert Haas wrote: > This doesn't seem to get rid of the morerows stuff. The troubling ones are in monitoring.sgml: LWLock Lock Activity Client IPC Timeout IO And the patch previously sent removes them, but perhaps I am missing your point? -- Michael si

Re: Let's remove DSM_IMPL_NONE.

2018-07-16 Thread Kyotaro HORIGUCHI
Hello. At Tue, 10 Jul 2018 18:52:54 +0200, Peter Eisentraut wrote in > committed Thank you for committing this! regards. -- Kyotaro Horiguchi NTT Open Source Software Center

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

2018-07-16 Thread Ashutosh Bapat
On Tue, Jul 17, 2018 at 8:31 AM, Kato, Sho wrote: > On 2018/07/17 10:49, Amit Langote wrote: >>Perhaps, Kato-san only intended to report that the time that planner spends >>for a partitioned table with 1100 partitions is just too high compared to the >>time it spends on a non-partitioned table.

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

2018-07-16 Thread Kato, Sho
On 2018/07/17 10:49, Amit Langote wrote: >Perhaps, Kato-san only intended to report that the time that planner spends >for a partitioned table with 1100 partitions is just too high compared to the >time it spends on a non-partitioned table. yes, It is included for the purposes of this comparison

Re: Make foo=null a warning by default.

2018-07-16 Thread David Fetter
On Mon, Jul 16, 2018 at 11:37:28AM -0400, Tom Lane wrote: > Heikki Linnakangas writes: > > On 16/07/18 18:10, Tom Lane wrote: > >> TBH I'm not really excited about investing any work in this area at all. > >> Considering how seldom we hear any questions about transform_null_equals > >> anymore[1],

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

2018-07-16 Thread Amit Langote
On 2018/07/13 22:10, Ashutosh Bapat wrote: > On Fri, Jul 13, 2018 at 9:23 AM, Kato, Sho wrote: >>> 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

Re: PATCH: psql tab completion for SELECT

2018-07-16 Thread Edmund Horner
On 17 July 2018 at 03:27, Joao De Almeida Pereira wrote: > After playing alittle bit around with the patch I noticed that a comma was > missing in line 1214 > + 1202 /* min_server_version */ > + 1203 9, > + 1204 /* catname */ > + 1205

Re: PATCH: psql tab completion for SELECT

2018-07-16 Thread Edmund Horner
On 17 July 2018 at 00:00, Heikki Linnakangas wrote: > Playing around with this a little bit, I'm not very satisfied with the > completions. Sometimes this completes too much, and sometimes too little. > All of this has been mentioned in this and the other thread [1] already, > this just my opinion

Re: patch to allow disable of WAL recycling

2018-07-16 Thread Michael Paquier
On Mon, Jul 16, 2018 at 10:38:14AM -0400, Robert Haas wrote: > It's been a few years since I tested this, but my recollection is that > if you fill up pg_xlog, the system will PANIC and die on a vanilla > Linux install. Sure, you can set max_wal_size, but that's a soft > limit, not a hard limit, a

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

2018-07-16 Thread Amit Langote
On 2018/07/17 8:17, Alvaro Herrera wrote: > On 2018-Jul-16, Ashutosh Bapat wrote: > >>> Hmm, let me reword this comment completely. How about the attached? > >> That looks much better. However it took me a small while to understand >> that (1), (2) and (3) correspond to strategies. > > You're r

Re: Internal error XX000 with enable_partition_pruning=on, pg 11 beta1 on Debian

2018-07-16 Thread David Rowley
On 16 July 2018 at 06:55, Tom Lane wrote: > I started to look at this patch. I think this is basically the right > direction to go in, but I'm not terribly happy with the details of the > data structure design. I've made an attempt at addressing the issues that I understood. I've not done anyth

Re: Fix error message when trying to alter statistics on included column

2018-07-16 Thread Alvaro Herrera
On 2018-Jun-28, Yugo Nagata wrote: > According to the error message, it is not allowed to alter statistics on > included column because this is "non-expression column". > > postgres=# create table test (i int, d int); > CREATE TABLE > postgres=# create index idx on test(i) include (d); > CREA

Re: [HACKERS] WAL logging problem in 9.4.3?

2018-07-16 Thread Michael Paquier
On Mon, Jul 16, 2018 at 09:41:51PM +0300, Heikki Linnakangas wrote: > On 16 July 2018 21:38:39 EEST, Robert Haas wrote: >>On Thu, Jul 12, 2018 at 10:12 AM, Heikki Linnakangas >>wrote: >>> Doesn't have to be a trigger, could be a CHECK constraint, datatype >>input >>> function, etc. Admittedly, ha

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

2018-07-16 Thread Alvaro Herrera
On 2018-Jul-16, Ashutosh Bapat wrote: > > Hmm, let me reword this comment completely. How about the attached? > That looks much better. However it took me a small while to understand > that (1), (2) and (3) correspond to strategies. You're right. Amended again and pushed. I also marked the op

Re: patch to allow disable of WAL recycling

2018-07-16 Thread Jerry Jelinek
There have been quite a few comments since last week, so at this point I am uncertain how to proceed with this change. I don't think I saw anything concrete in the recent emails that I can act upon. I would like to respond to the comment about trying to "self-tune" the behavior based on inferences

Re: AtEOXact_ApplyLauncher() and subtransactions

2018-07-16 Thread Robert Haas
On Mon, Jul 16, 2018 at 2:36 AM, Amit Khandekar wrote: > 0001 patch contains the main fix. In this patch I have used some > naming conventions and some comments that you used in your patch, > plus, I used your method of lazily allocating new stack level. The > stack is initially Null. Committed a

Re: New GUC to sample log queries

2018-07-16 Thread Tomas Vondra
On 07/16/2018 05:24 PM, Robert Haas wrote: > On Sun, Jul 15, 2018 at 6:53 AM, Vik Fearing > wrote: >> Hmm. Not sure if that last word should be _sample, _sampling, _rate, or >> a combination of those. > > +1 for rate or sample_rate. I think "sample" or "sampling" without > "rate" will not be ve

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

2018-07-16 Thread Tomas Vondra
On 07/16/2018 02:54 PM, Dean Rasheed wrote: > On 16 July 2018 at 13:23, Tomas Vondra wrote: The top-level clauses allow us to make such deductions, with deeper clauses it's much more difficult (perhaps impossible). Because for example with (a=1 AND b=1) there can be just a single

Re: ENOSPC FailedAssertion("!(RefCountErrors == 0)"

2018-07-16 Thread Tom Lane
I wrote: > So I said I didn't want to do extra work on this, but I am looking into > fixing it by having these aux process types run a ResourceOwner that can > be told to clean up any open buffer pins at exit. We could be sure the > coverage is complete by dint of removing the special-case code in

Re: New GUC to sample log queries

2018-07-16 Thread Adrien Nayrat
On 07/16/2018 05:24 PM, Robert Haas wrote: > On Sun, Jul 15, 2018 at 6:53 AM, Vik Fearing > wrote: >> Hmm. Not sure if that last word should be _sample, _sampling, _rate, or >> a combination of those. > > +1 for rate or sample_rate. I think "sample" or "sampling" without > "rate" will not be ve

Re: [HACKERS] WAL logging problem in 9.4.3?

2018-07-16 Thread Alvaro Herrera
On 2018-Jul-12, Heikki Linnakangas wrote: > > > Thanks for the pointer. My tap test has been covering two out of > > > the three scenarios you have in your script. I have been able to > > > convert the extra as the attached, and I have added as well an > > > extra test with TRUNCATE triggers. S

Re: Fix some error handling for read() and errno

2018-07-16 Thread Alvaro Herrera
On 2018-Jul-16, Michael Paquier wrote: > On Sat, Jul 14, 2018 at 03:37:56PM +0900, Michael Paquier wrote: > > For now, I think that just moving forward with 0001, and then revisit > > 0002 once the other 2PC patch is settled makes the most sense. On the > > other thread, the current 2PC behavior

Re: Alter index rename concurrently to

2018-07-16 Thread Victor Yegorov
пн, 16 июл. 2018 г. в 21:58, Andrey Klychkov : > I made a patch to solve this issue (see the attachment). > It allows to avoid locks by a query like this: > “alter index rename CONCURRENTLY to ”. > Please, have a look at previous discussions on the subject: - 2012 https://www.postgresql.org/mess

Re: [HACKERS] logical decoding of two-phase transactions

2018-07-16 Thread Tomas Vondra
On 07/16/2018 08:09 PM, Robert Haas wrote: > On Mon, Jul 16, 2018 at 1:28 PM, Tomas Vondra > wrote: >> I'm not sure I understand. Are you suggesting the process might get killed >> or something, thanks to the CHECK_FOR_INTERRUPTS() call? > > Yes. CHECK_FOR_INTERRUPTS() can certainly lead to a

Re: Alter index rename concurrently to

2018-07-16 Thread Andrey Borodin
Hi! > 16 июля 2018 г., в 22:58, Andrey Klychkov написал(а): > Dear hackers! > > I have an idea to facilitate work with index rebuilding. > > "ALTER INDEX ... RENAME CONCURRENTLY TO ..." The idea seems useful. I'm not an expert in CIC, but your post do not cover one very important topic. W

Alter index rename concurrently to

2018-07-16 Thread Andrey Klychkov
Dear hackers! I have an idea to facilitate work with index rebuilding. Usually if we want to rebuild an index without table locks we should do the queries below: 1. create index concurrently... (with different name on the same columns) 2. drop index concurrently 3. alter index rename to As you

Re: [HACKERS] WAL logging problem in 9.4.3?

2018-07-16 Thread Heikki Linnakangas
On 16 July 2018 21:38:39 EEST, Robert Haas wrote: >On Thu, Jul 12, 2018 at 10:12 AM, 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 "hu

Re: [HACKERS] WAL logging problem in 9.4.3?

2018-07-16 Thread Robert Haas
On Thu, Jul 12, 2018 at 10:12 AM, 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 > catch al

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

2018-07-16 Thread Andrew Dunstan
On 07/16/2018 11:35 AM, Claudio Freire wrote: On Mon, Jul 16, 2018 at 11:34 AM Claudio Freire wrote: On Fri, Jul 13, 2018 at 5:43 PM Andrew Dunstan wrote: On 07/13/2018 09:44 AM, Heikki Linnakangas wrote: On 13/07/18 01:39, Andrew Dunstan wrote: On 07/12/2018 06:34 PM, Alvaro Herrera w

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

2018-07-16 Thread Claudio Freire
On Mon, Jul 16, 2018 at 3:30 PM Andrew Dunstan wrote: > > > > On 07/16/2018 10:34 AM, Claudio Freire wrote: > > On Fri, Jul 13, 2018 at 5:43 PM Andrew Dunstan > > wrote: > >> > >> > >> On 07/13/2018 09:44 AM, Heikki Linnakangas wrote: > >>> On 13/07/18 01:39, Andrew Dunstan wrote: > On 07/12

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

2018-07-16 Thread Andrew Dunstan
On 07/16/2018 10:34 AM, Claudio Freire wrote: On Fri, Jul 13, 2018 at 5:43 PM Andrew Dunstan wrote: On 07/13/2018 09:44 AM, Heikki Linnakangas wrote: On 13/07/18 01:39, Andrew Dunstan wrote: On 07/12/2018 06:34 PM, Alvaro Herrera wrote: On 2018-Jul-12, Andrew Dunstan wrote: I fully un

Re: [HACKERS] logical decoding of two-phase transactions

2018-07-16 Thread Robert Haas
On Mon, Jul 16, 2018 at 1:28 PM, Tomas Vondra wrote: > I'm not sure I understand. Are you suggesting the process might get killed > or something, thanks to the CHECK_FOR_INTERRUPTS() call? Yes. CHECK_FOR_INTERRUPTS() can certainly lead to a non-local transfer of control. > But BackendXidGetProc

Re: [HACKERS] logical decoding of two-phase transactions

2018-07-16 Thread Tomas Vondra
On 07/16/2018 07:21 PM, Tom Lane wrote: Robert Haas writes: I agree. As a general statement, I think the idea of trying to prevent transactions from aborting is really scary. It's almost an axiom of the system that we're always allowed to abort, and I think there could be a lot of unintended

Re: [HACKERS] logical decoding of two-phase transactions

2018-07-16 Thread Tomas Vondra
On 07/16/2018 06:15 PM, Robert Haas wrote: On Mon, Jul 16, 2018 at 11:21 AM, Tomas Vondra wrote: Overall, I think it's clear the main risk associated with this patch is the decode group code - it touches PROC entries, so a bug may cause trouble pretty easily. So I've focused on this part, fo

Re: GiST VACUUM

2018-07-16 Thread Andrey Borodin
> 16 июля 2018 г., в 18:58, Robert Haas написал(а): > > On Fri, Jul 13, 2018 at 10:10 AM, Heikki Linnakangas wrote: >> I'm still a bit scared about using pd_prune_xid to store the XID that >> prevents recycling the page too early. Can we use some field in >> GISTPageOpaqueData for that, simila

Re: [HACKERS] logical decoding of two-phase transactions

2018-07-16 Thread Tom Lane
Robert Haas writes: > I agree. As a general statement, I think the idea of trying to > prevent transactions from aborting is really scary. It's almost an > axiom of the system that we're always allowed to abort, and I think > there could be a lot of unintended and difficult-to-fix consequences >

Another usability issue with our TAP tests

2018-07-16 Thread Tom Lane
Since "make check-world" is rather chatty, if you get a failure while running it under high parallelism, the location of the failure has often scrolled off the terminal window by the time all the other subjobs exit. This is not a huge problem for tests using our traditional infrastructure, because

Re: [HACKERS] logical decoding of two-phase transactions

2018-07-16 Thread Robert Haas
On Mon, Jul 16, 2018 at 11:21 AM, Tomas Vondra wrote: > Overall, I think it's clear the main risk associated with this patch is the > decode group code - it touches PROC entries, so a bug may cause trouble > pretty easily. So I've focused on this part, for now. I agree. As a general statement, I

Re: Make foo=null a warning by default.

2018-07-16 Thread David Fetter
On Mon, Jul 16, 2018 at 09:49:14AM +0200, Fabien COELHO wrote: > Hello David, > > >Per a discussion with Andrew Gierth and Vik Fearing, both of whom > >helped make this happen, please find attached a patch which makes it > >possible to get SQL standard behavior for "= NULL", which is an error. > >

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

2018-07-16 Thread Heikki Linnakangas
On 16/07/18 18:35, Claudio Freire wrote: On Mon, Jul 16, 2018 at 11:34 AM Claudio Freire wrote: On Fri, Jul 13, 2018 at 5:43 PM Andrew Dunstan wrote: On 07/13/2018 09:44 AM, Heikki Linnakangas wrote: Claudio raised a good point, that doing small pallocs leads to fragmentation, and in particu

Re: Make foo=null a warning by default.

2018-07-16 Thread Tom Lane
Heikki Linnakangas writes: > On 16/07/18 18:10, Tom Lane wrote: >> TBH I'm not really excited about investing any work in this area at all. >> Considering how seldom we hear any questions about transform_null_equals >> anymore[1], I'm wondering if we couldn't just rip the "feature" out >> entirely

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

2018-07-16 Thread Claudio Freire
On Mon, Jul 16, 2018 at 11:34 AM Claudio Freire wrote: > > On Fri, Jul 13, 2018 at 5:43 PM Andrew Dunstan > wrote: > > > > > > > > On 07/13/2018 09:44 AM, Heikki Linnakangas wrote: > > > On 13/07/18 01:39, Andrew Dunstan wrote: > > >> On 07/12/2018 06:34 PM, Alvaro Herrera wrote: > > >>> On 2018-

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

2018-07-16 Thread Heikki Linnakangas
On 12/07/18 21:27, Fabien COELHO wrote: For the testing, we just need to make sure that at least one progress report is always printed, if -P is used. Right? Yep. That is the first condition above the last_report is set to thread_start meaning that there has been no report. So where does the

Re: PATCH: psql tab completion for SELECT

2018-07-16 Thread Joao De Almeida Pereira
Hello, postgres=# select partkey, partc[TAB] > [no completions] > >From the thread, I believe that this feature will be implemented in a after patch. > > And I'd love this case, where go back to edit the SELECT list, after > already typing the FROM part, to be smarter: > > postgres=# select p[TA

Re: New GUC to sample log queries

2018-07-16 Thread Robert Haas
On Sun, Jul 15, 2018 at 6:53 AM, Vik Fearing wrote: > Hmm. Not sure if that last word should be _sample, _sampling, _rate, or > a combination of those. +1 for rate or sample_rate. I think "sample" or "sampling" without "rate" will not be very clear. -- Robert Haas EnterpriseDB: http://www.ent

Re: Logical decoding from promoted standby with same replication slot

2018-07-16 Thread Jeremy Finzel
On Fri, Jul 13, 2018 at 2:30 PM, Jeremy Finzel wrote: > Hello - > > We are working on several DR scenarios with logical decoding. Although we > are using pglogical the question we have I think is generally applicable to > logical replication. > > Say we have need to drop a logical replication sl

Re: Refactor documentation for wait events (Was: pgsql: Add wait event for fsync of WAL segments)

2018-07-16 Thread Robert Haas
On Sun, Jul 15, 2018 at 10:10 PM, Michael Paquier wrote: > On Fri, Jul 13, 2018 at 04:57:59PM -0500, Robert Haas wrote: >> On Mon, Jul 9, 2018 at 4:41 PM, Michael Paquier wrote: >>> Another idea that I have here, is to rework the page for monitoring >>> stats so as we create one sub-section for e

Re: [HACKERS] logical decoding of two-phase transactions

2018-07-16 Thread Tomas Vondra
Hi Nikhil, I've been looking at this patch series, and I do have a bunch of comments and questions, as usual ;-) Overall, I think it's clear the main risk associated with this patch is the decode group code - it touches PROC entries, so a bug may cause trouble pretty easily. So I've focused

Re: Make foo=null a warning by default.

2018-07-16 Thread Heikki Linnakangas
On 16/07/18 18:10, Tom Lane wrote: Heikki Linnakangas writes: On 16/07/18 04:40, David Fetter wrote: Per a discussion with Andrew Gierth and Vik Fearing, both of whom helped make this happen, please find attached a patch which makes it possible to get SQL standard behavior for "= NULL", which

Re: Make foo=null a warning by default.

2018-07-16 Thread Tom Lane
Heikki Linnakangas writes: > On 16/07/18 04:40, David Fetter wrote: >> Per a discussion with Andrew Gierth and Vik Fearing, both of whom >> helped make this happen, please find attached a patch which makes it >> possible to get SQL standard behavior for "= NULL", which is an error. >> It's been up

Re: Make foo=null a warning by default.

2018-07-16 Thread Robert Haas
On Mon, Jul 16, 2018 at 3:49 AM, Heikki Linnakangas wrote: > I don't agree with changing the default to 'warn'. "foo = NULL" is perfectly > legal SQL, even if it's not very useful in practice. +1. I think that will probably generate lots of annoying warnings in server logs compared to the value

Re: [HACKERS] advanced partition matching algorithm for partition-wise join

2018-07-16 Thread Robert Haas
On Sun, Jul 15, 2018 at 1:43 PM, Dmitry Dolgov <9erthali...@gmail.com> wrote: > Partitions: test11 FOR VALUES FROM (0) TO (100), > test12 FOR VALUES FROM (100) TO (200), > test13 FOR VALUES FROM (200) TO (300) > > Partitions: test21 FOR VALUES FROM (10) TO (110), >

Re: GiST VACUUM

2018-07-16 Thread Robert Haas
On Fri, Jul 13, 2018 at 10:10 AM, Heikki Linnakangas wrote: > I'm still a bit scared about using pd_prune_xid to store the XID that > prevents recycling the page too early. Can we use some field in > GISTPageOpaqueData for that, similar to how the B-tree stores it in > BTPageOpaqueData? What's yo

Re: Libpq support to connect to standby server as priority

2018-07-16 Thread Laurenz Albe
Haribabu Kommi wrote: > > On Wed, Jul 4, 2018 at 11:14 PM Laurenz Albe > > wrote: > > > Haribabu Kommi wrote: > > > > > > - I think the construction with "read_write_host_index" makes the code > > > even more > > > complicated than it already is. > > > > > > What about keeping the first s

Re: ENOSPC FailedAssertion("!(RefCountErrors == 0)"

2018-07-16 Thread Tom Lane
Andres Freund writes: > On 2018-07-15 18:48:43 -0400, Tom Lane wrote: >> So basically, WAL replay hits an error while holding a buffer pin, and >> nothing is done to release the buffer pin, but AtProcExit_Buffers thinks >> something should have been done. > I think there's a few other cases where

Re: patch to allow disable of WAL recycling

2018-07-16 Thread Robert Haas
On Mon, Jul 16, 2018 at 10:12 AM, Tom Lane wrote: > But anyway, this means we have two nearly independent issues to > investigate: whether recycling/renaming old files is cheaper than > constantly creating and deleting them, and whether to use physical > file zeroing versus some "just set the EOF

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

2018-07-16 Thread Claudio Freire
On Fri, Jul 13, 2018 at 5:43 PM Andrew Dunstan wrote: > > > > On 07/13/2018 09:44 AM, Heikki Linnakangas wrote: > > On 13/07/18 01:39, Andrew Dunstan wrote: > >> On 07/12/2018 06:34 PM, Alvaro Herrera wrote: > >>> On 2018-Jul-12, Andrew Dunstan wrote: > >>> > I fully understand. I think this

Re: cursors with prepared statements

2018-07-16 Thread Robert Haas
On Mon, Jul 16, 2018 at 8:56 AM, Peter Eisentraut wrote: >> The attached patch seems to do the trick, of allowing EXECUTE + USING. >> I'm not sure this is worth the trouble, though, since EXECUTE as a plain >> SQL command is a PostgreSQL-extension anyway. > > I think it's a PostgreSQL extension th

Re: patch to allow disable of WAL recycling

2018-07-16 Thread Tom Lane
Andres Freund writes: > On 2018-07-15 20:55:38 -0400, Tom Lane wrote: >> That's not the way to think about it. On a COW file system, we don't >> want to "create 16MB files" at all --- we should just fill WAL files >> on-the-fly, because the pre-fill activity isn't actually serving the >> intended

Re: Parallel queries in single transaction

2018-07-16 Thread Paul Muntyanu
> Well, sure. But you could just as well open multiple connections and > make the queries concurrent that way. Or change the GUC to increase the > number of workers for the nightly ETL. This is an option right now for having permanent staging tables for future join. I mistakenly said ETL while it

Re: Usage of epoch in txid_current

2018-07-16 Thread Tom Lane
Andres Freund writes: > On 2018-07-15 16:41:35 -0400, Tom Lane wrote: >> Andres Freund writes: >>> On 2018-07-09 19:56:25 -0400, Tom Lane wrote: Or, perhaps, use a struct in assert builds and int64 otherwise? You could hide the ensuing notational differences in macros. >> [ bunch of te

Re: Pluggable Storage - Andres's take

2018-07-16 Thread Andres Freund
Hi, On 2018-07-05 15:25:25 +0300, Alexander Korotkov wrote: > > - I think the move of the indexing from outside the table layer into the > > storage layer isn't a good idea. It lead to having to pass EState into > > the tableam, a callback API to perform index updates, etc. This seems > > t

Re: Pluggable Storage - Andres's take

2018-07-16 Thread Andres Freund
Hi, I've pushed up a new version to https://github.com/anarazel/postgres-pluggable-storage which now passes all the tests. Besides a lot of bugfixes, I've rebased the tree, moved TriggerData to be primarily slot based (with a conversion roundtrip when calling trigger functions), and a lot of othe

Re: patch to allow disable of WAL recycling

2018-07-16 Thread Andres Freund
Hi, On 2018-07-15 20:55:38 -0400, Tom Lane wrote: > That's not the way to think about it. On a COW file system, we don't > want to "create 16MB files" at all --- we should just fill WAL files > on-the-fly, because the pre-fill activity isn't actually serving the > intended purpose of reserving di

Re: ENOSPC FailedAssertion("!(RefCountErrors == 0)"

2018-07-16 Thread Andres Freund
Hi, On 2018-07-15 18:48:43 -0400, Tom Lane wrote: > So basically, WAL replay hits an error while holding a buffer pin, and > nothing is done to release the buffer pin, but AtProcExit_Buffers thinks > something should have been done. I think there's a few other cases where we hit this. I've seen s

Re: patch to allow disable of WAL recycling

2018-07-16 Thread Andres Freund
On 2018-07-15 20:32:39 -0400, Robert Haas wrote: > On Thu, Jul 5, 2018 at 4:39 PM, Andres Freund wrote: > > This is formulated *WAY* too positive. It'll have dramatic *NEGATIVE* > > performance impact of non COW filesystems, and very likely even negative > > impacts in a number of COWed scenarios

Re: Usage of epoch in txid_current

2018-07-16 Thread Andres Freund
Hi, On 2018-07-15 16:41:35 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2018-07-09 19:56:25 -0400, Tom Lane wrote: > >> Or, perhaps, use a struct in assert builds and int64 otherwise? > >> You could hide the ensuing notational differences in macros. > > > That should be doable. But I'd

Re: cursors with prepared statements

2018-07-16 Thread Peter Eisentraut
On 11.07.18 19:07, Heikki Linnakangas wrote: >> One point worth pondering is how to pass the parameters of the prepared >> statements. The actual SQL standard syntax would be >> >> DECLARE cursor_name CURSOR FOR prepared_statement_name; >> OPEN cursor_name USING param, param; >> >> But s

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

2018-07-16 Thread Dean Rasheed
On 16 July 2018 at 13:23, Tomas Vondra wrote: >>> The top-level clauses allow us to make such deductions, with deeper >>> clauses it's much more difficult (perhaps impossible). Because for >>> example with (a=1 AND b=1) there can be just a single match, so if we >>> find it in MCV we're done. With

Re: Finding database for pg_upgrade missing library

2018-07-16 Thread Bruce Momjian
On Fri, Jul 13, 2018 at 10:15:34PM -0500, Justin T Pryzby wrote: > On Fri, Jul 13, 2018 at 12:28:15PM -0400, Bruce Momjian wrote: > > I received a private pg_upgrade feature request to report the database > > name for missing loadable libraries. Currently we report "could not > > load library" and

Re: Parallel queries in single transaction

2018-07-16 Thread Tomas Vondra
On 07/16/2018 12:03 PM, Paul Muntyanu wrote: Hi Tomas, thanks for looking into. I am more talking about queries which can not be optimized, e.g. * fullscan of the table and heavy calculations for another one. * query through FDW for both queries(e.g. one query fetches data from Kafka and ano

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

2018-07-16 Thread Tomas Vondra
On 07/15/2018 11:36 AM, Dean Rasheed wrote: On 13 July 2018 at 18:27, Tomas Vondra wrote: I'm not so sure. The issue is that a lot of the MCV deductions depends on whether we can answer questions like "Is there a single match?" or "If we got a match in MCV, do we need to look at the non-MCV

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

2018-07-16 Thread Tomas Vondra
On 07/16/2018 12:16 AM, Tomas Vondra wrote: On 07/15/2018 04:43 PM, Dean Rasheed wrote: On 15 July 2018 at 14:29, Tomas Vondra wrote: It's quite unclear to me how this algorithm could reliably end up with hist_sel=0 (in cases where we already don't end up with that). I mean, if a bucket ma

Re: Finding database for pg_upgrade missing library

2018-07-16 Thread Bruce Momjian
On Sat, Jul 14, 2018 at 12:14:51AM +0200, Daniel Gustafsson wrote: > > On 13 Jul 2018, at 18:28, Bruce Momjian wrote: > > > > I received a private pg_upgrade feature request to report the database > > name for missing loadable libraries. Currently we report "could not > > load library" and the l

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

2018-07-16 Thread Peter Eisentraut
On 12.07.18 11:12, Pavel Stehule wrote: > 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 implementation is > simple enough. > > One change perhaps: How about naming the default setting "auto" instead

Re: PATCH: psql tab completion for SELECT

2018-07-16 Thread Heikki Linnakangas
(trimmed CC list to evade gmail's spam filter, sorry) On 21/03/18 08:51, Edmund Horner wrote: Hi all, I haven't heard anything for a while and so assume you're beavering away on real features. :) I've been dogfooding this patch at work, and I am personally pretty happy with it. I still think t

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

2018-07-16 Thread Ashutosh Bapat
On Sat, Jul 14, 2018 at 2:41 AM, Alvaro Herrera wrote: > On 2018-Jul-13, Ashutosh Bapat wrote: > >> On Fri, Jul 13, 2018 at 1:15 PM, Amit Langote >> wrote: >> >> >> >> I don't think this is true. When equality conditions and IS NULL clauses >> >> cover >> >> all partition keys of a hash partitio

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

2018-07-16 Thread Michail Nikolaev
Hello. Thanks a lot for your feedback. I'll try to update patch in few days (currently stuck at small performance regression in unknown place). Regarding issue with delete: yes, it is valid point, but record removing should clear visibility buffer - and tuple will be fetched from heap to test its

Re: Fix some error handling for read() and errno

2018-07-16 Thread Michael Paquier
On Sat, Jul 14, 2018 at 03:37:56PM +0900, Michael Paquier wrote: > For now, I think that just moving forward with 0001, and then revisit > 0002 once the other 2PC patch is settled makes the most sense. On the > other thread, the current 2PC behavior can create silent data loss so > I would like to

Re: Parallel queries in single transaction

2018-07-16 Thread Paul Muntyanu
Hi Tomas, thanks for looking into. I am more talking about queries which can not be optimized, e.g. * fullscan of the table and heavy calculations for another one. * query through FDW for both queries(e.g. one query fetches data from Kafka and another one is fetching from remote Postgres. There are

Re: Parallel queries in single transaction

2018-07-16 Thread Tomas Vondra
Hi, On 07/16/2018 09:45 AM, Paul Muntyanu wrote: Hello,    I am working with data warehouse based on postgresql and would like to propose a feature. The idea is to give control and ability for developer to execute queries in parallel within single transaction. Usual flow is next: START_TRAN

Re: patch to allow disable of WAL recycling

2018-07-16 Thread Tomas Vondra
On 07/16/2018 04:54 AM, Stephen Frost wrote: Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: I think that the right basic idea is to have a GUC that chooses between the two implementations, but whether it can be set automatically is not clear to me. Can initdb perhaps investigate what kind o

Re: Libpq support to connect to standby server as priority

2018-07-16 Thread Haribabu Kommi
On Wed, Jul 11, 2018 at 6:00 PM Haribabu Kommi wrote: > > > On Wed, Jul 4, 2018 at 11:14 PM Laurenz Albe > wrote: > >> Haribabu Kommi wrote: >> >> > - I think the construction with "read_write_host_index" makes the code >> even more >> complicated than it already is. >> >> What about keeping

RE: Internal error XX000 with enable_partition_pruning=on, pg 11 beta1 on Debian

2018-07-16 Thread Phil Florent
I get it. Thank you for this precision. Regards Phil De : David Rowley Envoyé : lundi 16 juillet 2018 07:48 À : Phil Florent Cc : Tom Lane; Robert Haas; Amit Langote; PostgreSQL Hackers Objet : Re: Internal error XX000 with enable_partition_pruning=on, pg 11 bet

Re: Make foo=null a warning by default.

2018-07-16 Thread Fabien COELHO
Hello Heikki, The use case for transform_null_equals='warn' that I see is to wean off an application that uses transform_null_equals='on', to find all the queries that rely on it. But I'm not too excited about that either. The documented case for using transform_null_equals='on' is compatibi

Parallel queries in single transaction

2018-07-16 Thread Paul Muntyanu
Hello, I am working with data warehouse based on postgresql and would like to propose a feature. The idea is to give control and ability for developer to execute queries in parallel within single transaction. Usual flow is next: START_TRANSACTION -> QUERY1 -> QUERY2 -> QUERY3 -> END_TRANSACTION

Re: Make foo=null a warning by default.

2018-07-16 Thread Heikki Linnakangas
On 16/07/18 04:40, David Fetter wrote: Folks, Per a discussion with Andrew Gierth and Vik Fearing, both of whom helped make this happen, please find attached a patch which makes it possible to get SQL standard behavior for "= NULL", which is an error. It's been upgraded to a warning, and can sti

Re: Make foo=null a warning by default.

2018-07-16 Thread Fabien COELHO
Hello David, Per a discussion with Andrew Gierth and Vik Fearing, both of whom helped make this happen, please find attached a patch which makes it possible to get SQL standard behavior for "= NULL", which is an error. It's been upgraded to a warning, and can still be downgraded to silence (of

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

2018-07-16 Thread Haozhou Wang
+1, I also think that we may not change the previous behavior of plpython. @Nikita Glukhov maybe we just check the whether pyobject is int or long only in related conversion functions, and fallback otherwise? On Fri, Jul 13, 2018 at 12:09 AM Heikki Linnakangas wrote: > On 12/07/18 18:06, Nikita