Re: [sqlalchemy] autogenerate migrations with alembic for inherited tables

[mike bayer]

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
 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 
 > .
 > To post to this group, send email to sqlal...@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 

Re: [sqlalchemy] autogenerate migrations with alembic for inherited tables

[Антонио Антуан]

пятница, 21 апреля 2017 г., 17:07:34 UTC+3 пользователь Mike Bayer написал:
>
>
>
> On 04/21/2017 09:16 AM, Антонио Антуан wrote: 
> > Helllo. 
> > I have a model, with specified __tablename__ = 'base_table'. 
> > In postgresql, the table has trigger, which executes before each insert: 
> > it creates partition for current month (if it not exist yet), specifies 
> > "INHERITS (base_table)" for new partition and insert data into it. 
> > 
> > Is there any way to autodetect table inheritance and generate migration 
> > script for it? 
>
>
> What's the actual migration you want to generate?   E.g. start with A, 
> change to Bare you starting with Table(), and then adding 
> "postgresql_inherits" to it after the fact?  it's not clear. 
>
> if you can illustrate what this migration would be I'd have some idea 
> what you are actually trying to do.The "trigger" you refer to seems 
> to be something that emits CREATE TABLE upon INSERT, which would be 
> outside the realm of Alembic. 
>

For example, here is my model:
import time
from sqlalchemy import Column, Integer, Unicode, event, DDL
from myproject import Base

class Foo(Base):
__tablename__ = 'foos'
id = Column(Integer, primary_key=True, autoincrement=True)
ts_spawn = Column(Integer, nullable=False)
name = Column(Unicode, nullable=False)
data_id = Column(Integer, index=True)

def __init__(self):
self.ts_spawn = int(time.time())

event.listen(Foo.__table__, 'after_create', 
 DDL("""
CREATE OR REPLACE FUNCTION foos_insert_trigger() RETURNS TRIGGER AS 
$BODY$
DECLARE
table_master VARCHAR(255) := 'foos';
table_part VARCHAR(255) := '';
ts_spawn_date DATE := to_timestamp(NEW.ts_spawn);
ts_start INT := date_part('epoch', date_trunc('day', 
ts_spawn_date))::INT;
ts_end INT := date_part('epoch', date_trunc('day', ts_spawn_date + 
INTERVAL '1day))::INT;
BEGIN

-- Giving name for partition --
table_part := table_master
  || '_y' || DATE_PART('year', ts_spawn_date)::TEXT
  || '_m' || DATE_PART('month', ts_spawn_date)::TEXT
  || '_d' || DATE_PART('day', ts_spawn_date)::TEXT;

-- Trying to insert into partition 
EXECUTE 'INSERT INTO ' || quote_ident(table_part) || ' SELECT ($1).*' USING NEW;
RETURN NULL;
-- If not then creating it 
EXCEPTION WHEN UNDEFINED_TABLE
THEN
BEGIN
-- Creating partition ---

EXECUTE 'CREATE TABLE ' || table_part || ' (
CHECK ( ts_spawn >= ' || ts_start || ' AND ts_spawn < ' || 
ts_end || '),

CONSTRAINT ' || table_part || '_pk PRIMARY KEY
(id)
) INHERITS ( ' || table_master || ' ) WITH ( OIDS=FALSE )';
EXECUTE 'CREATE INDEX ids_' || table_part || '_data_id
ON ' || table_part || ' USING btree (data_id);'

EXCEPTION WHEN DUPLICATE_TABLE THEN
-- Do nothing

EXECUTE 'INSERT INTO ' || quote_ident(table_part) || ' SELECT ($1).*' USING NEW;
RETURN NULL;

END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

CREATE TRIGGER insert_foo_trigger BEFORE INSERT
ON foos
FOR EACH ROW
EXECUTE PROCEDURE foos_insert_trigger();

"""))


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:
from alembic importop
import sqlalchemy as sa

revision = '77e958e7e1bd'
down_revision = 'e47e752436d1'

def upgrade():
   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()

for child_name in conn.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?




>
> > 
> > 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 

Re: [sqlalchemy] autogenerate migrations with alembic for inherited tables

[mike bayer]

On 04/21/2017 09:16 AM, Антонио Антуан wrote:

Helllo.
I have a model, with specified __tablename__ = 'base_table'.
In postgresql, the table has trigger, which executes before each insert: 
it creates partition for current month (if it not exist yet), specifies 
"INHERITS (base_table)" for new partition and insert data into it.


Is there any way to autodetect table inheritance and generate migration 
script for it?



What's the actual migration you want to generate?   E.g. start with A, 
change to Bare you starting with Table(), and then adding 
"postgresql_inherits" to it after the fact?  it's not clear.


if you can illustrate what this migration would be I'd have some idea 
what you are actually trying to do.The "trigger" you refer to seems 
to be something that emits CREATE TABLE upon INSERT, which would be 
outside the realm of Alembic.





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 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.


--
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.

[sqlalchemy] autogenerate migrations with alembic for inherited tables

[Антонио Антуан]

Helllo.
I have a model, with specified __tablename__ = 'base_table'.
In postgresql, the table has trigger, which executes before each insert: it 
creates partition for current month (if it not exist yet), specifies 
"INHERITS (base_table)" for new partition and insert data into it.

Is there any way to autodetect table inheritance and generate migration 
script for it?

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"

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 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.