Re: [GENERAL] using SSL client certs?

OpenMacNews <[EMAIL PROTECTED]> writes: > what now? where/how do i add the client certs? For libpq-based clients, see the libpq docs http://www.postgresql.org/docs/8.1/static/libpq-ssl.html Dunno about other client-side libraries. regards, tom lane --

Re: [GENERAL] using SSL client certs?

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 hi tom, > For libpq-based clients, see the libpq docs > http://www.postgresql.org/docs/8.1/static/libpq-ssl.html > > Dunno about other client-side libraries. good enuf. exactly what i needed. also, is is possible to 'point' -- probably in pos

[GENERAL] mount -o async - is it safe?

Hi, We've recently set up our database (7.4.9) with our new hosting provider. We have two database servers running RHEL 4 in a cluster; one active and one hot-spare. They share a [fibre-channel connected] SAN partition; the active server has it mounted. Now my question is this; the provider ha

Re: [GENERAL] mount -o async - is it safe?

On Thu, Jan 19, 2006 at 09:42:59AM +, Shane Wright wrote: > Now my question is this; the provider has, by default, mounted it with -o > sync; so all reads/writes are synchronous. This doesn't result in the > greatest of performance, and indeed remounting -o async is significantly > faster. >

[GENERAL] tsearch2 and "how to use"

Hello, because I am not in Strasbourg and have NO WEB Access I have following question: How to use tsearch2? I have load the tsearch2.sql into my Database but I do not know how to use it, because the Documentation under Debian is not very usefull. Note: I am sending mes

[GENERAL] Insert a default timestamp when nothing given

Hi, I have to port an application from MS SQL7 to Postgresql (7.4). When I have a column with a datetime on MS SQL7 the following is possible: INSERT INTO mytable (mydate) values (''); In this case MSSQL will insert '01.01.1900' as the date. When I do the same on Postgresql it says: "invalid i

[GENERAL] Normalized storage to denormalized report

I have a table that stores data like so: Create table raw_vals ( expt_id int, clone_idx int, val numeric, primary key (expt_id,clone_idx) ); And I would like to design a query that gives: Clone_idx expt_id_1 expt_id_2 1 0.7834 0.8231 2 0.28

Re: [GENERAL] Insert a default timestamp when nothing given

On Thu, Jan 19, 2006 at 12:55:44PM +0100, Martin Pohl wrote: > > Hi, > > I have to port an application from MS SQL7 to Postgresql (7.4). > > When I have a column with a datetime on MS SQL7 the following is possible: > INSERT INTO mytable (mydate) values (''); > > In this case MSSQL will inser

[GENERAL] GBorg as a spam magnet

For some time now, spambots have been mistaking an old bug ticket on my GBorg project for a discussion board. Several times a day I get notification emails notifying me of added comments--and they're all spam. See for yourself on http://gborg.postgresql.org/project/libpqxx/ under bug #664. I sen

Re: [GENERAL] Normalized storage to denormalized report

On Thu, Jan 19, 2006 at 07:03:47AM -0500, Sean Davis wrote: > I have a table that stores data like so: > And I would like to design a query that gives: > > Clone_idx expt_id_1 expt_id_2 > 1 0.7834 0.8231 > 2 0.2832 1.2783 > > There are severa

Re: [GENERAL] Normalized storage to denormalized report

On Jan 19, 2006, at 21:03 , Sean Davis wrote: I have a table that stores data like so: Create table raw_vals ( expt_id int, clone_idx int, val numeric, primary key (expt_id,clone_idx) ); And I would like to design a query that gives: Clone_idx expt_id_1 expt_id_2 1

Re: [GENERAL] Insert a default timestamp when nothing given

am 19.01.2006, um 12:55:44 +0100 mailte Martin Pohl folgendes: > > Hi, > > I have to port an application from MS SQL7 to Postgresql (7.4). > > When I have a column with a datetime on MS SQL7 the following is possible: > INSERT INTO mytable (mydate) values (''); wrong date! > > In this ca

Re: [GENERAL] Normalized storage to denormalized report

On 1/19/06 7:14 AM, "Martijn van Oosterhout" wrote: > On Thu, Jan 19, 2006 at 07:03:47AM -0500, Sean Davis wrote: >> I have a table that stores data like so: > > > >> And I would like to design a query that gives: >> >> Clone_idx expt_id_1 expt_id_2 >> 1 0.7834 0.8231

Re: [GENERAL] mount -o async - is it safe?

Martijn van Oosterhout writes: > That depends. As long as the data is appropriately sync()ed when > PostgreSQL asks, it should be fine. However, from reading the manpage > it's not clear if fsync() still works when mounted -o async. > > If -o async means "all I/O is asyncronous except stuff expl

Re: [GENERAL] Insert a default timestamp when nothing given

Martijn van Oosterhout writes: > Not directly. I suppose you could create a view that converted the > value to the right date on insert. I think a trigger might make more sense. -Doug ---(end of broadcast)--- TIP 9: In versions below 8.0, the pla

Re: [GENERAL] Windows re-installation problem

> I uninstalled PostgreSQL from windows for the sake of > re-installing (for the sake of documenting an install for our > product on a clean machine), and now during re-install on the > Service Configuration screen I get "Invalid username > specified: Logon failure: unknown user name or bad pas

Re: [GENERAL] Insert a default timestamp when nothing given

Hi, > > Not directly. I suppose you could create a view that converted the > > value to the right date on insert. > I think a trigger might make more sense. That was a very good idea! I tought it would solve my problem. Unfortunately it didn't: I still get the "invalid syntax" error (I ensured th

Re: [GENERAL] mount -o async - is it safe?

Hi, thanks :) > > If -o async means "all I/O is asyncronous except stuff explicitly > > fsync()ed" you're fine. Otherwise... > > That's the way it works. Async is the default setting for most > filesystems, but fsync() is always honored, at last as far as > non-lying hardware will allow. :) Th

Re: [GENERAL] Insert a default timestamp when nothing given

Change the column type in the view to text, then in the insert/update rule, if the value is '' insert null or what ever, else insert the date (as text) into the real date column (as a date) Jim -- Original Message --- From: " Martin Pohl" <[EMAIL PROTECTED]> To: Doug McNaught

Re: [GENERAL] Insert a default timestamp when nothing given

On Thu, Jan 19, 2006 at 02:43:26PM +0100, Martin Pohl wrote: > Hi, > > > > Not directly. I suppose you could create a view that converted the > > > value to the right date on insert. > > I think a trigger might make more sense. > > That was a very good idea! I tought it would solve my problem.

Re: [GENERAL] tsearch2 and "how to use"

The big job is populating the index columns. I think you can only put the full text index column in the same table as the referenced columns. In other words, you will end up with 3 tables, each with a ftidx column. I hope your docs show how to create and populate the indexes and to create trigge

Re: [GENERAL] mount -o async - is it safe?

Shane Wright <[EMAIL PROTECTED]> writes: > Actually I thought that *all* the database had to have fsync() work > correctly; > not for integrity on failed transactions, but to maintain integrity during > checkpointing as well. But I could well be wrong! I think you're write, but what I was thi

Re: [GENERAL] PostgreSQL Top 10 Wishlist

On Wed, 18 Jan 2006, David Fetter wrote: > On Wed, Jan 18, 2006 at 04:33:23PM -0800, Stephan Szabo wrote: > > On Wed, 18 Jan 2006, Jim C. Nasby wrote: > > > > > Yeah, this isn't about production code, it's about making life > > > easier on developers. Humans naturally want to group data into > > >

Re: [GENERAL] [HACKERS] No heap lookups on index

On Wed, Jan 18, 2006 at 10:11:26PM -0500, Bruce Momjian wrote: > Glen Parker wrote: > > Tom Lane wrote: > > >>What ever happened to grouped heap reads, i.e. building a list of tuples > > >>from the index, sorting in heap order, then reading the heap in a batch? > > > > > > > > > Done in 8.1. I

Re: [GENERAL] mount -o async - is it safe?

On Thu, Jan 19, 2006 at 09:34:00AM -0500, Doug McNaught wrote: > Shane Wright <[EMAIL PROTECTED]> writes: > > > Actually I thought that *all* the database had to have fsync() work > > correctly; > > not for integrity on failed transactions, but to maintain integrity during > > checkpointing as

[GENERAL] PostgreSQL - a ORDBMS?

Hello! In a german spoken python ng is a thread running about DMBMS and Object-Databases. I was asked - becouse of http://www.postgresql.org/docs/8.1/interactive/preface.html#INTRO-WHATIS - how a "object-relational database management system (ORDBMS)" ist defined in the view of PosgreSQL, and w

Re: [GENERAL] Insert a default timestamp when nothing given

Change your table definition and specify a defeault value for your timestamp column this way -- when nothing is given on insert --> it will populate... CREATE TABLE test ( id serial not null primary key, defaultdate timestamp not null default now() ); "" Martin Pohl"" <[EMAIL PRO

[GENERAL] create plperlu langage fails

hi, i get an exception while trying to create plperlu langage : CREATE OR REPLACE FUNCTION plperl_call_handler() RETURNS language_handler AS '/home/adocv3/postgresql-8.1.2/src/pl/plperl/libplperl.so', 'plperl_call_handler' LANGUAGE 'c' VOLATILE; CREATE LANGUAGE 'plperlu' HANDLER

Re: [GENERAL] Rollback to Previous Version

hi all,may be i asked too many questions,Let me rephrase this,What do u think are the best strategies for rolling back to previous schema version.What i think is, (a) having a table which will log version updates. (b) having stored procedures Rollback_from_1.0.0.3_to_1.0.0.2() Rollback_from_1.0.0.2

[GENERAL] Privilege for seeing queries using pg_stat_get_backend_activity

I want certain users to be able to examine running queries using pg_stat_get_backend_activity. Unfortunately, this will only show other users' activity if you have superuser privilege. I do not want to give monitoring users superuser privilege, but I do need to allow them to perform monitoring ta

Re: [GENERAL] create plperlu langage fails

On Thu, Jan 19, 2006 at 05:22:23PM +0100, FERREIRA, William (VALTECH) wrote: > ERROR: could not load library > "/home/adocv3/postgresql-8.1.2/src/pl/plperl/libplperl.so": ld.so.1: > /opt/pgsql/bin/postgres: fatal: relocation error: file > /home/adocv3/postgresql-8.1.2/src/pl/plperl/libplperl.so

Re: [GENERAL] create plperlu langage fails

"FERREIRA, William (VALTECH)" <[EMAIL PROTECTED]> writes: > This e-mail is intended only for the above addressee. It may contain > privileged information. If you are not the addressee you must not copy, > distribute, disclose or use any of the information in it. If you have > received it in error p

Re: [GENERAL] Privilege for seeing queries using pg_stat_get_backend_activity

Marc Munro <[EMAIL PROTECTED]> writes: > I want certain users to be able to examine running queries using > pg_stat_get_backend_activity. Unfortunately, this will only show other > users' activity if you have superuser privilege. > I do not want to give monitoring users superuser privilege, but I

[GENERAL]

unsubscribe pgsql-general ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] Privilege for seeing queries using pg_stat_get_backend_activity

On Thu, Jan 19, 2006 at 09:17:12AM -0800, Marc Munro wrote: > I've tried tricks with security definer functions but this does not help > as pg_stat_get_backend_activity explicitly checks for the caller being a > superuser. Works here. Could you post an example? -- Michael Fuhr

Re: [GENERAL] create plperlu langage fails

Tom Lane schrieb: ... >>Investigatory Powers Act 2000, Lawful Business Practises. > > > I don't think I'm allowed to read your question, let alone answer it... > "Awful Business Practices"? SCNR ;) ---(end of broadcast)--- TIP 5: don't forget to

Re: [GENERAL] FW: Surrogate keys (Was: enums)

Thomas, Many times you will have references to a specific row from somewhere outside of your database. Perhaps you have a federation of web services that collaborate or other arbitrary URL's that contain the key. It might be harder to create remote row sets, middle tier caches, and other sim

Re: [GENERAL] FW: Surrogate keys (Was: enums)

Josh Berkus wrote: Why? I don't find this statement to be self-evident. Why would we have ON UPDATE CASCADE if keys didn't change sometimes? Many times you will have references to a specific row from somewhere outside of your database. Perhaps you have a federation of web services that col

[GENERAL] Question about Hardware & Configuration for Massive Database

I'm looking to set up an informational database which will be accessed by the general public on our website (there will be no internal users).  The database will likely have about 10 million records with a total database size of 200-300 GB.  All 10 million records will be very similar (the same dat

Re: [GENERAL] Question about Hardware & Configuration for Massive

On Thu, 2006-01-19 at 12:39, Kevin & Jessica Hermansen wrote: > I'm looking to set up an informational database which will be accessed > by the general public on our website (there will be no internal > users). The database will likely have about 10 million records with a > total database size of

[GENERAL] tsearchd (tsearch2 daemon) is available for playing

Hi there, we did a 8.1 compatible version of tsearchd, available from http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch2 tsearchd is our old experiment with inverted index. It's fully compatible with tsearch2, actually it's tsearch2+several functions and daemon. Very brief documentation

Re: [GENERAL] Question about Hardware & Configuration for Massive

At 11:39 AM -0700 1/19/06, Kevin & Jessica Hermansen wrote: I'm looking to set up an informational database which will be accessed by the general public on our website (there will be no internal users). The database will likely have about 10 million records with a total database size of 200-30

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

I modified pg_dump.c and aded a sleep of 1000 micro sec in the loop for copying data and it fixed the problem. It looks like for slower machines when it comes to reading larger tables, pg_dump finds no data to read in new lines and assumes EOL while there is still data to be dumped, as a result PQe

Re: [GENERAL] Privilege for seeing queries using

Thanks Tom, On further investigation it seems that the problem is that I can create an equivalent function with security definer, and I can create a wrapper function with security definer but I cannot modify the existing function for security definer. This is a problem because the monitoring use

[GENERAL] Permission denied on relation

I have PostgreSQL 8.0.6 running on Windows 2003 Server. A few days ago I noticed that my logs are getting filled pretty fast. When I checked the issue, I noticed that every one second I get the same error as follows: 2006-01-17 05:07:38 ERROR: could not open relation 20321/20322/796354: Permissi

[GENERAL] Replicating a remote database (backuping)

Hello all! I was looking for ways to backup my remote database in a local computer. I was talking to #postgresql folks, who said me to try sloty. I tried, but I couldn't make it run (I was following http://gborg.postgresql.org/project/slony1/genpage.php?howto_basic). I have a database that is, f

Re: [GENERAL] Error when inserting millions of records

Steven also submitted this subject in the bugs list: "BUG #2168: 45.000.000 records too much?". He also posted more details and how to reproduce the bug. Andrew, no special constraints or triggers were used. Thanks, Rudolph ---(end of broadcast)--

[GENERAL] Connections not closing

I have a batch file that calls two separate sql scripts as follows: ECHO **| psql -h host_name -p 5432 db_name -f script1.sql user_name ECHO **| psql -h host_name -p 5432 db_name -f script2.sql user_name EXIT script1.sql contains something like this: BEGIN; TRUNCATE TABLE table2; INSERT

[GENERAL] Using rowtype parameter

I'm trying to write a stored proc (in pl/Pgl) that can accept rowtypes as arguments:   CREATE or replace FUNCTION www_get_data(user_id "varchar", objectname "varchar", operation "varchar", primarykeyvalue anyelement, rowvalue anyelement)  RETURNS SETOF varchar[] AS...     Whenever I try to t

Re: [GENERAL] 7.4.11 HP-UX ia64 build error

"Ed L." <[EMAIL PROTECTED]> writes: > I'm having trouble building 32-bit pgsql 7.4.11 on the latest > release of HP-UX 11.23 on ia64, and need a little help. > Here's my compiler: > cc: HP aC++/ANSI C B3910B A.06.05 [Jul 25 2005] We support that combination in PG 8.0 and later. If you really wa

Re: [GENERAL] Permission denied on relation

"Nik" <[EMAIL PROTECTED]> writes: > I have PostgreSQL 8.0.6 running on Windows 2003 Server. > A few days ago I noticed that my logs are getting filled pretty fast. > When I checked the issue, I noticed that every one second I get the > same error as follows: > 2006-01-17 05:07:38 ERROR: could not

Re: [GENERAL] 7.4.11 HP-UX ia64 build error

On Thursday January 19 2006 2:12 pm, Tom Lane wrote: > "Ed L." <[EMAIL PROTECTED]> writes: > > I'm having trouble building 32-bit pgsql 7.4.11 on the > > latest release of HP-UX 11.23 on ia64, and need a little > > help. > > > > Here's my compiler: > > cc: HP aC++/ANSI C B3910B A.06.05 [Jul 25 2005

Re: [GENERAL] Using rowtype parameter

"Peter Zeltins" <[EMAIL PROTECTED]> writes: > I'm trying to write a stored proc (in pl/Pgl) that can accept rowtypes = > as arguments: > CREATE or replace FUNCTION www_get_data(user_id "varchar", objectname = > "varchar", operation "varchar", primarykeyvalue anyelement, rowvalue = > anyelement) >

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

Vishal Dixit wrote: > I modified pg_dump.c and aded a sleep of 1000 micro sec in the > loop for copying data and it fixed the problem. It looks like > for slower machines when it comes to reading larger tables, > pg_dump finds no data to read in new lines and assumes EOL while > there is still data

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

Rich Shepard <[EMAIL PROTECTED]> writes: >7) back to /usr/local/pgsql; as postgres ran 'pg_restore' >8) I see nothing in the virtual terminal. The cursor sits on the following > line. Did you tell pg_restore to read from a file? Otherwise it will try to read from your terminal, which pro

Re: [GENERAL] Question about Hardware & Configuration for Massive

On Thu, Jan 19, 2006 at 02:26:03PM -0500, Dan Sugalski wrote: > At 11:39 AM -0700 1/19/06, Kevin & Jessica Hermansen wrote: > >I'm looking to set up an informational database which will be > >accessed by the general public on our website (there will be no > >internal users). The database will li

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

Rich Shepard <[EMAIL PROTECTED]> writes: > On Thu, 19 Jan 2006, Doug McNaught wrote: > >> Did you tell pg_restore to read from a file? Otherwise it will try to read >> from your terminal, which probably isn't what you want. > > Doug, > >No, I didn't. I've no idea where the file was dumped, so

[GENERAL] 7.4.11 HP-UX ia64 build error

I'm having trouble building 32-bit pgsql 7.4.11 on the latest release of HP-UX 11.23 on ia64, and need a little help. Here's my compiler: cc: HP aC++/ANSI C B3910B A.06.05 [Jul 25 2005] Here's my error: cc -L../../src/port -Wl,+b -Wl,/opt/pgsql/installs/postgresql-7.4.11-32bit-aCC/lib -Wl,-z

[GENERAL] Indexes

Another little doubt: I've already asked that in #postgresql at freenode, but I didn't understand well. I have two tables: Books - book_id - name Authors - author_id - name One book can have many authors and one author can have many books. To make that possible, I need a third table: Intermed

[GENERAL] auto increment within a compound key

Title: auto increment within a compound key Hi, I want to create a compound primary key. The elements of this primary key should be the fields called nb (int) and d (double). nb should be default and autoincremented, so that the following four inserts insert into mytable (ts) values ( 1

Re: [GENERAL] A tale of two similar databases

Hi All, I haven't vacuum/analysed the D2 database or for that matter D1. In fact I have never used VACUUM before. Maybe that is the problem. What are the benefits of the VACUUM command in PostgreSQL? Thanks in advance - Kishore ---(end of broadcast)-

Re: [GENERAL] Problem with restoring database from 7.3.1 to 8.0.1

Hi Tom, thanks for your answer. I did some testing now but still have the same problem. Tom Lane wrote: >> Kai Hessing <[EMAIL PROTECTED]> writes: > [DB-Problem] > >> >> Hmm. This looks like a pg_dump bug, ie, issuing ALTER OWNER commands >> for the wrong index name (or, perhaps, issuing

[GENERAL] Need help in installing postgresql 8.1.2 on Windows

Sir I am trying to install Globus Toolkit4 on Windows machine. Postgresql8.1.2 is required software for installing GT4. But i cant get installation steps from anywhere.. Please help me and do reply Thanks Sarvjot Yahoo! Photos – Showcase holiday pictures in hardcover Photo Books. You desig

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

Rich Shepard <[EMAIL PROTECTED]> writes: >Well, so much for the book. It did seem to be rather sparse on the upgrade. > Hmmm-m-m. Wonder what is the most efficient way to get going again. Think > I'll try the 'pg_dumpall -format=c' from the old directory and see if there's > a new file there.

[GENERAL] Isolation level in a function

Hello, this is my first post, please don't shoot... I was just experimenting with transactions (PG 8.1), and there is something which puzzles me. If i write 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;' in my function, it breaks. Error informs me, that it was executed after some query, while it

Re: [GENERAL] Connections not closing

Ok, I simplified the problem. I tried just running psql from the command line, and I noticed that it opens two connection on two different ports, and it closes only one. For example I do the following in the command prompt: ---

Re: [GENERAL] A tale of two similar databases

On Tue, Jan 17, 2006 at 09:17:41PM -0800, [EMAIL PROTECTED] wrote: > I haven't vacuum/analysed the D2 database or for that matter D1. > In fact I have never used VACUUM before. > Maybe that is the problem. > > What are the benefits of the VACUUM command in PostgreSQL? See the section on vacuuming

Re: [GENERAL] Replicating a remote database (backuping)

Silas Justiniano <[EMAIL PROTECTED]> schrieb: > I was looking for ways to backup my remote database in a local > computer. I was talking to #postgresql folks, who said me to try sloty. > > Is sloty the best choice? Is there anything different? Only backup? I think, you can use pg_dump or pg_dumpa

[GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

It's time for me to stop procrastinating and upgrade postgres from -7.4.3 to -8.1.2. Unfortunately, I think that I got it wrong. Here's what I did on my Slackware-10.2 box: 1) cd to /usr/local/pgsql 2) su'd to postgres 3) ran 'pg_dumpall' 4) as root, ran 'upgradepkg slackware-8.1.2*.tgz

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

On Thu, 19 Jan 2006, Doug McNaught wrote: Furthermore, whenever you use pg_dump or pg_dumpall, you need to redirect it to a file: $ pg_dumpall > /var/tmp/backup.sql I was just about to try this; it seemed the way to go. I highly suggest you read: http://www.postgresql.org/docs/8.1/static/

[GENERAL] out parameters and SETOF

Hi, there is a way to use OUT parameters in conjunction with SETOF? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [GENERAL] A tale of two similar databases

On Thu, 2006-01-19 at 16:46, Michael Glaesemann wrote: > On Jan 18, 2006, at 14:17 , [EMAIL PROTECTED] wrote: > > > What are the benefits of the VACUUM command in PostgreSQL? > > The docs have quite a bit of information on this. For a start, take a > look at: > http://www.postgresql.org/docs/cu

Re: [GENERAL] Connections not closing

"Nik" <[EMAIL PROTECTED]> writes: > Ok, I simplified the problem. I tried just running psql from the > command line, and I noticed that it opens two connection on two > different ports, and it closes only one. > For example I do the following in the command prompt: > C:\> psql -h host_name -p 5432

Re: [GENERAL] [PATCH] Better way to check for getaddrinfo function.

Where are we on this? Rajesh, I think we are waiting for more information from you. --- R, Rajesh (STSD) wrote: > > That was very much situation specific. > But the bottomline is the default test does not include in the >

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

On Thu, 19 Jan 2006, Doug McNaught wrote: Did you tell pg_restore to read from a file? Otherwise it will try to read from your terminal, which probably isn't what you want. Doug, No, I didn't. I've no idea where the file was dumped, so I've no idea of the name or location. Reading in Dougl

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

On Thu, 19 Jan 2006, Doug McNaught wrote: Did you tell pg_restore to read from a file? Otherwise it will try to read from your terminal, which probably isn't what you want. Doug, Here's what I have in /var/lib/: drwxr-x--- 3 postgres postgres 104 2006-01-19 12:49 pgsql/ drwx-- 3 p

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

On Thu, 19 Jan 2006, Doug McNaught wrote: pg_dumpall writes to standard output, so you should have seen an enormous spew of data at your terminal. I'm surprised you didn't. The manpages in the official Postgres documentation are quite clear about the behavior of these utilities; your book doesn'

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

On Thu, 19 Jan 2006, Joshua D. Drake wrote: What happens when you psql -U postgres template1 ? A-ha! Welcome to psql 8.1.2 (server 7.4.3), the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql command

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

This is Windows 2000 Server, 2.00GHz Pentium. Get your own "800" number Voicemail, fax, email, and a lot more http://www.ureach.com/reg/tag ---(end of broadcast)--- TIP 3: Have you checked our extensi

Re: [GENERAL] Indexes

On Jan 18, 2006, at 4:53 , Silas Justiniano wrote: CREATE UNIQUE INDEX foo ON Intermediate(book_id, author_id); You'll want this index for normalization. CREATE UNIQUE INDEX bar ON Intermediate(book_id); CREATE UNIQUE INDEX baz ON Intermediate(author_id); You probably don't want these two

Re: [GENERAL] A tale of two similar databases

On Jan 18, 2006, at 14:17 , [EMAIL PROTECTED] wrote: What are the benefits of the VACUUM command in PostgreSQL? The docs have quite a bit of information on this. For a start, take a look at: http://www.postgresql.org/docs/current/interactive/ maintenance.html#ROUTINE-VACUUMING Recently,

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

Rich Shepard <[EMAIL PROTECTED]> writes: >Using the 8.1.2 pg_dumpall from the 2004 data directory produces a 819K > file. But, when I then cd to the new data directory and run: > > [EMAIL PROTECTED]:/var/lib/pgsql/data$ pg_restore /var/tmp/backup.sql > pg_restore: [archiver] input file does no

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

On Thu, 19 Jan 2006, Doug McNaught wrote: Yes, as I said before, 'pg_dumpall' only produces SQL format dumps, which you restore using 'psql'. 'pg_restore' is only for binary dumps. Doug, I read the backup/restore web page to which you pointed me. I think that the new database is not initiat

Re: [GENERAL] A tale of two similar databases

On Tue, Jan 17, 2006 at 09:17:41PM -0800, [EMAIL PROTECTED] wrote: > Hi All, > > I haven't vacuum/analysed the D2 database or for that matter D1. > In fact I have never used VACUUM before. > Maybe that is the problem. > > What are the benefits of the VACUUM command in PostgreSQL? VACUUM is as cl

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

Rich Shepard wrote: On Thu, 19 Jan 2006, Doug McNaught wrote: Yes, as I said before, 'pg_dumpall' only produces SQL format dumps, which you restore using 'psql'. 'pg_restore' is only for binary dumps. Doug, I read the backup/restore web page to which you pointed me. I think that the new da

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

Rich Shepard <[EMAIL PROTECTED]> writes: > psql -f /var/tmp/backup.sql > I see: > psql: FATAL: database "postgres" does not exist This looks like you are trying to use an 8.1 psql to talk to a 7.4 postmaster. Database "postgres" should exist by default in an 8.1 installation but it would not in

Re: [GENERAL] out parameters and SETOF

On Thu, Jan 19, 2006 at 04:03:41PM -0500, Jaime Casanova wrote: > there is a way to use OUT parameters in conjunction with SETOF? Do you want to return a set of the OUT parameters or a set of something else? I don't think you can do the latter; for the former use SETOF record: CREATE FUNCTION fo

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

On Thu, 19 Jan 2006, Tom Lane wrote: This looks like you are trying to use an 8.1 psql to talk to a 7.4 postmaster. Database "postgres" should exist by default in an 8.1 installation but it would not in 7.4. Better check which postmaster is really running. Tom, Yes. That seems to have been

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

On Thu, 19 Jan 2006, Rich Shepard wrote: I've attached the serverlog. Oops! Let me try this again. Rich -- Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"

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

Vishal Dixit <[EMAIL PROTECTED]> writes: > I modified pg_dump.c and aded a sleep of 1000 micro sec in the > loop for copying data and it fixed the problem. It looks like > for slower machines when it comes to reading larger tables, > pg_dump finds no data to read in new lines and assumes EOL while

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

On Thu, 19 Jan 2006, Joshua D. Drake wrote: What happens when you psql -U postgres template1 ? Josh, OK. Now it's all straightened out on this side: [EMAIL PROTECTED]:/var/lib/pgsql$ psql -U postgres template1 Welcome to psql 8.1.2, the PostgreSQL interactive terminal. Type: \copyright f

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

On Thu, 19 Jan 2006, Rich Shepard wrote: However, I'm still getting a 500 server error from httpd when I try to log in to sql-ledger (http://localhost/sql-ledger/login.pl). What do I need to do to identify the reason? I stopped and restarted httpd. No relief there. Rich -- Richard B. Shep

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

No more server conflicts. Whew! It seems that I have postgres properly -- and fully -- upgraded. However, I'm still getting a 500 server error from httpd when I try to log in to sql-ledger (http://localhost/sql-ledger/login.pl). What do I need to do to identify the reason? What does y

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

On Thu, 19 Jan 2006, Joshua D. Drake wrote: What does your apache error log say? I am guess that DBD::Pg can no longer find libpq and you need to recompile DBD::Pg. Joshua, Yup. That seems to be the case. I'll dig in my stored messages for how to re-install DBD::Pg. Thanks, Rich -- Richa

[GENERAL] selecting array slice problem

Hi, Please help. I'm trying to access a bit array field that is declared as sp1 bit[][] When I attempt to access a slice of the array with the following command: SELECT sp1[1:2][1] I get the error: ' Field '2][1]' is of an unknown type'. Postgre seems to think that the characters following th

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

On Thu, 19 Jan 2006, Joshua D. Drake wrote: What does your apache error log say? I am guess that DBD::Pg can no longer find libpq and you need to recompile DBD::Pg. Josh, No, that's not quite right. I re-installed DBD::Pg and was told that it's up to date. Then I stopped and restarted ht

[GENERAL] What is the maximum length of an IN(a,b,c....d) list in PostgreSQL

What is the maximum length of an IN(a,b,cd) list in PostgreSQL? I am using 7.4. This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 3:

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

Sure enough. There is no pgsql.so on the system. I have php-4.4.1-i486-2 installed here; just upgraded from the -1 build. A Google search suggests that pgsql.so should be with either php or postgres; probably the former. If it is slackware I have no idea but on fedora it would be something

Re: [GENERAL] Upgrade Problem: 7.4.3 -> 8.1.2

On Thu, 19 Jan 2006, Joshua D. Drake wrote: If it is slackware I have no idea but on fedora it would be something like php-pgsql. Josh, I've searched the Slackware package repository and there is no php-pgsql. Patrick puts mysql.so in php, but not pgsql.so. If you are not using PHP then y

[GENERAL] How to convert Big5 to UTF8

Hi, All, I create a database which encoding is UTF8, and create a table test create table test ( name varchar); I insert a data '\244\350' encoding is Big5, I wish translate them to UTF8, so I use insert into test values ('convert('\244\350' using big5_to_utf_8)); I got an error: ERROR:

  1   2   >