[GENERAL] Copy of a schema

Hi, I wonder whether it is possible to copy a schema with a postgreSQL command like "copy_schema schema_src schema_dest" ? It would be interesting for the development and the tests to be able to work on a copy of a schema without having to make a dump of database and to insert it in a new da

Re: [GENERAL] improve 'where not exists' query..

Noel Whelan wrote: I executed the following: EXPLAIN ANALYZE SELECT cwit.cempid FROM "cwItems" cwit WHERE (NOT (EXISTS (SELECT con.cempid FROM contacts con WHERE (con.cempid = cwit.cempid; It comes back with: Seq Scan on "cwItems" cwit (cost=0.00..8929702.11 rows=5132 width=8) (actual time

Re: [GENERAL] Copy of a schema

am 27.10.2005, um 11:13:43 +0200 mailte Frederic Massot folgendes: > Hi, > > I wonder whether it is possible to copy a schema with a postgreSQL command > like "copy_schema schema_src schema_dest" ? You can rename a schema. And, you can make a dump, then rename it, and then restore from backup.

Re: [GENERAL] a stored procedure ..with integer as the parameter

Title: Re: [GENERAL] a stored procedure ..with integer as the parameter My appliaction is in C++   and i am getting char* ..s which i need to insert into the table...and for insert i am calling a stored procedure.   But i need to form the call to the stored procedure with the above char*s

Re: [GENERAL] Why database is corrupted after re-booting

On 26 Oct 2005, at 19:43, snacktime wrote: I remember a few months back when someone hit the emergency power switch to the whole floor where we host at Internap. Subsequently the backup power system had a cascading failure. Livejournal, who also hosts there, was up all night and into t

[GENERAL] creating users in groups, in 8.1

Hello. I had the following function in Postgres 8.0.4 for creation of users inside existing groups. Now I need to adjust it for new Roles system. What do I neeed to change? Especially regarding: CMD := 'CREATE USER "' || l_username || '" WITH ENCRYPTED PASSWORD ''' || l_password || ''' IN GROUP

Re: [GENERAL] a stored procedure ..with integer as the parameter

surabhi.ahuja wrote: My appliaction is in C++ and i am getting char* ..s which i need to insert into the table...and for insert i am calling a stored procedure. But i need to form the call to the stored procedure with the above char*s as the argument. Fine - just make sure you validate your d

[GENERAL] Problem with SSL and postgreSQL 8.0.4

Hi, I have installed postgresql 8.0.4 onto my linux distro (SuSe 9.3 PRO). I configured the build to support SSL: ./configure --with-openssl To start postgresql I use the following command: /usr/local/pgsql/bin/postmaster -i -D /usr/local/pgsql/data In my data folder I have my server.crt and se

Re: [GENERAL] Why database is corrupted after re-booting

>> Why the corruption occurs ? > > Most likely because the IDE was caching the information. IDE drives > sometimes lie about having caching turned on or off. > >> Will NTFS file system prevent all corruptions ? > > No. Joshua, thank you. Please re-confirm. In the configuration 1. Windows XP 2.

[GENERAL] missing FROM clause ?

Hello. In Postgres 8.1 I have a message that there is a missing FROM clause in the following query:   select into l_validity "rok_valjanosti"."rok_valjanosti" FROM ( SELECT min("rok_valjanosti"."rv_id") AS "rv_id", "rok_valjanosti"."rok_valjanosti" FROM "rok_valjanosti" GROUP BY "rok_valjano

Re: [GENERAL] Problem with SSL and postgreSQL 8.0.4

On Thu, Oct 27, 2005 at 11:46:16AM +0100, Uzo Madujibeya wrote: > As you can see there is no message to say that the root.crt file is missing, > which I hav received in previous installs of postgresql, and of course when > I try to access postgresql via jboss 4 using an ssl url I get a message > sa

Re: [GENERAL] Why database is corrupted after re-booting

Andrus wrote: My problem: Sometimes I need also to run desktop (server and client in same desktop computer) applications with Postgres. Desktop computer have this config. It is not possible to force users to buy SCSI drives nor upses for each desktop computer. Can Firebird or SQLLite automatica

Re: [GENERAL] Problem with SSL and postgreSQL 8.0.4

On Thu, Oct 27, 2005 at 12:26:35PM +0100, Uzo Madu wrote: > On Thursday 27 October 2005 12:17, you wrote: > > Did you enable ssl in postgresql.conf? > > refresh my mind please, what is it i'm meant to alter in my postgresql.comf > file to enable ssl again? The "ssl" setting -- uncomment it and c

Re: [GENERAL] missing FROM clause ?

Zlatko Matić wrote: Hello. In Postgres 8.1 I have a message that there is a missing FROM clause in the following query: select into l_validity "rok_valjanosti"."rok_valjanosti" FROM ( SELECT min("rok_valjanosti"."rv_id") AS "rv_id", "rok_valjanosti"."rok_valjanosti" FROM "rok_valjanosti" GROUP B

Re: [GENERAL] Why database is corrupted after re-booting

> If data on your disk gets corrupted then NOTHING can guarantee to recover > your database - not PG, not Firebird, not Oracle. Richard, thank you for reply. I ask my questing more presicely: I have configuration like in my previous message. Hardware (IDE drive, computer) and software (Windows

Re: [GENERAL] Why database is corrupted after re-booting

On Thu, Oct 27, 2005 at 02:54:50PM +0300, Andrus wrote: > I have configuration like in my previous message. Hardware (IDE drive, > computer) and software (Windows XP) works according to vendor > specifications. > > If I turn power off by breaking power cord when Postgres server is busy, is > it

Re: [GENERAL] Why database is corrupted after re-booting

Andrus wrote: If data on your disk gets corrupted then NOTHING can guarantee to recover your database - not PG, not Firebird, not Oracle. Richard, thank you for reply. I ask my questing more presicely: I have configuration like in my previous message. Hardware (IDE drive, computer) and soft

Re: [GENERAL] Why database is corrupted after re-booting

>> If I turn power off by breaking power cord when Postgres server is busy, >> is it possible that >> after that SELECT * FROM anytable does not work ? > > It is always *possible*, but if your system isn't caching writes then it > is *very very* unlikely. The tricky bit is that a lot of IDE drive

Re: [GENERAL] Why database is corrupted after re-booting

Alex Stapleton <[EMAIL PROTECTED]> writes: > suspicion is that if the power failure isn't a particularly fast one, > (e.g. you overloaded a fuse somewhere, fuses are insanely slow to > fail compared to alternatives like MCBs) then your RAID card's RAM > will get corrupted as the voltage drops

Re: [GENERAL] Need help with INOUT mis-understanding

> Probably not, if the way you seem to expect it to work is like Oracle. > An INOUT parameter isn't some sort of modifiable by-reference variable, > it's just a shorthand for declaring an IN parameter and an OUT > parameter. > Thanks for the response. That makes a lot of sense but I guess I was w

Re: [GENERAL] Why database is corrupted after re-booting

In article <[EMAIL PROTECTED]>, Welty, Richard <[EMAIL PROTECTED]> wrote: >crappy disk drives and bad windows file systems, nothing more. Could even be crappy memory. -- http://yosemitecampsites.com/ ---(end of broadcast)--- TIP 4: Have you search

Re: [GENERAL] Why database is corrupted after re-booting

On 27 Oct 2005, at 14:57, Tom Lane wrote: Alex Stapleton <[EMAIL PROTECTED]> writes: suspicion is that if the power failure isn't a particularly fast one, (e.g. you overloaded a fuse somewhere, fuses are insanely slow to fail compared to alternatives like MCBs) then your RAID card's RAM will

Re: [GENERAL] Why database is corrupted after re-booting

Unless I missed something, I think you can select on a fresh install but not after. I doubt even an image could be switched but I could be wrong, I am too often. Troy ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ?

Re: [GENERAL] Need help with INOUT mis-understanding

Tom's right, As in the first message of this thread kindof shows; func2(INOUT) adds the var_1 to itself and ouputs back to func1 as the updated value. NOTICE: var_1 starts as 5 NOTICE: var_1 in func2 is 10 CONTEXT: PL/pgSQL function "func1" line 7 at assignment NOTICE: var_X Now is (10,

Re: [GENERAL] Why database is corrupted after re-booting

Cheaper solution is to get a second hard drive an put it in your computer as a slave yes you could xcopy your drive to some backup device then repartition and plop it back - that would take alot of work and involves DiskCopy/Ghost like software and has great risk. (Run Defrag first - Plus you

Re: [GENERAL] Why database is corrupted after re-booting

I couldn't load it on a FAT32 partition on an XP HOME pc. So I loaded it on the NTSF partition of the same drive. I don't know why it did & now doesn't work but it could be that you need to defrag and clear some space. To change partition types you need to re-format (resetting partitions will los

[GENERAL] pg_dump fails when it gets to table containing bytea

Could anyone suggest something that we can check to ascertain why pg_dumps fail?  The pg_dump for our database just started to fail this week.  Dumps of the same database succeeded just last week.  Moreover, we can create a new database using the database (that we are trying to dump) as a t

Re: [GENERAL] Why database is corrupted after re-booting

Alex Stapleton <[EMAIL PROTECTED]> writes: > The system RAM won't usually be supported by any batteries though, so > it will go crazy, copy corrupt data to the DIMMs on the RAID > controller, which then will refuse to write it to the disk until the > power comes up, and then write the bad dat

Re: [GENERAL] Need help with INOUT mis-understanding

"Troy" <[EMAIL PROTECTED]> writes: > P.S. - Tom if the return of func2 = var_X = (10,5) how can I parse the > varible out like: > var_Y = var_X[1] -- first ARRAY item > to get var_Y = 10? Try assigning the function result to a RECORD variable, perhaps SELECT * INTO rec FROM foo(...); The

Re: [GENERAL] Why database is corrupted after re-booting

On 27 Oct 2005, at 16:07, Tom Lane wrote: Alex Stapleton <[EMAIL PROTECTED]> writes: The system RAM won't usually be supported by any batteries though, so it will go crazy, copy corrupt data to the DIMMs on the RAID controller, which then will refuse to write it to the disk until the power co

Re: [GENERAL] pg_dump fails when it gets to table containing bytea

"Carlos Oliva" <[EMAIL PROTECTED]> writes: > We are getting the following error message whe we run "pg_dump -Ft name> > database.tar": > pg_dump: ERROR: canceling query due to user request This implies that something sent SIGINT to the backend process. We've heard some reports that suggest tha

Re: [GENERAL] Why database is corrupted after re-booting

Andrus wrote: QUANTUM FIREPALLP LM20.5 is a widely used ATA IDE drive. Where do find information does it implement write caching properly or not ? I don't think the manufacturers bother to make this sort of information available. Is there IDE drive compatibility list for Postgres ? No -

Re: [GENERAL] pg_dump fails when it gets to table containing bytea

Is your table really named "blob" ??? You said it fails when it gets to the table named "blob" not somewhere in the process of dumping the table "blob"... There might be a clue in that... What happens if yo rename the table to something other than an SQL reserverd word ? Although postgreSQL doe

Re: [GENERAL] SHA1 authentication

On Mon, Oct 24, 2005 at 11:33:50 +0200, Martijn van Oosterhout wrote: > > By all means, submit a patch but there's no real hurry right now. We > should probably move straight to something more secure anyway, maybe > SHA-256 or something. This makes more sense. There is little point in going to

Re: [GENERAL] improve 'where not exists' query..

I've created an index on contacts.cempid (I'd not even checked whether one existed); and the query is certainly improved. I'm wondering..I get the impression that the hash index is ideal in this case, technically, because I only intend to query with '='; but the btree index is a bit faster. I'll ne

Re: [GENERAL] alt+F not working after calling pg_dump

> .. and also > what happens if you just call a bat-file that does nothing and then > exits. After running bat file containing single line exit Alt+F key works. After running bat file containg 3 lines set pgpassword=x C:\PROGRA~1\POSTGR~1\8.0\PGADMI~1\pg_dump.exe -Z9 -b -v -f "I:\051027 DEMO bac

[GENERAL] Connections to DB

Title: Connections to DB How can I view the current, persistent connections to a database?  Is there a way to do this with 'psql' or with some other PostgreSQL bundled tool/utility (~pgsql_directory/bin), instead of resorting to the underlying operating system commands (ie: netstat -a | grep

Re: [GENERAL] Connections to DB

SELECT * FROM pg_stat_activity; Note that if you turn on stats_command_string you'll also be able to see what each connection is doing if you're connected as a superuser. On Thu, Oct 27, 2005 at 11:21:26AM -0600, Onyx wrote: > How can I view the current, persistent connections to a database? Is

[GENERAL] aix build question re: duplicate symbol warning

Hi all, I'm trying to build PG 8.1 beta on an AIX server. The 'make' finishes without errors, but I'm getting lots of duplicate symbol warnings like the following one. What am I to make of these? gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -fno-strict-aliasing -Wl,-bnoentr

Re: [GENERAL] aix build question re: duplicate symbol warning

Kevin Murphy <[EMAIL PROTECTED]> writes: > I'm trying to build PG 8.1 beta on an AIX server. > The 'make' finishes without errors, but I'm getting lots of duplicate > symbol warnings like the following one. What am I to make of these? > gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winlin

Re: [GENERAL] Why database is corrupted after re-booting

Actually, because I lost several thousands of dollars or equipement a couple of years ago, I recommended these "brickwall" products to a company. http://brickwall.com/index.htm We actually never deployed these units (grounding the communications lines ended up being a much cheaper solution) but I

Re: [GENERAL] SHA1 authentication

On 10/27/05, Bruno Wolff III <[EMAIL PROTECTED]> wrote: On Mon, Oct 24, 2005 at 11:33:50 +0200,  Martijn van Oosterhout wrote:>> By all means, submit a patch but there's no real hurry right now. We > should probably move straight to something more secure anyway, maybe> SHA-256 o

Re: [GENERAL] Why database is corrupted after re-booting

On Thu, 2005-10-27 at 15:14, Keith C. Perry wrote: > Actually, because I lost several thousands of dollars or equipement a couple > of > years ago, I recommended these "brickwall" products to a company. > > http://brickwall.com/index.htm > > We actually never deployed these units (grounding the

Re: [GENERAL] copy data from utf16 csv file

On Thu, Oct 27, 2005 at 03:30:43PM -0700, Shane wrote: > > Can anyone suggest how I can either get these into PG directly > or massage the file so as to be compatable? To my knowledge the only Unicode encoding used by Postgres is utf-8. Try 'recode' or 'iconv' on unix-like systems. A better text

Re: [GENERAL] Why database is corrupted after re-booting

w_tom wrote: Series mode protector will ignore or avoid THE one and essential component of an effective protection system - single point earth ground. Indeed. And yes, a high end data center should survive a lightning strike (as well as hospital's power systems, etc). Here's a nice articl