[GENERAL] Correct update statement

[Khangelani Gama]

Hi



Please help, we are using postgreSQL 9.2.4. I need to  update over 9000
rows. See the query below: A table called contact has got *addr_id *field
as null which is incorrect. So now I need to update contact table for each
account (cus_acno is in cus table) where contact_addr_id is null. For
example using the first of the results below: I need take set addr_id (in
contact table)  to 187479 where cus_acno = 243492 and con_id = 119360





Example:



select distinct(cus_acno), contact.con_id, address.addr_id from address
join person using (addr_id) join  cus using (per_id) join link_contact
using (cus_acno) join contact using (con_id) where contact.addr_id is null;
 cus_acno | con_id | addr_id
--++-
   243492 | 119360 |  187479
   393701 | 119824 |  458532
   388538 | 118413 |  453178





Thanks



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.

[GENERAL] are analyze statistics synced with replication?

[Kevin Goess]

We have a master/slave setup with replication.  Today we failed over to the
slave and saw disk I/O go through the roof.

Are the pg_statistic statistics synced along with streaming replication?
Are you expected to have to do a vacuum analyze after failing over?  That's
what we're trying now to see if it makes a difference.  Our next step will
be to fall back to the first host and see where this one went wrong
(society?  lax discipline at home? the wrong sort of friends?)

Re: [GENERAL] what should be the best autovacuum configuration for daily partition table

[Keith]

On Wed, May 14, 2014 at 3:45 PM, Jeff Janes  wrote:

> On Wed, May 14, 2014 at 12:06 AM, AI Rumman  wrote:
>
>> Hi,
>>
>> I have a table with daily partition setup where old partitions are static
>> tables that is after each day we don't get any new data in old partitions.
>> The database size is 2 TB and I am running with autovacuum on for
>> Postgresql 8.4.
>> Now, I am facing a problem where old tables are not being vacuumed by
>> autovacuum deamon and every now and then we are seeing autovacuum to
>> prevent wrap around in the database and age(datfrozenzid) gets high for the
>> database.
>> Any idea what should be best configuration for this type of database
>> environment.
>>
>
> How high is age(datfrozenxid) getting?  What is the problem you are
> experiencing?
>
> Cheers,
>
> Jeff
>

It'd be good to know what you have autovacuum_freeze_max_age set to. You
may have it set a bit too low and causing that automatic vacuuming to kick
in too soon.

Even with autovacuum_freeze_max_age set to a reasonable value, we still see
this issue often with data warehousing systems with a lot of static data.
As you are seeing, autovacuum will never kick in for these tables until you
hit autovacuum_freeze_max_age. The best solution we've found for this is to
run a cronjob to routinely vacuum a controlled batch of the tables with the
oldest vacuum freeze age. This controls how many tables are being vacuumed
instead of running into the situation where many of them all hit
autovacuum_freeze_max_age at the same time and cause extensively long
vacuuming sessions.

Below is a script we run for one of our clients twice a day. You can adjust
the limit on the first query to set how many you want to run per batch.
This has to be high enough (or run the script often enough) to keep the
count of old tables below hitting autovacuum_freeze_max_age and having
autovacuum kick in on them. Just pass the name of the database as a
parameter to the script.

manual_vacuum.sh:

# Manually vacuum tables with the oldest xid (25)

psql -d $1 -t -o /tmp/manual_vacuum_$1.sql -c "select 'vacuum analyze
verbose ' || oid::regclass || ';' from pg_class where relkind in ('r', 't')
and age(relfrozenxid) > 1 order by age(relfrozenxid) desc limit 25"

psql -d $1 -t -a -f /tmp/manual_vacuum_$1.sql > $HOME/manual_vacuum_$1.log
2>&1


Keith
http://www.keithf4.com

Re: [GENERAL] what should be the best autovacuum configuration for daily partition table

[Jeff Janes]

On Wed, May 14, 2014 at 12:06 AM, AI Rumman  wrote:

> Hi,
>
> I have a table with daily partition setup where old partitions are static
> tables that is after each day we don't get any new data in old partitions.
> The database size is 2 TB and I am running with autovacuum on for
> Postgresql 8.4.
> Now, I am facing a problem where old tables are not being vacuumed by
> autovacuum deamon and every now and then we are seeing autovacuum to
> prevent wrap around in the database and age(datfrozenzid) gets high for the
> database.
> Any idea what should be best configuration for this type of database
> environment.
>

How high is age(datfrozenxid) getting?  What is the problem you are
experiencing?

Cheers,

Jeff

Re: [GENERAL] what should be the best autovacuum configuration for daily partition table

[chiru r]

Hi Rumman,

Please publish the below information.

1. vacuum and auto-vacuum parametters current settings on cluster.
 select name,setting from pg_settings where name ilike '%vacuum%';

2.  show maintenance_work_mem ;
 show autovacuum_max_workers ;

3. Physical Ram size on server.

--Chiru


On Wed, May 14, 2014 at 12:36 PM, AI Rumman  wrote:

> Hi,
>
> I have a table with daily partition setup where old partitions are static
> tables that is after each day we don't get any new data in old partitions.
> The database size is 2 TB and I am running with autovacuum on for
> Postgresql 8.4.
> Now, I am facing a problem where old tables are not being vacuumed by
> autovacuum deamon and every now and then we are seeing autovacuum to
> prevent wrap around in the database and age(datfrozenzid) gets high for the
> database.
> Any idea what should be best configuration for this type of database
> environment.
>
> Thanks.
>
>

Re: [GENERAL] LDAP authentication not working

[Stephan Fabel]

On May 14, 2014 12:56 AM, "Magnus Hagander"  wrote:
>
> On Wed, May 14, 2014 at 11:48 AM, Jürgen Fuchsberger <
juergen.fuchsber...@uni-graz.at> wrote:
>>
>>
>>
>> On 05/14/2014 09:10 AM, Magnus Hagander wrote:
>> > On Wed, May 14, 2014 at 8:35 AM, Stephan Fabel > > > wrote:
>> >
>> > I don't think SSL support for LDAP is supported. Have you tried TLS
>> > on port 389?
>> >
>> Thanks for the hint, no wonder it does not work. Unfortunately this info
>> is not in the postgres documentation.
>
>
> It is - indirectly, in the ldapurl documentation. "To use encrypted LDAP
connections, the ldaptls option has to be used in addition to ldapurl. The
ldaps URL scheme (direct SSL connection) is not supported."

In the documentation for 9.1 ldapurl is not mentioned. That's what the OP
is using.

>> This does not work with our LDAP server (seems it is not configured to
>> support TLS)
>
> That's strangely configured. The LDAP TLS support (in the protocol) is
the standardized one, and the "SSL wrapper"  mode is not in the standard.

Enabling TLS on OpenLDAP is trivial, especially if you have SSL enabled
already. Ask your SysAdmin.

> I *think* the "SSL wrapper" really is just that - wrap it in a standard
SSL connection. In which case it might work if you set up stunnel or
something like that to proxy the connection for you.

That would work, but it shouldn't be necessary. Just enable TLS in
OpenLDAP.

>> Any idea whether LDAP over SSL will be supported in future postgres
>> releases?
>
> I am not aware of any such plans, but if you (or somebody else) is
willing to write a patch, I don't see a reason it would be rejected. Even
though it's non-standard, it's fairly widespread. I recall there being a
reason it wasn't added in the first place, but I don't recall what it was.
>

I agree that it would be nice to support the LDAPS scheme in PostgreSQL as
well.

-Stephan

Re: [GENERAL] Receiving many more rows than expected

[Vincent de Phily]

On Friday 09 May 2014 08:36:04 David G Johnston wrote:
> This seems to likely be the same, still open, bug reported previously:
> 
> No Number Assigned:
> http://www.postgresql.org/message-id/CANCipfpfzoYnOz5jj=UZ70_R=CwDHv36dqWSpw
> si27vpm1z...@mail.gmail.com
> 
> #8464
> http://www.postgresql.org/message-id/E1VN53g-0002Iy-Il@wrigleys.postgresql.o
> rg
> 
> #8470 is referenced in the first thread as well...though that is
> specifically a performance issue and not a query bug.
> 
> The recommended work-around is to move the sub-query using the "FOR UPDATE"
> into a CTE.

Thanks for those pointers, it certainly looks like the same issue (the only 
difference being the size of the limit) and there has been a good amount of 
discussion there.

I'll try the CTE workaround, although that leaves a bad taste in my mouth. 
>From the discussions in the 1st thread, I wonder wether raising the isolation 
level to repeatable read would also fix the issue ?

In any case, testing will take time because the bug triggers less than once a 
day and I haven't yet managed to reproduce it locally.




ps: sorry I'm only seeing your answer now, it helps if you cc me when 
answering the list.
-- 
Vincent de Phily


-- 
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] LDAP authentication not working

[Magnus Hagander]

On Wed, May 14, 2014 at 11:48 AM, Jürgen Fuchsberger <
juergen.fuchsber...@uni-graz.at> wrote:

>
>
> On 05/14/2014 09:10 AM, Magnus Hagander wrote:
> > On Wed, May 14, 2014 at 8:35 AM, Stephan Fabel  > > wrote:
> >
> > I don't think SSL support for LDAP is supported. Have you tried TLS
> > on port 389?
> >
> Thanks for the hint, no wonder it does not work. Unfortunately this info
> is not in the postgres documentation.
>

It is - indirectly, in the ldapurl documentation. "To use encrypted LDAP
connections, the ldaptls option has to be used in addition to ldapurl. The
ldaps URL scheme (direct SSL connection) is not supported."

But maybe it could be made more clear...

>
> > Correct, and you need to set ldaptls=1 to use that as well.
>
> This does not work with our LDAP server (seems it is not configured to
> support TLS)
>

That's strangely configured. The LDAP TLS support (in the protocol) is the
standardized one, and the "SSL wrapper"  mode is not in the standard.

I *think* the "SSL wrapper" really is just that - wrap it in a standard SSL
connection. In which case it might work if you set up stunnel or something
like that to proxy the connection for you.



> Any idea whether LDAP over SSL will be supported in future postgres
> releases?
>

I am not aware of any such plans, but if you (or somebody else) is willing
to write a patch, I don't see a reason it would be rejected. Even though
it's non-standard, it's fairly widespread. I recall there being a reason it
wasn't added in the first place, but I don't recall what it was.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Log Data Analytics : Confused about the choice of Database

[Dorian Hoxha]

On Wed, May 14, 2014 at 6:48 AM, Peeyush Agarwal <
peeyushagarwal1...@gmail.com> wrote:

> Hi,
>
> Thanks for the reply :)
>
> Yes, Storing timestamp as timestamp [ (p) ] would be better. I simplified
> the session in question. It may contain alphabets as well. So, I will
> probably need to store it as a string only.
>
> The problem with types of events is that it is not fixed and will keep
> increasing over time (as more event types are added depending on the need).
> Would you still recommend saving a map in another table? Will it have a
> significant storage benefit and are there any other benefits? Storing it in
> the app will require some addition in code each time a new event type is
> added which is not difficult but time consuming and tedious.
>
Depending on how many types of events (store it as an smallint) . Integer
indexes can be searched faster + require less size than text ones.
If some parameters inside the json field show up all the time maybe put
them on their own fields, so you don't store the key each time + indexes
build faster.

>
> I am not very familiar with json data type. Can I query on it as
> effectively as hstore? Also, is it possible to index it partially so as to
> increase query speeds for certain types of queries? For eg. I would require
> parameter username in a lot of queries so I would like to partially index
> over it.
>
http://www.postgresql.org/docs/9.3/static/functions-json.html for
expression indexes (also make partial indexes, when fields don't exist).
(more functions will come with jsonb in 9.4).

>
> Peeyush Agarwal
>
>
> On Tue, May 13, 2014 at 3:13 PM, Dorian Hoxha wrote:
>
>> Why not store session as integer?
>>
>> And timestamp as timesamp(z?) ?
>>
>> If you know the types of events, also store them as integer , and save a
>> map of them in the app or on another table ?
>>
>> And save the parameters as a json column, so you have more data-types?
>> Hstore only has strings.
>>
>> Be carefull with the mongodb hipster on the stackoverflow post.
>> Elasticsearch is often used for log collection.
>>
>> So, what really is the problem ?
>>
>>
>>
>> On Tue, May 13, 2014 at 4:11 AM, Peeyush Agarwal <
>> peeyushagarwal1...@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> I have log data of the following format:
>>>
>>> SessionTimestampEventParameters1  1 
>>>Started Session  1  2Logged In
>>> Username:"user1"2  3Started Session1  3 
>>>Started Challengetitle:"Challenge 1", level:"2"2  4  
>>>   Logged InUsername:"user2"
>>>
>>> Now, a person wants to carry out analytics on this log data (And would
>>> like to receive it as a JSON blob after appropriate transformations). For
>>> example, he may want to receive a JSON blob where the Log Data is grouped
>>> by Session and TimeFromSessionStart and CountOfEvents are added before
>>> the data is sent so that he can carry out meaningful analysis. Here I
>>> should return:
>>>
>>> [
>>>   {
>>> 
>>> "session":1,"CountOfEvents":3,"Actions":[{"TimeFromSessionStart":0,"Event":"Session
>>>  Started"}, {"TimeFromSessionStart":1, "Event":"Logged In", 
>>> "Username":"user1"}, {"TimeFromSessionStart":2, "Event":"Startd Challenge", 
>>> "title":"Challenge 1", "level":"2" }]
>>>   },
>>>   {
>>> "session":2, 
>>> "CountOfEvents":2,"Actions":[{"TimeFromSessionStart":0,"Event":"Session 
>>> Started"}, {"TimeFromSessionStart":2, "Event":"Logged In", 
>>> "Username":"user2"}]
>>>   }]
>>>
>>>
>>> Here, TimeFromSessionStart, CountOfEvents etc. [Let's call it synthetic
>>> additional data] will not be hard coded and I will make a web interface to
>>> allow the person to decide what kind of synthetic data he requires in the
>>> JSON blob. I would like to provide a good amount of flexibility to the
>>> person to decide what kind of synthetic data he wants in the JSON blob.
>>>
>>> If I use PostgreSQL, I can store the data in the following manner:
>>> Session and Event can be string, Timestamp can be date and Parameters can
>>> be hstore(key value pairs available in PostgreSQL). After that, I can
>>> use SQL queries to compute the synthetic (or additional) data, store it
>>> temporarily in variables in a Rails Application (which will interact with
>>> PostgreSQL database and act as interface for the person who wants the JSON
>>> blob) and create JSON blob from it.
>>>
>>> However I am not sure if PostgreSQL is the best choice for this use
>>> case. I have put the detailed question on SO at
>>> http://stackoverflow.com/questions/23544604/log-data-analytics
>>>
>>>  Looking for some help from the community.
>>>
>>> Peeyush Agarwal
>>>
>>
>>
>
>
> --
> Peeyush Agarwal
> IIT Kanpur
> +91 8953453689
>

Re: [GENERAL] Full-Text Search question

[Dorian Hoxha]

Search for fulltext tutorial  + json functions
http://www.postgresql.org/docs/9.3/static/functions-json.html


On Wed, May 14, 2014 at 1:00 AM, Jesus Rafael Sanchez Medrano <
jesusraf...@gmail.com> wrote:

> thanks... could you please be so kind to post some snippet/code for this?
>
> Att.
> ==
> Jesus Rafael Sanchez Medrano
> "Life is a dream, of which all must wake up"
>
>
> On Tue, May 13, 2014 at 5:33 PM, Oleg Bartunov wrote:
>
>> Easy, you need to extract text fields from json and construct tsvector
>> from them (use concatenation, for example).
>>
>> On Tue, May 13, 2014 at 7:38 PM, Jesus Rafael Sanchez Medrano
>>  wrote:
>> > can postgres do FTS (full text search) on a json column? if possible,
>> please
>> > be so kindd to give some snippet/example.
>> >
>> >
>> > Att.
>> > ==
>> > Jesus Rafael Sanchez Medrano
>> > "Life is a dream, of which all must wake up"
>>
>
>

Re: [GENERAL] better performance on poorer machine?

[Vegard Bønes]

Hi, all.

I found the problem - it was merely a poorly written query, which for some 
reason was less terrible on my laptop. Looking at http://explain.depesz.com/ 
helped me solve the issue. Performance is now back to normal.

It seems this was a classic case of pebcak. Thank you for your help and time!


VG


- Original Message -
Fra: "Alban Hertroys" 
Til: "Vegard Bønes" 
Kopi: pgsql-general@postgresql.org
Sendt: 13. mai 2014 17:49:28
Emne: Re: [GENERAL] better performance on poorer machine?

On 13 May 2014 16:48, Vegard Bønes  wrote:
> I have a database on a test server with queries that perform terribly. Trying 
> to fix this problem, I copied the database (using pg_dump) to my laptop, and 
> reran tests there. The same queries perform perfectly on my laptop.

We can but guess, but... Quite possibly your laptop is not actually a
poorer machine for single uncomplicated queries. If it's cores are
faster than the test servers, than your laptop would out-perform the
server for such queries.

Once you get other users starting to run queries as well, turning the
load into a parallel load, the server will probably turn out faster
again.

-- 
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] LDAP authentication not working

[Magnus Hagander]

On Wed, May 14, 2014 at 8:35 AM, Stephan Fabel  wrote:

> I don't think SSL support for LDAP is supported. Have you tried TLS on
> port 389?
>

Correct, and you need to set ldaptls=1 to use that as well.

(And yes, unfortunately the LDAP error messages from openldap are
notoriously bad)

//Magnus



> On May 13, 2014 8:20 PM, "Jürgen Fuchsberger" <
> juergen.fuchsber...@uni-graz.at> wrote:
>
>> Hi,
>>
>> I'm running postgresql 9.1 on Debian and am trying to set up LDAP
>> authentication using the following configuration in pg_hba.conf:
>>
>> hostssl testdb  all 143.50.203.0/24 ldap ldapserver="
>> wegc24.uni-graz.at"
>> ldapport=636 ldapbinddn="cn=nss,dc=uni-graz,dc=at"
>> ldapbindpasswd="" ldapbasedn="dc=uni-graz,dc=at"
>>
>>
>> Trying to access testdb via psql fails with the following error in the
>> log:
>> '''could not perform initial LDAP bind for ldapbinddn
>> "cn=nss,dc=uni-graz,dc=at" on server "wegc24.uni-graz.at": error code
>> -1'''
>>
>> Unfortunately I did not find what error code -1 means.
>>
>> Ldapsearch works fine:
>> > ldapsearch -W -H ldaps://wegc24.uni-graz.at:636/ -D
>> "CN=nss,DC=uni-graz,DC=at"
>>
>> Interesting is also, that postgres seems to not even reach the ldap
>> server: If I change parameter ldapserver to a non-existing url it gives
>> the same error code -1.
>>
>> Any help much appreciated!
>>
>> Best,
>> Juergen
>>
>>


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

[GENERAL] what should be the best autovacuum configuration for daily partition table

[AI Rumman]

Hi,

I have a table with daily partition setup where old partitions are static
tables that is after each day we don't get any new data in old partitions.
The database size is 2 TB and I am running with autovacuum on for
Postgresql 8.4.
Now, I am facing a problem where old tables are not being vacuumed by
autovacuum deamon and every now and then we are seeing autovacuum to
prevent wrap around in the database and age(datfrozenzid) gets high for the
database.
Any idea what should be best configuration for this type of database
environment.

Thanks.