Re: Using JSONB with nested key-value structures, and skipping/wildcarding the key

[Shaheed Haque]

>
> Hi,
>
> On Mon, 1 Jun 2020 at 23:50, Alban Hertroys  wrote:


> > On 1 Jun 2020, at 20:18, Shaheed Haque  wrote:
> >
> > Hi,
> >
> > I'm using Django's ORM to access Postgres12. My "MyModel" table has a
> JSONB column called 'snapshot'. In Python terms, each row's 'snapshot'
> looks like this:
> >
> > ==
> > snapshot = {
> > 'pay_definition' : {
> > '1234': {..., 'name': 'foo', ...},
> > '99': {..., 'name': 'bar', ...},
> > }
> > ==
> >
> > I'd like to find all unique values of 'name' in all rows of MyModel. I
> have this working using native JSON functions from the ORM like this:
> >
> > =
> > class PayDef(Func):
> > function='to_jsonb'
> >
>  
> template="%(function)s(row_to_json(jsonb_each(%(expressions)s->'pay_definition'))->'value'->'name')"
> >
> >
> MyModel.objects.annotate(paydef=PayDef(F('snapshot'))).order_by().distinct('paydef').values_list('paydef',
> flat=True)
> > =
> >
> > So, skipping the ordering/distinct/ORM parts, the core looks like this:
> >
> >
> to_jsonb(row_to_json(jsonb_each('snapshot'->'pay_definition'))->'value'->'name’)
>
>
> I do something like this to get a set of sub-paths in a JSONB field (no
> idea how to write that in Django):
>
> select snapshot->’pay_definition’->k.value->’name’
>   from MyModel
>   join lateral jsonb_object_keys(snapshot->’pay_definition’) k(value) on
> true
>
>
I was unaware of the LATERAL keyword, so thanks. After a bit of Googling
however, it seems that it is tricky/impossible to use from the ORM (barring
a full scale escape to a "raw" query). One question: as a novice here, I
think I understand the right hand side of your JOIN "... k(value)" is
shorthand for:

... AS table_name(column_name)

except that I don't see any clues in the docs that jsonb_object_keys() is a
"table function". Can you kindly clarify?

> I don’t know how that compares performance-wise to using jsonb_each, but
> perhaps worth a try. Obviously, the way it’s written above it doesn’t
> return distinct values of ’name’ yet, but that’s fairly easy to remedy.
>
> Indeed; this is what I managed to get to:

SELECT DISTINCT snapshot -> 'pay_definition' -> k.value -> 'name' AS name
FROM paiyroll_payrun
 JOIN LATERAL jsonb_object_keys(snapshot -> 'pay_definition')
AS k(value) ON true
ORDER BY name;

At any rate, I'll have to ponder the "raw" route absent some way to "JOIN
LATERAL".

Thanks, Shaheed


> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
>
>
>

Re: Using JSONB with nested key-value structures, and skipping/wildcarding the key

[Thomas Kellerer]

> One question: as a novice here, I think I understand the right hand
> side of your JOIN "... k(value)" is shorthand for:
>
> ... AS table_name(column_name)
>
> except that I don't see any clues in the docs that
> jsonb_object_keys() is a "table function".> Can you kindly clarify?

The clue is in the column "return type" which states: "setof text" for 
jsonb_object_keys()
A function returning "setof" is the same as a "table function"

Re: Oracle vs. PostgreSQL - a comment

[Ron]

On 6/1/20 4:58 AM, Peter J. Holzer wrote:
[snip]

As a developer (and part time DBA) I have a hard time thinking of any Oracle
feature that I'm missing in PostgreSQL.


The inability to do a point-in-time restoration of a *single* database in a 
multi-db cluster is a serious -- and fundamental -- missing feature (never 
to be implemented because of the fundamental design).


In SQL Server, it's trivial to restore -- including differentials and WAL 
files -- an old copy of a prod database *to a different name* so that you 
now have databases FOO and FOO_OLD in the same instance.


In Postgres, though, you've got to create a new cluster using a new port 
number (which in our case means sending a firewall request through channels 
and waiting two weeks while the RISK team approves opening the port -- and 
they might decline it because it's non-standard -- and then the Network team 
creates a /change order/ and then implements it).


Bottom line: something I can do in an afternoon with SQL Server takes two 
weeks for Postgres.


This has given Postgres a big, fat black eye with our end users.

--
Angular momentum makes the world go 'round.

Re: Oracle vs. PostgreSQL - a comment

[Grigory Smolkin]

On 6/2/20 11:22 AM, Ron wrote:
The inability to do a point-in-time restoration of a *single* database 
in a multi-db cluster is a serious -- and fundamental -- missing 
feature (never to be implemented because of the fundamental design).

It is possible via 3rd party tools like pg_probackup and pgbackrest.

--
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: pg_dump crashes

[Nico De Ranter]

FYI:  I tried setting the md5 field to '' in the whole table but that
didn't fix the pg_dump issue.  In the end I decided to drop the database
and revert to my last successful backup. I'm now still reading in all tapes
to reconstruct the latest database state.

Thanks for the help anyway.

Nico

On Mon, May 25, 2020 at 4:20 PM Adrian Klaver 
wrote:

> On 5/24/20 10:30 PM, Nico De Ranter wrote:
> > Unfortunately not. I discovered the issue rather late. The last working
> > backup is about 2 months old.
>
> Well first it is entirely possible this is not the only corruption in
> the database.
>
> Second you are probably going to have to reach out to the Bacula folks:
>
> https://www.bacula.org/support/
> https://sourceforge.net/projects/bacula/lists/bacula-users
>
> I would say the questions to ask them are:
>
> 1) Is the md5 required for a file?
>
> 2) If so how and over what is calculated?
>
>
> Then you could experiment with trying to update the md5 field with new
> data.
>
> >  >
> >  >
> >  >
> >  > Following up on the max(bigint), I tried
> >  >
> >  > SELECT md5 FROM public.file where fileid >2087994666;
> >  >
> >  > and got
> >  >
> >  > ERROR:  compressed data is corrupted
> >  >
> >  > So it does look like those entries are killing it.  Now for the
> >  > million-dollar question: how do I get them out?
> >
> > Do you have recent previous backup?
> >
> >  >
> >  > Nico
> >  >
> >  > --
> >  >
> >  > Nico De Ranter
> >  >
> >  > Operations Engineer
> >  >
> >
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
> >
> >
> > --
> >
> > Nico De Ranter
> >
> > Operations Engineer
> >
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 

Nico De Ranter

Operations Engineer

T. +32 16 38 72 10







eSATURNUS
Philipssite 5, D, box 28
3001 Leuven – Belgium

T. +32 16 40 12 82
F. +32 16 40 84 77
www.esaturnus.com



*For Service & Support :*

Support Line Belgium: +32 2 2009897

Support Line International: +44 12 56 68 38 78

Or via email : medical.services...@sony.com

Patroni

[Sonam Sharma]

Can someone please share steps or any link for how to do set up postgres
replication using patroni. And also to test automatic failover.

Thanks in advance,
Sonam

Re: Oracle vs. PostgreSQL - a comment

[Tim Clarke]

On 02/06/2020 09:22, Ron wrote:
The inability to do a point-in-time restoration of a single database in a 
multi-db cluster is a serious -- and fundamental -- missing feature (never to 
be implemented because of the fundamental design).

In SQL Server, it's trivial to restore -- including differentials and WAL files 
-- an old copy of a prod database to a different name so that you now have 
databases FOO and FOO_OLD in the same instance.

In Postgres, though, you've got to create a new cluster using a new port number 
(which in our case means sending a firewall request through channels and 
waiting two weeks while the RISK team approves opening the port -- and they 
might decline it because it's non-standard -- and then the Network team creates 
a change order and then implements it).

Bottom line: something I can do in an afternoon with SQL Server takes two weeks 
for Postgres.

This has given Postgres a big, fat black eye with our end users.

--
Angular momentum makes the world go 'round.


But that's nothing to do with Postgres; it takes two weeks because you have 
broken procedures imho


Tim Clarke



Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: 
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here https://www.manifest.co.uk/legal/ for further information.

LOG: could not send data to client: Broken pipe

[TALLURI Nareshkumar]

Hello Postgres Support Team,

I need your help to identify the issue and apply the fix for it.

My client is running one query through Application and getting error like below.

11:20:46.298 [http-nio-8083-exec-7] ERROR c.s.s.g.a.alcor.pdi.dao.PdiDao - 
While getting pdi from 2019-12-21 to 2020-06-01: An I/O error occurred while 
sending to the backend.
org.postgresql.util.PSQLException: An I/O error occurred while sending to the 
backend.

When I checked in the postgres log I noticed the below.

2020-06-02 02:09:21.333 CEST db:[xxx] user:[x] [xx(53643)] [08006] 
LOG:  could not send data to client: Broken pipe
2020-06-02 02:09:21.333 CEST db:[xxx] user:[x] [(53643)] 
[08006] STATEMENT:  select . (it just a 
select query)
2020-06-02 02:09:21.333 CEST db:[xxx] user:[xx] [xx(53643)] [08006] 
FATAL:  connection to client lost

Note: DB name, hostname, username, sql text  morphed in above message for 
security reasons, so kindly consider it.

I ran the above select query from psql prompt and I got the output, Time: 
7178.926 ms. Ofcource my one is local connection so no broken pipe, where as 
client is using the network so he is getting broken pipe.
We checked with Network team they are claiming no issues with 
the network, But not sure.

I worked with Unix team and they raised a case to Red hat 
support and got confirmation from them that "no issues observed from OS End"
In-fact Red hat looking for a feedback of Postgres Community.

Version of Postgres :   PostgreSQL 9.6.5 on 
x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 
4.8.5-11), 64-bit
OS  :   Red Hat Enterprise 
Linux Server release 7.2 (Maipo)

We observed "LOG:  could not send data to client: Broken pipe" in postgres log 
from long time , But recently client complained about it. So we are in process 
of fixing it for which we need your help.
Fyi.. I had performed VACUUM(FULL, ANALYZE, VERBOSE) on the 
table. So the last analyzed is latest.

I already checked in Postgres archive and found below links on the same topic, 
But I could not get the concreate answer to fix the issue, so please help.

https://www.postgresql.org/search/?m=1&q=LOG%3A++could+not+send+data+to+client%3A+Broken+pipe&l=2&d=-1&s=i

checked  11,12,13,14,15,16,17,18 mail replies

Let me know if you need any more details from my end.

Regards,
Narresh





=

Ce message et toutes les pieces jointes (ci-apres le "message")
sont confidentiels et susceptibles de contenir des informations
couvertes par le secret professionnel. Ce message est etabli
a l'intention exclusive de ses destinataires. Toute utilisation
ou diffusion non autorisee interdite.
Tout message electronique est susceptible d'alteration. La SOCIETE GENERALE
et ses filiales declinent toute responsabilite au titre de ce message
s'il a ete altere, deforme falsifie.

=

This message and any attachments (the "message") are confidential,
intended solely for the addresses, and may contain legally privileged
information. Any unauthorized use or dissemination is prohibited.
E-mails are susceptible to alteration. Neither SOCIETE GENERALE nor any
of its subsidiaries or affiliates shall be liable for the message
if altered, changed or falsified.

=

Fine grained permissions on User Mapping

[Paul Bonaud]

Hello,

I couldn't find any answer in the PostgreSQL documentation so here I am
with a question regarding FDW and User Mappings. *Is it possible to define
permissions on user mappings to hide the connection info (mainly the
password) to a user?*

More details of the context:

Imagine you have a destination database which you have no control over.
Let's call it “external-db”. This database has a unique pg user (no
specific pg permission attributes) with read-write access to the whole
database let's call it “external-user”.

Now over to our own database which we have control over. Imagine we want to
use a pg foreign data wrapper to access tables from the “external-db” from
a basic (non superuser) user, let's call it “basic-user”.

-- Setup as a superuser
-- Setup FDW
CREATE EXTENSION postgres_fdw;

-- Create foreign server
CREATE SERVER "external-db" FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
'127.0.0.1', dbname 'external-db', port '5434');
CREATE USER MAPPING FOR "basic-user" SERVER "external-db" OPTIONS (user
'external-user', password 'external_user_super_secret_password');
GRANT USAGE ON FOREIGN SERVER "external-db" TO "basic-user";

If we connect now with the “basic-user” we can create foreign tables to
access the “external-db” which is great.

The issue:

*However*, we would like to avoid our “basic-user” to have full control
over the external-db. We would like this basic user to only be able to
*read* the external database.
With this current setup the user can very simply list the user mappings
with details (\deu+ in psql) to collect the username/password combination
and thus directly connect to the initial “external-db” with full access.

Does PostgreSQL offer some kind of permissions over the USER MAPPING
options so it can be *used* by a pg user but not *seen*? Is there any other
solution for this kind of requirement?


Many thanks for reading and any help will be very appreciated :),
Paul

Re: Patroni

[Paul Förster]

Hi Sonam,

> On 02. Jun, 2020, at 13:36, Sonam Sharma  wrote:
> 
> Can someone please share steps or any link for how to do set up postgres 
> replication using patroni. And also to test automatic failover. 

all you need to know is here: https://github.com/zalando/patroni

Cheers,
Paul

Re: Using JSONB with nested key-value structures, and skipping/wildcarding the key

[Alban Hertroys]

> On 2 Jun 2020, at 9:30, Shaheed Haque  wrote:
> 
> 
>> I do something like this to get a set of sub-paths in a JSONB field (no idea 
>> how to write that in Django):
>> 
>> select snapshot->’pay_definition’->k.value->’name’
>>   from MyModel
>>   join lateral jsonb_object_keys(snapshot->’pay_definition’) k(value) on true
>> 
> I was unaware of the LATERAL keyword, so thanks. After a bit of Googling 
> however, it seems that it is tricky/impossible to use from the ORM (barring a 
> full scale escape to a "raw" query). One question: as a novice here, I think 
> I understand the right hand side of your JOIN "... k(value)" is shorthand for:
> 
> ... AS table_name(column_name)
> 
> except that I don't see any clues in the docs that jsonb_object_keys() is a 
> "table function". Can you kindly clarify?

Correct. Thomas already explained the return type, but the plural form of the 
function name is also an indication that it returns multiple results.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: Fine grained permissions on User Mapping

[Tom Lane]

Paul Bonaud  writes:
> Imagine you have a destination database which you have no control over.
> Let's call it “external-db”. This database has a unique pg user (no
> specific pg permission attributes) with read-write access to the whole
> database let's call it “external-user”.
> ...
> Now over to our own database which we have control over. Imagine we want to
> use a pg foreign data wrapper to access tables from the “external-db” from
> a basic (non superuser) user, let's call it “basic-user”.
> ...
> *However*, we would like to avoid our “basic-user” to have full control
> over the external-db. We would like this basic user to only be able to
> *read* the external database.
> With this current setup the user can very simply list the user mappings
> with details (\deu+ in psql) to collect the username/password combination
> and thus directly connect to the initial “external-db” with full access.

So you're doing it wrong at at least two levels here:

1. The remote user you're mapping to ought to have just the privileges
you want the local user to have w.r.t. that database.  User IDs are
cheap in Postgres; don't be so miserly as not to create a separate one
for each privilege level you need.  If you did that, you wouldn't really
care whether the user could also connect directly to the remote DB.

2. You don't want to grant USAGE on the foreign server to the local
user, either.  It's possibly an error in the design of SQL/MED that
foreign server USAGE grants both the ability to create/delete foreign
tables and the ability to create/delete/inspect user mappings.  But
that's how the committee did it, so we're stuck.

If it's really too painful to not let the local user create/delete his
own foreign tables, then what you could do is make sure the remote user
ID's password is useless for any purpose except connecting from the
source database.  One way to do that is to adjust the remote DB's
pg_hba.conf to disallow the remote user ID from connecting from
anyplace except the local database server.

regards, tom lane

Re: LOG: could not send data to client: Broken pipe

[Adrian Klaver]

On 6/1/20 8:41 PM, TALLURI Nareshkumar wrote:

Hello Postgres Support Team,

I need your help to identify the issue and apply the fix for it.

My client is running one query through Application and getting error 
like below.


11:20:46.298 [http-nio-8083-exec-7] ERROR c.s.s.g.a.alcor.pdi.dao.PdiDao 
- While getting pdi from 2019-12-21 to 2020-06-01: An I/O error occurred 
while sending to the backend.


org.postgresql.util.PSQLException: An I/O error occurred while sending 
to the backend.


When I checked in the postgres log I noticed the below.

2020-06-02 02:09:21.333 CEST db:[xxx] user:[x] [xx(53643)] 
[08006] LOG:  could not send data to client: Broken pipe


2020-06-02 02:09:21.333 CEST db:[xxx] user:[x] [(53643)] 
[08006] STATEMENT:  select . (it just a select query)


2020-06-02 02:09:21.333 CEST db:[xxx] user:[xx] [xx(53643)] 
[08006] FATAL:  connection to client lost


Note: DB name, hostname, username, sql text  morphed in above message 
for security reasons, so kindly consider it.


I ran the above select query from psql prompt and I got the output, 
Time: 7178.926 ms. Ofcource my one is local connection so no broken 
pipe, where as client is using the network so he is getting broken pipe.


     We checked with Network team they are claiming no 
issues with the network, But not sure.


I worked with Unix team and they raised a case to Red hat support and 
got confirmation from them that “no issues observed from OS End”


In-fact Red hat looking for a feedback of Postgres Community.

Version of Postgres :   PostgreSQL 9.6.5 on 
x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 
4.8.5-11), 64-bit


OS      :   Red Hat 
Enterprise Linux Server release 7.2 (Maipo)


We observed “LOG:  could not send data to client: Broken pipe” in 
postgres log from long time , But recently client complained about it. 
So we are in process of fixing it for which we need your help.


     Fyi.. I had performed VACUUM(FULL, ANALYZE, VERBOSE) on 
the table. So the last analyzed is latest.


I already checked in Postgres archive and found below links on the same 
topic, But I could not get the concreate answer to fix the issue, so 
please help.


https://www.postgresql.org/search/?m=1&q=LOG%3A++could+not+send+data+to+client%3A+Broken+pipe&l=2&d=-1&s=i

checked  11,12,13,14,15,16,17,18 mail replies

Let me know if you need any more details from my end.


Is there anything relevant in the OS system log around the time period 
2020-06-02 02:09:21.333 CEST?




Regards,

Narresh



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: LOG: could not send data to client: Broken pipe

[Adrian Klaver]

On 6/1/20 8:41 PM, TALLURI Nareshkumar wrote:

Hello Postgres Support Team,

I need your help to identify the issue and apply the fix for it.

My client is running one query through Application and getting error 
like below.


11:20:46.298 [http-nio-8083-exec-7] ERROR c.s.s.g.a.alcor.pdi.dao.PdiDao 
- While getting pdi from 2019-12-21 to 2020-06-01: An I/O error occurred 
while sending to the backend.


org.postgresql.util.PSQLException: An I/O error occurred while sending 
to the backend.




Should have included in previous post:

What version of JDBC?

What is the application stack?

Client OS?

What is the network distance between the client/application and the server?


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Oracle vs. PostgreSQL - a comment

[Michael Nolan]

I spent about 10 years as an Oracle DBA (back around Oracle 7 and 8) and
the last 20 or so years doing PostgreSQL.

My initial impressions were that Oracle did a better job providing tools
and options that users and DBAs need and PostgreSQL was pretty much
roll-your-own.

Things like being able to copy tables from one database to another or to
restore the dump of a table to another table name in the same database are
things that would make a DBA's life a lot easier.

I worked on a general-purpose web-based tool that could read the PostgreSQL
table structures and provide a form for querying and updating most field
types in a table, but never really took it beyond the persona use basis.
Now that I'm retired, maybe I"ll work on this again.
--
Mike Nolan


>

When to use PARTITION BY HASH?

[Oleksandr Shulgin]

Hi!

I was reading up on declarative partitioning[1] and I'm not sure what could
be a possible application of Hash partitioning.

Is anyone actually using it?  What are typical use cases?  What benefits
does such a partitioning scheme provide?

On its face, it seems that it can only give you a number of tables which
are smaller than the un-partitioned one, but I fail to see how it would
provide any of the potential advantages listed in the documentation.

With a reasonable hash function, the distribution of rows across partitions
should be more or less equal, so I wouldn't expect any of the following to
hold true:
- "...most of the heavily accessed rows of the table are in a single
partition or a small number of partitions."
- "Bulk loads and deletes can be accomplished by adding or removing
partitions...",
etc.

That *might* turn out to be the case with a small number of distinct values
in the partitioning column(s), but then why rely on hash assignment instead
of using PARTITION BY LIST in the first place?

Regards,
-- 
Alex

[1] https://www.postgresql.org/docs/12/ddl-partitioning.html

Re: When to use PARTITION BY HASH?

[MichaelDBA]

Hi,

I use it quite often, since I'm dealing with partitioning keys that have 
high cardinality, ie, high number of different values.  If your 
cardinality is very high, but your spacing between values is not 
uniform, HASH will balance your partitioned tables naturally.  If your 
spacing between values is consistent, perhaps RANGE partitioning would 
be better.


Regards,
Michael Vitale

Oleksandr Shulgin wrote on 6/2/2020 1:17 PM:

Hi!

I was reading up on declarative partitioning[1] and I'm not sure what 
could be a possible application of Hash partitioning.


Is anyone actually using it? What are typical use cases?  What 
benefits does such a partitioning scheme provide?


On its face, it seems that it can only give you a number of tables 
which are smaller than the un-partitioned one, but I fail to see how 
it would provide any of the potential advantages listed in the 
documentation.


With a reasonable hash function, the distribution of rows across 
partitions should be more or less equal, so I wouldn't expect any of 
the following to hold true:
- "...most of the heavily accessed rows of the table are in a single 
partition or a small number of partitions."
- "Bulk loads and deletes can be accomplished by adding or removing 
partitions...",

etc.

That *might* turn out to be the case with a small number of distinct 
values in the partitioning column(s), but then why rely on hash 
assignment instead of using PARTITION BY LIST in the first place?


Regards,
--
Alex

[1] https://www.postgresql.org/docs/12/ddl-partitioning.html

Re: When to use PARTITION BY HASH?

[David G. Johnston]

On Tue, Jun 2, 2020 at 10:17 AM Oleksandr Shulgin <
oleksandr.shul...@zalando.de> wrote:

> That *might* turn out to be the case with a small number of distinct
> values in the partitioning column(s), but then why rely on hash
> assignment instead of using PARTITION BY LIST in the first place?
>
> [1] https://www.postgresql.org/docs/12/ddl-partitioning.html
>

Why the cross-posting? (-performance is oriented toward problem solving,
not theory, so -general is the one and only PostgreSQL list this should
have been sent to)

Anyway, quoting the documentation you linked to:

"When choosing how to partition your table, it's also important to consider
what changes may occur in the future. For example, if you choose to have
one partition per customer and you currently have a small number of large
customers, consider the implications if in several years you instead find
yourself with a large number of small customers. In this case, it may be
better to choose to partition by HASH and choose a reasonable number of
partitions rather than trying to partition by LIST and hoping that the
number of customers does not increase beyond what it is practical to
partition the data by."

Hashing does indeed preclude some of the benefits and introduces others.

I suspect that having a hash function that turns its input into a different
output and checking for equality on the output would be better than trying
to "OR" a partition list together in order to combine multiple inputs onto
the same table.

David J.

Re: When to use PARTITION BY HASH?

[Michel Pelletier]

On Tue, Jun 2, 2020 at 10:17 AM Oleksandr Shulgin <
oleksandr.shul...@zalando.de> wrote:

> Hi!
>
> I was reading up on declarative partitioning[1] and I'm not sure what
> could be a possible application of Hash partitioning.
>
> Is anyone actually using it?  What are typical use cases?  What benefits
> does such a partitioning scheme provide?
>
> On its face, it seems that it can only give you a number of tables which
> are smaller than the un-partitioned one, but I fail to see how it would
> provide any of the potential advantages listed in the documentation.
>

I'm sure there will be many delightful answers to your question, and I look
forward to them!  From my point of view, hash partitioning is very useful
for spreading out high insert/update load.  Yes its' true you end up with
more smaller tables than one big large one, but remember the indexes are
(often) tree data structures.  Smaller trees are faster than bigger trees.
By making the indexes smaller they are faster.  Since the planner can knows
to only examine the specific index it needs, this ends up being a lot
faster.

Postgres can also parallelize queries on partitions.  This is different
from a parallel sequential scan, which can also happen per-partition, so
there are multiple levels of parallel opportunity.

And last that I can think of, you can put the different partitions in
different tablespaces, improving the total IO bandwidth.

-Michel



> With a reasonable hash function, the distribution of rows across
> partitions should be more or less equal, so I wouldn't expect any of the
> following to hold true:
> - "...most of the heavily accessed rows of the table are in a single
> partition or a small number of partitions."
> - "Bulk loads and deletes can be accomplished by adding or removing
> partitions...",
> etc.
>
> That *might* turn out to be the case with a small number of distinct
> values in the partitioning column(s), but then why rely on hash
> assignment instead of using PARTITION BY LIST in the first place?
>
> Regards,
> --
> Alex
>
> [1] https://www.postgresql.org/docs/12/ddl-partitioning.html
>
>

Re: When to use PARTITION BY HASH?

[Stephen Frost]

Greetings,

Please don't cross post to multiple lists without any particular reason
for doing so- pick whichever list makes sense and post to that.

* Oleksandr Shulgin (oleksandr.shul...@zalando.de) wrote:
> I was reading up on declarative partitioning[1] and I'm not sure what could
> be a possible application of Hash partitioning.

Yeah, I tend to agree with this.

> Is anyone actually using it?  What are typical use cases?  What benefits
> does such a partitioning scheme provide?

I'm sure folks are using it but that doesn't make it a good solution.

> On its face, it seems that it can only give you a number of tables which
> are smaller than the un-partitioned one, but I fail to see how it would
> provide any of the potential advantages listed in the documentation.

Having smaller tables can be helpful when it comes to dealing with
things like VACUUM (particularly since, even though we can avoid having
to scan the entire heap, we have to go through the indexes in order to
clean them up and generally larger tables have larger indexes),
however..

> With a reasonable hash function, the distribution of rows across partitions
> should be more or less equal, so I wouldn't expect any of the following to
> hold true:
> - "...most of the heavily accessed rows of the table are in a single
> partition or a small number of partitions."
> - "Bulk loads and deletes can be accomplished by adding or removing
> partitions...",
> etc.
> 
> That *might* turn out to be the case with a small number of distinct values
> in the partitioning column(s), but then why rely on hash assignment instead
> of using PARTITION BY LIST in the first place?

You're entirely correct with this- there's certainly no small number of
situations where you end up with a 'hot' partition when using hashing
(which is true in other RDBMS's too, of course...) and that ends up
being pretty painful to deal with.

Also, you're right that you don't get to do bulk load/drop when using
hash partitioning, which is absolutely one of the largest benefits to
partitioning in the first place, so, yeah, their usefullness is.. rather
limited.  Better to do your own partitioning based on actual usage
patterns that you know and the database's hash function certainly
doesn't.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Oracle vs. PostgreSQL - a comment

[Ron]

On 6/2/20 3:27 AM, Tim Clarke wrote:

On 02/06/2020 09:22, Ron wrote:
The inability to do a point-in-time restoration of a *single* database in 
a multi-db cluster is a serious -- and fundamental -- missing feature 
(never to be implemented because of the fundamental design).


In SQL Server, it's trivial to restore -- including differentials and WAL 
files -- an old copy of a prod database *to a different name* so that you 
now have databases FOO and FOO_OLD in the same instance.


In Postgres, though, you've got to create a new cluster using a new port 
number (which in our case means sending a firewall request through 
channels and waiting two weeks while the RISK team approves opening the 
port -- and they might decline it because it's non-standard -- and then 
the Network team creates a /change order/ and then implements it).


Bottom line: something I can do in an afternoon with SQL Server takes two 
weeks for Postgres.


This has given Postgres a big, fat black eye with our end users.

--
Angular momentum makes the world go 'round.



But that's nothing to do with Postgres; it takes two weeks because you 
have broken procedures imho




Following ISO 2 process (which is a pain) doesn't impact SQL Server like 
it does Postgres.


--
Angular momentum makes the world go 'round.

Re: Oracle vs. PostgreSQL - a comment

[Ron]

On 6/2/20 4:59 AM, Grigory Smolkin wrote:



On 6/2/20 11:22 AM, Ron wrote:
The inability to do a point-in-time restoration of a *single* database in 
a multi-db cluster is a serious -- and fundamental -- missing feature 
(never to be implemented because of the fundamental design).

It is possible via 3rd party tools like pg_probackup and pgbackrest.


pgbackrest does *not* support PITR recovery of individual databases into 
*new* database names in the same cluster (so that the end user can have both 
the current database and an old version at the same time).



--
Angular momentum makes the world go 'round.

Re: Oracle vs. PostgreSQL - a comment

[Stephen Frost]

Greetings,

* Ron (ronljohnso...@gmail.com) wrote:
> On 6/2/20 4:59 AM, Grigory Smolkin wrote:
> >On 6/2/20 11:22 AM, Ron wrote:
> >>The inability to do a point-in-time restoration of a *single* database
> >>in a multi-db cluster is a serious -- and fundamental -- missing feature
> >>(never to be implemented because of the fundamental design).
> >It is possible via 3rd party tools like pg_probackup and pgbackrest.
> 
> pgbackrest does *not* support PITR recovery of individual databases into
> *new* database names in the same cluster (so that the end user can have both
> the current database and an old version at the same time).

No, nothing does as PG doesn't support it as we have one WAL stream for
the entire cluster.

Generally speaking, I discourage having lots of databases under one PG
cluster for exactly these kinds of reasons.  PG's individual clusters
are relatively lightweight, after all.

Thanks,

Stephen


signature.asc
Description: PGP signature

RE: LOG: could not send data to client: Broken pipe

[TALLURI Nareshkumar]

Hello Adrian Klaver,

Here is the answers 


What version of JDBC?   :   9.3-1104-jdbc41

What is the application stack?  :   Java

Client OS?  :   windows 2016

What is the network distance between the client/application and the server?
Reply from 184.6.160.214: bytes=32 time=2ms TTL=57
Reply from 184.6.160.214: bytes=32 time=2ms TTL=57
Reply from 184.6.160.214: bytes=32 time=2ms TTL=57
Reply from 184.6.160.214: bytes=32 time=2ms TTL=57

Ping statistics for 184.6.160.214:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 2ms, Maximum = 2ms, Average = 2ms


Regards,
Narresh




-Original Message-
From: Adrian Klaver  
Sent: Tuesday, June 2, 2020 7:50 PM
To: TALLURI Nareshkumar SgscGtsPasDre ; 
pgsql-gene...@postgresql.org
Subject: Re: LOG: could not send data to client: Broken pipe

On 6/1/20 8:41 PM, TALLURI Nareshkumar wrote:
> Hello Postgres Support Team,
> 
> I need your help to identify the issue and apply the fix for it.
> 
> My client is running one query through Application and getting error 
> like below.
> 
> 11:20:46.298 [http-nio-8083-exec-7] ERROR 
> c.s.s.g.a.alcor.pdi.dao.PdiDao
> - While getting pdi from 2019-12-21 to 2020-06-01: An I/O error 
> occurred while sending to the backend.
> 
> org.postgresql.util.PSQLException: An I/O error occurred while sending 
> to the backend.
> 

Should have included in previous post:

What version of JDBC?

What is the application stack?

Client OS?

What is the network distance between the client/application and the server?


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Oracle vs. PostgreSQL - a comment

[Ron]

On 6/2/20 1:30 PM, Stephen Frost wrote:

Greetings,

* Ron (ronljohnso...@gmail.com) wrote:

On 6/2/20 4:59 AM, Grigory Smolkin wrote:

On 6/2/20 11:22 AM, Ron wrote:

The inability to do a point-in-time restoration of a *single* database
in a multi-db cluster is a serious -- and fundamental -- missing feature
(never to be implemented because of the fundamental design).

It is possible via 3rd party tools like pg_probackup and pgbackrest.

pgbackrest does *not* support PITR recovery of individual databases into
*new* database names in the same cluster (so that the end user can have both
the current database and an old version at the same time).

No, nothing does as PG doesn't support it as we have one WAL stream for
the entire cluster.


Right.  Making WAL files specific to a database should be high on the list 
of priorities.



Generally speaking, I discourage having lots of databases under one PG
cluster for exactly these kinds of reasons.


It's just two... :)


   PG's individual clusters are relatively lightweight, after all.


But require a new port, and Enterprises have Processes that must be followed.

--
Angular momentum makes the world go 'round.

Re: Oracle vs. PostgreSQL - a comment

[Stephen Frost]

Greetings,

* Ron (ronljohnso...@gmail.com) wrote:
> On 6/2/20 1:30 PM, Stephen Frost wrote:
> >* Ron (ronljohnso...@gmail.com) wrote:
> >>On 6/2/20 4:59 AM, Grigory Smolkin wrote:
> >>>On 6/2/20 11:22 AM, Ron wrote:
> The inability to do a point-in-time restoration of a *single* database
> in a multi-db cluster is a serious -- and fundamental -- missing feature
> (never to be implemented because of the fundamental design).
> >>>It is possible via 3rd party tools like pg_probackup and pgbackrest.
> >>pgbackrest does *not* support PITR recovery of individual databases into
> >>*new* database names in the same cluster (so that the end user can have both
> >>the current database and an old version at the same time).
> >No, nothing does as PG doesn't support it as we have one WAL stream for
> >the entire cluster.
> 
> Right.  Making WAL files specific to a database should be high on the list
> of priorities.

That's almost certainly not going to happen.  I'm not aware of anyone
pursuing that nor has there been any discussion on hackers.

In other words, I wouldn't bet on that being something that's going to
happen as there's seemingly very little interest in it among those who
are developing PG and it'd be an awful lot of work for what seems to be
very little actual gain.

> >Generally speaking, I discourage having lots of databases under one PG
> >cluster for exactly these kinds of reasons.
> 
> It's just two... :)
> 
> >   PG's individual clusters are relatively lightweight, after all.
> 
> But require a new port, and Enterprises have Processes that must be followed.

Sure they do.  Automate them.

:)

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: LOG: could not send data to client: Broken pipe

[Adrian Klaver]

On 6/2/20 11:18 AM, TALLURI Nareshkumar wrote:

Hello Adrian Klaver,

Here is the answers


What version of JDBC?   :   9.3-1104-jdbc41

What is the application stack?  :   Java


That is the programming language. I was looking for the actual 
applications used.




Client OS?  :   windows 2016


When I see Windows and interruptions to Postgres I think anti-virus 
software. Is there any running on the client?




What is the network distance between the client/application and the server?


I should have been more specific:

Are the client and server on the same network?

Are the client/server separated by a building, country, etc?


Reply from 184.6.160.214: bytes=32 time=2ms TTL=57
Reply from 184.6.160.214: bytes=32 time=2ms TTL=57
Reply from 184.6.160.214: bytes=32 time=2ms TTL=57
Reply from 184.6.160.214: bytes=32 time=2ms TTL=57

Ping statistics for 184.6.160.214:
 Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
 Minimum = 2ms, Maximum = 2ms, Average = 2ms


Regards,
Narresh




-Original Message-
From: Adrian Klaver 
Sent: Tuesday, June 2, 2020 7:50 PM
To: TALLURI Nareshkumar SgscGtsPasDre ; 
pgsql-gene...@postgresql.org
Subject: Re: LOG: could not send data to client: Broken pipe

On 6/1/20 8:41 PM, TALLURI Nareshkumar wrote:

Hello Postgres Support Team,

I need your help to identify the issue and apply the fix for it.

My client is running one query through Application and getting error
like below.

11:20:46.298 [http-nio-8083-exec-7] ERROR
c.s.s.g.a.alcor.pdi.dao.PdiDao
- While getting pdi from 2019-12-21 to 2020-06-01: An I/O error
occurred while sending to the backend.

org.postgresql.util.PSQLException: An I/O error occurred while sending
to the backend.



Should have included in previous post:

What version of JDBC?

What is the application stack?

Client OS?

What is the network distance between the client/application and the server?


--
Adrian Klaver
adrian.kla...@aklaver.com




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Oracle vs. PostgreSQL - a comment

[Tim Clarke]

On 02/06/2020 19:43, Stephen Frost wrote:
>> But require a new port, and Enterprises have Processes that must be followed.
> Sure they do.  Automate them.
>
> :)
>
> Thanks,
>
> Stephen


+1 for automation, isoX != slow


Tim Clarke MBCS
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420


Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: 
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here https://www.manifest.co.uk/legal/ for further information.

RE: LOG: could not send data to client: Broken pipe

[TALLURI Nareshkumar]

Hello Adrian,


What is the application stack?  -   I will check with Application team 
and get back to you.

When I see Windows and interruptions to Postgres I think anti-virus software. 
Is there any running on the client?    yes , symantec 

Are the client and server on the same network?  yes

Are the client/server separated by a building, country, etc?  same country , 
But different Datacenter


Regards,
Narresh

-Original Message-
From: Adrian Klaver  
Sent: Wednesday, June 3, 2020 12:38 AM
To: TALLURI Nareshkumar SgscGtsPasDre ; 
pgsql-gene...@postgresql.org
Subject: Re: LOG: could not send data to client: Broken pipe

On 6/2/20 11:18 AM, TALLURI Nareshkumar wrote:
> Hello Adrian Klaver,
> 
> Here is the answers
> 
> 
> What version of JDBC? :   9.3-1104-jdbc41
> 
> What is the application stack?:   Java

That is the programming language. I was looking for the actual applications 
used.

> 
> Client OS?:   windows 2016

When I see Windows and interruptions to Postgres I think anti-virus software. 
Is there any running on the client?

> 
> What is the network distance between the client/application and the server?

I should have been more specific:

Are the client and server on the same network?

Are the client/server separated by a building, country, etc?

> Reply from 184.6.160.214: bytes=32 time=2ms TTL=57 Reply from 
> 184.6.160.214: bytes=32 time=2ms TTL=57 Reply from 184.6.160.214: 
> bytes=32 time=2ms TTL=57 Reply from 184.6.160.214: bytes=32 time=2ms 
> TTL=57
> 
> Ping statistics for 184.6.160.214:
>  Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate 
> round trip times in milli-seconds:
>  Minimum = 2ms, Maximum = 2ms, Average = 2ms
> 
> 
> Regards,
> Narresh
> 
> 
> 
> 
> -Original Message-
> From: Adrian Klaver 
> Sent: Tuesday, June 2, 2020 7:50 PM
> To: TALLURI Nareshkumar SgscGtsPasDre 
> ; pgsql-gene...@postgresql.org
> Subject: Re: LOG: could not send data to client: Broken pipe
> 
> On 6/1/20 8:41 PM, TALLURI Nareshkumar wrote:
>> Hello Postgres Support Team,
>>
>> I need your help to identify the issue and apply the fix for it.
>>
>> My client is running one query through Application and getting error 
>> like below.
>>
>> 11:20:46.298 [http-nio-8083-exec-7] ERROR 
>> c.s.s.g.a.alcor.pdi.dao.PdiDao
>> - While getting pdi from 2019-12-21 to 2020-06-01: An I/O error 
>> occurred while sending to the backend.
>>
>> org.postgresql.util.PSQLException: An I/O error occurred while 
>> sending to the backend.
>>
> 
> Should have included in previous post:
> 
> What version of JDBC?
> 
> What is the application stack?
> 
> Client OS?
> 
> What is the network distance between the client/application and the server?
> 
> 
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
> 


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Oracle vs. PostgreSQL - a comment

[Ron]

On 6/2/20 1:56 PM, Tim Clarke wrote:

On 02/06/2020 19:43, Stephen Frost wrote:

But require a new port, and Enterprises have Processes that must be followed.

Sure they do.  Automate them.

:)

Thanks,

Stephen


+1 for automation, isoX != slow
It is when FW rules must be manually approved (and they do review them all), 
then the TASK is converted to a CHANGE and that goes before a CAB meeting.


That's all bypassed with SQL Server and Oracle, though.

--
Angular momentum makes the world go 'round.

Re: Oracle vs. PostgreSQL - a comment

[Ravi Krishna]

> 
> Generally speaking, I discourage having lots of databases under one PG
> cluster for exactly these kinds of reasons.  PG's individual clusters
> are relatively lightweight, after all.
> 

Plus PG does not directly support cross database queries using 3 part name, 
something
sqlserver excels at.

Re: Oracle vs. PostgreSQL - a comment

[Guyren Howe]

On Jun 2, 2020, at 12:45 , Ravi Krishna  wrote:
> 
>> 
>> 
>> Generally speaking, I discourage having lots of databases under one PG
>> cluster for exactly these kinds of reasons.  PG's individual clusters
>> are relatively lightweight, after all.
>> 
> 
> Plus PG does not directly support cross database queries using 3 part name, 
> something
> sqlserver excels at.

Gotta say, not generally a fan of SQL Server, but this is very nice. If I’m 
dealing with a database with 50 small databases on it, it’s an utter pain to 
have to set up fdw connections between every set of databases I want to use 
together.

Any chance of getting this in Postgres? Seems like since the databases are in 
the same program, this ought to be simpler.

Re: Oracle vs. PostgreSQL - a comment

[Stephen Frost]

Greetings,

* Ravi Krishna (sravikris...@comcast.net) wrote:
> > Generally speaking, I discourage having lots of databases under one PG
> > cluster for exactly these kinds of reasons.  PG's individual clusters
> > are relatively lightweight, after all.
> 
> Plus PG does not directly support cross database queries using 3 part name, 
> something
> sqlserver excels at.

Eh, that's something that I think we should be looking at supporting, by
using FDWs, but I haven't tried to figure out how hard it'd be.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Oracle vs. PostgreSQL - a comment

[Christophe Pettus]

> On Jun 2, 2020, at 13:30, Stephen Frost  wrote:
> 
> Eh, that's something that I think we should be looking at supporting, by
> using FDWs, but I haven't tried to figure out how hard it'd be.

Being able to access a FDW that way would rock.

--
-- Christophe Pettus
   x...@thebuild.com

Re: Oracle vs. PostgreSQL - a comment

[Ravi Krishna]

> 
> Eh, that's something that I think we should be looking at supporting, by
> using FDWs, but I haven't tried to figure out how hard it'd be.
> 

How good will that be in performance.

In db2 you can do it using dblinks and that kills performance. isn't FDW  
something like dblink.

The cool part is that in SQLServer the optimizer recognizes 3 part name and 
caching works also.

Re: Oracle vs. PostgreSQL - a comment

[Stephen Frost]

Greetings,

* Ravi Krishna (sravikris...@comcast.net) wrote:
> > Eh, that's something that I think we should be looking at supporting, by
> > using FDWs, but I haven't tried to figure out how hard it'd be.
> 
> How good will that be in performance.
> 
> In db2 you can do it using dblinks and that kills performance. isn't FDW  
> something like dblink.
> 
> The cool part is that in SQLServer the optimizer recognizes 3 part name and 
> caching works also.

I'm sure there's things we can do to improve the performance of the FDW.
Not sure we'll get to a point where we are actually cacheing information
from the far side... but who knows, maybe if we arrange to have a
notification sent whenever certain objects are updated...

These things could be worked on independnetly, of course, no need to
have one done before the other.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Oracle vs. PostgreSQL - a comment

[Guyren Howe]

On Jun 2, 2020, at 14:16 , Stephen Frost  wrote:
> 
> Greetings,

> I'm sure there's things we can do to improve the performance of the FDW.
> Not sure we'll get to a point where we are actually cacheing information
> from the far side... but who knows, maybe if we arrange to have a
> notification sent whenever certain objects are updated...
> 
> These things could be worked on independnetly, of course, no need to
> have one done before the other.

By all means, let’s improve the FDW. But is it practical to make it possible to 
query across databases on the same server,  in a similar manner to SQL Server, 
without needing FDW at all?

Re: Oracle vs. PostgreSQL - a comment

[Thomas Kellerer]

Ron schrieb am 02.06.2020 um 20:38:



   PG's individual clusters are relatively lightweight, after all.


But require a new port, and Enterprises have Processes that must be followed.


I am not 100% sure, but I think you can get around that by putting pgPool or 
pgBouncer
in front and make all connections through that (with a single port)

Re: Oracle vs. PostgreSQL - a comment

[Adam Brusselback]

>  How good will that be in performance.

In my experience, not great. It's definitely better than not having it at
all, but it does not make for quick queries and caused serious
connection overhead when a query referenced that foreign table. I've since
moved to logical replication to improve the situation there.

In SQL Server I had never noticed the difference when I had to do cross-db
queries. There may or may not be a difference, but it was never something
that I had to think about or look up, so I am not sure.

It's something I do still miss though, as it sure was convenient.

Just my $0.02.
-Adam

Re: Oracle vs. PostgreSQL - a comment

[Stephen Frost]

Greetings,

* Guyren Howe (guy...@gmail.com) wrote:
> On Jun 2, 2020, at 14:16 , Stephen Frost  wrote:
> > I'm sure there's things we can do to improve the performance of the FDW.
> > Not sure we'll get to a point where we are actually cacheing information
> > from the far side... but who knows, maybe if we arrange to have a
> > notification sent whenever certain objects are updated...
> > 
> > These things could be worked on independnetly, of course, no need to
> > have one done before the other.
> 
> By all means, let’s improve the FDW. But is it practical to make it possible 
> to query across databases on the same server,  in a similar manner to SQL 
> Server, without needing FDW at all?

If you'd like to try and figure out how to make that work, it could be
theoretically possible, but I seriously doubt it'd be at all straight
forward to do, or that it'd ultimately end up being worth it.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Oracle vs. PostgreSQL - a comment

[Stephen Frost]

Greetings,

* Adam Brusselback (adambrusselb...@gmail.com) wrote:
> >  How good will that be in performance.
> 
> In my experience, not great. It's definitely better than not having it at
> all, but it does not make for quick queries and caused serious
> connection overhead when a query referenced that foreign table. I've since
> moved to logical replication to improve the situation there.

How much of the issue there has been setup/connection time..?  That's
something I've wondered about maybe finding a way to improve on.  Also
depends on if you're using the "run explain first before running the
query" approach with the FDW or if you're actually running analyze on
the foreign tables.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Vanishing unique constraint

[Bernhard Beroun]

Am Montag, Juni 01, 2020 17:27 CEST, schrieb Julien Rouhaud 
:
 On Mon, Jun 1, 2020 at 1:23 PM Bernhard Beroun  wrote:
>
> Am Montag, Juni 01, 2020 12:56 CEST, schrieb Julien Rouhaud 
> :
>
> On Mon, Jun 1, 2020 at 11:15 AM Bernhard Beroun  wrote:
> >
> > Hello,
> >
> > I am experiencing a strange thing on my production database server, which I 
> > can't explain.
> >
> > On my production database server, there is a table called 
> > "label_suggestion" which has a unique constraint on the "name" column. If I 
> > do a "\d+ label_suggestion" I can see, that the unique constraint shows up 
> > with
> >
> > "label_suggestion_name_unique" UNIQUE CONSTRAINT, btree (name)
> >
> > in the output. But when I execute the following query
> >
> > SELECT name FROM label_suggestion GROUP BY name HAVING COUNT(name) > 1
> >
> > I can see that there are actually entries with a duplicate name in the 
> > database, which makes me believe that the unique constraint isn't there at 
> > all. (or at least it's not enforced)
>
> It looks like the underlying index is corrupted. Did you have any
> issue on that server? If the datatype is collatable, another
> possibility would be that the underlying glibc version (or the
> equivalent on your system) was upgraded to a version with different
> ordering for your collation. Recently, glibc 2.28 is a quite likely
> scenario, see for instance
> https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html, but
> it could also be some bug, possibly in earlier version if you updated
> postgres since the initial install.
>
> > Next, I dumped the complete production database (via pg_dumpall) and 
> > applied the dump on my local machine. I did again a "\d+ label_suggestion" 
> > and now the unique constraint doesn't show up anymore.
>
> There's probably an error displayed during the restore. The constraint
> are restored after the data, so there's no way that the constraint can
> be restored if you have duplicated values.
>
> You should definitely find out how you ended up in this situation and
> fix the root cause before trying to manually clean up the data.
>
>
> you are spot on with your failure analysis. I initially had PostgreSQL 9.6 
> running on Debian Stretch (glibc < 2.28) and then migrated to Debian Buster 
> (glibc 2.28) a few months ago.
>
> There's probably an error displayed during the restore. The constraint
> are restored after the data, so there's no way that the constraint can
> be restored if you have duplicated values.
>
> You are absolutely right, there is indeed an error - completely missed that!
>
> Many thanks for your help, Julien - you really helped me with this one. Very 
> much appreciated!
>
> Is there something special I need to consider when cleaning up the duplicated 
> values? My current plan would be to run the migrate script that I've written 
> (which cleans up the mess and get rids of the duplicate values) and then 
> reindex with "REINDEX database ".
>
> Is that right or am I missing something here?

Mmm, so it's been several months since you did the glibc upgrade,
that's quite some time. The best would probably be to reindex all
indexes that depend on a collatable datatype in all databases, and
note which are failing. It's possible that for some tables the index
corruption didn't lead to storing wrong data in the table yet. And
yes for the one which fails, you'll need to manually remove duplicate
values. Note that you'll have to make sure that no index scan is used
when loooking for duplicate (using multiple SET enable_* = off). And
finally reindex those tables or required indexes.
Will do, many thanks! Thanks again for your help, Julien - very much 
appreciated!

Have a nice day,
Bernhard

 

Re: Oracle vs. PostgreSQL - a comment

[Kenneth Marshall]

On Tue, Jun 02, 2020 at 11:18:52PM +0200, Thomas Kellerer wrote:
> Ron schrieb am 02.06.2020 um 20:38:
> >
> >>   PG's individual clusters are relatively lightweight, after all.
> >
> >But require a new port, and Enterprises have Processes that must be followed.
> 
> I am not 100% sure, but I think you can get around that by putting pgPool or 
> pgBouncer
> in front and make all connections through that (with a single port)
> 

Hi,

That was going to be my suggestion and you can connect to local sockets
only.

Regards,
Ken

Re: Oracle vs. PostgreSQL - a comment

[raf]

Ron wrote:

> On 6/2/20 1:56 PM, Tim Clarke wrote:
> > On 02/06/2020 19:43, Stephen Frost wrote:
> > > > But require a new port, and Enterprises have Processes that must be 
> > > > followed.
> > > Sure they do.  Automate them.
> > > 
> > > :)
> > > 
> > > Thanks,
> > > Stephen
> > 
> > +1 for automation, isoX != slow
> It is when FW rules must be manually approved (and they do review them all),
> then the TASK is converted to a CHANGE and that goes before a CAB meeting.
> 
> That's all bypassed with SQL Server and Oracle, though.

Presumably, these processes have to be followed for SQL
Server and Oracle at least once too. If someone with
the same process requirements wanted to use Postgresql
instead, and they knew that they might need multiple
ports, presumably the process could be followed once
for a (possibly pre-allocated) set of ports. It doesn't
have to be any less efficient. Same number of meetings,
just in relation to a different number of ports. The
only problem would be when the processes were followed
for a single port before it was realised that more
ports would be needed later. Then the process would
have to be followed twice, once for the first port, and
once again for all the other ports that might become
necessary.

cheers,
raf

Re: LOG: could not send data to client: Broken pipe

[Adrian Klaver]

On 6/2/20 12:20 PM, TALLURI Nareshkumar wrote:

Hello Adrian,


What is the application stack?  -   I will check with Application team 
and get back to you.

When I see Windows and interruptions to Postgres I think anti-virus software. 
Is there any running on the client?    yes , symantec


Probably should check if AV software is logging anything at the time of 
the broken pipes.




Are the client and server on the same network?  yes

Are the client/server separated by a building, country, etc?  same country , 
But different Datacenter


Regards,
Narresh

-Original Message-
From: Adrian Klaver 
Sent: Wednesday, June 3, 2020 12:38 AM
To: TALLURI Nareshkumar SgscGtsPasDre ; 
pgsql-gene...@postgresql.org
Subject: Re: LOG: could not send data to client: Broken pipe

On 6/2/20 11:18 AM, TALLURI Nareshkumar wrote:

Hello Adrian Klaver,

Here is the answers


What version of JDBC?   :   9.3-1104-jdbc41

What is the application stack?  :   Java


That is the programming language. I was looking for the actual applications 
used.



Client OS?  :   windows 2016


When I see Windows and interruptions to Postgres I think anti-virus software. 
Is there any running on the client?



What is the network distance between the client/application and the server?


I should have been more specific:

Are the client and server on the same network?

Are the client/server separated by a building, country, etc?


Reply from 184.6.160.214: bytes=32 time=2ms TTL=57 Reply from
184.6.160.214: bytes=32 time=2ms TTL=57 Reply from 184.6.160.214:
bytes=32 time=2ms TTL=57 Reply from 184.6.160.214: bytes=32 time=2ms
TTL=57

Ping statistics for 184.6.160.214:
  Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate
round trip times in milli-seconds:
  Minimum = 2ms, Maximum = 2ms, Average = 2ms


Regards,
Narresh




-Original Message-
From: Adrian Klaver 
Sent: Tuesday, June 2, 2020 7:50 PM
To: TALLURI Nareshkumar SgscGtsPasDre
; pgsql-gene...@postgresql.org
Subject: Re: LOG: could not send data to client: Broken pipe

On 6/1/20 8:41 PM, TALLURI Nareshkumar wrote:

Hello Postgres Support Team,

I need your help to identify the issue and apply the fix for it.

My client is running one query through Application and getting error
like below.

11:20:46.298 [http-nio-8083-exec-7] ERROR
c.s.s.g.a.alcor.pdi.dao.PdiDao
- While getting pdi from 2019-12-21 to 2020-06-01: An I/O error
occurred while sending to the backend.

org.postgresql.util.PSQLException: An I/O error occurred while
sending to the backend.



Should have included in previous post:

What version of JDBC?

What is the application stack?

Client OS?

What is the network distance between the client/application and the server?


--
Adrian Klaver
adrian.kla...@aklaver.com




--
Adrian Klaver
adrian.kla...@aklaver.com




--
Adrian Klaver
adrian.kla...@aklaver.com