Re: [HACKERS] Notices for redundant operations
Alvaro Herrera writes: > If people doesn't receive any message regarding the command they > executed, they will execute it again, and again, and they will > eventually wonder what's wrong and start investigating why "nothing is > happening". That is not the case here. The commands still generate the normal response, e.g., "ALTER TABLE", if they succeed. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [PATCHES] Warning for missing createlang
Peter Eisentraut wrote: Tom Lane writes: There are good security arguments not to have it in the default install, no? I think last time the only reason we saw was that dump restoring would be difficult. I don't see any security reasons. That could be overcome by doing a 'drop language' before running your restore, couldn't it? Maybe it would also be useful for such cases to have a switches on initdb and pg_dump to inhibit creation of the language. I did see a reference in the archives to a problem with heavy recursion as a possible security hole. I guess my answer to that would be that if you are worried about it you should drop the language, but I don't see this alone as a reason not to install it by default. After all, you don't need plpgsql to bring the system to its knees :-) But maybe there's some other reason my search didn't find. cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Notices for redundant operations
"Tom Lane" <[EMAIL PROTECTED]> wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > I found a few notices and warnings that inform you that the command you > > are executing has no effect because the object is already in the state you > > want it. I think these are useless, and there is also some inconsistency. > > Does someone want to defend keeping them? > > Do you think the commands should just silently do nothing? That seems > strange to me. I like giving a NOTICE (not WARNING though, those guys > should be changed). Of course not but I personally prefer have back a normal response. Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Notices for redundant operations
On Saturday 06 September 2003 07:25, Peter Eisentraut wrote:
> Alvaro Herrera writes:
> > If people doesn't receive any message regarding the command they
> > executed, they will execute it again, and again, and they will
> > eventually wonder what's wrong and start investigating why "nothing is
> > happening".
>
> That is not the case here. The commands still generate the normal
> response, e.g., "ALTER TABLE", if they succeed.
Hmm... the counter state seems to be that now these commands would tell you
they are doing something even though they are arn't really doing anything:
=> alter table test set without oids;
ALTER TABLE
=> alter table test set without oids;
ALTER TABLE
=> alter table test set without oids;
ALTER TABLE
I think popping a notice that the table is already without oids could be
helpful. for sure these types of messages should not be warnings though.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Warning for missing createlang
Andrew Dunstan <[EMAIL PROTECTED]> writes: > I did see a reference in the archives to a problem with heavy recursion > as a possible security hole. I guess my answer to that would be that if > you are worried about it you should drop the language, but I don't see > this alone as a reason not to install it by default. After all, you > don't need plpgsql to bring the system to its knees :-) Yeah, now that we allow recursion in SQL functions, you don't need a PL language to overflow the stack. So that particular argument is seeming a bit weak. Were there any other security arguments against making plpgsql standard? Inability to load existing pg_dump archives might be a bigger objection. However, we could fix that if pg_restore were modified to not stop dead in its tracks upon encountering an error. IMHO that was a wrong choice from the beginning ... pg_dump scripts don't act that way, and pg_restore should not either. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] checkpoints too frequent
> "BM" == Bruce Momjian <[EMAIL PROTECTED]> writes: BM> Vivek, you reported recently that increasing sort_mem and BM> checkpoint_segments increased performance. Can you run a test to see BM> how much of that improvement was just because of increasing BM> checkpoint_segments? i was thinking just the same thing myself. i'll start that run now. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] plpython
The key value of having both SD vs. GB is scope. We *do* want to be able to have dictionaries with scope that is function specific, statement specific and global (available to all functions). I do use plpython primarily for running aggregates. Having the different scopes (if they all worked correctly) would enable 1) multiple calls to the same function within a statement to use the same SD["whatever"] to store data cleanly and clearly without overwriting the other instance's values. 2) Allow any functions within the same statement access to a statement dictionary. 3) Global allows any function instance in any statement access to the same values. You can change the name if you must, but I don't see the point. I don't understand why you would want to move plpy out. If you are reimplementing, why don't you do it under the plpy wrappers? Am I missing something. Taking out the functionality to execute functions and calling notice and replacing them with other named calls also seems pointless to me. One of two primary features of good pl languages *is* the ability to run queries and interface with the database. I can certainly understand the separation with regards to implementation of the language, however I cannot see that it is appropriate for the interface level. The discussions with regards to scoping was not necessarily on list. Most of it was in person. I suggest contacting Joe and Tom directly or via this discussion. I strongly suspect Jan knows about this as well as I believe tcl supports this too. Exactly how will your type conversions change what people program? Your description and explanation are not clear. Remember it is the interface that must remain stable. Be sure that you are do not eliminate capabilities that are common in all/most of the languages. Pl/R is a relatively new language implemented by Joe Conway. It would be really good to talk over some of the design decisions with him. Jan, et. al, have done a particularly thorough job with pltcl. He also should be a key person with whom to discuss major interfaces changes. Make sure your vision of plpython matches the basic framework of other procedural languages in postgres. [EMAIL PROTECTED] = [EMAIL PROTECTED] www.varlena.com PostgreSQL Consulting & Support PostgreSQL General Bits http://www.varlena.com/GeneralBits/ = "Sometimes we are confronted with more data than we can really use, and it may be wisest to forget and to destroy most of it" -- Donald Knuth, The Art of Computer Programming On Thu, Sep 04, 2003 at 11:41:14PM -0700, James Pye wrote: > Greetings, > > Thanks for your e-mail, I really do appreciate the feedback. :) > > First of all, I was planning on calling it plpy(should have said something in > the e-mail), as to not necessarily show backward compatibility with plpython, but I > can understand the annoyance that it would be for whatever users that wanted to take > advantage of whatever improvements that I am able to generate. > Perhaps it would be wise to provide "legacy" support as a compilation option > for some period of time, if I actually move away from some/most of plpython's > supplied features. It should be fairly easy to do.. > > > NO!!! Don't remove SD and GD!!! They are useful. > > I use them in several applications, primarily > > for running aggregates. > > Perhaps I will keep SD, but I will talk to some more users. SD could easily be > emulated through the use of globals, but dealing with initialization inside every > procedure would be a hassle, so I probably will keep it. > I disagree with you about GD, as I said in my e-mail you can easily, and more > naturally(IMO, as it is part of Python itself), use the Python "global" keyword. As > far as it being unsafe(I think the docs declare that somewhere, or that GD is safe), > I don't understand what makes it safer than `global myglobal`, other than the fact > that it doesn't deal with the real globals dictionary. > Although, I suppose it may be nice to have the extra clarification that the > coder is wanting to access global data by explicitly specifying GD everywhere it is > accessed, but does that really justify its creation(not a serious issue, but why > have it)? > > > > What needs to be fixed is that the SD needs to be > > initialized at the start of each statement. > > Joe Conway just implemented this in Pl/R and > > Tom Lane had an idea about it too. > > Very good point. I will look into this more. Do you remember what the subject > of the hacker's thread was that discussed this? Was it recent? I'll search the > archives a bit and see if I can find it... > Although, it seems to me that de
[HACKERS] sequences and pg_type
Hello, I have a table with a serial type in it as a record id. The type of this object comes back as int4 when I query via pg_type. How can I distinguish this counter type from just a plain int4? Peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Planning to force reindex of hash indexes
Tom Lane <[EMAIL PROTECTED]> writes: > "Mendola Gaetano" <[EMAIL PROTECTED]> writes: >> "Tom Lane" <[EMAIL PROTECTED]> wrote: >>> I've found a number of infelicities in the hash index code that >>> can't be fixed without an on-disk format change. > >> How can we avoid this kind of mess for the future ? > > Build a time machine, go back fifteen years, wave a magic wand to > increase the IQ levels of the Berkeley grad students? Sometimes we > just have to change bad decisions, that's all. > > regards, tom lane Actually, I think that your time would be better spent on the time machine. After all, with working time travel you wouldn't have to worry about optimizing PostgreSQL. Queries could take as long as they needed to take and then you could send the response back in time to right before the query was issued. It's too bad you don't have the time machine finished already. You could use the time machine to go back in time and work on the time machine :). You'd be finished in no time. Jason ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Needed function IF(expr, expr, expr)
If the function is defined with ANY* and you defer typing the arguments until the first reference then I think you will get what you want with the CASE statement. If the function is called if( x>y, x+1, y), the first reference is in the argument list and so should be typed there. But if you pass constants or non-expressions, then delaying the typing would enable a better fit when using ANY*. Letting the first usage define the type would leverage the existing expression handling work as is without special cases. It would also avoid implicit casts of unexpected argument types. If the operation in the function is dependent on the arguments being, say some kind of numeric or a class of types we ought to raise an error if they are incompatible. Of course this would ONLY be for ANY arguments. A function declared with a non-generic type needs to have arguments of the declared types. [EMAIL PROTECTED] On Sat, Sep 06, 2003 at 12:47:07PM -0400, Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > Marek Lewczuk writes: > >> Currently I have big problem with function IF(), below the description > >> of this function from MySQL manual. > > > You cannot implement this kind of function, unless you want to create one > > version for each data type combination. > > As of 7.4, one can avoid the data type problem with a polymorphic > function: > > regression=# create function if (bool,anyelement,anyelement) returns anyelement > regression-# as 'select case when $1 then $2 else $3 end' language sql; > CREATE FUNCTION > > However, there are some limitations: > > regression=# select if(true, 33, 44); > if > > 33 > (1 row) > > regression=# select if(true, 33, 44.4); > ERROR: function if(boolean, integer, numeric) does not exist > HINT: No function matches the given name and argument types. You may need to add > explicit typecasts. > > regression=# select if(true, 'a','b'); > ERROR: could not determine ANYARRAY/ANYELEMENT type because input is UNKNOWN > > You can hack around these problems by adding explicit casts: > > regression=# select if(true, 'a'::text,'b'); > if > > a > (1 row) > > but I wonder whether we shouldn't allow all-UNKNOWN inputs to be > resolved as TEXT in this situation, as we do when working directly with > CASE. > > BTW, I started out this email intending to point out that a function > cannot replace CASE in general because the function will insist on > evaluating all its arguments, which is a behavior you do not want for > CASE, and I'd imagine not for MySQL's IF() either. (But I dunno, maybe > their IF() does evaluate the "unused" argument. Anyone know?) > > However, as of 7.4, that problem is gone too. If you write the function > just as above (language sql, volatile, not strict) then the planner will > inline it and indeed what you get is a CASE. Watch this: > > regression=# explain select * from tenk1 where if(ten QUERY PLAN > -- > Seq Scan on tenk1 (cost=0.00..508.00 rows=50 width=244) >Filter: (CASE WHEN (ten < hundred) THEN unique1 ELSE unique2 END = 44) > (2 rows) > > So we do actually have a sort-of-credible way to make a user-defined > function that emulates IF(). I think we might be able to do Oracle's > DECODE() as well, though I don't know its exact definition. (You'd > still need to make several of 'em to handle differing numbers of > arguments, but that seems well within the bounds of feasibility.) > > Any comments on the UNKNOWN issue? It's not too late to change that for > 7.4, if we have consensus that we should. > > regards, tom lane > > ---(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 7: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Needed function IF(expr, expr, expr)
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Marek Lewczuk writes: >> Currently I have big problem with function IF(), below the description >> of this function from MySQL manual. > You cannot implement this kind of function, unless you want to create one > version for each data type combination. As of 7.4, one can avoid the data type problem with a polymorphic function: regression=# create function if (bool,anyelement,anyelement) returns anyelement regression-# as 'select case when $1 then $2 else $3 end' language sql; CREATE FUNCTION However, there are some limitations: regression=# select if(true, 33, 44); if 33 (1 row) regression=# select if(true, 33, 44.4); ERROR: function if(boolean, integer, numeric) does not exist HINT: No function matches the given name and argument types. You may need to add explicit typecasts. regression=# select if(true, 'a','b'); ERROR: could not determine ANYARRAY/ANYELEMENT type because input is UNKNOWN You can hack around these problems by adding explicit casts: regression=# select if(true, 'a'::text,'b'); if a (1 row) but I wonder whether we shouldn't allow all-UNKNOWN inputs to be resolved as TEXT in this situation, as we do when working directly with CASE. BTW, I started out this email intending to point out that a function cannot replace CASE in general because the function will insist on evaluating all its arguments, which is a behavior you do not want for CASE, and I'd imagine not for MySQL's IF() either. (But I dunno, maybe their IF() does evaluate the "unused" argument. Anyone know?) However, as of 7.4, that problem is gone too. If you write the function just as above (language sql, volatile, not strict) then the planner will inline it and indeed what you get is a CASE. Watch this: regression=# explain select * from tenk1 where if(ten
Re: [HACKERS] sequences and pg_type
pw <[EMAIL PROTECTED]> writes: > I have a table with a serial type in it as a record id. > The type of this object comes back as int4 when I query via > pg_type. > How can I distinguish this counter type from just a plain int4? Well, you can't, because serial isn't actually a type in Postgres. As the manual tries to explain, serial is just a shorthand for creating an integer column whose default is a nextval() call on a sequence. So the way to tell is to look for a default expression of that kind. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] sequences and pg_type
I think I found a solution. Hopefully the system tables don't change too much in the future.I just used pg_attrdef to tell me which columns *not* to use. I hope that's right. It seems to work. Peter On Sat, 2003-09-06 at 18:19, Tom Lane wrote: > pw <[EMAIL PROTECTED]> writes: > > I have a table with a serial type in it as a record id. > > The type of this object comes back as int4 when I query via > > pg_type. > > How can I distinguish this counter type from just a plain int4? > > Well, you can't, because serial isn't actually a type in Postgres. > As the manual tries to explain, serial is just a shorthand for creating > an integer column whose default is a nextval() call on a sequence. > So the way to tell is to look for a default expression of that kind. > > regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [PATCHES] Warning for missing createlang
Andrew Dunstan kirjutas L, 06.09.2003 kell 16:14: > Peter Eisentraut wrote: > > >Tom Lane writes: > > > > > > > >>There are good security arguments not to have it in the default install, > >>no? > >> > >> > > > >I think last time the only reason we saw was that dump restoring would be > >difficult. I don't see any security reasons. > > > > That could be overcome by doing a 'drop language' before running your > restore, couldn't it? or to have CREATE OR REPLACE LANGUAGE (like we have for FUNCTIONS). --- Hannu ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
