-migrate to Postgress 11: use BIGINT GENERATED BY DEFAULT AS IDENTITY instead of BIGSERIAL

1 files changed, 29 insertions, 29 deletions

diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index 6e69751ec..3f9979c06 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -22,7 +22,7 @@ SELECT _v.register_patch('exchange-0001', NULL, NULL);
 
 
 CREATE TABLE IF NOT EXISTS denominations
-  (denominations_serial BIGSERIAL UNIQUE
+  (denominations_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64)
   ,denom_type INT4 NOT NULL DEFAULT (1) -- 1 == RSA (for now, remove default later!)
   ,age_mask INT4 NOT NULL DEFAULT (0)
@@ -58,7 +58,7 @@ CREATE INDEX IF NOT EXISTS denominations_by_expire_legal_index
 
 
 CREATE TABLE IF NOT EXISTS denomination_revocations
-  (denom_revocations_serial_id BIGSERIAL UNIQUE
+  (denom_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,denominations_serial INT8 PRIMARY KEY REFERENCES denominations (denominations_serial) ON DELETE CASCADE
   ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
   );
@@ -67,7 +67,7 @@ COMMENT ON TABLE denomination_revocations
 
 
 CREATE TABLE IF NOT EXISTS wire_targets
-  (wire_target_serial_id BIGSERIAL -- UNIQUE
+  (wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=64)
   ,payto_uri VARCHAR NOT NULL
   ,kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)
@@ -89,7 +89,7 @@ CREATE TABLE IF NOT EXISTS wire_targets_default
   FOR VALUES WITH (MODULUS 1, REMAINDER 0);
 
 CREATE TABLE IF NOT EXISTS reserves
-  (reserve_uuid BIGSERIAL
+  (reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY
   ,reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)
   ,current_balance_val INT8 NOT NULL
   ,current_balance_frac INT4 NOT NULL
@@ -130,7 +130,7 @@ COMMENT ON INDEX reserves_by_gc_date_index
 
 
 CREATE TABLE IF NOT EXISTS reserves_in
-  (reserve_in_serial_id BIGSERIAL -- UNIQUE
+  (reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,reserve_pub BYTEA PRIMARY KEY REFERENCES reserves (reserve_pub) ON DELETE CASCADE
   ,wire_reference INT8 NOT NULL
   ,credit_val INT8 NOT NULL
@@ -168,7 +168,7 @@ CREATE INDEX IF NOT EXISTS reserves_in_by_exchange_account_reserve_in_serial_id_
 
 
 CREATE TABLE IF NOT EXISTS reserves_close
-  (close_uuid BIGSERIAL -- UNIQUE / PRIMARY KEY
+  (close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE / PRIMARY KEY
   ,reserve_pub BYTEA NOT NULL REFERENCES reserves (reserve_pub) ON DELETE CASCADE
   ,execution_date INT8 NOT NULL
   ,wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)
@@ -195,7 +195,7 @@ CREATE INDEX IF NOT EXISTS reserves_close_by_reserve_pub_index
 
 
 CREATE TABLE IF NOT EXISTS reserves_out
-  (reserve_out_serial_id BIGSERIAL -- UNIQUE
+  (reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) -- UNIQUE
   ,denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial)
   ,denom_sig BYTEA NOT NULL
@@ -227,7 +227,7 @@ COMMENT ON INDEX reserves_out_by_reserve_uuid_and_execution_date_index
 
 
 CREATE TABLE IF NOT EXISTS auditors
-  (auditor_uuid BIGSERIAL UNIQUE
+  (auditor_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,auditor_pub BYTEA PRIMARY KEY CHECK (LENGTH(auditor_pub)=32)
   ,auditor_name VARCHAR NOT NULL
   ,auditor_url VARCHAR NOT NULL
@@ -247,7 +247,7 @@ COMMENT ON COLUMN auditors.last_change
 
 
 CREATE TABLE IF NOT EXISTS auditor_denom_sigs
-  (auditor_denom_serial BIGSERIAL UNIQUE
+  (auditor_denom_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,auditor_uuid INT8 NOT NULL REFERENCES auditors (auditor_uuid) ON DELETE CASCADE
   ,denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial) ON DELETE CASCADE
   ,auditor_sig BYTEA CHECK (LENGTH(auditor_sig)=64)
@@ -264,7 +264,7 @@ COMMENT ON COLUMN auditor_denom_sigs.auditor_sig
 
 
 CREATE TABLE IF NOT EXISTS exchange_sign_keys
-  (esk_serial BIGSERIAL UNIQUE
+  (esk_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,exchange_pub BYTEA PRIMARY KEY CHECK (LENGTH(exchange_pub)=32)
   ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
   ,valid_from INT8 NOT NULL
@@ -286,7 +286,7 @@ COMMENT ON COLUMN exchange_sign_keys.expire_legal
 
 
 CREATE TABLE IF NOT EXISTS signkey_revocations
-  (signkey_revocations_serial_id BIGSERIAL UNIQUE
+  (signkey_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,esk_serial INT8 PRIMARY KEY REFERENCES exchange_sign_keys (esk_serial) ON DELETE CASCADE
   ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
   );
@@ -295,7 +295,7 @@ COMMENT ON TABLE signkey_revocations
 
 
 CREATE TABLE IF NOT EXISTS extensions
-  (extension_id BIGSERIAL UNIQUE
+  (extension_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,name VARCHAR NOT NULL UNIQUE
   ,config BYTEA
   );
@@ -308,7 +308,7 @@ COMMENT ON COLUMN extensions.config
 
 
 CREATE TABLE IF NOT EXISTS known_coins
-  (known_coin_id BIGSERIAL -- UNIQUE
+  (known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial) ON DELETE CASCADE
   ,coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)
   ,age_hash BYTEA CHECK (LENGTH(age_hash)=32)
@@ -339,7 +339,7 @@ CREATE INDEX IF NOT EXISTS known_coins_by_known_coin_id_index
 
 
 CREATE TABLE IF NOT EXISTS refresh_commitments
-  (melt_serial_id BIGSERIAL -- UNIQUE
+  (melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)
   ,old_coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE CASCADE
   ,h_age_commitment BYTEA CHECK(LENGTH(h_age_commitment)=32)
@@ -372,7 +372,7 @@ CREATE INDEX IF NOT EXISTS refresh_commitments_by_old_coin_pub_index
 
 
 CREATE TABLE IF NOT EXISTS refresh_revealed_coins
-  (rrc_serial BIGSERIAL -- UNIQUE
+  (rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,melt_serial_id INT8 NOT NULL -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE
   ,freshcoin_index INT4 NOT NULL
   ,link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)
@@ -416,7 +416,7 @@ CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_melt_serial_id_index
 
 
 CREATE TABLE IF NOT EXISTS refresh_transfer_keys
-  (rtc_serial BIGSERIAL -- UNIQUE
+  (rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,melt_serial_id INT8 PRIMARY KEY -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE
   ,transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)
   ,transfer_privs BYTEA NOT NULL
@@ -442,7 +442,7 @@ CREATE INDEX IF NOT EXISTS refresh_transfer_keys_by_rtc_serial_index
 
 
 CREATE TABLE IF NOT EXISTS extension_details
-  (extension_details_serial_id BIGSERIAL PRIMARY KEY
+  (extension_details_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
   ,extension_options VARCHAR);
 COMMENT ON TABLE extension_details
   IS 'Extensions that were provided with deposits (not yet used).';
@@ -451,7 +451,7 @@ COMMENT ON COLUMN extension_details.extension_options
 
 
 CREATE TABLE IF NOT EXISTS deposits
-  (deposit_serial_id BIGSERIAL -- PRIMARY KEY
+  (deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
   ,shard INT8 NOT NULL
   ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE
   ,amount_with_fee_val INT8 NOT NULL
@@ -523,7 +523,7 @@ COMMENT ON INDEX deposits_for_iterate_matching_index
 
 
 CREATE TABLE IF NOT EXISTS refunds
-  (refund_serial_id BIGSERIAL -- UNIQUE
+  (refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,deposit_serial_id INT8 NOT NULL -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE
   ,merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)
   ,rtransaction_id INT8 NOT NULL
@@ -550,7 +550,7 @@ CREATE INDEX IF NOT EXISTS refunds_by_refund_serial_id_index
 
 
 CREATE TABLE IF NOT EXISTS wire_out
-  (wireout_uuid BIGSERIAL -- PRIMARY KEY
+  (wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
   ,execution_date INT8 NOT NULL
   ,wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)
   ,wire_target_serial_id INT8 NOT NULL -- REFERENCES wire_targets (wire_target_serial_id)
@@ -579,7 +579,7 @@ CREATE INDEX IF NOT EXISTS wire_out_by_wire_target_serial_id_index
 
 
 CREATE TABLE IF NOT EXISTS aggregation_tracking
-  (aggregation_serial_id BIGSERIAL -- UNIQUE
+  (aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,deposit_serial_id INT8 PRIMARY KEY -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE
   ,wtid_raw BYTEA CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE
   )
@@ -603,7 +603,7 @@ COMMENT ON INDEX aggregation_tracking_by_wtid_raw_index
 
 
 CREATE TABLE IF NOT EXISTS wire_fee
-  (wire_fee_serial BIGSERIAL UNIQUE
+  (wire_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,wire_method VARCHAR NOT NULL
   ,start_date INT8 NOT NULL
   ,end_date INT8 NOT NULL
@@ -625,7 +625,7 @@ CREATE INDEX IF NOT EXISTS wire_fee_by_end_date_index
 
 
 CREATE TABLE IF NOT EXISTS recoup
-  (recoup_uuid BIGSERIAL -- UNIQUE
+  (recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id)
   ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
   ,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)
@@ -661,7 +661,7 @@ CREATE INDEX IF NOT EXISTS recoup_by_known_coin_id_index
 
 
 CREATE TABLE IF NOT EXISTS recoup_refresh
-  (recoup_refresh_uuid BIGSERIAL -- UNIQUE
+  (recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id)
   ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
   ,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)
@@ -695,7 +695,7 @@ CREATE INDEX IF NOT EXISTS recoup_refresh_by_known_coin_id_index
 
 
 CREATE TABLE IF NOT EXISTS prewire
-  (prewire_uuid BIGSERIAL PRIMARY KEY
+  (prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
   ,wire_method TEXT NOT NULL
   ,finished BOOLEAN NOT NULL DEFAULT false
   ,failed BOOLEAN NOT NULL DEFAULT false
@@ -743,12 +743,12 @@ COMMENT ON COLUMN wire_accounts.is_active
   IS 'true if we are currently supporting the use of this account.';
 COMMENT ON COLUMN wire_accounts.last_change
   IS 'Latest time when active status changed. Used to detect replays of old messages.';
--- "wire_accounts" has no BIGSERIAL because it is a 'mutable' table
+-- "wire_accounts" has no sequence because it is a 'mutable' table
 --            and is of no concern to the auditor
 
 
 CREATE TABLE IF NOT EXISTS cs_nonce_locks
-  (cs_nonce_lock_serial_id BIGSERIAL -- UNIQUE
+  (cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)
   ,op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)
   ,max_denomination_serial INT8 NOT NULL
@@ -765,7 +765,7 @@ COMMENT ON COLUMN cs_nonce_locks.max_denomination_serial
 
 
 CREATE TABLE IF NOT EXISTS work_shards
-  (shard_serial_id BIGSERIAL UNIQUE
+  (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,last_attempt INT8 NOT NULL
   ,start_row INT8 NOT NULL
   ,end_row INT8 NOT NULL
@@ -797,7 +797,7 @@ CREATE INDEX IF NOT EXISTS work_shards_by_job_name_completed_last_attempt_index
 
 
 CREATE UNLOGGED TABLE IF NOT EXISTS revolving_work_shards
-  (shard_serial_id BIGSERIAL UNIQUE
+  (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,last_attempt INT8 NOT NULL
   ,start_row INT4 NOT NULL
   ,end_row INT4 NOT NULL