Yeah so in the node rather than using the insert / update select Execute Query and manually input the query and see if that works. It could also be worth copying the node out and putting it into a text editor and changing the typeVersion value to 1 then copy / paste back into n8n and see if that works.
Hey @Roket,
We already know about the v2 node so no need to test the upsert again 
The execute query operation will be the best solution for now.
1 Like
Hey @Roket,
We have just put in a fix that should solve this here: fix(Postgres Node): Remove reusable connections (no-changelog) by michael-radency · Pull Request #6259 · n8n-io/n8n · GitHub it should be available either this week or next week depending on how long the review takes.
1 Like
I am receiving a bunch of webhooks and upserting them into a table in Supabase, but it seems that all update operations are failing with the same error. I am in the latest beta version of N8n 0.230.1 .
I am going to try to do it with the query, but it can be painful with so many values to be sent.
@jpm , hey!
I implemented the preprocessing of objects arriving at the postgres node using JS.
You can customize this process template to your liking.
1 Like
Thanks @Rocket! Will try it out.
If you have control of your postgresql server, updating my .conf using these suggestions fixed this for me:
I’m running a small server, product information data only for about 80k skus, so fairly chunky, but only really one system (well, two n8n instances) querying.
I used data-center as a preset which seems stable so far.
2 Likes
I encounter this issue as well. I think that it started to appear when I changed execution mode from “own” to “main” as suggested by documentation. I’m using latest n8n version and I tried to recreate node to use latest postgres node version.
Here’s relevant JSON snippet:
{
"parameters": {
"operation": "update",
"schema": {
"__rl": true,
"mode": "list",
"value": "public"
},
"table": {
"__rl": true,
"value": "patients",
"mode": "list",
"cachedResultName": "patients"
},
"columns": {
"mappingMode": "defineBelow",
"value": {
"id": "={{ $node[\"GetPatient\"].json[\"id\"] }}",
"firstname": "={{$node[\"XML1\"].json[\"data\"][\"GetPersonByFIOIINResponse\"][\"array\"][\"firstName\"]}}",
"lastname": "={{$node[\"XML1\"].json[\"data\"][\"GetPersonByFIOIINResponse\"][\"array\"][\"lastName\"]}}",
"bdate": "={{$node[\"XML1\"].json[\"data\"][\"GetPersonByFIOIINResponse\"][\"array\"][\"birthDate\"]}}",
"region": "Astana",
"gender": "={{$node[\"XML1\"].json[\"data\"][\"GetPersonByFIOIINResponse\"][\"array\"][\"sex\"]}}",
"fullname": "={{$node[\"XML1\"].json[\"data\"][\"GetPersonByFIOIINResponse\"][\"array\"][\"lastName\"]}} {{$node[\"XML1\"].json[\"data\"][\"GetPersonByFIOIINResponse\"][\"array\"][\"firstName\"]}} {{$node[\"XML1\"].json[\"data\"][\"GetPersonByFIOIINResponse\"][\"array\"][\"secondName\"]}}",
"surname": "={{$node[\"XML1\"].json[\"data\"][\"GetPersonByFIOIINResponse\"][\"array\"][\"secondName\"]}}",
"document_type": "IIN"
},
"matchingColumns": [
"id"
],
"schema": [
{
"id": "id",
"displayName": "id",
"required": true,
"defaultMatch": true,
"display": true,
"type": "number",
"canBeUsedToMatch": true,
"removed": false
},
{
"id": "created_at",
"displayName": "created_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": true,
"removed": true
},
{
"id": "iin",
"displayName": "iin",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": true
},
{
"id": "firstname",
"displayName": "firstname",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "lastname",
"displayName": "lastname",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "surname",
"displayName": "surname",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "bdate",
"displayName": "bdate",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": true
},
{
"id": "region",
"displayName": "region",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "gender",
"displayName": "gender",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "phone",
"displayName": "phone",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": true
},
{
"id": "fullname",
"displayName": "fullname",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "email",
"displayName": "email",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": true
},
{
"id": "passport_id",
"displayName": "passport_id",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": true
},
{
"id": "document_type",
"displayName": "document_type",
"required": true,
"defaultMatch": false,
"display": true,
"type": "options",
"canBeUsedToMatch": true,
"options": [
{
"name": "PASSPORT",
"value": "PASSPORT"
},
{
"name": "IIN",
"value": "IIN"
}
]
},
{
"id": "lang",
"displayName": "lang",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": true
}
]
},
"options": {}
},
"id": "c2c1dc10-256b-4206-8c23-48496686d7b3",
"name": "UpdatePatientRPNandPhone1N",
"type": "n8n-nodes-base.postgres",
"typeVersion": 2.2,
"position": [
3060,
440
],
"credentials": {
"postgres": {
"id": "25",
"name": "MedreviewDB_157"
}
}
}
],
and here’s stacktrace:
Workflow: JOB_CheckIIN_V2
URL: https://n8n-test.cmctech.pro/workflow/157/executions/1835055
Node: UpdatePatientRPNandPhone1N
Message: Connection pool of the database object has been destroyed.
Stack: Error: Connection pool of the database object has been destroyed.
at /usr/local/lib/node_modules/n8n/node_modules/pg-promise/lib/connect.js:24:25
at new Promise (<anonymous>)
at Object.promise (/usr/local/lib/node_modules/n8n/node_modules/pg-promise/lib/promise-parser.js:30:20)
at poolConnect (/usr/local/lib/node_modules/n8n/node_modules/pg-promise/lib/connect.js:20:19)
at Object.pool (/usr/local/lib/node_modules/n8n/node_modules/pg-promise/lib/connect.js:176:24)
at Database.query (/usr/local/lib/node_modules/n8n/node_modules/pg-promise/lib/database.js:330:36)
at Database.obj.any (/usr/local/lib/node_modules/n8n/node_modules/pg-promise/lib/database.js:772:30)
at getTableSchema (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Postgres/v2/helpers/utils.js:234:30)
at Object.execute (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Postgres/v2/actions/database/update.operation.js:229:62)
at processTicksAndRejections (node:internal/process/task_queues:95:5)
Hey @vbezhenar,
We are tracking this internally to resolve it. Are you seeing this with lots of items or just a few and are you using the standard postgres docker image or something else?
I don’t think that it’s 100%. Some requests fail, some work. Also I think that database request is actually gets executed, though not 100% sure about it. Data seems to be updated.
We’re using standard docker image postgres:15.3.
1 Like
I’m having the exact same issue as @vbezhenar . It seems to be intermittent. I suspected a DB issue because it seemed to be happening when large numbers of executions come in and n8n tries to handle them all at the same time. I figured this was creating too many connections to the DB and throwing the error, but it sounds like it might be an n8n issue.
Hey @pooria,
Are you getting the same error message or something else? Are you also self hosting supabase or using the cloud version?
Fix can be found in the PR below which will be released soon.
1 Like
cloud based
It may be solved in the PR above or it could be a limitation with the cloud based version of Supabase. This won’t fix every issue but it will fix everything from our side.
1 Like