Re: [HACKERS] GRANT role_name TO role_name ON database_name
On Wed, May 29, 2013, at 04:26 AM, Albe Laurenz wrote: Clark C. Evans wrote: I'd really love the ability to grant a *user* role-based privileges database by database. The only cluster-wide role permissions are the options SUPERUSER, CREATEDB, CREATEROLE, INHERIT, LOGIN and REPLICATION. Incorrect; role-to-role membership (different from INHERIT) is also a cluster-wide role permission. Hence, I have no way to assign a user auditor role in one database, and not grant that same user auditor role in another database. All object privileges of a role are limited to a certain database. This is indirectly false, since role membership is cluster wide. That is, a cluster-wide change in role membership cascades to a change in object permissions. By granting user Tom the Auditor role for the Sales database, I also have granted him Auditor role for the HR database. The work around we use is to mangle the roles in our system to prefix them by the database; e.g. Sales_Auditor. However, this process is very bothersome and error prone when you hit dozens of databases in a cluster. The other work around is to only use one database per cluster, but that seems silly to me. Why can't you use a role auditor and give it different permissions in different databases? Because the role defines the expected set of permissions. If someone has an auditor role, they should probably have the database object permissions to see the audit tables, etc. My schema are largely the same; differing mostly for administrative purposes. My applications use roles to define and limit access. I apologize for posting to -hackers; it was probably the wrong list. That said, I won't follow up till next year since I know everyone is super busy and this probably isn't high on anyone's list. Best, Clark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GRANT role_name TO role_name ON database_name
On Wed, May 29, 2013, at 09:45 AM, Stephen Frost wrote: * Albe Laurenz (laurenz.a...@wien.gv.at) wrote: Maybe the db_user_namespace parameter can help: http://www.postgresql.org/docs/9.2/static/runtime-config-connection.html#GUC-DB-USER-NAMESPACE I doubt it and I wouldn't encourage anyone to use it even if it happened to help in this situation.. This feature won't help me, and I'd concur with Stephen that I wouldn't encourage its use.Auto-magical name-mangling sounds suspiciously like an application feature. The major problem isn't prefixing - you can do that in application logic easy enough. The harder problem is that this convention would have to be respected by dump/restore and create database from template. So, the application role auditor@sales would be dumped in a serialization of the sales database; and, when restored into sales-testing would become auditor@sales-testing. Speaking of which, the choice of a @ delimiter is unfortunate, since email addresses (authenticated by Mozilla Persona) make lovely user names. If a delimiter is used for name mangling, I'd lobby for a character that is an unwise RFC2396 character and not a reserved RFC3986 character. So, perhaps the PIPE (|) or caret (^) would be good choices since those can be percent-encoded in valid emails, and don't have assigned meanings as a standard URI. Best, Clark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GRANT role_name TO role_name ON database_name
On Wed, May 29, 2013, at 10:08 AM, Stephen Frost wrote: This capability might well come with a real way to have per-database roles in general, which has been asked for quite often as well. You would then be able to have an 'auditor' role in each database and have them actually be different roles- would that match your needs..? Yes, if we had per-database roles, it would work. However, I don't think it's necessary. We've already got role permissions specific to a database; so we're most of the way there. The main piece missing is a way for me to assign a role to a user, but only for a specific database. Let me rephrase this, using a different syntax... CAST user AS role ON database This statement permits the user to execute SET ROLE role when they are attached to database. The user doesn't inherit from the role, it's only a permission that enables them to SET ROLE and only when attached to the permitted database. I think this would solve my problem. Suppose again I've got a database cluster with a sales and an hr database. In this database cluster, I've got an auditor role which can read the audit_table in the respective database. Now, suppose I wish for Tom to be an auditor for Sales, and not for HR. I'd issue CAST tom AS auditor ON sales. When Tom normally joins the database, he wouldn't see auditor tables (since this CAST isn't really role inheritance). But, if he wanted to see them, and he were in the Sales database, he'd issue: SET ROLE auditor. Then he could query audit_table. On other hand, just because Tom joined the HR database to enter his timeslips, he'd not have access to audit_table; and if he did a SET ROLE auditor it'd fail to escalate his permission. I hope this makes sense and that it might be general enough. Best, Clark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] GRANT role_name TO role_name ON database_name
I'd really love the ability to grant a *user* role-based privileges database by database. For background, I have several databases running in a single cluster, one database per business unit. Each database has the same core schema with the same basic role permissions, but with significant customizations. Even if it were technically possible to make them a single database, it would be unwise for administrative reasons. Each user may have access to any number of databases, but, within each database may be assigned to different roles. For example, we may have an 'auditor' role which gives specific access to some trigger-maintained change history. But, a given user may only be an auditor for the business units they are assigned. That said, they may have other roles in other business units. My requirements are very fluid here and dictated by regulatory requirements. Currently, we work around the lack of per-database role permissions by prefixing roles with the name of the database. This is quite tedious though, it requires specialized logic to overlay creation, backups, restores, updating and deleting databases. It's very irritating, requires custom code and conventions, even though it works. About 5 years ago, I think I asked for roles to become database specific. I know think that is a bit draconian given the cluster-wide permission structure used by PostgreSQL. However, perhaps a way to make it optionally limited to a given database would simplify my permission tracking? Best, Clark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is there really no interest in SQL Standard?
On Monday, September 19, 2011 9:20 AM, David Fetter da...@fetter.org wrote: On Mon, Sep 19, 2011 at 10:58:49AM -0400, Joe Abbate wrote: On 09/19/2011 09:50 AM, Josh Berkus wrote: FWIW, the fact that the drafts *are* confidential is symptomatic of everything which is wrong with the ISO. Maybe it's time for an open source SQL standard, one not controlled by the big guys and their IP claims. That's probably not a bad idea. The down side is that it'll be the work of decades, not years, to get this thing going. If anyone wants to start on something like this, I think it could start as a rigorous review of PostgreSQL semantics. On Monday, September 19, 2011 4:44 PM, Greg Smith g...@2ndquadrant.com wrote: Not spending as much time sitting in meetings and fighting with other vendors is one of the competitive advantages PostgreSQL development has vs. the big guys. There needs to be a pretty serious problem with your process before adding bureaucracy to it is anything but a backwards move. And standardization tends to attract lots of paperwork... Perhaps focusing only on PostgreSQL semantics and edge cases is also where the effort should stop. I'm not offering to do this. I think this work would only be really valuable if it significantly improved the already excellent documentation and regression tests -- ie, provides direct user value. Best, Clark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
On Sat, 13 Nov 2010 17:23 +0200, Marko Tiikkaja wrote: So these queries would behave differently? WITH t AS (DELETE FROM foo RETURNING *) SELECT 1 WHERE false; WITH t AS (DELETE FROM foo RETURNING *) SELECT 1 FROM t LIMIT 0; I'm still trying to wrap my head around this new mechanism. What would this return? UPDATE foo SET access = 0; WITH t AS (UPDATE foo SET access = access + 1 RETURNING *) SELECT x.access, y.access FROM t CROSS JOIN t; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Role incompatibilities
Sorry to ressurect this thread. However, I've been playing with the new role system and I'd prefer to keep CURRENT_USER as the login user, and not making it a synonymn for CURRENT_ROLE. In my application, I love the ability to shed privleges by SET ROLE dataentry;. However, I need CURRENT_USER to remain as 'clark' for audit trail triggers (recording that 'dataentry' changed a particular order is kinda useless). I have a related information_schema question. Tom said that I could probably use login or inherit to determine which 'roles' are users, and which are really roles. Is this still the advice? That said, shouldn't PostgreSQL just call this mixed-thingy an 'authority' to reduce confusion. Then role-is-authority and user-is-authority. Probably too late, but, just in case it is still changable... My deeper question is... from the information_schema, is it possible (both in theory via definition, and in pratice via implementation) to obtain two things: (a) the roles to which I can do SET ROLE with, I guess this is my granted roles? (b) the roles to which I currently am using for my permission(s), or simply, the role inherit graph and my current role Thanks for your time, Clark P.S. There isn't a way to list all roles from the information_schema, except via DISTINCT on a table that refers to them? On Mon, Apr 10, 2006 at 03:41:59PM -0400, Bruce Momjian wrote: | | Is there a TODO here? | | --- | | Peter Eisentraut wrote: | Am Samstag, 25. M?rz 2006 16:10 schrieb Tom Lane: | No, the current implementation is a compromise between exact standards | compatibility and backwards compatibility with our historical groups | behavior. I'm not really prepared to toss the latter overboard. | | My two major sticking points here are the SET ROLE command and the noinherit | feature. The SET ROLE command is not required by our historical group | behavior (because we didn't have it before) and does not do what the SQL | standard says it should do. The noinherit feature is not required by the | historical group behavior (because groups are yes-inherit) and is not in the | SQL standard either. So these two features were just mistakes as far as I | can tell. | | I'm not passing judgement on whether a command like the currently implemented | SET ROLE command or a feature like the currently implemented noinherit | feature is useful. They are just not in line with either the historical | group behavior or the SQL standard. | | -- | Peter Eisentraut | http://developer.postgresql.org/~petere/ | | ---(end of broadcast)--- | TIP 1: 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 | | | -- | Bruce Momjian http://candle.pha.pa.us | EnterpriseDBhttp://www.enterprisedb.com | | + If your life is a hard drive, Christ can be your backup. + | | ---(end of broadcast)--- | TIP 3: Have you checked our extensive FAQ? | |http://www.postgresql.org/docs/faq | ---(end of broadcast)--- TIP 1: 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] constraints and sql92 information_schema compliance
On Tue, Mar 14, 2006 at 08:14:12PM -0800, Stephan Szabo wrote: | CREATE TABLE x (y text, z text, PRIMARY KEY(y,z)); | CREATE TABLE a (b text, c text); | ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y); ... | I assert the problem here is that the FOREIGN KEY constraint | construction should have *failed* since the *tuple* (z,y) | does not infact match any unique key in table x. | | Looking at this more, I'm not sure that making it match the unique key | exactly helps information_schema.constraint_column_usage at least. My problem is that the column order can be provided in the reference clause in a way that does *not* match a canidate key: in the example above, there isn't a primary key nor a unique key index on (z,y). | create table ta(a int, b int, primary key(a,b)); | create table tb(a int, b int, foreign key (a,b) references ta); | create table tc(a int, b int, foreign key (b,a) references ta); When reference column list is omitted, it implies that the primary key of the referenced table is used; hence, these are equivalent to: create table tb(a int, b int, foreign key (a,b) references ta (a,b)); create table tc(a int, b int, foreign key (b,a) references ta (a,b)); | I don't see how you can differentiate the foreign keys in the last two | without a position column, which doesn't seem to be in at least our | current view (although I haven't checked 2003 to see if they changed it). | Both of those should be valid, although the second is wierd. There isn't a problem with the examples you provided, although the resulting join isn't what the user intended. I think the ability to omit the reference column list is a bad idea; but alias, it is quite different from the problem I'm reporting. Very Best, Clark ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] constraints and sql92 information_schema compliance
On Tue, Mar 14, 2006 at 10:01:16PM -0800, Stephan Szabo wrote:
| The point is that because rows in a table don't have order (unless
| information_schema has special rules) the two constraints above seem to
| look the same to me in their representation in
| information_schema.constraint_column_usage. If that's true then forcing
| the referenced columns to match exactly doesn't actually fix the problem
| with the representation in infomration schema. The same ambiguity exists.
Actually, there is no ambiguity; it's just that constraint_column_usage
view is absolutely useless. You want to be using key_column_usage.
--
-- Create the test tables, taking particular care to name the
-- constraints so that they are unique within the schema.
--
create table ta(a int, b int);
alter table ta add constraint ta_pk primary key (a,b);
create table tb(a int, b int);
alter table tb add constraint tb_ta_fk foreign key (a,b) references ta;
create table tc(a int, b int);
alter table tc add constraint tc_ta_fk foreign key (b,a) references ta;
--
-- Return the pairing between the foreign-key column, and
-- the canidate-key columns they refer to.
--
SELECT fk.table_name AS fk_table, fk.column_name AS fk_column,
uk.table_name AS uk_table, uk.column_name AS uk_column
FROM
( SELECT c.constraint_schema, c.constraint_name,
c.table_schema, c.table_name,
u.column_name, u.ordinal_position
FROM information_schema.table_constraints c
JOIN information_schema.key_column_usage u ON (
u.constraint_schema = c.constraint_schema
AND u.constraint_name = c.constraint_name
AND u.table_schema = c.table_schema
AND u.table_name = c.table_name)
WHERE c.constraint_type in ('UNIQUE', 'PRIMARY KEY')
) AS uk,
( SELECT c.unique_constraint_schema, c.unique_constraint_name,
u.table_schema, u.table_name,
c.constraint_schema, c.constraint_name,
u.column_name, u.ordinal_position
FROM information_schema.referential_constraints c
JOIN information_schema.key_column_usage u ON (
c.constraint_schema = u.constraint_schema
AND c.constraint_name = u.constraint_name )
) AS fk
WHERE uk.constraint_schema = fk.unique_constraint_schema
AND uk.constraint_name = fk.unique_constraint_name
AND uk.ordinal_position = fk.ordinal_position
ORDER BY fk.table_name, fk.ordinal_position;
I hope this helps! (and that it's even remotely correct)
Best,
Clark
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] constraints and sql92 information_schema compliance
On Tue, Mar 14, 2006 at 11:11:29PM -0800, Stephan Szabo wrote: | When we're allowing other order access, immediately reorder the | constraint information to match the primary key order. Let me try to parrot. In PostgreSQL, the pairing information between the foreign-key and unique-key constraint is available; even though it isn't represented in the information_schema. Hence, this option re-orders the foreign-key columns to match the corresponding canidate key constraint (unique _or_ foreign key). If so, I like it. | This helps out | with IS since the loaded constraint should display properly, but | theoretically could change the visual representation after load for people | who don't care about this option. I doubt that the actual ordering of the columns in the foreign key constraint matters to people; so I don't see a downside with this option other than the perhaps unexpected difference. | Change the representation unconditionally on dump. Basically reorder the | constraint at dump time to always generate a dump in SQL03 order. This has | the same downside as the above except only after another dump/restore. You could do both? | Change the representation on dump only if the flag is set (probably | exporting this as an option to pg_dump as well). This could be a little | more difficult to use, but pretty much causes the user to drive the | choice. Hmm. I just cannot think of a reason why someone would even care that the columns in their foreign-key constraint create get re-orderd as long as the behavior is the same. Best, Clark ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] constraints and sql92 information_schema compliance
On Mon, Feb 27, 2006 at 11:39:30AM -0500, Tom Lane wrote: | Josh Berkus josh@agliodbs.com writes: | No way. The entire point of information_schema is that it is standard; | adding non-spec things to it renders it no better than direct access | to the PG catalogs. | | Hmmm ... so, per you, we can't add extra views covering non-spec | objects to the information_schema (like aggregates) because we | can't modify it in any way. But per Peter we can't add new | views to the pg_catalog because we want people to use | information_schema. I sense a catch-22 here. | I doubt Peter really meant that we can't add any new views; in | particular, for information that is not available from the standard | information_schema it's certainly silly to claim that people should go | to information_schema for it. I do see his point that we shouldn't | unnecessarily duplicate functionality that's available in a standardized | view. If my opinion is worth anything here, nothing should go in the information_schema unless is is specified in one of the SQL1992, SQL1999, or SQL2003 specifications. According to my objectives, if it isn't in the information_schema, I should not be using it. I've been using information_schema reflectively, and would have been confused to see anything in there that wasn't in the specs. | I do have doubts about adding any large number of add-on views to | pg_catalog, because of the privileged place of that schema in search | paths. It'd be better to put them in a separate schema (pg_info | maybe?) where they'd pose less risk of conflicts with user-defined names. | Does newsysviews already do this? A separate pg_info probably would not hurt, I suppose. Best, Clark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sat, Feb 25, 2006 at 10:52:48PM -0800, Stephan Szabo wrote: |* Forbidding the creation of a foreign key constraint where | the column list for the referenced table doesn't *exactly* | match a canidate key on that table. | | About the best we're likely to be able to do is change pg_dump to dump | it in the 03 order and possibly give an optional way to turn on an exact | check (default off) for the next version, probably changing the default | 1 or 2 versions after that. Excellent. |* Issue a warning when creating a constraint who's name is | not unique within its (the constraint's) schema. | | I don't have a problem with it (once, I argued for following the spec | constraint on this way back when), however I think this was proposed and | rejected before as excess noise. You might want to look back through the | archives. I think the problem /w the noise was that default trigger names were not automatically prefixed with the table name. I'd like to see this warning; perhaps in the next release, the ``dump`` module can rename constraints like $1 and $2 to include the table name? Given that both of these issues consist of first changing the dumper and making an optional warning (at first) and then turning it into an error way down the line, could they be considered part of the same ticket? On Sat, Feb 25, 2006 at 10:52:48PM -0800, Stephan Szabo wrote: | Personally, I disagree with the 03 requirement and think that it's more an | example of them misdesigning the information schema, but we should | probably move in that direction for compatibility with more recent | versions of spec. Agreed; there is no reason why the information_schema could not have been improved to add an additional view to provide this information. On Sun, Feb 26, 2006 at 12:57:13PM -0500, Tom Lane wrote: | This thread is fairly interesting since we appear to be watching the SQL | committee allowing a brain-dead choice in the initial information_schema | design to force a non-backwards-compatible dumbing-down of the main spec. I'm glad that this thread has not been an undue burden; I'm greatful for your help (as I'm writing an application that is attempting to follow the specification as closely as possible). Best, Clark ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sat, Feb 25, 2006 at 11:51:55AM -0800, Josh Berkus wrote: | This has been discussed previously in a couple of threads. I believe the | desire is to make it work as specified in SQL-2003, but I do not remember | whether or not anyone volunteered to do the work to make it happen. | | I believe that the newsysviews follow the SQL03 permissions structure. Fantastic! The SQL92 permission structure was braindead. After some time working with the information schema, I have three suggestions: * for foreign-key and check constraints, the default names are $1, $2, etc.; it would be great if they were upgraded to use the default names given by primary and unique key constraints: table_uk_1stcol, table_pk - the problem with $1 is that they arn't unique across tables, and hence won't work /w information_schema nicely unless you manually name the constraints * when creating a foreign key constraint on two columns, say from A (x, y) to B (x, y), if the unique index on B is (x,y) you can make a foreign key from A-B using (y,x) - this might seem correct, but it makes it impossible to deterine from the information schema which columns to join on -- and you might infer the wrong relation ie, FROM A JOIN B ON (b.x = a.y and b.y = a.x) * it would be great to add a warning if a constraint is not unique within its schema (obviously, making it an error is a bad idea) I think with these changes no updates to the views are necessary; and compliance with the information_schema is more or less automatic unless someone is ignoring the warnings. Kind Regards, Clark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sat, Feb 25, 2006 at 12:51:51PM -0800, Stephan Szabo wrote: | * for foreign-key and check constraints, the default names | are $1, $2, etc.; it would be great if they were upgraded | to use the default names given by primary and unique key | constraints: table_uk_1stcol, table_pk | | Err... what version are you using? I get constraint names like tt_a_fkey | from devel, and I thought at least 8.1 does the same. 7.4.8, so it's a bit old -- glad to hear this made it! | * when creating a foreign key constraint on two columns, say | from A (x, y) to B (x, y), if the unique index on B is (x,y) | you can make a foreign key from A-B using (y,x) | | I don't understand which particular case you're complaining about, but as | far as I can see, we have to allow that case by the rest of the spec. To be clear, I'm talking about... CREATE TABLE x (y text, z text, PRIMARY KEY(y,z)); CREATE TABLE a (b text, c text); ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y); For this case, the information schema details: 1. the foreign key constraint as a reference to the primary key constraint and lists the tuple (b,c) 2. the primary key constraint lists the keys (y,z) In particular, the column ordering (z, y) in the reference clause is *lost*. Hence, if you were to blindly reconstruct a join critiera from the information schema, you'd wrongly assume that useful join critiera is: ON (a.b == x.y AND a.c == x.z) when the correct join critiera should be: ON (a.b == x.z AND a.c == x.y) I assert the problem here is that the FOREIGN KEY constraint construction should have *failed* since the *tuple* (z,y) does not infact match any unique key in table x. | If A(x) is comparable to B(x) and B(y) and A(y) is comparable to B(x) | and B(y), all of A(x,y)-B(x,y), A(y,x)-B(x,y), A(x,y)-B(y,x) and | A(y,x)-B(y,x) seem to be allowed by the definition in the constraint | section (as only the sets must be equal, with no mention of ordering). Ordering of tuples (fields within a row object) are significant in SQL; and hence the two above are not comparable. | The sets in this case being the referenced columns and the unique | columns in the unique constraint. Not sure I get this; sorry about being so obscure in my first email. I hope this one clarifies the problem. Cheers, Clark ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sat, Feb 25, 2006 at 04:50:41PM -0500, Rod Taylor wrote:
| Can out point it out in the below psql output?
|
| k=# CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
| NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index x_pkey
| for table x
| CREATE TABLE
| k=# CREATE TABLE a (b text, c text);
| CREATE TABLE
| k=# ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
| ALTER TABLE
- this alter _should_ fail, since there isn't a canidate key
on the table x matching (z, y)
| x_pkey PRIMARY KEY, btree (y, z)
| a_b_fkey FOREIGN KEY (b, c) REFERENCES x(z, y)
The problem is that PostgreSQL is maintaining information that is/should
not be available to an SQL processor: the ordering of the _referenced_
columns. That a_b_fkey happens to reference (z, y) is not available in
the SQL INFORMATION_SCHEMA, and thus should not be used to interpret
standard SQL statements affected by the foreign key constraint.
| k=# insert into x values ('foo', 'bar');
| INSERT 0 1
| k=# insert into a values ('foo', 'bar');
| ERROR: insert or update on table a violates foreign key constraint
| a_b_fkey
| DETAIL: Key (b,c)=(foo,bar) is not present in table x.
Assuming that you _could_ create the FOREIGN KEY reference above, if you
are strictly using the meta-data available in the information_schema,
this insert should succeed
| k=# insert into a values ('bar', 'foo');
| INSERT 0 1
and this insert should fail. The opposite happens beacuse PostgreSQL
is storing _more_ information than what is specified and has over
interpreted the meaning of the reference clause.
On Sat, Feb 25, 2006 at 02:01:00PM -0800, Stephan Szabo wrote:
| On Sat, 25 Feb 2006, Clark C. Evans wrote:
|
| CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
| CREATE TABLE a (b text, c text);
| ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
|
| I assert the problem here is that the FOREIGN KEY constraint
| construction should have *failed* since the *tuple* (z,y)
| does not infact match any unique key in table x.
|
| I disagree because the spec doesn't say that the columns must be equal
| or the list of columns must be the equal but that the set of columns must
| be equal. And in the definitions section, set is defined as an unordered
| collection of distinct objects.
Let's use the example Rod gave us above. If the comparison for foreign
key constraints should be done as an unorderd set, then why does the
following fail?
| k=# insert into x values ('foo', 'bar');
| INSERT 0 1
| k=# insert into a values ('foo', 'bar');
| ERROR: insert or update on table a violates foreign key constraint
| a_b_fkey
| DETAIL: Key (b,c)=(foo,bar) is not present in table x.
While the SQL1992 specification may be horribly incorrect; the current
behavior is not compliant with it... so this isn't a great defense. If
PostgreSQL didn't store the order of the columns referenced, it couldn't
provide the error above (which makes sense, given the extension).
| Okay, I'll take that back for SQL2003. They must have realized that this
| was broken with information schema and changed it.
Ok.
| That's an interesting incompatibility with old versions,
| but it's easy to implement.
This would be great; it would reduce the chances of an external program
generating SQL from making incorrect joins and causing a very strange
behavior and incorrect results.
Cheers,
Clark
---(end of broadcast)---
TIP 1: 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] constraints and sql92 information_schema compliance
Stephen, So, a quick re-cap of the questions/concerns I had: * Making the default constraint names include the table - This was implemented in 8.x, thank you! * Forbidding the creation of a foreign key constraint where the column list for the referenced table doesn't *exactly* match a canidate key on that table. - I think you've agreed to something like this, or am I mis-understanding? * Issue a warning when creating a constraint who's name is not unique within its (the constraint's) schema. - This request seems to have gotten lost in the vigorous discussion ;) Kind Regards, Clark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] constraints and sql92 information_schema compliance
Hello all. I've got a question with regard to the INFORMATION_SCHEMA of PostgreSQL, specificially related to constraints. In the SQL92 specification, the DEFINITION_SCHEMA.DOMAIN_CONSTRAINTS (the imaginary base for INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS), has a primary key: CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME This would leave me to believe that at constraints must have a unique name within a given schema; however, this seems not to be the case: # create domain test_one text - constraint test check (value is not null); CREATE DOMAIN # create domain test_two text - constraint test check (value is not null); CREATE DOMAIN # select constraint_catalog, constraint_schema, constraint_name - from information_schema.domain_constraints - where domain_name like 'test_%'; constraint_catalog | constraint_schema | constraint_name +---+- cce| public| test cce| public| test (2 rows) So it would seem that naming rules for constraints in PostgreSQL isn't exactly compliant with SQL92. I'm curious what sorts of constraints are enforced... Thank you so much, Clark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] constraints and sql92 information_schema compliance
On Fri, Feb 24, 2006 at 04:23:19PM -0800, Josh Berkus wrote: | Correct. Our uniqueness on constraints is: | schema_name | table_name | constraint_name | | We're aware that it's a violation of SQL92, but there's no way for us to | change it now without making it very hard for people to upgrade. And, | frankly, aside from the very occasional information_schema complaint, | nobody seems to care. Thank you for the quick response; I'm sure you've considered contatinating the internal pg_type name with the pg_constraint name? If so, is there areason this was rejected, since it is a constraint it isn't like you'd reference it in an SQL query (just trying to figure out what I should do in my application). On a related note, this view seems to be filtering by user, I'm curious what the rule is (I'm not that familiar /w PostgreSQL's internal meta-model)? While the textual description of this view Identify domain constraints in this catalog accessable to a given user. has not changed between SQL-1992 and SQL-2003, the actual critera specified is quite different: In SQL 1992, it seems to show only domains that are in schemas owned by the current user. In SQL 2003, it seems to be more intelligent: showing all constraints that are visible to the current user. I'm curious which rule PostgreSQL's information_schema is using? I think the SQL-2003 rules more properly follow the textual description and are more useful; the SQL-1999 rules are effectively useless in all but trivial cases. Kind Regards, Clark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] COMMENT ON [GROUP/USER]
It would be wonderful to be able to create comments on users and groups. In particular, I need a place to store the user's name. Yes, I could make a user table, but that seems overkill as all of the other aspects of a user are already in the metadata. Best, Clark -- Clark C. Evans Prometheus Research, LLC Chief Technology Officer Turning Data Into Knowledge [EMAIL PROTECTED] www.prometheusresearch.com (main) 203.777.2550(cell) 203.444.0557 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Postgresql likes Tuesday...
On Mon, Sep 30, 2002 at 06:49:34PM -0400, Tom Lane wrote: | The other issue is what | to_date(...,'WW') should do to produce a date representing a week | number. Shouldn't it always produce the first date of that week? | If not, what other conventions make sense? IMHO, it should choose the Week Ending date. This is usually what all of the companies that I've worked with want to see for the day column. For example, the defect^H^H^H^H^H^H quality reports at Ford Motor in 1993 used a Predo of part by defect by week-ending. Where week ending date was the Sunday following the work week (monday-sunday). In various project data in companies that I've worked with before and after 1993 I've yet to see a weekly report that didn't give the week ending... alhtough some did use Friday or Saturday for the week ending. One hickup with this choice is that you'd probably want the time portion to be 23:59:59.999 so that it includes everything up to the end of the day. Hmm. Clark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [BUGS] Bug #613: Sequence values fall back to previously chec
(userland comment) On Fri, Mar 15, 2002 at 01:05:33AM -0800, Vadim Mikheev wrote: | But sequences should not be under transaction control. Can you | safely rollback a sequence? No! The only way to ensure that would | ... | Placing a restriction on an application that says it must treat the values | returned from a sequence as if they might not be committed is absurd. | | Why? The fact that you are not able to rollback sequences does not | necessary mean that you are not required to perform commit to ensure | permanent storage of changes made to database. I use sequences to generate message identifiers for a simple external-to-database message passing system. I also use them for file upload identifiers. In both cases, if the external action (message or file upload) succeeds, I commit; otherwise I roll-back. I assume that the datbase won't give me a duplicate sequence... otherwise I'd have to find some other way go get sequences or I'd have duplicate messages or non-unique file identifiers. With these changes is this assumption no longer valid? If so, this change will break alot of user programs. | And why? Just for convenience of 1% applications which need | to use sequences in their own, non-database, external objects? I think you may be underestimating the amount of external resources which may be associated with a datbase object. Regardless, may of the database features in PostgreSQL are there for 1% or less of the user base... Best, Clark -- Clark C. Evans Axista, Inc. http://www.axista.com800.926.5525 XCOLLA Collaborative Project Management Software ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
