Re: [SQL] pl/pgsql and returning rows
From: "wade" <[EMAIL PROTECTED]> > create function get_details(int4) returns details as ' > declare > ret details%ROWTYPE; > site_recrecord; > cntct contacts%ROWTYPE; > begin > select into site_rec * sites_table where id = $1 limit 1; > select into cntct * from contacts where id = site_rec.contact; > > -- and then i populate rows of ret. > ret.name := cntct.name; > ret.ip := site_rec.ip; > . > . > . > return ret; > end; > ' language 'plpgsql'; > > now the problem is when is when I do a: > SELECT get_details(55); > all i get is a single oid-looking return value: > get_details > - > 136295592 > (1 row) Sorry - you can't return a row from a function at the present time (except for trigger functions which are special) although I believe this is on the todo list for a later 7.x release. Just from the top of my head, you might try a view with a select rule, although I'm not completely clear what your objectives are. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: Still don't know how to build this string ? how to concat ??
Hello I'm closer to a solution. The query results is : Result: 01 1440 02 1460 03 1398 The values (1440, ...) are the last entries of z_u_umfang for each z_u_typ. This is correct since in the function the list:= ... is overwritten until the last record is read. When I try to concat the list in the manner of list := list || text(rec.z_u_umfang); the zustring is empty ! Thanks for any help ... jr Query : select distinct z_u_typ, buildString(z_u_typ) as zustring from zylinder_umfang Function: CREATE FUNCTION buildString(bpchar) RETURNS text AS ' DECLARE list text; rec record; BEGIN FOR rec IN SELECT z_u_umfang FROM zylinder_umfang WHERE z_u_typ = $1; list := text(rec.z_u_umfang); END LOOP; RETURN list; END; ' LANGUAGE 'plpgsql'; PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Re: Still don't know how to build this string ?
Hey folk's Thanks everybody helping me with my problem, it is solved ! The problem was that I took double quotes instead of single quotes ... arg. By the way, is there any doc's about plpgsql ? jr PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315 ---(end of broadcast)--- TIP 3: 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] all views in database broken at once
Mathijs Brands wrote: > On Sat, Mar 24, 2001 at 11:36:56PM -0500, Tom Lane allegedly wrote: > > Mathijs Brands <[EMAIL PROTECTED]> writes: > > > How about being able to recompile them (keeping the SQL around in the > > > system catalogs)? Doesn't Oracle allow you to do something like that? > > > > That's another possibility. It's not real clear that there's any > > advantage to storing rules in preparsed form to begin with --- if > > we just stored the original text and reparsed it each time it was > > read, the system would be vastly more flexible, and probably not > > noticeably slower. > > But every bit of performance counts, of course... The question is if parsing the original query is that more expensive than converting the printed node tree back into it's binary representation, what's done now. And then again, this is only done when the relation is opened and it's RelationData not found in the relcache. If the relcache serves well, this happens once per connection. OTOH, due to toast we don't need to save space in the pg_rewrite tuples any more. Adding two new attributes to hold just the backparsed rule qualification and actions (backparsed from the nodes like done for pg_dump), it might be easy to create a utility that recompiles rules - for one or all relations. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 3: 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] Functions and Triggers
Cedar Cox wrote: > > CREATE FUNCTION lastupdated() RETURNS opaque AS ' > begin > new.last_updated := CURRENT_TIMESTAMP; > return new; > end; > ' LANGUAGE 'plpgsql'; > > CREATE TRIGGER trigname BEFORE INSERT OR UPDATE on tblname > FOR EACH ROW EXECUTE PROCEDURE lastupdated(); > > Note: you could use now() instead of CURRENT_TIMESTAMP > > Note2: on a BEFORE trigger you must return new or old. If you return null > the statement will be aborted. (?) If you return NULL from a BEFORE trigger the action on that particular row will be silently suppressed. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Upgrading from 6.2 to 7
We are planing to upgrade to version 7 of Postgres, I understand that you cannot use version 6 databases with 7. Where can I find information on how to convert these Databases ? Sean Ion Solutions
Re: [SQL] Re: Still don't know how to build this string ? how to concat ??
Tuesday, March 27, 2001, 10:20:18 AM, you wrote: jrpc> jrpc> Result: jrpc> 01 1440 jrpc> 02 1460 jrpc> 03 1398 jrpc> The values (1440, ...) are the last entries of z_u_umfang for each z_u_typ. jrpc> This is correct since in the function the list:= ... is overwritten until jrpc> the last record is read. jrpc> When I try to concat the list in the manner of list := list || jrpc> text(rec.z_u_umfang); the zustring is empty ! jrpc> Thanks for any help ... jr jrpc> Query : jrpc> select distinct z_u_typ, buildString(z_u_typ) as zustring from jrpc> zylinder_umfang jrpc> Function: jrpc> CREATE FUNCTION buildString(bpchar) RETURNS text AS ' jrpc> DECLARE jrpc> list text; jrpc> rec record; jrpc> BEGIN jrpc> FOR rec IN SELECT z_u_umfang FROM zylinder_umfang WHERE z_u_typ jrpc> = $1; jrpc> list := text(rec.z_u_umfang); jrpc> END LOOP; jrpc> RETURN list; jrpc> END; jrpc> ' LANGUAGE 'plpgsql'; You seem to be constantly re-assigning "list", rather than adding to it with each iteration of the "for loop". Would: ... list := list || ',' || text(rec.z_u_umfang) ... be what your solution is missing? -- Best regards, Andymailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Upgrading from 6.2 to 7
"Sean Weissensee" <[EMAIL PROTECTED]> writes: > We are planing to upgrade to version 7 of Postgres, > I understand that you cannot use version 6 databases with 7. > Where can I find information on how to convert these Databases ? Short version: pg_dumpall (with old version), initdb, reload. Long version: Great Bridge's docs explain upgrading in excruciating detail ;-). You can download PDFs for free from http://www.greatbridge.com/docs/ regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Re: Still don't know how to build this string ? how to concat ??
Hello Andy, Tuesday, March 27, 2001, 3:22:37 PM, you wrote: AC> Tuesday, March 27, 2001, 10:20:18 AM, you wrote: jrpc>> jrpc>> Result: jrpc>> 01 1440 jrpc>> 02 1460 jrpc>> 03 1398 jrpc>> The values (1440, ...) are the last entries of z_u_umfang for each z_u_typ. jrpc>> This is correct since in the function the list:= ... is overwritten until jrpc>> the last record is read. jrpc>> When I try to concat the list in the manner of list := list || jrpc>> text(rec.z_u_umfang); the zustring is empty ! jrpc>> Thanks for any help ... jr jrpc>> Query : jrpc>> select distinct z_u_typ, buildString(z_u_typ) as zustring from jrpc>> zylinder_umfang jrpc>> Function: jrpc>> CREATE FUNCTION buildString(bpchar) RETURNS text AS ' jrpc>> DECLARE jrpc>> list text; jrpc>> rec record; jrpc>> BEGIN jrpc>> FOR rec IN SELECT z_u_umfang FROM zylinder_umfang WHERE z_u_typ jrpc>> = $1; jrpc>> list := text(rec.z_u_umfang); jrpc>> END LOOP; jrpc>> RETURN list; jrpc>> END; jrpc>> ' LANGUAGE 'plpgsql'; AC> You seem to be constantly re-assigning "list", rather than adding to AC> it with each iteration of the "for loop". AC> Would: AC> ... AC> list := list || ',' || text(rec.z_u_umfang) AC> ... AC> be what your solution is missing? I read it again and noticed your comment about having tried || already - I must learn to read messages fully... But, I did wonder if the semicolon ";" at the end of the for loop is what is causing your problem? The syntax explanation I have does not show the ";", therefore it is possible that the loop is executing a null instruction ";", moving on the the list assignment, and then finding the unmatched "end loop" which might not throw an error. Can anyone comment if this is a plausible explanation? -- Best regards, Andymailto:[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])
Re: [SQL] Re: Still don't know how to build this string ? how to concat ??
If I understand corrently, the idea is to get a comma
delimited list as a result.
here is a modified function with a slightly different set of names
for the table. The commands include commands to add and drop the
needed tables.
CREATE TABLE emps (username text, userid int4);
INSERT INTO emps VALUES ('User 1', 1);
INSERT INTO emps VALUES ('User X', 2);
INSERT INTO emps VALUES ('User 2', 2);
INSERT INTO emps VALUES (null, 2);
INSERT INTO emps VALUES ('something', null);
CREATE FUNCTION com_delim(int4) RETURNS text AS '
DECLARE
rec record;
str text;
comstr text;
BEGIN
str := ;
comstr := ;
FOR rec IN SELECT username FROM emps WHERE userid = $1 AND NOT username
ISNULL LOOP
str := str || comstr || rec.username;
comstr := '','';
END LOOP;
RETURN str;
END;
' LANGUAGE 'plpgsql';
SELECT com_delim(2) FROM emps;
DROP FUNCTION com_delim(int4);
DROP TABLE emps;
Troy
>
> Hello Andy,
>
> Tuesday, March 27, 2001, 3:22:37 PM, you wrote:
>
> AC> Tuesday, March 27, 2001, 10:20:18 AM, you wrote:
>
> jrpc>>
>
> jrpc>> Result:
> jrpc>> 01 1440
> jrpc>> 02 1460
> jrpc>> 03 1398
>
> jrpc>> The values (1440, ...) are the last entries of z_u_umfang for each z_u_typ.
> jrpc>> This is correct since in the function the list:= ... is overwritten until
> jrpc>> the last record is read.
> jrpc>> When I try to concat the list in the manner of list := list ||
> jrpc>> text(rec.z_u_umfang); the zustring is empty !
>
> jrpc>> Thanks for any help ... jr
>
> jrpc>> Query :
> jrpc>> select distinct z_u_typ, buildString(z_u_typ) as zustring from
> jrpc>> zylinder_umfang
>
> jrpc>> Function:
> jrpc>> CREATE FUNCTION buildString(bpchar) RETURNS text AS '
> jrpc>> DECLARE
> jrpc>> list text;
> jrpc>> rec record;
> jrpc>> BEGIN
> jrpc>> FOR rec IN SELECT z_u_umfang FROM zylinder_umfang WHERE z_u_typ
> jrpc>> = $1;
> jrpc>> list := text(rec.z_u_umfang);
> jrpc>> END LOOP;
> jrpc>> RETURN list;
> jrpc>> END;
> jrpc>> ' LANGUAGE 'plpgsql';
>
> AC> You seem to be constantly re-assigning "list", rather than adding to
> AC> it with each iteration of the "for loop".
>
> AC> Would:
> AC> ...
> AC> list := list || ',' || text(rec.z_u_umfang)
> AC> ...
> AC> be what your solution is missing?
>
> I read it again and noticed your comment about having tried || already
> - I must learn to read messages fully...
>
> But, I did wonder if the semicolon ";" at the end of the for loop is
> what is causing your problem? The syntax explanation I have does not
> show the ";", therefore it is possible that the loop is executing a
> null instruction ";", moving on the the list assignment, and then
> finding the unmatched "end loop" which might not throw an error.
>
> Can anyone comment if this is a plausible explanation?
>
> --
> Best regards,
> Andymailto:[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])
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
RE: [SQL] AY
What does "=P0] Ick ,O$0;r7N+d, ,O$0;r&r*:AYmailto:[EMAIL PROTECTED]]On Behalf Of S.F. Lee Sent: Monday, March 26, 2001 5:40 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: [SQL] AY wrote: > > I'm using 7.1 Beta 3, which has been pretty > stable up until now. This > > morning, I went to export a function I spent 5 > hours debugging on on > > Friday to text so that I could have a copy of the > final version. To my > > horror, the function was GONE from the system > catalog (pg_proc). > > Ick. Were you maybe working on it inside a > transaction that you forgot > to commit? =P0] Ick ,O$0;r7N+d, ,O$0;r&r*:AYhttp://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Replace into...?
Diehl, Jeffrey writes: > I'm in the process of migrating a database and application suite from mysql > to postgresql. The problem is that I've used > mysql's "replace into..." quite frequently... begin transaction; update ... insert ... commit; or some permutation thereof. If you need to do a lot of this, writing a PL/pgSQL function might be worthwhile. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Replace into...?
"Diehl, Jeffrey" <[EMAIL PROTECTED]> writes: > I'm in the process of migrating a database and application suite from mysql > to postgresql. The problem is that I've used > mysql's "replace into..." quite frequently... > Does anyone know of a "clean" way to implement this feature in postgresql? > I really don't want my applications to have to know what indexes are in > place for a given table. I'm confused. What does "replace into" have to do with having to know what indexes are in place? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] possible row locking bug in 7.0.3 & 7.1
I'm having a problem with functions written in SQL. Specifically, they
don't seem to be adhering to Postgres locking rules. For the record, I'm
using postgres 7.0.3, installed from RPMs, on Red Hat 6.2. I got the same
results with postgres 7.1 beta 6, installed from sources.
Here's what I'm seeing:
(psql input represented by '<<'; output represented by '>>'.)
session1<< create table idseq
session1<< (
session1<< name varchar(32) not null,
session1<< id int8 not null default 0
session1<< );
session1>> CREATE
session1<< insert into idseq values ('myid');
session1>> INSERT 18734 1
Each row in the table is supposed to represent a named numeric sequence,
much like the sequences built into postgres. (Mine use an int8 though,
so their values can be much higher.)
session1<< create function nextid( varchar(32)) returns int8 as '
session1<< select * from idseq where name = $1::text for update;
session1<< update idseq set id = id + 1 where name = $1::text;
session1<< select id from idseq where name = $1::text;
session1<< ' language 'sql';
session1>> CREATE
The idea here is that the select...for update within the nextid() function
will establish a row level lock, preventing two concurrent function calls
from overlapping.
Next, I test with two sessions as follows:
session1<< begin;
session1>> BEGIN
session2<< begin;
session2>> BEGIN
session1<< select nextid('myid');
session1>> nextid
session1>>
session1>> 1
session1>> (1 row)
session2<< select nextid('myid');
(session2 blocks until session1 completes its transaction)
session1<< commit;
session1>> COMMIT
(session2 resumes)
session2>> nextid
session2>>
session2>> 0
session2>> (1 row)
What gives??? I expected the second call to nextid() to return 2!
session2<< commit;
session2>> COMMIT
session2<< select * from idseq;
session2>> name | id
session2>> --+
session2>> myid | 2
session2>> (1 row)
session1<< select * from idseq;
session1>> name | id
session1>> --+
session1>> myid | 2
session1>> (1 row)
As you can see, my nextid() function is not synchronized the way I hoped.
I don't know why, though. Can someone help? I'm going to try out some of my
SPI functions with 7.1 beta 6, to see if they exhibit a locking problem as
well.
Thanks,
Forest Wilkinson
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] RE: pl/pgsql and returning rows
As a workaround, you can insert your row into an existing table, then retrieve it from there later. I think you need to enumerate all of the fields, as in 'INSERT INTO table VALUES (ret.field1, ret.field2,...ret.fieldn);'. At least, I haven't succeeded any other way. Messy, but the best method available right now. > -Original Message- > From: Richard Huxton [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, March 27, 2001 2:27 AM > To: [EMAIL PROTECTED]; wade > Subject: Re: pl/pgsql and returning rows > > From: "wade" <[EMAIL PROTECTED]> > > > create function get_details(int4) returns details as ' > > declare > > ret details%ROWTYPE; > > site_recrecord; > > cntct contacts%ROWTYPE; > > begin > > select into site_rec * sites_table where id = $1 limit 1; > > select into cntct * from contacts where id = site_rec.contact; > > > > -- and then i populate rows of ret. > > ret.name := cntct.name; > > ret.ip := site_rec.ip; > > . > > . > > . > > return ret; > > end; > > ' language 'plpgsql'; > > > > now the problem is when is when I do a: > > SELECT get_details(55); > > all i get is a single oid-looking return value: > > get_details > > - > > 136295592 > > (1 row) > > Sorry - you can't return a row from a function at the present time (except > for trigger functions which are special) although I believe this is on the > todo list for a later 7.x release. > > Just from the top of my head, you might try a view with a select rule, > although I'm not completely clear what your objectives are. > > - Richard Huxton > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [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])
Re: [SQL] RE: pl/pgsql and returning rows
MY book in chapter 18 has a Pl/PgSQL function called change_statename that does insert/update automatically. http://www.postgresql.org/docs/awbook.html > As a workaround, you can insert your row into an existing table, then > retrieve it from there later. I think you need to enumerate all of the > fields, as in 'INSERT INTO table VALUES (ret.field1, > ret.field2,...ret.fieldn);'. At least, I haven't succeeded any other way. > Messy, but the best method available right now. > > > -Original Message- > > From: Richard Huxton [SMTP:[EMAIL PROTECTED]] > > Sent: Tuesday, March 27, 2001 2:27 AM > > To: [EMAIL PROTECTED]; wade > > Subject:Re: pl/pgsql and returning rows > > > > From: "wade" <[EMAIL PROTECTED]> > > > > > create function get_details(int4) returns details as ' > > > declare > > > ret details%ROWTYPE; > > > site_recrecord; > > > cntct contacts%ROWTYPE; > > > begin > > > select into site_rec * sites_table where id = $1 limit 1; > > > select into cntct * from contacts where id = site_rec.contact; > > > > > > -- and then i populate rows of ret. > > > ret.name := cntct.name; > > > ret.ip := site_rec.ip; > > > . > > > . > > > . > > > return ret; > > > end; > > > ' language 'plpgsql'; > > > > > > now the problem is when is when I do a: > > > SELECT get_details(55); > > > all i get is a single oid-looking return value: > > > get_details > > > - > > > 136295592 > > > (1 row) > > > > Sorry - you can't return a row from a function at the present time (except > > for trigger functions which are special) although I believe this is on the > > todo list for a later 7.x release. > > > > Just from the top of my head, you might try a view with a select rule, > > although I'm not completely clear what your objectives are. > > > > - Richard Huxton > > > > > > ---(end of broadcast)--- > > TIP 1: subscribe and unsubscribe commands go to [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]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] RE: pl/pgsql and returning rows
FYI, I am always looking for additional examples that I should add in the next edition. > Yes: good example! I keep a printed copy on my desk... :-) > > > -Original Message- > > From: Bruce Momjian [SMTP:[EMAIL PROTECTED]] > > Sent: Tuesday, March 27, 2001 3:52 PM > > To: Jeff Eckermann > > Cc: [EMAIL PROTECTED]; wade > > Subject:Re: [SQL] RE: pl/pgsql and returning rows > > > > MY book in chapter 18 has a Pl/PgSQL function called change_statename > > that does insert/update automatically. > > > > http://www.postgresql.org/docs/awbook.html > > > > > As a workaround, you can insert your row into an existing table, then > > > retrieve it from there later. I think you need to enumerate all of the > > > fields, as in 'INSERT INTO table VALUES (ret.field1, > > > ret.field2,...ret.fieldn);'. At least, I haven't succeeded any other > > way. > > > Messy, but the best method available right now. > > > > > > > -Original Message- > > > > From: Richard Huxton [SMTP:[EMAIL PROTECTED]] > > > > Sent: Tuesday, March 27, 2001 2:27 AM > > > > To: [EMAIL PROTECTED]; wade > > > > Subject:Re: pl/pgsql and returning rows > > > > > > > > From: "wade" <[EMAIL PROTECTED]> > > > > > > > > > create function get_details(int4) returns details as ' > > > > > declare > > > > > ret details%ROWTYPE; > > > > > site_recrecord; > > > > > cntct contacts%ROWTYPE; > > > > > begin > > > > > select into site_rec * sites_table where id = $1 limit 1; > > > > > select into cntct * from contacts where id = site_rec.contact; > > > > > > > > > > -- and then i populate rows of ret. > > > > > ret.name := cntct.name; > > > > > ret.ip := site_rec.ip; > > > > > . > > > > > . > > > > > . > > > > > return ret; > > > > > end; > > > > > ' language 'plpgsql'; > > > > > > > > > > now the problem is when is when I do a: > > > > > SELECT get_details(55); > > > > > all i get is a single oid-looking return value: > > > > > get_details > > > > > - > > > > > 136295592 > > > > > (1 row) > > > > > > > > Sorry - you can't return a row from a function at the present time > > (except > > > > for trigger functions which are special) although I believe this is on > > the > > > > todo list for a later 7.x release. > > > > > > > > Just from the top of my head, you might try a view with a select rule, > > > > although I'm not completely clear what your objectives are. > > > > > > > > - Richard Huxton > > > > > > > > > > > > ---(end of > > broadcast)--- > > > > TIP 1: subscribe and unsubscribe commands go to > > [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]) > > > > > > > > > -- > > Bruce Momjian| http://candle.pha.pa.us > > [EMAIL PROTECTED] | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 3: 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] Re: psql win32 version
On Thu, 22 Mar 2001 11:45, Jack wrote: > Hi, Christopher > > Thank you for your help, but the URL > http://www.zeos.dn.ua/download/ipgsql-1.6.2.zip is a broken link. Would > you please check again. Thank you very much. The problem is that your ISP has a vicious anti spam policy in place. They block all contact with certain other isps at the ip address level. My ISP's mail server was blocked by yours, and I suspect that the Ukrainian address is being blocked too. The above url _is_ a worker. I have d/l'ed the file myself from there. You might like to try getting the file via an anonimizer. -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<-- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: psql win32 version
On Thu, 22 Mar 2001 11:45, Jack wrote: > Hi, Christopher > > Thank you for your help, but the URL > http://www.zeos.dn.ua/download/ipgsql-1.6.2.zip is a broken link. Would > you please check again. Thank you very much. One of these may work for you. http://www.google.com/search?q=ipgsql-1.6.2.zip%A0&btnG=Google+Search -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<-- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: psql win32 version
Chris, Jack, Etc: > > Thank you for your help, but the URL > > http://www.zeos.dn.ua/download/ipgsql-1.6.2.zip is a broken link. > Would > > you please check again. Thank you very much. > > The problem is that your ISP has a vicious anti spam policy in place. > They block all contact with certain other isps at the ip address > level. > My ISP's mail server was blocked by yours, and I suspect that the > Ukrainian address is being blocked too. Also, please note that if you need Win32 tools for Postgres 7.1, you'll need the new source from Great Bridge, who is now maintaining them. Look in their Projects area. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
Philip Warner <[EMAIL PROTECTED]> writes: >> The workaround for Forest is to make the final SELECT be a SELECT FOR >> UPDATE, so that it's playing by the same rules as the earlier commands. > Eek. Does this seem good to you? I did call it a workaround ;-) I don't think that we dare try to make any basic changes in MVCC for 7.1 at this late hour, so Forest is going to have to live with that answer for awhile. But I would like to see a cleaner answer in future releases. As I've opined before, the whole EvalPlanQual mechanism strikes me as essentially bogus in any case... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
Forest Wilkinson <[EMAIL PROTECTED]> writes: > session1<< create function nextid( varchar(32)) returns int8 as ' > session1<< select * from idseq where name = $1::text for update; > session1<< update idseq set id = id + 1 where name = $1::text; > session1<< select id from idseq where name = $1::text; > session1<< ' language 'sql'; > [ doesn't work as expected in parallel transactions ] This is a fairly interesting example. What I find is that at the final SELECT, the function can see both the tuple outdated by the other transaction AND the new tuple it has inserted. (You can demonstrate that by doing select count(id) instead of select id.) Whichever one happens to be visited first is the one that gets returned by the function, and that's generally the older one in this example. MVCC seems to be operating as designed here, more or less. The outdated tuple is inserted by a known-committed transaction, and deleted by a transaction that's also committed, but one that committed *since the start of the current transaction*. So its effects should not be visible to the SELECT, and therefore the tuple should be visible. The anomalous behavior is not really in the final SELECT, but in the earlier commands that were able to see the effects of a transaction committed later than the start of the second session's transaction. The workaround for Forest is to make the final SELECT be a SELECT FOR UPDATE, so that it's playing by the same rules as the earlier commands. But I wonder whether we ought to rethink the MVCC rules so that that's not necessary. I have no idea how we might change the rules though. If nothing else, we should document this issue better: SELECT and SELECT FOR UPDATE have different visibility rules, so you probably don't want to intermix them. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
At 18:14 27/03/01 -0500, Tom Lane wrote: >Forest Wilkinson <[EMAIL PROTECTED]> writes: >> session1<< create function nextid( varchar(32)) returns int8 as ' >> session1<< select * from idseq where name = $1::text for update; >> session1<< update idseq set id = id + 1 where name = $1::text; >> session1<< select id from idseq where name = $1::text; >> session1<< ' language 'sql'; >> [ doesn't work as expected in parallel transactions ] > >What I find is that at the final >SELECT, the function can see both the tuple outdated by the other >transaction AND the new tuple it has inserted. Surely we should distinguish between real new tuples, and new tuple versions? I don't think it's ever reasonable behaviour to see two versions of the same row. >(You can demonstrate >that by doing select count(id) instead of select id.) Whichever one >happens to be visited first is the one that gets returned by the >function, and that's generally the older one in this example. > >MVCC seems to be operating as designed here, more or less. The outdated >tuple is inserted by a known-committed transaction, and deleted by a >transaction that's also committed, but one that committed *since the >start of the current transaction*. So its effects should not be visible >to the SELECT, and therefore the tuple should be visible. The anomalous >behavior is not really in the final SELECT, but in the earlier commands >that were able to see the effects of a transaction committed later than >the start of the second session's transaction. Looking at the docs, I see that 'SERIALIZABLE' has the same visibility rules as 'READ COMMITTED', which is very confusing. I expect that a Read Committed TX should see committed changes for a TX that commits during the first TX (although this may need to be limited to TXs started before the first TX, but I'm not sure). If this is not the case, then we never get non-repeatable reads, AFAICT: P2 (Non-repeatable read): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted. which is one of the differences between SERIALIZABLE and READ-COMMITTED. >The workaround for Forest is to make the final SELECT be a SELECT FOR >UPDATE, so that it's playing by the same rules as the earlier commands. Eek. Does this seem good to you? I would expect that SELECT and SELECT...FOR UPDATE should return the same result set. >But I wonder whether we ought to rethink the MVCC rules so that that's >not necessary. I have no idea how we might change the rules though. Disallowing visibility of two versions of the same row would help. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
Forest Wilkinson <[EMAIL PROTECTED]> writes: > If I remember correctly, UPDATE establishes a lock on the affected rows, > which will block another UPDATE on the same rows for the duration of the > transaction. If that's true, shouldn't I be able to achieve my desired > behavior by removing the initial as follows: > create function nextid( varchar(32)) returns int8 as ' > update idseq set id = id + 1 where name = $1::text; > select id from idseq where name = $1::text; > ' language 'sql'; > Or, would I still have to add FOR UPDATE to that final SELECT? You're right, the initial SELECT FOR UPDATE is a waste of cycles considering that you're not using the value it returns. But you'll still need the last select to be FOR UPDATE so that it plays by the same rules as the UPDATE does. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] possible row locking bug in 7.0.3 & 7.1
On Tuesday 27 March 2001 15:14, Tom Lane wrote: > Forest Wilkinson <[EMAIL PROTECTED]> writes: > > session1<< create function nextid( varchar(32)) returns int8 as ' > > session1<< select * from idseq where name = $1::text for update; > > session1<< update idseq set id = id + 1 where name = $1::text; > > session1<< select id from idseq where name = $1::text; > > session1<< ' language 'sql'; > > [ doesn't work as expected in parallel transactions ] [snip] > The workaround for Forest is to make the final SELECT be a SELECT FOR > UPDATE, so that it's playing by the same rules as the earlier commands. > But I wonder whether we ought to rethink the MVCC rules so that that's > not necessary. I have no idea how we might change the rules though. > If nothing else, we should document this issue better: SELECT and SELECT > FOR UPDATE have different visibility rules, so you probably don't want > to intermix them. My, that's ugly. (But thanks for the workaround.) If I remember correctly, UPDATE establishes a lock on the affected rows, which will block another UPDATE on the same rows for the duration of the transaction. If that's true, shouldn't I be able to achieve my desired behavior by removing the initial as follows: create function nextid( varchar(32)) returns int8 as ' update idseq set id = id + 1 where name = $1::text; select id from idseq where name = $1::text; ' language 'sql'; Or, would I still have to add FOR UPDATE to that final SELECT? Forest ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
Tom Lane wrote: > > Philip Warner <[EMAIL PROTECTED]> writes: > >> The workaround for Forest is to make the final SELECT be a SELECT FOR > >> UPDATE, so that it's playing by the same rules as the earlier commands. > > > Eek. Does this seem good to you? > > I did call it a workaround ;-) > > I don't think that we dare try to make any basic changes in MVCC for 7.1 > at this late hour, so Forest is going to have to live with that answer > for awhile. But I would like to see a cleaner answer in future > releases. Is it the MVCC's restriction that each query inside a function must use the same snapshot ? > As I've opined before, the whole EvalPlanQual mechanism > strikes me as essentially bogus in any case... > How would you change it ? UPDATE/SELECT FOR UPDATE have to SELECT/UPDATE the latest tuples. I don't think of any simple way for 'SELECT FOR UPDATE' to have the same visibility as simple SELECT. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
