Re: Multiple COPY on the same table

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?"