current- crash

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.        |