sql - PostgreSQL 错误 : payload string too long

Question

我有以下 PostgreSQL 表和实现表历史记录的触发器函数:

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();

对于某些超过一定限制的查询，我有以下错误:

[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

这样的查询示例是:

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;

我发现 max notify 有限制有效负载为 8000 字节。我没有在触发器函数中直接使用 notify，但似乎有些运算符间接使用它。

是否可以通过某种变通方法来克服此限制？

Answer 1

不幸的是 8000 似乎是硬限制 - 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.)

另见此处 - 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.