up vote 0 down vote favorite

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?

share|improve this question
    
\sf ps_counters_history_trigger() ?.. – Vao Tsun Dec 9 '16 at 9:35
up vote 1 down vote

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.
share|improve this answer
1  
yes, try select proname from pg_proc where lower(prosrc) like '%pg_notify%' and proname <> 'pg_notify' to see if function is called somewhere – JosMac Dec 9 '16 at 12:30
1  
hmmm, then please try the same but with like '%notify%' - to see if command "notify" is called somewhere – JosMac Dec 9 '16 at 13:51
1  
Good - look into underlying trigger procedures of these 2 triggers if some of them issues notify with parameters shown in error message. You probably have some complicated chain of actions and error message does not show whole error stack. – JosMac Dec 9 '16 at 14:46
1  
OK, even better :-D and is this database the only one involved in whole system? Aren't there any actions on other database/ instances? Don't send some audit data from some other trigger somewhere else - another pg db? – JosMac Dec 9 '16 at 15:08
1  
OK, let's check something else - please try select * from pg_rules where lower(definition) like '%notify%' – JosMac Dec 9 '16 at 15:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.