New Postgresql feature: 0 rows is failure

I have workflow that has triggers for the different events that can come from my CI system. I use this to send messages to Slack, but want the entire job to appear as a single thread in Slack. To do this I have to save the timestamp of the “job started” Slack message and re-use that in later events when creating the reply-to Slack message.

The problem for me is that the CI system sends “new job” and “job started” at the same time, and “job started” will often come before “new job”. “new job” is the one that creates the initial Slack message.

I’m storing the timestamp in a Postgresql database on “new job”, and pulling that up for the other events.

What I want (I think) is a way for the select node to return an error if there where not exactly 1 row returned, or maybe just a non-empty set. I can then use the delay/retry feature of the node to make a simple retry method.

Does that sounds like a useful enhancement or are there better ways?

hey @trygvis sorry for the late response. Welcome to the community.

What do you mean exactly with the select node? Do you mean the Postgres node and the select operation?

Can you share the workflow so that I can have a look for a solution? If you do not want to share the workflow here you can send me a private message. I’m happy to help.

Hi

Yes, I meant the PostgreSQL node with select operation.

Here is my workflow:

Workflow
{
  "name": "Statera CI",
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        870,
        500
      ]
    },
    {
      "parameters": {
        "owner": "XXX",
        "repository": "XXX",
        "events": [
          "job"
        ]
      },
      "name": "Gitlab - Job",
      "type": "n8n-nodes-base.gitlabTrigger",
      "typeVersion": 1,
      "position": [
        750,
        950
      ],
      "notesInFlow": true,
      "credentials": {
        "gitlabApi": "Gitlab"
      },
      "notes": "Job"
    },
    {
      "parameters": {
        "owner": "XXX",
        "repository": "XXX",
        "events": [
          "pipeline"
        ]
      },
      "name": "Gitlab - Pipeline",
      "type": "n8n-nodes-base.gitlabTrigger",
      "typeVersion": 1,
      "position": [
        750,
        700
      ],
      "notesInFlow": true,
      "credentials": {
        "gitlabApi": "Gitlab"
      },
      "notes": "Pipeline"
    },
    {
      "parameters": {
        "channel": "=n8n-action",
        "text": "=Pipeline",
        "attachments": [
          {
            "pretext": "=Status: {{ $node[\"Gitlab - Pipeline\"].json[\"body\"].object_attributes.status }}\nDetailed status: {{ $node[\"Gitlab - Pipeline\"].json[\"body\"].object_attributes.detailed_status }}"
          }
        ],
        "otherOptions": {
          "thread_ts": "={{$node[\"Postgres - load ts for pipeline\"].json[\"value\"]}}"
        }
      },
      "name": "Slack - pipeline",
      "type": "n8n-nodes-base.slack",
      "typeVersion": 1,
      "position": [
        1750,
        700
      ],
      "credentials": {
        "slackApi": "n8n bot token"
      }
    },
    {
      "parameters": {
        "channel": "n8n-action",
        "text": "=Job",
        "attachments": [
          {
            "pretext": "={{$node[\"Job text\"].json[\"body\"]}}"
          }
        ],
        "otherOptions": {
          "thread_ts": "={{$node[\"Postgres - load ts\"].json[\"value\"]}}"
        }
      },
      "name": "Slack - job",
      "type": "n8n-nodes-base.slack",
      "typeVersion": 1,
      "position": [
        1750,
        950
      ],
      "credentials": {
        "slackApi": "n8n bot token"
      }
    },
    {
      "parameters": {
        "channel": "=n8n-action",
        "text": "=<{{$node[\"Gitlab - push\"].json[\"body\"][\"project\"][\"homepage\"]}}|{{$node[\"Gitlab - push\"].json[\"body\"][\"project\"][\"name\"]}}> Push with {{$node[\"Gitlab - push\"].json[\"body\"][\"total_commits_count\"]}} commits.",
        "attachments": [
          {}
        ],
        "otherOptions": {}
      },
      "name": "Slack - push",
      "type": "n8n-nodes-base.slack",
      "typeVersion": 1,
      "position": [
        790,
        220
      ],
      "credentials": {
        "slackApi": "n8n bot token"
      }
    },
    {
      "parameters": {
        "owner": "XXX",
        "repository": "XXX",
        "events": [
          "push"
        ]
      },
      "name": "Gitlab - push",
      "type": "n8n-nodes-base.gitlabTrigger",
      "typeVersion": 1,
      "position": [
        600,
        220
      ],
      "notesInFlow": true,
      "credentials": {
        "gitlabApi": "Gitlab"
      },
      "disabled": true,
      "notes": "Push"
    },
    {
      "parameters": {
        "schema": "worker",
        "table": "variable",
        "columns": "workflow,key,value",
        "returnFields": "=*"
      },
      "name": "Postgres - save ts",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        1550,
        450
      ],
      "credentials": {
        "postgres": "n8n-worker"
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=select value from worker.variable where workflow='{{ $workflow.name }}' and key='pipeline-ts/{{$node[\"Pipeline id from job\"].json[\"pipeline_id\"]}}';"
      },
      "name": "Postgres - load ts",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        1350,
        950
      ],
      "retryOnFail": false,
      "credentials": {
        "postgres": "n8n-worker"
      }
    },
    {
      "parameters": {
        "channel": "=n8n-action",
        "text": "=Pipeline started",
        "attachments": [],
        "otherOptions": {}
      },
      "name": "Slack - message",
      "type": "n8n-nodes-base.slack",
      "typeVersion": 1,
      "position": [
        1150,
        450
      ],
      "credentials": {
        "slackApi": "n8n bot token"
      }
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$node[\"Gitlab - Pipeline\"].json[\"body\"][\"object_attributes\"][\"status\"]}}",
              "value2": "pending"
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1150,
        700
      ],
      "notesInFlow": true,
      "notes": "Is new pipeline"
    },
    {
      "parameters": {
        "functionCode": "var item = items[0].json;\n\nvar pipeline_id = $node[\"Gitlab - Pipeline\"].json[\"body\"][\"object_attributes\"][\"id\"]\n\nreturn [{json: {\nworkflow: $workflow.name,\nkey: \"pipeline-ts/\" + pipeline_id,\nvalue: item.ts,\n}}];\n"
      },
      "name": "Function - prepare insert",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        1350,
        450
      ]
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "pipeline_id",
              "value": "={{$node[\"Gitlab - Job\"].json[\"body\"][\"pipeline_id\"]}}"
            }
          ]
        },
        "options": {
          "dotNotation": true
        }
      },
      "name": "Pipeline id from job",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1150,
        950
      ]
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "=pipeline_id",
              "value": "={{$node[\"Gitlab - Pipeline\"].json[\"body\"][\"object_attributes\"][\"id\"]}}"
            }
          ]
        },
        "options": {
          "dotNotation": true
        }
      },
      "name": "Pipeline id from pipeline",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        950,
        700
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=select value from worker.variable where workflow='{{ $workflow.name }}' and key='pipeline-ts/{{$node[\"Pipeline id from pipeline\"].json[\"pipeline_id\"]}}';"
      },
      "name": "Postgres - load ts for pipeline",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        1350,
        700
      ],
      "credentials": {
        "postgres": "n8n-worker"
      }
    },
    {
      "parameters": {
        "functionCode": "var i = items[0].json.body;\n\nvar body = \"Stage: \" + i.build_name + \": \" + i.build_status;\n\nif (i.build_status == \"running\") {\n  body = body + \" :construction_worker:\";\n} else if (i.build_status == \"success\") {\n  body = body + \" :heavy_check_mark:\";\n}\n\nreturn [{json: {body: body, x: JSON.stringify(i)}}];"
      },
      "name": "Job text",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        950,
        950
      ]
    }
  ],
  "connections": {
    "Gitlab - Pipeline": {
      "main": [
        [
          {
            "node": "Pipeline id from pipeline",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Gitlab - push": {
      "main": [
        [
          {
            "node": "Slack - push",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Postgres - load ts": {
      "main": [
        [
          {
            "node": "Slack - job",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Slack - message": {
      "main": [
        [
          {
            "node": "Function - prepare insert",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        [
          {
            "node": "Slack - message",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Postgres - load ts for pipeline",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Postgres - save ts": {
      "main": [
        [
          {
            "node": "Postgres - load ts for pipeline",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function - prepare insert": {
      "main": [
        [
          {
            "node": "Postgres - save ts",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Gitlab - Job": {
      "main": [
        [
          {
            "node": "Job text",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Pipeline id from pipeline": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Pipeline id from job": {
      "main": [
        [
          {
            "node": "Postgres - load ts",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Postgres - load ts for pipeline": {
      "main": [
        [
          {
            "node": "Slack - pipeline",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Job text": {
      "main": [
        [
          {
            "node": "Pipeline id from job",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": true,
  "settings": {},
  "id": "1"
}


Trygve