Re: [HACKERS] [PATCHES] [EMAIL PROTECTED]: Re: [BUGS] Problem identifying constraints which should not be inherited]
Hi, On Sat, May 10, 2008 at 6:11 AM, Alex Hunsaker [EMAIL PROTECTED] wrote: On Fri, May 9, 2008 at 5:37 PM, Tom Lane [EMAIL PROTECTED] wrote: Alex Hunsaker [EMAIL PROTECTED] writes: [ patch to change inherited-check-constraint behavior ] Applied after rather heavy editorializations. You didn't do very well on getting it to work in multiple-inheritance scenarios, such as create table p (f1 int check (f10)); create table c1 (f2 int) inherits (p); create table c2 (f3 int) inherits (p); create table cc () inherits (c1,c2); Here the same constraint is multiply inherited. The base case as above worked okay, but adding the constraint to an existing inheritance tree via ALTER TABLE, not so much. Ouch. Ok Ill (obviously) review what you committed so I can do a lot better next time. Thanks for muddling through it! Ouchie indeed! I'm not sure if we ought to try to back-patch that --- it'd be a behavioral change with non-obvious implications. In the back branches, ADD CHECK followed by DROP CONSTRAINT will end up not deleting the child-table constraints, which is probably a bug but I wouldn't be surprised if applications were depending on the behavior. Given the lack complaints it does not seem worth a back patch IMHO. Yeah, same IMHO. I do hope we have covered things properly for inherited check constraints by now. One minor thing that myself and Alex discussed was the usage of child tables in tablecmds.c, especially in error messages. Again English is not my native language, but shouldn't that be worded as children tables? Admittedly even this does not sound any better than child tables though :). It is nit-picking really, but I can submit a cleanup patch to reword this if the list thinks so.. Regards, Nikhils -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Setting a pre-existing index as a primary key
Joshua D. Drake wrote: Tom Lane wrote: Well it should be optional but it would be nice if we had the option to have it renamed per the default... meaning the same output if I were to do this: If you want that, you can rename the index (either before or afterwards). I don't see any reason to clutter the make-constraint-from-index command with questions of renaming. As a counter point, I don't see any reason to make the DBA's life harder. Sure it is just one step but it is a human step, prone to error and taking more time than it should. Why not just make it easy? Especially when the easy isn't sacrificing data integrity or quality of product? well the name is by no means a functional problem. Its merely cosmetics, so if you want propose that a warning is issued to suggest a saner name. This should be sufficient I think. T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] another ecpg crash
On Sun, May 11, 2008 at 02:19:05AM -0300, Euler Taveira de Oliveira wrote: Alvaro Herrera wrote: Huh, isn't the test backwards? In which way? I use a simple one but whatever test that uses 'exec sql include foo' and foo.h doesn't exist, it will crash. I think he means specifically this line in the diff: ! /* there are some cases (i.e. file not found) ! * that the input is not available */ ! if (!yyin) ! fclose(yyin); This will close the file *only* if yyin is NULL, which probably isn't what is meant. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
[HACKERS] XIDs and big boxes again ...
hello everybody, i know that we have discussed this issue already. my view of the problem has changed in the past couple of weeks, however. maybe other people had similar experiences. i have been working on a special purpose application which basically looks like that: - 150.000 tables (for several reasons heavily constraint excluded): small changes made once in a while - XX medium sized tables which are heavily changed. - size: 5 TB my DB is facing around 600mio transaction a month. 85% of those contain at least some small modification so I cannot save on XIDs. my problem is that I cannot VACUUM FREEZE my 150k tables where most of the data is as I have a couple of thousand transactions a day modifying this data. but, i also have troubles to prevent myself from transaction wraparound as it is pretty boring to vacuum that much data under heavy load - with some useful vacuum delay it just takes too long. i basically have to vacuum the entire database too often to get spare XIDs. i suggest to introduce a --with-long-xids flag which would give me 62 / 64 bit XIDs per vacuum on the entire database. this should be fairly easy to implement. i am not too concerned about the size of the tuple header here - if we waste 500 gb of storage here i am totally fine. any chances to get a properly written fix like that in? maybe somebody else has similar problems? hannu krosing maybe? :-P hans -- Cybertec Schönig Schönig GmbH PostgreSQL Solutions and Support Gröhrmühlgasse 26, A-2700 Wiener Neustadt Tel: +43/1/205 10 35 / 340 www.postgresql-support.de, www.postgresql-support.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] XIDs and big boxes again ...
Hans-Juergen Schoenig [EMAIL PROTECTED] writes: my DB is facing around 600mio transaction a month. 85% of those contain at least some small modification so I cannot save on XIDs. What's a mio? Assuming it's short for million I don't see the problem. The transaction horizon is 2 *billion*. So as long as you can complete a vacuum of every table once every 3 months you should be fine. my problem is that I cannot VACUUM FREEZE my 150k tables where most of the data is as I have a couple of thousand transactions a day modifying this data. vacuum freeze doesn't take any locks. But in any case vacuum freeze would only extend the vacuum horizon by 100k so it would hardly make any difference. but, i also have troubles to prevent myself from transaction wraparound as it is pretty boring to vacuum that much data under heavy load - with some useful vacuum delay it just takes too long. How long is too long? i basically have to vacuum the entire database too often to get spare XIDs. How often is too often? i suggest to introduce a --with-long-xids flag which would give me 62 / 64 bit XIDs per vacuum on the entire database. this should be fairly easy to implement. i am not too concerned about the size of the tuple header here - if we waste 500 gb of storage here i am totally fine. Keep in mind you're proposing to make everything run 3% slower instead of using that 3% i/o bandwidth headroom to run vacuum outside the critical path. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] Database owner installable modules patch
On Sat, May 10, 2008 at 11:02 AM, Bruce Momjian [EMAIL PROTECTED] wrote: Where are we on this? I haven't had time to do any work since the original patch. That patch was fairly basic - it just ran install / uninstall scripts and created catalog entries, and introduced some slightly exotic syntax to do it (INSTALL/UNINSTALL vs CREATE/DROP). The next version is intended to handle dependencies, which should make uninstallation straight forward for most cases. I was intending to revert the syntax creativity and make the commands CREATE/DROP too. I'll get a bit of time to look at both this and the enum patch this week. Cheers Tom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] [EMAIL PROTECTED]: Re: [BUGS] Problem identifying constraints which should not be inherited]
Nikhils [EMAIL PROTECTED] writes: ... One minor thing that myself and Alex discussed was the usage of child tables in tablecmds.c, especially in error messages. Again English is not my native language, but shouldn't that be worded as children tables? Admittedly even this does not sound any better than child tables though :). No, child tables sounds better to me. English doesn't usually pluralize adjectives. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Setting a pre-existing index as a primary key
On Sat, May 10, 2008 at 10:41:56PM -0400, Andrew Sullivan wrote: On Sat, May 10, 2008 at 11:55:29AM -0400, Tom Lane wrote: IMHO a utility command should do one easily-explained thing. The fewer options the better. Sticking to that principle makes for a better-maintained system. I agree. If we want to point out, You might rename your index afterwards to make it look like other default primary keys, I have no objection. For convenience, it might be nice to include the generated name in the notice. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] XIDs and big boxes again ...
Gregory Stark [EMAIL PROTECTED] writes: ... Keep in mind you're proposing to make everything run 3% slower instead of using that 3% i/o bandwidth headroom to run vacuum outside the critical path. I think that's actually understating the problem. Assuming this is a 64-bit machine (which it had better be, if you want XID to be 64 bits...) then the effective increase in tuple header size is not just 12 bytes but 16 bytes, due to alignment padding. Greg's 3% overhead number is only on-target if your average row width is presently about 530 bytes. It could easily be a whole lot less than that, and the overhead proportionally higher. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] XIDs and big boxes again ...
Tom Lane wrote: Gregory Stark [EMAIL PROTECTED] writes: ... Keep in mind you're proposing to make everything run 3% slower instead of using that 3% i/o bandwidth headroom to run vacuum outside the critical path. I think that's actually understating the problem. Assuming this is a 64-bit machine (which it had better be, if you want XID to be 64 bits...) then the effective increase in tuple header size is not just 12 bytes but 16 bytes, due to alignment padding. Greg's 3% overhead number is only on-target if your average row width is presently about 530 bytes. It could easily be a whole lot less than that, and the overhead proportionally higher. regards, tom lane overhead is not an issue here - if i lose 10 or 15% i am totally fine as long as i can reduce vacuum overhead to an absolute minimum. overhead will vary with row sizes anyway - this is not the point. the point is that you don't want to potentially vacuum a table when only a handful of records has been changed. many thanks, hans -- Cybertec Schönig Schönig GmbH PostgreSQL Solutions and Support Gröhrmühlgasse 26, A-2700 Wiener Neustadt Tel: +43/1/205 10 35 / 340 www.postgresql-support.de, www.postgresql-support.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] XIDs and big boxes again ...
Hans-Juergen Schoenig [EMAIL PROTECTED] writes: overhead is not an issue here - if i lose 10 or 15% i am totally fine as long as i can reduce vacuum overhead to an absolute minimum. I cannot see the sanity of taking a ~10% hit on all I/O activity (especially foreground queries) to avoid having background vacuuming going on --- at least assuming that we can keep the impact of vacuuming below 10%, which I should hope that we could. What your problem sounds like to me is that you need a smarter autovacuum scheduler. Some of the map-fork ideas we've discussed would also help, by allowing vacuum to skip pages that're known to contain only frozen tuples --- your large low-turnover tables would probably have a lot of those. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] XIDs and big boxes again ...
Hans-Juergen Schoenig wrote: regards, tom lane overhead is not an issue here - if i lose 10 or 15% i am totally fine as long as i can reduce vacuum overhead to an absolute minimum. overhead will vary with row sizes anyway - this is not the point. I am not buying this argument. If you have a 5TB database, I am going to assume you put it on enterprise class hardware. Enterprise class hardware can handle the I/O required to appropriately run vacuum. We have a customer that is constantly running 5 autovacuum workers on only 28 spindles. We are in the process of upgrading them to 50 spindles at which point I will likely try 10 autovacuum workers. the point is that you don't want to potentially vacuum a table when only a handful of records has been changed. Right, generally speaking 20% is reasonable, although I tend to be much more aggressive and try to keep it at 10%. Sincerely, Joshua D. Drake -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bloated heapam.h
Alvaro Herrera [EMAIL PROTECTED] writes: So here's a patch (includes the #ifndef FRONTEND hack in htup.h.) I like this except for the #ifndef FRONTEND hack --- you're going to need to fix that before applying. I'm good with doing that by pushing the system attribute numbers into a separate header. BTW, you didn't compress the patch, which likely explains why it didn't show up on -hackers. If you want to post a shorter form, I think that the diffs in the header files are the only interesting part; the ensuing additions in .c files are just mechanical. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bloated heapam.h
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: So here's a patch (includes the #ifndef FRONTEND hack in htup.h.) I like this except for the #ifndef FRONTEND hack --- you're going to need to fix that before applying. I'm good with doing that by pushing the system attribute numbers into a separate header. Committed with the attribute numbers moved to access/sysattr.h, which is what pg_dump now uses. Thanks. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bloated heapam.h
Alvaro Herrera wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: So here's a patch (includes the #ifndef FRONTEND hack in htup.h.) I like this except for the #ifndef FRONTEND hack --- you're going to need to fix that before applying. I'm good with doing that by pushing the system attribute numbers into a separate header. Committed with the attribute numbers moved to access/sysattr.h, which is what pg_dump now uses. Oops :-( I just noticed that I removed bufmgr.h from bufpage.h, which is a change you had objected to previously :-( http://archives.postgresql.org/pgsql-patches/2008-04/msg00149.php -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bloated heapam.h
Alvaro Herrera wrote: Oops :-( I just noticed that I removed bufmgr.h from bufpage.h, which is a change you had objected to previously :-( However, it seems the right fix is to move BufferGetPageSize and BufferGetPage from bufpage.h to bufmgr.h. (Digging further, it seems like bufpage.h should also include transam.h in order to get TransactionIdIsNormal ... I start to wonder how many problems of this nature we have on our headers. Without having a way to detect whether the defined macros are valid, it seems hard to check programatically, however.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bloated heapam.h
Alvaro Herrera [EMAIL PROTECTED] writes: Oops :-( I just noticed that I removed bufmgr.h from bufpage.h, which is a change you had objected to previously :-( http://archives.postgresql.org/pgsql-patches/2008-04/msg00149.php Hmm. I did notice that the patch seemed to need to add bufmgr.h inclusions to an awful lot of files, but I'd forgotten the argument about the bufpage.h macros needing it. Do you want to undo that aspect of it? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bloated heapam.h
Alvaro Herrera [EMAIL PROTECTED] writes: However, it seems the right fix is to move BufferGetPageSize and BufferGetPage from bufpage.h to bufmgr.h. That sounds sane if it fixes the problem. (Digging further, it seems like bufpage.h should also include transam.h in order to get TransactionIdIsNormal ... I start to wonder how many problems of this nature we have on our headers. Without having a way to detect whether the defined macros are valid, it seems hard to check programatically, however.) Yeah, I'm certain there's some other problems of the same kind, but I don't see any easy way to find 'em. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bloated heapam.h
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: However, it seems the right fix is to move BufferGetPageSize and BufferGetPage from bufpage.h to bufmgr.h. That sounds sane if it fixes the problem. Actually it's not, because bufmgr.h does not include bufpage.h, and it knows nothing about pages. However, I think most additions of bufmgr.h to source files are not all that bogus -- a lot of the warnings I got were about BufferAccessStrategyType. I'm still checking what's the best way to deal with this, and if I can't find anything else I'll re-add bufmgr.h to bufpage.h. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] another ecpg crash
Martijn van Oosterhout wrote: This will close the file *only* if yyin is NULL, which probably isn't what is meant. Ops... you're right. :-) -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
