Re: CREATE TABLE with REFERENCE - Mailing list pgsql-general

Jonathan Bartlett wrote:

>>Exactly. But 'copy from ' does *not* - so, after you have loaded, your
>>sequnce next_val () will return 1.
>>
>>
>
>We just use pg_dump, which re-sets the sequence to its proper value.
>
>
Lucky you :-)
But wait, till your database grows beyond a few hundred gig...

>
>
>>>The OID type/column is ideal for this.
>>>
>>>
>>>
>>No, it isn't. Not all tables have oids. The ones that do, do not
>>guarantee, that they will be unique.
>>
>>
>
>I wasn't indicating that the current implementation was ideal.  In fact, I
>indicated exactly the opposite.  I was talking about the idea behind it.
>
I even kept your original quote above - you *did* say it was "ideal" in
those exact words :-)
I wasn't dreaming :-)

>
>
>
>>>even do record merges with automatic database support.
>>>
>>>
>>>
>>What do you mean by "record merges"?
>>Any meaning of that phrase I can imagine can be easily done with the
>>currently supported database features... so, you must mean something
>>different by that, I assume...
>>
>>
>
>Kind of.  Lets say that you build a commodity database application, which
>has customer tables, invoice tables, etc.  Let's say you had two
>customers, A and B, who merged, and you wanted to merge their records
>together.  You could write a program to do it, but it would be specific to
>customer records, and if other kinds of record merges were needed you
>would have to write separate programs for those (say, contact merges or
>something).  So, you have to write a custom application for every type of
>record merge, and it won't even attempt to take into account any custom
>tables taht someone else defines.
>
If your schema was properly designed to begin with, you should not even
need any application at all - just do:

begin;
set constraints all immediate;
update users set id=<new_userid> where login = 'customerA';
set constraints all deferred;
delete from users where login = 'custmerA';
update users set id=<new_userid> where login = 'custmerB';
delete from users where login = 'customerB';
insert into users values (<new_id>, 'merged_customer_login', ...);
commit;

This should take care about rerouting all the depending entries to the
new user *as long as you have your FKs setup properly*, of course.

>
>Let's say that instead you used the following pattern when building your
>database:
>
>* All rows had OIDs
>* All foreign keys that related to OIDs had a specific, OID type (not just
>generic integer)
>
>Now, if you want to merge record 1345 with record 1765, and you wanted
>1765 to be the new master, you could do the following:
>
>Search the database catalog for columns of type OID.
>For each instance, update all rows having 1345 to have 1765 instead
>Not each instance this generates an exception
>If successful, great, if not, report back which rows had integrity
>problems after the merge.
>Mark record 1345 as being deleted.  In addition, you could have a generic
>"merge" table which recorded every record and what record it was merged
>into.
>
>With this, you can apply this generic merge function to any record of any
>table at all, and it will continue to work in user-defined custom modules.
>
>
See above - all this is *easier* done with just regular FKs - no need to
lookup catalogs, reporting integrity problems, blah, blah, blah...
All you need is to set up your FKs correctly, so that the DB knows your
integrity rules - everything else is just done 'automagically' for you
'under the hood'.

>
>
>>No. They would have a base class of "Object" (or whatever), and the
>>'notes' would be linked to the Object.
>>This would in fact, be a *beatiful* solution... it's a shame really that
>>it doesn't work.
>>
>>
>
>Hmm, on the one hand you think this is a beautiful solution, but on the
>other hand you reject my notion that the database does not have all the
>power it could?
>
>
I don't reject your notion. Database does luck power in many areas -
just  not in the ones you are complaining about :-)

>
>
>>I am wonderring if what postgres does with those inherited FK
>>constraints is specified by the standard, or if it is just an
>>implementation feature, that can be improved...
>>
>>
>
>I'm not sure that inheritance is part of any standard.
>
>
Me neither :-)
But, I think I heard somewhere that SQL99 has something about it...


Dima