Re: [GENERAL] HA and Replication - how to choose among all the available solutions

On Fri, Nov 30, 2007 at 10:30:47PM +0100, Pascal Cohen wrote: I am facing a probably very common problem. I made a search in the recent archives and could find many posts related to my issue. But I did not get exactly "the answer" to my question. No, and I doubt you will. I don't

Re: [GENERAL] HA and Replication - how to choose among all the available solutions

On Mon, Dec 03, 2007 at 09:16:40AM +0100, Pascal Cohen wrote: * For High-availability, I mainly studied PgPool and Log-shipping (and in fact forgot Slony). Until now I feel more comfortable with Log-shipping because it seems safer (I am not sure I can't get some problems with sequences and curr

Re: [GENERAL] HA and Replication - how to choose among all the available solutions

On Mon, Dec 03, 2007 at 09:16:40AM +0100, Pascal Cohen wrote: > > * For High-availability, I mainly studied PgPool and Log-shipping (and > in fact forgot Slony). > Until now I feel more comfortable with Log-shipping because it seems > safer (I am not sure I can't get some problems with sequences

Re: [GENERAL] pgcrypto functions fail for asymmetric encryption/decryption

Am Fri, 30 Nov 2007 12:06:37 +0200 schrieb "Marko Kreen" <[EMAIL PROTECTED]>: Hi Marko, first of all, thank you for your help. Please find my answers below: > On 11/29/07, Stefan Niantschur <[EMAIL PROTECTED]> wrote: > > I have a table with userids and public keys. I want to write a > > functio

Re: [GENERAL] power failure....

I was able to get things up and running OK. Don't have any WAL that I'm aware of, but it managed to have another power failure hours later. I seems that the UPS is more POS than UPS. I think the battery is dead. On Dec 2, 2007, at 3:52 AM, Greg Smith wrote: On Sat, 1 Dec 2007, Joshua D.

Re: [GENERAL] Why LIMIT and OFFSET are commutative

Andrus wrote: >> Under what interpretation would the results differ? > > Results must differ for easy creation of LinQ-PostgreSQL driver. > If results are always the same , PostgreSQL should not allow to use both > order of clauses. > > Nicholas explains: > >Assuming the ordering is the same

Re: [GENERAL] Dump/Restore Large Object OID

Gurjeet Singh wrote: Why not give it a try once? Dump and restore once and see for yourself. You'd have done that by now, but if you haven't do give it a try instead of waiting any more. You may learn a thing or two in the process... On 11/29/07, Norberto Delle <[EMAIL PROTECTED]> wrote

Re: [GENERAL] Dump/Restore Large Object OID

Gurjeet Singh wrote: Why not give it a try once? Dump and restore once and see for yourself. You'd have done that by now, but if you haven't do give it a try instead of waiting any more. You may learn a thing or two in the process... On 11/29/07, *Norberto Delle* <[EMAIL PROTECTED]

[GENERAL] Archiving problem on Windows

Hi list, a have a problem using the following archiving command on windows: 'copy %p C:\\Archive\\DBArchive\\%f' The error message i get is saying that the file could not be copied because of error code 1. The strange thing is that in the archive directory there are files that were copied one or t

Re: [GENERAL] [HACKERS] Stored procedure issue

Hello What I have noticed is that when I don't use procedure at all,there's only 2-5 locks in pg_locks,after I start application which uses stored procedure the number in pg_locks increase rapidly to steady 75 even to 130 at certain moments. Any clue why procedure usage might increase locks so he

Re: [GENERAL] full_page_writes = off?

Greg Smith escribió: > On Sat, 1 Dec 2007, Tomasz Ostrowski wrote: > >> You can also use "hdparm -I" to check this - look for a "Write >> caching" in "Commands/features" section. If it has a "*" in front >> then it is enabled and dangerous. > > Right; using -I works with most Linux hdparm version

Re: [GENERAL] [HACKERS] Stored procedure issue

Hello On 03/12/2007, Dragan Zubac <[EMAIL PROTECTED]> wrote: > Hello > > What I have noticed is that when I don't use procedure > at all,there's only 2-5 locks in pg_locks,after I > start application which uses stored procedure the > number in pg_locks increase rapidly to steady 75 even > to 130 a

Re: [GENERAL] pgcrypto functions fail for asymmetric encryption/decryption

On 12/3/07, Stefan Niantschur <[EMAIL PROTECTED]> wrote: > > Or at least send key parameters (gpg --list-keys output). > > pub 1024D/0476AD06 2007-11-27 [verfällt: 2008-11-26] > uid Test User (Probebenutzer) <[EMAIL PROTECTED]> > sub 2048g/879D6C41 2007-11-27 [verfällt: 2008-11-26] Elgamal 2048 wo

Re: [GENERAL] full_page_writes = off?

On Mon, 3 Dec 2007, Martin Marques wrote: Out of ignorance, do RAID devices have write cache? I'm talking about RAID through hardware. Depends on the device. There are hardware RAID cards with cache, and ones without. Generally the ones with cache allow you to adjust whether the cache is u

Re: [GENERAL] Will PG use composite index to enforce foreign keys?

Scott Marlowe wrote: As a secondary question, is there any way I could have answered this myself, using analyze, the system catalogs, etc? ANALYZE DELETE doesn't seem to show the FK checking that must go on behind the scenes. You could have coded up an example to see if it worked I guess. H

Re: [GENERAL] HA and Replication - how to choose among all the available solutions

On Mon, Dec 03, 2007 at 09:57:30AM +0100, Pascal Cohen wrote: > > > What kind of hardware solutions do you know ? - I will look on my own > what I can find. Have a look at the discussion in the 8.3 manual, about shared disk and block level replication: http://www.postgresql.org/docs/8.3/static

[GENERAL] uninstall postgres 8.2

Hi, What is the best way to uninstall postgres? I'm planning to use postgres 8.3 beta and I would like to uninstall my earlier version 8.2.3. This is only a test database and so I'm not interested in upgrading or preserving anything from this version. Thanks josh

Re: [GENERAL] Will PG use composite index to enforce foreign keys?

"John Burger" <[EMAIL PROTECTED]> writes: > Scott Marlowe wrote: > >>> As a secondary question, is there any way I could have answered this >>> myself, using analyze, the system catalogs, etc? ANALYZE DELETE >>> doesn't seem to show the FK checking that must go on behind the scenes. >> >> You co

Re: [GENERAL] HA and Replication - how to choose among all the available solutions

Andrew Sullivan wrote: On Mon, Dec 03, 2007 at 09:57:30AM +0100, Pascal Cohen wrote: What kind of hardware solutions do you know ? - I will look on my own what I can find. Have a look at the discussion in the 8.3 manual, about shared disk and block level replication: http://w

Re: [GENERAL] 8.3, a few questions

On Mon, Dec 03, 2007 at 04:41:35PM +0100, Enrico Sirola wrote: > Dear all, > I'd like to use the upcoming release 8.3 for my next project. When is it > expected to be finalized? My deadline is 2nd quarter 2008, do you think > there is a chance for me to use it? Most likely. While there is no pro

[GENERAL] PostgreSQL on the internet

Hi I am kicking around the idea of using PostgreSQL as a web based service. Access to the site will be through a separate application/interface. The user's interface will install the database on entry to the website. When the user exits the site, the database will be dumped to the user's comput

Re: [GENERAL] uninstall postgres 8.2

On 03/12/2007 15:20, Josh Harrison wrote: What is the best way to uninstall postgres? What platform are you on? How did you install it in the first place? If you're on Windows, use the installer. If you're on Debian or Ubuntu and you used apt-get to install, use apt-get remove [...]. If you

Re: [GENERAL] uninstall postgres 8.2

Is this on windows or linux/unix? On windows run the setup/uninstaller from your control-panel. You can remove the data directory assuming PG is installed on c:\program files\postgresql\8.2\ that's it for windows :) On unix/linux, you should try to uninstall using your package manager f

[GENERAL] 8.3, a few questions

Dear all, I'd like to use the upcoming release 8.3 for my next project. When is it expected to be finalized? My deadline is 2nd quarter 2008, do you think there is a chance for me to use it? A second question (more technical). Is it possible to put an index on an xpath expression of an XML ty

Re: [GENERAL] uninstall postgres 8.2

Thanks. Ill just delete them josh On Dec 3, 2007 10:37 AM, Gregory Williamson < [EMAIL PROTECTED]> wrote: > josh -- > > Delete everything under the postgres directory (we put ours on > /apps/postgres.version.whatever.) to remove the executables/libs etc, and > delete the entire data directory th

Re: [GENERAL] uninstall postgres 8.2

josh -- Delete everything under the postgres directory (we put ours on /apps/postgres.version.whatever.) to remove the executables/libs etc, and delete the entire data directory that the initdb pointed to. e.g if $PGDATA is /data/postgres/billing, rm -rf /data/postgres/billing would get rid of

Re: [GENERAL] PostgreSQL on the internet

Bob Pawley wrote: Hi I am kicking around the idea of using PostgreSQL as a web based service. Access to the site will be through a separate application/interface. Why web-based if you're installing an application to the user's PC. Can someone tell me what criteria I need to look at in order

Re: [GENERAL] pgcrypto functions fail for asymmetric encryption/decryption

Am Montag, 3. Dezember 2007 schrieb Marko Kreen: Hi Marko, I finally made it. I created a brand-new key, reworked the query and voila. It seems that the GnuPG key has to be created with paramter --cipher-algo=blowfish before it can be used together with pgcrypto. The generated key with the def

Re: [GENERAL] PostgreSQL on the internet

On Dec 3, 2007 9:33 AM, Bob Pawley <[EMAIL PROTECTED]> wrote: > > Hi > > I am kicking around the idea of using PostgreSQL as a web based service. > Access to the site will be through a separate application/interface. > > The user's interface will install the database on entry to the website. When >

Re: [GENERAL] PostgreSQL on the internet

At the moment the database dump is 4.1 meg. I suspect the end result will be less than 10 meg including the user's information. Is there other size information you need? Bob - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Bob Pawley" <[EMAIL PROTECTED]> Cc:

Re: [GENERAL] PostgreSQL on the internet

Basically, the database will be used to build up an engineering document called a P&ID which traditionally comes in the form of a drawing. From the database I'll create a drwawing which the user can, if he wants, convert into a DXF file for use in AutoCad or Bentley drawing systems. The datab

Re: [GENERAL] PostgreSQL on the internet

Bob Pawley wrote: At the moment the database dump is 4.1 meg. I suspect the end result will be less than 10 meg including the user's information. Is there other size information you need? Well, you'll want estimates of: 1. Total number of users (assuming you don't drop the database on the

[GENERAL] rpm's for 8.3 beta 4

Hello is there going to be an rpm release of beta 4 (for RHEL 5)? It seems that beta 2 did have red hat rpms but beta 3 did not. Regards Mikko

Re: [GENERAL] "relation deleted while in use" encountered with Postgresql 8.0.8

On Nov 29, 2007 4:44 PM, Gautam Sampathkumar <[EMAIL PROTECTED]> wrote: > Hi, > > I'm using a python script w/ the PyGreSQL library to insert 1 billion rows > into a database table for an experiment (performing a commit every 10K > rows). My script failed at about 170M rows with the following excep

[GENERAL] stored procedures and dynamic queries

Any general rule about dynamically generated queries in stored procedures vs. performances? I was going to write stuff as simple as create or replace function EditQty(int, int, int, varchar(10)) returns boolean as ' declare _uid alias for $1; _aid alias for $2; _qty alias

[GENERAL] initdb - encoding question

Hi Im tying to initialize the cluster using initdb and encoding 'UTF8' initdb -E UTF8 -D /export/home/josh/postgres8.3/pgsql/data But I get this error The files belonging to this database system will be owned by user "josh". This user must also own the server process. The database cluster will be

Re: [GENERAL] initdb - encoding question

Josh Harrison wrote: Hi Im tying to initialize the cluster using initdb and encoding 'UTF8' initdb -E UTF8 -D /export/home/josh/postgres8.3/pgsql/data But I get this error The files belonging to this database system will be owned by user "josh". This user must also own the server process. The d

Re: [GENERAL] Will PG use composite index to enforce foreign keys?

Gregory Stark wrote: I guess a generalization of my question is whether the FK- checking machinery simply does a SELECT against the referencing column. It does Actually the query is (effectively, assuming your equality operators are named "=" and the columns match in type) SELECT 1 F

Re: [GENERAL] initdb - encoding question

Josh Harrison wrote: initdb -E en_CA.utf-8 -D /export/home/sjothirajah/postgres8.3/pgsql/data gives this error initdb: "en_CA.utf-8" is not a valid server encoding name My bad. Use UTF-8. brian ---(end of broadcast)--- TIP 5: don't forget to in

[GENERAL] Transaction problem

Hi everyone, I would like to know how can i do a simple transaction for this situation: I have n products in certain row of a table. When the user buys a product, the quantity of this product will be decreased. The user can only buy a product that has a quantity n > 0. This means that when the

Re: [GENERAL] HA and Replication - how to choose among all the available solutions

On Mon, Dec 03, 2007 at 04:16:04PM +0100, Pascal Cohen wrote: > needs fast connections like fiber channels ? What is the impact on I/O > and general DB perfs ? - Sorry if my question is stupid - not an expert > on such things. > And I suppose it is not very cheap ;) Well, if you think you're goi

Re: [GENERAL] Transaction problem

On Dec 3, 2007 12:27 PM, x asasaxax <[EMAIL PROTECTED]> wrote: > Hi everyone, > >I would like to know how can i do a simple transaction for this > situation: > > I have n products in certain row of a table. When the user buys a product, > the quantity of this product will be decreased. The user

Re: [GENERAL] stored procedures and dynamic queries

Ivan Sergio Borgonovo wrote: Any general rule about dynamically generated queries in stored procedures vs. performances? It's the same decision as any with any prepared plan vs plan-each-time trade-off. A query built using EXECUTE will have to be planned each time. That costs you something

Re: [GENERAL] pgcrypto functions fail for asymmetric encryption/decryption

On 12/3/07, Stefan Niantschur <[EMAIL PROTECTED]> wrote: > I finally made it. I created a brand-new key, reworked the query and voila. > > It seems that the GnuPG key has to be created with > paramter --cipher-algo=blowfish before it can be used together with pgcrypto. > The generated key with the

Re: [GENERAL] Transaction problem

What are you programing with?. are you using npgsql? Regards Cesar Alvarez. Hi everyone, I would like to know how can i do a simple transaction for this situation: I have n products in certain row of a table. When the user buys a product, the quantity of this product will be decreased.

Re: [GENERAL] PL/pgSQL and SETOF

On Nov 30, 2007 1:18 PM, Pavel Stehule <[EMAIL PROTECTED]> wrote: > On 30/11/2007, Cultural Sublimation <[EMAIL PROTECTED]> wrote: > > But still on that subject: is my version of get_items2 the simplest > > that is possible in PL/pgSQL? It seems awfully verbose compared to > > the SQL version... >

Re: [GENERAL] HA and Replication - how to choose among all the available solutions

Andrew Sullivan wrote: On Mon, Dec 03, 2007 at 04:16:04PM +0100, Pascal Cohen wrote: needs fast connections like fiber channels ? What is the impact on I/O and general DB perfs ? - Sorry if my question is stupid - not an expert on such things. And I suppose it is not very cheap ;) We

Re: [GENERAL] PL/pgSQL and SETOF

Hello > > > > no. Your simplest version is historic relict and is available only in > > sql language. I am not sure, maybe in C language too. > > It is extremely useful to be able call functions in this way. I > really wish it were possible to do this with pl/sql functions as > well... > > merlin

[GENERAL] postmaster logfile

What's a good way to start the postmaster, send the log info to a logfile somewhere, and return the linux prompt? v8.2.0 on suse64 Thanks -dave

Re: [GENERAL] postmaster logfile

On Dec 3, 2007 2:35 PM, Gauthier, Dave <[EMAIL PROTECTED]> wrote: > > What's a good way to start the postmaster, send the log info to a logfile > somewhere, and return the linux prompt? Use whatever startup script comes with the pacakge for your OS. I.e. in redhat or suse you should have a postgr

Re: [GENERAL] postmaster logfile

Well, I can start the server with postmaster -D /myplace/db ... and then... ^z bg ... to get to the prompt. But each/every time a message from the postmaster gets logged, it goes to stdout of the current window. I want it to go to a logfile. Attempts to redirect with "&" all seem to fa

[GENERAL] Disconnects hanging server

We have a dual 3.0 GHz Intel Dual-core Xserve, running Mac OS X 10.5.1 Leopard Server and PostgreSQL 8.2.5. When we disconnect several clients at a time (30+) in production, the CPU goes through the roof and the server will hang for many seconds where it is completely non- responsive. It see

[GENERAL] Moving pgstat.stat and pgstat.tmp

Hi, I'm currently doctoring a situation wherein we've got table inheritance scheme that over the years that has ballooned like only in your nightmares (think well over 100K tables + indexes on those). The obvious solution is to re-design the schema with a better partitioning scheme in mind

Re: [GENERAL] pgcrypto functions fail for asymmetric encryption/decryption

Am Montag, 3. Dezember 2007 schrieben Sie: > On 12/3/07, Stefan Niantschur <[EMAIL PROTECTED]> wrote: > > I finally made it. I created a brand-new key, reworked the query and > > voila. > > > > It seems that the GnuPG key has to be created with > > paramter --cipher-algo=blowfish before it can be u

Re: [GENERAL] PL/pgSQL and SETOF

On Dec 3, 2007 3:31 PM, Pavel Stehule <[EMAIL PROTECTED]> wrote: > > > no. Your simplest version is historic relict and is available only in > > > sql language. I am not sure, maybe in C language too. > > > > It is extremely useful to be able call functions in this way. I > > really wish it were p

Re: [GENERAL] Moving pgstat.stat and pgstat.tmp

Erik Jones <[EMAIL PROTECTED]> writes: > Hi, I'm currently doctoring a situation wherein we've got table > inheritance scheme that over the years that has ballooned like only > in your nightmares (think well over 100K tables + indexes on those). > The obvious solution is to re-design the sch

Re: [GENERAL] postmaster logfile

Gauthier, Dave escribió: > Well, I can start the server with > > postmaster -D /myplace/db > > ... and then... > ^z > bg > > ... to get to the prompt. But each/every time a message from the > postmaster gets logged, it goes to stdout of the current window. I want > it to go to a logfil

[GENERAL] Re-partitioning huge schema

Hi, I've inherited a database schema wherein the original developers took the inheritance mechanism to an extreme where new client accounts get 13 different tables of their own created for them. We're at the many tens of thousands of tables mark (well over 100K) and I'm going to be re-par

Re: [GENERAL] Moving pgstat.stat and pgstat.tmp

On Dec 3, 2007, at 4:16 PM, Tom Lane wrote: Erik Jones <[EMAIL PROTECTED]> writes: Hi, I'm currently doctoring a situation wherein we've got table inheritance scheme that over the years that has ballooned like only in your nightmares (think well over 100K tables + indexes on those). The obvious

Re: [GENERAL] Disconnects hanging server

On Dec 3, 2007, at 4:16 PM, Brian Wipf wrote: We have a dual 3.0 GHz Intel Dual-core Xserve, running Mac OS X 10.5.1 Leopard Server and PostgreSQL 8.2.5. When we disconnect several clients at a time (30+) in production, the CPU goes through the roof and the server will hang for many second

[GENERAL] 8.3 release notes

The release notes seem to be in two places, with slightly different information. The page Google sends back for most 8.3 queries http://www.postgresql.org/docs/8.3/static/release-8-3.html and the one you get from the PostgreSQL beta program link http://developer.postgresql.org/pgdocs/postgres/rel

Re: [GENERAL] Moving pgstat.stat and pgstat.tmp

Erik Jones <[EMAIL PROTECTED]> writes: > 8.2.5 on Solaris 10. Before we upgraded to 8.2.4 it was doing about > 65 Mbs/sec. Interestingly, a while back we were running with the > data directory mounted with forcedirectio and saw none of this, I'm > guessing that fsync calls would have someth

Re: [GENERAL] stored procedures and dynamic queries

On Mon, 03 Dec 2007 19:06:29 + Richard Huxton <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo wrote: > > Any general rule about dynamically generated queries in stored > > procedures vs. performances? > > It's the same decision as any with any prepared plan vs > plan-each-time trade-off.

Re: [GENERAL] Moving pgstat.stat and pgstat.tmp

On Dec 3, 2007, at 6:10 PM, Tom Lane wrote: Erik Jones <[EMAIL PROTECTED]> writes: 8.2.5 on Solaris 10. Before we upgraded to 8.2.4 it was doing about 65 Mbs/sec. Interestingly, a while back we were running with the data directory mounted with forcedirectio and saw none of this, I'm guessing

[GENERAL] CPU

is there much of a difference in performance between a XEON, dual core from intel and a dual core AMD 64 CPU? I need a bit of an upgrade and am not sure which, if any, have a significant advantage for postgres databases. ---(end of broadcast)-

Re: [GENERAL] Moving pgstat.stat and pgstat.tmp

Erik Jones <[EMAIL PROTECTED]> writes: > For now, is renaming the > #define'd paths for the stats file and temp file sufficient for > moving them? I would think so, but haven't tried it. There definitely shouldn't be anything outside pgstat.c that's touching them. reg

[GENERAL] libpq messages language

Hi, I am using Windows, and pg 8.2.5 When making a connection with libpq, if it fails I would like to get the errors messages in spanish (PQerrorMessage ) Is this possible? How can this be done? thanks

Re: [GENERAL] CPU

On Monday 03 December 2007, Tom Allison wrote: > is there much of a difference in performance between a XEON, dual > core from intel and a dual core AMD 64 CPU? > > I need a bit of an upgrade and am not sure which, if any, have a > significant advantage for postgres databases. > Personally I've ne

Re: [GENERAL] CPU

Uwe C. Schroeder wrote: On Monday 03 December 2007, Tom Allison wrote: is there much of a difference in performance between a XEON, dual core from intel and a dual core AMD 64 CPU? Well honestly, with how cheap you can get a quad core from Intel... I say do that :). The general difference bet

Re: [GENERAL] CPU

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/03/07 21:27, Joshua D. Drake wrote: > Uwe C. Schroeder wrote: >> On Monday 03 December 2007, Tom Allison wrote: >>> is there much of a difference in performance between a XEON, dual >>> core from intel and a dual core AMD 64 CPU? > > Well honest

Re: [GENERAL] CPU

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 03 Dec 2007 22:30:58 -0600 Ron Johnson <[EMAIL PROTECTED]> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 12/03/07 21:27, Joshua D. Drake wrote: > > Uwe C. Schroeder wrote: > >> On Monday 03 December 2007, Tom Allison wrote:

Re: [GENERAL] CPU

On Mon, 3 Dec 2007, Joshua D. Drake wrote: Well honestly, with how cheap you can get a quad core from Intel... I say do that Exactly, the budget single processor configuration to beat in server land right now is the Xeon X3210. The frequency of the cores is a little on the low side, so indi

[GENERAL] limits

Hi All. My question is simple and plain: Are there some limit in the number of database operations between a BEGIN statement and a COMMIT statement? Thanks in advance. Luca -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Un look da modella in pochi se