Re: speed up unicode normalization quick check

On Sat, May 30, 2020 at 12:13 AM Mark Dilger wrote: > > > I forgot in my first round of code review to mention, "thanks for the patch". > I generally like what you are doing here, and am trying to review it so it > gets committed. And I forgot to say thanks for taking a look! > The reason I g

Re: Inlining of couple of functions in pl_exec.c improves performance

so 30. 5. 2020 v 7:28 odesílatel Amit Khandekar napsal: > On Thu, 28 May 2020 at 14:39, Pavel Stehule > wrote: > > I don't see 17% anywhere, but 3-5% is not bad. > Did you see 3-5% only for the pi function, or did you see the same > improvement also for the functions that I wrote ? I was getting

Re: Inlining of couple of functions in pl_exec.c improves performance

On Thu, 28 May 2020 at 14:39, Pavel Stehule wrote: > I don't see 17% anywhere, but 3-5% is not bad. Did you see 3-5% only for the pi function, or did you see the same improvement also for the functions that I wrote ? I was getting a consistent result of 14-18 % on both of the VMs. Also, is your te

Re: feature idea: use index when checking for NULLs before SET NOT NULL

On Fri, May 29, 2020 at 09:53:14PM -0400, John Bachir wrote: > Hi Sergei - I just used the recipe on my production database. I didn't > observe all the expected benefits, I wonder if there were confounding factors > or if I did something wrong. If you have time, I'd love to get your feedback. > Let

Re: feature idea: use index when checking for NULLs before SET NOT NULL

Hi Sergei - I just used the recipe on my production database. I didn't observe all the expected benefits, I wonder if there were confounding factors or if I did something wrong. If you have time, I'd love to get your feedback. Let me know if you need more info. I'd love to write a blog post info

Re: [PATCH] Fix install-tests target for vpath builds

On 2020-May-29, Andrew Dunstan wrote: > I've come up with a slightly nicer version of your patch 1, which I > propose to commit and backpatch before long. Looks good to me. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training &

Re: [PATCH] Fix install-tests target for vpath builds

On 4/23/20 12:55 AM, Craig Ringer wrote: > Patch 0001 fixes this issue with vpath postgres builds: > > $ make -C src/test/regress install-tests > /usr/bin/install: cannot create regular file > 'PGPREFIX/lib/postgresql/regress/PGPREFIX/src/test/regress/expected/errors.out': > No such file or direct

Re: Read access for pg_monitor to pg_replication_origin_status view

Hi, > While working on some monitoring tasks I realised that the pg_monitor > role doesn't have access to the pg_replication_origin_status. > > Are there any strong thoughts on not giving pg_monitor access to this > view, or is it just something that nobody asked for yet? I can't find > any reason

Re: Trouble with hashagg spill I/O pattern and costing

On Fri, 2020-05-29 at 15:04 +0200, Tomas Vondra wrote: > Ah, right. Yeah, we only need to check for AGG_HASH here. Moreover, > AGG_MIXED probably does not need the tlist tweak, because the input > should be pre-sorted as with AGG_SORTED. > > And we should probably do similar check in the > create_

Re: pie-in-sky idea: 'sensitive' function parameters

Robert Haas writes: > On Fri, May 29, 2020 at 3:26 PM Tom Lane wrote: >> One missing part of that is that we'd need to support bind parameters >> for utility statements, eg new password in ALTER USER. That's been >> on the wish list for a long time anyway, of course. I think it's >> mostly a ma

Re: Default gucs for EXPLAIN

On Tue, May 26, 2020 at 7:30 AM David Rowley wrote: > I'm against adding GUCs to control what EXPLAIN does by default. > > A few current GUCs come to mind which gives external control to a > command's behaviour are: > > standard_conforming_strings > backslash_quote > bytea_output > > It's pretty d

Re: pie-in-sky idea: 'sensitive' function parameters

On 05/29/20 15:26, Tom Lane wrote: > all of the simpler cases I can think of: aside from the ALTER USER > PASSWORD case, there's INSERT INTO accounts(..., creditcardnumber, > ...) VALUES(..., $n, ...). Neither one of those have a nearby UDF > to control it with. I was thinking incrementally ...

Re: pie-in-sky idea: 'sensitive' function parameters

On Fri, May 29, 2020 at 3:26 PM Tom Lane wrote: > One missing part of that is that we'd need to support bind parameters > for utility statements, eg new password in ALTER USER. That's been > on the wish list for a long time anyway, of course. I think it's > mostly a matter of lack of round tuits

Re: pie-in-sky idea: 'sensitive' function parameters

On Fri, May 29, 2020 at 3:05 PM Chapman Flack wrote: > A possible step in the direction of good-enough would be to have > 'sensitive' flags only in the parameter-bind message of the extended > protocol. Interesting idea. Changing the wire protocol for this sort of thing makes it a much bigger lif

Re: pie-in-sky idea: 'sensitive' function parameters

Chapman Flack writes: > A possible step in the direction of good-enough would be to have > 'sensitive' flags only in the parameter-bind message of the extended > protocol. Yeah, if we restrict our promises to being willing to not log (some) bind parameters, that seems far more manageable, and rel

Re: Internal key management system

On Fri, May 29, 2020 at 1:50 AM Masahiko Sawada wrote: > However, this usage has a downside that user secret can be logged to > server logs when log_statement = 'all' or an error happens. To deal > with this issue I've created a PoC patch on top of the key manager > patch which adds a libpq functi

Re: pie-in-sky idea: 'sensitive' function parameters

On 05/29/20 14:51, Robert Haas wrote: > stuff that works today. We have options to log queries before parsing > them. You can't redact sensitive details at that point because you > don't know whether the query contains any such details, or where they > are located. You can't postpone logging the qu

Re: pie-in-sky idea: 'sensitive' function parameters

On Fri, May 29, 2020 at 12:38 PM Chapman Flack wrote: > Just giving this thread a bump in honor of the mention of sensitive > things in logs in the cryptography unconference session. I agree that there's a problem here and I would like to see a solution to it, too. Several EnterpriseDB customers

Re: proposal: possibility to read dumped table's name from file

On Fri, May 29, 2020 at 04:21:00PM +0200, Pavel Stehule wrote: > one my customer has to specify dumped tables name by name. After years and > increasing database size and table numbers he has problem with too short > command line. He need to read the list of tables from file (or from stdin). +1 -

Re: Expand the use of check_canonical_path() for more GUCs

On 2020-May-28, Mark Dilger wrote: > A little poking around shows that in SelectConfigFiles(), these four > directories were set by SetConfigOption(). I don't see a problem with > the code, but the way this stuff is spread around makes it easy for > somebody adding a new GUC file path to do it wr

Re: pg_dump fail to not dump public schema orders

On Fri, May 29, 2020 at 7:42 AM Adrien Nayrat wrote: > On 5/29/20 3:56 PM, David G. Johnston wrote: > > On Friday, May 29, 2020, Adrien Nayrat > > wrote: > > > > Hello, > > > > I noticed pg_dump failed to not dump creation or comment commands > for publ

Re: Expand the use of check_canonical_path() for more GUCs

On Tue, May 19, 2020 at 7:02 AM Peter Eisentraut wrote: > That thread didn't resolve why check_canonical_path() is necessary > there. Maybe the existing uses could be removed? This first sentence of this reply seems worthy of particualr attention. We have to know what problem this is intended to

Re: pie-in-sky idea: 'sensitive' function parameters

Just giving this thread a bump in honor of the mention of sensitive things in logs in the cryptography unconference session. I'm not partisan about any of the particular syntax examples I gave earlier, but it seems like there were two key ingredients: 1. In what is sent from the client to the se

Re: proposal: possibility to read dumped table's name from file

pá 29. 5. 2020 v 18:03 odesílatel Tom Lane napsal: > David Fetter writes: > > Would it make sense to expand this patch to handle other objects? > Sure. Just we should to design system (and names of options). > > If we're gonna do something like this, +1 for being more general. > The fact that

Re: speed up unicode normalization quick check

> On May 28, 2020, at 8:54 PM, John Naylor wrote: > > On Fri, May 29, 2020 at 5:59 AM Mark Dilger > wrote: >> >>> On May 21, 2020, at 12:12 AM, John Naylor >>> wrote: > >>> very picky in general. As a test, it also successfully finds a >>> function for the OS "words" file, the "D" sets of

Re: proposal: possibility to read dumped table's name from file

David Fetter writes: > Would it make sense to expand this patch to handle other objects? If we're gonna do something like this, +1 for being more general. The fact that pg_dump only has selection switches for tables and schemas has always struck me as an omission. regards

Re: PG function with pseudotype "anyelement" for IN, OUT parameter shows wrong behaviour.

On Fri, May 29, 2020 at 8:30 PM Pavel Stehule wrote: > > > pá 29. 5. 2020 v 16:45 odesílatel Prabhat Sahu < > prabhat.s...@enterprisedb.com> napsal: > >> Hi All, >> >> Please check the below scenario, with pseudotype "anyelement" for IN, OUT >> parameter and the RETURN record in a function. >> >>

Re: proposal: possibility to read dumped table's name from file

On Fri, May 29, 2020 at 04:21:00PM +0200, Pavel Stehule wrote: > Hi > > one my customer has to specify dumped tables name by name. After years and > increasing database size and table numbers he has problem with too short > command line. He need to read the list of tables from file (or from stdin)

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Tue, May 26, 2020 at 4:46 PM Amit Kapila wrote: > > On Tue, May 26, 2020 at 2:44 PM Dilip Kumar wrote: > > > > On Tue, May 26, 2020 at 10:27 AM Amit Kapila > > wrote: > > > > > > > > > > > 2. There is a bug fix in handling the stream abort in 0008 (earlier it > > > > was 0006). > > > > > > >

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Tue, May 26, 2020 at 3:04 PM Amit Kapila wrote: > > On Mon, May 25, 2020 at 8:07 PM Dilip Kumar wrote: > > > > On Fri, May 22, 2020 at 11:54 AM Amit Kapila > > wrote: > > > > > > 4. > > > + * XXX Do we need to allocate it in TopMemoryContext? > > > + */ > > > +static void > > > +subxact_info

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Thu, May 28, 2020 at 2:41 PM Amit Kapila wrote: > > On Thu, May 28, 2020 at 12:46 PM Dilip Kumar wrote: > > > > On Tue, May 26, 2020 at 12:00 PM Amit Kapila > > wrote: > > > > > > Isn't this problem only for subxact file as we anyway create changes > > > file as part of start stream message

Re: PG function with pseudotype "anyelement" for IN, OUT parameter shows wrong behaviour.

pá 29. 5. 2020 v 16:45 odesílatel Prabhat Sahu < prabhat.s...@enterprisedb.com> napsal: > Hi All, > > Please check the below scenario, with pseudotype "anyelement" for IN, OUT > parameter and the RETURN record in a function. > > postgres=# create table tab1(c1 int, c2 int, c3 timestamp) ; > CREATE

Re: proposal: possibility to read dumped table's name from file

pá 29. 5. 2020 v 16:28 odesílatel Tom Lane napsal: > Pavel Stehule writes: > > one my customer has to specify dumped tables name by name. After years > and > > increasing database size and table numbers he has problem with too short > > command line. He need to read the list of tables from file

PG function with pseudotype "anyelement" for IN, OUT parameter shows wrong behaviour.

Hi All, Please check the below scenario, with pseudotype "anyelement" for IN, OUT parameter and the RETURN record in a function. postgres=# create table tab1(c1 int, c2 int, c3 timestamp) ; CREATE TABLE postgres=# CREATE OR REPLACE FUNCTION func_any(IN anyelement, IN anyelement, OUT v1 anyelement

Re: pg_dump fail to not dump public schema orders

On 5/29/20 3:56 PM, David G. Johnston wrote: > On Friday, May 29, 2020, Adrien Nayrat > wrote: > > Hello, > > I noticed pg_dump failed to not dump creation or comment commands for > public > schema when we explicitly ask it to dump public schema. >

Re: pg_dump fail to not dump public schema orders

"David G. Johnston" writes: > On Friday, May 29, 2020, Adrien Nayrat wrote: >> I noticed pg_dump failed to not dump creation or comment commands for >> public schema when we explicitly ask it to dump public schema. > As far as I can tell this is working as intended/documented. The public > sche

Re: proposal: possibility to read dumped table's name from file

Pavel Stehule writes: > one my customer has to specify dumped tables name by name. After years and > increasing database size and table numbers he has problem with too short > command line. He need to read the list of tables from file (or from stdin). I guess the question is why. That seems like

proposal: possibility to read dumped table's name from file

Hi one my customer has to specify dumped tables name by name. After years and increasing database size and table numbers he has problem with too short command line. He need to read the list of tables from file (or from stdin). I wrote simple PoC patch Comments, notes, ideas? Regards Pavel diff

Re: Make the qual cost on index Filter slightly higher than qual cost on index Cond.

> The other serious error we could be making here is to change things on >> the basis of just a few examples. You really need a pretty wide range >> of test cases to be sure that you're not making things worse, any time >> you're twiddling basic parameters like these. >> >> > I will try more thing

Re: Make the qual cost on index Filter slightly higher than qual cost on index Cond.

On Fri, May 29, 2020 at 9:37 PM Ashutosh Bapat wrote: > On Fri, May 29, 2020 at 6:40 AM Andy Fan wrote: > > > > > >> > >> >so we need to optimize the cost model for such case, the method is the > >> >patch I mentioned above. > >> > >> Making the planner more robust w.r.t. to estimation errors is

Re: pg_dump fail to not dump public schema orders

On Friday, May 29, 2020, Adrien Nayrat wrote: > Hello, > > I noticed pg_dump failed to not dump creation or comment commands for > public > schema when we explicitly ask it to dump public schema. > > Shorter example: pg_dump -n public dump will give: > [Create schema public] > As far as I

Re: Speeding up parts of the planner using a binary search tree structure for nodes

On Fri, May 29, 2020 at 10:47 AM David Rowley wrote: > > Hi, > > In [1] I mentioned that I'd like to look into coding a data structure > to allow Node types to be looked up more efficiently than what List > allows. There are many places in the planner where we must determine > if a given Node is

Re: Make the qual cost on index Filter slightly higher than qual cost on index Cond.

On Fri, May 29, 2020 at 6:40 AM Andy Fan wrote: > > >> >> >so we need to optimize the cost model for such case, the method is the >> >patch I mentioned above. >> >> Making the planner more robust w.r.t. to estimation errors is nice, but >> I wouldn't go as far saying we should optimize for such ca

Re: password_encryption default

Stephen Frost writes: > * Jonathan S. Katz (jk...@postgresql.org) wrote: >> By that logic, I would +1 removing ENCRYPTED & UNENCRYPTED, given >> ENCRYPTED effectively has no meaning either after all this time too. >> Perhaps a stepping stone is to emit a deprecation warning on PG14 and >> remove i

Re: password_encryption default

On 5/29/20 9:22 AM, Stephen Frost wrote: > Greetings, > > * Jonathan S. Katz (jk...@postgresql.org) wrote: >> On 5/29/20 3:33 AM, Michael Paquier wrote: >>> On Thu, May 28, 2020 at 02:53:17PM +0200, Peter Eisentraut wrote: More along these lines: We could also remove the ENCRYPTED and UNENCRY

Re: password_encryption default

Greetings, * Jonathan S. Katz (jk...@postgresql.org) wrote: > On 5/29/20 3:33 AM, Michael Paquier wrote: > > On Thu, May 28, 2020 at 02:53:17PM +0200, Peter Eisentraut wrote: > >> More along these lines: We could also remove the ENCRYPTED and UNENCRYPTED > >> keywords from CREATE and ALTER ROLE.

Re: feature idea: use index when checking for NULLs before SET NOT NULL

Hi > Sergei, a few questions: > > - Just to be clear, your recipe does not require any indexes, right? Because > the constraint check table scan is inherently concurrent? Right. "alter table validate constraint" can not use indexes, but does not block concurrent read/write queries. Other querie

Re: password_encryption default

Greetings, * Michael Paquier (mich...@paquier.xyz) wrote: > On Thu, May 28, 2020 at 02:53:17PM +0200, Peter Eisentraut wrote: > > More along these lines: We could also remove the ENCRYPTED and UNENCRYPTED > > keywords from CREATE and ALTER ROLE. AFAICT, these have never been emitted > > by pg_dum

pg_dump fail to not dump public schema orders

Hello, I noticed pg_dump failed to not dump creation or comment commands for public schema when we explicitly ask it to dump public schema. Shorter example: pg_dump -n public dump will give: -- -- Name: public; Type: SCHEMA; Schema: -; Owner: postgres -- CREATE SCHEMA public; ALTER SCHEMA pub

Re: password_encryption default

On 5/29/20 3:33 AM, Michael Paquier wrote: > On Thu, May 28, 2020 at 02:53:17PM +0200, Peter Eisentraut wrote: >> More along these lines: We could also remove the ENCRYPTED and UNENCRYPTED >> keywords from CREATE and ALTER ROLE. AFAICT, these have never been emitted >> by pg_dump or psql, so there

Re: Trouble with hashagg spill I/O pattern and costing

On Thu, May 28, 2020 at 06:14:55PM -0700, Jeff Davis wrote: On Thu, 2020-05-28 at 20:57 +0200, Tomas Vondra wrote: Attached is a patch adding CP_SMALL_TLIST to create_agg_plan and create_groupingsets_plan. Looks good, except one question: Why would aggstrategy ever be MIXED when in create_agg

Re: feature idea: use index when checking for NULLs before SET NOT NULL

> > > > John, I think it's worth pointing out that Postgres most likely does a > full table scan to validate a constraint by design and not in optimization > oversight. Think of what's gonna happen if the index used for checking is > corrupted? > > This can't be true: a corrupted index is a failur

Re: OpenSSL 3.0.0 compatibility

> On 29 May 2020, at 13:34, Peter Eisentraut > wrote: > > On 2020-05-29 00:16, Daniel Gustafsson wrote: >> Regarding the deprecations, we can either set preprocessor directives or use >> compiler flags to silence the warning and do nothing (for now), or we could >> update to the new API. We pro

Re: feature idea: use index when checking for NULLs before SET NOT NULL

Wow! Thank you Sergei for working on this patch, for working for months/years to get it in, and for replying to my email! For others reading this later: - the feature was introduced in 12 - the commit is here https://github.com/postgres/postgres/commit/bbb96c3704c041d139181c6601e5bc770e045d26 S

Re: OpenSSL 3.0.0 compatibility

On 2020-05-29 00:16, Daniel Gustafsson wrote: Regarding the deprecations, we can either set preprocessor directives or use compiler flags to silence the warning and do nothing (for now), or we could update to the new API. We probably want to different things for master vs back-branches, but as a

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Wed, May 27, 2020 at 5:19 PM Mahendra Singh Thalor wrote: > On Tue, 26 May 2020 at 16:46, Amit Kapila wrote: > > Hi all, > On the top of v16 patch set [1], I did some testing for DDL's and DML's to > test wal size and performance. Below is the testing summary; > > *Test parameters:* > wal_lev

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Wed, May 27, 2020 at 8:22 PM Dilip Kumar wrote: > > > While reviewing/testing I have found a couple of problems in 0005 and > 0006 which I have fixed in the attached version. > .. > > In 0006: If we are streaming the serialized changed and there are > still few incomplete changes, then currentl

Re: SIGSEGV from START_REPLICATION 0/XXXXXXX in XLogSendPhysical () at walsender.c:2762

On Fri, 29 May 2020 at 17:57, Kyotaro Horiguchi wrote: > > At Fri, 29 May 2020 16:21:38 +0900, Michael Paquier > wrote in > > On Thu, May 28, 2020 at 06:11:39PM +0900, Kyotaro Horiguchi wrote: > > > Mmm. It is not the proper way to use physical replication and it's > > > totally accidental that

Re: SIGSEGV from START_REPLICATION 0/XXXXXXX in XLogSendPhysical () at walsender.c:2762

At Fri, 29 May 2020 16:21:38 +0900, Michael Paquier wrote in > On Thu, May 28, 2020 at 06:11:39PM +0900, Kyotaro Horiguchi wrote: > > Mmm. It is not the proper way to use physical replication and it's > > totally accidental that that worked (or even it might be a bug). The > > documentation is s

Does PG server process keep backend info

Hi, Is there any extension or option in PG to keep information of any (memory context/some memory address) of backend process after getting killed in (postgres server process/postmaster process) Thanks Braj

RE: archive status ".ready" files may be created too early

2020-03-26 18:50:24 Bossart, Nathan wrote: > The v3 patch is a proof-of-concept patch that moves the ready-for- > archive logic to the WAL writer process. We mark files as ready-for- > archive when the WAL flush pointer has advanced beyond a known WAL > record boundary. I like such a simple re

Re: password_encryption default

On Thu, May 28, 2020 at 02:53:17PM +0200, Peter Eisentraut wrote: > More along these lines: We could also remove the ENCRYPTED and UNENCRYPTED > keywords from CREATE and ALTER ROLE. AFAICT, these have never been emitted > by pg_dump or psql, so there are no concerns from that end. Thoughts? +0.5

Re: feature idea: use index when checking for NULLs before SET NOT NULL

On Fri, May 29, 2020 at 8:56 AM Sergei Kornilov wrote: > Hello > > Correct index lookup is a difficult task. I tried to implement this > previously... > > But the answer in SO is a bit incomplete for recent postgresql releases. > Seqscan is not the only possible way to set not null in pg12+. My p

Re: race condition when writing pg_control

On 2020/05/27 16:10, Michael Paquier wrote: On Tue, May 26, 2020 at 07:30:54PM +, Bossart, Nathan wrote: While an assertion in UpdateControlFile() would not have helped us catch the problem I initially reported, it does seem worthwhile to add it. I have attached a patch that adds this as

Re: SIGSEGV from START_REPLICATION 0/XXXXXXX in XLogSendPhysical () at walsender.c:2762

On Thu, May 28, 2020 at 06:11:39PM +0900, Kyotaro Horiguchi wrote: > Mmm. It is not the proper way to use physical replication and it's > totally accidental that that worked (or even it might be a bug). The > documentation is saying as the follows, as more-or-less the same for > all versions since

Re: OpenSSL 3.0.0 compatibility

> On 29 May 2020, at 08:06, Laurenz Albe wrote: >> Regarding the deprecations, we can either set preprocessor directives or use >> compiler flags to silence the warning and do nothing (for now), or we could >> update to the new API. We probably want to different things for master vs >> back-bran