Re: [HACKERS] Tablespace issues (comment on ,moving indexes)
Christopher Kings-Lynne wrote: > >>Errr, unlike all the other uses for alter table and friends? ie: > >> > >>OWNER TO > > > >Which changes the attributes of the table... > > And indexes. Sure. But not *just* indexes. > >>RENAME TO > > > >Same. > > And indexes. It does? I thought the indexes pointed to relations directly, not to tables by name, and so changing the name of the table wouldn't have any effect on the indexes, right? > >>SET TABLESPACE > > > >Which again changes the attributes of the table.. > > And indexes. But it does change more than just the indexes. But the context here is changing the tablespace of indexes independently of the tablespace for the table. For that, how exactly does it affect the table metadata? Not at all, I'd wager. If you're going to go use ALTER TABLE to make changes to the attributes of indexes, might I suggest that you also use ALTER TABLE to create and destroy them as well? Otherwise you end up with an inconsistent language, which is fine if the spec calls for it or if you somehow are attempting to maintain compatibility with something. But what we're talking about here is brand new functionality for which the language hasn't been defined yet. It would be a bit unfortunate to introduce inconsistencies where they're not needed, wouldn't you say? -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] fsync vs open_sync
Tom Lane wrote: [EMAIL PROTECTED] writes: The improvements were REALLY astounding, and I would like to know if other Linux users see this performance increase, I mean, it is almost 8~10 times faster than using fsync. Furthermore, it seems to also have the added benefit of reducing the I/O storm at checkpoints over a system running with fsync off. What size transactions are you using in your tests? For a system with small transactions (not much more than 1 page worth of WAL traffic per transaction) I'd be pretty surprised if there was any real difference at all. There certainly should not be any difference in terms of the number of physical writes. We have seen some platforms where fsync() is inefficiently implemented and requires more kernel overhead than is reasonable --- not for I/O, but just to look through the kernel buffers and confirm that none of them need flushing. But I didn't think Linux was one of these. IDE or scsi? If IDE: Write cache on or off? Which 2.4 kernel? The numbers are very high - it could be a side effect of write caching by the disks. I think some Suse 2.4 kernels have partial support for reliable fsync even if the write cache is on (i.e. fsync issues a cache flush command to the disk), but not all code paths are handled. Perhaps fsync is handled and O_SYNC is not handled. I could try to find the details. -- Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Can't figure out column type dependencies
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > How is that possible? I have heaps of columns that use the timestamp type: We don't make dependency entries for pinned objects (which includes all built-in datatypes). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Can't figure out column type dependencies
Hi, I'm looking to find all records in pg_depend that show that some columns in my db depend on the 'timestamp' type. So I do this: select * from pg_depend where refclassid=1247 and refobjid=1114; 1257 is the oid of the pg_type table and 1114 is the oid of the timestamp type. It returns just this: australia=# select * from pg_depend where refclassid=1247 and refobjid=1114; classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype -+---+--++--+-+- 0 | 0 |0 | 1247 | 1114 | 0 | p (1 row) How is that possible? I have heaps of columns that use the timestamp type: select count(*) from pg_attribute where atttypid=1114; If such dependencies aren't recorded, does the bit of code in alter column type that deletes them ever do anything? Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Beta Leader?
On Monday 09 August 2004 13:15, Josh Berkus wrote: > 1) track platform tests, namely which platforms have been tested, what they > reported, and which have not, including soliciting on the lists for more > platform testers; I wonder, would folks be interested in keeping a "supported programs" list as well? People could download various packages that they use regularly (Horde, DCL, OpenACS,etc..) that connect to postgresql, verify that the programs still work with 8.0, and then either report success or failure to the appropriate parties. I mention this because I seem to recall some application developers being caught off-gaurd by changes made in 7.4 since thier apps were never tested against the 7.4 beta. We could certainly devote some wiki space on techdocs for such a listing if folks thought it would be worth it. -- Robert Treat Build A Better Lamp :: Linux Apache {middleware} PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PITR - Some data is not recovered.
> Okay, so theoretically it should work ... I'm trying it now to see > if I can reproduce the problem locally. It took several tries, but eventually I did reproduce it. It seems the triggering condition is for the REDO pointer to be before the checkpoint record itself. Not sure why, yet, but it looks like the record(s) in between are not restored for some reason... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] VACUUM DELAY
On 8/9/2004 7:41 PM, Gaetano Mendola wrote: If I remember well this is the first command that need to change GUC in order to change behaviour, I don't think we wrote: set vacuum_mode = full; set vacuum_verbosity = on; vacuum; You got a point here. However, we don't have SELECT foo FROM bar WHERE baz = 'bumm' NOSEQSCAN; either, and I hope you don't suggest doing that next :-) 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 8: explain analyze is your friend
Re: [HACKERS] fsync vs open_sync
[EMAIL PROTECTED] writes: >> Just out of interest, what happens to the difference if you use *ext3* >> (perhaps with data=writeback) > > Actually, I was working for a client, so it wasn't a general exploritory, > but I can say that early on we discovered that ext3 was about the worst > file system for PostgreSQL. We gave up on it and decided to use ext2. I'd be interested in which ext3 mount options you used--I can see how anything other than 'data=writeback' could be a performance killer. I've been meaning to run a few tests myself, but haven't had the time... -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] fsync vs open_sync
> Just out of interest, what happens to the difference if you use *ext3* > (perhaps with data=writeback) Actually, I was working for a client, so it wasn't a general exploritory, but I can say that early on we discovered that ext3 was about the worst file system for PostgreSQL. We gave up on it and decided to use ext2. I have been considering a full sweep in my test lab off client time later on. ext2, ext3, jfs, xfs, and ReiserFS, fsync on with fdatasync or open_sync, and fsync off. One million inserts with auto commit. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Tablespace issues (comment on ,moving indexes)
Errr, unlike all the other uses for alter table and friends? ie: OWNER TO Which changes the attributes of the table... And indexes. RENAME TO Same. And indexes. SET TABLESPACE Which again changes the attributes of the table.. And indexes. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Tablespace issues (comment on ,moving indexes)
Christopher Kings-Lynne wrote: > >Hmm...not ALTER INDEX? Now that there's an operation that actually > >modifies an index instead of the table itself, should there be an ALTER > >INDEX? It would be cleaner and more consistent, IMO... > > Errr, unlike all the other uses for alter table and friends? ie: > > OWNER TO Which changes the attributes of the table... > RENAME TO Same. > SET TABLESPACE Which again changes the attributes of the table.. But using ALTER TABLE to change the tablespace that an index belongs to doesn't change an attribute of a table, it changes the attribute of an index. > etc. > > Lots of things against tables work against indexes and views. Some > stuff for commenting on columns say works on views, composite types and > indexes! No doubt. Of course, that something's been done a certain way in the past doesn't imply that it's the right way to do something new, nor does it imply that the new thing must be done that way. I mean, it's not a terribly big deal or anything, but since we're talking about stuff that isn't in the SQL spec it seems reasonable to define the commands in such a way that they don't violate the principle of least surprise. Using ALTER TABLE to alter the characteristics of an index violates that principle, at least in my opinion. It's not the first command I would have thought of when asking myself "how do I change the tablespace of an index?" -- ALTER INDEX is. And the reason is simple: we use CREATE INDEX to create an index and DROP INDEX to drop one -- we don't use ALTER TABLE subcommands to create or drop indexes. Why, then, should modification of an index's properties be treated any differently than the rest of the index manipulation commands? I just happen to like consistency. :-) -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] VACUUM DELAY
Gaetano Mendola wrote: > However I think is annoying to write: > > set vacuum_cost_delay = 100; > vacuum table ; > set vacuum_cost_delay = 0; > set ; > vacuum table ; > > Well, you are already seting it to zero for night, so why not just set it to non-zero for day? Seems the same to me, or set it to non-zero in postgresql.conf and set it to zero at night. > or even better: > > psql -c "set vacuum_cost_delay = 100; vacuum analyze;" > > and what about the utility vacuumdb ? Anyone using the utility command can use PGOPT to set the GUC I think. Maybe we should mention that in the manual page. -- 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
Re: [HACKERS] PITR - Some data is not recovered.
OKADA Satoshi <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Is this actually the official beta1 version, or is it a snapshot from >> last week sometime? >> > I got it from > ftp.postgresql.org/pub/source/v8.0.0beta/postgresql-8.0.0beta1.tar.gz, and > xlog.c revision number is 1.157. Okay, so theoretically it should work ... I'm trying it now to see if I can reproduce the problem locally. 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])
Re: [HACKERS] Tablespace issues (comment on ,moving indexes)
OK, added. --- Christopher Kings-Lynne wrote: > >>>1. there is no COMMENT ON TABLESPACE support > >> > >>That's right. > > That's deliberate. > > > Added to TODO: > > > > * Add COMMENT for tablespaces > > You may as well make that: > > * Add COMMENT ON for all cluster global objects (users, groups, > databases and tablespaces) > > Chris > > > ---(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 > -- 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] PITR - Some data is not recovered.
Tom Lane wrote: >OKADA Satoshi <[EMAIL PROTECTED]> writes: > >>I'm testing PITR using pgbench and postgresql ver.8.0bata. >> > >Is this actually the official beta1 version, or is it a snapshot from >last week sometime? > I got it from ftp.postgresql.org/pub/source/v8.0.0beta/postgresql-8.0.0beta1.tar.gz, and xlog.c revision number is 1.157. Thanks, Satoshi OKADA ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Tablespace issues (comment on ,moving indexes)
1. there is no COMMENT ON TABLESPACE support That's right. That's deliberate. Added to TODO: * Add COMMENT for tablespaces You may as well make that: * Add COMMENT ON for all cluster global objects (users, groups, databases and tablespaces) Chris ---(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] Tablespace issues (comment on ,moving indexes)
Hmm...not ALTER INDEX? Now that there's an operation that actually modifies an index instead of the table itself, should there be an ALTER INDEX? It would be cleaner and more consistent, IMO... Errr, unlike all the other uses for alter table and friends? ie: OWNER TO RENAME TO SET TABLESPACE etc. Lots of things against tables work against indexes and views. Some stuff for commenting on columns say works on views, composite types and indexes! Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Changing the type of timestamp columns
I wouldn't try this without running it against a test database copy first. I've already discovered that a backend change to a column data type like your describing can disrupt indexes, views, and analyze rows based on the table -- the last time I did such, I ended up having to dump and reload the database to get everything responding reliably. Yeah, I just remembered table types and pg_depend entries, so I'm not really keen to do it any more :/ Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] 8.0 Beta Thank You
Let me be one of the first to say thank you to all of you guys for yet another awesome version of PgSQL, beta or not. My company and I appreciate all the hard work and such that has gone into making this version happen. Keep up the good work and let me know where I can help. Sincerely, Gavin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Beta Page on Developer's site is out of date
Updated ... On Mon, 9 Aug 2004, Marc G. Fournier wrote: I'll be modifying that tonight ... On Mon, 9 Aug 2004, Serguei A. Mokhov wrote: Maybe this should be brought up-to-date slightly? http://developer.postgresql.org/beta.php Or maybe point elsewhere at least where the more up-to-date info actually is. -- Serguei A. Mokhov| /~\The ASCII Computer Science Department | \ / Ribbon Campaign Concordia University | XAgainst HTML Montreal, Quebec, Canada | / \ Email! ---(end of broadcast)--- TIP 8: explain analyze is your friend Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Tablespace issues (comment on ,moving indexes)
Kevin Brown <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Use ALTER TABLE on the index. > Hmm...not ALTER INDEX? Now that there's an operation that actually > modifies an index instead of the table itself, should there be an ALTER > INDEX? It would be cleaner and more consistent, IMO... [ shrug ] There have been some variants of ALTER TABLE that would work on indexes since day one. Sequences too. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] fsync vs open_sync
Just out of interest, what happens to the difference if you use *ext3* (perhaps with data=writeback) regards Mark [EMAIL PROTECTED] wrote: I did a little test on the various options of fsync. ... create table testndx (value integer, name varchar); create index testndx_val on testndx (value); for(int i=0; i < 100; i++) { printf_query( "insert into testndx (value, name) values ('%d', 'test')", random()); // report here } Anyway, with fsync enabled using standard fsync(), I get roughly 300-400 inserts per second. With fsync disabled, I get about 7000 inserts per second. When I re-enable fsync but use the open_sync option, I can get about 2500 inserts per second. (This is on Linux 2.4 kernel, ext2 file system) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Tablespace issues (comment on ,moving indexes)
Bruce Momjian <[EMAIL PROTECTED]> writes: >>> Added to TODO: >>> >>> * Add COMMENT for tablespaces > Oh, that shared thing! OK, removed. Well, it's a legitimate thing to have in TODO, just as long as you don't think it's trivial ;-). But don't we already have a TODO item about properly supporting comments on shared objects? Databases, users, groups, and now tablespaces all have the same issue. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Updateable Views?
On Sat, 07 Aug 2004 10:24:34 -0400, Jan Wieck <[EMAIL PROTECTED]> wrote: >I have not heard of "updatable subselects" yet. http://asktom.oracle.com/pls/ask/f?p=4950:8:6693556430011788783::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:273215737113, | Here we update a join. [...] | [EMAIL PROTECTED]> update | 2( select columnName, value | 3from name, lookup | 4 where name.keyname = lookup.keyname | 5 and lookup.otherColumn = :other_value ) | 6 set columnName = value | 7 / Google for oracle "delete statement" syntax or oracle "update statement" syntax Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Beta Page on Developer's site is out of date
I'll be modifying that tonight ... On Mon, 9 Aug 2004, Serguei A. Mokhov wrote: Maybe this should be brought up-to-date slightly? http://developer.postgresql.org/beta.php Or maybe point elsewhere at least where the more up-to-date info actually is. -- Serguei A. Mokhov| /~\The ASCII Computer Science Department | \ / Ribbon Campaign Concordia University | XAgainst HTML Montreal, Quebec, Canada | / \ Email! ---(end of broadcast)--- TIP 8: explain analyze is your friend Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Tablespace issues (comment on ,moving indexes)
On Mon, Aug 09, 2004 at 06:47:45PM -0400, Bruce Momjian wrote: > Gavin Sherry wrote: > > Well, Chris did bring this up but it will have the same problem as other > > shared tables, from memory. That is, you can add the comment in one > > database, but wont see if from another. > > Oh, that shared thing! OK, removed. How about a TODO for allowing comments for global objects, if there isn't one already? -- Alvaro Herrera (<[EMAIL PROTECTED]>) "The West won the world not by the superiority of its ideas or values or religion but rather by its superiority in applying organized violence. Westerners often forget this fact, non-Westerners never do." (Samuel P. Huntington) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Beta Leader?
This is the kind of thing I can usually help with. However, right now I'm a little swamped with customer work. I can back someone up, though. --elein On Mon, Aug 09, 2004 at 06:28:33PM -0400, Bruce Momjian wrote: > Marc G. Fournier wrote: > > On Mon, 9 Aug 2004, Josh Berkus wrote: > > > > > Folks, > > > > > > Per our discussion earlier this year, I really think that we could shorten the > > > beta process and make it more effective if someone can step forward to be the > > > "8.0 Beta Leader." This person would have to: > > > 1) track platform tests, namely which platforms have been tested, what they > > > reported, and which have not, including soliciting on the lists for more > > > platform testers; > > > 2) track reported bugs and issues to make sure that they are reported resolved > > > before release. > > > > > > It's not a huge task, but too big for me since I need to get started on the > > > Press Release and translations this week. Does anyone have time? Just a > > > little organization could make a big difference. > > > > Is there a reason why Bruce isn't doing it like he has for all previous > > releases? As he previously held such a role, shouldn't it be he calling > > for someone to take over that role from him? > > I am happy for someone else to do it, of course, anytime. I will be > traveling September 5 - October 7 so while I can still do it, having > someone else do it would help. > > -- > 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 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] am i asking in the wrong place?
On Mon, 9 Aug 2004, Hicham G. Elmongui wrote: > Hi everybody, > I never meant my emails to be spam. That's why i am just asking whether my > questions here are out of subject. Typically my questions are about > postgresql source code, like the question below. Please advise me whether > i should forward my questions to somewhere else. > Thanks a lot, > --h Most people are currently with beta and stuff. > > > On Thu, 5 Aug 2004, Hicham G. Elmongui wrote: > > > In "join_selectivity" function (plancat.c), a function call is made to > > "OidFunctionCall4" (fmgr.c), which in turn calls a function pointer. > > > > In need to know what is the actual function being called from > > OidFunctionCall4 if the selectivity of mergejoin is the one required from > > join_selectivity. Connect to the backend with a debugger such as gdb and step through the code and you will find it. Alternatively, have a better look at the code. The function called is that returned by get_oprjoin(). This looks up the operator (ie, '=', '<') in pg_operator and gets the function from there. For example, '=' is eqjoinsel() from memory. Gavin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] VACUUM DELAY
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jan Wieck wrote: | On 8/9/2004 1:19 PM, Gaetano Mendola wrote: | |> Jan Wieck wrote: |> |>> On 8/9/2004 7:19 AM, Gaetano Mendola wrote: |>> |>>> Hi all, |>>> I have seen the big debat about to have the delay |>>> off or on by default. |>>> |>>> Why not enable it by default and introduce a new |>>> parameter to vacuum command itself ? Something like: |>>> |>>> |>>> VACUUM WITH DELAY 100; |>> |>> |>> |>> It's not just one parameter to tune here. It is a set of parameters |>> that all together need to be viewed as a whole. The slowdown will be |>> affected by the other parameters as well, so turning the millisecond |>> knob only is not even half of the story. |> |> |> So the other parameter will inserted in the new sintax too, I think is |> fundamental |> the ability of override this values during the vacuum call: |> |> VACUUM WITH DELAY 100 [ ]; | | | You can do it right now. | | set vacuum_cost_delay = 100; | vacuum analyze; | No need to panic. No need to be smarty pants too. I know that it can be possible, after all 4 years for a dummy like I'm, are enough to understand that is possible to change some GUC for a given connection. :-) However I think is annoying to write: set vacuum_cost_delay = 100; vacuum table ; set vacuum_cost_delay = 0; set ; vacuum table ; or even better: psql -c "set vacuum_cost_delay = 100; vacuum analyze;" and what about the utility vacuumdb ? If I remember well this is the first command that need to change GUC in order to change behaviour, I don't think we wrote: set vacuum_mode = full; set vacuum_verbosity = on; vacuum; Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBGAud7UpzwH2SGd4RAjR0AKDw8XLAI2Lo2uqRauwhWJWwGmwYtgCgmI7u WDZvqwUMzuwXN6Z1qqj91vs= =Wxpz -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Missing French backend translations in the HEAD
Hello Peter, backend/po/fr.po had 99% translations done for 7.4, and nos it is totally missing for the current CVS tip (it is in the Attic)... why? Most of those messasge are still applicable to the current, no? Commit message from you from 2 weeks ago says: 2 weeks petere branches: 1.1.2; file fr.po was initially added on branch REL7_4_STABLE. http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/po/Attic/ I don't understad it and the reasons for the file to be removed. As a consequence it is also missing in the current translation status table. -- Serguei A. Mokhov| /~\The ASCII Computer Science Department | \ / Ribbon Campaign Concordia University | XAgainst HTML Montreal, Quebec, Canada | / \ Email! ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Beta Page on Developer's site is out of date
Maybe this should be brought up-to-date slightly? http://developer.postgresql.org/beta.php Or maybe point elsewhere at least where the more up-to-date info actually is. -- Serguei A. Mokhov| /~\The ASCII Computer Science Department | \ / Ribbon Campaign Concordia University | XAgainst HTML Montreal, Quebec, Canada | / \ Email! ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] OidFunctionCall4
On Thursday 05 August 2004 04:43 pm, Hicham G. Elmongui wrote: > In "join_selectivity" function (plancat.c), a function call is made to > "OidFunctionCall4" (fmgr.c), which in turn calls a function pointer. > > In need to know what is the actual function being called from > OidFunctionCall4 if the selectivity of mergejoin is the one required from > join_selectivity. > I'm no expert, but I can tell you from experience with the database that my first impression is that it is calling a function in the database (stored in pg_proc table or something like that). -- Jonathan Gardner [EMAIL PROTECTED] ---(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] Ready for Beta ... ?
On 8/9/2004 3:46 PM, Bruce Momjian wrote: Jan Wieck wrote: On 8/8/2004 11:58 AM, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: >> The only open issue I see for beta1 is perhaps disabling vacuum delay. > > Given that Jan is clearly in the minority on that, I suggest we just > turn it off for beta1. We can always turn it on later if he manages > to convince more people. Won't try to convince more people. I was about to disable it when Bruces commit message flew by. Jan, I hate to back out someone else's patches. I should have waited longer. You know that it's fine with me. Actually it's my turn to apologize in this case because I activated vacuum_cost_delay under false assumptions and lacking discussion. I was a bit slow in backing it out because my VM crashed once following several suspends, and I had to get my notebook into a friends WLan. So I was happy to see you did it already. 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Tablespace issues (comment on ,moving indexes)
Gavin Sherry wrote: > On Mon, 9 Aug 2004, Bruce Momjian wrote: > > > Tom Lane wrote: > > > Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > > > > 1. there is no COMMENT ON TABLESPACE support > > > > > > That's right. > > > > Added to TODO: > > > > * Add COMMENT for tablespaces > > Well, Chris did bring this up but it will have the same problem as other > shared tables, from memory. That is, you can add the comment in one > database, but wont see if from another. > > Did I misunderstand? Oh, that shared thing! OK, removed. -- 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] fsync vs open_sync
[EMAIL PROTECTED] writes: > The improvements were REALLY astounding, and I would like to know if other > Linux users see this performance increase, I mean, it is almost 8~10 times > faster than using fsync. > Furthermore, it seems to also have the added benefit of reducing the I/O > storm at checkpoints over a system running with fsync off. What size transactions are you using in your tests? For a system with small transactions (not much more than 1 page worth of WAL traffic per transaction) I'd be pretty surprised if there was any real difference at all. There certainly should not be any difference in terms of the number of physical writes. We have seen some platforms where fsync() is inefficiently implemented and requires more kernel overhead than is reasonable --- not for I/O, but just to look through the kernel buffers and confirm that none of them need flushing. But I didn't think Linux was one of these. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Tablespace issues (comment on ,moving indexes)
On Mon, 9 Aug 2004, Bruce Momjian wrote: > Tom Lane wrote: > > Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > > > 1. there is no COMMENT ON TABLESPACE support > > > > That's right. > > Added to TODO: > > * Add COMMENT for tablespaces Well, Chris did bring this up but it will have the same problem as other shared tables, from memory. That is, you can add the comment in one database, but wont see if from another. Did I misunderstand? Gavin ---(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] fsync vs open_sync
[EMAIL PROTECTED] wrote: > Furthermore, it seems to also have the added benefit of reducing the I/O > storm at checkpoints over a system running with fsync off. > > I'm really serious about this, changing this one parameter had dramatic > results on performance. We should have a general call to users to test > this setting with their OS of choice. If not that, if we can be sure that > there are no cases where using O_SYNC is worse than fsync() or > fdatasync(), it should be considered as the default. Agreed. Have you looked at src/tools/fsync? -- 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
Re: [HACKERS] Tablespace issues (comment on ,moving indexes)
Tom Lane wrote: > Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > > 1. there is no COMMENT ON TABLESPACE support > > That's right. Added to TODO: * Add COMMENT for tablespaces -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Beta Leader?
Marc G. Fournier wrote: > On Mon, 9 Aug 2004, Josh Berkus wrote: > > > Folks, > > > > Per our discussion earlier this year, I really think that we could shorten the > > beta process and make it more effective if someone can step forward to be the > > "8.0 Beta Leader." This person would have to: > > 1) track platform tests, namely which platforms have been tested, what they > > reported, and which have not, including soliciting on the lists for more > > platform testers; > > 2) track reported bugs and issues to make sure that they are reported resolved > > before release. > > > > It's not a huge task, but too big for me since I need to get started on the > > Press Release and translations this week. Does anyone have time? Just a > > little organization could make a big difference. > > Is there a reason why Bruce isn't doing it like he has for all previous > releases? As he previously held such a role, shouldn't it be he calling > for someone to take over that role from him? I am happy for someone else to do it, of course, anytime. I will be traveling September 5 - October 7 so while I can still do it, having someone else do it would help. -- 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 7: don't forget to increase your free space map settings
Re: [HACKERS] Tablespace issues (comment on ,moving indexes)
Tom Lane wrote: > Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > > 1. there is no COMMENT ON TABLESPACE support > > That's right. > > > 2. how is one supposed to move indexes(not tables) to another tablespace? > > Use ALTER TABLE on the index. Hmm...not ALTER INDEX? Now that there's an operation that actually modifies an index instead of the table itself, should there be an ALTER INDEX? It would be cleaner and more consistent, IMO... -- Kevin Brown [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] fsync vs open_sync
> [EMAIL PROTECTED] writes: >> I did a little test on the various options of fsync. > > There were considerably more extensive tests back when we created the > different WAL options, and the conclusions seemed to be that the best > choice is platform-dependent and also usage-dependent. (In particular, > it makes a huge difference whether WAL has its own drive or not.) > > I don't really recall why open_sync didn't end up among the set of > choices considered for the default setting. It may be that we need to > reconsider based on the behavior of newer Linux versions ... > > In any case, comparing open_sync to fsync is irrelevant, seeing that > the current default choice on Linux is fdatasync. What you ought to > be telling us about is the performance relative to that. I can tell you, and I'll send all the results if you like, but fsync and fdatasync are, as far as I can tell, idenitical. In fact, I can't find any documentation that fdatasync is no longer implemented on Linux as fsync. I tested fsync and fdatasync first and in my tests, the performance of fdatasync and fsync were the same. I never went beyond these as it looked like the fsync options were all basically the same. I hadn't read anywhere where open_sync could make such a difference. It is only because of some idle chatter (over a few years) I read in a couple Linux kernel mailing list about O_SYNC being improved, that I thought I'd try it. The improvements were REALLY astounding, and I would like to know if other Linux users see this performance increase, I mean, it is almost 8~10 times faster than using fsync. Furthermore, it seems to also have the added benefit of reducing the I/O storm at checkpoints over a system running with fsync off. I'm really serious about this, changing this one parameter had dramatic results on performance. We should have a general call to users to test this setting with their OS of choice. If not that, if we can be sure that there are no cases where using O_SYNC is worse than fsync() or fdatasync(), it should be considered as the default. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] 8.0.0beta1 ... packaged for testing ...
On Mon, 9 Aug 2004, Peter Eisentraut wrote: Tom Lane wrote: Would it be possible to automatically generate an un-fixed-up version for beta releases? I'd rather have the right info in an ugly format than the wrong info ... In doc/src/, run "make man.tar.gz". The required software should be listed in the documentation. I don't actually seem to have it available myself (OS change since last release, I think); I will look for it tomorrow. 'k, and I'll look into this tonight ... won't be there for beta1, but at least we might have it for beta2 ... Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 8.0.0beta1 ... packaged for testing ...
Tom Lane wrote: > Would it be possible to automatically generate an un-fixed-up version > for beta releases? I'd rather have the right info in an ugly format > than the wrong info ... In doc/src/, run "make man.tar.gz". The required software should be listed in the documentation. I don't actually seem to have it available myself (OS change since last release, I think); I will look for it tomorrow. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] fsync vs open_sync
[EMAIL PROTECTED] writes: > I did a little test on the various options of fsync. There were considerably more extensive tests back when we created the different WAL options, and the conclusions seemed to be that the best choice is platform-dependent and also usage-dependent. (In particular, it makes a huge difference whether WAL has its own drive or not.) I don't really recall why open_sync didn't end up among the set of choices considered for the default setting. It may be that we need to reconsider based on the behavior of newer Linux versions ... In any case, comparing open_sync to fsync is irrelevant, seeing that the current default choice on Linux is fdatasync. What you ought to be telling us about is the performance relative to that. regards, tom lane ---(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] Tablespace issues (comment on ,moving indexes)
Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > 1. there is no COMMENT ON TABLESPACE support That's right. > 2. how is one supposed to move indexes(not tables) to another tablespace? Use ALTER TABLE on the index. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] am i asking in the wrong place?
Hi everybody, I never meant my emails to be spam. That's why i am just asking whether my questions here are out of subject. Typically my questions are about postgresql source code, like the question below. Please advise me whether i should forward my questions to somewhere else. Thanks a lot, --h On Thu, 5 Aug 2004, Hicham G. Elmongui wrote: > In "join_selectivity" function (plancat.c), a function call is made to > "OidFunctionCall4" (fmgr.c), which in turn calls a function pointer. > > In need to know what is the actual function being called from > OidFunctionCall4 if the selectivity of mergejoin is the one required from > join_selectivity. > > Thanks > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Ready for Beta ... ?
Jan Wieck wrote: > On 8/8/2004 11:58 AM, Tom Lane wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > >> The only open issue I see for beta1 is perhaps disabling vacuum delay. > > > > Given that Jan is clearly in the minority on that, I suggest we just > > turn it off for beta1. We can always turn it on later if he manages > > to convince more people. > > Won't try to convince more people. I was about to disable it when Bruces > commit message flew by. Jan, I hate to back out someone else's patches. I should have waited longer. -- 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 7: don't forget to increase your free space map settings
Re: [HACKERS] VACUUM DELAY
On 8/9/2004 1:19 PM, Gaetano Mendola wrote: Jan Wieck wrote: On 8/9/2004 7:19 AM, Gaetano Mendola wrote: Hi all, I have seen the big debat about to have the delay off or on by default. Why not enable it by default and introduce a new parameter to vacuum command itself ? Something like: VACUUM WITH DELAY 100; It's not just one parameter to tune here. It is a set of parameters that all together need to be viewed as a whole. The slowdown will be affected by the other parameters as well, so turning the millisecond knob only is not even half of the story. So the other parameter will inserted in the new sintax too, I think is fundamental the ability of override this values during the vacuum call: VACUUM WITH DELAY 100 [ ]; You can do it right now. set vacuum_cost_delay = 100; vacuum analyze; No need to panic. 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 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] switch WAL segment
Andreas Pflug wrote: Tom Lane wrote: Do we have a TODO for allowing users to force switching to a new WAL file segment? Together with PITR, this might make sense? Another idea: Has anyone tried to put the WAL segment directory on a cluster filesystem and use that for cold (perhaps even hot) failover? The archive script could apply completed wal segments to the backup node. If the primary node fails, the last (partial) segment is applied as well and the backup node is activated. -- Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] VACUUM DELAY
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alvaro Herrera wrote: | On Mon, Aug 09, 2004 at 07:19:44PM +0200, Gaetano Mendola wrote: | | |>So the other parameter will inserted in the new sintax too, I think is |>fundamental |>the ability of override this values during the vacuum call: |> |>VACUUM WITH DELAY 100 [ ]; | | | What's wrong with | | SET vacuum_delat 100; | SET whatever_parameter 'value'; | VACUUM ...; Noting wrong but: 1) The parameters and new feature will be spotted out better to new users 2) My shell script will become less hugly :-) Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBF8wv7UpzwH2SGd4RAnSHAJ0QI0Uu9ZVJiMFn3NY5jFT6omdkYwCfZ8pU BaVnYczZ9pGGTBXMurNtj30= =hP7Q -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Beta Leader?
Marc, > Is there a reason why Bruce isn't doing it like he has for all previous > releases? As he previously held such a role, shouldn't it be he calling > for someone to take over that role from him? Clearly I was confused about the import of a discussion we had on Core, some 3 months ago or so, about this topic. Please forget I said anything. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Beta Leader?
On Mon, 9 Aug 2004, Josh Berkus wrote: Folks, Per our discussion earlier this year, I really think that we could shorten the beta process and make it more effective if someone can step forward to be the "8.0 Beta Leader." This person would have to: 1) track platform tests, namely which platforms have been tested, what they reported, and which have not, including soliciting on the lists for more platform testers; 2) track reported bugs and issues to make sure that they are reported resolved before release. It's not a huge task, but too big for me since I need to get started on the Press Release and translations this week. Does anyone have time? Just a little organization could make a big difference. Is there a reason why Bruce isn't doing it like he has for all previous releases? As he previously held such a role, shouldn't it be he calling for someone to take over that role from him? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Beta Leader?
On Mon, 2004-08-09 at 13:57, Peter Eisentraut wrote: > Josh Berkus wrote: > > Per our discussion earlier this year, I really think that we could > > shorten the beta process and make it more effective if someone can > > step forward to be the "8.0 Beta Leader." This person would have > > to: > > 1) track platform tests, namely which platforms have been tested, > > what they reported, and which have not, including soliciting on the > > lists for more platform testers; > > 2) track reported bugs and issues to make sure that they are reported > > resolved before release. > > Bruce has always done these things and there are no indications that he > won't do them again. In particular, platform tests will as usual be > called for later in the beta phase, and the list of open items is > frequently posted. Is there anything else? > > Nevertheless, I think that shortening the beta phase is something that > we cannot and should not do. > Well, we cannot shorten it just for the sake of shortening it, but we need to try to keep things from "stalling". A number of people mentioned that at times last year it seemed that nothing was going on with 7.4beta, so we need to try and stay cognizant of actual activity going on. Not sure if it requires a person with a title, but as a general goal I think it is something we would benefit from. Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] VACUUM DELAY
On Mon, Aug 09, 2004 at 07:19:44PM +0200, Gaetano Mendola wrote: > So the other parameter will inserted in the new sintax too, I think is > fundamental > the ability of override this values during the vacuum call: > > VACUUM WITH DELAY 100 [ ]; What's wrong with SET vacuum_delat 100; SET whatever_parameter 'value'; VACUUM ...; -- Alvaro Herrera () Licensee shall have no right to use the Licensed Software for productive or commercial use. (Licencia de StarOffice 6.0 beta) ---(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] fsync vs open_sync
I did a little test on the various options of fsync. I'm not sure my tests are scientific enough for general publication or evaluation, all I am doing is performaing a loop that inserts a value into a table 1 million times. create table testndx (value integer, name varchar); create index testndx_val on testndx (value); for(int i=0; i < 100; i++) { printf_query( "insert into testndx (value, name) values ('%d', 'test')", random()); // report here } Anyway, with fsync enabled using standard fsync(), I get roughly 300-400 inserts per second. With fsync disabled, I get about 7000 inserts per second. When I re-enable fsync but use the open_sync option, I can get about 2500 inserts per second. (This is on Linux 2.4 kernel, ext2 file system) (1) Is there any drawback to using open_sync as it appears to be a happy medium to turing fsync off? (2) Does anyone know if the "open_sync" option performs this well across most platforms or only Linux? (3) If "open_sync" works well across many platforms, and there are no drawbacks, shouldn't it be the default wal sync method? The performance bood is increadible. ---(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
[HACKERS] fsync vs open_sync
I did a little test on the various options of fsync. I'm not sure my tests are scientific enough for general publication or evaluation, all I am doing is performaing a loop that inserts a value into a table 1 million times. create table testndx (value integer, name varchar); create index testndx_val on testndx (value); for(int i=0; i < 100; i++) { insert into testndx (value, name) values ('%d', 'test') ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Beta Leader?
Josh Berkus wrote: > Per our discussion earlier this year, I really think that we could > shorten the beta process and make it more effective if someone can > step forward to be the "8.0 Beta Leader." This person would have > to: > 1) track platform tests, namely which platforms have been tested, > what they reported, and which have not, including soliciting on the > lists for more platform testers; > 2) track reported bugs and issues to make sure that they are reported > resolved before release. Bruce has always done these things and there are no indications that he won't do them again. In particular, platform tests will as usual be called for later in the beta phase, and the list of open items is frequently posted. Is there anything else? Nevertheless, I think that shortening the beta phase is something that we cannot and should not do. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Add Missing From?
On 8/9/2004 12:53 PM, Josh Berkus wrote: People, > DELETE FROM target_tbl USING other_tbls WHERE ... Feels much more understandable. The second FROM looks like a hickup. Yes, although imagine: DELETE FROM staff USING users JOIN logons USING (user_id) WHERE last_logon < ( now() - '6 months'); Not as bad as FROM, but still a bit baffling to look at. Still, I can't think of anything else that wouldn't require inventing a new reserved word. What about DELETE FROM staff JOIN users ... then? Oh, and MySQL's "multi-table deletes": PLEASE tell me that's not SQL-standard. Yes, not standard. 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 4: Don't 'kill -9' the postmaster
Re: Postgres development model (was Re: [HACKERS] CVS comment)
On 08/09/04:32/1, Peter Eisentraut wrote: > BitKeeper ist not open source, so it's out of the question for most > people. Subversion is shockingly unstable. I'm very open for > something that replaces CVS, but I'd rather not use any than one of > these two. >From my casual usage of svn, I haven't noticed any stability issues. If it were shockingly unstable, I would have expected to have had problems with it. Using the ssh tunnel, served on an fbsd jail(yeah, rented from Marc), and connecting with svn client 1.0.4 on my home fbsd 4.10 box.. -- Regards, James William Pye pgpSZcjQr1lVw.pgp Description: PGP signature
Re: [HACKERS] VACUUM DELAY
Jan Wieck wrote: On 8/9/2004 7:19 AM, Gaetano Mendola wrote: Hi all, I have seen the big debat about to have the delay off or on by default. Why not enable it by default and introduce a new parameter to vacuum command itself ? Something like: VACUUM WITH DELAY 100; It's not just one parameter to tune here. It is a set of parameters that all together need to be viewed as a whole. The slowdown will be affected by the other parameters as well, so turning the millisecond knob only is not even half of the story. So the other parameter will inserted in the new sintax too, I think is fundamental the ability of override this values during the vacuum call: VACUUM WITH DELAY 100 [ ]; Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SRFs ExecMakeTableFunctionResult
> Seems reasonable to me. A SRF function really ought to explicitly set > isDone on every call anyway. Aye, it seems reasonable, but a bit inconsistent with the effect of ExecMakeFunctionResult, which does the same thing but bases the continuity of the result gathering on the isDone pointer, which is set to ExprMultipleResult if isDone is not ExprEndResult, thus making it continue until rsinfo.isDone is explicitly set to ExprEndResult, unlike table functions which will end on either SingleResult or EndResult. (Around lines #941-984 in execQual.c) Is this inconsistency desired? My confusion came in when I implemented SRFs that worked with non-table SRFs, and then table functions didn't work because I wasn't setting isDone to MultipleResult every call. -- Regards, James William Pye pgpoHdRYbOzbM.pgp Description: PGP signature
[HACKERS] Tablespace issues (comment on ,moving indexes)
Hi! I'm currently working on the psql tab-complete code, fixing quite a lot of bugs/annoyances in the process. One of the things I'm trying to do is syncing the available commands in psql with the docs - during this work I found two irritating things regarding tablespaces: 1. there is no COMMENT ON TABLESPACE support - it is neither documented nor does it seem to work using the obvious syntax (COMMENT ON TABLESPACE 'foo' IS 'bar'). 2. how is one supposed to move indexes(not tables) to another tablespace? The (devel)docs have this in the ALTER TABLE - section: "This form changes the table's tablespace to the specified tablespace and moves the data file(s) associated with the table to the new tablespace. Indexes on the table, if any, are not moved; but they can be moved separately with additional SET TABLESPACE commands. " not sure how to interpret that - who would an example for moving an index look like given that (AFAIR there is nothing like ALTER INDEX 'foo' SET TABLESPACE 'bar') ? thanks Stefan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PITR - Some data is not recovered.
OKADA Satoshi <[EMAIL PROTECTED]> writes: > I'm testing PITR using pgbench and postgresql ver.8.0bata. Is this actually the official beta1 version, or is it a snapshot from last week sometime? In the first commit that had pg_start_backup(), there wasn't any logic to ensure that the recovery replay would really start from before the backup began. If a checkpoint occurred after you started the tar run but before tar got around to copying pg_control, the recovery wouldn't work properly. I can't be sure but your report seems consistent with such a problem. [ digs in CVS logs ] The necessary additional code was committed in revision 1.155 of src/backend/access/transam/xlog.c. If you have 1.154 please update and try again. If you do have 1.155 or later then we need to look closer. regards, tom lane ---(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] Add Missing From?
Josh Berkus <[EMAIL PROTECTED]> writes: > Oh, and MySQL's "multi-table deletes": PLEASE tell me that's not > SQL-standard. It's not. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Beta Leader?
Folks, Per our discussion earlier this year, I really think that we could shorten the beta process and make it more effective if someone can step forward to be the "8.0 Beta Leader." This person would have to: 1) track platform tests, namely which platforms have been tested, what they reported, and which have not, including soliciting on the lists for more platform testers; 2) track reported bugs and issues to make sure that they are reported resolved before release. It's not a huge task, but too big for me since I need to get started on the Press Release and translations this week. Does anyone have time? Just a little organization could make a big difference. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Ready for Beta ... ?
On 8/8/2004 11:58 AM, Tom Lane wrote: Bruce Momjian <[EMAIL PROTECTED]> writes: The only open issue I see for beta1 is perhaps disabling vacuum delay. Given that Jan is clearly in the minority on that, I suggest we just turn it off for beta1. We can always turn it on later if he manages to convince more people. Won't try to convince more people. I was about to disable it when Bruces commit message flew by. 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Add Missing From?
Jan Wieck <[EMAIL PROTECTED]> writes: > What about > DELETE FROM staff JOIN users ... > then? I don't much care for that, mainly because in my mind "x JOIN y" should always be semantically equivalent to "y JOIN x". I think we want a real clear syntactical separation between the deletion target table and the other tables. Also we do have the precedent of the way that UPDATE does things. We don't want to use the keyword FROM because of confusion, but I think we want to keep it basically the same as UPDATE. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Changing the type of timestamp columns
KL, > Is it safe to update the atttypid of a timestamp column to be a > timestamptz column? I wish to do this on a production database, so I > need to be sure! I wouldn't try this without running it against a test database copy first. I've already discovered that a backend change to a column data type like your describing can disrupt indexes, views, and analyze rows based on the table -- the last time I did such, I ended up having to dump and reload the database to get everything responding reliably. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Add Missing From?
People, > > DELETE FROM target_tbl USING other_tbls WHERE ... > > Feels much more understandable. The second FROM looks like a hickup. Yes, although imagine: DELETE FROM staff USING users JOIN logons USING (user_id) WHERE last_logon < ( now() - '6 months'); Not as bad as FROM, but still a bit baffling to look at. Still, I can't think of anything else that wouldn't require inventing a new reserved word. Oh, and MySQL's "multi-table deletes": PLEASE tell me that's not SQL-standard. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] lpthread errors while compiling 8.0beta
On Monday 09 Aug 2004 9:18 pm, Robert Treat wrote: > but I was pretty sure I had lpthread library on my machine (and verified > this with some help from irc). however Makefile.global told me > > PTHREAD_CFLAGS = -pthread -D_REENTRANT -D_THREAD_SAFE > -D_POSIX_PTHREAD_SEMANTICS > PTHREAD_LIBS= > > so I added -lpthread to the PTHREAD_LIBS line and it all compiled ok and > passed regression. one theory of where the problem lies focused on this > bit of config.log: > > configure:13260: checking for the pthreads library -lpthreads > configure:13301: gcc -o conftest -O2 -fno-strict-aliasing -g -D_GNU_SOURCE > conftest.c -lpthreads -lz -lreadline -ltermcap -lcrypt -lresolv -lnsl -ldl > -lm >&5 > /usr/i386-slackware-linux/bin/ld: cannot find -lpthreads > collect2: ld returned 1 exit status > configure:13304: $? = 1 > configure: failed program was: I noticed it on a slackware 9.1 system as well. I didn't noticed the above part but I had to change Makefile.global. My hypothesis was linuxthreads does not transmit libpthread.so dependency correctly.(Out of memory, found while reading on differences between NTPL and linuxthreads. Don't remember the exact source now). I got a failure while linking initdb because it was linking against libpq, which in turn linking against libpthreads.so. So in order to close the linking unit, the linker needs -lpthreads mentioned against initdb(or in global linker flags). NTPL can take care of this situation IIRC. Since linuxthreads are becoming extinct rapidly(barring slackware and may be debian, of course), I didn't chase the issue much. May be we need to document this. Shridhar ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] lpthread errors while compiling 8.0beta
Worked through some troubles on irc last night, thought I would post it in case anyone else sees something similar. system is Slackware 8.1, Linux phppgadmin 2.4.18 #2 Fri May 31 01:21:23 PDT 2002 i586 unknown ./configure was run with '--prefix=/usr/local/pgsql-8.0.0' '--enable-debug' '--enable-depend' '--enable-cassert' '--enable-thread-safety' '--with-tcl' '--without-tk' '--with-python' which worked fine on 7.0 - 7.4, and seemed to work ok, however when running make I got the following: make[4]: Leaving directory `/usr/local/src/postgresql-8.0.0beta1/src/port' gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -DFRONTEND -I../../../src/interfaces/libpq -I../../../src/include -D_GNU_SOURCE -c -o initdb.o initdb.c -MMD rm -f dirmod.c && ln -s ../../../src/port/dirmod.c . gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -DFRONTEND -I../../../src/interfaces/libpq -I../../../src/include -D_GNU_SOURCE -c -o dirmod.o dirmod.c -MMD rm -f exec.c && ln -s ../../../src/port/exec.c . gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -DFRONTEND -I../../../src/interfaces/libpq -I../../../src/include -D_GNU_SOURCE -c -o exec.o exec.c -MMD gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations initdb.o dirmod.o exec.o -L../../../src/interfaces/libpq -lpq -L../../../src/port -Wl,-rpath,/usr/local/pgsql-8.0.0/lib -lpgport -lz -lreadline -ltermcap -lcrypt -lresolv -lnsl -ldl -lm -o initdb ../../../src/interfaces/libpq/libpq.so: undefined reference to `pthread_getspecific' ../../../src/interfaces/libpq/libpq.so: undefined reference to `pthread_once' ../../../src/interfaces/libpq/libpq.so: undefined reference to `pthread_key_create' ../../../src/interfaces/libpq/libpq.so: undefined reference to `pthread_setspecific' collect2: ld returned 1 exit status make[3]: *** [initdb] Error 1 make[3]: Leaving directory `/usr/local/src/postgresql-8.0.0beta1/src/bin/initdb' make[2]: *** [all] Error 2 make[2]: Leaving directory `/usr/local/src/postgresql-8.0.0beta1/src/bin' make[1]: *** [all] Error 2 make[1]: Leaving directory `/usr/local/src/postgresql-8.0.0beta1/src' make: *** [all] Error 2 so I went back and checked the relevant parts of configure which told me: checking for the pthreads library -lpthreads... no checking whether pthreads work without any flags... no checking whether pthreads work with -Kthread... no checking whether pthreads work with -kthread... no checking for the pthreads library -llthread... no checking whether pthreads work with -pthread... yes checking for joinable pthread attribute... PTHREAD_CREATE_JOINABLE checking if more special flags are required for pthreads... no checking for cc_r... gcc checking pthread.h usability... yes checking pthread.h presence... yes checking for pthread.h... yes but I was pretty sure I had lpthread library on my machine (and verified this with some help from irc). however Makefile.global told me PTHREAD_CFLAGS = -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS PTHREAD_LIBS= so I added -lpthread to the PTHREAD_LIBS line and it all compiled ok and passed regression. one theory of where the problem lies focused on this bit of config.log: configure:13260: checking for the pthreads library -lpthreads configure:13301: gcc -o conftest -O2 -fno-strict-aliasing -g -D_GNU_SOURCE conftest.c -lpthreads -lz -lreadline -ltermcap -lcrypt -lresolv -lnsl -ldl -lm >&5 /usr/i386-slackware-linux/bin/ld: cannot find -lpthreads collect2: ld returned 1 exit status configure:13304: $? = 1 configure: failed program was: noteably that it should have been using -lpthread not -lpthreads. that might be a typo, or might be some type of configure error since I know some platforms use -lpthreads, but I'm not sure, maybe someone else can put the info to good use. -- Robert Treat Build A Better Lamp :: Linux Apache {middleware} PostgreSQL ---(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] VACUUM DELAY
On 8/9/2004 7:19 AM, Gaetano Mendola wrote: Hi all, I have seen the big debat about to have the delay off or on by default. Why not enable it by default and introduce a new parameter to vacuum command itself ? Something like: VACUUM WITH DELAY 100; It's not just one parameter to tune here. It is a set of parameters that all together need to be viewed as a whole. The slowdown will be affected by the other parameters as well, so turning the millisecond knob only is not even half of the story. Setting the delay to zero simply disables the whole feature at runtime. That is why this discussion was using the delay parameter as a synonym for enabling/disabling the feature by default. Jan this will permit to change easilly the delay in the maintainance scripts. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- #==# # 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Analyze using savepoints?
Alvaro Herrera Munoz <[EMAIL PROTECTED]> writes: > I think both VACUUM and ANALYZE could be best served by appropiate use > of short-lived ResourceOwners. It needs some thought though. At least for VACUUM FULL, this is pretty much a nonstarter: it needs a real live genuine COMMIT in the middle. No half measures unless you are willing to lose your data on crash. ANALYZE could possibly get away with simply releasing the table lock early. I haven't thought about it in detail. 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])
Re: [HACKERS] Add Missing From?
Harald Fuchs <[EMAIL PROTECTED]> writes: > Actually, MySQL supports two different syntaxes for multi-table DELETEs: > 1. DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id; >(introduced in MySQL 4.0.0) > 2. DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id; >(introduced in MySQL 4.0.2) Yeah. I ignored the first, as being so stupid that even the MySQL guys soon realized what a bad idea it was ;-) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Add Missing From?
On 8/9/2004 12:29 AM, Tom Lane wrote: Robert Treat <[EMAIL PROTECTED]> writes: Well, as yall have pointed out, the feature is not sql spec (for some reason I thought it had been put in) so since the update syntax seems quite similar to oracles, perhaps they can provide a pointer on delete syntax as well? I can't seem to find my oracle syntax book, anyone have one handy ? Didn't get any Oracle hits in a quick google, but I did find out that MySQL spells it USING: DELETE FROM target_tbl USING other_tbls WHERE ... Feels much more understandable. The second FROM looks like a hickup. Jan This isn't a particularly compelling precedent seeing that (a) MySQL doesn't use our flavor of UPDATE syntax and (b) they only adopted the above in 4.0.2. But it's better than no precedent. And frankly I was having a big problem with "DELETE FROM target FROM others ..." If that's not a recipe for confusion I don't know what is. regards, tom lane ---(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 -- #==# # 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] Analyze using savepoints?
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > I read this in the release notes: > --- > # Database-wide ANALYZE does not hold locks across tables (Tom) > This reduces the potential for deadlocks against other backends that > want exclusive locks on tables. To get the benefit of this change, do > not execute database-wide ANALYZE inside a transaction block (BEGIN > block); it must be able to commit and start a new transaction for each > table. > --- > Does that mean that now if we used savepoints internally, analyze can > still be run in a transaction and still not hold lots of locks? No. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Changing the type of timestamp columns
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > Is it safe to update the atttypid of a timestamp column to be a > timestamptz column? For sufficiently small values of "safe", sure. The problem is that unless you live in GMT zone, the interpretation of values is different: the zero reference for timestamptz is midnight GMT 2000-01-01, whereas for timestamp it's midnight your local time 2000-01-01. So if you do the above, all the stored timestamps will appear to change value by your offset from GMT. If you're planning to replace all the column entries then it won't matter, but ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SRFs ExecMakeTableFunctionResult
James William Pye <[EMAIL PROTECTED]> writes: > While I was finishing up SRF support in PL/Py, I noticed that when VPC is the > selected mode for a table function, ExecMakeTableFunctionResult will set > rsinfo.isDone to ExprSingleResult each time it loops to fetch another value > (when a direct_function_call). This makes the VPC-SRF author set isDone to > ExprMultipleResult on _every_ call while returning values, as it will break > out if rsinfo.isDone !=3D ExprMultipleResult. > Is this the desired behavior? Seems reasonable to me. A SRF function really ought to explicitly set isDone on every call anyway. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.0.0beta1 ... packaged for testing ...
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Marc G. Fournier wrote: >>> Yeah, sure wish we could generate those man pages automatically :-( >> >> Is there a reason why we can't? > Usually, you need to look them over and fix them up a bit. You also > need a patched version of the processing tools. Would it be possible to automatically generate an un-fixed-up version for beta releases? I'd rather have the right info in an ugly format than the wrong info ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Analyze using savepoints?
On Mon, Aug 09, 2004 at 04:44:58PM +0800, Christopher Kings-Lynne wrote: > I read this in the release notes: > > --- > # Database-wide ANALYZE does not hold locks across tables (Tom) > > This reduces the potential for deadlocks against other backends that > want exclusive locks on tables. To get the benefit of this change, do > not execute database-wide ANALYZE inside a transaction block (BEGIN > block); it must be able to commit and start a new transaction for each > table. > --- > > Does that mean that now if we used savepoints internally, analyze can > still be run in a transaction and still not hold lots of locks? No, because savepoints do not release locks on successful completion, only on rollback. I think both VACUUM and ANALYZE could be best served by appropiate use of short-lived ResourceOwners. It needs some thought though. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "El realista sabe lo que quiere; el idealista quiere lo que sabe" (AnĂ³nimo) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] VACUUM DELAY
On Mon, 2004-08-09 at 05:19, Gaetano Mendola wrote: > Hi all, > I have seen the big debat about to have the delay > off or on by default. > > Why not enable it by default and introduce a new > parameter to vacuum command itself ? Something like: > > > VACUUM WITH DELAY 100; > > > this will permit to change easilly the delay in the maintainance > scripts. The problem, I believe, is that any delay at all results in a VERY slow vacuum run (like 3 to 5 times slower) and for some people, this will be such unexpected behaviour they may believe postgresql is broken, or just want the older, faster vacuum, especially in a development environment. Imagine an increase from 1 to 5 minutes on an otherwise duplicate database from a 7.4 machine. I'll personally be running the delay and autovacuum on any machine I'll be running, and I think once the autovacuum is integrated, it might make sense to have a vacuum command just toss an entry in a que saying "vacuum this table next scheduled run" and return immediately with a NOTICE: vacuum (on tablex) scheduled. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Postgres development model (was Re: [HACKERS] CVS comment)
Hi, On Monday 09 August 2004 09:30, you wrote: > Tom Lane wrote: > > I haven't seen any particular reason why we should adopt another SCM. > > Perhaps BitKeeper or SubVersion would be better for our purposes than > > CVS, but are they enough better to justify the switchover costs? > > BitKeeper ist not open source, so it's out of the question for most > people. Subversion is shockingly unstable. I'm very open for > something that replaces CVS, but I'd rather not use any than one of > these two. Wow, that's a remark. 'Shockingly unstable'... I wonder where you got that from? As someone who is using Subversion very heavily in production environments, with code repositories which outgrow PostgreSQL's codebase size by factors of up to 20 (mainly due to being binary source code for a strange development platform) and having a very high update rate I *never* had any problems except one or two short periods of inaccessibility due to web server probs. I would really like you to substantiate those claims, and please not from the pre-beta time area. 'K, 'nough said! Greetings, Joerg Hessdoerfer -- Leading SW developer - S.E.A GmbH Mail: [EMAIL PROTECTED] WWW: http://www.sea-gmbh.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] VACUUM DELAY
Hi all, I have seen the big debat about to have the delay off or on by default. Why not enable it by default and introduce a new parameter to vacuum command itself ? Something like: VACUUM WITH DELAY 100; this will permit to change easilly the delay in the maintainance scripts. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Add Missing From?
In article <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> writes: > Robert Treat <[EMAIL PROTECTED]> writes: >> Well, as yall have pointed out, the feature is not sql spec (for some >> reason I thought it had been put in) so since the update syntax seems >> quite similar to oracles, perhaps they can provide a pointer on delete >> syntax as well? I can't seem to find my oracle syntax book, anyone >> have one handy ? > Didn't get any Oracle hits in a quick google, but I did find out that > MySQL spells it USING: > DELETE FROM target_tbl USING other_tbls WHERE ... > This isn't a particularly compelling precedent seeing that (a) MySQL > doesn't use our flavor of UPDATE syntax and (b) they only adopted the > above in 4.0.2. Actually, MySQL supports two different syntaxes for multi-table DELETEs: 1. DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id; (introduced in MySQL 4.0.0) 2. DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id; (introduced in MySQL 4.0.2) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgres development model (was Re: CVS comment)
Peter Eisentraut wrote: Tom Lane wrote: I haven't seen any particular reason why we should adopt another SCM. Perhaps BitKeeper or SubVersion would be better for our purposes than CVS, but are they enough better to justify the switchover costs? BitKeeper ist not open source, so it's out of the question for most people. Not for Linus Torvalds apparently. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] PITR - Some data is not recovered.
I'm testing PITR using pgbench and postgresql ver.8.0bata. I think that result of recovery is wrong. My test procedure is as follows: I edited postgresql.conf for PITR and started the postmaster. And I executed "pgbench -t 2". % pgbench -t 2 I did backup procedure before end of pgbench. % psql -c "SELECT pg_start_backup('label1')" % cd $PGDATA % tar cf /tmp/back.tar ./* % psql -c "SELECT pg_stop_backup()" restore and recovery % pg_ctl stop % cp -R $PGDATA/pg_xlog /tmp/. % rm -rf $PGDATA/* % cd $PGDATA % tar xf /tmp/data.tar create "recovery.conf" file % rm -rf $PGDATA/pg_xlog % cp -R /tmp/pg_xlog $PGDATA/. % pg_ctl start check data after recovery % psql -c "SELECT count(*) from history" count --- 1 (1 row) Number of records should be 2, but result is 1. I found lack of data that was inserted near backup time, as a result of comparing original "history" table and "history" table which was recovered. Is my backup procedure wrong? Thanks, Satoshi OKADA ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Analyze using savepoints?
I read this in the release notes: --- # Database-wide ANALYZE does not hold locks across tables (Tom) This reduces the potential for deadlocks against other backends that want exclusive locks on tables. To get the benefit of this change, do not execute database-wide ANALYZE inside a transaction block (BEGIN block); it must be able to commit and start a new transaction for each table. --- Does that mean that now if we used savepoints internally, analyze can still be run in a transaction and still not hold lots of locks? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Changing the type of timestamp columns
Is it safe to update the atttypid of a timestamp column to be a timestamptz column? I wish to do this on a production database, so I need to be sure! Oh, and what about indexes on them? Do I just drop them beforehand and recreate? 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
[HACKERS] Changing the type of timestamp columns
Hi guys, Is it safe to update the atttypid of a timestamp column to be a timestamptz column? I wish to do this on a production database, so I need to be sure! Thanks, Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Fwd: init scripts and su
Tom Lane wrote: > (a) And there would be untrusted code running as postgres exactly > why? Because someone has cracked the PostgreSQL server. > (b) Seems to me the real security bug here is the mere existence of > that ioctl call. Probably. I'm just pointing out the findings about the environment we're operating in. The fact is that right now "run as postgres to protect your root account" won't work on some systems and with unfortunately written init scripts. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Postgres development model (was Re: [HACKERS] CVS comment)
Tom Lane wrote: > I haven't seen any particular reason why we should adopt another SCM. > Perhaps BitKeeper or SubVersion would be better for our purposes than > CVS, but are they enough better to justify the switchover costs? BitKeeper ist not open source, so it's out of the question for most people. Subversion is shockingly unstable. I'm very open for something that replaces CVS, but I'd rather not use any than one of these two. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Windows binary in the beta directory?
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe > Sent: 09 August 2004 06:31 > To: PostgreSQL-development > Subject: [HACKERS] Windows binary in the beta directory? > > Since this is the first release supporting Windows natively, > and Windows people tend to not have any development > environment by default, should there be a windows binary > version of some sort into the beta directory, or is that > something that will come along later with setup.exe type > packaging or something? I hope to roll one today... /D ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] SRFs ExecMakeTableFunctionResult
Greets, While I was finishing up SRF support in PL/Py, I noticed that when VPC is the selected mode for a table function, ExecMakeTableFunctionResult will set rsinfo.isDone to ExprSingleResult each time it loops to fetch another value (when a direct_function_call). This makes the VPC-SRF author set isDone to ExprMultipleResult on _every_ call while returning values, as it will break out if rsinfo.isDone != ExprMultipleResult. Is this the desired behavior? -- Regards, James William Pye pgp5jFzzXeeDI.pgp Description: PGP signature
Re: [HACKERS] 8.0.0beta1 ... packaged for testing ...
Marc G. Fournier wrote: > > Yeah, sure wish we could generate those man pages automatically :-( > > Is there a reason why we can't? Usually, you need to look them over and fix them up a bit. You also need a patched version of the processing tools. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org