0

I have a database called db1 and I'm filling it with other two different databases, let's call db2 and db3. I connect db2 and db3 to db1 using postgres_fdw and now I'm running scripst like this:

insert into db1.table from select a,b,c from db2.table

and

insert into db1.table from select a,b,c from db3.table

turns out that only the first script is running, the second is waiting with idle in transaction. This is what htop shows me from the second script, while the first is ok.

htop

looking for reasons, I found out that this may be happening due to a lock, so investigating on postgres, I also found this

lock

I'm inserting data into interactions and each interactions has user, so I'm selecting the correct user before insert into interaction.
Is there a way to bypass the lock? If so, is it safe?

7
  • 1
    The second insert query is blocked while the first one is running? In other words, does the second query start after the first one is completed?
    – shx
    Feb 22, 2016 at 15:30
  • I don't know exactly because the first query didn't finished ran, but I guess it runs smoothly after the first finishes.
    – Luiz E.
    Feb 22, 2016 at 16:30
  • 1
    The seccond query is blocked by the first one because its a single transaction which affects the same table. You can only READ from a locked table, for INSERTS or UPDATES you will have to be queued and they will be performed when the lock is gone.
    – Solrac
    Feb 22, 2016 at 16:33
  • 1
    Short answer is "there is not", here is the long answer postgresql.org/docs/9.1/static/explicit-locking.html You could put both query's into one statement though but they wont run in paralell...
    – Solrac
    Feb 22, 2016 at 17:22
  • 1
    @SolracRagnarockradio: that is wrong. You can update, delete, insert with many transactions on the same table without the transactions blocking each other - provided that each transactions deals with different rows. Two inserts will only block each other if the second one is trying to insert the same primary key (or unique key) values as the first one. The same is true for updates: you can have as many concurrent updates on a table as there are unique rows (if each transaction updates a single row)
    – user1822
    Feb 23, 2016 at 19:21

1 Answer 1

3

idle in transaction means it is waiting for data from the client, not that is waiting on a lock.

And your excerpt from pg_lock supports this, assuming the last column is the "granted" column. All locks have been granted, no one is blocking.

If your scripts are blocking each other, it seems to be happening outside of the database. Or at least, outside of that particular database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.