类型选择和更新锁不起作用

Question

我是新打字机/pgsql。我正在尝试使用typeorm select and update lock，但没能让它正常工作。我的守则如下：

const queryRunner = this.dataSource.createQueryRunner();

// initiate transaction
await queryRunner.connect();
await queryRunner.startTransaction();

// sum of total points
const pointData = await queryRunner.manager
      .getRepository(PointsLedger)
      .createQueryBuilder('points_ledger')
      .useTransaction(true)
      .setLock("pessimistic_write")
      .where(`points_ledger.customerId = :customerId`, { customerId })
      .select('SUM(points_ledger.credit) - SUM(points_ledger.debit)', 'points')
      .getRawOne();

// add some condition for business logic

const pointsLedgerData = { customerId, debit, credit };
// trying to save points, but it will failed. error message is added below
const savePoints = await queryRunner.manager
      .getRepository(PointsLedger)
      .save(pointsLedgerData);

// get point summary
const pointsSummaryData = await queryRunner.manager
      .getRepository(PointsSummary)
      .createQueryBuilder('pointsSummary')
      .where(`pointsSummary.customerId = :customerId`, { customerId })
      .getRawOne();

// pointsSummaryData is null, save as new data, or update for existing data


// update point summary
const response = await queryRunner.manager
        .getRepository(PointsSummary)
        .update(
            { customerId }, 
            { totalPoints: pointData.points + debit }
        );

我得到了以下错误：

QueryFailedError: FOR UPDATE is not allowed with aggregate functions
    at PostgresQueryRunner.query (project/src/driver/postgres/PostgresQueryRunner.ts:299:19)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at SelectQueryBuilder.loadRawResults (project/src/query-builder/SelectQueryBuilder.ts:3555:25)
    at SelectQueryBuilder.getRawMany (project/src/query-builder/SelectQueryBuilder.ts:1553:29)
    at SelectQueryBuilder.getRawOne (project/src/query-builder/SelectQueryBuilder.ts:1530:17)
    at LedgerService.getPoints (project/src/server/app/ledger/ledger.service.ts:226:20)
    at LedgerService.debit (project/src/server/app/ledger/ledger.service.ts:91:27)
    at LedgerController.burnPoints (project/src/server/app/ledger/ledger.controller.ts:60:22) {
  query: 'SELECT SUM("points_ledger"."credit") - SUM("points_ledger"."debit") AS "points" FROM "points_ledger" "points_ledger" WHERE "points_ledger"."customer_id" = $1 FOR UPDATE',
  parameters: [ 'some-customer-id' ],
  driverError: error: FOR UPDATE is not allowed with aggregate functions
      at Parser.parseErrorMessage (project/node_modules/pg-protocol/src/parser.ts:369:69)
      at Parser.handlePacket (project/node_modules/pg-protocol/src/parser.ts:188:21)
      at Parser.parse (project/node_modules/pg-protocol/src/parser.ts:103:30)
      at Socket.<anonymous> (project/node_modules/pg-protocol/src/index.ts:7:48)
      at Socket.emit (node:events:527:28)
      at addChunk (node:internal/streams/readable:324:12)
      at readableAddChunk (node:internal/streams/readable:297:9)
      at Socket.Readable.push (node:internal/streams/readable:234:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
      at TCP.callbackTrampoline (node:internal/async_hooks:130:17) 

我怎样才能解决这个问题？提前谢谢。

Answer 1

typeorm select and update lock不正确。您应该更改以下代码：

// sum of total points
const pointData = await queryRunner.manager
      .getRepository(PointsLedger)
      .createQueryBuilder('points_ledger')
      .useTransaction(true)
      .setLock("pessimistic_write")
      .where(`points_ledger.customerId = :customerId`, { customerId })
      .select('SUM(points_ledger.credit) - SUM(points_ledger.debit)', 'points')
      .getRawOne();

以下代码：

// sum of total points
const pointData = await queryRunner.manager
      .getRepository(PointsLedger)
      .createQueryBuilder('points_ledger')
      .useTransaction(true)
      .setLock("pessimistic_write")
      .where(`points_ledger.customerId = :customerId`, { customerId })
      .getRawMany();

// now calculate total points manually