github.com/wfusion/gofusion@v1.1.14/common/infra/watermill/pubsub/sql/schema_adapter_postgresql.go (about)

     1  package sql
     2  
     3  import (
     4  	"database/sql"
     5  	"fmt"
     6  	"strings"
     7  
     8  	"github.com/pkg/errors"
     9  
    10  	"github.com/wfusion/gofusion/common/infra/watermill/message"
    11  	"github.com/wfusion/gofusion/common/utils/serialize/json"
    12  )
    13  
    14  // DefaultPostgreSQLSchema is a default implementation of SchemaAdapter based on PostgreSQL.
    15  type DefaultPostgreSQLSchema struct {
    16  	// GenerateMessagesTableName may be used to override how the messages table name is generated.
    17  	GenerateMessagesTableName func(topic string) string
    18  
    19  	// SubscribeBatchSize is the number of messages to be queried at once.
    20  	//
    21  	// Higher value, increases a chance of message re-delivery in case of crash or networking issues.
    22  	// 1 is the safest value, but it may have a negative impact on performance when consuming a lot of messages.
    23  	//
    24  	// Default value is 100.
    25  	SubscribeBatchSize int
    26  }
    27  
    28  func (s DefaultPostgreSQLSchema) SchemaInitializingQueries(topic string) []string {
    29  	createMessagesTable := ` 
    30  		CREATE TABLE IF NOT EXISTS ` + s.MessagesTable(topic) + ` (
    31  			"offset" SERIAL,
    32  			"uuid" VARCHAR(36) NOT NULL,
    33  			"created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    34  			"payload" BYTEA DEFAULT NULL,
    35  			"metadata" JSON DEFAULT NULL,
    36  			"transaction_id" xid8 NOT NULL,
    37  			PRIMARY KEY ("transaction_id", "offset")
    38  		);
    39  	`
    40  
    41  	return []string{createMessagesTable}
    42  }
    43  
    44  func (s DefaultPostgreSQLSchema) InsertQuery(topic string, msgs message.Messages) (string, []any, error) {
    45  	insertQuery := fmt.Sprintf(
    46  		`INSERT INTO %s (uuid, payload, metadata, transaction_id) VALUES %s`,
    47  		s.MessagesTable(topic),
    48  		defaultInsertMarkers(len(msgs)),
    49  	)
    50  
    51  	args, err := defaultInsertArgs(msgs)
    52  	if err != nil {
    53  		return "", nil, err
    54  	}
    55  
    56  	return insertQuery, args, nil
    57  }
    58  
    59  func defaultInsertMarkers(count int) string {
    60  	result := strings.Builder{}
    61  
    62  	index := 1
    63  	for i := 0; i < count; i++ {
    64  		result.WriteString(fmt.Sprintf("($%d,$%d,$%d,pg_current_xact_id()),", index, index+1, index+2))
    65  		index += 3
    66  	}
    67  
    68  	return strings.TrimRight(result.String(), ",")
    69  }
    70  
    71  func (s DefaultPostgreSQLSchema) batchSize() int {
    72  	if s.SubscribeBatchSize == 0 {
    73  		return 100
    74  	}
    75  
    76  	return s.SubscribeBatchSize
    77  }
    78  
    79  func (s DefaultPostgreSQLSchema) SelectQuery(topic string,
    80  	consumerGroup string, offsetsAdapter OffsetsAdapter) (string, []any) {
    81  	// Query inspired by https://event-driven.io/en/ordering_in_postgres_outbox/
    82  
    83  	nextOffsetQuery, nextOffsetArgs := offsetsAdapter.NextOffsetQuery(topic, consumerGroup)
    84  	selectQuery := `
    85  		WITH last_processed AS (
    86  			` + nextOffsetQuery + `
    87  		)
    88  
    89  		SELECT "offset", transaction_id, uuid, payload, metadata FROM ` + s.MessagesTable(topic) + `
    90  
    91  		WHERE 
    92  		(
    93  			(
    94  				transaction_id = (SELECT last_processed_transaction_id FROM last_processed) 
    95  				AND 
    96  				"offset" > (SELECT offset_acked FROM last_processed)
    97  			)
    98  			OR
    99  			(transaction_id > (SELECT last_processed_transaction_id FROM last_processed))
   100  		)
   101  		AND 
   102  			transaction_id < pg_snapshot_xmin(pg_current_snapshot())
   103  		ORDER BY
   104  			transaction_id ASC,
   105  			"offset" ASC
   106  		LIMIT ` + fmt.Sprintf("%d", s.batchSize())
   107  
   108  	return selectQuery, nextOffsetArgs
   109  }
   110  
   111  func (s DefaultPostgreSQLSchema) DeleteQuery(topic string, offset int64) (string, []any) {
   112  	return `DELETE FROM ` + s.MessagesTable(topic) + ` WHERE "offset" = $1`, []any{offset}
   113  }
   114  
   115  func (s DefaultPostgreSQLSchema) UnmarshalMessage(row Scanner) (Row, error) {
   116  	r := Row{}
   117  	var transactionID int64
   118  
   119  	err := row.Scan(&r.Offset, &transactionID, &r.UUID, &r.Payload, &r.Metadata)
   120  	if err != nil {
   121  		return Row{}, errors.Wrap(err, "could not scan message row")
   122  	}
   123  
   124  	msg := message.NewMessage(string(r.UUID), r.Payload)
   125  
   126  	if r.Metadata != nil {
   127  		err = json.Unmarshal(r.Metadata, &msg.Metadata)
   128  		if err != nil {
   129  			return Row{}, errors.Wrap(err, "could not unmarshal metadata as JSON")
   130  		}
   131  	}
   132  
   133  	r.Msg = msg
   134  	r.ExtraData = map[string]any{
   135  		"transaction_id": transactionID,
   136  	}
   137  
   138  	return r, nil
   139  }
   140  
   141  func (s DefaultPostgreSQLSchema) MessagesTable(topic string) string {
   142  	if s.GenerateMessagesTableName != nil {
   143  		return s.GenerateMessagesTableName(topic)
   144  	}
   145  	return fmt.Sprintf(`"watermill_%s"`, topic)
   146  }
   147  
   148  func (s DefaultPostgreSQLSchema) SubscribeIsolationLevel() sql.IsolationLevel {
   149  	// For Postgres Repeatable Read is enough.
   150  	return sql.LevelSerializable
   151  }