Обсуждение: How to log whole query statement to relation?
Hi to all,
I realy need help of you. My problem is $subj. I mean, for example. If user run command like:
INSERT INTO public.foo (at1,at10) VALUES ('val1','val10')
I would need to catch this (may be in trigger?) and store this textual command to other log table. Later I will need
torun EXECUTE on this. That's why logging.
I tryied to build a trigger, but it's not generally usable, becase some user may run INSERT stmt with others
specifiedatributes (some of them have default values) and trigger cannot catch this situation.
Or anyone has other possible solution how to solve this problem? Generaly: on specified tables I need to know (inside
PostgreSQL,not seeing log files in system) which command user run. This is because of I need to made of very simplified
asynchronnousmultimaster replication (better say synchronization).
Thank's to all contributions...
David F
I create something like this:
--------CUT HERE-----
CREATE OR REPLACE FUNCTION ftgr_data_all() RETURNS trigger AS $$
DECLARE
fname text;
BEGIN
fname:=quote_ident(TG_TABLE_SCHEMA)||'.'||quote_ident(TG_TABLE_NAME);
IF (TG_OP = 'INSERT') THEN
INSERT INTO rep.repl_log (id,op,cmd)
VALUES (NEW.id,'INS',
'INSERT INTO '||fname||' '||
'VALUES ('||quote_literal(NEW.id)||','
||quote_literal(NEW.at1)||','
||quote_literal(NEW.at2)||','
||quote_literal(NEW.at3)||','
||quote_literal(NEW.at4)||')'
);
ELSEIF (TG_OP = 'UPDATE') THEN
ELSEIF (TG_OP = 'DELETE') THEN
END IF;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
am Tue, dem 24.04.2007, um 9:00:54 +0200 mailte David Flegl folgendes:
> Hi to all, I realy need help of you. My problem is $subj. I mean, for
> example. If user run command like: INSERT INTO public.foo (at1,at10)
> VALUES ('val1','val10') I would need to catch this (may be in
> trigger?) and store this textual command to other log table. Later I
> will need to run EXECUTE on this. That's why logging. I tryied to
> build a trigger, but it's not generally usable, becase some user may
> run INSERT stmt with others specified atributes (some of them have
> default values) and trigger cannot catch this situation.
>
> Or anyone has other possible solution how to solve this problem?
> Generaly: on specified tables I need to know (inside PostgreSQL, not
> seeing log files in system) which command user run. This is because of
> I need to made of very simplified asynchronnous multimaster
> replication (better say synchronization).
I think, you can't do log the sql as text, but you can tools like
'tablelog' to obtain a similar behavior:
http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Hi,
> I think, you can't do log the sql as text, but you can tools like
> 'tablelog' to obtain a similar behavior:
thank's for inspiration.
And another question. Is it possible to parse record type NEW? How could
one do something like (in trigger where new is set up):
text_var:=to_char(NEW)
So if NEW.at1='test' and NEW.at2=100 I will get string: 'test',100
But the problem is then if trigger is generally used for many tables, I
don't know number of elements and so on.
I know that for basic types is defined out function, and I may get this
behavior for example point type this way:
SELECT textin(point_out('(1,1)'::point))::varchar;
Thank's to help me...
David F
am Tue, dem 24.04.2007, um 13:13:50 +0200 mailte David Flegl folgendes: > Hi, > > I think, you can't do log the sql as text, but you can tools like > > 'tablelog' to obtain a similar behavior: > > thank's for inspiration. > > And another question. Is it possible to parse record type NEW? How could > one do something like (in trigger where new is set up): > text_var:=to_char(NEW) > So if NEW.at1='test' and NEW.at2=100 I will get string: 'test',100 > But the problem is then if trigger is generally used for many tables, I > don't know number of elements and so on. Not that i know, at least in plpgsql. Perhaps with languages like pl/perl or in plain C, but i'm not sure. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
"David Flegl" <ml_flegl@centrum.cz> writes:
> Or anyone has other possible solution how to solve this problem? Generaly: on specified tables I need to know (inside
PostgreSQL,not seeing log files in system) which command user run. This is because of I need to made of very simplified
asynchronnousmultimaster replication (better say synchronization).
I can pretty much guarantee that whatever you're building won't work.
There's no such thing as simple multimaster replication.
You'd be much better off to look into adopting one of the existing
solutions that someone else has already written and debugged.
regards, tom lane
>I can pretty much guarantee that whatever you're building won't work. >There's no such thing as simple multimaster replication. I know, but I don't tend to build something generally usable. I suppose to create it just for this small project. Very, veryspecific and tied to concrete tables, functions and so on. >You'd be much better off to look into adopting one of the existing >solutions that someone else has already written and debugged. I've already tried to look into any similiar solution, but only one suitable is pgcluster. Which is synchronous and in myprojects are many separated db's which must contain same content but, are not 100% connected via internet. DF