[GENERAL] Overlapping ranges
I have a large table of access logs to an application. I want is to find all rows that overlap startdate and enddate with any other rows. The query below seems to work, but does not finish unless I specify a single id. select distinct a1.id from t_access a1, t_access a2 where tstzrange(a1.startdate, a1.enddate) tstzrange(a2.startdate, a2.enddate) -- 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] Overlapping ranges
On Wed, 2014-06-18 at 18:08 -0600, Rob Sargent wrote: On 06/18/2014 05:47 PM, Jason Long wrote: I have a large table of access logs to an application. I want is to find all rows that overlap startdate and enddate with any other rows. The query below seems to work, but does not finish unless I specify a single id. select distinct a1.id from t_access a1, t_access a2 where tstzrange(a1.startdate, a1.enddate) tstzrange(a2.startdate, a2.enddate) I'm sure you're best bet is a windowing function, but your descriptions suggests there is no index on start/end date columns. Probably want those in any event. There are indexs on startdate and enddate. If I specify a known a1.id=1234 then the query returns all records that overlap it, but this takes 1.7 seconds. There are about 2 million records in the table. I will see what I come up with on the window function. If anyone else has some suggestions let me know. I get with for EXPLAIN ANALYZE the id specified. Nested Loop (cost=0.43..107950.50 rows=8825 width=84) (actual time=2803.932..2804.558 rows=11 loops=1) Join Filter: (tstzrange(a1.startdate, a1.enddate) tstzrange(a2.startdate, a2.enddate)) Rows Removed by Join Filter: 1767741 - Index Scan using t_access_pkey on t_access a1 (cost=0.43..8.45 rows=1 width=24) (actual time=0.016..0.019 rows=1 loops=1) Index Cond: (id = 1928761) - Seq Scan on t_access a2 (cost=0.00..77056.22 rows=1764905 width=60) (actual time=0.006..1200.657 rows=1767752 loops=1) Filter: (enddate IS NOT NULL) Rows Removed by Filter: 159270 Total runtime: 2804.599 ms and for EXPLAIN without the id specified. EXPLAIN ANALYZE will not complete without the id specified. Nested Loop (cost=0.00..87949681448.20 rows=17005053815 width=84) Join Filter: (tstzrange(a1.startdate, a1.enddate) tstzrange(a2.startdate, a2.enddate)) - Seq Scan on t_access a2 (cost=0.00..77056.22 rows=1764905 width=60) Filter: (enddate IS NOT NULL) - Materialize (cost=0.00..97983.33 rows=1927022 width=24) - Seq Scan on t_access a1 (cost=0.00..77056.22 rows=1927022 width=24)
[GENERAL] ON DELETE CASCADE Question
I would like for corresponding records in t_a to be deleted when I delete a record from t_b. This deletes from t_b when I delete from t_a, but not the other way around. I am unable to create a foreign key constraint on t_a because this table holds records from several other tables. I added a simple script below that demonstrates my problem. Any suggestions? /***/ drop table IF EXISTS t_b; drop table IF EXISTS t_a; CREATE TABLE t_a ( id bigint NOT NULL, CONSTRAINT pk_a PRIMARY KEY (id) ); CREATE TABLE t_b ( id bigint NOT NULL, CONSTRAINT pk_b PRIMARY KEY (id), CONSTRAINT fk_b_a FOREIGN KEY (id) REFERENCES t_a (id) ON DELETE CASCADE ); INSERT INTO t_a VALUES (1),(2),(3); INSERT INTO t_b VALUES (1),(2),(3); delete from t_b where id = 2; select * from t_a; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with left join when moving a column to another table
I am having some problems moving a column to another table and fixing some views that rely on it. I want to move the area_id column from t_offerprice_pipe to t_offerprice and then left join the results. When I have only one table I get the correct results. area_id is currently in the t_offerprice_pipe. The working portion on the query is below. I am joining the price.t_offerprice_pipe twice because I am looking for a wild card with manufacturer_id=-100 that has lower precedence than a specific manufacturer_id LEFT JOIN t_offerprice_pipe opp ON opp.size_id = st.size_id AND opp.manufacturer_id = st.manufacturer_id AND opp.area_id = c.area_id LEFT JOIN price.t_offerprice_pipe opam ON opam.size_id = st.size_id AND opam.manufacturer_id = (-100) AND opam.area_id = c.area_id After moving the column to t_offerprice I am attempting to add a second left join, but is not working as I expected. I am getting multiple results from this query. LEFT JOIN t_offerprice_pipe opp ON opp.size_id = st.size_id AND opp.manufacturer_id = st.manufacturer_id LEFT JOIN t_offerprice op ON op.id = opp.id AND op.area_id = c.area_id LEFT JOIN price.t_offerprice_pipe oppam ON oppam.size_id = st.size_id AND oppam.manufacturer_id = (-100) LEFT JOIN t_offerprice opam ON opam.id = oppam.id AND opam.area_id = c.area_id This is a stripped down version of the query for clarity. I tried moving the condition into the where clause with no success. I would greatly appreciate any advice on rewriting this query.
Re: [GENERAL] Problem with left join when moving a column to another table
David, Thank you very much for your response. Below is a script that will reproduce the problem with comments included. /***/ --drop table t_item; --drop table t_price_base_table; --drop table t_price_original_with_area_id; --this table represents inventory line items CREATE TABLE t_item ( id bigint NOT NULL, size_id bigint NOT NULL, area_id bigint NOT NULL, CONSTRAINT pk_t_item PRIMARY KEY (id) ); INSERT INTO t_item VALUES (1, 1, 10); INSERT INTO t_item VALUES (2, 4, 1); INSERT INTO t_item VALUES (3, 19, 1); -- I want to move the area_id(and other columns not listed here) to another base table and left join it CREATE TABLE t_price_original_with_area_id ( id bigint NOT NULL, size_id bigint NOT NULL, area_id bigint NOT NULL, CONSTRAINT pk_t_price_original_with_area_id PRIMARY KEY (id) ); INSERT INTO t_price_original_with_area_id VALUES (162, 4, 6); INSERT INTO t_price_original_with_area_id VALUES (161, 4, 2); INSERT INTO t_price_original_with_area_id VALUES (159, 4, 1); INSERT INTO t_price_original_with_area_id VALUES (638, 19, 9); INSERT INTO t_price_original_with_area_id VALUES (633, 19, 14); INSERT INTO t_price_original_with_area_id VALUES (675, 19, 45); INSERT INTO t_price_original_with_area_id VALUES (64, 19, 1); -- My simplified base table CREATE TABLE t_price_base_table ( id bigint NOT NULL, area_id bigint NOT NULL, CONSTRAINT pk_t_price_base_table PRIMARY KEY (id) ); -- insert to add the information I want to transfer to the base table so I can drop the area_id column insert into t_price_base_table (id, area_id) (select id, area_id from t_price_original_with_area_id); /* This is the working query. Note it joins size_id and area_id in one left join. It produces 1 row for each item. There is no match for item 1. Item 2 and 3 match the price table. */ select it.*, pwoa.* from t_item it left join t_price_original_with_area_id pwoa on it.size_id=pwoa.size_id and it.area_id=pwoa.area_id order by it.id; /* This is the new query that is not working correctly. I am trying to left join the base table by its id and area_id. I need a left join because there is no guarantee that there is a matching price. The where claues seems to work, but I the orginal query is much more complicated, and I will be needed to do a simiar join in may views. */ select it.*, pwoa.*, pbt.* from t_item it left join t_price_original_with_area_id pwoa on it.size_id=pwoa.size_id left join t_price_base_table pbt on pbt.id=pwoa.id and it.area_id=pbt.area_id /* where (pwoa.id is not null and pbt.id is not null) or (pwoa.id is null and pbt.id is null) */ order by it.id; /***/ On Thu, 2013-06-20 at 12:29 -0700, David Johnston wrote: Jason Long-2 wrote I am having some problems moving a column to another table and fixing some views that rely on it. I want to move the area_id column from t_offerprice_pipe to t_offerprice and then left join the results. When I have only one table I get the correct results. area_id is currently in the t_offerprice_pipe. The working portion on the query is below. Maybe someone else can make sense of your partial examples but I cannot. I'd suggest creating self-contained queries that exhibit both the correct and incorrect behavior. Use the following template: WITH from_table_not_specified (col1, col2) AS ( VALUES (1, 1), (2, 2) ) , t_offerprice_pipe () AS ( VALUES (...), () ) , to_offerprice (...) AS ( VALUES (...), (...) ) /* working query */ SELECT * FROM from_table_not_specified LEFT JOIN t_offerprice_pipe op1 ON ... LEFT JOIN t_offerprice_pipe op2 ON ... /* not working query using same or similar CTEs where possible. */ SELECT * FROM ... LEFT JOIN ... LEFT JOIN ... LEFT JOIN ... LEFT JOIN ... Without a working query it is really hard (impossible really) to debug wrong number of rows problems. Especially since the query itself is possibly not the problem but rather your data model is flawed. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-left-join-when-moving-a-column-to-another-table-tp5760187p5760192.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
[GENERAL] Easiest way to compare the results of two queries row by row and column by column
Can someone suggest the easiest way to compare the results from two
queries to make sure they are identical?
I am rewriting a large number of views and I want to make sure that
nothing is changes in the results.
Something like
select compare_results('select * from v_old', 'select * from v_new');
I would want this to check that the row count and each row matched
column by column.
I am hoping someone has already written something for this...
--
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] Problem with left join when moving a column to another table
On Thu, 2013-06-20 at 15:37 -0700, David Johnston wrote: Jason Long-2 wrote David, Thank you very much for your response. Below is a script that will reproduce the problem with comments included. /* This is the new query that is not working correctly. I am trying to left join the base table by its id and area_id. I need a left join because there is no guarantee that there is a matching price. The query I am pretty sure you want is: WITH item (i_id, size_id, area_id) AS ( VALUES (1,1,10),(2,4,1),(3,19,1) ) , price_orig (p_id, size_id, area_id) AS ( VALUES (162,4,6),(161,4,2),(159,4,1),(638,19,9),(633,19,14),(675,19,45),(64,19,1) ) , simple_base (p_id, area_id) AS ( SELECT p_id, area_id FROm price_orig ) --SELECT * FROM item LEFT JOIN price_orig USING (size_id, area_id) --original /* your problem query SELECT * FROM item LEFT JOIN price_orig USING (size_id) LEFT JOIN simple_base ON (price_orig.p_id = simple_base.p_id AND item.area_id = simple_base.area_id) */ -- the correct query SELECT * FROM item LEFT JOIN (SELECT p_id, price_orig.size_id, simple_base.area_id FROM price_orig JOIN simple_base USING (p_id)) rebuild USING (size_id, area_id) In the original query you used both size and area to link to the price table. Even though you have moved the area to a different table in order to keep the same semantics you have to continue performing the same relational join. If you intend something different then you are not providing enough information since neither size_id nor area_id are unique within the price table. Because the combination of the two just happens to not be duplicated in the supplied data the correct queries only return a single result per item. There is a unique constraint on the real price table. I hadn't thought of how I will enforce the constraint across two tables. size_id and area_id will have to be unique across both t_price_base_table and t_price_original_with_area_id. I will want to drop area_id from t_price_original_with_area_id. What is the best way to implement the cross table unique constraint? In the correct query I am providing I am first re-joining (with an inner join) the two tables so that they appear just like the original table appeared. Then I am joining the view to the items table using both size and area. The fundamental problem is that you really do not want right-hand tables in left joins to refer to each other. FROM item LEFT JOIN price_orig ON item = price_orig LEFT JOIN price_base ON item = price_baseAND price_orig = price_base -- the second AND expression is the problem. I do not even try to remember nesting rules for JOIN generally. My basic form is: FROM INNER* LEFT* (with the ON clause only referring to tables joined via INNER) if my solution requires a different usage I either move parts of the query into CTEs or I start explicitly adding parenthesis to explicitly group the different pieces - and adding INNER JOIN where necessary like I did for your example. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-left-join-when-moving-a-column-to-another-table-tp5760187p5760210.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] Re: Easiest way to compare the results of two queries row by row and column by column
Thank you. I will give it a try. I have never used WITH before. Thank you for the tips. On Thu, 2013-06-20 at 16:05 -0700, David Johnston wrote: Jason Long-2 wrote Can someone suggest the easiest way to compare the results from two queries to make sure they are identical? First thing that comes to mind: WITH before_qry (col1, col2, col3) AS ( VALUES (1,1,1),(2,2,2),(3,3,3) ) , after_qry (col1, col2, col3) AS ( VALUES (1,1,1),(2,2,2),(3,3,3) ) , before_array AS (SELECT array_agg(before_qry) AS before_agg_array FROM before_qry) , after_array AS (SELECT array_agg(before_qry) AS after_agg_array FROM before_qry) SELECT *, before_agg_array = after_agg_array FROM before_array CROSS JOIN after_array Basically turn the resultsets into arrays (of composites) and then see if the arrays are the same. This has issues with respect to column names and comparable datatypes (i.e., if one column is bigint and the other is integer they still compare equally). One thought would to only allow a view name (and possibly, separately, the ORDER BY clause). Catalog lookups can be used to check for identical view output types. No idea of something like this exists and is readily available. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Easiest-way-to-compare-the-results-of-two-queries-row-by-row-and-column-by-column-tp5760209p5760215.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] Problem with left join when moving a column to another table
On Thu, 2013-06-20 at 16:22 -0700, David Johnston wrote: Jason Long-2 wrote Jason Long-2 wrote There is a unique constraint on the real price table. I hadn't thought of how I will enforce the constraint across two tables. size_id and area_id will have to be unique across both t_price_base_table and t_price_original_with_area_id. I will want to drop area_id from t_price_original_with_area_id. What is the best way to implement the cross table unique constraint? Don't. If size+area is a unique constraint then there should be a table that defines valid pairs and creates a PRIMARY KEY over them. Per my original comment your issue isn't JOINs (well, your biggest issue anyway) but your model. The fact that you couldn't write a good query simply exposed the problems in the model. This is not uncommon. I would need a lot more information (and time) than I have now to offer any design thoughts on your schema; though I do find the unique constraint over size+area to be unusual - as well as using that as a foreign key from the item table. You haven't specified the domain for this model but using homes as an example I would use a 'model' table with model_id, size, area as columns. A particular house would then link in model and price. You could possibly further restrict that certain models can only sell for certain prices if necessary - in which case you would have model_price and possibly house_model_price tables (the later could be an FK). David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-left-join-when-moving-a-column-to-another-table-tp5760187p5760220.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. David, I really appreciate your help. I had not used WITH or any of the syntax you showed me before. Pretty cool. I normally just write a bunch of views to build complex queries. Does the syntax you showed me have performance benefits vs joining a bunch of views together? I spent way to much time trying to get the query to work, and all I needed to do was write a view create or replace view price.v_offerprice_pipe as select op.id, op.price, op.active, op.stditem, op.version, opp.size_id, opp.weight_id, opp.grade_id, opp.endfinish_id, opp.manufacturer_id, opp.condition_id, opp.area_id from price.t_offerprice_pipe opp join price.t_offerprice op on op.id=opp.id; This allowed me to move (price, active, stditem, version) to the base table without breaking any of my views with very minimal change to the view definitions. I just had to replace any references to price.t_offerprice_pipe with the view price.v_offerprice_pipe in any of the views that were complaining about dropping the columns. I decided not to move area_id to the base table for now. Without being able to properly do a cross table unique constraint, it will stay where it is currently.
[GENERAL] Optimizing Queries Joining Several Views
In order to do some complex calculations I have joined several views. Each view could join quite a few tables. The user is allowed to filter the results with several multi-select input fields and this is used in the query as where a.id in (:listOfIds). This works fine if the user does not filter the results. These calcs for every row in the entire can be calculated in 1-2 seconds. Certain combinations of filters will make the query take up to 4 minutes and will freeze the system until it has completed. Queries without these calcs at all, but using the same filters work in a reasonable amount of time. I have considered the following ways to make this faster. 1. increase geqo_threshold, from_collapse_limit, join_collapse_limit While this does improve the performance on some of the more complex queries, generally others suffer. 2. Filter the results first and then join the complex calcs. The database is small. About 1 GB on disk and the vast majority of that is taken by bytea documents that are never accessed. From what I can tell all data is in shared buffers. Any advice would be greatly appreciated. Here are the settings I have changed in postgresql.conf statement_timeout = 60 # in milliseconds, 0 is disabled geqo_effort = 10# range 1-10 default_statistics_target = 1 geqo_threshold = 13 from_collapse_limit = 9 join_collapse_limit = 9# 1 disables collapsing of explicit JOIN clauses work_mem = 48MB # pgtune wizard 2011-12-12 maintenance_work_mem = 480MB # pgtune wizard 2011-12-12 shared_buffers = 1920MB # pgtune wizard 2011-12-12 effective_cache_size = 5632MB # pgtune wizard 2011-12-12 seq_page_cost = 0.005# measured on an arbitrary scale random_page_cost = 0.005 # same scale as above -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query Slowdown after upgrade from 9.1.1 to 9.1.2
I upgraded to v9.1.2 a couple of days ago. Some of my queries are taking noticeably longer. Some of the slower ones would max out at about 45 seconds before. Now they are maxing out at almost 2 minutes. The only change I made to postgresql.conf was geqo_effort = 10 and this was long before this upgrade. I can provide more info if necessary. The queries involve join quite a few tables and views together and also a window query for totals. Any ideas?
[GENERAL] Supply Chain Calcs
I have a custom inventory system that runs on PG 9.1. I realize this is not a postgres specify question, but I respect the skills of the members of this list and was hoping for some general advice. The system is not based on any ERP and was built from scratch. My customer requested some supply forecasting to see when there will be a surplus or shortage of parts based on delivery dates and production dates that will require these items. I need to identify which items will run out and when based on current available inventory, orders, delivery dates, and production dates. Would someone please give me some keywords to google so I can track down a few different ways of doing these calculations? Or links to any examples. I am really not trying to reinvent the wheel here.
Re: [GENERAL] Supply Chain Calcs
Thanks for the reply. Weeks of Supply(WOS) is not exactly what I am looking for, but might lead to a solution. Here is a better description of the problem. I know the following: Delivery dates and quantities for items on order or in transit. A manager will forecast manually what the pending items will be customized with and a date this will happen. This might very well change and will not be based on any historical information. There is also a 30-45 day delay in delivery since all components come from over seas and must come via ship. This is meant to point out where manually foretasted productions will fail due to lack of specific parts. On Mon, Nov 21, 2011 at 12:23 PM, Henry Drexler alonup...@gmail.com wrote: google 'weeks of supply' On Mon, Nov 21, 2011 at 1:18 PM, Jason Long mailing.li...@octgsoftware.com wrote: I have a custom inventory system that runs on PG 9.1. I realize this is not a postgres specify question, but I respect the skills of the members of this list and was hoping for some general advice. The system is not based on any ERP and was built from scratch. My customer requested some supply forecasting to see when there will be a surplus or shortage of parts based on delivery dates and production dates that will require these items. I need to identify which items will run out and when based on current available inventory, orders, delivery dates, and production dates. Would someone please give me some keywords to google so I can track down a few different ways of doing these calculations? Or links to any examples. I am really not trying to reinvent the wheel here.
Re: [GENERAL] Any was to prevent a join if no columns are selected from a view?
On Thu, 2011-09-29 at 22:54 -0600, Ben Chobot wrote: On Sep 29, 2011, at 4:57 PM, Jason Long wrote: I thought I had read somewhere that Postges could ignore a join if it was not necessary because there were no columns from the table or view selected in the query. Is this possible? This sounds like incorrect logic to me, so I would be surprised if it was possible. That is the way it is looking. I just modified my application to generate the join manually.
Re: [GENERAL] Identifying old/unused views and table
On Wed, 2011-09-28 at 08:52 +0200, Guillaume Lelarge wrote: On Wed, 2011-09-28 at 09:04 +0800, Craig Ringer wrote: On 09/28/2011 04:51 AM, Jason Long wrote: I have an application with a couple hundred views and a couple hundred tables. Is there some way I can find out which views have been accessed in the last 6 months or so? Or some way to log this? I know there are views and tables that are no longer in used by my application and I am looking for a way to identify them. Look at the pg_catalog.pg_stat* tables I fail to see how that gives him any answer on the views, and tables no longer used. AFAICT, there's no way to know for views (apart from logging all queries in the log). As for tables, still apart from the log, pg_stat_user_tables could give an answer if he was monitoring it at least the last six months. Thanks for the replies. Views were my main problem. My application could use some cleanup. Doing is manually is probably the best approach. I was just looking for a jump start. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Any was to prevent a join if no columns are selected from a view?
I started an application around 5 years ago using Hibernate and writing my queries in HQL. The primary search screen has many options to filter and joins many tables and views. As the application grew the SQL Hibernate is generating is out of hand and needs optimization. As with other parts of the application, I usually fall back to raw SQL with good results. This is what I am attempting now. I thought I had read somewhere that Postges could ignore a join if it was not necessary because there were no columns from the table or view selected in the query. Is this possible? What I want to do is to create a view with all the columns I need for the search, and have my search pull from this view. Here are some query times: 396 ms without the table joined 2008 ms with the query joined, but no columns selected The time is the same if I select the calculated values from the view. This way I can just generate the select, group by, and order by terms and just select from view without having to manually join the views if they are required. I also tried another approach and tried to join the more expensive views to a simpler view that was not expensive. select * from v_no_expensive_calcs vne join v_expensive_calcs ve.id=vne.id This takes about 2000 ms when joining v_expensive_calcs directly inside v_no_expensive_calcs only takes 1100 ms. I thought these would be equivalent. The idea is that some users do not need cost, price, look up, or profit calculations which are expensive. Hibernate handles this currently well, but it also generates a crazy mess that is dragging down the whole application. I will probably end up doing this manually, but I am trying to make the application logic simpler. Any ideas or advice? I am using Postgres 9.1.1. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Identifying old/unused views and table
I have an application with a couple hundred views and a couple hundred tables. Is there some way I can find out which views have been accessed in the last 6 months or so? Or some way to log this? I know there are views and tables that are no longer in used by my application and I am looking for a way to identify them. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] comma vs cross join question
I recently upgraded to JBoss AS 6.0.0.Final which includes a newer version of Hibernate. Previously the Postgres dialect was using a comma, but now is is using cross join. In order do to the migration I had to override the cross join operator to a comma in HIbernate so it would generate the same query. With the cross join this query never completes. With the comma the query is identical to what was there before and takes less than 300 ms. The rest of the application seems fine, but this one query is a show stopper. I have attached the queries below for reference. The only difference is the use of cross join vs comma. Do you think this is the right way to correct this or should I be looking to tune Postgres to work when cross join is used? ** --Hibernate 3.6.0 select count(pipe0_.id) as col_0_0_, sum(pipe0_.numFeet) as col_1_0_, sum(pipecalc1_.nt) as col_2_0_, sum(pipecalc1_.mt) as col_3_0_, sum(pipe0_1_.numPieces) as col_4_0_, sum(pipecalc1_.wt100) as col_5_0_ from inventory.t_pipe pipe0_ inner join inventory.t_generic_item pipe0_1_ on pipe0_.id=pipe0_1_.id, public.v_pipe_calc pipecalc1_ cross join state.t_state state4_ cross join property.t_status status5_ cross join state.t_po_pipe popipe6_ inner join state.t_state popipe6_1_ on popipe6_.id=popipe6_1_.id where pipe0_.id=pipecalc1_.id and pipe0_1_.state_id=state4_.id and state4_.status_id=status5_.id and pipe0_.poPipe_id=popipe6_.id and status5_.activeStatus=true and popipe6_1_.spec=true --Hibernate 3.3.1 select count(pipe0_.id) as col_0_0_, sum(pipe0_.numFeet) as col_1_0_, sum(pipecalc1_.nt) as col_2_0_, sum(pipecalc1_.mt) as col_3_0_, sum(pipe0_1_.numPieces) as col_4_0_, sum(pipecalc1_.wt100) as col_5_0_ from inventory.t_pipe pipe0_ inner join inventory.t_generic_item pipe0_1_ on pipe0_.id=pipe0_1_.id, public.v_pipe_calc pipecalc1_, state.t_state state4_, property.t_status status5_, state.t_po_pipe popipe6_ inner join state.t_state popipe6_1_ on popipe6_.id=popipe6_1_.id where pipe0_.id=pipecalc1_.id and pipe0_1_.state_id=state4_.id and state4_.status_id=status5_.id and pipe0_.poPipe_id=popipe6_.id and status5_.activeStatus=true and popipe6_1_.spec=true
[GENERAL] Count for pagination
The main search screen of my application has pagination. I am basically running 3 queries with the same where clause. 1. Totals for the entire results(not just the number of rows on the first page) a. 300 ms 2. Subset of the total records on that page. a. 1-2 sec 3. Count of the total records for the pagination to show the number of pages a. 1-2 sec The queries are generated by Hibernate and I am looking to rewrite them in native SQL to improve performance. Any suggestions on how to get the count of all records that could be returned and only a subset of those records for that page in an optimized fashion? I have no problem using a widow query or a Postgres specific feature as my app only runs on Postgres. -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com
[GENERAL] Count for pagination
The main search screen of my application has pagination. I am basically running 3 queries with the same where clause. 1. Totals for the entire results(not just the number of rows on the first page) a. 300 ms 2. Subset of the total records for one page. a. 1-2 sec 3. Count of the total records for the pagination to show the number of pages a. 1-2 sec The queries are generated by Hibernate and I am looking to rewrite them in native SQL to improve performance. Any suggestions on how to get the count of all records that could be returned and only a subset of those records for that page in an optimized fashion? I have no problem using a widow query or a Postgres specific feature as my app only runs on Postgres. -- 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] comma vs cross join question
On Fri, 2011-04-08 at 14:45 -0400, Tom Lane wrote: Jason Long mailing.li...@octgsoftware.com writes: I am using 9.0.3 and the only setting I have changed is geqo_effort = 10 One of the joins is a view join. Ah. The explain shows there are actually nine base tables in that query, which is more than the default join_collapse_limit. Try cranking up both join_collapse_limit and from_collapse_limit to 10 or so. (I'm not sure offhand if from_collapse_limit affects this case, but it might.) regards, tom lane I have to say I love this mailing list and thank you Tom for your expertise. I played with the settings with the following results. Worked like a charm from_collapse_limit = 10 join_collapse_limit = 10 Worked like a charm from_collapse_limit = 10 join_collapse_limit = 8 Failed from_collapse_limit = 8 join_collapse_limit = 10 It looks like from_collapse_limit was the key. I am going to leave them both at 10. -- 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] Full Vacuum/Reindex vs autovacuum
On Mon, 2010-11-08 at 16:23 -0700, Scott Marlowe wrote: On Mon, Nov 8, 2010 at 3:42 PM, Jason Long ja...@octgsoftware.com wrote: On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote: On Mon, Nov 8, 2010 at 11:50 AM, Jason Long ja...@octgsoftware.com wrote: I currently have Postgres 9.0 install after an upgrade. My database is relatively small, but complex. The dump is about 90MB. Every night when there is no activity I do a full vacuum, a reindex, One question, why? and then dump a nightly backup. Good idea. Is this optimal with regards to performance? autovacuum is set to the default. that depends very much on your answer to the question of why are you doing it and what you're trying to gain / work around with vacuum full / reindex every night. I have been doing this for several years. Since my database is small and it takes little time to do a full vacuum. I am doing the reindex because I thought that was recommended after a full vacuum. Definitely reindex after a full vacuum on previous versions (i.e. before 9.0) I think with 9.0 vacuum full is like a cluster without any reordering, so it likely doesn't need reindexing, but I've not played with 9.0 much yet. As the data has grown the system is slowing down. Right now I am looking at ways to improve performance without getting into the queries themselves because I am swamped with new development. OK, so it's a standard maintenance procedure you've been doing for a while. That doesn't really explain why you started doing it, but I can guess that you had some bloat issues way back when and vacuum full fixed them, so doing it got kind of enshrined in the nightly maintenance. Is doing the full vacuum and reindex hurting or helping anything? It might help a small amount if you've got regular usage patterns. If you routinely update whole tables over and over then it might be helping. Any other quick fixes that I can try? Increasing work_mem, shared_buffers, changing random_page_cost and / or seq_page_cost. Log long running queries and run explain analyze on any that show up very often. But for real performance, you do often have to get into the queries because an inefficient query may be something you can cut down to 1/1th the run time with a simple change, and often that change is impossible to make by tuning the db, only the query can be tuned. It might be something simple like you need to cast a type to match some other type. Hard to say without looking. When a 90Meg database is slow, it's almost always poorly written / non-optimized queries at the heart of it. I stopped doing the nightly vacuum full and reindex. After 3 months some queries would not complete within 2 minutes. Normally these take less than 5 seconds. I tried vacuum without full and reindex, but the problem was still there. Only vacuum full and reindex returned performance to normal. Now I am back to my previous nightly full vacuum and reindex. Any suggestions? -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com -- 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] Full Vacuum/Reindex vs autovacuum
On Mon, 2010-11-08 at 16:23 -0700, Scott Marlowe wrote: On Mon, Nov 8, 2010 at 3:42 PM, Jason Long ja...@octgsoftware.com wrote: On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote: On Mon, Nov 8, 2010 at 11:50 AM, Jason Long ja...@octgsoftware.com wrote: I currently have Postgres 9.0 install after an upgrade. My database is relatively small, but complex. The dump is about 90MB. Every night when there is no activity I do a full vacuum, a reindex, One question, why? and then dump a nightly backup. Good idea. Is this optimal with regards to performance? autovacuum is set to the default. that depends very much on your answer to the question of why are you doing it and what you're trying to gain / work around with vacuum full / reindex every night. I have been doing this for several years. Since my database is small and it takes little time to do a full vacuum. I am doing the reindex because I thought that was recommended after a full vacuum. Definitely reindex after a full vacuum on previous versions (i.e. before 9.0) I think with 9.0 vacuum full is like a cluster without any reordering, so it likely doesn't need reindexing, but I've not played with 9.0 much yet. As the data has grown the system is slowing down. Right now I am looking at ways to improve performance without getting into the queries themselves because I am swamped with new development. OK, so it's a standard maintenance procedure you've been doing for a while. That doesn't really explain why you started doing it, but I can guess that you had some bloat issues way back when and vacuum full fixed them, so doing it got kind of enshrined in the nightly maintenance. Is doing the full vacuum and reindex hurting or helping anything? It might help a small amount if you've got regular usage patterns. If you routinely update whole tables over and over then it might be helping. Any other quick fixes that I can try? Increasing work_mem, shared_buffers, changing random_page_cost and / or seq_page_cost. Log long running queries and run explain analyze on any that show up very often. But for real performance, you do often have to get into the queries because an inefficient query may be something you can cut down to 1/1th the run time with a simple change, and often that change is impossible to make by tuning the db, only the query can be tuned. It might be something simple like you need to cast a type to match some other type. Hard to say without looking. When a 90Meg database is slow, it's almost always poorly written / non-optimized queries at the heart of it. I stopped doing the nightly vacuum full and reindex. After 3 months some queries would not complete within 2 minutes. Normally these take less than 5 seconds. I tried vacuum without full and reindex, but the problem was still there. Only vacuum full and reindex returned performance to normal. Now I am back to my previous nightly full vacuum and reindex. Any suggestions? -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.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] Full Vacuum/Reindex vs autovacuum
I currently have Postgres 9.0 install after an upgrade. My database is relatively small, but complex. The dump is about 90MB. Every night when there is no activity I do a full vacuum, a reindex, and then dump a nightly backup. Is this optimal with regards to performance? autovacuum is set to the default. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Full Vacuum/Reindex vs autovacuum
I currently have Postgres 9.0 install after an upgrade. My database is relatively small, but complex. The dump is about 90MB. Every night when there is no activity I do a full vacuum, a reindex, and then dump a nightly backup. Is this optimal with regards to performance? autovacuum is set to the default. -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com -- 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] Full Vacuum/Reindex vs autovacuum
On Mon, 2010-11-08 at 13:28 -0800, John R Pierce wrote: On 11/08/10 10:50 AM, Jason Long wrote: I currently have Postgres 9.0 install after an upgrade. My database is relatively small, but complex. The dump is about 90MB. Every night when there is no activity I do a full vacuum, a reindex, and then dump a nightly backup. Is this optimal with regards to performance? autovacuum is set to the default. if you have frequently updated tables that are accessed mostly from their primary key, it may pay to CLUSTER those tables on said index rather than doing the full vacuum. VACUUM FULL is usually not recommended, btw. Also, if you have tables that get lots of updates that only affect data and not indexed columns, setting a FILL FACTOR of, say, 70 or 80 (its in %) might help with performance by better facilitating HOT updates (HOT is a internal feature added to pg 8.3 to speed up these sorts of updates) Just so I understand, why is full vacuum not recommended? -- 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] Full Vacuum/Reindex vs autovacuum
On Mon, 2010-11-08 at 13:28 -0800, John R Pierce wrote: On 11/08/10 10:50 AM, Jason Long wrote: I currently have Postgres 9.0 install after an upgrade. My database is relatively small, but complex. The dump is about 90MB. Every night when there is no activity I do a full vacuum, a reindex, and then dump a nightly backup. Is this optimal with regards to performance? autovacuum is set to the default. if you have frequently updated tables that are accessed mostly from their primary key, it may pay to CLUSTER those tables on said index rather than doing the full vacuum. VACUUM FULL is usually not recommended, btw. Also, if you have tables that get lots of updates that only affect data and not indexed columns, setting a FILL FACTOR of, say, 70 or 80 (its in %) might help with performance by better facilitating HOT updates (HOT is a internal feature added to pg 8.3 to speed up these sorts of updates) Thanks for the tip on CLUSTER. My application has a couple hundred tables that all have an int8 for their primary key. They are joined heavily on their primary key from views and dynamically generated SQL. I am going to looking into clustering the most frequently updated tables. Thanks for the tip. Currently my performance problems are reads to display data. -- 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] Full Vacuum/Reindex vs autovacuum
On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote: On Mon, Nov 8, 2010 at 11:50 AM, Jason Long ja...@octgsoftware.com wrote: I currently have Postgres 9.0 install after an upgrade. My database is relatively small, but complex. The dump is about 90MB. Every night when there is no activity I do a full vacuum, a reindex, One question, why? and then dump a nightly backup. Good idea. Is this optimal with regards to performance? autovacuum is set to the default. that depends very much on your answer to the question of why are you doing it and what you're trying to gain / work around with vacuum full / reindex every night. Sorry I am not bumping this. I meant to send this to the list as well. I have been doing this for several years. Since my database is small and it takes little time to do a full vacuum. I am doing the reindex because I thought that was recommended after a full vacuum. As the data has grown the system is slowing down. Right now I am looking at ways to improve performance without getting into the queries themselves because I am swamped with new development. Is doing the full vacuum and reindex hurting or helping anything? Any other quick fixes that I can try? -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com -- 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] Full Vacuum/Reindex vs autovacuum
On Mon, 2010-11-08 at 16:23 -0700, Scott Marlowe wrote: On Mon, Nov 8, 2010 at 3:42 PM, Jason Long ja...@octgsoftware.com wrote: On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote: On Mon, Nov 8, 2010 at 11:50 AM, Jason Long ja...@octgsoftware.com wrote: I currently have Postgres 9.0 install after an upgrade. My database is relatively small, but complex. The dump is about 90MB. Every night when there is no activity I do a full vacuum, a reindex, One question, why? and then dump a nightly backup. Good idea. Is this optimal with regards to performance? autovacuum is set to the default. that depends very much on your answer to the question of why are you doing it and what you're trying to gain / work around with vacuum full / reindex every night. I have been doing this for several years. Since my database is small and it takes little time to do a full vacuum. I am doing the reindex because I thought that was recommended after a full vacuum. Definitely reindex after a full vacuum on previous versions (i.e. before 9.0) I think with 9.0 vacuum full is like a cluster without any reordering, so it likely doesn't need reindexing, but I've not played with 9.0 much yet. As the data has grown the system is slowing down. Right now I am looking at ways to improve performance without getting into the queries themselves because I am swamped with new development. OK, so it's a standard maintenance procedure you've been doing for a while. That doesn't really explain why you started doing it, but I can guess that you had some bloat issues way back when and vacuum full fixed them, so doing it got kind of enshrined in the nightly maintenance. Exactly. Is doing the full vacuum and reindex hurting or helping anything? It might help a small amount if you've got regular usage patterns. If you routinely update whole tables over and over then it might be helping. I rarely update whole tables. Any other quick fixes that I can try? Increasing work_mem, shared_buffers, changing random_page_cost and / or seq_page_cost. I did up those at one point, but saw little improvement. I will reinvestigate. Log long running queries and run explain analyze on any that show up very often. But for real performance, you do often have to get into the queries because an inefficient query may be something you can cut down to 1/1th the run time with a simple change, and often that change is impossible to make by tuning the db, only the query can be tuned. It might be something simple like you need to cast a type to match some other type. Hard to say without looking. When a 90Meg database is slow, it's almost always poorly written / non-optimized queries at the heart of it. I have no doubt that poorly written and non-optimized queries are at the heart of it. Stupid developer I'll have to fire that lazy bastard... Oh wait that's me. ;) I am going to start using auto_explain and logging long running queries. Also time to learn how to read query plans. So far I have gotten by by throwing faster hardware at the problem. -- 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] Linux
I use Centos for production and Fedora for development and I am very happy with both. Especially Centos as I have never had an update break anything. On Fri, 2010-11-05 at 09:50 -0400, David Siebert wrote: I would say that if you pick any of the big four you will be fine. CentOS Ubuntu Server LTS Red Hat Suse Debian can also be a good choice. We used to be an OpenSuse shop but we are now moving everything to Ubuntu Server LTS. I can not say enough good things about CentOS as far as stability and long support times. On 11/4/2010 11:00 AM, Michael Gould wrote: I know that this is probably a religion issue but we are looking to move Postgres to a Linux server. We currently have a Windows 2008 R2 active directory and all of the other servers are virtualized via VMWare ESXi. One of the reasons is that we want to use a 64 bit Postgres server and the UUID processing contrib module does not provide a 64 bit version for Windows. I would also assume that the database when properly tuned will probably run faster in a *inx environment. What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. Best Regards Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Streaming Replication
I have several databases running under on Postgres 9.0 installation in production. Is there a way to specify with which onces will be replicated on another server or must all of them be included?
[GENERAL] Case sensitive without quotes
Is there any way to have Postgres preserve the case of tables and column names in queries without having to use quotes for columns with mixed case? -- 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] Pet Peeves?
Daniel Verite wrote: Gregory Stark wrote: Is it the hierarchical query ability you're looking for or pivot? The former we are actually getting in 8.4. AFAIK even in systems with pivot you still have to declare a fixed list of columns in advance anyways. Do you see a system where it works differently? MS-Access SQL has a TRANSFORM clause that allows for crosstab queries without the need to know in advance the number of columns: http://msdn.microsoft.com/en-us/library/bb208956.aspx As for Oracle, it wasn't possible until recently but now 11g has the PIVOT clause: http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f eatures/11g-pivot.html In contrast of these clauses, PG's contrib/tablefunc looks more limited and quite harder to use. Best regards, The PIVOT clause would kick ass. -- 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] Pet Peeves?
Gregory Stark wrote: Daniel Verite dan...@manitou-mail.org writes: Gregory Stark wrote: Is it the hierarchical query ability you're looking for or pivot? The former we are actually getting in 8.4. AFAIK even in systems with pivot you still have to declare a fixed list of columns in advance anyways. Do you see a system where it works differently? MS-Access SQL has a TRANSFORM clause that allows for crosstab queries without the need to know in advance the number of columns: http://msdn.microsoft.com/en-us/library/bb208956.aspx That's puzzling. I wonder what they do about clients requesting info about the results. Or for that matter such queries being used in subqueries or anywhere else where the surrounding code needs to know the type of results to expect. As for Oracle, it wasn't possible until recently but now 11g has the PIVOT clause: http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f eatures/11g-pivot.html From this the result columns do need to be explicitly listed in advance unless you're asking for the pivot to be into an xml blob which seems like a whole different feature really. In contrast of these clauses, PG's contrib/tablefunc looks more limited and quite harder to use. Incidentally, the work-around I've used in the past was to aggregate the rows into an array instead of separate columns. Definitely not the same of course, just a work-around. I think PIVOT is enticing too. It'll be interesting to see what happens in the standard with the divergence between MSSQL and Oracle. PIVOT would prove very valuable to my application. :)
Re: [GENERAL] Pet Peeves?
Richard Huxton wrote: Gregory Stark wrote: I'm putting together a talk on PostgreSQL Pet Peeves for discussion at FOSDEM 2009 this year. Hmm - three niggles things leap to mind. 1. Case-folding on column-names. Quoting is a PITA sometimes when you're transferring from a different DBMS. Be nice to have a true_case_insensitive=on flag. I was just wishing for this the other day. 2. Non-deferred unique checks Merging two sets of data and renumbering pkeys is more fiddly than it needs to be. Doesn't happen often, but it's one of the few things we don't do properly. 3. Date handling Sometimes I've got data with invalid dates and it would be great if it could replace all the bad ones with, say -00-00. You could throw in non-updateable views, exact-match tsearch queries, per-statement triggers not having the old/new rowsets but that's more things PG doesn't do rather than things it does.
Re: [GENERAL] Pet Peeves?
Gregory Stark wrote: Jason Long mailing.l...@supernovasoftware.com writes: Richard Huxton wrote: 1. Case-folding on column-names. Quoting is a PITA sometimes when you're transferring from a different DBMS. Be nice to have a true_case_insensitive=on flag. I was just wishing for this the other day. I'm kind of wondering what behaviour you two are looking for and what different DBMS you're referring to. Ah, I misread. I was wishing for the a way to make table and column names case sensitive without having to add quotes everywhere. I'm assuming it's not the ANSI fold-to-uppercase behaviour you're looking for.
Re: [GENERAL] New 8.4 hot standby feature
Fujii Masao wrote: Hi, On Wed, Jan 28, 2009 at 4:28 AM, Gabi Julien gabi.jul...@broadsign.com wrote: Yes, the logs are shipped every minute but the recevory is 3 or 4 times longer. Are you disabling full_page_writes? It may slow down recovery several times. Thanks I will take a look at it. Also, I came across the record log shipping feature too in my research: http://www.postgresql.org/docs/current/static/warm-standby.html#WARM-STANDBY-RECORD Could this help? If the logs are smaller then I could potentially afford shipping then at a higher frequency. No. Even if the logs are shipped frequently, they cannot be applied until the log file fills. Regards, Is pg_clearxlogtail http://www.2ndquadrant.com/code/pg_clearxlogtail.c going to be in contrib or integrated in some other way?
Re: [GENERAL] difference between current_timestamp and now() in quotes
Adrian Klaver wrote: On Thursday 22 January 2009 8:16:46 am Alvaro Herrera wrote: Grzegorz Jaśkiewicz escribió: test2=# insert into dupa(a) select 'current_timestamp' from generate_series(1,100); ERROR: date/time value current is no longer supported LINE 1: insert into dupa(a) select 'current_timestamp' from generate... ^ test2=# insert into dupa(a) select 'now()' from generate_series(1,100); INSERT 0 100 Any ideas why the difference ? The parser handles CURRENT_TIMESTAMP (and others) specially, and doesn't recognize it in quotes. I don't know why 'now()' works; I think it is a literal of type unknown. I guess it's expanded to the actual value in later parsing stages. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support At least on 8.2 'now()' does not work either at least not in the way I think you want. I get: test=# SELECT 'now()'; ?column? -- now() (1 row) Try select now();
Re: [GENERAL] Query sometimes takes down server
Jeff Davis wrote: On Fri, 2009-01-16 at 08:43 -0600, Jason Long wrote: The numbers in the table names are due to hibernate generating the query. Well, that's what auto-generated schemas and queries do, I guess. The schema is not auto generated. It evolved as I created my inventory system. It is relatively easy for humans to understand. Or at least for me since I wrote it. Now we are getting somewhere. Someone suggested tweaking the genetic algorithm parameters. Has anyone else had to do this and what results did you acheive? Can someone offer me some detailed advice on tweaking these parameters? There are a lot of tables, so no matter what you do will require GEQO (the genetic algorithm I was talking about). I am familiar with with Global Optimization. I was part of my research for my masters degree. The fact that some of the plans are fast is good news: it means that it's possible to execute the query quickly. The other good news is that the slower plans are, indeed, estimated to be slower in the examples you provided (not by exactly proportional amounts, but it's still a good sign). If the estimations are so far off that they are basically random, GEQO won't help much; but in your case they look surprisingly good. I would try increasing geqo_effort, and tweaking geqo_pool_size and geqo_generations (mostly try increasing these last two, but smaller values might be useful), and tweak geqo_selection_bias randomly between 1.5 and 2. I raised the geqo_effort to 10 and this made this happen my less frequently, but still fairly often. Thanks for the advice. I will post my results if I achieve improvement. See useful ranges of the parameters here: http://www.postgresql.org/docs/8.3/static/runtime-config-query.html When you start to get stable execution times (make sure you don't just get lucky once), keep the values you're using. Post to the list with your results. I think I am going to write a script an run the query enough times for me to see some statistics on how my tuning of the parameters work. Would anyone have such a script already made? Maybe I should use a genetic algorithm to analyze all the possible combinations of GEQO parameters for may case. :) But, realistically I am slammed with work and while this is very interesting to me I will have to keep cranking out new features to keep the clients happy. Hopefully, I can find a a better set of parameters through trial and error. You may be able to fix some of your queries (like this one), but I suspect this will just make the problem more rare. When you come up with some new query later, I think the problem will come back. The solution is really to have a more reasonable schema, something that PostgreSQL (and humans) can understand well enough to optimize. Making them more rare is probably good enough for now. Thank you very much for the advice. Regards, Jeff Davis
Re: [GENERAL] Query sometimes takes down server
Scott Marlowe wrote: Weird. I wonder if the attachment is too big and the mailing list server is chopping it off of the email. I just tried it by sending text only instead of text and html. We will see if it goes through this time. Other than that do you see anything weird about my email? On Fri, Jan 16, 2009 at 1:19 PM, Jason Long mailing.l...@supernovasoftware.com wrote: Scott Marlowe wrote: On Thu, Jan 15, 2009 at 11:04 PM, Jeff Davis pg...@j-davis.com wrote: On Thu, 2009-01-15 at 19:37 -0600, Jason Long wrote: I have not looked into the detail of the explain, and I do see visually that very different plans are being chosen. It would help to share these plans with us... See EXPLAIN ANALYZE below for three different plans @ .5, 6, and 84 seconds Jason, note that the post with explain analyze didn't make it to the mailing list, so you're effectively leaving everyone else who could give you advice out of the conversation. I just sent it again. I wonder why it did not make it. Hopefully this one will. Please let me know if this one does not either. -- 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] Query sometimes takes down server
Scott Marlowe wrote: On Thu, Jan 15, 2009 at 11:04 PM, Jeff Davis pg...@j-davis.com wrote: On Thu, 2009-01-15 at 19:37 -0600, Jason Long wrote: I have not looked into the detail of the explain, and I do see visually that very different plans are being chosen. It would help to share these plans with us... See EXPLAIN ANALYZE below for three different plans @ .5, 6, and 84 seconds Jason, note that the post with explain analyze didn't make it to the mailing list, so you're effectively leaving everyone else who could give you advice out of the conversation. I just sent it again. I wonder why it did not make it. Hopefully this one will. Please let me know if this one does not either.
Re: [GENERAL] Query sometimes takes down server
David Wilson wrote: On Fri, Jan 16, 2009 at 3:27 PM, Jason Long mailing.l...@supernovasoftware.com wrote: I just tried it by sending text only instead of text and html. We will see if it goes through this time. Other than that do you see anything weird about my email? Still nothing. Do you have webspace you could place it on? If not, you could use http://explain-analyze.info and simply provide the list with links to the plans. Actually I tweaked the GEQO parameters as follows: # - Genetic Query Optimizer - geqo = on geqo_threshold = 12 geqo_effort = 10# range 1-10 geqo_pool_size = 1000 # selects default based on effort geqo_generations = 1000 # selects default based on effort geqo_selection_bias = 2.0 # range 1.5-2.0 I use the following script to test the settings. #!/bin/bash for ((i=0;i=1000;i+=1)); do psql -d pipetracker-dev -f /opt/main_list_count.sql | grep runtime /var/lib/pgsql/test.txt done The query was able to run 1000 times without even getting close to the 2 minute timeout I have set. Here are some statistics I have from the runs. Out of 1000 runs only these were more that 3 seconds 17377.86 ms 15040.32 ms 12343.12 ms 11915.26 ms 11409.88 ms 9719.72 ms 8535.49 ms 5531.8 ms 5286.9 ms 4920.17 ms 3849.4 ms Avg 468.74 ms Min 173.3 ms Max 17337.86 ms STDEV 1102.35 ms I then put the settings back to the default and got a timeout after 45 tries. These are the results greater than 3 seconds from those 45 114450.17 ms 79529.79 ms 40999.69 ms 28981.87 ms 25802.51 ms 13346.41 ms 9569.23 ms 9267.75 ms 8221.4 ms 8213.87 ms 5975.05 ms Thank you everyone for your help. Has anyone developed some sort of testing script that is much better than my simple bash script shown above? I would like to just let some queries run and have the script run and then alter some parameters in postgres.conf and run again. After running for a while it could tune these parameters based on the queries I have it run. Thank you everyone for your advice. My users will be much happier. :) Can anyone see any problems with the settings I have and how they might affect my application as a whole?
[GENERAL] Vacuum and Reindex hangs
*I am attempting to vacuum and reindex my database. It keeps timing out. See commands and last part of output below. The vacuum or reindex only takes a short time to complete normally because the database it less than 50 mb. I have the query timeout set to 2 minutes, but I do not know if this can affect vacuumdb or reindexdb and the time should take much less than 2 minutes anyway. I am using 8.3.3 on Centos 5. Any advice would be greatly appreciated. vacuumdb -f -v -z -e -d pipetracker-live* INFO: analyzing general.t_access INFO: t_access: scanned 3000 of 20186 pages, containing 100449 live rows and 0 dead rows; 3000 rows in sample, 675888 estimated total rows INFO: vacuuming information_schema.sql_parts INFO: sql_parts: found 0 removable, 9 nonremovable row versions in 1 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 56 to 96 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 7500 bytes. 0 pages are or will become empty, including 0 at the end of the table. 1 pages containing 7500 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: sql_parts: moved 0 row versions, truncated 1 to 1 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: vacuuming pg_toast.pg_toast_11449 INFO: pg_toast_11449: found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 0 to 0 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 0 bytes. 0 pages are or will become empty, including 0 at the end of the table. 0 pages containing 0 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index pg_toast_11449_index now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing information_schema.sql_parts INFO: sql_parts: scanned 1 of 1 pages, containing 9 live rows and 0 dead rows; 9 rows in sample, 9 estimated total rows vacuumdb: vacuuming of database pipetracker-live failed: ERROR: canceling statement due to statement timeout *reindexdb -d pipetracker-live* NOTICE: table pg_class was reindexed NOTICE: table pg_type was reindexed NOTICE: table sql_features was reindexed NOTICE: table sql_implementation_info was reindexed NOTICE: table sql_languages was reindexed NOTICE: table pg_statistic was reindexed NOTICE: table sql_packages was reindexed NOTICE: table t_access was reindexed NOTICE: table sql_parts was reindexed reindexdb: reindexing of database pipetracker-live failed: ERROR: canceling statement due to statement timeout
Re: [GENERAL] Vacuum and Reindex hangs
Alan Hodgson wrote: On Thursday 15 January 2009, Jason Long mailing.l...@supernovasoftware.com wrote: *I am attempting to vacuum and reindex my database. It keeps timing out. See commands and last part of output below. The vacuum or reindex only takes a short time to complete normally because the database it less than 50 mb. I have the query timeout set to 2 minutes, but I do not know if this can affect vacuumdb or reindexdb and the time should take much less than 2 minutes anyway. Obviously it is affecting it and it is taking longer than 2 minutes regardless of how long you think it should take. Set it higher for these tasks or buy a faster server. A faster server. Well the sever is plenty fast. It has 2 quad core 1600MHz FSB 3.0 GHz Xeon 5472 CPUs and a very light workload. My statement about the time is that it has never taken that long. Ever. Not even close. I will increase the setting and see if that works.
Re: [GENERAL] Vacuum and Reindex hangs
I don't mean to be a pain either and I mean no disrespect to anyone on this list in the following comments. However, this is about the most anal list ever. I see so many emails on here about people complaining regarding the proper way to reply or post to the list. I used larger font to point point out my statement from the code. I also did not realize it appeared that large to you. My res is 2560X1600 so it didn't look that large. I apologize. *Just out of curiosity, why are you so apposed to HTML in a email?* Raymond O'Donnell wrote: On 15/01/2009 20:06, Jason Long wrote: bigbigbI am attempting to vacuum...[snip] I don't mean to be a pain, but could you please avoid HUGE type sizes such as the aboveor better still, avoid using HTML altogether in your emails to this list. It makes it look as if you are not just shouting, but SCREAMING at the top of your lungs! :-) Thanks in advance. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals --
Re: HTML email (was Re: [GENERAL] Vacuum and Reindex hangs
Steve Atkins wrote: On Jan 15, 2009, at 12:32 PM, Jason Long wrote: I don't mean to be a pain either and I mean no disrespect to anyone on this list in the following comments. However, this is about the most anal list ever. I see so many emails on here about people complaining regarding the proper way to reply or post to the list. I used larger font to point point out my statement from the code. I also did not realize it appeared that large to you. My res is 2560X1600 so it didn't look that large. I apologize. Just out of curiosity, why are you so apposed to HTML in a email? There are technical reasons. One of them is that most clients sending html mail are actually sending multipart/alternative mail with an html part and a plain text generate mechanically from the html part. People using plain text mail clients (and there tend to be a lot of them on technical lists) will see the plain text part only. That's fine for some sorts of email, but leads to tears when someone insists that they've hilighted the problem in red or bold or whatever, and half the recipients are reading the plain text version. Also, HTML mail tends to not use standard email quoting, meaning that it tends to discard context about who said what, which makes it very difficult to follow discussions. And it often plays hell with list digests and archives. There are also social reasons - it tends to be used by people who don't realize how it looks when received by the recipient, and who don't care. It's generally a sign of someone who has little experience of normal technical mailing list etiquette or polite online behaviour (such as following community norms). It also correlates strongly with people whose behaviour is antisocial in other respects (not so much use of html per-se as use of large font sizes, colours and suchlike, which are perceived by most recipients as SHOUTING, or vehement defense of html email). And it tends to derail threads into discussions like this, which is always bad. I'm sure none of that other than the last actually applies to you, but those are the expectations you set by using HTML email and then insulting all the list members when someone asks you to stop. That's not the way to get useful help from a technical peer support list. Fair enough and I had no intention of insulting anyone. Being anal is not necessarily a bad thing. :) Cheers, Steve -- 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] Vacuum and Reindex hangs
Scott Marlowe wrote: On Thu, Jan 15, 2009 at 1:28 PM, Jason Long mailing.l...@supernovasoftware.com wrote: A faster server. Well the sever is plenty fast. It has 2 quad core 1600MHz FSB 3.0 GHz Xeon 5472 CPUs and a very light workload. A few things. That doesn't make a fast server. The disk i/o subsystem makes a fast server. And you've mentioned nothing of that yet. If you've got a 16 SAS Disk RAID -10 array on a fast RAID controller with battery backed cache, you've got a fast database server. If you've got a single SATA drive or a mirror set of two SATA drives, you do not have a fast database server. You got me. I have a set of mirrored raptors. I am not sure the disk i/o subsystem is a bottleneck. The whole DB is 50 mb with minimal users. Would a 16 SAS Disk RAID -10 really help me that much? The dataset is small, but contains a complex data structure with many joins between tables. I would appreciate any advice on the effect of a high end disk setup for my case. My statement about the time is that it has never taken that long. Ever. Not even close. I wonder if you're getting a lot of bloating in your indexes from the full vacuums. Is there a reason you're running full vacuums over regular vacuums? While there are quite a few circumstances where full vacuums are the right answer, most of the time they are not, at least not on a regular basis. I used to use full vacuum and reindex ever night just before I did a dump backup. Then I started to try the autovacuum. The reason for the vacuum now it that I have a dynamic query that sometimes brings the server to a grinding halt. This is why I set the timeout to 2 minutes. Now all I get is users pissed about 2 times a day and the problem corrects itself before they finish complaining to me. I tried to add some more indexes and then tried to vacuum full and reindex the database. A lot of things can cause your current vacuums to run slow. Maybe there's a competing regular autovacuum that's kicked in at the same time, someone is backing up the database, and so on. Autovacuum could be the case, but I have total control of the database an no backups are in progress. As for : However, this is about the most anal list ever. I see so many emails on here about people complaining regarding the proper way to reply or post to the list. That's because many of us receive hundreds of emails a week, and if everyone starts sending html email,using bouncing email addresses, or sending emails to 5 lists at once, things can get out of hand pretty quickly. Since your email agent is sending multi-part mime email with regular text and html email, there's no real reason to complain, as any agent worth its salt can be set to show only the text part. I'm pretty sure the email archive process also lops off the html part before storing it. I totally understand and will limit my use of HTML in the future. Busy lists tend to be anal. Wanna get a bunch of people mad at once? Break the rules on the lkml. We're a bunch of fuzzy little kittens playing with balls of yarn by comparison. :)
Re: [GENERAL] Use PSQLFS for photo storage
Steven Lembark wrote: I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Once they are in there I can deal with them. My main purpose is to use rsync to get the files into the database. Is there a better way to load 20,000 plus files reliably into Postgres? Don't: put them into a reasonably-organized filesystem and store the paths. This is exactly what I do not want to do. I do not want to deal with the file system at all except for initial import. I want to manage the photos entirely with SQL. I use dir names of date-venue-descr and basenames of the dir-image number (e.g., 2009.01.12-foo-bar/123-4567.nef). You'll probably find that moving the data into a viewer using a path will be simpler than pumping the raw data through postgres anyway. Please explain. What does moving the data into a viewer using a path mean. At that point you could create COPY statements that look like a pg_dump output to load the stuff the first time. Please elaborate.
[GENERAL] Query sometimes takes down server
I am having a serious problem with my application and I hope someone can help me out. This could not happen at a worse time as a consulting firm is at my clients to recommend a new financial system and the inventory system(which I developed) keeps locking up. I have a dynamically built query that will periodically(2 times a day and becoming more frequent) make my server totally unresponsive. The query that hangs the system is requesting a count(*) based on some parameters the users selects. Since I set my timeout to 2 minutes(the only way I have been able to deal with this so far) I see the offending query in the log. I took the query from the logs and pasted it into pgAdmin and ran it a few times. Sometime is takes 700-900 ms, but others it takes 60-100 seconds. Other times it never stops(I waited 10 minutes). If I run 10 times I get 8 less that 1 sec 2 5-10 sec And maybe 1 in 20 will not complete. Dev Server specs 1 CPU Xeon 5472 Quad core 3.0 GHz 1600MHz FSB 2x10k Raptor Raid 1 DB 50 mb with a lot of table joins These queries are being run with nothing else running on the server. My guess it that the planner is picking an inefficient plan sometimes. I have not looked into the detail of the explain, and I do see visually that very different plans are being chosen. How can I see the plan that was chosen when the time it very high? Is there a way to print the query plan chosen when the query times out as well? -- 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] Use PSQLFS for photo storage
Reid Thompson wrote:
On Tue, 2009-01-13 at 18:22 -0600, Jason Long wrote:
Never used Python or Perl. I use primarily Java. I was thinking of
doing something like
INSERT INTO pictures (filename,data) VALUES
('filename','/path/to/my/image/img0009.jpg');
But, this syntax doesn't seem to be supported.
Maybe I can use a custom C function to get the contents of the file. Then do
something like
INSERT INTO pictures (filename,data) VALUES
('/path/to/my/image/img0009.jpg',getBinaryFileContents('/path/to/my/image/img0009.jpg'));
Is there some postgres contrib for something like this?
Simple java stub test program attached. Modify to meet your needs. See
file header for URL of original example.
Thanks for the sample. I will be using Hibernate with will make my code
much cleaner. I will post an example here when done. I never use
straight JDBC.
[GENERAL] Use PSQLFS for photo storage
I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Once they are in there I can deal with them. My main purpose is to use rsync to get the files into the database. Is there a better way to load 20,000 plus files reliably into Postgres? -- 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] Use PSQLFS for photo storage
bytea was what I was going for. *Does anyone have a script they would share for this purpose? * If not I will probably use Java because this is what I am familiar with. The web app I will write for managing my photos will be written in Java. I want to be able to categorize, label, search, my photos. Alan Hodgson wrote: On Tuesday 13 January 2009, Jason Long mailing.l...@supernovasoftware.com wrote: I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Once they are in there I can deal with them. My main purpose is to use rsync to get the files into the database. Is there a better way to load 20,000 plus files reliably into Postgres? A perl script using either bytea fields or the lo_ interface via DBD::Pg would work well.
Re: [GENERAL] Use PSQLFS for photo storage
Steve Atkins wrote: On Jan 13, 2009, at 10:34 AM, Jason Long wrote: I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Once they are in there I can deal with them. My main purpose is to use rsync to get the files into the database. Is there a better way to load 20,000 plus files reliably into Postgres? If it's a filesystem then you'd get the files into the system by copying them there. You wouldn't want to touch the database manually (that'd be like touching the raw disk device on a real filesystem). Conversely, it's just a filesystem. There's not really any use to putting a filesystem on top of a database on top of a filesystem other than the (significant) hack value. In other words, you probably don't really want to do this. Cheers, Steve I just want an easy way to load the files into the DB and their original path they were loaded from. Is possible through SQL to load a file into a bytea column? -- 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] Use PSQLFS for photo storage
Sam Mason wrote:
On Tue, Jan 13, 2009 at 03:28:18PM -0600, Jason Long wrote:
Steve Atkins wrote:
On Jan 13, 2009, at 10:34 AM, Jason Long wrote:
I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/)
to store 100 GB of images in PostgreSQL.
Is there a better way to load 20,000 plus files reliably into Postgres?
That would imply that they're around 5MB on average? If they're all
under, say, 20MB (or maybe even much more) you should be able to handle
it by doing the most naive things possible.
*This is correct. They are all around 5 MB.*
I just want an easy way to load the files into the DB and their original
path they were loaded from.
Is possible through SQL to load a file into a bytea column?
You'd need to generate the SQL somehow; if you know python it's probably
a pretty easy 20 or 30 lines of code to get this working. psycopg seems
to be the recommend way of accessing PG with python and you basically
want to be doing something like:
import psycopg2;
filename = myimage.jpeg
conn = psycopg2.connect();
conn.cursor().execute(
INSERT INTO pictures (filename,data) VALUES (%s,%s);,
[filename,psycopg2.Binary(open(filename,rb).read())]);
conn.commit();
This seems to do the right thing for me, and obviously needs to be put
into a loop of some sort. But it'll hopefully get you started.
Sam
*Never used Python or Perl. I use primarily Java. I was thinking of
doing something like
*
*INSERT INTO pictures (filename,data) VALUES
('filename','/path/to/my/image/img0009.jpg');
But, this syntax doesn't seem to be supported.
Maybe I can use a custom C function to get the contents of the file. Then do
something like
***INSERT INTO pictures (filename,data) VALUES
('**/path/to/my/image/img0009.jpg**',getBinaryFileContents('/path/to/my/image/img0009.jpg'));
Is there some postgres contrib for something like this?**
Re: [GENERAL] Use PSQLFS for photo storage
Sam Mason wrote:
On Tue, Jan 13, 2009 at 06:22:34PM -0600, Jason Long wrote:
Sam Mason wrote:
You'd need to generate the SQL somehow; if you know python it's probably
a pretty easy 20 or 30 lines of code to get this working.
*Never used Python or Perl. I use primarily Java. I was thinking of
doing something like
*INSERT INTO pictures (filename,data) VALUES
('filename','/path/to/my/image/img0009.jpg');
If you're OK with using large objects, instead of byteas, you can use
the lo_import function. You'd do something like:
CREATE TABLE pics (
path TEXT PRIMARY KEY,
data OID
);
INSERT INTO pics (path,data)
SELECT path, lo_import(path)
FROM (VALUES ('/path/to/my/image/img0009.jpg')) x(path);
This assumes that the files are accessable to the database server (i.e.
the paths are relative to the server daemon, not the psql command line
or whatever JDBC app you're driving this from).
Does that help any more?
Sam
*They are on the server. I would rather use bytea. Is it possible to
import them as large objects and then use SQL to convert them to bytea?*
Re: [GENERAL] Use PSQLFS for photo storage
Sam Mason wrote: On Wed, Jan 14, 2009 at 12:56:42AM +, Sam Mason wrote: If you Java you'd probably be better off using it Hum, it's getting late. That should be If you *know* Java! Bed time for me I think! Sam Thanks for the advice. I will probably go with Java. In the inventory system I developed I am already storing documents via bytea. I will probably do the same with these images. I will be dealing with them via hibernate so I guess I should import them the same way. I was hoping for a already developed function that could import an entire directory structure recursively. I can do this easily enough in Java.
[GENERAL] Out of control query
I have an inventory system based on PostgreSQL 8.3.5, JBoss, Hibernate.. I have a query builder that lets users filter data in a fairly complex way. For some reason the search gets out of control and consumes all CPU. I set my statement timeout to 2 minutes and this keeps the system from going down totally, but this is happening much more frequently than it used to. I can find the problem with the individual query easily enough, but I would like a more general solution. What seems strange to me is that one one core of my Quad core x5472 is 100%. The others show minimal usage. I would think that other users would get a different core and one query could not deadlock the whole system for everyone. *Do you think this is due to my Postgres or JBoss AS configuration for database connections?* Any advice will be greatly appreciated.
[GENERAL] Multi-table CHECK constraint
I need to add some complex constraints at the DB. For example. Do not allow a line item of inventory to be changed if it does result in the same number of joints originaly shipped. These will involve several tables. What is the best approach for this? Here is what I have been trying. CREATE OR REPLACE FUNCTION numoriginaljts(genericitem_id bigint) RETURNS double precision AS 'select coalesce(vsjo.diff,0) from inventory.t_generic_item gi left join view.generic_item_shipment_id v on v.id=gi.id left join v_shipment_jts_off vsjo on vsjo.shipmentId=v.shipment_id where gi.id=$1;' LANGUAGE 'sql' VOLATILE COST 100; ALTER FUNCTION numoriginaljts(bigint) OWNER TO exploreco; alter table inventory.t_generic_item add constraint check_shipment_original_jts CHECK (numoriginaljts(id)=0); *Does this approach seem reasonable? This did not work, but it is probably my error. It actually let me break the constraint, but my constraint kicked in when I tried to correct the problem. Can someone point me to an example of doing something like this?* The point of this is to never let the total number of original pieces be different than the number originally shipped. My code has done this occasionally and users can override the inventory. Basically I would rather the application throw an error than let this number become unbalanced. -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com
[GENERAL] Multi-table CHECK constraint
I need to add some complex constraints at the DB. For example. Do not allow a line item of inventory to be changed if it does not result in the same number of joints originally shipped. These will involve several tables. What is the best approach for this? Here is what I have been trying. CREATE OR REPLACE FUNCTION numoriginaljts(genericitem_id bigint) RETURNS double precision AS 'select coalesce(vsjo.diff,0) from inventory.t_generic_item gi left join view.generic_item_shipment_id v on v.id=gi.id left join v_shipment_jts_off vsjo on vsjo.shipmentId=v.shipment_id where gi.id=$1;' LANGUAGE 'sql' VOLATILE COST 100; ALTER FUNCTION numoriginaljts(bigint) OWNER TO exploreco; alter table inventory.t_generic_item add constraint check_shipment_original_jts CHECK (numoriginaljts(id)=0); *Does this approach seem reasonable? This did not work, but it is probably my error. It actually let me break the constraint, but my constraint kicked in when I tried to correct the problem. Can someone point me to an example of doing something like this?* The point of this is to never let the total number of original pieces be different than the number originally shipped. My code has done this occasionally and users can override the inventory. Basically I would rather the application throw an error than let this number become unbalanced. -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com
Re: [GENERAL] Multi-table CHECK constraint
Richard Broersma wrote: On Wed, Dec 10, 2008 at 3:58 PM, Jason Long [EMAIL PROTECTED] wrote: I need to add some complex constraints at the DB. These will involve several tables. What is the best approach for this? Well ANSI-SQL provides the CREATE ASSERTION for this purpose. However, PostgreSQL doesn't support this feature. It can be mimiced by using the PostgreSQL CONSTRAINT TRIGGER extenstion. IIRC, it works very much like an ordinary trigger except that you must raise an error when an DML attempt possibly violates your condition. Can someone point me to an example of doing something like this? http://www.postgresql.org/docs/8.3/interactive/sql-createconstraint.html http://www.postgresql.org/docs/8.3/interactive/triggers.html I hope this helps. Thanks. I will look into this and see how it goes. Any other advice would be greatly appreciated.
Re: [GENERAL] Job scheduling in Postgre
Robert Treat wrote: On Tuesday 02 December 2008 07:11:02 A. Kretschmer wrote: am Tue, dem 02.12.2008, um 16:45:16 +0500 mailte IPS folgendes: I have certain jobs to be executed automatically at a given interval of time in the postgre SQL database. Is their any utility/feature available in Postgre to do so. No, use the scheduler from the OS, CRON for example (UNIX). There is a database level schedular called (iirc) pgAgent, which comes bundled with pgAdmin. I think it's so well hidden because it comes as a part of a tool which is only used by a small subset of the community. I had hopes that it might follow autovacuums path and get moved into a contrib module and possibly integrated into the backend some day, but I haven't seen much push in that direction. What is everyone using instead of pgAdmin? In production I only use ssh and psql, but in development I do use pgAdmin.
Re: [GENERAL] Monty on MySQL 5.1: Oops, we did it again
Greg Smith wrote: I wonder if I'm the only one who just saved a copy of that post for reference in case it gets forcibly removed... Recently I was thinking about whether I had enough material to warrant a 2008 update to Why PostgreSQL instead of MySQL; who would have guessed that Monty would do most of the research I was considering for me? -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD I quit using MySQL years ago when the default table type did not have transactions and subqueries were not existent. The features I was looking for were already in PostgreSQL for several versions. I am surprised to see such an honest post regarding MySQL. Sun Picks Up MySQL For $1 Billion http://www.techcrunch.com/2008/01/16/sun-picks-up-mysql-for-1-billion-open-source-is-a-legitimate-business-model/ to bad for them they did not go with PostgreSQL. :)
Re: [GENERAL] Monty on MySQL 5.1: Oops, we did it again
Scott Marlowe wrote: On Mon, Dec 1, 2008 at 4:10 PM, Jason Long [EMAIL PROTECTED] wrote: Greg Smith wrote: I wonder if I'm the only one who just saved a copy of that post for reference in case it gets forcibly removed... Recently I was thinking about whether I had enough material to warrant a 2008 update to Why PostgreSQL instead of MySQL; who would have guessed that Monty would do most of the research I was considering for me? -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD I quit using MySQL years ago when the default table type did not have transactions and subqueries were not existent. The features I was looking for were already in PostgreSQL for several versions. I am surprised to see such an honest post regarding MySQL. Sun Picks Up MySQL For $1 Billion to bad for them they did not go with PostgreSQL. :) It's free. The pgsql community, however, is priceless. No doubt. The pgsql community rocks. In fact the support on this mailing list is top notch and free. :) Thank you a million times over to anyone that has give me advice here. I have never gotten bad advice from this list.
Re: [GENERAL] archive command Permission Denied?
Tom Lane wrote: Jason Long [EMAIL PROTECTED] writes: I got this error /usr/sbin/sendmail: Permission denied So I guess I need to allow the use of sendmail. How is postgres running the command different from my doing it as the postgres user or cron running as the postgres user? SELinux treats it differently: programs that are run as network-accessible daemons get locked down to do only what the SELinux policy says they should be able to do. This is not unreasonable --- if someone managed to crack into your Apache server, for instance, you'd be really glad that they weren't able to use the breach to spam the world from your machine. However, if you want your Postgres server able to do things not listed in the SELinux policy for it, you'll need to adjust that policy. Or disable SELinux ... but I don't really recommend doing that if your machine is at all exposed to the internet. regards, tom lane You were dead on. That was it. I disabled SELinux and it worked. Thank you very much for your advice. Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com
[GENERAL] archive command Permission Denied?
Please bear with me. I am fairly new to Linux. I am working on archiving my WAL files. For now I am just mailing myself a list of the directory contents using the following script. *-rwxr-xr-x 1 postgres postgres 87 Oct 28 20:23 /var/lib/pgsql/mail-WAL-list.sh* the contents are simply *ls -lrt /var/lib/pgsql/data/pg_xlog/ | mail -s WAL files are: [EMAIL PROTECTED] I also have this set up to run in the postgres users cron at 8 a.m. I can run this when logged on as postgres and the cron runs fine as well, but I keep seeing the following in my logs. *LOG: archive command failed with exit code 126 DETAIL: The failed archive command was: /var/lib/pgsql/mail-WAL-list.sh WARNING: transaction log file 000100F0 could not be archived: too many failures sh: /var/lib/pgsql/mail-WAL-list.sh: Permission denied* I would appreciate any advice on what permission I need to set in order for this command to run. -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com
Re: [GENERAL] archive command Permission Denied?
Tom Lane wrote: I wrote: That's just bizarre. The permissions on the script itself seem to be fine, so the only theory that comes to mind is the server doesn't have search (x) permission on one of the containing directory levels ... Oh, wait, I bet I've got it: you're using a SELinux-enabled system and SELinux doesn't believe that it's a good idea to let the Postgres server execute something out of its data directory. *I am using Centos 5.2 and is looks like there is SELinux. This is the first time it has caused me a problem so far.* That would explain why the other methods of executing the script work --- typical SELinux policy is a lot stricter on network-exposed daemon processes than other stuff. If that is what's happening, you'll find avc denied messages in the system log that correlate to the archive failures. *I did not see anything like this in my logs.* The solution I'd recommend is putting the script someplace that's more usual to store scripts. *I moved this inside the postgres data directory. I will post back the results. If this does not work I will have my Linux consultant have a look. Thank you very much for the advice.* You might be able to do something with changing the security context on the script file instead, but I'm not sure exactly what to change it to. regards, tom lane
Re: [GENERAL] archive command Permission Denied?
Tom Lane wrote: Jason Long [EMAIL PROTECTED] writes: Tom Lane wrote: If that is what's happening, you'll find avc denied messages in the system log that correlate to the archive failures. *I did not see anything like this in my logs.* You'd need to look in the system log (/var/log/messages) not the postmaster's log. I did not look in the postmasters logs. I looked in less /var/log/message and less /var/log/secure I saw nothing that looked related to this. This does seem a little strange. I will definitely post back my finding when I resolve this. regards, tom lane
Re: [GENERAL] archive command Permission Denied?
Tom Lane wrote: Jason Long [EMAIL PROTECTED] writes: Tom Lane wrote: If that is what's happening, you'll find avc denied messages in the system log that correlate to the archive failures. *I did not see anything like this in my logs.* You'd need to look in the system log (/var/log/messages) not the postmaster's log. *I think I found the problem. By putting the archive command directly in postgresql.conf I got this error /usr/sbin/sendmail: Permission denied So I guess I need to allow the use of sendmail. How is postgres running the command different from my doing it as the postgres user or cron running as the postgres user? Oh, well time to call my Linux guru. Thanks for all your help. You definitely got me going tin the right direction.* regards, tom lane
Re: [GENERAL] Debugging infrequent pegged out CPU usage
Greg Smith wrote: On Mon, 3 Nov 2008, Jason Long wrote: For some reason Postgres is pegging my CPU and I can barely log on to reboot the machine. Take a look at pg_stat_activity when this happens to see what's going on. Also, try running top -c to see what is going on (the -c displays extra information for the postgresql processes) and save a snapshot of what you see. I normally put a quick script on the server to collect everything I want before even thinking of a restart when I get into this sort of common and ugly situation. Here's a sample; run this next time and you'll be way ahead of where you are now at figuring out what's going wrong: #!/bin/bash date crash psql -c select * from pg_stat_activity crash top -c -b -n 1 crash vmstat 1 5 crash iostat 1 5 crash psql -c select * from pg_stat_activity crash date crash *Thanks for your advice. This is one of the most helpful mailing lists ever. :)* Can I limit Postgres to a certain number of cores or set the timeout on the queries to a lower value? You should at a minimum set log_min_duration_statement to figure out what the statements taking so long are. At the root of this problem there's probably some bad queries. I'd bet you start seeing an increase of those reported in the logs in advance of when the server becomes completely unresponsive. While there are relatively live few users the data is extremely important and the users will not wait for me to see what is wrong. They demand immediate resolution and the best I can do is reboot. Well, if you don't have any idea how to fix the problem, that's reasonable I guess (although stopping just the postgresql process with pg_ctl is probably all that's really needed, a full reboot is just prolonging the downtime). Once you've gotten some ideas for what to look at, like the little script above, you have to make the users wait until you're done running that before giving into pressure to fix something. Otherwise you'll never solve the problem. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
Re: [GENERAL] JDBC and setting statement_timeout
Kris Jurka wrote: On Mon, 3 Nov 2008, Jason Long wrote: *Would someone please comment on the status of setQueryTimeout in the JDBC driver? Is there any workaround if this is still not implemented?* setQueryTimeout is not implemented, the workaround is to manually issue SET statement_timeout = xxx calls via Statement.execute. Kris Jurka 1. Could you provide a code sample to work with straight JDBC? 2. Can someone advise how this might work with EJB3/Hibernate? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Debugging infrequent pegged out CPU usage
I am running PostgreSQL 8.3.4 on Centos 5.2 with a single Xeon 5472, 1600 MHz, 12 MB cache, 3.0 GHz quad core, and 4 GB RAM. My database is only about 50 MB and there are only about 20 users. For some reason Postgres is pegging my CPU and I can barely log on to reboot the machine. After reboot all is well for another week or so, but this brings the system to a grinding halt. * What is the best way to debug this? Can I limit Postgres to a certain number of cores or set the timeout on the queries to a lower value?* I would greatly appreciate any advice on debugging this problem. While there are relatively live few users the data is extremely important and the users will not wait for me to see what is wrong. They demand immediate resolution and the best I can do is reboot. -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com
Re: [GENERAL] Debugging infrequent pegged out CPU usage
Scott Marlowe wrote: On Mon, Nov 3, 2008 at 12:25 PM, Scott Marlowe [EMAIL PROTECTED] wrote: On Mon, Nov 3, 2008 at 11:30 AM, Jason Long [EMAIL PROTECTED] wrote: I am running PostgreSQL 8.3.4 on Centos 5.2 with a single Xeon 5472, 1600 MHz, 12 MB cache, 3.0 GHz quad core, and 4 GB RAM. My database is only about 50 MB and there are only about 20 users. For some reason Postgres is pegging my CPU and I can barely log on to reboot the machine. After reboot all is well for another week or so, but this brings the system to a grinding halt. What is the best way to debug this? Can I limit Postgres to a certain number of cores or set the timeout on the queries to a lower value? How about preventing this lockup by limiting CPU resources to Postgres or giving up if a query takes too long? I am barely able to log in let alone poke around. Best way I've found it to keep track of the server over a period of time. nagios and mrtg are your friends here. Thank you for the advice. I will investigate these options. You can use some more primitive methods, like ps ax|grep postgres|wc -l to see how many postgres backends are running. You need to figure out exactly what's happening to the machine before it dies, but as its approaching that point. Also, use the built in pg_xxx tables / views that show you what the server is doing. use vmstat, iostat, top and other tools to keep track. If you're on Windows, ignore all that and ask someone else cause I don't know enough about troubleshooting windows systems to be a lot of help there.
[GENERAL] JDBC and setting statement_timeout
In order to keep my application from freezing up when a query pegs my CPU I set statement_timeout=12, but I read in the manual Setting statement_timeout in postgresql.conf is not recommended because it affects all sessions. I am used JDBC exclusively for the applicatoin and I read here http://www.nabble.com/Implementing-setQueryTimeout()-ts15537669.html http://www.nabble.com/Implementing-setQueryTimeout%28%29-ts15537669.html that setTimeout is not implemented in the JDBC driver for PostgreSQL. I would like for nearly all of my queries to throw and error if they do not complete in a certain time and if I know if might take more I would like to set this explicitly for that query. *Would someone please comment on the status of setQueryTimeout in the JDBC driver? Is there any workaround if this is still not implemented?*
Re: [GENERAL] Decreasing WAL size effects
Greg Smith wrote: On Thu, 30 Oct 2008, Joshua D. Drake wrote: This reminds me yet again that pg_clearxlogtail should probably get added to the next commitfest for inclusion into 8.4; it's really essential for a WAN-based PITR setup and it would be nice to include it with the distribution. What is to be gained over just using rsync with -z? When a new XLOG segment is created, it gets zeroed out first, so that there's no chance it can accidentally look like a valid segment. But when an existing segment is recycled, it gets a new header and that's it--the rest of the 16MB is still left behind from whatever was in that segment before. That means that even if you only write, say, 1MB of new data to a recycled segment before a timeout that causes you to ship it somewhere else, there will still be a full 15MB worth of junk from its previous life which may or may not be easy to compress. I just noticed that recently this project has been pushed into pgfoundry, it's at http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/clearxlogtail/clearxlogtail/ What clearxlogtail does is look inside the WAL segment, and it clears the tail behind the portion of that is really used. So our example file would end up with just the 1MB of useful data, followed by 15MB of zeros that will compress massively. Since it needs to know how XLogPageHeader is formatted and if it makes a mistake your archive history will be silently corrupted, it's kind of a scary utility to just download and use. I would really like to add something like this to my application. 1. Should I be scared or is it just scary in general? 2. Is this safe to use with 8.3.4? 3. Any pointers on how to install and configure this? That's why I'd like to see it turn into a more official contrib module, so that it will never lose sync with the page header format and be available to anyone using PITR. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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] Decreasing WAL size effects
Kyle Cordes wrote: Greg Smith wrote: there's no chance it can accidentally look like a valid segment. But when an existing segment is recycled, it gets a new header and that's it--the rest of the 16MB is still left behind from whatever was in that segment before. That means that even if you only write, say, 1MB of new [...] What clearxlogtail does is look inside the WAL segment, and it clears the tail behind the portion of that is really used. So our example file would end up with just the 1MB of useful data, followed by 15MB of It sure would be nice if there was a way for PG itself to zero the unused portion of logs as they are completed, perhaps this will make it in as part of the ideas discussed on this list a while back to make a more out of the box log-ship mechanism? *I agree totally. I looked at the code for clearxlogtail and it seems short and not very complex. Hopefully something like this will at least be a trivial to set up option in 8.4.** *
Re: [GENERAL] Decreasing WAL size effects
Greg Smith wrote: On Thu, 30 Oct 2008, Tom Lane wrote: The real reason not to put that functionality into core (or even contrib) is that it's a stopgap kluge. What the people who want this functionality *really* want is continuous (streaming) log-shipping, not WAL-segment-at-a-time shipping. Sure, and that's why I didn't care when this got kicked out of the March CommitFest; was hoping a better one would show up. But if 8.4 isn't going out the door with the feature people really want, it would be nice to at least make the stopgap kludge more easily available. +1 Sure I would rather have synchronous WAL shipping, but if that is going to be a while or synchronous would slow down my applicaton I can get comfortably close enough for my purposes with some highly compressible WALs. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Decreasing WAL size effects
I am planning on setting up PITR for my application. It does not see much traffic and it looks like the 16 MB log files switch about every 4 hours or so during business hours. I am also about to roll out functionality to store documents in a bytea column. This should make the logs roll faster. I also have to ship them off site using a T1 so setting the time to automatically switch files will just waste bandwidth if they are still going to be 16 MB anyway. *1. What is the effect of recompiling and reducing the default size of the WAL files? 2. What is the minimum suggested size? 3. If I reduce the size how will this work if I try to save a document that is larger than the WAL size? Any other suggestions would be most welcome. * Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com
Re: [GENERAL] Annoying Reply-To
I am not fond of this approach either. I never find myself replying directly to the poster. I actually greatly prefer forums which email me a copy of every post with a nice link to the original thread. 95% of the time I do not even need to use the link. The latest posting is enough. This makes things more organized as accessible. Dave Coventry wrote: I am a member of a number of lists, some of which exhibit this 'reply-to' behaviour and I have also managed to adapt... to a point. Sometimes, however, I do end up replying directly to the poster rather than through the list. Tellingly, I very nearly sent this post directly to Serge Fonvilee. Without wanting to be too controversial, I have generally found that the lists which have the default reply configured like this do tend to be those that are dominated by members who are, shall we say, pedantic about protocol and 'netiquette'. Personally I would prefer the default reply-to to go to the list, but I'm not really bothered about it. My 2 cents. Disclaimer: If we are not talking about the default 'reply-to' behaviour of this list, please ignore this post; I came upon the thread late and it is possible that I am at cross purposes. Kind reagards, Dave Coventry 2008/10/17 Serge Fonville [EMAIL PROTECTED]: Altough I am not sure what the real issue is, I do know that on (for example) the tomcat mailing list, when I choose reply (in gmail) the to: field contains the address of the mailing list. Based on what I know, this should be relatively easy to set up in the mailing list manager. just my 2ct Serge Fonvilee
Re: [GENERAL] auto insert data every one minute
Richard Broersma wrote: On Fri, Oct 17, 2008 at 12:32 AM, searchelite [EMAIL PROTECTED] wrote: I have an sql script consists of insert statement data. I want to insert every row of data every one minute. How can i do that using batch file in windows Take a look at your windows scheduler in the control panel. You might need to spend quite a bit of time configuring a 1 minute interval since it only seems to be designed for daily occurrences. Use Linux and you life will become much easier. ;)
Re: [GENERAL] Slony vs Longiste
Richard Huxton wrote: Jason Long wrote: I need to set up master vs slave replication. My use case is quite simple. I need to back up a small but fairly complex(30 MB data, 175 tables) DB remotely over T1 and be able to switch to that if the main server fails. The switch can even be a script run manually. Can someone either comment in as much detail as possible or point me to a comparison of Slony vs Longiste. Or some other option I have not heard of? Three questions you need to ask yourself. 1. How heavily updated is the database? 2. How often do you change the database's schema? 3. Are there other databases in the installation? If #1 is very heavy then you'll want to do some testing with any solution you use. If #2 is a lot then you'll want to consider WAL shipping as mentioned below. Slony can handle schema changes, but you'll need to process them through its own script. I'm afraid I can't comment on Londiste. If you just want a backup and the answer to #3 is no, look at WAL shipping (see the various archive_xxx config settings in the manual and google a bit). From what I read Longiste is easy to set up while I got a quote for Slony setup for 5-10k. Unless your requirements are strange, that seems a little high, even assuming USD as a currency. Of course, if you want support and maintenance that will tend to make things mount. The database has 10-20 concurrent users so updates are not very heavy. The schema changes very frequently. There are not other databases in the installation. This quote included initial setup, failure testing, and scripts that were to automate setup and manage the installation. It did not include support and maintenance.
Re: [GENERAL] Slony vs Longiste
Robert Treat wrote: On Wednesday 24 September 2008 12:34:17 Jason Long wrote: Richard Huxton wrote: Jason Long wrote: I need to set up master vs slave replication. My use case is quite simple. I need to back up a small but fairly complex(30 MB data, 175 tables) DB remotely over T1 and be able to switch to that if the main server fails. The switch can even be a script run manually. Can someone either comment in as much detail as possible or point me to a comparison of Slony vs Longiste. Or some other option I have not heard of? Three questions you need to ask yourself. 1. How heavily updated is the database? 2. How often do you change the database's schema? 3. Are there other databases in the installation? If #1 is very heavy then you'll want to do some testing with any solution you use. If #2 is a lot then you'll want to consider WAL shipping as mentioned below. Slony can handle schema changes, but you'll need to process them through its own script. I'm afraid I can't comment on Londiste. If you just want a backup and the answer to #3 is no, look at WAL shipping (see the various archive_xxx config settings in the manual and google a bit). From what I read Longiste is easy to set up while I got a quote for Slony setup for 5-10k. Unless your requirements are strange, that seems a little high, even assuming USD as a currency. Of course, if you want support and maintenance that will tend to make things mount. The database has 10-20 concurrent users so updates are not very heavy. The schema changes very frequently. There are not other databases in the installation. This quote included initial setup, failure testing, and scripts that were to automate setup and manage the installation. It did not include support and maintenance. Are you planning on hiring someone to do it, or are you going to do it yourself, because the prices of the solution is completely orthogonal to which is the better fit technically. In your case, since you do a lot of DDL changes, I'd go with londiste over slony if I had to pick from those two. However, given the requirements you laid out, PITR is probably your best option (this is what Richard alluded too), and certainly the one I would recommend you try first. I am looking at a combination of hiring someone for setup and advice and them maintaining it myself. I agree PITR is probably a good fit. How far time wise would could the fall behind the live server and what would affect that? Anything else I should consider if I go PITR? The default size of WAL segment files is 16 MB. Since my entire DB is only 30 MB(will grow quickly as I am going to add internal document storage soon). Will I need to recompile Postgres to reduce this? What will be the repercussions of reducing the size?
[GENERAL] Slony vs Longiste
I need to set up master vs slave replication. My use case is quite simple. I need to back up a small but fairly complex(30 MB data, 175 tables) DB remotely over T1 and be able to switch to that if the main server fails. The switch can even be a script run manually. Can someone either comment in as much detail as possible or point me to a comparison of Slony vs Longiste. Or some other option I have not heard of? From what I read Longiste is easy to set up while I got a quote for Slony setup for 5-10k. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Replication setup
I have a custom inventory system built on JBoss AS, Seam, EJB3, JSF, and Richfaces with a PostgreSQL back end that runs on Centos. Being a single developer my time is short and I need to set up remote replication and fail over without delaying what I am currently working on. I have been reading about Slony and more recently Londiste. I need help setting this up in a reliable manner quickly. I need advice on what to use and scripts that I can run on my servers to set this up automatically. Please email me directly at (jason at supernovasoftware dot com) if you can recommend a company that can get this done for me quickly and economically. Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.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] Full vacuum really slowing query down
I have a query that takes 2.5 sec if I run it from a freshly restored dump. If I run a full vacuum on the database it then takes 30 seconds. Would someone please comment as to why I would see over a 10x slow down by only vacuuming the DB? I am using 8.3.1 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] full vacuum really slows down query
I have a query that takes 2 sec if I run it from a freshly restored dump. If I run a full vacuum on the database it then takes 30 seconds. Would someone please comment as to why I would see a 15x slow down by only vacuuming the DB? I am using 8.3.1 -- 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] full vacuum really slows down query
Thanks for the advice. I will keep playing with it. Can someone here comment on EnterpriseDB or another companies paid support? I may consider this to quickly improve my performance. Scott Marlowe wrote: Have you run analyze on the tables? bumped up default stats and re-run analyze? Best way to send query plans is as attachments btw. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] full vacuum really slows down query
I have a query that takes 2 sec if I run it from a freshly restored dump. If I run a full vacuum on the database it then takes 30 seconds. Would someone please comment as to why I would see a 15x slow down by only vacuuming the DB? Reindexing does not help, and a full vacuum was run just prior to the dump. I run a full vacuum every night. I am using 8.3.1 -- 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] full vacuum really slows down query
Yes. This is what I do. 1. Vacuum full 2. Reindex force This is done ever night My users complained about a report being slow. I grabbed a dump and restored it to my development machine. The query worked just fine, but not on the production server. I did a vacuum full and then reran the query. Now it performs just as slow as on the production machine. That is the only change I made. I am in the process of trying to figure out the EXPLAIN ANALYZE for both queries, but I have never had to read this output and I am unfamiliar with how to find the problem. I will post both here and maybe someone can help me out. Joshua D. Drake wrote: On Wed, 2008-06-04 at 17:02 -0500, Jason Long wrote: I have a query that takes 2 sec if I run it from a freshly restored dump. If I run a full vacuum on the database it then takes 30 seconds. If you run it a second time after the vacuum full? Joshua D. Drake
Re: [GENERAL] full vacuum really slows down query
) Filter: condition7_.needsprojection - Index Scan using t_action_pkey on t_state state9_ (cost=0.00..5.77 rows=1 width=16) (actual time=0.132..0.091 rows=1 loops=6066) Index Cond: (state9_.id = pipe0_1_.state_id) - Index Scan using pk_t_status on t_status status10_ (cost=0.00..0.27 rows=1 width=8) (actual time=0.047..0.091 rows=1 loops=6066) Index Cond: (status10_.id = state9_.status_id) Filter: status10_.needsprojection - Hash Join (cost=81.35..460.86 rows=2004 width=23) (actual time=0.092..11.790 rows=998 loops=3174) Hash Cond: (popipe1_1_.id = popipe1_.id) - Seq Scan on t_state popipe1_1_ (cost=0.00..330.83 rows=5727 width=15) (actual time=0.087..8.880 rows=5732 loops=3174) Filter: (NOT spec) - Hash (cost=49.49..49.49 rows=2549 width=8) (actual time=2.507..2.507 rows=2549 loops=1) - Seq Scan on t_po_pipe popipe1_ (cost=0.00..49.49 rows=2549 width=8) (actual time=0.015..1.208 rows=2549 loops=1) - Index Scan using pk_t_actor on t_actor actor5_ (cost=0.00..1.18 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=427) Index Cond: (actor5_.id = pipe0_1_.actor_id) - Index Scan using pk_t_contact on t_contact contact6_ (cost=0.00..0.36 rows=1 width=16) (actual time=-0.597..-0.597 rows=1 loops=427) Index Cond: (contact6_.id = actor5_.contact_id) - Index Scan using pk_t_endfinish on t_endfinish endfinish4_ (cost=0.00..0.52 rows=1 width=15) (actual time=0.005..0.005 rows=1 loops=427) Index Cond: (endfinish4_.id = pipe0_.endfinish_id) - Index Scan using pk_t_grade on t_grade grade3_ (cost=0.00..0.52 rows=1 width=14) (actual time=0.004..0.005 rows=1 loops=427) Index Cond: (grade3_.id = pipe0_.grade_id) - Index Scan using pk_t_weight on t_weight weight2_ (cost=0.00..0.60 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=427) Index Cond: (weight2_.id = pipe0_.weight_id) Total runtime: 36650.609 ms Tom Lane wrote: Jason Long [EMAIL PROTECTED] writes: I have a query that takes 2 sec if I run it from a freshly restored dump. If I run a full vacuum on the database it then takes 30 seconds. Would someone please comment as to why I would see a 15x slow down by only vacuuming the DB? EXPLAIN ANALYZE of both cases might yield some insight. 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] Automatic install on windows
I have a client that wants a disaster recovery plan put into place. What is the easiest way to do a hands free install of postgresql on a window box? Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.supernovasoftware.com HJBUG Founder and President http://www.hjbug.com
[GENERAL] Is there any way to index or cache a view, or function results?
Is it possible to define a function or view that performs fairly intensive calculations and then index or cache these results? The data I have will be accessed more than modified, but still will be modified semi regularly. Would someone please enlighten me on my options for improving performance is this situation? Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.supernovasoftware.com HJBUG Founder and President http://www.hjbug.com -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006
Re: [GENERAL] Is there any way to index or cache a view, or function results?
I was hoping for something a bit more automatic with less maintenance from me. Thank you for your reply. Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.supernovasoftware.com HJBUG Founder and President http://www.hjbug.com -Original Message- From: Tomi NA [mailto:[EMAIL PROTECTED] Sent: Saturday, July 01, 2006 4:17 PM To: [EMAIL PROTECTED] Subject: Re: [GENERAL] Is there any way to index or cache a view, or function results? On 7/1/06, Jason Long [EMAIL PROTECTED] wrote: Is it possible to define a function or view that performs fairly intensive calculations and then index or cache these results? The data I have will be accessed more than modified, but still will be modified semi regularly. Would someone please enlighten me on my options for improving performance is this situation? You could create a new table to store the results in and refresh it's contents every time the original data changes and you can index whatever you want, as long as you take into account that frequent and extensive changes to a table with a couple of indexes might slow things down a bit, depending on the number of changed records, the number of indices you define on the table and so on... You can keep the data in sync using triggers or with a periodic update task, depending on what kind of precision you need. Cheers, t.n.a. -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Is there any way to index or cache a view, or function results?
Thanks that is basically what I was looking for I will investigate further. I appreciate your response. Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.supernovasoftware.com HJBUG Founder and President http://www.hjbug.com -Original Message- From: Richard Broersma Jr [mailto:[EMAIL PROTECTED] Sent: Saturday, July 01, 2006 4:49 PM To: [EMAIL PROTECTED]; pgsql-general@postgresql.org Subject: Re: [GENERAL] Is there any way to index or cache a view, or function results? I was hoping for something a bit more automatic with less maintenance from me. Thank you for your reply. On 7/1/06, Jason Long [EMAIL PROTECTED] wrote: Is it possible to define a function or view that performs fairly intensive calculations and then index or cache these results? The data I have will be accessed more than modified, but still will be modified semi regularly. Would someone please enlighten me on my options for improving performance is this situation? You could create a new table to store the results in and refresh it's contents every time the original data changes and you can index whatever you want, as long as you take into account that frequent and extensive changes to a table with a couple of indexes might slow things down a bit, depending on the number of changed records, the number of indices you define on the table and so on... You can keep the data in sync using triggers or with a periodic update task, depending on what kind of precision you need. Perhaps an implemenation of a materialized view is more what you would like? http://archives.postgresql.org/pgsql-performance/2004-02/msg00279.php http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Regards, Richard Broersma jr. -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] pg_dump from Java or SQL?
Is it possible to access backup and restore functionality
from SQL or Java? I am attempting to allow some this functionality from a
webapp.
The best I have so far is the following Java code.
Runtime runtime = Runtime.getRuntime();
String[] cmd = { cmd,
/c,
C:/PostgreSQL/8.0/pgAdmin_III/pg_dump.exe
-i -h localhost -p 5432 -U postgres -F c -b -v -f C:/
+(new Date()).getTime()
+.backup mydatabase
};
Process p = runtime.exec(cmd);
Thank you for your time,
Jason Long
CEO and Chief Software Engineer
BS Physics, MS Chemical Engineering
http://www.supernovasoftware.com
[GENERAL] information_schema._pg_keypositions() in 8.1???
I cannot get automatic schema update to work in 8.1 with hibernate 3.0.5. I get the following error every time. java.sql.SQLException: ERROR: function information_schema._pg_keypositions() does not exist Is this something I should be looking to fix with Hibernate or PostgreSQL? Any assistance would be greatly appreciated. Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.supernovasoftware.com
Re: [GENERAL] information_schema._pg_keypositions() in 8.1???
Executing the following from
information_schema.sql from the 8.0 distribution against the
restored database in 8.1 solved the problem. Is this a bug?
*
SET search_path TO
information_schema, public;
/*
* A few supporting
functions first ...
*/
/* This returns the integers
from 1 to INDEX_MAX_KEYS/FUNC_MAX_ARGS */
CREATE FUNCTION
_pg_keypositions() RETURNS SETOF integer
LANGUAGE
sql
IMMUTABLE
AS
'select g.s
from generate_series(1,current_setting(''max_index_keys'')::int,1)
as g(s)';
Thank you for your time,
Jason Long
CEO and Chief Software Engineer
BS Physics, MS Chemical Engineering
http://www.supernovasoftware.com
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jason Long
Sent: Thursday, December 01, 2005
11:43 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL]
information_schema._pg_keypositions() in 8.1???
I cannot get automatic schema update to work in 8.1 with
hibernate 3.0.5. I get the following error every time.
java.sql.SQLException: ERROR: function
information_schema._pg_keypositions() does not exist
Is this something I should be looking to fix with Hibernate
or PostgreSQL?
Any assistance would be greatly appreciated.
Thank you for your time,
Jason Long
CEO and Chief Software Engineer
BS Physics, MS Chemical Engineering
http://www.supernovasoftware.com
