Thread: Storing old and new tuple values after an UPDATE, INSERT or DELETE

Storing old and new tuple values after an UPDATE, INSERT or DELETE

From
rmd22
Date:
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.

Re: Storing old and new tuple values after an UPDATE, INSERT or DELETE

From
Matthew Walden
Date:
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°

Re: Storing old and new tuple values after an UPDATE, INSERT or DELETE

From
rmd22
Date:
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.

Re: Re: Storing old and new tuple values after an UPDATE, INSERT or DELETE

From
David Fetter
Date:
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

Re: Storing old and new tuple values after an UPDATE, INSERT or DELETE

From
rmd22
Date:
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.

Re: Re: Storing old and new tuple values after an UPDATE, INSERT or DELETE

From
David Fetter
Date:
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