TAIL is handled as a non-standard COPY TO statement

[timcash]

Trying to get node-postgres working with a streaming database that uses the postgres protocol. From the docs I cut a section and added it below. Having trouble getting it to work. Does this driver support something like cursor.copy_expert("TAIL some_materialized_view", sys.stdout)?

In terms of the pgwire protocol, TAIL is handled as a non-standard COPY TO statement. As long as your driver lets you send your own COPY statement to the running Materialize node, you can TAIL updates from Materialize anywhere you’d like.

#!/usr/bin/env python3

import sys
import psycopg2

def main():

    dsn = 'postgresql://localhost:6875/materialize?sslmode=disable'
    conn = psycopg2.connect(dsn)

    with conn.cursor() as cursor:
        cursor.copy_expert("TAIL some_materialized_view", sys.stdout)

if __name__ == '__main__':
    main()

[timcash]

I am trying something like this

const pg = require('pg')
const QueryStream = require('pg-query-stream')
const JSONStream = require('JSONStream')

//pipe 1,000,000 rows to stdout without blowing up your memory usage
pg.connect((err, client, done) => {
  if (err) throw err;
  const query = new QueryStream('SELECT * FROM generate_series(0, $1) num', [1000000])
  const stream = client.query(query)
  //release the client when the stream is finished
  stream.on('end', done)
  stream.pipe(JSONStream.stringify()).pipe(process.stdout)
})

[vitaly-t]

If you want to use streams, that's pg-query-stream. And if you want to use a cursor, for that there is pg-cursor.

From the two posts that you gave, there's a sense of confusion about those two separate things. 😄

[charmander]

The pg-query-stream README references pg.connect, but it’s out of date. You should connect a client as shown at https://node-postgres.com/features/connecting/. The query stream might work then.

@vitaly-t “cursor” in the Python code refers to a PEP 249 cursor, not a PostgreSQL cursor.

I was thinking of pg-copy-streams, not pg-query-stream. pg-query-stream is a wrapper for pg-cursor. You want pg-copy-streams.

[vitaly-t]

I was thinking of pg-copy-streams

Oh dear lord, there's another one. Even I got confused on that, so no wonder newcomers get confused with all that. I think @brianc needs to do a bit more unification on the library 😄

[timcash]

Ill try this out and report back