metadata.create_all with postgres 9.3.3 on AWS RDS

[AM]

Hi all.

In my app I have a bootstrap method that calls:

metadata.create_all(checkfirst)

against a postgres RDS instance.

The tables already exist however the query emitted seems to be:

CREATE TABLE user_roles (
user_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY(user_id) REFERENCES "user" (id),
FOREIGN KEY(role_id) REFERENCES role (id)
)

Per my understanding this query should not even be emitted or if it is
there should be a 'IF NOT EXISTS' somewhere in there.

I was wondering if someone could point out what I am doing wrong?

Thanks.
AM

[Michael Bayer]

Post by AM
Hi all.
metadata.create_all(checkfirst)
against a postgres RDS instance.
CREATE TABLE user_roles (
user_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY(user_id) REFERENCES "user" (id),
FOREIGN KEY(role_id) REFERENCES role (id)
)
Per my understanding this query should not even be emitted or if it is there should be a 'IF NOT EXISTS' somewhere in there.
I was wondering if someone could point out what I am doing wrong?

it doesn't use "IF NOT EXISTS". it runs a query against pg_namespace. Turn on echo=True on your engine and you'll see something like:

select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s
{'name': u'a'}

CREATE TABLE a (
id SERIAL NOT NULL,
PRIMARY KEY (id)
)

if this query isn't succeeding, perhaps you have a schema mismatch of some kind. if the CREATE TABLE does succeed then the default schema is probably not what you expect.

[AM]

Post by Michael Bayer

Post by AM
Hi all.
metadata.create_all(checkfirst)
against a postgres RDS instance.
CREATE TABLE user_roles (
user_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY(user_id) REFERENCES "user" (id),
FOREIGN KEY(role_id) REFERENCES role (id)
)
Per my understanding this query should not even be emitted or if it is there should be a 'IF NOT EXISTS' somewhere in there.
I was wondering if someone could point out what I am doing wrong?

select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s
{'name': u'a'}
CREATE TABLE a (
id SERIAL NOT NULL,
PRIMARY KEY (id)
)
if this query isn't succeeding, perhaps you have a schema mismatch of some kind. if the CREATE TABLE does succeed then the default schema is probably not what you expect.

So I do get the pg_namespace query with one row in the result. What I am
a it confused about is why the CREATE is emitted since the table exists?

Thanks.
AM

[Michael Bayer]

Post by Michael Bayer

Post by AM
Hi all.
metadata.create_all(checkfirst)
against a postgres RDS instance.
CREATE TABLE user_roles (
user_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY(user_id) REFERENCES "user" (id),
FOREIGN KEY(role_id) REFERENCES role (id)
)
Per my understanding this query should not even be emitted or if it is there should be a 'IF NOT EXISTS' somewhere in there.
I was wondering if someone could point out what I am doing wrong?

select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s
{'name': u'a'}
CREATE TABLE a (
id SERIAL NOT NULL,
PRIMARY KEY (id)
)
if this query isn't succeeding, perhaps you have a schema mismatch of some kind. if the CREATE TABLE does succeed then the default schema is probably not what you expect.

So I do get the pg_namespace query with one row in the result. What I am a it confused about is why the CREATE is emitted since the table exists?

That output above isn't actually showing any result rows, the {"name"} there are the params in the query. use echo='debug' to actually see the result rows, if any.