Re: Need to update all my 60 million rows at once without transactional integrity

On Mon, Apr 21, 2008 at 3:49 AM, <christian_behrens(at)gmx(dot)net> wrote:

>
> Could I use that to hack my way around transactions?
>

Since you are asking for trouble, may there is something you can do
with Before UPDATE Triggers and heap_inplace_update(). Before you try
this out: I must say, *I have no idea if this would work in all
scenario and I don't take any guarantee of data consistency*. So do it
on your own risk :-) Obviously, transaction integrity and MVCC is
compromised. But I think crash recovery should work fine because
heap_inplace_update() takes care of WAL logging.

Write a BEFORE UPDATE trigger in C, something like this:

PG_FUNCTION_INFO_V1(inplace_update_trigger);
extern Datum inplace_update_trigger(PG_FUNCTION_ARGS);

Datum
inplace_update_trigger(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *)fcinfo->context;
trigdata->tg_newtuple->t_self = trigdata->tg_trigtuple->t_self;
heap_inplace_update(trigdata->tg_relation, trigdata->tg_newtuple);
return NULL;
}

CREATE OR REPLACE FUNCTION inplace_update_trigger()
RETURNS TRIGGER
AS 'trigger.so', 'inplace_update_trigger'
LANGUAGE C STRICT;

CREATE TRIGGER inplace_update_trigger BEFORE UPDATE ON <tblname>
FOR EACH ROW EXECUTE PROCEDURE inplace_update_trigger();

Now whenever you update a row in the table, the before update trigger
would update the old tuple in-place and return NULL. That would ensure
that the actual UPDATE operation is not performed, but the changes are
permanently recorded on the old tuple. In case of crash or transaction
abort, the updates can not be rolled back. Also, you may want to take
an exclusive lock on the relation before you start the update.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com