[GENERAL] Best way to "mask" password in DBLINK
I'm starting to use DBLink / DBI-Link and one of the "bad" things is that the password is out in the clear. What can I do to prevent it from being such? How do I protect it from 'innocent' users? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Windows 7 and SSPI
Hello, I'm using version 8.4 on Windows 7 RC (build 7100). When using SSPI I could never get it to work on loopback with IPv6 but it worked on IPv4 just fine. However, after I installed the latest Windows updates I cannot get SSPI to work on either loopback address. The error I get is the following: psql: FATAL: could not get token from SSPI security context DETAIL: The function requested is not supported (80090302) Is anyone else experiencing problems with SSPI on Windows 7? Cheers, Adam Ruth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help interpreting pg_stat_bgwriter output
Hello all, I wonder if I could have some help interpreting the output of pg_stat_bgwriter. Here's my output: checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc ---+-++---+--+-+--- 333 | 0 | 617784 | 0 |0 | 740 | 19163 (1 row) In case that gets garbled in email transmission, it transcribes to: checkpoints_timed = 333 checkpoints_req = 0 buffers_checkpoint = 617784 buffers_clean = 0 maxwritten_clean = 0 buffers_backend = 740 buffers_alloc = 19163 So I've had a look at the docs, but I'm still not entirely clear what all those values mean. I think what I'm concerned about is the fact that buffers_clean = 0. I've read Greg Smith's introduction to the bgwriter ( http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm), and if I understand it correctly does the fact that buffers_clean = 0 imply that the bgwriter is actually not writing *any* buffers? So my questions are: * is my interpreting of buffers_clean = 0 correct? * If so, why would the bgwriter not be writing out any buffers? My bgwriter config settings are: bgwriter_delay = 200ms bgwriter_lru_maxpages = 800 bgwriter_lru_multiplier = 8.0 Checkpoint settings are default except for: checkpoint_timeout = 30min checkpoint_segments = 10 checkpoint_completion_target = 0.9 Not sure what other config settings are pertinent here, but I can supply them if required. I am using postgresql 8.3.7-0ubuntu8.04.1, running on an OpenVZ VPS. Thanks for any pointers. Sam Mulube
Re: [GENERAL] Race hazard deleting using CTID?
> Well, the CTID of a row you can see can't be changed by another > transaction while your transaction is still live. However, if > someone else does modify/delete one of those rows concurrently, > it will fail the outer WHERE check and thus silently not be > deleted. Net effect is that you might delete fewer than 10 > rows. Not sure if you'd consider that a race hazard or not. Thanks for the explanation. My actual use-case is an UPDATE, and updating fewer rows than I wanted to is definitely not the desired behavior. It's a race hazard condition by definition if the behavior of a system is non-deterministic due to timing of another process. This is all in a pl/pgsql function. I was trying to avoid a cursor and loop solution in the hope of better performance. Sounds like I need to separate the SELECT into a top-level statement and get the list of ctids into an array variable that I then use in the DELETE (actually an UPDATE in my case, but that's beside the point). I know this question would be better on new users, but since we already have all the context established, I'd be grateful to know how to get the ctids into an array variable in pl/pgsql. -- Peter Headland Architect Actuate Corporation -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, August 11, 2009 12:01 To: Peter Headland Cc: pgsql-general@postgresql.org; Pavel Stehule Subject: Re: Race hazard deleting using CTID? "Peter Headland" writes: > My question is, does this code contain a race hazard, because the list from the SELECT might get changed by another session before the DELETE uses it? > delete from del where ctid = any(array(select ctid from del limit 10)) Well, the CTID of a row you can see can't be changed by another transaction while your transaction is still live. However, if someone else does modify/delete one of those rows concurrently, it will fail the outer WHERE check and thus silently not be deleted. Net effect is that you might delete fewer than 10 rows. Not sure if you'd consider that a race hazard or not. > If so, am I correct to think that adding FOR UPDATE to create the version below would eliminate the hazard? > delete from del where ctid = any(array(select ctid from del limit 10 for update)) If you'd bothered to try that before asking the list, you'd know the system won't take it --- FOR UPDATE is only supported at top level. You could probably do something equivalent using a plpgsql loop, or pulling the CTIDs back to the client side. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Does PERFORM hold a lock?
Peter Headland wrote: > If I do the following in a pl/pgsql function, will an update lock be > held until the end of the transaction? > > PERFORM c FROM t FOR UPDATE; Yes. (PERFORM is translated to SELECT by the plpgsql engine, so to the SQL executor this is the same as SELECT FOR UPDATE). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Race hazard deleting using CTID?
"Peter Headland" writes: > My question is, does this code contain a race hazard, because the list from > the SELECT might get changed by another session before the DELETE uses it? > delete from del where ctid = any(array(select ctid from del limit 10)) Well, the CTID of a row you can see can't be changed by another transaction while your transaction is still live. However, if someone else does modify/delete one of those rows concurrently, it will fail the outer WHERE check and thus silently not be deleted. Net effect is that you might delete fewer than 10 rows. Not sure if you'd consider that a race hazard or not. > If so, am I correct to think that adding FOR UPDATE to create the version > below would eliminate the hazard? > delete from del where ctid = any(array(select ctid from del limit 10 for > update)) If you'd bothered to try that before asking the list, you'd know the system won't take it --- FOR UPDATE is only supported at top level. You could probably do something equivalent using a plpgsql loop, or pulling the CTIDs back to the client side. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Race hazard deleting using CTID?
I believe that the following statement was originally suggested by Tom Lane; I got it from Pavel Stehule's PostgreSQL Tips page. My question is, does this code contain a race hazard, because the list from the SELECT might get changed by another session before the DELETE uses it? delete from del where ctid = any(array(select ctid from del limit 10)) If so, am I correct to think that adding FOR UPDATE to create the version below would eliminate the hazard? delete from del where ctid = any(array(select ctid from del limit 10 for update)) -- Peter Headland Architect Actuate Corporation -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Does PERFORM hold a lock?
If I do the following in a pl/pgsql function, will an update lock be held until the end of the transaction? PERFORM c FROM t FOR UPDATE; -- Peter Headland Architect Actuate Corporation
Re: [GENERAL] mail alert
On Aug 11, 2009, at 12:19 AM, Jan Verheyden wrote: I was looking in what way it’s possible to alert via mail when some conditions are true in a database. An external script that connects to the database, checks for the condition, and sends the email if it exists is probably your best solution. You might want to take a look at check_postgres: http://bucardo.org/check_postgres/ -- -- Christophe Pettus x...@thebuild.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] mail alert
Jan Verheyden wrote: Hi All, I was looking in what way it’s possible to alert via mail when some conditions are true in a database. a probe script that runs periodically (from crontab?) and checks said conditions, would probably be the best way. this could be tied into a Nagios style monitoring system if you have a requirement for multiple different sorts of monitors like that. Nagios would handle little things like not flooding you with repeated emails if that condition remained 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] Need help with libpq and PQexec
On Tue, Aug 11, 2009 at 3:35 AM, Juan Backson wrote: > PGRES_COMMAND_OK You want PGRES_TUPLES_OK for a select statement. You're not getting an actual failure- you're checking for the wrong status. -- - David T. Wilson david.t.wil...@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
[GENERAL] mail alert
Hi All, I was looking in what way it's possible to alert via mail when some conditions are true in a database. Thanks in advance! Jan
Re: [GENERAL] keepalive problem with libpg connection
On Tue, Aug 11, 2009 at 3:56 AM, Juan Backson wrote: > Hi, > > I am writing my own connection pool for libpq's connection. > > My goal is to try to reduce the amount of time spent in opening and closing > socket. However, I see that Postgres would close my connection after every > so often. it's not postgres, most likely someone in the middle is closing inactive connections, typically after 3-5 minutes. check out the keepalive settings in postgresql.conf to handle this on the tcp level (better than throwing a select 1). 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] keepalive problem with libpg connection
Hi, I am writing my own connection pool for libpq's connection. My goal is to try to reduce the amount of time spent in opening and closing socket. However, I see that Postgres would close my connection after every so often. What is the best solution for that? Should I regularly send some "Select 1" to keep the connection alive or is there a better way to write a connection pool for libpq? Any suggestion will be greatly appreciated. JB
Re: [GENERAL] Need help with libpq and PQexec[RESOLVEd]
I used if (PQresultStatus(res) != PGRES_TUPLES_OK) and it is fine now.
Thanks,
JB
On Tue, Aug 11, 2009 at 3:35 PM, Juan Backson wrote:
> Hi,
> In my source code, I have:
>
> res = PQexec(conn,"select * from resource");
> if(PQresultStatus(res) != PGRES_COMMAND_OK){
> switch_log_printf(SWITCH_CHANNEL_LOG,SWITCH_LOG_ERROR,"PQexec
> failed:%s\n",PQerrorMessage(conn));
> PQclear(res);
> return NULL;
> }
> PQclear(res);
>
> When it is executed, I am getting PGexec failed error , but the return
> value of PQerrorMessage(conn) is empty.
>
> Does anyone know why?
>
> Thanks,
> JB
>
[GENERAL] Need help with libpq and PQexec
Hi,
In my source code, I have:
res = PQexec(conn,"select * from resource");
if(PQresultStatus(res) != PGRES_COMMAND_OK){
switch_log_printf(SWITCH_CHANNEL_LOG,SWITCH_LOG_ERROR,"PQexec
failed:%s\n",PQerrorMessage(conn));
PQclear(res);
return NULL;
}
PQclear(res);
When it is executed, I am getting PGexec failed error , but the return value
of PQerrorMessage(conn) is empty.
Does anyone know why?
Thanks,
JB
