Bug: GlareDB does not work with SuperDuperDB (postgres protocol)

[talagluck]

Description

I'm trying to connect SuperDuperDB to GlareDB Cloud using a posgres connection, and I get the below error.

The command that raises this is

from superduperdb import superduper

db = superduper(<GLAREDB_POSTGRES_CONNECTION_STRING>)

 2024-May-22 16:35:43.37| INFO     | Tals-MacBook-Pro.local| fa93d15b-680e-4a68-a398-3981e6557c64| superduperdb.base.build:61   | Data Client is ready. <ibis.backends.postgres.Backend object at 0x17573add0>
 2024-May-22 16:35:43.40| INFO     | Tals-MacBook-Pro.local| fa93d15b-680e-4a68-a398-3981e6557c64| superduperdb.base.build:36   | Connecting to Metadata Client with engine:  <ibis.backends.postgres.Backend object at 0x17573add0>
 2024-May-22 16:36:00.29| WARNING  | Tals-MacBook-Pro.local| fa93d15b-680e-4a68-a398-3981e6557c64| superduperdb.base.build:41   | Error building metadata from DataBackend: (psycopg2.errors.SyntaxError) sql parser error: Expected identifier, found: 255

[SQL: 
CREATE TABLE meta (
	key VARCHAR(255) NOT NULL, 
	value VARCHAR(255), 
	PRIMARY KEY (key)
)

]
(Background on this error at: https://sqlalche.me/e/14/f405)
 2024-May-22 16:36:00.30| INFO     | Tals-MacBook-Pro.local| fa93d15b-680e-4a68-a398-3981e6557c64| superduperdb.base.build:47   | Connecting to Metadata Client with URI:  postgresql://5dlVK8XLaVJp:PASSWORD@o_PRocU0j.proxy.glaredb.com:6543/rough_glitter

 2024-May-22 16:36:06.17| ERROR    | Tals-MacBook-Pro.local| fa93d15b-680e-4a68-a398-3981e6557c64| superduperdb.base.build:51   | Error initializing to Metadata Client: (psycopg2.errors.SyntaxError) sql parser error: Expected identifier, found: 255

[SQL: 
CREATE TABLE meta (
	key VARCHAR(255) NOT NULL, 
	value VARCHAR(255), 
	PRIMARY KEY (key)
)

]
(Background on this error at: https://sqlalche.me/e/14/f405)

---------------------------------------------------------------------------
SyntaxError                               Traceback (most recent call last)
File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1910, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1909     if not evt_handled:
-> 1910         self.dialect.do_execute(
   1911             cursor, statement, parameters, context
   1912         )
   1914 if self._has_events or self.engine._has_events:

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/engine/default.py:736, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736     cursor.execute(statement, parameters)

SyntaxError: sql parser error: Expected identifier, found: 255


The above exception was the direct cause of the following exception:

ProgrammingError                          Traceback (most recent call last)
File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/superduperdb/base/build.py:48, in build_metadata(cfg, databackend)
     47     logging.info("Connecting to Metadata Client with URI: ", cfg.data_backend)
---> 48     return build(cfg.data_backend, metadata_stores, type='metadata')
     49 except Exception as e:
     50     # Exit quickly if a connection fails.

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/superduperdb/base/build.py:140, in build(uri, mapping, type)
    139 sql_conn = create_engine(uri)
--> 140 return mapping['sqlalchemy'](sql_conn, name)

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/superduperdb/backends/sqlalchemy/metadata.py:44, in SQLAlchemyMetadata.__init__(self, conn, name)
     43 self.dialect = conn.dialect.name
---> 44 self._init_tables()
     46 self._lock = threading.Lock()

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/superduperdb/backends/sqlalchemy/metadata.py:124, in SQLAlchemyMetadata._init_tables(self)
    117 self.meta_table = Table(
    118     'meta',
    119     metadata,
   (...)
    122     *meta_table_args,
    123 )
--> 124 metadata.create_all(self.conn)

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:4930, in MetaData.create_all(self, bind, tables, checkfirst)
   4929     bind = _bind_or_error(self)
-> 4930 bind._run_ddl_visitor(
   4931     ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
   4932 )

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3238, in Engine._run_ddl_visitor(self, visitorcallable, element, **kwargs)
   3237 with self.begin() as conn:
-> 3238     conn._run_ddl_visitor(visitorcallable, element, **kwargs)

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/engine/base.py:2221, in Connection._run_ddl_visitor(self, visitorcallable, element, **kwargs)
   2215 """run a DDL visitor.
   2216 
   2217 This method is only here so that the MockConnection can change the
   2218 options given to the visitor so that "checkfirst" is skipped.
   2219 
   2220 """
-> 2221 visitorcallable(self.dialect, self, **kwargs).traverse_single(element)

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py:524, in ExternalTraversal.traverse_single(self, obj, **kw)
    523 if meth:
--> 524     return meth(obj, **kw)

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py:855, in SchemaGenerator.visit_metadata(self, metadata)
    854 if table is not None:
--> 855     self.traverse_single(
    856         table,
    857         create_ok=True,
    858         include_foreign_key_constraints=fkcs,
    859         _is_metadata_operation=True,
    860     )
    861 else:

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py:524, in ExternalTraversal.traverse_single(self, obj, **kw)
    523 if meth:
--> 524     return meth(obj, **kw)

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py:899, in SchemaGenerator.visit_table(self, table, create_ok, include_foreign_key_constraints, _is_metadata_operation)
    897     include_foreign_key_constraints = None
--> 899 self.connection.execute(
    900     # fmt: off
    901     CreateTable(
    902         table,
    903         include_foreign_key_constraints=  # noqa
    904             include_foreign_key_constraints,  # noqa
    905     )
    906     # fmt: on
    907 )
    909 if hasattr(table, "indexes"):

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1385, in Connection.execute(self, statement, *multiparams, **params)
   1384 else:
-> 1385     return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py:80, in DDLElement._execute_on_connection(self, connection, multiparams, params, execution_options)
     77 def _execute_on_connection(
     78     self, connection, multiparams, params, execution_options
     79 ):
---> 80     return connection._execute_ddl(
     81         self, multiparams, params, execution_options
     82     )

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1477, in Connection._execute_ddl(self, ddl, multiparams, params, execution_options)
   1474 compiled = ddl.compile(
   1475     dialect=dialect, schema_translate_map=schema_translate_map
   1476 )
-> 1477 ret = self._execute_context(
   1478     dialect,
   1479     dialect.execution_ctx_cls._init_ddl,
   1480     compiled,
   1481     None,
   1482     execution_options,
   1483     compiled,
   1484 )
   1485 if self._has_events or self.engine._has_events:

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1953, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1952 except BaseException as e:
-> 1953     self._handle_dbapi_exception(
   1954         e, statement, parameters, cursor, context
   1955     )
   1957 return result

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/engine/base.py:2134, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   2133 elif should_wrap:
-> 2134     util.raise_(
   2135         sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2136     )
   2137 else:

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/util/compat.py:211, in raise_(***failed resolving arguments***)
    210 try:
--> 211     raise exception
    212 finally:
    213     # credit to
    214     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    215     # as the __traceback__ object creates a cycle

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1910, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1909     if not evt_handled:
-> 1910         self.dialect.do_execute(
   1911             cursor, statement, parameters, context
   1912         )
   1914 if self._has_events or self.engine._has_events:

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/sqlalchemy/engine/default.py:736, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736     cursor.execute(statement, parameters)

ProgrammingError: (psycopg2.errors.SyntaxError) sql parser error: Expected identifier, found: 255

[SQL: 
CREATE TABLE meta (
	key VARCHAR(255) NOT NULL, 
	value VARCHAR(255), 
	PRIMARY KEY (key)
)

]
(Background on this error at: https://sqlalche.me/e/14/f405)

During handling of the above exception, another exception occurred:

SystemExit                                Traceback (most recent call last)
    [... skipping hidden 1 frame]

Cell In[2], line 1
----> 1 db = superduper('postgresql://5dlVK8XLaVJp:PASSWORD@o_PRocU0j.proxy.glaredb.com:6543/rough_glitter')

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/superduperdb/base/superduper.py:21, in superduper(item, **kwargs)
     20 if isinstance(item, str):
---> 21     return _auto_identify_connection_string(item, **kwargs)
     23 return _DuckTyper.run(item, **kwargs)

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/superduperdb/base/superduper.py:46, in _auto_identify_connection_string(item, **kwargs)
     45     CFG.force_set('data_backend', item)
---> 46 return build_datalayer(CFG, **kwargs)

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/superduperdb/base/build.py:191, in build_datalayer(cfg, databackend, **kwargs)
    190 # Build metadata store
--> 191 metadata = build_metadata(cfg, databackend)
    192 assert metadata

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/superduperdb/base/build.py:52, in build_metadata(cfg, databackend)
     51 logging.error("Error initializing to Metadata Client:", str(e))
---> 52 sys.exit(1)

SystemExit: 1

During handling of the above exception, another exception occurred:

AttributeError                            Traceback (most recent call last)
    [... skipping hidden 1 frame]

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/IPython/core/interactiveshell.py:2145, in InteractiveShell.showtraceback(self, exc_tuple, filename, tb_offset, exception_only, running_compiled_code)
   2142 if exception_only:
   2143     stb = ['An exception has occurred, use %tb to see '
   2144            'the full traceback.\n']
-> 2145     stb.extend(self.InteractiveTB.get_exception_only(etype,
   2146                                                      value))
   2147 else:
   2149     def contains_exceptiongroup(val):

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/IPython/core/ultratb.py:710, in ListTB.get_exception_only(self, etype, value)
    702 def get_exception_only(self, etype, value):
    703     """Only print the exception type and message, without a traceback.
    704 
    705     Parameters
   (...)
    708     value : exception value
    709     """
--> 710     return ListTB.structured_traceback(self, etype, value)

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/IPython/core/ultratb.py:568, in ListTB.structured_traceback(self, etype, evalue, etb, tb_offset, context)
    565     chained_exc_ids.add(id(exception[1]))
    566     chained_exceptions_tb_offset = 0
    567     out_list = (
--> 568         self.structured_traceback(
    569             etype,
    570             evalue,
    571             (etb, chained_exc_ids),  # type: ignore
    572             chained_exceptions_tb_offset,
    573             context,
    574         )
    575         + chained_exception_message
    576         + out_list)
    578 return out_list

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/IPython/core/ultratb.py:1454, in AutoFormattedTB.structured_traceback(self, etype, evalue, etb, tb_offset, number_of_lines_of_context)
   1452 else:
   1453     self.tb = etb
-> 1454 return FormattedTB.structured_traceback(
   1455     self, etype, evalue, etb, tb_offset, number_of_lines_of_context
   1456 )

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/IPython/core/ultratb.py:1345, in FormattedTB.structured_traceback(self, etype, value, tb, tb_offset, number_of_lines_of_context)
   1342 mode = self.mode
   1343 if mode in self.verbose_modes:
   1344     # Verbose modes need a full traceback
-> 1345     return VerboseTB.structured_traceback(
   1346         self, etype, value, tb, tb_offset, number_of_lines_of_context
   1347     )
   1348 elif mode == 'Minimal':
   1349     return ListTB.get_exception_only(self, etype, value)

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/IPython/core/ultratb.py:1192, in VerboseTB.structured_traceback(self, etype, evalue, etb, tb_offset, number_of_lines_of_context)
   1183 def structured_traceback(
   1184     self,
   1185     etype: type,
   (...)
   1189     number_of_lines_of_context: int = 5,
   1190 ):
   1191     """Return a nice text document describing the traceback."""
-> 1192     formatted_exception = self.format_exception_as_a_whole(etype, evalue, etb, number_of_lines_of_context,
   1193                                                            tb_offset)
   1195     colors = self.Colors  # just a shorthand + quicker name lookup
   1196     colorsnormal = colors.Normal  # used a lot

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/IPython/core/ultratb.py:1082, in VerboseTB.format_exception_as_a_whole(self, etype, evalue, etb, number_of_lines_of_context, tb_offset)
   1079 assert isinstance(tb_offset, int)
   1080 head = self.prepare_header(str(etype), self.long_header)
   1081 records = (
-> 1082     self.get_records(etb, number_of_lines_of_context, tb_offset) if etb else []
   1083 )
   1085 frames = []
   1086 skipped = 0

File /opt/homebrew/anaconda3/envs/superduperdb/lib/python3.10/site-packages/IPython/core/ultratb.py:1150, in VerboseTB.get_records(self, etb, number_of_lines_of_context, tb_offset)
   1148 while cf is not None:
   1149     try:
-> 1150         mod = inspect.getmodule(cf.tb_frame)
   1151         if mod is not None:
   1152             mod_name = mod.__name__

AttributeError: 'tuple' object has no attribute 'tb_frame'

The issues appear to be that VARCHAR fields of fixed length are not currently supported with GlareDB, and also that it is trying to add a primary key, and indexes are not currently supported.