Re: [BUGS] low performance
Is this running as one transaction, or is it not a transaction? Have you tried committing every 10,000 or so if it is in one transaction? It could be a logging problem with the transaction being too big. Does the file system as a whole get slow, or just Postgres? Is it one connection, or does it disconnect and reconnect a lot? Is it the main postmaster sucking up all the CPU, or the one spawned by the PERL, or the one spawned by psql? How much do the file system cache and io buffers grow? __ Your mouse has moved. You must restart Windows for your changes to take effect. #!/usr/bin/perl print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10); ---(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
Re: [BUGS] Left Join/Outer Join
Left/Outer joins are supported, but not until version 7.1. Most Linux distros are shipping still with 7.0.3. Upgrade. Your problem will be fixed. __ Your mouse has moved. You must restart Windows for your changes to take effect. #!/usr/bin/perl print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10); ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [BUGS] Bug #441: Index using INT8 data type is ignored
2 questions: 1) Have you recently run an analyze? 2) Are you sure that an index scan would be more efficient than a seq scan? (are less than 25% of the records selected) I don't know the break-off boint in the query optimizer, but it may be more efficient on that table to read the whole thing. __ Your mouse has moved. You must restart Windows for your changes to take effect. #!/usr/bin/perl print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] pg_dumpall forces plain text format
You could fake some of this (select only) by using the dblink stuff in contrib. You could link back to yourself and make it work. Maybe if you REALLY need it, you could modify dblink to allow updates as well as selects. If you really need it that bad, you have the source, write it. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] Bug in libpq large-object interface
Uh ... I forgot to send the code I referred to. Here it is again:
Hi PostgreSQL people in general and Tatsuo in particular,
I'm using V 7.0 on a Linux machine and I believe I have found a bug in the
large object interface provided by libpq. The code below will reproduce it, I
hope. Basically it creates a large object, writes six 'a' characters to it,
then closes it. Then, in another transaction, it opens the object, seeks to
position 1 from the start, writes a 'b', then seeks to position 3 from the
start and writes another 'b'. Then it closes the object and COMMITs the
transaction. Finally, in a further separate transaction, it calls lo_export
to write out the resulting object to a file testloseek.c.lobj I find this
file, instead of containing the string 'ababaa' as expected, contains
'^@b^@baa' where ^@ is ASCII NUL.
Compile with something like
gcc -o testloseek testloseek.c -lpq
The program sets the PQtrace to STDOUT and writes messages to STDERR, so run
it with STDOUT redirected to a log file.
This is a C version of a basic regression test of guile-pg, my Guile language
bindings for libpq. You may recall I reported a similar bug a year or so ago,
and I believed it was then fixed by Tatsuo, after a couple of iterations. I'm
sorry to be the bearer of bad news ...
Please reply to me directly since I'm not on the list.
Thanks
Ian
#include
#include "libpq-fe.h"
#include "libpq/libpq-fs.h"
void exec_cmd(PGconn *conn, char *str);
main (int argc, char *argv[])
{
PGconn *conn;
int lobj_fd;
char buf[256];
int ret, i;
Oid lobj_id;
conn = PQconnectdb("dbname=test");
if (PQstatus(conn) != CONNECTION_OK) {
fprintf(stderr, "Can't connect to backend.\n");
fprintf(stderr, "ERROR: %s\n", PQerrorMessage(conn));
exit(1);
}
exec_cmd(conn, "BEGIN TRANSACTION");
PQtrace (conn, stdout);
if ((lobj_id = lo_creat(conn, INV_READ | INV_WRITE)) < 0) {
fprintf(stderr, "Can't create lobj.\n");
fprintf(stderr, "ERROR: %s\n", PQerrorMessage(conn));
exit(1);
}
fprintf(stderr, "lo_creat() returned OID %ld.\n", lobj_id);
if ((lobj_fd = lo_open(conn, lobj_id, INV_READ | INV_WRITE)) < 0) {
fprintf(stderr, "Can't open lobj.\n");
fprintf(stderr, "ERROR: %s\n", PQerrorMessage(conn));
exit(1);
}
fprintf(stderr, "lo_open returned fd = %d.\n", lobj_fd);
if ((ret = lo_write(conn, lobj_fd, "aa", 6)) != 6) {
fprintf(stderr, "Can't write lobj.\n");
fprintf(stderr, "ERROR: %s\n", PQerrorMessage(conn));
exit(1);
}
ret = lo_close(conn, lobj_fd);
printf("lo_close returned %d.\n", ret);
exec_cmd(conn, "END TRANSACTION");
exec_cmd(conn, "BEGIN TRANSACTION");
if ((lobj_fd = lo_open(conn, lobj_id, INV_READ | INV_WRITE)) < 0) {
fprintf(stderr, "Can't open lobj.\n");
fprintf(stderr, "ERROR: %s\n", PQerrorMessage(conn));
exit(1);
}
fprintf(stderr, "lo_open returned fd = %d.\n", lobj_fd);
if (ret)
fprintf(stderr, "Error message: %s\n", PQerrorMessage(conn));
if ((ret = lo_lseek(conn, lobj_fd, 1, 0)) != 1) {
fprintf(stderr, "error (%d) lseeking in large object.\n", ret);
fprintf(stderr, "ERROR: %s\n", PQerrorMessage(conn));
exit(1);
}
if ((ret = lo_write(conn, lobj_fd, "b", 1)) != 1) {
fprintf(stderr, "Can't write lobj.\n");
fprintf(stderr, "ERROR: %s\n", PQerrorMessage(conn));
exit(1);
}
if ((ret = lo_lseek(conn, lobj_fd, 3, 0)) != 3) {
fprintf(stderr, "error (%d) lseeking in large object.\n", ret);
fprintf(stderr, "ERROR: %s\n", PQerrorMessage(conn));
exit(1);
}
if ((ret = lo_write(conn, lobj_fd, "b", 1)) != 1) {
fprintf(stderr, "Can't write lobj.\n");
fprintf(stderr, "ERROR: %s\n", PQerrorMessage(conn));
exit(1);
}
ret = lo_close(conn, lobj_fd);
printf("lo_close returned %d.\n", ret);
if (ret)
fprintf(stderr, "Error message: %s\n", PQerrorMessage(conn));
PQuntrace(conn);
exec_cmd(conn, "END TRANSACTION");
exec_cmd(conn, "BEGIN TRANSACTION");
ret = lo_export(conn, lobj_id, "testloseek.c.lobj");
printf("lo_export returned %d.\n", ret);
if (ret != 1)
fprintf(stderr, "Error message: %s\n", PQerrorMessage(conn));
exec_cmd(conn, "END TRANSACTION");
exit(0);
}
void exec_cmd(PGconn *conn, char *str)
{
PGresult *res;
if ((res = PQexec(conn, str)) == NULL) {
fprintf(stderr, "Error executing %s.\n", str);
fprintf(stderr, "Error message: %s\n", PQerrorMessage(conn));
exit(1);
}
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
fprintf(stderr, "Error executing %s.\n", str);
fprintf(stderr, "Error message: %s\n", PQerrorMessage(conn));
PQclear(res);
exit(1);
}
PQclear(res);
}
--
Ian Grant, Computer Lab., New Museums Site, Pembroke Street, Cambridge
Phone: +44 1223 334420 Personal e-mail: iang at pobox dot com
[BUGS] Bug in libpq large-object interface
Hi PostgreSQL people in general and Tatsuo in particular, I'm using V 7.0 on a Linux machine and I believe I have found a bug in the large object interface provided by libpq. The code below will reproduce it, I hope. Basically it creates a large object, writes six 'a' characters to it, then closes it. Then, in another transaction, it opens the object, seeks to position 1 from the start, writes a 'b', then seeks to position 3 from the start and writes another 'b'. Then it closes the object and COMMITs the transaction. Finally, in a further separate transaction, it calls lo_export to write out the resulting object to a file testloseek.c.lobj I find this file, instead of containing the string 'ababaa' as expected, contains '^@b^@baa' where ^@ is ASCII NUL. Compile with something like gcc -o testloseek testloseek.c -lpq The program sets the PQtrace to STDOUT and writes messages to STDERR, so run it with STDOUT redirected to a log file. This is a C version of a basic regression test of guile-pg, my Guile language bindings for libpq. You may recall I reported a similar bug a year or so ago, and I believed it was then fixed by Tatsuo, after a couple of iterations. I'm sorry to be the bearer of bad news ... Please reply to me directly since I'm not on the list. Thanks Ian -- Ian Grant, Computer Lab., New Museums Site, Pembroke Street, Cambridge Phone: +44 1223 334420 Personal e-mail: iang at pobox dot com
[BUGS]
et);
if (ret != 1)
fprintf(stderr, "Error message: %s\n", PQerrorMessage(conn));
exec_cmd(conn, "END TRANSACTION");
exit(0);
}
void exec_cmd(PGconn *conn, char *str)
{
PGresult *res;
if ((res = PQexec(conn, str)) == NULL) {
fprintf(stderr, "Error executing %s.\n", str);
fprintf(stderr, "Error message: %s\n", PQerrorMessage(conn));
exit(1);
}
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
fprintf(stderr, "Error executing %s.\n", str);
fprintf(stderr, "Error message: %s\n", PQerrorMessage(conn));
PQclear(res);
exit(1);
}
PQclear(res);
}
--
Ian Grant, Computer Lab., New Museums Site, Pembroke Street, Cambridge
Phone: +44 1223 334420 Personal e-mail: iang at pobox dot com
Re: [BUGS] large object seek/write bug
> Thank you for the carefully developed test case. The bug is actually > in the backend, not in libpq: ... snip ... > > I believe the attached patch fixes these problems, but I haven't been > able to wring it out very thoroughly because I don't have applications > that do random seeks and writes in large objects. If you could bang > on it a little more and report back, that'd be great. Hi Tom. Thanks for the response and the quick fix. I'll write some more test cases now. I just thought that whilst this part of the backend is still fresh in your mind you might consider implementing lo_truncate (the lo_ analog of the unix truncate system call.) At present there is no way to reduce the size of a large object except by copying to a new one (and then we still can't delete the old one, or can we do that now?) Cheers Ian -- Ian Grant, Computer Lab., New Museums Site, Pembroke Street, Cambridge Phone: +44 1223 334420 Personal e-mail: iang at pobox dot com
Re: [BUGS] createlang
I have noticed that some of the command line tools do not work nicely when the local auth is not set to trust, but trust is dangerous. Have you tried creating the language from psql rather than from the command line? [EMAIL PROTECTED] wrote: >postgres@abigail ~/data $ createdb test1 >Password: >CREATE DATABASE > >postgres@abigail ~/data $ createlang --dbname=test1 --pglib=/usr/lib/pgsql >'plpgsql' >Password: Password: Password: >Password: > >postgres@abigail ~/data $ createlang --list --dbname=test1 >Password: > Procedural languages > Name | Trusted? | Compiler >-+--+-- > plpgsql | t| PL/pgSQL >(1 row) > > >postgres@abigail ~/data $ dropdb test1 >Password: >DROP DATABASE > > > >- > >- > >The createlang does not accept my password correctly. Also, should it not >give a confirmation such as "CREATE" when it executes? One can see from the >above output that I had to enter it 4 times. Note that it does not behave >this way when I enter the incorrect password (see below). > >I'm on RedHat 7.1 and using the default Postgres RPM from my initial >install. > >template1=# SELECT version() >template1-# ; > version >- > PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96 >(1 row) > >postgres@abigail ~/data $ cat /proc/meminfo >total:used:free: shared: buffers: cached: >Mem: 393175040 219648000 173527040 1470464 32092160 80633856 >Swap: 2713927680 271392768 > > >- > >- > > >postgres@abigail ~/data $ createdb test1 >Password: >CREATE DATABASE > >postgres@abigail ~/data $ createlang --dbname=test1 --pglib=/usr/lib/pgsql >'plpgsql' >Password: psql: Password authentication failed for user 'postgres' >createlang: external error > >postgres@abigail ~/data $ createlang --list --dbname=test1 >Password: >Procedural languages > Name | Trusted? | Compiler >--+--+-- >(0 rows) > > >postgres@abigail ~/data $ dropdb test1 >Password: >DROP DATABASE > > > > >---(end of broadcast)--- >TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] initdb problems
All CYGWIN users, make sure that cygipc is on your machine and running before doing anything with Postgres. Michaele Holtman wrote: [EMAIL PROTECTED]"> I am trying to use postgres with the CYGWIN binaries for WIN/2000. I started initdb with '--debug' and it got as far as postgres -boot -x! -C -F -DDatabase -d template1 and then just sat there with 'postgres' consuming CPU, but not appearing to do anything. Has something similar been reported before.
[BUGS] initdb fails on ia64 SMP system
Hello PostgreSQL people, Thanks for making PostgreSQL, it's great. Here's a bug report. Thanks Ian Your name : Ian Grant Your email address : [EMAIL PROTECTED] System Configuration - Architecture (example: Intel Pentium) : ia64, 4 way SMP Operating System (example: Linux 2.0.26 ELF) : Linux 2.4.3 SMP kernel PostgreSQL version (example: PostgreSQL-7.2.1): PostgreSQL-7.2.1 Compiler used (example: gcc 2.95.2) : 2.96 2731 (Red Hat Linux 7.1 2.96-85) Please enter a FULL description of your problem: - Running "initdb -D /data/path" fails. With debug enabled (-d switch) I get a trace which ends: DEBUG: inserting column 10 value 'byteain' DEBUG: Typ == NULL, typeindex = 7 DEBUG: -> byteain DEBUG: inserted DEBUG: inserting column 11 value 'byteaout' DEBUG: Typ == NULL, typeindex = 7 ERROR: Relation "pg_class" does not exist initdb failed. Removing /local/scratch/ig206/postgresql_data. Running the same command again causes it to fail somewhere different, e.g.: DEBUG: inserting column 9 value '0' DEBUG: Typ == NULL, typeindex = 9 DEBUG: -> 0 DEBUG: inserted DEBUG: inserting column 10 value 'cidin' DEBUG: Typ == NULL, typeindex = 7 DEBUG: -> cidin DEBUG: inserted DEBUG: inserting column 11 value 'cidout' DEBUG: Typ == NULL, typeindex = 7 ERROR: Relation "pg_class" does not exist initdb failed. Removing /local/scratch/ig206/postgresql_data. It's not always pg_class which doesn't exist, e.g.: DEBUG: inserting column 10 value 'boolin' DEBUG: Typ == NULL, typeindex = 7 DEBUG: -> boolin DEBUG: inserted DEBUG: inserting column 11 value 'boolout' DEBUG: Typ == NULL, typeindex = 7 ERROR: Relation "pg_proc" does not exist Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: --- Build postgresql 7.2.1 on an SMP ia64 machine and run initdb? If you know how this problem might be fixed, list the solution below: -- Sorry, I have no idea at all. -- Ian Grant, Computer Lab., William Gates Building, JJ Thomson Ave., Cambridge Phone: +44 1223 334420 -- Ian Grant, Computer Lab., William Gates Building, JJ Thomson Ave., Cambridge Phone: +44 1223 334420 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] initdb fails on ia64 SMP system
> Ian Grant <[EMAIL PROTECTED]> writes: > > Architecture (example: Intel Pentium) : ia64, 4 way SMP > > Operating System (example: Linux 2.0.26 ELF) : Linux 2.4.3 SMP kernel > > Does anything else work for you on that box? I seem to recall that > kernels before about 2.4.9 had serious SMP bugs. No, not much that I've tried, but its owners don't seem to notice. > > Running the same command again causes it to fail somewhere different, e.g.: > > In that case I don't think it's our bug. initdb is quite deterministic, > at least when running on a deterministic platform ;-) I didn't know this. I will try 2.4.9 and get back to you. Many thanks, Ian -- Ian Grant, Computer Lab., William Gates Building, JJ Thomson Ave., Cambridge Phone: +44 1223 334420 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] initdb fails on ia64 SMP system
> Ian Grant <[EMAIL PROTECTED]> writes: > > Architecture (example: Intel Pentium) : ia64, 4 way SMP > > Operating System (example: Linux 2.0.26 ELF) : Linux 2.4.3 SMP kernel > > Does anything else work for you on that box? I seem to recall that > kernels before about 2.4.9 had serious SMP bugs. Thanks. That was it. Best wishes Ian -- Ian Grant, Computer Lab., William Gates Building, JJ Thomson Ave., Cambridge Phone: +44 1223 334420 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] 7.3.3 configure should check for curses before readline
The canned readline check assumes that curses is already installed. If it isn't then the readline check fails and configure reports that readline is not available. The real problem though is that curses is not available. -- Ian Grant, Computer Lab., William Gates Building, JJ Thomson Ave., Cambridge Phone: +44 1223 334420 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] select where id=random()*something returns two results
> Sorry for buggering you, I get the point :) I think you mean 'bugging.' Buggering is something quite different. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] Column names: where, group by, having inconsistent behaviour?
Apologies for raising this - I've found a message in the archives from about a month ago; but... I can't find the relevant sections in the SQL spec; nevertheless, the docs for the SELECT command lead me to believe that this should work, since: http://developer.postgresql.org/docs/postgres/sql-select.html [[ In the SQL-92 standard, an ORDER BY clause may only use result column names or numbers, while a GROUP BY clause may only use expressions based on input column names. PostgreSQL extends each of these clauses to allow the other choice as well (but it uses the standard's interpretation if there is ambiguity). PostgreSQL also allows both clauses to specify arbitrary expressions. Note that names appearing in an expression will always be taken as input-column names, not as result-column names. ]] Sure enough: select col1 as x, col2 as y from table1 order by x; ...works, as does: select col1 as x, count(col2) as y from table1 group by x having count(col2) = 1; "Having" operates after "group by"; and I'd argue that [[ Each column referenced in condition must unambiguously reference a grouping column, unless the reference appears within an aggregate function. ]] so since we can "group by x", one would imagine that select col1 as x, count(col2) as y from table1 group by x having x = 1; should work - it's hardly ambiguous. I appreciate the distinction between input and output columns; nevertheless, the loosening of the behaviour of "group by" doesn't seem to sit with the restriction on "having". I also appreciate the need to support "standard" sql - but in the absence of ambiguities, shouldn't this expression "do what I mean"? Cheers, jan -- jan grant, ILRT, University of Bristol. http://www.ilrt.bris.ac.uk/ Tel +44(0)117 9287864 or +44 (0)117 9287088 http://ioctl.org/jan/ You see what happens when you have fun with a stranger in the Alps? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] BUG #2000: psql does not prompt for password
I get the same thing with 8.0.4, on Windows XP Professional. ~Mike Bruce Momjian wrote: > Todd wrote: > > > > The following bug has been logged online: > > > > Bug reference: 2000 > > Logged by: Todd > > Email address: [EMAIL PROTECTED] > > PostgreSQL version: 8.1 Beta 4 > > Operating system: Windows Xp home > > Description:psql does not prompt for password > > Details: > > > > psql -U postgres does not prompt for password and responds with... > > > > psql: FATAL: password authentication failed for user "postgres". > > > > even when I pass the -W option it doesn't prompt for password and gives me > > the error above. I deleted and recreated my cluster and get the same > > result. I can connect to the database using PgAdmin as user postgres. > > I have no idea what is causing this, but I have never heard of this > problem before. > > -- > Bruce Momjian| http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #5955: One-click installer does not escape password
The following bug has been logged online: Bug reference: 5955 Logged by: Rob Grant Email address: r...@occipital.com PostgreSQL version: 9.0 Operating system: OS X Description:One-click installer does not escape password Details: I provided the one-click installer a password that included a double quote. This caused the following errors: Error running /Library/PostgreSQL/9.0/installer/server/initcluster.sh "postgres" "postgres" "" "/Library/PostgreSQL/9.0" "/Library/PostgreSQL/9.0/data" 5432 DEFAULT : /bin/sh: -c: line 0: unexpected EOF while looking for matching `"' Error running /Library/PostgreSQL/9.0/installer/server/loadmodules.sh "postgres" "postgres" "" "/Library/PostgreSQL/9.0" 5432 : /bin/sh: -c: line 0: unexpected EOF while looking for matching `"' I reinstalled and provided a password that did not include a double quote and was able to complete the installation successfully. -- 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 #5781: unaccent() function should be marked IMMUTABLE
The following bug has been logged online:
Bug reference: 5781
Logged by: Grant Hutchins and Peter Jaros
Email address: gr...@pivotallabs.com
PostgreSQL version: 9.0.1
Operating system: Mac OS X 10.6.4
Description:unaccent() function should be marked IMMUTABLE
Details:
The unaccent(text) function supplied by contrib/unaccent is marked VOLATILE.
This prevents it from being used in indexes. We believe that the function
meets the requirements to be marked IMMUTABLE.
=# CREATE TABLE foo ();
CREATE TABLE
=# CREATE INDEX bar ON foo ( unaccent('baz') );
ERROR: functions in index expression must be marked IMMUTABLE
=# ALTER FUNCTION unaccent(text) IMMUTABLE;
ALTER FUNCTION
=# CREATE INDEX bar ON foo ( unaccent('baz') );
CREATE INDEX
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
