On 10/8/14, 2:24 PM, Israel Brewster wrote:
Agreed. and no, I don't need all the data. But pg_dump doesn't give me an
option to, say, only grab the last week of data.
You might want to check out pg_sample. It lets you get a referentially
consistent sample of a database. You specify the numb
On 1/17/14, 11:02 AM, Tom Lane wrote:
Jeff Ross writes:
I had to move our production database to a new server with virtually
identical hardware. At the same time I went to 9.3.2 from 9.3.
Queries on the old server (nirvana) run many magnitudes faster than on
the new server (dukkha).
The two
.depesz.com/s/fiHh
More complex queries that run in a couple hundred ms on the old server
are taking about 9 seconds to complete on the new server, and postgres
is using 90+% of cpu for the entire time. I've vacuumed analyzed the
new server but that didn't seem to make a difference.
Thanks for any insight!
Jeff Ross
On 11/6/13, 12:26 PM, Jeff Ross wrote:
On 11/6/13, 11:32 AM, Jeff Janes wrote:
On Wed, Nov 6, 2013 at 9:40 AM, Jeff Ross <mailto:jr...@wykids.org>> wrote:
_postgresql@nirvana:/var/postgresql $ cat start_hot_standby.sh
#!/bin/sh
backup_label=wykids_`date +%Y-%m-%d`
#r
On 11/6/13, 11:32 AM, Jeff Janes wrote:
On Wed, Nov 6, 2013 at 9:40 AM, Jeff Ross <mailto:jr...@wykids.org>> wrote:
_postgresql@nirvana:/var/postgresql $ cat start_hot_standby.sh
#!/bin/sh
backup_label=wykids_`date +%Y-%m-%d`
#remove any existing wal files on th
/postgresql.5432
The standby server fails to start with the following:
2013-11-06 07:50:23.436056500 <%> LOG: database system was interrupted;
last known up at 2013-11-06 07:47:07 MST
2013-11-06 07:50:23.495849500 <%> LOG: entering standby mode
2013-11-06 07:50:23.495854500 <%> LOG: in
Maybe some of these folks can chime in?
http://cds.u-strasbg.fr/
Simbad (and I think VisieR) runs on PostgreSQL. A friend of mine is a
grad student in astronomy and he told me about them.
Jeff Ross
On 10/1/13 3:49 PM, Mark Jones wrote:
Hi all,
We are currently working with a customer who
On 11/19/12 12:58, Tom Lane wrote:
Jeff Ross writes:
Hi,
I'm having a hard time figuring out how to use split_part with a
carriage return (hex 0a) as the delimiter.
Um, 0x0a is line feed last I checked. But you should be able to write
the literal as E'\r' (if you wanted CR) o
Hi,
I'm having a hard time figuring out how to use split_part with a
carriage return (hex 0a) as the delimiter.
Would someone please offer me a clue?
Thanks!
Jeff Ross
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
On 7/11/12 5:13 PM, Adrian Klaver wrote:
On 07/11/2012 04:02 PM, Jeff Ross wrote:
On 7/11/12 3:52 PM, Adrian Klaver wrote:
Is there an index on this table?
If so have you tried a REINDEX?
Here's the table definition:
jross@nirvana:/home/jross $ psql wykids
psql (9.1.4, server
On 7/11/12 4:24 PM, Marcin Mańk wrote:
On Tue, Jul 10, 2012 at 10:06 PM, Jeff Ross <mailto:jr...@wykids.org>> wrote:
2012-06-19 15:37:36.283752500 LOG: statement: update
survey_response set srv_resp_submitted = now() where
srv_resp_srv_id = 2 and srv_resp_pp_i
On 7/11/12 3:52 PM, Adrian Klaver wrote:
On 07/11/2012 02:41 PM, Jeff Ross wrote:
On 7/11/12 2:07 PM, Adrian Klaver wrote:
On 07/11/2012 07:01 AM, Jeff Ross wrote:
On 7/10/12 8:39 PM, Adrian Klaver wrote:
On 07/10/2012 07:30 PM, Jeff Ross wrote:
On 7/10/12 6:21 PM, Adrian Klaver wrote:
On
On 7/11/12 2:07 PM, Adrian Klaver wrote:
On 07/11/2012 07:01 AM, Jeff Ross wrote:
On 7/10/12 8:39 PM, Adrian Klaver wrote:
On 07/10/2012 07:30 PM, Jeff Ross wrote:
On 7/10/12 6:21 PM, Adrian Klaver wrote:
On 07/10/2012 01:06 PM, Jeff Ross wrote:
Hi all,
Thanks for any and all ideas
On 7/10/12 8:39 PM, Adrian Klaver wrote:
On 07/10/2012 07:30 PM, Jeff Ross wrote:
On 7/10/12 6:21 PM, Adrian Klaver wrote:
On 07/10/2012 01:06 PM, Jeff Ross wrote:
Hi all,
Thanks for any and all ideas!
For your initial attempt everything was done in one session?
All the inserts were
On 7/10/12 6:21 PM, Adrian Klaver wrote:
On 07/10/2012 01:06 PM, Jeff Ross wrote:
Hi all,
I have an anomaly on my hands that I'm at a loss to understand.
We recently ran a small survey where participants were required to
answer all the questions. After validation for skipped questions
33 | 2012-06-19 15:33:05.547012 | 2012-06-19 15:38:16.724393
5595 | 2 | 19361 | 2012-06-19
15:12:39.37469 | 2012-06-19 18:26:00.391711 | 2012-06-19 18:38:47.068152
(3 rows)
After finding these anomalies, I cut and pasted the insert statements
from the logs i
On 08/30/11 18:03, Fujii Masao wrote:
On Wed, Aug 31, 2011 at 5:51 AM, Jeff Ross wrote:
Is there a setting in this or something else that I should tweak so this
query can complete against the replica? Google turned up some threads on
the error code associated with the error but I didn't
inst the replica? Google turned up some threads
on the error code associated with the error but I didn't find much else
that seems applicable.
Thanks,
Jeff Ross
Wyoming Children's Action Alliance
Cheyenne, Wyoming
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 02/01/11 08:55, Herouth Maoz wrote:
As a result of my recent encounter with table bloat and other tuning issues
I've been running into, I'm looking for a good resource for improving my tuning
skills.
My sysadmin ran into the following book:
PostgreSQL 9.0 High Performance, by Gregory Smith
On 01/31/11 17:13, Tom Lane wrote:
Jeff Ross writes:
In the meantime, I now have 242 16MB files in pg_xlog/. The
archive_status directory in pg_xlog/has a matching .done file for all
but the most recent of those pg_xlog files.
What have you got checkpoint_segments set to?
I had
t found it yet.
Thanks,
Jeff Ross
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
version from Digital Stratum doesn't work and you (or anyone else
for that matter) would like my slightly modified version let me know off
list and I'll get it to you.
Jeff Ross
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscript
and shows me some, um, interesting things in my database that probably
need addressing.
Warm regards from snowy Wyoming!
Jeff Ross
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 10/05/10 10:35, Tom Lane wrote:
Jeff Ross writes:
Thanks for the hint, Greg! It was indeed the .psql_history file.
Although I'm using the default history size, the file was over 5MB in
size and full of a table dump. I truncated the file and no more delay
quitting psql.
Huh ...
On 10/04/10 15:55, Greg Sabino Mullane wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
This is more of an odd anoyance than anything, but for the past month or
so when I \q out of psql I have to wait 20-25 seconds for the return to
the shell prompt. This is the only copy of psql run
.4.4 on OpenBSD and
primarily PostgreSQL is powering Drupal.
Searching Google turned up nothing. I can live with it but I'd be more
interested in fixing it.
Thanks!
Jeff Ross
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to you
Tom Lane wrote:
Jeff Ross writes:
To get the output I want above, I'd think I'd need to do a left outer join
like this:
jr...@wykids localhost# select ed_cat_name as "Level", pp_ed_cat_subject as
"Subject", pp_ed_cat_institution as "Institution" fr
but that only gives me this:
Level | Subject| Institution
---++-
Bachelors | Elementary Education/Fine Arts |
(1 row)
In fact, every join combination (left|right|full|inner|outer) I've tried gives
me the s
On 04/25/10 14:20, Tom Lane wrote:
Jeff Ross writes:
Now I'm *really* confused. I thought the table structure I created at
the beginning of the function was where the results would be returned
to.
Uh, you're using that as the destination for the FOR loop's SELECT.
What
On 04/25/10 12:32, Tom Lane wrote:
Jeff Ross writes:
I'm trying to write my first plpgsql function and I'm running into a
problem that may or may not have to do with a coalesce statement.
No, it's not the coalesce ...
When I try to run this I get the following error:
jr..
e trs_tr_level_core_area_id = 6 and
trs_tr_level_pp_id = $45 ) is not null then 't'::boolean else
'f'::boolean end as $46 , case when (select sum(trs_tr_level) from
trainers_trainer_levels where trs_tr_level_core_area_id = 7 and
trs_tr_level_pp_id = $47 ) is not null then 't'::boolean else
'f'::boolean end as $48 , case when (select sum(trs_tr_level) from
trainers_trainer_levels where trs_tr_level_core_area_id = 8 and
trs_tr_level_pp_id = $49 ) is not null then 't'::boolean else
'f'::boolean end as $50 from people join trainers on $1 = tr_pp_id
join trainers_trainer_dates as ttd1 on $1 = trs_tr_date_pp_id join
trainer_dates on tr_date_id = trs_tr_date_id WHERE NOT EXISTS ( SELECT *
FROM trainers_trainer_dates as ttd2 where ttd1.trs_tr_date_pp_id =
ttd2.trs_tr_date_pp_id and ttd1.trs_tr_date_recorded <
ttd2.trs_tr_date_recorded ) and tr_date_name not in ('Application
Date','Last Updated Date') and $1 = $51
CONTEXT: SQL statement in PL/PgSQL function "view_all_trainers" near
line 151
I'm sure this is something simple but I've been staring at it so long
now I can't see the forest for the trees.
Thanks!
Jeff Ross
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
on to figure this out.
Jeff
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jeff Ross
Sent: Thursday, March 11, 2010 11:37 AM
To: PostgreSQL
Subject: [GENERAL] Joining one-to-one and one-to-many tables
I'm stumped
into a
hitch in the gitalong there.
I'm sure there's a better way to accomplish what I'm trying to get here
(especially since this really doesn't quite *work*) but I can't see it. This
type of query has become important for me to get because more and more we are
movi
Greg Smith wrote:
Jeff Ross wrote:
I think I'm doing it right. Here's the whole script. I run it from
another server on the lan.
That looks basically sane--your description was wrong, not your
program, which is always better than the other way around.
Note that everything your
Greg Smith wrote:
Jeff Ross wrote:
pgbench is run with this:
pgbench -h varley.openvistas.net -U _postgresql -t 2 -c $SCALE
pgbench
with scale starting at 10 and then incrementing by 10. I call it
three times for each scale. I've turned on logging to 'all' to try
and
ll not have this problem, however, the amd64 kernel will not run on this particular server. I think that means that I'm going to be shopping for an Opteron based server before long.
Thanks to all!
Jeff Ross
Tom Lane wrote:
Jeff Ross writes:
Running a simple select only pgbench test against it will fail with an out of
memory error as it tries to vacuum --analyze the newly created database with
750 tuples.
Better look at the "ulimit" values the postmaster is started with;
you sh
d on request of
size 67108864.
Jeff Ross
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
mation to help someone point me in the right
direction. Even before I get to the out of memory error 4000 tps seems very
low for what I think this hardware should be capable of doing.
Thanks,
Jeff Ross
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Tom Lane wrote:
Jeff Ross writes:
Tom Lane wrote:
heap_update is broken. Details left as an exercise for the reader
Well, as the reader that started this all ;-) should I be worried?
Should I do a pg_dump and reinstall? Roll back to 8.3.7? Or just
relax, don't
Tom Lane wrote:
Greg Stark writes:
On Sun, Aug 23, 2009 at 7:34 PM, Tom Lane wrote:
Urgh. I bet that's where the problem is then. Some path is failing
to clear that bit, or maybe there's a race condition that allows it
to become set incorrectly (ie right after somebody else adds a tu
Greg Stark wrote:
On Sun, Aug 23, 2009 at 5:37 PM, Jeff Ross wrote:
pg_clog is 32K. �I've put it at http://www.openvistas.net/pg_clog
Sorry, I'm getting a 404
Oops--fixed now.
Jeff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make chang
Greg Stark wrote:
On Sun, Aug 23, 2009 at 5:02 PM, Greg Stark wrote:
On Sun, Aug 23, 2009 at 2:18 PM, Jeff Ross wrote:
Incidentally, may as well ask the usual questions:
And just for reference, what does pg_controldata print?
_postgre...@heinlein:/var/postgresql $
pg_controldata
Greg Stark wrote:
On Sun, Aug 23, 2009 at 2:18 PM, Jeff Ross wrote:
I had to modify your query slightly to make it run--hope I got what you are
after!
select (h).* from (select
heap_page_items(get_raw_page('pg_namespace',0)) as h) as x;
http://www.openvistas.net/pagein
Greg Stark wrote:
On Sun, Aug 23, 2009 at 4:06 AM, Jeff Ross wrote:
Greg Stark wrote:
Actually, I wonder if this isn't more likely to show the problem -- it
would explain why *all* your tables are showing up with duplicates
rather than just one.
select xmin,xmax,ctid,oid,*
Greg Stark wrote:
On Sat, Aug 22, 2009 at 9:31 PM, Jeff Ross wrote:
I browsed through the system catalogs but haven't found anything yet that
can shine some light on this.
Actually, I wonder if this isn't more likely to show the problem -- it
would explain why *all* your
Greg Stark wrote:
On Sat, Aug 22, 2009 at 9:31 PM, Jeff Ross wrote:
Hi,
I recently upgraded to 8.4 and everything went great. �All databases are
working as they are supposed to, no problems seen.
Today, however, I did a \d on a database and was surprised to see sets of 5
identical table
e light on this.
System is running OpenBSD -current.
Thanks,
Jeff Ross
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
service_id,
people.name,
people.address,
test.price_serv,
null as "total"
from test, people where test.person_id = people.person_id
union all
select
null as person_id,
null as service_id,
null as name,
null as address
Joao Ferreira gmail wrote:
Hello all,
I need to print to a file a simple list of all the databases on my
postgresql.
I need to do this from a shell script to be executed without human
intervention
I guess something like:
su postgres -c 'psql ...whatever > /tmp/my_databases.txt'
but I don
Jeff Davis wrote:
On Tue, 2008-09-09 at 17:03 -0600, Jeff Ross wrote:
select
ts_date as "Transcript Date",
ts_expiration_date as "Current Expiration Date",
expiration_value as "Expiration Interval"
from transcript, t
ts_expiration_date != ts_date + interval 'expiration_value';
and I'm getting the following error:
ERROR: invalid input syntax for type interval: "expiration_value"
Is there a way to use the value in expiration_value for the interval?
Thanks,
Jeff Ross
--
Sent via
Martijn van Oosterhout wrote:
On Wed, Aug 13, 2008 at 10:47:17AM -0600, Jeff Ross wrote:
I'm a little confused about how to use a column alias in the where
clause of a query. I'm sure the answer is something simple, but I
haven't found anything searching through Google or f
s is provided, it
completely hides the actual name of the table or function; for example
given FROM foo AS f, the remainder of the SELECT must refer to this FROM
item as f not foo.".
Does this apply in the WHERE clause as well? If it doesn't how can I
refer to the results of the case sta
Scott Marlowe wrote:
On 9/18/07, Jeff Ross <[EMAIL PROTECTED]> wrote:
I'm using copy to insert a bunch of rows into a new table with a unique
primary key. Copy is correctly incrementing the primary key, but
apparently the sequence itself is never updated because when I go to
insert
s);
but that isn't working.
Thanks in advance for any help!
Jeff Ross
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Tom Lane wrote:
Jeff Ross <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
Hmm. Can you try that with \set VERBOSITY verbose so we can determine
where the error is being thrown from?
psql:/tmp/people.sql:79: ERROR: XX000: unrecognized node type: 655
LOCATION: _outValue, outfuncs.c:1707
Tom Lane wrote:
Jeff Ross <[EMAIL PROTECTED]> writes:
When psql begins loading this file, it throws the error:
psql:/tmp/people.sql:79: ERROR: unrecognized node type: 655
Hmm. Can you try that with \set VERBOSITY verbose so we can determine
where the error is being thrown from?
ERROR: relation "people_pp_id_seq" already exists
Line 79 of the file is the closing ");" at the end of the CREATE TABLE
statement.
The interesting thing is that this exact process works just fine to a
third machine also running 8.2.4--my entire pg_dumpall script runs
wit
Robert Fitzpatrick wrote:
Can anyone suggest how one might be able to do this? I want to be able
to generate an 8 character random password for users in their password
field. Perhaps through the default setting of the field or a trigger
function. I found the following, but is there anything that
r me under 8.2.3. I intend to fix a few
of its problems (it does fine until the backend goes away ;-) and
re-release it.
Thanks,
Jeff Ross
P.S. Apologies to the owner of the list--I inadvertently sent this to
the wrong address first.
---(end of broadcast)---
Desire Athow wrote:
I am looking for a email list management solution (can be a script, an
open source application etc), which uses Postgre SQL (AKA Postgres) as
database.
Can you let me know whether this rare bird exist?
---(end of broadcast)---
Vivek Khera wrote:
On Feb 16, 2007, at 4:13 PM, Andrew Kirkness wrote:
I am currently setting up a website and have PostGreSQL database I'm
using for the backend. I'm researching an open source Content
Management System that uses PostGreSQL. Do you have any recommendations?
You need to def
Tom Lane wrote:
Thanks for the reply, Tom.
Jeff Ross <[EMAIL PROTECTED]> writes:
This used to work before my upgrade to 8.2.1.
Which version were you using before?
8.1.x
The error the function now throws is:
ERROR: invalid regular expression: invalid backref
us ways I can call substring, but I
don't understand why this is throwing the error.
Any help would be greatly appreciated!
Jeff Ross
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
65 matches
Mail list logo
