[BUGS] BUG #3669: cann't use UTF8 as server side encoding
The following bug has been logged online: Bug reference: 3669 Logged by: huanghongdong Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3b1 Operating system: windowsXP Description:cann't use UTF8 as server side encoding Details: use the installer to install 8.3b1,but I can't select UTF8 as server side encoding. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3669: cann't use UTF8 as server side encoding
Hi. Yes, you look at the same thing as this. http://winpg.jp/~saito/pginstaller/pginstaller_8.3.beta1-error1.png We will correct it after discussion. Please expect the following version. Thanks! Regrad, Hiroshi Saito - Original Message - From: "huanghongdong" <[EMAIL PROTECTED]> To: Sent: Thursday, October 11, 2007 9:31 AM Subject: [BUGS] BUG #3669: cann't use UTF8 as server side encoding The following bug has been logged online: Bug reference: 3669 Logged by: huanghongdong Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3b1 Operating system: windowsXP Description:cann't use UTF8 as server side encoding Details: use the installer to install 8.3b1,but I can't select UTF8 as server side encoding. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #3670: Exception thrown when attempting to debug
The following bug has been logged online: Bug reference: 3670 Logged by: Ognjen Babic Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3beta-1 Operating system: Windows 2003 Server Description:Exception thrown when attempting to debug Details: Exception is thrown when attempting to debug a function or a trigger. Unhandled exception at 0x in pgAdmin3.exe: 0xC005: Access violation reading location 0x. ---(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 #3667: Job scheduling with Greenplum fails
The original query: EXPLAIN ANALYZE SELECT *, (SELECT jlgstatus FROM pgagent.pga_joblog jl WHERE jl.jlgjobid = j.jobid ORDER BY jlgid DESC LIMIT 1) AS joblastresult FROM pgagent.pga_job j JOIN pgagent.pga_jobclass cl ON cl.jclid=jobjclid LEFT OUTER JOIN pgagent.pga_jobagent ag ON ag.jagpid=jobagentid -- + restriction + ORDER BY jobname; "Sort (cost=5359.18..5360.33 rows=460 width=221) (actual time=0.295..0.300 rows=2 loops=1)" " Sort Key: j.jobname" " -> Hash Join (cost=69.50..5338.84 rows=460 width=221) (actual time=0.189..0.249 rows=2 loops=1)" "Hash Cond: (j.jobjclid = cl.jclid)" "-> Hash Left Join (cost=33.40..54.33 rows=460 width=185) (actual time=0.068..0.084 rows=2 loops=1)" " Hash Cond: (j.jobagentid = ag.jagpid)" " -> Seq Scan on pga_job j (cost=0.00..14.60 rows=460 width=141) (actual time=0.025..0.030 rows=2 loops=1)" " -> Hash (cost=20.40..20.40 rows=1040 width=44) (actual time=0.019..0.019 rows=1 loops=1)" "-> Seq Scan on pga_jobagent ag (cost=0.00..20.40 rows=1040 width=44) (actual time=0.005..0.008 rows=1 loops=1)" "-> Hash (cost=21.60..21.60 rows=1160 width=36) (actual time=0.050..0.050 rows=5 loops=1)" " -> Seq Scan on pga_jobclass cl (cost=0.00..21.60 rows=1160 width=36) (actual time=0.011..0.022 rows=5 loops=1)" "SubPlan" " -> Limit (cost=0.00..11.40 rows=1 width=9) (actual time=0.023..0.025 rows=1 loops=2)" "-> Index Scan Backward using pga_joblog_pkey on pga_joblog jl (cost=0.00..68.38 rows=6 width=9) (actual time=0.014..0.014 rows=1 loops=2)" " Filter: (jlgjobid = $0)" "Total runtime: 0.519 ms" My revised query: EXPLAIN ANALYZE select j.*, cl.*, ag.*, sub3.jlgstatus from pgagent.pga_job j join pgagent.pga_jobclass cl on cl.jclid=jobjclid left outer join pgagent.pga_jobagent ag on ag.jagpid=jobagentid join (select j2.jlgstatus, sub.jlgjobid from pgagent.pga_joblog j2 join (select jl.jlgjobid, max(jl.jlgid) as max_jlgid from pgagent.pga_joblog jl group by jl.jlgjobid) sub on sub.jlgjobid = j2.jlgjobid and sub.max_jlgid = j2.jlgid) sub3 on sub3.jlgjobid = j.jobid -- + restriction + order by jobname; "Sort (cost=81.07..81.07 rows=1 width=226) (actual time=0.780..0.784 rows=2 loops=1)" " Sort Key: j.jobname" " -> Nested Loop (cost=66.00..81.06 rows=1 width=226) (actual time=0.633..0.736 rows=2 loops=1)" "-> Nested Loop Left Join (cost=66.00..80.67 rows=1 width=190) (actual time=0.613..0.684 rows=2 loops=1)" " -> Nested Loop (cost=66.00..80.29 rows=1 width=146) (actual time=0.598..0.651 rows=2 loops=1)" "-> Hash Join (cost=66.00..72.01 rows=1 width=13) (actual time=0.566..0.583 rows=2 loops=1)" " Hash Cond: ((sub.jlgjobid = j2.jlgjobid) AND (sub.max_jlgid = j2.jlgid))" " -> HashAggregate (cost=27.25..29.75 rows=200 width=8) (actual time=0.252..0.257 rows=2 loops=1)" "-> Seq Scan on pga_joblog jl (cost=0.00..21.50 rows=1150 width=8) (actual time=0.012..0.111 rows=44 loops=1)" " -> Hash (cost=21.50..21.50 rows=1150 width=13) (actual time=0.283..0.283 rows=44 loops=1)" "-> Seq Scan on pga_joblog j2 (cost=0.00..21.50 rows=1150 width=13) (actual time=0.023..0.145 rows=44 loops=1)" "-> Index Scan using pga_job_pkey on pga_job j (cost=0.00..8.27 rows=1 width=141) (actual time=0.014..0.017 rows=1 loops=2)" " Index Cond: (sub.jlgjobid = j.jobid)" " -> Index Scan using pga_jobagent_pkey on pga_jobagent ag (cost=0.00..0.37 rows=1 width=44) (actual time=0.002..0.002 rows=0 loops=2)" "Index Cond: (ag.jagpid = j.jobagentid)" "-> Index Scan using pga_jobclass_pkey on pga_jobclass cl (cost=0.00..0.37 rows=1 width=36) (actual time=0.006..0.009 rows=1 loops=2)" " Index Cond: (cl.jclid = j.jobjclid)" "Total runtime: 1.096 ms" My table only has 2 records in it so it might be different when we have several hundred jobs. The cost is significantly lower but the total runtime is higher. This is on a PostgreSQL database installed on my desktop. It has nothing to do with Greenplum. I can't even run an explain plan on GP with that first query because it fails. Another solution would be to call a function in the database rather than imbedding the SQL in C++. If you look at pgagent.sql, there are a few functions created to support jobs. Maybe this could be another function call so it could easily be modified to support Greenplum and make it easier for users to twea
Re: [BUGS] BUG #3667: Job scheduling with Greenplum fails
On Thu, Oct 11, 2007 at 07:31:44AM -0500, Roberts, Jon wrote: > The cost is significantly lower but the total runtime is higher. Um, so you want developers to change the thing so that it performs more slowly, but has a prettier estimate of how much work it's going to do? That seems like a poor optimisation to me. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(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
[BUGS] BUG #3671: if locale=french, service idles at 100% CPU
The following bug has been logged online: Bug reference: 3671 Logged by: Nathanael TERRIEN Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4+ Operating system: Windows XP SP2 Description:if locale=french, service idles at 100% CPU Details: Tested on 3 different laptops (and only reproduced on laptops, not desktops) : - fresh or upgraded install of 8.2.4 or 8.2.5 on Windows XP SP2 French (in an Active Directory Win 2003 domain) - if, during installation (with the installer), I choose "locale=French" then, when the pgsql service starts, it idles at 100% CPU (or 50% if the PC has a 2 cores CPU) - if I choose "locale=C", it idles à 0% (normal behaviour) I tried distinstalling and re installing (with intermediate system cleaning) several times, it always behaves the same. It did not occured with 8.0 or 8.1 I did not tried with 8.2.1 to 8.2.3 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3667: Job scheduling with Greenplum fails
Like I said in the email, I think the best solution is to put the code in a function. However, after I inserted a total of 25 jobs, the difference is more noticeable and my sql is better in terms of cost and total time. vacuum analyze pgagent.pga_job; vacuum analyze pgagent.pga_jobclass; vacuum analyze pgagent.pga_jobagent; Original SQL: explain analyze SELECT *, (SELECT jlgstatus FROM pgagent.pga_joblog jl WHERE jl.jlgjobid = j.jobid ORDER BY jlgid DESC LIMIT 1) AS joblastresult FROM pgagent.pga_job j JOIN pgagent.pga_jobclass cl ON cl.jclid=jobjclid LEFT OUTER JOIN pgagent.pga_jobagent ag ON ag.jagpid=jobagentid -- + restriction + ORDER BY jobname "Sort (cost=291.31..291.37 rows=25 width=127) (actual time=2.125..2.181 rows=25 loops=1)" " Sort Key: j.jobname" " -> Hash Join (cost=2.13..290.73 rows=25 width=127) (actual time=0.204..1.823 rows=25 loops=1)" "Hash Cond: (j.jobjclid = cl.jclid)" "-> Hash Left Join (cost=1.02..4.38 rows=25 width=105) (actual time=0.061..0.264 rows=25 loops=1)" " Hash Cond: (j.jobagentid = ag.jagpid)" " -> Seq Scan on pga_job j (cost=0.00..3.25 rows=25 width=63) (actual time=0.009..0.074 rows=25 loops=1)" " -> Hash (cost=1.01..1.01 rows=1 width=42) (actual time=0.021..0.021 rows=1 loops=1)" "-> Seq Scan on pga_jobagent ag (cost=0.00..1.01 rows=1 width=42) (actual time=0.005..0.008 rows=1 loops=1)" "-> Hash (cost=1.05..1.05 rows=5 width=22) (actual time=0.050..0.050 rows=5 loops=1)" " -> Seq Scan on pga_jobclass cl (cost=0.00..1.05 rows=5 width=22) (actual time=0.005..0.026 rows=5 loops=1)" "SubPlan" " -> Limit (cost=0.00..11.40 rows=1 width=9) (actual time=0.045..0.045 rows=0 loops=25)" "-> Index Scan Backward using pga_joblog_pkey on pga_joblog jl (cost=0.00..68.38 rows=6 width=9) (actual time=0.036..0.036 rows=0 loops=25)" " Filter: (jlgjobid = $0)" "Total runtime: 2.436 ms" My SQL: explain analyze select j.*, cl.*, ag.*, sub3.jlgstatus from pgagent.pga_job j join pgagent.pga_jobclass cl on cl.jclid=jobjclid left outer join pgagent.pga_jobagent ag on ag.jagpid=jobagentid join (select j2.jlgstatus, sub.jlgjobid from pgagent.pga_joblog j2 join (select jl.jlgjobid, max(jl.jlgid) as max_jlgid from pgagent.pga_joblog jl group by jl.jlgjobid) sub on sub.jlgjobid = j2.jlgjobid and sub.max_jlgid = j2.jlgid) sub3 on sub3.jlgjobid = j.jobid -- + restriction + order by jobname "Sort (cost=68.35..68.36 rows=1 width=132) (actual time=1.026..1.033 rows=2 loops=1)" " Sort Key: j.jobname" " -> Nested Loop (cost=36.69..68.34 rows=1 width=132) (actual time=0.877..0.961 rows=2 loops=1)" "-> Nested Loop Left Join (cost=36.69..67.58 rows=1 width=110) (actual time=0.838..0.882 rows=2 loops=1)" " -> Hash Join (cost=36.69..66.82 rows=1 width=68) (actual time=0.810..0.830 rows=2 loops=1)" "Hash Cond: ((j2.jlgjobid = j.jobid) AND (j2.jlgid = sub.max_jlgid))" "-> Seq Scan on pga_joblog j2 (cost=0.00..21.50 rows=1150 width=13) (actual time=0.024..0.130 rows=44 loops=1)" "-> Hash (cost=36.31..36.31 rows=25 width=71) (actual time=0.542..0.542 rows=2 loops=1)" " -> Hash Join (cost=30.81..36.31 rows=25 width=71) (actual time=0.506..0.523 rows=2 loops=1)" "Hash Cond: (sub.jlgjobid = j.jobid)" "-> HashAggregate (cost=27.25..29.75 rows=200 width=8) (actual time=0.270..0.275 rows=2 loops=1)" " -> Seq Scan on pga_joblog jl (cost=0.00..21.50 rows=1150 width=8) (actual time=0.013..0.116 rows=44 loops=1)" "-> Hash (cost=3.25..3.25 rows=25 width=63) (actual time=0.206..0.206 rows=25 loops=1)" " -> Seq Scan on pga_job j (cost=0.00..3.25 rows=25 width=63) (actual time=0.011..0.082 rows=25 loops=1)" " -> Index Scan using pga_jobagent_pkey on pga_jobagent ag (cost=0.00..0.75 rows=1 width=42) (actual time=0.006..0.006 rows=0 loops=2)" "Index Cond: (ag.jagpid = j.jobagentid)" "-> Index Scan using pga_jobclass_pkey on pga_jobclass cl (cost=0.00..0.75 rows=1 width=22) (actual time=0.016..0.020 rows=1 loops=2)" " Index Cond: (cl.jclid = j.jobjclid)" "Total runtime: 1.406 ms" I think this trend will continue as more and more jobs are inserted. BOOYA! Jon -Original Message- From: Andrew Sullivan [mailto:[EMAIL PROTECTED] Sent: Thursday, October 11, 2007 10:14 AM To: Roberts, Jon Cc: 'Tom Lane'; pgsql-bugs@postgresql
[BUGS] Deferred FK / PK deletion problems
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
Came across an odd bug while dealing with deferred foreign keys.
Short story: messing around with the PK table screws up deferred
constraints on the FK table. Here's a quick script to demonstrate.
Confirmed as broken on today's cvs version, as well as on 8.2 and
8.1:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Data::Dumper;
use Time::HiRes qw/gettimeofday tv_interval/;
my $t0 = [gettimeofday];
my $type = 'M';
my $port = 5432;
my $dbh1 = DBI->connect("dbi:Pg:dbname=greg;port=$port",'greg','',
{AutoCommit=>0,PrintError=>0,RaiseError=>0});
my $dbh2 = DBI->connect("dbi:Pg:dbname=greg;port=$port",'greg','',
{AutoCommit=>0,PrintError=>0,RaiseError=>0});
$dbh1->{InactiveDestroy} = 1; $dbh2->{InactiveDestroy} = 1;
$dbh1->do("DROP TABLE bar; DROP TABLE foo");
$dbh1->commit();
$dbh1->{RaiseError}=1; $dbh2->{RaiseError}=1;
$dbh1->do(qq{
CREATE TABLE foo (
foo_id INT NOT NULL PRIMARY KEY
);
CREATE TABLE bar (
bar_id INT NOT NULL PRIMARY KEY,
foo_id INT NOT NULL
);
ALTER TABLE bar ADD CONSTRAINT bar_ref_foo FOREIGN KEY (foo_id)
REFERENCES foo(foo_id) DEFERRABLE INITIALLY DEFERRED;
});
$dbh1->commit();
go(1, "INSERT INTO foo VALUES (1)");
go(1, "INSERT INTO bar VALUES (1,1)");
go(1, "COMMIT");
go(1, "DELETE FROM foo");
if (fork) {
$type = 'F';
go(2, "INSERT INTO bar VALUES (3,1);");
go(2, "COMMIT");
exit;
}
sleep 1;
go(1, "INSERT INTO foo VALUES (1)");
go(1, "COMMIT");
my $run = 1;
sub go {
my ($db,$com) = @_;
$run++;
printf "DB $db [%0.3f] {$type$run} RUN: $com\n", tv_interval($t0);
my $dbh = $db==1 ? $dbh1 : $dbh2;
my $res;
eval {
$res = $com eq 'COMMIT' ? $dbh->commit() :
$com =~ /^SELECT/ ? $dbh->selectall_arrayref($com) : $dbh->do($com);
};
if ($@) {
chomp $@;
printf "DB $db [%0.3f] {$type$run} ERROR: [EMAIL PROTECTED]",
tv_interval($t0);
$dbh->rollback();
}
}
__DATA__
Output:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for
table "foo"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for
table "bar"
DB 1 [0.120] {M1} RUN: INSERT INTO foo VALUES (1)
DB 1 [0.124] {M2} RUN: INSERT INTO bar VALUES (1,1)
DB 1 [0.126] {M3} RUN: COMMIT
DB 1 [0.162] {M4} RUN: DELETE FROM foo
DB 2 [0.165] {F5} RUN: INSERT INTO bar VALUES (3,1);
DB 2 [0.170] {F6} RUN: COMMIT
DB 1 [1.168] {M5} RUN: INSERT INTO foo VALUES (1)
DB 1 [1.169] {M6} RUN: COMMIT
DB 2 [1.183] {F6} ERROR: DBD::Pg::db commit failed: ERROR: insert or update on
table "bar" violates foreign key constraint "bar_ref_foo"
DETAIL: Key (foo_id)=(1) is not present in table "foo".
- --
Greg Sabino Mullane [EMAIL PROTECTED]
End Point Corporation
PGP Key: 0x14964AC8 200710111804
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFHDp4IvJuQZxSWSsgRAz9RAKD0HzqNlVrcM5/m+IZY5+D4W2ZfsgCgyXt8
sqioJN8iHhIo+RQWcH3p3E8=
=YbhB
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [BUGS] Deferred FK / PK deletion problems
"Greg Sabino Mullane" <[EMAIL PROTECTED]> writes: > Came across an odd bug while dealing with deferred foreign keys. I'm not convinced this is a bug. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] expression_tree_walker() and primitive node types
I wouldn't call this behavior buggy, but I found it somewhat surprising. expression_tree_walker() assumes that the walker has already been invoked on the current node (the node that a given recursive call of expression_tree_walker() has been invoked on). Therefore, calling expression_tree_walker() on a primitive node type, such as a Var, is a no-op. ---(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
