Re: [HACKERS] Bug with initDB under windows 2003
Hi Magnus, Magnus Wrote: I'dbeinterestedinseeingtheoutputfromthecommand: Subinacl/serviceNULL Onasystemwherethisdoesnotwork. Here is the output for "Subinacl/serviceNULL" Both, for the Administrator user and for the Postgres user: Postgres user: Subinacl /service NULLSeSecurityPrivilege : Access is denied. WARNING :Unable to set SeSecurityPrivilege privilege. This privilege may be required.Error OpenSCManager : Access is denied. Elapsed Time: 00 00:00:00Done: 0, Modified 0, Failed 0, Syntax errors 0 -- Administrator user: ==+Service NULL==/control=0x0/owner =system/primary group =system/audit ace count =1/aace =everyone SYSTEM_AUDIT_ACE_TYPE-0x2 FAILED_ACCESS_ACE_FLAG-0x80 FAILED_ACCESS_ACE_FLAG-0x0x80 SERVICE_ALL_ACCESS/perm. ace count =6/pace =system ACCESS_ALLOWED_ACE_TYPE-0x0 SERVICE_QUERY_CONFIG-0x1 SERVICE_QUERY_STATUS-0x4 SERVICE_ENUMERATE_DEPEND-0x8 SERVICE_START-0x10 SERVICE_STOP-0x20 SERVICE_PAUSE_CONTINUE-0x40_INTERROGATE-0x80 READ_CONTROL-0x2 SERVICE_USER_DEFINED_CONTROL-0x0100/pace =builtin\administrators ACCESS_ALLOWED_ACE_TYPE-0x0 SERVICE_ALL_ACCESS/pace =interactive ACCESS_ALLOWED_ACE_TYPE-0x0 SERVICE_QUERY_CONFIG-0x1 SERVICE_QUERY_STATUS-0x4 SERVICE_ENUMERATE_DEPEND-0x8 SERVICE_INTERROGATE-0x80 READ_CONTROL-0x2 SERVICE_USER_DEFINED_CONTROL-0x0100/pace =service ACCESS_ALLOWED_ACE_TYPE-0x0 SERVICE_QUERY_CONFIG-0x1 SERVICE_QUERY_STATUS-0x4 SERVICE_ENUMERATE_DEPEND-0x8 SERVICE_INTERROGATE-0x80 READ_CONTROL-0x2 SERVICE_USER_DEFINED_CONTROL-0x0100/pace =authenticated users ACCESS_ALLOWED_ACE_TYPE-0x0 SERVICE_USER_DEFINED_CONTROL-0x0100/pace =builtin\power users ACCESS_ALLOWED_ACE_TYPE-0x0 SERVICE_QUERY_CONFIG-0x1 SERVICE_QUERY_STATUS-0x4 SERVICE_ENUMERATE_DEPEND-0x8 SERVICE_START-0x10 SERVICE_STOP-0x20 SERVICE_PAUSE_CONTINUE-0x40_INTERROGATE-0x80 READ_CONTROL-0x2 SERVICE_USER_DEFINED_CONTROL-0x0100 Elapsed Time: 00 00:00:00Done: 1, Modified 0, Failed 0, Syntax errors 0Last Done : NULL As you can see it failed toset SeSecurityPrivilege privilege for the Postgres user. Regards Dror; Subject: Re: [HACKERS] Bug with initDB under windows 2003 Date: Fri, 18 Aug 2006 14:33:58 +0200 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; kleptog@svana.org; [EMAIL PROTECTED] CC: pgsql-hackers@postgresql.org Haveyoubeenabletodetermine*why*Microsoftmadethis braindead decision?Orwhereit'sdocumented?Theirownknowledgebaseis filled withexamplesofusingthedevice,soIimaginethey'llhaveto posta workaroundsomewhere... AFAIK,nobodyhaspostedanylinkstoinformationthatshowsthat Microsoftactually*did*changethis.I'vesearchedtheirKBsome (includingthepartner-onlyoneforpeoplewiththepaid agreements,whichcontainsinformationaboutbugsthattheydon't wanttobepublic),andfindnothingaboutit. Unlessyoucanreproducethisonacleansystem,I'mdefinitely inclinedtosaythisiscausedbysomeotherpieceofsoftwareon themachine-firewall,antivirus,antispywareorvirus/spyware itself. Actually,I'vebeenabletofindsomemoreinformationaboutthis,which confirmsthattheproblemisaccessrightsontheNULdevice,butit's *NOT*setbyaMSsecuritypatch. I'dbeinterestedinseeingtheoutputfromthecommand: Subinacl/serviceNULL Onasystemwherethisdoesnotwork. (Ifyougetanemptyupdate,makesureyouhaveaversionofsubinacl thatcorrespondstoyourwindowsversion) (Thereareknownappsthatmessthisup,butmyNDApreventsmefrom tellingyouwhichone(s)...) //Magnus ---(endofbroadcast)--- TIP4:Haveyousearchedourlistarchives? http://archives.postgresql.orgBe one of the first to try Windows Live Mail. Windows Live Mail.
Re: [HACKERS] OTRS
OTRS was recommended to me as a bug tracker. Has anyone used that? Nope, but looking at their demo i tseems they're actually *using* the blink HTML tag. Which probably should disqualify it out of hand ;-) That's just evil... //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_dump versus SERIAL, round N
Tom Lane wrote: Almost everything I just said is already how it works today; the difference is that today you do not have the option to drop t1 without dropping the sequence, because there's no (non-hack) way to remove the dependency. As far as I understand your proposal I like it, but I'd like to insure that the situation where a sequence is used by multiple tables is handled correctly. There _are_ databases that reuse a sequence for multiple serial-like columns, and pgadmin supports this (including a pg_depend insert, which would need a version dependent fix). Regards, Andreas ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_dump versus SERIAL, round N
Andreas Pflug wrote: Tom Lane wrote: Almost everything I just said is already how it works today; the difference is that today you do not have the option to drop t1 without dropping the sequence, because there's no (non-hack) way to remove the dependency. As far as I understand your proposal I like it, but I'd like to insure that the situation where a sequence is used by multiple tables is handled correctly. There _are_ databases that reuse a sequence for multiple serial-like columns, and pgadmin supports this (including a pg_depend insert, which would need a version dependent fix). If we were implementing serial from scratch, I would be arguing that the underlying sequence should be merely an implementation detail that should be totally hidden, and sequences used explicitly should be kept as a separate concept. Then many of these problems simply wouldn't exist. I realise that might be difficult to get to now :-( cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Coverity reports looking good
I thought I'd like to report that right now the Coverity reports are looking good. There are no issues detected in either the backend code or the ECPG library. For the latter I'd like the thank Joachim Wieland and Michael Meskes for getting ECPG into shape. Whats basically left is a large number of memory leaks in frontend applications such as pg_dump, initdb, pg_ctl, etc. These haven't ever really been a priority (buildACLCommands is really bad in this respect). Some examples can be found here: http://svana.org/kleptog/temp/pgsql-bin.xml If someone wants to make a serious attempt at tackling them, I can provide an updated list. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] pg_dump versus SERIAL, round N
Andreas Pflug [EMAIL PROTECTED] writes: As far as I understand your proposal I like it, but I'd like to insure that the situation where a sequence is used by multiple tables is handled correctly. There _are_ databases that reuse a sequence for multiple serial-like columns, and pgadmin supports this (including a pg_depend insert, which would need a version dependent fix). What do you think is correctly? It's already the case in 8.1 that saying DEFAULT nextval('foo_seq') generates a normal dependency from the column default expression to foo_seq, which means that you can't drop the sequence without saying CASCADE, and if you say that then all the dependent defaults are dropped. My recommendation for a multi-table scenario would be that the sequence be created by hand as a standalone object, in which case those normal dependencies are the only ones involved, and my proposal won't change the behavior at all from what it was in 8.1. If you insist on initially creating the sequence by saying SERIAL for the first of the tables, and then saying DEFAULT nextval('foo_seq') for the rest, then under both 8.1 and my proposal you'd not be able to drop the first table without dropping the sequence (thus requiring you to say CASCADE so that the other tables' defaults can be dropped). The difference is that I'm proposing a way to decouple the sequence from its original owning column and make it into a true freestanding object, after which you could drop the first table without losing the sequence and the other defaults. Basically the proposed command allows you to convert from the case where a sequence was created by SERIAL to the case where it was created free-standing, or vice versa. The other change is that using an AUTO instead of INTERNAL dependency makes it legal to drop the sequence without dropping the column. My testing version does this: regression=# create table zit (f1 serial); NOTICE: CREATE TABLE will create implicit sequence zit_f1_seq for serial column zit.f1 CREATE TABLE regression=# drop sequence zit_f1_seq; NOTICE: default for table zit column f1 depends on sequence zit_f1_seq ERROR: cannot drop sequence zit_f1_seq because other objects depend on it HINT: Use DROP ... CASCADE to drop the dependent objects too. regression=# drop sequence zit_f1_seq cascade; NOTICE: drop cascades to default for table zit column f1 DROP SEQUENCE regression=# \d zit Table public.zit Column | Type | Modifiers +-+--- f1 | integer | not null regression=# Previous versions would have disallowed that DROP even with CASCADE; the *only* way to get rid of a SERIAL-created sequence was to drop the owning column (or its whole table) altogether. If you try the same thing in 8.1 you get regression=# drop sequence zit_f1_seq cascade; ERROR: cannot drop sequence zit_f1_seq because table zit column f1 requires it HINT: You may drop table zit column f1 instead. AFAICS this doesn't disallow anything you could do before, and it allows fixing the problems pg_dump is having. Is there something you need it to do that it doesn't do? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump versus SERIAL, round N
Andrew Dunstan [EMAIL PROTECTED] writes: If we were implementing serial from scratch, I would be arguing that the underlying sequence should be merely an implementation detail that should be totally hidden, and sequences used explicitly should be kept as a separate concept. Then many of these problems simply wouldn't exist. I realise that might be difficult to get to now :-( Well, we're not in a green field anymore :-(. In any case there would be some serious practical disadvantages in trying to hide the underlying sequence fully: * you couldn't use ALTER SEQUENCE, eg to adjust the sequence's CYCLE property, which seems like a useful thing to do; * permissions management would get interesting too; * how's pg_dump going to access the sequence to restore its correct count value etc? I think we'd end up building a lot of facilities parallel to those that exist for ordinary sequences, and then this doesn't seem like such a clean solution anymore... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Coverity reports looking good
On Sun, Aug 20, 2006 at 03:49:24PM +0200, Martijn van Oosterhout wrote: Whats basically left is a large number of memory leaks in frontend applications such as pg_dump, initdb, pg_ctl, etc. These haven't ever really been a priority (buildACLCommands is really bad in this respect). ... If someone wants to make a serious attempt at tackling them, I can provide an updated list. If my time permits I'm willing to look into these a little bit as I now know a little bit about Coverity reports. And since I found that these reports not only show potential memory leaks (which I don't worry about much in short lived frontend apps) they sometimes point to bugs that may cause real problems. I just had a small look at one in pg_dump.c. However, it seems the line numbers are completely different from my CVS snapshot. Are the Coverity reports you listed up-to-date? Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Coverity reports looking good
Martijn van Oosterhout kleptog@svana.org writes: Whats basically left is a large number of memory leaks in frontend applications such as pg_dump, initdb, pg_ctl, etc. These haven't ever really been a priority (buildACLCommands is really bad in this respect). Well, if Coverity's idea of good programming practice is that every program must explicitly free everything it ever malloced before it terminates, then I'm afraid we'll have to agree to disagree. The above is nothing but make-work as far as programs with short intended runtimes go. A leak in psql would be interesting if it can accumulate across command-execution cycles, but I have zero interest in cleaning up any of the programs mentioned above. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Coverity reports looking good
Martijn van Oosterhout wrote: I thought I'd like to report that right now the Coverity reports are looking good. There are no issues detected in either the backend code or the ECPG library. For the latter I'd like the thank Joachim Wieland and Michael Meskes for getting ECPG into shape. Whats basically left is a large number of memory leaks in frontend applications such as pg_dump, initdb, pg_ctl, etc. These haven't ever really been a priority (buildACLCommands is really bad in this respect). Some examples can be found here: http://svana.org/kleptog/temp/pgsql-bin.xml If someone wants to make a serious attempt at tackling them, I can provide an updated list. At least in the case of initdb, when I was coding it I was perfectly well aware that there would be memory leaks - and the code contains a comment to the effect that cleaning them up isn't worth the trouble. I think we can be more liberal in programs that are not long-lived and won't continue to leak more and more memory. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] default child of partition master
I'm using partitioned tables a lot and loving it. I have a suggestion that I believe would make it easier to bring the performance and maintenance advantages of partitions to more applications and users: As I understand it, at present I can select records from sales_master and get data returned from all tables that inherit sales_master: sales_2006_q1 sales_2006_q2 sales_2006_q3 ... But INSERT and certain other operations must specify the destination table. (Only makes sense.) My suggestion is to allow specifying a default destination table in the master partition table definition. This default destination table could be changed with ALTER TABLE. This would make it simpler to adapt existing applications to use partitions. Instead of having to edit the table name in every location in every application that accesses a particular table - the partition master can be created with the name all those applications expect. The existing data is then placed into partitions split by date - and the applications continue to function as expected. A scheduled process creates a new table that inherits the master as needed: sales_2006_q4, sales_2007_q1 etc - inheriting from sales_master. And each time, sales_master is altered to set the default table to, for instance sales_2006_q4. The need I have for this right now is for dbmail - a mail store in SQL that supports both mysql and postgresql. It's pretty good - I've been using it for some years - but they don't seem to know much about postgresql. The table that holds the message blocks has a huge amount of deletes and inserts. They do have a maintenance util which runs on a cron frequently but... this is a live mail system - one cannot do operations that prevent mail being delivered - and the run times for full vacuums on the whole message blocks table or whole db are huge. This table would be extremely enhanced by partitions split on date. However it is extermely unlikely that I could get the dbmail developers (and everyone else who has written related apps that access the dbmail tables, including me) to alter their code so that it selects from a master table but inserts to an *ever-changing other table*. For one thing, every type of maintenance could be done on the older tables without affecting mail delivery into the current table. Users who access only recent mail wouldn't suffer due to the size of the table caused by users who keep a lot of mail on the server. Etc. If the specify a default table for INSERTs (and other operations that can't be sent to the partition master) feature were added - it would allow users of postgresql and dbmail - (and many other apps in similar situations) to independently choose to run partitions. Thank you for your consideration, - April Lorenzen ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] default child of partition master
April Lorenzen [EMAIL PROTECTED] writes: My suggestion is to allow specifying a default destination table in the master partition table definition. This default destination table could be changed with ALTER TABLE. The recommended way to do this is with an ON INSERT DO INSTEAD rule on the master table. The advantage of using rules is that you can have several conditional rules to ensure that insertions go into the right sub-tables, whereas a default would likely just fail :-( regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] default child of partition master
Keep in mind if you have multiple rules for a master table, it won't return the number of affected rows as you might expect. This screws up Hibernate which I'm using for my application. It checks the return value to make sure it was inserted properly. Luckily I only need one rule which puts it into the current child table (im partitioning on current timestamp). I suppose I could get around this by using a stored procedure or something but that would not be as portable. I'm looking forward to future versions of PG which automate more of the partitioning features :) keep up the good work! GeneOn 8/20/06, Tom Lane [EMAIL PROTECTED] wrote: April Lorenzen [EMAIL PROTECTED] writes: My suggestion is to allow specifying a default destination table in the master partition table definition. This default destination table could be changed with ALTER TABLE.The recommended way to do this is with an ON INSERT DO INSTEAD ruleon the master table.The advantage of using rules is that you can haveseveral conditional rules to ensure that insertions go into the right sub-tables, whereas a default would likely just fail :-(regards, tom lane---(end of broadcast)---TIP 6: explain analyze is your friend -- Eugene Hart
Re: [HACKERS] pg_dump versus SERIAL, round N
Tom Lane wrote: If you insist on initially creating the sequence by saying SERIAL for the first of the tables, and then saying DEFAULT nextval('foo_seq') for the rest, then under both 8.1 and my proposal you'd not be able to drop the first table without dropping the sequence (thus requiring you to say CASCADE so that the other tables' defaults can be dropped). The difference is that I'm proposing a way to decouple the sequence from its original owning column and make it into a true freestanding object, after which you could drop the first table without losing the sequence and the other defaults. For decoupling, you'd require ALTER SEQUENCE ... OWNER BY NONE to be executed, right? I basically doubt the concept of a single owner. I'd expect a sequence to be dropped from cascaded table dropping, if that was the last usage and dependencies existed. This would probably mean multiple owners. Basically the proposed command allows you to convert from the case where a sequence was created by SERIAL to the case where it was created free-standing, or vice versa. The other change is that using an AUTO instead of INTERNAL dependency makes it legal to drop the sequence without dropping the column. Sounds fine. AFAICS this doesn't disallow anything you could do before, and it allows fixing the problems pg_dump is having. Is there something you need it to do that it doesn't do? Sequence cleanup with multiple tables (multiple owners). Regards, Andreas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Coverity reports looking good
On Sun, Aug 20, 2006 at 11:52:53AM -0400, Tom Lane wrote: Well, if Coverity's idea of good programming practice is that every program must explicitly free everything it ever malloced before it terminates, then I'm afraid we'll have to agree to disagree. The I don't think Coverity even knows how long a program may run. But while I agree that freeing all memory is not needed in a short running program, I still think we should examine those reports because they may show some real bugs. At least this is what happened to some degree in ecpg. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_dump versus SERIAL, round N
Andreas Pflug [EMAIL PROTECTED] writes: I basically doubt the concept of a single owner. I'd expect a sequence to be dropped from cascaded table dropping, if that was the last usage and dependencies existed. This would probably mean multiple owners. That's not going to happen without extensive revisions to our dependency mechanisms, which I am not about to undertake now. And I don't see the point anyway. If you did have a sequence being used to feed multiple tables, why would you want it to go away if the number of tables dropped transiently to zero? If you then want to add back another table being fed by that sequence, you've lost the state of the sequence. That's the same sort of corner case that prompted us to allow zero-column tables, ie, the table can continue to exist even if it momentarily has no columns. I see SERIAL as a simple shorthand for a common case, not some hydra-headed beast ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_dump versus SERIAL, round N
Bruce Momjian [EMAIL PROTECTED] writes: What method will people use to see if a sequence used as a default is one that was created by SERIAL, and will be dropped by drop table, or manually created? How does that distinction show up in pg_dump? Hm. It will show in pg_dump because there will (or won't) be an ALTER SEQUENCE OWNED BY command, but right now the only way to see if a sequence is owned is to look in pg_depend for a link to a table column. That's how it's always been before, too --- have you noticed any complaints? We could consider adding something to psql's \ds display to show ownership, but that's definitely getting into the realm of new feature rather than bug fix, and given the lack of past requests for it I can't say that I find it to be an immediate must-have. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_dump versus SERIAL, round N
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: I basically doubt the concept of a single owner. I'd expect a sequence to be dropped from cascaded table dropping, if that was the last usage and dependencies existed. This would probably mean multiple owners. That's not going to happen without extensive revisions to our dependency mechanisms, which I am not about to undertake now. And I don't see the point anyway. If you did have a sequence being used to feed multiple tables, why would you want it to go away if the number of tables dropped transiently to zero? If you then want to add back another table being fed by that sequence, you've lost the state of the sequence. That's the same sort of corner case that prompted us to allow zero-column tables, ie, the table can continue to exist even if it momentarily has no columns. Agreed. I think it makes sense that if SERIAL auto-created the column, dropping the table should remove the sequence, but if the sequence was created manually, drop table should not drop it automatically. What method will people use to see if a sequence used as a default is one that was created by SERIAL, and will be dropped by drop table, or manually created? How does that distinction show up in pg_dump? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_dump versus SERIAL, round N
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: What method will people use to see if a sequence used as a default is one that was created by SERIAL, and will be dropped by drop table, or manually created? How does that distinction show up in pg_dump? Hm. It will show in pg_dump because there will (or won't) be an ALTER SEQUENCE OWNED BY command, but right now the only way to see if a sequence is owned is to look in pg_depend for a link to a table column. That's how it's always been before, too --- have you noticed any complaints? We could consider adding something to psql's \ds display to show ownership, but that's definitely getting into the realm of new feature rather than bug fix, and given the lack of past requests for it I can't say that I find it to be an immediate must-have. Right. My only point is that right now SERIAL shows up in pg_dump, while in the future it will show up as SEQUENCE OWNED BY. We just need to look out if people get confused. Also, if someone restores one table, does the sequence come with it like it does now with SERIAL? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Coverity reports looking good
On Sun, Aug 20, 2006 at 11:52:53AM -0400, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: Whats basically left is a large number of memory leaks in frontend applications such as pg_dump, initdb, pg_ctl, etc. These haven't ever really been a priority (buildACLCommands is really bad in this respect). Well, if Coverity's idea of good programming practice is that every program must explicitly free everything it ever malloced before it terminates, then I'm afraid we'll have to agree to disagree. The above is nothing but make-work as far as programs with short intended runtimes go. A leak in psql would be interesting if it can accumulate across command-execution cycles, but I have zero interest in cleaning up any of the programs mentioned above. Each of the reported issues should be investigated, for however short, to see whether it is intentional or not. For example, if the memory is allocated within a loop, or which the bounds are not fixed, even a short running program can benefit from being fixed. If it is just configuration data represented in memory, created once, who cares... :-) Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Windows 2000 Support
(btw, clinically insane without patching it.. And obviously you didn't patch yours? :-P) Yeah, well it's behind all manner of firewalls, doing nothing but buildfarm runs of which I ran the first before WSUS had installed all the pending updates :-p Excuses, excuses... ;) //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump versus SERIAL, round N
Bruce Momjian [EMAIL PROTECTED] writes: Also, if someone restores one table, does the sequence come with it like it does now with SERIAL? Hm, probably not. I do have pg_dump set to force dumping of the sequence if you try to dump just its table, but it'd be possible to tell pg_restore (via -l) to load only the table, which could fail. I'm not too excited about that though; there are plenty of other ways to tell pg_restore to restore a database subset that won't work. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump versus SERIAL, round N
Bruce Momjian [EMAIL PROTECTED] writes: What method will people use to see if a sequence used as a default is one that was created by SERIAL, and will be dropped by drop table, or manually created? How does that distinction show up in pg_dump? BTW, it's easy to see if a column has an associated sequence: pg_get_serial_sequence(). It's going from the sequence to the column that requires manually looking into pg_depend. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] PostgreSQL on 64 bit Linux
I have a PostgreSQL installation on a Debian box that had the 64bit SMP kernel installed before PostgreSQL was compiled and installed on it. Does PostgreSQL take any advantage of the 64 bit environment or have we not done anything to move into the 64 bit world yet? Regards, - Naz ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PostgreSQL on 64 bit Linux
Naz Gassiep [EMAIL PROTECTED] writes: I have a PostgreSQL installation on a Debian box that had the 64bit SMP kernel installed before PostgreSQL was compiled and installed on it. Does PostgreSQL take any advantage of the 64 bit environment or have we not done anything to move into the 64 bit world yet? Depends on whether PG was compiled as 64-bit or 32-bit--is your toolchain 64-bit all the way, or is it just the kernel? -Doug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL on 64 bit Linux
Douglas McNaught wrote: Naz Gassiep [EMAIL PROTECTED] writes: I have a PostgreSQL installation on a Debian box that had the 64bit SMP kernel installed before PostgreSQL was compiled and installed on it. Does PostgreSQL take any advantage of the 64 bit environment or have we not done anything to move into the 64 bit world yet? Depends on whether PG was compiled as 64-bit or 32-bit--is your toolchain 64-bit all the way, or is it just the kernel? -Doug I just compiled as the manual says. I guess I must have compiled it in 32. I'll recompile in 64 when I upgrade to 8.2 when it's out. Thanks, - Naz.
Re: [HACKERS] PostgreSQL on 64 bit Linux
Naz Gassiep [EMAIL PROTECTED] writes: I just compiled as the manual says. I guess I must have compiled it in 32. I'll recompile in 64 when I upgrade to 8.2 when it's out. The 'file' command will tell you whether a binary is 32- or 64-bit. If you have a full 64-bit install, you'll get a 64-bit compile by default, but it sounds like you just added a 64-bit kernel to a 32-bit Debian system? -Doug ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] [PATCH] Provide 8-byte transaction IDs to
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: The part of this that would actually be useful to put in core is maintaining a 64-bit XID counter, ie, keep an additional counter that bumps every time XID wraps around. This cannot be done very well from outside core but it would be nearly trivial, and nearly free, to add inside. Everything else in the patch could be done just as well as an extension datatype. (I wouldn't do it like this though --- TransactionIdAdvance itself is the place to bump the secondary counter.) Agreed. I reconsidered after trying to do it that way --- although fixing TransactionIdAdvance itself to maintain a 2-word counter isn't hard, there are a whole lot of other places that can advance nextXid, mostly bits like this in WAL recovery: /* Make sure nextXid is beyond any XID mentioned in the record */ max_xid = xid; for (i = 0; i xlrec-nsubxacts; i++) { if (TransactionIdPrecedes(max_xid, sub_xids[i])) max_xid = sub_xids[i]; } if (TransactionIdFollowsOrEquals(max_xid, ShmemVariableCache-nextXid)) { ShmemVariableCache-nextXid = max_xid; TransactionIdAdvance(ShmemVariableCache-nextXid); } We could hack all these places to know about maintaining an XID-epoch value, but it's not looking like a simple single-place-to-touch fix :-( So I'm now agreeing that the approach of maintaining an epoch counter in checkpoints is best after all. That will work so long as the system doesn't exceed 4G transactions between checkpoints ... and you'd have a ton of other problems before that, so this restriction does not bother me. Putting this in the core code still beats the alternatives available to non-core code because of the impossibility of being sure you get control on any fixed schedule, not to mention considerations of what happens during WAL replay and PITR. There's still a lot more cruft in the submitted patch than I think belongs in core, but I'll work on extracting something we can apply. There was some worry upthread about whether Slony would actually use this in the near future, but certainly if we don't put it in then they'll *never* be able to use it. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] OTRS
All, We use OTRS for customer issue tracking, but we use Jira from Atlassian for our internal bug tracking. We also use Confluence and Fisheye from Atlassian, and they are all excellent tools IMO. OTRS has been OK and fairly malleable. Actually, having done a fairly significant amount of the hacking on OTRS, I can't recommend it. The code is a mess, and very hard to modify. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] DROP OWNED BY doesn't work
I found one other problem in this area, which was that REASSIGN OWNED didn't work real well either after I changed serial sequences' dependency type to AUTO. What I did about it was to make shdepReassignOwned call ATExecChangeOwner with recursing = true, which suppresses all those tedious error checks ;-). This means we don't need objectIsInternalDependency() at all anymore: just barrel ahead and try to change owner on everything. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL on 64 bit Linux
On Sun, Aug 20, 2006 at 04:46:30PM -0400, Douglas McNaught wrote: Naz Gassiep [EMAIL PROTECTED] writes: I have a PostgreSQL installation on a Debian box that had the 64bit SMP kernel installed before PostgreSQL was compiled and installed on it. Does PostgreSQL take any advantage of the 64 bit environment or have we not done anything to move into the 64 bit world yet? Depends on whether PG was compiled as 64-bit or 32-bit--is your toolchain 64-bit all the way, or is it just the kernel? I think he means - have benchmarks, or profiling been done with the goal of specifically improving performance on 64-bit platforms. For most applications available today, the answer is no. Compiling an application designed for 32-bit, on a 64-bit architecture, does not automatically improve performance. Too frequently, it can actually reduce performance. Pointers are large, which means that any application that is heavily pointer based can be forced to deal with twice as many copies of memory, which reduces the effectiveness of the various cache levels, and RAM itself. Hopefully GLIBC counts here, in that it should contain 64-bit specific code where it might count, so libc calls should be able to take advantage of the 64-bit machine instructions. Is there an interest, or any active project to examine PostgreSQL in the area of 64-bit processors? Has it already been done? I don't recall seeing a reference to it in my travels. I'm also not sure on what to expect for results, as the territory is still new. 64-bit processors have existed for a while, but 32-bit processors have been the popular choice, making 64-bit support an after thought? Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PostgreSQL on 64 bit Linux
On 8/21/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Is there an interest, or any active project to examine PostgreSQL in the area of 64-bit processors? Has it already been done? I don't recall seeing a reference to it in my travels. I'm also not sure on what to expect for results, as the territory is still new. 64-bit processors have existed for a while, but 32-bit processors have been the popular choice, making 64-bit support an after thought? That's certainly just a reference to the wintel world? AIX, HP-UX and Solaris-Sparc have been 64-bit for a while now... Cheers, mark Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL on 64 bit Linux
[EMAIL PROTECTED] writes: Is there an interest, or any active project to examine PostgreSQL in the area of 64-bit processors? Has it already been done? I don't recall seeing a reference to it in my travels. I'm also not sure on what to expect for results, as the territory is still new. 64-bit processors have existed for a while, but 32-bit processors have been the popular choice, making 64-bit support an after thought? I find this question a bit amusing, since PG has run on 64-bit architectures such as MIPS, Sparc, Alpha and PA-RISC for quite a while now. :) As I said in a private email to Naz, the main advantage I think you'd see from 64-bit is the ability to run with more than 2GB or so of shared buffers on a system with lots of RAM. Whether you'd want to do that, or let the OS do most of the buffering, is an open question... -Doug ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL on 64 bit Linux
Naz, On 8/20/06 12:59 PM, Naz Gassiep [EMAIL PROTECTED] wrote: I have a PostgreSQL installation on a Debian box that had the 64bit SMP kernel installed before PostgreSQL was compiled and installed on it. Does PostgreSQL take any advantage of the 64 bit environment or have we not done anything to move into the 64 bit world yet? Very likely the default gcc compiles for 64-bit, if not you need to specify -m64. As another respondent said - do a file `which initdb` to find out whether you have compiled for 64-bit or not. WRT 64-bit and Postgres, it depends on the CPU as to whether you see a simple performance benefit. On the Opteron you will see a benefit when doing CPU bound work. When doing the CPU portion, the additional registers of the Opteron running in 64-bit mode are used by the compiler to produce a 20-30% boost in performance. On the Xeon in 64-bit mode, the same regions of execution will slow down by about 5%. Postgres benefits automatically from the larger memory addressing of the 64-bit kernel by using the larger I/O cache of Linux. - Luke ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PostgreSQL on 64 bit Linux
WRT 64-bit and Postgres, it depends on the CPU as to whether you see a simple performance benefit. On the Opteron you will see a benefit when doing CPU bound work. When doing the CPU portion, the additional registers of the Opteron running in 64-bit mode are used by the compiler to produce a 20-30% boost in performance. On the Xeon in 64-bit mode, the same regions of execution will slow down by about 5%. Is that true of even Woodcrest? Joshua D. Drake Postgres benefits automatically from the larger memory addressing of the 64-bit kernel by using the larger I/O cache of Linux. - Luke ---(end of broadcast)--- TIP 6: explain analyze is your friend -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Replication
One person who commented on the The business of Postbrsql made this comment: Posted Aug 3, 2006 8:45 UTC (Thu) by subscriber *jgarzik* [Link http://lwn.net/Articles/193946/]Cluster immaturity. MySQL has been shipping a workable single-master replication+failover for quite a while now in most Linux distros. MySQL's multi-master solution, while requiring RAM (not disk) for storage, is also well-integrated and deployed in production. In contrast, the PostgreSQL team has chosen to provide hooks for replication and failover. This has led to a situation where there are multiple projects supporting replications/failover, none of which are production-ready nor shipped in a modern Linux distro. Modern systems *must* scale beyond a single computer, and the PostgreSQL support shipped in modern Linux distros is completely incapable of this. I really would appreciate a response. Thanks~ Michael -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.3/423 - Release Date: 8/18/2006 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Replication
In contrast, the PostgreSQL team has chosen to provide hooks for replication and failover. This has led to a situation where there are multiple projects supporting replications/failover, none of which are production-ready nor shipped in a modern Linux distro. And no, we don't really provide hooks :). However there are several projects trying to solve different problems with PostgreSQL. Modern systems *must* scale beyond a single computer, and the PostgreSQL support shipped in modern Linux distros is completely incapable of this. Slony-I is quite capable as a production class FOSS replication system and is in use widely. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PostgreSQL on 64 bit Linux
Josh, On 8/20/06 8:52 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: Is that true of even Woodcrest? Joshua D. Drake Not sure - haven't read anything about the register set on the Core 2 to make me think it benefits from 64 bit. The point may be academic from now on though - the comparisons between Opteron and Core 2 will all likely be in 64-bit mode from now on. - Luke ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Costs estimates for (inline SQL) functions ...
As my last mail did not seem to go through here one more try ... When looking at some fairly complex SQL stuff I came across some interesting issue which is a bit surprising to me: CREATE OR REPLACE FUNCTION xy() RETURNS SETOF record AS $$ SELECT relname::text, relpages::int4 FROM pg_class; $$ LANGUAGE SQL IMMUTABLE; explain SELECT * FROM xy() AS (relname text, relpages int4); [EMAIL PROTECTED]:/tmp$ psql test check.sql CREATE FUNCTION QUERY PLAN Function Scan on xy (cost=0.00..12.50 rows=1000 width=36) (1 row) As far as i remember inlined SQL code has been implemented into the planner around 7.4. This should also be true according to ... http://conferences.oreillynet.com/cs/os2003/view/e_sess/4372 In my theory the function is inlined and therefore we should see different costs here (the ones of the real query). Does anybody happen to know more about this issue? Many thanks, hans -- Cybertec Geschwinde Schönig GmbH Schöngrabern 134; A-2020 Hollabrunn Tel: +43/1/205 10 35 / 340 www.postgresql.at, www.cybertec.at ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Costs estimates for (inline SQL) functions ...
On Mon, 2006-08-21 at 07:31 +0200, Hans-Juergen Schoenig wrote: CREATE OR REPLACE FUNCTION xy() RETURNS SETOF record AS $$ SELECT relname::text, relpages::int4 FROM pg_class; $$ LANGUAGE SQL IMMUTABLE; As far as i remember inlined SQL code has been implemented into the planner around 7.4. We don't currently inline set-returning functions. This has been on my TODO list for a while, but I haven't found the cycles for it yet (of course, if anyone's interested in fixing this, don't let my idleness stop you -- I would think it shouldn't be too difficult). -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq