[GENERAL] Another RAID controller recommendation question
We're looking to deploy a bunch of new machines. Our DB is fairly small and write-intensive. Most of the disk traffic is PG WAL. Historically we've avoided RAID controllers for various reasons, but this new deployment will be done with them (also for various reasons ;) We like to use white-boxish machines and we run CentOS. This would be a good example of the kind of machine we'd buy: http://www.newegg.com/Product/Product.aspx?Item=N82E16816101339 manufacturer page : http://www.supermicro.com/products/system/1U/6016/SYS-6016T-URF4_.cfm?UIO=N these boxes have a proprietary controller slot, with these cards: http://www.supermicro.com/products/nfo/UIO.cfm#Adapters specifically this LSI-based one which seems to be the newest/fastest, with BBWBC: http://www.supermicro.com/products/accessories/addon/AOC-USAS2LP-H8iR.cfm I'd be interested to hear any options good or bad on these controllers, or ideas for alternatives. These machines are operated in a lights-out mode, and will handle heavy constant load (hundreds of write txn/s) with 15K SAS drives in a RAID-1 setup (2 drives, or 2 + 2 with data and WAL split between spindle groups). Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 2 questions re RAID
On 06/17/2011 01:02 PM, Scott Ribe wrote: 1) Is my impression correct that given a choice between Areca& Highpoint, it's a no-brainer to go with Areca? I guess you could call Highpoint a RAID manufacturer, but I wouldn't do so. They've released so many terrible problems over the years that it's hard to take the fact that they may have something reasonable you can buy now (the 43XX cards I think?) seriously. And, in further digging, I discover that gh is an option for me. Anyone got comments on these? (I notice that they use ultracapacitor/flash to protect cache...) Atto is so Mac focused that you're not going to find much experience here, for the same reason you didn't get any response to your original question. Their cards are using the same Intel IO Processor (IOP) hardware as some known capable cards. For example, the ExpressSAS R348 is named that because it has an Intel 348 IOP. That's the same basic processor as on the medium sized Areca boards: http://www.areca.us/products/pcietosas1680series.htm So speed should be reasonable, presuming they didn't make any major errors in board design or firmware. The real thing you need to investigate is whether the write cache setup is done right, and whether monitoring is available in a way you can talk to. What you want is for the card to run in write-back mode normally, degrading to write-through when the battery stops working well. If you don't see that sort of thing clearly documented as available, you really don't want to consider their cards. 2) I understand why RAID 5 is not generally recommended for good db performance. But if the database is not huge (10-20GB), and the server has enough RAM to keep most all of the db cached, and the RAID uses (battery-backed) write-back cache, is it sill really an issue? You're basically asking "if I don't write to the database, does the fact that write performance on RAID5 is slow matter?" When asked that way, sure, it's fine. If after applying the write cache to help, your write throughput requirements don't ever exceed what a single disk can provide, than maybe RAID5 will be fine for you. Make sure you keep shared_buffers low though, because you're not going to be able to absorb a heavy checkpoint sync on RAID5. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to find a tablespace for the table?
On 06/17/2011 06:50 PM, hyelluas wrote: I'm looking into pg_tables view and only one tablespace is displayed is pg_global. All my tables are created in my custom tablespace and that column is empty for them. I'm not sure what's wrong here, but the query you are trying to use to decode this information doesn't look quite right. pg_tables is just a regular query; here is its source code: CREATE VIEW pg_tables AS SELECT N.nspname AS schemaname, C.relname AS tablename, pg_get_userbyid(C.relowner) AS tableowner, T.spcname AS tablespace, C.relhasindex AS hasindexes, C.relhasrules AS hasrules, C.relhastriggers AS hastriggers FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace) WHERE C.relkind = 'r'; I think that if you start with this and try to experiment from there, you may be able to figure out what's going on here a little better. This connects up the main relevant tables in the right way. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade only to 9.0 ?
Iain Barnett wrote: > Hi, > > I'm currently running 8.4.4. I downloaded the source for 9.0.4 and > installed it, and then installed pg_upgrade and ran it, and got the > following message: > > > This utility can only upgrade to PostgreSQL version 9.0. > > It seems strange to me that it can only upgrade to that and not 4 patch > points above, but still, so I go to the source directory to download > 9.0 and it isn't listed. > > Could anyone tell me how I'm supposed to get this to work please? I'd > be really grateful. Very late reply, but can I see the command you are running? Odds are you are pointing to 8.4 as the new database instead of 9.0.4. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] merge in postgres trigger function
Hello PostgreSQL doesn't support MERGE statement yet Regards Pavel Stehule 2011/6/17 Leon Match : > Hello, > > > > I am trying to move few objects to postgres from oracle. > > > > I have an issue with a trigger, which has a merge inside? > > > > Here is my code: > > BEGIN > > MERGE INTO Requests r > > using (select > new.web_form_id web_form_id, > > > new.form_type form_type, > > > new.submit_date submit_date, > > > new.email email, > > > new.custom_fields custom_fields > > > from DUAL) w > > on > (r.request_id = new.web_form_id) > > when not matched > > THEN > > > insert ( > > > r.request_id, > > > r.form_type, > > > r.submit_date, > > > r.request_email, > > > r.request_description > > > ) > > values ( > > > w.web_form_id, > > > w.form_type, > > > w.submit_date, > > > w.email, > > > w.custom_fields > > > ) > > when matched > > then > > update set > > form_type = > NVL (w.form_type, r.form_type), > > submit_date > = NVL (w.submit_date, r.submit_date), > > > request_email = NVL (w.email, r.request_email), > > > request_description = NVL (w.custom_fields, r.request_description); > > end if; > > END; > > > > I receive an error – “Requests is not a known variable, Line 3: MERGE INTO > Requests r” > > > > But “Requests” is a table is not a variable! > > What is wrong with my statement? May be Merge is not legal in postgres > trigger functions? > > What would be the way around? > > > > Thank you, > > Leon > > leon.ma...@convergia.net > > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Odd performance difference in check constraint : SQL(slow) vs plpgsql(fast)
bubba postgres writes: > This is the reverse of what I thought I would find. > In short my check constraint is extracting the epoch from a start timestamp, > and an end timestamp to get the number of seconds difference. > It then uses this number to check the array_upper() of an array to make sure > it's the proper size > The SQL version uses a case statement, and the plpgsql uses an IF/ELSE > In a particular insert test > The plpgsql version adds 1 second over the no constraints case. > the sql version adds 10 seconds over the no constraints case. > Why would this be? It would not likely be faster unless it can be inlined, and maybe not even then, because of plan caching effects (plpgsql is a lot better about that). In this particular case, I'm suspicious whether all the operations are immutable; if they aren't, the marking of the function as immutable will definitely prevent inlining. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint to ensure value does NOT exist in another table?
I know I can setup a FK constraint to make sure Table1.ColA exists in Table2.Key, however what if I want to do the reverse? I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do this with any sort of CHECK constraint, trigger, custom function, etc? Thanks! >>> >>> >>> Perhaps it's possible to use a unique constraint in a third table to >>> guarantee those foreign keys can never have the same value. That would >>> probably be more efficient than executing stored procedure code. >> >> You'd still have to use a TRIGGER to insert any new or updated values >> into the third table. Otherwise, you'd have to modify a bunch of code >> to insert/update the keys into the third table and that somewhat goes >> against the whole idea of making the database responsible for its own >> integrity in the first place. > > > No you don't. > > If Table1.ColA is an FK to Table3.ColA and Table2.ColA is also an FK to > Table3.ColA, you can put a unique constraint on Table3.ColA to make sure the > values are unique: > Table1 Table3 Table2 > -- -- -- > ColA >---|- ColA -|---< ColA > > If you insert a value in either Table1 or Table2, it first HAS to exist in > Table3, due to the FK constraints. However, that still allows for values that > are in both tables 1 and 3, just pointing to the same value in Table3. > > To solve that you add an extra column to all tables, for example: > ALTER TABLE Table1 ADD src CHAR(1) DEFAULT 'A'; > ALTER TABLE Table2 ADD src CHAR(1) DEFAULT 'B'; > And you change the FK constraints in A and B to include "src": > > Table1 Table3 Table2 > -- -- -- > ColA >---|- ColA -|---< ColA > src >-/ \-|- src -|-/ \-< src > > You also add back a UNIQUE constraint over Table3.ColA (without the "src" > column). > > Now, if you add a value to Table1, it requires a value of (ColA, 'A') in > Table3. If you add one to Table2, it requires a value of (ColA, 'B'). If > either of those already exist though, you violate the UNIQUE constraint on > Table3.ColA. > > It's probably convenient to write some triggers to auto-generate the records > in Table3, but those triggers are NOT needed for relational integrity - they > just make the task easier. Yup yup, I see where you're going.. It's like the third table is a "name broker" that grants the unique priveledge of using a name in the database. The other tables will have a FK on it so you'd have to add that name to the table before it can be inserted elsewhere. The third table will be unique which ensures a name is only used once. This would work great, however I'd have to modify a bunch of code to insert a name into the third table before it could be used.. Since an admin tool is the only thing that would be doing this (this data hardly ever changes), this isn't out of the question. I actually don't need any TRIGGERS if I do this, I just need to modify some code. This design will ensure my data is always in a valid state. A fine approach. Thanks! Mike -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to find a tablespace for the table?
hello, I'm looking into pg_tables view and only one tablespace is displayed is pg_global. All my tables are created in my custom tablespace and that column is empty for them. select * from pg_tablespace show my tablespace, pgAdmin shows that tablespace for each table. I need to query the data dictionary to find the tablespace for the table in my proc. select t.spcname, c.relname AS tablename from pg_class c , pg_tablespace t where t.oid = c.reltablespace does not show my tables, only the dd tables. SELECT COALESCE(tbs.spcname, '*') AS tbsname FROM pg_catalog.pg_class AS t JOIN pg_catalog.pg_namespace AS s ON (s.oid = t.relnamespace) LEFT OUTER JOIN pg_catalog.pg_tablespace AS tbs ON (tbs.oid = t.reltablespace) WHERE t.relname like 'summ%' AND s.nspname = 'public'; returns * please help. thank you. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4500200.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Odd performance difference in check constraint : SQL(slow) vs plpgsql(fast)
This is the reverse of what I thought I would find. In short my check constraint is extracting the epoch from a start timestamp, and an end timestamp to get the number of seconds difference. It then uses this number to check the array_upper() of an array to make sure it's the proper size The SQL version uses a case statement, and the plpgsql uses an IF/ELSE In a particular insert test The plpgsql version adds 1 second over the no constraints case. the sql version adds 10 seconds over the no constraints case. Why would this be? ---> CREATE OR REPLACE FUNCTION check_end_time_foo( _start_time TIMESTAMP, _end_time TIMESTAMP, _granularity SMALLINT, _values DOUBLE PRECISION[] ) RETURNS boolean AS $$ BEGIN if( _granularity = 5 ) THEN return( EXTRACT( EPOCH FROM _end_time at time zone 'utc' )::BIGINT - EXTRACT( EPOCH FROM _start_time at time zone 'utc' )::BIGINT = 60 * array_upper( _values,1 ) ); ELSEIF( _granularity = 7 ) THEN return( EXTRACT( EPOCH FROM _end_time at time zone 'utc' )::BIGINT - EXTRACT( EPOCH FROM _start_time at time zone 'utc' )::BIGINT = 900 * array_upper( _values,1 ) ); ELSEIF( _granularity = 9 ) THEN return( EXTRACT( EPOCH FROM _end_time at time zone 'utc' )::BIGINT - EXTRACT( EPOCH FROM _start_time at time zone 'utc' )::BIGINT = 3600 * array_upper( _values,1 ) ); ELSEIF( _granularity = 12 ) THEN return( ( (EXTRACT( YEAR FROM (_end_time at time zone 'utc') at time zone 'utc' )::INT * 12) + EXTRACT( MONTH FROM (_end_time at time zone 'utc' ) at time zone 'utc' )::INT ) - ( (EXTRACT( YEAR FROM (_start_time at time zone 'utc') at time zone 'utc' )::INT * 12) + EXTRACT( MONTH FROM (_start_time at time zone 'utc') at time zone 'utc' )::INT ) = array_upper( _values,1 ) ); END IF; END; $$ language plpgsql IMMUTABLE; alter table timeseries add CONSTRAINT timeseries_valid_end_time CHECK( check_end_time_foo( series_start_time, series_end_time, granularity, data_value ) ); -vs- alter table timeseries add CONSTRAINT timeseries_valid_end_time CHECK( CASE WHEN granularity = 5 THEN EXTRACT( EPOCH FROM series_end_time at time zone 'utc' )::BIGINT - EXTRACT( EPOCH FROM series_start_time at time zone 'utc' )::BIGINT = 60 * array_upper( data_value,1 ) WHEN granularity = 7 THEN EXTRACT( EPOCH FROM series_end_time at time zone 'utc' )::BIGINT - EXTRACT( EPOCH FROM series_start_time at time zone 'utc' )::BIGINT = 900 * array_upper( data_value,1 ) WHEN granularity = 9 THEN EXTRACT( EPOCH FROM series_end_time at time zone 'utc' )::BIGINT - EXTRACT( EPOCH FROM series_start_time at time zone 'utc' )::BIGINT = 3600 * array_upper( data_value,1 ) WHEN granularity = 12 THEN ((EXTRACT( YEAR FROM ( series_end_time at time zone 'utc') at time zone 'utc' )::INT * 12) + EXTRACT( MONTH FROM ( series_end_time at time zone 'utc' ) at time zone 'utc' )::INT ) - ( (EXTRACT( YEAR FROM ( series_start_time at time zone 'utc') at time zone 'utc' )::INT * 12) + EXTRACT( MONTH FROM ( series_start_time at time zone 'utc') at time zone 'utc' )::INT ) = array_upper( data_value,1 ) ELSE false END );
[GENERAL] merge in postgres trigger function
Hello, I am trying to move few objects to postgres from oracle. I have an issue with a trigger, which has a merge inside? Here is my code: BEGIN MERGE INTO Requests r using (select new.web_form_id web_form_id, new.form_type form_type, new.submit_date submit_date, new.email email, new.custom_fields custom_fields from DUAL) w on (r.request_id = new.web_form_id) when not matched THEN insert ( r.request_id, r.form_type, r.submit_date, r.request_email, r.request_description ) values ( w.web_form_id, w.form_type, w.submit_date, w.email, w.custom_fields ) when matched then update set form_type = NVL (w.form_type, r.form_type), submit_date = NVL (w.submit_date, r.submit_date), request_email = NVL (w.email, r.request_email), request_description = NVL (w.custom_fields, r.request_description); end if; END; I receive an error - "Requests is not a known variable, Line 3: MERGE INTO Requests r" But "Requests" is a table is not a variable! What is wrong with my statement? May be Merge is not legal in postgres trigger functions? What would be the way around? Thank you, Leon leon.ma...@convergia.net
Re: [GENERAL] Are check constraints always evaluated on UPDATE?
bubba postgres writes: > Are there any optimizations around check constraints such that they will not > be evaluated if constituent columns are not updated? Nope. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Are check constraints always evaluated on UPDATE?
Are there any optimizations around check constraints such that they will not be evaluated if constituent columns are not updated? Regards, -JD
Re: [GENERAL] 2 questions re RAID
On Fri, Jun 17, 2011 at 11:35 AM, Scott Ribe wrote: > It's small enough that there's some other things going on at the same small > server with 4 disk bays ;-) My thinking was that write-back cache might > mitigate the poor write performance enough to not be noticed. This db doesn't > generally get big batch updates anyway, it's mostly a constant stream of > small updates coming in and I have a hard time imagining 256MB of cache > filling up very often. (I have at least a fuzzy understanding of how WAL > segments affect the write load.) We run our internal dev server on RAID-6 and it works well enough. Again, like your usage case, it doesn't get beat up too hard, so RAID-6 works fine. I prefer RAID-6 because it doesn't degrade as bad as RAID-5 when a single drive fails, and of course it's still fully redundant with a single drive failure. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 2 questions re RAID
On Jun 17, 2011, at 11:20 AM, Scott Marlowe wrote: > Generally, yes, but the model of the card is more important than the > maker. I.e. an Areca 1880 or 1680 is a fantastic performer. But the > older 1120 series aren't gonna set the world on fire or anything. And, in further digging, I discover that ATTO ExpressSAS is an option for me. Anyone got comments on these? (I notice that they use ultracapacitor/flash to protect cache...) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On 17 June 2011 04:44, Robert Haas wrote: > On Wed, Feb 9, 2011 at 4:50 AM, Thom Brown wrote: >> On 9 February 2011 02:11, Robert Haas wrote: >>> On Tue, Feb 8, 2011 at 8:30 PM, Andrew Dunstan wrote: Quite right, but the commitfest manager isn't meant to be a substitute for one. Bug fixes aren't subject to the same restrictions of feature changes. >>> >>> Another option would be to add this here: >>> >>> http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Open_Items >> >> I've removed it from the commitfest because it really doesn't belong >> there, and I've added it to the open items list. > > So, I finally got around to look at this, and I think there is a > simpler solution. When an overflow occurs while calculating the next > value, that just means that the value we're about to return is the > last one that should be generated. So we just need to frob the > context state so that the next call will decide we're done. There are > any of number of ways to do that; I just picked what looked like the > easiest one. I knew there'd be a much simpler way of solving this. Works for me. Thanks Robert. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
Robert Haas writes: > So, I finally got around to look at this, and I think there is a > simpler solution. When an overflow occurs while calculating the next > value, that just means that the value we're about to return is the > last one that should be generated. So we just need to frob the > context state so that the next call will decide we're done. There are > any of number of ways to do that; I just picked what looked like the > easiest one. +1 for this solution. BTW, there was some mention of changing the timestamp versions of generate_series as well, but right offhand I'm not convinced that those need any change. I think you'll get overflow detection there automatically from the functions being used --- and if not, it's a bug in those functions, not in generate_series. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
> > On Wed, Feb 9, 2011 at 4:50 AM, Thom Brown wrote: > > On 9 February 2011 02:11, Robert Haas wrote: > >> On Tue, Feb 8, 2011 at 8:30 PM, Andrew Dunstan > wrote: > >>> Quite right, but the commitfest manager isn't meant to be a > >>> substitute for one. Bug fixes aren't subject to the same restrictions of > feature changes. > >> > >> Another option would be to add this here: > >> > >> http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Open_Items > > > > I've removed it from the commitfest because it really doesn't belong > > there, and I've added it to the open items list. > > So, I finally got around to look at this, and I think there is a simpler solution. > When an overflow occurs while calculating the next value, that just means > that the value we're about to return is the last one that should be generated. > So we just need to frob the context state so that the next call will decide > we're done. There are any of number of ways to do that; I just picked what > looked like the easiest one. > Tangential comment but have you considered emitting a warning (and/or log entry) when you are 10,000-50,000 away from issuing the last available number in the sequence so that some recognition exists that any code depending on the sequence is going to fail soon? Also, during sequence creation you know the integer type being used so that maximum value is known and an overflow should not need to come into play (I guess the trade-off is the implicit "try-catch" [or whatever mechanism C uses] performance hit versus the need to store another full integer in the data structure). You could also give access to the "warning threshold" value so that the developer can change it to whatever value is desired (with a meaningful default of course). David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On Fri, Jun 17, 2011 at 10:39 AM, David Johnston wrote: > Tangential comment but have you considered emitting a warning (and/or log > entry) when you are 10,000-50,000 away from issuing the last available > number in the sequence so that some recognition exists that any code > depending on the sequence is going to fail soon? > > Also, during sequence creation you know the integer type being used so that > maximum value is known and an overflow should not need to come into play (I > guess the trade-off is the implicit "try-catch" [or whatever mechanism C > uses] performance hit versus the need to store another full integer in the > data structure). > > You could also give access to the "warning threshold" value so that the > developer can change it to whatever value is desired (with a meaningful > default of course). There are already tools out there that can monitor this stuff - for example, check_postgres.pl. http://bucardo.org/check_postgres/check_postgres.pl.html#sequence We tend to avoid emitting warnings for this kind of thing because they can consume vast amounts of disk space, and a lot of times no one's looking at them anyway. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
Robert Haas writes: > On Fri, Jun 17, 2011 at 2:15 PM, Tom Lane wrote: >> BTW, there was some mention of changing the timestamp versions of >> generate_series as well, but right offhand I'm not convinced that >> those need any change. I think you'll get overflow detection there >> automatically from the functions being used --- and if not, it's a >> bug in those functions, not in generate_series. > Maybe not, because those functions probably throw an error if an > overflow is detected, and that's not really correct. Oh, good point. > I'm not sure how much energy it's worth expending on that case. Using > really large dates may be less common that using values that strain > the range of a 4-byte integer. But it might at least be worth a TODO. Yeah, I can't get excited about it either; restructuring that code enough to avoid an error seems like a lot more work than the case is worth. Maybe someday somebody will hit the case in practice and then be motivated to work on it, but in the meantime ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On Fri, Jun 17, 2011 at 2:15 PM, Tom Lane wrote: > Robert Haas writes: >> So, I finally got around to look at this, and I think there is a >> simpler solution. When an overflow occurs while calculating the next >> value, that just means that the value we're about to return is the >> last one that should be generated. So we just need to frob the >> context state so that the next call will decide we're done. There are >> any of number of ways to do that; I just picked what looked like the >> easiest one. > > +1 for this solution. > > BTW, there was some mention of changing the timestamp versions of > generate_series as well, but right offhand I'm not convinced that > those need any change. I think you'll get overflow detection there > automatically from the functions being used --- and if not, it's a > bug in those functions, not in generate_series. Maybe not, because those functions probably throw an error if an overflow is detected, and that's not really correct. By definition, the second generate_series() is the point at which we should stop generating, and that point has to be within the range of the underlying data type, by definition. So if an overflow occurs, that's just another way of saying that we've certainly gone past the stop point and needn't generate anything further. The error is an artifact of the method we've used to generate the next point. I'm not sure how much energy it's worth expending on that case. Using really large dates may be less common that using values that strain the range of a 4-byte integer. But it might at least be worth a TODO. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Stumped on windowing
I'm working with a product that uses effective date based data structures. We then create views using analytic functions that have begin and end dates for when that record was valid. This works fine when there is just one record per item that is valid at any given time (for instance job assignment details). But now I have a table where multiple rows can be valid for a given date and it is giving me grief. This particular table is about job funding and at any given time a job may be funded out of several accounts. Here is a simplified example of the data: EMP_ID POSN EFF_DATE FUND ORG PCT DENSE_RANK 56332 001071 2010-07-01 22086 182030 4.00 1 56332 001071 2010-07-01 24095 184001 10.00 1 56332 001071 2010-07-01 22065 182024 20.00 1 56332 001071 2010-07-01 14001 000121 26.00 1 56332 001071 2010-07-01 22088 182031 40.00 1 56332 001071 2010-12-01 24095 184001 14.00 2 56332 001071 2010-12-01 14001 000121 21.00 2 56332 001071 2010-12-01 22065 182024 25.00 2 56332 001071 2010-12-01 22088 182031 40.00 2 56332 001071 2011-04-01 22086 182030 4.00 3 56332 001071 2011-04-01 24095 184001 10.00 3 56332 001071 2011-04-01 22088 182031 40.00 3 56332 001071 2011-04-01 22065 182024 46.00 3 So my initial attempt was to do something like : LEAD(eff_date - 1) OVER (PARTITION BY emp_id, posn, fund, org ORDER BY eff_date) But that wont work in the above example because the fund 22086 drops off completely on 12/01 and returns 4/01. So if I used that approach the result would be wrong between 12/01 and 4/01. What I really need is the value from the next window but nothing seems to work that way. What I need is something like: MIN ( CASE WHEN eff_date > CURRENT_ROW.eff_date THEN eff_date END ) OVER ( PARTITION BY emp_id, posn ORDER BY eff_date) <- a way not to include the current value MIN(eff_date) OVER (PARTITION BY emp_id, posn ORDER BY eff_date RANGE UNBOUNDED FOLLOWING) <- not include current row MIN(eff_date) OVER(PARTITION BY emp_id, posn ORDER BY eff_date RANGE BETWEEN CURRENT ROW + 1 AND UNBOUNDED FOLLOWING) BTW, here is the correctly working dense rank part: DENSE_RANK() OVER (PARTITION BY emp_id, posn ORDER BY eff_date) Is there any way to do this with analytic functions or am I going to have to resort to a subquery? Scott Bailey
Re: [GENERAL] 2 questions re RAID
Thanks much for the specific info on Areca RAID cards. Very helpful. On Jun 17, 2011, at 11:20 AM, Scott Marlowe wrote: > The problem with RAID-5 is crappy write performance. Being big or > small won't change that. Plus if the db is small why use RAID-5? It's small enough that there's some other things going on at the same small server with 4 disk bays ;-) My thinking was that write-back cache might mitigate the poor write performance enough to not be noticed. This db doesn't generally get big batch updates anyway, it's mostly a constant stream of small updates coming in and I have a hard time imagining 256MB of cache filling up very often. (I have at least a fuzzy understanding of how WAL segments affect the write load.) RAID-1 & RAID-10 are not ruled out, I'm just exploring options. And I'm not actually wanting to use RAID 5; it's RAID 6 that I'm considering... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 2 questions re RAID
On Fri, Jun 17, 2011 at 11:02 AM, Scott Ribe wrote: > No responses to my earlier post, I'm assuming because OS X experience is > rather thin in this group ;-) So a couple of more specific questions: > > 1) Is my impression correct that given a choice between Areca & Highpoint, > it's a no-brainer to go with Areca? Generally, yes, but the model of the card is more important than the maker. I.e. an Areca 1880 or 1680 is a fantastic performer. But the older 1120 series aren't gonna set the world on fire or anything. Pluses for the Arecas I've used: Out Of Band monitoring. Heck, I've updated the firmware on them from 1000 miles away. fast in RAID-10. Lots of HW controllers (I'm looking at you, LSI) perform poorly with layered RAID. They all use the same simple standard battery backed unit, unlike some manufacturers that glue them onto the DIMM so you have to buy a new memory module to replace your BBU (again, I'm looking at you LSI) Great UI via the web and / or the BIOS. Again, some other RAID setup utils are not so nice (and again, I'm looking at you, LSI) > 2) I understand why RAID 5 is not generally recommended for good db > performance. But if the database is not huge (10-20GB), and the server has > enough RAM to keep most all of the db cached, and the RAID uses > (battery-backed) write-back cache, is it sill really an issue? The problem with RAID-5 is crappy write performance. Being big or small won't change that. Plus if the db is small why use RAID-5? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 2 questions re RAID
No responses to my earlier post, I'm assuming because OS X experience is rather thin in this group ;-) So a couple of more specific questions: 1) Is my impression correct that given a choice between Areca & Highpoint, it's a no-brainer to go with Areca? 2) I understand why RAID 5 is not generally recommended for good db performance. But if the database is not huge (10-20GB), and the server has enough RAM to keep most all of the db cached, and the RAID uses (battery-backed) write-back cache, is it sill really an issue? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql reports back wrong number of affected rows.
On 6/14/2011 8:08 PM, David Johnston wrote: alter table tblissue add constraint "tblissue_parentissueid_fkey_casc_del" FOREIGN KEY (parentissueid) REFERENCES tblissue(issueid) ON DELETE CASCADE; = Then: delete from tblissue where issueid=1; DELETE 1 Postgresql now deletes all rows that had a 1 for parentissueid. (5 in my testcase). That was correct, and as I intended, but why does Postgres answer "DELETE 1" instead of DELETE 6? Can somebody explain that to me please? Thanks for your time. You only explicitly deleted a single row; all the rest were done via the CASCADE and thus are not counted in the delete count. Make sense; If I delete a record and see "DELETE 1000" because 999 FK records were deleted I would have no way of know if I foo-barred the DELETE query itself and actually killed 1000 records using the DELETE itself or got it right and hit the 1 intended record and simply got 999 more deletions indirectly. I can see where a more helpful response would be: "DELETE 1 \n NOTICE: 999 FK references were deleted due to Cascade" but the "DELETE 1" MUST show me explicitly how many records were deleted solely due to my DELETE statement's FROM and WHERE clauses. Agree 100%. I am not a big fan of CASCADING effects (I rather do it 'by hand'), but in this case it was a really easy solution. Thanks you for your response. Regards, Erwin Moller David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [ADMIN] Postgres 8.3.10 Alter Table Waiting issue
[please don't send a post to multiple lists] Pratheeban Jebasingh Tharmaraj wrote: > I am trying to add column to the table that's hanging. > > alter table hr_firms add column_name biginit; > > This is the lock I see in the db > > relation | 564709 | 586888 | | || > | | | | 1/8| 1871 | > AccessExclusiveLock | f So it's temporarily blocking, waiting for conflicting access to complete. > Is it known issue or any fix available? Don't modify the structure of the table at the same time as conflicting access against the table, or wait for the transactions performing the conflicting access to commit? You haven't described the other activity on the table or shown the other lock(s) involved in the blocking. Without more information, it's hard to be more specific. http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On Wed, Feb 9, 2011 at 4:50 AM, Thom Brown wrote: > On 9 February 2011 02:11, Robert Haas wrote: >> On Tue, Feb 8, 2011 at 8:30 PM, Andrew Dunstan wrote: >>> Quite right, but the commitfest manager isn't meant to be a substitute for >>> one. Bug fixes aren't subject to the same restrictions of feature changes. >> >> Another option would be to add this here: >> >> http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Open_Items > > I've removed it from the commitfest because it really doesn't belong > there, and I've added it to the open items list. So, I finally got around to look at this, and I think there is a simpler solution. When an overflow occurs while calculating the next value, that just means that the value we're about to return is the last one that should be generated. So we just need to frob the context state so that the next call will decide we're done. There are any of number of ways to do that; I just picked what looked like the easiest one. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company generate-series-overflow.patch Description: Binary data -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Auto Start second postgresql instance MAC OS X
Hi, I'm used to work with PostgreSQL on Windows but now I've moved to OS X
and I'm having problems to create a service to auto start a new server
(instance) of PostgreSQL.
Firstly I used the PostgreSQL installer to create the first server and the
postgres user, then I used initdb to create another server and until this
step, everything turned fine.
After that, I created a folder on StartupItems with StartupParameters and
the other script.
Here are the contents of the two files:
#walkinsense-pgsql
#!/bin/sh
. /etc/rc.common
# Postgres Plus Service script for OS/X
StartService ()
{
ConsoleMessage "Starting Walkinsense PostgreSQL"
su - postgres -c "/Library/PostgreSQL/8.3/bin/pg_ctl -D
/Users/iMac1/Library/Teste/data -l
/Library/Walkinsense/data/pg_log/startup.log -o \"-p 5440\" start"
if [ -e "/Library/Walkinsense/data/postmaster.pid" ]
then
ConsoleMessage "PostgreSQL 8.3 started successfully"
else
ConsoleMessage "PostgreSQL 8.3 did not start in a timely fashion, please see
/Library/Walkinsense/data/pg_log/startup.log for details"
fi
}
StopService()
{
ConsoleMessage "Stopping PostgreSQL 8.3"
su - postgres -c "/Library/PostgreSQL/8.3/bin/pg_ctl stop -m fast -w -D
/Users/iMac1/Library/Teste/data"
}
RestartService ()
{
StopService
sleep 2
StartService
}
RunService "$1"
#SetupParameters
{
Description = "Walkinsense-pgsql";
Provides = ("walkinsense-pgsql");
Requires = ("Resolver");
Preference= "Late";
Messages =
{
start = "Starting Walkinsense PostgreSQL";
stop = "Stopping Walkinsense PostgreSQL";
};
}
I appreciate your help because I'm wasting a lot of time with this and I
think you could help me easily.
Thanks!!
Diogo
Re: [GENERAL] ncoding "Table Name" and "Filed Name"
We are expecting following advantages through this, Unauthorized use either by the 1. DB administrator 2. ex-developer Or 3. Any body This why PostgreSQL has the concept of "ROLE"s. The data, not the schema, is what needs to be secured.you are not preventing unauthorized use only making both it and authorized use more difficult. It isn't that hard to get the database to tell you all the table and field names then it is just a matter of issuing a "SELECT * FROM .." to see/retrieve the data. A rose is a rose by any other name. For the people you do trust you are making them jump through lots of hoops in order to work with the schema. David J.
Re: [GENERAL] Postgres performance and the Linux scheduler
Simon Windsor wrote: > Can the performance of Postgres be boosted, especially on busy systems, using the none default > DEADLINE Scheduler? I think that mostly depends on your storage. I personally have made one experience where (after weeks of trying everything else) I changed the scheduler from CFQ to deadline and immediately got about four times better performance. The storage in this case was a SAN. I don't have enough experience, but my guess is that the smarter your storage is, the less the kernel should try to optimize I/O. I assume that in my case the kernel's optimizations (which try to optimize things for a physical hard drive) and the SAN's optimizations got in each other's way. As I think is the case for all performance questions, there cannot be a certain answer -- the best thing is for you to try it out and see if it does something for you. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Invalid byte sequence for encoding "UTF8": 0xedbebf
BRUSSER Michael wrote:
>>> Is there a way to find the records with the text field containing
Unicode bytes "0xedbebf"?
>>> Unfortunately this is a very old version 7.3.10
>>
>> This should work on 7.3 (according to the documentation):
>> SELECT id FROM nlsdata WHERE position('\360\235\204\236'::bytea IN
val::bytea) = 1;
> Albe, thanks for pointing this out!
> I made a minor change, added decode since text cannot be cast to bytea
and tried something like this:
> SELECT id FROM myTable WHERE position('\360\235\204\236'::bytea IN
decode(myTextField, 'escape')) !=0
> ERROR: decode: Bad input string for type bytea
Hrm. I didn't know that there was no cast from text to bytea in 7.3.
> Maybe this explains why?
> testdb=# select decode('\360\235\204\236'::text, 'escape');
> ERROR: Unicode >= 0x1 is not supported
No, that is an error on my side. I gave you the wrong byte sequence.
For "0xedbebf" you should actually write '\355\276\277'. But that's no
valid UTF-8 sequence.
> but I'm not ready to give up yet...
If you know the byte sequence that causes trouble, you could also use
something like "sed"
to search and replace it in the dump file.
Or (if there are not too many) you could search for the pattern and
identify the rows
in the database. Then you know which database rows to update.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
