Thread: Transaction Questions
Hi, Following on from my last question, I'm attempting to write a function that inserts periods. Periods are either bookings,available, or unavailable, each stored in a seperate table, and all have start date and end date columns, all ofwhich inherit a table 'calendar_entries'. The function needs to check that there are no overlapping periods, so I need to check all three tables - I need to prevententries being added in parrell for the duration of the function. My question is - Is it sufficient to LOCK calendar_entriesIN EXCLUSIVE MODE, or do I need to lock all three tables individually? Also, inside the function I need to use a transaction in order to DELETE one row, and UPDATE another. Will this 'inner' transactionhave write access to all 3 tables? Just trying to get my head around concurrency - Have a good weekend! Ta, Neil. This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other useof the email by you is prohibited.
Neil, > Following on from my last question, I'm attempting to write a function that > inserts periods. Periods are either bookings, available, or unavailable, > each stored in a seperate table, and all have start date and end date > columns, all of which inherit a table 'calendar_entries'. > > The function needs to check that there are no overlapping periods, so I > need to check all three tables - I need to prevent entries being added in > parrell for the duration of the function. My question is - Is it sufficient > to LOCK calendar_entries IN EXCLUSIVE MODE, or do I need to lock all three > tables individually? If calendar_entries is the first table being checked for all backends, it would be sufficient to lock it. Personally, I would try to devise a more elaborate strategy that allowed for some degree of concurrency, but possibly you don't need that. > Also, inside the function I need to use a transaction in order to DELETE > one row, and UPDATE another. Will this 'inner' transaction have write > access to all 3 tables? Subtransactions (Savepoints) inherit the properties of their parent transaction (the function). -- Josh Berkus Aglio Database Solutions San Francisco
Neil, > Thanks Josh. I agree with you that it would be preferable to have a better > design - But since all 'periods' are constrained based on each other, I can > see no other way than locking all tables to prevent a race hazard. Any > ideas you have an any improvment would be gratefully received - I'm quite > new to database design! You've chosen a tough problem to start with, then. Calendaring in SQL is a pain, mostly due to a bunch of bad decisions made by the SQL committee in the early 90's. After some thought, there isn't an *easy* way to avoid locking the whole table. There are some elaborate workarounds, but those are only worth pursuing if the locking is seriously choking your application. -- Josh Berkus Aglio Database Solutions San Francisco
Here's a possible solution that would maintain concurrency. It would involve breaking down your booking periods into discrete units. Create a table w/ unique constraint based on the discrete time block. So instead of inserting a single start/end record, you would insert a record for each unit inside timeframe. Example, let's say the minimum booking period is 1 hour and somebody wants the timeframe between 8/25/05 10PM to 8/26/05 9PM. So you would have a loop that attempts to insert 8/25/05 10PM, 8/25/05 11PM, ..., 8/26/05 7PM, 8/26/05 8PM. With an unique constraint on DATE+TIME_BLOCK, an unavailable period would return a duplicate key error -- which if you detect, you'd break out of your loop. Put this loop inside a transaction and the entire job will just rollback so the booked time units up to that point would auto-unbook themselves. You are getting concurrency in exchange for doing more work so the point where the extra records decreases performance more than locking depends on how many period units within the average start/end timeframe. neil.saunders@accenture.com wrote: > Hi, > > Following on from my last question, I'm attempting to write a function that inserts periods. Periods are either bookings,available, or unavailable, each stored in a seperate table, and all have start date and end date columns, all ofwhich inherit a table 'calendar_entries'. > > The function needs to check that there are no overlapping periods, so I need to check all three tables - I need to prevententries being added in parrell for the duration of the function. My question is - Is it sufficient to LOCK calendar_entriesIN EXCLUSIVE MODE, or do I need to lock all three tables individually? > > Also, inside the function I need to use a transaction in order to DELETE one row, and UPDATE another. Will this 'inner'transaction have write access to all 3 tables? > > Just trying to get my head around concurrency - Have a good weekend!
hi all. i'm trying to copy data from a csv file into a PGSQL 7.4 database. i tried tab delimited and i tried comma delimted. i placed both files in my c:\cygwin and typed the following in pgadmin3: COPY t_ipc_defect FROM '/IPC'; my table looks like this: description date id my data looks like this... "Tie too loose",08/25/2005,"MA18" i also tried this... Tie too loose,08/25/2005,MA18 and this... "Tie too loose","08/25/2005","MA18" the error i receive is pretty consistent. ERROR: missing data column "date" CONTEXT: COPY defect, line 1: ""PI[] (this is me - looks to be a small vertical rectangle) Spacer not in contact with component and body","08/25/2005","MA18"" i created my source csv file from openoffice.org. i will go through the motions again, except i will try a single quote instead of a double quote to see if that helps. does anyone have any ideas? tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
<operationsengineer1@yahoo.com> writes:
> i'm trying to copy data from a csv file into a PGSQL
> 7.4 database. i tried tab delimited and i tried comma
> delimted.
PG 7.4's COPY does not have any support for CSV format (that was added
in 8.0). Tab-delimited should work OK though.
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > <operationsengineer1@yahoo.com> writes: > > i'm trying to copy data from a csv file into a > PGSQL > > 7.4 database. i tried tab delimited and i tried > comma > > delimted. > > PG 7.4's COPY does not have any support for CSV > format (that was added > in 8.0). Tab-delimited should work OK though. > > regards, tom lane > Tom, thanks for the follow up. i'm hoping to transfer my db in house running PHP 5 anf PGSQL 8 within a couple weeks. i did try tab delimited from openoffice.org. when i opened the file, though, there was a character inserted in lieu of the tab space. i think it was a small vertical rectangle. should i have opened the file and seen data separated by actual tabs? i'm wondering if OOO.org is inserting a character for the tabs and 7.4 is choking on the unexpected character. tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com