Thread: current- crash
Hi,
I can easily crash the backend of current- postgres on current- FreeBSD with
loading a database with test.sql and test.dmp an executing the script x.sql.
Anyone else?
Bye!
----
Michael Reifenberger
Plaut Software GmbH, R/3 Basis
CREATE TABLE b (begt datetime, kid int4);
CREATE TABLE a (kid int4);
CREATE TABLE c (a int4, b varchar(30), c int4);
CREATE INDEX b_0 on b using btree ( begt datetime_ops );
CREATE INDEX b_1 on b using btree ( kid int4_ops );
CREATE INDEX a_0 on a using btree ( kid int4_ops );
COPY b FROM stdin;
\.
COPY a FROM stdin;
\.
COPY c FROM stdin;
1 foo 1
2 foo bar 2
3 \N 3
4 \\serverla 4
\.
explain SELECT a.kid as foo
FROM a, b WHERE
a.kid = b.kid AND
( b.kid = 23 OR
b.kid = 36 );
On Sun, 20 Sep 1998, Michael Reifenberger wrote:
> Date: Sun, 20 Sep 1998 20:50:59 +0200 (MET DST)
> From: Michael Reifenberger <root@totum.plaut.de>
> To: PostgreSQL Hackers <pgsql-hackers@postgreSQL.org>
> Subject: [HACKERS] current- crash
>
> Hi,
> I can easily crash the backend of current- postgres on current- FreeBSD with
> loading a database with test.sql and test.dmp an executing the script x.sql.
> Anyone else?
>
> Bye!
> ----
> Michael Reifenberger
> Plaut Software GmbH, R/3 Basis
>
Yes,
I've posted message about query which crashes backend on my Linux box -
it looks as yours. Also, could you check 'vacuum analyze'
on your database when you run postgres with -B 1024 option.
On my system it crashes.
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
> Hi, > I can easily crash the backend of current- postgres on current- FreeBSD with > loading a database with test.sql and test.dmp an executing the script x.sql. > Anyone else? > > Bye! > ---- > Michael Reifenberger > Plaut Software GmbH, R/3 Basis Content-Description: > CREATE TABLE b (begt datetime, kid int4); > CREATE TABLE a (kid int4); > CREATE TABLE c (a int4, b varchar(30), c int4); > CREATE INDEX b_0 on b using btree ( begt datetime_ops ); > CREATE INDEX b_1 on b using btree ( kid int4_ops ); > CREATE INDEX a_0 on a using btree ( kid int4_ops ); > Content-Description: > COPY b FROM stdin; > \. > COPY a FROM stdin; > \. > COPY c FROM stdin; > 1 foo 1 > 2 foo bar 2 > 3 \N 3 > 4 \\serverla 4 > \. > Content-Description: > explain SELECT a.kid as foo > FROM a, b WHERE > a.kid = b.kid AND > ( b.kid = 23 OR > b.kid = 36 ); > This is a known problem, and is going on the open items list. The problem is the new OR indexing code, and an attempt to test for a MERGEJOIN where it really should not be. I can see why it is happening, and will have to research it. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 http://www.op.net/~candle | (610) 353-9879(w) + If your life is a hard drive, | (610) 853-3000(h) + Christ can be your backup. |
Michael Reifenberger wrote:
>
> Hi,
> I can easily crash the backend of current- postgres on current- FreeBSD with
> loading a database with test.sql and test.dmp an executing the script x.sql.
> Anyone else?
>
> Bye!
> ----
> Michael Reifenberger
> Plaut Software GmbH, R/3 Basis
>
> ------------------------------------------------------------------------
> CREATE TABLE b (begt datetime, kid int4);
> CREATE TABLE a (kid int4);
> CREATE TABLE c (a int4, b varchar(30), c int4);
> CREATE INDEX b_0 on b using btree ( begt datetime_ops );
> CREATE INDEX b_1 on b using btree ( kid int4_ops );
> CREATE INDEX a_0 on a using btree ( kid int4_ops );
>
> ------------------------------------------------------------------------
> COPY b FROM stdin;
> \.
> COPY a FROM stdin;
> \.
> COPY c FROM stdin;
> 1 foo 1
> 2 foo bar 2
> 3 \N 3
> 4 \\serverla 4
> \.
>
> ------------------------------------------------------------------------
> explain SELECT a.kid as foo
> FROM a, b WHERE
> a.kid = b.kid AND
> ( b.kid = 23 OR
> b.kid = 36 );
I tried your script but I can't see nothing wrong. :)
CREATE TABLE b (begt datetime, kid int4);
CREATE
CREATE TABLE a (kid int4);
CREATE
CREATE TABLE c (a int4, b varchar(30), c int4);
CREATE
CREATE INDEX b_0 on b using btree ( begt datetime_ops );
CREATE
CREATE INDEX b_1 on b using btree ( kid int4_ops );
CREATE
CREATE INDEX a_0 on a using btree ( kid int4_ops );
CREATE
COPY b FROM stdin;
COPY a FROM stdin;
COPY c FROM stdin;
select * from c;
a|b |c
-+---------+-
1|foo |1
2|foo bar |2
3| |3
4|\\servela|4
(4 rows)
explain SELECT a.kid as foo
FROM a, b WHERE
a.kid = b.kid AND
( b.kid = 23 OR
b.kid = 36 );
NOTICE: QUERY PLAN:
Merge Join (cost=0.00 size=1 width=8)
-> Seq Scan (cost=0.00 size=0 width=0)
-> Sort (cost=0.00 size=0 width=0)
-> Seq Scan on a (cost=0.00 size=0 width=4)
-> Seq Scan (cost=0.00 size=0 width=0)
-> Sort (cost=0.00 size=0 width=0)
-> Seq Scan on b (cost=0.00 size=0 width=4)
EXPLAIN
Jose'
> Michael Reifenberger wrote: > > > > Hi, > > I can easily crash the backend of current- postgres on current- FreeBSD with > > loading a database with test.sql and test.dmp an executing the script x.sql. > > Anyone else? > > > > Bye! > > ---- > > Michael Reifenberger > > Plaut Software GmbH, R/3 Basis > > > > ------------------------------------------------------------------------ > > CREATE TABLE b (begt datetime, kid int4); > > CREATE TABLE a (kid int4); > > CREATE TABLE c (a int4, b varchar(30), c int4); > > CREATE INDEX b_0 on b using btree ( begt datetime_ops ); > > CREATE INDEX b_1 on b using btree ( kid int4_ops ); > > CREATE INDEX a_0 on a using btree ( kid int4_ops ); > > > > ------------------------------------------------------------------------ > > COPY b FROM stdin; > > \. > > COPY a FROM stdin; > > \. > > COPY c FROM stdin; > > 1 foo 1 > > 2 foo bar 2 > > 3 \N 3 > > 4 \\serverla 4 > > \. > > > > ------------------------------------------------------------------------ > > explain SELECT a.kid as foo > > FROM a, b WHERE > > a.kid = b.kid AND > > ( b.kid = 23 OR > > b.kid = 36 ); > > > I tried your script but I can't see nothing wrong. :) > > CREATE TABLE b (begt datetime, kid int4); > CREATE > CREATE TABLE a (kid int4); > CREATE > CREATE TABLE c (a int4, b varchar(30), c int4); > CREATE > CREATE INDEX b_0 on b using btree ( begt datetime_ops ); > CREATE > CREATE INDEX b_1 on b using btree ( kid int4_ops ); > CREATE > CREATE INDEX a_0 on a using btree ( kid int4_ops ); > CREATE > > COPY b FROM stdin; > COPY a FROM stdin; > > COPY c FROM stdin; > > select * from c; > a|b |c > -+---------+- > 1|foo |1 > 2|foo bar |2 > 3| |3 > 4|\\servela|4 > (4 rows) > > explain SELECT a.kid as foo > FROM a, b WHERE > a.kid = b.kid AND > ( b.kid = 23 OR > b.kid = 36 ); > NOTICE: QUERY PLAN: > > Merge Join (cost=0.00 size=1 width=8) > -> Seq Scan (cost=0.00 size=0 width=0) > -> Sort (cost=0.00 size=0 width=0) > -> Seq Scan on a (cost=0.00 size=0 width=4) > -> Seq Scan (cost=0.00 size=0 width=0) > -> Sort (cost=0.00 size=0 width=0) > -> Seq Scan on b (cost=0.00 size=0 width=4) > > EXPLAIN Perhaps because I fixed it two days ago. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 http://www.op.net/~candle | (610) 353-9879(w) + If your life is a hard drive, | (610) 853-3000(h) + Christ can be your backup. |
On Tue, 22 Sep 1998, Bruce Momjian wrote: ... > > I tried your script but I can't see nothing wrong. :) Right! The crash is fixed. Thanks Bruce! Now, shouldn't I see Index Scans below or is it a optimizer feature because the tables are too empty? > > Merge Join (cost=0.00 size=1 width=8) > > -> Seq Scan (cost=0.00 size=0 width=0) > > -> Sort (cost=0.00 size=0 width=0) > > -> Seq Scan on a (cost=0.00 size=0 width=4) > > -> Seq Scan (cost=0.00 size=0 width=0) > > -> Sort (cost=0.00 size=0 width=0) > > -> Seq Scan on b (cost=0.00 size=0 width=4) Bye! ---- Michael Reifenberger Plaut Software GmbH, R/3 Basis
> On Tue, 22 Sep 1998, Bruce Momjian wrote: > ... > > > I tried your script but I can't see nothing wrong. :) > Right! > The crash is fixed. > Thanks Bruce! > Now, shouldn't I see Index Scans below or is it a optimizer feature > because the tables are too empty? > > > Merge Join (cost=0.00 size=1 width=8) > > > -> Seq Scan (cost=0.00 size=0 width=0) > > > -> Sort (cost=0.00 size=0 width=0) > > > -> Seq Scan on a (cost=0.00 size=0 width=4) > > > -> Seq Scan (cost=0.00 size=0 width=0) > > > -> Sort (cost=0.00 size=0 width=0) > > > -> Seq Scan on b (cost=0.00 size=0 width=4) Not sure. Joins usually don't use index scans if the table is used in the outer part of the join loop because it is just quicker to sort them. Also the tables must have a significant amount of data in them to use indexes. I am also not sure how the system handles an OR and a join on the same column. I would think it does not use an index, but perhaps it does the restriction using an index, then does the join. That is what it should do. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 http://www.op.net/~candle | (610) 353-9879(w) + If your life is a hard drive, | (610) 853-3000(h) + Christ can be your backup. |
Hi,
Documents stat that datetime type allows "far into future."
So I did some testing.
test=> select datetime '3276900-12-01';
?column?
--------------------------
Tue Aug 27 00:00:00 337095
(1 row)
test=> select '32769000-12-01'::datetime;
?column?
--------------------------
Thu Jan 07 00:00:00 431143
(1 row)
test=> select '327690000-12-01'::datetime;
?column?
---------------------------
Sat Aug 27 00:00:00 1371616
(1 row)
I suspect this is due to the limitation of the timezone database
in my system. Is that correct? If so, how can I know how far we
can go into the future using the datetime type?
BTW, dt.c seems to have problem with date_part() function.
test=> select date_part('microsecond','1998/9/23 12:04:05.1234'::datetime);
date_part
---------
123.4
(1 row)
test=> select date_part('millisecond','1998/9/23 12:04:05.1234'::datetime);
date_part
---------
123.4
Included patches should fix this.
*** dt.c.orig Wed Sep 23 16:03:44 1998
--- dt.c Wed Sep 23 16:06:10 1998
***************
*** 2199,2205 ****
{INVALID, RESERV, DTK_INVALID}, /* "invalid" reserved for invalid
* time */
{"m", UNITS, DTK_MINUTE}, /* "minute" relative time units */
! {"microsecon", UNITS, DTK_MILLISEC}, /* "microsecond" relative
* time units */
{"mil", UNITS, DTK_MILLENIUM}, /* "millenium" relative time units */
{"mils", UNITS, DTK_MILLENIUM}, /* "millenia" relative time units */
--- 2199,2205 ----
{INVALID, RESERV, DTK_INVALID}, /* "invalid" reserved for invalid
* time */
{"m", UNITS, DTK_MINUTE}, /* "minute" relative time units */
! {"microsecon", UNITS, DTK_MICROSEC}, /* "microsecond" relative
* time units */
{"mil", UNITS, DTK_MILLENIUM}, /* "millenium" relative time units */
{"mils", UNITS, DTK_MILLENIUM}, /* "millenia" relative time units */
--
Tatsuo Ishii
t-ishii@sra.co.jp
Applied.
---------------------------------------------------------------------------
Documents stat that datetime type allows "far into future."
So I did some testing.
test=> select datetime '3276900-12-01';
?column?
--------------------------
Tue Aug 27 00:00:00 337095
(1 row)
test=> select '32769000-12-01'::datetime;
?column?
--------------------------
Thu Jan 07 00:00:00 431143
(1 row)
test=> select '327690000-12-01'::datetime;
?column?
---------------------------
Sat Aug 27 00:00:00 1371616
(1 row)
I suspect this is due to the limitation of the timezone database
in my system. Is that correct? If so, how can I know how far we
can go into the future using the datetime type?
BTW, dt.c seems to have problem with date_part() function.
test=> select date_part('microsecond','1998/9/23 12:04:05.1234'::datetime);
date_part
---------
123.4
(1 row)
test=> select date_part('millisecond','1998/9/23 12:04:05.1234'::datetime);
date_part
---------
123.4
Included patches should fix this.
*** dt.c.orig Wed Sep 23 16:03:44 1998
--- dt.c Wed Sep 23 16:06:10 1998
***************
*** 2199,2205 ****
{INVALID, RESERV, DTK_INVALID}, /* "invalid" reserved for invalid
* time */
{"m", UNITS, DTK_MINUTE}, /* "minute" relative time units */
! {"microsecon", UNITS, DTK_MILLISEC}, /* "microsecond" relative
* time units */
{"mil", UNITS, DTK_MILLENIUM}, /* "millenium" relative time units */
{"mils", UNITS, DTK_MILLENIUM}, /* "millenia" relative time units */
--- 2199,2205 ----
{INVALID, RESERV, DTK_INVALID}, /* "invalid" reserved for invalid
* time */
{"m", UNITS, DTK_MINUTE}, /* "minute" relative time units */
! {"microsecon", UNITS, DTK_MICROSEC}, /* "microsecond" relative
* time units */
{"mil", UNITS, DTK_MILLENIUM}, /* "millenium" relative time units */
{"mils", UNITS, DTK_MILLENIUM}, /* "millenia" relative time units */
--
Tatsuo Ishii
t-ishii@sra.co.jp
[Charset iso-2022-jp unsupported, skipping...]
--
Bruce Momjian | maillist@candle.pha.pa.us
830 Blythe Avenue | http://www.op.net/~candle
Drexel Hill, Pennsylvania 19026 | (610) 353-9879(w)
+ If your life is a hard drive, | (610) 853-3000(h)
+ Christ can be your backup. |