djrobstep / schemainspect Goto Github PK

View Code? Open in Web Editor NEW

48.0 48.0 63.0 273 KB

Schema inspection for PostgreSQL (and potentially others).

License: The Unlicense

Makefile 0.31% Python 99.69%

schemainspect's People

Contributors

Stargazers

Watchers

schemainspect's Issues

Use uppercase SQL keywords

In most places, SQL keywords such as CREATE DROP, etc. are written in uppercase. Would you accept a PR that changes the output of this library accordingly?

Support including or excluding list of schemas, not just single schema

Work done in #58 adds an ability to include or exclude single schema, but recently I've met the need to limit considered schemas to a list of them or symmetrically to exclude a list of schemas from consideration, hence this feature request. Also I'd like to submit a pull request with implementation.

As for implementation I see 2 possible ways:

make get_inspector() and schemainspect.pg.obj.PostgreSQL.filter_schema() to accept list of strings in schema and exclude_schema parameter. Here again we have 2 possibilities: accept list in addition to just string to keep old behaviour or accept only list and break compatibility
treat schema and include_schema parameters as regular expression patterns thus keeping function signature but slighly changing old behaviour: passing plain string would mean matching schema by given prefix

What do you think? Personally I lean towards the first alternative, i.e. accept a list of strings in addition to just string in corresponding function and method parameters.

KeyError when loading relations from Timescale DB

Hi,
Running schemainspect against PostgreSQL with a TimeScale extension results in error.
I believe this could be caused by indexes pointing to extension tables.
The default logic seems to skip extension tables from the relations collection at load_all_relations but will still try to load the indexes.
I'll attach an output of test done against a fresh Docker image of Timescale:

fail_at_test_indexes.txt
Samuli

Column options not supported

Column options:
ALTER TABLE test ALTER COLUMN test SET STATISTICS 0;
ALTER TABLE test ALTER COLUMN test SET (n_distinct=-1);
Makes no difference with migra.

Custom column options can be found in pg_catalog.pg_attribute.attoptions, statistics setting in pg_catalog.pg_attribute.attstattarget.

relpartbound issue

it seems relpartbound does not exist in pg<10
a fallback or something is needed for relations.sql

RuntimeError: maximum recursion depth exceeded

Hi, I ran into this issue running migra, but it looked like a schemainspect issue so I'm reporting it here. Please let me know if it would fit better in the other repo. Thanks for taking a look!

Python version is 2.7.14, but the same issue occurred with 3.6.3.
migra version is migra-1.0.1503199248.
Using brand new pyenv install on Ubuntu.

Running migra:

    $ migra postgresql://user:pass@server1/db postgresql://user:pass@server2/db
    Traceback (most recent call last):
      File "/home/migra/.pyenv/versions/2.7.14/bin/migra", line 11, in <module>
        sys.exit(do_command())
      File "/home/migra/.pyenv/versions/2.7.14/lib/python2.7/site-packages/migra/command.py", line 73, in do_command
        status = run(args)
      File "/home/migra/.pyenv/versions/2.7.14/lib/python2.7/site-packages/migra/command.py", line 52, in run
        m = Migration(ac0, ac1)
      File "/home/migra/.pyenv/versions/2.7.14/lib/python2.7/site-packages/migra/migra.py", line 20, in __init__
        self.changes.i_from = get_inspector(x_from)
      File "/home/migra/.pyenv/versions/2.7.14/lib/python2.7/site-packages/schemainspect/get.py", line 19, in get_inspector
        return ic(c)
      File "/home/migra/.pyenv/versions/2.7.14/lib/python2.7/site-packages/schemainspect/pg/__init__.py", line 331, in __init__
        super(PostgreSQL, self).__init__(c, include_internal)
      File "/home/migra/.pyenv/versions/2.7.14/lib/python2.7/site-packages/schemainspect/inspector.py", line 25, in __init__
        self.load_all()
      File "/home/migra/.pyenv/versions/2.7.14/lib/python2.7/site-packages/schemainspect/pg/__init__.py", line 342, in load_all
        self.load_deps_all()
      File "/home/migra/.pyenv/versions/2.7.14/lib/python2.7/site-packages/schemainspect/pg/__init__.py", line 383, in load_deps_all
        'dependents'
      File "/home/migra/.pyenv/versions/2.7.14/lib/python2.7/site-packages/schemainspect/pg/__init__.py", line 370, in get_related_for_item
        for _ in get_related_for_item(d, att)
      File "/home/migra/.pyenv/versions/2.7.14/lib/python2.7/site-packages/schemainspect/pg/__init__.py", line 370, in get_related_for_item
        for _ in get_related_for_item(d, att)
      .. repeat 977 more times ..
      File "/home/migra/.pyenv/versions/2.7.14/lib/python2.7/site-packages/schemainspect/pg/__init__.py", line 367, in get_related_for_item
        return [item.signature] + [
      File "/home/migra/.pyenv/versions/2.7.14/lib/python2.7/site-packages/schemainspect/pg/__init__.py", line 113, in signature
        return '{}({})'.format(self.quoted_full_name, self.identity_arguments)
      File "/home/migra/.pyenv/versions/2.7.14/lib/python2.7/site-packages/schemainspect/inspected.py", line 9, in quoted_full_name
        quoted_identifier(self.schema), quoted_identifier(self.name))
      File "/home/migra/.pyenv/versions/2.7.14/lib/python2.7/site-packages/schemainspect/misc.py", line 33, in quoted_identifier
        identifier, remainder = identifier.split('(', 1)
    RuntimeError: maximum recursion depth exceeded

Help getting environment set up to contribute

I'm interested in contributing to this repo, but I can't work out your development setup.

There's a tox.ini file, but that wants a setup.py file to run. There used to be a setup.py file but it got removed. Also, the tox.ini refers to requirements.txt but there isn't one as far as I can see!

There's something I'm missing but what is it? Sometimes repositories have some steps in their readme to get you started with development.

Can you help get me started?

Python 3.5.3: "SyntaxError: invalid syntax" due to "f string" usage in command.py

I just recently updated schemainspect + migra on a Debian 9 box.
Debian 9 comes with: Python 3.5.3
Looks like the issue is that a newer version of Python is required to use "f strings"

Running the migra command alone (no args, or with args), I get a stack trace:

Traceback (most recent call last):
  File "/usr/local/bin/migra", line 7, in <module>
    from migra import do_command
  File "/usr/local/lib/python3.5/dist-packages/migra/__init__.py", line 4, in <module>
    from .command import do_command
  File "/usr/local/lib/python3.5/dist-packages/migra/command.py", line 9, in <module>
    from .migra import Migration
  File "/usr/local/lib/python3.5/dist-packages/migra/migra.py", line 3, in <module>
    from schemainspect import DBInspector, get_inspector
  File "/usr/local/lib/python3.5/dist-packages/schemainspect/__init__.py", line 4, in <module>
    from .command import do_command
  File "/usr/local/lib/python3.5/dist-packages/schemainspect/command.py", line 34
    thing=f"{dep.kind_dependent_on}: {thing}",
                                            ^
SyntaxError: invalid syntax

Link to a couple of relevant lines: https://github.com/djrobstep/schemainspect/blob/master/schemainspect/command.py#L34-L35 (there may be more)

Is backwards compatibility with Python 3.5 an option at all?

Missing dependency for py3.12 (pkg_resources / setuptools)

Hello,

Python3.12 does not include setuptools in default venvs anymore, if a package needs setuptools, it needs to explicitly import it.

This package uses pkg_resources which is distributed by setuptools.
It should not use pkg_resources, because it's deprecated, but in the meantime, it should declare an explicit dependency to setuptools.

Dependencies on aggregate functions

Somewhat similar to #3, I guess, but this fails as well:

CREATE AGGREGATE array_accum(anyelement) (
    SFUNC = array_append,
    STYPE = anyarray,
    INITCOND = '{}'
);
CREATE VIEW foov AS SELECT array_accum(1);

Stacktrace:

Traceback (most recent call last):
  File "foo.py", line 5, in <module>
    i = get_inspector(s)
  File "/home/marko/ff/mainenv/lib/python3.4/site-packages/schemainspect/get.py", line 19, in get_inspector
    return ic(c)
  File "/home/marko/ff/mainenv/lib/python3.4/site-packages/schemainspect/pg/__init__.py", line 331, in __init__
    super(PostgreSQL, self).__init__(c, include_internal)
  File "/home/marko/ff/mainenv/lib/python3.4/site-packages/schemainspect/inspector.py", line 25, in __init__
    self.load_all()
  File "/home/marko/ff/mainenv/lib/python3.4/site-packages/schemainspect/pg/__init__.py", line 340, in load_all
    self.load_deps()
  File "/home/marko/ff/mainenv/lib/python3.4/site-packages/schemainspect/pg/__init__.py", line 357, in load_deps
    self.selectables[x_dependent_on].dependents.append(x)
KeyError: '"public"."array_accum"(anyelement)'

Failing tests

Following test seem to fail, when using the newest packages available, whilst still meeting the requirements from the pyproject.toml:

    "test_can_replace"
    "test_collations"
    "test_constraints"
    "test_dep_order"
    "test_enum_deps"
    "test_exclusion_constraint"
    "test_fk_col_order"
    "test_fk_info"
    "test_generated_columns"
    "test_identity_columns"
    "test_indexes"
    "test_inherit"
    "test_kinds"
    "test_lineendings"
    "test_long_identifiers"
    "test_partitions"
    "test_postgres_inspect"
    "test_postgres_inspect_excludeschema"
    "test_postgres_inspect_sigleschema"
    "test_raw_connection"
    "test_relationship"
    "test_replica_trigger"
    "test_rls"
    "test_separate_validate"
    "test_sequences"
    "test_table_dependency_order"
    "test_types_and_domains"
    "test_view_trigger"
    "test_weird_names"

The issue seems to be caused by the same problems described in the migra issue.

Uploading entire database schemas instead of single needed schema.

Hi!

Can you tell me if there is any serious reason to upload the entire database schemas metadata for inspector and then filter out it with one_schema method usage?

Cause at our project we have a database that can contain a lot of schemas and we're syncing it from time to time with your great project Migra that uses this inspector to discover database. As a result of big schemas amount inspector discovering the database was too long, up to 10 minutes for x_from and x_target each.

To resolve it I modified your SQL queries for fetching only one schema metadata instead of the entire database can it cause some problems for us? If no so what is the reason to discover the entire database?

Regards, Artur.

schemainspect can't handle multiple tables having constraints with the same name

First off, I'm not a Python dev, so sorry if I get some terms wrong.

While testing migra, which uses schemainspect, I discovered diffs weren't being generated correctly when I modified certain constraints.

I managed to track this down to an issue with schemainspect - when you call get_inspector(), the constraints property is an OrderedDict with keys formatted as "schema_name"."constraint_name". Formatting the key this way means you can't have two constraints in different tables with the same name. Postgres actually allows this - constraint names only have to be unique per-table, not per-schema (unlike index names).

My hacky solution is to patch schemainspect/pg/__init__.py to change the key format to "schema_name"."table_name"."constraint_name". This causes migra to work correctly on the schemas I'm testing it with. Here's my patch. I'm hesitant to open a pull request for it since I've never used schemainspect other than as a dependency of migra, so I'm not sure if this is considered a breaking change, or if it might cause other issues.

'language internal' probably not supported

CREATE OR REPLACE FUNCTION test_case(text)
 RETURNS text
 LANGUAGE internal
 IMMUTABLE
AS $function$upper$function$

This causes problem in Migra. It is trivial to avoid (use 'LANGUAGE SQL' function for comparison), so mentioning it mainly for reference.

Exception inspecting a table or composite type referencing an enum from an extension

This looks similar to #59, but with enums. The error occurs a little earlier, in load_all_relations.

To reproduce this problem you need an extension containing composite type containing an enum. I couldn't find anything like that in the standard postgresql distribution, so I put together a dummy extension. This short script will create the extension, or you can grab the archive linked below and unpack it in your postgresql share dir.

pgsharedir=$(pg_config --sharedir)

cat >$pgsharedir/extension/dummy.control <<'EOF'
# dummy extension
comment = 'dummy test extension'
default_version = '0.0.1'
relocatable = true
EOF

cat >$pgsharedir/extension/dummy--0.0.1.sql <<'EOF'
SET client_min_messages = warning;
CREATE TYPE color AS ENUM (
    'blue',
    'teal',
    'aqua',
    'grey',
    'eggshell');
CREATE TYPE colorpair AS (
    a color,
    b color);
EOF

dummy-extension.tar.gz

Once the extension is installed, the following schema should reproduce the problem.

create extension dummy;
create table foo (id serial primary key, xx colorpair);

Traceback (most recent call last):
  File ".../bin/migra", line 3, in <module>
    import re
  File ".../lib/python3.6/site-packages/migra/command.py", line 108, in do_command
    status = run(args)
  File ".../lib/python3.6/site-packages/migra/command.py", line 79, in run
    m = Migration(ac0, ac1, schema=schema, exclude_schema=exclude_schema)
  File ".../lib/python3.6/site-packages/migra/migra.py", line 26, in __init__
    x_from, schema=schema, exclude_schema=exclude_schema
  File ".../lib/python3.6/site-packages/schemainspect/get.py", line 20, in get_inspector
    inspected = ic(c)
  File ".../lib/python3.6/site-packages/schemainspect/pg/obj.py", line 1060, in __init__
    super(PostgreSQL, self).__init__(c, include_internal)
  File ".../lib/python3.6/site-packages/schemainspect/inspector.py", line 25, in __init__
    self.load_all()
  File ".../lib/python3.6/site-packages/schemainspect/pg/obj.py", line 1064, in load_all
    self.load_all_relations()
  File ".../lib/python3.6/site-packages/schemainspect/pg/obj.py", line 1319, in load_all_relations
    for c in clist
  File ".../lib/python3.6/site-packages/schemainspect/pg/obj.py", line 1320, in <listcomp>
    if c.position_number
  File ".../lib/python3.6/site-packages/schemainspect/pg/obj.py", line 1302, in get_enum
    return self.enums[quoted_full_name]
KeyError: '"sterling"."rrule_freq"'

Ignore temporary schemas

Ignore temporary schemas (eg pg_toast_temp_9, pg_temp_11), so as to eliminate unwanted output as per djrobstep/migra#41

Support custom aggregations

What it sez on the tin

Tables with zero columns not reported

As reported in djrobstep/migra#22, schemainspect doesn't currently detect tables with zero columns.

Support collation detection

Collations currently entirely disregarded by schemainspect. Needs fixing.

Refactor request: add docstrings and use more informative variable names

Docstrings would be great for ease of contribution and use. Some names could be made more informative, such as

connection_from_s_or_c
s_or_c
any one-letter variable/argument

empty string vs null in misc::quoted_identifiers

Since both empty string and None will be evaluated as False in python, the line if identity_arguments: will mis-produce a result ofschema.func instead of schema.func() for a function without any arguments.

Changing the corresponding line to if identity_arguments is not None: will be a quick fix.

Thank you!

Column p.parameter_default does not exist

I tried to use migra tool and the query from schemainspect/pg/functions.sql does not work for me (PostgreSQL 9.3.12 on Mac OS):

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column p.parameter_default does not exist
LINE 79:             p.parameter_default as parameter_default,
                     ^
 [SQL: "with r1 as (\n        SELECT\n            r.routine_name as name,\n            r.routine_schema as schema,\n            r.specific_name as specific_name,\n            r.data_type,\n            r.external_language,\n            r.routine_definition as definition\n        FROM information_schema.routines r\n         where r.external_language not in ('C', 'INTERNAL')\n         and r.routine_schema not in ('pg_catalog', 'information_schema')\n        order by\n            r.specific_name\n    ),\n    pgproc as (\n      select\n        nspname as schema,\n        proname as name,\n        p.oid as oid,\n        case proisstrict when true then\n          'RETURNS NULL ON NULL INPUT'\n        else\n          'CALLED ON NULL INPUT'\n        end as strictness,\n        case prosecdef when true then\n          'SECURITY DEFINER'\n        else\n          'SECURITY INVOKER'\n        end as security_type,\n        case provolatile\n          when 'i' then\n            'IMMUTABLE'\n          when 's' then\n            'STABLE'\n          when 'v' then\n            'VOLATILE'\n          else\n            null\n        end as volatility\n      from\n          pg_proc p\n          INNER JOIN pg_namespace n\n              ON n.oid=p.pronamespace\n       where nspname not in ('pg_catalog', 'information_schema')\n    ),\n    extension_oids as (\n      select\n          objid\n      from\n          pg_depend d\n      WHERE\n          d.refclassid = 'pg_extension'::regclass\n          and d.classid = 'pg_proc'::regclass\n    ),\n    r as (\n        select\n            r1.*,\n            pp.volatility,\n            pp.strictness,\n            pp.security_type,\n            pp.oid,\n            e.objid as extension_oid\n        from r1\n        left outer join pgproc pp\n          on r1.schema = pp.schema\n          and r1.name = pp.name\n        left outer join extension_oids e\n          on pp.oid = e.objid\n         where e.objid is null\n    ),\n    pre as (\n        SELECT\n            r.schema as schema,\n            r.name as name,\n            r.data_type as returntype,\n            p.parameter_name as parameter_name,\n            p.data_type as data_type,\n            p.parameter_mode as parameter_mode,\n            p.parameter_default as parameter_default,\n            p.ordinal_position as position_number,\n            r.definition as definition,\n            r.external_language as language,\n            r.strictness as strictness,\n            r.security_type as security_type,\n            r.volatility as volatility,\n            r.oid as oid,\n            r.extension_oid as extension_oid,\n            pg_get_function_result(oid) as result_string,\n            pg_get_function_identity_arguments(oid) as identity_arguments\n        FROM r\n            JOIN information_schema.parameters p ON\n                r.specific_name=p.specific_name\n        order by\n            name, parameter_mode, ordinal_position, parameter_name\n    )\nselect\n*\nfrom pre\norder by\n    schema, name, parameter_mode, position_number, parameter_name;\n"]

pg_class relpartbound breaks PG 9.6

The now-closed but unresolved issue raised earlier about pg_catalog.pg_class.relpartbound means that I can't use this with PG9.6.
Is there any plan to fix this?

Exception when inspecting a view that uses the PostGIS ST_Dump() function

I've encountered this bug when using migra against a database that contains views that use the PostGIS function ST_Dump().

Minimal SQL schema to reproduce:

create extension postgis;
create table t (geom geometry);
create view v as select (ST_Dump(geom)).geom from t;

Traceback:

$ migra postgres:///a postgres:///b
Traceback (most recent call last):
  File "/home/st/p/python/migra/venv/bin/migra", line 8, in 
    sys.exit(do_command())
  File "/home/st/p/python/migra/venv/lib/python3.8/site-packages/migra/command.py", line 108, in do_command
    status = run(args)
  File "/home/st/p/python/migra/venv/lib/python3.8/site-packages/migra/command.py", line 79, in run
    m = Migration(ac0, ac1, schema=schema, exclude_schema=exclude_schema)
  File "/home/st/p/python/migra/venv/lib/python3.8/site-packages/migra/migra.py", line 33, in __init__
    self.changes.i_target = get_inspector(
  File "/home/st/p/python/migra/venv/lib/python3.8/site-packages/schemainspect/get.py", line 20, in get_inspector
    inspected = ic(c)
  File "/home/st/p/python/migra/venv/lib/python3.8/site-packages/schemainspect/pg/obj.py", line 1041, in __init__
    super(PostgreSQL, self).__init__(c, include_internal)
  File "/home/st/p/python/migra/venv/lib/python3.8/site-packages/schemainspect/inspector.py", line 25, in __init__
    self.load_all()
  File "/home/st/p/python/migra/venv/lib/python3.8/site-packages/schemainspect/pg/obj.py", line 1058, in load_all
    self.load_deps()
  File "/home/st/p/python/migra/venv/lib/python3.8/site-packages/schemainspect/pg/obj.py", line 1133, in load_deps
    self.selectables[x_dependent_on].dependents.append(x)
KeyError: '"public"."geometry_dump"'

"geometry_dump" is the postgis type that describes the records returned by ST_Dump.

security_invoker on views is ignored

Define a view with security_invoker=on
Run migra schema diff
security_invoker clause is not included in output

More reproducible example here supabase/cli#792

Inheritance multi parent: more than one row returned by a subquery used as an expression

From the PG docs: A table can inherit from more than one parent table, in which case it has the union of the columns defined by the parent tables.

That means we can have many parents for one child table which make the concept of the PG table inheritance confusing ;-)

If we create table which have many parents, then we will have SQL error with schemainspect: more than one row returned by a subquery used as an expression

schemainspect/schemainspect/pg/sql/relations.sql

Line 43 in 066262d

where child.oid = c.oid)

I would propose returning array of parent tables, but not sure if that is supported by the rest of the schemainspect

Here is the problematic SQL:

schemainspect/schemainspect/pg/sql/relations.sql

Line 37 in 066262d

'"' || nmsp_parent.nspname || '"."' || parent.relname || '"' as parent

You can use

SELECT json_agg(
              '"' || nmsp_parent.nspname || '"."' || parent.relname || '"') as parent

SELECT array_agg(
              '"' || nmsp_parent.nspname || '"."' || parent.relname || '"') as parent

and probably even

SELECT array_agg( format('%I.%I', nmsp_parent.nspname, parent.relname)) as parent

But you may want to set quote_all_identifiers to ensure that all names are always quoted with ".

Licensing Issues

The Unlicense

The Unlicense has questionable legal power. We don't have to drop it since imo it most clearly communicates the intent to release to public domain but multi-licensing under another license with better understood legal impact would be great. I suggest one (or all) of these:

MIT - most widely recognized, but carries an attribution requirement and is not public domain
BSD0 - unencumbered and explicitly pre-approved for use in Google projects, but not public domain
CC0 - public domain attribution with appropriate fallback license, has good legal basis as it is designed and maintained by Creative Commons. Appropriate to use in licensing software as opposed to other CC licenses. It is the only not OSI-approved license on this list

Contributor License

Currently schemainspect is in a legal gray zone because it carries no explicit contributor licensing agreement. This can be added retroactively but would require reaching every contributor individually.

We would need to get the contributor license sorted out before we can re-license the code if we are to switch from the Unlicense.