Incorrect SQL generated for INSERT into PostgreSQL
Nathan Robertson
I've come across a bug (hopefully in my configuration) where
SQLAlchemy will generate an INSERT statement for a table with a SERIAL
primary key which PostgreSQL errors on. I'm running EnterpriseDB's
Postgres Plus 8.4 on openSUSE 11.4 x64, with Python 2.7, SQLAlchemy
0.7.2 and psycopg2 2.4.2. From the PostgreSQL logs:
2011-09-26 15:38:52 ESTLOG: statement: INSERT INTO test.customer
(custid, name) VALUES (nextval('"test.customer_custid_seq"'), E'Test')
RETURNING test.customer.custid
2011-09-26 15:38:52 ESTERROR: relation "test.customer_custid_seq"
does not exist at character 58
Running this query in psql from the command line reproduces the error
accurately. But if I remove the double quotes that are inside the
single quotes that are around the sequence name in the query (ie.
change the custid to be nextval('test.customer_custid_seq')) in the
command line psql the INSERT works fine. So, SQLAlchemy for some
reason is adding the "" which PostgreSQL doesn't like.
Could somebody help me work out why SQLAlchemy is adding in the extra
"" around the sequence name?
The test database I'm running this against looks like this:
create schema test;
create table test.customer (
custid bigserial not null,
name varchar(60) not null,
primary key (custid)
);
The code looks like this:
from sqlalchemy import create_engine, Table, Column, Sequence,
MetaData, ForeignKey, BigInteger, CHAR, Date, Integer, NUMERIC,
SmallInteger, String, TIMESTAMP
from sqlalchemy.orm import mapper, relationship, backref,
sessionmaker, scoped_session
from sqlalchemy.sql import and_
# Definitions
metadata = MetaData()
customer_table = Table('customer', metadata,
Column('custid', Integer, Sequence('test.customer_custid_seq'),
primary_key=True),
Column('name', String(60)),
schema='test')
class Customer(object):
pass
mapper(Customer, customer_table, properties={})
# Database connection
conn_args = {
'host':'/tmp/',
'database':'test',
}
engine = create_engine('postgresql+psycopg2://', connect_args =
conn_args)
Session = scoped_session(sessionmaker(bind=engine))
cust = Customer()
cust.name = 'Test'
Session.merge(cust)
Session.commit()
The output from running that all that looks like this:
nathanr@coopers:~/Desktop> createdb test
nathanr@coopers:~/Desktop> psql -f test.sql test
CREATE SCHEMA
psql:test.sql:7: NOTICE: CREATE TABLE will create implicit sequence
"customer_custid_seq" for serial column "customer.custid"
psql:test.sql:7: NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "customer_pkey" for table "customer"
CREATE TABLE
nathanr@coopers:~/Desktop> python test.py
Traceback (most recent call last):
File "test.py", line 28, in <module>
Session.commit()
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
scoping.py", line 113, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
session.py", line 617, in commit
self.transaction.commit()
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
session.py", line 293, in commit
self._prepare_impl()
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
session.py", line 277, in _prepare_impl
self.session.flush()
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
session.py", line 1493, in flush
self._flush(objects)
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
session.py", line 1562, in _flush
flush_context.execute()
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
unitofwork.py", line 327, in execute
rec.execute(self)
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
unitofwork.py", line 471, in execute
uow
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
mapper.py", line 2174, in _save_obj
execute(statement, params)
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/engine/
base.py", line 1399, in execute
params)
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/engine/
base.py", line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/engine/
base.py", line 1640, in _execute_context
context)
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/engine/
base.py", line 1633, in _execute_context
context)
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/engine/
default.py", line 325, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation
"test.customer_custid_seq" does not exist
LINE 1: ...INTO test.customer (custid, name) VALUES
(nextval('"test.cus...
^
'INSERT INTO test.customer (custid, name) VALUES
(nextval(\'"test.customer_custid_seq"\'), %(name)s) RETURNING
test.customer.custid' {'name': 'Test'}
nathanr@coopers:~/Desktop>
PostgreSQL Logs:
2011-09-26 15:38:52 ESTLOG: statement: BEGIN
2011-09-26 15:38:52 ESTLOG: statement: INSERT INTO test.customer
(custid, name) VALUES (nextval('"test.customer_custid_seq"'), E'Test')
RETURNING test.customer.custid
2011-09-26 15:38:52 ESTERROR: relation "test.customer_custid_seq"
does not exist at character 58
2011-09-26 15:38:52 ESTSTATEMENT: INSERT INTO test.customer (custid,
name) VALUES (nextval('"test.customer_custid_seq"'), E'Test')
RETURNING test.customer.custid
2011-09-26 15:38:52 ESTLOG: statement: ROLLBACK
2011-09-26 15:38:52 ESTLOG: unexpected EOF on client connection
Michael Bayer
On Sep 26, 2011, at 2:50 AM, Nathan Robertson wrote:
> Column('custid', Integer, Sequence('test.customer_custid_seq'),
> primary_key=True),
for the Sequence, as with all schema items, you need to specify the "schema" portion separately so that SQLAlchemy knows where each token starts and ends:
Sequence("customer_custid_seq", schema="test")
Nathan Robertson
Yep, that's it. Solved the problem. Thanks a lot for your help.
Nathan Robertson
Actually, I just noticed that the same thing doesn't apply to foreign
keys. I've actually got code in production which does something like:
Column('custid', BigInteger, ForeignKey('test.customer.custid'),
primary_key=True)
What's the reason for the distinction requiring the schema to be split
out in a Sequence, but not in the case of a ForeignKey?
Michael Bayer
OK that's a great question. This is a case where looking at it from my perspective, I'd never see it that way, but from someone coming into it from the API usage side, yeah that seems pretty obvious. Maybe ForeignKey and ForeignKeyConstraint should accept a "schema" argument instead, though if that were the case I'd deprecate the other method since we try not to have multiple ways to do something...but that would be hard at this point since there's thousands of apps that do it the current way. Maybe having "schema" as an option and just having two ways to do it....arg. Would have to think about it. I added http://www.sqlalchemy.org/trac/ticket/2288 for this idea.
Basically from the ForeignKey perspective it's an element of a Table, and the directives to generate a foreign key constraint don't include the schema where the constraint itself lives; that's part of its parent Table and is already handled. The column it references, that belongs to a table which may have a schema, but ForeignKey doesn't need the separate schema information because it ultimately is pointing to an actual Column object on another Table, which also has "schema" taken care of. The string "x.y.z" sent to ForeignKey is only used internally to locate another Table/Column within the MetaData. Whereas Table, Sequence, we need to quote the individual elements so "schema" has always been separate at that level.