Re: Rows violating Foreign key constraint exists

[Nandakumar M]

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 actions should have found them.  Random
> > querying of the tables wouldn't necessarily expose that --- you'd need
> > to be sure that your queries use the questionable indexes, and maybe
> > even search for some of the specific rows that seem mis-indexed.

This indeed is the problem. Select query with criteria on FK column
did not return any rows. However, after I disabled indexscan, bitmap
indexscan and tried the same query this time sequential scan was
performed by PG and it returned 80 rows.

> Or try using contrib/amcheck, which is available in Postgres 10.
> Perhaps try the query here, modified to verify all B-Tree indexes (not
> just those indexes in the pg_catalog schema):
>
> https://www.postgresql.org/docs/10/amcheck.html
>
> --

I tried amcheck query on all indexes in the database and it did not
raise any errors.

How do I identify such corruption exists in the database? Will
enabling page checksum be of help here?

Thanks.

Regards,
Nanda

PostgreSQL HA FO question

[Dor Ben Dov]

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 Terms of Service, which you may review at 
https://www.amdocs.com/about/email-terms-of-service 

AW: PostgreSQL HA FO question

[Zwettler Markus (OIZ)]

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/ 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 Terms of Service, which you may review at 
https://www.amdocs.com/about/email-terms-of-service

RE: PostgreSQL HA FO question

[Dor Ben Dov]

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.



Von: Dor Ben Dov mailto:dor.ben-...@amdocs.com>>
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/ 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 Terms of Service, which you may review at 
https://www.amdocs.com/about/email-terms-of-service
This email and the information contained herein is proprietary and confidential 
and subject to the Amdocs Email Terms of Service, which you may review at 
https://www.amdocs.com/about/email-terms-of-service 

Re: migration from 9.4 to 9.6

[Ekaterina Amez]

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 ready 
to switch, shut down the master, promote the new db, and then shut it 
down and pg_upgrade -k it to 9.6.


That does require the binaries from both versions to be on the new 
server for a while, but it'll give you the least downtime and it's a 
very simple replication setup.



This is the plan we're going to follow to migrate from 9.2 to 9.6, as 
this is the only way to achieve almost zero downtime. The cons are, 
despite what Alan has mentioned, that you need double space because you 
have to create a slave replica.

Re: AW: secure deletion of archived logs

[Stephen Frost]

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 written out and sync'd.

> The tools mentioned will all do backup to disk.
> We are required to do backup to tape.

Back up to disk first and then tar to tape.

Thanks,

Stephen


signature.asc
Description: PGP signature

Upgrade PostgreSQL 9.6 to 10.6

[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 want to create on 10.6 and then copy data
form 9.6 and shutdown 9.6
2) Cluster today contains 1 master instance and another instance for reader
3) Can this be done without a downtime ?  what are the options I have to do
this without making this complicated . We have about 30 -40 tables to do
the migration.

Appreciate your replies.

Thanks,
Kranthi

Re: Upgrade PostgreSQL 9.6 to 10.6

[Andreas Kretschmer]

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 want to create on 10.6 and then copy 
data form 9.6 and shutdown 9.6
2) Cluster today contains 1 master instance and another instance for 
reader
3) Can this be done without a downtime ?  what are the options I have 
to do this without making this complicated . We have about 30 -40 
tables to do the migration.





yes, you can use pg_logical from us (2ndquadrant). Please visit our 
homepage, there you can find it for 9.6. There are also some 
blogs-postings about all that in our blog.



Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: Q: cert authentication and user remapping fails

[Albrecht Dreß]

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 to specify allowed combinations of the validated external user name 
("Albrecht Dreß" in your example) and the database role the user asked to 
connect as.  So given

> certaccess /^.*$   testuser

it should be possible to do

psql -h dbserver -U testuser testdb

with a certificate that has CN="Albrecht Dreß" (or anything else).


Yes, this works perfectly – I really misunderstood the docs here!

Thanks a lot for your help,
Albrecht.

pgpocGwz5awno.pgp
Description: PGP signature

Query with correlated join having slow performance

[saket bansal]

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 analysis:

I have done vacuum full , analyze , even with 100% samples using a much
higher value of default_statistics_target.
Also tried different hints using pg_hint_plan extension. Overall cost
reduces, but actual run hangs forever.
I think problem is with correlated subquery join condition.
If "and pdtaltrelt*%*_.tenant_dkby=pdtaltrelt0_.tenant_dkby" condition is
removed from both subqueries, result comes in secs(I understand that would
be skipping correlated join)

 SQL> select count(*) from pdtalt_rel_to_tenant_rel;
267216

SQL> select count(distinct tenant_dkby) from pdtalt_rel_to_tenant_rel;
 3

Table DDLs , query plan and parameter configuration available at below git
link:
https://github.com/bansalsaket/PG_correlated_subquery_slowness

I have 16 GB , 4 CPU , rhel 7 machine.

Thanks for help in advance, let me know if any additional information is
required

Re: Query with correlated join having slow performance

[Michael Lewis]

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 together to separate the
productalt1_.alt_pdt_dbky OR productalt1_.orgnl_pdt_dbky conditions.

Re: Query with correlated join having slow performance

[Justin]

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 )
 )
and ( pdtaltrelt0_.status_typ_dbky in (19 , 20)
   or pdtaltrelt0_.status_typ_dbky=102
   and (pdtaltrelt0_.rule_status_typ_dbky is null)
)
TO

and (
(pdtaltrelt0_.status_typ_dbky = 102 and pdtaltrelt0_.rule_status_typ_dbky
is null)
or pdtaltrelt0_.status_typ_dbky in (19, 20)
)

The Explain shows the filter seq filter like so
 Filter: (
 ((status_typ_dbky = ANY ('{19,20}'::bigint[]))
   OR ((status_typ_dbky = 102) AND (rule_status_typ_dbky IS NULL))
  )
 AND
 (((status_typ_dbky = 102) AND (rule_status_typ_dbky IS NULL))
OR (status_typ_dbky = 19)
OR (status_typ_dbky = 20)
)
)

I can not see the difference between above/below the AND  other than the
order of operations...



On Mon, Dec 9, 2019 at 1:33 PM saket bansal  wrote:

> 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 analysis:
>
> I have done vacuum full , analyze , even with 100% samples using a much
> higher value of default_statistics_target.
> Also tried different hints using pg_hint_plan extension. Overall cost
> reduces, but actual run hangs forever.
> I think problem is with correlated subquery join condition.
> If "and pdtaltrelt*%*_.tenant_dkby=pdtaltrelt0_.tenant_dkby" condition is
> removed from both subqueries, result comes in secs(I understand that would
> be skipping correlated join)
>
>  SQL> select count(*) from pdtalt_rel_to_tenant_rel;
> 267216
>
> SQL> select count(distinct tenant_dkby) from pdtalt_rel_to_tenant_rel;
>  3
>
> Table DDLs , query plan and parameter configuration available at below git
> link:
> https://github.com/bansalsaket/PG_correlated_subquery_slowness
>
> I have 16 GB , 4 CPU , rhel 7 machine.
>
> Thanks for help in advance, let me know if any additional information is
> required
>
>

Re: Query with correlated join having slow performance

[saket bansal]

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
> and ( pdtaltrelt0_.rule_status_typ_dbky is null )
> or pdtaltrelt0_.status_typ_dbky in ( 19 )
> or pdtaltrelt0_.status_typ_dbky in (20 )
>  )
> and ( pdtaltrelt0_.status_typ_dbky in (19 , 20)
>or pdtaltrelt0_.status_typ_dbky=102
>and (pdtaltrelt0_.rule_status_typ_dbky is null)
> )
> TO
>
> and (
> (pdtaltrelt0_.status_typ_dbky = 102 and pdtaltrelt0_.rule_status_typ_dbky
> is null)
> or pdtaltrelt0_.status_typ_dbky in (19, 20)
> )
>
> The Explain shows the filter seq filter like so
>  Filter: (
>  ((status_typ_dbky = ANY ('{19,20}'::bigint[]))
>OR ((status_typ_dbky = 102) AND (rule_status_typ_dbky IS NULL))
>   )
>  AND
>  (((status_typ_dbky = 102) AND (rule_status_typ_dbky IS NULL))
> OR (status_typ_dbky = 19)
> OR (status_typ_dbky = 20)
> )
> )
>
> I can not see the difference between above/below the AND  other than the
> order of operations...
>
>
>
> On Mon, Dec 9, 2019 at 1:33 PM saket bansal  wrote:
>
>> 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 analysis:
>>
>> I have done vacuum full , analyze , even with 100% samples using a much
>> higher value of default_statistics_target.
>> Also tried different hints using pg_hint_plan extension. Overall cost
>> reduces, but actual run hangs forever.
>> I think problem is with correlated subquery join condition.
>> If "and pdtaltrelt*%*_.tenant_dkby=pdtaltrelt0_.tenant_dkby" condition
>> is removed from both subqueries, result comes in secs(I understand that
>> would be skipping correlated join)
>>
>>  SQL> select count(*) from pdtalt_rel_to_tenant_rel;
>> 267216
>>
>> SQL> select count(distinct tenant_dkby) from pdtalt_rel_to_tenant_rel;
>>  3
>>
>> Table DDLs , query plan and parameter configuration available at below
>> git link:
>> https://github.com/bansalsaket/PG_correlated_subquery_slowness
>>
>> I have 16 GB , 4 CPU , rhel 7 machine.
>>
>> Thanks for help in advance, let me know if any additional information is
>> required
>>
>>

Re: Query with correlated join having slow performance

[saket bansal]

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.
But question remains somewhat valid. Data volume is not huge and original
query wasn't very badly written either. Operating system level resources
are similar.
Do you know of any bugs associated with using co-related sub queries in
postgres. In Oracle, it runs in a sec, while in postgres it does not give
result at all , even after 8-9 hours.
I understand both database engines work differently, but such drastic
change in performance is a surprise!
We have lot of migrations planned from oracle to postgres, this could be a
show stopper. :(
Any suggestions...

On Mon, Dec 9, 2019 at 1:49 PM Michael Lewis  wrote:

> 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 together to separate the
> productalt1_.alt_pdt_dbky OR productalt1_.orgnl_pdt_dbky conditions.
>

Re: Query with correlated join having slow performance

[Pavel Stehule]

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 their vendor whether this can be modified at
> source code level or not.
> But question remains somewhat valid. Data volume is not huge and original
> query wasn't very badly written either. Operating system level resources
> are similar.
> Do you know of any bugs associated with using co-related sub queries in
> postgres. In Oracle, it runs in a sec, while in postgres it does not give
> result at all , even after 8-9 hours.
> I understand both database engines work differently, but such drastic
> change in performance is a surprise!
> We have lot of migrations planned from oracle to postgres, this could be a
> show stopper. :(
> Any suggestions...
>

There was more times discussion about rewriting OR conditions to UNION -
but nobody did this work what I know. Usually Postgres process OR
conditions well due bitmap scans, but it doesn't work well in some special
cases. To this time this issue was fixed by manual query rewriting.

Regards

Pavel


> On Mon, Dec 9, 2019 at 1:49 PM Michael Lewis  wrote:
>
>> 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 together to separate the
>> productalt1_.alt_pdt_dbky OR productalt1_.orgnl_pdt_dbky conditions.
>>
>

Re: Upgrade PostgreSQL 9.6 to 10.6

[github kran]

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,
> > 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.6 and shutdown 9.6
> > 2) Cluster today contains 1 master instance and another instance for
> > reader
> > 3) Can this be done without a downtime ?  what are the options I have
> > to do this without making this complicated . We have about 30 -40
> > tables to do the migration.
> >
> >
>
> yes, you can use pg_logical from us (2ndquadrant). Please visit our
> homepage, there you can find it for 9.6. There are also some
> blogs-postings about all that in our blog.
>
>
> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
>
>

Re: Tuple concurrency issue in large objects

[Rene Romero Benavides]

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 users to work on single
> large text document. I am using lo_put to apply only the diff into the
> large object without replacing it with a new lob. While working on it, I
> encountered an error "Tuple concurrently updated".
> The error can be reproduced with two psql clients.
>
> Setup:
>
> mydb=# create table text_docs(id serial primary key, data oid);
> CREATE TABLE
> mydb=# insert into text_docs(data) select lo_import('./upload.txt');
> INSERT 0 1
> mydb=# select * from text_docs;
>   id |  data
> +-
>1 | 5810130
> (1 rows)
>
> Now, if we open two psql clients and execute the following commands:
>
> Client 1:
>
> mydb=# begin;
> BEGIN
> mydb=# select lo_put(5810130, 10, '\xaa');
> UPDATE 1
>
> Client 2:
>
> mydb=# select lo_put(5810130, 10, '\xaa');
>
> Client 1:
> mydb=# commit;
> COMMIT
>
> Client 2:
> mydb=# select lo_put(5810130, 10, '\xaa');
> ERROR:  tuple concurrently updated
>
> Is there a workaround to this concurrency issue without creating a new
> large object?
>
> Regards
> Shalini
>
>
>
>
>

-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Identity columns, DEFAULT keyword and multi-row inserts

[Thomas Kellerer]

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:

insert into test (id, data)
values 
  (default,1),
  (default,2);


fails with: 

ERROR: cannot insert into column "id"
  Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
  Hint: Use OVERRIDING SYSTEM VALUE to override.


My question is: 

* If DEFAULT is not allowed for identity columns, then why does the single-row 
insert work? 
* If DEFAULT _is_ allowed, then why does the multi-row insert fail? 

The above happens with Postgres 10,11 and 12

Regards
Thomas

Logical Replication of Multiple Schema Versions

[Dan shmidt]

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 which uses the database in order to retrieve data.
I recently created an aggregated node which according to the logical 
replication documentation is a common use-case "Consolidating multiple 
databases into a single one (for example for analytical purposes)."
The aggregation node also has an API server and should serve the same use cases 
as a regular node serves but with aggregated information.

My question is about schema upgrades. As very well documented logical 
replication does not replicate schema changes, thus all schema modifications 
should be done on both regular-nodes and aggregated-node.
Here are my constraints and relaxation points:

  *   I would prefer having the freedom of making any schema change (i.e: 
removing/renaming a column).
  *   I must have the ability to upgrade each node separately and not taking 
all of the nodes down.
  *   I'm willing to allow a rather big (2 week) downtime in the replication as 
long as the regular-node keeps on working. (Hopefully aggregated-node can still 
be operative, and not be up to date with will regular-nodes)
  *   I'm willing to allow downtime of regular node as long as the process of 
upgrading the node is taking place.

I started with a naive approach:

  1.  Take down all the nodes
  2.  Schema upgrade for the aggregated-node
  3.  Schema upgrade for the regular-nodes
  4.  Start everything up

The problem I encountered with this approach is that while taking down the 
regular-nodes there might be some not yet replicated WAL entries containing 
schema prior to the upgrade, thus after step 4 above replication will fail.
My next approach was to let the logical replication "drain" until there are no 
changes in the database and then upgrading all of the nodes, but this breaks 
constraint #2.

What is the correct way to perform such an operation?
Is there a way to keep constraint #1 or the only option is to not allow 
"breaking" schema changes between versions.

Thank you,
Dan.

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

[Patrick FICHE]

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 as "generated by default".

Regards,

Patrick Fiche
Database Engineer, Aqsacom Sas.
c. 33 6 82 80 69 96 



-Original Message-
From: Thomas Kellerer  
Sent: Tuesday, December 10, 2019 8:42 AM
To: pgsql-general@lists.postgresql.org
Subject: 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:

insert into test (id, data)
values 
  (default,1),
  (default,2);


fails with: 

ERROR: cannot insert into column "id"
  Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
  Hint: Use OVERRIDING SYSTEM VALUE to override.


My question is: 

* If DEFAULT is not allowed for identity columns, then why does the single-row 
insert work? 
* If DEFAULT _is_ allowed, then why does the multi-row insert fail? 

The above happens with Postgres 10,11 and 12

Regards
Thomas