[GENERAL] Strange problem with string and select

[Condor]

Hello,
can I ask is exist some kind of automatic escape string in postgresql ?
I use pgsql 9.1.5 and I have very interest problem, I have field with 
text string that I cant find normally.

Variable encoding from variables:

 server_encoding | WIN1251
 lc_collate  | bg_BG.CP1251
 lc_ctype| bg_BG.CP1251
 lc_messages | bg_BG.CP1251
 lc_monetary | bg_BG.CP1251
 lc_numeric  | bg_BG.CP1251
 lc_time | bg_BG.CP1251
 client_encoding | WIN1251

Here is examples (I replace in example Cyrillic encoding because most 
of the ppl don't have cp1251 encoding)


select * from postcodes where namejr LIKE 'LULIN V%';
id | namejr
21 | LULIN VIII
22 | LULIN VII
23 | LULIN VIII
24 | LULIN VI
25 | LULIN VII
26 | LULIN V

buf if I do:
select * from postcodes where namejr LIKE 'LULIN VII%';
result is:
22 | LULIN VII
25 | LULIN VII


as I can see ids 21 and 23 missing that should be: LULIN VIII
I dump follow records to text file and make hex compare,
both ids 21 and 23 is the same (equal).

Any one can give me a little help?


Hristo C.





--
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] String comparision in PostgreSQL

[Nicola Cisternino]

Il 29/08/2012 18.09, Chris Angelico ha scritto:

On Thu, Aug 30, 2012 at 1:56 AM, Nicola Cisternino  wrote:

The same query using "  LIKE  " is completed in 15 ms while
using "  ILIKE  " the execution time is 453 ms 

Sounds to me like (pun not intended) there's an index that's being
used in one case and not in the other.

But taking this back a step: Do you really need case-insensitive
comparisons? They become pretty much impossible once you start looking
at internationalization. Sure, it's easy in ASCII. You just mask off
one bit and off you go. But truly case insensitive matching gets
really hairy. Can you redo things with case sensitive searches,
possibly with some forcing of case in simple situations? For instance,
you accept a name prefix from the user, look at it and find that it's
all ASCII; lower-case it, then upper-case the first letter, then add a
percent sign, and use a case-sensitive LIKE. That's going to produce
correct results in most cases, and is way faster than truly case
insensitive searching.

ChrisA


Yes I need case-insensitive comparision and the best way to (optionally) 
obtain it is without alter all application queries that already works 
with other DB engine (!!)

My simply questions are:
1) Can be a custom collation a solution for my needs ?
2) How can create a custom collation (... what steps ...) ?
Thanks.

Re: [GENERAL] Strange problem with string and select

[Alban Hertroys]

On 30 August 2012 10:12, Condor  wrote:
> Hello,
> can I ask is exist some kind of automatic escape string in postgresql ?
> I use pgsql 9.1.5 and I have very interest problem, I have field with text
> string that I cant find normally.

> Here is examples (I replace in example Cyrillic encoding because most of the
> ppl don't have cp1251 encoding)
>
> select * from postcodes where namejr LIKE 'LULIN V%';
> id | namejr
> 21 | LULIN VIII
> 22 | LULIN VII
> 23 | LULIN VIII
> 24 | LULIN VI
> 25 | LULIN VII
> 26 | LULIN V
>
> buf if I do:
> select * from postcodes where namejr LIKE 'LULIN VII%';
> result is:
> 22 | LULIN VII
> 25 | LULIN VII
>
>
> as I can see ids 21 and 23 missing that should be: LULIN VIII
> I dump follow records to text file and make hex compare,
> both ids 21 and 23 is the same (equal).
>
> Any one can give me a little help?

Perhaps the records with ids 21 and 23 have a lower-case 'l' ('L')
instead of an upper-case 'I' ('i'), or something similar? Are the
hex-codes for ids 21, 22, 23 and 25 the same for the substring reading
'VII'?

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.


-- 
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][postgis-users] pg_dump -s should use add_geometrycolumn(...)

[Willy-Bas Loos]

On Wed, Aug 29, 2012 at 5:46 PM, Andres Freund wrote:

>
> Two things:
> * the geometry_columns table is not a table anymore but a view of the
> postgres
> catalogs
>
Great! I didn't know that yet. It must derive the info from the constraints
then.
So that means you can just create the column with standard DDL, and there
is no more redundant info. Nice :)


> * extensions (9.1+, used by postgis 2 onwards) can declare configuration
> tables now, so even if you would still need a configuration table it would
> get
> dumped in a schema only dump
>
That's really cool, advanced stuff. It's interesting that the functions
still reside in public, but people probably did spend a few thoughts on
that.
(why not a separate catalog with all the functions and types etc? hmz maybe
because you'd need to change the search_path, which is in postgresql.conf)
I'll look into the typemod stuff further.

>
>
On Wed, Aug 29, 2012 at 6:02 PM, Tom Lane  wrote:
>if postgis needs some info in a special table geometry_columns,
>that should be handled by pg_dump dumping and restoring that table too

Well, pg_dump -s doesn't dump the data, so it would then be missing. But it
is actually not data, but functional configuration. That's why creating
config tables for extensions (that do get dumped with a schema dump) is
awesome stuff! (even if postgis doesn't even use it now)

Thanks for your answers.
Cheers,

WBL
-- 
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

Re: [GENERAL] [BUGS] PostGreSQL pgdac - C++ Builder 2007

[Craig Ringer]

On 08/30/2012 04:11 PM, lacm...@sapo.pt wrote:


Yes, i am working with Borland/Embarcadero C++ builder, but i dont think
the problem has to do with builder.
I think the problem has to do with pgDac that i downloaded and installed.
I downloaded it from, http://www.devart.com/pgdac/download.html ( its
the 'PgDAC 3.2 for Delphi, C++Builder, and RAD Studio 2007' ).

I will try to contact someone from pgDac.


I'd say that's a good choice. They seem to have a support link on the 
bottom of their site that points to forums they say they respond to 
quickly. Try that.


BTW, if replying, please reply to the mailing list or use reply all, 
rather than replying to me directly.


--
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][postgis-users] pg_dump -s should use add_geometrycolumn(...)

[Craig Ringer]

On 08/30/2012 04:40 PM, Willy-Bas Loos wrote:


(why not a separate catalog with all the functions and types etc? hmz
maybe because you'd need to change the search_path, which is in
postgresql.conf)


You can choose which schema an extension goes into when you CREATE 
EXTENSION. See the documentation.


You don't have to set search_path in postgresql.conf ; it can be set 
per-session with `SET search_path` and can also be set per-user, 
per-database and per-function.


--
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] String comparision in PostgreSQL

[Nicola Cisternino]

Il 30/08/2012 4.01, Craig Ringer ha scritto:

On 08/28/2012 10:46 PM, Nicola Cisternino wrote:


1) Why PostgreSQL don't use COLLATE to manage case sensitive /
insensitive comparision (I think it's the best and ANSI standard way 
) ?


Support for per-column collations in PG was only added relatively 
recently - in 9.1, by the looks:


http://www.postgresql.org/docs/9.1/static/collation.html

Prior to that, there was no meaningful way to use case insensitive 
collations, as these would affect the whole database, including system 
tables, which could break all sorts of things in new and exciting ways.


With the advent of per-column and per-operation collation control, 
case-insensitive collations become very appealing.


One of the challenges with adding case insensitive collations is that, 
AFAIK, collations are implemented using the operating system charset 
and locale support, which may not offer case insensitive collation 
directly.


Another challenge is the rather ... variable ... meaning of "case 
insensitive". Results are likely to vary between host platforms and 
versions.


Still, now that per-col / per-op collation is supported, it'd be nice 
to have. I don't know if it's just a matter of needing someone with 
the desire and time (or funding) and expertise to design and build it, 
or if there'd be issues with getting it accepted.


--
Craig Ringer

Thus the problem is that "  collations are implemented using the 
operating system charset and locale support ... " while, other engines, 
implements collations internally . is it right ?

Thanks.

Re: [GENERAL] PQfformat question and retrieving bytea data in C

[Jason Armstrong]

Thank-you for the thoughtful answers.

I have updated my C library to return the binary data correctly. I
note the restriction on not being able to retrieve different columns
in different formats.

I found that my perl DBI interface wasn't happy either with the
'escape' output format, so I changed bytea_output to 'hex', and
DBD::Pg is also happy now (I also read that the hex format is more
efficient).

-- 
Jason Armstrong


-- 
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] String comparision in PostgreSQL

[Craig Ringer]

On 08/30/2012 05:16 PM, Nicola Cisternino wrote:


Thus the problem is that "  collations are implemented using the
operating system charset and locale support ... " while, other engines,
implements collations internally . is it right ?


That's my understanding, but I don't know which other database systems 
you're talking about because you've never specifically named any.


It's entirely possible that some other DBMSs use the system locale and 
collation support with internal downcasing, for example.


All I know, I've already said, but I'm not an expert on Pg's innards.

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

[GENERAL] Need help on autovacuum in postgres 9.1.2

[Khangelani Gama]

Hi



I need help in turning off autovacuum in the Database that’s running
postgres 9.1.2 DB. I tried to turn it off by putting “off” in
postgresql.cont file and restarted the postmaster but when I run “*show
autovacuum;* “ query I still see autovacuum set on inside the database.



autovacuum



on

(1 row)





postgresql.conf currently looks as follows. Should I remove the hash sign
that’s before “autovacuum = off”?









#autovacuum = off   # Enable autovacuum subprocess?
'on'

#log_autovacuum_min_duration = -1   # -1 disables, 0 logs all actions
and

#autovacuum_max_workers = 3 # max number of autovacuum
subprocesses

#autovacuum_naptime = 1min  # time between autovacuum runs

#autovacuum_vacuum_threshold = 50   # min number of row updates before

#autovacuum_analyze_threshold = 50  # min number of row updates before

#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before
vacuum

#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before
analyze

#autovacuum_freeze_max_age = 2  # maximum XID age before forced
vacuum

#autovacuum_vacuum_cost_delay = 20ms# default vacuum cost delay for

# autovacuum, in milliseconds;

#autovacuum_vacuum_cost_limit = -1  # default vacuum cost limit for

# autovacuum, -1 means use







Please assist.







Thanks













*C O N F I D E N T I A L I T Y   N O T I C E*
The contents of and attachments to this e-mail are intended for the
addressee only, and may contain the confidential information of Argility
(Proprietary) Limited and/or its subsidiaries. Any review, use or
dissemination thereof by anyone other than the intended addressee is
prohibited. If you are not the intended addressee please notify the writer
immediately and destroy the e-mail. Argility (Proprietary) Limited and its
subsidiaries distance themselves from and accept no liability for
unauthorised use of their e-mail facilities or e-mails sent other than
strictly for business purposes.





CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee 
only, and may contain the confidential information of Argility (Proprietary) 
Limited and/or its subsidiaries. Any review, use or dissemination thereof by 
anyone other than the intended addressee is prohibited.
If you are not the intended addressee please notify the writer immediately and 
destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries 
distance themselves from and accept no liability for unauthorised use of their 
e-mail facilities or e-mails sent other than strictly for business purposes.

Re: [GENERAL] String comparision in PostgreSQL

[Nicola Cisternino]

Il 30/08/2012 12.45, Craig Ringer ha scritto:

On 08/30/2012 05:16 PM, Nicola Cisternino wrote:


Thus the problem is that "  collations are implemented using the
operating system charset and locale support ... " while, other engines,
implements collations internally . is it right ?


That's my understanding, but I don't know which other database systems 
you're talking about because you've never specifically named any.


It's entirely possible that some other DBMSs use the system locale and 
collation support with internal downcasing, for example.


All I know, I've already said, but I'm not an expert on Pg's innards.

--
Craig Ringer

Tahnk you for replay.
At this point, the solution could be a new, custom, operating system 
collation  (something like: en_CI_US.UTF-8) 

Re: [GENERAL] Strange problem with string and select

[Condor]

On , Alban Hertroys wrote:

On 30 August 2012 10:12, Condor  wrote:

Hello,
can I ask is exist some kind of automatic escape string in 
postgresql ?
I use pgsql 9.1.5 and I have very interest problem, I have field 
with text

string that I cant find normally.


Here is examples (I replace in example Cyrillic encoding because 
most of the

ppl don't have cp1251 encoding)

select * from postcodes where namejr LIKE 'LULIN V%';
id | namejr
21 | LULIN VIII
22 | LULIN VII
23 | LULIN VIII
24 | LULIN VI
25 | LULIN VII
26 | LULIN V

buf if I do:
select * from postcodes where namejr LIKE 'LULIN VII%';
result is:
22 | LULIN VII
25 | LULIN VII


as I can see ids 21 and 23 missing that should be: LULIN VIII
I dump follow records to text file and make hex compare,
both ids 21 and 23 is the same (equal).

Any one can give me a little help?


Perhaps the records with ids 21 and 23 have a lower-case 'l' ('L')
instead of an upper-case 'I' ('i'), or something similar? Are the
hex-codes for ids 21, 22, 23 and 25 the same for the substring 
reading

'VII'?



Ah, you are absolute right, after an exhausting search I forgot to 
compare hex value of other strings.

in LULIN VIII last three hex strings is 0xb2 0xb2 0xb2
and in  LULIN VII last two hex strings is 0x49 0x49.

Thank you for the idea.


Hristo C.


--
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] String comparision in PostgreSQL

[Craig Ringer]

On 08/30/2012 06:54 PM, Nicola Cisternino wrote:

At this point, the solution could be a new, custom, operating system 
collation  (something like: en_CI_US.UTF-8) 


As far as I know - and as I said, I'm hardly an expert in Pg's guts - 
there's no way to create a case insensitive collation as things stand.


Creating case insensitive collations in the C library is probably more 
work than you could possibly expect, if it's even possible at all on 
some platforms.


You'd have a better chance investigating whether it could be possible to 
"wrap" an operating system collation with lower-casing. This is likely 
to be something you will want to work with some experienced Pg 
developers with, most likely on a funded work basis because you're 
unlikely to find anyone who wants to implement case insensitive 
collations for fun in their spare time.


If you're seriously interested in enhancing PostgreSQL to support case 
insensitive collation, and you have the funds to sponsor the significant 
amount of work required, you could ask on pgsql-hackers and see if 
anyone's interested, or contact 
http://www.postgresql.org/support/professional_support/ . This is not a 
trivial job.


It will help if you are able to provide *specific* *test* *cases* 
showing exactly how you think it should work, with samples showing how 
it works on other specifically named products. No more hand-waving.


--
Craig Ringer

Re: [GENERAL] Need help on autovacuum in postgres 9.1.2

[Albe Laurenz]

Khangelani Gama wrote:
> I need help in turning off autovacuum in the Database that's running
postgres 9.1.2 DB. I tried to
> turn it off by putting "off" in postgresql.cont file and restarted the
postmaster but when I run "show
> autovacuum; " query I still see autovacuum set on inside the database.
> 
> autovacuum
> 
> on
> (1 row)
> 
> postgresql.conf currently looks as follows. Should I remove the hash
sign that's before "autovacuum =
> off"?
> 
> #autovacuum = off   # Enable autovacuum
subprocess?  'on'
[snip]

Exactly.

See http://www.postgresql.org/docs/9.1/static/config-setting.html
  "Hash marks (#) designate the rest of the line as a comment."

Yours,
Laurenz Albe



-- 
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] Need help on autovacuum in postgres 9.1.2

[Craig Ringer]

On 08/30/2012 06:52 PM, Khangelani Gama wrote:

Hi

I need help in turning off autovacuum in the Database that’s running
postgres 9.1.2 DB. I tried to turn it off by putting “off” in
postgresql.cont


postgresql.conf, I presume.

Why do you want to turn autovaccum off? That's almost never the right 
thing to do; if anything you should usually be turning it *up*.


--
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] Dropping a column on parent table doesn't propagate to children?

[Sergey Konoplev]

On Wed, Aug 29, 2012 at 5:48 PM, Moshe Jacobson  wrote:
> 1. If I want the inherited table's columns indexed the same way as the
> parent, must I create new indexes on the inherited table?

You must. Indexes are not inheritable.

> 2. If I move the inherited table to a new schema, will its indexes also be
> moved into the new schema?

They will be.

> 3. Any difference in behavior regarding check constraints, schemas, indexes,
> etc that I should be aware of between inherited tables created with pure
> inheritance as opposed to "like "?

AIU you mean pure inheritance vs inheritance + pure LIKE. In this case
I do not see any.

>> > Also, another question about the docs. The syntax for the ALTER TABLE
>> > command starts as follows:
>> >
>> > ALTER TABLE [ ONLY ] name [ * ]
>> >
>> > What is the asterisk? It is not explained anywhere on that page.

As I was told in IRC:

RhodiumToad: grayhemp: opposite of ONLY, only present for historical
reasons since it's the default and has been for decades

so nothing important.

>> >
>> > Thanks,
>> > Moshe
>> >
>> > --
>> > Moshe Jacobson
>> > Nead Werx, Inc. | Senior Systems Engineer
>> > 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
>> > mo...@neadwerx.com | www.neadwerx.com
>> >
>>
>> --
>> Sergey Konoplev
>>
>> a database architect, software developer at PostgreSQL-Consulting.com
>> http://www.postgresql-consulting.com
>>
>> Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204
>
>
>
>
> --
> Moshe Jacobson
> Nead Werx, Inc. | Senior Systems Engineer
> 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
> mo...@neadwerx.com | www.neadwerx.com
>



-- 
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204


-- 
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] PQfformat question and retrieving bytea data in C

[Albe Laurenz]

Jason Armstrong wrote:
> I have updated my C library to return the binary data correctly. I
> note the restriction on not being able to retrieve different columns
> in different formats.

Actually, PostgreSQL supports that if you use the line protocol
to talk to the server (see the description of "Bind (F)" in
http://www.postgresql.org/docs/9.1/static/protocol-message-formats.html)
.

Alas, this is not supported by the C API.
Maybe that would be a useful extension to libpq.

Yours,
Laurenz Albe


-- 
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] PQfformat question and retrieving bytea data in C

[Dmitriy Igrishin]

2012/8/30 Albe Laurenz 

> Jason Armstrong wrote:
> > I have updated my C library to return the binary data correctly. I
> > note the restriction on not being able to retrieve different columns
> > in different formats.
>
> Actually, PostgreSQL supports that if you use the line protocol
> to talk to the server (see the description of "Bind (F)" in
> http://www.postgresql.org/docs/9.1/static/protocol-message-formats.html)
> .
>
> Alas, this is not supported by the C API.
> Maybe that would be a useful extension to libpq.
>
+1

-- 
// Dmitriy.

[GENERAL] Would my postgresql 8.4.12 profit from doubling RAM?

[Alexander Farber]

Hello,

I run CentOS 6.3 server with 16 GB RAM and:
postgresql-8.4.12-1.el6_2.x86_64
pgbouncer-1.3.4-1.rhel6.x86_64

The modified params in postgresql.conf are:
max_connections = 100
shared_buffers = 4096MB

and the pgbouncer runs with:
pool_mode = session
server_reset_query = DISCARD ALL;

The main app is a card game with 30-500
simultaneous users for which I save some
playing stats into the db +
PHP scripts to display those stats again.

I have an option to double the RAM for EUR 180,-
but wonder if it will improve any performance and
also what to do on the PostgreSQL side once
I've doubled the RAM (like double shared_buffers?
but how do I find out if it's needed, maybe they're empty?)

Below is a typical top output, the pref.pl is my game daemon:

top - 13:40:30 up 21 days,  5:11,  1 user,  load average: 0.61, 1.14, 1.31
Tasks: 232 total,   1 running, 231 sleeping,   0 stopped,   0 zombie
Cpu0  : 14.6%us,  0.3%sy,  0.0%ni, 84.4%id,  0.3%wa,  0.0%hi,  0.3%si,  0.0%st
Cpu1  :  3.0%us,  0.0%sy,  0.0%ni, 97.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu2  :  7.3%us,  0.0%sy,  0.0%ni, 92.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu3  :  7.3%us,  0.0%sy,  0.0%ni, 92.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu4  : 10.0%us,  0.0%sy,  0.0%ni, 90.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu5  :  2.3%us,  0.0%sy,  0.0%ni, 97.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu6  :  0.0%us,  0.3%sy,  0.0%ni, 99.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu7  :  1.7%us,  0.0%sy,  0.0%ni, 98.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  16243640k total, 14091172k used,  2152468k free,   621072k buffers
Swap:  2096056k total,0k used,  2096056k free,  8929900k cached

  PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND
19992 postgres  20   0 4378m 782m 775m S 11.0  4.9   2:28.58 postmaster
16184 nobody20   0  116m  21m 3908 S  9.0  0.1  22:01.32 pref.pl
16187 postgres  20   0 4375m 502m 497m S  7.3  3.2  37:13.48 postmaster
20229 postgres  20   0 4377m 426m 420m S  6.3  2.7   0:07.01 postmaster
20201 postgres  20   0 4378m 512m 505m S  4.7  3.2   0:23.65 postmaster
20135 postgres  20   0 4378m 771m 764m S  2.7  4.9   2:14.57 postmaster
20209 postgres  20   0 4377m 571m 564m S  2.0  3.6   1:14.34 postmaster
20030 postgres  20   0 4376m 890m 883m S  1.7  5.6   3:39.64 postmaster
20171 apache20   0  370m  30m  16m S  0.7  0.2   0:01.87 httpd
18986 apache20   0  371m  43m  28m S  0.3  0.3   0:11.47 httpd
19523 apache20   0  370m  32m  18m S  0.3  0.2   0:07.18 httpd
19892 apache20   0  380m  37m  19m S  0.3  0.2   0:04.86 httpd
20129 apache20   0  376m  37m  16m S  0.3  0.2   0:02.39 httpd
20335 root  20   0 15148 1416  996 R  0.3  0.0   0:00.13 top

Thank you
Alex


-- 
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] Need help on autovacuum in postgres 9.1.2

[Khangelani Gama]

Thanks at lot.



-Original Message-
From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at]
Sent: Thursday, August 30, 2012 1:22 PM
To: Khangelani Gama *EXTERN*; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Need help on autovacuum in postgres 9.1.2

Khangelani Gama wrote:
> I need help in turning off autovacuum in the Database that's running
postgres 9.1.2 DB. I tried to
> turn it off by putting "off" in postgresql.cont file and restarted the
postmaster but when I run "show
> autovacuum; " query I still see autovacuum set on inside the database.
>
> autovacuum
> 
> on
> (1 row)
>
> postgresql.conf currently looks as follows. Should I remove the hash
sign that's before "autovacuum =
> off"?
>
> #autovacuum = off   # Enable autovacuum
subprocess?  'on'
[snip]

Exactly.

See http://www.postgresql.org/docs/9.1/static/config-setting.html
  "Hash marks (#) designate the rest of the line as a comment."

Yours,
Laurenz Albe




CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee 
only, and may contain the confidential information of Argility (Proprietary) 
Limited and/or its subsidiaries. Any review, use or dissemination thereof by 
anyone other than the intended addressee is prohibited.
If you are not the intended addressee please notify the writer immediately and 
destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries 
distance themselves from and accept no liability for unauthorised use of their 
e-mail facilities or e-mails sent other than strictly for business purposes.



-- 
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] Would my postgresql 8.4.12 profit from doubling RAM?

[Craig Ringer]

On 08/30/2012 07:42 PM, Alexander Farber wrote:

Hello,

I run CentOS 6.3 server with 16 GB RAM and:
 postgresql-8.4.12-1.el6_2.x86_64
 pgbouncer-1.3.4-1.rhel6.x86_64

The modified params in postgresql.conf are:
 max_connections = 100
 shared_buffers = 4096MB

and the pgbouncer runs with:
 pool_mode = session
 server_reset_query = DISCARD ALL;

The main app is a card game with 30-500
simultaneous users for which I save some
playing stats into the db +
PHP scripts to display those stats again.

I have an option to double the RAM for EUR 180,-
but wonder if it will improve any performance and
also what to do on the PostgreSQL side once
I've doubled the RAM (like double shared_buffers?
but how do I find out if it's needed, maybe they're empty?)

Below is a typical top output, the pref.pl is my game daemon


What you really want to look at is the iowait%, which you aren't 
showing, and at vmstat output to see what kind of disk read rates you're 
doing.


Looking at `free -m` can be informative too; if your server has more 
than a few hundred MB of free memory there's unlikely to be much to gain 
by adding more RAM 'cos it can't find anything useful to do with what it 
already has. In practice this is rare.



--
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] String comparision in PostgreSQL

[F. BROUARD / SQLpro]

Le 30/08/2012 12:45, Craig Ringer a écrit :


That's my understanding, but I don't know which other database systems
you're talking about because you've never specifically named any.

In his primary post he talk about SQL Server, Sybase and MySQL wich does 
good jobs with collation


Almost a majority of RDBMS have collation support wich is very important 
for non english languages, that represents about 90% of the planet 
languages !


This feature has always been a "black spot" in PG, and the most 
important topic to discourage to use it in professionnal applications.


A +

--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR  :  http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*** http://www.sqlspot.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] Dropping a column on parent table doesn't propagate to children?

[Moshe Jacobson]

Perfect response, thank you Sergey.

On Thu, Aug 30, 2012 at 7:29 AM, Sergey Konoplev <
sergey.konop...@postgresql-consulting.com> wrote:

> On Wed, Aug 29, 2012 at 5:48 PM, Moshe Jacobson 
> wrote:
> > 1. If I want the inherited table's columns indexed the same way as the
> > parent, must I create new indexes on the inherited table?
>
> You must. Indexes are not inheritable.
>
> > 2. If I move the inherited table to a new schema, will its indexes also
> be
> > moved into the new schema?
>
> They will be.
>
> > 3. Any difference in behavior regarding check constraints, schemas,
> indexes,
> > etc that I should be aware of between inherited tables created with pure
> > inheritance as opposed to "like "?
>
> AIU you mean pure inheritance vs inheritance + pure LIKE. In this case
> I do not see any.
>
> >> > Also, another question about the docs. The syntax for the ALTER TABLE
> >> > command starts as follows:
> >> >
> >> > ALTER TABLE [ ONLY ] name [ * ]
> >> >
> >> > What is the asterisk? It is not explained anywhere on that page.
>
> As I was told in IRC:
>
> RhodiumToad: grayhemp: opposite of ONLY, only present for historical
> reasons since it's the default and has been for decades
>
> so nothing important.
>
> >> >
> >> > Thanks,
> >> > Moshe
> >> >
> >> > --
> >> > Moshe Jacobson
> >> > Nead Werx, Inc. | Senior Systems Engineer
> >> > 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
> >> > mo...@neadwerx.com | www.neadwerx.com
> >> >
> >>
> >> --
> >> Sergey Konoplev
> >>
> >> a database architect, software developer at PostgreSQL-Consulting.com
> >> http://www.postgresql-consulting.com
> >>
> >> Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204
> >
> >
> >
> >
> > --
> > Moshe Jacobson
> > Nead Werx, Inc. | Senior Systems Engineer
> > 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
> > mo...@neadwerx.com | www.neadwerx.com
> >
>
>
>
> --
> Sergey Konoplev
>
> a database architect, software developer at PostgreSQL-Consulting.com
> http://www.postgresql-consulting.com
>
> Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204
>



-- 
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
mo...@neadwerx.com | www.neadwerx.com

[GENERAL] Refreshing functional index

[Grzegorz Tańczyk]

Hello,

I have a problem with functional index feature in Postgres 8.3

There are two tables, lets call them: PARENTS and CHILDREN(with 
timestamp column)


I created functional index on parents with function, which selects max 
value of timestamp from child elements(for given parent_id).


The problem is that plpgsql function, which returns the value is 
IMMUTABLE and it works like a cache.  When I insert new record to 
children table, select over parents with function gives wrong results.


So far I figured out only one way to flush this "cache". It's by calling 
REINDEX on my index. I guess I should call it after every insert to 
children table. It's not good for me since it locks the table.


I'm thinking about partitioning the index in my app, so reindexing will 
be less painful, but perhaps there is some other easier way to solve 
tihs problem?


Thanks

--
Regards,
  Grzegorz



--
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] psql & unix env variables

[Achilleas Mantzios]

I have found useful the use of variable assignment in psql, e.g.

#!/bin/sh

# lets say you have some var with a value, or even populate some var with a 
value from 
# psql as shown below
somevar=`psql -P pager=off -q -t -c "SELECT foo from bar" | head -1 | sed -e 
's/ //g'`

# now use that variable in psql, (what you want to achieve), but in more tight 
manner
# than simple shell substitution (see -v switch and : notation)

psql -P pager=off -q -v somevar=$somevar -c "select foo2 from bar2 where 
var=:somevar"

On Τετ 29 Αυγ 2012 15:00:29 Little, Douglas wrote:
> Thanks
> 
> 
> -Original Message-
> From: Ryan Kelly [mailto:rpkell...@gmail.com] 
> Sent: Wednesday, August 29, 2012 12:41 PM
> To: Little, Douglas
> Cc: PostgreSQL General (pgsql-general@postgresql.org)
> Subject: Re: [GENERAL] psql & unix env variables
> 
> On Wed, Aug 29, 2012 at 12:35:32PM -0500, Little, Douglas wrote:
> > Is there a method for having unix env variables incorporated into a psql 
> > sql statement?
> > Ie
> > Export var='dev'
> > Psql =c 'select count(*) from $var.customer;'
> export FOO="bar"
> psql -c "select count(*) from $FOO.customer;"
> 
> Note the double quotes. That allows your shell to interpolate the string into 
> your query. Note that the interpolation is done by your shell, and not psql.
> 
> > 
> > 
> > 
> > Doug Little
> > 
> > Sr. Data Warehouse Architect | Business Intelligence Architecture | 
> > Orbitz Worldwide
> > 500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | 
> > Fax 312.894.5164 | Cell 847-997-5741 
> > douglas.lit...@orbitz.com
> >  [cid:image001.jpg@01CD85E2.C7732E50]   orbitz.com 
> > | ebookers.com | 
> > hotelclub.com | 
> > cheaptickets.com | 
> > ratestogo.com | 
> > asiahotels.com
> > 
> 
> -Ryan Kelly
> 
> 
> 
-
Achilleas Mantzios
IT DEPT


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

[GENERAL] UPDATE RULE to be invoked when UPDATE .. WHERE fails the WHERE predicate ?‏

[John Lumby]

I would like to use an UPDATE RULE to modify the action performed 
when any UPDATE is attempted on a certain table,
*including* an UPDATE which would fail because of no rows matching the WHERE.
 
Is this at all possible?    I have tried with variations of ALSO|INSTEAD etc
but the RULE is never invoked in the failing case.  And my reading of 
chapter 38.3.1. How Update Rules Work
is that the original query's quallification is always present.
 
Example :
 
create table updatable (id bigint , version int , discriminator text)
 
insert into updatable values (1 , 0 , 'root')
insert into updatable values (2 , 0 , 'leaf')
select * from updatable order by id
 id | version | discriminator 
+-+---
  1 |   0 | root
  2 |   0 | leaf
(2 rows)
 
CREATE or REPLACE FUNCTION optlock_control( OLD public.updatable , NEW 
public.updatable )
 returns bool LANGUAGE c AS '\$libdir/optlock_control.so', 'optlock_control'
 
/*  for this little test,  this function always inserts one row with a high id 
into the table update  */
 
CREATE OR REPLACE RULE update_updatable AS ON UPDATE to updatable 
    DO INSTEAD SELECT optlock_control(OLD,NEW)
 
Now I want optlock_control() invoked on every UPDATE of updatable.
 
try it with an UPDATE that would have succeeded:
update updatable set version = 1 where id = 2 and version = 0
 optlock_control 
-
 f
(1 row)
/*  verify function was invoked */
select * from updatable order by id
 id  | version | discriminator 
-+-+---
   1 |   0 | root
   2 |   0 | leaf
 999 |   0 | 9    /*  yes it was */
(3 rows)
 
 
Now try it with an UPDATE that would have failed:
 
update updatable set version = 2 where id = 1 and version = 1
 optlock_control 
-
(0 rows)
 
UPDATE 0
/*  was my function invoked ? */
select * from updatable order by id"
 id  | version | discriminator 
-+-+---
   1 |   0 | root
   2 |   0 | leaf
 999 |   0 | 9
(3 rows)
/*  no it wasnt */
  


-- 
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] Need help on autovacuum in postgres 9.1.2

[Adrian Klaver]

On 08/30/2012 03:52 AM, Khangelani Gama wrote:

Hi

I need help in turning off autovacuum in the Database that’s running
postgres 9.1.2 DB. I tried to turn it off by putting “off” in
postgresql.cont file and restarted the postmaster but when I run “*show
autovacuum;* “ query I still see autovacuum set on inside the database.

autovacuum



on



In addition to what has already been said, it is important to note that 
changing the setting to off does not entirely turn off autovacuum.


http://www.postgresql.org/docs/9.1/interactive/runtime-config-autovacuum.html

"Note that even when this parameter is disabled, the system will launch 
autovacuum processes if necessary to prevent transaction ID wraparound. 
See Section 23.1.4 for more information."




--
Adrian Klaver
adrian.kla...@gmail.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] Would my postgresql 8.4.12 profit from doubling RAM?

[Merlin Moncure]

On Thu, Aug 30, 2012 at 6:42 AM, Alexander Farber
 wrote:
> Hello,
>
> I run CentOS 6.3 server with 16 GB RAM and:
> postgresql-8.4.12-1.el6_2.x86_64
> pgbouncer-1.3.4-1.rhel6.x86_64
>
> The modified params in postgresql.conf are:
> max_connections = 100
> shared_buffers = 4096MB
>
> and the pgbouncer runs with:
> pool_mode = session
> server_reset_query = DISCARD ALL;
>
> The main app is a card game with 30-500
> simultaneous users for which I save some
> playing stats into the db +
> PHP scripts to display those stats again.
>
> I have an option to double the RAM for EUR 180,-
> but wonder if it will improve any performance and
> also what to do on the PostgreSQL side once
> I've doubled the RAM (like double shared_buffers?
> but how do I find out if it's needed, maybe they're empty?)
>
> Below is a typical top output, the pref.pl is my game daemon:
>
> top - 13:40:30 up 21 days,  5:11,  1 user,  load average: 0.61, 1.14, 1.31
> Tasks: 232 total,   1 running, 231 sleeping,   0 stopped,   0 zombie
> Cpu0  : 14.6%us,  0.3%sy,  0.0%ni, 84.4%id,  0.3%wa,  0.0%hi,  0.3%si,  0.0%st
> Cpu1  :  3.0%us,  0.0%sy,  0.0%ni, 97.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Cpu2  :  7.3%us,  0.0%sy,  0.0%ni, 92.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Cpu3  :  7.3%us,  0.0%sy,  0.0%ni, 92.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Cpu4  : 10.0%us,  0.0%sy,  0.0%ni, 90.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Cpu5  :  2.3%us,  0.0%sy,  0.0%ni, 97.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Cpu6  :  0.0%us,  0.3%sy,  0.0%ni, 99.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Cpu7  :  1.7%us,  0.0%sy,  0.0%ni, 98.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Mem:  16243640k total, 14091172k used,  2152468k free,   621072k buffers
> Swap:  2096056k total,0k used,  2096056k free,  8929900k cached
>
>   PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND
> 19992 postgres  20   0 4378m 782m 775m S 11.0  4.9   2:28.58 postmaster
> 16184 nobody20   0  116m  21m 3908 S  9.0  0.1  22:01.32 pref.pl
> 16187 postgres  20   0 4375m 502m 497m S  7.3  3.2  37:13.48 postmaster
> 20229 postgres  20   0 4377m 426m 420m S  6.3  2.7   0:07.01 postmaster
> 20201 postgres  20   0 4378m 512m 505m S  4.7  3.2   0:23.65 postmaster
> 20135 postgres  20   0 4378m 771m 764m S  2.7  4.9   2:14.57 postmaster
> 20209 postgres  20   0 4377m 571m 564m S  2.0  3.6   1:14.34 postmaster
> 20030 postgres  20   0 4376m 890m 883m S  1.7  5.6   3:39.64 postmaster
> 20171 apache20   0  370m  30m  16m S  0.7  0.2   0:01.87 httpd
> 18986 apache20   0  371m  43m  28m S  0.3  0.3   0:11.47 httpd
> 19523 apache20   0  370m  32m  18m S  0.3  0.2   0:07.18 httpd
> 19892 apache20   0  380m  37m  19m S  0.3  0.2   0:04.86 httpd
> 20129 apache20   0  376m  37m  16m S  0.3  0.2   0:02.39 httpd
> 20335 root  20   0 15148 1416  996 R  0.3  0.0   0:00.13 top

I would say no -- things are fine.  That's a top from a perfectly
healthy server.  Are you experiencing poor performance?  What problem
are you trying to solve exactly?  One thing to possibly explore if
you're seeing unpredictable query latency when lots of users are
logged on is pgbouncer transaction mode.

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] String comparision in PostgreSQL

[Scott Marlowe]

On Thu, Aug 30, 2012 at 6:09 AM, F. BROUARD / SQLpro
 wrote:
> Le 30/08/2012 12:45, Craig Ringer a écrit :
>
>
>> That's my understanding, but I don't know which other database systems
>> you're talking about because you've never specifically named any.
>>
> In his primary post he talk about SQL Server, Sybase and MySQL wich does
> good jobs with collation
>
> Almost a majority of RDBMS have collation support wich is very important for
> non english languages, that represents about 90% of the planet languages !
>
> This feature has always been a "black spot" in PG, and the most important
> topic to discourage to use it in professionnal applications.

Citations please.

PostgreSQL has excellent collation support.
http://www.postgresql.org/docs/9.1/static/collation.html

Sybase performance on upper() case insensitive searchs:
http://stackoverflow.com/questions/81268/case-insensitive-search-on-sybase

MySQL case insensitive searchs rely on indexing upper or lower
functions just like PostgreSQL and use seq scans for collation induced
case insensitive searchs:
http://use-the-index-luke.com/sql/where-clause/functions/case-insensitive-search

Further PostgreSQL has the citext type:
http://www.postgresql.org/docs/9.1/static/citext.html
Which can be handy for case insensitive searches but can ONLY do case
insensitive stuff.


-- 
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] Would my postgresql 8.4.12 profit from doubling RAM?

[Scott Marlowe]

On Thu, Aug 30, 2012 at 5:42 AM, Alexander Farber
 wrote:
> Hello,
>
> I run CentOS 6.3 server with 16 GB RAM and:
> postgresql-8.4.12-1.el6_2.x86_64
> pgbouncer-1.3.4-1.rhel6.x86_64
>
> The modified params in postgresql.conf are:
> max_connections = 100
> shared_buffers = 4096MB

That's probably plenty.  Remember Postgresql uses memory in other ways
so handing it all over to shared buffers is not a good idea anyway.
You might consider upping work_mem a bit, depending on your workload.

> Below is a typical top output, the pref.pl is my game daemon:
>
> Mem:  16243640k total, 14091172k used,  2152468k free,   621072k buffers
> Swap:  2096056k total,0k used,  2096056k free,  8929900k cached

This is the important stuff right here.  Note you've got 2G free mem,
and almost 9G of cache.  That's good.  There's no memory pressure on
your server right now.  Let's say that you average about 100 active
users, and currently work_mem is set to 1M (the default.)  If you
increase that to 16M, that'd be max 1.6G of memory, which you have
free anyway right now.  I've found that small increases of work_mem
into the 8 to 16M zone often increase performance because they allow
bigger queries to fit results into hash_* operations which are a
pretty fast way of joining larger data sets.

If pgbouncer keeps your actual connections in the range of a few dozen
at a time then you can look at going high on work_mem, but often with
workloads like the one you're talking about you won't see any increase
in performance.  Once the data set used for hash_* operations fits in
memory, more work_mem won't help.

Note that work_mem is PER op, not per connections, per user or per
database, so it can add up REAL fast, hence why it's so small to start
with (1M). Overdoing it can result in a server that falls to its knees
during heavy load.


-- 
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] Would my postgresql 8.4.12 profit from doubling RAM?

[Scott Marlowe]

On Thu, Aug 30, 2012 at 8:42 AM, Scott Marlowe  wrote:
> users, and currently work_mem is set to 1M (the default.)  If you
> increase that to 16M, that'd be max 1.6G of memory, which you have
> free anyway right now.

Self correction here.  Of course that's assuming 1 sort on average per
query.  My experience is that you generally use less than that. Except
when you don't.  :) If your typical query has 6 or 12 sorts in it,
your memory can run out fast.  But judging by your current free RAM,
I'm guessing it doesn't do that.  Only thorough testing can tell you
the sweet spot tho.


-- 
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] String comparision in PostgreSQL

[Merlin Moncure]

On Thu, Aug 30, 2012 at 9:34 AM, Scott Marlowe  wrote:
> On Thu, Aug 30, 2012 at 6:09 AM, F. BROUARD / SQLpro
>  wrote:
>> Le 30/08/2012 12:45, Craig Ringer a écrit :
>>
>>
>>> That's my understanding, but I don't know which other database systems
>>> you're talking about because you've never specifically named any.
>>>
>> In his primary post he talk about SQL Server, Sybase and MySQL wich does
>> good jobs with collation
>>
>> Almost a majority of RDBMS have collation support wich is very important for
>> non english languages, that represents about 90% of the planet languages !
>>
>> This feature has always been a "black spot" in PG, and the most important
>> topic to discourage to use it in professionnal applications.
>
> Citations please.
>
> PostgreSQL has excellent collation support.
> http://www.postgresql.org/docs/9.1/static/collation.html
>
> Sybase performance on upper() case insensitive searchs:
> http://stackoverflow.com/questions/81268/case-insensitive-search-on-sybase
>
> MySQL case insensitive searchs rely on indexing upper or lower
> functions just like PostgreSQL and use seq scans for collation induced
> case insensitive searchs:
> http://use-the-index-luke.com/sql/where-clause/functions/case-insensitive-search
>
> Further PostgreSQL has the citext type:
> http://www.postgresql.org/docs/9.1/static/citext.html
> Which can be handy for case insensitive searches but can ONLY do case
> insensitive stuff.

Yeah.  In particular, lower() approaches for case insensitive
searching have always worked and IMSNHO remain the best way.
Expression based searching and indexing is a real strong point for
postgres and is the 'go to' method for solving a broad array of
problems.  The fairest complaint you can make is that historically
you've had to ditch performance to get good collation features -- and
this is mostly solved.

I guess the biggest problem that remains is the inability to use LIKE
searches for index through utf8 ordered indexes.

merlin


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

[GENERAL] Baffling behavior regarding tables as types

[Chris Travers]

Hi all;

I figure this is a good way of opening the question of "what should the
behavior be?"  We discussed this a bit on bugs, and in the past in general.
 However, the behavior of composite types (and table types) as columns of
data is remarkably inconsistent and I think that if you work in this area
much the only thing one can conclude is that it involves discovering how
little consensus there is on how these should behave and negotiating all
sorts of conflicting assumptions that can be confusing at times.

In my view I think we'd do better to push for some consistency one way or
another.  One line might be to check all type constraints at storage time,
the way domains in complex types are currently checked.  In other words, if
the table type has a not null, check it on the column when used as a
collection.  If it has a check constraint check it.

The other option would be to make the table check all constraints
internally within the collection types, which is the way it currently works
when domains are not involved.  I am hoping perhaps we can get at least on
the same page and decide how, in our undefined future version, things will
eventually work, then possibly it will be possible to get there.  As it is
right now, the discussions suggest to me (and the database behavior even
moreso) that we are not on the same page.

So with this in mind, consider these two examples.

Example 1:  Shows that domain constraints are checked on storage.

Not null positive int:
or_examples=# create domain pos_not_null_int as int not null check (value >
0);
CREATE DOMAIN

Table containing such:
or_examples=# create table rel_examples.domaintest (id pos_not_null_int);
CREATE TABLE

constraints are enforced as expected on the simple column so we can show
there is no problem:
or_examples=# insert into rel_examples.domaintest values (-1);
ERROR:  value for domain pos_not_null_int violates check constraint
"pos_not_null_int_check"
or_examples=# insert into rel_examples.domaintest values (null);
ERROR:  domain pos_not_null_int does not allow null values

Table using our above test table as a type:
or_examples=# create table comp_domain_test ( text rel_examples.domaintest);
CREATE TABLE

Same constraints are enforced on the element of the tuple in the column:
or_examples=# insert into comp_domain_test values (row(null));
ERROR:  domain pos_not_null_int does not allow null values
or_examples=# insert into comp_domain_test values (row(-1));
ERROR:  value for domain pos_not_null_int violates check constraint
"pos_not_null_int_check"


Example 2:  Shoes that non-domain constraints are not checked on storage

Same but without domain:
or_examples=# create table rel_examples.tabletest (id int not null check
(id > 0));
CREATE TABLE

Show that the constraints are enforced in the simple table:
or_examples=# insert into rel_examples.tabletest values (null);
ERROR:  null value in column "id" violates not-null constraint
or_examples=# insert into rel_examples.tabletest values (-1);
ERROR:  new row for relation "tabletest" violates check constraint
"tabletest_id_check"

Table using other table as type:
or_examples=# create table comp_table_test (test rel_examples.tabletest);
CREATE TABLE

Constraints not enforced:
or_examples=# insert into comp_table_test values (row(null));
INSERT 0 1 ^
or_examples=# insert into comp_table_test values (row(-1));
INSERT 0 1

Are both of these correct behavior long-term?  Should they be made
consistent long-term?

Best Wishes,
Chris Travers

[GENERAL] Performance implications of adding a "disabled" column to a table

[Seref Arikan]

Greetings,
I have a large number of rows (up to 3-4 millions) that I'll either be
fetching into ram (if it is a few thousand rows), or scrolling through a
cursor.
Deletions or updates on content of these rows lead to expensive operations
in my business logic, so I simply need to mark them as disabled.
I have two options here: to exclude the disabled rows from my query results
via ..where not disabled or to not to process disabled rows in business
logic.
There will be very few disabled rows compared to total rows I'll have to
return. This table will have very few queries, so including disabled =
false or enabled = true in every query would not be an issue

I have a feeling that including this criteria in the queries may add
unnecessary processing to my queries, since the table is expected to get up
to 100 million or more rows. So should I deal with this in DB, or at the
application layer?

Kind regards
Seref

Re: [GENERAL] Performance implications of adding a "disabled" column to a table

[David Johnston]

From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Seref Arikan
Sent: Thursday, August 30, 2012 12:48 PM
To: PG-General Mailing List
Subject: [GENERAL] Performance implications of adding a "disabled" column to
a table

Greetings, 
I have a large number of rows (up to 3-4 millions) that I'll either be
fetching into ram (if it is a few thousand rows), or scrolling through a
cursor. 
Deletions or updates on content of these rows lead to expensive operations
in my business logic, so I simply need to mark them as disabled. 
I have two options here: to exclude the disabled rows from my query results
via ..where not disabled or to not to process disabled rows in business
logic. 
There will be very few disabled rows compared to total rows I'll have to
return. This table will have very few queries, so including disabled = false
or enabled = true in every query would not be an issue 

I have a feeling that including this criteria in the queries may add 
unnecessary processing to my queries, since the table is expected to get up
to 100 million or more rows. So should I deal with this in DB, or at the
application layer?

Kind regards
Seref


=

So you are willing to live with the fact that whatever, apparently
important, business logic processing occurs when a row is updated or deleted
is in-fact not important since now you are considering a class of data that
is not useful but still present and reflected elsewhere in the system.

If the proportion of rows that are disabled is small, and you don't intend
to query "where disabled = TRUE", then adding the column is sufficient.  No
index required since it will not be used.  Whether you alter queries to add
the "disabled = FALSE" or read and deal with it in the application doesn't
really matter (unless individual records are large).  Personally I would
suggest creating VIEWs that do not output "disabled" and that always use
"disabled = FALSE".  Call it something like "tablename_active" and then use
that view thus only sending enabled records to the application layer.

I would also make sure I am very confident on the implications of doing this
versus deleting the data outright (though possibly moving it to an "archive"
table)

David J.




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

[GENERAL] Baseline configurations

[Mike Orr]

Does PostgreSQL have any baseline security configuration documents?
(Aka "hardened" configuration "benchmark" checklist.) My organization
is asking for official or vendor-supported baseline configurations for
all our software. I looked through the PG manual, the security page on
the website, and in Google and found some discussions about
customizing role permissions and SSL connections, but nothing that
covered the entirety of the software like this one for MySQL:

http://benchmarks.cisecurity.org/en-us/?route=downloads.show.single.mysql.102
(Center for Internet Security). I can't link directly to the document
because it's behind a download form,  but the TOC outline covers: OS
level configuration, file system permissions, logging, general
(default test databases, accounts), database/table permissions,
configuration options, backup/recovery. Each recommendation specifies
whether it's scoreable (verifiable by an audit program), and its
tradeoffs (i.e., whether it might be too burdensome or a bad idea in
various situations).

If I can't find such a checklist for PostgreSQL I can write my own,
but it would be more authoritative if it were an official PostgreSQL
document or supported by a vendor or organization.

Thanks in advance. I've been a happy PostgreSQL user for two or three years now.

-- 
Mike Orr 


-- 
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] Performance implications of adding a "disabled" column to a table

[Seref Arikan]

Thanks David,
I've been thinking hard on this one, and I tried to keep the details of the
application requirements to a minimum in my question, to focus on the
postgres performance aspect.
Though it may be off topic, let me try to describe the reason I'm trying
not to delete rows. Originally, my idea was exactly what you've described:
to move data to an archive table, but here is the problem:

The rows contain metadata for subgraphs (partitions) of a huge graph
structure. Each partition has its local coordinate system for the nodes,
and a parallel processing framework goes through partitions looking for
patterns. When patterns are found, the matching coordinates need to be
converted to global coordinates, and partition metadata allows that.
However, partition metadata is build via calculations based on the previous
partitions' metadata. If I delete a single row, or update it, it means that
the graph structure has changed, and I have to update metadata for all
partitions that follow the deleted/updated one, which means updating lots
of rows in db.
If I leave the dead/disabled partition in place, and simply add a copy of
it as if it is a new partition, then all is fine. All I have to do is not
to use the dead/disabled partition during graph pattern search.

I am not happy about having enabled/disabled status leaking into other
layers, and a view may be a good solution; thanks for that!. I am not happy
about hiding dirt under the carpet rather than putting it in the rubbish
bin, but the performance penalty is simply too big.

Best regards
Seref


On Thu, Aug 30, 2012 at 8:09 PM, David Johnston  wrote:

> From: pgsql-general-ow...@postgresql.org
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Seref Arikan
> Sent: Thursday, August 30, 2012 12:48 PM
> To: PG-General Mailing List
> Subject: [GENERAL] Performance implications of adding a "disabled" column
> to
> a table
>
> Greetings,
> I have a large number of rows (up to 3-4 millions) that I'll either be
> fetching into ram (if it is a few thousand rows), or scrolling through a
> cursor.
> Deletions or updates on content of these rows lead to expensive operations
> in my business logic, so I simply need to mark them as disabled.
> I have two options here: to exclude the disabled rows from my query results
> via ..where not disabled or to not to process disabled rows in business
> logic.
> There will be very few disabled rows compared to total rows I'll have to
> return. This table will have very few queries, so including disabled =
> false
> or enabled = true in every query would not be an issue
>
> I have a feeling that including this criteria in the queries may add
> unnecessary processing to my queries, since the table is expected to get up
> to 100 million or more rows. So should I deal with this in DB, or at the
> application layer?
>
> Kind regards
> Seref
>
>
> 
> =
>
> So you are willing to live with the fact that whatever, apparently
> important, business logic processing occurs when a row is updated or
> deleted
> is in-fact not important since now you are considering a class of data that
> is not useful but still present and reflected elsewhere in the system.
>
> If the proportion of rows that are disabled is small, and you don't intend
> to query "where disabled = TRUE", then adding the column is sufficient.  No
> index required since it will not be used.  Whether you alter queries to add
> the "disabled = FALSE" or read and deal with it in the application doesn't
> really matter (unless individual records are large).  Personally I would
> suggest creating VIEWs that do not output "disabled" and that always use
> "disabled = FALSE".  Call it something like "tablename_active" and then use
> that view thus only sending enabled records to the application layer.
>
> I would also make sure I am very confident on the implications of doing
> this
> versus deleting the data outright (though possibly moving it to an
> "archive"
> table)
>
> David J.
>
>
>

Re: [GENERAL] Baseline configurations

[Bruce Momjian]

On Thu, Aug 30, 2012 at 12:18:11PM -0700, Mike Orr wrote:
> Does PostgreSQL have any baseline security configuration documents?
> (Aka "hardened" configuration "benchmark" checklist.) My organization
> is asking for official or vendor-supported baseline configurations for
> all our software. I looked through the PG manual, the security page on
> the website, and in Google and found some discussions about
> customizing role permissions and SSL connections, but nothing that
> covered the entirety of the software like this one for MySQL:
> 
> http://benchmarks.cisecurity.org/en-us/?route=downloads.show.single.mysql.102
> (Center for Internet Security). I can't link directly to the document
> because it's behind a download form,  but the TOC outline covers: OS
> level configuration, file system permissions, logging, general
> (default test databases, accounts), database/table permissions,
> configuration options, backup/recovery. Each recommendation specifies
> whether it's scoreable (verifiable by an audit program), and its
> tradeoffs (i.e., whether it might be too burdensome or a bad idea in
> various situations).
> 
> If I can't find such a checklist for PostgreSQL I can write my own,
> but it would be more authoritative if it were an official PostgreSQL
> document or supported by a vendor or organization.
> 
> Thanks in advance. I've been a happy PostgreSQL user for two or three years 
> now.

I have never seen such a documents.  If you want to write it, perhaps on
our wiki, we can then reference is for other users.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
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] Baseline configurations

[salah jubeh]

Hello,

I think database security is quite complex issue depends on the 
institution requirements. I have worked with elections and voting and we had an 
extreme polices for security not only for authorization, authentication, and 
password policies. We was obligated to use database auditing to record each 
change (insert, update) on the data and the delete sql command was disabled for 
all tables. Other institution has less security requirements. A baseline for 
security fluctuate too much based on needs. In general, I find the following 
document a very a good guide to give a base line for securing the data, because 
it handles the issue also from management point view

http://www.databasesecurity.com/dbsec/database-stig-v7r1.pdf


Regards



 From: Mike Orr 
To: pgsql-general@postgresql.org 
Sent: Thursday, August 30, 2012 9:18 PM
Subject: [GENERAL] Baseline configurations
 
Does PostgreSQL have any baseline security configuration documents?
(Aka "hardened" configuration "benchmark" checklist.) My organization
is asking for official or vendor-supported baseline configurations for
all our software. I looked through the PG manual, the security page on
the website, and in Google and found some discussions about
customizing role permissions and SSL connections, but nothing that
covered the entirety of the software like this one for MySQL:

http://benchmarks.cisecurity.org/en-us/?route=downloads.show.single.mysql.102
(Center for Internet Security). I can't link directly to the document
because it's behind a download form,  but the TOC outline covers: OS
level configuration, file system permissions, logging, general
(default test databases, accounts), database/table permissions,
configuration options, backup/recovery. Each recommendation specifies
whether it's scoreable (verifiable by an audit program), and its
tradeoffs (i.e., whether it might be too burdensome or a bad idea in
various situations).

If I can't find such a checklist for PostgreSQL I can write my own,
but it would be more authoritative if it were an official PostgreSQL
document or supported by a vendor or organization.

Thanks in advance. I've been a happy PostgreSQL user for two or three years now.

-- 
Mike Orr 


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

[GENERAL] options for ad-hoc web-based data queries

[Scott Ribe]

Anybody know of tools for adding ad-hoc query builder to a web app? (Backed by 
PostgreSQL 9.1.)

I'm familiar with HTSQL, and it looks good for more highly skilled & trained 
users. But I'm looking for something more graphical, you know: list of tables, 
select one, list of columns, enter conditions, click "search", see the rows, 
right click see a list of tables related by foreign key...

Being available within the web app is not a hard 100% requirement, but if we're 
talking about installing an application and JDBC driver on the user's machine, 
then I already know about lots & lots of options ;-)

-- 
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] Baseline configurations

[Mike Orr]

Yes, a general document shouldn't be applied blindly to a specific
site. It can't address the highest security or lowest security
situation, but instead aim for a general middle applicable to the
majority of situations. The local admin has to review each
recommendation and decide whether it's (A) applicable, (B) worth the
effort, (C) should be implemented differently, or (D) superceded by
somebody else's better recommendation. So we have two documents, the
external well-known baseline, and a local document listing how we
apply each recommendation or why we ignore it or what additional
requirements we have. But the baseline document is still useful as an
authoritative reference.

I'll look through your general database document and see if it has
anything relevant.

On Thu, Aug 30, 2012 at 1:33 PM, salah jubeh  wrote:
> Hello,
>
> I think database security is quite complex issue depends on the institution
> requirements. I have worked with elections and voting and we had an extreme
> polices for security not only for authorization, authentication, and
> password policies. We was obligated to use database auditing to record each
> change (insert, update) on the data and the delete sql command was disabled
> for all tables. Other institution has less security requirements. A baseline
> for security fluctuate too much based on needs. In general, I find the
> following document a very a good guide to give a base line for securing the
> data, because it handles the issue also from management point view
>
> http://www.databasesecurity.com/dbsec/database-stig-v7r1.pdf
>
>
> Regards
>
> 
> From: Mike Orr 
> To: pgsql-general@postgresql.org
> Sent: Thursday, August 30, 2012 9:18 PM
> Subject: [GENERAL] Baseline configurations
>
> Does PostgreSQL have any baseline security configuration documents?
> (Aka "hardened" configuration "benchmark" checklist.) My organization
> is asking for official or vendor-supported baseline configurations for
> all our software. I looked through the PG manual, the security page on
> the website, and in Google and found some discussions about
> customizing role permissions and SSL connections, but nothing that
> covered the entirety of the software like this one for MySQL:
>
> http://benchmarks.cisecurity.org/en-us/?route=downloads.show.single.mysql.102
> (Center for Internet Security). I can't link directly to the document
> because it's behind a download form,  but the TOC outline covers: OS
> level configuration, file system permissions, logging, general
> (default test databases, accounts), database/table permissions,
> configuration options, backup/recovery. Each recommendation specifies
> whether it's scoreable (verifiable by an audit program), and its
> tradeoffs (i.e., whether it might be too burdensome or a bad idea in
> various situations).
>
> If I can't find such a checklist for PostgreSQL I can write my own,
> but it would be more authoritative if it were an official PostgreSQL
> document or supported by a vendor or organization.
>
> Thanks in advance. I've been a happy PostgreSQL user for two or three years
> now.
>
> --
> Mike Orr 
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>



-- 
Mike Orr 


-- 
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] psql & unix env variables

[Chris Angelico]

On Thu, Aug 30, 2012 at 4:42 PM, Achilleas Mantzios
 wrote:
> I have found useful the use of variable assignment in psql, e.g.
>
> #!/bin/sh
>
> # lets say you have some var with a value, or even populate some var with a 
> value from
> # psql as shown below
> somevar=`psql -P pager=off -q -t -c "SELECT foo from bar" | head -1 | sed -e 
> 's/ //g'`
>
> # now use that variable in psql, (what you want to achieve), but in more 
> tight manner
> # than simple shell substitution (see -v switch and : notation)
>
> psql -P pager=off -q -v somevar=$somevar -c "select foo2 from bar2 where 
> var=:somevar"

At this point, I have to ask: Why not switch to a language with actual
Postgres bindings? Try Python, or Pike, or something; I'm sure it's
going to be easier than doing everything through shell scripts.

ChrisA


-- 
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] psql & unix env variables

[Adrian Klaver]

On 08/30/2012 04:19 PM, Chris Angelico wrote:

On Thu, Aug 30, 2012 at 4:42 PM, Achilleas Mantzios
 wrote:

I have found useful the use of variable assignment in psql, e.g.

#!/bin/sh

# lets say you have some var with a value, or even populate some var with a 
value from
# psql as shown below
somevar=`psql -P pager=off -q -t -c "SELECT foo from bar" | head -1 | sed -e 
's/ //g'`

# now use that variable in psql, (what you want to achieve), but in more tight 
manner
# than simple shell substitution (see -v switch and : notation)

psql -P pager=off -q -v somevar=$somevar -c "select foo2 from bar2 where 
var=:somevar"


At this point, I have to ask: Why not switch to a language with actual
Postgres bindings? Try Python, or Pike, or something; I'm sure it's
going to be easier than doing everything through shell scripts.


There is always ShellSQL. Not sure how current it is, but maybe worth a 
look:

http://shellsql.sourceforge.net/



ChrisA





--
Adrian Klaver
adrian.kla...@gmail.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] psql & unix env variables

[Craig Ringer]

On 08/30/2012 02:42 PM, Achilleas Mantzios wrote:

I have found useful the use of variable assignment in psql, e.g.


If you're going to to that, why not drive psql as a coprocess:

   http://stackoverflow.com/a/8305578/398670

or if at all possible, use a language with sane PostgreSQL bindings.

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

[GENERAL] Re: [GENERAL] UPDATE RULE to be invoked when UPDATE .. WHERE fails the WHERE predicate ?‏

[Pavan Deolasee]

On Thu, Aug 30, 2012 at 6:31 PM, John Lumby  wrote:

>
> I would like to use an UPDATE RULE to modify the action performed
> when any UPDATE is attempted on a certain table,
> *including* an UPDATE which would fail because of no rows matching the
> WHERE.
>
> Is this at all possible?I have tried with variations of ALSO|INSTEAD
> etc
> but the RULE is never invoked in the failing case.  And my reading of
> chapter 38.3.1. How Update Rules Work
> is that the original query's quallification is always present.
>
>
You did not mention why you need such a facility, but AFAICS RULEs will
only be applied on the qualifying rows. So as you rightly figured out, you
won't see them firing unless there are any qualifying rows. Is this not
something you can achieve via statement-level triggers though ?

Thanks,
Pavan