Option to ignore pg_temp_xxxx tables

[jmealo]

When you refresh a materialized views you get a ton of events from pg_temp_# tables that I don't think are useful for most use cases. It'd be helpful to have a way of filtering these out at the logical decoding level to cut down on noise.

[leptonix]

Could you give an example (table/view structures) with the change which results in these events?

[jmealo]

If you refresh a materialized view or do a large insert over the default page size (usually 8kb) resulting in use of a TOAST table.

So to reproduce:

1. Refresh a materialized view
2. Insert a 10kb JSON document into a JSONB column

[leptonix]

Ok, will try that.

Could also just ignore all pg_# tables. (or just pgtemp#) What do you think?

[jmealo]

For example, if you create a foreign table backed by mysql_fdw:

CREATE FOREIGN TABLE fdw_sessions (
    "ID" integer NOT NULL,
    "Class" text NOT NULL,
    "Created" timestamp without time zone NOT NULL,
    "Handle" character varying NOT NULL,
    "LastRequest" timestamp without time zone NOT NULL,
    "LastIP" bigint,
    "PersonID" integer
)
SERVER $spark
OPTIONS (
    dbname '$school',
    table_name 'sessions'
);

And create a materialized view that mirrors that foreign table:

CREATE MATERIALIZED VIEW sessions AS SELECT * FROM fdw_sessions;

And refresh that materialized view:

REFRESH MATERIALIZED VIEW sessions;

You'll get this:

PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":47068,"Class":"UserSession","Created":"2015-12-16 20:13:06","Handle":"e3332d47b016a58b131b37c75c006796","LastRequest":"2015-12-16 20:13:06","LastIP":27338928
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":47311,"Class":"UserSession","Created":"2015-12-16 21:39:51","Handle":"cf93d2ba058f8baf
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":47555,"Class":"Us
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":48042,"Class":"UserSession","Created":"2015-12-17 02:55:23","Handle":"b95cff9dd71db1a823f884627337d424","LastRequest":"2015-12-17 02:55:23","L
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":48285,"Class":"UserSession","Created":"2015-12-17 04:43:39","Handle":
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{
PostgreSQL: SyntaxError: Unexpected end of input{"type":"tabl
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":49013,"Class":"UserSession","Created":"2015-12-17 10:15:21","Handle":"f3cb886178a2c93e70ca1804db679586","LastRequest"
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":49256,"Class":"UserSession","Created":"2015
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"I
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":49995,"Class":"UserSession","Created":"2015-12-17 15:29:11","Handle":"redacted","LastRequest":"2015-12-17 15:29:11","LastIP":2733892876,"PersonID
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":50238,"Class":"UserSession","Created":"2015-12-17 16:26:01","Handle":"redacted","Las
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":50484,"Class":"UserSession","Created":"2015-12-17 17:26:
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":50
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","na
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":51223,"Class":"UserSession","Created":"2015-12-17 20:44:41","Handle":"redacted","LastRequest":"2015-12-17 20:44:41","LastIP":2
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":51468,"Class":"UserSession","Created":"2015-12-17 22:23:04","Handle":"a06e967
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":51711,"Class":"UserSession","Created":"2015
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","ch
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":52197,"Class":"UserSession","Created":"2015-12-18 03:46:28","Handle":"redacted","LastRequest":"2015-12-18 03:46:28","LastIP":"redacted","PersonID"
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":52440,"Class":"UserSession","Created":"2015-12-18 05:36:37","Handle":"cdabfa2408fb3842091c10003a
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":52683,"Class":"UserSes
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","n
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":53168,"Class":"UserSession","Created":"2015-12-18 11:05:10","Handle":"redacted","LastRequest":"2015-12-18 11:05:10","Las
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":53411,"Class":"UserSession","Created":"2015-12-18 12:54:52","Handle":"
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47398","change":"INSERT","data":{"ID":53658,"Class":"UserSession","C
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47
PostgreSQL: SyntaxError: Unexpected end of input{"type":"table","schema":"merit-live","name":"pg_temp_47426","change":"INSERT","data":{"ID":24,

Sometimes I get invalid JSON. I'm running pg_recvlogical with decoding_json on the same machine as PostgreSQL so it shouldn't be network fragmentation.

[jmealo]

I'm not sure what the implications or filtering out all pg_ tables would be... ignoring pg_temp tables seems safer for now.

[leptonix]

pgtemp# tables are now ignored, only not the transactions in which they occur

I will add an option to omit all transaction data later.

[leptonix]

Have to read up on materialized views, don't know anything about that.

[jmealo]

@leptonix thanks, I'll deploy your latest changes during our next maintenance window.