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

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:* >

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

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

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

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

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

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?

Re: OpenSSL 3.0.0 compatibility

On Fri, 2020-05-29 at 00:16 +0200, Daniel Gustafsson wrote: > Since OpenSSL is now releasing 3.0.0-alpha versions, I took a look at using it > with postgres to see what awaits us. As it is now shipping in releases (with > GA planned for Q4), users will probably soon start to test against it so I

Re: segmentation fault using currtid and partitioned tables

On Thu, May 28, 2020 at 05:55:59PM -0700, Andres Freund wrote: > And there only for very old servers (< 8.2), according to Hiroshi > Inoue. Found that out post 12 freeze. I was planning to drop them for > 13, but I unfortunately didn't get around to do so :( [... digging ...] Ah, I think I see

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

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 patch was commited ( https://commitfest.postgresql.org/22/1389/ )

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

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

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

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

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

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

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

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) ; >

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

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

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

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

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

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

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

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

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

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

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

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

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

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 >

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 >

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

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

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