Re: [GENERAL] SUBSTRING performance for large BYTEA

Karsten Hilbert writes: > Well, in my particular case it isn't so much that I *want* > to access bytea in chunks but rather that under certain > not-yet-pinned-down circumstances windows clients tend to go > out-or-memory on the socket during *retrieval* (insertion is > fine, as is put/get access f

[GENERAL] query large amount of data in c++ using libpq

Hi all, i am using PQexecParams() to "SELECT" about 3 million record in C++, and it takes several minutes to make it done with used memory dramatically incresed(about 200MB). it seems when i using PQexecParams(), i can't use the query result before all the data is sent to client. is there somethin

Re: [GENERAL] Writing most code in Stored Procedures

Ron Johnson wrote: So why is Perl-SP-INSERT so much slower than Perl-SQL-INSERT? (I can imagine that the SP code path would be longer, but since IO is the slowest part of the system, I'm surprised that it's *that* much slower.) I'm guessing that since PG allows overloaded SP names, the slowne

[GENERAL] Seeking datacenter PITR backup procedures [RESENDING]

Greetings, We have several web applications with Pg 8.2.x running on isolated servers (~25). The database size on each machines (du -h pgdata) is ~2 GB. We have been using nightly filesystem backup (stop pg, tar backup to ftp, start pg) and it worked well. We would like to move to PITR backups si

Re: [GENERAL] Automating logins for mundane chores

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/18/07 21:10, Phoenix Kiula wrote: > On 18/08/07, Ron Johnson <[EMAIL PROTECTED]> wrote: >> >> On 08/18/07 06:02, Phoenix Kiula wrote: >> [snip] >>> Thanks for this. I am logged in as root. Put it there and it works. I >> Well, that's your first p

Re: [GENERAL] Automating logins for mundane chores

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Phoenix Kiula wrote: > On 18/08/07, Ron Johnson <[EMAIL PROTECTED]> wrote: >> -BEGIN PGP SIGNED MESSAGE- >> Hash: SHA1 >> >> On 08/18/07 06:02, Phoenix Kiula wrote: >> [snip] >>> Thanks for this. I am logged in as root. Put it there and it work

Re: [GENERAL] Automating logins for mundane chores

On 18/08/07, Ron Johnson <[EMAIL PROTECTED]> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 08/18/07 06:02, Phoenix Kiula wrote: > [snip] > > > > Thanks for this. I am logged in as root. Put it there and it works. I > > Well, that's your first problem. > > And second. And third.

Re: [GENERAL] SUBSTRING performance for large BYTEA

"Karsten Hilbert" <[EMAIL PROTECTED]> writes: > But maybe this can be nefariously interpreted such that I could sort-of > implement cutoff-based extended/external switching by prepending "alter > table ... set storage external/extended ..." to INSERTs/UPDATEs based on > bytea parameter size. Or e

Re: [GENERAL] SUBSTRING performance for large BYTEA

On Sat, Aug 18, 2007 at 01:51:18PM -0400, Tom Lane wrote: > Karsten Hilbert <[EMAIL PROTECTED]> writes: > > Would it be feasible to add an ALTER TABLE mode > > ... set storage externally-extended cutoff ... > > where is the user configurable size of the column > > data at which PostgreSQL sw

Re: [GENERAL] SUBSTRING performance for large BYTEA

On Sat, Aug 18, 2007 at 09:32:33PM +0100, Gregory Stark wrote: > I do have to wonder how you're getting the data *in* though. If it's large > enough to have to stream out like this then how do you initially load the > data? Well, in my particular case it isn't so much that I *want* to access bytea

Re: [GENERAL] SUBSTRING performance for large BYTEA

"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Well this is a guess, but: > > Set existing column to storage external > update existing column with existing data: > > UPDATE foo SET bar = bar; Well, not quite. That would actually reuse the toast pointer without decompressing it. We try to be cl

Re: [GENERAL] Writing most code in Stored Procedures

Ron Johnson wrote: Interesting. Does PG have to initiate the Perl interpreter every time you call a Perl-written SP? I mean the *application* language was Perl for both the inline insert and the proc call. The proc was written in plpgsql. ---(end of broadcast)--

[GENERAL] query large amount of data in c++ using libpq

Hi all, i am using PQexecParams() to "SELECT" about 3 million record in C++, and it takes several minutes to make it done with used memory dramatically incresed(about 200MB). it seems when i using PQexecParams(), i can't use the query result before all the data is sent to client. is there something

Re: [GENERAL] server closed the connection unexpectedly

This is pg_log : 2007-08-19 03:00:50 LOG: database system was shut down at 2007-08-19 02:58:26 Malay Peninsula Standard Time 2007-08-19 03:00:50 LOG: checkpoint record is at 0/75A808 2007-08-19 03:00:50 LOG: redo record is at 0/75A808; undo record is at 0/0; shutdown TRUE 2007-08-19 03:00:

Re: [GENERAL] server closed the connection unexpectedly

On 18/08/2007 19:30, Muhyiddin A.M Hayat wrote: somebody help me please You'll need to post a lot more information before anyone can help. Is there anything in the server log? - or the Windows event log? Ray. --- Raymond O'Donnell,

[GENERAL] server closed the connection unexpectedly

Dear all, i'm unable to connect postgres server with error : C:\Program Files\PostgreSQL\8.2\bin>psql -U postgres siakad Password for user postgres: psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the

[GENERAL] server closed the connection unexpectedly

Dear all, i'm unable to connect postgres server with error : C:\Program Files\PostgreSQL\8.2\bin>psql -U postgres siakad Password for user postgres: psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the

Re: [GENERAL] SUBSTRING performance for large BYTEA

On Sat, Aug 18, 2007 at 10:23:42AM -0700, Joshua D. Drake wrote: > >> SET STORAGE EXTERNAL (before storing anything in it...) See the > >> ALTER TABLE reference page. > > Now, to convert an existing bytea column I would need to add > > a new bytea column with "set storage external", move the > >

Re: [GENERAL] SUBSTRING performance for large BYTEA

Karsten Hilbert <[EMAIL PROTECTED]> writes: > Would it be feasible to add an ALTER TABLE mode > ... set storage externally-extended cutoff ... > where is the user configurable size of the column > data at which PostgreSQL switches from extended to external > storage strategy ? Actually, it

Re: [GENERAL] Writing most code in Stored Procedures

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ron Johnson wrote: > On 08/18/07 11:08, Joshua D. Drake wrote: >> Josh Tolley wrote: >>> On 8/18/07, Ron Johnson <[EMAIL PROTECTED]> wrote: Interesting. Does PG have to initiate the Perl interpreter every time you call a Perl-written SP? >>>

Re: [GENERAL] SUBSTRING performance for large BYTEA

On Sat, Aug 18, 2007 at 12:49:09PM -0400, Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > Should we consider setting storage external by default for the type? > > No. That would be counterproductive for the more typical case of bytea > values in the range of some-small-number

Re: [GENERAL] SUBSTRING performance for large BYTEA

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karsten Hilbert wrote: > On Sat, Aug 18, 2007 at 12:20:42PM -0400, Tom Lane wrote: > >> "Vance Maverick" <[EMAIL PROTECTED]> writes: >>> My question is about performance in the postgres server. When I execute >>> "SELECT SUBSTRING (my_bytea FROM ? FO

Re: [GENERAL] SUBSTRING performance for large BYTEA

On Sat, Aug 18, 2007 at 12:20:42PM -0400, Tom Lane wrote: > "Vance Maverick" <[EMAIL PROTECTED]> writes: > > My question is about performance in the postgres server. When I execute > > "SELECT SUBSTRING (my_bytea FROM ? FOR ?) FROM my_table WHERE id =3D ?", > > does it fetch the whole BYTEA into

Re: [GENERAL] SUBSTRING performance for large BYTEA

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: >> Should we consider setting storage external by default for the type? > > No. That would be counterproductive for the more typical case of bytea > values in the range of some-small-num

Re: [GENERAL] Blobs in Postgresql

Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/17/07 23:16, Merlin Moncure wrote: On 8/18/07, Ron Olson <[EMAIL PROTECTED]> wrote: The language is Java. I've made some tests and they work very well for 25meg filesworks exactly the way it should, first time. MySQL ha

Re: [GENERAL] SUBSTRING performance for large BYTEA

"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Should we consider setting storage external by default for the type? No. That would be counterproductive for the more typical case of bytea values in the range of some-small-number-of-kilobytes. Or at least I think that's more typical than values t

Re: [GENERAL] language interface in postgresql

David Fetter wrote: >> Dollar-quoting is a cute technical solution to that, but you can't >> deny that it's simpler if you just restrict the function language to >> be SQL-ish so that CREATE FUNCTION can parse it without any >> interesting quoting rules. So sayeth Oracle and the SQL standards >> c

Re: [GENERAL] Finding my database

On 18/08/2007 09:03, Adrian Pitt wrote: I have put v8.0 back on as you suggested, but so far it has made no difference. I am still unable to get the service restarted. When installing I changed the install to drive D, and also changed the data reference to the directory where I moved the data t

Re: [GENERAL] SUBSTRING performance for large BYTEA

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: > "Vance Maverick" <[EMAIL PROTECTED]> writes: >> My question is about performance in the postgres server. When I execute >> "SELECT SUBSTRING (my_bytea FROM ? FOR ?) FROM my_table WHERE id =3D ?", >> does it fetch the whole BYTEA into

Re: [GENERAL] Writing most code in Stored Procedures

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/18/07 11:08, Joshua D. Drake wrote: > Josh Tolley wrote: >> On 8/18/07, Ron Johnson <[EMAIL PROTECTED]> wrote: >>> Interesting. Does PG have to initiate the Perl interpreter every >>> time you call a Perl-written SP? >> IIRC PostgreSQL should on

Re: [GENERAL] SUBSTRING performance for large BYTEA

"Vance Maverick" <[EMAIL PROTECTED]> writes: > My question is about performance in the postgres server. When I execute > "SELECT SUBSTRING (my_bytea FROM ? FOR ?) FROM my_table WHERE id =3D ?", > does it fetch the whole BYTEA into memory? Or does it access only the > pages that contain the reques

Re: [GENERAL] Writing most code in Stored Procedures

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Josh Tolley wrote: > On 8/18/07, Ron Johnson <[EMAIL PROTECTED]> wrote: >> Interesting. Does PG have to initiate the Perl interpreter every >> time you call a Perl-written SP? > > IIRC PostgreSQL should only load the perl interpreter once per session

Re: [GENERAL] Writing most code in Stored Procedures

On 8/18/07, Ron Johnson <[EMAIL PROTECTED]> wrote: > Interesting. Does PG have to initiate the Perl interpreter every > time you call a Perl-written SP? IIRC PostgreSQL should only load the perl interpreter once per session. - Josh ---(end of broadcast)--

Re: [GENERAL] Partitioning

On 8/18/07, Julio Cesar Sánchez González <[EMAIL PROTECTED]> wrote: > Hi guys, > > It's natural what master table in the partitioning table contain data > (http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html) ? > or to be empty. I'm no partitioning expert, but I would say most of

Re: [GENERAL] Interpreting statistics collector output

Decibel! wrote: > On Aug 15, 2007, at 2:11 PM, Gregory Stark wrote: >> "Decibel!" <[EMAIL PROTECTED]> writes: >>> On Wed, Aug 15, 2007 at 01:26:02PM -0400, Steve Madsen wrote: On Aug 15, 2007, at 11:52 AM, Decibel! wrote: > I can't really think of a case where a seqscan wouldn't return all

Re: [GENERAL] Automating logins for mundane chores

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/18/07 06:02, Phoenix Kiula wrote: [snip] > > Thanks for this. I am logged in as root. Put it there and it works. I Well, that's your first problem. And second. And third. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eat

Re: [GENERAL] Writing most code in Stored Procedures

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/17/07 21:45, Steve Manes wrote: > Ron Johnson wrote: >>> Moving all the application-bound inserts into stored procedures didn't >>> achieve nearly the performance enhancement I'd assumed I'd get, which I >>> figured was due to the overhead of the

Re: [GENERAL] Transactional DDL

Scott Marlowe wrote: > On 8/14/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote: >> Hi, >> I read a few lines about SP compilation in postgres >> >> http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html >> >> 1. stored procedure compilation is transactional. >> "You ca

Re: [GENERAL] Blobs in Postgresql

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/17/07 23:16, Merlin Moncure wrote: > On 8/18/07, Ron Olson <[EMAIL PROTECTED]> wrote: >> The language is Java. I've made some tests and they work very well for 25meg >> filesworks exactly the way it should, first time. MySQL had all kinds of

[GENERAL] SUBSTRING performance for large BYTEA

I'm working on reading large BYTEA fields from PostgreSQL 8.1. (For legacy reasons, it's unattractive to move them to large objects.) I'm using JDBC, and as various people have pointed out , the standard stream-style access method ru

Re: [GENERAL] Automating logins for mundane chores

Phoenix Kiula wrote: > On 18/08/07, Magnus Hagander <[EMAIL PROTECTED]> wrote: >> Phoenix Kiula wrote: >>> I am writing some simple batch scripts to login to the DB and do a >>> pg_dump. Also, when I login to do my own SQL tinkering, I'd like not >>> to be asked for a password every time (which, fo

Re: [GENERAL] Automating logins for mundane chores

On 18/08/07, Magnus Hagander <[EMAIL PROTECTED]> wrote: > Phoenix Kiula wrote: > > I am writing some simple batch scripts to login to the DB and do a > > pg_dump. Also, when I login to do my own SQL tinkering, I'd like not > > to be asked for a password every time (which, for silly corporate > > re

Re: [GENERAL] Automating logins for mundane chores

Phoenix Kiula wrote: > I am writing some simple batch scripts to login to the DB and do a > pg_dump. Also, when I login to do my own SQL tinkering, I'd like not > to be asked for a password every time (which, for silly corporate > reasons, is quite a convoluted one). > > So I read up on .pgpass. W

[GENERAL] Automating logins for mundane chores

I am writing some simple batch scripts to login to the DB and do a pg_dump. Also, when I login to do my own SQL tinkering, I'd like not to be asked for a password every time (which, for silly corporate reasons, is quite a convoluted one). So I read up on .pgpass. Where should this file be located.

[GENERAL] Partitioning

Hi guys, It's natural what master table in the partitioning table contain data (http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html) ? or to be empty. Thanks for all. -- Regards, Julio Cesar Sánchez González www.sistemasyconectividad.com.mx blog: http://darkavngr.blogspot.com

Re: [GENERAL] Finding my database

I have put v8.0 back on as you suggested, but so far it has made no difference. I am still unable to get the service restarted. When installing I changed the install to drive D, and also changed the data reference to the directory where I moved the data to. I unchecked the initialize database clus