Skip to content

Commit d1b9d94

Browse files
devbwwdevbww
authored
feat(spanner): samples for bit-reversed sequence (#12280)
Bit-Reversed Sequence provides a way for Cloud Spanner to automatically generate hotspot-proof, unique 64-bit values for primary key columns. Manually bit-reversing monotonic key values is now recommended as a best practice.
1 parent 90ad988 commit d1b9d94

2 files changed

Lines changed: 313 additions & 9 deletions

File tree

‎google/cloud/spanner/samples/postgresql_samples.cc‎

Lines changed: 144 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -260,11 +260,11 @@ void InsertUsingDmlReturning(google::cloud::spanner::Client client) {
260260
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
261261
auto sql = google::cloud::spanner::SqlStatement(R"""(
262262
INSERT INTO Singers (SingerId, FirstName, LastName)
263-
VALUES (12, 'Melissa', 'Garcia'),
264-
(13, 'Russell', 'Morales'),
265-
(14, 'Jacqueline', 'Long'),
266-
(15, 'Dylan', 'Shaw')
267-
RETURNING FullName
263+
VALUES (12, 'Melissa', 'Garcia'),
264+
(13, 'Russell', 'Morales'),
265+
(14, 'Jacqueline', 'Long'),
266+
(15, 'Dylan', 'Shaw')
267+
RETURNING FullName
268268
)""");
269269
using RowType = std::tuple<std::string>;
270270
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
@@ -850,6 +850,133 @@ void JsonbQueryWithParameter(google::cloud::spanner::Client client) {
850850
}
851851
// [END spanner_postgresql_jsonb_query_parameter]
852852

853+
// [START spanner_postgresql_create_sequence]
854+
void CreateSequence(
855+
google::cloud::spanner_admin::DatabaseAdminClient admin_client,
856+
google::cloud::spanner::Database const& database,
857+
google::cloud::spanner::Client client) {
858+
std::vector<std::string> statements;
859+
statements.emplace_back(R"""(
860+
CREATE SEQUENCE Seq BIT_REVERSED_POSITIVE
861+
)""");
862+
statements.emplace_back(R"""(
863+
CREATE TABLE Customers (
864+
CustomerId BIGINT DEFAULT NEXTVAL('Seq'),
865+
CustomerName CHARACTER VARYING(1024),
866+
PRIMARY KEY (CustomerId)
867+
)
868+
)""");
869+
auto metadata =
870+
admin_client.UpdateDatabaseDdl(database.FullName(), std::move(statements))
871+
.get();
872+
google::cloud::spanner_testing::LogUpdateDatabaseDdl( //! TODO(#4758)
873+
admin_client, database, metadata.status()); //! TODO(#4758)
874+
if (!metadata) throw std::move(metadata).status();
875+
std::cout << "Created `Seq` sequence and `Customers` table,"
876+
<< " where the key column `CustomerId`"
877+
<< " uses the sequence as a default value,"
878+
<< " new DDL:\n"
879+
<< metadata->DebugString();
880+
auto commit = client.Commit(
881+
[&client](google::cloud::spanner::Transaction txn)
882+
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
883+
auto sql = google::cloud::spanner::SqlStatement(R"""(
884+
INSERT INTO Customers (CustomerName)
885+
VALUES ('Alice'),
886+
('David'),
887+
('Marc')
888+
RETURNING CustomerId
889+
)""");
890+
using RowType = std::tuple<std::int64_t>;
891+
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
892+
// Note: This mutator might be re-run, or its effects discarded, so
893+
// changing non-transactional state (e.g., by producing output) is,
894+
// in general, not something to be imitated.
895+
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
896+
if (!row) return std::move(row).status();
897+
std::cout << "Inserted customer record with CustomerId: "
898+
<< std::get<0>(*row) << "\n";
899+
}
900+
std::cout << "Number of customer records inserted is: "
901+
<< rows.RowsModified() << "\n";
902+
return google::cloud::spanner::Mutations{};
903+
});
904+
if (!commit) throw std::move(commit).status();
905+
}
906+
// [END spanner_postgresql_create_sequence]
907+
908+
// [START spanner_postgresql_alter_sequence]
909+
void AlterSequence(
910+
google::cloud::spanner_admin::DatabaseAdminClient admin_client,
911+
google::cloud::spanner::Database const& database,
912+
google::cloud::spanner::Client client) {
913+
std::vector<std::string> statements;
914+
statements.emplace_back(R"""(
915+
ALTER SEQUENCE Seq SKIP RANGE 1000 5000000
916+
)""");
917+
auto metadata =
918+
admin_client.UpdateDatabaseDdl(database.FullName(), std::move(statements))
919+
.get();
920+
google::cloud::spanner_testing::LogUpdateDatabaseDdl( //! TODO(#4758)
921+
admin_client, database, metadata.status()); //! TODO(#4758)
922+
if (!metadata) throw std::move(metadata).status();
923+
std::cout << "Altered `Seq` sequence"
924+
<< " to skip an inclusive range between 1000 and 5000000,"
925+
<< " new DDL:\n"
926+
<< metadata->DebugString();
927+
auto commit = client.Commit(
928+
[&client](google::cloud::spanner::Transaction txn)
929+
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
930+
auto sql = google::cloud::spanner::SqlStatement(R"""(
931+
INSERT INTO Customers (CustomerName)
932+
VALUES ('Lea'),
933+
('Catalina'),
934+
('Smith')
935+
RETURNING CustomerId
936+
)""");
937+
using RowType = std::tuple<std::int64_t>;
938+
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
939+
// Note: This mutator might be re-run, or its effects discarded, so
940+
// changing non-transactional state (e.g., by producing output) is,
941+
// in general, not something to be imitated.
942+
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
943+
if (!row) return std::move(row).status();
944+
std::cout << "Inserted customer record with CustomerId: "
945+
<< std::get<0>(*row) << "\n";
946+
}
947+
std::cout << "Number of customer records inserted is: "
948+
<< rows.RowsModified() << "\n";
949+
return google::cloud::spanner::Mutations{};
950+
});
951+
if (!commit) throw std::move(commit).status();
952+
}
953+
// [END spanner_postgresql_alter_sequence]
954+
955+
// [START spanner_postgresql_drop_sequence]
956+
void DropSequence(
957+
google::cloud::spanner_admin::DatabaseAdminClient admin_client,
958+
google::cloud::spanner::Database const& database) {
959+
std::vector<std::string> statements;
960+
statements.emplace_back(R"""(
961+
ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT
962+
)""");
963+
statements.emplace_back(R"""(
964+
DROP SEQUENCE Seq
965+
)""");
966+
auto metadata =
967+
admin_client.UpdateDatabaseDdl(database.FullName(), std::move(statements))
968+
.get();
969+
google::cloud::spanner_testing::LogUpdateDatabaseDdl( //! TODO(#4758)
970+
admin_client, database, metadata.status()); //! TODO(#4758)
971+
if (!metadata) throw std::move(metadata).status();
972+
std::cout << "Altered `Customers` table to"
973+
<< " drop DEFAULT from `CustomerId` column,"
974+
<< " and dropped the `Seq` sequence,"
975+
<< " new DDL:\n"
976+
<< metadata->DebugString();
977+
}
978+
// [END spanner_postgresql_drop_sequence]
979+
853980
void DropDatabase(google::cloud::spanner_admin::DatabaseAdminClient client,
854981
google::cloud::spanner::Database const& database) {
855982
auto status = client.DropDatabase(database.FullName());
@@ -1010,6 +1137,9 @@ int RunOneCommand(std::vector<std::string> argv,
10101137
{"jsonb-add-column", Command(samples::JsonbAddColumn)},
10111138
{"jsonb-update-data", Command(samples::JsonbUpdateData)},
10121139
{"jsonb-query-with-parameter", Command(samples::JsonbQueryWithParameter)},
1140+
{"create-sequence", Command(samples::CreateSequence)},
1141+
{"alter-sequence", Command(samples::AlterSequence)},
1142+
{"drop-sequence", Command(samples::DropSequence)},
10131143
{"drop-database", Command(samples::DropDatabase)},
10141144
{"help", HelpCommand(commands)},
10151145
};
@@ -1135,6 +1265,15 @@ int RunAll() {
11351265

11361266
SampleBanner("spanner_postgresql_jsonb_query_parameter");
11371267
samples::JsonbQueryWithParameter(client);
1268+
1269+
SampleBanner("spanner_postgresql_create_sequence");
1270+
samples::CreateSequence(database_admin_client, database, client);
1271+
1272+
SampleBanner("spanner_postgresql_alter_sequence");
1273+
samples::AlterSequence(database_admin_client, database, client);
1274+
1275+
SampleBanner("spanner_postgresql_drop_sequence");
1276+
samples::DropSequence(database_admin_client, database);
11381277
} catch (...) {
11391278
// Try to clean up after a failure.
11401279
samples::DropDatabase(database_admin_client, database);

‎google/cloud/spanner/samples/samples.cc‎

Lines changed: 169 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1324,7 +1324,7 @@ void CreateDatabaseWithEncryptionKeyCommand(std::vector<std::string> argv) {
13241324
encryption_key);
13251325
}
13261326

1327-
// [create-backup-with-encryption-key]
1327+
//! [create-backup-with-encryption-key]
13281328
// [START spanner_create_backup_with_encryption_key]
13291329
void CreateBackupWithEncryptionKey(
13301330
google::cloud::spanner_admin::DatabaseAdminClient client,
@@ -1379,7 +1379,7 @@ void CreateBackupWithEncryptionKeyCommand(std::vector<std::string> argv) {
13791379
encryption_key);
13801380
}
13811381

1382-
// [restore-database-with-encryption-key]
1382+
//! [restore-database-with-encryption-key]
13831383
// [START spanner_restore_backup_with_encryption_key]
13841384
void RestoreDatabaseWithEncryptionKey(
13851385
google::cloud::spanner_admin::DatabaseAdminClient client,
@@ -3917,6 +3917,141 @@ void PartitionQuery(google::cloud::spanner::Client client) {
39173917
//! [execute-sql-query-partition]
39183918
}
39193919

3920+
// [START spanner_create_sequence]
3921+
void CreateSequence(
3922+
google::cloud::spanner_admin::DatabaseAdminClient admin_client,
3923+
google::cloud::spanner::Client client, std::string const& project_id,
3924+
std::string const& instance_id, std::string const& database_id) {
3925+
google::cloud::spanner::Database database(project_id, instance_id,
3926+
database_id);
3927+
std::vector<std::string> statements;
3928+
statements.emplace_back(R"""(
3929+
CREATE SEQUENCE Seq
3930+
OPTIONS (sequence_kind = 'bit_reversed_positive')
3931+
)""");
3932+
statements.emplace_back(R"""(
3933+
CREATE TABLE Customers (
3934+
CustomerId INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(SEQUENCE Seq)),
3935+
CustomerName STRING(1024)
3936+
) PRIMARY KEY (CustomerId)
3937+
)""");
3938+
auto metadata =
3939+
admin_client.UpdateDatabaseDdl(database.FullName(), std::move(statements))
3940+
.get();
3941+
google::cloud::spanner_testing::LogUpdateDatabaseDdl( //! TODO(#4758)
3942+
admin_client, database, metadata.status()); //! TODO(#4758)
3943+
if (!metadata) throw std::move(metadata).status();
3944+
std::cout << "Created `Seq` sequence and `Customers` table,"
3945+
<< " where the key column `CustomerId`"
3946+
<< " uses the sequence as a default value,"
3947+
<< " new DDL:\n"
3948+
<< metadata->DebugString();
3949+
auto commit = client.Commit(
3950+
[&client](google::cloud::spanner::Transaction txn)
3951+
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
3952+
auto sql = google::cloud::spanner::SqlStatement(R"""(
3953+
INSERT INTO Customers (CustomerName)
3954+
VALUES ('Alice'),
3955+
('David'),
3956+
('Marc')
3957+
THEN RETURN CustomerId
3958+
)""");
3959+
using RowType = std::tuple<std::int64_t>;
3960+
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
3961+
// Note: This mutator might be re-run, or its effects discarded, so
3962+
// changing non-transactional state (e.g., by producing output) is,
3963+
// in general, not something to be imitated.
3964+
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
3965+
if (!row) return std::move(row).status();
3966+
std::cout << "Inserted customer record with CustomerId: "
3967+
<< std::get<0>(*row) << "\n";
3968+
}
3969+
std::cout << "Number of customer records inserted is: "
3970+
<< rows.RowsModified() << "\n";
3971+
return google::cloud::spanner::Mutations{};
3972+
});
3973+
if (!commit) throw std::move(commit).status();
3974+
}
3975+
// [END spanner_create_sequence]
3976+
3977+
// [START spanner_alter_sequence]
3978+
void AlterSequence(
3979+
google::cloud::spanner_admin::DatabaseAdminClient admin_client,
3980+
google::cloud::spanner::Client client, std::string const& project_id,
3981+
std::string const& instance_id, std::string const& database_id) {
3982+
google::cloud::spanner::Database database(project_id, instance_id,
3983+
database_id);
3984+
std::vector<std::string> statements;
3985+
statements.emplace_back(R"""(
3986+
ALTER SEQUENCE Seq
3987+
SET OPTIONS (skip_range_min = 1000, skip_range_max = 5000000)
3988+
)""");
3989+
auto metadata =
3990+
admin_client.UpdateDatabaseDdl(database.FullName(), std::move(statements))
3991+
.get();
3992+
google::cloud::spanner_testing::LogUpdateDatabaseDdl( //! TODO(#4758)
3993+
admin_client, database, metadata.status()); //! TODO(#4758)
3994+
if (!metadata) throw std::move(metadata).status();
3995+
std::cout << "Altered `Seq` sequence"
3996+
<< " to skip an inclusive range between 1000 and 5000000,"
3997+
<< " new DDL:\n"
3998+
<< metadata->DebugString();
3999+
auto commit = client.Commit(
4000+
[&client](google::cloud::spanner::Transaction txn)
4001+
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
4002+
auto sql = google::cloud::spanner::SqlStatement(R"""(
4003+
INSERT INTO Customers (CustomerName)
4004+
VALUES ('Lea'),
4005+
('Catalina'),
4006+
('Smith')
4007+
THEN RETURN CustomerId
4008+
)""");
4009+
using RowType = std::tuple<std::int64_t>;
4010+
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
4011+
// Note: This mutator might be re-run, or its effects discarded, so
4012+
// changing non-transactional state (e.g., by producing output) is,
4013+
// in general, not something to be imitated.
4014+
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
4015+
if (!row) return std::move(row).status();
4016+
std::cout << "Inserted customer record with CustomerId: "
4017+
<< std::get<0>(*row) << "\n";
4018+
}
4019+
std::cout << "Number of customer records inserted is: "
4020+
<< rows.RowsModified() << "\n";
4021+
return google::cloud::spanner::Mutations{};
4022+
});
4023+
if (!commit) throw std::move(commit).status();
4024+
}
4025+
// [END spanner_alter_sequence]
4026+
4027+
// [START spanner_drop_sequence]
4028+
void DropSequence(
4029+
google::cloud::spanner_admin::DatabaseAdminClient admin_client,
4030+
std::string const& project_id, std::string const& instance_id,
4031+
std::string const& database_id) {
4032+
google::cloud::spanner::Database database(project_id, instance_id,
4033+
database_id);
4034+
std::vector<std::string> statements;
4035+
statements.emplace_back(R"""(
4036+
ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT
4037+
)""");
4038+
statements.emplace_back(R"""(
4039+
DROP SEQUENCE Seq
4040+
)""");
4041+
auto metadata =
4042+
admin_client.UpdateDatabaseDdl(database.FullName(), std::move(statements))
4043+
.get();
4044+
google::cloud::spanner_testing::LogUpdateDatabaseDdl( //! TODO(#4758)
4045+
admin_client, database, metadata.status()); //! TODO(#4758)
4046+
if (!metadata) throw std::move(metadata).status();
4047+
std::cout << "Altered `Customers` table to"
4048+
<< " drop DEFAULT from `CustomerId` column,"
4049+
<< " and dropped the `Seq` sequence,"
4050+
<< " new DDL:\n"
4051+
<< metadata->DebugString();
4052+
}
4053+
// [END spanner_drop_sequence]
4054+
39204055
// [START spanner_query_information_schema_database_options]
39214056
void QueryInformationSchemaDatabaseOptions(
39224057
google::cloud::spanner::Client client) {
@@ -3951,6 +4086,22 @@ void QueryInformationSchemaDatabaseOptions(
39514086
}
39524087
// [END spanner_query_information_schema_database_options]
39534088

4089+
//! [drop-table]
4090+
void DropTable(google::cloud::spanner_admin::DatabaseAdminClient client,
4091+
std::string const& project_id, std::string const& instance_id,
4092+
std::string const& database_id, std::string const& table) {
4093+
google::cloud::spanner::Database database(project_id, instance_id,
4094+
database_id);
4095+
auto metadata =
4096+
client.UpdateDatabaseDdl(database.FullName(), {"DROP TABLE " + table})
4097+
.get();
4098+
google::cloud::spanner_testing::LogUpdateDatabaseDdl( //! TODO(#4758)
4099+
client, database, metadata.status()); //! TODO(#4758)
4100+
if (!metadata) throw std::move(metadata).status();
4101+
std::cout << "Table dropped.\nNew DDL:\n" << metadata->DebugString();
4102+
}
4103+
//! [drop-table]
4104+
39544105
std::string Basename(absl::string_view name) {
39554106
auto last_sep = name.find_last_of("/\\");
39564107
if (last_sep != absl::string_view::npos) name.remove_prefix(last_sep + 1);
@@ -4864,6 +5015,22 @@ void RunAll(bool emulator) {
48645015
SampleBanner("make-delete-mutation");
48655016
MakeDeleteMutation(client);
48665017

5018+
if (!emulator) {
5019+
SampleBanner("spanner_create_sequence");
5020+
CreateSequence(database_admin_client, client, project_id, instance_id,
5021+
database_id);
5022+
5023+
SampleBanner("spanner_alter_sequence");
5024+
AlterSequence(database_admin_client, client, project_id, instance_id,
5025+
database_id);
5026+
5027+
SampleBanner("spanner_drop_sequence");
5028+
DropSequence(database_admin_client, project_id, instance_id, database_id);
5029+
5030+
DropTable(database_admin_client, project_id, instance_id, database_id,
5031+
"Customers");
5032+
}
5033+
48675034
if (!emulator) {
48685035
SampleBanner("spanner_query_information_schema_database_options");
48695036
QueryInformationSchemaDatabaseOptions(client);
@@ -4885,13 +5052,11 @@ void RunAll(bool emulator) {
48855052

48865053
SampleBanner("spanner_drop_database");
48875054
DeleteAll(client);
4888-
48895055
if (!emulator) {
48905056
SampleBanner("spanner_update_database");
48915057
UpdateDatabase(database_admin_client, project_id, instance_id, database_id,
48925058
/*drop_protection=*/false);
48935059
}
4894-
48955060
DropDatabase(database_admin_client, project_id, instance_id, database_id);
48965061

48975062
if (!emulator) { // default_leader

0 commit comments

Comments
 (0)