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

Re: [HACKERS] Wrong defeinition of pq_putmessage_noblock since 9.5

At Fri, 29 Jul 2016 12:47:53 +0900, Michael Paquier wrote in > 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 hav

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

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

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

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 have not been updated. >> > Some comments still use the old function name l

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 - in contrast to regi

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(). > > > Attached patch fixes the above issues.

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 virtue of the heap. I

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 basebackup

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) limitation of > MySQL

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: > > https://www.dropbox.com/s/t6a15s66jxg50tg/mysqlstrong_pgday16russia.p

[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) T

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

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

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 variable? > > > Good catch! It is totally useless unless bei

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 right and this 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 get rid of a pointer >>> based list here, and a) replace the queue with inte

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 th

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 http://fet

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 looking at the oldes

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 so you don't even > control

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

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 entr

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

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 itera

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 outside > start_postmaster. Just bein

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 curio

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

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 decisions. Why is it that you

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 offsets ... > > Here is a prototype of that idea. It re

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 Sure. It's also incr

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 .tar for -Z 0. I was thinking that the proposal is

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: > > ``` > extern void rb_begin_iterate

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: http://postgresql.nabble.com/Why-we-lost-Ub

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 want compression, he or s

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[]) r

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 waits for TCP >> retransmission timeout for the case of sud

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 > standby. > > My investigation told me tha

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 >>> wrote: On 7/12/16 12:53 PM, Jeff Janes wrote: > The --help message

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: The --help message for pg_basebackup says: -Z, --compress=0-9 co

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 a master to all slaves, which doesn't happen with statement > >> l

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. > > ​ > I'm not sure that that makes sense

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 database bug that occurs

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 bein

[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 *blocki

[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 a/src/bin/pg_upgrade/se

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 CF unless we decide this will >>> not get fixed for 9.6. >> >> It may make se

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: The --help message for pg_basebackup says: -Z, --compress=0-9 com

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 necessary. So I'm afraid that the pg_proc entry for the function