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
>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
> 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
> >> 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
>
> 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
> > 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
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
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
>
> 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
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
> >
> > 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
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
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
> -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:
> &
>
> > 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
> > 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
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
> "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
> 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
> >> 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
> -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
> - 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
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
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; }
>
> 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 |
>
> 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
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
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
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
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
> >
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
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
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ó:
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
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
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
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
37 matches
Mail list logo
