Postgres COPY FROM forever

Question

I have a program that will be copying large amounts of data into Postgres 9 using COPY FROM via stdin more or less continually.

This is currently working fine, but I am buffering chunks of data and then running COPY FROM operations in batches.

I am wondering, and couldn't find after looking around, whether it would be a bad idea for me to just create the COPY FROM stream and just never close it until my program terminates. As in, while my program is running and accepting new data, I would like to open a COPY FROM and continually stream that data for its lifetime.

I am looking for what the internal mechanics of that are on the Postgres end:

• Does the COPY FROM operation create a transaction internally?
• Related: Will the data I'm streaming be immediately accessible to other sessions?
• Does Postgres have any internal mechanics that would cause this to not work (i.e. some internal state that would overflow without routine closing of the COPY FROM stream)?

Note: I'm aware similar considerations also apply to the client side driver I'm using, but I assume (perhaps incorrectly) that the choice of client won't change what I'm asking with regard to the Postgres side of things. I'd like to keep this question focused on Postgres specifically if possible.

Answer 1

Does the COPY FROM operation create a transaction internally?

Every SQL statement in Postgres, including a COPY FROM, will either be part of a larger transaction, or will be itself wrapped in a transaction. ref:

PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.

--

Related: Will the data I'm streaming be immediately accessible to other sessions?

No, it is never the case that un-committed data will be visible to other transactions. In SQL terminology, this would be called a "dirty read", and is not possible in Postgres ref.

Does Postgres have any internal mechanics that would cause this to not work (i.e. some internal state that would overflow without routine closing of the COPY FROM stream)?

Nothing will directly stop you from doing this. But in general, it is considered good practice to keep your transactions relatively short to cooperate with the rest of the system. If you let a COPY FROM statement hang out for hours, you are going to have an impact on VACUUM being able to do its job ref.

Another aspect to consider is the locking impact. If you have a primary key, unique indexes, or other constraints established on the table (you should!), Postgres will understand that the rows you are COPYing in hold a row-level lock until they are committed. Let's say you are COPYing in a row with unique_column='abc123', and you let this statement hang out for hours. If somebody else comes along and tries to COPY or INSERT a row which also has unique_column='abc123', he will be blocked until your COPY FROM transaction finally commits. This sort of behavior can cause a ripple effect of blocked transactions throughout your system, and grind your database to a halt in the worst case, especially if the table you are COPYing into is heavily contended by other writers.