MantisBT - opensim
View Issue Details
0008200opensim[REGION] OpenSim Corepublic2017-06-27 20:252019-02-06 11:28
testdev627 
Fly-Man- 
normalmajoralways
closedfixed 
master (dev code) 
master (dev code) 
Grid (Multiple Regions per Sim)
BulletSim
Unknown
None
0008200: [PATCH] PGSQL EstateStore.migrations does not create estate_settings_id SEQUENCE
On fresh install the PGSQL EstateStore.migrations do not complete because an exception is thrown on

CREATE TABLE public.estate_settings
(
  "EstateID" integer NOT NULL DEFAULT nextval('estate_settings_id'::regclass)....

The OpenSim.exe will fail to complete a first run. It works if you add this to the top of the migrations file

-- ----------------------------
-- SEQUENCE estate_settings_id
-- ----------------------------
CREATE SEQUENCE IF NOT EXISTS "public"."estate_settings_id"
  INCREMENT 100
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;
Fresh install using Postgresql with GridHypergrid Architecture

http://opensimulator.org/mantis/view.php?id=8163 [^] was a partial fix to the EstateStore.migrations problem but did not include this sequence.
No tags attached.
related to 0008163closed UbitUmarov PGSQL EstateStore.migrations leaves database without estate tables on first run 
patch 0001-PGSQL-EstateStore-create-estate_settings_id-SEQUENCE.patch (1,151) 2017-11-11 16:47
http://opensimulator.org/mantis/file_download.php?file_id=4704&type=bug
Issue History
2017-06-27 20:25testdev627New Issue
2017-07-05 10:49testdev627Relationship addedrelated to 0008163
2017-07-05 10:52testdev627Steps to Reproduce Updatedbug_revision_view_page.php?rev_id=6351#r6351
2017-07-17 00:02Gavin HirdNote Added: 0032155
2017-11-11 15:32TomTheDragonNote Added: 0032410
2017-11-11 16:47TomTheDragonFile Added: 0001-PGSQL-EstateStore-create-estate_settings_id-SEQUENCE.patch
2017-11-11 19:26TomTheDragonNote Added: 0032411
2017-11-11 19:26TomTheDragonStatusnew => patch included
2017-11-11 19:47UbitUmarovNote Added: 0032413
2017-11-12 00:58Gavin HirdNote Added: 0032414
2017-11-12 01:19UbitUmarovNote Added: 0032415
2018-09-26 07:36Fly-Man-SummaryPGSQL EstateStore.migrations does not create estate_settings_id SEQUENCE and will prevent OpenSim from completing first run => [PATCH] PGSQL EstateStore.migrations does not create estate_settings_id SEQUENCE
2018-09-26 07:37Fly-Man-Note Added: 0033070
2018-09-26 07:37Fly-Man-Statuspatch included => resolved
2018-09-26 07:37Fly-Man-Fixed in Version => master (dev code)
2018-09-26 07:37Fly-Man-Resolutionopen => fixed
2018-09-26 07:37Fly-Man-Assigned To => Fly-Man-
2019-02-06 11:28BillBlightNote Added: 0034368
2019-02-06 11:28BillBlightStatusresolved => closed

Notes
(0032155)
Gavin Hird   
2017-07-17 00:02   
What version of Postgres do you test on?

This is the full SQL that is needed to create the estate_settings table:

-- ----------------------------
-- Table structure for estate_settings
-- ----------------------------
DROP TABLE IF EXISTS "estate_settings";
CREATE TABLE "estate_settings" (
  "EstateID" int4 NOT NULL DEFAULT nextval('estate_settings_id'::regclass),
  "EstateName" varchar(64) COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,
  "AbuseEmailToEstateOwner" bool NOT NULL DEFAULT NULL,
  "DenyAnonymous" bool NOT NULL DEFAULT NULL,
  "ResetHomeOnTeleport" bool NOT NULL DEFAULT NULL,
  "FixedSun" bool NOT NULL DEFAULT NULL,
  "DenyTransacted" bool NOT NULL DEFAULT NULL,
  "BlockDwell" bool NOT NULL DEFAULT NULL,
  "DenyIdentified" bool NOT NULL DEFAULT NULL,
  "AllowVoice" bool NOT NULL DEFAULT NULL,
  "UseGlobalTime" bool NOT NULL DEFAULT NULL,
  "PricePerMeter" int4 NOT NULL DEFAULT NULL,
  "TaxFree" bool NOT NULL DEFAULT NULL,
  "AllowDirectTeleport" bool NOT NULL DEFAULT NULL,
  "RedirectGridX" int4 NOT NULL DEFAULT NULL,
  "RedirectGridY" int4 NOT NULL DEFAULT NULL,
  "ParentEstateID" int4 NOT NULL DEFAULT NULL,
  "SunPosition" float8 NOT NULL DEFAULT NULL,
  "EstateSkipScripts" bool NOT NULL DEFAULT NULL,
  "BillableFactor" float8 NOT NULL DEFAULT NULL,
  "PublicAccess" bool NOT NULL DEFAULT NULL,
  "AbuseEmail" varchar(255) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL,
  "EstateOwner" uuid NOT NULL DEFAULT NULL,
  "DenyMinors" bool NOT NULL DEFAULT NULL,
  "AllowLandmark" bool NOT NULL DEFAULT true,
  "AllowParcelChanges" bool NOT NULL DEFAULT true,
  "AllowSetHome" bool NOT NULL DEFAULT true
)
;
ALTER TABLE "estate_settings" OWNER TO "tableowner";

-- ----------------------------
-- Primary Key structure for table estate_settings
-- ----------------------------
ALTER TABLE "estate_settings" ADD CONSTRAINT "estate_settings_pkey" PRIMARY KEY ("EstateID");
(0032410)
TomTheDragon   
2017-11-11 15:32   
I can confirm this issue.
Running PostgreSQL 10.1, opensim master.

I believe the issue is that the sequence is not created in the migration and it's expected it already exists.. should probably be added to the migration so it is smooth either way.
(0032411)
TomTheDragon   
2017-11-11 19:26   
I have included patch listed in description which I have tested. YMMV.
(0032413)
UbitUmarov   
2017-11-11 19:47   
ok thx, waiting a bit for more feedback to improve "mileage" :)
(0032414)
Gavin Hird   
2017-11-12 00:58   
It looks like the statement

create Sequence estate_settings_id increment by 100 start with 100;

got dropped in commit 03a38a8 presumably because Navicat Postgres does not include sequences required for the table in the table definition SQL export. (I'll report that as a bug to Navicat).

The patch can be added, but please increment the version number of Estatestore.migrations to version 13. meaning there must be a new transaction block:

:VERSION 13

BEGIN TRASACTION;

-- ----------------------------
-- SEQUENCE estate_settings_id
-- ----------------------------
CREATE SEQUENCE IF NOT EXISTS "public"."estate_settings_id"
  INCREMENT 100
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;

COMMIT;
(0032415)
UbitUmarov   
2017-11-12 01:19   
ok on master as version 13
(0033070)
Fly-Man-   
2018-09-26 07:36   
Patch has been applied to master in 2017
(0034368)
BillBlight   
2019-02-06 11:28   
Marked as Resolved but never closed, can be reopened if needed.