Generate Hibernate Id from database function, for table partitioning-CodePudding

I want to embed date information in the primary key, for a table that will be partitioned (monthly) in a PostgreSQL database. This should in theory speed up the process on finding out in which partition to look for the data. I followed this article to embed the date in a date into the serial. Now, I am however facing the problem that I can't get the Id been used by Hibernate.

c.f. the sql that should give an idea of the attempted approach.

CREATE SEQUENCE test_serial START 1;

CREATE OR REPLACE FUNCTION gen_test_key() RETURNS BIGINT AS $$
DECLARE
    new_id bigint;
BEGIN
    new_id = (nextval('public.test_serial'::regclass)::bigint % 10000000000000::bigint
          ( (EXTRACT(year from now())-2000)::bigint * 10000::bigint
              EXTRACT(month from now())::bigint * 100::bigint
              EXTRACT(day from now())::bigint
              )::bigint * 10000000000000::bigint
        )::bigint;
    RETURN new_id;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE test
( id bigint primary key default gen_test_key(),
  something text,
  tstamp timestamp default now()
) PARTITION BY RANGE (id);

CREATE TABLE test_2022_10 PARTITION OF test
FOR VALUES FROM (2210100000000000000::bigint ) TO (2211010000000000000::bigint);

I came across a similar question, where it was suggested to use a stored procedure. Unfortunately only functions are allowed as default in the table definition and therefore stored procedures, seam not to work for me.

CodePudding user response：

I think what you need here is a subtype of SequenceStyleGenerator that overrides determineBulkInsertionIdentifierGenerationSelectFragment to run the code of this function. You should be able to configure this generator on your entity with @GenericGenerator. I understand the desire to use this concept when you don't want to change your existing queries, but are you sure that partitioning will help you in your use case?

Also, be careful and do not rely on the date information in the primary key, because with pooled optimizers, it might happen that a value is generated way before it actually is used as primary key for a row.

CodePudding user response：

So this is a solution that worked out in the end as suggested @ChristianBeikov here the entity with the annotations pointing to the CustomIdGenerator.

public class Test {

    @Id
    @GenericGenerator(name = "CustomIdGenerator", strategy = "nl.test.components.CustomIdGenerator")
    @GeneratedValue(generator = "CustomIdGenerator")
    private Long id;

    private String something;
    private OffsetDateTime tstamp;
}

As explained by @Mr_Thorynque it is similarly possible to call a stored function as a procedure. Just replace "CALL gen_test_key()" with "SELECT gen_test_key()" and don't pass it to the wrong method for stored procedures connection.prepareCall(CALL_STORE_PROC);, but instead connection.prepareStatement(STORED_FUNCTION); So, this is the CustomIdGenerator.

public class CustomIdGenerator implements IdentifierGenerator {

private static final String STORED_FUNCTION = "select gen_test_key()";

@Override
public Serializable generate(SharedSessionContractImplementor session, Object object) throws HibernateException {
    Long result = null;
    try {
        Connection connection = session.connection();
        PreparedStatement pstmt = connection.prepareStatement(STORED_FUNCTION);
        ResultSet resultSet = pstmt.executeQuery();

        if (resultSet.next()) {
            result = resultSet.getLong(1);
            System.out.println("Generated Id: "   result);
        }

    } catch (SQLException sqlException) {
        throw new HibernateException(sqlException);
    }
    return result;
}
}