RPI 6.6.4: Pre- and Post-patch steps for VisitorID > 36 characters fix (SQLServer / PostgreSQL)

Overview

When upgrading to 6.6.23212.1444 and later, the following steps should be included pre and post-upgrade:

Pre-patch

From the System -> Operations tab, disable the Web cache data importer system task on any RPI tenant where Realtime is enabled and where RPI web cache data is being written to RPI_WebVisitor* tables.

Post-patch

Drop the primary keys from the following tables in the RPI Warehouse:
1. RPI_WebVisitors (PK is on VisitorID)
2. RPI_WebDevices (PK is on VisitorID + DeviceID)
3. RPI_WebVisitorScores (PK is on VisitorID + ScorecardIdentifier)
Extend the column size of VisitorID on each of the tables from nvarchar(36) to nvarchar(200) and make sure the column is NOT NULL
Rebuild the indexes above
From RPI, run a catalog sync so RPI picks up the changes to these tables
Re-enable the Web cache data importer jobs

Sample SQL Script for reference

SQL

USE [CDP] -- your dwh
GO


-- RPI_WebVisitors
ALTER TABLE [dbo].[RPI_WebVisitors] DROP CONSTRAINT [PK_RPI_WebVisitors]
GO

ALTER TABLE [dbo].[RPI_WebVisitors]
ALTER COLUMN VisitorID nvarchar(200) NOT NULL;
GO

ALTER TABLE [dbo].[RPI_WebVisitors] ADD CONSTRAINT [PK_RPI_WebVisitors] PRIMARY KEY CLUSTERED 
(
[VisitorID] ASC
)
GO

-- RPI_WebDevices
ALTER TABLE [dbo].[RPI_WebDevices] DROP CONSTRAINT [PK_RPI_WebDevices]
GO

ALTER TABLE [dbo].[RPI_WebDevices]
ALTER COLUMN VisitorID nvarchar(200) NOT NULL;
GO

ALTER TABLE [dbo].[RPI_WebDevices] ADD CONSTRAINT [PK_RPI_WebDevices] PRIMARY KEY CLUSTERED 
(
[VisitorID] ASC,
[DeviceID] ASC
)
GO


-- RPI_WebVisitorsScores
ALTER TABLE [dbo].[RPI_WebVisitorsScores] DROP CONSTRAINT [PK_RPI_WebVisitorsScores] 
GO

ALTER TABLE [dbo].[RPI_WebVisitorsScores]
ALTER COLUMN VisitorID nvarchar(200) NOT NULL;
GO

ALTER TABLE [dbo].[RPI_WebVisitorsScores] ADD CONSTRAINT [PK_RPI_WebVisitorsScores] PRIMARY KEY CLUSTERED 
(
[VisitorID] ASC,
[ScorecardIdentifier] ASC
)
GO

Sample PostgreSQL Script for reference

SQL

-- RPI_WebVisitors
ALTER TABLE "rpi"."RPI_WebVisitors" DROP CONSTRAINT "PK_RPI_WebVisitors";
ALTER TABLE "rpi"."RPI_WebVisitors" ALTER COLUMN "VisitorID" TYPE varchar(200);
ALTER TABLE "rpi"."RPI_WebVisitors" ADD CONSTRAINT "PK_RPI_WebVisitors" PRIMARY KEY ("VisitorID")

-- RPI_WebDevices
ALTER TABLE "rpi"."RPI_WebDevices" DROP CONSTRAINT "PK_RPI_WebDevices";
ALTER TABLE "rpi"."RPI_WebDevices" ALTER COLUMN "VisitorID" TYPE varchar(200);
ALTER TABLE "rpi"."RPI_WebDevices" ADD CONSTRAINT "PK_RPI_WebDevices" PRIMARY KEY ("VisitorID", "DeviceID");

-- RPI_WebVisitorsScores
ALTER TABLE "rpi"."RPI_WebVisitorsScores" DROP CONSTRAINT "PK_RPI_WebVisitorsScores";
ALTER TABLE "rpi"."RPI_WebVisitorsScores" ALTER COLUMN "VisitorID" TYPE varchar(200);
ALTER TABLE "rpi"."RPI_WebVisitorsScores" ADD CONSTRAINT "PK_RPI_WebVisitorsScores" PRIMARY KEY