Re: [GENERAL] plpgsql function confusing behaviour

Merlin Moncure-2 wrote: > > One proposed solution is to cache plpgsql plans around the search path. > I like the proposed solution, since search_path plays a part when generating plpgsql plan, it make sense to be part of the cache. Merlin Moncure-2 wrote: > > *) use sql functions for porti

[GENERAL] About permissions on large objects

Hi all, I moved a few clusters from 8.4 to 9.0 since I required the new way of authenticating against LDAP (or, in my case, AD). Now, I found the new database version introduced permissions on large object, so my application, in order to share large object across a group, require a bit of change.

Re: [GENERAL] ts_rank seems very slow (140 ranked documents / second on my machine)

Hi Oleg and all, On Wed, Jul 13, 2011 at 08:16, Oleg Bartunov wrote: > there is problem with estimating of cost scanning gin index in < 9.1 > versions, > so you can set enable_seqscan=off; > or try 9.1 which beta3 now. I re-ran my queries using enable seqscan=off. Now the first query, without t

[GENERAL] Server stops responding randomly for 5 minutes

Sometimes per week server stops randomly responding for approx 5 minutes. User should wait for 5 minutes before server responds. Other users can work normally at same time. Monday this happens at 12:16 I havent noticed anythis special in PostgreSql and windows logs at this time. How to fix o

Re: [GENERAL] Server stops responding randomly for 5 minutes

I think logs may help. Have you checked that? 2011/7/13 Andrus > Sometimes per week server stops randomly responding for approx 5 minutes. > User should wait for 5 minutes before server responds. > Other users can work normally at same time. > > Monday this happens at 12:16 > I havent noticed

Re: [GENERAL] Server stops responding randomly for 5 minutes

> Sometimes per week server stops randomly responding for approx 5 minutes. > User should wait for 5 minutes before server responds. > Other users can work normally at same time. So does the whole machine just stop responding, or just the postgresql? Are those other users using postgresql or some

Re: [GENERAL] Server stops responding randomly for 5 minutes

2011/7/13 Andrus : > Sometimes per week server stops randomly responding for approx 5 minutes. > User  should wait for 5 minutes before server responds. > Other users can work normally at same time. Sounds very much like a locking issue. Are you doing something like storing a frequently updated sy

Re: [GENERAL] Server stops responding randomly for 5 minutes

Tomáš and Tony, thank you. Have you done some basic monitoring? This typically happens when the machine does a lot of I/O (swapping, checkpoints, ...) - not sure how this is logged. This is dedicated server, used only for PostgreSql. I filtered windows event logs near this time (12:16) . The

Re: [GENERAL] Server stops responding randomly for 5 minutes

On 13 Červenec 2011, 13:34, Andrus wrote: > 2011-07-11 12:18:35 EEST LOG: unexpected EOF on client connection > 2011-07-11 12:18:46 EEST LOG: could not receive data from client: No > connection could be made because the target machine actively refused it. > > > 2011-07-11 12:18:46 EEST LOG: unex

Re: [GENERAL] Server stops responding randomly for 5 minutes

Peter, Sometimes per week server stops randomly responding for approx 5 minutes. User should wait for 5 minutes before server responds. Other users can work normally at same time. Sounds very much like a locking issue. Thank you. This may be the issue. Are you doing something like storing a

Re: [GENERAL] About permissions on large objects

On 13/07/2011 8:49 AM, Giuseppe Sacco wrote: Hi all, I moved a few clusters from 8.4 to 9.0 since I required the new way of authenticating against LDAP (or, in my case, AD). Now, I found the new database version introduced permissions on large object, so my application, in order to share large ob

[GENERAL] Web-based Graphical Query Building Tool for PostgreSQL

Hi all, My company sells software that uses PostgreSQL and the need has come up to provide a tool that gives our clients access to query the database but we don't want to expect these users to be able to use SQL. Is there a product out there that provides a graphical query builder? Preferably we

Re: [GENERAL] dirty read from plpgsql

erm, you're right (re-tested that today) I don't know what happened the other day. The query updating the flag would not return until the test function was done. I must have made the test duration too short, so that it was only appearances. whatever, it works. thanks. WBL On Wed, Jul 6, 2011 at 2

Re: [GENERAL] plpgsql function confusing behaviour

On Tue, Jul 12, 2011 at 12:10 PM, Shianmiin wrote: > > Merlin Moncure-2 wrote: >> >> One proposed solution is to cache plpgsql plans around the search path. >> > > I like the proposed solution, since search_path plays a part when generating > plpgsql plan, it make sense to be part of the cache. >

[GENERAL] Using LDAP roles in PostgreSQL

Hi, LDAP is often used to do a centralized user and role management in an enterprise environment. PostgreSQL offers different authentication methods, like LDAP, SSPI, GSSAPI or SSL. However, for any of these methods the user must already exist in the database, before the authentication can be

Re: [GENERAL] ts_rank seems very slow (140 ranked documents / second on my machine)

I didn't notice, reading 40K tuples in random order takes a long time and this is a problem of any database. Can you measure time to read all documents found ? :( The only solution I see is to store enough information for ranking in index. Oleg On Wed, 13 Jul 2011, Nicolas Grilly wrote: Hi Ole

Re: [GENERAL] ts_rank seems very slow (140 ranked documents / second on my machine)

On Wed, Jul 13, 2011 at 17:36, Oleg Bartunov wrote: > I didn't notice, reading 40K tuples in random order takes a long time and > this > is a problem of any database. Can you measure time to read all documents > found ? As you asked, I measured the time required to read all documents. For refere

Re: [GENERAL] Server stops responding randomly for 5 minutes

I think your log files, and the description of your issue provide at least a decent idea of what is probably happening. Unfortunately without being able to observe the server when it happens, I think you are going to have limited success tracking this down. Proximal causes could be network errors

[GENERAL] SerializableSnapshot removed from postgresql 8.4

Hi list, I'm currently upgrading from Postgresql 8.1 to 8.4 one of the steps of the process for me involves compiling the replication toolkit we use against 8.4. I've just run into a problem since this replication code references SerializableSnapshot which as been removed in 8.4, i was wonderin

Re: [GENERAL] Using LDAP roles in PostgreSQL

On Wed, Jul 13, 2011 at 6:59 AM, Lars Kanis wrote: > Homepage: https://github.com/larskanis/pg-ldap-sync > > Is it something useful for someone apart of mine? Hi Lars; While I don't have an immediate use for it, it is very nice to know such a tool exists, and I think it is likely that at some u

Re: [GENERAL] SerializableSnapshot removed from postgresql 8.4

On Wed, 2011-07-13 at 18:10 +0100, Duarte Fonseca wrote: > Hi list, > > I'm currently upgrading from Postgresql 8.1 to 8.4 one of the steps of the > process for me involves compiling the replication toolkit we use against 8.4. > > I've just run into a problem since this replication code referenc

Re: [GENERAL] About permissions on large objects

Hi Howard, Il giorno mer, 13/07/2011 alle 13.18 +0100, Howard Cole ha scritto: [...] > As an interim solution, you could set the large object compatibility: > > www.postgresql.org/docs/9.0/interactive/runtime-config-compatible.html#GUC-LO-COMPAT-PRIVILEGES thanks for pointing to this option. I a

Re: [GENERAL] About permissions on large objects

On 13/07/2011 8:15 PM, Giuseppe Sacco wrote: Is there any other possibility? Hi Guiseppe, Perhaps you can create a trigger that monitors for the insertion of an oid and then grant permissions. No idea if this can be done, but if it can it will save you lots of repeated grants. An easier o

Re: [GENERAL] Using LDAP roles in PostgreSQL

Hi Chris, > I do have a question though. Does your application allow for creating > only users and groups in part of the LDAP tree? Or does it have that > possibility yet? Also can it be configured to ignore grants of > specific Pg roles to users? Yes, filters on both sides can be set and they

Re: [GENERAL] Web-based Graphical Query Building Tool for PostgreSQL

phpPgAdmin works great for me! You can do most simple queries without any SQL, including add, select, update, sort (order by), alter, create, drop, etc etc. All that can be done just by clicking buttons and labels in the browser. There is the ability to execute actual SQL queries if your users k

Re: [GENERAL] About permissions on large objects

On Wed, 2011-07-13 at 23:30 +0100, Howard Cole wrote: > On 13/07/2011 8:15 PM, Giuseppe Sacco wrote: > > > > Is there any other possibility? > > Hi Guiseppe, > > Perhaps you can create a trigger that monitors for the insertion of an > oid and then grant permissions. No idea if this can be done,

Re: [GENERAL] Weird problem that enormous locks

Could I consider it a hardware problem, or postgresql running too long which causes problems? (It ran about half a month, however, it ran much longer than that without problems) On Wed, Jul 13, 2011 at 00:52, Tony Wang wrote: > Hi, > > The configuration information is listed at the end. > I met

Re: [GENERAL] Weird problem that enormous locks

On 07/13/11 6:55 PM, Tony Wang wrote: Could I consider it a hardware problem, or postgresql running too long which causes problems? (It ran about half a month, however, it ran much longer than that without problems) i have postgres servers that run for months and even years without problems.

Re: [GENERAL] Web-based Graphical Query Building Tool for PostgreSQL

On 13/07/2011 9:02 PM, Caleb Palmer wrote: Hi all, My company sells software that uses PostgreSQL and the need has come up to provide a tool that gives our clients access to query the database but we don't want to expect these users to be able to use SQL. Is there a product out there that provi

Re: [GENERAL] Weird problem that enormous locks

On Thu, Jul 14, 2011 at 10:01, John R Pierce wrote: > On 07/13/11 6:55 PM, Tony Wang wrote: > >> Could I consider it a hardware problem, or postgresql running too long >> which causes problems? (It ran about half a month, however, it ran much >> longer than that without problems) >> > > i have po

Re: [GENERAL] Weird problem that enormous locks

On 07/13/11 7:16 PM, Tony Wang wrote: On Thu, Jul 14, 2011 at 10:01, John R Pierce > wrote: On 07/13/11 6:55 PM, Tony Wang wrote: Could I consider it a hardware problem, or postgresql running too long which causes problems? (It ran about half a mo

Re: [GENERAL] Weird problem that enormous locks

On Thu, Jul 14, 2011 at 10:35, John R Pierce wrote: > On 07/13/11 7:16 PM, Tony Wang wrote: > > On Thu, Jul 14, 2011 at 10:01, John R Pierce > pie...@hogranch.com>> wrote: >> >>On 07/13/11 6:55 PM, Tony Wang wrote: >> >>Could I consider it a hardware problem, or postgresql running >>

Re: [GENERAL] Weird problem that enormous locks

On 07/13/11 8:47 PM, Tony Wang wrote: It's a game server, and the queries are updating users' money, as normal. The sql is like "UPDATE player SET money = money + 100 where id = 12345". The locks were RowExclusiveLock for the table "player" and the indexes. The weird thing is there was another

Re: [GENERAL] Weird problem that enormous locks

On Thu, Jul 14, 2011 at 12:35, John R Pierce wrote: > On 07/13/11 8:47 PM, Tony Wang wrote: > >> It's a game server, and the queries are updating users' money, as normal. >> The sql is like "UPDATE player SET money = money + 100 where id = 12345". >> The locks were RowExclusiveLock for the table