Thread: [WIP] The shared dependency patch

Hackers,

Here is the current shared dependency patch I promised.  (The new files
are src/include/catalog/pg_shdepend.h and
src/backend/catalog/pg_shdepend.c).

The big problem with the current patch is this:

-- session 1
BEGIN;
DROP USER foo;
-- checks dependencies, all is OK
                -- session 2
                ALTER TABLE foo OWNER TO foo;
COMMIT;

Everything works, a dependency on user foo is recorded, but now it's
useless (it will be never checked).

Of course, there needs to be a lock to protect this from happening.  But
I'm not sure what should be locked.  The whole pg_shadow relation?  That
might be overkill.

I was trying to find out if I could lock the user (and have the ALTER
TABLE get a shared lock on the user before checking its existance, and
the DROP USER get an exclusive lock which would be release at
transaction end.  So everything would remain consistant.)  However the
LOCKTAG does not have provisions to lock arbitrary objects, only
relations (I could end up locking some completely unrelated table, I
guess).

Any ideas on how to handle this?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Aprende a avergonzarte más ante ti que ante los demás" (Demócrito)

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> I was trying to find out if I could lock the user (and have the ALTER
> TABLE get a shared lock on the user before checking its existance, and
> the DROP USER get an exclusive lock which would be release at
> transaction end.  So everything would remain consistant.)  However the
> LOCKTAG does not have provisions to lock arbitrary objects, only
> relations (I could end up locking some completely unrelated table, I
> guess).

IIRC, Rod Taylor did some work on supporting locks for non-table objects
back around the beginning of the year.  We rejected the patch for various
reasons but you might be able to adopt some of it.

Or you could do something like the pg_xactlock hack.  Basically you need
a convention that identifies a LOCKTAG value as locking a particular
user, such that it can't exactly equal any lock on a regular relation.

            regards, tom lane

On Thu, Dec 16, 2004 at 12:46:46PM -0500, Tom Lane wrote:

> IIRC, Rod Taylor did some work on supporting locks for non-table objects
> back around the beginning of the year.  We rejected the patch for various
> reasons but you might be able to adopt some of it.

At the beggining of the past year, you mean?  I found this:

From: Rod Taylor <rbt@rbt.ca>
To: PostgreSQL Patches <pgsql-patches@postgresql.org>
Date: 15 Feb 2003 19:50:46 -0500
Subject: Object (Domain) locking

http://archives.postgresql.org/pgsql-patches/2003-02/msg00093.php

In the archives, I see Bruce's message telling that it was applied, then
it was backed out for untold reasons, and nothing else happenned.

Does anyone remember why the patch was backed out?  A pointer to the
archives would be most helpful.

> Or you could do something like the pg_xactlock hack.  Basically you need
> a convention that identifies a LOCKTAG value as locking a particular
> user, such that it can't exactly equal any lock on a regular relation.

Hmm.  The problem is that I need to lock users, groups and tablespaces,
so a single value won't do.  I could create three special values
(pg_userlock, pg_grouplock, pg_tblspclock?), but at that point it
seems something more general is needed, like maybe Rod's patch.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Cuando no hay humildad las personas se degradan" (A. Christie)

On Fri, 2004-12-17 at 14:48 -0300, Alvaro Herrera wrote:
> On Thu, Dec 16, 2004 at 12:46:46PM -0500, Tom Lane wrote:
>
> > IIRC, Rod Taylor did some work on supporting locks for non-table objects
> > back around the beginning of the year.  We rejected the patch for various
> > reasons but you might be able to adopt some of it.
>
> At the beggining of the past year, you mean?  I found this:
>
> From: Rod Taylor <rbt@rbt.ca>
> To: PostgreSQL Patches <pgsql-patches@postgresql.org>
> Date: 15 Feb 2003 19:50:46 -0500
> Subject: Object (Domain) locking
>
> http://archives.postgresql.org/pgsql-patches/2003-02/msg00093.php
>
> In the archives, I see Bruce's message telling that it was applied, then
> it was backed out for untold reasons, and nothing else happenned.
>
> Does anyone remember why the patch was backed out?  A pointer to the
> archives would be most helpful.

In order to prevent the ability for a domain, schema, etc. to be dropped
while another process was using it, we were going to need to take a
large number of new locks for nearly everything done (simple select
statements included). I.e. Locks on all data types used in a simple
select.

I don't remember if there was a specific reason given, but I've presumed
it was due to the above and the resulting performance hit.

> > Or you could do something like the pg_xactlock hack.  Basically you need
> > a convention that identifies a LOCKTAG value as locking a particular
> > user, such that it can't exactly equal any lock on a regular relation.
>
> Hmm.  The problem is that I need to lock users, groups and tablespaces,
> so a single value won't do.  I could create three special values
> (pg_userlock, pg_grouplock, pg_tblspclock?), but at that point it
> seems something more general is needed, like maybe Rod's patch.
--

Rod Taylor <pg@rbt.ca> writes:
> On Fri, 2004-12-17 at 14:48 -0300, Alvaro Herrera wrote:
>> Does anyone remember why the patch was backed out?  A pointer to the
>> archives would be most helpful.

> In order to prevent the ability for a domain, schema, etc. to be dropped
> while another process was using it, we were going to need to take a
> large number of new locks for nearly everything done (simple select
> statements included). I.e. Locks on all data types used in a simple
> select.

My recollection is that the patch either did, or intended to ultimately
do, locking on every single database object referenced by every query ---
datatypes, operators, functions, operator classes, schemas, you name it.
In an academic sense that's probably a good idea but the cost/benefit
ratio seemed much too high to me, and still does.  What we actually need
in that area, I think, is a mechanism for invalidation and replanning of
cached query plans, which is not the same thing at all.

AFAICS locks on user/group IDs would only need to be taken in CREATE and
GRANT operations (other than CREATE/DROP USER/GROUP themselves), so the
locking costs should be far lower than what Rod was pointing towards
doing.

This doesn't necessarily affect the design of LOCKTAGs, however, only
what objects we actually choose to apply locking to.

            regards, tom lane