column "logcode" of relation "log" does not exist --- and other SQL errors

[Thomas Schweikle]

Binary package hint: dsyslog-module-postgresql

The dsyslog module postgresql does exaust wrong sql-statements. Various SQL-Errors are resulting:

Dec 13 17:56:20 dbsrv dsyslog[3351]: (ERROR) output_postgres.c:72 [output_postgres_handler]: Preparing log statement failed: ERROR: syntax error at or near "`"
LINE 1: INSERT INTO `log` (logcode, timestamp, datestamp, source, pr...

After removing '`' (editing the binary making '`' to ' ' (space)):
Dec 13 18:10:09 dbsrv dsyslog[13593]: (ERROR) output_postgres.c:72 [output_postgres_handler]: Preparing log statement failed: ERROR: column "logcode" of relation "log" does not exist
LINE 1: INSERT INTO log (logcode, timestamp, datestamp, source, pr...

ProblemType: Bug
DistroRelease: Ubuntu 10.10
Package: dsyslog-module-postgresql 0.6.0build1
ProcVersionSignature: Ubuntu 2.6.32-26.48-generic-pae 2.6.32.24+drm33.11
Uname: Linux 2.6.32-26-generic-pae i686
Architecture: i386
Date: Mon Dec 13 18:10:18 2010
InstallationMedia: Ubuntu-Server 10.04 LTS "Lucid Lynx" - Release i386 (20100427)
ProcEnviron:
 LC_CTYPE=en_US.UTF-8
 SHELL=/bin/zsh
SourcePackage: dsyslog

[Thomas Schweikle (tps)]

This bug is present in:

11.04 (alpha)
10.10
10.04.1

[Thomas Schweikle (tps)]

INSERT INTO table [ ( column [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ RETURNING * | output_expression [ AS output_name ] [, ...] ]

has to correspond to
INSERT INTO `log` (logcode, timestamp, datestamp, source, program, message) VALUES ($1, $2, $3, $4, $5, $6)

and to
CREATE TABLE log
(
        logid int8 NOT NULL,
        "timestamp" int8 NOT NULL,
        datestamp varchar NOT NULL,
        source varchar NOT NULL,
        program varchar NOT NULL,
        message text NOT NULL,
        CONSTRAINT log_logid_pkey PRIMARY KEY (logid)
)

logcode should be logid

[Thomas Schweikle (tps)]

In the configuration file the dbport is for MySQL. Should be the one for PostgreSQL:

output postgres { dbhost localhost; dbport 3306; dbuser sysloguser; dbpass syslogpass; dbname syslogs; };

output postgres { dbhost srv.domain; dbport 5423; dbuser sysloguser; dbpass syslogpass; dbname syslogs; };

[Thomas Schweikle (tps)]

This bug exists in the latest version of the package it is not fixed.