[BUGS] BUG #4558: Error 255!
The following bug has been logged online: Bug reference: 4558 Logged by: Paul Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.5 Operating system: Vista Ultimate Description:Error 255! Details: Whenever I try to install postgres database I keep getting an error that says Failed to run initdb: 255! Please see log file and then gives the log file location. When I check the log file it is blank. What can I do to fix this? -- 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?
Sequence: - listen xxx; create table xx (a int4); create rule xx_xx2 as on delete to xx where old.a=1 do notify xxx; - Output: something like "backend closed connection" Output I expected: at least some error message, but not closing of connection. I know that my SQL sequence is wrong, but i just expected the error message. PostgreSQL version: 7.1.1 Platform information: Linux Slackware 7.1, kernel 2.4.1 -- Best regards, Paul mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] BUG #1608: integer negative limit in plpgsql function arguments
The following bug has been logged online: Bug reference: 1608 Logged by: Paul Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.2 Operating system: Gentoo and Fedora Core 3 Description:integer negative limit in plpgsql function arguments Details: The script below best sums up the problem (and the work around). The question is: should I use that for all integers being put into a function? 8< create table test ( test_id integer ); insert into test (test_id) values (-2147483648); create function print_test_id (integer) returns integer AS ' DECLARE tmp ALIAS FOR $1; val integer; BEGIN select into val test_id from test where test_id = tmp; return val; END; ' LANGUAGE plpgsql; -- this doesn't work (and I think it should!) SELECT print_test_id(-2147483648); -- this is the workaround SELECT print_test_id((-2147483648)::int); >8 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #5972: Update with subquery: erroneous results for foreign key field
The following bug has been logged online: Bug reference: 5972 Logged by: Paul Email address: paul.co...@punct.ro PostgreSQL version: 9.0.3 Operating system: CentOS 5.5 Description:Update with subquery: erroneous results for foreign key field Details: Hello. We found what we think is a bug while running an update with a subquery in the condition. The relevant database layout is as follows: CREATE TABLE users ( id integer serial PRIMARY KEY, username character varying(32), email character varying(200), password character varying(32), status smallint DEFAULT 1 NOT NULL, rdate timestamp without time zone DEFAULT now() NOT NULL, last_action timestamp without time zone DEFAULT now() NOT NULL, ); CREATE TABLE cart ( id integer serial PRIMARY KEY, userid integer, dt timestamp without time zone DEFAULT now(), status integer DEFAULT 0, optional_firstname character varying(100), optional_lastname character varying(100), optional_email character varying(254) ); ALTER TABLE ONLY cart ADD CONSTRAINT cart_userid_fkey FOREIGN KEY (userid) REFERENCES users(id); We issued the following query, directly through psql: update cart set status = 1 where userid = (select userid from users where email = 'exam...@example.com'); As you can see, the subquery is broken (users table doesn't have the column userid). We missed that when we ran it, and we were stunned to see that the query updated 1573 rows, when we expected it to updated only 1 (even though this one should have failed). We further investigated the problem, and were able to replicate it on other databases as well. It seems that the folowing query has the same result: update cart set status = 1 where userid = (select userid); So it seems that PostgreSQL uses the foreign key as some kind of "shortcut", even though the following query fails (more than one row returned by a subquery) update cart set status = 1 where userid=(select cart.userid from cart, users where cart.userid = users.id); We are still not sure if this is a bug or the desired behaviour, but it seems strange (because the subquery, issued separately, fails). Thank you very much, Paul -- 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 #8396: Window function results differ when selecting from table and view, with where clause
The following bug has been logged on the website: Bug reference: 8396 Logged by: Paul M. Email address: p...@weotta.com PostgreSQL version: 9.3rc1 Operating system: Ubuntu Linux Description: When I select from a view, the where clause in my select statement does not restrict the rows processed by window functions referenced inside the view definition. Thus, if window functions are involved, using a where clause when selecting from a view and using a where clause when selecting directly from the underlying table produces different results. Without wanting to speculate on the cause of the differing results, I will say that this seems to be a case of an issue noted a year ago on Stack Overflow: "Will Postgres push down a WHERE clause into a VIEW with a Window Function (Aggregate)?" http://stackoverflow.com/questions/7533877/ At that time, responder Evan Carroll noted, "I can't think of anyway an un-referenced Window function can change the result if the WHERE was pushed down." This seems to be just such a case. Thanks in advance for looking into this. I've written a test case, which I hope will be helpful... create table plant ( plant_id character varying( 6 ) , city character varying( 25 ) , constraint p_pk_pid primary key ( plant_id ) ); insert into plant ( plant_id, city ) values ( '14 ST', 'San Francisco' ) , ( 'FOLSOM', 'San Francisco' ) , ( 'CHAVEZ', 'San Francisco' ) , ( 'HEINZ', 'Berkeley' ) ; create view plant_extend as select plant_id as plant_id , row_number() over ( partition by city order by plant_id ) as plant_number , count(*) over ( partition by city ) as of_plants_in_city , city as city from plant; -- Despite the where clause, the window functions see all 3 San Francisco plants: select * from plant_extend where plant_id = 'FOLSOM' ; -- But when the query is expressed this way, the window functions see only the Folsom Street plant: select plant_id as plant_id , row_number() over ( partition by city order by plant_id ) as plant_number , count(*) over ( partition by city ) as of_plants_in_city , city as city from plant where plant_id = 'FOLSOM' ; -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [postgis-devel] RE: [BUGS] BUG #4567: Clustering on GIST INDEX clobbers records in table intermittently
Yes, that patch removes the bug. P. On Sun, Dec 7, 2008 at 11:51 PM, Obe, Regina <[EMAIL PROTECTED]> wrote: > "Regina Obe" <[EMAIL PROTECTED]> writes: >> Description:Clustering on GIST INDEX clobbers records in table >> intermittently > >> This doesn't always happen to me but does intermittently, and for others >> it >> happens all the time. > >> I wonder whether this is fixed by this recent patch: >> http://archives.postgresql.org/pgsql-committers/2008-12/msg00053.php > >> The pre-patch behavior would've depended on the value of a >> never-initialized struct field, so the erratic behavior is explained >> by varying contents of memory. I'm unable to make it happen in an >> assert-enabled build, but that's probably because the initial contents >> of a palloc'd chunk are never zeroes in such a build. > >> regards, tom lane > > Tom, > > Thanks for the quick response on this. > > Paul, > You think by chance you can test out the patch since you can make this bug > happen consistently on your build. > > Thanks, > Regina > > > > > > The substance of this message, including any attachments, may be > confidential, legally privileged and/or exempt from disclosure pursuant to > Massachusetts law. It is intended solely for the addressee. If you received > this in error, please contact the sender and delete the material from any > computer. > > > > Help make the earth a greener place. If at all possible resist printing this > email and join us in saving paper. > > ___ > postgis-devel mailing list > [EMAIL PROTECTED] > http://postgis.refractions.net/mailman/listinfo/postgis-devel > > -- 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 #4809: Missing Expected Operator
The following bug has been logged online: Bug reference: 4809 Logged by: Paul Matthews Email address: p...@netspace.net.au PostgreSQL version: 8.3.7 Operating system: Linux OpenSuse 11.0 Description:Missing Expected Operator Details: Not a bug as such, but an obvious operator is missing. WHERE g.box_boundary @> w.geocode and ERROR: operator does not exist: box @> point g.boundary @> w.geocode; This can be worked around with, the following, but it is really a kludge. WHERE g.box_boundary @> box( w.geocode, w.geocode ) and g.boundary @> w.geocode; -- 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 #4810: Complex Contains, Bad Performace.
The following bug has been logged online: Bug reference: 4810 Logged by: Paul Mathews Email address: p...@netspace.net.au PostgreSQL version: 8.3.7 Operating system: Linux SuSE 11.0 Description:Complex Contains, Bad Performace. Details: Consider a table : Postcodes postcode char[4] boundary polygon with an GIST index on boundary. The table contains about 500 postcodes. Each boundary object is very complicated however. Each one may contain up to 2000 (latitude, longitude) points. Despite the existence of the index, postgresql is determined to full table scan when given. SELECT postcode WHERE boundary @> point 'x,y'; This is slow. 4m19 for 500 points. Adding a bounding box to the table: Postcodes postcode char[4] boundary polygon boxbound box Allows 500 points to be processed in less than 2 seconds. SELECT postcode WHERE boxbound @> box( point 'x,y', point 'x,y' ) and boundary @> point 'x,y'; Issue: For complex polygon contains, users have to write their own bounding box routines. Issue: The existence of a GIST index on the boundary polygons is ignored, despite the horrendous complexity of the polygons. -- 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 #4810: Complex Contains, Bad Performace.
Tom Lane wrote: "Paul Mathews" writes: Despite the existence of the index, postgresql is determined to full table scan when given. SELECT postcode WHERE boundary @> point 'x,y'; polygon @> point isn't an indexable operator. The indexable operators for a gist index on polygon are <<(polygon,polygon) &<(polygon,polygon) &&(polygon,polygon) &>(polygon,polygon) >>(polygon,polygon) ~=(polygon,polygon) @>(polygon,polygon) <@(polygon,polygon) &<|(polygon,polygon) <<|(polygon,polygon) |>>(polygon,polygon) |&>(polygon,polygon) ~(polygon,polygon) @(polygon,polygon) So it looks like you need to convert the point to a one-point polygon. regards, tom lane WHERE g.boundary @> polygon(box(w.geocode,w.geocode)); Is there are more convenient, less ugly, way to convert a point to a polygon?
[BUGS] BUG #4967: polygon @> point incorrect for points close to shared borders
The following bug has been logged online: Bug reference: 4967 Logged by: Paul Matthews Email address: p...@netspace.net.au PostgreSQL version: 8.3.7 Operating system: Linux Open Suse 11.0 + 11.1 Description:polygon @> point incorrect for points close to shared borders Details: Have two polygons, both with many vertex, sharing a common edge. Several thousand points where then tested to see which of the polygons the points fell into using the "polygon @> point" operator. A number of points close to the common border claimed they fell into both of the polygons. A quick perl+DBI+GD application was developed to plot both the polygons, the polygon boundaries, as well as the points that thought they where in both. This showed points close, but still several pixels away from the shared border, thinking they where in both. Guidance on this matter would be appreciated. -- 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 #4967: polygon @> point incorrect for points close to shared borders
Tom Lane wrote:
"Paul Matthews" writes:
A number of points close to the common border claimed they fell into both of
the polygons.
How close is "close"? There's some pretty arbitrary fuzzy-comparisons
logic in the geometric datatypes ... see FPeq() and friends. That might
be doing it to you.
regards, tom lane
I'll try to figure out how "relatively" close tonight, this stuff is
sub-metre resolution GPS data. The attached picture shows the two
polygons, the shared border, a road in this case, and the houses that
think they are on both sides of the road. Houses and other features are
located with latitude+longitude.
Last night I plugged in the polygon contains point code from
http://www.ecse.rpi.edu/Homepages/wrf/Research/Short_Notes/pnpoly.html.
This resolved to houses correctly. If it helps in anyway please see the
attached. No use of fuzziness. Opaque yes, fuzzy no. :-) . Use in any way you
see fit.
#include "postgres.h"
#include "utils/geo_decls.h"
#include "fmgr.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(kontains);
Datum
kontains(PG_FUNCTION_ARGS)
{
POLYGON* polygon;
Point* point;
int isin;
polygon = PG_GETARG_POLYGON_P(0);
point = PG_GETARG_POINT_P(1);
isin = contains( polygon->npts, polygon->p, point );
PG_RETURN_BOOL(isin);
}
int contains( int nvert, Point* vertex, Point* test )
{
int i, j, c = 0;
for( i=0, j=nvert-1; i
if( ((vertex[i].y>test->y) != (vertex[j].y>test->y))
&&
(test->x < (vertex[j].x-vertex[i].x) *
(test->y-vertex[i].y) /
(vertex[j].y-vertex[i].y) + vertex[i].x) )
c = !c;
}
return c;
}
<>
--
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: Contrib\fulltextindex\fti.c?
Hello,
Is it me, or does the declaration of difference as an unsigned int
prevent the if statements just below from working correctly?
Should it not be a normal int?
>From postgresql-7.0.2/contrib/fulltextindex/fti.c
==
bool
is_stopword(char *text)
{
char **StopLow;/* for list of stop-words */
char **StopHigh;
char **StopMiddle;
unsigned int difference;
StopLow = &StopWords[0];/* initialize stuff for binary search */
StopHigh = endof(StopWords);
if (lengthof(StopWords) == 0)
return false;
while (StopLow <= StopHigh)
{
StopMiddle = StopLow + (StopHigh - StopLow) / 2;
difference = strcmp(*StopMiddle, text);
if (difference == 0)
return (true);
else if (difference < 0)
StopLow = StopMiddle + 1;
else
StopHigh = StopMiddle - 1;
}
return (false);
}
==
--
Paul McGarrymailto:[EMAIL PROTECTED]
Systems Integrator http://www.opentec.com.au
Opentec Pty Ltd http://www.iebusiness.com.au
6 Lyon Park RoadPhone: (02) 9878 1744
North Ryde NSW 2113 Fax: (02) 9878 1755
[BUGS] Re: [SQL] MAX() of 0 records.
Hi Tom, > Hmm. There are several things going on here, but one thing that needs > clarification is whether this UPDATE is written correctly. Since it My goofup (you said you wanted a compact script!). You are correct there should have been an extra where condition in the triggered function. UPDATE entry_stats SET entry_minprice=min(item_price) FROM item where item_entry_id=NEW.item_entry_id AND item_live='f'; Should really have been: UPDATE entry_stats SET entry_minprice=min(item_price) FROM item where item_entry_id=NEW.item_entry_id AND entry_stats.entry_id=item_entry_id AND item_live='f'; which still generates the same error message (as the 'problem' is caused by the where clause, not what is being updated). FWIW I've attached the real function that I've implemented to get around the error message. In all probability the way I'm handling it is the right way: 1. Check I'm going to get a valid response from my aggregate 2a. If so perform the update with the aggregate 2b. If not perform the update with zeros(default value) Originally I was just wondering if I could do it all in one go, Try to perform the update and automatically get the aggregate result if it were 'available' and default to zeros if not. If I forget about aggregate functions for a moment and just consider an update where nothing matches the where criterion then I'd still use the same logic above to reset the values to their default. The only differences between using the aggregate function and not is that one throws an error and the other just updates 0 rows. > The thing that jumps out at me is that if you actually try the SELECT > illustrated above, you do not get any row, null or otherwise; you get > ERROR: Attribute entry_stats.ctid must be GROUPed or used in an > aggregate function > which is a mighty valid complaint. If you are aggregating rows to get > the MIN() then you don't have a unique ctid to deliver, so which row > ought to be updated? This is the system's way of expressing the same > concern I started with: this query doesn't seem to be well-posed. > > You don't see this complaint when you try the UPDATE, because ctid > is added to the implicit select result in a back-door way that doesn't > get checked for GROUP-BY validity. I wonder whether that is the bug. > If so, we'd basically be saying that no query like this is valid > (since UPDATE doesn't have a GROUP BY option, there'd be no way to > pass the grouping check). Would that mean that any update that used an aggregate function would be invalid? That would be a bit scary seeing as I am doing this in part to get around using aggregate functions in a view. > Another way to look at it is that perhaps an UPDATE involving aggregate > functions ought to be implicitly treated as GROUP BY targetTable.ctid. What exactly is a ctid? Thanks for your response Tom, it has been enlightening. I feel I'm getting a better understanding of what's going inside pgsql by the day from yourself and other peoples posts on the various lists. -- Paul McGarrymailto:[EMAIL PROTECTED] Systems Integrator http://www.opentec.com.au Opentec Pty Ltd http://www.iebusiness.com.au 6 Lyon Park RoadPhone: (02) 9878 1744 North Ryde NSW 2113 Fax: (02) 9878 1755
[BUGS] Re: [SQL] MAX() of 0 records.
Here's the attachment I said I was going to attach to the last message. TFIF! -- Paul McGarrymailto:[EMAIL PROTECTED] Systems Integrator http://www.opentec.com.au Opentec Pty Ltd http://www.iebusiness.com.au 6 Lyon Park RoadPhone: (02) 9878 1744 North Ryde NSW 2113 Fax: (02) 9878 1755 function.sql
[BUGS] Re: [SQL] MAX() of 0 records.
Tom Lane wrote: > This seems like a backend bug to me, but being an overworked hacker > I'm too lazy to try to reconstruct the scenario from your sketch. > Could I trouble you to submit a formal bug report with a specific, > hopefully compact script that triggers the problem? I've attached it here, along with the output I see. I am running 7.0.2 and the problem occurs on both my x86 Linux and Sparc Solaris installations. In addition to the output attached the postmaster console adds: DEBUG: Last error occured while executing PL/pgSQL function setentrystats DEBUG: line 4 at SQL statement ==== Thanks, -- Paul McGarrymailto:[EMAIL PROTECTED] Systems Integrator http://www.opentec.com.au Opentec Pty Ltd http://www.iebusiness.com.au 6 Lyon Park RoadPhone: (02) 9878 1744 North Ryde NSW 2113 Fax: (02) 9878 1755 ctidnulltest.out ctidnulltest.sql
[BUGS] Segfault in pgsql, Sparc Solaris 2.7, Postgresql 7.1.1
Howdy, I'm getting a segfault from psql when a createdb is attempted. Postgresql is configured with: ./configure --prefix=/opt/pgsql --enable-syslog and seems to compile without trouble (gcc 2.95.2), but fails during the createdb stage of the regressions tests. Installing and attempting to do the relevant bit of createdb from the command line: == $ gdb /opt/pgsql/bin/psql GNU gdb 4.18 (gdb) set args -d template1 -c "CREATE DATABASE \"paulmtest\"" (gdb) run Starting program: /opt/pgsql/bin/psql -d template1 -c "CREATE DATABASE \"paulmtest\"" CREATE DATABASE Program received signal SIGSEGV, Segmentation fault. 0xff363b60 in __do_global_dtors_aux () from /opt/pgsql/lib/libpq.so.2 (gdb) bt #0 0xff363b60 in __do_global_dtors_aux () from /opt/pgsql/lib/libpq.so.2 #1 0xff3728d4 in _fini () from /opt/pgsql/lib/libpq.so.2 #2 0xff3ba060 in ?? () #3 0xff0a0130 in _exithandle () from /usr/lib/libc.so.1 #4 0xff116964 in exit () from /usr/lib/libc.so.1 == Any ideas? -- Paul McGarrymailto:[EMAIL PROTECTED] Systems Integrator http://www.opentec.com.au Opentec Pty Ltd http://www.iebusiness.com.au 6 Lyon Park RoadPhone: (02) 9870 4718 North Ryde NSW 2113 Fax: (02) 9878 1755 This document and any attachments are intended solely for the named addressee(s), are confidential, and may be subject to legal professional privilege. Please notify us (on +61-2 9878 1744) as soon as possible if you have received this document in error. Any confidentiality or privilege is not waived or lost because this email has been sent to you by mistake. This document and any attachments are subject to copyright. No part of them should be reproduced or distributed by any means whatsoever without the prior consent of the copyright owner. Opentec does not warrant that this email and any attachments are error or virus free. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [BUGS] Segfault in pgsql, Sparc Solaris 2.7, Postgresql 7.1.1
Hi Justin, > I'm not sure if it'll be of assistance with this, but there's > a Solaris > specific installation guide for PostgreSQL at : > http://techdocs.postgresql.org/installguides.php#solaris I've compiled postgres 6.5 and 7.0 so many times I just jumped in and went with it :) Upgrading to gcc 2.95.3 has fixed the problem I was having, it's compiled and the regression tests are now whirring away happily. Those build instrucions look good, as does the init script. Thanks again. -- Paul McGarrymailto:[EMAIL PROTECTED] Systems Integrator http://www.opentec.com.au Opentec Pty Ltd http://www.iebusiness.com.au 6 Lyon Park RoadPhone: (02) 9870 4718 North Ryde NSW 2113 Fax: (02) 9878 1755 This document and any attachments are intended solely for the named addressee(s), are confidential, and may be subject to legal professional privilege. Please notify us (on +61-2 9878 1744) as soon as possible if you have received this document in error. Any confidentiality or privilege is not waived or lost because this email has been sent to you by mistake. This document and any attachments are subject to copyright. No part of them should be reproduced or distributed by any means whatsoever without the prior consent of the copyright owner. Opentec does not warrant that this email and any attachments are error or virus free. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] casting TEXT to CIDR (or to INET) has to be possible
POSTGRESQL BUG REPORT TEMPLATE Your name : Paul Vixie Your email address : [EMAIL PROTECTED] System Configuration - Architecture (example: Intel Pentium) : Intel Dual Pentium Operating System (example: Linux 2.0.26 ELF) : Freebsd 4.3 PostgreSQL version (example: PostgreSQL-7.1.1): PostgreSQL-7.2devel Compiler used (example: gcc 2.95.2) : gcc version 2.95.3 Please enter a FULL description of your problem: I know there's code in the server to convert presentation-format INET or CIDR into internal format. To get the effect of C's "inet_netof()" I need to be able to do this conversion from TEXT rather than from interpreter literals. Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: -- lartomatic=# select (host(relay)||'/18')::cidr from spam limit 5; ERROR: Cannot cast type 'text' to 'cidr' lartomatic=# select (host(relay)||'/18')::inet from spam limit 5; ERROR: Cannot cast type 'text' to 'inet' If you know how this problem might be fixed, list the solution below: - Nope, this is beyond my abilities. ---(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] Trying Cygwin version of PostgreSQL
Great! But where is the documentation on doing this stuff? I'd like to try. --- Tom Lane <[EMAIL PROTECTED]> wrote: > Peter <[EMAIL PROTECTED]> writes: > > I resorted to trial and error. Initdb hangs when > "Creating template1 > > database" so there must be a step missing. > > I think you forgot to run the cygipc daemon. > > regards, tom lane > > ---(end of > broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2498: Problem with ADO.RECORDSET.UPDATE
The following bug has been logged online: Bug reference: 2498 Logged by: Paul Röttgerding Email address: [EMAIL PROTECTED] PostgreSQL version: DBMS 8.0.3 Operating system: Windows / Linux Description:Problem with ADO.RECORDSET.UPDATE Details: Hello, we have PG 8.0.3 on Linux and Windows ODBC 8.2.2. Wenn we try to update recordsets in Visual basic 6 with the rs.update we get the following error. -2147467259 (Nicht gengend Basistabelleninformationen zum Aktualisieren.) What will that be. The Table and the sql are all OK. When you need more information, please tell me. Thanks Paul ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #2611: 2 instalation probs: invalid directory and user account creation
The following bug has been logged online: Bug reference: 2611 Logged by: Paul Suckling Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4-1 Operating system: Windows XP Description:2 instalation probs: invalid directory and user account creation Details: I used the file postgresql-8.1.msi to install the software from an administrator account on an up to date Win XP, using the instructions at http://pginstaller.projects.postgresql.org/ as suggested by the readme. At the time of installing the software, no other accounts existed. Problem 1) During installation I chose to install the software to C:\PostgreSQL\8.1\ I have ActivePerl installed (5.8.7), and at the "Enable procedural languages" step I selected both "PL/pgsql" and "PL/perl". I got to the installing step, and everything was progressing fine. Just before the "Installation complete" window, I encountered a couple of error messages telling me that it couldn't find certain perl related files at under C:\Program Files\PostgreSQL\8.1\ or something similar. Problem 2) During the "Service Configuration" step of the installation, I entered a non-existant account name (Web) and left the account domain as is. I expected the installer to create an account for me. I believe an error must have occured when doing so. I can see the account directory under C:\Documents and Settings\Web\ However, when I log off windows, it doesn't appear on the log on screen. Furthermore, when I investigate "User Accounts" from the control panel, it doesn't exist there either. I cannot therefore log onto the account, nor delete the account. If I try to create an account with the same name, it tells me that one already exists. Please can you tell me what I should do to resolve this most annoying problem? I tried uninstalling postgreSQL, but, as I guessed it wouldn't, it didn't delete this ghost account. Please let me know if there is any other information I can provide which will help you. Thank you, Paul ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] duplicate key violates unique constraint pg_toast_635216540
Hi,For the last 6 months or so we've had an intermittent issue while doing a data import with a simple update statement. The fix that we've found for this issue is to REINDEX TABLE ; Has anyone seen this error before? Again, the error is: duplicate key violates unique constraint pg_toast_<>Thanks,Paul
Re: [BUGS] duplicate key violates unique constraint pg_toast_635216540
warehouse=# select count(distinct chunk_id) from pg_toast.pg_toast_635216540;count---74557(1 row)We're on version 8.0.7On 10/16/06, Tom Lane <[EMAIL PROTECTED]> wrote:"Paul Laughlin" < [EMAIL PROTECTED]> writes:> For the last 6 months or so we've had an intermittent issue while doing a> data import with a simple update statement. The fix that we've found for > this issue is to REINDEX TABLE ;What PG version is this?Are you sure that the REINDEX actually does anything, as opposed tomerely retrying the data import? I'm thinking you may be having problems with OID collisions after OID wraparound, which is something8.1 should defend against but no earlier version does.What do you get fromselect count(distinct chunk_id) from pg_toast.pg_toast_635216540 ; regards, tom lane
Re: [BUGS] duplicate key violates unique constraint pg_toast_635216540
We got it early last week and again this morning. Before these two it was about six months ago.On 10/16/06, Tom Lane < [EMAIL PROTECTED]> wrote:"Paul Laughlin" < [EMAIL PROTECTED]> writes:> warehouse=# select count(distinct chunk_id) from> pg_toast.pg_toast_635216540;> count> ---> 74557> (1 row)> We're on version 8.0.7 Well, 8.0 is definitely at risk for OID collisions in a toast table,but with so few entries I'd have thought the probability pretty low.How often do you see these errors?regards, tom lane
[BUGS] Getting the command line to work
Hi. Perhaps this isn't the proper place to send this, but it's the only address I have. I've installed postgres and I'm trying to use the command prompt to create a database (PGAdmin III having failed miserably at the task). The documentation I'm working with, created by people I trust know what they're doing, says that I should enter the following: "createdb qf_local -U postgres -W -h localhost" But when I do that, it tells me that there are too many command line arguments and it fails. What am I doing wrong? I've never worked with DOS before, so my error might even be blindingly obvious, but I have no idea what it is. Any help you could give me in getting postgres to do what it's supposed to do would be marvelous. Many thanks, Paul Jordan Digital Archivist Occidental College
[BUGS] copy works, \copy doesn't
Sorry for the duplicate post. I forgot to mention this is with Postgresql 7.2.2. It seems the psql \copy command doesn't work properly when dealing with certain non-ascii characters (which ones I don't know). At any rate I took some binary data and escaped it like so with perl. $text=~s/\\//g; $text=~s/\n/\\\n/g; $text=~s/\t/\\\t/g; In other words I escaped escape characters, newlines and tabs. I made a table like this CREATE TABLE "testtable" ( "somenumber" integer DEFAULT '0' NOT NULL, "sometext" text DEFAULT '' NOT NULL ); I made my input file by printing out a asciified number, a tab, the text above and a newline. This is the attached file. In the event attached files get stripped by the mailing list it is also at http://www.aracnet.com/~paulb/sample-data When I do copy testtable from '/home/paulb/sample-data'; it works fine. When I do \copy testtable from '/home/paulb/sample-data' I get ERROR: copy: line 2, pg_atoi: error in "(binary gobbledygook) sample-data Description: Binary data ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] I was wrong about Re: copy works, \copy doesn't (fwd)
I wrote: > It seems the psql \copy command doesn't work properly when dealing > with certain non-ascii characters (which ones I don't know). At any rate > I took some binary data and escaped it like so with perl. I was wrong. I wasn't escaping things properly; in particular, ascii 0 needed to be escaped to \\000 . Also I was using 'text' type instead of 'bytea' type. I thought 'copy' was working but it was actually cutting off my input at the first ascii zero, whereas \copy gave me the error message. Sorry to waste people's time. In the event anyone else is out there who wants to use binary data with 'copy from' and is using perl to do the escaping this seems to work. # double up the escape character twice! Once for the 'copy from' command and # once for postgres bytea type escaping. $text=~s/\\//g; $text=~s/\n/\\\n/g; # escape \n $text=~s/\t/\\\t/g; # escape \t $text=~s/'/\\'/g; # escape ' $text=~s/\x00/000/g;# escape ascii 0 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] copy works, \copy doesn't
It seems the psql \copy command doesn't work properly when dealing with certain non-ascii characters (which ones I don't know). At any rate I took some binary data and escaped it like so with perl. $text=~s/\\//g; $text=~s/\n/\\\n/g; $text=~s/\t/\\\t/g; In other words I escaped escape characters, newlines and tabs. I made a table like this CREATE TABLE "testtable" ( "somenumber" integer DEFAULT '0' NOT NULL, "sometext" text DEFAULT '' NOT NULL ); I made my input file by printing out a asciified number, a tab, the text above and a newline. This is the attached file. In the event attached files get stripped by the mailing list it is also at http://www.aracnet.com/~paulb/sample-data When I do copy testtable from '/home/paulb/sample-data'; it works fine. When I do \copy testtable from '/home/paulb/sample-data' I get ERROR: copy: line 2, pg_atoi: error in "(binary gobbledygook) sample-data Description: Binary data ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] Installation Errors
Dear sir, i am encounting the following problem while initializing data cluster(i am using Mac os x 10.2). Plez tell me solution for this -- bash-2.05a$ initdb -D /usr/local/PostgreSQL/data The files belonging to this database system will be owned by user "ampleuser". This user must also own the server process. creating directory /usr/local/PostgreSQL/data... ok creating directory /usr/local/PostgreSQL/data/base... ok creating directory /usr/local/PostgreSQL/data/global... ok creating directory /usr/local/PostgreSQL/data/pg_xlog... ok creating directory /usr/local/PostgreSQL/data/pg_clog... ok creating template1 database in /usr/local/PostgreSQL/data/base/1... dyld: /usr/local/PostgreSQL/bin/postgres Undefined symbols: /usr/local/PostgreSQL/bin/postgres undefined reference to _crypt expected to be defined in /usr/lib/libcrypto.0.9.dylib /usr/local/PostgreSQL/bin/initdb: line 473: 536 Broken pipe cat "$POSTGRES_BKI" 537 | sed -e "s/POSTGRES/$POSTGRES_SUPERUSERNAME/g" -e "s/ENCODING/$MULTIBYTEID/g" 538 Trace/BPT trap | "$PGPATH"/postgres -boot -x1 $PGSQL_OPT $BACKEND_TALK_ARG template1 initdb failed. Removing /usr/local/PostgreSQL/data. Missed your favourite TV serial last night? Try the new, Yahoo! TV. visit http://in.tv.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] Installtion error
Dear sir,
Sir last one week i am try to install PostgreSQL in
Mac os x 10.2 .I dowm loaded PostgreSQL 7.3 from net
and i installed it in 'ampleuser', my login (in
dir:Library/PostgreSQL).
After that i set the paths in follwing manner.And it
successfully starting the postmaster also.But when i
try to create the database its giving the error.
Sir what is the error and plez give me solution
for it.
--
Mahesh-Ss-Computer:/Library/PostgreSQL] root# chown
ampleuser /Library/PostgreSQL/data
Mahesh-Ss-Computer:/Library/PostgreSQL] root# su -l
ampleuser
Mahesh-Ss-Computer:~] ampleuser%
[Mahesh-Ss-Computer:~] ampleuser% setenv PATH
${PATH}:/Library/PostgreSQL/bin
[Mahesh-Ss-Computer:~] ampleuser% setenv PGDATA
/users/ampleuser/data
[Mahesh-Ss-Computer:~] ampleuser%
/Library/PostgresqL/bin/pg_ctl -D
/users/ampleuser/data -l logfile start
postmaster successfully started
[Mahesh-Ss-Computer:~] ampleuser% createdb test
psql: 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"?
createdb: database creation failed
Missed your favourite TV serial last night? Try the new, Yahoo! TV.
visit http://in.tv.yahoo.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] fe-connect build failure (Solaris 9, PGSQL 7.3.3, OpenSSL 0.9.7b)
I ran into the following problem building PostgreSQL 7.3.3 on Solaris
9 (sparc) using GCC 3.3 and OpenSSL 0.9.7b.
gcc -g -Wall -Wmissing-prototypes -Wmissing-declarations -fPIC -I.
-I../../../src/include -I/opt/reb/include -I/opt/sfw/include -DFRONTEND
-DSYSCONFDIR='"/opt/reb/etc/postgresql"' -c -o fe-connect.o fe-connect.c
In file included from fe-connect.c:46:
/usr/include/crypt.h:22: error: parse error before '(' token
/usr/include/crypt.h:22: error: parse error before "const"
gmake[4]: *** [fe-connect.o] Error 1
gmake[4]: Leaving directory
`/net/cog/project/reb/src/base/postgresql/src/interfaces/libpq'
This is the OpenSSH-versus-crypt.h compatibility issue that is discussed
in doc/FAQ_Solaris thusly:
2) Why do I get problems when building with OpenSSL support?
When you build PostgreSQL with OpenSSL support you might get
compilation errors in the following files:
src/backend/libpq/crypt.c
src/backend/libpq/password.c
src/interfaces/libpq/fe-auth.c
src/interfaces/libpq/fe-connect.c
This is because of a namespace conflict between the standard
/usr/include/crypt.h header and the header files provided by OpenSSL.
Upgrading your OpenSSL installation to version 0.9.6a fixes this
problem.
Apparently the problem has come back (in limited form) in OpenSSL 0.9.7b,
the current version of OpenSSL.
I worked around the problem as follows. However, I don't offhand see
why fe-connect.c has to include at all, so perhaps a simpler
fix is to remove the #include on all platforms.
===
RCS file: src/interfaces/libpq/fe-connect.c,v
retrieving revision 7.3.3.0
retrieving revision 7.3.3.1
diff -pu -r7.3.3.0 -r7.3.3.1
--- src/interfaces/libpq/fe-connect.c 2003/01/30 19:50:07 7.3.3.0
+++ src/interfaces/libpq/fe-connect.c 2003/06/12 21:06:06 7.3.3.1
@@ -42,7 +42,7 @@
#ifndef HAVE_STRDUP
#include "strdup.h"
#endif
-#ifdef HAVE_CRYPT_H
+#if defined(HAVE_CRYPT_H) && !defined(des_crypt)
#include
#endif
---(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] pg_dumpall does not save CREATE permission on databases
POSTGRESQL BUG REPORT TEMPLATE
Your name : Paul Tillotson
Your email address : ptchristendom at yahoo dot com
System Configuration
-
Architecture (example: Intel Pentium) : AMD athlon something
Operating System (example: Linux 2.0.26 ELF) : FreeBSD
PostgreSQL version (example: PostgreSQL-7.3.4): PostgreSQL-7.3.4
Compiler used (example: gcc 2.95.2) : gcc
template1=# select version();
version
-
PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
Please enter a FULL description of your problem:
pg_dumpall does not save all access control permissions on a database.
(This is true for at least the CREATE permission.)
This causes the restore script to fail when, for example, it tries to create a
schema which is owned by a different user than the database which it resides
in.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
--
DO THIS IN PSQL:
template1=# create database foobar;
template1=# create user mrfoobar;
template1=# grant create on database foobar to mrfoobar;
template1=# select datname, datacl from pg_database;
datname | datacl
---+--
foobar| {=T,pgsql=CT,mrfoobar=C}
template1 | {=,pgsql=CT}
template0 | {=,pgsql=CT}
(3 rows)
THEN DO THIS FROM YOUR SHELL. NOTICE THAT THERE IS NO LINE
OF THE FORM "GRANT CREATE ON "
james% pg_dumpall
--
-- PostgreSQL database cluster dump
--
\connect "template1"
--
-- Users
--
DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE
datname = 'template0');
CREATE USER mrfoobar WITH SYSID 100 NOCREATEDB NOCREATEUSER;
--
-- Groups
--
DELETE FROM pg_group;
--
-- Database creation
--
CREATE DATABASE foobar WITH OWNER = pgsql TEMPLATE = template0 ENCODING =
'SQL_ASCII';
\connect foobar
--
-- PostgreSQL database dump
--
\connect template1
--
-- PostgreSQL database dump
--
--
-- TOC entry 2 (OID 1)
-- Name: DATABASE template1; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON DATABASE template1 IS 'Default template database';
If you know how this problem might be fixed, list the solution below:
-
pg_dumpall should read the from the datacl column from the pg_database table
and
write lines like this into the dump script when appropriate:
GRANT ON DATABASE TO ;
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
---(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] Migration 7.3 to 7.4 Numeric values using PQ_ interface
Hi, I have been using the C libraries with version 7.33 to retrieve data with success. Now I have migrated to 7.4., although using the psql client all looks fine my integer values returned through the programmatic interface are wild, e.g. should be 101 and is 7236487. Char and varchar are fine. Has something changed radically in this release that could cause a binary incompatibility? Any suggestions? Thanks, Paul Download Yahoo! Messenger now for a chance to win Live At Knebworth DVDs http://www.yahoo.co.uk/robbiewilliams ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] plpgsql For SQLQuery Loop Flags Error
Hi All,
I am compiling a function that uses the For Query
loopEnd Loop.
I get error "missing ".." at end of SQL expression"
I have read following Note: The PL/pgSQL parser
presently distinguishes the two kinds of FOR loops
(integer or query result) by checking whether the
target variable mentioned just after FOR has been
declared as a record or row variable. If not, it's
presumed to be an integer FOR loop. This can cause
rather nonintuitive error messages when the true
problem is, say, that one has misspelled the variable
name after the FOR. Typically the complaint will be
something like missing ".." at end of SQL expression
at
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS
Re checked the RECORD variable in function
CREATE OR REPLACE FUNCTION slice(char(15),varchar,
integer) RETURNS integer AS '
DECLARE
curTrackList char(15) ALIAS for $1;
sliceFile varchar ALIAS for $2;
lmfpLimit integer ALIAS for $3
mTrackDet RECORD;
lpmfSum integer := 0;
Sliced CONSTANT integer := 2;
curId varchar;
counter integer :=1 ;
BEGIN
Build the curSliceId value
curId := curTrackList;
Insert into mysliceDetail
values(''abcdef'',''Ghijkl'',0);
FOR mTrackDet IN SELECT myrequest.trackId,
lmfpsize from myrequest,track where trkfound <> Sliced
and myRequest.trackid = track.trackId and lmfpsize > 0
order by volatility LOOP
-- Now "mTrackDet" has one record from slice
list
--LOOP
--WHILE lpmfSum < lpmfLimit LOOP
--Insert into mysliceDetail values(curId,
mTrackDet.trackId ,0);
--Insert into curSliceDetail values(curId,
mTrackDet.trackId ,0);
--lmfpSum := mTrackDet.lmfpsize + lmfpSum ;
--EXIT WHEN lpmfSum > lpmfLimit
--END LOOP;
insert into sliceToBuild values(curId, 0);
copy curSliceDetail to sliceFile;
counter := counter + 1;
Truncate Table curSliceDetail;
---Generate next curSliceId
curSliceId := curTrackList + counter;
END LOOP;
RETURN 1;
END;
' LANGUAGE plpgsql;
Can someone help please
Regards
Paul
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[BUGS] plpgsql Integer Concat To String
Hi, I want to concatenate a it counter to a string in a loop in plpgsql. DECLARE counter integer := 1; IdSet char : = 'UniqueId' IdForEachRun varchar; BEGIN IdForEachRun := IdSet || counter;(PROBLEM HERE) --Or IdForEachRun := IdSet + counter;(PROBLEM HERE) While condition LOOP Insert into Table values(IdForEachRun, ..) counter := counter + 1; IdForEachRun := IdSet || counter;(PROBLEM HERE) --Or IdForEachRun := IdSet + counter;(PROBLEM HERE) END LOOP END Language 'plpgsql' Thanks in advance. Paul __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] plpgsql
Hi, I want to concatenate a it counter to a string in a loop in plpgsql. DECLARE counter integer := 1; IdSet char : = 'UniqueId' IdForEachRun varchar; BEGIN IdForEachRun := IdSet || counter;(PROBLEM HERE) --Or IdForEachRun := IdSet + counter;(PROBLEM HERE) While condition LOOP Insert into Table values(IdForEachRun, ..) counter := counter + 1; IdForEachRun := IdSet || counter;(PROBLEM HERE) --Or IdForEachRun := IdSet + counter;(PROBLEM HERE) END LOOP END Language 'plpgsql' Thanks in advance. Paul Puneet ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Renaming a table leaves orphaned implicit sequences which
I got bitten by this one also. Perhaps it would be possible to change pg_dump so that it dumps the create table statement with the explicit sequence, rather than the original SQL used to create the table? (This would preserve old dumps and the syntactical sugar which I would not want to forego.) Paul Tillotson Hello. I use PostgreSQL 7.4.2 on i386-portbld-freebsd5.2, compiled by GCC cc (GCC) 3.3.3 [FreeBSD] 20031106 Description: It seems that renaming tables with columns of type "serial" leaves "orphaned" implicit sequences which breaks pg_restore. How to reproduce: 1. Create a table CREATE DATABASE something1; CREATE DATABASE something2; \c something1 CREATE TABLE test1 (id serial, name char(12)); ALTER TABLE test1 RENAME TO test2; 2. Run dump/restore and get an error: $ pg_dump -Fc something1 | pg_restore -d something2 pg_restore: NOTICE: CREATE TABLE will create implicit sequence "test2_id_seq" for "serial" column "test2.id" pg_restore: [archiver (db)] could not execute query: ERROR: relation "test1_id_seq" does not exist Workaround: Do not use the "serial" data type, always create sequences explicitly. pg_dump always generates a "CREATE SEQUENCE" clause for explicit sequences. ---(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] Windows command line utilities problems
I am using postgresql 8.0 beta 4 on win2000. psql operates strangely within a cmd.exe window. It is somewhat like if you backgrounded the command in unix. with psql in interactive mode (no -f switch) whatever line you type is alternately processed by cmd.exe and psql. So you get the following (more commentary below the second dotted line) === C:\Documents and Settings\satbuy.EUG-POS-02>psql -U postgres book C:\Documents and Settings\satbuy.EUG-POS-02>Welcome to psql 8.0.0beta4, the Post greSQL 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 Warning: Console codepage (437) differs from windows codepage (1252) 8-bit characters will not work correctly. See PostgreSQL documentation "Installation on Windows" for details. book=# dir Volume in drive C has no label. Volume Serial Number is 41AD-7C61 Directory of C:\Documents and Settings\satbuy.EUG-POS-02 12/02/2004 12:29p. 12/02/2004 12:29p.. 11/30/2004 12:06pDesktop 12/02/2004 12:29p 0 dir 11/22/2004 05:02pFavorites 11/22/2004 04:30pMy Documents 12/02/2004 12:29p 0 psql 12/02/2004 12:29p 0 select 07/10/2003 03:39aStart Menu 12/02/2004 12:29p 0 Welcome 4 File(s) 0 bytes 6 Dir(s) 33,284,308,992 bytes free C:\Documents and Settings\satbuy.EUG-POS-02>select * from mbs limit 1; matkey | matseq | mbsno | mmbsck | author |authori | title |titlei| edition | binding | publisher | publisheri | imprint | isbn| price | mbillf | mreflg | mrfbkx | mrelfl | mactx | mntxfl | mbs_comme nt | mtguid | status | mtxtrd | mtlmt | unique_id -++---++++-- --+--+-+-+---++-+--- +---+++++---++-- ---++++---+--- ||| | 1 | 1 | 0 | CLEAN WITH ALL | clean with all | PAGES IN TACT! | pages intact | | | || | 00 000 | 0 ||| 0 || 0 || | 0 || 0 | 999 | 1 (1 row) book=# select * from mbs limit 1; 'select' is not recognized as an internal or external command, operable program or batch file. C:\Documents and Settings\satbuy.EUG-POS-02>dir; ERROR: syntax error at or near "dir" at character 1 LINE 1: dir; ^ book=# C:\Documents and Settings\satbuy.EUG-POS-02> === If you run psql with the -f switch you get the windows command prompt back immediately. The command continues running echo the usual stuff to screen such as SELECT or CREATE INDEX. This is confusing because it is hard to tell when postgres as finished processing the file. SELECT, CREATE INDEX and the rest show on the screen. However, COPY is not displaying. The -f switch doesn't seem to handle relative paths correctly. If my current directory is the desktop, and I have a file mbs.dump on my desktop and I type psql -U postgres -f mbs.dump book it says the system can't find the specified file. Without relative paths but using < I get no error message and no results with psql -U postgres book < c:\mbs.dump Overall the server on windows seems to work pretty well but psql seems a bit messy right now. ---(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] error in install to progress 8 windows2000
Hi, I am trying to install the windows version of postgres. I have installed it at home fine but in work there is a problem. The install is getting to creating the intidb where by it can not access it or create the service. I have downloaded the following but when I re install it does not work. Can you advise. PostgreSQL 8.0.0 RC5-2 Regards Paul Conroy an Chéim Computer Services 52 Broomhill Road Tallaght Dublin 24 Email: [EMAIL PROTECTED] Ph: 01 -4310708 This email, its content and any files transmitted with it are intended solely for the addressee(s) and may be legally privileged and/or confidential. Access by any other party is unauthorised without the express written permission of the sender. If you have received this email in error you may not copy or use the contents, attachments or information in any way. Please destroy it and contact the sender via e-mail return. Internet communications are not secure unless protected using strong cryptography. This email has been prepared using information believed by the author to be reliable and accurate, but the MIS Programme Office makes no warranty as to accuracy or completeness. In particular the author does not accept responsibility for changes made to this email after it was sent. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] Help
I am working on a product that uses a database for some elementary permanent storage. Currently we are using Firebird. I have been asked to evaluate the product against Postgres, but I have limited time to do so. Is there an easy way to get started so I can quickly install Postgress and load my schema? Loaded message here - One of the reasons that I choose Firebird was that it was very, very, very easy to use. I am not having the same experience with Postgres. Any pointers as to how to get it set up enough to load imy schema would not only be appreciated, but would be a point in Postgres’s favor. Regards, Paul Bramble, Vidiom Inc.
[BUGS] BUG #1879: ODBC Connection Dies
The following bug has been logged online: Bug reference: 1879 Logged by: Paul O'Connor Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.2 Operating system: Redhat: kernel 2.4 Description:ODBC Connection Dies Details: The ODBC Connection dies, displaying various frontend messages (frontend is MSAccess 2000/2003). Have tried almost everything, but found nothing. here are some of the things I've tried: Compile frontend on local machine that has problems Check Version is being changed on update (frontend specific) Check MDAC Type and change in frontend Check Application Error Log in Windows Check timeout with individual connection (through odbc) Check ODBC Settings Check Driver version for ODBC-PostrgeSQL Check Windows Version Check Office Version Check updates to Jet Engine Update MDAC Check for corruption in frontend Check using MDB for more explicit error messages (no result) Check for postgres DB Corruption (vacuum db) Check ActiveX Versions and if present Check connection time (run overnight on working machine) Possible indication towards table - test it (no problems, and errors were not all here anyway) Check for fields with caps (possible act of PEBKAC) Check User data (for unexpected or wrong data) Logging ODBC trace Compare data structure with last known working Added indexes to tables that don't have them Vacuum tables again None of these have given my anything useful. The problem is happening on 20(ish) machines, and there is no defined pattern as to which machines are not gtting the errors (although this could just be a matter of usage). The errors appear after random intervals (5mins/10mins/30mins/etc.) and the application frontend has a connection sustaining module (that definitely works). if anyone could help, I'd be very much obliged. Thank you. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[BUGS] BUG #1987: UTF8 encoding differences hamper upgrades
The following bug has been logged online: Bug reference: 1987 Logged by: Paul Lindner Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1beta3 Operating system: Fedora Core 4 x86_64 Description:UTF8 encoding differences hamper upgrades Details: I've been doing some test imports of UNICODE databases into Postgres 8.1beta3. The only problem I've seen is that some data from 8.0 databases will not import. I've generated dumps using pg_dump from 8.0 and 8.1. Attempting to restore these results in Invalid UNICODE byte sequence detected near byte ... Question: Does the 8.1 Unicode sanity code accept the full set of characters accepted by the 8.0 Unicode sanity code? If not we'll see a lot of problems like the one above. I believe this patch is the one causing the problem I see: http://www.mail-archive.com/pgsql-patches@postgresql.org/msg08198/unicode.di ff Is there any solution other than scrubbing the entire dataset to conform to the new (8.1) encoding rules? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #1992: ODBC error with PostgreSQL Win32 Clients
The following bug has been logged online: Bug reference: 1992 Logged by: Paul Anderson Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.3 Operating system: Microsoft Windows XP Professional version 5.1.2600 Service Pack 1 Build 2600 Description:ODBC error with PostgreSQL Win32 Clients Details: Error may not be with Postress per say but with the ODBC diriver. Installed pgw32cli-1[1].0.0.2-full.exe. Select Version(); PostgreSQL 8.0.3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) I created an ODBC connection using the Postgres driver through the ODBC admin tool Am running Microsoft Word 2002 SP2. I am trying to use the mail merge functions to estalish a dynamic table link to Postgres. This is done by choosing insert database from the mail merge menu It brings up a dialog box to get data. choose new source choose ODBC DSN choose the pre-creared PostgreSQL DSN It returns an error "unable to obtain a list of tables from the data source". The same error can be reproduced through Excelif you choose Data|Import External Data|Import Data The ODBC connection DOES work if you choose Data|Import External Data|New Database Query My need is to dynamically embedd reports into a word documents so I can distribute pre-formated reports so the excell option is not a viable option for what I need. The work around is to export the data from Postgres as tab delimeted text files and then have word connect to the text files. This is workable but cumbersome. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #2436: cannot --enable-thread-safety on -lpthread host
The following bug has been logged online: Bug reference: 2436 Logged by: Paul Forgey Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.3 Operating system: Debian stable (sarge) Description:cannot --enable-thread-safety on -lpthread host Details: Linux supplies -lpthread. The configure script seems to only look for -lpthreads and then give up. I'm surprised to not find anything about this on the mailing list archives. configure:16421: checking for the pthreads library -lpthreads configure:16459: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -D_GNU_SOURCEconftest.c -lpth reads -lz -lreadline -lcrypt -lresolv -lnsl -ldl -lm >&5 conftest.c: In function `main': conftest.c:124: warning: `th' might be used uninitialized in this function /usr/bin/ld: cannot find -lpthreads collect2: ld returned 1 exit status configure:16465: $? = 1 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2436: cannot --enable-thread-safety on -lpthread host
As given in my OS info, this isn't FC4. The configure script fails to enable threading in Debian Sarge. On May 15, 2006, at 6:48 AM, Tom Lane wrote: "Paul Forgey" <[EMAIL PROTECTED]> writes: Linux supplies -lpthread. The configure script seems to only look for -lpthreads and then give up. Say again? I get this configure trace on Fedora 4: checking for the pthreads library -lpthreads... no checking whether pthreads work without any flags... no checking whether pthreads work with -Kthread... no checking whether pthreads work with -kthread... no checking for the pthreads library -llthread... no checking whether pthreads work with -pthread... yes checking whether pthreads work with -pthreads... no checking whether pthreads work with -mthreads... no checking for the pthreads library -lpthread... yes checking whether pthreads work with --thread-safe... no checking whether pthreads work with -mt... no checking for the pthreads library -lpthreadGC2... no checking pthread.h usability... yes checking pthread.h presence... yes checking for pthread.h... yes regards, tom lane smime.p7s Description: S/MIME cryptographic signature
Re: [BUGS] BUG #5804: Connection aborted after many queries.
On Wed, Dec 29, 2010 at 10:30 AM, Paul J. Davis
wrote:
>
> The following bug has been logged online:
>
> Bug reference: 5804
> Logged by: Paul J. Davis
> Email address: paul.joseph.da...@gmail.com
> PostgreSQL version: 9.0.2
> Operating system: OS X 10.6.5, Ubuntu 10.04
> Description: Connection aborted after many queries.
> Details:
>
> After running many queries (millions) a connection will report an error that
> the server has unexpectedly closed the connection. I first noticed this
> through psycopg2, but I've been able to reproduce it with a small C program
> using only libpq which I've included below. I compiled this against a libpq
> built by Homebrew (after upgrading the formula to use a 9.0.2 tarball) on OS
> X 10.6.5. The server was installed from 9.0.2 package available from
> https://launchpad.net/~pitti/+archive/postgresql
>
> My next step is to try building libpq with --enable-cassert to see if that
> triggers anything client side. Let me know if there's something else I
> should be doing to debug this.
>
> This test has been bailing between 2.6 and 2.7M queries:
>
>
> #include
> #include
> #include "libpq-fe.h"
>
> static void
> fail(PGconn* conn, PGresult* res)
> {
> if(res != NULL) PQclear(res);
> PQfinish(conn);
> exit(1);
> }
>
> static void
> check(PGconn* conn, PGresult* res, const char* fmt)
> {
> ExecStatusType status = PQresultStatus(res);
>
> if(status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK)
> {
> fprintf(stderr, fmt, PQerrorMessage(conn));
> fail(conn, res);
> }
> }
>
> void
> run_query(PGconn* conn, PGresult* res)
> {
> int nFields, i, j;
>
> res = PQexec(conn, "DECLARE myportal CURSOR FOR select 1");
> check(conn, res, "DECLARE CURSOR failed: %s");
> PQclear(res);
>
> res = PQexec(conn, "FETCH ALL in myportal");
> check(conn, res, "FETCH ALL failed: %s");
>
> nFields = PQnfields(res);
> for(i = 0; i < PQntuples(res); i++)
> {
> for(j = 0; j < nFields; j++)
> {
> PQgetvalue(res, i, j);
> }
> }
>
> PQclear(res);
>
> res = PQexec(conn, "CLOSE myportal");
> check(conn, res, "CLOSE failed: %s");
> PQclear(res);
> }
>
> int
> main(int argc, char **argv)
> {
> PGconn* conn;
> PGresult* res;
> int i;
>
> if(argc != 2)
> {
> fprintf(stderr, "usage: %s DSN\n", argv[0]);
> exit(1);
> }
>
> conn = PQconnectdb(argv[1]);
>
> if(PQstatus(conn) != CONNECTION_OK)
> {
> fprintf(stderr, "Connection failed: %s", PQerrorMessage(conn));
> fail(conn, NULL);
> }
>
> res = PQexec(conn, "BEGIN");
> check(conn, res, "BEGIN failed: %s");
> PQclear(res);
>
> for(i = 0; i < 1000; i++)
> {
> if((i+1) % 10 == 0)
> {
> fprintf(stderr, "I: %d\n", i);
> }
> run_query(conn, res);
> }
>
> res = PQexec(conn, "END");
> check(conn, res, "END failed: %s");
> PQclear(res);
>
> PQfinish(conn);
>
> return 0;
> }
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
I should've mentioned the various version combinations I tried this with.
Originally the client was 8.2 ish on OS X 10.5.something (it was an
old MacBook I don't have anymore) against the 8.2 server package in
Ubuntu 9.04. The python scripts where I noticed this issue would run
fine against that combination. After upgrading my MacBook to a Mac
Pro, I ended up installing Postgres 9.0.1 on the client (and building
psycopg2 against that) which is when I started getting errors. The
original error in the 9.0.1 client against the older server was that
libpq would get stuck on a poll() call down when trying to fetch
tuples or execute a command.
After a bit of narrowing down what was the cause I ended up trying to
upgrade the server to see if it was just a weird interplay between
9.0.1 and the older server. After upgrading to Ubuntu 10.04 and
installing Postgres 8.4 (from apt) the error turned into the current
manifestation in that libpq would give an error saying that the server
had unexpectedly closed the connection (instead of blocking on the
poll() call).
At some point I upgraded my client install to 9.0.2 and started a
server locally. Running the test program against a local database
failed to trigger the bug. I then tried to downgrade my local client
to 8.4 and tested that against the 8.4 install on Ubuntu which showed
the bug. And finally I upgraded both the server and the client to
9.0.2 and I can trigger the bug.
Thanks,
Paul Davis
--
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 #5804: Connection aborted after many queries.
On Wed, Dec 29, 2010 at 10:58 AM, Kevin Grittner wrote: > "Paul J. Davis" wrote: > >> After running many queries (millions) a connection will report an >> error that the server has unexpectedly closed the connection. > > What message are you getting? (Copy/paste is a good thing.) > > What do you see in the server log at the time of failure? > > Are you using a connection pool? > > -Kevin > Doh, that was on my list of things to add but managed to forget. The error message reported by the client: DECLARE CURSOR failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. And this intriguing error in the server logs from around that time: 2010-12-28 18:40:02 EST LOG: SSL renegotiation failure 2010-12-28 18:40:02 EST LOG: SSL failed to send renegotiation request 2010-12-28 18:40:02 EST LOG: SSL renegotiation failure 2010-12-28 18:40:02 EST LOG: SSL error: unsafe legacy renegotiation disabled 2010-12-28 18:40:02 EST LOG: could not send data to client: Connection reset by peer 2010-12-28 18:40:02 EST LOG: SSL error: unsafe legacy renegotiation disabled 2010-12-28 18:40:02 EST LOG: could not receive data from client: Connection reset by peer 2010-12-28 18:40:02 EST LOG: unexpected EOF on client connection Googling, I see something that suggests turning off SSL renegotiation which I'll try next. -- 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 #5804: Connection aborted after many queries.
On Wed, Dec 29, 2010 at 11:27 AM, Tom Lane wrote: > Paul Davis writes: >> And this intriguing error in the server logs from around that time: > >> 2010-12-28 18:40:02 EST LOG: SSL renegotiation failure >> 2010-12-28 18:40:02 EST LOG: SSL failed to send renegotiation request >> 2010-12-28 18:40:02 EST LOG: SSL renegotiation failure >> 2010-12-28 18:40:02 EST LOG: SSL error: unsafe legacy renegotiation disabled >> 2010-12-28 18:40:02 EST LOG: could not send data to client: >> Connection reset by peer >> 2010-12-28 18:40:02 EST LOG: SSL error: unsafe legacy renegotiation disabled >> 2010-12-28 18:40:02 EST LOG: could not receive data from client: >> Connection reset by peer >> 2010-12-28 18:40:02 EST LOG: unexpected EOF on client connection > >> Googling, I see something that suggests turning off SSL renegotiation >> which I'll try next. > > In all cases, you were testing a client against a server on a different > machine, right? This looks to me like you've got two different openssl > libraries, one of which has a bogus partial fix for the recent SSL > renegotiation security issue. I'm not sure what the state of play is > in Apple's shipping version of openssl --- you might have to get an > up-to-date source distribution and compile it yourself to have non-bogus > renegotiation behavior. Or you could just disable renegotiation on the > PG server. > > regards, tom lane > Yeah, all failures were between separate machines with various versions of OpenSSL that I never thought to keep track of. After more Googling I've found that OS X "fixed" the renegotiation issue by disabling it in a security fix [1]. For the time being I'll just disable it server side as traffic isn't ever routed across a public network. Thanks for the help. Paul Davis [1] http://support.apple.com/kb/HT4004 -- 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 #5972: Update with subquery: erroneous results for foreign key field
Thank you very much, i understand now :) Paul On Tue, Apr 12, 2011 at 4:42 PM, Kevin Grittner wrote: > "Paul" wrote: > > > update cart set status = 1 where userid = (select userid from > > users where email = 'exam...@example.com'); > > > > As you can see, the subquery is broken (users table doesn't have > > the column userid). > > By standard, if the identifier isn't defined within the most local > scope, each enclosing scope, from the inside out, will be checked. > I would expect the above to update each row where cart.userid was > not null. > > I always use and recommend aliases where practical. If you wrote it > this way, such a mistake would be clearly identified: > > update cart set status = 1 where userid = (select u.userid from > users u where u.email = 'exam...@example.com'); > > Not a bug. > > -Kevin >
[BUGS] BUG #5978: Running postgress in a shell script fails
The following bug has been logged online:
Bug reference: 5978
Logged by: Paul Deschamps
Email address: pdescha...@gmail.com
PostgreSQL version: 8.4.6
Operating system: Ubuntu 10.4
Description:Running postgress in a shell script fails
Details:
When running postgres in a shell using the -c option it looks as though it
parses the contents of the --command as command line arguments.
---BEGIN SCRIPT---
#!/bin/bash
psql --version
PCOMMAND='psql postgres -c"SELECT tablename FROM PG_TABLES limit 1;"'
echo "PGSQL - Execution from a shell script test"
echo
echo "Running Command:"${PCOMMAND}
echo "TEST 1 "
OUTPUT1=$(${PCOMMAND})
echo $OUTPUT1
echo
echo "TEST 2"
OUTPUT2=`$PCOMMAND`
echo $OUTPUT2
echo
echo "TEST 3"
OUTPUT3=`exec $PCOMMAND`
echo $OUTPUT3
echo
echo "TEST 4"
$PCOMMAND
echo "TEST 5"
psql postgres -c"SELECT tablename FROM PG_TABLES limit 1;"
---END SCRIPT---
---BEGIN OUTPUT---
[postgres@host03 scripts]$ ./test.sh
psql (PostgreSQL) 8.4.6
PGSQL - Execution from a shell script test
Running Command:psql postgres -c"SELECT tablename FROM PG_TABLES limit 1;"
TEST 1
psql: warning: extra command-line argument "FROM" ignored
psql: warning: extra command-line argument "PG_TABLES" ignored
psql: warning: extra command-line argument "limit" ignored
psql: warning: extra command-line argument "1;"" ignored
psql: FATAL: role "tablename" does not exist
TEST 2
psql: warning: extra command-line argument "FROM" ignored
psql: warning: extra command-line argument "PG_TABLES" ignored
psql: warning: extra command-line argument "limit" ignored
psql: warning: extra command-line argument "1;"" ignored
psql: FATAL: role "tablename" does not exist
TEST 3
psql: warning: extra command-line argument "FROM" ignored
psql: warning: extra command-line argument "PG_TABLES" ignored
psql: warning: extra command-line argument "limit" ignored
psql: warning: extra command-line argument "1;"" ignored
psql: FATAL: role "tablename" does not exist
TEST 4
psql: warning: extra command-line argument "FROM" ignored
psql: warning: extra command-line argument "PG_TABLES" ignored
psql: warning: extra command-line argument "limit" ignored
psql: warning: extra command-line argument "1;"" ignored
psql: FATAL: role "tablename" does not exist
TEST 5
tablename
---
pg_type
(1 row)
---END OUTPUT---
--
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 #6269: Anomaly detection
The following bug has been logged online: Bug reference: 6269 Logged by: Paul Stapersma Email address: paul.staper...@gmail.com PostgreSQL version: 8.3.3 Operating system: Mac OS X Version 10.6.8 Description:Anomaly detection Details: Dear reader, For a project at my University, we compared PostgreSQL with MySQL's InnoDB. In this research, we found several cases in which anomalies where detected in Isolation levels that guaranteed not to have these anomalies. In short summary: - we detected non-repeatable reads in the repeatable read isolation level - we detected non-repeatable reads in the serializable isolation level - we detected phantoms in the serializable isolation level - we detected lost updates in the repeatable read isolation level - we detected lost updates in the serializable isolation level Furthermore, we detected differences between Read Committed and Read Uncommitted and differences between Serializable and Repeatable Read which is in contrast to the documentation. A full report on our findings can be found here: http://dl.dropbox.com/u/19316575/report.pdf Friendly regards, Paul Stapersma -- 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 #6379: SQL Function Causes Back-end Crash
One extra detail, my PostgreSQL is compiled with --enable-cassert. This seems to be what sets off the killer function. On Wed, Jan 4, 2012 at 11:25 AM, hubert depesz lubaczewski wrote: > On Wed, Jan 04, 2012 at 07:17:17PM +, pram...@cleverelephant.ca wrote: >> The following bug has been logged on the website: >> >> Bug reference: 6379 >> Logged by: Paul Ramsey >> Email address: pram...@cleverelephant.ca >> PostgreSQL version: 9.1.2 >> Operating system: OSX 10.6.8 >> Description: >> >> CREATE OR REPLACE FUNCTION kill_backend() >> RETURNS VOID >> AS $$ >> DROP TABLE if EXISTS foo; >> CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1; >> $$ LANGUAGE 'SQL'; > > Cannot replicate: > > (depesz@localhost:5910) 20:23:43 [depesz] > $ CREATE OR REPLACE FUNCTION kill_backend() >>> RETURNS VOID >>> AS $$ >>> DROP TABLE if EXISTS foo; >>> CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1; >>> $$ LANGUAGE 'SQL'; > CREATE FUNCTION > (depesz@localhost:5910) 20:23:49 [depesz] > $ select kill_backend(); > NOTICE: table "foo" does not exist, skipping > CONTEXT: SQL function "kill_backend" statement 1 > kill_backend > -- > [null] > (1 row) > > (depesz@localhost:5910) 20:23:55 [depesz] > $ select kill_backend(); > kill_backend > -- > [null] > (1 row) > > (depesz@localhost:5910) 20:23:56 [depesz] > $ select kill_backend(); > kill_backend > -- > [null] > (1 row) > > (depesz@localhost:5910) 20:23:58 [depesz] > $ select version(); > version > --- > PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.6.real > (Debian 4.6.2-5) 4.6.2, 64-bit > (1 row) > > Side note - definition as is, doesn't work on 9.2: > $ CREATE OR REPLACE FUNCTION kill_backend() >>> RETURNS VOID >>> AS $$ >>> DROP TABLE if EXISTS foo; >>> CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1; >>> $$ LANGUAGE 'SQL'; > ERROR: language "SQL" does not exist > > changing it to proper sql (not uppercase) fixed this problem. > > Best regards, > > depesz > > -- > The best thing about modern society is how easy it is to avoid contact with > it. > http://depesz.com/ -- 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 #6379: SQL Function Causes Back-end Crash
Further notes, from Andrew (RhodiumToad) on IRC about the cause of this crasher: [12:03pm] RhodiumToad: what happens is this [12:04pm] RhodiumToad: postquel_start know this statement doesn't return the result, so it supplies None_Receiver as the dest-receiver for the query [12:04pm] RhodiumToad: however, it knows it's a plannedStmt, so it fires up the full executor to run it [12:05pm] RhodiumToad: and the executor allocates a new destreceiver in its own memory context, replaces es->qd->dest with it, [12:05pm] RhodiumToad: (the new destreceiver is the one that writes tuples to the created table) [12:06pm] RhodiumToad: then at executorEnd (called from postquel_end), executor shutdown closes the new rel, _and then frees the executor's memory context, including the destreceiver it created [12:07pm] RhodiumToad: postquel_end doesn't know that its setting of ->dest was clobbered, so it goes to try and destroy it again, and gets garbage (if assertions are on) [12:07pm] RhodiumToad: if assertions weren't on, then the rDestroy call is harmless [12:07pm] RhodiumToad: well, mostly harmless [12:07pm] RhodiumToad: sneaky one, that [12:09pm] RhodiumToad: you can confirm it by tracing through that second call to postquel_end and confirming that it's the call to ExecutorEnd that stomps the content of qd->dest [12:12pm] pramsey: confirmed, the pass through ExecutorEnd has clobbered the value so there's garbage when it arrives at line 638 [12:14pm] RhodiumToad: if you trace through ExecutorEnd itself, it should be the FreeExecutorState that does it [12:15pm] RhodiumToad: wonder how far back this bug goes [12:16pm] RhodiumToad: actually not very far [12:17pm] RhodiumToad: older versions just figured that qd->dest was always None_Receiver and therefore did not need an rDestroy call [12:17pm] RhodiumToad: (which is a no-op for None_Receiver) [12:17pm] pramsey: kills my 8.4 [12:17pm] RhodiumToad: so this is broken in 8.4+ [12:17pm] pramsey: ah [12:18pm] RhodiumToad: 8.4 introduced the lazy-eval of selects in sql functions [12:19pm] RhodiumToad: prior to that they were always run immediately to completion [12:19pm] RhodiumToad: that requires juggling the destreceiver a bit, hence the bug [12:20pm] RhodiumToad: btw, the first statement of the function shouldn't be needed [12:21pm] RhodiumToad: just ... as $f$ create table foo as select 1 as x; $f$; should be enough to break it [12:31pm] RhodiumToad: there's no trivial fix On Wed, Jan 4, 2012 at 11:32 AM, Paul Ramsey wrote: > One extra detail, my PostgreSQL is compiled with --enable-cassert. > This is required to set off the killer function. > >> On Wed, Jan 04, 2012 at 07:17:17PM +, pram...@cleverelephant.ca wrote: >>> The following bug has been logged on the website: >>> >>> Bug reference: 6379 >>> Logged by: Paul Ramsey >>> Email address: pram...@cleverelephant.ca >>> PostgreSQL version: 9.1.2 >>> Operating system: OSX 10.6.8 >>> Description: >>> >>> CREATE OR REPLACE FUNCTION kill_backend() >>> RETURNS VOID >>> AS $$ >>> DROP TABLE if EXISTS foo; >>> CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1; >>> $$ LANGUAGE 'sql'; >>> >>> SELECT kill_backend(); >> -- 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 #7632: Postgres binds to all IPs even when listen_addresses="localhost"
> This behaves as expected for me. I'm wondering whether your DNS > server is resolving "localhost" as meaning both 127.0.0.1 and your > public IP. "dig localhost." might be illuminating. Wow, that is some psychic debugging. Sure enough, my /etc/hosts is setting localhost to both 127.0.0.1 and my other IP. Using `listen_addresses = '127.0.0.1'` fixes the problem. Thank you! Paul -- _ Pulchritudo splendor veritatis. -- 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 #7795: Cannot choose UTF-8 encoding for initdb
The following bug has been logged on the website: Bug reference: 7795 Logged by: Paul Email address: paul.wat...@zephyr-consulting.com PostgreSQL version: 9.2.2 Operating system: Microsoft Windows [Version 6.0.6002] Vista 64-bit Description: C:\Program Files\PostgreSQL\9.2\data2>initdb.exe --locale="English_United States" --encoding=UTF8 --pgdata="C:\Program Files\PostgreSQL\9.2\data2" The files belonging to this database system will be owned by user "pwatson". This user must also own the server process. The database cluster will be initialized with locale "English_United States.1252". The default text search configuration will be set to "english". fixing permissions on existing directory C:/Program Files/PostgreSQL/9.2/data2 ... initdb: could not change permissions of directory "C:/Program Files /PostgreSQL/9.2/data2": Permission denied -- 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 #8247: Duplicate database names - pg_dump backup fails
The following bug has been logged on the website:
Bug reference: 8247
Logged by: Paul Macdonald
Email address: paul.macdon...@ssc-spc.gc.ca
PostgreSQL version: Unsupported/Unknown
Operating system: Debian Sarge
Description:
Postgres version: 7.4.7-6sarge1
Issue: pg_dump fails due to unexpected duplication of database name
("avipads"). Dropdb will remove one instance of the database name, but
unable to remove the second instance.
Scope: This specific database is used on multiple servers nationally. Three
of our servers have this issue, the majority do not. The issue only affects
our ability to locally backup the "avipads" database. The servers having the
problem are clustered pair systems. The other cluster pair servers do not
have the problem.
Name| Owner | Encoding
---+-+---
avipads | | SQL_ASCII
avipads | avimgr | SQL_ASCII
rtadb | rtafbackend | SQL_ASCII
rtafdb| rtafbackend | SQL_ASCII
template0 | postgres| SQL_ASCII
template1 | postgres| SQL_ASCII
When a new version of the database is available for installation, the
installation script does the following steps
a) remove the old database
su - avimgr -c "dropdb --quiet --username=avimgr avipads" || true
b) remove the avimgr user
su - postgres -c "dropuser --quiet avimgr" || true
c) create the avimgr Posgresql user
su - postgres -c "createuser avimgr --no-adduser --createdb --quiet; true"
2> /dev/null
d) install the new database
su - avimgr -c "/apps/avipads/voicedb/restorevoice
/apps/avipads/voicedb/vdb2.5.1.pg"
I would like details on how to successfully remove both "avipads" databases,
in order to have the pg_dump be functional again.
--
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] no meaningful way to determine fe or be version?
Bruce Momjian <[EMAIL PROTECTED]> writes: > > it would be nice if the metadata routines were actually tied to something retu rned from the backend. > > You can do a 'SELECT version()' to get a version string from the > backend. thanks for the workaround. the original point still stands, of course ;-) -- Paul M. Aoki / Xerox Palo Alto Research Center / Coyote Hill Road [EMAIL PROTECTED] / Computer Science Laboratory / Palo Alto, CA 94304-1314
Re: [BUGS] no meaningful way to determine fe or be version?
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Yes, should we return the client or server version from the interface?
> If we return the server version, how does someone query the client version?
sorry, yes. the client-side interfaces (DatabaseMetaData.getDriver*)
are actually fine - they return a major/minor version that have been
getting manually updated (they're much more visible, being in
org/postgresql/Driver.java). the server-side interfaces
(DatabaseMetaData.getDatabase{Name,Version}) are the ones that aren't
tied to the server, return hardcoded strings and haven't been getting
updated regularly.
--
Paul M. Aoki / Xerox Palo Alto Research Center / Coyote Hill Road
[EMAIL PROTECTED] / Computer Science Laboratory / Palo Alto, CA 94304-1314
Re: [BUGS] no way in LargeObject API to detect short read?
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Anyone able to fix this?
here's a hack we've been using in-house (written by Jun Gabayan,
<[EMAIL PROTECTED]>).
you may not like the style but it's a stab at a solution.
--
Paul M. Aoki / Xerox Palo Alto Research Center / Coyote Hill Road
[EMAIL PROTECTED] / Computer Science Laboratory / Palo Alto, CA 94304-1314
Index: LargeObject.java
===
RCS file:
/project/placeless/cvsroot/placeless2/src/org/postgresql/largeobject/LargeObject.java,v
retrieving revision 1.1
retrieving revision 1.3
diff -r1.1 -r1.3
64c64,67
<
---
>
> private int pos = 0; //current position
> private int size = 0;
>
85a89,90
> pos = tell();
> size = size();
102a108
> if(fd == 0) return;
105a112
> fd = 0;
118a126,132
> // calculate available data to read to avoid reading pass the end
> // to avoid an exception
> pos = tell();
> int avail = size - pos;
> if(avail == 0) return null;
> if(avail < len) len = avail;
> try {
123c137,141
<
---
> }catch(SQLException se) {
> System.out.println("***LargeObject.read: Caught SQLException: " +
>se.getMessage());
> return null;
> }
>
157c175
< public void read(byte buf[],int off,int len) throws SQLException
---
> public int read(byte buf[],int off,int len) throws SQLException
159c177,180
< System.arraycopy(read(len),0,buf,off,len);
---
> byte mybuf[] = read(len);
> int sz = (mybuf != null) ? mybuf.length : -1; //must return -1 for end of data
> if(sz > 0) System.arraycopy(mybuf,0,buf,off,sz);
> return sz;
Re: [JDBC] Re: [BUGS] no way in LargeObject API to detect short read?
Peter T Mount <[EMAIL PROTECTED]> writes: > Hmmm, what's the performance issues with this? Is there going to be a problem > with very large LargeObject's? you could probably be smarter about caching previous tell() state, if that's what you mean. jun's hack doesn't actually add any extra buffers or copies. -- Paul M. Aoki / Xerox Palo Alto Research Center / Coyote Hill Road [EMAIL PROTECTED] / Computer Science Laboratory / Palo Alto, CA 94304-1314
[BUGS] SQL update statements are dying in the query planner
Title: SQL update statements are dying in the query planner POSTGRESQL BUG REPORT TEMPLATE Your name : Your email address : System Configuration - Architecture (example: Intel Pentium) : Intel Pentium Pro Operating System (example: Linux 2.0.26 ELF) :Mandrake Linux 7.2 Kernel 2.2.17-21mdksmp PostgreSQL version (example: PostgreSQL-7.1.1): PostgreSQL-7.1.1 Compiler used (example: gcc 2.95.2) :gcc version 2.95.3 19991030 (prerelease) Please enter a FULL description of your problem: SQL update statements are dying in the query planner. A copy of the -d3 postgres log is attached. The error returned by psql when submitting this query is ERROR: Relation 2699531655 does not exist however a very similar statement was successfully executed shortly before the failing statement. I am using a custom GUID/UUID data type extension similar to the MS SQL Server uniqueidentifier data type. I used the varbits type in the contrib directory as a template, as well as the FreeDCE library to generate new UUIDs. I would be happy to contribute this code if anybody is interested. With the new ODBC driver working towards ODBC 3.0 level support, there may be more interest in having GUID support in PostgreSQL. Although I can't rule out that the UUID data type may be at fault, it does work correctly under 7.0.2 . Unfortunately I cannot provide a copy of the data beyond what is in the actual SQL statement, though I could probably provide a copy of the schema for the relevant SQL tables if it would help. -- Perhaps this may be related to another bug that Tom Lane describes as: The direct cause of the problem is that EvalPlanQual isn't completely initializing the estate that it sets up for re-evaluating the plan. In particular it's not filling in es_result_relations and es_num_result_relations, which need to be set up if the top plan node is an Append. since psql refers to a vary large relation oid in its error message? It looks like the query is crashing after the query parsing is completed. -d4 doesn't seem to provide additional useful information regarding the Query Processing and where it is breaking. If there are any additional flags at compile or run time that I can set to provide more useful information, please let me know. - <> Paul-Andre Panon Sierra Systems 1177 West Hastings Street, Suite 2500 Vancouver, BC V6E 2K3 Main: 604.688.1371 Fax: 604.688.6482 www.SierraSystems.com postgresql.log ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[BUGS] OpenSSH, Windows Clients, and Incoming Tunnels
Hi Folks I've got the built-in OpenSSH (2.2.0) running on FreeBSD 4.2, and I connect to it from Windows with SSH Security Corp.'s SSH Secure Shell 2.4. As a terminal, it works fine. I want to use this product to encrypt data between a PostgreSQL backend and Windows clients. The Windows clients use ODBC to talk to PostgreSQL. When I define an OUTGOING tunnel, it works fine. For example: :freebsd.box:5432. PostgreSQL listens and sends at port 5432 and the Windows ODBC driver looks for PostgreSQL at 127.0.0.1, port . When I define an INCOMING tunnel (same local_port:server:remote_port), it appears to work fine, but the 'BSD box console displays an error message 3 times: sshd[436]: error: Hm, dispatch protocol error: type 80 plen 33 The log, though listing this same error (3 times), does not indicate any other problems. This error only appears if I define an incoming tunnel in the Windows SSH client. My application works (quite well), but I'm worried that the incoming data stream, which will contain sensitive data, is not encrypted. By the way, the Van Dyke product (SecureCRT) does the exact same thing. Can anyone advise? Thankyou in advance... -- Paul Lender ---- Paul A. Lender Univ. of Minnesota Dept. Orthopaedic Surgery [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] postmaster locks up in 7.1b3
> As far as Postgres is concerned, there's no deadlock here, only a slow client that could be true if we used explicit locks. all our accesses are of the form "learn everything you need to know to do the transaction, then open the database, do it, and close". there are some really long SELECT's (which make dns zone files) but they can't block unless the file system is blocking the write()'s in the client, which would only happen in NFS, which we don't use. your scenario is not implausible, however, and i will watch for it if it happens again after i upgrade. i didn't mean to waste any of you guys' time at this point, i just wanted to let you know about this in case it was another data point in a problem you were tracking elsewhere, or in case i'm able to track it more closely later. thanks for your quick reply. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] function tree_level(varchar) (from OpenACS) no longer work under 7.2
Hi all, Testing a port from Oracle to PG 7.1.3. onto PG 7.2 has error trying to use the following function (frop OpenACS, to port connect by Oracle statments under PG): create function tree_level(varchar) returns integer as ' declare inkey alias for $1; cnt integer default 0; begin for i in 1..length(inkey) LOOP if substr(inkey,i,1) = ''/'' then cnt := cnt + 1; end if; end LOOP; return cnt; end;' language 'plpgsql'; example: dbks=# select tree_level(t01_tree_sortkey) from t01_20011231 limit 10; ERROR: SearchSysCache: Bad cache id 27 I found the SearchSysCache() function in tools/cache in the PG srcs. Readding the code, I tought about corruption problems on the database. I made a vacuum --analyze dbks, hopping this would "repair" the DB. But this still not works. So I'm a bit confused. What's wrong? Thanks in advance and best regards. -- Jean-Paul ARGUDO ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] function tree_level(varchar) (from OpenACS) no longer work under 7.2
> > ERROR: SearchSysCache: Bad cache id 27 > This is not a bug, it is a broken installation. You are trying to > invoke a 7.1 plpgsql in a 7.2 backend. Right analysis Tom! createlang with old plpgsql.so ... had to drop functions / triggers using old plpgsql.so (thanks to a select on pg_proc) drop the language create the language rebuild functions and triggers: all ok now.. Thanks Tom. -- Jean-Paul ARGUDO ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] cvs update, configure, make, error in bootstrap.* ?...
Hi, Tried to compile PG from CVS today, my platform is: $ uname -a Linux pastis 2.4.17-686 #2 Sat Dec 22 21:58:49 EST 2001 i686 unknown $ gcc -v Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs gcc version 2.95.4 20011002 (Debian prerelease) I do a simple ./configure then a simple make And the error is: " [...] make[3]: Entering directory `/home/jpargudo/etudes/postgresql-cvs/pgsql-cvs-snapshot-20020423/src/backend/bootstrap' gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I../../../src/include -c -o bootparse.o bootparse.c bootparse.y: In function `Int_yyparse': bootparse.y:276: structure has no member named `class' make[3]: *** [bootparse.o] Erreur 1 make[3]: Leaving directory `/home/jpargudo/etudes/postgresql-cvs/pgsql-cvs-snapshot-20020423/src/backend/bootstrap' make[2]: *** [bootstrap-recursive] Erreur 2 make[2]: Leaving directory `/home/jpargudo/etudes/postgresql-cvs/pgsql-cvs-snapshot-20020423/src/backend' make[1]: *** [all] Erreur 2 make[1]: Leaving directory `/home/jpargudo/etudes/postgresql-cvs/pgsql-cvs-snapshot-20020423/src' make: *** [all] Erreur 2 " I can't find anywhere such already notifyied bug :-( What am I doing wrong?... I'll watch the source and try to guess what's wrong in bootstrap.* ... Cheers, -- Jean-Paul ARGUDOIDEALX S.A.S Consultant bases de données 15-17, av. de Ségur http://www.idealx.com F-75007 PARIS ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] cvs update, configure, make, error in bootstrap.* ?...
> You seem to have an out-of-date bootparse.c. Perhaps a timestamp skew > problem? Try removing bootstrap_tokens.h and bootparse.c, then try > again. Yup. Works. I had to do it many times to make it work... strange :) I noticed many .cvsignore in many folders (there is one in src/backend/bootstrap for example), is that ok? Thanks for the right help :) -- Jean-Paul ARGUDOIDEALX S.A.S Consultant bases de données 15-17, av. de Ségur http://www.idealx.com F-75007 PARIS ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Bug #726: PHP/PG ERROR: parser: parse error at or near ","
Stephan:
Thanks for the tip, as it helped me find the error. And it has been
solved.
Bugs List:
You might want to remove this report. It was a PEBCAK 100%. I feel
pretty stupid about the whole thing (and my head hurts from the banging
against the wall).
Thanks for the quick response, though, it's greatly appreciated.
Paul Redd-LaFlamme
-Original Message-
From: Stephan Szabo [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 31, 2002 12:00 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [BUGS] Bug #726: PHP/PG ERROR: parser: parse error at or
near ","
On Wed, 31 Jul 2002 [EMAIL PROTECTED] wrote:
> Paul Redd-LaFlamme ([EMAIL PROTECTED]) reports a bug with a
severity of 1
> The lower the number the more severe it is.
>
> Short Description
> PHP/PG ERROR: parser: parse error at or near ","
>
> Long Description
> The SQL statement
> INSERT INTO users (user_name, comp_id, password) VALUES
('Yuk',8,'Yuk');
>
> works fine when typed directly into the postgres shell, but yields the
error
>
> ERROR: parser: parse error at or near ","
>
> when, and only when the '8' is inserted into the string in PHP by any
means.
>
> In other words, the SQL statement provided works fine when hardcoded,
> but fails every single time (making my application useless) when the
> integer insert value is inserted into the PHP statement by any means.
Try turning on query logging to see what sql statement the backend
thinks
it's getting.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[BUGS] BUG #5804: Connection aborted after many queries.
The following bug has been logged online:
Bug reference: 5804
Logged by: Paul J. Davis
Email address: paul.joseph.da...@gmail.com
PostgreSQL version: 9.0.2
Operating system: OS X 10.6.5, Ubuntu 10.04
Description:Connection aborted after many queries.
Details:
After running many queries (millions) a connection will report an error that
the server has unexpectedly closed the connection. I first noticed this
through psycopg2, but I've been able to reproduce it with a small C program
using only libpq which I've included below. I compiled this against a libpq
built by Homebrew (after upgrading the formula to use a 9.0.2 tarball) on OS
X 10.6.5. The server was installed from 9.0.2 package available from
https://launchpad.net/~pitti/+archive/postgresql
My next step is to try building libpq with --enable-cassert to see if that
triggers anything client side. Let me know if there's something else I
should be doing to debug this.
This test has been bailing between 2.6 and 2.7M queries:
#include
#include
#include "libpq-fe.h"
static void
fail(PGconn* conn, PGresult* res)
{
if(res != NULL) PQclear(res);
PQfinish(conn);
exit(1);
}
static void
check(PGconn* conn, PGresult* res, const char* fmt)
{
ExecStatusType status = PQresultStatus(res);
if(status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK)
{
fprintf(stderr, fmt, PQerrorMessage(conn));
fail(conn, res);
}
}
void
run_query(PGconn* conn, PGresult* res)
{
int nFields, i, j;
res = PQexec(conn, "DECLARE myportal CURSOR FOR select 1");
check(conn, res, "DECLARE CURSOR failed: %s");
PQclear(res);
res = PQexec(conn, "FETCH ALL in myportal");
check(conn, res, "FETCH ALL failed: %s");
nFields = PQnfields(res);
for(i = 0; i < PQntuples(res); i++)
{
for(j = 0; j < nFields; j++)
{
PQgetvalue(res, i, j);
}
}
PQclear(res);
res = PQexec(conn, "CLOSE myportal");
check(conn, res, "CLOSE failed: %s");
PQclear(res);
}
int
main(int argc, char **argv)
{
PGconn* conn;
PGresult* res;
int i;
if(argc != 2)
{
fprintf(stderr, "usage: %s DSN\n", argv[0]);
exit(1);
}
conn = PQconnectdb(argv[1]);
if(PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection failed: %s", PQerrorMessage(conn));
fail(conn, NULL);
}
res = PQexec(conn, "BEGIN");
check(conn, res, "BEGIN failed: %s");
PQclear(res);
for(i = 0; i < 1000; i++)
{
if((i+1) % 10 == 0)
{
fprintf(stderr, "I: %d\n", i);
}
run_query(conn, res);
}
res = PQexec(conn, "END");
check(conn, res, "END failed: %s");
PQclear(res);
PQfinish(conn);
return 0;
}
--
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 #2520: security problem
The following bug has been logged online: Bug reference: 2520 Logged by: Jean Paul Aguilar Ruiz Email address: [EMAIL PROTECTED] PostgreSQL version: 807,814 Operating system: Win xp Description:security problem Details: Hi, When you add a user as owner of database and the database has been created for another user as sistem administrator the user dont have all permissions òn the database, but if the database is created for the user (not sa) he can do all. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #2406: Not all systems support SHM_SHARE_MMU
The following bug has been logged online: Bug reference: 2406 Logged by: Paul van der Zwan Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.3 Operating system: Solaris Description:Not all systems support SHM_SHARE_MMU Details: Only systems with large pagesizes support ISM, so always defining #define PG_SHMAT_FLAGS SHM_SHARE_MMU in src/backend/port/sysv_shmem.c will cause all calls to shmat to fail with EINVAL on systems that do not support large pages. The following may be a better check: #if def SHM_SHARE_MMU #define PG_SHMAT_FLAGS ((getpagesizes(0,NULL)>1)?SHM_SHARE_MMU:0) #else #define PG_SHMAT_FLAGS 0 #endif This problem manifested itself on a VIA Mini ITX system and Solaris Nevada ( build 36) Paul van der Zwan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2406: Not all systems support SHM_SHARE_MMU
On 25-apr-2006, at 7:48, Tom Lane wrote: "Paul van der Zwan" <[EMAIL PROTECTED]> writes: Only systems with large pagesizes support ISM, so always defining #define PG_SHMAT_FLAGS SHM_SHARE_MMU in src/backend/port/sysv_shmem.c will cause all calls to shmat to fail with EINVAL on systems that do not support large pages. That code's been in there since PG 7.3, and no one before you has complained. Are you sure you've identified the problem correctly? regards, tom lane I am 99% sure that is the cause. If I put shmsys:ism_off=1 in /etc/ system it ignores the SHM_SHARE_MMU flag and it works. Maybe noone ever ran Postgres on Solaris on a VIA Epia system. I haven't rebuilt postgres with my suggested patch (yet) so that's were the 1% doubt comes in. I'll try to do that sometime this week. Paul ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2406: Not all systems support SHM_SHARE_MMU
On 25-apr-2006, at 9:08, Tom Lane wrote: Paul van der Zwan <[EMAIL PROTECTED]> writes: Maybe noone ever ran Postgres on Solaris on a VIA Epia system. Maybe. What is a "VIA Epia system"? VIA is a hardware manufacturer who make small, low power boards with their own X86 compatible cpu on it, you can find more about it on : http://www.via.com.tw/en/ products/mainboards/mini_itx/epia/index.jsp Frankly, I'm afraid that your patch is likely to break way more systems than it fixes. What is getpagesizes(), and is it guaranteed to exist on *every* Solaris system? What the heck correlation does its result have to whether SHM_SHARE_MMU will work? AFAIK getpagesizes() appeared in 2001 so that probably means it is missing in anything before Solaris 9. If you look at line 308 of http://cvs.opensolaris.org/source/xref/on/ usr/src/uts/common/os/shm.c you'll see that shmat return EINVAL if only one pagesize is available. Which is what happens on my system, and possibly also on older (32 bit pre Ultra ) Sparc systems. My guess is that all UltraSparce and 'modern' x86/amd64 cpu's support large pages and therefor will n ever hit this failure mode of shmat(). I'll see if I can get the x86 experts here to have a look at it... Regards Paul ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2406: Not all systems support SHM_SHARE_MMU
On 25-apr-2006, at 16:46, Tom Lane wrote:
Paul van der Zwan <[EMAIL PROTECTED]> writes:
AFAIK getpagesizes() appeared in 2001 so that probably means it is
missing in anything before Solaris 9.
We could handle this without relying on getpagesizes() by just trying
and falling back:
#ifdef SHM_SHARE_MMU
memAddress = shmat(shmid, addr, SHM_SHARE_MMU);
if (memAddress == (void *) -1 && errno == EINVAL)
memAddress = shmat(shmid, addr, 0);
#else
memAddress = shmat(shmid, addr, 0);
#endif
That would be a clean solution ( and was suggested by some of my
colleagues as well)
However, I would argue that a system is pretty broken if it exposes
the
SHM_SHARE_MMU #define and then rejects it at runtime.
It is just a define, the fact that this define exists has nothing to
do with it having
any meaning. It's not like a HAVE_ISM flag. shmat() can fail for a
number of reasons, one of
them is not having ISM available on the current system.
I'll see if I can get the x86 experts here to have a look at it...
I think either Solaris/x86 should not expose this #define, or it
should
silently ignore the bit at runtime. AFAICS, SHM_SHARE_MMU has no
guaranteed semantic effect anyway, it's just a performance hint; so
ignoring it on platforms that can't handle it is reasonable.
I disagree, I have no definite info why it is a hard failure,
probably because
there is no way to communicate to the app that it's request is
ignored. System calls
either fail or succeed. And introducing a new errno value just for
this is overkill, I guess.
regards, tom lane
Regards
Paul
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] BUG #2406: Not all systems support SHM_SHARE_MMU
On 25-apr-2006, at 20:34, Tom Lane wrote: Paul van der Zwan <[EMAIL PROTECTED]> writes: On 25-apr-2006, at 16:46, Tom Lane wrote: AFAICS, SHM_SHARE_MMU has no guaranteed semantic effect anyway, it's just a performance hint; so ignoring it on platforms that can't handle it is reasonable. I disagree, I have no definite info why it is a hard failure, probably because there is no way to communicate to the app that it's request is ignored. Which applications do you think will do anything except exactly what you are proposing we do, ie, just redo the call without the flag bit? Why are you going to make every application jump through this hoop in order to cope with a (possibly temporary) inadequacy in some seldom-used versions of Solaris? We'll probably put in the kluge because we have no other choice, but I strongly disagree that it's our problem. I think I have to make something clear, I am not part of the Solaris Engineering group and even though I work for Sun I personally have probably less influence on Solaris than a customer. What I wrote/write is my personal opinion and I should insert the usual disclaimer about me not 'officially' representing Sun Microsystems . I personally do believe that silently failing or ignoring something an application asks for explicitely is bad, if the application wants it and does not get it, the OS should communicate this to the application. I feel it is up to the application and not to the OS to decide how to respond when the request fails. It may be true that all or most applications will just redo it, or they may do something else because ISM is not present, to be honest I do not know. The code you suggested is IMHO a clean way to ask for an optimization and gracefully accept the denial and continue without it. My guess is the absence of ISM on the VIA cpu is purely a hardware issue and not related to a 'seldom used version of Solaris' as there are no different versions of Solaris, only different releases. If the hardware does not support something it may be difficult or impossible for an OS to implement a feature. It would be nice though if every CPU supports the large pages so the failure would never happen. Regards Paul ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
