error: date/time field value out of range: "1655239143"

[EvgeniyRRU]

I'm try to use connect-pg-simple with keystone5-application, it based on express 4.16. It standard use case, based on documentation:

const keystone = new Keystone({
  name: PROJECT_NAME,
  adapter: new KnexAdapter(),
  cookie: {
    secure: process.env.NODE_ENV === "production", // Defaults to true in production
    maxAge: 1000 * 60 * 60 * 24 * 30, // 30 days
    sameSite: false,
  },
  sessionStore: new PgSession({ createTableIfMissing: true, pruneSessionInterval: 600 }),
  cookieSecret: process.env.COOKIE_SECRET,
  appVersion: {
    version: "2.0.0",
    addVersionToHttpHeaders: true,
    access: true,
  },
// .... so one
});

When it was in local development stage (I used docker-compose and posgresql-13 image), it worked fine. But when I tried to deploy my application (to 11.4 postgresql), my code has broken.

Error looked something like this:

error: date/time field value out of range: "1655239143"
    at Parser.parseErrorMessage (/app/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/app/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/app/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/app/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:390:28)
    at Socket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:199:23)

It seems, postgresql changed to_timestamp casts (maybe even syntax?).
Postgres 13.6:

Postgres 11.4:

I suppose pg parametrized query wraps timestamp value into single quotes.

[voxpelli]

We are running the CI tests across the latest versions of Postgres 13, 12, 11, 10 and 9.6:

node-connect-pg-simple/.github/workflows/nodejs.yml

Line 29 in 25b1fbc

postgres_version: [13, 12, 11, 10, 9.6]

And they are passing, which is an indication of one of two things:

1. The test coverage is having a blind spot that makes it miss these
2. This bug report is inaccurate.

If you could reproduce it within the test suite, then that would be great 🙏

[voxpelli]

The tests here are testing this exact method:

node-connect-pg-simple/test/integration/express.spec.js

Lines 187 to 218 in a7bfb0b

	it('should invalidate a too old token', () => {
	store = new (connectPgSimple(session))({ conObject, pruneSessionInterval: false });
	const app = appSetup(store);
	const agent = request.agent(app);
	const clock = sinon.useFakeTimers(Date.now());
	return queryPromise('SELECT COUNT(sid) FROM session')
	.should.eventually.have.nested.property('rows[0].count', '0')
	.then(() => Promise.all([
	request(app).get('/'),
	agent.get('/')
	]))
	.then(() => queryPromise('SELECT COUNT(sid) FROM session'))
	.should.eventually.have.nested.property('rows[0].count', '2')
	.then(() => {
	clock.tick(maxAge * 0.6);
	// eslint-disable-next-line unicorn/no-useless-undefined
	return new Promise((resolve, reject) => store.pruneSessions(/** @param {Error} err */ err => { err ? reject(err) : resolve(undefined); }));
	})
	.then(() => queryPromise('SELECT COUNT(sid) FROM session'))
	.should.eventually.have.nested.property('rows[0].count', '2')
	.then(() => agent.get('/').expect(200))
	.then(() => {
	clock.tick(maxAge * 0.6);
	// eslint-disable-next-line unicorn/no-useless-undefined
	return new Promise((resolve, reject) => store.pruneSessions(/** @param {Error} err */ err => { err ? reject(err) : resolve(undefined); }));
	})
	.then(() => queryPromise('SELECT COUNT(sid) FROM session'))
	.should.eventually.have.nested.property('rows[0].count', '1');
	});

[EvgeniyRRU]

@voxpelli nevermind. It seems, we have a buggy custom tune of our database. I'm not sure whether it custom implicit type casting or overload to_timestamp function. So, it think, you can close this issue. Sorry to bother you.

[voxpelli]

No worries @EvgeniyRRU 🙏