search

ildus / clickhouse_fdw

ClickHouse FDW for PostgreSQL
Apache License 2.0
260 stars 55 forks source link

Can't run nested query #99

Open roster79 opened 1 year ago

roster79 commented 1 year ago

System: Debian11 + Postgresql13.11; Calculate Linux + Clickhouse 23.3.1.2823 (official build) clickhouse_fdw linked in text mode (numeric type)

Clickhouse: CREATE TABLE geo.route3 ( id UInt64, ship_data_id Int64, pointdata String, speed Decimal(9, 6), course Decimal(9, 6), pointdate DateTime ) ENGINE = MergeTree PARTITION BY toYYYYMMDD(pointdate) ORDER BY (ship_data_id, pointdate, id) SETTINGS index_granularity = 8192 This bale has 3449048500 rows;

Query: SELECT id, pointdate FROM v3ch3_geo.route3 WHERE id = (SELECT max(id) FROM v3ch3_geo.route3) - it worked in clickhouse, but I have error in Postgresql (bellow). But query SELECT id, pointdate FROM v3ch3_geo.route3 ORDER BY id DESC LIMIT 1 works fine!

Error in Postgresql(debug+): 2023-05-16 09:59:04.790 UTC [26100] LOG: server process (PID 26164) was terminated by signal 11: Segmentation fault 2023-05-16 09:59:04.790 UTC [26100] DETAIL: Failed process was running: SELECT id, pointdate FROM v3ch3_geo.route3 WHERE id = (SELECT max(id) FROM v3ch3_geo.route3); 2023-05-16 09:59:04.790 UTC [26100] LOG: terminating any other active server processes 2023-05-16 09:59:04.790 UTC [26134] WARNING: terminating connection because of crash of another server process 2023-05-16 09:59:04.790 UTC [26134] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2023-05-16 09:59:04.790 UTC [26134] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2023-05-16 09:59:04.811 UTC [26186] v3u@v3 FATAL: the database system is in recovery mode 2023-05-16 09:59:04.816 UTC [26100] LOG: all server processes terminated; reinitializing 2023-05-16 09:59:05.074 UTC [26187] LOG: database system was interrupted; last known up at 2023-05-16 09:56:47 UTC 2023-05-16 09:59:05.399 UTC [26187] LOG: database system was not properly shut down; automatic recovery in progress 2023-05-16 09:59:05.404 UTC [26187] LOG: redo starts at 610/F728C2F8 2023-05-16 09:59:05.404 UTC [26187] LOG: invalid record length at 610/F728C330: wanted 24, got 0 2023-05-16 09:59:05.404 UTC [26187] LOG: redo done at 610/F728C2F8 2023-05-16 09:59:05.446 UTC [26100] LOG: database system is ready to accept connections

Error in Clickhouse(debug): 2023.05.16 09:59:04.791872 [ 5760 ] {a2f9afda-e072-4253-b4c6-b7c494cc43d1} executeQuery: Code: 24. DB::Exception: Cannot write to ostream at offset 5157726336: While executing ParallelFormattingOutputFormat. (CANNOT_WRITE_TO_OSTREAM) (version 23.3.1.2823 (official build)) (from 10.40.4.30:39816) (in query: SELECT id, pointdate FROM geo.route3), Stack trace (when copying this message, always include the lines below):

  1. ./build_docker/./src/Common/Exception.cpp:91: DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0xe18d895 in /usr/lib/debug/usr/bin/clickhouse.debug
  2. DB::Exception::Exception(int, FormatStringHelperImpl<std::type_identity::type>, unsigned long&&) @ 0x9ef2cd0 in /usr/lib/debug/usr/bin/clickhouse.debug
  3. ./build_docker/./src/IO/WriteBufferFromOStream.cpp:21: DB::WriteBufferFromOStream::nextImpl() @ 0xe2d058a in /usr/lib/debug/usr/bin/clickhouse.debug
  4. ./build_docker/./src/IO/BufferBase.h:39: DB::WriteBufferFromHTTPServerResponse::nextImpl() @ 0x1490c754 in /usr/lib/debug/usr/bin/clickhouse.debug
  5. DB::WriteBuffer::write(char const*, unsigned long) @ 0x89c412b in /usr/lib/debug/usr/bin/clickhouse.debug
  6. ./build_docker/./contrib/llvm-project/libcxx/include/atomic:969: DB::ParallelFormattingOutputFormat::collectorThreadFunction(std::shared_ptr const&) @ 0x14a58f67 in /usr/lib/debug/usr/bin/clickhouse.debug
  7. ./build_docker/./src/Processors/Formats/Impl/ParallelFormattingOutputFormat.h:0: void std::function::policy_invoker<void ()>::__call_impl<std::function::default_alloc_func<ThreadFromGlobalPoolImpl::ThreadFromGlobalPoolImpl<DB::ParallelFormattingOutputFormat::ParallelFormattingOutputFormat(DB::ParallelFormattingOutputFormat::Params)::'lambda'()>(DB::ParallelFormattingOutputFormat::ParallelFormattingOutputFormat(DB::ParallelFormattingOutputFormat::Params)::'lambda'()&&)::'lambda'(), void ()>>(std::function::policy_storage const*) @ 0x14918bf5 in /usr/lib/debug/usr/bin/clickhouse.debug
  8. ./build_docker/./base/base/../base/wide_integer_impl.h:789: ThreadPoolImpl::worker(std::__list_iterator<std::thread, void*>) @ 0xe25cc73 in /usr/lib/debug/usr/bin/clickhouse.debug
  9. ./build_docker/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:302: void std::thread_proxy[abi:v15000]<std::tuple<std::unique_ptr<std::thread_struct, std::default_delete>, void ThreadPoolImpl::scheduleImpl(std::function<void ()>, long, std::optional, bool)::'lambda0'()>>(void) @ 0xe2628e1 in /usr/lib/debug/usr/bin/clickhouse.debug
  10. ? @ 0x84fe4 in /lib64/libc.so.6
  11. ? @ 0x1057bc in /lib64/libc.so.6

2023.05.16 09:59:04.798323 [ 5760 ] {a2f9afda-e072-4253-b4c6-b7c494cc43d1} DynamicQueryHandler: Code: 24. DB::Exception: Cannot write to ostream at offset 5157726336: While executing ParallelFormattingOutputFormat. (CANNOT_WRITE_TO_OSTREAM), Stack trace (when copying this message, always include the lines below):

  1. ./build_docker/./src/Common/Exception.cpp:91: DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0xe18d895 in /usr/lib/debug/usr/bin/clickhouse.debug

  2. DB::Exception::Exception(int, FormatStringHelperImpl<std::type_identity::type>, unsigned long&&) @ 0x9ef2cd0 in /usr/lib/debug/usr/bin/clickhouse.debug

  3. ./build_docker/./src/IO/WriteBufferFromOStream.cpp:21: DB::WriteBufferFromOStream::nextImpl() @ 0xe2d058a in /usr/lib/debug/usr/bin/clickhouse.debug

  4. ./build_docker/./src/IO/BufferBase.h:39: DB::WriteBufferFromHTTPServerResponse::nextImpl() @ 0x1490c754 in /usr/lib/debug/usr/bin/clickhouse.debug

  5. DB::WriteBuffer::write(char const*, unsigned long) @ 0x89c412b in /usr/lib/debug/usr/bin/clickhouse.debug

  6. ./build_docker/./contrib/llvm-project/libcxx/include/atomic:969: DB::ParallelFormattingOutputFormat::collectorThreadFunction(std::shared_ptr const&) @ 0x14a58f67 in /usr/lib/debug/usr/bin/clickhouse.debug

  7. ./build_docker/./src/Processors/Formats/Impl/ParallelFormattingOutputFormat.h:0: void std::function::policy_invoker<void ()>::__call_impl<std::function::default_alloc_func<ThreadFromGlobalPoolImpl::ThreadFromGlobalPoolImpl<DB::ParallelFormattingOutputFormat::ParallelFormattingOutputFormat(DB::ParallelFormattingOutputFormat::Params)::'lambda'()>(DB::ParallelFormattingOutputFormat::ParallelFormattingOutputFormat(DB::ParallelFormattingOutputFormat::Params)::'lambda'()&&)::'lambda'(), void ()>>(std::function::policy_storage const*) @ 0x14918bf5 in /usr/lib/debug/usr/bin/clickhouse.debug

  8. ./build_docker/./base/base/../base/wide_integer_impl.h:789: ThreadPoolImpl::worker(std::__list_iterator<std::thread, void*>) @ 0xe25cc73 in /usr/lib/debug/usr/bin/clickhouse.debug

  9. ./build_docker/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:302: void std::thread_proxy[abi:v15000]<std::tuple<std::unique_ptr<std::thread_struct, std::default_delete>, void ThreadPoolImpl::scheduleImpl(std::function<void ()>, long, std::optional, bool)::'lambda0'()>>(void) @ 0xe2628e1 in /usr/lib/debug/usr/bin/clickhouse.debug

  10. ? @ 0x84fe4 in /lib64/libc.so.6

  11. ? @ 0x1057bc in /lib64/libc.so.6 (version 23.3.1.2823 (official build)) 2023.05.16 09:59:04.798456 [ 5760 ] {a2f9afda-e072-4253-b4c6-b7c494cc43d1} DynamicQueryHandler: Cannot send exception to client: Code: 24. DB::Exception: Cannot write to ostream at offset 5156678128. (CANNOT_WRITE_TO_OSTREAM), Stack trace (when copying this message, always include the lines below):

  12. ./build_docker/./src/Common/Exception.cpp:91: DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0xe18d895 in /usr/lib/debug/usr/bin/clickhouse.debug

  13. DB::Exception::Exception(int, FormatStringHelperImpl<std::type_identity::type>, unsigned long&&) @ 0x9ef2cd0 in /usr/lib/debug/usr/bin/clickhouse.debug

  14. ./build_docker/./src/IO/WriteBufferFromOStream.cpp:21: DB::WriteBufferFromOStream::nextImpl() @ 0xe2d058a in /usr/lib/debug/usr/bin/clickhouse.debug

  15. ./build_docker/./src/IO/BufferBase.h:39: DB::WriteBufferFromHTTPServerResponse::nextImpl() @ 0x1490c754 in /usr/lib/debug/usr/bin/clickhouse.debug

  16. ./build_docker/./src/IO/BufferBase.h:39: DB::HTTPHandler::trySendExceptionToClient(String const&, int, DB::HTTPServerRequest&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&) @ 0x14895e77 in /usr/lib/debug/usr/bin/clickhouse.debug

  17. ./build_docker/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:290: DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&) @ 0x14898788 in /usr/lib/debug/usr/bin/clickhouse.debug

  18. ./build_docker/./base/poco/Foundation/include/Poco/AutoPtr.h:205: DB::HTTPServerConnection::run() @ 0x1490617d in /usr/lib/debug/usr/bin/clickhouse.debug

  19. ./build_docker/./base/poco/Net/src/TCPServerConnection.cpp:57: Poco::Net::TCPServerConnection::start() @ 0x17838054 in /usr/lib/debug/usr/bin/clickhouse.debug

  20. ./build_docker/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:48: Poco::Net::TCPServerDispatcher::run() @ 0x1783927b in /usr/lib/debug/usr/bin/clickhouse.debug

  21. ./build_docker/./base/poco/Foundation/src/ThreadPool.cpp:202: Poco::PooledThread::run() @ 0x179c0b07 in /usr/lib/debug/usr/bin/clickhouse.debug

  22. ./build_docker/./base/poco/Foundation/include/Poco/SharedPtr.h:231: Poco::ThreadImpl::runnableEntry(void*) @ 0x179be53d in /usr/lib/debug/usr/bin/clickhouse.debug

  23. ? @ 0x84fe4 in /lib64/libc.so.6

  24. ? @ 0x1057bc in /lib64/libc.so.6 (version 23.3.1.2823 (official build)) 2023.05.16 09:59:04.798786 [ 5760 ] {} ServerErrorHandler: Poco::Exception. Code: 1000, e.code() = 32, I/O error: Broken pipe, Stack trace (when copying this message, always include the lines below):

  25. ./build_docker/./base/poco/Foundation/src/Exception.cpp:164: Poco::Net::SocketImpl::error(int, String const&) @ 0x17831718 in /usr/lib/debug/usr/bin/clickhouse.debug

  26. ./build_docker/./contrib/llvm-project/libcxx/include/string:1499: Poco::Net::SocketImpl::sendBytes(void const*, int, int) @ 0x178328c3 in /usr/lib/debug/usr/bin/clickhouse.debug

  27. ./build_docker/./base/poco/Net/src/StreamSocketImpl.cpp:63: Poco::Net::StreamSocketImpl::sendBytes(void const*, int, int) @ 0x17836fb7 in /usr/lib/debug/usr/bin/clickhouse.debug

  28. ./build_docker/./base/poco/Net/src/HTTPSession.cpp:152: Poco::Net::HTTPSession::write(char const*, long) @ 0x17816a13 in /usr/lib/debug/usr/bin/clickhouse.debug

  29. ./build_docker/./base/poco/Net/src/HTTPChunkedStream.cpp:146: Poco::Net::HTTPChunkedStreamBuf::writeToDevice(char const*, long) @ 0x17804775 in /usr/lib/debug/usr/bin/clickhouse.debug

  30. ./build_docker/./base/poco/Foundation/include/Poco/BufferedStreamBuf.h:130: Poco::BasicBufferedStreamBuf<char, std::char_traits, Poco::Net::HTTPBufferAllocator>::sync() @ 0x17805869 in /usr/lib/debug/usr/bin/clickhouse.debug

  31. ./build_docker/./base/poco/Net/src/HTTPChunkedStream.cpp:55: Poco::Net::HTTPChunkedIOS::~HTTPChunkedIOS() @ 0x17804986 in /usr/lib/debug/usr/bin/clickhouse.debug

  32. ./build_docker/./base/poco/Net/src/HTTPChunkedStream.cpp:235: Poco::Net::HTTPChunkedOutputStream::~HTTPChunkedOutputStream() @ 0x17805522 in /usr/lib/debug/usr/bin/clickhouse.debug

  33. ./build_docker/./contrib/llvm-project/libcxx/src/include/atomic_support.h:74: DB::HTTPServerConnection::run() @ 0x149062e6 in /usr/lib/debug/usr/bin/clickhouse.debug

  34. ./build_docker/./base/poco/Net/src/TCPServerConnection.cpp:57: Poco::Net::TCPServerConnection::start() @ 0x17838054 in /usr/lib/debug/usr/bin/clickhouse.debug

  35. ./build_docker/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:48: Poco::Net::TCPServerDispatcher::run() @ 0x1783927b in /usr/lib/debug/usr/bin/clickhouse.debug

  36. ./build_docker/./base/poco/Foundation/src/ThreadPool.cpp:202: Poco::PooledThread::run() @ 0x179c0b07 in /usr/lib/debug/usr/bin/clickhouse.debug

  37. ./build_docker/./base/poco/Foundation/include/Poco/SharedPtr.h:231: Poco::ThreadImpl::runnableEntry(void*) @ 0x179be53d in /usr/lib/debug/usr/bin/clickhouse.debug

  38. ? @ 0x84fe4 in /lib64/libc.so.6

  39. ? @ 0x1057bc in /lib64/libc.so.6 (version 23.3.1.2823 (official build))