Re: [BUGS] BUG #5735: pg_upgrade thinks that it did not start the old server
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On 10/30/2010 7:33 PM, Dave Page wrote: upgrade from a 32bit 8.3 server to a 64 bit 9.0 server, which isn't going to work without a dump/restore. With pg_upgrade, the two builds need to be from the same platform, same word size, and have the same configuration for certain settings like integer_datetimes. Can anyone suggest a way pg_upgrade could detect an upgrade from a 32-bit to 64-bit cpu and throw an error? Surely it does that already, as a result of comparing pg_control contents. Surely it does, but I didn't understand how the user able to run pg_upgrade? I see now that he failed before we completed our checks so he would have gotten an error later if he could have started his server: http://archives.postgresql.org/pgsql-bugs/2010-10/msg00282.php Thanks. Not sure why he was unable to start the old server, but we decided he couldn't use pg_upgrade anyway in his setup. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5735: pg_upgrade thinks that it did not start the old server
On 11/10/2010 05:32 AM, Tom Lane wrote: Bruce Momjianbr...@momjian.us writes: On 10/30/2010 7:33 PM, Dave Page wrote: upgrade from a 32bit 8.3 server to a 64 bit 9.0 server, which isn't going to work without a dump/restore. With pg_upgrade, the two builds need to be from the same platform, same word size, and have the same configuration for certain settings like integer_datetimes. Can anyone suggest a way pg_upgrade could detect an upgrade from a 32-bit to 64-bit cpu and throw an error? Surely it does that already, as a result of comparing pg_control contents. The HTML manual might need an update or a small clarification too. Currently, it does not seem that the manual explicitly states that ``pg_upgrade is not applicable in upgrading 32bit systems to 64bit ones''. A good place to write such a sentence would be at the beginning of [1], at the intro of F.32. pg_upgrade. Maybe the documentation already implicitly states that in F.32.4. Limitations in Migrating from PostgreSQL 8.3 section of [1] by this description: ``For Windows users, note that due to different integer datetimes settings used by the one-click installer and the MSI installer, it is only possible to upgrade from version 8.3 of the one-click distribution to version 8.4 or later of the one-click distribution. It is not possible to upgrade from the MSI installer to the one-click installer.'' Unfortunately, I could not understand in full detail the above. Thanks, arturas [1] http://www.postgresql.org/docs/9.0/static/pgupgrade.html -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5745: geometry bug?
The following bug has been logged online: Bug reference: 5745 Logged by: Jin Email address: jind...@gmail.com PostgreSQL version: 8.4.5 Operating system: windows xp pro sp3 Description:geometry bug? Details: The distance of the horizontal lseg and the point on that is inaccurate. select point(1.0,1.0) - lseg'(0.0,0.0),(2.0,0.0)'; ↓ result 1.4142135623731 must be 1.0 P.S. I'm sorry about if it was already reported. Because I can't read English well. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Why dose the planner select one bad scan plan.
I use the postgresql in default configuration and use inheritance way to create table. My postgresql version is: SELECT version(); version PostgreSQL 9.0.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5, 32-bit (1 row) Reboot the computer to avoid memory cache. And then get the following explain: EXPLAIN ANALYZE SELECT authdomain,authuser,count(*),sum(SIZE) FROM tbltrafficlog WHERE (PROTOCOL in ('HTTP','HTTPS','FTP')) and (TIME = '2010-10-01 00:00:00' AND TIME '2010-11-01 00:00:00') GROUP BY authdomain,authuser order by count(*) DESC LIMIT 10 OFFSET 0; QUERY PLAN Limit (cost=600830.83..600830.86 rows=10 width=19) (actual time=225034.470..225034.483 rows=10 loops=1) - Sort (cost=600830.83..600833.25 rows=968 width=19) (actual time=225034.469..225034.473 rows=10 loops=1) Sort Key: (count(*)) Sort Method: top-N heapsort Memory: 17kB - HashAggregate (cost=600795.40..600809.92 rows=968 width=19) (actual time=225018.666..225019.522 rows=904 loops=1) - Append (cost=0.00..535281.08 rows=6551432 width=19) (actual time=4734.441..205514.878 rows=7776000 loops=1) - Seq Scan on tbltrafficlog (cost=0.00..11.50 rows=1 width=298) (actual time=0.001..0.001 rows=0 loops=1) Filter: ((time = '2010-10-01 00:00:00'::timestamp without time zone) AND (time '2010-11-01 00:00:00'::timestamp without time zone) AND ((protocol)::text = ANY ('{HTTP,HTTPS,FTP}'::text[]))) - Bitmap Heap Scan on tbltrafficlog_20101001 tbltrafficlog (cost=4471.33..17819.25 rows=218129 width=19) (actual time=4734.437..6096.206 rows=259200 loops=1) Recheck Cond: ((protocol)::text = ANY ('{HTTP,HTTPS,FTP}'::text[])) Filter: ((time = '2010-10-01 00:00:00'::timestamp without time zone) AND (time '2010-11-01 00:00:00'::timestamp without time zone)) - Bitmap Index Scan on tbltrafficlog_20101001_protocol_idx (cost=0.00..4416.80 rows=218129 width=0) (actual time=4731.860..4731.860 rows=259200 loops=1) Index Cond: ((protocol)::text = ANY ('{HTTP,HTTPS,FTP}'::text[])) … - Bitmap Heap Scan on tbltrafficlog_20101030 tbltrafficlog (cost=4472.75..17824.12 rows=218313 width=19) (actual time=4685.536..6090.222 rows=259200 loops=1) Recheck Cond: ((protocol)::text = ANY ('{HTTP,HTTPS,FTP}'::text[])) Filter: ((time = '2010-10-01 00:00:00'::timestamp without time zone) AND (time '2010-11-01 00:00:00'::timestamp without time zone)) - Bitmap Index Scan on tbltrafficlog_20101030_protocol_idx (cost=0.00..4418.17 rows=218313 width=0) (actual time=4677.147..4677.147 rows=259200 loops=1) Index Cond: ((protocol)::text = ANY ('{HTTP,HTTPS,FTP}'::text[])) Total runtime: 225044.255 ms Reboot the computer again. And then I close bitmap scan manually and get the following explain: SET SET enable_bitmapscan TO off; EXPLAIN ANALYZE SELECT authdomain,authuser,count(*),sum(SIZE) FROM tbltrafficlog WHERE (PROTOCOL in ('HTTP','HTTPS','FTP')) and (TIME = '2010-10-01 00:00:00' AND TIME '2010-11-01 00:00:00') GROUP BY authdomain,authuser order by count(*) DESC LIMIT 10 OFFSET 0; QUERY PLAN - Limit (cost=634901.26..634901.28 rows=10 width=19) (actual time=83805.465..83805.477 rows=10 loops=1) - Sort (cost=634901.26..634903.68 rows=968 width=19) (actual time=83805.463..83805.467 rows=10 loops=1) Sort Key: (count(*)) Sort Method: top-N heapsort Memory: 17kB - HashAggregate (cost=634865.82..634880.34 rows=968 width=19) (actual time=83789.686..83790.540 rows=904 loops=1) - Append (cost=0.00..569351.50 rows=6551432 width=19) (actual time=0.010..64393.284 rows=7776000 loops=1) - Seq Scan on tbltrafficlog (cost=0.00..11.50 rows=1 width=298) (actual time=0.001..0.001 rows=0 loops=1) Filter: ((time = '2010-10-01 00:00:00'::timestamp without time zone) AND (time '2010-11-01 00:00:00'::timestamp without time zone) AND ((protocol)::text = ANY ('{HTTP,HTTPS,FTP}'::text[]))) - Seq Scan on tbltrafficlog_20101001 tbltrafficlog (cost=0.00..18978.00 rows=218129
[BUGS] BUG #5746: /etc/init.d/postgresql-9.0 status returns the wrong value
The following bug has been logged online: Bug reference: 5746 Logged by: Jeff Mace Email address: jeff.m...@continuent.com PostgreSQL version: 9.0.1 Operating system: CentOS Description:/etc/init.d/postgresql-9.0 status returns the wrong value Details: The following code change is required in the script to check for the proper file. status -p /var/run/postmaster-9.0.${PGPORT}pid should be status -p /var/run/postmaster-9.0.${PGPORT}.pid A '.' is needed after ${PGPORT}. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5745: geometry bug?
On 10/11/10 10:08, Jin wrote: The following bug has been logged online: Bug reference: 5745 Logged by: Jin Email address: jind...@gmail.com PostgreSQL version: 8.4.5 Operating system: windows xp pro sp3 Description:geometry bug? Details: The distance of the horizontal lseg and the point on that is inaccurate. select point(1.0,1.0)- lseg'(0.0,0.0),(2.0,0.0)'; ↓ result 1.4142135623731 must be 1.0 P.S. I'm sorry about if it was already reported. Because I can't read English well. From my digging it appears that this is returning the distance between the first point in the line and the individual point. To get 1.0 you would be looking for the the distance between the midpoint of the line and the individual point which can be achieved with: SELECT POINT(1.0,1.0) - POINT(LSEG'(0.0,0.0),(2.0,0.0)'); Digging through the documentation I can't find anything that says which point should be used in the line for distance comparisons. So I would rephrase this bug as: The distance of the horizontal lseg and the point is calculated against the first point in the line. Should this be calculated against the midpoint of the line instead? Regards, -- Mike Fowler Registered Linux user: 379787 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5747: TimeStamps too far into the future are invalid
The following bug has been logged online: Bug reference: 5747 Logged by: Kurt Stam Email address: ks...@apache.org PostgreSQL version: 8.3 Operating system: OSX Description:TimeStamps too far into the future are invalid Details: https://issues.apache.org/jira/browse/JUDDI-374 When the coverage period goes out too far postgres has issues. The coverage periods are specified in the uddi-tck-base module in the directory src/main/resources/uddi_data/subscription; the files subscriptionresults3.xml and subscriptionresults4.xml: Changing the endPoint from 2100 to 2030. This is clearly a bug in postgres or the postgres driver. On saving no error is thrown, however the endpoint field is set to 'invalid' which is an issue when the date is parsed back into a timedate. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5745: geometry bug?
Mike Fowler m...@mlfowler.com writes: On 10/11/10 10:08, Jin wrote: The distance of the horizontal lseg and the point on that is inaccurate. From my digging it appears that this is returning the distance between the first point in the line and the individual point. I didn't look into the code yet, but that would match the result. The distance of the horizontal lseg and the point is calculated against the first point in the line. Should this be calculated against the midpoint of the line instead? The standard geometrical notion of distance between a point and a line is that you drop the perpendicular from the point to the line, and the length of that perpendicular is the distance. Now, in the case of a line segment, the perpendicular might not intersect the segment: x | | | + o-o in which case I think the distance from the point to the segment's nearer endpoint is a reasonable definition. In some quick testing it seems to work that way for every case except a perfectly horizontal line segment. So I'm thinking somebody fat-fingered the corner case where the perpendicular would have infinite slope, and it is falling through to the take the nearer endpoint case when it shouldn't. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5747: TimeStamps too far into the future are invalid
Kurt Stam ks...@apache.org writes: https://issues.apache.org/jira/browse/JUDDI-374 There is no evidence whatsoever on that page to demonstrate that there's any such postgresql bug. What's more, simple testing suggests that PG is perfectly happy with timestamps of 2100 or even further out. If you'd like us to believe we have something to fix, please exhibit some SQL commands that deliver an incorrect result. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5745: geometry bug?
I wrote: So I'm thinking somebody fat-fingered the corner case where the perpendicular would have infinite slope, and it is falling through to the take the nearer endpoint case when it shouldn't. Actually it's more basic than that: the line_construct_pm() function is just completely bogus for the case of infinite slope. Will fix. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5747: TimeStamps too far into the future are invalid
t...@sss.pgh.pa.us (Tom Lane) writes: https://issues.apache.org/jira/browse/JUDDI-374 There's an issue that JDBC doesn't cope very well with 'Infinity' values, which is more or less the opposite of what's reported here. I have been tending to put don't allow +/-Infinity constraints onto timestamps to avoid that particular impedance mismatch. Just ran a little test of this with... PostgreSQL 8.3.3, last year's JDBC, and had no difficulties pushing in the date 2100-01-01 00:00:00. That suggests the problem to be elsewhere. (As, I expect, you would expect!) -- (format nil ~...@~s cbbrowne gmail.com) Rules of the Evil Overlord #194. I will make several ludicrously erroneous maps to secret passages in my fortress and hire travellers to entrust them to aged hermits. http://www.eviloverlord.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Recovery bug
On Tue, 2010-10-26 at 10:48 +0300, Heikki Linnakangas wrote: The reason I didn't use ReadRecord is because it sets a global variable to point to the next location in the log, so that subsequent calls can just pass NULL for the location. True. XLogPageRead is new in 9.0, however. We'll have to use ReadRecord or invent something new for back-branches anyway. It looks like the patch leaves the global variable pointing just after the redo location rather than the checkpoint. I haven't tested your patch yet, but it looks like some of the following code depends on ReadRecord(NULL,...) fetching the record right after the checkpoint record; so I think something else is required if you want to use ReadRecord. Hmm, the next call to ReadRecord is this: /* * Find the first record that logically follows the checkpoint --- it * might physically precede it, though. */ if (XLByteLT(checkPoint.redo, RecPtr)) { /* back up to find the record */ record = ReadRecord((checkPoint.redo), PANIC, false); } else { /* just have to read next record after CheckPoint */ record = ReadRecord(NULL, LOG, false); } In the first case, the location is given explicitly. In the second case, the redo pointer equals the checkpoint record, so the current position is correct even with the patch. It makes me slightly nervous, though. It's correct today, but if someone adds code between the backup_label check and this that assumes that the current position is the checkpoint record, it'll fail. Then again, any new ReadRecord call in such added code would also break the assumption in the above block that the current position is the checkpoint record. In the case that the redo pointer is the same as the checkpoint record, we don't need to re-fetch the checkpoint record. I've added a test for that in the attached patch. There is a problem with this patch. ReadRecord() not only modifies global variables, it also modifies the location pointed to by record, which is later used to set wasShutdown. How about if we only set wasShutdown if there is no backup_label (because the checkpoint for pg_start_backup() will never be a shutdown checkpoint anyway)? Trivial patch attached. It's not easy to reproduce a real problem, but the easiest way that I found is by creating a commit record at the REDO location. Put a sleep() in CheckPointGuts() to give you time before the checkpoint completes. Session1: CREATE TABLE foo(i int); BEGIN; INSERT INTO foo VALUES(1); Session2: SELECT pg_start_backup('foo'); Session1 (before checkpoint for pg_start_backup() completes): COMMIT; Then, perform backup, stop backup, shutdown the server. Then try to restore the backup, and you'll get a PANIC. This seems like a pretty serious issue to me (backups could appear unrecoverable), so please consider this before the next patch-level release so that the bad fix doesn't go out to the world. Also, you might want to double-check that there aren't other side effects that we're still missing. Regards, Jeff Davis *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *** *** 5714,5720 StartupXLOG(void) { XLogCtlInsert *Insert; CheckPoint checkPoint; ! bool wasShutdown; bool reachedStopPoint = false; bool haveBackupLabel = false; XLogRecPtr RecPtr, --- 5714,5720 { XLogCtlInsert *Insert; CheckPoint checkPoint; ! bool wasShutdown = false; bool reachedStopPoint = false; bool haveBackupLabel = false; XLogRecPtr RecPtr, *** *** 5932,5942 StartupXLOG(void) (errmsg(could not locate a valid checkpoint record))); } memcpy(checkPoint, XLogRecGetData(record), sizeof(CheckPoint)); } LastRec = RecPtr = checkPointLoc; - wasShutdown = (record-xl_info == XLOG_CHECKPOINT_SHUTDOWN); - ereport(DEBUG1, (errmsg(redo record is at %X/%X; shutdown %s, checkPoint.redo.xlogid, checkPoint.redo.xrecoff, --- 5932,5942 (errmsg(could not locate a valid checkpoint record))); } memcpy(checkPoint, XLogRecGetData(record), sizeof(CheckPoint)); + + wasShutdown = (record-xl_info == XLOG_CHECKPOINT_SHUTDOWN); } LastRec = RecPtr = checkPointLoc; ereport(DEBUG1, (errmsg(redo record is at %X/%X; shutdown %s, checkPoint.redo.xlogid, checkPoint.redo.xrecoff, -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5746: /etc/init.d/postgresql-9.0 status returns the wrong value
On Wed, 2010-11-10 at 15:13 +, Jeff Mace wrote: The following code change is required in the script to check for the proper file. status -p /var/run/postmaster-9.0.${PGPORT}pid should be status -p /var/run/postmaster-9.0.${PGPORT}.pid A '.' is needed after ${PGPORT}. Thanks for the report. I fixed it in SVN. I'll push updated packages soon. Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part