table dump 
Author Message
 table dump

This is a multi-part message in MIME format.

------=_NextPart_000_009D_01C1DFAE.C0DC2140
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I just completed a data only dump of a table by using the following command:

pg_dump genex -Rau -t species > species.sql

I noticed that the first line of the file seems to be disabling some postgr=
es trigger. It reads:

UPDATE "pg_class" SET "reltriggers" =3D 0 WHERE "relname" =3D 'species';

Then all the data is listed.

At the end of the file the triggers are enabled again with the following co=
mmand:

UPDATE pg_class SET reltriggers =3D (SELECT count(*) FROM pg_trigger where =
pg_class.oid =3D tgrelid) WHERE relname =3D 'species';

Can someone explain why this is happening? The problem is that I am coping =
this dump into another schema creation file that I have. I want the data fr=
om this particular file included in with my new database. However, I am cre=
ating this database with a user account so that all tables will be owned by=
 that user. I think the problem here is that this trigger stuff requires th=
at you be signed in as postgres in order to do anything with the pg_class t=
able. correct?

Any assistane would be greatly appreciated.
thanks,
Jodi

_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of {*filter*}ia
(434) 924-2846

=20

=20

=20

------=_NextPart_000_009D_01C1DFAE.C0DC2140
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.3315.2870" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#d8d0c8>
<DIV><FONT face=3D"Times New Roman">I just completed a data only dump of a =
table=20
by using the following command:</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3D"Times New Roman">pg_dump&nbsp;genex -Rau -t&nbsp;species=
 &gt;=20
species.sql</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3D"Times New Roman">I noticed that the first line of the fi=
le=20
seems to be disabling some postgres trigger. It reads:</FONT></DIV>
<DIV><FONT face=3D"Times New Roman"></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Times New Roman">UPDATE "pg_class" SET "reltriggers" =3D=
 0 WHERE=20
"relname" =3D 'species';</FONT></DIV>
<DIV><FONT face=3D"Times New Roman"></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Times New Roman">Then all the data is listed.</FONT></DI=
V>
<DIV><FONT face=3D"Times New Roman"></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Times New Roman">At the end of the file the triggers are=
=20
enabled again with the following command:</FONT></DIV>
<DIV><FONT face=3D"Times New Roman"></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Times New Roman">UPDATE pg_class SET reltriggers =3D (SE=
LECT=20
count(*) FROM pg_trigger where pg_class.oid =3D tgrelid) WHERE relname =3D=
=20
'species';</FONT></DIV>
<DIV><FONT face=3D"Times New Roman"></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Times New Roman">Can someone explain why this is happeni=
ng? The=20
problem is that I am coping this dump into another schema creation file tha=
t I=20
have. I want the data from this particular file included in with my new=20
database. However, I am creating this database with a user account so that =
all=20
tables will be owned by that user. I think the problem here is that this tr=
igger=20
stuff requires that you be signed in as postgres in order to do anything wi=
th=20
the pg_class table. correct?</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3D"Times New Roman">Any assistane would be greatly=20
appreciated.</FONT></DIV>
<DIV><FONT face=3D"Times New Roman">thanks,</FONT></DIV>
<DIV><FONT face=3D"Times New Roman">Jodi</FONT></DIV>
<DIV><FONT face=3D"Times New Roman"></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Times New Roman"><BR>&nbsp;</DIV></FONT>
<DIV>&nbsp;</DIV>
<DIV>
<DIV class=3DSection1>
<P class=3DMsoNormal><I><SPAN=20
style=3D"FONT-FAMILY: Arial; FONT-SIZE: 9pt; mso-bidi-font-size: 12.0pt; ms=
o-fareast-font-family: 'MS Mincho'">_______________________________<BR></SP=
AN></I><I><SPAN=20
style=3D"FONT-SIZE: 10pt; mso-bidi-font-size: 12.0pt; mso-fareast-font-fami=
ly: 'MS Mincho'">Jodi=20
L Kanter<BR>BioInformatics Database Administrator<BR>University of=20
{*filter*}ia<BR>(434) 924-2846<BR><A=20

AN=20
style=3D"FONT-FAMILY: Arial; FONT-SIZE: 11pt; mso-bidi-font-size: 12.0pt; m=
so-fareast-font-family: 'MS Mincho'"><BR=20
style=3D"mso-special-character: line-break"><BR=20
style=3D"mso-special-character: line-break"><?xml:namespace prefix =3D o ns=
 =3D=20
"urn:schemas-microsoft-com:office:office" /><o:p></o:p></SPAN></P>
<P class=3DMsoNormal><SPAN=20
style=3D"FONT-FAMILY: Arial; FONT-SIZE: 11pt; mso-bidi-font-size: 12.0pt; m=
so-fareast-font-family: 'MS Mincho'">&nbsp;<o:p></o:p></SPAN></P>
<P class=3DMsoNormal><I><SPAN=20
style=3D"FONT-FAMILY: Arial; FONT-SIZE: 9pt; mso-bidi-font-size: 12.0pt; ms=
o-fareast-font-family: 'MS Mincho'">&nbsp;<o:p></o:p></SPAN></I></P>
<P class=3DMsoNormal><I><SPAN=20
style=3D"FONT-FAMILY: Arial; FONT-SIZE: 9pt; mso-bidi-font-size: 12.0pt; ms=
o-fareast-font-family: 'MS Mincho'">&nbsp;<o:p></o:p></SPAN></I></P></DIV><=
/DIV></BODY></HTML>

------=_NextPart_000_009D_01C1DFAE.C0DC2140--



Sat, 25 Sep 2004 22:51:56 GMT
 table dump

Quote:

> I noticed that the first line of the file seems to be disabling some postgr=
> es trigger. It reads:

> UPDATE "pg_class" SET "reltriggers" =3D 0 WHERE "relname" =3D 'species';

> Then all the data is listed.

> At the end of the file the triggers are enabled again with the following co=
> mmand:

> UPDATE pg_class SET reltriggers =3D (SELECT count(*) FROM pg_trigger where =
> pg_class.oid =3D tgrelid) WHERE relname =3D 'species';

It wants to disable your trigger, because it will make dumping the data quicker...

Quote:
> Can someone explain why this is happening? The problem is that I am coping =
> this dump into another schema creation file that I have. I want the data fr=
> om this particular file included in with my new database. However, I am cre=
> ating this database with a user account so that all tables will be owned by=
>  that user. I think the problem here is that this trigger stuff requires th=
> at you be signed in as postgres in order to do anything with the pg_class t=
> able. correct?

Why not just stick with COPY then?
Just do:
copy species to 'species_dump.txt';
then, in the other database:
copy species from 'species_dump.txt'

I hope, it helps...

Dima



Sun, 26 Sep 2004 00:32:19 GMT
 table dump

Quote:

> I just completed a data only dump of a table by using the following command:

> pg_dump genex -Rau -t species > species.sql

> I noticed that the first line of the file seems to be disabling some
> postgres trigger. It reads:

> UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'species';

> Then all the data is listed.

> At the end of the file the triggers are enabled again with the
> following command:

> UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger
> where pg_class.oid = tgrelid) WHERE relname = 'species';

> Can someone explain why this is happening? The problem is that I am
> coping this dump into another schema creation file that I have. I want
> the data from this particular file included in with my new database.
> However, I am creating this database with a user account so that all
> tables will be owned by that user. I think the problem here is that
> this trigger stuff requires that you be signed in as postgres in order
> to do anything with the pg_class table. correct?

You'd need to be a superuser I'd guess.  The reason for those statmenets
is to turn off foreign key constraints during the load of what's assumed
to be correct data since it came from a dump.  You can ignore them if you
don't have any or don't mind having the constraints run.

---------------------------(end of broadcast)---------------------------



Sun, 26 Sep 2004 01:37:06 GMT
 table dump
Hi Jodi,

    Its to stop the triggers firing when you restore the new database from
the dumped file.

     Imagine that the triggers have already done their work on other tables
when the data was first inserted or
    later changed.

     With pg_dump you are taking a snap shot that you would later wish
perhaps to restore.

    The foreign key, table and column constraints are not turned off because
their enforcement is still valid at         restore time. For example the
data may have mysteriously been damaged in the database or in the dump file
and you would thus need to know about this situation at restore time.

    The dumped file is thus copied back at restore time not inserted.

    Regards Mark Carew
    Brisbane Australia.



Wed, 29 Sep 2004 15:45:03 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Table Dump

2. Gremlins in table dump/restore

3. Table Dump and Load Problem

4. Regarding table dump through vb

5. Table Dump

6. fp tables dumped out to access

7. Table Dump

8. after dump/restore all objects req db.table.elem instead of table.elem

9. disk dump only restored tables not table data

10. disk dump only restored table not table data

11. dump/load - dump out of sequence.

12. Dumping big DB to disk dump device


 
Powered by phpBB® Forum Software