djrobstep / schemainspect Goto Github PK
View Code? Open in Web Editor NEWSchema inspection for PostgreSQL (and potentially others).
License: The Unlicense
Schema inspection for PostgreSQL (and potentially others).
License: The Unlicense
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?
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:
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 compatibilityschema
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 prefixWhat 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.
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:
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
.
it seems relpartbound
does not exist in pg<10
a fallback or something is needed for relations.sql
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!
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
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?
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?
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.
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)'
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.
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.
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.
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.
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
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 (eg pg_toast_temp_9
, pg_temp_11
), so as to eliminate unwanted output as per djrobstep/migra#41
What it sez on the tin
As reported in djrobstep/migra#22, schemainspect doesn't currently detect tables with zero columns.
Collations currently entirely disregarded by schemainspect. Needs fixing.
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
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!
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"]
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?
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;
$ 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
.
More reproducible example here supabase/cli#792
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
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:
You can use
SELECT json_agg(
'"' || nmsp_parent.nspname || '"."' || parent.relname || '"') as parent
or
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 "
.
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:
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.
As per: https://www.postgresql.org/docs/11/sql-createtable.html
Related issue: djrobstep/migra/issues/91
Using Python3.6+ type annotations would require documenting and refactoring, as requested in #40
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.