How is the lifecycle of an ibis connection instance managed?

[augcollet]

We use Ibis quite intensively within a solution that we develop in my company, with a postgresql backend. In particular, we have embedded certain features in a module so that the connection to our database is transparent for developers.

We then use our module every time we need to communicate with our Database.

Sometimes, we encounter problems that are quite complicated to resolve:
[1] We use .cache method on a table under construction and then, later, it is no longer accessible by its instance: "ibis_cached_[...] does not exist".
[2] We push a table into our database via Ibis functions (create_table, insert etc.), but we have a message "[...] does not exist" when we try to read it a little later in the process.

Unfortunately, despite multiple attempts, I have never managed to reproduce one of these problems in a minimal example case. However, when the problem appears during the development of a module, it is always reproducible in this specific situation (I just can't reproduce it in isolation).

Since we often use ibis across several modules, I thought that this kind of problem could occur in the case where we do not rigorously use the same Ibis connection instance everywhere.
Example: In a module A I create an Ibis connection, I cache a table during the execution of a function of the module; this cached table is transmitted to another module B (in which another Ibis connection has been created, therefore another instance), module B is unable to use the cached table because it does not exist for it.
As a solution, I set up a singleton via the syntax that I found here https://stackoverflow.com/a/12305404):

Since then, the problem [1] has not reproduced. However, the problem [2] continues to appear in certain situations. Recently this happened :

• I push a table t with Ibis into a Database, I try to read the table from database later in the same process: Ibis tells me that the table does not exist.
• To get around this, I push the table t with Ibis, but instead of reading it from the database and produce a new instance, I choose to continue using the instance t: my database no longer responds, and I am forced to restart it.

All this is very obscure and I am aware that it will be complicated to give me any help without being able to provide a minimal reproducible example. However, do the behaviors I describe mean anything to you? Do you have a lead to explore? Do you perhaps have some information on the management of the lifecycle of connections via Ibis that could explain this? In particular, how is the cataloging of tables created/deleted during a process managed by Ibis, which could explain why it is sometimes unaware of the existence of tables/caches that it has created itself?

Thank you in advance, any help is welcome.

Regards,

[gforsyth]

Hi @augcollet !

Your singleton approach will resolve the missing cached tables -- for a bit of background there, when you call cache we create a temporary table with the results of the cached expression, but we also attach a finalizer to it that cleans up the table when the session ends.

Postgres' temporary tables are bound to a given Postgres session, so that's why you can't see them in a separate connection instance. If you want tables to persist between sessions and between connections, you need to pass the expression to create_table.

We don't keep track of the (persistent / non-temp) tables that we create -- they're accessible via the information_schema -- we track the temp tables we create so we can clean them up (although with Postgres that's not strictly necessary, due to the above referenced session-related cleanup).

As for the table creation issue -- that's strange behavior and Postgres is (famously) quite robust -- are you inserting huge quantities of data into postgres?

The more detail you can provide, the better chance we have of helping to diagnose the issue.

[augcollet]

Hello,

Thank you very much for your feedback which is already very useful for me to have a better understanding of the underlying mechanisms.

Your explanations for cached tables make sense, this is consistent with what I observe now that I use a singleton.

Concerning the tables not found after being created by Ibis, these are concrete tables, materialized using the con.create_table(t) function.

If I understand correctly, each time Ibis needs to read a table it consults the database catalog in real time, so there is no reason why the table is invisible to Ibis when it does indeed exist (visible from dbeaver).

I will try to add other details if I can observe something that could provide an additional element of response.

Thank you for your help.

[augcollet]

Hello !

I managed to reproduce a stuck database problem after an Ibis table creation, in a rather strange but more isolated scenario. I would be curious to see if anyone manages to reproduce the problem on their side.

I created a very simple notebook with a single cell with the following code:

# Execute this cell multiple times : always OK
# Access table in DB eaver (close tab if already opened, re-open it, and refresh it)
# Re-execute this cell
# >> Database is stuck, try to refresh public schema

import os
import ibis
from ibis import _

table='test'

con=ibis.postgres.connect(
    user=os.getenv('POSTGRES_USER'),
    password=os.getenv('POSTGRES_PASSWORD'),
    host="postgres",
    port=os.getenv('POSTGRES_PORT'),
    database=os.getenv('POSTGRES_DB') 
)
ibis.set_backend(con)

t=ibis.memtable({
    'A':list(range(5)),
})

t=t.mutate(
    B=_['A']+1
)

con.drop_table(table, force=True)
con.create_table(table, obj=t)

con.table(table).execute()

If I run the cell everything works, I can even run it several times in a row and the 'test' table does indeed exist.

Then I go to DBeaver and open the table.

Then, if I re-run the cell, the database is blocked, the cell never ends. And if I try to refresh DBeaver (refresh the schema or the table) it remains blocked.

I would really appreciate your help because I have been stuck for several days with quite incomprehensible behaviors.

Here is my package version :

Name: ibis-framework
Version: 9.5.0
Summary: The portable Python dataframe library
Home-page: https://ibis-project.org/
Author: Ibis Maintainers
Author-email: maintainers@ibis-project.org
License: Apache-2.0
Location: /home/user/.local/lib/python3.12/site-packages
Requires: atpublic, parsy, python-dateutil, pytz, sqlglot, toolz, typing-extensions
Required-by:

And my postgresql version :

PostgreSQL 16.4 (Debian 16.4-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

Thank you for your help !