Re: [HACKERS] RTE_NAMEDTUPLESTORE, enrtuples and comments

2017-06-11 Thread Thomas Munro
On Sun, Jun 11, 2017 at 6:25 PM, Noah Misch wrote: > While completing my annual src/backend/nodes/*funcs.c audit, I noticed defects > in commit 18ce3a4 changes to RangeTblEntry: > > 1. Field relid is under a comment saying it is valid for RTE_RELATION only. The comment is out of date. Here's a f

[HACKERS] PG10 Partitioned tables and relation_is_updatable()

2017-06-11 Thread Dean Rasheed
Hi, It looks like relation_is_updatable() didn't get the message about partitioned tables. Thus, for example, information_schema.views and information_schema.columns report that simple views built on top of partitioned tables are non-updatable, which is wrong. Attached is a patch to fix this. I t

[HACKERS] Buildfarm failures on woodlouse (in ecpg-check)

2017-06-11 Thread Christian Ullrich
Hello, my buildfarm animal woodlouse (Visual Studio 2013 on Windows 7) stopped working correctly some months ago. After Tom kindly prodded me into fixing it, I noticed that I had configured it to skip the ecpg-check step because one of the tests in the "thread" section (not always the same) n

[HACKERS] Re: [BUGS] BUG #14682: row level security not work with partitioned table

2017-06-11 Thread Joe Conway
On 06/09/2017 02:52 PM, Joe Conway wrote: > On 06/09/2017 06:16 AM, Joe Conway wrote: >> On 06/08/2017 11:09 PM, Noah Misch wrote: >>> On Wed, Jun 07, 2017 at 08:45:20AM -0700, Joe Conway wrote: On 06/07/2017 06:49 AM, Mike Palmiotto wrote: > I ended up narrowing it down to 4 tables (one

Re: [HACKERS] PG10 Partitioned tables and relation_is_updatable()

2017-06-11 Thread Joe Conway
On 06/11/2017 04:32 AM, Dean Rasheed wrote: > It looks like relation_is_updatable() didn't get the message about > partitioned tables. Thus, for example, information_schema.views and > information_schema.columns report that simple views built on top of > partitioned tables are non-updatable, which

Re: [HACKERS] PG10 Partitioned tables and relation_is_updatable()

2017-06-11 Thread Joe Conway
On 06/11/2017 08:55 AM, Joe Conway wrote: > On 06/11/2017 04:32 AM, Dean Rasheed wrote: >> It looks like relation_is_updatable() didn't get the message about >> partitioned tables. Thus, for example, information_schema.views and >> information_schema.columns report that simple views built on top of

[HACKERS] regproc and when to schema-qualify

2017-06-11 Thread Chapman Flack
I was idly following along in GSoC 2017: Foreign Key Arrays when I noticed this: =# select * from pg_amproc where amprocfamily = 2745; amprocfamily | amproclefttype | amprocrighttype | amprocnum | amproc --++-+---+ 2745 | 2277

Re: [HACKERS] Make ANALYZE more selective about what is a "most common value"?

2017-06-11 Thread Dean Rasheed
On 05/06/17 09:30, Tom Lane wrote: > First, I think we need a larger hard floor on the number of occurrences > of a value that're required to make ANALYZE decide it is a "most common > value"... > > Second, the code also has a rule that potential MCVs need to have an > estimated frequency at least

Re: [HACKERS] regproc and when to schema-qualify

2017-06-11 Thread Tom Lane
Chapman Flack writes: > The manual says regproc "will display schema-qualified names on output > if the object would not be found in the current search path without > being qualified." That's less than the full truth :-( > Is regproc displaying the schema in this case because there are two > ove

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

2017-06-11 Thread Peter Geoghegan
On Thu, Jun 1, 2017 at 8:40 AM, Tom Lane wrote: > The thing that would actually have a chance of improving matters for Q20 > would be if we could see our way to looking through the aggregation > subquery and applying the foreign key constraint for lineitem. That > seems like a research project th

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

2017-06-11 Thread Tom Lane
Peter Geoghegan writes: > On Thu, Jun 1, 2017 at 8:40 AM, Tom Lane wrote: >> The thing that would actually have a chance of improving matters for Q20 >> would be if we could see our way to looking through the aggregation >> subquery and applying the foreign key constraint for lineitem. That >> s

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

2017-06-11 Thread Peter Geoghegan
On Sun, Jun 11, 2017 at 10:36 AM, Tom Lane wrote: >> Do you mean teaching the optimizer to do something like this?: > > Uh, no. I don't think we want to add any run-time checks. The point in > this example is that we'd get a better rowcount estimate if we noticed > that the FK constraint could b

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

2017-06-11 Thread Tom Lane
Alvaro Herrera writes: > Interesting stuff. Here's a small recommendation for a couple of those > new messages. Hm. I don't object to folding those two messages into one, but now that I look at it, the text needs some more work anyway, perhaps. What we're actually checking is not so much wheth

Re: [HACKERS] Broken hint bits (freeze)

2017-06-11 Thread Vladimir Borodin
> 8 июня 2017 г., в 17:03, Amit Kapila написал(а): > > On Thu, Jun 8, 2017 at 6:49 PM, Dmitriy Sarafannikov > wrote: >> >>> Why didn't rsync made the copies on master and replica same? >> >> Because rsync was running with —size-only flag. >> > > IIUC the situation, the new WAL and updated p

Re: [HACKERS] PG10 Partitioned tables and relation_is_updatable()

2017-06-11 Thread Dean Rasheed
On 11 June 2017 at 16:59, Joe Conway wrote: > On 06/11/2017 08:55 AM, Joe Conway wrote: >> Yeah, I noticed the same while working on the RLS related patch. I did >> not see anything else in rewriteHandler.c but it is probably worth >> looking wider for other omissions. > > So in particular: > > #d

Re: [HACKERS] Make ANALYZE more selective about what is a "most common value"?

2017-06-11 Thread Tom Lane
Dean Rasheed writes: > I think we should attempt to come up with a more principled approach > to this, taking into account the table and sample sizes. Here's what I > found, after a bit of research: Thanks for doing some legwork on this! > A common initial rule of thumb is that the value should

Re: [HACKERS] Make ANALYZE more selective about what is a "most common value"?

2017-06-11 Thread Dean Rasheed
On 11 June 2017 at 20:19, Tom Lane wrote: >> The standard way of doing this is to calculate the "standard error" of >> the sample proportion - see, for example [3], [4]: >> SE = sqrt(p*(1-p)/n) >> Note, however, that this formula assumes that the sample size n is >> small compared to the populat

Re: [HACKERS] Buildfarm failures on woodlouse (in ecpg-check)

2017-06-11 Thread Andrew Dunstan
On 06/11/2017 11:33 AM, Christian Ullrich wrote: > Hello, > > my buildfarm animal woodlouse (Visual Studio 2013 on Windows 7) > stopped working correctly some months ago. After Tom kindly prodded me > into fixing it, I noticed that I had configured it to skip the > ecpg-check step because one of

Re: pgindent (was Re: [HACKERS] [COMMITTERS] pgsql: Preventive maintenance in advance of pgindent run.)

2017-06-11 Thread Piotr Stefaniak
>>> * the comments get formatted differently for -ts4 than -ts8 Still haven't put any thought into it, so I still don't know what to do here. >>> * extra spacing getting inserted for fairly long labels I think the fix is as easy as not producing the space. I committed that. >>> * some enum decl

[HACKERS] Transactional sequence stuff breaks pg_upgrade

2017-06-11 Thread Tom Lane
I believe I've identified the reason why skink and some other buildfarm members have been failing the pg_upgrade test recently. It is that recent changes in sequence support have caused binary-upgrade restore runs to do some sequence OID/relfilenode assignments without any heed to the OIDs that pg

Re: [HACKERS] PG10 transition tables, wCTEs and multiple operations on the same table

2017-06-11 Thread Andrew Gierth
> "Robert" == Robert Haas writes: Robert> I don't see a reason why MakeTransitionCaptureState needs to Robert> force the tuplestores into TopTransactionContext or make them Robert> owned by TopTransactionResourceOwner. Nor do I, and I'm pretty sure it's leaking memory wholesale within a t

Re: [HACKERS] SCRAM in the PG 10 release notes

2017-06-11 Thread Álvaro Hernández Tortosa
On 11/05/17 09:20, Heikki Linnakangas wrote: On 05/11/2017 07:03 AM, Michael Paquier wrote: On Thu, May 11, 2017 at 11:50 AM, Bruce Momjian wrote: I have added this as an open item because we will have to wait to see where we are with driver support as the release gets closer. As Postgres

Re: [HACKERS] transition table behavior with inheritance appears broken

2017-06-11 Thread Andrew Gierth
> "Andrew" == Andrew Gierth writes: Andrew> I have it; I will post a status update before 23:59 BST on 11 Andrew> Jun. This is that status update. I am still studying Thomas' latest patch set; as I mentioned in another message, I've confirmed a memory leak, and I expect further work may b

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

2017-06-11 Thread Peter Geoghegan
On Sun, Jun 11, 2017 at 10:27 AM, Peter Geoghegan wrote: > Note that I introduced a new, redundant exists() in the agg_lineitem > fact table subquery. It now takes 23 seconds for me on Tomas' 10GB > TPC-H dataset, whereas the original query took over 90 minutes. > Clearly we're missing a trick or

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

2017-06-11 Thread Tomas Vondra
Hi, On 6/11/17 7:54 PM, Peter Geoghegan wrote: On Sun, Jun 11, 2017 at 10:36 AM, Tom Lane wrote: Do you mean teaching the optimizer to do something like this?: Uh, no. I don't think we want to add any run-time checks. The point in this example is that we'd get a better rowcount estimate if

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

2017-06-11 Thread Peter Geoghegan
On Sun, Jun 11, 2017 at 4:10 PM, Tomas Vondra wrote: > I do strongly recommend reading this paper analyzing choke points of > individual TPC-H queries: > > http://oai.cwi.nl/oai/asset/21424/21424B.pdf > > It's slightly orthogonal to the issue at hand (poor estimate in Q20 causing > choice of i

Re: [HACKERS] Transactional sequence stuff breaks pg_upgrade

2017-06-11 Thread Tom Lane
I wrote: > I believe I've identified the reason why skink and some other buildfarm > members have been failing the pg_upgrade test recently. > ... > Not sure what we want to do about it. One idea is to make > ALTER SEQUENCE not so transactional when in binary-upgrade mode. On closer inspection, t

Re: [HACKERS] Proposal : For Auto-Prewarm.

2017-06-11 Thread Mithun Cy
Thanks, Amit, On Fri, Jun 9, 2017 at 8:07 PM, Amit Kapila wrote: > > Few comments on the latest patch: > + > + /* Prewarm buffer. */ > + buf = ReadBufferExtended(rel, blk->forknum, blk->blocknum, RBM_NORMAL, > + NULL); > + if (BufferIsValid(buf)) > + ReleaseBuffer(buf); > + > + old_blk = blk; >

[HACKERS] documentation typo in ALTER TABLE example

2017-06-11 Thread Yugo Nagata
Hi, Attached is a simple patch to fix a documentation typo in the ALTER TABLE example. Regards, -- Yugo Nagata diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 56ea830..4c61c44 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_t

Re: [HACKERS] Re: Alter subscription..SET - NOTICE message is coming for table which is already removed

2017-06-11 Thread Masahiko Sawada
On Fri, Jun 9, 2017 at 10:50 PM, Peter Eisentraut wrote: > On 5/30/17 13:25, Masahiko Sawada wrote: >> However there is one more problem here; if the relation status entry >> is deleted while corresponding table sync worker is waiting to be >> changed its status, the table sync worker can be orpha

Re: [HACKERS] documentation typo in ALTER TABLE example

2017-06-11 Thread Tatsuo Ishii
> Hi, > > Attached is a simple patch to fix a documentation typo in > the ALTER TABLE example. Or the original author's intention might have been something like this: --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1399,7 +1399,7 @@ ALTER TABLE cities Deta

Re: [HACKERS] transition table behavior with inheritance appears broken (was: Declarative partitioning - another take)

2017-06-11 Thread Thomas Munro
On Sat, Jun 10, 2017 at 6:08 AM, Robert Haas wrote: > I have spent some time now studying this patch. I might be missing > something, but to me this appears to be in great shape. A few minor > nitpicks: > > -if ((event == TRIGGER_EVENT_DELETE && > !trigdesc->trig_delete_after_row) || > -

Re: [HACKERS] PG10 transition tables, wCTEs and multiple operations on the same table

2017-06-11 Thread Thomas Munro
On Mon, Jun 12, 2017 at 9:29 AM, Andrew Gierth wrote: >> "Robert" == Robert Haas writes: > > Robert> I don't see a reason why MakeTransitionCaptureState needs to > Robert> force the tuplestores into TopTransactionContext or make them > Robert> owned by TopTransactionResourceOwner. > > Nor

Re: [HACKERS] Transition tables vs ON CONFLICT

2017-06-11 Thread Thomas Munro
On Fri, Jun 9, 2017 at 4:10 PM, Thomas Munro wrote: > On Wed, Jun 7, 2017 at 10:47 AM, Peter Geoghegan wrote: >> I suppose you'll need two tuplestores for the ON CONFLICT DO UPDATE >> case -- one for updated tuples, and the other for inserted tuples. > > [...] > > Here is a patch implementing the

Re: [HACKERS] Adding support for Default partition in partitioning

2017-06-11 Thread Jeevan Ladhe
Hi Ashutosh, I tried to look into your refactoring code. When applied all 3 patches, I got some regression failures, I have fixed all of them now in attached patches, attached the regression.diffs. Moving further, I have also made following changes in attached patches: *1. 0001-Refactor-ATExecAt

Re: [HACKERS] RTE_NAMEDTUPLESTORE, enrtuples and comments

2017-06-11 Thread Thomas Munro
On Sun, Jun 11, 2017 at 11:11 PM, Thomas Munro wrote: > On Sun, Jun 11, 2017 at 6:25 PM, Noah Misch wrote: >> This fourth point is not necessarily a defect: I wonder if RangeTblEntry is >> the right place for enrtuples. It's a concept regularly seen in planner data >> structures but not otherwis

Re: [HACKERS] Adding support for Default partition in partitioning

2017-06-11 Thread Ashutosh Bapat
While the refactoring seems a reasonable way to re-use existing code, that may change based on the discussion in [1]. Till then please keep the refactoring patches separate from the main patch. In the final version, I think the refactoring changes to ATAttachPartition and the default partition supp

Re: [HACKERS] ECPG: WHENEVER statement with DO CONTINUE action

2017-06-11 Thread vinayak
Hi, On 2017/06/10 12:23, Vinayak Pokale wrote: Thank you for your reply On Jun 9, 2017 5:39 PM, "Michael Meskes" > wrote: > > Could you please add a "DO CONTINUE" case to one of the test cases? Or > add a new one? We would need a test case IMO. > Yes I will add t

Re: [HACKERS] Getting server crash on Windows when using ICU collation

2017-06-11 Thread Ashutosh Sharma
PFA patch that fixes the issue described in above thread. As mentioned in the above thread, the crash is basically happening in varstr_cmp() function and it's only happening on Windows because in varstr_cmp(), if the collation provider is ICU, we don't even think of calling ICU functions to compa

Re: [HACKERS] documentation typo in ALTER TABLE example

2017-06-11 Thread Amit Langote
On 2017/06/12 11:01, Tatsuo Ishii wrote: >> Hi, >> >> Attached is a simple patch to fix a documentation typo in >> the ALTER TABLE example. > > Or the original author's intention might have been something like > this: > > --- a/doc/src/sgml/ref/alter_table.sgml > +++ b/doc/src/sgml/ref/alter_tabl

Re: [HACKERS] Buildfarm failures on woodlouse (in ecpg-check)

2017-06-11 Thread Christian Ullrich
* Andrew Dunstan wrote: On 06/11/2017 11:33 AM, Christian Ullrich wrote: To build correctly, it requires defining _WIN32_WINNT to be 0x600 or above (and using an SDK that knows about InitOnceExecuteOnce()). We already define _WIN32_WINNT to be 0x0600 on all appropriate platforms (Vista/200

Re: [HACKERS] documentation typo in ALTER TABLE example

2017-06-11 Thread Tatsuo Ishii
>> Amit Langote, which one was your intention? > > I wanted to show DETACH PARTITION command's usage with a range partitioned > table (detaching the "oldest" partition). > > So, we should apply Nagata-san's patch. Thank you for the confirmation. I have pushed the patch. Best regards, -- Tatsuo

[HACKERS] Why forcing Hot_standby_feedback to be enabled when creating a logical decoding slot on standby

2017-06-11 Thread sanyam jain
Hi, I have created a logical decoding slot on a standby but i haven't enabled Hot_standby_feedback.What are the test cases where this setup will fail? Thanks, Sanyam Jain

Re: [HACKERS] Adding support for Default partition in partitioning

2017-06-11 Thread Jeevan Ladhe
On Mon, Jun 12, 2017 at 9:39 AM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > While the refactoring seems a reasonable way to re-use existing code, > that may change based on the discussion in [1]. Till then please keep > the refactoring patches separate from the main patch. In the f

Re: [HACKERS] ICU support on Windows

2017-06-11 Thread Michael Paquier
On Sat, Jun 10, 2017 at 6:40 PM, Ashutosh Sharma wrote: > Currently, we cannot perform ICU enabled build for postgres on Windows > platform. However, this can be done on Linux platforms using > '--with-icu' configuration parameter. Attached is the patch that > allows us to perform icu enabled buil

Re: [HACKERS] Why forcing Hot_standby_feedback to be enabled when creating a logical decoding slot on standby

2017-06-11 Thread Michael Paquier
On Mon, Jun 12, 2017 at 3:16 PM, sanyam jain wrote: > I have created a logical decoding slot on a standby but i haven't enabled > Hot_standby_feedback.What are the test cases where this setup will fail? hot_standby_feedback needs to be enabled at all times in logical decoding so as the node does