Re: [HACKERS] [pgsql-hackers-win32] Build with Visual Studio MSVC
Gurjeet Singh wrote: My main grudge is that if we are supporting almost all flovours of nixens and compilers (close to 34 according to official website), then why are we leaving Windows platform alone? This will bring in quite a lot more developers. You should look at MinGW as a development toolkit, not a platform. PostgreSQL builds and runs just fine on the Windows platform. Personally, I use Eclipse C/C++ with MinGW since it brings me a number of advantages. The most prominent one is that I only need to master one IDE regardless of platform. I am sure it's not going to be easy, but I am sure with this great community suppport, we sure can achieve it. Seems some people has done a lot of work to get things working with VC++ already. Search for the word MSVC on this list. Regards, Thomas Hallgren ---(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: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio MSVC
Hi William(uniware), Chuck and Hackers, I have been interested in doing complete PGSQL development in MSVC for a long time now. With reference to one of Chuck's mails to -hackers-win32 with the same subject, you said that you were able to successfully compile PG 8.1 with some minor tweaks. Also, William has 'vcproject' hosted on pgfoundry, I downloaded it, and tried compiling vcproject\msvc\postgres\postgres.dsw on VC++6.0. It failed miserably with over 1000 errors. I am sure there's some tweaks needed here too!!! Yes. There is a patch pending on -patches which fix almost all of these in HEAD. (There are a few tiny things related to perl and NLS that aren't included in it ATM. And I'm just assuming you're seeing the same problems as I was but I didn't base my work off vcproject). I'm also working on a buildscript to convert the Makefiles to visual c++ project files, but that's not quite done yet. The idea with this work is to have the stuff as integrated as possible with main CVS, so the maintenance will be as low as possible - unlike the vcproject project which has been focusing on keeping a separate build environment maintained. The target is VC++ 2003 and 2005 ATM, but it should just be a matter of a different output format for VC 6.0 I guess. You will still need things like bison and flex if you want to build off cvs, of course - there is no builtin support for that in VC++. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio MSVC
Yes. There is a patch pending on -patches which fix almost all of these in HEAD. (There are a few tiny things related to perl and NLS that aren't included in it ATM. And I'm just assuming you're seeing the same problems as I was but I didn't base my work off vcproject). I'm also working on a buildscript to convert the Makefiles to visual c++ project files, but that's not quite done yet. The idea with this work is to have the stuff as integrated as possible with main CVS, so the maintenance will be as low as possible - unlike the vcproject project which has been focusing on keeping a separate build environment maintained. The CrystalSpace and PlaneShift projects I'm associated with have automated MSVC generators. Want me to try to grab them for you? They'd be GPL though. Chris ---(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: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio MSVC
Yes. There is a patch pending on -patches which fix almost all of these in HEAD. (There are a few tiny things related to perl and NLS that aren't included in it ATM. And I'm just assuming you're seeing the same problems as I was but I didn't base my work off vcproject). I'm also working on a buildscript to convert the Makefiles to visual c++ project files, but that's not quite done yet. The idea with this work is to have the stuff as integrated as possible with main CVS, so the maintenance will be as low as possible - unlike the vcproject project which has been focusing on keeping a separate build environment maintained. The CrystalSpace and PlaneShift projects I'm associated with have automated MSVC generators. Want me to try to grab them for you? They'd be GPL though. You mean they have a tool that parses GNU Makefiles and generate VC project files? Sure, that might be interesting. I've seen I think two others, and tried, but they fell over badly because the pg build system was too complicated. But I beleive I'm still allowed to loko at GPL stuff and get ideas as long as I don't copy the code :-) //MAgnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio MSVC
You mean they have a tool that parses GNU Makefiles and generate VC project files? Sure, that might be interesting. I've seen I think two others, and tried, but they fell over badly because the pg build system was too complicated. But I beleive I'm still allowed to loko at GPL stuff and get ideas as long as I don't copy the code :-) http://cvs.sourceforge.net/viewcvs.py/crystal/CS/mk/msvcgen/ I think it actually takes some sort of XML format or something...or just reads in files in directories. I'm not sure. Chris ---(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: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio MSVC
- Original Message - From: Magnus Hagander The target is VC++ 2003 and 2005 ATM, but it should just be a matter of a different output format for VC 6.0 I guess. You will still need things like bison and flex if you want to build off cvs, of course - there is no builtin support for that in VC++. Ahh, Your right... However, I wish to offer construction of the DSP project of VC6. I continues after you. But, This is not fully considered carefully yet. http://inetrt.skcapi.co.jp/~saito/pgsql82dev/ Probably , Flex and bison do not need for the formal release souce of PostgreSQL. It will already be generated. Regards, Horoshi Saito ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Is a SERIAL column a black box, or not?
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
My idea is to create a new SECURITY DEFINER function called
serial_nextval(), and use that for SERIAL defaults.
You haven't thought about this at all. Who will own that function?
Surely we don't want to create a new one for every SERIAL column.
And even if we did, what magic will cause its ownership to change
when the table's owner is changed?
It would have to be a function that somehow grabbed the table owner and
internally did the permission checks based on that, but since CHECK
needs something similar, I think AS OWNER is probably best. Does that
solve all the SERIAL black box problems? TODO shows these SERIAL
issues:
* %Disallow changing default expression of a SERIAL column?
* %Disallow ALTER SEQUENCE changes for SERIAL sequences because pg_dump
does not dump the changes
* %Have ALTER TABLE RENAME rename SERIAL sequence names
I'm leaning towards the idea that we need special syntax, along the
lines of
DEFAULT nextval('some_seq') AS OWNER
which would result in generating a special expression node type at
the time the DEFAULT expression is inserted into a query plan (and
no earlier). At runtime this node would temporarily switch
current_user, just as we do for SECURITY_DEFINER functions --- but by
postponing the determination of which user to switch to until the plan
is built, we avoid trouble with ALTER TABLE OWNER.
Per Bruno's earlier comments, we probably need the same feature for
table CHECK constraints. Might be interesting to think about it for
domain check constraints too, though that's getting a bit far afield
unless someone has a convincing use-case.
Added to TODO:
* Add DEFAULT .. AS OWNER so permission checks are done as the table
owner
This would be useful for SERIAL nextval() calls and CHECK constraints.
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Altering view ownership doesn't work ...
Agreed, RULE permissions seem to be of limited usefulness. --- Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: On Sun, Apr 30, 2006 at 12:34:42PM -0400, Tom Lane wrote: 2. Run setRuleCheckAsUser during rule load rather than rule store. FWIW, I think #2 is better also. Actually, I'm sitting here realizing the problem is more complicated than I thought :-(. The spanner in the works is the existence of the RULE privilege --- a table owner can grant someone else the right to add rules to his table. As things currently work, when the someone else does so, it's *his* OID not the table owner's that gets put into the rule's checkAsUser fields. Thus for example the someone else could add a logging rule that makes entries into a table that the actual table owner has no permissions for. Whether or not you consider that sort of thing useful, it would certainly be bad to use the table owner's OID for such permission checks, because then granting RULE privilege on any table would be tantamount to handing over every permission the table owner has --- the grantee would be able to install arbitrary SQL to be executed as the table owner. So really the RULE privilege only makes sense if a rule is considered to be a separate object with separate ownership. So it seems we either have to abandon the separate RULE privilege (and just say that only table owners can install rules, and the rules are always executed as though by the current owner), or we have to promote rules to be fully separately owned objects. The latter will be a horrid mess, in particular it will break existing dump files that just ALTER the table's owner and don't go through altering ownership of individual rules. (No, we can't have ALTER TABLE OWNER automatically recurse to the individual rules, that'd just create the same Trojan-horse situation where a malicious rule now has the privileges it didn't have to start with.) I'm inclined to think that the best choice is to drop the separate RULE privilege. It's an interesting feature but I gauge its actual usefulness by the fact that I didn't even realize it worked like that. 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 -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Transactions per second
I often find myself wanting to know how many transactions per second a database is committing to disk, as well as how many queries per second it's processing. While Larry's busy making stats changes, I'd like to propose a few more counters: Number of commits: Ideally, this would only count transactions that actually modify data Number of statements: Simply, how many statements have been executed Number of DML statements: how many insert/update/delete statements executed. For the last two, it would be ideal if they counted statements that were executed inside of a function. Having these counters would make it easier to determine what kind of workload a server is actually handling. It's currently possible to determine transactions over a time period, but the process is rather convoluted and doesn't differentiate between transactions that modify data and those that don't. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [pgsql-hackers-win32] Build with Visual Studio MSVC
`vcproject` is based on pgsql-8.0.3. It's purpose is to make pgsql built with MSVC++. But I found there are few people intrested on it, so I stopped maintaining it months ago. `vcproject` still need MSYS/MinGW, the basic idea behind it is: 1) Let we do configure, make, make install in MinGW first. This step can make sure our source code is OK under MinGW, building with GNU toolchain. 2) My major work is maintaining MSVC++'s special project files, including *.dsp and *.dsw. I have also do some minor changes to the source files, you can diff with pgsql-8.0.3. 3) Finally, I can build the while system with MSVC++'s IDE or command line MSVC.exe. See README for details. The link is: http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/vcproject/vcproject/README?rev=1 .8content-type=text/x-cvsweb-markup `vcproject` is not perfect, but it works for me. And I think it can work with pgsql-8.1. Sorry for the late response. Regards, William ZHANG ---(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: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio
VC++6.0 isn't a very good compiler and it's not very compatible with gcc, while Visual Studio 2005 compiler is much more compatible and has a better optimizer. Plus, VC++6.0 had a closed proprietary data format for .dsp and .dsw files, while the current Visual Studio uses a standard XML format. Finally, Microsoft gives away (as in free, no cost) Visual C++ Express edition, which includes the current compiler. I don't see any reason we'd want to target VC++6.0. P.s. With the current Visual Studio, it's easy to add Bison and Flex custom rules, so that it automatically calls them for .y and .l files. -Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] Sent: Friday, May 05, 2006 12:42 AM To: Gurjeet Singh; pgsql-hackers@postgresql.org; [EMAIL PROTECTED]; Chuck McDevitt Subject: RE: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio MSVC Hi William(uniware), Chuck and Hackers, I have been interested in doing complete PGSQL development in MSVC for a long time now. With reference to one of Chuck's mails to -hackers-win32 with the same subject, you said that you were able to successfully compile PG 8.1 with some minor tweaks. Also, William has 'vcproject' hosted on pgfoundry, I downloaded it, and tried compiling vcproject\msvc\postgres\postgres.dsw on VC++6.0. It failed miserably with over 1000 errors. I am sure there's some tweaks needed here too!!! Yes. There is a patch pending on -patches which fix almost all of these in HEAD. (There are a few tiny things related to perl and NLS that aren't included in it ATM. And I'm just assuming you're seeing the same problems as I was but I didn't base my work off vcproject). I'm also working on a buildscript to convert the Makefiles to visual c++ project files, but that's not quite done yet. The idea with this work is to have the stuff as integrated as possible with main CVS, so the maintenance will be as low as possible - unlike the vcproject project which has been focusing on keeping a separate build environment maintained. The target is VC++ 2003 and 2005 ATM, but it should just be a matter of a different output format for VC 6.0 I guess. You will still need things like bison and flex if you want to build off cvs, of course - there is no builtin support for that in VC++. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio MSVC
On Fri, May 05, 2006 at 09:50:38AM +0200, Magnus Hagander wrote: You mean they have a tool that parses GNU Makefiles and generate VC project files? Sure, that might be interesting. I've seen I think two others, and tried, but they fell over badly because the pg build system was too complicated. But I beleive I'm still allowed to loko at GPL stuff and get ideas as long as I don't copy the code :-) [Note: I have no idea how much people have done on this already. It's just that all this talk of automatic generation makes me curious. Myself, I have no idea how VC makefile work.] Is it so hard to automatically generate the necessary info? On a clean source tree, make -n will dump all the commands required to complete the build. You could probably extract all the info you required from there, although the directory changing would kill you. So my thought is, create a number of tracing scripts, eg cc-trace which examine their arguments to see what needs to be done, recording the current directory and such. Then execute: make CC=cc-trace LD=ld-trace etc... And you should be able to build up a tree of what depends on what. This doesn't take care of the other stuff the makefile does though (like the generation of pg_config_paths.h, can VC makefile do things like that?) Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio MSVC
You mean they have a tool that parses GNU Makefiles and generate VC project files? Sure, that might be interesting. I've seen I think two others, and tried, but they fell over badly because the pg build system was too complicated. But I beleive I'm still allowed to loko at GPL stuff and get ideas as long as I don't copy the code :-) [Note: I have no idea how much people have done on this already. It's just that all this talk of automatic generation makes me curious. Myself, I have no idea how VC makefile work.] Is it so hard to automatically generate the necessary info? No. Not really :-) On a clean source tree, make -n will dump all the commands required to complete the build. You could probably extract all the info you required from there, although the directory changing would kill you. Can't do that on a clean source tree, you need to ./configure first. So my thought is, create a number of tracing scripts, eg cc-trace which examine their arguments to see what needs to be done, recording the current directory and such. Then execute: make CC=cc-trace LD=ld-trace etc... And you should be able to build up a tree of what depends on what. This doesn't take care of the other stuff the makefile does though (like the generation of pg_config_paths.h, can VC makefile do things like that?) Again, this requires msys and GNU make to start with. I want it to work without those completely. And the script I have now does this just fine, it just needs some cleanup work and the removing of a couple of hardcoded things. A lot of space in it right now is because I list each project manually in the beginning (postgres, one for each binary in bin/ etc) because the makefiles aren't entirely consistent for that. And it also gorws a bit because the conversion procs are built a different way... //Magnus ---(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
[HACKERS] 8.1.3 and unused files
Am I correct in the thought that the various files listed below are not
used by the database and can be safely removed? There were no other
active db connections when I issued this command.
I think truncate (Slony) left them behind.
ssdb=# select file
from pg_ls_dir('base/'|| (select oid from pg_database where datname =
'ssdb')) as tab(file)
where file !~ '\\..*$'
and file not in (select relfilenode from pg_class)
and file not in ('PG_VERSION', 'pgsql_tmp');
file
-
1434986
1434984
1434985
(3 rows)
[EMAIL PROTECTED] 16384]# ls -la 143498[456]*
-rw--- 1 rbt sysadmin 1073741824 May 1 20:56 1434984
-rw--- 1 rbt sysadmin 1073741824 May 1 21:11 1434984.1
-rw--- 1 rbt sysadmin 1073741824 May 1 23:21 1434984.10
-rw--- 1 rbt sysadmin 1073741824 May 1 23:36 1434984.11
-rw--- 1 rbt sysadmin 1073741824 May 1 23:50 1434984.12
-rw--- 1 rbt sysadmin 1073741824 May 2 00:06 1434984.13
-rw--- 1 rbt sysadmin 1073741824 May 2 00:23 1434984.14
-rw--- 1 rbt sysadmin 1073741824 May 2 00:39 1434984.15
-rw--- 1 rbt sysadmin 1073741824 May 2 00:57 1434984.16
-rw--- 1 rbt sysadmin 1073741824 May 2 01:14 1434984.17
-rw--- 1 rbt sysadmin 1073741824 May 2 01:31 1434984.18
-rw--- 1 rbt sysadmin 1073741824 May 2 01:50 1434984.19
-rw--- 1 rbt sysadmin 1073741824 May 1 21:25 1434984.2
-rw--- 1 rbt sysadmin 1073741824 May 2 02:07 1434984.20
-rw--- 1 rbt sysadmin 1073741824 May 2 02:23 1434984.21
-rw--- 1 rbt sysadmin 1073741824 May 2 02:41 1434984.22
-rw--- 1 rbt sysadmin 1073741824 May 2 02:55 1434984.23
-rw--- 1 rbt sysadmin 1073741824 May 2 03:09 1434984.24
-rw--- 1 rbt sysadmin 1073741824 May 2 03:24 1434984.25
-rw--- 1 rbt sysadmin 1073741824 May 2 03:37 1434984.26
-rw--- 1 rbt sysadmin 1073741824 May 2 03:53 1434984.27
-rw--- 1 rbt sysadmin 1073741824 May 2 04:09 1434984.28
-rw--- 1 rbt sysadmin 1073741824 May 2 04:24 1434984.29
-rw--- 1 rbt sysadmin 1073741824 May 1 21:40 1434984.3
-rw--- 1 rbt sysadmin 1073741824 May 2 04:40 1434984.30
-rw--- 1 rbt sysadmin 1073741824 May 2 04:56 1434984.31
-rw--- 1 rbt sysadmin 990912512 May 2 05:09 1434984.32
-rw--- 1 rbt sysadmin 1073741824 May 1 21:54 1434984.4
-rw--- 1 rbt sysadmin 1073741824 May 1 22:08 1434984.5
-rw--- 1 rbt sysadmin 1073741824 May 1 22:23 1434984.6
-rw--- 1 rbt sysadmin 1073741824 May 1 22:36 1434984.7
-rw--- 1 rbt sysadmin 1073741824 May 1 22:52 1434984.8
-rw--- 1 rbt sysadmin 1073741824 May 1 23:07 1434984.9
-rw--- 1 rbt sysadmin8192 May 1 20:40 1434985
-rw--- 1 rbt sysadmin 1073741824 May 2 11:27 1434986
-rw--- 1 rbt sysadmin 1073741824 May 2 11:39 1434986.1
-rw--- 1 rbt sysadmin 121733120 May 2 16:53 1434986.10
-rw--- 1 rbt sysadmin 1073741824 May 2 11:56 1434986.2
-rw--- 1 rbt sysadmin 1073741824 May 2 12:15 1434986.3
-rw--- 1 rbt sysadmin 1073741824 May 2 12:43 1434986.4
-rw--- 1 rbt sysadmin 1073741824 May 2 13:15 1434986.5
-rw--- 1 rbt sysadmin 1073741824 May 2 13:53 1434986.6
-rw--- 1 rbt sysadmin 1073741824 May 2 14:35 1434986.7
-rw--- 1 rbt sysadmin 1073741824 May 2 15:38 1434986.8
-rw--- 1 rbt sysadmin 1073741824 May 2 16:53 1434986.9
--
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio MSVC
VC++6.0 isn't a very good compiler and it's not very compatible with gcc, while Visual Studio 2005 compiler is much more compatible and has a better optimizer. Plus, VC++6.0 had a closed proprietary data format for .dsp and .dsw files, while the current Visual Studio uses a standard XML format. Finally, Microsoft gives away (as in free, no cost) Visual C++ Express edition, which includes the current compiler. I don't see any reason we'd want to target VC++6.0. P.s. With the current Visual Studio, it's easy to add Bison and Flex custom rules, so that it automatically calls them for .y and .l files. Right. It can be done. It's not quite that easy though - we need to rename the output files as well, the default names generated by bison/flex won't work. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1.3 and unused files
Rod Taylor [EMAIL PROTECTED] writes: Am I correct in the thought that the various files listed below are not used by the database and can be safely removed? There were no other active db connections when I issued this command. I think truncate (Slony) left them behind. I don't particularly like the casual assumption that truncate is broken. If I were you I'd be looking harder for a plausible explanation about where these files came from, especially seeing how large they are. Have you tried dumping the file contents to see if the data looks recognizable at all? 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] 8.1.3 and unused files
On Fri, 2006-05-05 at 14:09 -0400, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: Am I correct in the thought that the various files listed below are not used by the database and can be safely removed? There were no other active db connections when I issued this command. I think truncate (Slony) left them behind. I don't particularly like the casual assumption that truncate is broken. If I were you I'd be looking harder for a plausible explanation about where these files came from, especially seeing how large they are. Have you tried dumping the file contents to see if the data looks recognizable at all? Hardware is perfectly functional and has been for about 18 months in production with 8.0.x. It is a completely new 8.1 database and Slony is the only entity that has been working in it. There are not very many possibilities. I'm fairly confident I know exactly which table they are/were a part of. 1434984 is the table data, 1434986 is the primary key of the table (only index), and 1434985 is probably the toast structure. The structure have different relfilenode values and valid data at this time. At some point it must have failed in copying the data across, aborted, and restarted. So it would have been something like this: BEGIN; TRUNCATE; decouple indexes -- ask Jan; COPY; recouple indexes; REINDEX crash, abort, something else to cause a Slony to restart; reconnect BEGIN; TRUNCATE; decouple indexes -- ask Jan; COPY; recouple indexes; REINDEX; COMMIT; -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.1.3 and unused files
Rod Taylor [EMAIL PROTECTED] writes: At some point it must have failed in copying the data across, aborted, and restarted. Unless you had an actual backend crash, that's not an adequate explanation. Transaction abort does clean up created files. 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] 8.1.3 and unused files
On Fri, 2006-05-05 at 14:31 -0400, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: At some point it must have failed in copying the data across, aborted, and restarted. Unless you had an actual backend crash, that's not an adequate explanation. Transaction abort does clean up created files. The only reason I noticed is because pg_database_size didn't match sum(pg_total_relation_size()) and was investigating what I thought was a bug in one of those functions. I'm afraid we don't have all of the monitoring, logging, and change control bits hooked up to the non-production DBs, so that is pretty much all I have other than conjecture. The only thing I can come up with is that perhaps someone forcefully gave it a kick. SIGTERM is a necessary action once in a while to unwedge a stuck db connection (killing the client script doesn't always get it immediately). Slony holds open a transaction on the master while reindexing the slave, so perhaps someone thought the slave needed help. Making a copy of the master takes several weeks. They may have killed slony, found the statements still working away, SIGTERM'd them both, then restarted slony. It wouldn't be an unusual pattern of events, particularly since they've not been taught about pg_cancel_backend() yet (no 8.1 training). How about this? BEGIN; TRUNCATE; COPY; REINDEX SIGTERM during REINDEX; pg_class references old files. New files in their aborted state are left behind? -- ---(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] 8.1.3 and unused files
Rod Taylor [EMAIL PROTECTED] writes: On Fri, 2006-05-05 at 14:31 -0400, Tom Lane wrote: Unless you had an actual backend crash, that's not an adequate explanation. Transaction abort does clean up created files. The only thing I can come up with is that perhaps someone forcefully gave it a kick. SIGTERM is a necessary action once in a while to unwedge a stuck db connection (killing the client script doesn't always get it immediately). SIGTERM wouldn't cause that either. I hope your people are not in the habit of using kill -9? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.1.3 and unused files
On Fri, 2006-05-05 at 15:10 -0400, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: On Fri, 2006-05-05 at 14:31 -0400, Tom Lane wrote: Unless you had an actual backend crash, that's not an adequate explanation. Transaction abort does clean up created files. The only thing I can come up with is that perhaps someone forcefully gave it a kick. SIGTERM is a necessary action once in a while to unwedge a stuck db connection (killing the client script doesn't always get it immediately). SIGTERM wouldn't cause that either. I hope your people are not in the habit of using kill -9? Command line records, etc. are not available, but I did track down a a snippet of logs from the backups (daily log rotation). Sorry, I didn't realize there were backups initially -- it's unusual. Appears it did crash or get killed in some way exited with exit code 1. It's a temp DB to try a different database encoding (prep for 8.1 upgrade) with production data. Is there something you would like me to try doing in an attempt to reproduce? Preferably with a smaller structure. The truncate would have happened as part of the prepareTableForCopy() call. slony%ssdb 10171 4621947 2006-05-02 05:09:40 EDTLOG: 0: duration: 30526368.316 ms statement: select _ssrep.prepareTableForCopy(1010); copy SNIP from stdin; slony%ssdb 10171 4621947 2006-05-02 05:09:40 EDTLOCATION: exec_simple_query, postgres.c:1103 slony%ssdb 10171 4621947 2006-05-02 05:09:40 EDTLOCATION: exec_simple_query, postgres.c:1103 slony%ssdb 10181 0 2006-05-02 15:32:06 EDTLOG: 08P01: unexpected EOF on client connection slony%ssdb 10181 0 2006-05-02 15:32:06 EDTLOCATION: SocketBackend, postgres.c:295 slony%ssdb 10154 0 2006-05-02 15:32:06 EDTLOG: 08P01: unexpected EOF on client connection slony%ssdb 10154 0 2006-05-02 15:32:06 EDTLOCATION: SocketBackend, postgres.c:295 slony%ssdb 10173 0 2006-05-02 16:30:53 EDTLOG: 08P01: unexpected EOF on client connection slony%ssdb 10173 0 2006-05-02 16:30:53 EDTLOCATION: SocketBackend, postgres.c:295 slony%ssdb 10755 0 2006-05-02 16:30:53 EDTLOG: 08P01: unexpected EOF on client connection slony%ssdb 10755 0 2006-05-02 16:30:53 EDTLOCATION: SocketBackend, postgres.c:295 slony%ssdb 300 0 2006-05-02 16:55:18 EDTLOG: 08P01: unexpected EOF on client connection slony%ssdb 300 0 2006-05-02 16:55:18 EDTLOCATION: SocketBackend, postgres.c:295 slony%ssdb 301 0 2006-05-02 16:55:18 EDTLOG: 08P01: unexpected EOF on client connection slony%ssdb 301 0 2006-05-02 16:55:18 EDTLOCATION: SocketBackend, postgres.c:295 % 1960 2006-05-02 17:03:19 EDTLOG: 0: server process (PID 10171) exited with exit code 1 % 1960 2006-05-02 17:03:19 EDTLOCATION: LogChildExit, postmaster.c:2416 % 1960 2006-05-02 17:03:19 EDTLOG: 0: terminating any other active server processes % 1960 2006-05-02 17:03:19 EDTLOCATION: HandleChildCrash, postmaster.c:2306 % 1960 2006-05-02 17:03:19 EDTLOCATION: HandleChildCrash, postmaster.c:2306 % 1960 2006-05-02 17:03:19 EDTLOG: 0: all server processes terminated; reinitializing % 1960 2006-05-02 17:03:19 EDTLOCATION: reaper, postmaster.c:2206 snip connection attempts % 5826 2006-05-02 17:03:22 EDTLOG: 0: database system was interrupted at 2006-05-02 16:06:20 EDT % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4374 % 5826 2006-05-02 17:03:22 EDTLOG: 0: checkpoint record is at 59/E0B56920 % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4442 % 5826 2006-05-02 17:03:22 EDTLOG: 0: redo record is at 59/E0B56920; undo record is at 0/0; shutdown FALSE % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4469 % 5826 2006-05-02 17:03:22 EDTLOG: 0: next transaction ID: 4863932; next OID: 1441853 % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4472 % 5826 2006-05-02 17:03:22 EDTLOG: 0: next MultiXactId: 1; next MultiXactOffset: 0 % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4475 % 5826 2006-05-02 17:03:22 EDTLOG: 0: database system was not properly shut down; automatic recovery in progress % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4532 % 5826 2006-05-02 17:03:22 EDTLOG: 0: redo starts at 59/E0B56970 % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4569 % 5826 2006-05-02 17:03:22 EDTLOG: 0: record with zero length at 59/E0E429B8 % 5826 2006-05-02 17:03:22 EDTLOCATION: ReadRecord, xlog.c:2764 % 5826 2006-05-02 17:03:22 EDTLOG: 0: redo done at 59/E0E42988 % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4627 % 5826 2006-05-02 17:03:23 EDTLOG: 0: database system is ready % 5826 2006-05-02 17:03:23 EDTLOCATION: StartupXLOG, xlog.c:4821 % 5826 2006-05-02 17:03:23 EDTLOG: 0: transaction ID wrap limit is 1073749769, limited by database ssdb % 5826 2006-05-02 17:03:23 EDTLOCATION: SetTransactionIdLimit, varsup.c:234 -- ---(end of
Re: [HACKERS] 8.1.3 and unused files
Rod Taylor [EMAIL PROTECTED] writes: % 1960 2006-05-02 17:03:19 EDTLOG: 0: server process (PID 10171) exited with exit code 1 Hm. I wonder if there are any uses of exit(1) in the Slony triggers. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1.3 and unused files
On Friday 05 May 2006 13:11, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: % 1960 2006-05-02 17:03:19 EDTLOG: 0: server process (PID 10171) exited with exit code 1 Hm. I wonder if there are any uses of exit(1) in the Slony triggers. No, there are no calls to exit() in the database loaded funcs. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Darcy Buskermolen Wavefire Technologies Corp. http://www.wavefire.com ph: 250.717.0200 fx: 250.763.1759 ---(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] Transactions per second
Ühel kenal päeval, N, 2006-05-04 kell 17:23, kirjutas Jim Nasby: I often find myself wanting to know how many transactions per second a database is committing to disk, as well as how many queries per second it's processing. While Larry's busy making stats changes, I'd like to propose a few more counters: Number of commits: Ideally, this would only count transactions that actually modify data I' prefer one counter for total and one for data modifying statements. Number of statements: Simply, how many statements have been executed Number of DML statements: how many insert/update/delete statements executed. I'd like to add a request for function call counters, presented to user as view pg_stat_user_functions, similar in content to current pg_stat_user_tables. actually I'd like to have the following data gathered for each function: call count total call time min running time max running time I'd also like a possibility to gather information about usage of locks for both function calls and simple DML statements. Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com NOTICE: This communication contains privileged or other confidential information. If you have received it in error, please advise the sender by reply email and immediately delete the message and any attachments without copying or disclosing the contents. ---(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: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio
-Original Message- From: Chuck McDevitt[EMAIL PROTECTED] Sent: 05/05/06 18:01:49 To: Magnus Hagander[EMAIL PROTECTED], Gurjeet Singh[EMAIL PROTECTED], pgsql-hackers@postgresql.orgpgsql-hackers@postgresql.org, [EMAIL PROTECTED][EMAIL PROTECTED] Subject: Re: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio I don't see any reason we'd want to target VC++6.0. The runtimes ship with every platform we support, unlike v7 or v8. Regards, Dave -Unmodified Original Message- VC++6.0 isn't a very good compiler and it's not very compatible with gcc, while Visual Studio 2005 compiler is much more compatible and has a better optimizer. Plus, VC++6.0 had a closed proprietary data format for .dsp and .dsw files, while the current Visual Studio uses a standard XML format. Finally, Microsoft gives away (as in free, no cost) Visual C++ Express edition, which includes the current compiler. I don't see any reason we'd want to target VC++6.0. P.s. With the current Visual Studio, it's easy to add Bison and Flex custom rules, so that it automatically calls them for .y and .l files. -Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] Sent: Friday, May 05, 2006 12:42 AM To: Gurjeet Singh; pgsql-hackers@postgresql.org; [EMAIL PROTECTED]; Chuck McDevitt Subject: RE: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio MSVC Hi William(uniware), Chuck and Hackers, I have been interested in doing complete PGSQL development in MSVC for a long time now. With reference to one of Chuck's mails to -hackers-win32 with the same subject, you said that you were able to successfully compile PG 8.1 with some minor tweaks. Also, William has 'vcproject' hosted on pgfoundry, I downloaded it, and tried compiling vcproject\msvc\postgres\postgres.dsw on VC++6.0. It failed miserably with over 1000 errors. I am sure there's some tweaks needed here too!!! Yes. There is a patch pending on -patches which fix almost all of these in HEAD. (There are a few tiny things related to perl and NLS that aren't included in it ATM. And I'm just assuming you're seeing the same problems as I was but I didn't base my work off vcproject). I'm also working on a buildscript to convert the Makefiles to visual c++ project files, but that's not quite done yet. The idea with this work is to have the stuff as integrated as possible with main CVS, so the maintenance will be as low as possible - unlike the vcproject project which has been focusing on keeping a separate build environment maintained. The target is VC++ 2003 and 2005 ATM, but it should just be a matter of a different output format for VC 6.0 I guess. You will still need things like bison and flex if you want to build off cvs, of course - there is no builtin support for that in VC++. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.1.3 and unused files
On Fri, 2006-05-05 at 16:11 -0400, Tom Lane wrote:
Rod Taylor [EMAIL PROTECTED] writes:
% 1960 2006-05-02 17:03:19 EDTLOG: 0: server process (PID 10171)
exited with exit code 1
Hm. I wonder if there are any uses of exit(1) in the Slony triggers.
It doesn't appear so. It does have this though:
Datum
_Slony_I_killBackend(PG_FUNCTION_ARGS)
{
int32 pid;
int32 signo;
text *signame;
if (!superuser())
elog(ERROR, Slony-I: insufficient privilege for killBackend);
pid = PG_GETARG_INT32(0);
signame = PG_GETARG_TEXT_P(1);
if (VARSIZE(signame) == VARHDRSZ + 4
memcmp(VARDATA(signame), NULL, 0) == 0)
{
signo = 0;
}
else if (VARSIZE(signame) == VARHDRSZ + 4
memcmp(VARDATA(signame), TERM, 0) == 0)
{
signo = SIGTERM;
}
else
{
elog(ERROR, Slony-I: unsupported signal);
}
if (kill(pid, signo) 0)
PG_RETURN_INT32(-1);
PG_RETURN_INT32(0);
}
--
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Transactions per second
On Sat, May 06, 2006 at 12:09:45AM +0300, Hannu Krosing wrote: ??hel kenal p??eval, N, 2006-05-04 kell 17:23, kirjutas Jim Nasby: I often find myself wanting to know how many transactions per second a database is committing to disk, as well as how many queries per second it's processing. While Larry's busy making stats changes, I'd like to propose a few more counters: Number of commits: Ideally, this would only count transactions that actually modify data I' prefer one counter for total and one for data modifying statements. The reason I added in a transaction counter is because that's the only thing that tells you about the fsync rate on the WAL. Number of statements: Simply, how many statements have been executed Number of DML statements: how many insert/update/delete statements executed. I'd like to add a request for function call counters, presented to user as view pg_stat_user_functions, similar in content to current pg_stat_user_tables. actually I'd like to have the following data gathered for each function: call count total call time min running time max running time Wouldn't capturing timing statistics for short-running functions be too prohibitive? I'm thinking this is similar to the overheads we see with EXPLAIN ANALYZE... I'd also like a possibility to gather information about usage of locks for both function calls and simple DML statements. What do you mean by 'usage of locks'? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1.3 and unused files
Rod Taylor [EMAIL PROTECTED] writes: On Fri, 2006-05-05 at 16:11 -0400, Tom Lane wrote: Hm. I wonder if there are any uses of exit(1) in the Slony triggers. It doesn't appear so. It does have this though: Well, a SIGTERM would have resulted in a bleat in the postmaster log. The striking thing about your log is that the backend went down without saying a word; which would be understandable if it had crashed (eg SEGV or kill -9) but then the postmaster would have seen some other exit status. I'm fairly certain there are no paths in the standard backend code that will exit(1) without any attempt to print a message. That's why I'm wondering about add-ons. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] XLOG_BLCKSZ vs. wal_buffers table
On Tue, 02 May 2006 10:52:38 +0100 Simon Riggs [EMAIL PROTECTED] wrote: On Sun, 2006-04-30 at 22:14 -0700, Mark Wong wrote: I would have gotten this out sooner but I'm having trouble with our infrastructure. Here's a link to a table of data I've started putting together regarding XLOG_BLCKSZ and wal_buffers on a 4-way Opteron system: http://developer.osdl.org/markw/pgsql/xlog_blcksz.html There are a couple of holes in the table but I think it shows enough evidence to say that with dbt2 having a larger XLOG_BLCKSZ improves the overall throughput of the test. I'm planning on continuing to increase XLOG_BLCKSZ and wal_buffers to determine when the throughput starts to level out or drop off, and then start experimenting with varying BLCKSZ. Let me know if there are other things that would be more interesting to experiment with first. IMHO you should be testing with higher wal_buffers settings. ISTM likely that the improved performance is due to there being more buffer space, rather than actually improving I/O. Setting wal_buffers to something fairly high say 4096 would completely remove any such effect so we are left with a view on the I/O. I ran another few tests at the 600 scale factor just in case I was getting close to peaking at 500 warehouses. (Link above has updated data.) With wal_buffers set to 4096 the difference between 2048, 8192, and 32768 seem negligible. Some of the disks are at 90% utilization so perhaps I need to take a close look to make sure none of the other system resources are pegged. Thanks, Mark ---(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: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio
Statically link the c library, and your problem is solved. Or ship the dll with the installer, like any normal commercial application would. Microsoft specifically grants the right to do this. There is no other runtime besides the c library. Either approach is simple, and doesn't tie us to an ancient compiler (Well, it came out in the end of 1998, and I consider it ancient). -Original Message- From: Dave Page [mailto:[EMAIL PROTECTED] Sent: Friday, May 05, 2006 3:12 PM To: Chuck McDevitt; [EMAIL PROTECTED]; [EMAIL PROTECTED]; pgsql- [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio -Original Message- From: Chuck McDevitt[EMAIL PROTECTED] Sent: 05/05/06 18:01:49 To: Magnus Hagander[EMAIL PROTECTED], Gurjeet Singh[EMAIL PROTECTED], pgsql-hackers@postgresql.orgpgsql- [EMAIL PROTECTED], [EMAIL PROTECTED][EMAIL PROTECTED] Subject: Re: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio I don't see any reason we'd want to target VC++6.0. The runtimes ship with every platform we support, unlike v7 or v8. Regards, Dave -Unmodified Original Message- VC++6.0 isn't a very good compiler and it's not very compatible with gcc, while Visual Studio 2005 compiler is much more compatible and has a better optimizer. Plus, VC++6.0 had a closed proprietary data format for .dsp and .dsw files, while the current Visual Studio uses a standard XML format. Finally, Microsoft gives away (as in free, no cost) Visual C++ Express edition, which includes the current compiler. I don't see any reason we'd want to target VC++6.0. P.s. With the current Visual Studio, it's easy to add Bison and Flex custom rules, so that it automatically calls them for .y and .l files. -Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] Sent: Friday, May 05, 2006 12:42 AM To: Gurjeet Singh; pgsql-hackers@postgresql.org; [EMAIL PROTECTED]; Chuck McDevitt Subject: RE: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio MSVC Hi William(uniware), Chuck and Hackers, I have been interested in doing complete PGSQL development in MSVC for a long time now. With reference to one of Chuck's mails to -hackers-win32 with the same subject, you said that you were able to successfully compile PG 8.1 with some minor tweaks. Also, William has 'vcproject' hosted on pgfoundry, I downloaded it, and tried compiling vcproject\msvc\postgres\postgres.dsw on VC++6.0. It failed miserably with over 1000 errors. I am sure there's some tweaks needed here too!!! Yes. There is a patch pending on -patches which fix almost all of these in HEAD. (There are a few tiny things related to perl and NLS that aren't included in it ATM. And I'm just assuming you're seeing the same problems as I was but I didn't base my work off vcproject). I'm also working on a buildscript to convert the Makefiles to visual c++ project files, but that's not quite done yet. The idea with this work is to have the stuff as integrated as possible with main CVS, so the maintenance will be as low as possible - unlike the vcproject project which has been focusing on keeping a separate build environment maintained. The target is VC++ 2003 and 2005 ATM, but it should just be a matter of a different output format for VC 6.0 I guess. You will still need things like bison and flex if you want to build off cvs, of course - there is no builtin support for that in VC++. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.1.3 and unused files
On Fri, 2006-05-05 at 18:53 -0400, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: On Fri, 2006-05-05 at 16:11 -0400, Tom Lane wrote: Hm. I wonder if there are any uses of exit(1) in the Slony triggers. It doesn't appear so. It does have this though: Well, a SIGTERM would have resulted in a bleat in the postmaster log. The striking thing about your log is that the backend went down without saying a word; which would be understandable if it had crashed (eg SEGV or kill -9) but then the postmaster would have seen some other exit status. I'm fairly certain there are no paths in the standard backend code that will exit(1) without any attempt to print a message. That's why I'm wondering about add-ons. Add-ons are slim. Slony. We don't have any C based functions and only a few plpgsql functions in that DB. I did trim out a ton of autovacuum log entries (it likes to log once a minute) but I don't see anything interesting in that area nor autovac the pid that exited. My knowledge of signal handling is pretty basic. Any chance that multiple SIGTERMs could have caused it to avoid the logging? -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
