ot;default" options - what format does that
write? Should I have used -Fp to make a plain text backup but not
--inserts? Then it would be doing a COPY instead of an INSERT and maybe
that would be faster. Oh well.
Tomasz Ostrowski wrote:
On 2008-09-23 19:03, William Garrison wrote:
I ha
I found out about the quoting thing about 30 seconds after I made the
post. :) Thanks everyone who replied.
Douglas McNaught wrote:
On Tue, Sep 23, 2008 at 11:49 AM, William Garrison
<[EMAIL PROTECTED]> wrote:
In Postgresql 8.2.9 on Windows, you cannot rename a database if th
I have several .SQL files created from pg_dump, and I find that when I
feed them into psql that I get tons of foreign key errors because the
INSERT statements in the dump are not in the correct order. After
reading the docs, mailing lists, and googling, I see posts saying this
problem was fixe
In Postgresql 8.2.9 on Windows, you cannot rename a database if the name
contains mixed case.
To replicate:
1) Open the pgadmin tool.
2) Create a database named "MixedCase" (using the UI, not using a query
window or using PSQL)
3) Open a query window, or use PSQL to issue the following command
I have been optimizing my pg_restores (postgres 8.2.9 on Windows) and I
am confused by some of the results I get when combining various
command-line options.
The -c option for "clean" does not do DROP IF EXISTS statements, it just
does DROP. This results in an error if the object does not exi
Thanks so much!
So... if I am using pg_dump and pg_restore with a compressed backup,
then it is using COPY, correct? And I think that would follow a CREATE
TABLE statement as mentioned in the first link... so no WAL files written?
Greg Smith wrote:
On Fri, 12 Sep 2008, William Garrison
I know that PostgreSQL is slow at restoring databases. But there are
some tricks to use when speeding it up. Here is a brief list I compiled
from reading the docs and reading some forums. Is there a definitive
list of things to do?
* Turn off fsync
So it won’t flush after every commit
* Turn o
Thanks.
I notice that the link you provided says:
"Per best practices, my postgres data directory, xlogs and WAL archives
are on different filesystems (ZFS of course). "
Why is this a best practice? Is there a reference for that?
Greg Smith wrote:
On Mon, 8 Sep 2008, William Garr
We are using PostgreSQL 8.2.9 on Windows, and we are setting up some new
machines. We used to install PostgreSQL on C: and then we put the
tablespaces onto our SAN drive (Z:). When we tried to mount the
snapshots of the SAN we learned that they were useless since we only had
the tablespaces,
Coming from MS SQL server, if I ever change anything vital on a
production system, or do any kind of major hackery on my own, I wrap it
in a transaction first:
BEGIN TRANSACTION;
DELETE FROM vital_information WHERE primary_key = 10;
ROLLBACK TRANSACTION;
I then make sure that the result comes
--- Original message ------
From: William Garrison <[EMAIL PROTECTED]>
I am looking for records with duplicate keys, so I am running this query:
SELECT
fileid, COUNT(*)
FROM
file
GROUP BY
fileid
HAVING
COUNT(*)>1
The table has an index on fileid (non-unique index) so
When I attended the PostgreSQL East conference, someone presented a way
of doing this that they used for http://www.mailermailer.com/ and they
did this:
SET constraint_exclusion = on;
EXPLAIN
SELECT
*
FROM
test
WHERE
id = 7
AND id % 4 = 3
Their business layer then generated the "AN
I am looking for records with duplicate keys, so I am running this query:
SELECT
fileid, COUNT(*)
FROM
file
GROUP BY
fileid
HAVING
COUNT(*)>1
The table has an index on fileid (non-unique index) so I am surprised
that postgres is doing a table scan. This database is >15GB, and there
Thanks to everyone for for the myriad of informative replies on this.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
a hard-link to some other location?
Alvaro Herrera wrote:
William Garrison wrote:
1) I have a file system backup that *IS* consistent. So I should not
need any WAL files at all right?
It is consistent only if it was taken when the postmaster was down.
**update**
I got it working
Wait... there really is a pgfsck...? I just made that up as an example
of something I wanted. Great! And... how would I tell postgres to
start without using any indexes?
Martijn van Oosterhout wrote:
On Wed, Aug 27, 2008 at 01:45:43PM -0400, William Garrison wrote:
Are there any kind of
Alvaro Herrera wrote:
William Garrison wrote:
I have a PostgreSQL database on Windows Server 2003, and the database is
kept on a SAN that has the ability to make instantaneous snapshots.
Once I have made such a snapshot, I am unclear how to re-attach it to
another postgres database on
I have a PostgreSQL database on Windows Server 2003, and the database is
kept on a SAN that has the ability to make instantaneous snapshots.
Once I have made such a snapshot, I am unclear how to re-attach it to
another postgres database on another machine. Postgres seems to create
a directory
Craig Ringer wrote:
William Garrison wrote:
I fear I have a corrupted database, and I'm not sure what to do.
First, make sure you have a recent backup. If your backups rotate, stop
the rotation so that all currently available historical copies of the
database are preserved from n
I fear I have a corrupted database, and I'm not sure what to do.
Environment:
Windows Server 2003
8GB RAM
Dual processor, quad core 2.6Ghz
Postgres 8.2.3 (The IT dept wants to upgrade to 8.2.9, but they are
asking me what to do about this corrupt database before they proceed)
The
Is there an easy way to write one single query that can alternate
between ASC and DESC orders? Ex:
CREATE OR REPLACE FUNCTION GetSomeStuff(_start integer, _count integer,
_sortDesc boolean)
RETURNS SETOF text AS
$BODY$
SELECT
something
The dump is over 3GB. So there's no question this is it. I had a
feeling this would all come down to not being on the latest version.
Thanks to both Tom and Magnus for your help.
Tom Lane wrote:
William Garrison <[EMAIL PROTECTED]> writes:
I'm embarrassed to say it is 8
b limit, I
suggest you upgrade to 8.2.9 and see if it goes away.
As this is entirely a client bug, there would be nothing in the logs.
//Magnus
William Garrison wrote:
I'm embarrassed to say it is 8.2.3 :( I'm not sure why they haven't
upgraded our production servers to the late
g_dump was running.
Tom Lane wrote:
William Garrison <[EMAIL PROTECTED]> writes:
Our IT administrator ran a pg_dump and received the following error:
pg_dump: [custom archiver] WARNING: ftell mismatch with expected
position -- ftell used
What platform, and exactly what version of
Tom Lane wrote:
Joao Ferreira gmail <[EMAIL PROTECTED]> writes:
I executed REINDEX by hand and the disk ocupation imediatelly dropped 6
Giga...!!!
is there a way to configure postgres to automatically execute the needed
REINDEXING (on indexes and tables) for a given database
Our IT administrator ran a pg_dump and received the following error:
.
.
.
pg_dump: dumping contents of table history
pg_dump: [custom archiver] WARNING: ftell mismatch with expected
position -- ftell used
pg_dump: dumping contents of table history_archive
pg_dump: [custom archiver] WARNING: fte
If the autovacuum is running, will that show somewhere? I know there is
a command to see currently running queries (not in front of me right
now) but will that show vacuums or any other maintenance operations that
might be running?
--
Sent via pgsql-general mailing list (pgsql-general@postgre
That won't work if you have a value "Anz" in there. It would be in the
gap between An and Am.
create table test (test text);
insert into test values ('A');
insert into test values ('b');
insert into test values ('c');
insert into test values ('d');
insert into test values ('e');
insert into tes
I use npgsql 1.0 to access a PostgreSql 8.2.3 database. Recently, I
decided to test with standard_conforming_strings = on and I noticed that
npgsql still sends double-backslashes, which corrupts the data.
This is especially bad with byte arrays: if I insert N bytes I get back
4*N bytes becaus
I get the following error in the postgres log. I know what the error
means and how to fix it, but I don't know how to determine which
statement is causing it:
2007-03-27 09:29:04 WARNING: nonstandard use of \\ in a string literal
at character 72
2007-03-27 09:29:04 HINT: Use the escape stri
I don't recommend it. There are better ways to store UUIDs:
char(32)<-- Easy to work with, fixed length, inefficient
varchar(32) <-- 4 bytes larger due to variable size
bytea() <-- 20 bytes, variable length
bit(128)<-- 16 bytes, optimal
I don't like char() or varchar() because of ca
I have a server running Windows Server 2003 32-bit that has 8GB of
memory. Our system administrator installed PAE (Physical Address
Extensions) which I know MS SQL Server will use, but I'm not sure if
PostgreSQL will.
Can PostgreSQL use the memory above 2GB and 4GB?
-
should be able to
do what you suggest. I'll have to try that. Or maybe that is what you
meant all along.
Jorge Godoy wrote:
William Garrison <[EMAIL PROTECTED]> writes:
WHERE customerid = ANY($1);
Results in the error:
ERROR: op ANY/ALL (array) requires array on right side
Tom Lane wrote:
William Garrison <[EMAIL PROTECTED]> writes:
I'm using npgsql and C#, and I've realized it doesn't support passing
arrays. Barring things like updating npgsql, what form of hackiness
would work best here?
The customerIDs are GUIDs represented as 16-byt
I have a stored procedure that takes a list of IDs and uses the ANY
operator:
CREATE OR REPLACE FUNCTION CalculateTotals(
customerList bytea[],
out total bigint,
out most_recent_login_date date)
AS $$
BEGIN
SELECT
SUM(totalsize), MAX(last_login)
ored procedure. Is that correct?
Thanks to anyone who can assist.
Florian G. Pflug wrote:
William Garrison wrote:
I have a table that keeps running totals. It is possible that this
would get called twice simultaneously for the same UserID. Do I need
to put this in a serializable transactio
d transaction stuff in here, since PostgreSql does that
implicitly with any stored procedure. Is that correct?
Thanks to anyone who can assist.
Florian G. Pflug wrote:
William Garrison wrote:
I have a table that keeps running totals. It is possible that this
would get called twice simultane
I have a table that keeps running totals. It is possible that this
would get called twice simultaneously for the same UserID. Do I need to
put this in a serializable transaction? Since this gets called often,
could it be a performance problem if I make it serializable?
CREATE FUNCTION Updat
I have a table that keeps running totals. It is possible that this
would get called twice simultaneously for the same UserID. Do I need to
put this in a serializable transaction? Since this gets called often,
could it be a performance problem if I make it serializable?
CREATE FUNCTION Updat
lol.
yeah, I meant binary blobs. :-)
Thomas Kellerer wrote:
William Garrison wrote on 06.04.2007 00:22:
I have actually never stored data in the database.
Hmm, funny statement somehow ;)
---(end of broadcast)---
TIP 5: don't forg
I have actually never stored data in the database. But in a recent
project I've realized it might have been smart. We store a terabytes of
data on the file system, and many times I would love to have an ACID
compliant file system. For example, if I delete an entry, I need to
delete it from d
Would it speed things up siginficantly if you set the dtval_smaller()
function to be immutable? Volatile is the default, so it may be
redundantly evaluating things.
Jaime Silvela wrote:
In case anyone is interested, I was able to solve this, more or less.
Here's my new "Latest value" query:
I've never worked with a database with arrays, so I'm curious what the
advantages and disadvantages of using it are. For example:
-- METHOD 1: The "usual" way --
Items table:
item_id int,
item_data1 ...,
item_data2 ...
Primary Key = item_id
ItemSet table: <-- Join table
item_id int,
LESPACE IF EXISTS bad_tablespace;
6) Press F5
Strangely, I am unable to duplicate the problem with psql. I thought it
would submit a single batch if I didn't press enter between each
command, but it doesn't seem to work that way.
If there is a better term please let me know.
Tom L
ommands
that cause this problem, with error output.
-------
William Garrison wrote:
On Windows Server 2003, if you create a tablespace to a location that
doesn't exist, then try to remove that tablespace, you get an error that
pg_tblspc/# does not exist. It appears that p
On Windows Server 2003, if you create a tablespace to a location that
doesn't exist, then try to remove that tablespace, you get an error that
pg_tblspc/# does not exist. It appears that postgres created the
tablespace internally, but not the folder. When you try to drop the
tablespace, t
My guess is that integer division is to blame: 50 divided by 1500 = 0.03
which rounds to zero. You probably have to cast them to real before
doing the division.
Naz Gassiep wrote:
Is anyone able to tell me why in the last column of the returned result
set, the value calculated is always 0?
I am writing scripts to create a database that I want to run in my
development, testing, and production environments. That means I need to
be able to do something like
IF
CREATE TABLESPACE foo LOCATION E'C:\database';
ELSE
CREATE TABLESPACE foo LOCATION E'Z:\database';
I can't seem to fi
48 matches
Mail list logo
