[HACKERS] System load consideration before spawning parallel workers

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] Wrong defeinition of pq_putmessage_noblock since 9.5

At Fri, 29 Jul 2016 12:47:53 +0900, Michael Paquier wrote in

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

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] Oddity in EXPLAIN for foreign/custom join pushdown plans

> 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] Oddity in EXPLAIN for foreign/custom join pushdown plans

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] Oddity in EXPLAIN for foreign/custom join pushdown plans

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,

Re: [HACKERS] Wrong defeinition of pq_putmessage_noblock since 9.5

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] old_snapshot_threshold allows heap:toast disagreement

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] Wrong defeinition of pq_putmessage_noblock since 9.5

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(). >

Re: [HACKERS] old_snapshot_threshold allows heap:toast disagreement

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] A Modest Upgrade Proposal

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] "Strong sides of MySQL" talk from PgDay16Russia, translated

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] "Strong sides of MySQL" talk from PgDay16Russia, translated

> 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: > >

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

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.

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] Why we lost Uber as a user

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] pg_upgrade: exit_hook_registered variable

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] old_snapshot_threshold allows heap:toast disagreement

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

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] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE

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] BRIN vs. HOT

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] old_snapshot_threshold allows heap:toast disagreement

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] [Patch] RBTree iteration interface improvement

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] BRIN vs. HOT

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. >

Re: [HACKERS] BRIN vs. HOT

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] Why we lost Uber as a user

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] [Patch] RBTree iteration interface improvement

> 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

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

> > > >> 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

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] Wrong defeinition of pq_putmessage_noblock since 9.5

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] LWLocks in DSM memory

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

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

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] [Patch] RBTree iteration interface improvement

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] Why we lost Uber as a user

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] pg_basebackup wish list

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] Oddity in EXPLAIN for foreign/custom join pushdown plans

> 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 > >

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

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] Wrong defeinition of pq_putmessage_noblock since 9.5

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] Wrong defeinition of pq_putmessage_noblock since 9.5

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] pg_basebackup wish list

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] Oddity in EXPLAIN for foreign/custom join pushdown plans

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_basebackup wish list

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:

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

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] Why we lost Uber as a user

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

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] pg_upgrade: exit_hook_registered variable

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

[HACKERS] Wrong defeinition of pq_putmessage_noblock since 9.5

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

[HACKERS] pg_upgrade: exit_hook_registered variable

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] WAL logging problem in 9.4.3?

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

Re: [HACKERS] pg_basebackup wish list

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] pg_replication_origin_xact_reset() and its argument variables

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] handling unconvertible error messages

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