FAQ
And here is an old nemesis, thought to have been fixed in 7.0,
reproducible on 7.0beta3 with the following:

DROP TABLE foo;
CREATE TABLE foo (d date);
CREATE UNIQUE INDEX date_uidx ON foo(d);
CREATE UNIQUE INDEX datetime_uidx ON foo(datetime(d));
INSERT INTO foo (d) VALUES ('17-Jun-1995');

DROP TABLE bar;
DROP SEQUENCE bar_id_seq;
CREATE TABLE bar (
id SERIAL,
start_time DATETIME,
duration FLOAT
);
INSERT INTO bar (start_time, duration) VALUES ('17-Jun-1995', 3);

DROP TABLE baz;
DROP SEQUENCE baz_id_seq;
CREATE TABLE baz (
id SERIAL,
bar_id INTEGER,
duration FLOAT
);
INSERT INTO baz (bar_id, duration) SELECT id, duration FROM bar;

-- Here's the offending query...
SELECT f.d, r.start_time::date, r.duration AS "r_dur", z.duration AS
"z_dur"
FROM foo f, bar r, baz z
WHERE r.id = 2
AND z.bar_id = 2
AND f.d = (r.start_time - '1 day'::interval)::date ;

Search Discussions

  • Ed Loehr at Jun 26, 2000 at 5:24 am

    Ed Loehr wrote:

    And here is an old nemesis, thought to have been fixed in 7.0,
    reproducible on 7.0beta3 with the following:

    DROP TABLE foo;
    CREATE TABLE foo (d date);
    CREATE UNIQUE INDEX date_uidx ON foo(d);
    CREATE UNIQUE INDEX datetime_uidx ON foo(datetime(d));
    INSERT INTO foo (d) VALUES ('17-Jun-1995');

    DROP TABLE bar;
    DROP SEQUENCE bar_id_seq;
    CREATE TABLE bar (
    id SERIAL,
    start_time DATETIME,
    duration FLOAT
    );
    INSERT INTO bar (start_time, duration) VALUES ('17-Jun-1995', 3);

    DROP TABLE baz;
    DROP SEQUENCE baz_id_seq;
    CREATE TABLE baz (
    id SERIAL,
    bar_id INTEGER,
    duration FLOAT
    );
    INSERT INTO baz (bar_id, duration) SELECT id, duration FROM bar;

    A final clue: if I run 'VACUUM ANALYZE' at this point in the script,
    before the select, the error disappears.

    Regards,
    Ed Loehr
    -- Here's the offending query...
    SELECT f.d, r.start_time::date, r.duration AS "r_dur", z.duration AS
    "z_dur"
    FROM foo f, bar r, baz z
    WHERE r.id = 2
    AND z.bar_id = 2
    AND f.d = (r.start_time - '1 day'::interval)::date ;
  • Tom Lane at Jun 26, 2000 at 6:43 am
    Try it in 7.0.2 --- if it still happens there then I'm interested.
    (Don't see it here with current sources.)

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJun 26, '00 at 5:09a
activeJun 26, '00 at 6:43a
posts3
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Ed Loehr: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2021 Grokbase