The data misplacement is now fixed.
Over the past 2 days I have been moving the data that was accidentally
put in the master partition tables into their correct date partition
tables. I still need to clean up test_run, but I am pushing that to
this weekend since it will slow down the database for a few hours to
On Jan 9, 2008, at 4:27 PM, Ethan Mallove wrote:
> "pg_dump -s" seems to show that we are set until 2009?
> (Just put a note in my calendar about this for late December 2008 :-))
> On Wed, Jan/09/2008 04:07:01PM, Josh Hursey wrote:
>> I was showing MTT to someone today and noticed that it was
>> performing a
>> bit slower than it should. After taking a look under the hood I
>> discovered that we were missing the 2008 partition tables. :(
>> I'll keep you posted on this. Let me know if you have any problems in
>> the mean time.
>> -- Josh
>> Short Version:
>> We did not lose any data. The accumulated data was just put in a non-
>> optimal table, thus making queries slow. I added the 2008 partition
>> tables, and things should be back to normal. All new data will be
>> added correctly to the partition tables per usual.
>> There will be a lingering slowdown if anyone queries for results from
>> Jan 1, 2008 00:00 to ~Jan 9, 2008 15:15. I'm trying to fix this at
>> moment (see Long Version).
>> Long Version:
>> I totally forgot to upload the new tables to the database. Sorry
>> guys :(
>> All the data accumulated from the first of the year was put in the
>> main table for mpi_install/test_build/test_run instead of in their
>> respective date partitioned tables. This means that when someone
>> searches for something in the date range Jan 1, 2008 00:00 to ~Jan 9,
>> 2008 15:15 the database is going to do a bit of thrashing since the
>> optimizer is going to try to look to the partition table first then
>> failing that it will look at *all* the tables including the root
>> table. Luckily the optimizer seems to start with the root table so it
>> is not as bad as it could be, but still slower than it should be. :/
>> Currently this effects:
>> mpi_install: 434 tuples
>> test_build : 2,174 tuples
>> test_run : 1,077,117 tuples
>> I think I can fix this but I want to experiment a bit before
>> manipulating real data. I think I can create a transaction that does
>> something like:
>> Start Transaction
>> Drop check constraints on test_run
>> Save effected tuples to disk
>> Drop effected tuples from test_run
>> Add back effected tuples to test_run (inserting into partition
>> Add back check constraints
>> .. do the same for test_build, and mpi_install
>> End Transaction
> So you need to copy the data out of the root table into
> Something like the below does not work because it might collide with
> someone trying to INSERT into this week's partition table?
> SELECT * FROM mpi_install INTO mpi_install_y2008_m01_wk1;
> Could another option would be to turn off submit.php for
> a few minutes and do the above SELECT INTO?
>> Note for Josh: start_timestamp >= DATE '2008-01-01' and
>> start_timestamp < TIMESTAMP '2008-01-09 03:11'
>> mtt-devel mailing list
> mtt-devel mailing list