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:
- I am using windows 10 as my os.
- 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.
- 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