pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction Vanished
5+ messages / 3 participants
[nested] [flat]
* Function Vanished
@ 2001-03-26 19:55 Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2001-03-26 19:55 UTC (permalink / raw)
To: pgsql-sql
Tom, Jan,
I've already contacted PGSQL Inc. support about this, but I thought
that you might have some immediate insight.
I'm using 7.1 Beta 3, which has been pretty stable up until now. This
morning, I went to export a function I spent 5 hours debugging on on
Friday to text so that I could have a copy of the final version. To my
horror, the function was GONE from the system catalog (pg_proc). I have
not run VACUUM on the database anytime recently ... is there any
possibility that my function is still present as a "deleted" row?
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function Vanished
@ 2001-03-26 21:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 5+ messages in thread
From: Tom Lane @ 2001-03-26 21:09 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
"Josh Berkus" <josh@agliodbs.com> writes:
> I'm using 7.1 Beta 3, which has been pretty stable up until now. This
> morning, I went to export a function I spent 5 hours debugging on on
> Friday to text so that I could have a copy of the final version. To my
> horror, the function was GONE from the system catalog (pg_proc).
Ick. Were you maybe working on it inside a transaction that you forgot
to commit?
> I have
> not run VACUUM on the database anytime recently ... is there any
> possibility that my function is still present as a "deleted" row?
Should still be there in the table, if you haven't vacuumed. Getting
it out again is another story though. If it was a small enough function,
good ol' "strings" would do to extract the function body, which is
probably all that you really need. But if it's more than a couple K
then the text will be compressed and difficult to spot or extract.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function Vanished
@ 2001-03-26 21:25 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2001-03-26 21:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
Tom,
> Ick. Were you maybe working on it inside a transaction that you
> forgot
> to commit?
Nope. Friday was debugging work; the function had already been saved as
a buggy version. I can even find the last buggy call to the function,
on Friday, in the logs.
I do have a copy of the buggy version, but if I can get the debugged
version back ...
> Should still be there in the table, if you haven't vacuumed. Getting
> it out again is another story though. If it was a small enough
> function,
> good ol' "strings" would do to extract the function body, which is
> probably all that you really need. But if it's more than a couple K
> then the text will be compressed and difficult to spot or extract.
Can you explain that? I think that the text may be short entough to be
uncompressed.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function Vanished
@ 2001-03-26 21:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2001-03-26 21:35 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
"Josh Berkus" <josh@agliodbs.com> writes:
>> Should still be there in the table, if you haven't vacuumed. Getting
>> it out again is another story though. If it was a small enough
>> function,
>> good ol' "strings" would do to extract the function body, which is
>> probably all that you really need. But if it's more than a couple K
>> then the text will be compressed and difficult to spot or extract.
> Can you explain that? I think that the text may be short entough to be
> uncompressed.
Try running strings(1) on the pg_proc table, which will be ... hmm ...
$PGDATA/base/YOURDBOID/1255. Look in pg_database if you're not sure of
the OID of the database you are using.
If you have not vacuumed then the latest version of the row will be the
one closest to the front of the last 8K page of the file (got that? ;-))
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function Vanished
@ 2001-03-27 03:58 Justin Clift <aa2@bigpond.net.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Justin Clift @ 2001-03-27 03:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-sql
Hi would it work to do a pg_dump -d or a pgdumpall, then look through
the dumped sql file?
I do that to retrieve PL/pgSQL functions from the database when I've
accidentally wiped or modified the source (not often, but it happens).
Regards and best wishes,
Justin Clift
Tom Lane wrote:
>
> "Josh Berkus" <josh@agliodbs.com> writes:
> > I'm using 7.1 Beta 3, which has been pretty stable up until now. This
> > morning, I went to export a function I spent 5 hours debugging on on
> > Friday to text so that I could have a copy of the final version. To my
> > horror, the function was GONE from the system catalog (pg_proc).
>
> Ick. Were you maybe working on it inside a transaction that you forgot
> to commit?
>
> > I have
> > not run VACUUM on the database anytime recently ... is there any
> > possibility that my function is still present as a "deleted" row?
>
> Should still be there in the table, if you haven't vacuumed. Getting
> it out again is another story though. If it was a small enough function,
> good ol' "strings" would do to extract the function body, which is
> probably all that you really need. But if it's more than a couple K
> then the text will be compressed and difficult to spot or extract.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-03-27 03:58 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-26 19:55 Function Vanished Josh Berkus <josh@agliodbs.com>
2001-03-26 21:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-26 21:25 ` Josh Berkus <josh@agliodbs.com>
2001-03-26 21:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-27 03:58 ` Justin Clift <aa2@bigpond.net.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox