[GENERAL] close connection more expensive that open connection?

A developer here accidentally flooded a server with connection opens and closes, essentially one per transaction during a multi-threaded data migration process. We were curious if this suggests that connection clean up is more expensive than creation thereby exhausting resources, or if perhaps the

[GENERAL] data type

Hi, I am writing some function in postgres pl/sql. My function is of type St_ABC((select obj_geom from XYZ),(select boundary_geom from boundary)) I have table XYZ with 20,000 tuples and in boundary, i have only one geometry. In postgres, ST_intersects(obj_geom, boundary_geom) checks each obj_geom

Re: [GENERAL] ERROR: invalid byte sequence for encoding "UTF8": 0xc35c

On 03/03/11 09:18, Jasmin Dizdarevic wrote: > @ALL: Isn't it possible and wise to include an (optional) encoder in pgsql? > > we're importing a lot of data from textfiles, which are not utf-8. we > always have to change the encoding in another tool before using COPY. SET client_encoding -- Crai

Re: [GENERAL] ERROR: invalid byte sequence for encoding "UTF8": 0xc35c

@ALL: Isn't it possible and wise to include an (optional) encoder in pgsql? we're importing a lot of data from textfiles, which are not utf-8. we always have to change the encoding in another tool before using COPY. 2011/2/28 Craig Ringer > On 27/02/11 20:47, AI Rumman wrote: > > I am getting

Re: [GENERAL] PG on two nodes with shared disk ocfs2 & drbd

On 03/03/11 09:01, Jasmin Dizdarevic wrote: > hehe... > andrew, I appriciate pg and it's free open source features - maybe I've > chosen a wrong formulation. > in my eyes such a feature is getting more important nowadays. Why? Shared disk means shared point of failure, and poor redundancy against

Re: [GENERAL] PG on two nodes with shared disk ocfs2 & drbd

hehe... andrew, I appriciate pg and it's free open source features - maybe I've chosen a wrong formulation. in my eyes such a feature is getting more important nowadays. Postgresql-R and -XC are interesting ideas. thanks everybody for the comments regards, jasmin 2011/2/28 Andrew Sullivan >

Re: [GENERAL] how to avoid repeating expensive computation in select

On Tue, Mar 1, 2011 at 2:51 AM, Orhan Kavrakoglu wrote: >> I would like to know if there is a way in PostgreSQL to avoid repeating an >> expensive computation in a SELECT where the result is needed both as a >> returned value and as an expression in the WHERE clause. > > I think I've seen it said

Re: [GENERAL] Per-session memory footprint (9.0/windows)

Hannes Erven writes: > It is still about 5 MB of private memory per "idle" backend process. Is > there anything I can do to optimize? That sounds about the right ballpark for a working backend process with caches loaded up. If that's too much for you, you ought to be using connection pooling.

Re: [GENERAL] Per-session memory footprint (9.0/windows)

Scott, > It seems that each of the server postmaster.exe processes takes up > approx. 5 MB of server memory (the "virtual memory size" column in task > manager), and I guess this truly is the private memory these processes > require. This number is roughly the same for 8.4 and 9.0

Re: [GENERAL] select DISTINCT not ordering the returned rows

I totally agree with you and the problem is gonna be fixed. I just needed a temporary solution until the patch goes out. Thank you, Ioana --- On Wed, 3/2/11, Tom Lane wrote: > From: Tom Lane > Subject: Re: [GENERAL] select DISTINCT not ordering the returned rows > To: "Ioana Danes" > Cc: "P

Re: [GENERAL] select DISTINCT not ordering the returned rows

Ioana Danes, 02.03.2011 21:35: Hi Everyone, I would like to ask for your help finding a temporary solution for my problem. I upgraded postgres from 8.3 to 9.0.3 and I have an issue with the order of the returned rows. The database is free to return rows in any order it thinks is most efficie

Re: [GENERAL] select DISTINCT not ordering the returned rows

Ioana Danes writes: > I upgraded postgres from 8.3 to 9.0.3 and I have an issue with the order of > the returned rows. Your application is simply broken if it assumes that DISTINCT results in ordering the rows. The only thing that guarantees that is an ORDER BY. You could probably work around

Re: [GENERAL] select DISTINCT not ordering the returned rows

I found it: disabling enable_hashagg --- On Wed, 3/2/11, Ioana Danes wrote: > From: Ioana Danes > Subject: [GENERAL] select DISTINCT not ordering the returned rows > To: "PostgreSQL General" > Received: Wednesday, March 2, 2011, 3:35 PM > Hi Everyone, > > I would like to ask for your help fi

[GENERAL] select DISTINCT not ordering the returned rows

Hi Everyone, I would like to ask for your help finding a temporary solution for my problem. I upgraded postgres from 8.3 to 9.0.3 and I have an issue with the order of the returned rows. The following script is a simplification of my real case: create table tmp_1 (field1 integer, field2 intege

[GENERAL] How to approach dynamic status reporting

I have a situation whereby edi unit record files from an external system are read, parsed and loaded into a PostgreSQL database. As transmissions relating to each transaction are read a log table entry is made by transaction for each type of transmission encountered. The nature of the external ap

Re: [GENERAL] Grant question

Michael Black wrote: > > Ok. What am I missing here? B_USER is a defined Group Role > > CREATE ROLE "B_USER" You used double-quotes here. This will preserve case and any non-standard identifier characters (spaces, punctuation, etc.) > TO ROLE B_USER; And, thus, you need to use them here. W

Re: [GENERAL] Grant question

On Wed, Mar 2, 2011 at 2:30 PM, Michael Black wrote: > Ok.  What am I missing here?  B_USER is a defined Group Role > > CREATE ROLE "B_USER" >   NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; > > > GRANT SELECT PRIVILEGES > ON b.config_itm > TO ROLE B_USER; > > Nets this --

Re: [GENERAL] Grant question

In response to Michael Black : > > Ok. What am I missing here? B_USER is a defined Group Role > > > CREATE ROLE "B_USER" > > NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; > > > GRANT SELECT PRIVILEGES > > ON b.config_itm > > TO ROLE B_USER; > > Nets this --

Re: [GENERAL] Index question

Thank you for the links. > Subject: Re: [GENERAL] Index question > From: j...@commandprompt.com > To: a...@crankycanuck.ca > CC: pgsql-general@postgresql.org > Date: Wed, 2 Mar 2011 11:05:58 -0800 > > On Wed, 2011-03-02 at 13:45 -0500, Andrew Sullivan wrote: > > On Wed, Mar 02, 2011 at 06:31:57P

[GENERAL] Grant question

Ok. What am I missing here? B_USER is a defined Group Role CREATE ROLE "B_USER" NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; GRANT SELECT PRIVILEGES ON b.config_itm TO ROLE B_USER; Nets this --- ERROR: syntax error at or near "B_USER" LI

Re: [GENERAL] how are you?

On 03/02/11 6:31 AM, suresh ramasamy wrote: come here: My friends! I have ordered a laptop from online Quality is great!Price is low! Thousands of electronic products are waiting for you! Best wishes for you! 2nd spam from this gmail account in 3 days. email headers appear to show it was s

Re: [GENERAL] Index question

On Wed, 2011-03-02 at 13:45 -0500, Andrew Sullivan wrote: > On Wed, Mar 02, 2011 at 06:31:57PM +, Michael Black wrote: > > > > Ok. I have been working with databases a few years but my first real > > venture in to PostgreSql. I just want a plain simple index regardless if > > there are dupl

Re: [GENERAL] Index question

On Mar 2, 2011, at 11:43 AM, Michael Black wrote: > Thanks Scott. I just did not see the options in the PGAdmin III nor in the > doc at You may want to bookmark this: -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-d

Re: [GENERAL] Index question

On Wed, Mar 02, 2011 at 06:31:57PM +, Michael Black wrote: > > Ok. I have been working with databases a few years but my first real venture > in to PostgreSql. I just want a plain simple index regardless if there are > duplicates or not. How do I accomplish this in PostgreSql? > CREATE I

Re: *****SPAM***** [GENERAL] Index question

On Mar 2, 2011, at 11:31 AM, Michael Black wrote: > Ok. I have been working with databases a few years but my first real venture > in to PostgreSql. I just want a plain simple index regardless if there are > duplicates or not. How do I accomplish this in PostgreSql? Same as any other SQL data

[GENERAL] Index question

Ok. I have been working with databases a few years but my first real venture in to PostgreSql. I just want a plain simple index regardless if there are duplicates or not. How do I accomplish this in PostgreSql? Michael

Re: [GENERAL] pg_dump slow with bytea data

On 2 Mar 2011, at 9:35, chris r. wrote: > GB). Note that we ran VACUUM FULL on the tables affected. Did you also REINDEX them? Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4d6e8542235882633876383! -- Sent via pgsql-general mailing list

Re: [GENERAL] Per-session memory footprint (9.0/windows)

On Wed, Mar 2, 2011 at 11:07 AM, Hannes Erven wrote: > Folks, > > > I run a PG (currently 8.4, but will shortly migrate to 9.0) database on > Windows Server 2003 that supports a desktop application which opens a > few long-running sessions per user. This is due to the Hibernate > persistence laye

[GENERAL] Per-session memory footprint (9.0/windows)

Folks, I run a PG (currently 8.4, but will shortly migrate to 9.0) database on Windows Server 2003 that supports a desktop application which opens a few long-running sessions per user. This is due to the Hibernate persistence layer and the "one session per view" pattern that is recommended for su

[GENERAL] I need your help to get opinions about this situation

Hello to everybody, I have this situation that I would to know your opinions about it, to confirm some elements that secure me of use the amazing database system PostgreSQL. I have a database of 1000 tables, 300 of theirs are of major growing with 1 rows daily, the estimate growing for this dat

Re: [GENERAL] Dynamic binding in plpgsql function

On Wed, Mar 2, 2011 at 9:06 AM, Pierre Racine wrote: > Is EXECUTE slower than a direct assignment call? It is going to be slower, but how much slower and if it justifies the mechanism is going to be a matter of your requirements, definition of 'slow', and willingness to experiment. merlin -- S

Re: [GENERAL] Dynamic binding in plpgsql function

On Wed, Mar 2, 2011 at 8:54 AM, Dmitriy Igrishin wrote: > 2011/3/2 Merlin Moncure >> Lane...http://postgresql.1045698.n5.nabble.com/improvise-callbacks-in-plpgsql-td2052928.html >> (for whom nary a day goes by that I am not thankful for his tireless >> efforts). > > Ahh, thanks to Tom Lane then !

Re: [GENERAL] Dynamic binding in plpgsql function

Is EXECUTE slower than a direct assignment call? >-Original Message- >From: Vibhor Kumar [mailto:vibhor.ku...@enterprisedb.com] >Sent: 1 mars 2011 18:24 >To: Pierre Racine >Cc: pgsql-general@postgresql.org >Subject: Re: [GENERAL] Dynamic binding in plpgsql function > > >On Mar 2, 2011, at

Re: [GENERAL] Dynamic binding in plpgsql function

2011/3/2 Merlin Moncure > On Wed, Mar 2, 2011 at 8:34 AM, Dmitriy Igrishin > wrote: > > 2011/3/2 Merlin Moncure > >> On Tue, Mar 1, 2011 at 5:17 PM, Pavel Stehule > >> wrote: > >> > Hello > >> > > >> > 2011/3/2 Pierre Racine : > >> >> Hi, > >> >> > >> >> I would like to write a generic plpgsql

Re: [GENERAL] Dynamic binding in plpgsql function

On Wed, Mar 2, 2011 at 8:34 AM, Dmitriy Igrishin wrote: > 2011/3/2 Merlin Moncure >> On Tue, Mar 1, 2011 at 5:17 PM, Pavel Stehule >> wrote: >> > Hello >> > >> > 2011/3/2 Pierre Racine : >> >> Hi, >> >> >> >> I would like to write a generic plpgsql function with a text parameter >> >> being a ca

Re: [GENERAL] pg_dump slow with bytea data

On Wed, Mar 2, 2011 at 2:35 AM, chris r. wrote: > Dear list, > > As discussed extensively in the past [1], pg_dump tends to be slow for > tables that contain bytea columns with large contents. Starting with > postgres version 8.5 the COPY format of bytea was changed from escape to > hex [1], givin

Re: [GENERAL] Dynamic binding in plpgsql function

2011/3/2 Merlin Moncure > On Tue, Mar 1, 2011 at 5:17 PM, Pavel Stehule > wrote: > > Hello > > > > 2011/3/2 Pierre Racine : > >> Hi, > >> > >> I would like to write a generic plpgsql function with a text parameter > being a callback function name so that my general function can call this > callb

Re: [GENERAL] Dynamic binding in plpgsql function

On Tue, Mar 1, 2011 at 5:17 PM, Pavel Stehule wrote: > Hello > > 2011/3/2 Pierre Racine : >> Hi, >> >> I would like to write a generic plpgsql function with a text parameter being >> a callback function name so that my general function can call this callback >> function. e.g.: >> >> CREATE OR RE

Re: [GENERAL] pg_dump slow with bytea data

> As discussed extensively in the past [1] Argh, forgot to add the reference: [1]: http://archives.postgresql.org/pgsql-hackers/2009-05/msg00192.php Chris -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpr

[GENERAL] pg_dump slow with bytea data

Dear list, As discussed extensively in the past [1], pg_dump tends to be slow for tables that contain bytea columns with large contents. Starting with postgres version 8.5 the COPY format of bytea was changed from escape to hex [1], giving ~50% performance boost. However, we experience heavy prob