Re: [SQL] hi all......................!!
AKHILESH GUPTA wrote: hello everybody i am new to this mailing list. this is my first mail to this group. i jussst want to confirm that whether is it possible to update a view or not?? There is no automatic updating of views at present. You can write your own RULES though to do updates - see the manuals for details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] hi all......................!!
Hi, Akilesh, AKHILESH GUPTA wrote: > i am new to this mailing list. this is my first mail to this group. > i jussst want to confirm that whether is it possible to update a view or > not?? > i think you all help me in solving my queries in future...!! Do you think about issuing UPDATE commands on a view, or do you think about updating the view definition itsself? The former is possible if you add the appropriate 'ON UPDATE DO INSTEAD' Rules to the view, see http://www.postgresql.org/docs/8.1/static/rules.html and http://www.postgresql.org/docs/8.1/static/sql-createrule.html The latter is easily possible if the updated view definition has equal column definitions, just use "CREATE OR UPDATE VIEW ..." instead of "CREATE VIEW ..." to update the view. If your column definitions change, then you'll have to DROP the view before reCREATEing it, maybe it's best to encapsulate this inside a transaction or use a scheduled downtime. Btw, it seems that your '.'-key is broken and chatters. :-) 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 5: don't forget to increase your free space map settings
[SQL] Changing the transaction isolation level within the stored procedure?
Is it possible to change the transaction level within the procedure?
I'm using Postgres 8.1.2 default isolation level. But, I would like one
stored procedure to execute as in serializable isolation level. I have
created my stored procedure like this:
CREATE OR REPLACE FUNCTION set_message_status("varchar", int4)
RETURNS void AS
$BODY$
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE messages SET message_status = $2 WHERE message_id = $1 AND
message_status < $2;
INSERT INTO _update_log VALUES (now()::timestamp, $1, $2);$BODY$
LANGUAGE 'sql' VOLATILE SECURITY DEFINER;
But I'm getting error:
octopussy2=# select * from
set_message_status('b4c15204-123f-4cba-ad09-d423630c999d', 90);
ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT: SQL function "set_message_status" statement 1
I get the same error if I write my stored procedure in plpgsql language.
What I want to achive goes like this:
I have a client code (java/jdbc) that inserts some messages to my
database, and then process them.
Basicaly, it goes like this:
One thread (thread A) does this:
1. java got the message via http (whatever)
2. java does: begin;
3. java does: select * from create_message(...)
4. java does some checking
5. java does: select * from set_message_status(...)
6. java does some more checing
7. java does commit; (under rare circumstances java does rollback).
Another thread (thread B) does this:
1. java got the update_status_request via http (whatever)
2. java does: begin;
3. java does: select * from set_message_status(...)
4. java does: commit;
As I've said, I'm using 'read commited', the default isolation level.
Now, sometimes it happens that steps 4 or 6 take more time, and thread B
steps are executed before steps in thread A have finished. So I would
like the UPDATE in set_message_status to 'hold', until the transaction
that previoulsy called the set_message_status have commited or rolled
back.
Is there a way to do so withing the postgres, or I need to do 'SET
TRANSACTION ISOLATION LEVEL TO SERIALIZABLE' before step 2 in both
thread A and thread B?
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---(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
Hi, Mario, 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. > I'm using Postgres 8.1.2 default isolation level. But, I would like one > stored procedure to execute as in serializable isolation level. I have > created my stored procedure like this: [...] > One thread (thread A) does this: > > 1. java got the message via http (whatever) > 2. java does: begin; > 3. java does: select * from create_message(...) > 4. java does some checking > 5. java does: select * from set_message_status(...) > 6. java does some more checing > 7. java does commit; (under rare circumstances java does rollback). 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. 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. 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 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
[SQL] psql in the command line
Title: psql in the command line Hi I need some help regarding psql syntax. I would like to copy text files into the database from the command line in a script. I have found already how can I do it as user postrgres: test:/opt/pgsql# echo "copy test from '/data/log/bla';" | psql logdb COPY But when I try to execute it as root user I get the following error: [est/data/log] su - postgres -c "echo 'copy test from /data/log/bla;'| psql logdb" ERROR: syntax error at or near "/" at character 16 LINE 1: copy test from /data/log/bla; I have already tried with all kind of double and single quotes but it does not work. Anybody has an idea how can I make it run? Thanks and regards Aniko
Re: [SQL] psql in the command line
[EMAIL PROTECTED] wrote: > test:/opt/pgsql# echo "copy test from '/data/log/bla';" | psql logdb > COPY > > But when I try to execute it as root user I get the following error: > > [est/data/log] su - postgres -c "echo 'copy test from /data/log/bla;'| > psql logdb" > ERROR: syntax error at or near "/" at character 16 > LINE 1: copy test from /data/log/bla; This is clearly not the same line. The quotes are not in the same position as in the line above. su - postgres -c "psql logdb -c 'copy test from \'data/log/bla\''" -- Alvaro Herrerahttp://www.advogato.org/person/alvherre "Es filósofo el que disfruta con los enigmas" (G. Coli) ---(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 procedure?
> 1. java got the message via http (whatever) > 2. java does: begin; > 3. java does: select * from create_message(...) > 4. java does some checking > 5. java does: select * from set_message_status(...) > 6. java does some more checing > 7. java does commit; (under rare circumstances java does rollback). > > > Another thread (thread B) does this: > > 1. java got the update_status_request via http (whatever) > 2. java does: begin; > 3. java does: select * from set_message_status(...) > 4. java does: commit; > > As I've said, I'm using 'read commited', the default isolation level. > > Now, sometimes it happens that steps 4 or 6 take more time, and thread B > steps are executed before steps in thread A have finished. So I would > like the UPDATE in set_message_status to 'hold', until the transaction > that previoulsy called the set_message_status have commited or rolled > back. > > Is there a way to do so withing the postgres, or I need to do 'SET > TRANSACTION ISOLATION LEVEL TO SERIALIZABLE' before step 2 in both > thread A and thread B? > 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... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] filtering after join
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; But I want to apply foo() to the tuples generated by the join operation. How can I do that? Thanks! -- andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] psql in the command line
Alvaro Herrera wrote: > [EMAIL PROTECTED] wrote: > > > test:/opt/pgsql# echo "copy test from '/data/log/bla';" | psql logdb > > COPY > > > > But when I try to execute it as root user I get the following error: > > > > [est/data/log] su - postgres -c "echo 'copy test from > /data/log/bla;'| > > psql logdb" > > ERROR: syntax error at or near "/" at character 16 > > LINE 1: copy test from /data/log/bla; > > This is clearly not the same line. The quotes are not in the same > position as in the line above. > > su - postgres -c "psql logdb -c 'copy test from \'data/log/bla\''" Or, preferably: $ echo "copy test from '/data/log/bla';" | sudo -u postgres psql logdb HTH, Owen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] filtering after join
On Wed, Jan 25, 2006 at 06:27:33PM +0100, 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; > > But I want to apply foo() to the tuples generated by the join > operation. How can I do that? Is this what you're looking for? select * from (select * from A, B where A.a = B.b) as s where foo(s) < 2; -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] filtering after join
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; What makes you think that the filtering happens before the join here? And why would it matter? The results should be all the same. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] filtering after join
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; > > What makes you think that the filtering happens before the join here? > And why would it matter? The results should be all the same. > > -- > 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] Changing the transaction isolation level within the
On Wed, 2006-01-25 at 11:46 -0500, Jaime Casanova 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? Mari ---(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
On Wed, 2006-01-25 at 15:54 +0100, Markus Schaber wrote: > Hi, Mario, > > 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'm using Postgres 8.1.2 default isolation level. But, I would like one > > stored procedure to execute as in serializable isolation level. I have > > created my stored procedure like this: > [...] > > One thread (thread A) does this: > > > > 1. java got the message via http (whatever) > > 2. java does: begin; > > 3. java does: select * from create_message(...) > > 4. java does some checking > > 5. java does: select * from set_message_status(...) > > 6. java does some more checing > > 7. java does commit; (under rare circumstances java does rollback). > > 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. Thank you for your responses. Mario ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
