[DC-919] Import/Pgsql.php: listTableColumns - SQL failure with PostgreSQL Created: 07/Nov/10  Updated: 09/Apr/12

Status: Open
Project: Doctrine 1
Component/s: Import/Export
Affects Version/s: 1.2.3
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Christian Vogel Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 4
Labels: None

Postgres Import Schema

Attachments: File trac_9152_patch_for_Pgsql.php.diff    



this issue was reported at the symfony project which uses Doctrine 1.2.3:
"php symfony doctrine:build-schema failure with PostgreSQL for 1.4.7 and 1.4.8 version"

The SQL Statement 'listTableColumns' fails with an SQL-Error "missing from-clause"
I can reproduce the error directly in psql or pgadmin. The SQL Statement seems related to DC-697

Even when i turn on the add_missing_from option on the postgres-server it fails with "missing relation".

Now it seems to me, you already fixed this bug in the current 1.2 branch, because the current SQL-Statement is different and it works for me in psql/pgadmin.

Could you please close this ticket, if you already fixed this issue, or confirm if it's still an issue?
Attached you find my proposed patch at the symfony project . the current statement in the branch looks too different from my version, so i am not sure to use this patch directly. Tell me if I should work out a proper patch.

SQLSTATE[42P01]: Undefined table: 7 ERROR:  missing FROM-clause entry for table "t"                                               
 	  LINE 6: ...                                                  t.typtype ...                                                       
 	                                                               ^. Failing Query: "SELECT                                           
 	                                                       ordinal_position as attnum,                                                 
 	                                                       column_name as field,                                                       
 	                                                       udt_name as type,                                                           
 	                                                       data_type as complete_type,                                                 
 	                                                       t.typtype AS typtype,                                                       
 	                                                       is_nullable as isnotnull,                                                   
 	                                                       column_default as default,                                                   
 	                                                         SELECT 't'                                                                 
 	                                                           FROM pg_index, pg_attribute a, pg_class c, pg_type t                     
 	                                                           WHERE c.relname = table_name AND a.attname = column_name                 
 	                                                           AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid           
 	                                                           AND c.oid = pg_index.indrelid AND a.attnum = ANY (pg_index.indkey)       
 	                                                           AND pg_index.indisprimary = 't'                                         
 	                                                           AND format_type(a.atttypid, a.atttypmod) NOT LIKE 'information_schema%' 
 	                                                       ) as pri,                                                                   
 	                                                       character_maximum_length as length                                           
 	                                                     FROM information_schema.COLUMNS                                               
 	                                                     WHERE table_name = 'matable'                                   
 	                                                     ORDER BY ordinal_position"  

Comment by Nahuel Alejandro Ramos [ 09/Nov/10 ]

We apply the diff patch you submit and works perfect. We are using Doctrine 1.2.3 with PostgreSQL 8.4.
We could generates models from database with generateModelsFromDb() method.
Please add this patch to a new release.
Thank you very much.

Comment by Tim Hemming [ 23/Nov/10 ]

We have applied this patch directly to our server-wide Doctrine library and it works fine. We look forward to it becoming a part of the Doctrine distribution.

Comment by Christopher Hotchkiss [ 19/Dec/10 ]

I can confirm that this bug also affects symfony 1.4.8 and the attached fix works perfectly!

Comment by David Landgren [ 21/Feb/11 ]

Confirmed to fix crash with symfony 1.3.8

Comment by Cesar Miggiolaro [ 09/Apr/12 ]

I use the version 1.4.17 and also had the error with postgres 9.1. Applying the correction suggested in DIFF. The system worked.

Generated at Wed Nov 25 19:24:38 EST 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.