FAQ
i used to have a database on freebsd using 6.5.3.

i had several concurrent processes which would do inserts via COPY and
queries.

on that system, i don't recall the COPY processes as being blocked by the
query processes.

now i'm running that app on solaris 7 with pgsql 7.0.

i'm finding that a big long select is blocking other processes which
are doing COPY's.

i'm also finding that other queries are blocking.

the only real difference between what was running before and what is
running now is the use of an ORDER BY clause in the big long select, since 7.0
seems to need this to return records in the same order as 6.5.3 (i know, i shouldhave
been using the ORDER BY in 6.5.3, but, such is the way it is).


is the blocking i'm seeing supposed to be happening?

or did i miss some flag or something when installing on solaris?


--
[ Jim Mercer jim@reptiles.org +1 416 410-5633 ]
[ Reptilian Research -- Longer Life through Colder Blood ]
[ Don't be fooled by cheap Finnish imitations; BSD is the One True Code. ]

Search Discussions

  • Tom Lane at May 18, 2000 at 12:20 am

    Jim Mercer writes:
    i had several concurrent processes which would do inserts via COPY and
    queries.
    on that system, i don't recall the COPY processes as being blocked by the
    query processes.
    now i'm running that app on solaris 7 with pgsql 7.0.
    i'm finding that a big long select is blocking other processes which
    are doing COPY's.
    Hmm. In 7.0, COPY IN acquires an exclusive lock on the target table,
    which is something I put in in a fit of paranoia. It may not really
    be necessary --- probably a regular write lock would be good enough.
    (6.5's COPY code neglected to acquire any lock at all, which is surely
    *not* good enough, but maybe I overreacted.)

    Comments anyone?

    regards, tom lane
  • Tom Lane at May 18, 2000 at 1:58 am

    Jim Mercer writes:
    i had several concurrent processes which would do inserts via COPY and
    queries.
    on that system, i don't recall the COPY processes as being blocked by the
    query processes.
    now i'm running that app on solaris 7 with pgsql 7.0.
    i'm finding that a big long select is blocking other processes which
    are doing COPY's.
    Hmm. In 7.0, COPY IN acquires an exclusive lock on the target table,
    which is something I put in in a fit of paranoia. It may not really
    be necessary --- probably a regular write lock would be good enough.
    OK, fix committed. Jim, if you're in a hurry for this fix, just change
    AccessExclusiveLock to RowExclusiveLock at line 289 of
    backend/commands/copy.c.

    regards, tom lane
  • Bruce Momjian at May 18, 2000 at 2:33 am

    Jim Mercer writes:
    i had several concurrent processes which would do inserts via COPY and
    queries.
    on that system, i don't recall the COPY processes as being blocked by the
    query processes.
    now i'm running that app on solaris 7 with pgsql 7.0.
    i'm finding that a big long select is blocking other processes which
    are doing COPY's.
    Hmm. In 7.0, COPY IN acquires an exclusive lock on the target table,
    which is something I put in in a fit of paranoia. It may not really
    be necessary --- probably a regular write lock would be good enough.
    OK, fix committed. Jim, if you're in a hurry for this fix, just change
    AccessExclusiveLock to RowExclusiveLock at line 289 of
    backend/commands/copy.c.
    FYI, I have been telling people to grab tomorrow's snapshot from
    ftp:/pub/dev if they need changes that have been applied. At this
    point, we don't have any funny stuff in the cvs tree.

    --
    Bruce Momjian | http://www.op.net/~candle
    pgman@candle.pha.pa.us | (610) 853-3000
    + If your life is a hard drive, | 830 Blythe Avenue
    + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
  • Bruce Momjian at May 18, 2000 at 2:32 am

    Jim Mercer writes:
    i had several concurrent processes which would do inserts via COPY and
    queries.
    on that system, i don't recall the COPY processes as being blocked by the
    query processes.
    now i'm running that app on solaris 7 with pgsql 7.0.
    i'm finding that a big long select is blocking other processes which
    are doing COPY's.
    Hmm. In 7.0, COPY IN acquires an exclusive lock on the target table,
    which is something I put in in a fit of paranoia. It may not really
    be necessary --- probably a regular write lock would be good enough.
    (6.5's COPY code neglected to acquire any lock at all, which is surely
    *not* good enough, but maybe I overreacted.)
    I see no reason a write lock would not be good enough, unless we do some
    special stuff in copy which I have forgotten.

    --
    Bruce Momjian | http://www.op.net/~candle
    pgman@candle.pha.pa.us | (610) 853-3000
    + If your life is a hard drive, | 830 Blythe Avenue
    + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
  • Mikheev, Vadim at May 18, 2000 at 12:39 am

    Hmm. In 7.0, COPY IN acquires an exclusive lock on the target table,
    which is something I put in in a fit of paranoia. It may not really
    be necessary --- probably a regular write lock would be good enough.
    (6.5's COPY code neglected to acquire any lock at all, which is surely
    *not* good enough, but maybe I overreacted.)
    Oh, seems I forgot about COPY in 6.5... -:(
    ROW EXCLUSIVE lock is required (just like for INSERT, DELETE, UPDATE)...

    Vadim
  • Tom Lane at May 18, 2000 at 12:41 am

    "Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
    ROW EXCLUSIVE lock is required (just like for INSERT, DELETE, UPDATE)...
    OK, will fix (I have another little fix to make in copy.c anyway)

    regards, tom lane
  • Jim Mercer at May 18, 2000 at 12:56 am

    On Wed, May 17, 2000 at 08:41:25PM -0400, Tom Lane wrote:
    "Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
    ROW EXCLUSIVE lock is required (just like for INSERT, DELETE, UPDATE)...
    OK, will fix (I have another little fix to make in copy.c anyway)
    can i get a patch relative to 7.0-release?

    this is effecting a production database.

    --
    [ Jim Mercer jim@reptiles.org +1 416 410-5633 ]
    [ Reptilian Research -- Longer Life through Colder Blood ]
    [ Don't be fooled by cheap Finnish imitations; BSD is the One True Code. ]

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMay 17, '00 at 11:45p
activeMay 18, '00 at 2:33a
posts8
users4
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase