Extend 'capabilities' to be more dynamic?

[myndzi]

Spawned by the discussion here: https://github.com/tgriesser/knex/issues/802

Use case: prepared statements are connection-specific; the capabilities function could be used to request connections from the pool which have a given prepared statement available.

For this to work, a few things are needed:

• "strong" and "weak" capabilities requests: A strong request would return a resource from the pool that can fulfill the capability or wait until one is available; a weak request would return a resource from the pool that can fulfill the capability if available, otherwise return any available resource
• Ability to dynamically manipulate a resource's capabilities, primarily to "upgrade" plain connections to connections that have the query prepared

Also worth considering:

• affinity / anti-affinity?
• pool prioritization? (try to serve allocation requests with the "least-capable" resource, to leave more specialized resources idle as much as possible?)
• potential use case for hooks? (allow client to prepare statements on new resources when they are requested or when they become idle, for example)

Questions:

• Is the additional complexity worth implementing the feature?
• What would the API look like?

[myndzi]

// $ suffix = promisified function
function getFoos(args, cb) {
    var key = 'prepared:1';

    return cluster.acquire$(key, false)
    .tap(function (conn) {
        if (!cluster.hasCapability(conn, key)) {
            return prepareStatement$(conn, key)
            .then(function () {
                cluster.addCapability(conn, key);
            });
        }
    })
    .tap(function (conn) {
        return execStatement$(conn, key, args);
    })
    .finally(function (conn) {
        return cluster.release$(conn);
    });
}

Notes: Promise.using elided for clarity; I tried to write it as callbacks but almost killed myself

[myndzi]

Last thing (I hope!):

Currently 'capabilities' is 1:1 with Pool objects; it's used to select specific Pools among many in a Cluster; the usage discussed here is 1:1 with resource objects and would be used to select specific resources from among a pool or a cluster of pools. This semantic difference could present a problem, but in practice not a very big one: the current syntax can be retained and simply flag every resource within a pool with the specified capabilities when using pools in a cluster, while additional syntax can be added to allow more fine-grained capability manipulation on individual resources.

[taybin]

Any progress on this? I'm interested in knex adding support for PREPARED STATEMENTS and this appears to block that, maybe?

[myndzi]

This was kind of an interest-check at the time, but no, no development is planned. Knex has (unfortunately?) moved away from pool2 to a later version of generic pool due to some ongoing integration confusion that we never really got to the bottom of, so adding it here won't affect recent versions of knex anyway.