<commit_before>#########################################################################
# This file is part of CoCalc: Copyright © 2020 Sagemath, Inc.
# License: AGPLv3 s.t. "Commons Clause" – see LICENSE.md for details
#########################################################################

# PostgreSQL -- basic queries and database interface

exports.DEBUG = true

# If database conection is non-responsive but no error raised directly
# by db client, then we will know and fix, rather than just sitting there...
DEFAULT_TIMEOUS_MS = 60000

# Do not test for non-responsiveness until a while after initial connection
# established, since things tend to work initially, *but* may also be much
# slower, due to tons of clients simultaneously connecting to DB.
DEFAULT_TIMEOUT_DELAY_MS = DEFAULT_TIMEOUS_MS * 4

QUERY_ALERT_THRESH_MS=5000

EventEmitter = require('events')

fs      = require('fs')
async   = require('async')
escapeString = require('sql-string-escape')
validator = require('validator')
{callback2} = require('smc-util/async-utils')

pg      = require('pg').native    # You might have to do: "apt-get install libpq5 libpq-dev"
if not pg?
    throw Error("YOU MUST INSTALL the pg-native npm module")
# You can uncommment this to use the pure javascript driver.
#  However: (1) it can be 5x slower or more!
#           (2) I think it corrupts something somehow in a subtle way, since our whole
#               syncstring system was breaking... until I switched to native.  Not sure.
#pg      = require('pg')

winston      = require('./winston-metrics').get_logger('postgres')

misc_node = require('smc-util-node/misc_node')

{defaults} = misc = require('smc-util/misc')
required = defaults.required

{SCHEMA, client_db} = require('smc-util/schema')

exports.PUBLIC_PROJECT_COLUMNS = ['project_id',  'last_edited', 'title', 'description', 'deleted',  'created', 'env']
exports.PROJECT_COLUMNS = ['users'].concat(exports.PUBLIC_PROJECT_COLUMNS)

{read_db_password_from_disk} = require('./utils')

class exports.PostgreSQL extends EventEmitter    # emits a 'connect' event whenever we successfully connect to the database and 'disconnect' when connection to postgres fails
    constructor: (opts) ->
        super()
        opts = defaults opts,
            host            : process.env['PGHOST'] ? 'localhost'    # or 'hostname:port' or 'host1,host2,...' (multiple hosts) -- TODO -- :port only works for one host.
            database        : process.env['SMC_DB'] ? 'smc'
            user            : process.env['PGUSER'] ? 'smc'
            debug           : exports.DEBUG
            connect         : true
            password        : <PASSWORD>
            pool            : undefined   # IGNORED for now.
            cache_expiry    : 5000  # expire cached queries after this many milliseconds
                                    # keep this very short; it's just meant to reduce impact of a bunch of
                                    # identical permission checks in a single user query.
            cache_size      : 300   # cache this many queries; use @_query(cache:true, ...) to cache result
            concurrent_warn : 500
            concurrent_heavily_loaded : 100 # when concurrent hits this, consider load "heavy"; this changes home some queries behave to be faster but provide less info
            ensure_exists   : true  # ensure database exists on startup (runs psql in a shell)
            timeout_ms      : DEFAULT_TIMEOUS_MS # **IMPORTANT: if *any* query takes this long, entire connection is terminated and recreated!**
            timeout_delay_ms : DEFAULT_TIMEOUT_DELAY_MS # Only reconnect on timeout this many ms after connect.  Motivation: on initial startup queries may take much longer due to competition with other clients.
        @setMaxListeners(10000)  # because of a potentially large number of changefeeds
        @_state = 'init'
        @_debug = opts.debug
        @_timeout_ms = opts.timeout_ms
        @_timeout_delay_ms = opts.timeout_delay_ms
        @_ensure_exists = opts.ensure_exists
        @_init_test_query()
        dbg = @_dbg("constructor")  # must be after setting @_debug above
        dbg(opts)
        i = opts.host.indexOf(':')
        if i != -1
            @_host = opts.host.slice(0, i)
            @_port = parseInt(opts.host.slice(i+1))
        else
            @_host = opts.host
            @_port = 5432
        @_concurrent_warn = opts.concurrent_warn
        @_concurrent_heavily_loaded = opts.concurrent_heavily_loaded
        @_user = opts.user
        @_database = opts.database
        @_password = opts.password ? read_db_password_from_disk()
        @_init_metrics()

        if opts.cache_expiry and opts.cache_size
            @_query_cache = (new require('expiring-lru-cache'))(size:opts.cache_size, expiry: opts.cache_expiry)
        if opts.connect
            @connect()  # start trying to connect
<commit_msg>define heavily loaded slightly more aggressively<commit_after>#########################################################################
# This file is part of CoCalc: Copyright © 2020 Sagemath, Inc.
# License: AGPLv3 s.t. "Commons Clause" – see LICENSE.md for details
#########################################################################

# PostgreSQL -- basic queries and database interface

exports.DEBUG = true

# If database conection is non-responsive but no error raised directly
# by db client, then we will know and fix, rather than just sitting there...
DEFAULT_TIMEOUS_MS = 60000

# Do not test for non-responsiveness until a while after initial connection
# established, since things tend to work initially, *but* may also be much
# slower, due to tons of clients simultaneously connecting to DB.
DEFAULT_TIMEOUT_DELAY_MS = DEFAULT_TIMEOUS_MS * 4

QUERY_ALERT_THRESH_MS=5000

EventEmitter = require('events')

fs      = require('fs')
async   = require('async')
escapeString = require('sql-string-escape')
validator = require('validator')
{callback2} = require('smc-util/async-utils')

pg      = require('pg').native    # You might have to do: "apt-get install libpq5 libpq-dev"
if not pg?
    throw Error("YOU MUST INSTALL the pg-native npm module")
# You can uncommment this to use the pure javascript driver.
#  However: (1) it can be 5x slower or more!
#           (2) I think it corrupts something somehow in a subtle way, since our whole
#               syncstring system was breaking... until I switched to native.  Not sure.
#pg      = require('pg')

winston      = require('./winston-metrics').get_logger('postgres')

misc_node = require('smc-util-node/misc_node')

{defaults} = misc = require('smc-util/misc')
required = defaults.required

{SCHEMA, client_db} = require('smc-util/schema')

exports.PUBLIC_PROJECT_COLUMNS = ['project_id',  'last_edited', 'title', 'description', 'deleted',  'created', 'env']
exports.PROJECT_COLUMNS = ['users'].concat(exports.PUBLIC_PROJECT_COLUMNS)

{read_db_password_from_disk} = require('./utils')

class exports.PostgreSQL extends EventEmitter    # emits a 'connect' event whenever we successfully connect to the database and 'disconnect' when connection to postgres fails
    constructor: (opts) ->
        super()
        opts = defaults opts,
            host            : process.env['PGHOST'] ? 'localhost'    # or 'hostname:port' or 'host1,host2,...' (multiple hosts) -- TODO -- :port only works for one host.
            database        : process.env['SMC_DB'] ? 'smc'
            user            : process.env['PGUSER'] ? 'smc'
            debug           : exports.DEBUG
            connect         : true
            password        : <PASSWORD>
            pool            : undefined   # IGNORED for now.
            cache_expiry    : 5000  # expire cached queries after this many milliseconds
                                    # keep this very short; it's just meant to reduce impact of a bunch of
                                    # identical permission checks in a single user query.
            cache_size      : 300   # cache this many queries; use @_query(cache:true, ...) to cache result
            concurrent_warn : 500
            concurrent_heavily_loaded : 70 # when concurrent hits this, consider load "heavy"; this changes home some queries behave to be faster but provide less info
            ensure_exists   : true  # ensure database exists on startup (runs psql in a shell)
            timeout_ms      : DEFAULT_TIMEOUS_MS # **IMPORTANT: if *any* query takes this long, entire connection is terminated and recreated!**
            timeout_delay_ms : DEFAULT_TIMEOUT_DELAY_MS # Only reconnect on timeout this many ms after connect.  Motivation: on initial startup queries may take much longer due to competition with other clients.
        @setMaxListeners(10000)  # because of a potentially large number of changefeeds
        @_state = 'init'
        @_debug = opts.debug
        @_timeout_ms = opts.timeout_ms
        @_timeout_delay_ms = opts.timeout_delay_ms
        @_ensure_exists = opts.ensure_exists
        @_init_test_query()
        dbg = @_dbg("constructor")  # must be after setting @_debug above
        dbg(opts)
        i = opts.host.indexOf(':')
        if i != -1
            @_host = opts.host.slice(0, i)
            @_port = parseInt(opts.host.slice(i+1))
        else
            @_host = opts.host
            @_port = 5432
        @_concurrent_warn = opts.concurrent_warn
        @_concurrent_heavily_loaded = opts.concurrent_heavily_loaded
        @_user = opts.user
        @_database = opts.database
        @_password = opts.password ? read_db_password_from_disk()
        @_init_metrics()

        if opts.cache_expiry and opts.cache_size
            @_query_cache = (new require('expiring-lru-cache'))(size:opts.cache_size, expiry: opts.cache_expiry)
        if opts.connect
            @connect()  # start trying to connect

{'fraction_non_alphanumeric': 0.1171700790593959, 'fraction_numerical': 0.009426312588688425, 'mean_word_length': 2.80965250965251, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2865386', 'n_tokens_mistral': 2806, 'n_tokens_neox': 2671, 'n_words': 903}