Can pgBucket add JOB with SQL alone?

[山下 大介]

@dineshopenscg

Thank you for a wonderful product called pgBucket.

I have a question.

I have confirmed that JOB can be added via the command line, but can only add SQL JOB?

If possible, please let me know how you can do it.

I would like to be able to do JOB addition with database connection and SQL alone.

best regards.

[DineshC]

Hi,

We renamed this project as runseven(Need to change the project title as well). Yes we can add SQL(PostgreSQL Only) Jobs besides OS, API jobs. Refer the below syntax, but please note that this project is still in beta.

// ---------------------------- // Example DB job configuration // ---------------------------- A sample reference for the db related jobs.

$ runc -H <runseven server host address> -p <runseven server port> --create '
{
    "id": 2,
    "name": "test db job",
    "enable": true,
    "frequency": "Day:* Month:* Hour:* Minute:* Second:*/5",
    "run": {
            "kind": "db",
            "type": "job",
            "properties": {
            "query": "select * from pg_stat_activity",
            "connection_uri": "postgresql://postgres:postgres@localhost:5432/postgres",
            "store_columns": true
        }
        },
        "pass_event_id": [34,45],
        "fail_event_id": [11,22],
        "generate_run": false,
        "result_expression": "result != 100",
        "auto_disable_count": 3,
        "complete_flow_result_match": "some result",
      "skip_next_run": false
}'

Here, runc is a dedicated client for the runseven server. For your information, most of the code is in "ha" branch which has enterprise features like auto failover and worker pools, etc. It supports single line command build, by using "cmake" which will setup the binaries for us by downloading all the required additional dependencies.

[山下 大介]

@DineshC

Thank you.

My question is bad and I am sorry.

I want to register job without using the runc command.

I thought if I could register without creating binding etc, Is it possible to register JOB with SQL INSERT etc?

Best regards.

[DineshC]

Hi,

Let me know If I am wrong. We can't register a job by using direct INSERT statement. We can use the curl command OR runc command to register job, because runseven is a server, which accepts the job data as JSON string, and then it will update it's instance like hash, queue.

Let me know if you need any further details.

Regards, Dinesh

[山下 大介]

@dineshopenscg

OK. That means runseven has a web server.

Is also pgbucket2.0 the same?

Could you tell us more about runseven?

[DineshC]

Hi,

pgBucket 2.0 is not web server, and it run in a machine with unix local socket. And, we use a config file to store all the job's related information, and we feed that file to pgbucket server which will parse it and stores the new jobs into database. This was 2 years old working model, and we feel it should be like a web server and created the runseven server.

Run7

In Run7, we can define the flows based on a scheduled job output. That is, after a specific job execution is completed, then we can define the next operation of it, based on its result expression.

Run7 is a job flow system, where we can configure the cascaded actions/events like pgbucket 2.0.

Using Run7, we can configure from a simple single job to a complex job flow, where multiple events includes.

Below are the feature list of Run7 server.

1. It is a cross platform and it works on windows/linux and mac

2. Data consistency is achieved through out the API request. That is, if any process fails during the request process, it will the rollback the previous state of the job.

3. Dedicated client (runc - binary)

4. Standby mode. We can spin an another instance of run7 in standby mode by giving master node details.

5. Auto failover. When master node fails to send it's heartbeat for some interval, then standby will start dispatching the jobs.

6. Expression based flows. Run7 supports the "exprtk" library, where an end user can configure the complex mathematical/string operations of the result and then decide which cascaded action should follow.

7. Supported job types are OS command, DB(PostgreSQL) queries and API jobs(Lambda is in testing).

8. Single command deployment. By using single "cmake" we can configure and make the runseven/runc binaries

9. Supports the cascaded actions

Use Case

NOTE

This video is made with in a high resolution window, so requesting you to scroll the window if you feel the video is stopped. Find the attached asciicinema URL, where I've demonstrated about this tool.

https://asciinema.org/a/TeNkh9SqA1Bx9gO6LWtWJrMgA

In this use case, what I am trying to process the flow is as below

Get the last hour error count from app server
If  app server error count >=50, then check if there are any idle transactions in postgres database
        If idle transaction count >=1, then check if there are any locks in postgres database
            If lock count>=1,
                then kill the idle sessions in postgres database
            else 
                do nothing
            end if
        else
            store the idle transaction count in influxDB
        end if
else
    store the app server count in influxDB
end if

One advantage of storing the data in influxDB is we can generate the Grafana dashboards as well.

You can refer the files what we have used for this demo @"ha" branch in "demo" folder.

Let me know if you need any other details and thanks for showing your interest in runseven