Drop and recreate primary key indexes
Wolodja Wentland
is it possible to drop primary key indexes from within SA? I already
found the table.indexes set, but it does not contain the primary key
index. I can therefore drop all indexes for a table except the primary
key one.
It seems to me as if SA relies on a strict naming scheme for primary key
indexes in the form of "tablename_pkey" is this correct and could i rely
on that if i want to drop the index manually? What happens if there
already is a table with that name prior to table creation?
As you might have guessed i also would like to recreate this index later
on. Is there a automatic way to do so, or do i have to define the index
by myself?
with kind regards
Wolodja Wentland
Michael Bayer
this is not correct. SQLAlchemy creates the "primary key" of a table
only, using the standard DDL for PRIMARY KEY. The associated "index" and
its naming convention is automatically generated by your database, and is
not within the control of SQLAlchemy.
if you'd like to drop the primary key constraint entirely, you'd have to
issue the requisite ALTER statements appropriate for your database. As
far as dropping just the "index" part, that again is a database specific
issue and you'd have to consult your database's documentation.
Wolodja Wentland
> is it possible to drop primary key indexes from within SA? I already
> found the table.indexes set, but it does not contain the primary key
> index. I can therefore drop all indexes for a table except the primary
> key one.
I did some investigation and found that the primary key index is
generated automagically by PostgreSQL rather than SA. This is because i
declare some columns with 'primary_key=True' which causes psql to create
a primary key constraint and an index.
If i connect to the db in question and reflect the tables it is
unfortunately not possible to drop any pkey constraint, because the
.drop() method if not present in sa.schema.PrimaryKeyConstraint but only
in migrate.changeset.constraint.PrimaryKeyConstraint.
I create all PrimaryKeyConstraints explicitly now and use the
PrimaryKeyConstraint class from migrate. The problem i am facing now is
that i do not get instances of migrate's PrimaryKeyConstraint but rather
SA's one if i work on the constraints like exemplified in the following
code snippet:
--- snip ---
...
metadata = MetaData()
metadata.bind = engine
self._metadata.reflect()
tbl = self._metadata.tables[table_name]
pkc = [ con for con in tbl.constraints
if isinstance(con, PrimaryKeyConstraint) ][0]
log.debug('Primary key constraint for table [%s] on: %s'%(
table_name, pkc.columns.keys()))
log.debug('Dropping pkey constraint ...')
pkc.drop()
^^^^^^^^^^ This method is not present because i get instances from SA's classes not migrate's
--- snip ---
How can i tackle this problem? Any advise is welcome!
Michael Bayer
don't rely on Migrate's PKC object except for when you are ready to drop.
I.e.
for cons in table.constraints:
if isinstance(con, PrimaryKeyConstraint):
migrate.PrimaryKeyConstraint(con.name, ...etc...).drop()
Alternatively, just start using SQLalchemy 0.6 (its trunk so far):
from sqlalchemy.schema import DropConstraint
for cons in table.constraints:
if isinstance(con, PrimaryKeyConstraint):
engine.execute(DropConstraint(con))
Wolodja Wentland
> Alternatively, just start using SQLalchemy 0.6 (its trunk so far):
>
> from sqlalchemy.schema import DropConstraint
> for cons in table.constraints:
> if isinstance(con, PrimaryKeyConstraint):
> engine.execute(DropConstraint(con))
As i am using current trunk i tried to implement this approach.
Unfortunately this fails.
My goal is to be able to create and drop *Constraints whenever
i want to. I need this because i am writing a tool that does bulk
imports of data and the constraint checks are a severe performance
penalty.
Inspired by the now discovered AddConstraint/DropConstraint classes you
pointed me at I implemented constraint creation like:
--- snip ---
metadata = MetaData()
tbl = Table('foo', metadata,
Column('id', Integer)
...
)
...
pkey_constraints = [
PrimaryKeyConstraint(tbl.c.id, inline_ddl=False),
...
]
engine = create_engine(postgresql+...)
tbl.create(bind=engine)
...
engine.execute(AddConstraint(pkey_constraint_for_this_table))
--- snip ---
The assumption that creating a PrimaryKeyConstraint with
inline_ddl=False will prevent SA to generate DDL for this constraint
seems to be false as the generated SQL looks like:
--- snip ---
CREATE TABLE foo (
id SERIAL NOT NULL,
...
PRIMARY KEY (id)
)
--- snip ---
So the the attempt to manually create the PrimaryKeyConstraint fails
with the error "multiple primary keys for table ..."!
Questions:
* Why is that?
* Is inline_ddl not honoured for PrimaryKeyConstraints, which *is* a
subclass of Constraint?
* Is this caused by the "table._set_primary_key(self)" call
in PrimaryKeyConstraints._set_parent() ?
* How can i programmatically create primary key constraints?
I can't drop these constraints as well. Even if i accept that my tables
are created with primary key definitions the recipe you showed me does
not work. It fails with:
--- snip ---
...
/sqlalchemy/sql/compiler.pyc in _requires_quotes(self, value)
1306 def _requires_quotes(self, value):
1307 """Return True if the given identifier requires quoting."""
-> 1308 lc_value = value.lower()
1309 return (lc_value in self.reserved_words
1310 or self.illegal_initial_characters.match(value[0])
AttributeError: 'NoneType' object has no attribute 'lower'
--- snip ---
Is sqlalchemy-migrate the only way to handle this right now? I created
the primary key constraints by specifying the *columns* as strings
before and used a dictionary to differentiate between primary key
constraint column definitions for various tables? Is this advisable? Is
there a better way to achieve this?
with kind regards and thanks for this great tool!
Wolodja Wentland
Michael Bayer
> I can't drop these constraints as well. Even if i accept that my tables
> are created with primary key definitions the recipe you showed me does
> not work. It fails with:
>
> --- snip ---
> ...
> /sqlalchemy/sql/compiler.pyc in _requires_quotes(self, value)
> 1306 def _requires_quotes(self, value):
> 1307 """Return True if the given identifier requires
> quoting."""
> -> 1308 lc_value = value.lower()
> 1309 return (lc_value in self.reserved_words
> 1310 or self.illegal_initial_characters.match(value[0])
>
> AttributeError: 'NoneType' object has no attribute 'lower'
that's not a full stack trace but I would gather its looking for the name
of the constraint. so if you can provide names for your constraints that
would allow the DropConstraint to work. You'd need to do the same when
using Migrate so the instructions are more or less the same - make a
PrimaryKeyConstraint() with the correct name and issue DropConstraint, or
more simply just issue engine.execute("DROP CONSTRAINT
<constraint_name>").
Wolodja Wentland
>
> Wolodja Wentland wrote:
> > I can't drop these constraints as well. Even if i accept that my tables
> > are created with primary key definitions the recipe you showed me does
> > not work. It fails with:
> >
> > --- snip ---
> > ...
> > /sqlalchemy/sql/compiler.pyc in _requires_quotes(self, value)
> > 1306 def _requires_quotes(self, value):
> > 1307 """Return True if the given identifier requires
> > quoting."""
> > -> 1308 lc_value = value.lower()
> > 1309 return (lc_value in self.reserved_words
> > 1310 or self.illegal_initial_characters.match(value[0])
> >
> > AttributeError: 'NoneType' object has no attribute 'lower'
>
> that's not a full stack trace but I would gather its looking for the name
> of the constraint. so if you can provide names for your constraints
> that would allow the DropConstraint to work.
Yes you are right. That is not the full stack trace and the name of the
pkey constraint was missing. This, however, seems to be a bug within the
database reflection framework as these constraints have names within the
database and i am working on reflected tables here. It does not matter
whether i define names while creating the constraints as they are lost
as soon as i reflect the tables.
Any tips on how to dynamically create *Constraints? The inline_ddl idea
does not work and doing something like:
--- snip ---
tbl = metadata.tables[table_name]
...
new_pkc = PrimaryKeyConstraint(
*pkey_columns_for_table(table_name),
**{'name' : '%s_pkey'%(table)})
new_pkc._set_parent(tbl)
engine.execute(AddConstraint(new_pkc)
--- snip ---
... seems a bit hackish. It works however, but i am unsure if calling
_set_parent(tbl) really is safe and the correct way.
I am doing this because i have to create the same DB structure in a
bunch of databases and like to keep the table definition in one place.
The pkey_columns_for_table(..) function retrieves something like:
['foo_column', 'bar_column', ... ]
thanks
Wolodja Wentland
Michael Bayer
On Sep 8, 2009, at 7:01 PM, Wolodja Wentland wrote:
>
> Any tips on how to dynamically create *Constraints? The inline_ddl
> idea
> does not work and doing something like:
how about engine.execute("drop constraint pkey_%s" % table.name) ?
since PG is using a naming scheme for primary key constraints.
Adding the name of the PK to reflection is something that can be added
to PG's reflection sure but this is just to solve your immediate issue.