[dev.icinga.com #622] postgresql implementation based on libpq

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/622

Created by mfriedrich on 2010-07-19 15:24:08 +00:00

Assignee: (none)
Status: Rejected (closed on 2012-08-23 10:14:54 +00:00)
Target Version: (none)
Last Update: 2014-12-08 14:34:37 +00:00 (in Redmine)

---

making this optional next to libdbi support will fit best into the current setup.

$ sudo yum install postgresql-devel postgresql-libs

./configure --enable-idoutils --enable-pgsql

--with-pgsql-inc=/usr/include
--with-pgsql-lib=/usr/lib

should set the compiler flags correctly, including libpq-fe.h

header file will be included in ido2db.h for further usage.

next to that:

1/ use #ifdef USE_PGSQL, same as USE_ORACLE

2/ reorganize connection functions in db.c

3/ take care of sequences fetching in dbhandler.c

4/ create stored procedures for all queries, and adapt them slightly to fit for Oracle too

5/ create dynmaic delete queries in stored procedures as they are in oracle available

http://developer.postgresql.org/pgdocs/postgres/libpq.html

some dev hints for splitting and copying.

%s/\#ifndef USE_ORACLE/\#ifdef USE_LIBDBI/g

%s/\#else \/\* Oracle ocilib specific \*\//\#endif\r\r\#ifdef USE_PGSQL \/\* pgsql \*\/\r\r\#endif\r\r\#ifdef USE_ORACLE \/\* Oracle ocilib specific \*\//g


idi->dbinfo.pg_conn
idi->dbinfo.pg_result

:%s/varchar([0-9].*)/TEXT/g

Attachments

• ndoutils-1.4b6-postgresql.tar.gz mfriedrich - 2010-12-13 08:25:29 +00:00

Changesets

2010-07-19 19:21:27 +00:00 by mfriedrich f839aeea155a687d4c5be193e03bfde744a7e83e

first steps on postgresql with libpq

--enable-pgsql [--with-pgsql-inc|dir=]

configure checks for -lpq and libpq-fe.h and sets
the #defines HAVE_LIBPQ_FE_H and USE_PGSQL

code re-organized with #define USE_PGSQL, libdbi and
oracle work independant of my changes.

added initial pgsql database connection, which is tested
and working.

next to that, syslog output for ido2db added, if a client
is connecting.

refs #622

2010-07-20 12:10:47 +00:00 by mfriedrich dee775e15b8bc823df8da34a5b7c29dbb5cb6f59

update pgsql schema, replace varchar(n) by text

refs #622

2010-07-27 13:03:56 +00:00 by mfriedrich 4b1d162

update pgsql schema, replace varchar(n) by text

refs #622
refs #642

2010-07-28 19:02:16 +00:00 by mfriedrich dfb5fb5

first steps on postgresql with libpq

--enable-pgsql [--with-pgsql-inc|dir=]

configure checks for -lpq and libpq-fe.h and sets
the #defines HAVE_LIBPQ_FE_H and USE_PGSQL

code re-organized with #define USE_PGSQL, libdbi and
oracle work independant of my changes.

added initial pgsql database connection, which is tested
and working.

next to that, syslog output for ido2db added, if a client
is connecting.

refs #622

2010-07-30 12:28:33 +00:00 by mfriedrich 34cee48

fix binding NULL to name1/2 in oracle; cleanup code; update icinga-api

the mysql patch in #363 introduced single quotes to binded values
in oracle. that's wrong for objects entries on name1/name2.
furthermore, NULL was binded as a string, which can't be the case
when selecting on IS NULL.

name1/2 must be explicitly binded to \0 and then NULL if occuring
NULL char*

next to that, the code is cleaned up a bit and the api submodule
pointer is uptodate for git submodule update

still, binding null values needs a rewrite - in it's own function.

refs #668
refs #622
refs #363
refs #468

2010-11-24 12:30:06 +00:00 by mfriedrich a1768b0

install: add hint to configure that libpq is not yet fully working #622

refs #622

2011-01-13 14:19:19 +00:00 by mfriedrich 9ad3454

install: add hint to configure that libpq is not yet fully working #622

refs #622

---

Relations:

• relates #622
• relates #622

[icinga-migration]

Updated by mfriedrich on 2010-07-28 06:59:13 +00:00

the SERIAL for primary keys must be dropped as it initially creates sequences. I want to control that just like oracle does - create our own sequences.

when running queries, seq_name.nextval should be used for the primary key then.

using libpq and stored procedures, we should take the query like this:

upsert_timedevents($value1, $value2, ...)

and asprintf everything into a string, handing that over to PGExec. The way of handing over a parameter list won't match, since the datatypes differ (text, timestamp, integer).

the unix timestamp conversion stuff needs to be handled directly within the queries, not before asprintf'ing the strings.

[icinga-migration]

Updated by mfriedrich on 2010-07-28 07:02:43 +00:00

http://www.mail-archive.com/pgsql-performance@postgresql.org/msg21277.html

upsert examples:

Tabelle erstellen:

CREATE TABLE hugo_test (
  id             BIGINT     PRIMARY KEY,
  data           TEXT       NOT NULL
);

plpgsql Funktionen - nicht wundern, die checken auch schon race conditions...

===
CREATE OR REPLACE FUNCTION gogo_insert(insert_data TEXT[]) RETURNS void as $$
BEGIN
  LOOP
    UPDATE hugo_test
    SET    data = insert_data[2]::TEXT
    WHERE  id = insert_data[1]::BIGINT;
    IF found THEN RETURN; END IF;
    BEGIN
      INSERT INTO hugo_test (
        id,
        data
      ) VALUES (
        insert_data[1]::BIGINT,
        insert_data[2]::TEXT
      );
      RETURN;
    EXCEPTION WHEN unique_violation THEN -- do nothing
    END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION gogo_insert(key BIGINT, value TEXT) RETURNS void as $$
BEGIN
  LOOP
    UPDATE hugo_test
    SET    data = value
    WHERE  id = key;
    IF found THEN RETURN; END IF;
    BEGIN
      INSERT INTO hugo_test (
        id,
        data
      ) VALUES (
        key,
        value
      );
      RETURN;
    EXCEPTION WHEN unique_violation THEN -- do nothing
    END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
===

Beispiel 1:

select gogo_insert(array ['10', 'i can haz']);

Beispiel 2:

select gogo_insert(10, 'cheezeburgr?');

ordering of the different datatypes - integer, text (timestamps will be integer)

[icinga-migration]

Updated by mfriedrich on 2010-12-13 08:25:29 +00:00

• File added ndoutils-1.4b6-postgresql.tar.gz

some more ideas, especially on sequence handling and stuff.

[icinga-migration]

Updated by mfriedrich on 2011-05-23 16:23:36 +00:00

• Status changed from New to Closed
• Assigned to deleted mfriedrich

i'm stopping this for now as i don't have time for that. the current implementation first tries to update, then to insert if not any rows affected. this is a rather good solution based on the fact that most of the status date gets updated a lot. the historical checks might be replaced in the order somehow by adding stored procedures or similar.

but replacing libdbi-pgsql with libpq isn't sufficient currently. re-open if someone feels to implement that / provide a patch.

[icinga-migration]

Updated by mfriedrich on 2011-12-19 21:34:03 +00:00

• Status changed from Closed to Assigned
• Assigned to set to mfriedrich

[icinga-migration]

Updated by mfriedrich on 2012-08-23 10:14:54 +00:00

• Status changed from Assigned to Rejected
• Assigned to deleted mfriedrich

will consider for rewrite.

[icinga-migration]

Updated by mfriedrich on 2014-12-08 14:34:37 +00:00

• Project changed from 18 to Core, Classic UI, IDOUtils
• Category changed from 24 to IDOUtils