What is failing is the second query. I filed a ticket in the trac system
(http://www.sqlalchemy.org/trac/ticket/71).
What is strange is that no error is raised by postgres, no warning that we
don't have enought permission to do a select.
How did you work out the 2. query? is it documented in some pg docs?
Tanks again
sandro
*:-)
On Tue, Feb 21, 2006 at 11:30:30AM -0500, Michael Bayer wrote:
> Ah, no idea, not too familiar with different users mixing like that
> in postgres.
>
> try out these two queries with your PG database and see what you get,
> this is how it reflects the tables currently. the primary key
> columns are determined by the second query. if we can figure out
> what else the query needs to adjust for the user scenario we can
> update that.
>
> query # 1:
>
> SELECT columns.table_schema, columns.table_name, columns.column_name,
> columns.is_nullable, columns.data_type, columns.ordinal_position,
> columns.character_maximum_length, columns.numeric_precision,
> columns.numeric_scale, columns.column_default
> FROM information_schema.columns
> WHERE columns.table_name = %(columns_table_name)s AND
> columns.table_schema = %(columns_table_schema)s ORDER BY
> columns.ordinal_position
>
> bindparams: {'columns_table_name': 'engine_users',
> 'columns_table_schema': 'public'}
>
> query # 2:
>
> SELECT table_constraints.constraint_name AS
> table_constraints_constraint_name, table_constraints.constraint_type
> AS table_constraints_constraint_type, table_constraints.table_name AS
> table_constraints_table_name, key_column_usage.table_schema AS
> key_column_usage_table_schema, key_column_usage.table_name AS
> key_column_usage_table_name, key_column_usage.column_name AS
> key_column_usage_column_name, key_column_usage.constraint_name AS
> key_column_usage_constraint_name,
> constraint_column_usage.table_schema AS
> constraint_column_usage_table_schema,
> constraint_column_usage.table_name AS
> constraint_column_usage_table_name,
> constraint_column_usage.column_name AS
> constraint_column_usage_column_name,
> constraint_column_usage.constraint_name AS
> constraint_column_usage_constraint_name
> FROM information_schema.table_constraints JOIN
> information_schema.constraint_column_usage ON
> constraint_column_usage.constraint_name =
> table_constraints.constraint_name JOIN
> information_schema.key_column_usage ON
> key_column_usage.constraint_name =
> constraint_column_usage.constraint_name
> WHERE table_constraints.table_name = %(table_constraints_table_name)s
> AND table_constraints.table_schema = %(table_constraints_table_schema)s
>
> bindparams: {'table_constraints_table_schema': 'public',
> 'table_constraints_table_name': 'engine_users'}
>
>
> On Feb 21, 2006, at 5:44 AM, Sandro Dentella wrote:
>
> >hi all,
> >
> >Sqlalchemy fails to find primary keys in certain situations (if my
> >test/comprehension is correct... ;-). It seems that it fails when
> >looking
> >for a table that has been built by another user. Create 2
> >identical tables
> >as follows and make them property of 2 different users:
> >
> >
> > CREATE USER user1;
> > CREATE USER user2;
> >
> > SET SESSION AUTHORIZATION 'user1';
> > DROP TABLE table1;
> > CREATE TABLE table1 (
> > user_key varchar(3) NOT NULL PRIMARY KEY
> > );
> > GRANT ALL on table1 to PUBLIC;
> >
> > SET SESSION AUTHORIZATION 'user2';
> > DROP TABLE table2;
> > CREATE TABLE table2 (
> > user_key varchar(3) NOT NULL PRIMARY KEY
> > );
> > GRANT ALL on table2 to PUBLIC;
> >
> >
> >now connect as user1 and execute:
> >
> > tables = [ 'table1', 'table2' ]
> >
> > for table_name in tables:
> > table = Table(table_name, engine, autoload=True)
> > print table_name
> > print [c for c in table.columns]
> > print table.primary_key
> >
> >
> >you get something like this:
> >
> >table1
> >[Column('user_key',<sqlalchemy.databases.postgres.PGString object
> >at
> >0xb6cabf2c>,key='user_key',primary_key=True,nullable=False,hidden=Fals
> >e,default=None)]
> >[Column('user_key',<sqlalchemy.databases.postgres.PGString object
> >at
> >0xb6cabf2c>,key='user_key',primary_key=True,nullable=False,hidden=Fals
> >e,default=None)]
> >
> >table2
> >[Column('user_key',<sqlalchemy.databases.postgres.PGString object
> >at
> >0xb6cb086c>,key='user_key',primary_key=False,nullable=False,hidden=Fal
> >se,default=None)]
> >[]
> >
> >Where primary_key is True for the first and False for the second.
> >Why that? is this a bug or I'm missing something?
> >
> >
> >TIA
> >sandro
> >*:-)
> >
> >
> >--
> >Sandro Dentella *:-)
> >e-mail: [EMAIL PROTECTED]
> >http://www.tksql.org TkSQL Home page - My GPL work
> >
> >
> >-------------------------------------------------------
> >This SF.net email is sponsored by: Splunk Inc. Do you grep through
> >log files
> >for problems? Stop! Download the new AJAX search engine that makes
> >searching your log files as easy as surfing the web. DOWNLOAD
> >SPLUNK!
> >http://sel.as-us.falkag.net/sel?
> >cmd=lnk&kid=103432&bid=230486&dat=121642
> >_______________________________________________
> >Sqlalchemy-users mailing list
> >Sqlalchemy-users@lists.sourceforge.net
> >https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
--
Sandro Dentella *:-)
e-mail: [EMAIL PROTECTED]
http://www.tksql.org TkSQL Home page - My GPL work
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
