Обсуждение: SELECT INTO TABLE busted?
I've been seeing the following behavior with the CVS sources for the
last several days:
$ psql regression
regression=> SELECT * INTO TABLE ramp FROM road ;
SELECT
regression=> \d ramp
Couldn't find table ramp!
regression=> SELECT * INTO TABLE ramp FROM road ;
ERROR: ramp relation already exists
regression=> SELECT * FROM ramp ;
< works fine, produces plenty of output... >
If you exit psql and start a new session, "ramp" has disappeared
entirely. This is causing the create_view regression test to fail,
since it depends on a table made like this in a prior test.
My guess is that this is an unwanted side effect of the "temp table"
code. Is anyone else seeing the same, or am I looking for a
platform-specific bug? (gcc 2.7.2.2 on HPUX 9.03, for the record.)
regards, tom lane
> I've been seeing the following behavior with the CVS sources for the > last several days: > > $ psql regression > regression=> SELECT * INTO TABLE ramp FROM road ; > SELECT > regression=> \d ramp > Couldn't find table ramp! > regression=> SELECT * INTO TABLE ramp FROM road ; > ERROR: ramp relation already exists > regression=> SELECT * FROM ramp ; > < works fine, produces plenty of output... > > > If you exit psql and start a new session, "ramp" has disappeared > entirely. This is causing the create_view regression test to fail, > since it depends on a table made like this in a prior test. > > My guess is that this is an unwanted side effect of the "temp table" > code. Is anyone else seeing the same, or am I looking for a > platform-specific bug? (gcc 2.7.2.2 on HPUX 9.03, for the record.) Very likely the TEMP table stuff, though I think the changes only take affect when you do a temp table. I am passing the create_view regression test here. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Have you done a fresh initdb recently? > I've been seeing the following behavior with the CVS sources for the > last several days: > > $ psql regression > regression=> SELECT * INTO TABLE ramp FROM road ; > SELECT > regression=> \d ramp > Couldn't find table ramp! > regression=> SELECT * INTO TABLE ramp FROM road ; > ERROR: ramp relation already exists > regression=> SELECT * FROM ramp ; > < works fine, produces plenty of output... > > > If you exit psql and start a new session, "ramp" has disappeared > entirely. This is causing the create_view regression test to fail, > since it depends on a table made like this in a prior test. > > My guess is that this is an unwanted side effect of the "temp table" > code. Is anyone else seeing the same, or am I looking for a > platform-specific bug? (gcc 2.7.2.2 on HPUX 9.03, for the record.) > > regards, tom lane > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> I am passing the create_view regression test here.
I was afraid you would say that. I'll start digging.
> Have you done a fresh initdb recently?
Yup, this is starting from "distclean" sources and an empty
install directory.
I'm seeing several other regress failures that seem to be caused by
the same SELECT INTO problem. Most are the same "no such table"
failure in a later test session, but the "transactions" test
actually coredumps:
QUERY: BEGIN;
QUERY: SELECT * INTO TABLE xacttest FROM aggtest;
QUERY: INSERT INTO xacttest (a, b) VALUES (777, 777.777);
QUERY: END;
QUERY: SELECT a FROM xacttest WHERE a > 100;
pqReadData() -- backend closed the channel unexpectedly.
I did a little bit of corefile-entrails-examining and found
that heapgettuple was hitting a bad pointer, but ran out of
energy before getting further than that. If that rings any
bells please let me know. I won't have time to look at this
more until Saturday.
regards, tom lane
> Bruce Momjian <maillist@candle.pha.pa.us> writes: > > I am passing the create_view regression test here. > > I was afraid you would say that. I'll start digging. > > > Have you done a fresh initdb recently? > > Yup, this is starting from "distclean" sources and an empty > install directory. > > I'm seeing several other regress failures that seem to be caused by > the same SELECT INTO problem. Most are the same "no such table" > failure in a later test session, but the "transactions" test > actually coredumps: One idea. Can you cvs a version before the temp table stuff, and see if they pass? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> I'm seeing several other regress failures that seem to be caused by
>> the same SELECT INTO problem.
> One idea. Can you cvs a version before the temp table stuff, and see if
> they pass?
They were all passing a week or so ago. Since it works for you (and
apparently for the rest of the crew), I have to assume there's some
platform-dependent bug in the new temp table stuff. I'll try to dig
into it tomorrow --- but if you have any thoughts about the most likely
places to look, please let me know.
regards, tom lane
I found part of the SELECT INTO TABLE problem: at line 2872 of gram.y,
n->istemp = (bool)((A_Const *)lfirst($4))->val.val.ival;
the cast should be to Value* not A_Const*. On my machine, an
uninitialized field is picked up and dropped into n->istemp,
so that the system sometimes interprets SELECT INTO TABLE
as SELECT INTO TEMP TABLE. Ooops.
With this fix, the regression tests pass again (except the "don't know
whether nodes of type 600 are equal" problem is still there).
However, I can now report that there's a second bug involving
trying to access a temp table after end of transaction.
The query series (in the regression database)
BEGIN;
SELECT * INTO TEMP TABLE xacttest FROM aggtest;
INSERT INTO xacttest (a, b) VALUES (777, 777.777);
END;
SELECT a FROM xacttest WHERE a > 100;
crashes the backend. It seems to think that xacttest still exists,
but it chokes trying to retrieve tuples from it. (Whether a non-temp
table xacttest exists doesn't seem to matter, btw.)
Am I right in thinking that the temp table should disappear
at END TRANSACTION?
regards, tom lane