Re: pg_recvlogical broken in back branches

On Tue, Apr 17, 2018 at 03:01:33AM -0300, Euler Taveira wrote: > A proposed fix is attached. It should be applied to 9.4, 9.5, 9.6, and > 10. (Although, client version 10 can connect to server version 10, > client version 10 can't connect to server version 9.6.) > > Comments? The exact same fix h

pg_recvlogical broken in back branches

Hi, An issue [1] reported that pg_recvlogical emitted an error in 9.6.8. I confirmed that it was broken in recent minor versions (9.6.8, 9.5.12, 9.4.17 -- using same server/client version). It was broken by commit 582edc369cdbd348d68441fc50fa26a84afd0c1a and its siblings. $ postgres --version pos

Re: SHOW ALL does not honor pg_read_all_settings membership

On Mon, Apr 16, 2018 at 02:32:10PM +0200, Laurenz Albe wrote: > Now that the dust from the last commitfest is settling, I'll make a second > attempt to attract attention for this small bug fix. > > The original commit was Simon's. Thanks for the ping. This was new as of v10, so this cannot be li

Re: Fix slot's xmin advancement and subxact's lost snapshots in decoding.

On Sun, Apr 08, 2018 at 08:46:04AM +0300, Arseny Sher wrote: > I've discovered a couple of bugs in logical decoding code, both leading > to incorrect decoding results in somewhat rare cases. First, xmin of > slots is advanced too early. This affects the results only when > interlocking allows to pe

Re: Proposal: Adding json logging

On Mon, Apr 16, 2018 at 07:52:58PM -0400, Peter Eisentraut wrote: > I have used https://github.com/mpihlak/pg_logforward in the past, which > seems to be about the same thing. Didn't know this one. Thanks. > I have also had good success using syslog. While syslog is not very > structured, the s

Re: Overcoming SELECT ... FOR UPDATE permission restrictions

Michael Paquier writes: > I also don't quite understand the argument of application relying on > this behavior. If they do, that's wrong anyway, so the risk of > operation disruptions for shared environments would matter more in my > opinion. I'm not totally sure about that. If you suppose that

Re: [HACKERS] Runtime Partition Pruning

On 17 April 2018 at 14:33, Alvaro Herrera wrote: > David Rowley wrote: > >> For a while, during my review of the faster partition pruning patch I >> was asking Amit to add pfree() calls in various places for this exact >> reason, but in the end, I gave up and decided it was easier to just >> creat

Re: [HACKERS] Runtime Partition Pruning

David Rowley wrote: > For a while, during my review of the faster partition pruning patch I > was asking Amit to add pfree() calls in various places for this exact > reason, but in the end, I gave up and decided it was easier to just > create a new memory context to call the planner function from.

Re: Overcoming SELECT ... FOR UPDATE permission restrictions

On Mon, Apr 16, 2018 at 08:12:45PM +0300, Alexander Lakhin wrote: > The worst scenario (with the current system views) I could think of is: > user=> CREATE VIEW pgg AS SELECT * FROM pg_group; > BEGIN TRANSACTION; SELECT * FROM pgg FOR UPDATE; SELECT pg_sleep(60); > ROLLBACK; > and the parallel oper

Re: Oddity in tuple routing for foreign partitions

Hello. At Tue, 17 Apr 2018 10:10:38 +0900, Amit Langote wrote in > Fujita-san, > > On 2018/04/16 20:25, Etsuro Fujita wrote: > > Hi, > > > > While updating the fix-postgres_fdw-WCO-handling patch, I noticed that > > the patch for tuple routing for foreign partitions doesn't work well > > wit

Re: Oddity in tuple routing for foreign partitions

Hi Amit, (2018/04/17 10:10), Amit Langote wrote: > On 2018/04/16 20:25, Etsuro Fujita wrote: >> While updating the fix-postgres_fdw-WCO-handling patch, I noticed that >> the patch for tuple routing for foreign partitions doesn't work well >> with remote triggers. Here is an example: >> >> postgre

Re: Oddity in tuple routing for foreign partitions

Fujita-san, On 2018/04/16 20:25, Etsuro Fujita wrote: > Hi, > > While updating the fix-postgres_fdw-WCO-handling patch, I noticed that > the patch for tuple routing for foreign partitions doesn't work well > with remote triggers. Here is an example: > > postgres=# create table loct1 (a int chec

Re: [HACKERS] Runtime Partition Pruning

On 14 April 2018 at 05:04, Robert Haas wrote: > But I wonder why it's the executor's job to clean up after the > planner, instead of adjusting the relevant planner functions to avoid > leaking memory? It might be possible, but it might also be risky and difficult. For a while, during my review o

Re: Proposal: Adding json logging

On 4/13/18 20:00, David Arnold wrote: > I have reviewed some log samples and all DO contain some kind of multi > line logs which are very uncomfortable to parse reliably in a log streamer. > > I asked Michael Paquier about his > solution: https://github.com/michaelpq/pg_plugins/tree/master/jsonlog

Re: Instability in partition_prune test?

On 17 April 2018 at 09:31, Tom Lane wrote: > Alvaro Herrera writes: >> Tom Lane wrote: >>> Seems reasonable. I'm still uncomfortable with the assumption >>> that if we ask for two workers we will get two workers, but >>> that's a pre-existing problem in other parallel regression tests. > >> Yeah

Re: Instability in partition_prune test?

Alvaro Herrera writes: > Tom Lane wrote: >> Seems reasonable. I'm still uncomfortable with the assumption >> that if we ask for two workers we will get two workers, but >> that's a pre-existing problem in other parallel regression tests. > Yeah, I was looking at that line and wondering. But I t

Re: Instability in partition_prune test?

Tom Lane wrote: > Alvaro Herrera writes: > > Tom Lane wrote: > >> Yeah, loss of executor code coverage was what concerned me. > > > Here's a proposed patch for this. > > Seems reasonable. I'm still uncomfortable with the assumption > that if we ask for two workers we will get two workers, but >

Re: Instability in partition_prune test?

Alvaro Herrera writes: > Tom Lane wrote: >> Yeah, loss of executor code coverage was what concerned me. > Here's a proposed patch for this. Seems reasonable. I'm still uncomfortable with the assumption that if we ask for two workers we will get two workers, but that's a pre-existing problem in

Re: Instability in partition_prune test?

Tom Lane wrote: > Alvaro Herrera writes: > > Tom Lane wrote: > >> It seems quite silly to be asking for a parallel plan and then insisting > >> it not run in parallel. > > > Now that you mention it, this probably decreases coverage for the > > choose_next_subplan_for_worker function. > > Yeah, l

Re: very slow queries when max_parallel_workers_per_gather is higher than zero

Some extra info, which might help, increasing the cost of the parallel_setup_cost to a value of 4500, Postgres doesn't choose the parallel query anymore, making the query faster again. db_sq3rjf5b7p309lq9wuqrh3qhk4gy9fbw=# set parallel_setup_cost = 4500; SET db_sq3rjf5b7p309lq9wuqrh3qhk4gy9fbw=# e

Re: very slow queries when max_parallel_workers_per_gather is higher than zero

Thanks. So we now have a trivial query demonstrating the issue. IMHO this is not really a costing issue, but due to a misestimate. Essentially, the problem is that the two sides of the join mismatch, causing this: -> Bitmap Heap Scan on dwh_dm ... d (... rows=7 width=4) (...) -> Bitma

Re: ON CONFLICT DO UPDATE for partitioned tables

Alvaro Herrera writes: > Pushed now, thanks. Buildfarm doesn't like this even a little bit. regards, tom lane

Re: ON CONFLICT DO UPDATE for partitioned tables

Amit Langote wrote: > The solution I came up with is to call map_variable_attnos() directly, > instead of going through map_partition_varattnos() every time, after first > creating the attribute map ourselves. Yeah, sounds good. I added a tweak: if the tupledescs are equal, there should be no ne

Re: Proposal: Adding json logging

> In CSV a line break inside a field is easy to process for a parser, because (per https://tools.ietf.org/html/rfc4180): >"Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes" Interesting, does that implicitly mean the whole log event would get trans

Re: Proposal: Adding json logging

David Arnold wrote: > Not claiming this assumption does imply parsing of a *rolling* set > of log lines with *previously unkown cardinality*. That's expensive > on computing resources. I don't have actual numbers, but it doesn't > seem too far fetched, neither. > I filed a question to the

Re: very slow queries when max_parallel_workers_per_gather is higher than zero

Hope it's fine to jump in. db_sq3rjf5b7p309lq9wuqrh3qhk4gy9fbw=# set max_parallel_workers_per_gather=0; SET db_sq3rjf5b7p309lq9wuqrh3qhk4gy9fbw=# explain analyze SELECT count(*) FROM f_ticketupdate_aad5jtwal0ayaax AS f INNER JOIN dwh_dm_aabv5kk9rxac4lz_aaonw7nchsan2n1_aad8xhr0m_aaewg8j61iagl1z

Re: Overcoming SELECT ... FOR UPDATE permission restrictions

13.04.2018 18:55, Tom Lane wrote: Although this is arguably a security bug, I'm not sure we should back-patch it. The consequences seem relatively minor, and the behavioral change carries a significant risk of breaking applications that worked as-intended up to now. Thoughts? The worst scenari

Re: Fwd: [BUGS] pg_trgm word_similarity inconsistencies or bug

Hi everyone, When translating doc updates, Alexander Lakhin noticed that trigram examples were not quite accurate. A small patch fixing this issue is attached. On 03/21/2018 03:35 PM, Teodor Sigaev wrote: Thank you, pushed David Steele wrote: On 3/6/18 7:04 AM, Teodor Sigaev wrote: I agre

Re: [patch] pg_attribute.attndims turns to 0 when 'create table like/as'

Judging from the discussion in [1], attndims is deprecated. Good to know, thanks. Could you describe what you are trying to achieve with it? Mostly conformance with the documentation. Might be easier to fix the docs though.

Re: [patch] pg_attribute.attndims turns to 0 when 'create table like/as'

On 04/16/2018 05:01 PM, Alexey Bashtanov wrote: As reported in [1], pg_attribute.attndims turns to 0 when 'create table like/as'. The patch attached is to fix it. Hi Alexey, Judging from the discussion in [1], attndims is deprecated. Could you describe what you are trying to achieve with it

Re: Fix slot's xmin advancement and subxact's lost snapshots in decoding.

(delicate ping)

Re: Proposal: Adding json logging

*Hi all,* This discussion has made big steps forward. It is very encouraging to see this amount of interest. It seems that this has been around at the back of many minds for some time already... Thanks to Chrisophe friendly reminder, I aim to try to define the problem space as concise as possible

Re: Proposal: Adding json logging

On Mon, Apr 16, 2018 at 10:06:29AM -0400, Andrew Dunstan wrote: > On 04/15/2018 05:05 PM, Christophe Pettus wrote: > >> On Apr 15, 2018, at 12:16, David Arnold wrote: > >> > >> Core-Problem: "Multi line logs are unnecessarily inconvenient to parse and > >> are not compatible with the design of so

Re: Deadlock in multiple CIC.

I wrote: > As an investigative measure, I propose that we insert > Assert(MyPgXact->xmin == InvalidTransactionId); > into 9.4's DefineIndex, just after its InvalidateCatalogSnapshot call. Well, isn't this interesting: TRAP: FailedAssertion("!(MyPgXact->xmin == ((TransactionId) 0))", File:

Re: very slow queries when max_parallel_workers_per_gather is higher than zero

Apologies, the reduced query was missing a where condition on id_week: SELECT count(*) FROM f_ticketupdate_aad5jtwal0ayaax AS f INNER JOIN dwh_dm_aabv5kk9rxac4lz_aaonw7nchsan2n1_aad8xhr0m_aaewg8j61iagl1z AS d ON (f.dt_event_id = d.id) WHERE ( 6171 = d."id_euweek" ) regards -- Tomas Vondra

[patch] pg_attribute.attndims turns to 0 when 'create table like/as'

As reported in [1], pg_attribute.attndims turns to 0 when 'create table like/as'. The patch attached is to fix it. Best Regards,   Alexey [1] https://www.postgresql.org/message-id/20150707072942.1186.98151%40wrigleys.postgresql.org diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes

Re: Proposal: Adding json logging

On 04/15/2018 05:05 PM, Christophe Pettus wrote: >> On Apr 15, 2018, at 12:16, David Arnold wrote: >> >> Core-Problem: "Multi line logs are unnecessarily inconvenient to parse and >> are not compatible with the design of some (commonly used) logging >> aggregation flows." > I'd argue that the

Re: very slow queries when max_parallel_workers_per_gather is higher than zero

2018-04-16 15:52 GMT+02:00 Tomas Vondra : > > Query Performs nicely, but no parallel workers are used: > > GroupAggregate (cost=2611148.87..2611152.89 rows=31 width=22) (actual > > time=0.084..0.084 rows=0 loops=1) > >Group Key: > > f_zendesktickets_aaeljtllr5at3el.cstm_custom_38746665_primar

Re: very slow queries when max_parallel_workers_per_gather is higher than zero

> Query Performs nicely, but no parallel workers are used: > GroupAggregate  (cost=2611148.87..2611152.89 rows=31 width=22) (actual > time=0.084..0.084 rows=0 loops=1) >    Group Key: > f_zendesktickets_aaeljtllr5at3el.cstm_custom_38746665_primary_column >    ->  Sort  (cost=2611148.87..2611149.11

Re: very slow queries when max_parallel_workers_per_gather is higher than zero

2018-04-16 14:00 GMT+02:00 Tomas Vondra : > > > On 04/16/2018 11:34 AM, Pavel Stehule wrote: > > Hi, > > > > my customer does performance checks of PostgreSQL 9.5 and 10. Almost all > > queries on 10 are faster, but there are few queries (40 from 1000) where > > PostgreSQL 9.5 is significantly fas

Re: Postgres 10 problem with UNION ALL of null value in "subselect"

On Mon, Apr 16, 2018 at 4:10 PM, Martin Swiech wrote: > Hi folks, > > I got some complex query which works on PostgreSQL 9.6 , but fails on > PostgreSQL 10. > > Version of PostgreSQL: > PostgreSQL 10.3 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version > 7.0.0 (clang-700.1.76), 64-bit >

Re: Prefix operator for text and spgist support

On Mon, 16 Apr 2018 12:45:23 +0200 Emre Hasegeli wrote: > > Thank you, pushed with some editorization and renaming > > text_startswith to starts_with > > I am sorry for not noticing this before, but what is the point of this > operator? It seems to me we are only making the prefix searching >

Re: SHOW ALL does not honor pg_read_all_settings membership

On Thu, 2018-03-01 at 16:22 +0100, I wrote: > I noticed that commit 25fff40798fc4ac11a241bfd9ab0c45c085e2212 forgot > to teach SHOW ALL to show all GUCs when the user belongs to > pg_read_all_settings. > > Patch attached; I think this should be backpatched. Now that the dust from the last commit

Re: very slow queries when max_parallel_workers_per_gather is higher than zero

On 04/16/2018 11:34 AM, Pavel Stehule wrote: > Hi, > > my customer does performance checks of PostgreSQL 9.5 and 10. Almost all > queries on 10 are faster, but there are few queries (40 from 1000) where > PostgreSQL 9.5 is significantly faster than. Unfortunately - pretty fast > queries (about 2

Problem while updating a foreign table pointing to a partitioned table on foreign server

Hi, Consider this scenario postgres=# CREATE TABLE plt (a int, b int) PARTITION BY LIST(a); postgres=# CREATE TABLE plt_p1 PARTITION OF plt FOR VALUES IN (1); postgres=# CREATE TABLE plt_p2 PARTITION OF plt FOR VALUES IN (2); postgres=# INSERT INTO plt VALUES (1, 1), (2, 2); postgres=# CREATE FORE

Oddity in tuple routing for foreign partitions

Hi, While updating the fix-postgres_fdw-WCO-handling patch, I noticed that the patch for tuple routing for foreign partitions doesn't work well with remote triggers. Here is an example: postgres=# create table loct1 (a int check (a in (1)), b text); postgres=# create function br_insert_trigfunc(

Re: Postgres stucks in deadlock detection

On 14.04.2018 10:09, Юрий Соколов wrote: пт, 13 апр. 2018 г., 21:10 Andres Freund >: Hi, On 2018-04-13 19:13:07 +0300, Konstantin Knizhnik wrote: > On 13.04.2018 18:41, Andres Freund wrote: > > On 2018-04-13 16:43:09 +0300, Konstantin Knizhnik wrote:

Re: Prefix operator for text and spgist support

> Thank you, pushed with some editorization and renaming text_startswith to > starts_with I am sorry for not noticing this before, but what is the point of this operator? It seems to me we are only making the prefix searching business, which is already complicated, more complicated. Also, the ne

Postgres 10 problem with UNION ALL of null value in "subselect"

Hi folks, I got some complex query which works on PostgreSQL 9.6 , but fails on PostgreSQL 10. Version of PostgreSQL: PostgreSQL 10.3 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit Simplified core of the problematic query looks like this: ``` select *

very slow queries when max_parallel_workers_per_gather is higher than zero

Hi, my customer does performance checks of PostgreSQL 9.5 and 10. Almost all queries on 10 are faster, but there are few queries (40 from 1000) where PostgreSQL 9.5 is significantly faster than. Unfortunately - pretty fast queries (about 20ms) are too slow now (5 sec). attached execution plans I

Re: submake-errcodes

Hi, On Mon, 2018-04-16 at 09:24 +0200, Christoph Berg wrote: > Fwiw, setting MAKELEVEL=0 worked. Thanks! Great, it solved my problem as well! Thanks Tom. Regards, -- Devrim Gündüz EnterpriseDB: https://www.enterprisedb.com PostgreSQL Consultant, Red Hat Certified Engineer Twitter: @DevrimGundu

Re: ON CONFLICT DO UPDATE for partitioned tables

On 2018/04/10 11:56, Amit Langote wrote: > On 2018/03/27 13:27, Amit Langote wrote: >> On 2018/03/26 23:20, Alvaro Herrera wrote: >>> The one thing I wasn't terribly in love with is the four calls to >>> map_partition_varattnos(), creating the attribute map four times ... but >>> we already have it

Re: [HACKERS] [PATCH] Lockable views

On Thu, 05 Apr 2018 07:53:42 +0900 (JST) Tatsuo Ishii wrote: I update the patch to fix the lockable view issues. > >> > > +typedef struct > >> > > +{ > >> > > + Oid root_reloid; > >> > > + LOCKMODE lockmode; > >> > > + bool nowait; > >> > > + Oid viewowner; > >> > > + Oi

Re: auto_explain and parallel queries issue

2018-04-16 10:33 GMT+02:00 Tomas Vondra : > > > On 04/16/2018 09:25 AM, Pavel Stehule wrote: > > Hi > > > > I am testing PostgreSQL in customer's environment and I found some > > unexpected behave. > > > > When auto_explain is used, and there is slower parallel query, then > > auto_explain raises

Re: auto_explain and parallel queries issue

On 04/16/2018 09:25 AM, Pavel Stehule wrote: > Hi > > I am testing PostgreSQL in customer's environment and I found some > unexpected behave. > > When auto_explain is used, and there is slower parallel query, then > auto_explain raises log for any slow process. > FWIW this is the same issue t

Re: Documentation for bootstrap data conversion

On 4/9/18, Tom Lane wrote: >> 3. It seems the preferred style is to refer to "bootstrap" relations >> rather than "bootstrapped" relations. The attached patch makes code >> comments more like the docs in this regard. > > Meh, I think either is fine really. I do recall changing something > in bki.

Re: partitioning code reorganization

On 2018/04/15 9:17, Alvaro Herrera wrote: > Amit Langote wrote: >> On Sat, Apr 14, 2018 at 11:48 PM, Amit Langote >> wrote: >>> Hi. >>> >>> Thanks for taking care of few things I left like those PartitionKey >>> accessors in rel.h. >> >> Forgot to mention -- there are some files that still includ

auto_explain and parallel queries issue

Hi I am testing PostgreSQL in customer's environment and I found some unexpected behave. When auto_explain is used, and there is slower parallel query, then auto_explain raises log for any slow process. arallel worker for PID 20089 : auto_explain database=NULL client=local appname=psql arall

Re: submake-errcodes

Re: To Tom Lane 2018-04-12 <20180412202717.ga32...@msg.df7cb.de> > > ... or then again, maybe I do. Is it possible that your build > > recipe involves invoking our makefiles from an outer "make" run? > > If so, maybe you need to explicitly set MAKELEVEL=0 when invoking > > our build, to keep it fr

Re: Boolean partitions syntax

Sorry for a silly typo. At Mon, 16 Apr 2018 16:17:40 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20180416.161740.51264437.horiguchi.kyot...@lab.ntt.co.jp> > Hello. Thank you for the comment. > > the attached v6 patch differs only in gram.y since v5. > > At Fri, 13 Apr 2018 18:55:

Re: Boolean partitions syntax

Hello. Thank you for the comment. the attached v6 patch differs only in gram.y since v5. At Fri, 13 Apr 2018 18:55:30 +0900, Amit Langote wrote in > Horiguchi-san, > > Thanks for the latest patch. > > On 2018/04/12 13:12, Kyotaro HORIGUCHI wrote: > > Thank you for verification and the revis