New Postgresql feature: 0 rows is failure

trygvis · June 24, 2020, 12:13pm

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?

RicardoE105 · June 25, 2020, 2:58pm

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.

trygvis · June 29, 2020, 9:23am

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

trygvis · July 19, 2020, 7:18am

@RicardoE105 Have you had any time to look at this?

RicardoE105 · July 20, 2020, 12:37pm

Hey @trygvis so sorry, somehow I missed your last message. Checking right now.

RicardoE105 · July 20, 2020, 4:31pm

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

How much much is the difference between a job started and a new job? Maybe this can be solve with a small wait to guarantee Job started executes before a new job. Check the example below to learn how to make a wait function.

If this does help simply get back to me.

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "const waitTimeSeconds = 3;\n\nreturn new Promise((resolve) => {\n  setTimeout(() => {\n    resolve();\n  }, waitTimeSeconds * 1000);\n});\n\n"
      },
      "name": "Wait",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        1822,
        480.02532252669334
      ]
    }
  ],
  "connections": {}
}

trygvis · August 4, 2020, 8:36pm

I don’t know how long, shouldn’t be more than a few seconds, but inserting sleep() in the code has never been a good way to solve problems. I would much rather have a deterministic behavior with polling and failure on expiry (too many attempts).

For this particular case I’m sure I can just hard-code a 30 second delay, but it will stop me from other planned usage of n8n.

Is the idea of making it possible to say that 0 rows is a failure is a bad idea?

jan · August 4, 2020, 9:04pm

Then you should be able to simply create a loop. Set the Postgres-Node to “Always Output Data” and then pipe it in a IF-Node. There you check if the number of items is 1 and if it does not have any properties. In this case you can simply create a Function Node which waits a second and then you loop back in the Postgres-Node.

trygvis · August 5, 2020, 9:32am

Ok, I guess that is an option but it still feels like a workaround for something that would be pretty useful in other cases too. For example, if the node is expecting to get one/at least one item but doesn’t the user don’t have to add an If node, check the length and then continue or else fail.

Is there even a way for a workflow to even explicitly fail?