[HACKERS] Reporting planning time with EXPLAIN

2016-12-26 Thread Ashutosh Bapat
Hi, We report planning and execution time when EXPLAIN ANALYZE is issued. We do not have facility to report planning time as part EXPLAIN output. In order to get the planning time, one has to issue EXPLAIN ANALYZE which involves executing the plan, which is unnecessary. We report planning and

[HACKERS] merging some features from plpgsql2 project

2016-12-26 Thread Pavel Stehule
Hi I reread ideas described on page https://github.com/trustly/plpgsql2 Some points are well and can be benefit for PlpgSQL. First I describe my initial position. I am strongly against introduction "new" language - plpgsql2 or new plpgsql, or any else. The trust of developers to us is important

Re: [HACKERS] postgres_fdw bug in 9.6

2016-12-26 Thread Etsuro Fujita
On 2016/12/22 1:04, Ashutosh Bapat wrote: Some review comments Thanks for the review! 2. We should try to look for other not-so-cheap paths if the cheapest one is paramterized. You might want to use get_cheapest_path_for_pathkeys() to find a suitable unparameterized path by passing NULL for

Re: [HACKERS] Potential data loss of 2PC files

2016-12-26 Thread Michael Paquier
On Fri, Dec 23, 2016 at 3:02 AM, Andres Freund wrote: > Not quite IIRC: that doesn't deal with file size increase. All this would be > easier if hardlinks wouldn't exist IIUC. It's basically a question whether > dentry, inode or contents need to be synced. Yes, it sucks.

Re: [HACKERS] Speedup twophase transactions

2016-12-26 Thread Michael Paquier
On Tue, Dec 27, 2016 at 12:59 PM, Stas Kelvich wrote: > Standard config with increased shared_buffers. I think the most significant > impact on the recovery speed here is on the client side, namely time between > prepare and commit. Right now I’m using pgbench script

Re: [HACKERS] Speedup twophase transactions

2016-12-26 Thread Stas Kelvich
> On 22 Dec 2016, at 05:35, Michael Paquier wrote: > > True. The more spread the checkpoints and 2PC files, the more risk to > require access to disk. Memory's cheap anyway. What was the system > memory? How many checkpoints did you trigger for how many 2PC files >

Re: [HACKERS] postgres_fdw bug in 9.6

2016-12-26 Thread Etsuro Fujita
On 2016/12/23 1:04, Robert Haas wrote: On Wed, Dec 21, 2016 at 11:04 AM, Ashutosh Bapat wrote: Some review comments 1. postgres_fdw doesn't push down semi and anti joins so you may want to discount these two too. + jointype == JOIN_SEMI || +

Re: [HACKERS] postgres_fdw bug in 9.6

2016-12-26 Thread Etsuro Fujita
On 2016/12/21 21:44, Etsuro Fujita wrote: On 2016/12/20 0:37, Tom Lane wrote: Etsuro Fujita writes: On 2016/12/17 1:13, Tom Lane wrote: So I think the rule could be "When first asked to produce a path for a given foreign joinrel, collect the cheapest paths for

[HACKERS] Commit fest 2017-01 will begin soon!

2016-12-26 Thread Michael Paquier
Hi all, As mentioned a couple of weeks back, I am fine to work as commit fest manager for 2017-01. Here is the commit fest status at the moment I am writing this email: Needs review: 68. Waiting on Author: 17. Ready for Committer: 18. Committed: 27. Rejected: 2. Returned with Feedback: 6. Total:

Re: [HACKERS] IF (NOT) EXISTS in psql-completion

2016-12-26 Thread Kyotaro HORIGUCHI
Hello, At Mon, 26 Dec 2016 14:24:33 +0100, Pavel Stehule wrote in pavel.stehule> 2016-12-26 9:40 GMT+01:00 Kyotaro HORIGUCHI >: > > > > Thanks for reviewing but I

Re: [HACKERS] [PATCH] Rename pg_switch_xlog to pg_switch_wal

2016-12-26 Thread Michael Paquier
On Thu, Dec 15, 2016 at 8:51 AM, Michael Paquier wrote: > On Thu, Dec 15, 2016 at 1:20 AM, Vladimir Rusinov wrote: >>> Personally, I think this is not important, but if you want to do it, I'd >>> follow the suggestion in the thread to rename all

Re: [HACKERS] BUG: pg_stat_statements query normalization issues with combined queries

2016-12-26 Thread Kyotaro HORIGUCHI
Hello, At Tue, 27 Dec 2016 10:28:53 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20161227.102853.204155513.horiguchi.kyot...@lab.ntt.co.jp> > Putting the two above together, the following is my suggestion > for the parser part. > > - Make all parse

Re: [HACKERS] gettimeofday is at the end of its usefulness?

2016-12-26 Thread Greg Stark
On Dec 26, 2016 10:35 PM, "Tom Lane" wrote: So it seems like the configure support we'd need is to detect whether clock_gettime is available (note on Linux there's also a library requirement, -lrt), and we would also need a way to provide a platform-specific choice of

Re: [HACKERS] Speedup twophase transactions

2016-12-26 Thread Michael Paquier
On Thu, Dec 22, 2016 at 7:35 AM, Michael Paquier wrote: > On Wed, Dec 21, 2016 at 10:37 PM, Stas Kelvich > wrote: >> ISTM your reasoning about filesystem cache applies here as well, but just >> without spending time on file creation. > >

Re: [HACKERS] BUG: pg_stat_statements query normalization issues with combined queries

2016-12-26 Thread Kyotaro HORIGUCHI
Hello, At Mon, 26 Dec 2016 16:00:57 +0100 (CET), Fabien COELHO wrote in > > Hello Craig, > > > Please put me (ringerc) down as a reviewer. > > Done. > > Please do not loose time reviewing stupid version 1... skip to version >

[HACKERS] Support for pg_receivexlog --format=plain|tar

2016-12-26 Thread Michael Paquier
Hi all, Since 56c7d8d4, pg_basebackup supports tar format when streaming WAL records. This has been done by introducing a new transparent routine layer to control the method used to fetch WAL walmethods.c: plain or tar. pg_receivexlog does not make use of that yet, but I think that it could to

Re: [HACKERS] Questions regarding signal handler of postmaster

2016-12-26 Thread Tatsuo Ishii
> But we keep signals blocked almost all the time in the postmaster, > so in reality no signal handler can interrupt anything except the > select() wait call. People complain about that coding technique > all the time, but no one has presented any reason to believe that > it's broken. Ok, there

Re: [HACKERS] Questions regarding signal handler of postmaster

2016-12-26 Thread Tatsuo Ishii
> I encountered that problem with postmaster and fixed it in 9.4.0 (it's not > back-patched to earlier releases because it's relatively complex). > > https://www.postgresql.org/message-id/20DAEA8949EC4E2289C6E8E58560DEC0@maumau > > > [Excerpt from 9.4 release note] > During crash recovery or

Re: [HACKERS] Questions regarding signal handler of postmaster

2016-12-26 Thread Tsunakawa, Takayuki
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii > In postmaster.c signal handler pmdie() calls ereport() and > errmsg_internal(), which could call palloc() then malloc() if necessary. > Because it is possible that pmdie() gets called

Re: [HACKERS] Questions regarding signal handler of postmaster

2016-12-26 Thread Tom Lane
Tatsuo Ishii writes: > In postmaster.c signal handler pmdie() calls ereport() and > errmsg_internal(), which could call palloc() then malloc() if > necessary. Because it is possible that pmdie() gets called while > malloc() gets called in postmaster, I think it is possible

[HACKERS] Questions regarding signal handler of postmaster

2016-12-26 Thread Tatsuo Ishii
In postmaster.c signal handler pmdie() calls ereport() and errmsg_internal(), which could call palloc() then malloc() if necessary. Because it is possible that pmdie() gets called while malloc() gets called in postmaster, I think it is possible that a deadlock situation could occur through an

Re: [HACKERS] gettimeofday is at the end of its usefulness?

2016-12-26 Thread Tom Lane
Haribabu Kommi writes: > Attached a patch that replaces most of the getimeofday function calls, > except timeofday(user callable) and GetCurrentTimestamp functions. > Didn't add any configure checks in case if the clock_gettime function is > not available, the fallback

Re: [HACKERS] [PATCH] Generic type subscription

2016-12-26 Thread Artur Zakirov
2016-12-26 18:49 GMT+03:00 Dmitry Dolgov <9erthali...@gmail.com>: >> On 5 December 2016 at 12:03, Haribabu Kommi >> wrote: > >> Moved to next CF with "needs review" status. > > Looks like we stuck here little bit. Does anyone else have any > suggestions/improvements, or

Re: [HACKERS] pg_stat_activity.waiting_start

2016-12-26 Thread Tom Lane
Joel Jacobson writes: > Maybe a good tradeoff then would be to let "wait_start" represent the > very first time the txn started waiting? > ... > As long as the documentation is clear on "wait_start" meaning when the > first wait started in the txn, I think that's useful enough

Re: [HACKERS] Incautious handling of overlength identifiers

2016-12-26 Thread Tom Lane
Alvaro Herrera writes: > I also admit it didn't occur to me to test the function(s) against > overlength input when developing it. I wouldn't object to adding code > to deal with overlength identifies, but I'm not really sure I would > choose truncation over reporting

Re: [HACKERS] propose to pushdown qual into EXCEPT

2016-12-26 Thread Tom Lane
I wrote: > tl;dr: I now think what the patch proposes to do is legit. There's a heck > of a lot of work to be done on the comments it's falsified, though. Hmm, wait a minute. I mentioned before that what nodeSetOp.c actually returns is N copies of the same representative tuple. That in itself

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Pavel Stehule
2016-12-26 19:13 GMT+01:00 Pavel Stehule : > > > 2016-12-26 18:20 GMT+01:00 Fabien COELHO : > >> >> >> >> And how would it interact with some "fully persistent/shared" variables? >> >> >> >> I have not any use case for this. I don't know about any

Re: [HACKERS] Incautious handling of overlength identifiers

2016-12-26 Thread Alvaro Herrera
Tom Lane wrote: > I wrote: > > Another idea worth considering is to just make the low-level functions > > do truncation ... > > After further thought, there's a bigger-picture issue here, which > is whether the inputs to the SQL functions in question are intended to > be raw user input --- in

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Pavel Stehule
2016-12-26 18:20 GMT+01:00 Fabien COELHO : > > Hello Pavel, > > I don't understand to "expensive" word. >> > > Hmmm... > > How much often you create/drop these variables? >> > > Hmmm... As for "session variables" à la MY/MS-SQL, ISTM that a variable is > "created" each time

Re: [HACKERS] Incautious handling of overlength identifiers

2016-12-26 Thread Tom Lane
I wrote: > Another idea worth considering is to just make the low-level functions > do truncation ... After further thought, there's a bigger-picture issue here, which is whether the inputs to the SQL functions in question are intended to be raw user input --- in which case, not only would

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Fabien COELHO
Hello Pavel, I don't understand to "expensive" word. Hmmm... How much often you create/drop these variables? Hmmm... As for "session variables" à la MY/MS-SQL, ISTM that a variable is "created" each time it is asked for, and it disappears completely at the end of the session... So you

Re: [HACKERS] Incautious handling of overlength identifiers

2016-12-26 Thread Tom Lane
Michael Paquier writes: > On Sat, Dec 24, 2016 at 7:44 AM, Joe Conway wrote: >> On 12/23/2016 12:44 PM, Tom Lane wrote: >>> An alternative worth considering, especially for the back branches, >>> is simply to remove the Assert in hashname(). That

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Pavel Stehule
2016-12-26 17:33 GMT+01:00 Fabien COELHO : > > please, can send link? >> > > My badly interpreted PL/SQL example was on the same page you point to > below: > > so some better documentation >> https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/packa >> ges.htm#LNPLS99926

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Fabien COELHO
please, can send link? My badly interpreted PL/SQL example was on the same page you point to below: so some better documentation https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/packages.htm#LNPLS99926 There is a private 'number_hired' which given its name I thought was counting

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Pavel Stehule
2016-12-26 17:15 GMT+01:00 Fabien COELHO : > > Hello Pavel, > > SET ROLE Admin; >>> DECLARE @secure_variable INTEGER RESTRICT; -- only accessible to Admin >>> >> > Why introduce another security system? >> > > That is a good question. > > I would prefer to avoid it and

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Fabien COELHO
Hello Pavel, SET ROLE Admin; DECLARE @secure_variable INTEGER RESTRICT; -- only accessible to Admin Why introduce another security system? That is a good question. I would prefer to avoid it and just have simple session variables... but this is not what you want, so I'm trying to

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

2016-12-26 Thread Alvaro Herrera
Alvaro Herrera wrote: > Jaime Casanova wrote: > > > * The isolation test for partial_index fails (attached the regression.diffs) > > Hmm, I had a very similar (if not identical) failure with indirect > indexes; in my case it was a bug in RelationGetIndexAttrBitmap() -- I > was missing to have

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

2016-12-26 Thread Alvaro Herrera
Jaime Casanova wrote: > * The isolation test for partial_index fails (attached the regression.diffs) Hmm, I had a very similar (if not identical) failure with indirect indexes; in my case it was a bug in RelationGetIndexAttrBitmap() -- I was missing to have HOT considerate the columns in index

Re: [HACKERS] [PATCH] Generic type subscription

2016-12-26 Thread Dmitry Dolgov
> On 5 December 2016 at 12:03, Haribabu Kommi wrote: > > Moved to next CF with "needs review" status. Looks like we stuck here little bit. Does anyone else have any suggestions/improvements, or this patch is in good enough shape?

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Pavel Stehule
2016-12-26 15:53 GMT+01:00 Fabien COELHO : > > Hello Pavel, > > AFAICS they are shared between backends, [...] They constitute a >>> consistent design. >>> >> >> no >> http://stackoverflow.com/questions/2383061/scope-of-oracle- >> package-level-variables >> > > If

Re: [HACKERS] BUG: pg_stat_statements query normalization issues with combined queries

2016-12-26 Thread Fabien COELHO
Hello Craig, Please put me (ringerc) down as a reviewer. Done. Please do not loose time reviewing stupid version 1... skip to version 2 directly:-) Also, note that the query-location part may be considered separately from the pg_stat_statements part which uses it. -- Fabien. -- Sent

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Fabien COELHO
Hello Pavel, AFAICS they are shared between backends, [...] They constitute a consistent design. no http://stackoverflow.com/questions/2383061/scope-of-oracle-package-level-variables If stackoverflow says so, too bad for me:-) Now I do not understand the point of the example I read on

Re: [HACKERS] BUG: pg_stat_statements query normalization issues with combined queries

2016-12-26 Thread Craig Ringer
On 23 Dec. 2016 17:53, "Fabien COELHO" wrote: Yes. I'll try to put together a patch and submit it to the next CF. > Here it is. I'll add this to the next CF. Great. Please put me (ringerc) down as a reviewer. I'll get to this as soon as holidays settle down. It'd be very

Re: [HACKERS] BUG: pg_stat_statements query normalization issues with combined queries

2016-12-26 Thread Craig Ringer
On 21 Dec. 2016 11:44, "Robert Haas" wrote: On Tue, Dec 20, 2016 at 6:18 AM, Fabien COELHO wrote: > Would this approach be acceptable, or is modifying Nodes a no-go area? > > If it is acceptable, I can probably put together a patch and submit it. > >

Re: [HACKERS] IF (NOT) EXISTS in psql-completion

2016-12-26 Thread Pavel Stehule
2016-12-26 9:40 GMT+01:00 Kyotaro HORIGUCHI : > > Thanks for reviewing but I ran out of time for this CF.. > > > > I'm going to move this to the next CF. > > I splitted the patch into small pieces. f3fd531 conflicted to > this so rebased onto the current master

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Pavel Stehule
> >> A possible compromise I have proposed is to have some declared access >> restrictions on simple session variables, so that say only the owner can >> access it, but they should stay and look like light-weight session >> variables nevertheless. That could look like: >> >> SET ROLE Admin; >>

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Pavel Stehule
2016-12-26 13:08 GMT+01:00 Fabien COELHO : > > > Hello Pavel, > > you are talk about light session variables like MSSQL or MySQL (with same >> syntax), I am talking about secure session variables like Oracle package >> variables (with similar access syntax). >> > > Hmmm. I do

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Fabien COELHO
Hello Pavel, you are talk about light session variables like MSSQL or MySQL (with same syntax), I am talking about secure session variables like Oracle package variables (with similar access syntax). Hmmm. I do not know this Oracle stuff... After looking at the online documentation, my

Re: [HACKERS] Declarative partitioning - another take

2016-12-26 Thread Amit Langote
Sorry about the delay in replying. On 2016/12/23 8:08, Robert Haas wrote: > On Thu, Dec 22, 2016 at 3:35 AM, Amit Langote > wrote: >> While working on that, I discovered yet-another-bug having to do with the >> tuple descriptor that's used as we route a tuple down

Re: [HACKERS] ALTER TABLE parent SET WITHOUT OIDS and the oid column

2016-12-26 Thread Amit Langote
On 2016/12/26 19:06, Amit Langote wrote: > I suspect the following is a bug: A better subject line could be: "ALTER TABLE INHERIT and the oid column" Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

[HACKERS] ALTER TABLE parent SET WITHOUT OIDS and the oid column

2016-12-26 Thread Amit Langote
I suspect the following is a bug: create table foo (a int) with oids; CREATE TABLE create table bar (a int); CREATE TABLE alter table bar inherit foo; ERROR: table "bar" without OIDs cannot inherit from table "foo" with OIDs alter table bar set with oids; ALTER TABLE alter table bar inherit

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Pavel Stehule
2016-12-26 10:54 GMT+01:00 Pavel Stehule : > Hi > > >> >> In both case, the syntax should be nice and elegant... i.e. not only >> based on functions, probably it should use some prefix convention (@, $)... >> For the light weight option. >> >> DECLARE @someday DATE [ =

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Pavel Stehule
Hi > > In both case, the syntax should be nice and elegant... i.e. not only based > on functions, probably it should use some prefix convention (@, $)... > For the light weight option. > > DECLARE @someday DATE [ = ] [visibility restriction?]; > ... then use @now as a possible value

Re: [HACKERS] Parallel bitmap heap scan

2016-12-26 Thread Dilip Kumar
On Wed, Dec 21, 2016 at 3:23 PM, Andres Freund wrote: Sorry for the delayed response. > If we go there, it seems better to also wrap the memory context based approach > in the allocator. One option is we can keep default allocator in the simple hash, and if the caller

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2016-12-26 Thread Kyotaro HORIGUCHI
At Wed, 21 Dec 2016 10:21:09 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20161221.102109.51106943.horiguchi.kyot...@lab.ntt.co.jp> > At Tue, 20 Dec 2016 15:10:21 -0500, Tom Lane wrote in > <23492.1482264...@sss.pgh.pa.us> > >

Re: [HACKERS] proposal: session server side variables

2016-12-26 Thread Fabien COELHO
Hello Pavel, On Sat, 24 Dec 2016, Pavel Stehule wrote: Maybe you could consider removing the part of the message that you are not responding to, so that it would be easier for the reader to see your answers and comments. Hmmm. So I understand that you would like to do something like: