[BUGS] Problem with an Identical Query
Question for anyone... I have to queries. One runs in about 2 seconds. The other takes upwards of 2 minutes. I have a temp table that is created with 2 columns. This table is joined with the larger database of call detail records. However, these 2 queries are handled very differently. The queries: First calldetail= EXPLAIN SELECT current.* FROM current JOIN anitmp ON current.destnum=anitmp.ani AND istf=true; QUERY PLAN -- Nested Loop (cost=0.00..2026113.09 rows=500908 width=108) - Seq Scan on anitmp (cost=0.00..33.62 rows=945 width=8) Filter: (istf = true) - Index Scan using i_destnum on current (cost=0.00..2137.36 rows=531 width=108) Index Cond: (current.destnum = outer.ani) (5 rows) Second calldetail= EXPLAIN SELECT current.* FROM current JOIN anitmp ON current.orignum=anitmp.ani AND istf=false; QUERY PLAN --- Hash Join (cost=35.99..3402035.53 rows=5381529 width=108) Hash Cond: (outer.orignum = inner.ani) - Seq Scan on current (cost=0.00..907191.05 rows=10170805 width=108) - Hash (cost=33.62..33.62 rows=945 width=8) - Seq Scan on anitmp (cost=0.00..33.62 rows=945 width=8) Filter: (istf = false) (6 rows) The tables: Table public.current Column |Type | Modifiers --+-+--- datetime | timestamp without time zone | orignum | bigint | destnum | bigint | billto | bigint | cost | numeric(6,4)| duration | numeric(8,1)| origcity | character(12) | destcity | character(12) | file | character varying(30) | linenum | integer | carrier | character(1)| Indexes: i_destnum btree (destnum) i_orignum btree (orignum) Table public.anitmp Column | Type | Modifiers +-+--- ani| bigint | istf | boolean | Anyone have any ideas for me? I have indexes on each of the necessary columns. Rob ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] Problem with an Identical Query
Rob Schall [EMAIL PROTECTED] writes: I have to queries. One runs in about 2 seconds. The other takes upwards of 2 minutes. I have a temp table that is created with 2 columns. This table is joined with the larger database of call detail records. However, these 2 queries are handled very differently. This is not a bug. You might have better results if you ANALYZE the temp table before trying to join it with the bigger table. Otherwise, post EXPLAIN ANALYZE (not just EXPLAIN) results in pgsql-performance, and perhaps someone can help you. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] BUG #3083: Installation to path with umlaufs fails with initdb
The following bug has been logged online: Bug reference: 3083 Logged by: Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2 Operating system: Windows 2000 Description:Installation to path with umlaufs fails with initdb Details: Install Postgres to a path like c:\a including database initialization works perfectly. (Locale = C; Encoding = UTF8, though this parameters seem not to be relevant for the problem.) Install it to a path like c:\ä the installation fails when it comes to run initdb creating the database at c:\ä\data An error messages appears Failed to run initdb: 1! It refers to a log c:\ä\tmp\initdb.log However this log file is not created. (And yes, I looked in that folder before closing the message :-)). First I suspected that the problem is in initdb itself, however, initdb running from a command shell using a database path with umlauts works. Furthermore, having a firewall running, the firewall asks me for running initdb when installing with a GOOD path. This question doesn't come up with the BAD path. Therefore I think the problems is in the MSI installer itself when trying to call initdb, i.e. initdb is never launched, therfore also no log file is ever created. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #3086: DBMirror's error (SERIAL attribute)
The following bug has been logged online:
Bug reference: 3086
Logged by: Akio Iwaasa
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.8
Operating system: Redhat EL ES 3.0
Description:DBMirror's error (SERIAL attribute)
Details:
I'm sorry for my poor English.
An error occured in DBMirror when I executed INSERT
to the table which had SERIAL attribute.
DBMirror returned the following messages.
$ psql -c INSERT INTO seqtest1 (C2) values (1) ; db1
INSERT 0 1
$ Error sending query 5968 to localhost
select setval('seqtest1_c1_seq',1,'t') at
/usr/local/pgsql818/bin/DBMirror.pl line 771.
[1] Problem on DBMirror.pl
I found a problem on sendQueryToSlaves(DBMirror.pl).
When PGRES_TUPLES_OK was returned from PostgreSQL,
DBMirror returns error message.
[2] Environment of DBMirror
Master DB : db1 (localhost)
Slave DB : db2 (localhost,slave1)
[2-1] Master DB setup script(psql)
--
-- Setup Master DB
--
\i /usr/local/pgsql/share/contrib/MirrorSetup.sql
INSERT INTO dbmirror_MirrorHost (SlaveName) VALUES('slave1') ;
--
-- Define Table
--
CREATE TABLE seqtest1 (c1 SERIAL,c2 INT) ;
--
-- AddTrigger.sql
--
CREATE TRIGGER seqtest1_Trig
AFTER INSERT OR DELETE OR UPDATE ON seqtest1
FOR EACH ROW EXECUTE PROCEDURE recordchange ();
[2-2] Slave DB setup script(psql)
--
-- Setup Slave DB
--
\i /usr/local/pgsql/share/contrib/MirrorSetup.sql
--
-- Define Table
--
CREATE TABLE seqtest1 (c1 SERIAL,c2 INT) ;
[3] Patch
I evaded the trouble by following patche.
- PATCH -
*** DBMirror.pl 2007-02-28 10:41:10.0 +0900
--- DBMirror.pl.new 2007-02-28 10:44:13.0 +0900
***
*** 696,702
if($::slaveInfo-{status} eq 'DBOpen') {
my $queryResult = $::slaveInfo-{slaveConn}-exec($sqlQuery);
!unless($queryResult-resultStatus == PGRES_COMMAND_OK) {
my $errorMessage;
$errorMessage = Error sending query $seqId to ;
$errorMessage .= $::slaveInfo-{slaveHost};
--- 696,703
if($::slaveInfo-{status} eq 'DBOpen') {
my $queryResult = $::slaveInfo-{slaveConn}-exec($sqlQuery);
!unless($queryResult-resultStatus == PGRES_COMMAND_OK ||
! $queryResult-resultStatus == PGRES_TUPLES_OK ) {
my $errorMessage;
$errorMessage = Error sending query $seqId to ;
$errorMessage .= $::slaveInfo-{slaveHost};
- PATCH -
Regards.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[BUGS] BUG #3093: Error with converting error messages between server and client encodings
The following bug has been logged online: Bug reference: 3093 Logged by: Ilya Storozhilov Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.3 Operating system: FreeBSD 6.1-RELEASE-p10 #1 Description:Error with converting error messages between server and client encodings Details: 1. Create database in UTF8 encoding 2. Connect database with psql 3. \encoding KOI8-R 4. select * from some_wrong_table; 5. finally, we can see following sad message: PANIC: ERRORDATA_STACK_SIZE exceeded ... Fix, please - it's so hard to use databases with this bug. Thanks a lot! Sencierly yours, Ilya Storozhilov http://pregrad.net/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #3087: Endiannes, of all things
The following bug has been logged online: Bug reference: 3087 Logged by: Michael Witten Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.3 Operating system: Mac OS X 10.4.8 Description:Endiannes, of all things Details: I'm in a relatively foul mood, but I'll keep the quips to myself. I wanted an easy time of it, so I copied a cluster from my PowerBook G4 to an x86 Linux box. Lo! The database cluster was initialized with PG_CONTROL_VERSION 906166272, but the server was compiled with PG_CONTROL_VERSION 822. NUXI? yes Now I have to issues all of these commands and follow all of these protocols when a simple copy should have done the trick. For shame ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #3085: Performance BUG
The following bug has been logged online: Bug reference: 3085 Logged by: Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.3 Operating system: FreeBSD 6.1 Description:Performance BUG Details: Test sequence: %psql test create table t1 (i4 int4); insert into t1 SELECT generate_series(1,99); vacuum analyze; EXPLAIN ANALYZE SELECT * FROM ( SELECT i4,(SELECT COUNT(1) FROM t1 AS subselect) AS c1 FROM t1 main_table ) AS external ORDER BY external.c1+external.c1+external.c1; QUERY PLAN -- Sort (cost=17429.03..17664.97 rows=94374 width=4) (actual time=2582.681..2861.135 rows=10 loops=1) Sort Key: (($1 + $2) + $3) InitPlan - Aggregate (cost=1620.68..1620.69 rows=1 width=0) (actual time=410.606..410.608 rows=1 loops=1) - Seq Scan on t1 subselect (cost=0.00..1384.74 rows=94374 width=0) (actual time=0.038..220.444 rows=10 loops=1) - Aggregate (cost=1620.68..1620.69 rows=1 width=0) (actual time=407.217..407.219 rows=1 loops=1) - Seq Scan on t1 subselect (cost=0.00..1384.74 rows=94374 width=0) (actual time=0.100..218.832 rows=10 loops=1) - Aggregate (cost=1620.68..1620.69 rows=1 width=0) (actual time=408.512..408.514 rows=1 loops=1) - Seq Scan on t1 subselect (cost=0.00..1384.74 rows=94374 width=0) (actual time=0.099..221.303 rows=10 loops=1) - Aggregate (cost=1620.68..1620.69 rows=1 width=0) (actual time=413.888..413.890 rows=1 loops=1) - Seq Scan on t1 subselect (cost=0.00..1384.74 rows=94374 width=0) (actual time=0.122..224.616 rows=10 loops=1) - Seq Scan on t1 main_table (cost=0.00..1856.61 rows=94374 width=4) (actual time=1640.477..2060.580 rows=10 loops=1) Total runtime: 3074.265 ms (13 rows) Time: 3077.961 ms and similar: drop table t1; create table t1 (i4 int4); insert into t1 SELECT generate_series(1,999); vacuum analyze; EXPLAIN ANALYZE SELECT i4,x1,huge.x1+huge.x1+huge.x1+huge.x1+huge.x1 FROM (SELECT i4,c1+i4 as x1 FROM ( SELECT i4,(SELECT COUNT(1) FROM t1 AS subselect WHERE i4main_table.i4)+i4 AS c1 FROM t1 main_table ) AS external) AS HUGE ORDER BY i4-huge.x1+huge.x1+huge.x1+huge.x1+huge.x1; QUERY PLAN - Sort (cost=201598.03..201600.52 rows=999 width=4) (actual time=31236.239..31238.171 rows=999 loops=1) Sort Key: (main_table.i4 - (((subplan) + main_table.i4) + main_table.i4)) + (((subplan) + main_table.i4) + main_table.i4)) +(((subplan) + main_table.i4) + main_table.i4)) + (((subplan) + main_table.i4) + main_table.i4)) + (((subplan) + main_table.i4) + main_table.i4)) - Seq Scan on t1 main_table (cost=0.00..201548.25 rows=999 width=4) (actual time=11.886..31222.853 rows=999 loops=1) SubPlan - Aggregate (cost=18.32..18.33 rows=1 width=0) (actual time=2.857..2.859 rows=1 loops=999) - Seq Scan on t1 subselect (cost=0.00..17.49 rows=333 width=0) (actual time=0.060..1.795 rows=499 loops=999) Filter: (i4 $0) - Aggregate (cost=18.32..18.33 rows=1 width=0) (actual time=2.787..2.789 rows=1 loops=999) - Seq Scan on t1 subselect (cost=0.00..17.49 rows=333 width=0) (actual time=0.061..1.775 rows=499 loops=999) Filter: (i4 $0) - Aggregate (cost=18.32..18.33 rows=1 width=0) (actual time=2.829..2.831 rows=1 loops=999) - Seq Scan on t1 subselect (cost=0.00..17.49 rows=333 width=0) (actual time=0.060..1.768 rows=499 loops=999) Filter: (i4 $0) - Aggregate (cost=18.32..18.33 rows=1 width=0) (actual time=2.780..2.782 rows=1 loops=999) - Seq Scan on t1 subselect (cost=0.00..17.49 rows=333 width=0) (actual time=0.061..1.767 rows=499 loops=999) Filter: (i4 $0) - Aggregate (cost=18.32..18.33 rows=1 width=0) (actual time=2.831..2.833 rows=1 loops=999) - Seq Scan on t1 subselect (cost=0.00..17.49 rows=333 width=0) (actual time=0.063..1.745 rows=499 loops=999) Filter: (i4 $0) - Aggregate (cost=18.32..18.33 rows=1 width=0) (actual time=2.781..2.783 rows=1 loops=999) - Seq Scan on t1 subselect (cost=0.00..17.49 rows=333 width=0) (actual time=0.062..1.764 rows=499 loops=999) Filter: (i4 $0) - Aggregate (cost=18.32..18.33 rows=1 width=0) (actual time=2.801..2.803 rows=1 loops=999) - Seq Scan on t1 subselect (cost=0.00..17.49
Re: [BUGS] BUG #3059: psql to 'postgres' shortcut
Dear Team, I checked the shortcut and was unable to find any note about close on exit or anything like that. The .bat method sounds wonderful, and I would appreciate such a change to make the product a little more responsive. Thank you for your consideration and time, Raymond Naseef P.S. I apologize if my email were not being sent properly. I will pay close attention to this going forward. - Original Message From: Bruce Momjian [EMAIL PROTECTED] To: Magnus Hagander [EMAIL PROTECTED] Cc: Phil Frost [EMAIL PROTECTED]; Raymond Naseef [EMAIL PROTECTED]; pgsql-bugs@postgresql.org Sent: Tuesday, February 27, 2007 2:08:38 PM Subject: Re: [BUGS] BUG #3059: psql to 'postgres' shortcut Magnus Hagander wrote: On Tue, Feb 27, 2007 at 09:06:17AM -0500, Phil Frost wrote: Hrm...I haven't used windows for a while now, but isn't there an option on all shortcuts to command-line programs to make the shell pause on exit? It is something like, right click on shortcut, select 'properties', uncheck 'close shell on exit'. It would seem this would solve the problem of not being able to see errors, while also not adding a pause for programs which execute psql.bat directly. Yes, that is exactly the point -it would be very annoying in all other cases, as Bruce pointed out. I guess another option would be to add a commandline option to psql to pause on error, but I don't think that's going to fly ;-) I just ran a test on Unix and found psql exits with '2' if the password fails. In fact, looking at the psql sources I see: #ifndef EXIT_SUCCESS #define EXIT_SUCCESS 0 #endif #ifndef EXIT_FAILURE #define EXIT_FAILURE 1 #endif #define EXIT_BADCONN 2 #define EXIT_USER 3 Notice EXIT_BADCONN. So, what if we call psql from a batch file, and check for a '2' exit status, and then issue a pause for only that case? -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/
[BUGS] BUG #3092: character varying and integer cannot be matched
The following bug has been logged online: Bug reference: 3092 Logged by: Andrew Rass Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.3 Operating system: FreeBSD 6.2 Description:character varying and integer cannot be matched Details: Hello the following problem has occured, SELECT T055.MESOPRIM,T051.MESOPRIM FROM T055 T055,T051 T051 WHERE T055.MESOYEAR = 1278 AND T055.MESOCOMP = '1ZAP' AND T051.MESOYEAR = 1278 AND T051.MESOCOMP = '1ZAP' AND ( T055.C002 = T051.C001 AND T055.C004 IN (2,3) AND lower(t051.c052) LIKE '%frankfurt%' )ORDER BY T055.C002 ERROR: IN types character varying and integer cannot be matched SQL Status:42804 mesoprim character varying(34); mesoyear integer; mesocomp character varying(4); c002 character varying(20); c001 character varying(20); postgresql 7.4.7 did this and now it did this problem like describe thank you ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3092: character varying and integer cannot be matched
Andrew Rass wrote: ERROR: IN types character varying and integer cannot be matched SQL Status:42804 Please provide the complete table definitions necessary to reproduce the problem. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3087: Endiannes, of all things
Michael Witten wrote: Now I have to issues all of these commands and follow all of these protocols when a simple copy should have done the trick. What is your point? Complain to your CPU manufacturer if you don't like how they lay out data in memory. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3093: Error with converting error messages between server and client encodings
Ilya Storozhilov wrote: 1. Create database in UTF8 encoding You have likely forgotten to set a matching locale. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #3089: View/Table Creation/Ownership Bug
Andrew White [EMAIL PROTECTED] writes: I came across an interesting issue regarding views and ownership that I think may be a bug in PG. I am using PG 8.2.3 on SuSE Linux. AFAICS this is behaving as intended, because you did not grant select on the underlying table to the owner of the view. Relation references in a view are supposed to be checked according to the view owner's permissions. This did not always work right pre-8.2, see http://archives.postgresql.org/pgsql-hackers/2006-04/msg01138.php regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3085: Performance BUG
[EMAIL PROTECTED] writes: Description:Performance BUG You haven't actually shown us any bug. These are not the same query and there's no reason to expect them to take the same amount of time. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3092: character varying and integer cannot be matched
Andrew Rass [EMAIL PROTECTED] writes: ERROR: IN types character varying and integer cannot be matched postgresql 7.4.7 did this and now it did this problem like describe This is not a bug; it's an intentional tightening of the behavior. You'll need to put in a cast so that the system knows whether you want an integer comparison or a textual comparison to occur. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] RHEL4 RPM packages
the PGDG .rpm's for RHEL4 have a bit of an annoyance... RHEL4 ships with postgres 7.4.x, and various RHEL4 RPMs have a dependency on libpq.so.3 ... # rpm -Uvh postgresql-8.2.3-1PGDG.i686.rpm \ postgresql-libs-8.2.3-1PGDG.i686.rpm \ postgresql-server-8.2.3-1PGDG.i686.rpm \ postgresql-contrib-8.2.3-1PGDG.i686.rpm \ compat-postgresql-libs-4-2PGDG.rhel4.i686.rpm warning: /var/tmp/rpm-xfer.N9M9NQ: V3 DSA signature: NOKEY, key ID 20579f11 error: Failed dependencies: libpq.so.3 is needed by (installed) dovecot-0.99.11-4.EL4.i386 compat-postgresql-libs-4-2PGDG.rhel4.rpm supplies libpq.so.4 and .4.1, but it does NOT include .so.3. I think it should provide a .so.3 which can coexist with the rest of the 8.2.3 stack (or, there should be a compat-postgresql-libs-3 ?) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
