On 6/18/2006 11:41 PM, Wes wrote:
Is there a way to add a foreign key constraint without having to wait for it
to check the consistency of all existing records? If a database is being
reloaded (pg_dumpall then load), it really shouldn't be necessary to check
the referential integrity - or at le
On 6/22/06 2:57 PM, "Jim Nasby" <[EMAIL PROTECTED]> wrote:
> If nothing else, you should bring it up on -hackers and ask to have
> this added as a TODO. It seems like a worth-while addition to pg_dump/
> restore to me...
Thanks for the suggestion.
> To answer another of your emails in this threa
On Jun 21, 2006, at 8:38 AM, Wes wrote:
Maybe you could ask at some postgresql support companies how much
effort it
would
be to add a "without check" flag to "alter table add constraint
foreign key",
and
how much they'd charge for it...
Or if I get ambitious, dig into the code myself if I
On 6/20/06 8:17 PM, "Florian G. Pflug" <[EMAIL PROTECTED]> wrote:
> Maybe you could ask at some postgresql support companies how much effort it
> would
> be to add a "without check" flag to "alter table add constraint foreign key",
> and
> how much they'd charge for it...
Or if I get ambitious, d
Wes wrote:
On 6/20/06 5:07 AM, "Florian G. Pflug" <[EMAIL PROTECTED]> wrote:
My suggestion was to create the fk _before_ loading the data, and disable it
similarly to what "--disable-triggers" doest. It turned out, however, that a
FK always depends on a unique index (be it a primary key, or not
On Tue, Jun 20, 2006 at 00:49:21 -0400,
louis gonzales <[EMAIL PROTECTED]> wrote:
> Florian,
> I understand where you're coming from. Indexes are always unique and
> all RDBMS systems use them to 'uniquely' identify a row from the the
> perspective of internal software management. Index != Pr
On 6/20/06 5:07 AM, "Florian G. Pflug" <[EMAIL PROTECTED]> wrote:
> My suggestion was to create the fk _before_ loading the data, and disable it
> similarly to what "--disable-triggers" doest. It turned out, however, that a
> FK always depends on a unique index (be it a primary key, or not), which
louis gonzales wrote:
Florian,
I understand where you're coming from. Indexes are always unique and
all RDBMS systems use them to 'uniquely' identify a row from the the
perspective of internal software management.
Surely there are non-unique indices - meaning indices for which there
are more
Florian,
So if you:
create table test (
id varchar(2) primary key,
age int );
create table test2 (
id varchar(2) primary key,
age2 int );
alter table test2 add foreign key (id) references test (id);
\d test2
you'll see that attribute "id" from test2, now has both a primary key
constraint an
Florian,
I understand where you're coming from. Indexes are always unique and
all RDBMS systems use them to 'uniquely' identify a row from the the
perspective of internal software management. Index != PrimaryKey, so
every table created, despite any Primary/Foreign key contraints put on
them,
BTW, I do appreciate any and all suggestions. I hope my last message didn't
come across otherwise. That's not what was intended. It's just that
adding/replacing hardware is not an option right now. Maybe next year...
I'm still trying to dig up another 2GB memory.
The database actually perform
On 6/19/06 4:48 PM, "Scott Marlowe" <[EMAIL PROTECTED]> wrote:
>
> I think Jim was thinking of RAID 1+0. If you gotta use RAID 5, at least
> have a contoller with several hundred megs of battery backed cache.
> Better yet, use a RAID controller with a gig or so of BBU cache and run
> RAID 1+0 on
louis gonzales wrote:
Florian,
Are you certain:
"You can only create an FK if the fields you are referencing in the
foreign table form a PK there. And creating a PK implicitly creates an
index, which you can't drop without dropping the PK :-("
Arg.. Should have written "unique index" instea
On Mon, 2006-06-19 at 16:39, Wes wrote:
> On 6/19/06 3:47 PM, "Jim Nasby" <[EMAIL PROTECTED]> wrote:
>
> > FWIW, RAID5 isn't normally a good recipe for good database performance.
>
> Understood, but RAID 1 isn't always feasible. The database performs very
> well with the current hardware configu
On 6/19/06 3:47 PM, "Jim Nasby" <[EMAIL PROTECTED]> wrote:
> FWIW, RAID5 isn't normally a good recipe for good database performance.
Understood, but RAID 1 isn't always feasible. The database performs very
well with the current hardware configuration - I have no complaints. The
only time we hav
On 6/19/06 3:24 PM, "Scott Marlowe" <[EMAIL PROTECTED]> wrote:
> Are you sure that's really the problem? Do you have indexes on the
> referring tables (i.e. the foreign key that points to the other table's
> primary key). Not having an index on the subordinate table makes each
> and every check
On Jun 18, 2006, at 11:08 PM, Wes wrote:
System memory is 2GB (would like more, but...). Data is on one array
(hardware RAID 5 of eight 73 GB 10k SCSI drives), indexes on another
(hardware RAID 5 of five 15K 146 GB SCSI drives on a separate
channel), and
pg_xlog on a third RAID 1 on a third ch
On Sun, 2006-06-18 at 22:41, Wes wrote:
> Is there a way to add a foreign key constraint without having to wait for it
> to check the consistency of all existing records? If a database is being
> reloaded (pg_dumpall then load), it really shouldn't be necessary to check
> the referential integrity
Florian,
Are you certain:
"You can only create an FK if the fields you are referencing in the
foreign table form a PK there. And creating a PK implicitly creates an
index, which you can't drop without dropping the PK :-("
I'm not sure I am convinced the necessity of a foreign key, "need
Wes wrote:
You could create the fk-constraints _first_, then disable them, load
the data, reindex, and reenable them afterwards.
pg_dump/pg_restore can enable and disable fk-constraints before restoring
the data, I believe. It does so by tweaking the system catalogs.
Are referring to '--disabl
> What PG version is this, and what have you got maintenance_work_mem set to?
> Undersized m_w_m would hurt both index build and FK checking ...
Looking at the stats again, I did see something unexpected. With MWM set to
983025, index builds were running about 1.3 GB for both RSS and virtual.
Add
> You could create the fk-constraints _first_, then disable them, load
> the data, reindex, and reenable them afterwards.
>
> pg_dump/pg_restore can enable and disable fk-constraints before restoring
> the data, I believe. It does so by tweaking the system catalogs.
Are referring to '--disable-tr
Wes wrote:
Is there a way to add a foreign key constraint without having to wait for it
to check the consistency of all existing records? If a database is being
reloaded (pg_dumpall then load), it really shouldn't be necessary to check
the referential integrity - or at least I should be able to
On 6/18/06 10:48 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote:
>> My database reload is currently taking about 6 hours to load the data, 42
>> hours to reindex, and about another 40 hours or so to check the foreign key
>> constraints (about 1.2 billion rows).
>
> What PG version is this, and what hav
Wes <[EMAIL PROTECTED]> writes:
> My database reload is currently taking about 6 hours to load the data, 42
> hours to reindex, and about another 40 hours or so to check the foreign key
> constraints (about 1.2 billion rows).
What PG version is this, and what have you got maintenance_work_mem set
25 matches
Mail list logo
