PostgreSQL ERROR: payload string too long

Question

I have the following PostgreSQL table and trigger function implementing history of the table:

CREATE TABLE "ps_counters"
(
  "psid" integer NOT NULL,
  "counter" bigint[] NOT NULL -- This is the array of 256 counters. Upon insertion of new PS all these values must set to array of 256 bigint elements all equal to 0.
);

CREATE TABLE "ps_counters_history"
(
  "id" serial PRIMARY KEY,
  "timestamp" timestamp NOT NULL DEFAULT clock_timestamp(),
  "psid" integer NOT NULL,
  "counter" bigint[] NOT NULL
);

CREATE OR REPLACE FUNCTION ps_counters_history_trigger()
  RETURNS trigger AS
$BODY$
  DECLARE
    table_name text;
  BEGIN
    table_name := 'ps_counters_history_' || to_char(CURRENT_DATE, 'yyyy_mm');
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = table_name)
    THEN
      EXECUTE 'CREATE TABLE IF NOT EXISTS ' || table_name ||
              ' () INHERITS (ps_counters_history);';
    END IF;
    EXECUTE 'INSERT INTO ' || table_name ||
            '(psid, counter) VALUES ($1.psid, $1.counter);' USING NEW;
    RETURN NEW;
  END
$BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER ps_counters_history_trigger
AFTER INSERT OR UPDATE ON ps_counters FOR EACH ROW
EXECUTE PROCEDURE ps_counters_history_trigger();

And for some queries above certain limit I have the following error:

[2016-12-09 11:04:28.233844] [0x00007fc72ebaa700] [error]: PlayStation (ID: 5; DallasID: MC00-0497): ERROR:  payload string too long
CONTEXT:  SQL statement "SELECT pg_notify('addedrecord',json_build_object('dataNew',dataNew,'dataOld',dataOld,'table',TG_TABLE_NAME,'query',TG_OP,'schema',TG_TABLE_SCHEMA )::text )"
PL/pgSQL function ps_counters_history_trigger() line 13 at PERFORM

Sample such query is:

UPDATE ps_counters SET
counter[0] = 2035400,
counter[1] = 723500,
counter[2] = 5105400,
counter[3] = 750900,
counter[4] = 750900,
counter[5] = 12,
counter[6] = 10,
counter[7] = 2,
counter[8] = 0,
counter[9] = 0,
counter[10] = 0,
counter[11] = 161500,
counter[12] = 0,
counter[13] = 0,
counter[14] = 0,
counter[15] = 0,
counter[16] = 0,
counter[17] = 0,
counter[18] = 0,
counter[19] = 0,
counter[20] = 0,
counter[21] = 0,
counter[22] = 0,
counter[23] = 1901300,
counter[24] = 0,
counter[25] = 0,
counter[26] = 0,
counter[27] = 0,
counter[28] = 723500,
counter[29] = 0,
counter[30] = 0,
counter[31] = 5105400,
counter[32] = 0,
counter[33] = 0,
counter[34] = 5828900,
counter[35] = 5856300,
counter[36] = 2062800,
counter[37] = 0,
counter[38] = 12,
counter[39] = 0,
counter[40] = 0,
counter[41] = 0,
counter[42] = 0,
counter[43] = 0,
counter[44] = 0,
counter[45] = 0,
counter[46] = 1901300,
counter[47] = 0,
counter[48] = 0,
counter[49] = 0,
counter[50] = 0,
counter[51] = 0,
counter[52] = 0,
counter[53] = 0,
counter[54] = 0,
counter[55] = 0,
counter[56] = 0,
counter[57] = 0,
counter[58] = 0,
counter[59] = 0,
counter[60] = 0,
counter[61] = 0,
counter[62] = 0,
counter[63] = 0,
counter[64] = 5,
counter[65] = 0,
counter[66] = 0,
counter[67] = 6,
counter[68] = 0,
counter[69] = 0,
counter[70] = 5,
counter[71] = 5,
counter[72] = 4,
counter[73] = 0,
counter[74] = 0,
counter[75] = 0,
counter[76] = 0,
counter[77] = 0,
counter[78] = 0,
counter[79] = 0,
counter[80] = 0,
counter[81] = 0,
counter[82] = 0,
counter[83] = 0,
counter[84] = 0,
counter[85] = 0,
counter[86] = 0,
counter[87] = 0,
counter[88] = 0,
counter[89] = 0,
counter[90] = 0,
counter[91] = 0,
counter[92] = 0,
counter[93] = 0,
counter[94] = 0,
counter[95] = 0,
counter[96] = 0,
counter[97] = 0,
counter[98] = 0,
counter[99] = 0,
counter[100] = 0,
counter[101] = 0,
counter[102] = 0,
counter[103] = 0,
counter[104] = 0,
counter[105] = 0,
counter[106] = 0,
counter[107] = 0,
counter[108] = 0,
counter[109] = 0,
counter[110] = 0,
counter[111] = 0,
counter[112] = 0,
counter[113] = 0,
counter[114] = 0,
counter[115] = 0,
counter[116] = 0,
counter[117] = 0,
counter[118] = 0,
counter[119] = 0,
counter[120] = 0,
counter[121] = 0,
counter[122] = 0,
counter[123] = 0,
counter[124] = 0,
counter[125] = 0,
counter[126] = 0,
counter[127] = 0,
counter[128] = 0,
counter[129] = 0,
counter[130] = 0,
counter[131] = 0,
counter[132] = 0,
counter[133] = 0,
counter[134] = 0,
counter[135] = 0,
counter[136] = 0,
counter[137] = 0,
counter[138] = 0,
counter[139] = 0,
counter[140] = 0,
counter[141] = 0,
counter[142] = 0,
counter[143] = 0,
counter[144] = 0,
counter[145] = 0,
counter[146] = 0,
counter[147] = 0,
counter[148] = 0,
counter[149] = 0,
counter[150] = 0,
counter[151] = 0,
counter[152] = 0,
counter[153] = 0,
counter[154] = 0,
counter[155] = 0,
counter[156] = 0,
counter[157] = 0,
counter[158] = 0,
counter[159] = 0,
counter[160] = 0,
counter[161] = 0,
counter[162] = 0,
counter[163] = 0,
counter[164] = 0,
counter[165] = 0,
counter[166] = 0,
counter[167] = 0,
counter[168] = 0,
counter[169] = 0,
counter[170] = 0,
counter[171] = 0,
counter[172] = 0,
counter[173] = 0,
counter[174] = 0,
counter[175] = 0,
counter[176] = 0,
counter[177] = 0,
counter[178] = 0,
counter[179] = 0,
counter[180] = 0,
counter[181] = 0,
counter[182] = 0,
counter[183] = 0,
counter[184] = 0,
counter[185] = 0,
counter[186] = 0,
counter[187] = 0,
counter[188] = 0,
counter[189] = 0,
counter[190] = 0,
counter[191] = 0,
counter[192] = 2035400,
counter[193] = 0,
counter[194] = 2035400,
counter[195] = 0,
counter[196] = 2,
counter[197] = 0,
counter[198] = 0,
counter[199] = 0,
counter[200] = 0,
counter[201] = 0,
counter[202] = 0,
counter[203] = 0,
counter[204] = 0,
counter[205] = 0,
counter[206] = 0,
counter[207] = 0,
counter[208] = 0,
counter[209] = 0,
counter[210] = 0,
counter[211] = 0,
counter[212] = 0,
counter[213] = 0,
counter[214] = 0,
counter[215] = 0,
counter[216] = 0,
counter[217] = 0,
counter[218] = 0,
counter[219] = 0,
counter[220] = 0,
counter[221] = 0,
counter[222] = 0,
counter[223] = 0,
counter[224] = 0,
counter[225] = 0,
counter[226] = 0,
counter[227] = 0,
counter[228] = 0,
counter[229] = 0,
counter[230] = 0,
counter[231] = 0,
counter[232] = 0,
counter[233] = 0,
counter[234] = 0,
counter[235] = 0,
counter[236] = 0,
counter[237] = 0,
counter[238] = 0,
counter[239] = 0,
counter[240] = 0,
counter[241] = 0,
counter[242] = 0,
counter[243] = 0,
counter[244] = 0,
counter[245] = 0,
counter[246] = 0,
counter[247] = 0,
counter[248] = 0,
counter[249] = 0,
counter[250] = 1901300,
counter[251] = 0,
counter[252] = 0,
counter[253] = 0,
counter[254] = 0,
counter[255] = 0
WHERE psid = 5;

I found that there is limitation in max notify payload which is 8000 bytes. I don't use notify directly in the trigger function but it seems that some of operators use it indirectly.

Is it possible with some kind of work workaround to overcome this limitation?

Answer 1

Unfortunately 8000 seems to be hard limit - https://www.postgresql.org/docs/9.4/static/sql-notify.html

payload

The "payload" string to be communicated along with the notification.

This must be specified as a simple string literal. In the default configuration it must be shorter than 8000 bytes. (If binary data or large amounts of information need to be communicated, it's best to put it in a database table and send the key of the record.)

See also here - https://github.com/xstevens/pg_kafka

Before implementing this project I had looked into LISTEN/NOTIFY operations in PostgreSQL. NOTIFY is unfortunately limited to 8000 bytes for the total payload size. I also found several mentions in the PostgreSQL mailing lists that NOTIFY was never intended to send row data; rather it was intended to get change notifications on keys to clean up external caching, etc.