[HACKERS] Question about explain of index scan
How does Index scan perform a scan for overlapping Index Cond ? If I get a plan like this, what will actually be performed if EXPLAIN shows this: Sort (cost=12.90..12.91 rows=1 width=207) Sort Key: log_actionseq - Index Scan using sl_log_1_idx2_hu, sl_log_1_idx2_hu, sl_log_1_idx2_hu, sl_log_1_idx2_hu on sl_log_1 (cost=0.00..12.89 rows=1 width=207) Index Cond: ( ((log_xid '1349053093') AND (log_xid = '1349052761')) OR ((log_xid '1349053093') AND (log_xid = '1349052761')) OR ((log_xid '1349053093') AND (log_xid = '1349052761')) OR ((log_xid '1349053093') AND (log_xid = '1349052761')) ) (this is from a query generated by Slony for 4 sets replicated from the same master) Will the same range be scanned 4 times ? Or is the scan method smart enough to collapse them into one pass ? Or does this actually mean 4 conactenated index scans (Index Scan using X, X, X, X on sl_log_1) ? -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Proof of concept COLLATE support with patch
Supports any glibc platform and possibly Win32. Adds: SELECT ... ORDER BY expr COLLATE 'locale' CREATE INDEX locale_index ON table(expr COLLATE 'locale') Index scan used when COLLATE order permits This is just a proof of concept patch. I didn't send it to -patches because as Tom pointed out, there's no hope of it getting in due to platform dependant behaviour. This patch does not use setlocale and is completely orthoganal to any locale support already in the backend. As it turns out, meaningful locale support only needs a handful of support functions to work. These are listed at the bottom. My patch only uses the first two, but the third will be needed at some stage. The use of the last one depends on how the backend ends up support locales. Both glibc and wine32 have locale sensetive versions of many functions including: toupper_l, tolower_l, strfmon_l, strtoul_l, strtof_l, strftime_l, is*_l A windows function list is at: http://msdn2.microsoft.com/library/wyzd2bce(en-us,vs.80).aspx Patch available here: http://svana.org/kleptog/pgsql/collate1.patch Implementation notes follow and table of functions is at the bottom. I hope this helps whenever someone gets around to full COLLATE support. Have a nice day, Notes: * It works by replacing (expr COLLATE 'locale') with pg_strxfrm(expr, pg_findlocale(locale)) in the parsetree. pg_findlocale returns an opaque pointer to the locale. It is STRICT IMMUTABLE and is optimised away in the final query. pg_strxfrm takes the string and the locale and returns a bytea. bytea comparison uses memcmp so is safe from other locale effects in the backend. * Use of COLLATE for an index will probably double the diskspace required for that index due to the strxfrm. * I had to add the functions to pg_proc.h because CREATE FUNCTION couldn't find them. So they have OIDs I made up. You may need to initdb, I'm not sure. You can compile pg_xlocale.c as an shared object and load them that way too if you want to avoid the initdb. * Internally they are defined as taking and returning internal. CREATE FUNCTION doesn't like that so specify opaque or oid instead. The declarations are: create function pg_findlocale(text) returns oid as 'pg_findlocale' language internal strict immutable; create function pg_strxfrm(text,oid) returns bytea as 'pg_strxfrm' language internal strict immutable; * The clause ORDER BY 1 COLLATE 'en_AU' breaks, it treats the 1 like a constant. I couldn't quickly work out how to reference the columns the right way. Long term that code should be in the sorting code anyway. * The locale needs to be in quotes, otherwise the parser converts it to lower-case. Locale names are case-sensetive on many systems. * There is a text function strcoll_l for testing collation: create function pg_strcoll_l(text,text,text) returns int4 as 'pg_strcoll_l' language internal strict immutable; * Yes this is the easy way out, implementing the inheritence of the COLLATE attribute will be much more invasive. This gives most people what they want though. * Although these functions are documented on Windows, they are not for glibc, so it is an unstable insterface. Function Needed glibc Win32 - Function returing opaquenewlocale _create_locale pointer to locale data strxfrm with locale parameter strxfrm_l _strxfrm_l Method finding encoding for nl_langinfo_l ??? locale strcoll with locale parameter strcoll_l _strcoll_l -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpmj3S2BGvSy.pgp Description: PGP signature
Re: [HACKERS] Call for 7.5 feature completion
And I think VC++ 6.0 is ok, it is power enough and not so big for pgsql's development. And latter versions of VC++ can automatically convert 6.0's project files. There are also a VC++7 to VC++6 project converter on www.codeproject.com. | You might be surprised to know that this has been already done. Back in | the 7.2 cycle there was a win32 build floating around that compiled and | built inside of visual studio 6. I think Jan Wieck was one of the | people involved in the effort. | That would be a good place to start looking. | Merlin I know sth. about Jan Wieck's work, but cannot find the VC++ projects. Now I have started a PgFoundry project vcproject. Regards, William ZHANG The peerdirect port is still available on Bruce's ftp site here: ftp://momjian.postgresql.org/pub/postgresql/win32/PeerDirect/ as a patch vs. the 7.2 postgresql. fwiw, I think your project is in a race against time vs. the upcoming improved win32 posix support. Details are skimpy but the rumors are ms is going to allow running just about any unix app without emulation. Currently the major advantage I see of providing alternative to mingw is providing 64 bit version of postgresql to windows since mingw does not appear to be going 64 bit anytime soon. The win32 build environment issue was discussed quite heatedly when the porting effort started heating up. At the time I advocated for a vc6 build environment but have since then realized that probably would have been a mistake. Merlin ---(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] PL/pgSQL: EXCEPTION NOSAVEPOINT
Rewriting all my Oracle code function-by-function could be painful ... I'm still trying to hold on to my fantasy that I can hack Postgres (and contrib/ora2pg) into submission. Why don't you just use EnterpriseDB? I looked at EnterpriseDB a few months ago. The installation errored. It left stuff in /var/opt, which I consider non-standard for a Red Hat machine. The whole product just didn't feel clean to me. I admit that's a pretty limited and subjective evaluation, especially for a beta product, but I was in the mode of broadly evaluating alternatives, so I moved on. Maybe I need to look at it again. Basically I feel more secure tracking the core project, even if I need to maintain some of my own patches. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Question about explain of index scan
Hannu Krosing [EMAIL PROTECTED] writes: If I get a plan like this, what will actually be performed if EXPLAIN shows this: Sort (cost=12.90..12.91 rows=1 width=207) Sort Key: log_actionseq - Index Scan using sl_log_1_idx2_hu, sl_log_1_idx2_hu, sl_log_1_idx2_hu, sl_log_1_idx2_hu on sl_log_1 (cost=0.00..12.89 rows=1 width=207) Index Cond: ( ((log_xid '1349053093') AND (log_xid = '1349052761')) OR ((log_xid '1349053093') AND (log_xid = '1349052761')) OR ((log_xid '1349053093') AND (log_xid = '1349052761')) OR ((log_xid '1349053093') AND (log_xid = '1349052761')) ) Will the same range be scanned 4 times ? Yes. However, I don't understand how you got that result; AFAIK the planner should have eliminated the duplicate subclauses. For example, in 8.0 I get regression=# explain select * from tenk1 where unique1 between 1 and 100 or unique1 between 1 and 100 or unique1 between 1 and 100; QUERY PLAN - Index Scan using tenk1_unique1 on tenk1 (cost=0.00..360.63 rows=102 width=244) Index Cond: ((unique1 = 1) AND (unique1 = 100)) (2 rows) Is Slony doing something to bypass the planner? 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] Call for 7.5 feature completion
I think the most popular method to build a project on Win32 is using MSVC or Intel C. Intel C can be integrated with MSVC's IDE to help developers increase their productivity. Actually I have tried to make the backend of pgsql-8.0.3 build with MSVC 6.0, and it works well. Should I polish it and send it as a patch? Having been a Win32 developer for several years, I think it is more convenient to use MSVC's IDE than CL.exe with NMAKE.exe. Although I do not like Microsoft very much, and like to use MinGW or Cygwin to do some small tests, MSVC is more suitable for native Win32 development. If pgsql want to be the first class citizen on Windows, and want to compete with MySQL, I think supporting MSVC is important. I beleive there will be many contributions from the Win32 world. I think supporting MSVC is important, certainly (though I think that supporting the Intel compiler is even better, as the only compelling reason, IMO, to switch for the server end is generated code quality). But that's very different from supporting visual studio. I've been doing cross-platform development on a big codebase for years, and the idea of trying to use the proprietary build environments on each platform, and expecting to keep them sufficiently in-sync that the end result is actually comparable on each platform is laughable. And that's on a much smaller, simpler codebase than PG with a much smaller, more integrated development team. I use gmake or cons everywhere. On Windows I run them under cygwin and have them call the MSVC commandline compiler. It all works fine. And it doesn't stop me from using Visual Studio to edit the code, run the debugger or anything like that. On OS X I can use XCode. On Solaris I use the Forte environment. On Linux I use emacs and gcc. And that's all on the same codebase with the same makefile checked out from the same CVS repository. I think the main problem with switching to visual studio project files is maintainabilty. (It's not easy to get all the custom actions used to build some parts running in VS, but i'm su8re you can do it). The core development is done on Unix, and if you can't use the same Makefiles it's only a matter of time (and I bet very short time) before the VS files would be broken compared to the main ones etc. Win32 is a much more first class citizen now that it builds with gmake than it would be then. Building with the VC compiler using GNU makefiles is a whole different story - if that can be made to work reasonably easily it would be a worthwhile goal (in my experience, for example, the VSEE compiler optimises things a whole lot better than gcc on win32). I just don't see the payoff in getting rid of make. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Question about explain of index scan
On Fri, Sep 02, 2005 at 10:31:45AM -0400, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: If I get a plan like this, what will actually be performed if EXPLAIN shows this: Sort (cost=12.90..12.91 rows=1 width=207) Sort Key: log_actionseq - Index Scan using sl_log_1_idx2_hu, sl_log_1_idx2_hu, sl_log_1_idx2_hu, sl_log_1_idx2_hu on sl_log_1 (cost=0.00..12.89 rows=1 width=207) Index Cond: ( ((log_xid '1349053093') AND (log_xid = '1349052761')) OR ((log_xid '1349053093') AND (log_xid = '1349052761')) OR ((log_xid '1349053093') AND (log_xid = '1349052761')) OR ((log_xid '1349053093') AND (log_xid = '1349052761')) ) Will the same range be scanned 4 times ? Yes. However, I don't understand how you got that result; AFAIK the planner should have eliminated the duplicate subclauses. Maybe it has to do with the xxid datatype Slony-I adds; maybe it's missing some operator or property. I wonder why we don't support more operators on Xid, so these things are avoided? Right now we only have =, AFAIR. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Hay quien adquiere la mala costumbre de ser infeliz (M. A. Evans) ---(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] PL/pgSQL: EXCEPTION NOSAVEPOINT
On Fri, Sep 02, 2005 at 02:34:50PM +, Matt Miller wrote: Rewriting all my Oracle code function-by-function could be painful ... I'm still trying to hold on to my fantasy that I can hack Postgres (and contrib/ora2pg) into submission. Why don't you just use EnterpriseDB? I looked at EnterpriseDB a few months ago. The installation errored. It left stuff in /var/opt, which I consider non-standard for a Red Hat machine. The whole product just didn't feel clean to me. I admit that's a pretty limited and subjective evaluation, especially for a beta product, but I was in the mode of broadly evaluating alternatives, so I moved on. Maybe I need to look at it again. Well, the EnterpriseDB has much more support for Oracle syntax in general, and PL/SQL in particular. If you didn't get past the installation step, certainly you didn't have a feel of how the real features work. May I suggest you at least get it working and try to port your functions to it? (Disclaimer: while I work for EDB, I haven't had any relationship to the Oracle-PL/SQL layer yet, nor have I been involved at all in their commercial offering.) -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Having your biases confirmed independently is how scientific progress is made, and hence made our great society what it is today (Mary Gardiner) ---(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] Proof of concept COLLATE support with patch
Martijn van Oosterhout kleptog@svana.org writes: Supports any glibc platform and possibly Win32. Adds: SELECT ... ORDER BY expr COLLATE 'locale' CREATE INDEX locale_index ON table(expr COLLATE 'locale') Index scan used when COLLATE order permits This is just a proof of concept patch. I didn't send it to -patches because as Tom pointed out, there's no hope of it getting in due to platform dependant behaviour. This patch does not use setlocale and is completely orthoganal to any locale support already in the backend. I still doesn't get where the hostility towards this functionality comes from. Just because some platforms provide a better interface than others doesn't mean Postgres shouldn't do the best it can with what's available. If there were an autoconf test for the *_l functions and a failover to calling setlocale (safely protected) then it's just an issue that the feature will be faster on some platforms than others. It'll still be the same behaviour on all platforms. So there's no actual platform dependent Postgres behaviour. Should readline support be ripped out because not every platform will have readline? Or O_DIRECT support? Or unix domain socket support? -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Question about explain of index scan
Alvaro Herrera [EMAIL PROTECTED] writes: I wonder why we don't support more operators on Xid, so these things are avoided? Right now we only have =, AFAIR. I once started to make a btree opclass for XID, and stopped when it occurred to me that XID comparison doesn't obey the transitive law. btree won't like that... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Call for 7.5 feature completion
Magnus Hagander [EMAIL PROTECTED] writes: Building with the VC compiler using GNU makefiles is a whole different story - if that can be made to work reasonably easily it would be a worthwhile goal (in my experience, for example, the VSEE compiler optimises things a whole lot better than gcc on win32). I just don't see the payoff in getting rid of make. +1 here. It's already enough of a pain in the neck taking care of the Windows-specific build support for libpq and psql; we're not going to take on maintaining a complete parallel build infrastructure for a proprietary platform. (In fact, there's been serious discussion of dropping the Windows-specific build scripts that are there now, as it's not clear why they are still needed when you can build the stuff in mingw and then use it elsewhere.) But we already deal with lots of different compilers, so one more shouldn't be a problem --- as long as you can drive it with gmake. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] GRANT/roles problem: grant is shown as from login role
* Tom Lane ([EMAIL PROTECTED]) wrote: This is looking a bit messy. Maybe for GRANT/REVOKE, we have to insist that privileges do not inherit, you have to actually be SET ROLE'd to whatever role has the authority to do the grant. I haven't figured out how the SQL spec avoids this problem, considering that they do have the concept of rights inheriting for roles. There is 'revoke update on t1 from u3 granted by u2;' syntax in the SQL 2003 specification. It doesn't look like we support that syntax (looking at 8.0.3 anyway)- would that solve the problem if we did? From your example, u1 couldn't revoke it because u1 couldn't become u2, and we don't support syntax for saying revoke this priviledge which was granted by someone else, but the SQL spec has that syntax and if we did then I think we'd allow the owner to use it. Does that help? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Question about explain of index scan
On R, 2005-09-02 at 10:31 -0400, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: If I get a plan like this, what will actually be performed if EXPLAIN shows this: Sort (cost=12.90..12.91 rows=1 width=207) Sort Key: log_actionseq - Index Scan using sl_log_1_idx2_hu, sl_log_1_idx2_hu, sl_log_1_idx2_hu, sl_log_1_idx2_hu on sl_log_1 (cost=0.00..12.89 rows=1 width=207) Index Cond: ( ((log_xid '1349053093') AND (log_xid = '1349052761')) OR ((log_xid '1349053093') AND (log_xid = '1349052761')) OR ((log_xid '1349053093') AND (log_xid = '1349052761')) OR ((log_xid '1349053093') AND (log_xid = '1349052761')) ) Will the same range be scanned 4 times ? Yes. However, I don't understand how you got that result; AFAIK the planner should have eliminated the duplicate subclauses. For example, in 8.0 I get This was on 7.4, sorry for forgetting to mention it. I also edited out xid types and filter expression. maybe that filter expression is also something that is shown in a weird way for mulltiple range scans ? the query was similar to this: - select log_origin, log_xid, log_tableid, log_actionseq, log_cmdtype, log_cmddata from _bbb_cluster.sl_log_1 where log_origin = 1 and ( ( log_tableid in (3,9008,9007,9005,9004,2002,2001) and (log_xid '1312955843' and _bbb_cluster.xxid_lt_snapshot(log_xid, '1312950014:1312955843:''1312955836'',''1312955783'',''1312955806'',''1312950014'',''1312952044''')) and (log_xid = '1312942023' and _bbb_cluster.xxid_ge_snapshot(log_xid, '1312942023:1312947935:''1312947917'',''1312947924'',''1312942023'',''1312946242''')) ) or ( log_tableid in (1002,1003,1013,1041,1037,1028,1026,1023,1031,1012,1048,1050,1046,1021,1019,1024,1027,1029,1025,1035,1011,1009,1010,1016,1032,1018,1030,1138) and (log_xid '1312955843' and _bbb_cluster.xxid_lt_snapshot(log_xid, '1312950014:1312955843:''1312955836'',''1312955783'',''1312955806'',''1312950014'',''1312952044''')) and (log_xid = '1312942023' and _bbb_cluster.xxid_ge_snapshot(log_xid, '1312942023:1312947935:''1312947917'',''1312947924'',''1312942023'',''1312946242''')) ) or ( log_tableid in (7001,7008,7007,7004,7039,7002,7030,7018,7038,7003,7005,7006,7009,7011,7012,7013,7016,7021, 7022,7025,7026,7027,7028,7029,7031,7033,7034,7035,7036,7037,1075,9009,9011,9012,9013,9014, 9015,9016,9017,1051,1052,1053,1054,1055,1056,1057,1058,1059,1060,1061,1062,1063,1064,1065, 1066,1067,1068,1070,1071,1072,1073,1074,1076,1077,1078) and (log_xid '1312955843' and _bbb_cluster.xxid_lt_snapshot(log_xid, '1312950014:1312955843:''1312955836'',''1312955783'',''1312955806'',''1312950014'',''1312952044''')) and (log_xid = '1312942023' and _bbb_cluster.xxid_ge_snapshot(log_xid, '1312942023:1312947935:''1312947917'',''1312947924'',''1312942023'',''1312946242''')) ) or ( log_tableid in (7051,7050,7052,7053,7054,7055,7056,7057,7058,7059,7060,7061,7062,7063,7064,7065,7066,7067,7068,7069,7070,7071,7072,7073,7074) and (log_xid '1312955843' and _bbb_cluster.xxid_lt_snapshot(log_xid, '1312950014:1312955843:''1312955836'',''1312955783'',''1312955806'',''1312950014'',''1312952044''')) and (log_xid = '1312942023' and _bbb_cluster.xxid_ge_snapshot(log_xid, '1312942023:1312947935:''1312947917'',''1312947924'',''1312942023'',''1312946242''')) ) ) order by log_actionseq; --- the table used is : CREATE TABLE sl_log_1 ( log_origin integer, log_xid xxid, log_tableid integer, log_actionseq bigint, log_cmdtype character(1), log_cmddata text ); CREATE INDEX sl_log_1_idx1 ON sl_log_1 USING btree (log_origin, log_xid, log_actionseq); ALTER TABLE sl_log_1 CLUSTER ON sl_log_1_idx1; CREATE INDEX sl_log_1_idx2_hu ON sl_log_1 USING btree (log_xid); --- to get this plan you need to disable seqscan. without second index you get an indexscan using sl_log_1_idx1 for log_origin (always 1 in my case) and a really heavy filter. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Question about explain of index scan
On Fri, Sep 02, 2005 at 11:03:24AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: I wonder why we don't support more operators on Xid, so these things are avoided? Right now we only have =, AFAIR. I once started to make a btree opclass for XID, and stopped when it occurred to me that XID comparison doesn't obey the transitive law. btree won't like that... Not having it does affect the planner somehow, right? Maybe we could have the opclass but somehow dictate that making indexes with it is verboten. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Right now the sectors on the hard disk run clockwise, but I heard a rumor that you can squeeze 0.2% more throughput by running them counterclockwise. It's worth the effort. Recommended. (Gerry Pourwelle) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proof of concept COLLATE support with patch
Greg Stark [EMAIL PROTECTED] writes: I still doesn't get where the hostility towards this functionality comes from. We're not really willing to say here is a piece of syntax REQUIRED BY THE SQL SPEC which we only support on some platforms. readline, O_DIRECT, and the like are a completely inappropriate analogy, because those are inherently platform-dependent (and not in the spec). The objection is fundamentally that a platform-specific implementation cannot be our long-term goal, and so expending effort on creating one seems like a diversion. If there were a plan put forward showing how this is just a useful way-station, and we could see how we'd later get rid of the glibc dependency without throwing away the work already done, then it would be a different story. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Call for 7.5 feature completion
I think the main problem with switching to visual studio project files is maintainabilty. (It's not easy to get all I think the target should be a way to auto create those files with gmake (maybe with mingw for configure). The format of VS6 project and workspace files is pretty simple. It should be possible to derive them from the makefiles and simple templates. Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Question about explain of index scan
Hannu Krosing [EMAIL PROTECTED] writes: On R, 2005-09-02 at 10:31 -0400, Tom Lane wrote: Yes. However, I don't understand how you got that result; AFAIK the planner should have eliminated the duplicate subclauses. the query was similar to this: [snip] Oh, the OR arms are actually *not* equivalent because of the log_tableid subclauses. So that explains why canonicalize_qual() didn't eliminate them. [ experiments... ] It looks like 8.0 and up are smart enough to consolidate the identical extracted indexquals, but not 7.4. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Question about explain of index scan
Alvaro Herrera [EMAIL PROTECTED] writes: On Fri, Sep 02, 2005 at 11:03:24AM -0400, Tom Lane wrote: I once started to make a btree opclass for XID, and stopped when it occurred to me that XID comparison doesn't obey the transitive law. Not having it does affect the planner somehow, right? Maybe we could have the opclass but somehow dictate that making indexes with it is verboten. The reason it affects the planner is that the planner assumes that operators found in a btree opclass obey the normal laws of comparison. Such an opclass would certainly break predtest.c for instance, as it uses the assumption of transitivity directly. (In any case Hannu's problem seems to be unrelated to the datatype, see followups.) 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] Proof of concept COLLATE support with patch
On Fri, Sep 02, 2005 at 03:04:20PM +0200, Martijn van Oosterhout wrote: Supports any glibc platform and possibly Win32. MacOS X [1] supports this also apparently. And for glibc it appears to have been accepted as part of the API since 2.3.2 and formally accepted into LSB3.0. Win32 claims to have supported this since '98. But even though the MacOS X manpage says BSD Library Functions at the top of the page, neither FreeBSD or OpenBSD doesn't appear to have it at all. Not really a lot of chance that we could pull portions of the Darwin libc into PostgreSQL, huh? Maybe the easiest thing would be to download the libc locale support of one of the BSDs, remove the global variable and use that... [1] http://www.hmug.org/man/3/newlocale.php Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpBsEixB7sps.pgp Description: PGP signature
Re: [HACKERS] PL/pgSQL: EXCEPTION NOSAVEPOINT
I looked at EnterpriseDB a few months ago. The installation errored. It left stuff in /var/opt, which I consider non-standard for a Red Hat machine. The whole product just didn't feel clean to me. I admit that's a pretty limited and subjective evaluation, especially for a beta product, but I was in the mode of broadly evaluating alternatives, so I moved on. Maybe I need to look at it again. Basically I feel more secure tracking the core project, even if I need to maintain some of my own patches. The EnterpriseDB guys have a final product now, and it's designed to emulate Oracle as much as possible. I'd prefer that in production than my own patches :) Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proof of concept COLLATE support with patch
Martijn van Oosterhout kleptog@svana.org writes: [1] http://www.hmug.org/man/3/newlocale.php Hmm, the more general page seems to be http://www.hmug.org/man/3/xlocale.php This seems to be pretty much exactly what we want, at least API-wise. Now, if we can find an implementation of this with a BSD license ;-) ... [ I don't recall at the moment whether Apple publishes all of Darwin under a straight BSD license, but that would surely be a good place to look first. ] 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] PL/pgSQL: EXCEPTION NOSAVEPOINT
Christopher Kings-Lynne [EMAIL PROTECTED] writes: The EnterpriseDB guys have a final product now, and it's designed to emulate Oracle as much as possible. The question at hand is whether as much as possible includes having reinvented plpgsql's execution engine ... I have not seen their product, but if they've gotten that far then they've accomplished a heck of a lot in a very short time ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] dbt-4 (tpc-app) kit
Hi everyone, I've starting putting together a kit based on the TPC-App, which is a business-to-business Web services workload. I'm starting to implement it as a Java EJB and have the most of the interactions implemented with a simple single threaded driver. The code is available here: http://prdownloads.sourceforge.net/osdldbt/dbt4-0.2.tar.gz?download I don't think my Java skills are very good, so if anyone would like to help, let me know. -- Mark Wong - - [EMAIL PROTECTED] Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 (503) 626-2455 (office) (503) 626-2436 (fax) http://developer.osdl.org/markw/ ---(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] Proof of concept COLLATE support with patch
On Fri, Sep 02, 2005 at 12:44:00PM -0400, Tom Lane wrote:
Hmm, the more general page seems to be
http://www.hmug.org/man/3/xlocale.php
This seems to be pretty much exactly what we want, at least API-wise.
Now, if we can find an implementation of this with a BSD license ;-) ...
Yes it is, it's exactly the same interface as glibc. Windows has them
all with an underscore prefix.
[ I don't recall at the moment whether Apple publishes all of Darwin
under a straight BSD license, but that would surely be a good place to
look first. ]
libc is listed as APSL licence, whatever that means. Something with
that many clauses can't be BSD compatable.
What I wonder is how come Apple implemented all this in their version
yet none of the BSDs got around to it.
I've looked around for Citrus, it appears that NetBSD contains the
latest version and while there's a lot of stuff for LC_CTYPE and charset
conversion, LC_COLLATE didn't appear to be high on their priorities.
I especially liked these fragments from the OpenBSD and NetBSD CVS
repositories. Tom, you've comvinced me, relying on the platform is
silly. We have platforms that don't support LC_COLLATE in one locale,
let alone multiple. FreeBSD thankfully does support it.
http://cvsweb.netbsd.org/bsdweb.cgi/src/lib/libc/string/strcoll.c?rev=HEAD
http://www.openbsd.org/cgi-bin/cvsweb/src/lib/libc/string/strcoll.c?rev=HEAD
--- snip ---
/*
* Compare strings according to LC_COLLATE category of current locale.
*/
int
strcoll(s1, s2)
const char *s1, *s2;
{
_DIAGASSERT(s1 != NULL);
_DIAGASSERT(s2 != NULL);
/* LC_COLLATE is unimplemented, hence always C */
return (strcmp(s1, s2));
}
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
pgp1mXXdNMgaL.pgp
Description: PGP signature
Re: [HACKERS] Proof of concept COLLATE support with patch
The sources can be found here: http://darwinsource.opendarwin.org/10.4.2/Libc-391/locale/xlocale.c The Apple License *is* necessarily compatible with the BSD License. http://www.gnu.org/philosophy/apsl.html On Sep 2, 2005, at 11:44 AM, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: [1] http://www.hmug.org/man/3/newlocale.php Hmm, the more general page seems to be http://www.hmug.org/man/3/xlocale.php This seems to be pretty much exactly what we want, at least API-wise. Now, if we can find an implementation of this with a BSD license ;-) ... [ I don't recall at the moment whether Apple publishes all of Darwin under a straight BSD license, but that would surely be a good place to look first. ] regards, tom lane |-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|- AgentM [EMAIL PROTECTED] |-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|- ---(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] Remove xmin and cmin from frozen tuples
[skip] happening in the bgwriter's inner loop. Even more to the point, you can't do such changes without getting a superexclusive lock on the page (not only locked, but no one else has it pinned), which is a real nonstarter for the bgwriter, both for performance and possible deadlock issues. Hi Tom, I do not want to discuss in deep the place to do this job, it is really over my head. But, you said you need a super-exclusive lock, and I wonder if a wait-free algorithm would be good for pg in a general manner. I have given some references about it already on the list. http://archives.postgresql.org/pgsql-hackers/2005-02/msg00263.php Cordialement, Jean-Gérard Pailloncy ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Two different defs of MAX_TUPLES_PER_PAGE
ITAGAKI Takahiro [EMAIL PROTECTED] writes: Don't you think about PageHeaderData? I doubt it's really worth taking into account ... we can though. Also I guess a floor definition is ok because 'number of tuples' is an integer. Right, now that I'm more awake I agree with that ;-) Also, is this something that should be in a common header file? If so which one? BLCKSZ, HeapTupleHeaderData, and ItemIdData are all defined in different places ... Considering include-hierarchy, I think bufpage.h is a good place. No, that's a pretty bad place because it violates the module hierarchy: access is on top of storage. None of the include/storage files know what a HeapTupleHeader looks like. I think we can just put it in htup.h, since that includes bufpage.h already. Will make it happen. 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] upgrade path / versioning roles
On Thu, Sep 01, 2005 at 02:59:37PM -0400, Tom Lane wrote:
Patrick Welche [EMAIL PROTECTED] writes:
I tried the fix mentioned in the earlier message to encourage
validation. Now dumping this fixed database, and loadinging it into the
new database gives:
ALTER FUNCTION
psql:./huge.db:4403: ERROR: function plpgsql_validator(oid) does not exist
CREATE FUNCTION
ALTER FUNCTION
psql:./huge.db:4517: ERROR: language plpgsql does not exist
HINT: You need to use createlang to load the language into the database.
Ah, right, *that's* why it's a good idea to have the dependency from the
language to the function ;-) ... else there's no guarantee pg_dump will
dump them in the right order. If you want you could add a suitable
pg_depend row.
:-) Thanks all OK..
One other thing I noticed on this longlived database, is that one can
pg_dump new tables, and their associated sequences will be created,
but for old tables, the associated sequences need creating. The
difference seems to be old:
Table public.meter
id | integer | not null default nextval('meter_id_seq'::text)
new:
Table public.summary
id | integer | not null default nextval('public.summary_id_seq'::text)
that the old sequence hasn't got the schema explicitly appended..
(Just in case someone else comes across this..)
Cheers,
Patrick
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: Procedural language definitions (was Re: [HACKERS] 8.1 and syntax checking at create time)
On Wed, Aug 31, 2005 at 05:56:52PM -0400, Tom Lane wrote: I wrote: We've had repeated problems with PL languages stemming from the fact that pg_dump dumps them at a pretty low semantic level. Aside from this problem with adding a validator, we used to have issues with hardwired paths to the shared libraries in the CREATE FUNCTION commands. And in 8.1, whether the functions are in public or pg_catalog is going to vary across installations depending on whether the language was restored from a dump or not. I wonder if we could change the dump representation to abstract out the knowledge encapsulated in createlang. I don't suppose this would work: \! createlang plpgsql dbname but it'd be nice if the dump didn't know any more about the language than its name, and didn't mention the implementation functions at all. I thought some more about this and came up with a sketch of a solution. This would solve the problem of loading subtly-bogus language definitions from existing dump files, and it also offers a possibility of relaxing the rule that only superusers can create PLs. The basic idea is to create a shared catalog that contains procedural language templates. This catalog would essentially replace the knowledge that's now hardwired in the createlang program. It's shared because we need it to be already available in a new database; and anyway, the information in it need not vary across databases of an installation. I'm envisioning a schema like pg_pltemplate: lanname namename of PL lantrusted boolean trusted? lanhandler textname of its call handler function lanvalidator textname of its validator function, or NULL lanlibrary textpath of shared library, eg $libdir/plpgsql lanacl acl[] see below This could be filled in at initdb time with information about all the languages available in the standard distribution (whether or not they've actually been built) --- heck, we could include entries for all the PLs we know of, whether shipped in the core or not. Then we would change CREATE LANGUAGE so that it first takes the given PL name and looks to see if there is an entry by that name in pg_pltemplate. If so, it *ignores the given parameters* (if any) and uses what's in pg_pltemplate. The logic would be identical to what createlang does now: look to see if the functions already exist in the current database, create them if not, then create the language entry. (If the specified shared library does not actually exist in the installation, we'd fail at the create functions step --- this is why it's OK to have entries for languages not built in the distribution.) The bit about ignoring the given parameters is needed to be able to have the right things happen when loading an existing dump script from an older PG version with different support functions for the language. However, we would also simplify pg_dump to never dump the implementation functions of a language in future, and to emit CREATE LANGUAGE as just CREATE LANGUAGE plpgsql; without decoration. (createlang would reduce to that too.) For languages that do not have a template in pg_pltemplate, CREATE LANGUAGE would operate the same as now. This case supports languages that we don't know of. It might also be worthwhile to create a command like CREATE LANGUAGE TEMPLATE ... to simplify making new entries in pg_pltemplate. (However, we could not ask pg_dump to dump templates, else we've merely moved the obsolete-dump problem over one space. Not sure if anyone would see that as a fatal objection to the scheme. I think it's a pretty minor point as long as we are liberal about including template entries in the standard distro, so that you'd seldom need to add one by hand.) Finally, you noticed I stuck an ACL column in there. I am imagining that the superuser could grant USAGE rights on a template to designated people (eg, admins of individual databases), who could then issue CREATE LANGUAGE using that template in their databases, without needing superuser rights. You'd still have to be superuser to muck with the templates of course, but given a known-good template there's no reason why a non-superuser shouldn't be allowed to instantiate the language within his database. (This might need a little more thought when it comes to untrusted PLs, but the idea seems sound.) It's a shame that we didn't think about this before feature freeze, as the recent changes to create PL support functions in pg_catalog have made both pg_dump and createlang noticeably uglier than before. We could have dispensed with those hacks. Oh well. Comments? This idea appears to me to be sound. It may be worth adding the feature during beta anyway to simplify the ugliness of pg_dump with createlang problems. The large number of weird configurations out there could
Re: Procedural language definitions (was Re: [HACKERS] 8.1 and syntax checking at create time)
[EMAIL PROTECTED] (elein) writes: On Wed, Aug 31, 2005 at 05:56:52PM -0400, Tom Lane wrote: The basic idea is to create a shared catalog that contains procedural language templates. This catalog would essentially replace the knowledge that's now hardwired in the createlang program. ... It's a shame that we didn't think about this before feature freeze, as the recent changes to create PL support functions in pg_catalog have made both pg_dump and createlang noticeably uglier than before. We could have dispensed with those hacks. Oh well. This idea appears to me to be sound. It may be worth adding the feature during beta anyway to simplify the ugliness of pg_dump with createlang problems. The large number of weird configurations out there could use the beta testing of this release. I ran into this issue a lot with non-standard installations. I was thinking the same thing, but it's a big change to put in during beta. We could trim back the size of the patch a good deal by not implementing the ACL part just yet (ie, you'd still have to be superuser to create a PL). However, we'd still need to force an initdb to add the new system catalog, and I hate to do that to our long-suffering beta testers. An even more trimmed-back version would not create a new system catalog now, but would use a constant table of known PLs that's hardwired into the CREATE LANGUAGE code. We could do that in a really localized fashion, so it seems small enough for a post-beta change. On the other hand: if we put that into beta2, and then get a related bug report, we wouldn't be really sure if the reporter had a correct PL definition or an incorrect one that he'd carried forward from beta1. Forcing an initdb would let us be sure from the version what we were dealing with. Comments anyone? 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: Procedural language definitions (was Re: [HACKERS] 8.1 and syntax
elein wrote: On Wed, Aug 31, 2005 at 05:56:52PM -0400, Tom Lane wrote: [ interesting scheme for language handlers ] It's a shame that we didn't think about this before feature freeze, as the recent changes to create PL support functions in pg_catalog have made both pg_dump and createlang noticeably uglier than before. We could have dispensed with those hacks. Oh well. Comments? This idea appears to me to be sound. It may be worth adding the feature during beta anyway to simplify the ugliness of pg_dump with createlang problems. The large number of weird configurations out there could use the beta testing of this release. I ran into this issue a lot with non-standard installations. I agree with Tom that it should not be done at this stage of beta. But maybe we should look again at the much lower impact suggestion I made when we moved the handlers and validators to pg_catalog, which was to have pg_dump also do that move rather than leave existing handlers in public. I suspect that might ease the pain a few people are feeling. If so it would be a reasonable stopgap until we get the whole thing right in the next cycle. 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] dbt-4 (tpc-app) kit
Mark, I'd like to help out, let me know what you need help doing. Personally, doing this with EJB's is distasteful, but I'll help where I can. Dave On 2-Sep-05, at 12:53 PM, Mark Wong wrote: Hi everyone, I've starting putting together a kit based on the TPC-App, which is a business-to-business Web services workload. I'm starting to implement it as a Java EJB and have the most of the interactions implemented with a simple single threaded driver. The code is available here: http://prdownloads.sourceforge.net/osdldbt/dbt4-0.2.tar.gz? download I don't think my Java skills are very good, so if anyone would like to help, let me know. -- Mark Wong - - [EMAIL PROTECTED] Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 (503) 626-2455 (office) (503) 626-2436 (fax) http://developer.osdl.org/markw/ ---(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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Call for 7.5 feature completion
William ZHANG wrote: - Original Message - From: Dave Page dpage@vale-housing.co.uk To: Andrew Dunstan [EMAIL PROTECTED]; William ZHANG [EMAIL PROTECTED] Cc: pgsql-hackers@postgresql.org Sent: Thursday, September 01, 2005 3:21 PM Subject: RE: [HACKERS] Call for 7.5 feature completion And even those are a royal pain to maintain, never mind an entire set. Besides, I'm sure what William really wants is not nmake files, but VC++ Project files - but then which version do we keep? It's not like we could say that everyone should be using VS2005, so all commits would have to be VC++ 6.0 or earlier compatible, otherwise someone is bound to complain. You are right. What I want is VC++ projects(*.dsp, *.dsw). Once the project files is created, the maintance work is simply add/remove some new/deleted source files (*.c only) from the dsps. And I think VC++ 6.0 is ok, it is power enough and not so big for pgsql's development. And latter versions of VC++ can automatically convert 6.0's project files. There are also a VC++7 to VC++6 project converter on www.codeproject.com. Also, how do you build the backend with VC without the MinGW compatibility routines and include files? I know everyone is focused on the build environment and shell script support, but there is also library code translation support in MinGW too that we use. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PL/pgSQL: EXCEPTION NOSAVEPOINT
Matt, Seriously, though, I'm willing to devote considerable time to this. Rewriting all my Oracle code function-by-function could be painful, and I would end up dragging other people around this company into it. I'm still trying to hold on to my fantasy that I can hack Postgres (and contrib/ora2pg) into submission. In the end I'm hoping that the move from Oracle will be made easier for others. I'm happy to work with you on ora2pg, as long as we can use Perl. Joe Conway has some useful oracle-table-bulkloading stuff I can probably talk him out of. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] sequences TODO items
Hi, i'm looking for some item i can do and that have enough consensus to make it worth the effort... :) * Have sequence dependency track use of DEFAULT sequences, seqname.nextval? what this means? i don't understand it... * %Disallow changing default expression of a SERIAL column? why? a SERIAL is not really a datatype but a short-hand to make an integer with a nextval's sequence as default... so why making them both (the integer type and the nextval's sequence as default) act as if it were a single unit? Actually, i have dropped sequences created with SERIAL because i found that was better to me to make it manually... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] dbt-4 (tpc-app) kit
Hi Dave, Oops, EJB's are distasteful? My experience in this area is quite lacking. Not that I necessarily want to be 100% strict but the spec says this needs to conform to WS-I BP 1.0 specification, which I understand is basically using the SOAP transport? I just thought it would be nice to have something that could be plugged into any application server (Geronimo, JBoss, etc.) with minimal configuration changes and that was where I started. I don't mind doing away with the EJB altogether. What do you suggest? To give you a little better idea of where I'm at, I have most of 7 of 9 interactions implemented, the remaining two I haven't started. The driver needs to be expanded to simulate multiple users. I haven't started any of the post processing or data collection scripts yet and some of that can be used from our other kits. My Java programming style should probably be reviewed too. ;) Mark On Fri, 2 Sep 2005 15:17:46 -0400 Dave Cramer [EMAIL PROTECTED] wrote: Mark, I'd like to help out, let me know what you need help doing. Personally, doing this with EJB's is distasteful, but I'll help where I can. Dave On 2-Sep-05, at 12:53 PM, Mark Wong wrote: Hi everyone, I've starting putting together a kit based on the TPC-App, which is a business-to-business Web services workload. I'm starting to implement it as a Java EJB and have the most of the interactions implemented with a simple single threaded driver. The code is available here: http://prdownloads.sourceforge.net/osdldbt/dbt4-0.2.tar.gz? download I don't think my Java skills are very good, so if anyone would like to help, let me know. -- Mark Wong - - [EMAIL PROTECTED] Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 (503) 626-2455 (office) (503) 626-2436 (fax) http://developer.osdl.org/markw/ ---(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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Remove xmin and cmin from frozen tuples
Tom Lane wrote: Of course, it's fair to ask whether such a program would be any faster than binary-mode COPY by the time you got done ... or enough faster to justify your effort, anyway. THe only fundamental disadvantage that COPY labors under is having to write WAL records. It might be interesting to do something similar to the recent hacks for CREATE TABLE AS, so that a COPY into a table just created in the current transaction would skip writing WAL and instead fsync the table at the end. Added to TODO: o Allow COPY into an empty table to skip WAL logging -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Remove xmin and cmin from frozen tuples
Tom Lane wrote: ITAGAKI Takahiro [EMAIL PROTECTED] writes: I think it would be a waste to retain xmin and cmin for frozen tuples because their values represent only 'visible for all transactions'. True, but the hard part is getting rid of the storage for them. I wrote a makeshift patch to compress xmin and cmin (8bytes) to 1-bit flag, using tuple overlaping. Is this idea worth trying? I think this is incredibly ugly :-(. It eliminates a fairly basic assumption which is that items on a page don't overlap. The space savings cannot be worth the loss in testability and reliability. To take just one problem, it is no longer possible to check an item offset for validity against pd_upper. If we're going to do this, we need a more invasive patch that changes the structure of heaptuple headers in a more fundamental way, and avoids breaking the page layout representation. (Something like the way Oids are now handled might work, although there are alignment issues to worry about, and it'd take more work on VACUUM's part to convert a tuple to frozen state.) I'm also less than enthused about using up our last infomask bit for a relatively unimportant purpose. We might need that for something bigger someday... though I can't presently guess what. Considering the cost/benefits, rather than doing some optimization for long-lived tuples, I would like to see us merge the existing xmin/xmax/cmin/cmax values back into three storage fields like we had in 7.4 and had to expand to a full four in 8.0 to support subtransactions. The benefit is that every row would be reduced in size by 4 bytes or 14% for all rows: * Merge xmin/xmax/cmin/cmax back into three header fields Before subtransactions, there used to be only three fields needed to store these four values. This was possible because only the current transaction looks at the cmin/cmax values. If the current transaction created and expired the row the fields stored where xmin (same as xmax), cmin, cmax, and if the transaction was expiring a row from a another transaction, the fields stored were xmin (cmin was not needed), xmax, and cmax. Such a system worked because a transaction could only see rows from another completed transaction. However, subtransactions can see rows from outer transactions, and once the subtransaction completes, the outer transaction continues, requiring the storage of all four fields. With subtransactions, an outer transaction can create a row, a subtransaction expire it, and when the subtransaction completes, the outer transaction still has to have proper visibility of the row's cmin, for example, for cursors. One possible solution is to create a phantom cid which represents a cmin/cmax pair and is stored in local memory. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: Procedural language definitions (was Re: [HACKERS] 8.1 and syntax checking at create time)
Andrew Dunstan [EMAIL PROTECTED] writes: I agree with Tom that it should not be done at this stage of beta. But maybe we should look again at the much lower impact suggestion I made when we moved the handlers and validators to pg_catalog, which was to have pg_dump also do that move rather than leave existing handlers in public. How are you retroactively going to make existing pg_dumps do that? I think trying to handle this in pg_dump would introduce still more inconsistency across installations, because on top of the variables we have already, it'd matter which pg_dump version you used. I feel the best idea for a non-initdb-forcing solution is to hardwire the template knowledge into CREATE LANGUAGE for 8.1 (with of course the intention of doing my full original proposal for 8.2). With that in place, the only messiness from loading old dumps is that you would have handler function definitions in public --- but they wouldn't be used (the actual languages would rely on handlers in pg_catalog) and could be dropped easily. One reason for doing this now rather than later is that if we wait, in 8.2 we will be having to contend with 8.1 dumps that want to load handler function definitions into pg_catalog. That'll be OK as long as said definitions are correct --- but if we change any of the PL function properties between now and 8.2, we'll have a self-inflicted problem to deal with. (In the PL template approach as I proposed it, any existing function of the right name is presumed to be the right thing.) I think it would be a really good idea if we could get that out of pg_dump again before 8.1 goes final. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Remove xmin and cmin from frozen tuples
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: THe only fundamental disadvantage that COPY labors under is having to write WAL records. It might be interesting to do something similar to the recent hacks for CREATE TABLE AS, so that a COPY into a table just created in the current transaction would skip writing WAL and instead fsync the table at the end. Added to TODO: o Allow COPY into an empty table to skip WAL logging It has to be a *new* table, not an *empty* table. If it's already visible to other xacts then somebody else could insert into it in parallel with you, because COPY doesn't take an exclusive lock. Contrariwise, it doesn't really matter (I think) if there are WAL-logged records already in the table and COPY is adding more that aren't logged. (You might have to force COPY to start adding the rows on freshly added pages ... hmm ... all of a sudden I think we had this discussion already? I for sure remember the fresh-pages trick from some other thread.) 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] Remove xmin and cmin from frozen tuples
Bruce Momjian pgman@candle.pha.pa.us writes: Considering the cost/benefits, rather than doing some optimization for long-lived tuples, I would like to see us merge the existing xmin/xmax/cmin/cmax values back into three storage fields like we had in 7.4 and had to expand to a full four in 8.0 to support subtransactions. There is another reason for trying to do that rather than the frozen-row optimization, which is that to get it down to two visibility-related fields, we'd have to find another representation for tuples that are Datums in memory. The current Datum representation overlays three int32 fields where the visibility fields are for a tuple on-disk. This works fine now, and would still work fine if we could revert to the 7.4 approach, but it doesn't play nicely with a scheme to remove 2 of the 4 fields. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Remove xmin and cmin from frozen tuples
On Fri, Sep 02, 2005 at 04:02:08PM -0400, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: THe only fundamental disadvantage that COPY labors under is having to write WAL records. It might be interesting to do something similar to the recent hacks for CREATE TABLE AS, so that a COPY into a table just created in the current transaction would skip writing WAL and instead fsync the table at the end. Added to TODO: o Allow COPY into an empty table to skip WAL logging It has to be a *new* table, not an *empty* table. If it's already visible to other xacts then somebody else could insert into it in parallel with you, because COPY doesn't take an exclusive lock. What about the indexes? Logging one of the inserters and not the other is certain to corrupt the whole thing. (Logging index insertion but not the heap itself is silly, but perhaps an easy way out is to disable the feature for tables with indexes.) Contrariwise, it doesn't really matter (I think) if there are WAL-logged records already in the table and COPY is adding more that aren't logged. Only if the page is locked in a fashion that the bulk loader can't insert tuples into a page that the other transaction is using. (Not sure if this can happen in reality.) Else we risk both inserting a tuple in the same page, and on recovery finding out that somebody else used the tuple slot. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Los románticos son seres que mueren de deseos de vida ---(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: Procedural language definitions (was Re: [HACKERS] 8.1 and syntax
Tom Lane wrote: I feel the best idea for a non-initdb-forcing solution is to hardwire the template knowledge into CREATE LANGUAGE for 8.1 (with of course the intention of doing my full original proposal for 8.2). With that in place, the only messiness from loading old dumps is that you would have handler function definitions in public --- but they wouldn't be used (the actual languages would rely on handlers in pg_catalog) and could be dropped easily. Ok, that sounds good. Maybe have pg_dump issue a warning about the useless handler funcs left lying around? 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] Remove xmin and cmin from frozen tuples
Tom, Alvaro, It has to be a *new* table, not an *empty* table. If it's already visible to other xacts then somebody else could insert into it in parallel with you, because COPY doesn't take an exclusive lock. There's still major gains to be had, for ETL, in being able to disable logging on new tables/partitions. *particularly* partitions. Contrariwise, it doesn't really matter (I think) if there are WAL-logged records already in the table and COPY is adding more that aren't logged. (You might have to force COPY to start adding the rows on freshly added pages ... hmm ... all of a sudden I think we had this discussion already? I for sure remember the fresh-pages trick from some other thread.) Yes, and that's what shot the proposal down before. But I don't think we devoted sufficient discussion to the new table case. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Remove xmin and cmin from frozen tuples
People: Bruce Momjian pgman@candle.pha.pa.us writes: Considering the cost/benefits, rather than doing some optimization for long-lived tuples, I would like to see us merge the existing xmin/xmax/cmin/cmax values back into three storage fields like we had in 7.4 and had to expand to a full four in 8.0 to support subtransactions. Hmmm. I personally don't see a whole lot of value in trimming 4 bytes per row off an archive table, particularly if the table would need to go through some kind of I/O intensive operation to do it. Where I do see value is in enabling index-only access for frozen tables. That would be a *huge* gain, especially with bitmaps. I think we've discussed this before,though. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(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] Remove xmin and cmin from frozen tuples
Alvaro Herrera [EMAIL PROTECTED] writes: On Fri, Sep 02, 2005 at 04:02:08PM -0400, Tom Lane wrote: It has to be a *new* table, not an *empty* table. If it's already visible to other xacts then somebody else could insert into it in parallel with you, because COPY doesn't take an exclusive lock. What about the indexes? Logging one of the inserters and not the other is certain to corrupt the whole thing. Good point, but that fits in just fine with the restriction to just-created tables. Contrariwise, it doesn't really matter (I think) if there are WAL-logged records already in the table and COPY is adding more that aren't logged. Only if the page is locked in a fashion that the bulk loader can't insert tuples into a page that the other transaction is using. What other transaction? The point I was making is that BEGIN; CREATE TABLE ... INSERT ... COPY ... is still optimizable. There isn't going to be anyone competing with the COPY while it runs. 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] Remove xmin and cmin from frozen tuples
On Fri, Sep 02, 2005 at 04:27:59PM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Contrariwise, it doesn't really matter (I think) if there are WAL-logged records already in the table and COPY is adding more that aren't logged. Only if the page is locked in a fashion that the bulk loader can't insert tuples into a page that the other transaction is using. What other transaction? The point I was making is that BEGIN; CREATE TABLE ... INSERT ... COPY ... is still optimizable. There isn't going to be anyone competing with the COPY while it runs. Sure. I was thinking that you were looking for a mechanism to relax the other restriction. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Dios hizo a Adán, pero fue Eva quien lo hizo hombre. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Procedural language definitions (was Re: [HACKERS] 8.1 and syntax checking at create time)
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: I feel the best idea for a non-initdb-forcing solution is to hardwire the template knowledge into CREATE LANGUAGE for 8.1 (with of course the intention of doing my full original proposal for 8.2). With that in place, the only messiness from loading old dumps is that you would have handler function definitions in public --- but they wouldn't be used (the actual languages would rely on handlers in pg_catalog) and could be dropped easily. Ok, that sounds good. Maybe have pg_dump issue a warning about the useless handler funcs left lying around? Again, you're imagining that we can retroactively fix existing pg_dumps. A pg_dump that's aware of this change will simply not dump handlers at all --- so it doesn't need to issue any warning. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proof of concept COLLATE support with patch
AgentM wrote: The sources can be found here: http://darwinsource.opendarwin.org/10.4.2/Libc-391/locale/xlocale.c The Apple License *is* necessarily compatible with the BSD License. http://www.gnu.org/philosophy/apsl.html Does compatibile mean our combined work is still BSD licensed? --- On Sep 2, 2005, at 11:44 AM, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: [1] http://www.hmug.org/man/3/newlocale.php Hmm, the more general page seems to be http://www.hmug.org/man/3/xlocale.php This seems to be pretty much exactly what we want, at least API-wise. Now, if we can find an implementation of this with a BSD license ;-) ... [ I don't recall at the moment whether Apple publishes all of Darwin under a straight BSD license, but that would surely be a good place to look first. ] regards, tom lane |-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|- AgentM [EMAIL PROTECTED] |-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|- ---(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 -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Remove xmin and cmin from frozen tuples
On Fri, Sep 02, 2005 at 01:30:58PM -0700, Josh Berkus wrote: Contrariwise, it doesn't really matter (I think) if there are WAL-logged records already in the table and COPY is adding more that aren't logged. (You might have to force COPY to start adding the rows on freshly added pages ... hmm ... all of a sudden I think we had this discussion already? I for sure remember the fresh-pages trick from some other thread.) Yes, and that's what shot the proposal down before. But I don't think we devoted sufficient discussion to the new table case. If we are going to have real partitioning sometime soon, I don't think the restriction is a problem. You may have to load a whole partition again, which may be faster than using logged COPY to an already-filled partition. The point is, it's not the whole table, just a partition. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Acepta los honores y aplausos y perderás tu libertad ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Remove xmin and cmin from frozen tuples
On Fri, Sep 02, 2005 at 01:35:42PM -0700, Josh Berkus wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Considering the cost/benefits, rather than doing some optimization for long-lived tuples, I would like to see us merge the existing xmin/xmax/cmin/cmax values back into three storage fields like we had in 7.4 and had to expand to a full four in 8.0 to support subtransactions. Hmmm. I personally don't see a whole lot of value in trimming 4 bytes per row off an archive table, particularly if the table would need to go through some kind of I/O intensive operation to do it. I think you are missing something. These 4 bytes are not trimmed by an I/O-intensive operation, they are not written in the first place. Now, I agree for a very wide table those 4 bytes per tuple may not be a lot. But the optimization could be significant for not-wide (uh, sorry, I don't remember the word) tables. Where I do see value is in enabling index-only access for frozen tables. That would be a *huge* gain, especially with bitmaps. I think we've discussed this before, though. That's a completely different discussion. Btree-organized heaps may help you there. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Having your biases confirmed independently is how scientific progress is made, and hence made our great society what it is today (Mary Gardiner) ---(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] sequences TODO items
Jaime Casanova wrote: Hi, i'm looking for some item i can do and that have enough consensus to make it worth the effort... :) * Have sequence dependency track use of DEFAULT sequences, seqname.nextval? what this means? i don't understand it... The idea is to automatically add a dependency of the sequence on the table using it as a default. * %Disallow changing default expression of a SERIAL column? why? a SERIAL is not really a datatype but a short-hand to make an integer with a nextval's sequence as default... so why making them both (the integer type and the nextval's sequence as default) act as if it were a single unit? Actually, i have dropped sequences created with SERIAL because i found that was better to me to make it manually... Well, SERIAL adds dependency information, so if you drop the table, the sequence is dropped. If you change the default for a serial type, the dependency on the sequence should go away. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Ora2Pg (was PL/pgSQL: EXCEPTION NOSAVEPOINT)
On Fri, 2005-09-02 at 12:29 -0700, Josh Berkus wrote:
still trying to hold on to my fantasy that I can hack Postgres (and
contrib/ora2pg) into submission.
I'm happy to work with you on ora2pg
Cool.
It looks like I should have referred to contrib/oracle, not
contrib/ora2pg, but you got my point.
The latest version I found of ora2pg is at
http://www.samse.fr/GPL/ora2pg/ora2pg-3.3.tar.gz This seems to be more
recent than the version at contrib/oracle. For example, this newer
version has tablespace support. Given this as a starting point, I've
made the attached changes. Mostly I've added a few new config options,
but I also made a correction to the existing EXCLUDE option, and I
corrected a couple spelling/English errors along the way.
A big thing that's lacking is conversion for stored procedures and
functions. My initial approach to this was to use Perl to post-process
the PL/SQL code dumped by the export, making it look more like proper
Pl/pgSQL (e.g. VARCHAR2-VARCHAR). I'm no Perl hacker, and when I came
across significant PL/SQL -- PL/pgSQL differences (e.g. PL/pgSQL
exception == rollback), I added to my approach the idea of hacking
PL/pgSQL to make it look more like PL/SQL. Attacking the problem from
both ends like this, I imagined that Nirvana would be reached somewhere
in the middle.
The beginning of my Perl-based attempt to convert PL/SQL into PL/pgSQL
is a pretty simple stand-alone script. I can send it if you like, but
I'm a Perl newbie, so you can probably do much better. My attempts to
make PL/pgSQL look like PL/SQL have been posted to -hackers and -patches
over the last couple months.
diff -c ora2pg_3.3/ora2pg.conf ora2pg/ora2pg.conf
*** ora2pg_3.3/ora2pg.conf 2004-12-24 16:05:40.0 +
--- ora2pg/ora2pg.conf 2005-09-02 20:38:48.900376220 +
***
*** 56,61
--- 56,68
# Value must be a list of table name separated by space.
#EXCLUDE OTHER_TABLES
+ # Set whether to include invalid functions, procedures, and packages.
+ # Under Oracle's on-the-fly invalidation/recompilation model there
+ # may be any number of objects that have status of 'INVALID' but that
+ # are actually viable.
+ INCLUDE_INVALID 1
+
+
# Display table indice and exit program (do not perform any export)
SHOWTABLEID 0
***
*** 139,148
# Constraints will be checked at the end of each transaction.
DEFER_FKEY 0
! # If set to 1 replace portable numeric type into PostgreSQL internal type.
# Oracle data type NUMBER(p,s) is approximatively converted to smallint,
# integer, bigint, real and float PostgreSQL data type. If you have monetary
# fields you should preserve the numeric(p,s) PostgreSQL data type if you need
! # very good precision. NUMBER without precision are set to float.
! PG_NUMERIC_TYPE 1
--- 146,171
# Constraints will be checked at the end of each transaction.
DEFER_FKEY 0
! # If set to 1 replace portable numeric type with PostgreSQL internal type.
# Oracle data type NUMBER(p,s) is approximatively converted to smallint,
# integer, bigint, real and float PostgreSQL data type. If you have monetary
# fields you should preserve the numeric(p,s) PostgreSQL data type if you need
! # very good precision (see PG_INTEGER_TYPE). NUMBER without precision are set to
! # float.
! PG_NUMERIC_TYPE 0
!
! # If set to 1 replace portable numeric type with PostgreSQL internal type,
! # for integers only. This behaves as PG_NUMERIC_TYPE with respect to
! # Oracle data type NUMBER(p), but preserves exact arithmetic on NUMBER(p,s)
! # columns by converting to PostgreSQL numeric(p,s). NUMBER without precision
! # maps to numeric without precision.
! PG_INTEGER_TYPE 1
!
! # If set to 1 map Oracle's DATE type to PostgreSQL DATE type. Oracle DATE type
! # can contain time information, so PostgreSQL timestamp should, in general, be
! # used to hold Oracle DATEs. However, Oracle also supports TIMESTAMP. Setting
! # PG_DATE_TYPE indicates that Oracle TIMESTAMPs are the only incoming date columns
! # with a time portion that needs to be preserved, and that incoming Oracle DATEs
! # effectively contain only a date portion.
! PG_DATE_TYPE 1
diff -c ora2pg_3.3/ora2pg.pl ora2pg/ora2pg.pl
*** ora2pg_3.3/ora2pg.pl 2004-12-24 16:05:40.0 +
--- ora2pg/ora2pg.pl 2005-07-07 18:01:53.0 +
***
*** 40,45
--- 40,46
#tables = [EMAIL PROTECTED]'TABLES'}},
tables = $Config{'TABLES'},
exclude = $Config{'EXCLUDE'},
+ include_invalid = $Config{'INCLUDE_INVALID'} || 0,
showtableid = $Config{'SHOWTABLEID'} || 0,
min = $Config{'MIN'} || 0,
max = $Config{'MAX'} || 0,
***
*** 56,66
fkey_deferrable = $Config{'FKEY_DEFERRABLE'} || 0,
defer_fkey = $Config{'DEFER_FKEY'} || 0,
pg_numeric_type = $Config{'PG_NUMERIC_TYPE'} || 0,
);
exit 0 if ($Config{'SHOWTABLEID'});
! # Mofify export structure if required
if ($Config{'TYPE'} =~ /^(DATA|COPY)$/) {
for my $t
Re: [HACKERS] Remove xmin and cmin from frozen tuples
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Fri, Sep 02, 2005 at 04:02:08PM -0400, Tom Lane wrote: It has to be a *new* table, not an *empty* table. If it's already visible to other xacts then somebody else could insert into it in parallel with you, because COPY doesn't take an exclusive lock. What about the indexes? Logging one of the inserters and not the other is certain to corrupt the whole thing. Good point, but that fits in just fine with the restriction to just-created tables. Seem the newly created table could have an index, but we would skip logging on that too and create a zero-length file on crash restore. Contrariwise, it doesn't really matter (I think) if there are WAL-logged records already in the table and COPY is adding more that aren't logged. Only if the page is locked in a fashion that the bulk loader can't insert tuples into a page that the other transaction is using. What other transaction? The point I was making is that BEGIN; CREATE TABLE ... INSERT ... COPY ... is still optimizable. There isn't going to be anyone competing with the COPY while it runs. Updated TODO: o Allow COPY on a newly-created table to skip WAL logging On crash recovery, the table involved in the COPY would have its heap and index files truncated. One issue is that no other backend should be able to add to the table at the same time, which is something that is currently allowed. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Remove xmin and cmin from frozen tuples
Alvaro Herrera [EMAIL PROTECTED] writes: On Fri, Sep 02, 2005 at 01:35:42PM -0700, Josh Berkus wrote: Where I do see value is in enabling index-only access for frozen tables. That would be a *huge* gain, especially with bitmaps. I think we've discussed this before, though. That's a completely different discussion. Btree-organized heaps may help you there. There was some talk of using a spare bit in index entries to mark known good index entries (xmin committed and less than GlobalXmin, and xmax invalid) but the cost of maintaining such bits seems nontrivial. In any case I agree that's an independent issue. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Remove xmin and cmin from frozen tuples
Bruce Momjian pgman@candle.pha.pa.us writes: Updated TODO: o Allow COPY on a newly-created table to skip WAL logging On crash recovery, the table involved in the COPY would have its heap and index files truncated. One issue is that no other backend should be able to add to the table at the same time, which is something that is currently allowed. This is simply wrong. (1) a table created in the current transaction isn't visible to anyone else, (2) the correct rollback state is for it not to be there, rather than be there and empty. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Remove xmin and cmin from frozen tuples
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Updated TODO: o Allow COPY on a newly-created table to skip WAL logging On crash recovery, the table involved in the COPY would have its heap and index files truncated. One issue is that no other backend should be able to add to the table at the same time, which is something that is currently allowed. This is simply wrong. (1) a table created in the current transaction isn't visible to anyone else, (2) the correct rollback state is for it not to be there, rather than be there and empty. New text: o Allow COPY on a newly-created table to skip WAL logging On crash recovery, the table involved in the COPY would removed or have its heap and index files truncated. One issue is that no other backend should be able to add to the table at the same time, which is something that is currently allowed. I think we can lock a zero-length table and do this optimization. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Remove xmin and cmin from frozen tuples
On Fri, Sep 02, 2005 at 05:18:09PM -0400, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Updated TODO: o Allow COPY on a newly-created table to skip WAL logging On crash recovery, the table involved in the COPY would have its heap and index files truncated. One issue is that no other backend should be able to add to the table at the same time, which is something that is currently allowed. This is simply wrong. (1) a table created in the current transaction isn't visible to anyone else, (2) the correct rollback state is for it not to be there, rather than be there and empty. As a related note: I remember somebody mentioned some time ago that if you create a table and then crash before ending the transaction, the tuple in pg_class is no longer valid, but the file remains. I think this will be a much worse problem if we allow a table that's being COPY'ed to remain after a crash. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Oh, oh, las chicas galacianas, lo harán por las perlas, ¡Y las de Arrakis por el agua! Pero si buscas damas Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proof of concept COLLATE support with p.tch
On Fri, Sep 02, 2005 at 04:49:03PM -0400, Bruce Momjian wrote: AgentM wrote: The sources can be found here: http://darwinsource.opendarwin.org/10.4.2/Libc-391/locale/xlocale.c The Apple License *is* necessarily compatible with the BSD License. http://www.gnu.org/philosophy/apsl.html Does compatibile mean our combined work is still BSD licensed? No, because of clause 2.2 (c) of the APSL, at least. (Must distribute source code if modified.) -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Dios hizo a Adán, pero fue Eva quien lo hizo hombre. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] statement logging / extended query protocol issues
I have applied the following patch to output unnamed for unnamed
prepared statements. As far as your other issues, how would you want
server-side statements to be logged? statement: is a log label for a
statement. What else should we use?
---
Oliver Jowett wrote:
8.1-beta1 produces some odd results with statement logging enabled when
the extended query protocol is used (e.g. when using the JDBC driver).
Repeatedly running a simple query with log_statement = 'all' produces this:
LOG: statement: PREPARE AS SELECT 'dummy statement'
LOG: statement: BIND
LOG: statement: EXECUTE [PREPARE: SELECT 'dummy statement']
[...]
LOG: statement: PREPARE S_2 AS SELECT 'dummy statement'
LOG: statement: BIND
LOG: statement: EXECUTE [PREPARE: SELECT 'dummy statement']
LOG: statement: BIND
LOG: statement: EXECUTE [PREPARE: SELECT 'dummy statement']
LOG: statement: BIND
[...]
Comments:
- The PREPARE lines are misleading as the query actually sent does not
include PREPARE at all.
- The driver never sends EXECUTE as a statement, but it is logged as one.
- PREPARE AS is a confusing way of saying the unnamed statement
- The BIND lines are content-free.
Secondly, running a query that uses portals produces output like this:
LOG: statement: PREPARE S_3 AS SELECT * from pg_proc
LOG: statement: BIND C_4
LOG: statement: EXECUTE C_4 [PREPARE: SELECT * from pg_proc]
LOG: statement: EXECUTE C_4 [PREPARE: SELECT * from pg_proc]
LOG: statement: EXECUTE C_4 [PREPARE: SELECT * from pg_proc]
LOG: statement: EXECUTE C_4 [PREPARE: SELECT * from pg_proc]
LOG: statement: EXECUTE C_4 [PREPARE: SELECT * from pg_proc]
LOG: statement: EXECUTE C_4 [PREPARE: SELECT * from pg_proc]
LOG: statement: EXECUTE C_4 [PREPARE: SELECT * from pg_proc]
Comments:
- The BIND is still fairly content-free.
- The EXECUTEs are a bit misleading as the SELECT was actually only run
once (there are multiple Execute messages for the same portal). You
could infer that there is only one SELECT from the repeated portal name
and the lack of an intervening BIND, I suppose.
8.1 is admittedly better than 8.0 here (8.0 had no logging in this case
at all).. but it's not very user-friendly as it stands. I'm sure the
JDBC list is going to get lots of why does statement logging give me
this weird output questions :/
I've attached the Java code I used to produce this. It expects a single
argument, the JDBC URL to use, e.g.
'jdbc:postgresql://localhost:8101/test?user=oliver'
-O
import java.sql.*;
import java.util.*;
public class TestStatementLogging {
public static void main(String[] args) throws Exception {
Class.forName(org.postgresql.Driver);
Connection conn = DriverManager.getConnection(args[0]);
conn.setAutoCommit(false);
PreparedStatement stmt = conn.prepareStatement(SELECT 'dummy
statement');
for (int j = 0; j 10; ++j)
stmt.executeQuery();
stmt.close();
stmt = conn.prepareStatement(SELECT * from pg_proc);
stmt.setFetchSize(1);
ResultSet rs = stmt.executeQuery();
while (rs.next())
;
stmt.close();
conn.createStatement().execute(I am a syntax error);
}
}
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: src/backend/tcop/postgres.c
===
RCS file: /cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.457
diff -c -c -r1.457 postgres.c
*** src/backend/tcop/postgres.c 11 Aug 2005 21:11:45 - 1.457
--- src/backend/tcop/postgres.c 2 Sep 2005 21:46:20 -
***
*** 1164,1170
if (log_statement == LOGSTMT_ALL)
ereport(LOG,
! (errmsg(statement: PREPARE %s AS %s,
stmt_name, query_string)));
/*
* Start up a transaction command so we can run parse analysis etc.
--- 1164,1172
if (log_statement == LOGSTMT_ALL)
ereport(LOG,
! (errmsg(statement: PREPARE %s AS %s,
! (*stmt_name != '\0') ?
stmt_name : unnamed,
! query_string)));
/*
* Start up a transaction command so we can run parse analysis etc.
***
*** 1732,1738
if (log_statement == LOGSTMT_ALL)
/* We have the portal, so output the source query. */
ereport(LOG,
!
Re: [HACKERS] Remove xmin and cmin from frozen tuples
Alvaro Herrera [EMAIL PROTECTED] writes: I remember somebody mentioned some time ago that if you create a table and then crash before ending the transaction, the tuple in pg_class is no longer valid, but the file remains. Right --- it will be removed on transaction rollback, but not if the backend crashes first. There was a patch submitted earlier this year to try to clean out such files, but it got rejected (as too messy IIRC). I think we still have a TODO item about it. 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] Remove xmin and cmin from frozen tuples
On Fri, 2 Sep 2005 15:51:15 -0400 (EDT), Bruce Momjian pgman@candle.pha.pa.us wrote: * Merge xmin/xmax/cmin/cmax back into three header fields And don't forget xvac, please. Before subtransactions, there used to be only three fields needed to store these four values. ... five values. This was possible because only the current transaction looks at the cmin/cmax values. Which is a reason to get rid of cmin/cmax in tuple headers entirely. Once I had a patch based on 7.4 that stored cmin and cmax in backend-local memory. It passed make check and some volume tests, but I felt it was not ready to be applied without any spill-to-disk mechanism. Development stalled when I tried to eliminate xvac as well, which would have required deep cuts into VACUUM code :-( Servus Manfred ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Remove xmin and cmin from frozen tuples
Manfred Koizar wrote: On Fri, 2 Sep 2005 15:51:15 -0400 (EDT), Bruce Momjian pgman@candle.pha.pa.us wrote: * Merge xmin/xmax/cmin/cmax back into three header fields And don't forget xvac, please. Before subtransactions, there used to be only three fields needed to store these four values. ... five values. This was possible because only the current transaction looks at the cmin/cmax values. Which is a reason to get rid of cmin/cmax in tuple headers entirely. Once I had a patch based on 7.4 that stored cmin and cmax in backend-local memory. It passed make check and some volume tests, but I felt it was not ready to be applied without any spill-to-disk mechanism. Development stalled when I tried to eliminate xvac as well, which would have required deep cuts into VACUUM code :-( Interesting idea, but how would you record the cmin/xmin values without requiring unlimited memory? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
