Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

Sebastien Flaesch schrieb am 19.07.2022 um 18:50: > Tom, > > /If that's the behavior you want, you can build it out of standard SQL > facilities (e.g. update a one-row table). > / > > Can you elaborate please? > > Do you mean the code should use an UPDATE on a one-row table to acquire a >

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

Changing a bit the CASE/WHEN logic in the RETURNING clause solves the issue when concurrently inserting rows without specifying explicitly a value for the serial column (INSERTs specifying values for the serial column are seldom used by concurrent programs inserting many rows): insert

Re: Migrating from Oracle - Implicit Casting Issue

Hi David, Thanks for the quick response. Making sure I got it right - U mean Postgres DB Server when you say server right? IIUC, by configuring this property, the driver will not set the type and leave it to the Postgres DB Server to map it to the appropriate type. Will this have any performance i

Batch process

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() returns void as$$ Declare Begin Drop table t

Re: postgis

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 11:43 PM Adrian Klaver wrote: > 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

Re: citext on exclude using gist

On Tue, Jul 19, 2022 at 4:43 PM Tom Lane wrote: > Jean Carlo Giambastiani Lopes writes: > > I'm trying to use a citext column in the following manner without > success: > > > create extension btree_gist; > > create extension citext; > > create table my_table( > > foo citext, > > bar numr

Re: postgis

> from your message, I understand that for debian 11, I can NOT get any 3.0.x version. I can't see any Postgis 3.0.x version in the default *"http://apt.postgresql.org/pub/repos/apt/ bullseye-pgdg main 12"* repo *root@6b22a4450d93:/# cat /etc/apt/sourc

Re: citext on exclude using gist

Jean Carlo Giambastiani Lopes writes: > I'm trying to use a citext column in the following manner without success: > create extension btree_gist; > create extension citext; > create table my_table( > foo citext, > bar numrange, > primary key (foo, bar), > exclude using gist (foo w

citext on exclude using gist

Hi, I'm trying to use a citext column in the following manner without success: create extension btree_gist; create extension citext; create table my_table( foo citext, bar numrange, primary key (foo, bar), exclude using gist (foo with =, bar with &&) ); is this possible? If so, w

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

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 a sequence ( with cache 1, so I doubt it is much more performant than a single row table, as it has a backing element which needs to be saved ), and just used an

Re: postgis

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. Does this mean you have PostGIS installed now? Marc MILLAS Senior Archite

Re: postgis

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. Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Jul 19, 2022 at 8:42 PM Adrian Klaver wrote: > On

Re: postgis

On 7/19/22 11:19, Marc Millas wrote: It is. I do begin with the postgres site script: sudo sh -c 'echo "debhttp://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' so... Have you run?: sudo apt u

Re: postgis

Hi, from your message, I understand that for debian 11, I can NOT get any 3.0.x version. right ? Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Jul 19, 2022 at 7:20 PM Imre Samu wrote: > > I would like to install postgis 3.04 on a debian 11 > > postgres 12. > > I may use wh

Re: postgis

It is. I do begin with the postgres site script: sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' so... Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Jul 19, 2022 at 6:54 PM Adrian Klaver wro

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

On Tue, 19 Jul 2022 at 18:50, Sebastien Flaesch wrote: >> If that's the behavior you want, you can build it out of standard SQL >> facilities (e.g. update a one-row table). > Can you elaborate please? > Do you mean the code should use an UPDATE on a one-row table to acquire a > lock? That's the

Re: Out Of Memory

On 7/19/22 10:54 AM, ghoostin...@mail.com wrote: Again reply to list also. Ccing list. What should i do and what’s the requirements ———- The link explains what you should do. What requirements are you talking about? This is probably something that the Odoo community will be better able to

Re: Out Of Memory

On 7/19/22 9:56 AM, ghoostin...@mail.com wrote: Please reply to list also. Ccing list > 1) Detail how you are doing migration 1 - I'm Using Tool OpenUpgrade OCA 2 - Creation new db and restore my db on it (36GB and Some table 5m+ rows) 3 - Execution Script Sql to unist

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

On 7/19/22 10:42 AM, Adrian Klaver wrote: On 7/19/22 10:32 AM, Adrian Klaver wrote: On 7/19/22 10:26 AM, Achilleas Mantzios wrote: Have you tried: NULLIF(cept.value, 'inf')::numeric That was a miss. I originally tested this on Postgres 14 and of course it worked. Trying it on Postgres 12

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

On 7/19/22 10:32 AM, Adrian Klaver wrote: On 7/19/22 10:26 AM, Achilleas Mantzios wrote: Have you tried: NULLIF(cept.value, 'inf')::numeric That was a miss. I originally tested this on Postgres 14 and of course it worked. Trying it on Postgres 12 got: select nullif(1.5, 'inf')::numeric;

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

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. AND numrange(ceptl.min_alarm::numeric, c

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

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 < > ach...@matrix.gatewaynet.com> wrote: > >> >> ERROR: cannot convert infinity to numeric >> >> -- has no problem testing aga

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

Thank you Adrian! Στις 19/7/22 18:36, ο/η Adrian Klaver έγραψε: On 7/19/22 03:38, Achilleas Mantzios wrote: I reformatted queries to see thing better. -- still has problem testing the range select it.id ,cept.value::numeric as val, numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric

Re: restore question

Many thanks folks, the last message by Adrian gave me the relatively simple prescription. Sincerely, Dr. Ronald D. Haynes Professor, Department of Mathematics and Statistics Chair, MSc and Phd Scientific Computing Programs Memorial University of Newfoundland We acknowledge that the lands on whi

Re: postgis

> I would like to install postgis 3.04 on a debian 11 > postgres 12. > I may use whatever repo. > I install postgres from postgresql.org... As I see - from the official postgresql.org debian11 repo, you can only install the "postgresql-12-postgis-3" package ( now: Postgis=3.2.1 ) docker run -it

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

Στις 19/7/22 17:23, ο/η Tom Lane έγραψε: "David G. Johnston" writes: On Tuesday, July 19, 2022, Achilleas Mantzios wrote: ERROR: cannot convert infinity to numeric The column cept.value contains an infinity. I see nothing unusual in any of these queries given that fact. If you try to ca

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

Thanks David Στις 19/7/22 17:19, ο/η David G. Johnston έγραψε: On Tuesday, July 19, 2022, Achilleas Mantzios wrote: ERROR:  cannot convert infinity to numeric -- has no problem testing against infinity select it.id ,cept.value::numeric as val, numrange(ceptl.

Re: postgis

On 7/19/22 9:51 AM, Marc Millas wrote: just... nothing ! I thought you said you used the Postgres community repo to install Postgres. Is that not the case? Marc MILLAS Senior Architect +33607850334 www.mokadb.com -- Adrian Klaver adrian.kla...@aklaver.com

Re: restore question

On 7/19/22 9:44 AM, Ronald Haynes wrote: Thanks Ray, running pSql -f backup-file.sql psql: error: FATAL: database "rhaynes74" does not exist No that is expected as you did not specify a database to connect to using -d . In that case psql uses the OS user name you are running the command a

Re: postgis

just... nothing ! Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Jul 19, 2022 at 6:36 PM Adrian Klaver wrote: > On 7/19/22 9:01 AM, Marc Millas wrote: > > Thanks Adrian > > > > still, I see a bunch of files, and nothing that can be installed via > > apt-get install. > > The

Re: restore question

On 7/19/22 9:49 AM, Benedict Holland wrote: You have to create the database to restore to. Then pg_resrore should work. The OP is not using pg_restore but psql as the dump file is plain text. Thanks, Ben -- Adrian Klaver adrian.kla...@aklaver.com

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

Tom, If that's the behavior you want, you can build it out of standard SQL facilities (e.g. update a one-row table). Can you elaborate please? Do you mean the code should use an UPDATE on a one-row table to acquire a lock? Seb

Re: restore question

You have to create the database to restore to. Then pg_resrore should work. Thanks, Ben On Tue, Jul 19, 2022, 12:44 PM Ronald Haynes wrote: > Thanks Ray, running > > pSql -f backup-file.sql > > > > psql: error: FATAL: database "rhaynes74" does not exist > > Which seems odd since rhaynes74 is a

Re: restore question

Thanks Ray, running pSql -f backup-file.sql psql: error: FATAL: database "rhaynes74" does not exist Which seems odd since rhaynes74 is a user not a database name in the file. Sincerely, Dr. Ronald D. Haynes Professor, Department of Mathematics and Statistics Chair, MSc and Phd Scientific Com

Re: postgis

On 7/19/22 9:01 AM, Marc Millas wrote: Thanks Adrian still, I see a bunch of files, and nothing that can be installed via apt-get install. The only "things" big enough to contain a postgis distrib, like https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgis/postgis_3.0.3%2Bdfsg.orig.tar.

Re: Getting the table ID

Hi, guys, On Tue, Jul 19, 2022 at 4:42 AM Walter Dörwald wrote: > > On 19 Jul 2022, at 5:10, Igor Korot wrote: > > Hi, guys, > > In the database theory each table is identified as "schema_name"."table_name". > > When I tried to look at how to get the table id inside the PostgreSQL, > I saw that I

Re: About limitation of using postgresql in china

On Tue, Jul 19, 2022 at 2:07 PM pig lee wrote: > Dear postgresql member > > > > I will use postgresql in china for a project. > > But I’m not sure Whether there are some limitations when using > postgresql in china(limitation for China only). > > For example，license limitation in china or other l

Re: postgis

Thanks Adrian still, I see a bunch of files, and nothing that can be installed via apt-get install. The only "things" big enough to contain a postgis distrib, like https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgis/postgis_3.0.3%2Bdfsg.orig.tar.xz can obviously not be installed via apt.

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

Sebastien Flaesch writes: > I try to update the underlying sequence of a SERIAL column, by using a > RETURNING clause in my INSERT statement, which is checking that the column > value is greater than the last_value of my sequence, and reset the sequence > with setval() if needed. It's not too

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

On 7/19/22 03:38, Achilleas Mantzios wrote: I reformatted queries to see thing better. -- still has problem testing the range select it.id ,cept.value::numeric as val, numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric ,'()') as range from items it, cept_report cept , dynacom.vessels

Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

Hello! I try to update the underlying sequence of a SERIAL column, by using a RETURNING clause in my INSERT statement, which is checking that the column value is greater than the last_value of my sequence, and reset the sequence with setval() if needed. When running several client processes in

Re: How to handle failed COMMIT

> Correct, the client did not get confirmation of commit success so it must > operate as if it failed. I mean that’s the point, the client can’t operate as if it failed. It must operate as the state is unknown. But maybe that’s the correct application behaviour, just that I haven’t thought this

Re: Setting up a server with previous day data

On 7/19/22 00:22, Srinivasa T N wrote: Hi All,    I have a primary postgresql 12 server which is being continuously used for transaction processing.  For reporting purposes, I want to set up a secondary server which has got previous day data.  Everyday night, I want the data from primary to b

Re: Out Of Memory

On 7/19/22 06:16, ghoostin...@mail.com wrote: Hello, I’m using Odoo Erp v12 Community and i want migrate to v14 and i have tablea content more 5m rows and after 8hr of execution it sho msg “out of memory” so i need your help And we need information: 1) Detail how you are doing migration. 2)

Re: How to handle failed COMMIT

On Tuesday, July 19, 2022, Håvar Nøvik wrote: > > Correct, the client did not get confirmation of commit success so it > must operate as if it failed. > > I mean that’s the point, the client can’t operate as if it failed. It must > operate as the state is unknown. But maybe that’s the correct app

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

"David G. Johnston" writes: > On Tuesday, July 19, 2022, Achilleas Mantzios > wrote: >> ERROR: cannot convert infinity to numeric > The column cept.value contains an infinity. I see nothing unusual in any > of these queries given that fact. If you try to cast the infinity to > numeric it will

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

On Tuesday, July 19, 2022, Achilleas Mantzios wrote: > > ERROR: cannot convert infinity to numeric > > -- has no problem testing against infinity > > select it.id ,cept.value::numeric as val, numrange(ceptl.min_alarm:: > numeric,ceptl.max_alarm::numeric > ,'()') as range from items it, cept_repo

Re: How to handle failed COMMIT

On Monday, July 18, 2022, Håvar Nøvik wrote: > > try >execute transactional sql > catch (commit failed) >if (data is not stored) > // regard data as not stored Correct, the client did not get confirmation of commit success so it must operate as if it failed. David J.

Re: About limitation of using postgresql in china

pig lee writes: > But I’m not sure Whether there are some limitations when using postgresql > in china(limitation for China only). There are no such restrictions so far as the Postgres project is concerned. What the Chinese government might think about it, I don't know.

Out Of Memory

Hello, I’m using Odoo Erp v12 Community and i want migrate to v14 and i have tablea content more 5m rows and after 8hr of execution it sho msg “out of memory” so i need your help

RE: pg_dump is filling C: drive up to 100 percent

Hi Thomas, This worked for me, thanks a lot. Regards, Meera From: Thomas Boussekey Sent: Tuesday, July 19, 2022 5:33 PM To: Meera Nair Cc: Abdul Qoyyuum ; pgsql-general@lists.postgresql.org Subject: Re: pg_dump is filling C: drive up to 100 percent External email. Inspect before opening. He

Re: restore question

On 19/07/2022 11:11, Ronald Haynes wrote: Hi folks,  I am a fairly novice postgresql user. I have a backup file for a couple of small postgresql databases.    The backup file was created using postgresql 12.2, my Mac now has a postgresql 13.x or 14.x versions.   I would like to restore the s

Re: postgis

On 7/18/22 13:08, Marc Millas wrote: 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 ? thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com Install alien and c

Re: pg_dump is filling C: drive up to 100 percent

Hello Meera, Le mar. 19 juil. 2022 à 13:42, Meera Nair a écrit : > Hi Abdul, > > > > We do realize that. With tar format, is there a way to customize the path > used for temporary local files? > > Some way to configure another drive instead of using C:\? > I would try this solution: https://sup

RE: pg_dump is filling C: drive up to 100 percent

Hi Abdul, We do realize that. With tar format, is there a way to customize the path used for temporary local files? Some way to configure another drive instead of using C:\? Regards, Meera From: Abdul Qoyyuum Sent: Tuesday, July 19, 2022 3:40 PM To: Meera Nair Cc: pgsql-general@lists.postgres

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

dynacom=# select it.id ,cept.value::numeric as val, numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric ,'()') as range from items it, cept_report cept , dynacom.vessels vsl, machdefs md, cept_reportlimits ceptl wh ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND it.v

restore question

Hi folks,  I am a fairly novice postgresql user. I have a backup file for a couple of small postgresql databases.    The backup file was created using postgresql 12.2, my Mac now has a postgresql 13.x or 14.x versions.   I would like to restore the setup from the backup file.  Psql Postgres f

Re: pg_dump is filling C: drive up to 100 percent

Try dumping without tar format. https://dba.stackexchange.com/a/52730 On Tue, Jul 19, 2022 at 4:33 PM Meera Nair wrote: > Hi team, > > > > pg_dump is filling C:\ > > > > This is for postgres version 12. Binary directory, data directory are in > E:\ > > I’m redirecting pg_dump output also to E:\

Re: Setting up a server with previous day data

You could instead set up High Availability and use your secondary as actual streamed and backed up database. https://www.postgresql.org/docs/current/high-availability.html This way, you get up-to-date data that you can query/generate reports with. On Tue, Jul 19, 2022 at 3:22 PM Srinivasa T N wr

Re: Getting the table ID

On 19 Jul 2022, at 5:10, Igor Korot wrote: Hi, guys, In the database theory each table is identified as "schema_name"."table_name". When I tried to look at how to get the table id inside the PostgreSQL, I saw that I needed to look at the pg_class table. SELECT oid FROM pg_class WHERE relnam

About limitation of using postgresql in china

Dear postgresql member I will use postgresql in china for a project. But I’m not sure Whether there are some limitations when using postgresql in china(limitation for China only). For example，license limitation in china or other limitation when used. Can you tell me the postgresql limitatio

Re: pg_receivewal/xlog to ship wal to cloud

On Tue, 19 Jul 2022, 2:09 am Alan Hodgson, wrote: > On Mon, 2022-07-18 at 15:55 -0400, neslişah demirci wrote: > > Hi all, > > > > Wondering if anyone has any experience of using pg_receivewal/xlog > > to ship wal files to GCP/S3? > > > > I use archive_command to send WAL to S3. It works fine. I

pg_dump is filling C: drive up to 100 percent

Hi team, pg_dump is filling C:\ This is for postgres version 12. Binary directory, data directory are in E:\ I'm redirecting pg_dump output also to E:\, I was taking a tar dump output. But C:\ is getting filled up . Looks like it is used for some sort of temporary staging. Is there a way not to

How to handle failed COMMIT

I've been wondering what the behavior of postgres is when the server process stops, for whatever reason, at certain critical points in the execution flow. In the following example the client will only regard the data as stored until the COMMIT command is successfully executed. But the the server

Setting up a server with previous day data

Hi All, I have a primary postgresql 12 server which is being continuously used for transaction processing. For reporting purposes, I want to set up a secondary server which has got previous day data. Everyday night, I want the data from primary to be shifted to secondary. I can achieve this m