Thread: pg_restore and FK constraints with large dbs

pg_restore and FK constraints with large dbs

From
ow
Date:
pgSql 7.4.rc2

Hi,

Am somewhat lost about how one should use pg_restore with large dbs. For
simplicity, let's assume that db has only one (1) table with huge amout of
rows. Tried several approaches:

1) dump the data only, create the table with all indexes and constraints (from
script that has nothing to do with pg_restore), import the data. The import
part was running for hours (14+) with no end in sight. Had to kill it.

2) dump the data only, create the table with no indexes or constraints, import
the data, recreate indexes and constraints.

This approach produced better results. Data were imported in about two (2)
hours, indexes also were created fairly quickly. Howerver, creattion of FK
constraints is killing performance again. It appears in the past there was some
discussion regarding the ability to create a FK constraint without actually
checking the records. It makes a lot of sense when importing data from
pg_restore since data comes from db where the constraint was in place, hence
it's clean.

Any ideas? Thanks





__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: pg_restore and FK constraints with large dbs

From
Peter Eisentraut
Date:
ow writes:

> 1) dump the data only, create the table with all indexes and constraints (from
> script that has nothing to do with pg_restore), import the data. The import
> part was running for hours (14+) with no end in sight. Had to kill it.

Dump the data and the schema and it will do it in the right order.

--
Peter Eisentraut   peter_e@gmx.net


Re: pg_restore and FK constraints with large dbs

From
ow
Date:
--- Peter Eisentraut <peter_e@gmx.net> wrote:
> ow writes:
>
> > 1) dump the data only, create the table with all indexes and constraints
> (from
> > script that has nothing to do with pg_restore), import the data. The import
> > part was running for hours (14+) with no end in sight. Had to kill it.
>
> Dump the data and the schema and it will do it in the right order.

Can you clarify how this would be better than the second option I described?
Unless pg_restore can somehow create FK constraints differently (and more
efficiently) than using "ALTER TABLE xxx ADD CONSTRAINT", I do not see how this
would help.

Thanks






__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: pg_restore and FK constraints with large dbs

From
Peter Eisentraut
Date:
ow writes:

> Can you clarify how this would be better than the second option I described?
> Unless pg_restore can somehow create FK constraints differently (and more
> efficiently) than using "ALTER TABLE xxx ADD CONSTRAINT", I do not see how this
> would help.

If you you pg_dump and dump schema and data together, then there is some
magic to temporarily disable foreign key constraints.  Try it out.
Perhaps you can adopt the statements to your particular restoration method
as well, if it turns out necessary.

--
Peter Eisentraut   peter_e@gmx.net


Re: pg_restore and FK constraints with large dbs

From
ow
Date:
--- Peter Eisentraut <peter_e@gmx.net> wrote:
> If you you pg_dump and dump schema and data together, then there is some
> magic to temporarily disable foreign key constraints.  Try it out.
> Perhaps you can adopt the statements to your particular restoration method
> as well, if it turns out necessary.
>

No longer have the db to extract data and schema together (have only data
extracts), but I'll try later.

However, if FK constraints are temporarily disabled in pg_restore, why is it
necessary to arrage the statimements in the order that satisfies FK
constraints? One could be doing this for days ...

Wouldn't be simpler for pg_restore to do the following:
1) create table structures only (no indexes or constraints)
2) create indexes
3) create constraints

This way, there's no need to rearrange the order of imports.

Thanks





__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: pg_restore and FK constraints with large dbs

From
ow
Date:
pgSql 7.4.rc2

--- Peter Eisentraut <peter_e@gmx.net> wrote:
> If you you pg_dump and dump schema and data together, then there is some
> magic to temporarily disable foreign key constraints.  Try it out.

Dunno. Don't see any magic so far. In fact, pg_restore appears to use
internally the same SQL to verify that the validity of the FKs as "ALTER TABLE
xxx ADD CONSTRAINT". And it's not any faster either.

Is there a special option that one should specify to suspend FK checks during
pg_restore? Otherwise, pg_restore's performance is ... umm, not very good, to
put it mildly.

Thanks






__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: pg_restore and FK constraints with large dbs

From
Peter Eisentraut
Date:
ow writes:

> --- Peter Eisentraut <peter_e@gmx.net> wrote:
> > If you you pg_dump and dump schema and data together, then there is some
> > magic to temporarily disable foreign key constraints.  Try it out.
>
> Dunno. Don't see any magic so far. In fact, pg_restore appears to use
> internally the same SQL to verify that the validity of the FKs as "ALTER TABLE
> xxx ADD CONSTRAINT". And it's not any faster either.

Read again.  No one was talking of pg_restore.

--
Peter Eisentraut   peter_e@gmx.net


Re: pg_restore and FK constraints with large dbs

From
ow
Date:
--- Peter EiEisentrautpeter_e@gmgmxet> wrote:
>
> Read again.  No one was talking of pg_restore.

Perhaps I should clarify.

First, I ran pg_dump to extract schema and data *together*. Then I ran
pg_restore to restore the db. It took about 1 hour to create tables and copy
the data, then about 40 min to create indexes, then pg_restore spent 4.5 hours
checking one (1) FKFKonstraint (80M table with FKFKsgainst 20K table with PKPKs

4.5 hours to check one FKFKonstraint - this is want I meant by bad performance.
I'm  looking for a way to suspend FKFKhecks since data is coming from pg_dump
and, hence, it's clean.

Thanks

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: pg_restore and FK constraints with large dbs

From
Oli Sennhauser
Date:
Where can we place wishes for PostgreSQL v7.5 and 8.0 ??? Is it
pgsql-hackers ???

-------------------------------------------------------

Oli Sennhauser
Database-Engineer (Oracle & PostgreSQL)
Rebenweg 6
CH - 8610 Uster / Switzerland

Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14
e-Mail oli.sennhauser@bluewin.ch
Website http://mypage.bluewin.ch/shinguz/PostgreSQL/

Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch

Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import


Attachment

Re: pg_restore and FK constraints with large dbs

From
Stephan Szabo
Date:
On Sun, 16 Nov 2003, ow wrote:

> --- Peter EiEisentrautpeter_e@gmgmxet> wrote:
> >
> > Read again.  No one was talking of pg_restore.
>
> Perhaps I should clarify.
>
> First, I ran pg_dump to extract schema and data *together*. Then I ran
> pg_restore to restore the db. It took about 1 hour to create tables and copy
> the data, then about 40 min to create indexes, then pg_restore spent 4.5 hours
> checking one (1) FKFKonstraint (80M table with FKFKsgainst 20K table with PKPKs
>
> 4.5 hours to check one FKFKonstraint - this is want I meant by bad performance.
> I'm  looking for a way to suspend FKFKhecks since data is coming from pg_dump
> and, hence, it's clean.

Only assuming that no changes were made between dump and restore.  This
could be changes to schema or data done manually, but it could also be a
locale or possibly encoding change if you have any textual foreign keys.

Re: pg_restore and FK constraints with large dbs

From
ow
Date:
--- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> Only assuming that no changes were made between dump and restore.  This
> could be changes to schema or data done manually, but it could also be a
> locale or possibly encoding change if you have any textual foreign keys.

I'm restoring the database, meaning that (a) it's believed that current db is
corrupt and needs to be restored or (b) the db is moved to a different server.
Hence any changes after the last dump will be blown away anyway, wouldn't they?
One would have to apply the changes manually or whatever, once the restore is
completed.

The problem is that restoration process is taking too long, mostly due to FK
checks, as it was described earlier, and it appears there's no way to speed
this up.

Sadly, I'm looking at m*Sql and see that they have "SET FOREIGN_KEY_CHECKS=0"
command.

Thanks





__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: pg_restore and FK constraints with large dbs

From
Stephan Szabo
Date:
On Sun, 16 Nov 2003, ow wrote:

>
> --- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> > Only assuming that no changes were made between dump and restore.  This
> > could be changes to schema or data done manually, but it could also be a
> > locale or possibly encoding change if you have any textual foreign keys.
>
> I'm restoring the database, meaning that (a) it's believed that current db is
> corrupt and needs to be restored or (b) the db is moved to a different server.
> Hence any changes after the last dump will be blown away anyway, wouldn't they?
> One would have to apply the changes manually or whatever, once the restore is
> completed.

Locale/Encoding changes are not part of the inside of the database at all
so there's no guarantees for that.  For the others, sometimes people do
work on textual representations of dumps. Neither of those is a reason not
to have an option to turn them off in restore, but the check should be
allowed if desired.

If you look at the -hackers discussion, options for this were talked
about recently, but it came pretty late in the 7.4 development cycle,
after beta started. It might happen for 7.5 if a consensus is reached for
behavior.


Re: pg_restore and FK constraints with large dbs

From
ow
Date:
--- ow <oneway_111@yahoo.com> wrote:
> Perhaps I should clarify.
>
> First, I ran pg_dump to extract schema and data *together*. Then I ran
> pg_restore to restore the db. It took about 1 hour to create tables and copy
> the data, then about 40 min to create indexes, then pg_restore spent 4.5
> hours
> checking one (1) FK constraint (80M table with FKs against 20K table with
> PKs)
>
> 4.5 hours to check one FK constraint - this is what I meant by bad
> performance.
> I'm  looking for a way to suspend FK checks since data is coming from pg_dump
> and, hence, it's clean.
>

No solution? Thanks





















__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: pg_restore and FK constraints with large dbs

From
Rudi Starcevic
Date:
Hi ow,

When your using expensive support, like this email list, technical
replies can take some time.
Perhaps those who can help you are on the other side of the globe and
are sleeping.
Or maybe they busy trying to make a living.

Please be patient.
If you do not recieve a reply I suggest you politely re-post you question.

If all this is not fast enough for you I recommend finding a Postgresql
consultant who
is happy to be on call all day and all night for you.

There are several Postgresql consultant's on these lists, you could post
a request seeking their services.

I'm not trying to upset anyone just trying to help you get an answer for
your issues.

Thanks
Kind regards
Rudi.

ow wrote:

>--- ow <oneway_111@yahoo.com> wrote:
>
>
>>Perhaps I should clarify.
>>
>>First, I ran pg_dump to extract schema and data *together*. Then I ran
>>pg_restore to restore the db. It took about 1 hour to create tables and copy
>>the data, then about 40 min to create indexes, then pg_restore spent 4.5
>>hours
>>checking one (1) FK constraint (80M table with FKs against 20K table with
>>PKs)
>>
>>4.5 hours to check one FK constraint - this is what I meant by bad
>>performance.
>>I'm  looking for a way to suspend FK checks since data is coming from pg_dump
>>and, hence, it's clean.
>>
>>
>>
>
>No solution? Thanks
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>__________________________________
>Do you Yahoo!?
>Protect your identity with Yahoo! Mail AddressGuard
>http://antispam.yahoo.com/whatsnewfree
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>


ow wrote:

>--- ow <oneway_111@yahoo.com> wrote:
>
>
>>Perhaps I should clarify.
>>
>>First, I ran pg_dump to extract schema and data *together*. Then I ran
>>pg_restore to restore the db. It took about 1 hour to create tables and copy
>>the data, then about 40 min to create indexes, then pg_restore spent 4.5
>>hours
>>checking one (1) FK constraint (80M table with FKs against 20K table with
>>PKs)
>>
>>4.5 hours to check one FK constraint - this is what I meant by bad
>>performance.
>>I'm  looking for a way to suspend FK checks since data is coming from pg_dump
>>and, hence, it's clean.
>>
>>
>>
>
>No solution? Thanks
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>__________________________________
>Do you Yahoo!?
>Protect your identity with Yahoo! Mail AddressGuard
>http://antispam.yahoo.com/whatsnewfree
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>



Re: pg_restore and FK constraints with large dbs

From
ow
Date:
--- Rudi Starcevic wrote:

> I'm not trying to upset anyone just trying to help you get an answer for your

> issues.

Who knows what you're trying to do? You did not provide any answers and why you
decided to quote the *same* email *twice* if it was sent only *once* I may
never know.

Anyway, that's not important. What important is that there's no way to suspend
FK checks when restoring a database. Depending on the size of the database, FK
checks alone may add hours or maybe even days to the time that's otherwise
would be necessary to run pg_restore. People with large dbs should be aware of
that.

If one needs to suspend FK checks during db import one may have to consider
another RDBMS ... like mySql, for example.





__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: pg_restore and FK constraints with large dbs

From
Stephan Szabo
Date:
On Sun, 16 Nov 2003, ow wrote:

> --- ow <oneway_111@yahoo.com> wrote:
> > Perhaps I should clarify.
> >
> > First, I ran pg_dump to extract schema and data *together*. Then I ran
> > pg_restore to restore the db. It took about 1 hour to create tables and copy
> > the data, then about 40 min to create indexes, then pg_restore spent 4.5
> > hours
> > checking one (1) FK constraint (80M table with FKs against 20K table with
> > PKs)
> >
> > 4.5 hours to check one FK constraint - this is what I meant by bad
> > performance.
> > I'm  looking for a way to suspend FK checks since data is coming from pg_dump
> > and, hence, it's clean.
> >
>
> No solution? Thanks

By the way, what does your schema look like?  I created an 80M row fk
table and 20K row pk table with an int4 key between them and indexes on
the two key fields.  It took about 25 minutes on my not terribly fast
system using 7.4b5 to make the foreign key between them.  It might have
been faster if I'd raised sort_mem to something larger than 8192.

Re: pg_restore and FK constraints with large dbs

From
Jeff
Date:
On Mon, 17 Nov 2003 10:40:20 -0800 (PST)
Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:

>
> By the way, what does your schema look like?  I created an 80M row fk
> table and 20K row pk table with an int4 key between them and indexes on
> the two key fields.  It took about 25 minutes on my not terribly fast
> system using 7.4b5 to make the foreign key between them.  It might have
> been faster if I'd raised sort_mem to something larger than 8192.
>

i missed the start of this thread... but unless you are running 7.4 adding an FK (restore) is very slow.  ZIt is fixed
in7.4.. especially w/lots of sort mem. 

But the need still exists for a faster backup & restore. I've had a couple threads on this in I think -admin and
-performancelistts. 


> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html


--
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/


Re: pg_restore and FK constraints with large dbs

From
Stephan Szabo
Date:
On Mon, 17 Nov 2003, Jeff wrote:

> On Mon, 17 Nov 2003 10:40:20 -0800 (PST)
> Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
>
> >
> > By the way, what does your schema look like?  I created an 80M row fk
> > table and 20K row pk table with an int4 key between them and indexes on
> > the two key fields.  It took about 25 minutes on my not terribly fast
> > system using 7.4b5 to make the foreign key between them.  It might have
> > been faster if I'd raised sort_mem to something larger than 8192.
> >
>
> i missed the start of this thread... but unless you are running 7.4
> adding an FK (restore) is very slow.  ZIt is fixed in 7.4.. especially
> w/lots of sort mem.

Yeah.  At the start of the discussion I believe 7.4RC2 was mentioned
as the version which is why I'm surprised at the difference in times.

Re: pg_restore and FK constraints with large dbs

From
ow
Date:
--- Jeff <threshar@torgo.978.org> wrote:
> On Mon, 17 Nov 2003 10:40:20 -0800 (PST)
> Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
>
> >
> > By the way, what does your schema look like?  I created an 80M row fk
> > table and 20K row pk table with an int4 key between them and indexes on
> > the two key fields.  It took about 25 minutes on my not terribly fast
> > system using 7.4b5 to make the foreign key between them.  It might have
> > been faster if I'd raised sort_mem to something larger than 8192.
> >

PK and FK keys are of "Dkey" domain, "Dkey" domain is mapped to INT4. There's
PK index on 20K pk table, *NO* index on the fk field on 80M fk table. Why no
index on the fk field? Several reasons: (1) app logic does not call for
accessing fk table based solely on the fk (2) fk field is a part of multi-field
AK index (3) unnecessary indexes take space and slow down inserts/updates,
which is a factor for large tables.

> i missed the start of this thread... but unless you are running 7.4 adding an
> FK (restore) is very slow.  ZIt is fixed in 7.4.. especially w/lots of sort
> mem.

Am running 7.4.rc2 and it takes me about 4.5 hours to verify one (1) FK
constraint.

> But the need still exists for a faster backup & restore.

Can't agree more. Thanks





__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: pg_restore and FK constraints with large dbs

From
Stephan Szabo
Date:
On Mon, 17 Nov 2003, ow wrote:

> --- Jeff <threshar@torgo.978.org> wrote:
> > On Mon, 17 Nov 2003 10:40:20 -0800 (PST)
> > Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> >
> > >
> > > By the way, what does your schema look like?  I created an 80M row fk
> > > table and 20K row pk table with an int4 key between them and indexes on
> > > the two key fields.  It took about 25 minutes on my not terribly fast
> > > system using 7.4b5 to make the foreign key between them.  It might have
> > > been faster if I'd raised sort_mem to something larger than 8192.
> > >
>
> PK and FK keys are of "Dkey" domain, "Dkey" domain is mapped to INT4. There's
> PK index on 20K pk table, *NO* index on the fk field on 80M fk table. Why no
> index on the fk field? Several reasons: (1) app logic does not call for
> accessing fk table based solely on the fk (2) fk field is a part of multi-field
> AK index (3) unnecessary indexes take space and slow down inserts/updates,
> which is a factor for large tables.

I assume you're also not modifying the pktable rows (since that would
access the fk table based solely on the fk). Does the multi-field index
start with fk or some other field? Hmm, the changes in 7.4 for alter
probably don't help as much without an index (it saves the cost of making
all those executors, but that's might be it). Doing, schema, turning off
triggers on the table, data might have worked in general, but if you've
got a textual combined dump that'd be a mess for a workaround (it might be
reasonable in pg_restore, but I don't tend to use it, so I couldn't say)

Re: pg_restore and FK constraints with large dbs

From
ow
Date:
--- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
>
> I assume you're also not modifying the pktable rows (since that would
> access the fk table based solely on the fk).

I do modify the fk table: inserts and updates (but pk is never updated). Also
do not delete records.

> Does the multi-field index start with fk or some other field?
Some other field.

In general, even if I put that additional index and somehow cut down FK
verification time for 1 constraint to, say, 30 min, does that mean that the
problem is solved? Tomorrow, someone may come with 800M fk table against 200K
pk table, or he may have 10 FK constraints like this, etc. How's he going to
restore db in production environment when there's a problem and time is factor?

Thanks







__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: pg_restore and FK constraints with large dbs

From
Stephan Szabo
Date:
On Mon, 17 Nov 2003, ow wrote:

> --- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> >
> > I assume you're also not modifying the pktable rows (since that would
> > access the fk table based solely on the fk).
>
> I do modify the fk table: inserts and updates (but pk is never updated). Also
> do not delete records.
>
> > Does the multi-field index start with fk or some other field?
> Some other field.
>
> In general, even if I put that additional index and somehow cut down FK
> verification time for 1 constraint to, say, 30 min, does that mean that the
> problem is solved? Tomorrow, someone may come with 800M fk table against 200K
> pk table, or he may have 10 FK constraints like this, etc. How's he going to
> restore db in production environment when there's a problem and time is factor?

Well, I've been trying to deal with the particular case at hand, ie, get
your load in a more reasonable amount of time assuming that you had more
constraints and this was going to be a particular problem for you in the
immediate term.  I've mentioned that there's likely to be future
development on the issue in this thread already and that there was a
recent discussion on the topic that occurred a bit late to do anything for
7.4.  I can't change the past to suit your desires, sorry, the best I can
do is point you towards getting a solution for the future.

Re: pg_restore and FK constraints with large dbs

From
ow
Date:
--- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> Well, I've been trying to deal with the particular case at hand, ie, get
> your load in a more reasonable amount of time assuming that you had more
> constraints and this was going to be a particular problem for you in the
> immediate term.  I've mentioned that there's likely to be future
> development on the issue in this thread already and that there was a
> recent discussion on the topic that occurred a bit late to do anything for
> 7.4.  I can't change the past to suit your desires, sorry, the best I can
> do is point you towards getting a solution for the future.
>

Thanks, you've been very helpful and I appreciate the effort.

In case if there's no immediate solution, the solution could be something like
"yes, we understand the importance of the raised issue and we intent to
implement a fix for it in 7.5/8.0/or whatever".

Personally, I see the fix as the ability for the superuser to issue "SET
suspend_fk_checks(or whatever)=true/false" on the respective connection and
perhaps the respective pg_restore option.

Anything I can do to make this happen? (am not a C-developer but perhaps could
help with testing).

Thanks





__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: pg_restore and FK constraints with large dbs

From
Stephan Szabo
Date:
On Mon, 17 Nov 2003, ow wrote:

> --- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> > Well, I've been trying to deal with the particular case at hand, ie, get
> > your load in a more reasonable amount of time assuming that you had more
> > constraints and this was going to be a particular problem for you in the
> > immediate term.  I've mentioned that there's likely to be future
> > development on the issue in this thread already and that there was a
> > recent discussion on the topic that occurred a bit late to do anything for
> > 7.4.  I can't change the past to suit your desires, sorry, the best I can
> > do is point you towards getting a solution for the future.
> >
>
> Thanks, you've been very helpful and I appreciate the effort.
>
> In case if there's no immediate solution, the solution could be something like
> "yes, we understand the importance of the raised issue and we intent to
> implement a fix for it in 7.5/8.0/or whatever".

Like I said, it's been discussed and I would expect some form of this for
7.5 although I can't say for certain.  Enough people were interested in
the discussion that it's likely to happen with a little championing.

> Personally, I see the fix as the ability for the superuser to issue "SET
> suspend_fk_checks(or whatever)=true/false" on the respective connection and
> perhaps the respective pg_restore option.

That was one of the options mentioned in the thread I talked about before.
There's still some question about whether it'd affect all checks or just
ones from things like alter table.

> Anything I can do to make this happen? (am not a C-developer but perhaps could
> help with testing).

Well, generally looking through the archives and making a reasonable
proposal on -general or -hackers is a good start.  The code for this is
unlikely to be difficult, but there's alot of behavior to decide. Things
to think about are things like what checks do we want to disable, who can
do so and what're the mechanisms for doing so, for example, alter time
checks disabled by superusers via a set option.  And then thinking about
the limitations.

Re: pg_restore and FK constraints with large dbs

From
ow
Date:
--- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> Like I said, it's been discussed and I would expect some form of this for
> 7.5 although I can't say for certain.  Enough people were interested in
> the discussion that it's likely to happen with a little championing.

Does not appear like it's on the TODO list though ...

Thanks





__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Upgrade from 7.2 to 7.4

From
Chris Gamache
Date:
I'm trying to avoid having to install 7.3, dump 7.2 and reload 7.3, then
install 7.4, dump 7.3 and install to 7.4 ... What's the chances of just using
the 7.4 pg_dumpall on the 7.2 database and having it work 100% ?

Also, I recall that when upgrading from 7.2 to 7.3 there was something that had
to be run to bootstrap the links between sequences and their respective tables
such that if the table was dropped the sequence would go with it. What is that
thing called? Is it included in the 7.4 tarball?



__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: pg_restore and FK constraints with large dbs

From
Stephan Szabo
Date:
On Mon, 17 Nov 2003, ow wrote:

> --- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> > Like I said, it's been discussed and I would expect some form of this for
> > 7.5 although I can't say for certain.  Enough people were interested in
> > the discussion that it's likely to happen with a little championing.
>
> Does not appear like it's on the TODO list though ...

Hmm, that's somewhat surprising, I'd thought that the consensus was that
we were going to be doing something even if we weren't entirely sure what.
Am I right about that, Bruce?

Re: pg_restore and FK constraints with large dbs

From
Bruce Momjian
Date:
Stephan Szabo wrote:
>
> On Mon, 17 Nov 2003, ow wrote:
>
> > --- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> > > Like I said, it's been discussed and I would expect some form of this for
> > > 7.5 although I can't say for certain.  Enough people were interested in
> > > the discussion that it's likely to happen with a little championing.
> >
> > Does not appear like it's on the TODO list though ...
>
> Hmm, that's somewhat surprising, I'd thought that the consensus was that
> we were going to be doing something even if we weren't entirely sure what.
> Am I right about that, Bruce?

Yep, we want to do something, but not sure what.  We have:

    * Allow triggers to be disabled [trigger]

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Upgrade from 7.2 to 7.4

From
Peter Eisentraut
Date:
Chris Gamache writes:

> I'm trying to avoid having to install 7.3, dump 7.2 and reload 7.3, then
> install 7.4, dump 7.3 and install to 7.4 ... What's the chances of just using
> the 7.4 pg_dumpall on the 7.2 database and having it work 100% ?

Pretty good.  (At least no worse than going via 7.3.)  If it does not
work, it's cause for a bug report.

> Also, I recall that when upgrading from 7.2 to 7.3 there was something that had
> to be run to bootstrap the links between sequences and their respective tables
> such that if the table was dropped the sequence would go with it. What is that
> thing called? Is it included in the 7.4 tarball?

Yes, contrib/adddepend.

--
Peter Eisentraut   peter_e@gmx.net


Re: pg_restore and FK constraints with large dbs

From
ow
Date:
--- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>
> Yep, we want to do something, but not sure what.  We have:
>
>     * Allow triggers to be disabled [trigger]
>

Will ppl remember some time from now that it also deals with FK constraints and
pg_restore/dump options?

Thanks




__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: pg_restore and FK constraints with large dbs

From
Bruce Momjian
Date:
ow wrote:
>
> --- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> >
> > Yep, we want to do something, but not sure what.  We have:
> >
> >     * Allow triggers to be disabled [trigger]
> >
>
> Will ppl remember some time from now that it also deals with FK constraints and
> pg_restore/dump options?

We probably need a multi-option approach, where you can disable all
triggers, constraints, and foreign keys depending on your needs.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: pg_restore and FK constraints with large dbs

From
ow
Date:
--- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> ow wrote:
> >
> > --- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> > >
> > > Yep, we want to do something, but not sure what.  We have:
> > >
> > >     * Allow triggers to be disabled [trigger]
> > >

I think the following should be added to the "Allow triggers to be disabled
[trigger]" links:

http://archives.postgresql.org/pgsql-admin/2003-11/msg00323.php
http://archives.postgresql.org/pgsql-admin/2003-11/msg00313.php

Thanks






__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree