Berend Tober <[EMAIL PROTECTED]> writes:
>> On Thu, 2005-05-19 at 23:27 -0400, Tom Lane wrote:
>>> OK, now I finally get the point: you are creating child tables in
>>> different schemas than their parents live in.
>>
> The case in question was not one of the child table being in a different
>
Berend Tober <[EMAIL PROTECTED]> writes:
> Now what, oh most wise one?
OK, now I finally get the point: you are creating child tables in
different schemas than their parents live in. This creates a problem
because reverse-listing of the constraints varies depending on what
the search path is.
An
Tom Lane wrote:
What do you get from
select conname, consrc from pg_catalog.pg_constraint
where contype = 'c' and conrelid = 'person'::regclass;
conname
|
consrc
-
Berend Tober <[EMAIL PROTECTED]> writes:
> Interesting. The consrc column values differ in that the explicit schema
> qualification on the function calls is missing for the descendent table.
> So, you think maybe if I remove the explicit schema qualification from
> the function calls in the cons
Tom Lane wrote:
What do you get from
select conname, consrc from pg_catalog.pg_constraint
where contype = 'c' and conrelid = 'person'::regclass;
conname | consrc
--
Berend Tober <[EMAIL PROTECTED]> writes:
> I tried a simpler example than my original, as you have, and the problem
> bahavior didn't manifest, but it still happens in my dev copy of my
> production database. The immediately obvious difference between the
> simpler example, like yours, and the a
Tom Lane wrote:
The case I tested seems to work in 7.3 as well:
CREATE TABLE person (last_name varchar(24),
first_name varchar(24),
CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
(first_name IS NOT NULL;
CREATE TABLE person_change_history(
action VARCHAR(6),
update_dat
Greg Patnude wrote:
Yeah… this is where the inheritance model gets a little funky… What do
you have “SQL_INEHERITANCE” set to when you dump the database ? I’ve
never tested this so I don’t know if it makes a difference being on or
off when you dump a table…. You might try it and compare the two
Berend Tober <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Hmm, it shouldn't do that ... and in a quick test here I couldn't
>> reproduce any such bug. What version of pg_dump are you using?
> Sorry I failed to specify. Production version is 7.3.1 (change is
> hard!), although I origianally w
Tom Lane wrote:
Berend Tober <[EMAIL PROTECTED]> writes:
But after doing a dump of the modified data base, the script created by
pg dump wants to recreate the history table as
...
CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
(first_name IS NOT NULL))),
Hmm, it shouldn
Berend Tober <[EMAIL PROTECTED]> writes:
> But after doing a dump of the modified data base, the script created by
> pg dump wants to recreate the history table as
> ...
> CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
> (first_name IS NOT NULL))),
Hmm, it shouldn't do that .
A few months ago, a question by Scott Frankel produced a suggestion from
Greg Patnude which I found very exciting that had to do with using pg
table inheritance to maintain an audit or row change history table. I've
been testing Patnude's idea and ran into a problem, described below, and
wanted
> Its called a "temporal database".
> Usually its intended for medical or police databases where
> you need a hind sight. i.e. if today is 31/12/2005, what did we know at
> 20/12/2005.
> for example, for a doctor appearing at court and required to testify
> what he knew at 20/12/2005.
What he "can
On Sat, Mar 05, 2005 at 03:46:53AM +0200, Tzahi Fadida wrote:
> Its called a "temporal database".
> Usually its intended for medical or police databases where
> you need a hind sight. i.e. if today is 31/12/2005, what did we know at
> 20/12/2005.
> for example, for a doctor appearing at court and
ql-general@postgresql.org
> Subject: [GENERAL] preserving data after updates
>
>
>
> Is there a canonical form that db schema designers use
> to save changes to the data in their databases?
>
> For example, given a table with rows of data, if I UPDATE
> a field in a row,
-Original Message-
From: Berend Tober [mailto:[EMAIL PROTECTED]
Sent: Friday, March 04, 2005 8:47 AM
To: Greg Patnude
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] preserving data after updates
> I use a modified form of option 3 with an ON UPDATE RULE the update
r
Door number 3.
Thanks for the responses and terrific suggestions!
Scott
On Mar 4, 2005, at 8:28 AM, Greg Patnude wrote:
I use a modified form of option 3 with an ON UPDATE RULE the
update rule
copies the row to an inherited table...
CREATE TABLE dm_user (
id SERIAL NOT NULL PRIMARY KE
> I use a modified form of option 3 with an ON UPDATE RULE the update rule
> copies the row to an inherited table...
I just gotta say that THAT is one COOL use of PG inheritance! Do you find that
it works well and is robust and all the good stuff it seems like would be the
case?
-- Berend
I use a modified form of option 3 with an ON UPDATE RULE the update rule
copies the row to an inherited table...
CREATE TABLE dm_user (
id SERIAL NOT NULL PRIMARY KEY,
lu_user_type INTEGER NOT NULL REFERENCES lu_user_type(id),
dm_user_address INTEGER NOT NULL DEFAULT 0,
I do option 3. A generic trigger writes the tablename, field name, type
of change (insert/update/delete) and the old and new values for columns
that were affected. It is kind of a hog, but it works very well. I
have cron delete old entries so it doesn't eat my whole disk.
I haven't tried to ge
Is there a canonical form that db schema designers use
to save changes to the data in their databases?
For example, given a table with rows of data, if I UPDATE
a field in a row, the previous value is lost. If I wanted to
track the changes to my data over time, it occurs to me that
I could,
1) cop
21 matches
Mail list logo
