Thread

DROP TABLE inside a transaction block

Tatsuo Ishii <t-ishii@sra.co.jp> — 2000-03-05T09:09:51Z

I see following in HISTORY:

	Disallow DROP TABLE/DROP INDEX inside a transaction block

However, it seems that this is not done with current?

test=# create table t1(i int);
CREATE
test=# begin;
BEGIN
test=# drop table t1;
NOTICE:  Caution: DROP TABLE cannot be rolled back, so don't abort now
DROP
test=# end;
COMMIT
test=# \d
No relations found.
--
Tatsuo Ishii

Re: [HACKERS] DROP TABLE inside a transaction block

Peter Eisentraut <peter_e@gmx.net> — 2000-03-05T13:36:22Z

Tatsuo Ishii writes:

> I see following in HISTORY:
> 
> 	Disallow DROP TABLE/DROP INDEX inside a transaction block
> 
> However, it seems that this is not done with current?
> 
> test=# create table t1(i int);
> CREATE
> test=# begin;
> BEGIN
> test=# drop table t1;
> NOTICE:  Caution: DROP TABLE cannot be rolled back, so don't abort now

Wow, with all due respect, that's pretty sh^H^Hpoor. That's like saying
"Haha, either you commit your transaction or your database is fried." Any
reason that's not an ERROR before anything destructive is done?

> DROP
> test=# end;
> COMMIT
> test=# \d
> No relations found.
> --
> Tatsuo Ishii
> 
> ************
> 
> 

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] DROP TABLE inside a transaction block

Mike Mascari <mascarm@mascari.com> — 2000-03-06T01:31:25Z

Peter Eisentraut wrote:
> 
> Tatsuo Ishii writes:
> 
> > I see following in HISTORY:
> >
> >       Disallow DROP TABLE/DROP INDEX inside a transaction block
> >
> > However, it seems that this is not done with current?
> >
> > test=# create table t1(i int);
> > CREATE
> > test=# begin;
> > BEGIN
> > test=# drop table t1;
> > NOTICE:  Caution: DROP TABLE cannot be rolled back, so don't abort now
> 
> Wow, with all due respect, that's pretty sh^H^Hpoor. That's like saying
> "Haha, either you commit your transaction or your database is fried." Any
> reason that's not an ERROR before anything destructive is done?
> 
> > DROP
> > test=# end;
> > COMMIT
> > test=# \d
> > No relations found.

We had an elaborate discussion on this very topic several months
ago. What it comes down to is three possible options:

1) Allow DDL statements in transactions. If the transaction
aborts, currently, corruption can result. Some DDL statements
(such as TRUNCATE) make no sense with respect to ROLLBACK. So, I
guess, the idea is that SOME DDL statements will be ROLLBACK-able
and some won't - yuck.

2) Disallow DDL statement in transactions. This would break code
for people which is working now, only because their transactions
are being committed between the time they issue the DDL statement
and the COMMIT (or END), instead of aborting and causing their
database to become corrupt, or require manual removal of files
when the catalogue gets out-of-sync with the filesystem.

3) Implicitly commit the running transaction and begin a new one.
Only Vadim and I support this notion, although this is precisely
what Oracle does (not that that should define PostgreSQL's
behavior, of course). Everyone else, it seems wants to try to
implement #1 successfully...(I don't see it happening any time
soon).

So, as some sort of compromise, a NOTICE was issued.

Mike Mascari

Re: [HACKERS] DROP TABLE inside a transaction block

Mike Mascari <mascarm@mascari.com> — 2000-03-06T03:03:28Z

Philip Warner wrote:
> 
> At 07:59 6/03/00 +0100, Peter Eisentraut wrote:
> >> (such as TRUNCATE) make no sense with respect to ROLLBACK. So, I
> >> guess, the idea is that SOME DDL statements will be ROLLBACK-able
> >> and some won't - yuck.
> >
> >I don't see a problem with disallowing some DDL commands in a transaction
> >as long as they throw an error and the transaction aborts.
> 
> Is it really necessary to abort the TX? Seems a little antisocial - can't
> you just return an error, and let the user/application decide if it needs
> to abort?
> 
> >> 3) Implicitly commit the running transaction and begin a new one.
> >> Only Vadim and I support this notion, although this is precisely
> >> what Oracle does (not that that should define PostgreSQL's
> >> behavior, of course). Everyone else, it seems wants to try to
> >> implement #1 successfully...(I don't see it happening any time
> >> soon).
> >
> >I support that too since it also happens to be SQL's idea more or less.
> >One of these days we'll have to offer this as an option. At least for
> >commands for which #1 doesn't work yet.
> 
> Do you really mean it when ou say 'Implicitly commit the running
> transaction'. I would be deeply opposed to this philosophically, if so. No
> TX should ever be commited unless the user requests it.
> 
> Just my 0.02c

Philosophically, I agree with you 100%. And apparently, from the
previous discussion on this issue, databases like Informix are
completely capable of rolling back DDL statements like DROP
TABLE, ALTER TABLE RENAME, etc. However, the complexity involved
apparently was too much for Oracle:

"ORACLE implicitly commits the current transaction before and
after every Data Definition Language statement."

Its just my feeling that robustness is the number one priority
and that the current state is kind of "riding the fence" between
ORACLE and Informix. On either side of the fence, it is safe, but
in the middle, you risk corruption.

Naturally, I'd like to see PostgreSQL on the Informix side of the
fence, but I don't see it happening any time soon. And the ORACLE
side of the fence is far easier to implement. Or, of course, you
could choose Peter's suggestion, and disallow the DDL statement
entirely. But as soon as that happened, all those people that
begin their .cgi programs with BEGIN and end it with END will
say, "Hey, if we can't use DDL statements in transactions, can't
we at least do what Oracle does so we don't have to change our
code?"

Mike Mascari

Re: [HACKERS] DROP TABLE inside a transaction block

Peter Eisentraut <e99re41@docs.uu.se> — 2000-03-06T06:59:00Z

On Sun, 5 Mar 2000, Mike Mascari wrote:

> 1) Allow DDL statements in transactions. If the transaction
> aborts, currently, corruption can result. Some DDL statements
                     ^^^^^^^^^^^^^^^^^^^^^
I think those are the key words.

> (such as TRUNCATE) make no sense with respect to ROLLBACK. So, I
> guess, the idea is that SOME DDL statements will be ROLLBACK-able
> and some won't - yuck.

I don't see a problem with disallowing some DDL commands in a transaction
as long as they throw an error and the transaction aborts. Users see this
and don't do it next time. Sure it's inconsistent but the current state is
plain bad, sorry.

> 3) Implicitly commit the running transaction and begin a new one.
> Only Vadim and I support this notion, although this is precisely
> what Oracle does (not that that should define PostgreSQL's
> behavior, of course). Everyone else, it seems wants to try to
> implement #1 successfully...(I don't see it happening any time
> soon).

I support that too since it also happens to be SQL's idea more or less.
One of these days we'll have to offer this as an option. At least for
commands for which #1 doesn't work yet.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] DROP TABLE inside a transaction block

Philip Warner <pjw@rhyme.com.au> — 2000-03-06T07:27:34Z

At 07:59 6/03/00 +0100, Peter Eisentraut wrote:
>> (such as TRUNCATE) make no sense with respect to ROLLBACK. So, I
>> guess, the idea is that SOME DDL statements will be ROLLBACK-able
>> and some won't - yuck.
>
>I don't see a problem with disallowing some DDL commands in a transaction
>as long as they throw an error and the transaction aborts. 

Is it really necessary to abort the TX? Seems a little antisocial - can't
you just return an error, and let the user/application decide if it needs
to abort?


>> 3) Implicitly commit the running transaction and begin a new one.
>> Only Vadim and I support this notion, although this is precisely
>> what Oracle does (not that that should define PostgreSQL's
>> behavior, of course). Everyone else, it seems wants to try to
>> implement #1 successfully...(I don't see it happening any time
>> soon).
>
>I support that too since it also happens to be SQL's idea more or less.
>One of these days we'll have to offer this as an option. At least for
>commands for which #1 doesn't work yet.

Do you really mean it when ou say 'Implicitly commit the running
transaction'. I would be deeply opposed to this philosophically, if so. No
TX should ever be commited unless the user requests it.

Just my 0.02c



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

Re: [HACKERS] DROP TABLE inside a transaction block

Peter Eisentraut <e99re41@docs.uu.se> — 2000-03-06T10:10:54Z

On Mon, 6 Mar 2000, Philip Warner wrote:

> >I don't see a problem with disallowing some DDL commands in a transaction
> >as long as they throw an error and the transaction aborts. 
> 
> Is it really necessary to abort the TX? Seems a little antisocial - can't
> you just return an error, and let the user/application decide if it needs
> to abort?

I'm afraid yes, it is necessary. Either the whole transaction or none of
it. Anything else is opening a can of worms that you can't control unless
you have a Ph.D. in fancy databases or something. (Incidentally, I know
that a non-zero amount of people around here have one of those, but that
won't help the rest of us much. :{ )

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] DROP TABLE inside a transaction block

Tom Lane <tgl@sss.pgh.pa.us> — 2000-03-07T07:53:49Z

Mike Mascari <mascarm@mascari.com> writes:
> So, as some sort of compromise, a NOTICE was issued.

It seems everybody but Mike has forgotten the previous go-round on
this issue.  I had in fact put in an ERROR for DROP TABLE inside a
transaction block, and was beat up for it --- on the very reasonable
grounds that it's useful to be able to drop a table and do some other
things inside a transaction.  Although we can't support rollback-ability
for such a transaction right now, we *do* support the atomic nature of
such a transaction.  It's not reasonable to take away a capability that
was available in prior releases just because it's got deficiencies.
So the compromise was to issue a NOTICE instead of an ERROR.

BTW, we are not *that* far from being able to roll back a DROP TABLE.
The only thing that's really needed is for everyone to take a deep
breath and let go of the notion that table files ought to be named
after the tables.  If we named table files after the OIDs of their
tables, then rollback-able DROP or RENAME TABLE would be pretty
straightforward.  If you don't recall why this is, consult the
pghackers archives...

			regards, tom lane

Re: [HACKERS] DROP TABLE inside a transaction block

Tatsuo Ishii <t-ishii@sra.co.jp> — 2000-03-07T08:06:43Z

> It seems everybody but Mike has forgotten the previous go-round on
> this issue.  I had in fact put in an ERROR for DROP TABLE inside a
> transaction block, and was beat up for it --- on the very reasonable
> grounds that it's useful to be able to drop a table and do some other
> things inside a transaction.  Although we can't support rollback-ability
> for such a transaction right now, we *do* support the atomic nature of
> such a transaction.  It's not reasonable to take away a capability that
> was available in prior releases just because it's got deficiencies.
> So the compromise was to issue a NOTICE instead of an ERROR.
> 
> BTW, we are not *that* far from being able to roll back a DROP TABLE.
> The only thing that's really needed is for everyone to take a deep
> breath and let go of the notion that table files ought to be named
> after the tables.  If we named table files after the OIDs of their
> tables, then rollback-able DROP or RENAME TABLE would be pretty
> straightforward.  If you don't recall why this is, consult the
> pghackers archives...

So what was the conclusion for 7.0?

>	Disallow DROP TABLE/DROP INDEX inside a transaction block

We should remove above from HISTORY, no?
--
Tatsuo Ishii

Re: [HACKERS] DROP TABLE inside a transaction block

Tom Lane <tgl@sss.pgh.pa.us> — 2000-03-07T16:47:15Z

Tatsuo Ishii <t-ishii@sra.co.jp> writes:
>> BTW, we are not *that* far from being able to roll back a DROP TABLE.
>> The only thing that's really needed is for everyone to take a deep
>> breath and let go of the notion that table files ought to be named
>> after the tables.  If we named table files after the OIDs of their
>> tables, then rollback-able DROP or RENAME TABLE would be pretty
>> straightforward.  If you don't recall why this is, consult the
>> pghackers archives...

> So what was the conclusion for 7.0?

Too late to consider it for 7.0, I think.  I'd like to see it happen in
7.1 or 7.2 or so.

>> Disallow DROP TABLE/DROP INDEX inside a transaction block

> We should remove above from HISTORY, no?

Yes, it's not correct.

			regards, tom lane

SCHEMA support (was Re: DROP TABLE inside a transaction block)

Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu> — 2000-03-07T19:25:23Z

On Tue, Mar 07, 2000 at 02:53:49AM -0500, Tom Lane wrote:
> Mike Mascari <mascarm@mascari.com> writes:
> > So, as some sort of compromise, a NOTICE was issued.
> 
> 
> BTW, we are not *that* far from being able to roll back a DROP TABLE.
> The only thing that's really needed is for everyone to take a deep
> breath and let go of the notion that table files ought to be named
> after the tables.  If we named table files after the OIDs of their
> tables, then rollback-able DROP or RENAME TABLE would be pretty
> straightforward.  If you don't recall why this is, consult the
> pghackers archives...

Another data point regarding table filenames: I've been looking into
what's needed to support SQL92 schemas.

The standard defines a hierarchy of what are essentially scopes for
database objects. They are (in order from high to low):

Cluster of catalogs -> catalog -> schema -> (DB objects)

"Cluster of catalogs" is defined as (section 4.13):

     Exactly one cluster is associated with an SQL-session and it defines
     the totality of the SQL-data that is available to that SQL-session.

A catalog is (section 4.12):

     Catalogs are named collections of schemas in an SQL-environment. An
     SQL-environment contains zero or more catalogs. A catalog con-
     tains one or more schemas, but always contains a schema named
     INFORMATION_SCHEMA that contains the views and domains of the
     Information Schema.

catalog and schema names show up in the syntax, e.g. the BNF for table
names (section 5.4):

         <table name> ::=
                <qualified name>
         <qualified name> ::=
              [ <schema name> <period> ] <qualified identifier>

         <qualified identifier> ::= <identifier> <schema name> ::=
              [ <catalog name> <period> ] <unqualified schema name>

Which collapses to (using unique names for the various identifiers):

<table name> ::= [ <catalog indentifier> <period> ] [ <schema indentifier>
<period> ]
    <table identifier>

and make a fully qualified column name BNF:

[ <catalog identifier> <period> ] [ <schema identifier> <period> ]
    [ <table identifier> <period> ] <column identifier>

so:
    foo.bar.baz.bongo

is a well formed column identifier for column bongo of table baz in
schema bar in catalog foo.

What's all this mean for pgsql? Well, SCHEMA are an Entry SQL
requirement. So, the syntax: schema.table needs to be supported. Both
schema and catalog define persistent visibilty scopes, and we need to
support identical table names in multiple schema.

I see two possiblities:

1) Map a pgsql database to a SQL schema.

Since we need to support identical table names in multiple schema,
it might be tempting to map a pgsql database to a schema. In fact,
since Entry SQL requires the syntax:

CREATE SCHEMA <schema authorization identifier>

And, in practice, the SCHEMA name seems to be equal to the database user
name, the pgsql default of creating (and accessing) a DB matching the
username implies this mapping.

However, that means we need to solve the one backend accessing multiple
DBs problem. I have a feeling that there may be 'gotchas' in the current
backend code that presume that all the tuples are coming from one DB.

2) Map pgsql DB -> SQL catalog

If we do this, the multiDB access problem can be pushed down the road,
since cross catalog access (<catalog name> in identifiers) is not
even required by Intermediate SQL, only Full SQL. In addition, almost
everything about catalogs is 'implemetation defined' so we get to claim
them as done. ;-)

2a) However, if a single pgsql database is a catalog, then each DB needs
to be able to contain tables in multiple schema, potentially with the
identical table names. One solution would be to do what we do for DBs:
create seperate subdirs for each schema, and put the table files in there.
Changes are probably isolated to the storage manager code, but I haven't
looked in detail.

2b) Another possiblity is what Tom has suggested, to solve the DDL
statements in a transaction problem: use some other unique identifier
for table filenames, perhaps based on OID. Then, supporting schemas
means supporting the syntax in the parser, and that's it, I think. This
would seem to minimize the changes needed to implement this Entry SQL92
requirement.

So, what do y'all think?

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Re: [HACKERS] DROP TABLE inside a transaction block

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-03-07T23:35:35Z

> I see following in HISTORY:
> 
> 	Disallow DROP TABLE/DROP INDEX inside a transaction block
> 
> However, it seems that this is not done with current?
> 
> test=# create table t1(i int);
> CREATE
> test=# begin;
> BEGIN
> test=# drop table t1;
> NOTICE:  Caution: DROP TABLE cannot be rolled back, so don't abort now
> DROP
> test=# end;
> COMMIT
> test=# \d
> No relations found.
> --
> Tatsuo Ishii

OK, seems it is fixed.  I will remove the item.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] DROP TABLE inside a transaction block

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-03-07T23:40:23Z

[Charset ISO-8859-1 unsupported, filtering to ASCII...]
> Tatsuo Ishii writes:
> 
> > I see following in HISTORY:
> > 
> > 	Disallow DROP TABLE/DROP INDEX inside a transaction block
> > 
> > However, it seems that this is not done with current?
> > 
> > test=# create table t1(i int);
> > CREATE
> > test=# begin;
> > BEGIN
> > test=# drop table t1;
> > NOTICE:  Caution: DROP TABLE cannot be rolled back, so don't abort now
> 
> Wow, with all due respect, that's pretty sh^H^Hpoor. That's like saying
> "Haha, either you commit your transaction or your database is fried." Any
> reason that's not an ERROR before anything destructive is done?

I tried it and the ABORT worked, so I have no idea now what is happening
here.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] DROP TABLE inside a transaction block

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-03-08T00:17:33Z

> BTW, we are not *that* far from being able to roll back a DROP TABLE.
> The only thing that's really needed is for everyone to take a deep
> breath and let go of the notion that table files ought to be named
> after the tables.  If we named table files after the OIDs of their
> tables, then rollback-able DROP or RENAME TABLE would be pretty
> straightforward.  If you don't recall why this is, consult the
> pghackers archives...

The oid will be appended to the base file name.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] DROP TABLE inside a transaction block

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-03-08T00:17:51Z

> So what was the conclusion for 7.0?
> 
> >	Disallow DROP TABLE/DROP INDEX inside a transaction block
> 
> We should remove above from HISTORY, no?

Yes removed.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] DROP TABLE inside a transaction block

Tom Lane <tgl@sss.pgh.pa.us> — 2000-03-08T00:55:17Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I tried it and the ABORT worked, so I have no idea now what is happening
> here.

Is the table file still there after the ABORT?  If not, it won't work
for long...

			regards, tom lane

Re: [HACKERS] DROP TABLE inside a transaction block

Tom Lane <tgl@sss.pgh.pa.us> — 2000-03-08T01:13:53Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> BTW, we are not *that* far from being able to roll back a DROP TABLE.
>> The only thing that's really needed is for everyone to take a deep
>> breath and let go of the notion that table files ought to be named
>> after the tables.  If we named table files after the OIDs of their
>> tables, then rollback-able DROP or RENAME TABLE would be pretty
>> straightforward.  If you don't recall why this is, consult the
>> pghackers archives...

> The oid will be appended to the base file name.

If we do it that way, then RENAME TABLE will be kinda complicated...
not impossible, but is it worth it?

			regards, tom lane

Re: [HACKERS] DROP TABLE inside a transaction block

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-03-08T01:48:51Z

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I tried it and the ABORT worked, so I have no idea now what is happening
> > here.
> 
> Is the table file still there after the ABORT?  If not, it won't work
> for long...

Oh, well.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] DROP TABLE inside a transaction block

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-03-08T01:51:34Z

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> BTW, we are not *that* far from being able to roll back a DROP TABLE.
> >> The only thing that's really needed is for everyone to take a deep
> >> breath and let go of the notion that table files ought to be named
> >> after the tables.  If we named table files after the OIDs of their
> >> tables, then rollback-able DROP or RENAME TABLE would be pretty
> >> straightforward.  If you don't recall why this is, consult the
> >> pghackers archives...
> 
> > The oid will be appended to the base file name.
> 
> If we do it that way, then RENAME TABLE will be kinda complicated...
> not impossible, but is it worth it?

100% worth it.  Ingres doesn't use table names in the file name, and
administration is a mess.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] DROP TABLE inside a transaction block

Lamar Owen <lamar.owen@wgcr.org> — 2000-03-08T02:57:36Z

On Tue, 07 Mar 2000, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> BTW, we are not *that* far from being able to roll back a DROP TABLE.
> >> The only thing that's really needed is for everyone to take a deep
> >> breath and let go of the notion that table files ought to be named
> >> after the tables.  If we named table files after the OIDs of their
> >> tables, then rollback-able DROP or RENAME TABLE would be pretty
> >> straightforward.  If you don't recall why this is, consult the
> >> pghackers archives...

> > The oid will be appended to the base file name.

> If we do it that way, then RENAME TABLE will be kinda complicated...
> not impossible, but is it worth it?

You know, I really hate to disagree with Bruce, but, Tom, you have a point.
IMHO, the benefits of having tables named by OID are going to be numerous --
the schema idea included.  Of course, Bruce's concerns are good concerns as
well. What to do......

Why not do this:

Let the tables be named by OID, and only OID.  Then, for admins' convenience,
put in a flat file that is updated periodically, similarly to pg_pwd being a
flat text dump of pg_shadow.  Since there's going to have to be a system
table mapping table names to OID's anyway, a flat dump of said system table
should be similarly done as pg_pwd.  Call it pg_realnames or something.  Have
it have two columns: OID, and pathname (relative to PGDATA) of table.

This would help admins who might want to restore single tables -- as long as
they have a snapshot of pg_realnames at the same time each table is dumped,
then a restore of pg_realnames into a temp dir, then the actual table can be
restored in by its OID (of course, its OID might have changed in the interim,
but you would simply restore it on top of the OID that that table now maps to).

Besides, the OID for the table itself is not likely to change often.

Bruce, would this allay some of your (entirely valid) concerns?  (I read the
thread about this the first time around, when Vadim said the tbale names
_would_ go to OID's.)

Just my two cents.

--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: [HACKERS] DROP TABLE inside a transaction block

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-03-08T04:53:53Z

> > If we do it that way, then RENAME TABLE will be kinda complicated...
> > not impossible, but is it worth it?
> 
> You know, I really hate to disagree with Bruce, but, Tom, you have a point.
> IMHO, the benefits of having tables named by OID are going to be numerous --
> the schema idea included.  Of course, Bruce's concerns are good concerns as
> well. What to do......
> 
> Why not do this:
> 
> Let the tables be named by OID, and only OID.  Then, for admins' convenience,
> put in a flat file that is updated periodically, similarly to pg_pwd being a
> flat text dump of pg_shadow.  Since there's going to have to be a system
> table mapping table names to OID's anyway, a flat dump of said system table
> should be similarly done as pg_pwd.  Call it pg_realnames or something.  Have
> it have two columns: OID, and pathname (relative to PGDATA) of table.
> 
> This would help admins who might want to restore single tables -- as long as
> they have a snapshot of pg_realnames at the same time each table is dumped,
> then a restore of pg_realnames into a temp dir, then the actual table can be
> restored in by its OID (of course, its OID might have changed in the interim,
> but you would simply restore it on top of the OID that that table now maps to).
> 
> Besides, the OID for the table itself is not likely to change often.
> 
> Bruce, would this allay some of your (entirely valid) concerns?  (I read the
> thread about this the first time around, when Vadim said the tbale names
> _would_ go to OID's.)

I will fight this to my death.  :-)

I have cursed Ingres every time I needed to look at the Ingres data
directory to find out which tables match which files.  Even a lookup
file is a pain.  Right now, I can do ls -l to see which tables are
taking disk space.  

Considering the number of times administrators have to do this, it is a
pain.  It is only lazy database internals programmers that would advance
an oid-only file name concept.  FYI, Informix SE uses this the
tablename_oid concept in their implementation.

Keeping that flat file in sync with the database will be a royal pain. 
Imagine the concurrency problems keeping it up to date.

Guess everyone knows where I stand on this one.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] DROP TABLE inside a transaction block

Marc G. Fournier <scrappy@hub.org> — 2000-03-08T05:49:58Z

On Tue, 7 Mar 2000, Lamar Owen wrote:

> Let the tables be named by OID, and only OID.  Then, for admins' convenience,
> put in a flat file that is updated periodically, similarly to pg_pwd being a
> flat text dump of pg_shadow.  Since there's going to have to be a system
> table mapping table names to OID's anyway, a flat dump of said system table
> should be similarly done as pg_pwd.  Call it pg_realnames or something.  Have
> it have two columns: OID, and pathname (relative to PGDATA) of table.

This I would be against ... I personally hate the whole pg_hba.conf,
pg_pwd, etc 'flatfiles' ...

But, could there not be some way of 'extracting extended data' from the
backend?  ie. some sort of \d command that would provide you with
tablename+path+disk size+??

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] DROP TABLE inside a transaction block

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-03-08T05:54:37Z

> > I will fight this to my death.  :-)
> > I have cursed Ingres every time I needed to look at the Ingres data
> > directory to find out which tables match which files.  Even a lookup
> > file is a pain.  Right now, I can do ls -l to see which tables are
> > taking disk space.
> 
> I had Ingres also, and found their scheme to be a royal pain. But that
> was really only because they had such a *bad* schema that I'd have to
> poke around forever to reconstruct a query which would give me file
> names and table names. And then I'd have to print that and compare
> that to the directories which were buried way down in a directory
> tree.
> 
> But with Postgres, we can write a utility to do this for us, so I
> think that it isn't so much of an issue. In fact, perhaps we could
> have a backend function which could do this, so we could query the
> sizes directly.

Does not work if the table was accidentally deleted.  Also requires the
backend to be running.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] DROP TABLE inside a transaction block

Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-03-08T05:55:43Z

> I will fight this to my death.  :-)
> I have cursed Ingres every time I needed to look at the Ingres data
> directory to find out which tables match which files.  Even a lookup
> file is a pain.  Right now, I can do ls -l to see which tables are
> taking disk space.

I had Ingres also, and found their scheme to be a royal pain. But that
was really only because they had such a *bad* schema that I'd have to
poke around forever to reconstruct a query which would give me file
names and table names. And then I'd have to print that and compare
that to the directories which were buried way down in a directory
tree.

But with Postgres, we can write a utility to do this for us, so I
think that it isn't so much of an issue. In fact, perhaps we could
have a backend function which could do this, so we could query the
sizes directly.

                      - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Re: [HACKERS] DROP TABLE inside a transaction block

Philip Warner <pjw@rhyme.com.au> — 2000-03-08T06:05:05Z

At 21:57 7/03/00 -0500, Lamar Owen wrote:
>Let the tables be named by OID, and only OID.  Then, for admins' convenience,
>put in a flat file that is updated periodically, similarly to pg_pwd being a
>flat text dump of pg_shadow.  Since there's going to have to be a system
>table mapping table names to OID's anyway, a flat dump of said system table
>should be similarly done as pg_pwd.  Call it pg_realnames or something.  Have
>it have two columns: OID, and pathname (relative to PGDATA) of table.

For the ignorant, are you able to explain why naming files
'<table_name>_<IOD>' is not acceptable? This seems to satisfy both
requirements (and seemed to be the conclusion of the previous discussion).

I presume I have missed something, and assume there is a good reason for
the '<IOD>' naming convention, so if that is the final choice, would it be
hard to have a file header containing details about the table/index/thing
the the file contains and it's OID. In this way, a future pd_dumpfile
command can tell us what our backed up file '1FA12347.dat' is supposed to
contain?

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

Re: [HACKERS] DROP TABLE inside a transaction block

Chris <chrisb@nimrod.itg.telstra.com.au> — 2000-03-08T06:24:30Z

Bruce Momjian wrote:

> I will fight this to my death.  :-)
> 
> I have cursed Ingres every time I needed to look at the Ingres data
> directory to find out which tables match which files.  Even a lookup
> file is a pain.  Right now, I can do ls -l to see which tables are
> taking disk space.

Assuming a script "tableoid", is..

ls -l `tableoid foobar`

or

tableoid | xargs ls -l

so bad?

Re: [HACKERS] DROP TABLE inside a transaction block

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-03-08T06:41:15Z

> Bruce Momjian wrote:
> 
> > I will fight this to my death.  :-)
> > 
> > I have cursed Ingres every time I needed to look at the Ingres data
> > directory to find out which tables match which files.  Even a lookup
> > file is a pain.  Right now, I can do ls -l to see which tables are
> > taking disk space.
> 
> Assuming a script "tableoid", is..
> 
> ls -l `tableoid foobar`
> 
> or
> 
> tableoid | xargs ls -l

Give me a reason we don't put the table name in the file name?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] DROP TABLE inside a transaction block

Tom Lane <tgl@sss.pgh.pa.us> — 2000-03-08T06:54:52Z

Philip Warner <pjw@rhyme.com.au> writes:
> For the ignorant, are you able to explain why naming files
> '<table_name>_<IOD>' is not acceptable? This seems to satisfy both
> requirements (and seemed to be the conclusion of the previous discussion).

Well, it's pretty simple: consider what has to happen to make RENAME
TABLE be rollback-able.

You clearly have to update the pg_class tuple whose relname field
contains the table name.  That's no problem, because the normal
tuple commit mechanics will take care of making that tuple update
visible or not.

But, in the current implementation, renaming a table also requires
renaming the physical files that hold the table's data --- and last
I checked, Unix filesystems don't know anything about Postgres
transactions.  Our current code renames the files instantly when
the table rename command is done, and there isn't any code for
undoing that rename.  Thus, aborting the xact afterwards fails, because
the pg_class entries revert to their pre-xact values, but the physical
files don't revert to their prior names.

If we change the implementation so that the files are named after
the (fixed, never-changed-after-creation) table OID, then RENAME
TABLE is no problem: it affects *nothing* except the relname field
of the table's pg_class row, and either that row update is committed
or it ain't.

But if the physical file names contain the logical table name, we
have to be prepared to rename those files in sync with the transaction
commit that makes the pg_class update valid.  Quite aside from any
implementation effort involved, the critical point is this: it is
*not possible* to ensure that that collection of changes is atomic.
At best, we can make the window for failure small.

Bruce seems to be willing to accept a window of failure for RENAME
TABLE in order to make database admin easier.  That is very possibly
the right tradeoff --- but it is *not* an open-and-shut decision.
We need to talk about it.

			regards, tom lane

Re: [HACKERS] DROP TABLE inside a transaction block

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-03-08T07:06:00Z

> If we change the implementation so that the files are named after
> the (fixed, never-changed-after-creation) table OID, then RENAME
> TABLE is no problem: it affects *nothing* except the relname field
> of the table's pg_class row, and either that row update is committed
> or it ain't.
> 
> But if the physical file names contain the logical table name, we
> have to be prepared to rename those files in sync with the transaction
> commit that makes the pg_class update valid.  Quite aside from any
> implementation effort involved, the critical point is this: it is
> *not possible* to ensure that that collection of changes is atomic.
> At best, we can make the window for failure small.
> 
> Bruce seems to be willing to accept a window of failure for RENAME
> TABLE in order to make database admin easier.  That is very possibly
> the right tradeoff --- but it is *not* an open-and-shut decision.
> We need to talk about it.

How about creating a hard link during RENAME, and you can just remove
the old link on commit or remove the new link on transaction rollback?

We can register this in the at_exit processing too if you think it is
necessary to clean it up on a backend crash that never gets to an abort,
though I think abort is always called.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] DROP TABLE inside a transaction block

Philip Warner <pjw@rhyme.com.au> — 2000-03-08T07:10:04Z

At 01:54 8/03/00 -0500, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>> For the ignorant, are you able to explain why naming files
>> '<table_name>_<IOD>' is not acceptable? This seems to satisfy both
>> requirements (and seemed to be the conclusion of the previous discussion).
>
>Well, it's pretty simple: consider what has to happen to make RENAME
>TABLE be rollback-able.
...etc

Sorry for the stupid question. I was confusing the previous discussions
over 'DROP COLUMN' with this one, without actually engaging my brain. 

Your response was admirably patient.

FWIW, without a 'storage area' or 'table space' concept, I agree that table
names based on OID's are TWTG.


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

Re: [HACKERS] DROP TABLE inside a transaction block

Mike Mascari <mascarm@mascari.com> — 2000-03-08T08:07:45Z

Can I throw one more question out there on this subject?

There's something that I view as inconsistent behavior with
respect to DDL statements and MVCC and was wondering if this
would have any impact on the discussion (the following is with
6.5.3):

Session #1:

emptoris=> begin;
BEGIN
emptoris=> select * from test;
value
-----
    1
(1 row)

Session #2:

emptoris=> begin;
BEGIN
emptoris=> select * from test;
value
-----
    1
(1 row)

Session #1:

emptoris=> drop table test;
DROP

Session #2:

emptoris=> select * from test;
ERROR:  mdopen: couldn't open test: No such file or directory

Now it would seem to me that if DROP TABLE is going to be
ROLLBACK-able, then Session #2, in a MVCC environment should
never see:

ERROR:  mdopen: couldn't open test: No such file or directory

but it does, because the "effect" of the drop table is an action
that is seen by all sessions, as though it were "committed". So I
am now wondering, are there any
Multi-Versioning/Multi-Generational RDBMS that support
ROLLBACK-able DDL statements in transactions...

Just curious,

Mike Mascari

Re: [HACKERS] DROP TABLE inside a transaction block

Philip Warner <pjw@rhyme.com.au> — 2000-03-08T08:26:19Z

>but it does, because the "effect" of the drop table is an action
>that is seen by all sessions, as though it were "committed". So I
>am now wondering, are there any
>Multi-Versioning/Multi-Generational RDBMS that support
>ROLLBACK-able DDL statements in transactions...
>

Dec/Rdb for one. They do, however, make their lives easier by 'locking the
metadata' when a user does a select. This means the 'drop table' would hang
until the first user commits. I think it even hangs until the first user
exits - basically if they have referenced tha table, you can't touch it
until they exit. But they do allow rollback on all DDL statements.

They do not allow rollback on 'managment' functions like moving storage
areas (where one or more tables are stored) across disks, doing vacuum-like
functions etc.


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

Re: [HACKERS] DROP TABLE inside a transaction block

Tom Lane <tgl@sss.pgh.pa.us> — 2000-03-08T08:31:40Z

Mike Mascari <mascarm@mascari.com> writes:
> Now it would seem to me that if DROP TABLE is going to be
> ROLLBACK-able, then Session #2, in a MVCC environment should
> never see:

> ERROR:  mdopen: couldn't open test: No such file or directory

Check.  We didn't say this worked yet ;-)

			regards, tom lane

Re: [HACKERS] DROP TABLE inside a transaction block

Tom Lane <tgl@sss.pgh.pa.us> — 2000-03-08T08:41:17Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Bruce seems to be willing to accept a window of failure for RENAME
>> TABLE in order to make database admin easier.  That is very possibly
>> the right tradeoff --- but it is *not* an open-and-shut decision.
>> We need to talk about it.

> How about creating a hard link during RENAME, and you can just remove
> the old link on commit or remove the new link on transaction rollback?

Still non-atomic as far as I can see...

			regards, tom lane

RE: [HACKERS] DROP TABLE inside a transaction block

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-03-08T09:40:20Z

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Mike Mascari
> 
> Can I throw one more question out there on this subject?
> 
> There's something that I view as inconsistent behavior with
> respect to DDL statements and MVCC and was wondering if this
> would have any impact on the discussion (the following is with
> 6.5.3):
> 
> Session #1:
> 
> emptoris=> begin;
> BEGIN
> emptoris=> select * from test;
> value
> -----
>     1
> (1 row)
> 
> Session #2:
> 
> emptoris=> begin;
> BEGIN
> emptoris=> select * from test;
> value
> -----
>     1
> (1 row)
> 
> Session #1:
> 
> emptoris=> drop table test;
> DROP
> 
> Session #2:
> 
> emptoris=> select * from test;
> ERROR:  mdopen: couldn't open test: No such file or directory
> 
> Now it would seem to me that if DROP TABLE is going to be
> ROLLBACK-able, then Session #2, in a MVCC environment should
> never see:
> 
> ERROR:  mdopen: couldn't open test: No such file or directory
> 
> but it does, because the "effect" of the drop table is an action
> that is seen by all sessions, as though it were "committed".

The inconsistency is due the current implementation of DROP
TABLE which immediately unlinks the base relation file phisically.
Though the definition(i.e pg_class tuple) of test relation still exits
(logically),the base relation file doesn't exist.
 
PostgreSQL has a standard mechanism of transaction control
for tuples but there's no such mechanism for relation files.
Currently even a single DDL command outside transaction
doesn't have atomicity. I have really disliked this feature(? bug)
for a long time.
Flexible mapping from a relation to the relation file name is
needed in order to enable transaction control for relation files. 

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

RE: [HACKERS] DROP TABLE inside a transaction block

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-03-08T10:12:43Z

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Tom Lane
>
> Philip Warner <pjw@rhyme.com.au> writes:
> > For the ignorant, are you able to explain why naming files
> > '<table_name>_<IOD>' is not acceptable? This seems to satisfy both
> > requirements (and seemed to be the conclusion of the previous
> discussion).
>
> Well, it's pretty simple: consider what has to happen to make RENAME
> TABLE be rollback-able.
>

Is it necessary to get the relation path name from the relation name/oid etc
each time ?
Is it bad to keep the relation path name in pg_class(or another relation) ?
If a new vessel is needed for copy(etc)ing existent tuples we have to
allocate
another unique path name otherwise we can use already allocated file name.
And is it good to dicide the unique path name from oid/relname etc ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: [HACKERS] DROP TABLE inside a transaction block

Mark Hollomon <mhh@nortelnetworks.com> — 2000-03-08T13:05:20Z

Tom Lane wrote:
> 
> 
> If we change the implementation so that the files are named after
> the (fixed, never-changed-after-creation) table OID, then RENAME
> TABLE is no problem: it affects *nothing* except the relname field
> of the table's pg_class row, and either that row update is committed
> or it ain't.
> 
> But if the physical file names contain the logical table name, we
> have to be prepared to rename those files in sync with the transaction
> commit that makes the pg_class update valid.  Quite aside from any
> implementation effort involved, the critical point is this: it is
> *not possible* to ensure that that collection of changes is atomic.
> At best, we can make the window for failure small.
>

How about using hard-links? The transaction that created the change
would see the new link along with the new tuple. other transactions
would see the old directory and the old tuple. rollback drops the new
tuple and the new directory entry. Commit does the obvious.

Does WinNT have something similar to a hard link?
-- 

Mark Hollomon
mhh@nortelnetworks.com
ESN 451-9008 (302)454-9008

Re: [HACKERS] DROP TABLE inside a transaction block

Michael Alan Dorman <mdorman@debian.org> — 2000-03-08T14:04:27Z

pgman@candle.pha.pa.us (Bruce Momjian) writes:
> I have cursed Ingres every time I needed to look at the Ingres data
> directory to find out which tables match which files.  Even a lookup
> file is a pain.  Right now, I can do ls -l to see which tables are
> taking disk space.  
> 
> Considering the number of times administrators have to do this, it is a
> pain.  It is only lazy database internals programmers that would advance
> an oid-only file name concept.  FYI, Informix SE uses this the
> tablename_oid concept in their implementation.
> 
> Keeping that flat file in sync with the database will be a royal pain. 
> Imagine the concurrency problems keeping it up to date.

I'm just a lurker here, and not familiar with postgres internals, so
this suggestion could easily be so ignorant that it's simply not worth
it to even respond.

Caveats aside, it occurs to me that a possible compromise might be to
name tables files by OID, but put them in directories that are named
for the tables themselves.

Bruce has to use du -s rather than ls -l, but he can still achieve the
same end (measuring space used by particular tables), while allowing
the tables to be named by OID, which means that we could get the
benefits that accrue from that.

This actually occured to me when the whole "tablespace" discussion
came up a couple of months ago---by using a different directory for
each table, it would suddenly become very easy to split up a database
across spindles with what would seem to be at least adequate control.

Again, I am sufficiently ignorant of postgres internals---I just read
the list, I haven't really looked at the code---that this is probably
utterly untenable.

Mike.

Re: [HACKERS] DROP TABLE inside a transaction block

Tom Lane <tgl@sss.pgh.pa.us> — 2000-03-08T16:15:56Z

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Is it necessary to get the relation path name from the relation name/oid etc
> each time ?
> Is it bad to keep the relation path name in pg_class(or another relation) ?

Hmm, we could maybe do that for user relations, but it obviously would
not work for pg_class itself.  I'm a little worried about trying to do
it for the other critical system relations, too.  We'd want to keep the
relation's pathname in its relcache entry, so any system relation that
is read while setting up a relcache entry has to have a fixed path that
can be determined without a relcache entry.

Perhaps it would be good enough to say that all system relations live in
the database's primary directory, and only user relations have pathnames
specified in their pg_class entries.  Renaming a system table would be
a Really Bad Idea anyway ;-)

			regards, tom lane

Re: [HACKERS] DROP TABLE inside a transaction block

Marc G. Fournier <scrappy@hub.org> — 2000-03-08T20:12:38Z

On Wed, 8 Mar 2000, Bruce Momjian wrote:

> > But with Postgres, we can write a utility to do this for us, so I
> > think that it isn't so much of an issue. In fact, perhaps we could
> > have a backend function which could do this, so we could query the
> > sizes directly.
> 
> Does not work if the table was accidentally deleted.  Also requires the
> backend to be running.

For ppl that aim ourselves at providing for data integrity, we sure have a
lot of "if the table was accidentally deleted" problems with poor
solutions, no? :)  

IMHO, we are basically supporting ppl *not* doing regular backups of their
data ... most, if not all, of the problems that ppl feel exist that
requires the use of 'flat files', IMHO, aren't big problems if properly
backup procedures are followed ...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] DROP TABLE inside a transaction block

Lamar Owen <lamar.owen@wgcr.org> — 2000-03-08T21:15:40Z

The Hermit Hacker wrote:
> IMHO, we are basically supporting ppl *not* doing regular backups of their
> data ... most, if not all, of the problems that ppl feel exist that
> requires the use of 'flat files', IMHO, aren't big problems if properly
> backup procedures are followed ...

I suggested the 'flat-file' more as a compromise than anything else.
(Although it kindof backfired :-().  Technically speaking, my
'flat-file' is trading the flat-file in the OS's filesystem (the
directory) with a separate flat-file.  Little to no admin difference
from my point of view.

The problem that Bruce is talking about occurs when you try to restore
(from a properly built off-line binary backup) a single table or small
set of tables.  It doesn't have anything to do with supporting people
who won't do proper backups, IMO.

Of course, I personally use on-line pg_dump backups and feed into psql
for on-line restore -- which doesn't require knowing anything about the
underlying filesystem structures.

So, the dichotomy is between those who want to admin at the OS file
level versus those who feel the backend should hide all those details
from the admin.  At least that's how istm.

--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: [HACKERS] DROP TABLE inside a transaction block

Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu> — 2000-03-08T21:19:43Z

On Wed, Mar 08, 2000 at 03:41:17AM -0500, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> Bruce seems to be willing to accept a window of failure for RENAME
> >> TABLE in order to make database admin easier.  That is very possibly
> >> the right tradeoff --- but it is *not* an open-and-shut decision.
> >> We need to talk about it.
> 
> > How about creating a hard link during RENAME, and you can just remove
> > the old link on commit or remove the new link on transaction rollback?
> 
> Still non-atomic as far as I can see...
> 

And there doesn't seem to be an obvious way to extend it to the DROP
TABLE case. Hmm, on second thought, to rollback DROP TABLE we'll need to
'hide' the table from the current transaction: one way would be to rename
it, then do the drop at commit time.

Regardless, since I think there are other, SQL92 standard driven reasons to
break the relname == filename link, I decided to go ahead and see how hard
coding it would be, and how much code might be depending on that behavior.

Looked like it was going to be very simple: the RelationGetRelationName
and RelationGetPhysicalRelationName macros encapsulate access to the
(relation)->rd_rel->relname structure member pretty effectively (thanks
to Bruce's temp. relation work, I presume)

As a first crack, I decided to use the oid for the filename, just because
it simplified the chamges to the Macro, and there was already an oidout()
builtin that'd do the palloc for me ;-)

<some time latter...>

Well, ... it is, as they say, a Small Matter of Programming. I now know
a lot more about the bootstrap process, and the relcache, I can tell you!

Most problems where code that used RelationGetPhysicalRelationName
when they it should use RelationGetRelationName. In several cases,
the code assumed RelationGetPhysicalRelationName handed them a
pointer to rd_rel->relname, which they copy into! I substituted
RelationGetRelationName for all these cases.

There's some uglyness with SharedSystemRelations, as well. I just hacked
in hard coded numbers where ever I found hardcoded relation names, for
an inital test of principle.

I've got a version running, and I can type at a standalone backend:
still some problems with initdb: the pg_log, pg_shadow and pg_user
relations don't get created: I cheated and copied the first two from my
'current' install. That got the backend up, either standalone, or as
postmaster. It'll even accept connections from pgsql: just errors a lot,
since pg_user isn't there!

However, typing at the backend, I can create tables, insert, delete,
start transactions, rollback, etc. Basically, everything works.

Suffice to say, altering the physical storage name is not too difficult,
if _I_ can get this far in just a few hours. Whatever we decide for
'policy' on the name issue (and now that I've generated it, I can tell
you that a directory full of numbers is _really_ ugly) implementation
should go easily.

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Re: [HACKERS] DROP TABLE inside a transaction block

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-03-08T23:24:35Z

> Looked like it was going to be very simple: the RelationGetRelationName
> and RelationGetPhysicalRelationName macros encapsulate access to the
> (relation)->rd_rel->relname structure member pretty effectively (thanks
> to Bruce's temp. relation work, I presume)

Yes.

> As a first crack, I decided to use the oid for the filename, just because
> it simplified the chamges to the Macro, and there was already an oidout()
> builtin that'd do the palloc for me ;-)
> 
> <some time latter...>
> 
> Well, ... it is, as they say, a Small Matter of Programming. I now know
> a lot more about the bootstrap process, and the relcache, I can tell you!
> 
> Most problems where code that used RelationGetPhysicalRelationName
> when they it should use RelationGetRelationName. In several cases,
> the code assumed RelationGetPhysicalRelationName handed them a
> pointer to rd_rel->relname, which they copy into! I substituted
> RelationGetRelationName for all these cases.

Please send in a patch on those if they need to be corrected, OK?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] DROP TABLE inside a transaction block

Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu> — 2000-03-09T00:22:20Z

On Wed, Mar 08, 2000 at 06:24:35PM -0500, Bruce Momjian wrote:
> > Looked like it was going to be very simple: the RelationGetRelationName
> > and RelationGetPhysicalRelationName macros encapsulate access to the
> > (relation)->rd_rel->relname structure member pretty effectively (thanks
> > to Bruce's temp. relation work, I presume)
> 
> Yes.

Well, thank you, then ;-)
> 
> > 
> > Most problems where code that used RelationGetPhysicalRelationName
> > when they it should use RelationGetRelationName. In several cases,
> > the code assumed RelationGetPhysicalRelationName handed them a
> > pointer to rd_rel->relname, which they copy into! I substituted
> > RelationGetRelationName for all these cases.
> 
> Please send in a patch on those if they need to be corrected, OK?
> 

Once I'm sure it's the Right Thing To Do, I will. That's probably
the only clean part of the ugly hack I've done so far.

I've got a complete system up, now. For some reason, the bootstrapping
in initdb doesn't create the pg_log (or pg_shadow!) relations, even
though the same step on a clean CVS tree does. Can't quite find why. So,
the non-bootstrap connections in initdb (creating all the system views)
then fail.  If I manually copy the pg_log and pg_shadow files over from
'current' to 'hacked', I can then run the code from initdb by hand,
and get a fully functional system.

I went ahead and ifdefed out the rename() in renamerel(). Low and behold,
I can rollback an ALTER TABLE RENAME, and have a concurrent session
see the right thing. The conncurent session hangs, though, because of
the exclusive lock. Based on comments in that function, I think the
lock is still needed to handle the buffer cache, which is indexed by
relname. Should probably make that indexed by PhysicalName, since they're
disk buffers, after all. Haven't touched DROP TABLE, yet though.

My real goal with all this is to now look at the parser, and see how
hard it will be to do something with schema. I think that's going to
require an other field in the relation structure, to indicate which
schema a relation belongs to, then control access based on what the
current default schema is. All the relname stuff was just so different
schema can have different tables with the same name. ;-)

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

RE: [HACKERS] DROP TABLE inside a transaction block

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-03-09T00:43:35Z

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Bruce Momjian
> 
> > Looked like it was going to be very simple: the RelationGetRelationName
> > and RelationGetPhysicalRelationName macros encapsulate access to the
> > (relation)->rd_rel->relname structure member pretty effectively (thanks
> > to Bruce's temp. relation work, I presume)
> 
> Yes.
> 
> > As a first crack, I decided to use the oid for the filename, 
> just because
> > it simplified the chamges to the Macro, and there was already 
> an oidout()
> > builtin that'd do the palloc for me ;-)
> >

I object to this proposal.

I have been suspicious why mapping algorithm from relations
to the relation file names is needed for existent relations.
This should be changed first.

And pluaral relation file names are needed for a relation oid/relname.
Why do you prefer fixed mapping oid/relname --> relation file name ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: [HACKERS] DROP TABLE inside a transaction block

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-03-09T02:00:56Z

> > > Most problems where code that used RelationGetPhysicalRelationName
> > > when they it should use RelationGetRelationName. In several cases,
> > > the code assumed RelationGetPhysicalRelationName handed them a
> > > pointer to rd_rel->relname, which they copy into! I substituted
> > > RelationGetRelationName for all these cases.
> > 
> > Please send in a patch on those if they need to be corrected, OK?
> > 
> 
> Once I'm sure it's the Right Thing To Do, I will. That's probably
> the only clean part of the ugly hack I've done so far.

I was just really interested in places where
RelationGetPhysicalRelationName() and RelationGetRelationName() where
called incorrectly.  That can go into 7.0.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026