Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
Any ideas for a temporary work around? On 12/29/05, Sebastian [EMAIL PROTECTED] wrote: How many columns in the table? There are 4 columns in the table On 12/29/05, Michael Fuhr [EMAIL PROTECTED] wrote: On Thu, Dec 29, 2005 at 12:12:52PM -0800, Sebastian wrote: I've waited 10 minutes before cancelling. On pg8 it runs in less than a second How many columns in the table? In 8.1.1 I'm seeing a nearly exponential increase in time with each extra column, at least up to about five columns; with more columns the time continues to increase although not as sharply. I don't see such an increase in 8.0.5. Querying the views individually doesn't take long; I wonder if the planner is doing something wrong with the join operation. -- Michael Fuhr ---(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] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Thu, 2005-12-29 at 11:37 -0500, Bruce Momjian wrote: Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: My view would be that this thread has been complex because everybody has expressed a somewhat different requirement, which could be broken down as: 1. The need for a multi-user-accessible yet temporary table 2. Loading data into a table immediately after it is created (i.e. in same transaction), including but not limited to a reload from pg_dump 3. How to load data quickly into an existing table (COPY) 4. How to add/modify data quickly in an existing table (INSERT SELECT, UPDATE) However, you then seem to be arguing for still using the COPY LOCK syntax, which I think Bruce intended would go away in favor of using these ALTER commands. Certainly that's what I'd prefer --- COPY has got too darn many options already. COPY LOCK was Tom's suggestion at the end of a long discussion thread on this precise issue. Nobody objected to it at that point; I implemented it *exactly* that way because I wanted to very visibly follow the consensus of the community, after informed debate. http://archives.postgresql.org/pgsql-hackers/2005-06/msg00068.php Please re-read the links to previous discussions. http://archives.postgresql.org/pgsql-hackers/2005-06/msg00069.php There are points there, not made by me, that still apply and need to be considered here, yet have not been. Just to restate my current thinking: - agree we should have ALTER TABLE ... RELIABILITY DELETE ROWS - we should have COPY LOCK rather than ALTER TABLE RELIABILITY EXCLUSIVE (Though I welcome better wording and syntax in either case; it is the behaviour only that I discuss). It seems now that we have agreed approaches for (1), (2) and (4). Please note that I have listened to the needs of others with regard to requirement (1), as espoused by earlier by Hannu and again now by Martijn. Some of the points about requirement (3) I made in my previous post have not yet been addressed, IMHO. My mind is not fixed. AFAICS there are valid points remaining on both sides of the discussion about loading data quickly into an existing table. I do think it is valid concern about someone use the table between the CREATE and the ALTER TABLE RELIABILITY. One solution would be to allow the RELIABILITY as part of the CREATE TABLE, another is to tell users to create the table inside a transaction. Neither solution works for this use case: 3. How to load data quickly into an existing table (COPY) This is the only use case for which ALTER TABLE ... EXCLUSIVE makes sense. That option means that any write lock held upon the table would be an EXCLUSIVE table lock, so would never be a performance gain with single row INSERT, UPDATE or DELETEs. Following Andrew's concerns, I'd also note that ALTER TABLE requires a much higher level of privilege to operate than does COPY. That sounds like it will make things more secure, but all it does is open up the administrative rights, since full ownership rights must be obtained merely to load data. Having COPY behave differently because it is in a transaction is fine as long as it is user-invisible Good I think there is great utility in giving users one API, namely RELIABILITY (or some other keyword), and telling them that is where they control logging. I realize adding one keyword, LOCK, to an existing command isn't a big deal, but once you decentralize your API enough times, you end up with a terribly complex database system. It is this design rigidity that helps make PostgreSQL so much easier to use than other database systems. I do see the appeal of your suggestion... TRUNCATE is a special command to delete quickly. There is no requirement to do an ALTER TABLE statement before that command executes. Balance would suggest that a special command to load data quickly would be reasonably accepted by users. Minor points below: In the patch, pg_dump has *not* been altered to use COPY LOCK, so a pg_dump *will* work with any other version of PostgreSQL, which *would not* be the case if we added ALTER TABLE ... RELIABILITY statements into it. Wrong --- the good thing about ALTER TABLE is that an old version of Postgres would simply reject it and keep going. Therefore we could get the speedup in dumps without losing compatibility, which is not true of COPY LOCK. That was pointing out one of Bruce's objections was not relevant because it assumed COPY LOCK was required to make pg_restore go faster; that was not the case - so there is no valid objection either way now. BTW, this is a perfect example of the use-case for not abandoning a dump-file load simply because one command fails. (We have relied on this sort of reasoning many times before, too, for example by using SET default_with_oids in preference to CREATE TABLE WITH/WITHOUT OIDS.) I don't think that wrap the whole load into begin/end is really a very workable
Re: [HACKERS] [DOCS] Online backup vs Continuous backup
On Mon, 2005-12-26 at 13:46 -0500, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I suggest the following patch to rename our capability Continuous Backup. This doesn't seem like an improvement. Online backup is the standard terminology AFAIK. But why is it the standard terminology? It doesn't seem logical. Well, as Greg says its a physical backup that can be done on-line, so online backup makes perfect sense to me. I've never had somebody say that makes no sense before. Nomenclature is different everywhere, I accept. I generally describe it like this: Logical Backup - use pg_dump - must be done on-line Physical Backup All file copy only - must be Cold/Off-line backup All file copy + WAL archiving - allows Hot/Online or Cold/Offline backup People understand those terms... When do I mention PITR? Well, I describe this as Archive Recovery, with an option to go to end-of-logs, or to a point-in-time. [In the code, the mode variable is InArchiveRecovery.] I do think that saying do you use PITR? makes little sense. We should be talking about the backup mode, not the potential future recovery mode. I think it would all make more sense if we described the use of archive_command = something as being in WAL Archive Mode. That would then allow us to say: You can only take Online Backups while in WAL Archive Mode. If you ever wish to perform PITR, you must use WAL Archive Mode. If you backed-up in WAL Archive Mode, you can perform an Archive Recovery. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Simon Riggs said: Following Andrew's concerns, I'd also note that ALTER TABLE requires a much higher level of privilege to operate than does COPY. That sounds like it will make things more secure, but all it does is open up the administrative rights, since full ownership rights must be obtained merely to load data. Yeah. And since a role can own a table you could have a role and add lots of users to it My concern is more about making plain that this is for special operations, not normal operations. Or maybe I have misunderstood the purpose. cheers andrew ---(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] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Andrew Dunstan [EMAIL PROTECTED] writes: Simon Riggs said: Following Andrew's concerns, I'd also note that ALTER TABLE requires a much higher level of privilege to operate than does COPY. That sounds like it will make things more secure, but all it does is open up the administrative rights, since full ownership rights must be obtained merely to load data. My concern is more about making plain that this is for special operations, not normal operations. Or maybe I have misunderstood the purpose. Rephrase that as full ownership rights must be obtained to load data in a way that requires dropping any existing indexes and locking out other users of the table. I don't think the use-case for this will be very large for non-owners, or indeed even for owners except during initial table creation; and so I don't think the above argument is strong. 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 SORTFUNC_LT/REVLT
On Thu, Dec 29, 2005 at 10:49:23AM -0500, Tom Lane wrote: What I'd really like is to deprecate the USING operator syntax in favor of a USING operatorclassname syntax. Actually, USING opclass [ASC/DESC] would get the job done, since given an opclass you can certainly run the sort function either normal or reverse. Thought of something this morning: this seems OK at first glance but I don't think it's workable. The example being locale dependant sorting. I really don't think we want to create a new operator class for each possible way you can sort strings. Collations (currently anyway) are really just an operator class + [ASC/DESC] + optional locale rolled into a single identifier. The idea being that when you use '', the collation on that node decides unambiguously which version of '' you mean, rather than looking up the operator and trying to guess which operator class you meant. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpugX7Tq8Z5h.pgp Description: PGP signature
Re: [HACKERS] Removing SORTFUNC_LT/REVLT
Martijn van Oosterhout kleptog@svana.org writes: On Thu, Dec 29, 2005 at 10:49:23AM -0500, Tom Lane wrote: What I'd really like is to deprecate the USING operator syntax in favor of a USING operatorclassname syntax. Actually, USING opclass [ASC/DESC] would get the job done, since given an opclass you can certainly run the sort function either normal or reverse. Thought of something this morning: this seems OK at first glance but I don't think it's workable. The example being locale dependant sorting. I really don't think we want to create a new operator class for each possible way you can sort strings. Well, you would need to add a COLLATE layer on top of this in just the same way as you'd need a COLLATE layer now if you want locale-dependent sorting. I didn't claim it handled that; just pointing out that COLLATE doesn't handle this, either. Collations (currently anyway) are really just an operator class + [ASC/DESC] + optional locale rolled into a single identifier. I really need to study your mail from the other day, but unfortunately other pressures will probably keep me from getting to it today :-(. One comment though --- it's not really sane to include ASC/DESC in there is it? I thought the spec wanted ORDER BY foo COLLATE bar [ASC/DESC] ... or if not, users certainly will. If every single collation has to be created in a matched ASC/DESC pair, you've done it wrong. 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] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Simon Riggs wrote: On Thu, 2005-12-29 at 11:37 -0500, Bruce Momjian wrote: Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: My view would be that this thread has been complex because everybody has expressed a somewhat different requirement, which could be broken down as: 1. The need for a multi-user-accessible yet temporary table 2. Loading data into a table immediately after it is created (i.e. in same transaction), including but not limited to a reload from pg_dump 3. How to load data quickly into an existing table (COPY) 4. How to add/modify data quickly in an existing table (INSERT SELECT, UPDATE) However, you then seem to be arguing for still using the COPY LOCK syntax, which I think Bruce intended would go away in favor of using these ALTER commands. Certainly that's what I'd prefer --- COPY has got too darn many options already. COPY LOCK was Tom's suggestion at the end of a long discussion thread on this precise issue. Nobody objected to it at that point; I implemented it *exactly* that way because I wanted to very visibly follow the consensus of the community, after informed debate. http://archives.postgresql.org/pgsql-hackers/2005-06/msg00068.php Please re-read the links to previous discussions. http://archives.postgresql.org/pgsql-hackers/2005-06/msg00069.php There are points there, not made by me, that still apply and need to be considered here, yet have not been. Yes, I know we agreed to the COPY LOCK, but new features now being requested, so we have to re-evaluate where we are going with COPY LOCK to get a more consistent solution. Just to restate my current thinking: - agree we should have ALTER TABLE ... RELIABILITY DELETE ROWS - we should have COPY LOCK rather than ALTER TABLE RELIABILITY EXCLUSIVE (Though I welcome better wording and syntax in either case; it is the behaviour only that I discuss). It seems now that we have agreed approaches for (1), (2) and (4). Please note that I have listened to the needs of others with regard to requirement (1), as espoused by earlier by Hannu and again now by Martijn. Some of the points about requirement (3) I made in my previous post have not yet been addressed, IMHO. My mind is not fixed. AFAICS there are valid points remaining on both sides of the discussion about loading data quickly into an existing table. I do think it is valid concern about someone use the table between the CREATE and the ALTER TABLE RELIABILITY. One solution would be to allow the RELIABILITY as part of the CREATE TABLE, another is to tell users to create the table inside a transaction. Neither solution works for this use case: 3. How to load data quickly into an existing table (COPY) This is the only use case for which ALTER TABLE ... EXCLUSIVE makes sense. That option means that any write lock held upon the table would be an EXCLUSIVE table lock, so would never be a performance gain with single row INSERT, UPDATE or DELETEs. Ah, but people wanted fast INSERT INTO ... SELECT, and that would use EXCLUSIVE too. What about a massive UPDATE? Perhaps that could use EXCLUSIVE? We don't want to add LOCK to every command that might use EXCLUSIVE. ALTER is much better for this. I agree if we thought EXCLUSIVE would only be used for COPY, we could use LOCK, but I am thinking it will be used for other commands as well. Following Andrew's concerns, I'd also note that ALTER TABLE requires a much higher level of privilege to operate than does COPY. That sounds like it will make things more secure, but all it does is open up the administrative rights, since full ownership rights must be obtained merely to load data. True, but as pointed out by others, I don't see that happening too often. Having COPY behave differently because it is in a transaction is fine as long as it is user-invisible Good I think there is great utility in giving users one API, namely RELIABILITY (or some other keyword), and telling them that is where they control logging. I realize adding one keyword, LOCK, to an existing command isn't a big deal, but once you decentralize your API enough times, you end up with a terribly complex database system. It is this design rigidity that helps make PostgreSQL so much easier to use than other database systems. I do see the appeal of your suggestion... TRUNCATE is a special command to delete quickly. There is no requirement to do an ALTER TABLE statement before that command executes. The TRUNCATE happens during recovery. There is no user interaction. It happens because we can't restore the contents of the table in a consistent state because no logging was used. Basically, a table marked RELIABILITY TRUNCATE would be truncated on a recovery start of the postmaster. Balance would suggest that a special command to load data quickly would be reasonably accepted by users. Minor points below:
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Simon Riggs said: Following Andrew's concerns, I'd also note that ALTER TABLE requires a much higher level of privilege to operate than does COPY. That sounds like it will make things more secure, but all it does is open up the administrative rights, since full ownership rights must be obtained merely to load data. My concern is more about making plain that this is for special operations, not normal operations. Or maybe I have misunderstood the purpose. Rephrase that as full ownership rights must be obtained to load data in a way that requires dropping any existing indexes and locking out other users of the table. I don't think the use-case for this will be very large for non-owners, or indeed even for owners except during initial table creation; and so I don't think the above argument is strong. Those restrictions aren't true of Bruce's proposed drop and delete/truncate recovery modes, are they? People do crazy things in pursuit of performance. Illustration: a few months ago I was instrumenting an app (based on MySQL/ISAM) and I noticed that under load it simply didn't update the inventory properly - of 1000 orders placed within a few seconds it might reduce inventory by 3 or 4. I reported this and they shrugged their shoulders and said well, we'd have to lock the table and that would slow everything down I just want to be sure we aren't providing a footgun. Oh, just set recovery mode to delete. It won't make any difference unless you crash and you'll run faster. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Andrew Dunstan wrote: My concern is more about making plain that this is for special operations, not normal operations. Or maybe I have misunderstood the purpose. Rephrase that as full ownership rights must be obtained to load data in a way that requires dropping any existing indexes and locking out other users of the table. I don't think the use-case for this will be very large for non-owners, or indeed even for owners except during initial table creation; and so I don't think the above argument is strong. Those restrictions aren't true of Bruce's proposed drop and delete/truncate recovery modes, are they? Only the owner could do the ALTER, for sure, but once the owner sets it, any user with permission to write to the table would have those characteristics. People do crazy things in pursuit of performance. Illustration: a few months ago I was instrumenting an app (based on MySQL/ISAM) and I noticed that under load it simply didn't update the inventory properly - of 1000 orders placed within a few seconds it might reduce inventory by 3 or 4. I reported this and they shrugged their shoulders and said well, we'd have to lock the table and that would slow everything down I just want to be sure we aren't providing a footgun. Oh, just set recovery mode to delete. It won't make any difference unless you crash and you'll run faster. I think we have to trust the object owner in this case. I don't know of any super-user-only ALTER commands, but I suppose we could set it up that way if we wanted. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
On Fri, Dec 30, 2005 at 03:15:03AM -0500, Sebastian wrote: Any ideas for a temporary work around? You could try querying the system catalogs directly instead of using the information_schema views; look at the view definitions and run some \d commands under psql -E to see what kinds of queries to make. See also System Information Functions and System Catalogs in the documentation. http://www.postgresql.org/docs/8.1/interactive/functions-info.html http://www.postgresql.org/docs/8.1/interactive/catalogs.html -- Michael Fuhr ---(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] Permissions vs SERIAL columns
Haven't seen this discussed in a while, but I do recall it being
mentioned sometime before...
The problem:
testdb=# create table mytable (id serial, txt text);
testdb=# grant insert on mytable to user2;
GRANT
testdb=# \connect testdb user2
You are now connected to database testdb as user user2.
testdb= insert into mytable (txt) values ('foobar');
ERROR: permission denied for sequence mytable_id_seq
What I'd like to happen is for the grant for INSERT on the table to
cascade into an UPDATE permission on the sequence (when associated with
a SERIAL column only, of course).
Coming from a different database, such as MSSQL, makes people forget
this very easily, and it becomes very annoying.
Is this something that can be done without too much work? Anything you
can do in current pg even, just me not knowing how?
//Magnus
---(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] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
Michael Fuhr [EMAIL PROTECTED] writes: However, EXPLAIN fails in 8.1.1: test= EXPLAIN SELECT * FROM information_schema.element_types; ERROR: record type has not been registered I've applied a patch for this. It's just a bug in EXPLAIN output, however, and doesn't have anything directly to do with the performance issue. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Fri, 2005-12-30 at 11:49 -0500, Bruce Momjian wrote: Yes, I know we agreed to the COPY LOCK, but new features now being requested, so we have to re-evaluate where we are going with COPY LOCK to get a more consistent solution. Thank you. Ah, but people wanted fast INSERT INTO ... SELECT, and that would use EXCLUSIVE too. What about a massive UPDATE? Perhaps that could use EXCLUSIVE? We don't want to add LOCK to every command that might use EXCLUSIVE. ALTER is much better for this. I agree if we thought EXCLUSIVE would only be used for COPY, we could use LOCK, but I am thinking it will be used for other commands as well. Agreed, I will look to implement this. Could the internals of my recent patch be reviewed? Changing the user interface is less of a problem than changing the internals, which is where the hard work takes place. I do not want to extend this work further only to have that part rejected later. The implications of EXCLUSIVE are: - there will be a check on each and every I, U, D to check the state of the relation - *every* operation that attempts a write lock will attempt to acquire an EXCLUSIVE full table lock instead - following successful completion of *each* DML statement, the relation will be heap_sync'd involving a full scan of the buffer cache Can I clarify the wording of the syntax? Is EXCLUSIVE the right word? How about FASTLOAD or BULKLOAD? Those words seem less likely to be misused in the future - i.e. we are invoking a special mode, rather than invoking a special go faster option. I don't consider the single-transaction to be a no-cost solution. You are adding flags to commands, and you are using a dump layout for performance where the purpose for the layout is not clear. The ALTER is clear to the user, and it allows nologging operations to happen after the table is created. In fact, for use in pg_dump, I think DROP is the proper operation for loading, not your transaction wrapping solution. We already agree we need DROP (or TRUNCATE), so why not use that rather than the transaction wrap idea? This was discussed on-list by 2 core team members, a committer and myself, but I see no requirements change here. You even accepted the invisible COPY optimization in your last post - why unpick that now? Please forgive my tone, but I am lost for reasonable yet expressive words. The --single-transaction mode would apply even if the dump was created using an earlier version of pg_dump. pg_dump has *not* been altered at all. (And I would again add that the idea was not my own) So, to summarize, I think we should add DROP/TRUNCATE, and use that by default (or optionally off?) in pg_dump, and, assuming we want EXCLUSIVE for more than just COPY, we need to add ALTER TABLE EXCLUSIVE. Would you mind stating again what you mean, just so I can understand this? Your summary isn't enough. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Anonymous CVS having problems?
It's been several hours since Tom's Repair EXPLAIN failure commit but anonymous CVS doesn't have it yet. That seems slower than usual; are there any problems? -- Michael Fuhr ---(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] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Simon Riggs wrote: On Fri, 2005-12-30 at 11:49 -0500, Bruce Momjian wrote: Yes, I know we agreed to the COPY LOCK, but new features now being requested, so we have to re-evaluate where we are going with COPY LOCK to get a more consistent solution. Thank you. Good. I think we can be happy that COPY LOCK didn't get into a release, so we don't have to support it forever. When we are adding features, we have to consider not only the current release, but future releases and what people will ask for in the future so the syntax can be expanded without breaking previous usage. Ah, but people wanted fast INSERT INTO ... SELECT, and that would use EXCLUSIVE too. What about a massive UPDATE? Perhaps that could use EXCLUSIVE? We don't want to add LOCK to every command that might use EXCLUSIVE. ALTER is much better for this. I agree if we thought EXCLUSIVE would only be used for COPY, we could use LOCK, but I am thinking it will be used for other commands as well. Agreed, I will look to implement this. Could the internals of my recent patch be reviewed? Changing the user interface is less of a problem than changing the internals, which is where the hard work takes place. I do not want to extend this work further only to have that part rejected later. OK, I will look it over this week or next. The implications of EXCLUSIVE are: - there will be a check on each and every I, U, D to check the state of the relation - *every* operation that attempts a write lock will attempt to acquire an EXCLUSIVE full table lock instead - following successful completion of *each* DML statement, the relation will be heap_sync'd involving a full scan of the buffer cache Yes, I think that is it. What we can do is implement EXCLUSIVE to affect only COPY at this point, and document that, and later add other commands. Can I clarify the wording of the syntax? Is EXCLUSIVE the right word? How about FASTLOAD or BULKLOAD? Those words seem less likely to be misused in the future - i.e. we are invoking a special mode, rather than invoking a special go faster option. The problem with the FASTLOAD/BULKLOAD words is that EXCLUSIVE mode is probably not the best for loading. I would think TRUNCATE would be a better option. In fact, in loading a table, I think both EXCLUSIVE and TRUNCATE would be the same, mostly. You would create the table, set its RELIABILITY to TRUNCATE, COPY into the table, then set the RELIABILITY to SHARE or DEFAULT. The second ALTER has to sync all the dirty data blocks, which the same thing EXCLUSIVE does at the conclusion of COPY. So, we need a name for EXCLUSIVE mode that suggests how it is different from TRUNCATE, and in this case, the difference is that EXCLUSIVE preserves the previous contents of the table on recovery, while TRUNCATE does not. Do you want to call the mode PRESERVE, or EXCLUSIVE WRITER? Anyway, the keywords are easy to modify, even after the patch is submitted. FYI, I usually go through keywords.c looking for a keyword we already use. I don't consider the single-transaction to be a no-cost solution. You are adding flags to commands, and you are using a dump layout for performance where the purpose for the layout is not clear. The ALTER is clear to the user, and it allows nologging operations to happen after the table is created. In fact, for use in pg_dump, I think DROP is the proper operation for loading, not your transaction wrapping solution. We already agree we need DROP (or TRUNCATE), so why not use that rather than the transaction wrap idea? This was discussed on-list by 2 core team members, a committer and myself, but I see no requirements change here. You even accepted the invisible COPY optimization in your last post - why unpick that now? Please forgive my tone, but I am lost for reasonable yet expressive words. Do you think you are the only one who has rewritten a patch multiple times? We all have. The goal is to get the functionality into the system in the most seamless way possible. Considering the number of people who use PostgreSQL, if it takes use 10 tries, it is worth it considering the thousands of people who will use it. Would you have us include a sub-optimal patch and have thousands of people adjust to its non-optimal functionality? I am sure you would not. Perhaps a company would say, Oh, just ship it, but we don't. The --single-transaction mode would apply even if the dump was created using an earlier version of pg_dump. pg_dump has *not* been altered at all. (And I would again add that the idea was not my own) I assume you mean this: http://archives.postgresql.org/pgsql-patches/2005-12/msg00257.php I guess with the ALTER commands I don't see much value in the --single-transaction flag. I am sure others suggested it, but would they suggest it now given our current direction. The fact that the patch was submitted does not give it any more weight --- the question is
Re: [HACKERS] Anonymous CVS having problems?
Michael Fuhr wrote: It's been several hours since Tom's Repair EXPLAIN failure commit but anonymous CVS doesn't have it yet. That seems slower than usual; are there any problems? cvsup is also unhappy: [EMAIL PROTECTED] ]# cvsup -g /home/cvsmirror/postgres.cvsup Cannot connect to cvsup.postgresql.org: Connection refused cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Anonymous CVS having problems?
Fixed On Fri, 30 Dec 2005, Michael Fuhr wrote: It's been several hours since Tom's Repair EXPLAIN failure commit but anonymous CVS doesn't have it yet. That seems slower than usual; are there any problems? -- Michael Fuhr ---(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 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Removing SORTFUNC_LT/REVLT
On Fri, Dec 30, 2005 at 10:18:48AM -0500, Tom Lane wrote: I really need to study your mail from the other day, but unfortunately other pressures will probably keep me from getting to it today :-(. One comment though --- it's not really sane to include ASC/DESC in there is it? I thought the spec wanted ORDER BY foo COLLATE bar [ASC/DESC] ... or if not, users certainly will. If every single collation has to be created in a matched ASC/DESC pair, you've done it wrong. Well, that's an interesting question. From a user point of view, forward and backwards isn't a real big deal. Internally it is though. Two places really care: - Pathkeys - Index keys For pathkeys in particular, I really don't like the propect of going through all the planning code changing a single pathkey (which is currently just the oid of the sorting operator) to a collation plus direction pair. Index keys are similar, though the code to change it not as much. We'd still need to invent another column to store the direction. For users ofcourse, we want them to look similar. Currently forward and backward collations have different names and different OIDs, though it's quite reasonable to give them the same name and require the user to say DESC to access the reverse collation (still with different OIDs). However, the real reason I think they should both be declared is that as actual objects, they don't resemble eachother very much. For example, an int4 forward collation can be merge joined with an int2 forward collation but not with an int2 backward collation. When you start considering relationships between collations, forwards and backwards behave very differently. Final food for thought: does the xid type have a collation. There's no b-tree operator class but it does have the concept of equality. It has a valid hashing function. Is this a collation? If so it has no order (so ASC/DESC don't apply). Yet hashing is also a property of the collation, not the type. The same string in different locales would hash differently. Where this heads is that a user creates a type, then a collation by specifying an order and a hash function. The system then creates the operator classes in the background. This is a radical departure from where we are now and not something I'm seriously proposing. Don't worry about how long it takes to think about it. 'tis the season to be jolly, no? I myself will be offline for most of next week anyway and the next few days are to be enjoyed. Seasons greetings, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp1AXRb2Cbax.pgp Description: PGP signature
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
As far as EXCLUSIVE or COPY LOCK goes, I think this would be useful functionality but perhaps there doesn't have to be any proprietary user interface to it at all. Why not just check if the conditions are already present to allow the optimization and if so go ahead. That is, if the current transaction already has an exclusive lock on the table and there are no indexes (and PITR isn't active) then Postgres could go ahead and use the same WAL skipping logic as the other operations that already so so. This would work for inserts whether coming from COPY or plain SQL INSERTs. The nice thing about this is that the user's SQL wouldn't need any proprietary extensions at all. Just tell people to do BEGIN; LOCK TABLE foo; COPY foo from ... COMMIT; There could be a COPY LOCK option to obtain a lock, but it would be purely for user convenience so they don't have to bother with BEGIN and COMMIt. The only downside is a check to see if an exclusive table lock is present on every copy and insert. That might be significant but perhaps there are ways to finess that. If not perhaps only doing it on COPY would be a good compromise. -- greg ---(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] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Greg Stark wrote: As far as EXCLUSIVE or COPY LOCK goes, I think this would be useful functionality but perhaps there doesn't have to be any proprietary user interface to it at all. Why not just check if the conditions are already present to allow the optimization and if so go ahead. That is, if the current transaction already has an exclusive lock on the table and there are no indexes (and PITR isn't active) then Postgres could go ahead and use the same WAL skipping logic as the other operations that already so so. This would work for inserts whether coming from COPY or plain SQL INSERTs. The nice thing about this is that the user's SQL wouldn't need any proprietary extensions at all. Just tell people to do BEGIN; LOCK TABLE foo; COPY foo from ... COMMIT; There could be a COPY LOCK option to obtain a lock, but it would be purely for user convenience so they don't have to bother with BEGIN and COMMIt. The only downside is a check to see if an exclusive table lock is present on every copy and insert. That might be significant but perhaps there are ways to finess that. If not perhaps only doing it on COPY would be a good compromise. Well, again, if we wanted to use EXCLUSIVE only for COPY, this might make sense. However, also consider that the idea for EXCLUSIVE was that users could continue read-only queries on the table while it is being loaded (like COPY allows now), and that in EXCLUSIVE mode, we are only going to write into new pages. If someone has an exclusive lock on the table and does a COPY or SELECT INTO do we want to assume we are only going to write into new pages, and do we want to force an exclusive lock rather than a single-writer lock? I don't think so. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Fri, 2005-12-30 at 16:14 -0500, Bruce Momjian wrote: This was discussed on-list by 2 core team members, a committer and myself, but I see no requirements change here. You even accepted the invisible COPY optimization in your last post - why unpick that now? Please forgive my tone, but I am lost for reasonable yet expressive words. Do you think you are the only one who has rewritten a patch multiple times? We all have. The goal is to get the functionality into the system in the most seamless way possible. Considering the number of people who use PostgreSQL, if it takes use 10 tries, it is worth it considering the thousands of people who will use it. Would you have us include a sub-optimal patch and have thousands of people adjust to its non-optimal functionality? I am sure you would not. Perhaps a company would say, Oh, just ship it, but we don't. You're right. Not like we've not been here before, eh? [I'll look at the tech another day] Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Simon Riggs wrote: On Fri, 2005-12-30 at 16:14 -0500, Bruce Momjian wrote: This was discussed on-list by 2 core team members, a committer and myself, but I see no requirements change here. You even accepted the invisible COPY optimization in your last post - why unpick that now? Please forgive my tone, but I am lost for reasonable yet expressive words. Do you think you are the only one who has rewritten a patch multiple times? We all have. The goal is to get the functionality into the system in the most seamless way possible. Considering the number of people who use PostgreSQL, if it takes use 10 tries, it is worth it considering the thousands of people who will use it. Would you have us include a sub-optimal patch and have thousands of people adjust to its non-optimal functionality? I am sure you would not. Perhaps a company would say, Oh, just ship it, but we don't. You're right. Not like we've not been here before, eh? [I'll look at the tech another day] I know it is discouraging. I have felt it many times myself. However, I have to keep my eye on the greater good that we are doing as a project, and that my frustration is a small price to pay for the greater usability we will give to our users. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
On Fri, Dec 30, 2005 at 11:02:20AM -0700, Michael Fuhr wrote: On Fri, Dec 30, 2005 at 03:15:03AM -0500, Sebastian wrote: Any ideas for a temporary work around? You could try querying the system catalogs directly instead of using the information_schema views; You could also set enable_nestloop to off for your original query. Now that EXPLAIN is fixed it looks like 8.1.1 is choosing a nested loop where a hash join is actually much faster. -- Michael Fuhr ---(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] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Bruce Momjian pgman@candle.pha.pa.us writes: BEGIN; LOCK TABLE foo; COPY foo from ... COMMIT; There could be a COPY LOCK option to obtain a lock, but it would be purely for user convenience so they don't have to bother with BEGIN and COMMIt. The only downside is a check to see if an exclusive table lock is present on every copy and insert. That might be significant but perhaps there are ways to finess that. If not perhaps only doing it on COPY would be a good compromise. Well, again, if we wanted to use EXCLUSIVE only for COPY, this might make sense. However, also consider that the idea for EXCLUSIVE was that users could continue read-only queries on the table while it is being loaded (like COPY allows now), and that in EXCLUSIVE mode, we are only going to write into new pages. Well I pictured the above kicking in for any insert. You can't do it on deletes and updates anyways since torn pages could cause the table to become corrupt. We could add a LOCK TABLE SHARED feature to allow the appropriate type of lock to be acquired. But now that I think further on this that doesn't really make this free. fsyncing a table isn't free. If some other transaction has come and done some massive updates on the table then I come along and do a single quick insert I don't necessarily want to fsync all those pending writes, it's cheaper to fsync the WAL log. If someone has an exclusive lock on the table and does a COPY or SELECT INTO do we want to assume we are only going to write into new pages, and do we want to force an exclusive lock rather than a single-writer lock? I don't think so. And only using new pages is itself a cost as well. Though I think the fact that it would tend to mean a lot less seeking and more sequential i/o would tend to make it a worth the extra garbage in the table. It might be useful having some kind of LOCK TABLE SHARED anyways. It seems silly to have the functionality in the database and not expose it for users. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Greg Stark wrote: Bruce Momjian pgman@candle.pha.pa.us writes: BEGIN; LOCK TABLE foo; COPY foo from ... COMMIT; There could be a COPY LOCK option to obtain a lock, but it would be purely for user convenience so they don't have to bother with BEGIN and COMMIt. The only downside is a check to see if an exclusive table lock is present on every copy and insert. That might be significant but perhaps there are ways to finess that. If not perhaps only doing it on COPY would be a good compromise. Well, again, if we wanted to use EXCLUSIVE only for COPY, this might make sense. However, also consider that the idea for EXCLUSIVE was that users could continue read-only queries on the table while it is being loaded (like COPY allows now), and that in EXCLUSIVE mode, we are only going to write into new pages. Well I pictured the above kicking in for any insert. You can't do it on deletes and updates anyways since torn pages could cause the table to become corrupt. True, but UPDATE could save 1/2 the log I/O because the new rows would not have to be logged. We could add a LOCK TABLE SHARED feature to allow the appropriate type of lock to be acquired. Do you really want this behavior to be controlled by the locking mode? That seem strange to me, as well as confusing, especially if you already have some other kind of lock on the table. But now that I think further on this that doesn't really make this free. fsyncing a table isn't free. If some other transaction has come and done some massive updates on the table then I come along and do a single quick insert I don't necessarily want to fsync all those pending writes, it's cheaper to fsync the WAL log. That is true. If someone has an exclusive lock on the table and does a COPY or SELECT INTO do we want to assume we are only going to write into new pages, and do we want to force an exclusive lock rather than a single-writer lock? I don't think so. And only using new pages is itself a cost as well. Though I think the fact that it would tend to mean a lot less seeking and more sequential i/o would tend to make it a worth the extra garbage in the table. It might be useful having some kind of LOCK TABLE SHARED anyways. It seems silly to have the functionality in the database and not expose it for users. We could if there is a use-case for it. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
Changing enable_nestloop works great -- I'll use it for now. Thanks all On 12/30/05, Michael Fuhr [EMAIL PROTECTED] wrote: On Fri, Dec 30, 2005 at 11:02:20AM -0700, Michael Fuhr wrote: On Fri, Dec 30, 2005 at 03:15:03AM -0500, Sebastian wrote: Any ideas for a temporary work around? You could try querying the system catalogs directly instead of using the information_schema views; You could also set enable_nestloop to off for your original query. Now that EXPLAIN is fixed it looks like 8.1.1 is choosing a nested loop where a hash join is actually much faster. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] EINTR error in SunOS
On Fri, 30 Dec 2005, Tom Lane wrote:
I've heard of this in connection with NFS ... is your DB on an NFS
filesystem by any chance?
I have patched IO routines in backend/storage that POSIX says EINTR is
possible except unlink(). Though POSIX says EINTR is not possible, during
many regressions, I found it sometimes sets this errno on NFS (I still
don't know where is the smoking-gun):
TRUNCATE TABLE trunc_c,trunc_d,trunc_e; -- ok
+ WARNING: could not remove relation 1663/16384/37822: Interrupted system call
There are many other unlink() scattered in backend, some even without
error check. Shall we patch pg_unlink for this situation and replace them
like this:
pg_unlink(const char* path, int errlevel)
{
retry:
returnCode = unlink(path);
if (returnCode 0 errno==EINTR)
goto retry;
if other_errors
elog(elevel, ...);
return returnCode;
}
Or
pg_unlink(const char* path)
{
/* no elog -- but we still have to do error check */
}
Or
let it be ...
If we decide to do something for unlink(), then we'd better do something
for other EINTR-possible IO routines for fairness :-)
By the way, seems POSIX is not very consistent with EINTR. For example,
closedir() can set EINTR, but opendir()/readdir() can't. Any magic in it?
Regards,
Qingqing
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Removing SORTFUNC_LT/REVLT
Martijn van Oosterhout kleptog@svana.org writes: Yet hashing is also a property of the collation, not the type. The same string in different locales would hash differently. I think this is a mistake -- the same mistake that got us into trouble with Turkish. Hashing depends on the concept of equality which is integral to the type. Two things are either the same or they aren't, and that can't change based on context. Specifically in the case of strings, two strings should only be considered equal if they consist of the exact same series of characters. (That is, they could be encoded differently but they have to encode the same actual characters.) That they happen to sort equally compared to all other strings doesn't mean that they're equal. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] EINTR error in SunOS
Qingqing Zhou [EMAIL PROTECTED] writes: On Fri, 30 Dec 2005, Tom Lane wrote: I've heard of this in connection with NFS ... is your DB on an NFS filesystem by any chance? I have patched IO routines in backend/storage that POSIX says EINTR is possible except unlink(). Though POSIX says EINTR is not possible, during many regressions, I found it sometimes sets this errno on NFS (I still don't know where is the smoking-gun): Well there is a reason intr is not the default for NFS mounts. It's precisely because it breaks the traditional unix filesystem interface. Syscalls that historically are not interruptible become interruptible and not all programs behave properly when that occurs. In any case POSIX explicitly allows functions to return other errors aside from those specified as long as it's for error conditions not listed. [Chapter 2 Section 3, paragraph 6] Implementations may support additional errors not included in this list, may generate errors included in this list under circumstances other than those described here, or may contain extensions or limitations that prevent some errors from occurring. The ERRORS section on each reference page specifies whether an error shall be returned, or whether it may be returned. Implementations shall not generate a different error number from the ones described here for error conditions described in this volume of IEEE Std 1003.1-2001, but may generate additional errors unless explicitly disallowed for a particular function Ironically EINTR *is* singled out to be specifically forbidden to be returned from some system calls but only those in the Threads option which are mostly pthread* functions. unlink isn't covered by that prohibition. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] pgAdmin translation to italian language
I'd like to contribute to localize the application to italian language. Do you want my help?? I wait your reply. Michele ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
