HicServices/RDMP

relation "z_dataloadtaskstatus" does not exist when creating logging server on Postgres

Closed this issue · 3 comments

rkm commented

Describe the bug

When trying to create a new logging server on a blank Postgres database, the command crashes with relation "z_dataloadtaskstatus" does not exist

To Reproduce

  1. (First terminal) docker run --rm -it -p5432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust postgres:14
  2. (Second terminal) ./rdmp --dir conf --command run CreateNewExternalDatabaseServer LiveLoggingServer_ID "DatabaseType:PostgreSql:Server=127.0.0.1;Uid=postgres;Database=postgres"

Expected behavior

The logging server is created.

RDMP Version

$ ./rdmp --version
2024-05-17 15:34:13.2228 INFO Dotnet Version:8.0.3 .
2024-05-17 15:34:13.2653 INFO RDMP Version:8.1.5.0 .
rdmp 8.1.5
2024-05-17 15:34:13.2653 INFO Exiting with code 0 .

Error with Stack Trace

$ ./rdmp --dir conf --command run CreateNewExternalDatabaseServer  LiveLoggingServer_ID "DatabaseType:PostgreSql:Server=127.0.0.1;Uid=postgres;Database=postgres"

... (startup output)

2024-05-17 15:34:56.0129 TRACE Running Command 'ExecuteCommandCreateNewExternalDatabaseServer' .
Success:About to run:
/*--Version:1.0.0*/
/*--Description:Initial Create*/
CREATE TABLE "postgres".public."DataSet"(
"dataSetID" varchar(150)    NOT NULL ,
"name" varchar(2000)    NULL ,
"description" text    NULL ,
"time_period" varchar(64)    NULL ,
"SLA_required" varchar(3)    NULL ,
"supplier_name" varchar(32)    NULL ,
"supplier_tel_no" varchar(32)    NULL ,
"supplier_email" varchar(64)    NULL ,
"contact_name" varchar(64)    NULL ,
"contact_position" varchar(64)    NULL ,
"currentContactInstitutions" varchar(64)    NULL ,
"contact_tel_no" varchar(32)    NULL ,
"contact_email" varchar(64)    NULL ,
"frequency" varchar(32)    NULL ,
"method" varchar(16)    NULL ,
 CONSTRAINT PK_DataSet PRIMARY KEY ("dataSetID"));
CREATE TABLE "postgres".public."DataLoadTask"(
"ID" int    NOT NULL ,
"description" text    NULL ,
"name" varchar(1000)    NULL ,
"createTime" timestamp default now()   NULL ,
"userAccount" varchar(500)    NULL ,
"statusID" int    NULL ,
"isTest" bit    NULL ,
"dataSetID" varchar(150)    NULL ,
 CONSTRAINT PK_DataLoadTask PRIMARY KEY ("ID"),

CONSTRAINT FK_DataLoadTask_DataSet FOREIGN KEY ("dataSetID")
REFERENCES "postgres".public."DataSet"("dataSetID")  on delete cascade);
CREATE TABLE "postgres".public."DataLoadRun"(
"ID" int    NOT NULL GENERATED ALWAYS AS IDENTITY,
"description" text    NULL ,
"startTime" timestamp default now()   NULL ,
"endTime" timestamp    NULL ,
"dataLoadTaskID" int    NULL ,
"isTest" bit    NULL ,
"packageName" varchar(750)    NULL ,
"userAccount" varchar(500)    NULL ,
"suggestedRollbackCommand" text    NULL ,
 CONSTRAINT PK_DataLoadRun PRIMARY KEY ("ID"),

CONSTRAINT FK_DataLoadRun_DataLoadTask FOREIGN KEY ("dataLoadTaskID")
REFERENCES "postgres".public."DataLoadTask"("ID")  on delete cascade);
CREATE TABLE "postgres".public."TableLoadRun"(
"startTime" timestamp default now()   NULL ,
"endTime" timestamp    NULL ,
"dataLoadRunID" int    NULL ,
"targetTable" varchar(200)    NULL ,
"expectedInserts" bigint    NULL ,
"inserts" bigint    NULL ,
"updates" bigint    NULL ,
"deletes" bigint    NULL ,
"errorRows" bigint    NULL ,
"ID" int    NOT NULL GENERATED ALWAYS AS IDENTITY,
"duplicates" bigint    NULL ,
"notes" varchar(8000)    NULL ,
"suggestedRollbackCommand" text    NULL ,
 CONSTRAINT PK_TableLoadRun PRIMARY KEY ("ID"),

CONSTRAINT FK_TableLoadRun_DataLoadRun FOREIGN KEY ("dataLoadRunID")
REFERENCES "postgres".public."DataLoadRun"("ID")  on delete cascade);
CREATE TABLE "postgres".public."DataSource"(
"ID" int    NOT NULL GENERATED ALWAYS AS IDENTITY,
"source" text    NULL ,
"tableLoadRunID" int    NULL ,
"archive" text    NULL ,
"originDate" timestamp default now()   NULL ,
"MD5" varchar(128)    NULL ,
 CONSTRAINT PK_DataSource PRIMARY KEY ("ID"),

CONSTRAINT FK_DataSource_TableLoadRun FOREIGN KEY ("tableLoadRunID")
REFERENCES "postgres".public."TableLoadRun"("ID")  on delete cascade);
CREATE TABLE "postgres".public."FatalError"(
"ID" int    NOT NULL GENERATED ALWAYS AS IDENTITY,
"time" timestamp default now()   NULL ,
"source" text    NULL ,
"description" text    NULL ,
"explanation" text    NULL ,
"dataLoadRunID" int    NULL ,
"statusID" int    NULL ,
"interestingToOthers" bit    NULL ,
 CONSTRAINT PK_FatalError PRIMARY KEY ("ID"),

CONSTRAINT FK_FatalError_DataLoadRun FOREIGN KEY ("dataLoadRunID")
REFERENCES "postgres".public."DataLoadRun"("ID")  on delete cascade);
CREATE TABLE "postgres".public."ProgressLog"(
"dataLoadRunID" int    NULL ,
"eventType" varchar(50)    NULL ,
"description" text    NULL ,
"source" text    NULL ,
"time" timestamp default now()   NULL ,
"ID" int    NOT NULL GENERATED ALWAYS AS IDENTITY,
 CONSTRAINT PK_ProgressLog PRIMARY KEY ("ID"),

CONSTRAINT FK_ProgressLog_DataLoadRun FOREIGN KEY ("dataLoadRunID")
REFERENCES "postgres".public."DataLoadRun"("ID")  on delete cascade);
CREATE TABLE "postgres".public."RowError"(
"ID" int    NOT NULL GENERATED ALWAYS AS IDENTITY,
"tableLoadRunID" int    NULL ,
"rowErrorTypeID" int    NULL ,
"description" text    NULL ,
"locationOfRow" text    NULL ,
"requiresReloading" bit    NULL ,
"columnName" text    NULL ,
 CONSTRAINT PK_RowError PRIMARY KEY ("ID"),

CONSTRAINT FK_RowError_TableLoadRun FOREIGN KEY ("tableLoadRunID")
REFERENCES "postgres".public."TableLoadRun"("ID")  on delete cascade);
CREATE TABLE "postgres".public."z_DataLoadTaskStatus"(
"ID" int    NOT NULL ,
"status" varchar(50)    NULL ,
"description" text    NULL ,
 CONSTRAINT PK_z_DataLoadTaskStatus PRIMARY KEY ("ID"));
CREATE TABLE "postgres".public."z_FatalErrorStatus"(
"ID" int    NOT NULL ,
"status" varchar(20)    NULL ,
 CONSTRAINT PK_z_FatalErrorStatus PRIMARY KEY ("ID"));
CREATE TABLE "postgres".public."z_RowErrorType"(
"ID" int    NOT NULL ,
"type" varchar(20)    NULL ,
 CONSTRAINT PK_z_RowErrorType PRIMARY KEY ("ID"));

INSERT INTO z_DataLoadTaskStatus(ID, status, description) VALUES(1, 'Open', NULL);
INSERT INTO z_DataLoadTaskStatus (ID, status, description) VALUES(2, 'Ready', NULL);
INSERT INTO z_DataLoadTaskStatus (ID, status, description) VALUES(3, 'Committed', NULL);
INSERT INTO z_FatalErrorStatus(ID, status) VALUES(1, 'Outstanding');
INSERT INTO z_FatalErrorStatus (ID, status) VALUES(2, 'Resolved');
INSERT INTO z_FatalErrorStatus (ID, status) VALUES(3, 'Blocked');
INSERT INTO z_RowErrorType(ID, type) VALUES(1, 'LoadRow');
INSERT INTO z_RowErrorType (ID, type) VALUES(2, 'Duplication');
INSERT INTO z_RowErrorType (ID, type) VALUES(3, 'Validation');
INSERT INTO z_RowErrorType (ID, type) VALUES(4, 'DatabaseOperation');
INSERT INTO z_RowErrorType (ID, type) VALUES(5, 'Unknown');

/*create datasets*/
INSERT INTO DataSet (dataSetID, name, description, time_period, SLA_required, supplier_name, supplier_tel_no, supplier_email, contact_name, contact_position, currentContactInstitutions, contact_tel_no, contact_email, frequency, method) VALUES(N'DataExtraction', 'DataExtraction', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO DataSet (dataSetID, name, description, time_period, SLA_required, supplier_name, supplier_tel_no, supplier_email, contact_name, contact_position, currentContactInstitutions, contact_tel_no, contact_email, frequency, method) VALUES(N'Internal', 'Internal', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

/*create tasks*/
INSERT INTO DataLoadTask(ID, description, name, userAccount, statusID, isTest, dataSetID) VALUES(1, 'Internal', 'Internal', 'Thomas', 1, 0, 'Internal');
INSERT INTO DataLoadTask (ID, description, name, userAccount, statusID, isTest, dataSetID) VALUES(2, 'DataExtraction', 'DataExtraction', 'Thomas', 1, 0, 'DataExtraction');


Warning:Database postgres already exists
Fail:Create failed
2024-05-17 15:34:56.2381 ERROR Failed check with message: Create failed .
2024-05-17 15:34:56.2381 INFO Fatal error occurred so returning -1 . System.Exception: Failed check with message: Create failed
 ---> Npgsql.PostgresException (0x80004005): 42P01: relation "z_dataloadtaskstatus" does not exist

POSITION: 13
   at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Rdmp.Core.ReusableLibraryCode.UsefulStuff.ExecuteBatchNonQuery(String sql, DbConnection conn, DbTransaction transaction, Dictionary`2& performanceFigures, Int32 timeout) in D:\a\RDMP\RDMP\Rdmp.Core\ReusableLibraryCode\UsefulStuff.cs:line 304
   at Rdmp.Core.MapsDirectlyToDatabaseTable.Versioning.MasterDatabaseScriptExecutor.RunSQL(KeyValuePair`2 kvp) in D:\a\RDMP\RDMP\Rdmp.Core\MapsDirectlyToDatabaseTable\Versioning\MasterDatabaseScriptExecutor.cs:line 142
   at Rdmp.Core.MapsDirectlyToDatabaseTable.Versioning.MasterDatabaseScriptExecutor.CreateDatabase(Patch initialCreationPatch, ICheckNotifier notifier) in D:\a\RDMP\RDMP\Rdmp.Core\MapsDirectlyToDatabaseTable\Versioning\MasterDatabaseScriptExecutor.cs:line 123
  Exception data:
    Severity: ERROR
    SqlState: 42P01
    MessageText: relation "z_dataloadtaskstatus" does not exist
    Position: 13
    File: parse_relation.c
    Line: 1381
    Routine: parserOpenTable
   --- End of inner exception stack trace ---
   at Rdmp.Core.ReusableLibraryCode.Checks.AcceptAllCheckNotifier.OnCheckPerformed(CheckEventArgs args) in D:\a\RDMP\RDMP\Rdmp.Core\ReusableLibraryCode\Checks\AcceptAllCheckNotifier.cs:line 35
   at Rdmp.Core.MapsDirectlyToDatabaseTable.Versioning.MasterDatabaseScriptExecutor.CreateDatabase(Patch initialCreationPatch, ICheckNotifier notifier) in D:\a\RDMP\RDMP\Rdmp.Core\MapsDirectlyToDatabaseTable\Versioning\MasterDatabaseScriptExecutor.cs:line 133
   at Rdmp.Core.MapsDirectlyToDatabaseTable.Versioning.MasterDatabaseScriptExecutor.CreateAndPatchDatabase(IPatcher patcher, ICheckNotifier notifier) in D:\a\RDMP\RDMP\Rdmp.Core\MapsDirectlyToDatabaseTable\Versioning\MasterDatabaseScriptExecutor.cs:line 422
   at Rdmp.Core.CommandExecution.BasicActivateItems.CreateNewPlatformDatabase(ICatalogueRepository catalogueRepository, PermissableDefaults defaultToSet, IPatcher patcher, DiscoveredDatabase db) in D:\a\RDMP\RDMP\Rdmp.Core\CommandExecution\BasicActivateItems.cs:line 730
   at Rdmp.Core.CommandExecution.AtomicCommands.ExecuteCommandCreateNewExternalDatabaseServer.Execute() in D:\a\RDMP\RDMP\Rdmp.Core\CommandExecution\AtomicCommands\ExecuteCommandCreateNewExternalDatabaseServer.cs:line 97
   at Rdmp.Core.CommandExecution.CommandInvoker.ExecuteCommand(ConstructorInfo constructorInfo, CommandLineObjectPicker picker) in D:\a\RDMP\RDMP\Rdmp.Core\CommandExecution\CommandInvoker.cs:line 298
   at Rdmp.Core.CommandLine.Runners.ExecuteCommandRunner.RunCommand(String command) in D:\a\RDMP\RDMP\Rdmp.Core\CommandLine\Runners\ExecuteCommandRunner.cs:line 104
   at Rdmp.Core.CommandLine.Runners.ExecuteCommandRunner.Run(IRDMPPlatformRepositoryServiceLocator repositoryLocator, IDataLoadEventListener listener, ICheckNotifier checkNotifier, GracefulCancellationToken token) in D:\a\RDMP\RDMP\Rdmp.Core\CommandLine\Runners\ExecuteCommandRunner.cs:line 94
   at Rdmp.Core.CommandLine.RdmpCommandLineBootStrapper.Run(RDMPCommandLineOptions opts, IRunner explicitRunner, IRDMPPlatformRepositoryServiceLocator existingLocator) in D:\a\RDMP\RDMP\Rdmp.Core\CommandLine\RdmpCommandLineBootStrapper.cs:line 145
   at Rdmp.Core.Program.HandleArguments(String[] args, Logger logger) in D:\a\RDMP\RDMP\Tools\rdmp\Program.cs:line 83

Database Engine

Postgres

rkm commented

Looking at the output, I suspect this is a quoting issue (z_dataloadtaskstatus vs z_DataLoadTaskStatus). Postgres treats table names as case-insensitive.

rkm commented

The bug is likely here:

RDMP/Rdmp.Core/Databases/LoggingDatabasePatcher.cs

Lines 87 to 89 in f4112ab

INSERT INTO z_DataLoadTaskStatus(ID, status, description) VALUES(1, 'Open', NULL);
INSERT INTO z_DataLoadTaskStatus (ID, status, description) VALUES(2, 'Ready', NULL);
INSERT INTO z_DataLoadTaskStatus (ID, status, description) VALUES(3, 'Committed', NULL);

I'll have a go at fixing this.

  • 👍1
jas88 commented

@rkm Slightly worse than that I'm afraid - PostgreSQL isn't case insensitive, it is case sensitive but folds unquoted identifiers into lower case for consistency.

So, CREATE TABLE Foo (INT id); INSERT INTO foo VALUES (1) is fine because both table names are converted to foo - but quote it as CREATE TABLE "Foo" (INT id); INSERT INTO "foo" VALUES (1) will fail, because the table created is called Foo rather than foo.

  • 👍1