Re: Multitenent architecture

[Achilleas Mantzios]

On 4/6/20 6:52 μ.μ., Vasu Madhineni wrote:

Hi All,

We are planning a POC on multitenant architecture in Postgres, Could you please 
help us with steps for multitenant using schema for each application model.

Hello,
you may benefit from this blog post :
https://severalnines.com/database-blog/multitenancy-options-postgresql


Thank you so much all.

Regards,
Vasu



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Something else about Redo Logs disappearing

[Magnus Hagander]

On Wed, Jun 10, 2020 at 1:53 AM Peter  wrote:

> 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.
>

This one unfortunately rings out of somebody who doesn't know how to back
up postgres, at least in the past 10-15 years.

They are using an API that has been deprecated for years - in what's
announced as a brand new product. They are advocating local archiving,
which basically guarantees dataloss in the event of a disaster.

That's from a 3 minute look, but that's definitely enough to suggest this
is  not something I'd consider using.


! > 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.
>

Yes, those docs are unfortunately "known bad" and should definitely be
improved on. it does very clearly state that the example is just an
example. But it doesn't clearly state *why* it shouldn't be used.



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.
>

I'd say it's the exact opposite. backups are done via pg_basebackup or
manual basebackups. Archive logs are for point in time recovery. pg_dump
can be used as a secondary "backup to the backups" option, but it is most
interesting for things that are not backups (such as inspecting data, or
provisioning partial test systems).

Different for different scenarios of course, but that would be the base
scenario. And pg_dump are definitely as far from good backups are you can
get while still having something that can be called approximately backups.
It might be enough for small databases, but even in those cases
pg_basebackup (without archive logging) is easier...


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. There is no "somehow", it's 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.
>

It is not impossible. It is harder if you limit your available tools yes,
but it also *works*.


So yes, this is really a LOT of work. But the point is: this all is
> not really necessary, because currently the stuff works fine in the
> old way.
>

It does not, no. It works in the simple cases, but it has multiple failure
scenarios that *cannot* be fixed without changing those fundamentals.

But you can always go for the actual old way -- just stop postgres in the
pre-job and start it again in the post-job. That's by far the easiest. And
that *does* work and is fully supported.


! > 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
> ! misse

Re: Something else about Redo Logs disappearing

[Peter]

On Tue, Jun 09, 2020 at 03:42:48PM -0400, Stephen Frost wrote:
! > 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.

What repo?? I seem to have missed that at first glance.

Are You indeed suggesting that one should have their data within
the database, where it is worked with, and then use Your tool
to copy it to some "repo" disk playground whatever area, and then
use their regular backup system to COPY IT AGAIN into their
backup/archiving system? Are You kiddin'?

Are You indeed suggesting that people should buy the amount
of disks that they use for their database AGAIN in order for Your
software to copy the stuff around?

Is this becoming a madhouse, or are You going to refund them that?

Let me tell You something: the people I used to work for, sometimes
had a problem. They had some amount of data that was created during
the day, and they had the night to write that data away to backup.
That would usually mean, four or eight of the big tapes, streaming in
parallel, fibers saturated, all night thru. And the problem usually was
that they would need a longer night. At least the math had to be done
properly.

Maybe You never encountered these, but there are surroundings where
there is no spare room for nonsense. Maybe that'S why these people
preferred to use oracle.


cheerio,
PMc

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:
> ! > 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.
> 
> What repo?? I seem to have missed that at first glance.

Yes, pgbackrest has a repo, like most other tools (though they call them
different things... pg_basebackup has one though it's not really
formal).

> Are You indeed suggesting that one should have their data within
> the database, where it is worked with, and then use Your tool
> to copy it to some "repo" disk playground whatever area, and then
> use their regular backup system to COPY IT AGAIN into their
> backup/archiving system? Are You kiddin'?

No, I'm not kidding and yes, that's what I'm suggesting.  You need a
consistent backup of your database that includes all the needed WAL to
perform a restore.

This is only one option though, there are others- you can also use
pgbackrest to push your backups to s3 (or any s3-compatible data storage
system, which includes some backup systems), and we'll be adding support
for Azure very shortly, and have plans to add GCS too in the future,
along with others probably.

> Is this becoming a madhouse, or are You going to refund them that?

I concur that this is becoming a madhouse, and is pushing past the limit
for what I'm willing to deal with when trying to assist someone.

> Let me tell You something: the people I used to work for, sometimes
> had a problem. They had some amount of data that was created during
> the day, and they had the night to write that data away to backup.
> That would usually mean, four or eight of the big tapes, streaming in
> parallel, fibers saturated, all night thru. And the problem usually was
> that they would need a longer night. At least the math had to be done
> properly.

Indeed, parallel backup is important, which is why pgbackrest supports
it, along with compression and encryption, all in-stream between the
database server and the repo, along with calculating a SHA to be stored
of every single file seen, allowing you to validate that the files
haven't changed since the backup was done when restoring.

> Maybe You never encountered these, but there are surroundings where
> there is no spare room for nonsense. Maybe that'S why these people
> preferred to use oracle.

I've both dealt with keeping tape drives fully loaded to avoid breaking
the tape by studdering it (and writing dedicated C code to deal with
exactly that), and dealt with backing up and restoring Oracle, including
with various "enterprise" backup technologies (with varying levels of
success...).  None of what is being brought up here is new, novel, or
even particularly interesting.

Thanks,

Stephen


signature.asc
Description: PGP signature

[HELP] Reset postgres server

[Praveen Kumar K S]

Hello,

I have a master and slave setup running and sometimes we have to do a
complete refresh on dev environments. How can I reset postgres server so
that it wipes out everything including WAL ? So that I configure
master/slave streaming replication again from scratch ? TIA.

-- 


*Regards,*


*K S Praveen Kumar*

Re: [HELP] Reset postgres server

[Praveen Kumar K S]

Providing more details.
OS is Ubuntu 16.04 and postgres is installed from APT.
logical and physical replication slots configured on master.
No WAL archiving configured.
Streaming replication is enabled on master.
Slave is hot-standby

On Wed, Jun 10, 2020 at 6:03 PM Praveen Kumar K S 
wrote:

> Hello,
>
> I have a master and slave setup running and sometimes we have to do a
> complete refresh on dev environments. How can I reset postgres server so
> that it wipes out everything including WAL ? So that I configure
> master/slave streaming replication again from scratch ? TIA.
>
> --
>
>
> *Regards,*
>
>
> *K S Praveen Kumar*
>


-- 


*Regards,*


*K S Praveen Kumar*

javascript app running in crhome with connection to postgreSQL

[stan]

OK, I have to admit this is a case of my being influenced by things going on
in the news, which I am not normally influenced by.

All the news about the touch screens on the crew dragon capsule using a
JavaScript app running in Chrome, have me thinking about doing some
testing for a project I am working on that I am not happy with the
direction we are currently going in for the fronted. 

Can anyone point me to some documentation as to how to start building a
test application for this?

My first question is whether this would have a persistent connection to the
backend database, or would it work like a CGI script, and have to make a
connection to the backend every time the user takes an action?

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: javascript app running in crhome with connection to postgreSQL

[Richard Bernstein]

Hi Stan. I have been dealing with this lately. I have a Chrome Extension
that does work gathering the info to save in the postgresql, such as screen
scraping a specific tab. I am using AWS RDS hosted Postgresql, btw. I am
not sure however, due to security  reasons, that you can open a connection
*directly* from the Chrome extension to the database. If you find it is
possible, please let me know. The security rules on Extension  are very
strict. The Extension is written in javascript,.  The extension can
communicate to my web app that is running in a tab (via the Chrome API). It
is this web app that will maintain an open connection to the postgres
server (which is remote). My webapp is written in php and from my
understanding each copy (it is multiuser) maintains its own connection to
postgres. I will implement RLS (row level security) just to be sure!  Will
this all work? I am not totally done yet and am stuck right now on getting
the php App (running on Docker) to open a connection with the RDS
Postgresql.

Nice BF quote. If you haven't read it, read BF's autobiography. It reads
like it was written just recently.

On Wed, Jun 10, 2020 at 9:23 AM stan  wrote:

>
> OK, I have to admit this is a case of my being influenced by things going
> on
> in the news, which I am not normally influenced by.
>
> All the news about the touch screens on the crew dragon capsule using a
> JavaScript app running in Chrome, have me thinking about doing some
> testing for a project I am working on that I am not happy with the
> direction we are currently going in for the fronted.
>
> Can anyone point me to some documentation as to how to start building a
> test application for this?
>
> My first question is whether this would have a persistent connection to the
> backend database, or would it work like a CGI script, and have to make a
> connection to the backend every time the user takes an action?
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>
>
>

Re: Help with plpython3u

[Adrian Klaver]

On 6/9/20 10:38 PM, PEDRO PABLO SEVERIN HONORATO wrote:
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?


See below for more information:

https://docs.anaconda.com/anaconda/user-guide/faq/#installing-anaconda


Thanks,
PS





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

Re: [HELP] Reset postgres server

[Adrian Klaver]

On 6/10/20 5:33 AM, Praveen Kumar K S wrote:

Hello,

I have a master and slave setup running and sometimes we have to do a 
complete refresh on dev environments. How can I reset postgres server so 
that it wipes out everything including WAL ? So that I configure 
master/slave streaming replication again from scratch ? TIA.


Are both servers part of the dev setup?

If so would deleting the content of $PGDATA and then doing an initdb on 
it work?
To make above easier I would keep a copies of the *.conf files where you 
could copy them back into $PGDATA after the initdb.




--
*Regards,

*
*K S Praveen Kumar

*



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

Re: Help with plpython3u

[PEDRO PABLO SEVERIN HONORATO]

Thank you Adrian, will see the link you sent me.

Thank you, Laurenz. I noticed that copying the DLL is not a good idea.
How can I set a python path for postgres, so that I tell postgres "hey, if
you are looking for python see this path" and don't lose conda? Because
after following all Adrian's instructions and  after creating the
variable"*PYTHONHOME
= C:\edb\languagepack\v1\Python-3.7*" and adding *%PYTHONHOME%* to the *PATH,
*I get this message when I open Anaconda Prompt:
*"File "C:\Users\developer\Anaconda3\Scripts\conda-script.py", line 11, in
 from conda.cli import main ModuleNotFoundError: No module named
'conda'"*

Then, I removed the variable name PYTHONHOME and tried to execute again
Anaconda Prompt without any problem. So I think that postgres and conda are
like "fighting" for the PYTHONHOME variable name? Is this correct?

El mié., 10 jun. 2020 a las 10:08, Adrian Klaver ()
escribió:

> On 6/9/20 10:38 PM, PEDRO PABLO SEVERIN HONORATO wrote:
> > 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?
>
> See below for more information:
>
> https://docs.anaconda.com/anaconda/user-guide/faq/#installing-anaconda
> >
> > Thanks,
> > PS
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Help with plpython3u

[Adrian Klaver]

On 6/10/20 7:39 AM, PEDRO PABLO SEVERIN HONORATO wrote:

Thank you Adrian, will see the link you sent me.

Thank you, Laurenz. I noticed that copying the DLL is not a good idea.
How can I set a python path for postgres, so that I tell postgres "hey, 
if you are looking for python see this path" and don't lose conda? 
Because after following all Adrian's instructions and  after creating 
the variable"*PYTHONHOME = C:\edb\languagepack\v1\Python-3.7*" and 


Just to be clear those where instructions from here, correct?:

https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/language-pack-guide/12/EDB_Postgres_Language_Pack_Guide.1.08.html#

adding *%PYTHONHOME%* to the *PATH, *I get this message when I open 
Anaconda Prompt:
*"File "C:\Users\developer\Anaconda3\Scripts\conda-script.py", line 11, 
in  from conda.cli import main ModuleNotFoundError: No module 
named 'conda'"*


So when you did the Anaconda install:

https://docs.anaconda.com/anaconda/install/windows/

What did you check for the Advanced options in Step 8?


*
*
Then, I removed the variable name PYTHONHOME and tried to execute again 
Anaconda Prompt without any problem. So I think that postgres and conda 
are like "fighting" for the PYTHONHOME variable name? Is this correct?


El mié., 10 jun. 2020 a las 10:08, Adrian Klaver 
(mailto:adrian.kla...@aklaver.com>>) escribió:


On 6/9/20 10:38 PM, PEDRO PABLO SEVERIN HONORATO wrote:
 > 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?

See below for more information:

https://docs.anaconda.com/anaconda/user-guide/faq/#installing-anaconda
 >
 > Thanks,
 > PS
 >
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Parallel safety of contrib extensions

[Winfield, Steven]

Hi all,

There was a thread about this back in 2016[1], but I've just been bitten by it 
and wondered if any (more) extensions, particularly btree_gist, will have their 
operators/functions verified and marked as parallel-safe?

In our case, we're interested in the float8 <-> float8 operator. Although this 
is defined in btree_gist, we also have plain btree indexes that use this 
operator in functional columns. We're currently running v11.7.

An example:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE distance_test AS
SELECT id, mod(id, 10) as m10, mod(id, 17) as m17, random() as r
FROM generate_series(1, 100) as ids(id);

ANALYZE distance_test;

CREATE INDEX idx_distance_test ON distance_test USING btree (m10, m17, (r <-> 
0.5));

SET force_parallel_mode = on;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;

EXPLAIN ANALYZE
(
  SELECT DISTINCT ON (m10, m17) m10, m17
  FROM distance_test
  WHERE m10 = 5
  ORDER BY m10, m17
)
UNION ALL
(
  SELECT DISTINCT ON (m10, m17) m10, m17
  FROM distance_test
  WHERE m10 = 3
  ORDER BY m10, m17
)

This gives a nice parallel plan - the two subqueries are farmed out to workers, 
each doing a Unique of an Index Only Scan, and the results are 
Parallel-Appended.

However, if the two "ORDER BY" clauses have the functional column "r <-> 0.5" 
added then parallelism is no longer allowed - the plan becomes a plain Append 
of two Subquery Scans.

As an aside, the values of "r <-> 0.5" have been precomputed - which is why an 
Index Only Scan can take place - so the supposed parallel-unsafe operator isn't 
even being invoked here!

But, either way, the loss of parallelism in my real-world case wasn't easy to 
track down, so it would be great if some more of the contrib-but-core-really 
extensions, such as btree_gist, could have their functions and operators marked 
parallel safe.

And, yes, if I'd used "abs(r - 0.5)" here then parallelism would not have been 
lost, but we do use gist indexes elsewhere and we don't want to have to switch 
between this and "<->" depending on which index we think a particular query 
needs, or worry about subtle differences between them when handling - say - 
NaNs.

I can also force parallelism to return if I manually mark the "float8_dist" 
function as parallel-safe:
UPDATE pg_proc SET proparallel = 's' WHERE oid = 'float8_dist'::regproc

Looking at the source code[2] I can't see why this would cause issues, but I'm 
not an expert - any comments on why this might be an incredibly bad idea would 
be most welcome (e.g. will the setting survive a major upgrade of the db, and 
an upgrade of the btree_gist extension? Is it in fact subtly NOT parallel safe?)


Best wishes,
Steven.


[1] https://www.postgresql.org/message-id/flat/573E3509.9040309%40proxel.se
[2] 
https://github.com/postgres/postgres/blob/master/contrib/btree_gist/btree_float8.c#L102


(Apologies if my company's mail servers append an annoying disclaimer here...)

** Cantab Capital Partners LLP is now named GAM Systematic LLP. Please note 
that our email addresses have changed from @cantabcapital.com to @gam.com.**

This email was sent by and on behalf of GAM Investments. GAM Investments is the 
corporate brand for GAM Holding AG and its direct and indirect subsidiaries. 
These companies may be referred to as 'GAM' or 'GAM Investments'. In the United 
Kingdom, the business of GAM Investments is conducted by GAM (U.K.) Limited 
(No. 01664573) or one or more entities under the control of GAM (U.K.) Limited, 
including the following entities authorised and regulated by the Financial 
Conduct Authority: GAM International Management Limited (No. 01802911), GAM 
London Limited (No. 00874802), GAM Sterling Management Limited (No. 01750352), 
GAM Unit Trust Management Company Limited (No. 2873560) and GAM Systematic LLP 
(No. OC317557). GAM (U.K.) Limited and its regulated entities are registered in 
England and Wales. The registered office and principal place of business of GAM 
(U.K.) Limited and its regulated entities is at 8 Finsbury Circus, London, 
England, EC2M 7GB. The registered office of GAM Systematic LLP is at City 
House, Hills Road, Cambridge, CB2 1RE. This email, and any attachments, is 
confidential and may be privileged or otherwise protected from disclosure. It 
is intended solely for the stated addressee(s) and access to it by any other 
person is unauthorised. If you are not the intended recipient, you must not 
disclose, copy, circulate or in any other way use or rely on the information 
contained herein. If you have received this email in error, please inform us 
immediately and delete all copies of it. See - 
https://www.gam.com/en/legal/email-disclosures-eu/ for further information on 
confidentiality, the risks of non-secure electronic communication, and certain 
disclosures which we are required to make in accordance with applicable 
legislation and regulations. If you cannot access this link, please notify us 
by reply message and we will send the

Re: Help with plpython3u

[PEDRO PABLO SEVERIN HONORATO]

Just to be clear those where instructions from here, correct?:

https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/language-pack-guide/12/EDB_Postgres_Language_Pack_Guide.1.08.html#

Yes. When I did that I could execute python functions and create the
extension in postgres without problem, but later I realized that doing this
will create the other problem with anaconda.

So when you did the Anaconda install:

https://docs.anaconda.com/anaconda/install/windows/

What did you check for the Advanced options in Step 8?

I left the first one unchecked and the second one checked, which is the
default settings.

El mié., 10 jun. 2020 a las 10:50, Adrian Klaver ()
escribió:

> On 6/10/20 7:39 AM, PEDRO PABLO SEVERIN HONORATO wrote:
> > Thank you Adrian, will see the link you sent me.
> >
> > Thank you, Laurenz. I noticed that copying the DLL is not a good idea.
> > How can I set a python path for postgres, so that I tell postgres "hey,
> > if you are looking for python see this path" and don't lose conda?
> > Because after following all Adrian's instructions and  after creating
> > the variable"*PYTHONHOME = C:\edb\languagepack\v1\Python-3.7*" and
>
> Just to be clear those where instructions from here, correct?:
>
>
> https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/language-pack-guide/12/EDB_Postgres_Language_Pack_Guide.1.08.html#
>
> > adding *%PYTHONHOME%* to the *PATH, *I get this message when I open
> > Anaconda Prompt:
> > *"File "C:\Users\developer\Anaconda3\Scripts\conda-script.py", line 11,
> > in  from conda.cli import main ModuleNotFoundError: No module
> > named 'conda'"*
>
> So when you did the Anaconda install:
>
> https://docs.anaconda.com/anaconda/install/windows/
>
> What did you check for the Advanced options in Step 8?
>
> > *
> > *
> > Then, I removed the variable name PYTHONHOME and tried to execute again
> > Anaconda Prompt without any problem. So I think that postgres and conda
> > are like "fighting" for the PYTHONHOME variable name? Is this correct?
> >
> > El mié., 10 jun. 2020 a las 10:08, Adrian Klaver
> > (mailto:adrian.kla...@aklaver.com>>)
> escribió:
> >
> > On 6/9/20 10:38 PM, PEDRO PABLO SEVERIN HONORATO wrote:
> >  > 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?
> >
> > See below for more information:
> >
> >
> https://docs.anaconda.com/anaconda/user-guide/faq/#installing-anaconda
> >  >
> >  > Thanks,
> >  > PS
> >  >
> >  >
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Help with plpython3u

[Adrian Klaver]

On 6/10/20 8:25 AM, PEDRO PABLO SEVERIN HONORATO wrote:

Just to be clear those where instructions from here, correct?:

https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/language-pack-guide/12/EDB_Postgres_Language_Pack_Guide.1.08.html#

Yes. When I did that I could execute python functions and create the 
extension in postgres without problem, but later I realized that doing 
this will create the other problem with anaconda.


So when you did the Anaconda install:

https://docs.anaconda.com/anaconda/install/windows/

What did you check for the Advanced options in Step 8?

I left the first one unchecked and the second one checked, which is the 
default settings.




Alright. Before we go any further it would be best to determine what 
your needs are. So:


1) Did you install Anaconda just to get Python or for the science data 
set of libraries it provides?


2) Do you need the system Python you installed for anything that 
Anaconda does not provide?


3) What are you planning to do with plpython3u?


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

Re: [HELP] Reset postgres server

[Praveen Kumar K S]

Hello,

Yes, both are part of dev. Should I clean the data directory on both
servers or only on master ?

On Wed, Jun 10, 2020 at 7:50 PM Adrian Klaver 
wrote:

> On 6/10/20 5:33 AM, Praveen Kumar K S wrote:
> > Hello,
> >
> > I have a master and slave setup running and sometimes we have to do a
> > complete refresh on dev environments. How can I reset postgres server so
> > that it wipes out everything including WAL ? So that I configure
> > master/slave streaming replication again from scratch ? TIA.
>
> Are both servers part of the dev setup?
>
> If so would deleting the content of $PGDATA and then doing an initdb on
> it work?
> To make above easier I would keep a copies of the *.conf files where you
> could copy them back into $PGDATA after the initdb.
>
> >
> > --
> > *Regards,
> >
> > *
> > *K S Praveen Kumar
> >
> > *
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 


*Regards,*


*K S Praveen Kumar*

Re: [HELP] Reset postgres server

[Adrian Klaver]

On 6/10/20 9:31 AM, Praveen Kumar K S wrote:

Hello,

Yes, both are part of dev. Should I clean the data directory on both 
servers or only on master ?


Both otherwise the standby will be working with out of date data and 
will not sync.




On Wed, Jun 10, 2020 at 7:50 PM Adrian Klaver > wrote:


On 6/10/20 5:33 AM, Praveen Kumar K S wrote:
 > Hello,
 >
 > I have a master and slave setup running and sometimes we have to
do a
 > complete refresh on dev environments. How can I reset postgres
server so
 > that it wipes out everything including WAL ? So that I configure
 > master/slave streaming replication again from scratch ? TIA.

Are both servers part of the dev setup?

If so would deleting the content of $PGDATA and then doing an initdb on
it work?
To make above easier I would keep a copies of the *.conf files where
you
could copy them back into $PGDATA after the initdb.

 >
 > --
 > *Regards,
 >
 > *
 > *K S Praveen Kumar
 >
 > *


-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
*Regards,

*
*K S Praveen Kumar

*



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

Re: Parallel safety of contrib extensions

[Tom Lane]

"Winfield, Steven"  writes:
> There was a thread about this back in 2016[1], but I've just been bitten by 
> it and wondered if any (more) extensions, particularly btree_gist, will have 
> their operators/functions verified and marked as parallel-safe?

Whenever somebody does the legwork and sends a patch ...

regards, tom lane

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

[Michael Lewis]

On Wed, Jun 10, 2020 at 12:05 AM Ishan Joshi  wrote:

> How many rows did these tables have before partitioning? à  We starts
> test with  0 rows in partition table.
>

Partitions are far from free and pruning is great but not guaranteed. How
many total rows do you currently have or foresee having in the biggest
table (all partitions combined, or without partitioning) within the next
1-3 years? 1 million? 100 million? 2 billion?  You may have partitioned
before it was prudent to do so is the point. Just because something can be
done, doesn't mean it should be. What sort of key are you partitioning on?

Also, what do you mean about tests? There are some assumptions made about
empty tables since stats may indicate they are empty when they are not. If
your tests involve many empty tables, then it may give rather different
performance than real life where there are few or no empty partitions.

Re: Help with plpython3u

[PEDRO PABLO SEVERIN HONORATO]

Alright. Before we go any further it would be best to determine what
your needs are. So:

1) Did you install Anaconda just to get Python or for the science data
set of libraries it provides?

A: For the data science set of libraries. Also, it helps to keep libraries
updated.

2) Do you need the system Python you installed for anything that
Anaconda does not provide?

A: I don't know if I understood well this question, but I think the answer
is no. Anaconda does provide almost all the libraries I need. I just
installed a couple more (like talib, tensorflow, keras, psycopg2, etc) and
created some libraries of my own.

3) What are you planning to do with plpython3u?

A: I'm planning to create some functions using python language to process
data that is stored in real time in the database and then use them as
triggers. I would like to be able to import some libraries that I have in
python to do it. Some of the functions are fourier and wavelet transforms.
If I can't import the library, then I'll have to code the full function,
but it's easier (for me) to code them in python language rather than sql
language.

El mié., 10 jun. 2020 a las 12:17, Adrian Klaver ()
escribió:

> On 6/10/20 8:25 AM, PEDRO PABLO SEVERIN HONORATO wrote:
> > Just to be clear those where instructions from here, correct?:
> >
> >
> https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/language-pack-guide/12/EDB_Postgres_Language_Pack_Guide.1.08.html#
> >
> > Yes. When I did that I could execute python functions and create the
> > extension in postgres without problem, but later I realized that doing
> > this will create the other problem with anaconda.
> >
> > So when you did the Anaconda install:
> >
> > https://docs.anaconda.com/anaconda/install/windows/
> >
> > What did you check for the Advanced options in Step 8?
> >
> > I left the first one unchecked and the second one checked, which is the
> > default settings.
> >
>
> Alright. Before we go any further it would be best to determine what
> your needs are. So:
>
> 1) Did you install Anaconda just to get Python or for the science data
> set of libraries it provides?
>
> 2) Do you need the system Python you installed for anything that
> Anaconda does not provide?
>
> 3) What are you planning to do with plpython3u?
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

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

[Ishan Joshi]

Hi Michael,

We have table having rows 2.5 millions records inserted and updated in each 
hour and another table having about 1 million records in an hour. WE have 
dedicated column added in table that have list of 1-500 and this column is used 
as partition key.
Idea behind the 500 partition is to store 1 year data in the table with 
partition key value change at every 20hrs.
As the data is huge in these tables we are approaching to partition and the 
list partition for performing  the maintenance on these tables.

This is not a test as we have partition on these tables with oracle but as we 
migrate to postgres, we are enabling the feature in postgres as well.

As we want to see the impact from beginning 0 size and understanding the 
details for next 72 hrs under heavy load.

As I just executed the same environment with 100 partition on these tables, Run 
was running for 12 hrs with constant 70% RAM utilization and 50% cpu 
utilization.

So I am suspecting the number of partition is the issue behind the memory 
utilization.

Thanks & Regards,
Ishan Joshi

From: Michael Lewis 
Sent: Wednesday, June 10, 2020 10:28 PM
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 Wed, Jun 10, 2020 at 12:05 AM Ishan Joshi 
mailto:ishan.jo...@amdocs.com>> wrote:
How many rows did these tables have before partitioning? -->  We starts 
test with  0 rows in partition table.

Partitions are far from free and pruning is great but not guaranteed. How many 
total rows do you currently have or foresee having in the biggest table (all 
partitions combined, or without partitioning) within the next 1-3 years? 1 
million? 100 million? 2 billion?  You may have partitioned before it was 
prudent to do so is the point. Just because something can be done, doesn't mean 
it should be. What sort of key are you partitioning on?

Also, what do you mean about tests? There are some assumptions made about empty 
tables since stats may indicate they are empty when they are not. If your tests 
involve many empty tables, then it may give rather different performance than 
real life where there are few or no empty partitions.


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 with plpython3u

[Adrian Klaver]

On 6/10/20 9:57 AM, PEDRO PABLO SEVERIN HONORATO wrote:

Alright. Before we go any further it would be best to determine what
your needs are. So:

1) Did you install Anaconda just to get Python or for the science data
set of libraries it provides?

A: For the data science set of libraries. Also, it helps to keep 
libraries updated.


2) Do you need the system Python you installed for anything that
Anaconda does not provide?

A: I don't know if I understood well this question, but I think the 
answer is no. Anaconda does provide almost all the libraries I need. I 
just installed a couple more (like talib, tensorflow, keras, psycopg2, 
etc) and created some libraries of my own.


Those libraries are available from Anaconda:

https://docs.anaconda.com/anaconda/packages/py3.7_win-64/

or from AnacondaCloud:

https://anaconda.org/

I just think it would make your life easier to reduce the number of 
Python installs you have. If I'm keeping track correctly you now have:


1) Anaconda Python

2) OS system Python

3) EDB Python.


What happens if you try to create a plpythonu3u function in the database?




3) What are you planning to do with plpython3u?

A: I'm planning to create some functions using python language to 
process data that is stored in real time in the database and then use 
them as triggers. I would like to be able to import some libraries that 
I have in python to do it. Some of the functions are fourier and wavelet 
transforms. If I can't import the library, then I'll have to code the 
full function, but it's easier (for me) to code them in python language 
rather than sql language.


El mié., 10 jun. 2020 a las 12:17, Adrian Klaver 
(mailto:adrian.kla...@aklaver.com>>) escribió:


On 6/10/20 8:25 AM, PEDRO PABLO SEVERIN HONORATO wrote:
 > Just to be clear those where instructions from here, correct?:
 >
 >

https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/language-pack-guide/12/EDB_Postgres_Language_Pack_Guide.1.08.html#
 >
 > Yes. When I did that I could execute python functions and create the
 > extension in postgres without problem, but later I realized that
doing
 > this will create the other problem with anaconda.
 >
 > So when you did the Anaconda install:
 >
 > https://docs.anaconda.com/anaconda/install/windows/
 >
 > What did you check for the Advanced options in Step 8?
 >
 > I left the first one unchecked and the second one checked, which
is the
 > default settings.
 >

Alright. Before we go any further it would be best to determine what
your needs are. So:

1) Did you install Anaconda just to get Python or for the science data
set of libraries it provides?

2) Do you need the system Python you installed for anything that
Anaconda does not provide?

3) What are you planning to do with plpython3u?


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Help with plpython3u

[PEDRO PABLO SEVERIN HONORATO]

I'm sorry, when I try to create a plpython3u function, the database tells
me:

"

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

If I try to execute the function, then the database crashes.

El mié., 10 jun. 2020 a las 13:36, PEDRO PABLO SEVERIN HONORATO (<
ppseve...@miuandes.cl>) escribió:

> I just think it would make your life easier to reduce the number of
> Python installs you have. If I'm keeping track correctly you now have:
>
> 1) Anaconda Python
>
> 2) OS system Python
>
> 3) EDB Python.
>
> The OS is windows, and afaik python is not included in windows, so it
> would be just Anaconda python and EDB python.
>
> What happens if you try to create a plpythonu3u function in the database?
>
> The database crashes and tells me "*Connection to the server has been
> lost.*"
>
> El mié., 10 jun. 2020 a las 13:12, Adrian Klaver (<
> adrian.kla...@aklaver.com>) escribió:
>
>> On 6/10/20 9:57 AM, PEDRO PABLO SEVERIN HONORATO wrote:
>> > Alright. Before we go any further it would be best to determine what
>> > your needs are. So:
>> >
>> > 1) Did you install Anaconda just to get Python or for the science data
>> > set of libraries it provides?
>> >
>> > A: For the data science set of libraries. Also, it helps to keep
>> > libraries updated.
>> >
>> > 2) Do you need the system Python you installed for anything that
>> > Anaconda does not provide?
>> >
>> > A: I don't know if I understood well this question, but I think the
>> > answer is no. Anaconda does provide almost all the libraries I need. I
>> > just installed a couple more (like talib, tensorflow, keras, psycopg2,
>> > etc) and created some libraries of my own.
>>
>> Those libraries are available from Anaconda:
>>
>> https://docs.anaconda.com/anaconda/packages/py3.7_win-64/
>>
>> or from AnacondaCloud:
>>
>> https://anaconda.org/
>>
>> I just think it would make your life easier to reduce the number of
>> Python installs you have. If I'm keeping track correctly you now have:
>>
>> 1) Anaconda Python
>>
>> 2) OS system Python
>>
>> 3) EDB Python.
>>
>>
>> What happens if you try to create a plpythonu3u function in the database?
>>
>>
>> >
>> > 3) What are you planning to do with plpython3u?
>> >
>> > A: I'm planning to create some functions using python language to
>> > process data that is stored in real time in the database and then use
>> > them as triggers. I would like to be able to import some libraries that
>> > I have in python to do it. Some of the functions are fourier and
>> wavelet
>> > transforms. If I can't import the library, then I'll have to code the
>> > full function, but it's easier (for me) to code them in python language
>> > rather than sql language.
>> >
>> > El mié., 10 jun. 2020 a las 12:17, Adrian Klaver
>> > (mailto:adrian.kla...@aklaver.com>>)
>> escribió:
>> >
>> > On 6/10/20 8:25 AM, PEDRO PABLO SEVERIN HONORATO wrote:
>> >  > Just to be clear those where instructions from here, correct?:
>> >  >
>> >  >
>> >
>> https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/language-pack-guide/12/EDB_Postgres_Language_Pack_Guide.1.08.html#
>> >  >
>> >  > Yes. When I did that I could execute python functions and create
>> the
>> >  > extension in postgres without problem, but later I realized that
>> > doing
>> >  > this will create the other problem with anaconda.
>> >  >
>> >  > So when you did the Anaconda install:
>> >  >
>> >  > https://docs.anaconda.com/anaconda/install/windows/
>> >  >
>> >  > What did you check for the Advanced options in Step 8?
>> >  >
>> >  > I left the first one unchecked and the second one checked, which
>> > is the
>> >  > default settings.
>> >  >
>> >
>> > Alright. Before we go any further it would be best to determine what
>> > your needs are. So:
>> >
>> > 1) Did you install Anaconda just to get Python or for the science
>> data
>> > set of libraries it provides?
>> >
>> > 2) Do you need the system Python you installed for anything that
>> > Anaconda does not provide?
>> >
>> > 3) What are you planning to do with plpython3u?
>> >
>> >
>> > --
>> > Adrian Klaver
>> > adrian.kla...@aklaver.com 
>> >
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>

Re: Help with plpython3u

[Adrian Klaver]

On 6/10/20 10:39 AM, PEDRO PABLO SEVERIN HONORATO wrote:
I'm sorry, when I try to create a plpython3u function, the database 
tells me:


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

If I try to execute the function, then the database crashes.


Alright I'm going to have to review where we are at. That will not 
happen for a bit, so hang on.




El mié., 10 jun. 2020 a las 13:36, PEDRO PABLO SEVERIN HONORATO 
(mailto:ppseve...@miuandes.cl>>) escribió:


I just think it would make your life easier to reduce the number of
Python installs you have. If I'm keeping track correctly you now have:

1) Anaconda Python

2) OS system Python

3) EDB Python.

The OS is windows, and afaik python is not included in windows, so
it would be just Anaconda python and EDB python.

What happens if you try to create a plpythonu3u function in the
database?

The database crashes and tells me "*Connection to the server has
been lost.*"

El mié., 10 jun. 2020 a las 13:12, Adrian Klaver
(mailto:adrian.kla...@aklaver.com>>)
escribió:

On 6/10/20 9:57 AM, PEDRO PABLO SEVERIN HONORATO wrote:
 > Alright. Before we go any further it would be best to
determine what
 > your needs are. So:
 >
 > 1) Did you install Anaconda just to get Python or for the
science data
 > set of libraries it provides?
 >
 > A: For the data science set of libraries. Also, it helps to keep
 > libraries updated.
 >
 > 2) Do you need the system Python you installed for anything that
 > Anaconda does not provide?
 >
 > A: I don't know if I understood well this question, but I
think the
 > answer is no. Anaconda does provide almost all the libraries
I need. I
 > just installed a couple more (like talib, tensorflow, keras,
psycopg2,
 > etc) and created some libraries of my own.

Those libraries are available from Anaconda:

https://docs.anaconda.com/anaconda/packages/py3.7_win-64/

or from AnacondaCloud:

https://anaconda.org/

I just think it would make your life easier to reduce the number of
Python installs you have. If I'm keeping track correctly you now
have:

1) Anaconda Python

2) OS system Python

3) EDB Python.


What happens if you try to create a plpythonu3u function in the
database?


 >
 > 3) What are you planning to do with plpython3u?
 >
 > A: I'm planning to create some functions using python
language to
 > process data that is stored in real time in the database and
then use
 > them as triggers. I would like to be able to import some
libraries that
 > I have in python to do it. Some of the functions are fourier
and wavelet
 > transforms. If I can't import the library, then I'll have to
code the
 > full function, but it's easier (for me) to code them in
python language
 > rather than sql language.
 >
 > El mié., 10 jun. 2020 a las 12:17, Adrian Klaver
 > (mailto:adrian.kla...@aklaver.com>
>>) escribió:
 >
 >     On 6/10/20 8:25 AM, PEDRO PABLO SEVERIN HONORATO wrote:
 >      > Just to be clear those where instructions from here,
correct?:
 >      >
 >      >
 >

https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/language-pack-guide/12/EDB_Postgres_Language_Pack_Guide.1.08.html#
 >      >
 >      > Yes. When I did that I could execute python functions
and create the
 >      > extension in postgres without problem, but later I
realized that
 >     doing
 >      > this will create the other problem with anaconda.
 >      >
 >      > So when you did the Anaconda install:
 >      >
 >      > https://docs.anaconda.com/anaconda/install/windows/
 >      >
 >      > What did you check for the Advanced options in Step 8?
 >      >
 >      > I left the first one unchecked and the second one
checked, which
 >     is the
 >      > default settings.
 >      >
 >
 >     Alright. Before we go any further it would be best to
determine what
 >     your needs are. So:
 >
 >     1) Did you install Anaconda just to get Python or for the
science data
 >     set of libraries it provides?
 >
 >     2) Do you need the system Python you installed for
anything that
 >     Anaconda does not p

Re: Help with plpython3u

[PEDRO PABLO SEVERIN HONORATO]

I just think it would make your life easier to reduce the number of
Python installs you have. If I'm keeping track correctly you now have:

1) Anaconda Python

2) OS system Python

3) EDB Python.

The OS is windows, and afaik python is not included in windows, so it would
be just Anaconda python and EDB python.

What happens if you try to create a plpythonu3u function in the database?

The database crashes and tells me "*Connection to the server has been lost.*
"

El mié., 10 jun. 2020 a las 13:12, Adrian Klaver ()
escribió:

> On 6/10/20 9:57 AM, PEDRO PABLO SEVERIN HONORATO wrote:
> > Alright. Before we go any further it would be best to determine what
> > your needs are. So:
> >
> > 1) Did you install Anaconda just to get Python or for the science data
> > set of libraries it provides?
> >
> > A: For the data science set of libraries. Also, it helps to keep
> > libraries updated.
> >
> > 2) Do you need the system Python you installed for anything that
> > Anaconda does not provide?
> >
> > A: I don't know if I understood well this question, but I think the
> > answer is no. Anaconda does provide almost all the libraries I need. I
> > just installed a couple more (like talib, tensorflow, keras, psycopg2,
> > etc) and created some libraries of my own.
>
> Those libraries are available from Anaconda:
>
> https://docs.anaconda.com/anaconda/packages/py3.7_win-64/
>
> or from AnacondaCloud:
>
> https://anaconda.org/
>
> I just think it would make your life easier to reduce the number of
> Python installs you have. If I'm keeping track correctly you now have:
>
> 1) Anaconda Python
>
> 2) OS system Python
>
> 3) EDB Python.
>
>
> What happens if you try to create a plpythonu3u function in the database?
>
>
> >
> > 3) What are you planning to do with plpython3u?
> >
> > A: I'm planning to create some functions using python language to
> > process data that is stored in real time in the database and then use
> > them as triggers. I would like to be able to import some libraries that
> > I have in python to do it. Some of the functions are fourier and wavelet
> > transforms. If I can't import the library, then I'll have to code the
> > full function, but it's easier (for me) to code them in python language
> > rather than sql language.
> >
> > El mié., 10 jun. 2020 a las 12:17, Adrian Klaver
> > (mailto:adrian.kla...@aklaver.com>>)
> escribió:
> >
> > On 6/10/20 8:25 AM, PEDRO PABLO SEVERIN HONORATO wrote:
> >  > Just to be clear those where instructions from here, correct?:
> >  >
> >  >
> >
> https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/language-pack-guide/12/EDB_Postgres_Language_Pack_Guide.1.08.html#
> >  >
> >  > Yes. When I did that I could execute python functions and create
> the
> >  > extension in postgres without problem, but later I realized that
> > doing
> >  > this will create the other problem with anaconda.
> >  >
> >  > So when you did the Anaconda install:
> >  >
> >  > https://docs.anaconda.com/anaconda/install/windows/
> >  >
> >  > What did you check for the Advanced options in Step 8?
> >  >
> >  > I left the first one unchecked and the second one checked, which
> > is the
> >  > default settings.
> >  >
> >
> > Alright. Before we go any further it would be best to determine what
> > your needs are. So:
> >
> > 1) Did you install Anaconda just to get Python or for the science
> data
> > set of libraries it provides?
> >
> > 2) Do you need the system Python you installed for anything that
> > Anaconda does not provide?
> >
> > 3) What are you planning to do with plpython3u?
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Help with plpython3u

[Adrian Klaver]

On 6/10/20 10:39 AM, PEDRO PABLO SEVERIN HONORATO wrote:
I'm sorry, when I try to create a plpython3u function, the database 
tells me:


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

If I try to execute the function, then the database crashes.



Alright that was not fun. I pulled up an Windows 7 machine and installed 
Anaconda and Postgres from EDB. I installed Anaconda with the defaults 
you mentioned. I could not get Anaconda and Postgres to work together 
with regards to Python. As you have found the PYTHONHOME setting 
Postgres needs is picked up by Anaconda and prevents it from starting. 
Dropping PYTHONHOME  allows Anaconda to work but causes plpython3u to 
fail. To me the solution would be to have something like EDB_PYTHONHOME 
that only the Postgres install would use. I could not find anything like 
that though. I'm out of ideas. I now remember why I don't use Windows:( 
I was going to point you at the EDB forums, but they don't exist 
anymore. I guess you have to ask questions on Stackoverflow:


https://stackoverflow.com/questions/tagged/enterprisedb

I would try there.


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

Re: Help with plpython3u

[PEDRO PABLO SEVERIN HONORATO]

something like EDB_PYTHONHOME
that only the Postgres install would use.

That's exactly what I was looking for. Isn't a way to configure some
postgres file, so that postgres would use that file and look for python in
that directory before seeing the path?

I guess you have to ask questions on Stackoverflow

Stackoverflow was the first place where I asked this, but with less
knowledge than now. Maybe now I can ask a more concrete question about
paths.

Anyways, thank you very much for your time, Adrian. I really appreciate it.
If I manage to solve this I'll let you know, so if anyone else has the
same problem.

El mié., 10 jun. 2020 a las 18:19, Adrian Klaver ()
escribió:

> On 6/10/20 10:39 AM, PEDRO PABLO SEVERIN HONORATO wrote:
> > I'm sorry, when I try to create a plpython3u function, the database
> > tells me:
> >
> > "*ERROR: could not load library "C:/Program
> > Files/PostgreSQL/12/lib/plpython3.dll": The specified module could not
> > be found. SQL state: 58P01*"
> >
> > If I try to execute the function, then the database crashes.
> >
>
> Alright that was not fun. I pulled up an Windows 7 machine and installed
> Anaconda and Postgres from EDB. I installed Anaconda with the defaults
> you mentioned. I could not get Anaconda and Postgres to work together
> with regards to Python. As you have found the PYTHONHOME setting
> Postgres needs is picked up by Anaconda and prevents it from starting.
> Dropping PYTHONHOME  allows Anaconda to work but causes plpython3u to
> fail. To me the solution would be to have something like EDB_PYTHONHOME
> that only the Postgres install would use. I could not find anything like
> that though. I'm out of ideas. I now remember why I don't use Windows:(
> I was going to point you at the EDB forums, but they don't exist
> anymore. I guess you have to ask questions on Stackoverflow:
>
> https://stackoverflow.com/questions/tagged/enterprisedb
>
> I would try there.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Help with plpython3u

[Adrian Klaver]

On 6/10/20 4:35 PM, PEDRO PABLO SEVERIN HONORATO wrote:

something like EDB_PYTHONHOME
that only the Postgres install would use.

That's exactly what I was looking for. Isn't a way to configure some 
postgres file, so that postgres would use that file and look for python 
in that directory before seeing the path?


That is not something that is native to Postgres. The issue is that EDB 
bundles a Python version with the Language Pack and points the 
plpython(3)u extensions at it. I understand that, as they can't count on 
Python being installed as in Unix(en) OS'es or invoke a package manager 
to satisfy the dependency. The problem is when you do install some other 
form of Python on your Windows machine. Then you get the conflicts.




I guess you have to ask questions on Stackoverflow

Stackoverflow was the first place where I asked this, but with less 
knowledge than now. Maybe now I can ask a more concrete question about 
paths.


Anyways, thank you very much for your time, Adrian. I really appreciate 
it. If I manage to solve this I'll let you know, so if anyone else has 
the same problem.





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