[BUGS] BUG #4633: Bug in PL/PgSQL "SELECT .. INTO" statement parser
The following bug has been logged online: Bug reference: 4633 Logged by: Oleg Email address: sero...@gmail.com PostgreSQL version: PostgreSQL 8.3 Operating system: CentOS Description:Bug in PL/PgSQL "SELECT .. INTO" statement parser Details: Here is an example: CREATE TABLE test2 ( id BIGINT, chunk_id BIGINT ); CREATE TABLE test1 ( id BIGINT ); CREATE OR REPLACE FUNCTION "bug" () RETURNS pg_catalog.void AS $body$ DECLARE row_test1 test1%rowtype; row_test2 test2%rowtype; BEGIN SELECT test1, test2 FROM test1 JOIN test2 ON(chunk.id = test2.chunk_id) LIMIT 1 INTO row_test1, row_test2; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER; Will throw error: ERROR: LIMIT #,# syntax is not supported HINT: Use separate LIMIT and OFFSET clauses. QUERY: SELECT test1, test2 FROM test1 JOIN test2 ON(chunk.id = test2.chunk_id) LIMIT 1, 0, $1 CONTEXT: SQL statement in PL/PgSQL function "bug" near line 8 ** Ошибка ** ERROR: LIMIT #,# syntax is not supported SQL state: 42601 Подсказка:Use separate LIMIT and OFFSET clauses. Контекст:SQL statement in PL/PgSQL function "bug" near line 8 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4673: pl/PgSQL: Bug, when updating changed composite types.
The following bug has been logged online: Bug reference: 4673 Logged by: Oleg Email address: sero...@gmail.com PostgreSQL version: 8.3.6 Operating system: i686-redhat-linux-gnu Description:pl/PgSQL: Bug, when updating changed composite types. Details: If you added a field into composite type(table type), plpgsql won't update it, until you recompile function. DEMO SQL: ROLLBACK; BEGIN; CREATE TABLE bug_composite ( column_a INT, column_b INT ); CREATE TABLE bug_parent ( id INT, info bug_composite ); INSERT INTO bug_parent(id, info) VALUES (1, ROW(1, 2)); CREATE OR REPLACE FUNCTION bug_update(r bug_parent) RETURNS void AS $BODY$ BEGIN UPDATE bug_parent SET info = r.info WHERE id = r.id; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; SELECT bug_update(ROW(1, ROW(3, 5))); SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(3, 5)); ALTER TABLE bug_composite ADD COLUMN column_c INT; SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(3, 5, NULL)); SELECT bug_update(ROW(1, ROW(4, 6, 9))); SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(4, 6, NULL)); SELECT version(); -- "PostgreSQL 8.3.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)" -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4688: Bug in cache.
The following bug has been logged online:
Bug reference: 4688
Logged by: Oleg
Email address: sero...@gmail.com
PostgreSQL version: last stable
Operating system: CentOS
Description:Bug in cache.
Details:
Demo sql:
ROLLBACK;
BEGIN;
CREATE TABLE bug_composite_type (
text character varying(50)
);
CREATE TABLE bug_list (
test bug_composite_type
);
INSERT INTO bug_list VALUES (ROW('text'));
ALTER TABLE bug_composite_type RENAME TO tmp_table;
CREATE TABLE bug_composite_type
(
text character varying(250)
);
CREATE CAST (tmp_table AS composite_ad_texts)
WITHOUT FUNCTION AS ASSIGNMENT;
ALTER TABLE bug_list ALTER test TYPE composite_ad_texts;
DROP CAST (tmp_table AS composite_ad_texts);
DROP TABLE tmp_table;
SELECT * FROM bug_list; -- bug
-- ERROR: could not open relation with OID 395705050
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4710: Bug with sql functions, when using INSERT.. RETURNING .. statment
The following bug has been logged online: Bug reference: 4710 Logged by: Oleg Email address: sero...@gmail.com PostgreSQL version: last stable Operating system: CentOS Description:Bug with sql functions, when using INSERT.. RETURNING .. statment Details: Example: CREATE TABLE test (id BIGINT); CREATE OR REPLACE FUNCTION test(in_row test) RETURNS bigint AS $body$ INSERT INTO test (SELECT $1.*) RETURNING id; $body$ LANGUAGE 'sql'; WILL THROW: ERROR: return type mismatch in function declared to return bigint DETAIL: Function's final statement must be a SELECT. CONTEXT: SQL function "test" It is okey ? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5314: Error in nested composite types in plpgsql.
The following bug has been logged online:
Bug reference: 5314
Logged by: Oleg
Email address: sero...@gmail.com
PostgreSQL version: 8.3/8.4
Operating system: any
Description:Error in nested composite types in plpgsql.
Details:
Here is it reproduce code:
It works only, when procedure is plpgsql, with sql works fine.
ROLLBACK;
BEGIN;
CREATE TABLE bug_level_tree(
field BIGINT
);
CREATE TABLE bug_level_two(
field bug_level_tree
);
CREATE TABLE bug_level_one(
id BIGINT,
field bug_level_two
);
CREATE FUNCTION bug_procedure(in_row bug_level_one) RETURNS text AS $$
BEGIN
-- void
SELECT 1/0;
END;
$$ LANGUAGE plpgsql;
-- All okey
SELECT '(1,)'::bug_level_one;
-- Throws error
SELECT bug_procedure('(1,)');
-- ERROR: cannot assign non-composite value to a row variable
CONTEXT: PL/pgSQL function "bug_procedure" while storing call arguments
into local variables
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5352: Bug in PL/PgSQL "SELECT .. INTO" statement parser
The following bug has been logged online: Bug reference: 5352 Logged by: Oleg Email address: sero...@gmail.com PostgreSQL version: any Operating system: any Description:Bug in PL/PgSQL "SELECT .. INTO" statement parser Details: CREATE TABLE test2 ( id BIGINT, chunk_id BIGINT ); CREATE TABLE test1 ( id BIGINT ); CREATE OR REPLACE FUNCTION "bug" () RETURNS pg_catalog.void AS $body$ DECLARE row_test1 test1%rowtype; row_test2 test2%rowtype; BEGIN SELECT test1, chunk_id FROM test1 JOIN test2 ON(chunk.id = test2.chunk_id) LIMIT 1 INTO row_test1, row_test2; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER; Will throw error: ERROR: LIMIT #,# syntax is not supported HINT: Use separate LIMIT and OFFSET clauses. QUERY: SELECT test1, chunk_id FROM test1 JOIN test2 ON(chunk.id = test2.chunk_id) LIMIT 1, 0, $1 CONTEXT: SQL statement in PL/PgSQL function "bug" near line 8 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5353: Bug in procedure When you modificate table
The following bug has been logged online:
Bug reference: 5353
Logged by: Oleg
Email address: sero...@gmail.com
PostgreSQL version: any
Operating system: any
Description:Bug in procedure When you modificate table
Details:
CREATE TYPE "buggy_enum_first" AS ENUM ( 'bug1', 'bug2', 'bug3' );
CREATE TABLE "bug_table" (
"id" BIGINT NOT NULL,
"buggy_enum_field" "buggy_enum_first" DEFAULT 'bug1'::buggy_enum_first NOT
NULL,
CONSTRAINT "test_table_pkey" PRIMARY KEY("id")
) WITHOUT OIDS;
CREATE FUNCTION buggy_procedure() RETURNS SETOF bug_table
AS $$
BEGIN
-- @todo hide password
RETURN QUERY (
SELECT *
FROM bug_table
);
END;
$$
LANGUAGE plpgsql STRICT SECURITY DEFINER;
SELECT * FROM buggy_procedure(); -- All Okey
DROP TYPE buggy_enum_first CASCADE;
CREATE TYPE "buggy_enum_second" AS ENUM ( 'bug1', 'bug2', 'bug3' );
ALTER TABLE bug_table ADD COLUMN buggy_enum_field buggy_enum_second;
SELECT * FROM buggy_procedure(); -- Bug
/*
ERROR: structure of query does not match function result type
CONTEXT: PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5434: select from xpath_table gives wrong results
The following bug has been logged online:
Bug reference: 5434
Logged by: Oleg
Email address: sacramento_...@mail.ru
PostgreSQL version: 8.4.3
Operating system: KUbuntu
Description:select from xpath_table gives wrong results
Details:
Here is a code:
create table tmp_xml(rowId SERIAL UNIQUE NOT NULL,
xml_context xml,
CONSTRAINT PK_tmp_xml PRIMARY KEY (rowId));
insert into tmp_xml (xml_context)
values(''
''
''
''
''
'');
SELECT t.OBJ_ID, t.OBJ_NAME, t.OBJ_MASTER
FROM xpath_table('rowId',
'xml_context',
'tmp_xml',
'/root/obj/@obj_id|/root/obj/@obj_name|/root/obj/@obj_master|',
'true')
AS t(row_id integer,
OBJ_ID integer,
OBJ_NAME varchar(255),
OBJ_MASTER INT
);
I was sure, that result of this query should be like this:
obj_id | obj_name| obj_master
1 | First Object |
2 | Second Object |1
3 | Third Object |
4 | Fourth Object |1
But instead of I got this result:
obj_id | obj_name| obj_master
1 | First Object |1
2 | Second Object |1
3 | Third Object |
4 | Fourth Object |
Why is this so? Maybe I use xpath_table wrong way?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5852: Function date_trunc is not IMMUTABLE
The following bug has been logged online: Bug reference: 5852 Logged by: Oleg Email address: sero...@gmail.com PostgreSQL version: 8.3 Operating system: CentOS Description:Function date_trunc is not IMMUTABLE Details: Please mark function date_trunc as IMMUTABLE for using in index. Thanks! -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Bug in procedure When you modificate table
SQL BUG CODE:
BEGIN;
SELECT version(); -- "PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by
GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)"
CREATE TYPE "buggy_enum_first" AS ENUM ( 'bug1', 'bug2', 'bug3' );
CREATE TABLE "bug_table" (
"id" BIGINT NOT NULL,
"buggy_enum_field" "buggy_enum_first" DEFAULT 'bug1'::buggy_enum_first NOT
NULL,
CONSTRAINT "test_table_pkey" PRIMARY KEY("id")
) WITHOUT OIDS;
CREATE FUNCTION buggy_procedure() RETURNS SETOF bug_table
AS $$
BEGIN
-- @todo hide password
RETURN QUERY (
SELECT *
FROM bug_table
);
END;
$$
LANGUAGE plpgsql STRICT SECURITY DEFINER;
SELECT * FROM buggy_procedure(); -- All Okey
DROP TYPE buggy_enum_first CASCADE;
CREATE TYPE "buggy_enum_second" AS ENUM ( 'bug1', 'bug2', 'bug3' );
ALTER TABLE bug_table ADD COLUMN buggy_enum_field buggy_enum_second;
SELECT * FROM buggy_procedure(); -- Bug
ROLLBACK;
/*NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_table_pkey" for table "bug_table"Результат запроса с отброшенным
числом строк: 1.
NOTICE: drop cascades to default for table bug_table column
buggy_enum_fieldNOTICE: drop cascades to table bug_table column
buggy_enum_field
ERROR: structure of query does not match function result type
CONTEXT: PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/
[BUGS] Bug with FOR ... LOOP and composite types
Hello.
Seems there is an error when I try to use a table with one field - composite
type, when SELECT QUERY in FOR ... LOOP instruction returns empty result.
Here are steps to reproduce:
CREATE TYPE "t_type" AS (
"a" BIGINT
);
CREATE TABLE"t_table" (
"id" BIGINT NOT NULL,
"t" "t_type",
CONSTRAINT "t_table_pkey" PRIMARY KEY("id")
) WITH OIDS;
CREATE OR REPLACE FUNCTION "t_func" () RETURNS SETOF "t_table" AS
$body$
DECLARE
rec t_table%ROWTYPE;
BEGIN
FOR rec IN
SELECT *
FROM t_table
WHERE 1=0
LOOP
RETURN NEXT rec;
END LOOP;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
SELECT * FROM t_func()
Result:
ERROR: cannot assign non-composite value to a row variable
CONTEXT: PL/pgSQL function "t_func" line 4 at FOR over SELECT rows
[BUGS] Bug in RETURN QUERY
Hello all SQL BUG CODE:
BEGIN;
SELECT version(); -- "PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by
GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)"
CREATE TYPE "buggy_enum_first" AS ENUM ( 'bug1', 'bug2', 'bug3' );
CREATE TABLE "bug_table" (
"id" BIGINT NOT NULL,
"buggy_enum_field" "buggy_enum_first" DEFAULT 'bug1'::buggy_enum_first NOT
NULL,
CONSTRAINT "test_table_pkey" PRIMARY KEY("id")
) WITHOUT OIDS;
CREATE FUNCTION buggy_procedure() RETURNS SETOF bug_table
AS $$
BEGIN
-- @todo hide password
RETURN QUERY (
SELECT *
FROM bug_table
);
END;
$$
LANGUAGE plpgsql STRICT SECURITY DEFINER;
SELECT * FROM buggy_procedure(); -- All Okey
DROP TYPE buggy_enum_first CASCADE;
CREATE TYPE "buggy_enum_second" AS ENUM ( 'bug1', 'bug2', 'bug3' );
ALTER TABLE bug_table ADD COLUMN buggy_enum_field buggy_enum_second;
SELECT * FROM buggy_procedure(); -- Bug
ROLLBACK;
/*NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_table_pkey" for table "bug_table"
NOTICE: drop cascades to default for table bug_table column
buggy_enum_fieldNOTICE: drop cascades to table bug_table column
buggy_enum_field
ERROR: structure of query does not match function result type
CONTEXT: PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/
Re: [BUGS] Bug with FOR ... LOOP and composite types
But if there are some records in t_table and we romove WHERE 1=0, we will
have
ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function
"t_func" line 9 at RETURN NEXT
2008/9/1 Pavel Stehule <[EMAIL PROTECTED]>
> Hello
>
> 2008/9/1 Oleg Serov <[EMAIL PROTECTED]>:
> > Hello.
> >
> > Seems there is an error when I try to use a table with one field -
> composite
> > type, when SELECT QUERY in FOR ... LOOP instruction returns empty result.
> > Here are steps to reproduce:
> >
> > CREATE TYPE "t_type" AS (
> > "a" BIGINT
> > );
> >
> > CREATE TABLE"t_table" (
> > "id" BIGINT NOT NULL,
> > "t" "t_type",
> > CONSTRAINT "t_table_pkey" PRIMARY KEY("id")
> > ) WITH OIDS;
> >
> > CREATE OR REPLACE FUNCTION "t_func" () RETURNS SETOF "t_table" AS
> > $body$
> > DECLARE
> > rec t_table%ROWTYPE;
> > BEGIN
> > FOR rec IN
> > SELECT *
> > FROM t_table
> > WHERE 1=0
> > LOOP
> > RETURN NEXT rec;
> > END LOOP;
> > END;
> > $body$
> > LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
> >
> > SELECT * FROM t_func()
> >
> > Result:
> >
> > ERROR: cannot assign non-composite value to a row variable
> > CONTEXT: PL/pgSQL function "t_func" line 4 at FOR over SELECT rows
>
> ROWTYPE is problem.
>
> postgres=# CREATE OR REPLACE FUNCTION "t_func" () RETURNS SETOF "t_table"
> AS
> postgres-# $body$
> postgres$# DECLARE
> postgres$# rec record;
> postgres$# BEGIN
> postgres$# FOR rec IN
> postgres$# SELECT *
> postgres$# FROM t_table
> postgres$# WHERE 1=0
> postgres$# LOOP
> postgres$# RETURN NEXT rec;
> postgres$# END LOOP;
> postgres$# END;
> postgres$# $body$
> postgres-# LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY
> INVOKER;
> CREATE FUNCTION
> postgres=# select * from t_func();
> id | t
> +---
> (0 rows)
>
> regards
> Pavel Stehule
>
Re: [BUGS] Bug with FOR ... LOOP and composite types
Yes, you are right, with record type working correct; Thanks 2008/9/2 Tom Lane <[EMAIL PROTECTED]> > "Oleg Serov" <[EMAIL PROTECTED]> writes: > > But if there are some records in t_table and we romove WHERE 1=0, we will > > have > > ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL > function > > "t_func" line 9 at RETURN NEXT > > I couldn't reproduce that here, at least not with versions newer than > 8.0. Maybe you were testing a case that also involved dropped columns? > >regards, tom lane >
[BUGS] Bug in triggers
SQL code:
ROLLBACK;
BEGIN;
CREATE TYPE "composite_type" AS (
"typename" VARCHAR
);
CREATE TABLE "buggy" (
"id" BIGINT NOT NULL,
"bug" "composite_type",
CONSTRAINT "buggy_pkey" PRIMARY KEY("id")
) WITH OIDS;
INSERT INTO buggy (id, bug) VALUES
(100196418052926086, NULL);
CREATE OR REPLACE FUNCTION "public"."test_bug" () RETURNS trigger AS
$body$
DECLARE
tmp_old buggy%rowtype;
tmp_new buggy%rowtype;
BEGIN
RAISE NOTICE 'OLD: %', OLD;
RAISE NOTICE 'NEW: %', NEW;
tmp_old := OLD;
RAISE NOTICE 'TMP OLD: %', tmp_old;
RAISE NOTICE 'TMP OLD = OLD => %', tmp_old::text = OLD::text;
tmp_old.id := NEW.id;
tmp_new := NEW;
RAISE NOTICE 'TMP OLD: %', tmp_old;
RAISE NOTICE 'TMP NEW: %', tmp_new;
RAISE NOTICE 'TMP OLD = TMP NEW => %', tmp_old::text = tmp_new::text;
RAISE NOTICE 'TMP OLD = NEW => %', tmp_old::text = NEW::text;
IF (tmp_old::text <> tmp_new::text) <> (tmp_old::text <> NEW::text)
THEN
RAISE EXCEPTION 'PGSQL BUG!';
END IF;
RETURN OLD;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE TRIGGER "t_bug" BEFORE UPDATE
ON buggy FOR EACH ROW
EXECUTE PROCEDURE "test_bug"();
UPDATE buggy SET id = 100112779830304388 WHERE 100196418052926086 = id;
/**
NOTICE: OLD: (100196418052926086,)
NOTICE: NEW: (100112779830304388,)
NOTICE: TMP OLD: (100196418052926086,"()")
NOTICE: TMP OLD = OLD => f
NOTICE: TMP OLD: (100112779830304388,"()")
NOTICE: TMP NEW: (100112779830304388,"()")
NOTICE: TMP OLD = TMP NEW => t
NOTICE: TMP OLD = NEW => f -- BUG!!!
**/
Re: [BUGS] Bug in triggers
Sorry, bug is not in triggers, it is in PL/PGSQL var assign mechanism
here it is an example:
ROLLBACK;
BEGIN;
CREATE TYPE "composite_type" AS (
"type" VARCHAR,
"type2" VARCHAR
);
CREATE TABLE "buggy" (
"id" BIGINT NOT NULL,
"bug" "composite_type",
CONSTRAINT "buggy_pkey" PRIMARY KEY("id")
) WITH OIDS;
CREATE OR REPLACE FUNCTION "test_bug" () RETURNS pg_catalog.void AS
$body$
DECLARE
tmp_old buggy%rowtype;
BEGIN
tmp_old := ROW(1, NULL)::buggy;
IF tmp_old::text <> ROW(1, NULL)::buggy::text THEN
RAISE EXCEPTION '% <> %', tmp_old, ROW(1, NULL)::buggy;
END IF;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
WILL THROW A EXCEPTION:
ERROR: (1,"(,)") <> (1,)
SELECT test_bug();
2008/9/26, Oleg Serov <[EMAIL PROTECTED]>:
> SQL code:
>
>
> ROLLBACK;
> BEGIN;
> CREATE TYPE "composite_type" AS (
> "typename" VARCHAR
> );
>
>
> CREATE TABLE "buggy" (
> "id" BIGINT NOT NULL,
> "bug" "composite_type",
> CONSTRAINT "buggy_pkey" PRIMARY KEY("id")
> ) WITH OIDS;
>
> INSERT INTO buggy (id, bug) VALUES
> (100196418052926086, NULL);
>
> CREATE OR REPLACE FUNCTION "public"."test_bug" () RETURNS trigger AS
> $body$
> DECLARE
> tmp_old buggy%rowtype;
> tmp_new buggy%rowtype;
> BEGIN
> RAISE NOTICE 'OLD: %', OLD;
> RAISE NOTICE 'NEW: %', NEW;
>
> tmp_old := OLD;
> RAISE NOTICE 'TMP OLD: %', tmp_old;
>
> RAISE NOTICE 'TMP OLD = OLD => %', tmp_old::text = OLD::text;
>
> tmp_old.id := NEW.id;
> tmp_new := NEW;
>
> RAISE NOTICE 'TMP OLD: %', tmp_old;
> RAISE NOTICE 'TMP NEW: %', tmp_new;
>
> RAISE NOTICE 'TMP OLD = TMP NEW => %', tmp_old::text = tmp_new::text;
> RAISE NOTICE 'TMP OLD = NEW => %', tmp_old::text = NEW::text;
>
>
>
> IF (tmp_old::text <> tmp_new::text) <> (tmp_old::text <> NEW::text)
> THEN
> RAISE EXCEPTION 'PGSQL BUG!';
> END IF;
> RETURN OLD;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> CREATE TRIGGER "t_bug" BEFORE UPDATE
> ON buggy FOR EACH ROW
> EXECUTE PROCEDURE "test_bug"();
>
>
> UPDATE buggy SET id = 100112779830304388 WHERE 100196418052926086 = id;
> /**
> NOTICE: OLD: (100196418052926086,)
> NOTICE: NEW: (100112779830304388,)
> NOTICE: TMP OLD: (100196418052926086,"()")
> NOTICE: TMP OLD = OLD => f
> NOTICE: TMP OLD: (100112779830304388,"()")
> NOTICE: TMP NEW: (100112779830304388,"()")
> NOTICE: TMP OLD = TMP NEW => t
> NOTICE: TMP OLD = NEW => f -- BUG!!!
>
> **/
>
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Bug in pl/pgsql with hstore OR bug in pl/pgsql IF (text field is boolean?)
I can't get hstore value by key. i have very interesting error
DEMO SQL CODE:
--
ROLLBACK;
BEGIN;
-- VERSION INFO
SELECT VERSION();
-- RESULT:
-- PostgreSQL 8.3.4 on i686-redhat-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)
-- TEST FOR HSTORE:
SELECT (('test' => 'testvalue')::hstore)->'test'; -- HSTORE WORKS!
-- HSTORE OK;
-- BUG REPRODUCTION FUNCTION
CREATE OR REPLACE FUNCTION "hstore_bug" () RETURNS pg_catalog.void AS
$body$
DECLARE
res HSTORE;
BEGIN
res := ('test' => NULL)::hstore;
IF res->'test' IS NULL THEN
RAISE EXCEPTION 'HSTORE IS OK';
END IF;
RAISE EXCEPTION 'HSTORE IS BUGGY';
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
--- BUG REPRODUCTION:
SELECT hstore_bug();
-- ERROR MSG
-- ERROR: operator does not exist: hstore -> boolean
-- LINE 1: SELECT $1 ->'test' IS NULL
-- ^
-- HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
-- QUERY: SELECT $1 ->'test' IS NULL
-- CONTEXT: PL/pgSQL function "hstore_bug" line 6 at IF
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug in pl/pgsql with hstore OR bug in pl/pgsql IF (text field is boolean?)
Ok, thanks... it is not a bug;
2008/10/27 hubert depesz lubaczewski <[EMAIL PROTECTED]>:
> On Mon, Oct 27, 2008 at 05:08:35PM +0300, Oleg Serov wrote:
>> I can't get hstore value by key. i have very interesting error
>
> the problem is that -> has very low priority, but you work with it
> anyway:
> # select tconvert('a', 'b')->'a' is null;
> ERROR: operator does not exist: hstore -> boolean
> LINE 1: select tconvert('a', 'b')->'a' is null;
> ^
> HINT: No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
> ([EMAIL PROTECTED]:5840) 15:14:03 [depesz]
> # select (tconvert('a', 'b')->'a') is null;
> ?column?
> --
> f
> (1 row)
>
> depesz
>
> --
> Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
> jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007
>
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Bug in plpgsql, when using NEW with composite field value.
SQL: CREATE OR REPLACE FUNCTION "bug_with_triggers" () RETURNS trigger AS $body$ BEGIN PERFORM COALESCE(NEW.some_composite_field.field, TRUE); END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; Error: ERROR: NEW used in query that is not in a rule QUERY: SELECT COALESCE(NEW.somecompositefield.field, TRUE) CONTEXT: SQL statement in PL/PgSQL function "bug_with_triggers" near line 2 ** ERROR ** ERROR: NEW used in query that is not in a rule SQL state: 42601 Контекст:SQL statement in PL/PgSQL function "bug_with_triggers" near line 2 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] plpgsql bug OR future request: Assign fileds in composite subfiled. eg. table.compositefield.subfield := TRUE;
ERROR: syntax error at or near "("
LINE 15:(row_main_table.subtype).flag := FALSE; -- cannot set
^
** Ошибка **
ERROR: syntax error at or near "("
SQL state: 42601
Характеристика:321
2008/12/10 Pavel Stehule <[EMAIL PROTECTED]>:
> Hello
>
> 2008/12/10 Oleg Serov <[EMAIL PROTECTED]>:
>> SQL:
>> CREATE TABLE second_type (
>>flag BOOLEAN
>> );
>> CREATE TABLE main_type (
>>subtype second_type
>> );
>> CREATE OR REPLACE FUNCTION "bug_in_tabletypes" () RETURNS pg_catalog.void AS
>> $body$
>> DECLARE
>>row_main_table main_type%rowtype;
>> BEGIN
>>row_main_table.subtype := NULL; -- all okey;
>
>
>
>>row_main_table.subtype.flag := FALSE; -- cannot set
>
> try
> (row_main_table.subtype).flag := FALSE; -- cannot set
>
> regards
> Pavel Stehule
>
>> END;
>> $body$
>> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>>
>> Will produce error:
>>
>>
>> ERROR: syntax error at or near "row_main_table"
>> LINE 1: row_main_table.subtype.flag := FALSE
>>^
>> QUERY: row_main_table.subtype.flag := FALSE
>> CONTEXT: SQL statement in PL/PgSQL function "bug_in_tabletypes" near line
>>
>> --
>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-bugs
>>
>
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] plpgsql bug OR future request: Assign fileds in composite subfiled. eg. table.compositefield.subfield := TRUE;
SQL: CREATE TABLE second_type ( flag BOOLEAN ); CREATE TABLE main_type ( subtype second_type ); CREATE OR REPLACE FUNCTION "bug_in_tabletypes" () RETURNS pg_catalog.void AS $body$ DECLARE row_main_table main_type%rowtype; BEGIN row_main_table.subtype := NULL; -- all okey; row_main_table.subtype.flag := FALSE; -- cannot set END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; Will produce error: ERROR: syntax error at or near "row_main_table" LINE 1: row_main_table.subtype.flag := FALSE ^ QUERY: row_main_table.subtype.flag := FALSE CONTEXT: SQL statement in PL/PgSQL function "bug_in_tabletypes" near line -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4591: postgres.exe more than 150 processes runned
The following bug has been logged online: Bug reference: 4591 Logged by: Steblina OLeg Email address: oldn...@ukrlink.net PostgreSQL version: 8.1 Operating system: Windows XP pro sp2 Description:postgres.exe more than 150 processes runned Details: http://apoc.org.ua/oldnick/images/pgsql_processes.jpg Processes start each other and hang, doing nothing. Connections with base are not made, for a night 177 processes postgres.exe. I used version 8.1 instead of newer 8.3 for the reason that the service in version 8.3 is started and at once stops. Computer configuration: Q6600, 2Gb, WinXPproSP2 And some part of log (about 300Kb per day): autovacuum process (PID 2344) was terminated by signal 66 2008-12-21 16:48:54 LOG: terminating any other active server processes 2008-12-21 16:48:54 LOG: all server processes terminated; reinitializing 2008-12-21 16:48:54 LOG: database system was interrupted at 2008-12-21 16:47:53 Греция, Турция (зима) 2008-12-21 16:48:54 LOG: checkpoint record is at 0/55D720 2008-12-21 16:48:54 LOG: redo record is at 0/55D720; undo record is at 0/0; shutdown TRUE 2008-12-21 16:48:54 LOG: next transaction ID: 733; next OID: 17233 2008-12-21 16:48:54 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2008-12-21 16:48:54 LOG: database system was not properly shut down; automatic recovery in progress 2008-12-21 16:48:54 LOG: record with zero length at 0/55D768 2008-12-21 16:48:54 LOG: redo is not required 2008-12-21 16:48:54 LOG: database system is ready 2008-12-21 16:48:54 LOG: transaction ID wrap limit is 2147484148, limited by database "postgres" 2008-12-21 16:49:54 LOG: autovacuum process (PID 4016) was terminated by signal 66 2008-12-21 16:49:54 LOG: terminating any other active server processes 2008-12-21 16:49:55 LOG: all server processes terminated; reinitializing 2008-12-21 16:49:55 LOG: database system was interrupted at 2008-12-21 16:48:54 Греция, Турция (зима) 2008-12-21 16:49:55 LOG: checkpoint record is at 0/55D768 2008-12-21 16:49:55 LOG: redo record is at 0/55D768; undo record is at 0/0; shutdown TRUE 2008-12-21 16:49:55 LOG: next transaction ID: 733; next OID: 17233 2008-12-21 16:49:55 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2008-12-21 16:49:55 LOG: database system was not properly shut down; automatic recovery in progress 2008-12-21 16:49:55 LOG: record with zero length at 0/55D7B0 2008-12-21 16:49:55 LOG: redo is not required 2008-12-21 16:49:55 LOG: database system is ready 2008-12-21 16:49:55 LOG: transaction ID wrap limit is 2147484148, limited by database "postgres" 2008-12-21 16:50:55 LOG: autovacuum process (PID 7512) was terminated by signal 66 2008-12-21 16:50:55 LOG: terminating any other active server processes 2008-12-21 16:50:55 LOG: all server processes terminated; reinitializing 2008-12-21 16:50:55 LOG: database system was interrupted at 2008-12-21 16:49:55 Греция, Турция (зима) 2008-12-21 16:50:55 LOG: checkpoint record is at 0/55D7B0 2008-12-21 16:50:55 LOG: redo record is at 0/55D7B0; undo record is at 0/0; shutdown TRUE 2008-12-21 16:50:55 LOG: next transaction ID: 733; next OID: 17233 2008-12-21 16:50:55 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2008-12-21 16:50:55 LOG: database system was not properly shut down; automatic recovery in progress 2008-12-21 16:50:55 LOG: record with zero length at 0/55D7F8 2008-12-21 16:50:55 LOG: redo is not required 2008-12-21 16:50:55 LOG: database system is ready 2008-12-21 16:50:55 LOG: transaction ID wrap limit is 2147484148, limited by database "postgres" -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4562: ts_headline() adds space when parsing url
On Wed, 14 Jan 2009, Bruce Momjian wrote:
This bug still exists in my testing.
We fixed all issues with ts_headline and will submit soon.
---
Tom Lane wrote:
"Denis Monsieur" writes:
The problem is a space being added to text in the form of
http://some.url/path
Compare the output:
shs=# SELECT ts_headline('http://some.url', to_tsquery('sometext'));
ts_headline
-
http://some.url
(1 row)
shs=# SELECT ts_headline('http://some.url/path', to_tsquery('sometext'));
ts_headline
---
http:// some.url/path
(1 row)
I looked into this, and it seems that the problem is that
generateHeadline() emits a space for any token marked as replace = 1.
I think it probably shouldn't emit anything at all. AFAICS the cases
where replace will get set are token types URL, TAG, NUMHWORD,
ASCIIHWORD, HWORD. For URL and the HWORD variants the space is
certainly undesirable, because these token types are just respecifying
text that is also covered by their component tokens. The only case
where you could make an argument that the space is useful is TAG,
as in
regression=# SELECT ts_headline('httpblah', to_tsquery('sometext'));
ts_headline
-
http blah
(1 row)
But it seems to me to be at least as plausible that you should get
nothing as that you should get a space for a removed tag.
Comments?
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug in PL/PgSQL "SELECT .. INTO" statement parser
Sorry, but is not important, i forgot to remove original table name "chunk_ad", but is not affected to the bug.. 2009/1/21 Oleg Serov : > Here is an example: > > CREATE TABLE test2 ( >id BIGINT, >chunk_id BIGINT > ); > CREATE TABLE test1 ( >id BIGINT > ); > > CREATE OR REPLACE FUNCTION "bug" () RETURNS pg_catalog.void AS > $body$ > DECLARE >row_test1 test1%rowtype; >row_test2 test2%rowtype; > BEGIN >SELECT test1, chunk_ad >FROM test1 JOIN test2 ON(chunk.id = test2.chunk_id) >LIMIT 1 >INTO row_test1, row_test2; > > END; > $body$ > LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER; > > Will throw error: > ERROR: LIMIT #,# syntax is not supported > HINT: Use separate LIMIT and OFFSET clauses. > QUERY: SELECT test1, chunk_ad FROM test1 JOIN test2 ON(chunk.id = > test2.chunk_id) LIMIT 1, 0, $1 > CONTEXT: SQL statement in PL/PgSQL function "bug" near line 8 > > ** Ошибка ** > > ERROR: LIMIT #,# syntax is not supported > SQL state: 42601 > Подсказка:Use separate LIMIT and OFFSET clauses. > Контекст:SQL statement in PL/PgSQL function "bug" near line 8 > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Bug in PL/PgSQL "SELECT .. INTO" statement parser
Here is an example: CREATE TABLE test2 ( id BIGINT, chunk_id BIGINT ); CREATE TABLE test1 ( id BIGINT ); CREATE OR REPLACE FUNCTION "bug" () RETURNS pg_catalog.void AS $body$ DECLARE row_test1 test1%rowtype; row_test2 test2%rowtype; BEGIN SELECT test1, chunk_ad FROM test1 JOIN test2 ON(chunk.id = test2.chunk_id) LIMIT 1 INTO row_test1, row_test2; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER; Will throw error: ERROR: LIMIT #,# syntax is not supported HINT: Use separate LIMIT and OFFSET clauses. QUERY: SELECT test1, chunk_ad FROM test1 JOIN test2 ON(chunk.id = test2.chunk_id) LIMIT 1, 0, $1 CONTEXT: SQL statement in PL/PgSQL function "bug" near line 8 ** Ошибка ** ERROR: LIMIT #,# syntax is not supported SQL state: 42601 Подсказка:Use separate LIMIT and OFFSET clauses. Контекст:SQL statement in PL/PgSQL function "bug" near line 8 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS]
Привет! Скажите пожалуйста адрес поддержки или помогите решить мою проблему. Я не могу скачать PostgreSQL 8.0.3, версия не выше. Как узнать ссылку для скачивания? Hi! Tell me please mail support or help to solve my problem. I can not download PostgreSQL 8.0.3, not version above. How do I know the link for download? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Diffrent column ordering after dump/restore tables with INHERITS
If i have tables with inherits, and the n i adding a column in to the base table, the new column will be at the on of column list in child table, but when i done dump->restore i have surprise, Column ordering was changed. Some example: CREATE TABLE test_base ( id INT ); CREATE TABLE test_child_with_data ( t TEXT ) INHERITS (test_base); ALTER TABLE test_base ADD COLUMN date DATE; INSERT INTO test_child_with_data SELECT 1, 'test', NOW(); -- works fine. -- Dump/restore INSERT INTO test_child_with_data SELECT 1, 'test', NOW(); -- error.. So, column ordering after dump, restore must be the same! I don't know how to live with it, now i have on production-server small db, and i can use dump/restore. but on other project dump/restore takes 8 hours.. so, any ideas ? More detailed how i use it in one procedure INSERT INTO test_child_with_data SELECT select_procedure(); select_procedure: ... SELECT 1, 'test', NOW() FROM... -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Diffrent column ordering after dump/restore tables with INHERITS
No, we can't do like this, because we have something like this: CREATE TABLE test_base ( id INT ); CREATE TABLE test_child_with_data ( t TEXT ) INHERITS (test_base); ALTER TABLE test_base ADD COLUMN date DATE; INSERT INTO test_child_with_data VALUES (1, 'text', NOW()); CREATE FUNCTION some_magic() RETURNS SETOF test_child_with_data AS $$ SELECT (SUM(id), t, MIN(date))::test_child_with_data FROM test_child_with_data GROUP BY t $$ LANGUAGE SQL; INSERT INTO test_child_with_data SELECT * FROM some_magic(); -- Works fine. -- Dump/restore INSERT INTO test_child_with_data SELECT * FROM some_magic(); -- Error. On Sun, Jul 5, 2009 at 4:48 AM, toruvinn wrote: > On Sat, 04 Jul 2009 22:10:55 +0200, Oleg Serov wrote: >> >> INSERT INTO test_child_with_data >> SELECT 1, 'test', NOW(); -- works fine. >> -- Dump/restore >> INSERT INTO test_child_with_data >> SELECT 1, 'test', NOW(); -- error.. > > How about INSERT INTO test_child_with_data (id, t, date) SELECT 1, 'test', > NOW(); ? You'll won't have to care about order of the columns anymore. > > Best regards, > -- > ru > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Diffrent column ordering after dump/restore tables with INHERITS
Here is the solution of this problem:
Patch
src/bin/pg_dump/pg_dump.c
appendPQExpBuffer(q, "CREATE TABLE %s (",
fmtId(tbinfo->dobj.name));
actual_atts = 0;
for (j = 0; j < tbinfo->numatts; j++)
{
/* Is this one of the table's own attrs, and not dropped ? */
- if (!tbinfo->inhAttrs[j] &&
- (!tbinfo->attisdropped[j] || binary_upgrade))
+ if (!tbinfo->attisdropped[j] || binary_upgrade)
{
/* Format properly if not first attr */
if (actual_atts > 0)
appendPQExpBuffer(q, ",");
appendPQExpBuffer(q, "\n ");
Before patch
CREATE TABLE "tmp"."stat_agg_all" (
"foo" BOOLEAN
) INHERITS ("prt4_stat"."stat_data", "prt4_stat"."stat_agg")
WITH OIDS;
After patch:
CREATE TABLE "tmp"."stat_agg_all" (
"foo" BOOLEAN,
"time" TIMESTAMP WITH TIME ZONE NOT NULL,
"banner_views" NUMERIC,
"ad_page_views" NUMERIC,
"phone_validate_views" NUMERIC,
"phone_validate_code_sended" NUMERIC,
"phone_validate_code_validate" NUMERIC,
"ad_form_views" NUMERIC,
"ad_form_submits" NUMERIC,
"ad_shop_select_views" NUMERIC,
"ad_emitted" NUMERIC,
"ad_redeemed" NUMERIC,
"client_payed" NUMERIC,
"mediapartner_charged" NUMERIC,
"ad_banner_views" NUMERIC,
"id" BIGINT NOT NULL,
"interval" "prt4_stat"."stat_enum_interval"
) INHERITS ("prt4_stat"."stat_data", "prt4_stat"."stat_agg")
WITH OIDS;
it well be okey!
On Sun, Jul 5, 2009 at 12:45 PM, Oleg Serov wrote:
> No, we can't do like this, because we have something like this:
>
> CREATE TABLE test_base (
> id INT
> );
> CREATE TABLE test_child_with_data (
> t TEXT
> ) INHERITS (test_base);
>
> ALTER TABLE test_base ADD COLUMN date DATE;
>
> INSERT INTO test_child_with_data VALUES (1, 'text', NOW());
>
> CREATE FUNCTION some_magic() RETURNS SETOF test_child_with_data AS
> $$
> SELECT (SUM(id), t, MIN(date))::test_child_with_data
> FROM test_child_with_data
> GROUP BY t
> $$ LANGUAGE SQL;
>
>
> INSERT INTO test_child_with_data
> SELECT * FROM some_magic(); -- Works fine.
>
>
> -- Dump/restore
>
>
> INSERT INTO test_child_with_data
> SELECT * FROM some_magic(); -- Error.
>
>
> On Sun, Jul 5, 2009 at 4:48 AM, toruvinn wrote:
>> On Sat, 04 Jul 2009 22:10:55 +0200, Oleg Serov wrote:
>>>
>>> INSERT INTO test_child_with_data
>>> SELECT 1, 'test', NOW(); -- works fine.
>>> -- Dump/restore
>>> INSERT INTO test_child_with_data
>>> SELECT 1, 'test', NOW(); -- error..
>>
>> How about INSERT INTO test_child_with_data (id, t, date) SELECT 1, 'test',
>> NOW(); ? You'll won't have to care about order of the columns anymore.
>>
>> Best regards,
>> --
>> ru
>>
>
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Diffrent column ordering after dump/restore tables with INHERITS
How about adding this patch to postgresql it will slove the problem? On Sun, Jul 5, 2009 at 8:10 PM, Greg Stark wrote: > On Sun, Jul 5, 2009 at 4:28 PM, Tom Lane wrote: >> when i done dump->restore i >>> have surprise, >>> Column ordering was changed. >> >> This is not a bug, it's the intended behavior. > > I thought that was a bug, just one that was too hard to fix for the > problems it caused. It might be more fixable if we get around to the > work that was discussed earlier where we separate attnum into three > different values. > > Oleg: note that having the columns in the same position allows some > optimizations in the executor so it's probably a good thing if it > hasn't broken your application. > > -- > greg > http://mit.edu/~gsstark/resume.pdf > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Crazy query plan.
SQL: CREATE TABLE test (id BIGINT, id2 BIGINT, id3 BIGINT, id4 BIGINT); INSERT INTO test SELECT i, i, i, i FROM generate_series(0, 9) i; EXPLAIN ANALYZE SELECT ((SELECT tmp::test FROM (SELECT * FROM test LIMIT 1) tmp)::test).*; WILL: QUERY PLAN Result (cost=0.11..0.12 rows=1 width=0) (actual time=0.076..0.078 rows=1 loops=1) InitPlan -> Subquery Scan tmp (cost=0.00..0.03 rows=1 width=32) (actual time=0.014..0.019 rows=1 loops=1) -> Limit (cost=0.00..0.02 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=1) -> Seq Scan on test (cost=0.00..23.10 rows=1310 width=32) (actual time=0.005..0.005 rows=1 loops=1) -> Subquery Scan tmp (cost=0.00..0.03 rows=1 width=32) (actual time=0.008..0.013 rows=1 loops=1) -> Limit (cost=0.00..0.02 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=1) -> Seq Scan on test (cost=0.00..23.10 rows=1310 width=32) (actual time=0.002..0.002 rows=1 loops=1) -> Subquery Scan tmp (cost=0.00..0.03 rows=1 width=32) (actual time=0.008..0.012 rows=1 loops=1) -> Limit (cost=0.00..0.02 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=1) -> Seq Scan on test (cost=0.00..23.10 rows=1310 width=32) (actual time=0.003..0.003 rows=1 loops=1) -> Subquery Scan tmp (cost=0.00..0.03 rows=1 width=32) (actual time=0.009..0.013 rows=1 loops=1) -> Limit (cost=0.00..0.02 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=1) -> Seq Scan on test (cost=0.00..23.10 rows=1310 width=32) (actual time=0.002..0.002 rows=1 loops=1) Total runtime: 0.138 ms One subquery for each column. wtf? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5235: Segmentation fault under high load through JDBC
The following bug has been logged online: Bug reference: 5235 Logged by: Oleg Yurchenko Email address: o...@fts.ee PostgreSQL version: 8.4.1 Operating system: FreeBSD 8.0-RELEASE #0 Generic kernel i386 Description:Segmentation fault under high load through JDBC Details: Postgres-8.4.1 back-end crashes with segmentation fault after 20-30 min of high load through postgres-jdbc. Tried different versions of jdbc: postgresql-8.4-701.jdbc3.jar, postgresql-8.3-605.jdbc3.jar, postgresql-jdbc-8.3.603_1 Core dump and bt are following: # gdb -c /usr/local/pgsql/data/postgres.core postgres GNU gdb 6.1.1 [FreeBSD] Copyright 2004 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "i386-marcel-freebsd"... Core was generated by `postgres'. Program terminated with signal 11, Segmentation fault. #0 0x0839380b in MemoryContextAllocZeroAligned (context=0x2d9eaf70, size=16) at mcxt.c:559 559 mcxt.c: No such file or directory. in mcxt.c [New Thread 28b01140 (LWP 100115)] #(gdb)bt #14185 0x081c1024 in ExecTargetList (targetlist=0x2b61a158, econtext=0x2b619330, values=0x2b61a0c8, isnull=0x2b61a0d8 "", itemIsDone=0x2b61a170, isDone=0xbfbfe4f0) at execQual.c:5007 #14186 0x081c14cd in ExecProject (projInfo=0x2b61a0e8, isDone=0xbfbfe4f0) at execQual.c:5222 #14187 0x081c1623 in ExecScan (node=0x2b6192a8, accessMtd=0x81d5120 ) at execScan.c:143 #14188 0x081d516a in ExecSubqueryScan (node=0x2b6192a8) at nodeSubqueryscan.c:85 #14189 0x081b7f9e in ExecProcNode (node=0x2b6192a8) at execProcnode.c:381 #14190 0x081b598e in ExecutePlan (estate=0x2b619018, planstate=0x2b6192a8, operation=CMD_SELECT, numberTuples=0, ---Type to continue, or q to quit--- direction=ForwardScanDirection, dest=0x28bc4420) at execMain.c:1504 #14191 0x081b3d53 in standard_ExecutorRun (queryDesc=0x2b789e40, direction=ForwardScanDirection, count=0) at execMain.c:309 #14192 0x081b3c66 in ExecutorRun (queryDesc=0x2b789e40, direction=ForwardScanDirection, count=0) at execMain.c:258 #14193 0x082958a7 in PortalRunSelect (portal=0x2ab68018, forward=1 '\001', count=0, dest=0x28bc4420) at pquery.c:953 #14194 0x082955c0 in PortalRun (portal=0x2ab68018, count=2147483647, isTopLevel=1 '\001', dest=0x28bc4420, altdest=0x28bc4420, completionTag=0xbfbfe7d4 "") at pquery.c:779 #14195 0x0829155f in exec_execute_message (portal_name=0x28bc4018 "", max_rows=2147483647) at postgres.c:1928 #14196 0x08293e23 in PostgresMain (argc=4, argv=0x28b3b890, username=0x28b3b7c0 "tad") at postgres.c:3671 #14197 0x0825e2d0 in BackendRun (port=0x28b2f600) at postmaster.c:3447 #14198 0x0825d7b3 in BackendStartup (port=0x28b2f600) at postmaster.c:3061 #14199 0x0825ad4a in ServerLoop () at postmaster.c:1387 #14200 0x0825a466 in PostmasterMain (argc=3, argv=0xbfbfec2c) at postmaster.c:1040 #14201 0x081ea4e5 in main (argc=3, argv=0xbfbfec2c) at main.c:188 Regards, Oleg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5235: Segmentation fault under high load through JDBC
This the end of core dump. It is 8.3M bzip-ed. I can provide it on the request. I'm trying to compile Openbravo ERP application. There is no C/Perl/Python functions. My investigations show that pgsql catches segmentation fault on some| ||ported Oracle PLSQL function|: Query in the source code is following: select instr(ad_parent_tree(?,?),'|'||?||'|') AS isItsOwnChild from dual; Best regards, Oleg. Tom Lane wrote: "Oleg Yurchenko" writes: Program terminated with signal 11, Segmentation fault. #0 0x0839380b in MemoryContextAllocZeroAligned (context=0x2d9eaf70, size=16) at mcxt.c:559 559 mcxt.c: No such file or directory. in mcxt.c [New Thread 28b01140 (LWP 100115)] #(gdb)bt #14185 0x081c1024 in ExecTargetList (targetlist=0x2b61a158, econtext=0x2b619330, values=0x2b61a0c8, isnull=0x2b61a0d8 "", itemIsDone=0x2b61a170, isDone=0xbfbfe4f0) at execQual.c:5007 #14186 0x081c14cd in ExecProject (projInfo=0x2b61a0e8, isDone=0xbfbfe4f0) at execQual.c:5222 So where are the 14184 intermediate call levels? If that's actually accurate and not a symptom of gdb being confused, it's reasonable to guess that something went into infinite recursion and the segfault occurred when it ran out of stack space. But there's no evidence here to suggest what that was. Have you got any potentially-recursive C or Perl or Python functions? Because the system ought to notice when it's getting into recursion trouble with any higher-level code. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5235: Segmentation fault under high load through JDBC
Both files are there. Oleg. Robert Haas wrote: 2009/12/8 Oleg Jurtšenko : This the end of core dump. It is 8.3M bzip-ed. I can provide it on the request. I think maybe the beginning would be more useful than the end. ...Robert -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5235: Segmentation fault under high load through JDBC
Once more: http://www.fts.ee/pgsqldebug.tgz - with loging enabled http://www.ftse.ee/pg_core.tar.bzip2 - full core dump Robert Haas wrote: 2009/12/8 Oleg Jurtšenko : Both files are there. Both files are where? I don't see an attachment or a link. ...Robert
Re: [BUGS] BUG #5235: Segmentation fault under high load through JDBC
You are right, it crushes on following statement: "select instr(ad_parent_tree(?,?),'|'||?||'|') AS isItsOwnChild from dual;" max_stack_depth is commented out, I think it has the default value: #max_stack_depth = 2MB I'm attaching related functions. Oleg Robert Haas wrote: 2009/12/8 Oleg Jurtšenko : Once more: http://www.fts.ee/pgsqldebug.tgz - with loging enabled http://www.ftse.ee/pg_core.tar.bzip2 - full core dump It looks like you've got a pl/pgsql function that called itself recursively 1417 times before running out of stack space. What do you have max_stack_depth set to? ...Robert -- Function: instr(character varying, character varying, integer, integer) -- DROP FUNCTION instr(character varying, character varying, integer, integer); CREATE OR REPLACE FUNCTION instr(string character varying, string_to_search character varying, beg_index integer, occur_index integer) RETURNS integer AS $BODY$DECLARE pos integer NOT NULL DEFAULT 0; occur_number integer NOT NULL DEFAULT 0; temp_str varchar; beg integer; i integer; length integer; ss_length integer; BEGIN IF ((string IS NULL) OR (string_to_search IS NULL) OR (beg_index IS NULL) OR (occur_index IS NULL)) THEN RETURN 0; END IF; IF beg_index > 0 THEN beg := beg_index; temp_str := substring(string FROM beg_index); FOR i IN 1..occur_index LOOP pos := position(string_to_search IN temp_str); IF i = 1 THEN beg := beg + pos - 1; ELSE beg := beg + pos; END IF; temp_str := substring(string FROM beg + 1); END LOOP; IF pos = 0 THEN RETURN 0; ELSE RETURN beg; END IF; ELSE ss_length := char_length(string_to_search); length := char_length(string); beg := length + beg_index - ss_length + 2; WHILE beg > 0 LOOP temp_str := substring(string FROM beg FOR ss_length); pos := position(string_to_search IN temp_str); IF pos > 0 THEN occur_number := occur_number + 1; IF occur_number = occur_index THEN RETURN beg; END IF; END IF; beg := beg - 1; END LOOP; RETURN 0; END IF; END; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE COST 100; ALTER FUNCTION instr(character varying, character varying, integer, integer) OWNER TO tad; -- Function: instr(character varying, character varying, integer) -- DROP FUNCTION instr(character varying, character varying, integer); CREATE OR REPLACE FUNCTION instr(string character varying, string_to_search character varying, beg_index integer) RETURNS integer AS $BODY$DECLARE pos integer NOT NULL DEFAULT 0; temp_str varchar; beg integer; length integer; ss_length integer; BEGIN IF ((string IS NULL) OR (string_to_search IS NULL) OR (beg_index IS NULL)) THEN RETURN 0; END IF; IF beg_index > 0 THEN temp_str := substring(string FROM beg_index); pos := position(string_to_search IN temp_str); IF pos = 0 THEN RETURN 0; ELSE RETURN pos + beg_index - 1; END IF; ELSE ss_length := char_length(string_to_search); length := char_length(string); beg := length + beg_index - ss_length + 2; WHILE beg > 0 LOOP temp_str := substring(string FROM beg FOR ss_length); pos := position(string_to_search IN temp_str); IF pos > 0 THEN RETURN beg; END IF; beg := beg - 1; END LOOP; RETURN 0; END IF; END; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE COST 100; ALTER FUNCTION instr(character varying, character varying, integer) OWNER TO tad; -- Function: instr(character varying, character varying) -- DROP FUNCTION instr(character varying, character varying); CREATE OR REPLACE FUNCTION instr(character varying, character varying) RETURNS integer AS $BODY$DECLARE pos integer; BEGIN pos:= instr($1, $2, 1); RETURN pos; END; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE COST 100; ALTER FUNCTION instr(character varying, character varying) OWNER TO tad; -- Function: ad_parent_tree(character varying, character varying) -- DROP FUNCTION ad_parent_tree(character varying, character varying); CREATE OR REPLACE FUNCTION ad_parent_tree(p_tree_id character varying, p_node_id character varying) RETURNS character varying AS $BODY$ DECLARE /* * The contents of this file are subject to the Openbravo Public License * Version 1.0 (the "License"), being the Mozilla Public License * Version 1.1 with a permitted attribution clause; you may not use this * file except in compliance with the License. You may obtain a copy of * the License at http://www.openbravo.com/legal/license.html * Software distributed under the License is distributed on an "AS IS" * basis, WITH
Re: [BUGS] BUG #5235: Segmentation fault under high load through JDBC
I'm not sure about the theory about recursion and infinity loop. I have tested different versions of Postgres and FreeBSD. Please take a look on results below. Well, output of "ulimit -a": $ ulimit -a cpu time (seconds, -t) unlimited file size (512-blocks, -f) unlimited data seg size (kbytes, -d) 524288 stack size (kbytes, -s) 65536 core file size (512-blocks, -c) unlimited max memory size (kbytes, -m) unlimited locked memory (kbytes, -l) unlimited max user processes (-u) 4986 open files (-n) 9972 virtual mem size(kbytes, -v) unlimited swap limit (kbytes, -w) unlimited sbsize (bytes, -b) unlimited pseudo-terminals(-p) unlimited Output of "SHOW max_stack_depth;" postgres=# SHOW max_stack_depth; max_stack_depth - 2MB (1 row) I tried to execute "select instr(ad_parent_tree(?,?),'|'||'?'||'|') AS isItsOwnChild from dual;" query with psql terminal and got segmentation fault as well. The most interesting thing is that this function makes segmentation fault also on FreeBSD 7.2 with Postgresql-8.3.7. Consequentially, both Postgresql-8.3.7 and Postresql-8.4.1 are affected. Oleg. Tom Lane wrote: Robert Haas writes: 2009/12/8 Oleg Jurtšenko : You are right, it crushes on following statement: "select instr(ad_parent_tree(?,?),'|'||?||'|') AS isItsOwnChild from dual;" max_stack_depth is commented out, I think it has the default value: #max_stack_depth = 2MB Well, my guess is you have your kernel limit for max stack depth set to something very small. See: http://www.postgresql.org/docs/current/interactive/runtime-config-resource.html#GUC-MAX-STACK-DEPTH You can do "SHOW max_stack_depth;" to confirm the setting for that parameter. But I'm not quite sure how to check what value is being applied to PG. Sounds like it's smaller than 2MB, though. You may be able to reduce max_stack_depth to prevent the crash, but then you'll get an error instead. The weird thing about this is that recent versions of PG try to adjust max_stack_depth automatically. The only ways I can see for that to fail is if (1) the platform hasn't got getrlimit(RLIMIT_STACK), or (2) the effective stack rlimit is so tiny Postgres doesn't believe it, which looks to be anything under 100KB. The claim in the docs that the default value is 2MB is a vast oversimplification of reality, so I'd be interested to know what "show max_stack_depth" actually reports. It'd also be useful to run "ulimit -a" in the context in which the postmaster is normally started (that's NOT your interactive shell session, usually --- try adding that to the postmaster start script). regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5235: Segmentation fault under high load through JDBC
Functions are attached
Oleg
Andrew Gierth wrote:
"Oleg" == Oleg Jurtšenko writes:
Oleg> I tried to execute "select instr(ad_parent_tree(?,?),'|'||'?'||'|') AS
Oleg> isItsOwnChild from dual;" query with psql terminal and got
Oleg> segmentation fault as well.
Oleg> The most interesting thing is that this function makes segmentation
Oleg> fault also on FreeBSD 7.2 with Postgresql-8.3.7.
What are the definitions of your instr() and ad_parent_tree() functions?
-- Function: instr(character varying, character varying, integer, integer)
-- DROP FUNCTION instr(character varying, character varying, integer, integer);
CREATE OR REPLACE FUNCTION instr(string character varying, string_to_search
character varying, beg_index integer, occur_index integer)
RETURNS integer AS
$BODY$DECLARE
pos integer NOT NULL DEFAULT 0;
occur_number integer NOT NULL DEFAULT 0;
temp_str varchar;
beg integer;
i integer;
length integer;
ss_length integer; BEGIN
IF ((string IS NULL) OR (string_to_search IS NULL) OR (beg_index IS NULL)
OR (occur_index IS NULL)) THEN RETURN 0; END IF;
IF beg_index > 0 THEN
beg := beg_index;
temp_str := substring(string FROM beg_index);
FOR i IN 1..occur_index LOOP
pos := position(string_to_search IN temp_str);
IF i = 1 THEN
beg := beg + pos - 1;
ELSE
beg := beg + pos;
END IF;
temp_str := substring(string FROM beg + 1);
END LOOP;
IF pos = 0 THEN
RETURN 0;
ELSE
RETURN beg;
END IF;
ELSE
ss_length := char_length(string_to_search);
length := char_length(string);
beg := length + beg_index - ss_length + 2;
WHILE beg > 0 LOOP
temp_str := substring(string FROM beg FOR ss_length);
pos := position(string_to_search IN temp_str);
IF pos > 0 THEN
occur_number := occur_number + 1;
IF occur_number = occur_index THEN
RETURN beg;
END IF;
END IF;
beg := beg - 1;
END LOOP;
RETURN 0;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE
COST 100;
ALTER FUNCTION instr(character varying, character varying, integer, integer)
OWNER TO tad;
-- Function: instr(character varying, character varying, integer)
-- DROP FUNCTION instr(character varying, character varying, integer);
CREATE OR REPLACE FUNCTION instr(string character varying, string_to_search
character varying, beg_index integer)
RETURNS integer AS
$BODY$DECLARE
pos integer NOT NULL DEFAULT 0;
temp_str varchar;
beg integer;
length integer;
ss_length integer;
BEGIN
IF ((string IS NULL) OR (string_to_search IS NULL) OR (beg_index IS NULL))
THEN RETURN 0; END IF;
IF beg_index > 0 THEN
temp_str := substring(string FROM beg_index);
pos := position(string_to_search IN temp_str);
IF pos = 0 THEN
RETURN 0;
ELSE
RETURN pos + beg_index - 1;
END IF;
ELSE
ss_length := char_length(string_to_search);
length := char_length(string);
beg := length + beg_index - ss_length + 2;
WHILE beg > 0 LOOP
temp_str := substring(string FROM beg FOR ss_length);
pos := position(string_to_search IN temp_str);
IF pos > 0 THEN
RETURN beg;
END IF;
beg := beg - 1;
END LOOP;
RETURN 0;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE
COST 100;
ALTER FUNCTION instr(character varying, character varying, integer) OWNER TO
tad;
-- Function: instr(character varying, character varying)
-- DROP FUNCTION instr(character varying, character varying);
CREATE OR REPLACE FUNCTION instr(character varying, character varying)
RETURNS integer AS
$BODY$DECLARE
pos integer;
BEGIN
pos:= instr($1, $2, 1);
RETURN pos;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE
COST 100;
ALTER FUNCTION instr(character varying, character varying) OWNER TO tad;
-- Function: ad_parent_tree(character varying, character varying)
-- DROP FUNCTION ad_parent_tree(character varying, character varying);
CREATE OR REPLACE FUNCTION ad_parent_tree(p_tree_id character varying,
p_node_id character varying)
RETURNS character varying AS
$BODY$ DECLARE
/*
* The contents of this file are subject to the Openbravo Public License
* Version 1.0 (the "License"), being the Mozilla Public License
* Version 1.1 with a permitted attribution clause; you may not use this
* file except in compliance with the License. You may obtain a copy of
* the License at http://www.openbravo.com/legal/license.html
* Software distributed under the License is distributed on an "AS IS"
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
* License for the specific language g
Re: [BUGS] BUG #5235: Segmentation fault under high load through JDBC
After upgrading to the latest patch level: PostgreSQL 8.4.2 on i386-portbld-freebsd8.0 I have got a different core dump (gdb) bt #0 0x328b1068 in malloc () from /lib/libc.so.7 #1 0x082f1be9 in load_tzoffsets () #2 0x080aa5ef in btrescan () #3 0x082db80c in FunctionCall2 () #4 0x080a4c15 in index_rescan () #5 0x080a44a2 in index_getprocid () #6 0x080aa662 in btgetbitmap () #7 0x082db759 in FunctionCall3 () #8 0x080a4f03 in index_endscan () #9 0x080a4f84 in index_insert () #10 0x08193881 in ExecIndexRestrPos () #11 0x34f14af8 in ?? () #12 0x37715f40 in ?? () #13 0x0002 in ?? () #14 0x37da5e58 in ?? () #15 0x37da51f8 in ?? () #16 0x37da51fc in ?? () #17 0x37da5200 in ?? () #18 0x in ?? () #19 0x in ?? () #20 0x0100 in ?? () #21 0x355a5c88 in ?? () #22 0x0002 in ?? () #23 0x in ?? () #24 0xbfa00608 in ?? () #25 0x0818382b in ExecInitNode () which gave me an idea to tune "max_stack_depth". I increased it four times from 2MB to 8MB and tried to run my test query: "select instr(ad_parent_tree(?,?),'|'||?||'|') AS isItsOwnChild from dual;" Execution was successful without segfault, but it took 25 seconds to get a result. Regards, Oleg Tom Lane wrote: Andrew Gierth writes: Still, even though the code is preposterous, the result shouldn't be a segfault. I wasn't able to reproduce one myself (using 8.3.7 on freebsd 7.2) however. Yeah, for me it also recurses till the exception is hit, and then processes that successfully. This is effectively identical to a case in the standard regression tests, which also intentionally recurses till stack overflow. Since we have FreeBSD machines in the buildfarm, it is reasonably safe to conclude that this isn't a generic FreeBSD bug. I suspect the OP has used some unusual configure/build option or linked in some nonstandard code that is causing the available stack space to change unexpectedly. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5314: Error in nested composite types in plpgsql.
Somebody will fix this bug or not?
On Thu, Feb 4, 2010 at 7:13 PM, Oleg wrote:
>
> The following bug has been logged online:
>
> Bug reference: 5314
> Logged by: Oleg
> Email address: sero...@gmail.com
> PostgreSQL version: 8.3/8.4
> Operating system: any
> Description:Error in nested composite types in plpgsql.
> Details:
>
> Here is it reproduce code:
> It works only, when procedure is plpgsql, with sql works fine.
>
> ROLLBACK;
> BEGIN;
> CREATE TABLE bug_level_tree(
>field BIGINT
> );
> CREATE TABLE bug_level_two(
>field bug_level_tree
> );
> CREATE TABLE bug_level_one(
>id BIGINT,
>field bug_level_two
> );
> CREATE FUNCTION bug_procedure(in_row bug_level_one) RETURNS text AS $$
> BEGIN
>-- void
>SELECT 1/0;
> END;
> $$ LANGUAGE plpgsql;
>
> -- All okey
> SELECT '(1,)'::bug_level_one;
>
> -- Throws error
> SELECT bug_procedure('(1,)');
>
> -- ERROR: cannot assign non-composite value to a row variable
> CONTEXT: PL/pgSQL function "bug_procedure" while storing call arguments
> into local variables
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
--
С уважением
Олег Серов
Re: [BUGS] BUG #5314: Error in nested composite types in plpgsql.
When it could be fixed? On Thu, Feb 11, 2010 at 9:58 PM, Tom Lane wrote: > Robert Haas writes: >> 2010/2/10 Oleg Serov : >>> Somebody will fix this bug or not? > >> I'm not sure whether this is a bug. > > Yeah, I think it is. The problem is that exec_move_row is taking too > many shortcuts with nulls. If the input record is short of fields it > is willing to pass this data to exec_assign_value: > > value = (Datum) 0; > isnull = true; > valtype = InvalidOid; > > The invalid datatype value doesn't matter in the scalar case, but > if the target is a sub-row it fails the type_is_rowtype() sanity > check in exec_assign_value. > > The cleanest fix would probably be to use the target variable's > datatype here instead of InvalidOid. Alternatively, we could > change exec_assign_value to not apply the sanity check unless > the input is non-null. > > regards, tom lane > -- С уважением Олег Серов -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5314: Error in nested composite types in plpgsql.
Thanks!, when it will be released on 8.3.X? On Wed, Feb 24, 2010 at 6:17 PM, Tom Lane wrote: > Oleg Serov writes: > > When it could be fixed? > > Oh, it is fixed, but I forgot to reply to this thread about it. > Sorry about that. > >regards, tom lane > -- С уважением Олег Серов
Re: [BUGS] BUG #4673: pl/PgSQL: Bug, when updating changed composite types.
Hello!? anybody will fix this bug? Hey! On Mon, Feb 23, 2009 at 5:11 PM, Oleg wrote: > > The following bug has been logged online: > > Bug reference: 4673 > Logged by: Oleg > Email address: sero...@gmail.com > PostgreSQL version: 8.3.6 > Operating system: i686-redhat-linux-gnu > Description:pl/PgSQL: Bug, when updating changed composite types. > Details: > > If you added a field into composite type(table type), plpgsql won't update > it, until you recompile function. > > DEMO SQL: > > ROLLBACK; > BEGIN; > CREATE TABLE bug_composite ( >column_a INT, >column_b INT > ); > > CREATE TABLE bug_parent ( >id INT, >info bug_composite > ); > > INSERT INTO bug_parent(id, info) VALUES (1, ROW(1, 2)); > > CREATE OR REPLACE FUNCTION bug_update(r bug_parent) RETURNS void > AS > $BODY$ > BEGIN >UPDATE bug_parent >SET info = r.info >WHERE id = r.id; > END; > $BODY$ LANGUAGE 'plpgsql' VOLATILE; > > SELECT bug_update(ROW(1, ROW(3, 5))); > > SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(3, 5)); > > ALTER TABLE bug_composite ADD COLUMN column_c INT; > > SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(3, 5, NULL)); > > SELECT bug_update(ROW(1, ROW(4, 6, 9))); > > SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(4, 6, NULL)); > > SELECT version(); -- "PostgreSQL 8.3.6 on i686-redhat-linux-gnu, compiled > by > GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)" > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- С уважением Олег Серов
Re: [BUGS] Bug in procedure When you modificate table
Hey, anybody will answer here?
2008/7/4 Oleg Serov
> SQL BUG CODE:
> BEGIN;
> SELECT version(); -- "PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled
> by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)"
> CREATE TYPE "buggy_enum_first" AS ENUM ( 'bug1', 'bug2', 'bug3' );
>
> CREATE TABLE "bug_table" (
> "id" BIGINT NOT NULL,
> "buggy_enum_field" "buggy_enum_first" DEFAULT 'bug1'::buggy_enum_first
> NOT NULL,
> CONSTRAINT "test_table_pkey" PRIMARY KEY("id")
> ) WITHOUT OIDS;
>
>
> CREATE FUNCTION buggy_procedure() RETURNS SETOF bug_table
> AS $$
> BEGIN
> -- @todo hide password
> RETURN QUERY (
> SELECT *
> FROM bug_table
> );
> END;
> $$
> LANGUAGE plpgsql STRICT SECURITY DEFINER;
>
> SELECT * FROM buggy_procedure(); -- All Okey
> DROP TYPE buggy_enum_first CASCADE;
> CREATE TYPE "buggy_enum_second" AS ENUM ( 'bug1', 'bug2', 'bug3' );
> ALTER TABLE bug_table ADD COLUMN buggy_enum_field buggy_enum_second;
> SELECT * FROM buggy_procedure(); -- Bug
> ROLLBACK;
> /*NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "test_table_pkey" for table "bug_table"Результат запроса с отброшенным
> числом строк: 1.
>
> NOTICE: drop cascades to default for table bug_table column
> buggy_enum_fieldNOTICE: drop cascades to table bug_table column
> buggy_enum_field
> ERROR: structure of query does not match function result type
> CONTEXT: PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/
>
>
>
>
>
--
С уважением
Олег Серов
Re: [BUGS] Bug in triggers
Up!, Anybody will answer on this bugreport?
On Fri, Sep 26, 2008 at 2:57 PM, Oleg Serov wrote:
> Sorry, bug is not in triggers, it is in PL/PGSQL var assign mechanism
> here it is an example:
> ROLLBACK;
> BEGIN;
>
> CREATE TYPE "composite_type" AS (
> "type" VARCHAR,
>"type2" VARCHAR
> );
>
>
> CREATE TABLE "buggy" (
>"id" BIGINT NOT NULL,
>"bug" "composite_type",
>CONSTRAINT "buggy_pkey" PRIMARY KEY("id")
> ) WITH OIDS;
>
>
> CREATE OR REPLACE FUNCTION "test_bug" () RETURNS pg_catalog.void AS
> $body$
> DECLARE
>tmp_old buggy%rowtype;
> BEGIN
>tmp_old := ROW(1, NULL)::buggy;
>IF tmp_old::text <> ROW(1, NULL)::buggy::text THEN
>RAISE EXCEPTION '% <> %', tmp_old, ROW(1, NULL)::buggy;
>END IF;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> WILL THROW A EXCEPTION:
> ERROR: (1,"(,)") <> (1,)
>
>
> SELECT test_bug();
>
>
> 2008/9/26, Oleg Serov :
> > SQL code:
> >
> >
> > ROLLBACK;
> > BEGIN;
> > CREATE TYPE "composite_type" AS (
> > "typename" VARCHAR
> > );
> >
> >
> > CREATE TABLE "buggy" (
> > "id" BIGINT NOT NULL,
> > "bug" "composite_type",
> > CONSTRAINT "buggy_pkey" PRIMARY KEY("id")
> > ) WITH OIDS;
> >
> > INSERT INTO buggy (id, bug) VALUES
> > (100196418052926086, NULL);
> >
> > CREATE OR REPLACE FUNCTION "public"."test_bug" () RETURNS trigger AS
> > $body$
> > DECLARE
> > tmp_old buggy%rowtype;
> > tmp_new buggy%rowtype;
> > BEGIN
> > RAISE NOTICE 'OLD: %', OLD;
> > RAISE NOTICE 'NEW: %', NEW;
> >
> > tmp_old := OLD;
> > RAISE NOTICE 'TMP OLD: %', tmp_old;
> >
> > RAISE NOTICE 'TMP OLD = OLD => %', tmp_old::text = OLD::text;
> >
> > tmp_old.id := NEW.id;
> > tmp_new := NEW;
> >
> > RAISE NOTICE 'TMP OLD: %', tmp_old;
> > RAISE NOTICE 'TMP NEW: %', tmp_new;
> >
> > RAISE NOTICE 'TMP OLD = TMP NEW => %', tmp_old::text = tmp_new::text;
> > RAISE NOTICE 'TMP OLD = NEW => %', tmp_old::text = NEW::text;
> >
> >
> >
> > IF (tmp_old::text <> tmp_new::text) <> (tmp_old::text <>
> NEW::text)
> > THEN
> > RAISE EXCEPTION 'PGSQL BUG!';
> > END IF;
> > RETURN OLD;
> > END;
> > $body$
> > LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
> >
> > CREATE TRIGGER "t_bug" BEFORE UPDATE
> > ON buggy FOR EACH ROW
> > EXECUTE PROCEDURE "test_bug"();
> >
> >
> > UPDATE buggy SET id = 100112779830304388 WHERE 100196418052926086 =
> id;
> > /**
> > NOTICE: OLD: (100196418052926086,)
> > NOTICE: NEW: (100112779830304388,)
> > NOTICE: TMP OLD: (100196418052926086,"()")
> > NOTICE: TMP OLD = OLD => f
> > NOTICE: TMP OLD: (100112779830304388,"()")
> > NOTICE: TMP NEW: (100112779830304388,"()")
> > NOTICE: TMP OLD = TMP NEW => t
> > NOTICE: TMP OLD = NEW => f -- BUG!!!
> >
> > **/
> >
>
--
С уважением
Олег Серов
Re: [BUGS] Bug in PL/PgSQL "SELECT .. INTO" statement parser
Up. Anybody will answer on this bug report? 2009/1/21 Oleg Serov > Sorry, but is not important, i forgot to remove original table name > "chunk_ad", but is not affected to the bug.. > > 2009/1/21 Oleg Serov : > > Here is an example: > > > > CREATE TABLE test2 ( > >id BIGINT, > >chunk_id BIGINT > > ); > > CREATE TABLE test1 ( > >id BIGINT > > ); > > > > CREATE OR REPLACE FUNCTION "bug" () RETURNS pg_catalog.void AS > > $body$ > > DECLARE > >row_test1 test1%rowtype; > >row_test2 test2%rowtype; > > BEGIN > >SELECT test1, chunk_ad > >FROM test1 JOIN test2 ON(chunk.id = test2.chunk_id) > >LIMIT 1 > >INTO row_test1, row_test2; > > > > END; > > $body$ > > LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER; > > > > Will throw error: > > ERROR: LIMIT #,# syntax is not supported > > HINT: Use separate LIMIT and OFFSET clauses. > > QUERY: SELECT test1, chunk_ad FROM test1 JOIN test2 ON(chunk.id = > > test2.chunk_id) LIMIT 1, 0, $1 > > CONTEXT: SQL statement in PL/PgSQL function "bug" near line 8 > > > > ** Ошибка ** > > > > ERROR: LIMIT #,# syntax is not supported > > SQL state: 42601 > > Подсказка:Use separate LIMIT and OFFSET clauses. > > Контекст:SQL statement in PL/PgSQL function "bug" near line 8 > > > -- С уважением Олег Серов
Re: [BUGS] Diffrent column ordering after dump/restore tables with INHERITS
Up! Anybody will answer about the patch? On Mon, Jul 6, 2009 at 11:20 AM, Oleg Serov wrote: > How about adding this patch to postgresql it will slove the problem? > > On Sun, Jul 5, 2009 at 8:10 PM, Greg Stark wrote: > > On Sun, Jul 5, 2009 at 4:28 PM, Tom Lane wrote: > >> when i done dump->restore i > >>> have surprise, > >>> Column ordering was changed. > >> > >> This is not a bug, it's the intended behavior. > > > > I thought that was a bug, just one that was too hard to fix for the > > problems it caused. It might be more fixable if we get around to the > > work that was discussed earlier where we separate attnum into three > > different values. > > > > Oleg: note that having the columns in the same position allows some > > optimizations in the executor so it's probably a good thing if it > > hasn't broken your application. > > > > -- > > greg > > http://mit.edu/~gsstark/resume.pdf<http://mit.edu/%7Egsstark/resume.pdf> > > > -- С уважением Олег Серов
Re: [BUGS] Diffrent column ordering after dump/restore tables with INHERITS
So there are no simple way to do it right, and it will be not fixed? Will this bug appear in todo list? On Fri, Feb 26, 2010 at 6:13 PM, Greg Stark wrote: > 2010/2/26 Oleg Serov : > > Up! Anybody will answer about the patch? > > The patch causes the inheritance history to be lost. If you > subsequently drop the column form the parent it'll be kept on the > child because it was explicitly declared when you created the child. > In the original structure if you dropped the column from the parent it > would be dropped from the child because it was an inherited column. > > Inheritance is in a kind of no-mans land. It's not good enough to be > an important feature anyone cares enough about to make it work > properly and it's not shoddy enough that it's worth removing. I'm sure > there are people using it effectively despite the caveats and rough > edges. > > -- > greg > -- С уважением Олег Серов
Re: [BUGS] Diffrent column ordering after dump/restore tables with INHERITS
On Fri, Feb 26, 2010 at 9:29 PM, Tom Lane wrote: > Oleg Serov writes: > > So there are no simple way to do it right, and it will be not fixed? Will > > this bug appear in todo list? > > It's not a bug, it's just what happens when you make the parent and > It is a bug. If i'm doing dump restore i must have _same_ database structure as i dumped. Yes? > child column orders inconsistent. Would you prefer that we restricted > ALTER TABLE to refuse to perform the alteration in the first place? > No! it is not right. We must think how to solve this problem correct. >regards, tom lane > -- С уважением Олег Серов
Re: [BUGS] Diffrent column ordering after dump/restore tables with INHERITS
I'm think you should add some abstract-layer for handling column ordering not as they stored at disk. It is possible? On Fri, Feb 26, 2010 at 10:32 PM, Alvaro Herrera wrote: > Tom Lane escribió: > > Oleg Serov writes: > > > So there are no simple way to do it right, and it will be not fixed? > Will > > > this bug appear in todo list? > > > > It's not a bug, it's just what happens when you make the parent and > > child column orders inconsistent. Would you prefer that we restricted > > ALTER TABLE to refuse to perform the alteration in the first place? > > Possibly the right fix is to implement the oft-discussed idea about > columns able to be moved around in table definitions. That would let > ALTER TABLE ADD COLUMN to put the column in the right positions in > children. > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > -- С уважением Олег Серов
Re: [BUGS] Bug in PL/PgSQL "SELECT .. INTO" statement parser
Submitted as #*5352 bug.* 2010/2/26 Oleg Serov > Up. Anybody will answer on this bug report? > > > 2009/1/21 Oleg Serov > >> Sorry, but is not important, i forgot to remove original table name >> "chunk_ad", but is not affected to the bug.. >> >> 2009/1/21 Oleg Serov : >> > Here is an example: >> > >> > CREATE TABLE test2 ( >> >id BIGINT, >> >chunk_id BIGINT >> > ); >> > CREATE TABLE test1 ( >> >id BIGINT >> > ); >> > >> > CREATE OR REPLACE FUNCTION "bug" () RETURNS pg_catalog.void AS >> > $body$ >> > DECLARE >> >row_test1 test1%rowtype; >> >row_test2 test2%rowtype; >> > BEGIN >> >SELECT test1, chunk_ad >> >FROM test1 JOIN test2 ON(chunk.id = test2.chunk_id) >> >LIMIT 1 >> >INTO row_test1, row_test2; >> > >> > END; >> > $body$ >> > LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER; >> > >> > Will throw error: >> > ERROR: LIMIT #,# syntax is not supported >> > HINT: Use separate LIMIT and OFFSET clauses. >> > QUERY: SELECT test1, chunk_ad FROM test1 JOIN test2 ON(chunk.id = >> > test2.chunk_id) LIMIT 1, 0, $1 >> > CONTEXT: SQL statement in PL/PgSQL function "bug" near line 8 >> > >> > ** Ошибка ** >> > >> > ERROR: LIMIT #,# syntax is not supported >> > SQL state: 42601 >> > Подсказка:Use separate LIMIT and OFFSET clauses. >> > Контекст:SQL statement in PL/PgSQL function "bug" near line 8 >> > >> > > > > -- > С уважением > > Олег Серов > -- С уважением Олег Серов
Re: [BUGS] Bug in procedure When you modificate table
submitted as #5353
2010/2/26 Oleg Serov
> Hey, anybody will answer here?
>
> 2008/7/4 Oleg Serov
>
> SQL BUG CODE:
>> BEGIN;
>> SELECT version(); -- "PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled
>> by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)"
>> CREATE TYPE "buggy_enum_first" AS ENUM ( 'bug1', 'bug2', 'bug3' );
>>
>> CREATE TABLE "bug_table" (
>> "id" BIGINT NOT NULL,
>> "buggy_enum_field" "buggy_enum_first" DEFAULT 'bug1'::buggy_enum_first
>> NOT NULL,
>> CONSTRAINT "test_table_pkey" PRIMARY KEY("id")
>> ) WITHOUT OIDS;
>>
>>
>> CREATE FUNCTION buggy_procedure() RETURNS SETOF bug_table
>> AS $$
>> BEGIN
>> -- @todo hide password
>> RETURN QUERY (
>> SELECT *
>> FROM bug_table
>> );
>> END;
>> $$
>> LANGUAGE plpgsql STRICT SECURITY DEFINER;
>>
>> SELECT * FROM buggy_procedure(); -- All Okey
>> DROP TYPE buggy_enum_first CASCADE;
>> CREATE TYPE "buggy_enum_second" AS ENUM ( 'bug1', 'bug2', 'bug3' );
>> ALTER TABLE bug_table ADD COLUMN buggy_enum_field buggy_enum_second;
>> SELECT * FROM buggy_procedure(); -- Bug
>> ROLLBACK;
>> /*NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
>> "test_table_pkey" for table "bug_table"Результат запроса с отброшенным
>> числом строк: 1.
>>
>> NOTICE: drop cascades to default for table bug_table column
>> buggy_enum_fieldNOTICE: drop cascades to table bug_table column
>> buggy_enum_field
>> ERROR: structure of query does not match function result type
>> CONTEXT: PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/
>>
>>
>>
>>
>>
>
>
> --
> С уважением
>
> Олег Серов
>
--
С уважением
Олег Серов
Re: [BUGS] Bug in triggers
2010/3/1 Robert Haas
>
> It's not obvious whether this is the same as one of the various other
> problems you've complained about. If it isn't, an English description
> of what you think the problem is would probably improve your odds.
> See also:
>
> http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
>
> ...Robert
Thanks! This was long time ago, so i reposted it due empty responses.
i think this problem already discussed by Tom Lane, it is about "Row of
nulls OR null row", but i couldn't find this thread in archive.
So if you have null row in plpgsql and assign it to plpgsql var it will be
translated to row of nulls instead null row.
Here it is an example:
It is assign with direct function call:
> CREATE TYPE "type_subrow" AS (
> "typename" VARCHAR
> );
> CREATE TYPE "type_row" AS (
> "typename" VARCHAR,
> "subrow" type_subrow
> );
>
> CREATE OR REPLACE FUNCTION "test_bug"(in_row type_row) RETURNS void AS
> $body$
> DECLARE
> var type_row%rowtype;
> BEGIN
> var := in_row;
> RAISE NOTICE 'Original value: %', in_row;
> RAISE NOTICE 'Assigned value: %', var;
>
> IF var::TEXT <> in_row::TEXT THEN
> RAISE EXCEPTION 'var is not equals in_row';
> END IF;
> END;
> $body$
> LANGUAGE 'plpgsql';
>
> SELECT test_bug('("Test",)'::type_row);
>
Will output:
NOTICE: Original value: (Test,"()")
> NOTICE: Assigned value: (Test,"()")
>
As you see - subrow of type row is not null, it is ROW(NULL).
Now see how it will be in trigger:
ROLLBACK;
> BEGIN;
>
> CREATE TYPE "type_subrow" AS (
> "typename" VARCHAR
> );
> CREATE TABLE "type_row" (
> "typename" VARCHAR,
> "subrow" type_subrow
> );
>
> CREATE OR REPLACE FUNCTION "test_bug"() RETURNS trigger AS
> $body$
> DECLARE
> var type_row%rowtype;
> BEGIN
> var := NEW;
> RAISE NOTICE 'Original value: %', NEW;
> RAISE NOTICE 'Assigned value: %', var;
>
> IF var::TEXT <> NEW::TEXT THEN
> RAISE NOTICE 'var is not equals NEW';
> END IF;
>
> RETURN NEW;
> END;
> $body$
> LANGUAGE 'plpgsql';
>
> CREATE TRIGGER "t_bug" BEFORE INSERT
> ON type_row FOR EACH ROW
> EXECUTE PROCEDURE "test_bug"();
>
> INSERT INTO type_row VALUES('Test', NULL);
>
Will output:
NOTICE: Original value: (Test,)
> NOTICE: Assigned value: (Test,"()")
> NOTICE: var is not equals NEW
>
As you see - NEW.subrow is null.
But var.subrow is not null, it is ROW(NULL).
Do you understand what is the problem?
>
> 2010/2/26 Oleg Serov :
> > Up!, Anybody will answer on this bugreport?
> >
> > On Fri, Sep 26, 2008 at 2:57 PM, Oleg Serov wrote:
> >>
> >> Sorry, bug is not in triggers, it is in PL/PGSQL var assign mechanism
> >> here it is an example:
> >> ROLLBACK;
> >> BEGIN;
> >>
> >> CREATE TYPE "composite_type" AS (
> >>"type" VARCHAR,
> >>"type2" VARCHAR
> >> );
> >>
> >>
> >> CREATE TABLE "buggy" (
> >>"id" BIGINT NOT NULL,
> >>"bug" "composite_type",
> >>CONSTRAINT "buggy_pkey" PRIMARY KEY("id")
> >> ) WITH OIDS;
> >>
> >>
> >> CREATE OR REPLACE FUNCTION "test_bug" () RETURNS pg_catalog.void AS
> >> $body$
> >> DECLARE
> >>tmp_old buggy%rowtype;
> >> BEGIN
> >>tmp_old := ROW(1, NULL)::buggy;
> >>IF tmp_old::text <> ROW(1, NULL)::buggy::text THEN
> >>RAISE EXCEPTION '% <> %', tmp_old, ROW(1, NULL)::buggy;
> >>END IF;
> >> END;
> >> $body$
> >> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
> >>
> >> WILL THROW A EXCEPTION:
> >> ERROR: (1,"(,)") <> (1,)
> >>
> >>
> >> SELECT test_bug();
> >>
> >>
> >> 2008/9/26, Oleg Serov :
> >> > SQL code:
> >> >
> >> >
> >> > ROLLBACK;
> >> > BEGIN;
> >> > CREATE TYPE "composite_type" AS (
> >> > "typename" VARCHAR
> >> > );
> >>
Re: [BUGS] Bug in triggers
I'm asking to fix this =)
On Wed, Mar 3, 2010 at 9:53 PM, Robert Haas wrote:
> 2010/3/3 Oleg Serov :
> >
> >
> > 2010/3/1 Robert Haas
> >>
> >> It's not obvious whether this is the same as one of the various other
> >> problems you've complained about. If it isn't, an English description
> >> of what you think the problem is would probably improve your odds.
> >> See also:
> >>
> >> http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
> >>
> >> ...Robert
> >
> > Thanks! This was long time ago, so i reposted it due empty responses.
> > i think this problem already discussed by Tom Lane, it is about "Row of
> > nulls OR null row", but i couldn't find this thread in archive.
> >
> > So if you have null row in plpgsql and assign it to plpgsql var it will
> be
> > translated to row of nulls instead null row.
> > Here it is an example:
> > It is assign with direct function call:
> >>
> >> CREATE TYPE "type_subrow" AS (
> >> "typename" VARCHAR
> >> );
> >> CREATE TYPE "type_row" AS (
> >> "typename" VARCHAR,
> >> "subrow" type_subrow
> >> );
> >>
> >> CREATE OR REPLACE FUNCTION "test_bug"(in_row type_row) RETURNS void AS
> >> $body$
> >> DECLARE
> >> var type_row%rowtype;
> >> BEGIN
> >> var := in_row;
> >> RAISE NOTICE 'Original value: %', in_row;
> >> RAISE NOTICE 'Assigned value: %', var;
> >>
> >> IF var::TEXT <> in_row::TEXT THEN
> >> RAISE EXCEPTION 'var is not equals in_row';
> >> END IF;
> >> END;
> >> $body$
> >> LANGUAGE 'plpgsql';
> >>
> >> SELECT test_bug('("Test",)'::type_row);
> >
> > Will output:
> >
> >> NOTICE: Original value: (Test,"()")
> >> NOTICE: Assigned value: (Test,"()")
> >
> > As you see - subrow of type row is not null, it is ROW(NULL).
> >
> > Now see how it will be in trigger:
> >
> >> ROLLBACK;
> >> BEGIN;
> >>
> >> CREATE TYPE "type_subrow" AS (
> >> "typename" VARCHAR
> >> );
> >> CREATE TABLE "type_row" (
> >> "typename" VARCHAR,
> >> "subrow" type_subrow
> >> );
> >>
> >> CREATE OR REPLACE FUNCTION "test_bug"() RETURNS trigger AS
> >> $body$
> >> DECLARE
> >> var type_row%rowtype;
> >> BEGIN
> >> var := NEW;
> >> RAISE NOTICE 'Original value: %', NEW;
> >> RAISE NOTICE 'Assigned value: %', var;
> >>
> >> IF var::TEXT <> NEW::TEXT THEN
> >> RAISE NOTICE 'var is not equals NEW';
> >> END IF;
> >>
> >> RETURN NEW;
> >> END;
> >> $body$
> >> LANGUAGE 'plpgsql';
> >>
> >> CREATE TRIGGER "t_bug" BEFORE INSERT
> >> ON type_row FOR EACH ROW
> >> EXECUTE PROCEDURE "test_bug"();
> >>
> >> INSERT INTO type_row VALUES('Test', NULL);
> >
> > Will output:
> >
> >> NOTICE: Original value: (Test,)
> >> NOTICE: Assigned value: (Test,"()")
> >> NOTICE: var is not equals NEW
> >
> > As you see - NEW.subrow is null.
> > But var.subrow is not null, it is ROW(NULL).
> >
> > Do you understand what is the problem?
>
> It does seem weird that assigning NEW to var changes the value; I'm
> not sure why that happens. Is that what you're asking about?
>
> ...Robert
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
--
С уважением
Олег Серов
Re: [BUGS] Bug in triggers
On Sat, Mar 6, 2010 at 2:12 AM, Chris Travers wrote: > On Fri, Mar 5, 2010 at 2:32 PM, Tom Lane wrote: > > Robert Haas writes: > >>> On Wed, Mar 3, 2010 at 9:53 PM, Robert Haas > wrote: > >>> It does seem weird that assigning NEW to var changes the value; I'm > >>> not sure why that happens. Is that what you're asking about? > > > >> Anyone else have an opinion on whether this is a bug? > > > > It's arguably a bug, but since we lack consensus on whether NULL and > > ROW(NULL,NULL,...) are the same thing, it's difficult to make a > > bulletproof case either way. In any case nothing is likely to get done > > about it in the near term because it's wired into plpgsql's > > implementation. Changing from row to record representation of such > > variables is possible but would probably have side effects, ie, it would > > create new compatibility issues of unknown seriousness. I'm not too > > optimistic about the performance implications either. > > I don't know if it is a bug. Different textual representations could > easily happen due to intermediate conversions of datatypes > > For example: I wouldn't expect timestamp::date::text to equal > timestamp::text. Textual representations are not necessarily > consistent. > > I guess a better question for Oleg might be: > > "Why is it important to you to get this fixed? What are you trying to > do that you can't do without fixing this?" > This bug is not critical, i'm comparing two rows with single structure. and i cast it to text and compare. > > Best Wishes, > Chris Travers > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- С уважением Олег Серов
[BUGS] 9.0_alpha4 Planner Bug
Hello, My OS is Gentoo Linux 2.6.32.10 x86_64 Postgre:# select version(); PostgreSQL 9.0alpha4 on x86_64-pc-linux-gnu, compiled by GCC x86_64-pc-linux-gnu-gcc (Gentoo 4.3.4 p1.0, pie-10.1.5) 4.3.4, 64-bit There is a regression, in some query that was working fine in Postgre 8.4.3 and became broken after upgrade the details are here http://pastebin.ca/1848469 basically, i have 2 tables cjw.service and cjw.company_to_service when i run query that left-joins them and produces a boolean constant named "linked" on successful join SELECT service_nameAS name -- , com_serv.service::boolAS linked , linked AS linked FROM cjw.service LEFT JOIN ( SELECT service , true as linked FROM cjw.company_to_service WHERE company = 3 ) AS com_serv USING (service) ORDER BY service_name so when I select that constant by it's name - the planner completely ignores join and returns "true" for every row, even though that is wrong and that row could not be joined and if i select, a real value from joined part - the planner performs as expected and returns valid result. please checkout pastebin url http://pastebin.ca/1848469 i included descriptions of tables and ANALYZE of queries there. as well as partial query results please note that first query shows WRONG result and second one is OK. Regards. Oleg. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Re: [BUGS] --enable-locale doesn't work
On Sun, 2 Apr 2000, Tom Lane wrote: > werner <[EMAIL PROTECTED]> writes: > > I'm running the 7.0 beta 3. It seems like queries that use german > > characters like ü,ä,ö or ß don't work with the ~* > > Operator (case insensetive regex). It only works with case sensetive > > queries. So the configure option > > --enable-locale doesn't have any influence. > > This isn't enough information. What exactly do you mean by "doesn't > work"? What query did you issue, what result did you get, what did > you expect to get? And which locale are you using? Just tested beta3 - working like a charm, as usual :) Are you sure you have correct locale settings? Look into src/test/locale directory; there you'll find locale test for some locales, including de_DE.ISO-8859-1. Run the test (make all test-de_DE.ISO-8859-1). Watch the results - is your locale ok? If you are sure your locale is Ok, but still unsatisfied with locale test - send your patches to me, please. Oleg. Oleg Broytmannhttp://members.xoom.com/phd2.1/[EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN.
[BUGS]
POSTGRESQL BUG REPORT TEMPLATE
Your name : Oleg Broytmann
Your email address : [EMAIL PROTECTED]
System Configuration
-
Architecture (example: Intel Pentium) : SUN Sparc Ultra-1
Operating System (example: Linux 2.0.26 ELF) : Solaris 2.5.1
PostgreSQL version (example: PostgreSQL-6.5.1): PostgreSQL-7.0beta5
Compiler used (example: gcc 2.8.0) : gcc 2.8.1
Please enter a FULL description of your problem:
Compilation error:
make -C libpq++ all
make[2]: Entering directory
`/usr/local/src/PostgreSQL/postgresql-7.0beta5/src/interfaces/libpq++'
gcc -O2 -I../../backend -I../../include -I../../interfaces/libpq -fPIC -c
pgconnection.cc
In file included from pgconnection.cc:18:
pgconnection.h:76: syntax error before `('
pgconnection.cc:132: syntax error before `::'
pgconnection.cc:135: `buffer' was not declared in this scope
pgconnection.cc:135: `buffer' was not declared in this scope
pgconnection.cc:135: warning: ANSI C++ forbids declaration `memset' with no type or
storage class
pgconnection.cc:135: `int memset' redeclared as different kind of symbol
/usr/include/string.h:56: previous declaration of `void * memset(void *, int, unsigned
int)'
pgconnection.cc:135: warning: initializer list being treated as compound expression
pgconnection.cc:136: `buffer' was not declared in this scope
pgconnection.cc:136: `n' was not declared in this scope
pgconnection.cc:136: warning: ANSI C++ forbids declaration `sprintf' with no type or
storage class
pgconnection.cc:136: `int sprintf' redeclared as different kind of symbol
/usr/include/stdio.h:177: previous declaration of `int sprintf(char *, const char *
...)'
pgconnection.cc:136: warning: initializer list being treated as compound expression
pgconnection.cc:137: parse error before `return'
make[2]: *** [pgconnection.o] Error 1
make[2]: Leaving directory
`/usr/local/src/PostgreSQL/postgresql-7.0beta5/src/interfaces/libpq++'
make[1]: *** [all] Error 2
make[1]: Leaving directory
`/usr/local/src/PostgreSQL/postgresql-7.0beta5/src/interfaces'
make: *** [all] Error 2
Oleg.
Oleg Broytmannhttp://members.xoom.com/phd2.1/[EMAIL PROTECTED]
Programmers don't die, they just GOSUB without RETURN.
Re: [BUGS]
On Tue, 11 Apr 2000, Tom Lane wrote:
> Oleg Broytmann <[EMAIL PROTECTED]> writes:
> > gcc -O2 -I../../backend -I../../include -I../../interfaces/libpq -fPIC -c
>pgconnection.cc
> > In file included from pgconnection.cc:18:
> > pgconnection.h:76: syntax error before `('
>
> Hmm. Something broken about "string"?
>
> My guess is that configure didn't think it should define
> HAVE_CXX_STRING_HEADER, but that is actually necessary on your
> machine. Please look into it.
Thanks.
Yes, it was undefined. I defined it in config.h - and got all sorts of
other errors. Seems I have broken or incomplete g++ installation, probably
libstd++ and/or libio++. I rarely compile C++ programs, but I cannot recall
such problem:
make[2]: Entering directory
`/usr/local/src/PostgreSQL/postgresql-7.0beta5/src/i
nterfaces/libpq++'
gcc -O2 -I../../backend -I../../include -I../../interfaces/libpq -fPIC
-c pgc
onnection.cc
In file included from /usr/local/include/g++/alloc.h:18,
from /usr/local/include/g++/std/bastring.h:39,
from /usr/local/include/g++/string:6,
from pgconnection.h:29,
from pgconnection.cc:18:
/usr/local/include/g++/stl_config.h:106: _G_config.h: No such file or
directory
In file included from /usr/local/include/g++/streambuf.h:36,
from /usr/local/include/g++/iostream.h:31,
from /usr/local/include/g++/stl_alloc.h:45,
from /usr/local/include/g++/alloc.h:21,
from /usr/local/include/g++/std/bastring.h:39,
from /usr/local/include/g++/string:6,
from pgconnection.h:29,
from pgconnection.cc:18:
/usr/local/include/g++/libio.h:30: _G_config.h: No such file or directory
make[2]: *** [pgconnection.o] Error 1
make[2]: Leaving directory
`/usr/local/src/PostgreSQL/postgresql-7.0beta5/src/in
terfaces/libpq++'
make[1]: *** [all] Error 2
make[1]: Leaving directory
`/usr/local/src/PostgreSQL/postgresql-7.0beta5/src/in
terfaces'
make: *** [all] Error 2
Oleg.
Oleg Broytmannhttp://members.xoom.com/phd2.1/[EMAIL PROTECTED]
Programmers don't die, they just GOSUB without RETURN.
Re: [BUGS]
On Wed, 12 Apr 2000, Kardos, Dr. Andreas wrote: > This seems to be a Sun Sparc (-fPIC). Sun Sparc, Solaris 2.5.1. > On mine (SunOS 5.4/ Solaris 2.4) isn't any . Therefore libpq++ is > not compilable on this machine (native compilers). gcc 2.8.1 > configure sets HAVE_CXX_STRING_HEADER correctly (no). Yes, set to undef... and didn't compile libpq++. I think I need ./configure --disable-cxx and skip libpq++ at all... Oleg. Oleg Broytmannhttp://members.xoom.com/phd2.1/[EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN.
Re: [BUGS] Too many open files
>From my /etc/rc.d/rc.local:
# increase RCVBUF to optimize proxy<->backend
echo 131072 > /proc/sys/net/core/rmem_max
# increase maximum opened files
echo 8192 > /proc/sys/fs/file-max
# increase shared memory
echo "1" > /proc/sys/kernel/shmmax
Regards,
Oleg
On Wed, 1 Aug 2001, Tom Lane wrote:
> Darin Fisher <[EMAIL PROTECTED]> writes:
> > I am running PosgreSQL 7.1 on Redhat 6.2 Kernel 2.4.6.
> > Under a pretty heavy load:
> > 1000 Transactions per second
> > 32 Open connections
>
> > Everything restarts because of too many open files.
> > I have increase my max number of open files to 16384 but this
> > just delays the inevitable.
>
> > I have tested the same scenario under Solaris 8 and it works
> > fine.
>
> Linux (and BSD) have a tendency to promise more than they can deliver
> about how many files an individual process can open. Look at
> pg_nofile() in src/backend/storage/file/fd.c --- it believes whatever
> sysconf(_SC_OPEN_MAX) tells it, and on these OSes the answer is likely
> to be several thousand. Which the OS can indeed support when *one*
> backend does it, but not when dozens of 'em do it.
>
> I have previously suggested that we should have a configurable upper
> limit for the number-of-openable-files that we will believe --- probably
> a GUC variable with a default value of, say, a couple hundred. No one's
> gotten around to doing it, but if you'd care to submit a patch...
>
> As a quick hack, you could just insert a hardcoded limit in
> pg_nofile().
>
> 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
>
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3048: pg_dump dumps intarray metadata incorrectly
On Mon, 2 Apr 2007, Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian <[EMAIL PROTECTED]> writes:
Tom, do you want this fixed for 8.2.X?
Tom Lane wrote:
Yeah. I'd say that intarray's attempt to override the default status of
the built-in gin opclass is simply a bad idea and should be removed.
I don't recall having seen a response from Oleg or Teodor, and would
like their input before making a final decision --- but at the moment
I think we should take that out.
Oleg or Teodor, I need a comment on this.
We agree with Tom in this case and we'll remove update of
system catalog. But, I want to rise the problem again - pg_dump doesn't
track changes of system catalog. The problem could be more pronounced in
case of built-in FTS, if somebody with superuser rights changes
fts configurations in system catalog.
Regards,
Oleg
_____
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[BUGS] BUG #3305: multiple Operator classes
The following bug has been logged online: Bug reference: 3305 Logged by: Belykh Oleg Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2 Operating system: MAC OS X Description:multiple Operator classes Details: Index scan use only default operator class anyway, but there is 2 defined operator classes. 1. This is partional value search (if values equal from begin) CREATE OPERATOR CLASS treetype_pops DEFAULT FOR TYPE treetype USING btree AS OPERATOR 1 <, OPERATOR 2 <=, OPERATOR 3 @, OPERATOR 4 >=, OPERATOR 5 >, FUNCTION 1 treetype_pcmp(treetype, treetype); 2. This is complete value search (values must be strictly equal) CREATE OPERATOR CLASS treetype_ops FOR TYPE treetype USING btree AS OPERATOR 1 <, OPERATOR 2 <=, OPERATOR 3 =, OPERATOR 4 >=, OPERATOR 5 >, FUNCTION 1 treetype_cmp(treetype, treetype); ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #3865: ERROR: failed to build any 8-way joins
The following bug has been logged online: Bug reference: 3865 Logged by: Oleg Kharin Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.6 Operating system: CentOS 5.1 x86 64-bit Description:ERROR: failed to build any 8-way joins Details: After PostgreSQL 8.2.5 to 8.2.6 upgrade there is a query that generates: ERROR: failed to build any 8-way joins. The text of the query is rather big. It will be better to attach its text as a file as well as the SQL script that creates a test case. But I don't know how to post a file in the bug report form. Oleg ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3865: ERROR: failed to build any 8-way joins
> 8.2 patch is here, if you need it now: > http://archives.postgresql.org/pgsql-committers/2008-01/msg00164.php Thanks. I have applied this patch and everything works fine. Regards, Oleg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] problem
Hello, help me please, I don't know how, but i got 2 postgres users. How can I delete the first one thx alot for your answers. /// select passwd,ctid,xmin,xmax,cmin,cmax from pg_shadow where usesysid =1; passwd| ctid | xmin | xmax | cmin | cmax -+---+--+--+--+- - | (0,1) |1 | 16754522 | 16754522 |0 md510db8c04d26d32185270721fcf32155 | (0,2) |2 |0 |0 |0 (2 rows) // select * from pg_shadow where usesysid=1; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig --+--+-+--+---+- +--+--- postgres |1 | t | t| t | | | postgres |1 | t | t| t | md510db8c04d231142d5 270721fcf740ed5 | infinity | (2 rows) Tom Lane пишет: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes: I have two "postgres" user. How to delete the first one ??? Let's see the system columns (ctid,xmin,xmax,cmin,cmax) for those two rows? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] [GENERAL] tsearch2,pgsql 7.4.[1|2], pg_dump problem
Achilleus,
we have regprocedure_7.4.patch.gz
(http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/)
Oleg
On Fri, 25 Jun 2004, Achilleus Mantzios wrote:
>
> Hi, i noticed a failure when i pg_dump/reload a database with tsearch2.sql
> installed.
>
> I get ERRORS of the type:
>
> dynacom=# SELECT lexize('en_stem','foo bar');
> ERROR: cache lookup failed for function 4652394
> dynacom=# SELECT lexize('en_stem','foo bar');
> ERROR: cache lookup failed for function 4652394
> dynacom=# SELECT lexize('en_stem','foo bar');
> ERROR: cache lookup failed for function 4652394
> dynacom=# SELECT lexize('en_stem','foo bar');
> ERROR: cache lookup failed for function 4652394
> dynacom=#
> dynacom=# SELECT to_tsvector('default','foo bar');
> ERROR: cache lookup failed for function 4652424
> dynacom=# SELECT to_tsvector('default','foo bar');
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !>
> !>
> !> \q
>
> Always the 1st call in a session to to_tsvector gives:
> ERROR: cache lookup failed for function 4652424
> and the very next call in the same session causes the postmaster
> to SEGV.
>
> This situation is demonstrated in FreeBSD 5.1-RELEASE-p10, pgsql 7.4.1
> and in Debian 2.4.18-bf2.4, pgsql 7.4.2.
>
> A workaround is to first load tsearch2.sql script in the newly created
> database, reload the dump, and ignore any tsearch2 subsequent ERRORS.
>
> Anyone has a clue?
>
>
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(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
[BUGS] BUG #2290: Incorrect sequence increment after backup/restore
The following bug has been logged online: Bug reference: 2290 Logged by: Oleg Mamontov Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.x/8.1.x Operating system: FreeBSD 4.x,5.x,6.x Description:Incorrect sequence increment after backup/restore Details: If after CREATE TABLE with SERIAL column i'll change sequence increment to 2 or some other value (with ALTER SEQUENCE) then always work correctly (all inserted rows will have values incremented by 2). But after database backup/restore (with pg_dump) this ALTER will not restored and all next inserted rows will have values incremented by 1 (default for SERIAL data type). It's seems like a bug... Changing increment value often used with replication (master database inserts has odd values and slave database inserts has even) and other situations. Sorry for my poor English. ---(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
[BUGS] BUG #2300: Error in upper function
The following bug has been logged online: Bug reference: 2300 Logged by: Kashin Oleg Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: Suse 9.1 Description:Error in upper function Details: My database is with UTF8 encoding. When I use function "upper" for any filed of table I get the next error "Invalid multibyte character for locale". Example: select from users where upper(user_login) like '%'; I rewrote such SQL query like next one - select from users where lower(user_login) like '%'; and everything now is ok. Thanks. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2345: odbc driver doesn't work
The following bug has been logged online: Bug reference: 2345 Logged by: Kashin Oleg Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: FreeBSD Description:odbc driver doesn't work Details: Hello! I've the trouble. I'd installed unixodbc 2.2.11 under freeBSD-4.8 and complided psqlodbc driver-v.08.01.0200. I made all necessary things to use odbc driver byt it doesn't work. When I use standart driver for postgres from unixodbc all is ok. Fot test I use isql programm. I tried to do all the same under linux (Suse 9.1) and this driver works in spite of I compiled unixodbc and psqlodbc driver as well. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #6327: Prefix full-text-search fails for hosts with complicated names
On Mon, 5 Dec 2011, Tom Lane wrote:
marcin.kasper...@mekk.waw.pl writes:
Synopsis
=
'goog:*' matches google.com
but
'e-goog:*' does not match e-google.com
The reason for this seems to be that the pattern is treated as a
hyphenated word:
regression=# select TO_TSQUERY('english', 'e-goog:*');
to_tsquery
---
'e-goog':* & 'e':* & 'goog':*
(1 row)
but the hostname isn't:
regression=# select TO_TSVECTOR('english', 'See e-google.com');
to_tsvector
--
'e-google.com':2 'see':1
(1 row)
If you change the text so it's not recognized as a hostname, you get
lexemes that would match the query:
regression=# select TO_TSVECTOR('english', 'See e-google com');
to_tsvector
-
'com':5 'e':3 'e-googl':2 'googl':4 'see':1
(1 row)
Possibly we could fix this by hacking the ts parser so that it would
also apply the hyphenated-word rules to a hostname containing a dash.
In general though, there are always going to be cases where prefix
match doesn't work because of dictionary transformations ...
I'd index 'after dictionary transformations' lexemes as well as an
original to let prefix march always work.
regards, tom lane
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
