Foreign key popover doesn't show corresponding row

[jkillian]

Postico version: 2.1.2

When clicking the little foreign key icon to see a related row, the popup table often doesn't show the relevant data. Below is a gif of it happening in practice for me. Let me know if there's any other helpful debugging information I can provide!

[jakob]

Is that a UUID column or a text/varchar column?

[jkillian]

Is that a UUID column or a text/varchar column?

It's a UUID column, here's the DDL from the cell table:

"hexVersionId" uuid NOT NULL REFERENCES hex_version(id) ON DELETE CASCADE,

And then on the hex_version table it's just this:

id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,

[jakob]

Thanks. I'll see if I can reproduce the unexpected behavior tomorrow. I think it might be a combination of the foreign table having more than 1000 rows and not being sorted. A possible workaround may be to sort the rows in the popover by clicking the header. Postico should remember the sort setting next time you open the popover.

[jkillian]

Thanks @jakob! I think your hunch might be right, I think FKs to smaller tables normally work correctly for me. Doing a quick test and it looks like your sorting workaround also works.

I do have the 'Always sort tables' option disabled (because some of our tables can be quite large) so I bet that might be an additional factor here too?

[jakob]

OK. The problem is that if the table isn't sorted, Postico doesn't know what page of the results to load, and pagination isn't guaranteed to work either, since the rows can be returned in an arbitrary order.

One possible workaround would be to add a filter that shows only the selected row. That would be useful for people who want to view the data from the foreign row and probably even speed everything up. But it would be annoying for people who want to select a row from the popover, because they would then need to change the filter first.

[jkillian]

Good point, admittedly I use Postico almost primarily as a read-only tool so most of the time I'm not thinking about selecting data at all. Maybe it would be reasonable to only apply the filter if there's more than one page of data? It seems like using the UI for selection if probably more likely for small tables. It'd also still be pretty easy to clear the filter in that case if you really want to select a row and it feels a bit more intuitive to go from "selected row" and move to "all rows" than not have the selected row show up at all.

Just brainstorming though, thanks for all the thoughts into my bug report!