Solution for tranaction independent logging in same database? - Mailing list pgsql-general

On Thu, Jan 01, 2009 at 02:41:08PM +0100, Gerhard Heift wrote:
> Hello,
>
> I want to log with triggers or in functions, and these logs should be
> independet of the transaction. Beside i want to have the information
> which action was commited and which not.
>
> So my idea was to log into the same database with dblink, return the
> primary keys and add them into a commit table.
>
> But my problem is, that I do not now how to write the rule properly.
>
> My schema locks like this:
>
> CREATE TABLE log_msg (
>   msg_id bigserial not null,
>   msg text not null,
>   constraint msg_pkey primary key (msg_id)
> );
>
> CREATE TABLE log_commit (
>   msg_id bigint not null,
>   constraint msg_pkey primary key (msg_id)
> );
>
> CREATE VIEW log AS
> SELECT log_msg.*, log_commit.msg_id IS NOT NULL AS commited
> FROM log_msg LEFT JOIN log_commit USING (msg_id);
>
> CREATE OR REPLACE RULE "insert_log" AS
>   ON INSERT TO log DO INSTEAD
>
>  -- now this is pseudo code:
> INSERT INTO log_commit (msg_id)
> SELECT dblink('dbname=samedb', 'INSERT log_msg (msg) VALUES ('
>   || quote_literal(new.msg)
>   || ') RETURNING msg_id');
>
> Regards,
>   Gerhard

I found a solution:

I added a function which add each new log message to the database via
dblink and returns the msg_id. So the msg is already commited, but not
visible to the current transaction. Then I add the msg_id into the
log_commit table. This will only be commited, if the whole transaction
commits. So, if the transaction is rolled back, the msg is still there,
but has not corresponding msg id in the log_commit table.

Is there a better solution for this?

Regards,
  Gerhard

SQL:

CREATE FUNCTION insert_remote(IN msg text, OUT msg_id bigint)
RETURNS bigint AS $BODY$
DECLARE
  RENAME msg TO a_msg;
  RENAME msg_id TO o_msg_id;
BEGIN
  IF NOT (ARRAY['remote_log'] <@ COALESCE(dblink_get_connections(),
  '{}'::text[])) THEN
    PERFORM dblink_connect('remote_log', 'dbname=...');
  END IF;

  SELECT nm.msg_id INTO o_msg_id
  FROM dblink('remote_log', 'INSERT INTO log_msg (msg) VALUES ('
    || quote_literal(msg) || ') RETURNING msg_id') nm(msg_id bigint);

  RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATIILE;

CREATE OR REPLACE RULE "insert_log" AS
  ON INSERT TO log DO INSTEAD
  INSERT INTO log_commit (msg_id) VALUES (insert_remote(new.msg))
  RETURNING msg_id, NULL::text, TRUE;