DuckDB postgres_attach potentially leaving connections open

[vimota]

What happens?

I set up an Expressjs server with a background process to incrementally update my local DuckDB. The server starts up postgres_attach on server startup, but then uses postgres_scanner every 30 seconds to query postgres for any updates.

I began hitting FATAL: remaining connection slots are reserved for non-replication superuser connections and was unable to start up the server again.

To Reproduce

It's tricky to setup a clean project to reproduce this but the basic outline of what I had was:

// Stop the background job when the server is shutting down
process.on('SIGINT', function () {
  clearTimeout(intervalId);
  process.exit();
});

let intervalId;
const performBackgroundIncrementalUpdate = async () => {
  try {
    // perform background job here
    await db.loadIncrementalUpdates();
  } catch (err) {
    log(err);
  }
  intervalId = setTimeout(performBackgroundIncrementalUpdate, db.MIN_TIME_TO_REFRESH);
};

app.listen(
  port,
  () => {
   await db.run("INSTALL postgres_scanner;");
    await db.run("LOAD postgres_scanner;");
    const dbUrl = process.env.DATABASE_URL;
    await db.run(
      `CALL postgres_attach('${dbUrl}', overwrite=true, filter_pushdown=true);`,
    );
    intervalId = setTimeout(performBackgroundIncrementalUpdate, db.MIN_TIME_TO_REFRESH);
    log(`Analytics service listening on port ${port}!`)
  },
);

Where loadIncrementalUpdates used postgres_scan. After leaving this running a few minutes (and killing and restarting the server), I began hitting the connection error.

Also I found a bunch of connections open in postgres like this:

FROM pg_stat_activity 
WHERE pid <> pg_backend_pid()
AND datname = 'postgres' and usename = 'postgres' and state = 'idle in transaction';

which I killed with pg_terminate_backend(pid).

Removing the postgres_attach (and only using the scan) seems to work now, it consistently queries postgres every 30 sec without the error! It's possible attach is not closing things properly, or it interacts with postgres_scan poorly?

OS:

macOS 12.4

PostgreSQL Version:

psql (14.5 (Homebrew))

DuckDB Version:

v0.6.1

DuckDB Client:

Nodejs

Full Name:

Victor Mota

Affiliation:

PinchedIO

Have you tried this on the latest master branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[vimota]

Sorry if it's too hard to repro / not helpful - feel free to close. I'm posting here for posterity (from the Discord thread: https://discord.com/channels/909674491309850675/921125471905787944/1066941090571243560 ) and in case others come across something similar.

[Mytherin]

Thanks for the report! I've attempted to but have not managed to reproduce the issue. Feel free to re-open if you can reproduce this consistently.