[BUGS] BUG #1722: table with a serial field don't works
The following bug has been logged online: Bug reference: 1722 Logged by: Massimo Mollo Email address: [EMAIL PROTECTED] PostgreSQL version: 8.03 Operating system: win2000pro, winXphome Description:table with a serial field don't works Details: I've installed postgres on 2 pcs, a win2000 and a winxp home. when i create a table with a SERIAL field, the pgadmin can see and use it, but with psql or a .net program using odbc, ole or lpgsql, the error is "RELATION *tablename* NOT FOUND". i tryed to use psql and the .net program on the same pc, or a lan-connected pc. If i change the type of the field originally SERIAL, the error vanish. i searched on forums, but none knows that problems. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] BUG #1722: table with a serial field don't works
Massimo Mollo wrote: I've installed postgres on 2 pcs, a win2000 and a winxp home. when i create a table with a SERIAL field, the pgadmin can see and use it, but with psql or a .net program using odbc, ole or lpgsql, the error is "RELATION *tablename* NOT FOUND". Please supply the real query and real error message. What is the real tablename? Is it in your search_path? Does it contain spaces or mixed case, and if so is it quoted? If you don't know why you would want to quote it, check the manuals (chapter "Identifiers and Keywords") and list archives. i tryed to use psql and the .net program on the same pc, or a lan-connected pc. If i change the type of the field originally SERIAL, the error vanish. Is the problem with the table not being found or the type of the column? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1723: array_cat() bug when passed empty array
The following bug has been logged online:
Bug reference: 1723
Logged by: Dave Chapeskie
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.4.6
Operating system: FreeBSD 5.x
Description:array_cat() bug when passed empty array
Details:
I believe this bug still exists in the latest 8.0.x but
have not tested that version, just looked at the code.
array_cat() has a bug when passed an empty array. The
code attempts to optimise/short-circuit this case and
returns a pointer to the non-empty argument. This is
bad/wrong. Especially when used in a construct like:
foo := foo ||
since after array_cat() returns exec_assign_value()
will pfree() 'foo' and then attempt to assign the now
invalid result that points to 'foo'.
Turning on assertion checks (which turns on memory
checks) catches this. Here is a simple example:
create or replace function array_test (text[],text[])
returns text[] as '
declare
tmp text[];
begin
tmp := $1;
tmp := tmp || $2;
return tmp;
end'
language plpgsql
stable;
select array_test(array['foo','bar'],'{x}');
select array_test(array['foo','bar'],'{}');
The first call to array_test() returns
{foo,bar,x}
as expected. With debuging on the second call returns:
ERROR: out of memory
DETAIL: Failed on request of size 1065320319.
CONTEXT: PL/pgSQL function "array_test" while casting return value to
function's return type
Note 1065320319 = 0x3F7F7F7F and with debugging pfree'd
memory is filled with 0x74 bytes. The top 0x400
got cleared because the top bits of the length field
are flag bits. I've also seen the error say
"Failed on request of size 2139062147.".
---(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: [BUGS] BUG #1721: mutiple bytes character string comaprison error
Tom Lane wrote: > Kris Jurka <[EMAIL PROTECTED]> writes: > > On Sun, 19 Jun 2005, Tom Lane wrote: > >> Sorry, but UTF-8 encoding doesn't work properly on Windows (yet). > >> Use some other database encoding. > > > Shouldn't we forbid its creation then? > > There was serious discussion of that before the 8.0 release, but > we decided not to forbid it. Check the archives; I don't recall > the reasoning at the moment. UTF8 encoding works with the C locale assuming you don't care about ordering of the character set, e.g. Japanese. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1721: mutiple bytes character string comaprison error
> > UTF8 encoding works with the C locale assuming you don't care > about ordering of the character set, e.g. Japanese. > Has anyone with the ability to compile postgresql on windows tested the ICU patch? ... John ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1723: array_cat() bug when passed empty array
I wrote:
> Actually, I would say the bug is exec_assign_value's. There is nothing
> at all wrong with a function returning one of its input values; for
> example the smaller/larger functions all do that.
For that matter, you don't need a function at all:
regression=# create or replace function copyit(text) returns text as $$
regression$# declare tmp text;
regression$# begin
regression$# tmp := $1;
regression$# tmp := tmp;
regression$# return tmp;
regression$# end$$ language plpgsql stable;
CREATE FUNCTION
regression=# select copyit('foo');
ERROR: out of memory
DETAIL: Failed on request of size 1065320319.
CONTEXT: PL/pgSQL function "copyit" line 4 at assignment
regression=#
This makes it perfectly clear that the problem is that exec_assign_value
must copy the given value before it frees the old, just in case they're
the same. (Hmm, I wonder if we can shortcircuit the whole thing ...)
regards, tom lane
---(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: [BUGS] BUG #1723: array_cat() bug when passed empty array
"Dave Chapeskie" <[EMAIL PROTECTED]> writes:
> array_cat() has a bug when passed an empty array. The
> code attempts to optimise/short-circuit this case and
> returns a pointer to the non-empty argument. This is
> bad/wrong. Especially when used in a construct like:
> foo := foo ||
> since after array_cat() returns exec_assign_value()
> will pfree() 'foo' and then attempt to assign the now
> invalid result that points to 'foo'.
Actually, I would say the bug is exec_assign_value's. There is nothing
at all wrong with a function returning one of its input values; for
example the smaller/larger functions all do that. Let's see...
regression=# create or replace function smal(text,text) returns text as $$
regression$# declare tmp text;
regression$# begin
regression$# tmp := $1;
regression$# tmp := text_smaller(tmp, $2);
regression$# return tmp;
regression$# end$$ language plpgsql stable;
CREATE FUNCTION
regression=# select smal('abc', '123');
smal
--
123
(1 row)
regression=# select smal('123', 'abc');
ERROR: out of memory
DETAIL: Failed on request of size 1065320319.
CONTEXT: PL/pgSQL function "smal" line 4 at assignment
regression=#
It's very surprising no one noticed this before. Thanks for the report!
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1721: mutiple bytes character string comaprison error
> > UTF8 encoding works with the C locale assuming you don't care about > > ordering of the character set, e.g. Japanese. > > > > Has anyone with the ability to compile postgresql on windows > tested the ICU patch? Yes. See http://archives.postgresql.org/pgsql-hackers/2005-05/msg00662.php //Magnus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] Out-of-Memory with ROWTYPE assignment in PL/pgSQL FOR loop
Hello, The PL/pgSQL FOR loop in the function consume_memory() defined below will consume VM on each iteration until the process hits its ulimit. The problem occurs with variables of ROWTYPE; there is no unbounded allocation when using simple types such as integer or varchar. Before I delve into the SPI code, perhaps someone familiar with PostgreSQL internals can spot the resource leak. Tested with 8.0.1 and CVS head as of 2005-06-20: -- Start of test code -- create a table with ten million rows CREATE TEMPORARY TABLE ten ( n integer DEFAULT 0 ) ; INSERT INTO ten VALUES (0); INSERT INTO ten VALUES (1); INSERT INTO ten VALUES (2); INSERT INTO ten VALUES (3); INSERT INTO ten VALUES (4); INSERT INTO ten VALUES (5); INSERT INTO ten VALUES (6); INSERT INTO ten VALUES (7); INSERT INTO ten VALUES (8); INSERT INTO ten VALUES (9); CREATE TEMPORARY TABLE thousand AS SELECT 100*i.n + 10*j.n + k.n AS n FROM ten AS i, ten as j, ten as k ; CREATE TEMPORARY TABLE tenmillion AS SELECT 1*i.n + 10*j.n + k.n AS n FROM thousand AS i, thousand as j, ten as k ; -- a function to consume memory CREATE OR REPLACE FUNCTION consume_memory() RETURNS void AS $PROC$ DECLARE rec tenmillion%ROWTYPE ; prev tenmillion%ROWTYPE ; BEGIN FOR rec IN SELECT * FROM tenmillion LOOP prev := rec ; END LOOP ; RETURN ; END $PROC$ LANGUAGE plpgsql; -- Until this point, memory usage is approximately constant. -- Evaluating the above function will rapidly consume VM. SELECT consume_memory() ; -- End of test code Here's a record of Committed_AS from /proc/meminfo on a Linux 2.6 system, over the course of the test: [EMAIL PROTECTED]: while : ; do grep Committed_AS /proc/meminfo ; sleep 1 ; done Committed_AS: 225592 kB Committed_AS: 225592 kB Committed_AS: 233692 kB <- Started Committed_AS: 258280 kB Committed_AS: 282868 kB Committed_AS: 299260 kB Committed_AS: 323848 kB Committed_AS: 340232 kB Committed_AS: 348436 kB Committed_AS: 356632 kB Committed_AS: 381220 kB Committed_AS: 397612 kB Committed_AS: 414004 kB Committed_AS: 422200 kB Committed_AS: 438592 kB Committed_AS: 463180 kB Committed_AS: 487768 kB Committed_AS: 504160 kB Committed_AS: 504160 kB Committed_AS: 520552 kB Committed_AS: 545140 kB Committed_AS: 569728 kB Committed_AS: 586120 kB Committed_AS: 586120 kB Committed_AS: 602512 kB Committed_AS: 225640 kB <- Cancelled Regards, Bill Rugolsky ---(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: [BUGS] BUG #1721: mutiple bytes character string comaprison
> Tom Lane wrote: > > Kris Jurka <[EMAIL PROTECTED]> writes: > > > On Sun, 19 Jun 2005, Tom Lane wrote: > > >> Sorry, but UTF-8 encoding doesn't work properly on Windows (yet). > > >> Use some other database encoding. > > > > > Shouldn't we forbid its creation then? > > > > There was serious discussion of that before the 8.0 release, but > > we decided not to forbid it. Check the archives; I don't recall > > the reasoning at the moment. > > UTF8 encoding works with the C locale assuming you don't care about > ordering of the character set, e.g. Japanese. No, sometimes Japanese needs char ordering too and I think this is not a Windows only problem. The real problem is Unicode defines char orderes in totally random manner because Chinese/Japanese/Korean Kanji characters are "Unified" in Unicode. To solve the problem, we can use convert UTF8 to EUC_JP using CONVERT. See archives for more details. Or you can use Unicode locale only if your platform's locale database is not broken and you only use single locale. -- Tatsuo Ishii ---(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: [BUGS] Out-of-Memory with ROWTYPE assignment in PL/pgSQL FOR loop
"Bill Rugolsky Jr." <[EMAIL PROTECTED]> writes:
> The PL/pgSQL FOR loop in the function consume_memory() defined below
> will consume VM on each iteration until the process hits its ulimit.
> The problem occurs with variables of ROWTYPE; there is no unbounded
> allocation when using simple types such as integer or varchar.
Yeah, looks like I introduced a memory leak with the 8.0 changes for
better support of rowtype variables :-(. Here's the patch.
regards, tom lane
Index: pl_exec.c
===
RCS file: /cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.127.4.2
diff -c -r1.127.4.2 pl_exec.c
*** pl_exec.c 20 Jun 2005 20:44:50 - 1.127.4.2
--- pl_exec.c 20 Jun 2005 22:46:14 -
***
*** 2003,2013
estate->eval_tuptable = NULL;
estate->eval_processed = 0;
estate->eval_lastoid = InvalidOid;
- estate->eval_econtext = NULL;
estate->err_func = func;
estate->err_stmt = NULL;
estate->err_text = NULL;
}
/* --
--- 2003,2032
estate->eval_tuptable = NULL;
estate->eval_processed = 0;
estate->eval_lastoid = InvalidOid;
estate->err_func = func;
estate->err_stmt = NULL;
estate->err_text = NULL;
+
+ /*
+* Create an EState for evaluation of simple expressions, if there's
+* not one already in the current transaction. The EState is made a
+* child of TopTransactionContext so it will have the right lifespan.
+*/
+ if (simple_eval_estate == NULL)
+ {
+ MemoryContext oldcontext;
+
+ oldcontext = MemoryContextSwitchTo(TopTransactionContext);
+ simple_eval_estate = CreateExecutorState();
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /*
+* Create an expression context for simple expressions.
+* This must be a child of simple_eval_estate.
+*/
+ estate->eval_econtext = CreateExprContext(simple_eval_estate);
}
/* --
***
*** 3238,3243
--- 3257,3264
Datum *value,
bool *isnull)
{
+ MemoryContext oldcontext;
+
switch (datum->dtype)
{
case PLPGSQL_DTYPE_VAR:
***
*** 3264,3272
--- 3285,3295
elog(ERROR, "row variable has no
tupdesc");
/* Make sure we have a valid type/typmod
setting */
BlessTupleDesc(row->rowtupdesc);
+ oldcontext =
MemoryContextSwitchTo(estate->eval_econtext->ecxt_per_tuple_memory);
tup = make_tuple_from_row(estate, row,
row->rowtupdesc);
if (tup == NULL)/* should not happen */
elog(ERROR, "row not compatible with
its own tupdesc");
+ MemoryContextSwitchTo(oldcontext);
*typeid = row->rowtupdesc->tdtypeid;
*value = HeapTupleGetDatum(tup);
*isnull = false;
***
*** 3299,3308
--- 3322,
* fields. Copy the tuple body and insert the
right
* values.
*/
+ oldcontext =
MemoryContextSwitchTo(estate->eval_econtext->ecxt_per_tuple_memory);
heap_copytuple_with_tuple(rec->tup, &worktup);
HeapTupleHeaderSetDatumLength(worktup.t_data,
worktup.t_len);
HeapTupleHeaderSetTypeId(worktup.t_data,
rec->tupdesc->tdtypeid);
HeapTupleHeaderSetTypMod(worktup.t_data,
rec->tupdesc->tdtypmod);
+ MemoryContextSwitchTo(oldcontext);
*typeid = rec->tupdesc->tdtypeid;
*value = HeapTupleGetDatum(&worktup);
*isnull = false;
***
*** 3579,3585
Oid *rettype)
{
Datum retval;
! ExprContext * volatile econtext;
ParamListInfo paramLI;
int i;
SnapshotsaveActiveSnapshot;
--- 3604,3610
Oid *rettype)
{
Datum retval;
! ExprContext *econtext = estate->eval_econtext;
ParamListInfo paramLI;
int i;
SnapshotsaveActiveSnapshot;
***
*** 3590,3609
*rettype = expr->expr_simple_type;
/*
-* Create an EState for evaluation of simple expressions,
Re: [BUGS] Out-of-Memory with ROWTYPE assignment in PL/pgSQL FOR loop
On Mon, Jun 20, 2005 at 06:54:20PM -0400, Tom Lane wrote: > "Bill Rugolsky Jr." <[EMAIL PROTECTED]> writes: > > The PL/pgSQL FOR loop in the function consume_memory() defined below > > will consume VM on each iteration until the process hits its ulimit. > > The problem occurs with variables of ROWTYPE; there is no unbounded > > allocation when using simple types such as integer or varchar. > > Yeah, looks like I introduced a memory leak with the 8.0 changes for > better support of rowtype variables :-(. Here's the patch. Thank you for the quick reply; much appreciated! Applied and tested against CVS head; that plugged the leak. -Bill ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
