PostgreSQL - Unable to execute SQL statement - ERROR: relation "dual" does not exist #1983
Description
Hi guys,
after switching the database of my Sympa dev environment from SQLite to PostgreSQL, I stumbled upon an issue while trying to use the "add" mail command to add a new subscriber to a mailing list.
add [email protected] [email protected]
quit
The account used to send the mail is an owner of the list [email protected]. The response from the system was "Kommando schlug aufgrund eines internen Serverfehlers fehl" (internal server error).
The sympa.log file showed the following error:
Jul 31 16:43:43 sympac001 sympa_msg[2310561]: notice Sympa::Request::Message::__parse() Parsing: test add 1
Jul 31 16:43:43 sympac001 sympa_msg[2310561]: notice Sympa::Request::Message::__parse() Parsing: add [email protected] [email protected]
Jul 31 16:43:43 sympac001 sympa_msg[2310561]: notice Sympa::Request::Message::__parse() Parsing: quit
Jul 31 16:43:43 sympac001 sympa_msg[2310561]: notice Sympa::Spindle::ProcessMessage::_twist() Processing Sympa::Request <action=add;[email protected];[email protected]>
Jul 31 16:43:43 sympac001 sympa_msg[2310561]: err main::#241 > Sympa::Spindle::spin#95 > Sympa::Spindle::DoCommand::_twist#120 > Sympa::Spindle::spin#95 > Sympa::Request::Handler::add::_twist#80 > Sympa::List::add_list_member#3292 > Sympa::DatabaseDriver::PostgreSQL::do_prepared_query#105 > Sympa::Database::do_prepared_query#383 Unable to execute SQL statement "INSERT INTO subscriber_table (date_epoch_subscriber, number_messages_subscriber, user_subscriber, visibility_subscriber, reception_subscriber, update_epoch_subscriber, subscribed_subscriber, list_subscriber, robot_subscriber) SELECT ?, ?, ?, ?, ?, ?, ?, ?, ? FROM dual WHERE NOT EXISTS ( SELECT 1 FROM subscriber_table WHERE user_subscriber = ? AND list_subscriber = ? AND robot_subscriber = ? )": (42P01) ERROR: relation "dual" does not exist#012LINE 1: ...r) SELECT $1, $2, $3, $4, $5, $6, $7, $8, $9 FROM dual WHERE...#012 ^
Jul 31 16:43:43 sympac001 sympa_msg[2310561]: err main::#241 > Sympa::Spindle::spin#95 > Sympa::Spindle::DoCommand::_twist#120 > Sympa::Spindle::spin#95 > Sympa::Request::Handler::add::_twist#80 > Sympa::List::add_list_member#3317 Unable to add member [email protected] to the list Sympa::List <[email protected]>The Postgres log file showed the following error:
2025-07-31 16:43:43.180 CEST [2311535] ERROR: relation "dual" does not exist at character 274
2025-07-31 16:43:43.180 CEST [2311535] STATEMENT: INSERT INTO subscriber_table (date_epoch_subscriber, number_messages_subscriber, user_subscriber, visibility_subscriber, reception_subscriber, update_epoch_subscriber, subscribed_subscriber, list_subscriber, robot_subscriber) SELECT $1, $2, $3, $4, $5, $6, $7, $8, $9 FROM dual WHERE NOT EXISTS ( SELECT 1 FROM subscriber_table WHERE user_subscriber = $10 AND list_subscriber = $11 AND robot_subscriber = $12 )
2025-07-31 16:43:43.180 CEST [2311535] ERROR: relation "dual" does not exist at character 274
2025-07-31 16:43:43.180 CEST [2311535] STATEMENT: INSERT INTO subscriber_table (date_epoch_subscriber, number_messages_subscriber, user_subscriber, visibility_subscriber, reception_subscriber, update_epoch_subscriber, subscribed_subscriber, list_subscriber, robot_subscriber) SELECT $1, $2, $3, $4, $5, $6, $7, $8, $9 FROM dual WHERE NOT EXISTS ( SELECT 1 FROM subscriber_table WHERE user_subscriber = $10 AND list_subscriber = $11 AND robot_subscriber = $12 )However using the web interface to add the subscriber worked flawlessly. The mail command also worked fine when Sympa was using the SQLite database.
When setting up the database I followed the instructions on Setup database: PostgreSQL.
Please let me know if you are able to reproduce the issue or if I missed something when configuring the system.
There are no other error messages in sympa.log hinting towards a configuration issue. The different behavior of the web interface makes me suspect this might be a bug.
Workaround
I created a view in the sympa database, that fixed the issue.
CREATE VIEW public.dual AS SELECT 'X'::varchar AS dummy;Version
Sympa 6.2.76
RHEL 9.6
Postgres 16.8
Installation method
RPM package
Expected behavior
The "add" mail command should add the new subscriber to the list. Sympa should not generate SQL statements using dual dummy table or there is something missing from the Postgres setup instructions.
Actual behavior
The "add" mail command fails adding the new subscriber to the list. The prepared SQL query fails because the dual dummy table doesn't exist.
Steps to reproduce
- Setup Sympa using Postgres 16 database.
- Create a new mailing list
- Use the "add" mail command to add a new subscriber to the mailing list
Additional information
Database config in sympa.conf
# Database configuration
db_type PostgreSQL
db_name sympa
db_user sympa_pgsql
db_passwd *****
db_host /var/run/postgresqlSo far I've not tried to use other versions of Postgres besides 16.8.
Best regards
Chris