RE: Identity columns, DEFAULT keyword and multi-row inserts

Hi Thomas, I agree that it does not seem very consistent. But is there any specific reason why are you using DEFAULT ? Why don't you simply execute : insert into test (data) values (1), (2); If you want / have to specify DEFAULT, then you should probably create your identity

Logical Replication of Multiple Schema Versions

Hi All, Thanks for having such a great mailing list. I hope I'm sending to the correct distribution. I will start with describing my current architecture and where do I use logical replication. I have several nodes each running a postgres-sql database. Each of these nodes has an API server whi

Identity columns, DEFAULT keyword and multi-row inserts

Hello, assume the following table: create table test ( id integer not null generated always as identity, data integer not null ); The following insert works fine: insert into test (id, data) values (default,1); However, a multi-row insert like the following:

Re: Tuple concurrency issue in large objects

Hi Shalini. The usual diagnostic info is your postgresql server version, major and minor version, such as in 12.1 , the major version is 12 and the minor version (patch version) is 1. On Fri, Dec 6, 2019 at 9:26 AM Shalini wrote: > Hi all, > > I am working on a project which allows multiple use

Re: Upgrade PostgreSQL 9.6 to 10.6

Great, thanks Andreas, So this seems to be a good feature using the core concept of replication. Can I use this extension and do the major upgrade without paying ?. On Mon, Dec 9, 2019 at 10:02 AM Andreas Kretschmer wrote: > > > Am 09.12.19 um 16:43 schrieb github kran: > > Hello PostgreSQL Team

Re: Query with correlated join having slow performance

po 9. 12. 2019 v 21:05 odesílatel saket bansal napsal: > Thank you Michael. I re-wrote it and it does perform well. Modified query > at: > > > https://github.com/bansalsaket/PG_correlated_subquery_slowness/blob/master/Modified%20query%20-%20performs%20faster.txt > > Our app team is checking with

Re: Query with correlated join having slow performance

Thank you Michael. I re-wrote it and it does perform well. Modified query at: https://github.com/bansalsaket/PG_correlated_subquery_slowness/blob/master/Modified%20query%20-%20performs%20faster.txt Our app team is checking with their vendor whether this can be modified at source code level or not

Re: Query with correlated join having slow performance

Thanks Justin for pointing this out. More work for optimizer for nothing, I will remove it. On Mon, Dec 9, 2019 at 2:48 PM Justin wrote: > Hi Saket > > The first filter condition seems to be duplicated it appears this can be > simplified from > > and ( pdtaltrelt0_.status_typ_dbky=102 >

Re: Query with correlated join having slow performance

Hi Saket The first filter condition seems to be duplicated it appears this can be simplified from and ( pdtaltrelt0_.status_typ_dbky=102 and ( pdtaltrelt0_.rule_status_typ_dbky is null ) or pdtaltrelt0_.status_typ_dbky in ( 19 ) or pdtaltrelt0_.status_typ_dbky in (20 )

Re: Query with correlated join having slow performance

I'd suggest re-writing your query to avoid ORs whenever possible. Is this generated by an ORM or subject to change with filters selected in application or can you totally control it on DB side? It may be hugely more performant to simply rewrite this as (almost) the same query twice UNION ALL'd tog

Query with correlated join having slow performance

Hi Postgres Experts, Please help me on a query tuning. Postgres verson: 11.5 This database has been migrated from oracle 12c to postgres. In Oracle query executes in 2-3 secs, but in postgres it hangs forever. There are no transactions at this time, I am stuck at first run after migration. My an

Re: Q: cert authentication and user remapping fails

Sorry for the late reply, I could test your solution only today… Am 06.12.19 18:52 schrieb(en) Tom Lane: I don't think that the user name mapping feature works in the way you are hoping it does. According to https://www.postgresql.org/docs/current/auth-username-maps.html what the map does is

Re: Upgrade PostgreSQL 9.6 to 10.6

Am 09.12.19 um 16:43 schrieb github kran: Hello PostgreSQL Team, I would like to know what would be the best way to do Database migration from PostgreSQL 9.6 engine to 10.6 by creating a new cluster in 10.6 and then copy data. Size of the cluster is 3.8 TB. 1) It would be a new cluster we

Upgrade PostgreSQL 9.6 to 10.6

Hello PostgreSQL Team, I would like to know what would be the best way to do Database migration from PostgreSQL 9.6 engine to 10.6 by creating a new cluster in 10.6 and then copy data. Size of the cluster is 3.8 TB. 1) It would be a new cluster we want to create on 10.6 and then copy data form 9.

Re: AW: secure deletion of archived logs

Greetings, * Zwettler Markus (OIZ) (markus.zwett...@zuerich.ch) wrote: > We use "rsync" on XFS with "wsync" mount mode. I think this should do the job? No, that just makes sure that namespace operations are executed synchronously, that doesn't provide any guarantee that the data has actually been

Re: migration from 9.4 to 9.6

El 6/12/19 a las 23:07, Alan Hodgson escribió: On Fri, 2019-12-06 at 21:38 +, Julie Nishimura wrote: I'd like to copy one single database from 9.4 cluster to a new 9.6 cluster (migration with the upgrade), to the different host Put 9.4 on the new server. Replicate the db to it. When you

RE: PostgreSQL HA FO question

Hi Zwettler, Based on what comparison, production experience ? Regards, Dor From: Zwettler Markus (OIZ) Sent: Monday, December 9, 2019 1:13 PM To: Dor Ben Dov ; pgsql-general@lists.postgresql.org Subject: AW: PostgreSQL HA FO question In my opinion, the best open source product is Patroni.

AW: PostgreSQL HA FO question

In my opinion, the best open source product is Patroni. Von: Dor Ben Dov Gesendet: Montag, 9. Dezember 2019 11:24 An: pgsql-general@lists.postgresql.org Betreff: PostgreSQL HA FO question Hi everyone, What is the best / mostly common / production used open source solution for HA / FO/ backu

PostgreSQL HA FO question

Hi everyone, What is the best / mostly common / production used open source solution for HA / FO/ backup and restore. Open source community / Commercial Best Regards, Dor Ben Dov This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Ter

Re: Rows violating Foreign key constraint exists

Hi, Sorry about the delay in getting back with the results. > > On Fri, Nov 29, 2019 at 7:23 AM Tom Lane wrote: > > The most likely "corruption" explanation is something wrong with the > > indexes on the referenced and/or referencing column, causing rows to > > not be found when referential acti