Re: [SQL] Is it normal that functions are so much faster than inline queries
On Wed, 31 Mar 2004 10:33:20 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: "Olivier Hubaut" <[EMAIL PROTECTED]> writes: When I want to execute this set of queries in a function: ... It takes only 2 seconds. But when I tried to do it directly in the psql term (replacing the $1 value with the same used in the function call), I'm obliged to kill the second query after 10 minutes because it's still runnning! You're presumably getting different plans in the two cases. Usually we hear complaints about the function case being slower, because the planner has less information when it has to work with a parameter instead of a constant. In this case it seems the stupider plan is being chosen with a constant :-(. You have not shown enough information to tell why, but I'm wondering about datatype mismatch preventing an index from being used. What is the declared datatype of the $1 parameter, and does it match what will be assumed for the unadorned constant? regards, tom lane Thank you for your response I'll try to give enough information this time - the columns 'batch' used in the join is a char(50) in the two table - the columns 'id' and 'new_value' also used in the join are both char(64) - the argument passed to the function is a string The first table (oly.amaze_log_database_object) have more or less 40,000 rows that are corresponding to the first part of the 'where' clause (batch=$1) on a total amount of 41,000 The second one (oly.amaze_log_object) have more or less 20,000 rows on a total amount of 21,000 that should match with the join condition. They are no index and I tried to put some on the couples (batch, id) and (batch, new_value) and/or the (batch) columns, without more success. Hope that's enough. For the moment, we planned to upgrade to Pg 7.4, hoping this will resolve the problem... Regards, Olivier Hubaut -- Downloading signature ... 99% *CRC FAILED* signature aborted ---(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
[SQL] Array_append does not work with Array variables in PL/pgSQL?
Folks, See if you can spot any mistake I'm making here. I've declared the following array variables in a plpgsql function: v_vals TEXT[]; n_vals TEXT[]; After some manipulation, I try to synch them: n_vals := array_append(n_vals, v_vals[arrloop]); val_result := v_vals[arrloop]; RAISE NOTICE ''orig value %'', val_result; val_result := array_to_string(n_vals, '', ''); RAISE NOTICE ''derived value %'', val_result; And I get: NOTICE: orig value 04/01/2004 NOTICE: derived value NOTICE: orig value 04/01/2004 NOTICE: derived value It seems like I cannot assign new elements to arrays inside a PL/pgsql function. What gives here? PostgreSQL 7.4.1 on Linux. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Array_append does not work with Array variables in PL/pgSQL?
Josh Berkus wrote:
v_vals TEXT[];
n_vals TEXT[];
try:
v_vals TEXT[] := ''{}'';
n_vals TEXT[] := ''{}'';
You have to initialize the array to something non-null, even if that be
an empty array (note that there is a difference). When trying to append
an element to a NULL valued array, you wind up with a NULL result. It is
similar to:
regression=# select (NULL || 'abc') is null;
?column?
--
t
(1 row)
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Array_append does not work with Array variables in PL/pgSQL?
Josh Berkus wrote: BTW, did you get my e-mail to Hackers about ARRAY[] IS NULL? I saw it, but I've been too swamped to really read it. I'll try to carve out some time this afternoon. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Array_append does not work with Array variables in PL/pgSQL?
Joe, > You have to initialize the array to something non-null, even if that be > an empty array (note that there is a difference). When trying to append > an element to a NULL valued array, you wind up with a NULL result. It is > similar to: Aha! I knew that I was missing something fundamental. BTW, did you get my e-mail to Hackers about ARRAY[] IS NULL? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Array_append does not work with Array variables in PL/pgSQL?
Joe, > I saw it, but I've been too swamped to really read it. I'll try to carve > out some time this afternoon. No urgency on my part. More something to fix for 7.5 -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] [pgsql-advocacy] SQL Spec Compliance Questions
Josh Berkus wrote: 6) SQL-99 Distinct Types 7) SQL-99 Structured Types 9) SQL-99 Collection Types 10) SQL-99 Typed tables and views My answers: 6), 7) Not sure what these are. Here's the section in SQL99: 4.8 User-defined types A user-defined type is a schema object, identified by a . The definition of a user-defined type specifies a number of components, including in particular a list of attribute definitions. Although the attribute definitions are said to define the representation of the userdefined type, in fact they implicitly define certain functions (observers and mutators) that are part of the interface of the user-defined type; physical representations of user-defined type values are implementation-dependent. The representation of a user-defined type is expressed either as a single data type (some predefined data type, called the source type), in which case the user-defined type is said to be a distinct type, or as a list of attribute definitions, in which case it is said to be a structured type. So if I read that correctly, they are user defined types, that are either scalar (distinct) or composite (structured) -- so I'd say yes. 9) ??? From SQL99: 4.11 Collection types A collection is a composite value comprising zero or more elements each a value of some data type DT. If the elements of some collection C are values of DT, then C is said to be a collection of DT. The number of elements in C is the cardinality of C. The term ââelementââ is not further defined in this part of ISO/IEC 9075. The term ââcollectionââ is generic, encompassing various types (of collection) in connection with each of which, individually, this part of ISO/IEC 9075 defines primitive type constructors and operators. This part of ISO/IEC 9075 supports one collection type, arrays. We are not yet fully compliant with SQL99 arrays, but not too far off either, I think. We have some extensions to SQL99 behavior, that would require breaking backward compatibility in order to do away with them. For example, SQL99 arrays *always* start with a lower bound of 1, if I read the spec correctly. Also multidimensional arrays in SQL99 are "arrays of arrays", which is not quite the same as our multidimensional arrays. 10) Also not sure SQL99: 4.16.2 Referenceable tables, subtables, and supertables A table BT whose row type is derived from a structured type ST is called a typed table. Only a base table or a view can be a typed table. A typed table has columns corresponding, in name and declared type, to every attribute of ST and one other column REFC that is the self-referencing column of BT; let REFCN be the of REFC. The declared type of REFC is necessarily REF(ST) and the nullability characteristic of REFC is known not nullable. If BT is a base table, then the table constraint ââUNIQUE(REFCN)ââ is implicit in the definition of BT. A typed table is called a referenceable table. A self-referencing column cannot be updated. Its value is determined during the insertion of a row into the referenceable table. The value of a system-generated selfreferencing column and a derived self-referencing column is automatically generated when the row is inserted into the referenceable table. The value of a user-generated self-referencing column is supplied as part of the candidate row to be inserted into the referenceable table. I really don't quite understand this, but I don't think we have it ;-) HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
