On Wed, Oct 26, 2016 at 12:43 PM, Merlin Moncure <mmonc...@gmail.com> wrote:
> On Wed, Oct 26, 2016 at 11:35 AM, Merlin Moncure <mmonc...@gmail.com> wrote:
>> On Tue, Oct 25, 2016 at 3:08 PM, Merlin Moncure <mmonc...@gmail.com> wrote:
>>> Confirmation of problem re-occurrence will come in a few days. I'm
>>> much more likely to believe 6+sigma occurrence (storage, freak bug,
>>> etc) should it prove the problem goes away post rebuild.
>>
>> ok, no major reported outage yet, but just got:
>>
>> 2016-10-26 11:27:55 CDT [postgres@castaging]: ERROR: invalid page in
>> block 12 of relation base/203883/1259
*) I've now strongly correlated this routine with the damage.
[root@rcdylsdbmpf001 ~]# cat
/var/lib/pgsql/9.5/data/pg_log/postgresql-26.log | grep -i
pushmarketsample | head -5
2016-10-26 11:26:27 CDT [postgres@castaging]: LOG: execute <unnamed>:
SELECT PushMarketSample($1::TEXT) AS published
2016-10-26 11:26:40 CDT [postgres@castaging]: LOG: execute <unnamed>:
SELECT PushMarketSample($1::TEXT) AS published
PL/pgSQL function pushmarketsample(text,date,integer) line 103 at SQL statement
PL/pgSQL function pushmarketsample(text,date,integer) line 103 at SQL statement
2016-10-26 11:26:42 CDT [postgres@castaging]: STATEMENT: SELECT
PushMarketSample($1::TEXT) AS published
*) First invocation was 11:26:27 CDT
*) Second invocation was 11:26:40 and gave checksum error (as noted
earlier 11:26:42)
*) Routine attached (if interested)
My next step is to set up test environment and jam this routine
aggressively to see what happens.
merlin
/*
* Send sample for a market to the pubs database. It will delete existing
* sample and replace it with whatever is determined to be the best available
* samble for each aparmtent in the market.
*
* XXX: assume 'asof' defines the period for which we are replacing data.
* Should that assumption be invalidated, we will need to accept an argument
* overriding the period.
*
* XXX: AsOf controls the extraction date but if a properites 'good' sample
* as after asof, no sample is pulled for the property.
*/
SELECT DropFunction('PushMarketSample');
CREATE OR REPLACE FUNCTION PushMarketSample(
_MarketID TEXT,
_AsOf DATE DEFAULT now()::DATE,
_MaxSampleDays INT DEFAULT 90,
Result OUT TEXT) RETURNS TEXT AS
$$
DECLARE
_OutputFile TEXT;
BEGIN
_OutputFile := format('/tmp/apartment_sample_%s_%s.sql',
_MarketId,
to_char(_AsOf, 'YYYYMMDD'));
DROP TABLE IF EXISTS AptSample;
CREATE TEMP TABLE AptSample ON COMMIT DROP AS
SELECT
ApartmentId,
FloorplanNumber,
msasubmkt AS SubmarketId,
(SELECT Period FROM DataEntryPeriod) AS Sampled,
Vacant,
DiscountedLowRent,
DiscountedHighRent,
LowRent,
HighRent,
LowDiscountRate,
HighDiscountRate,
Remarks, /* Is there a spot for remarks in MPF? */
TotalUnits,
UnitCount,
IncludeInAggregations,
Rent,
Occupancy
FROM vw_ApartmentSample aq
WHERE
Current
AND Sampled::DATE BETWEEN (_AsOf - _MaxSampleDays) AND _AsOf
AND _MarketID = msa
AND DataSource = 'Survey';
/* XXX: write out the sql script because we are stupidly prohibited from
* using pl/pgsql variables embedded into sql statements when using COPY.
*/
DROP TABLE IF EXISTS Script;
CREATE TEMP TABLE Script ON COMMIT DROP AS
SELECT 'begin transaction'
UNION ALL SELECT
format(
'DELETE tblAptDet '
'FROM tblAptDet d '
'JOIN tblApt a ON a.id = d.id '
'WHERE '
' a.msa = %s '
' AND d.Period = %s',
quote_literal(_MarketID),
(SELECT Period FROM DataEntryPeriod))
UNION ALL SELECT format(
'INSERT INTO tblaptdet('
'Id, PlanNumber, Period, Vacant, DLoRate, DHiRate, '
'FLoRate, FHiRate, LoDiscount, HiDiscount, Remarks, Quantity, '
'UpDtoHistory) '
'VALUES('
'%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
quote_nullable(ApartmentId),
quote_nullable(FloorplanNumber),
quote_nullable(Sampled),
quote_nullable(Vacant),
quote_nullable(DiscountedLowRent),
quote_nullable(DiscountedHighRent),
quote_nullable(LowRent),
quote_nullable(HighRent),
quote_nullable(LowDiscountRate),
quote_nullable(HighDiscountRate),
quote_nullable(Remarks),
quote_nullable(UnitCount),
CASE WHEN IncludeInAggregations THEN 1 ELSE 0 END)
FROM AptSample
UNION ALL SELECT 'commit transaction'
UNION ALL SELECT 'go';
EXECUTE 'COPY Script TO ' || quote_literal(_OutputFile);
/* push in the new sample! */
SELECT sqshf(ConnectTo('MPFUI'), _OutputFile) INTO Result;
PERFORM shexec('rm -f ' || _OutputFile);
DELETE FROM MarketPublishTracking WHERE
MarketId = _MarketID
AND Period =(SELECT Period FROM DataEntryPeriod);
DELETE FROM SubmarketPublishTracking WHERE
MarketId = _MarketID
AND Period = (SELECT Period FROM DataEntryPeriod);
DROP TABLE IF EXISTS ApartmentPublish;
CREATE TEMP TABLE ApartmentPublish ON COMMIT DROP AS
SELECT
ApartmentId,
TotalUnits,
SUM(Rent * UnitCount) / NULLIF(SUM(UnitCount), 0) AS Rent,
SUM(Occupancy * UnitCount) / NULLIF(SUM(UnitCount), 0) AS Occupancy
FROM AptSample
GROUP BY 1, 2;
DROP TABLE IF EXISTS ApartmentPublishBySubmkt;
CREATE TEMP TABLE ApartmentPublishBySubmkt ON COMMIT DROP AS
SELECT
ApartmentId,
TotalUnits,
SubmarketId,
SUM(Rent * UnitCount) / NULLIF(SUM(UnitCount), 0) AS Rent,
SUM(Occupancy * UnitCount) / NULLIF(SUM(UnitCount), 0) AS Occupancy
FROM AptSample
GROUP BY 1, 2, 3;
INSERT INTO MarketPublishTracking(Period, MarketId, PropertyCount, UnitCount)
SELECT
(SELECT Period FROM DataEntryPeriod),
_MarketId,
PropertyCount,
UnitCount
FROM
(
SELECT
SUM(TotalUnits) AS UnitCount,
COUNT(*) AS PropertyCount
FROM ApartmentPublish
) q;
INSERT INTO SubmarketPublishTracking(Period, MarketId, SubmarketId, PropertyCount, UnitCount)
SELECT
(SELECT Period FROM DataEntryPeriod),
_MarketId,
SubmarketId,
PropertyCount,
UnitCount
FROM
(
SELECT
SubmarketId,
SUM(TotalUnits) AS UnitCount,
COUNT(*) AS PropertyCount
FROM ApartmentPublishBySubmkt
GROUP BY 1
) q;
INSERT INTO MarketPublishApartmentTracking(Period, MarketId, ApartmentId,
TotalUnits, Rent, Occupancy)
SELECT
(SELECT Period FROM DataEntryPeriod),
_MarketId,
ApartmentId,
TotalUnits,
Rent,
Occupancy
FROM ApartmentPublish;
INSERT INTO SubmarketPublishApartmentTracking(Period, MarketId, SubmarketId, ApartmentId,
TotalUnits, Rent, Occupancy)
SELECT
(SELECT Period FROM DataEntryPeriod),
_MarketId::TEXT AS MarketId,
SubmarketId,
ApartmentId,
TotalUnits,
Rent,
Occupancy
FROM ApartmentPublishBySubmkt;
UPDATE MarketPublishTracking SET
AnnualRentGrowth = q.AnnualRentGrowth,
AnnualOccupancyGrowth = q.AnnualOccupancyGrowth,
QuarterlyRentGrowth = q.QuarterlyRentGrowth,
QuarterlyOccupancyGrowth = q.QuarterlyOccupancyGrowth,
AnnualUnitCount = q.AnnualUnitCount,
QuarterlyUnitCount = q.QuarterlyUnitCount,
AnnualPropertyCount = q.AnnualPropertyCount,
QuarterlyPropertyCount = q.QuarterlyPropertyCount
FROM
(
SELECT
(a.Rent / a.PrevRent::FLOAT8) - 1.0 AS AnnualRentGrowth,
(a.Occupancy - a.PrevOccupancy::FLOAT8) * 100 AS AnnualOccupancyGrowth,
(q.Rent / q.PrevRent::FLOAT8) - 1.0 AS QuarterlyRentGrowth,
(q.Occupancy - q.PrevOccupancy::FLOAT8) * 100 AS QuarterlyOccupancyGrowth,
a.PropertyCount AS AnnualPropertyCount,
q.PropertyCount AS QuarterlyPropertyCount,
a.UnitCount AS AnnualUnitCount,
q.UnitCount AS QuarterlyUnitCount
FROM
(
SELECT
SUM(cur.Rent * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS Rent,
SUM(prev.Rent * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS PrevRent,
SUM(cur.Occupancy * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS Occupancy,
SUM(prev.Occupancy * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS PrevOccupancy,
COUNT(*) AS PropertyCount,
SUM(cur.TotalUnits) AS UnitCount
FROM MarketPublishApartmentTracking cur
JOIN MarketPublishApartmentTracking prev ON
cur.MarketId = prev.MarketId
AND LastPeriodYear(cur.Period) = prev.Period
AND cur.ApartmentId = prev.ApartmentId
WHERE
cur.MarketId = _MarketId
AND cur.Period = (SELECT Period FROM DataEntryPeriod)
) a,
(
SELECT
SUM(cur.Rent * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS Rent,
SUM(prev.Rent * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS PrevRent,
SUM(cur.Occupancy * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS Occupancy,
SUM(prev.Occupancy * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS PrevOccupancy,
COUNT(*) AS PropertyCount,
SUM(cur.TotalUnits) AS UnitCount
FROM MarketPublishApartmentTracking cur
JOIN MarketPublishApartmentTracking prev ON
cur.MarketId = prev.MarketId
AND LastPeriod(cur.Period) = prev.Period
AND cur.ApartmentId = prev.ApartmentId
WHERE
cur.MarketId = _MarketId
AND cur.Period = (SELECT Period FROM DataEntryPeriod)
) q
) q
WHERE
Period = (SELECT Period FROM DataEntryPeriod)
AND MarketId = _MarketId;
UPDATE SubmarketPublishTracking SET
AnnualRentGrowth = q.AnnualRentGrowth,
AnnualOccupancyGrowth = q.AnnualOccupancyGrowth,
QuarterlyRentGrowth = q.QuarterlyRentGrowth,
QuarterlyOccupancyGrowth = q.QuarterlyOccupancyGrowth,
AnnualUnitCount = q.AnnualUnitCount,
QuarterlyUnitCount = q.QuarterlyUnitCount,
AnnualPropertyCount = q.AnnualPropertyCount,
QuarterlyPropertyCount = q.QuarterlyPropertyCount
FROM
(
SELECT
a.SubmarketId,
(a.Rent / a.PrevRent::FLOAT8) - 1.0 AS AnnualRentGrowth,
(a.Occupancy - a.PrevOccupancy::FLOAT8) * 100 AS AnnualOccupancyGrowth,
(q.Rent / q.PrevRent::FLOAT8) - 1.0 AS QuarterlyRentGrowth,
(q.Occupancy - q.PrevOccupancy::FLOAT8) * 100 AS QuarterlyOccupancyGrowth,
a.PropertyCount AS AnnualPropertyCount,
q.PropertyCount AS QuarterlyPropertyCount,
a.UnitCount AS AnnualUnitCount,
q.UnitCount AS QuarterlyUnitCount
FROM
(
SELECT
cur.SubmarketId,
SUM(cur.Rent * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS Rent,
SUM(prev.Rent * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS PrevRent,
SUM(cur.Occupancy * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS Occupancy,
SUM(prev.Occupancy * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS PrevOccupancy,
COUNT(*) AS PropertyCount,
SUM(cur.TotalUnits) AS UnitCount
FROM SubmarketPublishApartmentTracking cur
JOIN SubmarketPublishApartmentTracking prev ON
cur.MarketId = prev.MarketId
AND cur.SubmarketId = prev.SubmarketId
AND LastPeriodYear(cur.Period) = prev.Period
AND cur.ApartmentId = prev.ApartmentId
WHERE
cur.MarketId = _MarketId
AND cur.Period = (SELECT Period FROM DataEntryPeriod)
GROUP BY cur.SubmarketId
) a,
(
SELECT
cur.SubmarketId,
SUM(cur.Rent * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS Rent,
SUM(prev.Rent * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS PrevRent,
SUM(cur.Occupancy * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS Occupancy,
SUM(prev.Occupancy * cur.TotalUnits)
/ NULLIF(SUM(cur.TotalUnits), 0) AS PrevOccupancy,
COUNT(*) AS PropertyCount,
SUM(cur.TotalUnits) AS UnitCount
FROM SubmarketPublishApartmentTracking cur
JOIN SubmarketPublishApartmentTracking prev ON
cur.MarketId = prev.MarketId
AND cur.SubmarketId = prev.SubmarketId
AND LastPeriod(cur.Period) = prev.Period
AND cur.ApartmentId = prev.ApartmentId
WHERE
cur.MarketId = _MarketId
AND cur.Period = (SELECT Period FROM DataEntryPeriod)
GROUP BY cur.SubmarketId
) q
WHERE a.SubmarketId = q.SubmarketId
) q
WHERE
Period = (SELECT Period FROM DataEntryPeriod)
AND MarketId = _MarketId
AND SubmarketPublishTracking.SubmarketId = q.SubmarketId;
EXCEPTION
WHEN OTHERS THEN
PERFORM shexec('rm -f ' || _OutputFile);
RAISE;
END;
$$ LANGUAGE PLPGSQL;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
