Re: oldest WAL files not removed

At Thu, 2 Sep 2021 08:21:37 +0200, wrote in > Hy Kyotaro, > > Thanks for this explanation. I joined the files to be complete. Thanks, it is informative. It looks like this if sorted in the file-name order. 01/09/2021 12:1516 777 216 0001008E0061.deleted 01/09/2021 16:35

No xmin in pg_database

Hi! I'm Alexander from DataGrip. We actively use xmin's from pg_catalog tables to incrementally synchronize our database model. We have a small number of users that do not have xmin in pg_database (we've asked them to try `select xmin from pg_database` and got `column xmin does not exist`). There i

Re: No xmin in pg_database

On Thu, 2021-09-02 at 08:50 +0300, Alexander Kass wrote: > Hi! I'm Alexander from DataGrip. > We actively use xmin's from pg_catalog tables to incrementally > synchronize our database model. > We have a small number of users that do not have xmin in pg_database > (we've asked them to try `select xm

Re: No xmin in pg_database

Laurenz Albe writes: > On Thu, 2021-09-02 at 08:50 +0300, Alexander Kass wrote: >> We have a small number of users that do not have xmin in pg_database >> (we've asked them to try `select xmin from pg_database` and got >> `column xmin does not exist`). > All PostgreSQL tables have "xmin", and all

Querying a table with jaccard similarity with 1.6 million records take 12 seconds

Hi Team We have encountered a problem in our testing environment. I have a scenario where I am running a similarity match for an address I have created a table with following number of records 1603423 We are using pg_similarity extension in postgresql version is 13. And I have created GIN index

Re: No xmin in pg_database

On Thu, 2021-09-02 at 12:10 +0300, Alexander Kass wrote: > As for xmin usage, we have a working scheme. We fetch objects based on > dbage(xid), starting from the oldest uncommitted transaction of > previous synchronization. > Do you think it does not work? I don't know what exactly you are doing,

Re: datfrozenxid not dropping after vacuum

Interestingly enough, I hopped on the database system this morning and found the `datfrozenxid` dropped back down below `autovacuum_freeze_max_age` around 0200 local time (roughly 18 hours after the fact). Looking through the Postgresql logs I don't see anything standing out at that time. I still

jsonpath duplication result

Hi, I am looking at the documentation and how jsonpath works and I fall on the following example: (On PostgreSQL 12.4, package for Debian 12.4-3): SELECT jsonb_path_query(j, '$.**.a.**.b') FROM (SELECT '{"a":{"a":{"b":{"c":3}}}'::jsonb as j) as T; The result: jsonb_path_query

Re: datfrozenxid not dropping after vacuum

On 2021-Sep-01, Matthew Tice wrote: > Hi Alvaro, thanks for the quick reply. Hi. Glad to hear that your problem is now behind. > I'm scheduled to do my patching maintenance at the end of this month - > but at this point I don't think I'm going to make it. > > Other than patching, is there a wor

Re: oldest WAL files not removed

Dear Oliver, Kindly do not remove any WAL file from pg_wal. You should consider checking out following settings in the postgresql.conf file. 1) wal_keep_segments - This setting enable retention of last this number of files. e.g. if this parameter is set to 256, last 256 files will not be deleted.

Re: Issue with a query while running on a remote host

Hi David/Karsten, Thank you for your response. This helped me. This thread can be closed. Regards, Ninad Shah On Tue, 31 Aug 2021 at 13:26, David G. Johnston wrote: > On Tuesday, August 31, 2021, Ninad Shah wrote: > >> Hi Karsten, >> >> I apologize for the delayed response. >> >> There is n

Re: jsonpath duplication result

On Thu, Sep 2, 2021 at 6:06 AM Charles Paperman wrote: > I also wonder if it is problematic to have simple small query like that > that can be design to make the database work endlessly? > > No, because the "simple small" part of that doesn't matter. A user with access has various ways to affect

Re: No xmin in pg_database

>From the version string I can suggest that it is vanilla postgres from The PostgreSQL Global Development Group (PGDG). But we will ask the user. I've checked PG source code, it checks system attrs (like xmin) existence in some cache. Maybe that is really corruption. Does postgres itself rely on x

memory consumption of memory for postgres db container

Hi all, We did a stability test for our product, where we continuously inserting data into postgres tables. We did this for three days continuously. Memory utilization of our Postgres container continuously increasing. [cid:image001.jpg@01D7A035.D9017060] Can you please provide us the clue why

Re: Querying a table with jaccard similarity with 1.6 million records take 12 seconds

Hi Bala, Are your statistics updated? Also, have you used the gin operator(gin_similarity_ops) correctly? It is fetching just 6 records out of a million, hence, it should not go for bitmap index scan. As bitmap index scan loads a complete index, and access relevant pages from the table later by b

Re: calling store procedure / insert statement never complete

Kindly consider checking tcp_keepalive in PostgreSQL as well as OS kernel. By default, it is 2 hours(7200). Regards, Ninad Shah On Thu, 2 Sept 2021 at 11:43, Trang Le wrote: > Hi Mladen, > > I missed previous emails, so I am creating a new one. > > I think the problem is coming from front-end

Re: memory consumption of memory for postgres db container

No need to worry. This isn't really a concern. No operating system releases memory upon using it, but caches it once the program finishes. This actually saves time as memory blocks can be allocated in less time than loading new blocks. However, if it's required to release memory, it can be perform

RE: memory consumption of memory for postgres db container

Can you please suggest few commands? Regards Tarkeshwar From: Ninad Shah Sent: Thursday, September 2, 2021 8:30 PM To: M Tarkeshwar Rao Cc: pgsql-gene...@postgresql.org Subject: Re: memory consumption of memory for postgres db container No need to worry. This isn't really a concern. No operat

Re: Querying a table with jaccard similarity with 1.6 million records take 12 seconds

This is showing many false positives from the index scan that get removed when the actual values are examined. With such a long search parameter, that does not seem surprising. I would expect a search on "raj nagar ghaziabad 201017" or something like that to yield far fewer results from the index s

Upgrade 9.5 cluster on Ubuntu 16.04

Hello, I've got 2 VMs with Postgresql 9.5 cluster with streaming replication. VMs have Ubuntu 16.04. I would like to upgrade Ubuntu and Postgresql to newer versions. Ubuntu 16.04 supports upgrading to 18.04. What is the safest way to upgrade Postgresql cluster along with it? The database is not b

Re: jsonpath duplication result

Le 09/02 06:55, David G. Johnston a écrit : > On Thu, Sep 2, 2021 at 6:06 AM Charles Paperman > wrote: > > I also wonder if it is problematic to have simple small query like that > that can be design to make the database work endlessly? > > > > No, because the "simple small" part of th

Re: Querying a table with jaccard similarity with 1.6 million records take 12 seconds

HI Ninad Thanks for your reply. If bitmap index should not be used. Do i need to disable it for the time being and carry out the test. The documentation in pg_similarity shows that index can be created on text column using gin with gin_similarity_ops. The same way the index is created like CREATE

Re: Upgrade 9.5 cluster on Ubuntu 16.04

On 9/2/21 10:38 AM, Vano Beridze wrote: Hello, I've got 2 VMs with Postgresql 9.5 cluster with streaming replication. VMs  have Ubuntu 16.04. I would like to upgrade Ubuntu and Postgresql to newer versions. Ubuntu 16.04 supports upgrading to 18.04. What is the safest way to upgrade Postgresql cl

Re: Upgrade 9.5 cluster on Ubuntu 16.04

Logical way – dump/restore. Bringing PGDATA physically may lead to corrupted indexes due to glibc version changes. 16.04 -> 18.04 shouldn't cause it, but it may. You can check btree index with amcheck and GIN indexes with a patched version of it (some backporting would be needed). You can find exa

Re: Upgrade 9.5 cluster on Ubuntu 16.04

> What repo are you getting Postgres from, Ubuntu or PGDG? Ubuntu On Thu, Sep 2, 2021 at 10:13 PM Adrian Klaver wrote: > On 9/2/21 10:38 AM, Vano Beridze wrote: > > Hello, > > > > I've got 2 VMs with Postgresql 9.5 cluster with streaming replication. > > VMs have Ubuntu 16.04. > > I would like

Re: Upgrade 9.5 cluster on Ubuntu 16.04

On 9/2/21 11:34 AM, Vano Beridze wrote: > What repo are you getting Postgres from, Ubuntu or PGDG? Ubuntu So you are going to get version 10. Is that what you want? If you want to move to something newer then this maybe the time to move to the PGDG repos, as they allow you to install any of

Re: Upgrade 9.5 cluster on Ubuntu 16.04

I just want to upgrade to the OS and Postgres that is supported. If I just stop the service on both VMs and upgrade Ubuntu, then start Postgres, will it work or I should go with clean install and dump/restore? On Thu, Sep 2, 2021 at 10:41 PM Adrian Klaver wrote: > On 9/2/21 11:34 AM, Vano Beridz

Re: Upgrade 9.5 cluster on Ubuntu 16.04

On Thursday, September 2, 2021, Vano Beridze wrote: > I just want to upgrade to the OS and Postgres that is supported. > If I just stop the service on both VMs and upgrade Ubuntu, then start > Postgres, will it work or I should go with clean install and dump/restore? > >> >> Personally I find the

Re: memory consumption of memory for postgres db container

You may checkout using "free -m" and may see how much memory is cache. If you are using RHEL, you may clear cache using below commands. sync; echo 1 > /proc/sys/vm/drop_caches; sync Though this is going to release some memory from the cache, it may fill up the cache again. Regards, Ninad Shah

Re: Querying a table with jaccard similarity with 1.6 million records take 12 seconds

Try the pg_tgrm extension. It is a rich set of operators. Regards, Ninad Shah On Thu, 2 Sept 2021 at 23:39, balasubramanian c r wrote: > HI Ninad > > Thanks for your reply. > If bitmap index should not be used. Do i need to disable it for the time > being and carry out the test. > > The docume

Re: Querying a table with jaccard similarity with 1.6 million records take 12 seconds

I see an issue with the operator. GIN index is capable of performing index scan and index-only scan. Regards, Ninad Shah On Thu, 2 Sept 2021 at 20:57, Michael Lewis wrote: > This is showing many false positives from the index scan that get removed > when the actual values are examined. With su

Re: Querying a table with jaccard similarity with 1.6 million records take 12 seconds

Michael Lewis writes: > This is showing many false positives from the index scan that get removed > when the actual values are examined. With such a long search parameter, > that does not seem surprising. I would expect a search on "raj nagar > ghaziabad 201017" or something like that to yield far

Re: Upgrade 9.5 cluster on Ubuntu 16.04

On 9/2/21 11:55 AM, Vano Beridze wrote: I just want to upgrade to the OS and Postgres that is supported. If I just stop the service on both VMs and upgrade Ubuntu, then start Postgres, will it work or I should go with clean install and dump/restore? Prudent behavior before you do any upgrading

Re: Upgrade 9.5 cluster on Ubuntu 16.04

Forgot to mention: the same concerns about possible index corruption are relevant for the pg_upgrade option too (because it doesn't rebuild indexes). So, I'd definitely choose dump/restore if the database is small. In the case of pg_upgrade, I would rebuild all the indexes during the maintenance wi

Re: Querying a table with jaccard similarity with 1.6 million records take 12 seconds

HI Tom/Ninad My bad I didn't explain my use case properly. The use case is to find the best string similarity for a given address against the list of addresses in the table. Initially I tried a similarity function provided by the pg_trgm extension. But the similarity scores were not satisfactory. L

Query takes around 15 to 20 min over 20Lakh rows

Hi , *Please help in optimizing this query. I need to actually generate reports daily using this query.. It takes almost 15 to 20 min to execute this query due to joins.. * *Here common_details is a jsonB column.* SELECT T.order_id, ( ( T.common_details -> 'commonDetails' :: text ) -> 'bun

Re: Query takes around 15 to 20 min over 20Lakh rows

What is T and how many rows are in there? How many rows in task_history? What indexes exist? Are you confident you want 2 million rows in that result set? What version is this on? What pg_settings have been changed from defaults?

Re: Query takes around 15 to 20 min over 20Lakh rows

On Thu, Sep 2, 2021 at 3:16 PM Shubham Mittal wrote: > Hi , > > *Please help in optimizing this query. I need to actually generate reports > daily using this query.. It takes almost 15 to 20 min to execute this query > due to joins.. * > *Here common_details is a jsonB column.* > > SELECT T.order

Re: Query takes around 15 to 20 min over 20Lakh rows

"David G. Johnston" writes: > On Thu, Sep 2, 2021 at 3:16 PM Shubham Mittal > wrote: >> *Please help in optimizing this query. I need to actually generate reports >> daily using this query.. It takes almost 15 to 20 min to execute this query >> due to joins.. * > Use jsonb_populate_recordset (or

gen_random_uuid key collision

Hello, I'm hoping someone might be able to shed a little light on a strange situation I encountered recently. I work with a postgres instance which has dozens (probably hundreds) of tables which each have a column defined as "uuid primary key default gen_random_uuid()". Most of the time this is f

Re: gen_random_uuid key collision

On 9/2/21 4:25 PM, jesusthefrog wrote: Hello, I'm hoping someone might be able to shed a little light on a strange situation I encountered recently. Most of the time this is fine, but one specific table has recently started repeatedly having inserts fail because of a unique constraint viola

Re: gen_random_uuid key collision

On Thu, Sep 2, 2021 at 7:35 PM Adrian Klaver wrote: > What is the table schema as returned by \d in psql? > > The tables are in various schemas; that one is in one called "access_control", but we always set the search path explicitly to (in this case) "access_control, public". Anyway, if if were

Re: gen_random_uuid key collision

jesusthefrog writes: > I work with a postgres instance which has dozens (probably hundreds) of > tables which each have a column defined as "uuid primary key default > gen_random_uuid()". > Most of the time this is fine, but one specific table has recently started > repeatedly having inserts fail

Re: gen_random_uuid key collision

On 9/2/21 4:38 PM, jesusthefrog wrote: On Thu, Sep 2, 2021 at 7:35 PM Adrian Klaver > wrote: What is the table schema as returned by \d in psql? The tables are in various schemas; that one is in one called But only one is generating errors. Schema refe

Re: gen_random_uuid key collision

On Thu, Sep 2, 2021 at 7:47 PM Tom Lane wrote: > > BTW, are you *entirely* certain that your application never inserts > non-default values into that column? > > regards, tom lane > Yes, I double checked that we never attempt to bind a value for that column. I'll have a g

Re: gen_random_uuid key collision

On Thu, Sep 2, 2021 at 8:05 PM Adrian Klaver wrote: > But only one is generating errors. Schema refers to an object's > definition as well as a namespace. So what does: > > \d > > return? > I see what you mean. I don't have access to the instance at the moment so I'd have to take a look tomorro

Re: gen_random_uuid key collision

On 9/2/21 5:38 PM, jesusthefrog wrote: On Thu, Sep 2, 2021 at 8:05 PM Adrian Klaver > wrote: But only one is generating errors. Schema refers to an object's definition as well as a namespace. So what does: \d return? I see what you mean. I

Re: gen_random_uuid key collision

On Thu, Sep 2, 2021 at 4:48 PM Tom Lane wrote: > That is pretty weird, all right. The only idea that comes to mind > immediately is that maybe that table's pkey index is corrupt and needs > to be reindexed. This isn't a great theory, because I don't see why > a corrupt index would lead to bogus

Re: gen_random_uuid key collision

> On Sep 2, 2021, at 4:25 PM, jesusthefrog wrote: > > Anyone have any thoughts on this? I agree with Peter's suggestion upthread to run amcheck on the index, but if that comes back with no corruption, can you verify that there are no rules or triggers that might cause multiple copies of the

is there any memory leak with postgres version(12.x) - https://stackoverflow.com/questions/3849543/force-postgresql-to-release-allocated-memory

Hi All, Is there any memory leak in Postgres version 12.7 or any other 12.x? It is mentioned in https://stackoverflow.com/questions/3849543/force-postgresql-to-release-allocated-memory. Regards Tarkeshwar

Re: memory consumption of memory for postgres db container

On 02.09.2021 16:35, M Tarkeshwar Rao wrote: We did a stability test for our product, where we continuously inserting data into postgres tables. We did this for three days continuously. Memory utilization of our Postgres container continuously increasing. I am not certain whether it is rela