Re: [GENERAL] [ADMIN] postgresql server crash on windows 7 when using plpython

[Craig Ringer]

On 9/08/2011 10:54 PM, c k wrote:


Normal python functions returning text etc. are working but when conatining

import sys
from uuid import getnode as get_mac
mac = get_mac()
return mac

fails. What will be the reason?


You still haven't supplied the error message you get when you run this.

In the absence of better information, my guess would be that python's 
uuid module uses ossp-uuid, same as PostgreSQL does, but a different 
version or one compiled differently. The PostgreSQL `bin' dir with the 
postgresql version of the DLL will be in the path before the Python one, 
so Python is calling into a different version of the DLL than it expects 
and probably crashing as a result.


That would be consistent with it working from the python command line.

To work around this, I'd either use the ossp-uuid functions via the SPI 
rather than using the Python UUID module, or I'd remove the OSSP-UUID 
dll from the postgresql directory. You can certainly try that to see if 
it helps.


This is one of the joys you get with Windows software not being managed 
by a central packaging system. Everyone bundles their own versions of 
all the dependencies, leaving messes like this where two DLLs with the 
same name aren't quite compatible. Yay!


--
Craig Ringer

--
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] postgresql server crash on windows 7 when using plpython

[c k]

Here is the actual function.
create or replace function software.python_test() returns text as
$body$
import sys
from uuid import getnode as get_mac
mac = get_mac()
return mac
$body$
language plpythonu volatile security definer;

When running the same code from python prompt, it run correctly without any
error.


Chaitanya Kulkarni
On Wed, Aug 10, 2011 at 12:57 AM, Adrian Klaver wrote:

>
>
> On Tue, Aug 9, 2011 at 7:54 AM, c k  wrote:
>
>> Dear Friends,
>> For client requirements, I am testing postgresql 9.0.4 on windows 7 with
>> plpython. First I installed python 2.7 and then added python installation
>> path to windows PATH variable. Then installed postgresql from one-click
>> installer. Server started and every thing was ok. Then I go to create
>> plpythonu in a database. But it failed. After searching, I found that it
>> needs python 2.6. So I again installed python 2.6 and then dropped plpython
>> from database and created again. It was successful. Normal python functions
>> returning text etc. are working but when conatining
>>
>> import sys
>> from uuid import getnode as get_mac
>> mac = get_mac()
>> return mac
>>
>> fails. What will be the reason?
>>
>
> What would be the error message(s)?
>
> The actual function code would also be helpful, to put things into context.
>
>
>>
>> Advance thanks for your help.
>>
>> Chaitanya Kulkarni
>>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>

Re: [GENERAL] Indicating DEFAULT values in INSERT statement

[Postgres User]

Good idea but you're right, if the default value is an expression such as a
the next serial value, it will need to be executed first.  If Execute
doesn't return the interpreted value, I don't know of a way to make it
work..

On Tue, Aug 9, 2011 at 5:35 PM, Diego Augusto Molina <
diegoaugustomol...@gmail.com> wrote:

> This is a bit hacky, but it may work (I haven't tried it yet). If it
> works let us know and if it doesn't then maybe we can debug it and get
> something useful, or move on to another solution.
>
> 
>  INSERT INTO public.test
> (
> userid, object_id, user_notes, object_status, created_ts
> )
> VALUES
> (
> p_userid, p_obj_id, p_user_notes, p_obj_status,
>Case When p_created_ts Is Not Null Then p_created_ts Else
> ( -- You may want to be sure the field has a default value.
>SELECT d.adsrc -- or should it be d.adbin?
>FROM
>pg_catalog.pg_attribute a INNER JOIN
>pg_catalog.pg_attrdef d ON (a.attnum =
> d.adnum)
>WHERE
>a.attname = 'created_ts' AND
>a.attrelid = 'public.test'::REGCLASS AND
>d.adrelid = 'public.test'::REGCLASS
>) End
> );
> 
>
> Well (thinking it thoroughly) it won't work at all as is. It will just
> put the source code of the default expression but you would need to
> *interpret* it first.
> Looked for a way to do this (without `EXECUTE') but couldn't find it.
> Sorry.
>
> --
> Diego Augusto Molina
> diegoaugustomol...@gmail.com
>
> ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
> desestimados.
> EN: Please, avoid attaching Microsoft Office documents. They shall be
> discarded.
> LINK: http://www.gnu.org/philosophy/no-word-attachments.html
>

Re: [GENERAL] Indicating DEFAULT values in INSERT statement

[Diego Augusto Molina]

This is a bit hacky, but it may work (I haven't tried it yet). If it
works let us know and if it doesn't then maybe we can debug it and get
something useful, or move on to another solution.


 INSERT INTO public.test
(
userid, object_id, user_notes, object_status, created_ts
)
VALUES
(
p_userid, p_obj_id, p_user_notes, p_obj_status,
Case When p_created_ts Is Not Null Then p_created_ts Else
( -- You may want to be sure the field has a default value.
SELECT d.adsrc -- or should it be d.adbin?
FROM
pg_catalog.pg_attribute a INNER JOIN
pg_catalog.pg_attrdef d ON (a.attnum = d.adnum)
WHERE
a.attname = 'created_ts' AND
a.attrelid = 'public.test'::REGCLASS AND
d.adrelid = 'public.test'::REGCLASS
) End
);


Well (thinking it thoroughly) it won't work at all as is. It will just
put the source code of the default expression but you would need to
*interpret* it first.
Looked for a way to do this (without `EXECUTE') but couldn't find it. Sorry.

-- 
Diego Augusto Molina
diegoaugustomol...@gmail.com

ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
desestimados.
EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
LINK: http://www.gnu.org/philosophy/no-word-attachments.html

-- 
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] Indicating DEFAULT values in INSERT statement

[Postgres User]

Thanks for the previous posts. I am in fact running 9.0 'nix and am unable
to find a way to embed DEFAULT within an expression.
I can always rewrite the function to call EXECUTE but that's not a very good
solution in this case because many of our db functions are code generated.

On Tue, Aug 9, 2011 at 4:47 PM, Diego Augusto Molina <
diegoaugustomol...@gmail.com> wrote:

>  Hi, when posting SQL it may be a good practice to post your PG
> version so that answers may be more accurate and better fit your
> needs. In this case, I don't think you'll be able to do what you are
> trying to, because as of my understanding the "DEFAULT" is not part of
> an expression but a keyword itself which is to be written *instead* of
> an expression. I get your point and seems pretty fair to me, but if
> I'm right you don't have access to the default value of the column
> from within an expression.
>  Assuming you're using the latest stable version of PostgreSQL (9.0
> as of today), you can check the following page for an online reading
> reference of the INSERT statement:
> http://www.postgresql.org/docs/9.0/interactive/sql-insert.html
>  If you look carefully, you'll see that for the value of a column you
> may write: the "DEFAULT" keyword [exclusive or] an expression (which
> includes specific values).
>  The  [exclusive or] is deduced because of the pipe (`|') between the
> two choices which are embraced by curly braces.
>  So, if you were able to use the "DEFAULT" keyword inside an
> expression, instead of having the "{ expression | DEFAULT }" syntax
> you would have the "expression" syntax, which would include the case
> of having just "DEFAULT" as an expression. It's just a matter of
> understanding the syntax.
>
>  Cheers!
>
> --
> Diego Augusto Molina
> diegoaugustomol...@gmail.com
>
> ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
> desestimados.
> EN: Please, avoid attaching Microsoft Office documents. They shall be
> discarded.
> LINK: http://www.gnu.org/philosophy/no-word-attachments.html
>

Re: [GENERAL] Indicating DEFAULT values in INSERT statement

[Diego Augusto Molina]

  Hi, when posting SQL it may be a good practice to post your PG
version so that answers may be more accurate and better fit your
needs. In this case, I don't think you'll be able to do what you are
trying to, because as of my understanding the "DEFAULT" is not part of
an expression but a keyword itself which is to be written *instead* of
an expression. I get your point and seems pretty fair to me, but if
I'm right you don't have access to the default value of the column
from within an expression.
  Assuming you're using the latest stable version of PostgreSQL (9.0
as of today), you can check the following page for an online reading
reference of the INSERT statement:
http://www.postgresql.org/docs/9.0/interactive/sql-insert.html
  If you look carefully, you'll see that for the value of a column you
may write: the "DEFAULT" keyword [exclusive or] an expression (which
includes specific values).
  The  [exclusive or] is deduced because of the pipe (`|') between the
two choices which are embraced by curly braces.
  So, if you were able to use the "DEFAULT" keyword inside an
expression, instead of having the "{ expression | DEFAULT }" syntax
you would have the "expression" syntax, which would include the case
of having just "DEFAULT" as an expression. It's just a matter of
understanding the syntax.

  Cheers!

-- 
Diego Augusto Molina
diegoaugustomol...@gmail.com

ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
desestimados.
EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
LINK: http://www.gnu.org/philosophy/no-word-attachments.html

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

[GENERAL] Postgres on SSD

[Ondrej Ivanič]

Hi,

I'm about to evaluate this SSD card: FusionIO ioDrive Duo [1]. The
main reason for this experiment is to see if SSD can significantly
improve query performance. So, I have the following questions:

- Could you please share your experience with SSD? Any issues?
- What needs to be changed at Postgres/Operating system level? The
obvious one is to change random_page_cost (now: 2) and seq_page_cost
(now: 4). What else should I look at?

Background:
Database schema is pretty simple:
Database size is around ~1.4TB. Main tables occupied around 1/3
(450GB, ~220mil rows) and aggregated data occupied 2/3 (900GB). All
indexes are on separate table space (~550GB)
1) main table(s)
This big fact table has ~200 columns and average row size is 1.5kb.
This table is never updated and new data is inserted constantly using
copy in chunks about 10k rows. Table is quite sparse so it is broken
into 5 tables which are joined as necessary (regular query reads 10 -
40% of the row size).

Tables are partitioned by month but I'm thinking to use week or two
week partitions.

Primary key is composite key (datetime, organisation, transaction_id).
The transaction_id column is unique but "datetime" column is used for
partitioning and all queries contains organisation=""
(multi-tetant database). In addition, there are ~15 single column
indexes. Old data is deleted after 6 months (drop partition)

The usual  query looks like this:
- select ... from T where organisation = ... and datetime between ...
and ... where ... order by  limit 1000
User can choose any column for sorting but we created indexes for the
most popular/reasonable ones (those ~15 single column indexes).

In the reality, query is more complex because of few Postgres issues:
- partitions/limit/order issue described on Stackoverflow and fixed in
9.1 or 9.2 [2], [3].
- partitions/join issues ie left join "on" clause must contain
datetime condition in order to avoid fulltable scan on joined table

Query response time for indexed columns is between 5 to 30 sec
(sometimes 200sec). The target is to have all queries under 5 sec. If
query has order by on non-indexed column then response time is in
hundreds seconds but desired response time should be 10sec (test query
is over 1 month range and organisation has between 0.5 and 2 mil row
per month; single partition has > 30 mil rows)

2) materialised aggregate tables
About 45 tables like this: agg_attribute1_attribute2(date,
organisation, attribute1, attribute2, count) (= select datetime::date,
organisation, attribute1, attribute2, count(*) from T where
organisation = ... and datetime between ... and ... group by 1,2,3,4)
Tables are updated by cron job every 15 minutes. Thanks for 8.3 HOT
updates - almost no bloat! Monthly partitions are used (date column).
Query response time is between 5 to 30 sec (sometimes 200sec) and the
target is to have all queries under 5 sec

Usual query is:
select attribute1, count(*) from agg_attribute1_... where organisation
= ... and datetime between ... and ... group by 1 limit 10
or
select attribute1, attribute2 count(*) from agg_attribute1_attribute2
where organisation = ... and datetime between ... and ... group by 1
limit 10

Top N queries perform even worse -- the query response time is in
minutes and the target is around 15 sec

Current hardware setup:
XEN host with 16 CPU (Intel(R) Xeon(R) CPU L5520  @ 2.27GHz). CentOS 5.6
80GB RAM
Storage: some Hitachi Fibre channel SAN with two LUNs:
1st LUN has *everything* under $PG_DATA (used 850 GB)
2nd LUN has *all* indexes (index table space) (used 550GB)

Postgres settings:
 name |
 current_setting

--+--

 version  | PostgreSQL 8.4.5 on
x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red
Hat 4.
1.2-48), 64-bit
 archive_command  | walarchive.sh %p %f
 archive_mode | on
 autovacuum   | on
 autovacuum_max_workers   | 6
 autovacuum_naptime   | 5min
 autovacuum_vacuum_cost_delay | -1
 checkpoint_completion_target | 0.9
 checkpoint_segments  | 48
 constraint_exclusion | on
 default_statistics_target| 100
 effective_cache_size | 20GB
 fsync| on
 lc_collate   | en_US.UTF-8
 lc_ctype | en_US.UTF-8
 log_autovacuum_min_duration  | 0
 log_destination  | csvlog
 log_min_duration_statement   | 10s
 log_rotation_age | 1d
 log_rotation_size| 0
 log_truncate_on_rotation | on
 logging_collector| on
 maintenance_work_mem | 256MB
 max_connections  | 100
 max_stack_depth  | 2MB
 random_page_cost | 2
 server_encoding  | UTF8
 shared_buffers   | 9GB
 TimeZone | UTC
 vacuum_cost_delay   

[GENERAL] Indicating DEFAULT values in INSERT statement

[Postgres User]

Hi,

According to the docs, the DEFAULT keyword lets you explicitly insert a
field's default value in an INSERT statement.

>From a db function, I'd like to force the use of default when an input
parameter is null.  This syntax looks correct but fails to compile.

Any suggestions?

INSERT INTO public.test
(
userid, object_id, user_notes, object_status, created_ts
)
VALUES
(
p_userid, p_obj_id, p_user_notes, p_obj_status,
Case When p_created_ts Is Not Null Then p_created_ts Else DEFAULT
End
);

I tried replacing the Case statement as follows, but it fails also:
 Coalesce(p_created_ts, DEFAULT)

Thanks

Re: [GENERAL] Problem with planner

[Tom Lane]

hubert depesz lubaczewski  writes:
> I solved the problem by adding "enable_bitmapscan = false" (and keeping
> the query in original format, with subselect) which caused the plan to
> be ok.

I doubt that solution is any more robust than what you had before ---
in particular, it's likely to fall back to seqscans.

> but I'm much more interested to understand why pg chooses *not* to use
> index which is tailored specifically for the query - it has exactly
> matching where clause, and it indexes the column that we use for
> comparison.

Because the planner thinks it will have to pull a huge number of rows
from the index.  Whether the index is "tailored" for the query
is irrelevant if it looks more expensive to use than a seqscan.

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] Problem with planner

[hubert depesz lubaczewski]

On Tue, Aug 09, 2011 at 04:08:39PM -0400, Tom Lane wrote:
> I suppose what's going on here is that the "state" and "ending_tsz"
> columns are highly correlated, such that there are lots of 'active'
> items but hardly any of them ended more than a day ago?  If so,

yes, that's correct.

> you're going to have to rethink the representation somehow to get
> good results, because there's no way the planner will see this until
> we have cross-column stats in some form.
> 
> The least invasive fix that I can think of offhand is to set up an
> index (non-partial) on the expression
> 
>   case when state = 'active' then ending_tsz else null end
> 
> and phrase the query as
> 
>   WHERE (case when state = 'active' then ending_tsz else null end) <= 
> (now() - '1 day'::interval)
> 
> This should result in condensing the stats about active items'
> ending_tsz into a format the planner can deal with, assuming
> you're running a PG version that will keep and use stats on
> expression indexes.

it's 8.3.11.
I solved the problem by adding "enable_bitmapscan = false" (and keeping
the query in original format, with subselect) which caused the plan to
be ok.

but I'm much more interested to understand why pg chooses *not* to use
index which is tailored specifically for the query - it has exactly
matching where clause, and it indexes the column that we use for
comparison.

the thing is - i solved the problem for now. I might add new index the
way you suggest, and it might help. but it's is very unnerving that
postgresql will just choose to ignore specially made index, perfectly
matching the criteria in query.

since I can't test it - is there any chance (Cédric suggested something
like this) that some newer version has more logic to try harder to use
best index?

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.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] JDBC driver throwing error while connecting to the slave server for partitioned table

[Merlin Moncure]

On Mon, Aug 8, 2011 at 8:14 AM, sameera vhora  wrote:
>  We are facing below issue after creating slony replication of
> partitioning table in edb server 8.3.
>
>  This issue persist only on slave one not the primary one.
>
>  Below logs we found in tomcats.
>
>
>  Error in postgresql driver initialization:
>  com.edb.util.PSQLException: The connection attempt failed.
>  at 
> com.edb.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:176)
>  at com.edb.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
>  at 
> com.edb.jdbc2.AbstractJdbc2Connection.(AbstractJdbc2Connection.java:153)
>  at 
> com.edb.jdbc3.AbstractJdbc3Connection.(AbstractJdbc3Connection.java:30)
>  at com.edb.jdbc3.Jdbc3Connection.(Jdbc3Connection.java:24)
>  at com.edb.Driver.makeConnection(Driver.java:385)
>  at com.edb.Driver.connect(Driver.java:260)
>  at java.sql.DriverManager.getConnection(DriverManager.java:582)

try checking the database logs?

merlin

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

[Tom Lane]

hubert depesz lubaczewski  writes:
> version with disabled bitmapscans:
> $ explain analyze  select count(*) from objects where state='active' and 
> ending_tsz <= now() - '1 day'::interval;
> QUERY PLAN
> --
>  Aggregate  (cost=1081.72..1081.73 rows=1 width=0) (actual 
> time=1416200.548..1416200.548 rows=1 loops=1)
>->  Seq Scan on objects  (cost=0.00..10795673.36 rows=7952943 width=0) 
> (actual time=1210074.356..1416200.498 rows=13 loops=1)
>  Filter: ((state = 'active'::text) AND (ending_tsz <= (now() - '1 
> day'::interval)))
>  Total runtime: 1416200.678 ms
> (4 rows)

Hmm, so it's not using the index at all here.  The problem clearly is
that the rowcount estimate is still completely bogus :-(, even though
it's presumably getting a reasonable estimate now for the ending_tsz
test in isolation.

I suppose what's going on here is that the "state" and "ending_tsz"
columns are highly correlated, such that there are lots of 'active'
items but hardly any of them ended more than a day ago?  If so,
you're going to have to rethink the representation somehow to get
good results, because there's no way the planner will see this until
we have cross-column stats in some form.

The least invasive fix that I can think of offhand is to set up an
index (non-partial) on the expression

case when state = 'active' then ending_tsz else null end

and phrase the query as

WHERE (case when state = 'active' then ending_tsz else null end) <= 
(now() - '1 day'::interval)

This should result in condensing the stats about active items'
ending_tsz into a format the planner can deal with, assuming
you're running a PG version that will keep and use stats on
expression indexes.

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] Problem with planner

[hubert depesz lubaczewski]

On Tue, Aug 09, 2011 at 12:11:48PM -0400, Tom Lane wrote:
> There's absolutely no hope of getting the planner to make intelligent
> plan choices when its estimates are that far off (3344138 rows estimated
> versus none actual).  It's just luck if you get the "right" plan despite
> that.
> Now part of the reason why the estimate is so bad is the sub-select,
> which completely defeats selectivity estimation for the ending_tsz
> condition.  Could we see EXPLAIN ANALYZE for the query without a
> sub-select, both with and without enable_bitmapscan?

version with disabled bitmapscans:
$ explain analyze  select count(*) from objects where state='active' and 
ending_tsz <= now() - '1 day'::interval;
QUERY PLAN
--
 Aggregate  (cost=1081.72..1081.73 rows=1 width=0) (actual 
time=1416200.548..1416200.548 rows=1 loops=1)
   ->  Seq Scan on objects  (cost=0.00..10795673.36 rows=7952943 width=0) 
(actual time=1210074.356..1416200.498 rows=13 loops=1)
 Filter: ((state = 'active'::text) AND (ending_tsz <= (now() - '1 
day'::interval)))
 Total runtime: 1416200.678 ms
(4 rows)


version with enabled bitmap scans is still working on (3 hours now), will post 
explain analyze when it finishes.

explain (with enabled bitmap scans) looks like this:

$ explain select count(*) from objects where state='active' and ending_tsz <= 
now() - '1 day'::interval;
QUERY PLAN  
  
--
 Aggregate  (cost=9117480.50..9117480.51 rows=1 width=0)
   ->  Bitmap Heap Scan on objects  (cost=326375.21..9097597.40 rows=7953240 
width=0)
 Recheck Cond: (state = 'active'::text)
 Filter: (ending_tsz <= (now() - '1 day'::interval))
 ->  Bitmap Index Scan on objects_stat_user_id_creation_tsz  
(cost=0.00..324386.90 rows=9754574 width=0)
   Index Cond: (state = 'active'::text)
(6 rows)


value of state and ending_tsz are pretty correlated - it's very
unlikely to have rows matching both criteria (state = 'active' and
ending_tsz <= now() - '1 day').

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.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] postgresql server crash on windows 7 when using plpython

[Adrian Klaver]

On Tue, Aug 9, 2011 at 7:54 AM, c k  wrote:

> Dear Friends,
> For client requirements, I am testing postgresql 9.0.4 on windows 7 with
> plpython. First I installed python 2.7 and then added python installation
> path to windows PATH variable. Then installed postgresql from one-click
> installer. Server started and every thing was ok. Then I go to create
> plpythonu in a database. But it failed. After searching, I found that it
> needs python 2.6. So I again installed python 2.6 and then dropped plpython
> from database and created again. It was successful. Normal python functions
> returning text etc. are working but when conatining
>
> import sys
> from uuid import getnode as get_mac
> mac = get_mac()
> return mac
>
> fails. What will be the reason?
>

What would be the error message(s)?

The actual function code would also be helpful, to put things into context.


>
> Advance thanks for your help.
>
> Chaitanya Kulkarni
>



-- 
Adrian Klaver
adrian.kla...@gmail.com

Re: [GENERAL] Problem with planner

[hubert depesz lubaczewski]

On Tue, Aug 09, 2011 at 12:11:48PM -0400, Tom Lane wrote:
> Now part of the reason why the estimate is so bad is the sub-select,
> which completely defeats selectivity estimation for the ending_tsz
> condition.  Could we see EXPLAIN ANALYZE for the query without a
> sub-select, both with and without enable_bitmapscan?

working on it.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.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] Update with ORDER BY and LIMIT

[Tom Lane]

Paul M Foster  writes:
> It'd be great if select pg_version() worked, but PG doesn't recognize
> the function, when issued from the PG prompt.

It's "select version()".

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] Problem with planner

[Tom Lane]

hubert depesz lubaczewski  writes:
> as for good plan. sure. If i'll disable bitmap scans, I get:

> $ explain analyze select count(*) from objects where state='active' and 
> ending_tsz <= (select now() - '1 day'::interval );
>QUERY 
> PLAN
> -
>  Aggregate  (cost=7651119.35..7651119.36 rows=1 width=0) (actual 
> time=63.150..63.151 rows=1 loops=1)
>InitPlan
>  ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.010..0.010 
> rows=1 loops=1)
>->  Index Scan using objects_ending_tsz_active on objects  
> (cost=0.00..7642758.99 rows=3344138 width=0) (actual time=63.131..63.131 
> rows=0 loops=1)
>  Index Cond: (ending_tsz <= $0)
>  Total runtime: 63.279 ms
> (6 rows)

There's absolutely no hope of getting the planner to make intelligent
plan choices when its estimates are that far off (3344138 rows estimated
versus none actual).  It's just luck if you get the "right" plan despite
that.

Now part of the reason why the estimate is so bad is the sub-select,
which completely defeats selectivity estimation for the ending_tsz
condition.  Could we see EXPLAIN ANALYZE for the query without a
sub-select, both with and without enable_bitmapscan?

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] Update with ORDER BY and LIMIT

[Paul M Foster]

On Mon, Aug 08, 2011 at 10:20:18PM -0400, David Johnston wrote:

> 
> > 
> > 8.X in this context means "8 point something, but I can't recall
> > which something". Could be 8.2, 8.3 or 8.4. Thus, in effect, asking
> > those replying to restrict themselves to 8 series features, as
> > opposed to 9 series features.
> > 
> 
> There are a lot of features added between 8.0 and 8.4;  WITH and
> WINDOW being two major ones, that just saying 8 is not helpful.  In
> the future please take the time to issue a SELECT pg_version() before
> asking others to take time to help.  It is for your own benefit and
> makes it easier for those wanting to help to give useful advice.

It'd be great if select pg_version() worked, but PG doesn't recognize
the function, when issued from the PG prompt. I had to go all the way
back to aptitude to find out it's verson 8.3.1-1 running under Debian
unstable.

Paul

-- 
Paul M. Foster
http://noferblatz.com
http://quillandmouse.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] Problem with planner

[hubert depesz lubaczewski]

On Tue, Aug 09, 2011 at 05:11:09PM +0200, Cédric Villemain wrote:
> The plan turn bad without any new ANALYZE, right  ?

Right.

> does the table increase more quickly now than before ? is it now way
> larger than before ?
> Also, do you have an explain with the 'good' plan ?

changes in the objects table are more or less the same (in volume) day
to day.

as for good plan. sure. If i'll disable bitmap scans, I get:

$ explain analyze select count(*) from objects where state='active' and 
ending_tsz <= (select now() - '1 day'::interval );
   QUERY 
PLAN
-
 Aggregate  (cost=7651119.35..7651119.36 rows=1 width=0) (actual 
time=63.150..63.151 rows=1 loops=1)
   InitPlan
 ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.010..0.010 
rows=1 loops=1)
   ->  Index Scan using objects_ending_tsz_active on objects  
(cost=0.00..7642758.99 rows=3344138 width=0) (actual time=63.131..63.131 rows=0 
loops=1)
 Index Cond: (ending_tsz <= $0)
 Total runtime: 63.279 ms
(6 rows)

for comparison, normal plan, with enable_bitmapscan = true:

$ explain  select count(*) from objects where state='active' and ending_tsz <= 
(select now() - '1 day'::interval );
   QUERY PLAN   
 
-
 Aggregate  (cost=6726333.89..6726333.90 rows=1 width=0)
   InitPlan
 ->  Result  (cost=0.00..0.01 rows=1 width=0)
   ->  Bitmap Heap Scan on objects  (cost=1295618.40..6717973.52 rows=3344138 
width=0)
 Recheck Cond: ((state = 'active'::text) AND (ending_tsz <= $0))
 ->  BitmapAnd  (cost=1295618.40..1295618.40 rows=3344138 width=0)
   ->  Bitmap Index Scan on objects_stat_user_id_creation_tsz  
(cost=0.00..333925.70 rows=10032414 width=0)
 Index Cond: (state = 'active'::text)
   ->  Bitmap Index Scan on objects_ending_tsz_idx  
(cost=0.00..960020.38 rows=25015994 width=0)
 Index Cond: (ending_tsz <= $0)
(10 rows)

Now. Interesting is, that if i'll change the query like Tom suggested, things 
go really bad:

$ set enable_bitmapscan = true;
SET
$ explain  select count(*) from objects where state='active' and ending_tsz <= 
now() - '1 day'::interval;
QUERY PLAN  
   
---
 Aggregate  (cost=9192258.28..9192258.29 rows=1 width=0)
   ->  Bitmap Heap Scan on objects  (cost=335966.69..9171848.45 rows=8163932 
width=0)
 Recheck Cond: (state = 'active'::text)
 Filter: (ending_tsz <= (now() - '1 day'::interval))
 ->  Bitmap Index Scan on objects_stat_user_id_creation_tsz  
(cost=0.00..333925.70 rows=10032414 width=0)
   Index Cond: (state = 'active'::text)
(6 rows)

$ set enable_bitmapscan = false;
SET
$ explain  select count(*) from objects where state='active' and ending_tsz <= 
now() - '1 day'::interval;
 QUERY PLAN 


 Aggregate  (cost=10814335.47..10814335.48 rows=1 width=0)
   ->  Seq Scan on objects  (cost=0.00..10793925.64 rows=8163932 width=0)
 Filter: ((state = 'active'::text) AND (ending_tsz <= (now() - '1 
day'::interval)))
(3 rows)

Current stats of the table:

$ select * from pg_stat_user_tables where relname = 'objects';
-[ RECORD 1 ]+--
relid| 71635994
schemaname   | public
relname  | objects
seq_scan | 181
seq_tup_read | 3164627085
idx_scan | 164923232565
idx_tup_fetch| 1359016133552
n_tup_ins| 31372199
n_tup_upd| 698411462
n_tup_del| 1
n_tup_hot_upd| 20426973
n_live_tup   | 75016862
n_dead_tup   | 494489
last_vacuum  | 2011-03-31 06:15:39.866869+00
last_autovacuum  | 2011-08-09 05:51:35.050683+00
last_analyze | 2011-08-09 03:30:14.986266+00
last_autoanalyze | 2010-09-27 05:10:10.793584+00

Best regards,

depesz


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

[Cédric Villemain]

2011/8/8 hubert depesz lubaczewski :
> Hi,
> we have 8.3.11 installation on client site, with table, which looks like
> this:
>
> $ \d objects
>                                              Table "public.objects"
>         Column          |           Type           |                          
>  Modifiers
> -+--+---
> ...
>  state                   | text                     |
> ...
>  ending_tsz              | timestamp with time zone | default (now() + '4 
> mons'::interval)
> ...
> Indexes:
>    "objects_stat_user_id_creation_tsz" btree (state, user_id, creation_tsz)
>    "objects_ending_tsz_active" btree (ending_tsz) WHERE state = 'active'::text
>    "objects_ending_tsz_idx" btree (ending_tsz)
> ...
>
>
> and we have a query:
> select count(*) from objects where state='active'::text and ending_tsz <= ( 
> select now() - '1 day'::interval );
>
> Normally this query has been getting plan, using
> objects_ending_tsz_active, which is sane and fast.
>
> But today, without any sensible reason, it switched to:
>
>                                                       QUERY PLAN
> -
>  Aggregate  (cost=6719810.62..6719810.63 rows=1 width=0)
>   InitPlan
>     ->  Result  (cost=0.00..0.01 rows=1 width=0)
>   ->  Bitmap Heap Scan on objects  (cost=1289719.58..6711422.56 rows=3355219 
> width=0)
>         Recheck Cond: ((state = 'active'::text) AND (ending_tsz <= $0))
>         ->  BitmapAnd  (cost=1289719.58..1289719.58 rows=3355219 width=0)
>               ->  Bitmap Index Scan on objects_stat_user_id_creation_tsz  
> (cost=0.00..334318.95 rows=10065657 width=0)
>                     Index Cond: (state = 'active'::text)
>               ->  Bitmap Index Scan on objects_ending_tsz_idx  
> (cost=0.00..953722.77 rows=24986738 width=0)
>                     Index Cond: (ending_tsz <= $0)
> (10 rows)
>
> running analyze objects 2 times in a row fixed the issue, but hour later
> - the problem came back.
>
> what can be wrong?

The plan turn bad without any new ANALYZE, right  ?
does the table increase more quickly now than before ? is it now way
larger than before ?
Also, do you have an explain with the 'good' plan ?
-- 
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

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

[GENERAL] postgresql server crash on windows 7 when using plpython

[c k]

Dear Friends,
For client requirements, I am testing postgresql 9.0.4 on windows 7 with
plpython. First I installed python 2.7 and then added python installation
path to windows PATH variable. Then installed postgresql from one-click
installer. Server started and every thing was ok. Then I go to create
plpythonu in a database. But it failed. After searching, I found that it
needs python 2.6. So I again installed python 2.6 and then dropped plpython
from database and created again. It was successful. Normal python functions
returning text etc. are working but when conatining

import sys
from uuid import getnode as get_mac
mac = get_mac()
return mac

fails. What will be the reason?

Advance thanks for your help.

Chaitanya Kulkarni

Re: [GENERAL] table / query as a prameter for PL/pgSQL function

[Merlin Moncure]

2011/8/8 Ondrej Ivanič :
> Hi,
>
> 2011/8/9 Merlin Moncure :
>> You have a few of different methods for passing sets between functions.
>
> I do not want to pass data between functions. The ideal solution
> should look like this:
> select * from my_map_func()

well, the method still applies: you'd just do:
select * from my_map_func(array())

...but, it sounds like that method is not appropriate -- see below.

>> 1) refcursor as David noted.  reasonably fast. however, I find the
>> 'FETCH' mechanic a little inflexible.
>
> I've came across this but manual example wasn't (isn't) clear to me:
> CREATE TABLE test (col text);
> INSERT INTO test VALUES ('123');
>
> CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
> BEGIN
>    OPEN $1 FOR SELECT col FROM test;
>    RETURN $1;
> END;
> ' LANGUAGE plpgsql;
>
> BEGIN;
> SELECT reffunc('funccursor');
> FETCH ALL IN funccursor;
> COMMIT;
>
> What is the "funccursor"?

funccursor is the name -- just a string.  refcursors can be named with
a variable string and later fetched as an identifier -- they are kinda
unique in that respect.

>> 2) stage data to TABLE/TEMP TABLE; extremely flexible, but can be a
>> headache because a non temp table can get thrashed pretty hard a and a
>> 'TEMP' can cause severe function plan invalidation issues if you're
>> not careful
>
> I'm not familiar with this issue (function plan invalidation issues).
> Could you please provide more details/links about it?

well, in your particular case it's probably not so much of an issue.
plpgsql, when a function is executed for the first time in a session,
'compiles' the source code into a plan that is kept around until it
invalidates.  one of the things that causes a plan to invalidate is a
table getting dropped that is inside the plan -- temp tables are
notorious for doing that (in older postgres we'd get annoying OID
errors).  if your application is even partially cpu bound, and you
have a lot of plpgsql flying around,  that can add up in a surprising
hurry.  temp tables also write to the system catalogs, so if your
function calls are numerous, short, and sweet, array passing is the
way to go because it's a completely in-memory structure that can be
used like a set (via unnest) without those issues.  for 'big' data
though, it's not good.

>> 3) arrays of composites -- the most flexible and very fast for *small*
>> amounts of records (say less than 10,000):
>
> My data set is huge: between 1 and 5 mil rows and avg row size is 100
> - 400 bytes

your best bet is probably a cursor IMO.

merlin

-- 
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] Pgadmin goes missing in Ubuntu

[Adrian Klaver]

On Tuesday, August 09, 2011 2:18:47 am Adarsh Sharma wrote:
> Dear all,
> 
> I installed postgresql from  a postgresplus-8.4.1-2-linux-x64 binary and
> it is working properly in the system.Yesterday i do some work in it.
> 
> But today when I restart the system, pgadmin goes missing from
> Accessories > .
> I don't know the reason of this .Please let me know how to solve this.
> Any other way to open pgadmin.

Open a terminal window and type pgadmin3.

> 
> 
> Thanks

-- 
Adrian Klaver
adrian.kla...@gmail.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] Pgadmin goes missing in Ubuntu

[Adarsh Sharma]

But Can U Please explain in Steps how to solve it.
I mean how to apply patch or anything.


Thanks

Chetan Suttraway wrote:

assuming this is ubuntu, checkout this link:

https://bugs.launchpad.net/unity-2d/+bug/800710
https://bugs.launchpad.net/ubuntu/+source/pgadmin3/+bug/755770


On Tue, Aug 9, 2011 at 2:48 PM, Adarsh Sharma 
mailto:adarsh.sha...@orkash.com>> wrote:


Dear all,

I installed postgresql from  a postgresplus-8.4.1-2-linux-x64
binary and it is working properly in the system.Yesterday i do
some work in it.

But today when I restart the system, pgadmin goes missing from
Accessories > .
I don't know the reason of this .Please let me know how to solve this.
Any other way to open pgadmin.


Thanks

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org

)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




--
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Website: www.enterprisedb.com 
EnterpriseDB Blog : http://blogs.enterprisedb.com 


Follow us on Twitter : http://www.twitter.com/enterprisedb

Re: [GENERAL] postgres table have a large number of relpages and occupied a big memory size

[Vivekkumar Pandey]

On Tue, Aug 9, 2011 at 2:59 PM, Tomas Vondra  wrote:
> On 9 Srpen 2011, 9:18, Vivekkumar Pandey wrote:
>> I have seen that autovacuum takes long time to process .
>> please give me the reason ..
>
> What do you mean by "takes long time to process"? How do you measure it?
>
> Autovacuum is meant as a background process, and it should run on
> background and not influence the performance significantly. In some cases
> the default settings is not aggressive enough, so the database grows.
>
> In that case you probably need to
> - lower autovacuum_vacuum_threshold
> - lower autovacuum_vacuum_scale_factor
> - lower autovacuum_vacuum_cost_delay
> - increase autovacuum_vacuum_cost_limit
>
> But I'm not sure this is the case. And this does not release the space,
> it's still occupied by the database (just ready to be reused by new data).
>
> If you really want to compact the database, you may run VACUUM FULL. But
> that may be very intensive process, locks tables etc.
>
> Tomas
>
>

I have seen postgres.log file and search for a string "autovacuum" .

I found that

time  |   DB
--
5:04   |   template0
5:09   |   DB1
5:25   |   DB2
5:30   |   template1
5:35   |   template0
5:40   |   DB1
5:54   |   DB2
5:59   |   template1

this time is given when autovacuum process available for Database.
Here we see that DB1 consume around 15 min of autovacuum process
while others consume 5 min(equal to naptime).
this shows autovacuum process takes too much time for DB1.

Also, size of DB1 is 15 GB .

Is there any relation of b/w big size of DB and long autovacuum process time

-- 
Thanks

VIVEK KUMAR PANDEY

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

[hubert depesz lubaczewski]

On Mon, Aug 08, 2011 at 10:01:58PM +0200, pasman pasmański wrote:
> Try to change index: objects_endings_tsz_active(state,endings_tsz)
> where state='active'.

adding new indexes on this machine is quite complicated, as it's heavily
used production system.
And the current index should be chosen by planner anyway - it is after
analyze, but then it breaks again.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.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] postgres table have a large number of relpages and occupied a big memory size

[Tomas Vondra]

On 9 Srpen 2011, 9:18, Vivekkumar Pandey wrote:
> I have seen that autovacuum takes long time to process .
> please give me the reason ..

What do you mean by "takes long time to process"? How do you measure it?

Autovacuum is meant as a background process, and it should run on
background and not influence the performance significantly. In some cases
the default settings is not aggressive enough, so the database grows.

In that case you probably need to
- lower autovacuum_vacuum_threshold
- lower autovacuum_vacuum_scale_factor
- lower autovacuum_vacuum_cost_delay
- increase autovacuum_vacuum_cost_limit

But I'm not sure this is the case. And this does not release the space,
it's still occupied by the database (just ready to be reused by new data).

If you really want to compact the database, you may run VACUUM FULL. But
that may be very intensive process, locks tables etc.

Tomas


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

[GENERAL] Pgadmin goes missing in Ubuntu

[Adarsh Sharma]

Dear all,

I installed postgresql from  a postgresplus-8.4.1-2-linux-x64 binary and 
it is working properly in the system.Yesterday i do some work in it.


But today when I restart the system, pgadmin goes missing from 
Accessories > .

I don't know the reason of this .Please let me know how to solve this.
Any other way to open pgadmin.


Thanks

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

Re: [GENERAL] postgres table have a large number of relpages and occupied a big memory size

[Vivekkumar Pandey]

I have seen that autovacuum takes long time to process .
please give me the reason ..

On Mon, Aug 8, 2011 at 2:10 PM, Tomas Vondra  wrote:
> On 8 Srpen 2011, 8:02, Vivekkumar Pandey wrote:
>> Hi ,
>>
>>       I have version of PostgreSQL as given below:---
>>
>>                                        version
>> -
>>  PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
>> 3.3.3 (SuSE Linux)
>>
>> And it does't have any n_dead_tup columns in pg_stat_all_tables like this:
>
> That's because this column was added in 8.3.
>
> BTW you're using a way too old version. It's not just that 8.1 is
> unsupported, but the last minor subversion in this branch is 8.1.23. There
> were numerous bugfixes since 8.1.2, so you should upgrade at least to this
> version (and plan to upgrade to something more fresh).
>
>> In what situations table creates a new row instead of using deleted
>> marked tuples that's why size of table increases abnormally.
>
> Whenever a row is modified - that means insert, update or delete. That's
> how PostgreSQL MVCC works. This wasted space needs to be reclaimed, which
> is exactly what (auto)vacuum does.
>
> PS: With HOT this is not exactly true, but HOT is not available in 8.1
> anyway. Another reason to upgrade to a more recent version.
>
> Tomas
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Thanks

VIVEK KUMAR PANDEY

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