Timeline for Partition table behavior for Postgres
Current License: CC BY-SA 4.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 30, 2019 at 6:30 | history | edited | Shiwangini | CC BY-SA 4.0 |
deleted 13 characters in body
|
| Dec 6, 2019 at 11:42 | vote | accept | Shiwangini | ||
| Dec 6, 2019 at 11:03 | answer | added | Laurenz Albe | timeline score: 0 | |
| Dec 6, 2019 at 10:56 | comment | added | Shiwangini | @a_horse_with_no_name , as you suggested, I edited and re- ran my loop (after recreating table) and started my loop for 1..1000, even now also ,I'm getting error: SQL Error [23514]: ERROR: no partition of relation "msg" found for row Detail: Partition key of the failing row contains (id) = (1000). What transaction you consider wrong now? My partition has capacity to insert records till 1000 ids. Please let me know, which statement is incorrect? | |
| Dec 6, 2019 at 10:51 | history | edited | Shiwangini | CC BY-SA 4.0 |
added 9 characters in body
|
| Dec 6, 2019 at 10:49 | comment | added | user1822 |
As I wrote: the DO block is one single transaction (regardless of the fact that you are using an inefficient LOOP) - either everything or nothing will be inserted. It's essentially the same as insert into ... values (1),(2,),(3),....(1001)
|
|
| Dec 6, 2019 at 10:47 | comment | added | Shiwangini | @a_horse_with_no_name , you can just run the scripts in the order I mentioned and check out the behavior. | |
| Dec 6, 2019 at 10:45 | comment | added | Shiwangini | @a_horse_with_no_name , Thanks for the response, Please have look at my question again. The first part: I have created a partition already for 1000 records and then after I try to insert 1000 records via a loop and it's throwing : SQL Error [23514]: ERROR: no partition of relation "msg" found for row Detail: Partition key of the failing row contains (id) = (1001). I'm not expecting another partition to get created. But, it should atleast insert into existing partition which is full empty for 1000 records. Also, none of the statement have any error. | |
| Dec 6, 2019 at 10:42 | comment | added | user1822 |
Postgres does not automatically create partitions if new values for the partition key are inserted, so I don't really understand what your question is. The errors seem pretty obvious. The DO block from the first example is treated as a single transaction. And by definition a transaction is rolled back if one of the statements have an error
|
|
| Dec 6, 2019 at 10:31 | history | asked | Shiwangini | CC BY-SA 4.0 |