0

I am trying to learn transactions better in PostgreSQL and came across this observation.

I have a situation where I am creating multiple tables using an SQL statement across multiple sessions. I am using CREATE TABLE *table_name* AS *sql_stmt* (CTAS) syntax. I would consider this a safe operation to execute concurrently (across my multiple sessions) because it is not updating any data, thus I would want to ensure that I have the least restrictive lock possible at all times.

That being said, multiple sessions are still creating data (the table) so I am quite worried that if the SQL statements contain references to the same table, I might be invoking a lock, even if I know that it is safe for the operations I have in mind. In other words, I know that all my operations are read-only (so can use a lock that is not restrictive) and that no table will have data which is updated, with the simple caveat that I am also saving the result as a table (so this is no longer 100% read-only; might invoke some restrictive lock at some point?). Thus, my question is as follows: how can I ensure the lowest lock restriction across all concurrent sessions while I do my CTAS workload?

For an example, let us say I have two sessions. One is issuing the following command:

CREATE TABLE t1 AS SELECT * FROM facttable;

The other, concurrently issues:

CREATE TABLE t2 AS SELECT * FROM facttable;

How can I ensure that such operations do not invoke a lock since they are referring to the same table?

3
  • I don't understand. Are these multiple sessions creating different tables? In different databases? Can you add some code and a more technical description? Mar 17, 2020 at 20:09
  • Yes, multiple sessions creating different tables. Not sure what you mean with different databases, this is all being done on the same database cluster. I shall supply a small example soon.
    – Zeruno
    Mar 17, 2020 at 22:34
  • Are you having performance problems? Locking problems? Or is this just theoretical? When you copy the data like you are doing, you don't prevent new data from being inserted -- you just won't see it or get it in the new table. Mar 19, 2020 at 8:11

1 Answer 1

1

There is no danger of locking with your scenario.

The only lock that is taken on facttable is the ACCESS SHARE lock that prevents others from dropping or altering the table while it is being read. Since ACCESS SHARE doesn't conflict with itself, that is no problem (two people can read the same poster).

The only consideration is the I/O load: if many processes perform a sequential scan on the table, that could be substantial. You should try to benefit from PostgreSQL's synchronized sequential scans by starting all processes at the same time, so that they have a chance to synchronize their sequential scans.

2
  • Just for clarification, with regards to the IO load consideration, this is something that should only happen when available memory is not sufficient, correct?
    – Zeruno
    Mar 18, 2020 at 9:08
  • 1
    Well, if all is cached, you won't have I/O. Mar 18, 2020 at 11:33

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.