Sample 3 node insecure CockroachDB cluster with HAProxy acting as load balancer and a sample EventSourcingIntro application using MartenDB

Prerequisites:

Services

• roach-0 - CockroachDB node
• roach-1 - CockroachDB node
• roach-2 - CockroachDB node
• lb - HAProxy acting as load balancer
• pgbouncer - Lightweight connection pooling utility
• client - client machine containing cockroach binary
• app - dotnet application

Getting started

If you are using Google Chrome as your browser, you may want to navigate here chrome://flags/#allow-insecure-localhost and set this flag to Enabled.

1. Start the tutorial using ./up.sh script

Creating network "cockroach-pgbouncer_default" with the default driver
Creating roach-0 ... done
Creating roach-1 ... done
Creating roach-2 ... done
Creating lb      ... done
Creating pgbouncer ... done
Creating client    ... done
Cluster successfully initialized
CREATE ROLE

Time: 9ms

GRANT

Time: 87ms

2. visit the HAProxy UI

Open Interactive Shells

docker exec -ti roach-0 /bin/bash
docker exec -ti roach-1 /bin/bash
docker exec -ti roach-2 /bin/bash
docker exec -ti lb /bin/sh
docker exec -ti client /bin/bash

# cli inside the container
cockroach sql --insecure --host=lb

# directly
docker exec -ti client cockroach sql --insecure --host=lb

3. Inspect the app

docker logs app

WIP

Unhandled exception. Marten.Exceptions.MartenSchemaException: DDL Execution for 'All Configured Changes' Failed!

CREATE OR REPLACE FUNCTION public.mt_immutable_timestamp(value text) RETURNS timestamp without time zone LANGUAGE sql IMMUTABLE AS
$function$
select value::timestamp

$function$;


CREATE OR REPLACE FUNCTION public.mt_immutable_timestamptz(value text) RETURNS timestamp with time zone LANGUAGE sql IMMUTABLE AS
$function$
select value::timestamptz

$function$;


CREATE OR REPLACE FUNCTION public.mt_grams_vector(text)
        RETURNS tsvector
        LANGUAGE plpgsql
        IMMUTABLE STRICT
AS $function$
BEGIN
        RETURN (SELECT array_to_string(mt_grams_array($1), ' ')::tsvector);
END
$function$;


CREATE OR REPLACE FUNCTION public.mt_grams_query(text)
        RETURNS tsquery
        LANGUAGE plpgsql
        IMMUTABLE STRICT
AS $function$
BEGIN
        RETURN (SELECT array_to_string(mt_grams_array($1), ' & ')::tsquery);
END
$function$;


CREATE OR REPLACE FUNCTION public.mt_grams_array(words text)
        RETURNS text[]
        LANGUAGE plpgsql
        IMMUTABLE STRICT
AS $function$
        DECLARE result text[];
        DECLARE word text;
        DECLARE clean_word text;
        BEGIN
                FOREACH word IN ARRAY string_to_array(words, ' ')
                LOOP
                     clean_word = regexp_replace(word, '[^a-zA-Z0-9]+', '','g');
                     FOR i IN 1 .. length(clean_word)
                     LOOP
                         result := result || quote_literal(substr(lower(clean_word), i, 1));
                         result := result || quote_literal(substr(lower(clean_word), i, 2));
                         result := result || quote_literal(substr(lower(clean_word), i, 3));
                     END LOOP;
                END LOOP;

                RETURN ARRAY(SELECT DISTINCT e FROM unnest(result) AS a(e) ORDER BY e);
        END;
$function$;



 ---> Npgsql.PostgresException (0x80004005): 0A000: at or near "language": syntax error: unimplemented: this syntax

DETAIL: source SQL:
CREATE OR REPLACE FUNCTION public.mt_grams_vector(text)
        RETURNS tsvector
        LANGUAGE plpgsql
        ^
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|213_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Weasel.Postgresql.PostgresqlMigrator.executeDelta(SchemaMigration migration, DbConnection conn, AutoCreate autoCreate, IMigrationLogger logger)
  Exception data:
    Severity: ERROR
    SqlState: 0A000
    MessageText: at or near "language": syntax error: unimplemented: this syntax
    Detail: source SQL:
CREATE OR REPLACE FUNCTION public.mt_grams_vector(text)
        RETURNS tsvector
        LANGUAGE plpgsql
        ^
    Hint: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/7821/v22.2
    File: lexer.go
    Line: 242
    Routine: UnimplementedWithIssueDetail
   --- End of inner exception stack trace ---
   at Marten.StoreOptions.Weasel.Core.Migrations.IMigrationLogger.OnFailure(DbCommand command, Exception ex)
   at Weasel.Postgresql.PostgresqlMigrator.executeDelta(SchemaMigration migration, DbConnection conn, AutoCreate autoCreate, IMigrationLogger logger)
   at Weasel.Core.Migrator.ApplyAll(DbConnection conn, SchemaMigration migration, AutoCreate autoCreate, IMigrationLogger logger)
   at Weasel.Core.Migrations.DatabaseBase`1.executeMigration(ISchemaObject[] schemaObjects, CancellationToken token)
   at Weasel.Core.Migrations.DatabaseBase`1.executeMigration(ISchemaObject[] schemaObjects, CancellationToken token)
   at Weasel.Core.Migrations.DatabaseBase`1.generateOrUpdateFeature(Type featureType, IFeatureSchema feature, CancellationToken token)
   at Weasel.Core.Migrations.DatabaseBase`1.ensureStorageExists(IList`1 types, Type featureType, CancellationToken token)
   at Weasel.Core.Migrations.DatabaseBase`1.ensureStorageExists(IList`1 types, Type featureType, CancellationToken token)
   at Weasel.Core.Migrations.DatabaseBase`1.EnsureStorageExists(Type featureType)
   at Marten.Storage.MartenDatabase.Marten.Storage.IMartenDatabase.EnsureStorageExists(Type documentType)
   at Marten.Linq.MartenLinqQueryProvider.ensureStorageExists(LinqHandlerBuilder builder)
   at Marten.Linq.MartenLinqQueryProvider.Execute[TResult](Expression expression)
   at WarehouseRepository.Get(Guid id) in /app/Program.cs:line 60
   at Program.<Main>$(String[] args) in /app/Program.cs:line 18