error selecting from a serial field

[Former user]

Hi all,

I think I found a bug in the last SQLAlchemy trunk.

Starting from an existing database, that pg_dump sees like this:

CREATE SCHEMA __test;
SET search_path = __test, pg_catalog;
CREATE SEQUENCE table1_id_seq
    INCREMENT 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1;

CREATE TABLE table1(
    id integer DEFAULT nextval('table1_id_seq'::regclass) PRIMARY KEY,
    payload varchar(100)
);

and having recreated it with psql, when I try to insert a record with SQLAlchemy:

>>> import sqlalchemy as sa
>>> engine = sa.create_engine('postgres://...')
>>> metadata = sa.BoundMetaData(engine)
>>> table1 = sa.Table('table1', metadata, schema='__test', autoload=True)
>>> table1.insert().execute(payload='foo')

I obtain:

Traceback (most recent call last):
  File "test-sqlalchemy.py", line 28, in <module>
    table1.insert().execute(payload='foo')
    [snip](snip)
  File "/home/david/Projects/sqlalchemy/lib/sqlalchemy/engine/default.py",
      line 207, in _process_defaults
    newid = drunner.get_column_default(c)
  File "/home/david/Projects/sqlalchemy/lib/sqlalchemy/databases/postgres.py",
      line 533, in get_column_default
    c = self.proxy("select %s" % column.default.arg)
    [snip](snip)
sqlalchemy.exceptions.SQLError: (ProgrammingError) relation "table1_id_seq"
  does not exist "select nextval('table1_id_seq'::regclass)" {}

Changing the table definition to include the database schema yields the same error:

CREATE TABLE table1(
    id integer DEFAULT nextval('__test.table1_id_seq'::regclass) PRIMARY KEY,
    payload varchar(100)
);

The attached patch fixes the problem, and the test-suite passes (except for two tests, that were failing anyway with PostgreSQL: sql.query.QueryTest and engine.pool.PoolTest).

On another note, I noticed another oddity. I attach also the code I use to isolate the bug:

If I run this script without the patch, the error does not occur.

But if I run this program two times, the first time only creating the database structure, and the second time only inserting some data (and commenting out the structure creation), the error does occur.

HTH david

[Former user]

the patch

[Former user]

The test program

[Mike Bayer]

is this error limited to tables that are reflected ? if so, can we move all that regexp logic into the reflecttable() method so that it is not invoked all the time ?

[Former user]

You are right, but the Sequence object doesn't support the schema attribute so, without the reflection, I have to declare the table as:

table1 = sa.Table('table1', metadata,
    sa.Column('id', sa.Integer, sa.Sequence('__test.table1_id_seq'), primary_key=True),
    sa.Column('payload', sa.String(100)),
    schema='__test')

This works without the patch.

[Former user]

The new patch, moved to the reflection code.

[Former user]

The new patch modify the reflecttable() method. But I don't know anything about these lines:

#python
if self.use_information_schema:
    ischema.reflecttable(connection, table, ischema_names)
else:
    ...

This condition can bypass my patch, so I don't know how good is my code.

[Mike Bayer]

ive committed this patch in changeset:2271, but i haven't run the test case (i usually like to add a unit test, but the test suite does not yet create separate schemas for PG). thanks for the patch.