Views do not help or hurt performance. Views encapsulate complex queries.
If you have a slow running query, the usual way to get help is to post:
*) explain analyze results (most important)
*) the query (important)
*) interesting tables/indexes (somewhat important)
--
These
On Tue, 27 Oct 2009 10:54:06 +
Richard Huxton wrote:
> > Association between email and password is just meant to build up
> > a queue for mailing and there is no uniqueness constraint on
> > (password, email) pair.
>
> > create table pw_email(
> > password varchar(16),
> > email varchar(
Hello. I'm new with postgresql, some times ago i'm turn on log slow
queries, but log file contains not only queries , nor
Oct 28 13:03:44 selfip postgres[18072]: [5-1]
user=dbu_vase_1,db=db_vase_1 WARNING: nonstandard use of \\ in a string
literal at character 90
Oct 28 13:03:44 selfip postgres[18
On Tue, Oct 27, 2009 at 06:53:55PM +, Tim Landscheidt wrote:
> You would have to adjust the result of "(EXTRACT('epoch'
> FROM B) - EXTRACT('epoch' FROM A)) / EXTRACT('epoch' FROM
> C)" by a factor of 31/30 (30/28? 28/30?) and then chop off
> timestamps after B with a "WHERE" clause.
I'm not s
On Tue, Oct 27, 2009 at 6:31 PM, Alvaro Herrera
wrote:
> JC Praud escribió:
>
> > So my question are: can the autovacuum daemon perform vacuum full ? Or
> > another internal postgres process ? Could it come from the TRUNCATE I run
> > and canceled 4 days before ?
>
> No. Autovacuum only issues co
On 28 Oct 2009, at 9:57, fox7 wrote:
Views do not help or hurt performance. Views encapsulate complex
queries.
If you have a slow running query, the usual way to get help is to
post:
*) explain analyze results (most important)
You forgot to show us the most important part.
*) the quer
> Rhys A.D. Stewart wrote:
> >I would like to remove the outliers in distance
As others have said; an "outlier" is normally a human call and not
something that's generally valid to do automatically. The operator
would probably want to go in and look to see why it's that far out and
either fix the
>
> I'd agree, stddev is probably best and the following should do
> something
> reasonable for what the OP was asking:
>
> SELECT d.*
> FROM data d, (
> SELECT avg(distance), stddev(distance) FROM data) x
> WHERE abs(d.distance - x.avg) < x.stddev * 2;
>
[Spotts, Christopher]
Statis
Alban Hertroys-3 wrote:
>
> On 28 Oct 2009, at 9:57, fox7 wrote:
>
> You forgot to show us the most important part.
> ---
> Do you absolutely need to order the output of your views? You could
> just order the results of your queries on your views instead. The way
> you do it now the databa
On 28 Oct 2009, at 13:42, fox7 wrote:
What do you mean for analyze results?
http://www.postgresql.org/docs/8.4/interactive/sql-explain.html
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4ae83f5911071064615400!
--
In response to fox7 :
>
> What do you mean for analyze results?
Try "explain analyse select ..."
>
> I create views by means of jdbc...
> For example I have created V2TO as:
> CREATE VIEW v2TO AS (
> SELECT DISTINCT TO.term1, TO.term2
> FROM TO
> UNION
> SELECT TB.term2 AS term1, TB.term1 AS t
Alban Hertroys-3 wrote:
>
>> What do you mean for analyze results?
> http://www.postgresql.org/docs/8.4/interactive/sql-explain.html
>
thanks...
Now I try and put here the results...
However I'm using Postgre 8.3, not 8.4...
...but I don't think this is the problem!
--
View this message in c
Hi,
Are there any test guides/plans generated for alpha releases, or are
such things only distributed to other developers? I've seen postings
which mention what the new features are, and links to documentation
and other postings as to what it can do, but no single page outlining
the changes toget
have you seen that one:
http://it.toolbox.com/blogs/database-soup/alpha2-is-out-and-we-need-you-to-test-it-35032?rss=1
?
2009/10/28 Grzegorz Jaśkiewicz :
> have you seen that one:
> http://it.toolbox.com/blogs/database-soup/alpha2-is-out-and-we-need-you-to-test-it-35032?rss=1
> ?
That's partly why I was asking. It mentions the areas where the
changes have occurred, but not necessarily the changes themselves. An
ex
I copy the results derived by istruction "EXPLAIN ANALYZE" for the two
query...
--Query without views-
"Unique (cost=406.58..407.13 rows=73 width=114) (actual time=1.262..1.448
rows=40 loops=1)"
" -> Sort (cost=406.58..406.77 rows=73 width=114) (act
On Wednesday 28 October 2009 6:46:13 am Thom Brown wrote:
> 2009/10/28 Grzegorz Jaśkiewicz :
> > have you seen that one:
> > http://it.toolbox.com/blogs/database-soup/alpha2-is-out-and-we-need-you-t
> >o-test-it-35032?rss=1 ?
>
> That's partly why I was asking. It mentions the areas where the
> ch
2009/10/28 Adrian Klaver :
>> Entirely new features are easier to deal with though. I still would,
>> however, want something like a detailed version of Josh's post which
>> breaks down where the changes have occurred. It seems quite scattered
>> and unclear at the moment.
>>
>> Thom
>
> http://d
On Wed, Oct 28, 2009 at 4:50 AM, Ivan Sergio Borgonovo
wrote:
>
> To have the 3rd constraint I'd have a table:
> create table pw_res(
> password varchar(16) primary key,
> res int references resources (res) on delete cascade
> );
>
> This comes handy for 2 reasons:
> - it helps me to enforce the
Sorry, I got a bit lost in the thread. BTW, Thanks for all the answers :)
On Wed, Oct 28, 2009 at 12:29 AM, Alvaro Herrera wrote:
> JC Praud escribió:
>
> > - Last night the database locked. pg_log full of messages about insert
> into
> > the mother table waiting for a lock.
>
> This bit does no
Hi,
I'm trying to aggregate a list of table attributes into an array.
The actual code looks something like this:
SELECT
node_ref AS id,
array_agg(DISTINCT ARRAY[namespace, name, value]) as annotations
...
GROUP BY id;
I guess the minimal example that reproduces the error is:
an
Viktor Rosenfeld writes:
> annis=> select array_agg(array['a'::varchar, 'b', 'c']);
> ERROR: could not find array type for data type character varying[]
> Why doesn't this work?
The output of array_agg would have to be an array whose elements
are array-of-varchar. Which is a datatype we do
JC Praud escribió:
> On Wed, Oct 28, 2009 at 12:29 AM, Alvaro Herrera > This bit does not make much sense to me. A transaction waiting will not
> > show up in the log. Were they cancelled? Can you paste an extract from
> > the log?
>
> No, the transactions were not cancelled. All I saw in he
On Wed, Oct 28, 2009 at 04:17:32PM +0100, Viktor Rosenfeld wrote:
> I'm trying to aggregate a list of table attributes into an array.
I'd suggest using a tuple, arrays for things where each element means
the same thing. I'd guess you care about the substructure (i.e. the
element has a "namespace"
On Wed, Oct 28, 2009 at 11:17 AM, Viktor Rosenfeld
wrote:
> Hi,
>
> I'm trying to aggregate a list of table attributes into an array.
>
> The actual code looks something like this:
>
> SELECT
> node_ref AS id,
> array_agg(DISTINCT ARRAY[namespace, name, value]) as annotations
> ...
> GROU
On Wed, 28 Oct 2009 10:12:19 -0500
Peter Hunsberger wrote:
> > The first approach requires a distinct/group by that may be
> > expensive.
> > The second one requires I keep in memory all the emails while the
> > first statement run.
> Unless you're dealing with 100,000's of these things I think
On Tue, Oct 27, 2009 at 6:29 PM, Alvaro Herrera
wrote:
>
> Do you have a vacuum in cron or something like that? As Tom says, if it
> had been autovacuum, it should have been cancelled automatically (else
> we've got a bug); but something invoking vacuum externally wouldn't
> have, so what you des
Jaime Casanova writes:
> On Tue, Oct 27, 2009 at 6:29 PM, Alvaro Herrera
> wrote:
>> Do you have a vacuum in cron or something like that? Â As Tom says, if it
>> had been autovacuum, it should have been cancelled automatically (else
>> we've got a bug); but something invoking vacuum externally wo
Looking for a forms generator for a web based UI for
entering/modifiying/viewing a table's records.
Any recommendations ???
Thanks,
Stuart
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-
i want to create a type for an email field but i'm not good with regx
can some one help me?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
We are running version:
edb=# select version();
version
-
EnterpriseDB 8.3.0.106 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.0
(1 row)
Has anyone seen this w
This script executed by cron. And segmentation fault generated by PHP script.
Richard Huxton wrote:
>
> PHP doesn't really do connection pools anyway. You would have ended up
> with one connection for each Apache backend.
>
> What fails with "segmentation fault" - Apache+PHP, pgpool or Post
Stuart Adams wrote on 28.10.2009 17:59:
Looking for a forms generator for a web based UI for
entering/modifiying/viewing a table's records.
Any recommendations ???
Thanks,
Stuart
I haven't used this (yet), but once:Radix seems to be what you are looking for
http://www.oncetechnologies
Penrod, John wrote:
> We are running version:
>
> edb=# select version();
>version
> -
> EnterpriseDB 8.3.0.106 on x86_64-unknown-linux-gnu, compiled by GCC gcc
> (GCC) 4.1
Hi Stuart,
I have seen some form generators, but for some reason or the other
they always partially worked,
or never fit my dataset because more often then others they assume
very simple relations.
Nowdays I tend to use Adobe Flex for a lot of my work (there are some
form generators for
"Penrod, John" writes:
> edb=# select version();
>version
> -
> EnterpriseDB 8.3.0.106 on x86_64-unknown-linux-gnu, compiled by GCC gcc
> (GCC) 4.1.0
> (1 row)
> Has anyon
Thank you. I will do that.
John J. Penrod, OCP
Oracle/EnterpriseDB Database Administrator
St. Jude Children's Research Hospital
262 Danny Thomas Place, MS 0574
Memphis, TN 38105
Phone: (901) 595-4941
FAX: (901) 595-2963
john.pen...@stjude.org
-Original Message-
From: Alvaro Herrera [mai
Hello All,
I'm new to postgres and it seems my server is unable to fork new
connections.
Here is the log:
LOG: could not fork new process for connection: Not enough space
LOG: could not fork new process for connection: Not enough space
TopMemoryContext: 84784 total in 8 blocks; 5584 free (
2009/10/28 Brooks Lyrette :
> Hello All,
>
> I'm new to postgres and it seems my server is unable to fork new
> connections.
>
> Here is the log:
>
> LOG: could not fork new process for connection: Not enough space
> LOG: could not fork new process for connection: Not enough space
> TopMemoryCont
VladK wrote:
> This script executed by cron. And segmentation fault generated by PHP
> script.
In that case you have a bug in one of: Apache, PHP, PDO libraries.
If the PDO libraries use PostgreSQL's libpq library then that could be
involved too.
Even if pgpool has a bug and isn't communicating
2009/10/28 Brooks Lyrette :
> There should be no other processes running, this system is dedicated to
> running postgresql.
>
> Max connections is configured to: max_connections = 400
>
Well it sounds like you've somehow run out of swap space. Are you
able to run top and sort by resident memory a
Vasiliy G Tolstov wrote:
> user=dbu_vase_1,db=db_vase_1 HINT: Use the escape string syntax for
> backslashes, e.g., E'\\'.
>
> How can i disable this hints, or (i'm use drupal for this database) fix
> queries?
See the manual section on configuration, "escape_string_warning".
--
Richard Huxto
Xai wrote:
> i want to create a type for an email field but i'm not good with regx
> can some one help me?
Google for "email regex". Be warned - this is very complicated if you
want to match *all* possible email addresses.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-general mailing li
Brooks Lyrette writes:
> I'm new to postgres and it seems my server is unable to fork new
> connections.
> LOG: could not fork new process for connection: Not enough space
For what I suppose is a lightly loaded machine, that is just plain
weird. What's the platform exactly? Is it possible t
Hi,
this looks good, but it does not work with DISTINCT.
CREATE TYPE annotation AS ( namespace varchar, name varchar, value varchar );
SELECT
node.id as id,
array_agg(DISTINCT ROW(namespace, name, value)::annotation) as annotation
...
GROUP BY id
produces:
ERROR: could not
Thanks!
kynn
On Tue, Oct 27, 2009 at 4:02 PM, Richard Huxton wrote:
> Kynn Jones wrote:
> > How can I list the permissions of a given user/role for a specific
> > relation/view/index, etc.?
>
> From psql use \dp
>
> Using plain SQL, the closest I can think of are the has_xxx_privilege()
> func
The machine is running a moderate load. This is running on a Solaris
Zone.
Top is showing:
load averages: 2.49, 4.00, 3.78;up
124
+
12
:
24
:
47
There should be no other processes running, this system is dedicated
to running postgresql.
Max connections is configured to: max_connections = 400
Brooks L.
On 28-Oct-09, at 3:46 PM, Thom Brown wrote:
2009/10/28 Brooks Lyrette :
Hello All,
I'm new to postgres and it seem
On Wed, Oct 28, 2009 at 05:45:14AM -0700, Xai wrote:
> i want to create a type for an email field but i'm not good with regx
> can some one help me?
http://marc.info/?l=postgresql-general&m=112612299412819&w=2
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make chan
On Wed, Oct 28, 2009 at 1:05 PM, Brooks Lyrette
wrote:
> The machine is running a moderate load. This is running on a Solaris Zone.
>
> Memory: 32G phys mem, 942M free mem, 76G swap, 74G free swap
>
> PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
> 5069 postgres 1 52
On Wed, 28 Oct 2009, Tom Lane wrote:
What's the platform exactly? Is it possible that the postmaster is
being launched under very restrictive ulimit settings?
Now that Brooks mentioned this being run inside of a Solaris zone, seems
like this might be running into some memory upper limit cont
On Wed, 28 Oct 2009, Greg Stark wrote:
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
5069 postgres 1 52 0 167M 20M sleep 0:04 13.50% postgres
Hm, well 400 processes if each were taking 190M would be 76G. But that
doesn't really make much sense since most of
Greg Smith writes:
> On Wed, 28 Oct 2009, Tom Lane wrote:
>> What's the platform exactly? Is it possible that the postmaster is
>> being launched under very restrictive ulimit settings?
> Now that Brooks mentioned this being run inside of a Solaris zone, seems
> like this might be running into
Le mercredi 28 octobre 2009 à 15:13:06, Thom Brown a écrit :
> 2009/10/28 Adrian Klaver :
> >> Entirely new features are easier to deal with though. I still would,
> >> however, want something like a detailed version of Josh's post which
> >> breaks down where the changes have occurred. It seems
Viktor Rosenfeld writes:
> this looks good, but it does not work with DISTINCT.
> CREATE TYPE annotation AS ( namespace varchar, name varchar, value varchar
> );
> ERROR: could not identify an equality operator for type annotation
My recollection is you need a complete btree operator clas
Le mercredi 28 octobre 2009 à 23:30:01, Adrian Klaver a écrit :
> - "Guillaume Lelarge" wrote:
> > Le mercredi 28 octobre 2009 à 15:13:06, Thom Brown a écrit :
> > > Similarly: "Fix encoding handling in binary input function of xml
> > > type." What was the problem before?
>
> See attached s
- "Guillaume Lelarge" wrote:
> Le mercredi 28 octobre 2009 à 23:30:01, Adrian Klaver a écrit :
> > - "Guillaume Lelarge" wrote:
> > > Le mercredi 28 octobre 2009 à 15:13:06, Thom Brown a écrit :
> > > > Similarly: "Fix encoding handling in binary input function of
> xml
> > > > type."
2009/10/28 Adrian Klaver :
>
>
>
> - "Guillaume Lelarge" wrote:
>
>> Le mercredi 28 octobre 2009 à 15:13:06, Thom Brown a écrit :
>
>> >
>> > Similarly: "Fix encoding handling in binary input function of xml
>> > type." What was the problem before?
>> >
>
> See attached screen shot for one po
Thom Brown escribió:
> Obviously PostgreSQL has survived very well without this, but I would
> expect this would help more users perform more testing.
Keep in mind alphas are new. Last time around, we only released a test
version when we were going to go to beta. And the alpha idea was
accepted
On Wednesday 28 October 2009 3:55:02 pm Thom Brown wrote:
> 2009/10/28 Adrian Klaver :
> > - "Guillaume Lelarge" wrote:
> >> Le mercredi 28 octobre 2009 à 15:13:06, Thom Brown a écrit :
> >> > Similarly: "Fix encoding handling in binary input function of xml
> >> > type." What was the problem
2009/10/28 Alvaro Herrera :
>
> If anyone (you?) wants to step up and produce the document you request,
> it'll probably be linked to. But please do not request the current
> development team to work on it, because most of them are overloaded
> already (or have other reasons not to).
I can unders
On Wed, 28 Oct 2009, Thom Brown wrote:
All we have are a summary of changes. We can find out all the
information if we do plenty of searching of mailing lists and comparing
old and new documentation, but obviously this can be off-putting and is
duplicated for everyone who wants to participate
62 matches
Mail list logo