[SQL] Subqueries in from clause?
Hi All, My question for this week: How far is the above subject from beeing implemented? (I've run through the archives, and found some quite old mails in this topic. They mentioned automatically generated temp tables as possibe solutions... Is this the plan even today?) Attila Ps.: Two weeks ago I had a question about multiplee joins. According to Tom's advice I translated the actual snapshot from cvs and after submitting the query with parenthesis on the right place I experienced a speedup factor of 200 (two hundred!!!) comapred to the GEQO. Great. Ps^2.: Also tested tuple toasting up to 4Mbytes of single tuples... I like it :))
[SQL] SQL functions not locking properly?
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.2, installed from RPMs, on Red Hat 6.2.
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 20700 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!
session1<< select * from idseq;
session1>> name | id
session1>> --+
session1>> myid | 1
session1>> (1 row)
session2<< select * from idseq;
session2>> name | id
session2>> --+
session2>> myid | 1
session2>> (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?
Thanks,
Forest Wilkinson
Re: [SQL] select
Hi, there, only possible is null, so select blah from tableblah where field is null; Jeff MacDonald wrote: how would i select all rows where a boolean value is neither t nor f.. ? ie if someone inserted without setting the boolean tag. Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Fascimile : 1 902 542 5386 IRC Nick : bignose -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] SQL functions not locking properly?
Forest Wilkinson <[EMAIL PROTECTED]> writes:
> 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!
Hmm. If you do the same commands without wrapping them in an SQL
function, they operate as expected. I'll bet there's some nasty
interaction between the FOR UPDATE support and the way that SQL
functions twiddle the current-command counter. Sigh, another bug.
You might try it in plpgsql to see if that language has the same
bug (and please report back the results!). If so, the only workaround
right now is not to use a function, or possibly to code it in C using
the SPI interface.
I'm up to my armpits in subselect-in-FROM right now, but will put this
on my to-do list. Will look at it in a week or two if no one else has
fixed it before then...
regards, tom lane
Re: [SQL] Subqueries in from clause?
Meszaros Attila <[EMAIL PROTECTED]> writes: > How far is the above subject from beeing implemented? I'm looking at it right now ... no promises yet, though. regards, tom lane
Re: [SQL] SQL functions not locking properly?
Forest Wilkinson wrote:
>
> 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.2, installed from RPMs, on Red Hat 6.2.
>
> 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 20700 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;
Queries in a function are executed under the
snapshot of the top level query.
So SELECT could never see changes made by
other backends once the top level query
started. If you change the definition of above
function to return setof int8,you may see 2
rows returned.
Maybe you could add 'for update' clause to your
above query.
> session1<< ' language 'sql';
> session1>> CREATE
>
Regards.
Hiroshi Inoue
