[GENERAL] Solution for tranaction independent logging in same database?
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;
signature.asc
Description: Digital signature
Re: [GENERAL] Bind message has 6 results formats but query has 5 columns
On Dec 29 2008, 5:22 am, teemu.juntu...@e-ngine.fi ("Teemu Juntunen")
wrote:
> Hi,
>
> I am using Delphi 2007 and turining property active false/true on
> table/query component will refresh the component (and its fields). If you
> are really using this query to drop columns of a table, then you shouldn't
> add the fields of the table in the query component. After dropping a column
> you could have a select result of 5 columns and fields for 6 columns.
>
> Best regards,
> Teemu Juntunen
>
> - Original Message -
> From: "J Ottery"
> To:
> Sent: Saturday, December 27, 2008 7:47 AM
> Subject: [GENERAL] Bind message has 6 results formats but query has 5
>
> columns
>
> > Windows XP, Using Delphi 7 ADO SQL Query Component to Drop/Delete a
> > Column from a simple table.
> > When I then try to query the table I get this error:
>
> > "Bind message has 6 results formats but query has 5 columns"
>
> > Obviously I need to refresh the connection or table but how?
>
> > I have tried
> > ADOConnection.Connected:=False;
> > ADOConnection.Connected:=True;
>
> > SQLTable.Close;
> > SQLTable.Open;
>
> > SQLTable.Fieldefs.Refresh;
>
> > All to no avial.
>
> > What is my solution to this?? Your contrib would be highly
> > aprreciated.
>
> > --
> > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> > To make changes to your subscription:
> >http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your
> subscription:http://www.postgresql.org/mailpref/pgsql-general
Thank. I am nott adding fields in the query, just dropping one column
then trying to query the whole table again
As you stated "After dropping a column you could have a select result
of 5 columns and fields for 6 columns" is what is happening.
turining property active false/true on is not fixing this.
Any suggestions??
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Solution for tranaction independent logging in same database?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unison Protein Database manuscript
Dear PostgreSQL friends- I spoke at OSCON 2005 on the Unison Protein Database, which is built on PostgreSQL. Unison's grown a lot in complexity, size, and (internal) user base since then. The whole thing -- database, most content, web interface, and command line tools -- are Open Source. A paper on Unison was accepted to the Pacific Symposium on Biocomputing and will appear shortly. I'll also be giving a talk on it at PSB on Jan 9. Links: http://unison-db.org/ -- the web site, docs, downloads http://harts.net/reece/pubs/ -- the manuscript http://psb.stanford.edu/ -- the PSB conference site I'm open to all feedback and problem reports. Cheers, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
