Re: [HACKERS] Custom compression methods

On Tue, Mar 09, 2021 at 01:04:10PM +0530, Dilip Kumar wrote: > On Tue, Mar 9, 2021 at 2:45 AM Robert Haas wrote: > > > > On Mon, Mar 8, 2021 at 3:59 PM Justin Pryzby wrote: > > > > It would be nice to have a way to force > > > > anything compressed with the old method to be re-compressed with

Re: shared-memory based stats collector

At Sat, 6 Mar 2021 00:32:07 +0900, Fujii Masao wrote in > > > On 2021/03/05 17:18, Kyotaro Horiguchi wrote: > > At Thu, 21 Jan 2021 12:03:48 +0900 (JST), Kyotaro Horiguchi > > wrote in > >> Commit 960869da08 (database statistics) conflicted with this. Rebased. > >> > >> I'm concerned about

Re: Parallel INSERT (INTO ... SELECT ...)

On Mon, Mar 8, 2021 at 6:25 PM Amit Langote wrote: > > A couple of things that look odd in v24-0001 (sorry if there were like > that from the beginning): > > +static bool > +target_rel_max_parallel_hazard(max_parallel_hazard_context *context) > +{ > + boolmax_hazard_found; > + > +

Re: alter table set TABLE ACCESS METHOD

On Mon, Mar 08, 2021 at 04:30:23PM +0900, Michael Paquier wrote: > This toast issue is a kind of interesting one, and it seems rather > right to rely on toast_build_flattened_tuple() to decompress things if > both table AMs support toast with the internals of toast knowing what > kind of

Re: authtype parameter in libpq

On 04.03.21 16:06, Daniel Gustafsson wrote: authtype is completely dead in terms of reading back the value, to the point of it being a memleak if it indeed was found in as an environment variable. But I tend to think we should remove them both altogether (modulo ABI and API preservation).

Re: Boundary value check in lazy_tid_reaped()

On 21.01.21 14:11, Masahiko Sawada wrote: Agreed. bsearch with bound check showed a reasonable improvement in my evaluation in terms of performance. Regarding memory efficiency, we can experiment with other methods later. I've attached the patch that adds a bound check for encoded itermpointers

Re: Using COPY FREEZE in pgbench

On Mon, 2021-03-08 at 14:39 +0900, Tatsuo Ishii wrote: > Currently pgbench uses plain COPY to populate pgbench_accounts > table. With adding FREEZE option to COPY, the time to perform "pgbench > -i" will be significantly reduced. > > Curent master: > pgbench -i -s 100 > : > : > done in 70.78 s

Re: 011_crash_recovery.pl intermittently fails

At Sun, 07 Mar 2021 20:09:33 -0500, Tom Lane wrote in > Thomas Munro writes: > > Thanks! I'm afraid I wouldn't get around to it for a few weeks, so if > > you have time, please do. (I'm not sure if it's strictly necessary to > > log *this* xid, if a higher xid has already been logged,

RE: should INSERT SELECT use a BulkInsertState?

> > I am very interested in this patch, and I plan to do some experiments with > > the > patch. > > Can you please rebase the patch because it seems can not applied to the > master now. > > Thanks for your interest. > > I was sitting on a rebased version since the bulk FDW patch will cause >

Make stream_prepare an optional callback

Hi Hackers, As part of commit 0aa8a0 , new plugin methods (callbacks) were defined for enabling two_phase commits. 5 callbacks were required: * begin_prepare * prepare * commit_prepared * rollback_prepared *

Re: Using COPY FREEZE in pgbench

Hello Tatsuo-san, Currently pgbench uses plain COPY to populate pgbench_accounts table. With adding FREEZE option to COPY, the time to perform "pgbench -i" will be significantly reduced. Curent master: pgbench -i -s 100 done in 70.78 s (drop tables 0.21 s, create tables 0.02 s, client-side

Re: Parallel INSERT (INTO ... SELECT ...)

On Mon, Mar 8, 2021 at 12:55 PM Amit Langote wrote: > > Hi Amit, Greg, > > Sorry, I hadn't noticed last week that some questions were addressed to me. > > On Sat, Mar 6, 2021 at 7:19 PM Amit Kapila wrote: > > Thanks, your changes look good to me. I went ahead and changed the > > patch to track

RE: pg_upgrade failing for 200+ million Large Objects

Thanks Daniel for the input / next-steps. I see that 'master' too has this same magic constant [1] and so I expect it to have similar restrictions, although I haven't tested this yet. I do agree that the need then is to re-submit a patch that works with 'master'. (I am travelling the next few

Re: Using COPY FREEZE in pgbench

Hi Fabien, > That looks good! > > As COPY FREEZE was introduced in 9.3, this means that loading data > would break with previous versions. Pgbench attempts at being > compatible with older versions. I'm wondering whether we should not > care or if we should attempt some compatibility layer. It

Re: Improvements and additions to COPY progress reporting

On Sun, Mar 07, 2021 at 04:50:31PM +0530, Bharath Rupireddy wrote: > Attaching remaining patches 0001 and 0003 from the v11 patch > set(posted upthread) here to make cfbot happier. Looking at patch 0002, the location of each progress report looks good to me. I have some issues with some of the

Re: [HACKERS] logical decoding of two-phase transactions

On Mon, Mar 8, 2021 at 1:26 PM Peter Smith wrote: > > On Mon, Mar 8, 2021 at 4:19 PM Amit Kapila wrote: > > > > On Mon, Mar 8, 2021 at 10:04 AM Peter Smith wrote: > > > > > > On Sun, Mar 7, 2021 at 3:00 PM Amit Kapila > > > wrote: > > > > > > > > On Sun, Mar 7, 2021 at 7:35 AM Peter Smith >

Re: [HACKERS] logical decoding of two-phase transactions

On Mon, Mar 8, 2021 at 11:30 AM Ajin Cherian wrote: > > On Fri, Mar 5, 2021 at 9:25 PM vignesh C wrote: > > > Created new patch v53: Thanks for the updated patch. I had noticed one issue, publisher does not get stopped normally in the following case: # Publisher steps psql -d postgres -c

Re: pgbench - add pseudo-random permutation function

What are your current thoughts? Thanks for prodding. I still think it's a useful feature. However I don't think I'll have to time to get it done on the current commitfest. I suggest to let it sit in the commitfest to see if somebody else will pick it up -- and if not, we move it to the

Re: pg_upgrade failing for 200+ million Large Objects

Thanks Peter. The original email [1] had some more context that somehow didn't get associated with this recent email. Apologies for any confusion. In short, pg_resetxlog (and pg_resetwal) employs a magic constant [2] (for both v9.6 as well as master) which seems to have been selected to force an

Re: Huge memory consumption on partitioned table with FKs

On Mon, Mar 8, 2021 at 3:43 PM Andy Fan wrote: > > > On Fri, Mar 5, 2021 at 5:00 AM Tom Lane wrote: > >> Amit Langote writes: >> > Updated patch attached. >> >> This claim seems false on its face: >> >> > All child constraints of a given foreign key constraint can use the >> > same RI query

Re: Using COPY FREEZE in pgbench

> Hi Fabien, > >> That looks good! >> >> As COPY FREEZE was introduced in 9.3, this means that loading data >> would break with previous versions. Pgbench attempts at being >> compatible with older versions. I'm wondering whether we should not >> care or if we should attempt some compatibility

Re: macOS SIP, next try

On 05.03.21 01:36, Tom Lane wrote: Hmm. So I tried this, ie "csrutil enable --without debug" in the recovery system, and after rebooting what I see is $ csrutil status System Integrity Protection status: unknown (Custom Configuration). Configuration: Apple Internal: disabled

Re: pg_upgrade failing for 200+ million Large Objects

On 07.03.21 09:43, Tharakan, Robins wrote: Attached is a proof-of-concept patch that allows Postgres to perform pg_upgrade if the instance has Millions of objects. It would be great if someone could take a look and see if this patch is in the right direction. There are some pending tasks (such

Re: About to add WAL write/fsync statistics to pg_stat_wal view

On 2021-03-08 13:44, Fujii Masao wrote: On 2021/03/05 19:54, Masahiro Ikeda wrote: On 2021-03-05 12:47, Fujii Masao wrote: On 2021/03/05 8:38, Masahiro Ikeda wrote: On 2021-03-05 01:02, Fujii Masao wrote: On 2021/03/04 16:14, Masahiro Ikeda wrote: On 2021-03-03 20:27, Masahiro Ikeda wrote:

Re: Occasional tablespace.sql failures in check-world -jnn

On 09.12.20 08:55, Michael Paquier wrote: It's not clear to me why we have this logic in the makefile at all? Somebody taught pg_regress to do so, but only on windows... See convert_sourcefiles_in(). ... Because we may still introduce this problem again if some new stuff uses

Re: [Patch] ALTER SYSTEM READ ONLY

On Wed, Mar 3, 2021 at 8:56 PM Robert Haas wrote: > > On Tue, Mar 2, 2021 at 7:22 AM Dilip Kumar wrote: > > Why do we need to move promote related code in XLogAcceptWrites? > > IMHO, this promote related handling should be in StartupXLOG only. > > That will look cleaner. > > The key design

Re: Using COPY FREEZE in pgbench

>> -res = PQexec(con, "copy pgbench_accounts from stdin"); >> +res = PQexec(con, "copy pgbench_accounts from stdin freeze"); > > I think it would be better to use the official syntax and put the "freeze" > in parentheses. Perhaps the old syntax will be desupported some day. Agreed. --

RE: should INSERT SELECT use a BulkInsertState?

> > > I am very interested in this patch, and I plan to do some > > > experiments with the > > patch. > > > Can you please rebase the patch because it seems can not applied to > > > the > > master now. > > > > Thanks for your interest. > > > > I was sitting on a rebased version since the bulk FDW

Re: pg_rewind race condition just after promotion

On Wed, Dec 9, 2020 at 6:35 PM Heikki Linnakangas wrote: > On 08/12/2020 06:45, Kyotaro Horiguchi wrote: > > At Mon, 7 Dec 2020 20:13:25 +0200, Heikki Linnakangas > wrote in > >> I think we should fix this properly. I'm not sure if it can lead to a > >> broken cluster, but at least it can cause

Re: Parallel INSERT (INTO ... SELECT ...)

Hi Amit On Mon, Mar 8, 2021 at 10:18 PM Amit Kapila wrote: > On Mon, Mar 8, 2021 at 3:54 PM Greg Nancarrow wrote: > > I've attached an updated set of patches with the suggested locking changes. (Thanks Greg.) > Amit L, others, do let me know if you have still more comments on > 0001* patch or

Re: Yet another fast GiST build

On Mon, Jan 18, 2021 at 3:52 AM Heikki Linnakangas wrote: > On 18/01/2021 00:35, Peter Geoghegan wrote: > > On Sun, Jan 17, 2021 at 12:50 PM Tom Lane wrote: > >> I noticed that gist_page_items() thinks it can hold inter_call_data->rel > >> open across a series of calls. That's completely

Re: simplifying foreign key/RI checks

On Thu, Mar 4, 2021 at 5:15 AM Tom Lane wrote: > I took a quick look at this. Thanks a lot for the review. > I guess I'm disturbed by the idea > that we'd totally replace the implementation technology for only one > variant of foreign key checks. That means that there'll be a lot > of minor

Re: pg_upgrade failing for 200+ million Large Objects

On Mon, Mar 8, 2021 at 12:02 PM Tharakan, Robins wrote: > > Thanks Peter. > > The original email [1] had some more context that somehow didn't get > associated with this recent email. Apologies for any confusion. Please take a look at your email configuration -- all your emails are lacking both

Re: Huge memory consumption on partitioned table with FKs

Hi Andy, On Mon, Mar 8, 2021 at 8:39 PM Andy Fan wrote: > On Mon, Mar 8, 2021 at 3:43 PM Andy Fan wrote: >> My point below is a bit off-topic, but I want to share it here. Since >> we implement a partitioned table in PG with the inherited class, it has much >> more flexibility than other

Re: pg_upgrade failing for 200+ million Large Objects

Hi Magnus, On Mon, 8 Mar 2021 at 23:34, Magnus Hagander wrote: > AFAICT at a quick check, pg_dump in binary upgrade mode emits one lo_create() and one ALTER ... OWNER TO for each large object - so with > 500M large objects that would be a billion statements, and thus a > billion xids. And

Re: [HACKERS] logical decoding of two-phase transactions

On Mon, Mar 8, 2021 at 4:20 PM vignesh C wrote: > > On Mon, Mar 8, 2021 at 11:30 AM Ajin Cherian wrote: > > > > On Fri, Mar 5, 2021 at 9:25 PM vignesh C wrote: > > > > > > Created new patch v53: > > Thanks for the updated patch. > I had noticed one issue, publisher does not get stopped normally

Re: Speeding up GIST index creation for tsvectors

On Wed, 3 Mar 2021 at 23:32, John Naylor wrote: > Your performance numbers look like this is a fruitful area to improve. I have > not yet tested performance, but I will do so at a later date. Thanks for reviewing the patch ! > I did some > microbenchmarking of our popcount implementation,

Re: [PATCH] pgbench: improve \sleep meta command

On 2021-Mar-08, kuroda.hay...@fujitsu.com wrote: > Dear Fujii-san, Miyake-san > > > Isn't it better to accept even negative sleep time like currently pgbench > > does? > > Otherwise we always need to check the variable is a positive integer > > (for example, using \if command) when using it as

Re: [PATCH] pg_permissions

On 3/6/21 2:03 PM, Joel Jacobson wrote: > ...but to answer the question... > >    - What permissions are there for a specific role in the database? > > you need to manually query all relevant pg_catalog or > information_schema.*_privileges views, > which is a O(n) mental effort, while the first

RE: non-HOT update not looking at FSM for large tuple update

> I've added this to the commitfest as a bug fix and added you as an author. Thanks. Patch looks good to me, but I guess there needs to be someone else reviewing too? Also, would this be a backpatchable bugfix? -Floris

Re: Huge memory consumption on partitioned table with FKs

On Mon, Mar 8, 2021 at 9:53 PM Andy Fan wrote: > On Mon, Mar 8, 2021 at 8:42 PM Amit Langote wrote: >> On Mon, Mar 8, 2021 at 8:39 PM Andy Fan wrote: >> > On Mon, Mar 8, 2021 at 3:43 PM Andy Fan wrote: >> >> My point below is a bit off-topic, but I want to share it here. Since >> >> we

Re: Parallel INSERT (INTO ... SELECT ...)

On Mon, Mar 8, 2021 at 3:54 PM Greg Nancarrow wrote: > > I've attached an updated set of patches with the suggested locking changes. > Amit L, others, do let me know if you have still more comments on 0001* patch or if you want to review it further? -- With Regards, Amit Kapila.

Re: [HACKERS] logical decoding of two-phase transactions

On Mon, Mar 8, 2021 at 7:17 AM Peter Smith wrote: > > Please find attached the latest patch set v52* > Few more comments: == 1. /* CREATE_REPLICATION_SLOT slot TEMPORARY LOGICAL plugin */ - | K_CREATE_REPLICATION_SLOT IDENT opt_temporary K_LOGICAL IDENT create_slot_opt_list + |

Re: popcount

On Tue, Jan 19, 2021 at 9:42 PM Isaac Morland wrote: > On Tue, 19 Jan 2021 at 11:38, David Fetter wrote: > >> You bring up an excellent point, which is that our builtin functions >> could use a lot more documentation directly to hand than they now >> have. For example, there's a lot of

Re: Huge memory consumption on partitioned table with FKs

On Mon, Mar 8, 2021 at 8:42 PM Amit Langote wrote: > Hi Andy, > > On Mon, Mar 8, 2021 at 8:39 PM Andy Fan wrote: > > On Mon, Mar 8, 2021 at 3:43 PM Andy Fan > wrote: > >> My point below is a bit off-topic, but I want to share it here. Since > >> we implement a partitioned table in PG with the

Re: New Table Access Methods for Multi and Single Inserts

On Sat, Feb 20, 2021 at 11:15 AM Bharath Rupireddy wrote: > > Please review the v3 patch set further. > > Below is the performance gain measured for CREATE TABLE AS with the > new multi insert am propsed in this thread: > > case 1 - 2 integer(of 4 bytes each) columns, 3 varchar(8), tuple size >

Re: [HACKERS] logical decoding of two-phase transactions

On Mon, Mar 8, 2021 at 6:25 PM Amit Kapila wrote: > > On Mon, Mar 8, 2021 at 4:20 PM vignesh C wrote: > > > > On Mon, Mar 8, 2021 at 11:30 AM Ajin Cherian wrote: > > > > > > On Fri, Mar 5, 2021 at 9:25 PM vignesh C wrote: > > > > > > > > > Created new patch v53: > > > > Thanks for the updated

Re: [PATCH] New default role allowing to change per-role/database settings

On Thu, Dec 31, 2020 at 6:16 PM Michael Banck wrote: > Hi, > > in today's world, some DBAs have no superuser rights, but we can > delegate them additional powers like CREATEDB or the pg_monitor default > role etc. Usually, the DBA can also view the database logs, either via > shell access or

Re: [PATCH] New default role allowing to change per-role/database settings

Hi, Am Montag, den 08.03.2021, 20:54 +0500 schrieb Ibrar Ahmed: > On Thu, Dec 31, 2020 at 6:16 PM Michael Banck > wrote: > > in today's world, some DBAs have no superuser rights, but we can > > delegate them additional powers like CREATEDB or the pg_monitor default > > role etc. Usually, the

Re: pg_upgrade failing for 200+ million Large Objects

Magnus Hagander writes: > On Mon, Mar 8, 2021 at 5:33 PM Tom Lane wrote: >> It does seem that --single-transaction is a better idea than fiddling with >> the transaction wraparound parameters, since the latter is just going to >> put off the onset of trouble. However, we'd have to do something

Re: Yet another fast GiST build

Thanks, Ibrar! > 8 марта 2021 г., в 21:15, Ibrar Ahmed написал(а): > > > > On Mon, Mar 8, 2021 at 8:59 PM Peter Geoghegan wrote: > On Mon, Mar 8, 2021 at 6:41 AM Ibrar Ahmed wrote: > > The patch (0001-Add-bool-column-for-LP_DEAF-flag-to-GiST-pageinspect.patch) > > does not apply

Re: Re: [PATCH] regexp_positions ( string text, pattern text, flags text ) → setof int4range[]

"Joel Jacobson" writes: > I prefer to think of a match as two points. If the points are at the same > position, it's a zero length match. FWIW, I personally think that returning a start position and a length would be the most understandable way to operate. If you report start position and end

Re: partial heap only tuples

On Wed, Feb 24, 2021 at 3:22 AM Bossart, Nathan wrote: > On 2/10/21, 2:43 PM, "Bruce Momjian" wrote: > > I wonder if you should create a Postgres wiki page to document all of > > this. I agree PG 15 makes sense. I would like to help with this if I > > can. I will need to study this email

Re: [PATCH] regexp_positions ( string text, pattern text, flags text ) → setof int4range[]

On 03/08/21 13:29, Chapman Flack wrote: > I think the s-free version is exactly the regexp_instr included in > the other concurrent proposal [1] sorry. [1] https://www.postgresql.org/message-id/fc160ee0-c843-b024-29bb-97b5da61971f%40darold.net

Re: Yet another fast GiST build

On Mon, Mar 8, 2021 at 8:59 PM Peter Geoghegan wrote: > On Mon, Mar 8, 2021 at 6:41 AM Ibrar Ahmed wrote: > > The patch > (0001-Add-bool-column-for-LP_DEAF-flag-to-GiST-pageinspect.patch) > > does not apply successfully and has multiple hanks failed. > > That's because it was committed. > >

Re: Improvements and additions to COPY progress reporting

On Mon, 8 Mar 2021 at 09:24, Michael Paquier wrote: > > On Sun, Mar 07, 2021 at 04:50:31PM +0530, Bharath Rupireddy wrote: > > Attaching remaining patches 0001 and 0003 from the v11 patch > > set(posted upthread) here to make cfbot happier. > > Looking at patch 0002, the location of each progress

Re: ResourceOwner refactoring

On Mon, Jan 25, 2021 at 10:15 PM Robert Haas wrote: > On Thu, Jan 21, 2021 at 5:14 AM Heikki Linnakangas > wrote: > > Here you can see that as numsnaps increases, the test becomes slower, > > but then it becomes faster again at 64-66, when it switches to the hash > > table. So 64 seems too

Re: [PATCH] regexp_positions ( string text, pattern text, flags text ) → setof int4range[]

On Mon, Mar 8, 2021, at 17:20, Mark Dilger wrote: > > On Mar 5, 2021, at 11:46 AM, Joel Jacobson wrote: > > <0003-regexp-positions.patch> > > I did a bit more testing: > > +SELECT regexp_positions('foobarbequebaz', 'b', 'g'); > + regexp_positions > +-- > + {"[3,5)"} > +

Re: [PATCH] regexp_positions ( string text, pattern text, flags text ) → setof int4range[]

> On Mar 8, 2021, at 9:05 AM, Joel Jacobson wrote: > > If a N+1 dimension array could easily be unnested to a N dimension array, > I would prefer Tom's idea of a 2-D regexp_positions(), since it simple and > not controversial. How about proposing some array functions to go along with the

Re: [PATCH] regexp_positions ( string text, pattern text, flags text ) → setof int4range[]

On Mon, Mar 8, 2021, at 18:11, Mark Dilger wrote: > > On Mar 8, 2021, at 9:05 AM, Joel Jacobson wrote: > > > > If a N+1 dimension array could easily be unnested to a N dimension array, > > I would prefer Tom's idea of a 2-D regexp_positions(), since it simple and > > not controversial. > > How

Re: About to add WAL write/fsync statistics to pg_stat_wal view

On 2021/03/08 19:42, Masahiro Ikeda wrote: On 2021-03-08 13:44, Fujii Masao wrote: On 2021/03/05 19:54, Masahiro Ikeda wrote: On 2021-03-05 12:47, Fujii Masao wrote: On 2021/03/05 8:38, Masahiro Ikeda wrote: On 2021-03-05 01:02, Fujii Masao wrote: On 2021/03/04 16:14, Masahiro Ikeda

Re: pg_upgrade failing for 200+ million Large Objects

On Mon, Mar 8, 2021 at 5:33 PM Tom Lane wrote: > > Robins Tharakan writes: > > On Mon, 8 Mar 2021 at 23:34, Magnus Hagander wrote: > >> Without looking, I would guess it's the schema reload using > >> pg_dump/pg_restore and not actually pg_upgrade itself. This is a known > >> issue in

Re: [PATCH] pg_permissions

On Mon, Mar 8, 2021, at 15:35, Joe Conway wrote: > While this is interesting and probably useful for troubleshooting, it does not > provide the complete picture if what you care about is something like "what > stuff can joel do in my database". Good point, I agree. I think that's a different

Re: [HACKERS] Custom compression methods

On Sun, Mar 07, 2021 at 06:04:41PM +0530, Dilip Kumar wrote: > On Sun, Mar 7, 2021 at 2:19 PM Justin Pryzby wrote: > > > > Earlier in this thread, I suggested to implement an option to pg_restore to > > avoid outputting compression, in order to allow restoring with a different > > compression (by

Re: [PATCH] regexp_positions ( string text, pattern text, flags text ) → setof int4range[]

On 03/08/21 12:30, Tom Lane wrote: > I'm inclined to think we should learn from that and provide equivalent > variants regexp_position[s] right off the bat. I think the s-free version is exactly the regexp_instr included in the other concurrent proposal [1], which closely corresponds to the ISO

Re: a verbose option for autovacuum

On Mon, Mar 8, 2021, at 2:32 AM, Masahiko Sawada wrote: > * Proposed idea > LOG: automatic vacuum of table "postgres.public.test": index scans: 1 > pages: 0 removed, 443 remain, 0 skipped due to pins, 0 skipped frozen > tuples: 1000 removed, 99000 remain, 0 are dead but not yet removable, >

Re: Yet another fast GiST build

On Mon, Mar 8, 2021 at 6:41 AM Ibrar Ahmed wrote: > The patch (0001-Add-bool-column-for-LP_DEAF-flag-to-GiST-pageinspect.patch) > does not apply successfully and has multiple hanks failed. That's because it was committed. -- Peter Geoghegan

Re: Evaluate expression at planning time for two more cases

On Tue, Nov 24, 2020 at 12:47 PM Surafel Temesgen wrote: > Hi Pavel Borisov, > It's always good to select the optimal way even if it didn't have > performance gain > but in case of this patch i see 4x speed up on my laptop and it will work > on any > table that have NULL constraint > > regards >

Re: pg_amcheck contrib application

On Thu, Mar 4, 2021 at 5:39 PM Mark Dilger wrote: > I think Robert mistook why I was doing that. I was thinking about a > different usage pattern. If somebody thinks a subset of relations have been > badly corrupted, but doesn't know which relations those might be, they might > try to find

Re: pg_upgrade failing for 200+ million Large Objects

Robins Tharakan writes: > On Mon, 8 Mar 2021 at 23:34, Magnus Hagander wrote: >> Without looking, I would guess it's the schema reload using >> pg_dump/pg_restore and not actually pg_upgrade itself. This is a known >> issue in pg_dump/pg_restore. And if that is the case -- perhaps just >>

Re: proposal: psql –help reflecting service or URI usage

Hi Mark, > On 08. Mar, 2021, at 16:39, Mark Dilger wrote: > > Fortunately, the man pages and html docs are generated from the same sources. > Those sources are written in sgml, and the tools to build the docs must be > installed. From the top directory, execute `make docs` and if it

Re: [PATCH] regexp_positions ( string text, pattern text, flags text ) → setof int4range[]

> On Mar 8, 2021, at 9:20 AM, Joel Jacobson wrote: > > On Mon, Mar 8, 2021, at 18:11, Mark Dilger wrote: >> > On Mar 8, 2021, at 9:05 AM, Joel Jacobson wrote: >> > >> > If a N+1 dimension array could easily be unnested to a N dimension array, >> > I would prefer Tom's idea of a 2-D

Re: proposal: psql –help reflecting service or URI usage

> On Mar 6, 2021, at 5:55 AM, Paul Förster wrote: > > Hi Mark, > > sorry for the delay. > >> On 01. Mar, 2021, at 17:02, Mark Dilger wrote: >> >> The output from --help should fit in a terminal window with only 80 >> characters width. For example, in src/bin/scripts/createuser.c the

Re: [PATCH] pgbench: improve \sleep meta command

On 2021/03/08 23:10, Alvaro Herrera wrote: On 2021-Mar-08, kuroda.hay...@fujitsu.com wrote: Dear Fujii-san, Miyake-san Isn't it better to accept even negative sleep time like currently pgbench does? Otherwise we always need to check the variable is a positive integer (for example, using

Re: [PATCH] regexp_positions ( string text, pattern text, flags text ) → setof int4range[]

> On Mar 5, 2021, at 11:46 AM, Joel Jacobson wrote: > > > /Joel > <0003-regexp-positions.patch> I did a bit more testing: +SELECT regexp_positions('foobarbequebaz', 'b', 'g'); + regexp_positions +-- + {"[3,5)"} + {"[6,8)"} + {"[11,13)"} +(3 rows) + I understand that these

Re: shared-memory based stats collector

On Fri, Mar 5, 2021 at 8:32 PM Fujii Masao wrote: > > > On 2021/03/05 17:18, Kyotaro Horiguchi wrote: > > At Thu, 21 Jan 2021 12:03:48 +0900 (JST), Kyotaro Horiguchi < > horikyota@gmail.com> wrote in > >> Commit 960869da08 (database statistics) conflicted with this. Rebased. > >> > >> I'm

Re: proposal: psql –help reflecting service or URI usage

> On Mar 8, 2021, at 8:40 AM, Paul Förster wrote: > > Hi Mark, > >> On 08. Mar, 2021, at 16:39, Mark Dilger wrote: >> >> Fortunately, the man pages and html docs are generated from the same >> sources. Those sources are written in sgml, and the tools to build the docs >> must be

Re: pg_stat_statements oddity with track = all

On Sun, Mar 7, 2021 at 8:39 AM Julien Rouhaud wrote: > > On Sat, Mar 06, 2021 at 06:56:49PM +0100, Magnus Hagander wrote: > > On Sun, Dec 27, 2020 at 9:39 AM Julien Rouhaud wrote: > > > > > - * > > - * Right now, this structure contains no padding. If you add any, make > > sure > > - * to

Re: pg_upgrade failing for 200+ million Large Objects

On Mon, Mar 8, 2021 at 5:58 PM Tom Lane wrote: > > Magnus Hagander writes: > > On Mon, Mar 8, 2021 at 5:33 PM Tom Lane wrote: > >> It does seem that --single-transaction is a better idea than fiddling with > >> the transaction wraparound parameters, since the latter is just going to > >> put

Re: TRUNCATE on foreign table

On Thu, Feb 11, 2021 at 6:23 PM Ashutosh Bapat wrote: > On Wed, Feb 10, 2021 at 10:58 PM Kazutaka Onishi > wrote: > > > > That's because using the foreign server is difficult for the user. > > > > For example, the user doesn't always have the permission to login to the > forein server. > > In

Re: cryptohash: missing locking functions for OpenSSL <= 1.0.2?

On Wed, 2021-03-03 at 15:30 +0900, Michael Paquier wrote: > Extra eyes are welcome here, though I feel comfortable with the > approach taken here. I have one suggestion for the new logic: >else >{ >/* > * In the non-SSL case, just remove the crypto

Re: [PATCH] pg_ownerships system view

On 3/7/21 1:08 AM, Joel Jacobson wrote: Attached is a suggestion of adding a convenience view, allowing quickly looking up all objects owned by a given user. This definitely seems like a useful feature. I know I am guilty of creating tables as the wrong role more than one time. Andreas

Re: Let people set host(no)ssl settings from initdb

On Thu, Mar 4, 2021 at 7:25 AM Michael Paquier wrote: > On Wed, Mar 03, 2021 at 03:07:30PM +0100, Peter Eisentraut wrote: > > I think there is enough sustained opposition to this patch that we can > mark > > this as rejected in the commitfest. > > +1. > -- > Michael > The patch

Re: [PATCH] regexp_positions ( string text, pattern text, flags text ) → setof int4range[]

On Mon, Mar 8, 2021, at 19:46, Joel Jacobson wrote: > However, for certain tasks, when a high-level language is preferred, > and when the raw performance of C isn't necessary, then maybe SQL/PLpgSQL > could be a serious alternative to Perl? Before we had jsonb, this would have been totally

Re: Re: [PATCH] regexp_positions ( string text, pattern text, flags text ) → setof int4range[]

"Joel Jacobson" writes: > If I understand it correctly, we don't need to run genbki.pl to compile > PostgreSQL, > so someone wanting to compile PostgreSQL without having a running > PostgreSQL-instance > could do so without problems. > A dependency on having a PostgreSQL instance running, > is

Re: Implementing Incremental View Maintenance

On 2/18/21 9:01 PM, Yugo NAGATA wrote: > On Thu, 18 Feb 2021 19:38:44 +0800 > Andy Fan wrote: > >> On Tue, Feb 16, 2021 at 9:33 AM Yugo NAGATA wrote: >> >>> Hi, >>> >>> Attached is a rebased patch (v22a). >>> >> Thanks for the patch. Will you think posting a patch with the latest commit >> at

Re: Re: [PATCH] regexp_positions ( string text, pattern text, flags text ) → setof int4range[]

po 8. 3. 2021 v 21:12 odesílatel Tom Lane napsal: > "Joel Jacobson" writes: > > If I understand it correctly, we don't need to run genbki.pl to compile > PostgreSQL, > > so someone wanting to compile PostgreSQL without having a running > PostgreSQL-instance > > could do so without problems. > >

Re: [HACKERS] Custom compression methods

On Mon, Mar 08, 2021 at 03:26:04PM -0500, Robert Haas wrote: > On Mon, Mar 8, 2021 at 5:02 AM Dilip Kumar wrote: > > So now only pending point is, how do we handle the upgrade when you > > are upgrading from --with-lz4 to --without-lz4 binary and a couple of > > options discussed here are > > a)

Re: Removing vacuum_cleanup_index_scale_factor

On Tue, Mar 2, 2021 at 6:01 PM Peter Geoghegan wrote: > 1. Any objections to the idea of teaching VACUUM ANALYZE to > distinguish between the cases where VACUUM ran and performed "real > index vacuuming", to make it more intelligent about overwriting > pg_class stats for indexes? I think that a

proposal - operators ? and ->> for type record, and functions record_keys and record_each_text

Hi When I wrote an reply to questing https://stackoverflow.com/questions/66523737/postgresql-10-pl-pgsql-test-if-column-exits-in-a-record-variable I found an interesting idea to have some basic functions and operators for record type (similar to json, jsonb or hstore). Now we can do almost all

Re: Confusing behavior of psql's \e

The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: not tested Documentation:tested, passed Very nice quality-of-life improvement. Thanks! The new status

Re: SQL/JSON: functions

On Sat, Jan 23, 2021 at 3:37 PM Erik Rijkers wrote: > On 2021-01-20 03:49, Nikita Glukhov wrote: > > > [0001-Add-common-SQL-JSON-clauses-v52.patch.gz] > > [0002-SQL-JSON-constructors-v52.patch.gz] > > [0003-IS-JSON-predicate-v52.patch.gz] > > [0004-SQL-JSON-query-functions-v52.patch.gz] > >

Re: [HACKERS] Custom compression methods

On Mon, Mar 8, 2021 at 5:02 AM Dilip Kumar wrote: > So now only pending point is, how do we handle the upgrade when you > are upgrading from --with-lz4 to --without-lz4 binary and a couple of > options discussed here are > a) Should we allow table creation with lz4 even if it is compiled >

Re: partial heap only tuples

On 3/8/21, 10:16 AM, "Ibrar Ahmed" wrote: > On Wed, Feb 24, 2021 at 3:22 AM Bossart, Nathan wrote: >> On 2/10/21, 2:43 PM, "Bruce Momjian" wrote: >>> I wonder if you should create a Postgres wiki page to document all of >>> this. I agree PG 15 makes sense. I would like to help with this if I

Re: Why isn't pg_stat_get_subscription() marked as proretset?

I wrote: > The code in pg_stat_get_subscription() appears to believe that it > can return a set of rows, but its pg_proc entry does not have > proretset set. It may be that this somehow accidentally fails > to malfunction when the function is used via the system views, > but if you try to call it

Re: automatic analyze: readahead - add "IO read time" log message

Greetings, * Tomas Vondra (tomas.von...@enterprisedb.com) wrote: > On 2/10/21 11:10 PM, Stephen Frost wrote: > > * Heikki Linnakangas (hlinn...@iki.fi) wrote: > >> On 05/02/2021 23:22, Stephen Frost wrote: > >>> Unless there's anything else on this, I'll commit these sometime next > >>> week. >

Re: pg_amcheck contrib application

> On Mar 8, 2021, at 8:26 AM, Robert Haas wrote: > > On Thu, Mar 4, 2021 at 5:39 PM Mark Dilger > wrote: >> I think Robert mistook why I was doing that. I was thinking about a >> different usage pattern. If somebody thinks a subset of relations have been >> badly corrupted, but doesn't

Re: [HACKERS] Custom compression methods

On Mon, Mar 8, 2021 at 3:59 PM Justin Pryzby wrote: > > It would be nice to have a way to force > > anything compressed with the old method to be re-compressed with the > > new method, but not having that doesn't preclude allowing the > > parameter to be changed. > > Doesn't vacuum

Re: [PATCH] regexp_positions ( string text, pattern text, flags text ) → setof int4range[]

On Mon, Mar 8, 2021, at 18:30, Tom Lane wrote: > FWIW, I personally think that returning a start position and a length > would be the most understandable way to operate. Very good point. I agree. (And then ranges cannot be used, regardless of canonical form.) > Yeah: it's hard. The amount of

Re: New IndexAM API controlling index vacuum strategies

On Mon, Feb 1, 2021 at 10:17 PM Peter Geoghegan wrote: > * No need to change MaxHeapTuplesPerPage for now, since that only > really makes sense in cases that heavily involve bottom-up deletion, > where we care about the *concentration* of LP_DEAD line pointers in > heap pages (and not just the

  1   2   >