[BUGS] BUG #5270: escape warning even with standard behavior

[Lothar]

The following bug has been logged online:

Bug reference:  5270
Logged by:  Lothar
Email address:  lotharbonga...@hotmail.com
PostgreSQL version: 8.4
Operating system:   Windows XP Professioanl
Description:escape warning even with standard behavior
Details: 

I am migrating from a hug SQL Server project.
I have to import a 4G database.

To switch off the escape character behavior, I changed the entry in
postgresql.conf to 
#standard_conforming_strings = on

Despite of this, I get following warnings when importing data:
State=22P06, Err=-1, Msg=WARNING: nonstandard use of escape in a string
literal

According to the documentation I should not get this warning.

Do I have to use another measurement additionally to the postgresql.conf
change, if I do not like characters to be treated as escape characters?

The warnings are by nature always related with error return codes for the
sql commands and therefore are destroying the intended program flow.

-- 
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 #5310: "NOT FOUND" throws "GetData to Procedure return failed." in stored functions

[Lothar Bongartz]

The following bug has been logged online:

Bug reference:  5310
Logged by:  Lothar Bongartz
Email address:  lotharbonga...@hotmail.com
PostgreSQL version: 8.4
Operating system:   Windows XP Professioanl
Description:"NOT FOUND" throws "GetData to Procedure return failed."
in stored functions
Details: 

A "NOT FOUND" condition in a stored function throws a "GetData to Procedure
return failed." error, which cannot be trapped by the EXCEPTION handling.
Example:

SELECT msg_id INTO v_nm FROM newmail WHERE memb_id=v_id;

The only way to avoid the complete failing of the stored function is to do a
check before:

IF EXISTS (SELECT * FROM newmail WHERE memb_id=v_id) THEN
SELECT msg_id INTO v_nm FROM newmail WHERE memb_id=v_id;
END IF;

-- 
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 #5395: UPDATE on shutdown overwrites table

[Lothar Bongartz]

The following bug has been logged online:

Bug reference:  5395
Logged by:  Lothar Bongartz
Email address:  lotharbonga...@hotmail.com
PostgreSQL version: 8.4.3
Operating system:   Windows Server 2003 R2
Description:UPDATE on shutdown overwrites table
Details: 

I have ported a community with about one million members from MS SQL to
Postgres.
While the overall performance is comparable, I have noticed, that
conditional bulk writing can be extremely slow in Postgres:
SELECT ... INTO ... WHERE
UPDATE ... FROM ... WHERE
For this reason the database is stalling from time to time and I have to
restart the server. For the second time I have detected, that Postgres
overwrites a table when shutting down. The table  is only
updated with NOW() and only for a single matching row:
UPDATE onlineinfo SET date_end=NOW() WHERE memb_id=v_id
When this command is executed while Postgres shuts down, all rows contain a
"random" date like '2007-06-25' in the  column afterwards.
To repair this, I have created a table from a backup:
CREATE TEMP TABLE temp_onlineinfo_bak
(
  memb_id integer NOT NULL,
  date_end timestamp NOT NULL
 )
After filling the temporary table from the backup, I use it to repair the
overwritten table:
UPDATE onlineinfo SET date_end=B.date_end
 FROM onlineinfo O, temp_onlineinfo_bak B
 WHERE O.date_end<'2010-03-21' AND B.memb_id=O.memb_id;
The WHERE condition is needed to prevent overwriting rows, which are updated
in the meantime.

Unfortunately this command needs several hours to complete, since this is
again a conditional bulk writing.

I tried all kind of variations like removing the index on
 before the update or setting up an index on


Nothing helps. These are the data of the table:
CREATE TABLE onlineinfo
(
  memb_id integer NOT NULL,
  log_count integer NOT NULL,
  date_end timestamp NOT NULL,
  ip integer NOT NULL,
  CONSTRAINT onlineinfo_pkey PRIMARY KEY (memb_id),
  CONSTRAINT fk__onlineinf__memb___55009f39 FOREIGN KEY (memb_id)
  REFERENCES member (memb_id) MATCH FULL
  ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE onlineinfo OWNER TO postgres;

-- Index: onlineinfo_date_end

-- DROP INDEX onlineinfo_date_end;

CREATE INDEX onlineinfo_date_end
  ON onlineinfo
  USING btree
  (date_end);

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