[BUGS] BUG #3435: problem with substring function

2007-07-10 Thread Carlos Quintero 

The following bug has been logged online:

Bug reference:  3435
Logged by:  Carlos Quintero
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.2.4
Operating system:   Linux RedHat Enterprise 3
Description:problem with substring function
Details: 

Hi, 

I like to use a numerical expression as parameter for the substring
function, but it works always as i use regular expressions. Please, try
this:

select substring('1234' from to_number('3', '99') for 3);

What returns a empty string:

 substring 
---
 
(1 fila)

But the right result must be this:

select substring('1234' from 3 for 3); 

 substring 
---
 34
(1 fila)

If i use other numbers, i got even some errors:

select substring('1234' from to_number('33', '99') for 3);

ERROR:  la expresión regular no es válida: invalid backreference number
CONTEXTO:  función SQL «substring» en la sentencia 1

This is a sample, really i like to use an expression using date_part() as
the first parameter for substring.


Best Regards,
Carlos

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

Re: [BUGS] BUG #3435: problem with substring function

2007-07-10 Thread Pavel Stehule 

Hello

It has little bit strange behave on 8.3. It works well, but I have to
use casting.

postgres=# select substring('1234' from to_number('3', '99') for 3);
ERROR:  function pg_catalog.substring(unknown, numeric, integer) does not exist
LINE 1: select substring('1234' from to_number('3', '99') for 3)...
  ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

postgres=# select substring('1234' from to_number('3', '99')::int for 3);
substring
---
34
(1 row)

Regards
Pavel Stehule



2007/7/10, Carlos Quintero <[EMAIL PROTECTED]>:


The following bug has been logged online:

Bug reference:  3435
Logged by:  Carlos Quintero
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.2.4
Operating system:   Linux RedHat Enterprise 3
Description:problem with substring function
Details:

Hi,

I like to use a numerical expression as parameter for the substring
function, but it works always as i use regular expressions. Please, try
this:

select substring('1234' from to_number('3', '99') for 3);

What returns a empty string:

 substring
---

(1 fila)

But the right result must be this:

select substring('1234' from 3 for 3);

 substring
---
 34
(1 fila)

If i use other numbers, i got even some errors:

select substring('1234' from to_number('33', '99') for 3);

ERROR:  la expresión regular no es válida: invalid backreference number
CONTEXTO:  función SQL «substring» en la sentencia 1

This is a sample, really i like to use an expression using date_part() as
the first parameter for substring.


Best Regards,
Carlos

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



---(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 #3436: inherited primary keys are not reported as primary key by the Java DatabaseMetaData

2007-07-10 Thread FAGOT Alain 

The following bug has been logged online:

Bug reference:  3436
Logged by:  FAGOT Alain
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.2.4
Operating system:   Win32
Description:inherited primary keys are not reported as primary key
by the Java DatabaseMetaData
Details: 

the last tests were issued with postgresql-8.2-505.jdbc3.jar
for a detailled description take a look at 
http://sourceforge.net/tracker/index.php?func=detail&aid=1563717&group_id=54
687&atid=474467

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

   http://archives.postgresql.org

Re: [BUGS] BUG #3435: problem with substring function

2007-07-10 Thread Tom Lane 
"Pavel Stehule" <[EMAIL PROTECTED]> writes:
> It has little bit strange behave on 8.3. It works well, but I have to
> use casting.

> postgres=# select substring('1234' from to_number('3', '99')::int for 3);
>  substring
> ---
>  34
> (1 row)

Yeah, the OP is getting burnt by an implicit cast to text, which the
parser picks because there is no implicit cast from numeric to integer
... but substring(text,text,text) has completely different behavior
from substring(text,int,int).

The reason we got rid of most implicit casts to text for 8.3 was exactly
to stop surprising choices like this one.

>> This is a sample, really i like to use an expression using date_part() as
>> the first parameter for substring.

There's no implicit cast from float8 to integer, either.

regards, tom lane

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

Re: [BUGS] BUG #3436: inherited primary keys are not reported as primary key by the Java DatabaseMetaData

2007-07-10 Thread Tom Lane 
"FAGOT Alain" <[EMAIL PROTECTED]> writes:
> Description:inherited primary keys are not reported as primary key
> by the Java DatabaseMetaData

There's no such thing as an inherited primary key.  See
http://www.postgresql.org/docs/8.2/static/ddl-inherit.html#DDL-INHERIT-CAVEATS

regards, tom lane

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

[BUGS] exit code -1073741819

2007-07-10 Thread Shuo Liu 

Hi, All,

I'm working on a GIS project using PostgreSQL and PostGIS. In the project I 
need to find locations of about 12K addresses (the process is referred to as 
geocoding). I wrote some script to perform this task by calling a procedure 
"tiger_geocoding" that is provided by PostGIS. My script seems to crash the 
server after a while with the following message:


server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost


The log shows the following message:


CurTransactionContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExecutorState: 122880 total in 4 blocks; 1912 free (9 chunks); 120968 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ExprContext: 8192 total in 1 blocks; 8000 free (1 chunks); 192 used
ExprContext: 8192 total in 1 blocks; 8000 free (1 chunks); 192 used
ExprContext: 8192 total in 1 blocks; 8096 free (1 chunks); 96 used
SPI Exec: 0 total in 0 blocks; 0 free (0 chunks); 0 used
SPI Proc: 8192 total in 1 blocks; 2616 free (0 chunks); 5576 used
ExecutorState: 57344 total in 3 blocks; 35776 free (7 chunks); 21568 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
2007-07-10 12:25:57 LOG: server process (PID 2004) exited with exit code 
-1073741819
2007-07-10 12:25:57 LOG: terminating any other active server processes
2007-07-10 12:25:59 WARNING: terminating connection because of crash of another 
server process


My script is listed below:

CREATE OR REPLACE FUNCTION geocode_fall2006 () RETURNS VOID AS $$
DECLARE
 address VARCHAR(255);
 geom GEOMETRY;
 tmp_geom GEOMETRY;
 counter INTEGER := 0;
 app_id VARCHAR(50);
 st VARCHAR(50);
 f_processed BOOLEAN;
 f_geocoded BOOLEAN;
BEGIN
 FOR app_id, address, st, f_processed, f_geocoded IN SELECT APPLICATION_ID, 
add, state, geoprocessed, geocoded FROM fall2006 LOOP
  RAISE NOTICE 'add=%, app_id=%, state=%', address, app_id, st;
  IF upper(st)='OH' AND f_processed='f'
  THEN
   geom := geocode_test(address);
   IF geom IS NOT NULL
   THEN
counter := counter + 1;
-- TIGER SRID is 32767.
tmp_geom := PointFromText('POINT(' || X(geom) || ' ' || 
Y(geom) || ')',4269);
tmp_geom := transform(tmp_geom,4326);
-- id := SRID(tmp_geom);
-- RAISE NOTICE 'srid=%', id;
UPDATE fall2006 SET lat_lon = tmp_geom WHERE APPLICATION_ID 
= app_id;
UPDATE fall2006 SET geocoded = 't' WHERE APPLICATION_ID = 
app_id;
RAISE NOTICE 'UPDATE fall2006 SET lat_lon = % WHERE 
APPLICATION_ID = %;', AsText(tmp_geom), app_id;
   END IF;
  UPDATE fall2006 SET geoprocessed = 't' WHERE APPLICATION_ID = app_id;
  END IF;
 END LOOP;
 RAISE NOTICE 'counter=%', counter;
END;
$$ LANGUAGE plpgsql


I googled and found a similar bug was reported for version 8.1 and was claimed 
to be fixed (http://archives.postgresql.org/pgsql-bugs/2006-12/msg00214.php). 
However, the PostgreSQL in my machine is 8.2.4, which is supposed to be free of 
the bug. Any suggestion will be greatly appreciated.




___
Join Excite! - http://www.excite.com
The most personalized portal on the Web!



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