Thread: Storing old and new tuple values after an UPDATE, INSERT or DELETE
Is it possible to modify source code in executor to keep a log of old tuples and new tuples after an UPDATE, INSERT or DELETE command is executed? (so basically Store Old Tuple Values -> UPDATE/INSERT/DELETE Command -> Store New Tuple Values) For example is it possible to modify the execMain.c file in backend/executor/ such that before the ExecutorRun you save the tuples in a log file and after running ExecutorRun you log the new tuples also. How can this be achieved (if at all possible)? If it is not possible to do this by modifying execMain.c file how else can I do it? (may be nodeModifyTable.c) Please correct me if my understanding is wrong. I am new to Postgresql and to source code programming specially. I would really appreciate it a lot. Thank you in advance. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Storing-old-and-new-tuple-values-after-an-UPDATE-INSERT-or-DELETE-tp3269108p3269108.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
You can create a trigger on the table to store the old/new values in an audit table if that is what you mean?
Re: Storing old and new tuple values after an UPDATE, INSERT or DELETE
From
Andreas Kretschmer
Date:
Matthew Walden <matthew.walden.list@gmail.com> wrote: > You can create a trigger on the table to store the old/new values in an audit > table if that is what you mean? Or he can use tablelog: 16:36 < akretschmer|home> ??tablelog 16:36 < pg_docbot> For information about 'tablelog' see: 16:36 < pg_docbot> http://pgfoundry.org/projects/tablelog/ 16:36 < pg_docbot> http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Yes I have tried it with triggers but I have to do it without using triggers. Since in my workplace someone has already done that and for some reason (may be because triggers are expensive i suppose...not sure though), hence they want me to do it by modifying the source code. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Storing-old-and-new-tuple-values-after-an-UPDATE-INSERT-or-DELETE-tp3269108p3269213.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Wed, Nov 17, 2010 at 07:37:09AM -0800, rmd22 wrote: > Yes I have tried it with triggers but I have to do it without using > triggers. Since in my workplace someone has already done that and > for some reason (may be because triggers are expensive i > suppose...not sure though), hence they want me to do it by modifying > the source code. Don't go there. Instead, do this: 1. Get a stated reason, other than "I said so," for not using triggers. My top bet is that they'll claim a performance issue. 2. Decide on criteria for evaluating the claim, whatever it is. Make sure that the criteria are not insane. Insane criteria look like, "We can't take so much as a 0.001% performance hit anywhere in the system, no matter what it gets us in return." 3. Test the claim. At the end of this, you'll either have tablelog on its way to production, or a solid evidence that you need to find another gig. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Yes it's the "performance issue". I am going to talk to them about the triggers tomorrow. On the other hand I would still like to know if it is possible to do it by modifying the execMain.c or nodeModifyTable.c files? And also what about fast path interface? Is it possible to use it instead? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Storing-old-and-new-tuple-values-after-an-UPDATE-INSERT-or-DELETE-tp3269108p3269442.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Wed, Nov 17, 2010 at 09:17:39AM -0800, rmd22 wrote: > > Yes it's the "performance issue". > I am going to talk to them about the triggers tomorrow. On the > other hand I would still like to know if it is possible to do it by > modifying the execMain.c or nodeModifyTable.c files? Yes, but it's insanely risky, and an enormous amount of work that by asking that question you're showing you're not qualified to do. > And also what about fast path interface? Is it possible to use it > instead? Before you go anywhere like this, do some testing on things that work for the vast majority of people. :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate