Обсуждение: Programmatically dropping and creating table
I am running Postgresql 7.2 and programmatically trying to drop a table called vm_config and then recreate it from java. The drop seems to work, but when I try to recreate the table I get a SQL exception saying "ERROR: cannot create vm_config: file exists". If I do a commit after the drop and then do the create I get the SQL Exception "ERROR: cannot create vm_config_pkey: file exists". What am I doing wrong?
Chris White
"Chris White" <cjwhite@cisco.com> writes: > I am running Postgresql 7.2 and programmatically trying to drop a table > called vm_config and then recreate it from java. The drop seems to work, but > when I try to recreate the table I get a SQL exception saying "ERROR: cannot > create vm_config: file exists". If I do a commit after the drop and then do > the create I get the SQL Exception "ERROR: cannot create vm_config_pkey: > file exists". What am I doing wrong? Are you *certain* you are talking to a 7.2 backend? That sounds a whole lot like the behavior you'd get back around 7.0 or earlier, before we'd switched to OID-based filenames: drop/create table inside a transaction didn't really work because of filename conflicts. Try "select version()" to be sure what you're talking to. regards, tom lane
It gives me PostgresSQL 7.2.1. -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Thursday, October 03, 2002 3:04 PM To: cjwhite@cisco.com Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] Programmatically dropping and creating table "Chris White" <cjwhite@cisco.com> writes: > I am running Postgresql 7.2 and programmatically trying to drop a table > called vm_config and then recreate it from java. The drop seems to work, but > when I try to recreate the table I get a SQL exception saying "ERROR: cannot > create vm_config: file exists". If I do a commit after the drop and then do > the create I get the SQL Exception "ERROR: cannot create vm_config_pkey: > file exists". What am I doing wrong? Are you *certain* you are talking to a 7.2 backend? That sounds a whole lot like the behavior you'd get back around 7.0 or earlier, before we'd switched to OID-based filenames: drop/create table inside a transaction didn't really work because of filename conflicts. Try "select version()" to be sure what you're talking to. regards, tom lane
"Chris White" <cjwhite@cisco.com> writes: >> Are you *certain* you are talking to a 7.2 backend? > It gives me PostgresSQL 7.2.1. Drat, so much for that theory. I think whatever's going wrong must be at a fairly low level. Can you try strace'ing the backend process that's handling your connection to see exactly what file name it's trying to create when you give this command? It should be something like path-to-pgdata-dir/base/oid-of-db/oid-of-new-file issued in an open() kernel call with O_CREAT and O_EXCL flags set. regards, tom lane