Running metadata.reflect on my Aurora pgsql DB throws

[mat100payette]

Hello. Not sure yet if this is a bug so please enlighten me. When I run metadata.reflect(bind=engine) on my AWS Aurora PGSQL DB, I get the following cryptic error:

An error occurred: (aurora_data_api.exceptions.DatabaseError) An error occurred (BadRequestException) when calling the ExecuteStatement operation: ERROR: function generate_subscripts(smallint[], bigint) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 568; SQLState: 42883
[SQL: SELECT attr.conrelid, array_agg(CAST(attr.attname AS TEXT) ORDER BY attr.ord) AS cols, attr.conname, min(attr.description) AS description, NULL AS extra
FROM (SELECT con.conrelid AS conrelid, con.conname AS conname, con.conindid AS conindid, con.description AS description, con.ord AS ord, pg_catalog.pg_attribute.attname AS attname
FROM pg_catalog.pg_attribute JOIN (SELECT pg_catalog.pg_constraint.conrelid AS conrelid, pg_catalog.pg_constraint.conname AS conname, pg_catalog.pg_constraint.conindid AS conindid, unnest(pg_catalog.pg_constraint.conkey) AS attnum, generate_subscripts(pg_catalog.pg_constraint.conkey, :generate_subscripts_1) AS ord, pg_catalog.pg_description.description AS description
FROM pg_catalog.pg_constraint LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_constraint.oid      
WHERE pg_catalog.pg_constraint.contype = :contype AND pg_catalog.pg_constraint.conrelid IN (:oids_1, :oids_2, :oids_3, :oids_4, :oids_5, :oids_6, :oids_7, :oids_8, :oids_9, :oids_10, :oids_11, :oids_12, :oids_13, :oids_14, :oids_15, :oids_16, :oids_17)) AS con ON pg_catalog.pg_attribute.attnum = con.attnum AND pg_catalog.pg_attribute.attrelid = con.conrelid
WHERE con.conrelid IN (:oids_1, :oids_2, :oids_3, :oids_4, :oids_5, :oids_6, :oids_7, :oids_8, :oids_9, :oids_10, :oids_11, :oids_12, :oids_13, :oids_14, :oids_15, :oids_16, :oids_17)) AS attr GROUP BY attr.conrelid, attr.conname ORDER BY attr.conrelid, attr.conname]
[parameters: {'generate_subscripts_1': 1, 'contype': 'p', 'oids_1': 1687569, 'oids_2': 20638, 'oids_3': 1220625, 'oids_4': 98913, 'oids_5': 20490, 'oids_6': 20557, 'oids_7': 20542, 'oids_8': 20512, 'oids_9': 565265, 'oids_10': 1237069, 'oids_11': 647185, 'oids_12': 565288, 'oids_13': 98936, 'oids_14': 20572, 'oids_15': 20502, 'oids_16': 20620, 'oids_17': 20595}]

When I track it down, it seems to stem from the get_multi_columns function of sqlalchemy\dialects\postgresql\base.py where that query is built. I'm not sure what I'm doing that could cause this. The sqlalchemy version I'm using is 2.0.29 and my Aurora cluster uses Postgres 13.12.

[zzzeek]

what does your driver URL look like?

[CaselIT]

oh I thought you were using a custom dialect that subclassed the default postgresql one.

[mat100payette]

I'll just drop the two files that matter in here, let me know if there's anything to be done there.
sqlalchemy_dataapi.zip

[mat100payette]

So I found that the generate_subscripts sql function is added in the _constraint_query function of PGDialect (which at runtime is my AuroraPostgresDataAPIDialect object/class). What's a proper way to patch that? Do I copy that _constraint_query function in my AuroraPostgresDataAPIDialect class and do the modifications there or is there a cleaner way to approach that?

[mat100payette]

Sorry for the multiple replies. I've got somewhere with this, enough to have the query run, but I'm not sure if what I've done is optimal or even correct. Here are the changes I applied:

1. Copied the following functions of sqlalchemy.dialects.postgresql.base into my own AuroraPostgresDataAPIDialect class: _constraint_query, _index_query and get_multi_indexes.
2. In _constraint_query, I changed the following:

   sql.func.generate_subscripts(
       pg_catalog.pg_constraint.c.conkey, 1
   ).label("ord"),

   to

   sql.func.generate_subscripts(
       pg_catalog.pg_constraint.c.conkey, sql.cast(1, INTEGER)
   ).label("ord"),

3. In _index_query , I applied the same cast in the generate_subscripts call, plus the following change:

   pg_catalog.pg_get_indexdef(
       idx_sq.c.indexrelid, idx_sq.c.ord + 1, True
   ),

   to

   pg_catalog.pg_get_indexdef(
       idx_sq.c.indexrelid, sql.cast(idx_sq.c.ord + 1, INTEGER), True
   ),

4. Finally in get_multi_indexes, I added this check:

   for col_index, col_flags in enumerate(row["indoption"]):
       col_sorting = ()
       # try to set flags only if they differ from PG
       # defaults...
       try:
           col_flags = int(col_flags)
       except ValueError:
           continue
   
       if col_flags & 0x01:
           ...

   to

   for col_index, col_flags in enumerate(row["indoption"]):
       col_sorting = ()
       # try to set flags only if they differ from PG
       # defaults...
   
       if col_flags & 0x01:
           ...

The casts to INTEGER were done because the 1 was showing up as bigint in the final query but postgresql doesn't seem to have a signature for generate_subscripts and pg_get_indexdef that take in bigint, just int.

The added check was done because col_flags in the debugger was a string (like '0') and sometimes even just a space ' '. I didn't check more in details why and/or if that is normal, but I hope that this can help you guys determine if I should be good with this or if there's something more needed on my and/or your end. 🙏

[CaselIT]

I still think the issue here is in your driver. It's not correct to threat 1 as bigint, considering how sensitive is postgresql.

The change you have done seem fine, feel free to propose ther to the AuroraPostgresDataAPIDialect dialect

[mat100payette]

The only thing I can see would maybe be this in the driver?

_data_api_type_map = {
      bytes: "blobValue",
      bool: "booleanValue",
      float: "doubleValue",
      int: "longValue", # <-----
      str: "stringValue",
      Decimal: "stringValue"
      # list: "arrayValue"
  }

But I don't see anything related to "bigint"

[CaselIT]

you should likely ask for guidance to the AuroraPostgresDataAPIDialect developers

[mat100payette]

That is unfortunately not possible in my case. But if that's not something you guys can help with that's fine, I'll keep chipping at it ^^ Thanks