Re: [GENERAL] Getting rid of duplicate tables.
Tom Lane wrote: Jared Carr [EMAIL PROTECTED] writes: Item 2 -- Length: 148 Offset: 6860 (0x1acc) Flags: USED XID: min (46034931) CMIN|XMAX: 2 CMAX|XVAC: 0 Block Id: 27 linp Index: 2 Attributes: 23 Size: 28 infomask: 0x2910 (HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) Item 43 -- Length: 148 Offset: 8044 (0x1f6c) Flags: USED XID: min (8051642) CMIN|XMAX: 46034931 CMAX|XVAC: 2 Block Id: 27 linp Index: 2 Attributes: 23 Size: 28 infomask: 0x2910 (HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) Well, there's the smoking gun ... somebody marked (27,2) as XMIN_COMMITTED, showing that they thought 46034931 was committed, while someone else marked (27,43) as XMAX_INVALID, showing that they thought 46034931 was aborted. So we have some kind of very-infrequent breakage in transaction commit-state lookup. Or a hardware problem, but I suspect we are looking at a bug. Could you check out what pg_clog has for transaction 46034931? This would be pg_clog/002B (which dates your problem to Dec 29 BTW), byte at offset 39BFC hex or 236540 decimal. I forget which way the bits run within the byte but will look it up if you can get me the value of that byte. Here is the appropriate line (line is used *very* loosely there) 00039BF0 04 10 00 00 44 00 14 44 50 00 10 01 00 40 04 40 [EMAIL PROTECTED]@ 39BFC = 0 Jared Carr ---(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
[GENERAL] postGresql Consulting ??
Hi Guys, Do you know any companies in the San Diego Area(or nearby) who can give consulting expertise. This is for getting us up and running with postGresql I would appreciate if I can get emails addresses/compnaies names who do this .. Thx Deep -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Tuesday, January 20, 2004 9:05 AM To: Jared Carr Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Getting rid of duplicate tables. Jared Carr [EMAIL PROTECTED] writes: Item 2 -- Length: 148 Offset: 6860 (0x1acc) Flags: USED XID: min (46034931) CMIN|XMAX: 2 CMAX|XVAC: 0 Block Id: 27 linp Index: 2 Attributes: 23 Size: 28 infomask: 0x2910 (HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) Item 43 -- Length: 148 Offset: 8044 (0x1f6c) Flags: USED XID: min (8051642) CMIN|XMAX: 46034931 CMAX|XVAC: 2 Block Id: 27 linp Index: 2 Attributes: 23 Size: 28 infomask: 0x2910 (HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) Well, there's the smoking gun ... somebody marked (27,2) as XMIN_COMMITTED, showing that they thought 46034931 was committed, while someone else marked (27,43) as XMAX_INVALID, showing that they thought 46034931 was aborted. So we have some kind of very-infrequent breakage in transaction commit-state lookup. Or a hardware problem, but I suspect we are looking at a bug. Could you check out what pg_clog has for transaction 46034931? This would be pg_clog/002B (which dates your problem to Dec 29 BTW), byte at offset 39BFC hex or 236540 decimal. I forget which way the bits run within the byte but will look it up if you can get me the value of that byte. I'm off to take a real close look at what was done to the pg_clog code during the 7.4 cycle ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Getting rid of duplicate tables.
Jared Carr [EMAIL PROTECTED] writes: Tom Lane wrote: Could you check out what pg_clog has for transaction 46034931? This would be pg_clog/002B (which dates your problem to Dec 29 BTW), byte at offset 39BFC hex or 236540 decimal. I forget which way the bits run within the byte but will look it up if you can get me the value of that byte. Here is the appropriate line (line is used *very* loosely there) 00039BF0 04 10 00 00 44 00 14 44 50 00 10 01 00 40 04 40 [EMAIL PROTECTED]@ 39BFC = 0 [ blinks... ] Hm, no need to check the bit direction on that one. Zero means that the transaction was never recorded as *either* committed or aborted. Which is certainly not the state that whoever marked (27,2) saw. So what we've got here apparently is active loss of transaction commit bits :-(. Can you tell whether you had any backend crashes on 29 December? It's barely possible that the transaction really did crash and this status is correct, in which case we have only a read failure to explain rather than an after-the-fact change in recorded transaction status ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Detecting database corruption
On Mon, Jan 19, 2004 at 02:45:27PM -0500, Jack Orenstein wrote: If this means, Does the database usually check for corruption? the answer is, Not as a matter of course. Do you mean that this happens in a few select situations? Or that there are configuration flags that can be used to enable such checks? There have been occasional reports of such corruption, but it seems always to come down to bad hardware. There are no flags to check for this as a part of regular operations, although you'd certainly get an error if you tried to retrieve bad data. Database corruption is a concern for two reasons. First, if it ever does occur, we have to be able to deal with the situation gracefully, even if that means nothing beyond a clean shutdown of the application. In the cases where people experience it, what usually shows up is some sort of inability to access data that is supposed to be in a place on the disk, but turns out not to be. You get error messages about missing tuples, mangled data, or a core dump. I think in such cases you probably would indeed want to shut down your application. Second, we are struggling with the IDE vs. fsync issue, that has come up on this mailing list. We definitely have to support IDE drives, and we're trying to determine how to balance performance against other concerns. If we do end up leaving IDE caching enabled, then my understanding is that corruption is a real possibility, (or have I drawn the wrong conclusion on this point?) This is a different problem. My best advice is, get a UPS with a brain. A UPS which will keep your system up for 10 minutes and which will shut it down as soon as the battery kicks in is pretty cheap. That and some regular testing and maintenance of it is likely to prevent most problematic cases you might run into here. Most fsync worries actually have to do with losing data rather than data corruption: fsync is called when a transaction commits, and if the hardware is lying about whether the bits are actually on the disk, you might lose some things you think are committed. You can apparently tolerate some data loss anyway, so in this case it's not too big a deal. A -- Andrew Sullivan | [EMAIL PROTECTED] Music is no business of mine. --Marge Simpson ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Transaction id
[EMAIL PROTECTED] wrote: Is it possible to find system change ID in SQL or stored procedures? By system change ID I understand an internal serialized number which could be used to serialize all SQL submitted into the database? Thank you in advance, Laimis Not sure what you exactly envision here. What affects the serialization of statements is more the time when their transaction commits, that the time they are executed. From the point of view of the statement, that order will be determined in the future, so how do you expect this thing to work? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Accessing template0 tables
Hi. Could someone please tell me how to access the template0 tables through PgAdmin. I'm using PgAdmin 1.4.12 and Postgres 7 something. If I log in as "postgres",and click on the template0 table, it won't expand and a big red "X" goes over the icon. How can I get around this? Thanks, Dylan Milks
Re: erServer (was: Re: [GENERAL] Postgress and MYSQL)
On Tue, Jan 20, 2004 at 09:16:45AM +0200, Devrim GUNDUZ wrote: In fact we would announce it next week, but I and Nicolai Tufar patched eRServer current CVS version last week, since it fails to configure and compile on most systems. Bug reports are welcome. I look forward to the patches. A -- Andrew Sullivan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Getting rid of duplicate tables.
Jared Carr [EMAIL PROTECTED] writes: Tom Lane wrote: Yes, it does appear that there was a backend crash/(operator stupidly kill -9 'ing possibly) on the 29th. Hmm ... could you send me that area of the log? Dec 29 16:31:54 penguin postgres[1714]: [3-1] LOG: received smart shutdown request What it looks like to me is that this shutdown never completed, probably because some client was hanging onto an open connection (smart shutdown doesn't forcibly abort active sessions). The subsequent log entries show a couple of failed attempts to start a postmaster. I would guess the sequence was roughly this: pg_ctl stop ... wait a couple minutes, get bored of waiting ... pg_ctl start ... fails with Dec 29 16:33:44 penguin postgres[5379]: [1-1] FATAL: lock file /var/lib/pgsql/data74/postmaster.pid already exists Dec 29 16:33:44 penguin postgres[5379]: [1-2] HINT: Is another postmaster (PID 1714) running in data directory /var/lib/pgsql/data74? kill -9 old postmaster pg_ctl start ... fails with Dec 29 16:34:12 penguin postgres[5395]: [1-1] FATAL: pre-existing shared memory block (key 5432001, ID 0) is still in use Dec 29 16:34:12 penguin postgres[5395]: [1-2] HINT: If you're sure there are no old server processes still running, remove the shared memory block with the command ipcrm, Dec 29 16:34:12 penguin postgres[5395]: [1-3] or just delete the file /var/lib/pgsql/data74/postmaster.pid. rm /var/lib/pgsql/data74/postmaster.pid pg_ctl start If those were the only steps taken, then the old postmaster's child backends would still have been running. At that point you're pretty well hosed because there are two independent sets of backends with separate shared memory blocks, and any subsequent transaction status updates from the old backends could overwrite pg_clog data from the new ones. So I now think it was operator error and not a software bug at all. You need to educate whoever did this about proper database shutdown procedures. As a Postgres maintainer, the only thing that troubles me about this is that the error messages from the failed postmaster start attempts could be read as having encouraged the operator to do exactly the worst possible things. I'm cc'ing this back to pgsql-general to see if anyone has any thoughts about rewording these messages. In particular it seems like the HINT for the second failure is really disastrous; it should tell you to kill off the old backends, not to zap the lockfile. regards, tom lane PS: do you know why the database was being shut down in the first place? Was there a pre-existing problem? PPS: at this point I think we've learned all we can from your database, so you can go ahead and repair the damage: delete from pg_class where ctid = '(27,43)'; should do it, at least for the one known problem. I'd encourage you to do what you can to look for other inconsistencies that may have been introduced. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SCO Extortion
On Tue, 20 Jan 2004, Gavin M. Roy wrote: I'm currently one of the targets of SCO's linux licensing extortion business plan, and am contemplating switching to one of the BSD's to avoid any potential problems. I'm curious which BSD people prefer for large scale databases and why. Any pointers as to which I should test out? for the longest time, the BSDs have been split as: FreeBSD - i386 rock solid NetBSD - work on as many platforms as possible OpenBSD - be as secure as possible There is alot of code sharing between them all though, so, IMHO, alot of it is personal preferences ... I've been using FreeBSD since '95, and other then having a habit of finding (and, usually pushing) its limits, I've been most happy with it ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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: [GENERAL] SCO Extortion
--On Tuesday, January 20, 2004 14:06:35 -0800 Gavin M. Roy [EMAIL PROTECTED] wrote: I'm currently one of the targets of SCO's linux licensing extortion business plan, and am contemplating switching to one of the BSD's to avoid any potential problems. I'm curious which BSD people prefer for large scale databases and why. Any pointers as to which I should test out? I like FreeBSD. It's PORTS system is WONDERFUL! LER Thanks, Gavin ---(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 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [GENERAL] SCO Extortion
Thanks for the feedback thus far. I should also mention I use freebsd for other stuff, but I am mainly asking in peoples experience, which is the best for PostgreSQL to live on specifically. In terms of a nice smp high end scsi system. Sorry for the lack of specifics on that before. Gavin Marc G. Fournier wrote: On Tue, 20 Jan 2004, Gavin M. Roy wrote: I'm currently one of the targets of SCO's linux licensing extortion business plan, and am contemplating switching to one of the BSD's to avoid any potential problems. I'm curious which BSD people prefer for large scale databases and why. Any pointers as to which I should test out? for the longest time, the BSDs have been split as: FreeBSD - i386 rock solid NetBSD - work on as many platforms as possible OpenBSD - be as secure as possible There is alot of code sharing between them all though, so, IMHO, alot of it is personal preferences ... I've been using FreeBSD since '95, and other then having a habit of finding (and, usually pushing) its limits, I've been most happy with it ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SCO Extortion
On Wed, 2004-01-21 at 11:55, Marc G. Fournier wrote: On Tue, 20 Jan 2004, Gavin M. Roy wrote: I'm currently one of the targets of SCO's linux licensing extortion business plan, and am contemplating switching to one of the BSD's to avoid any potential problems. I'm curious which BSD people prefer for large scale databases and why. Any pointers as to which I should test out? for the longest time, the BSDs have been split as: FreeBSD - i386 rock solid NetBSD - work on as many platforms as possible OpenBSD - be as secure as possible There is alot of code sharing between them all though, so, IMHO, alot of it is personal preferences ... I've been using FreeBSD since '95, and other then having a habit of finding (and, usually pushing) its limits, I've been most happy with it ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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 Of course SCO is planning to sue the BSD users, too, so it's not really a solution. Just ignore them :) Stephen signature.asc Description: This is a digitally signed message part
Re: [GENERAL] SCO Extortion
On Wed, 21 Jan 2004, Stephen Robert Norris wrote: Of course SCO is planning to sue the BSD users, too, so it's not really a solution. We figure that SCO will either be bought out, or go bankrupt, before we have to worry about them :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SCO Extortion
On Tue, 20 Jan 2004, Gavin M. Roy wrote: Thanks for the feedback thus far. I should also mention I use freebsd for other stuff, but I am mainly asking in peoples experience, which is the best for PostgreSQL to live on specifically. In terms of a nice smp high end scsi system. Sorry for the lack of specifics on that before. Again, its pretty much a personal opinion ... we use FreeBSD for all our PgSQL work, and have several clients that are doing so also, and have been most happy with it .. Again, you have to consider that with the code-sharing that happens, drivers and such tend to be very similar, if not identical ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] SCO Extortion
On Tue, Jan 20, 2004 at 06:16:46PM -0800, Gavin M. Roy wrote: Thanks for the feedback thus far. I should also mention I use freebsd for other stuff, but I am mainly asking in peoples experience, which is the best for PostgreSQL to live on specifically. In terms of a nice smp high end scsi system. Sorry for the lack of specifics on that before. i've built several billing systems for long distance companies using pgsql on FreeBSD since '97. i've found them to be quite stable and robust, including uniprocessor and SMP, using raw big disks, hardware RAID, and also the incumbent vinum software RAID. i've found upgrading the core OS, as well as upgrading pgsql and other apps, to be fairly clean and troublefree. Gavin Marc G. Fournier wrote: On Tue, 20 Jan 2004, Gavin M. Roy wrote: I'm currently one of the targets of SCO's linux licensing extortion business plan, and am contemplating switching to one of the BSD's to avoid any potential problems. I'm curious which BSD people prefer for large scale databases and why. Any pointers as to which I should test out? for the longest time, the BSDs have been split as: FreeBSD - i386 rock solid NetBSD - work on as many platforms as possible OpenBSD - be as secure as possible There is alot of code sharing between them all though, so, IMHO, alot of it is personal preferences ... I've been using FreeBSD since '95, and other then having a habit of finding (and, usually pushing) its limits, I've been most happy with it ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- [ Jim Mercer[EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying.] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SQL Exception Relation xxx does not exist
Kris, thanks for the reply. I dont actually use temp tables in the function (not that I know of) but I did truncated and reloaded a few tables incl. recreating indices the previous day, however the line no. indicated in the serverlog does not point to these sql calls. I am now re-starting tomcat every night which is not a bad thing anyway but still am wondering what the real reason could be. Alex Kris Jurka wrote: On Tue, 20 Jan 2004, Alex wrote: Hi, I am getting the following error when running an update from a JAVA program using a Tomcat Connection Pool. SQLException: Error Relation 215106760 does not exist In the server log I see additional info Error occured while executing PL/pgSQL function funcName line 105 at select into variables This error is likely the result of using a temp table without EXECUTE or having one of your tables dropped (recreating it won't help). plpgsql caches query plans, but doesn't track the plans dependencies, so if any of the underlying objects change you can get this error. 2. Could this be a problem with the connection pool of tomcat or the postgres JDBC driver ? The plans are cached once per backend, the connection pool keeps that same backend open forever which means you can never safely change your schema without restarting the pool. 3. how do i find out the name of the actual object referred by 215106760 in the error message? SELECT relname FROM pg_class WHERE oid = 215106760; Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
