Re: [HACKERS] WAL logging problem in 9.4.3?

2016-07-28 Thread Michael Paquier
On Wed, Apr 6, 2016 at 3:11 PM, Michael Paquier wrote: > On Wed, Mar 23, 2016 at 12:45 PM, Michael Paquier > wrote: >> On Wed, Mar 23, 2016 at 11:11 AM, David Steele wrote: >>> I would prefer not to bump it to the next

[HACKERS] Wrong defeinition of pq_putmessage_noblock since 9.5

2016-07-28 Thread Kyotaro HORIGUCHI
Hello, While testing replication for 9.5, we found that repl-master can ignore wal_sender_timeout and seemingly waits for TCP retransmission timeout for the case of sudden power-off of a standby. My investigation told me that the immediate cause could be that secure_write() is called with

Re: [HACKERS] handling unconvertible error messages

2016-07-28 Thread Kyotaro HORIGUCHI
Hello, At Wed, 27 Jul 2016 19:53:01 +0800, Craig Ringer wrote in > On 25 July 2016 at 22:43, Peter Eisentraut > wrote: > > > Example: I have a database cluster

[HACKERS] pg_upgrade: exit_hook_registered variable

2016-07-28 Thread Artur Zakirov
Hello hackers, I noticed that exit_hook_registered variable in start_postmaster() is local variable. Shouldn't it be a static variable? I attached a patch. Thank you! -- Artur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company diff --git

Re: [HACKERS] pg_basebackup wish list

2016-07-28 Thread Fujii Masao
On Tue, Jul 26, 2016 at 3:28 PM, Fujii Masao wrote: > On Wed, Jul 13, 2016 at 3:06 AM, Jeff Janes wrote: >> On Tue, Jul 12, 2016 at 10:48 AM, Peter Eisentraut >> wrote: >>> On 7/12/16 12:53 PM, Jeff Janes wrote:

Re: [HACKERS] Why we lost Uber as a user

2016-07-28 Thread pgwhatever
Statement-Based replication has a lot of problems with it like indeterminate UDFs. Here is a link to see them all: https://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html#replication-sbr-rbr-sbr-disadvantages -- View this message in context:

Re: [HACKERS] Oddity in EXPLAIN for foreign/custom join pushdown plans

2016-07-28 Thread Kouhei Kaigai
> On 2016/07/28 10:01, Kouhei Kaigai wrote: > > What I'm saying is here: > > > EXPLAIN (COSTS false, VERBOSE) > > SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY > > t1.c3, t1.c1 OFFSET 100 LIMIT 10; > > QUERY PLAN > >

Re: [HACKERS] pg_basebackup wish list

2016-07-28 Thread Amit Kapila
On Thu, Jul 28, 2016 at 5:37 PM, Fujii Masao wrote: > > Maybe I failed to parse his proposal. It's helpful if you elaborate it. > As per mail [1], it seems the proposal is not to use .tar for -Z 0. Now here actually we are on the fence, one can argue that if user doesn't

Re: [HACKERS] pg_basebackup wish list

2016-07-28 Thread Fujii Masao
On Thu, Jul 28, 2016 at 10:16 PM, Amit Kapila wrote: > On Thu, Jul 28, 2016 at 5:37 PM, Fujii Masao wrote: >> >> Maybe I failed to parse his proposal. It's helpful if you elaborate it. >> > > As per mail [1], it seems the proposal is not to use

Re: [HACKERS] Why we lost Uber as a user

2016-07-28 Thread Geoff Winkless
On 27 July 2016 at 17:04, Bruce Momjian wrote: > Well, their big complaint about binary replication is that a bug can > spread from a master to all slaves, which doesn't happen with statement > level replication. ​ ​I'm not sure that that makes sense to me. If there's a

Re: [HACKERS] Wrong defeinition of pq_putmessage_noblock since 9.5

2016-07-28 Thread Fujii Masao
On Thu, Jul 28, 2016 at 6:52 PM, Kyotaro HORIGUCHI wrote: > Hello, > > While testing replication for 9.5, we found that repl-master can > ignore wal_sender_timeout and seemingly waits for TCP > retransmission timeout for the case of sudden power-off of a >

Re: [HACKERS] [Patch] RBTree iteration interface improvement

2016-07-28 Thread Amit Kapila
On Wed, Jul 27, 2016 at 7:56 PM, Aleksander Alekseev wrote: > Hello > > While working on some new feature for PostgreSQL (which is still in > development and is irrelevant in this context) I noticed that current > RBTree implementation interface is following: > > ``` >

Re: [HACKERS] LWLocks in DSM memory

2016-07-28 Thread Robert Haas
On Thu, Jul 28, 2016 at 12:12 AM, Thomas Munro wrote: > On Tue, Jul 26, 2016 at 6:00 AM, Andres Freund wrote: >> I think the better fix here would acutally be to get rid of a pointer >> based list here, and a) replace the queue with integer

Re: [HACKERS] Why we lost Uber as a user

2016-07-28 Thread Merlin Moncure
On Thu, Jul 28, 2016 at 8:16 AM, pgwhatever wrote: > Statement-Based replication has a lot of problems with it like indeterminate > UDFs. Here is a link to see them all: > https://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html#replication-sbr-rbr-sbr-disadvantages

Re: [HACKERS] pg_basebackup wish list

2016-07-28 Thread Amit Kapila
On Tue, Jul 26, 2016 at 11:58 AM, Fujii Masao wrote: > On Wed, Jul 13, 2016 at 3:06 AM, Jeff Janes wrote: >> On Tue, Jul 12, 2016 at 10:48 AM, Peter Eisentraut >> wrote: >>> On 7/12/16 12:53 PM, Jeff Janes wrote:

[HACKERS] Fix for PL/Python slow input arrays traversal issue

2016-07-28 Thread Alexey Grishchenko
Hi Following issue exists with PL/Python: when your function takes array as input parameters, processing arrays of fixed-size elements containing null values is many times slower than processing same array without nulls. Here is an example: -- Function create or replace function test(a int8[])

Re: [HACKERS] pg_basebackup wish list

2016-07-28 Thread Fujii Masao
On Thu, Jul 28, 2016 at 8:44 PM, Amit Kapila wrote: > On Tue, Jul 26, 2016 at 11:58 AM, Fujii Masao wrote: >> On Wed, Jul 13, 2016 at 3:06 AM, Jeff Janes wrote: >>> On Tue, Jul 12, 2016 at 10:48 AM, Peter Eisentraut >>>

Re: [HACKERS] Wrong defeinition of pq_putmessage_noblock since 9.5

2016-07-28 Thread Fujii Masao
On Thu, Jul 28, 2016 at 9:08 PM, Fujii Masao wrote: > On Thu, Jul 28, 2016 at 6:52 PM, Kyotaro HORIGUCHI > wrote: >> Hello, >> >> While testing replication for 9.5, we found that repl-master can >> ignore wal_sender_timeout and seemingly

Re: [HACKERS] Oddity in EXPLAIN for foreign/custom join pushdown plans

2016-07-28 Thread Etsuro Fujita
On 2016/07/28 10:01, Kouhei Kaigai wrote: What I'm saying is here: EXPLAIN (COSTS false, VERBOSE) SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; QUERY PLAN --- Limit

Re: [HACKERS] pg_upgrade: exit_hook_registered variable

2016-07-28 Thread Kyotaro HORIGUCHI
Hello, > I noticed that exit_hook_registered variable in start_postmaster() is > local variable. Shouldn't it be a static variable? > > I attached a patch. Thank you! Good catch! It is totally useless unless being static. But I think it is not necessary to go outside start_postmaster. Just

Re: [HACKERS] Why we lost Uber as a user

2016-07-28 Thread Vitaly Burovoy
On 7/28/16, Geoff Winkless wrote: > On 27 July 2016 at 17:04, Bruce Momjian wrote: > >> Well, their big complaint about binary replication is that a bug can >> spread from a master to all slaves, which doesn't happen with statement >> level replication. > >

Re: [HACKERS] Why we lost Uber as a user

2016-07-28 Thread Geoff Winkless
On 28 Jul 2016 12:19, "Vitaly Burovoy" wrote: > > On 7/28/16, Geoff Winkless wrote: > > On 27 July 2016 at 17:04, Bruce Momjian wrote: > > > >> Well, their big complaint about binary replication is that a bug can > >> spread from

Re: [HACKERS] Wrong defeinition of pq_putmessage_noblock since 9.5

2016-07-28 Thread Tom Lane
Fujii Masao writes: > 3. Several source comments in pqcomm.c have not been updated. > Some comments still use the old function name like pq_putmessage(). > Attached patch fixes the above issues. I dunno, this seems like it's doubling down on some extremely poor

Re: [HACKERS] Why we lost Uber as a user

2016-07-28 Thread Vladimir Sitnikov
> > > >> That's a recipe for runaway table bloat; VACUUM can't do much because > >> there's always some minutes-old transaction hanging around (and SNAPSHOT > >> TOO OLD doesn't really help, we're talking about minutes here), and > >> because of all of the indexes HOT isn't effective. > Just

[HACKERS] BRIN vs. HOT

2016-07-28 Thread Robert Haas
If I understand correctly, we currently deem an update to be non-HOT whenever any indexed column is updated. This is because tuple versions created by HOT updates can later be removed by HOT pruning, which means that they must not be referenced by index entries. Otherwise, after HOT pruning

Re: [HACKERS] BRIN vs. HOT

2016-07-28 Thread Petr Jelinek
On 28/07/16 16:53, Robert Haas wrote: If I understand correctly, we currently deem an update to be non-HOT whenever any indexed column is updated. This is because tuple versions created by HOT updates can later be removed by HOT pruning, which means that they must not be referenced by index

Re: [HACKERS] [Patch] RBTree iteration interface improvement

2016-07-28 Thread Aleksander Alekseev
> Can you explain use case where you need it? Sure. You can consider RBTree as a container that always keeps its elements in sorted order. Now imagine you would like to write a code like this: ``` /* iterate over items in sorted order */ while(item1 = left_right_walk(tree)) { /* another

Re: [HACKERS] pg_upgrade: exit_hook_registered variable

2016-07-28 Thread Tom Lane
Kyotaro HORIGUCHI writes: >> I noticed that exit_hook_registered variable in start_postmaster() is >> local variable. Shouldn't it be a static variable? > Good catch! It is totally useless unless being static. Indeed. > But I think it is not necessary to go

Re: [HACKERS] Why we lost Uber as a user

2016-07-28 Thread Alex Ignatov
On 28.07.2016 17:53, Vladimir Sitnikov wrote: >> That's a recipe for runaway table bloat; VACUUM can't do much because >> there's always some minutes-old transaction hanging around (and SNAPSHOT >> TOO OLD doesn't really help, we're talking about minutes here), and >>

Re: [HACKERS] pg_replication_origin_xact_reset() and its argument variables

2016-07-28 Thread Fujii Masao
On Sat, Jul 2, 2016 at 7:01 AM, Tom Lane wrote: > Andres Freund writes: >> On 2016-06-30 10:14:04 -0400, Tom Lane wrote: >>> Fujii Masao writes: As far as I read the code of the function, those arguments don't seem to be

Re: [HACKERS] [Patch] RBTree iteration interface improvement

2016-07-28 Thread Tom Lane
Aleksander Alekseev writes: >> Can you explain use case where you need it? > ... Or maybe you have different objects, e.g. IndexScanDesc's, that should > iterate over some tree's independently somewhere in indexam.c > procedures. Exact order may depend on user's query

Re: [HACKERS] old_snapshot_threshold allows heap:toast disagreement

2016-07-28 Thread Andres Freund
On 2016-07-28 15:40:48 -0400, Robert Haas wrote: > On Wed, Jul 27, 2016 at 7:26 PM, Andres Freund wrote: > >> Also, I wonder why it's right to use > >> pairingheap_first() instead of looking at the oldest snapshot on the > >> active snapshot stack, or conversely why that is

Re: [HACKERS] LWLocks in DSM memory

2016-07-28 Thread Thomas Munro
On Fri, Jul 29, 2016 at 2:12 AM, Robert Haas wrote: > On Thu, Jul 28, 2016 at 12:12 AM, Thomas Munro > wrote: >> On Tue, Jul 26, 2016 at 6:00 AM, Andres Freund wrote: >>> I think the better fix here would acutally be to

Re: [HACKERS] BRIN vs. HOT

2016-07-28 Thread David Fetter
On Thu, Jul 28, 2016 at 10:53:47AM -0400, Robert Haas wrote: > [1] I look forward to a future PostgreSQL conference in which the > struggle to pronounce "HMT" forms a recurring theme. For maximal confusion, this should really be called a Heap Often Tuple. Best, David. -- David Fetter

Re: [HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE

2016-07-28 Thread David Fetter
On Wed, Jul 27, 2016 at 02:59:17PM +0200, Vik Fearing wrote: > On 27/07/16 06:11, David Fetter wrote: > > On Wed, Jul 27, 2016 at 03:24:28AM +0200, Vik Fearing wrote: > >> On 27/07/16 03:15, Peter Eisentraut wrote: > >>> On 7/26/16 6:14 PM, Vik Fearing wrote: > As mentioned elsewhere in the

Re: [HACKERS] pg_upgrade: exit_hook_registered variable

2016-07-28 Thread Kyotaro HORIGUCHI
At Thu, 28 Jul 2016 10:58:24 -0400, Tom Lane wrote in <4782.1469717...@sss.pgh.pa.us> > Kyotaro HORIGUCHI writes: > >> I noticed that exit_hook_registered variable in start_postmaster() is > >> local variable. Shouldn't it be a static

Re: [HACKERS] "Strong sides of MySQL" talk from PgDay16Russia, translated

2016-07-28 Thread Tatsuo Ishii
> Following Uber's case discussion, I found this talk by Alexey Kopytov to be > really interesting: > http://kaamos.me/talks/pgday16/strongmysql/strongmysql.html (online html, > in Russian) > > I translated it to English: > >

Re: [HACKERS] old_snapshot_threshold allows heap:toast disagreement

2016-07-28 Thread Andres Freund
On 2016-07-28 23:08:13 -0400, Robert Haas wrote: > On Thu, Jul 28, 2016 at 7:29 PM, Andres Freund wrote: > > I think just iterating through the active snapshots would have been > > fine. Afaics there's no guarantee that the first active snapshot pushed > > is the relevant one

Re: [HACKERS] Oddity in EXPLAIN for foreign/custom join pushdown plans

2016-07-28 Thread Etsuro Fujita
On 2016/07/28 22:11, Kouhei Kaigai wrote: I wrote: That may be so, but my point is that the target relations involved in the foreign join (ie, ft1 and ft2) should be printed somewhere in the EXPLAIN output by core, as in EXPLAIN for a simple foreign table scan. Why? According to your rule,

[HACKERS] System load consideration before spawning parallel workers

2016-07-28 Thread Haribabu Kommi
we observed that spawning the specified number of parallel workers for every query that satisfies for parallelism is sometimes leading to performance drop compared to improvement during the peak system load with other processes. Adding more processes to the system is leading to more context

Re: [HACKERS] Why we lost Uber as a user

2016-07-28 Thread Josh Berkus
On 07/28/2016 03:58 AM, Geoff Winkless wrote: > On 27 July 2016 at 17:04, Bruce Momjian >wrote: > > Well, their big complaint about binary replication is that a bug can > spread from a master to all slaves, which doesn't happen with statement >

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-28 Thread Bruce Momjian
On Thu, Jul 28, 2016 at 09:22:17AM +0800, Craig Ringer wrote: > It might, actually. One approach for online upgrade is to: > > * pg_basebackup the master > * start the replica and let it catch up > * create a logical replication slot on the master > * replace the replication.conf on the

Re: [HACKERS] Wrong defeinition of pq_putmessage_noblock since 9.5

2016-07-28 Thread Michael Paquier
On Fri, Jul 29, 2016 at 12:18 PM, Kyotaro HORIGUCHI wrote: > At Thu, 28 Jul 2016 10:46:00 -0400, Tom Lane wrote in > <4313.1469717...@sss.pgh.pa.us> >> Fujii Masao writes: >> > 3. Several source comments in pqcomm.c

Re: [HACKERS] Oddity in EXPLAIN for foreign/custom join pushdown plans

2016-07-28 Thread Ashutosh Bapat
I wrote: > >> Probably something like this: >>> >>>Foreign Processing >>> Remote Operations: ... >>> >>> In the Remote Operations line, the FDW/extension could print any info >>> about remote operations, eg, "Scan/Join + Aggregate". >>> >> > "Foreign" implies this node is processed by

Re: [HACKERS] Wrong defeinition of pq_putmessage_noblock since 9.5

2016-07-28 Thread Kyotaro HORIGUCHI
At Fri, 29 Jul 2016 12:47:53 +0900, Michael Paquier wrote in

[HACKERS] "Strong sides of MySQL" talk from PgDay16Russia, translated

2016-07-28 Thread Nikolay Samokhvalov
Following Uber's case discussion, I found this talk by Alexey Kopytov to be really interesting: http://kaamos.me/talks/pgday16/strongmysql/strongmysql.html (online html, in Russian) I translated it to English: https://www.dropbox.com/s/t6a15s66jxg50tg/mysqlstrong_pgday16russia.pdf?dl=0 (pdf)

[HACKERS] Duplicate prototype for socket_set_nonblocking.

2016-07-28 Thread Kyotaro HORIGUCHI
Hello, Looking into pqcomm.c, I noticed that the prototype of socket_set_nonblocking defined twice. Actually the prototype is not necessary at all but one may remain as a convention. It doesn't no harm so backpatching seems unnecessary. regards, -- Kyotaro Horiguchi NTT Open Source Software

Re: [HACKERS] Oddity in EXPLAIN for foreign/custom join pushdown plans

2016-07-28 Thread Kouhei Kaigai
> I wrote: > >> That may be so, but my point is that the target relations involved in > >> the foreign join (ie, ft1 and ft2) should be printed somewhere in the > >> EXPLAIN output by core, as in EXPLAIN for a simple foreign table scan. > > > Why? According to your rule, Hash Join should take "on

Re: [HACKERS] "Strong sides of MySQL" talk from PgDay16Russia, translated

2016-07-28 Thread Nikolay Samokhvalov
On Fri, Jul 29, 2016 at 4:39 AM, Tatsuo Ishii wrote: > > Great translation. > > BTW, is there any opposite information, i.e. showing the limitation of > MySQL comparing with PostgreSQL? I'm not familiar with MySQL, but > occasionally hearing surprising (as a PostgreSQL user)

Re: [HACKERS] old_snapshot_threshold allows heap:toast disagreement

2016-07-28 Thread Robert Haas
On Thu, Jul 28, 2016 at 7:29 PM, Andres Freund wrote: > I think just iterating through the active snapshots would have been > fine. Afaics there's no guarantee that the first active snapshot pushed > is the relevant one - in contrast to registered one, which are ordered > by

Re: [HACKERS] Wrong defeinition of pq_putmessage_noblock since 9.5

2016-07-28 Thread Kyotaro HORIGUCHI
At Thu, 28 Jul 2016 10:46:00 -0400, Tom Lane wrote in <4313.1469717...@sss.pgh.pa.us> > Fujii Masao writes: > > 3. Several source comments in pqcomm.c have not been updated. > > Some comments still use the old function name like pq_putmessage(). >

[HACKERS] Double invocation of InitPostmasterChild in bgworker with -DEXEC_BACKEND

2016-07-28 Thread Thomas Munro
Hi I discovered that if you build with -DEXEC_BACKEND on a Unix system and then try to start a background worker, it dies in InitializeLatchSupport: TRAP: FailedAssertion("!(selfpipe_readfd == -1)", File: "latch.c", Line: 161) That's because InitPostmasterChild is called twice. I can

Re: [HACKERS] old_snapshot_threshold allows heap:toast disagreement

2016-07-28 Thread Robert Haas
On Wed, Jul 27, 2016 at 7:26 PM, Andres Freund wrote: > Why did you decide to introduce MaximumXLogRecPtr? Wouldn't just using > InvalidXLogRecPtr do the trick? That already prevents errors. Oh, right. >> Also, I wonder why it's right to use >> pairingheap_first() instead of

Re: [HACKERS] BRIN vs. HOT

2016-07-28 Thread Alvaro Herrera
Robert Haas wrote: > If I understand correctly, we currently deem an update to be non-HOT > whenever any indexed column is updated. This is because tuple > versions created by HOT updates can later be removed by HOT pruning, > which means that they must not be referenced by index entries. >