Re: [HACKERS] pg_dump getBlobs query broken for 7.3 servers

2016-10-06 Thread Amit Langote
On 2016/10/07 11:47, Amit Langote wrote: > Just noticed that the getBlobs() query does not work for a 7.3 server > (maybe <= 7.3) due to the following change in commit 23f34fa4 [1]: > > else if (fout->remoteVersion >= 70100) > appendPQExpBufferStr(blobQry, > -

Re: [HACKERS] pgbench vs. wait events

2016-10-06 Thread Alfred Perlstein
Robert, This contention on WAL reminds me of another scenario I've heard about that was similar. To fix things what happened was that anyone that the first person to block would be responsible for writing out all buffers for anyone blocked behind "him". The for example if you have many

[HACKERS] pg_dump getBlobs query broken for 7.3 servers

2016-10-06 Thread Amit Langote
Just noticed that the getBlobs() query does not work for a 7.3 server (maybe <= 7.3) due to the following change in commit 23f34fa4 [1]: else if (fout->remoteVersion >= 70100) appendPQExpBufferStr(blobQry, - "SELECT DISTINCT loid, NULL::oid, NULL::oid" +

Re: [HACKERS] postgres_fdw : altering foreign table not invalidating prepare statement execution plan.

2016-10-06 Thread Etsuro Fujita
On 2016/10/07 10:26, Amit Langote wrote: On 2016/10/06 21:55, Etsuro Fujita wrote: On 2016/10/06 20:17, Amit Langote wrote: On 2016/10/05 20:45, Etsuro Fujita wrote: I noticed that we were wrong. Your patch was modified so that dependencies on FDW-related objects would be extracted from a

Re: [HACKERS] postgres_fdw : altering foreign table not invalidating prepare statement execution plan.

2016-10-06 Thread Amit Langote
On 2016/10/06 21:55, Etsuro Fujita wrote: > On 2016/10/06 20:17, Amit Langote wrote: >> On 2016/10/05 20:45, Etsuro Fujita wrote: >>> On 2016/10/05 14:09, Ashutosh Bapat wrote: IMO, maintaining that extra function and the risk of bugs because of not keeping those two functions in sync

Re: [HACKERS] VACUUM's ancillary tasks

2016-10-06 Thread Jeff Janes
On Thu, Oct 6, 2016 at 2:46 PM, Robert Haas wrote: > > > Also, I think that doing more counts which get amalgamated into the same > > threshold, rather than introducing another parameter, is a bad thing. I > > have insert-mostly, most of the time, tables which are never

Re: [HACKERS] pgbench vs. wait events

2016-10-06 Thread Michael Paquier
On Fri, Oct 7, 2016 at 3:38 AM, Robert Haas wrote: > I decided to do some testing on hydra (IBM-provided community > resource, POWER, 16 cores/64 threads, kernel 3.2.6-3.fc16.ppc64) using > the newly-enhanced wait event stuff to try to get an idea of what > we're waiting

Re: [HACKERS] amcheck (B-Tree integrity checking tool)

2016-10-06 Thread Michael Paquier
On Fri, Oct 7, 2016 at 8:05 AM, Peter Geoghegan wrote: > Your customer > databases might feature far more use of Japanese collations, for > example, which might be an important factor. Not really :) -- Michael -- Sent via pgsql-hackers mailing list

Re: [HACKERS] amcheck (B-Tree integrity checking tool)

2016-10-06 Thread Peter Geoghegan
On Sun, Oct 2, 2016 at 6:48 PM, Michael Paquier wrote: > Okay, moved to next CF. I may look at it finally I got some use-cases > for it, similar to yours I guess.. Let me know how that goes. One thing I've definitely noticed is that the tool is good at finding

Re: [HACKERS] WIP: About CMake v2

2016-10-06 Thread Yury Zhuravlev
Stas Kelvich wrote: On 17 Sep 2016, at 20:21, Yury Zhuravlev wrote: Michael Paquier wrote: ... Tried to generate Xcode project out of cmake, build fails on genbki.pl: can't locate Catalog.pm (which itself lives in src/backend/catalog/Catalog.pm) Can you

Re: [HACKERS] pgbench vs. wait events

2016-10-06 Thread Robert Haas
On Thu, Oct 6, 2016 at 4:40 PM, Jeff Janes wrote: > Scale factor 3000 obviously doesn't fit in shared_buffers. But does it fit > in RAM? That is, are the backends doing real IO, or they just doing fake IO > to the kernel's fs cache? That's a good question. [rhaas@hydra

Re: [HACKERS] VACUUM's ancillary tasks

2016-10-06 Thread Robert Haas
On Thu, Oct 6, 2016 at 3:56 PM, Jeff Janes wrote: >> Sure, I could handle each case separately, but the goal of this patch >> (as hinted at in the Subject) is to generalize all the different tasks >> we've been giving to VACUUM. The only missing piece is what the first >>

Re: [HACKERS] Switch to unnamed POSIX semaphores as our preferred sema code?

2016-10-06 Thread Tom Lane
Robert Haas writes: > Alternatively, get a bigger box. :-) So what's it take to get access to hydra? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] autonomous transactions

2016-10-06 Thread Simon Riggs
On 6 October 2016 at 21:27, Robert Haas wrote: >> * The labelling "Autonomous Transaction" is a simple coat of paint, >> which can easily be transferred to a better implementation if one >> comes. If one doesn't then its better to have something than nothing. >> So I

Re: [HACKERS] pgbench vs. wait events

2016-10-06 Thread Jeff Janes
On Thu, Oct 6, 2016 at 11:38 AM, Robert Haas wrote: > Hi, > > I decided to do some testing on hydra (IBM-provided community > resource, POWER, 16 cores/64 threads, kernel 3.2.6-3.fc16.ppc64) using > the newly-enhanced wait event stuff to try to get an idea of what > we're

Re: [HACKERS] autonomous transactions

2016-10-06 Thread Robert Haas
On Thu, Oct 6, 2016 at 5:56 AM, Simon Riggs wrote: > Just to point out that I've actually written this approach already. > The patch is available, Autonomous Subtransactions. > We discussed it in Ottawa and it was rejected. (I thought Robert was > there, but Serge and Tom

Re: [HACKERS] VACUUM's ancillary tasks

2016-10-06 Thread Jeff Janes
On Sat, Oct 1, 2016 at 1:34 PM, Vik Fearing wrote: > > Sure, I could handle each case separately, but the goal of this patch > (as hinted at in the Subject) is to generalize all the different tasks > we've been giving to VACUUM. The only missing piece is what the first >

Re: [HACKERS] PostgreSQL - Weak DH group

2016-10-06 Thread Christoph Berg
Re: Heikki Linnakangas 2016-10-06 > I propose the attached patch. It gives up on trying to deal with multiple > key lengths (as noted earlier, OpenSSL just always passed keylength=1024, so > that was useless). Instead of using the callback, it just

Re: [HACKERS] Switch to unnamed POSIX semaphores as our preferred sema code?

2016-10-06 Thread Robert Haas
On Thu, Oct 6, 2016 at 9:46 AM, Tom Lane wrote: > Can anyone think of a test case that would stress semaphore operations > more heavily, without being unrealistic? I think it's going to be pretty hard to come up with a non-artificial test case that has exhibits meaningful

[HACKERS] pgbench vs. wait events

2016-10-06 Thread Robert Haas
Hi, I decided to do some testing on hydra (IBM-provided community resource, POWER, 16 cores/64 threads, kernel 3.2.6-3.fc16.ppc64) using the newly-enhanced wait event stuff to try to get an idea of what we're waiting for during pgbench. I did 30-minute pgbench runs with various configurations,

[HACKERS] FSM corruption leading to errors

2016-10-06 Thread Pavan Deolasee
I investigated a bug report from one of our customers and it looked very similar to previous bug reports here [1], [2], [3] (and probably more). In these reports, the error looks something like this: ERROR: could not read block 28991 in file "base/16390/572026": read only 0 of 8192 bytes I

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

2016-10-06 Thread Vitaly Burovoy
On 10/6/16, Serge Rielau wrote: >> On Oct 6, 2016, at 9:20 AM, Tom Lane wrote: >> Vitaly Burovoy writes: >>> But what I discover for myself is that we have pg_attrdef separately >>> from the pg_attribute. Why? >> >> The core reason

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

2016-10-06 Thread Serge Rielau
> On Oct 6, 2016, at 9:20 AM, Tom Lane wrote: > > Vitaly Burovoy writes: >> But what I discover for myself is that we have pg_attrdef separately >> from the pg_attribute. Why? > > The core reason for that is that the default expression needs to be

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

2016-10-06 Thread Vitaly Burovoy
On 10/6/16, Vitaly Burovoy wrote: > Ough. I made a mistake about pg_attribute because I forgot about the > pg_attrdef. > If we do not merge these tables, the pg_attrdef is the best place to > store evaluated expression as a constant the same way defaults are > stored in

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

2016-10-06 Thread Vitaly Burovoy
On 10/6/16, Serge Rielau wrote: >> On Oct 6, 2016, at 9:01 AM, Tom Lane wrote: >> BTW, it also occurs to me that there are going to be good implementation >> reasons for restricting it to be a hard constant, not any sort of >> expression. We are likely to

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

2016-10-06 Thread Tom Lane
Vitaly Burovoy writes: > But what I discover for myself is that we have pg_attrdef separately > from the pg_attribute. Why? The core reason for that is that the default expression needs to be a separate object from the column for purposes of dependency analysis. For

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

2016-10-06 Thread Vitaly Burovoy
On 10/6/16, Tom Lane wrote: > Serge Rielau writes: >>> On Oct 6, 2016, at 5:25 AM, Vitaly Burovoy >>> wrote: Which makes me think we should call this missing_value or absent_value Be honest Simon Rigg's wrote that words.

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

2016-10-06 Thread Serge Rielau
> On Oct 6, 2016, at 9:01 AM, Tom Lane wrote: > > BTW, it also occurs to me that there are going to be good implementation > reasons for restricting it to be a hard constant, not any sort of > expression. We are likely to need to be able to insert the value in > low-level

Re: [HACKERS] memory leak in e94568ecc10 (pre-reading in external sort)

2016-10-06 Thread Peter Geoghegan
On Thu, Oct 6, 2016 at 8:44 AM, Peter Geoghegan wrote: > Besides, what I propose to do is really exactly the same as what you > also want to do, except it avoids actually changing state->maxTapes. > We'd just pass down what you propose to assign to state->maxTapes > directly,

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

2016-10-06 Thread Tom Lane
Serge Rielau writes: >> On Oct 6, 2016, at 5:25 AM, Vitaly Burovoy wrote: >>> Which makes me think we should call this missing_value or absent_value >>> so its clear that it is not a "default" it is the value we use for >>> rows that do not have any

Re: [HACKERS] memory leak in e94568ecc10 (pre-reading in external sort)

2016-10-06 Thread Peter Geoghegan
On Thu, Oct 6, 2016 at 12:00 AM, Heikki Linnakangas wrote: > This is related to earlier the discussion with Peter G, on whether we should > change state->maxTapes to reflect the actual number of tape that were used, > when that's less than maxTapes. I think his confusion about

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

2016-10-06 Thread Serge Rielau
> On Oct 6, 2016, at 5:25 AM, Vitaly Burovoy wrote: > > On 10/6/16, Simon Riggs wrote: >> On 6 October 2016 at 04:43, Serge Rielau wrote: > Or should I compose some sort of a design document? >> >> Having read this

Re: [HACKERS] Hash Indexes

2016-10-06 Thread Amit Kapila
On Wed, Oct 5, 2016 at 10:22 PM, Robert Haas wrote: > On Tue, Oct 4, 2016 at 12:36 AM, Amit Kapila wrote: >> I think one way to avoid the risk of deadlock in above scenario is to >> take the cleanup lock conditionally, if we get the cleanup lock

Re: [HACKERS] PostgreSQL - Weak DH group

2016-10-06 Thread Heikki Linnakangas
On 10/05/2016 09:57 PM, Heikki Linnakangas wrote: On 10/05/2016 05:15 PM, Nicolas Guini wrote: We are working with Postgres 9.3.14 and executing nmap we found that it is using “weak DH group” (nmap –script ssl-dh-params). Weak = 1024 bits. Yeah, it seems that we're a bit

Re: [HACKERS] Switch to unnamed POSIX semaphores as our preferred sema code?

2016-10-06 Thread Tom Lane
I wrote: > Although in normal cases the semaphore code paths aren't very heavily > exercised in our code, I was able to get a measurable performance > difference by building with --disable-spinlocks, so that spinlocks are > emulated with semaphores. On an 8-core RHEL6 machine, "pgbench -S -c 20 >

Re: [HACKERS] postgres_fdw : altering foreign table not invalidating prepare statement execution plan.

2016-10-06 Thread Etsuro Fujita
On 2016/10/06 20:17, Amit Langote wrote: On 2016/10/05 20:45, Etsuro Fujita wrote: On 2016/10/05 14:09, Ashutosh Bapat wrote: I wrote: So, I added a new callback function for those caches that is much like PlanCacheFuncCallback but skips checking the list for the query tree. IMO,

Re: [HACKERS] psql autocomplete works not good in USER command in 9.6

2016-10-06 Thread Michael Paquier
On Thu, Oct 6, 2016 at 9:17 PM, Дмитрий Воронин wrote: > I find, that autocomplete does not works good when I type in psql some USER > commands: > > CREATE USER ... > ALTER USER ... > > psql's autocomplete returns database users and "pg_signal_backend" function, > I

Re: [HACKERS] pg_rewind hangs if --source-server is used and syncrep is enabled

2016-10-06 Thread Michael Paquier
On Thu, Oct 6, 2016 at 7:37 PM, Heikki Linnakangas wrote: > Committed, thanks! I moved the call to where we establish the connection, > that felt slightly more natural. Thanks for the commit. Indeed that's better with the other sanity checks. -- Michael -- Sent via

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

2016-10-06 Thread Vitaly Burovoy
On 10/6/16, Simon Riggs wrote: > On 6 October 2016 at 04:43, Serge Rielau wrote: Or should I compose some sort of a design document? > > Having read this thread, I'm a little unclear as to what you're > writing now, though there's definitely good

[HACKERS] psql autocomplete works not good in USER command in 9.6

2016-10-06 Thread Дмитрий Воронин
Hello, I find, that autocomplete does not works good when I type in psql some USER commands: CREATE USER ... ALTER USER ... psql's autocomplete returns database users and "pg_signal_backend" function, I suppose, that is not correct. I try to undestand reasons and make a patch if those

Re: [HACKERS] autonomous transactions

2016-10-06 Thread Petr Jelinek
On 06/10/16 11:56, Simon Riggs wrote: > > * The labelling "Autonomous Transaction" is a simple coat of paint, > which can easily be transferred to a better implementation if one > comes. If one doesn't then its better to have something than nothing. > So I suggest we commit Background

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

2016-10-06 Thread Simon Riggs
On 6 October 2016 at 04:43, Serge Rielau wrote: >>> Or should I compose some sort of a design document? Having read this thread, I'm a little unclear as to what you're writing now, though there's definitely good ideas here. I think it would be beneficial to write up a single

Re: [HACKERS] postgres_fdw : altering foreign table not invalidating prepare statement execution plan.

2016-10-06 Thread Amit Langote
Thanks to both of you for taking this up and sorry about the delay in responding. On 2016/10/05 20:45, Etsuro Fujita wrote: > On 2016/10/05 14:09, Ashutosh Bapat wrote: >>> I think it would be a bit inefficient to use PlanCacheFuncCallback as the >>> inval callback function for those caches,

Re: [HACKERS] pg_rewind hangs if --source-server is used and syncrep is enabled

2016-10-06 Thread Heikki Linnakangas
On 10/06/2016 02:24 AM, Michael Paquier wrote: On Wed, Oct 5, 2016 at 11:53 PM, Michael Banck wrote: My colleague Christoph Berg pointed out that pg_rewind could just set synchronous_commit = local before creating the temporary table, which indeed works,

Re: [HACKERS] autonomous transactions

2016-10-06 Thread Simon Riggs
On 7 September 2016 at 20:46, Robert Haas wrote: > On Sat, Sep 3, 2016 at 7:09 AM, Simon Riggs wrote: >> On 2 September 2016 at 09:45, Robert Haas wrote: >>> On Wed, Aug 31, 2016 at 7:20 AM, Peter Eisentraut >>>

Re: [HACKERS] Transactions involving multiple postgres foreign servers

2016-10-06 Thread Amit Langote
On 2016/10/06 17:45, Ashutosh Bapat wrote: > On Thu, Oct 6, 2016 at 1:34 PM, Masahiko Sawada wrote: >> On Thu, Oct 6, 2016 at 1:41 PM, Ashutosh Bapat >> wrote: My understanding is that basically the local server can not return

Re: [HACKERS] Transactions involving multiple postgres foreign servers

2016-10-06 Thread Ashutosh Bapat
On Thu, Oct 6, 2016 at 1:34 PM, Masahiko Sawada wrote: > On Thu, Oct 6, 2016 at 1:41 PM, Ashutosh Bapat > wrote: No, the COMMIT returns after the first phase. It can not wait for all the foreign servers to complete their

Re: [HACKERS] Transactions involving multiple postgres foreign servers

2016-10-06 Thread Masahiko Sawada
On Thu, Oct 6, 2016 at 1:41 PM, Ashutosh Bapat wrote: >>> >>> No, the COMMIT returns after the first phase. It can not wait for all >>> the foreign servers to complete their second phase >> >> Hm, it sounds like it's same as normal commit (not 2PC). >> What's the

Re: [HACKERS] Feature suggestion: ON CONFLICT DO NOTHING RETURNING which returns existing row data

2016-10-06 Thread Tom Dunstan
> On 5 Oct 2016, at 8:11 PM, Pantelis Theodosiou wrote: > > This can be solved by chaining modifying CTEs. > > Something like this (not tested) that can work with multiple rows inserted: Thanks for the suggestion, but it was actually slower than our current

Re: [HACKERS] memory leak in e94568ecc10 (pre-reading in external sort)

2016-10-06 Thread Heikki Linnakangas
On 10/06/2016 07:50 AM, Tomas Vondra wrote: it seems e94568ecc10 has a pretty bad memory leak. A simple Oops, fixed, thanks for the report! To be precise, this wasn't a memory leak, just a gross overallocation of memory. The new code in tuplesort.c assumes that it's harmless to call

Re: [HACKERS] Relids in upper relations

2016-10-06 Thread Ashutosh Bapat
On Wed, Oct 5, 2016 at 7:12 PM, Tom Lane wrote: > Ashutosh Bapat writes: >> While reviewing aggregate pushdown patch [1] we noticed that >> RelOptInfos for upper relations do not have relids set. > > Indeed, because they don't correspond to