Проблема с совместной работой node js и серверной базы данных postgresql
у меня есть программа, встроенная в node js. Цель этой программы - получить информацию из pos-системы кафе через api и отфильтровать ее по нужным параметрам и записать в бд. Затем веб-страница должна вывести отложенные и выполненные ордера. Когда я запускаю эту программу со своего локального компьютера и подключаюсь к базе данных postgresql на своем компьютере, программа работает отлично, но когда я загружаю ее на железнодорожный сервер, то она иногда очень поздно отображает подготовленные и готовые заказы на веб-страницу, а иногда и очень поздно. это даже не показывает. Я впервые создаю такую программу на nodejs. Пожалуйста, просмотрите код и помогите мне исправить его, если есть какие-то неправильные части. Я думаю, что код в целом правильный, но есть небольшие проблемы в коде, который добавляет, удаляет, обновляет новые данные в базе данных postgresql и сортирует заказы на веб-странице, пожалуйста, помогите мне это исправить.
Мой код:
const express = require('express');
const http = require('http');
const axios = require('axios');
const { Pool } = require('pg');
const { DateTime } = require('luxon');
const { env } = require('process');
const cors = require('cors');
const app = express();
const port = 3000;
app.use(express.static('public'));
// Add cors middleware here
app.use(cors());
// const pool = new Pool({
// user: 'postgres',
// host: 'localhost', // Typically 'localhost' or '127.0.0.1' for local development
// database: 'db',
// password: 'password',
// port: 5432,
// });
const pool = new Pool({
user: 'user here',
host: 'host here',
database: 'database here',
password: 'password here',
// port: 26243,
});
const apiEndpoint = 'https://joinposter.com/api/dash.getTransactions';
const accessToken = "token_here";
const waitingMinutes = 3;
async function createTableWithRetry() {
const maxRetries = 3; // Set the maximum number of retries
let retryCount = 0;
while (retryCount < maxRetries) {
const client = await pool.connect();
try {
await client.query(`
CREATE TABLE IF NOT EXISTS orders (
id BIGSERIAL PRIMARY KEY,
transaction_id VARCHAR(355) UNIQUE,
product_id_count INT,
finishedcooking_count INT,
is_printed BOOLEAN DEFAULT FALSE,
finished_at TIMESTAMP DEFAULT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
`);
// If the operation is successful, break out of the loop
break;
} catch (error) {
// Handle the error gracefully
if (error.code === 'ENOTFOUND' || error.code === 'EAI_AGAIN') {
console.error(`Error: Could not resolve hostname. Check your PostgreSQL host configuration.`);
} else {
console.error(`Error creating table. Attempt ${retryCount + 1}/${maxRetries}:`, error.message);
// Retry the operation after a delay
await new Promise(resolve => setTimeout(resolve, 1000)); // Add a delay (1 second in this example)
}
// Increment the retry count
retryCount++;
} finally {
client.release();
}
}
if (retryCount === maxRetries) {
console.error(`Maximum number of retries reached. Operation failed.`);
}
}
async function fetchOrders() {
const client = await pool.connect();
try {
const result = await client.query(`
SELECT * FROM orders
WHERE NOW() - created_at <= INTERVAL '80 minutes'
AND NOW() - created_at >= INTERVAL '0 minutes'
ORDER BY transaction_id DESC;
`);
return result.rows;
} finally {
client.release();
}
}
async function fetchData() {
try {
const today = DateTime.now().toFormat('yyyyMMdd');
const url = `${apiEndpoint}?token=${accessToken}&dateFrom=${today}&dateTo=${today}&include_history=true`;
const response = await axios.get(url, { timeout: 20000 });
const data = response.data;
return data;
} catch (error) {
console.error('Error fetching data:', error);
return null;
}
}
async function insertOrUpdateData(data) {
const client = await pool.connect();
try {
await client.query('BEGIN');
for (const transaction of data.response) {
const transactionId = transaction.transaction_id;
const productIdCount = transaction.history
.filter((history) => history.type_history === 'sendtokitchen')
.flatMap((history) => history.value_text)
.filter((entry) => 'product_id' in entry)
.length;
const finishedcookingCount = transaction.history
.filter((history) => history.type_history === 'finishedcooking')
.length;
await client.query(
`
INSERT INTO orders (transaction_id, product_id_count, finishedcooking_count)
VALUES ($1, $2, $3)
ON CONFLICT (transaction_id) DO UPDATE
SET product_id_count = $2, finishedcooking_count = $3;
`,
[transactionId, productIdCount, finishedcookingCount]
);
}
await client.query('COMMIT');
} catch (error) {
await client.query('ROLLBACK');
console.error('Error inserting/updating data:', error);
} finally {
client.release();
}
}
async function markOrdersPrinted() {
const client = await pool.connect();
try {
await client.query('BEGIN');
await client.query(`
UPDATE orders
SET is_printed = TRUE,
finished_at = CASE
WHEN finished_at IS NULL THEN NOW()
ELSE finished_at
END
WHERE product_id_count = finishedcooking_count
AND product_id_count != 0;
`);
await client.query('COMMIT');
} catch (error) {
await client.query('ROLLBACK');
console.error('Error marking orders as printed:', error);
} finally {
client.release();
}
}
async function deleteOldFinishedOrders() {
const client = await pool.connect();
try {
const currentTime = DateTime.now();
if (currentTime.hour === 4 && currentTime.minute === 10) {
await client.query('DROP TABLE IF EXISTS orders;');
console.log("Table 'orders' dropped at 4:10 AM");
await new Promise(resolve => setTimeout(resolve, 5000));
await client.query(`
CREATE TABLE IF NOT EXISTS orders (
id SERIAL PRIMARY KEY,
transaction_id VARCHAR(255) UNIQUE,
product_id_count INT,
finishedcooking_count INT,
is_printed BOOLEAN DEFAULT FALSE,
finished_at TIMESTAMP DEFAULT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
`);
console.log("Table 'orders' recreated.");
}
await new Promise((resolve) => setTimeout(resolve, 1000));
} catch (error) {
console.error('Error deleting old finished orders:', error);
} finally {
client.release();
}
}
async function main() {
while (true) {
const apiData = await fetchData();
if (apiData) {
await insertOrUpdateData(apiData);
await new Promise((resolve) => setTimeout(resolve, 1000));
await markOrdersPrinted();
await new Promise((resolve) => setTimeout(resolve, 1000));
await deleteOldFinishedOrders();
await new Promise((resolve) => setTimeout(resolve, 1000));
}
await new Promise((resolve) => setTimeout(resolve, 5000));
}
}
app.get('/', async (req, res) => {
const orders = await fetchOrders();
const preparingOrders = orders.filter(
(order) => order.product_id_count > 0 && order.finishedcooking_count < order.product_id_count
);
const readyOrders = orders.filter(
(order) =>
order.product_id_count === order.finishedcooking_count &&
order.product_id_count !== 0 &&
DateTime.now().diff(DateTime.fromJSDate(order.finished_at), 'minutes').minutes < parseInt(waitingMinutes)
);
app.get('/fetch_data', async (req, res) => {
const orders = await fetchOrders(); // Implement fetchOrders function
res.json({
preparingOrders: orders.filter(order => order.product_id_count > 0 && order.finishedcooking_count < order.product_id_count),
readyOrders: orders.filter(order => order.product_id_count === order.finishedcooking_count &&
order.product_id_count !== 0 &&
DateTime.now().diff(DateTime.fromJSDate(order.finished_at), 'minutes').minutes < parseInt(waitingMinutes))
});
});
const htmlResponse = `
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Order Status</title>
<link rel="icon" href="public/awlogo.png" type="image/png">
<style>
body {
margin: 0;
font-family: Arial, sans-serif;
}
.container {
display: flex;
margin: 0 auto; /* markazga joylashgan */
padding: 0px; /* tepa, chap, past va o'ng yonida 20 px ichki padding */
box-sizing: border-box; /* padding va borderni ichki qismini qo'shadi */
}
.yellow-side {
flex: 1;
background-color: yellow;
padding: 0px;
height: 1000vh;
}
.green-side {
flex: 1;
background-color: green;
padding: 0px;
height: 1000vh;
}
ul {
list-style-type: none;
padding: 0;
display: flex;
flex-wrap: wrap;
justify-content: flex-start;
margin-left: 5px;
margin-right: -44px;
}
li {
background-color: rgba(128, 128, 128, 0.5);
margin: 7px;
padding: 0px;
padding-right: 1px;
padding-left: 1px;
border-radius: 99px;
flex: 1 0 200px;
max-width: calc(12%);
text-align: center;
font-size: 45px;
font-weight: bold;
line-height: 0px;
}
.bolim{
text-align: center;
font-size: 28px;
}
.tana {
width: 1920;
height: 1080;
}
</style>
</head>
<body class="tana">
<div class="container">
<div class="yellow-side">
<h1 class="bolim">ГОТОВИТСЯ</h1>
<ul id="preparingOrdersList">
${preparingOrders.map((order) => `<li><p>${order.transaction_id.slice(-2)}</p></li>`).join('')}
</ul>
</div>
<div class="green-side">
<h1 class="bolim">ГОТОВЫЙ</h1>
<ul id="readyOrdersList">
${readyOrders.map((order) => `<li><p>${order.transaction_id.slice(-2)}</p></li>`).join('')}
</ul>
</div>
</div>
<script>
async function fetchDataAndUpdate() {
try {
const response = await fetch('/fetch_data');
if (response.ok) {
const data = await response.json();
updateOrdersList(data.preparingOrders, 'preparingOrdersList');
updateOrdersList(data.readyOrders, 'readyOrdersList');
}
} catch (error) {
console.error('Error fetching data:', error);
}
}
function updateOrdersList(orders, listId) {
const list = document.getElementById(listId);
list.innerHTML = orders.map(order => \`<li><h3>\${order.transaction_id.slice(-2)}</h3></li>\`).join('');
}
setInterval(fetchDataAndUpdate, 8000);
</script>
</body>
</html>
`;
res.send(htmlResponse);
});
app.listen(port, () => {
console.log(`Server is running on port ${port}`);
});
(async () => {
await createTableWithRetry();
await main();
})();
Вот некоторые ошибки, возникшие в ходе тестирования после загрузки приложения на железнодорожный сервер:
2024-02-19 21:20:50.698 UTC [8] LOG: listening on IPv4 address "0.0.0.0", port 5432
2024-02-19 21:20:50.698 UTC [8] LOG: listening on IPv6 address "::", port 5432
2024-02-19 21:20:50.712 UTC [8] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-02-19 21:20:50.718 UTC [78] LOG: database system was shut down at 2024-02-19 21:20:50 UTC
2024-02-19 21:20:50.724 UTC [8] LOG: database system is ready to accept connections
2024-02-19 21:25:50.815 UTC [76] LOG: checkpoint starting: time
2024-02-19 21:25:55.047 UTC [76] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.221 s, sync=0.005 s, total=4.233 s; sync files=12, longest=0.003 s, average=0.001 s; distance=261 kB, estimate=261 kB; lsn=0/1954508, redo lsn=0/19544D0
2024-02-19 21:29:29.138 UTC [105] ERROR: deadlock detected
2024-02-19 21:29:29.138 UTC [105] DETAIL: Process 105 waits for ShareLock on transaction 757; blocked by process 119.
Process 119 waits for ShareLock on transaction 755; blocked by process 105.
Process 105:
INSERT INTO orders (transaction_id, product_id_count, finishedcooking_count)
VALUES ($1, $2, $3)
ON CONFLICT (transaction_id) DO UPDATE
Это ошибка в журнале базы данных postgresql в railway:
2024-02-19 21:35:50.983 UTC [76] LOG: checkpoint starting: time
2024-02-19 21:35:52.503 UTC [76] LOG: checkpoint complete: wrote 16 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=1.506 s, sync=0.003 s, total=1.521 s; sync files=10, longest=0.003 s, average=0.001 s; distance=508 kB, estimate=508 kB; lsn=0/1A334A8, redo lsn=0/1A33470
2024-02-19 21:40:50.603 UTC [76] LOG: checkpoint starting: time
2024-02-19 21:40:51.717 UTC [76] LOG: checkpoint complete: wrote 12 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=1.105 s, sync=0.004 s, total=1.115 s; sync files=7, longest=0.003 s, average=0.001 s; distance=244 kB, estimate=482 kB; lsn=0/1A70838, redo lsn=0/1A707F8
2024-02-19 21:45:50.817 UTC [76] LOG: checkpoint starting: time
2024-02-19 21:45:51.731 UTC [76] LOG: checkpoint complete: wrote 10 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.904 s, sync=0.003 s, total=0.914 s; sync files=6, longest=0.003 s, average=0.001 s; distance=25 kB, estimate=436 kB; lsn=0/1A76DE0, redo lsn=0/1A76D98
2024-02-19 21:55:16.358 UTC [244] LOG: unexpected EOF on client connection with an open transaction
2024-02-19 21:55:16.358 UTC [119] LOG: unexpected EOF on client connection with an open transaction
2024-02-19 21:55:16.368 UTC [302] LOG: unexpected EOF on client connection with an open transaction
Когда программа работает правильно, она должна отображаться вот так ?, но иногда она вообще не отображается.
Я думал, что причина сбоя программы в времени функций, работающих с базой данных postgresql, и всячески пытался изменить их время, но не получилось.
Я думаю, проблема в этой части кода:
async function main() {
while (true) {
const apiData = await fetchData();
if (apiData) {
await insertOrUpdateData(apiData);
await new Promise((resolve) => setTimeout(resolve, 1000));
await markOrdersPrinted();
await new Promise((resolve) => setTimeout(resolve, 1000));
await deleteOldFinishedOrders();
await new Promise((resolve) => setTimeout(resolve, 1000));
}
await new Promise((resolve) => setTimeout(resolve, 5000));
}
}