could not fsync file ""pg_wal/xlogtemp.23229"": Input/output error

[Soni M]

Hi All,
Postgres 11 on RHEL 7.9
We filled out the wal archive disk, and then those messages appear

2021-01-15 01:18:47.284
CST,"replicator","consprod",3510,"[local]",5fdebf22.db6,21,"SELECT",2020-12-19
21:04:02 CST,35/46792889,437317854,PANIC,58030,"could not fsync file
""pg_wal/xlogtemp.3510"": Input/output error",,"select
""_consprod_replication"".forwardConfirm(2, 3, '6233786669', '2021-01-15
00:32:32.828355-06'); ",,,"slon.remoteWorkerThread_2"
2021-01-15 01:18:47.508 CST,,,2461,,5fdebf20.99d,23,,2020-12-19 21:04:00
CST,,0,LOG,0,"server process (PID 3510) was terminated by signal 6:
Aborted","Failed process was running: select
""_consprod_replication"".forwardConfirm(2, 3, '6233786669', '2021-01-15
00:32:32.828355-06'); """
2021-01-15 01:18:47.508 CST,,,2461,,5fdebf20.99d,24,,2020-12-19 21:04:00
CST,,0,LOG,0,"terminating any other active server processes",""
2021-01-15 01:18:47.508
CST,"monitoring","consprod",14606,"10.2.5.12:48018",6001416d.390e,3,"SELECT",2021-01-15
01:17:01 CST,122/339910,0,WARNING,57P02,"terminating connection because of
crash of another server process","The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.","In
a moment you should be able to reconnect to the database and repeat your
command.",,,"psql"


And now I cannot start postgres

2021-01-15 03:04:19.972 CST,,,23229,,6001550d.5abd,6,,2021-01-15 02:40:45
CST,,0,PANIC,58030,"could not fsync file ""pg_wal/xlogtemp.23229"":
Input/output error",""2021-01-15 03:04:19.979
CST,,,23226,,6001550d.5aba,2,,2021-01-15 02:40:45 CST,,0,LOG,0,"startup
process (PID 23229) was terminated by signal 6: Aborted",""
2021-01-15 03:04:19.979 CST,,,23226,,6001550d.5aba,3,,2021-01-15 02:40:45
CST,,0,LOG,0,"aborting startup due to startup process
failure",""
2021-01-15 03:04:20.041 CST,,,23226,,6001550d.5aba,4,,2021-01-15 02:40:45
CST,,0,LOG,0,"database system is shut down",""

Any idea to solve this ?

-- 
Regards,

Soni Maula Harriz

Re: could not fsync file ""pg_wal/xlogtemp.23229"": Input/output error

[Laurenz Albe]

On Fri, 2021-01-15 at 16:13 +0700, Soni M wrote:
> We filled out the wal archive disk, and then those messages appear
> 
> [...]PANIC,58030,"could not fsync file ""pg_wal/xlogtemp.3510"": Input/output 
> error"
> 
> And now I cannot start postgres
> 
> [...]PANIC,58030,"could not fsync file ""pg_wal/xlogtemp.23229"": 
> Input/output error"
> 
> Any idea to solve this ?

Did you add more disk space?

You won't be able to start PostgreSQL unless you do that.

And no, don't start deleting files in the data directory.

What you can do is move the *complete* data directory to another location
with more disk space and start it there.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: could not fsync file ""pg_wal/xlogtemp.23229"": Input/output error

[Soni M]

More disk space now available, but still the error happens and postgres
won't start.
I think it could be corrupted shared buffers, as stated on the log
messages, and perhaps the corruption goes to disk.

On Fri, Jan 15, 2021 at 4:33 PM Laurenz Albe 
wrote:

> On Fri, 2021-01-15 at 16:13 +0700, Soni M wrote:
> > We filled out the wal archive disk, and then those messages appear
> >
> > [...]PANIC,58030,"could not fsync file ""pg_wal/xlogtemp.3510"":
> Input/output error"
> >
> > And now I cannot start postgres
> >
> > [...]PANIC,58030,"could not fsync file ""pg_wal/xlogtemp.23229"":
> Input/output error"
> >
> > Any idea to solve this ?
>
> Did you add more disk space?
>
> You won't be able to start PostgreSQL unless you do that.
>
> And no, don't start deleting files in the data directory.
>
> What you can do is move the *complete* data directory to another location
> with more disk space and start it there.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

-- 
Regards,

Soni Maula Harriz

time-based range partitioning and truncate/delete different timezones

[Niels Jespersen]

Hi all

I need ideas regarding a way to design an insert, truncate/delete, and reinsert 
process on data that is in UTC and database range partitions that are in the 
Europe/Brussels time zone. Therefore, any input is welcome. 

The issue at hand is this. We receive daily file deliveries of time-based 
measurements. The files are zipped csv's. A file covers a UTC-day (e.g. 
2021-01-15 00:00:00+00 to 2021-01-15 23:59:59+00). About 100 million rows a 
day. 

Loaded by Python into a Postgres 12 database table, range-partitioned on 
Europe/Brussels days. Database session in UTC, since timestamps in data has no 
time zone indicator. Most rows hit the same partition, but the last hour (this 
time of year, in the summer it is 2 hours) hits the next partition. Streamed 
directly from zip by psycopg2.cursor.copy_expert. Works perfect. 

Then analysis on the data can proceed. Aggregating stuff on daily or monthly 
basis. This works fine since data is partitioned on our local time zone and we 
want aggregates on the same time zone. 

Then, two weeks later a better delivery comes in covering the same UTC-period. 
Data quality is better, so we want to replace the old data with the new data. 
If data were partitioned on the same time zone boundary as the file covers, 
then a truncate partition, insert new data would be the obvious solution. That 
is no good, since the truncate will wipe an hour too much on one end and an 
hour too little on the other end. 

So what is a better solution? Delete from t where t.timecolumn between a and b, 
and reinsert the new data. My instinct says no, but I cannot really think of a 
good alternative. It is 100 million rows with 5 or 6 numeric columns.

Regards Niels Jespersen

migration from postgres to enterprosedb

[Atul Kumar]

Hi,

I have postgres setup of having version postgres 9.5 and I want to
migrate it to enterprisedb 10.

So Please help me for the same as I am newbee for postgres migration.




Regards,
Atul

upgrade postgres 9.5 to 9.6

[Atul Kumar]

Hi,

I want to upgrade my server from postgres 9.5 to 9.6, but my DB size
is in TBs and I want to do it in minimum downtime (2-3 hours) so
please help me how should I perform it.


Please share the document, if possible, it will be grateful.



Regards,
Atul

Re: upgrade postgres 9.5 to 9.6

[Enrico Pirozzi]

Hi Atul,
you could use logical replication and do an hot upgrade between the 2 servers 
with a zero downtime ;)

If you want to use logical replication between a 9.5 , 9.6 version you could 
use for example pglogical.

You could achieve the same result using an external logic replication tool like 
slony, although pglogical is more powerful.

I hope that this can help you 

Regards,
Enrico

On Fri, 15 Jan 2021 18:47:51 +0530
Atul Kumar  wrote:

> Hi,
> 
> I want to upgrade my server from postgres 9.5 to 9.6, but my DB size
> is in TBs and I want to do it in minimum downtime (2-3 hours) so
> please help me how should I perform it.
> 
> 
> Please share the document, if possible, it will be grateful.
> 
> 
> 
> Regards,
> Atul

-- 
Enrico Pirozzi 

ldap connection parameter lookup

[Zwettler Markus (OIZ)]

Hi,


I want to use ldap to lookup the connection parameters:
https://www.postgresql.org/docs/12/libpq-ldap.html


Do I have to create one static entry per database within pg_service.conf like:

[mydatabase]
ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=mydatabase)


or is there also some kind of generic variant like this (meaning lookup 
connection parameters for the database name I tell you somehow):

[${PGDATABASE}]
ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=${PGDATABASE})


Thanks, Markus

Re: upgrade postgres 9.5 to 9.6

[Atul Kumar]

May you provide me steps to perform it (logical replication upgrade).

I will be thankful to you.


Regards
Atul



On Friday, January 15, 2021, Enrico Pirozzi  wrote:

> Hi Atul,
> you could use logical replication and do an hot upgrade between the 2
> servers with a zero downtime ;)
>
> If you want to use logical replication between a 9.5 , 9.6 version you
> could use for example pglogical.
>
> You could achieve the same result using an external logic replication tool
> like slony, although pglogical is more powerful.
>
> I hope that this can help you
>
> Regards,
> Enrico
>
> On Fri, 15 Jan 2021 18:47:51 +0530
> Atul Kumar  wrote:
>
> > Hi,
> >
> > I want to upgrade my server from postgres 9.5 to 9.6, but my DB size
> > is in TBs and I want to do it in minimum downtime (2-3 hours) so
> > please help me how should I perform it.
> >
> >
> > Please share the document, if possible, it will be grateful.
> >
> >
> >
> > Regards,
> > Atul
>
> --
> Enrico Pirozzi 
>

Re: could not fsync file ""pg_wal/xlogtemp.23229"": Input/output error

[Soni M]

Fix it already, so it was the SAN and vmware issue.

Thanks Everyone.

On Fri, Jan 15, 2021 at 4:40 PM Soni M  wrote:

> More disk space now available, but still the error happens and postgres
> won't start.
> I think it could be corrupted shared buffers, as stated on the log
> messages, and perhaps the corruption goes to disk.
>
> On Fri, Jan 15, 2021 at 4:33 PM Laurenz Albe 
> wrote:
>
>> On Fri, 2021-01-15 at 16:13 +0700, Soni M wrote:
>> > We filled out the wal archive disk, and then those messages appear
>> >
>> > [...]PANIC,58030,"could not fsync file ""pg_wal/xlogtemp.3510"":
>> Input/output error"
>> >
>> > And now I cannot start postgres
>> >
>> > [...]PANIC,58030,"could not fsync file ""pg_wal/xlogtemp.23229"":
>> Input/output error"
>> >
>> > Any idea to solve this ?
>>
>> Did you add more disk space?
>>
>> You won't be able to start PostgreSQL unless you do that.
>>
>> And no, don't start deleting files in the data directory.
>>
>> What you can do is move the *complete* data directory to another location
>> with more disk space and start it there.
>>
>> Yours,
>> Laurenz Albe
>> --
>> Cybertec | https://www.cybertec-postgresql.com
>>
>>
>
> --
> Regards,
>
> Soni Maula Harriz
>


-- 
Regards,

Soni Maula Harriz

Re: upgrade postgres 9.5 to 9.6

[Thomas Kellerer]

Atul Kumar schrieb am 15.01.2021 um 14:17:
> I want to upgrade my server from postgres 9.5 to 9.6, but my DB size
> is in TBs and I want to do it in minimum downtime (2-3 hours) so
> please help me how should I perform it.
>
>
> Please share the document, if possible, it will be grateful.

pg_upgrade with the --link option should only take a few minutes.


Is there a reason you are upgrading to 9.6 and not 12 or 13?

9.6 will be end-of-live this November (in 10 months), you will have to do 
another upgrade then.

Thomas

Accounting for between table correlation

[Alexander Stoddard]

I am having ongoing trouble with a pair of tables, the design of which is
beyond my control.

There is a 'primary' table with hundreds of millions of rows. There is then
a 'subclass' table ~ 10% of the primary which has additional fields. The
tables logically share a primary key field (although that is not annotated
in the schema).

Membership of the subclass table has high correlation with fields in the
primary table - it is very much not random. It seems query plans where the
two tables are joined are 'unstable'. Over time very different plans can
result  even for unchanged queries and some plans are exceedingly
inefficient.

I think what is going on is that the query planner assumes many fewer rows
are going to join to the subtable than actually do (because of the strong
correlation).

Can anyone offer any advice on dealing with this scenario (or better
diagnosing it)?

Thank you,
Alex

Re: Accounting for between table correlation

[Ron]

On 1/15/21 9:19 AM, Alexander Stoddard wrote:
I am having ongoing trouble with a pair of tables, the design of which is 
beyond my control.


There is a 'primary' table with hundreds of millions of rows. There is 
then a 'subclass' table ~ 10% of the primary which has additional fields. 
The tables logically share a primary key field (although that is not 
annotated in the schema).


Membership of the subclass table has high correlation with fields in the 
primary table - it is very much not random. It seems query plans where the 
two tables are joined are 'unstable'. Over time very different plans can 
result  even for unchanged queries and some plans are exceedingly inefficient.


I think what is going on is that the query planner assumes many fewer rows 
are going to join to the subtable than actually do (because of the strong 
correlation).


Can anyone offer any advice on dealing with this scenario (or better 
diagnosing it)?


Do the tables get analyzed on a regular basis?

--
Angular momentum makes the world go 'round.

Re: Accounting for between table correlation

[Atul Kumar]

Hi Alexander,

As per Ron, you are not supposed to ask your questions here.

As According to him, we should keep on doing research on internet rather
than asking for support directly even you have done enough research and
until unless “Ron” won’t be satisfied you have to do keep on researching.





Regards
Atul


On Friday, January 15, 2021, Ron  wrote:

> On 1/15/21 9:19 AM, Alexander Stoddard wrote:
>
>> I am having ongoing trouble with a pair of tables, the design of which is
>> beyond my control.
>>
>> There is a 'primary' table with hundreds of millions of rows. There is
>> then a 'subclass' table ~ 10% of the primary which has additional fields.
>> The tables logically share a primary key field (although that is not
>> annotated in the schema).
>>
>> Membership of the subclass table has high correlation with fields in the
>> primary table - it is very much not random. It seems query plans where the
>> two tables are joined are 'unstable'. Over time very different plans can
>> result  even for unchanged queries and some plans are exceedingly
>> inefficient.
>>
>> I think what is going on is that the query planner assumes many fewer
>> rows are going to join to the subtable than actually do (because of the
>> strong correlation).
>>
>> Can anyone offer any advice on dealing with this scenario (or better
>> diagnosing it)?
>>
>
> Do the tables get analyzed on a regular basis?
>
> --
> Angular momentum makes the world go 'round.
>
>
>

Re: upgrade postgres 9.5 to 9.6

[Ron]

On 1/15/21 9:17 AM, Thomas Kellerer wrote:
[snip]

Is there a reason you are upgrading to 9.6 and not 12 or 13?


The application software vendor might only certify the application on 9.6.
Or /management/, in their infinite "wisdom", might insist on a One-version 
upgrade.
Or 9.6 might be the default version in the distro they're migrating to, and 
Security Policy dictates that No Outside Software is allowed.  (Last year, I 
watched in horror as a database was upgraded from 8.4 *to 9.2* for that very 
reason.)



9.6 will be end-of-live this November (in 10 months), you will have to do 
another upgrade then.


You'd be shocked to see how much data is pumped through *old* Postgresql 
databases.  "It works, so we're not going to fix it." And they don't want to 
pony up for a new version of the application, and spend the money (manpower 
and h/w resources) validating it.


--
Angular momentum makes the world go 'round.

Best tools to monitor and fine tune postgres

[svsn raju]

Hi All,
Can someone please suggest some tools to monitor and tune postgres

Sent from Yahoo Mail on Android

Re: Accounting for between table correlation

[o1bigtenor]

On Fri, Jan 15, 2021 at 9:29 AM Atul Kumar  wrote:
>
> Hi Alexander,
>
> As per Ron, you are not supposed to ask your questions here.
>
> As According to him, we should keep on doing research on internet rather than 
> asking for support directly even you have done enough research and until 
> unless “Ron” won’t be satisfied you have to do keep on researching.
>
>
Mr Atul

With respect - - - -
When you asked your question I thought you had a valid question but
really hadn't done any research for an answer.
The list is here to help you help yourself.
I have found myself answering my own questions sometimes. But that
also means that I now have a better idea of what's going on.
Just demanding answers with all the steps - - - - well - - - you are
hindering your own learning.
I would suggest that instead of barging into others threads
complaining that you didn't get the answer you wanted that you try
actually working on your own problem.
(I am not a senior listee here - - - - just not happy with someone who
is grumbly AND doesn't want to help themselves!)

(To the admins - - - - if I have overstepped - - - please advise!)

Regards

Re: Accounting for between table correlation

[Atul Kumar]

Hi Ron,

I have a simple mindset that If I don’t know about something about anyone’s
query I don’t respond.

Please start learning to not to interrupt or raising useless questions/
phrases on someone’s query bcz it makes you judgemental.

So please take an advice to not to interrupt if you can’t help. It would be
helpful for all of us.



Regards
Atul


On Friday, January 15, 2021, o1bigtenor  wrote:

> On Fri, Jan 15, 2021 at 9:29 AM Atul Kumar  wrote:
> >
> > Hi Alexander,
> >
> > As per Ron, you are not supposed to ask your questions here.
> >
> > As According to him, we should keep on doing research on internet rather
> than asking for support directly even you have done enough research and
> until unless “Ron” won’t be satisfied you have to do keep on researching.
> >
> >
> Mr Atul
>
> With respect - - - -
> When you asked your question I thought you had a valid question but
> really hadn't done any research for an answer.
> The list is here to help you help yourself.
> I have found myself answering my own questions sometimes. But that
> also means that I now have a better idea of what's going on.
> Just demanding answers with all the steps - - - - well - - - you are
> hindering your own learning.
> I would suggest that instead of barging into others threads
> complaining that you didn't get the answer you wanted that you try
> actually working on your own problem.
> (I am not a senior listee here - - - - just not happy with someone who
> is grumbly AND doesn't want to help themselves!)
>
> (To the admins - - - - if I have overstepped - - - please advise!)
>
> Regards
>

Re: Accounting for between table correlation

[David G. Johnston]

On Fri, Jan 15, 2021 at 8:44 AM Atul Kumar  wrote:

> Hi Ron,
>
> I have a simple mindset that If I don’t know about something about
> anyone’s query I don’t respond.
>
> Please start learning to not to interrupt or raising useless questions/
> phrases on someone’s query bcz it makes you judgemental.
>
> So please take an advice to not to interrupt if you can’t help. It would
> be helpful for all of us.
>

This advice isn't all that helpful as it presumes that the author of the
response actually understands that what they are writing is not helpful.
That isn't always obvious to the writer, and if it was it probably would
not have been written.  Instead, if you find a piece of advice to be
unhelpful, and feel the need to say so, be explicit about why you believe
the response was not helpful.

As to the question at hand - it is correct that "analyze" being run or not
doesn't really matter here - PostgreSQL doesn't have cross-table statistics
that can be updated.  That is about the most one can glean from the limited
information the OP provided.  Now, if you can place the columns on the same
table, say in a materialized view, and then use the somewhat recent
multi-column statistics feature, there is probably room for improvement.

Otherwise, the question is basically an open-ended one and maybe someone
responds with some rules-of-thumb, or maybe they don't.

David J.

Re: Best tools to monitor and fine tune postgres

[Atul Kumar]

Hi o1bigtenor,

Now what will you say about below query that he should have come with some
research before asking here ?

On Friday, January 15, 2021, svsn raju  wrote:

> Hi All,
>
> Can someone please suggest some tools to monitor and tune postgres
>
>
> Sent from Yahoo Mail on Android
> 
>

Re: Best tools to monitor and fine tune postgres

[David G. Johnston]

On Fri, Jan 15, 2021 at 8:56 AM Atul Kumar  wrote:

> Hi o1bigtenor,
>
> Now what will you say about below query that he should have come with some
> research before asking here ?
>

Yes.  The question gets asked frequently so if one searches the archive
usually you'll get a thread from the past 6 months or so which is usually
recent enough.  Plus lots of people publish blog posts and articles on the
topic, which are probably more well written and researched than
off-the-cuff replies to a mailing list would be.  At least posting "hey, I
found these three tools, and am leaning toward such-and-such because of
reasons, does anyone have any thoughts I should consider?" would should the
effort and allow for better responses since at least some background is
given.  Knowing generally how the poster uses PostgreSQL also helps and
should be included.

A posting like this I'll usually just ignore since I know that answers
already exist to be found, and it is not something that I have chosen to
become a "librarian/interactive assistant" for - I relegate to the
documentation for that.

David J.

Re: Accounting for between table correlation

[Thomas Kellerer]

Atul Kumar schrieb am 15.01.2021 um 16:29:

As per Ron, you are not supposed to ask your questions here.

As According to him, we should keep on doing research on internet
rather than asking for support directly even you have done enough
research and until unless “Ron” won’t be satisfied you have to do
keep on researching.


Ron's question was perfectly valid.

Missing and wrong statistics are one reason for the planner to choose a bad 
execution plan.

Maybe there are many "idle in transaction" sessions that prevent autovacuum 
from properly
analyzing those tables. Or maybe for some unknown reason autovacuum was turned 
off.
Or maybe they receive a lot of bulk loads which would require a manual
analyze.

So the question "are they analyzed on a regular basis" is a valid point and 
nowhere
did Ron say that the OP didn't do enough research. Ron was merely trying to
rule out one of the more obvious reasons.

Thomas

Re: ldap connection parameter lookup

[Laurenz Albe]

On Fri, 2021-01-15 at 14:09 +, Zwettler Markus (OIZ) wrote: 
> I want to use ldap to lookup the connection parameters:
> https://www.postgresql.org/docs/12/libpq-ldap.html
>  
>  
> Do I have to create one static entry per database within pg_service.conf like:
>  
> [mydatabase]
> ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=mydatabase)
>  
>  
> or is there also some kind of generic variant like this (meaning lookup 
> connection parameters for the database name I tell you somehow):
>  
> [${PGDATABASE}]
> ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=${PGDATABASE})

I proposed something like that a while ago:
https://postgr.es/m/D960CB61B694CF459DCFB4B0128514C2F3442B%40exadv11.host.magwien.gv.at
but it was rejected.

Perhaps you could come up with a better version.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Best tools to monitor and fine tune postgres

[Atul Kumar]

Again Thanks alot David for ur response.

You expect everyone to be perfect which cannot happen.

And related to thread, if someone has joined this thread today then it is
obvious that he may not be able to find the previous threads as well.




Regards
Atul

On Friday, January 15, 2021, David G. Johnston 
wrote:

> On Fri, Jan 15, 2021 at 8:56 AM Atul Kumar  wrote:
>
>> Hi o1bigtenor,
>>
>> Now what will you say about below query that he should have come with
>> some research before asking here ?
>>
>
> Yes.  The question gets asked frequently so if one searches the archive
> usually you'll get a thread from the past 6 months or so which is usually
> recent enough.  Plus lots of people publish blog posts and articles on the
> topic, which are probably more well written and researched than
> off-the-cuff replies to a mailing list would be.  At least posting "hey, I
> found these three tools, and am leaning toward such-and-such because of
> reasons, does anyone have any thoughts I should consider?" would should the
> effort and allow for better responses since at least some background is
> given.  Knowing generally how the poster uses PostgreSQL also helps and
> should be included.
>
> A posting like this I'll usually just ignore since I know that answers
> already exist to be found, and it is not something that I have chosen to
> become a "librarian/interactive assistant" for - I relegate to the
> documentation for that.
>
> David J.
>
>

Re: Accounting for between table correlation

[David G. Johnston]

On Fri, Jan 15, 2021 at 9:10 AM Thomas Kellerer  wrote:

> Atul Kumar schrieb am 15.01.2021 um 16:29:
> > As per Ron, you are not supposed to ask your questions here.
> >
> > As According to him, we should keep on doing research on internet
> > rather than asking for support directly even you have done enough
> > research and until unless “Ron” won’t be satisfied you have to do
> > keep on researching.
>
> Ron's question was perfectly valid.
>
> Missing and wrong statistics are one reason for the planner to choose a
> bad execution plan.
>

Yeah, at first blush I didn't think analyze really mattered (and it mostly
doesn't because while you can keep the statistics up-to-date the
multi-table nature of the problem means they are only marginally helpful
here), but that just points out the under-specified nature of the original
posting.  Taken as a simple question of "is there a way to work around the
lack of multi-table statistics" the analyze, and even the specific queries,
don't matter all that much.  But it also would be much more useful if the
OP would choose a single problematic query and show the schema, query, and
explain results, hopefully both good and bad, and comment on how analyze
seems to affect the plan choice.  But for the general question about
overcoming our statistics limitations the analyze point is not relevant.

David J.

Re: Best tools to monitor and fine tune postgres

[Hemil Ruparel]

Refer to this page for archives: https://www.postgresql.org/list/

On Fri, Jan 15, 2021 at 9:55 PM Atul Kumar  wrote:

> Again Thanks alot David for ur response.
>
> You expect everyone to be perfect which cannot happen.
>
> And related to thread, if someone has joined this thread today then it is
> obvious that he may not be able to find the previous threads as well.
>
>
>
>
> Regards
> Atul
>
> On Friday, January 15, 2021, David G. Johnston 
> wrote:
>
>> On Fri, Jan 15, 2021 at 8:56 AM Atul Kumar  wrote:
>>
>>> Hi o1bigtenor,
>>>
>>> Now what will you say about below query that he should have come with
>>> some research before asking here ?
>>>
>>
>> Yes.  The question gets asked frequently so if one searches the archive
>> usually you'll get a thread from the past 6 months or so which is usually
>> recent enough.  Plus lots of people publish blog posts and articles on the
>> topic, which are probably more well written and researched than
>> off-the-cuff replies to a mailing list would be.  At least posting "hey, I
>> found these three tools, and am leaning toward such-and-such because of
>> reasons, does anyone have any thoughts I should consider?" would should the
>> effort and allow for better responses since at least some background is
>> given.  Knowing generally how the poster uses PostgreSQL also helps and
>> should be included.
>>
>> A posting like this I'll usually just ignore since I know that answers
>> already exist to be found, and it is not something that I have chosen to
>> become a "librarian/interactive assistant" for - I relegate to the
>> documentation for that.
>>
>> David J.
>>
>>

Re: Best tools to monitor and fine tune postgres

[Atul Kumar]

And basically you are improving your knowledge as you are expecting those
questions the answers of which you dont know about.

Thats why you seem to not to be helpful and somehow stopping others to not
to be helpful.

I don’t find find it wrong if any new guy asking the question that has been
repeated 1000 times earlier, if I know the answer I WILL RESPOND 1000
TIMES, “unlike you”.





On Friday, January 15, 2021, Atul Kumar  wrote:

> Again Thanks alot David for ur response.
>
> You expect everyone to be perfect which cannot happen.
>
> And related to thread, if someone has joined this thread today then it is
> obvious that he may not be able to find the previous threads as well.
>
>
>
>
> Regards
> Atul
>
> On Friday, January 15, 2021, David G. Johnston 
> wrote:
>
>> On Fri, Jan 15, 2021 at 8:56 AM Atul Kumar  wrote:
>>
>>> Hi o1bigtenor,
>>>
>>> Now what will you say about below query that he should have come with
>>> some research before asking here ?
>>>
>>
>> Yes.  The question gets asked frequently so if one searches the archive
>> usually you'll get a thread from the past 6 months or so which is usually
>> recent enough.  Plus lots of people publish blog posts and articles on the
>> topic, which are probably more well written and researched than
>> off-the-cuff replies to a mailing list would be.  At least posting "hey, I
>> found these three tools, and am leaning toward such-and-such because of
>> reasons, does anyone have any thoughts I should consider?" would should the
>> effort and allow for better responses since at least some background is
>> given.  Knowing generally how the poster uses PostgreSQL also helps and
>> should be included.
>>
>> A posting like this I'll usually just ignore since I know that answers
>> already exist to be found, and it is not something that I have chosen to
>> become a "librarian/interactive assistant" for - I relegate to the
>> documentation for that.
>>
>> David J.
>>
>>

Re: Best tools to monitor and fine tune postgres

[Hemil Ruparel]

People in any mailing lists are not free consultants. They expect their
time to be valued and they expect the OP to at least do some research
before posting

On Fri, Jan 15, 2021 at 9:56 PM Hemil Ruparel 
wrote:

> Refer to this page for archives: https://www.postgresql.org/list/
>
> On Fri, Jan 15, 2021 at 9:55 PM Atul Kumar  wrote:
>
>> Again Thanks alot David for ur response.
>>
>> You expect everyone to be perfect which cannot happen.
>>
>> And related to thread, if someone has joined this thread today then it is
>> obvious that he may not be able to find the previous threads as well.
>>
>>
>>
>>
>> Regards
>> Atul
>>
>> On Friday, January 15, 2021, David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> On Fri, Jan 15, 2021 at 8:56 AM Atul Kumar 
>>> wrote:
>>>
 Hi o1bigtenor,

 Now what will you say about below query that he should have come with
 some research before asking here ?

>>>
>>> Yes.  The question gets asked frequently so if one searches the archive
>>> usually you'll get a thread from the past 6 months or so which is usually
>>> recent enough.  Plus lots of people publish blog posts and articles on the
>>> topic, which are probably more well written and researched than
>>> off-the-cuff replies to a mailing list would be.  At least posting "hey, I
>>> found these three tools, and am leaning toward such-and-such because of
>>> reasons, does anyone have any thoughts I should consider?" would should the
>>> effort and allow for better responses since at least some background is
>>> given.  Knowing generally how the poster uses PostgreSQL also helps and
>>> should be included.
>>>
>>> A posting like this I'll usually just ignore since I know that answers
>>> already exist to be found, and it is not something that I have chosen to
>>> become a "librarian/interactive assistant" for - I relegate to the
>>> documentation for that.
>>>
>>> David J.
>>>
>>>

Re: Best tools to monitor and fine tune postgres

[Hemil Ruparel]

http://catb.org/~esr/faqs/smart-questions.html

On Fri, Jan 15, 2021 at 9:58 PM Atul Kumar  wrote:

> And basically you are improving your knowledge as you are expecting those
> questions the answers of which you dont know about.
>
> Thats why you seem to not to be helpful and somehow stopping others to not
> to be helpful.
>
> I don’t find find it wrong if any new guy asking the question that has
> been repeated 1000 times earlier, if I know the answer I WILL RESPOND 1000
> TIMES, “unlike you”.
>
>
>
>
>
> On Friday, January 15, 2021, Atul Kumar  wrote:
>
>> Again Thanks alot David for ur response.
>>
>> You expect everyone to be perfect which cannot happen.
>>
>> And related to thread, if someone has joined this thread today then it is
>> obvious that he may not be able to find the previous threads as well.
>>
>>
>>
>>
>> Regards
>> Atul
>>
>> On Friday, January 15, 2021, David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> On Fri, Jan 15, 2021 at 8:56 AM Atul Kumar 
>>> wrote:
>>>
 Hi o1bigtenor,

 Now what will you say about below query that he should have come with
 some research before asking here ?

>>>
>>> Yes.  The question gets asked frequently so if one searches the archive
>>> usually you'll get a thread from the past 6 months or so which is usually
>>> recent enough.  Plus lots of people publish blog posts and articles on the
>>> topic, which are probably more well written and researched than
>>> off-the-cuff replies to a mailing list would be.  At least posting "hey, I
>>> found these three tools, and am leaning toward such-and-such because of
>>> reasons, does anyone have any thoughts I should consider?" would should the
>>> effort and allow for better responses since at least some background is
>>> given.  Knowing generally how the poster uses PostgreSQL also helps and
>>> should be included.
>>>
>>> A posting like this I'll usually just ignore since I know that answers
>>> already exist to be found, and it is not something that I have chosen to
>>> become a "librarian/interactive assistant" for - I relegate to the
>>> documentation for that.
>>>
>>> David J.
>>>
>>>

Re: Best tools to monitor and fine tune postgres

[Hemil Ruparel]

A quote from the aforementioned article. Do read. Worth your time

```

Hackers have a reputation for meeting simple questions with what looks like
hostility or arrogance. It sometimes looks like we're reflexively rude to
newbies and the ignorant. But this isn't really true.

What we are, unapologetically, is hostile to people who seem to be
unwilling to think or to do their own homework before asking questions.
People like that are time sinks — they take without giving back, and they
waste time we could have spent on another question more interesting and
another person more worthy of an answer. We call people like this “losers”
(and for historical reasons we sometimes spell it “lusers”).

```

On Fri, Jan 15, 2021 at 10:01 PM Hemil Ruparel 
wrote:

> http://catb.org/~esr/faqs/smart-questions.html
>
> On Fri, Jan 15, 2021 at 9:58 PM Atul Kumar  wrote:
>
>> And basically you are improving your knowledge as you are expecting those
>> questions the answers of which you dont know about.
>>
>> Thats why you seem to not to be helpful and somehow stopping others to
>> not to be helpful.
>>
>> I don’t find find it wrong if any new guy asking the question that has
>> been repeated 1000 times earlier, if I know the answer I WILL RESPOND 1000
>> TIMES, “unlike you”.
>>
>>
>>
>>
>>
>> On Friday, January 15, 2021, Atul Kumar  wrote:
>>
>>> Again Thanks alot David for ur response.
>>>
>>> You expect everyone to be perfect which cannot happen.
>>>
>>> And related to thread, if someone has joined this thread today then it
>>> is obvious that he may not be able to find the previous threads as well.
>>>
>>>
>>>
>>>
>>> Regards
>>> Atul
>>>
>>> On Friday, January 15, 2021, David G. Johnston <
>>> david.g.johns...@gmail.com> wrote:
>>>
 On Fri, Jan 15, 2021 at 8:56 AM Atul Kumar 
 wrote:

> Hi o1bigtenor,
>
> Now what will you say about below query that he should have come with
> some research before asking here ?
>

 Yes.  The question gets asked frequently so if one searches the archive
 usually you'll get a thread from the past 6 months or so which is usually
 recent enough.  Plus lots of people publish blog posts and articles on the
 topic, which are probably more well written and researched than
 off-the-cuff replies to a mailing list would be.  At least posting "hey, I
 found these three tools, and am leaning toward such-and-such because of
 reasons, does anyone have any thoughts I should consider?" would should the
 effort and allow for better responses since at least some background is
 given.  Knowing generally how the poster uses PostgreSQL also helps and
 should be included.

 A posting like this I'll usually just ignore since I know that answers
 already exist to be found, and it is not something that I have chosen to
 become a "librarian/interactive assistant" for - I relegate to the
 documentation for that.

 David J.

Re: Best tools to monitor and fine tune postgres

[David G. Johnston]

On Fri, Jan 15, 2021 at 9:25 AM Atul Kumar  wrote:

> You expect everyone to be perfect which cannot happen.
>
> And related to thread, if someone has joined this thread today then it is
> obvious that he may not be able to find the previous threads as well.
>

The quality of a response is pretty highly positively correlated to the
quality of the request.  I don't expect people to be perfect, I'm just
describing those things that increase request quality.

"Being able to demonstrate being able to locate existing information" isn't
a prerequisite for receiving a response, but I also don't feel bad when I
don't respond because of the lack, or when my response is to point people
to that already existing information.

David J.

Re: Best tools to monitor and fine tune postgres

[David G. Johnston]

On Fri, Jan 15, 2021 at 9:28 AM Atul Kumar  wrote:

> I don’t find find it wrong if any new guy asking the question that has
> been repeated 1000 times earlier, if I know the answer I WILL RESPOND 1000
> TIMES, “unlike you”.
>

At some point that becomes both personally disruptive and bad for the
community.  Intentionally not answering and letting someone else answer
(this being a public forum) is how other members of the community will end
up growing.  If you answer questions well beneath your capabilities you
don't grow while someone else, who is a bit uncertain, giving the same
response, will receive positive feedback when their same answer is shown to
be correct.  And given the safety net of others chiming in for incorrect
(or incomplete) answers it is a safe forum in which to do those kinds of
things.  If anything giving honest but wrong answers and receiving
corrective comments is a virtue of this community.

David J.

Re: Best tools to monitor and fine tune postgres

[Adrian Klaver]

On 1/15/21 7:37 AM, svsn raju wrote:

Hi All,

Can someone please suggest some tools to monitor and tune postgres


Postgres numeric version?
Community version, forked version, cloud version(e.g. AWS)?
OS and version you will be running tools on?





Sent from Yahoo Mail on Android 




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

Re: Best tools to monitor and fine tune postgres

[Atul Kumar]

Yes you are right. And it seems for those that ignore queries to answer.

Your mates have already said that they ignore answering repeated questions.

On Friday, January 15, 2021, Hemil Ruparel 
wrote:

> http://catb.org/~esr/faqs/smart-questions.html
>
> On Fri, Jan 15, 2021 at 9:58 PM Atul Kumar  wrote:
>
>> And basically you are improving your knowledge as you are expecting those
>> questions the answers of which you dont know about.
>>
>> Thats why you seem to not to be helpful and somehow stopping others to
>> not to be helpful.
>>
>> I don’t find find it wrong if any new guy asking the question that has
>> been repeated 1000 times earlier, if I know the answer I WILL RESPOND 1000
>> TIMES, “unlike you”.
>>
>>
>>
>>
>>
>> On Friday, January 15, 2021, Atul Kumar  wrote:
>>
>>> Again Thanks alot David for ur response.
>>>
>>> You expect everyone to be perfect which cannot happen.
>>>
>>> And related to thread, if someone has joined this thread today then it
>>> is obvious that he may not be able to find the previous threads as well.
>>>
>>>
>>>
>>>
>>> Regards
>>> Atul
>>>
>>> On Friday, January 15, 2021, David G. Johnston <
>>> david.g.johns...@gmail.com> wrote:
>>>
 On Fri, Jan 15, 2021 at 8:56 AM Atul Kumar 
 wrote:

> Hi o1bigtenor,
>
> Now what will you say about below query that he should have come with
> some research before asking here ?
>

 Yes.  The question gets asked frequently so if one searches the archive
 usually you'll get a thread from the past 6 months or so which is usually
 recent enough.  Plus lots of people publish blog posts and articles on the
 topic, which are probably more well written and researched than
 off-the-cuff replies to a mailing list would be.  At least posting "hey, I
 found these three tools, and am leaning toward such-and-such because of
 reasons, does anyone have any thoughts I should consider?" would should the
 effort and allow for better responses since at least some background is
 given.  Knowing generally how the poster uses PostgreSQL also helps and
 should be included.

 A posting like this I'll usually just ignore since I know that answers
 already exist to be found, and it is not something that I have chosen to
 become a "librarian/interactive assistant" for - I relegate to the
 documentation for that.

 David J.

Re: Accounting for between table correlation

[Alexander Stoddard]

Unfortunately I'm not free to share the specific schema or the query plans.
They derive from an upstream vendor that is 'protective' of their data
model. To get to a proper example I'll need to recreate the behavior with
generic data in a generified schema.

I apologize for being frustratingly vague. I do feel like an idiot for not
at least saying this was with version PG 11.10.
It has been beneficial to me that the replies so far appear to validate my
understanding that no version of postgres has cross table correlation
statistics in the planner.

Analyze is done frequently, and I think at sufficient sample size. The
context is in a large data analysis setting and the data is changed via
bulk ETL not OLTP. The effect on analyzing is just instability - the plan
can flip in either direction (without underlying data changing) between
giving an answer in minutes and timing out after 10s of hours. That could
be indicative of too small a sample but I think in this case it is more the
statistics aren't useful because it is cross table correlation that can't
be accounted for.

The 'fast plans' use parallel seq scans. The 'slow plans' is using index
scans. It appears a good query plan correctly predicts it should be bulk
processing the tables but bad ones get fooled into trashing (hard disk, not
SSD) by mispredicting too few rows to join between the tables.

If anyone has similar experiences and is generous enough with their time to
share possible solutions/work arounds then I'm most grateful. If my
description is too vague to be worthwhile answering then I quite understand
and apologize for the time wasted in reading.

Thank you.





On Fri, Jan 15, 2021 at 10:26 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Fri, Jan 15, 2021 at 9:10 AM Thomas Kellerer  wrote:
>
>> Atul Kumar schrieb am 15.01.2021 um 16:29:
>> > As per Ron, you are not supposed to ask your questions here.
>> >
>> > As According to him, we should keep on doing research on internet
>> > rather than asking for support directly even you have done enough
>> > research and until unless “Ron” won’t be satisfied you have to do
>> > keep on researching.
>>
>> Ron's question was perfectly valid.
>>
>> Missing and wrong statistics are one reason for the planner to choose a
>> bad execution plan.
>>
>
> Yeah, at first blush I didn't think analyze really mattered (and it mostly
> doesn't because while you can keep the statistics up-to-date the
> multi-table nature of the problem means they are only marginally helpful
> here), but that just points out the under-specified nature of the original
> posting.  Taken as a simple question of "is there a way to work around the
> lack of multi-table statistics" the analyze, and even the specific queries,
> don't matter all that much.  But it also would be much more useful if the
> OP would choose a single problematic query and show the schema, query, and
> explain results, hopefully both good and bad, and comment on how analyze
> seems to affect the plan choice.  But for the general question about
> overcoming our statistics limitations the analyze point is not relevant.
>
> David J.
>
>

Re: Accounting for between table correlation

[Adrian Klaver]

On 1/15/21 9:21 AM, Alexander Stoddard wrote:
Unfortunately I'm not free to share the specific schema or the query 
plans. They derive from an upstream vendor that is 'protective' of their 
data model. To get to a proper example I'll need to recreate the 
behavior with generic data in a generified schema.


I apologize for being frustratingly vague. I do feel like an idiot for 
not at least saying this was with version PG 11.10.
It has been beneficial to me that the replies so far appear to validate 
my understanding that no version of postgres has cross table correlation 
statistics in the planner.


Analyze is done frequently, and I think at sufficient sample size. The 
context is in a large data analysis setting and the data is changed via 
bulk ETL not OLTP. The effect on analyzing is just instability - the 
plan can flip in either direction (without underlying data changing) 
between giving an answer in minutes and timing out after 10s of hours. 
That could be indicative of too small a sample but I think in this case 
it is more the statistics aren't useful because it is cross table 
correlation that can't be accounted for.


So to be clear, the process imports the data, then you run a query and 
it completes in x time, you then ANALYZE the same data and it runs in y 
time. Is that correct?




The 'fast plans' use parallel seq scans. The 'slow plans' is using index 
scans. It appears a good query plan correctly predicts it should be bulk 
processing the tables but bad ones get fooled into trashing (hard disk, 
not SSD) by mispredicting too few rows to join between the tables.


If anyone has similar experiences and is generous enough with their time 
to share possible solutions/work arounds then I'm most grateful. If my 
description is too vague to be worthwhile answering then I quite 
understand and apologize for the time wasted in reading.


Thank you.





On Fri, Jan 15, 2021 at 10:26 AM David G. Johnston 
mailto:david.g.johns...@gmail.com>> wrote:


On Fri, Jan 15, 2021 at 9:10 AM Thomas Kellerer mailto:sham...@gmx.net>> wrote:

Atul Kumar schrieb am 15.01.2021 um 16:29:
 > As per Ron, you are not supposed to ask your questions here.
 >
 > As According to him, we should keep on doing research on internet
 > rather than asking for support directly even you have done enough
 > research and until unless “Ron” won’t be satisfied you have to do
 > keep on researching.

Ron's question was perfectly valid.

Missing and wrong statistics are one reason for the planner to
choose a bad execution plan.


Yeah, at first blush I didn't think analyze really mattered (and it
mostly doesn't because while you can keep the statistics up-to-date
the multi-table nature of the problem means they are only marginally
helpful here), but that just points out the under-specified nature
of the original posting.  Taken as a simple question of "is there a
way to work around the lack of multi-table statistics" the analyze,
and even the specific queries, don't matter all that much.  But it
also would be much more useful if the OP would choose a single
problematic query and show the schema, query, and explain results,
hopefully both good and bad, and comment on how analyze seems to
affect the plan choice.  But for the general question about
overcoming our statistics limitations the analyze point is not relevant.

David J.




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

Re: Best tools to monitor and fine tune postgres

[Adrian Klaver]

On 1/15/21 8:44 AM, Atul Kumar wrote:

Yes you are right. And it seems for those that ignore queries to answer.


I am not following what you are trying to say above?



Your mates have already said that they ignore answering repeated questions.





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

Re: Accounting for between table correlation

[Michael Lewis]

On Fri, Jan 15, 2021 at 10:22 AM Alexander Stoddard <
alexander.stodd...@gmail.com> wrote:

> The 'fast plans' use parallel seq scans. The 'slow plans' is using index
> scans. It appears a good query plan correctly predicts it should be bulk
> processing the tables but bad ones get fooled into trashing (hard disk, not
> SSD) by mispredicting too few rows to join between the tables.
>

How many tables are involved? Are you sure it is stats getting updated
causing the change in behavior? Are you hitting the genetic optimizer?

Re: Accounting for between table correlation

[Michael Lewis]

On Fri, Jan 15, 2021 at 9:10 AM Thomas Kellerer  wrote:

> Maybe there are many "idle in transaction" sessions that prevent
> autovacuum from properly
> analyzing those tables. Or maybe for some unknown reason autovacuum was
> turned off.
> Or maybe they receive a lot of bulk loads which would require a manual
> analyze.
>

"hundreds of millions of rows"

Also of note that the default autovacuum settings for scale factor of
10-20% for vacuum & analyze behavior may need to be lowered for such a
table. OP has chimed in that they believe it is being vacuumed/analyzed
often enough, but if data is primarily or onnly maintained by bulk load, it
would seem like autovacuum/analyze wouldn't be a factor anyway.

Re: time-based range partitioning and truncate/delete different timezones

[Michael Lewis]

What version are you using? How long are you keeping data for? It is
possible to partition by hour or would that exceed the number of
recommended partitions too quickly for your retention time period? Else, I
would partition on date according to the timezone of your data. Selecting
from multiple partitions for the aggregate should be performant enough in
most cases. The truncate / detach type commands may need to be the priority.

Re: migration from postgres to enterprosedb

[Michael Lewis]

You'd probably get more response if you gave more info on your situation
and what you have already come up with as a tentative approach- how much
data in your 9.5 instance, what OS, what is acceptable downtime, etc.

Regardless, do you mean EDB Postgres Advanced Server or just one of the
installers that EBD provides for community Postgres?

If the paid software, then contacting EBD support would be your best
option. If using their installer for community Postgres, then I wonder why
and why not just use pg_upgrade which takes minutes.

Re: Accounting for between table correlation

[Adrian Klaver]

On 1/15/21 10:49 AM, Alexander Stoddard wrote:

Please reply to list also.
Ccing list.



So to be clear, the process imports the data, then you run a query and
it completes in x time, you then ANALYZE the same data and it runs in y
time. Is that correct?

The process imports data, ANALYZE is run and then queries run in x time.
A subsequent ANALYZE, may or may not, change the time to y.
x may be greater or less than y for any given pair of runs, and the 
difference is vast. Two very different performance domains, due to the 
plan, I believe. If I am correctly reading the EXPLAIN plans the row 
estimates are always way off (and low), regardless of if a high or low 
performing plan is actually chosen.


Well I'm going to say this is not going to get a useful answer without 
some concrete numbers. Too many variables involved to just start 
guessing at solutions.




Thank you,
Alex



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

Re: Best tools to monitor and fine tune postgres

[Adrian Klaver]

On 1/15/21 8:28 AM, Atul Kumar wrote:
And basically you are improving your knowledge as you are expecting 
those questions the answers of which you dont know about.


Thats why you seem to not to be helpful and somehow stopping others to 
not to be helpful.


I don’t find find it wrong if any new guy asking the question that has 
been repeated 1000 times earlier, if I know the answer I WILL RESPOND 
1000 TIMES, “unlike you”.




It was for this reason that FAQ's(https://wiki.postgresql.org/wiki/FAQ) 
where created. Also why the mailing list archives are searchable:


https://www.postgresql.org/search/?m=1&ln=pgsql-general&q=monitor+tool

Which leads to:

https://www.postgresql.org/message-id/caodziv7le+oawptya8g6vbd0ggmrwfrp1x0jxs5wvauoren...@mail.gmail.com

https://www.postgresql.org/message-id/2020052809.mog2nxiyuan7xjjy%40office.hexack.fr

The frustration is that with literally a world of information at hand 
and searchable, folks often do not do the basic homework that would 
answer their question in less time then waiting for a response from a 
list. You see it enough times and even the calmest person gets 
irritated. In a perfect world that would not happen, we don't live in a 
perfect world.



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

Re: Best tools to monitor and fine tune postgres

[Atul Kumar]

You keep on giving excuses (imperfect world and blah blah) and I will keep
on raising questions on them.

The link you shared could have been shared earlier as well.


But instead of that as usual you and your mates stretched the conversation
without going in any direction so basically you wasted author’s time.


And I have no doubt that you will waste more time of yours as well as of us
by giving some kore excuses.





On Saturday, January 16, 2021, Adrian Klaver 
wrote:

> On 1/15/21 8:28 AM, Atul Kumar wrote:
>
>> And basically you are improving your knowledge as you are expecting those
>> questions the answers of which you dont know about.
>>
>> Thats why you seem to not to be helpful and somehow stopping others to
>> not to be helpful.
>>
>> I don’t find find it wrong if any new guy asking the question that has
>> been repeated 1000 times earlier, if I know the answer I WILL RESPOND 1000
>> TIMES, “unlike you”.
>>
>>
> It was for this reason that FAQ's(https://wiki.postgresql.org/wiki/FAQ)
> where created. Also why the mailing list archives are searchable:
>
> https://www.postgresql.org/search/?m=1&ln=pgsql-general&q=monitor+tool
>
> Which leads to:
>
> https://www.postgresql.org/message-id/CAODZiv7LE+OAwpTyA8G6v
> bd0ggmrwfrp1x0jxs5wvauoren...@mail.gmail.com
>
> https://www.postgresql.org/message-id/2020052809.mog2nxi
> yuan7xjjy%40office.hexack.fr
>
> The frustration is that with literally a world of information at hand and
> searchable, folks often do not do the basic homework that would answer
> their question in less time then waiting for a response from a list. You
> see it enough times and even the calmest person gets irritated. In a
> perfect world that would not happen, we don't live in a perfect world.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Best tools to monitor and fine tune postgres

[Christophe Pettus]

This conversation doesn't really have anything to do with monitoring and 
fine-tuning PostgreSQL, at this point, does it?  It might be appropriate to let 
go the meta-discussion when each individual person thinks it is appropriate to 
answer a question.

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

Re: Best tools to monitor and fine tune postgres

[David G. Johnston]

On Fri, Jan 15, 2021 at 1:53 PM Christophe Pettus  wrote:

> This conversation doesn't really have anything to do with monitoring and
> fine-tuning PostgreSQL, at this point, does it?  It might be appropriate to
> let go the meta-discussion when each individual person thinks it is
> appropriate to answer a question.
>
>
Actually, on the whole it is admirably on-topic as to how what seems like
such a simple question from a newcomer's perspective is perceived by those
on the list to whom that question is being posed.  With a number of the
responses giving indirect answers to the question since it, as has been
established, is a common question with answers available from sources other
than volunteers writing new emails or searching themselves for old replies.

On a thread of interest to newcomers it seems like a reasonably productive
spot to describe the reality, which is what is being done here - not
providing excuses.  We aren't apologetic for these realities, while
understanding that improvement is possible (if hard to achieve - technical
and personal).

David J.

Re: time-based range partitioning and truncate/delete different timezones

[Niels Jespersen]

Fra: Michael Lewis 
Dato: 15. januar 2021 kl. 19.49.32 CET
Til: Niels Jespersen 
Cc: pgsql-gene...@postgresql.org 
Emne: Re: time-based range partitioning and truncate/delete different timezones



What version are you using? How long are you keeping data for? It is possible to partition by hour or would that exceed the number of recommended partitions too quickly for your retention time period? Else, I would partition on date according
 to the timezone of your data. Selecting from multiple partitions for the aggregate should be performant enough in most cases. The truncate / detach type commands may need to be the priority.


Version 12.1. 


6. months retention, which would give about 4.400 partitions using hourly partitioning. Maybe partitioning on the same timezone as input data and in something like 6 hour intervals is the way to go. I will think about it, based on your input. 


Thank you for your ideas


Regards Niels

Re: Accounting for between table correlation

[Adrian Klaver]

On 1/15/21 11:54 AM, Adrian Klaver wrote:

On 1/15/21 10:49 AM, Alexander Stoddard wrote:

Please reply to list also.
Ccing list.



    So to be clear, the process imports the data, then you run a query 
and
    it completes in x time, you then ANALYZE the same data and it runs 
in y

    time. Is that correct?

The process imports data, ANALYZE is run and then queries run in x time.
A subsequent ANALYZE, may or may not, change the time to y.
x may be greater or less than y for any given pair of runs, and the 
difference is vast. Two very different performance domains, due to the 
plan, I believe. If I am correctly reading the EXPLAIN plans the row 
estimates are always way off (and low), regardless of if a high or low 
performing plan is actually chosen.


Well I'm going to say this is not going to get a useful answer without 
some concrete numbers. Too many variables involved to just start 
guessing at solutions.


Not sure if it would work for the vendor or not but:

https://explain.depesz.com/

offers an option to obfuscate EXPLAIN/ANALYZE output.





Thank you,
Alex






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

Re: Best tools to monitor and fine tune postgres

[Adrian Klaver]

On 1/15/21 12:50 PM, Atul Kumar wrote:
You keep on giving excuses (imperfect world and blah blah) and I will 
keep on raising questions on them.


They are not excuses they are explaining the reality of the situation. 
This is a list of people taking time out of their day to answer 
questions. They receive no direct payment(though many are probably doing 
it on the clock of their employer) for this, so the motivation to answer 
is personal. That motivation may not cover questions they have answered 
many times and/or questions that are content free. At that point the 
questioner has to either wait for someone with the motivation or do some 
leg work to make the question more appealing. That is just how it is.
The other option is to provide monetary motivation and buy responses 
from one of the many companies that offer that service:


https://www.postgresql.org/support/professional_support/



And I have no doubt that you will waste more time of yours as well as of 
us by giving some kore excuses.




This post marks the end of my wasting your time.




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

Re: Accounting for between table correlation

[Alexander Stoddard]

On Fri, Jan 15, 2021 at 12:27 PM Michael Lewis  wrote:

> On Fri, Jan 15, 2021 at 10:22 AM Alexander Stoddard <
> alexander.stodd...@gmail.com> wrote:
>
>> The 'fast plans' use parallel seq scans. The 'slow plans' is using index
>> scans. It appears a good query plan correctly predicts it should be bulk
>> processing the tables but bad ones get fooled into trashing (hard disk, not
>> SSD) by mispredicting too few rows to join between the tables.
>>
>
> How many tables are involved?
>

The queries are complex, multiple joins to 10 plus tables, although most
are to tiny enum type lookup tables. I believe it is the join between the
two large tables that I have described that causes the issue, and that
seems to be reflected in the different strategies in the plans. For my own
learning and to clarify the problem I probably will have to try and
reproduce the behavior in a test case.


> Are you sure it is stats getting updated causing the change in behavior?
>
No I'm not sure, could something else flip a plan after an ANALYZE?
Differing performance of multiple runs of the same query could be due
caching etc. but that would be a timing difference without a change in
query plan. The output plans I see are radically different and correlate
with large magnitude performance changes.


> Are you hitting the genetic optimizer?
>

I am doing nothing to specify the optimizer. Do I have configurable options
in that regard? I was unaware of them.

Thank you,
Alex

On Fri, Jan 15, 2021 at 12:27 PM Michael Lewis  wrote:

> On Fri, Jan 15, 2021 at 10:22 AM Alexander Stoddard <
> alexander.stodd...@gmail.com> wrote:
>
>> The 'fast plans' use parallel seq scans. The 'slow plans' is using index
>> scans. It appears a good query plan correctly predicts it should be bulk
>> processing the tables but bad ones get fooled into trashing (hard disk, not
>> SSD) by mispredicting too few rows to join between the tables.
>>
>
> How many tables are involved? Are you sure it is stats getting updated
> causing the change in behavior? Are you hitting the genetic optimizer?
>

PostgreSQL License Question

[Kristi Kangas]

Hello,
 I see on your website that PostgreSQL is released under an Open Source license.
 Do you consider addendums proposed by your users, modifying the posted license 
terms?
 Thank you.
 From your website:
PostgreSQL is released under the PostgreSQL 
License,
 a liberal Open Source license, similar to the BSD or MIT licenses.

Kristi Kangas
Procurement and Support Services
312-653-5503

__
Disclaimer

The information contained in this communication is confidential, private, 
proprietary, or otherwise privileged and is intended only for the use of the 
addressee.  Unauthorized use, disclosure, distribution or copying is strictly 
prohibited and may be unlawful.  If you have received this communication in 
error, please notify the sender immediately.

Re: PostgreSQL License Question

[Adrian Klaver]

On 1/15/21 6:35 AM, Kristi Kangas wrote:

Hello,

  I see on your website that PostgreSQL is released under an Open Source 
license.


  Do you consider addendums proposed by your users, modifying the posted 
license terms?


I believe that is answered further down on the page:

"Will PostgreSQL ever be released under a different license?

The PostgreSQL Global Development Group remains committed to making 
PostgreSQL available as free and open source software in perpetuity. 
There are no plans to change the PostgreSQL License or release 
PostgreSQL under a different license.


"



  Thank you.

_From your website:_

PostgreSQL is released under the *PostgreSQL License* 
, 
a liberal Open Source license, similar to the BSD or MIT licenses.


Kristi Kangas

Procurement and Support Services

312-653-5503


Disclaimer

The information contained in this communication is confidential, 
private, proprietary, or otherwise privileged and is intended only for 
the use of the addressee. Unauthorized use, disclosure, distribution or 
copying is strictly prohibited and may be unlawful. If you have received 
this communication in error, please notify the sender immediately.



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

couldn't drop subscription after upgrading via logical replication.

[James(王旭)]

Hello!


I upgraded my postgres from 11 to 13 using logical replication(publication in 
PG-11 and subscription in PG-13 of course).


Now I encountered this problem, I cannot drop the subscription in PG-13:
drop subscription sub_upgrade_to_13;
ERROR:  could not drop the replication slot "sub_upgrade_to_13" on 
publisher
DETAIL:  The error was: ERROR:  replication slot "sub_upgrade_to_13" 
does not exist
The thing is that I already stoped my PG-11, there's no way I drop the 
replication slot "sub_upgrade_to_13" on publisher,
is there anything I can do to solve it?


Many thanks!

Re: Best tools to monitor and fine tune postgres

[Hemil Ruparel]

If you want answers everytime no matter how stupid they are, my price is
$40/hour. We can negotiate how much of my time you are allowed to waste.
This is my personal time I am spending on replying. Plus, you are probably
working for a company which uses postgres but doesn't even care to provide
proper training to its employees let alone fund the development of
postgres. We are under no obligation to reply to you. Neither do you
respect our time and effort.

On Sat, Jan 16, 2021 at 3:27 AM Adrian Klaver 
wrote:

> On 1/15/21 12:50 PM, Atul Kumar wrote:
> > You keep on giving excuses (imperfect world and blah blah) and I will
> > keep on raising questions on them.
>
> They are not excuses they are explaining the reality of the situation.
> This is a list of people taking time out of their day to answer
> questions. They receive no direct payment(though many are probably doing
> it on the clock of their employer) for this, so the motivation to answer
> is personal. That motivation may not cover questions they have answered
> many times and/or questions that are content free. At that point the
> questioner has to either wait for someone with the motivation or do some
> leg work to make the question more appealing. That is just how it is.
> The other option is to provide monetary motivation and buy responses
> from one of the many companies that offer that service:
>
> https://www.postgresql.org/support/professional_support/
>
> >
> > And I have no doubt that you will waste more time of yours as well as of
> > us by giving some kore excuses.
> >
>
> This post marks the end of my wasting your time.
>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>

Do we need a way to moderate mailing lists?

[Hemil Ruparel]

This is a meta discussion. I couldn't find a meta mailing list
 so I am posting it here. This discussion
sparked from this message

.

We should clearly mention on the postgres mailing list page
 that people are encouraged to do their
own research and that we are a community of people who help for free. And
so we expect OP to do basic initial research. And the OP is not entitled to
receive an answer as we are not bound by any contract. We spend our
personal time away from our busy schedule to help others and we have the
right to choose who we give our time to.

Another important point is we need a mechanism to prevent polluting our
community. We need to raise the bar a little. Or we become like quora.
Where the same question is posted thousands of times with the exact same
wording. Stack Exchange has taken it to the extreme to the point that it's
become toxic for newbies. We need to be somewhere in between. I propose
when the first time someone posts such a question, we give them the benefit
of doubt and point them to resources which they should consider before
posting. Second time, we warn them not to do this again and use the
resources they have at their disposal. The third time we should temporarily
ban them (say for a week).

It's my personal opinion. But i personally do not want to deal with
entitled people who cannot do basic google searches.

Re: Do we need a way to moderate mailing lists?

[Christophe Pettus]

> On Jan 15, 2021, at 21:51, Hemil Ruparel  wrote:
> 
> This is a meta discussion. I couldn't find a meta mailing list so I am 
> posting it here. This discussion sparked from this message. 

If you feel someone is being disruptive, or abusive, there is a Code of Conduct 
process:

https://www.postgresql.org/about/policies/coc/
--
-- Christophe Pettus
   x...@thebuild.com

Re: Do we need a way to moderate mailing lists?

[Hemil Ruparel]

I don't know on what basis should the complaint be made other than the
peron's entitlement

On Sat, Jan 16, 2021 at 11:24 AM Christophe Pettus  wrote:

>
>
> > On Jan 15, 2021, at 21:51, Hemil Ruparel 
> wrote:
> >
> > This is a meta discussion. I couldn't find a meta mailing list so I am
> posting it here. This discussion sparked from this message.
>
> If you feel someone is being disruptive, or abusive, there is a Code of
> Conduct process:
>
> https://www.postgresql.org/about/policies/coc/
> --
> -- Christophe Pettus
>x...@thebuild.com
>
>

Re: Do we need a way to moderate mailing lists?

[Hemil Ruparel]

I will send them a mail anyway. Lets see their response

On Sat, Jan 16, 2021 at 11:30 AM Hemil Ruparel 
wrote:

> I don't know on what basis should the complaint be made other than the
> peron's entitlement
>
> On Sat, Jan 16, 2021 at 11:24 AM Christophe Pettus 
> wrote:
>
>>
>>
>> > On Jan 15, 2021, at 21:51, Hemil Ruparel 
>> wrote:
>> >
>> > This is a meta discussion. I couldn't find a meta mailing list so I am
>> posting it here. This discussion sparked from this message.
>>
>> If you feel someone is being disruptive, or abusive, there is a Code of
>> Conduct process:
>>
>> https://www.postgresql.org/about/policies/coc/
>> --
>> -- Christophe Pettus
>>x...@thebuild.com
>>
>>

Re: Do we need a way to moderate mailing lists?

[Christophe Pettus]

> On Jan 15, 2021, at 21:51, Hemil Ruparel  wrote:
> It's my personal opinion. But i personally do not want to deal with entitled 
> people who cannot do basic google searches.

Well, I have to mention that you don't have to deal with them.  It might be 
slightly irritating to read questions like that, but they really aren't choking 
the list, and you can just move on to a different question that you feel like 
answering.

There is also a virtue in people asking very basic questions on the list, even 
one that could be revealed by a search, because it gives people who are not 
deep experts a chance to answer the question.
--
-- Christophe Pettus
   x...@thebuild.com

Re: Do we need a way to moderate mailing lists?

[Hemil Ruparel]

I fear that mailing lists become like quora. Eternal September
 was probably the first
time this happened. History repeats itself. I don't want the community to
lose its value

On Sat, Jan 16, 2021 at 11:37 AM Christophe Pettus  wrote:

>
>
> > On Jan 15, 2021, at 21:51, Hemil Ruparel 
> wrote:
> > It's my personal opinion. But i personally do not want to deal with
> entitled people who cannot do basic google searches.
>
> Well, I have to mention that you don't have to deal with them.  It might
> be slightly irritating to read questions like that, but they really aren't
> choking the list, and you can just move on to a different question that you
> feel like answering.
>
> There is also a virtue in people asking very basic questions on the list,
> even one that could be revealed by a search, because it gives people who
> are not deep experts a chance to answer the question.
> --
> -- Christophe Pettus
>x...@thebuild.com
>
>

Re: Do we need a way to moderate mailing lists?

[Christophe Pettus]

> On Jan 15, 2021, at 22:10, Hemil Ruparel  wrote:
> 
> I fear that mailing lists become like quora.

pgsql-general is almost old enough to drink. :)  The very first message I could 
find with a reliable date is from 31 May 2000.  I think after nearly 21 years, 
we don't have much to worry about in that regard.

That message also includes the statements:

> What you are asking for is replication, which is not easy to implement, and 
> almost damn impossible to get it RIGHT.

and

> Now, what is WAL? When is it scheduled for implementation?

... which is pretty wild all by itself.
--
-- Christophe Pettus
   x...@thebuild.com

Re: Do we need a way to moderate mailing lists?

[Hemil Ruparel]

I have no problems if there are one or two questions which are exactly the
same. I give them the benefit of doubt. What I won't tolerate are entitled
people who think we work for them for free and that they are entitled to
receive and answer.

On Sat, Jan 16, 2021 at 11:45 AM Christophe Pettus  wrote:

>
>
> > On Jan 15, 2021, at 22:10, Hemil Ruparel 
> wrote:
> >
> > I fear that mailing lists become like quora.
>
> pgsql-general is almost old enough to drink. :)  The very first message I
> could find with a reliable date is from 31 May 2000.  I think after nearly
> 21 years, we don't have much to worry about in that regard.
>
> That message also includes the statements:
>
> > What you are asking for is replication, which is not easy to implement,
> and almost damn impossible to get it RIGHT.
>
> and
>
> > Now, what is WAL? When is it scheduled for implementation?
>
> ... which is pretty wild all by itself.
> --
> -- Christophe Pettus
>x...@thebuild.com
>
>

Re: Do we need a way to moderate mailing lists?

[Christophe Pettus]

> On Jan 15, 2021, at 22:19, Hemil Ruparel  wrote:
> 
> I have no problems if there are one or two questions which are exactly the 
> same. I give them the benefit of doubt. What I won't tolerate are entitled 
> people who think we work for them for free and that they are entitled to 
> receive and answer.

I suppose it would be rude to point out that PostgreSQL list style is to not 
top-post?  I have to say, if you are going to be firm with people about 
etiquette...

If someone gets abusive about not receiving help (and it does happen, sadly), 
that's exactly the kind of thing the Code of Conduct was designed for.  If they 
are seriously spamming the list, likewise.

For a lot of people, though, they just aren't familiar with list etiquette, do 
not have English as their first language and are not clear what is being asked 
of them, or just don't know the resources out there.

I would assume they are acting in good faith.  If you politely point out 
resources to them and they get snappish, then it can become a CoC issue.  
Otherwise, I think that being generous in what we receive and accurate in what 
we reply, as with any protocol, is the right answer.
--
-- Christophe Pettus
   x...@thebuild.com

Re: Do we need a way to moderate mailing lists?

[Hemil Ruparel]

I agree. That's why I proposed to guide such people as the first attempt
giving them the benefit of the doubt.

On Sat, Jan 16, 2021 at 11:52 AM Christophe Pettus  wrote:

>
>
> > On Jan 15, 2021, at 22:19, Hemil Ruparel 
> wrote:
> >
> > I have no problems if there are one or two questions which are exactly
> the same. I give them the benefit of doubt. What I won't tolerate are
> entitled people who think we work for them for free and that they are
> entitled to receive and answer.
>
> I suppose it would be rude to point out that PostgreSQL list style is to
> not top-post?  I have to say, if you are going to be firm with people about
> etiquette...
>
> If someone gets abusive about not receiving help (and it does happen,
> sadly), that's exactly the kind of thing the Code of Conduct was designed
> for.  If they are seriously spamming the list, likewise.
>
> For a lot of people, though, they just aren't familiar with list
> etiquette, do not have English as their first language and are not clear
> what is being asked of them, or just don't know the resources out there.
>
> I would assume they are acting in good faith.  If you politely point out
> resources to them and they get snappish, then it can become a CoC issue.
> Otherwise, I think that being generous in what we receive and accurate in
> what we reply, as with any protocol, is the right answer.
> --
> -- Christophe Pettus
>x...@thebuild.com
>
>

Re: Do we need a way to moderate mailing lists?

[Rob Sargent]

I watched this sadness play out.  It had nothing to do with the OP's 
original question. Rather someone with feelings hurt from a separate 
thread (table correlation) felt the need drag that squabble over to the 
focal issue here (tools).  I was tempted bring up the COC but I'm not 
really a believer.


We see these flare ups almost as frequently as the repetition of the 
"tune pg tool" request.  They die out pretty quickly especially when an 
obvious expert declines to waste any more of the offender's (offended's) 
time.



On 1/15/21 10:51 PM, Hemil Ruparel wrote:
This is a meta discussion. I couldn't find a meta mailing list 
so I am posting it here. This 
discussion sparked from this message 
. 



We should clearly mention on the postgres mailing list page 
 that people are encouraged to do 
their own research and that we are a community of people who help for 
free. And so we expect OP to do basic initial research. And the OP is 
not entitled to receive an answer as we are not bound by any contract. 
We spend our personal time away from our busy schedule to help others 
and we have the right to choose who we give our time to.


Another important point is we need a mechanism to prevent polluting 
our community. We need to raise the bar a little. Or we become like 
quora. Where the same question is posted thousands of times with the 
exact same wording. Stack Exchange has taken it to the extreme to the 
point that it's become toxic for newbies. We need to be somewhere in 
between. I propose when the first time someone posts such a question, 
we give them the benefit of doubt and point them to resources which 
they should consider before posting. Second time, we warn them not to 
do this again and use the resources they have at their disposal. The 
third time we should temporarily ban them (say for a week).


It's my personal opinion. But i personally do not want to deal with 
entitled people who cannot do basic google searches.

Re: Do we need a way to moderate mailing lists?

[Hemil Ruparel]

Exactly my point. We need to raise the bar of the behavior we tolerate.
This should not be tolerated. We need to set an example. The person in
question clearly understood english and I have never seen a person who
could use mailing lists but not google. So that's out of the question.

We are not free consultants. And you are not entitled to shit. You are
probably being paid to work on that project. We are not. Your problem. Fix
it yourself. Or at least have to courtesy to google it.

On Sat, Jan 16, 2021 at 12:06 PM Rob Sargent  wrote:

> I watched this sadness play out.  It had nothing to do with the OP's
> original question.  Rather someone with feelings hurt from a separate
> thread (table correlation) felt the need drag that squabble over to the
> focal issue here (tools).  I was tempted bring up the COC but I'm not
> really a believer.
>
> We see these flare ups almost as frequently as the repetition of the "tune
> pg tool" request.  They die out pretty quickly especially when an obvious
> expert declines to waste any more of the offender's (offended's) time.
>
>
> On 1/15/21 10:51 PM, Hemil Ruparel wrote:
>
> This is a meta discussion. I couldn't find a meta mailing list
>  so I am posting it here. This
> discussion sparked from this message
> 
> .
>
> We should clearly mention on the postgres mailing list page
>  that people are encouraged to do their
> own research and that we are a community of people who help for free. And
> so we expect OP to do basic initial research. And the OP is not entitled to
> receive an answer as we are not bound by any contract. We spend our
> personal time away from our busy schedule to help others and we have the
> right to choose who we give our time to.
>
> Another important point is we need a mechanism to prevent polluting our
> community. We need to raise the bar a little. Or we become like quora.
> Where the same question is posted thousands of times with the exact same
> wording. Stack Exchange has taken it to the extreme to the point that it's
> become toxic for newbies. We need to be somewhere in between. I propose
> when the first time someone posts such a question, we give them the benefit
> of doubt and point them to resources which they should consider before
> posting. Second time, we warn them not to do this again and use the
> resources they have at their disposal. The third time we should temporarily
> ban them (say for a week).
>
> It's my personal opinion. But i personally do not want to deal with
> entitled people who cannot do basic google searches.
>
>
>
>

Re: Do we need a way to moderate mailing lists?

[Rob Sargent]

To pick up Pettus's point: filtering out precisely and only the noise is 
hard.  Maybe in a couple more decades we'll be there.  (Sorry I won't be 
around for it.) For now ignoring it might be the best option.


On 1/15/21 11:43 PM, Hemil Ruparel wrote:
Exactly my point. We need to raise the bar of the behavior we 
tolerate. This should not be tolerated. We need to set an example. The 
person in question clearly understood english and I have never seen a 
person who could use mailing lists but not google. So that's out of 
the question.


We are not free consultants. And you are not entitled to shit. You are 
probably being paid to work on that project. We are not. Your problem. 
Fix it yourself. Or at least have to courtesy to google it.


On Sat, Jan 16, 2021 at 12:06 PM Rob Sargent > wrote:


I watched this sadness play out.  It had nothing to do with the
OP's original question.  Rather someone with feelings hurt from a
separate thread (table correlation) felt the need drag that
squabble over to the focal issue here (tools).  I was tempted
bring up the COC but I'm not really a believer.

We see these flare ups almost as frequently as the repetition of
the "tune pg tool" request.  They die out pretty quickly
especially when an obvious expert declines to waste any more of
the offender's (offended's) time.


On 1/15/21 10:51 PM, Hemil Ruparel wrote:

This is a meta discussion. I couldn't find a meta mailing list
so I am posting it here. This
discussion sparked from this message

.


We should clearly mention on the postgres mailing list page
 that people are encouraged to
do their own research and that we are a community of people who
help for free. And so we expect OP to do basic initial research.
And the OP is not entitled to receive an answer as we are not
bound by any contract. We spend our personal time away from our
busy schedule to help others and we have the right to choose who
we give our time to.

Another important point is we need a mechanism to prevent
polluting our community. We need to raise the bar a little. Or we
become like quora. Where the same question is posted thousands of
times with the exact same wording. Stack Exchange has taken it to
the extreme to the point that it's become toxic for newbies. We
need to be somewhere in between. I propose when the first time
someone posts such a question, we give them the benefit of doubt
and point them to resources which they should consider before
posting. Second time, we warn them not to do this again and use
the resources they have at their disposal. The third time we
should temporarily ban them (say for a week).

It's my personal opinion. But i personally do not want to deal
with entitled people who cannot do basic google searches.

Re: Do we need a way to moderate mailing lists?

[David G. Johnston]

On Friday, January 15, 2021, Christophe Pettus  wrote:

>
>
> > On Jan 15, 2021, at 22:19, Hemil Ruparel 
> wrote:
> >
> > I have no problems if there are one or two questions which are exactly
> the same. I give them the benefit of doubt. What I won't tolerate are
> entitled people who think we work for them for free and that they are
> entitled to receive and answer.
>
> I suppose it would be rude to point out that PostgreSQL list style is to
> not top-post?  I have to say, if you are going to be firm with people about
> etiquette...
>
> If someone gets abusive about not receiving help (and it does happen,
> sadly), that's exactly the kind of thing the Code of Conduct was designed
> for.  If they are seriously spamming the list, likewise.
>
> For a lot of people, though, they just aren't familiar with list
> etiquette, do not have English as their first language and are not clear
> what is being asked of them, or just don't know the resources out there.
>
> I would assume they are acting in good faith.  If you politely point out
> resources to them and they get snappish, then it can become a CoC issue.
> Otherwise, I think that being generous in what we receive and accurate in
> what we reply, as with any protocol, is the right answer.
>

Agreed on all counts.  It should take more than innocent subjective
inconsideration to get a ban or even a formal warning.  With respect to
this flare up while heated and probably not flattering it has value and not
worthy of censure.  For me the status quo fits into “good enough to keep my
attention elsewhere”.  The areas I’d focus on, though, is site layout and
content, but in a positive manner, not rules listings and policies; and
also guidance and resources for “community defenders” on how constructively
engage when this kind of “meta” discussion arises.  As I’m not volunteering
for the work this is just idle complaining and ideation on my part.

Oh, and an auto-responder instead of an FAQ...

David J.

Re: Do we need a way to moderate mailing lists?

[Hemil Ruparel]

I am not asking people to get banned the moment they ask easy questions. I
am 18. Believe me I have done that too (way more than i would like to admit
;P). I am just saying we should guide them. But there are some people who
do not wish to be guided. They just want spoonfed answers which is
untolerable in my opinion. You need to be ready to learn on your own. And
attempt to struggle with your problem for at least a few hours before
asking for help

On Sat, Jan 16, 2021 at 12:24 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Friday, January 15, 2021, Christophe Pettus  wrote:
>
>>
>>
>> > On Jan 15, 2021, at 22:19, Hemil Ruparel 
>> wrote:
>> >
>> > I have no problems if there are one or two questions which are exactly
>> the same. I give them the benefit of doubt. What I won't tolerate are
>> entitled people who think we work for them for free and that they are
>> entitled to receive and answer.
>>
>> I suppose it would be rude to point out that PostgreSQL list style is to
>> not top-post?  I have to say, if you are going to be firm with people about
>> etiquette...
>>
>> If someone gets abusive about not receiving help (and it does happen,
>> sadly), that's exactly the kind of thing the Code of Conduct was designed
>> for.  If they are seriously spamming the list, likewise.
>>
>> For a lot of people, though, they just aren't familiar with list
>> etiquette, do not have English as their first language and are not clear
>> what is being asked of them, or just don't know the resources out there.
>>
>> I would assume they are acting in good faith.  If you politely point out
>> resources to them and they get snappish, then it can become a CoC issue.
>> Otherwise, I think that being generous in what we receive and accurate in
>> what we reply, as with any protocol, is the right answer.
>>
>
> Agreed on all counts.  It should take more than innocent subjective
> inconsideration to get a ban or even a formal warning.  With respect to
> this flare up while heated and probably not flattering it has value and not
> worthy of censure.  For me the status quo fits into “good enough to keep my
> attention elsewhere”.  The areas I’d focus on, though, is site layout and
> content, but in a positive manner, not rules listings and policies; and
> also guidance and resources for “community defenders” on how constructively
> engage when this kind of “meta” discussion arises.  As I’m not volunteering
> for the work this is just idle complaining and ideation on my part.
>
> Oh, and an auto-responder instead of an FAQ...
>
> David J.
>
>

Re: Do we need a way to moderate mailing lists?

[David G. Johnston]

On Friday, January 15, 2021, Hemil Ruparel 
wrote:

> I am not asking people to get banned the moment they ask easy questions. I
> am 18. Believe me I have done that too (way more than i would like to admit
> ;P). I am just saying we should guide them. But there are some people who
> do not wish to be guided. They just want spoonfed answers which is
> untolerable in my opinion. You need to be ready to learn on your own. And
> attempt to struggle with your problem for at least a few hours before
> asking for help
>

Please consider taking the not top-posting request to heart.

This is part of what I was referring to - while you feel strongly about
this position the moderators are not going to ban people for this behavior,
nor should they.  The community can impose a punishment by simply not
responding, or can avoid conflict by giving answers, while some cases
result in attempts to educate (which I do on occasion...and which frankly,
and correctly, puts me out there for needing rebuke/support as a community
representative moreso than the questioner).

There is a lot of grey here and untolerable doesn’t fit well and you will
need to make a personal decision if you find yourself keeping score and
simply reading that kind of email causes discomfort.  On that point, I will
say that my innate emotional response is quite similar and that I get
concerned that I act in counter-productive and unhealthy ways at times.
But its not the responsibility of the project to prevent exposing me to
situations where my highly judgmental nature is riled, and it should tell
me if I fail to control my response to that emotion.  At the same time,
acknowledging that people such as myself do generally positively represent
the community it is does behoove them to consider providing support/tools
that can mitigate this human aspect of the request/reply dynamic.

David J.

Re: Do we need a way to moderate mailing lists?

[Tim Cross]

Hemil Ruparel  writes:

> Exactly my point. We need to raise the bar of the behavior we tolerate.
> This should not be tolerated. We need to set an example. The person in
> question clearly understood english and I have never seen a person who
> could use mailing lists but not google. So that's out of the question.
>
> We are not free consultants. And you are not entitled to shit. You are
> probably being paid to work on that project. We are not. Your problem. Fix
> it yourself. Or at least have to courtesy to google it.
>

While I can understand your frustration, I disagree with your position.

It is too subjective and difficult to apply/adopt such a strong position
and could too easily backfire, resulting in a perception of an elitist,
unwelcoming and unfriendly community.

Banning should be reserved for the most serious and abusive cases.
Banning because someone appears to be acting entitled or lazy is hard to
assess in a non-bias manner and likely has too much cultural variation
to applied consistently. Someone you feel who is being entitled or lazy
might be someone I feel is frustrated, may lack good communication
and/or social skills or might simply be immature and in need of some
guidance and education. My response may also differ depending on my own
state of mind and mood at the time when I read the message.

I've been on the postgres lists for some years now and to be honest,
have not noticed this type of issue very often. There are occasionally
rude and lazy individuals who may appear to be acting entitled, but they
soon go away. In some respects, the list is self-moderating because
people who do act poorly soon get ignored and their messages die out
with no responses.

The great benefit of lists like these is that you can just ignore anyone
you think are rude, entitled or fail to put in the effort you believe is
warranted before their question/issue needs attention. Many mail clients
will even allow you to 'block' specific senders. I have done this once
with someone from a different list. I don't know if they are still
behaving badly as now I never see their messages.

My advice would be to just delete and move on, a luxury you don't have
when you are employed and paid to deal with such messages, which is one
reason I don't like or have the temperament to fulfil the difficult
service/support desk roles which too often maligned and fail to get the
recognition they deserve.

Tim

Re: Do we need a way to moderate mailing lists?

[Hemil Ruparel]

Okay. I will not reply to them. Enough mental cycles wasted

On Sat, Jan 16, 2021 at 1:11 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Friday, January 15, 2021, Hemil Ruparel 
> wrote:
>
>> I am not asking people to get banned the moment they ask easy questions.
>> I am 18. Believe me I have done that too (way more than i would like to
>> admit ;P). I am just saying we should guide them. But there are some people
>> who do not wish to be guided. They just want spoonfed answers which is
>> untolerable in my opinion. You need to be ready to learn on your own. And
>> attempt to struggle with your problem for at least a few hours before
>> asking for help
>>
>
> Please consider taking the not top-posting request to heart.
>
> This is part of what I was referring to - while you feel strongly about
> this position the moderators are not going to ban people for this behavior,
> nor should they.  The community can impose a punishment by simply not
> responding, or can avoid conflict by giving answers, while some cases
> result in attempts to educate (which I do on occasion...and which frankly,
> and correctly, puts me out there for needing rebuke/support as a community
> representative moreso than the questioner).
>
> There is a lot of grey here and untolerable doesn’t fit well and you will
> need to make a personal decision if you find yourself keeping score and
> simply reading that kind of email causes discomfort.  On that point, I will
> say that my innate emotional response is quite similar and that I get
> concerned that I act in counter-productive and unhealthy ways at times.
> But its not the responsibility of the project to prevent exposing me to
> situations where my highly judgmental nature is riled, and it should tell
> me if I fail to control my response to that emotion.  At the same time,
> acknowledging that people such as myself do generally positively represent
> the community it is does behoove them to consider providing support/tools
> that can mitigate this human aspect of the request/reply dynamic.
>
> David J.
>
>

Re: Do we need a way to moderate mailing lists?

[Hemil Ruparel]

In my furstration, I never thought about the self moderation effect. Thanks
Tim

On Sat, Jan 16, 2021 at 1:14 PM Tim Cross  wrote:

>
> Hemil Ruparel  writes:
>
> > Exactly my point. We need to raise the bar of the behavior we tolerate.
> > This should not be tolerated. We need to set an example. The person in
> > question clearly understood english and I have never seen a person who
> > could use mailing lists but not google. So that's out of the question.
> >
> > We are not free consultants. And you are not entitled to shit. You are
> > probably being paid to work on that project. We are not. Your problem.
> Fix
> > it yourself. Or at least have to courtesy to google it.
> >
>
> While I can understand your frustration, I disagree with your position.
>
> It is too subjective and difficult to apply/adopt such a strong position
> and could too easily backfire, resulting in a perception of an elitist,
> unwelcoming and unfriendly community.
>
> Banning should be reserved for the most serious and abusive cases.
> Banning because someone appears to be acting entitled or lazy is hard to
> assess in a non-bias manner and likely has too much cultural variation
> to applied consistently. Someone you feel who is being entitled or lazy
> might be someone I feel is frustrated, may lack good communication
> and/or social skills or might simply be immature and in need of some
> guidance and education. My response may also differ depending on my own
> state of mind and mood at the time when I read the message.
>
> I've been on the postgres lists for some years now and to be honest,
> have not noticed this type of issue very often. There are occasionally
> rude and lazy individuals who may appear to be acting entitled, but they
> soon go away. In some respects, the list is self-moderating because
> people who do act poorly soon get ignored and their messages die out
> with no responses.
>
> The great benefit of lists like these is that you can just ignore anyone
> you think are rude, entitled or fail to put in the effort you believe is
> warranted before their question/issue needs attention. Many mail clients
> will even allow you to 'block' specific senders. I have done this once
> with someone from a different list. I don't know if they are still
> behaving badly as now I never see their messages.
>
> My advice would be to just delete and move on, a luxury you don't have
> when you are employed and paid to deal with such messages, which is one
> reason I don't like or have the temperament to fulfil the difficult
> service/support desk roles which too often maligned and fail to get the
> recognition they deserve.
>
> Tim
>
>
>

Re: Do we need a way to moderate mailing lists?

[Hemil Ruparel]

In my frustration*

On Sat, Jan 16, 2021 at 1:24 PM Hemil Ruparel 
wrote:

> In my furstration, I never thought about the self moderation effect.
> Thanks Tim
>
> On Sat, Jan 16, 2021 at 1:14 PM Tim Cross  wrote:
>
>>
>> Hemil Ruparel  writes:
>>
>> > Exactly my point. We need to raise the bar of the behavior we tolerate.
>> > This should not be tolerated. We need to set an example. The person in
>> > question clearly understood english and I have never seen a person who
>> > could use mailing lists but not google. So that's out of the question.
>> >
>> > We are not free consultants. And you are not entitled to shit. You are
>> > probably being paid to work on that project. We are not. Your problem.
>> Fix
>> > it yourself. Or at least have to courtesy to google it.
>> >
>>
>> While I can understand your frustration, I disagree with your position.
>>
>> It is too subjective and difficult to apply/adopt such a strong position
>> and could too easily backfire, resulting in a perception of an elitist,
>> unwelcoming and unfriendly community.
>>
>> Banning should be reserved for the most serious and abusive cases.
>> Banning because someone appears to be acting entitled or lazy is hard to
>> assess in a non-bias manner and likely has too much cultural variation
>> to applied consistently. Someone you feel who is being entitled or lazy
>> might be someone I feel is frustrated, may lack good communication
>> and/or social skills or might simply be immature and in need of some
>> guidance and education. My response may also differ depending on my own
>> state of mind and mood at the time when I read the message.
>>
>> I've been on the postgres lists for some years now and to be honest,
>> have not noticed this type of issue very often. There are occasionally
>> rude and lazy individuals who may appear to be acting entitled, but they
>> soon go away. In some respects, the list is self-moderating because
>> people who do act poorly soon get ignored and their messages die out
>> with no responses.
>>
>> The great benefit of lists like these is that you can just ignore anyone
>> you think are rude, entitled or fail to put in the effort you believe is
>> warranted before their question/issue needs attention. Many mail clients
>> will even allow you to 'block' specific senders. I have done this once
>> with someone from a different list. I don't know if they are still
>> behaving badly as now I never see their messages.
>>
>> My advice would be to just delete and move on, a luxury you don't have
>> when you are employed and paid to deal with such messages, which is one
>> reason I don't like or have the temperament to fulfil the difficult
>> service/support desk roles which too often maligned and fail to get the
>> recognition they deserve.
>>
>> Tim
>>
>>
>>