170 questions
0
votes
0
answers
59
views
Why plpython functions increase transaction counter much more then plpgsql functions?
I'm trying to understand why plpython function has much bigger impact on transaction counter in Postgres than plpgSQL function. Below is example which uses 2 functions:
Vesion with plpgSQL (each part ...
- 331
0
votes
1
answer
307
views
Postgres - TypeError: tabulate() got an unexpected keyword argument 'colalign'
I have problem in Postgres 16.2 with plpython procedure and library tabulate.
I created procedure:
CREATE OR REPLACE PROCEDURE dpl.__test_tabulate()
LANGUAGE plpython3u AS $plpy$
from tabulate import ...
- 1
2
votes
0
answers
653
views
"ERROR: could not load library "C:/Program Files/PostgreSQL/14/lib/plpython3.dll": The specified module could not be found." plpython3u Windows 10
When ever I try to create extension plpython3u
ERROR: could not load library "C:/Program Files/PostgreSQL/14/lib/plpython3.dll": The specified module could not be found.
This message shows ...
0
votes
0
answers
140
views
python function for plpython3u
I have a python function that works perfectly, but I need to pass it to plpython3u (don't ask me why, juniors only obey)
python function:
import cryptography.hazmat.primitives.serialization.pkcs12 ...
0
votes
0
answers
269
views
how to download modules to use with plpython postgresql
I'm trying to use plpython3u with postgresql on windows 11, and I want to use the requests library to make http requests..
So I did this test function:
CREATE OR REPLACE FUNCTION teste()
...
0
votes
0
answers
835
views
An I/O error occurred while sending to the backend postgresql
Just so you understand my case:
Recently I was trying to install plpython3u on postgresql 15, and with a lot of struggle I managed
I set the python environment variables (both for system and user) and ...
0
votes
0
answers
453
views
ERROR: could not load library plpython3u postgresql
I'm trying to install the plpython3u extension in my postgresql with this command: CREATE EXTENSION plpython3u;
mas eu recebo este erro:
ERROR: could not load library "C:/Program Files/PostgreSQL/...
0
votes
2
answers
162
views
plpy does not print on the postgres log
I have a plpython trigger function and I'm trying to log something in it to better understand what it does, but I can't manage to print anything on the postgres log file.
I have tried different level ...
- 4,747
1
vote
0
answers
349
views
how to install python3 in postgres
I'm trying to work with plpython in postgresql
So I run the command:
CREATE EXTENSION plpython3u
but I get the error:
Erro SQL [58P01]: ERROR: could not load library "C:/Program Files/PostgreSQL/...
2
votes
2
answers
384
views
PostgreSQL Procedure using python write dataframe to table
I am developing a PostgreSQL Procedure using the plpython3u extension which let you use python procedural language into PostgreSQL.
With the following code using plpy, I am able to retrive data form ...
- 1,517
1
vote
1
answer
110
views
plpygis can't find shapely dependency
I have a problem with the plpygis library for python. Whenever I'm trying to convert a plpygis Point type into its shapely counterpart, I get the following error:
ERROR: plpygis.exceptions....
- 125
1
vote
1
answer
359
views
Use virtual environment for plpython3u in PostgreSQL
I'm trying to use packages from an existing virtual environment within the plpython3u extension for PostgreSQL. I found and followed this tutorial, but it didn't work.
Firstly, the configuration file ...
- 125
0
votes
1
answer
2k
views
Update Redis cache value from PostgreSQL
I am working on a Python project and I have a requirement that whenever a row is inserted, updated, or deleted in a PostgreSQL table, the corresponding value should be updated in Redis. I am working ...
- 1
1
vote
0
answers
78
views
Are `AFTER UPDATE` triggers `FOR EACH ROW` atomic?
I'd like to insert a row inside a "history" table whenever the original row gets modified (updated). I'd like to use original row's ID as a FOREIGN KEY for the history table. But this ID ...
- 2,124
2
votes
1
answer
196
views
problem to access of the properties of an edge when using plpython3u in PostgreSQL to create functions
I implement python function for for the following query and i get correct answer, but when i try to create functions in postgreSQL by using plpython3u extension, it doesn't have any error but still ...
- 23
0
votes
0
answers
42
views
Using dask delayed function from within postgresql plpython with "plpy.execute"
The below example demonstrates using dask delayed funtions (ref) from within postgres plpython while using "plpy.execute" (ref) to query the database.
It returns an error:
ERROR: ...
- 10.4k
0
votes
1
answer
479
views
Can pl/python be used to write postgres functions in an azure hosted db?
Is it possible to write postgres functions in pl/python on an azure hosted db?
Postgres allows python, but I read that creating python functions requires superuser; however, I also read that azure ...
- 373
0
votes
1
answer
85
views
select a specific row for each group according to a custom logic
I have the following table:
create table test_table (
id serial primary key,
store text,
price int,
id_2 text);
insert into test_table (store, id_2, price) values
('a', '11', 100),
('...
- 332
1
vote
1
answer
220
views
How is PL/Python code executed by Postgresql
When a PL/Python procedure is executed, the python code is executed by a Python interpreter. My question is, is the Python interpreter running as a separate process, or is it a shared library that ...
- 545
0
votes
0
answers
177
views
how to resolve this backend error when using plpython in SQL
I'm using postgres 14 for creating this function in Datagrip:
CREATE EXTENSION plpython3u;
CREATE OR REPLACE FUNCTION pymax (x integer, y integer) RETURNS integer
AS $$
if x>y:
...
- 1,595
1
vote
2
answers
411
views
PL/Python: trying to understand global dictionary
I am using pl/python3u in postgres. In that environment I have imported pandas and networkx. I have created a simple function that builds a graph from information in the tables and it works ...
- 1,297
1
vote
0
answers
200
views
Installing PL/Python2 on Ubuntu for PostgreSQL 12
PL/Python, and specifically PL/Python 2, is a requirement for the MADlib extension - which appears to support Postgres 9.2+. I am using Postgres 12 on Ubuntu 20.04.
Unfortunately, while I have ...
- 71
0
votes
0
answers
180
views
Plpython3u breaking encoding on execute
Environment:
Debian 11 with pl_PL locale(ISO-8859-2)
Postgresql 13 database created with ISO 8859-2 encoding
Plpython3u(Python 3.9.2)
A simple example of the problem:
CREATE OR REPLACE FUNCTION ...
- 1
0
votes
0
answers
264
views
Problem to update postgres table column values using TD["new"] from update trigger function written in plpython3u
I am facing problems in updating column values from the postgresql trigger function written in plpython3u using TD['new'].
All the other statements before and after the TD['new'] assignments are ...
6
votes
1
answer
2k
views
How to control which Python version plpython3 uses?
I have a postgres 11 database running on CentOS 7 and am trying to use PL/python, but need to point to a specific version of the python interpreter. I need to use python3.9 whereas it is using python3....
- 837
1
vote
0
answers
606
views
Where is the missing postgres-plpython-12 for Ubuntu 20.04, required for psycopg2?
There is no available package for postgres-plpython-12
https://packages.ubuntu.com/search?keywords=postgresql-plpython
The psycopg2 library does not work with plpython3 (or does it)?
psycopg2.errors....
- 1,714
0
votes
2
answers
468
views
Greenplum Database : enabling PL/Python module
I'm on Version 6.6.14 of Greenplum and trying to enable python 3. When I try to enable it with this command:
CREATE LANGUAGE plpython3u;
I get the following error:
ERROR: could not access file &...
- 11
1
vote
1
answer
230
views
How to enable hstore_plpython3u
The extension hstore_plpython3u is supposed to transform hstore to/from python dict as described in the docs, but doesn't seem to work. The hstore becomes a string (no transform seems to happen). Why, ...
2
votes
5
answers
10k
views
PostgreSQL 13 + Python 3.7.9 + plpython3u: 'psql: server closed the connection unexepectedly.' + 'The application has lost the database connection.'
I have added all of the details I could find, with all of the links, and there is no way to get plpython3u to work on Windows in PostgreSQL 13, it seems.
OLD, since the accepted answer shows that v3.7....
- 9,922
0
votes
1
answer
340
views
time.sleep() has 60 sec limit in plpythonu?
Note: I'm running Postgres 11.7 and Python 2.7.17.
It appears that time.sleep() has 60 sec limit in plpythonu function. It works as expected up to 60 seconds. But if passed a value greater than 60 ...
- 13.8k
0
votes
1
answer
760
views
How can i Call restFull API in PostgreSQL
I am using PostgreSQL 12.6, compiled by Visual C++ build 1914, 64-bit on Windows Server. And like to know how can I call RestFull API using python or perl in postgres.
I am relatively new in call ...
- 13
1
vote
0
answers
368
views
Global variables implementation using plperl/plpython in postgres is throwing out of memory
We are actually migrating db from oracle to postgres. We are using postgreSQL 11.
So the packages are converted to procedures/functions accordingly.
For global variables implementation we used the ...
0
votes
1
answer
165
views
Confused about strange import behavior within plpython stored procedures
I'm running Postgresql 9.4, and I'm confused about how it handles plpython imports, specifically regarding Decimal.
First example:
CREATE OR REPLACE FUNCTION devel.foo()
RETURNS void AS
$BODY$
def ...
1
vote
1
answer
399
views
PostgreSQL: PL/Python function not taking ARRAY input parameter
Objective:
Creating a full text search engine using PostgreSQL FTS.
Populating tsvector:
I have a tsvector column.
To populate tsvector column, I am executing a trigger which calls a procedure which ...
- 1,598
2
votes
0
answers
318
views
Use plpython in PostgreSQL13 function to connect pymssql error
I want to use plpython in postgresql's function. I installed Python3.9 and configured the path and some other configations, and I copied the python's dll into the pg directory (my server is windows ...
- 53
1
vote
1
answer
1k
views
Upgrade postgresql 9.6 -> postgresql 13 blocked by missing plpython2 extension, but I can't seem to remove the extension
I'm trying to upgrade a system from Postgresql 9.6 to Postgresql 13 using pg_upgrade shipped with Postgresql 13. This upgrade fails with the error:
Your installation references loadable libraries ...
- 13
0
votes
1
answer
268
views
Can I pass a variable through $1 to a "NOTIFY" command in PostgreSQL?
I have the following working code :
CREATE EXTENSION IF NOT EXISTS plpython3u;
UPDATE pg_language SET lanpltrusted = true WHERE lanname LIKE 'plpython3u';
CREATE OR REPLACE FUNCTION ...
- 21
1
vote
1
answer
2k
views
PostgresSQL could not load library plpython3.dll
I managed to install the plpython3u extention. But when I try to use a function that uses the extension I get:
ERROR: could not load library "C:/Program Files/PostgreSQL/12/lib/plpython3.dll&...
- 11
1
vote
1
answer
1k
views
Cannot install plpython for postgres 12
First off , my question is posted after reading many other questions like this one
Install PL/Python on Windows for PostgreSQL 12
and various trials of previous methods and comments.
But I ve been ...
- 11
0
votes
1
answer
301
views
How to run multiprocess on a windows server with pl/python 3?
I am running a trigger function on INSERT/UPDATE that would create a new process that sends a post request to an api.
on a Ubuntu + PostgresQL 12 docker container running I was able to get the new ...
- 621
0
votes
1
answer
534
views
Running system command in Postgres and use return value in Insert
I have a linux command that i want to run in following function:
CREATE FUNCTION tarifador_func2() RETURNS TRIGGER
LANGUAGE plsh
AS $$
#!/bin/sh
SET userid[] = // here i want to run linux command and ...
- 2,885
0
votes
1
answer
1k
views
Sending data from POSTGRESQL trigger to a python TCP server
I'm trying to monitor a table in postgres database.
I want to send every operation on this table (INSERT OR UPDATE OR DELETE) to a python tcp server.
So I tried to follow this tutorial, but without ...
- 355
3
votes
4
answers
7k
views
Install PL/Python on Windows for PostgreSQL 12
I've been working on FHIR for a project and we are using PostgreSQL as a database. While reading docs, I've come to know about PL/Python and decided to give it a shot but I am unable to install the ...
- 495
0
votes
1
answer
325
views
PostgreSQL, plpython3u function. os.getenv does not find the environment variable
I get the below situation with PostgreSQL plpython3u function.
I've recently added a environment variable to my shell startup file. The env | grep MY_VAR command returns MY_VAR=/path/. But when I run ...
- 1,057
1
vote
2
answers
32k
views
PostgreSQL 11: Error [57P03]: FATAL: the database system is in recovery mode
I'm trying to get the plpython3u language working in PostgreSQL 11 (I'm working on a Windows 10 machine).
I was able to successfully install it using the following command.
CREATE EXTENSION ...
- 4,191
0
votes
0
answers
93
views
plpython3u path issue with network drive
I have a working postgres trigger for folder operations on local drives. I would like to move these operations to a network drive where K is a network drive:
CREATE OR REPLACE FUNCTION "public"."...
- 352
1
vote
0
answers
68
views
plpythonu how to pass a text array to a plpythonu function?
How do I pass an array of text strings (phone numbers) as a parameter - recipients - to a python function, then translate it into a json payload?
Simplified, the CREATE STATEMENT:
CREATE OR REPLACE ...
- 649
0
votes
1
answer
173
views
Postgresql functions execution process
I have a procedural function (written in pl/python) which queries table A, does some calculations and then returns a set. I use this function as query for my materialized view B.
Everything works ...
- 518
2
votes
2
answers
502
views
Is there a way to remove the “plpy.Error:” prefix from an exception raised with `plpy.error`?
In a PL/pgSQL function, RAISE EXCEPTION 'mymessage' USING ...; will have “mymessage” as the error message, but in a PL/Python function, plpy.error('mymessage', ...) will have “plpy.Error: mymessage” ...
- 305
6
votes
1
answer
2k
views
"Module not found" when importing a Python package within a plpython3u procedure
I am using a plpython3 stored function, on a postgres database on MacOS (installed with standard Enterprise DB package).
I can import standard python packages such as:
CREATE OR REPLACE FUNCTION foo(x ...
- 3,969