I stopped using autovacuum months ago because of similar problems
(version 8.1.4). Because we do some major inserts and updates about four
times a day, there were a few tables that I didn't want autovacuumed.
Even after I turned autovacuum off for these tables it still tried to
vacuum them whil
Alvaro Herrera wrote:
FYI, in 8.2 and up the Xid wraparound problem is considered on a table
by table basis, which means that only the tables that have not been
vacuumed recently need to be vacuumed. The need for database wide
vacuuming is gone.
That's good.
Time to start VACUUM FULL ANAL
Andrew Sullivan wrote:
On Fri, Jun 01, 2007 at 01:27:14PM -0700, Ron St-Pierre wrote:
imp=# select age(datfrozenxid) from pg_database where datname = 'imp';
age
1571381411
(1 row)
Time to start VACUUM FULL ANALYZE over the weekend.
I guess this come
Harpreet Dhaliwal wrote:
Hi,
I keep getting this duplicate unique key constraint error for my
primary key even
though I'm not inserting anything duplicate. It even inserts the
records properly
but my console throws this error that I'm sure of what it is all about.
Corruption of my Primary Ke
There's likely someone here that can help you, if you can give us some
more info. To start with, did the uninstall even work?
Ron
Tom Allison wrote:
Ran into a problem.
I hosed up postgresql by deleting the data directory.
So I thought I would just uninstall and reinstall postgres using
De
carter ck wrote:
> Hi all,
>
> I was trying to create function in postgres, but it returns error mentioning
> the language is NOT defined.
>
> The function is as following:
>
> CREATE OR REPLACE FUNCTION test_word_count(TEXT, TEXT) RETURNS INTEGER AS $$
> DECLARE
> d_word ALIAS FOR $1;
> d_phras
Craig White wrote:
I wrote a little script to individually back up table schemas, table
data and then vacuum the table and it works nicely but I wanted a way to
query a database and get a text file with just the table names and
cannot figure out a way to do that.
my script looks like this...
(al
A. Kretschmer wrote:
am Tue, dem 04.12.2007, um 20:19:29 -0800 mailte pc folgendes:
Hi,
How to redirect the output of an sql command to a file?
Thanks in advance
within psql you can use \o , from the shell you can use this:
[EMAIL PROTECTED]:~$ echo "select now()" | psql test > now.
Thomas Hart wrote:
Andrew Sullivan wrote:
I don't think top posting is always the crime it's made to be (and I
get a
little tired of lectures to others about it on these lists).
A
I agree. Obviously there is convention, and I will post in the style
generally accepted in the list, but to
Unfortunately there isn't a current version of Joshua Drake's book out
there (which I do own along with PostgreSQL Essential Reference by
Stinson), so alternatively, can anyone recommend a good DBA book
outlining best practices, physical design, etc? I would like something
that is relevant to P
Olexandr Melnyk wrote:
Joe Celko's "SQL Programming Style" is a good not vendor-specific book.
As for PostgreSQL-specific books, I can only speak for one I read
before: Bruce Momjian's "PostgreSQL: Introduction and Concepts". It is
a good introductionary book, although
it isn't based on Postgr
I'm sure that others have solved this but I can't find anything with my
(google and archive) searches. I need to retrieve data where the text
field is within a certain range e.g.
A-An
Am-Bc
Bc-Eg
Yi-Zz
Does anyone know of a good approach to achieve this? Should I be looking
into regular e
Thanks Richard and Joshua, I had no idea that BETWEEN worked for text.
SELECT *
FROM Your_table AS YT
WHERE YT.text_field BETWEEN 'Aa' AND 'An';
postgres=# select * from test where test between 'A' and 'An';
test
--
A
Ab
Ac
(3 rows)
Ron
Ron St
'A' and test <'Am';
"A"
"Ab"
"Ac"
select * from test where test >= 'Am' and test <'Bc';
"Amz"
"Az"
The end will be tricky because "" is not < "zz" so you will need
the la
Eyinagho Newton wrote:
Hiya Everyone,
Can anyone explain how postgreSQL reads from a text
file into tables already created in PostgreSQL?
I am also checking the thread in the Forum just to see
if someone has written about it in the past.
Thanks.
Newton
Do you mean when you read it in using the C
John Browne wrote:
Ok, I'm designing a new database for work, and I have run across a
situation where a "conditional relationship" makes sense. Here is a
*simplified* example of what I'm talking about:
tb_address_data_us
address_id
addr1
addr2
city
state
zip_code
tb_address_data_ca
address_id
addr
I have a simple function which I use to set up a users' expiry date. If
a field in a table contains an interval then
this function returns a timestamp some time in the future (usually two
weeks), null otherwise. I can't pass the
interval from the table into a variable properly within the function
Michael Fuhr wrote:
On Mon, Nov 08, 2004 at 04:15:34PM -0800, Ron St-Pierre wrote:
SELECT INTO exptime current_timestamp + INTERVAL ''intval'';
You're using the literal value 'intval' instead of its value, thus
the syntax error.
Of course, I should hav
Joachim Zobel wrote:
Hi.
I can't be the first to think about this. There is a million online
shops out there, which all more or less have the same database design.
Has anybody thought about creating generic reusable/customizable designs
for such cases?
Thx,
Joachim
Check out your favourite boo
I'm sure that this has been asked before but I can't find any reference to it
in google, and the search facility on postgresql.org is currently down.
I have a large number of entries (possibly 10,000+) in an XML file that I need
to import into the database (7.4 on Debian) on a daily basis. Does
scope of the list, I
wasn't sure whether or not there were postgres modules to deal with this.
Thanks for pointing me to possible solutions.
Ron
> - Original Message -
> From: "George Pavlov" <[EMAIL PROTECTED]>
> To: "Ron St-Pierre" <[EMAIL PRO
We received the following error on our development server this morning
(postgresql 7.4.1, debian woody):
org.postgresql.util.PSQLException: ERROR: schema "customer" does not exist
When I login to postgres it looks as if the other schemas are okay, but the
customer schema is gone. I have a back
> - Original Message -
> From: "Michael Fuhr" <[EMAIL PROTECTED]>
> To: "Ron St-Pierre" <[EMAIL PROTECTED]>
> Subject: Re: [GENERAL] Schema is Missing
> Date: Fri, 10 Mar 2006 11:27:54 -0700
>
>
> On Fri, Mar 10, 2006 at 12
Douglas McNaught wrote:
rstp <[EMAIL PROTECTED]> writes:
pg_config is telling us that we are running version 7.3.6-RH, but when
we start psql it shows that we are running 8.1.4 (which is the correct
version).
[EMAIL PROTECTED] bin]$ pg_config --version
PostgreSQL 7.3.6-RH
[EMAIL PROTEC
Hi, I've been trying to see whether or not autovacuum is vacuuming all
of my tables, and how often (for my peace of mind). I can see that it is
running, but I don't know what it's doing. There are a handful of key
tables in our database which suffer quite a bit if their not vacuumed
regularly (
Hi, I'm having a problem with one of my functions, where I delete all
rows containing a particular date and then re-insert a row with that
same date. When I try this I get a constraint error. This just started
recently, after upgrading our database from 7.4 to 8.1.4 (now on RH EL).
here's the
Michael Fuhr wrote:
On Wed, Aug 09, 2006 at 10:33:02AM -0700, Ron St-Pierre wrote:
ERROR: duplicate key violates unique constraint "systemcounts_pkey"
CONTEXT: SQL statement "INSERT INTO dm.systemCounts (updateDate) VALUES (
$1 ::date)"
PL/pgSQL function "upda
Greg Sabino Mullane wrote:
The database contains several schemas and excluding "comment_archive" by
moving it to different schema doesn't sound very convenient. pg_dump
doesn't have an option to dump multiple schemas at once.
Are there any working "-X" patches for pg_dump or does anyone have oth
On a daily basis I place a lot of data into the empty table dailyList,
and from that data update certain fields in currentList. I thought that
using a function would be a good way to do this(?). However I get the
following error when I run updateCurrentData():
ERROR: set-valued function calle
Stephan Szabo wrote:
On Wed, 17 Dec 2003, Ron St-Pierre wrote:
On a daily basis I place a lot of data into the empty table dailyList,
and from that data update certain fields in currentList. I thought that
using a function would be a good way to do this(?). However I get the
following error
I wish to insert data into a table from a very large text file (from a
cron script) using COPY. However if the lName (TEXT), fName(TEXT),
workDate(DATE) already exist I don't want to insert data and just want
to move onto the next record. Is there any way I can tell my bash
script/COPY to ignor
ezra epstein wrote:
Aother head banger for me.
Below is a complete example of the code
Using Postgres 7.4,
the function "test" gets this: psql:temp3.sql:10: ERROR: syntax error
at or near "%" at character 135
the function "test2" gets this: psql:temp3.sql:10: ERROR: syntax error
at or nea
Rick Gigger wrote:
I am running a few web based applications with postgres on the
backend. We have a few app servers load balanced all connecting to a
dedicated postgres server. As usage on the applications increases I
want to monitor my resources so that I can anticipate when I will hit
bot
I am using postgres 7.3.4 and need to be able to determine which
database a query is being run in (from a script). pg_database lists
databases but doesn't tell me which one is currently active. Is there a
query I can use along the lines of:
UPDATE tblUpdates SET xxx=1234 WHERE pg_current = TRUE;
Eric Ridge wrote:
On Feb 13, 2004, at 6:05 PM, Ron St-Pierre wrote:
I am using postgres 7.3.4 and need to be able to determine which
database a query is being run in (from a script). pg_database lists
databases but doesn't tell me which one is currently active. Is there
a query I ca
Alexander Cohen wrote:
How would i go about changing a databases encoding? Is this at all
possible?
There does not seem to be much i can with ALTER DATABASE except change
its name!
You could try to:
-pg_dump the database to file,
-drop the database,
-recreate the database with unicode encoding
Igor Kryltsov wrote:
Hi,
I have table:
# \d category;
category_id | integer| not null default
nextval('public.category_category_id_seq'::text)
category_name | character varying(100) | not null
Indexes: category_pkey primary key btree (category_id)
My goal is to remove sequence
Phil Campaigne wrote:
Hello,
I originally installed postgresql as root user and now I am setting up
a development environment with cvs and a java ide and tomcat. I have
everything with the exception of postgresql integreted using a
non-root user.
THe process I am using is to logon as postges a
Phil Campaigne wrote:
Hi Ron,
I had a couple of questions on your instructions:
1. what is this for?
>#make install-all-headers
According to the docs you need it if you are going to create your own
functions, however the documentation is a bit *vague*. "If you plan to
do any server-side program
CSN wrote:
Is it possible to have "less"-type paging with psql's
\s command? Or other ways like display the last 50
commands backwards?
__
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html
--
Matthias Teege wrote:
Moin,
I try to concat values from three fields in a function like this:
create or replace function
fconcat_name(varchar, varchar, varchar) returns varchar as '
declare
ttl alias for $1;
vnm alias for $2;
nme alias for $3;
begin
return ttl || '' '' || vnm || ''
Whenever I run certain functions, such as the example below, the output
is either displayed in the terminal or emailed to be by cron, depending
on how I run it. Is there any way I can re-write the function, set some
psql parameters, etc, to ensure that the results aren't displayed? I've
check
Ivan Sergio Borgonovo wrote:
what's wrong with this?
create type tSession
as ( ty_found boolean, ty_Session char(32) );
create or replace function GetSessionID( integer )
returns tSession as '
declare
thisSession tSession;
begin
--HERE!!!
thisSession := ( ''t
Ron St-Pierre wrote:
Ivan Sergio Borgonovo wrote:
what's wrong with this?
create type tSession
as ( ty_found boolean, ty_Session char(32) );
create or replace function GetSessionID( integer )
returns tSession as '
declare
thisSession tSession;
begin
--HERE!!!
t
Ivan Sergio Borgonovo wrote:
On Tue, 27 Apr 2004 10:12:13 -0700
thisSession := ( ''t'', md5( now( ) || rand( ) ) );
- md5 takes TEXT as an argument, not a numeric type
Since it works you surely fixed my code but this should't be an issue
since I tried
test1=# select md5( now( ) || r
Richard Huxton wrote:
Ron St-Pierre wrote:
I am trying to use a sequence value in a function but I keep getting
an error message:
WARNING: Error occurred while executing PL/pgSQL function
correctaddress
WARNING: line 8 at SQL statement
ERROR: column "addressid" is of ty
I found this error in /var/log/messages yesterday after a cron job
wouldn't complete:
STATEMENT: ALTER TABLE victoria.eodData DROP COLUMN tickDate;
ERROR: tables can have at most 1600 columns
STATEMENT: ALTER TABLE victoria.eodData ADD COLUMN tickerID INTEGER;
ERROR: tables can have at
Tom Lane wrote:
Alvaro Herrera <[EMAIL PROTECTED]> writes:
On Sun, Jun 27, 2004 at 11:11:32AM -0700, Ron St-Pierre wrote:
STATEMENT: ALTER TABLE victoria.eodData DROP COLUMN tickDate;
ERROR: tables can have at most 1600 columns
STATEMENT: ALTER TABLE victoria.eodData ADD COLUMN ti
We're developing a java app and are using postgres as the database. On
our dev server I started the app, closed it, but the java process was
still open so I killed it, which caused the above error. I've had to do
this in the past but have not had this happen before. I've searched the
archives a
Richard Huxton wrote:
An "update or insert" would be useful sometimes, but it's not always
necessary. Indeed, if I find I don't know whether I'm adding or
updating something I take a long hard look at my design - it ususally
means I've not thought clearly about something.
Can you give an actual
Richard Huxton wrote:
Ron St-Pierre wrote:
Richard Huxton wrote:
An "update or insert" would be useful sometimes, but it's not always
necessary. Indeed, if I find I don't know whether I'm adding or
updating something I take a long hard look at my design - it
ususal
Greg Stark wrote:
Ron St-Pierre <[EMAIL PROTECTED]> writes:
BTW these updates do take longer than we'd like so I would appreciate more
input on how this setup could be redesigned.
Where is the input coming from?
One option is to batch changes.
Something like
update current_
Darkcamel wrote:
Hello all,
I am new to postgres and don't really understand how the database is
set-up. I am very fluent with mysql and sql2000, but postgres is new to
me. If anyone can point me to some good links I would appreciate it very
much.
Thanks,
Darkcamel
Eduardo S. Fontanetti wrote:
How can I do a test if my dumping is really working, I
can't apply the dump in my database, because it will
overwrite a lot of data. I was thinking about restore
in another database name, but I can't, it always
restore on the original database.
Somebody have a cooking r
You can also try
pgsql.ru
Ron
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Peter Eisentraut wrote:
Ron St-Pierre wrote:
the line in the sql script to
SELECT * FROM myFunction() \o /dev/null;
output from this is suppressed. HOWEVER, I get an error when it tries
to process the next line;
psql:/usr/local/pgsql/quiet.sql:2: ERROR: syntax error at or near
"SELEC
Ron St-Pierre wrote:
We have a web based application with data that is updated daily. The
biggest bottleneck occurs when we try to update
one of the tables. This table contains 58,000 rows and 62 columns, and
EVERY column is indexed. Every column is
queryable (?) by the users through the web
Tom Lane wrote:
Ron St-Pierre <[EMAIL PROTECTED]> writes:
One question about redirecting VACUUMs output to file though. When I run:
psql -d imperial -c "vacuum full verbose analyze;" > vac.info
vac.info contains only the following line:
VACUUM
I've been unable to ca
oops, sent this to performance by mistake.
Jeffrey W. Baker wrote:
All these replies are really interesting, but the point is not that my
RAIDs are too slow, or that my CPUs are too slow. My point is that, for
long stretches of time, by database doesn't come anywhere near using the
capacity of the
Taber, Mark wrote:
I have Postgres 8.0-beta2 set up on two machines (one Windows 2000
Server, the other Windows XP Pro); I have Postgres up and running as a
service on both machines, no problem. I’m even able to go into psql
and putz around. However, I am not able to log on remotely using
pgAdm
I want to create a constraint that's a little different, but don't know
how to implement it. I have an intermediate table with a compound PK and
a boolean 'ysnDefault' column:
comanyID INTEGER REFERENCES companies,
assocationID INTEGER REFERENCES associations,
ysnDefault BOOLEAN
I
Tom Lane wrote:
Ron St-Pierre <[EMAIL PROTECTED]> writes:
I just want to constrain the ysnDefault field to only allow one TRUE
value for any companyID/associationID pair, with no restrictions on the
number of FALSES.
You could do that with a partial unique index. There is an example
Greg Stark wrote:
Ron St-Pierre <[EMAIL PROTECTED]> writes:
This is not quite what I need. I need to create a constraint to allow only
-one- of
company<->association<->default(=true) value
but any number of
company<->association<->default(=false) va
63 matches
Mail list logo
