Re: [HACKERS] thread safety tests
Bruce Momjian [EMAIL PROTECTED] writes: Are people OK with requiring PGUSER, $USER, $LOGNAME, or the username to be supplied by the connection string in libpq on platforms that want threads and don't have getpwuid_r() (Solaris, FreeBSD, etc.)? AFAICS that was not what Jan was suggesting at all. I don't like it either --- changing the user-visible behavior based on whether we think the platform is thread-safe or not is horrid. What I understood Jan to be saying is that we should be willing to build the most thread-safe approximation we can when --enable-thread-safety is requested. Don't bomb out if you don't have getpwuid_r, just give a warning and then use getpwuid. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] serverlog function (log_destination file)
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Looks good to me. The only issue I saw was that the default file name mentioned in postgresql.conf doesn't match the actual default. I'm really not happy with the concept that the postmaster overrides its stderr direction. Me either without more thought. If we start logging to a file explicitly, do we need to revisit the log rotation discussion which seems to have gone nowhere several times recently? cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Improving postgresql.conf
On Wed, Jun 09, 2004 at 09:13:05PM +0530, Shridhar Daithankar wrote: Well that is easy. In the service file just say [Cluster1] datapath=/data/foo [Cluster2] datapath=/data/foo1 and postgresql.conf could still reside inside each cluster to provide specific configuration. Thenhave a script which can say 'service postgresql cluster1 start' This is awfull way of doing configuration. Why should different installation share anything, in one file? Running /usr/bin/pg_ctl -C /etc/postgres.isp1.conf start seems much more maintainable. And /etc/postgres.isp1.conf can specify that the data files are in /bigdisk/data/isp1x or wherever you please. Postgresql as a database server is a service. A cluster is an service instance. A service configuration file documents all service instances and their parameters required for all tuning and control purposes. Add a possibility of multiple versions of postgresql on same box. That sums it up One file does not add possibility of multiple versions of postgresql on same box, it merely makes it harder. Well, I wish I could have some archives link handy but suffice to say that Tom has rejected this idea many times before.. That does not necessarily mean the idea is broken. Tom's main objection (IIRC) was that he needs to be able to have multiple postgresqls on one machine. That can easily be achieved, either by specifying datadirectory in the configuration file, or even defaulting to the same directory where the .conf file is stored when no datadirectory option is used. -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] thread safety tests
On 6/10/2004 2:11 AM, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Are people OK with requiring PGUSER, $USER, $LOGNAME, or the username to be supplied by the connection string in libpq on platforms that want threads and don't have getpwuid_r() (Solaris, FreeBSD, etc.)? AFAICS that was not what Jan was suggesting at all. I don't like it either --- changing the user-visible behavior based on whether we think the platform is thread-safe or not is horrid. What I understood Jan to be saying is that we should be willing to build the most thread-safe approximation we can when --enable-thread-safety is requested. Don't bomb out if you don't have getpwuid_r, just give a warning and then use getpwuid. Make it so that --enable-thread-safety bombs out, but make another --enable-thread-safey-anyway work the way Tom descibed it. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] I/O support for composite types
regression=# insert into bar values (row(row(1.1, 2.2), row(3.3, 4.4))); BTW, I forgot to mention that the keyword ROW is optional as long as you've got at least two items in the row expression, so the above can be simplified to regression=# insert into bar values (((1.1, 2.2), (3.3,4.4))); INSERT 155011 1 Some other examples: regression=# select (1,2)::complex; ERROR: output of composite types not implemented yet regression=# select cast ((1,2) as complex); ERROR: output of composite types not implemented yet Looking at these, it does seem like it would be natural to get back complex - (1,2) so I'll now agree with you that the I/O syntax should use parens not braces as the outer delimiters. Following this path, perhaps the array i/o syntax should be changed to use []s and the keyword ARRAY should likewise be optional in the array constructor. That would let people do things like insert into bar values ([(1,2),(2,3)]) to insert a list of point/complex data structures. and get back '[(1,2),(2,3)]' in their dumps. Personally I would have been more inclined to use braces for structs in both places. And either parens or brackets for arrays. But eh. This whole thing is just too cool to worry about the choice of delimiters. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Out of space situation and WAL log pre-allocation (was
Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes: Maybe specify an archive location (that of course could be on a separate partition) that the external archiver should check in addition to the normal WAL location. At some predetermined interval, push WAL log segments no longer needed to the archive location. Does that really help? The panic happens when you fill the normal and archive partitions, how's that different from one partition? I see your point. But it would allow you to use a relatively modest local partition for WAL segments, while you might be using a 1TB netapp tray over NFS for the archive segments. I guess if the archive partition fills up, I would err on the side of dropping archive segments on the floor. That would mean a new full backup would be needed, but at least it wouldn't result in a corrupt, or shut down, database. Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: My feeling is that we need not support tablespaces on OS's without symlinks. Agreed, but are we going to support non-tablespace installs? I wasn't sure that was an option. A setup containing only the default tablespace cannot use any symlinks. That doesn't seem hard though. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] simple make check failures
Bruce Momjian wrote: ! *** If you are going to modify the lexer files or build from CVS, the installed ! *** version of Bison is too old. Bison version 1.875 or later is required.]) Bison has nothing to do with the lexer files. ---(end of broadcast)--- TIP 3: 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] simple make check failures
Peter Eisentraut wrote: Bruce Momjian wrote: ! *** If you are going to modify the lexer files or build from CVS, the installed ! *** version of Bison is too old. Bison version 1.875 or later is required.]) Bison has nothing to do with the lexer files. Oops, sorry, new text: *** If you are going to modify the grammar files or build from CVS, the installed *** version of Bison is too old. Bison version 1.875 or later is required.]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Prepared queries and portals
While adapting an application to make use of the new protocol, I've faced one problem. I cannot execute a prepared query and fetch the result in several time. The multiple fetch is accessible with cursor in SQL but I haven't found any way to execute a prepared query in a cursor. The documentation clearly state that the protocol support this beahvior, so I've modified libpq to handle the case by adding to functions : PQexecPreparedPortal(conn,stmtName,portalName,nParams,paramValues,paramlengt h,paramFormats,resultFormat,maxrows); and PQfetchPortal(conn,portalName,maxrows) PQexecPreparedPortal is a PQexecPrepared clone but you can specify the portal in which the result should be put (which might be the unnamed one) and the maximum number of rows to retreive after the execution. PQfetchPortal fetch the next rows. This works nicely. I would like to see this included in the standard libpq, and will submit a patch if this might be usefull, but as it extend the libpq API there might be more general plan to support the functionality. Any comments ? cyril ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Comments on patch for date_trunc( 'week', ... );
When grilled further on (Wed, 3 Mar 2004 22:40:50 -0500 (EST)), Bruce Momjian [EMAIL PROTECTED] confessed: Well, it must have hit the lists if I have put it in the patch queue, no? --- Hey Bruce, I never saw the patch hit the hackers list. Did any of you smart folks take a look at it? More emphasis on the latter, less on the former. That was the gist of the e-mail, to make sure someone else actually looked at it ;-) Later, Rob -- 20:44:41 up 18 days, 4:22, 3 users, load average: 1.04, 1.12, 1.15 Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003 pgpYmHt2hlUj8.pgp Description: PGP signature
Re: [HACKERS] Improving postgresql.conf
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 We discussed this and thought that it would end up duplicating stuff already in the docs Which is fine. Keeping some documentation in the file itself is a necessity. For example, we've changed sort_mem to work_mem. There should at the least be a note to this effect in the postgresql.conf file. Better yet, there should be a brief explanation of what each of the parameters _means_ and what each one _does_. It does not have to go into detail, but there should be enough language to remind somebody what exactly the sometimes cryptically named parameter does. The name alone is not enough. When in doubt, it is always better to err on the side of more verbose documentation. and removing the comments means that you have no way to know which are being set to non-default values. This seems a non-issue to me. The end-user does not really care so much about what is default so much as what it is right now. We are overloading the # operator, so to speak, by making it not only a documentation markup, but by making it a set default because it is commented out. What happens when somebody changes the sort_mem to something, and then comments it out to turn it back to the default? The next person looking at the file is not going to know what the setting is, because instead of the default being in the documentation part of the file, it is in the commented-out parameter, and it is now wrong. Far better to explicitly set every parameter. You can then go into the file and know exactly what each parameter is set to. Are people saying the Apache config files are easier to use? I actually find it quite hard to understand, especially httpd.conf. It is certainly well documented. You can step into it for the first time and have a relatively complete understanding of what each setting does. It's also laid out logically, but we have mostly addressed this in the last big rearrangement of postgresql.conf that happened a few months ago. One idea that has been floated around is to pull the docs automatically from SGML and put them in postgresql.conf. We do that for psql's use of \help now, so it seems this is possible. I'm not sure this is the way to go. The SGML should be more detailed, and also assumes that you are reading it in a different context than from within the configuration file. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200406100751 -BEGIN PGP SIGNATURE- iD8DBQFAyE0rvJuQZxSWSsgRAqL3AJ0eR28O8LyWV3Kn5wgMtggqJi8/nACeI/JC onWV778+vewEdBeAI+EYOkw= =/wqn -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Why hash indexes suck
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: [blink] This seems to miss out on the actual point of the thread (hash bucket size shouldn't be a disk page) in favor of an entirely unsupported sub-suggestion. Yes, I was unsure of the text myself. I have changed it to: * Allow hash buckets to fill disk pages, rather than being sparse OK, though maybe pack hash index buckets onto disk pages more efficiently would be clearer. OK, updated. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 8: explain analyze is your friend
Re: [HACKERS] Tablespaces
Thomas Swan [EMAIL PROTECTED] writes: Bruce Momjian wrote: The advantage of symlinks is that an administrator could see how things are laid out from the command line. That's a poor reason to require symlinks. The administrator can just as easily open up psql and query pg_tablespace to see that same information. Something to keep in mind here is that one of the times you would most likely need that information is when the database is broken and you *can't* simply open up psql and inspect system catalogs. I like the fact that a symlink implementation can be inspected without depending on a working database. If we were going to build a non-symlink implementation, I'd want the highlevel-to-lowlevel data transfer to take the form of a flat ASCII file that could be inspected by hand, rather than some hidden in-memory datastructure. But given the previous discussion in this thread, I cannot see any strong reason not to rely on symlinks for the purpose. We are not in the business of building replacements for OS features. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Improving postgresql.conf
Dear Greg, One idea that has been floated around is to pull the docs automatically from SGML and put them in postgresql.conf. We do that for psql's use of \help now, so it seems this is possible. I'm not sure this is the way to go. The SGML should be more detailed, and also assumes that you are reading it in a different context than from within the configuration file. As for the level defail, I guess the idea is to extract only a relevant part of the sgml doc: parameter name, summary and advices, default value. Sure the doc can contains more than that, but at least this should be available. As for the context, I think that is is factual enough so as to be able to write documentation that would fit both the doc and the configuration file. -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Improving postgresql.conf
I understand your points below. However, the group has weighed in the direction of clearly showing non-default values and not duplicating documentation. We can change that, but you will need more folks agreeing with your direction. --- Greg Sabino Mullane wrote: [ There is text before PGP section. ] [ PGP not available, raw data follows ] -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 We discussed this and thought that it would end up duplicating stuff already in the docs Which is fine. Keeping some documentation in the file itself is a necessity. For example, we've changed sort_mem to work_mem. There should at the least be a note to this effect in the postgresql.conf file. Better yet, there should be a brief explanation of what each of the parameters _means_ and what each one _does_. It does not have to go into detail, but there should be enough language to remind somebody what exactly the sometimes cryptically named parameter does. The name alone is not enough. When in doubt, it is always better to err on the side of more verbose documentation. and removing the comments means that you have no way to know which are being set to non-default values. This seems a non-issue to me. The end-user does not really care so much about what is default so much as what it is right now. We are overloading the # operator, so to speak, by making it not only a documentation markup, but by making it a set default because it is commented out. What happens when somebody changes the sort_mem to something, and then comments it out to turn it back to the default? The next person looking at the file is not going to know what the setting is, because instead of the default being in the documentation part of the file, it is in the commented-out parameter, and it is now wrong. Far better to explicitly set every parameter. You can then go into the file and know exactly what each parameter is set to. Are people saying the Apache config files are easier to use? I actually find it quite hard to understand, especially httpd.conf. It is certainly well documented. You can step into it for the first time and have a relatively complete understanding of what each setting does. It's also laid out logically, but we have mostly addressed this in the last big rearrangement of postgresql.conf that happened a few months ago. One idea that has been floated around is to pull the docs automatically from SGML and put them in postgresql.conf. We do that for psql's use of \help now, so it seems this is possible. I'm not sure this is the way to go. The SGML should be more detailed, and also assumes that you are reading it in a different context than from within the configuration file. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200406100751 -BEGIN PGP SIGNATURE- iD8DBQFAyE0rvJuQZxSWSsgRAqL3AJ0eR28O8LyWV3Kn5wgMtggqJi8/nACeI/JC onWV778+vewEdBeAI+EYOkw= =/wqn -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] [ Decrypting message... End of raw data. ] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] thread safety tests
Jan Wieck wrote: On 6/10/2004 2:11 AM, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Are people OK with requiring PGUSER, $USER, $LOGNAME, or the username to be supplied by the connection string in libpq on platforms that want threads and don't have getpwuid_r() (Solaris, FreeBSD, etc.)? AFAICS that was not what Jan was suggesting at all. I don't like it either --- changing the user-visible behavior based on whether we think the platform is thread-safe or not is horrid. What I understood Jan to be saying is that we should be willing to build the most thread-safe approximation we can when --enable-thread-safety is requested. Don't bomb out if you don't have getpwuid_r, just give a warning and then use getpwuid. Make it so that --enable-thread-safety bombs out, but make another --enable-thread-safey-anyway work the way Tom descibed it. Sure, we can do that by just not running the thread_test program. In fact a cross-compile already skips running the test. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: Don't 'kill -9' the postmaster
[HACKERS] ecpg fixes
I have emailed Michael Meskes to get his help in resolving open ecpg issues for 7.4.X. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] trigger inheritance on inherited tables ?
I am using table inheritance and it simplifies things for me a lot, but there is one thing that is missing: trigger inheritance (I do not mean constraint triggers). So far I wrote function that copy all non-constraint triggers from parent to child table, but I must call that function on all child tables whenever I change something in the parent (and there are more than few). Any idea how to simplify this ? Regards ! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] simple_heap_update: tuple concurrently updated -- during INSERT
=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= [EMAIL PROTECTED] writes: A final question: as far as you can remember, may this be an issue already fixed in later versions? I should have looked in the code before, because indeed we have a recent bug fix addressing exactly this issue. Here's the commit message: 2003-09-15 19:33 tgl * src/: backend/access/heap/heapam.c, backend/commands/async.c, backend/executor/execMain.c, include/access/heapam.h: Fix LISTEN/NOTIFY race condition reported by Gavin Sherry. While a really general fix might be difficult, I believe the only case where AtCommit_Notify could see an uncommitted tuple is where the other guy has just unlistened and not yet committed. The best solution seems to be to just skip updating that tuple, on the assumption that the other guy does not want to hear about the notification anyway. This is not perfect --- if the other guy rolls back his unlisten instead of committing, then he really should have gotten this notify. But to do that, we'd have to wait to see if he commits or not, or make UNLISTEN hold exclusive lock on pg_listener until commit. Either of these answers is deadlock-prone, not to mention horrible for interactive performance. Do it this way for now. (What happened to that project to do LISTEN/NOTIFY in memory with no table, anyway?) This is in 7.4, but not 7.3.*. You can duplicate the failure like so (in 7.3): session one: listen foo; begin; unlisten foo; session two: notify foo; -- hangs session one: end; -- session two now says WARNING: AbortTransaction and not in in-progress state ERROR: simple_heap_update: tuple concurrently updated regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Why frequently updated tables are an issue
OK, the problem I am having with whole discussion, on several fronts, is the idea of performance. If performance and consistent behavior were not *important* issues to a project, a summary table would work fine, and I could just vacuum frequently. Currently a client needs to vacuum two summary tables at least once every two seconds. The performace of the system slowly declines with each summary update, until the next vacuum. After a vacuum, the transaction comes in at about 7ms, it increases to about 35ms~50ms, then we vacuum and we've back to 7ms. When we vacuumed every 30 seconds, it would sometimes get up to whole seconds. There is an important issue here. Yes, MVCC is good. I agree, and no one is arguing against it in a general case, however, there are classes of problems in which MVCC, or at least PostgreSQL's implementation of it, is not the best solution. There are two basic problems which are fundimental issues I've had with PostgreSQL over the years: summary tables and session tables. The summary tables take the place of a select sum(col) from table where table is very small. The amount of vacuuming required and the steady degradation of performance prior to each vacuum is a problem that could be addressed by some global variable system. The session table is a different issue, but has the same problems. You have an active website, hundreds or thousands of hits a second, and you want to manage sessions for this site. Sessions are created, updated many times, and deleted. Performance degrades steadily until a vacuum. Vacuum has to be run VERY frequently. Prior to lazy vacuum, this was impossible. Both session tables and summary tables have another thing in common, they are not vital data, they hold transitive state information. Yea, sure, data integrity is important, but if you lose these values, you can either recreate it or it isn't too important. Why put that is a database at all? Because, in the case of sessions especially, you need to access this information for other operations. In the case of summary tables, OLAP usually needs to join or include this info. PostgreSQL's behavior on these cases is poor. I don't think anyone who has tried to use PG for this sort of thing will disagree, and yes it is getting better. Does anyone else consider this to be a problem? If so, I'm open for suggestions on what can be done. I've suggested a number of things, and admittedly they have all been pretty weak ideas, but they were potentially workable. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] I/O support for composite types
Greg Stark [EMAIL PROTECTED] writes: Following this path, perhaps the array i/o syntax should be changed to use []s I would think about that if there weren't compatibility issues to worry about, but in practice the pain from such an incompatible change would vastly outweigh the benefit. and the keyword ARRAY should likewise be optional in the array constructor. Not sure this is syntactically feasible, or a good idea even if it is possible to get bison to take it --- it might foreclose more useful syntactic ideas later on. (I wouldn't think that omitting ROW is a good idea either, but the spec says we have to.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Why frequently updated tables are an issue
On Jun 10, 2004, at 10:30 AM, [EMAIL PROTECTED] wrote: Prior to lazy vacuum, this was impossible. Do you know for sure that lazy vacuum and/or autovacuum does not indeed solve / alleviate the symptoms of the general problem of very high rate table updates? Back to lurking! James Robinson Socialserve.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Why frequently updated tables are an issue
[EMAIL PROTECTED] wrote: The session table is a different issue, but has the same problems. You have an active website, hundreds or thousands of hits a second, and you want to manage sessions for this site. Sessions are created, updated many times, and deleted. Performance degrades steadily until a vacuum. Vacuum has to be run VERY frequently. Prior to lazy vacuum, this was impossible. Both session tables and summary tables have another thing in common, they are not vital data, they hold transitive state information. Yea, sure, data integrity is important, but if you lose these values, you can either recreate it or it isn't too important. Why put that is a database at all? Because, in the case of sessions especially, you need to access this information for other operations. In the case of summary tables, OLAP usually needs to join or include this info. PostgreSQL's behavior on these cases is poor. I don't think anyone who has tried to use PG for this sort of thing will disagree, and yes it is getting better. Does anyone else consider this to be a problem? If so, I'm open for suggestions on what can be done. I've suggested a number of things, and admittedly they have all been pretty weak ideas, but they were potentially workable. There is another as-of-non-feasible and hence rejected approach. Vacuum in postgresql is tied to entire relations/objects since indexes do not have transaction visibility information. It has been suggested in past to add such a visibility to index tuple header so that index and heaps can be cleaned out of order. In such a case other backround processes such as background writer and soon-to-be integrated autovacuum daemon can vacuum pages/buffers rather than relations. That way most used things will remain clean and cost of cleanup will remain outside crtical transaction processing path. However increasing index footprint seems to be a tough sell. Besides FSM would need some rework to accomodate/autotune it's behaviour. I am quoting from memory, so don't flame me if I misquote it. Just adding to make this complete. Only from performance point of view, it could solve quite some problems, at least in theory. Shridhar ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] simple_heap_update: tuple concurrently updated -- during INSERT
=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= [EMAIL PROTECTED] writes: The only thing I still don't understand is the not in in-progress state thing. At the point where it's trying to send a NOTIFY, it's partially out of its transaction --- the state has become TRANS_COMMIT instead of TRANS_INPROGRESS. Thus the warning. It's no big deal. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: My feeling is that we need not support tablespaces on OS's without symlinks. To create symlinked directories on Win2k NTFS see: http://www.sysinternals.com/ntw2k/source/misc.shtml#junction I think Win2000 or XP would be a reasonable restriction for Win32 PG installations that want tablespaces. Oh, good --- symlinks for directories are all that we need for this design. I think that settles it then. Er, sorry to drop into the middle of this but do you want to cripple a port before it is even complete? Is there a compelling reason to use symlinks rather than a flat file? If the issue is just: Gavin Sherry [EMAIL PROTECTED] writes: how the low-level file access code finds a tablespace. then what is wrong with using an XML file that is loaded and traversed at start up? I agree it would be a cool to use the file system as a database, but why place a possible limiting factor for the sake of elegance? Isn't XML a valid and accepted way to store hierarchial data? Gavin Sherry [EMAIL PROTECTED] writes: I am expecting to hear some bleating about this from people whose preferred platforms don't support symlinks ;-). However, if we don't Well bleat I guess. Although I wouldn't exactly say preferred. I prefer to think of myself as a realest getting paid to program on a platform. A platform with symlinks carrying quite a bit of baggage. On NTFS they are called Junction Points and are a special type of Reparse Point. One thing I noticed on the Microsoft site regarding these: (http://www.microsoft.com/whdc/DDK/IFSkit/reparse.mspx) Reparse Points are a powerful feature of Windows 2000 (not available on Windows NT® 4.0), but developers should be aware that there can only be one reparse point per file, and some new Windows 2000 mechanisms use reparse points (HSM, Native Structured Storage). Developers need to have fallback strategies for when the reparse point tag is already in use for a file. makes me question their usefulness at this point. I am currently exploring another solution to the problem that caused me to investigate them. Well, thanks for your time. I guess I can go baaack to lurking now. ;-) Lawrence E. Smithmier, Jr. MCP, MCAD (919) 522-9738 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Sigh, 7.3.6 rewrap not right
Marc G. Fournier [EMAIL PROTECTED] writes: Okay, I've repackaged it, and temporarily put everything into /pub/source/v7.3.6_1 ... if ppl can confirm that I haven't somehow missed something again (I rm -rf'd the old build tree and re-cvs exported it, so it started clean), I'll move those files over to 7.3.6 ... We are snakebit today, for certain :-(. The repackaged main tarball has the right files, but there is something wrong with the built HTML docs (doc/postgres.tar.gz). It is only 36K and seems to contain just -rw-r--r-- pgsql/wheel 26163 2004-03-04 19:35 catalogs.gif -rw-r--r-- pgsql/wheel9485 2004-03-04 19:35 connections.gif -rw-r--r-- pgsql/wheel1151 2002-10-12 12:33 stylesheet.css In the previous wrap it was 950K ... regards, tom lane ---(end of broadcast)--- TIP 3: 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] Frequently updated tables
Mohawksoft: I don't think anyone who has seriously looked at these issues has concluded that PostgreSQL works fine in these cases. The question is what, if anything, can be done? The frequent update issue really affects PostgreSQL's acceptance in web applications, and one which MySQL seems to do a better job. I think that we'd welcome any suggestions that don't break MVCC. Do you have any? MySQL is able to handle this situation -- in MyISAM tables -- precisely because there is no transaction isolation and they regard 97% data integrity as good enough. Essentially, the MyISAM tables are little better than delimited text flatfiles. That's not an approach we can take. IMHO, this issue, a two stage commit based replication system, and a real and usable setup/configuration system are all that stands between PostgreSQL and the serious enterprise deployment. There I have to disagree with you. The features you mention may be important to your clients, but they are not to mine; instead, we're waiting for clustering, and table partitioning in addition to what's in 7.5. Please don't assume that all DB applications have the same needs as yours. The problems you raise are legitimate, but not everyone shares your priorities. Besides, we already have serious enterprise deployment. 5 of my clients are startups which run on PostgreSQL. The .ORG and .INFO domains run on PostgreSQL. There are two commerical-grade, deployed, ERP systems for manufacturers which run on PostgreSQL.What is your definition of enterprise deployment, exactly? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: 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] Improving postgresql.conf
Bruce Momjian wrote: I understand your points below. However, the group has weighed in the direction of clearly showing non-default values and not duplicating documentation. We can change that, but you will need more folks agreeing with your direction. I don't remember the behaviour but tell me what happen if I comment out a value changing the value. Kill UP the postmater. Recommenting that value and now re killing the postmaster. I believe that postmaster will not run with the default value. Who will look the configuration file will not understand the right reality. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested xacts: looking for testers and review
Am I the only one who has a hard time understanding why COMMIT in the
case of an error is allowed? Since nothing is actually committed, but
instead everything was actually rolled back. Isn't it misleading to
allow a commit under these circumstances?
Then to further extend the commit syntax with COMMIT WITHOUT ABORT makes
even less since, IMHO. If we are going to extend the syntax shouldn't
we be extending ROLLBACK or END, something other than COMMIT so that we
don't imply that anything was actually committed.
Perhaps I am being too literal here in reading the keyword COMMIT as
meaning that something was actually committed, instead of COMMIT simply
being end-of-transaction that may or may not have committed the changes
in that transaction. I have always looked at COMMIT and ROLLBACK as a
symmetric pair of commands - ROLLBACK - the changes in the transaction
are not committed, COMMIT - the changes in the transaction are
committed. That symmetry doesn't exist in reality since COMMIT only
means that the changes might have been committed.
--Barry
Alvaro Herrera wrote:
On Fri, May 28, 2004 at 04:05:40PM -0400, Bruce Momjian wrote:
Bruce,
One interesting idea would be for COMMIT to affect the outer
transaction, and END not affect the outer transaction. Of course that
kills the logic that COMMIT and END are the same, but it is an
interesting idea, and doesn't affect backward compatibility because
END/COMMIT behave the same in non-nested transactions.
I implemented this behavior by using parameters to COMMIT/END. I didn't
want to add new keywords to the grammar so I just picked up
COMMIT WITHOUT ABORT. (Originally I had thought COMMIT IGNORE
ERRORS but those would be two new keywords and I don't want to mess
around with the grammar. If there are different opinions, tweaking the
grammar is easy).
So the behavior I originally implemented is still there:
alvherre=# begin;
BEGIN
alvherre=# begin;
BEGIN
alvherre=# select foo;
ERROR: no existe la columna foo
alvherre=# commit;
COMMIT
alvherre=# select 1;
ERROR: transacción abortada, las consultas serán ignoradas hasta el fin de bloque de
transacción
alvherre=# commit;
COMMIT
However if one wants to use in script the behavior you propose, use
the following:
alvherre=# begin;
BEGIN
alvherre=# begin;
BEGIN
alvherre=# select foo;
ERROR: no existe la columna foo
alvherre=# commit without abort;
COMMIT
alvherre=# select 1;
?column?
--
1
(1 fila)
alvherre=# commit;
COMMIT
The patch is attached. It applies only after the previous patch,
obviously.
diff -Ncr --exclude-from=diff-ignore 10bgwriter/src/backend/access/transam/xact.c 13commitOpt/src/backend/access/transam/xact.c
*** 10bgwriter/src/backend/access/transam/xact.c 2004-06-08 17:34:49.0 -0400
--- 13commitOpt/src/backend/access/transam/xact.c 2004-06-09 12:00:49.0 -0400
***
*** 2125,2131
* EndTransactionBlock
*/
void
! EndTransactionBlock(void)
{
TransactionState s = CurrentTransactionState;
--- 2125,2131
* EndTransactionBlock
*/
void
! EndTransactionBlock(bool ignore)
{
TransactionState s = CurrentTransactionState;
***
*** 2163,2172
/*
* here we are in an aborted subtransaction. Signal
* CommitTransactionCommand() to clean up and return to the
! * parent transaction.
*/
case TBLOCK_SUBABORT:
! s-blockState = TBLOCK_SUBENDABORT_ERROR;
break;
case TBLOCK_STARTED:
--- 2163,2177
/*
* here we are in an aborted subtransaction. Signal
* CommitTransactionCommand() to clean up and return to the
! * parent transaction. If we are asked to ignore the errors
! * in the subtransaction, the parent can continue; else,
! * it has to be put in aborted state too.
*/
case TBLOCK_SUBABORT:
! if (ignore)
! s-blockState = TBLOCK_SUBENDABORT_OK;
! else
! s-blockState = TBLOCK_SUBENDABORT_ERROR;
break;
case TBLOCK_STARTED:
diff -Ncr --exclude-from=diff-ignore 10bgwriter/src/backend/parser/gram.y 13commitOpt/src/backend/parser/gram.y
*** 10bgwriter/src/backend/parser/gram.y 2004-06-03 20:46:48.0 -0400
--- 13commitOpt/src/backend/parser/gram.y 2004-06-09 11:51:04.0 -0400
***
*** 225,232
target_list update_target_list insert_column_list
insert_target_list def_list opt_indirection
group_clause TriggerFuncArgs select_limit
! opt_select_limit opclass_item_list transaction_mode_list
! transaction_mode_list_or_empty
TableFuncElementList
prep_type_clause prep_type_list
execute_param_clause
--- 225,232
target_list update_target_list insert_column_list
insert_target_list def_list opt_indirection
group_clause TriggerFuncArgs select_limit
! opt_select_limit opclass_item_list transaction_commit_opts
! transaction_mode_list
Re: [HACKERS] Nested xacts: looking for testers and review
Well, the default behavior of COMMIT for an aborted subtransaction is
that it will abort the upper transaction too, so I think this is the
behavior you want.
We are considering allowing COMMIT IGNORE ABORT for scripts that want to
do a subtransaction, but don't care if it fails, and because it is a
script, they can't test the return value to send ROLLBACK:
BEGIN;
BEGIN;
DROP TABLE test;
COMMIT
CREATE TABLE test(x int);
COMMIT;
In this case you don't care if the DROP fails, but you do it all in a
subtransaction so the visibility happens all at once.
---
Barry Lind wrote:
Am I the only one who has a hard time understanding why COMMIT in the
case of an error is allowed? Since nothing is actually committed, but
instead everything was actually rolled back. Isn't it misleading to
allow a commit under these circumstances?
Then to further extend the commit syntax with COMMIT WITHOUT ABORT makes
even less since, IMHO. If we are going to extend the syntax shouldn't
we be extending ROLLBACK or END, something other than COMMIT so that we
don't imply that anything was actually committed.
Perhaps I am being too literal here in reading the keyword COMMIT as
meaning that something was actually committed, instead of COMMIT simply
being end-of-transaction that may or may not have committed the changes
in that transaction. I have always looked at COMMIT and ROLLBACK as a
symmetric pair of commands - ROLLBACK - the changes in the transaction
are not committed, COMMIT - the changes in the transaction are
committed. That symmetry doesn't exist in reality since COMMIT only
means that the changes might have been committed.
--Barry
Alvaro Herrera wrote:
On Fri, May 28, 2004 at 04:05:40PM -0400, Bruce Momjian wrote:
Bruce,
One interesting idea would be for COMMIT to affect the outer
transaction, and END not affect the outer transaction. Of course that
kills the logic that COMMIT and END are the same, but it is an
interesting idea, and doesn't affect backward compatibility because
END/COMMIT behave the same in non-nested transactions.
I implemented this behavior by using parameters to COMMIT/END. I didn't
want to add new keywords to the grammar so I just picked up
COMMIT WITHOUT ABORT. (Originally I had thought COMMIT IGNORE
ERRORS but those would be two new keywords and I don't want to mess
around with the grammar. If there are different opinions, tweaking the
grammar is easy).
So the behavior I originally implemented is still there:
alvherre=# begin;
BEGIN
alvherre=# begin;
BEGIN
alvherre=# select foo;
ERROR: no existe la columna foo
alvherre=# commit;
COMMIT
alvherre=# select 1;
ERROR: transacci?n abortada, las consultas ser?n ignoradas hasta el fin de bloque
de transacci?n
alvherre=# commit;
COMMIT
However if one wants to use in script the behavior you propose, use
the following:
alvherre=# begin;
BEGIN
alvherre=# begin;
BEGIN
alvherre=# select foo;
ERROR: no existe la columna foo
alvherre=# commit without abort;
COMMIT
alvherre=# select 1;
?column?
--
1
(1 fila)
alvherre=# commit;
COMMIT
The patch is attached. It applies only after the previous patch,
obviously.
diff -Ncr --exclude-from=diff-ignore 10bgwriter/src/backend/access/transam/xact.c
13commitOpt/src/backend/access/transam/xact.c
*** 10bgwriter/src/backend/access/transam/xact.c2004-06-08 17:34:49.0
-0400
--- 13commitOpt/src/backend/access/transam/xact.c 2004-06-09 12:00:49.0
-0400
***
*** 2125,2131
*EndTransactionBlock
*/
void
! EndTransactionBlock(void)
{
TransactionState s = CurrentTransactionState;
--- 2125,2131
*EndTransactionBlock
*/
void
! EndTransactionBlock(bool ignore)
{
TransactionState s = CurrentTransactionState;
***
*** 2163,2172
/*
* here we are in an aborted subtransaction. Signal
* CommitTransactionCommand() to clean up and return to the
!* parent transaction.
*/
case TBLOCK_SUBABORT:
! s-blockState = TBLOCK_SUBENDABORT_ERROR;
break;
case TBLOCK_STARTED:
--- 2163,2177
/*
* here we are in an aborted subtransaction. Signal
* CommitTransactionCommand() to clean up and return to the
!* parent transaction. If we are asked to ignore the errors
!* in the subtransaction, the parent can
Re: [HACKERS] Nested xacts: looking for testers and review
Bruce Momjian [EMAIL PROTECTED] writes: We are considering allowing COMMIT IGNORE ABORT for scripts that want to do a subtransaction, but don't care if it fails, and because it is a script, they can't test the return value to send ROLLBACK: While we clearly want this functionality, I tend to agree with Barry that COMMIT IGNORE ABORT (and the other variants that have been floated) is a horrid, confusing name for it. I would suggest using END with some modifier, instead. Perhaps END [ WORK | TRANSACTION ] [ IGNORE ERRORS ] END doesn't so directly imply that you are trying to commit a failed transaction. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Nested xacts: looking for testers and review
Alvaro Herrera [EMAIL PROTECTED] writes: On Thu, Jun 10, 2004 at 03:39:14PM -0400, Tom Lane wrote: END doesn't so directly imply that you are trying to commit a failed transaction. The problem with END is how about executing it inside a PL/pgSQL function. Can we distinguish it from plpgsql's END? We're going to have to deal with that on the BEGIN side anyway. A reasonable possibility would be to require the TRANSACTION word to appear when you do it in plpgsql. Also, COMMITing an aborted main transaction is the same as ENDing it; and in fact, it's the same as ROLLBACK. Why is it more confusing for a subtransaction to behave the same? But the point here is that the behavior would *not* be the same. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested xacts: looking for testers and review
Tom Lane wisely wrote: While we clearly want this functionality, I tend to agree with Barry that COMMIT IGNORE ABORT (and the other variants that have been floated) is a horrid, confusing name for it. I would suggest using END with some modifier, instead. Perhaps END [ WORK | TRANSACTION ] [ IGNORE ERRORS ] END doesn't so directly imply that you are trying to commit a failed transaction. Would it make more sense to specify at the time the optional subtransaction is committed that it is not critical to the completion of the outer transaction? BEGIN; BEGIN; DROP TABLE foo; COMMIT NON_CRITICAL; CREATE TABLE foo (i int); COMMIT; I don't 'get' the nested transaction code, so I don't know how horrible this would be to write. It just seemed more useful, because you could specify which sub-transactions are show stoppers, and which ones aren't. Or if I'm completely off base, please forgive my intrusion. Paul ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested xacts: looking for testers and review
On Thu, Jun 10, 2004 at 03:39:14PM -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: We are considering allowing COMMIT IGNORE ABORT for scripts that want to do a subtransaction, but don't care if it fails, and because it is a script, they can't test the return value to send ROLLBACK: While we clearly want this functionality, I tend to agree with Barry that COMMIT IGNORE ABORT (and the other variants that have been floated) is a horrid, confusing name for it. I would suggest using END with some modifier, instead. Perhaps END [ WORK | TRANSACTION ] [ IGNORE ERRORS ] END doesn't so directly imply that you are trying to commit a failed transaction. The problem with END is how about executing it inside a PL/pgSQL function. Can we distinguish it from plpgsql's END? Also, COMMITing an aborted main transaction is the same as ENDing it; and in fact, it's the same as ROLLBACK. Why is it more confusing for a subtransaction to behave the same? I agree that the grammar I proposed is wrong. I guess I can ask for two words and then strcmp() them to ignore errors? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) La naturaleza, tan frágil, tan expuesta a la muerte... y tan viva ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] More vacuum.c refactoring
Near the end of repair_frag() in vacuum.c -- under the comment /* clean
moved tuples from last page in Nvacpagelist list */ -- there is code
that marks itemids as unused. Itemids affected are those referring to
tuples that have been moved off the last page.
This code is very similar to vacuum_page(). The major difference is
that vacuum_page() uses vacpage-offsets while the code in repair_frag()
looks for MOVED_OFF bits in tuple headers. AFAICS the tuples with the
MOVED_OFF bit set are exactly those referenced by vacpage-offsets.
The attached patch passes make check and make installcheck. Please
apply unless I'm missing something.
Servus
Manfred
diff -Ncr ../base/src/backend/commands/vacuum.c src/backend/commands/vacuum.c
*** ../base/src/backend/commands/vacuum.c Wed Jun 2 21:46:59 2004
--- src/backend/commands/vacuum.c Thu Jun 10 18:50:26 2004
***
*** 2288,2355
vacpage-offsets_free 0)
{
Buffer buf;
- Pagepage;
- OffsetNumberunused[BLCKSZ / sizeof(OffsetNumber)];
- OffsetNumberoffnum,
- maxoff;
- int uncnt;
- int num_tuples = 0;
buf = ReadBuffer(onerel, vacpage-blkno);
LockBuffer(buf, BUFFER_LOCK_EXCLUSIVE);
! page = BufferGetPage(buf);
! maxoff = PageGetMaxOffsetNumber(page);
! for (offnum = FirstOffsetNumber;
!offnum = maxoff;
!offnum = OffsetNumberNext(offnum))
! {
! ItemId itemid = PageGetItemId(page, offnum);
! HeapTupleHeader htup;
!
! if (!ItemIdIsUsed(itemid))
! continue;
! htup = (HeapTupleHeader) PageGetItem(page, itemid);
! if (htup-t_infomask HEAP_XMIN_COMMITTED)
! continue;
!
! /*
! ** See comments in the walk-along-page loop above, why
we
! ** have Asserts here instead of if (...) elog(ERROR).
! */
! Assert(!(htup-t_infomask HEAP_MOVED_IN));
! Assert(htup-t_infomask HEAP_MOVED_OFF);
! Assert(HeapTupleHeaderGetXvac(htup) == myXID);
!
! itemid-lp_flags = ~LP_USED;
! num_tuples++;
!
! }
! Assert(vacpage-offsets_free == num_tuples);
!
! START_CRIT_SECTION();
!
! uncnt = PageRepairFragmentation(page, unused);
!
! /* XLOG stuff */
! if (!onerel-rd_istemp)
! {
! XLogRecPtr recptr;
!
! recptr = log_heap_clean(onerel, buf, unused, uncnt);
! PageSetLSN(page, recptr);
! PageSetSUI(page, ThisStartUpID);
! }
! else
! {
! /*
!* No XLOG record, but still need to flag that XID
exists
!* on disk
!*/
! MyXactMadeTempRelUpdate = true;
! }
!
! END_CRIT_SECTION();
!
LockBuffer(buf, BUFFER_LOCK_UNLOCK);
WriteBuffer(buf);
}
--- 2288,2297
vacpage-offsets_free 0)
{
Buffer buf;
buf = ReadBuffer(onerel, vacpage-blkno);
LockBuffer(buf, BUFFER_LOCK_EXCLUSIVE);
! vacuum_page(onerel, buf, vacpage);
LockBuffer(buf, BUFFER_LOCK_UNLOCK);
WriteBuffer(buf);
}
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [HACKERS] serverlog function (log_destination file)
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Looks good to me. The only issue I saw was that the default file name mentioned in postgresql.conf doesn't match the actual default. I'm really not happy with the concept that the postmaster overrides its stderr direction. I agree, that's why I implemented it as *additional* log_destination. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [HACKERS] serverlog function (log_destination file)
Andreas Pflug wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Looks good to me. The only issue I saw was that the default file name mentioned in postgresql.conf doesn't match the actual default. I'm really not happy with the concept that the postmaster overrides its stderr direction. I agree, that's why I implemented it as *additional* log_destination. One idea would be to send a message to stderr when this option is used stating that everything is going to 'filename'. Also can we close/reopen the file on SIGHUP. My guess is we can't because of all the backends accessing the output file. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: 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] Why frequently updated tables are an issue
It has been suggested in past to add such a visibility to index tuple header so that index and heaps can be cleaned out of order. In such a case other backround It seems to me that the benefit of this wouldn't be all that impressive *when accessing the cache*, which is the problem this discussion is about. Disk access would occur more commonly with large tables, which I'll ignore. Let's say total scan time for a query on a very dirty table is 100ms. It seems safe to assume that the scan time for the index would be *roughly* half that of the heap. If visibilty could be determined by looking at just the index tuple, you'd cut you query scan time down to 50ms. When the clean table case is 7ms total scan time, the difference between 50 and 100 ms is not much of an issue; either way, it's still way to high! However increasing index footprint seems to be a tough sell. And rightly so, IMO. Glen Parker ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] serverlog function (log_destination file)
Andreas Pflug wrote: Sorry I didn't get back on this earlier, yesterday morning my internet access was literally struck by lightning, I'm running temporary hardware now. Bruce Momjian wrote: Looks good to me. The only issue I saw was that the default file name mentioned in postgresql.conf doesn't match the actual default. I'll fix the default filename difference, postgresql.log seems best. I'll add doc too. One idea would be to send a message to stderr when this option is used stating that everything is going to 'filename'. I can ereport LogFileOpen and LogFileClose, do we need this? Currently, only open problems will be reported. Actually, my idea of sending a message to stderr saying we are using a pre-configured file is so folks aren't surprised by the fact they can't see any stderr anymore. But doesn't syslog have the same issue. Maybe not, and that's why we need a message like: All standard output and standard error are going to postgresql.conf and send that to the processes standard error. Also can we close/reopen the file on SIGHUP. My guess is we can't because of all the backends accessing the output file. I'd also like it flushed in pg_logfile and pg_logfile_length, same problem; any hints welcome. I don't understand this. I was thinking of close/reopen so log files could be rotated. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 8: explain analyze is your friend
Re: [HACKERS] More vacuum.c refactoring
Manfred Koizar [EMAIL PROTECTED] writes: This code is very similar to vacuum_page(). The major difference is that vacuum_page() uses vacpage-offsets while the code in repair_frag() looks for MOVED_OFF bits in tuple headers. AFAICS the tuples with the MOVED_OFF bit set are exactly those referenced by vacpage-offsets. This does not make me comfortable. You *think* that two different bits of code are doing the same thing, so you want to hack up vacuum.c? This module is delicate code --- we've had tons of bugs there in the past --- and no I have zero confidence that passing the regression tests proves anything, because all those prior bugs passed the regression tests. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] serverlog function (log_destination file)
Bruce Momjian [EMAIL PROTECTED] writes: Actually, my idea of sending a message to stderr saying we are using a pre-configured file is so folks aren't surprised by the fact they can't see any stderr anymore. Hm? I thought we'd just established that the patch wasn't going to suppress output to stderr. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] [HACKERS] serverlog function (log_destination file)
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Actually, my idea of sending a message to stderr saying we are using a pre-configured file is so folks aren't surprised by the fact they can't see any stderr anymore. Hm? I thought we'd just established that the patch wasn't going to suppress output to stderr. Oh, I didn't see that. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] thread safety tests
On 6/10/2004 8:49 AM, Bruce Momjian wrote: Jan Wieck wrote: Make it so that --enable-thread-safety bombs out, but make another --enable-thread-safey-anyway work the way Tom descibed it. Sure, we can do that by just not running the thread_test program. In fact a cross-compile already skips running the test. That sounds good to me. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] More vacuum.c refactoring
On Thu, 10 Jun 2004 17:19:22 -0400, Tom Lane [EMAIL PROTECTED] wrote: This does not make me comfortable. I understand you, honestly. Do I read between your lines that you didn't review my previous vacuum.c refactoring patch? Please do. It'd make *me* more comfortable. You *think* that two different bits of code are doing the same thing, I see three significant differences between the code in repair_frag() and vacuum_page(). 1) vacuum_page() has Assert(vacpage-offsets_used == 0); vacpage is the last VacPage that has been inserted into Nvacpagelist. It is allocated in line 1566, offsets_used is immediately set to 0 and never changed. So this Assert(...) doesn't hurt. 2) In vacuum_page() the lp_flags are set inside a critical section. This is no problem because the clear-used-flags loop does not elog(ERROR, ...). Please correct me if I'm wrong. 3) vacuum_page() uses vacpage-offsets to locate the itemids that are to be updated. If we can show that these are the same itemids that belong to the tuples that are found by inspecting the tuple headers, then the two code snippets are equivalent. The first hint that this is the case is Assert(vacpage-offsets_free == num_tuples); So both spots expect to update the same number of itemids. What about the contents of the offsets[] array? Offset numbers are entered into this array by statements like vacpage-offsets[vacpage-offsets_free++] = offnum; in or immediately after the walk-along-page loop. These assignments are preceded either by code that sets the appropriate infomask bits or by assertions that the bits are already set appropriately. The rest (from PageRepairFragmentation to END_CRIT_SECTION) is identical. so you want to hack up vacuum.c? This module is delicate code --- we've had tons of bugs there in the past But why is it so delicate? Not only because it handles difficult problems, but also because it is written in a not very maintenance-friendly way. Before I started refactoring the code repair_frag() had more than 1100 lines and (almost) all variables used anywhere in the function were declared at function level. We cannot declare a code freeze for a module just because it is so badly written that every change is likely to break it. Someday someone will *have* to change it. Better we break it today in an effort to make the code clearer. --- and no I have zero confidence that passing the regression tests proves anything Unfortunately you are right :-( Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Postgresql JDBC-Driver
Hi, i'm using the jdbc postgresql driver. I need to fetch the oid of a just insertet row (getGeneratedKeys() feature). That' why i ask you to provide me the source code to implement this feature.It would be glad if you tell me how and where to get these sources. Thank you very much, Rudolpho Gugliotta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] More vacuum.c refactoring
On Fri, Jun 11, 2004 at 02:00:07AM +0200, Manfred Koizar wrote: If I may ... so you want to hack up vacuum.c? This module is delicate code --- we've had tons of bugs there in the past But why is it so delicate? Not only because it handles difficult problems, but also because it is written in a not very maintenance-friendly way. Before I started refactoring the code repair_frag() had more than 1100 lines and (almost) all variables used anywhere in the function were declared at function level. I agree. This code is horrid. I also agree with Tom in that this should be done with extreme caution, but it is a needed task. Maybe we could establish heavier testing for this kind of change so potential patches can be tested extensively. Concurrent vacuums with all kinds of imaginable operations (insert, updates, deletes), in tight loops, could be a start. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) No es bueno caminar con un hombre muerto ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Postgresql JDBC-Driver
On Fri, 5 Mar 2004, Rudolpho Gian-Franco Gugliotta wrote: Hi, i'm using the jdbc postgresql driver. I need to fetch the oid of a just insertet row (getGeneratedKeys() feature). That' why i ask you to provide me the source code to implement this feature.It would be glad if you tell me how and where to get these sources. The driver source is included in the main source tree for the 7.4 series, but for the upcoming 7.5 release the driver is being developed independently here: http://gborg.postgresql.org/project/pgjdbc/projdisplay.php Some discussion of the problems with implementing getGeneratedKeys is here: http://archives.postgresql.org/pgsql-jdbc/2003-12/threads.php#00193 Finally you don't necessarily need to implement getGeneratedKeys if you don't mind using some pg specific code along the lines of the following: Statement stmt = conn.createStatement(); stmt.executeUpdate(INSERT INTO t VALUES (1)); long oid = ((org.postgresql.PGStatement)stmt).getLastOID(); Kris Jurka ---(end of broadcast)--- TIP 9: 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] Tablespaces
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Zeugswetter Andreas SB SD Sent: Friday, March 05, 2004 1:20 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: Bruce Momjian; Tom Lane; Greg Stark; [EMAIL PROTECTED]; PostgreSQL Win32 port list Subject: Re: [pgsql-hackers-win32] [HACKERS] Tablespaces First of all, symlinks are a pretty popular feature. Even Windows supports what would be needed. Second of all, PostgreSQL will still run on OSes without symlinks, tablespaces won't be available, but PostgreSQL will still run. Since we are all using PostgreSQL without My idea for platforms that don't support symlinks would be to simply create a tblspaceoid directory inplace instead of the symlink (maybe throw a warning). My feeling is, that using the same syntax on such platforms is important, but actual distribution is not (since they will most likely be small systems). I know of bot SQL*Server and Oracle database systems on Win32 with hundreds of millions of rows and many hundreds of gigabytes of space. These are production systems, run by fortune 500 companies. I expect that PostgreSQL systems on Win32 will have multiple 64-bit CPU systems, with 16 gigs or so of ram, and a terabyte of disk, not long after 7.5 is released (unless problems with PostgreSQL on that platform turn up). Is that what you have in mind when you say small systems? I expect that one year after release, there will be ten times as many PostgreSQL systems on Win32 as all combined versions now on UNIX flavors (of course, that is a SWAG, but I think a sound one) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested xacts: looking for testers and review
On Wed, Jun 09, 2004 at 11:32:08PM -0700, Stephan Szabo wrote: Unfortunately, I've gotten it to fail, but I haven't looked in depth (I'm at work, so I'm doing it during compilations and such.) [...] Okay - I think I see what's going on here. It looks like deferredTriggerInvokeEvents is being run (immediate_only), but since deferredTriggers-events_imm is NULL it's using deferredTriggers-events as the start of the list to check, but this value isn't getting reset in DeferredTriggerEndSubXact in the case that the entire list was created in an aborted subtransaction. Ok, thanks for the test and diagnostics; patch attached. I'll see if I can find other situations like this. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) No hay cielo posible sin hundir nuestras raíces en la profundidad de la tierra(Malucha Pinto) diff -u 10bgwriter/src/backend/commands/trigger.c 13commitOpt/src/backend/commands/trigger.c --- 10bgwriter/src/backend/commands/trigger.c 2004-06-03 19:26:35.0 -0400 +++ 13commitOpt/src/backend/commands/trigger.c 2004-06-10 16:33:27.0 -0400 @@ -2278,9 +2278,11 @@ deferredTriggers-imm_stack[deferredTriggers-numpushed]; /* -* Make sure the last element is last. +* Cleanup the head and the tail of the list. */ - if (deferredTriggers-tail_thisxact != NULL) + if (deferredTriggers-tail_thisxact == NULL) + deferredTriggers-events = NULL; + else deferredTriggers-tail_thisxact-dte_next = NULL; /* ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Delaying the planning of unnamed statements until Bind
I've applied the patch you sent in for this, with some editorializations --- you were being too aggressive about substituting constants, with the net effect that the plan was not still parameterized as it was supposed to be. I realized along the way that what we're really doing here is inventing a notion of constant-folding expressions for estimation purposes only. As such, we don't have to be as rigid about making only provably safe transformations as eval_const_expressions normally has to be. I didn't do anything with the idea yet, but I'd like to look into having this mode do more than just substitute Param values. An example that's been causing us trouble for a long while is that the planner can't make any nondefault selectivity estimate for SELECT ... WHERE timestampcol now() - '1 day'; because eval_const_expressions dare not reduce now() to current time. But I think it would be entirely reasonable to do so for estimation purposes. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [COMMITTERS] pgsql-server: Clean up generation of default names
(moved to -hackers)
If you use sufficiently long table/field names then different tables
could truncate to the same generated names, and in that case there's
some risk of concurrently choosing the same unique name. But I don't
recall anyone having complained of that since we started using this
technique for choosing index names, so I'm not very worried. Basically
what this commit did was propagate the index naming technique to
constraints and sequences.
Is it conceivable that different SERIAL sequence names could now be
generated?
ie. If I upgrade from 7.4 with a dump that looks like this:
CREATE TABLE blah (
id SERIAL
);
COPY ...
SELECT SETVAL('blah_id_seq', 10);
Then if the name given to the id sequence is now different, these dumps
will not restore. (In this case it will be the same, I'm just
illustrating the general problem of hard-coding those sequence names in
the dump - I've never liked it :) )
Chris
---(end of broadcast)---
TIP 3: 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] [COMMITTERS] pgsql-server: Clean up generation of default names
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Then if the name given to the id sequence is now different, these dumps will not restore. (In this case it will be the same, I'm just illustrating the general problem of hard-coding those sequence names in the dump - I've never liked it :) ) Yeah, I know ... we ought to find some way around that, but I dunno what yet ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
Dann Corbit [EMAIL PROTECTED] writes: I expect that one year after release, there will be ten times as many PostgreSQL systems on Win32 as all combined versions now on UNIX flavors I surely hope not. Especially not multi-gig databases. The folks running those should know better than to use Windows, and if they do not, I'll be happy to tell them so. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [COMMITTERS] pgsql-server: Clean up generation of default names
Yeah, I know ... we ought to find some way around that, but I dunno
what yet ...
My idea, which I tried hacking, but gave up was to do the following:
1. Extend this command:
ALTER SEQUENCE seqname RESTART WITH 17;
to allow:
ALTER SEQUENCE ON table(col) RESTART WITH 17...
or
ALTER SEQUENCE ON table.col RESTART WITH 17...
2. Overload nextval, curval and setval:
SELECT SETVAL('schema.table', 'col', 17, false);
3. Or even create a pg_get_sequence() function:
SELECT SETVAL(pg_get_sequence(schema.table, col), 17);
etc.
Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [COMMITTERS] pgsql-server: Clean up generation of default
3. Or even create a pg_get_sequence() function: SELECT SETVAL(pg_get_sequence(schema.table, col), 17); Actually, this is the best solution :) Chris ---(end of broadcast)--- TIP 3: 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] Nested xacts: looking for testers and review
Bort, Paul [EMAIL PROTECTED] writes: Would it make more sense to specify at the time the optional subtransaction is committed that it is not critical to the completion of the outer transaction? BEGIN; BEGIN; DROP TABLE foo; COMMIT NON_CRITICAL; CREATE TABLE foo (i int); COMMIT; I assumed that was what was being proposed. It doesn't make sense to have a single flag on the entire outer transaction since there could have been multiple inner transactions, not all of which are unimportant. Hm, perhaps a parallel to CREATE OR REPLACE would be COMMIT OR ROLLBACK. I'm not sure if I'm serious about that or joking though. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Why frequently updated tables are an issue
Glen Parker wrote: It has been suggested in past to add such a visibility to index tuple header so that index and heaps can be cleaned out of order. In such a case other backround It seems to me that the benefit of this wouldn't be all that impressive *when accessing the cache*, which is the problem this discussion is about. I doubt. I have seen examnples on general list where people have thousands of dead *pages* for few hundred live tuples. If it is a problem with cache access, it will spill to disk as the problem grows. I don't think postgresql cache is that bad. No matter how you do it RAM is RAM. Problem is with disk bandwidth. See past discussions about vacuum delay patch and improvement it brought around. Vacuum costs disk bandwidth and that affects performance. That remains a fact. Disk access would occur more commonly with large tables, which I'll ignore. Let's say total scan time for a query on a very dirty table is 100ms. It seems safe to assume that the scan time for the index would be *roughly* half that of the heap. If visibilty could be determined by looking at just the index tuple, you'd cut you query scan time down to 50ms. When the clean table case is 7ms total scan time, the difference between 50 and 100 ms is not much of an issue; either way, it's still way to high! However increasing index footprint seems to be a tough sell. And rightly so, IMO. Mee too. Unless somebody comes up with patch that demonstrates the improvement. Obviously people can live with cost of mandatory vacuum so this is not high priority. But one day it will be. OTOH if the perceived benefit is not there, at least it is proven that it is not there. I plan to do it when I find time. But again, I don't face the problem myself(I don't even use postgresql for anything important for that matter) so haven't bothered spending any time on it. As long as it is not high priority, it is going to be a tough sell. Thats not unusual. Shridhar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] More vacuum.c refactoring
Manfred Koizar [EMAIL PROTECTED] writes: I understand you, honestly. Do I read between your lines that you didn't review my previous vacuum.c refactoring patch? Please do. It'd make *me* more comfortable. I did not yet, but I will get to it. I encourage everyone else to take a look too. I agree with Alvaro that fooling with this code merits extreme caution. BTW, I do not at all mean to suggest that vacuum.c contains no bugs at the moment ;-). I suspect for example that it is a bit random about whether MOVED_OFF/MOVED_IN bits get cleared immediately, or only by the next transaction that chances to visit the tuple. The next-transaction-fixup behavior has to be there in case the VACUUM transaction crashes, but that doesn't mean that VACUUM should deliberately leave work undone. I see three significant differences between the code in repair_frag() and vacuum_page(). Will study these comments later, but it's too late at night here... again, the more eyeballs on this the better... regards, tom lane ---(end of broadcast)--- TIP 3: 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] More vacuum.c refactoring
Alvaro Herrera [EMAIL PROTECTED] writes: Maybe we could establish heavier testing for this kind of change so potential patches can be tested extensively. Concurrent vacuums with all kinds of imaginable operations (insert, updates, deletes), in tight loops, could be a start. VACUUM FULL takes an exclusive lock, so it should not have to worry about concurrent operations on the table. What we have to think about is the initial states it can see. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
