Users must be defined for the transaction pipeline

[siwee]

Describe the bug

root@dev01:~# systemctl status pgagroal.service
× pgagroal.service - High-performance connection pool for PostgreSQL
     Loaded: loaded (/lib/systemd/system/pgagroal.service; disabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Thu 2023-02-16 20:56:18 HKT; 12min ago
TriggeredBy: ● pgagroal.socket
       Docs: man:pgagroal(1)
             https://agroal.github.io/pgagroal/
    Process: 7460 ExecStart=/usr/bin/pgagroal (code=exited, status=1/FAILURE)
   Main PID: 7460 (code=exited, status=1/FAILURE)
        CPU: 8ms

Feb 16 20:56:18 dev01 pgagroal[7057]: 2023-02-16 20:56:18 INFO  main.c:1145 pgagroal: shutdown
Feb 16 20:56:18 dev01 systemd[1]: Stopped High-performance connection pool for PostgreSQL.
Feb 16 20:56:18 dev01 pgagroal[7316]: 2023-02-16 20:56:18 WARN  management.c:330 pgagroal_management_kill_connection: connect: 12
Feb 16 20:56:18 dev01 pgagroal[7316]: 2023-02-16 20:56:18 WARN  management.c:1048 pgagroal_management_client_done: connect: 5
Feb 16 20:56:18 dev01 systemd[1]: Starting High-performance connection pool for PostgreSQL...
Feb 16 20:56:18 dev01 pgagroal[7460]: 2023-02-16 20:56:18 FATAL configuration.c:938 pgagroal: Users must be defined for the transaction pipeline
Feb 16 20:56:18 dev01 pgagroal[7460]: pgagroal: Invalid configuration
Feb 16 20:56:18 dev01 systemd[1]: Started High-performance connection pool for PostgreSQL.
Feb 16 20:56:18 dev01 systemd[1]: pgagroal.service: Main process exited, code=exited, status=1/FAILURE
Feb 16 20:56:18 dev01 systemd[1]: pgagroal.service: Failed with result 'exit-code'.

[pgagroal]
host = 0.0.0.0
port = 2345

log_type = console
log_level = info
log_path =

pipeline = transaction
max_connections = 100
idle_timeout = 600
validation = off
unix_socket_dir = /run/postgresql

[primary]
host = localhost
port = 5432

Any suggestions? I am new to pgagroal. Thanks.

To Reproduce

Steps to reproduce the behavior.

Version

What is the version of pgagroal ?

PostgreSQL

What is the version of PostgreSQL ?

libev

What is the version of libev ?

OpenSSL

What is the version of OpenSSL ?

Access method

Which security access method is used (trust, password, md5, scram-sha-256) ?

OS

Which Operating System (OS) is used ?

ulimit

What is the output from ulimit -a ?

Configuration

Can you provide the configuration pgagroal ?

• pgagroal.conf
• pgagroal_hba.conf
• pgagroal_databases.conf
• pgagroal_users.conf

Debug logs

Can you provide any debug logs (log_level = debug5) of the issue ?

Tip

Use ``` before and after the text to keep the output as is.

[jesperpedersen]

Did you go through the two first tutorials ?

You need to have a pgagroal_users.conf in place

[siwee]

Prepared statements working by setting track_prepared_statements = on in transaction mode. This feature is not available in pgbouncer, thanks for the great work.

[siwee]

Prepared statements have been tested and it only works occasionally. Concurrency will receive unnamed prepared statement does not exist. It seems that if I want prepared statements, I can only use session or performance mode.

[jesperpedersen]

Can you send me a pgbench script that shows your problem ?

[siwee]

I am not testing with pgbench. I don't know how prepared statements work in Pgbench, it doesn't seem to be the same in Java applications. In Postgresql Java driver, the execution of prepared statements is divided into two steps, so pgagroal should be able to track connections to ensure that they are in the same connection.

The situation now is that the query keeps waiting. Attaching my config and trace logs, maybe useful. Thanks.

pgagroal.conf:

[pgagroal]
host = 0.0.0.0
port = 2345

log_type = console
log_level = info
log_path =

#pipeline = performance
pipeline = transaction
tracker = on
track_prepared_statements = on
allow_unknown_users = false
max_connections = 20
#idle_timeout = 600
blocking_timeout = 0
validation = off
unix_socket_dir = /run/postgresql

[primary]
host = localhost
port = 5432

Pgagroal log:

● pgagroal.service - High-performance connection pool for PostgreSQL
     Loaded: loaded (/lib/systemd/system/pgagroal.service; disabled; vendor preset: enabled)
     Active: active (running) since Fri 2023-02-17 11:05:56 HKT; 1min 3s ago
TriggeredBy: ● pgagroal.socket
       Docs: man:pgagroal(1)
             https://agroal.github.io/pgagroal/
   Main PID: 19800 (pgagroal)
      Tasks: 2 (limit: 18978)
     Memory: 3.3M
        CPU: 4.652s
     CGroup: /system.slice/pgagroal.service
             ├─19800 "pgagroal: main" "" "" ""
             └─19806 "pgagroal: postgres@localhost:5432/statistic"

Feb 17 11:05:56 dev01 systemd[1]: Started High-performance connection pool for PostgreSQL.
Feb 17 11:05:56 dev01 pgagroal[19800]: 2023-02-17 11:05:56 INFO  main.c:1085 pgagroal: 1.5.0 started on 0.0.0.0:2345
Feb 17 11:06:21 dev01 pgagroal[19806]: 2023-02-17 11:06:21 INFO  tracker.c:73 PGAGROAL|0|-1|-3|1676603181457|19806||||-1|0|-1|-3|-1|-1|0|0|
Feb 17 11:06:21 dev01 pgagroal[19806]: 2023-02-17 11:06:21 INFO  tracker.c:159 PGAGROAL|100|1676603181457|19806|8|
Feb 17 11:06:21 dev01 pgagroal[19806]: 2023-02-17 11:06:21 INFO  tracker.c:73 PGAGROAL|8|-1|-3|1676603181457|19806|postgres|statistic||-1|0|-1|-3|-1|-1|0|0|
Feb 17 11:06:21 dev01 pgagroal[19806]: 2023-02-17 11:06:21 INFO  tracker.c:124 PGAGROAL|2|0|1|1676603181457|19806|postgres|statistic||1|0|0|-2|0|5|1|1|
Feb 17 11:06:21 dev01 pgagroal[19806]: 2023-02-17 11:06:21 INFO  tracker.c:159 PGAGROAL|101|1676603181601|19806|5|
Feb 17 11:06:21 dev01 pgagroal[19806]: 2023-02-17 11:06:21 INFO  tracker.c:124 PGAGROAL|30|0|1|1676603181601|19806|postgres|statistic|vertx-pg-client|1|0|0|10|0|5|1|1|
Feb 17 11:06:21 dev01 pgagroal[19806]: 2023-02-17 11:06:21 INFO  tracker.c:124 PGAGROAL|5|0|1|1676603181601|19806|postgres|statistic|vertx-pg-client|1|0|0|10|0|5|1|1|
Feb 17 11:06:21 dev01 pgagroal[19806]: 2023-02-17 11:06:21 INFO  tracker.c:73 PGAGROAL|32|-1|-3|1676603181607|19806|postgres|statistic||-1|0|-1|-3|-1|-1|0|0|

Java code:

        String sql = "SELECT * FROM table1 WHERE id = $1";
        Future<?> future = pool.withTransaction(conn ->
            // 1. Prepared statement
            conn.preparedQuery(sql).mapping(Row::toJson)
                // 2. Execute with parameters
                .execute(Tuple.of(1))
                .onSuccess(rows -> {
                    System.out.println(rows.iterator().next().toString());
                }));

        Uni<?> uni = Uni.createFrom().completionStage(future::toCompletionStage);

        asserter.assertThat(() -> uni, r -> {

        });

[jesperpedersen]

Its pgbench -M prepared ....

Thanks for the feedback !