Re: [GENERAL] Adding TEXT columns tanks performance?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/08/07 20:50, Arturo Perez wrote: > Hi all, > > Saturday I changed a table to add a varchar(24) and a TEXT column. It's > used for some reporting purposes (small potatoe stuff really) and the > TEXT column remains mostly empty. However, this we

[GENERAL] Adding TEXT columns tanks performance?

Hi all, Saturday I changed a table to add a varchar(24) and a TEXT column. It's used for some reporting purposes (small potatoe stuff really) and the TEXT column remains mostly empty. However, this week performance has gotten terrible. Queries joining against the aforementioned table h

Re: [GENERAL] Move data between two databases SQL-ASCII to UTF8

On Thu, Feb 08, 2007 at 08:22:40PM -0500, Chad Wagner wrote: > On 2/8/07, Clodoaldo <[EMAIL PROTECTED]> wrote: > >Use pg_dump to dump the db and use iconv on the generated file: > > > >iconv -f ASCII -t UTF-8 mydb.dump -o mydb_utf8.dump Converting the data from ASCII to UTF-8 doesn't make much sen

[GENERAL] Possibly odd question; diff results?

Hi all, I've got a 'history' schema that records changes in the public schema tables over time. I use a trigger and function to do this. What I would like to do though, and this may not even be possible, is say something like (pseudo-code) "SELECT DIFF foo_name FROM history.foo WHERE foo_id=X;"

Re: [GENERAL] Move data between two databases SQL-ASCII to UTF8

On 2/8/07, Clodoaldo <[EMAIL PROTECTED]> wrote: Use pg_dump to dump the db and use iconv on the generated file: iconv -f ASCII -t UTF-8 mydb.dump -o mydb_utf8.dump Wouldn't it be adequate to set the client encoding to SQL_ASCII in the dump file (if that was infact the encoding on the origina

Re: [GENERAL] Move data between two databases SQL-ASCII to UTF8

2007/2/8, [EMAIL PROTECTED] <[EMAIL PROTECTED]>: I need to convert my database to UTF8. Is there a way to do a SELECT ... INSERT from the old database table to the new one? Would the INSERT correct data errors between the two data types? I only have 10 tables and the biggest has < 8000 rows. U

[GENERAL] Move data between two databases SQL-ASCII to UTF8

I need to convert my database to UTF8. Is there a way to do a SELECT ... INSERT from the old database table to the new one? Would the INSERT correct data errors between the two data types? I only have 10 tables and the biggest has < 8000 rows. Running Version 8.1.4 on Redhat 9 *** *** *** ***

Re: [GENERAL] Limit for number of Joins in a View? Version 8.1.4 on Redhat 9

On 2/9/07, Tom Lane <[EMAIL PROTECTED]> wrote: Jim Nasby <[EMAIL PROTECTED]> writes: > Note that DOMAIN support unfortunately isn't perfect; for example, > plpgsql doesn't enforce domain constraints (IIRC there's some other > bugs as well). Fixed in 8.2 ... not that there aren't necessarily bugs

[GENERAL] PgSQL dump

Hello, we have one table in database (db has over 200 tables), which has one blob filed for storing user photos. We are making backup every night, but it is now too large because of blobs. We do not need to make backup of photos every night, but the question is how to backup database without blob

Re: [GENERAL] Limit for number of Joins in a View? Version 8.1.4 on Redhat 9

>Alvaro Herrera <[EMAIL PROTECTED]> wrote on 02/08/2007 02:51:52 PM: > > [EMAIL PROTECTED] wrote: > > Using PGADMINIII I added a column of this type to my database but when I > > try to update it I get an error. > > > > column: > > ALTER TABLE datatype ADD COLUMN charflag "char"[]; > > You add

Re: [GENERAL] Limit for number of Joins in a View? Version 8.1.4 on Redhat 9

[EMAIL PROTECTED] wrote: > Using PGADMINIII I added a column of this type to my database but when I > try to update it I get an error. > > column: > ALTER TABLE datatype ADD COLUMN charflag "char"[]; You added an array of "char", which is not the same. -- Alvaro Herrera

Re: [GENERAL] Limit for number of Joins in a View? Version 8.1.4 on Redhat 9

>Alvaro Herrera <[EMAIL PROTECTED]> wrote on 02/08/2007 01:58:20 PM: > The "char" type (including quotes) is a very different animal from all > those character types the manual you quote is talking about. "char" is > a single byte, while varchar(1) and all the rest are a single character, > meani

Re: [GENERAL] Limit for number of Joins in a View? Version 8.1.4 on Redhat 9

[EMAIL PROTECTED] wrote: > >Jim Nasby <[EMAIL PROTECTED]> wrote on 02/08/2007 12:12:00 PM: > > If you do end up back at using foreign keys, I suggest using either a > > smallint or "char"... the savings across the number of fields you're > > looking at would start to add up, especially if you st

Re: [GENERAL] Problems shutting down Postmaster

Okay, for some reason we still had a problem connecting after a pg_ctl reload, but the shutdown went through this time, and everything's working now. Thanks for the assistance. Tom Lane <[EMAIL PROTECTED]> wrote: Andrew Edson writes: > Someone else added the IP address for the other system to

Re: [GENERAL] Problems shutting down Postmaster

On Thu, 8 Feb 2007, Andrew Edson wrote: > Someone else added the IP address for the other system to the > pg_hba.conf file later, but since we had already started postgres by > then, it didn't take. And now, for some reason, postgres doesn't seem > to want to let me shut down. Try telling Pos

Re: [GENERAL] Problems shutting down Postmaster

Andrew Edson wrote: I need to add a system to our postgres pg_hba.conf file; the main server went down a few days ago, and this system was mistakenly left off the list of allowed systems when the server was brought back up. (Older version of the .conf file; I managed to accidentally delete the

Re: [GENERAL] Problems shutting down Postmaster

Andrew Edson <[EMAIL PROTECTED]> writes: > Someone else added the IP address for the other system to the > pg_hba.conf file later, but since we had already started postgres by > then, it didn't take. You don't need to shut down the server to adjust pg_hba.conf ... just "pg_ctl reload". > An

Re: [GENERAL] Limit for number of Joins in a View? Version 8.1.4 on Redhat 9

>Jim Nasby <[EMAIL PROTECTED]> wrote on 02/08/2007 12:12:00 PM: > Also, I suggest using "char" instead of just char. "char" is a > special data type that's limited to storing a single character; the > advantage is that it's much smaller and faster than a char. > > If you do end up back at using

[GENERAL] Problems shutting down Postmaster

I need to add a system to our postgres pg_hba.conf file; the main server went down a few days ago, and this system was mistakenly left off the list of allowed systems when the server was brought back up. (Older version of the .conf file; I managed to accidentally delete the more current one whi

Re: [GENERAL] Limit for number of Joins in a View? Version 8.1.4 on Redhat 9

Jim Nasby <[EMAIL PROTECTED]> writes: > Note that DOMAIN support unfortunately isn't perfect; for example, > plpgsql doesn't enforce domain constraints (IIRC there's some other > bugs as well). Fixed in 8.2 ... not that there aren't necessarily bugs left, but the above as a blanket statement i

Re: [GENERAL] Ouch!

andy rost <[EMAIL PROTECTED]> writes: > Running 8.1.3 on FreeBSD 6.0 server and got the following (I'm most > interested in the "PANIC" entry): > 2007-02-06 17:22:22 CST PANIC: right sibling's left-link doesn't match > Any advice? Update to 8.1.8. I'm not sure about this specific symptom, but

Re: [GENERAL] Installing PostgreSQL on Windows XP

Jim Nasby wrote: > BTW, 8.2.2 has a rather nasty bug; you'll want to go grab 8.2.3 or > 8.2.1. You don't want 8.2.1 either because it has a nasty security bug. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development,

Re: [GENERAL] Installing PostgreSQL on Windows XP

BTW, 8.2.2 has a rather nasty bug; you'll want to go grab 8.2.3 or 8.2.1. On Feb 7, 2007, at 10:30 AM, Christian Henning wrote: Hi there, first post. I would like to share a solution for Windows users having problems installing PostgreSQL 8.2.2. I couldn't find anything in the docs and FAQ.

Re: [GENERAL] Limit for number of Joins in a View? Version 8.1.4 on Redhat 9

On Feb 7, 2007, at 10:05 AM, [EMAIL PROTECTED] wrote: I was using the flag table to keep the flags consistent between all the tables in the database that might use them. I didn't know about CREATE DOMAIN which will do what I want perfectly Note that DOMAIN support unfortunately isn't perfect

[GENERAL] Ouch!

Running 8.1.3 on FreeBSD 6.0 server and got the following (I'm most interested in the "PANIC" entry): 2007-02-06 17:15:11 CST LOG: archived transaction log file "000104A30025" 2007-02-06 17:15:27 CST LOG: archived transaction log file "000104A30026" 2007-02-06 17:15

Re: [GENERAL] DBI-Link 2.0

On Thu, Feb 08, 2007 at 03:38:25AM -0800, [EMAIL PROTECTED] wrote: > On Feb 8, 8:54 am, Hannes Dorbath <[EMAIL PROTECTED]> wrote: > > On 07.02.2007 17:59, [EMAIL PROTECTED] wrote: > > > > > Is there any form of manual for DBI-link out there? > > > > > Any link is greatly appreciated! > > > > I thin

Re: [GENERAL] SQL textbook

Ted, Google the publisher's name to get to its site. mike - Original Message - From: Ted Byers To: Michael Schmidt ; pgsql-general@postgresql.org Sent: Thursday, February 08, 2007 11:05 AM

Re: [GENERAL] SQL textbook

On Thu, 8 Feb 2007, Ted Byers wrote: Do you have any URLs for the books that are available for download? Google is your friend. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM)

Re: [GENERAL] SQL textbook

On Thu, 8 Feb 2007, Michael Schmidt wrote: There are a lot of resources available. Each database program has a different dialect of SQL, and I find the PostgreSQL on-line manual has an excellent introduction of SQL and even some theoretical issues. I would start there (its free and directly ap

Re: [GENERAL] SQL textbook

- Original Message - From: Michael Schmidt To: Jan Mura ; pgsql-general@postgresql.org Sent: Thursday, February 08, 2007 12:54 PM Subject: Re: [GENERAL] SQL textbook Many books are available for download. Joe Celko is a well respected author and just about anything with his name on i

Re: [GENERAL] SQL textbook

Jan, There are a lot of resources available. Each database program has a different dialect of SQL, and I find the PostgreSQL on-line manual has an excellent introduction of SQL and even some theoretical issues. I would start there (its free and directly applicable to using PostgreSQL). Sear

Re: [GENERAL] Database corruption.

>> Zeroing out the whole block containing it is the usual recipe. Something like this worked for me in the past: % dd bs=8k count=X < /dev/zero >> clog-file I had to calculate X, because I usually had a situation with truncated clog-file, and a failed attempt to read it from offset XYZ. And I

Re: [GENERAL] Database corruption.

Michael Guerin <[EMAIL PROTECTED]> writes: > You're suggesting to zero out the block in the underlying table files, > or creating the missing pg_clog file and start filling with zero's? The former. Making up clog data is unlikely to help --- the bad xmin is just the first symptom of what's proba

Re: [GENERAL] Client connection: Port 5432 / Postgres

Nicolas Gignac wrote: I have installed Postgres 8.2 on a internal server having Windows Server 2003 (IIS 6) up and running. - I have configure the hp_config file to: host all 0.0.0.0./0md5 ^

Re: [GENERAL] Database corruption.

Zeroing out the whole block containing it is the usual recipe. I forget the exact command but if you trawl the archives for mention of "dd" and "/dev/zero" you'll probably find it. Keep in mind you want to stop the postmaster first, to ensure it doesn't have a copy of the bad block cached in m

Re: [GENERAL] Error on table insert: attribute 13 has wrong type

"Andrus" <[EMAIL PROTECTED]> writes: > I installed 8.2 server on Windows XP > Today every instert to table causes error: > ERROR: attribute 13 has wrong type > DETAIL: Table has type character, but query expects character. > Any idea how to fix it ? Update to 8.2.3. re

Re: [GENERAL] Service startup - troubleshooting

Mike wrote: Hello, I know this is probably a borderline Linux question but I think someone with PostgreSQL expertise will better be able to answer it (I already struck out on the Linux side and after much searching on the Web). I installed Postgres 8.1 on Ubuntu 6.10 using Synaptic. Postgres is

Re: [GENERAL] Client connection: Port 5432 / Postgres

Nicolas Gignac wrote: Hello, I have installed Postgres 8.2 on a internal server having Windows Server 2003 (IIS 6) up and running. - I have configure the hp_config file to: host all 0.0.0.0./0 md5 Don't you mean pg_hba.conf? - I have change the listening address t

Re: [GENERAL] Client connection: Port 5432 / Postgres

am Wed, dem 07.02.2007, um 11:28:56 -0500 mailte Nicolas Gignac folgendes: > Hello, > > I have installed Postgres 8.2 on a internal server having Windows Server 2003 > (IIS 6) up and running. > - I have configure the hp_config file to: host > all 0.0

Re: [GENERAL] Error on table insert: attribute 13 has wrong type

Andrus wrote: > I installed 8.2 server on Windows XP > > Today every instert to table causes error: > > ERROR: attribute 13 has wrong type > DETAIL: Table has type character, but query expects character. > > Any idea how to fix it ? You need to upgrade to 8.2.3. That was a rush release to fi

Re: [GENERAL] Database corruption.

Michael Guerin <[EMAIL PROTECTED]> writes: > Ok, so I'm trying to track down the rows now (big table slow queries :( > ) How does one zero out a corrupt row, plain delete? I see references > for creating the missing pg_clog file but I don't believe that's what > you're suggesting.. Zeroing ou

Re: [GENERAL] Database corruption.

Tom Lane wrote: Michael Guerin <[EMAIL PROTECTED]> writes: Hmm, that makes it sound like a plain old data-corruption problem, ie, trashed xmin or xmax in some tuple header. Can you do a "select count(*)" from this table without getting the error? no, select count(*) fails around 25

Re: [GENERAL] Postgres training down under.

Paul Lambert wrote: Are there any companies in the great land of Australia that offer Postgres training courses? I see a number listed for around the US on the postgresql.org website - just curious if anything similar goes on down under. Cheers, P. Fujitsu -- Cheers Tony

[GENERAL] Error on table insert: attribute 13 has wrong type

I installed 8.2 server on Windows XP Today every instert to table causes error: ERROR: attribute 13 has wrong type DETAIL: Table has type character, but query expects character. Any idea how to fix it ? Andrus. ---(end of broadcast)--- TIP 4

[GENERAL] DBI-Link 2.0

Hi, Is there any form of manual for DBI-link out there? Any link is greatly appreciated! Regards, Jo. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] DBI-Link 2.0

On Feb 8, 8:54 am, Hannes Dorbath <[EMAIL PROTECTED]> wrote: > On 07.02.2007 17:59, [EMAIL PROTECTED] wrote: > > > Is there any form of manual for DBI-link out there? > > > Any link is greatly appreciated! > > I think all available documentation comes with it in various README > files. Do you have

[GENERAL] Service startup - troubleshooting

Hello, I know this is probably a borderline Linux question but I think someone with PostgreSQL expertise will better be able to answer it (I already struck out on the Linux side and after much searching on the Web). I installed Postgres 8.1 on Ubuntu 6.10 using Synaptic. Postgres is listed under

Re: [GENERAL] Postgres SQL Syntax

Richard Huxton wrote: > Jim C. wrote: >>> You probably want one of the mysql converter projects, e.g. >>> http://pgfoundry.org/projects/mysql2pgsql/ >>> Also read the "converting from other databases" section here: >>> http://www.postgresql.org/docs/techdocs >> >> I tried several conversion too

[GENERAL] line folding versus shop line

Hi, I'm using postgresql 7.4. If I execute SQL Select statement, then the records fold around lines. I would like to turn this off, so that lines do not fold. I know the option in less, which one has to set to "-S". I have exported the variable and it works with textfiles. However, if I use psq

[GENERAL] Client connection: Port 5432 / Postgres

Hello, I have installed Postgres 8.2 on a internal server having Windows Server 2003 (IIS 6) up and running. - I have configure the hp_config file to: host all 0.0.0.0./0 md5 - I have change the listening address to '*' in the postgres.conf file - No Firewall activated

Re: [GENERAL] Under Red Hat AS in a WIndows Virtual Machine

Matt Miller wrote: I'm looking at running PG under Red Hat AS, but within a virtual machine. The host will be a Windows 2003 Enterprise server running Windows Virtual Machine 2005 Enterprise. From the archives it seems that people are doing this type of thing successfully, but I just wanted to

Re: [GENERAL] Data migration from version 7.2.1 to 8.1.5

On 2/8/07, Tom Lane <[EMAIL PROTECTED]> wrote: Arindam <[EMAIL PROTECTED]> writes: > On 2/8/07, Tom Lane <[EMAIL PROTECTED]> wrote: >> Now you *should* be using the newer pg_dump to dump from the old >> database --- that's considered good practice. But you need an >> up-to-date pg_restore to rea

Re: [GENERAL] Data migration from version 7.2.1 to 8.1.5

Arindam <[EMAIL PROTECTED]> writes: > On 2/8/07, Tom Lane <[EMAIL PROTECTED]> wrote: >> Now you *should* be using the newer pg_dump to dump from the old >> database --- that's considered good practice. But you need an >> up-to-date pg_restore to read its output. > Well this time I got it dumped u

Re: [GENERAL] Data migration from version 7.2.1 to 8.1.5

On 2/8/07, Tom Lane <[EMAIL PROTECTED]> wrote: Arindam <[EMAIL PROTECTED]> writes: > I tried dumping data in tar format using the pg_dump utility (with > version 7.2.1) and restoring it using pg_restore utility (with version > 8.1.5). But on running this command: > pg_restore -d mydb -C -u > I

[GENERAL] Under Red Hat AS in a WIndows Virtual Machine

I'm looking at running PG under Red Hat AS, but within a virtual machine. The host will be a Windows 2003 Enterprise server running Windows Virtual Machine 2005 Enterprise. From the archives it seems that people are doing this type of thing successfully, but I just wanted to see if anyone had any

[GENERAL] [Security] New RPM Sets for Fedora Core / Red Hat Enterprise Linux

- PostgreSQL New RPM Sets 2007-02-08 Versions: 8.2.3, 8.1.8, 8.0.12 Set label: 8.2.3-1PGDG, 8.1.8-1PGDG, 8.0.12-1PGDG -

Re: [GENERAL] Data migration from version 7.2.1 to 8.1.5

Arindam <[EMAIL PROTECTED]> writes: > I tried dumping data in tar format using the pg_dump utility (with > version 7.2.1) and restoring it using pg_restore utility (with version > 8.1.5). But on running this command: > pg_restore -d mydb -C -u > I get the following error: > pg_restore: [archiver]

[GENERAL] Data migration from version 7.2.1 to 8.1.5

Hi, I am trying to write a script which will migrate all the data from a particular database on version 7.2.1 and restore the same data along with the full schema on an 8.1.5 database. The database itself will be created and does not need to be created. I tried dumping data in tar format using t

Re: [GENERAL] Array OUT columns in a record returned from a function - part deux

OK I reread the array documentation and something that didn't stand out before now does :). "By default, the lower bound index value of an array's dimensions is set to one. If any of an array's dimensions has a lower bound index not equal to one, an additional decoration that indicates the actual

Re: [GENERAL] Persistent dead rows

Richard Huxton writes: > Well, both cluster and vacuum full will require a lock on the table. But > they're more or less doing the same thing, so why the one should work > and the other not I don't know. CLUSTER isn't MVCC-safe --- it'll drop rows that are committed dead even if they are pote

Re: [GENERAL] Persistent dead rows

Malcolm McLean wrote: Richard Huxton wrote: Malcolm McLean wrote: I tested this theory by stopping java applications that were connected to the database and all other connections that were using transactions and the full vacuum was still unable to remove the dead rows. What I'm still wonde

Re: [GENERAL] Persistent dead rows

Richard Huxton wrote: > Malcolm McLean wrote: >> I tested this theory by stopping java applications that were connected >> to the database and all other connections that were using transactions >> and the full vacuum was still unable to remove the dead rows. >> >> What I'm still wondering about, i

Re: [GENERAL] Persistent dead rows

Malcolm McLean wrote: Richard Huxton wrote: What is causing those dead rows to not get cleared even by a full vacuum? Is there any way keep them low without having to run a cluster command as that is a locking statement and requires me to close all java applications that are connecting to th

Re: [GENERAL] temp tables in functions?

Agreed :) I guess missed out some details from there as I just thought he needed to drop a temp table inside a function like this: CREATE OR REPLACE function tempfunc (int) returns int AS $$ begin execute 'create temporary table test (a numeric) on commit drop'; execute 'INSERT INTO test val

Re: [GENERAL] temp tables in functions?

On Thu, Feb 08, 2007 at 12:29:37PM +0500, Shoaib Mir wrote: > Something like this will help you: > > execute immediate 'create temporary table test (a number) on commit drop'; PL/pgSQL doesn't recognize "immediate" and number isn't a PostgreSQL type so the above yields a syntax error. Also, EXEC

[GENERAL] Array OUT columns in a record returned from a function - part deux

I was on here a couple of days ago with problems getting relational data *into* some array variables (now solved thank you), but now I'm here with problems getting data *out* again. A week or so ago I did a little test page with a little test table pulled from some example about postgres arrays.

Re: [GENERAL] Persistent dead rows

Richard Huxton wrote: >> What is causing those dead rows to not get cleared even by a full >> vacuum? Is there any way keep them low without having to run a cluster >> command as that is a locking statement and requires me to close all java >> applications that are connecting to that table before r

Re: [GENERAL] Persistent dead rows

Malcolm McLean wrote: Hi, We are currently having a problem with one of our tables containing far too many dead rows. The table in question will have a few hundred thousand inserts and deletes per day and usually builds up quite a large dead row count that starts to affect the performance of t

Re: [GENERAL] simplifying SQL

garry saddington wrote: I have this query which essentially returns records that exist in one table (studentclass) and not another (effortandattainment) to check when teachers have entered their student grades. select from studentclass left join effortandattainment and studentclass.classid

Re: [GENERAL] SQL textbook

Jan Mura wrote: Hello, I would like to ask for a hint for a good SQL textbook. I don't want just a reference manual but real textbook with a theoretical background so I could to design my databases following the general rules as normal forms and so on. Try "An Introduction to Database Systems"

Re: [GENERAL] Storing database in cluster (Memory)

roopa perumalraja wrote: Hi all, As I am inserting 100million rows daily into partitioned tables (daily wise), it is getting slower. What is - the inserts? By how much? What tables? What indexes? How are you inserting these rows? I take my words back as 100million rows. The insert of 20mill

[GENERAL] Persistent dead rows

Hi, We are currently having a problem with one of our tables containing far too many dead rows. The table in question will have a few hundred thousand inserts and deletes per day and usually builds up quite a large dead row count that starts to affect the performance of the queries select from th