Re: [BUGS] Not a real bug, but an error
Christopher Kings-Lynne has made pg_dump and pg_dumpall much more reliable in this respect for 8.0. Ð ÐÑÐ, 28.10.2004, Ð 16:46, Andrea D. ÐÐÑÐÑ: > Hi, I've just upgraded Roma 7.3.x to 7.4.5 on a Mandrake 10.1 community. > I've used pg_dumpall (Roma version 7.4.5) to dump my previous databases to a > file in plain text form and then trend to restore using 'psql -f'. > But there was an error: a function needed in a constraint of a table was > placed after the table definition! So it was impossible to create the > table! > It was just a matter to edit the file and cut and copy the function > definition in the right place, drop the database and run the file again, > but it could be a good idea to find a workaround. Actually, I had to dump > the schema and data separately, as one file was to big to manage, but it > was just a matter of confort. > Thank for the great work. > Postgresql is really a wonderfull thing. > Andrea from Italy > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] minor issue with psql keeping connections
pgsql running on port 5480, connect to template1 as non-superuser "phppgadmin"
who does not have createdb privileges. attempt to run a script that creates a
database "phppgadmin" and then do a \connect to that database. this fails
(as it should) but when it does, it loses the connection to template1, and i
am not able to reconnect to any database, i think because it is looking on
port 5432 rather than the 5480 i specified when i originally connected.
[EMAIL PROTECTED]:/usr/local/pgsql-8.0.0beta-4/bin$ ./psql -p5480 template1
-U phppgadmin
Welcome to psql 8.0.0beta4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
template1=> select version();
version
PostgreSQL 8.0.0beta4 on i586-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)
template1=> \i /usr/local/apache/htdocs/phppgadmin3cvs/sql/reports-pgsql.sql
ERROR: permission denied to create database
psql:/usr/local/apache/htdocs/phppgadmin3cvs/sql/reports-pgsql.sql:7: ERROR:
permission denied to create database
FATAL: database "phppgadmin" does not exist
psql:/usr/local/apache/htdocs/phppgadmin3cvs/sql/reports-pgsql.sql:9:
\connect: FATAL: database "phppgadmin" does not exist
!> \c template1
could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
!>
!>
relativily minor issue, but seems like it should either be able to retain the
connection when inside the script, or recall that it needs to connect on 5480
after failure.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] minor issue with psql keeping connections
On Sunday 31 October 2004 10:16, Robert Treat wrote:
> pgsql running on port 5480, connect to t\echo :PORTemplate1 as non-superuser
> "phppgadmin" who does not have createdb privileges. attempt to run a script
> that creates a database "phppgadmin" and then do a \connect to that
> database. this fails (as it should) but when it does, it loses the
> connection to template1, and i am not able to reconnect to any database, i
> think because it is looking on port 5432 rather than the 5480 i specified
> when i originally connected.
>
> [EMAIL PROTECTED]:/usr/local/pgsql-8.0.0beta-4/bin$ ./psql -p5480
> template1 -U phppgadmin
> Welcome to psql 8.0.0beta4, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
>\h for help with SQL commands
>\? for help with psql commands
>\g or terminate with semicolon to execute query
>\q to quit
>
> template1=> select version();
> version
>
> PostgreSQL 8.0.0beta4 on i586-pc-linux-gnu, compiled by GCC 2.95.3
> (1 row)
>
> template1=> \i
> /usr/local/apache/htdocs/phppgadmin3cvs/sql/reports-pgsql.sql ERROR:
> permission denied to create database
> psql:/usr/local/apache/htdocs/phppgadmin3cvs/sql/reports-pgsql.sql:7:
> ERROR: permission denied to create database
> FATAL: database "phppgadmin" does not exist
> psql:/usr/local/apache/htdocs/phppgadmin3cvs/sql/reports-pgsql.sql:9:
> \connect: FATAL: database "phppgadmin" does not exist
> !> \c template1
> could not connect to server: No such file or directory
> Is the server running locally and accepting
> connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
> !>
> !>
>
> relativily minor issue, but seems like it should either be able to retain
> the connection when inside the script, or recall that it needs to connect
> on 5480 after failure.
little addition... the variable :PORT is unsert after the script bombs. psql
assumes that it should be connecting on 5432 rather than what would normally
be aqailable in :PORT and so it will reconnect if you are on a standard port.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] date_trunc problems crossing DST border
"Matthew \"Cheetah\" Gabeler-Lee" <[EMAIL PROTECTED]> writes:
> [ PostgreSQL 7.4.5, Linux (Debian sarge) ]
> The date_trunc function is making some off-by-one errors when working at
> a DST border.
> => select date_trunc('minute', '2004-10-31 01:00:00-05'::timestamptz);
>date_trunc
>
> 2004-10-31 01:00:00-04
This is not an off-by-one error. What it's doing is taking the
truncated timestamp value as current local time (ie, it recomputes
the appropriate timezone offset), and so you get the equivalent of
'2004-10-31 01:00:00'::timestamptz which is interpreted as DST.
(That surprises me in itself --- I thought the rule for ambiguous
times was to use the local-standard-time interpretation --- but
the point here is about date_trunc.)
I think it is reasonable for it to be recalculating the TZ offset with
trunc levels of DAY or more, since otherwise you might get a result that
should be local midnight and isn't. Arguably the recalculation is wrong
for levels of HOUR or less though. Normally it would make no difference,
and where it does make a difference the result is evidently surprising.
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
[BUGS] psql: set \encoding according to the current locale
Hi PostgreSQL developers! Currently (i. e. in 7.4.6) psql seems to use the default database encoding as locale for both its own messages and for database output strings. Whereas it is only an inconvenience for the latter, psql's own messages should really respect LANG/LC_MESSAGES environment variables instead of the database encoding. It would also be nice to call "set \encoding" to match the locale psql was called under, at least when it is called interactively. Thanks and have a nice day! Martin -- Martin Pitt http://www.piware.de Ubuntu Developerhttp://www.ubuntulinux.org Debian GNU/Linux Developer http://www.debian.org signature.asc Description: Digital signature
Re: [BUGS] could not find /usr/local/timezone
Tom, > Hmmm ... where is that coming from exactly? PG itself should not be > referring to /usr/share/timezone --- we have our own TZ database now. > And glibc-based platforms don't keep their info there either (it's in > /usr/share/zoneinfo, at least on my Fedora machine). So there's > something awfully odd here. That's postmaster's feedback on startup. Here's a clue: when I tried to enable automated log rotation (using the default filenaming scheme) I got the same error but it was fatal. Let me know if you want a trace. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] could not find /usr/local/timezone
Josh Berkus <[EMAIL PROTECTED]> writes: >> Hmmm ... where is that coming from exactly? PG itself should not be >> referring to /usr/share/timezone --- we have our own TZ database now. > Let me know if you want a trace. Please. Also, what nondefault configuration or postgresql.conf settings are you using? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] Some patches to enhance the contrib build
Hi PostgreSQL developers! The current Debian package has some patches that tweak the building of contrib modules. I think they would be interesting for other distributions, too. Most of this stuff was contributed by users who actually use these modules. 25contrib-dbmirror: - additionally install some useful scripts and data files 25contrib-enablemysql: - enable mysql module build - add Makefile - fix hashbang of mysql2pgsql 25contrib-enableoracle: - enable oracle module build - add Makefile - patch ora2pg.pl to look for Ora2Pg.pm additionally in the path where ora2pg.pl is installed 25contrib-enablexml: - enable xml module build - Makefile: add CFLAGS for "-I/usr/include/libxml2" to allow building 26dbf2pg-errorcheck: - add better error checking to dbf2pg 27dbf2pg-textfield: - add dbf2pg support for the dbase field 'M' which corresponds to PostgreSQL's TEXT field Do you consider adopting them in the official version? Thanks a lot and have a nice day! Martin -- Martin Pitt http://www.piware.de Ubuntu Developerhttp://www.ubuntulinux.org Debian GNU/Linux Developer http://www.debian.org signature.asc Description: Digital signature
Re: [BUGS] could not find /usr/local/timezone
Tom, > Please. Also, what nondefault configuration or postgresql.conf settings > are you using? Will have to get the trace tommorrow. Config options are --with-perl and --with-odbc -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Some patches to enhance the contrib build
On Mon, 2004-11-01 at 10:49, Martin Pitt wrote: > The current Debian package has some patches that tweak the building of > contrib modules. I think they would be interesting for other > distributions, too. Most of this stuff was contributed by users who > actually use these modules. Thanks for letting us know. BTW, the information in postgresql-7.4.6/debian/copyright is out of date (old download URL, old PostgreSQL copyright dates & the regex copyright should be updated per the license in src/backend/regex/COPYRIGHT). 20pg_dump_datestyle seems redundant (we already set DateStyle to ISO in pg_dump). > 25contrib-dbmirror: > - additionally install some useful scripts and data files Makes sense to me, although I won't claim to be a dbmirror user. I'll apply this to HEAD unless anyone objects. > 25contrib-enablexml: > - enable xml module build > - Makefile: add CFLAGS for "-I/usr/include/libxml2" to allow building ISTM the right fix is to use xml2-config. > 25contrib-enablemysql: > - enable mysql module build > - add Makefile > - fix hashbang of mysql2pgsql > > 25contrib-enableoracle: > - enable oracle module build > - add Makefile > - patch ora2pg.pl to look for Ora2Pg.pm additionally in the path >where ora2pg.pl is installed > > 26dbf2pg-errorcheck: > - add better error checking to dbf2pg > > 27dbf2pg-textfield: > - add dbf2pg support for the dbase field 'M' which corresponds to >PostgreSQL's TEXT field I can't find these patches in this diff: http://ftp.debian.org/debian/pool/main/p/postgresql/postgresql_7.4.6-2.diff.gz could you tell me where to find them? -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] Some patches to enhance the contrib build
Neil Conway <[EMAIL PROTECTED]> writes: > On Mon, 2004-11-01 at 10:49, Martin Pitt wrote: >> 25contrib-dbmirror: >> - additionally install some useful scripts and data files > Makes sense to me, although I won't claim to be a dbmirror user. I'll > apply this to HEAD unless anyone objects. Steve Singer, the author and maintainer of dbmirror, is still around... so it would be polite to get his concurrence first. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] possible bug using combination of 'serial' and rule
Hi, I think this is a bug (I hope not a feature). Description: The table 'tmp' gets records added, and uses a serial to fill the attribute 'strorage_id'. The table has a rule which logs all inserts into the table 'log'. Problem: For each insert into table 'tmp' the serial counter is increased twice, once to create the entry in 'tmp', once for 'log'. The problem is that the rule does not see the correct 'storage_id'!!! You can see that the 'tmp' table only contains odd storage_ids, while the log table only contains even ones. The problem can be reproduced with postgresql 8.0.0beta4 but also with 7.4.1. So it does not seem to be new. Example: --- create table log ( storage_id integer ); create table tmp ( storage_id serial not null, location_id integer ); create or replace rule INS_STORAGE as on INSERT to tmp do (insert into log (storage_id) values (NEW.storage_id); ); -- Now fill the table: insert into tmp (location_id) values (1); insert into tmp (location_id) values (1); test=# select * from tmp; storage_id | location_id +- 1 | 1 3 | 1 (2 rows) test=# select * from log; storage_id 2 4 (2 rows) ---(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
[BUGS] Bug report for Postgresql 8.0-beta1 for WINDOWS.
Hello pgsql-bugs, The bug description is as follows: Then server with running postmaster unexpectedly shut down, server cannot be started next time. I've found that manual starting of that service also unsuccessful. I investigated the bug, and found that the .pid file exists at PGDATA folder. So I decided to delete that file, and try to start service. It works! Probably pg_ctl looks for .pid file, and if it exists it supposes that the service is already started. In fact it's wrong. How to repeat the bug: 1. Stop service 2. Create postmaster.pid at PGDATA 3. Try to start service. It will refuse. Suggested solution: Change service detection logic. May be not just looking for the pid file, but parsing it and finding the service process itself and testing it for existance. Sorry if it is already fixed in new version. -- Best regards, Sergey mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] locale related problem with debian
in response to; tar -jxf postgresql-8.0.0beta4.tar.bz2 cd postgresql-8.0.0beta4 ./configure make check combination, i received the following message. postgres 7.4.3 makes check fine (with a problem today caused by daylight saving) *snip* /bin/sh ./pg_regress --temp-install --top-builddir=../../.. --schedule=./parallel_schedule --multibyte=SQL_ASCII == creating temporary installation== == initializing database system == == starting postmaster== running on port 65432 with pid 11316 == creating database "regression" == CREATE DATABASE pg_regress: could not set database default locales make[2]: *** [check] Error 2 rm regress.o make[2]: Leaving directory `/home/dave/postgresql-8.0.0beta4/src/test/regress' make[1]: *** [check] Error 2 make[1]: Leaving directory `/home/dave/postgresql-8.0.0beta4/src/test' make: *** [check] Error 2 [EMAIL PROTECTED]:~/postgresql-8.0.0beta4$ [EMAIL PROTECTED]:~$ uname -a Linux summit 2.6.7 #1 SMP Sun Jul 18 08:02:18 EST 2004 i686 unknown Sorry for the brevity of the report. Would be happy to respond with more detail if requested. Uru -David Dick ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
