Обсуждение: 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

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