Re: [SQL] Changing the transaction isolation level within the
Hi, Mario, Mario Splivalo wrote: >>you need to set the transaction level after the begin and before every >>other statement... after the begin you have a select that invoke your >>function so that set is not the first statement... > > But I can't do that inside of a function, right? Right, as you need a SELECT to actually execute your function, so the transaction commands inside the function are invoced _after_ the first SELECT began execution. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Changing the transaction isolation level within the stored
Hi, Mario, My explanation is a little longer, as I think I must at least basically explain some of the fundamentals of database synchronization. Mario Splivalo wrote: >>>Is it possible to change the transaction level within the procedure? >>No, currently not, the PostgreSQL "stored procedures" really are "stored >>functions" that are called inside a query, and thus cannot contain inner >>transactions. > Is above true for the newly introduced stored procedures? (Above, when > mentioning 'stored procedures' I was actualy reffering to 'functions'). I have to admit that I don't know what "newly introduced stored procedures" you're talking about? Is this an 8.2 feature? >>So you even want to change the transaction serialization level within a >>running transaction? I'm sorry, this will not work, and I cannot think >>of a sane way to make it work. > I have some ideas, I just needed confirmation it can't be done this way. > Thank you! :) >>It is locically not possible to raise the isolation level when the >>transaction was started with a lower level and thus may already have >>irreversibly violated the constraits that the higher level wants to >>guarantee. > Yes, a thread will need to start a transaction, I'm just affraid that > create_message could lead me to deadlocks. Don't misinterpret transaction isolation as locking. PostgreSQL (and e. G. Oracle) use a MVCC system for transactions, that doesn't need exclusive locks. Read-only transactions can never collide, and writing transactions only when using transaction isolation "serializable" and manipulating the same data rows. Some of the colliding transactions will be aborted to resolve the conflicts, and the others can commit fine. AFAIK, in PostgreSQL normal SQL commands cannot create deadlocks at all, the only way to introduce deadlocks is to issue LOCK commands to take locks manually. And for this rare case, PostgreSQL contains a deadlock detection routine that will abort one of the insulting transactions, and the others can proceed. I suggest you to read "Chapter 12. Concurrency Control" from the PostgreSLQ docs. Its easy: if you need "read committed" guarantees, then run the entire transaction as "read committed". If you need "serializable", then run the entire transaction as "serializable". If you need real serialization and synchronization of external programs, use LOCK (or take a deep breath, redesign your application and use e. G. LISTEN/NOTIFY. Most times, the usage of LOCK is a good indicator of misdesign.) I just re-read your original posting. You want to make thread B wait until thread A has committed. This will not be possible with the ACID levels. Even when using "serializable" for both threads. If thread B issues SELECT after thread A committed, then all works fine. If thread B issues SELECT before thread A commits, it sees the database in the state it was before thread A started its transaction (so even create_message has not been called). It cannot know whether thread A will COMMIT or ROLLBACK. Transaction isolation is about consistency guarantees, not for true serialization. The reason for this is that databases with high load will need to allow paralellism. So for your case, threas A should issue "NOTIFY" before COMMIT, and then thread B should use LISTEN and then wait for the notification before beginning its transaction. Be shure to read the paragraph about how "NOTIFY interacts with SQL transactions" in the NOTIFY documentation. I don't know the exact sematics of set_message_status and your checks, but it may be another solution to split thread A into two transactions by committing after step 3, and another BEGIN after step 4. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] filtering after join
andrew wrote: > Sorry for the confusion. This is what i meant. Thanks, Michael. > > select * > from (select * from A, B where A.a = B.b) as s > where foo(s) < 2; > > On 1/25/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > > andrew wrote: > > > I want to use a UDF to filter tuples t that are generated after a > > > join. More specifially, I have a UDF foo(record), which computes > > > a value for a given tuple. I can do the filtering before the > > > join. e.g.: > > > > > > select * from A, B where foo(A)<2 and A.a=B.b; Note that these two queries will produce the same internal execution plan, so if you wanted to make some difference you will not. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] filtering after join
But the function foo() would produce different values for the two queries, so the result will be different. A simple example is foo() computes the sum of all the integer fields of the input record. On 1/26/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > andrew wrote: > > Sorry for the confusion. This is what i meant. Thanks, Michael. > > > > select * > > from (select * from A, B where A.a = B.b) as s > > where foo(s) < 2; > > > > On 1/25/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > > > andrew wrote: > > > > I want to use a UDF to filter tuples t that are generated after a > > > > join. More specifially, I have a UDF foo(record), which computes > > > > a value for a given tuple. I can do the filtering before the > > > > join. e.g.: > > > > > > > > select * from A, B where foo(A)<2 and A.a=B.b; > > Note that these two queries will produce the same internal execution > plan, so if you wanted to make some difference you will not. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > -- andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] filtering after join
andrew wrote: > But the function foo() would produce different values for the two > queries, so the result will be different. > A simple example is foo() computes the sum of all the integer fields > of the input record. OK, I see now where you're getting at. You want to combine the record type of A and B into one. Then the proposed solution is right. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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
Re: [SQL] Changing the transaction isolation level within the stored
On Thu, Jan 26, 2006 at 10:42:54AM +0100, Markus Schaber wrote: > AFAIK, in PostgreSQL normal SQL commands cannot create deadlocks at all, > the only way to introduce deadlocks is to issue LOCK commands to take > locks manually. And for this rare case, PostgreSQL contains a deadlock > detection routine that will abort one of the insulting transactions, and > the others can proceed. You can too. Consider this: t1t2 BEGIN;BEGIN; UPDATE table1 SET col1= UPDATE table2 SET col1= col1+5; (SELECT col3 FROM DELETE FROM table2 WHERE table3); col1 = col1+6;UPDATE table1 SET col1 = col1 +5; COMMIT; COMMIT; Suppose these are concurrent. The problem here is that each transaction need something in the other transaction either to complete or rollback before the work can proceed. So one of them has to lose. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] question with times and intervals
Hi, I have a table like this: test=# select * from status_log ; id | status |t_start | t_end +++ 1 | 1 | 2006-01-20 23:00:00+01 | 2006-01-21 06:00:00+01 1 | 1 | 2006-01-21 06:00:00+01 | 2006-01-21 22:00:00+01 1 | 2 | 2006-01-21 22:00:00+01 | 2006-01-22 05:00:00+01 1 | 1 | 2006-01-22 05:00:00+01 | 2006-01-22 15:00:00+01 1 | 2 | 2006-01-22 15:00:00+01 | 2006-01-23 02:00:00+01 (5 rows) Now i need for a particular intervall (one day or two days, entires days) the accumulated time for id=X and status=Y. Exampel: id=1, status=1, date=2006-01-21: from 00:00:00 - 06:00:00 and 06:00:00 - 22:00:00 ===> 6 hours + 16 hours = 22 hours id=1, status=2, date=2006-01-21: from 22:00:00 - 23:59:59 ===> 2 hours I need also this for a week or month. How can i calculate this? Thanks very much for help. -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] question with times and intervals
A. Kretschmer wrote: Hi, I have a table like this: test=# select * from status_log ; id | status |t_start | t_end +++ 1 | 1 | 2006-01-20 23:00:00+01 | 2006-01-21 06:00:00+01 1 | 1 | 2006-01-21 06:00:00+01 | 2006-01-21 22:00:00+01 1 | 2 | 2006-01-21 22:00:00+01 | 2006-01-22 05:00:00+01 1 | 1 | 2006-01-22 05:00:00+01 | 2006-01-22 15:00:00+01 1 | 2 | 2006-01-22 15:00:00+01 | 2006-01-23 02:00:00+01 (5 rows) Now i need for a particular intervall (one day or two days, entires days) the accumulated time for id=X and status=Y. Exampel: id=1, status=1, date=2006-01-21: from 00:00:00 - 06:00:00 and 06:00:00 - 22:00:00 ===> 6 hours + 16 hours = 22 hours OK - all untested... First step - don't ask for a date, ask between two timestamptz's (which I'll call t1, t2) Then, define two functions: earlier(timestamptz, timesatmptz) and later(...) as SQL functions using SELECT ... CASE SELECT later(T1, t_start) AS lower_time earlier(T2, t_end) AS upper_time FROM status_log WHERE id = X AND status = Y AND ts_end >= T1 AND ts_start <= T2 ; Now (upper_time - lower_time) is the interval you want and summing them will give you your answer. Any help? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] question with times and intervals
Richard Huxton schrieb:
> Now (upper_time - lower_time) is the interval you want and summing them
> will give you your answer.
>
> Any help?
Yes, thanks.
But, i remember a little function that i wrote in the past:
http://a-kretschmer.de/tools/time_intersect.sql
And now i have a solution (i hope):
test=# select id, status,
test-# sum((time_intersect(t_start, t_end, '2006/01/21
00:00:00'::timestamptz, '2006/01/22 23:59:59'::timestamptz)).t2 -
test(# (time_intersect(t_start, t_end, '2006/01/21
00:00:00+1'::timestamptz, '2006/01/22 23:59:59+1'::timestamptz)).t1)
test-# from status_log
test-# where (t_start, t_end) overlaps ('2006/01/21 00:00:00+1'::timestamptz,
'2006/01/22 23:59:59'::timestamptz)
test-# group by id, status
test-# order by 1,2;
id | status | sum
++--
1 | 1 | 32:00:00
1 | 2 | 15:59:59
(2 rows)
Okay, now i can write a function similar above which returns the
interval and the i can sum() this.
Thanks, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Changing the transaction isolation level within the stored
Hi, Andrew, Andrew Sullivan wrote: >>AFAIK, in PostgreSQL normal SQL commands cannot create deadlocks at all, >>the only way to introduce deadlocks is to issue LOCK commands to take >>locks manually. And for this rare case, PostgreSQL contains a deadlock >>detection routine that will abort one of the insulting transactions, and >>the others can proceed. > > You can too. Consider this: > > t1t2 > > BEGIN;BEGIN; > UPDATE table1 SET col1= UPDATE table2 SET col1= > col1+5; (SELECT col3 FROM > DELETE FROM table2 WHERE table3); > col1 = col1+6;UPDATE table1 SET col1 = >col1 +5; > COMMIT; COMMIT; Hmm, are you shure that this is correct? The delete will always delete 0 rows. http://www.postgresql.org/docs/8.0/static/transaction-iso.html#XACT-SERIALIZABLE contains a nice example in '12.2.2.1. Serializable Isolation versus True Serializability' that you should probably read. > Suppose these are concurrent. The problem here is that each > transaction need something in the other transaction either to > complete or rollback before the work can proceed. So one of them has > to lose. Despite the fact that I don't see such a collision in your example: Depending on the transaction isolation level and exact timings, colliding queries may lead to different results or even one transaction aborted, but there is no deadlock under MVCC. Not needing such locks is the whole point in using MVCC at all. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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
Re: [SQL] filtering after join
How will the query planner do for a nesting query? Treat the subqueries as multiple queries and then link them together? where can I find the information (codes or documents)? Thanks. On 1/26/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > andrew wrote: > > But the function foo() would produce different values for the two > > queries, so the result will be different. > > A simple example is foo() computes the sum of all the integer fields > > of the input record. > > OK, I see now where you're getting at. You want to combine the record > type of A and B into one. Then the proposed solution is right. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > -- andrew ---(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
Re: [SQL] Changing the transaction isolation level within the stored
On Thu, 26 Jan 2006, Markus Schaber wrote: > AFAIK, in PostgreSQL normal SQL commands cannot create deadlocks at all, > the only way to introduce deadlocks is to issue LOCK commands to take > locks manually. And for this rare case, PostgreSQL contains a deadlock > detection routine that will abort one of the insulting transactions, and > the others can proceed. That's not true. See all the complaints about pre-8.1 foreign keys and the row locks taken out by FOR UPDATE as an example. A simpler example than the one given before (with potential timing) is: create table t1 (a int); create table t2 (a int); insert into t1 values(1); insert into t2 values(1); T1: begin; T2: begin; T1: update t1 set a=3; T2: update t2 set a=3; T1: update t2 set a=2; T2: update t1 set a=2; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [PERFORM] Query optimization with X Y JOIN
On 1/26/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > If I want my database to go faster, due to X then I would think that the > issue is about performance. I wasn't aware of a paticular constraint on X. > > I have more that a rudementary understanding of what's going on here, I was > just hoping that someone could shed some light on the basic principal of > this JOIN command and its syntax. Most people I ask, don't give me straight > answers and what I have already read on the web is not very helpful thus > far. http://www.postgresql.org/docs/current/static/sql-select.html -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Changing the transaction isolation level within the stored
On Thu, Jan 26, 2006 at 01:51:27PM +0100, Markus Schaber wrote: > Hmm, are you shure that this is correct? The delete will always delete 0 > rows. Quite, and no it won't. The contrived example is actually a simplification of a case one of our developers implemented. The conflict is on the updates. Two concurrent transactions likely wouldn't be enough to cause it on a fast system, but multiple ones for sure will. The problem is that the updates have to wait for one another to complete in order to know what result they can use, but then the _other_ contention on the other table causes them to have to wait for one another there. I don't think anybody would have gone to the trouble of putting in deadlock detection if the only way to deadlock was to trip over yourself with manual locking: presumably, if you're issuing locks by hand, you either know what you're doing or get what you deserve. > Depending on the transaction isolation level and exact timings, > colliding queries may lead to different results or even one transaction > aborted, but there is no deadlock under MVCC. > > Not needing such locks is the whole point in using MVCC at all. I think you don't have a clear idea of what locks are necessary for updates. Write operations on a row must block other write operations on the same row. If more than one transaction needs the same kinds of locks on two different tables, but attempts to get those locks in the opposite order, you are all but guaranteed a deadlock. MVCC helps, but it can't avoid locking the same data when that data is being updated. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] filtering after join
andrew wrote: > How will the query planner do for a nesting query? Treat the > subqueries as multiple queries and then link them together? > where can I find the information (codes or documents)? Look at the execution plan using the EXPLAIN command. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] [HELP] Defining a function as a procedure
Hi, Is there a way to define a function as a procedure, I mean a function that returns nothing. CREATE OR REPLACE FUNCTION foo() AS $$ BEGIN END; $$ LANGUAGE 'plpgsql'; Actually, PostgreSQL complains as a “function result type must be specified”. I can patch my function so that it compiles but that won’t be really nice: CREATE OR REPLACE FUNCTION foo() RETURNS int AS $$ BEGIN RETURN 1; END; $$ LANGUAGE 'plpgsql'; Is there any other prettier way to do that? Thanks, -- Daniel
Re: [SQL] [HELP] Defining a function as a procedure
"Daniel Caune" <[EMAIL PROTECTED]> writes: > Is there a way to define a function as a procedure, I mean a function > that returns nothing. In recent versions you can say RETURNS VOID, which is a bit of a hack but it gets the point across... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
