Re: [GENERAL] pg_dump throws no buffer space available error

"Vishal Dixit" <[EMAIL PROTECTED]> wrote > > We are running postgres version 8.0 on windows server 2003. On > doing a pg_dump of a large database the following error occurs: > > pg_dump: could not receive data from server: No buffer space > available > > There is one table in the database containi

Re: [GENERAL] More atomic online backup

On Fri, 6 Jan 2006, Bruno Almeida do Lago wrote: > Hi, > > 1) I've a pg cluster with 5 databases here. I was wondering if it's possible > to make an online backup of 1 database only, not the entire cluster. > Check out tool pg_dump or the documents for other options. > 2) Online backups at Ora

Re: [GENERAL] contrib library in windows

"SunWuKung" <[EMAIL PROTECTED]> wrote > > Does the contrib library exist for Windows? If you are compiling from source tar ball, it is in pgsql\contrib. Regards, Qingqing ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will i

Re: [GENERAL] plpgsql question

Michael Fuhr <[EMAIL PROTECTED]> wrote: On Fri, Jan 06, 2006 at 01:14:38AM -0800, Matthew Peter wrote:> Michael Fuhr wrote:> > On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote:> > > Is it possible to skip the loop and just return all records in a> > > single query and shove all those

Re: [GENERAL] Strange behavior

On Fri, Jan 06, 2006 at 03:26:27PM -0200, Bruno Almeida do Lago wrote: > #!/bin/bash > > imprime () { > echo `date +"%d/%m/%y %H:%M:%S |"` $* > } > > BANCOS=`psql -Atl | cut -d"|" -f1 | grep -v template` > for BANCO in $BANCOS; do > imprime "Inicio do backup da base $BANCO" > done > > > [E

Re: [GENERAL] plpgsql question

On Fri, Jan 06, 2006 at 01:14:38AM -0800, Matthew Peter wrote: > Michael Fuhr <[EMAIL PROTECTED]> wrote: > > On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote: > > > Is it possible to skip the loop and just return all records in a > > > single query and shove all those rows into a table

Re: [GENERAL] Reordering columns in a table

As I recall, the MS SQL Server draggy droppy diagrammer tool made it seem trivial to rearrange columns did the same thing. It just generated SQL statements to: Begin transaction select data in new order into a new table drop dependent objects drop old table rename new table re-create dependent ob

Re: [GENERAL] Drop user doesn't drop granted privs?

* Pete Deffendol ([EMAIL PROTECTED]) wrote: > Correct me if I'm wrong, but it seems that PostgreSQL 7.4 does not remove a > user's privileges on tables when that user is dropped. The privileges are > still showing up if I do a \z in psql, but with the SYSID instead of the > username (obviously, si

[GENERAL] Drop user doesn't drop granted privs?

Correct me if I'm wrong, but it seems that PostgreSQL 7.4 does not remove a user's privileges on tables when that user is dropped.  The privileges are still showing up if I do a \z in psql, but with the SYSID instead of the username (obviously, since the username doesn't exist.)  Is there an easy w

[GENERAL] contrib library in windows

I keep hearing about it and reading about it, and it sounds like something I should be familiar with, but I have never seen the contrib library - probably because I am working on Windows. Does the contrib library exist for Windows? if yes could somebody point me where can I download it from? th

[GENERAL] Flagging and/or Cleansing/Correcting bad telephone number data

Hoping that perhaps someone here has already been down this road and may be willing to provide some pointers. Can anyone direct me to information regarding the use of PostgreSQL with other references to verify/flag bad telephone number data? I've tables with 'people' data ( f/lname, addr, zip,

Re: [GENERAL] why am I getting a seq scan on this query?

"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Someone might have a better idea but my guess is that PG things the > seq_scan would be faster. That's what it thinks, and it might be right. This query is fetching 2% of the table, which is near the crossover point where a seqscan is faster, assum

Re: [GENERAL] Reordering columns in a table

> I would assume > that all dependent database objects are also dropped when you drop the > table, so you'd have to recreate all of your foreign keys (both > directions) and triggers etc.? Basically. I try to keep my DDL scripts organized in a way that makes this easy. Of course an automated tool

Re: [GENERAL] ./configure --with-openssl=path fails

On 1/6/06 2:47 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote: >> ./configure --with-openssl=path >> This no longer works with 8.x. See: >> >> What is the correct way to work around this restriction? > > Use --with-includes and --

Re: [GENERAL] why am I getting a seq scan on this query?

Mark Harrison wrote: I'm expecting this to do an indexed scan... any clue why it's not? This is with PG 7.4. Someone might have a better idea but my guess is that PG things the seq_scan would be faster. You could try decreasing your random_page_cost. I have also heard that setting your (alth

[GENERAL] why am I getting a seq scan on this query?

I'm expecting this to do an indexed scan... any clue why it's not? This is with PG 7.4. Thanks!! planb=# explain select id,shotname from df_files where showid=30014515::bigint; QUERY PLAN --- Seq Scan o

Re: [GENERAL] Reordering columns in a table

"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Actually, I'm pretty sure this is on the TODO. [ checks... ] No, it isn't. My recollection is that we looked very hard at this around the time DROP COLUMN was implemented, and concluded that we weren't ever going to support it, because distinguishing

Re: [GENERAL] ./configure --with-openssl=path fails

Wes <[EMAIL PROTECTED]> writes: > ./configure --with-openssl=path > This no longer works with 8.x. See: > > What is the correct way to work around this restriction? Use --with-includes and --with-libraries as needed.

Re: [GENERAL] Reordering columns in a table

Actually, I'm pretty sure this is on the TODO. It can't really happen until we have the ability to somehow divorce on-disk ordering from what's presented in the catalog. It's not exactly rocket science to make this happen, but it is quite a bit of work... On Fri, Jan 06, 2006 at 10:59:12AM -0600,

Re: [GENERAL] Indexes works only on miss

You might need to increase the statistics target (for that table or for the cluster), and/or decrease random_page_cost (most folks find something between 2 and 3 to perform the best). On Fri, Jan 06, 2006 at 07:07:54PM +0100, Sebastjan Trepca wrote: > Thank you for exhaustive explanation, this is

Re: [GENERAL] Reordering columns in a table

John McCawley <[EMAIL PROTECTED]> schrieb: > OK, one last question on the subject and I'll shut up. I would assume that > all dependent database objects are also dropped when you drop the table, so > you'd have to recreate all of your foreign keys (both directions) and > triggers etc.? Um, ye

[GENERAL] ./configure --with-openssl=path fails

Because of our build environment, we need to build PostgreSQL specifying the location of the openssl libraries to insure everyone is using the same libraries, regardless of the system software is built and executed on. At 7.4.5, we used: ./configure --with-openssl=path This no longer works with

Re: [GENERAL] Reordering columns in a table

Andreas Kretschmer wrote: John McCawley <[EMAIL PROTECTED]> schrieb: However, in the real world, ... ...And i think, other systems do this: - begin - create a new temp. table with the new order - insert all values from the old table in the new temp. table - drop the old table - renam

Re: [GENERAL] Reordering columns in a table

OK, one last question on the subject and I'll shut up. I would assume that all dependent database objects are also dropped when you drop the table, so you'd have to recreate all of your foreign keys (both directions) and triggers etc.? Andreas Kretschmer wrote: John McCawley <[EMAIL PROTECT

Re: [GENERAL] Adding another primary key to a populated table

Adding -general back in... As someone else already mentioned, you can't have multiple primary keys on one table. > From: Daniel Kunkel [mailto:[EMAIL PROTECTED] > ERROR: ALTER TABLE / PRIMARY KEY multiple primary keys for table > 'product_price' are not allowed > > > On Thu, 2006-01-05 at 22:

Re: [GENERAL] Reordering columns in a table

John McCawley <[EMAIL PROTECTED]> schrieb: > However, in the real world, columns are often added willy-nilly as they are > needed, and it is not immediately obvious which, if any, of the columns > will be related. Later, solely for visual clarity, it is desirable to have > the ability to reorde

Re: [GENERAL] 'Official' definition of ACID compliance?

Good points. ACID is still useful to discuss wrt to database managers, because there are databases out there which fail to provide the basics, not just the kind of corner cases discussed re mySQL. In fact, there's a popular Mac-derived thing popular in vertical market development, called 4th Dimens

Re: [GENERAL] 'Official' definition of ACID compliance?

I think there is a reason that there is no mention at all of ACID in the ANSI/ISO SQL standard. It is incredibly hard to achieve. Transactions are not enough and primary + foreign keys are not enough and check constraints are not enough. You can have all these things operating correctly but if t

Re: [GENERAL] Reordering columns in a table

I would certainly never have any of my normal application code depend on the ordering of columns. My desire for column ordering is strictly for administration purposes, keeping the visual clarity of the datamodel, and ensuring that the visual datamodel is consistent with what is actually in th

Re: [GENERAL] 'Official' definition of ACID compliance?

> ACID > compliance requires that either all or none of the operations in the > transaction happen. In this case one of them does not. So maybe it's Durability that's violated in your example or Atomicity ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---

[GENERAL] RECORD return types in general, dblink in particular

Hello, I'm trying to use dblink to build an interface to a number of foreign databases. In particular, my goal is to hide the relevant data in those databases behind a number of local updateable views, which I can then treat interchangeably with my own local tables. To abstract the act of connect

[GENERAL] More atomic online backup

Hi, 1) I've a pg cluster with 5 databases here. I was wondering if it's possible to make an online backup of 1 database only, not the entire cluster. 2) Online backups at Oracle are done per tablespace. Do you see any advantage on this? Best Regards, Bruno Almeida do Lago

Re: [GENERAL] Reordering columns in a table

John McCawley <[EMAIL PROTECTED]> schrieb: > I believe that it makes a lot of practical difference, just like organizing > related code into files, classes etc. is important for clarity. This isn't > a trivial thing, and the other (sarcastic?) suggestion that I reorder my > select misses the p

Re: [GENERAL] Problems reading dump files coming from version 7.1 into version 8.1.

"Dumortier" <[EMAIL PROTECTED]> writes: > Since I could not figure how to make the pg_dump of v8.1 work correctly. I > used the pg_dump of v7.1 and I obtained 5 files of about 5Go. > I found on the mailing list a solution that someone had used which consisted > in replacing the literal carriage re

Re: [GENERAL] Data loading from a flat file...

On Thu, Jan 05, 2006 at 23:04:55 -0600, Angshu Kar <[EMAIL PROTECTED]> wrote: > Could you please tell me what's the syntax with INSERT for this? http://developer.postgresql.org/docs/postgres/sql-insert.html ---(end of broadcast)--- TIP 2: Don't 'k

Re: [GENERAL] Indexes works only on miss

Thank you for exhaustive explanation, this is the output with analyze :"Seq Scan on test  (cost=0.00..120.67 rows=627 width=11) (actual time=0.018..5.467 rows=621 loops=1)""  Filter: (("Owner")::text = 'root'::text)" "Total runtime: 7.288 ms""Index Scan using idx_test_owner on test  (cost=0.00..96.

Re: [GENERAL] Relational Inheritance Thoughts

Trent Shipley wrote: At the risk of belaboring the point, relational single inheritance resembles a hierarchical database, like a traditional computer file system. A relational multiple inheritance database resembles a network database, like the once promising CODASYL standard or the GROVE or

[GENERAL] pg_dump throws no buffer space available error

We are running postgres version 8.0 on windows server 2003. On doing a pg_dump of a large database the following error occurs: pg_dump: could not receive data from server: No buffer space available There is one table in the database containing bytea type column, this error comes as we add more e

Re: [GENERAL] Indexes works only on miss

On Fri, Jan 06, 2006 at 05:42:41PM +0100, Sebastjan Trepca wrote: > I really don't understand this behaviour. I have a table with column "owner" > on which I created an index with btree method. The table contains around 3k > rows. > > Now I run it using EXPLAIN command. Please post the EXPLAIN AN

[GENERAL] Relational Inheritance Features.

Relational Inheritance Supporting Features Perhaps the most important deficit in Postgresql's current INHERITS model is hard to detect. That deficit is the inability to name inheritance classes themselves. One has to refer to the _per se_ class by referencing an associated table. While it i

[GENERAL] Strange behavior

Hello my friends! I was making a pretty simple script to export our databases, and found a strange (funny) behavior of psql (or bash?). [EMAIL PROTECTED] psql -Atl | cut -d"|" -f1 | grep -v template Db_1 Db_2 Db_3 Db_4 The databases name were changed due privacy reasons (our client database), bu

Re: [GENERAL] Indexes works only on miss

Sebastjan Trepca <[EMAIL PROTECTED]> writes: > "Seq Scan on test (cost=0.00..119.11 rows=263 width=11)" > " Filter: (("Owner")::text = 'root'::text)" > "Index Scan using idx_test_owner on test (cost=0.00..96.56 rows=28 > width=11)" > " Index Cond: (("Owner")::text = 'blah'::text)" > Why is th

[GENERAL] Relational Inheritance Thoughts

Relational Inheritance Thoughts The most fundamental property of relational inheritance is that it creates hierarchies of relations that act as composite relations. That is, relational inheritance produces a tree of relations (presumably tables) that itself can be treated as a relation. The

Re: [GENERAL] Problems building pg 8.1.1

Le Vendredi 6 Janvier 2006 16:31, Tom Lane a écrit : > > > > conftest.c:140:44: error: ./src/tools/thread/thread_test.c: No such file > > or directory > > Hmm, it works fine for me. Is that file actually present in your source > tree? No and that's my fault, I have dowloaded postgresql-base-8.1.1

Re: [GENERAL] Adding another primary key to a populated table

On Fri, 6 Jan 2006, Daniel Kunkel wrote: > Thank you for taking time to give that a try. > > I'm currently running 7.3.10. In that case, if you do a drop constraint and then try manually dropping the index (probably drop index product_price_pkey), does it work? ---(end of

Re: [GENERAL] Reordering columns in a table

I believe that it makes a lot of practical difference, just like organizing related code into files, classes etc. is important for clarity. This isn't a trivial thing, and the other (sarcastic?) suggestion that I reorder my select misses the point. I think that having a good visual representa

Re: [GENERAL] Autocommit in Fedora 3

am 06.01.2006, um 17:45:57 +0100 mailte A. Kretschmer folgendes: > am 05.01.2006, um 15:28:06 -0300 mailte MARCELO_LIMA/[EMAIL PROTECTED] > folgendes: > > Help me please, > > > > I need to change autocommit for off in Fedora 3. > > ,[ change your ~/.psqlrc ] > | [EMAIL PROTECTED]:~$ cat

Re: [GENERAL] constraint on type object in oracle

Florent Garcin wrote: Hello! I've a project to implement in oracle using odmg and I'm stuck... Hi, I'm kind of new here, but shouldn't you be asking Oracle questions in oracle mailing lists? ---(end of broadcast)--- TIP 1: if posting/reading th

Re: [GENERAL] Autocommit in Fedora 3

am 05.01.2006, um 15:28:06 -0300 mailte MARCELO_LIMA/[EMAIL PROTECTED] folgendes: > Help me please, > > I need to change autocommit for off in Fedora 3. ,[ change your ~/.psqlrc ] | [EMAIL PROTECTED]:~$ cat ~/.psqlrc | set AUTOCOMMIT off ` HTH, Andreas -- Andreas Kretschmer(Kon

[GENERAL] Indexes works only on miss

Hi,I really don't understand this behaviour. I have a table with column "owner" on which I created an index with btree method. The table contains around 3k rows.Now I run it using EXPLAIN command. This query has some results:explain SELECT "Name" FROM test WHERE "Owner"='root'"Seq Scan on test  (co

[GENERAL] constraint on type object in oracle

Hello! I've a project to implement in oracle using odmg and I'm stuck... Assume that we have the following script: create or replace type Tmyenum is object ( myenum varchar2(10) ); create or replace type a is object ( ... ); create or replace type b under a ( bla Tmyenum, ...

[GENERAL] Problems reading dump files coming from version 7.1 into version 8.1.

Dear all,   I have been using PostgreSQL version 7.1 for years, but recently my hard disk had problems, I decided to change it and took the opportunity to move to the new version 8.1. This is a sun/solaris machine, v7.1 was working under Solaris 2.6, v8.1 is now running under Solaris 2.10

[GENERAL] Autocommit in Fedora 3

Help me please, I need to change autocommit for off in Fedora 3. Have any idea...anyone? Thanks, Marcelo Lima. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMA

Re: [GENERAL] Arrays and Performance

On Fri, Jan 06, 2006 at 09:43:53AM +0100, [EMAIL PROTECTED] wrote: > What we have been observing in the last few weeks is, that the > overall database size is increasing rapidly due to this table and > vacuum processes seem to deadlock with other processes querying data > from this table. Are you

Re: [GENERAL] Adding another primary key to a populated table

On 1/6/06, Daniel Kunkel <[EMAIL PROTECTED]> wrote: > Hi > > It makes sense that I can't have more than 1 primary key. > > Postgres was trying to create another primary key instead of modify the > existing primary key. > > So... > > As I understand it, a table does not always have to have a primary

Re: [GENERAL] Reordering columns in a table

am 06.01.2006, um 9:45:53 -0600 mailte John McCawley folgendes: > Is there a way to change the order of columns in a table in Postgres after > it has been created? Back when I used MS SQL Server, it was trivial to go Yes. 'select foo, bar, batz from table', 'select bar, foo, batz from table'

Re: [GENERAL] Arrays and Performance

[EMAIL PROTECTED] wrote: Would it be more efficient to not use an array for this purpose but split the table in two parts? Any help is appreciated! This is a duplicate of your post from the other day, to which I responded, as did Tom Lane: http://archives.postgresql.org/pgsql-general/2006-0

Re: [GENERAL] Reordering columns in a table

John McCawley wrote: Is there a way to change the order of columns in a table in Postgres after it has been created? ... The best way to do it is when you have the opportunity to do a restore, edit the pg_dump output between the dump and the restore steps. There are other approaches that mi

Re: [GENERAL] Adding another primary key to a populated table

Hi Thank you for taking time to give that a try. I'm currently running 7.3.10. On Fri, 2006-01-06 at 07:50 -0800, Stephan Szabo wrote: > On Thu, 5 Jan 2006, Daniel Kunkel wrote: > > > It makes sense that I can't have more than 1 primary key. > > > > Postgres was trying to create another prima

Re: [GENERAL] Reordering columns in a table

John McCawley wrote: Is there a way to change the order of columns in a table in Postgres after it has been created? Back when I used MS SQL Server, it was trivial to go into Enterprise Manager and move columns up and down. I find this a desirable feature, as I like to keep like columns grou

Re: [GENERAL] Adding another primary key to a populated table

On Thu, 5 Jan 2006, Daniel Kunkel wrote: > It makes sense that I can't have more than 1 primary key. > > Postgres was trying to create another primary key instead of modify the > existing primary key. > > So... > > As I understand it, a table does not always have to have a primary key > defined. >

[GENERAL] Reordering columns in a table

Is there a way to change the order of columns in a table in Postgres after it has been created? Back when I used MS SQL Server, it was trivial to go into Enterprise Manager and move columns up and down. I find this a desirable feature, as I like to keep like columns grouped in my table. Some

Re: [GENERAL] 'Official' definition of ACID compliance?

Russ Brown wrote: On Thu, 5 Jan 2006 15:11:49 -0500 Jaime Casanova <[EMAIL PROTECTED]> wrote: Anyone know who came up with the term in the first place? FWIW --- as defined by ORACLE http://www.orafaq.com/glossary/faqglosa.htm ACID The basic properties of a database transaction: Atomici

Re: [GENERAL] Adding another primary key to a populated table

Hi Yes... It's the only solution I know will work, but this is a core table on a live system, and I haven't done this before. Does anyone have a proven script they could share? On Fri, 2006-01-06 at 08:22 -0700, Guy Fraser wrote: > Have you considered dumping the data, dropping the table and

Re: [GENERAL] Hardware recommendation for PostgreSQL on Windows?

Bjørn T Johansen wrote: I an planning to make a small Windows application and need a nice database I am used to using PostgreSQL under Linux and I am thinking about using this under Windows but how much resources does it use under Windows? The server will be running on the workstation alon

Re: [GENERAL] Problems building pg 8.1.1

mordicus <[EMAIL PROTECTED]> writes: > ./configure --prefix=/opt/pgsql8.1.1 --enable-thread-safety --with-python > ... > checking thread safety of required library functions... no > And when I look in config.log, I have : > conftest.c:140:44: error: ./src/tools/thread/thread_test.c: No such file

Re: [GENERAL] Adding another primary key to a populated table

Have you considered dumping the data, dropping the table and building the replacement table with the correct properties then repopulating the table with the dumped data? On Thu, 2006-05-01 at 23:02 -0800, Daniel Kunkel wrote: > Why do I want to include 6 fields in the primary key? > > Good que

Re: [GENERAL] "Could not open relation with OID x" while deleting a row

On 1/6/06, Sebastjan Trepca <[EMAIL PROTECTED]> wrote: > Hi, > > postgres just started to report this error yesterday when I ran a user > function which deletes rows in a lot of tables. > I get an error: > > "Could not open relation with OID 18789" > > The function looks like this: > > CREATE OR RE

Re: [GENERAL] Data loading from a flat file...

Pandurangan R S wrote: To get rid of ^M characters you could use cat file | tr -d ^M you need to type ^V before you type ^M in the preceeding command. But ^V will not be displayed on the screen. Or you can use dos2unix/unix2dos, if installed. I believe they are in the sysutils package. ---

[GENERAL] "Could not open relation with OID x" while deleting a row

Hi,postgres just started to report this error yesterday when I ran a user function which deletes rows in a lot of tables.I get an error:"Could not open relation with OID 18789"The function looks like this: CREATE OR REPLACE FUNCTION delete_photo(int8)  RETURNS bool AS$BODY$DECLAREgid RECORD;photo R

[GENERAL] Problems building pg 8.1.1

Hi, I'm unable to compile PostgreSQL 8.1.1 on my Suse 10.0 with thread safety enabled. ./configure --prefix=/opt/pgsql8.1.1 --enable-thread-safety --with-python ... ... ... checking thread safety of required library functions... no configure: error: *** Thread test program failed. Your platform

Re: [GENERAL] plpgsql question

Michael Fuhr <[EMAIL PROTECTED]> wrote: On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote:> Is it possible to skip the loop and just return all records in a> single query and shove all those rows into a table variable?Not in PL/pgSQL -- you need to return each row with RETURN NEXT,gene

Re: [GENERAL] Hardware recommendation for PostgreSQL on Windows?

Qingqing Zhou wrote: > ""Bjørn T Johansen"" <[EMAIL PROTECTED]> wrote >> I an planning to make a small Windows application and need a nice >> database I am used to using PostgreSQL >> under Linux and I am thinking about using this under Windows but how much >> resources does it use under Wind

[GENERAL] Arrays and Performance

A few performance issues using PostgreSQL's arrays led us to the question how postgres actually stores variable length arrays. First, let me explain our situation. We have a rather large table containing a simple integer primary key and a couple more columns of fixed size. However, there is a date

Re: [GENERAL] Data loading from a flat file...

No need for that. Everything works fine. I'm grateful pgsql. And thanks a ton Pandu:)On 1/6/06, Angshu Kar < [EMAIL PROTECTED]> wrote:Thanks a lot Pandu.Everything works ok. Now one last thing : I want to insert a fixed value to the D field in all rows. Any statement for that? On 1/6/06, Panduranga