[SQL] working around setQuerySnapshot limitations in functions

[Robert Treat]

I have a function called move_tree() which moves a node from 1 parent to
another (in a nested set tree) by computing some necessary values, then
updating the nodes as needed.  The problem I have is that when the
function is called multiple times simultaneously, each function does a
setQuerySnapshot at the start of the function, and then cannot see the
changes made by the other function calls. I had thought to do a lock on
the table at the start of my function, but due to the fact
setQuerySnapshot is called at the function start (before I can acquire a
lock) I can't guarantee no changes have been made before the function
tries to make changes. ISTM the only way around this is to do a lock on
the table before calling the function. The major downside to that it
requires me to put that logic inside any apps that use the function,
rather than being able to keep it inside the database.  Does anyone see
another work-around?

Robert Treat





---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] CHECKS vs. BEFORE INSERT OR UPDATE TRIGGER

[Ludwig Lim]

Hi:

   I am just wondering. Which one is executed first:
CHECK Constraints or BEFORE INSERT OR UPDATE TRIGGER?

Thank you very much,

ludwig.

__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] CHECKS vs. BEFORE INSERT OR UPDATE TRIGGER

[Michael Paesold]

Ludwig Lim <[EMAIL PROTECTED]> wrote:

>I am just wondering. Which one is executed first:
> CHECK Constraints or BEFORE INSERT OR UPDATE TRIGGER?

First the BEFORE... TRIGGERs, then the CHECK contraints,
so you definetly can change a row in a trigger to match
the contraints...

Regards,
Michael Paesold

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)

[javier garcia]

Thanks Josh and Bret for your answers.
But I think I can try a slightly different thing. As I said,  I've got data, 
extracted from rain gauge stations. Perhaps I wasn't explicit enough. My 
source data are in this format:

 cod_variable | cod_station | year | month | ten | rain_day1 | wind_day1 | 
rain_day2 | wind_day2 | rain_day3 | wind_day3 | rain_day4 | wind_day4 | 
rain_day5 | wind_day5 | rain_day6 | wind_day6 | rain_day7 | wind_day7 | 
rain_day8 | wind_day8 | rain_day9 | wind_day9 | rain_day10 | wind_day10 | 
rain_day11 | wind_day11 | ten_sum_rain
--+-+--+---+-+---+---+---+---+---+---+---+---+--
 30201| 7237| 1953 | 1 |   1 | 0 | 0 |
 0 | 0 | 0 | 0 | 0 | 0 |  
 0 | 0 | 0 | 0 | 0 | 0 |  
 0 | 0 | 0 | 0 | 0 |  0 | 
|| 0
and this is just a row (ten means that the data belong to the first, second 
or third of the weeks of the month). I've got five gauge stations and daily 
data for about forty years (73000 of these long rows). Missing data are 
non-existent rows; so, when a row doesn't appears I lost ten rain data (the 
rain of ten days).
But with the help of the wonderful people in this list I could extract rain 
series (excellent):

 cod_station | year | month | day | rain
-+--+---+-+--
 7250| 1933 | 8 |   1 |0
 7250| 1933 | 8 |   2 |0
 7250| 1933 | 8 |   3 |0...

So, now, in the result of this query, a missing data translates into several 
non-existent row.

Now, I have prepared a table with a complete series of date field (everyday 
from 1553 to 2004), and made ALTER it to ADD aditional fields for every 
station I have:
 year | month | day | st7237 | st7238 | st7239 ...
--+---+-+-+-+-+-
 1953 | 1 |   1 | | | |
 1953 | 1 |   2 | | | |
 1953 | 1 |   3 | | | |...

My problem is that I need to fill in the gaps (the available rain data in the 
corresponding stations), and this would be a very good output for me.
I've prepared an UPDATE but it doesn't work. Perhaps someone could tell me 
where is the error (I've not a very good knowledge of Postgresql). The UPDATE 
is based on the results of the query:


UPDATE  series_lluvia SET st7237=rain FROM

/* here begins the SELECT to obtain the series for one rain gauge station; 
and it works right
 from here to the next comment */
SELECT cod_station, year, month, day, rain FROM (

SELECT cod_variable, cod_station, year, month, 1 as day, rain_day1 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 2 as day, rain_day2 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 3 as day, rain_day3 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 4 as day, rain_day4 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 5 as day, rain_day5 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 6 as day, rain_day6 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 7 as day, rain_day7 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 8 as day, rain_day8 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 9 as day, rain_day9 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 10 as day, rain_day10 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 11 as day, rain_day1 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 12 as day, rain_day2 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 13 as day, rain_day3 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 14 as day, rain_day4 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 15 as day, rain_day5 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 16 as day, rain_day6 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 17 as day, rain_day7 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 18 as day, rain_day8 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_stat

Re: [SQL] [GENERAL] working around setQuerySnapshot limitations in functions

[Jakub Ouhrabka]

Hi,

I'm no expert on this but I think that some cases can be work around using
foo updates instead of selects. See threads "LOCK TABLE oddness in PLpgSQL
function called via JDBC" and "SetQuerySnapshot, once again" in the
archives.
But I think there is no general work around using only plpgsql. I'd also
love to hear about one...

HTH,

kuba

> I have a function called move_tree() which moves a node from 1 parent to
> another (in a nested set tree) by computing some necessary values, then
> updating the nodes as needed.  The problem I have is that when the
> function is called multiple times simultaneously, each function does a
> setQuerySnapshot at the start of the function, and then cannot see the
> changes made by the other function calls. I had thought to do a lock on
> the table at the start of my function, but due to the fact
> setQuerySnapshot is called at the function start (before I can acquire a
> lock) I can't guarantee no changes have been made before the function
> tries to make changes. ISTM the only way around this is to do a lock on
> the table before calling the function. The major downside to that it
> requires me to put that logic inside any apps that use the function,
> rather than being able to keep it inside the database.  Does anyone see
> another work-around?
>
> Robert Treat
>
>
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster