Re: Restoring default privileges on objects
On 2023-08-29 14:44:48 -0600, Stuart McGraw wrote: > On 8/29/23 13:27, Tom Lane wrote: > > Fixing \dp to honor "\pset null" for this might be a reasonable > > thing to do too. I'm actually a bit surprised that that doesn't > > work already. > > That change would still require someone using \dp to realize that > the "Access privileges" value could be either '' or NULL (I guess > that could be pointed out more obviously in the psql doc), and then > do a '\pset null' before doing \dp? That seems a little inconvenient. Or just always do a \pset null. For me printing NULL the same as an empty string is just as confusing in normal tables, so that's the first line in my ~/.psqlrc. YMMV, of course. But I guess the point is that people who do \pset null expect to be able to distinguish '' and NULL visually and might be surprised if that doesn't work everywhere, while people who don't \pset null know that '' and NULL are visually indistinguishable and that they may need some other way to distinguish them if the difference matters. So +1 for me fixing \dp to honor "\pset null". hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Calculating vm.nr_hugepages
Hello, I'm writing an Ansible play which is to set the correct value for vm.nr_hugepages on Linux servers where I hope to make Postgres make use of huge pages. However, I'm struggling to find the right formula. I assume I need to find the same value as I get from running "postgres -C shared_memory_size_in_huge_pages". I call that my target value. Note: I cannot simply run "postgres -C ...", because I need my Ansible play to work against a server where Postgres is running. I've tried using the formula described at https://www.cybertec-postgresql.com/en/huge-pages-postgresql/, but it produces a different value than my target: Using a shared_buffers value of 21965570048, like in Cybertec Postgresql's example: "postgres ... -C 21965570048B" yields: 10719 The formula from Cybertec Postgresql says: 10475 I've also tried doing what ChatGPG suggested: Number of Huge Pages when shared_buffers is set to 1 GiB = shared_buffers / huge_page_size = 1073741824 bytes / 2097152 bytes = 512 But that's also wrong compared to "postgres -C ..." (which said 542). Which formula can I use? It's OK for me for it to be slightly wrong compared to "postgres -C", but if it's wrong, it needs to be slightly higher than what "postgres -C" outputs, so that I'm sure there's enough huge pages for Postgres to be able to use them properly. -- Kind regards, Troels Arvin
Re: Calculating vm.nr_hugepages
On Wed, Aug 30, 2023 at 8:12 AM Troels Arvin wrote: > Hello, > > I'm writing an Ansible play which is to set the correct value for > vm.nr_hugepages on Linux servers where I hope to make Postgres make use > of huge pages. > > However, I'm struggling to find the right formula. > > I assume I need to find the same value as I get from running "postgres > -C shared_memory_size_in_huge_pages". I call that my target value. > Note: I cannot simply run "postgres -C ...", because I need my Ansible > play to work against a server where Postgres is running. > > I've tried using the formula described at > https://www.cybertec-postgresql.com/en/huge-pages-postgresql/, but it > produces a different value than my target: > > Using a shared_buffers value of 21965570048, like in Cybertec > Postgresql's example: > "postgres ... -C 21965570048B" yields: 10719 > The formula from Cybertec Postgresql says: 10475 > > I've also tried doing what ChatGPG suggested: > Number of Huge Pages when shared_buffers is set to 1 GiB = > shared_buffers / huge_page_size > = 1073741824 bytes / 2097152 bytes > = 512 > But that's also wrong compared to "postgres -C ..." (which said 542). > > Which formula can I use? It's OK for me for it to be slightly wrong > compared to "postgres -C", but if it's wrong, it needs to be slightly > higher than what "postgres -C" outputs, so that I'm sure there's enough > huge pages for Postgres to be able to use them properly. > Good morning Troels, I had a similar thread a couple of years ago, you may want to read: https://www.postgresql.org/message-id/flat/CAHJZqBBLHFNs6it-fcJ6LEUXeC5t73soR3h50zUSFpg7894qfQ%40mail.gmail.com In it, Justin Przby provides the detailed code for exactly what factors into HugePages, if you require that level of precision. I hadn't seen that Cybertec blog post before. I ended up using my own equation that I derived in that thread after Justin shared his info. The chef/ruby code involved is: padding = 100 if shared_buffers_size > 4 padding = 500 end shared_buffers_usage = shared_buffers_size + 200 + (25 * shared_buffers_size / 1024) max_connections_usage = (max_connections - 100) / 20 wal_buffers_usage = (wal_buffers_size - 16) / 2 vm.nr_hugepages = ((shared_buffers_usage + max_connections_usage + wal_buffers_usage + padding) / 2).ceil() wal_buffers_size is usually 16MB so wal_buffers_usage ends up being zeroed out. This has worked out for our various postgres VM sizes. There's obviously going to be a little extra HugePages that goes unused, but these VMs are dedicated for postgresql usage and shared_buffers_size defaults to 25% of VM memory so there's still plenty to spare. But we use this so we can configure vm.nr_hugepages at deployment time via Chef. Don. -- Don Seiler www.seiler.us
Re: Calculating vm.nr_hugepages
> On 30/08/2023 15:12 CEST Troels Arvin wrote: > > I assume I need to find the same value as I get from running "postgres > -C shared_memory_size_in_huge_pages". I call that my target value. > Note: I cannot simply run "postgres -C ...", because I need my Ansible > play to work against a server where Postgres is running. > > I've tried using the formula described at > https://www.cybertec-postgresql.com/en/huge-pages-postgresql/, but it > produces a different value than my target: > > Using a shared_buffers value of 21965570048, like in Cybertec > Postgresql's example: > "postgres ... -C 21965570048B" yields: 10719 > The formula from Cybertec Postgresql says: 10475 Probably because 21965570048B > 20GB. What does your command look like exactly? Why do you use shared_buffers=21965570048B and not 20GB? The larger value is quoted in the last section of the linked blog post for pre-15 Postgres and is the shared memory size that Postgres wants to allocate but fails to do with shared_buffers=20GB. The section also provides the formula for manually calculating vm.nr_hugepages. > I've also tried doing what ChatGPG suggested: > Number of Huge Pages when shared_buffers is set to 1 GiB = > shared_buffers / huge_page_size > = 1073741824 bytes / 2097152 bytes > = 512 > But that's also wrong compared to "postgres -C ..." (which said 542). The formula from the blog post gives me 513 but it also includes some additional shared memory for internal stuff. So 512 is correct when the shared memory size already includes the overhead for internal stuff. -- Erik
event trigger clarification
Hi, the doc v15 states: " pg_event_trigger_ddl_commands returns a list of DDL commands executed by each user action, when invoked in a function attached to a ddl_command_end event trigger." When some ddl command is executed within a block, I would like to know if the event trigger fires when the line is executed or at commit time. thanks. Marc MILLAS Senior Architect +33607850334 www.mokadb.com
Re: event trigger clarification
> On 30/08/2023 17:12 CEST Marc Millas wrote: > > the doc v15 states: " pg_event_trigger_ddl_commands returns a list of DDL > commands executed by each user action, when invoked in a function attached > to a ddl_command_end event trigger." > When some ddl command is executed within a block, I would like to know if the > event trigger fires when the line is executed or at commit time. The event trigger fires just before[1]/after[2] executing a complete command. So not at commit time. [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/tcop/utility.c;h=6b0a8652622b26887ea2ccd15ced3300e951c5dc#l1120 [2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/tcop/utility.c;h=6b0a8652622b26887ea2ccd15ced3300e951c5dc#l1923 -- Erik
Re: event trigger clarification
On Wednesday, August 30, 2023, Marc Millas wrote: > Hi, > the doc v15 states: " pg_event_trigger_ddl_commands returns a list of DDL > commands > executed by each user action, when invoked in a function attached to a > ddl_command_end event trigger." > When some ddl command is executed within a block, I would like to know if > the event trigger fires when the line is executed or at commit time. > https://www.postgresql.org/docs/current/event-trigger-definition.html There is nothing there about event execution being able to be deferred. David J.
Re: Partitionwise JOIN scanning all partitions, even unneeded ones
On Thu, 31 Aug 2023 at 07:55, Dimitrios Apostolou wrote: > I'd appreciate help on whether it's a real issue, and if it's unknown I > can forward this to the psql-bugs mailing list. I'd also appreciate any > critique on the clarity of my description and on my schema and queries, > since I'm new to postgres. Thank you for posting this here first. We often get reports on bugs that are not bugs, so this saves from traffic there. > = Slow query: > > EXPLAIN (ANALYZE, VERBOSE,BUFFERS,SETTINGS) SELECT DISTINCT workitem_n > FROM task_ids > JOIN tasks_mm_workitems USING(task_n) > JOIN test_runs_raw USING(workitem_n) > WHERE task_id = '1698813977'; > > The EXPLAIN output here shows a parallel hash join doing seq scans on each > and every partition. Basically the whole 10G rows table is being > seq-scanned. I'm sorry to say that this is not a bug, it's simply an optimisation that we've not yet implemented. The run-time partition pruning that runs and causes the "(never executed)" Append subnodes in the fast plan appears because run-time pruning during execution only works when the Append (or MergeAppend) is parameterised by some column from above or from the outer side of the join. You can see that in this fragment of your EXPLAIN output: -> Index Only Scan using test_runs_raw__part_max6180k_pkey on public.test_runs_raw__part_max6180k test_runs_raw_309 Output: test_runs_raw_309.workitem_n Index Cond: (test_runs_raw_309.workitem_n = tasks_mm_workitems.workitem_n) Note that tasks_mm_workitems is from the outer side of the join. The same isn't done for Hash Joins as there is no parameterisation with that join type. It is technically possible to do, but it means running the partition pruning algorithm once for each row that goes into the hash table and taking the union of all the matching partitions. That's quite a bit of work, especially if you don't manage to prune anything in the end. Having said that, there is some work going on by Richard Guo [1] where he aims to implement this. It is quite a tricky thing to do without causing needless pruning work in cases where no partitions can be pruned. If you have an interest, you can follow the thread there to see the discussion about the difficulties with implementing this in a way that does not cause performance regressions for queries where no pruning was possible. David [1] https://commitfest.postgresql.org/44/4512/
Re: Partitionwise JOIN scanning all partitions, even unneeded ones
Thank you for the clear explanation, and I hope the missing optimisation gets implemented sooner rather than later. Maybe the query planner should consider the missing optimisation and ban *hash* partitionwise joins. Indeed I verified that disabling hash join fixed the situation, with both queries taking almost the same time. Great! In the meantime, I'm considering disabling hash joins globally, as I've had issues with them before (excessive I/O slowing causing much slower execution than merge join, see [1] for your answer to my question then :). Do you think that would save me from other problems I'm not aware of, given the huge size of the table I'm querying? [1] https://www.postgresql.org/message-id/caaphdvppvydonkeqlybsbjwq8kq8m7ywdka44rtea2mnao3...@mail.gmail.com Regards, Dimitris
PG FDW query fails, works local, same credentials
I've got a view on server A (PG 15.2) that fails when queried via FDW from server B (also PG 15.2). Querying it as a foreign table from server B yields a message like "ERROR: function blah(type) does not exist". Confusingly, I succeed when: I log into server A, set my role to match the role used with the FDW, and query with the exact query text that server B sent to server A (according to the error on server B). The function that it complains about does exist, and I have made the effort to provide appropriate grants to the role in question. So it does work as expected from within server A, as the same role. But why would it then fail from server B? Both cases use the same role/credentials. Plenty of other foreign tables work in this same setup between these two servers, whether pointing to tables or views. I hit and resolved similar issues earlier where the role lacked necessary grants. Having experienced it before, this time I'm pretty sure that the right grants are in place, yet it still fails when used via FDW. I'm running out of things to try and wondering if this will turn out to be a bug. What should I be trying before reporting it as a bug? Thanks, Pete
Re: PG FDW query fails, works local, same credentials
"Pete O'Such" writes: > I've got a view on server A (PG 15.2) that fails when queried via FDW from > server B (also PG 15.2). Querying it as a foreign table from server B > yields a message like "ERROR: function blah(type) does not exist". Check your search path assumptions. postgres_fdw runs remote queries with a very minimal search_path setting, so that unqualified references to non-built-in objects are likely to fail. regards, tom lane
