4

Whenever I try to do a simple SELECT on my Postgres db, I get this error : 

tutorial=> select id from table LIMIT 1;
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

I try to increase "max_locks_per_transaction" to 256, increased all memory parameter, it does nothing. The command just took longer to return.

All just worked fine during months, then this problem appears and I can't trace the logs to find what happens.

Here is the boot sequence when the problem appear, after I try to reboot postgres when this problem occurs :

LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 2/2C7A1580
LOG:  invalid record length at 2/2C848AC8: wanted 24, got 0
LOG:  redo done at 2/2C848AA0
LOG:  last completed transaction was at log time 2018-01-29 14:38:52.726603+00
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

My database implement TimescaleDB and PostGIS plugin and is composed of one table only. I can do all other statement (INSERT, UPDATE, etc), but SELECT won't work and it's pretty annoying.

It only concern this hypertable. I only have the table spatial_ref_sys next to it and I have no problem with this one.

With EXPLAIN, I get the exact same behaviour and no information record :

tutorial=> EXPLAIN SELECT id FROM table LIMIT 1;
WARNING:  out of shared memory
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

The server is a Debian 2x86 64bits core and 2GB of memory. I set up this database with docker following these steps : http://docs.timescale.com/v0.8/getting-started/installation/linux/installation-docker

Here is the instructs I used to set up the db :

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
CREATE EXTENSION postgis; 
CREATE EXTENSION postgis_topology;
CREATE TABLE "public"."table" (
    "id" BIGSERIAL NOT NULL PRIMARY KEY,
    "id_s" int8 NOT NULL,
    "id_g" int8,
    "datetime" TIMESTAMPTZ DEFAULT now(),
    [...]
);
SELECT create_hypertable('table', 'datetime');
ALTER TABLE table ADD CONSTRAINT id_pkey PRIMARY KEY (id, datetime);
CREATE INDEX ON table (id_s, datetime DESC);
CREATE INDEX ON table (id_g, datetime DESC);
ALTER TABLE table ADD COLUMN geom geometry(POINT,2192)
Raphiki
  • 190
  • 3
  • 11

0 Answers0