WSO2 API变现PostgreSQL数据库文档重复表名问题及报错求助

1. 核心问题定位 #

报错ERROR: null value in column "id" violates not-null constraint的直接原因是PostgreSQL表结构中的自增字段配置错误：

• 原文档的PostgreSQL脚本错误使用了MySQL的AUTO_INCREMENT语法（PostgreSQL不支持该关键字）；
• 虽然创建了序列，但未正确将序列与ID字段绑定，导致插入数据时ID字段无法自动生成值。

同时，原脚本中所有表复用AM_POLICY_SUBSCRIPTION表名的问题，会导致表结构被覆盖，必须参照MySQL/Oracle脚本修正表名。

2. 修正PostgreSQL表结构脚本 #

以下是修正后的完整脚本（参照MySQL表名+适配PostgreSQL语法）：

-- 表1：API与Stripe产品/计划映射
CREATE SEQUENCE AM_MONETIZATION START WITH 1 INCREMENT BY 1;
CREATE TABLE IF NOT EXISTS AM_MONETIZATION (
    API_ID INTEGER NOT NULL,
    TIER_NAME VARCHAR(512),
    STRIPE_PRODUCT_ID VARCHAR(512),
    STRIPE_PLAN_ID VARCHAR(512),
    FOREIGN KEY (API_ID) REFERENCES AM_API (API_ID) ON DELETE CASCADE
);

-- 表2：策略与Stripe产品/计划映射
CREATE SEQUENCE AM_POLICY_PLAN_MAPPING START WITH 1 INCREMENT BY 1;
CREATE TABLE IF NOT EXISTS AM_POLICY_PLAN_MAPPING (
    POLICY_ID INTEGER DEFAULT NEXTVAL('AM_POLICY_PLAN_MAPPING'),
    POLICY_UUID VARCHAR(256),
    PRODUCT_ID VARCHAR(512),
    PLAN_ID VARCHAR(512),
    -- 修正外键：关联AM_POLICY表的UUID（原脚本错误指向重复表）
    FOREIGN KEY (POLICY_UUID) REFERENCES AM_POLICY(UUID) ON DELETE CASCADE
);

-- 表3：平台客户信息
CREATE SEQUENCE AM_MONETIZATION_PLATFORM_CUSTOMERS START WITH 1 INCREMENT BY 1;
CREATE TABLE IF NOT EXISTS AM_MONETIZATION_PLATFORM_CUSTOMERS (
    POLICY_ID INTEGER DEFAULT NEXTVAL('AM_MONETIZATION_PLATFORM_CUSTOMERS'),
    -- 替换AUTO_INCREMENT为PostgreSQL自增语法，绑定序列
    ID INTEGER NOT NULL DEFAULT NEXTVAL('AM_MONETIZATION_PLATFORM_CUSTOMERS'),
    SUBSCRIBER_ID INTEGER NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    CUSTOMER_ID VARCHAR(256) NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (SUBSCRIBER_ID) REFERENCES AM_SUBSCRIBER(SUBSCRIBER_ID) ON DELETE CASCADE
);
-- 将序列与ID字段关联，避免孤立
ALTER SEQUENCE AM_MONETIZATION_PLATFORM_CUSTOMERS OWNED BY AM_MONETIZATION_PLATFORM_CUSTOMERS.ID;

-- 表4：共享客户信息
CREATE SEQUENCE AM_MONETIZATION_SHARED_CUSTOMERS START WITH 1 INCREMENT BY 1;
CREATE TABLE IF NOT EXISTS AM_MONETIZATION_SHARED_CUSTOMERS (
    POLICY_ID INTEGER DEFAULT NEXTVAL('AM_MONETIZATION_SHARED_CUSTOMERS'),
    ID INTEGER NOT NULL DEFAULT NEXTVAL('AM_MONETIZATION_SHARED_CUSTOMERS'),
    APPLICATION_ID INTEGER NOT NULL,
    API_PROVIDER VARCHAR(256) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    SHARED_CUSTOMER_ID VARCHAR(256) NOT NULL,
    PARENT_CUSTOMER_ID INTEGER NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON DELETE CASCADE,
    FOREIGN KEY (PARENT_CUSTOMER_ID) REFERENCES AM_MONETIZATION_PLATFORM_CUSTOMERS(ID) ON DELETE CASCADE
);
ALTER SEQUENCE AM_MONETIZATION_SHARED_CUSTOMERS OWNED BY AM_MONETIZATION_SHARED_CUSTOMERS.ID;

-- 表5：订阅信息
CREATE SEQUENCE AM_MONETIZATION_SUBSCRIPTIONS START WITH 1 INCREMENT BY 1;
CREATE TABLE IF NOT EXISTS AM_MONETIZATION_SUBSCRIPTIONS (
    POLICY_ID INTEGER DEFAULT NEXTVAL('AM_MONETIZATION_SUBSCRIPTIONS'),
    ID INTEGER NOT NULL DEFAULT NEXTVAL('AM_MONETIZATION_SUBSCRIPTIONS'),
    SUBSCRIBED_APPLICATION_ID INTEGER NOT NULL,
    SUBSCRIBED_API_ID INTEGER NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    SUBSCRIPTION_ID VARCHAR(256) NOT NULL,
    SHARED_CUSTOMER_ID INTEGER NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (SUBSCRIBED_APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON DELETE CASCADE,
    FOREIGN KEY (SUBSCRIBED_API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
    FOREIGN KEY (SHARED_CUSTOMER_ID) REFERENCES AM_MONETIZATION_SHARED_CUSTOMERS(ID) ON DELETE CASCADE
);
ALTER SEQUENCE AM_MONETIZATION_SUBSCRIPTIONS OWNED BY AM_MONETIZATION_SUBSCRIPTIONS.ID;

关键修正点： #

• 替换所有重复的AM_POLICY_SUBSCRIPTION表名为对应MySQL的表名；
• 将AUTO_INCREMENT替换为PostgreSQL的DEFAULT NEXTVAL('序列名')，并通过ALTER SEQUENCE将序列与字段绑定；
• 修正原脚本中错误的外键引用（如POLICY_UUID关联到AM_POLICY表而非重复表）。

3. 验证工作流配置 #

检查api-manager.xml中的工作流执行器配置，确保使用正确的Stripe执行器类名（替换占位符）：

<SubscriptionCreation executor="org.wso2.carbon.apimgt.impl.workflow.StripeSubscriptionCreationWorkflowExecutor"/>
<SubscriptionDeletion executor="org.wso2.carbon.apimgt.impl.workflow.StripeSubscriptionDeletionWorkflowExecutor"/>

4. 修复后的操作步骤 #

1. 停止WSO2 APIM服务；
2. 删除之前错误创建的表（若已创建）；
3. 执行修正后的PostgreSQL脚本；
4. 启动WSO2 APIM服务；
5. 重新创建变现策略并尝试订阅API。

5. 额外检查点 #

• 确认Stripe配置（API密钥、Webhook等）已正确在api-manager.xml中配置；
• 检查数据库用户是否有足够权限执行表创建、插入操作；
• 查看WSO2 APIM日志中的详细报错，若仍有问题，重点排查外键关联是否正确。

内容的提问来源于stack exchange，提问作者Iam8139