Postgres does not reflect sequences which were created by SERIAL

[Former user]

Without the foreign key, it works fine and with sqlite instead of postgres, it works fine too.

See attached file for a test case.

It fails with the following traceback:

Traceback (most recent call last):
  File "test_autoload.py", line 20, in <module>
    meta2.create_all()   # this fails.
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/schema.py", line 1147, in create_all
    connectable.create(self, checkfirst=checkfirst, tables=tables)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/engine/base.py", line 681, in create
    self._run_visitor(self.dialect.schemagenerator, entity, connection=connection, **kwargs)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/engine/base.py", line 711, in _run_visitor
    element.accept_visitor(visitorcallable(conn, **kwargs))
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/schema.py", line 1169, in accept_visitor
    visitor.visit_metadata(self)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/ansisql.py", line 805, in visit_metadata
    table.accept_visitor(self)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/sql.py", line 2095, in accept_visitor
    visitor.visit_table(self)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/ansisql.py", line 840, in visit_table
    self.execute()
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1179, in execute
    return self.connection.execute(self.buffer.getvalue())
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/engine/base.py", line 502, in execute
    return Connection.executors[c](c)(self, object, *multiparams, **params)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/engine/base.py", line 517, in execute_text
    self._execute_raw(context)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/engine/base.py", line 566, in _execute_raw
    self._execute(context)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/engine/base.py", line 583, in _execute
    raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (ProgrammingError) syntax error at or near "$1"
LINE 6:   CONSTRAINT $1 FOREIGN KEY(test_id) REFERENCES test (id)
                     ^
 "\nCREATE TABLE test (\n\tid INTEGER DEFAULT nextval('public.test_id_seq'::text) NOT NULL, \n\ttest_id INTEGER, \n\tPRIMARY KEY (id), \n\t CONSTRAINT $1 FOREIGN KEY(test_id) REFERENCES test (id)\n)\n\n" {}

[Former user]

Sorry for the double post (I hit my browser "back" button before submitting the attachment)... BTW: did I suddenly become blind or is there no way anymore to precise who's reporting the bug (I'm referring to the "Please put your email address or some kind of name so I can distinguish who is posting what." comment)?

[Mike Bayer]

I cant reproduce the error you are getting, however there is a known issue with postgres in that the sequence created by "SERIAL" becomes the "default" on the primary key column. after the drop, the sequence is also dropped. because this sequence is not known by SA to be a Sequence construct, it does not get re-created, but the column is created not as SERIAL but as having an explicit default with the non-existent sequence name (just turn on echo=True to see this happening).

so workaround are a. put an explicit Sequence on your table or b. remove the "default" before re-creating, i.e.

table2 = Table('test', meta2, autoload=True)
table2.c.id.default = None
meta2.drop_all()
meta2.create_all()   # this fails.

the script then works for me.

id like to mark this as "wontfix", but it is theoretically possible for postgres.py to figure out that "nextval" in the reflection result set means a "Sequence" and then to perform separate reflection steps to attempt reflecting the sequence, then on not finding it creating its own Sequence, but this is a lot of effort for something that can be worked around in two distinct ways. feel free to attempt creating a patch.

[Mike Bayer]

also the trac upgrade seems to have a lot of hardcoded defaults which used to be more flexible so feel free to use the "CC" field for now.

[Mike Bayer]

this works fine and the default is reflected:

print table2.c.id.server_default.arg

nextval('test_id_seq1'::regclass)

its not as a Sequence but we do know that the col is SERIAL.