PKs returned by realtime update hook varies from what is actually in the database

[mlanza]

Bug report

Describe the bug

After logging into the client side in my browser:

  supabase
    .from("tables")
    .select()
    .then(console.log.bind(console));

The above returns my single record (just one in the table) which includes the acutal keys as they exist in the database:

...
   id: '823Wonk34yU', //pk
   game_id: '8Mj1', //fk
...

The table was declared as such and configured for realtime observance:

CREATE TABLE tables (
    id char(11) default generate_uid(11) not null primary key,
    game_id char(4) references games(id) not null,
    ...

The generate_uuid chooses random letters and numbers in a certain length. These kinds of keys are preferred because they obfuscate serial progression of typical serial int columns.

I want to take advantage of realtime observers on this table. I got it to trigger my callbacks, but I struggled to add the filters. Without the filter, it worked. With the filter it didn't.

      const sub = supabase
        .from("tables:id=eq.823Wonk34yU") // with filter
        .on("*", (payload) => {
          console.log("->", payload);
        })
        .subscribe();

So I inspected the data returned while no filter was set and I saw something strange. On both the new and old snapshot of the record, the ids were something other than what they are in the database:

...
   id: '8', //pk
   game_id: '8', //fk
...

Both the pk and fk were set to '8', not as an int, but as a string. The values in the table are char but random and more than one char. The values returned by the realtime hook are not what is actually stored in the database, what is correctly returned by the select earlier. When I filtered on id=eq.8 it worked but this is not right.

To Reproduce

1. Create a table with a char(11) pk and stuff it with random characters (letters and numbers).
2. Configure it for realtime.
3. Then subscribe to changes with realtime on the client side after logging in.
4. Trigger changes. I did so but creating a touches int on the same table and running an update that adds 1 to it every time it's called. I called the update from the supabase SQL Editor.
5. Set a breakpoint or debugger statement within the realtime callback. Confirm that the pk being returned is the random characters actually stuffed into the field. In my case it wasn't.

Expected behavior

I expect the new and old realtime snapshots in the JavaScript client (in the browser) to correctly reflect the data actually on the record.

I suspect this may be a condition which is not correctly handled in the code somewhere, perhaps because most developers uses guids and ints for pks rather than char(11) what supabase says is format bpchar. Or maybe there's in incorrect object merge somewhere in the code. Whatever the case, it's returning objectively incorrect data.

System information

• OS: macOS
• Browser: Vivaldi
• Version of supabase-js: import { createClient } from 'https://cdn.jsdelivr.net/npm/@supabase/supabase-js/+esm';

[mlanza]

Wondering if calling a table 'tables', a potential reserved word, I renamed the table to tabletops and it had the same result.

[w3b6x9]

@mlanza thanks so much for reporting this issue. This is a server-side issue that we'll investigate and will post our findings here.

[mlanza]

Reading that Walrus' handling of char was the culprit, I used the suggestion of using varchar instead and that does solve the immediate issue. It does not overcome that fact that the proposed alternative eliminates the opportunity to use the situationally more pragmatic char.

[chasers]

@mlanza this looks to be more walrus related so I'm going to close it. If you have any more related issues please feel free to re-open.