Re: [HACKERS] Scope of constraint names
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: A considerable advantage of per-relation constraint names is that a new unique name can be assigned for a nameless constraint while holding only a lock on the target relation. We'd need a global lock to create unique constraint names in the SQL92 semantics. Presumably, the field pg_class.relchecks already keeps a count of the number of constraints, so it should be possible to assign numbers easily. But pg_class.relchecks is per-relation --- how does it help you assign a globally unique number? After much thought I am coming around to the conclusion that we should name constraints within-schemas (ie, there will be a schema OID column in pg_constraint), but *not require these names to be unique*. DROP CONSTRAINT, SET CONSTRAINTS, etc will act on all constraints matching the target name, as they do now. This will create the minimum risk of breaking existing database schemas, while still allowing us to move some of the way towards SQL compliance --- in particular, SET CONSTRAINTS with a schema-qualified constraint name would work as the spec expects. We would still take care to generate unique-within-a-relation names for nameless constraints, using the same code that exists now, but we'd not enforce this by means of a unique index on pg_constraint. A compromise between that and exact SQL semantics would be to enforce uniqueness of conname + connamespace + conrelid + contypid (the last being a column that links to pg_type for domain constraints; conrelid and contypid are each zero if not relevant). This would have the effect of making relation constraint names unique per-relation, and domain constraint names separately unique per-domain, and also allowing global assertion names that are unique per-schema as in SQL92. This seems a little baroque to me, but maybe it will appeal to others. Comments? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Scope of constraint names
Tom Lane writes: A considerable advantage of per-relation constraint names is that a new unique name can be assigned for a nameless constraint while holding only a lock on the target relation. We'd need a global lock to create unique constraint names in the SQL92 semantics. Presumably, the field pg_class.relchecks already keeps a count of the number of constraints, so it should be possible to assign numbers easily. The only way I can see around that would be to use newoid(), or perhaps a dedicated sequence generator, to construct constraint names. The resulting unpredictable constraint names would be horribly messy to deal with in the regression tests, so I'm not eager to do this. Or we simply assign constraint names explicitly in the regression tests. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Scope of constraint names
and not simply a lock on the pg_constraint table In this context, a lock on pg_constraint *is* global, because it will mean that no one else can be creating an index on some other table. They'd need to hold that same lock to ensure that *their* chosen constraint name is unique. So I am understanding correctly. I think it would be a rare event to have more than one person changing the database structure at the same time. Anyway, the index example is a bad example isn't it? It already takes an lock on pg_class which is just as global. Check constraints and foreign key constraints are two that I can see affected in the manner described. Anyway, my current implementation has constraint names unique to the relation only -- not the namespace, although my locking may be excessive in that area. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Scope of constraint names
Rod Taylor [EMAIL PROTECTED] writes: I think it would be a rare event to have more than one person changing the database structure at the same time. I don't buy this assumption --- consider for example two clients creating temp tables. Anyway, the index example is a bad example isn't it? It already takes an lock on pg_class which is just as global. Au contraire; there is no exclusive lock needed at present. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Scope of constraint names
SQL92 requires named constraints to have names that are unique within their schema. Our past implementation did not require constraint names to be unique at all; as a compromise I suggested requiring constraint names to be unique for any given relation. Rod Taylor's pending pg_constraint patch implements that approach, but I'm beginning to have second thoughts about it. One problem I see is that pg_constraint entries can *only* be associated with relations; so the table has no way to represent constraints associated with domains --- not to mention assertions, which aren't associated with any table at all. I'm in no hurry to try to implement assertions, but domain constraints are definitely interesting. We'd probably have to put domain constraints into a separate table, which is possible but not very attractive. At the SQL level, constraint names seem to be used in only two contexts: DROP CONSTRAINT subcommands of ALTER TABLE and ALTER DOMAIN commands, and SET CONSTRAINTS ... IMMEDIATE/DEFERRED. In the DROP context there's no real need to identify constraints globally, since the associated table or domain name is available, but in SET CONSTRAINTS the syntax doesn't include a table name. Our current implementation of SET CONSTRAINTS changes the behavior of all constraints matching the specified name, which is pretty bogus given the lack of uniqueness. If we don't go over to the SQL92 approach then I think we need some other way of handling SET CONSTRAINTS that allows a more exact specification of the target constraint. A considerable advantage of per-relation constraint names is that a new unique name can be assigned for a nameless constraint while holding only a lock on the target relation. We'd need a global lock to create unique constraint names in the SQL92 semantics. The only way I can see around that would be to use newoid(), or perhaps a dedicated sequence generator, to construct constraint names. The resulting unpredictable constraint names would be horribly messy to deal with in the regression tests, so I'm not eager to do this. Even per-relation uniqueness has some unhappiness: if you have a domain with a named constraint, and you try to use this domain for two columns of a relation, you'll get a constraint name conflict. Inheriting similar constraint names from two different parent relations is also troublesome. We could get around these either by going back to the old no-uniqueness approach, or by being willing to alter constraint names to make them unique (eg, by tacking on _nnn when needed). But this doesn't help SET CONSTRAINTS. At the moment I don't much like any of the alternatives. Ideas anyone? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Scope of constraint names
One problem I see is that pg_constraint entries can *only* be associated with relations; so the table has no way to represent constraints associated with domains --- not to mention assertions, which aren't associated with any table at all. I'm in no hurry to try to implement assertions, but domain constraints are definitely interesting. We'd probably have to put domain constraints into a separate table, which is possible but not very attractive. Hmmm...there must be some sort of schema that can do both in one table? Even something nastly like: refid Oid of relation or domain type 'r' for relation and 'd' for domain ... Our current implementation of SET CONSTRAINTS changes the behavior of all constraints matching the specified name, which is pretty bogus given the lack of uniqueness. If we don't go over to the SQL92 approach then I think we need some other way of handling SET CONSTRAINTS that allows a more exact specification of the target constraint. If we do go over to SQL92, what kind of problems will people have reloading their old schema? Should unnamed be excluded from the uniqueness check...? A considerable advantage of per-relation constraint names is that a new unique name can be assigned for a nameless constraint while holding only a lock on the target relation. We'd need a global lock to create unique constraint names in the SQL92 semantics. Surely adding a foreign key is what you'd call a 'rare' event in a database, occurring once once for millions or queries? Hence, we shouldn't worry about it too much? The only way I can see around that would be to use newoid(), or perhaps a dedicated sequence generator, to construct constraint names. The resulting unpredictable constraint names would be horribly messy to deal with in the regression tests, so I'm not eager to do this. Surely you do the ol' loop and test sort of thing...? Even per-relation uniqueness has some unhappiness: if you have a domain with a named constraint, and you try to use this domain for two columns of a relation, you'll get a constraint name conflict. Inheriting similar constraint names from two different parent relations is also troublesome. We could get around these either by going back to the old no-uniqueness approach, or by being willing to alter constraint names to make them unique (eg, by tacking on _nnn when needed). But this doesn't help SET CONSTRAINTS. At the moment I don't much like any of the alternatives. Ideas anyone? If they're both equally evil, then maybe we should consider going the SQL92 way, for compatibilities sake? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Scope of constraint names
Christopher Kings-Lynne [EMAIL PROTECTED] writes: A considerable advantage of per-relation constraint names is that a new unique name can be assigned for a nameless constraint while holding only a lock on the target relation. We'd need a global lock to create unique constraint names in the SQL92 semantics. Surely adding a foreign key is what you'd call a 'rare' event in a database, occurring once once for millions or queries? Hence, we shouldn't worry about it too much? I don't buy that argument even for foreign keys --- and remember that pg_constraint will also hold entries for CHECK, UNIQUE, and PRIMARY KEY constraints. I don't want to have to take a global lock whenever we create an index. The only way I can see around that would be to use newoid(), or perhaps a dedicated sequence generator, to construct constraint names. The resulting unpredictable constraint names would be horribly messy to deal with in the regression tests, so I'm not eager to do this. Surely you do the ol' loop and test sort of thing...? How is a static 'expected' file going to do loop-and-test? One possible answer to that is to report all unnamed constraints as unnamed in error messages, even though they'd have distinct names internally. I don't much care for that approach though, since it might make it hard for users to figure out which internal name to mention in DROP CONSTRAINT. But it'd keep the expected regression output stable. If they're both equally evil, then maybe we should consider going the SQL92 way, for compatibilities sake? If the spec didn't seem so brain-damaged on this point, I'd be more eager to follow it. I can't see any advantage in the way they chose to do it. But yeah, I'd lean to following the spec, if we can think of a way around the locking and regression testing issues it creates. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Scope of constraint names
I don't buy that argument even for foreign keys --- and remember that pg_constraint will also hold entries for CHECK, UNIQUE, and PRIMARY KEY constraints. I don't want to have to take a global lock whenever we create an index. I don't understand why a global lock is necessary -- and not simply a lock on the pg_constraint table and the relations the constraint is applied to (foreign key locks two, all others one). ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Scope of constraint names
Rod Taylor [EMAIL PROTECTED] writes: I don't want to have to take a global lock whenever we create an index. I don't understand why a global lock is necessary -- To be sure we are creating a unique constraint name. and not simply a lock on the pg_constraint table In this context, a lock on pg_constraint *is* global, because it will mean that no one else can be creating an index on some other table. They'd need to hold that same lock to ensure that *their* chosen constraint name is unique. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Scope of constraint names
One problem I see is that pg_constraint entries can *only* be associated with relations; so the table has no way to represent constraints associated with domains --- not to mention assertions, which aren't It's ugly, but one could make the relid 0, and add a typeid which is non-zero to represent a constraint against a domain. Relation constraints have typeid 0 and relid as a normal number. Obviously I prefer unique constraint names mostly for my users. For some reason they tend to try to make assumptions about a constraint given the name and have been fooled about what the constraint actually is more than once due to 'having seen it before elsewhere'. Is applying a lock on the pg_constraint table really that bad during creation? Sure, you could only make one constraint at a time -- but thats the same with relations, types, and a fair number of other things that are usually created at the same time (or same transaction) as most constraints will be. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
