Thread: How to cast a general record?

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

Gerhard-
It is good you covered INSERTS but I would replicate the Insert Trigger for Update event
Vielen Danke,
Martin

______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.




> Date: Thu, 1 Jan 2009 14:41:08 +0100
> From: ml-postgresql-20081012-3518@gheift.de
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] How to cast a general record?
>
> 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

---

It’s the same Hotmail®. If by “same” you mean up to 70% faster. Get your account now.

Gerhard Heift wrote:
> 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.

The part of your plan where I see the biggest problem is the wish
to log whether the transaction was committed or not. You will probably
not be able to do that.

> CREATE OR REPLACE RULE "insert_log" AS ON INSERT TO log DO INSTEAD

... and shouldn't it be "DO ALSO"?

Yours,
Laurenz Albe

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;

On Friday 02 January 2009 03:53:58 Gerhard Heift wrote:
> 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.
> >

We created a similar project to this which is in the pgsoltools repo;
http://labs.omniti.com/trac/pgsoltools/browser/trunk/autonomous_logging_tool

This was originally created to mimic logging done in long-running Oracle
PL/SQL functions using autonomous commits, but should work within any trigger
functions on the postgres side as well (or at least givec you a good starting
point to adapt it).  HTH

--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com