Error while running plpython function in postgresql

455 views Asked by At

I have Postgresql 12.8.1 version and python 3.7.6 version installed on my system. I want to create a trigger function using plpython so I created plpython3 extension using CREATE EXTENSION plpython3u . While trying to compile the trigger function, I face the following error -

ERROR: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request

Trigger function code:

CREATE OR REPLACE FUNCTION getTemperature() RETURNS trigger as $pgsqlTrigger$
city = TD["new"]["City"]
Station  = TD["new"]["Station"]
dtime = TD["new"]["dtime"]

import requests, json, time, pandas as pd, numpy as np
from datetime import datetime
from calendar import monthrange
from copy import deepcopy
import sys

template_request = "https://api.weather.com/v1/location/{station}/observations/historical.json?apiKey=apikey&units=m&startDate={start_date}&endDate={end_date}"
df_header = ["City", "Year", "Month", "Day", "Hour", "Temperature(C)", "Condition"]
def get_weather_data(city, year, month, day, station):
    start_date = "%d%02d%02d" % (year, month, day)
    end_date = "%d%02d%02d" % (year, month, day)
    request = template_request.format(station=station, start_date=start_date, end_date=end_date)
    request_data = json.loads(requests.get(request).content)
    weather_data = []
    last_dt = None
    for observation in request_data["observations"]:
        dt = datetime.fromtimestamp(observation["valid_time_gmt"]+3600)
        if last_dt and dt.hour > (last_dt.hour + 1):
            last_row = deepcopy(weather_data[-1])
            last_row[4] = last_row[4]+1
            weather_data.append(last_row)
        weather_data.append([city, year, month, dt.day, dt.hour, observation["temp"], observation["wx_phrase"]])
        last_dt = dt
    return weather_data
dtime = datetime.strptime(dtime, '%Y-%m-%d %H:%M:%S%z')
data = get_weather_data(city, dtime.year, dtime.month, dtime.day, station)
weather_df = pd.DataFrame(data, columns=df_header).drop_duplicates(subset=["City", "Year", "Month", "Day", "Hour"])
avg = (weather_df["Temperature(C)"].values).mean()
weather_df = pd.DataFrame()
TD["new"]["temp"] = avg;
return NEW;
$pgsqlTrigger$ LANGUAGE plpython3u;



CREATE TRIGGER pgsqlTrigger
BEFORE INSERT ON tweets
FOR EACH ROW EXECUTE FUNCTION getTemperature();

I have not yet found a solution for this. Any ideas on what should I do?

UPDATE:

  1. I am using windows 10 as my os.
  2. Regarding how I installed my plpython3u extension. I was facing the error " no such module named plpython3u found" and the fix I found on net was to copy and paste the python37.dll from the local python folder to my C:/windows/System32 folder. After doing that I was able to create plpython3u extension by using the command - CREATE EXTENSION plpython3u.
  3. I am using the default user "postgres" which has the superuser permission.

below is the output of the server log: log file output:

2021-09-22 11:46:28.518 IST [10620] LOG:  server process (PID 2316) was terminated by exception 0xC0000409
2021-09-22 11:46:28.518 IST [10620] DETAIL:  Failed process was running: CREATE FUNCTION public.proc1()
2021-09-22 11:46:28.518 IST [10620] HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2021-09-22 11:46:28.522 IST [10620] LOG:  terminating any other active server processes
2021-09-22 11:46:28.547 IST [13168] WARNING:  terminating connection because of crash of another server process
2021-09-22 11:46:28.547 IST [13168] 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.
2021-09-22 11:46:28.547 IST [13168] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2021-09-22 11:46:28.566 IST [7776] WARNING:  terminating connection because of crash of another server process
2021-09-22 11:46:28.566 IST [7776] 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.
2021-09-22 11:46:28.566 IST [7776] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2021-09-22 11:46:28.586 IST [10620] LOG:  all server processes terminated; reinitializing
2021-09-22 11:46:28.668 IST [9204] LOG:  database system was interrupted; last known up at 2021-09-21 19:23:24 IST
2021-09-22 11:46:29.103 IST [14208] FATAL:  the database system is in recovery mode
2021-09-22 11:46:29.549 IST [9204] LOG:  database system was not properly shut down; automatic recovery in progress
2021-09-22 11:46:29.555 IST [9204] LOG:  redo starts at 0/1D19AB8
2021-09-22 11:46:29.562 IST [9204] LOG:  invalid record length at 0/1D21978: wanted 24, got 0
2021-09-22 11:46:29.563 IST [9204] LOG:  redo done at 0/1D21940
2021-09-22 11:46:29.618 IST [10620] LOG:  database system is ready to accept connections
0

There are 0 answers