Refresh Relation Model Issue

Posts   
 
    
Posts: 34
Joined: 22-Oct-2014
# Posted on: 03-Sep-2015 14:26:18   

Hi again! There is an issue with updated Npgsql v3.0.1... But now it's related with refreshing of relational model and tables in DB which have references to itself. For Example we have table Jobs, that created with next script:

CREATE TABLE public.Job
(
    Id bigint NOT NULL DEFAULT new_id() PRIMARY KEY,
    TypeFk bigint NOT NULL REFERENCES public.JobType,
    ParentJobFk bigint REFERENCES public.Job,
    ExecMethod varchar(250),
    Arguments text,
    Status varchar(50) NOT NULL,
    Comment varchar(500),
    Created timestamp NOT NULL DEFAULT utcnow(),
    Modified timestamp NOT NULL DEFAULT utcnow()
);

As we see table have ParentJobFk field that is foreign key reference to this table itself.

So that is the problem, when I trying to refresh relational model from DB i get error:


Exception message:
-------------------------------
Exception type: EndOfStreamException

LLBLGen Pro version 4.2. Build October 3rd, 2014
-----[Core exception]--------------------
   в Npgsql.NpgsqlBuffer.Ensure(Int32 count)
   в Npgsql.TypeHandler.Read[T](NpgsqlBuffer buf, Int32 len, FieldDescription fieldDescription)
   в Npgsql.TypeHandler.Read[T](DataRowMessage row, Int32 len, FieldDescription fieldDescription)
   в Npgsql.TypeHandler`1.ReadValueAsObject(DataRowMessage row, FieldDescription fieldDescription)
   в Npgsql.NpgsqlDataReader.GetValue(Int32 ordinal)
   в Npgsql.NpgsqlDataReader.GetValues(Object[] values)
   в System.Data.ProviderBase.SchemaMapping.LoadDataRow()
   в System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
   в System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   в System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   в System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   в System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   в System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   в SD.LLBLGen.Pro.DBDrivers.PostgreSql.PostgreSqlCatalogRetriever.RetrieveForeignKeys(DBCatalog catalogMetaData)
   в SD.LLBLGen.Pro.DBDrivers.PostgreSql.PostgreSqlCatalogRetriever.<ProduceAdditionalActionsToPerform>b__5(DBCatalog catalog)
   в SD.LLBLGen.Pro.DBDriverCore.DBCatalogRetriever.PerformAdditionalActions(String catalogName, DBCatalog catalogMetaData, IEnumerable`1 actionsToPerform)
   в SD.LLBLGen.Pro.DBDriverCore.DBCatalogRetriever.RetrieveCatalog(String catalogName, List`1 elementsToRetrieve)
   в SD.LLBLGen.Pro.DBDriverCore.DBDriverBase.PopulateCatalogs(Dictionary`2 callBacks, Dictionary`2 elementsToRetrieve)
   в SD.LLBLGen.Pro.Gui.Classes.GuiController.ObtainMetaDataFromDatabase(DBDriverBase driverUsed, Dictionary`2 selectedElements)

When i dropped this field i was able to refresh without problems. Can you help me to resolve this issue?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 03-Sep-2015 16:01:44   

We execute this query:

SELECT  c.conname AS constraint_name, pkn.nspname AS pk_schema, fkn.nspname AS fk_schema, 
        fkt.relname AS fk_table, pkt.relname AS pk_table, pkf.attname AS pk_field, fkf.attname AS fk_field, 
        c.confkey AS pk_field_ordinals, c.conkey AS fk_field_ordinals, pkf.attnum AS pk_field_ordinal, 
        fkf.attnum AS fk_field_ordinal, c.confupdtype AS fk_update_rule, c.confdeltype AS fk_delete_rule 
FROM pg_constraint c 
    INNER JOIN pg_namespace n ON c.connamespace = n.oid 
    INNER JOIN pg_class pkt ON c.confrelid=pkt.oid 
    INNER JOIN pg_namespace pkn ON pkt.relnamespace = pkn.oid 
    INNER JOIN pg_class fkt ON c.conrelid=fkt.oid 
    INNER JOIN pg_namespace fkn ON fkt.relnamespace = fkn.oid 
    INNER JOIN pg_attribute pkf ON pkf.attnum = ANY(c.confkey) AND pkf.attrelid = pkt.oid 
    INNER JOIN pg_attribute fkf ON fkf.attnum = ANY(c.conkey) AND fkf.attrelid = fkt.oid 
WHERE c.contype='f' ORDER BY fk_schema ASC, fk_table ASC, constraint_name ASC, fk_field ASC, pk_schema ASC

using a simple DbDataAdapter.Fill(Datatable) call.

Looks like a bug in Npgsql. I'll file a bug there.

edit: https://github.com/npgsql/npgsql/issues/747

Frans Bouma | Lead developer LLBLGen Pro
Posts: 34
Joined: 22-Oct-2014
# Posted on: 03-Sep-2015 16:09:39   

Thanks! I'll wait answer from npgsql team!

Posts: 34
Joined: 22-Oct-2014
# Posted on: 04-Sep-2015 12:55:34   

Hi there! I'm want to say about this bug, that we have not only one table with self references, but tables that was mapped before upgrading Npgsql are updating successfully even after update of npgsql. So the exception is throwing when i'm trying to map new table with self-reference. And the other thing is that if i delete old Entity from LLBLGen project and try to refresh relation model i got this exception too.

UPD: Just tested Npgsql.3.0.2-beta0027 from unstable feed, as i was recommended in git thread, and i approve that bug is not reproducing with this release of npgsql. So we'll wait stable 3.0.2 and will use it!

Thanks again, Frans! For you operational support and help!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 04-Sep-2015 13:31:51   

Bug in npgsql. 3.0.2 fixes it.

Frans Bouma | Lead developer LLBLGen Pro