0

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
7
  • Look in the server's log file for a better error message. Commented Sep 21, 2021 at 14:23
  • What OS an version are you using? How did you get/install the plpython3u extension? Do you have the plpythonu extension installed? Are creating the function as a superuser? Add answers as update to your question. Commented Sep 21, 2021 at 15:13
  • I have updated my original post. Please check. Thanks Commented Sep 22, 2021 at 5:57
  • updated original post with server log output. Commented Sep 22, 2021 at 6:30
  • That still did not answer of where you actually got the extension from? Assuming you used the EDB installer take a look at Language Packs. You can't run against just any version of Python. Commented Sep 22, 2021 at 14:21

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.