Re: [PATCHES] [WIP] The shared dependency patch
Rod Taylor <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] [WIP] The shared dependency patch
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 <[EMAIL PROTECTED]> > To: PostgreSQL Patches <[EMAIL PROTECTED]> > 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. -- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] [WIP] The shared dependency patch
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 <[EMAIL PROTECTED]> To: PostgreSQL Patches <[EMAIL PROTECTED]> 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 (<[EMAIL PROTECTED]>) "Cuando no hay humildad las personas se degradan" (A. Christie) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [WIP] The shared dependency patch
Alvaro Herrera <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PATCHES] [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 () "Aprende a avergonzarte más ante ti que ante los demás" (Demócrito) Index: doc/src/sgml/catalogs.sgml === RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/catalogs.sgml,v retrieving revision 2.94 diff -c -r2.94 catalogs.sgml *** doc/src/sgml/catalogs.sgml 13 Dec 2004 18:05:07 - 2.94 --- doc/src/sgml/catalogs.sgml 13 Dec 2004 20:15:32 - *** *** 174,179 --- 174,183 + pg_shdepend + cross-database dependencies between objects + + pg_statistic planner statistics *** *** 3095,3100 --- 3099,3174 + + pg_shdepend + + +pg_shdepend + + + +The shared catalog pg_shdepend records the +dependency relationships between database objects and global objects, +such as users and tablespaces. This information allows DROP +USER and DROP TABLESPACE to ensure that +those objects are unreferenced before attempting to delete them. + + + +pg_depend Columns + + + + + Name + Type + References + Description + + + + + + dbid + oid + pg_database.oid + The OID of the database the dependent object is in + + + + classid + oid + pg_class.oid + The OID of the system catalog the dependent object is in + + + + objid + oid + any OID column + The OID of the specific dependent object + + + + refclassid + oid + pg_class.oid + The OID of the system catalog the referenced object is in + + + + refobjid + oid + any OID column + The OID of the specific referenced object + + + + + + + + pg_statistic Index: src/backend/catalog/Makefile === RCS file: /home/alvherre/cvs/pgsql/src/backend/catalog/Makefile,v retrieving revision 1.53 diff -c -r1.53 Makefile *** src/backend/catalog/Makefile21 Jul 2004 20:34:45 - 1.53 --- src/backend/catalog/Makefile10 Dec 2004 14:21:47 - *** *** 12,18 OBJS = catalog.o dependency.o heap.o index.o indexing.o namespace.o aclchk.o \ pg_aggregate.o pg_constraint.o pg_conversion.o pg_depend.o \ !pg_largeobject.o pg_namespace.o pg_operator.o pg_proc.o pg_type.o BKIFILES = postgres.bki postgres.description --- 12,19 OBJS = catalog.o dependency.o heap.o index.o indexing.o namespace.o aclchk.o \ pg_aggregate.o pg_constraint.o pg_conversion.o pg_depend.o \ !pg_largeobject.o pg_namespace.o pg_operator.o pg_proc.o pg_shdepend.o \ !pg_type.o BKIFILES = postgres.bki postgres.description *** *** 32,38 pg_language.h pg_largeobject.h pg_aggregate.h pg_statistic.h \ pg_rewrite.h pg_trigger.h pg_listener.h pg_description.h pg_cast.h \ pg_namespace.h pg_conversion.h pg_database.h pg_shadow.h pg_group.h \ ! pg_tablespace.h pg_depend.h indexing.h \ ) pg_includes := $(sort -I$(top_srcdir)/src/include -I$(top_builddir)/src/include) --- 33,39 pg_language.h pg_largeobject.h pg_aggregate.h pg_statistic.h \ pg_rewrite.h pg_trigger.h pg_listener.h pg_description.h pg_cast.h \ pg_namespace.h pg_conversion.h pg_database.h pg_shadow.h pg_group.h \ ! pg_tablespace.h pg_depend.h pg_shdepend.h indexing.h \ ) pg_includes := $(sort -I$(top_srcdir)/src/include -I$(top_builddir)/src/include) Index: src/backend/catalog/catalog.c === RCS file: /home/alvherre/cvs/pgsql/src/backend/catal
