Re: Partial aggregates pushdown

fujii.y...@df.mitsubishielectric.co.jp писал 2023-06-08 02:08: From: Alexander Pyhalov Sent: Wednesday, June 7, 2023 6:47 PM This seems to be more robust, but the interface became more strange. I'm not sure what to do with it. Some ideas I had to avoid introducing this parameter. Not sure I li

[ psql - review request ] review request for \d+ tablename, \d+ indexname indenting

Hi, We are Query Tricks. We are a project team created to provide better usability for PostgreSQL DBAs and users. and I'm Hyunhee Ryu, a member of the project team. There is something I would like you to consider introducing in a new version of the release. This is related to \d+ table_name and \d

Re: Order changes in PG16 since ICU introduction

Tatsuo Ishii wrote: > >> Yes it's a special case but when doing initdb --locale=C, a user does > >> not need or want an ICU locale. They want the same thing than what v15 > >> does with the same arguments: a template0 database with > >> datlocprovider='c', datcollate='C', datctype='C', dat

Re: Remove WindowClause PARTITION BY items belonging to redundant pathkeys

On Thu, Jun 8, 2023 at 7:37 AM David Rowley wrote: > What the attached patch does is process each WindowClause and removes > any items from the PARTITION BY clause that are columns or expressions > relating to redundant PathKeys. > > Effectively, this allows the nodeWindowAgg.c code which stops >

Re: Do we want a hashset type?

On Wed, Jun 7, 2023, at 19:37, Tomas Vondra wrote: > Interesting, considering how dumb the the hash table implementation is. That's promising. >> I tested Neo4j and the results are surprising; it appears to be >> significantly *slower*. >> However, I've probably misunderstood something, maybe I

Re: Named Prepared statement problems and possible solutions

Hi Konstantin, Yes, I ran into Euler at pgcon and he mentioned this. I intend to test it. I'd still like to see my proposal in the server. Dave Cramer On Thu, 8 Jun 2023 at 02:15, Konstantin Knizhnik wrote: > > > On 07.06.2023 10:48 PM, Dave Cramer wrote: > > Greetings, > > At pgcon last week

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 7, 2023 at 11:37 PM Andres Freund wrote: > > Hi, > > On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote: > > 2. While I wouldn't want to necessarily discourage a moonshot effort, I > > would ask if developer time could be better spent on tackling some of the > > other problems around

Re: Let's make PostgreSQL multi-threaded

I think I remember that in the early days of development somebody did send a patch-set for making PostgreSQL threaded on Solaris. I don't remember why this did not catch on. On Wed, Jun 7, 2023 at 11:40 PM Thomas Kellerer wrote: > > Tomas Vondra schrieb am 07.06.2023 um 21:20: > > Also, which ot

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 12:09 AM Andres Freund wrote: ... > We could e.g. eventually decide that we > don't support parallel query without threading support - which would allow us > to get rid of a very significant amount of code and runtime overhead. Here I was hoping to go in the opposite direc

[btree-indexed column] <@ [range | multirange]

I've noticed the planner is not yet smart enough to do an index scan when the left operand of a contains operator (<@) is a btree-indexed column and the right operand is a range or multirange type of the same type as the column. For instance, given a users table with an id int primary key column,

Re: Order changes in PG16 since ICU introduction

Jeff Davis wrote: > As I replied in that subthread, that creates a worse problem: if you > only change the provider when the locale is C, then what about when the > locale is *not* C? > > export LANG=en_US.UTF-8 > initdb -D data --locale=fr_FR.UTF-8 > ... >provider:icu >ICU

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 11:54 AM Hannu Krosing wrote: > > On Wed, Jun 7, 2023 at 11:37 PM Andres Freund wrote: > > > > Hi, > > > > On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote: > > > 2. While I wouldn't want to necessarily discourage a moonshot effort, I > > > would ask if developer time c

Re: Do we want a hashset type?

On 6/8/23 11:41, Joel Jacobson wrote: > On Wed, Jun 7, 2023, at 19:37, Tomas Vondra wrote: >> Interesting, considering how dumb the the hash table implementation is. > > That's promising. > Yeah, not bad for sleep-deprived on-plane hacking ... There's a bunch of stuff that needs to be improved

Re: running logical replication as the subscription owner

On Thu, Jun 8, 2023 at 6:32 AM Masahiko Sawada wrote: > > On Mon, Jun 5, 2023 at 3:15 AM Amit Kapila wrote: > > > > On Fri, May 26, 2023 at 6:18 PM Masahiko Sawada > > wrote: > > > > > > On Thu, May 25, 2023 at 5:41 PM Amit Kapila > > > wrote: > > > > > > I've attached the updated patch. Plea

Re: postgres_fdw: wrong results with self join + enable_nestloop off

Hi Richard, On Tue, Jun 6, 2023 at 12:20 PM Richard Guo wrote: > On Mon, Jun 5, 2023 at 9:19 PM Etsuro Fujita wrote: >> To avoid this issue, I am wondering if we should modify >> add_paths_to_joinrel() in back branches so that it just disallows the >> FDW to consider pushing down joins when the

Re: postgres_fdw: wrong results with self join + enable_nestloop off

Hi, On Wed, Jun 7, 2023 at 7:28 PM Nishant Sharma wrote: > Etsuro's patch is also showing the correct output for "set > enable_nestloop=off". Looks good to me for back branches due to backport > issues. > > But below are a few observations for the same:- > 1) I looked into the query plan for bo

Re: Let's make PostgreSQL multi-threaded

On 6/8/23 01:37, Thomas Munro wrote: > On Thu, Jun 8, 2023 at 10:37 AM Jeremy Schneider > wrote: >> On 6/7/23 2:39 PM, Thomas Kellerer wrote: >>> Tomas Vondra schrieb am 07.06.2023 um 21:20: Also, which other projects did this transition? Is there something we could learn from them? W

Seeking Guidance on Using Valgrind in PostgreSQL for Detecting Memory Leaks in Extension Code

Dear Postgres Hackers, I am writing to seek your guidance and utilization of Valgrind in PostgreSQL for detecting memory leaks in extension-related code. Recently, I have been exploring ways to improve the stability and performance of PostgreSQL extensions by addressing memory-related issues, spec

Re: win32ver data in meson-built postgres.exe

On Thu, Jun 8, 2023 at 3:45 AM Noah Misch wrote: > > On Wed, Jun 07, 2023 at 04:47:26PM -0700, Andres Freund wrote: > > On 2023-06-07 16:14:07 -0700, Noah Misch wrote: > > > A postgres.exe built with meson, ninja, and MSVC lacks the version > > > metadata > > > that postgres.exe gets under non-me

Re: Git sources doesn't contain the INSATLL file?

Hi Daniel, As a first-time developer I don't know how the released README / INSTALL are generated. So it's quite unintuitive to find the README.git file. I may expect an entry in README or a BUILD file when I obtain the sources by git clone, while a conventional ./configure && make works so it ma

Re: [DOCS] alter_foreign_table.sgml typo

On 2023/06/08 0:53, Fujii Masao wrote: On 2023/06/07 23:25, Mehmet Emin KARAKAŞ wrote: Fixed typo in SQL. Current: ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3 'value3'); Fixed: ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD op

Re: Return value of pg_promote()

On Wed, Jun 7, 2023 at 9:55 PM Fujii Masao wrote: > > > > On 2023/06/07 2:00, Laurenz Albe wrote: > > On Tue, 2023-06-06 at 16:35 +0530, Ashutosh Sharma wrote: > >> At present, pg_promote() returns true to the caller on successful > >> promotion of standby, however it returns false in multiple sce

Error in calculating length of encoded base64 string

Greetings, everyone! While working on an extension I've found an error in how length of encoded base64 string is calulated; This error is present in 3 files across all supported versions: /src/common/base64.c, function pg_b64_enc_len; /src/backend/utils/adt/encode.c, function pg_base64_enc_le

Re: Use COPY for populating all pgbench tables

I guess that COPY will still be slower than generating the data server-side ( --init-steps=...G... ) ? What I'd really like to see is providing all the pgbench functions also on the server. Specifically the various random(...) functions - random_exponential(...), random_gaussian(...), random_zipf

Re: [DOCS] alter_foreign_table.sgml typo

Thank you. Fujii Masao , 8 Haz 2023 Per, 14:19 tarihinde şunu yazdı: > > > On 2023/06/08 0:53, Fujii Masao wrote: > > > > > > On 2023/06/07 23:25, Mehmet Emin KARAKAŞ wrote: > >> Fixed typo in SQL. > >> > >> Current: ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 > 'value', SET opt2

Re: Let's make PostgreSQL multi-threaded

On 2023-06-07 We 17:58, Andres Freund wrote: Hi, On 2023-06-07 08:53:24 -0400, Robert Haas wrote: Now, Andres is not a man who accepts a tax on performance of any size without a fight, so his "really expensive" might turn out to resemble my "pretty cheap." However, if widespread use of TLS is

Re: Let's make PostgreSQL multi-threaded

On 7/6/23 23:37, Andres Freund wrote: [snip] I think we're starting to hit quite a few limits related to the process model, particularly on bigger machines. The overhead of cross-process context switches is inherently higher than switching between threads in the same process - and my suspicion is

Re: Support logical replication of DDLs

On Tue, Jun 6, 2023 at 4:26 PM Amit Kapila wrote: > > On Mon, Jun 5, 2023 at 3:00 PM shveta malik wrote: > > > > Few assorted comments: Hi Amit, thanks for the feedback. Addressed these in recent patch posted (*2023_06_08.patch) > === > 1. I see the following text in 0005 patch

Re: Let's make PostgreSQL multi-threaded

On Thu, 8 Jun 2023 at 11:54, Hannu Krosing wrote: > > On Wed, Jun 7, 2023 at 11:37 PM Andres Freund wrote: > > > > Hi, > > > > On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote: > > > 2. While I wouldn't want to necessarily discourage a moonshot effort, I > > > would ask if developer time coul

Re: Support logical replication of DDLs

On Tue, Jun 6, 2023 at 11:31 AM Wei Wang (Fujitsu) wrote: > > On Thur, June 1, 2023 at 23:42 vignesh C wrote: > > On Wed, 31 May 2023 at 14:32, Wei Wang (Fujitsu) > > wrote: > > > ~~~ > > > > > > 2. Deparsed results of the partition table. > > > When I run the following SQLs: > > > ``` > > > cre

Re: Named Prepared statement problems and possible solutions

On 6/8/23 02:15, Konstantin Knizhnik wrote: There is a PR with support of prepared statement support to pgbouncer: https://github.com/pgbouncer/pgbouncer/pull/845 any feedback, reviews and suggestions are welcome. I was about to say that the support would have to come from the pooler as it is

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 2:15 PM Matthias van de Meent wrote: > > On Thu, 8 Jun 2023 at 11:54, Hannu Krosing wrote: > > > > On Wed, Jun 7, 2023 at 11:37 PM Andres Freund wrote: > > > > > > Hi, > > > > > > On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote: > > > > 2. While I wouldn't want to nec

Parallel CREATE INDEX for BRIN indexes

Hi, Here's a WIP patch allowing parallel CREATE INDEX for BRIN indexes. The infrastructure (starting workers etc.) is "inspired" by the BTREE code (i.e. copied from that and massaged a bit to call brin stuff). _bt_begin_parallel -> _brin_begin_parallel _bt_end_parallel -> _brin_end_parallel _b

Re: Named Prepared statement problems and possible solutions

On Thu, Jun 8, 2023 at 8:43 AM Jan Wieck wrote: > On 6/8/23 02:15, Konstantin Knizhnik wrote: > > > There is a PR with support of prepared statement support to pgbouncer: > > https://github.com/pgbouncer/pgbouncer/pull/845 > > any feedback, reviews and suggestions are welcome. > > I was about to

Re: Named Prepared statement problems and possible solutions

On 08.06.2023 3:43 PM, Jan Wieck wrote: On 6/8/23 02:15, Konstantin Knizhnik wrote: There is a PR with support of prepared statement support to pgbouncer: https://github.com/pgbouncer/pgbouncer/pull/845 any feedback, reviews and suggestions are welcome. I was about to say that the support w

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 6:04 AM Hannu Krosing wrote: > Here I was hoping to go in the opposite direction and support parallel > query across replicas. > > This looks much more doable based on the process model than the single > process / multiple threads model. I don't think this is any more or le

Re: Inconsistent results with libc sorting on Windows

> On 6/7/23 07:58, Daniel Verite wrote: > > Thomas Munro wrote: > > > >> > > Also, it does not occur at all if parallel scan is disabled. > >> > > >> > Could this be a clue that it is failing to be transitive? > >> > >> That vaguely rang a bell for me... and then I remembered this thread: >

Re: Let's make PostgreSQL multi-threaded

On 07.06.2023 3:53 PM, Robert Haas wrote: I think I remember a previous conversation with Andres where he opined that thread-local variables are "really expensive" (and I apologize in advance if I'm mis-remembering this). Now, Andres is not a man who accepts a tax on performance of any size wi

Re: Named Prepared statement problems and possible solutions

On 6/8/23 09:21, Dave Cramer wrote: On Thu, Jun 8, 2023 at 8:43 AM Jan Wieck > wrote: On 6/8/23 02:15, Konstantin Knizhnik wrote: > There is a PR with support of prepared statement support to pgbouncer: > https://github.com/pgbouncer/pgbouncer/pull/8

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 7, 2023 at 5:30 PM Andres Freund wrote: > On 2023-06-05 17:51:57 +0300, Heikki Linnakangas wrote: > > If there are no major objections, I'm going to update the developer FAQ, > > removing the excuses there for why we don't use threads [1]. > > I think we should do this even if there's

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 7, 2023 at 5:37 PM Andres Freund wrote: > I think we're starting to hit quite a few limits related to the process model, > particularly on bigger machines. The overhead of cross-process context > switches is inherently higher than switching between threads in the same > process - and m

Typo in src/backend/access/nbtree/README?

Hi, I am not a native English speaker, but shouldn't there be a "to" before "detect"? These two additions make it possible detect a concurrent page split Regards Daniel

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 7, 2023 at 5:39 PM Peter Eisentraut wrote: > On 07.06.23 23:30, Andres Freund wrote: > > Yea, we definitely need the supervisor function in a separate > > process. Presumably that means we need to split off some of the postmaster > > responsibilities - e.g. I don't think it'd make sens

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 7, 2023 at 5:45 PM Andres Freund wrote: > People have argued that the process model is more robust. But it turns out > that we have to crash-restart for just about any "bad failure" anyway. It used > to be (a long time ago) that we didn't, but that was just broken. How hard have you t

Re: Cleaning up nbtree after logical decoding on standby work

On 2023-Jun-07, Peter Geoghegan wrote: > On Wed, Jun 7, 2023 at 5:12 PM Andres Freund wrote: > > I don't really understand why the patch does s/heaprel/heapRel/. > > That has been the style used within nbtree for many years now. IMO this kind of change definitely does not have place in a post-

Re: Named Prepared statement problems and possible solutions

On 6/8/23 09:53, Jan Wieck wrote: On 6/8/23 09:21, Dave Cramer wrote: The server doesn't know about all the clients of the pooler, does it? It has no way of telling if/when a client disconnects from the pooler. Another problem that complicates doing it in the server is that the information req

Re: Let's make PostgreSQL multi-threaded

On Wed, 7 Jun 2023 at 18:09, Andres Freund wrote: > Having the same memory mapping between threads makes allows the > hardware to share the TLB (on x86 via process context identifiers), which > isn't realistically possible with different processes. As a matter of historical interest Solaris actua

Re: Error in calculating length of encoded base64 string

o.tselebrovs...@postgrespro.ru writes: > While working on an extension I've found an error in how length of > encoded base64 string is calulated; Yeah, I think you're right. It's not of huge significance, because it just overestimates by 1 or 2 bytes, but we might as well get it right. Thanks f

Re: Named Prepared statement problems and possible solutions

On Thu, 8 Jun 2023 at 09:53, Jan Wieck wrote: > On 6/8/23 09:21, Dave Cramer wrote: > > > > > > On Thu, Jun 8, 2023 at 8:43 AM Jan Wieck > > wrote: > > > > On 6/8/23 02:15, Konstantin Knizhnik wrote: > > > > > There is a PR with support of prepared statement supp

Re: Named Prepared statement problems and possible solutions

On Thu, 8 Jun 2023 at 10:31, Jan Wieck wrote: > On 6/8/23 09:53, Jan Wieck wrote: > > On 6/8/23 09:21, Dave Cramer wrote: > > The server doesn't know about all the clients of the pooler, does it? It > > has no way of telling if/when a client disconnects from the pooler. > > Another problem that c

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 8:44 AM Hannu Krosing wrote: > > That sounds like a bad idea, dynamic shared memory is more expensive > > to maintain than our static shared memory systems, not in the least > > because DSM is not guaranteed to share the same addresses in each > > process' address space. > >

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 4:56 PM Robert Haas wrote: > > On Thu, Jun 8, 2023 at 8:44 AM Hannu Krosing wrote: > > > That sounds like a bad idea, dynamic shared memory is more expensive > > > to maintain than our static shared memory systems, not in the least > > > because DSM is not guaranteed to sha

Re: Let's make PostgreSQL multi-threaded

On Thu, 8 Jun 2023 at 14:44, Hannu Krosing wrote: > > On Thu, Jun 8, 2023 at 2:15 PM Matthias van de Meent > wrote: > > > > On Thu, 8 Jun 2023 at 11:54, Hannu Krosing wrote: > > > > > > This part was touched in the "AMA with a Linux Kernale Hacker" > > > Unconference session where he mentioned t

Re: Named Prepared statement problems and possible solutions

On 6/8/23 10:56, Dave Cramer wrote: On Thu, 8 Jun 2023 at 10:31, Jan Wieck > wrote: On 6/8/23 09:53, Jan Wieck wrote: > On 6/8/23 09:21, Dave Cramer wrote: > The server doesn't know about all the clients of the pooler, does it? It > has no way

Re: Seeking Guidance on Using Valgrind in PostgreSQL for Detecting Memory Leaks in Extension Code

On 08/06/2023 14:08, Pradeep Kumar wrote: I am writing to seek your guidance and utilization of Valgrind in PostgreSQL for detecting memory leaks in extension-related code. https://wiki.postgresql.org/wiki/Valgrind is a good place to start. The same tricks for using Valgrind on PostgreSQL its

Re: Named Prepared statement problems and possible solutions

On Thu, 8 Jun 2023 at 11:15, Jan Wieck wrote: > On 6/8/23 10:56, Dave Cramer wrote: > > > > > > > > > > On Thu, 8 Jun 2023 at 10:31, Jan Wieck > > wrote: > > > > On 6/8/23 09:53, Jan Wieck wrote: > > > On 6/8/23 09:21, Dave Cramer wrote: > > > The server doe

Re: Named Prepared statement problems and possible solutions

On 08.06.2023 6:18 PM, Dave Cramer wrote: On Thu, 8 Jun 2023 at 11:15, Jan Wieck wrote: On 6/8/23 10:56, Dave Cramer wrote: > > > > > On Thu, 8 Jun 2023 at 10:31, Jan Wieck > wrote: > >     On 6/8/23 09:53, Jan Wieck wrote: >     

Re: GTIN14 support for contrib/isn

čt 8. 6. 2023 v 17:20 odesílatel Michael Kefeder napsal: > > > Am 15.03.19 um 17:27 schrieb Tom Lane: > > Michael Kefeder writes: > >> For a project of ours we need GTIN14 data type support. > > > > Hm, what is that and where would a reviewer find the specification for it? > > > specs are from GS

Re: Allow pg_archivecleanup to remove backup history files

On 2023/05/31 10:51, torikoshia wrote: Update the patch according to the advice. Thanks for updating the patches! I have small comments regarding 0002 patch. + + Remove backup history files. Isn't it better to document clearly which backup history files to be removed? For e

Re: Cleaning up nbtree after logical decoding on standby work

On Thu, Jun 8, 2023 at 7:22 AM Alvaro Herrera wrote: > IMO this kind of change definitely does not have place in a post-beta1 > restructuring patch. We rarely indulge in case-fixing exercises at any > other time, and I don't see any good argument why post-beta1 is a better > time for it. There i

Re: Let's make PostgreSQL multi-threaded

On 2023-06-08 10:33:26 -0400, Greg Stark wrote: > On Wed, 7 Jun 2023 at 18:09, Andres Freund wrote: > > Having the same memory mapping between threads makes allows the > > hardware to share the TLB (on x86 via process context identifiers), which > > isn't realistically possible with different proc

Re: Let's make PostgreSQL multi-threaded

On Thu, 8 Jun 2023 at 17:02, Hannu Krosing wrote: > > On Thu, Jun 8, 2023 at 4:56 PM Robert Haas wrote: > > > > On Thu, Jun 8, 2023 at 8:44 AM Hannu Krosing wrote: > > > > That sounds like a bad idea, dynamic shared memory is more expensive > > > > to maintain than our static shared memory syste

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 11:02 AM Hannu Krosing wrote: > No, I meant that this needs to be fixed at OS level, by being able to > use the same mapping. > > We should not shy away from asking the OS people for adding the useful > features still missing. > > It was mentioned in the Unconference Kernel

Re: Let's make PostgreSQL multi-threaded

On 2023-06-08 14:01:16 +0200, Jose Luis Tallon wrote: > * For "heavyweight" queries, the scalability of "almost independent" > processes w.r.t. NUMA is just _impossible to achieve_ (locality of > reference!) with a pure threaded system. When CPU+mem-bound > (bandwidth-wise), threads add nothing IMO

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-08 12:15:58 +0200, Hannu Krosing wrote: > On Thu, Jun 8, 2023 at 11:54 AM Hannu Krosing wrote: > > > > On Wed, Jun 7, 2023 at 11:37 PM Andres Freund wrote: > > > > > > Hi, > > > > > > On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote: > > > > 2. While I wouldn't want to necessa

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 8:44 AM Hannu Krosing wrote: > Do we have any statistics for the distribution of our user base ? > > My gut feeling says that for performance-critical use the non-Linux is > in low single digits at best. > Stats are probably not possible, but based on years of consulting,

Re: Use COPY for populating all pgbench tables

On Thu Jun 8, 2023 at 12:33 AM CDT, David Rowley wrote: > On Thu, 8 Jun 2023 at 07:16, Tristan Partin wrote: > > > > master: > > > > 5000 of 5000 tuples (100%) done (elapsed 260.93 s, remaining 0.00 > > s)) > > vacuuming... > > creating primary keys... > > done in 1414.26 s (drop tables 0

Re: Making Vars outer-join aware

[ back from PGCon ... ] "Anton A. Melnikov" writes: > On 04.05.2023 15:22, Tom Lane wrote: >> Under what circumstances would you be trying to inject INDEX_VAR >> into a nullingrel set? Only outer-join relids should ever appear there. > The thing is that i don't try to push INDEX_VAR into a null

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-08 16:47:48 +0300, Konstantin Knizhnik wrote: > Actually TLS not not more expensive then accessing struct fields (at least > at x86 platform), consider the following program: It really depends on the OS and the architecture, not just the architecture. And even on x86-64 Linux, the

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 07, 2023 at 10:26:07AM +1200, Thomas Munro wrote: > On Tue, Jun 6, 2023 at 6:52???AM Andrew Dunstan wrote: > > If we were starting out today we would probably choose a threaded > > implementation. But moving to threaded now seems to me like a > > multi-year-multi-person project with

Re: Let's make PostgreSQL multi-threaded

I discovered this thread from a Twitter post "PostgreSQL will finally be rewritten in Rust" :) On Mon, Jun 5, 2023 at 5:18 PM Tom Lane wrote: > > Heikki Linnakangas writes: > > I spoke with some folks at PGCon about making PostgreSQL multi-threaded, > > so that the whole server runs in a single

Re: Named Prepared statement problems and possible solutions

On Thu, 8 Jun 2023 at 11:22, Konstantin Knizhnik wrote: > > > On 08.06.2023 6:18 PM, Dave Cramer wrote: > > > > On Thu, 8 Jun 2023 at 11:15, Jan Wieck wrote: > >> On 6/8/23 10:56, Dave Cramer wrote: >> > >> > >> > >> > >> > On Thu, 8 Jun 2023 at 10:31, Jan Wieck > > > wro

Re: Use of additional index columns in rows filtering

Hi, I took a stab at this and implemented the trick with the VM - during index scan, we also extract the filters that only need the indexed attributes (just like in IOS). And then, during the execution we: 1) scan the index using the scan keys (as before) 2) if the heap page is all-visible,

Postgres v15 windows bincheck regression test failures

Hi All: I upgraded to postgres v15, and I am getting intermittent failures for some of the bin regression tests when building on Windows 10. Example: perl vcregress.pl bincheck Installation complete. t/001_initdb.pl .. ok All tests successful. Files=1, Tests=25, 12 wallclock secs ( 0.03 usr + 0

Re: explain analyze rows=%.0f

On Mon, Mar 20, 2023 at 7:56 PM Gregory Stark (as CFM) wrote: > On Wed, 4 Jan 2023 at 10:05, Ibrar Ahmed wrote: > > > > Thanks, I have modified everything as suggested, except one point > > > > > Don't use variable format strings. They're hard to read and they > > > probably defeat compile-time

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-08 17:02:08 +0200, Hannu Krosing wrote: > On Thu, Jun 8, 2023 at 4:56 PM Robert Haas wrote: > > > > On Thu, Jun 8, 2023 at 8:44 AM Hannu Krosing wrote: > > > > That sounds like a bad idea, dynamic shared memory is more expensive > > > > to maintain than our static shared memory sy

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-08 17:55:57 +0200, Matthias van de Meent wrote: > While I agree that "sharing page tables across processes" is useful, > it looks like it'd be much more effort to correctly implement for e.g. > DSM than implementing threading. > Konstantin's diff is "only" 20.1k lines [0] added and/

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-08 11:56:13 -0400, Robert Haas wrote: > On Thu, Jun 8, 2023 at 11:02 AM Hannu Krosing wrote: > > No, I meant that this needs to be fixed at OS level, by being able to > > use the same mapping. > > > > We should not shy away from asking the OS people for adding the useful > > featur

Re: index prefetching

On Thu, Jun 8, 2023 at 8:40 AM Tomas Vondra wrote: > We already do prefetching for bitmap index scans, where the bitmap heap > scan prefetches future pages based on effective_io_concurrency. I'm not > sure why exactly was prefetching implemented only for bitmap scans, but > I suspect the reasoning

Re: Let's make PostgreSQL multi-threaded

On Fri, Jun 9, 2023 at 5:02 AM Ilya Anfimov wrote: > Isn't all the memory operations would require nearly the same > shared memory allocators if someone switches to a threaded imple- > mentation? It's true that we'd need concurrency-aware MemoryContext implementations (details can be debated)

Re: Let's make PostgreSQL multi-threaded

On 8/6/23 15:56, Robert Haas wrote: Yeah, I've had similar thoughts. I'm not exactly sure what the advantages of such a refactoring might be, but the current structure feels pretty limiting. It works OK because we don't do anything in the postmaster other than fork a new backend, but I'm not sure

Re: Postgres v15 windows bincheck regression test failures

On 2023-06-08 Th 13:41, Russell Foster wrote: Hi All: I upgraded to postgres v15, and I am getting intermittent failures for some of the bin regression tests when building on Windows 10. Example: perl vcregress.pl bincheck Installation complete. t/001_initdb.pl .. ok All tests successful. Fil

Re: Let's make PostgreSQL multi-threaded

On Fri, Jun 9, 2023 at 4:00 AM Andres Freund wrote: > On 2023-06-08 12:15:58 +0200, Hannu Krosing wrote: > > > This part was touched in the "AMA with a Linux Kernale Hacker" > > > Unconference session where he mentioned that the had proposed a > > > 'mshare' syscall for this. > > As-is that'd just

Re: Let's make PostgreSQL multi-threaded

> On Mon, Jun 05, 2023 at 06:43:54PM +0300, Heikki Linnakangas wrote: > On 05/06/2023 11:28, Tristan Partin wrote: > > > # Exposed PIDs > > > > > > We expose backend process PIDs to users in a few places. > > > pg_stat_activity.pid and pg_terminate_backend(), for example. They need > > > to be repl

Re: Named Prepared statement problems and possible solutions

On 6/8/23 13:31, Dave Cramer wrote: On Thu, 8 Jun 2023 at 11:22, Konstantin Knizhnik > wrote: So it will be responsibility of client to remember text of prepared query to be able to resend it when statement doesn't exists at server? IMHO very strange d

Re: Major pgbench synthetic SELECT workload regression, Ubuntu 23.04+PG15

Gregory Smith writes: > Pushing SELECT statements at socket speeds with prepared statements is a > synthetic benchmark that normally demos big pgbench numbers. My benchmark > farm moved to Ubuntu 23.04/kernel 6.2.0-20 last month, and that test is > badly broken on the system PG15 at larger core c

Re: Named Prepared statement problems and possible solutions

On Thu, 8 Jun 2023 at 15:49, Jan Wieck wrote: > On 6/8/23 13:31, Dave Cramer wrote: > > > > On Thu, 8 Jun 2023 at 11:22, Konstantin Knizhnik > > wrote: > > > > > So it will be responsibility of client to remember text of prepared > > query to be able to resend

Re: Let's make PostgreSQL multi-threaded

On Thu, 8 Jun 2023 at 13:08, Hannu Krosing wrote: > I discovered this thread from a Twitter post "PostgreSQL will finally > be rewritten in Rust" :) > By the time we got around to finishing this, there would be a better language to write it in. Dave

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-09 07:34:49 +1200, Thomas Munro wrote: > I wasn't in Mathew Wilcox's unconference in Ottawa but I found an > older article on LWN: > > https://lwn.net/Articles/895217/ > > For what it's worth, FreeBSD hackers have studied this topic too (and > it's been done in Android and no doub

Re: ERROR: no relation entry for relid 6

Richard Guo writes: > On Tue, May 30, 2023 at 10:28 AM Richard Guo wrote: >> I haven't thought through how to fix it, but I suspect that we may need >> to do more checking before we decide to remove PHVs in >> remove_rel_from_query. Oh, I like this example! It shows a place where we are now sma

Re: Order changes in PG16 since ICU introduction

On Wed, 2023-06-07 at 20:52 -0400, Joe Conway wrote: > If the provider has no such thing, throw an error. Just to be clear, that implies that users (and buildfarm members) with LANG=C.UTF-8 in their environment would not be able to run a plain "initdb -D data"; they'd get an error. It's hard for m

Re: Named Prepared statement problems and possible solutions

On 6/8/23 15:57, Dave Cramer wrote: Apparently this is coming in pgbouncer Support of prepared statements by knizhnik · Pull Request #845 · pgbouncer/pgbouncer (github.com) I am quite interested in that patch. Considering how pgbouncer works

Re: Major pgbench synthetic SELECT workload regression, Ubuntu 23.04+PG15

On Thu, Jun 8, 2023 at 3:09 PM Gregory Smith wrote: > Pushing SELECT statements at socket speeds with prepared statements is a > synthetic benchmark that normally demos big pgbench numbers. My benchmark > farm moved to Ubuntu 23.04/kernel 6.2.0-20 last month, and that test is badly > broken on

Re: Order changes in PG16 since ICU introduction

On 6/8/23 17:15, Jeff Davis wrote: On Wed, 2023-06-07 at 20:52 -0400, Joe Conway wrote: If the provider has no such thing, throw an error. Just to be clear, that implies that users (and buildfarm members) with LANG=C.UTF-8 in their environment would not be able to run a plain "initdb -D data";

Re: Fix search_path for all maintenance commands

On Fri, 26 May 2023 at 19:22, Jeff Davis wrote: > > Maintenance commands (ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW, > REINDEX, and VACUUM) currently run as the table owner, and as a > SECURITY_RESTRICTED_OPERATION. > > I propose that we also fix the search_path to "pg_catalog, pg_temp" > when r

Re: index prefetching

On 6/8/23 20:56, Peter Geoghegan wrote: > On Thu, Jun 8, 2023 at 8:40 AM Tomas Vondra > wrote: >> We already do prefetching for bitmap index scans, where the bitmap heap >> scan prefetches future pages based on effective_io_concurrency. I'm not >> sure why exactly was prefetching implemented only

Re: Major pgbench synthetic SELECT workload regression, Ubuntu 23.04+PG15

Hi, On 2023-06-08 15:08:57 -0400, Gregory Smith wrote: > Pushing SELECT statements at socket speeds with prepared statements is a > synthetic benchmark that normally demos big pgbench numbers. My benchmark > farm moved to Ubuntu 23.04/kernel 6.2.0-20 last month, and that test is > badly broken on

Re: Major pgbench synthetic SELECT workload regression, Ubuntu 23.04+PG15

Hi, On 2023-06-08 15:18:07 -0700, Andres Freund wrote: > E.g. on my workstation (two sockets, 10 cores/20 threads each), with 32 > clients, performance changes back and forth between ~600k and ~850k. Whereas > with 42 clients, it's steadily at 1.1M, with little variance. FWIW, this is with linux

Re: Introduce WAIT_EVENT_EXTENSION and WAIT_EVENT_BUFFER_PIN

On Thu, Jun 08, 2023 at 10:57:55AM +0900, Masahiro Ikeda wrote: > (Excuse me for cutting in, and this is not directly related to the thread.) > +1. I'm interested in the feature. > > This is just a example and it probable be useful for other users. IMO, at > least, it's better to improve the speci

Re: Let's make PostgreSQL multi-threaded

This is an interesting message thread. I think in regards to the OP's call to make PG multi-threaded, there should be a clear and identifiable performance target and use cases for the target. How much performance boost can be expected, and if so, in which data application context? Will queries retu

  1   2   >