Documentation request: how to use PGJDBC-NG with Spring Boot

[mickaeltr]

Hello,

It would be nice to have documentation about using PGJDBC-NG with Spring Boot.

Thanks!

[YikSanChan]

Just curious, is there a way to use pgjdbc-ng with spring boot?

[kdubb]

The driver is JDBC compliant so I imagine you need to just change the maven/gradle dependency and use the correct URL (see here)

[YikSanChan]

@kdubb is there an example app that demos the use of pgjdbc-ng

[kdubb]

Not the I know of. I tried generating a spring boot application and added JDBC & PostgreSQL support... it doesn't have any code using the driver though.

If you find a real example using JDBC & Postgres we can work from there. That'll help me update the docs to include a spring boot section as well.

[mickaeltr]

I tried and failed configuring my Spring Boot application with PGJDBC-NG. It was 5 months and I forgot about what went wrong but I'll give it another try and let you know about it…

[kdubb]

@mickaeltr That'd be great. If I have something using the mainline driver I can switch it and work through any issues, then update docs.

[YikSanChan]

@kdubb

A good example of spring boot + jdbc: https://github.com/spring-guides/gs-relational-data-access/tree/master/complete A good example of spring boot + jpa: https://github.com/spring-guides/gs-accessing-data-jpa/tree/master/complete

I am especially interested in using spring boot + jpa + leverage pgjdbc-ng s.t. my app is able to listen to changes in postgresql and receive Asynchronous Notifications

Thanks

[kdubb]

@YikSanChan Those use H2 database with some Spring Boot magic to autowire that db. I tinkered for 5 minutes but don't know how to replace the H2 magic. Get me something using the mainline pgdjbc driver.

[YikSanChan]

@kdubb my bad. will give you an example using postgresql soon.

[YikSanChan]

@kdubb https://github.com/YikSanChan/gs-accessing-data-jpa/tree/master/complete Here we go: spring boot + jpa + postgres. Thanks for your help.

[kdubb]

@YikSanChan I created a PR for to your repository that switches to this driver.

./mvnw spring-boot:run completes successfuly.

[YikSanChan]

@kdubb Thx! I am especially interested in using spring boot + jpa + leverage pgjdbc-ng s.t. my app is able to listen to changes in postgresql and receive Asynchronous Notifications. Have you tried that before?

[kdubb]

http://impossibl.github.io/pgjdbc-ng/docs/current/user-guide/#extensions-notifications

[YikSanChan]

@kdubb Will look into that and let you know if I have questions. Thx

[kdubb]

@mickaeltr Can you look at @YikSanChan's repository and my PR against it to see if that solves your problems with Spring Boot? (they are very simple).

If that is the case I think I have enough to add a spring boot section to the docs, if not we can work through those too.

[mickaeltr]

I'll give it a try in a couple of days. My project might be interesting because we have liquibase, native queries, testcontainers…

[mickaeltr]

So, I managed to set up PGJBC-NG on my project, with a few more changes though.

1. It seems like the default schema is not properly set with this driver, so I had to specify it:

   spring.jpa.properties.hibernate.default_schema=public
   spring.liquibase.default-schema=public

2. I was unable to use the type TIMESTAMP WITH TIME ZONE and had to replace it with TIMESTAMP

3. I had to override the existing PostgreSQLContainer from testcontainers, in order to run my tests:

import org.testcontainers.containers.PostgreSQLContainer;

public class PGJDBCSQLContainer<SELF extends PostgreSQLContainer<SELF>> extends PostgreSQLContainer<SELF> {

  public PGJDBCSQLContainer(String dockerImageName) {
    super(dockerImageName);
  }

  @Override
  public String getDriverClassName() {
    return "com.impossibl.postgres.jdbc.PGDriver";
  }

  @Override
  public String getJdbcUrl() {
    return "jdbc:pgsql://" + getContainerIpAddress() + ":" + getMappedPort(POSTGRESQL_PORT) + "/" + getDatabaseName();
  }
}

1. and 2. seems to me like bugs/inconsistensies with the default PostgreSQL driver, while 3. could be a nice addition to testcontainers.

What do you think?

[davecramer]

How are 1 and 2 bugs ? Can you explain ?

[mickaeltr]

When I read that "PGJDBC-NG aims to be a fully compliant JDBC driver for PostgreSQL", I expect to be able to switch to this driver without that kind of changes.

The TIMESTAMP (WITH TIMEZONE) thing seems particularly embarrassing in my opinion, since it cannot be fixed with configuration properties. The error is:

org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column [mycolumn] in table [public.mytable]; found [timestamptz (Types#TIMESTAMP_WITH_TIMEZONE)], but expecting [timestamp (Types#TIMESTAMP)]

[kdubb]

A1) The default "SCHEMA" in PostgreSQL is "not a thing"... it has something called search_path, a list of schemas to search (like a unix shell's PATH variable). It defaults to "$user", public. So I'm not quite sure how it is not being set correctly because it defaults to reading the public schema.

What error do you get without that line?

A2) TIMESTAMP WITH TIME ZONE is fully supported. It's implementation is different than the mainline driver's but it passes nearly the exact same test cases see here.

What is stopping the use of it? What error(s) are received?

A3) Seems like it would be helpful. testcontainers was previously unknown to me (I flagged it for review in our commercial code... thanks for the introduction 😉), I'm not yet briefed on its use.

Did you open an enhancement request in testcontainers? Do they have an existing solution for selecting between specific driver implementations?

[davecramer]

as to "fully compliant JDBC driver" that means that it is compliant with JDBC which sadly allows for quite a bit of latitude as the spec is not particularly well written.

[kdubb]

@mickaeltr It seems I responded having completely missed your error regarding timestamps... need my coffee before responding next time.

That's an error raised by Hibernate. With the provided information this appears to point out a mismatch in your JDBC schema and the Database's SQL schema.

This driver is "binary first" and sometimes it requires more exact matching of JDBC types to their SQL counterparts. I'm not sure in this case because I believe the mainline driver uses binary for timestamps. Providing a simple failing project would help me greatly in discovering why Hibernate is acting differently between drivers.

[davecramer]

@kdubb my recollection is that we have no real way to support both timestamp and timestamptz in hibernate so we choose one. This may help https://stackoverflow.com/questions/13357487/how-to-map-postgresql-timestamp-with-time-zone-in-a-jpa-2-entity

[mickaeltr]

A1) If I don't specify a default schema, my application fails to start with org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing table [my_table]. Setting spring.jpa.properties.hibernate.default_schema=public and spring.liquibase.default-schema=public fixes it.

A2) My application was using TIMESTAMP WITH TIMEZONE for Instant objects, which I understand is wrong. So I just changed that for TIMESTAMP. At least we know that some inconsistencies can be met while switching drivers.

A3) I am not aware of any plan to support alternative drivers for testcontainers. As you saw above, it's pretty straightforward to implement and they may be opened to contributions.

Just so you know, I don't have a use case (yet) for switching my application to PGJDBC-NG. I just gave it a try a while ago and had troubles setting it up. Anyway, I am happy if I can help make it better.

[kdubb]

FYI, Instant is not supported by JDBC at all. The types to use are OffsetDateTime for TIMESTAMP_WITH_TIMEZONE and LocalDateTime for TIMESTAMP.

If Hibernate supports Instant they seem to have fallen into the same trap as JDBC did originally (prior to JDBC 4.2) in that they are "auto-converting" based on the current JVM timezone; JDBC realized this was wrong and stopped doing this in 4.2 (hence no support for Instance) and recommends not using previous Date/Time types & methods.

[kdubb]

@mickaeltr @davecramer As demonstrated here using Java 8's time/date classes Hibernate naturally supports TIMESTAMP_WITH_TIMEZONE as long as your field is an OffsetDateTime; no configuration necessary.

[kdubb]

@YikSanChan I simplified the changes to the application.properties even further (the PR against your repository is updated).

Essentially we just need to set the following properties:

spring.datasource.driver-class-name=com.impossibl.postgres.jdbc.PGDriver
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

The second property is actually required for either of the PostgreSQL drivers due to the server's handling of lobs.

[kdubb]

@mickaeltr Do you have a small project you can publish that demonstrates the issue related to the schema. The basic project here doesn't have any issues with the schema so I'm assuming this is a problem created by adding liquibase.

[mickaeltr]

@kdubb There you go: https://github.com/mickaeltr/gs-accessing-data-jpa/commit/91573e880b3c20d970eab0e1b85d46dc5af2374b

You will see the reproduction of the 2 issues mentioned above (search for the TODO in the commit):

• the TIMESTAMP (WITH TIMEZONE) thing
• the default schema not being found

As a side note, thanks for the heads up regarding the different date objects. I am really confused about that and would like to understand why the Hibernate team decided to support LocalDateTime for @CreationTimestamp and @UpdateTimestamp, although the Javadoc says "It cannot represent an instant on the time-line without additional information such as an offset or time-zone."; that's why we chose Instant. I will dig into that… EDIT: actually Hibernate supports all kinds of date types -> https://docs.jboss.org/hibernate/orm/5.3/javadocs/org/hibernate/annotations/CreationTimestamp.html ; problem solved!

[kdubb]

@mickaeltr Your changes were very helpful! Thank you!

First, I was able to determine that Liquibase has a peculiar, but not necessarily incorrect, mannerism in that it uses a CallableStatement like a PreparedStatement which wasn't accounted for in our implementation of CallableStatement. I opened #447 and have a fix available in #449.

Second, Hibernate requires an implementation of getSchema & setSchema if you don't specify the schema via properties. These are optional according to JDBC but coincidentally they were added last week and will be in the next release (probably 0.8.4); currently they are available in 0.9-SNAPSHOT

Finally, after getting past both of those issues, Hibernate will still not work with ddl-auto=validate because it expects the OffsetDateTime to be a TIMESTAMP by default. After much investigation this is codified inside Hibernate (see here). So, changing the type to what Hibernate expects (i.e TIMESTAMP) causes it to pass.

FYI, the reason the mainline driver works with ddl-auto=validate and TIMESTAMP WITH TIMEZONE is because it reports all timestamp columns as TIMESTAMP in its meta-data (see here). This is a change we will not be making because the driver is attempting adhere strictly to JDBC 4.2. As you pointed out there are other ways to use TIMESTAMP WITH TIMEZONE in Hibernate.

[kdubb]

Clearly I now have enough information to produce the required documentation for using the driver with Spring Boot.

Please open new issues for anything you encounter or would like changed. While I like the outcome of all the discussion and work in the thread it veered off topic a couple times already and its getting the original issue confused.

Thank you all for the discussion and help!

[arbiter34]

@kdubb Wanted to check in with you in regards to your comment:

Second, Hibernate requires an implementation of getSchema & setSchema if you don't specify the schema via properties. These are optional according to JDBC but coincidentally they were added last week and will be in the next release (probably 0.8.4); currently they are available in 0.9-SNAPSHOT

We use HikariCP which uses the java.sql.Connection.setSchema(String schema) in order to set search_path on pooled connections when it's specified. That code can be found here: https://github.com/brettwooldridge/HikariCP/blob/dev/src/main/java/com/zaxxer/hikari/pool/PoolBase.java#L412

I'm basically checking to see if you guys have an estimate on when you'll ship your next version that would contain your implementations of getSchema/setSchema.

Thank you!

[craftmaster2190]

If it helps anyone else or with some documentation; I've successfully integrated PGJDBC-NG using Spring Boot with the following:

spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults=false
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
app.db.driver=com.impossibl.postgres.jdbc.PGDriver
app.db.url=jdbc:pgsql://localhost:5432/fgc
app.db.username=postgres
app.db.password=postgres

import javax.sql.DataSource;
import lombok.Setter;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Setter
@Configuration
@ConfigurationProperties("app.db")
public class DataSourceConfig {

  private String driver;
  private String url;
  private String username;
  private String password;

  @Bean
  @ConditionalOnProperty("app.db.url")
  public DataSource getDataSource() {
    DataSourceBuilder<? extends DataSource> dataSourceBuilder = DataSourceBuilder.create();
    dataSourceBuilder.driverClassName(driver);
    dataSourceBuilder.url(url);
    dataSourceBuilder.username(username);
    dataSourceBuilder.password(password);
    return dataSourceBuilder.build();
  }
}

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.impossibl.postgres.api.jdbc.PGConnection;
import com.impossibl.postgres.api.jdbc.PGNotificationListener;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.function.Consumer;
import javax.sql.DataSource;
import lombok.RequiredArgsConstructor;
import lombok.Value;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

@Slf4j
@Service
@RequiredArgsConstructor
public class PostgresSubscriptions {
  private final DataSource dataSource;
  private final JdbcTemplate jdbcTemplate;
  private final ObjectMapper objectMapper;
  private final ExecutorService executorService = Executors.newFixedThreadPool(4);

  @Value
  private static class TypeConsumers<T> {
    private final Class<T> type;
    private final Set<Consumer<T>> consumers = ConcurrentHashMap.newKeySet();

    public void sendPayload(String payload) {
      T payloadObj = null;
      if (payload != null) {
        try {
          payloadObj = objectMapper.readValue(payload, getType());
        } catch (IOException e) {
          log.error("Unable to parse {}", payload, e);
        }
      }
      T finalPayloadObj = payloadObj;
      consumers.forEach(subscription -> subscription.accept(finalPayloadObj));
    }
  }

  private final Map<String, TypeConsumers<?>> topic2Type = new ConcurrentHashMap<>();

  private PGConnection getConnection() {
    try {
      return dataSource.getConnection().unwrap(PGConnection.class);
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }

  @SuppressWarnings("unchecked")
  public <T> void subscribe(String topicName, Class<T> clazz, Consumer<T> subscription) {
    String normalizedTopicName = topicName.toLowerCase();

    Set<? extends Consumer<?>> consumers =
        topic2Type
            .computeIfAbsent(
                normalizedTopicName,
                ignored -> {
                  PGConnection pgConnection = getConnection();
                  pgConnection.addNotificationListener(
                      new PGNotificationListener() {
                        @Override
                        public void notification(
                            int processId, String channelName, String payload) {
                          log.debug("Postgres Message: {} {} {}", processId, channelName, payload);
                          executorService.submit(
                              () -> topic2Type.get(channelName).sendPayload(payload));
                        }

                        @Override
                        public void closed() {
                          log.warn("{} listener closed!", normalizedTopicName);
                        }
                      });
                  try (Statement statement = pgConnection.createStatement()) {
                    statement.execute("LISTEN " + normalizedTopicName + ";");
                  } catch (SQLException e) {
                    throw new RuntimeException(e);
                  }
                  return new TypeConsumers<T>(clazz);
                })
            .getConsumers();
    ((Set<Consumer<T>>) consumers).add(subscription);
  }

  public <T> void send(String topicName, T payload) {
    try {
      String payloadString = objectMapper.writeValueAsString(payload);
      Assert.isTrue(
          !payloadString.contains("'"),
          () -> "Payload must not contain ' (single quotes): " + payloadString);
      jdbcTemplate.execute("NOTIFY " + topicName.toLowerCase() + ", '" + payloadString + "';");
    } catch (JsonProcessingException e) {
      throw new RuntimeException(e);
    }
  }
}

[alrawas]

@craftmaster2190 Curious, what did you do regarding Hikari pool? Also could you please tell how to use the PostgresSubscriptions class?

I am getting this exception on the instantiation of the class where I am using PGConnection according to https://database-patterns.blogspot.com/2014/04/postgresql-nofify-websocket-spring-mvc.html

com.zaxxer.hikari.pool.HikariProxyConnection cannot be cast to com.impossibl.postgres.api.jdbc.PGConnection

/**
 * @since 7/22/2014
 */
public class PGNotifyToWebSocket {

    @Autowired
    private SimpMessagingTemplate messagingTemplate;

    private PGConnection pgConnection;

    public PGNotifyToWebSocket(DataSource dataSource) throws Throwable {

        pgConnection = (PGConnection) dataSource.getConnection();

        pgConnection.addNotificationListener(new PGNotificationListener() {
            @Override
            public void notification(int processId, String channelName, String payload) {
                messagingTemplate.convertAndSend("/channels/" + channelName, payload);
            }
        });
    }

    public void init() throws Throwable {

        Statement statement = pgConnection.createStatement();
        statement.execute("LISTEN dml_events");
        statement.close();
    }

    public void destroy() throws Throwable {

        Statement statement = pgConnection.createStatement();
        statement.execute("UNLISTEN dml_events");
        statement.close();
    }
}

[alrawas]

@craftmaster2190 no worries I figured it out and got it working. Leaving this here for reference. Below is an example on how to use PostgresSubscriptions class written by @craftmaster2190 here https://github.com/impossibl/pgjdbc-ng/issues/413#issuecomment-599368592

I removed all spring.datasource properties that I defined previously

...
    @Autowired
    private PostgresSubsciptions postgresSubsciptions;

    @PostConstruct
    public void init() {

        Consumer<MyPayloadModel> pgConsumer = payloadObject -> {
            // do whatever with your payloadObject
        };
        postgresSubsciptions.subscribe("my_channel", MyPayloadModel.class, pgConsumer);
    }
...