Re: PostgreSQL 14.4 ERROR: out of memory issues

Hello, po 18. 7. 2022 v 21:04 odesílatel Tom Lane napsal: > =?UTF-8?B?QWxlxaEgWmVsZW7DvQ==?= writes: > > postgres=# show shared_buffers ; > > shared_buffers > > > > 10GB > > (1 row) > > Oh! In that case, there is absolutely nothing to see here. > This chunk: > > > 7fd9b0

Re: postgis

right. so I scratch the debian vm, install a centos 7 and within minutes I have a postgres 12 with postgis 3.0.4 running. so easy. regards. Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Wed, Jul 20, 2022 at 7:27 PM Imre Samu wrote: > > I would expect the 35 packages implied by

Re: Patroni & PostgreSQL issue

Hi Uma, If i understand your scenario correct, after failover, Patroni created deleted files on old primary by replciating from New primary? If that is correct, i would recommend to check lag between new primary and old primary(now slave). if it is zero then we are good to perform failover. Reg

Re: Batch process

On 7/20/22 11:56, Ron wrote: How will DELETE WHERE sales_id IN (...); , given that test_old has no index? Of course, we don't know if there's an index on sales.bill_date, since OP's only response has been another "tell me how to do it". This awaits more information. At this point it is abo

Re: Batch process

How will DELETE WHERE sales_id IN (...); , given that test_old has no index? Of course, we don't know if there's an index on sales.bill_date, since OP's only response has been another "tell me how to do it". On 7/20/22 13:32, Adrian Klaver wrote: On 7/20/22 11:16 AM, Ron wrote: But the first

Re: plan for function returning table combined with condition

Thierry Henrio writes: > I made a function out of this sql: > create or replace function expand_shop_opening_times() returns table(id > int, name text, day int, startt time, endt time) > as $$ > select id, name, day::int, (value -> 0 ->> 0)::time as startt, (value -> 0 > ->> 1)::time as endt from

Re: Batch process

On 7/20/22 11:16 AM, Ron wrote: But the first thing he does is drop test_old. Then: Create table test_old as select * from sales where bill_date-interval '1 year'; At that point you could do either: Delete from sales where sales_id in (select sales_id from test_old); or DELETE FROM sales

Re: Batch process

But the first thing he does is drop test_old. On 7/20/22 09:52, Adrian Klaver wrote: On 7/20/22 01:28, Ron wrote: On 7/20/22 00:08, Rama Krishnan wrote: Hi All, I am doing purge activity my sales table contains 5M records I am going to delete more than 1 year data (which was 3M) records so i

plan for function returning table combined with condition

Hello, I have a table with a jsonb row, opening_times, that I need to expand for later processing. jsonb is an object, like so {"1": [["06:00:00", "23:59:59"]], ...}. select id, name, day::int, (value -> 0 ->> 0)::time as startt, (value -> 0 ->> 1)::time as endt from ( select s.id, s.name, j.*

Re: Batch process

On 7/20/22 9:38 AM, Rama Krishnan wrote: Reply to list also Ccing list. Hi ALL, I have created the batch wise query but the variable is not working in the delete. create or replace function sports_sale() returns void as $$ declare    counter integer := 0;    row_count integer :=0;    sta

Re: postgis

> I would expect the 35 packages implied by the version policies of those two projects. Based on my docker-postgis support - the "geos" is also important. Now Bullseye(Debian11) geos version is 3.9 - and this is likely to continue until the end of the cycle ( so no upgrade expected to 3.10,3.11)

Re: postgis

On Wed, Jul 20, 2022 at 9:21 AM Imre Samu wrote: > > My general impression is that the packaging, at least for Debian, > > doesn’t actually understand how the PostGIS project handles versioning > support. > > But i may be missing something > > "PostGIS Pre-built Binary Distributions for various O

Re: postgis

On Wed, Jul 20, 2022 at 9:16 AM Adrian Klaver wrote: > On 7/20/22 08:55, David G. Johnston wrote: > > > That is what I found, and nowhere on the web can I find confirmation of > > the existence of the package: > > > > 3.0.3+dfsg-2.pgdg+1 > > https://apt.postgresql.org/pub/repos/apt/pool/main/p/po

Re: postgis

> My general impression is that the packaging, at least for Debian, > doesn’t actually understand how the PostGIS project handles versioning support. > But i may be missing something "PostGIS Pre-built Binary Distributions for various OS" ---> https://trac.osgeo.org/postgis/wiki/UsersWikiPackages

Re: postgis

On 7/20/22 08:55, David G. Johnston wrote: That is what I found, and nowhere on the web can I find confirmation of the existence of the package: 3.0.3+dfsg-2.pgdg+1 https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgis/ See my most recent reply to Marc on how to get at it. Even th

Re: postgis

On 7/20/22 07:21, Marc Millas wrote: Thanks for your answer. I would like to avoid compiling as much as possible. I know that postgis 3.2.1 is available and does install without pb. but.. That db run an app which is very long to test, so I need to stick to a postgis 3.0.x Alright as close as c

Re: postgis

On Wed, Jul 20, 2022 at 8:36 AM Adrian Klaver wrote: > On 7/20/22 07:57, David G. Johnston wrote: > > On Wednesday, July 20, 2022, Marc Millas > > wrote: > > > My general impression is that the packaging, at least for Debian, > > doesn’t actually understand how the

Re: postgis

On 7/20/22 07:57, David G. Johnston wrote: On Wednesday, July 20, 2022, Marc Millas > wrote: My general impression is that the packaging, at least for Debian, doesn’t actually understand how the PostGIS project handles versioning support.  But i may be missing so

Re: Batch process

On 7/20/22 09:26, Rama Krishnan wrote: Hi Adrian, Thanks for the update. Is it possible to achieve 1 records deletion on every iteration because my original table contains 5M records during the deletion process it consumes more cpu and resources. See here

Re: Batch process

Hi Adrian, Thanks for the update. Is it possible to achieve 1 records deletion on every iteration because my original table contains 5M records during the deletion process it consumes more cpu and resources. On Wed, 20 Jul, 2022, 20:37 Adrian Klaver, wrote: > On 7/19/22 22:08, Rama Krishn

Re: Batch process

On 7/19/22 22:08, Rama Krishnan wrote: Hi All, I am doing purge activity my sales table contains 5M records I am going to delete more than 1 year data (which was 3M) records so it was running more so I want to do batch wise deletion through plsql  created or replace function data_purge()

Re: postgis

On Wednesday, July 20, 2022, Marc Millas wrote: > Thanks for your answer. > I would like to avoid compiling as much as possible. > I know that postgis 3.2.1 is available and does install without pb. but.. > That db run an app which is very long to test, so I need to stick to a > postgis 3.0.x >

Re: Batch process

On 7/20/22 01:28, Ron wrote: On 7/20/22 00:08, Rama Krishnan wrote: Hi All, I am doing purge activity my sales table contains 5M records I am going to delete more than 1 year data (which was 3M) records so it was running more so I want to do batch wise deletion through plsql  created or

Re: postgis

Thanks for your answer. I would like to avoid compiling as much as possible. I know that postgis 3.2.1 is available and does install without pb. but.. That db run an app which is very long to test, so I need to stick to a postgis 3.0.x regards, Marc MILLAS Senior Architect +33607850334 www.mokadb

Re: postgis

Can you try compiling from source: https://postgis.net/source/ postgis 3.2.1 is OK. postgresql & postgis version info: https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS if you already installed check postgis version: https://postgis.net/docs/PostGIS_Version.html On Wed, Jul 20, 2022

Re: postgis

??? I didnt get any error, as I dont know the name of the package to be installed !!! my question was, and still is: "Hi, I would like to install postgis 3.04 on a debian 11. digging into various web sites, I didnt found the name of that packet. can someone help ?" so.. the only info on the deb

Re: operator does not exist: text = bytea

Karthik K L V writes: > We have a query with bind value which sometimes gets resolved to null (no > data) depending on the application scenario. > The datatype of the bindvalue and the corresponding column is String. > The same query executes fine when the value of the bindvalue is populated. > Co

Re: postgis

This long drama is about *POSTGIS*, not Postgresql.  What error do you get when trying to install *POSTGIS*? On 7/20/22 08:26, Marc Millas wrote: ??? I did describe precisely what I did: On 7/19/22 2:09 PM, Marc Millas wrote: > I did run each step of the script and did install a post

Re: postgis

??? I did describe precisely what I did: On 7/19/22 2:09 PM, Marc Millas wrote: > > I did run each step of the script and did install a postgres 12.11. > > then destroyed the instance created by the script, and, then > > pg_createcluster a new one, which is running fine. no error messages.. Post

Paging through table one row at a ttime

I am running postgres 13 under CentOS 7. I have a need to be able to page through a table one row at a time, possibly using pspg (or other tool) allowing me to move in either direction in the table one single row at a time. Ideally only the columns in the selected row should be visible, even bet

Re: operator does not exist: text = bytea

Hi depesz, Thanks for your reply. But, this issue is happening only when the bind value of the query resolves to null. I am not trying to compare text to bytes. And the same query works fine when the bind value gets resolves to some String. So, looking for an option which can tell Postgres Engine

Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity

On 19/7/22 20:32, Adrian Klaver wrote: On 7/19/22 10:26 AM, Achilleas Mantzios wrote: Thank you Adrian! Actually thank: https://sqlformat.darold.net/ Στις 19/7/22 18:36, ο/η Adrian Klaver έγραψε: On 7/19/22 03:38, Achilleas Mantzios wrote: I reformatted queries to see thing better.

Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity

On 19/7/22 20:31, David G. Johnston wrote: On Tuesday, July 19, 2022, Achilleas Mantzios wrote: Thanks David Στις 19/7/22 17:19, ο/η David G. Johnston έγραψε: On Tuesday, July 19, 2022, Achilleas Mantzios wrote: ERROR:  cannot convert infinity to numeric -- h

Re: operator does not exist: text = bytea

On Wed, Jul 20, 2022 at 03:02:13PM +0530, Karthik K L V wrote: > *Caused by: org.postgresql.util.PSQLException: ERROR: operator does not > exist: text = bytea Hint: No operator matches the given name and argument > types. You might need to add explicit type casts. Position: 1037* > Could you plea

Patroni & PostgreSQL issue

Hi All, This is regarding the Postgres HA working with patroni in 3 node setup, we have an issue with the primary because a few database files were deleted manually so performed a switch over to move the services from primary to secondary with patroni, post the switchover was deleted file was r

Re: operator does not exist: text = bytea

Update: Followed this thread PostgreSQL: Re: Null bind variable in where clause and set *transform_null_equals to ON* in the parameter group on the AWS Console. But no luck. We are using Aurora PostgresS

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

Am Wed, Jul 20, 2022 at 09:15:29AM + schrieb Sebastien Flaesch: > Thomas, we already have a similar solution. > The idea is to use the native PostgreSQL SERIAL type. Which does not guarantuee gaplessness. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

operator does not exist: text = bytea

Hi Team, I am getting the below error while executing a Select query using Spring DataJPA and Hibernate framework in Aurora Postgres SQL. *Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: text = bytea Hint: No operator matches the given name and argument types. You

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

Thomas, we already have a similar solution. The idea is to use the native PostgreSQL SERIAL type. Seb From: Thomas Kellerer Sent: Wednesday, July 20, 2022 8:56 AM To: pgsql-general@lists.postgresql.org Subject: Re: Concurrent INSERT statements with RETURNING claus

Re: Batch process

On 7/20/22 00:08, Rama Krishnan wrote: Hi All, I am doing purge activity my sales table contains 5M records I am going to delete more than 1 year data (which was 3M) records so it was running more so I want to do batch wise deletion through plsql  created or replace function data_purge()

Re: postgis

You've never shown us *exactly what you did*, along with any *error messages*. On 7/19/22 22:07, Marc Millas wrote: Postgres installed, but not postgis.. which is why I need some help... Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Jul 19, 2022 at

RE: Proposed Translations of Updated Code of Conduct Policy

Thank you both. I will make these changes. Lucie From: Guillaume Lelarge Sent: 19 July 2022 08:50 To: Stefan Fercot Cc: Lucie Šimečková; pgsql-general@lists.postgresql.org

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

Hi Gavan. On Wed, 20 Jul 2022 at 00:10, Gavan Schneider wrote: > On 20 Jul 2022, at 4:08, Francisco Olarte wrote: > As a remark, in Spain bill numbers need to be gapless increasing. I > have done it with > > One answer to this problem has been around for a while, and my version is > shown b