On Mon, Dec 19, 2005 at 08:46:39AM -0500, Lane Van Ingen wrote: > Thanks, that helped. > > Please answer 2 other related questions, if you would: > (1) What must I do to 'Be prepared for serialization failures' (how to > detect, how to handle)? > Do you have a sample?
You'll get an error. You should read this section of the docs: http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html#XACT-SERIALIZABLE > (2) Also, I am assuming that the effect of all of this is to just force > transactions to wait in line > to be processed serially, and that it only lasts as long as the pl/pgsql > transaction block or > the next COMMIT. No. The effect is to _emulate_ the case where the set transaction is processed serially. Importantly, on a high-concurrency database, you tend to get serialization failures. Moreover, it is not true mathematical serialization. See section 12.2.2.1 for details in case you need such a feature, in which case you're back to explicit locking. A > > -----Original Message----- > From: Achilleus Mantzios [mailto:[EMAIL PROTECTED] > Sent: Monday, December 19, 2005 9:25 AM > To: Lane Van Ingen > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] How to Force Transactions to Process Serially on A Table > > O Lane Van Ingen ?????? ???? Dec 19, 2005 : > > > I am using PL/SQL functions on Windows 2003, version 8.0.1. > > > > I have not used explicit PostgreSQL LOCKing before, but I need some advice > > on how properly to use some explicit locking. I think that duplicate key > > violations I am now getting are the result. > > > > I want to force transactions being used to update a table to be processed > on > > a first-come, first-served basis. I want my Pl/sql function to execute to > > completion on each transaction before another starts. > > > > Need some advice on how to do this. From what I can read in the docs, it > > looks like I need to solve the problem by using the following, but doing > so > > gives me an SPI_execution error: > > BEGIN; > > LOCK <table> IN SHARE ROW EXCLUSIVE MODE; > > lock adns_report_hour_history in share row exclusive mode; > > INSERT INTO <table> VALUES ... - or - UPDATE <table> SET .... > > COMMIT; > > Will this make the next transaction wait until the previous transaction > has > > completed? Do I need to set any config parameters? > > > > If you can include an actual code snippet in the response, it would help > ... > > what you want is to set the xaction isolation level. > > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > ...... > COMMIT; > > Be prepared for serialization failures though. > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > > > -- > -Achilleus > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
