Re: Is it possible to use keywords (date units) in a function definition?

[Alistair Johnson]

>
> I think you need to be more specific as to what "this" means.
>
> Looking again after Andrian's comment are you trying to write, in the
> script file:
>
> datetime_diff('start time as string'::timestamp, 'end time as
> string'::timestamp, HOUR)
>
> and get PostgreSQL to recognize the value HOUR as a custom type
> value without single quotes surrounding it
>
> If that is the question the answer is no.  The only type literals that can
> be written without single quotes are numbers.
>
> The parsing of SQL can handle some standard mandated non-quoted constants
> but they are basically keywords, not values.
>
> David J.
>

Thanks David - that was exactly my question - and it's nice to have a clear
answer even if it wrinkles my plans a bit!

-Alistair

[HELP] query regarding replication

[Praveen Kumar K S]

Hello,

I had setup one master and hot-slave by setting WAL_LEVEL=replica

But I use a debezium plugin and it requires to connect to master with
WAL_LEVEL=logical and it is mandatory. Can I achieve master/slave streaming
replication by setting WAL_LEVEL to logical on master ? Are there any
drawbacks of it ?

-- 


*Regards,*


*K S Praveen Kumar*

Re: [HELP] query regarding replication

[Andreas Kretschmer]

Am 09.06.20 um 09:55 schrieb Praveen Kumar K S:
Can I achieve master/slave streaming replication by setting WAL_LEVEL 
to logical on master ? Are there any drawbacks of it ?


yes, no problem. the wal's would be a bit larger, that's all.


Regards, Andreas

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

Re: [HELP] query regarding replication

[Praveen Kumar K S]

Thanks. Will this approach replicate DDL changes ?

On Tue, Jun 9, 2020 at 1:36 PM Andreas Kretschmer 
wrote:

>
>
> Am 09.06.20 um 09:55 schrieb Praveen Kumar K S:
> > Can I achieve master/slave streaming replication by setting WAL_LEVEL
> > to logical on master ? Are there any drawbacks of it ?
>
> yes, no problem. the wal's would be a bit larger, that's all.
>
>
> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
>
>

-- 


*Regards,*


*K S Praveen KumarM: +91-9986855625 *

Re: [HELP] query regarding replication

[Andreas Kretschmer]

Am 09.06.20 um 10:44 schrieb Praveen Kumar K S:

Thanks. Will this approach replicate DDL changes ?


sure.


Regards, Andreas

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

Re: Something else about Redo Logs disappearing

[Stephen Frost]

Greetings,

* Adrian Klaver (adrian.kla...@aklaver.com) wrote:
> I use pg_backrest, but it does not look promising for running on BSD:
> https://fluca1978.github.io/2019/03/04/pgbackrest_FreeBSD.html

That's an unfortunately ancient post, really, considering that
pgbackrest has now been fully rewritten into C, and Luca as recently as
September 2019 was saying he has it working on FreeBSD.

If folks do run into issues with pgbackrest on FreeBSD, please let us
know.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Index no longer being used, destroying and recreating it restores use.

[Koen De Groote]

Right. In that case, the function I ended up with is this:

create or replace function still_needs_backup(bool, bool)
returns BOOLEAN as $$
BEGIN
PERFORM 1 from item where shouldbebackedup=$1 and backupperformed=$2;
  IF FOUND THEN
RETURN TRUE;
  ELSE
RETURN FALSE;
  END IF;
END;
$$
language plpgsql;


And the index for that should be this, I think: "CREATE INDEX CONCURRENTLY
index_test ON item USING btree (still_needs_backup, itemCreated, filepath)
WHERE still_needs_backup(true, false) = true;"
However postgres throws an error here, saying "ERROR:  functions in index
predicate must be marked IMMUTABLE".

I tried it also without the first argument, same error.

And I don't think I can do that, because the return is not IMMUTABLE. It is
at best STABLE, but certainly not IMMUTABLE.

So yeah, I'm probably not understanding the suggestion properly? Either
way, I still have questions about the earlier function I created, namely
how reliable that performance is. If not the same thing will happen as with
the re-created index.

Regards,
Koen


On Mon, Jun 8, 2020 at 11:15 PM Michael Lewis  wrote:

> On Mon, Jun 8, 2020 at 2:34 PM Koen De Groote  wrote:
>
>> So, this query:
>>
>> select * from item where shouldbebackedup=true and
>> itemCreated<='2020-06-05 00:00:00.000' and backupperformed=false order by
>> filepath asc, id asc limit 100 offset 10400;
>>
>> Was made into a function:
>>
>> create or replace function NeedsBackup(text, int, int default 100)
>> returns setof item as $$
>> BEGIN
>> return query select * from item where shouldbebackedup=true and
>> itemCreated<=$1::timestamp without time zone and backupperformed=false
>> order by filepath asc, id asc limit $3 offset $2;
>> END;
>> $$
>> language 'plpgsql';
>>
>
>
> What I had meant was a function perhaps called backup_needed_still(
> backupperformed bool, shouldbebackedup bool) which would return bool; This
> could be written in SQL only with no need for plpgsql. By the way, the
> language name being in single quotes is deprecated.
>
> Then you could create an index on the table- either on the timestamp
> column where that function returns true, or just creating the index
> directly on the boolean result of that function call if that is what is
> needed to get the custom stats from a functional index. Then you would
> include the function call in your query instead of the two individual
> boolean columns.
>

Re: troubleshooting postgresql ldap authentication

[Chris Stephens]

yes, shortly after i sent this out to the list, one of our security
administrators suggested ldapscheme.  I just tested and ldapurl works as
well.

the security admin explained it like this:

"since we are using port 636 I know that it needs the TLS connection in
place before LDAP commands. starttls does the opposite.  allows an LDAP
connection to "upgrade" to TLS.  so the previous errors were simply it
unable to connect to server."

i'm guessing information like that doesn't belong in postgresql
documentation but it would have been useful yesterday. :)

thanks for the response! i just recently made the switch to postgresql
after 20 years of mainly Oracle. during that time, the oracle-l mailing
list was invaluable as a learning tool and as a way to get help
when needed. it's great to know there's a similar mailing list in the
postgresql community!

On Mon, Jun 8, 2020 at 7:41 PM Thomas Munro  wrote:

> On Tue, Jun 9, 2020 at 9:05 AM Chris Stephens 
> wrote:
> > hostsslall all 0.0.0.0/0  ldap
> ldapserver="ldaps://xxx" ldapbasedn="yyy" ldaptls=1
>
> > does anyone know what might be causing "LDAP: Bad parameter to an ldap
> routine"
>
> You probably want ldapurl="ldaps://xxx" (note: ldapurl, not
> ldapserver).  Or you could use ldapscheme="ldaps" and
> ldapserver="xxx".
>

Re: troubleshooting postgresql ldap authentication

[Stephen Frost]

Greetings,

* Chris Stephens (cstephen...@gmail.com) wrote:
> yes, shortly after i sent this out to the list, one of our security
> administrators suggested ldapscheme.  I just tested and ldapurl works as
> well.
> 
> the security admin explained it like this:
> 
> "since we are using port 636 I know that it needs the TLS connection in
> place before LDAP commands. starttls does the opposite.  allows an LDAP
> connection to "upgrade" to TLS.  so the previous errors were simply it
> unable to connect to server."
> 
> i'm guessing information like that doesn't belong in postgresql
> documentation but it would have been useful yesterday. :)

Might be interesting to know if the security administrator also
understands that the way ldap-based auth works (at least in PG) is that
the user's password is sent to the PG server where it could potentially
be hijacked if the PG server is compromised..

If you're in an active directory environment, you really should be using
the 'gss' method instead, which is Kerberos underneath and avoids that
issue.

> thanks for the response! i just recently made the switch to postgresql
> after 20 years of mainly Oracle. during that time, the oracle-l mailing
> list was invaluable as a learning tool and as a way to get help
> when needed. it's great to know there's a similar mailing list in the
> postgresql community!

You're certainly welcome here!  One thing to mention is that, as you may
have noticed, we communicate on these lists by responding in-line rather
than 'top-posting', since it makes things easier for everyone else on
the list to follow.

Thanks, and welcome!

Stephen


signature.asc
Description: PGP signature

Re: Something else about Redo Logs disappearing

[Adrian Klaver]

On 6/9/20 4:15 AM, Stephen Frost wrote:

Greetings,

* Adrian Klaver (adrian.kla...@aklaver.com) wrote:

I use pg_backrest, but it does not look promising for running on BSD:
https://fluca1978.github.io/2019/03/04/pgbackrest_FreeBSD.html


That's an unfortunately ancient post, really, considering that
pgbackrest has now been fully rewritten into C, and Luca as recently as
September 2019 was saying he has it working on FreeBSD.


Yeah, but this:

https://github.com/pgbackrest/pgbackrest/issues/686

is not clear on whether a user can do that w/o a certain amount of hand 
holding.




If folks do run into issues with pgbackrest on FreeBSD, please let us
know.

Thanks,

Stephen




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

Re: Something else about Redo Logs disappearing

[Stephen Frost]

Greetings,

* Adrian Klaver (adrian.kla...@aklaver.com) wrote:
> On 6/9/20 4:15 AM, Stephen Frost wrote:
> >* Adrian Klaver (adrian.kla...@aklaver.com) wrote:
> >>I use pg_backrest, but it does not look promising for running on BSD:
> >>https://fluca1978.github.io/2019/03/04/pgbackrest_FreeBSD.html
> >
> >That's an unfortunately ancient post, really, considering that
> >pgbackrest has now been fully rewritten into C, and Luca as recently as
> >September 2019 was saying he has it working on FreeBSD.
> 
> Yeah, but this:
> 
> https://github.com/pgbackrest/pgbackrest/issues/686
> 
> is not clear on whether a user can do that w/o a certain amount of hand
> holding.

I've asked Luca to update his blog post and/or re-test on FreeBSD and
he's said he would.  We've moved to using autoconf and friends, and it's
all in C now, so it really shouldn't be as much of an issue these days.
I recall someone else building on FreeBSD not long ago, but not finding
a reference to it offhand.

> >If folks do run into issues with pgbackrest on FreeBSD, please let us
> >know.

... again, this.

Thanks,

Stephen


signature.asc
Description: PGP signature

Postgres server 12.2 crash with process exited abnormally and possibly corrupted shared memory

[Ishan Joshi]

Hi Team,

I have using postgresql server v12.2  on CentOS Linux release 7.3.1611 (Core).

My application is working fine with non partition tables but recently we are 
trying to adopt partition table on few of application tables.
So we have created List partition on 6 table. 2 out of 6 tables have 24 
partitions and 4 out of 6 tables have 500 list partitions. After performing 
partition table, we are trying to run our application it is getting crash as I 
can see the memory utilization is consumed 100% and once it reach to 100%  
Postgres server getting crash with following error

2020-06-09 00:47:41.876 IDT pid:9701 xid:0 ip:10.247.150.107 WARNING:  
terminating connection because of crash of another server process
2020-06-09 00:47:41.876 IDT pid:9701 xid:0 ip:10.247.150.107 DETAIL:  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.
2020-06-09 00:47:44.606 IDT pid:9701 xid:0 ip: HINT:  In a moment you should be 
able to reconnect to the database and repeat your command.
2020-06-09 00:47:41.876 IDT pid:9961 xid:0 ip:10.247.150.107 FATAL:  the 
database system is in recovery mode

Prior to partition changes Postgres server utilizing 30% of total memory. Does 
number of partition is impacting the memory utilization?

My configuration as follows

PG version 12.2
RAM on Server : 78GB
effective_cache_size 44GB
Shared_buffers  21GB
max_connections 2000
work_mem   9MB
max_worker_processes 24

Thanks & Regards,
Ishan Joshi

This email and the information contained herein is proprietary and confidential 
and subject to the Amdocs Email Terms of Service, which you may review at 
https://www.amdocs.com/about/email-terms-of-service 

Re: [HELP] query regarding replication

[Jerry Sievers]

Andreas Kretschmer  writes:

> Am 09.06.20 um 10:44 schrieb Praveen Kumar K S:
>> Thanks. Will this approach replicate DDL changes ?

No.

>
> sure.
>
>
> Regards, Andreas

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net

Planner misestimation for JOIN with VARCHAR

[Sebastian Dressler]

Helloes,

I do have a set of tables which contain user data and users can choose to have 
columns as constrained VARCHAR, limit is typically 100. While users can also 
choose from different types, quite often they go the VARCHAR route. 
Furthermore, they can pick PKs almost freely. As a result, I quite often see 
tables with the following DDL:

CREATE TABLE example_1(
a VARCHAR(100)
  , b VARCHAR(100)
  , c VARCHAR(100)
  , payload TEXT
);
ALTER TABLE example_1 ADD PRIMARY KEY (a, b, c);

Due to processing, these need to be joined together sometimes considering the 
complete PK. For instance, assume example_1 and example_2 having the same 
structure as above. Then, when I do

SELECT *
FROM example_1 t1
INNER JOIN example_2 t2 ON(
  t1.a = t2.a
  AND t1.b = t2.b
  AND t1.c = t2.c
);

the planner will very likely estimate a single resulting row for this 
operation. For instance:

 Gather  (cost=1510826.53..3100992.19 rows=1 width=138)
   Workers Planned: 13
   ->  Parallel Hash Join  (cost=1510726.53..3100892.04 rows=1 width=138)
 Hash Cond: (((t1.a)::text = (t2.a)::text) AND ((t1.b)::text = 
(t2.b)::text) AND ((t1.c)::text = (t1.c)::text))
 ->  Parallel Seq Scan on example_1 t1  (cost=0.00..1351848.61 
rows=7061241 width=69)
 ->  Parallel Hash  (cost=1351848.61..1351848.61 rows=7061241 
width=69)
   ->  Parallel Seq Scan on example_2 t2  
(cost=0.00..1351848.61 rows=7061241 width=69)

This does not create a problem when joining just two tables on their own. 
However, with a more complex query, there will be more than one single-row 
estimates. Hence, I typically see a nested loop which takes very long to 
process eventually.

This runs on PG 12, and I ensured that the tables are analyzed, my 
default_statistics_target is 2500. However, it seems, that with more VARCHARs 
being in the JOIN, the estimates becomes worse. Given the table definition as 
above, I wonder whether I have overlooked anything in terms of settings or 
additional indices which could help here.

Things tried so far without any noticeable change:

- Add an index on top of the whole PK
- Add indexes onto other columns trying to help the JOIN
- Add additional statistics on two related columns

Another idea I had was to make use of generated columns and hash the PKs 
together to an BIGINT and solely use this for the JOIN. However, this would not 
work when not all columns of the PK are used for the JOIN.


Thanks,
Sebastian

--

Sebastian Dressler, Solution Architect 
+49 30 994 0496 72 | sebast...@swarm64.com 

Swarm64 AS
Parkveien 41 B | 0258 Oslo | Norway
Registered at Brønnøysundregistrene in Norway under Org.-Number 911 662 787
CEO/Geschäftsführer (Daglig Leder): Thomas Richter; Chairman/Vorsitzender 
(Styrets Leder): Dr. Sverre Munck 

Swarm64 AS Zweigstelle Hive
Ullsteinstr. 120 | 12109 Berlin | Germany
Registered at Amtsgericht Charlottenburg - HRB 154382 B 

Re: Something else about Redo Logs disappearing

[Peter]

On Mon, Jun 08, 2020 at 09:21:47PM -0700, Adrian Klaver wrote:
! 
! On 6/8/20 7:33 PM, Peter wrote:
! > 
! > Actually, the affair had some good side: as usual I was checking
! > my own designs first and looking for flaws, and indeed I found one:
! > If you do copy out the archive logs not directly to tape, but to
! > some disk area for further processing, then there is an issue with
! > possible loss. If you do it like the docs say, with a command like
! > this:
! > archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p
! > +/mnt/server/archivedir/%f'  # Unix
! > That "cp" is usually not synchronous. So there is the possibility
! > that this command terminates successfully, and reports exitcode zero
! > back to the Postgres, and then the Postgres will consider that log
! > being safely away.
! 
! Which is why just following the above command in the docs is:
! 
! "(This is an example, not a recommendation, and might not work on all
! platforms.) "

So, what You are basically saying is: my worries are justified and
correctly founded, and this is indeed a matter that needs to be taken
care of.
Thank You.

! Generally for peace of mind folks use third party tools like:
! 
! pg_backrest(https://pgbackrest.org/),
! pg_probackup(https://postgrespro.com/products/extensions/pg_probackup) or
! Barman(https://www.pgbarman.org/).

Hmja. We may on occasion have a look into these...

! I use pg_backrest, but it does not look promising for running on BSD:
! https://fluca1978.github.io/2019/03/04/pgbackrest_FreeBSD.html

That looks mostly like the usual things which can be fixed.

Now, for the facts: I am already using a professional backup
solution. (It is actually a "dual-use/commercial" solution, of the
kind which you can either fetch from github and use without support,
or buy with a contract or whatever and get support.)

With this professional backup solution I have already identified 28
(spell: twenty-eight) bugs, and fixed/workarounded these, until I got it
properly working.

This professional backup solution also offers support for postgres.
Sadly, it only covers postgres up to Rel.9, and that piece of software
wasn't touched in the last 6 or 7 years.
But the bigger issue there is, that backup solution needs it's
own postgres database as it's backend - and it cannot backup the
database it is using. Looks quite pointless to me, then.
So I just did it all with shell (and it wasn't many lines).

So now, as I've been thru identifying and handling all the issues in
that one backup solution, and since it is supposed to handle *all*
backup demands (and not only postgres), I will certainly not start
and go thru the same process again with one of these supposed
solutions, where 90% of the code tries to solve the same things
redundantly again, but then only for PG.


Actually, I am getting very tired of reading that something which can
easily be done within 20 lines of shell scripting, would need special
"solutions", solutions that need to be compiled, solutions that would
bring along their own fashion of interpreter, solutions that have a
lot of their own dependencies and introduce mainly one thing: new bugs.

Does nobody know anymore how to do proper systems management
scripting? Using just the basic system tools which have proven to
work for more than 50 years now!?

! Not sure about pg_probackup.

Okay, I had a -very short- look into these. Just scanning the
introductory pages.

The only really interesting thing there is the pg_probackup. These
folks seem to have found a way to do row-level incremental backups.

And pgbarman seems to have an impressive understanding of ITIL (in
case anybody bothers about that).

All these tools do only cover PG, but do that in any possible regards.

This is fine as long as you do not run any computers, and the only
application you are using is Postgres.
But, if you have other applications as well, or have computers, then
you will need a different backup solution, something that will cover
your site-wide backup demands, in a consistent fashion (think
something in the style of ADSM, or nowadays called Spectrum Protect).

And then 90% of the things offered here become superfluous, because
they are already handled site-wide. And then you will have to
consider integration of both pieces - and that will most likely be
more work and more error-prone than just writing a few adapters in
shell.



cheerio,
PMc

Re: Planner misestimation for JOIN with VARCHAR

[Michael Lewis]

On Tue, Jun 9, 2020 at 12:34 PM Sebastian Dressler 
wrote:

> - Add an index on top of the whole PK
> - Add indexes onto other columns trying to help the JOIN
> - Add additional statistics on two related columns
>
> Another idea I had was to make use of generated columns and hash the PKs
> together to an BIGINT and solely use this for the JOIN. However, this would
> not work when not all columns of the PK are used for the JOIN.
>

Can you expand on the additional statistics you created? Why was it on only
two columns? Did you include MCVs type of extended stats?

Re: Something else about Redo Logs disappearing

[Adrian Klaver]

On 6/9/20 10:55 AM, Peter wrote:

On Mon, Jun 08, 2020 at 09:21:47PM -0700, Adrian Klaver wrote:
!
! On 6/8/20 7:33 PM, Peter wrote:
! >
! > Actually, the affair had some good side: as usual I was checking
! > my own designs first and looking for flaws, and indeed I found one:
! > If you do copy out the archive logs not directly to tape, but to
! > some disk area for further processing, then there is an issue with
! > possible loss. If you do it like the docs say, with a command like
! > this:
! > archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p
! > +/mnt/server/archivedir/%f'  # Unix
! > That "cp" is usually not synchronous. So there is the possibility
! > that this command terminates successfully, and reports exitcode zero
! > back to the Postgres, and then the Postgres will consider that log
! > being safely away.
!
! Which is why just following the above command in the docs is:
!
! "(This is an example, not a recommendation, and might not work on all
! platforms.) "

So, what You are basically saying is: my worries are justified and
correctly founded, and this is indeed a matter that needs to be taken
care of.
Thank You.

! Generally for peace of mind folks use third party tools like:
!
! pg_backrest(https://pgbackrest.org/),
! pg_probackup(https://postgrespro.com/products/extensions/pg_probackup) or
! Barman(https://www.pgbarman.org/).

Hmja. We may on occasion have a look into these...

! I use pg_backrest, but it does not look promising for running on BSD:
! https://fluca1978.github.io/2019/03/04/pgbackrest_FreeBSD.html

That looks mostly like the usual things which can be fixed.

Now, for the facts: I am already using a professional backup
solution. (It is actually a "dual-use/commercial" solution, of the
kind which you can either fetch from github and use without support,
or buy with a contract or whatever and get support.)

With this professional backup solution I have already identified 28
(spell: twenty-eight) bugs, and fixed/workarounded these, until I got it
properly working.

This professional backup solution also offers support for postgres.
Sadly, it only covers postgres up to Rel.9, and that piece of software
wasn't touched in the last 6 or 7 years.
But the bigger issue there is, that backup solution needs it's
own postgres database as it's backend - and it cannot backup the
database it is using. Looks quite pointless to me, then.
So I just did it all with shell (and it wasn't many lines).


The backup solution is?



So now, as I've been thru identifying and handling all the issues in
that one backup solution, and since it is supposed to handle *all*
backup demands (and not only postgres), I will certainly not start
and go thru the same process again with one of these supposed
solutions, where 90% of the code tries to solve the same things
redundantly again, but then only for PG.


They are not supposed. They are in use by many people/organizations 
across a wide variety of installations.





Actually, I am getting very tired of reading that something which can
easily be done within 20 lines of shell scripting, would need special
"solutions", solutions that need to be compiled, solutions that would
bring along their own fashion of interpreter, solutions that have a
lot of their own dependencies and introduce mainly one thing: new bugs.


They where developed as they could not be done in 20 lines of shell 
scripting and work at a reliable level.


Fine rant below. Go forth and work your wonders.



Does nobody know anymore how to do proper systems management
scripting? Using just the basic system tools which have proven to
work for more than 50 years now!?

! Not sure about pg_probackup.

Okay, I had a -very short- look into these. Just scanning the
introductory pages.

The only really interesting thing there is the pg_probackup. These
folks seem to have found a way to do row-level incremental backups.

And pgbarman seems to have an impressive understanding of ITIL (in
case anybody bothers about that).

All these tools do only cover PG, but do that in any possible regards.

This is fine as long as you do not run any computers, and the only
application you are using is Postgres.
But, if you have other applications as well, or have computers, then
you will need a different backup solution, something that will cover
your site-wide backup demands, in a consistent fashion (think
something in the style of ADSM, or nowadays called Spectrum Protect).

And then 90% of the things offered here become superfluous, because
they are already handled site-wide. And then you will have to
consider integration of both pieces - and that will most likely be
more work and more error-prone than just writing a few adapters in
shell.



cheerio,
PMc




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

Re: Something else about Redo Logs disappearing

[Stephen Frost]

Greetings,

* Peter (p...@citylink.dinoex.sub.org) wrote:
> This professional backup solution also offers support for postgres.
> Sadly, it only covers postgres up to Rel.9, and that piece of software
> wasn't touched in the last 6 or 7 years.

Then it certainly doesn't work with the changes in v12, and probably has
other issues, as you allude to.

> Actually, I am getting very tired of reading that something which can
> easily be done within 20 lines of shell scripting, would need special

This is just simply false- you can't do it properly in 20 lines of shell
scripting.  Sure, you can write something that has probably next to no
error checking, uses the deprecated API that'll cause your systems to
fail to start if you ever happen to have a reboot during a backup, and
has no way to provide verification that the backup was at all successful
after the fact, but that's not what I'd consider a proper solution-
instead it's one that'll end up causing you a lot of pain down the road.

Even the shell-script based solution (which I've never used and
personally wouldn't really recommend, but to each their own) called
'pitery' (available here: https://github.com/dalibo/pitrery) is
thousands of lines of code.

> Does nobody know anymore how to do proper systems management
> scripting? Using just the basic system tools which have proven to
> work for more than 50 years now!?

I've not met anything I'd call 'proper systems management scripting'
that's 20 lines of code, shell script or not.

> ! Not sure about pg_probackup.
> 
> Okay, I had a -very short- look into these. Just scanning the
> introductory pages.
> 
> The only really interesting thing there is the pg_probackup. These
> folks seem to have found a way to do row-level incremental backups.

pg_probackup doesn't do row-level incremental backups, unless I've
missed some pretty serious change in its development, but it does
provide page-level, with, as I recall, an extension that didn't get
good reception when it was posted and discussed on these mailing lists
by other PG hackers.  I don't know if those concerns about it have been
addressed or not, you might ask the pg_probackup folks if you're
considering it as a solution.

> This is fine as long as you do not run any computers, and the only
> application you are using is Postgres.
> But, if you have other applications as well, or have computers, then
> you will need a different backup solution, something that will cover
> your site-wide backup demands, in a consistent fashion (think
> something in the style of ADSM, or nowadays called Spectrum Protect).
> 
> And then 90% of the things offered here become superfluous, because
> they are already handled site-wide. And then you will have to
> consider integration of both pieces - and that will most likely be
> more work and more error-prone than just writing a few adapters in
> shell.

pgbackrest's repo can be safely backed up using the simple file-based
backup utilities that you're referring to here.  I suspect some of the
other solution's backups also could be, but you'd probably want to make
sure.

PG generally isn't something that can be backed up using the simple file
based backup solutions, as you might appreciate from just considering
the number of tools written to specifically deal with the complexity of
backing up an online PG cluster.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Postgres server 12.2 crash with process exited abnormally and possibly corrupted shared memory

[Michael Lewis]

On Tue, Jun 9, 2020 at 8:35 AM Ishan Joshi  wrote:

> I have using postgresql server v12.2  on CentOS Linux release 7.3.1611
> (Core).
>
>
>
> My application is working fine with non partition tables but recently we
> are trying to adopt partition table on few of application tables.
>
> So we have created List partition on 6 table. 2 out of 6 tables have 24
> partitions and 4 out of 6 tables have 500 list partitions. After performing
> partition table, we are trying to run our application it is getting crash
> as I can see the memory utilization is consumed 100% and once it reach to
> 100%  Postgres server getting crash with following error
>


How many rows did these tables have before partitioning? Why did you decide
to partition? Do these list partitions allow for plan-time pruning? Do they
support partition wise joins? work_mem can be used for each node of the
plan and if you are getting parallel scans of many tables or indexes where
you previously had one, that could be an issue.

2000 for max_connections strikes me as quite high. Consider the use of a
connection pooler like pgbouncer or pgpool such that Postgres can be run
with max connections more like 2-5x your number of CPUs, and those
connections get re-used as needed. There is some fixed memory overhead for
each potential connection.

Re: Something else about Redo Logs disappearing

[Peter]

On Tue, Jun 09, 2020 at 01:27:20AM -0400, Tom Lane wrote:
! Adrian Klaver  writes:
! > On 6/8/20 7:33 PM, Peter wrote:
! >> That "cp" is usually not synchronous. So there is the possibility
! >> that this command terminates successfully, and reports exitcode zero
! >> back to the Postgres, and then the Postgres will consider that log
! >> being safely away.
! 
! > Which is why just following the above command in the docs is:
! > "(This is an example, not a recommendation, and might not work on all 
! > platforms.) "
! 
! Yeah.  There have been discussions about changing that disclaimer to be
! more forceful, because in point of fact a plain "cp" is generally not safe
! enough.  You need to fsync the written file, and on many filesystems you
! also have to fsync the directory it's in.

It certainly does not need to be "more forceful" - because this is not
about behavioural education, like training dogs, horses, or monkeys,
and neither do we entertain a BDSM studio.

What it needs instead is mention of the magic word "fsync". Because,
we already know that - we just need a reminder at the proper place.

Systems integrators are professional people. They are not in need of
more beating (spell: forceful education), only of substantial
technical hints and informations.

! > Generally for peace of mind folks use third party tools like:
! 
! +1.  Rolling your own archive script is seldom advisable.

Well then, using commercial solutions brings it's own problems. E.g.,
the people I happened to work for often had problems with torsion,
which happens when the solution gets longer than, say, twenty meters,
and these are walked at high speeds.

They didn't have a problem with scripting - rather the opposite, they
were happy with it and paid good money for.


cheerio,
PMc

Re: Something else about Redo Logs disappearing

[Adrian Klaver]

On 6/9/20 12:02 PM, Peter wrote:

On Tue, Jun 09, 2020 at 01:27:20AM -0400, Tom Lane wrote:
! Adrian Klaver  writes:
! > On 6/8/20 7:33 PM, Peter wrote:
! >> That "cp" is usually not synchronous. So there is the possibility
! >> that this command terminates successfully, and reports exitcode zero
! >> back to the Postgres, and then the Postgres will consider that log
! >> being safely away.
!
! > Which is why just following the above command in the docs is:
! > "(This is an example, not a recommendation, and might not work on all
! > platforms.) "
!
! Yeah.  There have been discussions about changing that disclaimer to be
! more forceful, because in point of fact a plain "cp" is generally not safe
! enough.  You need to fsync the written file, and on many filesystems you
! also have to fsync the directory it's in.

It certainly does not need to be "more forceful" - because this is not
about behavioural education, like training dogs, horses, or monkeys,
and neither do we entertain a BDSM studio.

What it needs instead is mention of the magic word "fsync". Because,
we already know that - we just need a reminder at the proper place.

Systems integrators are professional people. They are not in need of
more beating (spell: forceful education), only of substantial
technical hints and informations.

! > Generally for peace of mind folks use third party tools like:
!
! +1.  Rolling your own archive script is seldom advisable.

Well then, using commercial solutions brings it's own problems. E.g.,


FYI, the projects Stephen and I mentioned are Open Source. I'm sure you 
can get paid support for them, but you exist a higher plane then that so 
you can use then for free.



the people I happened to work for often had problems with torsion,
which happens when the solution gets longer than, say, twenty meters,
and these are walked at high speeds.

They didn't have a problem with scripting - rather the opposite, they
were happy with it and paid good money for.


cheerio,
PMc




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

Re: Planner misestimation for JOIN with VARCHAR

[David Rowley]

On Wed, 10 Jun 2020 at 07:31, Michael Lewis  wrote:
>
> On Tue, Jun 9, 2020 at 12:34 PM Sebastian Dressler  
> wrote:
>>
>> - Add an index on top of the whole PK
>> - Add indexes onto other columns trying to help the JOIN
>> - Add additional statistics on two related columns
>>
>> Another idea I had was to make use of generated columns and hash the PKs 
>> together to an BIGINT and solely use this for the JOIN. However, this would 
>> not work when not all columns of the PK are used for the JOIN.
>
>
> Can you expand on the additional statistics you created? Why was it on only 
> two columns? Did you include MCVs type of extended stats?

Unfortunately, the join selectivity functions have yet to learn about
extended statistics.

David

Re: Planner misestimation for JOIN with VARCHAR

[Michael Lewis]

>
> the join selectivity functions have yet to learn about extended statistics.
>

That is very interesting to me. So, extended statistics would help to
properly estimate the result set coming out of a single table when
comparing each of those columns to one or many values, but not when joining
up another table? Is that on a roadmap or in progress as far as you know?

Re: Logical replication stuck in catchup state

[Michael Lewis]

I don't know if it would be relevant to this problem, but you are missing
almost 1 full year of bug fixes. 11.4 was released on 20 June last year.
Upgrading minor versions asap is recommended.

I do see this in the release notes from 11.8 last month (
https://www.postgresql.org/docs/release/11.8/)-

Ensure that a replication slot's io_in_progress_lock is released in failure
code paths (Pavan Deolasee)
--This could result in a walsender later becoming stuck waiting for the
lock.

Re: Logical replication stuck in catchup state

[Peter Eisentraut]

On 2020-06-09 23:30, Dan shmidt wrote:
We have a setup in which there are several master nodes replicating to a 
single slave/backup node. We are using Postgres 11.4.

Recently, one of the nodes seems to be stuck and stopped replicating.
I did some basic troubleshooting and couldn't find the root cause for that.


Have you checked the server logs?  Maybe it has trouble applying a 
change, for example due to a unique constraint or something like that.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Planner misestimation for JOIN with VARCHAR

[David Rowley]

On Wed, 10 Jun 2020 at 09:05, Michael Lewis  wrote:
>>
>> the join selectivity functions have yet to learn about extended statistics.
>
>
> That is very interesting to me. So, extended statistics would help to 
> properly estimate the result set coming out of a single table when comparing 
> each of those columns to one or many values, but not when joining up another 
> table? Is that on a roadmap or in progress as far as you know?

Yeah, they're currently only used for selectivity estimates on base
table.  Using extended statistics for join selectivity estimation is
being worked on [1], so there's a chance that it may happen for PG14.

David

[1] https://commitfest.postgresql.org/28/2386/

Re: Something else about Redo Logs disappearing

[Peter]

On Tue, Jun 09, 2020 at 12:34:38PM -0700, Adrian Klaver wrote:

! The backup solution is?

https://www.bareos.com/

! Fine rant below. Go forth and work your wonders.

I don't need to, anymore. I did that, for about 20 years - people
I used to work for as a consultant (major banks and insurance shops)
would usually run Informix or Oracle. Postgres is just my own private
fancy.

On Tue, Jun 09, 2020 at 03:42:48PM -0400, Stephen Frost wrote:
! * Peter (p...@citylink.dinoex.sub.org) wrote:
! > This professional backup solution also offers support for postgres.
! > Sadly, it only covers postgres up to Rel.9, and that piece of software
! > wasn't touched in the last 6 or 7 years.
! 
! Then it certainly doesn't work with the changes in v12, and probably has
! other issues, as you allude to.

Just having a look at their webpage, something seems to have been updated
recently, they now state that they have a new postgres adapter:

https://www.bareos.com/en/company_news/postgres-plugin-en1.html
Enjoy reading, and tell us what You think.

! > Actually, I am getting very tired of reading that something which can
! > easily be done within 20 lines of shell scripting, would need special
! 
! This is just simply false- you can't do it properly in 20 lines of shell
! scripting.

Well, Your own docs show how to do it with a one-liner. So please
don't blame me for improving that to 20 lines.

! Sure, you can write something that has probably next to no
! error checking,

Before judging that, one should first specify what precisely is the
demand.
In a basic approach, the demand may be to get the logs out on tape in
a failsafe automated fashion without any miss, and get the data tree
out periodically, and have guaranteed that these files are untampered
as on disk.

And that can very well be done properly with an incremental filesystem
backup software plus some 20 lines of shellscript.

Now talking about doing an automated restore, or, having some menu-
driven solution, or -the worst of all- having a solution that can be
operated by morons - that's an entirely different matter.

In my understanding, backup is done via pgdump. The archive logs are
for emergencies (data corruption, desaster), only. And emergencies
would usually be handled by some professional people who know what
they have to do.

You may consider different demands, and that is also fine, but doesn't
need to concern me.

! uses the deprecated API that'll cause your systems to
! fail to start if you ever happen to have a reboot during a backup

It is highly unlikely that I did never have that happen during 15
years. So what does that mean? If I throw in a pg_start_backup('bogus'),
and then restart the cluster, it will not work anymore?
Lets see...
Clean stop/start - no issue whatsoever. (LOG:  online backup mode
canceled)
kill -9 the whole flock - no issue whatsoever (Log: database system
was interrupted)
I won't pull the plug now, but that has certainly happened lots of
times in the past, and also yielded no issue whatsoever - simply
because there *never* was *any* issue whatsover with Postgres (until
I got the idea to install the relatively fresh R.12 - but that's
understandable).

So maybe this problem exists only on Windows?

And yes, I read that whole horrible discussion, and I could tear my
hair out, really, concerning the "deprecated API". I suppose You mean
the mentioning in the docs that the "exclusive low-level backup" is
somehow deprecated.
This is a very big bad. Because: normally you can run the base backup
as a strictly ordinary file-level backup in "full" mode, just as any
backup software can do it. You will simply execute the
pg_start_backup() and pg_stop_backup() commands in the before- and
after- hooks - and any backup software will offer these hooks.

But now, with the now recommended "non-exclusive low-level backup",
the task is different: now your before-hook needs to do two things
at the same time:
 1. keep a socket open in order to hold the connection to postgres
(because postgres will terminate the backup when the socket is
closed), and
 2. invoke exit(0) (because the actual backup will not start until
the before- hook has properly delivered a successful exit code.
And, that is not only difficult, it is impossible.

So, what has to be done instead: you need to write a separate network
daemon, with the only purpose of holding that connection to postgres
open. And that network daemon needs to handle the communication to
the backup software on one side, and to postgres on the other side.
And that network daemon then needs the full-blown feature requirements
as a fail-safe network daemon should have (and that is a LOT!), plus
it needs to handle all kinds of possible failures (like network
interruptions) in that triangle, during the backup, and properly
notify both sides of whatever might be ongoing (and that is NOT
trivial).

So yes, this is really a LOT of work. But the point is: this all is
not really necessary, because currently

Help with plpython3u

[PEDRO PABLO SEVERIN HONORATO]

Hello!

I'm cracking my head while trying to make python language work within
postges. I executed "*create extension plpython3u*" but I get this error:

"*ERROR: could not load library "C:/Program
Files/PostgreSQL/12/lib/plpython3.dll": The specified module could not be
found. SQL state: 58P01*"

Looking into several stackoverflow pages and asking like everywhere, I also
installed dependency walker and saw that python37.dll was required. My PC
already has python 3.7.4, but I don't know if that is an issue and postgres
requires python 3.7.0 strictly.

I also copied the python37.dll file directly in the System32 folder, as
some pages suggested. That actually made it possible to run the "* create
extension plpython3u* ", but when I try to create a simple function like:

"*CREATE FUNCTION pymax (a integer, b integer)*





*  RETURNS integerAS $$  if a > b:return a  return b*
*$$ LANGUAGE plpython3u*;"

The server crashes and I get this message: "*Connection to the server has
been lost*."

Some additional information:
- My PC is running windows server 2019 64 bits (its a vps)
- The python version I have is 3.7.4. Installed it with Anaconda
- Postgres version is 12.2-1. pgAdmin is 4.18

Please, help me :(
Regards,
PS

Re: Help with plpython3u

[Adrian Klaver]

On 6/9/20 5:09 PM, PEDRO PABLO SEVERIN HONORATO wrote:

Hello!

I'm cracking my head while trying to make python language work within 
postges. I executed "*create extension plpython3u*" but I get this error:


"*ERROR: could not load library "C:/Program 
Files/PostgreSQL/12/lib/plpython3.dll": The specified module could not 
be found. SQL state: 58P01*"


Looking into several stackoverflow pages and asking like everywhere, I 
also installed dependency walker and saw that python37.dll was required. 
My PC already has python 3.7.4, but I don't know if that is an issue and 
postgres requires python 3.7.0 strictly.


I also copied the python37.dll file directly in the System32 folder, as 
some pages suggested. That actually made it possible to run the 
"* create extension plpython3u* ", but when I try to create a simple 
function like:


"*CREATE FUNCTION pymax (a integer, b integer)*
*  RETURNS integer
AS $$
   if a > b:
     return a
   return b
*
*$$ LANGUAGE plpython3u*;"

The server crashes and I get this message: "*Connection to the server 
has been lost*."


Some additional information:
- My PC is running windows server 2019 64 bits (its a vps)


How did you install Postgres?


- The python version I have is 3.7.4. Installed it with Anaconda


Unless things have changed since the last time I used Anaconda, it 
basically creates virtualenvs for it's installs. So when you refer to 
the Python dll above are you talking about a system installed one or one 
from within the Anaconda environment?



- Postgres version is 12.2-1. pgAdmin is 4.18

Please, help me :(
Regards,
PS



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

Re: Something else about Redo Logs disappearing

[Stephen Frost]

Greetings,

* Peter (p...@citylink.dinoex.sub.org) wrote:
> On Tue, Jun 09, 2020 at 03:42:48PM -0400, Stephen Frost wrote:
> ! * Peter (p...@citylink.dinoex.sub.org) wrote:
> ! > This professional backup solution also offers support for postgres.
> ! > Sadly, it only covers postgres up to Rel.9, and that piece of software
> ! > wasn't touched in the last 6 or 7 years.
> ! 
> ! Then it certainly doesn't work with the changes in v12, and probably has
> ! other issues, as you allude to.
> 
> Just having a look at their webpage, something seems to have been updated
> recently, they now state that they have a new postgres adapter:
> 
> https://www.bareos.com/en/company_news/postgres-plugin-en1.html
> Enjoy reading, and tell us what You think.

I'm afraid I'm not particularly interested in performing a pro bono
evaluation of a commercial product, though considering they've put out a
press release with obviously broken links, I already have suspicions of
what I'd find ... (try clicking on their 'experimental/nightly' link).

A quick look at the docs also shows that it's referring to
recovery.conf, which no longer exists since v12 was released back in
September, so, yeah, isn't exactly current.

> ! > Actually, I am getting very tired of reading that something which can
> ! > easily be done within 20 lines of shell scripting, would need special
> ! 
> ! This is just simply false- you can't do it properly in 20 lines of shell
> ! scripting.
> 
> Well, Your own docs show how to do it with a one-liner. So please
> don't blame me for improving that to 20 lines.

No, the documentation provides an example for the purpose of
understanding how the replacement in the command is done and explicitly
says that you probably shouldn't use that command.

> ! Sure, you can write something that has probably next to no
> ! error checking,
> 
> Before judging that, one should first specify what precisely is the
> demand.

I really don't need to in order to be able to judge the notion of a 20
line shell script being able to manage to perform a backup correctly.

> In my understanding, backup is done via pgdump. The archive logs are
> for emergencies (data corruption, desaster), only. And emergencies
> would usually be handled by some professional people who know what
> they have to do.

No, that's not the case.  pg_dump isn't at all involved in the backups
that we're talking about here which are physical, file-level, backups.

> ! uses the deprecated API that'll cause your systems to
> ! fail to start if you ever happen to have a reboot during a backup
> 
> It is highly unlikely that I did never have that happen during 15
> years. So what does that mean? If I throw in a pg_start_backup('bogus'),
> and then restart the cluster, it will not work anymore?

If you perform a pg_start_backup(), have a checkpoint happen such that
older WAL is removed, and then reboot the box or kill -9 postgres, no,
it's not going to start anymore because there's going to be a
backup_label file that is telling the cluster that it needs to start
replaying WAL from an older point in time than what you've got WAL for.

> Lets see...
> Clean stop/start - no issue whatsoever. (LOG:  online backup mode
> canceled)
> kill -9 the whole flock - no issue whatsoever (Log: database system
> was interrupted)
> I won't pull the plug now, but that has certainly happened lots of
> times in the past, and also yielded no issue whatsoever - simply
> because there *never* was *any* issue whatsover with Postgres (until
> I got the idea to install the relatively fresh R.12 - but that's
> understandable).

Being lucky really isn't what you want to bet on.

> So maybe this problem exists only on Windows?

No, it's not Windows specific.

> And yes, I read that whole horrible discussion, and I could tear my
> hair out, really, concerning the "deprecated API". I suppose You mean
> the mentioning in the docs that the "exclusive low-level backup" is
> somehow deprecated.

Yes, it's deprecated specifically because of the issues outlined above.
They aren't hypothetical, they do happen, and people do get bit by them.

> This is a very big bad. Because: normally you can run the base backup
> as a strictly ordinary file-level backup in "full" mode, just as any
> backup software can do it. You will simply execute the
> pg_start_backup() and pg_stop_backup() commands in the before- and
> after- hooks - and any backup software will offer these hooks.
> 
> But now, with the now recommended "non-exclusive low-level backup",
> the task is different: now your before-hook needs to do two things
> at the same time:
>  1. keep a socket open in order to hold the connection to postgres
> (because postgres will terminate the backup when the socket is
> closed), and
>  2. invoke exit(0) (because the actual backup will not start until
> the before- hook has properly delivered a successful exit code.
> And, that is not only difficult, it is impossible.

One would imagine that if the commercial 

Re: Help with plpython3u

[PEDRO PABLO SEVERIN HONORATO]

Hi Adrian,

I installed Postgres downloading the file posgresql-12.2-1-windows-x64 from
the website https://www.postgresql.org/download/windows/.

The python37.dll I copied to the System32 folder was located
in C:\Users\developer\Anaconda3.
After trying this, I installed python 3.7.0 and copied the python37.dll
that comes with the installation, but got the same result.

Thanks,
PS

El mar., 9 jun. 2020 a las 20:17, Adrian Klaver ()
escribió:

> On 6/9/20 5:09 PM, PEDRO PABLO SEVERIN HONORATO wrote:
> > Hello!
> >
> > I'm cracking my head while trying to make python language work within
> > postges. I executed "*create extension plpython3u*" but I get this error:
> >
> > "*ERROR: could not load library "C:/Program
> > Files/PostgreSQL/12/lib/plpython3.dll": The specified module could not
> > be found. SQL state: 58P01*"
> >
> > Looking into several stackoverflow pages and asking like everywhere, I
> > also installed dependency walker and saw that python37.dll was required.
> > My PC already has python 3.7.4, but I don't know if that is an issue and
> > postgres requires python 3.7.0 strictly.
> >
> > I also copied the python37.dll file directly in the System32 folder, as
> > some pages suggested. That actually made it possible to run the
> > "* create extension plpython3u* ", but when I try to create a simple
> > function like:
> >
> > "*CREATE FUNCTION pymax (a integer, b integer)*
> > *  RETURNS integer
> > AS $$
> >if a > b:
> >  return a
> >return b
> > *
> > *$$ LANGUAGE plpython3u*;"
> >
> > The server crashes and I get this message: "*Connection to the server
> > has been lost*."
> >
> > Some additional information:
> > - My PC is running windows server 2019 64 bits (its a vps)
>
> How did you install Postgres?
>
> > - The python version I have is 3.7.4. Installed it with Anaconda
>
> Unless things have changed since the last time I used Anaconda, it
> basically creates virtualenvs for it's installs. So when you refer to
> the Python dll above are you talking about a system installed one or one
> from within the Anaconda environment?
>
> > - Postgres version is 12.2-1. pgAdmin is 4.18
> >
> > Please, help me :(
> > Regards,
> > PS
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Something else about Redo Logs disappearing

[Adrian Klaver]

On 6/9/20 4:35 PM, Peter wrote:

On Tue, Jun 09, 2020 at 12:34:38PM -0700, Adrian Klaver wrote:





And that can very well be done properly with an incremental filesystem
backup software plus some 20 lines of shellscript.


Read the caveats here:

https://www.postgresql.org/docs/12/backup-file.html



Now talking about doing an automated restore, or, having some menu-
driven solution, or -the worst of all- having a solution that can be
operated by morons - that's an entirely different matter.

In my understanding, backup is done via pgdump. The archive logs are
for emergencies (data corruption, desaster), only. And emergencies
would usually be handled by some professional people who know what
they have to do.


Read the entire section below, for why WAL's are for backup also. FYI, 
if you don't properly set it up then you may not be protected for data 
corruption. See PITR.


https://www.postgresql.org/docs/12/continuous-archiving.html

Postgres is used by a wide gamut of people of differing abilities, many 
of who appreciate the availability of tested solutions to protect their 
data as they are not morons and understand there are people who can make 
their life easier.






This was actually my job as a consultant: to de-mystify technology,
and make it understandable as an arrangement of well explainable
pieces of functionality with well-deducible consequences.


Not seeing it.


But this is no longer respected today; now people are expected to
*NOT* understand the technology they handle, and instead believe
in marketing and that it all is very complicated and un-intellegible.


cheerio,
PMc




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

Re: Help with plpython3u

[Adrian Klaver]

On 6/9/20 5:26 PM, PEDRO PABLO SEVERIN HONORATO wrote:

Hi Adrian,

I installed Postgres downloading the file posgresql-12.2-1-windows-x64 
from the website https://www.postgresql.org/download/windows/.


Did you go through the steps here?:

https://www.enterprisedb.com/edb-docs/d/postgresql/user-guides/language-pack-guide/12/toc.html



The python37.dll I copied to the System32 folder was located 
in C:\Users\developer\Anaconda3.
After trying this, I installed python 3.7.0 and copied the python37.dll 
that comes with the installation, but got the same result.


Thanks,
PS

El mar., 9 jun. 2020 a las 20:17, Adrian Klaver 
(mailto:adrian.kla...@aklaver.com>>) escribió:


On 6/9/20 5:09 PM, PEDRO PABLO SEVERIN HONORATO wrote:
 > Hello!
 >
 > I'm cracking my head while trying to make python language work
within
 > postges. I executed "*create extension plpython3u*" but I get
this error:
 >
 > "*ERROR: could not load library "C:/Program
 > Files/PostgreSQL/12/lib/plpython3.dll": The specified module
could not
 > be found. SQL state: 58P01*"
 >
 > Looking into several stackoverflow pages and asking like
everywhere, I
 > also installed dependency walker and saw that python37.dll was
required.
 > My PC already has python 3.7.4, but I don't know if that is an
issue and
 > postgres requires python 3.7.0 strictly.
 >
 > I also copied the python37.dll file directly in the System32
folder, as
 > some pages suggested. That actually made it possible to run the
 > "* create extension plpython3u* ", but when I try to create a simple
 > function like:
 >
 > "*CREATE FUNCTION pymax (a integer, b integer)*
 > *  RETURNS integer
 > AS $$
 >    if a > b:
 >      return a
 >    return b
 > *
 > *$$ LANGUAGE plpython3u*;"
 >
 > The server crashes and I get this message: "*Connection to the
server
 > has been lost*."
 >
 > Some additional information:
 > - My PC is running windows server 2019 64 bits (its a vps)

How did you install Postgres?

 > - The python version I have is 3.7.4. Installed it with Anaconda

Unless things have changed since the last time I used Anaconda, it
basically creates virtualenvs for it's installs. So when you refer to
the Python dll above are you talking about a system installed one or
one
from within the Anaconda environment?

 > - Postgres version is 12.2-1. pgAdmin is 4.18
 >
 > Please, help me :(
 > Regards,
 > PS


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Help with plpython3u

[PEDRO PABLO SEVERIN HONORATO]

Thanks Adrian, this actually helped me. I was looking for the "language
pack" thing without any success and thanks god that you pointed me in the
right direction :)

What I don't understand is the following. After installing the language
pack with stack builder, added a variable named "PYTHONHOME" with its path (
C:\edb\languagepack\v1\Python-3.7) and then added PYTHONHOME and the full
path to the PATH variable. After that, I restarted the server as requested
but nothing happened. Then I added "%" to the beginning and the end of
PYTHONHOME and C:\edb\languagepack\v1\Python-3.7, restarted the server and
then it worked. Why is that?

Also, I would like to know if it is possible to import specific python
libraries? For example, I would like to use the talib library, which can be
downloaded and installed. Or a custom library made by myself. And how can I
import other python libraries, like scikit learn? Where can I find
information about this?

Thank you so much for your help!! :D
PS

El mar., 9 jun. 2020 a las 20:37, Adrian Klaver ()
escribió:

> On 6/9/20 5:26 PM, PEDRO PABLO SEVERIN HONORATO wrote:
> > Hi Adrian,
> >
> > I installed Postgres downloading the file posgresql-12.2-1-windows-x64
> > from the website https://www.postgresql.org/download/windows/.
>
> Did you go through the steps here?:
>
>
> https://www.enterprisedb.com/edb-docs/d/postgresql/user-guides/language-pack-guide/12/toc.html
>
> >
> > The python37.dll I copied to the System32 folder was located
> > in C:\Users\developer\Anaconda3.
> > After trying this, I installed python 3.7.0 and copied the python37.dll
> > that comes with the installation, but got the same result.
> >
> > Thanks,
> > PS
> >
> > El mar., 9 jun. 2020 a las 20:17, Adrian Klaver
> > (mailto:adrian.kla...@aklaver.com>>)
> escribió:
> >
> > On 6/9/20 5:09 PM, PEDRO PABLO SEVERIN HONORATO wrote:
> >  > Hello!
> >  >
> >  > I'm cracking my head while trying to make python language work
> > within
> >  > postges. I executed "*create extension plpython3u*" but I get
> > this error:
> >  >
> >  > "*ERROR: could not load library "C:/Program
> >  > Files/PostgreSQL/12/lib/plpython3.dll": The specified module
> > could not
> >  > be found. SQL state: 58P01*"
> >  >
> >  > Looking into several stackoverflow pages and asking like
> > everywhere, I
> >  > also installed dependency walker and saw that python37.dll was
> > required.
> >  > My PC already has python 3.7.4, but I don't know if that is an
> > issue and
> >  > postgres requires python 3.7.0 strictly.
> >  >
> >  > I also copied the python37.dll file directly in the System32
> > folder, as
> >  > some pages suggested. That actually made it possible to run the
> >  > "* create extension plpython3u* ", but when I try to create a
> simple
> >  > function like:
> >  >
> >  > "*CREATE FUNCTION pymax (a integer, b integer)*
> >  > *  RETURNS integer
> >  > AS $$
> >  >if a > b:
> >  >  return a
> >  >return b
> >  > *
> >  > *$$ LANGUAGE plpython3u*;"
> >  >
> >  > The server crashes and I get this message: "*Connection to the
> > server
> >  > has been lost*."
> >  >
> >  > Some additional information:
> >  > - My PC is running windows server 2019 64 bits (its a vps)
> >
> > How did you install Postgres?
> >
> >  > - The python version I have is 3.7.4. Installed it with Anaconda
> >
> > Unless things have changed since the last time I used Anaconda, it
> > basically creates virtualenvs for it's installs. So when you refer to
> > the Python dll above are you talking about a system installed one or
> > one
> > from within the Anaconda environment?
> >
> >  > - Postgres version is 12.2-1. pgAdmin is 4.18
> >  >
> >  > Please, help me :(
> >  > Regards,
> >  > PS
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Help with plpython3u

[Adrian Klaver]

On 6/9/20 6:17 PM, PEDRO PABLO SEVERIN HONORATO wrote:
Thanks Adrian, this actually helped me. I was looking for the "language 
pack" thing without any success and thanks god that you pointed me in 
the right direction :)


What I don't understand is the following. After installing the language 
pack with stack builder, added a variable named "PYTHONHOME" with its 
path (C:\edb\languagepack\v1\Python-3.7) and then added PYTHONHOME and 
the full path to the PATH variable. After that, I restarted the server 
as requested but nothing happened. Then I added "%" to the beginning and 
the end of PYTHONHOME and C:\edb\languagepack\v1\Python-3.7, restarted 
the server and then it worked. Why is that?


For explanation see here:

https://docs.python.org/3.7/using/windows.html
3.6.1. Excursus: Setting environment variables



Also, I would like to know if it is possible to import specific python 
libraries? For example, I would like to use the talib library, which can 
be downloaded and installed. Or a custom library made by myself. And how 
can I import other python libraries, like scikit learn? Where can I find 
information about this?


Yes you can import libraries/packages. The issue is where you are 
installing them, in the system or the Anaconda environment. I don't work 
with Windows much anymore, so I am not up on how to grab a library from 
within an Anaconda environment. Maybe someone else can help or you might 
ask the Anaconda forum:


https://groups.google.com/a/anaconda.com/forum/#!forum/anaconda



Thank you so much for your help!! :D
PS

El mar., 9 jun. 2020 a las 20:37, Adrian Klaver 
(mailto:adrian.kla...@aklaver.com>>) escribió:


On 6/9/20 5:26 PM, PEDRO PABLO SEVERIN HONORATO wrote:
 > Hi Adrian,
 >
 > I installed Postgres downloading the file
posgresql-12.2-1-windows-x64
 > from the website https://www.postgresql.org/download/windows/.

Did you go through the steps here?:


https://www.enterprisedb.com/edb-docs/d/postgresql/user-guides/language-pack-guide/12/toc.html

 >
 > The python37.dll I copied to the System32 folder was located
 > in C:\Users\developer\Anaconda3.
 > After trying this, I installed python 3.7.0 and copied the
python37.dll
 > that comes with the installation, but got the same result.
 >
 > Thanks,
 > PS
 >
 > El mar., 9 jun. 2020 a las 20:17, Adrian Klaver
 > (mailto:adrian.kla...@aklaver.com>
>>) escribió:
 >
 >     On 6/9/20 5:09 PM, PEDRO PABLO SEVERIN HONORATO wrote:
 >      > Hello!
 >      >
 >      > I'm cracking my head while trying to make python language work
 >     within
 >      > postges. I executed "*create extension plpython3u*" but I get
 >     this error:
 >      >
 >      > "*ERROR: could not load library "C:/Program
 >      > Files/PostgreSQL/12/lib/plpython3.dll": The specified module
 >     could not
 >      > be found. SQL state: 58P01*"
 >      >
 >      > Looking into several stackoverflow pages and asking like
 >     everywhere, I
 >      > also installed dependency walker and saw that python37.dll was
 >     required.
 >      > My PC already has python 3.7.4, but I don't know if that is an
 >     issue and
 >      > postgres requires python 3.7.0 strictly.
 >      >
 >      > I also copied the python37.dll file directly in the System32
 >     folder, as
 >      > some pages suggested. That actually made it possible to
run the
 >      > "* create extension plpython3u* ", but when I try to
create a simple
 >      > function like:
 >      >
 >      > "*CREATE FUNCTION pymax (a integer, b integer)*
 >      > *  RETURNS integer
 >      > AS $$
 >      >    if a > b:
 >      >      return a
 >      >    return b
 >      > *
 >      > *$$ LANGUAGE plpython3u*;"
 >      >
 >      > The server crashes and I get this message: "*Connection to the
 >     server
 >      > has been lost*."
 >      >
 >      > Some additional information:
 >      > - My PC is running windows server 2019 64 bits (its a vps)
 >
 >     How did you install Postgres?
 >
 >      > - The python version I have is 3.7.4. Installed it with
Anaconda
 >
 >     Unless things have changed since the last time I used
Anaconda, it
 >     basically creates virtualenvs for it's installs. So when you
refer to
 >     the Python dll above are you talking about a system installed
one or
 >     one
 >     from within the Anaconda environment?
 >
 >      > - Postgres version is 12.2-1. pgAdmin is 4.18
 >      >
 >      > Please, help me :(
 >      > Regards,
 >      > PS
 >
 >
 >     --
 >     Adrian Klaver
 > adrian.kla...@aklaver.com 

Re: Help with plpython3u

[PEDRO PABLO SEVERIN HONORATO]

Thank you, Adrian. Will read about all this. By the way, the issue
reappeared. For some reason, I had to reinstall Anaconda and had to delete
the "PYTHONHOME" variable because I could not launch conda.
Seems that they both fight for the same variable name?
Is there a way to set it to two different names?

Thanks,
PS


El mar., 9 jun. 2020 a las 22:16, Adrian Klaver ()
escribió:

> On 6/9/20 6:17 PM, PEDRO PABLO SEVERIN HONORATO wrote:
> > Thanks Adrian, this actually helped me. I was looking for the "language
> > pack" thing without any success and thanks god that you pointed me in
> > the right direction :)
> >
> > What I don't understand is the following. After installing the language
> > pack with stack builder, added a variable named "PYTHONHOME" with its
> > path (C:\edb\languagepack\v1\Python-3.7) and then added PYTHONHOME and
> > the full path to the PATH variable. After that, I restarted the server
> > as requested but nothing happened. Then I added "%" to the beginning and
> > the end of PYTHONHOME and C:\edb\languagepack\v1\Python-3.7, restarted
> > the server and then it worked. Why is that?
>
> For explanation see here:
>
> https://docs.python.org/3.7/using/windows.html
> 3.6.1. Excursus: Setting environment variables
>
> >
> > Also, I would like to know if it is possible to import specific python
> > libraries? For example, I would like to use the talib library, which can
> > be downloaded and installed. Or a custom library made by myself. And how
> > can I import other python libraries, like scikit learn? Where can I find
> > information about this?
>
> Yes you can import libraries/packages. The issue is where you are
> installing them, in the system or the Anaconda environment. I don't work
> with Windows much anymore, so I am not up on how to grab a library from
> within an Anaconda environment. Maybe someone else can help or you might
> ask the Anaconda forum:
>
> https://groups.google.com/a/anaconda.com/forum/#!forum/anaconda
>
> >
> > Thank you so much for your help!! :D
> > PS
> >
> > El mar., 9 jun. 2020 a las 20:37, Adrian Klaver
> > (mailto:adrian.kla...@aklaver.com>>)
> escribió:
> >
> > On 6/9/20 5:26 PM, PEDRO PABLO SEVERIN HONORATO wrote:
> >  > Hi Adrian,
> >  >
> >  > I installed Postgres downloading the file
> > posgresql-12.2-1-windows-x64
> >  > from the website https://www.postgresql.org/download/windows/.
> >
> > Did you go through the steps here?:
> >
> >
> https://www.enterprisedb.com/edb-docs/d/postgresql/user-guides/language-pack-guide/12/toc.html
> >
> >  >
> >  > The python37.dll I copied to the System32 folder was located
> >  > in C:\Users\developer\Anaconda3.
> >  > After trying this, I installed python 3.7.0 and copied the
> > python37.dll
> >  > that comes with the installation, but got the same result.
> >  >
> >  > Thanks,
> >  > PS
> >  >
> >  > El mar., 9 jun. 2020 a las 20:17, Adrian Klaver
> >  > (mailto:adrian.kla...@aklaver.com>
> >  > >>) escribió:
> >  >
> >  > On 6/9/20 5:09 PM, PEDRO PABLO SEVERIN HONORATO wrote:
> >  >  > Hello!
> >  >  >
> >  >  > I'm cracking my head while trying to make python language
> work
> >  > within
> >  >  > postges. I executed "*create extension plpython3u*" but I
> get
> >  > this error:
> >  >  >
> >  >  > "*ERROR: could not load library "C:/Program
> >  >  > Files/PostgreSQL/12/lib/plpython3.dll": The specified
> module
> >  > could not
> >  >  > be found. SQL state: 58P01*"
> >  >  >
> >  >  > Looking into several stackoverflow pages and asking like
> >  > everywhere, I
> >  >  > also installed dependency walker and saw that python37.dll
> was
> >  > required.
> >  >  > My PC already has python 3.7.4, but I don't know if that
> is an
> >  > issue and
> >  >  > postgres requires python 3.7.0 strictly.
> >  >  >
> >  >  > I also copied the python37.dll file directly in the
> System32
> >  > folder, as
> >  >  > some pages suggested. That actually made it possible to
> > run the
> >  >  > "* create extension plpython3u* ", but when I try to
> > create a simple
> >  >  > function like:
> >  >  >
> >  >  > "*CREATE FUNCTION pymax (a integer, b integer)*
> >  >  > *  RETURNS integer
> >  >  > AS $$
> >  >  >if a > b:
> >  >  >  return a
> >  >  >return b
> >  >  > *
> >  >  > *$$ LANGUAGE plpython3u*;"
> >  >  >
> >  >  > The server crashes and I get this message: "*Connection to
> the
> >  > server
> >  >  > has been lost*."
> >  >  >
> >  >  > Some additional information:
> >  >  > - My PC is running windows server 20

Re: Planner misestimation for JOIN with VARCHAR

[Sebastian Dressler]

> On 9. Jun 2020, at 21:30, Michael Lewis  wrote:
> 
>> On Tue, Jun 9, 2020 at 12:34 PM Sebastian Dressler  
>> wrote:
>> - Add an index on top of the whole PK
>> - Add indexes onto other columns trying to help the JOIN
>> - Add additional statistics on two related columns
>> 
>> Another idea I had was to make use of generated columns and hash the PKs 
>> together to an BIGINT and solely use this for the JOIN. However, this would 
>> not work when not all columns of the PK are used for the JOIN.
> 
> Can you expand on the additional statistics you created? Why was it on only 
> two columns? Did you include MCVs type of extended stats?

Sure, for the MCVs specifically I tried to select which columns belong together 
to also have value for the end-user when running a query. Basically in 
accordance what the docs suggest, i.e. [1] section 14.2.2.3 last paragraph. 
Given the nature of the data I however think this can be very difficult to do 
without requiring further user input. Likewise, as the others suggested, it did 
not help for this particular case.

Cheers,
Sebastian

[1]: 
https://www.postgresql.org/docs/current/planner-stats.html#PLANNER-STATS-EXTENDED

--

Sebastian Dressler, Solution Architect 
+49 30 994 0496 72 | sebast...@swarm64.com 

Swarm64 AS
Parkveien 41 B | 0258 Oslo | Norway
Registered at Brønnøysundregistrene in Norway under Org.-Number 911 662 787
CEO/Geschäftsführer (Daglig Leder): Thomas Richter; Chairman/Vorsitzender 
(Styrets Leder): Dr. Sverre Munck 

Swarm64 AS Zweigstelle Hive
Ullsteinstr. 120 | 12109 Berlin | Germany
Registered at Amtsgericht Charlottenburg - HRB 154382 B

RE: Postgres server 12.2 crash with process exited abnormally and possibly corrupted shared memory

[Ishan Joshi]

Hi Michael,

Thanks for your response.

Please find answers for your questions
How many rows did these tables have before partitioning? -->  We starts 
test with  0 rows in partition table.
Why did you decide to partition?
  -->  These tables are heave tables with high number of DML operation 
performed on this tables with high number of rows generated every hour.
Do these list partitions allow for plan-time pruning?-->   WE 
have tune application queries to utilize partition pruning. Still we have 2-3 
queries not utilizing partition pruning and we are working on same.
Do they support partition wise joins?  
-->  Most of the queries are querying to single table.  We have change our 
queries that can utilize partition key.
work_mem can be used for each node of the plan and if you are getting parallel 
scans of many tables or indexes where you previously had one, that could be an 
issue. --> some of query are scanning indexes on all the partition.

Current work_mem is set with 9MB.
cpu_tuple_cost = 0.03
seq_page_cost = 0.7
random_page_cost=1
huge_pages=off.


Thanks & Regards,
Ishan Joshi

From: Michael Lewis 
Sent: Wednesday, June 10, 2020 1:23 AM
To: Ishan Joshi 
Cc: pgsql-gene...@postgresql.org
Subject: Re: Postgres server 12.2 crash with process exited abnormally and 
possibly corrupted shared memory

On Tue, Jun 9, 2020 at 8:35 AM Ishan Joshi 
mailto:ishan.jo...@amdocs.com>> wrote:
I have using postgresql server v12.2  on CentOS Linux release 7.3.1611 (Core).

My application is working fine with non partition tables but recently we are 
trying to adopt partition table on few of application tables.
So we have created List partition on 6 table. 2 out of 6 tables have 24 
partitions and 4 out of 6 tables have 500 list partitions. After performing 
partition table, we are trying to run our application it is getting crash as I 
can see the memory utilization is consumed 100% and once it reach to 100%  
Postgres server getting crash with following error


How many rows did these tables have before partitioning? Why did you decide to 
partition? Do these list partitions allow for plan-time pruning? Do they 
support partition wise joins? work_mem can be used for each node of the plan 
and if you are getting parallel scans of many tables or indexes where you 
previously had one, that could be an issue.

2000 for max_connections strikes me as quite high. Consider the use of a 
connection pooler like pgbouncer or pgpool such that Postgres can be run with 
max connections more like 2-5x your number of CPUs, and those connections get 
re-used as needed. There is some fixed memory overhead for each potential 
connection.
This email and the information contained herein is proprietary and confidential 
and subject to the Amdocs Email Terms of Service, which you may review at 
https://www.amdocs.com/about/email-terms-of-service 

Re: Logical replication stuck in catchup state

[Dan shmidt]

Thank you very much for your replies.

Regarding the server logs, I didn't find anything but healthy log when the 
server start which says that it is going to recover from the same point in WAL 
which was last sent.
Regarding bugfixes, I will try to update ASAP - but wouldn't a restart of the 
server release the lock? Is there a way to release the lock manually?

Any other suggestion on how to recover from this state without upgrading?
Is there a way to restart the replication from scratch?


Sent from Outlook


From: Dan shmidt
Sent: Wednesday, June 10, 2020 12:30 AM
To: pgsql-gene...@postgresql.org 
Subject: Logical replication stuck in catchup state

Hi All,

We have a setup in which there are several master nodes replicating to a single 
slave/backup node. We are using Postgres 11.4.
Recently, one of the nodes seems to be stuck and stopped replicating.
I did some basic troubleshooting and couldn't find the root cause for that.

On one hand:
  - the replication slot does seem to be active according to 
pg_replication_slots (Sorry no screenshot)
  - on slave node it seems that last_msg_receipt_time is updating on 
pg_stat_subscription

On the other hand:
 - on the slave node: received_lsn keeps pointing on the same wal segment 
(pg_stat_subscription)
 - redo_lsn - restart_lsn shows ~20GB lag

According to logs on the master it seems that the sender hits a timeout, when 
trying to increase the wal_sender_timeout even to 0 (no timeout) - it doesn't 
have any effect. On the other hand, the  last_msg_receipt_time is updated. How 
is that possible?

Screenshots attached. The stuck subscription/replication slot is the one ending 
with "53db6". On images with more than one row - it's the second one.


Any suggestions on what may be the root cause or how to continue debugging?
Appreciate your help.

Thank you,
Dan.

Re: Help with plpython3u

[Laurenz Albe]

On Tue, 2020-06-09 at 20:26 -0400, PEDRO PABLO SEVERIN HONORATO wrote:
> I installed Postgres downloading the file posgresql-12.2-1-windows-x64 from 
> the website https://www.postgresql.org/download/windows/. 
> 
> The python37.dll I copied to the System32 folder was located in 
> C:\Users\developer\Anaconda3.
> After trying this, I installed python 3.7.0 and copied the python37.dll that 
> comes with the installation, but got the same result.

Do not copy libraries around.  That's a road to hell.
Instead, add them to the PATH.
The library might depend on something else at the original location.

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