Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0

On 2018-Dec-06, Amit Langote wrote: > The partitionwise join related > changes in PG 11 moved the add_child_rel_equivalences call in > set_append_rel_size such that child EC members would be added even before > checking if the child rel is dummy, but for a reason named in the comment > above the

Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0

On 2018-Dec-06, Amit Langote wrote: Hi > [ Parallel SeqScan on precio_126 to precio_998 ] > > > -> Parallel Seq Scan on precio_999 p_874 > > (cost=0.00..27.50 rows=1 width=16) > >Filter: ((fecha >= '1990-05-06 > > 00:00:00'::timestamp

Re: Facility for detecting insecure object naming

On Thu, Aug 30, 2018 at 12:06:09AM -0700, Noah Misch wrote: > On Sat, Aug 11, 2018 at 12:47:05PM -0700, Noah Misch wrote: > > On Wed, Aug 08, 2018 at 09:58:38AM -0400, Tom Lane wrote: > > > When the security team was discussing this issue before, we speculated > > > about ideas like inventing a

Re: zheap: a new storage format for PostgreSQL

čt 6. 12. 2018 v 8:08 odesílatel Amit Kapila napsal: > On Thu, Dec 6, 2018 at 12:30 PM Pavel Stehule > wrote: > > > > čt 6. 12. 2018 v 7:55 odesílatel Mithun Cy > napsal: > >> > >> On Thu, Dec 6, 2018 at 11:13 AM Amit Kapila > wrote: > >> > > >> > On Thu, Dec 6, 2018 at 10:03 AM Pavel Stehule

Re: zheap: a new storage format for PostgreSQL

On Thu, Dec 6, 2018 at 12:30 PM Pavel Stehule wrote: > > čt 6. 12. 2018 v 7:55 odesílatel Mithun Cy > napsal: >> >> On Thu, Dec 6, 2018 at 11:13 AM Amit Kapila wrote: >> > >> > On Thu, Dec 6, 2018 at 10:03 AM Pavel Stehule >> > wrote: >> > > >> > > čt 6. 12. 2018 v 5:02 odesílatel Mithun Cy

Re: zheap: a new storage format for PostgreSQL

čt 6. 12. 2018 v 7:55 odesílatel Mithun Cy napsal: > On Thu, Dec 6, 2018 at 11:13 AM Amit Kapila > wrote: > > > > On Thu, Dec 6, 2018 at 10:03 AM Pavel Stehule > wrote: > > > > > > čt 6. 12. 2018 v 5:02 odesílatel Mithun Cy > napsal: > > >> > > >> COPY command seems to have improved very

Re: minor leaks in pg_dump (PG tarball 10.6)

On Wednesday, December 5, 2018 4:59:18 PM CET Stephen Frost wrote: > This change doesn't seem to make any sense to me..? If anything, seems > like we'd end up overallocating memory *after* this change, where we > don't today (though an analyzer tool might complain because we don't > free the

Re: zheap: a new storage format for PostgreSQL

On Thu, Dec 6, 2018 at 11:13 AM Amit Kapila wrote: > > On Thu, Dec 6, 2018 at 10:03 AM Pavel Stehule wrote: > > > > čt 6. 12. 2018 v 5:02 odesílatel Mithun Cy > > napsal: > >> > >> COPY command seems to have improved very slightly with zheap in both with > >> size of wal and execution time. I

Re: Fixing findDependentObjects()'s dependency on scan order (regressions in DROP diagnostic messages)

On Wed, Dec 5, 2018 at 10:35 PM Andrey Lepikhov wrote: > This solution changes pg_depend relation for solve a problem, which > exists only in regression tests. Very rarely it can be in the > partitioning cases. Or is it not? I don't think it's a matter of how rarely this will happen. We're

Re: Fixing findDependentObjects()'s dependency on scan order (regressions in DROP diagnostic messages)

On 14.11.2018 11:28, Peter Geoghegan wrote: We're already relying on the scan order being in reverse chronological order, so we might as well formalize the dependency. I don't think that it's possible to sort the pg_depend entries as a way of fixing the breakage while avoiding storing this extra

Re: Minor typo

At Tue, 4 Dec 2018 11:37:16 -0500, Stephen Frost wrote in <20181204163716.gr3...@tamriel.snowman.net> > Thanks to everyone for sharing their thoughts here, the goal is simply > to try and have the comments as clear as we can for everyone. > > Please find attached a larger rewording of the

RE: Too many logs are written on Windows (LOG: could not reserve shared memory region (addr=%p) for child %p:)

Hi, I found the reason of the message. My customer uses "F-secure" antivirus software. There are several pages that indicate F-secure causes this message such as [1]. I told my customer to stop F-secure and report to the vendor. Anyway, I think this message is not helpful to administrators

Re: Limitting full join to one match

On 12/5/2018 8:30 PM, John W Higgins wrote: On Wed, Dec 5, 2018 at 4:34 PM Phil Endecott > wrote: Dear Experts, I have a couple of tables that I want to reconcile, finding rows that match and places where rows are missing from one

Re: zheap: a new storage format for PostgreSQL

On Thu, Dec 6, 2018 at 10:03 AM Pavel Stehule wrote: > > čt 6. 12. 2018 v 5:02 odesílatel Mithun Cy > napsal: >> >> COPY command seems to have improved very slightly with zheap in both with >> size of wal and execution time. I also did some tests with insert statement >> where I could see

Re: Use durable_unlink for .ready and .done files for WAL segment removal

On Thu, Dec 06, 2018 at 01:55:46PM +0900, Kyotaro HORIGUCHI wrote: > durable_unlink has two modes of faiure. Failure to unlink and > fsync. If once it fails at the fsync stage, subsequent > durable_unlink calls for the same file always fail to unlink with > ENOENT. If durable_unlink is intended

RE: Timeout parameters

Hi, Fabien. Thank you for your review. And I'm very sorry to have kept you waiting so long. About "socket_timeout" > I'm generally fine with giving more access to low-level parameters to users. > However, I'm not sure I understand the use case you have that needs these > new extensions. If you

Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0

Hi, (Re-sending after adding -hackers, sorry for the noise to those who would receive this twice) On 2018/12/05 6:55, Alvaro Herrera wrote: > I noticed another interesting thing, which is that if I modify the query > to actually reference some partition that I do have (as opposed to the > above,

Re: Use durable_unlink for .ready and .done files for WAL segment removal

Hello. At Wed, 5 Dec 2018 16:11:23 +, "Bossart, Nathan" wrote in > The v4 patch looks good to me! durable_unlnk has two modes of faiure. Failure to unlink and fsync. If once it fails at the fsync stage, subsequent durable_unlink calls for the same file always fail to unlink with ENOENT.

Re: error message when subscription target is a partitioned table

On 2018/12/06 13:19, Michael Paquier wrote: > On Thu, Dec 06, 2018 at 11:34:19AM +0900, Amit Langote wrote: >> Adding to January CF. > > Okay, that looks good to me based on your arguments upthread. Thanks for looking. > A > small-ish comment I have is that you could use a set of if/else if >

Re: Hint and detail punctuation

On Wed, Dec 05, 2018 at 05:22:25PM +0100, Daniel Gustafsson wrote: > While looking at error messages downstream, I noticed a few hints and details > in postgres which aren’t punctuated as per the style guide. The attached > patch > fixes the ones where it seemed reasonable to end with a period.

Re: zheap: a new storage format for PostgreSQL

čt 6. 12. 2018 v 5:02 odesílatel Mithun Cy napsal: > > On Thu, Mar 1, 2018 at 7:39 PM Amit Kapila > wrote: > > I did some testing for performance of COPY command for zheap against heap, > here are my results, > Machine : cthulhu, (is a 8 node numa machine with 500GB of RAM) > server non default

Re: error message when subscription target is a partitioned table

On Thu, Dec 06, 2018 at 11:34:19AM +0900, Amit Langote wrote: > Adding to January CF. Okay, that looks good to me based on your arguments upthread. A small-ish comment I have is that you could use a set of if/else if conditions instead of separate ifs. -- Michael signature.asc Description: PGP

Re: zheap: a new storage format for PostgreSQL

> On Thu, Mar 1, 2018 at 7:39 PM Amit Kapila wrote: I did some testing for performance of COPY command for zheap against heap, here are my results, Machine : cthulhu, (is a 8 node numa machine with 500GB of RAM) server non default settings: shared buffers 32GB, max_wal_size = 20GB, min_wal_size

Re: on or true

>> I found a few more >> places where true/false is used other than >> ssl_passphrase_command_supports_reload in config.sgml. >> Attached is a patch to fix them in config.sgml. > > I will commit this to the master branch if there's no objection. > Since this is an enhancement, not a bug fix, I

Re: \gexec \watch

On Wed, Dec 05, 2018 at 07:50:23PM -0300, Alvaro Herrera wrote: > I just noticed that using \watch after \gexec does not do what I would > like it to do, namely re-execute the returned queries. Instead, it > executes the returned queries once, then it just returns the queries. > That is: > > =#

Re: error message when subscription target is a partitioned table

On 2018/12/06 11:28, Amit Langote wrote: > On 2018/12/05 10:28, Amit Langote wrote: >> On 2018/12/05 10:20, Michael Paquier wrote: >>> On Tue, Dec 04, 2018 at 09:25:09AM +0100, Magnus Hagander wrote: I think more people would directly understand the "is not a table" for a foreign table

Re: slight tweaks to documentation about runtime pruning

On 2018/12/05 16:23, Amit Langote wrote: > Hi, > > Documentation of run-time pruning tells readers to inspect "nloops" > property of the EXPLAIN ANALYZE output, but I think that's a typo of > "loops" which is actually output ("internal variable to track that > property is indeed nloops). > >

Re: error message when subscription target is a partitioned table

On 2018/12/05 10:28, Amit Langote wrote: > On 2018/12/05 10:20, Michael Paquier wrote: >> On Tue, Dec 04, 2018 at 09:25:09AM +0100, Magnus Hagander wrote: >>> I think more people would directly understand the "is not a table" for a >>> foreign table than a partitioned one (for example, it does now

Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0

On 2018/12/06 11:14, Amit Langote wrote: > I ran the original unmodified query at [1] (the one that produces an empty > plan due to all children being pruned) against the server built with > patches I posted on the "speeding up planning with partitions" [2] thread > and it finished in a jiffy.

Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0

Hi, On 2018/12/05 6:55, Alvaro Herrera wrote: > On 2018-Dec-04, Alvaro Herrera wrote: > >> CREATE TABLE precio(fecha timestamp, pluid int, loccd int, plusalesprice >> int) PARTITION BY RANGE (fecha); >> SELECT format('CREATE TABLE public.precio_%s PARTITION OF public.precio >> (PRIMARY KEY

Re: [PATCH] Opclass parameters

Attached 3rd version of the patches. On 20.11.2018 14:15, Nikolay Shaplov wrote: В письме от 15 ноября 2018 18:26:43 пользователь Nikita Glukhov написал: Attached 2nd version of the patches. Nothing has changed since March, this is just a rebased version. CREATE INDEX syntax and parameters

Re: Connection slots reserved for replication

On 05/12/2018 15:33, Oleksii Kliukin wrote: > >> On 30. Nov 2018, at 13:58, Alexander Kukushkin wrote: >> >> attaching the new version of the patch. >> Now it simply reserves max_wal_senders slots in the ProcGlobal, what >> guarantees that only walsender process could use them. > > With this

Wrong parent node for WCO expressions in nodeModifyTable.c?

Hi Stephen, All, While working on the pluggable storage patchset I noticed that it initializes the WCO expression like: /* * Initialize any WITH CHECK OPTION constraints if needed. */ resultRelInfo = mtstate->resultRelInfo; i = 0; foreach(l, node->withCheckOptionLists)

\gexec \watch

I just noticed that using \watch after \gexec does not do what I would like it to do, namely re-execute the returned queries. Instead, it executes the returned queries once, then it just returns the queries. That is: =# select 'select now()' \gexec \watch 2018-12-05 19:46:04.928995-03 select

Re: slow queries over information schema.tables

On 2018-12-05 13:41:32 -0500, Tom Lane wrote: > The bigger picture here is that people seem to like to use domains > as simple type aliases, which will never have any constraints, but > we're very dumb about that today. So the patch as presented seems > like it has lots of potential

Re: proposal: plpgsql pragma statement

st 5. 12. 2018 v 19:12 odesílatel Dmitry Igrishin napsal: > вт, 4 дек. 2018 г. в 20:13, Pavel Stehule : > > > > Hi > > > > I wrote plpgsql_check https://github.com/okbob/plpgsql_check. > > > > It is working well, but because it does static analyse only, sometimes > it can produces false alarms

Re: slow queries over information schema.tables

Hi, On 2018-12-05 13:22:23 -0500, Tom Lane wrote: > Andres Freund writes: > > On 2018-12-05 12:24:54 -0500, Tom Lane wrote: > >> There are two different issues in that. One is that the domain might > >> have constraints (though in reality it does not), so the planner can't > >> throw away the

Re: slow queries over information schema.tables

Andres Freund writes: > On 2018-12-05 12:24:54 -0500, Tom Lane wrote: >> There are two different issues in that. One is that the domain might >> have constraints (though in reality it does not), so the planner can't >> throw away the CoerceToDomain node, and thus can't match the expression >> to

Re: proposal: plpgsql pragma statement

вт, 4 дек. 2018 г. в 20:13, Pavel Stehule : > > Hi > > I wrote plpgsql_check https://github.com/okbob/plpgsql_check. > > It is working well, but because it does static analyse only, sometimes it can > produces false alarms or it should to stop a analyse, because there are not > necessary data. >

Re: slow queries over information schema.tables

Hi, On 2018-12-05 12:24:54 -0500, Tom Lane wrote: > The core of the problem, I think, is that we're unable to convert the > condition on table_name into an indexscan on pg_class.relname, because > the view has cast pg_class.relname to the sql_identifier domain. > > There are two different issues

Re: psql display of foreign keys

On 2018-Dec-04, Alvaro Herrera wrote: > On 2018-Dec-04, Alvaro Herrera wrote: > > > v2 attached. > > Oops. One more oops: The version I posted was for pg11, and does not apply to master. This version applies to master. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL

Re: proposal: plpgsql pragma statement

Hi st 5. 12. 2018 v 18:28 odesílatel Jonah H. Harris napsal: > You can alter the lexer and create a comment node, right? That’s how we > did hints in EnterpriseDB. > I don't think so it is adequate solution - sure, it is, if I would to implement Oracle's hints. But it is not my target. I

Re: minor leaks in pg_dump (PG tarball 10.6)

Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Pavel Raiskup (prais...@redhat.com) wrote: > >> - attrdefs = (AttrDefInfo *) pg_malloc(numDefaults * > >> sizeof(AttrDefInfo)); > >> ... > >> + attrdefs = (AttrDefInfo *) >

Re: proposal: plpgsql pragma statement

You can alter the lexer and create a comment node, right? That’s how we did hints in EnterpriseDB. On Wed, Dec 5, 2018 at 11:41 AM Pavel Stehule wrote: > > > st 5. 12. 2018 v 15:03 odesílatel Pavel Luzanov > napsal: > >> >> But maybe your extension could read the PERFORM statement preceding it

Re: slow queries over information schema.tables

Pavel Stehule writes: > Slow query > select * from information_schema.tables where table_name = 'pg_class'; Yeah. This has been complained of many times before. The core of the problem, I think, is that we're unable to convert the condition on table_name into an indexscan on pg_class.relname,

Re: proposal: plpgsql pragma statement

st 5. 12. 2018 v 15:03 odesílatel Pavel Luzanov napsal: > > But maybe your extension could read the PERFORM statement preceding it and > treat it as an annotation hint for the following statement. > > > In this case, comment line in some format will be better than real PERFORM > statement. Like

Re: don't mark indexes invalid unnecessarily

Pushed, thanks for the review. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: psql display of foreign keys

I added this patch to commitfest in order to get more opinions, particularly on whether to backpatch this. I might commit sooner than that if others care to comment. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training &

Hint and detail punctuation

While looking at error messages downstream, I noticed a few hints and details in postgres which aren’t punctuated as per the style guide. The attached patch fixes the ones where it seemed reasonable to end with a period. cheers ./daniel errhint_punctuation.patch Description: Binary data

Re: minor leaks in pg_dump (PG tarball 10.6)

Stephen Frost writes: > * Pavel Raiskup (prais...@redhat.com) wrote: >> -attrdefs = (AttrDefInfo *) pg_malloc(numDefaults * >> sizeof(AttrDefInfo)); >> ... >> +attrdefs = (AttrDefInfo *) >> pg_malloc(numDefaults * sizeof(AttrDefInfo)); > This

Re: minor leaks in pg_dump (PG tarball 10.6)

Greetings, * Pavel Raiskup (prais...@redhat.com) wrote: > Among other reports (IMO clearly non-issues), I'm sending patch which > fixes/points to a few resource leaks detected by Coverity that might be > worth fixing. If they are not, feel free to ignore this mail. > diff --git

minor leaks in pg_dump (PG tarball 10.6)

Among other reports (IMO clearly non-issues), I'm sending patch which fixes/points to a few resource leaks detected by Coverity that might be worth fixing. If they are not, feel free to ignore this mail. Pavel diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c index

Re: don't mark indexes invalid unnecessarily

On 2018-Dec-05, Amit Langote wrote: > Hi, > > On 2018/12/04 7:50, Alvaro Herrera wrote: > > While working on FKs pointing to partitioned tables, I noticed that in > > PG11 we fail to produce a working dump in the case of a partitioned > > table that doesn't have partitions. > > > > The attached

Re: Connection slots reserved for replication

> On 30. Nov 2018, at 13:58, Alexander Kukushkin wrote: > > attaching the new version of the patch. > Now it simply reserves max_wal_senders slots in the ProcGlobal, what > guarantees that only walsender process could use them. With this patch It looks like InitProcess will trigger the

Re: NOTIFY and pg_notify performance when deduplicating notifications

Is there a particular format that is needed for the benchmark? Here's a quick benchmark below. Unpatched, generating N notifications takes t milliseconds: N= 1, t= 348 N= 2, t=1419 N= 3, t=3253 N= 4, t=6170 Patched, with the 'never' collapse mode, on another (much faster)

Re: proposal: plpgsql pragma statement

st 5. 12. 2018 v 15:03 odesílatel Pavel Luzanov napsal: > > But maybe your extension could read the PERFORM statement preceding it and > treat it as an annotation hint for the following statement. > > > In this case, comment line in some format will be better than real PERFORM > statement. Like

Re: proposal: plpgsql pragma statement

st 5. 12. 2018 v 14:42 odesílatel Alexey Bashtanov napsal: > > >> You can use PERFORM as a workaround: >> >> PERFORM 'PRAGMA', 'cmdtype', 'CREATE'; >> >> There's some overhead when executing, but probably not too much. >> > > Thank you for tip, but I have not any idea, how it can work? > > >

Re: proposal: plpgsql pragma statement

But maybe your extension could read the PERFORM statement preceding it and treat it as an annotation hint for the following statement. In this case, comment line in some format will be better than real PERFORM statement. Like this: /*+PRAGMA cmdtype CREATE; */ EXECUTE format('CREATE TABLE

Re: proposal: plpgsql pragma statement

You can use PERFORM as a workaround: PERFORM 'PRAGMA', 'cmdtype', 'CREATE'; There's some overhead when executing, but probably not too much. Thank you for tip, but I have not any idea, how it can work? Well, I thought you were for a comment-like thing that remains there

Re: proposal: plpgsql pragma statement

Hi st 5. 12. 2018 v 12:42 odesílatel Alexey Bashtanov napsal: > Hello Pavel, > > > > > The PRAGMA statement does nothing in runtime. It works only in compile > > time, and add a pair of key, value to next non pragma statement. This > > information can be used by some plpgsql extensions. > > > >

Re: proposal: plpgsql pragma statement

Hello Pavel, The PRAGMA statement does nothing in runtime. It works only in compile time, and add a pair of key, value to next non pragma statement. This information can be used by some plpgsql extensions. What do you think about this proposal? You can use PERFORM as a workaround:

Re: [PROPOSAL]a new data type 'bytea' for ECPG

Matsumura-san, > Sorry to bother you, but I hope any comment of yours. It is no bother. I'm fine with the patch if it does not work against the standard. I do think, though, we should change the debug output for ecpg_free_params(). The way it is now it prints binary values which we also have

Re: don't mark indexes invalid unnecessarily

Hi, On 2018/12/04 7:50, Alvaro Herrera wrote: > While working on FKs pointing to partitioned tables, I noticed that in > PG11 we fail to produce a working dump in the case of a partitioned > table that doesn't have partitions. > > The attached patch fixes that. The fix makes sense. I see that