Re: [BUGS] TOAST error in 7.4.2 on frequently truncated tables
Tom, > > Aha! Yes, the problem is that I dropped the last VARCHAR column, not in > > that table but in one that came after it. Any workaround to fix? > > Easiest is to add back a useless varchar column ... Can't do it, the column needed to be dropped in order to fix a problem with the data transfer.Maybe re-create the table? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] TOAST error in 7.4.2 on frequently truncated tables
Josh Berkus <[EMAIL PROTECTED]> writes: >> Easiest is to add back a useless varchar column ... > Can't do it, the column needed to be dropped in order to fix a problem with > the data transfer.Maybe re-create the table? Yeah, I think you're stuck with doing that. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] TOAST error in 7.4.2 on frequently truncated tables
Tom, > Yeah, I think you're stuck with doing that. BTW, this is a pretty nasty error, although apparently infrequent give the lack of list e-mails. Can we fix it for 7.4 series? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] TOAST error in 7.4.2 on frequently truncated tables
> BTW, this is a pretty nasty error, although apparently infrequent give the > lack of list e-mails. Can we fix it for 7.4 series? Possibly, but I'm not very excited about it --- it's certainly a corner case. I'm not sure it's worth the risk of breaking something. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] vm/swap used until exhausted
Zane <[EMAIL PROTECTED]> writes: > Different memory usage 7.4.3 vs 8.0.0beta1 > client does: > begin > bulk inserts into single table via PQexecParams (1.2 million records) > commit > under 7.4.3 memory usage is static > under 8.0.0beta1 server used increasing memory untill depletion of vm/swap I've looked into this, and the source of the problem is the new ResourceOwner mechanism we added to manage locks etc. held by subtransactions. Each of the INSERT commands takes out another lock on the target table. In prior releases this had no effect except to increment a lock count in shared memory. In CVS tip, each lock request is also recorded in a ResourceOwner object, and it's the accumulation of those that is responsible for the memory leak. To deal with this, I am thinking about creating a new hash table (local in each backend) that records locks already held, the ResourceOwner(s) they are held on behalf of, and a lock count for each one. Increasing the lock count for a lock already held would thus not need any additional memory. Another nice property is that we could have the shared-memory lock table register only one lock count per backend; increasing the local lock count for an already-obtained lock wouldn't require touching shared memory and thus not require obtaining the LockMgrLock. (This would be comparable to the existing mechanism for private vs. shared reference counts for buffers.) That might be enough of a win to buy back the extra time spent maintaining the additional hash table. This is a bigger change than I'd really like to be making in beta, but I don't see any other good solution to the memory-leak problem. Anyone have a better idea? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] BUG #1231: Probelm with transactions in stored code.
The following bug has been logged online: Bug reference: 1231 Logged by: Piotr Figiel Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.3 Operating system: Linux Suse Description:Probelm with transactions in stored code. Details: Hello I have a problem with transactions in stored code in database. This is testcase: create table test_trans ( id numeric(4,0), next_number numeric(4,0) ); insert into test_trans values (1,1); CREATE OR REPLACE FUNCTION test_tr() RETURNS numeric AS' DECLARE a numeric; b numeric; BEGIN select next_number into b from test_trans where id=1; update test_trans set next_number=next_number+1 where id=1; select next_number into a from test_trans where id=1; RETURN a ; END; ' LANGUAGE 'plpgsql' VOLATILE; What I do then. I've run two sessions. In first I've run test_trans(), then in second I've run test_trans() too. Second sessions waiting for first commit or rollback. Very good. Then I've commited first session. What I see then: First session returned value 2 - very good, but second session returned value 1 - poor, poor. Why , why, why? Second session should returned value 3. What happends. In version 8.0 Beta is the same situation. Additionl info: I've must user read commited transacion isolation. Please answer for my problem. My application based on this database but this problem show everyone that PostgreSQL is not a transactional database. Reagards Piotr Figiel ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1232: Singapore Timezone missing
The following bug has been logged online:
Bug reference: 1232
Logged by: John R Pierce
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.4.2
Operating system: Linux 2.4.9
Description:Singapore Timezone missing
Details:
The timezone designation SGT is not recognized on inserts to "timestamp with
time zone" fields.
In file src/backend/utils/adt/datetime.c SGT is missing/undefined. it
should be...
{"sgt", TZ, POS(32)}, /* Singapore Time */
(i.e UTC+0800, no daylight time)
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1232: Singapore Timezone missing
The following bug has been logged online: ... The timezone designation SGT is not recognized on inserts to "timestamp with time zone" fields. ... fyi, I am on this list, if anyone wants to tell me I'm way off base here or whatever :) something tells me I'm going to hit this problem again when our app gets deployed at our plants in thailand and china and indonesia (and, no, I don't yet know what time zone codes they use, I'm trying to figure out how to dump the linux timezone files) ok, on my redhat enterprise linux server, /usr/share/zoneinfo/Asia/Bangkok uses ICT which is defined. however, mainland china, ugh. CN Asia/Shanghai east China - Beijing, Guangdong, Shanghai, etc. CN Asia/Harbin Heilongjiang CN Asia/Chongqing central China - Gansu, Guizhou, Sichuan, Yunnan, etc. CN Asia/Urumqi Tibet & most of Xinjiang Uyghur CN Asia/Kashgarsouthwest Xinjiang Uyghur Asia/Shanghai Thu Aug 26 08:29:36 2004 CST Asia/Harbin Thu Aug 26 08:29:36 2004 CST Asia/Chongqing Thu Aug 26 08:29:36 2004 CST Asia/Urumqi Thu Aug 26 08:29:36 2004 CST Asia/KashgarThu Aug 26 08:29:36 2004 CST so all of china uses CST, which is *NOT* CST as we know it here in North America ;-/ *UGH* (btw, thats the trimmed output of... $ grep ^CN /usr/share/zoneinfo/zone.tab and $ cd /usr/share/zoneinfo; /usr/sbin/zdump Asia/* ) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] Win32 Native port more then 64 connection
Hi, postmaster.c function win32_waitpid(int *exitstatus) call to Win32 WaitForMultipleObjects ret = WaitForMultipleObjects(win32_numChildren, win32_childHNDArray, FALSE, 0); problem is 'win32_numChildren' could be more then 64 ( function supports ), problem basically arise ( kills postgres ) when you create more then 64 connections and terminate some of them sill leaving more then 64. So several iterations like that, in my case completely reproducible after I'd. Regards. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Memory leak
That's a good theory. I will definitely check it out. I appreciate you looking into this Tom. Spence -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 24, 2004 4:13 PM To: Spencer Quin Cc: [EMAIL PROTECTED]; Thomas Parry; Geoffrey Stitt Subject: Re: [BUGS] Memory leak "Spencer Quin" <[EMAIL PROTECTED]> writes: > I have found a memory leak in the libpq library for postrgesql 7.4.3. > The code sample in the attached file will produce the error. The traceback says that the leak is in libresolv, not libpq. I'm not sure it's really a leak at all --- I'd expect libresolv to do some internal caching, and this looks like it could be data that's just being held onto for possible reuse. But in any case you want to file this report with somebody else. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] BUG #1229: memory leak in backend with COPY and FOREIGN KEY
Hello! After splitting the the job into smaller pieces (e.g. 18x 1Mrow) the backend process now seems to release the memory after each subjob. Therefore the trigger queue seems to be a good candidate. Until now this queue was unknown to me. Perhaps a note in the docu of COPY FROM and in the section "13.4.2 Use COPY FROM" within "Performance Tips" would prevent other people like me doing such bad things. Many thanks for the fast help. Andreas Heiduk Stephan Szabo <[EMAIL PROTECTED]> schrieb am 24.08.04 19:25:56: > > > On Tue, 24 Aug 2004, PostgreSQL Bugs List wrote: > > > I'm trying to COPY ~18Mrows into a table which has a foreign key to another > > table. Memory and swap are exhausted and finaly the postgres.log says: > > This is very possibly the space taken up by the trigger queue (which > cannot currently spill out to disk). If you load a smaller number of rows > does the space go up and then down after the copy ends? ___ SMS schreiben mit WEB.DE FreeMail - einfach, schnell und kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] server crash in very big transaction [postgresql 8.0beta1]
--- Alvaro Herrera <[EMAIL PROTECTED]> wrote: > On Sun, Aug 22, 2004 at 09:39:07AM +0800, ?? > wrote: > > BEGIN; > > ... > > ... > > ... > > END; > > > > PANIC: invalid xlog record length 236052 > > Huh, so what kind of operations did you execute > within the transaction? > > -- > Alvaro Herrera () > Voy a acabar con todos los humanos / con los humanos > yo acabar?> voy a acabar con todos / con todos los humanos > acabar?(Bender) > > ---example 1 $ echo "BEGIN;" > backup.sql $ pg_dump -o >> backup.sql $ echo "END;" >> backup.sql ... $ psql -f backup.sql PANIC: invalid xlog record length 236052 example 2 There are 1600 tables in database 'db1', I wrote a pl/pgsql function "update_tables" like " FOR table IN SELECT relname FROM pg_class LOOP ... DROP INDEX ON ... ; ALTER TABLE DROP CONSTRAINT ...; ... CREATE INDEX xxx ON TABLE xxx; ... ALTER TABLE xxx ADD PRIMARY KEY... ALTER TABLE xxx ADD ... ... END LOOP ... " $ select update_tables(); PANIC: invalid xlog record length 236052 __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] pgsql 8 beta - Service fails... problem found
The postmaster.pid file from the pre-crash instance remained in the data directory. Deleted it and started fine. >Using pgsql 8.0.0 beta 1 installed via PGInstaller 08092004 release >on Windows 2000 SP4 build 5.00.2195, Dell Precision 360 single Pentium 4 > >After a power cutoff and reboot, the db service will not start, either automatically or manually. > >Two incidents; after the first I reinstalled before it would start again; still trying to find a better solution after the second. > >In continuous use (developing a new db) for ~two weeks without other problems. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] pgsql 8 beta - Service fails to start after system crash
Using pgsql 8.0.0 beta 1 installed via PGInstaller 08092004 release on Windows 2000 SP4 build 5.00.2195, Dell Precision 360 single Pentium 4 After a power cutoff and reboot, the db service will not start, either automatically or manually. Two incidents; after the first I reinstalled before it would start again; still trying to find a better solution after the second. In continuous use (developing a new db) for ~two weeks without other problems. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1231: Probelm with transactions in stored code.
On Wed, 25 Aug 2004, PostgreSQL Bugs List wrote: > CREATE OR REPLACE FUNCTION test_tr() RETURNS numeric AS' > DECLARE > a numeric; > b numeric; > BEGIN > select next_number into b from test_trans where id=1; > update test_trans set next_number=next_number+1 where id=1; > select next_number into a from test_trans where id=1; > > RETURN a ; > END; > ' > LANGUAGE 'plpgsql' VOLATILE; > > What I do then. > I've run two sessions. > In first I've run test_trans(), then in second I've run test_trans() too. > Second sessions waiting for first commit or rollback. Very good. Then I've > commited first session. What I see then: > First session returned value 2 - very good, but second session returned > value 1 - poor, poor. Why , why, why? Second session should returned > value 3. > What happends. In version 8.0 Beta is the same situation. Additionl info: > I've must user read commited transacion isolation. > Please answer for my problem. My application based on this database but this > problem show everyone that PostgreSQL is not a transactional database. Actually, it shows that functions have odd behavior when locking is involved (your statement would potentially be true if you could replicate this without the functions). IIRC, there are issues currently with which rows you see in such functions unless you end up using FOR UPDATE on the selects or something of that sort. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1232: Singapore Timezone missing
"John R Pierce" <[EMAIL PROTECTED]> writes: > so all of china uses CST, which is *NOT* CST as we know it here in North > America ;-/ Yeah, it's going to be difficult to do much about this stuff with the current approach of a hardwired table of zone names. There are other unresolved conflicts (IST is one I think). The Aussies got special dispensation (cf australian_timezones) mainly because we have several contributors down there who were annoyed enough to code a solution. But that approach doesn't scale. I think what we really have to do is remove the zone name entries from the hardwired table and instead load them from a config file. Then it would be easy for people to set up just the names they wanted to use. This won't happen for 8.0 but maybe in 8.1, if anyone steps up to the plate and does it. 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: [BUGS] Inconsistent pg_ctl behaviour: start vs. runservice
Steffen Macke wrote: > [PostgreSQL 8.0beta1 on Windows 2000 Professional] > > In case of a leftover postmaster.pid, pg_ctl start > tries to start anyway, > but pg_ctl runservice (as installer by the PostgreSQL > Windows installer) will not start the service, requiring a manual > removal of postmaster.pid. > > Apparently no proper error code is returned on the service startup > failure - I couldn't get the service > recovery options to delete postmaster.pid (Tried batchfile and executable). Yes, this pid file is a known problem we are researching. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Postgres 8.0/Windows 2000 Load testing
Bellan Saravanan wrote: > While performing Load testing using DOTS > http://ltp.sourceforge.net/dotshowto.php , > during the tests, an update to a specific table starts hanging. > > The test name is called BTCJ2 > http://ltp.sourceforge.net/dotshowto.php#SEC26, > > "This test case mainly uses SQL commands to execute database operations such > as insert, update, select and delete. This test case uses tables BASIC1, > BASIC2, BASIC3. For details about the tables, refer to Appendix A." > > Postgres itself was running fine and accepting new connections and able to > perform queries on the same table. Only the UPDATE was hanging. > > There was no interesting messages in the log file. I was running with debug > level 2. I can see the last line which prints the UPDATE statement. Using > debug level 3 the file grows very large and it very slow. > > Let me know if you want anymore information. Any chance of testing this on Unix? It might be some way our MVCC is interacting with the test and not a Win32-specific issue. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] BUG #1231: Probelm with transactions in stored code.
Stephan Szabo wrote: On Wed, 25 Aug 2004, PostgreSQL Bugs List wrote: CREATE OR REPLACE FUNCTION test_tr() RETURNS numeric AS' DECLARE a numeric; b numeric; BEGIN select next_number into b from test_trans where id=1; update test_trans set next_number=next_number+1 where id=1; select next_number into a from test_trans where id=1; RETURN a ; END; ' LANGUAGE 'plpgsql' VOLATILE; What I do then. I've run two sessions. In first I've run test_trans(), then in second I've run test_trans() too. Second sessions waiting for first commit or rollback. Very good. Then I've commited first session. What I see then: First session returned value 2 - very good, but second session returned value 1 - poor, poor. Why , why, why? Second session should returned value 3. What happends. In version 8.0 Beta is the same situation. Additionl info: I've must user read commited transacion isolation. Please answer for my problem. My application based on this database but this problem show everyone that PostgreSQL is not a transactional database. Actually, it shows that functions have odd behavior when locking is involved (your statement would potentially be true if you could replicate this without the functions). IIRC, there are issues currently with which rows you see in such functions unless you end up using FOR UPDATE on the selects or something of that sort. If the first select is a "FOR UPDATE" nothing change. For sure the last select in that function doesn't see the same row if you perform that same select after the function execution, and for sure doesn't see the same row that the update statement touch. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1231: Probelm with transactions in stored code.
On Thu, 26 Aug 2004, Gaetano Mendola wrote: > Stephan Szabo wrote: > > > On Wed, 25 Aug 2004, PostgreSQL Bugs List wrote: > > > > Actually, it shows that functions have odd behavior when locking is > > involved (your statement would potentially be true if you could replicate > > this without the functions). IIRC, there are issues currently with which > > rows you see in such functions unless you end up using FOR UPDATE on the > > selects or something of that sort. > > If the first select is a "FOR UPDATE" nothing change. For sure the last select in Right, I changed both to see if that made it "work" for me and it did. I didn't bother to try the only after one. > that function doesn't see the same row if you perform that same select after > the function execution, and for sure doesn't see the same row that the update > statement touch. I believe it sees the one that was valid in the snapshot as of the beginning of the function. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] server crash in very big transaction [postgresql 8.0beta1]
"ÿceÿac" "ÿbdÿaa" <[EMAIL PROTECTED]> writes: > --- Alvaro Herrera <[EMAIL PROTECTED]> wrote: >> Huh, so what kind of operations did you execute >> within the transaction? > There are 1600 tables in database 'db1', I wrote a > pl/pgsql function "update_tables" like > " > FOR table IN SELECT relname FROM pg_class > LOOP > ... > DROP INDEX ON ... ; > ALTER TABLE DROP CONSTRAINT ...; > ... > CREATE INDEX xxx ON TABLE xxx; > ... > ALTER TABLE xxx ADD PRIMARY KEY... > ALTER TABLE xxx ADD ... > ... > END LOOP Okay, so it was the number-of-deleted-files issue and not the number-of-subtransactions issue. Still says we have to allow commit records to be bigger than 64K ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1231: Probelm with transactions in stored code.
Stephan Szabo <[EMAIL PROTECTED]> writes: > I believe it sees the one that was valid in the snapshot as of the > beginning of the function. Actually, the problem is that it can see *both* that row and the updated row; it's a crapshoot which one will be returned by the SELECT INTO. The reason this can happen is that we're not doing SetQuerySnapshot between commands of a plpgsql function. There is discussion going way way back about whether we shouldn't do so (see the archives). I think the major reason why we have not done it is fear of introducing non-backwards-compatible behavior. Seems like 8.0 is exactly the right version to consider doing that in. 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: [BUGS] BUG #1232: Singapore Timezone missing
so all of china uses CST, which is *NOT* CST as we know it here in North America ;-/ Yeah, it's going to be difficult to do much about this stuff with the current approach of a hardwired table of zone names. There are other unresolved conflicts (IST is one I think). Yeah, its a maze of twisty little passages. I just fired off a suggestion to the DBAs and SQL developers on my team that everything should use RFC822 style time, $ date -R Wed, 25 Aug 2004 19:43:22 -0700 and be done with it. I haven't had a chance to look at the Java/JDBC code which triggered the SGT bug in Singapore yet. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
