Re: [HACKERS] What is happening on buildfarm member baiji?
Andrew Dunstan wrote: Not to my knowledge, but I have no method of testing what's going on, and I hate guessing like this - in fact this is what has worried me all along about supporting MSVC builds - we always said we didn't want to have to have 2 build environments, but now we have two and we'll be supporting them forever, even though one of them is not used by 95% of our developers. I realise that MSVC builds are likely to perform better, but we have now got a situation where we are likely to have breakage on a regular basis, ISTM. It's not just that they perform better - we also get a debugger that actually works well (yes, I know newer gdb's apparently do work on Mingw; but even a fully functional GDB doesn't come close to VC++), but more importantly it's looking more and more like it'll be our only way of producing a 64bit build for Windows. (sorry to grumble - it's been a very frustrating 24 hours) :-( Regards, Dave. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Concurrently updating an updatable view
Hiroshi Inoue wrote: Concurrently updating an updatable view seems to cause an unexpected result. Is it a known issue? Looks right to me. What did you expect? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Concurrently updating an updatable view
Heikki Linnakangas wrote: Hiroshi Inoue wrote: Concurrently updating an updatable view seems to cause an unexpected result. Is it a known issue? Looks right to me. What did you expect? Shouldn't the last response (session-2) UPDATE 1 be (seesion-2) UPDATE 0 ? regards, Hiroshi Inoue ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Concurrently updating an updatable view
Hiroshi Inoue wrote: Heikki Linnakangas wrote: Hiroshi Inoue wrote: Concurrently updating an updatable view seems to cause an unexpected result. Is it a known issue? Looks right to me. What did you expect? Shouldn't the last response (session-2) UPDATE 1 be (seesion-2) UPDATE 0 ? Ah, I re-read the example and I see what you mean now. The problem is that the new tuple version is checked only against the condition in the update rule, id=OLD.id, but not the condition in the original update-claus, dt='a'. Yeah, that's confusing :(. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] What is happening on buildfarm member baiji?
Tom Lane wrote: So I now state fairly confidently that baiji is failing to overwrite *any* of the installation tree, /share and /bin both, and instead is testing an installation dating from sometime between May 1 and May 11. Close. There was an Msys build from the 9th running on port 5432. So, it seems there are a couple of issues here: 1) There appears to be no way to specify the default port number in the MSVC build. The buildfarm passes it to configure for regular builds, which obviously isn't run in VC++ mode, thus leaving the build on 5432. 2) VC++ and Msys builds will both happily start on the same port at the same time. The first one to start listens on 5432 until it shuts down, at which point the second server takes over seamlessly! It doesn't matter which is started first - it's as if Windows is queuing up the listens on the port. Confusingly, the similar behaviour is reproducible on XP Pro, except the connection seems to go to the last server started, instead of the first! Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Concurrently updating an updatable view
Heikki Linnakangas wrote: The problem is that the new tuple version is checked only against the condition in the update rule, id=OLD.id, but not the condition in the original update-claus, dt='a'. Yeah, that's confusing :(. Bit more than just normal rule confusion I'd say. Try the following two statements in parallel (assuming you've just run the previous): UPDATE test SET dt='c'; UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b'; This isn't a problem with the view mechanism - it's a problem with re-checking clauses involving subqueries or joins I'd guess. I'm trying to decide if it's unexpected or just plain wrong, and I think I'd have to argue wrong. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] What is happening on buildfarm member baiji?
Close. There was an Msys build from the 9th running on port 5432. 2) VC++ and Msys builds will both happily start on the same port at the same time. The first one to start listens on 5432 until it shuts down, at which point the second server takes over seamlessly! It doesn't matter which is started first - it's as if Windows is queuing up the listens on the port. Um, we explicitly set SO_REUSEADDR. So the port will happily allow a second bind. http://support.microsoft.com/kb/307175 quote: If you use SO_REUSADDR to bind multiple servers to the same port at the same time, only one random listening socket accepts a connection request. Andreas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Performance monitoring
Heikki Linnakangas napsal(a): Jim C. Nasby wrote: There is two counters for checkpoints in pgstats, the number of timed (triggered by checkpoint_timeout) and requested (triggered by checkpoint_segments) checkpoints. Maybe we should improve the stats system so that we can collect events with timestamps and durations, but in my experience log files actually are the most reliable and universal way to collect real-time performance information. Any serious tool has a generic log parser. The other alternative is SNMP. I welcome the efforts on pgsnmpd.. Whats about add some DTrace probes? Zdenek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Seq scans roadmap
Simon Riggs wrote: On Fri, 2007-05-11 at 22:59 +0100, Heikki Linnakangas wrote: For comparison, here's the test results with vanilla CVS HEAD: copy-head | 00:06:21.533137 copy-head | 00:05:54.141285 I'm slightly worried that the results for COPY aren't anywhere near as good as the SELECT and VACUUM results. It isn't clear from those numbers that the benefit really is significant. Agreed, the benefit isn't clear. Are you thinking that having COPY avoid cache spoiling is a benefit just of itself? Or do you see a pattern of benefit from your other runs? I think it's worth having just to avoid cache spoiling. I wouldn't bother otherwise, but since we have the infrastructure for vacuum and large seqscans, we might as well use it for COPY as well. (BTW what was wal_buffers set to? At least twice the ring buffer size, hopefully). Good question. [checks]. wal_buffers was set to 128KB. I tried raising it to 1MB, but it didn't make any difference. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] What is happening on buildfarm member baiji?
Zeugswetter Andreas ADI SD wrote: Close. There was an Msys build from the 9th running on port 5432. 2) VC++ and Msys builds will both happily start on the same port at the same time. The first one to start listens on 5432 until it shuts down, at which point the second server takes over seamlessly! It doesn't matter which is started first - it's as if Windows is queuing up the listens on the port. Um, we explicitly set SO_REUSEADDR. So the port will happily allow a second bind. So we do. I must confess I didn't look at the code, just spoke with Magnus who agreed it didn't seem like it should be possible. Regards, Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] What is happening on buildfarm member baiji?
Dave Page wrote: Tom Lane wrote: So I now state fairly confidently that baiji is failing to overwrite *any* of the installation tree, /share and /bin both, and instead is testing an installation dating from sometime between May 1 and May 11. Close. There was an Msys build from the 9th running on port 5432. So, it seems there are a couple of issues here: 1) There appears to be no way to specify the default port number in the MSVC build. The buildfarm passes it to configure for regular builds, which obviously isn't run in VC++ mode, thus leaving the build on 5432. 2) VC++ and Msys builds will both happily start on the same port at the same time. The first one to start listens on 5432 until it shuts down, at which point the second server takes over seamlessly! It doesn't matter which is started first - it's as if Windows is queuing up the listens on the port. Confusingly, the similar behaviour is reproducible on XP Pro, except the connection seems to go to the last server started, instead of the first! I'll look at the port mess. Are you running 2 buildfarm members on the same machine? If so, you should look at using the multi-root factility which is explicitly designed to avoid clashes of this sort. cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] What is happening on buildfarm member baiji?
Dave Page [EMAIL PROTECTED] writes: 2) VC++ and Msys builds will both happily start on the same port at the same time. The first one to start listens on 5432 until it shuts down, at which point the second server takes over seamlessly! Uh ... so the lock-file stuff is completely broken on Windows? The SO_REUSEADDR flag is intentional --- without that, on many platforms there would be a significant time delay needed between stopping a postmaster and starting a new one. But our socket lock file machinery ought to have detected the conflict. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] What is happening on buildfarm member baiji?
Andrew Dunstan wrote: I'll look at the port mess. Are you running 2 buildfarm members on the same machine? If so, you should look at using the multi-root factility which is explicitly designed to avoid clashes of this sort. Yes, I've got VC++ and Mingw/Msys animals on each of two (virtual) machines. Each is completely independent of each other - different configs, different scripts, different ports, different directories etc. Where can I find out about multi-root? I can't see anything in the config file, or in PGBuildFarm-HOWTO.txt Regards, Dave. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] What is happening on buildfarm member baiji?
I wrote: Uh ... so the lock-file stuff is completely broken on Windows? Not so much broken as commented out ... on looking at the code, it's blindingly obvious that we don't even try to create a socket lock file if not HAVE_UNIX_SOCKETS. Sigh. There is a related risk even on Unix machines: two postmasters can be started on the same port number if they have different settings of unix_socket_directory, and then it's indeterminate which one you will contact if you connect to the TCP port. I seem to recall that we discussed this several years ago, and didn't really find a satisfactory way of interlocking the TCP port per se. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] What is happening on buildfarm member baiji?
On Mon, May 14, 2007 at 08:50:54AM -0400, Tom Lane wrote: I wrote: Uh ... so the lock-file stuff is completely broken on Windows? Not so much broken as commented out ... on looking at the code, it's blindingly obvious that we don't even try to create a socket lock file if not HAVE_UNIX_SOCKETS. Sigh. There is a related risk even on Unix machines: two postmasters can be started on the same port number if they have different settings of unix_socket_directory, and then it's indeterminate which one you will contact if you connect to the TCP port. I seem to recall that we discussed this several years ago, and didn't really find a satisfactory way of interlocking the TCP port per se. If all we want to do is add a check that prevents two servers to start on the same port, we could do that trivially in a win32 specific way (since we'll never have unix sockets there). Just create an object in the global namespace named postgresql.interlock.portnumber or such a thing. Worth doing? //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] What is happening on buildfarm member baiji?
Magnus Hagander [EMAIL PROTECTED] writes: If all we want to do is add a check that prevents two servers to start on the same port, we could do that trivially in a win32 specific way (since we'll never have unix sockets there). Just create an object in the global namespace named postgresql.interlock.portnumber or such a thing. Does it go away automatically on postmaster crash? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] What is happening on buildfarm member baiji?
* Tom Lane ([EMAIL PROTECTED]) wrote: There is a related risk even on Unix machines: two postmasters can be started on the same port number if they have different settings of unix_socket_directory, and then it's indeterminate which one you will contact if you connect to the TCP port. I seem to recall that we discussed this several years ago, and didn't really find a satisfactory way of interlocking the TCP port per se. I'm curious as to which Unix systems allow multiple processes to listen on the same port at the same time.. On Linux, and I thought on most, you get an EADDRINUSE on the listen() call (which the postmaster should pick up on and bomb out, which it may already). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] What is happening on buildfarm member baiji?
Stephen Frost wrote: * Tom Lane ([EMAIL PROTECTED]) wrote: There is a related risk even on Unix machines: two postmasters can be started on the same port number if they have different settings of unix_socket_directory, and then it's indeterminate which one you will contact if you connect to the TCP port. I seem to recall that we discussed this several years ago, and didn't really find a satisfactory way of interlocking the TCP port per se. I'm curious as to which Unix systems allow multiple processes to listen on the same port at the same time.. On Linux, and I thought on most, you get an EADDRINUSE on the listen() call (which the postmaster should pick up on and bomb out, which it may already). Linux certainly does. Windows seems to treat SO_REUSEADDR in the same way as SO_REUSEPORT which just seems wrong. Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] What is happening on buildfarm member baiji?
Tom Lane [EMAIL PROTECTED] writes: I wrote: Uh ... so the lock-file stuff is completely broken on Windows? Not so much broken as commented out ... on looking at the code, it's blindingly obvious that we don't even try to create a socket lock file if not HAVE_UNIX_SOCKETS. Sigh. Isn't the socket lock file only there to protect the socket? There is a related risk even on Unix machines: two postmasters can be started on the same port number if they have different settings of unix_socket_directory, and then it's indeterminate which one you will contact if you connect to the TCP port. I seem to recall that we discussed this several years ago, and didn't really find a satisfactory way of interlocking the TCP port per se. [EMAIL PROTECTED]:~/src/local-concurrent-psql/pgsql/src/bin/psql$ /usr/local/pgsql/bin/postgres -D /var/tmp/db2 LOG: could not bind IPv4 socket: Address already in use HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for localhost FATAL: could not create any TCP/IP sockets Is it possible the previous discussion related to servers with IPv6 where they did manage to bind to one but not the other? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] What is happening on buildfarm member baiji?
On Mon, May 14, 2007 at 09:02:10AM -0400, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: If all we want to do is add a check that prevents two servers to start on the same port, we could do that trivially in a win32 specific way (since we'll never have unix sockets there). Just create an object in the global namespace named postgresql.interlock.portnumber or such a thing. Does it go away automatically on postmaster crash? Yes. //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] What is happening on buildfarm member baiji?
Magnus Hagander wrote: On Mon, May 14, 2007 at 09:02:10AM -0400, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: If all we want to do is add a check that prevents two servers to start on the same port, we could do that trivially in a win32 specific way (since we'll never have unix sockets there). Just create an object in the global namespace named postgresql.interlock.portnumber or such a thing. Does it go away automatically on postmaster crash? Yes. Then I think it's worth adding, and I'd argue that as a low risk safety measure we should allow it to sneak into 8.3. I'm assuming the code involved will be quite small. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] What is happening on buildfarm member baiji?
Dave Page [EMAIL PROTECTED] writes: Stephen Frost wrote: I'm curious as to which Unix systems allow multiple processes to listen on the same port at the same time.. On Linux, and I thought on most, you get an EADDRINUSE on the listen() call (which the postmaster should pick up on and bomb out, which it may already). Linux certainly does. Mmm, you're right, I misread the man page: Setting the SO_REUSEADDR option allows the local socket address to be reused in subsequent calls to bind(). This permits multiple SOCK_STREAM sockets to be bound to the same local address, as long as all existing sockets with the desired local address are in a connected state before bind() is called for a new socket. The bit about connected state is relevant here --- a listening socket isn't connected. Time for more caffeine. Windows seems to treat SO_REUSEADDR in the same way as SO_REUSEPORT which just seems wrong. Well, Microsoft getting standards wrong is no surprise. So what do we want to do about it? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] What is happening on buildfarm member baiji?
Tom Lane wrote: Setting the SO_REUSEADDR option allows the local socket address to be reused in subsequent calls to bind(). This permits multiple SOCK_STREAM sockets to be bound to the same local address, as long as all existing sockets with the desired local address are in a connected state before bind() is called for a new socket. The bit about connected state is relevant here --- a listening socket isn't connected. Time for more caffeine. That's what I thought it meant. I am glad to see that I am not quite as out of date as I thought I must be reading upthread :-) cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] What is happening on buildfarm member baiji?
Tom Lane wrote: Windows seems to treat SO_REUSEADDR in the same way as SO_REUSEPORT which just seems wrong. Well, Microsoft getting standards wrong is no surprise. So what do we want to do about it? Microsoft did lift that code from BSD many moons ago, so it might be worth checking if the bug actually originated there. Assuming it didn't, then Magnus' idea sounds good to me. Regards, Dave ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] What is happening on buildfarm member baiji?
Andrew Dunstan wrote: Magnus Hagander wrote: On Mon, May 14, 2007 at 09:02:10AM -0400, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: If all we want to do is add a check that prevents two servers to start on the same port, we could do that trivially in a win32 specific way (since we'll never have unix sockets there). Just create an object in the global namespace named postgresql.interlock.portnumber or such a thing. Does it go away automatically on postmaster crash? Yes. Then I think it's worth adding, and I'd argue that as a low risk safety measure we should allow it to sneak into 8.3. I'm assuming the code involved will be quite small. Do you actually mean 8.2 here? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] What is happening on buildfarm member baiji?
Andrew Dunstan [EMAIL PROTECTED] writes: Magnus Hagander wrote: If all we want to do is add a check that prevents two servers to start on the same port, we could do that trivially in a win32 specific way (since we'll never have unix sockets there). Just create an object in the global namespace named postgresql.interlock.portnumber or such a thing. Then I think it's worth adding, and I'd argue that as a low risk safety measure we should allow it to sneak into 8.3. I'm assuming the code involved will be quite small. What happens if we just #ifndef WIN32 the setsockopt(SO_REUSEADDR) call? I believe the reason that's in there is that some platforms will reject bind() to a previously-used address for a TCP timeout delay after a previous postmaster quit, but if that doesn't happen on Windows then maybe all we need is to not set the option. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] What is happening on buildfarm member baiji?
On Mon, May 14, 2007 at 09:49:47AM -0400, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Magnus Hagander wrote: If all we want to do is add a check that prevents two servers to start on the same port, we could do that trivially in a win32 specific way (since we'll never have unix sockets there). Just create an object in the global namespace named postgresql.interlock.portnumber or such a thing. Then I think it's worth adding, and I'd argue that as a low risk safety measure we should allow it to sneak into 8.3. I'm assuming the code involved will be quite small. What happens if we just #ifndef WIN32 the setsockopt(SO_REUSEADDR) call? I believe the reason that's in there is that some platforms will reject bind() to a previously-used address for a TCP timeout delay after a previous postmaster quit, but if that doesn't happen on Windows then maybe all we need is to not set the option. I think that at least used to happen on Windows in earlier versions. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] What is happening on buildfarm member baiji?
On Mon, May 14, 2007 at 09:34:05AM -0400, Andrew Dunstan wrote:
Magnus Hagander wrote:
On Mon, May 14, 2007 at 09:02:10AM -0400, Tom Lane wrote:
Magnus Hagander [EMAIL PROTECTED] writes:
If all we want to do is add a check that prevents two servers to start on
the same port, we could do that trivially in a win32 specific way (since
we'll never have unix sockets there). Just create an object in the global
namespace named postgresql.interlock.portnumber or such a thing.
Does it go away automatically on postmaster crash?
Yes.
Then I think it's worth adding, and I'd argue that as a low risk safety
measure we should allow it to sneak into 8.3. I'm assuming the code
involved will be quite small.
Yes, see attached.
BTW, did you mean 8.2? One typical case where this could happen is in an
upgrade scenario, I think...
//Magnus
Index: src/backend/libpq/pqcomm.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/libpq/pqcomm.c,v
retrieving revision 1.191
diff -c -r1.191 pqcomm.c
*** src/backend/libpq/pqcomm.c 3 Mar 2007 19:32:54 - 1.191
--- src/backend/libpq/pqcomm.c 14 May 2007 13:52:05 -
***
*** 261,266
--- 261,291
snprintf(portNumberStr, sizeof(portNumberStr), %d,
portNumber);
service = portNumberStr;
}
+ #ifdef WIN32
+ /* Win32 doesn't have Unix sockets, but will allow multiple processes
+* to listen on the same port. This interlock is to prevent that.
+*/
+ {
+ char mutexName[64];
+ HANDLE mutex;
+
+ sprintf(mutexName,postgresql.interlock.%i, portNumber);
+ mutex = CreateMutex(NULL, FALSE, mutexName);
+ if (mutex == NULL)
+ ereport(FATAL,
+ (errmsg_internal(could not create
interlocking mutex: %li,
+ GetLastError(;
+
+ if (GetLastError() == ERROR_ALREADY_EXISTS)
+ ereport(FATAL,
+ (errcode(ERRCODE_LOCK_FILE_EXISTS),
+errmsg(interlock mutex \%s\ already
exists, mutexName),
+errhint(Is another postgres listening
on port %i, portNumber)));
+
+ /* Intentionally leak the handle until process exit, so the
mutex
+* isn't freed. It will be automatically freed when the process
exits. */
+ }
+ #endif
ret = pg_getaddrinfo_all(hostName, service, hint, addrs);
if (ret || !addrs)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] What is happening on buildfarm member baiji?
Tom Lane [EMAIL PROTECTED] writes: What happens if we just #ifndef WIN32 the setsockopt(SO_REUSEADDR) call? I believe the reason that's in there is that some platforms will reject bind() to a previously-used address for a TCP timeout delay after a previous postmaster quit, but if that doesn't happen on Windows then maybe all we need is to not set the option. Well it's worth checking. But whereas Windows breaking our understanding of what SO_REUSEADDR does doesn't actually violate any specification, not having a TIME_WAIT state at all would certainly violate the TCP spec. So it's somewhat unlikely that that's what they're doing. But anything's possible. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] What is happening on buildfarm member baiji?
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: What happens if we just #ifndef WIN32 the setsockopt(SO_REUSEADDR) call? I believe the reason that's in there is that some platforms will reject bind() to a previously-used address for a TCP timeout delay after a previous postmaster quit, but if that doesn't happen on Windows then maybe all we need is to not set the option. Well it's worth checking. But whereas Windows breaking our understanding of what SO_REUSEADDR does doesn't actually violate any specification, not having a TIME_WAIT state at all would certainly violate the TCP spec. So it's somewhat unlikely that that's what they're doing. But anything's possible. This is not a behavior required by the TCP spec AFAICS. Also, in a quick test neither Linux nor HPUX appear to need SO_REUSEADDR --- on both, I can restart the postmaster immediately without it. [ digs in CVS and archives for awhile... ] An interesting historical point is that the SO_REUSEADDR call did not appear in the original Berkeley Postgres95 sources. It was added in rev 1.2 of pqcomm.c, for which the only comment is Finished merging in src/backend from Dr. George's source tree so the fact is that that code has undergone approximately 0 specific peer review. I'm beginning to wonder if we really need it at all. I thought I recalled us having discussed the need for it once, but I cannot find any trace of such a discussion. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] What is happening on buildfarm member baiji?
Aidan Van Dyk [EMAIL PROTECTED] writes: * Tom Lane [EMAIL PROTECTED] [070514 10:24]: This is not a behavior required by the TCP spec AFAICS. Also, in a quick test neither Linux nor HPUX appear to need SO_REUSEADDR --- on both, I can restart the postmaster immediately without it. Did you have an active connection before restarting? In HylaFAX, we had the same situation and went to using SO_REUSEADDR: http://bugs.hylafax.org/show_bug.cgi?id=217 Um, you're right, I hadn't done the test properly. If I have an open psql session across TCP and do pg_ctl stop -m fast, then I can't start a new postmaster until the socket goes out of CLOSE_WAIT state. Which, if I just leave the psql session sit there, seems to mean indefinitely ... so it's even worse than just a TCP timeout. So the notion of not using SO_REUSEADDR seems a nonstarter, and we probably have to go with Magnus' global-object hack. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] What is happening on buildfarm member baiji?
* Tom Lane [EMAIL PROTECTED] [070514 10:24]: This is not a behavior required by the TCP spec AFAICS. Also, in a quick test neither Linux nor HPUX appear to need SO_REUSEADDR --- on both, I can restart the postmaster immediately without it. Did you have an active connection before restarting? In HylaFAX, we had the same situation and went to using SO_REUSEADDR: http://bugs.hylafax.org/show_bug.cgi?id=217 The problem appears if there *was* a connection, and the server was stopped. Then the server can't bind again until the TIME_WAIT connection goes away. Using SO_REUSEADDR allows the new server to listen again right away. a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] What is happening on buildfarm member baiji?
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: What happens if we just #ifndef WIN32 the setsockopt(SO_REUSEADDR) call? I believe the reason that's in there is that some platforms will reject bind() to a previously-used address for a TCP timeout delay after a previous postmaster quit, but if that doesn't happen on Windows then maybe all we need is to not set the option. Well it's worth checking. But whereas Windows breaking our understanding of what SO_REUSEADDR does doesn't actually violate any specification, not having a TIME_WAIT state at all would certainly violate the TCP spec. So it's somewhat unlikely that that's what they're doing. But anything's possible. This is not a behavior required by the TCP spec AFAICS. Also, in a quick test neither Linux nor HPUX appear to need SO_REUSEADDR --- on both, I can restart the postmaster immediately without it. It certainly is, observe on page 55 of RFC 793 for the Open call in the example API: TIME-WAIT STATE Return error: connection already exists. so the fact is that that code has undergone approximately 0 specific peer review. I'm beginning to wonder if we really need it at all. I thought I recalled us having discussed the need for it once, but I cannot find any trace of such a discussion. It's certainly standard in Unix coding to have the server set SO_REUSEADDR and the client not set it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] What is happening on buildfarm member baiji?
Magnus Hagander [EMAIL PROTECTED] writes: + sprintf(mutexName,postgresql.interlock.%i, portNumber); That won't do; it should be legal for two postmasters to listen on different IP addresses using the same port number. So you need to include some representation of the IP address being bound to. + if (GetLastError() == ERROR_ALREADY_EXISTS) + ereport(FATAL, + (errcode(ERRCODE_LOCK_FILE_EXISTS), + errmsg(interlock mutex \%s\ already exists, mutexName), + errhint(Is another postgres listening on port %i, portNumber))); ereport(FATAL) is quite inappropriate here. Do the same thing that bind() failure would do, ie, ereport(LOG) and continue the loop. Also, you probably need to think about cleaning up the mutex in case one of the later steps of socket-acquisition fails. We should only be holding locks on addresses we've successfully bound. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] What is happening on buildfarm member baiji?
Tom Lane [EMAIL PROTECTED] writes: Um, you're right, I hadn't done the test properly. If I have an open psql session across TCP and do pg_ctl stop -m fast, then I can't start a new postmaster until the socket goes out of CLOSE_WAIT state. Which, if I just leave the psql session sit there, seems to mean indefinitely ... so it's even worse than just a TCP timeout. That's still not quite right. Are you running the client and server on the same machine? Shutting down the server should put its connection in FIN_WAIT1 which would immediately go to FIN_WAIT2 if psql is still reachable. I think the connection you're seeing in CLOSE_WAIT is the client's end of the connection. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] What is happening on buildfarm member baiji?
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: Um, you're right, I hadn't done the test properly. If I have an open psql session across TCP and do pg_ctl stop -m fast, then I can't start a new postmaster until the socket goes out of CLOSE_WAIT state. Which, if I just leave the psql session sit there, seems to mean indefinitely ... so it's even worse than just a TCP timeout. That's still not quite right. Are you running the client and server on the same machine? Yeah. The behavior might well be different if they're on different machines ... but it's moot in any case, since the point is that without SO_REUSEADDR we have at least an exposure to a TCP-timeout delay before being able to start a new postmaster. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] What is happening on buildfarm member baiji?
Dave Page wrote: Where can I find out about multi-root? I can't see anything in the config file, or in PGBuildFarm-HOWTO.txt It's a hack I want to get rid of. It's a command-line option: --multiroot = allow several members to use same build root Of course, at least part of our problem is that the MSVC build is not honoring port settings at all (and buildfarm isn't setting the port for MSVC anyway). Magnus and I will work on that - it's a serious deficiency. (refrains from whining again about 2 build systems) cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Use of ActiveSnapshot
On 5/12/2007 4:53 PM, Jan Wieck wrote: Either calling pg_plan_queries() with needSnapshot=false or saving and restoring ActiveSnapshot will prevent the backend from dumping core in the mentioned example, but I am not entirely sure as to which one is the right solution. Attached is a self contained example that crashes the current backend. It took me a moment to figure out exactly how to reproduce it. The problem occurs when the query that needs replanning is actually a FOR record IN SELECT ... that is inside of a nested function call. In that case, the revalidation of the saved plan actually happens inside of SPI_cursor_open(), which does not save and restore the ActiveSnapshot. Shouldn't it? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # create table t1 (a integer, b text, primary key (a)); create function f1 (integer) returns text as ' declare key alias for $1; row record; begin for row in select a, b from t1 loop if row.a = key then return row.b; end if; end loop; return null; end; ' language plpgsql; create function f2 (integer) returns text as ' declare key alias for $1; resultrecord; tmp record; begin select 5 as a, f1 as b into result from f1(key); return result.b; end; ' language plpgsql; insert into t1 values (1, 'one'); insert into t1 values (2, 'two'); select f2(1); alter table t1 add column c text; select f2(2); ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] What is happening on buildfarm member baiji?
On 2007-05-14, Tom Lane [EMAIL PROTECTED] wrote: Aidan Van Dyk [EMAIL PROTECTED] writes: * Tom Lane [EMAIL PROTECTED] [070514 10:24]: This is not a behavior required by the TCP spec AFAICS. Also, in a quick test neither Linux nor HPUX appear to need SO_REUSEADDR --- on both, I can restart the postmaster immediately without it. Did you have an active connection before restarting? In HylaFAX, we had the same situation and went to using SO_REUSEADDR: http://bugs.hylafax.org/show_bug.cgi?id=217 Um, you're right, I hadn't done the test properly. If I have an open psql session across TCP and do pg_ctl stop -m fast, then I can't start a new postmaster until the socket goes out of CLOSE_WAIT state. Which, if I just leave the psql session sit there, seems to mean indefinitely ... so it's even worse than just a TCP timeout. SO_REUSEADDR is required in all cases where you bind a listening socket to a specific port number. There are no exceptions to this rule. This is an artifact of the Berkeley Sockets interface design, not something inherent in the TCP spec. It arises because the sockets interface separates the bind() and listen()/connect() calls; if you replace bind/listen/connect with a single system call, then SO_REUSEADDR becomes unnecessary. (The behaviour of bind() needs to be different depending on whether it will be followed by listen() or connect(); this was not well understood by the original designers of the API, hence the use of SO_REUSEADDR as a klugy way of saying I'm going to use listen() on this socket after the bind.) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Use of ActiveSnapshot
Jan Wieck [EMAIL PROTECTED] writes: The comment for the call of pg_plan_queries in util/cache/plancache.c line 469 for example is fatally wrong. Not only should the snapshot be set by all callers at this point, but if the call actually does replan the queries, the existing ActiveSnapshot is replaced with one allocated on the current memory context. If this happens to be inside of a nested SPI call sequence, the innermost SPI stack frame will free the snapshot data without restoring ActiveSnapshot to the one from the caller. Yeah, I'd been meaning to go back and recheck that point after the code settled down, but forgot :-(. It is possible for RevalidateCachedPlan to be called with no snapshot yet set --- at least the protocol Describe messages can do that. I don't want Describe to force a snapshot because that would be bad for cases like LOCK TABLE at the start of a serializable transaction, so RevalidateCachedPlan had better be able to cope with this case. Since the typical case in which no replan is necessary won't touch the snapshot, I think we'd better adopt the rule that RevalidateCachedPlan never causes any caller-visible change in ActiveSnapshot, else we'll be risking very-hard-to-reproduce bugs. So my proposal is that RevalidateCachedPlan should set a snapshot for itself if it needs to replan and ActiveSnapshot is NULL (else it might as well just use the existing snap); and that it should save and restore ActiveSnapshot when it does this. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Planning large IN lists
Is this a TODO? --- Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: When planning queries with a large IN expression in the WHERE clause, the planner transforms the IN list into a scalar array expression. In clause_selectivity(), we estimate the selectivity of the ScalarArrayExpr by calling scalararraysel(), which in turn estimates the selectivity of *each* array element in order to determine the selectivity of the array expression as a whole. This is quite inefficient when the IN list is large. That's the least of the problems. We really ought to convert such cases into an IN (VALUES(...)) type of query, since often repeated indexscans aren't the best implementation. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_comparator table diff/sync
On May 11, 1:16 pm, Erik 2.0 [EMAIL PROTECTED] wrote: Is pg_comparator the only project out there that does what it does? I tried patching it, and it seems OK, but I'm not terribly confident in my patch. I'm hoping someone will tell me there's a great table- driven rsync out there that everyone uses and I just don't know about. Slony? But perhaps I'm not understanding what pg_comparator does. Andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Concurrently updating an updatable view
Richard Huxton wrote: Heikki Linnakangas wrote: The problem is that the new tuple version is checked only against the condition in the update rule, id=OLD.id, but not the condition in the original update-claus, dt='a'. Yeah, that's confusing :(. Bit more than just normal rule confusion I'd say. Try the following two statements in parallel (assuming you've just run the previous): UPDATE test SET dt='c'; UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b'; This isn't a problem with the view mechanism - it's a problem with re-checking clauses involving subqueries or joins I'd guess. I'm trying to decide if it's unexpected or just plain wrong, and I think I'd have to argue wrong. Or perhaps I'd not argue that :-/ This is really about MVCC in read committed mode, and the just right for simpler cases: http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html#XACT-READ-COMMITTED Clearly there needs to be a change to the sentence: Because of the above rule, it is possible for an updating command to see an inconsistent snapshot: it can see the effects of concurrent updating commands that affected the same rows it is trying to update Not true if there's a subquery/join involved. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Concurrently updating an updatable view
Richard Huxton wrote: Richard Huxton wrote: Heikki Linnakangas wrote: The problem is that the new tuple version is checked only against the condition in the update rule, id=OLD.id, but not the condition in the original update-claus, dt='a'. Yeah, that's confusing :(. Bit more than just normal rule confusion I'd say. Try the following two statements in parallel (assuming you've just run the previous): UPDATE test SET dt='c'; UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b'; This isn't a problem with the view mechanism - it's a problem with re-checking clauses involving subqueries or joins I'd guess. I'm trying to decide if it's unexpected or just plain wrong, and I think I'd have to argue wrong. Or perhaps I'd not argue that :-/ Well, src/backend/executor/README agrees with you that it's wrong.. Note a fundamental bogosity of this approach: if the relation containing the original tuple is being used in a self-join, the other instance(s) of the relation will be treated as still containing the original tuple, whereas logical consistency would demand that the modified tuple appear in them too. But we'd have to actually substitute the modified tuple for the original, while still returning all the rest of the relation, to ensure consistent answers. Implementing this correctly is a task for future work. This is really about MVCC in read committed mode, and the just right for simpler cases: http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html#XACT-READ-COMMITTED Clearly there needs to be a change to the sentence: Because of the above rule, it is possible for an updating command to see an inconsistent snapshot: it can see the effects of concurrent updating commands that affected the same rows it is trying to update Not true if there's a subquery/join involved. If the cited part of the README is correct, then all joins and subqueries are fine, except if they refer to the table being updated. I think there should be a big, fat warning that self-referential updates have highly non-obvious behaviour in read-committed mode, and should be avoided. greetings, Florian Pflug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_comparator table diff/sync
hello I didn't test it, but maybe it is what you are finding http://pgfoundry.org/projects/apgdiff regards Pavel Stehule 14 May 2007 11:11:08 -0700, Andrew Hammond [EMAIL PROTECTED]: On May 11, 1:16 pm, Erik 2.0 [EMAIL PROTECTED] wrote: Is pg_comparator the only project out there that does what it does? I tried patching it, and it seems OK, but I'm not terribly confident in my patch. I'm hoping someone will tell me there's a great table- driven rsync out there that everyone uses and I just don't know about. Slony? But perhaps I'm not understanding what pg_comparator does. Andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Use of ActiveSnapshot
Jan Wieck [EMAIL PROTECTED] writes: The only problem with that is that there are code paths that set ActiveSnapshot to palloc()'d memory that is released due to a MemoryContextDelete() without resetting ActiveSnapshot to NULL. Only at the very end of a transaction (where ActiveSnapshot *is* reset to null, in FreeXactSnapshot); otherwise we'd have bugs unrelated to RevalidateCachedPlan. Eventually I would like to have reference-counted snapshots managed by a centralized module, as was discussed a month or two back; but right at the moment I don't think it's broken and I don't want to spend time on intermediate solutions. I think it would be cleaner if RevalidateCachedPlan()'s API would have a Snapshot argument. How does that improve anything? AFAICS the only thing that would ever get passed is ActiveSnapshot, so this is just more notation to do exactly the same thing. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Use of ActiveSnapshot
On 5/14/2007 1:29 PM, Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: The comment for the call of pg_plan_queries in util/cache/plancache.c line 469 for example is fatally wrong. Not only should the snapshot be set by all callers at this point, but if the call actually does replan the queries, the existing ActiveSnapshot is replaced with one allocated on the current memory context. If this happens to be inside of a nested SPI call sequence, the innermost SPI stack frame will free the snapshot data without restoring ActiveSnapshot to the one from the caller. Yeah, I'd been meaning to go back and recheck that point after the code settled down, but forgot :-(. It is possible for RevalidateCachedPlan to be called with no snapshot yet set --- at least the protocol Describe messages can do that. I don't want Describe to force a snapshot because that would be bad for cases like LOCK TABLE at the start of a serializable transaction, so RevalidateCachedPlan had better be able to cope with this case. Since the typical case in which no replan is necessary won't touch the snapshot, I think we'd better adopt the rule that RevalidateCachedPlan never causes any caller-visible change in ActiveSnapshot, else we'll be risking very-hard-to-reproduce bugs. So my proposal is that RevalidateCachedPlan should set a snapshot for itself if it needs to replan and ActiveSnapshot is NULL (else it might as well just use the existing snap); and that it should save and restore ActiveSnapshot when it does this. The only problem with that is that there are code paths that set ActiveSnapshot to palloc()'d memory that is released due to a MemoryContextDelete() without resetting ActiveSnapshot to NULL. So it might be possible for RevalidateCachedPlan to go ahead with an ActiveSnapshot pointing to garbage. I think it would be cleaner if RevalidateCachedPlan()'s API would have a Snapshot argument. If it needs a snapshot and the argument is NULL, it can create (and free) one itself, otherwise it'd use the one given. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] What is happening on buildfarm member baiji?
Andrew Dunstan wrote: Dave Page wrote: Where can I find out about multi-root? I can't see anything in the config file, or in PGBuildFarm-HOWTO.txt It's a hack I want to get rid of. It's a command-line option: --multiroot = allow several members to use same build root I have in fact just removed this in buildfarm CVS tip. That means that you can now run as many buildfarm members as you like against a single buildroot and they will not trip over each other. We still have the MSVC port problem to fix though. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Use of ActiveSnapshot
On 5/14/2007 3:35 PM, Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: The only problem with that is that there are code paths that set ActiveSnapshot to palloc()'d memory that is released due to a MemoryContextDelete() without resetting ActiveSnapshot to NULL. Only at the very end of a transaction (where ActiveSnapshot *is* reset to null, in FreeXactSnapshot); otherwise we'd have bugs unrelated to RevalidateCachedPlan. Eventually I would like to have reference-counted snapshots managed by a centralized module, as was discussed a month or two back; but right at the moment I don't think it's broken and I don't want to spend time on intermediate solutions. Which means that the 8.3 fix for the reproducible backend crash, I posted earlier, is to have SPI_cursor_open() save and restore ActiveSnapshot while calling RevalidateCachedPlan(). I'll cross check that this fixes this symptom and commit later today. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Use of ActiveSnapshot
Jan Wieck [EMAIL PROTECTED] writes: Which means that the 8.3 fix for the reproducible backend crash, I posted earlier, is to have SPI_cursor_open() save and restore ActiveSnapshot while calling RevalidateCachedPlan(). I'll cross check that this fixes this symptom and commit later today. No, the correct fix is to do that inside RevalidateCachedPlan ... and I already did it. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Use of ActiveSnapshot
On 5/14/2007 4:26 PM, Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: Which means that the 8.3 fix for the reproducible backend crash, I posted earlier, is to have SPI_cursor_open() save and restore ActiveSnapshot while calling RevalidateCachedPlan(). I'll cross check that this fixes this symptom and commit later today. No, the correct fix is to do that inside RevalidateCachedPlan ... and I already did it. Works for me. It fixed the Slony test that actually tripped over the bug. Thanks. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Concurrently updating an updatable view
Richard Huxton wrote: Heikki Linnakangas wrote: The problem is that the new tuple version is checked only against the condition in the update rule, id=OLD.id, but not the condition in the original update-claus, dt='a'. Yeah, that's confusing :(. Bit more than just normal rule confusion I'd say. Try the following two statements in parallel (assuming you've just run the previous): UPDATE test SET dt='c'; UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b'; This isn't a problem with the view mechanism - it's a problem with re-checking clauses involving subqueries or joins I'd guess. I don't understand the PostgreSQL specific *FROM* clause correctly. Currently the relations in the *FROM* clause seem to be read only and UPDATE operations seem to acquire no tuple level lock on them. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Concurrently updating an updatable view
Hiroshi Inoue wrote: Richard Huxton wrote: Heikki Linnakangas wrote: The problem is that the new tuple version is checked only against the condition in the update rule, id=OLD.id, but not the condition in the original update-claus, dt='a'. Yeah, that's confusing :(. Bit more than just normal rule confusion I'd say. Try the following two statements in parallel (assuming you've just run the previous): UPDATE test SET dt='c'; UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b'; This isn't a problem with the view mechanism - it's a problem with re-checking clauses involving subqueries or joins I'd guess. I don't understand the PostgreSQL specific *FROM* clause correctly. Currently the relations in the *FROM* clause seem to be read only and UPDATE operations seem to acquire no tuple level lock on them. Yes, the above query is equivalent to: UPDATE test SET dt='x' WHERE id IN (SELECT id FROM test WHERE dt='b'); There are some expressions more naturally expressed as a set of where conditions though, and I think the FROM is just to provide a place to name them. The FROM form seemed to be the more natural match to the plan your view was generating - I'm not sure which the plan transformation process produces. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Alvaro Herrera wrote:
2. decide that the standard is braindead and just omit dumping the
grantor when it's no longer available, but don't remove
pg_auth_members.grantor
Which do people feel should be implemented? I can do whatever we
decide; if no one has a strong opinion on the matter, my opinion is we
do (2) which is the easiest.
Here is a patch implementing this idea, vaguely based on Russell's.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Index: src/bin/pg_dump/pg_dumpall.c
===
RCS file: /home/alvherre/cvs/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.90
diff -c -p -r1.90 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c 10 Feb 2007 14:58:55 - 1.90
--- src/bin/pg_dump/pg_dumpall.c 14 May 2007 23:13:43 -
*** dumpRoleMembership(PGconn *conn)
*** 702,709
res = executeQuery(conn, SELECT ur.rolname AS roleid,
um.rolname AS member,
! ug.rolname AS grantor,
! a.admin_option
FROM pg_auth_members a
LEFT JOIN pg_authid ur on ur.oid = a.roleid
LEFT JOIN pg_authid um on um.oid = a.member
--- 702,709
res = executeQuery(conn, SELECT ur.rolname AS roleid,
um.rolname AS member,
! a.admin_option,
! ug.rolname AS grantor
FROM pg_auth_members a
LEFT JOIN pg_authid ur on ur.oid = a.roleid
LEFT JOIN pg_authid um on um.oid = a.member
*** dumpRoleMembership(PGconn *conn)
*** 717,730
{
char *roleid = PQgetvalue(res, i, 0);
char *member = PQgetvalue(res, i, 1);
! char *grantor = PQgetvalue(res, i, 2);
! char *option = PQgetvalue(res, i, 3);
fprintf(OPF, GRANT %s, fmtId(roleid));
fprintf(OPF, TO %s, fmtId(member));
if (*option == 't')
fprintf(OPF, WITH ADMIN OPTION);
! fprintf(OPF, GRANTED BY %s;\n, fmtId(grantor));
}
PQclear(res);
--- 717,740
{
char *roleid = PQgetvalue(res, i, 0);
char *member = PQgetvalue(res, i, 1);
! char *option = PQgetvalue(res, i, 2);
fprintf(OPF, GRANT %s, fmtId(roleid));
fprintf(OPF, TO %s, fmtId(member));
if (*option == 't')
fprintf(OPF, WITH ADMIN OPTION);
!
! /*
! * We don't track the grantor very carefully in the backend, so cope
! * with the possibility that it has been dropped.
! */
! if (!PQgetisnull(res, i, 3))
! {
! char *grantor = PQgetvalue(res, i, 3);
!
! fprintf(OPF, GRANTED BY %s, fmtId(grantor));
! }
! fprintf(OPF, ;\n);
}
PQclear(res);
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] What is happening on buildfarm member baiji?
Dave Page wrote: 1) There appears to be no way to specify the default port number in the MSVC build. The buildfarm passes it to configure for regular builds, which obviously isn't run in VC++ mode, thus leaving the build on 5432. I have committed fixes to both pgsql and buildfarm that should in combination cure this, I hope. Please test - there might still be loose ends hanging around. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] MSVC build failure not exiting with proper error ststus
mastodon and skylark just failed at the make stage due to a thinko on my
part (now fixed). However, this is not correctly caught by the buildfarm
script, meaning that the process invoked at this stage ('build 21') is
not exiting properly with a non-zero status on error. That needs to be
fixed.
cheers
andrew
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Behavior of GENERATED columns per SQL2003
URL added to TODO item. Patch rejected for 8.3. --- Tom Lane wrote: I've been studying the SQL spec in a bit more detail and I'm suddenly thinking that we've got the behavior all wrong in the current GENERATED/IDENTITY patch. In particular, it looks to me like we've been implementing GENERATED ALWAYS AS (expr) according to the rules that the spec in fact lays down only for GENERATED ALWAYS AS IDENTITY. You'd think the two constructs would be pretty closely related but the behaviors specified by the spec are light-years apart. If you look closely, a generated column in the meaning of section 4.14.8 is one that has GENERATED ALWAYS AS (expr), and identity columns are *not* in this class. It looks to me like the behavior the spec intends for a generated column is actually that it can be implemented as a virtual column occupying no space on disk and instead computed on-the-fly when retrieved. Identity columns can have their values overridden by the user (it's a little harder if GENERATED ALWAYS, but still possible), and they don't change during an UPDATE unless specifically forced to. In contrast, generated columns cannot be overridden by assignment, and are recomputed from their base columns during updates. This realization also explains the following, otherwise rather strange, facts: * There is no GENERATED BY DEFAULT AS (expr) in the spec. * GENERATED expressions are specifically disallowed from containing subselects, calling functions that access any SQL-data, or being nondeterministic; hence their values depend solely on the regular columns in the same row. * While identity columns are updated (if needed) before execution of BEFORE triggers, generated columns are updated after BEFORE triggers; hence a BEFORE trigger can override the value in one case and not the other. (The current patch gets this wrong, btw.) * Generated columns are forcibly updated when their base columns change as a result of FK constraints (such as ON UPDATE CASCADE). It looks to me like a BEFORE trigger is actually the only place that can (transiently) see values of a generated column that are different from the result of applying the generation expression on the rest of the row. It's unclear whether that's intentional or an oversight. Is anyone familiar with a database that implements SQL-spec generated columns? Do they actually store the columns? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Concurrently updating an updatable view
Florian G. Pflug wrote: Richard Huxton wrote: Richard Huxton wrote: Heikki Linnakangas wrote: snip Bit more than just normal rule confusion I'd say. Try the following two statements in parallel (assuming you've just run the previous): UPDATE test SET dt='c'; UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b'; This isn't a problem with the view mechanism - it's a problem with re-checking clauses involving subqueries or joins I'd guess. I'm trying to decide if it's unexpected or just plain wrong, and I think I'd have to argue wrong. Or perhaps I'd not argue that :-/ Well, src/backend/executor/README agrees with you that it's wrong.. Thanks for the pointer. Note a fundamental bogosity of this approach: if the relation containing the original tuple is being used in a self-join, the other instance(s) of the relation will be treated as still containing the original tuple, whereas logical consistency would demand that the modified tuple appear in them too. Is the above description about UPDATE or DELETE operations? AFAIR SELECT FOR UPDATE operations avoided the incosistency from the first for joins though I'm not sure about subqueries. Or I may be misunderstanding something? But we'd have to actually substitute the modified tuple for the original, while still returning all the rest of the relation, to ensure consistent answers. Implementing this correctly is a task for future work. snip I think there should be a big, fat warning that self-referential updates have highly non-obvious behaviour in read-committed mode, and should be avoided. It seems pretty difficult for PostgreSQL rule system to avoid such kind of updates. I'm suspicious if UPDATABLE VIEWS can be implemented using the rule system. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
