Обсуждение: current- crash

Поиск

Список

Период

Сортировка

current- crash

От

Michael Reifenberger

Дата:

20 сентября 1998 г., 17:51:07

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 );

Re: [HACKERS] current- crash

От

Oleg Bartunov

Дата:

20 сентября 1998 г., 18:37:23

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

Re: [HACKERS] current- crash

От

Bruce Momjian

Дата:

21 сентября 1998 г., 01:25:29

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

Re: [HACKERS] current- crash

От

"Jose' Soares"

Дата:

22 сентября 1998 г., 08:16:21

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'

Re: [HACKERS] current- crash

От

Bruce Momjian

Дата:

22 сентября 1998 г., 19:02:13

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

Re: [HACKERS] current- crash

От

Michael Reifenberger

Дата:

22 сентября 1998 г., 20:24:21

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

Re: [HACKERS] current- crash

От

Bruce Momjian

Дата:

22 сентября 1998 г., 21:01:44

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

datetime questions and fix to dt.c

От

t-ishii@sra.co.jp (Tatsuo Ishii)

Дата:

23 сентября 1998 г., 06:56:17

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

Re: [HACKERS] datetime questions and fix to dt.c

От

Bruce Momjian

Дата:

23 сентября 1998 г., 16:52:44

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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: current- crash