JDBC Postgresql Error: This connection has been closed
- 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
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