Обсуждение: BUG #16974: memory leak

The following bug has been logged on the website:

Bug reference:      16974
Logged by:          RekGRpth
Email address:      rekgrpth@gmail.com
PostgreSQL version: 13.2
Operating system:   docker alpine
Description:

1) create type
CREATE TYPE state AS ENUM
   ('PLAN',
    'TAKE',
    'WORK',
    'DONE',
    'FAIL',
    'STOP');
2) create table
CREATE TABLE task (
  id bigserial NOT NULL,
  parent bigint DEFAULT (current_setting('pg_task.id'::text,
true))::bigint,
  dt timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  start timestamp with time zone,
  stop timestamp with time zone,
  "group" text NOT NULL DEFAULT 'group'::text,
  max integer,
  pid integer,
  input text NOT NULL,
  output text,
  error text,
  state state NOT NULL DEFAULT 'PLAN'::state,
  timeout interval,
  delete boolean NOT NULL DEFAULT false,
  repeat interval,
  drift boolean NOT NULL DEFAULT true,
  count integer,
  live interval,
  remote text,
  append boolean NOT NULL DEFAULT false,
  header boolean NOT NULL DEFAULT true,
  string boolean NOT NULL DEFAULT true,
  "null" text NOT NULL DEFAULT '\N'::text,
  delimiter "char" NOT NULL DEFAULT '    '::"char",
  quote "char",
  escape "char",
  CONSTRAINT task_pkey PRIMARY KEY (id),
  CONSTRAINT task_parent_fkey FOREIGN KEY (parent)
      REFERENCES task (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL
)
3) create indexes
CREATE INDEX task_dt_idx ON task USING btree (dt);
CREATE INDEX task_parent_idx ON task USING btree (parent);
CREATE INDEX task_state_idx ON task USING btree (state);
4) create file update.sql
WITH s AS (SELECT id FROM task AS t WHERE dt < current_timestamp -
concat_ws(' ', (60::int4 * 1000::int4)::text, 'msec')::interval AND state IN
('TAKE'::state, 'WORK'::state) AND pid NOT IN (
    SELECT      pid FROM pg_stat_activity
    WHERE       datname = current_catalog AND usename = current_user AND
application_name = concat_ws(' ', 'pg_task',
current_setting('pg_task.schema', true), 'task', "group")
) FOR UPDATE SKIP LOCKED) UPDATE task AS u SET state = 'PLAN'::state FROM s
WHERE u.id = s.id
5) create file timeout.sql
WITH s AS (WITH s AS (WITH s AS (WITH s AS (WITH s AS (
    SELECT      t.id, t.group, COALESCE(t.max, ~(1<<31)) AS max, a.pid FROM
task AS t
    LEFT JOIN   task AS a ON a.state = 'WORK'::state AND t.group = a.group
    WHERE       t.state = 'PLAN'::state AND t.dt + concat_ws(' ', (CASE WHEN
t.max < 0 THEN -t.max ELSE 0 END)::text, 'msec')::interval <=
current_timestamp
) SELECT id, "group", CASE WHEN max > 0 THEN max ELSE 1 END - count(pid) AS
count FROM s GROUP BY id, "group", max
) SELECT array_agg(id ORDER BY id) AS id, "group", count FROM s WHERE count
> 0 GROUP BY "group", count
) SELECT unnest(id[:count]) AS id, "group", count FROM s ORDER BY count
DESC
) SELECT s.* FROM s INNER JOIN task USING (id) FOR UPDATE SKIP LOCKED
) UPDATE task AS u SET state = 'TAKE'::state FROM s WHERE u.id = s.id
RETURNING u.id, u.group, u.remote, COALESCE(u.max, ~(1<<31)) AS max
6) open psql
7) in opened psql execute above two files periodically
\i sql/update.sql
\i sql/timeout.sql
8) the memory (using by corresponded postgres backend) is increasing
constantly

PG Bug reporting form <noreply@postgresql.org> writes:
> ...
> 7) in opened psql execute above two files periodically
> \i sql/update.sql
> \i sql/timeout.sql
> 8) the memory (using by corresponded postgres backend) is increasing
> constantly

I tried this, and I don't see any evidence of a leak, not even
after I inserted some data into "task" so that the queries were
actually doing something.

The backend's "SHR" memory size will increase for awhile, but
that's just evidence of it touching more and more of the shared
buffer pool over time.  The total virtual memory size ("VIRT"
column in top) is rock-solid steady, so there's no actual
accumulation of leaked memory.

Perhaps there's an issue here somewhere, but if so, you haven't
given a sufficient description of how to reproduce it.

            regards, tom lane

Yes,
The backend's "SHR" memory size increase from ~20Mb on startup to
~150Mb by one day (with inserts and updates into table)

вт, 20 апр. 2021 г. в 19:32, Tom Lane <tgl@sss.pgh.pa.us>:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > ...
> > 7) in opened psql execute above two files periodically
> > \i sql/update.sql
> > \i sql/timeout.sql
> > 8) the memory (using by corresponded postgres backend) is increasing
> > constantly
>
> I tried this, and I don't see any evidence of a leak, not even
> after I inserted some data into "task" so that the queries were
> actually doing something.
>
> The backend's "SHR" memory size will increase for awhile, but
> that's just evidence of it touching more and more of the shared
> buffer pool over time.  The total virtual memory size ("VIRT"
> column in top) is rock-solid steady, so there's no actual
> accumulation of leaked memory.
>
> Perhaps there's an issue here somewhere, but if so, you haven't
> given a sufficient description of how to reproduce it.
>
>                         regards, tom lane

RekGRpth <rekgrpth@gmail.com> writes:
> The backend's "SHR" memory size increase from ~20Mb on startup to
> ~150Mb by one day (with inserts and updates into table)

As I said, that proves little and indeed is expected ... especially
if your shared_buffers setting is around that.  There's no leak
unless you see VIRT increasing significantly.

            regards, tom lane

Ok, thanks.

I solved it by reconnect on some requests and/or after some timeout


ср, 21 апр. 2021 г. в 18:27, Tom Lane <tgl@sss.pgh.pa.us>:
>
> RekGRpth <rekgrpth@gmail.com> writes:
> > The backend's "SHR" memory size increase from ~20Mb on startup to
> > ~150Mb by one day (with inserts and updates into table)
>
> As I said, that proves little and indeed is expected ... especially
> if your shared_buffers setting is around that.  There's no leak
> unless you see VIRT increasing significantly.
>
>                         regards, tom lane