Features of key_match

[narrowtux]

We're looking to write a Store for Postgres and have the following plan:

Add this table as the store:

CREATE TABLE pow_store (
  namespace text,
  key ARRAY[bytea],
  value bytea
)

When a record is added to the store, we map its key like this: Enum.map(key, &:erlang.term_to_binary/1) so each key element is in its own binary and we can match on it later in a where clause.

When a key_match like this is passed to all/2 [MyApp.User, :user, "cc307fed-31ee-414d-b467-9690c7f39232", :session, :_] , we'd convert it to roughly this WHERE clause:

WHERE key[0] = $1 AND key[1] = $2 AND key[2] = $3 AND key[3] = $4 AND array_length(key) = $5

with these params expressed in elixir:

[
  :erlang.term_to_binary(MyApp.User),
  :erlang.term_to_binary(:user),
  :erlang.term_to_binary( "cc307fed-31ee-414d-b467-9690c7f39232"),
  :erlang.term_to_binary(:session),
  5
]

The question is: Is :_ the only "magic" from the erlang match spec you'll ever use in a key_match? If so, our approach should work forever.

[danschultzer]

The question is: Is :_ the only "magic" from the erlang match spec you'll ever use in a key_match? If so, our approach should work forever.

I expect so as I plan to keep the store super simple, but I would definitely recommend to guard against future changes by validating the values. You also have to remember to expire the records, so there should be an expires_at column.

Alternatively you can use Stream and :ets.match_spec_run/2 like in the Redis guide to iterate through the records, and future proof the match.

I've discussed postgres cache quite a few times, and I think it's better to keep the relational structure if possible. It makes lookups easier and keeps overhead down. To do that you have to make custom versions of the cache modules such as Pow.Store.CredentialsCache, but you can then have e.g. a sessions table that stores the session_id, user_id, metadata, expires_at and you won't deal with stale user records being returned.

[narrowtux]

this is the library in case you're interested: https://github.com/ZennerIoT/pow_postgres_store

[danschultzer]

Cool! Has it been working as expected for you?

I would probably have made the whole store into a GenServer so put/delete are async calls, and have auto expiration there. Also for tests, this is the way I usually do it: https://hexdocs.pm/pow/1.0.16/redis_cache_store_backend.html#test-module

Your issue prompted me to refactor how stores work so it would be easier to do a relational structure with Ecto. I would like for it to be relatively easy to tie user to a record.

[narrowtux]

Thanks, so far we haven't run into issues with it.

Postgres handles concurrency so well we don't really need to limit accessing this to just a single process that could get overloaded as the application scales.

Also the simple expires_at > now() filter means that we don't need to expire the records at that exact time it happens, but in a much lower frequency.

Linking the records with a user through a foreign key would be nice, especially because if you delete a user, it cleans up the sessions automatically. But I think pow does this internally anyway since in-memory stores won't give that convenience anyway?

[danschultzer]

Yeah, that makes sense.

Linking the records with a user through a foreign key would be nice, especially because if you delete a user, it cleans up the sessions automatically. But I think pow does this internally anyway since in-memory stores won't give that convenience anyway?

Sessions can still be active after a user has been deleted. E.g. Pow.Plug.delete_user/2 will only clear the current users session. I'll see if I can tighten this up, since it's trivial to clear all sessions for a user.