Re: [HACKERS] Strange interval arithmetic
On Wed, Nov 30, 2005 at 07:06:42PM -0300, Alvaro Herrera wrote: > Hmm, why not check both the return value _and_ errno: > > val = strtol(field[i], &cp, 10); > if (val == LONG_MAX && errno == ERANGE) > return DTERR_FIELD_OVERFLOW; I usually check both in my own code but I noticed several places where PostgreSQL doesn't, so I kept that style. I'll check both if that's preferred. -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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: [HACKERS] Strange interval arithmetic
On Wed, Nov 30, 2005 at 02:01:46PM -0500, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > Any preferences on an approach? The simplest and easiest to verify > > would be to raise an error for just this particular case; a TODO > > item might be to change how the string is parsed to allow values > > larger than LONG_MAX. > > I think the latter would be a feature enhancement and therefore not > good material to back-patch. Just erroring out seems appropriate > for now. Agreed. I'm thinking about rewriting strtol() calls in datetime.c to look like this: errno = 0; val = strtol(field[i], &cp, 10); if (errno == ERANGE) return DTERR_FIELD_OVERFLOW; Does that look okay? Or would you rather raise an error with ereport()? > > I see several calls to strtol() that aren't checked for overflow but > > that might not be relevant to this problem, so I'm thinking this patch > > ought not touch them. Maybe that's another TODO item. > > If it's possible for them to be given overflowing input, they probably > ought to be checked. I'm looking at all the strtol() calls in datetime.c right now; I haven't looked anywhere else yet. Should I bother checking values that will be range checked later anyway? Time zone displacements, for example? -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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: [HACKERS] Strange interval arithmetic
On Wed, Nov 30, 2005 at 12:37:40PM -0500, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > >> I see this behavior back to at least 7.3. I'd guess it's because > >> strtol() indicates overflow by returning LONG_MAX and setting errno > >> to ERANGE, but the code doesn't check for that. > > > Is this worth looking at for the upcoming dot releases? > > Sure, send a patch ... Any preferences on an approach? The simplest and easiest to verify would be to raise an error for just this particular case; a TODO item might be to change how the string is parsed to allow values larger than LONG_MAX. I see several calls to strtol() that aren't checked for overflow but that might not be relevant to this problem, so I'm thinking this patch ought not touch them. Maybe that's another TODO item. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Strange interval arithmetic
On Sun, Nov 27, 2005 at 11:27:54AM -0700, Michael Fuhr wrote: > On Sun, Nov 27, 2005 at 08:45:18AM -0700, Michael Fuhr wrote: > > Looks like the value is stuck at 2^31 - 1 seconds: > > I see this behavior back to at least 7.3. I'd guess it's because > strtol() indicates overflow by returning LONG_MAX and setting errno > to ERANGE, but the code doesn't check for that. Is this worth looking at for the upcoming dot releases? It's apparently a longstanding behavior that almost nobody encounters, yet knowingly not addressing it seems a bit MySQLish ;-) Here's the start of the thread for anybody who missed it: http://archives.postgresql.org/pgsql-hackers/2005-11/msg01385.php -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] BIN()
On Wed, Nov 30, 2005 at 07:42:36AM +0100, Tino Wildenhain wrote: > In python, I usually go like this: In Ruby (and therefore in PL/Ruby) you could do this: 10.to_s(2) => "1010" 10.to_s(16) => "a" -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] BIN()
On Tue, Nov 29, 2005 at 07:57:58PM -0700, Michael Fuhr wrote:
> Any reason not to use sprintf("%b", $_[0])?
Or something like this in SQL or PL/pgSQL:
test=> SELECT ltrim(textin(bit_out(12::bit(64))), '0');
ltrim
---
1100
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] BIN()
On Tue, Nov 29, 2005 at 09:46:13PM -0500, Andrew Dunstan wrote:
> create or replace function bin(bigint) returns text language plperl as $$
>
> my $arg = $_[0] + 0;
> my $res = "";
> while($arg)
> {
>$res = ($arg % 2) . $res;
>$arg >>= 1;
> }
> return $res;
>
> $$;
Any reason not to use sprintf("%b", $_[0])?
--
Michael Fuhr
---(end of broadcast)---
TIP 1: 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
[HACKERS] Anonymous CVS working?
Any problems with CVS or anonymous CVS since the work last evening? Anonymous CVS hasn't given me the following commit yet; it's been almost twelve hours since it was made: http://archives.postgresql.org/pgsql-committers/2005-11/msg00553.php -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Array comparisons involving NULL
Is there a built-in way to look for NULL array elements? I was
thinking of something like NULL IS DISTINCT FROM ALL (array expression)
but that doesn't work:
test=> SELECT NULL IS DISTINCT FROM ALL (ARRAY[1, 2, 3]);
ERROR: syntax error at or near "ALL" at character 30
LINE 1: SELECT NULL IS DISTINCT FROM ALL (ARRAY[1, 2, 3]);
^
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Strange interval arithmetic
On Sun, Nov 27, 2005 at 08:45:18AM -0700, Michael Fuhr wrote: > Looks like the value is stuck at 2^31 - 1 seconds: I see this behavior back to at least 7.3. I'd guess it's because strtol() indicates overflow by returning LONG_MAX and setting errno to ERANGE, but the code doesn't check for that. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Strange interval arithmetic
On Sun, Nov 27, 2005 at 11:15:04PM +0800, Christopher Kings-Lynne wrote: > What's going on here? Some sort of integer wraparound? [...] > test=# select interval '2378234234 seconds'; >interval > -- > 596523:14:07 > (1 row) Looks like the value is stuck at 2^31 - 1 seconds: test=> select interval '2147483646 seconds'; -- 2^31 - 2 interval -- 596523:14:06 (1 row) test=> select interval '2147483647 seconds'; -- 2^31 - 1 interval -- 596523:14:07 (1 row) test=> select interval '2147483648 seconds'; -- 2^31 interval -- 596523:14:07 (1 row) -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Are NULLs in Arrays compressed?
On Mon, Nov 21, 2005 at 04:46:40PM -0500, [EMAIL PROTECTED] wrote: > On Mon, Nov 21, 2005 at 08:51:32PM +, Simon Riggs wrote: > > Are NULLs in Arrays compressed? > > Just as NULLs are with normal unary datatypes. > > I thought NULLs don't work in arrays yet? :-) http://archives.postgresql.org/pgsql-committers/2005-11/msg00385.php http://developer.postgresql.org/docs/postgres/arrays.html -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Plan chosen for PQexecParams
On Sun, Nov 20, 2005 at 08:34:39PM -0500, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > Here's a simplified client program (the original has > > error checking but I've stripped it out for brevity; this simplified > > version behaves the same way): > > I get the same results for all three after fixing the subscripting > mistake: > > - values[1] = "Fairview"; > + values[0] = "Fairview"; > > Is your original program making the same mistake? [Smacks forehead.] Argh, that's what I get for thinking in one language while coding in another :-( Yeah, that's the problem; sorry for the noise. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Plan chosen for PQexecParams
On Sun, Nov 20, 2005 at 05:21:03PM -0500, Tom Lane wrote:
> Michael Fuhr <[EMAIL PROTECTED]> writes:
> > Is PQexecParams just shorthand for a prepare followed by an execute?
>
> Yes, but it uses the unnamed statement, so in recent server versions you
> should get a postponed plan that uses the Bind parameter values. What
> test case are you looking at exactly?
I'm using 8.1.0 from CVS. I have a table that contains city names;
I can send you the SQL to create and populate a test table if
necessary. Here's a simplified client program (the original has
error checking but I've stripped it out for brevity; this simplified
version behaves the same way):
#include "libpq-fe.h"
#include
#include
#include
int
main(void)
{
PGconn *conn;
PGresult*res;
const char *query_fixed;
const char *query_param;
char const *values[1];
query_fixed = "SELECT * FROM city WHERE name = 'Fairview'";
query_param = "SELECT * FROM city WHERE name = $1";
values[1] = "Fairview";
conn = PQconnectdb("dbname=test");
res = PQexec(conn, "SET debug_print_plan TO on");
res = PQexec(conn, "SET client_min_messages TO debug1");
fprintf(stderr, "# PQexec\n");
PQexec(conn, query_fixed);
fprintf(stderr, "# PQexecParams\n");
PQexecParams(conn, query_param, 1, NULL, values, NULL, NULL, 0);
fprintf(stderr, "# PQprepare\n");
PQprepare(conn, "stmt", query_param, 1, NULL);
PQfinish(conn);
return EXIT_SUCCESS;
}
When I run this program I see the following; you can see that
plan_rows and the plan itself differ:
% ./exectest | & egrep 'PQ|DETAIL'
# PQexec
DETAIL: {BITMAPHEAPSCAN :startup_cost 2.12 :total_cost 54.87 :plan_rows 35
:plan_width
# PQexecParams
DETAIL: {INDEXSCAN :startup_cost 0.00 :total_cost 6.01 :plan_rows 2
:plan_width 16
# PQprepare
DETAIL: {INDEXSCAN :startup_cost 0.00 :total_cost 6.01 :plan_rows 2
:plan_width 16
If I insert tens of thousands of matching rows, re-analyze, and
disable enable_bitmapscan, I get the following:
% ./exectest | & egrep 'PQ|DETAIL'
# PQexec
DETAIL: {SEQSCAN :startup_cost 0.00 :total_cost 1396.90 :plan_rows 40220
:plan_width
# PQexecParams
DETAIL: {INDEXSCAN :startup_cost 0.00 :total_cost 7.89 :plan_rows 3
:plan_width 16
# PQprepare
DETAIL: {INDEXSCAN :startup_cost 0.00 :total_cost 13.73 :plan_rows 6
:plan_width 16
pg_stat_user_tables show one new seq_scan and one new idx_scan,
which corresponds to the plans shown (the program doesn't call
PQexecPrepared so the third statement never gets executed). Also,
this particular example shows a difference between PQexecParams and
PQprepare that I hadn't noticed before.
Is my test flawed? Have I overlooked something?
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Plan chosen for PQexecParams
I've noticed that if a client uses PQexecParams, the query plan appears to be identical to the plan chosen for PQprepare/PQexecPrepared, which might not be as optimal as a plan chosen for PQexec. I can understand the PQprepare case since the planner doesn't know what parameters will actually be used, but with PQexecParams shouldn't the planner have all the information it needs to choose a plan based on specific parameters? Is PQexecParams just shorthand for a prepare followed by an execute? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Anyone want to fix plperl for null array elements?
On Thu, Nov 17, 2005 at 08:41:51PM -0500, Tom Lane wrote:
> I think plperl should be fixed to translate undef to NULL when returning
> an array, but currently it translates to an empty string:
I'll take a look at this if nobody else steps up. It might just
be a minor change to this part of plperl.c:
210 "else " \
211 "{ " \
212 " my $str = qq($elem); " \
213 " $str =~ s/([\"])/$1/g; " \
214 " $res .= qq(\"$str\"); " \
215 "} " \
> There might be some problems going in the other direction, too;
> I haven't tried. Anybody feeling eager to fix this?
Does the current implementation provide automatic conversion to a
Perl array for inbound values? Unless I'm missing something that
entire problem might still need to be solved.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Cursor estimated row count
On Sat, Nov 12, 2005 at 01:50:20PM -0500, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > An occasionally asked question is "How can I find out how many rows > > a cursor will return?" to which the answer is "Fetch them all." But > > what about a way to get the planner's estimate? Would anybody find > > that useful? > > Given how far off it frequently is, I can't believe that any of the > people who ask for the feature would find this a satisfactory answer :-( That aside, do you see any problems with the code? Is it at least returning the right wrong answer? ;-) -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Cursor estimated row count
An occasionally asked question is "How can I find out how many rows
a cursor will return?" to which the answer is "Fetch them all." But
what about a way to get the planner's estimate? Would anybody find
that useful? Does the code below look close to being correct?
test=> EXPLAIN SELECT * FROM pg_class;
QUERY PLAN
Seq Scan on pg_class (cost=0.00..6.88 rows=188 width=163)
(1 row)
test=> BEGIN;
BEGIN
test=> DECLARE curs CURSOR FOR SELECT * FROM pg_class;
DECLARE CURSOR
test=> SELECT cursor_plan_rows('curs');
cursor_plan_rows
--
188
(1 row)
#include "postgres.h"
#include "fmgr.h"
#include "nodes/pg_list.h"
#include "nodes/plannodes.h"
#include "utils/portal.h"
Datum cursor_plan_rows(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(cursor_plan_rows);
Datum
cursor_plan_rows(PG_FUNCTION_ARGS)
{
char*portalname = PG_GETARG_CSTRING(0);
Portal portal;
Plan*plan;
portal = GetPortalByName(portalname);
if (!PortalIsValid(portal)) {
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_CURSOR),
errmsg("cursor \"%s\" does not exist", portalname)));
}
if (!portal->planTrees) {
ereport(ERROR,
(errcode(ERRCODE_INVALID_CURSOR_STATE),
errmsg("cursor \"%s\" has no plan trees", portalname)));
}
plan = linitial(portal->planTrees);
if (!plan) {
ereport(ERROR,
(errcode(ERRCODE_INVALID_CURSOR_STATE),
errmsg("cursor \"%s\" plan is NULL", portalname)));
}
PG_RETURN_FLOAT8(plan->plan_rows);
}
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] relfilenode
On Thu, Oct 27, 2005 at 09:12:15PM -0400, Andrew Dunstan wrote: > The docs have this description for pg_class::relfilenode: "Name of the > on-disk file of this relation; 0 if none". However, Elein just pointed > out to me that there are no entries with 0, so this description seems > incorrect. What should we say? It appears that in at least some of these > cases the value is the same as the oid. Is 0 a legitimate value now that pg_xactlock is gone? In pre-8.1 clusters that's the only relation I see with relfilenode 0, but maybe that's just because I don't have anything defined that would have relfilenode 0 (what, if anything, would?). Composite types have relfilenode set even though they don't appear to have an on-disk file -- should they be 0? Based on what I've seen I've assumed that relfilenode starts out the same as oid but can change if you do something that rewrites the table (truncate, cluster, alter column type, etc.). I haven't dug into the code to confirm that, though. -- Michael Fuhr ---(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: [HACKERS] enums
On Thu, Oct 27, 2005 at 07:02:45PM -0400, Andrew Dunstan wrote:
> Jim C. Nasby wrote:
> >On another note, I noticed that the comparison operators seem to be
> >comparing the underlying numeric value used to store the enum, which is
> >wrong IMO. Consider:
> >
> >ENUM color '"red","blue","green"'
> >CREATE TABLE t (c color);
> >INSERT INTO t VALUES('blue');
> >INSERT INTO t VALUES('green');
> >INSERT INTO t VALUES('red');
> >SELECT c FROM t ORDER BY c;
> >red
> >blue
> >green
> >
> >That seems counter-intuitive. It's also exposing an implimentation
> >detail (that the enum is stored internally as a number).
>
> No it is not. Not in the slightest. It is honoring the enumeration order
> defined for the type. That is the ONLY correct behaviour, IMNSHO.
I agree. Honoring the enumeration order makes sense if you consider
the values as things that should be ordered based on some property
of their thingness instead of based on what their labels happen to
be in a particular language. If I have an enumeration of colors I
might want values sorted by their position in the spectrum, so
whether the labels are (red, green, blue) or (gorri, berde, urdin)
I might want to maintain that particular order.
If you want values ordered lexically then you can enumerate them
that way. Why force that behavior on people who want to order based
on some other criteria?
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Test settings in postgresql.conf.sample in beta4
On Thu, Oct 27, 2005 at 09:54:03AM -0700, Josh Berkus wrote: > Looks like someone left their test settings in postgresql.conf.sample in > the beta4 release: > > -Line 224 > silent_mode = true > #silent_mode = false # DO NOT USE without syslog or > redirect_stderr Are you sure you're looking at the right file? I see that in neither CVS nor the 8.1beta4 tarball. In the latest version of that file silent_mode is on line 286 and its value is "off", not 'false"; based on your output I'd guess you're looking at an 8.0 configuration file. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] expanded \df+ display broken in beta4
On Tue, Oct 25, 2005 at 02:27:50PM -0400, Robert Treat wrote: > not sure exactly when this was changed, but expanded display of \df+ > output is broken in beta4. http://archives.postgresql.org/pgsql-hackers/2005-06/msg00423.php http://archives.postgresql.org/pgsql-committers/2005-06/msg00149.php -- Michael Fuhr ---(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: [HACKERS] New timezone data
On Mon, Oct 24, 2005 at 07:02:07PM -0400, Andrew Dunstan wrote: > There's nothing magical about the files, is there? A user should be able > to plug in a zic-compiled zone file from just about anywhere if they > really need it, without having to update postgres, or I have badly > misunderstood how we handle things. Sure users can do that if they need to; I was just wondering about the policy for keeping the source code current. I do see an item in src/tools/RELEASE_CHANGES suggesting that releases should have the latest timezone data: * Update timezone data to match latest zic database (see src/timezone/README) So in case anybody was going to check on that prior to the upcoming 8.1 release candidate, there is new timezone data available (2005n vs. the current 2005m) but it appears to have only minor changes for Kyrgyzstan and Uruguay. If anybody wants, I'll submit a patch. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Creating table in different database
On Mon, Oct 24, 2005 at 11:32:55PM +0530, Paresh Bafna wrote: > If there are multiple databases, say db1 and db2. > And currently we are in db1, can we create table in db2 (without > switching databases)? > Is there any query to do this? This question seems off-topic for pgsql-hackers. The mailing list's description says: The PostgreSQL developers team lives here. Discussion of current development issues, problems and bugs, and proposed new features. If your question cannot be answered by people in the other lists, and it is likely that only a developer will know the answer, you may re-post your question in this list. You must try elsewhere first! You could use contrib/dblink, but perhaps you should be using schemas instead of separate databases. -- Michael Fuhr ---(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
[HACKERS] New timezone data
I see that new timezone data is available at ftp://elsie.nci.nih.gov/pub/ It looks like the only changes from PostgreSQL's current data involve Kyrgyzstan and Uruguay. What's the policy on keeping the source code up to date? Does the data change too often to bother except just before a release? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 2nd try @NetBSD/2.0 Alpha
On Tue, Oct 18, 2005 at 08:59:23PM -0600, Michael Fuhr wrote: > On Tue, Oct 18, 2005 at 09:41:21PM -0500, Larry Rosenman wrote: > > I could not find a truss/strace binary on the box :( > > In BSD land try ktrace. ...or attach with a debugger like gdb. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 2nd try @NetBSD/2.0 Alpha
On Tue, Oct 18, 2005 at 09:41:21PM -0500, Larry Rosenman wrote: > I could not find a truss/strace binary on the box :( In BSD land try ktrace. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] COALESCE bug
On Mon, Oct 10, 2005 at 03:56:10PM +0200, Daniel Schuchardt wrote: > my PostgreSQL 8.0.0 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 > > crashes when > > SELECT * FROM pg_stat_activity WHERE datname=COALESCE(NULL, NULL); Already reported and fixed as of 8.0.3; consider upgrading (8.0.4 is the latest). http://archives.postgresql.org/pgsql-bugs/2005-04/msg00058.php http://archives.postgresql.org/pgsql-committers/2005-04/msg00134.php -- Michael Fuhr ---(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: [HACKERS] Build Farm: thrush
On Mon, Oct 03, 2005 at 05:19:43PM +0200, Gaetano Mendola wrote:
> Tom Lane wrote:
> > Gaetano Mendola <[EMAIL PROTECTED]> writes:
> >> I'm the administrator of that machine and PLCheck is failing.
> >> Is there anything I can do to fix it ?
> >
> > What version of Python have you got on that thing? It seems to be
> > emitting still another spelling of the encoding error message :-(
>
> $ python -V
> Python 2.2.3
The attached variant file (plpython_error_2.out) should allow this
Python version's wording of the error message.
--
Michael Fuhr
-- test error handling, i forgot to restore Warn_restart in
-- the trigger handler once. the errors and subsequent core dump were
-- interesting.
SELECT invalid_type_uncaught('rick');
WARNING: plpython: in function invalid_type_uncaught:
DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare
ERROR: type "test" does not exist
SELECT invalid_type_caught('rick');
WARNING: plpython: in function invalid_type_caught:
DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare
ERROR: type "test" does not exist
SELECT invalid_type_reraised('rick');
WARNING: plpython: in function invalid_type_reraised:
DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare
ERROR: type "test" does not exist
SELECT valid_type('rick');
valid_type
(1 row)
--
-- Test Unicode error handling.
--
SELECT unicode_return_error();
ERROR: plpython: function "unicode_return_error" could not create return value
DETAIL: exceptions.UnicodeError: ASCII encoding error: ordinal not in
range(128)
INSERT INTO unicode_test (testvalue) VALUES ('test');
ERROR: plpython: function "unicode_trigger_error" could not modify tuple
DETAIL: exceptions.UnicodeError: ASCII encoding error: ordinal not in
range(128)
SELECT unicode_plan_error1();
WARNING: plpython: in function unicode_plan_error1:
DETAIL: plpy.Error: Unknown error in PLy_spi_execute_plan
ERROR: plpython: function "unicode_plan_error1" could not execute plan
DETAIL: exceptions.UnicodeError: ASCII encoding error: ordinal not in
range(128)
SELECT unicode_plan_error2();
ERROR: plpython: function "unicode_plan_error2" could not execute plan
DETAIL: exceptions.UnicodeError: ASCII encoding error: ordinal not in
range(128)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Expression index ignores column statistics target
On Sat, Oct 01, 2005 at 02:42:32AM -0400, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > Would an ALTER INDEX SET STATISTICS form be possible? > > It's not so much the table/index misnomer that's bothering me, it's > the lack of a clean way to identify which column of the index you > are talking about. Ah, I see -- I wasn't thinking about expressions in multicolumn indexes. What about identifying the column with the expression itself, ala quote_ident(pg_get_indexdef())? That might be tedious for the user to type but it would be attractive from a self-documentation standpoint. ALTER INDEX indexname ALTER COLUMN "the expression" SET STATISTICS 100; I do see that indexes allow multiple instances of the same expression, so this approach could be ambiguous. Or should such repetition be prohibited as it is with column names? test=> CREATE TABLE foo (x integer); CREATE TABLE test=> CREATE INDEX foo1_idx ON foo (x, x); ERROR: duplicate key violates unique constraint "pg_attribute_relid_attnam_index" test=> CREATE INDEX foo2_idx ON foo (abs(x), abs(x)); CREATE INDEX -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Expression index ignores column statistics target
On Sat, Oct 01, 2005 at 02:19:06AM -0400, Tom Lane wrote: > I wrote: > > I seem to recall bringing up the question of whether > > we could find a less implementation-specific way of commanding this > > behavior, but I can't find it in the archives right now. > > Ah, here it is: > http://archives.postgresql.org/pgsql-hackers/2004-03/msg00502.php > > No responses :-( Would an ALTER INDEX SET STATISTICS form be possible? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Expression index ignores column statistics target
On Sat, Oct 01, 2005 at 12:53:03AM -0400, Tom Lane wrote: > The code does in fact honor per-column statistics targets attached to > expression indexes, viz > > alter table myfuncindex alter column pg_expression_1 set statistics 100; Aha -- that's the piece I didn't know about. I was wondering where those statistics were being stored, since they were affected by default_statistics_target but not by per-column statistics targets. And now I see them when I don't restrict queries against pg_stats by just the table or column name. Thanks. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Expression index ignores column statistics target
On Fri, Sep 30, 2005 at 11:59:26PM -0400, Bruce Momjian wrote: > This is expected. The main TODO items is: > > * Allow accurate statistics to be collected on indexes with more than > one column or expression indexes, perhaps using per-index statistics > > Basically, we don't have multi-column or expression statistics. ANALYZE > just analyzes columns, even if an expression index exists. But the row count estimates imply that expression index queries do use column statistics, presumably as a proxy in the absence of expression statistics. This looks like a relevant commit: http://archives.postgresql.org/pgsql-committers/2004-02/msg00124.php The behavior I observed is that the planner does appear to use column statistics when planning an expression index query, but it doesn't appear to honor a column's non-default statistics target. In other words: * Row count estimates for expression index queries (at least simple ones) are reasonably accurate for the N most common column values, where N is the value of default_statistics_target when ANALYZE was run. * Specifically setting the column's statistics target with ALTER TABLE SET STATISTICS doesn't result in better statistics for expression index queries. That difference in behavior seems odd: if default_statistics_target has an effect, why doesn't ALTER TABLE SET STATISTICS? -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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
[HACKERS] Expression index ignores column statistics target
I've noticed that row count estimates for expression indexes appear
to rely on default_statistics_target rather than on a column's
actual statistics target. That is, if I use ALTER TABLE SET
STATISTICS to increase a column's statistics target and then run
ANALYZE, then estimates for non-expression-index queries improve
as expected. However, queries that use an expression index remain
accurate for only around the N most common values, where N is the
default_statistics_target that was in effect when ANALYZE ran. I'm
still rummaging through the archives looking for past discussion;
is this behavior a known limitation or just an oversight?
CREATE TABLE foo (x integer);
CREATE INDEX foo_x_idx ON foo (x);
CREATE INDEX foo_abs_x_idx ON foo (abs(x));
INSERT INTO foo (x)
SELECT r1 % r2
FROM generate_series(1, 100) AS g1(r1),
generate_series(1, 100) AS g2(r2);
SET default_statistics_target TO 15;
ALTER TABLE foo ALTER COLUMN x SET STATISTICS 20;
ANALYZE foo;
SELECT most_common_vals FROM pg_stats WHERE attname = 'x';
most_common_vals
-
{0,1,2,3,4,5,6,7,8,10,9,11,12,14,13,15,16,19,17,18}
(1 row)
EXPLAIN ANALYZE SELECT * FROM foo WHERE x = 13;
QUERY PLAN
--
Bitmap Heap Scan on foo (cost=2.72..50.28 rows=205 width=4) (actual
time=0.370..1.766 rows=220 loops=1)
Recheck Cond: (x = 13)
-> Bitmap Index Scan on foo_x_idx (cost=0.00..2.72 rows=205 width=0)
(actual time=0.314..0.314 rows=220 loops=1)
Index Cond: (x = 13)
Total runtime: 2.905 ms
(5 rows)
EXPLAIN ANALYZE SELECT * FROM foo WHERE abs(x) = 13;
QUERY PLAN
--
Bitmap Heap Scan on foo (cost=2.72..50.80 rows=205 width=4) (actual
time=0.358..1.720 rows=220 loops=1)
Recheck Cond: (abs(x) = 13)
-> Bitmap Index Scan on foo_abs_x_idx (cost=0.00..2.72 rows=205 width=0)
(actual time=0.305..0.305 rows=220 loops=1)
Index Cond: (abs(x) = 13)
Total runtime: 2.875 ms
(5 rows)
EXPLAIN ANALYZE SELECT * FROM foo WHERE x = 18;
QUERY PLAN
--
Bitmap Heap Scan on foo (cost=2.60..49.75 rows=172 width=4) (actual
time=0.312..1.442 rows=180 loops=1)
Recheck Cond: (x = 18)
-> Bitmap Index Scan on foo_x_idx (cost=0.00..2.60 rows=172 width=0)
(actual time=0.262..0.262 rows=180 loops=1)
Index Cond: (x = 18)
Total runtime: 2.393 ms
(5 rows)
EXPLAIN ANALYZE SELECT * FROM foo WHERE abs(x) = 18;
QUERY PLAN
-
Bitmap Heap Scan on foo (cost=2.22..43.65 rows=63 width=4) (actual
time=0.313..1.436 rows=180 loops=1)
Recheck Cond: (abs(x) = 18)
-> Bitmap Index Scan on foo_abs_x_idx (cost=0.00..2.22 rows=63 width=0)
(actual time=0.263..0.263 rows=180 loops=1)
Index Cond: (abs(x) = 18)
Total runtime: 2.418 ms
(5 rows)
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[HACKERS] pgbench: undefined reference to strndup()
A recent pgbench commit causes build failures due to an undefined reference to strndup(), at least on FreeBSD and UnixWare: http://pgbuildfarm.org/cgi-bin/show_log.pl?nm=echidna&dt=2005-09-29%2014:30:01 http://pgbuildfarm.org/cgi-bin/show_log.pl?nm=firefly&dt=2005-09-29%2014:27:00 I don't see strndup() in the Open Group Base Specifications, so I expect other platforms' builds might fail as well. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] pg_total_relation_size() could not open relation with OID X
Here's a test case for a pg_total_relation_size() failure:
test=> CREATE TABLE foo (id integer);
CREATE TABLE
test=> SELECT oid, relfilenode FROM pg_class WHERE relname = 'foo';
oid | relfilenode
---+-
26235 | 26235
(1 row)
test=> SELECT pg_total_relation_size('foo');
pg_total_relation_size
0
(1 row)
test=> TRUNCATE foo;
TRUNCATE TABLE
test=> SELECT oid, relfilenode FROM pg_class WHERE relname = 'foo';
oid | relfilenode
---+-
26235 | 26237
(1 row)
test=> SELECT pg_total_relation_size('foo');
ERROR: could not open relation with OID 26237
test=> SELECT pg_total_relation_size(26235);
ERROR: could not open relation with OID 26237
test=> SELECT pg_relation_size('foo');
pg_relation_size
--
0
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 1: 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: [HACKERS] Gerbil build farm failure
Gerbil's looking better lately: http://pgbuildfarm.org/cgi-bin/show_history.pl?nm=gerbil&br=REL8_0_STABLE -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Re-run query on automatic reconnect
On Mon, Sep 26, 2005 at 01:23:03PM -0500, Jim C. Nasby wrote: > Is there any particular reason why psql doesn't re-run a query that > failed due to disconnect from the server after re-connecting? I've > thought maybe it's because it might somehow be dangerous, but I can't > think of any case where that's actually true. What if the query itself resulted in the disconnect by causing the backend to crash? Re-running such a query automatically would be a bad idea. Or did I misunderstand what you're asking? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump fails to set index ownership
On Fri, Sep 23, 2005 at 04:45:02PM -0400, Bruce Momjian wrote: > Tom Lane wrote: > > Michael Fuhr <[EMAIL PROTECTED]> writes: > > > Regarding the removal of ALTER INDEX OWNER commands from pg_dump, > > > indexes are now restored with the wrong ownership if the user doing > > > the restore is different than the user who owned the original index > > > > pg_dump is not the source of the problem. We should instead arrange > > that an index's relowner value is copied directly from the parent table > > during CREATE INDEX. This is probably more important now with roles, > > since GetUserId() might not have a lot to do with the table's owner ID. > > My testing indicated this is fixed. Tom fixed it shortly after making that post: http://archives.postgresql.org/pgsql-committers/2005-08/msg00347.php -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] pg_dump COMMENT ON DATABASE sometimes inappropriate
pg_dump emits COMMENT ON DATABASE (if the database has a comment) even when dumping only an individual schema or table. That seems inappropriate, especially when copying data to a different cluster where the named database might not exist or might have a different comment that shouldn't be overwritten. Does anybody else think pg_dump shouldn't emit COMMENT ON DATABASE in these cases? -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] New dot releases
On Mon, Sep 19, 2005 at 10:53:44AM -0400, Tom Lane wrote: > Devrim GUNDUZ <[EMAIL PROTECTED]> writes: > > So no need to hold the new dot releases? :) > > I still object to releasing them until we find out what's going on > on gerbil. That machine was building 8.0 fine until the patch, and it's > failing consistently since then. To assume this is not our problem > would be the height of hubris. In an earlier message you said that "the owner of the machine has been completely unhelpful about providing any information to track it down." Is he not responding at all, or is he responding but with not enough information? Most of gerbil's failures are: creating information schema ... Bus Error - core dumped Is the message implying that the postgres process that initdb starts is dumping core? Any ideas on how the patch might cause that? The most recent failures are shmat(id=8326) failed: Not enough space and the default settings are selecting default max_connections ... 10 selecting default shared_buffers ... 50 Earlier tests that got as far as "creating information schema" had defaults lower than the maximums: selecting default max_connections ... 40 selecting default shared_buffers ... 700 Could the reduced settings (and thus what they imply about the amount of shared memory) be relevant? Could anything in the patch be affected by that? If you think it might be worthwhile, I could mess around with my box's shared memory settings and test it. Just looking for differences between gerbil and my box -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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: [HACKERS] New dot releases
On Fri, Sep 16, 2005 at 09:28:39AM -0600, Michael Fuhr wrote: > FWIW, I have a Solaris 9/sparc box with gcc 3.4.2 (same setup as > gerbil) and have no problems with REL7_2_STABLE through HEAD. I'll > test REL8_0_STABLE with gerbil's configure options when I get a > chance. I just built REL8_0_STABLE with the following configure options (same as gerbil): ./configure --enable-cassert --enable-debug --enable-nls \ --enable-integer-datetimes --with-perl --with-python \ --with-openssl --with-pgport=5682 gmake check returned the following: == All 96 tests passed. ====== -- Michael Fuhr ---(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: [HACKERS] New dot releases
On Fri, Sep 16, 2005 at 07:57:08AM -0700, Joshua D. Drake wrote: > >What's holding up the back branches at the moment is that the gerbil > >buildfarm member is showing failures in the 8.0 branch that started > >right after I patched the vacuum/ctid-chain stuff. That probably > >indicates a problem, but the owner of the machine has been completely > >unhelpful about providing any information to track it down. > > > I have a Solaris 9 machine on Sparc that I could let you have > access to. Would that help? FWIW, I have a Solaris 9/sparc box with gcc 3.4.2 (same setup as gerbil) and have no problems with REL7_2_STABLE through HEAD. I'll test REL8_0_STABLE with gerbil's configure options when I get a chance. Most of gerbil's errors are: creating information schema ... Bus Error - core dumped but a few are: creating template1 database in /home/pgbuildfarm/build-farm-2.05/REL8_0_STABLE/pgsql.5942/src/test/regress/./tmp_check/data/base/1 ... FATAL: shmat(id=8326) failed: Not enough space -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] problem for o/p
On Sat, Sep 10, 2005 at 12:11:52PM +0100, rajinder ruprai wrote: > i'am getting different o/p for the same program code as well as > the data base is the same .twice the output is being printed on > some machines whereas correct o/p on some.the program code is [...] > raise notice ' 'emp name %' ', e1; Where do the database server's logs go? You might be seeing duplicate output because the server's logs are sent to the same terminal as the client's output. BTW, this doesn't belong on pgsql-hackers. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/PGSQL and drop/create table
On Wed, Sep 07, 2005 at 03:51:05PM +0400, Teodor Sigaev wrote: > ERROR: relation with OID 16628 does not exist > CONTEXT: SQL statement "SELECT count(*) from foo where bar = $1 " http://www.postgresql.org/docs/faqs.FAQ.html#4.19 -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] need info about extensibility in other databases
On Tue, Sep 06, 2005 at 11:52:18PM +0400, Oleg Bartunov wrote: > btw, "GiST programming tutorial" is available in Russian, > http://www.sai.msu.su/~megera/postgres/talks/gist_tutorial.html Cool. Is an English version in the works? -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Version number in psql banner
On Thu, Sep 01, 2005 at 11:34:37PM -0500, Jim C. Nasby wrote: > As a side note, there's a typo in \?: > > \c[onnect] [DBNAME|- [USER]] > > Note the | where there should be a ]. Eh? Looks right to me; the | indicates an alternate, i.e., that you can use either DBNAME or -. I often use - to connect to the same database as a different user. test=> \c - postgres You are now connected as new user "postgres". test=# -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.1 and syntax checking at create time
[Please don't top-post; it destroys the conversational flow. I've moved your comment below what you commented on.] On Wed, Aug 31, 2005 at 03:13:02PM -0500, Tony Caduto wrote: > >In an already-loaded database, I think the following should work: > > > >UPDATE pg_language SET lanvalidator = 'plpgsql_validator'::regproc > >WHERE lanname = 'plpgsql'; > > When I run this I get this error in the database: > PostgreSQL Error Code: (1) > ERROR: function "plpgsql_validator" does not exist Oops...createlang would ordinarily create that function, but since you restored from another database the validator function was never created. Try adding this before the UPDATE (stolen from pg_dump): CREATE FUNCTION pg_catalog.plpgsql_validator(oid) RETURNS void AS '$libdir/plpgsql', 'plpgsql_validator' LANGUAGE c; -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.1 and syntax checking at create time
On Wed, Aug 31, 2005 at 03:37:21PM -0400, Andrew Dunstan wrote: > Tony Caduto wrote: > >How can I get my restored databases to behave the same as a fresh one? > > Run "createlang plpgsql mydb" before running your restore, and possibly > remove the bits that create them from the dump script, or they might > just fail benignly. In an already-loaded database, I think the following should work: UPDATE pg_language SET lanvalidator = 'plpgsql_validator'::regproc WHERE lanname = 'plpgsql'; I'd recommend wrapping the update in a transaction and making sure only one record was updated before committing. Tom (or anybody else), are there any gotchas with updating pg_language like this? It works for me in simple tests. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.1 and syntax checking at create time
On Wed, Aug 31, 2005 at 01:13:00PM -0500, Tony Caduto wrote: > From what I have seen it does not check anything in the body of the > function, I can put gibberish in the body as long as it has a begin and end. > > It does not seem to be doing anything differently than 8.0.x does with > function syntax checking at create time, so why even mention it in the > release notes? I see different behavior in 8.1 than in 8.0. Are you *sure* you're connected to an 8.1 system when you're running your tests? Are you using a database that was restored from an earlier version of PostgreSQL? I wonder if you're not getting the lanvalidator function. What's the result of the following query? SELECT lanname, lanplcallfoid, lanplcallfoid::regprocedure, lanvalidator, lanvalidator::regprocedure FROM pg_language; What happens if you create a fresh database and run "createlang plpgsql" in it, and then run your tests? > the function below also raises no errors at create, but at run time it does. With the example you posted I get the following at create time: ERROR: type "record44" does not exist CONTEXT: compile of PL/pgSQL function "test_func9" near line 2 If I change "record44" to "record" then I get the following (again, at create time): ERROR: syntax error at or near "asfdfdfdfafdsfsdfsdf" at character 1 QUERY: asfdfdfdfafdsfsdfsdf sdf bla bla sdf yada yada s df sd fsd END CONTEXT: SQL statement in PL/PgSQL function "test_func9" near line 10 LINE 1: asfdfdfdfafdsfsdfsdf sdf bla bla sdf yada yada s df sd fsd E... ^ > From what I read in the release notes I was expecting to see this > raised at create time. Create-time checking works here. -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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: [HACKERS] problem with PQExecParams
On Wed, Aug 31, 2005 at 12:08:10PM -, prasanna mavinakuli wrote: > We need to insert binary data to tables and retrieve the > Same-(data type is bytea). > We are using PQExecParams for inserting and retrieving > Data. This doesn't belong on pgsql-hackers, which is for discussing development of PostgreSQL itself. Please post to one of the other lists; if you're not sure which one then pgsql-general is usually a good place. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bitmap scan cause core dump 8.1dev
On Sat, Aug 27, 2005 at 10:28:30AM -0400, Tom Lane wrote:
> Oleg Bartunov writes:
> > I finally narrow down my problem with postmaster crashing 8.1dev
> > (today's CVS):
>
> Can you provide a self-contained test case? The backtrace is
> interesting but it's not enough information to find the bug.
Here's a simple test case based on what I think Oleg is doing. It
needs tsearch2, and it needs to insert enough records that a bitmap
scan would be chosen (I think).
CREATE TABLE foo (
t text,
tidx tsvector
);
CREATE INDEX foo_tidx_idx ON foo USING gist (tidx);
CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo
FOR EACH ROW EXECUTE PROCEDURE tsearch2('tidx', 't');
INSERT INTO foo (t) SELECT 'test' || x FROM generate_series(1, 3000) AS g(x);
ANALYZE foo;
SET enable_bitmapscan TO off;
SELECT t FROM foo, to_tsquery('test1') AS query WHERE tidx @@ query;
t
---
test1
(1 row)
SET enable_bitmapscan TO on;
SELECT t FROM foo, to_tsquery('test1') AS query WHERE tidx @@ query;
server closed the connection unexpectedly
My backtrace shows this:
#0 0x00112d14 in ExecEvalVar (exprstate=0x0, econtext=0x415588,
isNull=0xffbfe34f "\b", isDone=0x0) at execQual.c:491
491 Assert(attnum <= tuple_type->natts);
(gdb) bt
#0 0x00112d14 in ExecEvalVar (exprstate=0x0, econtext=0x415588,
isNull=0xffbfe34f "\b", isDone=0x0) at execQual.c:491
#1 0x00116128 in ExecEvalExprSwitchContext (expression=0x4154f0,
econtext=0x1, isNull=0xffbfe34f "\b", isDone=0x0)
at execQual.c:2808
#2 0x0011f75c in ExecIndexEvalRuntimeKeys (econtext=0x4154f0,
run_keys=0x415588, scan_keys=0xffbfe34f, n_keys=0)
at nodeIndexscan.c:270
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] bitmap scan cause core dump 8.1dev
On Sat, Aug 27, 2005 at 05:21:46PM +0400, Oleg Bartunov wrote:
> This query works as expected:
> # select name_qualified from place
> where fts_index @@ to_tsquery('moscow');
>
> This query (essentially the same as above) crashes:
> # select name_qualified from place, to_tsquery('moscow') as query
> where fts_index @@ query;
>
> When I disable bitmap scanning (set enable_bitmapscan=off)
> second query works fine.
Disabling enable_bitmapscan causes my example to succeed, but when
I do get the error I don't get a crash, and the same query without
EXPLAIN succeeds:
CREATE TABLE foo (x integer);
CREATE INDEX foo_x_idx ON foo (x);
CREATE VIEW fooview AS SELECT count(*) FROM foo WHERE x < 10;
SET enable_bitmapscan TO on;
SELECT * FROM fooview;
count
---
0
(1 row)
EXPLAIN SELECT * FROM fooview;
ERROR: bogus varno: 5
SET enable_bitmapscan TO off;
EXPLAIN SELECT * FROM fooview;
QUERY PLAN
--
Aggregate (cost=32.41..32.41 rows=1 width=0)
-> Index Scan using foo_x_idx on foo (cost=0.00..30.63 rows=713 width=0)
Index Cond: (x < 10)
(3 rows)
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[HACKERS] EXPLAIN with view: bogus varno: 5
Running EXPLAIN on a view that has an aggregate and uses an index results in the error "bogus varno: 5". At least I think the aggregate and index are necessary -- removing either from the following example allows EXPLAIN to succeed: test=> CREATE TABLE foo (x integer); CREATE TABLE test=> CREATE INDEX foo_x_idx ON foo (x); CREATE INDEX test=> CREATE VIEW fooview1 AS SELECT count(*) FROM foo WHERE x < 10; CREATE VIEW test=> CREATE VIEW fooview2 AS SELECT * FROM foo WHERE x < 10; CREATE VIEW test=> CREATE VIEW fooview3 AS SELECT count(*) FROM foo; CREATE VIEW test=> CREATE VIEW fooview4 AS SELECT * FROM foo; CREATE VIEW test=> \set VERBOSITY verbose test=> EXPLAIN SELECT * FROM fooview1; ERROR: XX000: bogus varno: 5 LOCATION: get_rte_for_var, ruleutils.c:2478 test=> EXPLAIN SELECT * FROM fooview2; QUERY PLAN -- Bitmap Heap Scan on foo (cost=3.50..22.41 rows=713 width=4) Recheck Cond: (x < 10) -> Bitmap Index Scan on foo_x_idx (cost=0.00..3.50 rows=713 width=0) Index Cond: (x < 10) (4 rows) test=> EXPLAIN SELECT * FROM fooview3; QUERY PLAN - Aggregate (cost=36.75..36.75 rows=1 width=0) -> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=0) (2 rows) test=> EXPLAIN SELECT * FROM fooview4; QUERY PLAN --- Seq Scan on foo (cost=0.00..31.40 rows=2140 width=4) (1 row) test=> DROP INDEX foo_x_idx; DROP INDEX test=> EXPLAIN SELECT * FROM fooview1; QUERY PLAN Aggregate (cost=38.53..38.53 rows=1 width=0) -> Seq Scan on foo (cost=0.00..36.75 rows=713 width=0) Filter: (x < 10) (3 rows) -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] pg_dump fails to set index ownership
Regarding the removal of ALTER INDEX OWNER commands from pg_dump, indexes are now restored with the wrong ownership if the user doing the restore is different than the user who owned the original index (if this sounds familiar, I reported the same problem for 8.0.0rc4 in January). ALTER INDEX OWNER no longer works, and ALTER TABLE OWNER won't change the index ownership if the table ownership doesn't actually change (i.e., nothing happens if the new owner and the old owner are the same). Should CREATE INDEX automatically set index ownership to be the same as the table ownership? Or did I miss past discussion about that? Seems like this ought to be fixed before beta1 is announced so it doesn't bite people who are trying 8.1 for the first time. postgres=# CREATE ROLE test LOGIN PASSWORD 'test'; CREATE ROLE postgres=# CREATE DATABASE test1; CREATE DATABASE postgres=# CREATE DATABASE test2; CREATE DATABASE postgres=# \c test1 test Password for user test: You are now connected to database "test1" as user "test". test1=> CREATE TABLE foo (id serial PRIMARY KEY, val text); NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE test1=> CREATE INDEX foo_val_idx ON foo (val); CREATE INDEX test1=> \q % pg_dump -U postgres test1 | psql -U postgres test2 SET SET SET COMMENT SET SET SET CREATE TABLE ALTER TABLE setval 1 (1 row) ALTER TABLE CREATE INDEX REVOKE REVOKE GRANT GRANT % psql -q -U test test2 Password for user test: test2=> \d List of relations Schema |Name| Type | Owner ++--+--- public | foo| table| test public | foo_id_seq | sequence | test (2 rows) test2=> \di List of relations Schema |Name | Type | Owner | Table +-+---+--+--- public | foo_pkey| index | postgres | foo public | foo_val_idx | index | postgres | foo (2 rows) test2=> DROP INDEX foo_val_idx; ERROR: must be owner of relation foo_val_idx test2=> \c test2 postgres Password for user postgres: You are now connected to database "test2" as user "postgres". test2=# ALTER INDEX foo_val_idx OWNER TO test; WARNING: cannot change owner of index "foo_val_idx" HINT: Change the ownership of the index's table, instead. ALTER INDEX test2=# ALTER TABLE foo OWNER TO test; ALTER TABLE test2=# \di List of relations Schema |Name | Type | Owner | Table +-+---+--+--- public | foo_pkey| index | postgres | foo public | foo_val_idx | index | postgres | foo (2 rows) test2=# ALTER TABLE foo OWNER TO postgres; ALTER TABLE test2=# ALTER TABLE foo OWNER TO test; ALTER TABLE test2=# \di List of relations Schema |Name | Type | Owner | Table +-----+---+---+--- public | foo_pkey| index | test | foo public | foo_val_idx | index | test | foo (2 rows) -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Sleep functions
On Wed, Aug 24, 2005 at 12:49:57PM -0400, Bruce Momjian wrote: > > Added to TODO: > > o Add sleep() to PL/PgSQL Just to PL/pgSQL? If we're going to add it (which doesn't seem to be decided yet), why not as an ordinary function that could be called from SQL as well? -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] SHOW, RESET require quotes, SET doesn't
SHOW and RESET require quoting in cases where SET doesn't: test=> SHOW plperl.use_strict; ERROR: syntax error at or near "." at character 12 LINE 1: SHOW plperl.use_strict; ^ test=> SHOW "plperl.use_strict"; plperl.use_strict --- on (1 row) test=> SET plperl.use_strict TO off; SET test=> RESET plperl.use_strict; ERROR: syntax error at or near "." at character 13 LINE 1: RESET plperl.use_strict; ^ test=> RESET "plperl.use_strict"; RESET I see in gram.y that SHOW and RESET take a ColId but SET takes a var_name, which is ColId or var_name.ColId. Is there a reason for the inconsistency or is it just an oversight? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Sleep functions
On Sun, Aug 21, 2005 at 09:13:20PM -0400, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > What do people think of exposing pg_usleep() to the user? > > I'm not real enthused about it. Generally speaking, a sleep() on the > database side means you are idling while holding locks, and that does > not seem like something we want to encourage people to do. I don't see how providing a server-side sleep() encourages idling while holding locks any more than people can already do. In that respect, is it any different than a client-side sleep() or going to lunch with an open transaction in psql? > As other responders noted, it's trivial to program this in any of the > untrusted PL languages, So what you're really proposing is that we give > sleep() to non-superusers, and that seems like a bit of a hard sell. > Let's see a use-case or three. Sure it's trivial in various languages, even in trusted PL/Tcl: CREATE FUNCTION sleep(integer) RETURNS void AS $$ after [expr $1 * 1000] $$ LANGUAGE pltcl STRICT; So aside from the ways to idle I mentioned above, non-superusers do have a way to perform a server-side sleep(), at least on systems that use PL/Tcl. Or is allowing "after" a bug in trusted PL/Tcl? In any case, I wonder how many people, not having a sleep() function, effect a delay with a busy loop; an example of such has been posted in response to the thread in pgsql-admin, and didn't the regression tests do so until recently? That seems less desirable than a real sleep(). A few use cases are learning, testing, and debugging: you might want to slow down operations so you can more easily watch what's happening, observe how the slowness affects other operations, or look for application problems related to timing. With a server-side sleep() those delays can be done with simple queries fed into psql or another interface that doesn't provide a way to sleep, and a client-side sleep() wouldn't help if you want to slow down operations inside a PL/pgSQL function. To others who've written their own sleep() function: what are you using it for? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Sleep functions
What do people think of exposing pg_usleep() to the user? It's sometimes useful to have a server-side sleep function, and people do ask about it occasionally (e.g., Don Drake today in pgsql-admin). It's easy enough to do in PL/Perl, PL/Tcl, etc., but since the backend already has pg_usleep(), is there any reason not to expose it? I'd propose both sleep() and usleep() functions. -- Michael Fuhr ---(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: [HACKERS] transactions not working properly ?
[This question would probably be more appropriate in pgsql-general than in pgsql-hackers.] On Wed, Aug 17, 2005 at 05:53:14AM -0700, Ali Baba wrote: > can any one describe how the transaction are being > handled in postgres. I think you're talking about how PL/pgSQL exception handlers work with transactions. See the documentation: http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > function given below should actually insert the desire > values in test table but it do not save them. A complete test case would make it easier help. All we see in the example is the start of a transaction and the creation of a function -- we don't see how you're actually using it nor what output (e.g., error messages) it produces. > begin > x := 1; > insert into test values (210,20); > x := x/0; > > RETURN 0; > > exception > when others then > raise info 'error generated '; > commit; > RETURN 0; > end; The "Trapping Errors" documentation states: When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back. Since the divide-by-zero error is in the same block as the INSERT, the INSERT is rolled back. Also, you can't issue COMMIT inside a function -- see the "Structure of PL/pgSQL" documentation: http://www.postgresql.org/docs/8.0/static/plpgsql-structure.html Functions and trigger procedures are always executed within a transaction established by an outer query they cannot start or commit that transaction, since there would be no context for them to execute in. However, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] ARRAY(subquery) volatility
Why does the first query below return the same value for each row
while the second query returns random values? Planner optimization?
test=> SELECT ARRAY(SELECT random()) FROM generate_series(1, 5);
?column?
-
{0.269273371561092}
{0.269273371561092}
{0.269273371561092}
{0.269273371561092}
{0.269273371561092}
(5 rows)
test=> SELECT ARRAY(SELECT random() + x * 0) FROM generate_series(1, 5) AS g(x);
?column?
-
{0.826863945846848}
{0.42534113182935}
{0.36419924318986}
{0.258920902972538}
{0.843205466327819}
(5 rows)
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Testing of MVCC
On Mon, Aug 15, 2005 at 10:37:06PM +, Matt Miller wrote: > > Perhaps we should look at Expect or something similar. > > Where can I get more info on Expect? http://www.google.com/ :-) Or here: http://expect.nist.gov/ -- Michael Fuhr ---(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: [HACKERS] psql SET/RESET/SHOW tab completion
On Sat, Aug 13, 2005 at 11:04:18AM -0600, Michael Fuhr wrote: > I had removed --enable-cassert from my configure script while doing > some performance tests and never put it back (I had noticed that > VACUUM was quite slow on that box and found that it was due to the > assertion checks). BTW, here are the results of those tests: a VACUUM ANALYZE of template1 without --enable-cassert takes about 830ms on my box. With --enable-cassert it takes about 24200ms, regardless of the debug_assertions setting. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] psql SET/RESET/SHOW tab completion
On Sat, Aug 13, 2005 at 12:41:51PM -0400, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > Is 162 a typo or are you working on something that hasn't been > > committed yet? I see 161 in the latest code. > > Uh, I get 162 ... and no I don't have any uncommitted changes ATM. I found the difference: one of my boxes is missing debug_assertions. I had removed --enable-cassert from my configure script while doing some performance tests and never put it back (I had noticed that VACUUM was quite slow on that box and found that it was due to the assertion checks). -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] psql SET/RESET/SHOW tab completion
On Sat, Aug 13, 2005 at 11:39:59AM -0400, Tom Lane wrote: > I count 98 GUC variables currently listed in tab-complete.c, > and 162 rows in pg_settings. Is 162 a typo or are you working on something that hasn't been committed yet? I see 161 in the latest code. template1=# SELECT count(*) FROM pg_settings; count --- 161 (1 row) -- Michael Fuhr ---(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: [HACKERS] psql SET/RESET/SHOW tab completion
On Sat, Aug 13, 2005 at 09:25:34AM -0600, Michael Fuhr wrote: > > Here's the list I came up with -- variables that SHOW shows that > aren't in psql's completion list. Here's the list broken down by context: PGC_USERSET autocommit check_function_bodies debug_assertions escape_string_warning exit_on_error role session_authorization transaction_isolation transaction_read_only vacuum_cost_delay vacuum_cost_limit vacuum_cost_page_dirty vacuum_cost_page_hit vacuum_cost_page_miss PGC_SUSET debug_deadlocks log_btree_build_stats trace_locks trace_lock_oidmin trace_lock_table trace_lwlocks trace_userlocks zero_damaged_pages PGC_INTERNAL block_size integer_datetimes is_superuser lc_collate lc_ctype max_function_args max_identifier_length max_index_keys server_version standard_compliant_strings PGC_POSTMASTER bonjour_name config_file custom_variable_classes data_directory external_pid_file hba_file ident_file krb_caseins_users krb_server_hostname krb_server_keyfile krb_srvname listen_addresses max_prepared_transactions preload_libraries redirect_stderr silent_mode PGC_SIGHUP archive_command authentication_timeout autovacuum autovacuum_analyze_scale_factor autovacuum_analyze_threshold autovacuum_naptime autovacuum_vacuum_cost_delay autovacuum_vacuum_cost_limit autovacuum_vacuum_scale_factor autovacuum_vacuum_threshold bgwriter_all_maxpages bgwriter_all_percent bgwriter_delay bgwriter_lru_maxpages bgwriter_lru_percent checkpoint_segments checkpoint_timeout checkpoint_warning db_user_namespace full_page_writes log_directory log_filename log_hostname log_line_prefix log_rotation_age log_rotation_size log_truncate_on_rotation pre_auth_delay PGC_BACKEND log_connections log_disconnections -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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: [HACKERS] psql SET/RESET/SHOW tab completion
On Sat, Aug 13, 2005 at 10:33:55AM -0400, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > I count about 65 variables that SHOW shows that are missing from > > pgsql_variables in tab-complete.c. Does the list intentionally > > omit certain variables? > > It's intentional that the tab completion not list every single variable; > I think if it did, it would be less useful not more so. However the > decisions about which to omit have been less consistent than they > perhaps should be, and I'm sure there were some omitted purely through > oversight. It would be good to go through the whole list and see > exactly what's missing or superfluous. Here's the list I came up with -- variables that SHOW shows that aren't in psql's completion list. archive_command authentication_timeout autovacuum autovacuum_analyze_scale_factor autovacuum_analyze_threshold autovacuum_naptime autovacuum_vacuum_cost_delay autovacuum_vacuum_cost_limit autovacuum_vacuum_scale_factor autovacuum_vacuum_threshold bgwriter_all_maxpages bgwriter_all_percent bgwriter_delay bgwriter_lru_maxpages bgwriter_lru_percent block_size bonjour_name check_function_bodies checkpoint_segments checkpoint_timeout checkpoint_warning config_file custom_variable_classes data_directory db_user_namespace escape_string_warning external_pid_file full_page_writes hba_file ident_file integer_datetimes krb_caseins_users krb_server_hostname krb_server_keyfile krb_srvname lc_collate lc_ctype listen_addresses log_connections log_directory log_disconnections log_filename log_hostname log_line_prefix log_rotation_age log_rotation_size log_truncate_on_rotation max_function_args max_identifier_length max_index_keys max_prepared_transactions pre_auth_delay preload_libraries redirect_stderr server_version silent_mode standard_compliant_strings transaction_isolation transaction_read_only vacuum_cost_delay vacuum_cost_limit vacuum_cost_page_dirty vacuum_cost_page_hit vacuum_cost_page_miss zero_damaged_pages -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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
[HACKERS] psql SET/RESET/SHOW tab completion
Would anybody object to a patch to update psql's tab completion for SET/RESET/SHOW to include everything that SHOW shows for a superuser? I count about 65 variables that SHOW shows that are missing from pgsql_variables in tab-complete.c. Does the list intentionally omit certain variables? The comment mentions that the list "should match USERSET and possibly SUSET," but I'm thinking it would be useful to include everything SHOWable. In any case, a few USERSET variables like check_function_bodies and escape_string_warning are missing, so I'd like to add at least those. -- Michael Fuhr ---(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: [HACKERS] Use of inv_getsize in functions
On Tue, Aug 09, 2005 at 10:54:49PM +0200, Soeren Laursen wrote:
> I have used other function calls like
> inv_open with no problem, but when I load this modules I get:
>
> undefined symbol: inv_getsize
Notice the word "static" in the definition of inv_getsize() in
src/backend/storage/large_object/inv_api.c:
static uint32
inv_getsize(LargeObjectDesc *obj_desc)
{
...
}
I don't know if there's a good reason for inv_getsize() being static.
Maybe your code could use inv_seek() instead.
> No errors when compiling.
If you compile with warning flags, then you should at least have
gotten a warning like "implicit declaration of function `inv_getsize'".
That's a hint that something's wrong.
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[HACKERS] ECPG ignores SAVEPOINT if first statement of a transaction
ECPG ignores SAVEPOINT if it's the first statement of a transaction:
% cat foo.pgc
int
main(void)
{
EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL CONNECT TO test;
EXEC SQL SAVEPOINT foo;
EXEC SQL DROP TABLE nosuch_1;
EXEC SQL ROLLBACK TO foo;
EXEC SQL DROP TABLE nosuch_2;
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
return 0;
}
% ./foo
sql error 'table "nosuch_1" does not exist' in line 10.
sql error 'current transaction is aborted, commands ignored until end of transa
The SAVEPOINT code is generated but apparently ECPGtrans() doesn't
execute it. A sniff of the connection doesn't show it, and the
sniff shows the ROLLBACK TO failing with "no such savepoint."
If I execute a command before the SAVEPOINT then I get the following,
which is what I was expecting:
% ./foo
sql error 'table "nosuch_1" does not exist' in line 11.
sql error 'table "nosuch_2" does not exist' in line 13.
A sniff of this connection shows both the SAVEPOINT and ROLLBACK TO
being executed and succeeding.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 6: explain analyze is your friend
[HACKERS] ECPG and escape strings
ECPG seems to be a little overzealous with the new escape string syntax:
% cat foo.pgc
int
main(void)
{
putchar('\n');
return 0;
}
% ecpg foo.pgc
% gcc -I`pg_config --includedir` -c foo.c
foo.pgc: In function `main':
foo.pgc:4: `E' undeclared (first use in this function)
foo.pgc:4: (Each undeclared identifier is reported only once
foo.pgc:4: for each function it appears in.)
foo.pgc:4: syntax error before character constant
% cat foo.c
/* Processed by ecpg (4.1.1) */
/* These include files are added by the preprocessor */
#include
#include
#include
#include
/* End of automatic include section */
#line 1 "foo.pgc"
int
main(void)
{
putchar(E'\n');
return 0;
}
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 6: explain analyze is your friend
[HACKERS] Information Schema DBMS VERSION wrong
The following query doesn't return the version of PostgreSQL currently running, but rather the version of initdb that initialized the cluster: SELECT character_value FROM information_schema.sql_implementation_info WHERE implementation_info_name = 'DBMS VERSION'; Is that the intended or desired behavior? sql_implementation_info is a table rather than a view, so its contents are hardcoded by initdb. Would it make sense to turn it into a view? This is admittedly a minor issue that might not merit the effort, but it still seems wrong. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] RESULT_OID Bug
On Wed, Jul 27, 2005 at 01:20:29PM -0700, Kevin McArthur wrote: > Changing just the one appears to resolve the oid bug. Should probably talk > to neilc to see why he changed it. Initializing isnull to false in exec_stmt_getdiag() appears to fix the bug on my Solaris 9 box as well. I'd guess the variations in behavior were due to different platforms having different garbage on the stack. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] RESULT_OID Bug
On Wed, Jul 27, 2005 at 12:19:51AM -0700, Kevin McArthur wrote: > This error has come up in the last week or so, and my suspicion remains > that its caused by something to do with roles but that could be way wrong. > > The FreeBSD machines were confirmed to work as of about a week ago ( i > reinstalled for a timezone patch and I'm pretty sure it was working then ) Have you tried using CVS to check out and test older code? I'll do that myself when I get a chance. If the developers can't reproduce the problem, then at least maybe we can narrow down which commit is responsible so they'll have something to look at. > I can note that the \set for lastoid is properly updated when I insert into > a table. Thus the problem has to be somewhere between plpgsql and that data > via the get diagnostics interface (under the assumption that the lastoid > structure is consistent for all inserts and psql having lastoid working at > all eliminates that part of the equation). So far the problem does seem to be specific to whatever PL/pgSQL's is doing, and it affects ROW_COUNT as well as RESULT_OID. I haven't been able to reproduce the problem with PL/Tcl or with C and SPI. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] RESULT_OID Bug
On Wed, Jul 27, 2005 at 12:19:51AM -0700, Kevin McArthur wrote: > The target system for my reproduction is on FreeBSD. What version of FreeBSD? What compiler and version? So far I haven't been able to reproduce the problem on FreeBSD 4.11-STABLE/ i386/gcc 2.95.4. > Though I sitll cannot get the initdb started one to work the first time > around. Odd -- that one works (or rather, fails) every time for me on Solaris 9/ sparc/gcc 3.4.2. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: 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: [HACKERS] RESULT_OID Bug
On Wed, Jul 27, 2005 at 12:56:15AM -0400, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > Could this be platform-specific? > > Seems that way. I tried it on HPUX 10.20/HPPA/gcc 2.95.3. No luck on FreeBSD 4.11-STABLE/i386/gcc 2.95.4. The box that does have a problem is Solaris 9/sparc/gcc 3.4.2. Can anybody else reproduce the problem? > My guess is that the behavior is related to plpgsql's caching > of plans for functions, and as such should be driven by the > backend's history not the whole database's history. But it's > just a guess. Another test case that's been consistent for me: Session 1: connect Session 1: create table and function Session 1: call function; returns oid Session 2: connect Session 2: call function; returns NULL Session 1: exit Session 3: connect Session 3: call function; returns NULL Session 2: exit Session 3: exit Session 4: connect Session 4: call function; returns oid Session 5: connect Session 5: call function; returns NULL Any suggestions? Would it be useful to attach gdb to one of the backends? If so, what should I be looking for? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] RESULT_OID Bug
On Wed, Jul 27, 2005 at 12:08:18AM -0400, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > Is anybody with a deeper understanding of the code looking at this? > > I tried to reproduce the problem ... no joy ... Hmmm...not even with the example that starts from initdb? I'm up to date with the latest commits and I can consistently reproduce it. I was just about to post that TRUNCATE apparently "fixes" the problem: CREATE TABLE foo (t timestamptz DEFAULT now()) WITH OIDS; CREATE OR REPLACE FUNCTION oidtest() RETURNS integer AS $$ DECLARE foo_count integer; foo_oidinteger; BEGIN EXECUTE 'INSERT INTO foo DEFAULT VALUES'; GET DIAGNOSTICS foo_count = ROW_COUNT; GET DIAGNOSTICS foo_oid = RESULT_OID; RAISE INFO 'ROW_COUNT = %, RESULT_OID = %', foo_count, foo_oid; RETURN foo_oid; END; $$ LANGUAGE plpgsql VOLATILE; SELECT oidtest(); INFO: ROW_COUNT = 1, RESULT_OID = 17008 oidtest - 17008 (1 row) CREATE OR REPLACE FUNCTION oidtest() RETURNS integer AS $$ DECLARE foo_count integer; foo_oidinteger; BEGIN EXECUTE 'INSERT INTO foo DEFAULT VALUES'; GET DIAGNOSTICS foo_count = ROW_COUNT; GET DIAGNOSTICS foo_oid = RESULT_OID; RAISE INFO 'ROW_COUNT = %, RESULT_OID = %', foo_count, foo_oid; RETURN foo_oid; END; $$ LANGUAGE plpgsql VOLATILE; SELECT oidtest(); INFO: ROW_COUNT = , RESULT_OID = oidtest - (1 row) TRUNCATE foo; SELECT oidtest(); INFO: ROW_COUNT = 1, RESULT_OID = 17011 oidtest - 17011 (1 row) Could this be platform-specific? Right now I can only test with Solaris 9/sparc, but if necessary I could build HEAD on FreeBSD 4.11-STABLE/i386. Kevin, what platform are you using? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] RESULT_OID Bug
On Tue, Jul 26, 2005 at 04:31:21PM -0700, Kevin McArthur wrote: > I cannot repoduce your experience with this bug. No matter what I do, > reconnect session or otherwise, it never returns a proper oid on the > newer cvs vers (I suspect it may be related to the roles update) I'm seeing varying results, depending on disconnects, database restarts, and possibly whether another session has executed the same function in another database. I suspect our systems aren't in exactly the same state so we're seeing slightly different results. Here's something that starts with initdb, so hopefully it'll be 100% reproducible: initdb data2 postmaster -D data2 -p createlang -p plpgsql postgres psql -p postgres CREATE TABLE foo (a time DEFAULT now()) WITH OIDS; CREATE OR REPLACE FUNCTION oidtest() RETURNS integer AS $$ DECLARE insert_oid_var INTEGER; BEGIN EXECUTE 'INSERT INTO foo DEFAULT VALUES'; GET DIAGNOSTICS insert_oid_var = RESULT_OID; RETURN insert_oid_var; END; $$ LANGUAGE plpgsql VOLATILE; SELECT oidtest(); oidtest - 16391 (1 row) CREATE OR REPLACE FUNCTION oidtest() RETURNS integer AS $$ DECLARE insert_oid_var INTEGER; BEGIN EXECUTE 'INSERT INTO foo DEFAULT VALUES'; GET DIAGNOSTICS insert_oid_var = RESULT_OID; RETURN insert_oid_var; END; $$ LANGUAGE plpgsql VOLATILE; SELECT oidtest(); oidtest - (1 row) When did you first notice this? When was the last time you know for sure that it was behaving correctly? So far I've only seen the problem with PL/pgSQL's GET DIAGNOSTICS -- I haven't been able to reproduce it with PL/Tcl's spi_lastoid. Is anybody with a deeper understanding of the code looking at this? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] RESULT_OID Bug
On Tue, Jul 26, 2005 at 04:31:21PM -0700, Kevin McArthur wrote: > I cannot repoduce your experience with this bug. No matter what I do, > reconnect session or otherwise, it never returns a proper oid on the newer > cvs vers (I suspect it may be related to the roles update) Hmmm...my system is only a couple of hours old; the only code it's missing is the recent "Minor correction: cause ALTER ROLE role ROLE role" commit: http://archives.postgresql.org/pgsql-committers/2005-07/msg00545.php Here's a test case on my system, run in a fresh session in a newly-created database named test2: CREATE TABLE foo (a time DEFAULT now()) WITH OIDS; CREATE FUNCTION oidtest() RETURNS integer AS $$ DECLARE insert_oid_var INTEGER; BEGIN INSERT INTO foo DEFAULT VALUES; GET DIAGNOSTICS insert_oid_var = RESULT_OID; RETURN insert_oid_var; END; $$ LANGUAGE plpgsql VOLATILE; SELECT oidtest(); oidtest - 16565 (1 row) SELECT oidtest(); oidtest - 16566 (1 row) CREATE OR REPLACE FUNCTION oidtest() RETURNS integer AS $$ DECLARE insert_oid_var INTEGER; BEGIN INSERT INTO foo DEFAULT VALUES; GET DIAGNOSTICS insert_oid_var = RESULT_OID; RETURN insert_oid_var; END; $$ LANGUAGE plpgsql VOLATILE; SELECT oidtest(); oidtest - (1 row) \c test2 You are now connected to database "test2". SELECT oidtest(); oidtest - 16568 (1 row) -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] RESULT_OID Bug
On Tue, Jul 26, 2005 at 03:36:26PM -0700, Kevin McArthur wrote: > Recent cvs versions are failing the following script; > > create table oidtest(a time default now()) with oids; > > CREATE OR REPLACE FUNCTION oidtest() RETURNS integer AS $oidtest$ > DECLARE > insert_oid_var INTEGER; > BEGIN > INSERT INTO oidtest DEFAULT VALUES; > GET DIAGNOSTICS insert_oid_var = RESULT_OID; > RETURN insert_oid_var; > END; > $oidtest$ Language plpgsql; > > select oidtest(); > > if its working you will see an oid, if its failing you will see 1 row with > blank data. The function appears to work in a session until you replace it (or drop and recreate it), after which you get NULL. If you exit the session and reconnect then it works again. I checked 8.0.3 and it doesn't have this problem. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: 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: [HACKERS] More buildfarm stuff
On Mon, Jul 25, 2005 at 04:40:19PM -0500, Jim C. Nasby wrote: > FWIW, AFAICT I did build the port with default options. Though, nm shows > no symbols for my libpython(s)... > > [EMAIL PROTECTED]:38]~:47>nm `locate libpython|grep .so` > > /usr/local/lib/compat/pkg/libpython2.3.so.1: > /usr/libexec/elf/nm: /usr/local/lib/compat/pkg/libpython2.3.so.1: no symbols Try "nm -D" (or --dynamic) or "objdump -T" (or --dynamic-syms) when looking at a shared object. Here's what I get: % nm -D /usr/local/lib/libpython2.4.so.1 | grep pthread U pthread_attr_destroy U pthread_attr_init U pthread_attr_setstacksize U pthread_create U pthread_detach U pthread_self % nm -D /usr/local/lib/perl5/5.8.7/mach/CORE/libperl.so | grep pthread [no output] -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] More buildfarm stuff
On Sun, Jul 24, 2005 at 06:40:35PM -0400, Tom Lane wrote: > I think someone mentioned this already, but it'd be a good idea to > compare the python situation to plperl. On my Linux box, libperl.so > shows several references to pthread_xxx symbols ... not the same ones > libpython.so depends on, but pthread symbols none the less. I'd kind > of expect them both to fail if the problem is that the stock libc > doesn't include pthreads. I have a FreeBSD 4.11-STABLE box with Perl 5.8.7 built from the ports collection, and "nm libperl.so" shows no pthread functions. The port's Makefile has a WITH_THREADS option that I don't think is enabled by default. The Python port's Makefile, however, has a WITHOUT_THREADS option, so I think it *does* build a threaded Python by default. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] suggested TODO: md5(bytea)
On Sun, Jul 24, 2005 at 07:32:22PM -0400, Andrew Dunstan wrote: > Could we please add this TODO that I just found missing, much to my > annoyance? > > . md5 function for bytea Eh? http://archives.postgresql.org/pgsql-committers/2005-05/msg00239.php test=> \df md5 List of functions Schema | Name | Result data type | Argument data types +--+--+- pg_catalog | md5 | text | bytea pg_catalog | md5 | text | text (2 rows) -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Patch to fix plpython on OS X
On Sun, Jul 24, 2005 at 08:40:42AM -0500, Andrew Dunstan wrote: > This is completely unnecessary - pg_regress has an alternative result > mechanism that doesn't rely on a resultmap file. Just name your alternative > result file foo_n.out instead of foo.out, for some n in [0-9]. In this case, > call it, say, plpython_error_1.out. Job done, and no OS dependence. Thanks -- I overlooked that in src/test/regress/README. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Patch to fix plpython on OS X
On Sat, Jul 23, 2005 at 10:38:59PM -0400, Tom Lane wrote:
> Well, if it is just a Python version issue then all we need do is add
> a variant expected-output file to match. I was just expressing a
> desire to know that for sure before we wallpaper over the symptom...
I just built Python 2.3 and it does indeed format the error differently
than later versions (the format appears to have changed in 2.3.1):
% python2.3
Python 2.3 (#1, Jul 24 2005, 06:18:30)
[GCC 3.4.2] on sunos5
Type "help", "copyright", "credits" or "license" for more information.
>>> str(u'\x80')
Traceback (most recent call last):
File "", line 1, in ?
UnicodeEncodeError: 'ascii' codec can't encode character '\u80' in position 0:
ordinal not in range(128)
% python2.4
Python 2.4.1 (#1, Apr 6 2005, 09:52:02)
[GCC 3.4.2] on sunos5
Type "help", "copyright", "credits" or "license" for more information.
>>> str(u'\x80')
Traceback (most recent call last):
File "", line 1, in ?
UnicodeEncodeError: 'ascii' codec can't encode character u'\x80' in position 0:
ordinal not in range(128)
One could check the version of Python that PL/Python is using with
the following function (assuming that Python isn't so broken that
it would use the core of one version but find modules from another):
CREATE FUNCTION pyversion() RETURNS text AS $$
import sys
return sys.version
$$ LANGUAGE plpythonu;
I've attached two new files that should go in the plpython directory:
resultmap
expected/plpython_error_py23.out
A problem with this patch is that it assumes a version of Python
based on the OS, which might clean up the current buildfarm but
that isn't really correct. Is there a better way to handle this?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
plpython_error/.*-darwin=plpython_error_py23
-- test error handling, i forgot to restore Warn_restart in
-- the trigger handler once. the errors and subsequent core dump were
-- interesting.
SELECT invalid_type_uncaught('rick');
WARNING: plpython: in function invalid_type_uncaught:
DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare
ERROR: type "test" does not exist
SELECT invalid_type_caught('rick');
WARNING: plpython: in function invalid_type_caught:
DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare
ERROR: type "test" does not exist
SELECT invalid_type_reraised('rick');
WARNING: plpython: in function invalid_type_reraised:
DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare
ERROR: type "test" does not exist
SELECT valid_type('rick');
valid_type
(1 row)
--
-- Test Unicode error handling.
--
SELECT unicode_return_error();
ERROR: plpython: function "unicode_return_error" could not create return value
DETAIL: exceptions.UnicodeEncodeError: 'ascii' codec can't encode character
'\u80' in position 0: ordinal not in range(128)
INSERT INTO unicode_test (testvalue) VALUES ('test');
ERROR: plpython: function "unicode_trigger_error" could not modify tuple
DETAIL: exceptions.UnicodeEncodeError: 'ascii' codec can't encode character
'\u80' in position 0: ordinal not in range(128)
SELECT unicode_plan_error1();
WARNING: plpython: in function unicode_plan_error1:
DETAIL: plpy.Error: Unknown error in PLy_spi_execute_plan
ERROR: plpython: function "unicode_plan_error1" could not execute plan
DETAIL: exceptions.UnicodeEncodeError: 'ascii' codec can't encode character
'\u80' in position 0: ordinal not in range(128)
SELECT unicode_plan_error2();
ERROR: plpython: function "unicode_plan_error2" could not execute plan
DETAIL: exceptions.UnicodeEncodeError: 'ascii' codec can't encode character
'\u80' in position 0: ordinal not in range(128)
---(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: [HACKERS] [PATCHES] Patch to fix plpython on OS X
On Sat, Jul 23, 2005 at 07:58:21PM -0400, Andrew Dunstan wrote: > Tom Lane wrote: > >"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > >>I don't think it's a version issue; cuckoo is at 2.4, platypus used to > >>be at 2.3 but I upgraded it to 2.4 to see if that was the issue, but > >>platypus kept working. > > > >Hmm ... if it's *not* a version thing then I really do want to know > >what's causing it. Anyone have an idea why this machine is saying > >'\u80' where everyone else's python says u'\x80' ? > > Another OSX box on buildfarm, wallaroo, is exhibiting the same > behaviour, albeit currently masked by interval regression failures. I suspect this is indeed a Python version issue: http://archives.postgresql.org/pgsql-hackers/2005-07/msg00669.php http://archives.postgresql.org/pgsql-hackers/2005-07/msg00684.php It looks like the Macs have some kind of Python framework that PL/Python is linking against even if a newer version of Python has been installed. Unfortunately I don't have a Mac I could use to do any deeper investigating. The regression tests that are failing are from the patch I submitted about a month ago to fix a core dump in PL/Python: http://archives.postgresql.org/pgsql-patches/2005-06/msg00519.php The tests exercise the error checking that the patch added, doing things that previously caused a segmentation fault but that now raise an exception. Should those tests remain in place? If so, should we rewrite them to avoid the version-specific Python messages (possibly by wrapping them in a PL/pgSQL function that traps the errors), or should we just leave the tests alone now that we think we understand what's happening? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] No user being created during initdb for OS X
On Thu, Jul 21, 2005 at 02:38:51PM +1000, Jamie Deppeler wrote: > I am doing it right > /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data How are you determining that "no user is being created"? What are you doing and what's the exact error message? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Patch to fix plpython on OS X
On Tue, Jul 19, 2005 at 06:06:00PM -0500, Jim C. Nasby wrote: > [EMAIL > PROTECTED]:00]~/buildfarm/HEAD/pgsqlkeep.1121809875/src/pl/plpython:41%otool > -L libplpython.0.0.so > libplpython.0.0.so: > /System/Library/Frameworks/Python.framework/Versions/2.3/Python > (compatibility version 2.3.0, current version 2.3.0) > /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current > version 71.1.3) If that first object has something to do with Python 2.3 then we might have found the culprit. But how'd you get that? > configure.log:checking Python configuration directory... > /opt/local/lib/python2.4/config > configure.log:checking how to link an embedded Python application... > -L/opt/local/lib/python2.4/config -lpython2.4 -ldl The above looks reasonable. > make.log:ccache gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes > -Wpointer-arith -Wendif-labels -fno-strict-aliasing -g -bundle plpython.o > -L../../../src/port -L/opt/local/lib -bundle_loader > ../../../src/backend/postgres -framework Python -o libplpython.0.0.so Hmmm...what's that "-framework Python" business? Looks mighty suspicious in light of the otool output. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Patch to fix plpython on OS X
On Tue, Jul 19, 2005 at 03:11:35PM -0500, Jim C. Nasby wrote: > On Tue, Jul 19, 2005 at 01:54:00PM -0600, Michael Fuhr wrote: > > Does this machine have ldd or the equivalent? If so, can you compare > > "ldd /path/to/python" and "ldd /path/to/plpython.so"? > > Oddly, no, it doesn't seem to have ldd. And the buildfarm script seems > to clean everything up even in the pgsqlkeep directories; or at least I > couldn't find a plpython.so laying around. [googles] "otool -L" appears to be the Darwin equivalent of ldd. If you can manage to find a plpython.so then it would be interesting to see which libpython it's linked against. Can you search the system for all files named libpython* and post what you find? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Patch to fix plpython on OS X
On Tue, Jul 19, 2005 at 02:48:52PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > I don't think it's a version issue; cuckoo is at 2.4, platypus used to > > be at 2.3 but I upgraded it to 2.4 to see if that was the issue, but > > platypus kept working. > > Hmm ... if it's *not* a version thing then I really do want to know > what's causing it. Anyone have an idea why this machine is saying > '\u80' where everyone else's python says u'\x80' ? Is it possible that plpython.so is linked against an old version of libpython? I see that the error message changed a few years ago: http://cvs.sourceforge.net/viewcvs.py/python/python/dist/src/Python/exceptions.c?r1=1.44&r2=1.45 http://cvs.sourceforge.net/viewcvs.py/python/python/dist/src/Python/exceptions.c?r1=1.45&r2=1.46 As I recall, Python must be configured with --enable-shared or you don't get a shared version of libpython, so if you installed a new Python but not a new version of libpython.*.so, then plpython.so might be linked against an old version. Does this machine have ldd or the equivalent? If so, can you compare "ldd /path/to/python" and "ldd /path/to/plpython.so"? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] 7.3 regression failures after recent commit
My Solaris 9 box has the same regression failures for copy2, domain, and alter_table in REL7_3_STABLE that caribou and stoat are showing (geometry fails on those boxes as well, but passes on mine). http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=caribou&dt=2005-07-14%2006:42:19 http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=stoat&dt=2005-07-14%2007:18:53 psql segfaults a couple of times during the tests; here's a stack trace: #0 0xff3655e8 in DLRemHead (l=0x0) at dllist.c:170 #1 0xff35d0c0 in PQnotifies (conn=0x4d970) at fe-exec.c:1560 #2 0x00019334 in SendQuery (query=0x4d970 "") at common.c:501 This regression failure began after the recent "Fix libpq memory leak during PQreset()" commit: http://archives.postgresql.org/pgsql-committers/2005-07/msg00285.php I just commented out the change and the regression tests now pass. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PL/Perl list value return causes segfault
On Tue, Jul 12, 2005 at 02:59:37PM -0400, Andrew Dunstan wrote: > > Note that all plperl functions are called in scalar context, and it is > always wrong to return a list (as opposed to a listref). In fact, the > value received might surprise you even if it worked (it would be the > value of the last member of the list). Yeah, I knew that returning a list was contrary to what was expected, but I wanted to see what would happen. I wasn't expecting a core dump :-( Thanks for the patch. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PL/Perl list value return causes segfault
In the latest HEAD, a PL/Perl function that returns a list value instead of a reference causes a segmentation fault: CREATE FUNCTION foo() RETURNS integer[] AS $$ return (1, 2, 3, 4); $$ LANGUAGE plperl; SELECT foo(); server closed the connection unexpectedly Here's the stack trace: #0 0xfed45bcc in plperl_call_handler (fcinfo=0xffbfe230) at plperl.c:1031 #1 0x0010e7d4 in ExecMakeFunctionResult (fcache=0x44af00, econtext=0x44ae58, isNull=0x44b470 "\177~\177\177\177\177\177\177", isDone=0x44b4d8) at execQual.c:1031 #2 0x001122b0 in ExecProject (projInfo=0x44af00, isDone=0x44ae58) at execQual.c:3607 -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 4 pgcrypto regressions failures - 1 unsolved
On Mon, Jul 11, 2005 at 06:41:35PM +0300, Marko Kreen wrote: > When I saw that only 0.9.7[efg] have new signature I even > considered macrofying that. But now with 0.9.8 again different > I really would like to not to touch it, as I have no idea which > one will be the stable signature. > > Comments? Sounds like a question for the OpenSSL developers. If a search through their list archives or CVS repository doesn't yield the answer, then maybe asking the question on one of their lists will. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 4 pgcrypto regressions failures - 1 unsolved
On Mon, Jul 11, 2005 at 10:39:26AM -0400, Tom Lane wrote: > Marko Kreen writes: > > They won't matter on older OpenSSL, as the macros will recast > > again. But on 0.9.7e the signature is: > > > void DES_ecb3_encrypt(const unsigned char *input, unsigned char *output, > > DES_key_schedule *ks1,DES_key_schedule *ks2, > > DES_key_schedule *ks3, int enc); > > > so it seems to me that with your patch the warnings will appear > > on newer OpenSSL. (Confirmed) > > Grumble --- you're right. It's probably not worth ifdef'ing the code to > suppress the warnings on 0.9.7a ... Hmmm...in 0.9.8 the signature is back to what it was in 0.9.7[a-d]: void DES_ecb3_encrypt(const_DES_cblock *input, DES_cblock *output, DES_key_schedule *ks1,DES_key_schedule *ks2, DES_key_schedule *ks3, int enc); -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.1dev ssl broke?
On Thu, Jul 07, 2005 at 12:30:35PM -0700, Joshua D. Drake wrote: > I pulled cvs today and performed the following: > > ./configure --with-openssl --prefix=/tmp/pgsqldev Did the build actually find OpenSSL? Does "ldd postgres" show it linked against libcrypto and libssl (I'm assuming those are shared libraries on your system)? > LOG: invalid entry in file "/tmp/pgsqldev/data/pg_hba.conf" at line 73, > token "hostssl" No problems here with the latest HEAD. Is it possible that you're running a non-SSL enabled postmaster, either because the build didn't find OpenSSL or because the postmaster you ran is from a different build? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] oids vs composite types, in cvs head
On Tue, Jul 05, 2005 at 09:22:37PM -0600, Michael Fuhr wrote: > > It fails for any system column -- weren't there some changes recently > in how system columns are handled? ...or was that just discussion that never resulted in any changes? I'm still digging through the archives trying to find what I'm remembering. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] oids vs composite types, in cvs head
On Tue, Jul 05, 2005 at 10:55:38PM -0400, Tom Lane wrote: > Andrew - Supernews <[EMAIL PROTECTED]> writes: > > This works on 7.4 and 8.0 but not in cvs head: > > create function foo(pg_type) returns oid as 'select $1.oid' language sql; > > ERROR: column "oid" not found in data type pg_type > > CONTEXT: SQL function "foo" > > > Is this intentional, or did the no-oids-by-default changes cut too deep? > > Hmm ... offhand, I'd say that if it worked in 8.0 it was only by chance. > In general a tuple that matches the signature of a pg_type row need not > contain an OID. Still it does seem that we have broken some specific > cases that used to work. Anyone want to dig into the details? It fails for any system column -- weren't there some changes recently in how system columns are handled? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pgcrypto 3des failure, OpenSSL 0.9.8, Solaris 9/sparc
On Tue, Jul 05, 2005 at 07:21:17PM +0300, Marko Kreen wrote: > > It is a bug in pgcrypto. I can only excuse it with my strong antipathy > towards 3des. > > Could you test it with newer OpenSSL? Looks good. After applying the patch, all pgcrypto regression tests pass on my box running Solaris 9/sparc, OpenSSL 0.9.8-beta6, and HEAD. I expect you'll need to submit the patch to pgsql-patches so it'll get put in the queue. Thanks. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] pgcrypto 3des failure, OpenSSL 0.9.8, Solaris 9/sparc
On my Solaris 9/sparc box with OpenSSL 0.9.8-beta6, the pgcrypto
regression tests fail the 3des test. I haven't checked against
older versions of OpenSSL; I'll do so when I get a chance.
I haven't dug into the pgcrypto code yet -- is it doing anything
that might be platform-specific? Or is this more likely a problem
with OpenSSL?
regression.diffs attached.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
*** ./expected/3des.out Sun Mar 20 22:24:51 2005
--- ./results/3des.out Tue Jul 5 08:20:26 2005
***
*** 17,23
select encode(encrypt('', 'foo', '3des'), 'hex');
encode
--
! 9b641a6936249eb4
(1 row)
-- 10 bytes key
--- 17,23
select encode(encrypt('', 'foo', '3des'), 'hex');
encode
--
! 4d91b5c9cc64e1c9
(1 row)
-- 10 bytes key
***
*** 24,30
select encode(encrypt('foo', '0123456789', '3des'), 'hex');
encode
--
! 6f02b7076a366504
(1 row)
-- 22 bytes key
--- 24,30
select encode(encrypt('foo', '0123456789', '3des'), 'hex');
encode
--
! d2fb8baa1717cb02
(1 row)
-- 22 bytes key
***
*** 37,44
-- decrypt
select decrypt(encrypt('foo', '0123456', '3des'), '0123456', '3des');
decrypt
! -
! foo
(1 row)
-- iv
--- 37,44
-- decrypt
select decrypt(encrypt('foo', '0123456', '3des'), '0123456', '3des');
decrypt
!
! \327\007C\202\334|\232\310
(1 row)
-- iv
***
*** 45,56
select encode(encrypt_iv('foo', '0123456', 'abcd', '3des'), 'hex');
encode
--
! df27c264fb24ed7a
(1 row)
select decrypt_iv(decode('df27c264fb24ed7a', 'hex'), '0123456', 'abcd',
'3des');
decrypt_iv
!
! foo
(1 row)
--- 45,56
select encode(encrypt_iv('foo', '0123456', 'abcd', '3des'), 'hex');
encode
--
! a1917305c1df08f8
(1 row)
select decrypt_iv(decode('df27c264fb24ed7a', 'hex'), '0123456', 'abcd',
'3des');
decrypt_iv
! -
! \325c\311\365\223\312E_
(1 row)
==
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
