more than one row returned by a subquery used as an expression

[rtrad89]

Running migra on Linux, Python 3.10:

Package           Version
----------------- --------------
greenlet          3.0.3
migra             3.0.1663481299
packaging         24.1
pip               24.1.2
psycopg2-binary   2.9.9
schemainspect     3.1.1663587362
setuptools        71.0.3
six               1.16.0
SQLAlchemy        2.0.31
sqlbag            0.1.1617247075
typing_extensions 4.12.2

Error:

Traceback (most recent call last):
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.CardinalityViolation: more than one row returned by a subquery used as an expression

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

Traceback (most recent call last):
  File "/home/SomeUser/.migra/bin/migra", line 8, in <module>
    sys.exit(do_command())
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/migra/command.py", line 121, in do_command
    status = run(args)
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/migra/command.py", line 86, in run
    m = Migration(
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/migra/migra.py", line 31, in __init__
    self.changes.i_from = get_inspector(
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/schemainspect/get.py", line 22, in get_inspector
    inspected = ic(c)
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/schemainspect/pg/obj.py", line 1138, in __init__
    super(PostgreSQL, self).__init__(c, include_internal)
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/schemainspect/inspector.py", line 27, in __init__
    self.load_all()
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/schemainspect/pg/obj.py", line 1150, in load_all
    self.load_all_relations()
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/schemainspect/pg/obj.py", line 1394, in load_all_relations
    q = self.execute(self.ALL_RELATIONS_QUERY)
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/schemainspect/pg/obj.py", line 1141, in execute
    result = self.c.execute(*args, **kwargs)
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/home/SomeUser/.migra/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.CardinalityViolation) more than one row returned by a subquery used as an expression

[SQL: with extension_oids as (
  select
      objid
  from
      pg_depend d
  WHERE
      d.refclassid = 'pg_extension'::regclass and
      d.classid = 'pg_class'::regclass
), enums as (

  SELECT
    t.oid as enum_oid,
    n.nspname as "schema",
    t.typname as name
  FROM pg_catalog.pg_type t
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
       left outer join extension_oids e
         on t.oid = e.objid
  WHERE
    t.typcategory = 'E'
    and e.objid is null
     and n.nspname not in ('pg_catalog', 'information_schema', 'pg_toast')
     and n.nspname not like 'pg_temp_%%' and n.nspname not like 'pg_toast_temp_%%'
  ORDER BY 1, 2
),
r as (
    select
        c.relname as name,
        n.nspname as schema,
        c.relkind as relationtype,
        c.oid as oid,
        case when c.relkind in ('m', 'v') then
          pg_get_viewdef(c.oid)
        else null end
          as definition,
        (SELECT
              '"' || nmsp_parent.nspname || '"."' || parent.relname || '"' as parent
          FROM pg_inherits
              JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
              JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
              JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
              JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
          where child.oid = c.oid)
        as parent_table,
        case when c.relpartbound is not null then
          pg_get_expr(c.relpartbound, c.oid, true)
        when c.relhassubclass is not null then
          pg_catalog.pg_get_partkeydef(c.oid)
        end
        as partition_def,
        c.relrowsecurity::boolean as rowsecurity,
        c.relforcerowsecurity::boolean as forcerowsecurity,
        c.relpersistence as persistence,
        c.relpages as page_size_estimate,
        c.reltuples as row_count_estimate
    from
        pg_catalog.pg_class c
        inner join pg_catalog.pg_namespace n
          ON n.oid = c.relnamespace
        left outer join extension_oids e
          on c.oid = e.objid
    where c.relkind in ('r', 'v', 'm', 'c', 'p')
     and e.objid is null
     and n.nspname not in ('pg_catalog', 'information_schema', 'pg_toast')
     and n.nspname not like 'pg_temp_%%' and n.nspname not like 'pg_toast_temp_%%'
)
select
    r.relationtype,
    r.schema,
    r.name,
    r.definition as definition,
    a.attnum as position_number,
    a.attname as attname,
    a.attnotnull as not_null,
    a.atttypid::regtype AS datatype,
    a.attidentity != '' as is_identity,
    a.attidentity = 'a' as is_identity_always,
    -- PRE_12 false as is_generated,
     a.attgenerated != '' as is_generated,
    (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
     WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS collation,
    pg_get_expr(ad.adbin, ad.adrelid) as defaultdef,
    r.oid as oid,
    format_type(atttypid, atttypmod) AS datatypestring,
    e.enum_oid is not null as is_enum,
    e.name as enum_name,
    e.schema as enum_schema,
    pg_catalog.obj_description(r.oid) as comment,
    r.parent_table,
    r.partition_def,
    r.rowsecurity,
    r.forcerowsecurity,
    r.persistence,
    r.page_size_estimate,
    r.row_count_estimate
FROM
    r
    left join pg_catalog.pg_attribute a
        on r.oid = a.attrelid and a.attnum > 0
    left join pg_catalog.pg_attrdef ad
        on a.attrelid = ad.adrelid
        and a.attnum = ad.adnum
    left join enums e
      on a.atttypid = e.enum_oid
where a.attisdropped is not true
 and r.schema not in ('pg_catalog', 'information_schema', 'pg_toast')
 and r.schema not like 'pg_temp_%%' and r.schema not like 'pg_toast_temp_%%'
order by relationtype, r.schema, r.name, position_number;
]
(Background on this error at: https://sqlalche.me/e/20/f405)

[rtrad89]

It looks like this error is actually caused by schemainspect module. A very similar issue is still open there: https://github.com/djrobstep/schemainspect/issues/81

As a workaround for me, I added LIMIT 1 to line 43 in sql/relations.sql script to be:

where child.oid = c.oid LIMIT 1)

The result is reasonably good.

[joshainglis]

@rtrad89 I think this is handled in this PR. At least it handles partitioned tables. Curious if it also handles your use-case.