Re: [GENERAL] Overlapping ranges

[Jason Long]

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] Overlapping ranges

[Jason Long]

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

[GENERAL] ON DELETE CASCADE Question

[Jason Long]

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

Re: [GENERAL] Problem with left join when moving a column to another table

[Jason Long]

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.

Re: [GENERAL] Re: Easiest way to compare the results of two queries row by row and column by column

[Jason Long]

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

[Jason Long]

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.
> 
> 

[GENERAL] Easiest way to compare the results of two queries row by row and column by column

[Jason Long]

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

[Jason Long]

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] Problem with left join when moving a column to another table

[Jason Long]

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.

[GENERAL] Optimizing Queries Joining Several Views

[Jason Long]

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

[Jason Long]

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?

Re: [GENERAL] Supply Chain Calcs

[Jason Long]

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  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.
>>
>
>

[GENERAL] Supply Chain Calcs

[Jason Long]

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?

[Jason Long]

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.

[GENERAL] Any was to prevent a join if no columns are selected from a view?

[Jason Long]

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

Re: [GENERAL] Identifying old/unused views and table

[Jason Long]

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] Identifying old/unused views and table

[Jason Long]

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

Re: [GENERAL] comma vs cross join question

[Jason Long]

On Fri, 2011-04-08 at 14:45 -0400, Tom Lane wrote:
> Jason Long  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

[GENERAL] Count for pagination

[Jason Long]

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

[GENERAL] Count for pagination

[Jason Long]

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] comma vs cross join question

[Jason Long]

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

Re: [GENERAL] Full Vacuum/Reindex vs autovacuum

[Jason Long]

On Mon, 2010-11-08 at 16:23 -0700, Scott Marlowe wrote:
> On Mon, Nov 8, 2010 at 3:42 PM, Jason Long  wrote:
> > On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote:
> >> On Mon, Nov 8, 2010 at 11: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,
> >>
> >> 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

[Jason Long]

On Mon, 2010-11-08 at 16:23 -0700, Scott Marlowe wrote:
> On Mon, Nov 8, 2010 at 3:42 PM, Jason Long  wrote:
> > On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote:
> >> On Mon, Nov 8, 2010 at 11: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,
> >>
> >> 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

[Jason Long]

On Mon, 2010-11-08 at 16:23 -0700, Scott Marlowe wrote:
> On Mon, Nov 8, 2010 at 3:42 PM, Jason Long  wrote:
> > On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote:
> >> On Mon, Nov 8, 2010 at 11: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,
> >>
> >> 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] Full Vacuum/Reindex vs autovacuum

[Jason Long]

On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote:
> On Mon, Nov 8, 2010 at 11: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,
> 
> 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

[Jason Long]

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

[Jason Long]

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

[GENERAL] Full Vacuum/Reindex vs autovacuum

[Jason Long]

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

[GENERAL] Full Vacuum/Reindex vs autovacuum

[Jason Long]

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

Re: [GENERAL] Linux

[Jason Long]

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

[Jason Long]

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

[Jason Long]

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?

[Jason Long]

Gregory Stark wrote:

"Daniel Verite"  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?

[Jason Long]

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?

[Jason Long]

Gregory Stark wrote:

Jason Long  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] Pet Peeves?

[Jason Long]

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] New 8.4 hot standby feature

[Jason Long]

Fujii Masao wrote:

Hi,

On Wed, Jan 28, 2009 at 4:28 AM, Gabi Julien  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  
going to be in contrib or integrated in some other way?

Re: [GENERAL] difference between current_timestamp and now() in quotes

[Jason Long]

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

[Jason Long]

David Wilson wrote:

On Fri, Jan 16, 2009 at 3:27 PM, Jason Long
 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?

Re: [GENERAL] Query sometimes takes down server

[Jason Long]

Scott Marlowe wrote:

On Thu, Jan 15, 2009 at 11:04 PM, Jeff Davis  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

[Jason Long]

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
 wrote:
  

Scott Marlowe wrote:

On Thu, Jan 15, 2009 at 11:04 PM, Jeff Davis  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

[Jason Long]

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


  

[GENERAL] Query sometimes takes down server

[Jason Long]

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

[Jason Long]

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.


  

Re: [GENERAL] Vacuum and Reindex hangs

[Jason Long]

Scott Marlowe wrote:

On Thu, Jan 15, 2009 at 1:28 PM, Jason Long
 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: HTML email (was Re: [GENERAL] Vacuum and Reindex hangs

[Jason Long]

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

[Jason Long]

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:

I 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: [GENERAL] Vacuum and Reindex hangs

[Jason Long]

Alan Hodgson wrote:
On Thursday 15 January 2009, Jason Long  
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.

[GENERAL] Vacuum and Reindex hangs

[Jason Long]

*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] Use PSQLFS for photo storage

[Jason Long]

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.

Re: [GENERAL] Use PSQLFS for photo storage

[Jason Long]

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.

Re: [GENERAL] Use PSQLFS for photo storage

[Jason Long]

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 "bytea"s, 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

[Jason Long]

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

[Jason Long]

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

[Jason Long]

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  
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.


  

[GENERAL] Use PSQLFS for photo storage

[Jason Long]

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

[GENERAL] Out of control query

[Jason Long]

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

[Jason Long]

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   

Re: [GENERAL] Multi-table CHECK constraint

[Jason Long]

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.

[GENERAL] Multi-table CHECK constraint

[Jason Long]

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] Job scheduling in Postgre

[Jason Long]

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"

[Jason Long]

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] Monty on MySQL 5.1: "Oops, we did it again"

[Jason Long]

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.  :)

Re: [GENERAL] archive command Permission Denied?

[Jason Long]

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  

Re: [GENERAL] archive command Permission Denied?

[Jason Long]

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] archive command Permission Denied?

[Jason Long]

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?

[Jason Long]

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
  

[GENERAL] archive command Permission Denied?

[Jason Long]

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] Debugging infrequent pegged out CPU usage

[Jason Long]

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

[Jason Long]

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] JDBC and setting statement_timeout

[Jason Long]

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 



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] Debugging infrequent pegged out CPU usage

[Jason Long]

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] Debugging infrequent pegged out CPU usage

[Jason Long]

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] Decreasing WAL size effects

[Jason Long]

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

Re: [GENERAL] Decreasing WAL size effects

[Jason Long]

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

[Jason Long]

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

[GENERAL] Decreasing WAL size effects

[Jason Long]

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] auto insert data every one minute

[Jason Long]

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] Annoying Reply-To

[Jason Long]

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] Slony vs Longiste

[Jason Long]

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?

Re: [GENERAL] Slony vs Longiste

[Jason Long]

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.

[GENERAL] Slony vs Longiste

[Jason Long]

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

[Jason Long]

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

Re: [GENERAL] full vacuum really slows down query

[Jason Long]

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

[Jason Long]

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

[GENERAL] Full vacuum really slowing query down

[Jason Long]

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

Re: [GENERAL] full vacuum really slows down query

[Jason Long]

  
->  Index Scan using t_pipe_pkey on t_pipe p  (cost=0.00..8.27 rows=1 
width=48) (actual time=0.059..0.059 rows=1 loops=9644)"
"  
Index Cond: (id = $0)"
"
->  Index Scan using t_generic_item_pkey on t_generic_item gi  
(cost=0.00..8.27 rows=1 width=16) (actual time=0.031..0.031 rows=1 
loops=9644)"
"  
Index Cond: (gi.id = $0)"
"  
->  Index Scan using uk_projection_weight_grade_endfinish_actor_condtion 
on t_projection pr  (cost=0.00..8.28 rows=1 width=56) (actual 
time=-0.075..-0.075 rows=0 loops=9644)"
"
Index Cond: ((pr.popipe_id = p.popipe_id) AND (pr.weight_id = 
p.weight_id) AND (pr.grade_id = p.grade_id) AND (pr.endfinish_id = 
p.endfinish_id) AND (pr.actor_id = gi.actor_id) AND (pr.condition_id = 
p.condition_id))"
"->  
Index Scan using t_generic_item_pkey on t_generic_item pipe0_1_  
(cost=0.00..5.69 rows=1 width=32) (actual time=-0.069..-0.067 rows=1 
loops=7236)"
"  
Index Cond: (pipe0_1_.id = pipe0_.id)"
"  ->  Index 
Scan using pk_t_condition on t_condition condition7_  (cost=0.00..0.52 
rows=1 width=15) (actual time=-0.139..-0.209 rows=1 loops=7236)"
"
Index Cond: (condition7_.id = pipe0_.condition_id)"
"
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

Re: [GENERAL] full vacuum really slows down query

[Jason Long]

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


  

[GENERAL] full vacuum really slows down query

[Jason Long]

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

[GENERAL] Automatic install on windows

[Jason Long]

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

 

Re: [GENERAL] Is there any way to index or cache a view, or function results?

[Jason Long]

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

Re: [GENERAL] Is there any way to index or cache a view, or function results?

[Jason Long]

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

[GENERAL] Is there any way to index or cache a view, or function results?

[Jason Long]

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
 

[GENERAL] pg_dump from Java or SQL?

[Jason Long]

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 

 

Re: [GENERAL] information_schema._pg_keypositions() in 8.1???

[Jason Long]

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 

 

[GENERAL] information_schema._pg_keypositions() in 8.1???

[Jason Long]

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