使用CTE后PostgreSQL执行SELECT *无返回值问题排查

我尝试创建新用户，同时将新用户ID与Google ID写入auth表，期望返回新用户数据。尽管users和auth表的记录都已成功创建，但执行SELECT语句后没有结果返回。

原SQL查询语句 #

WITH returned_user_id AS (
    INSERT INTO users (display_name, avatar)
    VALUES ('AAA', 'AAA')
    RETURNING id
),
    inserted_auth AS (
        INSERT INTO auth (user_id, google_id)
        SELECT id, '31231231'
        FROM returned_user_id
    )
SELECT *
FROM users
WHERE id = (SELECT user_id FROM auth WHERE google_id = '31231231');

原后端代码 #

// CREATE NEW ACCOUNT
async create(googleProfile:GoogleProfile):Promise<DataSingle>{
    const {googleId, displayName, avatar} = googleProfile

    try {
        const {rows} = await pool.query(`
         WITH returned_user_id AS (
        INSERT INTO users (display_name, avatar)
        VALUES ($1, $2) 
        RETURNING id
    ),
        inserted_auth AS (
        INSERT INTO auth (user_id, google_id) 
        SELECT id, $3
        FROM returned_user_id
        )
    SELECT *
    FROM users
    WHERE id = (SELECT user_id FROM auth WHERE google_id = $3);
    `, [displayName, avatar, googleId])

    return {
        data: snakeToCamel(rows)[0],
        error:null,
        status: 201
    };
}catch (err){
    console.error(err)
    return {
        data: null,
        error: "Could not create the user",
        status: 400
    };
}

问题原因 #

PostgreSQL中，写操作的CTE（如INSERT）如果没有指定RETURNING子句，其修改在当前查询的后续语句中可能无法被立即看到——因为这类CTE的执行是延迟的，导致最后查询auth表时，新插入的记录还不在当前查询的可见范围内。另外，通过auth表反向查询users的方式不仅多余，还会触发这个可见性问题。

修复方案 #

修复后的SQL语句 #

给inserted_auth添加RETURNING user_id，并直接通过这个返回的ID关联users表查询，避免依赖auth表的查询：

WITH returned_user_id AS (
    INSERT INTO users (display_name, avatar)
    VALUES ('AAA', 'AAA')
    RETURNING id
),
inserted_auth AS (
    INSERT INTO auth (user_id, google_id)
    SELECT id, '31231231'
    FROM returned_user_id
    RETURNING user_id -- 添加RETURNING确保修改对后续查询可见
)
SELECT u.*
FROM users u
JOIN inserted_auth ia ON u.id = ia.user_id; -- 直接关联返回的user_id

修复后的后端代码 #

// CREATE NEW ACCOUNT
async create(googleProfile: GoogleProfile): Promise<DataSingle> {
    const { googleId, displayName, avatar } = googleProfile;

    try {
        const { rows } = await pool.query(`
            WITH returned_user_id AS (
                INSERT INTO users (display_name, avatar)
                VALUES ($1, $2) 
                RETURNING id
            ),
            inserted_auth AS (
                INSERT INTO auth (user_id, google_id) 
                SELECT id, $3
                FROM returned_user_id
                RETURNING user_id
            )
            SELECT u.*
            FROM users u
            JOIN inserted_auth ia ON u.id = ia.user_id;
        `, [displayName, avatar, googleId]);

        return {
            data: snakeToCamel(rows)[0],
            error: null,
            status: 201
        };
    } catch (err) {
        console.error(err);
        return {
            data: null,
            error: "Could not create the user",
            status: 400
        };
    }
}

更简洁的写法 #

如果只需要返回新创建的用户数据，可直接在第一个CTE返回所有用户字段，无需二次查询：

WITH returned_user AS (
    INSERT INTO users (display_name, avatar)
    VALUES ('AAA', 'AAA')
    RETURNING * -- 返回所有用户字段
),
inserted_auth AS (
    INSERT INTO auth (user_id, google_id)
    SELECT id, '31231231'
    FROM returned_user
    RETURNING user_id
)
SELECT * FROM returned_user; -- 直接返回用户数据

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