nothingisdead / pg-live-query Goto Github PK

Hi,

Super excited for what you've built here. Thing is I was running the simple example and got this error and am not sure why. May you please help me?

Steps (in case you were wondering)

1. npm init
2. installed and saved yours and npm pg.
3. copy + pasted example
4. edited it for pg pool config
5. ran it - failed

$ node app.js
/Users/shalomabitan/sites/testing/node-pg-live-query/node_modules/pg-live-query/index.js:4

class LiveQuery extends Watcher {
^^^^^

SyntaxError: Block-scoped declarations (let, const, function, class) not yet supported outside strict mode
    at exports.runInThisContext (vm.js:53:16)
    at Module._compile (module.js:374:25)
    at Object.Module._extensions..js (module.js:405:10)
    at Module.load (module.js:344:32)
    at Function.Module._load (module.js:301:12)
    at Module.require (module.js:354:17)
    at require (internal/module.js:12:17)
    at Object.<anonymous> (/Users/shalomabitan/sites/testing/node-pg-live-query/app.js:3:19)
    at Module._compile (module.js:398:26)
    at Object.Module._extensions..js (module.js:405:10)

npm version 3.10.9
node verison v5.3.0

Thanks for any help :)

So basically pg-live-query adds id and rev to all the tables (if not already present) and the watching logic relies on the idea that your custom query will only track one id field (if any, otherwise the one added is the one of the main from table).

Actually this query (the one in the readme) should not work (I tried with a very similar and indeed it did fail with a mesage like "column id is ambiguous"):

        SELECT
            *
        FROM
            users u JOIN
            logins l ON
                l.user_id = u.id
        WHERE
            l.date > '2016-01-01'

To get a slightly better understanding look at this part of the code:
https://github.com/nothingisdead/pg-live-query/blob/master/watcher.js#L271
Many subsequent call to alias q in this sql query use the id field which does not mean anything if there are multiple of them. And there are multiple of them if your query results in multiple of them.

@nothingisdead, how do you think this could be solved ? I can think of two main solutions, either meta columns are prefixed with their table names (but that would require a bunch of changes in many places in the code) or the meta olumns are aliased in the "magic" query in the watcher but I can't see how that would be possible given an arbitrary sql command (some kind of filter ?).

As long as this remains that way, beware of queries with multiple id columns beeing returned

Geideaservices.net

Discussion of plans to optimize the refreshing of result sets

• For simple queries that generate updatable views, push serialized changed rows in the trigger's NOTIFY payload then verify if they match the original query using a simple CTE with the specified WHERE clause appended.
• Re-introduce manual triggers

Both of these options require putting the serialized changed row data in the NOTIFY payload.

Hey, seems there's a mistake in the code in pguids :

if(i === 'RangeVar') {
  const table  = tree[i].relname;
  const schema = tree[i].schemaname || null;
  const alias  = null;
  const key    = JSON.stringify([ schema, table ]);

  if(tree[i].alias) {
    alias = tree[i].alias.Alias.aliasname;
  }

  tables[key] = { table, schema, alias };
}

Will submit a PR to change alias to let, but perhaps a more thorough understanding of the underlying action might bring a better fix ?

EDIT: seems like alias is actually referring to the SQL aliases in a query (because that's what caused the issue)

Sometimes it works, sometimes it does not and queries start failing because of missing columns. I am observing this when running benchmarks (see #10). Maybe I am not initializing it correctly.

Memory usage is growing and growing through time, even just for watch.

I have made a benchmarking tool and added comparisons with other similar projects. See it here. Results are at the end. It seems your project has a memory leak or something. Also, the latency jumps a lot around. Sometimes it is very fast, sometimes it has some strange spikes.

Hey, awsome package, but how do you stop the query watching ? (without closing the connection I mean... Connections should be reusable)

This question may carry some distance.

Can this module be used with AWS Redshift? It's based off PostgreSQL so theoretically it should work