All,
One other problem with this case, those 900K worth of files in each of the table and index directories (1.8M total files) are still hanging around. I have:
* fixed the and reloaded the stored procedure
* restarted the database
* ran the stored procedure
* there are only 378 rows in the pg_class table
How do I get rid of those other files?
Just a guess, but do I shutdown the database, and delete any file not listed in pg_class? I do not see anything in the PostgreSQL documentation about this.
Thank again.
On Tue, Jan 28, 2014 at 9:47 AM, Peter Blair <petertblair@xxxxxxxxx> wrote:
Tom,You are correct. The was an infinate loop created because of the differences in the date math between Oracle and Postgres.Thank again for your help.On Mon, Jan 27, 2014 at 7:43 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Peter Blair <petertblair@xxxxxxxxx> writes:Is that "it's known to terminate if you give it a week", or "we've let
> Have a problem where a stored procedure is taking a week to run. The
> stored procedure should take less than a second to run.
it run for a week and it shows no sign of ever terminating"?
I'd bet on the last, given that you're apparently working with an immature
> In researching a
> select hanging problem, three things are suggested; an autovacuum problem,
> a resource is locked, or there is something wrong with the stored procedure.
port from Oracle. The error recovery semantics, in particular, are enough
different in PL/SQL and PL/pgSQL that it's not too hard to credit having
accidentally written an infinite loop via careless translation.
If the filenames are just numbers, then they must be actual tables or
> Lastly, in the directories used to store the tables and indexes, there are
> 918896 files in the tables directory and 921291 files in the indexes
> directory. All of the file names are just numbers (no extensions). About
> 60 files are added to each directory every second. On our test systems and
> at our other customer site, there are only about 50 files in each directory.
> Why are there so many files?
indexes, not temp files. (You could cross-check that theory by noting
whether the system catalogs, such as pg_class, are bloating at a
proportional rate.) I'm guessing that there's some loop in your procedure
that's creating new temp tables, or maybe even non-temp tables. You would
not be able to see them via "select * from pg_class" in another session
because they're not committed yet, but they'd be taking up filesystem
entries. The loop might or might not be dropping the tables again; IIRC
the filesystem entries wouldn't get cleaned up till end of transaction
even if the tables are nominally dropped.
Not much to go on, but I'd look for a loop that includes a CREATE TABLE
and a BEGIN ... EXCEPT block, and take a close look at the conditions
under which the EXCEPT allows the loop to continue.
regards, tom lane
