Re: [BUGS] bug with aggregate + multi column index + index_scan
Brian Hirt <[EMAIL PROTECTED]> writes: > I've run across a rather nasty bug in 8.1.2. It seems when the > planer uses an index_scan within a GroupAggregate for a multi column > index you can get incorrect results. Good catch. Looks to me like it only happens if two or more leading index columns are equated to the same constant value, ie where id1 = 1 and id2 = 1 and ... will show the bug but where id1 = 1 and id2 = 2 and ... won't. Does that match up with the original behavior that led you to make the test case? The problem is that implied equality deduction causes the planner to conclude id1 = id2, and this extra bit of info is confusing the code that determines whether the index's sort order can be considered to match the needs of the GROUP BY clause. So you get a plan that feeds the IndexScan directly to GroupAggregate, which is wrong because the data isn't sorted by "grp". In the related case select grp,sum(v) from test where id1 = 1 and id2 = 2 and day = '1/1/2006' group by grp order by sum(v) desc; it *is* OK to decide that the indexscan result is effectively sorted by "grp", so it's important to have this check ... it's just not being done quite right. Thanks for the test case! regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] bug with aggregate + multi column index + index_scan
Brian Hirt <[EMAIL PROTECTED]> writes: > I've run across a rather nasty bug in 8.1.2. It seems when the > planer uses an index_scan within a GroupAggregate for a multi column > index you can get incorrect results. Patch is here if you need it before 8.1.3: http://archives.postgresql.org/pgsql-committers/2006-01/msg00377.php regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2218: Variables selected in VIEWs under different names break queries using those views
"Nicholas" <[EMAIL PROTECTED]> writes: > Description:Variables selected in VIEWs under different names break > queries using those views This is a poor description, the problem actually seems to be related to using sub-SELECTs in bitmap index scan conditions. Patch is here if you need it before 8.1.3 comes out: http://archives.postgresql.org/pgsql-committers/2006-01/msg00382.php regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[BUGS] BUG #2221: Bad delimiters allowed in COPY ... TO
The following bug has been logged online:
Bug reference: 2221
Logged by: David Fetter
Email address: [EMAIL PROTECTED]
PostgreSQL version: all
Operating system: all
Description:Bad delimiters allowed in COPY ... TO
Details:
This came up while I was testing my pg_dump "specify DELIMITER AS and/or
NULL AS" patch.
Below is a repro using newline. A similar problem happens when you specify
the delimiter as a backslash. Should COPY simply not allow these
delimiters? What about carriage return? Just in general, what bytes other
than null ought COPY to reject out of hand?
Also, what regression tests do we want to put in in order to ensure that
COPY ... TO generates output that COPY ... FROM can understand?
CREATE TABLE pqxxevents (
"year" integer,
event text
);
COPY pqxxevents ("year", event) FROM stdin DELIMITER AS '^M';
2010^MOdyssey Two
2038^Mtime_t overflow
1971^Mjtv
1981^MC:\\>
1997^MAsian crisis
1999^M\N
1978^Mbloody\t\tcold
1989^MOde an die Freiheit
2001^MNew millennium
2001^M'911' WTC attack
2001^MA Space Odyssey
2002^Mlibpqxx
3001^MFinal Odyssey
\.
---(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: [BUGS] BUG #2221: Bad delimiters allowed in COPY ... TO
On Sun, Jan 29, 2006 at 09:50:08PM +, David Fetter wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2221
> Logged by: David Fetter
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: all
> Operating system: all
> Description:Bad delimiters allowed in COPY ... TO
> Details:
>
> This came up while I was testing my pg_dump "specify DELIMITER AS and/or
> NULL AS" patch.
Folks,
Please pardon the self-followup. I believe that this patch fixes the
problem in COPY.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
Index: src/backend/commands/copy.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/copy.c,v
retrieving revision 1.257
diff -c -r1.257 copy.c
*** src/backend/commands/copy.c 28 Dec 2005 03:25:32 - 1.257
--- src/backend/commands/copy.c 30 Jan 2006 00:39:28 -
***
*** 51,56
--- 51,57
#define ISOCTAL(c) (((c) >= '0') && ((c) <= '7'))
#define OCTVALUE(c) ((c) - '0')
+ #define BADCHARS "\r\n\\"
/*
* Represents the different source/dest cases we need to worry about at
***
*** 856,861
--- 857,867
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY delimiter must be a single
character")));
+ /* Disallow the forbidden_delimiter strings from
src/include/commands/copy.h */
+ if (strcspn(cstate->delim, BADCHARS) != 1)
+ elog(ERROR, "COPY delimiter cannot be %#02x",
+*cstate->delim);
+
/* Check header */
if (!cstate->csv_mode && cstate->header_line)
ereport(ERROR,
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [BUGS] bug with aggregate + multi column index + index_scan
Tom, Yes, what you describe are exactly the circumstances that are required for our query to fail. Once again, thanks for the great help and quick fix. Do you think this fix will make 8.1.3? Best Regards, Brian Hirt On Jan 29, 2006, at 9:31 AM, Tom Lane wrote: Brian Hirt <[EMAIL PROTECTED]> writes: I've run across a rather nasty bug in 8.1.2. It seems when the planer uses an index_scan within a GroupAggregate for a multi column index you can get incorrect results. Good catch. Looks to me like it only happens if two or more leading index columns are equated to the same constant value, ie where id1 = 1 and id2 = 1 and ... will show the bug but where id1 = 1 and id2 = 2 and ... won't. Does that match up with the original behavior that led you to make the test case? The problem is that implied equality deduction causes the planner to conclude id1 = id2, and this extra bit of info is confusing the code that determines whether the index's sort order can be considered to match the needs of the GROUP BY clause. So you get a plan that feeds the IndexScan directly to GroupAggregate, which is wrong because the data isn't sorted by "grp". In the related case select grp,sum(v) from test where id1 = 1 and id2 = 2 and day = '1/1/2006' group by grp order by sum(v) desc; it *is* OK to decide that the indexscan result is effectively sorted by "grp", so it's important to have this check ... it's just not being done quite right. Thanks for the test case! regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [BUGS] BUG #2221: Bad delimiters allowed in COPY ... TO
On Sun, Jan 29, 2006 at 04:41:43PM -0800, David Fetter wrote:
> On Sun, Jan 29, 2006 at 09:50:08PM +, David Fetter wrote:
> >
> > The following bug has been logged online:
> >
> > Bug reference: 2221
> > Logged by: David Fetter
> > Email address: [EMAIL PROTECTED]
> > PostgreSQL version: all
> > Operating system: all
> > Description:Bad delimiters allowed in COPY ... TO
> > Details:
> >
> > This came up while I was testing my pg_dump "specify DELIMITER AS and/or
> > NULL AS" patch.
>
> Folks,
>
> Please pardon the self-followup. I believe that this patch fixes
> the problem in COPY.
Another followup, this time with the comment done right.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
Index: src/backend/commands/copy.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/copy.c,v
retrieving revision 1.257
diff -c -r1.257 copy.c
*** src/backend/commands/copy.c 28 Dec 2005 03:25:32 - 1.257
--- src/backend/commands/copy.c 30 Jan 2006 01:01:20 -
***
*** 51,56
--- 51,57
#define ISOCTAL(c) (((c) >= '0') && ((c) <= '7'))
#define OCTVALUE(c) ((c) - '0')
+ #define BADCHARS "\r\n\\"
/*
* Represents the different source/dest cases we need to worry about at
***
*** 856,861
--- 857,867
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY delimiter must be a single
character")));
+ /* Disallow the forbidden_delimiter strings */
+ if (strcspn(cstate->delim, BADCHARS) != 1)
+ elog(ERROR, "COPY delimiter cannot be %#02x",
+*cstate->delim);
+
/* Check header */
if (!cstate->csv_mode && cstate->header_line)
ereport(ERROR,
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] bug with aggregate + multi column index + index_scan
Brian Hirt <[EMAIL PROTECTED]> writes: > Yes, what you describe are exactly the circumstances that are > required for our query to fail. Once again, thanks for the great > help and quick fix. Do you think this fix will make 8.1.3? It's already in CVS ... regards, tom lane ---(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
[BUGS] BUG #2220: PostgreSQL-JDBC 8.1-404 fails to compile with ecj
The following bug has been logged online: Bug reference: 2220 Logged by: Bernhard Rosenkraenzer Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.2 Operating system: Linux (Ark Linux 2006.1-pre) Description:PostgreSQL-JDBC 8.1-404 fails to compile with ecj Details: PostgreSQL-JDBC 8.1-404 fails to compile (ecj bytecode compiler w/ gij 4.1 JDK): [javac] 1. ERROR in /usr/src/ark/BUILD/postgresql-jdbc-8.1-404.src/org/postgresql/jdbc3g/Jdbc3gA rray.java [javac] (at line 17) [javac] public class Jdbc3gArray extends org.postgresql.jdbc2.AbstractJdbc2Array implements java.sql.Array [javac] ^^^ [javac] The type Jdbc3gArray must implement the inherited abstract method Array.getArray(long, int, Map) [javac] -- [javac] 2. ERROR in /usr/src/ark/BUILD/postgresql-jdbc-8.1-404.src/org/postgresql/jdbc3g/Jdbc3gA rray.java [javac] (at line 17) [javac] public class Jdbc3gArray extends org.postgresql.jdbc2.AbstractJdbc2Array implements java.sql.Array [javac] ^^^ [javac] The type Jdbc3gArray must implement the inherited abstract method Array.getArray(Map) [javac] -- [javac] 3. ERROR in /usr/src/ark/BUILD/postgresql-jdbc-8.1-404.src/org/postgresql/jdbc3g/Jdbc3gA rray.java [javac] (at line 17) [javac] public class Jdbc3gArray extends org.postgresql.jdbc2.AbstractJdbc2Array implements java.sql.Array [javac] ^^^ [javac] The type Jdbc3gArray must implement the inherited abstract method Array.getResultSet(long, int, Map) [javac] -- [javac] 4. ERROR in /usr/src/ark/BUILD/postgresql-jdbc-8.1-404.src/org/postgresql/jdbc3g/Jdbc3gA rray.java [javac] (at line 17) [javac] public class Jdbc3gArray extends org.postgresql.jdbc2.AbstractJdbc2Array implements java.sql.Array [javac] ^^^ [javac] The type Jdbc3gArray must implement the inherited abstract method Array.getResultSet(Map) [javac] -- [javac] 5. ERROR in /usr/src/ark/BUILD/postgresql-jdbc-8.1-404.src/org/postgresql/jdbc3g/Jdbc3gA rray.java [javac] (at line 24) [javac] public Object getArray(Map < String, Class < ? >> map) throws SQLException [javac] ^ [javac] The type Class is not generic; it cannot be parameterized with arguments [javac] -- [javac] 6. ERROR in /usr/src/ark/BUILD/postgresql-jdbc-8.1-404.src/org/postgresql/jdbc3g/Jdbc3gA rray.java [javac] (at line 29) [javac] public Object getArray(long index, int count, Map < String, Class < ? >> map) throws SQLException [javac] ^ [javac] The type Class is not generic; it cannot be parameterized with arguments [javac] -- [javac] 7. ERROR in /usr/src/ark/BUILD/postgresql-jdbc-8.1-404.src/org/postgresql/jdbc3g/Jdbc3gA rray.java [javac] (at line 34) [javac] public ResultSet getResultSet(Map < String, Class < ? >> map) throws SQLException [javac] ^ [javac] The type Class is not generic; it cannot be parameterized with arguments [javac] -- [javac] 8. ERROR in /usr/src/ark/BUILD/postgresql-jdbc-8.1-404.src/org/postgresql/jdbc3g/Jdbc3gA rray.java [javac] (at line 39) [javac] public ResultSet getResultSet(long index, int count, Map < String, Class < ? >> map) [javac] throws SQLException [javac] ^ [javac] The type Class is not generic; it cannot be parameterized with arguments [javac] -- [javac] -- [javac] 9. ERROR in /usr/src/ark/BUILD/postgresql-jdbc-8.1-404.src/org/postgresql/jdbc3g/Jdbc3gC allableStatement.java [javac] (at line 15) [javac] class Jdbc3gCallableStatement extends Jdbc3gPreparedStatement implements CallableStatement [javac] ^^^ [javac] The type Jdbc3gCallableStatement must implement the inherited abstract method CallableStatement.getObject(String, Map) [javac] -- [javac] 10. ERROR in /usr/src/ark/BUILD/postgresql-jdbc-8.1-404.src/org/postgresql/jdbc3g/Jdbc3gC allableStatement.java [javac] (at line 15) [javac] class Jdbc3gCallableStatement extends Jdbc3gPreparedStatement implements CallableStatement [javac] ^^^ [javac] The type Jdbc3gCallableStatement must implement the inherited abstract method CallableStatement.getO [javac] bject(int, Map) [javac] -- [javac] 11. ERROR in /usr/src/ark/BUILD/postgresql-jdbc-8.1-404.src/org/postgresql/jdbc3g/Jdbc3gC allableStatement.java [javac] (at line 26) [javac] public Object getObject(int i, Map < String, Class < ? >> map) throws SQLException [javac]
[BUGS] BUG #2222: Can not reinstall due to account errors
The following bug has been logged online: Bug reference: Logged by: Jim B Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.3 Operating system: XP pro Description:Can not reinstall due to account errors Details: Having uninstalled postgres 8.0.3, I needed to reinstall on the same PC running locally. Note that the original postgres user account has been deleted. During the install, when I try to intall a new account name (postgres) and install as a service I get the following error... It informs me that "Sucessfully granted the 'log on as service' right" Then gives the following Account Error: "Invalid username specified: Logon failure: user not allowed to log on to this computer." I've tried different account names to 'postgres' with the same results. The account im using for the install is a administrator account. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [BUGS] BUG #2221: Bad delimiters allowed in COPY ...
On Sun, 2006-01-29 at 17:03 -0800, David Fetter wrote: > Another followup, this time with the comment done right. + /* Disallow the forbidden_delimiter strings */ + if (strcspn(cstate->delim, BADCHARS) != 1) + elog(ERROR, "COPY delimiter cannot be %#02x", +*cstate->delim); + The comment is still wrong: referencing "forbidden_delimiter" makes it sound like there is something named forbidden_delimiter, but there is not (at least in the patch as submitted). The patch should also use ereport rather than elog, because this error message might reasonably be encountered by the user. -Neil ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2220: PostgreSQL-JDBC 8.1-404 fails to compile with
On Sun, 29 Jan 2006, Bernhard Rosenkraenzer wrote: Bug reference: 2220 Description:PostgreSQL-JDBC 8.1-404 fails to compile with ecj Details: PostgreSQL-JDBC 8.1-404 fails to compile (ecj bytecode compiler w/ gij 4.1 JDK): This is because the driver dynamically decides what version of the driver to build (JDBC2, JDBC3, or JDBC3 w/ generics) at runtime based on the current JVM. You are running ant with a 1.5 JVM, but ecj's default -source argument is 1.4, so it's trying to build 1.5 code, but interpreting it as 1.4 code which cleary fails. Adding "source=1.5" in the tag in build.xml fixes this, but that's not a general solution because it clearly won't be able to build other versions. Do you have a better solution for runtime determination or specification of the version we should try to build. Kris Jurka ---(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: [BUGS] BUG #2217: serial in rule and trigger
On Thu, Jan 26, 2006 at 07:17:57PM +, ATTILA GATI wrote:
> create table test(id serial, txt varchar);
> create table mon(n int);
> create rule monitor as on insert to test do
> insert into mon values (NEW.id);
> insert into test (txt) values ('xxx');
>
> What I expect is to get the latest id written in
> table mon whenever I insert a data into table test.
> However test.id will be incremented by 2!
This isn't a bug, it's a misunderstanding of how rewrite rules work.
NEW.id in the rule is rewritten as whatever expression that column
had in the original query, so if id in the original query is evaluated
as nextval('test_id_seq') then it will be the same in the rule;
hence, nextval() gets called twice. See the archives for numerous
past discussion. Try using a trigger instead of a rule.
> However - although the relevant part of the documentation is identical for
> both versions - in case
> of version 8.1 I found now holes when the transaction was aborted for some
> reason (not in the above example,
> just without a trigger or rule).
> So there must be a difference between the 2 versions, but the documentation
> hasn't been modified.
Sequences don't roll back so they can have holes; that's long-standing
behavior that hasn't changed. Can you provide a test case that
behaves differently in different versions of PostgreSQL?
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PATCHES] [BUGS] BUG #2221: Bad delimiters allowed in COPY ...
On Sun, Jan 29, 2006 at 10:20:47PM -0500, Neil Conway wrote:
> On Sun, 2006-01-29 at 17:03 -0800, David Fetter wrote:
> > Another followup, this time with the comment done right.
>
> + /* Disallow the forbidden_delimiter strings */
> + if (strcspn(cstate->delim, BADCHARS) != 1)
> + elog(ERROR, "COPY delimiter cannot be %#02x",
> +*cstate->delim);
> +
>
> The comment is still wrong: referencing "forbidden_delimiter" makes
> it sound like there is something named forbidden_delimiter, but
> there is not (at least in the patch as submitted).
>
> The patch should also use ereport rather than elog, because this
> error message might reasonably be encountered by the user.
Patch with BADCHARS attached :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
Index: src/backend/commands/copy.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/copy.c,v
retrieving revision 1.257
diff -c -r1.257 copy.c
*** src/backend/commands/copy.c 28 Dec 2005 03:25:32 - 1.257
--- src/backend/commands/copy.c 30 Jan 2006 06:44:10 -
***
*** 51,56
--- 51,57
#define ISOCTAL(c) (((c) >= '0') && ((c) <= '7'))
#define OCTVALUE(c) ((c) - '0')
+ #define BADCHARS "\r\n\\"
/*
* Represents the different source/dest cases we need to worry about at
***
*** 856,861
--- 857,869
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY delimiter must be a single
character")));
+ /* Disallow BADCHARS characters */
+ if (strcspn(cstate->delim, BADCHARS) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+errmsg("COPY delimiter cannot be \"%#02x\"",
+ *cstate->delim)));
+
/* Check header */
if (!cstate->csv_mode && cstate->header_line)
ereport(ERROR,
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
