Live queries do not always emit new data when a condition is specified

[RedShift1]

I'm submitting a ...

• [X] bug report

PostGraphile version: 4.5.5

Minimal SQL file that can be loaded into a clean database:

CREATE TABLE public.users
(
    user_id integer NOT NULL DEFAULT nextval('users_id_seq'::regclass),
    created_at tsms NOT NULL DEFAULT (date_part('epoch'::text, now()) * (1000)::double precision),
    updated_at tsms NOT NULL DEFAULT (date_part('epoch'::text, now()) * (1000)::double precision),
    display_name text COLLATE pg_catalog."default" NOT NULL,
    email text COLLATE pg_catalog."default" NOT NULL,
    on_the_clock boolean NOT NULL DEFAULT false,
    clock_location text COLLATE pg_catalog."default",
    clock_job text COLLATE pg_catalog."default",
    clock_start tsms,
    CONSTRAINT users_pkey PRIMARY KEY (user_id)
)

CREATE INDEX "displayName_idx"
    ON public.users USING btree
    (display_name COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX email_idx
    ON public.users USING btree
    (email COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX on_the_clock_idx
    ON public.users USING btree
    (on_the_clock ASC NULLS LAST)
    TABLESPACE pg_default;

Steps to reproduce:

Start a live query as follows:

subscription WhosWorking {
  users(orderBy: [DISPLAY_NAME_ASC], condition: {onTheClock: true}) {
    edges {
      node {
        id
        userId
        displayName
        clockJob
        clockStart
      }
    }
  }
}

Current behavior:

• when a users transitions, onTheClock: false -> onTheClock: true, an update message via websocket with a new list is sent
• when a user transitions, onTheClock: true -> onTheClock: false, there is no update message via websocket

Expected behavior:

When the transition onTheClock: true -> onTheClock: false happens, a new list is sent via a websocket update message.

[Venryx]

Just thought I'd mention that I tried to replicate this issue using the livesotope demo, but was unable to (for that simpler database).

Specifically, I...

1. Modified the App.js file, adding this condition to the live-query: , condition: {ranking: 0})
2. Commented out the code in the random.js cell-updating script. (so that the live-query will only try to respond to manual cell changes, as this presumably matches better with the OP's case)
3. Reset all the ranking cell values to 0, using pgAdmin. (they previously had values for me, as I'd run the demo before)
4. Started the demo using yarn dev.
5. The demo page loaded, showing all the user entries.
6. Using pgAdmin, I then modified one of the cell's ranking values to 5. The entry was successfully removed from the result set in the demo page.
7. Using pgAdmin, I then reset the cell ranking value to 0. The entry showed up again in the demo page.
8. Same thing when I repeated the process.

Thus, the issue @RedShift1 is hitting must be due to something specific about their database schema, or some environmental factor. Perhaps it is due to the on_the_clock values using an index?

It's also possible that the issue was not hit in my case due to my having updated a few postgraphile/websocket-related dependencies. I described my brief changes to the demo here.

In summary, I updated these packages:

yarn add @graphile-contrib/[email protected]
yarn add @graphile/[email protected]
yarn add [email protected]
yarn add [email protected]

@RedShift1 It maybe is worth updating the dependencies above in your project, to see if that resolves the issue. (if not, perhaps my earlier guess is correct that it's related to something about your database' schema/index setup)

(Sorry that I can't be of more help, but I'm new to PostGraphile, and attempted to replicate this issue for my own selfish reasons of evaluating possible limitations of the live-query system. For my usage so far, it seems to be working -- so unfortunately the above is as far as I'll be investigating this issue for now [unless I end up hitting it myself, of course].)

[RedShift1]

Please try with a boolean datatype and don't forget the condition on the subscription.

[Venryx]

I just tried my test again with a boolean datatype, and get the same (successful) result.

Subscription code:

const Rankings = gql`
  subscription Rankings {
    people(orderBy: RANKING_DESC, condition: {test1: true}) {
      nodes {
        id
        name
        ranking
        avatarUrl
      }
    }
  }
`;

Database entries:

Outcome: Entries show and hide from the demo page as expected, whenever I change the test1 field in the database.

As guessed earlier, there must be something specific about your schema, subscription, or environment that's causing the issue. (my guess is an outdated library somewhere in your dependency chain, or something about your edge->node layering or index usage -- though I'm no db expert)