PostgreSQL 9.6：单事务内批量插入并同步更新用户产品计数

Hey there, let's work through your problem. You need to batch insert 20k product records in a transaction while updating the user's products_count in the users table, plus keep that count in sync when products get deleted. Here's how to handle this reliably, with multiple options depending on your preference:

First, wrapping everything in a transaction is non-negotiable—it ensures if any step fails, all changes roll back, so you never end up with mismatched product records and counts.

Option A: Raw SQL (with safety notes) #

If you're using raw SQL like your example, make sure to avoid SQL injection risks (never directly interpolate user input!). Here's how to structure it:

user_id = 123 # Target user ID
product_ids = (1..20000).to_a # Your list of product IDs

# Build values for bulk insert
values = product_ids.map do |pid|
  # Use UTC time formatted correctly for your DB
  "('#{pid}', '#{user_id}', '#{Time.now.utc.strftime('%Y-%m-%d %H:%M:%S')}')"
end.join(', ')

ActiveRecord::Base.transaction do
  # Execute bulk insert
  insert_sql = "INSERT INTO products(product_id, user_id, created_at) VALUES #{values}"
  ActiveRecord::Base.connection.execute(insert_sql)

  # Get number of rows actually inserted (DB-specific syntax)
  # MySQL uses ROW_COUNT(); PostgreSQL uses GETDIAGNOSTICS row_count = ROW_COUNT;
  inserted_count = ActiveRecord::Base.connection.execute("SELECT ROW_COUNT() AS count").first['count']

  # Update the user's product count
  ActiveRecord::Base.connection.execute(
    "UPDATE users SET products_count = products_count + #{inserted_count} WHERE id = #{user_id}"
  )
end

⚠️ Critical Note: If product_id or user_id comes from user input, skip direct string interpolation—use parameterized queries to avoid SQL injection.

Option B: ActiveRecord (Safer, Rails-Friendly) #

If you're using Rails, insert_all handles parameterization automatically and returns the number of successfully created records (great if you have unique constraints that might skip duplicates):

user_id = 123
product_ids = (1..20000).to_a

ActiveRecord::Base.transaction do
  # Bulk insert products, get the count of actually created records
  creation_result = Product.insert_all(
    product_ids.map { |pid| { product_id: pid, user_id: user_id, created_at: Time.now.utc } }
  )

  # Update the user's count
  User.where(id: user_id).update_all("products_count = products_count + #{creation_result.count}")
end

Just like inserts, deletions need to update the count in a transaction to avoid inconsistencies.

Single Product Deletion #

product = Product.find_by(id: target_product_id, user_id: user_id)

ActiveRecord::Base.transaction do
  product.destroy
  User.where(id: user_id).update_all("products_count = products_count - 1")
end

Bulk Product Deletion #

product_ids_to_delete = [101, 102, 103, ...] # Your list of IDs to delete

ActiveRecord::Base.transaction do
  deleted_count = Product.where(id: product_ids_to_delete, user_id: user_id).delete_all
  User.where(id: user_id).update_all("products_count = products_count - #{deleted_count}")
end

Manually updating counts in code works, but it's easy to miss edge cases (like someone running direct SQL to delete products). Database triggers automate this sync, so counts stay correct no matter how the data is modified.

MySQL Triggers #

After Insert Trigger

DELIMITER //
CREATE TRIGGER update_product_count_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
  UPDATE users SET products_count = products_count + 1 WHERE id = NEW.user_id;
END //
DELIMITER ;

After Delete Trigger

DELIMITER //
CREATE TRIGGER update_product_count_after_delete
AFTER DELETE ON products
FOR EACH ROW
BEGIN
  UPDATE users SET products_count = products_count - 1 WHERE id = OLD.user_id;
END //
DELIMITER ;

PostgreSQL Triggers #

First create a helper function:

CREATE OR REPLACE FUNCTION update_user_product_count()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE users SET products_count = products_count + 1 WHERE id = NEW.user_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE users SET products_count = products_count - 1 WHERE id = OLD.user_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Then attach triggers to the products table:

CREATE TRIGGER trigger_product_insert
AFTER INSERT ON products
FOR EACH ROW EXECUTE FUNCTION update_user_product_count();

CREATE TRIGGER trigger_product_delete
AFTER DELETE ON products
FOR EACH ROW EXECUTE FUNCTION update_user_product_count();

Triggers take the guesswork out of maintaining counts—they run automatically every time a product is added or removed, so you don't have to remember to update the count in every code path.

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