Re: [HACKERS] UTF8 or Unicode
On Tue, 2005-02-15 at 14:33 +0100, Peter Eisentraut wrote: Am Dienstag, 15. Februar 2005 10:22 schrieb Karel Zak: in PG: unicode = utf8 = utf-8 Our internal routines in src/backend/utils/mb/encnames.c accept all synonyms. The official internal PG name for UTF-8 is UNICODE :-( I think in the SQL standard the official name is UTF8. If someone wants to verify that this is the case and is exactly the encoding we offer (perhaps modulo the 0x1 issue), then it might make sense to change the canonical form to UTF8. Yes, I think we should fix it and remove UNICODE and WIN encoding names from PG code. Karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Help me recovering data
Just wondering after this discussion: Is transaction wraparound limited to a database or to an installation ? i.e. can heavy traffic in one db affect another db in the same installation ? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] win32 performance - fsync question
Tom Lane wrote: Portability, or rather the complete lack of it. Stuff that isn't in the Single Unix Spec is a hard sell. O_DIRECT is reasonably common among modern Unixen (it is supported by Linux, FreeBSD, and probably a couple of the commercial variants like AIX or IRIX); it should also be reasonably easy to check for support at configure time. It's on my TODO list to take a gander at adding support for O_DIRECT for WAL, I just haven't gotten around to it yet. -Neil ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Help me recovering data
On Fri, 18 Feb 2005 04:38 pm, Kevin Brown wrote: Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: BTW, why not do an automatic vacuum instead of shutdown ? At least the DB do not stop working untill someone study what the problem is and how solve it. No, the entire point of this discussion is to whup the DBA upside the head with a big enough cluestick to get him to install autovacuum. Once autovacuum is default, it won't matter anymore. I have a concern about this that I hope is just based on some misunderstanding on my part. My concern is: suppose that a database is modified extremely infrequently? So infrequently, in fact, that over a billion read transactions occur before the next write transaction. Once that write transaction occurs, you're hosed, right? Autovacuum won't catch this because it takes action based on the write activity that occurs in the tables. So: will autovacuum be coded to explicitly look for transaction wraparound, or to automatically vacuum every N number of transactions (e.g., 500 million)? autovacuum already checks for both Transaction wraparound, and table updates. It vacuums individual tables as they need it, from a free space/recovery point of view. It also does checks to ensure that no database is nearing transaction wraparound, if it is, it initiates a database wide vacuum to resolve that issue. Regards Russell Smith ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Help me recovering data
On Fri, 18 Feb 2005 08:53 pm, Jürgen Cappel wrote: Just wondering after this discussion: Is transaction wraparound limited to a database or to an installation ? i.e. can heavy traffic in one db affect another db in the same installation ? XID's are global to the pg cluster, or installation. So not using a database will still cause XID wraparound to occur on that database. Regards Russell Smith. ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] UTF8 or Unicode
Karel Zak wrote: Yes, I think we should fix it and remove UNICODE and WIN encoding names from PG code. The JDBC driver asks for a UNICODE client encoding before it knows the server version it is talking to. How do you avoid breaking this? -O ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] UTF8 or Unicode
On Sat, 2005-02-19 at 00:27 +1300, Oliver Jowett wrote: Karel Zak wrote: Yes, I think we should fix it and remove UNICODE and WIN encoding names from PG code. The JDBC driver asks for a UNICODE client encoding before it knows the server version it is talking to. How do you avoid breaking this? Fix JDBC driver as soon as possible. Add to 8.1 release notes: encoding names 'UNICODE' and 'WIN' are deprecated and it will removed in next release. Please, use correct names UTF-8 and WIN1215. 8.2: remove it. OK? Karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] UTF8 or Unicode
Add to 8.1 release notes: encoding names 'UNICODE' and 'WIN' are deprecated and it will removed in next release. Please, use correct names UTF-8 and WIN1215. 8.2: remove it. OK? Why on earth remove it? Just leave it in as an alias to UTF8 Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] win32 performance - fsync question
One point that I no longer recall the reasoning behind is that xlog.c doesn't think O_SYNC is a preferable default over fsync. For larger (8k) transactions O_SYNC|O_DIRECT is only good with the recent pending patch to group WAL writes together. The fsync method gives the OS a chance to do the grouping. (Of course it does not matter if you have small tx 8k WAL) Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] UTF8 or Unicode
Karel Zak wrote: On Sat, 2005-02-19 at 00:27 +1300, Oliver Jowett wrote: Karel Zak wrote: Yes, I think we should fix it and remove UNICODE and WIN encoding names from PG code. The JDBC driver asks for a UNICODE client encoding before it knows the server version it is talking to. How do you avoid breaking this? Fix JDBC driver as soon as possible. How, exactly? Ask for a 'utf8' client encoding instead of 'UNICODE'? Will this work if the driver is connecting to an older server? Add to 8.1 release notes: encoding names 'UNICODE' and 'WIN' are deprecated and it will removed in next release. Please, use correct names UTF-8 and WIN1215. 8.0 appears to spell it 'utf8'. Removing the existing aliases seems like a fairly gratuitous incompatibility to introduce to me. -O ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] UTF8 or Unicode
-Original Message- From: [EMAIL PROTECTED] on behalf of Oliver Jowett Sent: Fri 2/18/2005 11:27 AM To: Karel Zak Cc: List pgsql-hackers Subject: Re: [HACKERS] UTF8 or Unicode Karel Zak wrote: Yes, I think we should fix it and remove UNICODE and WIN encoding names from PG code. The JDBC driver asks for a UNICODE client encoding before it knows the server version it is talking to. How do you avoid breaking this? So does pgAdmin. Regards, Dave ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] sigint psql
I'm communicating with psql via a pipe stream. This works pretty well, but one problem I have is trying to cancel an operation. If I send a sigint, psql dies. In looking at the source I gather this is because it assumes I'm in non-interactive mode (pset.notty is true). I was wondering if there was some way to work around this short of recompiling the source. I need to do the same thing on Windows. Thanks for any suggestions, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] SPI_finish and RegisterExprContextCallback
I'd like to write a C-function that returns a SETOF a complex type. This set is obtained from a query performed using an SPI cursor. I don't want to build the complete set in memory so I tried the following. 1. During the SRF_IS_FIRSTCALL phase, I do an SPI_connect and I create the cursor using SPI_prepare and SPI_cursor_open. 2. For each call, I obtain row(s) as needed using SPI_cursor_fetch. A row is copied before I return the tuple in a SRF_RETURN_NEXT 3. When I have no more rows, I close the cursor and issue a SPI_finish. Then I return SRF_RETURN DONE. This works beautifully. Now I'm trying to deal with scenarios where I never reach the end of the set because the evaluator doesn't need all rows. So I use RegisterExprContextCallback to register a callback and instead of doing an SPI_finish when the end is reached I attempt to do this in the callback. The callback is called OK but when it calls SPI_finish I get an illegal memory access signal. How am I supposed to do this? Is it at all possible to stream the results of one query to another using a SETOF function? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SPI_finish and RegisterExprContextCallback
Thomas Hallgren [EMAIL PROTECTED] writes: The callback is called OK but when it calls SPI_finish I get an illegal memory access signal. From where? Minimum respect for the time of your fellow hackers would suggest including a gdb traceback in questions like this. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Help me recovering data
On Thursday 17 February 2005 07:47, [EMAIL PROTECTED] wrote:
Gaetano Mendola [EMAIL PROTECTED] writes:
We do ~4000 txn/minute so in 6 month you are screewd up...
Sure, but if you ran without vacuuming for 6 months, wouldn't you notice
the
huge slowdowns from all those dead tuples before that?
I would think that only applies to databases where UPDATE and DELETE are
done often. What about databases that are 99.999% inserts? A DBA lightly
going over the docs may not even know that vacuum needs to be run.
Yup... I don't vacuum a least a 100 of the tables in my schema cause they are
continuous insert with big deletions once every 6 months or so. Generally
speaking it isn't worth the performance hit to vacuum these big tables
regularly, so I only do it when I have to like every six months when wrap
around gets close.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] win32 performance - fsync question
Magnus prepared a trivial patch which added the O_SYNC flag for windows and mapped it to FILE_FLAG_WRITE_THROUGH in win32_open.c. We pg_benched it and here are the results of our test on my WinXP workstation on a 10k raptor: Settings were pgbench -t 100 -c 10. fsync = off: ~ 280 tps fsync on, WAL=fsync: ~ 35 tps fsync on, WAL=open_sync write cache policy on: ~ 240 tps fsync on, WAL=open_sync write cache policy off: ~ 80 tps 80 tps, btw, is about the results I'd expect from linux on this hardware. Also, the open_sync method plays much nicer with RAID devices, but it would need some more rigorous testing before I'd personally certify it as safe. As an aside, it doesn't look like the open_sync can be trusted with write caching policy on the disk (the default), and that's worth noting. Merlin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Help me recovering data
Russell Smith wrote: On Fri, 18 Feb 2005 04:38 pm, Kevin Brown wrote: Tom Lane wrote: No, the entire point of this discussion is to whup the DBA upside the head with a big enough cluestick to get him to install autovacuum. Once autovacuum is default, it won't matter anymore. I have a concern about this that I hope is just based on some misunderstanding on my part. My concern is: suppose that a database is modified extremely infrequently? So infrequently, in fact, that over a billion read transactions occur before the next write transaction. Once that write transaction occurs, you're hosed, right? Autovacuum won't catch this because it takes action based on the write activity that occurs in the tables. So: will autovacuum be coded to explicitly look for transaction wraparound, or to automatically vacuum every N number of transactions (e.g., 500 million)? autovacuum already checks for both Transaction wraparound, and table updates. It vacuums individual tables as they need it, from a free space/recovery point of view. It also does checks to ensure that no database is nearing transaction wraparound, if it is, it initiates a database wide vacuum to resolve that issue. Right, the check that autovacuum does for wraparound is totally separate from the monitoring of inserts updates and deletes. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Data loss, vacuum, transaction wrap-around
I want to see if there is a concensus of opinion out there. We've all known that data loss could happen if vacuum is not run and you perform more than 2b transactions. These days with faster and bigger computers and disks, it more likely that this problem can be hit in months -- not years. To me, the WORST thing a program can do is lose data. (Certainly this is bad for a database.) I don't think there is any real excuse for this. While the 2b transaction problem was always there, it seemed so remote that I never obcessed about it. Now that it seems like a real problem that more than one user has hit, I am worried. In fact, I think it is so bad, that I think we need to back-port a fix to previous versions and issue a notice of some kind. Here as my suggestions: (1) As Tom has already said, at some point start issuing warning in the log that vacuum needs to be run. (2) At some point, stop accepting transactions on anything but template1, issuing an error saying the vacuum needs to be run. (3) Either with psql on template1 or postgres or some vacuumall program, open the database in single user mode or on template1 and vacuum database. (4) This should remain even after autovacuum is in place. If for some reason auto vacuum is installed but not running, we still need to protect the data from a stupid admin. (Last time I looked, auto vacuum used various stats, and that may be something an admin disables.) (5) Vacuum could check for a wrap-around condition in the database cluster and take it upon itself to run more broadly even if it was directed only towards a table. We've been saying that mysql is ok if you don't care about your data, I would hate if people started using this issue against postgresql. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SPI_finish and RegisterExprContextCallback
Tom Lane wrote: From where? Minimum respect for the time of your fellow hackers would suggest including a gdb traceback in questions like this. My apologies. I'll do that next time. I was on a win32 system and the gdb that comes with the MinGW environment just doesn't do it for me (if anyone out there knows how to make the MinGW gdb work I'd very much appreciate any advice). I have a Linux box too though, so that's no excuse. Anyway, I think I've narrowed the problem down a bit. And indeed, I think there is a somewhat serious limitation in the SPI layer. Here's what happens: 1. I call a function that does an SPI_connect, SPI_prepare, SPI_cursor_open, and finally attempts to do an SPI_cursor_fetch. 2. Since the SQL statement I'm executing contains a call to function returning SETOF, and since that function in turn accesses the database, it in turn will issue a SPI_connect in its SRF_IS_FIRSTCALL phase. It then returns its first row. 3. The SPI_cursor_fetch call in my outer function now fails with improper call to spi_printtup since it is asociated with the first SPI_connect and since the second SPI_connect has not reached it's matching SPI_finish yet. I onclude that with the current implementation there's no way of achiving data streaming using SPI. When I say streaming, I mean a SETOF function that, one row at a time, delivers the result that it reads from a SPI_cursor. No matter what I do, short of building the whole set in memory, will result in unbalanced SPI_connect/SPI_finish calls. With reservations for me missing something painfully obvious of course. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] win32 performance - fsync question
Magnus prepared a trivial patch which added the O_SYNC flag for windows and mapped it to FILE_FLAG_WRITE_THROUGH in win32_open.c. Attached is this trivial patch. As Merlin says, it needs some more reliability testing. But the numbers are at least reasonable - it *seems* like it's doing the right thing (as long as you turn off write cache). And it's certainly a significant performance increase - it brings the speed almost up to the same as linux. //Magnus o_sync.patch Description: o_sync.patch ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] sigint psql
John DeSoi [EMAIL PROTECTED] writes: I'm communicating with psql via a pipe stream. This works pretty well, but one problem I have is trying to cancel an operation. If I send a sigint, psql dies. In looking at the source I gather this is because it assumes I'm in non-interactive mode (pset.notty is true). I was wondering if there was some way to work around this short of recompiling the source. I need to do the same thing on Windows. Thanks for any suggestions, On Unix you could run 'psql' through a pty rather than a pipe. No idea what the Windows equivalent would be. -Doug ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] SPI_finish and RegisterExprContextCallback
Thomas Hallgren [EMAIL PROTECTED] writes: 1. I call a function that does an SPI_connect, SPI_prepare, SPI_cursor_open, and finally attempts to do an SPI_cursor_fetch. 2. Since the SQL statement I'm executing contains a call to function returning SETOF, and since that function in turn accesses the database, it in turn will issue a SPI_connect in its SRF_IS_FIRSTCALL phase. It then returns its first row. You're right, you can't just return from that inner function while leaving its SPI connection open. It might be interesting to redesign SPI around the notion of independent connection objects rather than necessarily having a stack of 'em. I think that could be made to work ... but not while preserving the existing SPI API. I'm hesitant to break a ton of user-written code for a feature that only one person has needed :-( regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] SPI_finish and RegisterExprContextCallback
Tom, You're right, you can't just return from that inner function while leaving its SPI connection open. It might be interesting to redesign SPI around the notion of independent connection objects rather than necessarily having a stack of 'em. I made the same reflection looking at the SPI code. It would be nice if something corresponding to _SPI_current could be passed around. I think that could be made to work ... but not while preserving the existing SPI API. I'm not so sure you'd have to. A public API that can disable stack handling and instead use something similar to MemoryContextSwitchTo but for an _SPI_current like structure would perhaps be sufficient? I'm hesitant to break a ton of user-written code for a feature that only one person has needed :-( It is a fairly serious design flaw IMHO. I discovered it and so far no one else has complained. That's true for all flaws at first. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
[EMAIL PROTECTED] writes: In fact, I think it is so bad, that I think we need to back-port a fix to previous versions and issue a notice of some kind. They already do issue notices --- see VACUUM. A real fix (eg the forcible stop we were talking about earlier) will not be reasonable to back-port. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
Tom Lane wrote: [EMAIL PROTECTED] writes: In fact, I think it is so bad, that I think we need to back-port a fix to previous versions and issue a notice of some kind. They already do issue notices --- see VACUUM. A real fix (eg the forcible stop we were talking about earlier) will not be reasonable to back-port. I hope this question isn't too stupid Is it be possible to create a vacuum wraparound or vacuum xidreset command which would do the work required to fix the wraparound problem, without being as expensive as a normal vacuum of an entire database? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
Matthew T. O'Connor matthew@zeut.net writes: I hope this question isn't too stupid Is it be possible to create a vacuum wraparound or vacuum xidreset command which would do the work required to fix the wraparound problem, without being as expensive as a normal vacuum of an entire database? I don't think it'd be worth the trouble. You could skip index cleanup if you didn't actually delete any tuples, but you'd still have to do all of the scanning work. The cases where people think they don't need to do vacuum (because the table didn't have any deleted tuples) wouldn't get any cheaper at all. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
More suggestions: (1) At startup, postmaster checks for an XID, if it is close to a problem, force a vacuum. (2) At sig term shutdown, can the postmaster start a vacuum? (3) When the XID count goes past the trip wire can it spontaneously issue a vacuum? NOTE: Suggestions 1 and 2 are for 8.0 and prior. 3 is for later than 8.0.1 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: A real fix (eg the forcible stop we were talking about earlier) will not be reasonable to back-port. Would at least a automated warning mechanism be a reasonable backport? No, because the hard part of the problem actually is detecting that the condition exists in a reasonably cheap way. The check in VACUUM is really extremely expensive, which is why we don't make it except after completing a database-wide vacuum. Once we have an XID limit value sitting in shared memory then the code to use it (generate warnings and/or error out) is simple; it's initializing that value during postmaster start that I consider too complicated/risky to stick into existing versions. There is another issue here, which is that I have no faith that the people who actually need this are going to be clueful enough to update to 7.4.8 or 7.3.10 or whatever they'd need... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
[EMAIL PROTECTED] writes: More suggestions: (1) At startup, postmaster checks for an XID, if it is close to a problem, force a vacuum. Useless to a system that's run 24x7; also presumes the existence of a complete solution anyway (since getting the postmaster to find that out is the hard part). (2) At sig term shutdown, can the postmaster start a vacuum? Certainly not. We have to assume that SIGTERM means we are under a short-term sentence of death from init. And if it's a manual stop it doesn't sound much better: the sort of DBA that needs this feature is likely to decide he should kill -9 the postmaster because it's taking too long to shut down. (3) When the XID count goes past the trip wire can it spontaneously issue a vacuum? Only in the database you're connected to, which very likely isn't where the problem is. Moreover, having N backends all decide they need to do this at once doesn't sound like a winner. Furthermore, this still presumes the existence of the hard part of the solution, which is knowing where the trip point is. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
There is another issue here, which is that I have no faith that the people who actually need this are going to be clueful enough to update to 7.4.8 or 7.3.10 or whatever they'd need... Well I can't argue with that one ;) regards, tom lane -- Command Prompt, Inc., your source for PostgreSQL replication, professional support, programming, managed services, shared and dedicated hosting. Home of the Open Source Projects plPHP, plPerlNG, pgManage, and pgPHPtoolkit. Contact us now at: +1-503-667-4564 - http://www.commandprompt.com begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
Tom Lane [EMAIL PROTECTED] writes: (3) When the XID count goes past the trip wire can it spontaneously issue a vacuum? Only in the database you're connected to, which very likely isn't where the problem is. Moreover, having N backends all decide they need to do this at once doesn't sound like a winner. Furthermore, this still presumes the existence of the hard part of the solution, which is knowing where the trip point is. Alright, I have a suggestion. If the database kept a oldest xid for each table then there wouldn't be any expensive work to scan the table looking for an oldest xid. The only time oldest xid needs to be updated is when vacuum is run, which is precisely when it would be known. There could be a per-database oldest xid that any vacuum on any table updates (by skimming all the oldest xids for the current database). If that's stored in the shared pg_database table then it's accessible regardless of what database you connect to, no? Then on every connection and every n-thousandth transaction you just have to check the oldest xid for all the databases, and make sure the difference between the oldest one and the current xid is reasonable. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Get rid of system attributes in pg_attribute?
I'm wondering how useful it is to store explicit representations of the system attributes in pg_attribute. We could very easily hard-wire those things instead, which would make for a large reduction in the number of entries in pg_attribute. (In the current regression database nearly half of the rows have attnum 0.) I think the impact on the backend would be pretty minimal, but I'm wondering if removing these entries would be likely to break any client-side code. Does anyone know of client code that actually pays attention to pg_attribute rows with negative attnums? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
Greg Stark [EMAIL PROTECTED] writes: There could be a per-database oldest xid that any vacuum on any table updates (by skimming all the oldest xids for the current database). If that's stored in the shared pg_database table then it's accessible regardless of what database you connect to, no? You mean like pg_database.datfrozenxid? The problem is not that we don't have the data. The problem is getting the data to where it's needed, which is GetNewTransactionId(). That routine cannot be responsible for finding it out for itself, because we haven't yet started a transaction at the time where we need to know if it's safe to consume an XID. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Get rid of system attributes in pg_attribute?
-Original Message- From: [EMAIL PROTECTED] on behalf of Tom Lane Sent: Fri 2/18/2005 8:48 PM To: pgsql-hackers@postgresql.org Subject: [HACKERS] Get rid of system attributes in pg_attribute? Does anyone know of client code that actually pays attention to pg_attribute rows with negative attnums? pgAdmin certainly knows about them, but I don't believe it'll break if they go. I'm a few thousand miles from my laptop atm though so I cannot look more throughly right now. Regards, Dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] SPI_finish and RegisterExprContextCallback
I found another piece of information that might be of interest. This is related to nested calls and the ExprContextCallback but not related to nested active cursors. AtCommitPortals (portalmem.c) iterates over the entries in the PortalHashTable. This causes a chain of calls that sometimes reach an ExprContextCallback. If that callback issues a succesfull SPI_cursor_close some problems might lay ahead. As the AcCommitPortals iteration continues, it sometimes encounter a deleted portal and elogs with an error stating trying to delete portal name that does not exist. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
On Sat, 19 Feb 2005 04:10 am, Tom Lane wrote: [EMAIL PROTECTED] writes: In fact, I think it is so bad, that I think we need to back-port a fix to previous versions and issue a notice of some kind. They already do issue notices --- see VACUUM. A real fix (eg the forcible stop we were talking about earlier) will not be reasonable to back-port. Not to be rude, but if backporting is not an option, why do we not just focus on the job of getting autovacuum into 8.1, and not have to think about how a patch that will warn users will work? Regards Russell Smith ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
On Sat, 19 Feb 2005 04:10 am, Tom Lane wrote: [EMAIL PROTECTED] writes: In fact, I think it is so bad, that I think we need to back-port a fix to previous versions and issue a notice of some kind. They already do issue notices --- see VACUUM. A real fix (eg the forcible stop we were talking about earlier) will not be reasonable to back-port. Not to be rude, but if backporting is not an option, why do we not just focus on the job of getting autovacuum into 8.1, and not have to think about how a patch that will warn users will work? Unless I'm mistaken, even autovacuum may not be enough. AFAIK, autovacuum depends on the statistics daemon, and some admins may turn that off for performance. Even so, how unlikely is it that autovacuum doesn't run. I think there should be a 100% no data loss fail safe. Anything less is a cop-out. I can't see one successful argument that starts with data loss and ends with maintenence. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
Russell Smith wrote: On Sat, 19 Feb 2005 04:10 am, Tom Lane wrote: [EMAIL PROTECTED] writes: In fact, I think it is so bad, that I think we need to back-port a fix to previous versions and issue a notice of some kind. They already do issue notices --- see VACUUM. A real fix (eg the forcible stop we were talking about earlier) will not be reasonable to back-port. Not to be rude, but if backporting is not an option, why do we not just focus on the job of getting autovacuum into 8.1, and not have to think about how a patch that will warn users will work? What if autovacuum is turned off for some reason? Or fails? A more graceful failure along the lines suggested would be a good thing, ISTM. I agree with Tom about not backpatching, though. The situation seems analogous with a car owner who neglects the clear instructions in the manual to perform regular oil changes and then finds to his great surprise that the car stops running. It's hardly the manufacturer's fault. cheers andrew ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
In [EMAIL PROTECTED], on 02/18/05 at 09:48 PM, Andrew Dunstan [EMAIL PROTECTED] said: Russell Smith wrote: On Sat, 19 Feb 2005 04:10 am, Tom Lane wrote: [EMAIL PROTECTED] writes: In fact, I think it is so bad, that I think we need to back-port a fix to previous versions and issue a notice of some kind. They already do issue notices --- see VACUUM. A real fix (eg the forcible stop we were talking about earlier) will not be reasonable to back-port. Not to be rude, but if backporting is not an option, why do we not just focus on the job of getting autovacuum into 8.1, and not have to think about how a patch that will warn users will work? What if autovacuum is turned off for some reason? Or fails? A more graceful failure along the lines suggested would be a good thing, ISTM. I agree with Tom about not backpatching, though. The situation seems analogous with a car owner who neglects the clear instructions in the manual to perform regular oil changes and then finds to his great surprise that the car stops running. It's hardly the manufacturer's fault. cheers andrew I never did like car analogies... At least a car comes with an IDIOT light or maybe even an oil pressure gauge. Something like that (the idiot light) is missing from postgreSQL. The oil pressure gauge would be good to have, kind of like a gauge that lets you when you are about to run out of fuel. Of course the best thing is the auto-fill gas tank. -- --- [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] UTF8 or Unicode
Dave Page wrote: Karel Zak wrote: Yes, I think we should fix it and remove UNICODE and WIN encoding names from PG code. The JDBC driver asks for a UNICODE client encoding before it knows the server version it is talking to. How do you avoid breaking this? So does pgAdmin. I think we just need to _favor_ UTF8. The question is where are we favoring Unicode rather than UTF8? -- 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: 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] Data loss, vacuum, transaction wrap-around
[EMAIL PROTECTED] writes: I think there should be a 100% no data loss fail safe. Possibly we need to recalibrate our expectations here. The current situation is that PostgreSQL will not lose data if: 1. Your disk drive doesn't screw up (eg, lie about write complete, or just plain die on you). 2. Your kernel and filesystem don't screw up. 3. You follow the instructions about routine vacuuming. 4. You don't hit any bugs that we don't know about. I agree that it's a nice idea to be able to eliminate assumption #3 from our list of gotchas, but the big picture is that it's hard to believe that doing this will make for a quantum jump in the overall level of reliability. I think I listed the risks in roughly the right order of severity ... I'm willing to fix this for 8.1 (and am already in process of drafting a patch), especially since it ties into some other known problems such as the pg_pwd/pg_group files not being properly reconstructed after PITR recovery. But I think that a Chinese fire drill is not called for, and backpatching a significant but poorly tested change falls into that category IMHO. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SPI_finish and RegisterExprContextCallback
Thomas Hallgren [EMAIL PROTECTED] writes: AtCommitPortals (portalmem.c) iterates over the entries in the PortalHashTable. This causes a chain of calls that sometimes reach an ExprContextCallback. If that callback issues a succesfull SPI_cursor_close some problems might lay ahead. As the AcCommitPortals iteration continues, it sometimes encounter a deleted portal and elogs with an error stating trying to delete portal name that does not exist. The comment for AtCommit_Portals points out that there are risks of this sort, but I don't think you've described it properly. The SPI_cursor_close operation is probably failing not succeeding, because AtCommit_Portals will never find an already-deleted portal --- hash_seq_search() shouldn't return any already-dropped entries. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] UTF8 or Unicode
Bruce Momjian pgman@candle.pha.pa.us writes: I think we just need to _favor_ UTF8. I agree. The question is where are we favoring Unicode rather than UTF8? It's the canonical name of the encoding, both in the code and the docs. regression=# create database e encoding 'utf-8'; CREATE DATABASE regression=# \l List of databases Name| Owner | Encoding +--+--- e | postgres | UNICODE regression | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (5 rows) As soon as we decide whether the canonical name is UTF8 or UTF-8 ;-) we can fix it. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.0.X and the ARC patent
Tom Lane wrote: Still to be determined: what we lose in extra I/O from the presumably less efficient cache management; also what sort of slowdown occurs on a single-CPU machine that isn't going to get any benefit from the increased amount of lock management. But it looks promising. Yea, that was one of my questions --- the new buffer locking helps SMP, but how much does it hurt single-cpu machines? Do we need autodetection or a GUC to control SMP-beneficial locking? -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Help me recovering data
Matthew T. O'Connor wrote: Tom Lane wrote: [EMAIL PROTECTED] writes: Maybe I'm missing something, but shouldn't the prospect of data loss (even in the presense of admin ignorance) be something that should be unacceptable? Certainly within the realm normal PostgreSQL operation. Once autovacuum gets to the point where it's used by default, this particular failure mode should be a thing of the past, but in the meantime I'm not going to panic about it. Which I hope will be soon. I am ready to help you implement integrated autovacuum in 8.1. -- 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 8: explain analyze is your friend
Re: [HACKERS] 8.0.X and the ARC patent
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: Still to be determined: what we lose in extra I/O from the presumably less efficient cache management; also what sort of slowdown occurs on a single-CPU machine that isn't going to get any benefit from the increased amount of lock management. But it looks promising. Yea, that was one of my questions --- the new buffer locking helps SMP, but how much does it hurt single-cpu machines? So far I've not been able to measure any consistent difference, but you know how much I trust pgbench ;-). I hope that Mark Wong can give us some results on the OSDL setup soon. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] problems with locale settings
Hi, Starting postgres as part as of the normal boot process of my db server (up2date FC3) leads to an ``unusable'' database since it does not set the locale properly. For instnace: test=# select 'í' ~ '[[:lower:]]'; ?column? -- f (1 fila) However, starting postgres as $ LANG=es_MX pg_ctl -D ~/data start leads the desired result: test=# select 'í' ~ '[[:lower:]]'; ?column? -- t (1 fila) There's something wired here since my (fast) reading of the sources makes me thing that postgres should honor the pg_control's lc_type locale settings. BTW, I couldn't reproduce this in another machine using pg 8.0.1. Regards, Manuel. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
