[BUGS] sql function returning composite type

2004-09-21 Thread Ivan 
Hello,

Suppose we have sql function which returns composite type.
When such function's last select statement doesn't return any row
error occurs -
ERROR:  function returning row cannot return null value.
But if we use similar function that returns set of same type
error not occurs.

Example:


CREATE TYPE "test_type" AS (
"id" integer,
"name" character varying(64),
"description" text
);

CREATE FUNCTION "test"(character varying) RETURNS "test_type"
AS '
  select "id", "name", "description"
from "test"
where "name" = $1;
'
LANGUAGE sql SECURITY DEFINER;

CREATE FUNCTION "test2"(character varying) RETURNS SETOF "test_type"
AS '
  select "id", "name", "description"
from "test"
where "name" = $1;
'
LANGUAGE sql SECURITY DEFINER;


CREATE TABLE "test" (
"id" serial NOT NULL,
"name" character varying(64) NOT NULL,
"description" text,
"update_time" timestamp without time zone DEFAULT now() NOT NULL
);

INSERT INTO "test" ("id", "name", "description", "update_time") VALUES (1, 'first', 
'first row', '2004-09-21 15:32:41.171');
INSERT INTO "test" ("id", "name", "description", "update_time") VALUES (2, 'second', 
'second row', '2004-09-21 15:32:54.64');
INSERT INTO "test" ("id", "name", "description", "update_time") VALUES (3, 'third', 
'third row', '2004-09-21 15:33:08.406');

ALTER TABLE ONLY "test"
ADD CONSTRAINT "pk_test_id" PRIMARY KEY ("id");

ALTER TABLE ONLY "test"
ADD CONSTRAINT "unq_test_name" UNIQUE ("name");

----
select * from "test"('second')
will return one row with data
but
select * from "test"('secon')
will raise an ERROR
and
select * from "test2"('secon')
will return empty set.

I think that is more conveniently that when no data is fetched
such function returns instance of composite type with nulls.

In case of function returning record type we also "know" schema of
the last select.

Of course it is possible to use plpgsql function and select into
but sql functions is smaller and don't require handler.

-- 
Best regards,
 Ivan  mailto:[EMAIL PROTECTED]


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [BUGS] sql function returning composite type

2004-09-21 Thread Ivan 
Hello Tom,

Tuesday, September 21, 2004, 6:17:37 PM, you wrote:

TL> Ivan <[EMAIL PROTECTED]> writes:
>> Suppose we have sql function which returns composite type.
>> When such function's last select statement doesn't return any row
>> error occurs -
>> ERROR:  function returning row cannot return null value.

TL> Works for me (tested in 7.4.5 and CVS tip).

I've just (an hour ago) get latest version from CVS.

"PostgreSQL 8.0.0beta2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.2.3 (mingw 
special 20030504-1)"

Still have the same error - ERROR:  function returning row cannot return null value

I attached sql file.
Error occurs when do

select * from "test"('secon')

-- 
Best regards,
 Ivanmailto:[EMAIL PROTECTED]SET client_encoding = 'UNICODE';
SET check_functions_bodies = false;

SET search_path = public, pg_catalog;

CREATE TYPE "test_type" AS (
"id" integer,
"name" character varying(64),
"description" text
);

CREATE FUNCTION "test"(character varying) RETURNS "test_type"
AS '
  select "id", "name", "description"
from "test"
where "name" = $1;
'
LANGUAGE sql SECURITY DEFINER;

CREATE FUNCTION "test2"(character varying) RETURNS SETOF "test_type"
AS '
  select "id", "name", "description"
from "test"
where "name" = $1;
'
LANGUAGE sql SECURITY DEFINER;


SET default_with_oids = true;

CREATE TABLE "test" (
"id" serial NOT NULL,
"name" character varying(64) NOT NULL,
"description" text,
"update_time" timestamp without time zone DEFAULT now() NOT NULL
);

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('test', 'id'), 1, false);

INSERT INTO "test" ("id", "name", "description", "update_time") VALUES (1, 'first', 
'first row', '2004-09-21 15:32:41.171');
INSERT INTO "test" ("id", "name", "description", "update_time") VALUES (2, 'second', 
'second row', '2004-09-21 15:32:54.64');
INSERT INTO "test" ("id", "name", "description", "update_time") VALUES (3, 'third', 
'third row', '2004-09-21 15:33:08.406');

ALTER TABLE ONLY "test"
ADD CONSTRAINT "pk_test_id" PRIMARY KEY ("id");

ALTER TABLE ONLY "test"
ADD CONSTRAINT "unq_test_name" UNIQUE ("name");

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;



---(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] pg_dumpall fails

2004-10-01 Thread Ivan 
Hello,

When i do:

pg_dumpall -D --disable-dollar-quoting -U postgres -h hostname > cluster.backup

the following error occurs:

pg_dump.exe: [archiver (db)] connection to database "example" failed: could not 
translate host name
"'hostname'" to address: Unknown host
pg_dumpall.EXE: pg_dump failed on database "example", exiting

database "example" exist in the cluster
real host name i replaced with string hostname

WinXp SP2
PostgreSQL 8.0beta2

--
Then I get from cvs latest version
(PostgreSQL 8.0.0beta3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.2.3 (mingw 
special 20030504-1))

Perform initdb,
with psql do

create database "example1" with encoding = 'WIN';

and quit.

pg_dumpall -D --disable-dollar-quoting -U postgres -h localhost

fails with:
--
-- PostgreSQL database cluster dump
--

\connect "template1"

--
-- Users
--

--
-- Database creation
--

CREATE DATABASE example1 WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'WIN';

--
-- Users
--

ALTER USER postgres WITH CREATEDB CREATEUSER;

\connect example1

pg_dump.exe: [archiver (db)] connection to database "example1" failed: could not 
translate host name
 "'localhost'" to address: Unknown host
pg_dumpall.EXE: pg_dump failed on database "example1", exiting

and

pg_dumpall -D --disable-dollar-quoting -U postgres

outputs following:

--
-- PostgreSQL database cluster dump
--

\connect "template1"

--
-- Users
--

--
-- Database creation
--

CREATE DATABASE example1 WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'WIN';

--
-- Users
--

ALTER USER postgres WITH CREATEDB CREATEUSER;


\connect example1

pg_dump.exe: [archiver (db)] connection to database "example1" failed: FATAL:  user 
"'postgres'" doe
s not exist
pg_dumpall.EXE: pg_dump failed on database "example1", exiting


-- 
Best regards,
 Ivan  mailto:[EMAIL PROTECTED]


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [BUGS] Data corruption/loss when altering tables (fwd)

2004-11-22 Thread Ivan 
Hello Nicola,

Monday, November 22, 2004, 9:00:30 PM, you wrote:

NP> I've been experiencing data corruption/loss in Postgresql 7.4.2.

Try to recompile (create or replace) your stored procedure
after you alter the table

Hope this help...

-- 
Best regards,
 Ivanmailto:[EMAIL PROTECTED]


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[BUGS] union bug

2005-10-19 Thread Ivan 
Hello,

PostgreSQL 8.1beta3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 
(mingw-special)
Windows XP SP2

The following query

CREATE DOMAIN test_domain
  AS varchar(64)
  NOT NULL;
  
CREATE TYPE test_type AS
   ("Id" int4,
"Data" test_domain);

CREATE OR REPLACE FUNCTION union_test()
  RETURNS SETOF test_type AS
$BODY$
select 1 as "Id", 'string1'::test_domain as "Data"
union all
select 2 as "Id", 'string2'::test_domain as "Data"
$BODY$
  LANGUAGE 'sql' VOLATILE;

generates error message

ERROR:  return type mismatch in function declared to return test_type
DETAIL:  Final SELECT returns character varying instead of test_domain at 
column 2.
CONTEXT:  SQL function "union_test"

but this one is not

CREATE OR REPLACE FUNCTION union_test2()
  RETURNS SETOF test_type AS
$BODY$
  select "Id"::int4, "Data"::test_domain
  from (
select 1 as "Id", 'string1' as "Data"
union all
select 2 as "Id", 'string2' as "Data"
) as q1;
$BODY$
  LANGUAGE 'sql' VOLATILE;

-- 
Best regards,
 Ivan  mailto:[EMAIL PROTECTED]


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [BUGS] union bug

2005-10-20 Thread Ivan 
Hello,

>> CREATE DOMAIN test_domain
>>   AS varchar(64)
>>   NOT NULL;

>> CREATE TYPE test_type AS
>>("Id" int4,
>> "Data" test_domain);

>> CREATE OR REPLACE FUNCTION union_test()
>>   RETURNS SETOF test_type AS
>> $BODY$
>> select 1 as "Id", 'string1'::test_domain as "Data"
>> union all
>> select 2 as "Id", 'string2'::test_domain as "Data"
>> $BODY$
>>   LANGUAGE 'sql' VOLATILE;

>> generates error message

>> ERROR:  return type mismatch in function declared to return test_type
>> DETAIL:  Final SELECT returns character varying instead of test_domain at 
>> column 2.
>> CONTEXT:  SQL function "union_test"

TL> The reason this happens is that select_common_type() smashes all its
TL> inputs down to base types.  I'm a bit hesitant to change this behavior
TL> without thinking about all the possible consequences.  There are clearly
TL> some cases where it's the right thing --- for instance, if the inputs
TL> are two different domains over the same base type, selecting the base
TL> type seems the most reasonable behavior.  Also, at least some of the
TL> routine's callers seem to be relying on the assumption that the result
TL> won't be a domain type.

I'd like to offer following solution:

for given column of the union check if the types of all parts ot the
union for that column are !exactly! the same, then resulting column
type of the union is left to that type, otherwise it casts to the base
type.

In this case users can explicitly cast column types of union parts to
whatever they want to get that type in the result, i.e.

CREATE DOMAIN test_domain
  AS varchar(64)
  NOT NULL;
  
CREATE DOMAIN test_domain2
  AS varchar(64)
  NOT NULL
  CHECK (length(trim(value)) > 0);
  
CREATE TYPE test_type AS
   ("Id" int4,
"Data" test_domain);

CREATE OR REPLACE FUNCTION union_test()
  RETURNS SETOF test_type AS
$BODY$
select 1 as "Id", 'string1'::test_domain as "Data"
union all
select 2 as "Id", ('string2'::test_domain2)::test_domain as "Data"
$BODY$
  LANGUAGE 'sql' VOLATILE;

If i understand correctly current workaround is to use outer select
with type cast (as i note in previous message). But as i see it takes
extra processing (as query plans below shows)

  select "Id", "Data"::test_domain from
  (
select 1 as "Id", 'string1'::test_domain as "Data"
union all
select 2 as "Id", ('string2'::test_domain2)::test_domain as "Data"
  ) as q1;

Subquery Scan q1  (cost=0.00..0.07 rows=2 width=36)
  ->  Append  (cost=0.00..0.04 rows=2 width=0)
->  Subquery Scan "*SELECT* 1"  (cost=0.00..0.02 rows=1 width=0)
  ->  Result  (cost=0.00..0.01 rows=1 width=0)
->  Subquery Scan "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=0)
  ->  Result  (cost=0.00..0.01 rows=1 width=0)

select 1 as "Id", 'string1'::test_domain as "Data"
union all
select 2 as "Id", ('string2'::test_domain2)::test_domain as "Data"
  
Append  (cost=0.00..0.04 rows=2 width=0)
  ->  Subquery Scan "*SELECT* 1"  (cost=0.00..0.02 rows=1 width=0)
->  Result  (cost=0.00..0.01 rows=1 width=0)
  ->  Subquery Scan "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=0)
->  Result  (cost=0.00..0.01 rows=1 width=0)
  
  
Thank you for support.
-- 
Best regards,
 Ivanmailto:[EMAIL PROTECTED]


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[BUGS] pg_dump bud with functions

2005-10-27 Thread Ivan 
Hello,

PostgreSQL 8.0.x, 8.1beta3, WinXP Pro SP2

When I create a function in sql or plpgsql languages,
their body text is stored in the database files with
0D 0A line ends (I checked it in a hex editor).

If I create plain backup using pg_dump
it generates functions with
0D 0D 0A on the end of the body lines,
so when I open function definition in PGAdmin,
for example, I saw function text with extra empty lines:

CREATE OR REPLACE FUNCTION "foo"()
RETURNS bar AS
$$

  select * from "bar"

  where  bla-bla

  order by bla-bla;
  
$$
  LANGUAGE 'sql' VOLATILE;

It's very annoy, so please fix if it's not very hard.

Thank you!

-- 
Best regards,
 Ivan  mailto:[EMAIL PROTECTED]


---(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

Re: [BUGS] pg_dump bud with functions

2005-10-27 Thread Ivan 
Hello,

Thursday, October 27, 2005, 6:34:36 PM, you wrote:

TL> Ivan <[EMAIL PROTECTED]> writes:
>> When I create a function in sql or plpgsql languages,
>> their body text is stored in the database files with
>> 0D 0A line ends (I checked it in a hex editor).

>> If I create plain backup using pg_dump
>> it generates functions with
>> 0D 0D 0A on the end of the body lines,

TL> Good ol' Windows :-(.  Try writing the dump file with "pg_dump -f file"
TL> instead of "pg_dump >file" ... does that make it better?

Yes, it does. :)
Thank you very much!


-- 
Best regards,
 Ivanmailto:[EMAIL PROTECTED]


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [BUGS] pg_dump bud with functions

2005-10-31 Thread Ivan 
Hello,

TL> Ivan <[EMAIL PROTECTED]> writes:
>> When I create a function in sql or plpgsql languages,
>> their body text is stored in the database files with
>> 0D 0A line ends (I checked it in a hex editor).

>> If I create plain backup using pg_dump
>> it generates functions with
>> 0D 0D 0A on the end of the body lines,

TL> Good ol' Windows :-(.  Try writing the dump file with "pg_dump -f file"
TL> instead of "pg_dump >file" ... does that make it better?

This problem is actual for pg_dumpall because there is no -f file
option for it - output is always written to standard output.
Is it possible to add this option to pg_dumpall too?

Thank you for support!


-- 
Best regards,
 Ivanmailto:[EMAIL PROTECTED]


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[BUGS] pg_dumpall bug & feature request

2005-11-03 Thread Ivan 
Hello,

Perhaps you missed my previous message.
pg_dumpall has not -f command line option
to specify output file - it always send output
to standart output. But there is an issue with
it in Windows (I described it earlier) - function's
line endings are changed from 0D 0A to 0D 0D 0A.

Could you please add -f option to pg_dumpall,
because it's very inconvenient to replace these
character sequences in hex editor all the time.

Thank you for support!

-- 
Best regards,
 Ivan  mailto:[EMAIL PROTECTED]


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [BUGS] pg_dumpall bug & feature request

2005-11-03 Thread Ivan 
Hello Alvaro,

Thursday, November 3, 2005, 3:28:41 PM, you wrote:

AH> Ivan wrote:

AH> Hi,

>> Perhaps you missed my previous message.
>> pg_dumpall has not -f command line option
>> to specify output file - it always send output
>> to standart output. But there is an issue with
>> it in Windows (I described it earlier) - function's
>> line endings are changed from 0D 0A to 0D 0D 0A.

AH> Hmm, what do you need that for?  Why don't you directly restore the
AH> binary dump to a database by using option -d?

I don't need to restore binary dump. I need to make a dump of the
whole database cluster to plain sql file (what pg_dumpall does).
So I have to redirect pg_dumpall's output to a file
pg_dump [options] > cluster_dump.sql
When I do it on Windows, it creates cluster_dump.sql file
BUT in that file all sql and plpgsql function bodies are corrupted! -
each line in the body ends with 0D 0D 0A sequence instead of 0D 0A -
therefor functions after restoring looks like:

CREATE OR REPLACE FUNCTION "foo"()
RETURNS bar AS
$$

  select * from "bar"

  where  bla-bla

  order by bla-bla;
  
$$
  LANGUAGE 'sql' VOLATILE;

instead of

CREATE OR REPLACE FUNCTION "foo"()
RETURNS bar AS
$$
  select * from "bar"
  where  bla-bla
  order by bla-bla;
$$
  LANGUAGE 'sql' VOLATILE;

 - extra blank lines are added.

So it would be great if the output redirection ">" will be workaround
for example the same way like in pg_dump it is done - via -f file
option.

Thank you for support.

-- 
Best regards,
 Ivanmailto:[EMAIL PROTECTED]


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[BUGS] Severe bug with postgresql 7.0.3

2001-01-17 Thread Ivan Vighetto 

I use a DIGITAL ALPHA DEC2000 AXP500 with 128MB of RAM, architecture EV4
150MHz processor. I installed a Debian potato (2.2) and got problems
during installation, postmaster not starting.
After a mail exchange with postgres Debian mantainer, got sources of
postgresql version 7.0.3 (debian patched) and compiled, installation was
successful. Created a database with "createdb test", all OK. Created a
new user with "createuser gunny", all ok. Connected to test with psql
and create a very simple table named "prova" with only a column of
varchar(20), all ok. After did the command "\d prova", the problem is
that it never ended without message. With a ps I found the process
"/usr/lib/postgres/bin/postgres test idle" that get all the CPU but
little memory (4%). Some ideas?

[BUGS] 7.0.3 - Backend crash on simple SELECT query

2001-03-06 Thread Ivan Baldo 

Severity: Devastating

Short description:
Simple SELECT query with simple REGEXP comparison with simple
ORDER
and WITHOUT JOINS CRASHES BACKEND (seems to die in an infinite loop that

consumes CPU and uses the hard disk).

Long description:
The query:
SELECT t.* FROM t
WHERE UPPER(sa) ~ 'SOME STRING'
ORDER BY sa ASC
LIMIT 25, 0;
executed by the psql command line utility never finishes and cannot be
cancelled neither.
To create the table structure:
CREATE TABLE "t" (
"c1" character(6),
"d1" date,
"i1" int4,
"c2" character(74),
"c3" character(74),
"i2" int4,
"sa" character(50),
"c4" character,
"c5" character(50),
"c6" character(2),
"c7" character,
"d2" date,
"c8" character(4)
);
CREATE  INDEX "t_pkey" on "t" using btree ( "c1" "bpchar_ops" );

CREATE  INDEX "t_d1" on "t" using btree ( "d1" "date_ops" );
CREATE  INDEX "t_i2" on "t" using btree ( "i2" "int4_ops" );
CREATE  INDEX "t_sa" on "t" using btree ( "sa" "bpchar_ops" );
The table has 121422 tuples (counted with "select count(*) from
t;").
EXPLAIN VERBOSE of the query says:
QUERY DUMP:
{ INDEXSCAN :startup_cost 0.00 :total_cost 66398.74 :rows 1214
:width 124 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM
:resno 1 :restype 1042 :restypmod 10 :resname c1 :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1
:vartype 1042 :vartypmod 10  :varlevelsup 0 :varnoold 1 :varoattno 1}} {
TARGETENTRY :resdom { RESDOM :resno 2 :restype 1082 :restypmod -1
:resname d1 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 2 :vartype 1082 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 2}} { TARGETENTRY :resdom { RESDOM
:resno 3 :restype 23 :restypmod -1 :resname i1 :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 3
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 3}} {
TARGETENTRY :resdom { RESDOM :resno 4 :restype 1042 :restypmod 78
:resname c2 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 4 :vartype 1042 :vartypmod 78
:varlevelsup 0 :varnoold 1 :varoattno 4}} { TARGETENTRY :resdom { RESDOM
:resno 5 :restype 1042 :restypmod 78 :resname c3 :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 5
:vartype 1042 :vartypmod 78  :varlevelsup 0 :varnoold 1 :varoattno 5}} {
TARGETENTRY :resdom { RESDOM :resno 6 :restype 23 :restypmod -1 :resname
i2 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 1 :varattno 6 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold
1 :varoattno 6}} { TARGETENTRY :resdom { RESDOM :resno 7 :restype 1042
:restypmod 54 :resname sa :reskey 0 :reskeyop 0 :ressortgroupref 1
:resjunk false } :expr { VAR :varno 1 :varattno 7 :vartype 1042
:vartypmod 54  :varlevelsup 0 :varnoold 1 :varoattno 7}} { TARGETENTRY
:resdom { RESDOM :resno 8 :restype 1042 :restypmod 5 :resname c4 :reskey
0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 8 :vartype 1042 :vartypmod 5  :varlevelsup 0 :varnoold 1
:varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 9 :restype 1042
:restypmod 54 :resname c5 :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 9 :vartype 1042
:vartypmod 54  :varlevelsup 0 :varnoold 1 :varoattno 9}} { TARGETENTRY
:resdom { RESDOM :resno 10 :restype 1042 :restypmod 6 :resname c6
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 1 :varattno 10 :vartype 1042 :vartypmod 6  :varlevelsup 0
:varnoold 1 :varoattno 10}} { TARGETENTRY :resdom { RESDOM :resno 11
:restype 1042 :restypmod 5 :resname c7 :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 11
:vartype 1042 :vartypmod 5  :varlevelsup 0 :varnoold 1 :varoattno 11}} {
TARGETENTRY :resdom { RESDOM :resno 12 :restype 1082 :restypmod -1
:resname d2 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 12 :vartype 1082 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 12}} { TARGETENTRY :resdom {
RESDOM :resno 13 :restype 1042 :restypmod 8 :resname c8 :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 13 :vartype 1042 :vartypmod 8  :varlevelsup 0 :varnoold 1
:varoattno 13}}) :qpqual ({ EXPR :typeOid 16  :opType op :oper { OPER
:opno 641 :opid 1254 :opresulttype 16 } :args ({ EXPR :typeOid 25
:opType func :oper { FUNC :funcid 871 :functype 25 :funcisindex false
:funcsize 0  :func_fcache @ 0x0 :func_tlist ({ TARGETENTRY :resdom {
RESDOM :resno 1 :restype 25 :restypmod -1 :resname \ :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk

[BUGS] BUG #2726: reindex database failed if number is in database name

2006-10-31 Thread Ivan Volf 

The following bug has been logged online:

Bug reference:  2726
Logged by:  Ivan Volf
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.1
Operating system:   linux
Description:reindex database failed if number is in database name
Details: 

drin=# reindex database test;
NOTICE:  table "pg_class" was reindexed
NOTICE:  table "sql_features" was reindexed
NOTICE:  table "sql_implementation_info" was reindexed
NOTICE:  table "sql_languages" was reindexed

drin=# reindex database 06test;
ERROR:  syntax error kod ili u blizini "06" at character 18
LINE 1: reindex database 06test;

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[BUGS] Old bug concerning regular expressions and UTF-8

2007-03-27 Thread Ivan Panchenko 

I have noticed the same BUG in 8.2.3:

The ~* operation did not match cyrillic upper and lower case letters in 
utf-8 encoding.


Still the Helmar's fix solves the problem (see 
http://archives.postgresql.org/pgsql-bugs/2006-01/msg00200.php ).


Is there (or is it planned) an "official" solution?

Helmar, thank you very match.

Regards, Ivan.

**

   * *From*: *Helmar Spangenberg mailto:[EMAIL PROTECTED]>>*
   * *To*: *pgsql-bugs ( at ) postgresql ( dot ) org
 <mailto:[EMAIL PROTECTED]>*
   * *Subject*: *Bug concerning regular expressions and UTF-8*
   * Date: Fri, 20 Jan 2006 18:03:17 +0100



http://archives.postgresql.org/pgsql-bugs/2006-01/msg00200.php

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

[BUGS] Can't solve this problem

2002-07-25 Thread Ivan Dolinin 

Your name   :   Ruhl Anton
Your email address  :   [EMAIL PROTECTED]


System Configuration
-
  Architecture (example: Intel Pentium) :   Intel P-III 733

  Operating System (example: Linux 2.0.26 ELF)  :   Linux-2.2.19 RedHat

  PostgreSQL version (example: PostgreSQL-7.1.1):   PostgreSQL 7.1 on
i686-pc-linux-gnu, compiled by GCC 2.96

  Compiler used (example:  gcc 2.95.2)  :   GCC 2.96


Please enter a FULL description of your problem:


Several day ago we neew to clear some positions in the table bu SQL-script
starting the work by Cron. During the process we have found a problem we
can't solve. The problem is: not only corresponding the request positions
were deleted, the some positions not corresponding the request were deleted
also.

To make this request working is necessary:
delete from forum_data where interval_ge(interval '1 mon',
timestamp_mi(timestamp (now()), timestamp(date))) ='f';

We receive from PostreSQL "49 items were deleted" but all the entires from
the table were deleted. $9 - that is only our requested amount, that can be
any another.





Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
--

The problem is:
To make this request working is necessary:
delete from forum_data where interval_ge(interval '1 mon',
timestamp_mi(timestamp (now()), timestamp(date))) ='f';

We receive from PostreSQL "49 items were deleted" but all the entires from
the table were deleted. $9 - that is only our requested amount, that can be
any another.

The script controlled by Cron:

== SQL Script 
begin transaction;
select  id, date from forum_data;

delete from forum_data where interval_ge(interval '1
mon',timestamp_mi(timestamp (now()), timestamp(date))) ='f';
select  id, date from forum_data;
rollback;
select  id, date from forum_data;
==


This is the processing table:
= Describe test table =
  Table "forum_data"
  Attribute   |   Type   |  Modifier
--+--+--
---
 id   | integer  | not null default
nextval('forum_data_id_seq'::text)
 section_id   | integer  | not null
 parent_id| integer  | not null
 name | text |
 date | timestamp with time zone | default "timestamp"(now())
 text | text |
 price| text |
 contact_with | text |
 autor| text |
 send_email   | boolean  |
Index: forum_data_pkey


We can't solve this problem.

Thank you.

Anton Ruhl, Ivan Dolinin (Russia, Tomsk)


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[BUGS] Out of memory

2003-07-09 Thread Ivan Boscaino 
Hi,
I'm not sure if you know this problem.
Create an empty function with 26 parameters:

create function test (INTEGER,TEXT,TEXT,TEXT,CHAR(16),CHAR(11),
BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,BOOLEAN,
BOOLEAN,BOOLEAN,BOOLEAN,CHAR(2),TEXT,TEXT,TEXT,TEXT,
CHAR(5),TEXT,TEXT,TEXT,TEXT,TEXT )
returns integer as '
declare
begin
 return 0;
end;' language 'plpgsql';
Call it mistyping the boolean parameters:

select test 
(1,'a','a','a','a','a',1,1,1,1,1,1,1,1,1,'a','a','a','a','a','a','a','a','a','a','a');

Then the system crashes.

In /var/log/messages I found:

[...]
Jul  8 11:21:58 host1 kernel: Out of Memory: Killed process 20586 
(postmaster).
[...]

OS: RH7.3 and RH7.2
PG: 7.3.2 and 7.3.3


---(end of broadcast)---
TIP 8: explain analyze is your friend

[BUGS] plpgsql error handling bug

2004-09-05 Thread Ivan-Sun1 
Hello.

I found a bug in the behaviour of plpgsql error handling system
while trying to handle foreign key violation exception.

When this error occured, control doesn't jump to exception handling block.
It moves to the next statement instead. When control leaves the
function exception is occured. So it's impossible to handle this kind of
exception.

Furthermore, the FOUND local variable is set to true after insert
statement which raises foreign key violation and
GET DIAGNOSTICS var = ROW_COUNT set var to 1.

Attached file contains sample that shows this bug.
Execute fk_violation_bug, fk_violation_bug2 and fk_violation_bug3
plpgsql functions to see this error.

-
I'm using

"PostgreSQL 8.0.0beta1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.2.3 (mingw 
special 20030504-1)"

on

OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 2 Build 2600

Thank you.

-- 
Best regards,
 Ivan  mailto:[EMAIL PROTECTED]--
-- PostgreSQL database dump
--

SET client_encoding = 'WIN';
SET check_function_bodies = false;

SET SESSION AUTHORIZATION 'root';

--
-- Name: public; Type: SCHEMA; Schema: -; Owner: root
--

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: root
--

COMMENT ON SCHEMA public IS 'Standard public schema';


SET search_path = public, pg_catalog;

--
-- Name: plpgsql_call_handler(); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
AS '$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAGE c;


--
-- Name: plpgsql_validator(oid); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION plpgsql_validator(oid) RETURNS void
AS '$libdir/plpgsql', 'plpgsql_validator'
LANGUAGE c;


SET SESSION AUTHORIZATION DEFAULT;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner: 
--

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler VALIDATOR 
plpgsql_validator;


SET SESSION AUTHORIZATION 'root';

--
-- Name: fk_violation_bug(); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION fk_violation_bug() RETURNS integer
AS '

begin

  insert into detailed (main_id, task)

values (-1, ''bug'');

  return 0;

end;

'
LANGUAGE plpgsql;


--
-- Name: fk_violation_bug2(); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION fk_violation_bug2() RETURNS integer
AS '

begin

  insert into detailed (main_id, task)

values (-1, ''bug'');

  return 0;

  exception

when foreign_key_violation then

  raise warning ''foreign key violation'';

  return -1;

when others then

  raise warning ''other error occured'';

  return -2;

end;

'
LANGUAGE plpgsql;


--
-- Name: fk_violation_bug3(); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION fk_violation_bug3() RETURNS integer
AS '

declare

  tmp int4;

begin

  insert into detailed (main_id, task)

values (-1, ''bug'');

  raise notice ''should not be there if the foreign key violation occured'';

  raise notice ''local variable FOUND value: %'', FOUND;

  GET DIAGNOSTICS tmp = ROW_COUNT;

  raise notice ''DIAGNOSTICS ROW_COUNT value: %'', tmp;

  tmp := 1/0;

  raise notice ''should not be there if division by zero occured'';

  return 0;

  exception

when foreign_key_violation then

  raise warning ''foreign key violation'';

  return -1;

when division_by_zero then

  raise warning ''division by zero'';

  return -2;

when others then

  raise warning ''other error occured'';

  return -3;

end;

'
LANGUAGE plpgsql;


SET default_with_oids = true;

--
-- Name: detailed; Type: TABLE; Schema: public; Owner: root
--

CREATE TABLE detailed (
main_id integer,
task text NOT NULL
);


--
-- Name: main; Type: TABLE; Schema: public; Owner: root
--

CREATE TABLE main (
id serial NOT NULL,
name character varying(64)
);


--
-- Name: main_id_seq; Type: SEQUENCE SET; Schema: public; Owner: root
--

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('main', 'id'), 1, false);


--
-- Data for Name: detailed; Type: TABLE DATA; Schema: public; Owner: root
--

INSERT INTO detailed (main_id, task) VALUES (1, 'task11');
INSERT INTO detailed (main_id, task) VALUES (1, 'task12');


--
-- Data for Name: main; Type: TABLE DATA; Schema: public; Owner: root
--

INSERT INTO main (id, name) VALUES (1, 'name1');
INSERT INTO main (id, name) VALUES (2, 'name2');
INSERT INTO main (id, name) VALUES (3, 'name3');


--
-- Name: main_pkey; Type: C

[BUGS] BUG #1380: dont reading messages in russian languages

2005-01-13 Thread Ivan Chumak 

The following bug has been logged online:

Bug reference:  1380
Logged by:  Ivan Chumak
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.0
Operating system:   windows 2000
Description:dont reading messages in russian languages
Details: 

i talking about messages after install in services programms. if runing
command createlang (for example) in command line then all messages
output in codepage koi8, but in windows codepage is cp1251 (for exemple
after start command createlang inquiry
password dont read [??])

---(end of broadcast)---
TIP 8: explain analyze is your friend

[BUGS] BUG #1520: messages in Russian is made wrong

2005-03-04 Thread Ivan Chumak 

The following bug has been logged online:

Bug reference:  1520
Logged by:  Ivan Chumak
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.0
Operating system:   Windows XP SP2
Description:messages in Russian is made wrong
Details: 

The derivation (conclusion) of messages by sevice programs in Russian is
made wrong. As I think, the conclusion is made in coding koi8, but the
coding is in command line in windows, that`s why cp866 messages is not
readability.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[BUGS] BUG #8389: JDBC Driver assumes first column to be an integer

2013-08-20 Thread ivan . pli 
The following bug has been logged on the website:

Bug reference:  8389
Logged by:  Ivan Pliouchtchai
Email address:  ivan@solidit.cl
PostgreSQL version: 9.2.4
Operating system:   Windows 7
Description:

PRECONDITION:
When having a table with the next columns:
content varchar(10)
idcontent int primary key


ACTION:
Persisting a row using the JDBC driver (I used JBoss and hibernate to
persist a bean)




RESULT:
The JDBC driver throws an exception while trying to parse the first column
content (a string) as an integer.


Anyway the content gets saved to the database.


TEMPORARY SOLUTION:
Recreating the table with the integer key column as the first column solved
the problem.



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] data forma error in pgsql 7.1

2003-10-17 Thread Ivan E. Rivera Uria 
We are developing a Vehicle Control Application for Mexico Government 
(Morelia, Michoacan), and we have a problem with the date format in the 
database. The Operating System is RedHat Linux 7.2 and database is 
PostgreSQL Ver.  7.1.3.

Explanation:

The comand that we execute is:

insert into docs_requeridos values 
(243,5,15,123456,'01/11/2004','ivan',1);

the date format that we use is DD/MM/ but whet we execute a select 
command we get this information:

id  |status|id2|serie  |date_exe|f_name |l_name
---
243 |  5| 15|123456| 2004-01-11 00:00:00-06 |   ivan|1
Day and month are interchanged and this error happend only when the day
is less than 12, another error that occure is the year, in some cases 
the year is inserted in this way, if the year is  2003, postgresql 
inserts it like 0003.

We tested it with the to_char() function, but we have the same error in 
the database.

Do you have some information about this problem???

We need to know if is necessary to install some patch to fix this 
problem or upgrade the database.

regards



--
===
Ivan E. Rivera Uria
BNC - Systems FPS Manager
Tel. +(52)-55-2614-0510
Fax. +(52)-55-
Email: [EMAIL PROTECTED]
Web: http://www.bnchq.com.mx
==
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [BUGS] data forma error in pgsql 7.1

2003-10-21 Thread Ivan E. Rivera Uria 
Tom you right!!

We only set PGDATESTYLE on root env and then restart the httpd

thanks

Tom Lane wrote:
"Ivan E. Rivera Uria" <[EMAIL PROTECTED]> writes:

the date format that we use is DD/MM/ but whet we execute a select 
command we get this information:


Sounds to me like you haven't told the database what format you're
using.  See the DATESTYLE parameter; you need to select "european"
format to get it to default to dd-before-mm date order.

We need to know if is necessary to install some patch to fix this 
problem or upgrade the database.


An upgrade would be a good idea in any case.  7.1.3 is pretty old.

			regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match
--
===
Ivan E. Rivera Uria
BNC - Systems FPS Manager
Tel. +(52)-55-2614-0510
Fax. +(52)-55-
Email: [EMAIL PROTECTED]
Web: http://www.bnchq.com.mx
==
---(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] finger print (minutias)

2004-10-18 Thread Ivan Esteban Rivera Uria 
Title: finger print (minutias)







Hi.


I would like to know if it posible record a minutias code in Postgre SQL, it is a binary data



Ivan E. Rivera Uria

Company: BNC MEXICO

Phone Number: +(52)-55-5241-0670

Fax Number:   +(52)-55-5241-0680

Direct Number:+(52)-55-5241-0688 

Company Mail: [EMAIL PROTECTED]


The information contained in this communication may be confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notifiedthat any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message and any copy of it from your computer system.

Thank you.