Re: [SQL] locking problem
>
> hi,
>
> can anyone help me out on the following scenario:
> why this is happening, if i'm doing any thing wrong or its the feature of p=
> ostgres...
I'd say it's a feature - see below.
>
> regards
> cheetor
> ==
> ==
> ==
> PostgreSQL
> Steps:
>
> 1. Create a table
> create table mytab (name varchar(100), marks NUMERIC(9));
>
> 2. insert a row into the table:
> INSERT INTO mytab (name, marks) VALUES ('abc', 3);
>
> 3. compile the function myproc (at end of mail)
>
> 4. Open sql prompt and type:
> begin;
> select myproc(1, 'xyz', 3, 'abc', 10, 'pqr');
>
> This would insert into the table the values 'xyz' and 1.
>
> 5. Open another sql prompt and type:
> begin;
> select myproc(10, 'pqr', 3, 'abc', 1, 'xyz');
>
> This would try and insert into the table values 'pqr' and 10.
>
> But as the query in step4 has locked the table records, the query of
> step 5 would wait..
>
> 6. On the first sql prompt type commit;
> This would let the transaction of step 5 complete, but it outputs the
> statement "not exists". This means that even after the transaction was
> commited, the insert of step 4 was not visible in query of step 5.
My understanding of PG transactions is within a transaction started
with BEGIN; you'll see only what was committed before the BEGIN;
So even after commit of step 4, step 5 is still in the pre-commit state.
>
> 7. on sql prompt of step 5, again type
> select myproc(10, 'pqr', 3, 'abc', 1, 'xyz');
>
> and this outputs "exists" which means that now the insert is visible.
> Therefore it implies that if the second transaction is blocking on a
> locked resource, after it resumes, it does not see any inserts, but if
> has not blocked, these inserts are visible.
>
> The same steps were tried on oracle 8.1.7.
> Steps:
>
> 1. Create a table
> create table mytab (name varchar(100), marks int);
>
> 2. insert a row into the table:
> INSERT INTO mytab (name, marks) VALUES ('abc', 3);
> commit;
>
> 3. compile the procedure myproc (at end of mail)
>
> 4. Open sql prompt (set server output on) and type:
> exec myproc(1, 'xyz', 2, 'abc', 10, 'pqr');
>
> 5. Open another sql prompt and type (set server output on):
> exec myproc(10, 'pqr', 2, 'abc', 1, 'xyz');
> But as the query is step4 has locked the table records, the query of
> step 5 would wait..
>
> 6. On the first sql type commit;
> This would let the transaction of step 5 complete, and it outputs the
> statement "exists". This means that after the transaction was
> commited, the insert of step 4 is visible in query of step 5.
>
> __
> Postgres function
>
>
> CREATE FUNCTION myproc (INT8, VARCHAR, INT8, VARCHAR, INT8, VARCHAR)
> RETURNS TEXT AS '
> DECLARE
>
> DBMarks ALIAS FOR $1;
> DBName ALIAS FOR $2;
>
> DBMarks2 ALIAS FOR $3;
> DBName2 ALIAS FOR $4;
>
> DBMarks3 ALIAS FOR $5;
> DBName3 ALIAS FOR $6;
>
> DBMarks4 INT8;
> DBName4 VARCHAR (100);
>
> BEGIN
>
> SELECT name, marks
> INTO DBName4, DBMarks4
> FROM mytab
> WHERE name = DBName2
> AND marks = DBMarks2 FOR UPDATE;
>
> raise notice '' name : % : marks : % :'', DBName4, DBMarks4;
>
> INSERT INTO mytab (name, marks) VALUES (DBName, DBMarks);
>
> raise notice ''insert done'';
>
> IF EXISTS(SELECT * FROM mytab WHERE name = DBName3 AND marks =
> DBMarks3)
> THEN
> raise notice ''exists'';
> ELSE
> raise notice ''not exists'';
> END IF;
>
>
> return ''done'';
>
>
> END;
> ' language 'plpgsql';
>
>
Regards, Christoph
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Sometimes referential integrity seems not to work
scott.marlowe wrote: On Mon, 2 Feb 2004, Jan Wieck wrote: Stephan Szabo wrote: > On Sat, 31 Jan 2004, Tom Lane wrote: > >> Stephan Szabo <[EMAIL PROTECTED]> writes: >> > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: >> >> In a database I am working, I sometimes have to delete all the records in >> >> some tables. According to the referential integrity defined in the creation >> >> of the tables, postmaster should not delete the records, but it does. I have >> >> used the following commands: "delete from table_1" and "truncate table_1". >> >> ... >> >> can the postgres user delete records despite referential integrity? >> >> I think the first PG release or two that had TRUNCATE TABLE would allow >> you to apply it despite the existence of foreign-key constraints on the >> table. Recent releases won't though. > > Yeah, truncate didn't worry me much, but the implication that delete from > table_1; worked did. TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks for foreign keys. So I guess Enio is getting but ignoring the error message when trying the delete, but then the truncate does the job in his pre-7.3 database. Yes it can. I think it was starting in 7.3. Okay, so you're the third one correcting me on this. Now can any of you violate a foreign key constraint with anything else than using truncate in a pre-7.3 database? Because I can't do that and that was the original problem. Jan => select * from test2; info - abc'123 123 (2 rows) => begin; BEGIN => truncate test2; TRUNCATE TABLE => rollback; ROLLBACK => select * from test2; info - abc'123 123 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] date function problem
Hi All I have an expiration_date column in my table with data type as timestamp. I need to set that as one year after the current time. how do i do that. I am getting a casting problem whenever i try something. something like now()+ 1 year. Please let me know --mohan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] date function problem
--On Tuesday, February 03, 2004 10:51:45 -0500 [EMAIL PROTECTED] wrote: Hi All I have an expiration_date column in my table with data type as timestamp. I need to set that as one year after the current time. how do i do that. I am getting a casting problem whenever i try something. something like now()+ 1 year. Please let me know ler=# select now()+'1 year'; ?column? --- 2005-02-03 10:00:40.913383-06 (1 row) ler=# ler=# select version(); version --- - PostgreSQL 7.4.1 on i386-portbld-freebsd5.2, compiled by GCC cc (GCC) 3.3.3 [FreeBSD] 20031106 (1 row) ler=# --mohan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [SQL] Sometimes referential integrity seems not to work
On Tue, 3 Feb 2004, Jan Wieck wrote: > scott.marlowe wrote: > > > On Mon, 2 Feb 2004, Jan Wieck wrote: > > > >> Stephan Szabo wrote: > >> > >> > On Sat, 31 Jan 2004, Tom Lane wrote: > >> > > >> >> Stephan Szabo <[EMAIL PROTECTED]> writes: > >> >> > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: > >> >> >> In a database I am working, I sometimes have to delete all the records in > >> >> >> some tables. According to the referential integrity defined in the creation > >> >> >> of the tables, postmaster should not delete the records, but it does. I have > >> >> >> used the following commands: "delete from table_1" and "truncate table_1". > >> >> >> ... > >> >> >> can the postgres user delete records despite referential integrity? > >> >> > >> >> I think the first PG release or two that had TRUNCATE TABLE would allow > >> >> you to apply it despite the existence of foreign-key constraints on the > >> >> table. Recent releases won't though. > >> > > >> > Yeah, truncate didn't worry me much, but the implication that delete from > >> > table_1; worked did. > >> > >> TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks > >> for foreign keys. So I guess Enio is getting but ignoring the error > >> message when trying the delete, but then the truncate does the job in > >> his pre-7.3 database. > > > > Yes it can. I think it was starting in 7.3. > > Okay, so you're the third one correcting me on this. Now can any of you > violate a foreign key constraint with anything else than using truncate > in a pre-7.3 database? Because I can't do that and that was the original > problem. OK, I just tested the truncate foreign key truncate on 7.2, and other than truncate, I've not found any way to delete the fk data from the parent table. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Sometimes referential integrity seems not to work
On Tue, 3 Feb 2004, Jan Wieck wrote: > scott.marlowe wrote: > > > On Mon, 2 Feb 2004, Jan Wieck wrote: > > > >> Stephan Szabo wrote: > >> > >> > On Sat, 31 Jan 2004, Tom Lane wrote: > >> > > >> >> Stephan Szabo <[EMAIL PROTECTED]> writes: > >> >> > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: > >> >> >> In a database I am working, I sometimes have to delete all the records in > >> >> >> some tables. According to the referential integrity defined in the creation > >> >> >> of the tables, postmaster should not delete the records, but it does. I have > >> >> >> used the following commands: "delete from table_1" and "truncate table_1". > >> >> >> ... > >> >> >> can the postgres user delete records despite referential integrity? > >> >> > >> >> I think the first PG release or two that had TRUNCATE TABLE would allow > >> >> you to apply it despite the existence of foreign-key constraints on the > >> >> table. Recent releases won't though. > >> > > >> > Yeah, truncate didn't worry me much, but the implication that delete from > >> > table_1; worked did. > >> > >> TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks > >> for foreign keys. So I guess Enio is getting but ignoring the error > >> message when trying the delete, but then the truncate does the job in > >> his pre-7.3 database. > > > > Yes it can. I think it was starting in 7.3. > > Okay, so you're the third one correcting me on this. Now can any of you > violate a foreign key constraint with anything else than using truncate > in a pre-7.3 database? Because I can't do that and that was the original > problem. Our production machine is running 7.2, and I get this: begin; BEGIN =# truncate test; ERROR: TRUNCATE TABLE cannot run inside a transaction block =# commit; COMMIT =# select * from test; info | id --+ abc | 1 def | 2 (2 rows) So, at least in 7.2, it won't let me truncate. I'm not running any 7.3 boxes, just 7.4 and 7.2, so I can't test it on 7.3. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] date function problem
Dnia 2004-02-03 16:51, Użytkownik [EMAIL PROTECTED] napisał: Hi All I have an expiration_date column in my table with data type as timestamp. I need to set that as one year after the current time. how do i do that. I am getting a casting problem whenever i try something. something like now()+ 1 year. Please let me know now()+'1 year'::interval I suggest you reading more about interval datatype in documentation. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] request to plsql tutorial
please send me all the tutorials related to plsql i am in urgent need of it it would be very helpful for me if u send it as soon as possible thank you __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL]
Hi I have a postgresql in my red hat linux machine which will be our webserver. previously we had mysql in suse linux in another machine. so we are having a new webserver now with postgresql. I want to transfer my tables in mysql to postgresql so that I continue with my stuff in the new server. I tried to search for the transfer of tables from mysql to postgresql located on different machines, but could not figure out exactly how to start and proceed. could you please guide me through this process thank you prashanthi ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Executing dynamic queries (EXECUTE)
Hello Tomasz, I´m going to study the FOR-IN-EXECUTE statement and to try again. Thanks, Carla Mello - Original Message - From: "Tomasz Myrta" <[EMAIL PROTECTED]> To: "Carla Mello" <[EMAIL PROTECTED]> Cc: "Lista dyskusyjna pgsql-sql" <[EMAIL PROTECTED]> Sent: Sunday, February 01, 2004 5:43 PM Subject: Re: [SQL] Executing dynamic queries (EXECUTE) > Dnia 2004-01-27 16:25, Użytkownik Carla Mello napisał: > > > > Hello! > > > > I need to execute a dynamic query and capture your result in a > > integer variable. > > > > I´m using the statement "EXECUTE string", but I don´t obtain to > > capture the result of dynamic query. > > v_count:= EXECUTE v_query; > > Documentation: > 19.5.4. Executing dynamic queries > > "The results from SELECT queries are discarded by EXECUTE, and SELECT > INTO is not currently supported within EXECUTE. So, the only way to > extract a result from a dynamically-created SELECT is to use the > FOR-IN-EXECUTE form described later." > > Regards, > Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] SQL query
Hello, I would like to retrieve all the records from table A which have given lang_id and its modification date is later then modification date of the same id record with lang_id='pl'. Example: table A - data example == id | modification_date | lang_id +-+-- abc | 2002-10-11 10:12:11 | en abc | 2002-11-12 11:12:11 | pl abc | 2002-11-11 18:12:00 | de sample | 2003-04-15 22:43:14 | pl sample | 2003-05-16 11:10:15 | en sample | 2003-11-11 18:11:10 | de If given lang_id would be 'en' the following record should be choosed: sample | 2003-05-16 11:10:15 | en (it has mofication date later then record with id = sample and lang_id = pl) If you have any idea how to make SQL query for this, please help me. Thanx, Paul Czubilinski ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL]
On Tue, 3 Feb 2004, Prashanthi Muthyala wrote: > Hi > > I have a postgresql in my red hat linux machine which will be our webserver. > previously we had mysql in suse linux in another machine. so we are having > a new webserver now with postgresql. I want to transfer my tables in mysql > to postgresql so that I continue with my stuff in the new server. > > I tried to search for the transfer of tables from mysql to postgresql > located on different machines, but could not figure out exactly how to > start and proceed. > > could you please guide me through this process Download the postgresql source package, untar it, and look in the /contrib/mysql directory, or go to: http://ziet.zhitomir.ua/~fonin/code/ and download the latest version there. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL]
On Tuesday 03 February 2004 17:46, Prashanthi Muthyala wrote: > > I tried to search for the transfer of tables from mysql to postgresql > located on different machines, but could not figure out exactly how to > start and proceed. There is a section on converting from MySQL at the url below. http://techdocs.postgresql.org/ Feel free to come back and ask some more if you have any problems. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] request to plsql tutorial
On Tuesday 03 February 2004 13:06, arun raj wrote: > please send me all the tutorials related > to plsql > i am in urgent need of it > it would be very helpful for > me if u send it as soon as possible Sorry - I'm not sure what plsql is. Do you mean plpgsql? Anyway, assuming you've read the manuals, two good places to look for further info are: http://techdocs.postgresql.org/ http://www.varlena.com/GeneralBits/ -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL]
Look at http://techdocs.postgresql.org/#convertfrom There are several documents discussing converting from MySQL to PostgreSQL. Jan Prashanthi Muthyala wrote: Hi I have a postgresql in my red hat linux machine which will be our webserver. previously we had mysql in suse linux in another machine. so we are having a new webserver now with postgresql. I want to transfer my tables in mysql to postgresql so that I continue with my stuff in the new server. I tried to search for the transfer of tables from mysql to postgresql located on different machines, but could not figure out exactly how to start and proceed. could you please guide me through this process thank you prashanthi ---(end of broadcast)--- TIP 8: explain analyze is your friend -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
