Re: [BUGS] could not read block 77 of relation 1663/16385/388818775

2008-11-26 Thread John R Pierce 

Gregory Stark wrote:

John R Pierce <[EMAIL PROTECTED]> writes:

  

oracle has had an option for some time that uses read/only page protection for
each page of the shared buffer area...   when oracle knows it wants to modify a
page, it un-protects it via a system call. this catches any wild writes
into the shared buffer area as a memory protection fault.



The problem with both of these approaches is that most bugs occur when the
code *thinks* it's doing the right thing. A bug in the buffer management code
which returns the wrong buffer or a real wild pointer dereference. I don't
remember ever having either of those.

That said, the second option seems pretty trivial to implement. I think the
performance would be awful for a live database but for a read-only database it
might make more sense.
  



FWIW, it has modest overhead on Oracle on Solaris on Sparc...  EXCEPT on 
the "Niagra" aka 'Coolthreads'  CPUs (the T1 processor), on that it was 
horribly slow on our write intensive transactional system.Our 
environment is on very large scale servers where the shared buffers are 
often 32 or 64GB, I suspect this increases our exposure to bizarro-world 
writes.


believe me, especially in earlier Oracle releases (6, 7, 8), this 
caught/prevented many problems which otherwise would have ended in a 
Oracle fatal Block Corruption error, which would require many hours of 
DBA hackery before the database could be restarted.




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

[BUGS] PQescapeStringConn in C:\Program Files\PostgreSQL\8.3\lib not found"

2008-11-26 Thread 劉大維 
Dear Sir,

I try to make gammu-svn2643. I run Cmakesetup.exe and then change
the following setting:
where is the source code: d:\gammu-svn2643
where to build the binaries: d:\gammu-svn2643\win32\gammu-library\bcc06.mak
GETTEXT_MSGFMT_EXECUTABLE: C:\GETGNUWIN32\bin
GETTEXT_MSGMERGE_EXECUTABLE: C:\GETGNUWIN32\bin
MYSQL_ADD_INCLUDE_DIR: C:\Program Files\MySQL\MySQL Server 5.0\include
MYSQL_ADD_LIBRARY_PATH: C:\Program Files\MySQL\MySQL Server 5.0\lib\opt
MYSQL_LIBRARIES_mysql: C:\Program Files\MySQL\MySQL Server 5.0\lib\opt
POSTGRES_INCLUDE_DIR: C:\Program Files\PostgreSQL\8.3\include
POSTGRES_LIBRARY: C:\Program Files\PostgreSQL\8.3\lib
And then I click [config] button. But Cmakesetup.exe shows
"Cmake error: invalid escape sequence \p"
"Syntax error in cmake code at D:/gammu-svn2643/cmake/FindPostgres.cmake:73:
syntax error,
unexpected cal_ERRO, expecting $end (38), when parsing string Looking
for PQescapeStringConn in C:\Program Files\PostgreSQL\8.3\lib not found"
"Cmake error: invalid escape sequence \p"
"Syntax error in cmake code at D:/gammu-svn2643/cmake/FindPostgres.cmake:73:
syntax error,
unexpected cal_ERRO, expecting $end (4), when parsing string "Looking
for PQescapeStringConn in C:\Program Files\PostgreSQL\8.3\lib"
"WARNING: Target "cmTryComileExec" requests linking to directory "Syntax
error in cmake code at D:/gammu-svn2643/cmake/FindPostgres.cmake:73: syntax
error,
unexpected cal_ERRO, expecting $end (38), when parsing string "Looking
for PQescapeStringConn in C:\Program Files\PostgreSQL\8.3\lib". Targets may
link
only to libraries. Cmake is dropping the item."
"Cmake error: invalid escape sequence \p"
"Syntax error in cmake code at D:/gammu-svn2643/cmake/FindPostgres.cmake:73:
syntax error,
unexpected cal_ERRO, expecting $end (38), when parsing string 'Looking
for PQescapeStringConn in C:\Program Files\PostgreSQL\8.3\lib not found'"
"WARING: Target 'gammu' request linking to directory 'C:\Program
Files\PostgreSQL\8.3\lib'.
Targets may link only ot libraries. CMake is dropping the item.
Then I type make on cmd
D:\gammu-svn2643\win32\gammu-binary\bcc06.mak>make
MAKE Version 5.2  Copyright (c) 1987, 2000 Borland
MAKE Version 5.2  Copyright (c) 1987, 2000 Borland
MAKE Version 5.2  Copyright (c) 1987, 2000 Borland
Scanning dependencies of target libGammu
MAKE Version 5.2  Copyright (c) 1987, 2000 Borland
[  1%] Building C object common/CMakeFiles/libGammu.dir/gsmcomon.obj
Borland C++ 5.6 for Win32 Copyright (c) 1993, 2002 Borland
D:\gammu-svn2643\common\gsmcomon.c:
Warning W8057 D:\gammu-svn2643\common\gsmcomon.c 73: Parameter 'path' is
never u
sed in function GSM_InitLocales
[  1%] Building C object common/CMakeFiles/libGammu.dir/gsmphones.obj
Borland C++ 5.6 for Win32 Copyright (c) 1993, 2002 Borland
D:\gammu-svn2643\common\gsmphones.c:
[  2%] Building C object common/CMakeFiles/libGammu.dir/gsmstate.obj
Borland C++ 5.6 for Win32 Copyright (c) 1993, 2002 Borland
D:\gammu-svn2643\common\gsmstate.c:
Warning W8066 D:\gammu-svn2643\common\gsmstate.c 1350: Unreachable code in
funct
ion GSM_OSErrorInfo
[  3%] Building C object common/CMakeFiles/libGammu.dir/api.obj
Borland C++ 5.6 for Win32 Copyright (c) 1993, 2002 Borland
D:\gammu-svn2643\common\api.c:
Assertion failed D:\gammu-svn2643\common\api.c(46) : CompilingCPP at
c:\helena\b
cc\indep\cpperror.c(1571)
** error 3 ** deleting common\CMakeFiles\libGammu.dir\api.obj
** error 1 ** deleting common\CMakeFiles\libGammu.dir\all
** error 1 ** deleting all
D:\gammu-svn2643\win32\gammu-binary\bcc06.mak>

Please help to check it.

Thanks!

Best Regards,

sunshine_uyl

Re: [BUGS] PQescapeStringConn in C:\Program Files\PostgreSQL\8.3\lib not found"

2008-11-26 Thread Craig Ringer 
劉大維 wrote:
> Dear Sir,
>  
> I try to make gammu-svn2643.

You're having issues with `cmake' configuration of a project other than
PostgreSQL. This is unlikely to be a PostgreSQL bug.

> "Cmake error: invalid escape sequence \p"
> "Syntax error in cmake code at
> D:/gammu-svn2643/cmake/FindPostgres.cmake:73: syntax error,
> unexpected cal_ERRO, expecting $end (38), when parsing string Looking
> for PQescapeStringConn in C:\Program Files\PostgreSQL\8.3\lib not found"

FindPostgres.cmake is part of the gammu project. You need to contact
them and inform them that their CMake code to detect PostgreSQL is
broken. Make sure to tell them your cmake version, because that is the
first thing they will ask, and also send them your CMakeCache.txt .

This is not a problem with PostgreSQL.

> Then I type make on cmd

That won't work, because CMake configuration didn't work properly. CMake
shouldn't have even generated a Makefile; the fact that it did suggests
that the error handling in the gammu cmake code is far from ideal.

--
Craig Ringer

-- 
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 #4550: ecpg problem with copy command and hostvar

2008-11-26 Thread Michael Meskes 
On Tue, Nov 25, 2008 at 12:28:38PM +, Clemens Fischer wrote:
> The copy command can't be used with a hostvar.

This is essentially the answer. The major difference between the ecpg version
of PostgreSQL 8.2 and 8.3 is that the 8.3 version uses the backend supplied
prepare/execute facility instead of just simulating this feature. Part of this
change was to change the variable handling and no longer construct strings on
the client side but pass the variables to the backend directly. But there is no
way to pass a variable filename for the copy command AFAIK. Or in other words,
the old versions had a bug in that they accepted a variable there. 

Can you use EXECUTE and put the whole statement into a variable instead?

Michael

-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

-- 
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 #4549: ecpg produces code that don't compile

2008-11-26 Thread Michael Meskes 
On Tue, Nov 25, 2008 at 10:44:32AM +, Clemens Fischer wrote:
> Using ecpg and gcc generates the following output:
> ecpg -t -o ecpgtest-multidim.c ecpgtest-multidim.pgc
> cc -c -I/usr/local/pgsql/include ecpgtest-multidim.c
> ecpgtest-multidim.pgc: In function `main':
> ecpgtest-multidim.pgc:30: parse error before `_13'
> cc: /usr/qnx630/host/qnx6/x86/usr/lib/gcc-lib/ntox86/2.95.3/cc1 error 33
> make: *** [ecpgtest-multidim.o] Error 1

Thanks for reporting. Fixed in CVS HEAD and 8.3.

Patch is attached, it was just a small oversight that happened when the new
varchar naming was introduced.

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!
--- pgsql-ecpg/preproc/type.c	2008-05-17 11:09:51.0 +0200
+++ pgsql/src/interfaces/ecpg/preproc/type.c	2008-11-26 14:15:03.0 +0100
@@ -327,8 +327,9 @@
 		fprintf(o, "\n\tECPGt_descriptor, %s, 0L, 0L, 0L, ", name);
 	else
 	{
-		char	   *variable = (char *) mm_alloc(strlen(name) + ((prefix == NULL) ? 0 : strlen(prefix)) + 4);
-		char	   *offset = (char *) mm_alloc(strlen(name) + strlen("sizeof(struct varchar_)") + 1 + strlen(varcharsize) + sizeof(int) * CHAR_BIT * 10 / 3);
+		char *variable = (char *) mm_alloc(strlen(name) + ((prefix == NULL) ? 0 : strlen(prefix)) + 4);
+		char *offset = (char *) mm_alloc(strlen(name) + strlen("sizeof(struct varchar_)") + 1 + strlen(varcharsize) + sizeof(int) * CHAR_BIT * 10 / 3);
+		char *var_name; 
 
 		switch (type)
 		{
@@ -350,10 +351,14 @@
 else
 	sprintf(variable, "&(%s%s)", prefix ? prefix : "", name);
 
+/* remove trailing [] is name is array element */
+var_name = strdup(name);
+*(strchrnul(var_name, '[')) = '\0';
 if (lineno)
-	sprintf(offset, "sizeof(struct varchar_%s_%d)", name, lineno);
+	sprintf(offset, "sizeof(struct varchar_%s_%d)", var_name, lineno);
 else
-	sprintf(offset, "sizeof(struct varchar_%s)", name);
+	sprintf(offset, "sizeof(struct varchar_%s)", var_name);
+free(var_name);
 break;
 			case ECPGt_char:
 			case ECPGt_unsigned_char:

-- 
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 #4550: ecpg problem with copy command and hostvar

2008-11-26 Thread Clemens A. Fischer 
Hallo Michael,

Am Mittwoch, den 26.11.2008, 14:49 +0100 schrieb Michael Meskes:
> On Tue, Nov 25, 2008 at 12:28:38PM +, Clemens Fischer wrote:
> > The copy command can't be used with a hostvar.
> 
> This is essentially the answer. The major difference between the ecpg version
> of PostgreSQL 8.2 and 8.3 is that the 8.3 version uses the backend supplied
> prepare/execute facility instead of just simulating this feature. Part of this
> change was to change the variable handling and no longer construct strings on
> the client side but pass the variables to the backend directly. But there is 
> no
> way to pass a variable filename for the copy command AFAIK. Or in other words,
> the old versions had a bug in that they accepted a variable there. 
> 
> Can you use EXECUTE and put the whole statement into a variable instead?
I switched to EXECUTE yesterday. It works.
> 
> Michael
Thank you

Clemens



-- 
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] could not read block 77 of relation 1663/16385/388818775

2008-11-26 Thread Gregory Stark 

John R Pierce <[EMAIL PROTECTED]> writes:

> Alexandra Nitzschke wrote:
>> BTW... how about a block checksum that is checked just before writing a block
>> and just after reading it? I know this would degrade performance, but I think
>> we can afford that. Would it be possible to incorporate such code without
>> having to do too much patching?
>
> oracle has had an option for some time that uses read/only page protection for
> each page of the shared buffer area...   when oracle knows it wants to modify 
> a
> page, it un-protects it via a system call. this catches any wild writes
> into the shared buffer area as a memory protection fault.

The problem with both of these approaches is that most bugs occur when the
code *thinks* it's doing the right thing. A bug in the buffer management code
which returns the wrong buffer or a real wild pointer dereference. I don't
remember ever having either of those.

That said, the second option seems pretty trivial to implement. I think the
performance would be awful for a live database but for a read-only database it
might make more sense.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

-- 
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 #4549: ecpg produces code that don't compile

2008-11-26 Thread Clemens A. Fischer 
Hallo Michael,

thanks for the patch, but ...
compiling ecpg fails:
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-fno-strict-aliasing -Wno-error  -I../include
-I../../../../src/interfaces/ecpg/include -I. -DMAJOR_VERSION=4
-DMINOR_VERSION=4 -DPATCHLEVEL=1 -I../../../../src/include -D_GNU_SOURCE
-I/opt/include   -c -o type.o type.c
type.c: In function `ECPGdump_a_simple':
type.c:356: warning: implicit declaration of function `strchrnul'
type.c:356: invalid type argument of `unary *'
make: *** [type.o] Error 1

Is it possible to expand 'strchrnul' to
if(var_name != NULL) *(strchr( ...
or something like that?

Clemens

Am Mittwoch, den 26.11.2008, 14:20 +0100 schrieb Michael Meskes:
> On Tue, Nov 25, 2008 at 10:44:32AM +, Clemens Fischer wrote:
> > Using ecpg and gcc generates the following output:
> > ecpg -t -o ecpgtest-multidim.c ecpgtest-multidim.pgc
> > cc -c -I/usr/local/pgsql/include ecpgtest-multidim.c
> > ecpgtest-multidim.pgc: In function `main':
> > ecpgtest-multidim.pgc:30: parse error before `_13'
> > cc: /usr/qnx630/host/qnx6/x86/usr/lib/gcc-lib/ntox86/2.95.3/cc1 error 33
> > make: *** [ecpgtest-multidim.o] Error 1
> 
> Thanks for reporting. Fixed in CVS HEAD and 8.3.
> 
> Patch is attached, it was just a small oversight that happened when the new
> varchar naming was introduced.
> 
> Michael


-- 
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 #4549: ecpg produces code that don't compile

2008-11-26 Thread Tom Lane 
"Clemens A. Fischer" <[EMAIL PROTECTED]> writes:
> Hallo Michael,
> thanks for the patch, but ...
> compiling ecpg fails:
> type.c: In function `ECPGdump_a_simple':
> type.c:356: warning: implicit declaration of function `strchrnul'
> type.c:356: invalid type argument of `unary *'

Whatever strchrnul is, it isn't very portable --- the buildfarm is
breaking out in red too.  Please change that.

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 #4549: ecpg produces code that don't compile

2008-11-26 Thread John R Pierce 

Tom Lane wrote:

Whatever strchrnul is, it isn't very portable --- the buildfarm is
breaking out in red too.  Please change that.
  



sigh, more gnuish code



— Function: char * *strchrnul* (const char *string, int c)

   |strchrnul| is the same as |strchr| except that if it does not find
   the character, it returns a pointer to string's terminating null
   character rather than a null pointer.

   This function is a GNU extension.





--
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 #4549: ecpg produces code that don't compile

2008-11-26 Thread Michael Meskes 
On Wed, Nov 26, 2008 at 06:44:11AM -0800, John R Pierce wrote:
> Tom Lane wrote:
>> Whatever strchrnul is, it isn't very portable --- the buildfarm is
>> breaking out in red too.  Please change that.
>
> sigh, more gnuish code

Fixed. Sorry, didn't notice that it still is a GNU extension as GNU has for
quite a long time.

Michael

-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

-- 
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 #4533: Plpgsql complex type failure

2008-11-26 Thread Rod Taylor 
That was the short form exhibiting the bug. Selecting the complex
column from a table into a value results in the same error.


BEGIN;

CREATE TYPE timestamp_with_precision AS
( timestamp timestamp with time zone
, timestamp_precision varchar
);

CREATE TABLE timetest(t timestamp_with_precision);
INSERT INTO timetest VALUES ((CURRENT_DATE, 'day')::timestamp_with_precision);

CREATE OR REPLACE FUNCTION testfunc() RETURNS boolean AS $FUNC$
DECLARE
 v_time timestamp_with_precision;
BEGIN
 SELECT t
   INTO v_time
   FROM timetest;

 RETURN true;
END;
$FUNC$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER RETURNS NULL ON NULL
INPUT;

SELECT testfunc();

ROLLBACK;





On Sat, Nov 22, 2008 at 1:37 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Rod Taylor" <[EMAIL PROTECTED]> writes:
>> DECLARE
>>   v_time timestamp_with_precision;
>> BEGIN
>>   SELECT (CURRENT_DATE, 'day')::timestamp_with_precision
>> INTO v_time;
>
> You're overthinking the problem, it should just read
>
>  SELECT CURRENT_DATE, 'day'
>INTO v_time;
>
>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 #4547: sort columns in \d

2008-11-26 Thread Ferdinand Gassauer 
>Calling this a "bug" isn't a good way to start a discussion about it.
sorry, didn't want to insult anyone

All your comments are certainly valid.

I had to inspect and maintain a database which was not designed by me and has 
tables with some 20-30 columns - no way to understand which columns are in a 
table with the actual \d
OK other way is to use a graphical client...

may be a \D which sorts alphabetically  ?

thanks for listening
-- 
cu
ferdinand

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

[BUGS] pg_dumpall restore fails

2008-11-26 Thread Shaun Crampton 
Hi,

I'm trying to upgrade my server from Ubuntu Feisty to a later version and I
think I've found a bug in pg_dumpall.  The version of Postgres from Feisty
is not supported in later versions so I want to dump the DB and restore
after upgrade.

On the server machine I have

   - Postgres 7.4.13 running with my data in it.
   - Postgres 8.3.1 (latest version available on Feisty).

On my test machine I have Postgres 8.3.5.

I tried to dump the 7.4.13 database using pg_dumpall from 8.3.1 and restore
into 8.3.5 on my local test machine.  I get the following errors at restore
time:

ERROR:  missing FROM-clause entry for table "icc_countries"
LINE 2: ...de, icc_members.county, icc_states.name AS state, icc_countr...
 ^
ERROR:  relation "public.icc_members_test" does not exist
ERROR:  missing FROM-clause entry for table "icc_countries"
LINE 2: ...name AS state, icc_states.abbrev AS state_abbrev, icc_countr...
 ^
ERROR:  relation "public.icc_members_with_admin" does not exist
ERROR:  missing FROM-clause entry for table "icc_countries"
LINE 2: ...de, icc_members.county, icc_states.name AS state, icc_countr...
 ^
ERROR:  relation "public.icc_members_with_country" does not exist

It looks like pg_dumpall is generating malformed SQL for the dumped views.
I've attached the output from "pg_dumpall -c -s" as schema_dump.sql and the
full output of that running psql 8.3.5 with that as input as
psql_output.log.

Regards,

-Shaun
--
-- PostgreSQL database cluster dump
--

\connect postgres

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET escape_string_warning = 'off';

--
-- Roles
--

DROP ROLE apache;
CREATE ROLE apache;
ALTER ROLE apache WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN;
DROP ROLE postgres;
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN;
DROP ROLE shaun;
CREATE ROLE shaun;
ALTER ROLE shaun WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN;




--
-- Database creation
--

DROP DATABASE apache;
CREATE DATABASE apache WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'SQL_ASCII';
DROP DATABASE shaun;
CREATE DATABASE shaun WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'SQL_ASCII';
REVOKE ALL ON DATABASE template1 FROM PUBLIC;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;
REVOKE ALL ON DATABASE template1 FROM postgres;
GRANT CREATE,TEMPORARY ON DATABASE template1 TO postgres WITH GRANT OPTION;


\connect apache

--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = true;

--
-- Name: icc_admin_areas; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE icc_admin_areas (
id integer DEFAULT nextval('public.icc_admin_areas_id_seq'::text) NOT NULL,
name character varying(255) NOT NULL,
label_template character varying(255),
label_body_template character varying(255)
);


ALTER TABLE public.icc_admin_areas OWNER TO apache;

--
-- Name: icc_admin_areas_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

CREATE SEQUENCE icc_admin_areas_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;


ALTER TABLE public.icc_admin_areas_id_seq OWNER TO apache;

--
-- Name: icc_admin_areas_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE icc_admin_areas_id_seq OWNED BY icc_admin_areas.id;


--
-- Name: icc_countries; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE icc_countries (
id integer DEFAULT nextval('public.icc_countries_id_seq'::text) NOT NULL,
name character varying(255) NOT NULL,
admin_by integer,
iso character(2),
iso3 character(3),
numcode integer
);


ALTER TABLE public.icc_countries OWNER TO apache;

--
-- Name: icc_countries_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

CREATE SEQUENCE icc_countries_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;


ALTER TABLE public.icc_countries_id_seq OWNER TO apache;

--
-- Name: icc_countries_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE icc_countries_id_seq OWNED BY icc_countries.id;


--
-- Name: icc_globals; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE icc_globals (
id integer DEFAULT nextval('public.icc_globals_id_seq'::text) NOT NULL,
name character varying(255),
value text
);


ALTER TABLE public.icc_globals OWNER TO apache;

--
-- Name: icc_globals_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

CREATE SEQUENCE icc_globals_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;


ALTER TABL

Re: [BUGS] pg_dumpall restore fails

2008-11-26 Thread Tom Lane 
"Shaun Crampton" <[EMAIL PROTECTED]> writes:
> I tried to dump the 7.4.13 database using pg_dumpall from 8.3.1 and restore
> into 8.3.5 on my local test machine.  I get the following errors at restore
> time:

> ERROR:  missing FROM-clause entry for table "icc_countries"
> LINE 2: ...de, icc_members.county, icc_states.name AS state, icc_countr...
>  ^

Per the 8.1 release notes:

  It might be necessary to set add_missing_from to true
  in order to load an existing dump file, if the dump contains any
  views or rules created using the implicit-FROM syntax.
  This should be a one-time annoyance, because
  PostgreSQL 8.1 will convert
  such views and rules to standard explicit-FROM syntax.
  Subsequent dumps will therefore not have the problem.

Going from 7.4 to 8.3 in one jump is a bit ambitious; you should expect
to learn about a few compatibility issues the hard way.  Start by
reading the release notes for each version in between, *particularly*
the compatibility warnings.

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 in RETURN QUERY

2008-11-26 Thread raf 
Tom Lane wrote:

> "Pavel Stehule" <[EMAIL PROTECTED]> writes:
> > 2008/9/1 Oleg Serov <[EMAIL PROTECTED]>:
> >> ERROR:  structure of query does not match function result type
> >> CONTEXT:  PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/
> 
> > this isn't bug, it's feature.
> 
> No, it's a bug, and it's not related to plan caching at all --- even if
> you start a fresh session the error persists.  The problem is that
> plpgsql isn't very good at dealing with rowtypes that contain dropped
> columns.  Unfortunately Oleg shouldn't hold his breath waiting for a
> fix, because it's not trivial.  In this example, the function would need
> to return a three-column tuple (id, dropped-column, buggy_enum_field)
> but the SELECT is only giving it two columns.  There isn't anything in
> plpgsql that has the ability to convert a tuple to add dropped columns
> in the right places.  I think we'd consider adding such functionality
> as a new feature not a back-patchable bug fix.
> 
> The best near-term workaround would be to handle changes like this by
> means of ALTER COLUMN TYPE rather than dropping and re-adding columns.
> 
>   regards, tom lane

hi tom,

i've just come across this bug as well as soon as i dropped
some columns (demonstration code below for those anyone who
can't remember the bug).

i hope i misunderstood your suggested fix. i strongly
disagree that the fix is to make it possible for plpgsql to
add dropped columns to queries (either automatically or via
some explicit syntactic device). the dropped column was
dropped after all. it should stay dropped. i don't think
anyone would want to add dropped columns to any result sets.

it's the automatic rowtype of the table that is wrong,
because it has not been updated (and apparently needs to be)
to reflect the current state of the table.

i hope this does get fixed or dropping columns is a big mistake.
i guess i won't be doing that again :)

cheers,
raf

--- demonstrate: dropped column breaks rowtypes -
create table bug (a integer null, b integer null, c integer null);

insert into bug (a, b, c) values (1, 2, 3);

create or replace function bug1()
returns setof bug stable language plpgsql as
$$ begin return query select * from bug; end $$;

select * from bug1();
-- Works

alter table bug drop b;

-- Ending/restarting session here makes no difference
select * from bug1();
-- ERROR:  structure of query does not match function result type

-- Recreating the function explicitly makes no difference
create or replace function bug1()
returns setof bug stable language plpgsql as
$$ begin return query select * from bug; end $$;

-- Ending/restarting session here makes no difference
select * from bug1();
-- ERROR:  structure of query does not match function result type

drop table if exists bug cascade;


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