1

In my local pc/db, I have a C# app calling Postgres user functions using multiple threads (BackgroundWorkers) using NpgSQL. But after 1h hour something happen and process stop. My guess is something is eating too many resources or maybe some memory leak even when my pc only use 6gb / 8gb.

This is the screenshot of the app, there are three threads. First two run every min, the third one is very light and only run each three min. For this test I turn off the first one.

Got these errors:

Exception while reading from stream
57P03: The database system is on recovering mode.

enter image description here

This is an know bug for NpgSql but hasn't be isolated yet. So I want try to investigate how are those call affecting the db. After got the error in the app I go to pgAdmin and when try to call the same function the thread is calling also lost the db connection. If reconnect to db and run the query again same result.

SELECT * FROM avl_db.process_avl_pool();

enter image description here

But if try a different query like this, works ok. And there is nothing running.

select query, *
from pg_stat_activity;

And after 15 min I can run the query avl_db.process_avl_pool() again.

What other place should I check to see if is something is wrong or locked or some memory leak.

FYI: avl_db.process_avl_pool() Move data from pool to traffic.avl table in blocks of 400 rows.

SELECT MAX(avl_id) INTO int_pending
FROM   avl_db.avl_pool
WHERE  NOT exported;

WHILE  int_pending > 0  LOOP     
    INSERT INTO  traffic.avl
        (avl_id, car_id, azimuth, datetime, x, y, near_link)
    SELECT avl_id, car_id, azimuth, hora, x, y, near_link
    FROM avl_db.avl_pool
    WHERE  NOT exported
    ORDER BY avl_id
    LIMIT 400;        

    UPDATE avl_db.avl_pool a
    SET exported = true
    FROM (  SELECT avl_id
            FROM avl_db.avl_pool
            WHERE  NOT exported
            ORDER BY avl_id
            LIMIT 400
          ) s
    WHERE a.avl_id = s.avl_id;

    RAISE NOTICE 'Duration in seconds= % , Rows= %', Delta, int_row_count;

    SELECT MAX(avl_id) INTO int_pending 
    FROM   avl_db.avl_pool
    WHERE  NOT exported;
    END IF;

END LOOP;

LOG FILE:

2016-12-16 11:01:33 VET LOG:  server process (PID 23252) was terminated by exception 0xC0000005
2016-12-16 11:01:33 VET DETAIL:  Failed process was running: SELECT * FROM avl_db.process_avl_pool()
2016-12-16 11:01:33 VET HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2016-12-16 11:01:33 VET LOG:  terminating any other active server processes
2016-12-16 11:01:33 VET WARNING:  terminating connection because of crash of another server process
2016-12-16 11:01:33 VET 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.
2016-12-16 11:01:33 VET HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2016-12-16 11:01:33 VET CONTEXT:  SQL statement "with index_query as (
        *** LOOP QUERY ***
        WHERE a.avl_id = sq.avl_id"
    PL/pgSQL function avl_db.process_near_link() line 24 at SQL statement
2016-12-16 11:01:33 VET WARNING:  terminating connection because of crash of another server process
2016-12-16 11:01:33 VET 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.
2016-12-16 11:01:33 VET HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2016-12-16 11:01:33 VET LOG:  all server processes terminated; reinitializing
2016-12-16 11:01:33 VET LOG:  database system was interrupted; last known up at 2016-12-16 11:00:47 VET
2016-12-16 11:01:36 VET LOG:  database system was not properly shut down; automatic recovery in progress
2016-12-16 11:01:36 VET LOG:  redo starts at 7/5D659200
2016-12-16 11:01:37 VET LOG:  invalid record length at 7/5D9BCD18
2016-12-16 11:01:37 VET LOG:  redo done at 7/5D9BCCD8
2016-12-16 11:01:40 VET FATAL:  the database system is in recovery mode
2016-12-16 11:01:40 VET LOG:  MultiXact member wraparound protections are now enabled
2016-12-16 11:01:40 VET LOG:  database system is ready to accept connections
2016-12-16 11:01:41 VET LOG:  autovacuum launcher started
  • Is there anything in the Postgres logfile? – a_horse_with_no_name Dec 15 '16 at 22:58
  • @a_horse_with_no_name Im looking on it now, first need to learn how to translate messages to english – Juan Carlos Oropeza Dec 15 '16 at 23:02
  • @a_horse_with_no_name doesnt say any relevent, isnt? The problem occurs if I try to increase number of row to be process. Any Idea what should I check? – Juan Carlos Oropeza Dec 16 '16 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.