> From: Lamar Owen <lamar.owen@wgcr.org>
> On Fri, 26 Nov 1999, Mike Mascari wrote:
> > > Tom Lane <tgl@sss.pgh.pa.us> writes:
> 
> > What does ORACLE do here?
> 
> > > > Since ORACLE has 70% of the RDBMS market, it is the de facto
standard
> > > 
> > > Yes, and Windows is the de facto standard operating system.  I don't
use
> > > Windows, and I'm not willing to follow Oracle's lead when they make a
> > > bad decision...
> 
> > So I guess I should file away my other suggestion to use DCOM as 
> > the object technology of choice instead of CORBA? ;-)
> 
> This is a Free Software project -- PostgreSQL is not bound by the
decisions of
> the 'market leader' any more than Linux is bound by the standards of
Microsoft.

The DCOM remark was just a joke ;-). My remark concerning ORACLE was in
response to Andreas' comment that implicit COMMITs of DDL statements was
absurd. I wanted to simply point out that, since ORACLE has 70% market
share,
most corporate database developers EXPECT their DDL statements to commit
their transactions (if they've RTFM). I also pointed out that it would be
GREAT 
if PostgreSQL could successfully rollback DDL statements sanely (and thus 
diverge from ORACLE). I guess I don't expect that to happen successfully
until 
something the equivalent of TABLESPACES is implemented and there is a 
disassociation between table names, index names and their filesystem 
counterparts and to be able to "undo" filesystem operations. That, it seems
to 
me, will be a major undertaking and not going to happen any time soon...

I'll stop swinging at windmills now...

Mike Mascari



Fri, 26 Nov 1999 15:32:04 -0500
"Mike Mascari" <mascarm@mascari.com>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
On Fri, 26 Nov 1999, Mike Mascari wrote:
> The DCOM remark was just a joke ;-). My remark concerning ORACLE was in
> response to Andreas' comment that implicit COMMITs of DDL statements was
> absurd. I wanted to simply point out that, since ORACLE has 70% market
> share,

I did not see the response to Andreas, nor did I see Andreas' assertion that it
was absurd.  My apologies.

> counterparts and to be able to "undo" filesystem operations. That, it seems
> to 
> me, will be a major undertaking and not going to happen any time soon...

Yes, that is true.  As long as the storage manager relies on the filesystem for
table names, this will be a problem, unless filesystem deletions are delayed
until COMMIT, and filesystem creates are undone at a ROLLBACK.

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

Fri, 26 Nov 1999 16:51:55 -0500
Lamar Owen <lamar.owen@wgcr.org>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
> if PostgreSQL could successfully rollback DDL statements sanely (and thus 
> diverge from ORACLE). I guess I don't expect that to happen successfully
> until 
> something the equivalent of TABLESPACES is implemented and there is a 
> disassociation between table names, index names and their filesystem 
> counterparts and to be able to "undo" filesystem operations. That, it seems
> to 
> me, will be a major undertaking and not going to happen any time soon...

Ingres has table names that don't match on-disk file names, and it is a
pain to administer because you can't figure out what is going on at the
file system level.  Table files have names like AAAHFGE.

-- 
  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, Pennsylvania 19026

Mon, 29 Nov 1999 00:13:59 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Bruce Momjian wrote:
> 
> > if PostgreSQL could successfully rollback DDL statements sanely (and thus
> > diverge from ORACLE). I guess I don't expect that to happen successfully
> > until
> > something the equivalent of TABLESPACES is implemented and there is a
> > disassociation between table names, index names and their filesystem
> > counterparts and to be able to "undo" filesystem operations. That, it seems
> > to
> > me, will be a major undertaking and not going to happen any time soon...
> 
> Ingres has table names that don't match on-disk file names, and it is a
> pain to administer because you can't figure out what is going on at the
> file system level.  Table files have names like AAAHFGE.

I have to say that I'm going to change on-disk database/table/index 
file names to _OID_! This is required by WAL because of inside of 
log records there will be just database/table/index oids, not names, 
and after crash recovery will not be able to read pg_class to get 
database/table/index name using oid ...

Vadim

Mon, 29 Nov 1999 12:59:22 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
> I have to say that I'm going to change on-disk database/table/index 
> file names to _OID_! This is required by WAL because of inside of 
> log records there will be just database/table/index oids, not names, 
> and after crash recovery will not be able to read pg_class to get 
> database/table/index name using oid ...

Wow, that is a major pain.  Anyone else think so?

Using oid's instead of names may give us some ability to fix some other
bugs, though.

-- 
  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, Pennsylvania 19026

Mon, 29 Nov 1999 01:09:41 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Bruce Momjian wrote:
> 
> > I have to say that I'm going to change on-disk database/table/index
> > file names to _OID_! This is required by WAL because of inside of
> > log records there will be just database/table/index oids, not names,
> > and after crash recovery will not be able to read pg_class to get
> > database/table/index name using oid ...
> 
> Wow, that is a major pain.  Anyone else think so?

Why it's so painful? 
We can write utility to construct database dir with table names
symlinked to real table files -:)
Actually, I don't understand 
for what would you need to know what is what, (c) -:)

> Using oid's instead of names may give us some ability to fix some other
> bugs, though.

Yes.

Vadim

Mon, 29 Nov 1999 13:52:25 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
> Bruce Momjian wrote:
> > 
> > > I have to say that I'm going to change on-disk database/table/index
> > > file names to _OID_! This is required by WAL because of inside of
> > > log records there will be just database/table/index oids, not names,
> > > and after crash recovery will not be able to read pg_class to get
> > > database/table/index name using oid ...
> > 
> > Wow, that is a major pain.  Anyone else think so?
> 
> Why it's so painful? 
> We can write utility to construct database dir with table names
> symlinked to real table files -:)
> Actually, I don't understand 
> for what would you need to know what is what, (c) -:)

With Ingres, you can't just look at a file and know the table name, and
if you need to reload just one file from a tape, it is a royal pain to
know which file to bring back.  I have said Ingres make things 100 times
harder for adminstrators by doing this.


-- 
  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, Pennsylvania 19026

Mon, 29 Nov 1999 02:11:57 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> I have to say that I'm going to change on-disk database/table/index 
>> file names to _OID_! This is required by WAL because of inside of 
>> log records there will be just database/table/index oids, not names, 
>> and after crash recovery will not be able to read pg_class to get 
>> database/table/index name using oid ...

> Wow, that is a major pain.  Anyone else think so?
> Using oid's instead of names may give us some ability to fix some other
> bugs, though.

Yes, and yes.  I've been trying to nerve myself to propose that, because
it seems the only reasonable way to make rollback of RENAME TABLE and
DROP TABLE work safely.  It'll be a pain in the neck for debugging and
admin purposes though.

Can we make some sort of usually-correct-but-not-guaranteed-correct
dump that shows which corresponds to what?  Maybe something similar
to the textfile dump of pg_shadow that the postmaster uses for password
authentication?  Then at least you'd have some shot at figuring out
which file was what in extremis...

			regards, tom lane

Mon, 29 Nov 1999 02:33:13 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions 
Bruce Momjian wrote:
> 
> > >
> > > Wow, that is a major pain.  Anyone else think so?
> >
> > Why it's so painful?
> > We can write utility to construct database dir with table names
> > symlinked to real table files -:)
> > Actually, I don't understand
> > for what would you need to know what is what, (c) -:)
> 
> With Ingres, you can't just look at a file and know the table name, and
> if you need to reload just one file from a tape, it is a royal pain to
> know which file to bring back.  I have said Ingres make things 100 times
> harder for adminstrators by doing this.

Moving table file to/off database dir separately is not right way for
backup/restore...

On-line/off-line full backup utility will copy _all_ database files to
_somewhere_ (tape etc) as well as on-line transaction logs
and pg_control (to know when was the last checkpoint made).
And to restore things after disk failure administrator will
have to copy _all_ files + logs (+logs made as incremental backup)
+ pg_control back and start postmaster.

Vadim

Mon, 29 Nov 1999 14:55:15 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Tom Lane wrote:
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> I have to say that I'm going to change on-disk database/table/index
> >> file names to _OID_! This is required by WAL because of inside of
> >> log records there will be just database/table/index oids, not names,
> >> and after crash recovery will not be able to read pg_class to get
> >> database/table/index name using oid ...
> 
> > Wow, that is a major pain.  Anyone else think so?
> > Using oid's instead of names may give us some ability to fix some other
> > bugs, though.
> 
> Yes, and yes.  I've been trying to nerve myself to propose that, because
> it seems the only reasonable way to make rollback of RENAME TABLE and
> DROP TABLE work safely.  It'll be a pain in the neck for debugging and
> admin purposes though.

So, no more nerves needed, Tom, yeh? -:)
It would be nice if someone else, not me, implement this...

> Can we make some sort of usually-correct-but-not-guaranteed-correct
> dump that shows which corresponds to what?  Maybe something similar
> to the textfile dump of pg_shadow that the postmaster uses for password
> authentication?  Then at least you'd have some shot at figuring out
> which file was what in extremis...

As it was proposed - utility to create dir with database name
(in addition to dir with database oid where data really live)
and symlinks there: table_name --> ../db_oid/table_oid

Vadim

Mon, 29 Nov 1999 15:00:44 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Vadim Mikheev <vadim@krs.ru> writes:
> So, no more nerves needed, Tom, yeh? -:)
> It would be nice if someone else, not me, implement this...

Um, I've got more than enough on my plate already...

>> Can we make some sort of usually-correct-but-not-guaranteed-correct
>> dump that shows which corresponds to what?  Maybe something similar
>> to the textfile dump of pg_shadow that the postmaster uses for password
>> authentication?  Then at least you'd have some shot at figuring out
>> which file was what in extremis...

> As it was proposed - utility to create dir with database name
> (in addition to dir with database oid where data really live)
> and symlinks there: table_name --> ../db_oid/table_oid

I saw your message about that after sending mine.  Yes, that'd be
a cool way of displaying the relationship.  But the main thing to
remember is that it'd only be correct at steady-state when nothing
is being changed.  If we tried to guarantee the mapping was correct
100% of the time, we'd be back to square one.  Of course, that
makes the whole thing somewhat less useful for debugging purposes,
since Murphy's Law says that the times you really need to know
what's what are just when the system crashed in the middle of
a table rename ;-)

			regards, tom lane

Mon, 29 Nov 1999 03:10:34 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions 
> > Wow, that is a major pain.  Anyone else think so?
> > Using oid's instead of names may give us some ability to fix some other
> > bugs, though.
> 
> Yes, and yes.  I've been trying to nerve myself to propose that, because
> it seems the only reasonable way to make rollback of RENAME TABLE and
> DROP TABLE work safely.  It'll be a pain in the neck for debugging and
> admin purposes though.

I look at this and question the value of allowing such fancy things vs.
the ability to look at the directory and know exactly what table is
which file.  Maybe we can use file names like 23423_mytable where 24323
is the table oid and mytable is the table name.  That way, we can know
the table, and they are unique too to allow RENAME TABLE to work.

This doesn't solve Vadim's problem.  His additional work would be to
write a line to the log file for each table create/delete saying I
deleted this table with this oid, and when reading back the log, he has
to record the oid_username combination and use that to translate his log
oids into actual filenames.

In fact, doesn't that information already appear in the WAL log as part
of pg_class changes?  Or is the problem that the table changes happen
before the pg_class is committed?


> Can we make some sort of usually-correct-but-not-guaranteed-correct
> dump that shows which corresponds to what?  Maybe something similar
> to the textfile dump of pg_shadow that the postmaster uses for password
> authentication?  Then at least you'd have some shot at figuring out
> which file was what in extremis...

That is OK, and a possible workaround if the above idea is not good.

-- 
  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, Pennsylvania 19026

Mon, 29 Nov 1999 14:08:41 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
> Moving table file to/off database dir separately is not right way for
> backup/restore...
> 
> On-line/off-line full backup utility will copy _all_ database files to
> _somewhere_ (tape etc) as well as on-line transaction logs
> and pg_control (to know when was the last checkpoint made).
> And to restore things after disk failure administrator will
> have to copy _all_ files + logs (+logs made as incremental backup)
> + pg_control back and start postmaster.

No, I am talking about restoring a single table without doing the entire
database.  If you recreate the table empty with the same structure,
shutdown db, mv table restored file to data directory and restart, table
not has old contents.

-- 
  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, Pennsylvania 19026

Mon, 29 Nov 1999 14:10:20 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
> > Can we make some sort of usually-correct-but-not-guaranteed-correct
> > dump that shows which corresponds to what?  Maybe something similar
> > to the textfile dump of pg_shadow that the postmaster uses for password
> > authentication?  Then at least you'd have some shot at figuring out
> > which file was what in extremis...
> 
> As it was proposed - utility to create dir with database name
> (in addition to dir with database oid where data really live)
> and symlinks there: table_name --> ../db_oid/table_oid

That's interesting, but I am concerned about the extra overhead of
creating two links for every file.

The other issue is if the table is accidentally dropped, how do you use
that utility to know the oid of the table that was removed?

I guess I like the OID_tablename idea.

-- 
  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, Pennsylvania 19026

Mon, 29 Nov 1999 14:12:04 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
> As it was proposed - utility to create dir with database name
> (in addition to dir with database oid where data really live)
> and symlinks there: table_name --> ../db_oid/table_oid

In fact, let me change what I suggested.  Instead of 3434_mytable, I
suggest mytable_3434 so that the tables even appear in alphabetical
order in the directory.  The _ may be the wrong character to separate
tablename from oid.  Not sure, but we may need to use something that
can't be used in sql like mytable+234.


-- 
  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, Pennsylvania 19026

Mon, 29 Nov 1999 14:13:36 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Hi all,

I propose here that we stop the release of lock before end of transaction.
I have been suffering from the early release of lock.

Comments ?

If we don't allow DDL command inside transaction block,we won't need
the release before end of transaction.
If we allow DDL command inside transaction block,it may be a problem.
But are there any other principles which could guarantee consistency ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Tue, 30 Nov 1999 09:34:03 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] Re: [GENERAL] drop/rename table and transactions
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> I propose here that we stop the release of lock before end of transaction.
> I have been suffering from the early release of lock.

Now that read and write locks don't interfere with each other, this may
not be as big a performance loss as it sounds.

But, do you intend to apply this principle to system tables as well as
user tables?  I am concerned that we will have deadlock problems if we
try to do it for system tables, because practically all transactions
will start out with system-table accesses, which implies grabbing a read
lock on those system tables if you want to take a hard line about it.
If you later need to upgrade to a higher-grade lock on any of those
system tables, you've got trouble.

There is another issue I've been thinking about that seems to require
some amount of lock-releasing within a transaction, too.  Specifically,
I'd like to see the parser grab a minimal lock (AccessShareLock) on each
table referenced in a query as soon as it recognizes the table name.
The rewriter would also have to lock each table that it adds into the
query due to rules.  This would prevent problems that we have now with
ALTER TABLE running in parallel with parsing/planning of a query.

But, many queries require more than AccessShareLock on their tables.
If we simply try to grab the higher-grade locks without releasing
AccessShareLock, we will certainly suffer deadlock.

If anyone's having a hard time seeing why lock upgrade is dangerous,
consider two backends trying at about the same time to do
	BEGIN; LOCK TABLE foo; etc etc
since this can happen:
	Backend A's parser recognizes 'foo', grabs AccessShareLock on foo
	Backend B's parser recognizes 'foo', grabs AccessShareLock on foo
	Backend A's executor tries to get AccessExclusiveLock on foo,
		must wait for B
	Backend B's executor tries to get AccessExclusiveLock on foo,
		must wait for A

So I think the real solution must go something like this:

* Parser and rewriter grab AccessShareLock on each table as it is added
to the query.
* At start of planner, all tables and required access rights are known.
Release AccessShareLocks, then grab required lock levels on each table.
We probably want to error out if any DDL alteration has actually occurred
to any of the tables by the time we re-acquire its lock.

An easy improvement on this is to avoid the drop/grab if AccessShareLock
is the only thing needed on each table (as in a SELECT).  We could
further try to extend the parser so that it grabs a sufficient lock
on each table initially --- that's probably easy enough for INSERT
target tables and so forth, but we cannot guarantee that it will be
possible in every case.  (Consider rule rewrites that add actions
not present in the initial query.)

Can you see a way to solve this problem without dropping/grabbing locks?

> If we don't allow DDL command inside transaction block,we won't need
> the release before end of transaction.
> If we allow DDL command inside transaction block,it may be a problem.
> But are there any other principles which could guarantee consistency ?

I certainly do not wish to give up the goal of supporting DDL statements
inside transactions.  What problems do you foresee?

			regards, tom lane

Mon, 29 Nov 1999 22:59:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions 
> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Tom Lane
> 
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > I propose here that we stop the release of lock before end of 
> transaction.
> > I have been suffering from the early release of lock.
> 
> Now that read and write locks don't interfere with each other, this may
> not be as big a performance loss as it sounds.
> 
> But, do you intend to apply this principle to system tables as well as
> user tables?  

Yes I said about only system tables.
Isn't an early release of lock for user tables is already a bug ?(except
AccessShareLock). 

> I am concerned that we will have deadlock problems if we
> try to do it for system tables, because practically all transactions
> will start out with system-table accesses, which implies grabbing a read
> lock on those system tables if you want to take a hard line about it.
> If you later need to upgrade to a higher-grade lock on any of those
> system tables, you've got trouble.
>

Sorry,my target is only executor stage this time and AccessShareLock 
is an exception.

As for parser/planner stage,it needs further consideration and I don't
have any solution yet. SPI already has an ability to prepare plans and
executor could use them in other transactions. We have to draw a
clear line between executor and parse/planner. Probably plan invalidation
mecahnism will be needed and we may have to put plans on shared
memory to realize it.

> 
> > If we don't allow DDL command inside transaction block,we won't need
> > the release before end of transaction.
> > If we allow DDL command inside transaction block,it may be a problem.
> > But are there any other principles which could guarantee consistency ?
> 
> I certainly do not wish to give up the goal of supporting DDL statements
> inside transactions.  What problems do you foresee?
>

I may be the first man that threw a question about DDL commands inside
transactions. I'm stiil suspicious about the possibility.
I have thought about the following. I think they should be considered 
even in case of DDL commands *outside* transactions.

1) The biggest obstacle for me is this early release of lock(including 
    parser/planner handling). Without a solution for this I couldn't see
    any consistency for system tuples.
2) The implementation of row level share locking.
3) The naming of relation files which has been discussed in this thread.
4) The lack of read consistency in DDL statement though I couldn't 
    tell concretely what's wrong with it.  

Regards. 

Hiroshi Inoue
Inoue@tpf.co.jp

Tue, 30 Nov 1999 17:17:59 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] Re: [GENERAL] drop/rename table and transactions 
> I propose here that we stop the release of lock before end of transaction.
> I have been suffering from the early release of lock.
> 
> Comments ?

If there's no objection,I would change UnlockRelation() to not release 
the specified lock except AccessShareLock.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Wed, 1 Dec 1999 18:57:42 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Hiroshi Inoue wrote:
> 
> > I propose here that we stop the release of lock before end of transaction.
> > I have been suffering from the early release of lock.
> >
> > Comments ?
> 
> If there's no objection,I would change UnlockRelation() to not release
> the specified lock except AccessShareLock.

Why don't remove this call from improper places?
I would try to find all calls and understand why
they made...

Vadim

Wed, 01 Dec 1999 16:59:58 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions


Vadim Mikheev wrote:

> Hiroshi Inoue wrote:
> >
> > > I propose here that we stop the release of lock before end of transaction.
> > > I have been suffering from the early release of lock.
> > >
> > > Comments ?
> >
> > If there's no objection,I would change UnlockRelation() to not release
> > the specified lock except AccessShareLock.
>
> Why don't remove this call from improper places?
> I would try to find all calls and understand why
> they made...
>

I think UnlockRelation() is unnecessary

Oracle doesn't have

>
> Vadim


Wed, 01 Dec 1999 19:11:25 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Hiroshi Inoue wrote:
> 
> > Why don't remove this call from improper places?
> > I would try to find all calls and understand why
> > they made...
> >
> 
> I think UnlockRelation() is unnecessary
> 
> Oracle doesn't have

And we havn't UNLOCK TABLE _command_ as well -:)
But func call is internal thing and I don't know
Oracle internals.
If this call is unnecessary - get rid of it at all...

Vadim

Wed, 01 Dec 1999 17:28:36 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Oops sorry,I sent a draft by mistake.

Vadim Mikheev wrote:

> Hiroshi Inoue wrote:
> >
> > > I propose here that we stop the release of lock before end of transaction.
> > > I have been suffering from the early release of lock.
> > >
> > > Comments ?
> >
> > If there's no objection,I would change UnlockRelation() to not release
> > the specified lock except AccessShareLock.
>
> Why don't remove this call from improper places?
> I would try to find all calls and understand why
> they made...
>

I was surprized that few people really want DDL commands inside transactions.
Are there any reasons to releasing lock before end of transaction except
that long term lock for system tuples is not preferable ?

I think that UnlockRelation() is unnecessary fundamentally.
Mine is the simplest way to achieve this.
If there's no problem,I am glad to remove UnlockRelation() calls.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp



Wed, 01 Dec 1999 19:37:18 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Hiroshi Inoue wrote:
> 
> > >
> > > If there's no objection,I would change UnlockRelation() to not release
> > > the specified lock except AccessShareLock.
> >
> > Why don't remove this call from improper places?
> > I would try to find all calls and understand why
> > they made...
> >
> 
> I was surprized that few people really want DDL commands inside transactions.
> Are there any reasons to releasing lock before end of transaction except
> that long term lock for system tuples is not preferable ?
> 
> I think that UnlockRelation() is unnecessary fundamentally.
> Mine is the simplest way to achieve this.
> If there's no problem,I am glad to remove UnlockRelation() calls.

There are! I finally found where I used UnlockRelation() -
in execUtils.c:ExecCloseIndices(). Please read comments in
ExecOpenIndices() where LockRelation() is called...

Vadim

Wed, 01 Dec 1999 17:49:47 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Vadim Mikheev wrote:

> Hiroshi Inoue wrote:
> >
> > > >
> > > > If there's no objection,I would change UnlockRelation() to not release
> > > > the specified lock except AccessShareLock.
> > >
> > > Why don't remove this call from improper places?
> > > I would try to find all calls and understand why
> > > they made...
> > >
> >
> > I was surprized that few people really want DDL commands inside transactions.
> > Are there any reasons to releasing lock before end of transaction except
> > that long term lock for system tuples is not preferable ?
> >
> > I think that UnlockRelation() is unnecessary fundamentally.
> > Mine is the simplest way to achieve this.
> > If there's no problem,I am glad to remove UnlockRelation() calls.
>
> There are! I finally found where I used UnlockRelation() -
> in execUtils.c:ExecCloseIndices(). Please read comments in
> ExecOpenIndices() where LockRelation() is called...

I see it now.

Hmm,index itself doesn't have its time qualification and is out of
transaction control(at least now).

OK,I would examine it one by one.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Wed, 01 Dec 1999 21:24:29 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>> I propose here that we stop the release of lock before end of transaction.
>> I have been suffering from the early release of lock.

> If there's no objection,I would change UnlockRelation() to not release 
> the specified lock except AccessShareLock.

After thinking about this some more, I am not convinced that it will buy
anything --- and it might possibly break things that work now.  The
reason I'm not convinced about it is that we cannot apply the "don't
release locks till end of transaction" rule perfectly uniformly.  You
already propose not to treat AccessShareLock that way, and Vadim seems
to think there will be other cases where we need to break the rule.
So we won't have a theoretically-clean situation anyway, and will have
to look at things case by case.

Can you give specific examples of cases that will be fixed?

For the most part I believe that we effectively protect updates to
system-table rows by holding AccessExclusiveLock on the associated user
relation.  Locking the system table is just a means of preventing VACUUM
from running concurrently on the system table (and possibly moving the
tuple we want to update/delete).  So I think releasing the system-table
lock is OK as long as we hold the user table lock till end of
transaction.  VACUUM works fine with uncommitted tuples --- maybe we
should turn off its warning about them, at least in system relations?

There might be places where we are failing to hold user table locks long
enough, but those are just localized bugs and ought to be treated that way.

In any case, I do not think it's a good idea to put such a quick hack
in UnlockRelation().  UnlockRelation() should do what it's told.  If we
want to do this, we should go around and change the heap_close() calls
to specify NoLock instead of whatever locks they specify now.

			regards, tom lane

Wed, 01 Dec 1999 11:15:04 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions 
> -----Original Message-----
> From: owner-pgsql-hackers@postgresql.org
> [mailto:owner-pgsql-hackers@postgresql.org]On Behalf Of Tom Lane
> 
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> >> I propose here that we stop the release of lock before end of 
> transaction.
> >> I have been suffering from the early release of lock.
> 
> > If there's no objection,I would change UnlockRelation() to not release 
> > the specified lock except AccessShareLock.
> 
> After thinking about this some more, I am not convinced that it will buy
> anything --- and it might possibly break things that work now.  The
> reason I'm not convinced about it is that we cannot apply the "don't
> release locks till end of transaction" rule perfectly uniformly.  You

Why are we allowed to break 2 phase locking for system tables ?
Isn't 2 phase locking a fundamental principle to preserve consistency ?
If there's another principle to rely on,please teach me.

> already propose not to treat AccessShareLock that way, and Vadim seems

It seems to me that AccessShare(Exclusive)Lock is essentailly for
VACUUM. There's a possibilty to remove AccessExclusiveLock
except for VACUUM. Oracle has neither.
If AccessExclusiveLock is limited to VACUUM,AccessShareLock 
could be hold until end of transaction.

> to think there will be other cases where we need to break the rule.
> So we won't have a theoretically-clean situation anyway, and will have
> to look at things case by case.
>

OK case by case. I will be glad to check them one by one.
In fact,I have already excluded LockPage() because it is not 
used for transaction control.

But I have thought that the main purpose of early release of lock
is to avoid long term lock for system tables. 
Have I misunderstood until now ?
 
> Can you give specific examples of cases that will be fixed?
>

Unfortunately no example now.
 
> For the most part I believe that we effectively protect updates to
> system-table rows by holding AccessExclusiveLock on the associated user

There are system-table rows which don't have the associated user relations
and there are many DDL commands except VACUUM.
We have to preserve consistency for system tuples among themselves,
don't we ?

> relation.  Locking the system table is just a means of preventing VACUUM
> from running concurrently on the system table (and possibly moving the
> tuple we want to update/delete).  So I think releasing the system-table
> lock is OK as long as we hold the user table lock till end of

This is needed for DDL command inside transactions,isn't it ?
But isn't walking on such a tightrope wasteful in order to realize DDL
command inside transactions either ?

Anyway,I want a decision here.
I have already done a wasteful work in current spec about "can neither
drop nor create" bug. 

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Thu, 2 Dec 1999 09:56:19 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] Re: [GENERAL] drop/rename table and transactions 
> > -----Original Message-----
> > From: owner-pgsql-hackers@postgresql.org
> > [mailto:owner-pgsql-hackers@postgresql.org]On Behalf Of Tom Lane
> >
> > "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > >> I propose here that we stop the release of lock before end of
> > transaction.
> > >> I have been suffering from the early release of lock.
> >
> > > If there's no objection,I would change UnlockRelation() to
> not release
> > > the specified lock except AccessShareLock.
> >
> > After thinking about this some more, I am not convinced that it will buy
> > anything --- and it might possibly break things that work now.  The
> > reason I'm not convinced about it is that we cannot apply the "don't
> > release locks till end of transaction" rule perfectly uniformly.  You
>
> Why are we allowed to break 2 phase locking for system tables ?
> Isn't 2 phase locking a fundamental principle to preserve consistency ?
> If there's another principle to rely on,please teach me.
>
> > already propose not to treat AccessShareLock that way, and Vadim seems
> > to think there will be other cases where we need to break the rule.
> > So we won't have a theoretically-clean situation anyway, and will have
> > to look at things case by case.
> >
>
> OK case by case. I will be glad to check them one by one.

I'm checking them for AccessExclusiveLock now.

As for RowExclusiveLock,it would be much effective to remove
the release of lock unconditionally.
RowExclusiveLock isn't so intensive a lock. For example it
doesn't conflict relatively.  Therefore it would be hard to find
and resolve the bugs which are caused by the early release
of RowExclusiveLock,deadlock etc ...
Holding the lock a little longer won't be so harmful inversely.

Comments ?

As for AccessExclusiveLock I found followings now.

1) commands/user.c(CREATE/DROP/ALTER USER)
    I could create same 2 users easily.
    The lock should be held till end of trancaction.

2) commands/cluster.c(CLUSTER)
    It isn't properly implemented. It seems almost impossible
    to implement CLUSTER command properly in current spec.

3) commands/dbcommands.c(DROP DATABASE)
    elog(ERROR) follows immediately. The release should
    be removed.

4) commands/sequence.c(CREATE SEQUNECE)
    The lock is for the being created (sequence) relation.
    Holding the lock till end of transaction has no problem.

5) commands/vacuum.c(VACUUM)
    The release is caused by new security check. Probably
    the check could be done before acquiring AccessExcl-
    usiveLock.

6) commands/commands.c(ALTER TABLE)
    ALTER TABLE doesn't release AccessExclusiveLock till
    end of transaction.  I couldn't find any reason to allow
    the release for inheritors of a relation class. The release
    should be removed.

7) commands/async.c(LISTEN/UNLISTEN)
    This case seems dangerous too but unfortunately I couldn't
    point out a concrete flaw now.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp






Sat, 4 Dec 1999 00:24:28 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] Re: [GENERAL] drop/rename table and transactions 
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>> OK case by case. I will be glad to check them one by one.

> I'm checking them for AccessExclusiveLock now.

> As for RowExclusiveLock,it would be much effective to remove
> the release of lock unconditionally.
> RowExclusiveLock isn't so intensive a lock. For example it
> doesn't conflict relatively.  Therefore it would be hard to find
> and resolve the bugs which are caused by the early release
> of RowExclusiveLock,deadlock etc ...
> Holding the lock a little longer won't be so harmful inversely.

We could try it and see, certainly.  You are probably right that it
would not be harmful to hold it.

> As for AccessExclusiveLock I found followings now.

> 3) commands/dbcommands.c(DROP DATABASE)
>     elog(ERROR) follows immediately. The release should
>     be removed.

> 5) commands/vacuum.c(VACUUM)
>     The release is caused by new security check. Probably
>     the check could be done before acquiring AccessExcl-
>     usiveLock.

In both of these cases, we are closing the system table unmodified,
and AFAICT the point is just to release the lock a tad sooner than
we otherwise would.  (I coded the VACUUM code just like code I'd seen
elsewhere.)  It's probably harmless either way.

> 6) commands/commands.c(ALTER TABLE)
>     ALTER TABLE doesn't release AccessExclusiveLock till
>     end of transaction.  I couldn't find any reason to allow
>     the release for inheritors of a relation class. The release
>     should be removed.

Yes, the recursive subroutine will grab the same lock and not release
it.  I'm not sure why it's coded the way it is, but certainly there's
no benefit to releasing the lock earlier at the outer level.

> 7) commands/async.c(LISTEN/UNLISTEN)
>     This case seems dangerous too but unfortunately I couldn't
>     point out a concrete flaw now.

Holding the lock on pg_listener longer than absolutely necessary strikes
me as very risky, since any other backend might need to grab the lock
before it can complete its own transaction (in order to send or receive
notifies).  Deadlock could ensue depending on what other locks are held.

I think the locking logic for pg_listener was last revised for 6.4 (by me).
It seems to work fine as-is, but it doesn't know anything about MVCC.
It is possible that we could downgrade the locks to RowExclusiveLock and
rely on MVCC semantics instead of a hard lock.  This would require
careful study however, in particular to be sure that cross-backend
notifies couldn't be missed because of not-yet-committed tuples.
I haven't had time to think about it...

			regards, tom lane

Fri, 03 Dec 1999 18:33:04 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions 
> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Tom Lane
> 
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> >> OK case by case. I will be glad to check them one by one.
> 
> > I'm checking them for AccessExclusiveLock now.
> 
> > 7) commands/async.c(LISTEN/UNLISTEN)
> >     This case seems dangerous too but unfortunately I couldn't
> >     point out a concrete flaw now.
> 
> Holding the lock on pg_listener longer than absolutely necessary strikes
> me as very risky, since any other backend might need to grab the lock
> before it can complete its own transaction (in order to send or receive
> notifies).  Deadlock could ensue depending on what other locks are held.
>

It's difficult for me to find a flaw for this case.
There aren't so many conflicts. For example,LISTEN/UNLISTEN never
conflict relatively because they could be issued only for its own backend.
And as you say,it's very bad to hold the lock till end of transaction in
LISTEN/UNLISTEN.
 
Row level locking in MVCC may allow another(RowExclusiveLock?)
lock instead of AccessExclusiveLock.
I'm not sure now.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp  
 

Sun, 5 Dec 1999 08:45:30 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] Re: [GENERAL] drop/rename table and transactions 


is there a PostgreSQL in win98 or win95?



Tue, 7 Dec 1999 12:16:20 +0800 (PST)
Chris Ian Capon Fiel <ian@xavier.cc.xu.edu.ph>
Postgresql in win9x
Chris Ian Capon Fiel wrote:
> 
> is there a PostgreSQL in win98 or win95?

http://www.postgresql.org/docs/pgsql/doc/README.NT

The NT port will, AFAIK, run on any Win32 implementation, as long as you
have the Cygwin stuff loaded (talked about in the README.NT file....).
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Tue, 07 Dec 1999 16:00:39 -0500
Lamar Owen <lamar.owen@wgcr.org>
Re: Postgresql in win9x