Re: [GENERAL] Clients disconnect but query still runs
On Wed, 2009-07-29 at 14:56 +0100, Greg Stark wrote:
> SIGURG might be useful but it would be more complex to use and less
> widely useful since it would only work if the client disconnects
> gracefully (though it might be worth checking into as an alternative
> to our existing query cancel method).
Might it not also fire if the client disconnects without notice, but tcp
keepalives are enabled?
I might have to write a little test program and see.
[much later] My test program did not appear to receive SIGURB, even
after registering for it with fcntl(sockfd, F_SETOWN, ...) and setting a
signal handler for it. This was the case whether the connection was
dropped due to a tcp keepalive failure, the dropping of a network
interface, or a normal disconnect. The next read() or recv() returned
zero bytes read but no asynchronous notification appeared to occur. I'm
under the impression it's really for use with asynchronous sockets, but
haven't tested this yet.
What does work well is occasionally poking the socket with recv(...,
MSG_DONTWAIT) while doing other work. Program attached. TCP keepalives
seem to work very well at least on my Linux test system, and it's easy
to test for a dud connection using recv(...) with the MSG_DONTWAIT and
(if desired) MSG_PEEK flags. If the connection has exited cleanly it'll
return a zero-size read; if the connection has dropped due to keepalive
failure it'll return ETIMEDOUT.
Pg's backend code already supports keepalives. I guess what'd be helpful
would be a periodic recv(..., MSG_DONTWAIT) on the client<->server
socket while the backend is working hard on a query. A SIGLARM would be
handy for that, though I guess Pg isn't used to having to test for EINTR
on syscalls...
--
Craig Ringer
/* Behavioural controls */
#define HAVE_TCP_KEEPALIVE
static const unsigned short listen_port = ;
static const unsigned int conn_check_delay_seconds = 10;
/* End behavioural controls */
#define _POSIX_SOURCE
#define _BSD_SOURCE
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#if defined(HAVE_LINUX_IP_RECVERR)
#include
#endif
/**
* Enable TCP keepalives on the socket fd passed, if the platform
* supports them.
*
* \param clsockfd file descriptor for target socket
* \returns -1 on error, 0 on success
*/
int set_keepalive(int clsockfd) {
#if defined(HAVE_TCP_KEEPALIVE)
int val = 1;
socklen_t vallen = sizeof(val);
int ret = setsockopt(clsockfd, SOL_SOCKET, SO_KEEPALIVE, &val, vallen);
if (ret != 0)
return ret;
/* For testing purposes, make the keepalives insanely, stupidly aggressive */
val = 5;
if (setsockopt(clsockfd, SOL_TCP, TCP_KEEPCNT, &val, vallen)) /* Only probe val time(s) before giving up */
perror("setsockopt(sock, SOL_TCP, TCP_KEEPCNT, 1)");
val = 5;
if (setsockopt(clsockfd, SOL_TCP, TCP_KEEPIDLE, &val, vallen)) /* Assume the connection is idle after val second(s) of inactivity */
perror("setsockopt(sock, SOL_TCP, TCP_KEEPIDLE, 1)");
val = 5;
if (setsockopt(clsockfd, SOL_TCP, TCP_KEEPINTVL, &val, vallen)) /* Poke the remote end every val second(s) when the connection is idle */
perror("setsockopt(sock, SOL_TCP, TCP_KEEPINTVL, 1)");
return 0;
#else
return -1;
#endif
}
static void printpeer(struct sockaddr_in * addr) {
char buf[20];
inet_ntop(AF_INET, &addr->sin_addr.s_addr, &buf[0], sizeof(buf));
printf("Accepted connection from peer %s:%hu\n", buf, ntohs(addr->sin_port));
}
static int createsrvsock() {
int srvsockfd = socket(AF_INET, SOCK_STREAM, 0);
struct sockaddr_in addr;
addr.sin_family = AF_INET;
addr.sin_port = htons(listen_port);
addr.sin_addr.s_addr = INADDR_ANY;
if (bind(srvsockfd, &addr, sizeof(addr))) {
printf("Couldn't bind socket: %i %s\n", errno, strerror(errno));
exit(errno);
}
if (listen(srvsockfd, 1)) {
printf("Couldn't listen: %i %s\n", errno, strerror(errno));
exit(errno);
}
return srvsockfd;
}
static void setupclsock(int clsockfd) {
/* Enable socket keepalives if available */
if (set_keepalive(clsockfd) == -1) {
printf("Couldn't enable socket keepalives\n");
}
}
static int pokeclient(int clsockfd) {
/* Anything eventful on the socket? */
char readbuf[2048];
ssize_t read_size = recv(clsockfd, &readbuf, sizeof(readbuf), MSG_DONTWAIT|MSG_PEEK );
if (read_size == 0) {
/* If the remote end disconnected cleanly we get a zero-length read */
printf("Connection dropped by remote\n");
return 0;
} else if (read_size > 0) {
read_size = read(clsockfd, &readbuf, sizeof(readbuf));
printf("Read and discarded %u bytes\n", read_size);
return 1;
} else if (read_size == -1) {
if (errno == EAGAIN) {
/* No data ready so MSG_DONTWAIT flag caused EAGAIN return. Nothing interesting happening on the socket. */
return 1;
} else if (errno == ETIMEDOUT) {
/* If a TCP keepalive kills the remote end, we exit here */
printf("recv(..., MSG_DONTWAIT|MSG_PEEK) returned ETIMEDOUT: Remote has dropp
[GENERAL] pg_stat_activity undocumented?
Hi, is there a reason why pg_stat_activity is not documented in the chapter "System Catalogs"? Is this not a "offical" view? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to prevent duplicate key error when two processes do DELETE/INSERT simultaneously?
On Wed, Jul 29, 2009 at 19:53, Brodie Thiesfield wrote: > On further investigation, since the logic requires the delete to be > made first to get rid of other possible rows, so I'll go with: > > DELETE > (if supported) INSERT OR REPLACE > (otherwise) INSERT, if duplicate key, UPDATE > You can use plpgsql to emulate insert or replace (I think there are some comments on emulating mysql specifically in the commented versions of the docs somewhere... but thats up to you to find em) see http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING example 38-1 -- 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] Blog post about Amazon Elastic Block Storage
John Cheng writes: > I know quite a number of people here, like myself, are intrigued by the > prospect of running PostgreSQL on Amazon's EC. I thought this blog post on > the performance of EBS was interesting, so I figure I'd share it with > everybody. > http://orion.heroku.com/past/2009/7/29/io_performance_on_ebs/ 7000 seeks per second? Somehow I don't believe that. Especially not in tandem with the sub-megabyte-per-second throughput figures ... 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] Blog post about Amazon Elastic Block Storage
I know quite a number of people here, like myself, are intrigued by the prospect of running PostgreSQL on Amazon's EC. I thought this blog post on the performance of EBS was interesting, so I figure I'd share it with everybody. http://orion.heroku.com/past/2009/7/29/io_performance_on_ebs/ John L. Cheng -- 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] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf'
On Wed, Jul 29, 2009 at 9:14 PM, Alan Deane wrote: > Sorry, yes the error, Doh!. > > Standard Server doesn't Listen (sounds like my ex :)) > > Here it is in full. > > Server doesn't listen > > could not connect to server; Connection timed out (0x274C/10060) Is the > server running on host 192.168.2.99 and accepting TCP/IP connection on port > 5432? In postgresql.conf, set: listen_addresses='*' Then restart the server. Then (in windows, right?) do a: netstat -a Look for port 5432. --Scott > > > As you say, the config looks OK. Very frustrating. > > Cheers, > Alan. > > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Chris > Sent: 30 July 2009 01:23 > To: a...@anitltd.co.uk > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] V8.3 fails to start after unremming hba_file = > 'ConfigDir/pg_hba.conf' > > Alan Deane wrote: > > Hi Chris, > > > > Thanks for your reply. > > > > Maybe I should have reworded it slightly better or fuller. I assumed that > > having ConfigDir/pg_hba.conf line in postgresql.conf would look for the > hba > > file in the directory relative to where postgres is installed. I.e. the > data > > directory and given that the service when it starts uses > > > > g:/Program Files/PostgreSQL/8.3/bin/pg_ctl.exe runservice -N > > "postgresql-8.3" -D "G:/Program Files/PostgreSQL/8.3/data" -w > > Must be based off where the service name is, I'm not sure. > > > and from what I've read the -D switch is basically telling postgres where > to > > find the conf files and setting the relative path for ConfigDir, so I was > > surprised that it looked for the hba conf in the system32 directory. > > Yeh, I would have thought it would pick up from the data dir not > somewhere else. > > > As for the networking side of things. I have opened port 5432 on my > firewall > > and I also disabled the firewall (Kaspersky btw not Windows it is > disabled > > permanently). > > > > My pg_hba.conf file reads. > > > > # IPv4 local connections: > > hostall all 127.0.0.1/32 md5 > > hostall all 192.168.2.0/16 trust > > > > I have only set it to trust for testing purposes as soon as I get a > connect > > it will be going back to md5. > > and the error when you try to connect is? > > Did you restart postgres after changing the pg_hba.conf file? > > Seems like it should work. > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf'
I have now isolated the problem to the firewall as suspected. I found that when I disabled Kaspersky firewall the bloody Windows firewall would kick in unbeknown to me. Once that was disabled as well then I could connect from another machine on the LAN. So the next step is easy, configure Kaspersky to let it through. Chris, Thank you very much for your help it is really appreciated. I guess I would have got there in the end but it always helps when someone else throws ideas at you. Thanks again, Alan. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alan Deane Sent: 30 July 2009 03:15 To: a...@anitltd.co.uk; 'Chris' Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf' Ok, I have just had a look at the firewall network monitor (Open Ports) and the following is showing. 5432TCP POSTGRES.EXE-D "G:/PROGRAM FILES/POSTGRESQL/8.3/DATA" 127.0.0.1 02:11:46 4141UDP POSTGRES.EXE-D "G:/PROGRAM FILES/POSTGRESQL/8.3/DATA" 127.0.0.1 02:12:56 Which looks OK to me. I uses more investigation is required Alan -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alan Deane Sent: 30 July 2009 03:00 To: 'Chris' Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf' Hi Chris, I can connect to the server locally with PgAdmin using localhost: 5432 192.168.2.99: 5432 127.0.0.1 : 5432 As for the Windows event logs they are all fine. The server logs. Look OK except for %LOG: invalid length of startup packet which could have been when I was trying to telnet in. What is very interesting is the following from the Status log I have three connections now, all from my local machine, local host, 127.0.0.1 and 192.168.2.99 and what is very interesting is the following from the Status log is the client column. 192.168.2.99:3321 127.0.0.1:3237 127.0.0.1:3223 Which is telling me that it is using different outgoing ports. Which means it will probably be a problem with the firewall as I think I set both incoming and outgoing to 5432. Will have to go and see if changing the outgoing to any makes a difference. Will reports back soon. Thanks, Alan. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Chris Sent: 30 July 2009 02:23 To: a...@anitltd.co.uk Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf' Alan Deane wrote: > Sorry, yes the error, Doh!. > > Standard Server doesn't Listen (sounds like my ex :)) > > Here it is in full. > > Server doesn't listen > > could not connect to server; Connection timed out (0x274C/10060) Is the > server running on host 192.168.2.99 and accepting TCP/IP connection on port > 5432? Anything in the postgres logs or windows event logger? Can you manually telnet to that? Try - from the postgres server back to itself (127.0.0.1 5432) - from the postgres server to it's ip (2.99) - from another machine It still sounds like it's firewall related. -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems compiling contribs in Open Solaris
Emanuel Calvo Franco escribió: > HI all, > > I trying to compile several contribs in Osol. I had in result some > problems to take them work. > > /opt/SUNWspro/bin/cc -Xa -xO3 -xarch=native -xspace -W0,-Lt > -W2,-Rcond_elim -Xa -xildoff -xc99=none -xCC -KPIC -I. > -I../../src/include -c -o xpath.o xpath.c > Putting child 0x080a3290 (xpath.o) PID 1718 on the chain. > Live child 0x080a3290 (xpath.o) PID 1718 > "xpath.c", line 18: cannot find include file: > "xpath.c", line 19: cannot find include file: > "xpath.c", line 20: cannot find include file: > "xpath.c", line 21: cannot find include file: > "xpath.c", line 22: cannot find include file: You need to tell configure where to find libxml's headers (--with-includes). -- 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] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf'
Ok, I have just had a look at the firewall network monitor (Open Ports) and the following is showing. 5432TCP POSTGRES.EXE-D "G:/PROGRAM FILES/POSTGRESQL/8.3/DATA" 127.0.0.1 02:11:46 4141UDP POSTGRES.EXE-D "G:/PROGRAM FILES/POSTGRESQL/8.3/DATA" 127.0.0.1 02:12:56 Which looks OK to me. I uses more investigation is required Alan -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alan Deane Sent: 30 July 2009 03:00 To: 'Chris' Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf' Hi Chris, I can connect to the server locally with PgAdmin using localhost: 5432 192.168.2.99: 5432 127.0.0.1 : 5432 As for the Windows event logs they are all fine. The server logs. Look OK except for %LOG: invalid length of startup packet which could have been when I was trying to telnet in. What is very interesting is the following from the Status log I have three connections now, all from my local machine, local host, 127.0.0.1 and 192.168.2.99 and what is very interesting is the following from the Status log is the client column. 192.168.2.99:3321 127.0.0.1:3237 127.0.0.1:3223 Which is telling me that it is using different outgoing ports. Which means it will probably be a problem with the firewall as I think I set both incoming and outgoing to 5432. Will have to go and see if changing the outgoing to any makes a difference. Will reports back soon. Thanks, Alan. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Chris Sent: 30 July 2009 02:23 To: a...@anitltd.co.uk Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf' Alan Deane wrote: > Sorry, yes the error, Doh!. > > Standard Server doesn't Listen (sounds like my ex :)) > > Here it is in full. > > Server doesn't listen > > could not connect to server; Connection timed out (0x274C/10060) Is the > server running on host 192.168.2.99 and accepting TCP/IP connection on port > 5432? Anything in the postgres logs or windows event logger? Can you manually telnet to that? Try - from the postgres server back to itself (127.0.0.1 5432) - from the postgres server to it's ip (2.99) - from another machine It still sounds like it's firewall related. -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf'
Hi Scott, Yep, already have that set. Thanks anyway. Alan _ From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Scott Mead Sent: 30 July 2009 02:45 To: a...@anitltd.co.uk Cc: Chris; pgsql-general@postgresql.org Subject: Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf' On Wed, Jul 29, 2009 at 9:14 PM, Alan Deane wrote: Sorry, yes the error, Doh!. Standard Server doesn't Listen (sounds like my ex :)) Here it is in full. Server doesn't listen could not connect to server; Connection timed out (0x274C/10060) Is the server running on host 192.168.2.99 and accepting TCP/IP connection on port 5432? In postgresql.conf, set: listen_addresses='*' Then restart the server. That should do the trick. --Scott As you say, the config looks OK. Very frustrating. Cheers, Alan. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Chris Sent: 30 July 2009 01:23 To: a...@anitltd.co.uk Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf' Alan Deane wrote: > Hi Chris, > > Thanks for your reply. > > Maybe I should have reworded it slightly better or fuller. I assumed that > having ConfigDir/pg_hba.conf line in postgresql.conf would look for the hba > file in the directory relative to where postgres is installed. I.e. the data > directory and given that the service when it starts uses > > g:/Program Files/PostgreSQL/8.3/bin/pg_ctl.exe runservice -N > "postgresql-8.3" -D "G:/Program Files/PostgreSQL/8.3/data" -w Must be based off where the service name is, I'm not sure. > and from what I've read the -D switch is basically telling postgres where to > find the conf files and setting the relative path for ConfigDir, so I was > surprised that it looked for the hba conf in the system32 directory. Yeh, I would have thought it would pick up from the data dir not somewhere else. > As for the networking side of things. I have opened port 5432 on my firewall > and I also disabled the firewall (Kaspersky btw not Windows it is disabled > permanently). > > My pg_hba.conf file reads. > > # IPv4 local connections: > hostall all 127.0.0.1/32 md5 > hostall all 192.168.2.0/16 trust > > I have only set it to trust for testing purposes as soon as I get a connect > it will be going back to md5. and the error when you try to connect is? Did you restart postgres after changing the pg_hba.conf file? Seems like it should work. -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf'
Hi Chris, I can connect to the server locally with PgAdmin using localhost: 5432 192.168.2.99: 5432 127.0.0.1 : 5432 As for the Windows event logs they are all fine. The server logs. Look OK except for %LOG: invalid length of startup packet which could have been when I was trying to telnet in. What is very interesting is the following from the Status log I have three connections now, all from my local machine, local host, 127.0.0.1 and 192.168.2.99 and what is very interesting is the following from the Status log is the client column. 192.168.2.99:3321 127.0.0.1:3237 127.0.0.1:3223 Which is telling me that it is using different outgoing ports. Which means it will probably be a problem with the firewall as I think I set both incoming and outgoing to 5432. Will have to go and see if changing the outgoing to any makes a difference. Will reports back soon. Thanks, Alan. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Chris Sent: 30 July 2009 02:23 To: a...@anitltd.co.uk Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf' Alan Deane wrote: > Sorry, yes the error, Doh!. > > Standard Server doesn't Listen (sounds like my ex :)) > > Here it is in full. > > Server doesn't listen > > could not connect to server; Connection timed out (0x274C/10060) Is the > server running on host 192.168.2.99 and accepting TCP/IP connection on port > 5432? Anything in the postgres logs or windows event logger? Can you manually telnet to that? Try - from the postgres server back to itself (127.0.0.1 5432) - from the postgres server to it's ip (2.99) - from another machine It still sounds like it's firewall related. -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to prevent duplicate key error when two processes do DELETE/INSERT simultaneously?
On Thu, Jul 30, 2009 at 12:23 AM, Tom Lane wrote: > Brodie Thiesfield writes: >> Essentially, I have two processes connecting to a single PG database >> and simultaneously issuing the following statements: > >> BEGIN; >> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; >> DELETE FROM licence_properties WHERE key = xxx; >> INSERT INTO licence_properties ... values with key = xxx; >> COMMIT > > You mean they both want to insert the same key? Yes. There are two processes working independently on the same data. They are both trying to work around the lack of INSERT OR REPLACE by doing a DELETE/INSERT. I was hoping that this could be somehow done as an atomic action. >> One of these processes is getting to the INSERT and failing with >> duplicate key error. >> ERROR: duplicate key value violates unique constraint > > If they both insert the same key, this is what *must* happen. Surely > you don't expect both to succeed, or one to fail and not tell you. Yes, it appears my grasp of transaction isolation apparently isn't so firm. So, the delete/insert combination cannot be made atomic and transaction isolation is only for read and not update. I was hoping that the updates would be serialized and so both would succeed with only one being the eventual winner. On further investigation, since the logic requires the delete to be made first to get rid of other possible rows, so I'll go with: DELETE (if supported) INSERT OR REPLACE (otherwise) INSERT, if duplicate key, UPDATE Regards, Brodie -- 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] Monitoring with pg_controldata
O > cron runs programs in a very limited environment. Things like TZ etc. are > usually not set. To see what your cron sets, just run a shell script with > something like > > #!/bin/sh > env > > and look at the email you get with the output. > Read the cron/crontab manpage. It tells you how to set environment variables > for your scripts. Thanks for the tip. It looks like the LANG environment wasn't set and obviously pg_controldata is reliant on that. What solved it for me was to set LANG=en_GB.UTF-8 because that was the LANG setting in the postgres user env. Thanks again. -- 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] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf'
On Wed, Jul 29, 2009 at 9:14 PM, Alan Deane wrote: > Sorry, yes the error, Doh!. > > Standard Server doesn't Listen (sounds like my ex :)) > > Here it is in full. > > Server doesn't listen > > could not connect to server; Connection timed out (0x274C/10060) Is the > server running on host 192.168.2.99 and accepting TCP/IP connection on port > 5432? In postgresql.conf, set: listen_addresses='*' Then restart the server. That should do the trick. --Scott > > > As you say, the config looks OK. Very frustrating. > > Cheers, > Alan. > > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Chris > Sent: 30 July 2009 01:23 > To: a...@anitltd.co.uk > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] V8.3 fails to start after unremming hba_file = > 'ConfigDir/pg_hba.conf' > > Alan Deane wrote: > > Hi Chris, > > > > Thanks for your reply. > > > > Maybe I should have reworded it slightly better or fuller. I assumed that > > having ConfigDir/pg_hba.conf line in postgresql.conf would look for the > hba > > file in the directory relative to where postgres is installed. I.e. the > data > > directory and given that the service when it starts uses > > > > g:/Program Files/PostgreSQL/8.3/bin/pg_ctl.exe runservice -N > > "postgresql-8.3" -D "G:/Program Files/PostgreSQL/8.3/data" -w > > Must be based off where the service name is, I'm not sure. > > > and from what I've read the -D switch is basically telling postgres where > to > > find the conf files and setting the relative path for ConfigDir, so I was > > surprised that it looked for the hba conf in the system32 directory. > > Yeh, I would have thought it would pick up from the data dir not > somewhere else. > > > As for the networking side of things. I have opened port 5432 on my > firewall > > and I also disabled the firewall (Kaspersky btw not Windows it is > disabled > > permanently). > > > > My pg_hba.conf file reads. > > > > # IPv4 local connections: > > hostall all 127.0.0.1/32 md5 > > hostall all 192.168.2.0/16 trust > > > > I have only set it to trust for testing purposes as soon as I get a > connect > > it will be going back to md5. > > and the error when you try to connect is? > > Did you restart postgres after changing the pg_hba.conf file? > > Seems like it should work. > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf'
Alan Deane wrote: Sorry, yes the error, Doh!. Standard Server doesn't Listen (sounds like my ex :)) Here it is in full. Server doesn't listen could not connect to server; Connection timed out (0x274C/10060) Is the server running on host 192.168.2.99 and accepting TCP/IP connection on port 5432? Anything in the postgres logs or windows event logger? Can you manually telnet to that? Try - from the postgres server back to itself (127.0.0.1 5432) - from the postgres server to it's ip (2.99) - from another machine It still sounds like it's firewall related. -- Postgresql & php tutorials http://www.designmagick.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] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf'
Sorry, yes the error, Doh!. Standard Server doesn't Listen (sounds like my ex :)) Here it is in full. Server doesn't listen could not connect to server; Connection timed out (0x274C/10060) Is the server running on host 192.168.2.99 and accepting TCP/IP connection on port 5432? As you say, the config looks OK. Very frustrating. Cheers, Alan. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Chris Sent: 30 July 2009 01:23 To: a...@anitltd.co.uk Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf' Alan Deane wrote: > Hi Chris, > > Thanks for your reply. > > Maybe I should have reworded it slightly better or fuller. I assumed that > having ConfigDir/pg_hba.conf line in postgresql.conf would look for the hba > file in the directory relative to where postgres is installed. I.e. the data > directory and given that the service when it starts uses > > g:/Program Files/PostgreSQL/8.3/bin/pg_ctl.exe runservice -N > "postgresql-8.3" -D "G:/Program Files/PostgreSQL/8.3/data" -w Must be based off where the service name is, I'm not sure. > and from what I've read the -D switch is basically telling postgres where to > find the conf files and setting the relative path for ConfigDir, so I was > surprised that it looked for the hba conf in the system32 directory. Yeh, I would have thought it would pick up from the data dir not somewhere else. > As for the networking side of things. I have opened port 5432 on my firewall > and I also disabled the firewall (Kaspersky btw not Windows it is disabled > permanently). > > My pg_hba.conf file reads. > > # IPv4 local connections: > hostall all 127.0.0.1/32 md5 > hostall all 192.168.2.0/16 trust > > I have only set it to trust for testing purposes as soon as I get a connect > it will be going back to md5. and the error when you try to connect is? Did you restart postgres after changing the pg_hba.conf file? Seems like it should work. -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf'
Ah, OK. So what you are saying is that I don't need the 'ConfigDir/pg_hba.conf' in postgresql.conf . I guess that makes sense as well because you would only need that if it wasn't located in the data directory. Although having said that, I think I uncommented it because I was having trouble seeing the changes to the IP addresses in pgAdmin. But that was a couple of days ago now and a lot of water has passed under the bridge. I'll leave it hardcoded for now as it does seem to be picking up the setting in pgAdmin. Thanks for the link to the manual. Cheers, Alan. _ From: Scott Mead [mailto:scott.li...@enterprisedb.com] Sent: 30 July 2009 01:14 To: a...@anitltd.co.uk Cc: Chris; pgsql-general@postgresql.org Subject: Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf' Re-REM (called commenting for us non-windows geeks :-) the line Look in your data directory, the file exists and is used automatically, you don't need to point the postgresql.conf file to it explicitly. Look at: http://www.postgresql.org/docs/8.3/interactive/client-authentication.html For details on configuring the file. Good Luck --Scott
Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf'
Re-REM (called commenting for us non-windows geeks :-) the line Look in your data directory, the file exists and is used automatically, you don't need to point the postgresql.conf file to it explicitly. Look at: http://www.postgresql.org/docs/8.3/interactive/client-authentication.html For details on configuring the file. Good Luck --Scott
Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf'
Alan Deane wrote: Hi Chris, Thanks for your reply. Maybe I should have reworded it slightly better or fuller. I assumed that having ConfigDir/pg_hba.conf line in postgresql.conf would look for the hba file in the directory relative to where postgres is installed. I.e. the data directory and given that the service when it starts uses g:/Program Files/PostgreSQL/8.3/bin/pg_ctl.exe runservice -N "postgresql-8.3" -D "G:/Program Files/PostgreSQL/8.3/data" -w Must be based off where the service name is, I'm not sure. and from what I've read the -D switch is basically telling postgres where to find the conf files and setting the relative path for ConfigDir, so I was surprised that it looked for the hba conf in the system32 directory. Yeh, I would have thought it would pick up from the data dir not somewhere else. As for the networking side of things. I have opened port 5432 on my firewall and I also disabled the firewall (Kaspersky btw not Windows it is disabled permanently). My pg_hba.conf file reads. # IPv4 local connections: hostall all 127.0.0.1/32 md5 hostall all 192.168.2.0/16 trust I have only set it to trust for testing purposes as soon as I get a connect it will be going back to md5. and the error when you try to connect is? Did you restart postgres after changing the pg_hba.conf file? Seems like it should work. -- Postgresql & php tutorials http://www.designmagick.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] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf'
Hi Chris, Thanks for your reply. Maybe I should have reworded it slightly better or fuller. I assumed that having ConfigDir/pg_hba.conf line in postgresql.conf would look for the hba file in the directory relative to where postgres is installed. I.e. the data directory and given that the service when it starts uses g:/Program Files/PostgreSQL/8.3/bin/pg_ctl.exe runservice -N "postgresql-8.3" -D "G:/Program Files/PostgreSQL/8.3/data" -w and from what I've read the -D switch is basically telling postgres where to find the conf files and setting the relative path for ConfigDir, so I was surprised that it looked for the hba conf in the system32 directory. I agree I could supply the full path to the file in postgresql.conf and it will probably work. However, it doesn't help my understand why it didn't pick it up using the relative path. As for the networking side of things. I have opened port 5432 on my firewall and I also disabled the firewall (Kaspersky btw not Windows it is disabled permanently). My pg_hba.conf file reads. # IPv4 local connections: hostall all 127.0.0.1/32 md5 hostall all 192.168.2.0/16 trust I have only set it to trust for testing purposes as soon as I get a connect it will be going back to md5. Thanks for taking the time to look at this. It's really appreciated. Cheers, Alan. -Original Message- From: Chris [mailto:dmag...@gmail.com] Sent: 30 July 2009 00:37 To: a...@anitltd.co.uk Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf' Alan Deane wrote: > Hi All, > > I was wondering if anyone has had this problem and knows an answer. This > is the first time I have used postgres so I am at a loss after trying > many things as to how to solve the problem. > > > > I've unremmed the line hba_file = 'ConfigDir/pg_hba.conf' in the > postgresql.conf file as I want to be able to connect to the data base > from another machine on my LAN. > I have also added the IP range into the pg_hba.conf file. > > Now I get a Windows Services error saying the server started and stopped > and no access on the local machine, unsurprisingly. > > Having a look at the event viewer showed the following error. > > %t FATAL: could not open configuration file > "C:/WINDOWS/system32/ConfigDir/pg_hba.conf": No such file or directory > > Hmm, that's interesting, why would it be looking there and not in the > data folder where pg_hba.conf resides? Because you've supplied a relative path. You can supply a full path: G:/Program Files/PostgreSQL/8.3/data/pg_hba.conf or leave it commented out for it to look in the data/ folder you specify already. > Also despite enabling the network IP addresses in pg_hba.conf I still > can't connect to the database from a remote machine. Although I'd like > to solve the system32 problem first before I start worrying about > network connections. Can you post the uncommented parts of pg_hba.conf and what ip you are trying to connect from? Also what's the error? and lastly - have you opened port 5432 in your firewall? -- Postgresql & php tutorials http://www.designmagick.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] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf'
Alan Deane wrote: Hi All, I was wondering if anyone has had this problem and knows an answer. This is the first time I have used postgres so I am at a loss after trying many things as to how to solve the problem. I've unremmed the line hba_file = 'ConfigDir/pg_hba.conf' in the postgresql.conf file as I want to be able to connect to the data base from another machine on my LAN. I have also added the IP range into the pg_hba.conf file. Now I get a Windows Services error saying the server started and stopped and no access on the local machine, unsurprisingly. Having a look at the event viewer showed the following error. %t FATAL: could not open configuration file "C:/WINDOWS/system32/ConfigDir/pg_hba.conf": No such file or directory Hmm, that's interesting, why would it be looking there and not in the data folder where pg_hba.conf resides? Because you've supplied a relative path. You can supply a full path: G:/Program Files/PostgreSQL/8.3/data/pg_hba.conf or leave it commented out for it to look in the data/ folder you specify already. Also despite enabling the network IP addresses in pg_hba.conf I still can’t connect to the database from a remote machine. Although I’d like to solve the system32 problem first before I start worrying about network connections. Can you post the uncommented parts of pg_hba.conf and what ip you are trying to connect from? Also what's the error? and lastly - have you opened port 5432 in your firewall? -- Postgresql & php tutorials http://www.designmagick.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] V8.3 fails to start after unremming hba_file = 'ConfigDir/pg_hba.conf'
Hi All, I was wondering if anyone has had this problem and knows an answer. This is the first time I have used postgres so I am at a loss after trying many things as to how to solve the problem. I've unremmed the line hba_file = 'ConfigDir/pg_hba.conf' in the postgresql.conf file as I want to be able to connect to the data base from another machine on my LAN. I have also added the IP range into the pg_hba.conf file. Now I get a Windows Services error saying the server started and stopped and no access on the local machine, unsurprisingly. Having a look at the event viewer showed the following error. %t FATAL: could not open configuration file "C:/WINDOWS/system32/ConfigDir/pg_hba.conf": No such file or directory Hmm, that's interesting, why would it be looking there and not in the data folder where pg_hba.conf resides? I then created a ConfigDir in the system32 dir and copied the pg_hba.conf into there. Now the server starts. However, It doesn't ring true to my mind that the file should be located there. Also despite enabling the network IP addresses in pg_hba.conf I still can't connect to the database from a remote machine. Although I'd like to solve the system32 problem first before I start worrying about network connections. This is a brand new install and it has been installed in the default directories, however, on a different drive rather than c: BTW, the properties of the service read:- g:/Program Files/PostgreSQL/8.3/bin/pg_ctl.exe runservice -N "postgresql-8.3" -D "G:/Program Files/PostgreSQL/8.3/data" -w Any help appreciated, Thanks Alan.
Re: [GENERAL] combining db's- importing primary keys
Chris wrote: Bob Gobeille wrote: On Jul 29, 2009, at 8:26 AM, nha wrote: Hello, Le 29/07/09 15:02, Jasen Betts a écrit : On 2009-07-27, Bob Gobeille wrote: I would like to combine multiple databases (same schema) into one master db. Does anyone know how I can reconcile all my primary and foreign keys to maintain referential integrity. prefix them all with something that's unique to their source database. easy to say, hard to do. A process (and solution) may be different whether: -the master db will replace all other databases (ie. data manipulation would directly perform on data managed by master db); -or the master db will reflect a combined view of data hosted by all other databases (ie. data manipulation would still perform on distributed databases and master db would play a role of federated view); -or both (data manipulation have to be manageable at the both side: on master db and on distributed db). In my immediate case, I'm simply combining 7 global databases into a single master. Eventually, I would like to allow our (fossology.org) users to subscribe to data in other databases. That is, allow users to do periodic one-way batch updates from other servers. Have you thought about schemas? You can set up different users with different search paths so all the data's in one spot, and getting to another schema is easy enough. No changes necessary to the data itself, just the calling code. Blah, ignore - I didn't read your first email properly :P -- Postgresql & php tutorials http://www.designmagick.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] combining db's- importing primary keys
Bob Gobeille wrote: On Jul 29, 2009, at 8:26 AM, nha wrote: Hello, Le 29/07/09 15:02, Jasen Betts a écrit : On 2009-07-27, Bob Gobeille wrote: I would like to combine multiple databases (same schema) into one master db. Does anyone know how I can reconcile all my primary and foreign keys to maintain referential integrity. prefix them all with something that's unique to their source database. easy to say, hard to do. A process (and solution) may be different whether: -the master db will replace all other databases (ie. data manipulation would directly perform on data managed by master db); -or the master db will reflect a combined view of data hosted by all other databases (ie. data manipulation would still perform on distributed databases and master db would play a role of federated view); -or both (data manipulation have to be manageable at the both side: on master db and on distributed db). In my immediate case, I'm simply combining 7 global databases into a single master. Eventually, I would like to allow our (fossology.org) users to subscribe to data in other databases. That is, allow users to do periodic one-way batch updates from other servers. Have you thought about schemas? You can set up different users with different search paths so all the data's in one spot, and getting to another schema is easy enough. No changes necessary to the data itself, just the calling code. -- Postgresql & php tutorials http://www.designmagick.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] Monitoring with pg_controldata
On Wednesday 29 July 2009, Tim Uckun wrote: > > What is the issue here? Some sort of a timezone problem? How do I tell > it what time zone to use? > > Thanks. cron runs programs in a very limited environment. Things like TZ etc. are usually not set. To see what your cron sets, just run a shell script with something like #!/bin/sh env and look at the email you get with the output. Read the cron/crontab manpage. It tells you how to set environment variables for your scripts. HTH UC -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Monitoring with pg_controldata
I am trying to monitor my replication lag with pg_controldata and it's driving me nuts. If I run pg_controldata from the command line as user postgres or root I get the following line Time of latest checkpoint:Thu 30 Jul 2009 00:36:12 NZST If I run it from the crontab I get this output Time of latest checkpoint:Wed Jul 29 23:05:34 2009 Here is the cron entity /usr/lib/postgresql/8.3/bin/pg_controldata /var/lib/postgresql/8.3/main >> /tmp/pg_standby_log What is the issue here? Some sort of a timezone problem? How do I tell it what time zone to use? Thanks. -- 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] OID in $_TD->{new}/$_TD->{old}
> > It is posible to access to the row OID of the row which fired a pl/perl
> > trigger?
>
> > If I try to access to $_TD->{new}{oid} or $_TD->{old}{oid} I have no
> > result.
>
> It looks to me like plperl_hash_from_tuple considers only user
> attributes. Not sure if this would be worth changing.
>
> regards, tom lane
And is there any other way to access to the row OID from the trigger?
Thanks
--
Antonio José García Lagar
Compact Software International SA
http://www.c17.net
--
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] Strange Planner Issues
On Wed, Jul 29, 2009 at 8:01 PM, Jake Stride wrote:
> Hi,
>
> I have 2 databases running on the same server. One is a dump of the
> other, however the query plans for the same query on the same tables
> in each database is wildly different and I cannot work out why.
> -> Seq Scan on
> person_contact_methods e (cost=0.00..3281.89 rows=1 width=8) (actual
> time=114.181..114.181 rows=0 loops=1)
> Filter: (main AND
> ((contact)::text ~~* 'j...@omelett.es'::text) AND (type =
> 'E'::bpchar))
> -> Seq Scan on person_contact_methods e (cost=0.00..2942.06
> rows=1 width=8) (actual time=3.718..184.602 rows=772 loops=1)
> Filter: (("type" = 'E'::bpchar) AND main AND
> ((contact)::text ~~* 'j...@omelett.es'::text))
They don't look like the same data from here. One has no matching
records in this table and the other has over 700.
--
greg
http://mit.edu/~gsstark/resume.pdf
--
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] Query optimisation and sorting on external merge
Thanks I'll take a look into it - they query you provide seems to take longer in the query plan but I can see where you are coming from and it's good base to work from. Jake -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Strange Planner Issues
Hi,
I have 2 databases running on the same server. One is a dump of the
other, however the query plans for the same query on the same tables
in each database is wildly different and I cannot work out why.
The first result below is for the dump of the database and executes in
a reasonable time. The second is on the same server, just different
DB.
Does anybody have any ideas/pointers. Both have been vacuum analyzed.
QUERY PLAN
--
Aggregate (cost=3299.79..3299.80 rows=1 width=8) (actual
time=114.311..114.312 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.00..3299.79 rows=1 width=8)
(actual time=114.196..114.196 rows=0 loops=1)
-> Nested Loop Left Join (cost=0.00..3296.49 rows=1
width=16) (actual time=114.193..114.193 rows=0 loops=1)
Filter: ((u.username IS NULL) OR u.enabled)
-> Nested Loop Left Join (cost=0.00..3295.99 rows=1
width=16) (actual time=114.190..114.190 rows=0 loops=1)
-> Nested Loop Left Join (cost=0.00..3293.09
rows=1 width=16) (actual time=114.188..114.188 rows=0 loops=1)
-> Nested Loop (cost=0.00..3290.19 rows=1
width=16) (actual time=114.184..114.184 rows=0 loops=1)
-> Seq Scan on
person_contact_methods e (cost=0.00..3281.89 rows=1 width=8) (actual
time=114.181..114.181 rows=0 loops=1)
Filter: (main AND
((contact)::text ~~* 'j...@omelett.es'::text) AND (type =
'E'::bpchar))
-> Index Scan using person_pkey on
people p (cost=0.00..8.28 rows=1 width=16) (never executed)
Index Cond: (p.id = e.person_id)
Filter: (p.usercompanyid = 74607::bigint)
-> Index Scan using
person_contact_methods_person_id_index on person_contact_methods m
(cost=0.00..2.89 rows=1 width=8) (never executed)
Index Cond: (p.id = m.person_id)
Filter: (m.main AND (m.type = 'M'::bpchar))
-> Index Scan using
person_contact_methods_person_id_index on person_contact_methods ph
(cost=0.00..2.89 rows=1 width=8) (never executed)
Index Cond: (p.id = ph.person_id)
Filter: (ph.main AND (ph.type = 'T'::bpchar))
-> Index Scan using users_person_id_key on users u
(cost=0.00..0.49 rows=1 width=28) (never executed)
Index Cond: (u.person_id = p.id)
-> Index Scan using company_id_key on organisations org
(cost=0.00..3.28 rows=1 width=8) (never executed)
Index Cond: (org.id = p.organisation_id)
Total runtime: 115.119 ms
QUERY PLAN
---
Aggregate (cost=27525.76..27525.77 rows=1 width=8) (actual
time=60573.233..60573.234 rows=1 loops=1)
-> Nested Loop (cost=24244.80..27525.75 rows=1 width=8) (actual
time=60497.421..60573.169 rows=2 loops=1)
Join Filter: ("inner".id = "outer".person_id)
-> Seq Scan on person_contact_methods e (cost=0.00..2942.06
rows=1 width=8) (actual time=3.718..184.602 rows=772 loops=1)
Filter: (("type" = 'E'::bpchar) AND main AND
((contact)::text ~~* 'j...@omelett.es'::text))
-> Merge Left Join (cost=24244.80..24537.57 rows=3690
width=8) (actual time=17.930..76.188 rows=3495 loops=772)
Merge Cond: ("outer".id = "inner".person_id)
-> Merge Left Join (cost=22122.13..22354.98 rows=3690
width=8) (actual time=14.359..59.647 rows=3495 loops=772)
Merge Cond: ("outer".id = "inner".person_id)
-> Merge Left Join (cost=17317.28..17366.04
rows=3690 width=8) (actual time=2.179..12.455 rows=3495 loops=772)
Merge Cond: ("outer".id = "inner".person_id)
Filter: (("inner".username IS NULL) OR
"inner".enabled)
-> Sort (cost=16771.51..16780.74
rows=3690 width=8) (actual time=0.130..2.082 rows=3499 loops=772)
Sort Key: p.id
-> Nested Loop Left Join
(cost=29.91..16552.89 rows=3690 width=8) (actual time=2.979..91.991
rows=3499 loops=1)
-> Bitmap Heap Scan on people
p (cost=29.91..3186.38 rows=3690 width=16) (actual time=2.867..30.251
rows=3499 loops=1)
Recheck Cond:
(usercompanyid = 74607::bigint)
-> Bitmap Index Scan on
person_usercompanyid (cost=0.00..29.91 rows=3690 width=0) (actual
time=2.717..2.717 rows=10241 l
Re: [GENERAL] How do I run PG Tuning Wizard on Linux?
On 07/29/2009 09:46 PM, Jennifer Trey wrote: Is this it? http://pgfoundry.org/projects/pgtune I thought it was EnterpriseDB. Perhaps that's only for windows? Thanks / Jen You can have TuningWizard (EnterpriseDB's) for linux also available through StackBuilder. -- Regards, Sachin Srivastava www.enterprisedb.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] comparing NEW and OLD (any good this way?)
On Wed, Jul 29, 2009 at 9:40 AM, Sam Mason wrote: > On Wed, Jul 29, 2009 at 01:15:27PM +, Jasen Betts wrote: >> On 2009-07-23, Sam Mason wrote: >> > >> > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Attention_on_IS_NULL_and_IS_NOT_NULL_operators_for_composite_types >> > >> > is scary; even worse is that it was changed to be like this in 8.2 >> > because the standard says it should behave this way. What on earth were >> > they thinking when they defined the standard this way? >> >> since any comparson involving those tuples will return NULL true is the >> correct value for IS NULL > > I think you missed the point: > > SELECT r IS NULL, r IS NOT NULL > FROM (VALUES (1,NULL)) r(a,b); > > returns FALSE for *both* columns. How can a row be both NULL *and* > non-NULL? > >> if you are bothered by this behavior you are misusing NULL. > > I understand that this is the specified behavior, and hence PG is > correctly following the spec--but it still bothers me. not only that, but while pg's treats composite types with null members as null according to the 'is null' operator (in accordance with the spec), but as not null everywhere else. thus, for example, a 'null' composite type is counted in the count() aggregate function. how funky is that? 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] Clients disconnect but query still runs
Greg Stark writes: > That is a question. But actually I think sigio might be fairly > portable -- at least the first hit I found was for someone complaining > that it wasn't working on Linux (due to a bug) and this broke their > app which worked everywhere else. > In any case this would be a feature which if it didn't work would > leave us just where we are today. That's another advantage over trying > to do something with sigurg which would be far more likely to cause > headaches if it behave incorrectly. [ reads man pages for awhile... ] It looks to me like SIGIO is sent whenever the socket comes ready for either reading or writing, which makes it pretty nearly useless for detecting a broken-connection condition. You'd be too busy filtering out uninteresting signals --- and the signal handler itself can't do very much of that work. 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] How do I run PG Tuning Wizard on Linux?
Is this it? http://pgfoundry.org/projects/pgtune I thought it was EnterpriseDB. Perhaps that's only for windows? Thanks / Jen
Re: [GENERAL] Clients disconnect but query still runs
On Wed, Jul 29, 2009 at 3:17 PM, Tom Lane wrote: > Greg Stark writes: >> On Wed, Jul 29, 2009 at 1:58 PM, Jasen Betts wrote: >>> can't coerce a signal from the network stack? the linux socket(2) >>> manpage is full of promise (SIGPIPE, SIGURG, SIGIO) > > > And the other question is how much of what you read in the Linux manpage > is portable to any other system... That is a question. But actually I think sigio might be fairly portable -- at least the first hit I found was for someone complaining that it wasn't working on Linux (due to a bug) and this broke their app which worked everywhere else. In any case this would be a feature which if it didn't work would leave us just where we are today. That's another advantage over trying to do something with sigurg which would be far more likely to cause headaches if it behave incorrectly. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] combining db's- importing primary keys
Hello, Le 29/07/09 16:44, Bob Gobeille a écrit : > > On Jul 29, 2009, at 8:26 AM, nha wrote: > >> Hello, >> >> Le 29/07/09 15:02, Jasen Betts a écrit : >>> On 2009-07-27, Bob Gobeille wrote: I would like to combine multiple databases (same schema) into one master db. Does anyone know how I can reconcile all my primary and foreign keys to maintain referential integrity. >>> >>> prefix them all with something that's unique to their source database. >>> >>> easy to say, hard to do. >>> >> >> A process (and solution) may be different whether: [...cases...] > > In my immediate case, I'm simply combining 7 global databases into a > single master. > > Eventually, I would like to allow our (fossology.org) users to subscribe > to data in other databases. That is, allow > users to do periodic one-way batch updates from other servers. > > Bob In such a purpose (where asynchronous updates seem reasonable), prefixing (as suggested by Jasen Bett) or adding server-based column to current keys (as you mention thereafter) look like handy solutions. Good plan. Regards. -- nha / Lyon / France. -- 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] Relational Algebra and Aggregate Functions
On Wed, 2009-07-29 at 10:19 +0300, Peter Eisentraut wrote: > Is it weird that "Database in Depth" is shorter and easier than "Introduction > to Database Systems"? And they're by the same author, too. I agree that it's a little strange. The former is more conceptual and starts off assuming that you are familiar with things like normalization. The latter is more like a textbook: more complete and more formal. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How do I run PG Tuning Wizard on Linux?
I have been using Windows before and that was just an installer. How can I install and run something similar on Ubuntu ? Thanks / Jen
Re: [GENERAL] integration of fulltext search in bytea/docs
Hello Dne 29. červenec 2009 16:46 Radek Novotný napsal(a): > Hello, > is there in the roadmap of postgre integration of fulltext searching in > documents saved in blobs (bytea)? > What I know, no. PostgreSQL doesn't know about others binary formats, so it cannot do it. > For example linux antiword can export fine text output that can be inserted > into varchar field. I understand it well. Three years ago, we used antiword. We stored two values - bytea - original word doc, and text - antiword output. The overhead is minimal, and this solution worked very well. > > Would be very very nice (postgre users can be proud to be first) to save > documents into bytea and search that field via to_tsvector, to_tsquery ... > It's should be very slow. You have to do repeated transformation. Pavel Stehule > -- > > www.publicstream.cz - vytvořili jsme produkt pro živé přenosy (online > streaming) a videoarchív ve formátu FLASH. > > Bc. Radek Novotný > jednatel Mediawork group s.r.o. > > tel.: +420 724 020 361 > email: radek.novo...@mediawork.cz > http://www.mediawork.cz > > -- 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] OID in $_TD->{new}/$_TD->{old}
Antonio =?ISO-8859-1?Q?Jos=E9_Garc=EDa?= Lagar writes:
> It is posible to access to the row OID of the row which fired a pl/perl
> trigger?
> If I try to access to $_TD->{new}{oid} or $_TD->{old}{oid} I have no
> result.
It looks to me like plperl_hash_from_tuple considers only user
attributes. Not sure if this would be worth changing.
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] integration of fulltext search in bytea/docs
On Wed, Jul 29, 2009 at 04:46:43PM +0200, Radek Novotnnn wrote: > is there in the roadmap of postgre integration of fulltext searching in > documents saved in blobs (bytea)? Do you mean bytea or large-objects? > Would be very very nice (postgre users can be proud to be first) to save > documents into bytea and search that field via to_tsvector, to_tsquery ... This seems easy; for large objects, just use lo_export() to dump the blob out to the filesystem, and then use something like pl/perl to run antiword on it, saving the results to another file and then returning the file line-by-line as a SETOF TEXT (I think this is the best way of handling things in case the resulting text file is enormous anyway). If this code was called "runfilter" we can use it like: UPDATE myfiles f SET tsidx = ( SELECT ts_accum(to_tsvector(t)) FROM runfilter(f.loid) t); Where we've defined ts_accum to be: CREATE AGGREGATE ts_accum (tsvector) ( SFUNC = tsvector_concat, STYPE = tsvector, INITCOND = '' ); bytea is different because you know when the values has changed (i.e. write a trigger) but you need to write more code to get the bytea value out into the filesystem. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to prevent duplicate key error when two processes do DELETE/INSERT simultaneously?
Brodie Thiesfield writes: > Essentially, I have two processes connecting to a single PG database > and simultaneously issuing the following statements: > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > DELETE FROM licence_properties WHERE key = xxx; > INSERT INTO licence_properties ... values with key = xxx; > COMMIT You mean they both want to insert the same key? > One of these processes is getting to the INSERT and failing with > duplicate key error. > ERROR: duplicate key value violates unique constraint If they both insert the same key, this is what *must* happen. Surely you don't expect both to succeed, or one to fail and not tell you. > The DELETE should prevent this duplicate key error from occurring. I > thought that the ISOLATION LEVEL SERIALIZABLE would fix this problem > (being that the second process can see the INSERT from the first > process after it has done the DELETE), but it doesn't. I think you've got the effects of SERIALIZABLE backward, but in any case SERIALIZABLE does not affect uniqueness checks. Unique is unique. 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] OID in $_TD->{new}/$_TD->{old}
It is posible to access to the row OID of the row which fired a pl/perl
trigger?
If I try to access to $_TD->{new}{oid} or $_TD->{old}{oid} I have no
result.
Thanks in advance!
--
Antonio José García Lagar
Compact Software International SA
http://www.c17.net
--
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] Monitoring tools for Postgresql
I am running postgresql server on RHEL 5 system. Greg Smith-12 wrote: > > On Tue, 28 Jul 2009, mukeshp wrote: > >> Can anyone suggest me tools for monitoring postgresql server. ? > > An idea what operating system you're running the server on would help > here. > > -- > * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/Monitoring-tools-for-Postgresql-tp24713155p24715086.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] integration of fulltext search in bytea/docs
Hello, is there in the roadmap of postgre integration of fulltext searching in documents saved in blobs (bytea)? For example linux antiword can export fine text output that can be inserted into varchar field. Would be very very nice (postgre users can be proud to be first) to save documents into bytea and search that field via to_tsvector, to_tsquery ... -- www.publicstream.cz - vytvořili jsme produkt pro živé přenosy (online streaming) a videoarchív ve formátu FLASH. Bc. Radek Novotný jednatel Mediawork group s.r.o. tel.: +420 724 020 361 email: radek.novo...@mediawork.cz http://www.mediawork.cz
Re: [GENERAL] combining db's- importing primary keys
On Jul 29, 2009, at 8:26 AM, nha wrote: Hello, Le 29/07/09 15:02, Jasen Betts a écrit : On 2009-07-27, Bob Gobeille wrote: I would like to combine multiple databases (same schema) into one master db. Does anyone know how I can reconcile all my primary and foreign keys to maintain referential integrity. prefix them all with something that's unique to their source database. easy to say, hard to do. A process (and solution) may be different whether: -the master db will replace all other databases (ie. data manipulation would directly perform on data managed by master db); -or the master db will reflect a combined view of data hosted by all other databases (ie. data manipulation would still perform on distributed databases and master db would play a role of federated view); -or both (data manipulation have to be manageable at the both side: on master db and on distributed db). In my immediate case, I'm simply combining 7 global databases into a single master. Eventually, I would like to allow our (fossology.org) users to subscribe to data in other databases. That is, allow users to do periodic one-way batch updates from other servers. Bob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to prevent duplicate key error when two processes do DELETE/INSERT simultaneously?
Hi,
I've got a problem with a PG client that I'm not sure how to fix.
Essentially, I have two processes connecting to a single PG database
and simultaneously issuing the following statements:
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DELETE FROM licence_properties WHERE key = xxx;
INSERT INTO licence_properties ... values with key = xxx;
COMMIT
One of these processes is getting to the INSERT and failing with
duplicate key error.
ERROR: duplicate key value violates unique constraint
The DELETE should prevent this duplicate key error from occurring. I
thought that the ISOLATION LEVEL SERIALIZABLE would fix this problem
(being that the second process can see the INSERT from the first
process after it has done the DELETE), but it doesn't.
I am obviously going about this the wrong way. The database layer is
implemented for a number of different servers and so I was trying to
keep it simple. However, perhaps this is something that I can't
simplify. Should I do SELECT FOR UPDATE and then either an INSERT or
UPDATE?
I would be very appreciative if someone more knowledgeable would point
me to the correct way of doing this? The full PG log of the two
processes follows (in case it is useful).
Regards,
Brodie
log_line_prefix = '%m %p %x %v '
(timestamp, process ID, transaction ID, virtual transaction ID)
logs are stable sorted by process ID then timestamp.
Process 1:
2009-07-29 23:01:01.218 JST 6108 0 3/65 LOG: statement:
BEGIN;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2009-07-29 23:01:01.218 JST 6108 0 3/65 LOG: statement:
SAVEPOINT _EXEC_SVP_03741378
2009-07-29 23:01:01.218 JST 6108 0 3/65 LOG: statement: SELECT
lid FROM licences WHERE groupid = E'' AND userid = E'test';
2009-07-29 23:01:01.218 JST 6108 0 3/65 LOG: statement: RELEASE
_EXEC_SVP_03741378
2009-07-29 23:01:01.218 JST 6108 0 3/65 LOG: statement:
SAVEPOINT _EXEC_SVP_03740350
2009-07-29 23:01:01.218 JST 6108 0 3/65 LOG: statement: DELETE
FROM licence_properties WHERE lid = '114' AND pkey = E'mytestprop' AND
sessionid IN (0, '0') AND readonly IN (0, 0);
2009-07-29 23:01:01.218 JST 6108 0 3/65 LOG: statement: RELEASE
_EXEC_SVP_03740350
2009-07-29 23:01:01.218 JST 6108 0 3/65 LOG: statement:
SAVEPOINT _EXEC_SVP_03742768
2009-07-29 23:01:01.218 JST 6108 0 3/65 LOG: statement: INSERT
INTO licence_properties (lid, sessionid, pkey, value, readonly) VALUES
('114', '0', E'mytestprop', E'update2', 0);
2009-07-29 23:01:01.218 JST 6108 453328 3/65 LOG: statement: RELEASE
_EXEC_SVP_03742768
2009-07-29 23:01:01.218 JST 6108 453328 3/65 LOG: statement:
SAVEPOINT _EXEC_SVP_03740868
2009-07-29 23:01:01.218 JST 6108 453328 3/65 LOG: statement: UPDATE
licences SET revision = revision + 1 WHERE groupid = E'' AND userid =
E'test';
2009-07-29 23:01:01.218 JST 6108 453328 3/65 LOG: statement: RELEASE
_EXEC_SVP_03740868
2009-07-29 23:01:01.218 JST 6108 453328 3/65 LOG: statement: COMMIT
Process 2:
2009-07-29 23:01:01.218 JST 5460 0 2/47 LOG: statement:
BEGIN;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2009-07-29 23:01:01.218 JST 5460 0 2/47 LOG: statement:
SAVEPOINT _EXEC_SVP_03741378
2009-07-29 23:01:01.218 JST 5460 0 2/47 LOG: statement: SELECT
lid FROM licences WHERE groupid = E'' AND userid = E'test';
2009-07-29 23:01:01.218 JST 5460 0 2/47 LOG: statement: RELEASE
_EXEC_SVP_03741378
2009-07-29 23:01:01.218 JST 5460 0 2/47 LOG: statement:
SAVEPOINT _EXEC_SVP_03740350
2009-07-29 23:01:01.218 JST 5460 0 2/47 LOG: statement: DELETE
FROM licence_properties WHERE lid = '114' AND pkey = E'mytestprop' AND
sessionid IN (0, '0') AND readonly IN (0, 0);
2009-07-29 23:01:01.218 JST 5460 0 2/47 LOG: statement: RELEASE
_EXEC_SVP_03740350
2009-07-29 23:01:01.218 JST 5460 0 2/47 LOG: statement:
SAVEPOINT _EXEC_SVP_03742768
2009-07-29 23:01:01.218 JST 5460 0 2/47 LOG: statement: INSERT
INTO licence_properties (lid, sessionid, pkey, value, readonly) VALUES
('114', '0', E'mytestprop', E'update2', 0);
2009-07-29 23:01:01.218 JST 5460 453330 2/47 ERROR: duplicate key
value violates unique constraint "pk_lic_prop"
2009-07-29 23:01:01.218 JST 5460 453330 2/47 STATEMENT: INSERT INTO
licence_properties (lid, sessionid, pkey, value, readonly) VALUES
('114', '0', E'mytestprop', E'update2', 0);
2009-07-29 23:01:01.218 JST 5460 453330 2/47 LOG: statement: ROLLBACK
to _EXEC_SVP_03742768
2009-07-29 23:01:01.218 JST 5460 453330 2/47 LOG: statement: RELEASE
_EXEC_SVP_03742768
2009-07-29 23:01:01.218 JST 5460 453330 2/47 LOG: statement: ROLLBACK
--
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] combining db's- importing primary keys
Hello, Le 29/07/09 15:02, Jasen Betts a écrit : > On 2009-07-27, Bob Gobeille wrote: >> I would like to combine multiple databases (same schema) into one >> master db. Does anyone know how I can reconcile all my primary and >> foreign keys to maintain referential integrity. > > prefix them all with something that's unique to their source database. > > easy to say, hard to do. > A process (and solution) may be different whether: -the master db will replace all other databases (ie. data manipulation would directly perform on data managed by master db); -or the master db will reflect a combined view of data hosted by all other databases (ie. data manipulation would still perform on distributed databases and master db would play a role of federated view); -or both (data manipulation have to be manageable at the both side: on master db and on distributed db). Regards. -- nha / Lyon / France. -- 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] combining db's- importing primary keys
On Jul 29, 2009, at 7:02 AM, Jasen Betts wrote: On 2009-07-27, Bob Gobeille wrote: I would like to combine multiple databases (same schema) into one master db. Does anyone know how I can reconcile all my primary and foreign keys to maintain referential integrity. prefix them all with something that's unique to their source database. easy to say, hard to do. I can't believe this didn't occur to me. All my primary keys are bigint's, but I could add a second field, a unique server number or name, and change my primary (and foreign) keys to the two column combination. This would make it easy for new db's but I need to give some thought to see if there will be any hiccups migrating all the db's. Many thanks Jasen. Bob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problems compiling contribs in Open Solaris
HI all, I trying to compile several contribs in Osol. I had in result some problems to take them work. /opt/SUNWspro/bin/cc -Xa -xO3 -xarch=native -xspace -W0,-Lt -W2,-Rcond_elim -Xa -xildoff -xc99=none -xCC -KPIC -I. -I../../src/include -c -o xpath.o xpath.c Putting child 0x080a3290 (xpath.o) PID 1718 on the chain. Live child 0x080a3290 (xpath.o) PID 1718 "xpath.c", line 18: cannot find include file: "xpath.c", line 19: cannot find include file: "xpath.c", line 20: cannot find include file: "xpath.c", line 21: cannot find include file: "xpath.c", line 22: cannot find include file: "xpath.c", line 38: syntax error before or at: * "xpath.c", line 38: syntax error before or at: nodeset "xpath.c", line 38: warning: undefined or missing type for: nodeset "xpath.c", line 39: parameter redeclared: xmlChar "xpath.c", line 40: parameter redeclared: xmlChar "xpath.c", line 40: warning: old-style declaration or incorrect type for: pgxmlNodeSetToText "xpath.c", line 42: syntax error before or at: res "xpath.c", line 42: warning: undefined or missing type for: res "xpath.c", line 43: parameter redeclared: xmlChar "xpath.c", line 43: parameter redeclared: xmlChar "xpath.c", line 45: warning: old-style declaration or incorrect type for: xmlChar "xpath.c", line 45: warning: identifier redeclared; ANSI C requires "static": xmlChar "xpath.c", line 45: syntax error before or at: * "xpath.c", line 45: warning: old-style declaration or incorrect type for: pgxml_texttoxmlchar "xpath.c", line 47: syntax error before or at: pgxml_xpath "xpath.c", line 47: syntax error before or at: xmlChar "xpath.c", line 47: warning: undefined or missing type for: xmlChar "xpath.c", line 47: warning: old-style declaration or incorrect type for: pgxml_xpath "xpath.c", line 157: undefined symbol: xmlLoadExtDtdDefaultValue "xpath.c", line 175: undefined symbol: xmlDocPtr "xpath.c", line 175: syntax error before or at: doctree "xpath.c", line 176: warning: declaration can not follow a statement "xpath.c", line 177: warning: declaration can not follow a statement "xpath.c", line 181: undefined symbol: doctree "xpath.c", line 190: cannot recover from previous errors cc: acomp failed for xpath.c Reaping losing child 0x080a3290 PID 1718 gmake: *** [xpath.o] Error 2 Removing child 0x080a3290 PID 1718 from chain. The other problem was compiling uuid-opps and tablefunc contribs. Someone had the same problems. I'm using gmake and sunstudio 12 for compiling. Thkx! -- Emanuel Calvo Franco www.emanuelcalvofranco.com.ar -- 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] Clients disconnect but query still runs
Greg Stark writes: > On Wed, Jul 29, 2009 at 1:58 PM, Jasen Betts wrote: >> can't coerce a signal from the network stack? the linux socket(2) >> manpage is full of promise (SIGPIPE, SIGURG, SIGIO) > SIGIO on the other hand looks like exactly what we would need. I'm not > sure if it can be set to fire a signal only when the connection is > disconnected and not for other state changes but if so it would be > interesting. And the other question is how much of what you read in the Linux manpage is portable to any other system... 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] Clients disconnect but query still runs
On Mon, Jul 27, 2009 at 09:49:04PM -0400, Tom Lane wrote: > It does not spend cycles looking aside to see if the > connection has dropped when it is doing something that doesn't involve > output to the client. Is this ever an interesting case? It would seem possible for something to test the client connections every once in a while to see if they're still valid. The postmaster seems like a reasonable place to do this to me, it has all the descriptors it just discards them at the moment. -- Sam http://samason.me.uk/ -- 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] Clients disconnect but query still runs
On Wed, Jul 29, 2009 at 1:58 PM, Jasen Betts wrote: > can't coerce a signal from the network stack? the linux socket(2) > manpage is full of promise (SIGPIPE, SIGURG, SIGIO) [please don't quote the entire message back, just the part you're responding to] Well SIGPIPE is no help since it would only fire if we tried to write to the socket anyways. SIGIO on the other hand looks like exactly what we would need. I'm not sure if it can be set to fire a signal only when the connection is disconnected and not for other state changes but if so it would be interesting. SIGURG might be useful but it would be more complex to use and less widely useful since it would only work if the client disconnects gracefully (though it might be worth checking into as an alternative to our existing query cancel method). -- greg http://mit.edu/~gsstark/resume.pdf -- 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] comparing NEW and OLD (any good this way?)
On Wed, Jul 29, 2009 at 01:15:27PM +, Jasen Betts wrote: > On 2009-07-23, Sam Mason wrote: > > > > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Attention_on_IS_NULL_and_IS_NOT_NULL_operators_for_composite_types > > > > is scary; even worse is that it was changed to be like this in 8.2 > > because the standard says it should behave this way. What on earth were > > they thinking when they defined the standard this way? > > since any comparson involving those tuples will return NULL true is the > correct value for IS NULL I think you missed the point: SELECT r IS NULL, r IS NOT NULL FROM (VALUES (1,NULL)) r(a,b); returns FALSE for *both* columns. How can a row be both NULL *and* non-NULL? > if you are bothered by this behavior you are misusing NULL. I understand that this is the specified behavior, and hence PG is correctly following the spec--but it still bothers me. -- Sam http://samason.me.uk/ -- 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] org.postgresql.util.PSQLException: PANIC: could not write to log file
On Wed, Jul 29, 2009 at 09:05:37AM -0400, mzh...@ilww.com wrote: > Caused by: org.postgresql.util.PSQLException: PANIC: could not write to > log file 6, segment 176 at offset 14991360, length 8192: Read-only file > system You need the sysadmin to examine the OS status. It's possible the system did not boot cleanly. I've seen this state in the case of hardware issues. -- 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] Content-Type in form variables
On 2009-07-25, Dennis Gearon wrote: > If I receive a form via POST or PUT with with mulitple variables, files, > application/json, others, is there anywhere in the environment to test he > mime type of each variable? > POST /en/html/dummy.php HTTP/1.1 looks like a PHP question to me http://nz2.php.net/manual/en/reserved.variables.files.php -- 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] comparing NEW and OLD (any good this way?)
On 2009-07-23, Sam Mason wrote: > On Thu, Jul 23, 2009 at 01:45:36PM +0200, Pavel Stehule wrote: >> http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks > > Just had a quick flick through your list and one of the early ones stuck > out: > > > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Attention_on_IS_NULL_and_IS_NOT_NULL_operators_for_composite_types > > is scary; even worse is that it was changed to be like this in 8.2 > because the standard says it should behave this way. What on earth were > they thinking when they defined the standard this way? since any comparson involving those tuples will return NULL true is the correct value for IS NULL if you are bothered by this behavior you are misusing NULL. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] org.postgresql.util.PSQLException: PANIC: could not write to log file
In our customer site, the following error occurred. I tried to find some indication what this means. Is this log file used for the roll back purpose? In what scenario that one gets such fatal error? Please help!!! Mzhang Caused by: org.jboss.resource.JBossResourceException: SQLException; - nested throwable: (org.postgresql.util.PSQLException: PANIC: could not write to log file 6, segment 176 at offset 14991360, length 8192: Read-only file system) at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkException(BaseWrapperManagedConnection.java:636) at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.commit(LocalManagedConnection.java:73) at org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource.commit(TxConnectionManager.java:905) ... 23 more Caused by: org.postgresql.util.PSQLException: PANIC: could not write to log file 6, segment 176 at offset 14991360, length 8192: Read-only file system at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188) at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:617) at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:637) at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.commit(LocalManagedConnection.java:69) ... 24 more
Re: [GENERAL] combining db's- importing primary keys
On 2009-07-27, Bob Gobeille wrote: > I would like to combine multiple databases (same schema) into one > master db. Does anyone know how I can reconcile all my primary and > foreign keys to maintain referential integrity. prefix them all with something that's unique to their source database. easy to say, hard to do. -- 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] Clients disconnect but query still runs
On 2009-07-28, Tom Lane wrote: > Robert James writes: >> Hi. I noticed that when clients (both psql and pgAdmin) disconnect or >> cancel, queries are often still running on the server. A few questions: >> 1) Is there a way to reconnect and get the results? > > No. > >> 2) Is there a way to tell postgres to automatically stop all queries when >> the client who queried them disconnects? > > No. > >> 3) Is there a way to see all queries whose clients have disconnected? > > No. > >> 4) And finally: Why is this the behavior? > > It's not easy to tell whether a client has disconnected (particularly if > the network stack is unhelpful, which is depressingly often true). > Postgres will cancel a query if it gets told that the connection's been > dropped, but it will only discover this when an attempt to output to the > client fails. It does not spend cycles looking aside to see if the > connection has dropped when it is doing something that doesn't involve > output to the client. > > If your client code is polite enough to send a cancel request before > disconnecting, that should terminate the query reasonably promptly. > But just "yanking the plug" doesn't do that. can't coerce a signal from the network stack? the linux socket(2) manpage is full of promise (SIGPIPE, SIGURG, SIGIO) -- 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] Building from source vs RPMs
On Wed, Jul 29, 2009 at 3:27 AM, Peter Eisentraut wrote: > On Tuesday 28 July 2009 02:53:19 Christophe Pettus wrote: > > I'm moving from a long time in BSD-land to using Linux. I've always > > been in the habit of building PostgreSQL from the source tarballs. On > > Linux, is there an advantage either way to using the RPMs as opposed > > to building from source? Thanks! > Just to offer another opinion, source compiles are much easier to control, you're not subject to the author's feelings on certain options. Also, RPMs usually over-write a version of PG when you upgrade, so you can't run multiple versions side-by-side, and upgrading becomes tougher (unless you have a second box that is). Just my 2 cents. --Scott
[GENERAL] org.postgresql.util.PSQLException: PANIC: could not write to log file 6
Hello, In our customer site, the following error occurred. I tried to find some indication what this means. Is this log file used for the roll back purpose? In what scenario that one gets such fatal error? Please help!!! Mzhang Caused by: org.jboss.resource.JBossResourceException: SQLException; - nested throwable: (org.postgresql.util.PSQLException: PANIC: could not write to log file 6, segment 176 at offset 14991360, length 8192: Read-only file system) at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkException(BaseWrapperManagedConnection.java:636) at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.commit(LocalManagedConnection.java:73) at org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource.commit(TxConnectionManager.java:905) ... 23 more Caused by: org.postgresql.util.PSQLException: PANIC: could not write to log file 6, segment 176 at offset 14991360, length 8192: Read-only file system at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188) at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:617) at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:637) at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.commit(LocalManagedConnection.java:69) ... 24 more
Re: [GENERAL] Query optimisation and sorting on external merge
2009/7/29 A. Kretschmer : > In response to Jake Stride : >> Hi, >> >> I'm trying to optimise a query at the moment, I've added some new >> indexes to stop seq scans, but I'm now trying to work out if I can >> stop a join using external sort to speed up the query. I've included > > Increase work_mem to force sort in memory. > That stops the external storage thanks. I still think I need to optimise the query though as there are 3 other similar queries which are unioned together to form a view. Jake > > Andreas -- 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] Query optimisation and sorting on external merge
Hello, Le 29/07/09 13:46, Jake Stride a écrit : > Hi, > > I'm trying to optimise a query at the moment, I've added some new > indexes to stop seq scans, but I'm now trying to work out if I can > stop a join using external sort to speed up the query. I've included > an explain analyze below and would appreciate any pointers to gaps in > my understanding. > > explain analyze SELECT p.usercompanyid, 'people' AS type, p.id, > (p.firstname::text || ' '::text) || p.surname::text AS name, > p.assigned_to, p.owner, p.organisation_id, phr.username, p.private >FROM people p >LEFT JOIN organisation_roles pr ON p.organisation_id = > pr.organisation_id AND pr.read >LEFT JOIN hasrole phr ON pr.roleid = phr.roleid; > [...] A first idea could be to explicitely join tables organisation_roles and hasrole before joining with table people. The two first tables are assumed to be of very small size compared to the (main) table people. Joining both them as a preliminary step would reduce the number of rows to join to the latter and thence make the table people scan faster. A second idea may be to move the clause "pr.read" into a subquery (sub-select) of table organisation_roles because this latter is the only table concerned with this clause. Thus, in spite of (hash- or index-based) scanning the whole table organisation_roles, a smaller part would be relevant. Combining these two ideas, a corresponding rewritten query would be as follows: SELECT p.usercompanyid, 'people' AS type, p.id, (p.firstname::text || ' '::text) || p.surname::text AS name, p.assigned_to, p.owner, p.organisation_id, phr.username, p.private FROM people p LEFT JOIN ( (SELECT pr2.roleid, pr2.organisation_id FROM organisation_roles pr2 WHERE pr2.read) pr LEFT JOIN hasrole phr ON pr.roleid = phr.roleid ) t ON p.organisation_id = t.organisation_id; Let you consider if the corresponding query plan looks better. Regards. -- nha / Lyon / France. -- 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] Query optimisation and sorting on external merge
In response to Jake Stride : > Hi, > > I'm trying to optimise a query at the moment, I've added some new > indexes to stop seq scans, but I'm now trying to work out if I can > stop a join using external sort to speed up the query. I've included Increase work_mem to force sort in memory. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query optimisation and sorting on external merge
Hi, I'm trying to optimise a query at the moment, I've added some new indexes to stop seq scans, but I'm now trying to work out if I can stop a join using external sort to speed up the query. I've included an explain analyze below and would appreciate any pointers to gaps in my understanding. explain analyze SELECT p.usercompanyid, 'people' AS type, p.id, (p.firstname::text || ' '::text) || p.surname::text AS name, p.assigned_to, p.owner, p.organisation_id, phr.username, p.private FROM people p LEFT JOIN organisation_roles pr ON p.organisation_id = pr.organisation_id AND pr.read LEFT JOIN hasrole phr ON pr.roleid = phr.roleid; QUERY PLAN --- Merge Right Join (cost=25870.55..31017.51 rows=229367 width=92) (actual time=2884.501..5147.047 rows=354834 loops=1) Merge Cond: (phr.roleid = pr.roleid) -> Index Scan using hasrole_roleid_username on hasrole phr (cost=0.00..537.29 rows=9246 width=27) (actual time=0.049..41.782 rows=9246 loops=1) -> Materialize (cost=25870.49..27204.80 rows=106745 width=81) (actual time=2884.413..3804.537 rows=354834 loops=1) -> Sort (cost=25870.49..26137.35 rows=106745 width=81) (actual time=2884.406..3099.732 rows=111878 loops=1) Sort Key: pr.roleid Sort Method: external merge Disk: 8928kB -> Merge Left Join (cost=0.00..12027.25 rows=106745 width=81) (actual time=37.300..2519.719 rows=111878 loops=1) Merge Cond: (p.organisation_id = pr.organisation_id) -> Index Scan using person_company_id on people p (cost=0.00..5286.23 rows=106745 width=73) (actual time=37.216..1656.515 rows=106745 loops=1) -> Index Scan using companyroles_org_search on organisation_roles pr (cost=0.00..5410.60 rows=120342 width=16) (actual time=0.073..268.645 rows=138299 loops=1) Index Cond: (pr.read = true) Filter: pr.read Total runtime: 5588.105 ms (14 rows) Thanks Jake -- 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] Reporting problems wiki article - help requested
In response to Craig Ringer : > Hi > > I've started on a wiki article on reporting problems / bugs with > PostgreSQL. It focuses mainly on collecting enough information for it to > be possible to answer questions reasonably. > > I'd really like edits / comments / suggestions. the error-message as possible in english Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Reporting problems wiki article - help requested
Hi I've started on a wiki article on reporting problems / bugs with PostgreSQL. It focuses mainly on collecting enough information for it to be possible to answer questions reasonably. I'd really like edits / comments / suggestions. http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -- 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] Idle processes chewing up CPU?
On Wed, Jul 29, 2009 at 12:08, Craig Ringer wrote: > Craig Ringer wrote: >> >> Brendan Hill wrote: >>> >>> Hi Tom, >>> >>> Given it's on Windows, any suggestion for how I would get hold of this? >>> (Process Monitor tool perhaps?) >> >> I think you can get stack traces from Process Monitor using "Tools -> >> Stack Summary". I find it a bit hard to interpret this data, though, and I'm >> not sure how useful it is for this sort of thing. >> >> >> >> [ The following instructions may be put on the PostgreSQL wiki as advice >> for getting debugging details for runaway PostgreSQL processes on Windows if >> desired ]: > > Actually, I've expanded on the instructions and done it. See: > > http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows > > Accessible from "General Articles and Guides" -> "Troubleshooting" -> > "Generating_a_stack_trace_of_a_PostgreSQL_backend". This is very useful, thanks for putting it up! -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Idle processes chewing up CPU?
Craig Ringer wrote: Brendan Hill wrote: Hi Tom, Given it's on Windows, any suggestion for how I would get hold of this? (Process Monitor tool perhaps?) I think you can get stack traces from Process Monitor using "Tools -> Stack Summary". I find it a bit hard to interpret this data, though, and I'm not sure how useful it is for this sort of thing. [ The following instructions may be put on the PostgreSQL wiki as advice for getting debugging details for runaway PostgreSQL processes on Windows if desired ]: Actually, I've expanded on the instructions and done it. See: http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows Accessible from "General Articles and Guides" -> "Troubleshooting" -> "Generating_a_stack_trace_of_a_PostgreSQL_backend". It'd be rather helpful if others could fill in the equivalent for gdb on Linux/bsd/other unix as linked to here: http://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend -- 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] Idle processes chewing up CPU?
Brendan Hill wrote: Hi Tom, Given it's on Windows, any suggestion for how I would get hold of this? (Process Monitor tool perhaps?) I think you can get stack traces from Process Monitor using "Tools -> Stack Summary". I find it a bit hard to interpret this data, though, and I'm not sure how useful it is for this sort of thing. [ The following instructions may be put on the PostgreSQL wiki as advice for getting debugging details for runaway PostgreSQL processes on Windows if desired ]: You're better off using Process Explorer in conjunction with the Debugging Tools for Windows. Install the Debugging Tools for Windows: http://www.microsoft.com/whdc/devtools/debugging/installx86.mspx and Process Explorer: http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx Now launch Process Explorer (procexp.exe) and in the Options menu choose "Configure symbols". Replace the dbghelp.dll path with: C:\Program Files\Debugging Tools for Windows (x86)\dbghelp.dll In the same dialog put this string in the symbol path field (all one line, the only space being between "Program" and "Files"): C:\Program Files\PostgreSQL\8.4\symbols;SRV*c:\localsymbols*http://msdl.microsoft.com/download/symbols; (Change the path to your PostgreSQL symbols directory as appropriate for your version). This will tell Process Explorer where to look for PostgreSQL debug symbols, and tell it to use the Microsoft symbol server to get windows symbols, caching them in c:\localsymbols, which it will create if required. Now you can choose "Properties" on a process in Process Explorer and in the Thread tab see the active threads and what they're doing. You should see something like: TID CSwitch delta Start Address 1260 8 postgres.exe!mainCRTStartup 2792 postgres.exe!pg_signal_thread (If you see lots of references to "pg_init" or similar instead, your symbol path is wrong and Process Explorer can't find the PostgreSQL symbols.) Now you need to identify the active thread and get a stack trace from it. It'll usually have a non-zero cswitch delta. Select it and click "Stack". After a short delay, a stack trace will be shown. Select it all, and click "Copy". Here's a stack trace obtained from PostgreSQL 8.4 while it's executing: select generate_series(1,100); ... just so you have some idea what to expect: ntkrnlpa.exe!NtInitialUserProcessBuffer+0x26 ntkrnlpa.exe!RtlFreeHeapSlowly+0x88 ntkrnlpa.exe!NtCallbackReturn+0x29 ntkrnlpa.exe!MiGrowWsleHash+0xb0 ntkrnlpa.exe!MiTrimWorkingSet+0xc4 hal.dll!HalpApcInterrupt+0xc6 postgres.exe!ExecProcNode+0x5 postgres.exe!ExecutePlan+0x93 postgres.exe!standard_ExecutorRun+0x7a postgres.exe!PortalRunSelect+0x6a postgres.exe!PortalRun+0x14f postgres.exe!exec_simple_query+0x381 postgres.exe!PostgresMain+0xc67 postgres.exe!BackendRun+0x204 postgres.exe!SubPostmasterMain+0x224 postgres.exe!main+0x177 postgres.exe!__tmainCRTStartup+0x10f kernel32.dll!BaseProcessStart+0x23 You can also, if you prefer, use windbg.exe from the Debugging Tools for Windows to get a stack trace. This is widely documented. -- 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] Clients disconnect but query still runs
Robert James wrote: I see - thanks, Tom, for the informative explanation. In my experience admining high volume servers, I found this to a major failure pattern: Client tries query which seems to go on forever (either do to contention or resource exhaustion or some other problem), client gives up / fails / gets shut down or rebooted The client should always make its best effort to notify the server if it's disconnecting. How it's done depends on client OS, client program language, etc, but it generally ends up meaning AT LEAST that the client sends a TCP RST to the server to close the client <-> server socket. I don't know off the top of my head if the server backend will immediately notice an RST on the socket and terminate. If it doesn't, then that's certainly something that'd be desirable. If the client doesn't send an RST and just "vanishes" then of course the server has no way to know anything's changed. As you say, you'd need to have tcp keepalives in use to find out. , yet the database is left hanging working on the slw query, which is probably consuming all of its resources. Perhaps the client restarts and tries again, now making the problem much worse, and the vicious cycle continues until the server is rebooted. The server should never need to be rebooted. What about pg_cancel_backend() ? What about killing the backends with SIGTERM (not SIGKILL, -9) or similar? -- 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] Video available for PGDay SJC '09
David Fetter schrieb: Just a quick question. Wouldn't it be better to provide the videos in flv format in a player in this page. Since I have a MAC, I have no problems viewing the videos. But with my Linux box and FF 3.5 I can't. You can use xine on your Linux box :) Cheers, David. David, thanks for the tip ;-) CHeers Andy -- 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] Clients disconnect but query still runs
Robert James wrote: > Is there no way to have the OS interrupt the postgres process > when a TCP/IP disconnect happens? Or is the OS also in the > dark that the TCP/IP connection was dropped? I believe that > there is a way to monitor this using TCP/IP keep alives. > Or perhaps Postgres could check once every minute? Either > way, in my experience, solving this would be a major boon to > high volume servers, at least in the usage patterns I've worked with. The server machine has no way of knowing that the client died unless the client closes the connection gracefully. There are server configuration parameters "tcp_keepalives_idle", "tcp_keepalives_interval" and "tcp_keepalives_count" which, when used, will make the operating system check idle connections regularly. They are not supported on all operating systems (only on these whose socket options include TCP_KEEPIDLE, TCP_KEEPINTVL and TCP_KEEPCNT). Maybe they can help you. 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] Building from source vs RPMs
On Tuesday 28 July 2009 02:53:19 Christophe Pettus wrote: > I'm moving from a long time in BSD-land to using Linux. I've always > been in the habit of building PostgreSQL from the source tarballs. On > Linux, is there an advantage either way to using the RPMs as opposed > to building from source? Thanks! RPMs will make sure the init script is correctly wired in and the server will start at the right time, logging and log rotation is correctly set up, and depending on the system, that your time zone data is current. Also, the dependency system will make sure everything you need is installed and stays installed. And finally, it saves you from having to install a complete development and build environment on your server, and it installs much faster. -- 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] Relational Algebra and Aggregate Functions
On Tuesday 28 July 2009 04:38:03 Jeff Davis wrote: > On Mon, 2009-07-27 at 21:05 -0400, Robert James wrote: > > 1) Introduction to Database Systems > > http://www.amazon.com/Introduction-Database-Systems-Kannan-Swamynathan/dp > >/B001BVYKY4/ref=sr_1_5?ie=UTF8&s=books&qid=1248742811&sr=1-5 > > > > and > > 2) Database in Depth: Relational Theory for Practitioners > > http://www.amazon.com/Database-Depth-Relational-Theory-Practitioners/dp/0 > >596100124/ref=sr_1_7?ie=UTF8&s=books&qid=1248742811&sr=1-7 > > I recommend #2. It's shorter and easier to read than "An Introduction to > Database Systems", and I think it will answer your question about > relational theory and aggregates (see "SUMMARIZE"). Is it weird that "Database in Depth" is shorter and easier than "Introduction to Database Systems"? And they're by the same author, too. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
