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
Sun, 20 Sep 1998 20:50:59 +0200 (MET DST)
Michael Reifenberger <root@totum.plaut.de>
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


Sun, 20 Sep 1998 23:36:02 +0400 (MSK DST)
Oleg Bartunov <oleg@sai.msu.su>
Re: [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
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.        |

Sun, 20 Sep 1998 22:20:16 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] current- crash
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'

Tue, 22 Sep 1998 11:12:17 +0200
"Jose' Soares" <jose@sferacarta.com>
Re: [HACKERS] current- crash
> 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.        |

Tue, 22 Sep 1998 15:41:00 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] current- crash
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


Tue, 22 Sep 1998 23:23:18 +0200 (CEST)
Michael Reifenberger <root@nihil.plaut.de>
Re: [HACKERS] current- crash
> 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.        |

Tue, 22 Sep 1998 18:00:47 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] current- crash
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


Wed, 23 Sep 1998 16:54:38 +0900
t-ishii@sra.co.jp (Tatsuo Ishii)
datetime questions and fix to dt.c
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.        |  

Wed, 23 Sep 1998 13:52:44 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] datetime questions and fix to dt.c