apache / apache/pulsar

JDBC Postgresql Error: This connection has been closed

Open
#15,476 3 comments 0 reactions 0 assignees View on GitHub
Stale type/bug
Dominant language
Java
Stars
15.3k
Forks
3.8k
Avg merge
1d 14h
Merged PRs (30d)
160

Description

**Describe the bug**
I'm using the JDBC driver to sink Postgresql, and when Postgresql stops It says that "This connection has been closed". When Postgresql starts again the sink doesn't work and it doesn't reconnect.

**To Reproduce**

Steps to reproduce the behavior:
1. Start Postgresql and Pulsar
2. Set JDBC Postgresql sink. I did this quickstart
https://pulsar.apache.org/docs/en/io-quickstart/#connect-pulsar-to-postgresql
3. Check if the sink is already configured and working. Start to get new messages in Pulsar
4. Stop Postgresql instance
5. I see this error
```
2022-05-06T15:41:06,502+0000 [pool-6-thread-1] ERROR org.apache.pulsar.io.jdbc.JdbcAbstractSink - Got exception
org.postgresql.util.PSQLException: This connection has been closed.
at org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:857) ~[postgresql-42.2.12.jar:42.2.12]
at org.postgresql.jdbc.PgConnection.getAutoCommit(PgConnection.java:817) ~[postgresql-42.2.12.jar:42.2.12]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:409) ~[postgresql-42.2.12.jar:42.2.12]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369) ~[postgresql-42.2.12.jar:42.2.12]
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159) ~[postgresql-42.2.12.jar:42.2.12]
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:148) ~[postgresql-42.2.12.jar:42.2.12]
at org.apache.pulsar.io.jdbc.JdbcAbstractSink.flush(JdbcAbstractSink.java:205) ~[pulsar-io-jdbc-core-2.9.8.jar:?]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) [?:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304) [?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
at java.lang.Thread.run(Thread.java:829) [?:?]
```
7. This is ok but when i want to start Postgresql again, the error keeps appearing and it doesn't reconnect
8. If you want to write new messages from Pulsar to Postgres after this gets stopped and started, you have to restart the sink.

Should the Pulsar sink try to reconnect to Postgresql after this stops?

**Desktop (please complete the following information):**
- OS: Ubuntu 20.04
- Pulsar: 2.9.1
- Postgres: 12

Contributor guide

Open the contributing guide

Research direction

Start with JdbcAbstractSink.java at the flush call shown in the stack trace and reproduce the failure using the JDBC PostgreSQL sink quickstart. Stop and restart PostgreSQL while Pulsar is producing messages; done means the sink reconnects and resumes writing without requiring a sink restart.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, postgresql
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.