Re: [SQL] surrogate key or not?
Josh, > > In other words, with surrogate keys, you eliminate the chance > > that your original design was flawed due to lack of important > > initial knowledge. > > Well, you don't *eliminate* it, but you do decrease it. > > I'd say, yes, this is an important 4th reason: > > 4) Your spec may be incorrect and surrogate keys make it easier to make design > changes in production. thanks so much - this is exactly the kind of assurance I was looking for. BTW, I will come back to your generous offer in August. Thanks again. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] surrogate key or not?
On Fri, Jul 23, 2004 at 10:07:48AM -0400, Tom Lane wrote: > The other standard reason for using a made-up value as primary key is > that it's under your control and you can guarantee it isn't going to > change: one record will have the same primary key for its entire life, > which vastly simplifies handling of foreign keys and so forth. That is pretty much the main reason why our schema does so. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] surrogate key or not?
Just for the record, the GnuMed schema docs are done nightly with PostgreSQL Autodoc in HTML mode: http://www.rbt.ca/autodoc/ Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Is a backend id or something available for use as a foreign key?
Hi, I have this application which has various users. I want the results of functions to depend on which user is currently logged on. The usual reply to this is to use CURRENT_USER. But in my case the application's users are separate from the database users; the application always uses the same user to connect to the database. Is there a backend id available or something similar that would allow me to define a view like this: CREATE VIEW my_user_name AS SELECT u.user_name as user_name FROM users u JOIN user_has_backend ub ON (u.user_id = ub.user_id) WHERE ub.backend_id = CURRENT_BACKEND_ID; Thanks -- Markus Bertheau <[EMAIL PROTECTED]> ---(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: [SQL] surrogate key or not?
Josh Berkus wrote:
> Given: Surrogate keys, by definition, represent no real data;
> Given: Only items which represent real data have any place in
> a data model
> Conclusion: Surrogate keys have no place in the data model
But, once a surrogate key is assigned to a row, doesn't it become a
"real" data? For example, I have a bunch of invoices/receipts and I
write down a unique number on each of them. Doesn't the unique number
become part of the information contained by the invoice/receipt itself
(at least as long as I'm concerned)? Another example is when Adam
(+Eve?) named each animal in the Genesis. At that time the name he gave
for each animal was arbitrary ("surrogate"), but once assigned to each
species, it becomes part of the real data.
> 3) Mutability: Most RDBMSs are very inefficient about CASCADE deletes and
Change management IMO is perhaps the main reason of surrogate/artificial
key. We often need a PK that _never_ needs to change (because it can be
a royal PITA or downright impossibility to make this change; the PK
might already be printed on a form/card/document, recorded on some
permanent database, tattoed/embedded in someone's forehead, etc).
Meanwhile, every other aspect of the data can change (e.g. a person can
change his name, sex, age, email, address, even date & place of birth).
Not to mention data entry mistakes. So it's impossible to use any
"real"/natural key in this case.
> Now, you're probably wondering "why does this guy regard surrogate keys as a
> problem?" I'll tell you: I absolutely cannot count the number of "bad
> databases" I've encountered which contained tables with a surrogate key, and
> NO REAL KEY of any kind. This makes data normalization impossible, and
> cleanup of the database becomes a labor-intensive process requiring
> hand-examination of each row.
Okay, so surrogate key makes it easy for stupid people to design a
database that is prone to data duplication (because he doesn't install
enough unique constraints to prevent this). But I don't see how a
relation with a surrogate key is harder to "normalize" (that is, for the
duplicates to be removed) than a relation with no key at all. Compare:
street
--
Green Street
Green Street
Green Street
versus:
idstreet
----
2934 Green Street
4555 Green Street
5708 Green Street
They both contain duplicates and/or ambiguous data. They're both equally
hard to normalize/clean.
--
dave
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] Trigger functions with dynamic SQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi! I could use some help in writing PL/PGSQL trigger functions with dynamic SQL statements. The problem is not exactly an easy one but I hope it is of general interest to this list :-) I'm currently working on a database with several temporal tables. All temporal tables have some attributes which define the validity of a tuple in the time dimension. (For more info about temporal tables please take a look at "Developing Time-Oriented Database Applications in SQL" by Richard T. Snodgrass, for example) Example for temporal tables (reduced to the columns necessary): CREATE TABLE t_role ( id serial NOT NULL, begin_val timestamp DEFAULT now() NOT NULL, end_val timestamp DEFAULT 'infinity' NOT NULL, name character varying(50) NOT NULL, CONSTRAINT ck_role_begin_end CHECK (begin < end) ); CREATE TABLE t_person ( id serial NOT NULL, begin_val timestamp DEFAULT now() NOT NULL, end_val timestamp DEFAULT 'infinity' NOT NULL, username character varying(50) NOT NULL, role integer, CONSTRAINT ck_person_begin_end CHECK (begin < end) ); Columns "begin_val" and "end_val" define the interval of validity for the rows in the tables (I my real application I use table inheritance for all temporal tables, but I do not want to make the example more complex than necessary) Al time intervals are "closed-open" intervals. Gaps in history are not allowed. With this definition I can have the following rows in the table "t_role": id begin_val end_valname 12004-01-01 12:00:00 2004-01-03 13:00:00 sysadmin 12004-01-03 13:00:00 infinity System Administrator 22004-01-01 12:00:00 infinity Guest Table "t_person" could have the following contents: id begin_val end_valusername role = 12004-01-02 10:00:00 infinity andreas1 22004-01-02 10:03:00 infinity guest 2 I have set up views for all temporal tables to provide easy access to the current snapshot (including rules for easy data modifications) as well as triggers to maintain primary key as well as referential integrity constraints. For example, a current foreign key trigger function may look like this: CREATE FUNCTION func_fk_person_role() RETURNS "trigger" AS ' BEGIN IF EXISTS ( SELECT * FROM t_person AS SRC WHERE SRC.end_val = ''infinity'' AND NOT EXISTS ( SELECT * FROM t_role AS DST WHERE SRC.role = DST.id AND DST.end_val = ''infinity'') ) THEN RAISE EXCEPTION ''FK_person_role referential integrity violation - key referenced from temporal table t_person not found in temporal table t_role''; END IF; RETURN new; END; ' LANGUAGE plpgsql; The triggers on t_role and t_person are then defined as follows: CREATE TRIGGER trigger_fk_role_person AFTER DELETE OR UPDATE ON t_role FOR EACH ROW EXECUTE PROCEDURE func_fk_person_role(); CREATE TRIGGER trigger_fk_person_role AFTER INSERT OR UPDATE ON t_person FOR EACH ROW EXECUTE PROCEDURE func_fk_person_role(); As I have lots of temporal tables and all foreign key trigger definitions follow the same pattern, I would like to write a general trigger function which gets specialized by use of arguments to the trigger function. I want to have a single, general foreign key trigger function parametrized with variables "referer_tab", "referer_col", "referenced_tab" and "referenced_col" like this (note: the example is NOT working as it is!): CREATE FUNCTION func_fk_temporal () RETURNS "trigger" AS ' BEGIN IF EXISTS ( SELECT * FROM referer_tab WHERE referer_tab.end_val = ''infinity'' AND NOT EXISTS ( SELECT * FROM referenced_tab WHERE referer_tab.referer_col = referenced_tab.referenced_col AND referenced_tab.end_val = ''infinity'') ) THEN RAISE EXCEPTION ''referential integrity violation - key referenced from temporal table referer_tab not found in temporal table referenced_tab''; END IF; RETURN new; END; ' LANGUAGE plpgsql; and write the trigger definition like this: CREATE TRIGGER trigger_fk_role_person AFTER DELETE OR UPDATE ON t_role FOR EACH ROW EXECUTE PROCEDURE func_fk_temporal(t_person, role, t_role, id); CREATE TRIGGER trigger_fk_person_role AFTER INSERT OR UPDATE ON t_person FOR EACH ROW EXECUTE PROCEDURE func_fk_temporal(t_person, role, t_role, id); That way I could have one single FK trigger function for all my temporal tables. The manual for PostgreSQL
Re: [SQL] Is a backend id or something available for use as a foreign key?
Markus Bertheau <[EMAIL PROTECTED]> writes: > Is there a backend id available or something similar that would allow > me to define a view like this: IIRC there is a function to get your backend's PID. It was meant for identifying rows relevant to you in the pg_stat views, so look in that part of the documentation. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Trigger functions with dynamic SQL
Andreas Haumer <[EMAIL PROTECTED]> writes: > It seems I would have to use EXECUTE on dynamically constructed > PL/PGSQL statements in order to have my trigger function recognize > the parameters given to the trigger in TG_ARGV[] Yup, that's exactly right. plpgsql isn't designed for this; it's designed for situations where it can pre-plan and cache plans for queries, and dynamically-inserted table and column names would just break that completely. So you have to fall back to the mechanisms for fully general constructed-on-the-fly queries, which work but are a bit painful to use. You might want to look at pltcl instead, which is much friendlier to dynamically generated queries (since that's the only way it does things). Of course, if you've never used Tcl there'll be a bit of a learning curve :-( regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Trigger functions with dynamic SQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi! Many thanks for your reply! Tom Lane wrote: > Andreas Haumer <[EMAIL PROTECTED]> writes: > >>It seems I would have to use EXECUTE on dynamically constructed >>PL/PGSQL statements in order to have my trigger function recognize >>the parameters given to the trigger in TG_ARGV[] > > > Yup, that's exactly right. plpgsql isn't designed for this; it's Ok, that's what I thought. Thanks for confirmation. > designed for situations where it can pre-plan and cache plans for > queries, and dynamically-inserted table and column names would just > break that completely. So you have to fall back to the mechanisms > for fully general constructed-on-the-fly queries, which work but > are a bit painful to use. > "a bit painful" is the understatement of the year! :-) I just can't figure out where and how many quotation marks I have to place in my function. Some examples would be helpful (the examples in the manual are quite simple and always end where it begins to become interesting for me...) Also: what is the performance impact of using EXECUTE and constructed-on-the-fly queries? > You might want to look at pltcl instead, which is much friendlier > to dynamically generated queries (since that's the only way it > does things). Of course, if you've never used Tcl there'll be > a bit of a learning curve :-( > My experience with Tcl is almost zero. :-( What about writing trigger functions in C? It should be quite easy to dynamically create the SQL statements needed here and it seems I can access the trigger arguments through the tg_trigger->tgargs array. Would this be a better way to go? (faster code, but perhaps painful to write as well, too) I found some examples in the PostgreSQL sources under .../contrib/spi/ I think I will have to take a deeper look at that... - - andreas - -- Andreas Haumer | mailto:[EMAIL PROTECTED] *x Software + Systeme | http://www.xss.co.at/ Karmarschgasse 51/2/20 | Tel: +43-1-6060114-0 A-1100 Vienna, Austria | Fax: +43-1-6060114-71 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFBAoRMxJmyeGcXPhERAg/OAJ9Mg5Ecp1urDhJAnCQ+k8A9N7sv+QCfe0w9 Cdlbkwt0QITR2bU+lIO0TtU= =5tJf -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Trigger functions with dynamic SQL
Andreas Haumer <[EMAIL PROTECTED]> writes: > I just can't figure out where and how many quotation marks > I have to place in my function. It's messy all right. The "dollar quoting" feature in 7.5 should make it a lot less painful, since you can stop having to double and re-double quote marks. If you're interested in using beta-quality code, you can have that today. An example would go something like CREATE FUNCTION mytrigger() RETURNS trigger AS $PROC$ DECLARE ... EXECUTE $$ SELECT ... FROM $$ || tgargv[0] || $$ WHERE col = 'key' $$; ... END $PROC$ LANGUAGE plpgsql; Here I've used minimal dollar quotes ($$) for the literal constant parts of the EXECUTE'd query, which allows me not to have to double the quote marks that I actually want in the query text (the ones around "key"). And I used dollar quotes with a label ($PROC$) at the outermost level to quote the entire function body, so that there's no conflict with the embedded dollar quotes. In 7.4 the same EXECUTE command would have to be written EXECUTE '' SELECT ... FROM '' || tgargv[0] || '' WHERE col = key ''; which is already getting painful, and more complex cases get rapidly worse. With dollar quoting you can write the constant parts of your query the same way you normally would. > What about writing trigger functions in C? Seems like the hard way to me. I doubt it would be better than plpgsql, but it's all a matter of opinion... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
