Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Error on generating the Model using Visual Studio Design Time Support DDEX Provider #20

Open
roji opened this issue May 12, 2016 · 5 comments
Open

Error on generating the Model using Visual Studio Design Time Support DDEX Provider #20

roji opened this issue May 12, 2016 · 5 comments

Comments

@roji
Copy link
Member

roji commented May 12, 2016

From @snehitvelma on June 25, 2015 6:32

Unable to generate the model because of the following exception: '

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> Npgsql.NpgsqlException: ERROR: 42883: function information_schema._pg_expandarray(smallint[]) does not exist
   at Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()
   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)
   at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
   at Npgsql.ForwardsOnlyDataReader.NextResultInternal()
   at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription)
   at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.Execute(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadDataTable[T](String sql, Func`2 orderByFunc, DataTable table, EntityStoreSchemaFilterObjectTypes queryTypes, IEnumerable`1 filters, String[] filterAliases)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadTableDetails(IEnumerable`1 filters)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadStoreSchemaDetails(IList`1 filters)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GetStoreSchemaDetails(StoreSchemaConnectionFactory connectionFactory)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.CreateStoreModel()
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GenerateModel(List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModels(String storeModelNamespace, ModelBuilderSettings settings, List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModel(ModelBuilderSettings settings, IVsUtils vsUtils, ModelBuilderEngineHostContext hostContext)'.
Loading metadata from the database took 00:00:09.5977853.
Generating the model took 00:00:06.7410389.
Unable to generate the model because of the following exception: 'System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> Npgsql.NpgsqlException: ERROR: 42883: function information_schema._pg_expandarray(smallint[]) does not exist
   at Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()
   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)
   at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
   at Npgsql.ForwardsOnlyDataReader.NextResultInternal()
   at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription)
   at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.Execute(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadDataTable[T](String sql, Func`2 orderByFunc, DataTable table, EntityStoreSchemaFilterObjectTypes queryTypes, IEnumerable`1 filters, String[] filterAliases)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadTableDetails(IEnumerable`1 filters)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadStoreSchemaDetails(IList`1 filters)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GetStoreSchemaDetails(StoreSchemaConnectionFactory connectionFactory)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.CreateStoreModel()
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GenerateModel(List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModels(String storeModelNamespace, ModelBuilderSettings settings, List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModel(ModelBuilderSettings settings, IVsUtils vsUtils, ModelBuilderEngineHostContext hostContext)'.
Loading metadata from the database took 00:00:05.7852829.
Generating the model took 00:02:07.6435015.
Unable to generate the model because of the following exception: 'System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> Npgsql.NpgsqlException: ERROR: 42883: function information_schema._pg_expandarray(smallint[]) does not exist
   at Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()
   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)
   at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
   at Npgsql.ForwardsOnlyDataReader.NextResultInternal()
   at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription)
   at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.Execute(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadDataTable[T](String sql, Func`2 orderByFunc, DataTable table, EntityStoreSchemaFilterObjectTypes queryTypes, IEnumerable`1 filters, String[] filterAliases)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadTableDetails(IEnumerable`1 filters)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadStoreSchemaDetails(IList`1 filters)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GetStoreSchemaDetails(StoreSchemaConnectionFactory connectionFactory)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.CreateStoreModel()
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GenerateModel(List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModels(String storeModelNamespace, ModelBuilderSettings settings, List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModel(ModelBuilderSettings settings, IVsUtils vsUtils, ModelBuilderEngineHostContext hostContext)'.
Loading metadata from the database took 00:00:06.0284166.
Generating the model took 00:00:05.5605231.

Copied from original issue: npgsql/npgsql#651
@roji roji added ef6 labels May 12, 2016
@roji
Copy link
Member Author

roji commented May 12, 2016

From @kenjiuno on June 25, 2015 12:29

Hi @snehitvelma
Thanks for reporting it!
I'll check about this problem.

> Npgsql.NpgsqlException: ERROR: 42883: function information_schema._pg_expandarray(smallint[]) does not exist

What version of PostgreSQL server do you connect to?

@roji
Copy link
Member Author

roji commented May 12, 2016

From @snehitvelma on June 25, 2015 13:24

I'm connecting to Amazon Red Shift.

@roji
Copy link
Member Author

roji commented May 12, 2016

From @kenjiuno on June 26, 2015 4:35

Ok, I'll check the way how to fix this issue. This may take some while...

@roji
Copy link
Member Author

roji commented May 12, 2016

From @kenjiuno on December 4, 2015 0:18

Note to self:

conkey and confkey in pg_constraint are int2[].

They need to be expanded from array to rows by using information_schema._pg_expandarray, unnest or such.
However redshift doesn't implement it.

https://github.com/npgsql/npgsql/blob/58ecffc78c10ba0e458b2059e0cdefb23710381c/src/EntityFramework6.Npgsql/NpgsqlSchema.ssdl#L112-L114

An idea is to fully rewrite all SQL queries in ssdl using only information_schema.

@roji
Copy link
Member Author

roji commented May 12, 2016

From @kenjiuno on December 10, 2015 11:56

I have tried using information_schema but it doesn't help... My new NpgsqlSchema.ssdl

It will need another tool in order to scaffold Entity Data Model from Redshift db.

Official schema views are: PG_TABLE_DEF and SVV_TABLE_INFO.
Redshift db doesn't rely on pk/fk constraints. Define Primary Key and Foreign Key Constraints

So mark [Key] to the possible primary key column property. Just trick the EF!

Some important schemata cannot be queried: select * from information_schema.key_column_usage

An error occurred when executing the SQL command:
select * from information_schema.key_column_usage

[Amazon](500310) Invalid operation: must be superuser to examine "max_index_keys";

Execution time: 0.12s
1 statement failed.

So, EF's scaffolding cannot be done against Redshift...

Unable to Access information_schema.constraint_column_usage - SuperUser?
https://forums.aws.amazon.com/thread.jspa?threadID=133514

We can define primary keys and foreign keys on Redshift db.
However there is no good way to query them via information_schema.

@roji roji mentioned this issue May 12, 2016
Closed
@roji roji removed ef6 labels May 12, 2016
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@roji