PostgreSQL: describe() drops [unique] foreign keys and secondary indexes, and alter() fails on tables that have them

Abierto
#1,699 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 6 días

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
3/5
Tiempo estimado
1-2 días
Aptitud para principiantes
73/100
Tipo de issue
Error
Claridad
Bien especificado
Estado de actividad
Activo
Stack tecnológico
mysql, postgresql, python
Área
databases

Línea de trabajo

Start with src/datajoint/adapters/postgres.py around get_indexes_sql and src/datajoint/heading.py around the code that reads index columns. Check the PostgreSQL adapter tests for coverage of heading.indexes, describe(), and unchanged alter() calls; add or update cases for regular, unique, and primary key indexes. Done when PostgreSQL reports the same secondary index and unique foreign key metadata as MySQL and an unchanged definition alters without error.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

bug

On PostgreSQL, heading.indexes is always empty. As a result, describe() loses [unique] foreign keys and secondary indexes, and alter() fails on any table that has a secondary index.

get_indexes_sql returns indexname, indexdef from pg_indexes (adapters/postgres.py#L1025-L1030). The heading reads index_name, column_name, seq_in_index and non_unique (heading.py#L625-L649), so it finds none of them.

Repro (master, eefe74b4)

@schema
class Parent(dj.Manual):
    definition = """
    parent_id : int32
    """


@schema
class Other(dj.Manual):
    definition = """
    other_id : int32
    """


@schema
class Child(dj.Manual):
    definition = """
    child_id : int32
    ---
    -> [unique] Parent
    -> Other
    a : int32
    b : varchar(32)
    c : int32
    index(a)
    unique index(b, c)
    """
MySQL PostgreSQL
Child().heading.indexes ('a',), ('b', 'c') unique, ('other_id',), ('parent_id',) unique {}
Child().describe() -> [unique] Parent, INDEX (a), UNIQUE INDEX (b, c) -> Parent, no index lines
Child.alter(prompt=False) with the definition unchanged no change NotImplementedError: table.alter cannot alter indexes (yet)

The indexes exist and are enforced on both backends; a duplicate parent_id is rejected on both. Only reading them back fails.

Expected

The same heading.indexes and describe() output as on MySQL, and alter() with an unchanged definition changing nothing.

Suggested fix

Query pg_index and pg_attribute for index_name, column_name, non_unique and seq_in_index, and leave out the primary-key index (indisprimary).

Related: #1672, about declaring indexes on JSON paths on PostgreSQL.

Lenguaje dominante
Python
Estrellas
198
Forks
103
Merge medio
6 d 1 h
PR fusionados (30 d)
10

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de datajoint/datajoint-python

Todos los issues de datajoint/datajoint-python

Issues similares

Más issues de Python

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.