Re: Function will not back up on 7.2.3

Affects: 7.1.3, 7.2.1 to 7.2.3, not tested on 7.3.x or 7.4dev
Frequency: 100% Reproducable
Effect When Occurring: Object Missing from Backup
Difficulty of Fix: Unknown

For some time, I've been noticing that one of my database projects fails to
back up a few functions every time I run pg_dump. I've seen this since
7.1.3. Finally, I have a sample of the database that invariably refuses to
back up one function.

Given that the database in question uses functions that call other functions
that call views, it's probably some sort of dependancy issue. What can I
ship people so that we can resolve this?

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> For some time, I've been noticing that one of my database projects fails to
> back up a few functions every time I run pg_dump. I've seen this since
> 7.1.3. Finally, I have a sample of the database that invariably refuses to
> back up one function.

What do you mean by "refuses"?

regards, tom lane

Tom,

> > For some time, I've been noticing that one of my database projects fails
to
> > back up a few functions every time I run pg_dump. I've seen this since
> > 7.1.3. Finally, I have a sample of the database that invariably refuses
to
> > back up one function.
>
> What do you mean by "refuses"?

The function is silently dropped from the pg_dump file. This happens in both
binary and sql-script modes, and I've tracked the log to see if pg_dump is
reporting an error to postmaster. No luck.

But I'll try later to see if 7.3.2 fixes this or 7.4 devel.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> What do you mean by "refuses"?

> The function is silently dropped from the pg_dump file.

Is it possible that the function's owner has been dropped from pg_shadow?
How about dropped return type, etc? pg_dump used to use inner joins to
collect info about database objects, meaning it would silently miss
objects that were missing expected collateral objects.

(I thought we'd fixed that as of 7.2, but maybe it was later.)

regards, tom lane

Tom,

> Is it possible that the function's owner has been dropped from pg_shadow?

No, the function owner is the database owner ... and also the same user
calling pg_dump.

> How about dropped return type, etc? pg_dump used to use inner joins to
> collect info about database objects, meaning it would silently miss
> objects that were missing expected collateral objects.

Return type is TEXT, so I think that's OK too. However, this database does
have some pretty complex dependancies.

I just tested. This is still a bug in 7.3.0. I will download and test 7.3.2
now.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Folks,

This bug in 7.2.3 and 7.3.0 seems to have been fixed as a side effect of some
of the other fixes in 7.2.4 and 7.3.2. We're not sure exactly *how*, but the
bug occurs on 7.2.3 and not on 7.2.4.

Did anybody do anything to patch dependancy tracking 7.2.3 ==> 7.2.4?

--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco