porsager / postgres

Postgres.js - The Fastest full featured PostgreSQL client for Node.js, Deno, Bun and CloudFlare
The Unlicense
7.51k stars 275 forks source link

Error with type 109 #430

Closed nmime closed 1 year ago

nmime commented 2 years ago

PostgresError: expected password response, got message type 109 Postgres can work and give it out at some stage What could it be?

image

porsager commented 2 years ago

Interesting 😊 Could you supply some more details please?

nmime commented 2 years ago

I don't have more details.

I do about 10 selects and updates per second. and get it

porsager commented 2 years ago

Connection details, Postgres.js version, PostgreSQL version, when does it happen, etc etc.

nmime commented 2 years ago

connection details postgres(URI) PostgreSQL 14.4 Postgres.js 3.2.4 when i use postgres (select, update) and not always 😕 last time it was on query:

select count(*) from nfts
where collection = ADDRESS
porsager commented 2 years ago

Ok good info - so everything works except sometimes you receive this error at random. Do you connect directly to a Postgres DB or is there something in between, eg pgbouncer?

What connection options do you run with?

nmime commented 2 years ago

Directly, I did not install anything other than postgresql itself.

porsager commented 2 years ago

Do you have anything in you DB logs when this happens?

porsager commented 2 years ago

Would love to check this out if you can create a reproducible sample. Let me know if that's possible and reopen if you're still experiencing this issue.

Lesiuk commented 1 year ago

@NMIFUN @porsager I have the same issue.

Lesiuk commented 1 year ago
image

On my side It's not random. It always happens when I am doing the same query in parallel (multiple queries at the same time) .

Lesiuk commented 1 year ago

@porsager

deno 1.29.2, postgresql 11.7 run it few times, and you will get those errors

import { parallel, range } from 'npm:radash';
import postgres from 'https://deno.land/x/postgresjs/mod.js'

const sql = postgres('YOUR_URL');

await parallel(10, [...range(0, 100)], async () => {
    await sql`select 1;`; // select * from table is even better at reproducing it
})
image
Lesiuk commented 1 year ago

It works fine when connecting through pgbouncer.

rlopzc commented 1 year ago

Hello @Lesiuk did you find a fix without pgouncer? We are having this same error in prod

rlopzc commented 1 year ago

This are the logs for my error. 5 connections in parallel in the same second. @porsager could you reproduce with @Lesiuk provided code?

2023-04-14 11:19:46 UTC:42.982.123.5(6023):[unknown]@[unknown]:[12477]:LOG:  connection received: host=42.982.123.5 port=6023
2023-04-14 11:19:46 UTC:42.982.123.5(25172):[unknown]@[unknown]:[12478]:LOG:  connection received: host=42.982.123.5 port=25172
2023-04-14 11:19:46 UTC:42.982.123.5(20577):[unknown]@[unknown]:[12479]:LOG:  connection received: host=42.982.123.5 port=20577
2023-04-14 11:19:46 UTC:42.982.123.5(42886):[unknown]@[unknown]:[12481]:LOG:  connection received: host=42.982.123.5 port=42886
2023-04-14 11:19:46 UTC:42.982.123.5(51602):[unknown]@[unknown]:[12480]:LOG:  connection received: host=42.982.123.5 port=51602
2023-04-14 11:19:46 UTC:42.982.123.5(6023):user@test_db:[12477]:LOG:  connection authorized: user=user database=test_db application_name=postgres.js
2023-04-14 11:19:46 UTC:42.982.123.5(25172):user@test_db:[12478]:LOG:  connection authorized: user=user database=test_db application_name=postgres.js
2023-04-14 11:19:46 UTC:42.982.123.5(20577):user@test_db:[12479]:LOG:  connection authorized: user=user database=test_db application_name=postgres.js
2023-04-14 11:19:46 UTC:42.982.123.5(42886):user@test_db:[12481]:LOG:  connection authorized: user=user database=test_db application_name=postgres.js
2023-04-14 11:19:46 UTC:42.982.123.5(51602):user@test_db:[12480]:FATAL:  expected password response, got message type 109
porsager commented 1 year ago

Perhaps the error message is a red herring, and it is because of reaching a connection limit, and Postgres.js is missing integration for that specific case. I'll see if I can replicate something like that on my end.

porsager commented 1 year ago

Hmm.. no, that correctly errors with PostgresError: sorry, too many clients already

porsager commented 1 year ago

Sorry - I haven't had time to look at issues, but going through them now and testing this, I am unable replicate, also with the code above and same postgres + deno versions.

If anyone is up for making a completely reproducible setup I'd love to look into it, but until then I'm gonna close this out.

masfahru commented 1 year ago

Sorry - I haven't had time to look at issues, but going through them now and testing this, I am unable replicate, also with the code above and same postgres + deno versions.

If anyone is up for making a completely reproducible setup I'd love to look into it, but until then I'm gonna close this out.

Hi @porsager , i got the same error while trying to benchmark bun using techempower benchmark setup.

image

Techempower benchmark setup is using this flags to create Postgres docker image:

ENV POSTGRES_HOST_AUTH_METHOD=md5
ENV POSTGRES_INITDB_ARGS=--auth-host=md5

Steps to reproduce:

  1. Create postgres container using this command:
    docker run --name test-postgres-image -e POSTGRES_PASSWORD=mysecretpassword -e POSTGRES_INITDB_ARGS="--auth-host=md5" -e POSTGRES_HOST_AUTH_METHOD=md5 -p 5432:5432 -d postgres:15-bullseye
  2. Init bun project in a folder using bun init -y (install bun first if needed)
  3. Add postgres using cli bun add postgres
  4. Edit index.ts and change the content to this code
    
    import postgres from 'postgres'

const sql = postgres({ host: 'localhost', user: 'postgres', password: 'mysecretpassword', database: 'postgres', })

const results = await Promise.all([...Array(150).keys()].map(async (id) => { return sql.begin(async sql => sqlselect ${id};) })) console.log(results)

5. Run index.ts using cli `bun run index.ts`

Error: 
![image](https://github.com/porsager/postgres/assets/92277627/f196fa18-3262-4c50-aad7-2e5caba12891)

6. Edit index.ts with this code
```javascript
import postgres from 'postgres'

const sql = postgres({
  host: 'localhost',
  user: 'postgres',
  password: 'mysecretpassword',
  database: 'postgres',
})

const results = await Promise.all([...Array(150).keys()].map(async (id) => {
  return sql`select ${id};`;
}))
console.log(results)
  1. Run index.ts again using cli bun run index.ts Error: image

Hopefully it can be fixed soon, thank you

porsager commented 1 year ago

@masfahru I think point 7 was fixed in https://github.com/porsager/postgres/commit/cae4d9711d5109a794ca53b8ba4ec13305afdb10

About replication the md5 issue, I have no luck. Tried your steps precisely.

porsager commented 1 year ago

Yay ! I think I got it ! Just had to look at the damn code ;) Fix coming up