Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY
El mié, 7 feb 2024 8:07, Sean v escribió: > Exactly. I'm really just trying to understand if there's some functional > limitation to it being able to do that with how it executes these types of > queries, or if its just an optimization that hasn't been built into the > query planner yet. > > I know I can get it to do precisely this if I use a CROSS JOIN LATERAL: > > SELECT o.*FROM company_users cuCROSS JOIN LATERAL ( >SELECT * >FROM orders o >WHERE o.user_id = company_users.user_id >ORDER BY created_at DESC LIMIT 50 >) cuWHERE cu.company_id = ? ORDER BY created_at DESC LIMIT 50 > > That makes sense to me, it forces a nested loop and executes for each > user. But doing a nested select like the query below doesn't use the index > or limit the results to 50 per user - even though it does a nested loop > just like the lateral join does: > > SELECT "orders".* FROM "orders" WHERE user_id IN (SELECT user_id FROM > company_users WHERE company_id = ?)ORDER BY "orders"."created_at" LIMIT 50 > > Joins will generally query the whole tables, leading to long run times. Have you tried to preselect the rows of interest with a "WITH ... SELECT ..." query to reduce the amount of data processed? On 2024-02-05 7:58 a.m., David G. Johnston wrote: > > On Mon, Feb 5, 2024 at 8:55 AM Ron Johnson > wrote: > >> >> Who knows which users are going to be in that list??? >> >> > It doesn't matter. Worse case scenario there is only one user in the > result and so all 50 rows are their earliest 50 rows. The system will thus > never need more than the earliest 50 rows per user to answer this question. > > David J. > > Cheers Olivier > >
Re: Question on partitioning
On Tue, Feb 6, 2024 at 2:40 PM veem v wrote: > Thank you Laurenz. Got it. > > So basically , you mean to say any DDL on a table won't allow the table to > be read by other processes. I was under the assumption that it should allow > the read queries to move ahead at least. I must be wrong here. Thanks for > correcting me. > That word "any" in "any DDL" is quite a big word. It's certainly not going to allow reads while you're adding a Foreign Key. And the whole purpose of adding the CONCURRENTLY key word to CREATE INDEX is because regular CREATE INDEX statements block everyone else. https://www.postgresql.org/docs/16/sql-createindex.html Look for the keyword CONCURRENTLY in https://www.postgresql.org/docs/current/sql-altertable.html
Re: Question on partitioning
Thank you Laurenz. Got it. So basically , you mean to say any DDL on a table won't allow the table to be read by other processes. I was under the assumption that it should allow the read queries to move ahead at least. I must be wrong here. Thanks for correcting me. On Tue, 6 Feb 2024 at 15:46, Laurenz Albe wrote: > On Tue, 2024-02-06 at 00:26 +0530, veem v wrote: > > On Mon, 5 Feb 2024 at 17:52, Laurenz Albe > wrote: > > > On Mon, 2024-02-05 at 03:09 +0530, veem v wrote: > > > > In postgresql, Is it possible to partition an existing > nonpartitioned table having data > > > > already residing in it and indexes and constraints defined in it, > without the need of > > > > manually moving the data around, to make it faster? > > > > > > There is no way to do that. > > > > Which means for any such operation we need to create a new partition > table with that > > structure and load that with the data from the existing partition table. > > You could use logical replication to keep the downtime short. > > > Additionally I see a lot of other restrictions like > > 1)When creating indexes on the partition table "concurrently" keywords > are not allowed. > > That is no problem. > You create the index on ONLY the partitioned table, > then you create indexes CONCURRENTLY on the partitions, > then you attach these indexes as partitions to the index you created in > the beginning. > As soon as the last index is attached, the index on the partitioned table > becomes valid. > > > 2)While creating foreign key , it does not allow a "not valid" > clause if the table > > is partitioned. > > Create the foreign key on the partitions; the effect is the same. > > > 3) While creating indexes on this table or running any ALTER command, > the SELECT > >queries running from other processes run longer. Does it take any > lock while > >doing DDL on the base table in postgres? > > I am not sure what the "base table" is. Most DDL always locks the table in > ACCESS EXCLUSIVE mode; that is not specific to partitioned tables. > > Yours, > Laurenz Albe >
Re: Yum Update nothing provides libarmadillo.so.12()(64bit) needed by gdal36-libs-3.6.4-6PGDG.rhel9.x86_64 from pgdg-common
gdal-libs package belongs to PostGIS PostGIS Support Ticket #5664 - https://trac.osgeo.org/postgis/ticket/5664#ticket On Tue, Feb 6, 2024 at 10:12 AM Marcelo Marques wrote: > I appreciate the reply. > We have an internal Satellite that we pull from the EPEL repo. > The Satellite Repos are refreshed daily by the IT department. > It seems then that the EPEL repo has not been refreshed. > I will check with my IT department. > Thanks again for your help. > > On Mon, Feb 5, 2024 at 8:18 AM Devrim Gündüz wrote: > >> Hi, >> >> On Mon, 2024-02-05 at 08:16 -0800, Marcelo Marques wrote: >> > Yes, the EPEL repo is enabled. >> >> (Please keep the list CC'ed) >> >> Armadillo 12 packages *are* in the EPEL repo: >> >> https://dl.fedoraproject.org/pub/epel/9/Everything/x86_64/Packages/a/ >> >> If you have a local mirror, make sure that it is enabled. Also make sure >> that armadillo is not in the excludes list in either dnf.conf or in the >> repo config files. >> >> -HTH >> >> Regards, >> -- >> Devrim Gündüz >> Open Source Solution Architect, PostgreSQL Major Contributor >> Twitter: @DevrimGunduz , @DevrimGunduzTR >> >
Re: Exclude certain application pgaudit logging?
On 2/6/24 10:18 AM, Adrian Klaver wrote: On 2/6/24 10:11 AM, Ron Johnson wrote: Currently, we use Object audit logging to capture all READ access to columns FOO_1, FOO_2 and FOO_3 in table BAR.SCRABBLE. (They are the three columns have PII data.) Using what audit software? Memo to self, read subject line. The problem is that the application legitimately reads these columns thousands of times per day. Thus, the log fills up with meaningless data that swamps any legitimate invalid accesses. How do you know they are legitimate requests? Thus, I'd like to exclude reads from "Postgresql JDBC Driver". (Currently, I filter that out using "grep -v" in a shell script that runs hourly from cron, but I find that unsatisfactory.) -- Adrian Klaver adrian.kla...@aklaver.com
Re: Exclude certain application pgaudit logging?
> On Feb 6, 2024, at 10:11, Ron Johnson wrote: > Thus, I'd like to exclude reads from "Postgresql JDBC Driver". (Currently, I > filter that out using "grep -v" in a shell script that runs hourly from cron, > but I find that unsatisfactory.) pgAudit doesn't currently include filters by application name. Philosophically, I don't think you want to filter on application name, because it's a completely client-supplied string that could easily be spoofed.
Re: Exclude certain application pgaudit logging?
On 2/6/24 10:11 AM, Ron Johnson wrote: Currently, we use Object audit logging to capture all READ access to columns FOO_1, FOO_2 and FOO_3 in table BAR.SCRABBLE. (They are the three columns have PII data.) Using what audit software? The problem is that the application legitimately reads these columns thousands of times per day. Thus, the log fills up with meaningless data that swamps any legitimate invalid accesses. How do you know they are legitimate requests? Thus, I'd like to exclude reads from "Postgresql JDBC Driver". (Currently, I filter that out using "grep -v" in a shell script that runs hourly from cron, but I find that unsatisfactory.) -- Adrian Klaver adrian.kla...@aklaver.com
Re: Yum Update nothing provides libarmadillo.so.12()(64bit) needed by gdal36-libs-3.6.4-6PGDG.rhel9.x86_64 from pgdg-common
I appreciate the reply. We have an internal Satellite that we pull from the EPEL repo. The Satellite Repos are refreshed daily by the IT department. It seems then that the EPEL repo has not been refreshed. I will check with my IT department. Thanks again for your help. On Mon, Feb 5, 2024 at 8:18 AM Devrim Gündüz wrote: > Hi, > > On Mon, 2024-02-05 at 08:16 -0800, Marcelo Marques wrote: > > Yes, the EPEL repo is enabled. > > (Please keep the list CC'ed) > > Armadillo 12 packages *are* in the EPEL repo: > > https://dl.fedoraproject.org/pub/epel/9/Everything/x86_64/Packages/a/ > > If you have a local mirror, make sure that it is enabled. Also make sure > that armadillo is not in the excludes list in either dnf.conf or in the > repo config files. > > -HTH > > Regards, > -- > Devrim Gündüz > Open Source Solution Architect, PostgreSQL Major Contributor > Twitter: @DevrimGunduz , @DevrimGunduzTR >
Exclude certain application pgaudit logging?
Currently, we use Object audit logging to capture all READ access to columns FOO_1, FOO_2 and FOO_3 in table BAR.SCRABBLE. (They are the three columns have PII data.) The problem is that the application legitimately reads these columns thousands of times per day. Thus, the log fills up with meaningless data that swamps any legitimate invalid accesses. Thus, I'd like to exclude reads from "Postgresql JDBC Driver". (Currently, I filter that out using "grep -v" in a shell script that runs hourly from cron, but I find that unsatisfactory.)
Re: Deleting duplicate rows using ctid ?
Am 06.02.24 um 00:32 schrieb David G. Johnston: On Mon, Feb 5, 2024 at 4:09 PM David Gauthier wrote: I want the result to be just 2 recs, one for each dog. My present goto link for this question: https://harshitjain.home.blog/2019/06/17/postgresql-how-to-delete-duplicate-rows-in-postgresql/ David J. postgres=# select * from dogs; dog -- dog1 dog1 dog2 dog2 dog2 dog3 (6 rows) postgres=# select ctid, dog, row_number() over (partition by dog) from dogs ; ctid | dog | row_number ---+--+ (0,1) | dog1 | 1 (0,2) | dog1 | 2 (0,3) | dog2 | 1 (0,4) | dog2 | 2 (0,5) | dog2 | 3 (0,6) | dog3 | 1 (6 rows) postgres=# with ct as (select ctid, dog, row_number() over (partition by dog) from dogs) delete from dogs where ctid in (select ctid from ct where row_number != 1) ; DELETE 3 postgres=# select * from dogs; dog -- dog1 dog2 dog3 (3 rows) postgres=# Regards, Andreas -- Andreas Kretschmer CYBERTEC PostgreSQL Services and Support
Re: Question on partitioning
On Tue, 2024-02-06 at 00:26 +0530, veem v wrote: > On Mon, 5 Feb 2024 at 17:52, Laurenz Albe wrote: > > On Mon, 2024-02-05 at 03:09 +0530, veem v wrote: > > > In postgresql, Is it possible to partition an existing nonpartitioned > > > table having data > > > already residing in it and indexes and constraints defined in it, without > > > the need of > > > manually moving the data around, to make it faster? > > > > There is no way to do that. > > Which means for any such operation we need to create a new partition table > with that > structure and load that with the data from the existing partition table. You could use logical replication to keep the downtime short. > Additionally I see a lot of other restrictions like > 1)When creating indexes on the partition table "concurrently" keywords are > not allowed. That is no problem. You create the index on ONLY the partitioned table, then you create indexes CONCURRENTLY on the partitions, then you attach these indexes as partitions to the index you created in the beginning. As soon as the last index is attached, the index on the partitioned table becomes valid. > 2)While creating foreign key , it does not allow a "not valid" clause if the > table > is partitioned. Create the foreign key on the partitions; the effect is the same. > 3) While creating indexes on this table or running any ALTER command, the > SELECT >queries running from other processes run longer. Does it take any lock > while >doing DDL on the base table in postgres? I am not sure what the "base table" is. Most DDL always locks the table in ACCESS EXCLUSIVE mode; that is not specific to partitioned tables. Yours, Laurenz Albe
Re: Log pre-master keys during TLS v1.3 handshake
> On 5 Feb 2024, at 22:38, Максим Чистяков wrote: > > Is there a way to save the pre-master keys which are encrypted TLS handshake > between PostgreSQL server and psql client due to a TLS handshake? > For example, in a Chrome you can save those keys due to connecting through > HTTPS with option --ssl-key-log-file or an environment variable SSLKEYLOGFILE > (https://knowledgebase.paloaltonetworks.com/KCSArticleDetail?id=kA14u00wkvECAQ=en_US%E2%80%A9). > I need the similar feature, at least in psql client (ideally, on the > postgresql server side too). > > Why I need this: > I'm debugging TLS connection to postgres from a rust application, used > postgres-native-tls library. A psql client makes a successful TLS v1.3 > connection, but my based on postgres-native-tls client fails with an "error > performing TLS handshake" message. I want to dump tcp trafic, and analyze it > in the Wireshark, what exactly certificates exchanged between the psql and > Postgresql (succeeded TLS session), then between postgres-native-tls and > postgres, and then to compare them. Buuut... to view the certificates in > Wireshark, you need the TLS pre-master keys to decrypt the Encrypted > Extensions packets. There is no such thing, adding it yourself and debug your application using a custom build is probably your best option. -- Daniel Gustafsson
Re: Unused indexes
On Mon, Feb 5, 2024 at 9:54 PM Greg Sabino Mullane wrote: > The pg_stat_*_tables tables idx_* columns has accumulated usage since the >> last time you started the postmaster. >> > > Actually, those persist at restart - you can use > > select datname, stats_reset from pg_stat_database; > > to see when/if they were reset. You can look for zero/low entries in > pg_stat_user_indexes.idx_scan to find unused indexes. Note that replicas > maintain their own stats, so checking only the primary may cause a false > positive. > > >> I sql server we have this option to disable it and need to rebuild it to >>> ensemble it >>> >> >> Sadly, PG does not have ALTER INDEX ... DISABLE;. >> > > Not really sure what the purpose of that is in sql server, > To tell the system to stop using a specific index without having to drop the index. Its only purpose is to make the DBA's life easier. IMNSHO, that's an excellent reason to have such a feature. > but Ron is correct, we have nothing equivalent. General usage in Postgres > is to drop the index if it is unused. If you need to create it again, easy > enough with CREATE INDEX CONCURRENTLY. > Unless it's blocked by existing readers. I've seen that more than a few times. > Keeping your schema changes in a VCS (e.g. git) is a good way to document > when and why the index was dropped. I suppose in a pinch you could keep the > old index around by sticking it in a table comment. > The ALTER INDEX ... DISABLE / ALTER INDEX ... REBUILD pair ensures that Mistakes Were Not Made. You *can't* make the mistake of re-creating an index incorrectly if you didn't actually drop the index.
Log pre-master keys during TLS v1.3 handshake
Is there a way to save the pre-master keys which are encrypted TLS handshake between PostgreSQL server and psql client due to a TLS handshake? For example, in a Chrome you can save those keys due to connecting through HTTPS with option --ssl-key-log-file or an environment variable SSLKEYLOGFILE ( https://knowledgebase.paloaltonetworks.com/KCSArticleDetail?id=kA14u00wkvECAQ=en_US%E2%80%A9 ). I need the similar feature, at least in psql client (ideally, on the postgresql server side too). Why I need this: I'm debugging TLS connection to postgres from a rust application, used postgres-native-tls library. A psql client makes a successful TLS v1.3 connection, but my based on postgres-native-tls client fails with an "error performing TLS handshake" message. I want to dump tcp trafic, and analyze it in the Wireshark, what exactly certificates exchanged between the psql and Postgresql (succeeded TLS session), then between postgres-native-tls and postgres, and then to compare them. Buuut... to view the certificates in Wireshark, you need the TLS pre-master keys to decrypt the Encrypted Extensions packets.
Problem managing slots in Patroni
Hi, I use patroni version 3.2.1. There is a point that I do not understand in the slots management with Patroni. Patroni creates a slot automatically on primary node when there is a standby attached, although this slot does not belong to the patroni configuration. How to prevent the automatic creation of this slot? Thanks for helping Here is Patroni configuration: slots: barman_x: type: physical barman_y: type: physical logic_slot: database: test plugin: pgoutput postgresql: use_pg_rewind: true use_slots: true
