[HACKERS] 8.4devel out of memory

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=142884.33..142932.05 rows=3181 width=13)
-> Hash Left Join (cost=25179.44..142868.43 rows=3181 width=13)
Hash Cond: (("*SELECT* 1"."matterNo")::text = ("M"."matterNo")::text)
Join Filter: ((("MH".date)::date <= (('1974-05-15'::date + generate_series(0, (('now'::text)::date - '1974-05-15'::date))))) AND (NOT (subplan)))
Filter: ((COALESCE(("MEC"."newStatusCode")::character varying, 'OP'::character varying))::text <> 'CL'::text)
-> Nested Loop (cost=529.05..66375.07 rows=126 width=49)
-> Nested Loop (cost=529.05..66339.68 rows=126 width=81)
Join Filter: ((("*SELECT* 1".date)::date <= (('1974-05-15'::date + generate_series(0, (('now'::text)::date - '1974-05-15'::date))))) AND (NOT (subplan)) AND (NOT (subplan)))
-> Result (cost=0.00..0.02 rows=1 width=0)
-> Hash Join (cost=529.05..26811.51 rows=1513 width=83)
Hash Cond: (("*SELECT* 1"."matterNo")::text = (s."matterNo")::text)
-> Append (cost=6.64..26033.63 rows=64090 width=70)
-> Subquery Scan "*SELECT* 1" (cost=6.64..25383.01 rows=36954 width=70)
-> Hash Join (cost=6.64..25013.47 rows=36954 width=135)
Hash Cond: (("MH"."matterEventCode")::text = ("MEC"."matterEventCode")::text)
-> Nested Loop (cost=0.57..23873.98 rows=105156 width=135)
-> Seq Scan on "Matter" "M" (cost=0.00..379.26 rows=27136 width=112)
Filter: (("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on "MatterHist" "MH" (cost=0.57..0.75 rows=8 width=23)
Recheck Cond: ((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text))
-> BitmapOr (cost=0.57..0.57 rows=8 width=0)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.28 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.28 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)
-> Hash (cost=4.37..4.37 rows=136 width=8)
-> Seq Scan on "MatterEventCode" "MEC" (cost=0.00..4.37 rows=136 width=8)
Filter: ("newStageCode" IS NOT NULL)
-> Subquery Scan "*SELECT* 2" (cost=0.00..650.62 rows=27136 width=70)
-> Seq Scan on "Matter" "M" (cost=0.00..379.26 rows=27136 width=112)
Filter: (("matterType")::text <> 'LT'::text)
-> Hash (cost=514.39..514.39 rows=642 width=13)
-> Nested Loop (cost=6.23..514.39 rows=642 width=13)
Join Filter: (((d."matterNo")::text = (s."litigationMatterNo")::text) OR ((s."litigationMatterNo" IS NULL) AND ((d."matterNo")::text = (s."matterNo")::text)))
-> Bitmap Heap Scan on "Matter" d (cost=5.68..49.10 rows=642 width=13)
Recheck Cond: (("matterStatusCode")::text = ANY (('{OP,RO}'::character varying[])::text[]))
-> Bitmap Index Scan on "Matter_MatterStatusCode" (cost=0.00..5.52 rows=642 width=0)
Index Cond: (("matterStatusCode")::text = ANY (('{OP,RO}'::character varying[])::text[]))
-> Bitmap Heap Scan on "Matter" s (cost=0.55..0.68 rows=3 width=26)
Recheck Cond: (((d."matterNo")::text = (s."litigationMatterNo")::text) OR ((d."matterNo")::text = (s."matterNo")::text))
-> BitmapOr (cost=0.55..0.55 rows=3 width=0)
-> Bitmap Index Scan on "Matter_LitigationMatterNo" (cost=0.00..0.27 rows=2 width=0)
Index Cond: ((d."matterNo")::text = (s."litigationMatterNo")::text)
-> Bitmap Index Scan on "Matter_pkey" (cost=0.00..0.27 rows=1 width=0)
Index Cond: ((d."matterNo")::text = (s."matterNo")::text)
SubPlan
-> Nested Loop (cost=0.76..24.15 rows=1 width=722)
-> Nested Loop (cost=0.76..23.86 rows=1 width=563)
Join Filter: (NOT (subplan))
-> Index Scan using "Matter_pkey" on "Matter" (cost=0.00..0.47 rows=1 width=26)
Index Cond: (("matterNo")::text = ($0)::text)
Filter: (("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on "MatterHist" (cost=0.76..1.66 rows=8 width=550)
Recheck Cond: (((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text) OR ((public."MatterHist"."matterNo")::text = (public."Matter"."litigationMatterNo")::text))
Filter: ((public."MatterHist".date)::date <= $1)
-> BitmapOr (cost=0.76..0.76 rows=8 width=0)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond: ((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond: ((public."MatterHist"."matterNo")::text = (public."Matter"."litigationMatterNo")::text)
SubPlan
-> Nested Loop (cost=0.76..2.70 rows=1 width=722)
-> Nested Loop (cost=0.76..2.41 rows=1 width=563)
Join Filter: (ROW((public."MatterHist".date)::date, CASE WHEN ((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text) THEN (public."MatterHist"."matterHistSeqNo")::integer ELSE ((public."MatterHist"."matterHistSeqNo")::smallint + 10000) END) > ROW(($31)::date, CASE WHEN (($32)::text = ($33)::text) THEN ($34)::integer ELSE (($34)::smallint + 10000) END))
-> Index Scan using "Matter_pkey" on "Matter" (cost=0.00..0.47 rows=1 width=26)
Index Cond: (("matterNo")::text = ($0)::text)
Filter: (("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on "MatterHist" (cost=0.76..1.66 rows=8 width=550)
Recheck Cond: (((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text) OR ((public."MatterHist"."matterNo")::text = (public."Matter"."litigationMatterNo")::text))
Filter: ((public."MatterHist".date)::date <= $1)
-> BitmapOr (cost=0.76..0.76 rows=8 width=0)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond: ((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond: ((public."MatterHist"."matterNo")::text = (public."Matter"."litigationMatterNo")::text)
-> Index Scan using "MatterEventCode_pkey" on "MatterEventCode" "MEC2" (cost=0.00..0.27 rows=1 width=159)
Index Cond: (("MEC2"."matterEventCode")::text = (public."MatterHist"."matterEventCode")::text)
Filter: (("MEC2"."removeMaintCode")::text = 'INA'::text)
-> Index Scan using "MatterEventCode_pkey" on "MatterEventCode" "MEC1" (cost=0.00..0.27 rows=1 width=159)
Index Cond: (("MEC1"."matterEventCode")::text = (public."MatterHist"."matterEventCode")::text)
Filter: (("MEC1"."newMaintCode")::text = 'INA'::text)
-> Result (cost=0.76..3.16 rows=2 width=359)
-> Append (cost=0.76..3.16 rows=2 width=359)
-> Nested Loop (cost=0.76..2.66 rows=1 width=135)
-> Nested Loop (cost=0.76..2.37 rows=1 width=135)
Join Filter: (ROW(("MH".date)::date, (CASE WHEN (("MH"."matterNo")::text = ("M"."matterNo")::text) THEN ("MH"."matterHistSeqNo")::integer ELSE (("MH"."matterHistSeqNo")::smallint + 10000) END)::smallint) > ROW(($2)::date, $3))
-> Index Scan using "Matter_pkey" on "Matter" "M" (cost=0.00..0.47 rows=1 width=112)
Index Cond: (("matterNo")::text = ($0)::text)
Filter: (("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on "MatterHist" "MH" (cost=0.76..1.66 rows=8 width=23)
Recheck Cond: ((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text))
Filter: (("MH".date)::date <= $1)
-> BitmapOr (cost=0.76..0.76 rows=8 width=0)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)
-> Index Scan using "MatterEventCode_pkey" on "MatterEventCode" "MEC" (cost=0.00..0.27 rows=1 width=8)
Index Cond: (("MEC"."matterEventCode")::text = ("MH"."matterEventCode")::text)
Filter: ("MEC"."newStageCode" IS NOT NULL)
-> Index Scan using "Matter_pkey" on "Matter" "M" (cost=0.00..0.48 rows=1 width=112)
Index Cond: (("matterNo")::text = ($0)::text)
Filter: ((("matterType")::text <> 'LT'::text) AND (("filedDate")::date <= $1) AND (ROW(("filedDate")::date, 0::smallint) > ROW(($2)::date, $3)))
-> Index Scan using "Matter_pkey" on "Matter" "L" (cost=0.00..0.27 rows=1 width=13)
Index Cond: (("L"."matterNo")::text = (COALESCE("*SELECT* 1"."litigationMatterNo", "*SELECT* 1"."matterNo"))::text)
-> Hash (cost=24269.98..24269.98 rows=30433 width=70)
-> Nested Loop (cost=7.26..23965.65 rows=30433 width=35)
-> Hash Join (cost=6.74..2200.73 rows=30394 width=22)
Hash Cond: (("MH"."matterEventCode")::text = ("MEC"."matterEventCode")::text)
-> Seq Scan on "MatterHist" "MH" (cost=0.00..1496.22 rows=105022 width=23)
-> Hash (cost=5.34..5.34 rows=112 width=7)
-> Seq Scan on "MatterEventCode" "MEC" (cost=0.00..5.34 rows=112 width=7)
Filter: (("newStatusCode" IS NOT NULL) AND (("newStatusCode")::text <> 'CT'::text))
-> Bitmap Heap Scan on "Matter" "M" (cost=0.52..0.66 rows=3 width=26)
Recheck Cond: ((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text))
Filter: (("M"."matterType")::text <> 'LT'::text)
-> BitmapOr (cost=0.52..0.52 rows=3 width=0)
-> Bitmap Index Scan on "Matter_pkey" (cost=0.00..0.26 rows=1 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text)
-> Bitmap Index Scan on "Matter_LitigationMatterNo" (cost=0.00..0.27 rows=2 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)
SubPlan
-> Nested Loop (cost=0.76..2.66 rows=1 width=35)
-> Nested Loop (cost=0.76..2.37 rows=1 width=36)
Join Filter: (ROW(("MH".date)::date, (CASE WHEN (("MH"."matterNo")::text = ("M"."matterNo")::text) THEN ("MH"."matterHistSeqNo")::integer ELSE (("MH"."matterHistSeqNo")::smallint + 10000) END)::smallint) > ROW(($25)::date, $26))
-> Index Scan using "Matter_pkey" on "Matter" "M" (cost=0.00..0.47 rows=1 width=26)
Index Cond: (("matterNo")::text = ($24)::text)
Filter: (("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on "MatterHist" "MH" (cost=0.76..1.66 rows=8 width=23)
Recheck Cond: ((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text))
Filter: (("MH".date)::date <= $1)
-> BitmapOr (cost=0.76..0.76 rows=8 width=0)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)
-> Index Scan using "MatterEventCode_pkey" on "MatterEventCode" "MEC" (cost=0.00..0.27 rows=1 width=7)
Index Cond: (("MEC"."matterEventCode")::text = ("MH"."matterEventCode")::text)
Filter: (("MEC"."newStatusCode" IS NOT NULL) AND (("MEC"."newStatusCode")::text <> 'CT'::text))
(139 rows)

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=180410.55..180495.64 rows=5673 width=13)
-> Hash Left Join (cost=52015.63..180382.18 rows=5673 width=13)
Hash Cond: (("*SELECT* 1"."matterNo")::text = ("M"."matterNo")::text)
Join Filter: ((("MH".date)::date <= (('1974-05-15'::date + generate_series(0, (('now'::text)::date - '1974-05-15'::date))))) AND (NOT (subplan)))
Filter: ((COALESCE(("MEC"."newStatusCode")::character varying, 'OP'::character varying))::text <> 'CL'::text)
-> Nested Loop (cost=27365.34..63496.22 rows=225 width=49)
-> Hash Anti Join (cost=27365.34..63433.02 rows=225 width=81)
Hash Cond: (("*SELECT* 1"."matterNo")::text = ("*SELECT* 1"."matterNo")::text)
Join Filter: ((("*SELECT* 1".date)::date <= (('1974-05-15'::date + generate_series(0, (('now'::text)::date - '1974-05-15'::date))))) AND (ROW(("*SELECT* 1".date)::date, "*SELECT* 1"."matterHistRowOrder") > ROW(("*SELECT* 1".date)::date, "*SELECT* 1"."matterHistRowOrder")))
-> Nested Loop (cost=530.51..34570.69 rows=253 width=87)
Join Filter: ((("*SELECT* 1".date)::date <= (('1974-05-15'::date + generate_series(0, (('now'::text)::date - '1974-05-15'::date))))) AND (NOT (subplan)))
-> Result (cost=0.00..0.02 rows=1 width=0)
-> Hash Join (cost=530.51..26813.09 rows=1518 width=83)
Hash Cond: (("*SELECT* 1"."matterNo")::text = (s."matterNo")::text)
-> Append (cost=6.64..26033.70 rows=64091 width=70)
-> Subquery Scan "*SELECT* 1" (cost=6.64..25383.06 rows=36955 width=70)
-> Hash Join (cost=6.64..25013.51 rows=36955 width=135)
Hash Cond: (("MH"."matterEventCode")::text = ("MEC"."matterEventCode")::text)
-> Nested Loop (cost=0.57..23874.00 rows=105159 width=135)
-> Seq Scan on "Matter" "M" (cost=0.00..379.28 rows=27136 width=112)
Filter: (("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on "MatterHist" "MH" (cost=0.57..0.75 rows=8 width=23)
Recheck Cond: ((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text))
-> BitmapOr (cost=0.57..0.57 rows=8 width=0)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.28 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.28 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)
-> Hash (cost=4.37..4.37 rows=136 width=8)
-> Seq Scan on "MatterEventCode" "MEC" (cost=0.00..4.37 rows=136 width=8)
Filter: ("newStageCode" IS NOT NULL)
-> Subquery Scan "*SELECT* 2" (cost=0.00..650.63 rows=27136 width=70)
-> Seq Scan on "Matter" "M" (cost=0.00..379.28 rows=27136 width=112)
Filter: (("matterType")::text <> 'LT'::text)
-> Hash (cost=515.82..515.82 rows=644 width=13)
-> Nested Loop (cost=6.24..515.82 rows=644 width=13)
Join Filter: (((d."matterNo")::text = (s."litigationMatterNo")::text) OR ((s."litigationMatterNo" IS NULL) AND ((d."matterNo")::text = (s."matterNo")::text)))
-> Bitmap Heap Scan on "Matter" d (cost=5.69..49.14 rows=644 width=13)
Recheck Cond: (("matterStatusCode")::text = ANY ('{OP,RO}'::text[]))
-> Bitmap Index Scan on "Matter_MatterStatusCode" (cost=0.00..5.53 rows=644 width=0)
Index Cond: (("matterStatusCode")::text = ANY ('{OP,RO}'::text[]))
-> Bitmap Heap Scan on "Matter" s (cost=0.55..0.68 rows=3 width=26)
Recheck Cond: (((d."matterNo")::text = (s."litigationMatterNo")::text) OR ((d."matterNo")::text = (s."matterNo")::text))
-> BitmapOr (cost=0.55..0.55 rows=3 width=0)
-> Bitmap Index Scan on "Matter_LitigationMatterNo" (cost=0.00..0.27 rows=2 width=0)
Index Cond: ((d."matterNo")::text = (s."litigationMatterNo")::text)
-> Bitmap Index Scan on "Matter_pkey" (cost=0.00..0.27 rows=1 width=0)
Index Cond: ((d."matterNo")::text = (s."matterNo")::text)
SubPlan
-> Nested Loop (cost=1.52..5.10 rows=1 width=0)
-> Nested Loop Anti Join (cost=1.52..4.82 rows=1 width=4)
Join Filter: (ROW((public."MatterHist".date)::date, CASE WHEN ((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text) THEN (public."MatterHist"."matterHistSeqNo")::integer ELSE ((public."MatterHist"."matterHistSeqNo")::smallint + 10000) END) > ROW((public."MatterHist".date)::date, CASE WHEN ((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text) THEN (public."MatterHist"."matterHistSeqNo")::integer ELSE ((public."MatterHist"."matterHistSeqNo")::smallint + 10000) END))
-> Nested Loop (cost=0.76..2.25 rows=1 width=36)
-> Index Scan using "Matter_pkey" on "Matter" (cost=0.00..0.47 rows=1 width=26)
Index Cond: (("matterNo")::text = ($4)::text)
Filter: (("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on "MatterHist" (cost=0.76..1.66 rows=8 width=23)
Recheck Cond: (((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text) OR ((public."MatterHist"."matterNo")::text = (public."Matter"."litigationMatterNo")::text))
Filter: ((public."MatterHist".date)::date <= $1)
-> BitmapOr (cost=0.76..0.76 rows=8 width=0)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond: ((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond: ((public."MatterHist"."matterNo")::text = (public."Matter"."litigationMatterNo")::text)
-> Nested Loop (cost=0.76..2.53 rows=1 width=32)
-> Nested Loop (cost=0.76..2.25 rows=1 width=36)
-> Index Scan using "Matter_pkey" on "Matter" (cost=0.00..0.47 rows=1 width=26)
Index Cond: (("matterNo")::text = ($4)::text)
Filter: (("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on "MatterHist" (cost=0.76..1.66 rows=8 width=23)
Recheck Cond: (((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text) OR ((public."MatterHist"."matterNo")::text = (public."Matter"."litigationMatterNo")::text))
Filter: ((public."MatterHist".date)::date <= $1)
-> BitmapOr (cost=0.76..0.76 rows=8 width=0)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond: ((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond: ((public."MatterHist"."matterNo")::text = (public."Matter"."litigationMatterNo")::text)
-> Index Scan using "MatterEventCode_pkey" on "MatterEventCode" "MEC2" (cost=0.00..0.27 rows=1 width=4)
Index Cond: (("MEC2"."matterEventCode")::text = (public."MatterHist"."matterEventCode")::text)
Filter: (("MEC2"."removeMaintCode")::text = 'INA'::text)
-> Index Scan using "MatterEventCode_pkey" on "MatterEventCode" "MEC1" (cost=0.00..0.27 rows=1 width=4)
Index Cond: (("MEC1"."matterEventCode")::text = (public."MatterHist"."matterEventCode")::text)
Filter: (("MEC1"."newMaintCode")::text = 'INA'::text)
-> Hash (cost=26033.70..26033.70 rows=64091 width=38)
-> Append (cost=6.64..26033.70 rows=64091 width=38)
-> Subquery Scan "*SELECT* 1" (cost=6.64..25383.06 rows=36955 width=38)
-> Hash Join (cost=6.64..25013.51 rows=36955 width=135)
Hash Cond: (("MH"."matterEventCode")::text = ("MEC"."matterEventCode")::text)
-> Nested Loop (cost=0.57..23874.00 rows=105159 width=135)
-> Seq Scan on "Matter" "M" (cost=0.00..379.28 rows=27136 width=112)
Filter: (("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on "MatterHist" "MH" (cost=0.57..0.75 rows=8 width=23)
Recheck Cond: ((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text))
-> BitmapOr (cost=0.57..0.57 rows=8 width=0)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.28 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.28 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)
-> Hash (cost=4.37..4.37 rows=136 width=8)
-> Seq Scan on "MatterEventCode" "MEC" (cost=0.00..4.37 rows=136 width=8)
Filter: ("newStageCode" IS NOT NULL)
-> Subquery Scan "*SELECT* 2" (cost=0.00..650.63 rows=27136 width=38)
-> Seq Scan on "Matter" "M" (cost=0.00..379.28 rows=27136 width=112)
Filter: (("matterType")::text <> 'LT'::text)
-> Index Scan using "Matter_pkey" on "Matter" "L" (cost=0.00..0.27 rows=1 width=13)
Index Cond: (("L"."matterNo")::text = (COALESCE("*SELECT* 1"."litigationMatterNo", "*SELECT* 1"."matterNo"))::text)
-> Hash (cost=24269.86..24269.86 rows=30434 width=70)
-> Nested Loop (cost=7.26..23965.52 rows=30434 width=35)
-> Hash Join (cost=6.74..2199.88 rows=30395 width=22)
Hash Cond: (("MH"."matterEventCode")::text = ("MEC"."matterEventCode")::text)
-> Seq Scan on "MatterHist" "MH" (cost=0.00..1495.35 rows=105025 width=23)
-> Hash (cost=5.34..5.34 rows=112 width=7)
-> Seq Scan on "MatterEventCode" "MEC" (cost=0.00..5.34 rows=112 width=7)
Filter: (("newStatusCode" IS NOT NULL) AND (("newStatusCode")::text <> 'CT'::text))
-> Bitmap Heap Scan on "Matter" "M" (cost=0.52..0.66 rows=3 width=26)
Recheck Cond: ((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text))
Filter: (("M"."matterType")::text <> 'LT'::text)
-> BitmapOr (cost=0.52..0.52 rows=3 width=0)
-> Bitmap Index Scan on "Matter_pkey" (cost=0.00..0.26 rows=1 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text)
-> Bitmap Index Scan on "Matter_LitigationMatterNo" (cost=0.00..0.27 rows=2 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)
SubPlan
-> Nested Loop (cost=0.76..2.65 rows=1 width=0)
-> Nested Loop (cost=0.76..2.37 rows=1 width=4)
Join Filter: (ROW(("MH".date)::date, (CASE WHEN (("MH"."matterNo")::text = ("M"."matterNo")::text) THEN ("MH"."matterHistSeqNo")::integer ELSE (("MH"."matterHistSeqNo")::smallint + 10000) END)::smallint) > ROW(($2)::date, $3))
-> Index Scan using "Matter_pkey" on "Matter" "M" (cost=0.00..0.47 rows=1 width=26)
Index Cond: (("matterNo")::text = ($0)::text)
Filter: (("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on "MatterHist" "MH" (cost=0.76..1.66 rows=8 width=23)
Recheck Cond: ((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text))
Filter: (("MH".date)::date <= $1)
-> BitmapOr (cost=0.76..0.76 rows=8 width=0)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text)
-> Bitmap Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)
-> Index Scan using "MatterEventCode_pkey" on "MatterEventCode" "MEC" (cost=0.00..0.27 rows=1 width=4)
Index Cond: (("MEC"."matterEventCode")::text = ("MH"."matterEventCode")::text)
Filter: (("MEC"."newStatusCode" IS NOT NULL) AND (("MEC"."newStatusCode")::text <> 'CT'::text))
(140 rows)

listen_addresses = '*'
port = 5512
max_connections = 200
shared_buffers = 256MB
temp_buffers = 10MB
max_prepared_transactions = 0
work_mem = 16MB
maintenance_work_mem = 400MB
max_fsm_pages = 1000000
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4.0
wal_buffers = 256kB
checkpoint_segments = 50
seq_page_cost = 0.1
random_page_cost = 0.1
effective_cache_size = 3GB
geqo = off
default_statistics_target = 100
from_collapse_limit = 20
join_collapse_limit = 20
logging_collector = on
log_connections = on
log_disconnections = on
log_line_prefix = '[%m] %p %q<%u %d %r> '
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 10
autovacuum_analyze_threshold = 10
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'
escape_string_warning = off
sql_inheritance = off
standard_conforming_strings = on
I was testing a very complex statistical query, with (among other
things) many EXISTS and NOT EXISTS tests against a build of the source
snapshot from 3 September. (The query looks pretty innocent, but
those aren't tables, they're complicated views.) Under 8.3.3 this
query runs successfully, but takes a few hours. I started it last
night before leaving, on the same machine where 8.3.3 has been
running, and in the morning found this:

olr=# explain analyze
SELECT
"MS"."sMatterNo",
CAST(COUNT(*) AS int) AS "count"
FROM
"MatterSearch" "MS"
JOIN "MatterDateStat" "S" ON
(
"S"."matterNo" = "MS"."sMatterNo" AND
"S"."isOnHold" = FALSE
)
WHERE
(
"MS"."matterStatusCode" IN ('OP', 'RO')
)
GROUP BY "MS"."sMatterNo"
;
ERROR: out of memory
DETAIL: Failed on request of size 8.

It was running for about half an hour before I left, and I didn't
notice the error, so I'm pretty sure it took longer than that for this
error to appear.

kgrittn@OLR-DEV-PG:~> df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 20G 8.0G 11G 43% /
tmpfs 2.0G 16K 2.0G 1% /dev/shm
/dev/sda3 253G 7.9G 245G 4% /var/pgsql/data
kgrittn@OLR-DEV-PG:~> free -m
total used free shared buffers
cached
Mem: 4049 2239 1809 0 94
1083
-/+ buffers/cache: 1061 2987
Swap: 1027 561 466

There are several development databases on this machine, all fairly
small, but enough that there's usually no significant free memory --
it gets used as cache. The 1.8 GB free this morning suggests that
something allocated and free a lot of memory.

kgrittn@OLR-DEV-PG:~/postgresql-snapshot> uname -a
Linux OLR-DEV-PG 2.6.5-7.286-bigsmp #1 SMP Thu May 31 10:12:58 UTC 2007
i686 i686 i386 GNU/Linux
kgrittn@OLR-DEV-PG:~/postgresql-snapshot> cat /proc/version
Linux version 2.6.5-7.286-bigsmp (geeko@buildhost) (gcc version 3.3.3
(SuSE Linux)) #1 SMP Thu May 31 10:12:58 UTC 2007
kgrittn@OLR-DEV-PG:~/postgresql-snapshot> cat /etc/SuSE-release
SUSE LINUX Enterprise Server 9 (i586)
VERSION = 9
PATCHLEVEL = 3

Attached are the plans from 8.3.3 and 8.4devel. Also attached are the
non-default 8.3.3 postgresql.conf settings; the file is the same for
8.4devel except for the port number. I don't know if the specifics of
the views and tables would be useful here, or just noise, so I'll omit
them unless someone asks for them.

What would be the reasonable next step here?

-Kevin

kgrittn@OLR-DEV-PG:~> /usr/local/pgsql-8.4dev/bin/pg_config
BINDIR = /usr/local/pgsql-8.4dev/bin
DOCDIR = /usr/local/pgsql-8.4dev/share/doc
HTMLDIR = /usr/local/pgsql-8.4dev/share/doc
INCLUDEDIR = /usr/local/pgsql-8.4dev/include
PKGINCLUDEDIR = /usr/local/pgsql-8.4dev/include
INCLUDEDIR-SERVER = /usr/local/pgsql-8.4dev/include/server
LIBDIR = /usr/local/pgsql-8.4dev/lib
PKGLIBDIR = /usr/local/pgsql-8.4dev/lib
LOCALEDIR = /usr/local/pgsql-8.4dev/share/locale
MANDIR = /usr/local/pgsql-8.4dev/share/man
SHAREDIR = /usr/local/pgsql-8.4dev/share
SYSCONFDIR = /usr/local/pgsql-8.4dev/etc
PGXS = /usr/local/pgsql-8.4dev/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/usr/local/pgsql-8.4dev'
'--enable-integer-datetimes' '--enable-debug' '--disable-nls'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels
-fno-strict-aliasing -g
CFLAGS_SL = -fpic
LDFLAGS = -Wl,-rpath,'/usr/local/pgsql-8.4dev/lib'
LDFLAGS_SL =
LIBS = -lpgport -lz -lreadline -lcrypt -ldl -lm
VERSION = PostgreSQL 8.4devel
kgrittn@OLR-DEV-PG:~> /usr/local/pgsql-8.4dev/bin/pg_controldata
/var/pgsql/data/kgrittn
pg_control version number: 842
Catalog version number: 200808311
Database system identifier: 5242286260647024629
Database cluster state: in production
pg_control last modified: Thu 04 Sep 2008 05:17:28 PM CDT
Latest checkpoint location: 0/26E7A718
Prior checkpoint location: 0/26E7A6D4
Latest checkpoint's REDO location: 0/26E7A718
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 0/3561
Latest checkpoint's NextOID: 49152
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Time of latest checkpoint: Thu 04 Sep 2008 05:17:28 PM CDT
Minimum recovery ending location: 0/0
Maximum data alignment: 4
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 2000
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by reference
Maximum length of locale name: 128
LC_COLLATE: C
LC_CTYPE: C

Re: [HACKERS] code coverage patch

Gregory Stark wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
>
> > I have uploaded an example run here:
> > http://developer.postgresql.org/~petere/coverage/
> >
> > Current test coverage is about 66% overall.
>
> With some pretty glaring gaps: 0% coverage of geqo, 0% coverage of logtape
> which implies no tuplesorts are spilling to disk, no coverage of mark/restore
> on index scans...

Yah, that kinda shocked me too. Clearly we should spend some effort to
expand the regression tests a bit.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Need more reviewers!

Hi,

Simon Riggs wrote:
> Such as?

Dunno. Rules for sponsors? It would probably make sense to not only pay
a single developer to create and submit a patch, but instead plan for
paying others to review the code as well.

> You might think those arguments exist and work, but I would say
> they manifestly do not.

Most managers - especially within software companies I'd say - are
pretty much aware of how costly quality assurance (or the lack thereof)
can be, no?

What do you respond to potential sponsors who request that a new feature
must be accepted into Postgres itself?

Let's tell *them* that review is costly. Encourage them to pay others to
review your work, for example. Let's coopete ;-) (or whatever the verb
for coopetition is)

Maybe we can do more WRT organizing this reviewing process, including
payment. Some sort of bounty system or something. Dunno, this is just
some brainstorming.

> Almost all people doing reviews are people that
> have considerable control over their own time, or are directed by people
> that understand the Postgres review process and wish to contribute to it
> for commercial reasons.

Sure. I don't quite get where you are going with this argument, sorry.

Regards

Markus Wanner

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [ADMIN] rpm install not recognized by yum.

On Thu, Sep 4, 2008 at 4:50 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, Sep 4, 2008 at 2:42 PM, slamp slamp <slackamp@gmail.com> wrote:
>> ok i managed to get this to work. however i still get the "Repository
>> pgdg82 is listed more than once in the configuration", this is
>> probably a rhel yum bug.
>
> Sure you don't have it again in an included yum .conf file? That's
> happened to me before.
>

im pretty sure, i even uninstalled the repo rpm and added it manually
to yum.conf and it gave the same message. centos does not seem to have
this issue.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [PERFORM] SAN and full_page_writes

I seem to have answered my own question.  I'm sending the answer to the list in case someone else has the same question one day.

According to the NetApp documentation, it does protect me from partial page writes.  Thus, full_page_writes = off.

On Wed, Sep 3, 2008 at 12:03 PM, Nikolas Everett <nik9000@gmail.com> wrote:

I have the honor to be configuring Postgres to back into a NetApp FAS3020 via fiber.

Does anyone know if the SAN protects me from breakage due to partial page writes?

If anyone has an SAN specific postgres knowledge, I'd love to hear your words of wisdom.

For reference:
[postgres@localhost bonnie]$ ~neverett/bonnie++-1.03a/bonnie++
Writing with putc()...done
Writing intelligently...done
Rewriting...done
Reading with getc()...done
Reading intelligently...done
start 'em...done...done...done...
Create files in sequential order...done.
Stat files in sequential order...done.
Delete files in sequential order...done.
Create files in random order...done.
Stat files in random order...done.
Delete files in random order...done.
Version  1.03       ------Sequential Output------ --Sequential Input- --Random-
                    -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
localhost.lo 32104M 81299  94 149848  30 42747   8 45465  61 55528   4 495.5   0
                    ------Sequential Create------ --------Random Create--------
                    -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP
                 16 +++++ +++ +++++ +++ +++++ +++ +++++ +++ +++++ +++ +++++ +++

[HACKERS] Synchronous Log Shipping Replication

Hi,

In PGCon 2008, I proposed synchronous log shipping replication.
Sorry for late posting, but I'd like to start the discussion
about its implementation from now.
http://www.pgcon.org/2008/schedule/track/Horizontal%20Scaling/76.en.html

First of all, I'm not planning to put the prototype which I demoed
in PGCon into core directly.

- Portability issues (using message queue, multi-threaded ...)
- Have too much dependency on Heartbeat

Yes, since the prototype is useful reference of implementation,
I plan to open it ASAP. But, I'm sorry - it still takes a month
to open it.

Pavan re-designed the sync replication based on the prototype
and I posted that design doc on wiki. Please check it if you
are interested in it.
http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects

This design is too huge. In order to enhance the extensibility
of postgres, I'd like to divide the sync replication into
minimum hooks and some plugins and to develop it, respectively.
Plugins for the sync replication plan to be available at the
time of 8.4 release.

In my design, WAL sending is achieved as follow by WALSender.
WALSender is a new process which I introduce.

1) On COMMIT, backend requests WALSender to send WAL.
2) WALSender reads WAL from walbuffers and send it to slave.
3) WALSender waits for the response from slave and replies
backend.

I propose two hooks for WAL sending.

WAL-writing hook
----------------
This hook is for backend to communicate with WALSender.
WAL-writing hook intercepts write system call in XLogWrite.
That is, backend requests WAL sending whenever write is called.

WAL-writing hook is available also for other uses e.g.
Software RAID (writes WAL into two files for durability).

Hook for WALSender
------------------
This hook is for introducing WALSender. There are the following
three ideas of how to introduce WALSender. A required hook
differs by which idea is adopted.

a) Use WALWriter as WALSender

This idea needs WALWriter hook which intercepts WALWriter
literally. WALWriter stops the local WAL write and focuses on
WAL sending. This idea is very simple, but I don't think of
the use of WALWriter hook other than WAL sending.

b) Use new background process as WALSender

This idea needs background-process hook which enables users
to define new background processes. I think the design of this
hook resembles that of rmgr hook proposed by Simon. I define
the table like RmgrTable. It's for registering some functions
(e.g. main function and exit...) for operating a background
process. Postmaster calls the function from the table suitably,
and manages a start and end of background process. ISTM that
there are many uses in this hook, e.g. performance monitoring
process like statspack.

c) Use one backend as WALSender

In this idea, slave calls the user-defined function which
takes charge of WAL sending via SQL e.g. "SELECT pg_walsender()".
Compared with other ideas, it's easy to implement WALSender
because postmater handles the establishment and authentication
of connection. But, this SQL causes a long transaction which
prevents vacuum. So, this idea needs idle-state hook which
executes plugin before transaction starts. I don't think of
the use of this hook other than WAL sending either.

Which idea should we adopt?

Comments welcome.

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Need more reviewers!

On 9/4/08, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Thu, 2008-09-04 at 10:45 -0700, Josh Berkus wrote:
> > We currently have 38 patches pending, and only nine people reviewing them.
> > At this rate, the September commitfest will take three months.
> >
> > If you are a postgresql hacker at all, or even want to be one, we need your
> > help reviewing patches! There are several "easy" patches in the list, so
> > I can assign them to beginners.
> >
> > Please volunteer now!
>
>
> Everybody is stuck in "I'm not good enough to do a full review". They're
> right (myself included), so that just means we're organising it wrongly.
> We can't expect to grow more supermen, but we probably can do more
> teamwork and delegation.
>
> I think this should be organised with different kinds of reviewer:

The list is correct but too verbose. And it does not attack the core
of the problem. I think the problem is not:

What can/should I do?

but instead:

Can I take the responsibility?

Lets say reviewer would like look on coding style or performance.
ATM it seems to him he well be now fully responsible for that aspect.

I think we have better results and more relaxed atmospere if we
use following task description for reviewers:

The committer will do in-depth review. You task as a reviewer
is to take off load from committers by catching simple problems.
Your task is done if you think the patch is ready for in-depth
review from committer.

Note1 - Yes, the trick is to emphasize that all responsibility
lies on committer.

Note2 - detailed lists of areas to look at and reviewer types are not
useful as each patch is different and each revier is different.
Long lists just confuse people. The simpler the better.

The main thing is to make easy for reviewer to take the first look.

--
marko

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Need more reviewers!

On Fri, 2008-09-05 at 16:03 +0200, Markus Wanner wrote:

> > I don't *want* the rule, I just think we *need* the rule because
> > otherwise sponsors/managers/etc make business decisions to exclude that
> > aspect of the software dev process.
>
> I agree that making sponsors/managers/etc aware of that aspect of the
> dev process is necessary and worthwhile. However, I don't think a rule
> for *patch submitters* helps with that. There must be other ways to
> convince managers to encourage reviewers.

Such as? You might think those arguments exist and work, but I would say
they manifestly do not. Almost all people doing reviews are people that
have considerable control over their own time, or are directed by people
that understand the Postgres review process and wish to contribute to it
for commercial reasons.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[COMMITTERS] npgsql - Npgsql2: Fixed an improperly added finally block to handle

Log Message:
-----------

Fixed an improperly added finally block to handle closeconnection commandbehavior. By closing the connection in a finally block, I broke the fetch data on demand. Now, I only close the connection when there is an exception.

Thanks Christian Holzner (support at tuga dot it) for heads up and patch.

Modified Files:
--------------
Npgsql2/src/Npgsql:
NpgsqlCommand.cs (r1.27 -> r1.28)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/npgsql/Npgsql2/src/Npgsql/NpgsqlCommand.cs.diff?r1=1.27&r2=1.28)

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

Re: [HACKERS] code coverage patch

Peter Eisentraut <peter_e@gmx.net> writes:

> I have uploaded an example run here:
> http://developer.postgresql.org/~petere/coverage/
>
> Current test coverage is about 66% overall.

With some pretty glaring gaps: 0% coverage of geqo, 0% coverage of logtape
which implies no tuplesorts are spilling to disk, no coverage of mark/restore
on index scans...

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] PostgreSQL SSL problem

Hi Bruce and Team,

I have problems to setup SSL for PostgreSQL server. I did all the steps
which described in the documentation (17.8. Secure TCP/IP Connections
with SSL), but when I try to start the PostgreSQL server the pg_ctl gave
me: "could not start server". And nothing in the logs (I enabled all of
them). I googled around but did not find much.

After I disable SSL option in postgresql.conf the server is starting
successfully.

I have all certificates with proper CA signature, rest of applications
(Postfix, Apache, etc.) work with this certificates very well. I am
using OpenSSL from ports.

Please, advise.

My spec:

FreeBSD 7.0-RELEASE-p3 amd64

PostgreSQL 8.3.3 (installed from ports):

WITH_NLS=true
WITHOUT_PAM=true
WITHOUT_LDAP=true
WITHOUT_MIT_KRB5=true
WITHOUT_HEIMDAL_KRB5=true
WITHOUT_OPTIMIZED_CFLAGS=true
WITH_XML=true
WITHOUT_TZDATA=true
WITHOUT_DEBUG=true
WITH_ICU=true
WITH_INTDATE=true

$ pg_config
BINDIR = /usr/local/bin
DOCDIR = /usr/local/share/doc/postgresql
INCLUDEDIR = /usr/local/include
PKGINCLUDEDIR = /usr/local/include/postgresql
INCLUDEDIR-SERVER = /usr/local/include/postgresql/server
LIBDIR = /usr/local/lib
PKGLIBDIR = /usr/local/lib/postgresql
LOCALEDIR = /usr/local/share/locale
MANDIR = /usr/local/man
SHAREDIR = /usr/local/share/postgresql
SYSCONFDIR = /usr/local/etc/postgresql
PGXS = /usr/local/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--with-libraries=/usr/local/lib'
'--with-includes=/usr/local/include' '--enable-thread-safety'
'--with-docdir=/usr/local/share/doc/postgresql' '--with-openssl'
'--with-system-tzdata=/usr/share/zoneinfo' '--enable-integer-datetimes'
'--enable-nls' '--prefix=/usr/local' '--mandir=/usr/local/man'
'--infodir=/usr/local/info/' '--build=amd64-portbld-freebsd7.0' 'CC=cc'
'CFLAGS=-O2 -fno-strict-aliasing -pipe ' 'LDFLAGS= -pthread
-rpath=/usr/local/lib' 'build_alias=amd64-portbld-freebsd7.0'
CC = cc
CPPFLAGS = -I/usr/local/include
CFLAGS = -O2 -fno-strict-aliasing -pipe -Wall -Wmissing-prototypes
-Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels
-fno-strict-aliasing -fwrapv
CFLAGS_SL = -fPIC -DPIC
LDFLAGS = -pthread -rpath=/usr/local/lib -L/usr/local/lib
-Wl,-R'/usr/local/lib'
LDFLAGS_SL =
LIBS = -lpgport -lintl -lssl -lcrypto -lz -lreadline -lcrypt -lm
VERSION = PostgreSQL 8.3.3

Thanks,
Andriy

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Need more reviewers!

Hi,

Simon Riggs wrote:
> On Fri, 2008-09-05 at 09:19 -0400, Andrew Dunstan wrote:
>> All this would do is to deter people from submitting patches. Hard rules
>> like this don't work in FOSS communities. I know it's like herding cats,
>> but persuasion is really our only tool.

+1

> I don't *want* the rule, I just think we *need* the rule because
> otherwise sponsors/managers/etc make business decisions to exclude that
> aspect of the software dev process.

I agree that making sponsors/managers/etc aware of that aspect of the
dev process is necessary and worthwhile. However, I don't think a rule
for *patch submitters* helps with that. There must be other ways to
convince managers to encourage reviewers.

Regards

Markus Wanner

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] libpq events update

Andrew Chernow wrote:
>
> I think it got confused with the instanceData feature, which has nothing
> to do with the event system and requires public functions. libpqtypes
> happens to use the instanceData functions within its eventproc, but this
> is not a requirement.
>

I forgot to mention that the instanceData functions should be moved from
libpq-events.h to libpq-fe.h because they are not part of the event
system. I plan on making this change as well, so let me know if you
hate it.

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Need more reviewers!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> I don't *want* the rule, I just think we *need* the rule because
> otherwise sponsors/managers/etc make business decisions to exclude that
> aspect of the software dev process.

How exactly would you even begin to enforce such a rule? Retroactively
pull otherwise vali patches from the queue? Ban people from sending
email to the -patches list?

> Otherwise we have a patch-and-dump culture that is unsustainable because
> a few people's benevolence as reviewers turns everything into a
> bottleneck. It doesn't need to mean loss of control for core and
> committers.

That problem needs a solution, but not the one you proposed.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200809050953
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkjBOdIACgkQvJuQZxSWSsiFoACgoqOgumuuZq6z2HBPSAPZUWHd
kS0An2TgFmOLTgdFWuLkpazFbECY4nnz
=ZrYl
-----END PGP SIGNATURE-----

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [ADMIN] change max_value in sequence

On Fri, Sep 5, 2008 at 6:58 AM, Claus Guttesen <kometen@gmail.com> wrote:
> Hi.
>
> I have a table with a serial field defined with an older version of
> postgresql (ver. 7). Back then max_value was 2147483647:
>
> select max_value from my_bid_seq ;
> max_value
> ------------
> 2147483647
>
> How can I increase it? By updating the max_value-field? This is an
> older thread and may have changed:
> http://archives.postgresql.org/pgsql-admin/2002-12/msg00033.php

That's the max for a regular int / serial type. You'll need to change
it to bigserial / bigint which is 64 bits/

>
> I'm running postgresql 8.3.1.

Please update to 8.3.3

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[HACKERS] libpq events update

I would like to remove the PQpassThroughData and PQresultPassThroughData
functions. The passThrough pointer should be added as a 3rd argument
to the PGEventProc:

typedef int (*PGEventProc)(PGEventId evtId, void *evtInfo,
void *passThrough);

Having a public accessor function for the passThrough. doesn't seem
helpful. Its purpose is to be available to the eventproc, which doesn't
require a public function.

I think it got confused with the instanceData feature, which has nothing
to do with the event system and requires public functions. libpqtypes
happens to use the instanceData functions within its eventproc, but this
is not a requirement.

All those who oppose any of the above, speak now or forever hold your
peace. An updated patch with full sgml documentation is coming.

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_regress inputdir

Jorgen Austvik - Sun Norway wrote:
> Alvaro Herrera wrote:
>> In my opinion, the need
>> for running tests outside the test dir is not very strong (or we would
>> have heard complaints before), and thus the solution is to remove
>> --inputdir and --outputdir.
>
> Attached is a patch that removes --inputdir and --outputdir. I still
> prefere the first patch (that fixed my problem), but removing them is
> probably better than having them when they don't work.

There is interest among packagers to run the regression tests or other
tests after the build. The Red Hat RPMs have shipped a postgresql-test
package for years with a hacked-up makefile that will probably overwrite
random files that it shouldn't in /usr/lib. So I would rather be in
favor of coming up with a solution that would make this work rather than
removing the options. The solution would probably be adding another
option to place the generated files, but the exact behavior would need
to be worked out.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Need more reviewers!

On Fri, 2008-09-05 at 09:19 -0400, Andrew Dunstan wrote:
>
> Simon Riggs wrote:
> > On Thu, 2008-09-04 at 10:45 -0700, Josh Berkus wrote:
> >
> >
> >> If you are a postgresql hacker at all, or even want to be one, we need your
> >> help reviewing patches! There are several "easy" patches in the list, so
> >> I can assign them to beginners.
> >>
> >
> > It would be a reasonable rule that all patch submitters also have to do
> > patch reviews. If we made it a strict rule, then sponsoring companies
> > would know that they *must* provide money/time for that aspect also.
> > Otherwise it is almost impossible to get formal approval to do that.

> All this would do is to deter people from submitting patches. Hard rules
> like this don't work in FOSS communities. I know it's like herding cats,
> but persuasion is really our only tool.

I don't *want* the rule, I just think we *need* the rule because
otherwise sponsors/managers/etc make business decisions to exclude that
aspect of the software dev process.

Otherwise we have a patch-and-dump culture that is unsustainable because
a few people's benevolence as reviewers turns everything into a
bottleneck. It doesn't need to mean loss of control for core and
committers.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[COMMITTERS] stackbuilder - wizard: Improve error message handling for applist and

Log Message:
-----------
Improve error message handling for applist and mirror list downloads

Modified Files:
--------------
wizard:
App.cpp (r1.32 -> r1.33)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/stackbuilder/wizard/App.cpp.diff?r1=1.32&r2=1.33)
AppList.cpp (r1.17 -> r1.18)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/stackbuilder/wizard/AppList.cpp.diff?r1=1.17&r2=1.18)
MirrorList.cpp (r1.9 -> r1.10)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/stackbuilder/wizard/MirrorList.cpp.diff?r1=1.9&r2=1.10)

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

Re: [HACKERS] Extending grant insert on tables to sequences

On Wed, Sep 3, 2008 at 7:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> In short, this patch isn't much more ready to commit than it was
> in the last fest.
>

Just for the record, i put this updated patch just because there were
an entry for "Extending grant insert on tables to sequences" for this
Commit Fest without being an updated patch

--
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. (593) 87171157

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] StartupCLOG

On Thu, 2008-09-04 at 12:18 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
> > I was thinking about what happens when you are performing a PITR using
> > log records that contain a crash/recovery/shutdown checkpoint sequence.
>
> > I take it there's no problem there?
>
> I don't really see one.

OK, cool. I'm just trying to shake out all the possible problems, so
sorry if this one was a false positive.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PERFORM] limit clause breaks query planner?

On Thu, 4 Sep 2008, Guillaume Cottenceau wrote:
> It seems to me that if the correlation is 0.99, and you're
> looking for less than 1% of rows, the expected rows may be at the
> beginning or at the end of the heap?

Not necessarily. Imagine for example that you have a table with 1M rows,
and one of the fields has unique values from 1 to 1M, and the rows are
ordered in the table by that field. So the correlation would be 1. If you
were to SELECT from the table WHERE the field = 500000 LIMIT 1, then the
database should be able to work out that the rows will be right in the
middle of the table, not at the beginning or end. It should set the
startup cost of a sequential scan to the amount of time required to
sequential scan half of the table.

Of course, this does bring up a point - if the matching rows are
concentrated at the end of the table, the database could perform a
sequential scan backwards, or even a scan from the middle of the table
onwards.

This improvement of course only actually helps if the query has a LIMIT
clause, and presumably would muck up simultaneous sequential scans.

Matthew

--
Picard: I was just paid a visit from Q.
Riker: Q! Any idea what he's up to?
Picard: No. He said he wanted to be "nice" to me.
Riker: I'll alert the crew.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [HACKERS] StartupCLOG

Simon Riggs <simon@2ndQuadrant.com> writes:
> I was thinking about what happens when you are performing a PITR using
> log records that contain a crash/recovery/shutdown checkpoint sequence.

> I take it there's no problem there?

I don't really see one. I believe the reason for the StartupCLOG action
is just to make sure that clog doesn't claim that any transactions are
committed that weren't committed according to the WAL, or more precisely
by the portion of WAL we chose to read. Consider PITR stopping short of
the actual WAL end: it would clearly be possible that the current page
of clog says that some "future" transactions are committed, but in our
new database history we don't want them to be so. I think that the code
is also trying to guard against a similar situation in a crash where WAL
has been damaged and can't be read all the way to the end.

Since the PITR slave isn't going to make any changes to clog in the
first place that it isn't told to by WAL, it's hard to see how any
divergence would arise. It could diverge when the slave stops slaving
and goes live, but at that point it's going to do StartupCLOG itself.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [pgsql-es-ayuda] default current_time

2008/9/3 José Fermín Francisco Ferreras <josefermin54@hotmail.com>:
>
> Donde se ubica el archivo q se genera de log_destination=evenlog, para poder decirte.
>

log_destination=eventlog
(te falta una t)

busca el resultado del log en panel de control -> herramientas
administrativas -> visor de sucesos

> A proposito, se usaria comillas dobles o sencillas en timezone = 'gmt+4' o timezone = "gmt+4"??
>

yo lo tengo asi:
timezone = 'GMT+5'

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. (593) 87171157
--
TIP 4: No hagas 'kill -9' a postmaster

Re: [PERFORM] limit clause breaks query planner?

Matthew Wakeling <matthew 'at' flymine.org> writes:

> On Thu, 4 Sep 2008, Tom Lane wrote:
>> Ultimately the only way that we could get the right answer would be if
>> the planner realized that the required rows are concentrated at the end
>> of the table instead of being randomly scattered. This isn't something
>> that is considered at all right now in seqscan cost estimates. I'm not
>> sure offhand whether the existing correlation stats would be of use for
>> it, or whether we'd have to get ANALYZE to gather additional data.
>
> Using the correlation would help, I think, although it may not be the
> best solution possible. At least, if the correlation is zero, you
> could behave as currently, and if the correlation is 1, then you know
> (from the histogram) where in the table the values are.

It seems to me that if the correlation is 0.99[1], and you're
looking for less than 1% of rows, the expected rows may be at the
beginning or at the end of the heap?

Ref:
[1] or even 1, as ANALYZE doesn't sample all the rows?

--
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[ADMIN] Help!

 Hi! I have bought a WS 444 PC Weather Station.
In order to use the device, a software called WeatherProfessional was
included in the package.
That software uses the postgresql service. I also got the PostgreSQL 8.0
with the package. My problem is that I cannot get it to work properly.
Firstly, if I'm not mistaken, the 8.0 version does'nt run on Windows XP.
Therefor I tried to download the 8.3.3 version off of your site. I did not
have any problems installing that, but then when I tried to run the
WeatherProfessional, it said I had the wrong username or password. There are
no such information to find anywhere in the package i bought, it simply said
that the installation would pretty much install itself, and no such
information was needed for the install.
What will I have to do in order to get this working?
 
Thank you!
Christian Larsen
larsen7557@hotmail.com

---

Kolla på video med dina Messenger-polare! Messenger TV

Re: [GENERAL] Changes for version 8.4

On Thu, 2008-09-04 at 10:45 -0400, Alvaro Herrera wrote:
> Joao Ferreira gmail escribió:
> > Is there a date for the release of 8.4 ?
>
> http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Development_Plan

/me notes that noone responded like "It will be released when it is
ready".

--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

Re: [GENERAL] You need to rebuild PostgreSQL using --with-libxml.

Hi,

On Thu, 2008-09-04 at 10:18 -0430, Ricardo Antonio Yepez Jimenez wrote:
> Buenos Dias, necesito saber los pasos para recompilar con soporte
> para
> xml, en redhat 4 entreprise y postgres 8.3.

You cannot compile PostgreSQL 8.3 on RHEL 4 with xml support -- unless
you install libxml2 from sources. RHEL ships 2.6.16 version of libxml,
but PostgreSQL requires 2.6.23 at least.

Regards,
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

Re: [HACKERS] Debugging methods

M2Y <mailtoyahoo@gmail.com> writes:
> I am a beginner to Postgres and I am going through code. I would like
> to know the debugging methods used in development.

> Some of my requirements are; for a given query, how parse structures
> are created in pg_parse_query, how they are analyzed and rewritten in
> pg_analyze_and_rewrite and how the final plan is created in
> pg_plan_queries.

What I tend to do when trying to debug those areas is to set breakpoints
at interesting places with gdb, and then use commands like
"call pprint(node_pointer)" to dump the contents of specific parse or
plan trees to the postmaster log. The reason that outfuncs.c supports
so many node types (many that can't ever appear in stored rules) is
exactly to make it useful for examining internal data structures this
way.

Another possibility is to turn on debug_print_plan and so on, but those
settings only show you the finished results of parsing or planning,
which isn't real helpful for understanding how the code gets from point
A to point B.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PERFORM] Partitions number limitation ?

s.caillet@free.fr wrote:
> Is there some kind of limit in postgresql about the number of partitions ? Do
> you know some tuning in the conf files to improve postgresql management of so
> many tables ? I have already used different tablespaces, one for each main table
> and its 288 partitions.

Postgres is not really designed for performance of partitions, so you
have to manage that yourself. I am working on a project with a similar
design and found that the super table has its limitations. At some point
the db just aborts a query if there are to many partitions. I seem to
remeber I have worked with up to 100K partitions, but managed them
individually instead of through the super table.

Just a tip: if the table gets data inserted once and then mainly read
after that, its faster to create the index for the partition after the
insert.
Another tip: use COPY to insert data instead of INSERT, its about 3-5
times faster, it is supported by the C driver and a patched JDBC driver

regards

tom

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [HACKERS] StartupCLOG

On Thu, 2008-09-04 at 11:12 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
> > I notice that StartupCLOG zeroes out entries later than the nextxid when
> > we complete recovery in StartupXLOG, reason given is safety in case we
> > crash.
>
> > ISTM that we should also do that whenever we see a Shutdown Checkpoint
> > in WAL, since that can be caused by a shutdown immediate, shutdown abort
> > or crash.
>
> Er, what? The definition of a crash is the *lack* of a shutdown
> checkpoint.

Yes, but that's not what I'm saying.

I was thinking about what happens when you are performing a PITR using
log records that contain a crash/recovery/shutdown checkpoint sequence.

I take it there's no problem there?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Cleanup of GUC units code

On Thu, Sep 04, 2008 at 07:01:18AM -0700, Steve Atkins wrote:
> Settings in postgresql.conf are currently case-insensitive. Except
> for the units.

And, of course, filenames when you are using a case-sensitive
filesystem. Because these are things that are defined by some
convention other than the ones the PGDG made up. Since units fall
into that category, it seems to me that we're stuck with using
external conventions.

> one right now. If the answer to that is something along the lines
> of we don't support megaabits for shared_buffers, and never will because
> nobody in their right mind would ever intend to use megabits
> to set their shared buffer size... that's a useful datapoint when
> it comes to designing for usability.

And you are going to establish this worldwide convention on what
someone in right mind would do how, exactly? For instance, I think
nobody in right mind would use "KB" to mean "kilobytes". I suppose
you could get a random sample of all current Postgres users to decide
what makes sense, but then you'd have the problem of knowing whether
you had a random sample, since the population isn't obviously
identifiable. Or, we could just stick with the convention that we
already have, and write a tool that captures this an other issues.
Maybe even one that could later form the basis for an automatic tuning
advisor, as well.

The problem with appeals to common sense always turns out to be that
different people's common sense leads them to different conclusions.
(We had a devastating government in Ontario some years ago that claimed
to be doing things that were just common sense; the Province is still
cleaning up the mess.)

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [BUGS] BUG #4398: Backslashes get escaped despite of backslash_quote=off

"Rainer" <rainer@hamburg.ccc.de> writes:
> Description: Backslashes get escaped despite of backslash_quote=off

Aren't you looking for standard_conforming_strings? backslash_quote is
something else entirely, and doesn't actually do anything at all when
backslash escaping is disabled.

> Two questions:
> 1. What I actually want: Shouldn't the second statement work by
> documentation without the escape flag?

No. standard_conforming_strings has nothing to do with the behavior of
LIKE (nor does backslash_quote). They just control the initial parsing
of SQL string literals.

> 2. What I do not understand: Why does the fourth statement return a result
> as backslash_quote is off?

It looks like a perfectly good match to me.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [PERFORM] limit clause breaks query planner?

On Thu, 4 Sep 2008, Tom Lane wrote:
> Ultimately the only way that we could get the right answer would be if
> the planner realized that the required rows are concentrated at the end
> of the table instead of being randomly scattered. This isn't something
> that is considered at all right now in seqscan cost estimates. I'm not
> sure offhand whether the existing correlation stats would be of use for
> it, or whether we'd have to get ANALYZE to gather additional data.

Using the correlation would help, I think, although it may not be the best
solution possible. At least, if the correlation is zero, you could behave
as currently, and if the correlation is 1, then you know (from the
histogram) where in the table the values are.

Matthew

--
X's book explains this very well, but, poor bloke, he did the Cambridge Maths
Tripos... -- Computer Science Lecturer

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [BUGS] BUG #4389: FATAL: could not reattach to shared memory(key=1804, addr=018E0000): 487

Hi!

The reason was a corrupted system library and "sfc /scannow" have helped me.
I wish you remember this solution and will advise it to other people.

Thanks.

--- Исходное сообщение ---
От кого: Zdenek Kotala <Zdenek.Kotala@Sun.COM>
Кому: diesel_den@ukr.net
Дата: 1 сентября, 19:55:38
Тема: Re: [BUGS] BUG #4389: FATAL: could not reattach to shared memory(key=1804, addr=018E0000): 487

could not reattach to shared memory napsal(a):
> The following bug has been logged online:
>
> Bug reference: 4389
> Logged by: could not reattach to shared memory
> Email address: diesel_den@ukr.net
> PostgreSQL version: 8.3.3-1
> Operating system: any 8.3.*
> Description: FATAL: could not reattach to shared memory (key=1804,
> addr=018E0000): 487
> Details:
>
> This error came week ago.
> From that 'black' day I can not use Postgre.
> I have reinstalled several 8.3.* versions (including last version with
> vcredist_x86.exe) and nothing helps me.
>

try to remove postgesql.pid file in the data directory.

Zdenek

--
HeadHunter:Украина - http://www.hh.ua Элитные вакансии компаний.
Создайте резюме на сайте и получите работу!

Re: [BUGS] BUG #4397: crash in tab-complete.c

Rudolf Leitgeb <r.leitgeb@x-pin.com> writes:
> Yes, libedit is used. On Mac OSX libreadline is a soft link
> to libedit, so that's what's used regardless of configure settings.

Actually, given that you got compile warnings, the thing to focus on is
probably what readline #include files were used. I'm still suspicious
of a local readline installation messing things up --- is there anything
in /usr/local/include?

What were those warnings, anyway?

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [PERFORM] limit clause breaks query planner?

"Matt Smiley" <mss@rentrak.com> writes:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>> default cost settings will cause it to prefer bitmap scan for retrieving
>> up to about a third of the table, in my experience). I too am confused
>> about why it doesn't prefer that choice in the OP's example.

> It looks like the bitmap scan has a higher cost estimate because the
> entire bitmap index must be built before beginning the heap scan and
> returning rows up the pipeline.

Oh, of course. The LIMIT is small enough to make it look like we can
get the required rows after scanning only a small part of the table,
so the bitmap scan will lose out in the cost comparison because of its
high startup cost.

Ultimately the only way that we could get the right answer would be if
the planner realized that the required rows are concentrated at the end
of the table instead of being randomly scattered. This isn't something
that is considered at all right now in seqscan cost estimates. I'm not
sure offhand whether the existing correlation stats would be of use for
it, or whether we'd have to get ANALYZE to gather additional data.

regards, tom lane

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [ADMIN] Database Conversion

>
> Hello, All,
>
> I have a new faculty member who has a large database that is
> in MySQL. We don't support MySQL so the database needs to be
> ported to PostgreSQL. Her GA, who know MySQL, says that he
> has a query that he will run that will put the data into
> postgres. I thought that the data would have to be output to
> a text file and then copied into postgres. I don't know
> MySQL. I've done a conversion from Oracle and this is how I
> did it. Is he correct that he can put the data into a
> postgres database by running a MySQL query? It doesn't sound
> possible to me.
>
> Carol
>

You could possibly do it in a single operation using MS Access if you
have an ODBC connection to each database. If however the dataset is
large, I wouldn't recommend it. I have a number of MySQL and PostgreSQL
dbs and I either dump sql and then import or use PHP scripts when moving
between the two.

Nick

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [pgeu-general] LinuxLive UK

On Thu, 2008-09-04 at 15:45 +0100, Dave Page wrote:

> I've had no volunteers to help out at this show, so unless I get at
> least three firm commitments by Friday I'll be forced to cancel our
> table :-(

Is this the same show you asked about in June and got lots of yesses?

Me? Still yes.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

--
Sent via pgeu-general mailing list (pgeu-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgeu-general

Re: [HACKERS] StartupCLOG

Simon Riggs <simon@2ndQuadrant.com> writes:
> I notice that StartupCLOG zeroes out entries later than the nextxid when
> we complete recovery in StartupXLOG, reason given is safety in case we
> crash.

> ISTM that we should also do that whenever we see a Shutdown Checkpoint
> in WAL, since that can be caused by a shutdown immediate, shutdown abort
> or crash.

Er, what? The definition of a crash is the *lack* of a shutdown
checkpoint.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [pgsql-www] wiki.postgresql.org is awfully slow this evening

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Dave Page wrote:

> No we haven't - not even remotely. At a rough count we'd need at least
> another 17 servers (based on the current number of VMs), and we'd lose
> the ability to move services between hardware quickly and easily. Oh,
> and we'd have the management headache of dealing with a bunch more
> hosting providers, as I doubt the current ones will give us that many
> boxes.

Well, maybe we don't need to replace all 17, just some of the more
active ones.

Joshua points out:

> That being said :) I think its a mistake. It would be a complete waste
> of resources to go to dedicated machines. Some of the machines we have
> we hardly use at this point.

Fair enough, I withdraw the dedicated box request. Can we perhaps separate
the wiki then, so we don't have a repeat of yesterday? Maybe put wiki
or git onto one of the more lightly loaded physical boxes?

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200809041059
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAki/+DoACgkQvJuQZxSWSsj22QCfZlZQ4XoxYxo+UOxXAjNeKdio
UecAoOPRoRN8EPKhgTGRINzjnAxXOJNd
=MNYh
-----END PGP SIGNATURE-----

--
Sent via pgsql-www mailing list (pgsql-www@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-www

Re: [ADMIN] Database Conversion

Well, my database server lives on a Solaris 10 box. I'm running
PosgreSQL 8.2.3. The database that is being converted from MySQL is
currently on a Windows machine. So far it seems that every solution
involves an interim step or two. I think he was alluding to just
running a query.

Carol
On Sep 4, 2008, at 10:33 AM, Ben Kim wrote:

>
>> I have a new faculty member who has a large database that is in
>> MySQL. We don't support MySQL so the database needs to be ported to
>> PostgreSQL. Her GA, who know MySQL, says that he has a query that he
>> will run that will put the data into postgres. I thought that the
>> data would have to be output to a text file and then copied into
>> postgres. I don't know MySQL. I've done a conversion from Oracle
>> and this is how I did it. Is he correct that he can put the data
>> into a postgres database by running a MySQL query? It doesn't sound
>> possible to me.
>
> I don't think mysql has anything that exports data into postgresql.
> Unless he is talking about the likes of DTS/SSIS or perl DBI, or
> other tools. Or the tables are simple and he thinks he can
> ingeniously craft queries and run them through pipes eventually to
> psql. DDL will be more difficult.
>
>
> Regards,
> Ben
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[GENERAL] You need to rebuild PostgreSQL using --with-libxml.

Buenos Dias, necesito saber los pasos para recompilar con  soporte para xml, en redhat 4 entreprise y postgres 8.3.

 

Gracias,

---

.

Re: [GENERAL] Changes for version 8.4

Joao Ferreira gmail escribió:
> Is there a date for the release of 8.4 ?

http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Development_Plan

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [pgeu-general] LinuxLive UK

On Thu, Aug 28, 2008 at 10:13 AM, Dave Page <dpage@pgadmin.org> wrote:
> I've mentioned previously that we have a table in the .ORG village at
> LinuxLive, Olympia, London on the 23 - 25 October.
>
> http://www.linuxexpo.org.uk/
>
> It's about time that we got organised and figured out who will be
> available to attend, and when. I can volunteer Greg and myself, but we
> need at least a few additional people to man the booth effectively
> over the three days. So, can I get a show of hands from those able to
> attend, along with how many/which days please?
>
> Thanks!

I've had no volunteers to help out at this show, so unless I get at
least three firm commitments by Friday I'll be forced to cancel our
table :-(

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

--
Sent via pgeu-general mailing list (pgeu-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgeu-general

Re: [GENERAL] Changes for version 8.4

Is there a date for the release of 8.4 ?

joao

On Thu, 2008-09-04 at 10:09 -0400, Alvaro Herrera wrote:
> paul tilles wrote:
> > Where can I find a list of changes for Version 8.4 of postgres?
>
> It's not officially written anywhere. As a starting point you can look
> here:
> http://wiki.postgresql.org/wiki/Category:CommitFest
> Then look at each Commitfest:2008:xx page, and see the list of committed
> patches. Also, note that a certain number of patches have gone in
> without being listed there (most notably, a huge improvement in how
> EXISTS queries are handled).
>
> The definitive place, of course, is the CVS logs.
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [pgsql-www] wiki.postgresql.org is awfully slow this evening

On Thu, Sep 4, 2008 at 3:32 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>> No we haven't - not even remotely. At a rough count we'd need at least
>> another 17 servers (based on the current number of VMs), and we'd lose
>> the ability to move services between hardware quickly and easily. Oh,
>> and we'd have the management headache of dealing with a bunch more
>> hosting providers, as I doubt the current ones will give us that many
>> boxes.
>
> *cough*
>
> Yes I think they would.

Ya think? I'm struggling to see which of our 6 providers would pony up
more than a couple more machines.

> That being said :) I think its a mistake. It would be a complete waste of
> resources to go to dedicated machines. Some of the machines we have we
> hardly use at this point.

I think most are certainly used, but some require much fewer resources
than others.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

--
Sent via pgsql-www mailing list (pgsql-www@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-www

Re: [ADMIN] Database Conversion

> I have a new faculty member who has a large database that is in
> MySQL. We don't support MySQL so the database needs to be ported to
> PostgreSQL. Her GA, who know MySQL, says that he has a query that he
> will run that will put the data into postgres. I thought that the
> data would have to be output to a text file and then copied into
> postgres. I don't know MySQL. I've done a conversion from Oracle
> and this is how I did it. Is he correct that he can put the data
> into a postgres database by running a MySQL query? It doesn't sound
> possible to me.

I don't think mysql has anything that exports data into postgresql. Unless
he is talking about the likes of DTS/SSIS or perl DBI, or other tools. Or
the tables are simple and he thinks he can ingeniously craft queries and
run them through pipes eventually to psql. DDL will be more difficult.

Regards,
Ben

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [pgsql-www] wiki.postgresql.org is awfully slow this evening

Dave Page wrote:
> On Thu, Sep 4, 2008 at 3:08 PM, Greg Sabino Mullane <greg@turnstep.com> wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: RIPEMD160
>>
>>
>>> from remus which is in austria shows no problem for the last 24h) we had
>>> two runaway cvsps processes in the git jail(which is on the same
>>> physical host as the wiki) that more or less hogged all the CPU on the box.
>> Oh for Pete's sake, can we please get away from jails and just use
>> dedicated servers? We've had enough people volunteer hardware and
>> time to make this happen.
>
> No we haven't - not even remotely. At a rough count we'd need at least
> another 17 servers (based on the current number of VMs), and we'd lose
> the ability to move services between hardware quickly and easily. Oh,
> and we'd have the management headache of dealing with a bunch more
> hosting providers, as I doubt the current ones will give us that many
> boxes.

*cough*

Yes I think they would.

That being said :) I think its a mistake. It would be a complete waste
of resources to go to dedicated machines. Some of the machines we have
we hardly use at this point.

Sincerely,

Joshua D. Drake

--
Sent via pgsql-www mailing list (pgsql-www@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-www

Re: [HACKERS] [PATCH] Cleanup of GUC units code

On Thu, 2008-09-04 at 09:29 -0400, Andrew Sullivan wrote:
> On Thu, Sep 04, 2008 at 01:26:44AM +0300, Hannu Krosing wrote:
>
> > So Andrews opinion was that Mb (meaning Mbit) is different from MB (for
> > megabyte) and that if someone thinks that we define shared buffers in
> > megabits can get confused and order wrong kind of network card ?
>
> I know it's fun to point and laugh instead of giving an argument, but
> the above is not what I said. What I said is that there is a
> technical difference between at least some of these units, and one
> that is relevant in some contexts where we have good reason to believe
> Postgres is used. So it seems to me that there is at least a _prima
> facie_ reason in favour of making case-based decisions. Your argument
> against that appears to be, "Well, people can be sloppy."
>
> Alvaro's suggestion seems to me to be a better one.

Agreed. maybe this can even be implemented as a special switch to
postmaster (maybe -n or --dry-run, similar to make), not a separate
command.

> > I can understand Alvaros stance more readily - if we have irrational
> > constraints on what can go into conf file, and people wont listen to
> > reason
>
> Extending your current reasoning, it's irrational that all the names
> of the parameters have to be spelled correctly.

It would be irrational to allow all letters in parameter names to be
case-insensitive, except 'k' which has to be lowercase ;)

The main point of confusion comes from not accepting KB and this bites
you when you go down from MB, with reasoning like "ok, it seems that
units are in uppercase, so let's change 1MB to 768KB and see what
happens"

-------------
Hannu

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [patch] GUC source file and line number]

>>> Greg Smith <gsmith@gregsmith.com> wrote:

> name | Recommended | Current | Min | Default | Max
> -------------+-------------+---------+-------+---------+---------
> wal_buffers | 1024kB | 64kB | 32 kB | 64 kB | 2048 MB

Personally, I would take the "Min", "Default", and "Max" to mean what
Greg intends; it's the "Current" one that gives me pause. The current
value of this connection? The value that a new connection will
currently get? The value which new connections will get after a
reload with the current conf file? The value which new connections
will get after a restart with the current conf file? I can understand
how someone would take one of these four values to be what is meant by
"Default", though.

-Kevin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [pgsql-www] wiki.postgresql.org is awfully slow this evening

On Thu, Sep 4, 2008 at 3:08 PM, Greg Sabino Mullane <greg@turnstep.com> wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
>> from remus which is in austria shows no problem for the last 24h) we had
>> two runaway cvsps processes in the git jail(which is on the same
>> physical host as the wiki) that more or less hogged all the CPU on the box.
>
> Oh for Pete's sake, can we please get away from jails and just use
> dedicated servers? We've had enough people volunteer hardware and
> time to make this happen.

No we haven't - not even remotely. At a rough count we'd need at least
another 17 servers (based on the current number of VMs), and we'd lose
the ability to move services between hardware quickly and easily. Oh,
and we'd have the management headache of dealing with a bunch more
hosting providers, as I doubt the current ones will give us that many
boxes.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

--
Sent via pgsql-www mailing list (pgsql-www@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-www

Re: [pgsql-advocacy] famous multi-process architectures

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> "Google got inspired by Postgres: they use the same
> multi-process architecture for their browser as Postgres
> already features for many years. Simply because it
> provides better crash-safety than threaded applications."

That's a heck of a stretch to say they were "inspired" by
Postgres. A multi-process model is hardly a unique development
of Postgres, and it's not like we don't still have crash problems:

"process exited abnormally and possibly corrupted shared memory"
"terminating connection because of crash of another server process"

I suspect Chrome doesn't have the same shared memory requirements
that a database does, of course.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200809041014
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAki/7dUACgkQvJuQZxSWSshiAACg6cYg8GkpoNmTIV1/edxEdB0p
AkUAn2HMQPntdqjQARWA4Z9pKef7aPwj
=mpK2
-----END PGP SIGNATURE-----

--
Sent via pgsql-advocacy mailing list (pgsql-advocacy@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-advocacy

Re: [GENERAL] Changes for version 8.4

paul tilles wrote:
> Where can I find a list of changes for Version 8.4 of postgres?

It's not officially written anywhere. As a starting point you can look
here:
http://wiki.postgresql.org/wiki/Category:CommitFest
Then look at each Commitfest:2008:xx page, and see the list of committed
patches. Also, note that a certain number of patches have gone in
without being listed there (most notably, a huge improvement in how
EXISTS queries are handled).

The definitive place, of course, is the CVS logs.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [pgsql-www] wiki.postgresql.org is awfully slow this evening

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> from remus which is in austria shows no problem for the last 24h) we had
> two runaway cvsps processes in the git jail(which is on the same
> physical host as the wiki) that more or less hogged all the CPU on the box.

Oh for Pete's sake, can we please get away from jails and just use
dedicated servers? We've had enough people volunteer hardware and
time to make this happen.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200809041007
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAki/66gACgkQvJuQZxSWSsgFbACg38bPRwFMKFoixPg80QVtiVpO
sgsAnRu2S3lKJ3udfQTKl0gFujVKA/sD
=UT0M
-----END PGP SIGNATURE-----

--
Sent via pgsql-www mailing list (pgsql-www@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-www

Re: [HACKERS] [PATCH] Cleanup of GUC units code

On Sep 4, 2008, at 6:29 AM, Andrew Sullivan wrote:

> On Thu, Sep 04, 2008 at 01:26:44AM +0300, Hannu Krosing wrote:
>
>> So Andrews opinion was that Mb (meaning Mbit) is different from MB
>> (for
>> megabyte) and that if someone thinks that we define shared buffers in
>> megabits can get confused and order wrong kind of network card ?
>
> I know it's fun to point and laugh instead of giving an argument, but
> the above is not what I said. What I said is that there is a
> technical difference between at least some of these units, and one
> that is relevant in some contexts where we have good reason to believe
> Postgres is used. So it seems to me that there is at least a _prima
> facie_ reason in favour of making case-based decisions. Your argument
> against that appears to be, "Well, people can be sloppy."

Settings in postgresql.conf are currently case-insensitive. Except
for the units.

> Alvaro's suggestion seems to me to be a better one. It is customary,
> in servers with large complicated configuration systems, for the
> server to come with a tool that validates the configuration file
> before you try to load it. Postfix does this; apache does it; so does
> BIND. Heck, even NSD (which is way less configurable than BIND) does
> this. Offering such a tool provides considerable more benefit than
> the questionable one of allowing people to type whatever they want
> into the configuration file and suppose that the server will by magic
> know what they meant.

How would such a tool cope with, for example, shared_buffers
being set to one eighth the size the DBA intended, due to their
use of Mb rather than MB? Both of which are perfectly valid
units to use to set shared buffers, even though we only support
one right now. If the answer to that is something along the lines
of we don't support megaabits for shared_buffers, and never will because
nobody in their right mind would ever intend to use megabits
to set their shared buffer size... that's a useful datapoint when
it comes to designing for usability.

Cheers,
Steve

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[BUGS] BUG #4400: initdb doesn't work with partition D:

The following bug has been logged online:

Bug reference: 4400
Logged by: Jan-Peter Seifert
Email address: Jan-Peter.Seifert@gmx.de
PostgreSQL version: 8.3.3
Operating system: Windows xp Professional
Description: initdb doesn't work with partition D:
Details:

Hello,

whenever I try to run initdb on a directory on partition "D:" with the
parameter "-D" I get the error that a "file exists". I create a directory,
give full rights for the user postgres and then run initdb on it. On
partition E: it works. Both are NTFS. I have no programs open that might
access the directories ...

Strange.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [ADMIN] server/db encoding (mix) issues

Jan-Peter Seifert wrote:
> we have a mix of older software still using LATIN1 as db encoding and the psqlODBC-drivers (ANSI) and newer software using UTF8 as db encoding. As running two server instances would use up more resources(?) than just one we'd like to have all dbs in one cluster. Which cons against this solution are there? Which operating system locale should be used then? C locale is recommended in the docs - also because of better performance. However, the language of the software is not English but German - so shouldn't there be problems with sorting German Umlauts etc. correctly etc.? Which encoding should the server have - UTF8/Unicode or LATIN1? BTW which is the correct locale for LATIN1 and German (de_DE (my guess) or de_DE@euro (which seems to be for LATIN9)). Using SQL_ASCII doesn't seem to be a wise choice. Are there no problems when connecting with psqlODBC-ANSI drivers if the server encoding is UTF8/Unicode? I'd be happy if you could enlighten me a bit.

Set your locale to de_DE.utf8 and use UTF8 as server encoding.

I would be interested to know where the documentation "recommends" using
the C locale. That would certainly not be reasonable for many uses.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [HACKERS] Debugging methods

Hi,

M2Y wrote:
> I am a beginner to Postgres and I am going through code. I would like
> to know the debugging methods used in development.

Try ./configure with '--enable-debug' and '--enable-cassert', as
outlined in the developer's FAQ [1], where you certainly find more
information as well. Then run the postmaster with '-A1 -d5'

Regards

Markus Wanner

[1]: http://wiki.postgresql.org/wiki/Developer_FAQ

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [ADMIN] pg_dump etc. versions

Jan-Peter Seifert wrote:
> I'm wondering if there's a compatibility list of the tools supplied with PostgreSQL - e.g. psql seems to be very server version specific (only major or also minor versions?).
> For pg_dump I'd say users should use the version of the target server if it's already installed, but is this also the case if the target server version is older? Am I completely wrong? Should I always use the pg_dump from the source server? When migrating from 8.1 to 8.2 I get several errors with commands regarding creating users and a lib that had been integrated into the core when restoring from the source server's pg_dump's dump. When using the target server's pg_dump for the dump I don't. But is then really everything okay? And pgAdmin comes with its own set of the PostgreSQL tools ...

I think the only thing that we really check is that pg_dump of a newer
version can dump databases from an older version server. All the other
tools probably only work (completely) with a server from the same major
release.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Database Conversion

Στις Thursday 04 September 2008 16:24:34 ο/η Carol Walter έγραψε:
> Hello, All,
>
> I have a new faculty member who has a large database that is in
> MySQL. We don't support MySQL so the database needs to be ported to
> PostgreSQL. Her GA, who know MySQL, says that he has a query that he
> will run that will put the data into postgres. I thought that the
> data would have to be output to a text file and then copied into
> postgres. I don't know MySQL. I've done a conversion from Oracle
> and this is how I did it. Is he correct that he can put the data
> into a postgres database by running a MySQL query? It doesn't sound
> possible to me.

If his query is like:

SELECT 'INSERT INTO PostgreSqlTable(...) VALUES(''||somevalue...||'')' FROM mysqltable ....

then it is possible

>
> Carol
>

--
Achilleas Mantzios

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Database Conversion

Στις Thursday 04 September 2008 16:24:34 ο/η Carol Walter έγραψε:
> Hello, All,
>
> I have a new faculty member who has a large database that is in
> MySQL. We don't support MySQL so the database needs to be ported to
> PostgreSQL. Her GA, who know MySQL, says that he has a query that he
> will run that will put the data into postgres. I thought that the
> data would have to be output to a text file and then copied into
> postgres. I don't know MySQL. I've done a conversion from Oracle
> and this is how I did it. Is he correct that he can put the data
> into a postgres database by running a MySQL query? It doesn't sound
> possible to me.
>

We recently did a conversion from MS Access (i dont know details) to pgsql 8.3.3.
The MS Access-aware guy just declared the correct postgresql ODBC settings,
i adjusted the pgsql backend to accept connections from the MS workstation,
then performed an EXPORT from MS Access to the pgsql datasource
and thats all.
Of course all i got was the exact MS Access tables, which then were useful
to populate my new designed pgsql tables.

One caveat here, most commonly, is the design of the DB.
The lower end you get mysql->sql server->access->COBOL, etc...
the greater chance you need a re-engineering of the schema.

> Carol
>

--
Achilleas Mantzios

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [HACKERS] Conflict resolution in Multimaster replication(Postgres-R)

Hello Srinivas,

M2Y wrote:
> Markus: It looks like the hybrid approach used by Postgres-R(as
> described in that paper) is good.

Well, yeah. That's why am working on it ;-)

You are very welcome to download the patch and dig into the sources. See
www.postgres-r.org for more information.

To answer your original question in more details:

> Suppose there are two sites in the group, lets say, A and B and are
> managing a database D. Two transactions TA and TB started in sites A
> and B respectively, at nearly same time, wanted to update same row of
> a table in the database. As, no locking structures and other
> concurrency handling structures are replicated each will go ahead and
> do the modifications in their corresponding databases and sends the
> writeset.

Correct so far. Note that both transactions might have applied changes,
but they have not committed, yet.

In eager mode we rely on the Group Communication System to deliver these
two changesets [1] in the same order on both nodes. Let's say both
receive TA's changeset first, then TB's.

The backend which processed TA on node A can commit, because its changes
don't conflict with anything else. The changeset of TB is forwarded to a
helper backend, which tries to apply its changes. But the helper backend
detects the conflict against TA and aborts (because it knows TA takes
precedence on all other nodes as well).

On node B, the backend which processed TB has to wait with its commit,
because another changeset, namely TA's came in first. For that changeset
a helper backend is started as well, which applies the changes of TA.
During application of changes, that helper backend detects a conflict
against the (yet uncommitted) changes of TB. As it knows its transaction
TA takes precedence over TB (on all other nodes as well), it tells TB
to abort and continues applying its own changes.

I hope that was an understandable explanation.

Regards

Markus Wanner

[1]: In the original Postgres-R paper, these are called writesets. But
in my implementation, I've altered its meaning somewhat. Because of that
(and because I admittedly like "changeset" better), I've decided to call
them changesets now...

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Cleanup of GUC units code

On Thu, Sep 04, 2008 at 01:26:44AM +0300, Hannu Krosing wrote:

> So Andrews opinion was that Mb (meaning Mbit) is different from MB (for
> megabyte) and that if someone thinks that we define shared buffers in
> megabits can get confused and order wrong kind of network card ?

I know it's fun to point and laugh instead of giving an argument, but
the above is not what I said. What I said is that there is a
technical difference between at least some of these units, and one
that is relevant in some contexts where we have good reason to believe
Postgres is used. So it seems to me that there is at least a _prima
facie_ reason in favour of making case-based decisions. Your argument
against that appears to be, "Well, people can be sloppy."

Alvaro's suggestion seems to me to be a better one. It is customary,
in servers with large complicated configuration systems, for the
server to come with a tool that validates the configuration file
before you try to load it. Postfix does this; apache does it; so does
BIND. Heck, even NSD (which is way less configurable than BIND) does
this. Offering such a tool provides considerable more benefit than
the questionable one of allowing people to type whatever they want
into the configuration file and suppose that the server will by magic
know what they meant.

> I can understand Alvaros stance more readily - if we have irrational
> constraints on what can go into conf file, and people wont listen to
> reason

Extending your current reasoning, it's irrational that all the names
of the parameters have to be spelled correctly. Why can't we just
accept log_statement_duration_min? It's _obvious_ that it's the same
thing as log_min_duration_statement! It's silly to expect that
harried administrators have to spell these options correctly. Why
can't we parse all the file, separating each label by "_". Then if
any arrangements of those labels matches a "real" configuration
parameter, select that one as the thing to match and proceed from
there?

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[ADMIN] Database Conversion

Hello, All,

I have a new faculty member who has a large database that is in
MySQL. We don't support MySQL so the database needs to be ported to
PostgreSQL. Her GA, who know MySQL, says that he has a query that he
will run that will put the data into postgres. I thought that the
data would have to be output to a text file and then copied into
postgres. I don't know MySQL. I've done a conversion from Oracle
and this is how I did it. Is he correct that he can put the data
into a postgres database by running a MySQL query? It doesn't sound
possible to me.

Carol

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[HACKERS] Debugging methods

Hello,

I am a beginner to Postgres and I am going through code. I would like
to know the debugging methods used in development.

Some of my requirements are; for a given query, how parse structures
are created in pg_parse_query, how they are analyzed and rewritten in
pg_analyze_and_rewrite and how the final plan is created in
pg_plan_queries. I will go through code but I would like to know any
debugging methods available to understand what happens for a given
query.

I have searched in the net and I am unable to find them. Sorry if it
is available somewhere and I am asking again.

Thanks,
Srinivas

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[GENERAL] xpath_bool_ns() and xml2

Hi,

 

does anybody know how to use the xml2 function xpath_bool with namespaces.

I have used this function successfully as long as my xml documents haven’t contained namespaces. I searched with google and found some readme file where the function xpath_bool_ns was available that would probably resolve my namespace aware xml xpath issue, but this function isn’t contained in the pqxml.dll that comes with postgres 8.3.3.

 

Can anybody help me or point some other solution ? I just want to do some xpath queries on a table column that returns a Boolean as result.

 

Cheers, Tobias

Re: [HACKERS] [PATCH] Cleanup of GUC units code

Hannu Krosing escribió:
> On Wed, 2008-09-03 at 20:01 -0400, Alvaro Herrera wrote:

> > Yes there is --- it's the SI.
> >
> > http://en.wikipedia.org/wiki/SI#SI_writing_style
> >
> > I don't know about it being "evil" and punishment, but it's wrong.
>
> SI defines decimal-based prefixes, where k = kilo = 1000, so our current
> conf use is also wrong.

Actually, this has been a moving target. For a certain length of time,
some standards did accept that k meant 1024 "in computing context"; see

http://en.wikipedia.org/wiki/Binary_prefix

So we're not _absolutely_ wrong here; at least not until KiB are more
widely accepted and kB more widely refused to mean 1024 bytes. The
relevant standard has been published just this year by ISO.

http://en.wikipedia.org/wiki/ISO/IEC_80000#Binary_prefixes

So this is new territory, whereas case-sensitivity of prefixes and unit
abbreviations has existed for decades.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[GENERAL] Changes for version 8.4

Where can I find a list of changes for Version 8.4 of postgres?

Paul Tilles

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general