Is there any query by which I can get what are all rows that are
updated after a perticular time.

not sure anout what rows were updated but before you run the query run
a select with the same where clause as your update and that will tell
you all the rows that will be updated


chandan wrote:
> Is there any query by which I can get what are all rows that are
> updated after a perticular time.

its not the case that there are just 10 or 15 or 100 rows are there
there are millions of rows are there and we dont know what rows are
being updated with what values.

I tell you the real senario:

We are migrating our database, and there is a change in schema.
So the new table me have a column which was not there earlier.

After the migration will be done we bring the system live. Now if
anything goes wrong then we have to take the system to the previous
position with NEW DATA WHICH WAS INSERTED OR UPDATED AFTER THE
MIGRATION.

So we need to identified what are all rows which are updated or
inserted after the migration.

Thanks........
Chandan Sharma


scottishpoet wrote:
> not sure anout what rows were updated but before you run the query run
> a select with the same where clause as your update and that will tell
> you all the rows that will be updated
>
>
> chandan wrote:
> > Is there any query by which I can get what are all rows that are
> > updated after a perticular time.

If you have a serial ID it is easy to get the inserted (any serial
greater than the max when you migrate). If you add a trigger and
another table you can shadow the updates. Another simpler idea is to
add a column modtime datetime year to second default current. On insert
it will automatically get set because of the default. On update add a
trigger that updates the modtime field with the current time.

A special shortcut form of the trigger exists to upate fields in the
same table. It looks like this:

create table track_migration(
id serial,
val varchar(25) not null,
modtime datetime year to second default current year to second
) ;

create function mtime() returning datetime year to second;
return current;
end function ;

-- id can't be updated because it is a serial,
-- modtime is the field we are updating so it
-- should be excluded here.
create trigger trg_update_modtime update of val on track_migration
referencing old as o new as n
for each row
( execute procedure mtime() into modtime );

insert into track_migration(id,val) values (0,"First time");
select * from track_migration;

<wait for a while>

update track_migration set val="during migration" where 1 = 1;
select * from track_migration;

Now I have a question.

Is there a more elegant way to return the current time to a field than
creating a function and then calling it in the trigger.

I tried:

for each row ( current into modtime ) ; syntax error
for each row( let modtime = current ) ; syntax error
for each row( set modtime = current ) ; syntax error

for each row (extend(current, year to second) into modtime ) ; syntax
error

I didn't see anything posted or in the FM.



chandan wrote:
> its not the case that there are just 10 or 15 or 100 rows are there
> there are millions of rows are there and we dont know what rows are
> being updated with what values.
>
> I tell you the real senario:
>
> We are migrating our database, and there is a change in schema.
> So the new table me have a column which was not there earlier.
>
> After the migration will be done we bring the system live. Now if
> anything goes wrong then we have to take the system to the previous
> position with NEW DATA WHICH WAS INSERTED OR UPDATED AFTER THE
> MIGRATION.
>
> So we need to identified what are all rows which are updated or
> inserted after the migration.
>
> Thanks........
> Chandan Sharma
>
>
> scottishpoet wrote:
> > not sure anout what rows were updated but before you run the query run
> > a select with the same where clause as your update and that will tell
> > you all the rows that will be updated
> >
> >
> > chandan wrote:
> > > Is there any query by which I can get what are all rows that are
> > > updated after a perticular time.

> We are migrating our database, and there is a change in schema.
> So the new table me have a column which was not there earlier.
> After the migration will be done we bring the system live. Now if
> anything goes wrong then we have to take the system to the previous
> position with NEW DATA WHICH WAS INSERTED OR UPDATED AFTER THE
> MIGRATION.

in other words the only thing you do not need is the added
columns???!!!
right.??

in that case alter table ... drop ....

Superboer.

Yeah, what about doing a backup and then a restore.

Superboer wrote:
> > We are migrating our database, and there is a change in schema.
> > So the new table me have a column which was not there earlier.
> > After the migration will be done we bring the system live. Now if
> > anything goes wrong then we have to take the system to the previous
> > position with NEW DATA WHICH WAS INSERTED OR UPDATED AFTER THE
> > MIGRATION.
>
> in other words the only thing you do not need is the added
> columns???!!!
> right.??
>
> in that case alter table ... drop ....
>
> Superboer.

Superboer wrote:
>>We are migrating our database, and there is a change in schema.
>>So the new table me have a column which was not there earlier.
>>After the migration will be done we bring the system live. Now if
>>anything goes wrong then we have to take the system to the previous
>>position with NEW DATA WHICH WAS INSERTED OR UPDATED AFTER THE
>>MIGRATION.
>
>
> in other words the only thing you do not need is the added
> columns???!!!
> right.??
>
> in that case alter table ... drop ....

No, Chandan wants to know: "If the new DB has some problems after
implementation and we decide to fall back to the original DB, how can I
recover new rows and updates to existing rows from the new DB so I can
reapply them to the original without having to reload the entire large data
set?"

Bozon nailed it. A timestamp column with a DEFAULT CURRENT and UPDATE
trigger to restamp updated rows. This cannot trap deletes, but they are
easier to find then updates (obviously no harder than finding new rows
though). To locate deletes directly Chandan will need an audit table to
record the deleted key via a delete trigger.

Art S. Kagel

You don't happen to know a better way to get that timestamp for the
update trigger. It seems like a hassle to create a procedure that just
returns current and then calling it from the trigger.

Art S. Kagel wrote:
> Superboer wrote:
> >>We are migrating our database, and there is a change in schema.
> >>So the new table me have a column which was not there earlier.
> >>After the migration will be done we bring the system live. Now if
> >>anything goes wrong then we have to take the system to the previous
> >>position with NEW DATA WHICH WAS INSERTED OR UPDATED AFTER THE
> >>MIGRATION.
> >
> >
> > in other words the only thing you do not need is the added
> > columns???!!!
> > right.??
> >
> > in that case alter table ... drop ....
>
> No, Chandan wants to know: "If the new DB has some problems after
> implementation and we decide to fall back to the original DB, how can I
> recover new rows and updates to existing rows from the new DB so I can
> reapply them to the original without having to reload the entire large data
> set?"
>
> Bozon nailed it. A timestamp column with a DEFAULT CURRENT and UPDATE
> trigger to restamp updated rows. This cannot trap deletes, but they are
> easier to find then updates (obviously no harder than finding new rows
> though). To locate deletes directly Chandan will need an audit table to
> record the deleted key via a delete trigger.
>
> Art S. Kagel

bozon wrote:
> You don't happen to know a better way to get that timestamp for the
> update trigger. It seems like a hassle to create a procedure that just
> returns current and then calling it from the trigger.

Usually do as Doug C. does and perform an explicit update in the trigger.

Art

> Art S. Kagel wrote:
>
>>Superboer wrote:
>>
>>>>We are migrating our database, and there is a change in schema.
>>>>So the new table me have a column which was not there earlier.
>>>>After the migration will be done we bring the system live. Now if
>>>>anything goes wrong then we have to take the system to the previous
>>>>position with NEW DATA WHICH WAS INSERTED OR UPDATED AFTER THE
>>>>MIGRATION.
>>>
>>>
>>>in other words the only thing you do not need is the added
>>>columns???!!!
>>>right.??
>>>
>>>in that case alter table ... drop ....
>>
>>No, Chandan wants to know: "If the new DB has some problems after
>>implementation and we decide to fall back to the original DB, how can I
>>recover new rows and updates to existing rows from the new DB so I can
>>reapply them to the original without having to reload the entire large data
>>set?"
>>
>>Bozon nailed it. A timestamp column with a DEFAULT CURRENT and UPDATE
>>trigger to restamp updated rows. This cannot trap deletes, but they are
>>easier to find then updates (obviously no harder than finding new rows
>>though). To locate deletes directly Chandan will need an audit table to
>>record the deleted key via a delete trigger.
>>
>>Art S. Kagel
>
>

I wonder which is faster. I might benchmark this if I get a chance.

Art S. Kagel wrote:
> bozon wrote:
> > You don't happen to know a better way to get that timestamp for the
> > update trigger. It seems like a hassle to create a procedure that just
> > returns current and then calling it from the trigger.
>
> Usually do as Doug C. does and perform an explicit update in the trigger.
>
> Art
>
> > Art S. Kagel wrote:
> >
> >>Superboer wrote:
> >>
> >>>>We are migrating our database, and there is a change in schema.
> >>>>So the new table me have a column which was not there earlier.
> >>>>After the migration will be done we bring the system live. Now if
> >>>>anything goes wrong then we have to take the system to the previous
> >>>>position with NEW DATA WHICH WAS INSERTED OR UPDATED AFTER THE
> >>>>MIGRATION.
> >>>
> >>>
> >>>in other words the only thing you do not need is the added
> >>>columns???!!!
> >>>right.??
> >>>
> >>>in that case alter table ... drop ....
> >>
> >>No, Chandan wants to know: "If the new DB has some problems after
> >>implementation and we decide to fall back to the original DB, how can I
> >>recover new rows and updates to existing rows from the new DB so I can
> >>reapply them to the original without having to reload the entire large data
> >>set?"
> >>
> >>Bozon nailed it. A timestamp column with a DEFAULT CURRENT and UPDATE
> >>trigger to restamp updated rows. This cannot trap deletes, but they are
> >>easier to find then updates (obviously no harder than finding new rows
> >>though). To locate deletes directly Chandan will need an audit table to
> >>record the deleted key via a delete trigger.
> >>
> >>Art S. Kagel
> >
> >