Re: [SQL] unreferenced primary keys: garbage collection
Forest Wilkinson wrote: > I have a database in which five separate tables may (or may not) reference > any given row in a table of postal addresses. I am using the primary / > foreign key support in postgres 7 to represent these references. > > My problem is that, any time a reference is removed (either by deleting or > updating a row in one of the five referencing tables), no garbage > collection is being performed on the address table. That is, when the > last reference to an address record goes away, the record is not removed > from the address table. Over time, my database will fill up with > abandoned address records. While this behaviour makes sense in your case, it's not subject to referential integrity constraints. You could arrange for it with custom trigger procedures, checking all the five tables on DELETE or UPDATE on one of them. I'll make up a little example and post it the other day - need to take a nap now and tomorrow will be one of these 30-hour days (from MET to EST), so don't expect anything before Monday afternoon (EST). Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
[SQL] abstract data types?
Hi all,
I'm sure this has become somewhat of a FAQ recently, but I couldn't
find any reference to casting composite types in the mailing lists.
I'm trying to figure out what support PostgreSQL offers for SQL99
abstract data types.
PostgreSQL version is cvs from about a week ago.
Given the following relations:
test=> \d c_adtattr
Table "c_adtattr"
Attribute | Type | Modifier
---+--+--
attr1 | text |
attr2 | text |
test=> \d c_adtparent
Table "c_adtparent"
Attribute | Type| Modifier
---+---+--
basetype | text |
adtattr | c_adtattr |
OK, now try and insert a tuple into the relation with the composite
attribute:
test=> INSERT INTO c_adtparent values ('basetype','{"adtr1a1","adtr1a2"}');
ERROR: Attribute 'adtattr' is of type 'c_adtattr' but expression is of
type 'unknown'
You will need to rewrite or cast the expression
Is this use of the '{" delimiters correct? I took it from the array
docs, which was only reference that I could find to anything like
inserting values into composite types.
OK, try something stupid (cast as pg_type.typname):
test=> INSERT INTO c_adtparent values ('basetype', CAST
('{"adtr1a1","adtr1a2"}' AS c_adtattr));
ERROR: pg_atoi: error in "{"adtr1a1","adtr1a2"}": can't parse
"{"adtr1a1","adtr1a2"}"
OK, try insert into individual attributes:
test=> INSERT INTO c_adtparent (basetype, adtattr.attr1, adtattr.attr2)
VALUES ('basetype', CAST ('adtr1a1') AS text, CAST ('adtr1a2') AS text);
ERROR: parser: parse error at or near "."
OK, try double dot syntax from SQL99:
test=> INSERT INTO c_adtparent (basetype, adtattr..attr1,
adtattr..attr2) VALUES ('basetype', CAST ('adtr1a1') AS text, CAST
('adtr1a2') AS text);
ERROR: parser: parse error at or near "."
So far, so bad. Am I doing something really stupid with the syntax,
should I be using a different approach, or does the support not yet
exist? If it's just my stupidity, can someone please give me some
pointers to exactly where I should RTFM?
OK, so why am I attempting this lunacy? I am interested in assisting
with the development of a data store for GIS. Looks like most of the
mooted schemas will involve the creation of large numbers of tables of
identical type. Each of these in turn contains possibly repeating
identical conceptual structures. So to me it made sense, rather than
producing application specific code to manage these, to see if support
could be added to the backend DBMS, so that other applications (e.g.
CAD) could make use of the functionality.
TIA for any assistance.
cheers,
John
--
--
john reid e-mail [EMAIL PROTECTED]
uproot your questions from their ground and the dangling roots will be
seen. more questions!
-mentat zensufi
apply standard disclaimers as desired...
--
Re: [SQL] abstract data types?
John Reid <[EMAIL PROTECTED]> writes: > I'm trying to figure out what support PostgreSQL offers for SQL99 > abstract data types. None, I fear. The stuff you are fooling with is leftover from the old PostQuel language. Most of it is suffering from bit rot, because the developers' focus has been on SQL92 compliance for the last six or seven years. I hadn't realized that SQL99 had caught up to PostQuel in this area ;-). Sounds like we will have to dust off some of that stuff and get it working again. No promises about timeframe, unless someone steps up to the plate to do the work... regards, tom lane
Re: [SQL] abstract data types?
On Sat, 20 Jan 2001, Tom Lane wrote: > None, I fear. The stuff you are fooling with is leftover from the old > PostQuel language. Most of it is suffering from bit rot, because the > developers' focus has been on SQL92 compliance for the last six or seven > years. I hadn't realized that SQL99 had caught up to PostQuel in this > area ;-). Sounds like we will have to dust off some of that stuff and > get it working again. No promises about timeframe, unless someone > steps up to the plate to do the work... What goes around comes around. :-) -- Brett http://www.chapelperilous.net/~bmccoy/ --- mixed emotions: Watching a bus-load of lawyers plunge off a cliff. With five empty seats.
