Sometimes it is necessary to directly create a snowflake ID in the database

I use c# clr to create SnowSeed in Sqlserver

sqlserver I use c++ to create SnowSeed in Mysql

mysql

but I don't know how to write this funciton in PostgreSql with language C

postgresql bug

Sqlserver C# code

SnowSeed.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    private static readonly Dictionary<string, IdWorker> pools = new();

    [SqlFunction]
    public static SqlInt64 SnowSeed(SqlInt32 workerId,SqlInt32 datacenterId)
    {
        var key = workerId.Value + "-" + datacenterId.Value;
        if (!pools.ContainsKey(key)) pools.Add(key, new(workerId.Value, datacenterId.Value));
        return new(pools[key].NextId());
    }
}

ClassFiles.cs

using System;
public class IdWorker
{
    //基准时间
    public const long Twepoch = 1288834974657L;
    //机器标识位数
    const int WorkerIdBits = 5;
    //数据标志位数
    const int DatacenterIdBits = 5;
    //序列号识位数
    const int SequenceBits = 12;
    //机器ID最大值
    const long MaxWorkerId = -1L ^ (-1L << WorkerIdBits);
    //数据标志ID最大值
    const long MaxDatacenterId = -1L ^ (-1L << DatacenterIdBits);
    //序列号ID最大值
    private const long SequenceMask = -1L ^ (-1L << SequenceBits);
    //机器ID偏左移12位
    private const int WorkerIdShift = SequenceBits;
    //数据ID偏左移17位
    private const int DatacenterIdShift = SequenceBits + WorkerIdBits;
    //时间毫秒左移22位
    public const int TimestampLeftShift = SequenceBits + WorkerIdBits + DatacenterIdBits;

    private long _sequence = 0L;
    private long _lastTimestamp = -1L;

    public long WorkerId { get; protected set; }
    public long DatacenterId { get; protected set; }
    public long Sequence
    {
        get { return _sequence; }
        internal set { _sequence = value; }
    }
    public IdWorker(long workerId, long datacenterId, long sequence = 0L)
    {
        // 如果超出范围就抛出异常
        if (workerId > MaxWorkerId || workerId < 0) throw new ArgumentException($"workerId 必须大于0，且不能大于 MaxWorkerId： {MaxWorkerId}");
        if (datacenterId > MaxDatacenterId || datacenterId < 0) throw new ArgumentException($"datacenterId 必须大于0，且不能大于 MaxDatacenterId： {MaxDatacenterId}");
        //先检验再赋值
        WorkerId = workerId;
        DatacenterId = datacenterId;
        _sequence = sequence;
    }

    readonly object _lock = new();
    public virtual long NextId()
    {
        lock (_lock)
        {
            var timestamp = TimeGen();
            if (timestamp < _lastTimestamp) throw new Exception($"时间戳必须大于上一次生成ID的时间戳.  拒绝为{_lastTimestamp - timestamp}毫秒生成id");
            //如果上次生成时间和当前时间相同,在同一毫秒内
            if (_lastTimestamp == timestamp)
            {
                //sequence自增，和sequenceMask相与一下，去掉高位
                _sequence = (_sequence + 1) & SequenceMask;
                //判断是否溢出,也就是每毫秒内超过1024，当为1024时，与sequenceMask相与，sequence就等于0
                //等待到下一毫秒
                if (_sequence == 0) timestamp = TilNextMillis(_lastTimestamp);
            }
            //如果和上次生成时间不同,重置sequence，就是下一毫秒开始，sequence计数重新从0开始累加,
            //为了保证尾数随机性更大一些,最后一位可以设置一个随机数
            else _sequence = 0;//new Random().Next(10);
            _lastTimestamp = timestamp;
            return ((timestamp - Twepoch) << TimestampLeftShift) | (DatacenterId << DatacenterIdShift) | (WorkerId << WorkerIdShift) | _sequence;
        }
    }
    // 防止产生的时间比之前的时间还要小（由于NTP回拨等问题）,保持增量的趋势.
    protected virtual long TilNextMillis(long lastTimestamp)
    {
        var timestamp = TimeGen();
        while (timestamp <= lastTimestamp) timestamp = TimeGen();
        return timestamp;
    }
    // 获取当前的时间戳
    protected virtual long TimeGen() => (long)(DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)).TotalMilliseconds;
}

Mysql c++ code

dllmain.cpp

#include "pch.h"
#include "SnowSeed.h"
#include <unordered_map>
#include <string>
#include "ClassFiles.h"
using std::string;
bool SnowSeed_init(UDF_INIT* initid, UDF_ARGS* args, char* message) {
    bool error = false;
    int type = 0;
    if (args->arg_count != 2)  error = true;
    if (args->arg_type[0] != INT_RESULT)  error = true;
    else {
        try {
            int num = atoi(args->args[0]);
            if (num > 31)  error = true; 
        }
        catch (const std::invalid_argument& e) {
            error = true;
        }
        catch (const std::out_of_range& e) {
            error = true;
            
        }
    }
    if (args->arg_type[1] != INT_RESULT)  error = true; 
    else {
        try {
            int num = atoi(args->args[1]);
            if (num > 31)  error = true;
        }
        catch (const std::invalid_argument& e) {
            error = true;
            
        }
        catch (const std::out_of_range& e) {
            error = true;
        }
    }
    if (error) strcpy(message, (u8"参数必须是两位小于32的整数" + std::to_string(type)).c_str());
    return error;
}
static std::unordered_map<std::string, IdWorker*> pool;
long long SnowSeed(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* error) {
    int workerId = atoi(args->args[0]);
    int centerId = atoi(args->args[1]);
std:string key = std::string(args->args[0]) + "-" + std::string(args->args[1]);
    if (pool.count(key) == 0) pool.emplace(std::make_pair(key, new IdWorker(workerId, centerId)));
    return pool[key]->NextId();
}

void SnowSeed_deinit(UDF_INIT* initid) {}

SnowSeed.h

#pragma once
#pragma warning(disable: 4996)

#define UDF_DLL __declspec(dllexport) 
#include "mysql.h"  
#include "mysql/udf_registration_types.h"
#include <codecvt>
#include <wchar.h>


extern "C" {
    UDF_DLL bool SnowSeed_init(UDF_INIT* initid, UDF_ARGS* args, char* message);
    UDF_DLL long long SnowSeed(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* error);
    UDF_DLL void SnowSeed_deinit(UDF_INIT* initid);
}

ClassFiles.h

#include <chrono>
#include <mutex>
#include <stdexcept>
#include <thread>
#include <string>

class IdWorker {
public:
    static constexpr int64_t Twepoch = 1288834974657LL; // 基准时间 2010-11-04 09:42:54 UTC

    IdWorker(int32_t worker_id, int32_t datacenter_id, int64_t sequence = 0LL)
        : worker_id_(worker_id),
        datacenter_id_(datacenter_id),
        sequence_(sequence) {
        if (worker_id > MaxWorkerId || worker_id < 0) throw std::invalid_argument("workerId 必须大于0，且不能大于 MaxWorkerId： " + std::to_string(MaxWorkerId));
        if (datacenter_id > MaxDatacenterId || datacenter_id < 0) throw std::invalid_argument("datacenterId 必须大于0，且不能大于 MaxDatacenterId： " + std::to_string(MaxDatacenterId));
    }
    int64_t NextId() {
        std::lock_guard<std::mutex> lock(mutex_);
        auto timestamp = TimeGen();
        // 处理时钟回拨
        if (timestamp < last_timestamp_) throw std::runtime_error("时间戳必须大于上一次生成ID的时间戳.  拒绝为" + std::to_string(last_timestamp_ - timestamp) + "毫秒生成id");
        // 同一毫秒内生成
        if (last_timestamp_ == timestamp) {
            sequence_ = (sequence_ + 1) & SequenceMask;
            if (sequence_ == 0) timestamp = TilNextMillis(last_timestamp_);
        }
        // 新时间窗口重置序列号
        else sequence_ = 0;
        last_timestamp_ = timestamp;
        // 组合ID各部分
        return ((timestamp - Twepoch) << TimestampLeftShift)| (datacenter_id_ << DatacenterIdShift) | (worker_id_ << WorkerIdShift)| sequence_;
    }
private:
    // 位移配置
    static constexpr int WorkerIdBits = 5;
    static constexpr int DatacenterIdBits = 5;
    static constexpr int SequenceBits = 12;
    // 最大值计算
    static constexpr int32_t MaxWorkerId = -1LL ^ (-1LL << WorkerIdBits);          // 31
    static constexpr int32_t MaxDatacenterId = -1LL ^ (-1LL << DatacenterIdBits);  // 31
    static constexpr int32_t SequenceMask = -1LL ^ (-1LL << SequenceBits);         // 4095
    // 位移量
    static constexpr int WorkerIdShift = SequenceBits;                            // 12
    static constexpr int DatacenterIdShift = SequenceBits + WorkerIdBits;         // 17
    static constexpr int TimestampLeftShift = SequenceBits + WorkerIdBits + DatacenterIdBits; // 22
    // 成员变量
    int32_t worker_id_;
    int32_t datacenter_id_;
    int32_t sequence_ = 0LL;
    int64_t last_timestamp_ = -1LL;
    std::mutex mutex_;
    // 等待下一毫秒
    int64_t TilNextMillis(int64_t last_timestamp) {
        auto timestamp = TimeGen();
        while (timestamp <= last_timestamp) {
            std::this_thread::sleep_for(std::chrono::milliseconds(1));
            timestamp = TimeGen();
        }
        return timestamp;
    }
    // 获取当前时间戳
    static int64_t TimeGen() {
        using namespace std::chrono;
        auto now = system_clock::now();
        return duration_cast<milliseconds>(now.time_since_epoch()).count();
    }
};