>
> 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
>
> SELECT a, nextval('c') as b
> FROM table1
> ORDER BY a DESC LIMIT 5;
>
> I.e., I want to pick the 5 largest entries from table1 and show them
> alongside a new index column that tells the position of the entry. For
> example:
>
> a | b
>
> 82 | 5
> 79 | 4
> 34 | 3
> 12 | 2
> 11 |
After hours of search, I searched just some more and I think I found the
solution, hope it can be useful to someone else :
CREATE LANGUAGE plperlu;
CREATE FUNCTION test_perl_external (integer) RETURNS boolean AS $$
$filename = '/tmp/somefile';
if (-e $filename) { return true; }
Tom Lane wrote:
"Chris Spotts" writes:
many groups are you expecting in that query? Does the plan for the
array_agg query show hash or group aggregation?
GroupAggregate
Huh, there's no reason it should take much memory then. Maybe you've
found a m
> - type, and access into a varwidth array is O(n), so the sorting
> - step you've got here is O(n^2). It might help to use unnest()
> - instead of this handmade version of it ...)
>
> unnest() is 8.4 only, right?
>
> I'm actually probably just going to install R and use the median
> function fr
> -Original Message-
> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> Sent: Tuesday, July 21, 2009 12:16 PM
> To: Chris Spotts
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] array_agg crash?
>
> "Chris Spotts" writes:
> >> many groups
> >> How big were the arrays you were trying to push around here?
>
> > Like I'd said originally, there were no arrays that ended up being
> more 4
> > elements long - all integers. The vast majority of them were 1 or 2
> long.
>
> Hm, maybe the problem is not so much array size as too many arra
> killed
> > it after 15 minutes and no results...rather I tried to kill it, but
> it looks
> > like I'm going to have to -9 it...
>
> How big were the arrays you were trying to push around here? I tried
> interrupting a similar query and it came right back; but if you were
> building some really
> "Chris Spotts" writes:
> > LOG: 0: autovacuum launcher process (PID 10264) was terminated
> by
> > signal 9: Killed
>
> Looks like the OOM killer is loose on your system. Disable memory
> overcommit in the kernel and things will get better.
> h
I had one simple query that kept crashing the connection. It crashes after
several minutes.
Tried restarting, it still error'd at the same place.
Tried recreating the table it was selecting from, it still error'd at the
same place.
I rewrote the query with an ARRAY subselect and it finished fl
> > Details:
> >
> > In addition to the existing aggregate functions (avg, stddev etc),
> it would
> > be nice if postgres could return further information. For example,
> the
> > quartiles, percentiles, and median.
[Spotts, Christopher]
If you're interested in doing real stat work in postgres, tr
>
> > If I ran a select * from A where date1 >= '2009-07-02' and date1 <
> > '2009-07-15' then I would think it wouldn't check O.
>
[Spotts, Christopher]
I oversimplified this too much - but I figured out what was happening.
If you added the June table as well and added a separate NOT constrain
> -Original Message-
> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> Sent: Thursday, July 09, 2009 1:52 PM
> To: Chris Spotts
> Cc: 'postgres list'
> Subject: Re: [GENERAL] constraint checking on partitions
>
> "Chris Spotts" writes:
> &
postgres=# select version();
version
--
PostgreSQL 8.4.0 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
3.4.6 20060404 (Red Hat 3.4.6-8.0.1), 64-bit
Chris Spotts
Try moving your "as future"
SELECT
now() AT TIME ZONE 'America/Toronto',
CAST ((SELECT now() + '4d') AS TIMESTAMP) AT TIME ZONE
'America/Toronto' as future;
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Be
> >
> > Wouldn't you just be looking for something like:
> >
> > BEGIN;
> > EXECUTE 'insert into forums_readposts values ('...')';
> >EXCEPTION when unique_violation THEN
> >EXECUTE 'update forums_readposts set lastpostread =
> '...' ';
> > END;
> > The logic as i read you
On Thu, 2009-06-25 at 21:59 +0100, Greg Stark wrote:
> >> The transaction itself works flawlessly, but every once and awhile the data
> >> the it uploads from comes in flawed and we have to find a way to reset it.
>
> If you can automate the tests for the flaws you can do the whole
> transaction i
>
> Assuming that the data is mostly created from whole cloth each
> morning, it might do to have two dbs, and rename one to replace the
> other when you're ready. Gives you 20 or so hours to discover a screw
> up and still have the backup db before you toss it away to build the
> next day
For t
proc
started. If we had the xid of the long running transaction, is there a
better way to reset it right before that transaction happened? Restoring
the backup is a lengthy process because several of the tables that are
affected are rather large.
Chris Spotts
IMHO running queries on 23k'ish worth of rows isn't liable to stress any
reasonably modern server, likely several times over that shouldn't either
for simple "LIKE" searches.
What kind of growth are you expecting?
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsq
> > is around 250 lines.
>
> What I normally do for benchmarking of complex functions is to
> sprinkle the source with "raise notice '%', timeofday();" to figure
> out where the bottlenecks are. Following that, I micro-optimize
> problem queries or expressions outside of the function body in psql
>
> Is there any way to get count of docs & pages imported by date without
> resorting to selecting from a select:
[Spotts, Christopher]
If I understand you correctly..?
create table docs (id int8 primary key, imported_when timestamp );
create table pages (id int8 primary key, doc_id int8 not
> >> It's a classic story. I'm volunteering about one day per month for
> >> this project, learning SQL as I go. Priority was always given to
> the
> >> "get it working" tasks and never the "make it safe" tasks. I
> had/have
> >> grandiose plans to rewrite the whole system properly after I
> gra
> It's a classic story. I'm volunteering about one day per month for
> this project, learning SQL as I go. Priority was always given to the
> "get it working" tasks and never the "make it safe" tasks. I had/have
> grandiose plans to rewrite the whole system properly after I graduate.
> Unfort
>CREATE INDEX fecha_creacion_ordtrab ON orden_trabajo_cabecera USING btree
>((time_stamp_creacion::date));
>
>but in my postgresql 8.3 version i get this error:
>
>ERROR: functions in index expression must be marked IMMUTABLE
If your time_stamp_creacion is a timestamp with time
I just finished doing something very close to this - not quite once per
minute, but close. I started off with an array of integers and after about
a month of it, I'm having to redesign my way out of it. It would have
worked fine, but you just have to be sure that simple searches is all you're
eve
It sounds like you want a crosstab query. There is probably (I don't know
what version of postgres you're using) a contrib package called "tablefunc"
that includes the crosstab functions you're looking for.
_
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgre
A separate table for managing the relationships. One column for the manager
and one for employee.
Then you end up with a query like this.
Select field1,field2 FROM table1 inner join relationships on
table1.creator_user_id = relationships.employee WHERE relationships.manager
= ?
_
Fr
Could if be referencing the second IF..the one in your "END IF" that doesn't
have a semicolon after it...?
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of DaNieL
Sent: Wednesday, April 29, 2009 9:54 AM
To: pgsql-general@p
Not in regards to logging detail, but that function in general...
I'm pretty new to postgres, so I could be totally wrong in this, but I think
this thread
http://archives.postgresql.org/pgsql-performance/2008-03/msg00204.php
may pertain if you see some performance degradation with that trigger.
Li
That's a dead link for me.
-Original Message-
From: Alvaro Herrera [mailto:alvhe...@commandprompt.com]
Sent: Monday, April 06, 2009 12:42 PM
To: Chris Spotts
Cc: 'Tom Lane'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] copy from with trigger
Chris Spotts escribió:
Well that's a bummer, ok. Thanks.
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Sunday, April 05, 2009 10:27 PM
To: Chris spotts
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] copy from with trigger
Chris spotts writes:
> I'm trying to co
I'm trying to copy from a tab delimited file. The dates inside the file
are Unix timestamp style dates.
I thought the following script would do the trick, but it just gives me
an error saying
ERROR: invalid input syntax for type timestamp: "1238736600"
CONTEXT: COPY testtable line 1, column acq
Go figure, eh? It wasn't there when I installed the RHEL version. But
its all good.
Chris
On Sat, 2009-04-04 at 22:28 +0300, Devrim GÜNDÜZ wrote:
> On Sat, 2009-04-04 at 11:36 -0500, Chris spotts wrote:
> > Well, the only file that shows up is the uninstall file? Thats a
> >
Thanks, I got it installed from source and got uuid-ossp working. One
of the easier installs from source for a major app that I've done.
Appreciate the help.
Chris
On Sat, 2009-04-04 at 11:40 -0400, Tom Lane wrote:
> Chris spotts writes:
> > I’m brand new to Postgresql and wor
Well, the only file that shows up is the uninstall file? Thats a little
odd.
Looks like I'll be rebuilding from source on monday...joyous.
On Sat, 2009-04-04 at 11:40 -0400, Tom Lane wrote:
> Chris spotts writes:
> > I’m brand new to Postgresql and working on moving an
> > ex
I’m brand new to Postgresql and working on moving an existing large DB
into it. I’m trying to get uuid-ossp functions to work. I know the
UUID type is installed, but I need the generate functions. I’ve
installed postgresql-server and postgresql-contrib from the postgresql
yum repos (working with
37 matches
Mail list logo
