Re: [HACKERS] Formatting Curmudgeons WAS: MMAP Buffers
Josh Berkus j...@agliodbs.com writes: I received a private offlist email from someone who didn't feel comfortable bringing up their issues with this list publicly. Let me quote from it, because I think it pins part of the issue: I believe this is due to the current postgresql commitfest process whereby there is no real way to present new ideas or technologies without coming to the table with a fully-baked plan and patch. This is obvious even in the name commitfest since the expectation is that every patch presented is considered ready-to-commit by the patch presenter. This makes a novice or experimental contribution less likely. As Robert noted, the purpose of the commitfest mechanism is mostly to ensure that patches that *are* committable, or close to it, don't fall through the cracks. I'm not sure we're doing anybody any favors by trying to shoehorn reviews of WIP ideas into that same process. At the very least it seems we'd need a different set of review guidelines for WIP items, and we don't have one. I think useful reviewing of WIP stuff has to focus much more on design concepts and much less on code reading. The reason why the mmap patch was getting such negative feedback was that there was no way to provide such a review except by reverse-engineering the design out of some very messily-presented code. So if we're going to do anything about this, what we have to do is tell people that the first thing to present for a WIP review is a design document. If they feel a need to write some throwaway code to help them clarify their ideas, fine ... but *don't show us that code*. Write a design document. Get that reviewed. Then see about coding it, or bringing your first-draft code up to the point where it can stand the light of day. I don't know if we need a formal process akin to CFs for reviewing design documents. I think people are usually plenty willing to discuss ideas on -hackers, unless maybe you hit them at a particularly bad time like when they're already burnt out towards the end of a CF. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows 64 bit warnings
On Mon, Apr 18, 2011 at 19:00, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Andrew Dunstan's message of sáb abr 16 21:46:44 -0300 2011: The other, slightly more serious case, is at src/test/regress/pg_regress.c:2280, which is this code: printf(_(running on port %d with pid %lu\n), port, (unsigned long) postmaster_pid); Here the postmaster_pid is in fact a HANDLE which is 8 bytes, and so it should probably be cast to an unsigned long long and rendered with the format %llu in Win64. Is this uint64 and UINT64_FORMAT? Considering that this is a purely informational printout, I don't see any reason to give a damn about the possibility of high-order bits in the HANDLE being dropped. And it's not an especially good idea to stick UINT64_FORMAT into a translatable string, because of the platform dependency that creates. IIRC, even while HANDLE is a 64-bit value on Win64, only the lower 32-bits are actually used. Took me a while to find a ref, but this is one I found: http://msdn.microsoft.com/en-us/library/aa384203(v=vs.85).aspx I think all we need here is a way to shut up the overly-anal-retentive warning. I would have expected that explicit cast to be enough, actually, but apparently it's not. Ideas? Not sure about that one. Certainly seems like that case should be enough - it always was enough to silence similar warnings on MSVC in the past... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Warning during PostgreSQL 9.0.4 libpq.dll build on WinXP+MinGW
Hello, Pgsql-hackers. Today I built libpq.dll library on Windows XP using MinGW and got one warning message: Pablo@computer /z/pasha/postgresql-9.0.4/src/interfaces/libpq $ make ... gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fw rapv -DFRONTEND -DUNSAFE_STAT_OK -I. -I../../../src/include -I./src/include/port/win32 -DEXEC_BACKEND -I../../../src/ include/port/win32 -I../../../src/port -I../../../src/port -DSO_MAJOR_VERSION=5 -c -o fe-connect.o fe-connect.c fe-connect.c: In function 'PQconnectPoll': fe-connect.c:1533:13: warning: unused variable 'on' ... Is it OK? -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows 64 bit warnings
On 04/19/2011 04:08 AM, Magnus Hagander wrote: IIRC, even while HANDLE is a 64-bit value on Win64, only the lower 32-bits are actually used. Took me a while to find a ref, but this is one I found: http://msdn.microsoft.com/en-us/library/aa384203(v=vs.85).aspx Good. I think all we need here is a way to shut up the overly-anal-retentive warning. I would have expected that explicit cast to be enough, actually, but apparently it's not. Ideas? Not sure about that one. Certainly seems like that case should be enough - it always was enough to silence similar warnings on MSVC in the past... If we cast the HANDLE to a long long first and then truncate it the compiler is silent, it only complains if that's done in one operation. So maybe something like: #ifdef WIN64 #define ULONGPID(x) (unsigned long) (unsigned long long) (x) #else #define ULONGPID(x) (unsigned long) (x) #endif ... printf(_(running on port %d with pid %lu\n), port, ULONGPID(postmaster_pid)); It's a bit ugly, but we've done worse. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Warning during PostgreSQL 9.0.4 libpq.dll build on WinXP+MinGW
On 19.04.2011 14:09, Pavel Golub wrote: Today I built libpq.dll library on Windows XP using MinGW and got one warning message: Pablo@computer /z/pasha/postgresql-9.0.4/src/interfaces/libpq $ make ... gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fw rapv -DFRONTEND -DUNSAFE_STAT_OK -I. -I../../../src/include -I./src/include/port/win32 -DEXEC_BACKEND -I../../../src/ include/port/win32 -I../../../src/port -I../../../src/port -DSO_MAJOR_VERSION=5 -c -o fe-connect.o fe-connect.c fe-connect.c: In function 'PQconnectPoll': fe-connect.c:1533:13: warning: unused variable 'on' ... Is it OK? It's harmless. I just committed a patch to silence it, thanks for the report. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows 64 bit warnings
On Sat, Apr 16, 2011 at 08:46:44PM -0400, Andrew Dunstan wrote: One is at src/interfaces/ecpg/ecpglib/sqlda.c:231, which is this line: sqlda-sqlvar[i].sqlformat = (char *) (long) PQfformat(res, i); I'm not clear about the purpose of this anyway. It doesn't seem to be used anywhere, and the comment on the field says it for future By used you mean inside the postgres right? If so this is not surprising, sqlda is used to give data to the program using the library. use. If we're going to do it, I think it should be cast to a long long on Win64, since a char * is 8 bytes there, while a long is only 4. But if we really want to store the result from PQfformat() in it, why is it a char * at all? Zoltan, did you see this? Given that you wrote the code it might be good if you could comment on this. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org Jabber: michael.meskes at googlemail dot com VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
On Mon, Apr 18, 2011 at 06:02:53PM -0300, Alvaro Herrera wrote: Hi, Today (and previously) I wished that pgbench had a mechanism to help create simple random databases. For example, I could create a table tenk and fill it with random stuff like \setrandom foo 1 1 insert into foo values (:foo) Now I have to run this 1 times or something like that. But I don't want a transaction for each of those, so I had a desire for something like this: begin; \for iterator 1 1 \setrandom foo 1 :iterator insert into foo values (:foo); \end commit; Would something like this be acceptable? Are existing mechanisms (WITH and DO) insufficient for the purpose? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows 64 bit warnings
Andrew Dunstan and...@dunslane.net writes: If we cast the HANDLE to a long long first and then truncate it the compiler is silent, it only complains if that's done in one operation. So maybe something like: #ifdef WIN64 #define ULONGPID(x) (unsigned long) (unsigned long long) (x) #else #define ULONGPID(x) (unsigned long) (x) #endif ... with a comment, please. Perhaps #ifdef WIN64 /* need a series of two casts to convert HANDLE without compiler warning */ #define ULONGPID(x) (unsigned long) (unsigned long long) (x) #else #define ULONGPID(x) (unsigned long) (x) #endif regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Build farm coverage for isolation tests
I'm not sure what the right thing is to do here. Heikki added a new testing methodology under src/test/isolation which allows intermingling a series of statements on multiple connections in desired permutations. (By default each test defined for it runs all permutations of how the statement sequences can be interleaved, but you can specify one or more particular permutations if needed.) I filled it out with the SSI tests which had been run under dtester during initial development. To run it you change to the src/test/isolation directory and run `make check` or `make installcheck`. It takes too long to run this to think about including it in the main `make check`, but I'd feel better about things if this set of tests was normally run on the buildfarm. On my so-so development machine, the installcheck takes about 20 seconds. Setting up and tearing down a temp installation for the `make check` adds another 10 seconds. That seems unlikely to be a deal-breaker for the buildfarm, even if we were to add a little to the test set. Would it make sense to add this to `make check-world` and `make installcheck-world`? Would something else be a good idea to get buildfarm coverage? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [JDBC] [HACKERS] JDBC connections to 9.1
On Tue, 19 Apr 2011, Tom Lane wrote: Kris Jurka bo...@ejurka.com writes: On Mon, 18 Apr 2011, Mike Fowler wrote: As there seems to be a consensus forming for fixing the JDBC driver, I've taken the liberty do so at the risk of being shot down. The patch is fairly straightforward, just changing UNICODE to UTF8 in a number of files including the translation files. I've tested this against 9.1devel (HEAD) and 8.4.7. For each database version I build and the tests running JDKs 1.4.2_19, 1.5.0_22 and 1.6.0_2. All on 32-bit. Thanks, applied, mostly. It's great to have a patch for a problem before you even know it exists. For purposes of the notes in the server-side fix, could you state which JDBC driver versions these changes will first appear in? This is in 9.1dev-900 and won't be backpatched. http://jdbc.postgresql.org/download.html#development Kris Jurka -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
On Mon, Apr 18, 2011 at 5:37 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Merlin Moncure's message of lun abr 18 18:26:54 -0300 2011: On Mon, Apr 18, 2011 at 4:02 PM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: begin; \for iterator 1 1 \setrandom foo 1 :iterator insert into foo values (:foo); \end commit; Would something like this be acceptable? *) what does this do that isn't already possible with 'DO' (not being snarky, genuinely curious)? Uhm, not sure. I'm not really used to having DO available so I didn't think about it. I'll look at it a bit more. *) should psql get some of these features? simple logic and looping would be a nice addition? I dunno. They have been proposed and shot down in the past. Apparently people don't want psql to become too powerful. (But that would make psql turing complete! Soon you're going to want to have \while on it!). I think pgbench is supposed to be designed to handle data in bulk which is why I started using it for this in the first place. [ woops, just realized that i sent this response off-list the first time ] I do think that DO covers a lot of the same territory that could usefully be addressed by a more powerful backslash-command language in psql. It's in some ways quite a bit more powerful, because it's available from any client, and it knows about data types, which psql doesn't, so things like \while are going to be awkward (what comparison semantics will it use?). The only advantage I can really see to doing that stuff on the client side is that you could do things like \connect and \prompt that wouldn't make sense on the server side. Maybe that's useful enough to make it worth doing: I'm not sure. Now pgbench is a bit of a different case, because presumably in that case it matters somewhat whether the work happens on the client side or the server side, though I think in your particular case not really. Actually in that case it seems like you could do the whole thing in one SQL statement even without DO, using INSERT INTO foo SELECT ... FROM generate_series(1,1) g. If we are going to add more scripting capability, it would be nice to have a bit of consistency between pgbench and psql in terms of these backslash commands, and maybe some kind of sketch of what the overall design looks like. For example, if \for is defined as a loop over integers, whatdya do if you want to loop over query results or arrays? See recent discussions of these issues in relation to plpgsql. I don't really see a reason to oppose adding functionality like this categorically, but I do think it should be carefully thought out and well-designed so we don't box ourselves into a corner; and we should know what use cases we are shooting at. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] REINDEX vs broken HOT chains, redux
Last week we fixed a problem in which REINDEX could corrupt pg_index's own indexes by forbidding it from setting indcheckxmin on a system catalog's index. While thinking about bug #5985 I realized that there's a better, more general solution. Namely, that when reindexing an existing index, there cannot be any need to advance the index's indcheckxmin horizon. The existing code just blindly pushes the horizon forward to current time if it finds any possibly-broken HOT chains --- but if the index existed before, then any HOT chains that are actually broken with respect to it must predate its existing horizon. Therefore, when reindexing an existing index, we should never set indcheckxmin if it wasn't set before. In particular, this rule fixes the previous issue for system catalogs, which are certain to not have had indcheckxmin set when initdb made them. The existing code in index_build also forcibly updates the pg_index row even if indcheckxmin was already set, thus pushing the row'x xmin forward and moving the index's usability horizon. This effect isn't desirable either. In short, the entire update of pg_index in index_build is unwanted when reindexing an existing index. index_build doesn't currently know whether it's being called for a new index or a reindex operation, but it wouldn't be hard to pass down a flag for that. I'm intending to revert last week's patch in favor of this approach, at least in HEAD. It'll be slightly more invasive than the previous patch because of the API change for index_build, so I'm not sure whether to back-patch or not --- comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Build farm coverage for isolation tests
On 04/19/2011 11:16 AM, Kevin Grittner wrote: I'm not sure what the right thing is to do here. Heikki added a new testing methodology under src/test/isolation which allows intermingling a series of statements on multiple connections in desired permutations. (By default each test defined for it runs all permutations of how the statement sequences can be interleaved, but you can specify one or more particular permutations if needed.) I filled it out with the SSI tests which had been run under dtester during initial development. To run it you change to the src/test/isolation directory and run `make check` or `make installcheck`. It takes too long to run this to think about including it in the main `make check`, but I'd feel better about things if this set of tests was normally run on the buildfarm. On my so-so development machine, the installcheck takes about 20 seconds. Setting up and tearing down a temp installation for the `make check` adds another 10 seconds. That seems unlikely to be a deal-breaker for the buildfarm, even if we were to add a little to the test set. Would it make sense to add this to `make check-world` and `make installcheck-world`? Would something else be a good idea to get buildfarm coverage? The buildfarm doesn't run either of these. I think the best thing might be to add a new step which runs the isolation check or installcheck. It would only need a small amount of perl code adde3d to the client to accomplish, and nothing on the server side. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Build farm coverage for isolation tests
Andrew Dunstan and...@dunslane.net wrote: I think the best thing might be to add a new step which runs the isolation check or installcheck. It would only need a small amount of perl code adde3d to the client to accomplish, and nothing on the server side. Since I'm unskilled at perl and have never looked at the buildfarm scripts, could someone with the appropriate skills and knowledge add this for me? In bash, when I'm in the normal base directory for a build from source, my usual incantation is: pushd src/test/isolation make check popd (or use installcheck instead of check if an appropriate cluster is already running) Thanks in advance, -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
On Tue, Apr 19, 2011 at 10:22 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Apr 18, 2011 at 5:37 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Merlin Moncure's message of lun abr 18 18:26:54 -0300 2011: On Mon, Apr 18, 2011 at 4:02 PM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: begin; \for iterator 1 1 \setrandom foo 1 :iterator insert into foo values (:foo); \end commit; Would something like this be acceptable? *) what does this do that isn't already possible with 'DO' (not being snarky, genuinely curious)? Uhm, not sure. I'm not really used to having DO available so I didn't think about it. I'll look at it a bit more. *) should psql get some of these features? simple logic and looping would be a nice addition? I dunno. They have been proposed and shot down in the past. Apparently people don't want psql to become too powerful. (But that would make psql turing complete! Soon you're going to want to have \while on it!). I think pgbench is supposed to be designed to handle data in bulk which is why I started using it for this in the first place. [ woops, just realized that i sent this response off-list the first time ] I do think that DO covers a lot of the same territory that could usefully be addressed by a more powerful backslash-command language in psql. It's in some ways quite a bit more powerful, because it's available from any client, and it knows about data types, which psql doesn't, so things like \while are going to be awkward (what comparison semantics will it use?). The only advantage I can really see to doing that stuff on the client side is that you could do things like \connect and \prompt that wouldn't make sense on the server side. Well, you missed one big advantage: with DO you are 'one transaction' limited -- this makes it unsuitable for certain classes of maintenance tasks (no vacuum etc), creating a lot of tables, long running (even infinite) scripts etc. It would remain a boutique feature more or less, but would add a lot of value to psql scripting which is particularly suffering from adequate error handling. Ultimately if you have stored procedures then you have the best of both worlds especially if you had a method of sending the procedure body as you can with functions and DO. A psql meta language would do in a pinch though, and it would be a lot easier to implement -- don't like the bifurcated implementation (psql and pgbench) though. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
On Tue, Apr 19, 2011 at 11:56 AM, Merlin Moncure mmonc...@gmail.com wrote: I do think that DO covers a lot of the same territory that could usefully be addressed by a more powerful backslash-command language in psql. It's in some ways quite a bit more powerful, because it's available from any client, and it knows about data types, which psql doesn't, so things like \while are going to be awkward (what comparison semantics will it use?). The only advantage I can really see to doing that stuff on the client side is that you could do things like \connect and \prompt that wouldn't make sense on the server side. Well, you missed one big advantage: with DO you are 'one transaction' limited -- this makes it unsuitable for certain classes of maintenance tasks (no vacuum etc), creating a lot of tables, long running (even infinite) scripts etc. It would remain a boutique feature more or less, but would add a lot of value to psql scripting which is particularly suffering from adequate error handling. Ah, good point. I assume you mean inadequate rather than adequate. Ultimately if you have stored procedures then you have the best of both worlds especially if you had a method of sending the procedure body as you can with functions and DO. Also true. A psql meta language would do in a pinch though, and it would be a lot easier to implement -- don't like the bifurcated implementation (psql and pgbench) though. Yeah. I was wondering if anyone was gung-ho enough about this to implement some kind of library that both programs could draw on. It probably wouldn't be super-hard, if we could agree on a rough design. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
On Tue, Apr 19, 2011 at 12:22 PM, Robert Haas robertmh...@gmail.com wrote: Yeah. I was wondering if anyone was gung-ho enough about this to implement some kind of library that both programs could draw on. It probably wouldn't be super-hard, if we could agree on a rough design. It seems to me that the Mo Betta answer would be to implement the fabled stored procedure language, that has, as its distinctive, the capability to control transactions. That would have the capability of being used in places other than just inside psql. And it would be a good way for scripting things like specialized vacuum and analyze regimens, which cannot be done inside stored functions today. -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [JDBC] [HACKERS] JDBC connections to 9.1
Kris Jurka bo...@ejurka.com writes: On Tue, 19 Apr 2011, Tom Lane wrote: For purposes of the notes in the server-side fix, could you state which JDBC driver versions these changes will first appear in? This is in 9.1dev-900 and won't be backpatched. OK, thanks. I've committed a patch to keep the server from canonicalizing a setting of UNICODE (but not any other variations). That should keep older drivers from breaking, and in a few years we can remove the kluge, if anyone bothers ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
On Tue, Apr 19, 2011 at 12:27 PM, Christopher Browne cbbro...@gmail.com wrote: On Tue, Apr 19, 2011 at 12:22 PM, Robert Haas robertmh...@gmail.com wrote: Yeah. I was wondering if anyone was gung-ho enough about this to implement some kind of library that both programs could draw on. It probably wouldn't be super-hard, if we could agree on a rough design. It seems to me that the Mo Betta answer would be to implement the fabled stored procedure language, that has, as its distinctive, the capability to control transactions. That would have the capability of being used in places other than just inside psql. And it would be a good way for scripting things like specialized vacuum and analyze regimens, which cannot be done inside stored functions today. Well, I'm all good with that, too, but am not fired up about either one to implement it myself. So I think it's going to come down to what the person doing the work feels most strongly about. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Build farm coverage for isolation tests
Excerpts from Kevin Grittner's message of mar abr 19 12:53:07 -0300 2011: Since I'm unskilled at perl and have never looked at the buildfarm scripts, could someone with the appropriate skills and knowledge add this for me? In bash, when I'm in the normal base directory for a build from source, my usual incantation is: pushd src/test/isolation make check popd make -C src/test/isolation check -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
Excerpts from Robert Haas's message of mar abr 19 13:33:27 -0300 2011: On Tue, Apr 19, 2011 at 12:27 PM, Christopher Browne cbbro...@gmail.com wrote: On Tue, Apr 19, 2011 at 12:22 PM, Robert Haas robertmh...@gmail.com wrote: Yeah. I was wondering if anyone was gung-ho enough about this to implement some kind of library that both programs could draw on. It probably wouldn't be super-hard, if we could agree on a rough design. It seems to me that the Mo Betta answer would be to implement the fabled stored procedure language, that has, as its distinctive, the capability to control transactions. That would have the capability of being used in places other than just inside psql. And it would be a good way for scripting things like specialized vacuum and analyze regimens, which cannot be done inside stored functions today. Well, I'm all good with that, too, but am not fired up about either one to implement it myself. So I think it's going to come down to what the person doing the work feels most strongly about. I'm not at all fired up about stored procedures. The \for pgbench feature I'm proposing is 2 orders of magnitude less code than that. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
On Tue, Apr 19, 2011 at 11:49 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of mar abr 19 13:33:27 -0300 2011: On Tue, Apr 19, 2011 at 12:27 PM, Christopher Browne cbbro...@gmail.com wrote: On Tue, Apr 19, 2011 at 12:22 PM, Robert Haas robertmh...@gmail.com wrote: Yeah. I was wondering if anyone was gung-ho enough about this to implement some kind of library that both programs could draw on. It probably wouldn't be super-hard, if we could agree on a rough design. It seems to me that the Mo Betta answer would be to implement the fabled stored procedure language, that has, as its distinctive, the capability to control transactions. That would have the capability of being used in places other than just inside psql. And it would be a good way for scripting things like specialized vacuum and analyze regimens, which cannot be done inside stored functions today. Well, I'm all good with that, too, but am not fired up about either one to implement it myself. So I think it's going to come down to what the person doing the work feels most strongly about. I'm not at all fired up about stored procedures. The \for pgbench feature I'm proposing is 2 orders of magnitude less code than that. ...and gets you some of the same benefits. are you sure it belongs in pgbench though, and not psql? it would be pretty weird to have to switch to pgbench to do fancy sql scripting...i'd happily do it though. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] REINDEX vs broken HOT chains, redux
Excerpts from Tom Lane's message of mar abr 19 12:29:04 -0300 2011: Last week we fixed a problem in which REINDEX could corrupt pg_index's own indexes by forbidding it from setting indcheckxmin on a system catalog's index. While thinking about bug #5985 I realized that there's a better, more general solution. Namely, that when reindexing an existing index, there cannot be any need to advance the index's indcheckxmin horizon. The existing code just blindly pushes the horizon forward to current time if it finds any possibly-broken HOT chains --- but if the index existed before, then any HOT chains that are actually broken with respect to it must predate its existing horizon. Therefore, when reindexing an existing index, we should never set indcheckxmin if it wasn't set before. In particular, this rule fixes the previous issue for system catalogs, which are certain to not have had indcheckxmin set when initdb made them. Interesting. In short, the entire update of pg_index in index_build is unwanted when reindexing an existing index. index_build doesn't currently know whether it's being called for a new index or a reindex operation, but it wouldn't be hard to pass down a flag for that. I'm intending to revert last week's patch in favor of this approach, at least in HEAD. It'll be slightly more invasive than the previous patch because of the API change for index_build, so I'm not sure whether to back-patch or not --- comments? Maybe add a new function index_build_ext that has the API change, and keep the existing index_build as a wrapper that keeps the current behavior. In HEAD just change the API of index_build and make index_build_ext a macro on top of the function (or just make it disappear.) -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] REINDEX vs broken HOT chains, redux
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Tom Lane's message of mar abr 19 12:29:04 -0300 2011: I'm intending to revert last week's patch in favor of this approach, at least in HEAD. It'll be slightly more invasive than the previous patch because of the API change for index_build, so I'm not sure whether to back-patch or not --- comments? Maybe add a new function index_build_ext that has the API change, and keep the existing index_build as a wrapper that keeps the current behavior. In HEAD just change the API of index_build and make index_build_ext a macro on top of the function (or just make it disappear.) Not sure it's worth that amount of trouble. index_build is pretty far down in the nest of code that manages index (re)building --- is it at all likely that third-party code is calling it directly? And even more to the point, if there is such third-party code, we don't want the fix to fail to operate when a reindex is invoked through that code path rather than the core paths. So if you think there's a realistic risk of this, we probably shouldn't back-patch. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mar abr 19 13:33:27 -0300 2011: Well, I'm all good with that, too, but am not fired up about either one to implement it myself. So I think it's going to come down to what the person doing the work feels most strongly about. I'm not at all fired up about stored procedures. The \for pgbench feature I'm proposing is 2 orders of magnitude less code than that. I think what that really translates to is I don't want to bother doing the careful design work that Robert talked about. -1 for that approach. I generally feel that such a feature would be better off done server-side --- after all, there's more clients in the world than psql and pgbench, and not all of them could use a C library even if we had one. But in either case the coding work is going to be dwarfed by the design work, if it's done right and not just the-first-hack-that- comes-to-mind. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] REINDEX vs broken HOT chains, redux
Excerpts from Tom Lane's message of mar abr 19 14:12:46 -0300 2011: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Tom Lane's message of mar abr 19 12:29:04 -0300 2011: I'm intending to revert last week's patch in favor of this approach, at least in HEAD. It'll be slightly more invasive than the previous patch because of the API change for index_build, so I'm not sure whether to back-patch or not --- comments? Maybe add a new function index_build_ext that has the API change, and keep the existing index_build as a wrapper that keeps the current behavior. In HEAD just change the API of index_build and make index_build_ext a macro on top of the function (or just make it disappear.) Not sure it's worth that amount of trouble. index_build is pretty far down in the nest of code that manages index (re)building --- is it at all likely that third-party code is calling it directly? Then why bother keeping the API unchanged? If you're correct, it would be pointless. And even more to the point, if there is such third-party code, we don't want the fix to fail to operate when a reindex is invoked through that code path rather than the core paths. So if you think there's a realistic risk of this, we probably shouldn't back-patch. After actually having a look at the API, I don't. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
On Tuesday, April 19, 2011 07:22:54 PM Tom Lane wrote: I generally feel that such a feature would be better off done server-side --- after all, there's more clients in the world than psql and pgbench, and not all of them could use a C library even if we had one. But in either case the coding work is going to be dwarfed by the design work, if it's done right and not just the-first-hack-that- comes-to-mind. On the other hand doing it client side stresses a different part of the code, so it might be pretty sensible for pgbench... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
On Tue, Apr 19, 2011 at 1:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think what that really translates to is I don't want to bother doing the careful design work that Robert talked about. -1 for that approach. As someone not doing any of that work, agreed ;-) I generally feel that such a feature would be better off done server-side --- after all, there's more clients in the world than psql and pgbench, and not all of them could use a C library even if we had one. But in either case the coding work is going to be dwarfed by the design work, if it's done right and not just the-first-hack-that- comes-to-mind. And for the first-hack-that-comes-to-mind, I find my self pulling out the named fifo trick all the time, and just leaving my for/loop/if logic in bash writing SQL commands to the fifo, occasionally getting psql to write an answer to a file that I then read back in bash a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] CLUSTER versus broken HOT chains
I believe I've worked out what's going on in bug #5985. The example script contains an UPDATE on a table, then a creation of an index, then a CLUSTER on that index, all within one transaction. If the UPDATE does any HOT updates, then the index is going to be marked with indcheckxmin horizon equal to current transaction, because of our inadequate detection of whether HOT chains are really broken with respect to a new index. Then when CLUSTER tries to invoke the planner to see whether the index should be preferred over a seqscan-and-sort, plancat.c decides that the index isn't usable yet, so it doesn't add the index to the relation's IndexOptInfo list, causing the reported failure index nnn does not belong to table mmm in plan_cluster_use_sort. This failure is new in 9.1 because we did not try to use the planner in this way in previous versions, but just always did an indexscan. Now, over in cluster.c we find the following interesting bit of commentary: /* * Disallow if index is left over from a failed CREATE INDEX CONCURRENTLY; * it might well not contain entries for every heap row, or might not even * be internally consistent. (But note that we don't check indcheckxmin; * the worst consequence of following broken HOT chains would be that we * might put recently-dead tuples out-of-order in the new table, and there * is little harm in that.) */ if (!OldIndex-rd_index-indisvalid) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg(cannot cluster on invalid index \%s\, RelationGetRelationName(OldIndex; So this leads me to a few thoughts: 1. Now that we have the seqscan-and-sort code path, it'd be possible to support CLUSTER on a not-indisvalid index, at least when it's a btree index. We just have to force it into the seqscan-and-sort code path. 2. We could deal with a not-usable-because-of-indcheckxmin-horizon index by forcing an indexscan, which is alleged to be safe by the above comment, or (if it's btree) by forcing a seqscan-and-sort. The problem is that we won't know which way is cheaper. I suspect however that the seqscan way is usually cheaper and we wouldn't lose much by forcing that whenever we can. 3. Or we could kluge up the planner so it doesn't ignore unusable indexes when invoked for this purpose. That seems fairly messy though. Thoughts? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
On Tue, Apr 19, 2011 at 12:27:45PM -0400, Christopher Browne wrote: On Tue, Apr 19, 2011 at 12:22 PM, Robert Haas robertmh...@gmail.com wrote: Yeah. I was wondering if anyone was gung-ho enough about this to implement some kind of library that both programs could draw on. It probably wouldn't be super-hard, if we could agree on a rough design. It seems to me that the Mo Betta answer would be to implement the fabled stored procedure language, that has, as its distinctive, the capability to control transactions. That would have the capability of being used in places other than just inside psql. And it would be a good way for scripting things like specialized vacuum and analyze regimens, which cannot be done inside stored functions today. This seems like a proposal that's evolving toward a long-standing TODO, namely autonomous transactions. At the time it was added, it went into the Exotic Features section, which I believe needs extensive reworking, if not outright deletion. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
Aidan Van Dyk ai...@highrise.ca wrote: And for the first-hack-that-comes-to-mind, I find my self pulling out the named fifo trick all the time, and just leaving my for/loop/if logic in bash writing SQL commands to the fifo, occasionally getting psql to write an answer to a file that I then read back in bash I'm not clear on exactly what you're proposing there, but the thing I've considered doing is having threads to try to keep a FIFO queue populated with a configurable transaction mix, while a configurable number of worker threads pull those transactions off the queue and submit them to the server. The transactions would need to be scripted in some way such that they could query a value and then use it in another statement, or use flow control for conditional execution or looping. And, of course, there would need to be a way do define conditions under which a transaction would roll back and retry from the beginning -- with the retry being a separate count and the failed attempt not counted in the TPS numbers. It would take that much infrastructure to have a performance test which would give numbers which would correspond well to an actual production load in our environment. It still wouldn't be quite as good as actually logging production activity and playing it back, but it would come pretty close with a lot less work per test. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
On Tue, Apr 19, 2011 at 1:57 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Aidan Van Dyk ai...@highrise.ca wrote: And for the first-hack-that-comes-to-mind, I find my self pulling out the named fifo trick all the time, and just leaving my for/loop/if logic in bash writing SQL commands to the fifo, occasionally getting psql to write an answer to a file that I then read back in bash I'm not clear on exactly what you're proposing there, but the thing I've considered doing is having threads to try to keep a FIFO queue populated with a configurable transaction mix, while a configurable number of worker threads pull those transactions off the queue and submit them to the server. The transactions would need to be scripted in some way such that they could query a value and then use it in another statement, or use flow control for conditional execution or looping. And, of course, there would need to be a way do define conditions under which a transaction would roll back and retry from the beginning -- with the retry being a separate count and the failed attempt not counted in the TPS numbers. It would take that much infrastructure to have a performance test which would give numbers which would correspond well to an actual production load in our environment. It still wouldn't be quite as good as actually logging production activity and playing it back, but it would come pretty close with a lot less work per test. Well, I don't think I'm doing anything nearly as complicated as what your'e thinking... I'm talking about simple stuff like: mkfifo psql.fifo exec 4 psql.fifo psql psql.fifo ... for i in $(seq 1 1000) do echo SELECT 1; 4 done Couple that with: echo \o /path/to/some/file 4 and other \settitngs, and I can use bash for all my logic, and just feed lines/statements to psql to have them executed as I wish, with output directed/formated as I wish... -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
Excerpts from Tom Lane's message of mar abr 19 14:22:54 -0300 2011: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mar abr 19 13:33:27 -0300 2011: Well, I'm all good with that, too, but am not fired up about either one to implement it myself. So I think it's going to come down to what the person doing the work feels most strongly about. I'm not at all fired up about stored procedures. The \for pgbench feature I'm proposing is 2 orders of magnitude less code than that. I think what that really translates to is I don't want to bother doing the careful design work that Robert talked about. -1 for that approach. No, actually it doesn't. They're different features. I don't have a problem spending time designing it; I do have a problem with designing something that I'm not interested in. I generally feel that such a feature would be better off done server-side --- after all, there's more clients in the world than psql and pgbench, and not all of them could use a C library even if we had one. Why do we have pgbench at all in the first place? Surely we could rewrite it in plpgsql with proper stored procedures. But in either case the coding work is going to be dwarfed by the design work, if it's done right and not just the-first-hack-that- comes-to-mind. If the feature we're talking about is \for and similar control structures in pgbench, then no. I'm not really interested in doing server-side stuff for this kind of thing, mainly because I don't think it belongs in the server in the first place. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transforming IN (...) to ORs, volatility
On 11.04.2011 19:33, Heikki Linnakangas wrote: On 11.04.2011 19:06, Kevin Grittner wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com wrote: Hmm, the SQL specification explicitly says that X BETWEEN Y AND Z is equal to X= Y AND X= Z It doesn't say anything about side-effects of X. Seems like an oversight in the specification. I would not expect X to be evaluated twice, and I think we should change BETWEEN to not do that. Does the SQL spec explicitly say anything about how many times X should be evaluated if you were to code it as?: X= Y AND X= Z Not explicitly. However, it does say that: NOTE 258 — Since between predicate is an ordering operation, the Conformance Rules of Subclause 9.12, “Ordering operations”, also apply. If I'm reading those ordering operation conformance rules correctly, it only allows the operand to be a simple column or an expression that's specified in the ORDER BY or similar, not an arbitrary expression. Which seems quite restrictive, but it would dodge the whole issue.. Another data point on this: DB2 disallow volatile left-operand to BETWEEN db2 = SELECT * FROM atable WHERE smallint(rand()*10) BETWEEN 4 AND 5 SQL0583N The use of routine or expression SYSFUN.RAND is invalid because it is not deterministic or has an external action. SQLSTATE=42845 I'd like us to still fix this so that there's no multiple evaluation - that would actually make BETWEEN more useful than it is today. I'm working on a patch to handle both BETWEEN and IN. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transforming IN (...) to ORs, volatility
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: I'd like us to still fix this so that there's no multiple evaluation - that would actually make BETWEEN more useful than it is today. I'm working on a patch to handle both BETWEEN and IN. One other issue here is that the parser has traditionally handled BETWEEN by multiply linking the same expression tree. I've wanted to get rid of that behavior for a long time, but never got round to it. It causes a lot of headaches for later processing, because you have to be wary of multiply processing the same tree. If we switch BETWEEN to something with a dedicated representation we could probably get rid of all multiple-linking in the parser's output, allowing ensuing simplifications downstream. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Rename pg_regress option --multibyte to --encoding
On Sat, 2011-04-16 at 15:07 -0400, Andrew Dunstan wrote: On 04/15/2011 04:36 PM, Peter Eisentraut wrote: On Fri, 2011-04-15 at 12:28 -0400, Andrew Dunstan wrote: Yeah, what is the point of this?: -# Adjust REGRESS_OPTS because we need a UTF8 database -REGRESS_OPTS = --dbname=$(CONTRIB_TESTDB) --multibyte=UTF8 --no-locale +# We need a UTF8 database +ENCODING = UTF8 +NO_LOCALE = 1 Wouldn't it have worked just to change the way we spelled the option name in the REGRESS_OPTS line? It would have, but this way you have the same uniform interface everywhere. For example, you can easily override the encoding that that test prescribes. Well whatever we do, the buildfarm is currently broken for all MSVC builds because of this change, so I hope you're intending to fix this. I was able to fix this properly now after figuring out that you can actually run vcregress.pl on Linux. So it should be easier to deal with these sorts of issues in the future. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed table DDL loose ends
On Mon, 2011-04-18 at 19:34 -0400, Noah Misch wrote: On Mon, Apr 18, 2011 at 10:44:53PM +0300, Peter Eisentraut wrote: On Sat, 2011-04-09 at 21:57 -0400, Noah Misch wrote: * Users can CREATE TABLE OF on a type they don't own This in turns blocks the owner's ability to alter the table/type. However, we already have this hazard with composite-type columns. A TODO to address this broadly seems in order, but it's not a 9.1 issue. I think we should change that to mirror the inheritance policy: you have to be the owner of the parent. Note that this is newly relevant in 9.1, because you couldn't change composite types before. Would we add that restriction to use of CREATE TABLE t (c comptype) as well, or just to CREATE TABLE t OF comptype? Well, that's a tough one. It would be a regression. I would say no to changing the former, because the fact that this prevents the type owner from changing the type is not a regression because you couldn't change types before at all. Probably we need some privileges on types to address this properly. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
On Wed, Apr 6, 2011 at 9:21 AM, Robert Haas robertmh...@gmail.com wrote: A quick review of the open items list suggests that we have three main areas that need attention before we can declare ourselves ready for beta. In no particular order: 1. There are a bunch of small, outstanding SSI patches. 2. Bugs - plural - related to pg_upgrade typed tables. 3. Assorted collation issues. Since we're targeting code freeze for beta1 for approximately now + 1 week, it's probably about time to take stock of where we are. 1. All of the SSI patches have been dealt with. 2. The typed tables stuff vs. pg_upgrade still needs work. I would be just as happy if Tom or Peter wanted to fix this, mostly for fear of getting flak over the details of the fixes, but if not I will do it. 3. The collation issues that have been discussed on-list have, I *think*, mostly been dealt with. But maybe there are some broken things that haven't been discussed yet? New things: - There is an outstanding bug-fix patch for PL/python tracebacks, proof that no patch is too small to require multiple rounds of bug fixing. - There are some minor infelicities in the handling of permissions for foreign tables. Since I committed a chunk of that stuff, I think it probably falls to me to clean this up, unless someone else wants to volunteer. Thoughts? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Needs Suggestion
Suppose Postgres is installed in two computers C1 C2. C1 have some database tables. How can I copy these database tables from C1 to C2. I mean to say that can I copy tables from postgres installed in one m/c to another m/c. Is there any command in postgres to do so or any other short cut technique. -- Thank You, Subham Roy, CSE IIT Bombay.
Re: [HACKERS] Needs Suggestion
On Apr 20, 2011, at 4:08 AM, SUBHAM ROY wrote: Suppose Postgres is installed in two computers C1 C2. C1 have some database tables. How can I copy these database tables from C1 to C2. I mean to say that can I copy tables from postgres installed in one m/c to another m/c. Is there any command in postgres to do so or any other short cut technique. Use pg_dump/pg_restore command to copy the tables from One database to another. http://www.postgresql.org/docs/9.0/interactive/backup-dump.html Thanks Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
Robert Haas robertmh...@gmail.com writes: Since we're targeting code freeze for beta1 for approximately now + 1 week, it's probably about time to take stock of where we are. 3. The collation issues that have been discussed on-list have, I *think*, mostly been dealt with. But maybe there are some broken things that haven't been discussed yet? I have no open items for collations right now, but feel a need to re-read the original patch in toto before signing off on it. I'll try to get that done in the next day or two. BTW, I'm not sure if this was mentioned on-list previously, but we are thinking of wrapping the beta the evening of Wednesday 27th, not Thursday 28th as the traditional release scheduling would have it. (It seems our British contingent is planning to take the Friday off for some wedding or other, so there's no hope of getting Windows installers built on-time otherwise.) So that's one less day than you might have been thinking. I see no reason we can't make it though. It's past time to get this puppy out the door. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Insufficient description in collation mismatch error
Hi, I tried applying a collation to a GROUP BY clause without applying the collation to the corresponding column in the SELECT clause. postgres=# SELECT things, count(*) FROM stuff GROUP BY things COLLATE C; ERROR: column stuff.things must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT things, count(*) FROM stuff GROUP BY things COLLATE ... Firstly, does it even make sense for a GROUP BY clause to accept COLLATE? Even if it does, this error message doesn't explain the problem, being that the column with the necessary collation doesn't appear in the SELECT. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench \for or similar loop
On Tue, Apr 19, 2011 at 11:22 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Tom Lane's message of mar abr 19 14:22:54 -0300 2011: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mar abr 19 13:33:27 -0300 2011: Well, I'm all good with that, too, but am not fired up about either one to implement it myself. So I think it's going to come down to what the person doing the work feels most strongly about. I'm not at all fired up about stored procedures. The \for pgbench feature I'm proposing is 2 orders of magnitude less code than that. I think what that really translates to is I don't want to bother doing the careful design work that Robert talked about. -1 for that approach. No, actually it doesn't. They're different features. I don't have a problem spending time designing it; I do have a problem with designing something that I'm not interested in. I generally feel that such a feature would be better off done server-side --- after all, there's more clients in the world than psql and pgbench, and not all of them could use a C library even if we had one. Why do we have pgbench at all in the first place? Surely we could rewrite it in plpgsql with proper stored procedures. By proper, do you mean with autonomous transactions? I don't see how you could possibly get pgbench functionality into plgsql without that. Also, sometimes the libpq stuff is an important part of what you need to be benchmarking, but I suspect that was part of your rhetorical point. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: setlocale() on Windows doesn't work correctly if the locale name
(2011/04/16 2:56), Heikki Linnakangas wrote: setlocale() on Windows doesn't work correctly if the locale name contains apostrophes or dots. As for apostrophes, isn't the cause that initdb loses the single quote of locale? ([BUGS] BUG #5818: initdb lose the single quote of locale) As the bug reporter mentions, initdb loses the single quote in reality. Concretely speaking, scanstr() called from bootscanner.l loses it. I'm not sure if it's suitable for the bootstrap code to call scanstr(). regards, Hiroshi Inoue There isn't much hope of Microsoft fixing it any time soon, it's been like that for ages, so we better work around it. So, map a few common Windows locale names known to cause problems to aliases that work. Branch -- master Details --- http://git.postgresql.org/pg/commitdiff/d5a7bf8c11c8b66c822bbb1a6c90e1a14425bd6e Modified Files -- src/bin/initdb/initdb.c | 89 +++ 1 files changed, 82 insertions(+), 7 deletions(-) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
On Tue, Apr 19, 2011 at 7:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Since we're targeting code freeze for beta1 for approximately now + 1 week, it's probably about time to take stock of where we are. 3. The collation issues that have been discussed on-list have, I *think*, mostly been dealt with. But maybe there are some broken things that haven't been discussed yet? I have no open items for collations right now, but feel a need to re-read the original patch in toto before signing off on it. I'll try to get that done in the next day or two. BTW, I'm not sure if this was mentioned on-list previously, but we are thinking of wrapping the beta the evening of Wednesday 27th, not Thursday 28th as the traditional release scheduling would have it. (It seems our British contingent is planning to take the Friday off for some wedding or other, so there's no hope of getting Windows installers built on-time otherwise.) So that's one less day than you might have been thinking. I see no reason we can't make it though. It's past time to get this puppy out the door. +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: setlocale() on Windows doesn't work correctly if the locale name
Hiroshi Inoue in...@tpf.co.jp writes: (2011/04/16 2:56), Heikki Linnakangas wrote: setlocale() on Windows doesn't work correctly if the locale name contains apostrophes or dots. As for apostrophes, isn't the cause that initdb loses the single quote of locale? ([BUGS] BUG #5818: initdb lose the single quote of locale) As the bug reporter mentions, initdb loses the single quote in reality. Concretely speaking, scanstr() called from bootscanner.l loses it. I'm not sure if it's suitable for the bootstrap code to call scanstr(). Huh? Bootstrap mode just deals with the data found in src/include/catalog/*.h. The locale names found by initdb.c are stuck in there afterwards, using regular SQL commands. I don't know where the problem really comes from, but I doubt the connection you're trying to make above. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Insufficient description in collation mismatch error
Thom Brown t...@linux.com writes: I tried applying a collation to a GROUP BY clause without applying the collation to the corresponding column in the SELECT clause. postgres=# SELECT things, count(*) FROM stuff GROUP BY things COLLATE C; ERROR: column stuff.things must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT things, count(*) FROM stuff GROUP BY things COLLATE ... Firstly, does it even make sense for a GROUP BY clause to accept COLLATE? Probably, or at least I'm hesitant to hard-wire a restriction against it. The question is isomorphic to whether you believe that different collations can have different equality semantics. You'd want that for instance if you wanted a collation to be able to implement case-insensitive comparisons. The SQL committee seem to believe that that is possible, because they take the trouble to specify that foreign-key comparisons are done using the referenced not referencing column's collation; there'd be no need for that verbiage if it couldn't matter. But there are a number of places in our existing code that would need to be improved before we could support such a thing; in general I'd have to say the code is pretty schizophrenic on the point. Even if it does, this error message doesn't explain the problem, being that the column with the necessary collation doesn't appear in the SELECT. This isn't a new problem particularly; it happens whenever a GROUP BY item isn't just a simple variable. For example regression=# select f1 from int4_tbl group by abs(f1); ERROR: column int4_tbl.f1 must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select f1 from int4_tbl group by abs(f1); ^ I agree this isn't terribly user-friendly, but it's not real clear to me how to do better. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Fix for pg_upgrade with extra new cluster databases
The attached, applied patch adds a check to throw a pg_upgrade error during the check phase, rather than during the post-check upgrade phase. Specifically, if someone removes the 'postgres' database from the old cluster and the new cluster has a 'postgres' database, the number of databases will not match. We actually could upgrade such a setup, but it would violate the 1-to-1 mapping of database counts, so we throw an error instead. Previously they got an error during the upgrade, and not at the check stage; PG 9.0.4 does the same. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c new file mode 100644 index 7472440..469bbdb *** a/contrib/pg_upgrade/check.c --- b/contrib/pg_upgrade/check.c *** *** 11,17 static void set_locale_and_encoding(ClusterInfo *cluster); ! static void check_new_db_is_empty(void); static void check_locale_and_encoding(ControlData *oldctrl, ControlData *newctrl); static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster); --- 11,18 static void set_locale_and_encoding(ClusterInfo *cluster); ! static void check_new_cluster_is_empty(void); ! static void check_old_cluster_has_new_cluster_dbs(void); static void check_locale_and_encoding(ControlData *oldctrl, ControlData *newctrl); static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster); *** check_new_cluster(void) *** 112,118 { set_locale_and_encoding(new_cluster); ! check_new_db_is_empty(); check_loadable_libraries(); --- 113,122 { set_locale_and_encoding(new_cluster); ! get_db_and_rel_infos(new_cluster); ! ! check_new_cluster_is_empty(); ! check_old_cluster_has_new_cluster_dbs(); check_loadable_libraries(); *** check_locale_and_encoding(ControlData *o *** 341,352 static void ! check_new_db_is_empty(void) { int dbnum; - bool found = false; - - get_db_and_rel_infos(new_cluster); for (dbnum = 0; dbnum new_cluster.dbarr.ndbs; dbnum++) { --- 345,353 static void ! check_new_cluster_is_empty(void) { int dbnum; for (dbnum = 0; dbnum new_cluster.dbarr.ndbs; dbnum++) { *** check_new_db_is_empty(void) *** 358,372 { /* pg_largeobject and its index should be skipped */ if (strcmp(rel_arr-rels[relnum].nspname, pg_catalog) != 0) ! { ! found = true; ! break; ! } } } ! if (found) ! pg_log(PG_FATAL, New cluster is not empty; exiting\n); } --- 359,394 { /* pg_largeobject and its index should be skipped */ if (strcmp(rel_arr-rels[relnum].nspname, pg_catalog) != 0) ! pg_log(PG_FATAL, New cluster database \%s\ is not empty\n, ! new_cluster.dbarr.dbs[dbnum].db_name); } } ! } ! ! ! /* ! * If someone removes the 'postgres' database from the old cluster and ! * the new cluster has a 'postgres' database, the number of databases ! * will not match. We actually could upgrade such a setup, but it would ! * violate the 1-to-1 mapping of database counts, so we throw an error ! * instead. ! */ ! static void ! check_old_cluster_has_new_cluster_dbs(void) ! { ! int old_dbnum, new_dbnum; ! ! for (new_dbnum = 0; new_dbnum new_cluster.dbarr.ndbs; new_dbnum++) ! { ! for (old_dbnum = 0; old_dbnum old_cluster.dbarr.ndbs; old_dbnum++) ! if (strcmp(old_cluster.dbarr.dbs[old_dbnum].db_name, ! new_cluster.dbarr.dbs[new_dbnum].db_name) == 0) ! break; ! if (old_dbnum == old_cluster.dbarr.ndbs) ! pg_log(PG_FATAL, New cluster database \%s\ does not exist in the old cluster\n, ! new_cluster.dbarr.dbs[new_dbnum].db_name); ! } } diff --git a/contrib/pg_upgrade/relfilenode.c b/contrib/pg_upgrade/relfilenode.c new file mode 100644 index 6fb336c..9a0a3ac *** a/contrib/pg_upgrade/relfilenode.c --- b/contrib/pg_upgrade/relfilenode.c *** transfer_all_new_dbs(DbInfoArr *old_db_a *** 37,48 prep_status(Restoring user relation files\n); - /* - * If the user removed the 'postgres' database from the old cluster, - * this will cause the database counts to not match and throw an error. - * We could allow this to work because the new database is empty (we - * checked), but we don't. - */ if (old_db_arr-ndbs != new_db_arr-ndbs) pg_log(PG_FATAL, old and new clusters have a different number of databases\n); --- 37,42 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: setlocale() on Windows doesn't work correctly if the locale name
(2011/04/20 9:22), Tom Lane wrote: Hiroshi Inouein...@tpf.co.jp writes: (2011/04/16 2:56), Heikki Linnakangas wrote: setlocale() on Windows doesn't work correctly if the locale name contains apostrophes or dots. As for apostrophes, isn't the cause that initdb loses the single quote of locale? ([BUGS] BUG #5818: initdb lose the single quote of locale) As the bug reporter mentions, initdb loses the single quote in reality. Concretely speaking, scanstr() called from bootscanner.l loses it. I'm not sure if it's suitable for the bootstrap code to call scanstr(). Huh? Bootstrap mode just deals with the data found in src/include/catalog/*.h. The locale names found by initdb.c are stuck in there afterwards, using regular SQL commands. bootstrap_template1() in initdb runs the BKI script in bootstrap mode to create template1. Some symbols (LC_COLLATE, LC_CTYPE in pg_database etc) in the BKI script are substituted by actual values using replace_token(). Isn't it correct? ISTM replace_token() takes care of nothing about single quotes in its input values but the comment in scanstr() says /* * Note: if scanner is working right, unescaped quotes can only * appear in pairs, so there should be another character. */ regards, Hiroshi Inoue I don't know where the problem really comes from, but I doubt the connection you're trying to make above. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump --binary-upgrade vs. ALTER TYPE ... DROP ATTRIBUTE
On Mon, Apr 18, 2011 at 7:50 PM, Noah Misch n...@leadboat.com wrote: On Fri, Apr 15, 2011 at 11:58:30AM -0400, Noah Misch wrote: When we're done with the relkind-restriction patch, I'll post a new version of this one. It will remove the circularity check and add a relkind check. Here it is. Changes from tt1v1-alter-of.patch to tt1v2-alter-of.patch: * Use transformOfType()'s relkind check in ATExecAddOf() * Remove circularity check * Open pg_inherits with AccessShareLock * Fix terminology in ATExecDropOf() comment * Rebase over pgindent changes Changes from tt2v1-binary-upgrade.patch to tt2v2-binary-upgrade.patch: * Rebase over dumpCompositeType() changes from commit acfa1f45 I think there's a bug in the tt1v1 patch. I'm getting intermittent regression test failures at this point: ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed table server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost In src/test/regress/log/postmaster.log: TRAP: FailedAssertion(!(((bool)((relation)-rd_refcnt == 0))), File: relcache.c, Line: 1756) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] time-delayed standbys
While I was out at the MySQL conference last week, I heard that one of the forthcoming MySQL features is time-delayed replication: http://forge.mysql.com/worklog/task.php?id=344 That is, a standby configured such that replay lags a prescribed amount of time behind the master. This seemed easy to implement, so I did. Patch (for 9.2, obviously) attached. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company time-delayed-standby.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: setlocale() on Windows doesn't work correctly if the locale name
On 04/19/2011 09:42 PM, Hiroshi Inoue wrote: bootstrap_template1() in initdb runs the BKI script in bootstrap mode to create template1. Some symbols (LC_COLLATE, LC_CTYPE in pg_database etc) in the BKI script are substituted by actual values using replace_token(). Isn't it correct? ISTM replace_token() takes care of nothing about single quotes in its input values but the comment in scanstr() says /* * Note: if scanner is working right, unescaped quotes can only * appear in pairs, so there should be another character. */ That's perfectly true, but only one of the replaced locale names contains a single quote mark. So clearly there's more going on here than just the bug you're referring to. Heikki's commit message specifically refers to dots in locale names, which shouldn't cause a problem of that type, I believe. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: setlocale() on Windows doesn't work correctly if the locale name
(2011/04/20 12:25), Andrew Dunstan wrote: On 04/19/2011 09:42 PM, Hiroshi Inoue wrote: bootstrap_template1() in initdb runs the BKI script in bootstrap mode to create template1. Some symbols (LC_COLLATE, LC_CTYPE in pg_database etc) in the BKI script are substituted by actual values using replace_token(). Isn't it correct? ISTM replace_token() takes care of nothing about single quotes in its input values but the comment in scanstr() says /* * Note: if scanner is working right, unescaped quotes can only * appear in pairs, so there should be another character. */ That's perfectly true, but only one of the replaced locale names contains a single quote mark. So clearly there's more going on here than just the bug you're referring to. Heikki's commit message specifically refers to dots in locale names, which shouldn't cause a problem of that type, I believe. Yes it's completely another issue as for dots. I can find no concrete reference to problems about locale names containing dots. Is the following an example? In my environment (Windows Vista using VC8) setlocale(LC_, Chinese (Traditional)_MCO.950); works and setlocale(LC_, NULL); returns Chinese (Traditional)_Macao S.A.R..950 but setlocale(LC_, Chinese (Traditional)_Macao S.A.R..950); fails. regards, Hiroshi Inoue -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pgindent weirdness
pgindent seems to have muffed it when it comes to BulkInsertStateData: diff --git a/src/backend/access/heap/hio.c b/src/backend/access/heap/hio.c index 2849992..72a69e5 100644 --- a/src/backend/access/heap/hio.c +++ b/src/backend/access/heap/hio.c @@ -150,7 +150,7 @@ ReadBufferBI(Relation relation, BlockNumber targetBlock, Buffer RelationGetBufferForTuple(Relation relation, Size len, Buffer otherBuffer, int options, - struct BulkInsertStateData *bistate) + struct BulkInsertStateData * bistate) { booluse_fsm = !(options HEAP_INSERT_SKIP_FSM); Buffer buffer = InvalidBuffer; Not sure what happened here exactly... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] REINDEX vs broken HOT chains, redux
On Tue, Apr 19, 2011 at 4:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Namely, that when reindexing an existing index, there cannot be any need to advance the index's indcheckxmin horizon. Note that if isvalid is not set then we don't know anything about the hot chains since the concurrent index build never finished. I'm also a bit concerned since the part of the use case of REINDEX is for handling precisely the situations where the index is corrupt. If I change the code for my user-defined data type and knowingly break the semantics of the btree op, I might reasonably expect a REINDEX to fix it up. ((I don't recall if we went with binary equality or btree equality for determining of updates are eligible for hot updates or not though.) -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers