A database connection manager that handles connections to various database systems including Microsoft SQL Server and PostgreSQL. This class provides connection management, authentication, and automatic reconnection.
Details
The DBConnection_v9 class holds the database connection logic and provides a consistent interface to different database systems. It supports both trusted connections and user/password authentication. It handles connection failures, and it reconnects automatically.
Key features:
Support for multiple database systems (SQL Server, PostgreSQL).
Automatic connection management with retry logic.
Secure credential handling.
Connection status monitoring.
Graceful error handling and recovery.
A connection is never shared with another process.
Fork safety
A connection belongs to the process that opened it. After a fork, the child
holds a copy of this object and a copy of the parent's connection. Both
processes then use one socket. PostgreSQL can return wrong results and
report no error. Measured against the NorSySS server on 2026-08-14. A child
asked for select 4 and read 3. The parent asked for
select 999 and read 2. DBI::dbIsValid() reports TRUE on such a
handle, so nothing else detects it.
This class records the process that opens each connection. It drops any
connection whose recorded process is not the current one.
is_connected() then returns FALSE, connection returns NULL,
and autoconnection opens a new connection for the current process.
disconnect() closes nothing, because the handle belongs to the other
process.
The object never closes an inherited handle, and it keeps a reference to it.
Both parts are needed. A close, by DBI::dbDisconnect() or by the
garbage collector, would close the other process's socket.
PostgreSQL loads
DBTable_v9$insert_data(), $upsert_data() and
insert_data_many send rows to PostgreSQL as JSON text over
this connection. csdb starts no client, so a load makes no new login. One
statement sends at most 500,000 rows, and every statement of one load runs
in one transaction.
csdb finds the failure class in the English error text of the server. Each
new connection warns once when the lc_messages of the server is not
C, POSIX or English. A failed load is csdb_load_ambiguous unless its
ROLLBACK succeeded and the connection then answered SELECT 1. Only
then can a known English text give csdb_load_duplicate_key or
csdb_load_transient. Any other text gives csdb_load_other.
The COMMIT and a broken connection also give csdb_load_ambiguous.
Three more rules apply:
A statement that runs past
load_timeoutstops on the server, which stores no row. The error has classcsdb_load_other.A statement that stores fewer rows than csdb sent stops the load before the commit. The error has class
csdb_load_other, and the server stores no row.A connection that cannot open before the load gives
csdb_load_transient, with the error ofconnect()in its message.
A row with more than 900 MB of JSON text stops the load before csdb sends
it. PostgreSQL refuses a value of 1 GB or more. The error has class
csdb_load_other.
PostgreSQL double precision
The field type DOUBLE creates a double precision column. Until
2026.10.16 it created real, which keeps about 7 significant digits.
DBTable_v9$create_table() changes each real column that a
table declares DOUBLE to double precision with one
ALTER TABLE, and the table keeps its rows. PostgreSQL rewrites the
table and its indexes, and blocks every other access to the table until the
rewrite ends. The ALTER waits at most 60 s for its lock, and then
stops with an error. The table then stays as it was.
See also
The introduction vignette,
vignette("csdb", package = "csdb"), which creates one of these,
connects, and disconnects again.
csdb_set_auth_hook registers the function that
connect() calls after its first failed attempt.
Other database classes:
DBTable_v9
Active bindings
connectionDatabase connection. NULL when another process opened it.
autoconnectionDatabase connection that automatically connects if possible. After a fork it opens a connection for the current process.
Methods
DBConnection_v9$new()
Create a new DBConnection_v9 object.
Usage
DBConnection_v9$new(
driver = NULL,
server = NULL,
port = NULL,
db = NULL,
schema = NULL,
user = NULL,
password = NULL,
trusted_connection = NULL,
sslmode = NULL,
role_create_table = NULL
)DBConnection_v9$is_connected()
Is the DB schema connected?
A connection that another process opened does not count. The method drops that connection first, and then reports FALSE.
DBConnection_v9$connect()
Connect to the database.
The method drops a connection that another process opened, and then opens a connection for the current process.
The method tries again after any error, until it has made
attempts attempts. It waits 2 s before attempt 2 and 10 s
before each later attempt, as a bcp or a JSON load does. Each failed
attempt emits a message with its number, the wait and the error.
DBConnection_v9$disconnect()
Disconnect from the database.
The method closes only a connection that this process opened. A connection that another process opened stays open.
Examples
# Creating the object stores the settings. It opens no connection,
# so this runs without a database server.
db <- DBConnection_v9$new(
driver = "PostgreSQL Unicode",
server = "localhost",
port = 5432,
db = "mydb",
user = "myuser",
password = "mypass"
)
db$is_connected()
#> [1] FALSE
db
#> (disconnected)
#>
#> Driver: PostgreSQL Unicode
#> Server: localhost
#> Port: 5432
#> DB: mydb
#> User: myuser
#> Password: ******
#> SSL mode: x
#>
# \donttest{
# The full cycle, on SQLite. SQLite needs no server, so this block runs
# anywhere. Only the driver and the db argument change for a server.
# vignette("backends", package = "csdb") puts the two configurations
# side by side.
sqlite_db <- DBConnection_v9$new(
driver = "SQLite",
db = tempfile(fileext = ".sqlite")
)
sqlite_db$connect()
sqlite_db$is_connected()
#> [1] TRUE
DBI::dbListTables(sqlite_db$connection)
#> character(0)
sqlite_db$disconnect()
sqlite_db$is_connected()
#> [1] FALSE
# $autoconnection opens the file again, so a read after a disconnect
# still works.
DBI::dbListTables(sqlite_db$autoconnection)
#> character(0)
sqlite_db$disconnect()
# }