Re: [HACKERS] getting type name
On Mon, Jul 10, 2006 at 02:03:11AM +0300, Tzahi Fadida wrote: On Monday 10 July 2006 00:29, Martijn van Oosterhout wrote: On Sun, Jul 09, 2006 at 10:08:42PM +0300, Tzahi Fadida wrote: They return format_type_be(INT4OID) = integer or format_type_be(FLOAT8OID) = double precision I need to use this in a query with the :: cast operator. The problem being? Just if it is a one-to-one conversion, otherwise the same type equality functions i use would potentially not work properly. Well, it is a one-to-one conversion. A float8 == double precision. They are mapped together very early. Similarly for integer == int4. If you look in the pg_type table, you'll see there are no entries for integer or double precision. The fancy output is mostly for compatability purposes (SQL defines these names). 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] pgsql-patches considered harmful
On Mon, Jul 10, 2006 at 01:04:09AM -0300, Marc G. Fournier wrote: I, for one, would be interested in something like that ... somehow, this 'stripping' would have to be done within Majordomo2 itself, or ... Leave pgsql-patches@ as an alias that is the stripper, with the end result forwarded over to the pgsql-hackers@ list? I have in the past had a script that took email, pushed the attachments to disk and forwarded the email on. It's not spectacularly intelligent though, but I was thinking it could be used as a sort of patch queue. However, I think the other suggestions of having the listbot mangle the reply-tos of -patches and -committers to be -hackers would probably be good too. I myself subscribe to -committers in digest form (where I look at the summary to see if it's interesting) and read -patches occasionally via the archives to see if anything is there... 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
[HACKERS] comparing record and record problem if first rec is from function
Hello I have problem with expressions: select (select row(ret_num,ret_den) from mp.x_numer(39,32)) = row(32,33); select mp.x_numer(39,32)) = row(32,33); both has error: ERROR: operator does not exist: record = record LINE 1: ...lect row(ret_num,ret_den) from mp.x_numer(39,32)) = row(32,3... ^ HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. CREATE OR REPLACE FUNCTION mp.x_numer(numer integer, denom integer, OUT ret_num integer, OUT ret_den integer) AS $$ BEGIN ret_num := numer + 1; ret_den := denom * 2; WHILE floor( ret_num/2 ) = ret_num/2.0 LOOP ret_num := ret_num/2; ret_den := ret_den/2; END LOOP; RETURN; END; $$ LANGUAGE plpgsql IMMUTABLE; SELECT mp.x_numer(39,32); -- 5,8; What can be problem? I use csv version of postgresql regards Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pgsql-patches considered harmful
Martjin, Greg, Marc, etc.: However, I think the other suggestions of having the listbot mangle the reply-tos of -patches and -committers to be -hackers would probably be good too. I myself subscribe to -committers in digest form (where I look at the summary to see if it's interesting) and read -patches occasionally via the archives to see if anything is there... I agree that mangling the reply-tos would be the least complex (and thus probably best) solution. Unlike attachment stripping, this is supported by majordomo. However, to save on spam filtering, the reply-to should add -hackers *also*, not instead. --Josh Berkus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Removing AddDepends; should I bother with a project?
Folks, For the code sprint, I'm starting off by removing the projects from contrib which need to be removed by still have some usefulness. I'm not exactly sure what to do with adddepends, though. It seems unlike to lead an independant existance on pgFoundry; I'm inclined to just nuke it. For those of you who don't want to hunt through the archives, the reasons we're removing adddepends are: 1) Rod Taylor is not interested in maintaining it anymore; 2) It currently throws errors on 8.2 (and probably earlier); 3) With KL's improvements to pg_dump for 8.0, about half of its functionality is no longer necessary. So, speak up if someone thinks there's some reason to save adddepends anywhere other than the CVS and FTP archives. --Josh Berkus ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] A couple thoughts about btree fillfactor
Now that the index options infrastructure is in, I am having a couple of second thoughts about the specific behavior that's been implemented, particularly for btree fillfactor. 1. The btree build code (nbtsort.c) is dependent on the assumption that the fillfactor is at least 2/3rds. This is because we must put at least two keys in each page, and with maximally sized keys (1/3rd page) it might try to put only 0 or 1 tuple in a page if fillfactor is small. However, maximally sized keys are certainly a corner case, and in more usual situations a smaller fillfactor could be useful. I'm thinking we could change the nbtsort.c code to work like stop filling page when fillfactor is exceeded AND there are at least two entries already. Then any old fillfactor would work. 2. The build code is also set to force fillfactor 70 on non-leaf pages, using the user-specified fillfactor only on leaf pages. I think this is reasonable: if you're using a small fillfactor to avoid leaf page splits, then there shouldn't be much need for new insertions on upper pages, hence not much need for extra free space there; and having a low fillfactor on upper pages will force the tree to be much deeper and hence more expensive to search. In the other case (leaf fillfactor higher than 70, indicating index is expected to be static), I'm still not inclined to use the user fillfactor for non-leaf pages, because if a split does occur it will be very expensive if we have to propagate splits all the way up the tree. There's a case to be made for making leaf and non-leaf fillfactors accessible as separate knobs, but I'm inclined just to use a fixed value of 70 for non-leaf factor. The index page split code is currently getting this wrong either way (it's applying the user fillfactor to rightmost pages on all tree levels). 3. What should the minimum fillfactor be? The patch as submitted set the minimum to 50% for all relation types. I'm inclined to think we should allow much lower fillfactors, maybe down to 10%. A really low fillfactor could be a good idea in a heavily updated table --- at least, I don't think we have any evidence to prove that it's not sane to want a fillfactor below 50%. Comments? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] lastval exposes information that currval does not
Docs updated: para For schemas, allows the grantee to find objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). /para --- Martijn van Oosterhout wrote: -- Start of PGP signed section. On Sun, Jul 09, 2006 at 11:24:38AM -0400, Phil Frost wrote: On UNIX it is also clearly defined that if one does not have execute permissions on a directory, one can not open files within it by *any* *means*. There are no procedures that bypass this by taking an inode number directly. Well, not entirely true. If a file exists in multiple directories, you can open it as long as any of the directories are currently accessable to you (which is not the same as being accessable if you logged in again). However, the issue has been confused here by two completely different examples. In one case you prepare a statement and then execute it later which succeeds even though if you reprepared the statement it would fail. This is no different from the UNIX case where having an open file survives removing of permissions and even deletion. It is generally understood in the UNIX commuinity that adding a function in a new version that grants capabilities that were previously unavailable is an obvious security bug. In this case you're referring to the lastval() issue. That case is debatable I guess... You're suggesting it return a permission error instead. It's a little odd, though it think it's defensible position though. IMO you should simply drop the lastval() function altogether, since I don't think it's really that useful in exchange for the problems it creates. If it doesn't make sense to be able to revoke permissions on objects already accessed, why is this the behaviour of everything except the schema usage check? Does your definition of already accessed include accessed in a 'security definer' procedure intended to prevent the caller from accessing an object directly? Well, that's a good question. At a guess it's because the select/update/delete permissions are a property of the table, whereas the schema is not. The table is a member of the schema. All that suggests is that you should be revoking the permissions on the table itself, rather than on the schema. In the same vein, when reloading the pg_hba.conf, the database doesn't immediatly disconnect all users who would be disallowed by the new rules. Given that there are already several ways to bypass the check for usage on a schema, and the developers seem to not be bothered at all by adding more, of what security use is the schema usage privilege? Several other ways? If there were a case where a user who has never had access to a schema could access something in it, that would be an issue. But arguing about when a revoke should take effect is a completely different issue. IME the developers are extremely interested in security issues. At a minimum, I'd like to see the documentation updated to document the weakness of the usage privilege, and how to prevent these exploits. I'll write the patch if there is agreement. Ideally, I'd like to see the usage privilege changed to something more consistent and useful. I think it might be helpful for the documentation to state that USAGE controls whether people can lookup objects within a schema and that removing USAGE doesn't block access to the objects themselves, only that they may not be referred to by name. To do that you need to revoke permissions on the objects themselves. I'm not a core developer though, so my opinions aren't really that relevent. Do other database systems work the way you expect? 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. -- End of PGP section, PGP failed! -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] A couple thoughts about btree fillfactor
On Mon, Jul 10, 2006 at 12:36:34PM -0400, Tom Lane wrote: Now that the index options infrastructure is in, I am having a couple of second thoughts about the specific behavior that's been implemented, particularly for btree fillfactor. 1. The btree build code (nbtsort.c) is dependent on the assumption that the fillfactor is at least 2/3rds. This is because we must put at least two keys in each page, and with maximally sized keys (1/3rd page) it might try to put only 0 or 1 tuple in a page if fillfactor is small. However, maximally sized keys are certainly a corner case, and in more usual situations a smaller fillfactor could be useful. I'm thinking we could change the nbtsort.c code to work like stop filling page when fillfactor is exceeded AND there are at least two entries already. Then any old fillfactor would work. I like the idea. Do you think there should be a way of packing certain indexes tighter, once they are known to be mostly read only? For example, an option on REINDEX? This would free PostgreSQL to use a smaller fillfactor while still allowing people to optimize those of their tables that would benefit from a higher fillfactor once they become mostly static? 3. What should the minimum fillfactor be? The patch as submitted set the minimum to 50% for all relation types. I'm inclined to think we should allow much lower fillfactors, maybe down to 10%. A really low fillfactor could be a good idea in a heavily updated table --- at least, I don't think we have any evidence to prove that it's not sane to want a fillfactor below 50%. If there was a way of packing relations tighter, allowing much lower fillfactors should be fine. 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Warm-Standby using WAL archiving / Seperate pg_restorelog application
Hi I've now setup a warm-standby machine by using wal archiving. The restore_command on the warm-standby machine loops until the wal requested by postgres appears, instead of returning 1. Additionally, restore_command check for two special flag-files abort and take_online. If take_online exists, then it exists with code 1 in case of a non-existant wal - this allows me to take the slave online if the master fails. This methods seems to work, but it is neither particularly fool-proof nor administrator friendly. It's not possible e.g. to reboot the slave without postgres abortint the recovery, and therefor processing all wals generated since the last backup all over again. Monitoring this system is hard too, since there is no easy way to detect errors while restoring a particular wal. I think that all those problems could be solved if postgres provided a standalone application that could restore one wal into a specified data-dir. It should be possible to call this application repeatedly to restore wals as they are received from the master. Since pg_restorelog would be call seperately for every wal, I'd be easy to detect errors recovering a specific wal. Do you think this idea is feaseable? How hard would it be to turn the current archived-wal-recovery-code into a standalone executable (That of course needs to be called when postgres is _not_ running.) greetings, Florian Pflug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] lastval exposes information that currval does not
On Mon, Jul 10, 2006 at 12:49:54PM -0400, Bruce Momjian wrote: Docs updated: para For schemas, allows the grantee to find objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). /para I think that misses the point. One can easily find objects in a schema without usage by examining the system catalogs. The point is that there are ways to access objects without going through the schema usage check, and also that the check is made only once at the time a name is resolved to an oid, which may then be cached in a prepared statement, stored procedure, lastval, or the like. I would suggest something more like this: For schemas, allows the grantee to reference objects within the specified schema by name. Note that any method of accessing an object that does not involve naming will not check for this privilege. For example, any function taking an OID parameter or lastval(). Also, the check for this privilege will be made only once when a query is planned, so stored plans such as from prepared statements or stored procedures will not make the check again when subsequently executed. In applications where security is very important, it may be wise to assure that no users have undesired privileges on objects within a schema, and not to rely solely on the schema usage privilege. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Removing AddDepends; should I bother with a project?
Josh Berkus wrote: Folks, For the code sprint, I'm starting off by removing the projects from contrib which need to be removed by still have some usefulness. I'm not exactly sure what to do with adddepends, though. It seems unlike to lead an independent existence on pgFoundry; I'm inclined to just nuke it. I vote for the nuclear option. ;-) -- 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] A couple thoughts about btree fillfactor
Ühel kenal päeval, E, 2006-07-10 kell 12:36, kirjutas Tom Lane: 3. What should the minimum fillfactor be? The patch as submitted set the minimum to 50% for all relation types. I'm inclined to think we should allow much lower fillfactors, maybe down to 10%. A really low fillfactor could be a good idea in a heavily updated table --- at least, I don't think we have any evidence to prove that it's not sane to want a fillfactor below 50%. Sure 50% is way too big as an lower limit. We may even want to have pages that have only 1 tuple in heavy update cases. So perhaps we should set the minimum to 1% or even 0.1% and apply similar logic you suggested for btree pages above, that is stop adding new ones when the threasold is reached. Comments? -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(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] lastval exposes information that currval does not
On Mon, Jul 10, 2006 at 01:42:27PM -0400, Phil Frost wrote: I think that misses the point. One can easily find objects in a schema without usage by examining the system catalogs. The point is that there are ways to access objects without going through the schema usage check, and also that the check is made only once at the time a name is resolved to an oid, which may then be cached in a prepared statement, stored procedure, lastval, or the like. I would suggest something more like this: Can you SELECT/UPDATE/DELETE from a table knowing only its oid? I'd like to see that trick. lastval() is an odd case, given the user doesn't actually supply the oid. In applications where security is very important, it may be wise to assure that no users have undesired privileges on objects within a schema, and not to rely solely on the schema usage privilege. Indeed, never give priveledges unless you're sure you want people to have them. 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
[HACKERS] Win32 build error
Hi Bruce, In order to fix a win32 build error with thread safety enabled, can you please move src/interfaces/libpq/pthread-win32.h to src/include/port/win32/ ? I've updated snake to build with thread safety enabled so hopefully this won't go unnoticed if broken in the future. Thanks, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Removing AddDepends; should I bother with a project?
On Mon, 10 Jul 2006, Bruce Momjian wrote: Josh Berkus wrote: Folks, For the code sprint, I'm starting off by removing the projects from contrib which need to be removed by still have some usefulness. I'm not exactly sure what to do with adddepends, though. It seems unlike to lead an independent existence on pgFoundry; I'm inclined to just nuke it. I vote for the nuclear option. ;-) There are still 7.2 systems out there which need it. The problem is, adddepend is broken when run against 8.1. It breaks on serial, I think. Gavin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Removing AddDepends; should I bother with a project?
On Monday 10 July 2006 11:43, Gavin Sherry wrote: On Mon, 10 Jul 2006, Bruce Momjian wrote: Josh Berkus wrote: Folks, For the code sprint, I'm starting off by removing the projects from contrib which need to be removed by still have some usefulness. I'm not exactly sure what to do with adddepends, though. It seems unlike to lead an independent existence on pgFoundry; I'm inclined to just nuke it. I vote for the nuclear option. ;-) There are still 7.2 systems out there which need it. My understanding is that 7.2 is EOL... if people have 7.2 and need it they can pull the sources from anythin = 8.2 yes? So I vote nuke! Joshua D. Drake The problem is, adddepend is broken when run against 8.1. It breaks on serial, I think. Gavin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- === 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 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Removing AddDepends; should I bother with a project?
Gavin Sherry wrote: On Mon, 10 Jul 2006, Bruce Momjian wrote: Josh Berkus wrote: Folks, For the code sprint, I'm starting off by removing the projects from contrib which need to be removed by still have some usefulness. I'm not exactly sure what to do with adddepends, though. It seems unlike to lead an independent existence on pgFoundry; I'm inclined to just nuke it. I vote for the nuclear option. ;-) There are still 7.2 systems out there which need it. The problem is, adddepend is broken when run against 8.1. It breaks on serial, I think. Which is not really a problem, because it seems actually kinda absurd to be running adddepend against an 8.1 server. I mean, if you spent all that time running with broken FKs and stuff from 7.2 to 8.1, why are you going to care now? I don't think it would be very difficult to solve the problem with serial anyway. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] Warm-Standby using WAL archiving / Seperate pg_restorelog application
On 7/10/06, Florian G. Pflug [EMAIL PROTECTED] wrote: This methods seems to work, but it is neither particularly fool-proof nor administrator friendly. It's not possible e.g. to reboot the slave without postgres abortint the recovery, and therefor processing all wals generated since the last backup all over again. Monitoring this system is hard too, since there is no easy way to detect errors while restoring a particular wal. what I would really like to see is to have the postmaster start up in a special read only mode where it could auto-restore wal files placed there by an external process but not generate any of its own. This would be a step towards a pitr based simple replication method. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] lastval exposes information that currval does not
On Mon, Jul 10, 2006 at 08:24:08PM +0200, Martijn van Oosterhout wrote: On Mon, Jul 10, 2006 at 01:42:27PM -0400, Phil Frost wrote: I think that misses the point. One can easily find objects in a schema without usage by examining the system catalogs. The point is that there are ways to access objects without going through the schema usage check, and also that the check is made only once at the time a name is resolved to an oid, which may then be cached in a prepared statement, stored procedure, lastval, or the like. I would suggest something more like this: Can you SELECT/UPDATE/DELETE from a table knowing only its oid? I'd like to see that trick. lastval() is an odd case, given the user doesn't actually supply the oid. I haven't found a way to do this yet, but I wouldn't be suprised if there is a clever way, especially considering C extensions that might come from contrib or other sources. It seems like there is a good deal of potential for non-malicious developers to open unknowingly serious security holes. I think lastval is a great example of this potential; fortunately sequence values are rarely compromising. Imagine the consequences of a function which returns the last inserted row in a similar manner. In applications where security is very important, it may be wise to assure that no users have undesired privileges on objects within a schema, and not to rely solely on the schema usage privilege. Indeed, never give priveledges unless you're sure you want people to have them. The way I ran into this problem is moving a table that was previously in a public schema into a private schema. Since not having usage on the schema is enough to prevent access most of the time, the change passed testing. Later I noticed the odd grant on some objects in the private schema, and poking around I found ways to access them dispite not having usage on the schema. The check just once at plan time, and only when referencing an object by name semantics of the schema usage check which differ from the check always semantics of all the other ACL checks I found to be suprising. Since these fine details are not obvious, expected, or documented, there seems to be a good deal of potential for administrative error. Hopefully at least the docs will be updated so people can at least be aware of the issue. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] More nuclear options
Folks, I was looking at migrating mSQL-interface to pgFoundry, but I'm not sure there's any reason to do so. It was never finished, doesn't build, and it's not like I run across mSQL databases in the field. Does anyone? Shall we just kill it? Also, tips is an apache log converter for which the source code appears to be completely missing (?). So barring objections, that one will get the ol' missle from space too. --Josh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Removing AddDepends; should I bother with a project?
Gavin, There are still 7.2 systems out there which need it. The problem is, adddepend is broken when run against 8.1. It breaks on serial, I think. And on some other stuff, too. I didn't document all the failures, I just tested and killed it. Think is, 8.1 does a much better job of upgrading 7.2 datatabases than 7.3 or 7.4 did anyway. I just tested using a database created in 7.1 and upgraded to 7.2 which has a baroque and unnecessarily complex schema (legacy production applicaiton) which breaks on 7.4 without adddepends. I was able to upgrade it to 8.2(today) and it worked without adddepends. I'm testing dump, load, dump, load now to see if it still works OK. --Josh ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] A couple thoughts about btree fillfactor
On Mon, Jul 10, 2006 at 03:17:01PM -0400, Tom Lane wrote: [EMAIL PROTECTED] writes: ... Do you think there should be a way of packing certain indexes tighter, once they are known to be mostly read only? For example, an option on REINDEX? This would free PostgreSQL to use a smaller fillfactor while still allowing people to optimize those of their tables that would benefit from a higher fillfactor once they become mostly static? Isn't it sufficient to change the fillfactor and REINDEX? I've never tried that - if it works sure... :-) Thanks, 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
[HACKERS] CTIDs invalidations and dropping columns.
Hi, First, i use CTIDs to immensely speed up my function which is inherently slow because of the problem itself. I have a question about CTID invalidation when you open a read only cursor using SPI. Why does it at all happens? Why is it so important to invalidate a ctid of a read only query (for example when using indices, casting,etc...)? Specifically, i encountered something unexpected. i created a table: (a2 int4, a0 int4) then i did alter table add column a5 int4, then update set a5=a0, update set a0=a0+1, alter table drop column a0. Now that i run a simple select * from SPI cursor query on this table and look at the t_data-t_ctid i see that the ctids are invalidated for some unknown reason? previously before the alter table it was ok. I am using 8.1.4, can you tell me if it is a bug/feature/don't care about ctids in spi... reason. Is it such a difficult thing to return ctids if the query is read only. where is it invalidated anyway? I highly prefer not to use CTID as an attribute since it is going to greatly lower the performance since it is sitting on a bottleneck. -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] A couple thoughts about btree fillfactor
[EMAIL PROTECTED] writes: ... Do you think there should be a way of packing certain indexes tighter, once they are known to be mostly read only? For example, an option on REINDEX? This would free PostgreSQL to use a smaller fillfactor while still allowing people to optimize those of their tables that would benefit from a higher fillfactor once they become mostly static? Isn't it sufficient to change the fillfactor and REINDEX? 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] Removing AddDepends; should I bother with a project?
Bruce Momjian wrote: Josh Berkus wrote: Folks, For the code sprint, I'm starting off by removing the projects from contrib which need to be removed by still have some usefulness. I'm not exactly sure what to do with adddepends, though. It seems unlike to lead an independent existence on pgFoundry; I'm inclined to just nuke it. I vote for the nuclear option. ;-) as I said when this first came up - we still get a sizable number of support requests from people trying to import dumps(!) of very old postgresql versions on IRC. adddepends is often of some value for those people and I would rather like to see it fixed for 8.1 and maybe even 8.2 ... Stefan ---(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] More nuclear options
Josh Berkus wrote: Folks, I was looking at migrating mSQL-interface to pgFoundry, but I'm not sure there's any reason to do so. It was never finished, doesn't build, and it's not like I run across mSQL databases in the field. Does anyone? Shall we just kill it? Also, tips is an apache log converter for which the source code appears to be completely missing (?). So barring objections, that one will get the ol' missle from space too. Ka-boom! -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] CTIDs invalidations and dropping columns.
On Mon, Jul 10, 2006 at 11:47:23PM +0300, Tzahi Fadida wrote: Hi, First, i use CTIDs to immensely speed up my function which is inherently slow because of the problem itself. I have a question about CTID invalidation when you open a read only cursor using SPI. Why does it at all happens? Why is it so important to invalidate a ctid of a read only query (for example when using indices, casting,etc...)? You're talking about invalidation as if it's something someone deliberately does. That's incorrect. The t_ctid field is filled in if and only if the tuple is exactly the on disk tuple. Otherwise it's a new tuple, which by definition does not have a ctid (it doesn't exist on disk). Specifically, i encountered something unexpected. i created a table: (a2 int4, a0 int4) then i did alter table add column a5 int4, then update set a5=a0, update set a0=a0+1, alter table drop column a0. Now that i run a simple select * from SPI cursor query on this table and look at the t_data-t_ctid i see that the ctids are invalidated for some unknown reason? previously before the alter table it was ok. This doesn't make any sense. What is invalidated? Is it blank or what? I think you're going to have to provide some example code. What do you mean by invalidation anyway? I highly prefer not to use CTID as an attribute since it is going to greatly lower the performance since it is sitting on a bottleneck. You've measured this performance difference? 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] More nuclear options
All, At the request of Dave Page, here's the semi-final list after looking at the code: To be killed: adddepends tips mSQL-interface To be migrated to pgFoundry: dbmirror (need owner) dbase (owner?) fulltextindex (owner?) mac (LER) userlock (Merlin) --Josh Berkus ---(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_terminate_backend idea
On Tue, 2005-06-21 at 23:34 -0400, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: In any case the correct way to solve the problem is to find out what's being left corrupt by SIGTERM, rather than install more messiness in order to avoid facing the real issue ... I am confused. Are you talking about the client SIGTERM or the server? I am talking about Rod Taylor's reports that SIGTERM'ing individual backends tends to lead to lock table corrupted crashes awhile later. Now, I've been playing the part of Chicken Little on this for awhile, but seeing an actual report of problems from the field certainly strengthens my feelings about it. Bringing this thread back to life. I have not seen a lock table corruption issue with SIGTERM in 8.1 on Solaris/Sun IV, Linux/AMD64, or Linux/Intel. I don't recall seeing one on 8.0.3 either though I'm pretty sure there were several on 8.0.1. There are times when locks for a process hang around for a few minutes before getting cleared. I don't recall whether they were ungranted table locks or entries waiting on a transaction ID lock, but the source was Slony and a large pg_listener structure with more than 2 pages (yes, pages not tuples). I have also seen processes refusing to acknowledge the signal and exit during btree index builds, but that's not a data corruption issue. -- ---(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] Warm-Standby using WAL archiving / Seperate pg_restorelog
Merlin Moncure wrote: On 7/10/06, Florian G. Pflug [EMAIL PROTECTED] wrote: This methods seems to work, but it is neither particularly fool-proof nor administrator friendly. It's not possible e.g. to reboot the slave without postgres abortint the recovery, and therefor processing all wals generated since the last backup all over again. Monitoring this system is hard too, since there is no easy way to detect errors while restoring a particular wal. what I would really like to see is to have the postmaster start up in a special read only mode where it could auto-restore wal files placed there by an external process but not generate any of its own. This would be a step towards a pitr based simple replication method. I didn't dare to ask for being able to actually _access_ a wal-shipping based slaved (in read only mode) - from how I interpret the code, it's a _long_ way to get that working. So I figured a stand-alone executable that just recovers _one_ archived wal would at least remove that administrative burden that my current solution brings. And it would be easy to monitor the slave - much easier than with any automatic pickup of wals. greetings, Florian Pflug ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgsql-patches considered harmful
Marc, You've lost me on that last point ... how does that save on spam filtering? Many spam filters give points for reply-to address does not match from address. --Josh ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pgsql-patches considered harmful
On Mon, 10 Jul 2006, Josh Berkus wrote: Martjin, Greg, Marc, etc.: However, I think the other suggestions of having the listbot mangle the reply-tos of -patches and -committers to be -hackers would probably be good too. I myself subscribe to -committers in digest form (where I look at the summary to see if it's interesting) and read -patches occasionally via the archives to see if anything is there... I agree that mangling the reply-tos would be the least complex (and thus probably best) solution. Unlike attachment stripping, this is supported by majordomo. However, to save on spam filtering, the reply-to should add -hackers *also*, not instead. You've lost me on that last point ... how does that save on spam filtering? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pgsql-patches considered harmful
On Mon, 10 Jul 2006, Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: On Sunday 09 July 2006 20:00, Greg Stark wrote: BIRT pgsql-patches should be abolished in favour of something else that accomplishes the bandwidth-reduction aspect without the downsides. Alternatively, people could just use patches for patch submission and keep all discussion on hackers. If this is chosen as the preferred path, we could get the list bot to add Reply-To: pghackers in pgsql-patches postings to help push discussions there. I'd vote for doing the same in pgsql-committers, which also gets its share of non-null discussion content. that is a very easy and quick change ... but wasn't doing that brought up before and alot of ppl were against that? If nobody objects within, say, the next 24 hours ... ? I'll enabled that one both ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Removing AddDepends; should I bother with a project?
On Mon, Jul 10, 2006 at 11:22:49AM -0400, Josh Berkus wrote: Folks, For the code sprint, I'm starting off by removing the projects from contrib which need to be removed by still have some usefulness. I'm not exactly sure what to do with adddepends, though. It seems unlike to lead an independant existance on pgFoundry; I'm inclined to just nuke it. For those of you who don't want to hunt through the archives, the reasons we're removing adddepends are: 1) Rod Taylor is not interested in maintaining it anymore; 2) It currently throws errors on 8.2 (and probably earlier); 3) With KL's improvements to pg_dump for 8.0, about half of its functionality is no longer necessary. So, speak up if someone thinks there's some reason to save adddepends anywhere other than the CVS and FTP archives. As Ripley famously said: I say we take off and nuke the entire site from orbit. It's the only way to be sure. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CTIDs invalidations and dropping columns.
On Tuesday 11 July 2006 00:35, Martijn van Oosterhout wrote:
On Mon, Jul 10, 2006 at 11:47:23PM +0300, Tzahi Fadida wrote:
Hi,
First, i use CTIDs to immensely speed up my function which is inherently
slow because of the problem itself.
I have a question about CTID invalidation when you open a read only
cursor using SPI. Why does it at all happens? Why is it so important to
invalidate a ctid of a read only query (for example when using indices,
casting,etc...)?
You're talking about invalidation as if it's something someone
deliberately does. That's incorrect. The t_ctid field is filled in if
and only if the tuple is exactly the on disk tuple. Otherwise it's a
new tuple, which by definition does not have a ctid (it doesn't exist
on disk).
As i understand rowids, i.e ctids, are supposed to allow for fast access to
the tables. I don't see the rational, for example, when casting some
attributes, to blank the ctid. So it is not exactly the same, but it still
came from the same tuple. What will happen if for read only SPI queries
it will not be blank?
Specifically, i encountered something unexpected. i created a table:
(a2 int4, a0 int4) then i did alter table add column a5 int4, then update
set a5=a0, update set a0=a0+1, alter table drop column a0.
Now that i run a simple select * from SPI cursor query on this table and
look at the t_data-t_ctid i see that the ctids are invalidated for some
unknown reason?
previously before the alter table it was ok.
This doesn't make any sense. What is invalidated? Is it blank or what?
I think you're going to have to provide some example code.
blank.
i am attaching a code. it is not supposed to run to completion but to print to
screen using elog.
i used this sql to declare the function but you'll need to alter it where it
says fdfuncs:
CREATE OR REPLACE FUNCTION ctest(text) RETURNS SETOF RECORD
AS 'fdfuncs','ctest'
LANGUAGE C STABLE STRICT;
anyway, run these commands:
create table ctest (a2 int4, a0 int4);
insert into ctest values (10,11);
insert into ctest values (12,13);
then run the function.
it should print 1 in posid and 0 in hi and lo.
Now run the following commands:
alter table ctest add column a5 int4;
alter table ctest DROP COLUMN a0;
and run the function.
it should print 0 on all three fields which means the ctid is blank.
What do you mean by invalidation anyway?
I highly prefer not to use CTID as an attribute since it is going to
greatly lower the performance since it is sitting on a bottleneck.
You've measured this performance difference?
Yes, i played with this in the past. since i can pass over a relation
potentially hundreds or thousands of times, this can be a bottleneck.
Full disjunctions is a difficult problem that in order to speed up uses the
tuple set concept where a set of tuples are represented as a set of CTIDs.
Have a nice day,
--
Regards,
Tzahi.
--
Tzahi Fadida
Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info
WARNING TO SPAMMERS: see at
http://members.lycos.co.uk/my2nis/spamwarning.html
#include executor/spi.h
#include funcapi.h
extern char *stpcpy (char *__dest, const char *__src);
PG_FUNCTION_INFO_V1(ctest);
Datum
ctest(PG_FUNCTION_ARGS)
{
SPI_connect();
char query[255];
char *to = query;
to =(char *) stpcpy(to,SELECT * FROM ctest);
void * plan;
Portal portal;
if ((plan = SPI_prepare(query, 0, NULL)) == NULL)
elog(ERROR, initialize_SPI_structures: SPI_prepare('%s') returns NULL, query);
if ((portal = SPI_cursor_open(NULL, plan, NULL, NULL, true)) == NULL)
elog(ERROR, initialize_SPI_structures: SPI_cursor_open('%s') returns NULL,
query);
SPI_cursor_fetch(portal, true, 1);
elog(INFO,ctidhi:%d,SPI_tuptable-vals[0]-t_data-t_ctid.ip_blkid.bi_hi);
elog(INFO,ctidlo:%d,SPI_tuptable-vals[0]-t_data-t_ctid.ip_blkid.bi_lo);
elog(INFO,ctidip_posid:%d,SPI_tuptable-vals[0]-t_data-t_ctid.ip_posid);
elog(INFO,FINISHED PRINTING.);
SPI_finish();
SRF_RETURN_DONE(NULL);
}
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] lastval exposes information that currval does not
* Phil Frost ([EMAIL PROTECTED]) wrote: I haven't found a way to do this yet, but I wouldn't be suprised if there is a clever way, especially considering C extensions that might come from contrib or other sources. It seems like there is a good deal of potential for non-malicious developers to open unknowingly serious security holes. I think lastval is a great example of this potential; fortunately sequence values are rarely compromising. Imagine the consequences of a function which returns the last inserted row in a similar manner. Yes, you can compromise the security of the system by loading C modules. That's not going to change. If you find examples of such compromises in core, or in contrib, please bring them to our attention. As for from other sources, well, you'd have to bring it up with that source.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] pgsql-patches considered harmful
Marc G. Fournier wrote: On Mon, 10 Jul 2006, Josh Berkus wrote: Martjin, Greg, Marc, etc.: However, I think the other suggestions of having the listbot mangle the reply-tos of -patches and -committers to be -hackers would probably be good too. I myself subscribe to -committers in digest form (where I look at the summary to see if it's interesting) and read -patches occasionally via the archives to see if anything is there... I agree that mangling the reply-tos would be the least complex (and thus probably best) solution. Unlike attachment stripping, this is supported by majordomo. However, to save on spam filtering, the reply-to should add -hackers *also*, not instead. You've lost me on that last point ... how does that save on spam filtering? He is saying that other mail servers might think our email is spam, but I think the risk is worth it. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Removing AddDepends; should I bother with a project?
1) Rod Taylor is not interested in maintaining it anymore; 2) It currently throws errors on 8.2 (and probably earlier); 3) With KL's improvements to pg_dump for 8.0, about half of its functionality is no longer necessary. So, speak up if someone thinks there's some reason to save adddepends anywhere other than the CVS and FTP archives. I think it absolutely should be kept on pgFoundry no? I don't see how my improvements to pg_dump help anyone upgrading from 7.2 to later versions? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Removing AddDepends; should I bother with a project?
Think is, 8.1 does a much better job of upgrading 7.2 datatabases than 7.3 or 7.4 did anyway. I just tested using a database created in 7.1 and upgraded to 7.2 which has a baroque and unnecessarily complex schema (legacy production applicaiton) which breaks on 7.4 without adddepends. I was able to upgrade it to 8.2(today) and it worked without adddepends. I'm testing dump, load, dump, load now to see if it still works OK. That's irrelevant - does it actually have FK's is the question. I bet you'll instead have a bunch of CREATE CONSTRAINT TRIGGER statements... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] 10th Anniversary Conference
Dear Hackers, I would like to thank all of you for organizing, hosting, and attending the 10th Anniversary PostgreSQL Conference last weekend. I was especially interested in future PostgreSQL directions and that was definitely the conference's theme. It was great to meet the community's big wigs, too. Thanks especially to Josh, Gavin, and the other main organizers for making sure everything ran smoothly. I would most certainly be interested in future annual meetings. If any of you are ever in the Boston area, drop me a line for a free beer.* And, as mentioned by Bruce Momjian during his keynote, thanks for making a great database product that allows so many of us to pay the bills! Best regards, M *Offer void where prohibited. ---(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] CTIDs invalidations and dropping columns.
Martijn van Oosterhout kleptog@svana.org writes: You're talking about invalidation as if it's something someone deliberately does. That's incorrect. The t_ctid field is filled in if and only if the tuple is exactly the on disk tuple. Otherwise it's a new tuple, which by definition does not have a ctid (it doesn't exist on disk). The only way that t_ctid would be valid in the result of a SELECT * FROM is if the physical tlist optimization triggers to make the scan skip the usual ExecProject call and just return a direct pointer to the on-disk tuple. That optimization never existed before 8.0 or 8.1 (I forget exactly, but it's pretty recent). So the OP is depending on an undocumented, recently added behavior that only applies in one special case. To be blunt, the OP's code is broken. If you want to know the on-disk tuple's CTID, select it explicitly: SELECT ctid, whatever-else-you-need FROM foo ... 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
