[BUGS] ecpg preprocessor regression in 9.0
This used to work in the PostgreSQL 8.4 ecpg preprocessor:
EXEC SQL EXECUTE mystmt USING 1.23;
but in 9.0 it throws an error:
floattest.pgc:39: ERROR: variable "1" is not declared
Attached is the full test case, drop it in
src/interfaces/ecpg/test/preproc and compile.
I bisected the cause to this commit:
commit b2bddc2ff22f0c3d54671e43c67a2563deed7908
Author: Michael Meskes
Date: Thu Apr 1 08:41:01 2010 +
Applied Zoltan's patch to make ecpg spit out warnings if a local
variable hides a global one with the same name.
I don't immediately see why that's causing it, but it doesn't seem
intentional.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
#include
#include
EXEC SQL include ../regression;
int
main (void)
{
ECPGdebug (1, stderr);
EXEC SQL BEGIN DECLARE SECTION;
float f = 4.56;
const char *stmt2 = "INSERT INTO floattest VALUES (?);";
EXEC SQL END DECLARE SECTION;
EXEC SQL connect to REGRESSDB1;
if (sqlca.sqlcode)
{
printf ("connect error = %ld\n", sqlca.sqlcode);
exit (sqlca.sqlcode);
}
EXEC SQL create table floattest (f float);
if (sqlca.sqlcode)
{
printf ("create error = %ld\n", sqlca.sqlcode);
exit (sqlca.sqlcode);
}
EXEC SQL PREPARE mystmt FROM :stmt2;
if (sqlca.sqlcode)
{
printf ("prepare error = %ld\n", sqlca.sqlcode);
exit (sqlca.sqlcode);
}
EXEC SQL EXECUTE mystmt USING 1.23;
if (sqlca.sqlcode)
{
printf ("insert error = %ld\n", sqlca.sqlcode);
exit (sqlca.sqlcode);
}
EXEC SQL COMMIT;
EXEC SQL disconnect;
exit (0);
}
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] ecpg preprocessor regression in 9.0
On 01.11.2010 15:31, Heikki Linnakangas wrote:
This used to work in the PostgreSQL 8.4 ecpg preprocessor:
EXEC SQL EXECUTE mystmt USING 1.23;
but in 9.0 it throws an error:
floattest.pgc:39: ERROR: variable "1" is not declared
Attached is the full test case, drop it in
src/interfaces/ecpg/test/preproc and compile.
I bisected the cause to this commit:
commit b2bddc2ff22f0c3d54671e43c67a2563deed7908
Author: Michael Meskes
Date: Thu Apr 1 08:41:01 2010 +
Applied Zoltan's patch to make ecpg spit out warnings if a local
variable hides a global one with the same name.
I don't immediately see why that's causing it, but it doesn't seem
intentional.
On closer look, it's quite obvious: the code added to ECPGdump_a_type
thinks that ECPGt_const is a variable type, and tries to look up the
variable. The straightforward fix is this:
diff --git a/src/interfaces/ecpg/preproc/type.c
b/src/interfaces/ecpg/preproc/type.c
index cc668a2..a53018b 100644
--- a/src/interfaces/ecpg/preproc/type.c
+++ b/src/interfaces/ecpg/preproc/type.c
@@ -246,7 +246,7 @@ ECPGdump_a_type(FILE *o, const char *name, struct
ECPGtype * type,
struct variable *var;
if (type->type != ECPGt_descriptor && type->type != ECPGt_sqlda &&
- type->type != ECPGt_char_variable &&
+ type->type != ECPGt_char_variable && type->type != ECPGt_const
&&
brace_level >= 0)
{
char *str;
But I wonder if there is a better way to identify variable-kind of
ECPGttypes than list the ones that are not. There's some special
ECPGttypes still missing from the above if-test, like
ECPGt_NO_INDICATOR, but I'm not sure if they can ever be passed to
ECPGdump_a_type. Seems a bit fragile anyway.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5738: btree index search bug
The following bug has been logged online: Bug reference: 5738 Logged by: spche Email address: sp...@163.com PostgreSQL version: 9.01 Operating system: Winxp Description:btree index search bug Details: create table a (a int); create index a_a on a(a); insert into a values(1); insert into a values(2); insert into a select * from a; insert into a select * from a; insert into a select * from a; insert into a select * from a; insert into a select * from a; insert into a select * from a; insert into a select * from a; insert into a select * from a; insert into a select * from a; insert into a select * from a; insert into a select * from a; insert into a select * from a; delete from a where a=3; CREATE or replace FUNCTION aa() RETURNS integer AS $$ DECLARE curs2 cursor for SELECT a FROM a where a=3; rowv1 integer DEFAULT 0; rowv2 integer DEFAULT 32; cou1 integer DEFAULT 0; BEGIN insert into a values(3); OPEN curs2; FETCH curs2 INTO rowv1; IF FOUND THEN cou1 := cou1 + 1 ; END IF; insert into a values(3); insert into a values(3); insert into a values(3); --update a set a=4 where a=3; FETCH curs2 INTO rowv2; IF FOUND THEN cou1 := cou1 + 1 ; END IF; FETCH curs2 INTO rowv2; IF FOUND THEN cou1 := cou1 + 1 ; END IF; FETCH curs2 INTO rowv2; IF FOUND THEN cou1 := cou1 + 1 ; END IF; close curs2; RETURN cou1; END; $$ LANGUAGE plpgsql; select aa(); the result is 1; the index scan is not updated; because static bool _bt_readpage(IndexScanDesc scan, ScanDirection dir, OffsetNumber offnum) copy a old version, and not update when new version come. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5738: btree index search bug
"spche" writes: > Description:btree index search bug I see no bug here. The cursor is opened at a time when there is one row with a=3, so it can find only that one row because of its snapshot. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] index on function confuses drop table cascade on child
Checkout from HEAD this morning, no modifications. Did make
distclean and fresh build to ensure no problems caused by mixed
build. 32 bit kubuntu on single drive dual core workstation. Fresh
initdb. Default configuration.
postgres=# create database bug;
CREATE DATABASE
postgres=# \c bug
You are now connected to database "bug" as user "kgrittn".
bug=# create table person (namel text not null);
CREATE TABLE
bug=# create table t (id int primary key) inherits (person);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"t_pkey" for table "t"
CREATE TABLE
bug=# create or replace function sname(person) returns text
bug-# language sql immutable as $$select upper($1.namel)$$;
CREATE FUNCTION
bug=# create index t_sname on t (sname(t));
CREATE INDEX
bug=# select oid, relname from pg_class
bug-# where relname in ('person','t');
oid | relname
---+-
16385 | person
16391 | t
(2 rows)
bug=# drop table t cascade;
DROP TABLE
[so far, behavior is as expected]
bug=# drop table person cascade;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to function sname(person)
drop cascades to index t_sname
ERROR: could not open relation with OID 16391
bug=# create table person (namel text not null);
ERROR: relation "person" already exists
bug=# create table t (id int primary key) inherits (person);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"t_pkey" for table "t"
CREATE TABLE
bug=# create or replace function sname(person) returns text
bug-# language sql immutable as $$select upper($1.namel)$$;
CREATE FUNCTION
bug=# create index t_sname on t (sname(t));
ERROR: relation "t_sname" already exists
bug=# select oid, relname from pg_class
bug-# where relname in ('person','t');
oid | relname
---+-
16385 | person
16401 | t
(2 rows)
All is fine if the t_searchname index is left out or the t table
defines all columns directly rather than inheriting them from
person. Similar failure if person is dropped first or on the same
statement as t.
-Kevin
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5739: postgresql will not start
The following bug has been logged online: Bug reference: 5739 Logged by: Kim Garren Email address: deda...@bjoernvold.com PostgreSQL version: 9.0.1-2 Operating system: Arch Linux Description:postgresql will not start Details: Received update to postgresql 9.0.1-2 yesterday, and now postgresql won't start. On boot, I receive the [BUSY] message when it attempts to start, but never receive the [OK] denoting that it has started. Once booted, I try to start it manually, with the following result: # /etc/rc.d/postgresql start :: Starting PostgreSQL [BUSY] server starting [DONE] To test if it starts, I perform the following, with the results included here: # /etc/rc.d/postgresql stop :: Stopping PostgreSQL [BUSY] pg_ctl: PID file "/var/lib/postgres/data/postmaster.pid" does not exist Is server running? [FAIL] I have done everything I know to no avail; including uninstalling and re-installing postgresql. More info can be obtained here in these forum threads: http://bjoernvold.com/forum/viewtopic.php?f=20&t=822 https://bbs.archlinux.org/viewtopic.php?id=107656 A bug for Arch Linux has been filed; however the issue was determined to be upstream: https://bugs.archlinux.org/task/21560 Anything that can shed some light on this situation would be appreciated; I have lost my music db because of this. Thank you. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5739: postgresql will not start
"Kim Garren" wrote: > Received update to postgresql 9.0.1-2 yesterday How? From where? What were you running before? > Once booted, I try to start it manually, with the following > result: > > # /etc/rc.d/postgresql start > :: Starting PostgreSQL > [BUSY] server starting > > [DONE] What is showing for `ps aux | grep postgres` ? What is in the logs? > https://bbs.archlinux.org/viewtopic.php?id=107656 > https://bugs.archlinux.org/task/21560 Both of these make it sound like you were upgrading from PostgreSQL 8.4. This requires a database conversion. What technique did you use for that? (The more specific you can be about the exact steps, the more likely it is that someone will be able to help you.) > I have lost my music db because of this. Not unless you deleted your data directory. If you don't back it up regularly, it would certainly be wise to do so before a major release upgrade. Making a recursive copy of the database data directory would be a very good idea right now. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] index on function confuses drop table cascade on child
"Kevin Grittner" writes: > create index t_sname on t (sname(t)); Huh, interesting. The reason the DROP misbehaves is that the index doesn't have any dependency at all on table "t". Which appears to be exposing the folly of this bit in find_expr_references_walker: /* * A whole-row Var references no specific columns, so adds no new * dependency. */ if (var->varattno == InvalidAttrNumber) return false; This is broken at least as far back as 8.1. Surprising no one's noticed before. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] index on function confuses drop table cascade on child
I wrote:
> "Kevin Grittner" writes:
>> create index t_sname on t (sname(t));
> Huh, interesting. The reason the DROP misbehaves is that the index
> doesn't have any dependency at all on table "t". Which appears to
> be exposing the folly of this bit in find_expr_references_walker:
> /*
> * A whole-row Var references no specific columns, so adds no new
> * dependency.
> */
Hmm. Actually there is more here than meets the eye. There are at
least three ways we could fix this, each with their own pluses and
minuses:
1. The proximate reason that no dependency on table "t" gets generated
is that this bit in index_create() supposes that any Var in the index
expressions will result in some dependency on the index, so it need not
add a redundant one:
/*
* It's possible for an index to not depend on any columns of the
* table at all, in which case we need to give it a dependency on
* the table as a whole; else it won't get dropped when the table
* is dropped. This edge case is not totally useless; for
* example, a unique index on a constant expression can serve to
* prevent a table from containing more than one row.
*/
if (!have_simple_col &&
!contain_vars_of_level((Node *) indexInfo->ii_Expressions, 0) &&
!contain_vars_of_level((Node *) indexInfo->ii_Predicate, 0))
{
referenced.classId = RelationRelationId;
referenced.objectId = heapRelationId;
referenced.objectSubId = 0;
recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO);
}
Of course this isn't allowing for the special case about whole-row Vars.
So one fix would be to just delete these contain_vars_of_level() checks
and thus emit a whole-table dependency record whenever there aren't any
simple index columns. There is much to be said for this method; it's
both simpler and more robust than the current approach, and it has very
little risk of breaking anything because it won't affect any other
dependency-creating behavior. I don't care that much about the
possibility of emitting an extra dependency record, either. However,
this only fixes the issue for indexes, and doesn't do anything against
the possibility of current or future bugs of the same ilk elsewhere.
2. We could change the special case for whole-row Vars in
find_expr_references_walker. Now the interesting thing about that is
that it has cases for whole-row Vars referencing either simple relations
or JOIN expressions. Emitting a whole-table dependency seems to be the
right thing for the simple-relation case, but it is not possible to do
that for a JOIN. You might think that we should fix the JOIN case to
generate dependencies on all the individual columns of the JOIN, but in
fact that would be exactly the wrong thing --- the whole point here is
that the whole-row reference isn't invalidated by dropping any one
column in the input relation(s). That's why the special case is written
the way it is. It's okay as-is for references inside ordinary query
trees, because there will be whole-row references associated with the
rtable entries anyway. So we could possibly leave the JOIN case alone
(ie, ignore whole-row references) and just change the simple-rel case.
But that's likely to look broken no matter how much we try to explain it
in the comments; and perhaps it would actually *be* broken in some
future usage.
3. Or, perhaps we could change recordDependencyOnSingleRelExpr so that
it generates a whole-table dependency on the target relation even if
there are no Vars in the expression. This would make it act much more
like the regular-query context that find_expr_references_walker is
expecting --- in essence, since we're fabricating a single-element
rtable for find_expr_references_walker to work with, we should fabricate
the implied whole-table dependency entry too. But that seems a bit
weird too, and in particular it's not obvious whether to do that if in
fact the expression is empty, or doesn't contain any Var at all.
Right now, the only uses of recordDependencyOnSingleRelExpr are the ones
for index expressions and predicates (ie, exactly the current issue) and
the one for CHECK constraint expressions. The latter case does not have
a bug because CreateConstraintEntry is coded to create a whole-table
dependency if there aren't any easily-identifiable column dependencies
(ie, exactly the equivalent of fix #1 above for the index code).
I'm a bit tempted to go with solution #1, but we'd have to recognize
that probably every future use of recordDependencyOnSingleRelExpr would
be exposed to this same type of bug if it got too cute about eliminating
"redundant" dependencies. But at the same time, predicting what behavior
such uses might need is a tough game in itself, and maybe one that we
shouldn't get into now.
Any tho
