Re: [HACKERS] CTE inlining

2017-04-30 Thread Andres Freund
Hi, On 2017-04-30 13:58:14 +0800, Craig Ringer wrote: > We have OFFSET 0 for anyone really depending on it, and at least when you > read that you know to go "wtf" and look at the manual, wheras the CTE fence > behaviour is invisible and silent. I don't think that's a good idea. What if you need

Re: [HACKERS] On How To Shorten the Steep Learning Curve Towards PG Hacking...

2017-04-30 Thread Andrew Borodin
Hi, Kang and everyone in this thread. I'm planning to present the online course "Hacking PostgreSQL: data access methods in action and under the hood" on edX on June 1st. It's not announced yet, links will be available later. This course I'm describing information that was crucial for me to start

Re: [HACKERS] Declarative partitioning - another take

2017-04-30 Thread Noah Misch
On Mon, Apr 24, 2017 at 07:43:29PM +0900, Amit Langote wrote: > On 2017/04/21 17:00, Rajkumar Raghuwanshi wrote: > > I am able to create statement triggers at root partition, but these > > triggers, not getting fired on updating partition. > It would be great if you could check if the patches fix

Re: [HACKERS] Concurrent ALTER SEQUENCE RESTART Regression

2017-04-30 Thread Noah Misch
On Fri, Apr 28, 2017 at 04:55:45PM +0900, Michael Paquier wrote: > On Thu, Apr 27, 2017 at 4:10 PM, Andres Freund wrote: > > On April 27, 2017 12:06:55 AM PDT, Michael Paquier > > wrote: > >>On Thu, Apr 27, 2017 at 3:23 PM, Andres Freund

Re: [HACKERS] .pgpass's behavior has changed

2017-04-30 Thread Noah Misch
On Fri, Apr 28, 2017 at 04:54:32PM +0900, Kyotaro HORIGUCHI wrote: > I noticed that the precedence between host and hostaddr in a > connection string is reversed in regard to .pgpass lookup in > devel. > > For example the following connection string uses a .pgpass entry > with "127.0.0.1", not

Re: [HACKERS] snapbuild woes

2017-04-30 Thread Petr Jelinek
On 01/05/17 04:29, Craig Ringer wrote: > On 1 May 2017 at 09:54, Petr Jelinek wrote: > >> But, I still think we need to restart the tracking after new >> xl_running_xacts. Reason for that is afaics any of the catalog snapshots >> that we assigned to transactions at

Re: [HACKERS] OK, so culicidae is *still* broken

2017-04-30 Thread Amit Kapila
On Mon, May 1, 2017 at 10:23 AM, Tom Lane wrote: > Amit Kapila writes: >> Yeah, that's right. Today, I have spent some time to analyze how and >> where retry logic is required. I think there are two places where we >> need this retry logic, one is

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

2017-04-30 Thread Amit Langote
Hi Stephen, Sorry about the delay. On 2017/04/27 23:17, Stephen Frost wrote: > Amit, > > * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: So to summarize what the patches do (some of these were posted earlier) 0002: pg_dump: Do not emit WITH OPTIONS keywords with partition's

Re: [HACKERS] snapbuild woes

2017-04-30 Thread Petr Jelinek
On 01/05/17 03:35, Andres Freund wrote: > On 2017-04-30 17:59:21 -0700, Andres Freund wrote: >> ISTM, we need a xip_status array in SnapBuild->running. Then, whenever >> a xl_running_xacts is encountered while builder->running.xcnt > 0, loop >> for i in 0 .. xcnt_space, and end SnapBuildEndTxn()

Re: [HACKERS] CTE inlining

2017-04-30 Thread Tomas Vondra
On 04/30/2017 09:46 AM, Andres Freund wrote: Hi, On 2017-04-30 13:58:14 +0800, Craig Ringer wrote: We have OFFSET 0 for anyone really depending on it, and at least when you read that you know to go "wtf" and look at the manual, wheras the CTE fence behaviour is invisible and silent. I don't

Re: [HACKERS] CTE inlining

2017-04-30 Thread David Fetter
On Sun, Apr 30, 2017 at 11:54:48PM +0200, Tomas Vondra wrote: > On 04/30/2017 06:28 AM, Tom Lane wrote: > > Craig Ringer writes: > > > - as you noted, it is hard to decide when it's worth inlining vs > > > materializing for CTE terms referenced more than once. > > >

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

2017-04-30 Thread Sven R. Kunze
On 27.04.2017 22:21, Robert Haas wrote: On Thu, Apr 27, 2017 at 3:15 PM, Sven R. Kunze wrote: Just to make sound a little rounder: CREATE TABLE ... PARTITION OF ... AS DEFAULT CREATE TABLE ... PARTITION OF ... AS FALLBACK or CREATE TABLE ... PARTITION OF ... AS DEFAULT

Re: [HACKERS] snapbuild woes

2017-04-30 Thread Andres Freund
On 2017-04-30 17:59:21 -0700, Andres Freund wrote: > ISTM, we need a xip_status array in SnapBuild->running. Then, whenever > a xl_running_xacts is encountered while builder->running.xcnt > 0, loop > for i in 0 .. xcnt_space, and end SnapBuildEndTxn() if xip_status[i] but > the xid is not

Re: [HACKERS] Declarative partitioning - another take

2017-04-30 Thread Amit Langote
Thanks for the review. On 2017/04/29 1:25, Robert Haas wrote: > On Fri, Apr 28, 2017 at 2:13 AM, Amit Langote > wrote: By the way, code changes I made in the attached are such that a subsequent patch could implement firing statement-level triggers of all

Re: [HACKERS] CTE inlining

2017-04-30 Thread Pavel Stehule
2017-05-01 1:21 GMT+02:00 Andres Freund : > On 2017-04-30 07:19:21 +0200, Pavel Stehule wrote: > > why we cannot to introduce GUC option - enable_cteoptfence ? > > Doesn't really solve the issue, and we've generally shied away from GUCs > that influence behaviour after a few

Re: [HACKERS] Declarative partitioning - another take

2017-04-30 Thread Amit Langote
On 2017/04/29 6:56, David Fetter wrote: > On Fri, Apr 28, 2017 at 06:29:48PM +0900, Amit Langote wrote: >> On 2017/04/28 7:36, David Fetter wrote: >>> Did I notice correctly that there's no way to handle transition tables >>> for partitions in AFTER STATEMENT triggers? >> >> Did you mean to ask

Re: [HACKERS] OK, so culicidae is *still* broken

2017-04-30 Thread Tom Lane
Amit Kapila writes: > Yeah, that's right. Today, I have spent some time to analyze how and > where retry logic is required. I think there are two places where we > need this retry logic, one is if we fail to reserve the memory > (pgwin32_ReserveSharedMemoryRegion) and

Re: [HACKERS] CTE inlining

2017-04-30 Thread Andres Freund
On 2017-04-30 11:34:48 +0300, Ilya Shkuratov wrote: > Also, I would like to remind that the disabling optimization fence is > suggested > to be OPTIONAL. > So we don't break peoples' expectations, nor documented semantics. I think however is that that's not good enough, because it'll surprise

Re: [HACKERS] snapbuild woes

2017-04-30 Thread Andres Freund
Hi, On 2017-04-15 05:18:49 +0200, Petr Jelinek wrote: > + /* > + * c) we already seen the xl_running_xacts and tried to do the above. > + * However because of race condition in LogStandbySnapshot() there might > + * have been transaction reported as running but in reality has

Re: [HACKERS] CTE inlining

2017-04-30 Thread Tomas Vondra
On 04/30/2017 06:28 AM, Tom Lane wrote: Craig Ringer writes: - as you noted, it is hard to decide when it's worth inlining vs materializing for CTE terms referenced more than once. [ raised eyebrow... ] Please explain why the answer isn't trivially "never".

Re: [HACKERS] OK, so culicidae is *still* broken

2017-04-30 Thread Amit Kapila
On Tue, Apr 25, 2017 at 7:37 PM, Tom Lane wrote: > Craig Ringer writes: >> On 25 Apr. 2017 13:37, "Heikki Linnakangas" wrote: >>> For some data shared memory structures, that store no pointers, we wouldn't >>> need to insist that they

[HACKERS] Newbie project: Detect attempts to run pg_dump etc on psql, \copy on server

2017-04-30 Thread Craig Ringer
This is a proposal for a new developer patch idea, in case someone's interested. Keeping an eye on Stack Overflow has shown me that quite a few people seem to get confused by pg_dump and pg_restore being command line tools, not psql commands. It'd be interesting if psql detected this and gave a

Re: [HACKERS] CTE inlining

2017-04-30 Thread Andres Freund
On 2017-04-30 07:19:21 +0200, Pavel Stehule wrote: > why we cannot to introduce GUC option - enable_cteoptfence ? Doesn't really solve the issue, and we've generally shied away from GUCs that influence behaviour after a few bad experiences. What if you want one CTE inlined, but another one not?

Re: [HACKERS] Logical replication in the same cluster

2017-04-30 Thread Andres Freund
On 2017-04-26 23:41:51 +0200, Petr Jelinek wrote: > Yes that's result of how logical replication slots work, the transaction > that needs to finish is your transaction. It can be worked around by > creating the slot manually via the SQL interface for example and create > the subscription using

Re: [HACKERS] Crash when partition column specified twice

2017-04-30 Thread Amit Langote
On 2017/04/29 2:53, Robert Haas wrote: > On Fri, Apr 28, 2017 at 7:23 AM, Beena Emerson > wrote: >> Hello Amit, >> >> The extra n->is_from_type = false; seems to be added by mistake? >> >> @@ -11888,6 +11891,8 @@ TableFuncElement: ColId Typename >> opt_collate_clause

Re: [HACKERS] Re: logical replication and PANIC during shutdown checkpoint in publisher

2017-04-30 Thread Noah Misch
On Sat, Apr 29, 2017 at 11:18:45AM -0700, Noah Misch wrote: > On Tue, Apr 25, 2017 at 03:26:06PM -0400, Peter Eisentraut wrote: > > On 4/20/17 11:30, Peter Eisentraut wrote: > > > On 4/19/17 23:04, Noah Misch wrote: > > >> This PostgreSQL 10 open item is past due for your status update. Kindly >

Re: [HACKERS] snapbuild woes

2017-04-30 Thread Craig Ringer
On 1 May 2017 at 09:54, Petr Jelinek wrote: > But, I still think we need to restart the tracking after new > xl_running_xacts. Reason for that is afaics any of the catalog snapshots > that we assigned to transactions at the end of SnapBuildCommitTxn might > be