On Oct 4, 2011, at 12:17 PM, rdunklau wrote: > Hello. > > I'm having a problem with sqlalchemy and postgresql, and I really > don't know what should I do to correct it. > > I'm using sqlalchemy. 0.7.3 with postgresql 2.7.2, with the psycopg2 > driver and dialect.
I'm not aware of what versioning system "2.7.2" might be a part of, that is not any such version I'm aware of. PG was at version 6 in 1997 and the current version is 9 - the oldest PGs in use would be version 8 or 7 (see http://en.wikipedia.org/wiki/PostgreSQL#Major_releases for a full history). Can you confirm the version of Postgresql in use ? Data catalog errors tend to be version dependent. > > Looking through the logs, I've found that the following query is used > to find primary keys: > > > SELECT attname FROM pg_attribute > WHERE attrelid = ( > SELECT indexrelid FROM pg_index i > WHERE i.indrelid = %(table_oid)s > AND i.indisprimary = 't') > ORDER BY attnum > > I've tried to run this query myself, and the result is 'id', and not > 'product_id'. > While this kind of error has been seen in other areas, currently I'm not able to reproduce - PG has altered how they handle their data catalogs across versions so the exact version of PG here is of key importance. I've also committed the corresponding test for below to the current tip: CREATE TABLE t ( id SERIAL NOT NULL, PRIMARY KEY (id) ) COMMIT alter table t rename id to t_id COMMIT SELECT attname FROM pg_attribute WHERE attrelid = ( SELECT indexrelid FROM pg_index i WHERE i.indrelid = %(table_oid)s AND i.indisprimary = 't') ORDER BY attnum INFO:sqlalchemy.engine.base.Engine:{'table_oid': 11096193} DEBUG:sqlalchemy.engine.base.Engine:Col ('attname',) DEBUG:sqlalchemy.engine.base.Engine:Row (u't_id',) the correct value "t_id" is returned. > Is this a postgresql bug for not updating the attribute name in the > system catalog ? probably > Why did sqlalchemy choose to query the system tables instead of the > information_schema ? we used information_schema many years ago. At least back then, its performance was orders of magnitude slower and it also does not return complete data. > I'm not really sure, but it looks like the system tables are not meant > to be used for this kind of introspection. The information schema views are extremely slow for big reflection jobs. The PG tables also have information on PG specific features, which we need to get at: "The information schema views do not, however, contain information about PostgreSQL-specific features; to inquire about those you need to query the system catalogs or other PostgreSQL-specific views. " Most articles/howtos/help/documentation I've ever seen on the subject use the pg_ tables, some examples: http://crafted-software.blogspot.com/2011/03/get-all-column-names-of-table-in.html http://www.polak.ro/postgresql-select-tables-names.html http://www.alberton.info/postgresql_meta_info.html (illustrates using both INFO_SCHEMA and pg_ tables) I've also analyzed the queries emitted by the pgAdmin3 tool and it uses the pg_ tables. We've had lots of bugs with PG reflection and the system tables have always ultimately had the right information, though sometimes very hard to get at. > Does anyone have any workaround (besides the obvious: remap the table > manually) ? SQLAlchemy can attempt to work around the issue if a known workaround for the PG version in question is present, or alternatively you can provide the primary key column of the Table manually using the technique described at http://www.sqlalchemy.org/docs/core/schema.html#overriding-reflected-columns . > > Thank you. > > -- > Ronan Dunklau > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
