WITH clauses not implemented?

General
#1

Please excuse my SQL noobie status.

I was just trying to feed some timestamp value into an INSERT using a WITH clause. Like so:

WITH time_end AS  (SELECT '2017-12-03 20:00:00')
    INSERT INTO agregates_1
        SELECT
            source_id,
            time_end AS "timestamp",    -- The end time of the agregate 
            avg(value) AS average,
            sqrt(variance(value)) AS standard_deviation,
            count(value) AS count
        FROM series_1
        WHERE ("timestamp" > time_end - interval '24 hours') AND ("timestamp" <= time_end)  -- The start to end time of the agregate
        GROUP BY source_id;

I was not expecting that to work off the bat but it trips up on the first line:

invalid syntax: statement ignored: unimplemented at or near ")"
DETAIL: source SQL:
WITH time_end AS  (SELECT '2017-12-03 20:00:00')
                                           ^

I guess WITH is unimplemented but searching for “WITH” around here is predictably not helping.

Or am I just doing it wrong?

I can live with out WITH here, just wondering.

#2

WITH clauses, otherwise known as common table expressions, are currently not supported in CockroachDB.

See https://github.com/cockroachdb/cockroach/issues/7029 for more details.

#3

Thanks jordan,

I don’t begin to understand the use cases of common table expressions seen in your issue link.

I just wanted to specify a scalar value (timestamp) for use in the rest of the query.

No worries I can get by without.

#4

You could also use placeholders for this, if you wished.

#5

“placeholders”?

Sorry jordan, is that a thing in SQL? Google is not helping.

I’m just about to do the WITH thing I need with Javascript template literals.

#6

Sorry, I should have been more explicit. Placeholders are entities like $1 and $2 in a query. If you’re using a SQL driver, they usually have first-class support for placeholders to help you avoid SQL injection attacks.

For example, here’s the docs on Go’s database driver: https://golang.org/pkg/database/sql/#DB.Exec

If you’re just using the SQL shell, you could use PREPARE and EXECUTE if you want, which support placeholders.

PREPARE x AS INSERT INTO aggregates SELECT ..., $1::timestamp AS "timestamp", ... FROM ... WHERE ... ("timestamp" > $1 - interval '24 hours')… `

And then EXECUTE x('2017-12-03 20:00:00').

Does that make sense? Copy pasting the value would work too, of course.

1 Like
#7

jordan,

Yes it make sense, sort of, “place holders” as in macro substitution.

So in the sql shell I prepare my query like so:

PREPARE agregate AS
    INSERT INTO agregates_1
        SELECT
            source_id,
            $1::timestamp AS "timestamp",    -- The end time of the agregate 
            avg(value) AS average,
            sqrt(variance(value)) AS standard_deviation,
            count(value) AS count
        FROM series_1
        WHERE ("timestamp" >= ($1::timestamp - interval '24 hours')) AND ("timestamp" < $1::timestamp)  -- The start to end time of the agregate
        GROUP BY source_id;

That is accepted.

Then I execute it like so:

EXECUTE agregate ('2017-12-03 20:00:00');

But I get the error:

pq: value type timestamp doesn't match type TIMESTAMPTZ of column "timestamp"

I have tried every which way I can think of to fix that to no avail.

The “timestamp” columns in my series_1 and agregates_1 tables are both “TIMESTAMP WITH TIME ZONE NOT NULL”

Sorry for my noobie SQL questions here.

#8

OK, think I found the trick.

My prepare is now this:

PREPARE agregate AS
    INSERT INTO agregates_1
        SELECT
            source_id,
            $1::timestamp with time zone AS "timestamp",    -- The end time of the agregate 
            avg(value) AS average,
            sqrt(variance(value)) AS standard_deviation,
            count(value) AS count
        FROM series_1
        WHERE ("timestamp" >= ($1::timestamp - interval '24 hours')) AND ("timestamp" < $1::timestamp)  -- The start to end time of the agregate
        GROUP BY source_id;

and then I execute like so:

> EXECUTE agregate ('2017-12-04 20:00:00');
INSERT 4

Time: 2.4535008s

Looks like I have to spell out the type cast as “$1::timestamp with time zone” rather than the short hand “$1::timestampz”

I have now learned more about SQL than I ever though I might need to get this little job done!

#9

Oh shoot, the PREPARE thing is pretty useless from node.js and pg.

The PREPARE works OK.

The EXECUTE works OK.

But, trying to do another EXECUTE before the first one finishes fails with some error about how the prepared query “aggregate” does not work.

As far as I can tell it’s only possible to have one prepared query in flight at a time.

Looks like it’s back to JS string literals to create the queries for me…

#10

Hmm, that’s fairly surprising. Concurrent executes should work fine.

Could you please share details about the error?

#11

jordan,

It’s not pretty but here is my test code:

const fs = require('fs')
const { Pool } = require('pg')

const pgconfig = {
  host: '32.233.966.433',
  port: 26257,
  user: 'someone',
  database: 'time_series',
  // max: 100,  // The pool size
  ssl: {
    rejectUnauthorized: false,
    ca: fs.readFileSync('./certs/ca.crt').toString(),
    key: fs.readFileSync('./certs/client.root.key').toString(),
    cert: fs.readFileSync('./certs/client.root.crt').toString()
  }
}

const pool = new Pool(pgconfig)

function prepareAggregateQuery (cb) {
  const query = `PREPARE aggregate AS
    UPSERT INTO aggregates_1
        SELECT
            source_id,
            $1::timestamp with time zone AS "timestamp",    -- The end time of the aggregate
            avg(value) AS average,
            sqrt(variance(value)) AS standard_deviation,
            count(value) AS count
        FROM series_1
        WHERE ("timestamp" >= ($1::timestamp - interval '1 hours')) AND ("timestamp" < $1::timestamp)  -- The start to end time of the aggregate
        GROUP BY source_id;`

  pool.query(query, (err, res) => {
    if (err) {
      console.log('Error:', err)
      cb(err)
    } else {
      cb(null)
    }
  })
}

function executeAggregateQuery (timestamp, cb) {
  const query = `EXECUTE aggregate ('${timestamp}')`

  pool.query(query, (err, res) => {
    if (err) {
      console.log('Error:', err)
      cb(err)
    } else {
      cb(null, res.rows)
    }
  })
}

prepareAggregateQuery(function (err) {
  if (err) {
    console.log('Error: prepareAggregateQuery failed', err)
  } else {
    console.log('prepareAggregateQuery: OK', err)

    executeAggregateQuery('2017-12-04 04:00:00', function (err) {
      if (err) {
        console.log('Error: executeAggregateQuery', err)
      } else {
        console.log('executeAggregateQuery: OK', err)
      }
    })

    executeAggregateQuery('2017-12-04 05:00:00', function (err) {
      if (err) {
        console.log('Error: executeAggregateQuery', err)
      } else {
        console.log('executeAggregateQuery: OK', err)
      }
    })
  }
})

And here is the result:

prepareAggregateQuery: OK null
Error: { error: prepared statement "aggregate" does not exist
    at Connection.parseE (/home/michael/fits-monitor/node_modules/pg/lib/connection.js:546:11)
    at Connection.parseMessage (/home/michael/fits-monitor/node_modules/pg/lib/connection.js:371:19)
    at TLSSocket.<anonymous> (/home/michael/fits-monitor/node_modules/pg/lib/connection.js:114:22)
    at emitOne (events.js:115:13)
    at TLSSocket.emit (events.js:210:7)
    at addChunk (_stream_readable.js:266:12)
    at readableAddChunk (_stream_readable.js:253:11)
    at TLSSocket.Readable.push (_stream_readable.js:211:10)
    at TLSWrap.onread (net.js:585:20)
  name: 'error',
  length: 119,
  severity: 'ERROR',
  code: '26000',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'sql/prepare.go',
  line: '339',
  routine: 'getPreparedStatementForExecute' }
Error: executeAggregateQuery { error: prepared statement "aggregate" does not exist
    at Connection.parseE (/home/michael/fits-monitor/node_modules/pg/lib/connection.js:546:11)
    at Connection.parseMessage (/home/michael/fits-monitor/node_modules/pg/lib/connection.js:371:19)
    at TLSSocket.<anonymous> (/home/michael/fits-monitor/node_modules/pg/lib/connection.js:114:22)
    at emitOne (events.js:115:13)
    at TLSSocket.emit (events.js:210:7)
    at addChunk (_stream_readable.js:266:12)
    at readableAddChunk (_stream_readable.js:253:11)
    at TLSSocket.Readable.push (_stream_readable.js:211:10)
    at TLSWrap.onread (net.js:585:20)
  name: 'error',
  length: 119,
  severity: 'ERROR',
  code: '26000',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'sql/prepare.go',
  line: '339',
  routine: 'getPreparedStatementForExecute' }
executeAggregateQuery: OK null

Only one of the EXECUTES works.

If I rearrange things such that the second execute only happens when the first has completed all is OK:

$ node query-db.js
prepareAggregateQuery: OK null
executeAggregateQuery: OK null
executeAggregateQuery: OK null

#12

I forgot to mention that prepared statements are session-scoped. You need to prepare a statement before you use it in any individual session.

But in general there is a much easier way to do this. You can use parameterized queries to send the values to the database without having to do string concatenation, which is generally frowned upon because it opens the door to sql injection attacks.

Looks like the docs for the node pg driver have this covered pretty well: look at the Parameterized Queries section in this link. https://node-postgres.com/features/queries

#13

jordan,

Thanks. Great stuff.

This test shows something more like what I expect to do:

const fs = require('fs')
const { Pool } = require('pg')

const pgconfig = {
  host: '32.901.223.52',
  port: 26257,
  user: 'someone',
  database: 'time_series',
  // max: 100,  // The pool size
  ssl: {
    rejectUnauthorized: false,
    ca: fs.readFileSync('./certs/ca.crt').toString(),
    key: fs.readFileSync('./certs/client.root.key').toString(),
    cert: fs.readFileSync('./certs/client.root.crt').toString()
  }
}

const pool = new Pool(pgconfig)

const query = `
    UPSERT INTO aggregates_1
        SELECT
            source_id,
            $1::timestamp with time zone AS "timestamp",    -- The end time of the aggregate
            avg(value) AS average,
            sqrt(variance(value)) AS standard_deviation,
            count(value) AS count
        FROM series_1
        WHERE ("timestamp" >= ($1::timestamp - interval '1 hours')) AND ("timestamp" < $1::timestamp)  -- The start to end time of the aggregate
        GROUP BY source_id;`

pool.query(query, ['2017-10-01 00:00:00'], (err, res) => {
  if (err) {
    console.log('Error:', err)
  } else {
    console.log('OK !!!!')
  }
})

pool.query(query, ['2017-10-01 01:00:00'], (err, res) => {
  if (err) {
    console.log('Error:', err)
  } else {
    console.log('OK !!!!')
  }
})

Which produces:

OK !!!!
OK !!!!

Thanks again.