Thread: reproducable command sequence to get "mdopen: Couldn't open..."

Hi!

I have seen this problem on several occasions, using 7.02:

# vacuum;
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  RelationIdBuildRelation: smgropen(at_provider_user_info): No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
ERROR:  cannot open relation at_provider_user_info

To get it back to functional, I have to touch the file
data/base/dbname/at_provider_user_info, drop the table and restore it
from a backup (if it wasn't empty, which was luckily mostly the
case). This works, but there's a bug letting this happen...

Here's how I get it happening:

Script started on Sun Oct 15 18:06:12 2000
$  psql -e < src/core/db/elever-to-person.sql
You are now connected as new user nobody.
BEGIN;
BEGIN
CREATE TABLE person (userid text, giver text, first_name text, last_name text, email text, default_language text
DEFAULT'sv'); 
CREATE
CREATE UNIQUE INDEX person_index on person using btree (userid text_ops);
CREATE
CREATE INDEX person_giver_idx on person (giver);
CREATE
INSERT INTO person (userid,giver,first_name,last_name,email,default_language)
       SELECT userid,giver,fnamn,enamn,datorpost,defaultlanguage FROM elever;
INSERT 1189717 1
COMMIT;
COMMIT
BEGIN;
BEGIN
ALTER TABLE at_provider_user_info RENAME TO tmp;
NOTICE:  Caution: RENAME TABLE cannot be rolled back, so don't abort now
ALTER
CREATE TABLE at_provider_user_info (id int4, giver text, name text, order_nr int4, type text, az_only bool DEFAULT
false,hideable bool DEFAULT false, is_unique bool DEFAULT false ); 
CREATE
INSERT INTO at_provider_user_info (id,giver,name,order_nr,type,az_only,hideable,is_unique)
       SELECT *,false,false,false FROM tmp;
ERROR:  INSERT has more expressions than target columns
DROP TABLE tmp;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
ALTER TABLE at_user_info RENAME TO tmp;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
CREATE TABLE at_user_info (userid text, info_id int4, content text, visible bool DEFAULT true );
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_user_info (userid,info_id,content,visible)
       SELECT *,true FROM tmp;
ERROR:  Attribute 'visible' is of type 'bool' but expression is of type 'int4'
    You will need to rewrite or cast the expression
DROP TABLE tmp;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_provider_user_info (id,giver,name,order_nr,type)
       SELECT nextval('at_provider_user_info_seq'),giver,'Gatuadress',10,'text' FROM providers;
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  RelationIdBuildRelation: smgropen(at_provider_user_info): No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_user_info (userid,info_id,content)
       SELECT userid,currval('at_provider_user_info_seq'),gatuadress FROM elever;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_provider_user_info (id,giver,name,order_nr,type)
       SELECT nextval('at_provider_user_info_seq'),giver,'Postnummer',11,'text' FROM providers;
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_user_info (userid,info_id,content)
       SELECT userid,currval('at_provider_user_info_seq'),postnummer FROM elever;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_provider_user_info (id,giver,name,order_nr,type)
       SELECT nextval('at_provider_user_info_seq'),giver,'Postadress',12,'text' FROM providers;
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_user_info (userid,info_id,content)
       SELECT userid,currval('at_provider_user_info_seq'),postadress FROM elever;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_provider_user_info (id,giver,name,order_nr,type)
       SELECT nextval('at_provider_user_info_seq'),giver,'Telefon',13,'text' FROM providers;
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_user_info (userid,info_id,content)
       SELECT userid,currval('at_provider_user_info_seq'),telefon FROM elever;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_provider_user_info (id,giver,name,order_nr,type)
       SELECT nextval('at_provider_user_info_seq'),giver,'Fax',14,'text' FROM providers;
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_user_info (userid,info_id,content)
       SELECT userid,currval('at_provider_user_info_seq'),telefax FROM elever;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_provider_user_info (id,giver,name,order_nr,type)
       SELECT nextval('at_provider_user_info_seq'),giver,'Företag',15,'text' FROM providers;
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_user_info (userid,info_id,content)
       SELECT userid,currval('at_provider_user_info_seq'),foretag FROM elever;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_provider_user_info (id,giver,name,order_nr,type)
       SELECT nextval('at_provider_user_info_seq'),giver,'Beskrivning',16,'textarea' FROM providers;
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_user_info (userid,info_id,content)
       SELECT userid,currval('at_provider_user_info_seq'),beskrivning FROM elever;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_provider_user_info (id,giver,name,order_nr,type)
       SELECT nextval('at_provider_user_info_seq'),giver,'Titel',17,'text' FROM providers;
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  mdopen: couldn't open at_provider_user_info: No such file or directory
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_user_info (userid,info_id,content)
       SELECT userid,currval('at_provider_user_info_seq'),titel FROM elever;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
ALTER TABLE at_provider_course_info RENAME TO tmp;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
CREATE TABLE at_provider_course_info (id int4, giver text, name text, order_nr int4, type text, is_unique bool DEFAULT
false);
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_provider_course_info (id,giver,name,order_nr,type,is_unique)
       SELECT *,false FROM tmp;
ERROR:  INSERT has more expressions than target columns
DROP TABLE tmp;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
ALTER TABLE at_course_info RENAME TO tmp;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
CREATE TABLE at_course_info (course_id int4, info_id int4, content text, visible bool DEFAULT true);
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
INSERT INTO at_course_info (course_id,info_id,content,visible)
       SELECT *,false FROM tmp;
ERROR:  Attribute 'visible' is of type 'bool' but expression is of type 'int4'
    You will need to rewrite or cast the expression
DROP TABLE tmp;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
COMMIT;
COMMIT
VACUUM;
NOTICE:  Skipping "pg_type" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_attribute" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_proc" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_class" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_indexes" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_group" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_database" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_attrdef" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_relcheck" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_trigger" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_inherits" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_index" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_statistic" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_operator" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_opclass" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_am" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_amop" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_amproc" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_language" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_aggregate" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_ipl" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_inheritproc" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_rewrite" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_listener" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_description" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_shadow" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_user" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_rules" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_views" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_tables" --- only table owner can VACUUM it
ERROR:  pg_ownercheck: class "tmp" not found
VACUUM ANALYZE;
NOTICE:  Skipping "pg_type" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_attribute" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_proc" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_class" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_indexes" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_group" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_database" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_attrdef" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_relcheck" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_trigger" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_inherits" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_index" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_statistic" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_operator" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_opclass" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_am" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_amop" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_amproc" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_language" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_aggregate" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_ipl" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_inheritproc" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_rewrite" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_listener" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_description" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_shadow" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_user" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_rules" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_views" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_tables" --- only table owner can VACUUM it
ERROR:  pg_ownercheck: class "tmp" not found
$ exit

Script done on Sun Oct 15 18:06:22 2000

Cheers,
Palle

Palle Girgensohn <girgen@partitur.se> writes:
> BEGIN;
> BEGIN
> ALTER TABLE at_provider_user_info RENAME TO tmp;
> NOTICE:  Caution: RENAME TABLE cannot be rolled back, so don't abort now
  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> [ followed by deliberately-provoked error to abort the transaction ]

Well, it did tell you not to do this ;-)

Making ALTER TABLE RENAME abort-safe requires changing the conventions
for naming physical table files, and the details have been sufficiently
controversial that it's not been done yet.  In the meantime our only
choices are to forbid ALTER TABLE RENAME within transaction blocks, or
allow it with the knowledge that people can shoot themselves in the foot
with it.  The above warning notice is the current compromise.

            regards, tom lane

At 12:41 15/10/00 -0400, Tom Lane wrote:
>
>Making ALTER TABLE RENAME abort-safe requires changing the conventions
>for naming physical table files, and the details have been sufficiently
>controversial that it's not been done yet.  In the meantime our only
>choices are to forbid ALTER TABLE RENAME within transaction blocks, or
>allow it with the knowledge that people can shoot themselves in the foot
>with it.  The above warning notice is the current compromise.

Is it going to be in 7.1? If not, it might be worth disabling in TX blocks
since a mistyped SQL query can cause an aborted transaction, something that
is somewhat counter-intuitive. Maybe the message should be expanded to say:

    NOTICE:  Caution: beyond this point, a mis-spelled attribute name will
corrupt your database


Just an idea... ;-)


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Philip Warner <pjw@rhyme.com.au> writes:
>     NOTICE:  Caution: beyond this point, a mis-spelled attribute name will
> corrupt your database

How many distinct kinds of typos do you think the message should
mention?  I'd think something like that would be more confusing
than not, since it implies that only some kinds of errors are bad.

Personally, I'd vote for disallowing ALTER RENAME inside transaction
blocks until we can do it safely.  But people seem to think it's too
useful to do that...

            regards, tom lane

At 21:41 15/10/00 -0400, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>>     NOTICE:  Caution: beyond this point, a mis-spelled attribute name will
>> corrupt your database
>
>How many distinct kinds of typos do you think the message should
>mention?  I'd think something like that would be more confusing
>than not, since it implies that only some kinds of errors are bad.

Er...it was a joke.


>Personally, I'd vote for disallowing ALTER RENAME inside transaction
>blocks until we can do it safely.

I agree totally. The bogus error message was to highlight the fragility of
allowing rename in TX blocks.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Philip Warner <pjw@rhyme.com.au> writes:
> Er...it was a joke.

My mistake, missed the smiley :-(

>> Personally, I'd vote for disallowing ALTER RENAME inside transaction
>> blocks until we can do it safely.

> I agree totally.

Well, does anyone want to re-open the issue?  ALTER TABLE RENAME and
DROP TABLE both have this problem, and both would have to be disallowed
inside xact blocks to close up the hole.  That might be too much
functionality lost...

            regards, tom lane