PostgreSQL – Resolving pg_stat_activity Entries Without Client Process

A safe and reliable way is to use netstat.

I would start with netstat --numeric-ports | grep 5432 as this will show you active connections. The problem of course is that there may be some false positives. The following should work for most cases though:

netstat --numeric-ports | awk '/(localhost:5432|PGSQL.5432)/ {print $0}' | wc -l

Inspecting the process table (with ps) won't necessarily give you the number of open connections: there is no reason you might not start more worker processes than you need at startup.

The fragment you posted so far can be simplified to:

INSERT INTO table2 (id, name, date)  -- why "date" if you insert a timestamp?
SELECT NEW.id, t1.name, NEW.timestamp
FROM   table1 t1
WHERE  ST_DWithin(NEW.position
                , ST_SetSRID(ST_MakePoint(t1.longitudedecimal, t1.latitudedecimal), 4326)
                , 0.01447534783)
AND    t1.id > 0;  -- probably redundant!

IF NOT FOUND THEN ...

• It is very inefficient to run separate queries with assignments in plpgsql instead of a single query.

• Basic type names like date or timestamp lead to confusing error messages and other conflicts, id and name are the worst possible column names, non-descriptive and with countless duplicates all over your tables. Revisit your naming convention ...

You probably do not need any of this. Inserting millions of rows shouldn't be handled by triggers which are fired for each row. Extremely expensive. You need a set-based solution without triggers.

Probably best to COPY to a tmeporary staging table and INSERT / UPDATE from there. But basic information is missing.

Related answers:

• Optimizing bulk update performance in PostgreSQL

• How to update selected rows with values from a CSV file in Postgres?