Realtime: randomly receiving payload with missing column

[xylish7]

Bug report

Describe the bug

I've enabled realtime on one of my tables and randomly I get payloads which are missing one column. In my case, the column which sometimes is not present in the payload is the pending one, which is of type jsonb. The bug also appears only when the pending column has a lot of data in it. Another thing is that the bug popped up when I've sent a lot of requests one after another.

In this case the pending column was in the payload:

After quick updates in the database, at one moment, I've received the payload without the pending column:

The way I'm updating the row is using a rpc function:

I've seen that running the function without modifying the pending column will result always in a payload which does not have the pending column in it, so this is the reason I've added/modified line 12, 37, 47 and 54. Updating the pending column like this seems to reduce the chance of truncated payload.

To Reproduce

1. Create a table using the following sql query:

create table events (
  id            bigint generated by default as identity primary key,
  updated_at    timestamp with time zone default timezone('utc'::text, now()) not null,
  created_at    timestamp with time zone default timezone('utc'::text, now()) not null,
  group_id      bigint references groups on delete cascade not null,
  name varchar(50) not null,
  details varchar(2000),
  icon varchar(30) not null,
  date timestamp with time zone not null,
  vote_before timestamp with time zone not null,
  status event_status default 'open',
  coming jsonb default '[]',
  not_coming jsonb default '[]',
  pending jsonb default '[]',
  cars jsonb default '[]',
  location json
);

2. Add a row which has the data present in the record prop of the payload below:

{
	"event": "UPDATE",
	"payload": {
		"columns": [
			{
				"name": "id",
				"type": "int8"
			},
			{
				"name": "updated_at",
				"type": "timestamptz"
			},
			{
				"name": "created_at",
				"type": "timestamptz"
			},
			{
				"name": "group_id",
				"type": "int8"
			},
			{
				"name": "name",
				"type": "varchar"
			},
			{
				"name": "details",
				"type": "varchar"
			},
			{
				"name": "icon",
				"type": "varchar"
			},
			{
				"name": "date",
				"type": "timestamptz"
			},
			{
				"name": "vote_before",
				"type": "timestamptz"
			},
			{
				"name": "status",
				"type": "event_status"
			},
			{
				"name": "coming",
				"type": "jsonb"
			},
			{
				"name": "not_coming",
				"type": "jsonb"
			},
			{
				"name": "pending",
				"type": "jsonb"
			},
			{
				"name": "cars",
				"type": "jsonb"
			},
			{
				"name": "location",
				"type": "json"
			}
		],
		"commit_timestamp": "2022-01-24T12:33:54.97765+00:00",
		"errors": null,
		"old_record": {
			"id": 47
		},
		"record": {
			"cars": [
				{
					"timestamp": "2022-01-24T12:33:54.976183+00:00",
					"uid": "4308830f-bf7d-4b77-8028-fdb635a4e8e7"
				}
			],
			"coming": [
				{
					"timestamp": "2022-01-24T12:13:53.328576+00:00",
					"uid": "4308830f-bf7d-4b77-8028-fdb635a4e8e7"
				}
			],
			"created_at": "2022-01-23T19:24:51.461407+00:00",
			"date": "2022-01-26T19:24:32+00:00",
			"details": "",
			"group_id": 1,
			"icon": "book",
			"id": 47,
			"location": null,
			"name": "Test event",
			"not_coming": [],
			"pending": [
				{
					"uid": "11a225a4-2a19-4d8f-acec-7a740aeca233"
				},
				{
					"uid": "c40634a0-33be-45b5-af08-4dc60e12db40"
				},
				{
					"uid": "e48fddd4-8b02-4f9f-b70d-4ceb31b9af1a"
				},
				{
					"uid": "e1ab00e9-0b20-4363-939e-cd2c7f310790"
				},
				{
					"uid": "4c00150e-7598-466e-8599-71b4f659df99"
				},
				{
					"uid": "50de9980-f376-42cb-b328-32aba1cba4d4"
				},
				{
					"uid": "d2304c09-1feb-42ee-a5f6-b5d828d5fc30"
				},
				{
					"uid": "b4e06aaa-492c-4b7d-bf46-7969be70648a"
				},
				{
					"uid": "8113e1c7-1d62-4b62-b50a-ad562905f891"
				},
				{
					"uid": "8d34e021-4635-4f40-939c-8547b856b184"
				},
				{
					"uid": "505729ee-a06b-4d7a-8718-5506903fc7be"
				},
				{
					"uid": "8561bb06-b1f9-455e-8133-756ed1b0b2dd"
				},
				{
					"uid": "0c04a304-ca17-48a2-92fc-0940acd0994b"
				},
				{
					"uid": "7c9cefcf-ff75-4bd7-88bc-1b8db26a69b8"
				},
				{
					"uid": "5c31df02-85c2-45d2-975a-e5c970731bee"
				},
				{
					"uid": "22a655aa-de30-40da-97e2-a4ee29ee069e"
				},
				{
					"uid": "a04994d3-03ce-44f4-9777-8c775a68cd3c"
				},
				{
					"uid": "b10bb3f1-0e1e-4690-8ca6-969c0ec8f864"
				},
				{
					"uid": "8d3e36f5-7529-43d6-86f3-c92dfa32837d"
				},
				{
					"uid": "cab14307-1e18-4ca1-a4ec-be1580133f30"
				},
				{
					"uid": "7f57abb6-03c3-4b9e-8737-fa3d46be9a52"
				},
				{
					"uid": "8c0a13e7-a7ec-46ad-872e-201d1159531e"
				},
				{
					"uid": "d3ed9770-782b-4b70-96fa-dc0b92cb296f"
				},
				{
					"uid": "7c6d7b4a-bb22-48a2-a326-cde37fb83eef"
				},
				{
					"uid": "cac4b560-8327-47a3-9e61-03a7c600acf0"
				},
				{
					"uid": "10937d83-8990-4880-80e8-0f0b7fd3b252"
				},
				{
					"uid": "11f5395d-bf1e-4deb-b65c-1421b66e12fd"
				},
				{
					"uid": "bdba1b96-2854-41f9-959b-f80d6f9b5d70"
				},
				{
					"uid": "127fa12a-a53e-4e32-bb14-ac6c99667eac"
				},
				{
					"uid": "eda97956-9132-4e9e-8d0a-b6564e0adea7"
				},
				{
					"uid": "fd2c94dc-2e87-43b0-978a-dd546f5bbc77"
				},
				{
					"uid": "d329b1e8-2be8-475d-b6b1-41c3f0071fc0"
				},
				{
					"uid": "fe55cbff-0db5-47ec-b919-71e6aec8073e"
				},
				{
					"uid": "03df26d1-666b-43e3-aa6e-2a6ff5218c0c"
				},
				{
					"uid": "2fae759f-9ffd-468b-b1be-3af9ed5ec788"
				},
				{
					"uid": "82d96717-f889-4ab5-9b65-3f6ae7e97bc1"
				},
				{
					"uid": "84a6cc2d-2c17-4e31-b29b-6f5659ab0e17"
				},
				{
					"uid": "74ac9c7d-1c36-4601-aa57-c7a0a0f24ffa"
				},
				{
					"uid": "0df5422c-a311-4914-a5d4-b517d2c1ff15"
				},
				{
					"uid": "8748dab2-c54f-4dcf-8a74-d353fedebe78"
				},
				{
					"uid": "b69466b4-c31f-4772-8143-e92edfc4122f"
				},
				{
					"uid": "884706f8-62bb-4a9c-a127-2991f6b09602"
				},
				{
					"uid": "c8cc856a-2612-4ac7-b3cf-810e8e4403fd"
				},
				{
					"timestamp": "2022-01-24T12:12:47.202088+00:00",
					"uid": "6d15fffe-283a-42af-baba-c65ec8b899ae"
				},
				{
					"timestamp": "2022-01-24T12:20:30.120694+00:00",
					"uid": "e7c8fdec-1cbc-42b0-a8f8-d5e2787a2f59"
				}
			],
			"status": "open",
			"updated_at": "2022-01-23T19:24:51.461407+00:00",
			"vote_before": "2022-01-24T19:24:32+00:00"
		},
		"schema": "public",
		"table": "events",
		"type": "UPDATE"
	},
	"ref": null,
	"topic": "realtime:public"
}

3. Update the row 2 times per second (sometimes the payload may be truncated even on the first update) until a truncated payload with missing pending column is received.

Expected behavior

The payload should always have all columns present in it.

System information

• OS: Windows 11
• Browser: Mozilla Firefox
• Version of supabase-js: 1.28.4
• Version of Node.js: 16.13.0

[kiwicopple]

Thansk for the detailed bug report! I'll move this one over to the realtime server

[w3b6x9]

@xylish7 Thanks for writing this up!

This isn't a bug but a limitation. Postgres uses the TOAST technique to store large values. As a result, Postgres replication doesn't include those columns with large values when they have not been updated which means Realtime RLS is unable to pass them on to the client.

The easiest solution is to set the REPLICA IDENTITY to FULL for your events table:

ALTER TABLE events REPLICA IDENTITY FULL;

[w3b6x9]

@xylish7 and just to clarify by enabling the REPLICA IDENTITY to FULL you'll receive the column in old_record and not record (@olirice thanks for pointing this out!).

You can always go with your solution:

added/modified line 12, 37, 47 and 54. Updating the pending column like this seems to reduce the chance of truncated payload.

[xylish7]

Thank you for your fast response and sorry for taking me so long to answer back. Did not knew about that TOAST technique. However is still weird that even if I update that pending array for fast consecutive updates there are times when the pending column is missing in the payload.

I will give it a try with REPLICA IDENTITY set to FULL to see if this is still the case. Wanted to avoid this approach because of the additional payload size :)