Обсуждение: Delete w/ regexp

   On one of our tables, this works fine:

select * from images where path ~* 'packages/site_system';

  However, this:

delete from images where path ~* 'packages/site_system';

  will make it work for a second or two, then give:

pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

   This is with Postgres 7.0.2.  Any ideas what's going on?

steve

  This gets even better:

delete from images where image_id=30031;

 gives:

pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

  I can vacuum the table just fine.  I did a pg_dump on the entire database,
did destroydb and createdb, and piped in the output from pg_dump, all worked
without any complaint or error - but trying to delete from this table still
horks it up.  Suggestions?

steve

Do you have any triggers or fk constraints that reference
the table?

On Thu, 19 Apr 2001, Steve Wolfe wrote:

>
>    On one of our tables, this works fine:
>
> select * from images where path ~* 'packages/site_system';
>
>   However, this:
>
> delete from images where path ~* 'packages/site_system';
>
>   will make it work for a second or two, then give:
>
> pqReadData() -- backend closed the channel unexpectedly.
>         This probably means the backend terminated abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
>
>    This is with Postgres 7.0.2.  Any ideas what's going on?

> Do you have any triggers or fk constraints that reference
> the table?

  No triggers or FK.  The primary key has a 'default nextval('images_seq')',
but other than that, nothing.

steve

"Steve Wolfe" <steve@iboats.com> writes:
> Suggestions?

Show us a gdb backtrace from the backend core dump?

            regards, tom lane

> > Suggestions?
>
> Show us a gdb backtrace from the backend core dump?

  Sounds like a good suggestion to me. : )

   Where would the core file be found?  I could not find it in the working
directory, in the home directory, or under /usr/local/pgsql.  I have a few
other ideas, I'll investigate and report if I find anything interesting.


steve

"Steve Wolfe" <steve@iboats.com> writes:
>> Show us a gdb backtrace from the backend core dump?

>   Sounds like a good suggestion to me. : )

>    Where would the core file be found?

Should be in $PGDATA/base/YOURDB/core.

If it's not there, then either (a) the backend is not actually crashing,
but doing a semi-controlled exit --- check the postmaster log to find
out; or (b) no core dump is being made because you started the
postmaster with ulimit settings that forbid a core dump.  (b) is pretty
likely on Linux machines because "ulimit -c 0" is the standard context
for system boot scripts.  Try restarting the postmaster with "ulimit -c
unlimited" (I'd suggest putting this into the PG startup script).

            regards, tom lane

> >> Show us a gdb backtrace from the backend core dump?
>
> >   Sounds like a good suggestion to me. : )
>
> >    Where would the core file be found?
>
> Should be in $PGDATA/base/YOURDB/core.

  Hmm... well, never mind. : )

   Today, after the nightly vacuum, it works.  I don't know why, it didn't
work after repeated vacuums, dumps, restores, and vacuums, but hey... I
won't complain.

steve