Autoload indexes in postgres

[Former user]

Add index handling to reflecttable for Postgres to allow richer results from autocode & similar tools.

The attached patch (against current trunk), has been tested against unique & non-unique and single & multiple column indexes. See #663 for a similar patch for MySQL.

My use case for this is to aid conversion from another ORM. It also allows me to distribute my schema in a db portable way, plus I find it simpler to use sqlalchemy's toolset when moving between/promoting to different environments than it is to use a GUI db modeling tool.

-Ken Kuhlman

[Former user]

Autoload indexes for postgres reflection

[Mike Bayer]

hi -

the patch looks very good. would it be possible for you to also add a short unit test to test/dialect/postgres.py ?

[Former user]

Test index reflection in test/dialect/postgres.py

[Former user]

Sure thing.. it's attached. Should it be in test/engine/reflection.py though, if you're going to accept #663 too?

-ken

[Mike Bayer]

good point. probably with @supported('postgres') to start.

[Mike Bayer]

although, if we are reflecting aspects of the index which are PG-specific, those might be tested within the dialect module. havent looked at your patch yet tho :).

[jek]

This seems to explode when there are expression-based indexes on the reflected table:

create index idx1 on tbl1 ((col1 + col2))

[Former user]

Fix problem with reflecting expression-based indexes

[Former user]

Nice catch.. thanks!

I've moved the original test to test/dialect/reflection.py (with supported('postgres') ), added postgres-specific tests for expression-based & partial indexes, and changed the reflection to warn & skip expression-based indexes. Partial indexes are reflected without the predicate & a warning is generated.

In the DBMS I'm used to, these would be materialized views, but adding that to sqlalchemy is a bigger dragon than I'm equipt to slay.

Fixing the reflection was straight-forward, but it took me a couple of attempts to figure out how to test for warnings. Hopefully the solution I added to test/dialect/postgres.py is suitable.

Attachment is reflect_indexes_with_tests.patch

[Mike Bayer]

need this patched for 0.4 and 0.5, will also affect the reflection refactor for 0.6

[Mike Bayer]

my apologies for forgetting this great patch ! applied to 0.4 and 0.5 in 914dfb2bf4a70897c1742ed7c0b29a153fec3740 864644bee4e3acc5c63eac6e639ae39d1c3c8393.

[Mike Bayer]

Removing milestone: 0.4.xx (automated comment)