Include extra details when storing unparsable and uninsertable lines.

Now includes the hostnameId, timestamp and optionally error message.
Updates idx_entities_retryinsert to account for details not being NULL.
Also fixes the issue where the entities inserted and rollback would
still be recorded in the entity cache if the access log insert failed.
This would have lead to PK violations later on.

9 files changed, 43 insertions, 10 deletions

diff --git a/src/ingestor.cpp b/src/ingestor.cpp
index ff5cd92..077f7d8 100644
--- a/src/ingestor.cpp
+++ b/src/ingestor.cpp
@@ -420,17 +420,16 @@ namespace WebStat {
 				try {
 					DB::TransactionScope lineTx {*dbconn};
 					storeNewEntities(dbconn, valuesEntities);
-					existingEntities()->insert_range(valuesEntities | ENTITY_IDS);
 					storeLogLine(dbconn, values);
+					existingEntities()->insert_range(valuesEntities | ENTITY_IDS);
 				}
 				catch (const DB::Error & originalError) {
 					try {
 						DB::TransactionScope lineTx {*dbconn};
-						auto uninsertableLine = ToEntity<EntityType::UninsertableLine> {}(line);
-						storeNewEntity(dbconn, uninsertableLine);
+						const auto uninsertableLineId = storeUninsertableLine(dbconn, line, originalError);
 						log(LOG_NOTICE,
 								"Failed to store parsed line and/or associated entties, but did store raw line, %u:%s",
-								*uninsertableLine.id, line.c_str());
+								uninsertableLineId, line.c_str());
 					}
 					catch (const std::exception & excp) {
 						log(LOG_NOTICE, "Failed to store line in any form, DB connection lost? %s", excp.what());
@@ -440,9 +439,8 @@ namespace WebStat {
 			}
 			else {
 				stats.linesParseFailed++;
-				auto unparsableLine = ToEntity<EntityType::UnparsableLine> {}(line);
-				storeNewEntity(dbconn, unparsableLine);
-				log(LOG_NOTICE, "Failed to parse line, this is a bug: %u:%s", *unparsableLine.id, line.c_str());
+				const auto unparsableLineId = storeUnparsableLine(dbconn, line);
+				log(LOG_NOTICE, "Failed to parse line, this is a bug: %u:%s", unparsableLineId, line.c_str());
 			}
 		}
 	}
@@ -652,6 +650,20 @@ namespace WebStat {
 		stats.entitiesInserted += 1;
 	}
 
+	EntityId
+	Ingestor::storeUnparsableLine(DB::Connection * dbconn, const std::string_view line) const
+	{
+		return insert<EntityId>(dbconn, SQL::UNPARSABLE_INSERT, SQL::UNPARSABLE_INSERT_OPTS, line, hostnameId);
+	}
+
+	EntityId
+	Ingestor::storeUninsertableLine(
+			DB::Connection * dbconn, const std::string_view line, const std::exception & excp) const
+	{
+		return insert<EntityId>(
+				dbconn, SQL::UNINSERTABLE_INSERT, SQL::UNINSERTABLE_INSERT_OPTS, line, hostnameId, excp.what());
+	}
+
 	void
 	Ingestor::onNewUserAgent(const Entity & entity) const
 	{
diff --git a/src/ingestor.hpp b/src/ingestor.hpp
index ce4ac09..dacfdf7 100644
--- a/src/ingestor.hpp
+++ b/src/ingestor.hpp
@@ -117,6 +117,8 @@ namespace WebStat {
 		void fillKnownEntities(std::span<Entity *>) const;
 		void storeNewEntities(DB::Connection *, std::span<Entity *>) const;
 		void storeNewEntity(DB::Connection *, Entity &) const;
+		EntityId storeUnparsableLine(DB::Connection *, std::string_view) const;
+		EntityId storeUninsertableLine(DB::Connection *, std::string_view, const std::exception &) const;
 		void onNewUserAgent(const Entity &) const;
 		auto withCurlLock(auto &&...);
 		bool haveCurlOperations();
diff --git a/src/schema.sql b/src/schema.sql
index a4ea0e8..40cab51 100644
--- a/src/schema.sql
+++ b/src/schema.sql
@@ -92,7 +92,7 @@ CREATE UNIQUE INDEX uni_entities_value ON entities(MD5(value), type);
 
 CREATE INDEX idx_entities_retryinsert ON bad_lines(id)
 WHERE
-	type = 'uninsertable_line' AND detail IS NULL;
+	type = 'uninsertable_line' AND detail ->> 'retriedAt' IS NULL;
 
 CREATE OR REPLACE FUNCTION entity(newValue text, newType entity)
 	RETURNS TABLE(
diff --git a/src/sql.cpp b/src/sql.cpp
index 63e946c..2b91aab 100644
--- a/src/sql.cpp
+++ b/src/sql.cpp
@@ -16,6 +16,12 @@ namespace WebStat::SQL {
 	const std::string ENTITY_INSERT {
 #embed "sql/entityInsert.sql"
 	};
+	const std::string UNPARSABLE_INSERT {
+#embed "sql/unparsableInsert.sql"
+	};
+	const std::string UNINSERTABLE_INSERT {
+#embed "sql/uninsertableInsert.sql"
+	};
 	const std::string ENTITY_UPDATE_DETAIL {
 #embed "sql/entityUpdateDetail.sql"
 	};
@@ -40,6 +46,8 @@ namespace WebStat::SQL {
 	HASH_OPTS(ACCESS_LOG_INSERT);
 	HASH_OPTS(ACCESS_LOG_PURGE_OLD);
 	HASH_OPTS(ENTITY_INSERT);
+	HASH_OPTS(UNPARSABLE_INSERT);
+	HASH_OPTS(UNINSERTABLE_INSERT);
 	HASH_OPTS(ENTITY_UPDATE_DETAIL);
 	HASH_OPTS(HOST_UPSERT);
 	const DB::CommandOptionsPtr SELECT_UNINSERTABLE_OPTS
diff --git a/src/sql.hpp b/src/sql.hpp
index ae3559a..346754c 100644
--- a/src/sql.hpp
+++ b/src/sql.hpp
@@ -12,6 +12,8 @@ namespace WebStat::SQL {
 	EMBED_DECLARE(ACCESS_LOG_PURGE_OLD);
 	EMBED_DECLARE(ENTITY_INSERT);
 	EMBED_DECLARE(ENTITY_UPDATE_DETAIL);
+	EMBED_DECLARE(UNPARSABLE_INSERT);
+	EMBED_DECLARE(UNINSERTABLE_INSERT);
 	EMBED_DECLARE(HOST_UPSERT);
 	EMBED_DECLARE(SELECT_UNINSERTABLE);
 	EMBED_DECLARE(DELETE_ENTITY);
diff --git a/src/sql/markEntityRetried.sql b/src/sql/markEntityRetried.sql
index 6ec2263..a52a15c 100644
--- a/src/sql/markEntityRetried.sql
+++ b/src/sql/markEntityRetried.sql
@@ -1,6 +1,6 @@
 UPDATE
 	entities
 SET
-	detail = jsonb_build_object('retriedAt', CURRENT_TIMESTAMP at time zone 'utc', 'error', ?::text)
+	detail = detail || jsonb_build_object('retriedAt', CURRENT_TIMESTAMP at time zone 'utc', 'error', ?::text)
 WHERE
 	id = ?
diff --git a/src/sql/selectUninsertableLines.sql b/src/sql/selectUninsertableLines.sql
index 894ab67..060e800 100644
--- a/src/sql/selectUninsertableLines.sql
+++ b/src/sql/selectUninsertableLines.sql
@@ -5,7 +5,7 @@ FROM
 	entities
 WHERE
 	type = 'uninsertable_line'
-	AND detail IS NULL
+	AND detail ->> 'retriedAt' IS NULL
 ORDER BY
 	id
 LIMIT ?
diff --git a/src/sql/uninsertableInsert.sql b/src/sql/uninsertableInsert.sql
new file mode 100644
index 0000000..5b09393
--- /dev/null
+++ b/src/sql/uninsertableInsert.sql
@@ -0,0 +1,5 @@
+INSERT INTO entities(type, value, detail)
+	VALUES ('uninsertable_line', $1, jsonb_build_object('hostnameId', $2::int, 'error', $3::text,
+	'timestamp', CURRENT_TIMESTAMP))
+RETURNING
+	id
diff --git a/src/sql/unparsableInsert.sql b/src/sql/unparsableInsert.sql
new file mode 100644
index 0000000..6441f93
--- /dev/null
+++ b/src/sql/unparsableInsert.sql
@@ -0,0 +1,4 @@
+INSERT INTO entities(type, value, detail)
+	VALUES ('unparsable_line', $1, jsonb_build_object('hostnameId', $2::int, 'timestamp', CURRENT_TIMESTAMP))
+RETURNING
+	id