Re: [HACKERS] GSSAPI on Solaris - psql segfault
Zdenek Kotala wrote: > Stefan Kaltenbrunner wrote: >> I just took a look at adding gssapi build support on solaris (solaris >> 10/x86_64, sun studio 10, 64bit build) which seemed easy enough by >> educating configure to look for -lgss but while it compiles just fine >> the resulting tree will not be able to complete a make check due to >> psql(!) segfaulting as soon as it tries to connect to the backend. >> the backtrace looks similiar to: >> >> >> program terminated by signal SEGV (no mapping at the fault address) >> 0x7fd3d401: _memcpy+0x00e1: movq %rax,(%rdi) >> Current function is pqPutMsgBytes >> 475 memcpy(conn->outBuffer + conn->outMsgEnd, buf, len); >> (dbx) where >> [1] _memcpy(0x4, 0x4709a0, 0x67, 0x2, 0x646c697562677000, >> 0x7361626174616400), at 0x7fd3d401 >> =>[2] pqPutMsgBytes(buf = 0x4709a0, len = 103U, conn = 0x4682f0), line >> 475 in "fe-misc.c" >> [3] pqPutnchar(s = 0x4709a0 "", len = 103U, conn = 0x4682f0), line 189 >> in "fe-misc.c" >> [4] pqPacketSend(conn = 0x4682f0, pack_type = '\0', buf = 0x4709a0, >> buf_len = 103U), line 2439 in "fe-connect.c" >> [5] PQconnectPoll(conn = 0x4682f0), line 1299 in "fe-connect.c" >> [6] connectDBComplete(conn = 0x4682f0), line 936 in "fe-connect.c" >> [7] PQsetdbLogin(pghost = (nil), pgport = (nil), pgoptions = (nil), >> pgtty = (nil), dbName = 0xfd7fffdffbeb "postgres", login = (nil), >> pwd = (nil)), line 660 in "fe-connect.c" >> [8] main(argc = 3, argv = 0xfd7fffdff9b8), line 211 in "startup.c" >> >> >> ideas ? > > Do you use also 64bit psql? Can you send me your ./configure setup? > However, I see there potential integer overflow, because len is size_t > and conn->outMsgEnd is defined as int. yeah this is a full 64bit build - but I just did several fresh buildfarm cycles and I'm unable to reproduce the issue again ... Stefan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] non-blocking CREATE INDEX in 8.2??
Jim C. Nasby wrote: > On Fri, Jul 13, 2007 at 01:45:18PM -0700, Joshua D. Drake wrote: >> Jim C. Nasby wrote: >>> According to http://developer.postgresql.org/index.php/Feature_Matrix, >>> 8.2 has non-blocking CREATE INDEX, which is news to me. Is it correct? >> CREATE INDEX CONCURRENTLY > > Well, I guess it's a good thing someone created that cheat-sheet, 'cause > I can't keep which feature went into what version straight anymore. :) glad that you like it :-) - but keep in mind that it clearly states that this is page is still very much an experiment. There is a lot of stuff still missing and it might as well contain a number of inaccuracies too - so take everything there with a grain of salt ... Stefan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] non-blocking CREATE INDEX in 8.2??
Jim C. Nasby a écrit : On Fri, Jul 13, 2007 at 01:45:18PM -0700, Joshua D. Drake wrote: Jim C. Nasby wrote: According to http://developer.postgresql.org/index.php/Feature_Matrix, 8.2 has non-blocking CREATE INDEX, which is news to me. Is it correct? CREATE INDEX CONCURRENTLY Well, I guess it's a good thing someone created that cheat-sheet, 'cause I can't keep which feature went into what version straight anymore. :) +1 I will try to add on my todo list to proofread and enhance this. Thanks for this page, Stefan. Regards. -- Guillaume. http://www.postgresqlfr.org http://docs.postgresqlfr.org ---(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
[HACKERS] plpgsql FOR loop doesn't guard against strange step values
I just noticed that when the BY option was added to plpgsql FOR loops, no real error checking was done. If you specify a zero step value, you'll have an infinite loop. If you specify a negative value, the loop variable will increment in the "wrong direction" until integer overflow occurs. Neither of these behaviors seem desirable in the least. Another problem is that no check for overflow is done when incrementing the loop variable, which means that an infinite loop is possible if the step value is larger than the distance from the loop upper bound to INT_MAX --- the loop variable could overflow before it is seen to be greater than the upper bound, and after wrapping around to negative it's still less than the upper bound, so the loop continues to run. I suggest throwing an error for zero or negative step value, and terminating the loop if the loop variable overflows. Any objections? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plpgsql FOR loop doesn't guard against strange step values
Tom Lane wrote: > I just noticed that when the BY option was added to plpgsql FOR > loops, no real error checking was done. If you specify a zero step > value, you'll have an infinite loop. If you specify a negative > value, the loop variable will increment in the "wrong direction" > until integer overflow occurs. Neither of these behaviors seem > desirable in the least. That seems to be fairly normal proramming language behavior. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] plpgsql and qualified variable names
I have just absorbed the significance of some code that has been in
plpgsql since day one, but has never been documented anyplace.
It seems that if you attach a "label" to a statement block in a
plpgsql function, you can do more with the label than just use it in
an EXIT statement (as I'd always supposed it was for). You can also use
the label to qualify the names of variables declared in that block.
For example, I've extended the example in section 37.3 like this:
CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 30
quantity := 50;
--
-- Create a subblock
--
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 80
RAISE NOTICE 'Outer quantity here is %', outerblock.quantity; --
Prints 50
END;
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 50
RETURN quantity;
END;
$$ LANGUAGE plpgsql;
Now the reason I'm interested in this is that it provides another
technique you can use to deal with conflicts between plpgsql variable
names and SQL table/column/function names: you can qualify the variable
name with the block label when you use it in a SQL command. This is
not in itself a solution to the conflict problem, because unqualified
names are still at risk of being resolved the "wrong" way, but it still
seems worth documenting in the new section I'm writing about variable
substitution rules.
Anyway, I'm not writing just to point out that we have a previously
undocumented feature. I notice that the section on porting from Oracle
PL/SQL mentions
You cannot use parameter names that are the same as columns that are
referenced in the function. Oracle allows you to do this if you qualify
the parameter name using function_name.parameter_name.
While i haven't tested yet, I believe that we could match this Oracle
behavior with about a one-line code change: the outermost namespace
level ("block") that the function parameter aliases are put into just
needs to be given a label equal to the function name, instead of being
label-less as it currently is.
Comments? Also, can anyone verify whether this labeling behavior
matches Oracle?
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] plpgsql FOR loop doesn't guard against strange step values
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> I just noticed that when the BY option was added to plpgsql FOR >> loops, no real error checking was done. If you specify a zero step >> value, you'll have an infinite loop. If you specify a negative >> value, the loop variable will increment in the "wrong direction" >> until integer overflow occurs. Neither of these behaviors seem >> desirable in the least. > That seems to be fairly normal proramming language behavior. Well, it's about what I'd expect from C or something at a similar level of (non) abstraction. But I dislike the idea that plpgsql should have behavior as machine-dependent as that the number of iterations will depend on the value of INT_MIN. Also, at the SQL level our usual policy is to throw errors for obvious programmer mistakes, and it's hard to argue that a zero or negative step isn't a programmer mistake. Had we defined the stepping behavior differently (ie, make "BY -1" work like REVERSE) then there would be some sanity in allowing negative steps, but I don't see the sanity in it given the implemented behavior. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] plpgsql and qualified variable names
Tom Lane wrote:
> Anyway, I'm not writing just to point out that we have a previously
> undocumented feature. I notice that the section on porting from Oracle
> PL/SQL mentions
>
> You cannot use parameter names that are the same as columns that are
> referenced in the function. Oracle allows you to do this if you qualify
> the parameter name using function_name.parameter_name.
>
> While i haven't tested yet, I believe that we could match this Oracle
> behavior with about a one-line code change: the outermost namespace
> level ("block") that the function parameter aliases are put into just
> needs to be given a label equal to the function name, instead of being
> label-less as it currently is.
If I'm understanding that correctly, Oracle would resolve the reference
to "ambiguous" in the function below to column in table foo, but allows
you to reference the parameter instead by specifying
"somefunc.ambiguous", while we always resolve it to the parameter.
CREATE TABLE foo (ambiguous integer);
CREATE FUNCTION somefunc(ambiguous integer) RETURNS integer AS $$
DECLARE
SELECT ambiguous FROM foo;
...
ISTM supporting "somefunc.ambiguous" just gives us another way to
reference the parameter, and there still isn't any way to refer the column.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] plpgsql FOR loop doesn't guard against strange step values
Tom Lane wrote: Peter Eisentraut <[EMAIL PROTECTED]> writes: Tom Lane wrote: I just noticed that when the BY option was added to plpgsql FOR loops, no real error checking was done. If you specify a zero step value, you'll have an infinite loop. If you specify a negative value, the loop variable will increment in the "wrong direction" until integer overflow occurs. Neither of these behaviors seem desirable in the least. That seems to be fairly normal proramming language behavior. Well, it's about what I'd expect from C or something at a similar level of (non) abstraction. But I dislike the idea that plpgsql should have behavior as machine-dependent as that the number of iterations will depend on the value of INT_MIN. Also, at the SQL level our usual policy is to throw errors for obvious programmer mistakes, and it's hard to argue that a zero or negative step isn't a programmer mistake. Had we defined the stepping behavior differently (ie, make "BY -1" work like REVERSE) then there would be some sanity in allowing negative steps, but I don't see the sanity in it given the implemented behavior. I suspect we have a significant incompatibility with PLSQL in this area. The docs give this example: FOR i IN REVERSE 10..1 LOOP -- some computations here END LOOP; In PLSQL, as I understand it, (and certainly in its ancestor Ada) this loop will execute 0 times, not 10. To iterate from 10 down to 1 one would need to say: FOR i IN REVERSE 1..10 LOOP -- some computations here END LOOP; I'm not sure if this has been noticed before. It's actually quite unfortunate. At least it should be mentioned in the section of the docs relating to porting from PLSQL. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plpgsql and qualified variable names
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > ISTM supporting "somefunc.ambiguous" just gives us another way to > reference the parameter, and there still isn't any way to refer the column. Sure. All this will do is let us remove a noted incompatibility with Oracle, which seems worth doing if it's a one-line change that doesn't break anything. Further down the road, we could imagine some option in plpgsql that prevents substitution of variables *unless* they are qualified with the appropriate block name --- in which case we'd better make sure there is a way to qualify function parameter names. So this might be a necessary component of a solution that tightens up the substitution behavior, but it's not the solution by itself. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plpgsql and qualified variable names
ISTM supporting "somefunc.ambiguous" just gives us another way to reference the parameter, and there still isn't any way to refer the column. Could we not, at least, support explicit column disambiguation? e.g. This PL/SQL procedure: CREATE OR REPLACE PROCEDURE insert_emp (empno emp.empno%TYPE, ename emp.ename%TYPE) IS BEGIN INSERT INTO emp (empno, ename) VALUES (empno, ename); END; is tantamount to writing this: CREATE OR REPLACE PROCEDURE insert_emp (empno emp.empno%TYPE, ename emp.ename%TYPE) IS BEGIN INSERT INTO emp (emp.empno, emp.ename) VALUES (insert_emp.empno, insert_emp.ename); END; Both are valid, and notice how the latter evinces disambiguation supported both ways. -- Affan Salman EnterpriseDB Corporation http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plpgsql and qualified variable names
"Affan Salman" <[EMAIL PROTECTED]> writes: > Could we not, at least, support explicit column disambiguation? The problem is that there are places in the SQL grammar where we don't allow qualification of a SQL name --- INSERT column lists, UPDATE SET targets, and SELECT AS labels are three I can think of offhand. Without fixing that it's a bit tough, and in at least the UPDATE case there are severe ambiguity problems if we try to allow a noise qualification. In at least those three cases, we know that it's not sensible to substitute a parameter. If that's true in all the problem cases, which seems likely, then we could do something with Greg's idea of using the raw parse tree from the main SQL parser to guide decisions about where parameters may be substituted. I complained earlier about the loss of a printable representation of the substituted query, but we'd not necessarily have to give that up. Seeing that ColumnRef carries a pointer back into the source text, we could use the ColumnRefs to drive a textual substitution and not have to change that aspect of the API. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plpgsql FOR loop doesn't guard against strange step values
Andrew Dunstan <[EMAIL PROTECTED]> writes: > I suspect we have a significant incompatibility with PLSQL in this area. Ugh. Google seems to confirm your thought that Oracle expects > FOR i IN REVERSE 1..10 LOOP which is not the way we are doing it. Not sure if it's worth trying to fix this --- the conversion pain would be significant. I agree we gotta document it, however; will go do so. Note that in the Oracle worldview it still wouldn't be sensible to use a negative step. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plpgsql FOR loop doesn't guard against strange step values
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: I suspect we have a significant incompatibility with PLSQL in this area. Ugh. Google seems to confirm your thought that Oracle expects FOR i IN REVERSE 1..10 LOOP which is not the way we are doing it. Not sure if it's worth trying to fix this --- the conversion pain would be significant. I agree we gotta document it, however; will go do so. Note that in the Oracle worldview it still wouldn't be sensible to use a negative step. Quite so. I think we should probably require the step to be greater than 0, whether or not we are using REVERSE, and choose to use it as an increment or decrement as appropriate. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] has anyone looked at burstsort ?
On Fri, Jul 13, 2007 at 03:29:16PM +0100, Gregory Stark wrote: > The key to the algorithm is that it uses a trie to bin rows with common > leading prefixes together. This avoids performing redundant comparisons > between those columns later. Sounds like a variation on the idea suggested before, which is to allow each datatype to provide an xfrm function that returns a signed integer, which would allow you to compare values without invoking the actual datatype comparison function in most cases. That approach would work on any datatype, not just strings. Whether it's more efficient than the current method is another question entirely. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[HACKERS] Warning for exceeding max locks?
Hello, We ran into a problem with a customer this weekend. They had >128,000 tables and we were trying to run a pg_dump. When we reached max_locks_per_transaction, the dump just hung waiting to lock the next table. Would it make sense to have some sort of timeout for that? wait_for_lock_timeout? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Warning for exceeding max locks?
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > We ran into a problem with a customer this weekend. They had >128,000 > tables and we were trying to run a pg_dump. When we reached > max_locks_per_transaction, the dump just hung waiting to lock the next > table. > Would it make sense to have some sort of timeout for that? I don't think you have diagnosed this correctly. Running out of lock table slots generates an "out of shared memory" error, with a HINT that you might want to increase max_locks_per_transaction. If you can prove otherwise, please supply a test case. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] pg_dump ignore tablespaces
Recently I ran into an issue where restoring from pg_dump from one machine to another with non-matching tablespaces. The primary issue is that index creation will fail if the tablespace does not exist from the dump. I was thinking to best solution for this would be a pg_dump option such as --ignore-tablespaces which would not dump any tablespace related data. This would benefit restoring a dump from one machine to another where there are different disk or tablespace topologies. If such a patch were presented and found to be acceptable code wise, would it be a generally useful enough option to be included? Gavin
Re: [HACKERS] pg_dump ignore tablespaces
"Gavin M. Roy" <[EMAIL PROTECTED]> writes: > Recently I ran into an issue where restoring from pg_dump from one machine > to another with non-matching tablespaces. The primary issue is that index > creation will fail if the tablespace does not exist from the dump. I was > thinking to best solution for this would be a pg_dump option such as > --ignore-tablespaces which would not dump any tablespace related data. We have --no-owner, so --no-tablespace doesn't seem out of line. It's a bit late for 8.3 but no objection to adding such a feature for 8.4. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plpgsql and qualified variable names
Anyway, I'm not writing just to point out that we have a previously undocumented feature. I notice that the section on porting from Oracle PL/SQL mentions You cannot use parameter names that are the same as columns that are referenced in the function. Oracle allows you to do this if you qualify the parameter name using function_name.parameter_name. it's not supported yet? postgres=# create or replace function foox(a integer) returns integer as $$ begin return foox.a; end $$ language plpgsql; CREATE FUNCTION ostgres=# select foox(10); ERROR: missing FROM-clause entry for table "foox" LINE 1: SELECT foox.a ^ QUERY: SELECT foox.a CONTEXT: PL/pgSQL function "foox" line 1 at return I am sure, It's good idea - and I thing SQL/PSM specifies it too. Regards Pavel Stehule ---(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: [HACKERS] plpgsql and qualified variable names
In at least those three cases, we know that it's not sensible to substitute a parameter. If that's true in all the problem cases, which seems likely, then we could do something with Greg's idea of using the raw parse tree from the main SQL parser to guide decisions about where parameters may be substituted. I complained earlier about the loss of a printable representation of the substituted query, but we'd not necessarily have to give that up. Seeing that ColumnRef carries a pointer back into the source text, we could use the ColumnRefs to drive a textual substitution and not have to change that aspect of the API. Variables substitution is probable them most big hack on plpgsql. I am not sure, so this is well solution. We can generate more helpful hint and that is all. Regards Pavel Stehule ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
