Thread: pg_dump behaviour changed in 7.1.3?

Hi!

I'm not sure if this message reached you because I received an error
notification. I will simply post it again ...
I've encountered a problem during use of pg_dump: The database contains
several tables and some referential integrities between them, i.e. some
triggers. We use a pg_dump to backup the database to a file. Now I
noticed that the dump doesn't contain the triggers! I tried to find out
what the problem is and have come to this solution: The backup is done
by a user that doesn't own the tables. He has read and write permission
to the tables, but that does no longer seem to be enough. But I don't
know what permissions are necessary to perform the dump. And I don't
know how to set permission for triggers -- they are not even listed when
I type "\d"!
The problem is new since we updated from server version 7.1.2 to 7.1.3.
In 7.1.2 the dump was complete (including the triggers) and in 7.1.3 it
is not.
Hope you can help me with this!

Regards

    Christian

Christian =?iso-8859-1?Q?Schr=F6der?= <cs@theta-soft.de> writes:
> I've encountered a problem during use of pg_dump: The database contains
> several tables and some referential integrities between them, i.e. some
> triggers. We use a pg_dump to backup the database to a file. Now I
> noticed that the dump doesn't contain the triggers!

[ thinks ... ]  Are these triggers on plain tables, or views?

            regards, tom lane

I wrote:
> Christian =?iso-8859-1?Q?Schr=F6der?= <cs@theta-soft.de> writes:
>> I've encountered a problem during use of pg_dump: The database contains
>> several tables and some referential integrities between them, i.e. some
>> triggers. We use a pg_dump to backup the database to a file. Now I
>> noticed that the dump doesn't contain the triggers!

> [ thinks ... ]  Are these triggers on plain tables, or views?

Er, scratch that: views don't have triggers.  Not usefully, anyway.

Still, I can't see any change between 7.1.2 and 7.1.3 that would affect
dumping of triggers.  We did rejigger dumping of views, which is what
led to my question.  I don't really believe it's a permissions issue
as you seem to think --- a permission violation would lead to an
outright error.  I think you're going to have to delve into it yourself
and try to figure out why pg_dump isn't seeing those triggers.

            regards, tom lane

Tom Lane wrote:

> > I've encountered a problem during use of pg_dump: The database contains
> > several tables and some referential integrities between them, i.e. some
> > triggers. We use a pg_dump to backup the database to a file. Now I
> > noticed that the dump doesn't contain the triggers!
>
> [ thinks ... ]  Are these triggers on plain tables, or views?

Triggers on tables. As I explained they are the triggers which have been
auto-created to check referential integrity.

Regards,

    Christian