Re: Multiple COPY on the same table - Mailing list pgsql-general
| From | Christopher Browne |
|---|---|
| Subject | Re: Multiple COPY on the same table |
| Date | |
| Msg-id | CAFNqd5X+Xiu00Xb2rMAv=p0XSVbF+NM5YHcCTr=EkSr+dxmjog@mail.gmail.com Whole thread |
| In response to | Re: Multiple COPY on the same table (Adrian Klaver <adrian.klaver@aklaver.com>) |
| List | pgsql-general |
On Mon, 20 Aug 2018 at 16:23, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 08/20/2018 08:56 AM, Nicolas Paris wrote:
> >> Can I split a large file into multiple files and then run copy using
> >> each file.
> >
> > AFAIK, copy command locks the table[1] while there is no mention of this
> > in the documentation[2].
>
> [1] Is from Postgres 7.1(17 years ago). I suspect the conditions have
> changed at least a little:).
oxrsdb-generated@localhost-> create temp table foo ();
CREATE TABLE
oxrsdb-generated@localhost-> begin;
BEGIN
oxrsdb-generated@localhost-> * \copy foo from '/dev/null';
COPY 0
oxrsdb-generated@localhost-> * select oid, relname from pg_class where
relname = 'foo';
oid | relname
--------+---------
350686 | foo
(1 row)
oxrsdb-generated@localhost-> * select * from pg_locks ;
locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction | pid
| mode | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
relation | 345742 | 11713 | | | |
| | | | 2/200573 | 16754 |
AccessShareLock | t | t
relation | 345742 | 3455 | | | |
| | | | 2/200573 | 16754 |
AccessShareLock | t | t
relation | 345742 | 2663 | | | |
| | | | 2/200573 | 16754 |
AccessShareLock | t | t
relation | 345742 | 2662 | | | |
| | | | 2/200573 | 16754 |
AccessShareLock | t | t
relation | 345742 | 2685 | | | |
| | | | 2/200573 | 16754 |
AccessShareLock | t | t
relation | 345742 | 2684 | | | |
| | | | 2/200573 | 16754 |
AccessShareLock | t | t
relation | 345742 | 2615 | | | |
| | | | 2/200573 | 16754 |
AccessShareLock | t | t
relation | 345742 | 1259 | | | |
| | | | 2/200573 | 16754 |
AccessShareLock | t | t
relation | 345742 | 350686 | | | |
| | | | 2/200573 | 16754 |
RowExclusiveLock | t | t
virtualxid | | | | | 2/200573 |
| | | | 2/200573 | 16754 |
ExclusiveLock | t | t
(10 rows)
Table 'foo' has a RowExclusiveLock lock taken out as a consequence of
running COPY against it.
But that does not prevent other connections from concurrently writing
to the table.
Not all locks block other locks...
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"
pgsql-general by date: