Re: [BUGS] BUG #5604: Setting NOT NULL on inherited column turns to real column in dump
On Fri, Aug 6, 2010 at 12:31 AM, Jon Erdman (aka StuckMojo) wrote: > Description: Setting NOT NULL on inherited column turns to real > column in dump > > I think the fix here is relatively simple: make NOT NULL on an inherited > column dump as an ALTER TABLE. > > If you set NOT NULL on an inherited column in a child table, then drop the > column from the parent, it's gone from both and all is well. > > However, if you dump and restore the db, then drop the parent column, the > inherited column remains in the child table. This is a result of the NOT > NULL dumping as a column create in the child, which I assume then shadows > the inherited column after restore. That sucks. I have a feeling it's going to be hard to fix properly without this patch: https://commitfest.postgresql.org/action/patch_view?id=312 I don't think your proposed fix will work because the NOT NULL-ness could be either inherited or not inherited. The column could even be inherited from multiple parents, some of which have a NOT NULL constraint and others of which do not. Consider: create table top1 (a int not null); create table top2 (a int); create table bottom () inherits (top1, top2); alter table bottom no inherit ; If = top1, then bottom.a should now allow nulls, but if = top2, then it should still be not null. Unfortunately, we don't do enough bookkeeping right now to distinguish those cases. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5612: Database Integrity
The following bug has been logged online: Bug reference: 5612 Logged by: Ramachandran S Email address: nic.sr...@gmail.com PostgreSQL version: 8.4 Operating system: Ubuntu Description:Database Integrity Details: I have observed that if a table exceeds size (default installation) 1 GB. The table when it exceeds 1 GB a file is created say 1811(table) with 1811.1 and further records added are insertted into this file. My Question if I remove the file 1811.1 say still database is starting up? I will never know that such a file existed nad remved and data of 1811 is also availble? How can stop the start the database when such situation recur. pgfsck was available 8.2 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5612: Database Integrity
"Ramachandran S" wrote: > I have observed that if a table exceeds size (default > installation) 1 GB. The table when it exceeds 1 GB a file is > created say 1811(table) with 1811.1 and further records added are > insertted into this file. My Question if I remove the file 1811.1 > say still database is starting up? I will never know that such a > file existed nad remved and data of 1811 is also availble? > How can stop the start the database when such situation recur. > pgfsck was available 8.2 I don't see any hint of a PostgreSQL bug here. Since there's not really enough in your post to get very far, please re-post to a more appropriate list. pgsql-general is probably your best bet. I'm not entirely clear on whether you are asking a hypothetical question or whether your database cluster is currently out of commission because you mangled the underlying files. If you're trying to recover from such damage, please post actual details. See this page for suggestions on what to include: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems If this is all hypothetical, my advice is to review the backup documentation and always keep up-to-date backups, and never delete files in the manner you describe. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE
Dear Tom, Thanks for getting back to me so soon. I see that SQL functions seem to inline their calling arguments. My complaint remains. That inlined function f_return_ver_id_4() is a STABLE function, inlined or not. Postgres now calls it multiple times during the transaction, even though the arguments to f_return_ver_id_4() have not changed. STABLE no longer means STABLE. This behavior is killing my performance. I am getting 500% to 3% increase in latency. The problem does not stop with just this. Functions that I have no control over, like now(), behave like this too. The function now() gets called multiple times in a transaction.See the example below. All this behavior seems only to happen when I call the SQL function from a PL/PGSQL function. It doesn't happen when I call the SQL function directly from the psql command line. For example, below, look what I had to do with the now() function. I had to change: <<< create or replace function f_get_current_price_all_keys_by_mp_rank(culture_in text, ver_id_min_in int8, ver_id_max_in int8, query_in tsquery) returns setof type_product_price_key as $BODY$ - -- -- This function retrieves the all primary keys to the product_price table -- which point to the current prices of vendor products for a specific -- tsearch query. - select pp.vp_id, pp.ver_id, pp.pm_id, max(pp.pp_price_eff_time) as pp_price_eff_time, pp.pp_min_qty from product_price pp inner join vendor_product vp on vp.vp_id = pp.vp_id inner join manufacturer_product_tsv tsv on tsv.mp_id = vp.mp_id and tsv.ver_id = pp.ver_id inner join manufacturer_product mp on mp.mp_id = vp.mp_id inner join brandb on b.brand_id = mp.brand_id inner join manufacturer m on m.man_id = b.man_id inner join vendor_vertical vv on vv.vdr_id = vp.vdr_id and vv.ver_id = pp.ver_id inner join vendor v on v.vdr_id = vv.vdr_id inner join promotionpm on pm.pm_id = pp.pm_id left join promo_vendor_behaviorvb on vb.pm_id = pp.pm_id and vb.vdr_id = vp.vdr_id left join promo_type_attrs pa on pa.pmta_id = vb.pmta_id inner join time_zonet on t.tz_id= pp.tz_id where pp.active_cd = 1 and tsv.search_vector @@@ $4 and tsv.culture = $1 and pp.ver_id between $2 and $3 and vp.active_cd = 1 and vv.active_cd = 1 and v.active_cd = 1 and mp.active_cd = 1 and b.active_cd = 1 and m.active_cd = 1 and pm.active_cd = 1 and ((pa.pmta_id is null) or (pa.pmta_id is not null and pa.active_cd = 1)) and ((pp.pp_end_time is null) or (pp.pp_end_time > now())) and pp.pp_price_eff_time <= now() and (pp.days_of_week & (1 << (extract(dow from now() at time zone t.name)::int4))) <> 0 and (pp.days_of_month & (1 << (extract(day from now() at time zone t.name)::int4))) <> 0 and (pp.months_of_year & (1 << (extract(month from now() at time zone t.name)::int4))) <> 0 group by pp.vp_id, pp.ver_id, pp.pm_id, pp.pp_min_qty $BODY$ language 'SQL' STABLE; <<< To this PL/PGSQL function because now() gets called multiple times. The function above runs in 1.8 seconds. The function below runs in 0.25 seconds. create or replace function f_get_current_price_all_keys_by_mp_rank(culture_in text, ver_id_min_in int8, ver_id_max_in int8, query_in tsquery) returns setof type_product_price_key as $BODY$ - -- -- This function retrieves the all primary keys to the product_price table -- which point to the current prices of vendor products for a specific -- tsearch query. - declare now_wtimestamp with time zone; begin now_w := now(); return query select pp.vp_id, pp.ver_id, pp.pm_id, max(pp.pp_price_eff_time) as pp_price_eff_time, pp.pp_min_qty from product_price pp inner join vendor_product vp on vp.vp_id = pp.vp_id inner join manufacturer_product_tsv tsv on tsv.mp_id = vp.mp_id and tsv.ver_id = pp.ver_id inner join manufacturer_product mp on mp.mp_id = vp.mp_id inner join brandb on b.brand_id = mp.brand_id inner join manufacturer m on m.man_id = b.man_id inner j
Re: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE
Brian Ceccarelli writes: > STABLE no longer means STABLE. This behavior is killing my performance. > I am getting 500% to 3% increase in latency. You seem to be under the illusion that "stable" is a control knob for a function result cache. It is not, and never has been. Postgres doesn't do function result caching. If you've constructed your app in such a way that it depends on not inlining SQL set-returning functions, it's fairly easy to defeat that. >From memory, marking a SRF as either strict or volatile will do it (although volatile might cause you problems elsewhere --- I suspect your design is pretty brittle in this area). 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: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE
On Wed, Aug 11, 2010 at 11:01 AM, Brian Ceccarelli wrote: > My complaint remains. That inlined function f_return_ver_id_4() is a > STABLE function, inlined or not. Postgres now calls it multiple times during > the transaction, even though the arguments to f_return_ver_id_4() have not > changed. > > STABLE no longer means STABLE. This behavior is killing my performance. > I am getting 500% to 3% increase in latency. We've never guaranteed that, and almost certainly never will. Marking a function STABLE means that the planner is *allowed to assume* that the results won't change for a given set of arguments, not that it is *required to prevent* it from being called multiple times with the same set of arguments. You can certainly prevent the function from being inlined, though (perhaps, by writing it in PL/pgsql). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE
OK. The documentation says "allows the optimizer to optimize . . . ."But then the example guarantees the one-time-only for a index scan condition. From the documentation:8.4.4 Chapter 32 and 8.2.17 Chapter 33. .A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. This category allows the optimizer to optimize multiple calls of the function to a single call. In particular, it is safe to use an expression containing such a function in an index scan condition. (Since an index scan will evaluate the comparison value only once, not once at each row, it is not valid to use a VOLATILE function in an index scan condition.) The behavior of the optimizers <= 8.2 certainly fit the description. The 8.4 behavior is vastly different. I recommend that somebody change the documentation to say, "This category allows, but does not guarantee, the optimizer to optimize multiple calls . . . ." That would be more clear.And then mention the inlining deal, if you haven't already. There remains the problem with the now() function. A SQL function repetitively calls now(). Is that what you intended? There remains the problem with PGAdmin memory leak. I will change my SQL functions to PL/PGSQL functions. I am glad that there is a solution. Thank you for your help. -Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Wednesday, August 11, 2010 11:33 AM To: Brian Ceccarelli Cc: Tom Lane; pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE On Wed, Aug 11, 2010 at 11:01 AM, Brian Ceccarelli wrote: > My complaint remains. That inlined function f_return_ver_id_4() is a > STABLE function, inlined or not. Postgres now calls it multiple times during > the transaction, even though the arguments to f_return_ver_id_4() have not > changed. > > STABLE no longer means STABLE. This behavior is killing my performance. > I am getting 500% to 3% increase in latency. We've never guaranteed that, and almost certainly never will. Marking a function STABLE means that the planner is *allowed to assume* that the results won't change for a given set of arguments, not that it is *required to prevent* it from being called multiple times with the same set of arguments. You can certainly prevent the function from being inlined, though (perhaps, by writing it in PL/pgsql). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5612: Database Integrity
Excerpts from Ramachandran S's message of mié ago 11 05:36:31 -0400 2010: > I have observed that if a table exceeds size (default installation) 1 GB. > The table when it exceeds 1 GB a file is created say 1811(table) with 1811.1 > and further records added are insertted into this file. My Question if I > remove the file 1811.1 say still database is starting up? You're not supposed to mess with the underlying files. If you randomly delete files, it's your problem. No, we don't have pgfsck. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5607: memmory leak in ecpg
[moving discussion to -hackers] Michael Meskes wrote: > "Kevin Grittner" schrieb: >>"Marcelo Mas" wrote: >> >>> Valgrind reports memmory leak when getting decimal data. >> >>I wonder how much overlap there is between this and the patch for >>fixing ECPG memory leaks offered by Zoltán Böszörményi three days >>ago. >>http://archives.postgresql.org/pgsql-hackers/2010-08/msg00115.php > I guess the described problems are identical. Feel free to apply > the memleal patch. Is someone dealing with this? Should this be on the "PostgreSQL 9.0 Open Items" Wiki page? -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] postgres 9.0 beta libpq empty binary array error
On 09/08/10 21:29, Heikki Linnakangas wrote: On 09/08/10 21:26, Tom Lane wrote: Heikki Linnakangas writes: The behavior of empty arrays with dimensions is weird in general. Agreed, but we shouldn't be introducing random restrictions in the name of security. Patch looks good to me, except that it occurs to me to wonder about negative values of dim[i]. For small negative values this coding will catch it, but what if it's large enough to overflow the other way? Maybe use if (dim[i]< 0 || lBound[i]> ub) ereport... ArrayGetNItems checks for dim[i] < 0. I concur though that it looks weird to not check that along with the overflow check, so maybe we should, just to make the code clearer. Committed. I added a comment noting that dim[i] < 0 is checked in ArrayGetNItems. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Lucian Capdefier wants to stay in touch on LinkedIn
LinkedIn I'd like to add you to my professional network on LinkedIn. - Lucian Capdefier Lucian Capdefier Business Analytics and Optimization Service Line Leader at IBM Global Business Services Romania Confirm that you know Lucian Capdefier https://www.linkedin.com/e/-fsh06j-gcql2w64-19/isd/1554309907/uWsHY63D/ -- (c) 2010, LinkedIn Corporation
Re: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE
On Wed, Aug 11, 2010 at 11:50 AM, Brian Ceccarelli wrote: > OK. The documentation says "allows the optimizer to optimize . . . ." > But then the example guarantees the one-time-only for a index scan condition. > > From the documentation: 8.4.4 Chapter 32 and 8.2.17 Chapter 33. > > .A STABLE function cannot modify the database and is guaranteed to return > the same results given the same arguments for all rows within a single > statement. This category allows the optimizer to optimize multiple calls of > the function to a single call. In particular, it is safe to use an expression > containing such a function in an index scan condition. (Since an index scan > will evaluate the comparison value only once, not once at each row, it is not > valid to use a VOLATILE function in an index scan condition.) > > The behavior of the optimizers <= 8.2 certainly fit the description. The > 8.4 behavior is vastly different. Reading between the lines, I think I sense that this has got you pretty frustrated, so in defense of the new behavior, let me just mention that, in general, inlining SQL queries results in a HUGE performance benefit. It's sort of unfortunate that it doesn't work out that way for you in this case, but I don't think it's a bad idea in general. *thinks* In theory, the optimization Brian wants is possible here, right? I mean, you could replace the functional call with a Param, and pull the Param out and make it an InitPlan. Seems like that would generally be a win, if you figure to loop more than once and the execution cost of the function is not too tiny. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE
Brian Ceccarelli writes: > OK. The documentation says "allows the optimizer to optimize . . . ." > But then the example guarantees the one-time-only for a index scan condition. > No, the documentation states that *if* an index scan is used, functions involved in the indexscan's qual condition will be evaluated just once, rather than once per row. There is no "guarantee" of any sort that such a plan will be chosen. The point of the STABLE marking is to inform the optimizer that it is safe to choose an index scan because the function's results will not change compared to the naive SQL semantics wherein the WHERE condition is evaluated for each row. Thus, the guarantee actually runs the other way: you are promising the optimizer that your function doesn't have side effects or change its results intra-query. 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
[BUGS] BUG #5613: cannot delete
The following bug has been logged online: Bug reference: 5613 Logged by: Scott Email address: wheels7...@hotmail.com PostgreSQL version: 8.4 Operating system: vista Description:cannot delete Details: I have recently tried to install PostgreSQL to use with poker tracker. I have having problems connecting to the server, where then i was advised to completly remove and download again. I have come accross a file located in c:\programfilmes, called PostgresSQL. I am unable to delete this folder file, even when using CMD it says the file cannot be found. I then proceeded to open this file and delete the items individually to see what was left, all the files were able to delete except one: pg_stat_tmp Please could you help with this as i have spoke to comuter technichian company and they were unable to remove the file. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE
1. Basically, I was assuming that "STABLE" was more than just a optimizer "hint". 2. Is it not better to evaluate a STABLE function once, and pass the returned constant to a function, than pass the function itself and inline it? Is not passing a constant more efficient than even an inline query? Please show me an example where an inline query gets a performance boost. Thanks! -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Wednesday, August 11, 2010 4:34 PM To: Brian Ceccarelli Cc: Robert Haas; pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE Brian Ceccarelli writes: > OK. The documentation says "allows the optimizer to optimize . . . ." > But then the example guarantees the one-time-only for a index scan condition. > No, the documentation states that *if* an index scan is used, functions involved in the indexscan's qual condition will be evaluated just once, rather than once per row. There is no "guarantee" of any sort that such a plan will be chosen. The point of the STABLE marking is to inform the optimizer that it is safe to choose an index scan because the function's results will not change compared to the naive SQL semantics wherein the WHERE condition is evaluated for each row. Thus, the guarantee actually runs the other way: you are promising the optimizer that your function doesn't have side effects or change its results intra-query. 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: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE
Robert Haas writes: > In theory, the optimization Brian wants is possible here, right? I > mean, you could replace the functional call with a Param, and pull the > Param out and make it an InitPlan. Seems like that would generally be > a win, if you figure to loop more than once and the execution cost of > the function is not too tiny. Yeah, possibly. It would probably be difficult for the planner to figure out where the cutover point is to make that worthwhile, though; the point where you'd need to make the transformation is long before we have any rowcount estimates. I was about to suggest that Brian could make that happen manually by writing ... FROM srf((SELECT expensive_func())) ... but I think actually that will just amount to another way of disabling inlining. 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: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE
Excerpts from Brian Ceccarelli's message of mié ago 11 16:47:50 -0400 2010: > Please show me an example where an inline query gets a performance boost. The reason it's a performance boost is that the query gets to be planned as a single query, instead of there being a black-box that needs to be planned separately. I don't have any example handy to share. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE
On Wed, Aug 11, 2010 at 4:47 PM, Brian Ceccarelli wrote: > Please show me an example where an inline query gets a performance boost. Sure. rhaas=# create table example as select a from generate_series(1,10) a; SELECT 10 rhaas=# alter table example add primary key (a); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "example_pkey" for table "example" ALTER TABLE rhaas=# create function f() returns setof int as $$select a from example$$ language sql stable; rhaas=# explain analyze select * from f() where f = 1; QUERY PLAN -- Index Scan using example_pkey on example (cost=0.00..8.28 rows=1 width=4) (actual time=0.102..0.103 rows=1 loops=1) Index Cond: (a = 1) Total runtime: 0.149 ms (3 rows) rhaas=# alter function f() volatile; ALTER FUNCTION rhaas=# explain analyze select * from f() where f = 1; QUERY PLAN --- Function Scan on f (cost=0.25..12.75 rows=5 width=4) (actual time=34.585..51.972 rows=1 loops=1) Filter: (f = 1) Total runtime: 63.277 ms (3 rows) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5588: I use a lot of the "INHERITS", results of tests found that the performance is very low.
On Sun, Aug 1, 2010 at 10:16 PM, runner.mei wrote: > > The following bug has been logged online: > > Bug reference: 5588 > Logged by: runner.mei > Email address: runner@gmail.com > PostgreSQL version: 8.4.4 > Operating system: windows > Description: I use a lot of the "INHERITS", results of tests found > that the performance is very low. > Details: > > Hello, I try to build a cmdb database with using postgresql , I use a lot of > the "INHERITS", results of tests found that the performance is very low, > there are 2,000,000 pieces of data when the data when a data query, it was > however spent 1672 ms, I was wrong it? Please refer to the following wiki page for information about getting help with this problem. http://wiki.postgresql.org/wiki/SlowQueryQuestions You'll need to provide more details to get help, and it would also be a good idea to post to the correct mailing list, which is pgsql-performance. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Re: BUG #5602: Recovering from Hot-Standby file backup leads to the currupted indexes
On Fri, Aug 6, 2010 at 7:50 AM, Simon Riggs wrote: > The procedure used does differ from that documented. However, IMHO the > procedure *documented* is *not* safe and could lead to corrupt indexes > in the way described, since the last recovered point might be mid-way > between two halves of an index split record, which will never be > corrected during HS. An index split record is replayed by two calls of rm_redo()? If not, we don't need to worry about the above since the last recovered point which pg_last_xlog_replay_location() returns is updated after every rm_redo(). Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Re: BUG #5602: Recovering from Hot-Standby file backup leads to the currupted indexes
Fujii Masao writes: > On Fri, Aug 6, 2010 at 7:50 AM, Simon Riggs wrote: >> The procedure used does differ from that documented. However, IMHO the >> procedure *documented* is *not* safe and could lead to corrupt indexes >> in the way described, since the last recovered point might be mid-way >> between two halves of an index split record, which will never be >> corrected during HS. > An index split record is replayed by two calls of rm_redo()? If not, > we don't need to worry about the above since the last recovered point > which pg_last_xlog_replay_location() returns is updated after every > rm_redo(). Yeah, I thought that was bogus too. If we're following a live master, the second xlog record should be along shortly, and in any case queries will give the correct result in between. The problem is only interesting if the WAL series ends and we have to cons up the split completion by ourselves; but the logic to do that does exist. What was bothering me about the procedure is that it's not clear when the new slave has reached consistency, in the sense of having used WAL to clean up any out-of-sync conditions in the base backup it was started from. So you can't be sure when it's okay to begin treating it as a trustworthy backup or potential master. We track the minimum safe recovery point for normal PITR recovery cases, but that mechanism isn't available for slaves cloned according to this procedure. So the DBA is just flying blind as to whether the slave is trustworthy yet. I can't prove that that's what burnt the original complainant, but it fits the symptoms. 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
