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 d...@archonet.com 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
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
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 sure
On Tue, Oct 27, 2009 at 6:31 PM, Alvaro Herrera
alvhe...@commandprompt.comwrote:
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.
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
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]
Statistically
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 database
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 term2
FROM
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
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
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 gryz...@gmail.com:
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
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) (actual
On Wednesday 28 October 2009 6:46:13 am Thom Brown wrote:
2009/10/28 Grzegorz Jaśkiewicz gryz...@gmail.com:
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
2009/10/28 Adrian Klaver akla...@comcast.net:
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
On Wed, Oct 28, 2009 at 4:50 AM, Ivan Sergio Borgonovo
m...@webthatworks.it 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
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 alvhe...@commandprompt.com
wrote:
JC Praud escribió:
- Last night the database locked. pg_log full of messages about insert
into
the mother table waiting for a lock.
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:
Viktor Rosenfeld listuse...@googlemail.com 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.
JC Praud escribió:
On Wed, Oct 28, 2009 at 12:29 AM, Alvaro Herrera alvhe...@commandprompt.com
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
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
listuse...@googlemail.com 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
On Wed, 28 Oct 2009 10:12:19 -0500
Peter Hunsberger peter.hunsber...@gmail.com 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
On Tue, Oct 27, 2009 at 6:29 PM, Alvaro Herrera
alvhe...@commandprompt.com 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
Jaime Casanova jcasa...@systemguards.com.ec writes:
On Tue, Oct 27, 2009 at 6:29 PM, Alvaro Herrera
alvhe...@commandprompt.com 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
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:
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
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
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
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.0
(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 john.pen...@stjude.org 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
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
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 brooks.lyre...@gmail.com:
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
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 brooks.lyre...@gmail.com:
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
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 Huxton
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 list
Brooks Lyrette brooks.lyre...@gmail.com 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
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
Thanks!
kynn
On Tue, Oct 27, 2009 at 4:02 PM, Richard Huxton d...@archonet.com 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 tablename
Using plain SQL, the closest I can think of are the
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 brooks.lyre...@gmail.com:
Hello All,
I'm
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-generalm=112612299412819w=2
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes
On Wed, Oct 28, 2009 at 1:05 PM, Brooks Lyrette
brooks.lyre...@gmail.com 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
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
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 gsm...@gregsmith.com 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
Le mercredi 28 octobre 2009 à 15:13:06, Thom Brown a écrit :
2009/10/28 Adrian Klaver akla...@comcast.net:
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.
Viktor Rosenfeld listuse...@googlemail.com 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
Le mercredi 28 octobre 2009 à 23:30:01, Adrian Klaver a écrit :
- Guillaume Lelarge guilla...@lelarge.info 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
- Guillaume Lelarge guilla...@lelarge.info wrote:
Le mercredi 28 octobre 2009 à 23:30:01, Adrian Klaver a écrit :
- Guillaume Lelarge guilla...@lelarge.info wrote:
Le mercredi 28 octobre 2009 à 15:13:06, Thom Brown a écrit :
Similarly: Fix encoding handling in binary input
2009/10/28 Adrian Klaver akla...@comcast.net:
- Guillaume Lelarge guilla...@lelarge.info 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
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 akla...@comcast.net:
- Guillaume Lelarge guilla...@lelarge.info wrote:
Le mercredi 28 octobre 2009 à 15:13:06, Thom Brown a écrit :
Similarly: Fix encoding handling in binary input function of xml
2009/10/28 Alvaro Herrera alvhe...@commandprompt.com:
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
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
62 matches
Mail list logo
