Postgres fails to create empty tables

[AlexJoz]

Cannot init Postgres+psycopg2 database, the codes are basically from docs:

init_engine_and_session_factory(url=db_url)
engine = get_engine()
create_all_tables(engine)

Used latest official docker for Postgres (https://hub.docker.com/_/postgres).

My error:

ProgrammingError: (psycopg2.errors.SyntaxError) type modifier is not allowed for type "text"
LINE 4:  data_json TEXT(4294967295) NOT NULL, 
                   ^

[SQL: 
CREATE TABLE data_v2 (
	id SERIAL NOT NULL, 
	data_json TEXT(4294967295) NOT NULL, 
	description VARCHAR(255), 
	experiment_id INTEGER, 
	time_created BIGINT NOT NULL, 
	trial_index INTEGER, 
	generation_strategy_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(experiment_id) REFERENCES experiment_v2 (id), 
	FOREIGN KEY(generation_strategy_id) REFERENCES generation_strategy (id)
)

]

Looks like Text doesn't accept a length argument with PostgreSQL.

Ax/ax/storage/sqa_store/sqa_classes.py

Line 238 in eb707b1

data_json: str = Column(Text(LONGTEXT_BYTES), nullable=False)

However, same works perfect with with SQLite.

[Jakepodell]

Hi AlexJoz,

It looks like TEXT is of unlimited length with PostgreSQL, have you tried not including the length argument?

[AlexJoz]

Hi AlexJoz,

It looks like TEXT is of unlimited length with PostgreSQL, have you tried not including the length argument?

No, I haven't changed Ax source code yet

[Jakepodell]

okay, we're looking into this right now and will have an answer for you shortly!

[Jakepodell]

So unfortunately this is a known issue with SQLAlchemy sqlalchemy/sqlalchemy#4443. We're looking into a fix but it will likely take some time before we have Postgres compatibility :/. In the meantime, your options would be to move away from Postgres or to do a local checkout and modify the source code.

Let me know if there is anything else we can help with!

[ldworkin]

Thanks @Jakepodell !

Actually gonna keep this open, since hopefully we can fix and I want to keep the issue here as a reminder.

[lucapertile]

It is probably worth mentioning for whoever is bumping into this that another option to make Ax work on Postgres without touching the code locally or switching DB backend altogether, as suggested in sqlalchemy/sqlalchemy#4443 mentioned above by @Jakepodell, is to alter the sqlalchemy compilation rules for the postgres dialect for the TEXT type, directly on client side, or on the code that is supposed to do the initial migration. For example this seems to work fine, tables are created on Postgres and the experiments are logged correctly:

from sqlalchemy import Text
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.dialects import postgresql
from ax.storage.sqa_store.db import get_engine, create_all_tables

@compiles(Text, "postgresql")
def postgresql_text(type_, compiler, **kw):
    return "TEXT"

init_engine_and_session_factory(url="postgresql+psycopg2://user:pw@my_host:5432/test_db")

engine = get_engine()
create_all_tables(engine)
...

[ldworkin]

Thank you @lucapertile !

[lena-kashtelyan]

We'll close this issue for now unless it's reported by more users as there seems to be a solid workaround and we have not had a chance to attend to this in a year. Please feel free to follow up or reopen if you are effected by this still!