[SQL] Savepoints and SELECT FOR UPDATE in 8.2

[EXT-Rothermel, Peter M]

I have a client application that needs:

SELECT a set of records from a table and lock them for potential
updates.
for each record
 make some updates to this record and some other records in other
tables
 call some call a function that does some application logic that
does not access the database
 if this function is successful
 commit the changes for this record
 release any locks on this record
 if the function fails
 rollback any changes for this record
 release any locks for this record
 
It would not be too much of a problem if the locks for all the records
were held until all these
records were processed. It would probably not be too bad if all the
changes were not committed
until all the records were processed. It is important that all the
records are processed even when
some of iterations encounter errors.

I was thinking of something like this:

connect to DB

BEGIN

SELECT * FROM table_foo where foo_state = 'queued'  FOR UPDATE;
for each row 
do [

SAVEPOINT s;
UPDATE foo_resource SET in_use = 1 WHERE ...;

status = application_logic_code(foo_column1, foo_column2);

IF status OK 
THEN
  ROLLBACK TO SAVEPOINT s;
ELSE
  RELEASE SAVEPOINT s;
ENDIF   
]


COMMIT;

I found a caution in the documentation that says that SELECT FOR UPDATE
and SAVEPOINTS is not implemented correctly in version 8.2:

http://www.postgresql.org/docs/8.2/interactive/sql-select.html#SQL-FOR-U
PDATE-SHARE

Any suggestions?




-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] LIMIT question

[EXT-Rothermel, Peter M]

I need to use a LIMIT count in a query but I also need to know how many
rows the query itself would yield without the limit.
I can do this inside a transaction like this

BEGIN
SELECT COUNT(*) from table1 where blah;
select * from table1 where blah LIMIT 1000;
COMMIT

Now I can give some feedback like your search matches 200,000 but was
limited to 1000 items.
Is there a better way to accomplish this goal?



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] looking for example of inserting into a view

[EXT-Rothermel, Peter M]

I can't seem to find an example I vaguely remember seeing when I was
originally learning about INSERT rules and views.

This example features a view that is an outer join of several tables.
The example shows how to generate a CSV file of the data in the view
and then loading the data into the original tables from the CSV file.

Does anybody else remember seeing this example?


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql