[GENERAL] close connection more expensive that open connection?

[Rob Sargent]

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
server wasn't returning to the essentially backgrounded clean-up task
adroitly (enough)?

Don't worry we are now using a connection pool for the migration, but
the situation was, um, er, entertaining.  The server side said
eof-from-client, the client side said many variations of cannot-connect,
then they would work out their differences briefly and do the same dance
over again after sufficient connections.

Cheers,
rjs



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] data type

[Nick Raj]

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
with boundary_geom and returns true/false. It returns true/false 20,000
times
I want to write function that return only one true/false according to my
calculation.

So, create or replace function ST_ABC(?, geometry) returns boolean

Which type of data type will be used in above function (in place of ?)
that can collect more than one row(20,000) ?

Thanks
Raj

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

[Craig Ringer]

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

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Jasmin Dizdarevic]

@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 error in Postgresql 9.0.1.
> >
> > update import_details_test
> > set data_row = '["4","1 Monor JoÃ\u083ão S. AntÃ\u0083ão
> ^^
>
> Because your email client may have transformed the text encoding, I
> can't make any certain conclusions about what you're actually sending to
> the database, but it's highly likely that you're sending latin-1 encoded
> text to the database while your client_encoding is set to 'utf8'.
>
> The marked text is most likely the problem... but I think there's more
> wrong with it than just being latin-1 encoded. That kind of mangling
> often comes about when utf-8 text has been incorrectly interpreted as
> latin-1 and modified, or when something has incorrectly tried to do
> utf8<->latin-1 conversions more than once. You really need to figure out
> what encoding your input is in, convert it to a known encoding like
> utf-8 *once*, and keep it that way.
>
> If you're using Python, which I suspect you might be, the "".decode()
> function is useful. For example, I can convert a latin-1 encoded byte
> string to a python Unicode string with:
>
>   "somelatin1string".decode("latin-1")
>
> Sometimes you can get away with just "SET client_encoding=latin-1" but
> in this case your string data looks like it's been mangled by more than
> just a single encoding mis-interpretation, so you'll probably just
> silently insert corrupt data by doing that. Don't. Fix your code so it
> knows what the text encoding of the input is.
>
> If you are, in fact, using Python, it's a really good idea to always
> "".decode() all your inputs so your internal processing is done in
> Unicode (UTF-16, in fact). Similarly, Qt programmers should convert
> everything to unicode QString as soon as possible and use that for all
> internal manipulation. It'll save a lot of pain.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

[Craig Ringer]

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 a variety of non-total failure conditions  (data corruption,
etc). Add the synchronization costs to the mix, and I don't see the appeal.

I think clustering _in general_ is becoming a big issue, but I don't
really see the appeal of shared-disk clustering personally.

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Jasmin Dizdarevic]

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 

> On Mon, Feb 28, 2011 at 12:13:32AM +0100, Jasmin Dizdarevic wrote:
> > My idea was the one, that john described: DML and DDL are done on the
> small
> > box and reporting on the "big mama" with streaming replication and hot
> > stand-by enabled. the only problem is that we use temp tables for
> reporting
> > purposes. i hope that the query duration impact with not using temp
> tables
> > will be equalized through running dml/ddl on the small box.
>
> By the way, despite my flip comment, it is entirely possible that what
> you need would be better handled by one of the other replication
> systems.  Slony is actually well-suited to this sort of thing, despite
> the overhead that it imposes.  This is a matter of trade-offs, and you
> might want to think about different roles for different boxes --
> especially since hardware is so cheap these days.
>
> A
>
> --
> Andrew Sullivan
> a...@crankycanuck.ca
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

[Merlin Moncure]

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 here that PG avoids redundant multiple
> calculations of an expression.
>
> Even so, have you thought about using subqueries?
>
>>   SELECT id, expensivefunc(value) AS score FROM mytable
>>      WHERE id LIKE '%z%' AND expensivefunc(value)>  0.5;
>
> SELECT id, expensivefunc(value) FROM (
> (SELECT id, value FROM mytable WHERE id LIKE '%z%')
> ) WHERE expensivefunc(value) > 0.5;
>
> or even
>
> SELECT id, score FROM (
> SELECT id, expensivefunc(value) AS score FROM (
> (SELECT id, value FROM mytable WHERE id LIKE '%z%')
> )
> ) WHERE score > 0.5

you missed the point: even when you use subqueries postgres can inline
them, 'unsubquerying' your query.  I think the OP nailed probably the
best and most logical approach -- use a CTE.  It's more formal, and
while not super efficient today, isn't terrible.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tom Lane]

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.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Hannes Erven]

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 .
> 
> Task manager is mis-leading as multiple processes are sharing memory. 
> You need process explorer

That's exactly why I did not use the "default" columns of the Task
Manager, but "virtual memory size". I now compared the numbers to the
"private memory" column of Process Explorer, and Process Explorer shows
about 800k even more usage the the Task Manager.

It is still about 5 MB of private memory per "idle" backend process. Is
there anything I can do to optimize?


Thanks again,

-hannes

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Ioana Danes]

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: "PostgreSQL General" 
> Received: Wednesday, March 2, 2011, 3:44 PM
> 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 it for the short term by
> unsetting
> enable_hashagg, but you really ought to fix the query
> instead.
> 
>            
> regards, tom lane
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Thomas Kellerer]

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 efficient 
and you may never rely on any implicit ordering.

If you need your rows sorted in a specific way, you have to use an ORDER BY 
clause. Everything else is doomed to fail someday.

Regards
Thomas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tom Lane]

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 it for the short term by unsetting
enable_hashagg, but you really ought to fix the query instead.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Ioana Danes]

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 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 integer);
> insert into tmp_1 values (1, 3);
> insert into tmp_1 values (1, 3);
> insert into tmp_1 values (1, 3);
> insert into tmp_1 values (1, 3);
> insert into tmp_1 values (1, 3);
> insert into tmp_1 values (1, 4);
> insert into tmp_1 values (1, 4);
> insert into tmp_1 values (1, 4);
> insert into tmp_1 values (1, 4);
> insert into tmp_1 values (1, 4);
> insert into tmp_1 values (1, 4);
> insert into tmp_1 values (1, 1029);
> insert into tmp_1 values (1, 1101);
> insert into tmp_1 values (13, 3);
> insert into tmp_1 values (13, 3);
> insert into tmp_1 values (13, 3);
> insert into tmp_1 values (13, 3);
> insert into tmp_1 values (13, 3);
> insert into tmp_1 values (13, 3);
> insert into tmp_1 values (13, 4);
> insert into tmp_1 values (13, 4);
> insert into tmp_1 values (13, 4);
> insert into tmp_1 values (13, 4);
> insert into tmp_1 values (13, 4);
> insert into tmp_1 values (13, 4);
> insert into tmp_1 values (13, 4);
> insert into tmp_1 values (13, 1029);
> insert into tmp_1 values (13, 1101);
> analyze tmp_1;
> SELECT distinct field2 FROM tmp_1 WHERE field1 = 13;
> 
> The result in postgres 8.3 is as follows:
> 3
> 4
> 1029
> 1101
> And it stays the same no matter what the physical order of
> the records is in the table. I can do random update and I
> get the same results. It looks like the result is ordered by
> the distinct fields...
> 
> The result in postgres 9.0 is as follows:
> 3
> 4
> 1101
> 1029
> not ordered by the distinct fields nor physical order...
> 
> I am wondering if there is a temporary solution (updates,
> indexes, ...) to order the result by field1 without changing
> the statement...
> 
> Thank you in advance,
> Ioana Danes
> 
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] select DISTINCT not ordering the returned rows

[Ioana Danes]

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 integer);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 1029);
insert into tmp_1 values (1, 1101);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 1029);
insert into tmp_1 values (13, 1101);
analyze tmp_1;
SELECT distinct field2 FROM tmp_1 WHERE field1 = 13;

The result in postgres 8.3 is as follows:
3
4
1029
1101
And it stays the same no matter what the physical order of the records is in 
the table. I can do random update and I get the same results. It looks like the 
result is ordered by the distinct fields...

The result in postgres 9.0 is as follows:
3
4
1101
1029
not ordered by the distinct fields nor physical order...

I am wondering if there is a temporary solution (updates, indexes, ...) to 
order the result by field1 without changing the statement...

Thank you in advance,
Ioana Danes




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to approach dynamic status reporting

[James B. Byrne]

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 application is such that
the current status of any given transaction is ultimately dependent
upon the contents of the log table entries associated with that
transaction. For example:

Given log entries for transaction X of aa99, bb88, cc77, the current
status of X might be: 'completed'.  However the status of X derived
from log entries of aa99, bb88, cc77, dd66 might be: 'under
revision', while that from log entries aa99, bb88, cc77, dd66, aa99
might be: 'in progress'.

I have that part implemented and, insofar as testing reveals,
working.  My problem is that I now wish to select transactions from
the parent table based upon their derived status values.  I can see
several ways to proceed.  For instance I could store the last
calculated status value as a column on the parent table and then use
a WHERE table.column IN selection.  First, however, I wish to
inquire if this sort of thing crops up elsewhere and, if so, how is
it handled?

Sincerely,

-- 
***  E-Mail is NOT a SECURE channel  ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte & Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Grant question

[Bosco Rama]

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.  Without the double-quotes
PG assumes lowercase.

HTH

Bosco.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Grant question

[S G]

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 ---
>
> ERROR:  syntax error at or near "B_USER"
> LINE 3: TO ROLE B_USER;
>     ^
>
> ** Error **
>
> ERROR: syntax error at or near "B_USER"
> SQL state: 42601
> Character: 42
>
>

Syntax in the docs seems to indicate that "TO ROLE B_USER;" should
just be "TO B_USER;"

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Grant question

[Bill Moran]

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 ---
> 
> ERROR:  syntax error at or near "B_USER"
> 
> LINE 3: TO ROLE B_USER;
> 
> ^
> 
> 
> 
> ** Error **
> 
> 
> 
> ERROR: syntax error at or near "B_USER"
> 
> SQL state: 42601
> 
> Character: 42

You're missing case folding.  B_USER != b_user, and when you don't put
quotes around it, the case is folded to lower case.

My personal experience advises against using case-sensitive anything in
an SQL database, but if you're going to do it, you have to do it
consistently.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Index question

[Michael Black]

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: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 INDEX?
> 
> Perhaps this would be useful:
> 
> http://www.postgresql.org/docs/9.0/static/index.html
> 
> And specifically:
> 
> http://www.postgresql.org/docs/9.0/static/sql-commands.html
> 
> JD
> 
> 
> > 
> > A
> > 
> > -- 
> > Andrew Sullivan
> > a...@crankycanuck.ca
> > 
> 
> -- 
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
> Consulting, Training, Support, Custom Development, Engineering
> http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
  

[GENERAL] Grant question

[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 ---



ERROR:  syntax error at or near "B_USER"

LINE 3: TO ROLE B_USER;

^



** Error **



ERROR: syntax error at or near "B_USER"

SQL state: 42601

Character: 42


  

Re: [GENERAL] how are you?

[John R Pierce]

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 sent through gmail.


mail headers show signs of being generated by a spam script rather than 
a proper email program, including forged 'Recieved' headers.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Index question

[Joshua D. Drake]

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 duplicates or not.  How do I accomplish this in PostgreSql?
> > 
> 
> CREATE INDEX?

Perhaps this would be useful:

http://www.postgresql.org/docs/9.0/static/index.html

And specifically:

http://www.postgresql.org/docs/9.0/static/sql-commands.html

JD


> 
> A
> 
> -- 
> Andrew Sullivan
> a...@crankycanuck.ca
> 

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Index question

[Scott Ribe]

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-dev.com/
(303) 722-0567 voice





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Index question

[Andrew Sullivan]

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 INDEX?

A

-- 
Andrew Sullivan
a...@crankycanuck.ca

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Scott Ribe]

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 database: create index foobaridx on foo(bar)...

-- 
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Index question

[Michael Black]

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

[Alban Hertroys]

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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Scott Mead]

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 layer and the "one session per view" pattern that is
> recommended for such applications.
> These sessions usually load a pile of data once to display to the user,
> and then occasionally query updates of this data or even fetch single
> rows over a long time (like a few hours).
>
> 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 .
>
>
Task manager is mis-leading as multiple processes are sharing memory.  You
need process explorer
http://technet.microsoft.com/en-us/sysinternals/bb896653 (or something like
it) to see real memory consumption per backend.  Adding up the columns in
task manager is wrong and most definitely scary if you believe it :-)

--Scott



>
> As there are many, many such server processes running, is there anything
> I can do to reduce/optimize the per-session memory footprint?
>
> I'm aware of the sort_mem etc. parameters
> (http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ) but
> these seem to only apply to the execution of queries, not to sessions
> that mainly "sit around waiting", right?
>
>
> Thank you for any hints!
>
>-hannes
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

[Hannes Erven]

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 such applications.
These sessions usually load a pile of data once to display to the user,
and then occasionally query updates of this data or even fetch single
rows over a long time (like a few hours).

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 .


As there are many, many such server processes running, is there anything
I can do to reduce/optimize the per-session memory footprint?

I'm aware of the sort_mem etc. parameters
(http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ) but
these seem to only apply to the execution of queries, not to sessions
that mainly "sit around waiting", right?


Thank you for any hints!

-hannes

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Rayner Julio Rodríguez Pimentel]

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 database is of
2,6 TB every year.
There are accessing 5000 clients to this database of which will be
accessed 500 concurrent clients at the same time.
There are the questions:
1.  Is capable PostgreSQL to support this workload? Some examples
better than this.
2.  It is a recommendation to use a cluster with load balancer and
replication for this situation? Which tools are recommended for this
purpose?
3.  Which are the hardware recommendations to deploy on servers? CPU,
RAM memory capacity, Hard disk capacity and type of RAID system
recommended to use among others like Operating System and network
connection speed.
Greetings and thanks a lot.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dynamic binding in plpgsql function

[Merlin Moncure]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dynamic binding in plpgsql function

[Merlin Moncure]

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 !
> And thank you for the link of interesting topic from past :-)

yes...in the old days there was a lot more reason to do this because
there was no 'using' for execute and it was a lot more difficult to
coerce record types, arrays, etc into text format for passing.  today,
I would probably use execute unless I already had C library in the
backend and was *really* concerned about speed.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dynamic binding in plpgsql function

[Pierre Racine]

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 4:31 AM, Pierre Racine wrote:
>
>> CREATE OR REPLACE FUNCTION ST_MyGeneralFunction(callback text)
>>RETURNS SETOF geomval AS $$
>>DECLARE
>>x integer;
>>y integer;
>>BEGIN
>>y := somecalculation;
>>x := 'callback'(y);  --This is what I need
>>RETURN x;
>>END;
>>$$ LANGUAGE 'plpgsql';
>>
>> I don't want to do an EXECUTE statement since I have no table to put after 
>> the FROM clause. I want
>to assign the resulting value directly to a variable like in my example.
>
>
>You don't need any table to assign value of function to Variable, if function 
>returning single value.
>You can use EXECUTE 'SELECT '||$1||'(y)' into x;
>
>Thanks & Regards,
>Vibhor Kumar
>EnterpriseDB Corporation
>The Enterprise PostgreSQL Company
>vibhor.ku...@enterprisedb.com
>Blog:http://vibhork.blogspot.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dynamic binding in plpgsql function

[Dmitriy Igrishin]

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 function with a text
> parameter
> >> >> being a callback function name so that my general function can call
> this
> >> >> callback function. e.g.:
> >> >>
> >> >> CREATE OR REPLACE FUNCTION ST_MyCallbackFunction(y int)
> >> >>RETURNS int AS $$
> >> >>DECLARE
> >> >>BEGIN
> >> >>RETURN someCalculationBasedOnY;
> >> >>END;
> >> >>$$ LANGUAGE 'plpgsql';
> >> >>
> >> >> CREATE OR REPLACE FUNCTION ST_MyGeneralFunction(callback text)
> >> >>RETURNS SETOF geomval AS $$
> >> >>DECLARE
> >> >>x integer;
> >> >>y integer;
> >> >>BEGIN
> >> >>y := somecalculation;
> >> >>x := 'callback'(y);  --This is what I need
> >> >
> >> > EXECUTE 'SELECT ' || callback || '($1)' USING y INTO x;
> >> >
> >> > there are no other way than EXECUTE
> >> >
> >> > attention - there is a sql injection risk
> >>
> >> another way if you are willing to write some C is to wrap and expose
> >> OidFunctionCall1 so it takes oid of function you want to call.
> >
> > As always, Merlin shows the uncommon thinking! :-)
> > Respect!
>
> Thank you, and I would like to be able to claim original invention of
> this idea, however I cannot: it came from none other than Tom
> 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 !
And thank you for the link of interesting topic from past :-)


> merlin
>



-- 
// Dmitriy.

Re: [GENERAL] Dynamic binding in plpgsql function

[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 function with a text parameter
>> >> being a callback function name so that my general function can call this
>> >> callback function. e.g.:
>> >>
>> >> CREATE OR REPLACE FUNCTION ST_MyCallbackFunction(y int)
>> >>    RETURNS int AS $$
>> >>    DECLARE
>> >>    BEGIN
>> >>        RETURN someCalculationBasedOnY;
>> >>    END;
>> >>    $$ LANGUAGE 'plpgsql';
>> >>
>> >> CREATE OR REPLACE FUNCTION ST_MyGeneralFunction(callback text)
>> >>    RETURNS SETOF geomval AS $$
>> >>    DECLARE
>> >>        x integer;
>> >>        y integer;
>> >>    BEGIN
>> >>        y := somecalculation;
>> >>        x := 'callback'(y);  --This is what I need
>> >
>> > EXECUTE 'SELECT ' || callback || '($1)' USING y INTO x;
>> >
>> > there are no other way than EXECUTE
>> >
>> > attention - there is a sql injection risk
>>
>> another way if you are willing to write some C is to wrap and expose
>> OidFunctionCall1 so it takes oid of function you want to call.
>
> As always, Merlin shows the uncommon thinking! :-)
> Respect!

Thank you, and I would like to be able to claim original invention of
this idea, however I cannot: it came from none other than Tom
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).

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump slow with bytea data

[Merlin Moncure]

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], giving ~50% performance boost.
>
> However, we experience heavy problems during our weekly backup of our
> database recently. We suspect the reason for this is that we changed
> some columns from text with base64-encoded binary stuff to bytea
> columns. This change affected a large fraction of the database (~400
> GB). Note that we ran VACUUM FULL on the tables affected.
>
> After this change our backup procedure heavily slowed down. Whereas it
> took about 8 hours before the change, pg_dump is still busy with the
> first table (keeping roughly 50GB) after 12 hours of backup. If I
> approximate the time to complete the backup based on this, the backup
> procedure would require factor 10 the time it required before the
> change. The command we run is simply:  pg_dump -f  -F c 
>
> The main reason for this immense slow-down was identified in [1] as the
> conversion of bytea into a compatible format (i.e. hex). However, given
> the size of the db, a factor 10 makes backups practically infeasible.

hm.  where exactly is all this time getting spent?  Are you i/o bound?
cpu bound? Is there any compression going on? Maybe this is a
performance issue inside pg_dump itself, not necessarily a text/binary
issue (i have a hard time believing going from b64->hex is 10x slower
on format basis alone).  Can you post times comparing manual COPY via
text, manual COPY via binary, and pg_dump -F c?

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dynamic binding in plpgsql function

[Dmitriy Igrishin]

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
> callback function. e.g.:
> >>
> >> CREATE OR REPLACE FUNCTION ST_MyCallbackFunction(y int)
> >>RETURNS int AS $$
> >>DECLARE
> >>BEGIN
> >>RETURN someCalculationBasedOnY;
> >>END;
> >>$$ LANGUAGE 'plpgsql';
> >>
> >> CREATE OR REPLACE FUNCTION ST_MyGeneralFunction(callback text)
> >>RETURNS SETOF geomval AS $$
> >>DECLARE
> >>x integer;
> >>y integer;
> >>BEGIN
> >>y := somecalculation;
> >>x := 'callback'(y);  --This is what I need
> >
> > EXECUTE 'SELECT ' || callback || '($1)' USING y INTO x;
> >
> > there are no other way than EXECUTE
> >
> > attention - there is a sql injection risk
>
> another way if you are willing to write some C is to wrap and expose
> OidFunctionCall1 so it takes oid of function you want to call.
>
As always, Merlin shows the uncommon thinking! :-)
Respect!


>
> merlin
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

Re: [GENERAL] Dynamic binding in plpgsql function

[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 callback 
>> function. e.g.:
>>
>> CREATE OR REPLACE FUNCTION ST_MyCallbackFunction(y int)
>>    RETURNS int AS $$
>>    DECLARE
>>    BEGIN
>>        RETURN someCalculationBasedOnY;
>>    END;
>>    $$ LANGUAGE 'plpgsql';
>>
>> CREATE OR REPLACE FUNCTION ST_MyGeneralFunction(callback text)
>>    RETURNS SETOF geomval AS $$
>>    DECLARE
>>        x integer;
>>        y integer;
>>    BEGIN
>>        y := somecalculation;
>>        x := 'callback'(y);  --This is what I need
>
> EXECUTE 'SELECT ' || callback || '($1)' USING y INTO x;
>
> there are no other way than EXECUTE
>
> attention - there is a sql injection risk

another way if you are willing to write some C is to wrap and expose
OidFunctionCall1 so it takes oid of function you want to call.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump slow with bytea data

[chris r.]

> 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/mailpref/pgsql-general

[GENERAL] pg_dump slow with bytea data

[chris r.]

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 problems during our weekly backup of our
database recently. We suspect the reason for this is that we changed
some columns from text with base64-encoded binary stuff to bytea
columns. This change affected a large fraction of the database (~400
GB). Note that we ran VACUUM FULL on the tables affected.

After this change our backup procedure heavily slowed down. Whereas it
took about 8 hours before the change, pg_dump is still busy with the
first table (keeping roughly 50GB) after 12 hours of backup. If I
approximate the time to complete the backup based on this, the backup
procedure would require factor 10 the time it required before the
change. The command we run is simply:  pg_dump -f  -F c 

The main reason for this immense slow-down was identified in [1] as the
conversion of bytea into a compatible format (i.e. hex). However, given
the size of the db, a factor 10 makes backups practically infeasible.

We do not see any good solution to our problem except COPYing all data
in BINARY format. We understand there is a tough trade-off between
backup portability and backup efficiency here. As Bernd mentioned in
[1], however, not in all cases portability is required - particularly
not in ours.

A switch for binary output in pg_dump, or some alternative way to export
data *consistently* in binary format would be ideal for us, and probably
some others storing bytea data. Or do you see an alternative way how we
could get around this issue? Obviously, having no backup or deleting the
binary stuff from the database are no serious options.

Thanks for any discussion input in advance,
Chris

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general