Skip to content
TheChatArchiveThe Chat Archive
o1-mini-2024-09-12· OpenAIAi2 WildChat-4.8M (AI2)

Error executing query { text: 'INSERT INTO productcategories (productid, categoryid) \n'…

  1. User

    Error executing query { text: 'INSERT INTO product_categories (product_id, category_id) \n' + ' VALUES (1,1, 2)', error: error: current transaction is aborted, commands ignored until end of transaction block at /var/task/node_modules/pg/lib/client.js:526:17 at process.processTicksAndRejections (node:internal/process/task_queues:95:5) { length: 145, severity: 'ERROR', code: '25P02', detail: undefined, hint: undefined, position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'postgres.c', line: '1500', routine: 'exec_parse_message' } } Error updating product: error: current transaction is aborted, commands ignored until end of transaction block at /var/task/node_modules/pg/lib/client.js:526:17 at process.processTicksAndRejections (node:internal/process/task_queues:95:5) { length: 145, severity: 'ERROR', code: '25P02', detail: undefined, hint: undefined, position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'postgres.c', line: '1500', routine: 'exec_parse_message' } export const updateProduct = async (req: Request, res: Response) => { const { id } = req.params; const productData = req.body;

    try {
        await query('BEGIN');
    
        // Adjust price if necessary
        if (productData.price < 1000) {
            productData.price *= 1000;
        }
    
        // Update product details
        const result = await query(
            `UPDATE products 
             SET name = $1, 
                 description = $2, 
                 brand_id = $3, 
                 has_color = $4, 
                 has_size = $5, 
                 product_type = $6, 
                 price = $7, 
                 qty = $8, 
                 updated_at = CURRENT_TIMESTAMP 
             WHERE id = $9 
             RETURNING *`,
            [
                productData.name,
                productData.description,
                productData.brand_id,
                productData.hasColor,
                productData.hasSize,
                productData.product_type,
                productData.price,
                productData.qty,
                id
            ]
        );
    
        if (result.rows.length === 0) {
            await query('ROLLBACK');
            return res.status(404).json({ error: 'Product not found' });
        }
    
        // Delete existing associations and data
        await query('DELETE FROM product_categories WHERE product_id = $1', [id]);
        await query('DELETE FROM images WHERE product_id = $1', [id]);
    
        // Get existing colors and sizes
        const existingColors = await query('SELECT id FROM colors WHERE product_id = $1', [id]);
        const existingColorIds = new Set(existingColors.rows.map(row => row.id));
    
        const existingSizes = await query('SELECT id FROM sizes WHERE product_id = $1', [id]);
        const existingSizeIds = new Set(existingSizes.rows.map(row => row.id));
    
        // Determine colors and sizes to delete
        const updatedColorIds = new Set(
            productData.colors
                .map((color: { id: any }) => color.id)
                .filter((id: any) => id !== undefined)
        );
        const colorsToDelete = [...existingColorIds].filter(id => !updatedColorIds.has(id));
    
        const updatedSizeIds = new Set([
            ...productData.sizes
                .map((size: { id: any }) => size.id)
                .filter((id: any) => id !== undefined),
            ...productData.colors.flatMap((color: { sizes: any[] }) =>
                color.sizes.map(size => size.id).filter(id => id !== undefined)
            )
        ]);
        const sizesToDelete = [...existingSizeIds].filter(id => !updatedSizeIds.has(id));
    
        // Delete colors and associated sizes and images
        for (const colorId of colorsToDelete) {
            await query('DELETE FROM sizes WHERE color_id = $1', [colorId]);
            await query('DELETE FROM images WHERE color_id = $1', [colorId]);
            await query('DELETE FROM colors WHERE id = $1', [colorId]);
        }
    
        // Delete sizes and associated images
        for (const sizeId of sizesToDelete) {
            await query('DELETE FROM images WHERE size_id = $1', [sizeId]);
            await query('DELETE FROM sizes WHERE id = $1', [sizeId]);
        }
    
        // Insert category associations
        const categoryInsertPromises = productData.category_ids.map((categoryId: number) =>
            query(
                `INSERT INTO product_categories (product_id, category_id) 
                 VALUES ($1, $2)`,
                [id, categoryId]
            )
        );
        await Promise.all(categoryInsertPromises);
    
        // Insert images
        const imagesInsertPromises = productData.images.map((imageUrl: string) =>
            query(
                `INSERT INTO images (product_id, url) 
                 VALUES ($1, $2)`,
                [id, imageUrl]
            )
        );
        await Promise.all(imagesInsertPromises);
    
        // Handle colors and sizes based on product type
        if (productData.hasColor) {
            for (const color of productData.colors) {
                // Adjust color price if necessary
                if (color.price < 1000) {
                    color.price *= 1000;
                }
    
                let colorId: number;
                if (color.id) {
                    // Update existing color
                    await query(
                        `UPDATE colors 
                         SET name = $1, 
                             has_size = $2, 
                             price = $3, 
                             qty = $4, 
                             sold = $5 
                         WHERE id = $6`,
                        [color.name, color.hasSize, color.price, color.qty, color.sold || 0, color.id]
                    );
                    colorId = color.id;
                } else {
                    // Insert new color
                    const colorResult = await query(
                        `INSERT INTO colors (product_id, name, has_size, price, qty, sold)
                         VALUES ($1, $2, $3, $4, $5, 0) RETURNING id`,
                        [id, color.name, color.hasSize, color.price, color.qty]
                    );
                    colorId = colorResult.rows[0].id;
                }
    
                // Insert color images
                const colorImagesInsertPromises = color.images.map((imageUrl: string) =>
                    query(
                        `INSERT INTO images (product_id, color_id, url)
                         VALUES ($1, $2, $3)`,
                        [id, colorId, imageUrl]
                    )
                );
                await Promise.all(colorImagesInsertPromises);
    
                if (color.hasSize) {
                    for (const size of color.sizes) {
                        // Adjust size price if necessary
                        if (size.price < 1000) {
                            size.price *= 1000;
                        }
    
                        let sizeId: number;
                        if (size.id) {
                            // Update existing size
                            await query(
                                `UPDATE sizes 
                                 SET name = $1, 
                                     price = $2, 
                                     qty = $3, 
                                     sold = $4 
                                 WHERE id = $5`,
                                [size.name, size.price, size.qty, size.sold || 0, size.id]
                            );
                            sizeId = size.id;
                        } else {
                            // Insert new size
                            const sizeResult = await query(
                                `INSERT INTO sizes (product_id, color_id, name, price, qty, sold)
                                 VALUES ($1, $2, $3, $4, $5, 0) RETURNING id`,
                                [id, colorId, size.name, size.price, size.qty]
                            );
                            sizeId = sizeResult.rows[0].id;
                        }
    
                        // Insert size images
                        const sizeImagesInsertPromises = size.images.map((imageUrl: string) =>
                            query(
                                `INSERT INTO images (product_id, color_id, size_id, url)
                                 VALUES ($1, $2, $3, $4)`,
                                [id, colorId, sizeId, imageUrl]
                            )
                        );
                        await Promise.all(sizeImagesInsertPromises);
                    }
                }
            }
        } else if (productData.hasSize) {
            for (const size of productData.sizes) {
                // Adjust size price if necessary
                if (size.price < 1000) {
                    size.price *= 1000;
                }
    
                let sizeId: number;
                if (size.id) {
                    // Update existing size
                    await query(
                        `UPDATE sizes 
                         SET name = $1, 
                             price = $2, 
                             qty = $3, 
                             sold = $4 
                         WHERE id = $5`,
                        [size.name, size.price, size.qty, size.sold || 0, size.id]
                    );
                    sizeId = size.id;
                } else {
                    // Insert new size
                    const sizeResult = await query(
                        `INSERT INTO sizes (product_id, name, price, qty, sold)
                         VALUES ($1, $2, $3, $4, 0) RETURNING id`,
                        [id, size.name, size.price, size.qty]
                    );
                    sizeId = sizeResult.rows[0].id;
                }
    
                // Insert size images
                const sizeImagesInsertPromises = size.images.map((imageUrl: string) =>
                    query(
                        `INSERT INTO images (product_id, size_id, url)
                         VALUES ($1, $2, $3)`,
                        [id, sizeId, imageUrl]
                    )
                );
                await Promise.all(sizeImagesInsertPromises);
            }
        }
    
        // Invalidate the all_products cache
    
        await query('COMMIT');
        res.json({ message: 'Product updated successfully', product: result.rows[0] });
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error updating product:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
    

    }; this is issue is occuring only in this product while it is fine with other products... {"products":[{"id":904,"name":"Beauty of Joseon Relief Sun Aqua-Fresh: Rice + B5 Spf50","description":"واقي شمسي عضوي كريمي خفيف الوزن يوفر الراحة القصوى بينما يغذي بشرتك.\n هذه التركيبة الجديدة مصنوعة من ماء بذور الأرز والبانثينول لتوفير ترطيب منعش مع حماية بشرتك من الأشعة فوق البنفسجية الضارة والضغوط البيئية. \nمصمم لأولئك الذين يفضلون لمسة نهائية شفافة وغير دهنية ولمسة نهائية ندية سريعة الامتصاص. \nترطيب وتهدئة الدفاع عن الأشعة فوق البنفسجية.\nالمكونات الرئيسية:\n-ماء بذور الأرز (30%) يعد الأرز أحد مكونات الجمال الرائعة، فهو مليء بالفيتامينات والمعادن ومضادات الأكسدة التي تساعد على استعادة صحة البشرة الطبيعية، مما يجعلها نظيفة ومشرقة.\n-بانثينول (فيتامين ب5) يساعد البانثينول، المعروف بخصائصه المرطبة والمهدئة الاستثنائية، على تقوية حاجز رطوبة البشرة وتهدئة البشرة المتهيجة بسبب الأشعة فوق البنفسجية والعوامل البيئية.","brand_id":115,"has_color":false,"has_size":true,"product_type":"size","price":"0.00","qty":0,"created_at":"2024-09-16T12:48:57.175Z","updated_at":"2024-09-22T10:25:15.616Z","categories":[{"id":173,"name":"Face Sunscreens","main_category_id":1,"main_category_name":"Face Care"}],"colors":[],"sizes":[{"id":2068,"qty":47,"name":"50 ml","sold":0,"price":25000,"images":["2039.webp"],"color_id":null}],"images":["2039.webp"]},{"id":181,"name":"Beauty Of Joseon Ginseng Moist Sun Serum","description":"سيروم الجينسنغ المرطب مع وقايه من الشمس 50% من بيوتي اوف جونسون\nسيروم مغذي يحمي بشرتك أيضًا من الأشعة الضارة. \nمليء بمستخلص الجينسينغ 30% الذي يوفر قوة مكثفة مضادة للشيخوخة لتعزيز مرونة البشرة والحفاظ على رطوبة ونعومة بشرتك.\nبينما تحافظ 2% نياسيناميد على توازن الزيت والماء لبشرتك مع إزالة خلايا الجلد الميتة والشوائب، مما يخلق بشرة متوهجة وشابة المظهر.","brand_id":115,"has_color":false,"has_size":true,"product_type":"size","price":"0.00","qty":0,"created_at":"2024-08-25T23:42:10.866Z","updated_at":"2024-09-15T18:51:15.964Z","categories":[{"id":170,"name":"Serums","main_category_id":1,"main_category_name":"Face Care"}],"colors":[],"sizes":[{"id":1804,"qty":56,"name":"50 ml","sold":0,"price":20000,"images":["1234.webp"],"color_id":null}],"images":["1234.webp"]},{"id":180,"name":"Beauty of Joseon Ground Rice and Honey Glow Mask","description":"اكتشفي قوة الأرز والعسل مع الماسك القابل للغسل من Beauty of Joseon\n-يجمع هذا الماسك الكل في واحد بين فوائد التفتيح ومضادات الأكسدة والترطيب وتحسين مرونة الجلد والتقشير والتأثيرات المهدئة .\n-يمتص طين الكاولين الدهون بفعالية وينقي المسام، بينما يوفر العسل الرطوبة ويهدئ الالتهاب. وهذا يضمن ترك البشرة نقية ورطبة دون أي شد.\n-يعمل مسحوق قشر الأرز الطبيعي على تقشير خلايا الجلد الميتة والشوائب بلطف، بينما يوفر العسل ترطيبًا عميقًا ويساعد في تقليل مشاكل البشرة، مما يجعله مناسبًا لجميع أنواع البشرة.","brand_id":115,"has_color":false,"has_size":true,"product_type":"size","price":"0.00","qty":0,"created_at":"2024-08-25T23:39:59.497Z","updated_at":"2024-09-20T16:38:29.628Z","categories":[{"id":175,"name":"Masks","main_category_id":1,"main_category_name":"Face Care"}],"colors":[],"sizes":[{"id":636,"qty":24,"name":"150 ml","sold":0,"price":25000,"images":["1233.jpeg"],"color_id":null}],"images":["1233.jpeg"]},{"id":148,"name":"Beauty of Joseon Ginseng Cleansing Oil","description":"يجمع الغسول الزيتي بخلاصة الجينسنغ من بيوتي اوف جوسون بين العديد من الزيوت المغذية للبشرة مثل زيت فول الصويا وزيت بذور المورينغا وزيت بذور الجينسنغ لفتح المسام وإزالة البقايا. يترك زيت التنظيف هذا الذي يركز على الهانبانج البشرة الأكثر نضارة وتغذيةً.\nزيت الصويا: يحتوي على الأحماض الدهنية مثل حمض الأوليك وحمض اللينوليك وحمض اللينولينيك التي تعمل على تلطيف البشرة وتنعيمها. يقوي حاجز البشرة ويحسن الاحتفاظ بالرطوبة ويقلل بشكل واضح من فرط التصبغ.\nزيت بذور المورينجا: يغذي البشرة ويهدئها بحمض الأوليك ومضادات الأكسدة والأحماض الأمينية. يحمي من فقدان الرطوبة ويوفر فوائد مقاومة الشيخوخة مثل تقليل الخطوط الدقيقة والتجاعيد.\nزيت بذور الجينسنغ: يدافع عن البشرة ضد عوامل الإجهاد البيئية من خلال تقوية حاجز الجلد.\nخالٍ من العطور الاصطناعية والبارابين والكحول","brand_id":115,"has_color":false,"has_size":true,"product_type":"size","price":"0.00","qty":0,"created_at":"2024-08-25T18:25:41.250Z","updated_at":"2024-09-16T09:58:37.022Z","categories":[{"id":191,"name":"Cleansing oil","main_category_id":1,"main_category_name":"Face Care"}],"colors":[],"sizes":[{"id":1801,"qty":19,"name":"210 ml","sold":0,"price":25000,"images":["1201.webp"],"color_id":null}],"images":["1201.webp"]},{"id":87,"name":"Beauty of Joseon Glow Replenishing Rice Milk","description":"تونر يومي متعدد الاستخدامات من Beauty of Joseon\nيعمل تونر حليب الأرز المجدد على تحقيق أقصى استفادة من العناية بالبشرة. \nيتغلغل مستخلص الأرز الغني بالمغذيات وأحماض الأرز الأمينية بعمق لترطيب وتنعيم البشرة. \nيتميز هذا التونر بتركيبة مزدوجة الطبقات فريدة من نوعها: طبقة ترطيب مملوءة بمستخلص الأرز للترطيب وطبقة بودرة للتحكم في الدهون. \nتساعد على تهدئة البشرة الحساسة وخلق ملمس أكثر نعومة، مما يجعله خيارًا مثاليًا للحصول على بشرة متألقة.","brand_id":115,"has_color":false,"has_size":true,"product_type":"size","price":"0.00","qty":0,"created_at":"2024-08-25T00:57:12.771Z","updated_at":"2024-08-25T00:57:12.771Z","categories":[{"id":169,"name":"Toners","main_category_id":1,"main_category_name":"Face Care"}],"colors":[],"sizes":[{"id":111,"qty":0,"name":"150 ml","sold":0,"price":25000,"images":["1146.jpeg"],"color_id":null}],"images":["1146.jpeg"]},{"id":179,"name":"‏Beauty of Joseon Red Bean Refreshing Pore Mask","description":"ماسك للبشرة للعناية بالمسام والتقشير من بيوتي اوف جوسن\nيحتوي على  الفاصوليا الحمراء  والكاولين كمكونات رئيسية.\nيمتص الكاولين الدهون ، ويساعد مستخلص الفاصوليا الحمراء ومسحوق الفاصوليا الحمراء في تقشير البشرة وتنظيفها.\nقوامه طيني رطب: تساعد تركيبة الطين الناعمة المرطبة على امتصاص الدهون وإزالة الاوساخ المتراكمة دون جفاف أو شد. في الوقت نفسه ، فإنه يوفر شعورًا منعشًا .","brand_id":115,"has_color":false,"has_size":true,"product_type":"size","price":"0.00","qty":0,"created_at":"2024-08-25T23:13:04.160Z","updated_at":"2024-09-16T10:00:41.131Z","categories":[{"id":175,"name":"Masks","main_category_id":1,"main_category_name":"Face Care"}],"colors":[],"sizes":[{"id":1809,"qty":31,"name":"140 ml","sold":-4,"price":25000,"images":["1232.webp"],"color_id":null}],"images":["1232.webp"]},{"id":178,"name":"‏Beauty of Joseon Dynasty Cream","description":"داينستي كريم من بيوتي اوف جوسن\n كريم مفتح ومقاوم للشيخوخة مصنوع من مكونات الهانبانج الطبيعية لبشرة أكثر صحة. \nتحتوي هذه التركيبة المعاد صياغتها حديثًا على الجينسنغ المنعش وماء الأرز المشرق للمساعدة في تماسك البشرة وتنعيمها وترطيبها ، بينما يعمل مستخلص العسل والسكوالين والسيراميد على تقوية حاجز الجلد وحمايته.\nمناسب لجميع أنواع البشرة ، حتى البشرة الحساسة","brand_id":115,"has_color":false,"has_size":true,"product_type":"size","price":"0.00","qty":0,"created_at":"2024-08-25T21:41:57.937Z","updated_at":"2024-09-22T10:33:03.540Z","categories":[{"id":172,"name":"Face moisturiser","main_category_id":1,"main_category_name":"Face Care"}],"colors":[],"sizes":[{"id":1796,"qty":42,"name":"50 ml","sold":0,"price":25000,"images":["1231.jpeg"],"color_id":null}],"images":["1231.jpeg"]},{"id":177,"name":"‏Beauty of Joseon Red Bean Water Gel","description":"مرطب جل الفاصوليا الحمراء من بيوتي اوف جوسن\nمرطب جل خفيف ومنعش يساعد على ترطيب البشرة وتنشيطها. \nهذا المرطب الهلامي الكوري يرطب ويغذي بشرتك بعمق ، ويتركها ناعمة ونضرة ومنتعشة. \nتمتص التركيبة خفيفة الوزن وغير الدهنية بسرعة في بشرتك ، مما يوفر ترطيبًا طويل الأمد دون انسداد المسام أو ترك أي بقايا لزجة. \nويساعد أيضًا على تحسين ملمس بشرتك ، مما يجعلها أكثر نعومة وأكثر تناسقًا. \nمناسب لجميع أنواع البشرة","brand_id":115,"has_color":false,"has_size":true,"product_type":"size","price":"0.00","qty":0,"created_at":"2024-08-25T21:37:30.365Z","updated_at":"2024-09-16T10:05:41.886Z","categories":[{"id":172,"name":"Face moisturiser","main_category_id":1,"main_category_name":"Face Care"}],"colors":[],"sizes":[{"id":1810,"qty":16,"name":"100 ml","sold":0,"price":25000,"images":["1229.jpeg"],"color_id":null}],"images":["1229.jpeg"]},{"id":176,"name":"‏Beauty of Joseon Apricot Blossom Peeling Gel","description":"مقشر جل بزهر المشمش من ‏Beauty of Joseon\nجل مقشر يحتوي على 19% من ماء زهرة المشمش و8% من السليلوز المشتق من النباتات يزيل خلايا الجلد الميت و يساعد على تحسين مشاكل الجلد \nيحتوي جل التقشير المضاد للحساسية على إنزيمات طبيعية من المشمش والتفاح لإزالة خلايا الجلد الميتة بلطف وتنظيف المسام المسدودة.\nستساعد هذه المكونات الطبيعية أيضًا على تقوية حاجز الجلد لمكافحة الشيخوخة وتفتيح البشرة\nو تساعد على جعل ملمس البشرة ناعمًا وتترك بشرتك رطبة بشكل مكثف","brand_id":115,"has_color":false,"has_size":true,"product_type":"size","price":"0.00","qty":0,"created_at":"2024-08-25T21:35:46.880Z","updated_at":"2024-09-12T16:30:03.129Z","categories":[{"id":177,"name":"Face Exfoliating Acids","main_category_id":1,"main_category_name":"Face Care"}],"colors":[],"sizes":[{"id":1795,"qty":5,"name":"100 ml","sold":0,"price":20000,"images":["1228.jpeg"],"color_id":null}],"images":["1228.jpeg"]},{"id":175,"name":"‏Beauty of Joseon Green Plum Refreshing Toner Aha + Bha","description":"تونر منعش بالخوخ و أحماض AHA+BHA\n-تونر مقشر منعش ومرطب يخلق بشرة نظيفة وصافية\n-يزيل بلطف خلايا الجلد الميتة والشوائب الموجودة داخل المسام باستخدام الفقاعات الدقيقة الغنية.\n-يوفر السيطرة على الدهون لمنع مشاكل الجلد ويساعد على شد المسام للحصول على بشرة صافية.\n-غني بماء البرقوق الأخضر بنسبة 25٪ وحمض الجليكوليك بنسبة 2٪ ، وهو نوع من حمض ألفا هيدروكسي يزيل الجلد الميت بلطف الخلايا والشوائب \n- بمساعدة ماء نخالة الأرز وخلاصة العسل يترك بشرة مشرقة ورطبة وممتلئة.\n-يعزز خلاصة Portulaca Oleracea خصائص مهدئة ومضادة للالتهابات.\n-يتحكم مستخلص البورتولاكا oleracea المنقوع في إفراز الدهون ويشد المسام للحصول على بشرة أكثر دقة.","brand_id":115,"has_color":false,"has_size":true,"product_type":"size","price":"0.00","qty":0,"created_at":"2024-08-25T21:31:52.531Z","updated_at":"2024-09-16T10:04:52.967Z","categories":[{"id":169,"name":"Toners","main_category_id":1,"main_category_name":"Face Care"}],"colors":[],"sizes":[{"id":1805,"qty":12,"name":"150 ml","sold":0,"price":25000,"images":["1227.jpeg"],"color_id":null}],"images":["1227.jpeg"]}],"pagination":{"currentPage":1,"totalPages":4,"totalProducts":39,"limit":10}} [{"id":178,"name":"Body Cleansers","category_id":2,"category_name":"Body Care"},{"id":184,"name":"Body Exfoliating Solutions","category_id":2,"category_name":"Body Care"},{"id":179,"name":"Body Moisturizers","category_id":2,"category_name":"Body Care"},{"id":181,"name":"Body Sunscreens","category_id":2,"category_name":"Body Care"},{"id":213,"name":"Body mist","category_id":2,"category_name":"Body Care"},{"id":212,"name":"Body oil","category_id":2,"category_name":"Body Care"},{"id":269,"name":"Body razors","category_id":2,"category_name":"Body Care"},{"id":210,"name":"Body scrub","category_id":2,"category_name":"Body Care"},{"id":182,"name":"Deodorant","category_id":2,"category_name":"Body Care"},{"id":180,"name":"Fragrance","category_id":2,"category_name":"Body Care"},{"id":183,"name":"Tools","category_id":2,"category_name":"Body Care"},{"id":202,"name":"hair removal","category_id":2,"category_name":"Body Care"},{"id":159,"name":"Eye Cream","category_id":4,"category_name":"Eye Care"},{"id":160,"name":"Eye Masks","category_id":4,"category_name":"Eye Care"},{"id":196,"name":"Eye Patch","category_id":4,"category_name":"Eye Care"},{"id":157,"name":"Eye Serum","category_id":4,"category_name":"Eye Care"},{"id":161,"name":"Eye Treatments","category_id":4,"category_name":"Eye Care"},{"id":158,"name":"Lash Serum","category_id":4,"category_name":"Eye Care"},{"id":192,"name":"Cleansing balm","category_id":1,"category_name":"Face Care"},{"id":191,"name":"Cleansing oil","category_id":1,"category_name":"Face Care"},{"id":197,"name":"Essence","category_id":1,"category_name":"Face Care"},{"id":168,"name":"Face Cleansers","category_id":1,"category_name":"Face Care"},{"id":177,"name":"Face Exfoliating Acids","category_id":1,"category_name":"Face Care"},{"id":173,"name":"Face Sunscreens","category_id":1,"category_name":"Face Care"},{"id":172,"name":"Face moisturiser","category_id":1,"category_name":"Face Care"},{"id":174,"name":"Facial Oils","category_id":1,"category_name":"Face Care"},{"id":175,"name":"Masks","category_id":1,"category_name":"Face Care"},{"id":198,"name":"Micellar","category_id":1,"category_name":"Face Care"},{"id":193,"name":"Mist","category_id":1,"category_name":"Face Care"},{"id":195,"name":"Patch","category_id":1,"category_name":"Face Care"},{"id":194,"name":"Scrub","category_id":1,"category_name":"Face Care"},{"id":170,"name":"Serums","category_id":1,"category_name":"Face Care"},{"id":217,"name":"Spot","category_id":1,"category_name":"Face Care"},{"id":190,"name":"Toner pads","category_id":1,"category_name":"Face Care"},{"id":169,"name":"Toners","category_id":1,"category_name":"Face Care"},{"id":235,"name":"Tools","category_id":1,"category_name":"Face Care"},{"id":176,"name":"Treatments","category_id":1,"category_name":"Face Care"},{"id":209,"name":"Foot moisturizers","category_id":22,"category_name":"Foot care"},{"id":73,"name":"Acm","category_id":11,"category_name":"Global Brands"},{"id":95,"name":"Acure","category_id":11,"category_name":"Global Brands"},{"id":88,"name":"Advanced Clinicals","category_id":11,"category_name":"Global Brands"},{"id":91,"name":"Arm & Hammer","category_id":11,"category_name":"Global Brands"},{"id":86,"name":"Aveeno","category_id":11,"category_name":"Global Brands"},{"id":78,"name":"Avene","category_id":11,"category_name":"Global Brands"},{"id":96,"name":"Bath And Body","category_id":11,"category_name":"Global Brands"},{"id":76,"name":"Bioderma","category_id":11,"category_name":"Global Brands"},{"id":200,"name":"Bionnex","category_id":11,"category_name":"Global Brands"},{"id":273,"name":"Biore","category_id":11,"category_name":"Global Brands"},{"id":15,"name":"Briogio","category_id":11,"category_name":"Global Brands"},{"id":103,"name":"Cantu","category_id":11,"category_name":"Global Brands"},{"id":11,"name":"CeraVe","category_id":11,"category_name":"Global Brands"},{"id":12,"name":"Cetaphil","category_id":11,"category_name":"Global Brands"},{"id":228,"name":"Clarins","category_id":11,"category_name":"Global Brands"},{"id":64,"name":"Clinique","category_id":11,"category_name":"Global Brands"},{"id":215,"name":"Color wow","category_id":11,"category_name":"Global Brands"},{"id":246,"name":"Cotton","category_id":11,"category_name":"Global Brands"},{"id":225,"name":"Crest","category_id":11,"category_name":"Global Brands"},{"id":89,"name":"Dermedic","category_id":11,"category_name":"Global Brands"},{"id":240,"name":"Drunk Elephant","category_id":11,"category_name":"Global Brands"},{"id":274,"name":"Ecotools","category_id":11,"category_name":"Global Brands"},{"id":97,"name":"Embryolisse","category_id":11,"category_name":"Global Brands"},{"id":104,"name":"Eucerin","category_id":11,"category_name":"Global Brands"},{"id":206,"name":"Eveline","category_id":11,"category_name":"Global Brands"},{"id":265,"name":"Fable & Mane","category_id":11,"category_name":"Global Brands"},{"id":63,"name":"Farmacy","category_id":11,"category_name":"Global Brands"},{"id":102,"name":"Fino","category_id":11,"category_name":"Global Brands"},{"id":218,"name":"First aid beauty","category_id":11,"category_name":"Global Brands"},{"id":80,"name":"Floxia","category_id":11,"category_name":"Global Brands"},{"id":93,"name":"Foltene","category_id":11,"category_name":"Global Brands"},{"id":81,"name":"Garnier","category_id":11,"category_name":"Global Brands"},{"id":270,"name":"Gillette Venus","category_id":11,"category_name":"Global Brands"},{"id":13,"name":"Gisou","category_id":11,"category_name":"Global Brands"},{"id":14,"name":"Glow Recipe","category_id":11,"category_name":"Global Brands"},{"id":68,"name":"Grande Cosmetics","category_id":11,"category_name":"Global Brands"},{"id":94,"name":"Isdin","category_id":11,"category_name":"Global Brands"},{"id":74,"name":"Isis Pharma","category_id":11,"category_name":"Global Brands"},{"id":107,"name":"K18","category_id":11,"category_name":"Global Brands"},{"id":62,"name":"Kerastase","category_id":11,"category_name":"Global Brands"},{"id":70,"name":"La Roche","category_id":11,"category_name":"Global Brands"},{"id":263,"name":"Laneige","category_id":11,"category_name":"Global Brands"},{"id":222,"name":"Loreal Paris","category_id":11,"category_name":"Global Brands"},{"id":77,"name":"Mielle","category_id":11,"category_name":"Global Brands"},{"id":208,"name":"NIVEA","category_id":11,"category_name":"Global Brands"},{"id":65,"name":"Naturium","category_id":11,"category_name":"Global Brands"},{"id":87,"name":"Neutrogena","category_id":11,"category_name":"Global Brands"},{"id":72,"name":"Noreva","category_id":11,"category_name":"Global Brands"},{"id":106,"name":"Ogx","category_id":11,"category_name":"Global Brands"},{"id":105,"name":"Olaplex","category_id":11,"category_name":"Global Brands"},{"id":18,"name":"Ole Henriksen","category_id":11,"category_name":"Global Brands"},{"id":98,"name":"Ordinary","category_id":11,"category_name":"Global Brands"},{"id":60,"name":"Origins","category_id":11,"category_name":"Global Brands"},{"id":216,"name":"Oz naturals","category_id":11,"category_name":"Global Brands"},{"id":199,"name":"Palmers","category_id":11,"category_name":"Global Brands"},{"id":101,"name":"Panoxyl","category_id":11,"category_name":"Global Brands"},{"id":59,"name":"Paulas Choice","category_id":11,"category_name":"Global Brands"},{"id":84,"name":"Pfb","category_id":11,"category_name":"Global Brands"},{"id":17,"name":"Pixi","category_id":11,"category_name":"Global Brands"},{"id":261,"name":"Qv","category_id":11,"category_name":"Global Brands"},{"id":230,"name":"Revitalash","category_id":11,"category_name":"Global Brands"},{"id":61,"name":"Sephora","category_id":11,"category_name":"Global Brands"},{"id":82,"name":"Sesderma","category_id":11,"category_name":"Global Brands"},{"id":69,"name":"Shiseido","category_id":11,"category_name":"Global Brands"},{"id":204,"name":"Simple","category_id":11,"category_name":"Global Brands"},{"id":268,"name":"Softymo","category_id":11,"category_name":"Global Brands"},{"id":16,"name":"Sol De Janeiro","category_id":11,"category_name":"Global Brands"},{"id":67,"name":"Supergoop!","category_id":11,"category_name":"Global Brands"},{"id":75,"name":"Svr","category_id":11,"category_name":"Global Brands"},{"id":272,"name":"The Breath Co.","category_id":11,"category_name":"Global Brands"},{"id":233,"name":"The Purest Solutions","category_id":11,"category_name":"Global Brands"},{"id":203,"name":"The inkey list","category_id":11,"category_name":"Global Brands"},{"id":90,"name":"Tizo","category_id":11,"category_name":"Global Brands"},{"id":92,"name":"Tree Hut","category_id":11,"category_name":"Global Brands"},{"id":85,"name":"Uriage","category_id":11,"category_name":"Global Brands"},{"id":79,"name":"Vaseline","category_id":11,"category_name":"Global Brands"},{"id":83,"name":"Veet","category_id":11,"category_name":"Global Brands"},{"id":71,"name":"Vichy","category_id":11,"category_name":"Global Brands"},{"id":264,"name":"Wishful","category_id":11,"category_name":"Global Brands"},{"id":108,"name":"Wow Color","category_id":11,"category_name":"Global Brands"},{"id":66,"name":"Youth To The People","category_id":11,"category_name":"Global Brands"},{"id":207,"name":"garnier","category_id":11,"category_name":"Global Brands"},{"id":231,"name":"kiehl's","category_id":11,"category_name":"Global Brands"},{"id":211,"name":"‏ Victorias Secret","category_id":11,"category_name":"Global Brands"},{"id":221,"name":"‏Coco Monkey","category_id":11,"category_name":"Global Brands"},{"id":201,"name":"‏Galenia Skin Care","category_id":11,"category_name":"Global Brands"},{"id":223,"name":"‏Good Molecules","category_id":11,"category_name":"Global Brands"},{"id":248,"name":"‏Shea Moisture","category_id":11,"category_name":"Global Brands"},{"id":2,"name":"Conditioners","category_id":3,"category_name":"Hair Care"},{"id":3,"name":"Dry Shampoos","category_id":3,"category_name":"Hair Care"},{"id":7,"name":"Hair Brush","category_id":3,"category_name":"Hair Care"},{"id":6,"name":"Hair Mist","category_id":3,"category_name":"Hair Care"},{"id":4,"name":"Hair Oils","category_id":3,"category_name":"Hair Care"},{"id":8,"name":"Hair Styling","category_id":3,"category_name":"Hair Care"},{"id":9,"name":"Hair Treatments","category_id":3,"category_name":"Hair Care"},{"id":220,"name":"Heat protectant","category_id":3,"category_name":"Hair Care"},{"id":219,"name":"Leave-In","category_id":3,"category_name":"Hair Care"},{"id":5,"name":"Masks","category_id":3,"category_name":"Hair Care"},{"id":227,"name":"Serum","category_id":3,"category_name":"Hair Care"},{"id":1,"name":"Shampoos","category_id":3,"category_name":"Hair Care"},{"id":236,"name":"Tools","category_id":3,"category_name":"Hair Care"},{"id":163,"name":"Hand moisturiser","category_id":6,"category_name":"Hand Care"},{"id":165,"name":"Nail Polish","category_id":6,"category_name":"Hand Care"},{"id":166,"name":"Nail Polish Remover","category_id":6,"category_name":"Hand Care"},{"id":167,"name":"Nail Treatments","category_id":6,"category_name":"Hand Care"},{"id":109,"name":"Face Kit","category_id":9,"category_name":"Kit"},{"id":141,"name":"Abib","category_id":10,"category_name":"Korean Brands"},{"id":117,"name":"Anua","category_id":10,"category_name":"Korean Brands"},{"id":260,"name":"Aromatica","category_id":10,"category_name":"Korean Brands"},{"id":116,"name":"Axis Y","category_id":10,"category_name":"Korean Brands"},{"id":115,"name":"Beauty Of Joseon","category_id":10,"category_name":"Korean Brands"},{"id":257,"name":"Cos De Baha","category_id":10,"category_name":"Korean Brands"},{"id":119,"name":"Cosrx","category_id":10,"category_name":"Korean Brands"},{"id":126,"name":"Coxir","category_id":10,"category_name":"Korean Brands"},{"id":129,"name":"Dr.Althea","category_id":10,"category_name":"Korean Brands"},{"id":137,"name":"Dr.Jart+","category_id":10,"category_name":"Korean Brands"},{"id":241,"name":"Dr.g","category_id":10,"category_name":"Korean Brands"},{"id":125,"name":"Elizavecca","category_id":10,"category_name":"Korean Brands"},{"id":120,"name":"Etude House","category_id":10,"category_name":"Korean Brands"},{"id":139,"name":"Goodal","category_id":10,"category_name":"Korean Brands"},{"id":127,"name":"Haru Haru","category_id":10,"category_name":"Korean Brands"},{"id":122,"name":"Im From","category_id":10,"category_name":"Korean Brands"},{"id":142,"name":"Innisfree","category_id":10,"category_name":"Korean Brands"},{"id":118,"name":"Isntree","category_id":10,"category_name":"Korean Brands"},{"id":262,"name":"Iunik","category_id":10,"category_name":"Korean Brands"},{"id":130,"name":"Jumiso","category_id":10,"category_name":"Korean Brands"},{"id":245,"name":"Kaine","category_id":10,"category_name":"Korean Brands"},{"id":123,"name":"Laneige","category_id":10,"category_name":"Korean Brands"},{"id":134,"name":"Manyo","category_id":10,"category_name":"Korean Brands"},{"id":244,"name":"Missha","category_id":10,"category_name":"Korean Brands"},{"id":138,"name":"Mixsoon","category_id":10,"category_name":"Korean Brands"},{"id":143,"name":"Neogen","category_id":10,"category_name":"Korean Brands"},{"id":133,"name":"Nine Less","category_id":10,"category_name":"Korean Brands"},{"id":132,"name":"Numbuzin","category_id":10,"category_name":"Korean Brands"},{"id":205,"name":"Purito","category_id":10,"category_name":"Korean Brands"},{"id":239,"name":"Purito Seoul","category_id":10,"category_name":"Korean Brands"},{"id":121,"name":"Round Lab","category_id":10,"category_name":"Korean Brands"},{"id":267,"name":"Sccret key","category_id":10,"category_name":"Korean Brands"},{"id":247,"name":"Skin food","category_id":10,"category_name":"Korean Brands"},{"id":128,"name":"Skin1004","category_id":10,"category_name":"Korean Brands"},{"id":136,"name":"Snow White","category_id":10,"category_name":"Korean Brands"},{"id":131,"name":"Some By Mi","category_id":10,"category_name":"Korean Brands"},{"id":251,"name":"Tiam","category_id":10,"category_name":"Korean Brands"},{"id":140,"name":"Tirtir","category_id":10,"category_name":"Korean Brands"},{"id":124,"name":"Tocobo","category_id":10,"category_name":"Korean Brands"},{"id":135,"name":"Torriden","category_id":10,"category_name":"Korean Brands"},{"id":153,"name":"Lip Balm","category_id":5,"category_name":"Lip Care"},{"id":155,"name":"Lip Exfoliating","category_id":5,"category_name":"Lip Care"},{"id":156,"name":"Lip Mask","category_id":5,"category_name":"Lip Care"},{"id":154,"name":"Lip Oil","category_id":5,"category_name":"Lip Care"},{"id":148,"name":"Cheeks","category_id":7,"category_name":"Makeup"},{"id":145,"name":"Eyebrows","category_id":7,"category_name":"Makeup"},{"id":146,"name":"Eyes","category_id":7,"category_name":"Makeup"},{"id":144,"name":"Face","category_id":7,"category_name":"Makeup"},{"id":147,"name":"Lips","category_id":7,"category_name":"Makeup"},{"id":149,"name":"Makeup Brush","category_id":7,"category_name":"Makeup"},{"id":271,"name":"Mouth freshener","category_id":23,"category_name":"Mouth Care"},{"id":226,"name":"Teeth","category_id":23,"category_name":"Mouth Care"},{"id":151,"name":"Body Mist","category_id":8,"category_name":"Perfume"},{"id":152,"name":"Candles","category_id":8,"category_name":"Perfume"},{"id":150,"name":"Hair Mist","category_id":8,"category_name":"Perfume"}]

  2. Assistant

    The error you're encountering:

    error: current transaction is aborted, commands ignored until end of transaction block
    code: '25P02'
    

    indicates that an earlier operation within the same transaction has failed, causing PostgreSQL to mark the entire transaction as aborted. As a result, any subsequent commands within that transaction are ignored until you explicitly roll back the transaction.

    Let's analyze your updateProduct function and the provided product data to identify potential issues and solutions.

    Understanding the Root Cause

    1. Initial Error in Transaction:

      • The error message suggests that an earlier operation in the transaction failed, leading to the abortion of the entire transaction. Subsequent attempts to execute further queries within the same transaction result in the 25P02 error.
    2. Potential Culprit:

      • Price Adjustment Logic:
        if (productData.price < 1000) {
            productData.price *= 1000;
        }
        
        • For your problematic product (e.g., id: 904), the price is "0.00". After the adjustment, it becomes 0. If your database schema has constraints (e.g., price > 0), this operation would fail, causing the transaction to abort.
    3. Error Propagation:

      • Once the transaction is marked as aborted due to an error (e.g., violating a constraint by setting price to 0), any subsequent queries (like inserting into product_categories) will fail with the 25P02 error, as observed.

    Steps to Resolve the Issue

    1. Identify the Initial Error:

      • Enhanced Logging:
        • Modify your catch block to log more detailed information about the error. This will help you pinpoint the exact operation causing the failure.
        } catch (error) {
            await query('ROLLBACK');
            console.error('Error updating product:', error.message, error.stack);
            res.status(500).json({ error: 'Internal server error' });
        }
        
      • Check Application Logs:
        • Review your application's logs to identify the first error that occurs within the transaction. This is crucial for understanding why the transaction is failing.
    2. Validate and Sanitize Input Data:

      • Price Validation:
        • Ensure that the price is a valid number and meets any business logic or database constraints before performing operations.
        • Example Validation:
          const price = parseFloat(productData.price);
          if (isNaN(price) || price < 0) {
              throw new Error('Invalid price value');
          }
          if (price < 1000) {
              productData.price = price * 1000;
          } else {
              productData.price = price;
          }
          
      • Prevent Setting Price to Zero (If Not Allowed):
        • If price should never be zero, add a validation step to prevent this.
          if (productData.price === 0) {
              throw new Error('Price cannot be zero');
          }
          
    3. Review Database Constraints:

      • Check products Table Constraints:
        • Verify if there are any constraints on the price column, such as CHECK (price > 0).
        • Adjust Constraints or Business Logic Accordingly:
          • If setting price to zero is valid, adjust the database constraints.
          • Otherwise, ensure that your application logic prevents this scenario.
    4. Transaction Management Best Practices:

      • Use a Dedicated Client for Transactions:
        • Ensure that all queries within a transaction use the same client instance. This prevents issues where transaction commands are sent to different connections.
        • Example Using pg Client:
          const { Client } = require('pg');
          
          export const updateProduct = async (req: Request, res: Response) => {
              const { id } = req.params;
              const productData = req.body;
              const client = new Client(/* your config */);
          
              try {
                  await client.connect();
                  await client.query('BEGIN');
          
                  // Your update logic using `client.query` instead of a global `query` function.
          
                  await client.query('COMMIT');
                  res.json({ message: 'Product updated successfully', product: result.rows[0] });
              } catch (error) {
                  await client.query('ROLLBACK');
                  console.error('Error updating product:', error.message, error.stack);
                  res.status(500).json({ error: 'Internal server error' });
              } finally {
                  await client.end();
              }
          };
          
    5. Handle Specific Errors Gracefully:

      • Use Specific Error Handling:
        • Instead of a generic catch-all, handle known errors (like constraint violations) specifically to provide more meaningful feedback.
          } catch (error) {
              await client.query('ROLLBACK');
              if (error.code === '23505') { // Unique violation
                  res.status(400).json({ error: 'Duplicate entry detected' });
              } else if (error.code === '23514') { // Check violation
                  res.status(400).json({ error: 'Check constraint violated' });
              } else {
                  console.error('Error updating product:', error.message, error.stack);
                  res.status(500).json({ error: 'Internal server error' });
              }
          }
          
    6. Review Related Data Integrity:

      • Ensure Foreign Keys Are Valid:
        • Make sure that category_id, color_id, and size_id values exist in their respective tables to avoid foreign key violations.
      • Example Check Before Insert:
        const categoryExists = await client.query('SELECT 1 FROM categories WHERE id = $1', [categoryId]);
        if (categoryExists.rowCount === 0) {
            throw new Error(`Category ID ${categoryId} does not exist`);
        }
        

    Conclusion

    The issue stems from an error within the transaction, likely caused by attempting to set a price to an invalid value (e.g., 0). To resolve this:

    1. Identify the Initial Error: Enhance your logging to capture detailed error information.
    2. Validate Input Data: Ensure that all input data meets the required constraints before performing database operations.
    3. Review and Adjust Database Constraints: Ensure that your database schema aligns with your application’s business logic.
    4. Implement Robust Transaction Management: Use dedicated database clients for transactions and handle errors gracefully.

    By following these steps, you can pinpoint and rectify the underlying cause of the transaction failure, ensuring that only valid and consistent data is processed and stored in your database.

    If you need further assistance or encounter additional errors, feel free to provide more detailed logs or information about your database schema.