[GENERAL] Idle in transcation problem?

i use the "ps -ef | grep postgres" to see all the connections. the
connection's status is "Idle in transcation".

and i use the " du -h /mydb" to check the disk size. The disk size of
mydb has increased from 400MB to 600MB.
Why? because of the "idle in transcation" ?

my pg version:8.1.3
OS: Linux Enterprise 4

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

Re: [SQL] Join question

I thought of that, but it does violate table constraints.

 

Edward W. Rouse

 

From: Oliveiros Cristina [mailto:oliveiros.cristina@marktest.pt]
Sent: Monday, August 18, 2008 2:00 PM
To: pgsql-sql@postgresql.org; erouse@comsquared.com
Subject: Re: [SQL] Join question

 

I don't understand your count(total) expression...

It doesnt work, because apparently you dont have any "total" column...

Apparently, you meant count(color)

 

The problem is that you are grouping by a.org,a.user and on  table "a" u actually dont have any "null" users...

 

Well, if it is to include "null" users, a quick and dirty solution I can think of would be to add a "dummy" null user to every diferent org on table a and then

substitute your LEFT OUTER JOIN condition by this one :

 

from a left  join b

 

on (a.org = b.org and (a.user = b.user OR (a.user is null and b.user is null )))

 

 

Now, I don' know if "null" users on table "a" will violate any constraints you may have (e.g. NOT NULL) ...

 

I know This is not a very elegant solution, but seems to give the results you need....

 

Best,

Oliveiros

----- Original Message -----

From: Daniel Hernandez

To: pgsql-sql@postgresql.org ; erouse@comsquared.com

Sent: Monday, August 18, 2008 5:30 PM

Subject: Re: [SQL] Join question

 

have you tried a right Join?


Daniel Hernndez.
San Diego, CA.
"The more you learn, the more you earn".
Fax: (808) 442-0427


-----Original Message-----
From: "Edward W. Rouse" [erouse@comsquared.com]
Date: 08/15/2008 09:48 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Join question

I have 2 tables, both have a user column. I am currently using a left join from table a to table b because I need to show all users from table a even those not having an entry in table b. The problem is I also have to include items from table b with that have a null user. There are some other criteria as well that are simple where clause filters. So as an example:

Table a:

Org|user

A    | emp1

B    | emp1

B    | emp2

B    | emp3

C    | emp2

Table b:

Org|user|color

A   |emp1|red

A   |emp1|blue

A   |null|pink

A   |null|orange

B   |emp1|red

B   |emp3|red

B   |null|silver

C   |emp2|avacado

If I:

select org, user, count(total)

from a left join b

on (a.org = b.org and a.user = b.user)

where a.org = 'A'

group by a.org, a.user

order by a.org, a.user

I get:

Org|user|count

A    |emp1|2

A    |emp2|0

A    |emp3|0

But what I need is:

A    |emp1|2

A    |emp2|0

A    |emp3|0

A    |null|2

Thanks,

Edward W. Rouse

Re: [SQL] Join question

I have tried left, right outer and inner.

 

Edward W. Rouse

 

From: Daniel Hernandez [mailto:breydan@excite.com]
Sent: Monday, August 18, 2008 12:30 PM
To: pgsql-sql@postgresql.org; erouse@comsquared.com
Subject: Re: [SQL] Join question

 

have you tried a right Join?


Daniel Hernndez.
San Diego, CA.
"The more you learn, the more you earn".
Fax: (808) 442-0427


-----Original Message-----
From: "Edward W. Rouse" [erouse@comsquared.com]
Date: 08/15/2008 09:48 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Join question

I have 2 tables, both have a user column. I am currently using a left join from table a to table b because I need to show all users from table a even those not having an entry in table b. The problem is I also have to include items from table b with that have a null user. There are some other criteria as well that are simple where clause filters. So as an example:

 

Table a:

Org|user

A    | emp1

B    | emp1

B    | emp2

B    | emp3

C    | emp2

 

Table b:

Org|user|color

A   |emp1|red

A   |emp1|blue

A   |null|pink

A   |null|orange

B   |emp1|red

B   |emp3|red

B   |null|silver

C   |emp2|avacado

 

If I:

 

select org, user, count(total)

from a left join b

on (a.org = b.org and a.user = b.user)

where a.org = 'A'

group by a.org, a.user

order by a.org, a.user

 

I get:

 

Org|user|count

A    |emp1|2

A    |emp2|0

A    |emp3|0

 

But what I need is:

 

A    |emp1|2

A    |emp2|0

A    |emp3|0

A    |null|2

 

Thanks,

Edward W. Rouse

Re: [PERFORM] Cross Join Problem

Thanx alot... its solved my problem

On Mon, Aug 18, 2008 at 8:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

[ please keep the list cc'd for the archives' sake ]

"Gauri Kanekar" <meetgaurikanekar@gmail.com> writes:
> On Mon, Aug 18, 2008 at 7:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

>> No PG release since 7.3 would have voluntarily planned that query that
>> way.  Maybe you were using join_collapse_limit = 1 to force the join
>> order?

> Yes, We have set join_collapse_limit set to 1.

Ah, so really your question is why join_collapse_limit isn't working as
you expect.  That code changed quite a bit in 8.2, and the way it works
now is that the critical decision occurs while deciding whether to fold
the cross-join (a sub-problem of size 2) into the top-level join
problem.  Which is a decision that's going to be driven by
from_collapse_limit not join_collapse_limit.

So one way you could make it work is to reduce from_collapse_limit to
less than 3, but I suspect you'd find that that has too many bad
consequences for other queries.  What's probably best is to write the
problem query like this:

       FROM table1 a cross join ( table2 b cross join table3 c )

which will cause join_collapse_limit to be the relevant number at both
steps.

                       regards, tom lane

--
Regards
Gauri

Re: [pgsql-es-ayuda] empezar serial desde 1000

--- On Tue, 8/19/08, Carlos Mendez <lucas1850@gmail.com> wrote:

> From: Carlos Mendez <lucas1850@gmail.com>
> Subject: [pgsql-es-ayuda] empezar serial desde 1000
> To: pgsql-es-ayuda@postgresql.org
> Date: Tuesday, August 19, 2008, 3:03 AM
> hola, que tal,
>
> quisiera saber como empezar un serial desde 1000 y no desde
> 1, en la
> definicion de la tabla,
>
> desde ya muchas gracias por su ayuda,
> saludos.

Espero que esto te ayude...

create sequence seq_2 start 1000

--
TIP 8: explain analyze es tu amigo

Re: [GENERAL] NOT DEFERRABLE as default, why and how to manage it.

Am Tuesday, 19. August 2008 schrieb Ivan Sergio Borgonovo:
> > Is there any reason they put it that way in the standard other than
> > the mantra "stricter is better"?
>
> After reflecting a bit I think it is a matter of "failing earlier".

Deferrable constraints are an optional feature of SQL, and the reason this
default is chosen is that systems with and without the feature behave the
same.

> But it doesn't make things more transparent.
> Since there is no simple standard way to see which constraints are
> deferrable and no simple way to alter them.

Query information_schema.table_constraints to find out about existing
constraints and their parameters.

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

Re: [PERFORM] Slow query with a lot of data

Am 19.08.2008 um 14:17 schrieb Matthew Wakeling:

> On Tue, 19 Aug 2008, Moritz Onken wrote:
>> tablename | attname | n_distinct | correlation
>> result | domain | 1642 | 1
>
> Well, the important thing is the correlation, which is 1, indicating
> that Postgres knows that the table is clustered. So I have no idea
> why it is sorting the entire table.
>
> What happens when you run EXPLAIN SELECT * FROM result ORDER BY
> domain?
>

"Index Scan using result_domain_idx on result (cost=0.00..748720.72
rows=20306816 width=49)"
... as it should be.

>>> Sounds like an awfully long time to me. Also, I think restricting
>>> it to 280 users is probably not making it any faster.
>>
>> If I hadn't restricted it to 280 users it would have run ~350days...
>
> What makes you say that? Perhaps you could post EXPLAINs of both of
> the queries.
>
> Matthew

That was just a guess. The query needs to retrieve the data for about
50,000 users. But it should be fast if I don't retrieve the data for
specific users but let in run through all rows.

explain insert into setup1 (select
a."user", b.category, sum(1.0/b.cat_count)::float
from result a, domain_categories b
where a."domain" = b."domain"
and b.depth < 4
and a.results > 100
and a."user" < 30000
group by a."user", b.category);

"GroupAggregate (cost=11745105.66..12277396.81 rows=28704 width=12)"
" -> Sort (cost=11745105.66..11878034.93 rows=53171707 width=12)"
" Sort Key: a."user", b.category"
" -> Merge Join (cost=149241.25..1287278.89 rows=53171707
width=12)"
" Merge Cond: (b.domain = a.domain)"
" -> Index Scan using domain_categories_domain on
domain_categories b (cost=0.00..421716.32 rows=5112568 width=12)"
" Filter: (depth < 4)"
" -> Materialize (cost=148954.16..149446.36 rows=39376
width=8)"
" -> Sort (cost=148954.16..149052.60 rows=39376
width=8)"
" Sort Key: a.domain"
" -> Bitmap Heap Scan on result a
(cost=1249.93..145409.79 rows=39376 width=8)"
" Recheck Cond: ("user" < 30000)"
" Filter: (results > 100)"
" -> Bitmap Index Scan on
result_user_idx (cost=0.00..1240.08 rows=66881 width=0)"
" Index Cond: ("user" < 30000)"

This query limits the number of users to 215 and this query took about
50 minutes.
I could create to temp tables which have only those records which I
need for this query. Would this be a good idea?

moritz

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

Re: [pgsql-es-ayuda] empezar serial desde 1000

El 18 de agosto de 2008 23:03, Carlos Mendez <lucas1850@gmail.com> escribió:

hola, que tal,

 

quisiera saber como empezar un serial desde 1000 y no desde 1, en la definicion de la tabla,

 

desde ya muchas gracias por su ayuda,

saludos.

CREATE SEQUENCE aparatos_apt_cod_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 40
  CACHE 1;
ALTER TABLE aparatos_apt_cod_seq OWNER TO postgres;

asi mi secuencia empieza en 40


--
Cesar Erices Vergara
Ingeniero en Gestión Informática
Analista de Sistema

Re: [PERFORM] Slow query with a lot of data

On Tue, 19 Aug 2008, Moritz Onken wrote:
> tablename | attname | n_distinct | correlation
> result | domain | 1642 | 1

Well, the important thing is the correlation, which is 1, indicating that
Postgres knows that the table is clustered. So I have no idea why it is
sorting the entire table.

What happens when you run EXPLAIN SELECT * FROM result ORDER BY domain?

>> Sounds like an awfully long time to me. Also, I think restricting it to 280
>> users is probably not making it any faster.
>
> If I hadn't restricted it to 280 users it would have run ~350days...

What makes you say that? Perhaps you could post EXPLAINs of both of the
queries.

Matthew

--
What goes up must come down. Ask any system administrator.

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

Re: [HACKERS] Postgres-R

Hi,

K, Niranjan (NSN - IN/Bangalore) wrote:
> Thanks for the information.
> For Step5 (starting ensemble daemon).-
> I set the multicast address to both nodes (Node 1& Node 2 eth0: 224.0.0.9/4) before starting the ensemble. And started the server application mtalk in node 1 & node 2 and then client application in node 1 & node 2. But the count of members ('nmembers') show as 1. This is the output of the client program 'c_mtalk'. Seeing this, I'am assuming that the applications are not merged.

This sounds like IP multicast does not work properly for your network
(is IP multicast available and enabled for your OS? Maybe you are
running on virtual hosts with a virtual network, which doesn't support
multicasting?). You can either try to fix that or switch to using a
gossip process.

Regards

Markus Wanner

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

Re: [GENERAL] CASE

c k wrote:
> Hi,
> I want to create a function as follows:
>
> case variable=value1
> SQL statements
> case variable=value2
> SQL statements
> case else
> end of case
>
Looks like you want to use pl/pgsl and IF ... THEN ... ELSE instead?

Cheers
Tino

Re: [HACKERS] Postgres-R

Hi,

leiyonghua wrote:
> ./configure --enable-replication
> make & make install

You certainly also want --enable-debug and --enable-cassert, maybe also
additional flags for the C compiler, like -DRMGR_DEBUG, please check the
source code for these.

> 4. install the GCS ensemble, according the document :
> http://www.cs.technion.ac.il/dsl/projects/Ensemble/doc.html
>
> 5. start ensemble daemon and gossip if neccessary ( yes, make sure the
> two nodes can 'GCS' each other)

Yeah, either use the gossip process, or make sure IP multicast works for
your network configuration. I admit that ensemble is quite a beast WRT
compilation and configuration.

> 3. Assume that you have two nodes, start up postgresql and create a
> database 'db', and create a table 'tb' for testing which should be have
> a primary key for all nodes.
>
> 4. At the origin node, execute the command at psql console:
> alter database db start replication in group gcs;
> (which means the database 'db' is the origin and the group 'gcs' is the
> GCS group name)
>
> 5. At the subscriber node, execute the command:
> alter database db accept replication from group gcs;

As recovery doesn't work automatically, you still need to sync the
complete database from the node which initiated the replication group.
Then accept replication.

I'm working on automatic recovery.

Regards

Markus Wanner

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

Re: [HACKERS] about postgres-r setup.

Hi leiyonghua,

leiyonghua wrote:
> and still same status.

Uh.. do you have debugging enabled? Any logging output of the two
postmaster processes?

Regards

Markus

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

Re: [GENERAL] What's size of your PostgreSQL Database?

On Tue, 2008-08-19 at 02:28 -0400, David Wilson wrote:
> On Fri, Aug 15, 2008 at 11:42 PM, Amber <guxiaobo1982@hotmail.com> wrote:
> > Dear all:
> > We are currently considering using PostgreSQL to host a read only warehouse,
> we would like to get some experiences, best practices and performance metrics from the
> user community, following is the question list:

I didn't realise the initial questions from this and since I'm lazy to look for the original
mail, I'll put in my 2 cents worth.

DB is a DSS type store instead of OLTP type. Heavily denormalised data.

Master is a celeron 1.7Ghz, 768MB ram, 2x500GB 7200rpm IDE RAID1(data)+
1 spare, 1x80GB (system).
Slave is a celeron 1.7Ghz, 1.5GB RAM, 3x160GB 7200rpm IDE RAID1(data),
1x160GB system

Max columns ~120
DB size is ~200+GB ~600+M (denormalised) rows in ~60+ tables
(partitioned and otherwise)

vacuum is done nightly in addition to turning on autovacuum.

I'm both IO and CPU constrainted. :-)

Denormalisation/ETL process is done on the master and only the final
product is shipped to the slave for read-only via slony.

I've got close to 8 indexes on each table (for bitmap scanning)

Due to the denormalisation, gettin to the data is very snappy even based
on such a "small" server. (adding ram to the slave saw drastic
performance improvement over the initial 512MB)

Currently looking for an FOSS implementation of a Slice and Dice kind of
drilldown for reporting purposes. Tried a variety including pentaho, but
never been able to get it set-up.

--
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] CASE

Hi,
I want to create a function as follows:

case variable=value1
   SQL statements
case variable=value2
  SQL statements
case else
end of case

CPK

On Tue, Aug 19, 2008 at 5:17 PM, Bill Moran <wmoran@collaborativefusion.com> wrote:

In response to "c k" <shreeseva.learning@gmail.com>:

> Is there any control structure statement similar to select case ... ? If yes
> how to use it. I have tried to use, case when <expression> then <expression>
> end, but not worked well as I want to use other control structures and sql
> statements to be executed for each case.

Personally, I don't understand the question.

Perhaps if you provided an example of what you are trying to do with
CASE WHEN and why it doesn't work for you.

It _does_ sound like you need to be using something like pl/pgsql.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

Re: [HACKERS] Patch: plan invalidation vs stored procedures

"Hannu Krosing" <hannu@2ndQuadrant.com> writes:

> Maybe there should be something in postgreSQL docs that warns users against
> using functions in any non-trivial circumstances, as functions are not
> expected to behave like the rest of postgreSQL features and there is
> not plan to fix that ?

Now who's trolling :)

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

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

Re: [GENERAL] CASE

In response to "c k" <shreeseva.learning@gmail.com>:

> Is there any control structure statement similar to select case ... ? If yes
> how to use it. I have tried to use, case when <expression> then <expression>
> end, but not worked well as I want to use other control structures and sql
> statements to be executed for each case.

Personally, I don't understand the question.

Perhaps if you provided an example of what you are trying to do with
CASE WHEN and why it doesn't work for you.

It _does_ sound like you need to be using something like pl/pgsql.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

--
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] Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully

In response to Dale <harris_da@yahoo.com.au>:

> On Aug 18, 9:23 pm, Lew <no...@lewscanon.com> wrote:
> > Dale wrote:
> > > Hi,
> > > I've got some code which postgres 8.3.3 won't accept.  Postgres
> > > doesn't like the INTO clause on RETURNING INTO and I've tried
> > > following the documentation.
> >
> > > UPDATE "EntityRelation" SET "Status" = inStatus, "Modified" =
> > > Session_TimeStamp(), "ModifiedBy" = UserID() WHERE ("RelationID" =
> > > inRelationID) AND ("EntityID" = inEnityID) AND IsEqual(inRelatedID,
> > > "RelatedID") RETURNING "Default" INTO oldDefault;
> >
> > > Does anyone have any ideas if the INTO clause actually works at all
> > > for an UPDATE statement?
> >
> > <http://www.postgresql.org/docs/8.3/static/sql-update.html>
> > does not list an INTO clause for UPDATE, and when you think about it, indeed
> > such a clause doesn't make sense.
>
> http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html
>
> As per this documentation, you should be able to do it. It works for
> the INSERT command, but not UPDATE. For the INSERT command, it makes
> my code look neater and I image it's more efficient too.

Is it possible that your UPDATE command is updating multiple rows? I
don't believe RETURNING will work on an UPDATE that touches more than 1
row.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

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

Re: [PERFORM] Slow query with a lot of data

>>
> As far as I can tell, it should. If it is clustered on an index on
> domain, and then analysed, it should no longer have to sort on domain.
>
> Could you post here the results of running:
>
> select * from pg_stats where attname = 'domain';
>

schemaname | tablename | attname | null_frac |
avg_width | n_distinct |
most_common_vals
|
most_common_freqs
|
histogram_bounds
|
correlation

public | result | domain | 0 |
4 | 1642 |
{3491378,3213829,3316634,3013831,3062500,3242775,3290846,3171997,3412018,3454092
} |
{0.352333,0.021,0.01,0.00766667,0.00566667,0.00533333,0.00533333,0.005,0.00266667,0.00266667
} |
{3001780,3031753,3075043,3129688,3176566,3230067,3286784,3341445,3386233,3444374,3491203
} |
1

No idea what that means :)
>>
>
> Sounds like an awfully long time to me. Also, I think restricting it
> to 280 users is probably not making it any faster.

If I hadn't restricted it to 280 users it would have run ~350days...

Thanks for your help!

moritz

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

Re: [GENERAL] What's size of your PostgreSQL Database?

In response to Ow Mun Heng <Ow.Mun.Heng@wdc.com>:

> On Mon, 2008-08-18 at 11:01 -0400, justin wrote:
> > Ow Mun Heng wrote:
> > > -----Original Message-----
> > > From: Scott Marlowe <scott.marlowe@gmail.com>
> > >
> > > > If you're looking at read only / read
> > > > mostly, then RAID5 or 6 might be a better choice than RAID-10. But
> > > > RAID 10 is my default choice unless testing shows RAID-5/6 can beat
> > > > it.
> > > >
> > >
> > > I'm loading my slave server with RAID-0 based on 3 IDE 7200 Drives.
> > > Is this worst off than a RAID 5 implementation?
> > >
> > I see no problem using Raid-0 on a purely read only database where
> > there is a copy of the data somewhere else. RAID 0 gives performance.
> > If one of the 3 drives dies it takes the server down and lost of data
> > will happen. The idea behind RAID 1/5/6/10 is if a drive does fail
> > the system can keep going. Giving you time to shut down and replace
> > the bad disk or if you have hot swappable just pull and replace.
>
> I'm looking for purely read-only performance and since I didn't have the
> bandwidth to do extensive testing, I didn't know whether a RAID1 or a
> Raid 0 will do the better job. In the end, I decided to go with RAID 0
> and now, I'm thinking if RAID1 will do a better job.

When talking about pure read performance, the basic rule is the more
spindles you can have active simultaneously, the better. By that rule,
RAID 0 is the best, but you have to balance that with reliability. If
you have 10 disks in a RAID 0, the chance of the entire system going
down because of a disk failure is 10x that of a single disk system --
is that acceptable?

In theory, you can have so many disks that the bottleneck moves to some
other location, such as the IO bus or memory or the CPU, but I've never
heard of that happening to anyone. Also, you want to get fast, high-
quality disks, as 10 15,000 RPM disks are going to perform better than
10 7,200 RPM disks.

Another solution is RAM, if you can get enough RAM in the system to hold
your working set of data, then the speed of the disk is not really
relevant. Of course, that's tough to do if you've got 3TB of data,
which I don't know if that's your case or not.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

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

Re: [PERFORM] Slow query with a lot of data

On Tue, 19 Aug 2008, Moritz Onken wrote:
> explain select
> a."user", b.category, sum(1.0/b.cat_count)::float
> from result a, domain_categories b
> where a."domain" = b."domain"
> group by a."user", b.category;

> Both results and domain_categories are clustered on domain and analyzed.
> Why is it still sorting on domain? I thought the clustering should prevent
> the planner from doing this?

As far as I can tell, it should. If it is clustered on an index on domain,
and then analysed, it should no longer have to sort on domain.

Could you post here the results of running:

select * from pg_stats where attname = 'domain';

> It took 50 minutes to run this query for 280 users ("and "user" IN ([280
> ids])"), 78000 rows were returned and stored in a table. Is this reasonable?

Sounds like an awfully long time to me. Also, I think restricting it to
280 users is probably not making it any faster.

Matthew

--
It's one of those irregular verbs - "I have an independent mind," "You are
an eccentric," "He is round the twist."
-- Bernard Woolly, Yes Prime Minister

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

Re: [GENERAL] CASE

Hello

Develop PL/pgSQL (PostgreSQL 8.4) has CASE statement. Actual and
older version hasn't nothing similar.

Regards
Pavel Stehule

2008/8/19 c k <shreeseva.learning@gmail.com>:
> Is there any control structure statement similar to select case ... ? If yes
> how to use it. I have tried to use, case when <expression> then <expression>
> end, but not worked well as I want to use other control structures and sql
> statements to be executed for each case.
>
> Please reply.
> Thanks
> CPK.
>

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

[GENERAL] CASE

Is there any control structure statement similar to select case ... ? If yes how to use it. I have tried to use, case when <expression> then <expression> end, but not worked well as I want to use other control structures and sql statements to be executed for each case.

Please reply.
Thanks
CPK.

Re: [HACKERS] Patch: plan invalidation vs stored procedures

On Tue, 2008-08-19 at 12:42 +0200, Pavel Stehule wrote:
> 2008/8/19 Hannu Krosing <hannu@2ndquadrant.com>:
> > On Mon, 2008-08-18 at 22:41 +0200, Pavel Stehule wrote:
> >> 2008/8/18 Hannu Krosing <hannu@2ndquadrant.com>:
> >> > On Mon, 2008-08-18 at 11:05 +0200, Pavel Stehule wrote:
> >> >> 2008/8/18 Dimitri Fontaine <dfontaine@hi-media.com>:
> >> >> > Hi,
> >> >> >
> >> >> > Le lundi 18 août 2008, Andrew Dunstan a écrit :
> >> >> >> > On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote:
> >> >> >> >> This is not the kind of patch we put into stable branches.
> >> >> >>
> >> >> >> So what? That is not the only criterion for backpatching.
> >> >> >
> >> >> > I fail to understand why this problem is not qualified as a bug.
> >> >> >
> >> >>
> >> >> Does it change of result some queries?
> >> >
> >> > Not in the long run, but not invalidating the functions (current
> >> > behaviour) postpones seeing the results of function change until DBA
> >> > manually restarts the error-producing client.
> >> >
> >> >> It is protection to server's hang?
> >> >
> >> > Can't understand this question :(
> >> >
> >> > If you mean, does the change protect against hanging the server, then
> >> > no, currently the server does not actually hang, it just becomes
> >> > unusable until reconnect :(
> >>
> >> Hi
> >>
> >> I am sorry, but it's really new feature and not bug fix
> >
> > Could you please explain why you think so ?
> >
> > As I see it, the patch does not change visible behaviour, except
> > removing some sonditions where client becomes unusable after some other
> > backend does some legitimate changes.
>
> Are you sure, so this behave hasn't any secondary effect? So this
> change doesn't breaks any application?

I can't think of any.

What it does, is it makes the changed function usable right after
redefining the new function.

Current behaviour is to make the calling function unusable until the
backend is restarted, after which it still will use the new version of
the function.

> Pavel
>
> >
> > Is the current behavior planned or even defined by spec ?
> >
> > I agree, that the bug (if it is a bug) could also be circumvented by the
> > calling function by detecting a failed cache lookup and doing
> > replan/requery itself, but this would require all PL implementations and
> > other functions with stored plans to do it independently.
> >
> > -----
> > Hannu
> >
> >
> >
> >
>

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

Re: [pgsql-www] pgfoundry support tickets

Whats up?

On Tue, 19 Aug 2008, Peter Eisentraut wrote:

> Is anyone monitoring the pgfoundry support tickets? I'd love to work with
> someone to fix my problems, but I don't know how to reach any of the
> pgfoundry administrators except through the support tickets, and the
> responses there are very rare.
>
> --
> Sent via pgsql-www mailing list (pgsql-www@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-www
>

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email . scrappy@hub.org MSN . scrappy@hub.org
Yahoo . yscrappy Skype: hub.org ICQ . 7615664

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

Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008/8/19 Hannu Krosing <hannu@2ndquadrant.com>:
> On Mon, 2008-08-18 at 22:41 +0200, Pavel Stehule wrote:
>> 2008/8/18 Hannu Krosing <hannu@2ndquadrant.com>:
>> > On Mon, 2008-08-18 at 11:05 +0200, Pavel Stehule wrote:
>> >> 2008/8/18 Dimitri Fontaine <dfontaine@hi-media.com>:
>> >> > Hi,
>> >> >
>> >> > Le lundi 18 août 2008, Andrew Dunstan a écrit :
>> >> >> > On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote:
>> >> >> >> This is not the kind of patch we put into stable branches.
>> >> >>
>> >> >> So what? That is not the only criterion for backpatching.
>> >> >
>> >> > I fail to understand why this problem is not qualified as a bug.
>> >> >
>> >>
>> >> Does it change of result some queries?
>> >
>> > Not in the long run, but not invalidating the functions (current
>> > behaviour) postpones seeing the results of function change until DBA
>> > manually restarts the error-producing client.
>> >
>> >> It is protection to server's hang?
>> >
>> > Can't understand this question :(
>> >
>> > If you mean, does the change protect against hanging the server, then
>> > no, currently the server does not actually hang, it just becomes
>> > unusable until reconnect :(
>>
>> Hi
>>
>> I am sorry, but it's really new feature and not bug fix
>
> Could you please explain why you think so ?
>
> As I see it, the patch does not change visible behaviour, except
> removing some sonditions where client becomes unusable after some other
> backend does some legitimate changes.
>
> Is the current behavior planned or even defined by spec ?
>
> I agree, that the bug (if it is a bug) could also be circumvented by the
> calling function by detecting a failed cache lookup and doing
> replan/requery itself, but this would require all PL implementations and
> other functions with stored plans to do it independently.
>

I am not against to this patch or this feature. But I am sure, so
isn't well to do not necessary changes in stable version.

Pavel

> -----
> Hannu
>
>
>
>

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

Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008/8/19 Hannu Krosing <hannu@2ndquadrant.com>:
> On Mon, 2008-08-18 at 22:41 +0200, Pavel Stehule wrote:
>> 2008/8/18 Hannu Krosing <hannu@2ndquadrant.com>:
>> > On Mon, 2008-08-18 at 11:05 +0200, Pavel Stehule wrote:
>> >> 2008/8/18 Dimitri Fontaine <dfontaine@hi-media.com>:
>> >> > Hi,
>> >> >
>> >> > Le lundi 18 août 2008, Andrew Dunstan a écrit :
>> >> >> > On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote:
>> >> >> >> This is not the kind of patch we put into stable branches.
>> >> >>
>> >> >> So what? That is not the only criterion for backpatching.
>> >> >
>> >> > I fail to understand why this problem is not qualified as a bug.
>> >> >
>> >>
>> >> Does it change of result some queries?
>> >
>> > Not in the long run, but not invalidating the functions (current
>> > behaviour) postpones seeing the results of function change until DBA
>> > manually restarts the error-producing client.
>> >
>> >> It is protection to server's hang?
>> >
>> > Can't understand this question :(
>> >
>> > If you mean, does the change protect against hanging the server, then
>> > no, currently the server does not actually hang, it just becomes
>> > unusable until reconnect :(
>>
>> Hi
>>
>> I am sorry, but it's really new feature and not bug fix
>
> Could you please explain why you think so ?
>
> As I see it, the patch does not change visible behaviour, except
> removing some sonditions where client becomes unusable after some other
> backend does some legitimate changes.

Are you sure, so this behave hasn't any secondary effect? So this
change doesn't breaks any application?

Pavel

>
> Is the current behavior planned or even defined by spec ?
>
> I agree, that the bug (if it is a bug) could also be circumvented by the
> calling function by detecting a failed cache lookup and doing
> replan/requery itself, but this would require all PL implementations and
> other functions with stored plans to do it independently.
>
> -----
> Hannu
>
>
>
>

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

[COMMITTERS] pgsql: Fixed incorrect argument handling in SET command if argument is a

Log Message:
-----------
Fixed incorrect argument handling in SET command if argument is a variable.

Modified Files:
--------------
pgsql/src/interfaces/ecpg:
ChangeLog (r1.384 -> r1.385)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/ChangeLog?r1=1.384&r2=1.385)
pgsql/src/interfaces/ecpg/preproc:
preproc.y (r1.369 -> r1.370)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/preproc/preproc.y?r1=1.369&r2=1.370)
pgsql/src/interfaces/ecpg/test/expected:
sql-show.c (r1.13 -> r1.14)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-show.c?r1=1.13&r2=1.14)
sql-show.stderr (r1.8 -> r1.9)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-show.stderr?r1=1.8&r2=1.9)
sql-show.stdout (r1.3 -> r1.4)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-show.stdout?r1=1.3&r2=1.4)
pgsql/src/interfaces/ecpg/test/sql:
show.pgc (r1.2 -> r1.3)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/sql/show.pgc?r1=1.2&r2=1.3)

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

Re: [HACKERS] Patch: plan invalidation vs stored procedures

On Mon, 2008-08-18 at 20:29 -0400, Tom Lane wrote:
> "Asko Oja" <ascoja@gmail.com> writes:
> > For users of stored procedures it is protection from downtime. For Skype it
> > has been around 20% of databse related downtime this year.
>
> Perhaps Skype needs to rethink how they are modifying functions.

Why not suggest they just should stop using functions and move all
business logic into client or "3rd tier" ?

(Actually I would not recommend that as functions are very good way to
abstract database access AND provide better security AND speed up
queries)

> The reason that this case wasn't covered in 8.3 is that there didn't
> seem to be a use-case that justified doing the extra work. I still
> haven't seen one. Other than inline-able SQL functions there is no
> reason to invalidate a stored plan based on the fact that some function
> it called changed contents.

Maybe there should be something in postgreSQL docs that warns users against
using functions in any non-trivial circumstances, as functions are not
expected to behave like the rest of postgreSQL features and there is
not plan to fix that ?

----------------
Hannu

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

Re: [INTERFACES] ecpg 'set' failure using host vars

--- pgsql-ecpg/preproc/preproc.y 2008-08-18 16:56:11.000000000 +0200
+++ pgsql/src/interfaces/ecpg/preproc/preproc.y 2008-08-19 12:29:28.000000000 +0200
@@ -1247,7 +1247,16 @@
;

var_value: opt_boolean { $$ = $1; }
- | AllConst { $$ = $1; }
+ | AllConst { /* we have to check for a variable here because it has to be
+ replaced with its value on the client side */
+ if ($1[1] == '$')
+ {
+ $$ = make_str("$0");
+ free($1);
+ }
+ else
+ $$ = $1;
+ }
| ColId { $$ = $1; }
;

On Fri, Aug 15, 2008 at 08:47:49PM -0700, Bosco Rama wrote:
> exec sql char *sm;
> ...
> sm = "myschema"; // This may not always be constant
> ...
> exec sql set search_path to :sm;

Could you please try the attached patch and tell me whether it works for you? I will then check this in into 8.3 so that the next stable release works again as it should.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

Re: [GENERAL] NOT DEFERRABLE as default, why and how to manage it.

On Tue, 19 Aug 2008 10:49:11 +0200
Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:

> On Tue, 19 Aug 2008 11:20:08 +0300
> Peter Eisentraut <peter_e@gmx.net> wrote:

> > Am Tuesday, 19. August 2008 schrieb Ivan Sergio Borgonovo:
> > > I just learnt that NOT DEFERRABLE is default.

> > > Is it mandated by SQL standard?

> > Yes.

> Is there any reason they put it that way in the standard other than
> the mantra "stricter is better"?

After reflecting a bit I think it is a matter of "failing earlier".
But it doesn't make things more transparent.
Since there is no simple standard way to see which constraints are
deferrable and no simple way to alter them.

If you expect a constraint to be deferrable and it is not there are
higher chances you'll have some warning.
If you expect a constraint to be not deferrable but it is...
the chances that something you're not expecting will silently happen
are higher.
But you can still get surprises in both cases.

It would be nice to know some way which constraint are checked
during a transaction so it would be easier to see wich ones you
really need to defer and which one were declared as not deferrable.

anyway are there guidelines on how/when changing directly the system
tables?

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

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

Re: [HACKERS] Patch: plan invalidation vs stored procedures

On Mon, 2008-08-18 at 22:41 +0200, Pavel Stehule wrote:
> 2008/8/18 Hannu Krosing <hannu@2ndquadrant.com>:
> > On Mon, 2008-08-18 at 11:05 +0200, Pavel Stehule wrote:
> >> 2008/8/18 Dimitri Fontaine <dfontaine@hi-media.com>:
> >> > Hi,
> >> >
> >> > Le lundi 18 août 2008, Andrew Dunstan a écrit :
> >> >> > On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote:
> >> >> >> This is not the kind of patch we put into stable branches.
> >> >>
> >> >> So what? That is not the only criterion for backpatching.
> >> >
> >> > I fail to understand why this problem is not qualified as a bug.
> >> >
> >>
> >> Does it change of result some queries?
> >
> > Not in the long run, but not invalidating the functions (current
> > behaviour) postpones seeing the results of function change until DBA
> > manually restarts the error-producing client.
> >
> >> It is protection to server's hang?
> >
> > Can't understand this question :(
> >
> > If you mean, does the change protect against hanging the server, then
> > no, currently the server does not actually hang, it just becomes
> > unusable until reconnect :(
>
> Hi
>
> I am sorry, but it's really new feature and not bug fix

Could you please explain why you think so ?

As I see it, the patch does not change visible behaviour, except
removing some sonditions where client becomes unusable after some other
backend does some legitimate changes.

Is the current behavior planned or even defined by spec ?

I agree, that the bug (if it is a bug) could also be circumvented by the
calling function by detecting a failed cache lookup and doing
replan/requery itself, but this would require all PL implementations and
other functions with stored plans to do it independently.

-----
Hannu

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

Re: [HACKERS] A smaller default postgresql.conf

Peter Eisentraut wrote:
> I seem to recall that there was general support for installing a smaller
> default postgresql.conf file with only, say, a dozen parameters mentioned for
> initial tuning. The complete file can stay as a sample. Any objections to
> that? (Let's not discuss quite yet exactly which parameters are the chosen
> ones.)
>
>

i think this would make sense as long as this small file tells users
where to find the "full story".
generally i would say that this would be a step into the right direction.

alternatively we could use some sort of "#include" mechanism to split
"most important" and "not so important".

hans

--
Cybertec Schönig & Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql-support.de, www.postgresql-support.com

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

[HACKERS] A smaller default postgresql.conf

I seem to recall that there was general support for installing a smaller
default postgresql.conf file with only, say, a dozen parameters mentioned for
initial tuning. The complete file can stay as a sample. Any objections to
that? (Let's not discuss quite yet exactly which parameters are the chosen
ones.)

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

[pgsql-www] pgfoundry support tickets

Is anyone monitoring the pgfoundry support tickets? I'd love to work with
someone to fix my problems, but I don't know how to reach any of the
pgfoundry administrators except through the support tickets, and the
responses there are very rare.

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

Re: [GENERAL] How to execute 'set session role' from plpgsql function?

Олег Василенко wrote:
> I wish to have a function with code above, but compiller generate
> syntactic error at the line "SET SESSION ROLE wishedrole;".
>
> How to pass the wishedrole value to the structure?
>
> CREATE OR REPLACE FUNCTION f_switch_role(INOUT text,INOUT boolean) AS
> $BODY$
> DECLARE
> wishedrole ALIAS FOR $1;
> resetrole ALIAS FOR $2;
> BEGIN
> if resetrole=true then
> RESET ROLE;
> RETURN;
> end if;
>
> >>>>ERROR OCURS AT THE NEXT LINE <<<<<<
> SET SESSION ROLE wishedrole;

You need to use dynamic SQL, e.g.

EXECUTE 'SET SESSION ROLE "' || lower(regexp_replace(wishedrole, '"', '', 'g')) || '"';

> RETURN;
>
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE

The "lower" and "regexp_replace" are there to prevent SQL injection.

Yours,
Laurenz Albe

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

Re: [BUGS] BUG #4340: SECURITY: Is SSL Doing Anything?

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Actually, I had missed that the OP was looking at 7.3 rather than 8.3.
> There was a "verify_peer()" in 7.3 but it was #ifdef'd out. The
> question remains whether there's a reason to have it. It would be good
> if the discussion were based on a non-obsolete PG version ...

Well in theory SSL without at least one-way authentication is actually
worthless. It's susceptible to man-in-the-middle attacks meaning someone can
sniff all the contents or even inject into or take over connections. It is
proof against passive attacks but active attacks are known in the field so
that's cold comfort these days.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

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

Re: [PATCHES] hash index improving v3

With the help of David Fetter, you can get the copy by
git clone http://git.postgresql.org/git/~davidfetter/hash/.git
It's in the branch gsoc-hash.
Thank you, David.

--
Best Regards,
Xiao Meng

DKERC, Harbin Institute of Technology, China
Gtalk: mx.cogito@gmail.com
MSN: cnEnder@live.com
http://xiaomeng.yo2.cn

Re: [HACKERS] Overhauling GUCS

Am Monday, 18. August 2008 schrieb Tom Lane:
> The impression I get every time this comes up is that various people
> have different problems they want to solve that (they think) require
> redesign of the way GUC works.  Those complicated solutions arise from
> attempting to satisfy N different demands simultaneously.

Which may be the reason that I have been getting the impression that
the "Problems" and the proposed resolutions on
http://wiki.postgresql.org/wiki/GUCS_Overhaul are not really closely related.
I can agree with the Problems, but then I am lost.

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

Re: [HACKERS] possible minor EXPLAIN bug?

2008/8/19 Simon Riggs <simon@2ndquadrant.com>:
>
> On Tue, 2008-08-19 at 09:45 +0200, Pavel Stehule wrote:
>
>> postgres=# explain select sum(a) from twocol group by b;
>> QUERY PLAN
>> ----------------------------------------------------------------
>> HashAggregate (cost=42.10..44.60 rows=200 width=8) --< wrong should be 4
>> -> Seq Scan on twocol (cost=0.00..31.40 rows=2140 width=8)
>> (2 rows)
>
> Although column b is not displayed it is kept in the HashAgg node to
> allow your request to GROUP BY B. I'm happy that it tells me the width
> of 8 so I can work out space used by hash, but perhaps it should say 12
> (or even 16) to include hash value also, so we include the full cost per
> row in the hash table.
>
> If you do
> explain select sum(a) from twocol
> you will see the width is only 4

yes, Agg get this value directly, but it wrong

postgres=# explain select * from (select sum(a) from twocol group by b
offset 0) c;
QUERY PLAN
----------------------------------------------------------------------------
Subquery Scan c (cost=42.10..46.60 rows=200 width=8)
-> Limit (cost=42.10..44.60 rows=200 width=8)
-> HashAggregate (cost=42.10..44.60 rows=200 width=8)
-> Seq Scan on twocol (cost=0.00..31.40 rows=2140 width=8)
(4 rows)

limit, subquery scan has wrong width now.

regards
Pavel
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Training, Services and Support
>
>

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

[HACKERS] Auto-tuning GUCS

> I do think you and others make it less likely every time you throw up big
> insoluble problems like above though. As a consequence every proposal has
> started with big overly-complex solutions trying to solve all these incidental
> issues which never go anywhere instead of simple solutions which directly
> tackle the main problem.

insoluble? overly-complex solution? parsing a text file? I do not think we understand
each other, or rather we start with totally different assumptions and design goals.
it was probably a mistake to post keeping the subject line as it is, considering I have
no interest overhauling GUCS, but this is where the subject of autotuning was brought
up and this is where I posted.

now, to me, shell access, cron jobs, text config files - or rather, a single text config
file, these are all good. if you plan to deploy/maintain entire farms or cloud solutions,
tough! you should be looking into configuration management, such as cfengine and
puppet already!

you seem to consider ease of use a prerequisite for tuning efficiency, our design goals
couldn't be more different. what you want is an installer - what I'd like is DBA support

>Coping with user and system-generated comments is one difficult part that people
>normally don't consider, dealing with bad settings the server won't start with is another.

now, as things stand, I will tinker in this area, simply because I'm stubborn and this is
part of my job. I have parsed many text files in my professional career, please do not
think a simple config file should be a problem (even with comments, I think)

The impression I get every time this comes up is that various people
have different problems they want to solve that (they think) require
redesign of the way GUC works.  Those complicated solutions arise from
attempting to satisfy N different demands simultaneously.  The fact that
many of these goals aren't subscribed to by the whole community to begin
with doesn't help to ease resolution of the issues.

in this single thread I have identified at least three different development targets:

* newbie-friendly default-guessing installer
* configuration manager for farms/clouds etc.
* auto-tuning support

this is why I'm posting this with a different subject line. If anyone wants to discuss the
GUCS auto-tuning part, I'm all ears.

regards,

Michael

Re: [HACKERS] Overhauling GUCS

Am Monday, 18. August 2008 schrieb Josh Berkus:
> Right now, if you want to survey
> your databases, tables, approx disk space, query activity, etc., you can
> do that all through port 5432.  You can't manage most of your server
> settings that way, and definitely can't manage the *persistent* settings.  
> When you're trying to manage 1000 PostgreSQL servers, this is not a minor
> issue.

Some of that effort could go into making less settings persistent.

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

Re: [HACKERS] Extending varlena

Am Monday, 18. August 2008 schrieb Tom Lane:
> - permissions features (more than "none" anyway)
> - better management of orphaned objects (obsoleting vacuumlo)
> - support > 16TB of large objects (maybe partition pg_largeobject?)
> - dump and restore probably need improvement to be practical for such
>   large data volumes

If you replace the third point by "maybe partition TOAST tables", replace
large object handle by TOAST pointer, and create an API to work on TOAST
pointers, how are the two so much different? And why should they be? I can
see that there are going to be needs to access large data with interfaces
that are not traditional SQL, but at least the storage handling could be the
same. That way you would solve the first two points and others for free.

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

Re: [HACKERS] possible minor EXPLAIN bug?

On Tue, 2008-08-19 at 09:45 +0200, Pavel Stehule wrote:

> postgres=# explain select sum(a) from twocol group by b;
> QUERY PLAN
> ----------------------------------------------------------------
> HashAggregate (cost=42.10..44.60 rows=200 width=8) --< wrong should be 4
> -> Seq Scan on twocol (cost=0.00..31.40 rows=2140 width=8)
> (2 rows)

Although column b is not displayed it is kept in the HashAgg node to
allow your request to GROUP BY B. I'm happy that it tells me the width
of 8 so I can work out space used by hash, but perhaps it should say 12
(or even 16) to include hash value also, so we include the full cost per
row in the hash table.

If you do
explain select sum(a) from twocol
you will see the width is only 4

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

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

Re: [PERFORM] Slow query with a lot of data

Am 18.08.2008 um 18:05 schrieb Matthew Wakeling:

> On Mon, 18 Aug 2008, Moritz Onken wrote:
>> Running the query for more than one user is indeed not much slower.
>> That's what I need. I'm clustering the results table on domain
>> right now. But why is this better than clustering it on "user"?
>
> The reason is the way that the merge join algorithm works. What it
> does is takes two tables, and sorts them both by the join fields.
> Then it can stream through both tables producing results as it goes.
> It's the best join algorithm, but it does require both tables to be
> sorted by the same thing, which is domain in this case. The
> aggregating on user happens after the join has been done, and the
> hash aggregate can accept the users in random order.
>
> If you look at your last EXPLAIN, see that it has to sort the result
> table on domain, although it can read the domain_categories in
> domain order due to the clustered index.

explain select
a."user", b.category, sum(1.0/b.cat_count)::float
from result a, domain_categories b
where a."domain" = b."domain"
group by a."user", b.category;

"GroupAggregate (cost=21400443313.69..22050401897.13 rows=35049240
width=12)"
" -> Sort (cost=21400443313.69..21562757713.35 rows=64925759864
width=12)"
" Sort Key: a."user", b.category"
" -> Merge Join (cost=4000210.40..863834009.08
rows=64925759864 width=12)"
" Merge Cond: (b.domain = a.domain)"
" -> Index Scan using domain_categories_domain on
domain_categories b (cost=0.00..391453.79 rows=12105014 width=12)"
" -> Materialize (cost=3999931.73..4253766.93
rows=20306816 width=8)"
" -> Sort (cost=3999931.73..4050698.77
rows=20306816 width=8)"
" Sort Key: a.domain"
" -> Seq Scan on result a
(cost=0.00..424609.16 rows=20306816 width=8)"

Both results and domain_categories are clustered on domain and analyzed.
It took 50 minutes to run this query for 280 users ("and "user" IN
([280 ids])"), 78000 rows were returned and stored in a table. Is this
reasonable?
Why is it still sorting on domain? I thought the clustering should
prevent the planner from doing this?

moritz

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

Re: [HACKERS] Compatibility types, type aliases, and distinct types

Am Monday, 18. August 2008 schrieb Tom Lane:
> If the type has no functions of its own, then the only way to make it
> easily usable is to throw in implicit conversions *in both directions*
> between it and the type it's an alias for.  You're going to find that
> that's a problem.

I'm not finding that that's a problem. We have several cases of that in the
standard catalogs already. What kind of problem are you foreseeing?

One direction of the cast could be AS ASSIGNMENT, btw., but that is another
decision that would have to be worked out.

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

[GENERAL] 8.3.3 win32 crashing

Does this mean anything to anyone?

Faulting application postgres.exe, version 8.3.3.8160, faulting module
msvcr80.dll, version 8.0.50727.1433, fault address 0x0001e44a.

I have a function that's doing a summary report counting data. It makes
a couple of small temp tables then makes a big temp table. In the query
that makes the big temp table, there's an immutable function that gets
called thousands of times in the execution. In the immutable function,
there was a raise notice for debugging . Without the raise, the query
finishes in about 40sec. With it, the postgres.exe grinds down to about
2% cpu usage and eventually throws the message above. Then I reboot the
machine - nothing responds to control.

After reading of recent win32 network buffer problems, I'm wondering if
this is another one. The context statement is nearly 8k long. The
server is w2k3 on a 3.8ghz P4 with 3g memory on a 10mbit network (don't
ask about the network - it just is and there's nothing I can do about
it). The client is xp pentium M notebook, 2ghz, 1g memory.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

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