Postgres: error when using sequence on postgres 10+

[gonzalad]

Describe the bug

This issue originates from rapiddweller/rapiddweller-benerator-ce#134

I'm using postgres 10.1

When generating data using a postgres sequence (with DBSequenceGenerator), I get the following error:

java.lang.RuntimeException: Error executing query: select sequence_name, start_value, increment_by, max_value, min_value, is_cycled, cache_value, last_value from accounts_id_seq
    at com.rapiddweller.jdbacl.DBUtil.executeQuery (DBUtil.java:1014)
    at com.rapiddweller.jdbacl.DBUtil.querySingleRow (DBUtil.java:946)
    at com.rapiddweller.jdbacl.dialect.PostgreSQLDialect.querySequences (PostgreSQLDialect.java:93)
Caused by: org.postgresql.util.PSQLException: ERROR: column "sequence_name" does not exist
  Position: 8

It seems that sequence handling has been changed in postgres 10+

See rails/rails#28864

To Reproduce

• start en empty postgres db
• run this benerator.xml

<?xml version="1.0" encoding="UTF-8"?>

<setup xmlns="https://www.benerator.de/schema/2.0.0"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="https://www.benerator.de/schema/2.0.0-jdk-11 benerator-2.0.0.xsd"
        defaultEncoding="UTF-8"
        defaultDataset="US"
        defaultLocale="en_US"
        defaultLineSeparator="\n">

    <import platforms="db"/>

    <echo>
        Populates a database
    </echo>

    <echo>defining a database that will be referred by the id 'db' later</echo>
    <database id="db"
        url="jdbc:postgresql://localhost:5432/postgres"
        driver="org.postgresql.Driver"
        schema="public"
        user="postgres"
        password="postgres"/>


    <echo>Drop the testtable if it already exist. If it does not exist yet, the error message is ignored</echo>
    <execute target="db" type="sql" onError="ignore">
        DROP SEQUENCE IF EXISTS accounts_id_seq;
        DROP TABLE IF EXISTS accounts;
    </execute>

    <echo>Creating testtable</echo>
    <execute target="db" type="sql">
        CREATE SEQUENCE accounts_id_seq;
        CREATE TABLE accounts (
        user_id BIGINT PRIMARY KEY,
        username VARCHAR ( 50 ) UNIQUE NOT NULL
        );
    </execute>

    <echo>Generating 100 entries for testtable</echo>
    <generate type="accounts" consumer="db,ConsoleExporter" count="100">
        <id name="id" type="long"
            generator="new DBSequenceGenerator('accounts_id_seq', db, true)"/>
    </generate>
</setup>

Full project is available here: https://github.com/gonzalad/benerator-postgresql/tree/issue-postgres-sequence

Expected behavior

Data generation with a sequence should work.

Versions:

• benerator: 2.0.0-jdk-11
• postgres: 10.1 (but we should have the same error in 10)

[gonzalad]

I just created this PR: #20

Thanks !

[vbergmann2]

gonzalad, first of all thank you for reporting and contributing!

It is very helpful that you posted a reproducer, the issue is reproducible also on Postgres 14.

I will investigate this further, find out, at which version the name was changed and get back to you.

[vbergmann2]

Indeed, Postgres 10 is the first version to use the new syntax, excellent work and thank you for the contribution! Your code is now pulled and tested.

[vbergmann2]

That's all