On 04/24/2017 07:21 AM, Антонио Антуан wrote:
Here is definition.
PostgreSQL ensures that all columns that are in "table_master" will be
in the inherited tables, but the indexes won't: I should create them
manually by describing it in procedure code. So, if I add new index, I
should add them into trigger code.
That is my migration:
|
fromalembic importop
importsqlalchemy assa
revision='77e958e7e1bd'
down_revision='e47e752436d1'
defupgrade():
op.add_column('foos',sa.Column('new_column',sa.Integer))
op.create_index('ix_foos_name','foos',['name'])
|
After execution, table "foos" and its children will have new column
("new_column"), but new index will be only into "foos" table.
So, currently I should get all partitions and execute "CREATE INDEX"
manually, something like that:
|
conn =op.get_bind()
forchild_name inconn.execute(text("select child.relname from
pg_catelog.pg_ingerits
INNER JOIN pg_catalog.pg_class as child ON (pg_inherits.inhrelid =
child.oid WHERE inhparent = 'foos'::regclass")):
op.create_index('ix_{table}_name'.format(table=child_name),child_name,['name'])
|
First of all, it would be cool, If I did not have to insert "CREATE
INDEX" statement manually in trigger code. But it seems to be impossible
now :)
Also, as you can see, I should manually find each partition of master
table and manually create index for it. Can alembic do it himself?
OK so this is really complicated, there's these elements:
1. the trigger needs to CREATE INDEX
2. when you add Index to your application, you need to:
a. add the index to all the existing partitions
b. rewrite the trigger with the new index
If this were me I'm not sure I'd be taking the plunge into having
partitions be on the fly, and instead pre-partition in some way, with a
hashing scheme or something like that.
However, for this you'd need to customize Alembic's generation using the
hooks described at
http://alembic.zzzcomputing.com/en/latest/api/autogenerate.html#customizing-revision-generation.
Basically you would traverse the autogenerate for a new index, then add
new op.execute() directives that also add the index to the other tables
as well as rewrite the whole trigger. For the trigger you'd probably
use ops.ExecuteSQLOp("CREATE TRIGGER ...."), that op is at
http://alembic.zzzcomputing.com/en/latest/api/operations.html#alembic.operations.ops.ExecuteSQLOp.
There's a lot to grok here so try working from the examples and I can
try to advise where something needs to happen if you show some sample code.
>
> If autodetection not working...
> I can get list of inherited tables with such query:
> |SELECT child.relname, parent.relname
> FROM pg_catalog.pg_inherits
> INNER JOIN pg_catalog.pg_class as child ON (pg_inherits.inhrelid =
> child.oid)
> INNER JOIN pg_catalog.pg_class as parent ON
(pg_inherits.inhparent =
> parent.oid)
> WHERE inhparent = 'base_table'::regclass;|
>
> Returned names can be specified as parameter "only" in "reflect()"
> method of MetaData instance. Can I specify target table for each
table
> in metadata for migration?
> I found just such solution:
> |
> for table_name in inherit_table_names:
> meta_data.tables[table_name].name = "base_table"
I'm not really following what you're doing here. Changing the name of
a Table like that is probably not safe in general, depends on the
context.
> |
>
> Is my solution safe?
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve
<http://stackoverflow.com/help/mcve> for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it,
send
> an email to sqlalchemy+...@googlegroups.com <javascript:>
> <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> To post to this group, send email to sqlal...@googlegroups.com
<javascript:>
> <mailto:sqlal...@googlegroups.com <javascript:>>.
> Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
