Re: [HACKERS] checkpointer continuous flushing

I'd also like to see concurrent workloads with synchronous_commit=off - I've seen absolutely horrible latency behaviour for that, and I'm hoping this will help. It's also a good way to simulate faster hardware than you have. It helps. I've done a few runs, where the very-very-bad situation is

Re: [HACKERS] Time to get rid of PQnoPasswordSupplied?

On 22 June 2015 at 22:00, Tom Lane wrote: > I do not follow Craig's argument that this is somehow connected to the > wire protocol version. Upon revisiting it, neither do I. You know when you read code and think "what idiot wrote this" ... then "git blame" says it was you? I, at least, know tha

Re: [HACKERS] upper planner path-ification

> -Original Message- > From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane > Sent: Monday, May 18, 2015 1:12 AM > To: Robert Haas > Cc: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] upper planner path-ification > > Robert Haa

Re: [HACKERS] upper planner path-ification

> -Original Message- > From: David Rowley [mailto:david.row...@2ndquadrant.com] > Sent: Tuesday, June 23, 2015 2:06 PM > To: Kaigai Kouhei(海外 浩平) > Cc: Robert Haas; pgsql-hackers@postgresql.org; Tom Lane > Subject: Re: [HACKERS] upper planner path-ification > > > On 23 June 2015 at 13:55,

[HACKERS] how is a query passed between a coordinator and a datanode

Hello, I'm trying to figure out how a query and its result is passed between a coordinator and a datanode. I know there are many messages passed between them to finish a query. I did a test against the coordinator by adding a row to a table and the sql was, insert into hg1(id, name) values

Re: [HACKERS] PGXS "check" target forcing an install ?

On Tue, Jun 23, 2015 at 02:31:03PM +0900, Michael Paquier wrote: > On Tue, Jun 23, 2015 at 12:11 AM, Sandro Santilli wrote: > > I've noted that upgrading from PostgreSQL 9.3 to 9.5 I'm suddenly > > unable to specify a "check" rule in the Makefile that includes the > > PGXS one. The error is: > > >

Re: [HACKERS] 9.5 make world failing due to sgml tools missing

On 6/23/15 1:06 AM, Keith Fiske wrote: > > > > > > On Sun, Jun 21, 2015 at 10:56 AM, Peter Eisentraut > wrote: > > On 6/18/15 8:54 AM, Tom Lane wrote: > > Sure; the point is that libxml2 has suddenly been reclassified as a > > docum

[HACKERS] Memory context at PG_init call ?

Empirically, I seem to be getting the _PG_init call for a module while the active memory context lifetime is that of the function call which first needed to load the shared object. Is this the case ? Documented anywhere ? Initializing memory meant to be alive for the whole lifetime of a backend in

Re: [HACKERS] pg_rewind failure by file deletion in source server

On 06/23/2015 07:51 AM, Michael Paquier wrote: So... Attached are a set of patches dedicated at fixing this issue: Thanks for working on this! - 0001, add if_not_exists to pg_tablespace_location, returning NULL if path does not exist - 0002, same with pg_stat_file, returning NULL if file does

Re: [HACKERS] A couple of newlines missing in pg_rewind log entries

On 06/23/2015 07:39 AM, Michael Paquier wrote: Hi all, Some grepping is showing up that a couple of newlines are missing in pg_rewind, leading to unreadable log entries: libpq_fetch.c:pg_log(PG_DEBUG, "getting file chunks"); Fixed. logging.c:pg_log(PG_PROGRESS, "%*s/%s kB (%d%%) copi

Re: [HACKERS] [PATCH] introduce XLogLockBlockRangeForCleanup()

At 2014-08-20 11:07:44 +0300, hlinnakan...@vmware.com wrote: > > I don't think the new GetBufferWithoutRelcache function is in line > with the existing ReadBuffer API. I think it would be better to add a > new ReadBufferMode - RBM_CACHE_ONLY? - that only returns the buffer if > it's already in cach

Re: [HACKERS] upper planner path-ification

On Tue, Jun 23, 2015 at 4:41 AM, Kouhei Kaigai wrote: > So, unless we don't find out a solution around planner, 2-phase aggregation is > like a curry without rice Simon and I spoke with Tom about this upper planner path-ification problem at PGCon, and he indicated that he intended to work on

Re: [HACKERS] less log level for success dynamic background workers for 9.5

On Mon, Jun 22, 2015 at 9:47 PM, Tom Lane wrote: > Michael Paquier writes: >> On Tue, Jun 23, 2015 at 10:07 AM, Robert Haas wrote: >>> On Mon, Jun 22, 2015 at 8:19 PM, Jim Nasby wrote: Anything ever happen with this? I agree that LOG is to high for reporting most (if not all) of these t

Re: [HACKERS] Memory context at PG_init call ?

On Tue, Jun 23, 2015 at 7:41 AM, Sandro Santilli wrote: > Empirically, I seem to be getting the _PG_init call for a module while > the active memory context lifetime is that of the function call which > first needed to load the shared object. > > Is this the case ? Documented anywhere ? > Initiali

Re: [HACKERS] Insufficient locking for ALTER DEFAULT PRIVILEGES

On Sun, Jun 21, 2015 at 11:11 AM, Andres Freund wrote: > On 2015-06-21 11:45:24 -0300, Alvaro Herrera wrote: >> Alvaro Herrera wrote: >> > Now that I actually check with a non-relation object, I see pretty much >> > the same error. So probably if instead of some narrow bug fix what we >> > need i

Re: [HACKERS] pg_rewind failure by file deletion in source server

On Tue, Jun 23, 2015 at 9:19 PM, Heikki Linnakangas wrote: > On 06/23/2015 07:51 AM, Michael Paquier wrote: >> >> So... Attached are a set of patches dedicated at fixing this issue: > > > Thanks for working on this! > >> - 0001, add if_not_exists to pg_tablespace_location, returning NULL if >> pat

Re: [HACKERS] get_relation_info comment out of sync

On Sun, Jun 21, 2015 at 8:10 AM, Thomas Munro wrote: > The comment for get_relation_info should probably include serverid in > the list of rel members that it can update (see attached). Committed, thanks. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [HACKERS] pg_rewind failure by file deletion in source server

On 06/23/2015 05:03 PM, Fujii Masao wrote: On Tue, Jun 23, 2015 at 9:19 PM, Heikki Linnakangas wrote: On 06/23/2015 07:51 AM, Michael Paquier wrote: So... Attached are a set of patches dedicated at fixing this issue: Thanks for working on this! - 0001, add if_not_exists to pg_tablespace_

Re: [HACKERS] SSL TAP tests and chmod

On Sun, Jun 21, 2015 at 9:50 PM, Michael Paquier wrote: > On Fri, Jun 19, 2015 at 2:49 PM, Michael Paquier wrote: >> The TAP tests in src/test/ssl are using system() in combination with >> chmod, but perl has a command chmod integrated into it, and it would >> work better on Windows as well. >> Th

Re: [HACKERS] proposal: row_to_array function

2015-06-23 1:56 GMT+02:00 Jim Nasby : > On 6/22/15 2:46 AM, Pavel Stehule wrote: > >> >> FOREACH key, val IN RECORD myrow >> LOOP >>IF pg_typeof(val) IN ('int4', 'double precision', 'numeric') THEN >> val := val + 1; -- these variables can be mutable >> -- or maybe in futore >> m

Re: [HACKERS] less log level for success dynamic background workers for 9.5

2015-06-23 15:20 GMT+02:00 Robert Haas : > On Mon, Jun 22, 2015 at 9:47 PM, Tom Lane wrote: > > Michael Paquier writes: > >> On Tue, Jun 23, 2015 at 10:07 AM, Robert Haas wrote: > >>> On Mon, Jun 22, 2015 at 8:19 PM, Jim Nasby wrote: > Anything ever happen with this? I agree that LOG is to

Re: [HACKERS] less log level for success dynamic background workers for 9.5

On Tue, Jun 23, 2015 at 10:53 AM, Pavel Stehule wrote: > 2015-06-23 15:20 GMT+02:00 Robert Haas : >> I was thinking of a background worker flag, not a GUC. >> BGWORKER_QUIET, or something like that. But I guess we ought to just >> change it. > > I have not any problem with bg worker flag. The onl

Re: [HACKERS] Hash index creation warning

On Mon, Jun 22, 2015 at 8:46 PM, Michael Paquier wrote: > On Tue, Jun 23, 2015 at 9:06 AM, Jim Nasby wrote: >> On 6/12/15 5:00 PM, Thom Brown wrote: >>> >>> On 18 October 2014 at 15:36, Bruce Momjian wrote: On Fri, Oct 17, 2014 at 02:36:55PM -0400, Bruce Momjian wrote: > > On F

Re: [HACKERS] checkpointer continuous flushing

It'd be interesting to see numbers for tiny, without the overly small checkpoint timeout value. 30s is below the OS's writeback time. Here are some tests with longer timeout: tiny2: scale=10 shared_buffers=1GB checkpoint_timeout=5min max_wal_size=1GB warmup=600 time=4000 flsh |

[HACKERS] btree_gin and BETWEEN

If I use the btree_gin extension to build a gin index on a scalar value, it doesn't work well with BETWEEN queries. It looks like it scans the whole index, with the part of the index between the endpoints getting scanned twice. It is basically executed as if "col1 between x and y" were "col1 betw

Re: [HACKERS] pg_rewind failure by file deletion in source server

On Tue, Jun 23, 2015 at 11:21 PM, Heikki Linnakangas wrote: > On 06/23/2015 05:03 PM, Fujii Masao wrote: >> >> On Tue, Jun 23, 2015 at 9:19 PM, Heikki Linnakangas >> wrote: >>> >>> On 06/23/2015 07:51 AM, Michael Paquier wrote: So... Attached are a set of patches dedicated at fixin

Re: [HACKERS] NULL passed as an argument to memcmp() in parse_func.c

Glen Knowles writes: > It appears that, according to the standard, passing NULL to memcmp is > undefined behavior, even if the count is 0. See > http://stackoverflow.com/questions/16362925/can-i-pass-a-null-pointer-to-memcmp > for C99 and C++ standard references. Hmm ... looks like that's correct

Re: [HACKERS] less log level for success dynamic background workers for 9.5

Robert Haas wrote: > On Tue, Jun 23, 2015 at 10:53 AM, Pavel Stehule > wrote: > > 2015-06-23 15:20 GMT+02:00 Robert Haas : > >> I was thinking of a background worker flag, not a GUC. > >> BGWORKER_QUIET, or something like that. But I guess we ought to just > >> change it. > > > > I have not any

Re: [HACKERS] less log level for success dynamic background workers for 9.5

Alvaro Herrera writes: > Robert Haas wrote: >> Well, if the flag is BGWORKER_QUIET, then the default behavior remains >> unchanged, but when that flag is used, the log level is reduced to >> DEBUG1. That has the advantage of not breaking backward >> compatibility. But I'm not sure whether anyone

Re: [HACKERS] less log level for success dynamic background workers for 9.5

On Tue, Jun 23, 2015 at 1:21 PM, Tom Lane wrote: > Alvaro Herrera writes: >> Robert Haas wrote: >>> Well, if the flag is BGWORKER_QUIET, then the default behavior remains >>> unchanged, but when that flag is used, the log level is reduced to >>> DEBUG1. That has the advantage of not breaking bac

Re: [HACKERS] less log level for success dynamic background workers for 9.5

Robert Haas wrote: > On Tue, Jun 23, 2015 at 1:21 PM, Tom Lane wrote: > > However, I'm not real sure we need a flag. I think the use-case of > > wanting extra logging for a bgworker under development is unlikely to be > > satisfied very well by just causing existing start/stop logging messages >

Re: [HACKERS] pg_stat_*_columns?

On Tue, Jun 23, 2015 at 3:01 AM, Robert Haas wrote: > On Sun, Jun 21, 2015 at 11:43 AM, Magnus Hagander > wrote: > > On Sat, Jun 20, 2015 at 11:55 PM, Robert Haas > wrote: > >> On Sat, Jun 20, 2015 at 7:01 PM, Tom Lane wrote: > >> >> But if the structure > >> >> got too big to map (on a 32-bit

[HACKERS] Should we back-patch SSL renegotiation fixes?

Those of you who have been following http://www.postgresql.org/message-id/flat/1d3bc192-970d-4b70-a5fe-38d2a9f76...@me.com are aware that Red Hat shipped a rather broken version of openssl last week. While waiting for them to fix it, I've been poking at the behavior, and have found out that PG 9.4

Re: [HACKERS] pg_rewind failure by file deletion in source server

On Wed, Jun 24, 2015 at 1:40 AM, Fujii Masao wrote: > On Tue, Jun 23, 2015 at 11:21 PM, Heikki Linnakangas wrote: >> On 06/23/2015 05:03 PM, Fujii Masao wrote: >>> >>> On Tue, Jun 23, 2015 at 9:19 PM, Heikki Linnakangas >>> wrote: On 06/23/2015 07:51 AM, Michael Paquier wrote: > >>

Re: [HACKERS] Should we back-patch SSL renegotiation fixes?

On Tue, Jun 23, 2015 at 2:33 PM, Tom Lane wrote: > Those of you who have been following > http://www.postgresql.org/message-id/flat/1d3bc192-970d-4b70-a5fe-38d2a9f76...@me.com > are aware that Red Hat shipped a rather broken version of openssl last > week. While waiting for them to fix it, I've b

Re: [HACKERS] btree_gin and BETWEEN

Jeff Janes writes: > If I use the btree_gin extension to build a gin index on a scalar value, it > doesn't work well with BETWEEN queries. It looks like it scans the whole > index, with the part of the index between the endpoints getting scanned > twice. It is basically executed as if "col1 betw

Re: [HACKERS] less log level for success dynamic background workers for 9.5

On 6/23/15 12:21 PM, Tom Lane wrote: I concur: if we're to have a flag at all, it should work as Alvaro says. However, I'm not real sure we need a flag. I think the use-case of wanting extra logging for a bgworker under development is unlikely to be satisfied very well by just causing existing

Re: [HACKERS] pg_stat_*_columns?

On 6/22/15 8:05 PM, Robert Haas wrote: In totally different crazy way we could just use the existing buffer >manager we have and simply put the stats file in >shared_buffers. Inventing a per-database relfilenode that doesn't >conflict doesn't seem impossible. With some care it shouldn't be hard t

Re: [HACKERS] pg_stat_*_columns?

On 2015-06-22 21:05:52 -0400, Robert Haas wrote: > Interesting idea. We could consider the set of stats files a database > unto itself and reserve a low-numbered OID for it. The obvious thing > to do is use the database's OID as the relfilenode, but then how do > you replace the stats file? The

Re: [HACKERS] Should we back-patch SSL renegotiation fixes?

Robert Haas writes: > On Tue, Jun 23, 2015 at 2:33 PM, Tom Lane wrote: >> I do not know at this point whether these behaviors are really the same >> bug or not, but I wonder whether it's time to consider back-patching the >> renegotiation fixes we did in 9.4. Specifically, I think maybe we shoul

Re: [HACKERS] Should we back-patch SSL renegotiation fixes?

Robert Haas wrote: > On Tue, Jun 23, 2015 at 2:33 PM, Tom Lane wrote: > > I do not know at this point whether these behaviors are really the same > > bug or not, but I wonder whether it's time to consider back-patching the > > renegotiation fixes we did in 9.4. Specifically, I think maybe we sho

Re: [HACKERS] Should we back-patch SSL renegotiation fixes?

Alvaro Herrera writes: >> On Tue, Jun 23, 2015 at 2:33 PM, Tom Lane wrote: >>> I do not know at this point whether these behaviors are really the same >>> bug or not, but I wonder whether it's time to consider back-patching the >>> renegotiation fixes we did in 9.4. Specifically, I think maybe w

Re: [HACKERS] proposal: row_to_array function

On 6/23/15 9:45 AM, Pavel Stehule wrote: 2015-06-23 1:56 GMT+02:00 Jim Nasby mailto:jim.na...@bluetreble.com>>: On 6/22/15 2:46 AM, Pavel Stehule wrote: FOREACH key, val IN RECORD myrow LOOP IF pg_typeof(val) IN ('int4', 'double precision', 'numeric') T

Re: [HACKERS] checkpointer continuous flushing

On 6/22/15 11:59 PM, Fabien COELHO wrote: which might not be helpful for cases when checkpoint could have flushed soon-to-be-recycled buffers. I think flushing the sorted buffers w.r.t tablespaces is a good idea, but not giving any preference to clock-sweep point seems to me that we would loose i

Re: [HACKERS] proposal: row_to_array function

On Tue, Jun 23, 2015 at 2:57 PM, Jim Nasby wrote: > On 6/23/15 9:45 AM, Pavel Stehule wrote: >> 1. parametrized record reference syntax - some like SELECT $1[$] >> 2. possibility to throw plan cache, if result has different type than is >> expected in cache. > > > Well, the other option is we allo

Re: [HACKERS] Should we back-patch SSL renegotiation fixes?

On Tue, Jun 23, 2015 at 3:48 PM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Jun 23, 2015 at 2:33 PM, Tom Lane wrote: >>> I do not know at this point whether these behaviors are really the same >>> bug or not, but I wonder whether it's time to consider back-patching the >>> renegotiation fi

Re: [HACKERS] pg_stat_*_columns?

On Tue, Jun 23, 2015 at 3:47 PM, Andres Freund wrote: > On 2015-06-22 21:05:52 -0400, Robert Haas wrote: >> Interesting idea. We could consider the set of stats files a database >> unto itself and reserve a low-numbered OID for it. The obvious thing >> to do is use the database's OID as the relf

Re: [HACKERS] proposal: row_to_array function

2015-06-23 21:57 GMT+02:00 Jim Nasby : > On 6/23/15 9:45 AM, Pavel Stehule wrote: > >> >> 2015-06-23 1:56 GMT+02:00 Jim Nasby > >: >> >> >> On 6/22/15 2:46 AM, Pavel Stehule wrote: >> >> >> FOREACH key, val IN RECORD myrow >> LOOP >>

Re: [HACKERS] proposal: row_to_array function

On 6/23/15 3:22 PM, Merlin Moncure wrote: I would rephrase that to: "do X to all fields of an object". Array handling is pretty good now (minus arrays of arrays, but arrays Except that still won't make it easy to do something to each element of an array in SQL, which I think would be nice to h

Re: [HACKERS] proposal: row_to_array function

On 6/23/15 3:40 PM, Pavel Stehule wrote: BTW, I think this relates to the desire to be able to do more OO-ish things in the database. Like "do X to all elements in this array". And to have actual classes, private members, real arrays of arrays. It seems like there's a bigger need

[HACKERS] Making sure this isn't a new recovery bug ...

Hackers, At one site, I have some duplicate row corruption in a staging database. This database was created by a binary backup from 9.3.5, which was restored via PITR with a timestamp target to 9.3.5, so known-bad versions. The strange thing about the duplicate rows is that they were all frozen

Re: [HACKERS] 9.5 release notes

Andres Freund wrote: >> >> >>Improve concurrent locking and buffer scan performance (Andres >>Freund, Kevin Grittner) >> >> > > If this is ab5194e6f, I don't think it makes sense to mention "buffer > scan" - it's just any lwlock, and buffer locks aren't th

Re: [HACKERS] proposal: row_to_array function

On Tue, Jun 23, 2015 at 3:45 PM, Jim Nasby wrote: > On 6/23/15 3:22 PM, Merlin Moncure wrote: >> >> I would rephrase that to: "do X to all fields of an object". >> Array handling is pretty good now (minus arrays of arrays, but arrays > > > Except that still won't make it easy to do something to ea

[HACKERS] Oh, this is embarrassing: init file logic is still broken

Chasing a problem identified by my Salesforce colleagues led me to the conclusion that my commit f3b5565dd ("Use a safer method for determining whether relcache init file is stale") is rather borked. It causes pg_trigger_tgrelid_tgname_index to be omitted from the relcache init file, because that

Re: [HACKERS] upper planner path-ification

> -Original Message- > From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas > Sent: Tuesday, June 23, 2015 10:18 PM > To: Kaigai Kouhei(海外 浩平) > Cc: David Rowley; pgsql-hackers@postgresql.org; Tom Lane > Subject: Re: [HACKERS] upper

Re: [HACKERS] 9.5 release notes

On Tue, Jun 23, 2015 at 5:48 PM, Kevin Grittner wrote: > Andres Freund wrote: >>> >>> >>>Improve concurrent locking and buffer scan performance (Andres >>>Freund, Kevin Grittner) >>> >>> >> >> If this is ab5194e6f, I don't think it makes sense to mention

Re: [HACKERS] less log level for success dynamic background workers for 9.5

On 24 June 2015 at 03:23, Jim Nasby wrote: > On 6/23/15 12:21 PM, Tom Lane wrote: >> >> I concur: if we're to have a flag at all, it should work as Alvaro says. >> >> However, I'm not real sure we need a flag. I think the use-case of >> wanting extra logging for a bgworker under development is un

Re: [HACKERS] how is a query passed between a coordinator and a datanode

On Tue, Jun 23, 2015 at 5:07 AM, Rui Hai Jiang wrote: > I'm trying to figure out how a query and its result is passed between a > coordinator and a datanode. I know there are many messages passed between > them to finish a query. > > > I did a test against the coordinator by adding a row to a ta

Re: [HACKERS] git push hook to check for outdated timestamps

On Sun, Jun 14, 2015 at 12:37:00PM +0200, Magnus Hagander wrote: > On Fri, Jun 12, 2015 at 4:33 PM, Tom Lane wrote: > > Andrew Dunstan writes: > > > On 06/12/2015 09:31 AM, Robert Haas wrote: > > >> Could we update our git hook to refuse a push of a new commit whose > > >> timestamp is more than,

[HACKERS] Foreign join pushdown vs EvalPlanQual

Hi, While reviewing the foreign join pushdown core patch, I noticed that the patch doesn't perform an EvalPlanQual recheck properly. The example that crashes the server will be shown below (it uses the postgres_fdw patch [1]). I think the reason for that is because the ForeignScan node performin

Re: [HACKERS] checkpointer continuous flushing

On Tue, Jun 23, 2015 at 10:29 AM, Fabien COELHO wrote: > > Hello Amit, > > medium2: scale=300 shared_buffers=5GB checkpoint_timeout=30min >>> max_wal_size=4GB warmup=1200 time=7500 >>> >>> flsh | full speed tps | percent of late tx, 4 clients >>> /srt | 1 client | 4

Re: [HACKERS] pg_rewind failure by file deletion in source server

On Wed, Jun 24, 2015 at 3:36 AM, Michael Paquier wrote: > On Wed, Jun 24, 2015 at 1:40 AM, Fujii Masao wrote: >> On Tue, Jun 23, 2015 at 11:21 PM, Heikki Linnakangas wrote: >>> On 06/23/2015 05:03 PM, Fujii Masao wrote: On Tue, Jun 23, 2015 at 9:19 PM, Heikki Linnakangas wrote: >

Re: [HACKERS] checkpointer continuous flushing

flsh | full speed tps | percent of late tx, 4 clients /srt | 1 client | 4 clients | 100 | 200 | 400 | N/N | 173 +- 289* | 198 +- 531* | 27.61 | 43.92 | 61.16 | N/Y | 458 +- 327* | 743 +- 920* | 7.05 | 14.24 | 24.07 | Y/N | 169 +- 166* | 187 +- 302* | 4.01 | 3

Re: [HACKERS] checkpointer continuous flushing

I do not see how to do both, as these two orders seem more or less unrelated? The traditionnal assumption is that the I/O are very slow and they are to be optimized first, so going for buffer ordering to be nice to the disk looks like the priority. The point is that it's already expensive for

Re: [HACKERS] checkpointer continuous flushing

Besides, causing additional cacheline bouncing during the sorting process is a bad idea. Hmmm. The impact would be to multiply the memory required by 3 or 4 (buf_id, relation, forknum, offset), instead of just buf_id, and I understood that memory was a concern. Moreover, once the sort proc

Re: [HACKERS] pg_rewind failure by file deletion in source server

On Tue, Jun 23, 2015 at 9:19 PM, Heikki Linnakangas wrote: > On 06/23/2015 07:51 AM, Michael Paquier wrote: >> - 0001, add if_not_exists to pg_tablespace_location, returning NULL if >> path does not exist >> - 0002, same with pg_stat_file, returning NULL if file does not exist >> - 0003, same with