Re: [BUGS] BUG #2514: (jdbc driver) Multiple inlined statements with
On Wed, 5 Jul 2006, Gilles wrote:
Bug reference: 2514
Description:(jdbc driver) Multiple inlined statements with mixed
updates and queries return wrong results
Details:
When issuing this:
statement.execute("SELECT 2;UPDATE foo SET d='babar' where i=1; SELECT 3");
The 2 result returned is wrong. This seems to be a ResultSet containing the
same values as the first RS (it should be an update count)
Fixed in CVS for 8.0, 8.1 and 8.2dev. It was mistakenly retaining some
state from the first SELECT when issuing the update. I've put up a
temporary jar file here if you need a fix before the next official
releases. http://www.ejurka.com/pgsql/jars/gr/
Kris Jurka
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #2520: security problem
The following bug has been logged online: Bug reference: 2520 Logged by: Jean Paul Aguilar Ruiz Email address: [EMAIL PROTECTED] PostgreSQL version: 807,814 Operating system: Win xp Description:security problem Details: Hi, When you add a user as owner of database and the database has been created for another user as sistem administrator the user dont have all permissions òn the database, but if the database is created for the user (not sa) he can do all. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #2516: group privs do not seem to be honored
The following bug has been logged online:
Bug reference: 2516
Logged by: Sam Howard
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.4 -Deb Etch
Operating system: Linux - Debian Etch
Description:group privs do not seem to be honored
Details:
Trying to implement user level access and security, and am finding
unexpected behavior with respect to group roles.
This sort of user/group structure seems like it should be commonplace, so I
have a hard time believing it is a bug, but I cannot figure out why it is
not working as I expect. Apologies in advance if it is user error. :)
Platform: Debian Etch (testing)
DB: PostgreSQL 8.1.4 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.4
20060507 (prerelease) (Debian 4.0.3-3), pkg version 8.1.4-2
Simple scenerio:
Create a role to own the db objects:
CREATE ROLE dbowner LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
Create a group role for the user roles to belong to:
CREATE ROLE db_group
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
Create the application role and add it to the group:
CREATE ROLE appuser LOGIN
ENCRYPTED PASSWORD 'mdblahblahblah'
NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
GRANT db_group TO appuser;
Create table foo with a serial and varchar column, and matching sequence for
the serial:
CREATE TABLE foo
(
id serial NOT NULL,
data1 varchar(32),
CONSTRAINT foo_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE foo OWNER TO dbowner;
GRANT ALL ON TABLE foo TO dbowner;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE foo TO db_group;
Now, the default privs on the sequence do not include the db_group, so let's
add them now:
GRANT SELECT, UPDATE ON foo_id_seq TO db_group;
With a sample table and associated sequence set up, and group privs to
insert (or update in the case of the sequence), let's try an insert as the
user "appuser":
=> insert into foo (data1) values ('some stuff');
ERROR: permission denied for relation foo
=> \z foo;
Access privileges for database "db"
Schema | Name | Type | Access privileges
+--+---+--
public | foo | table | {dbowner=arwdRxt/dbowner,db_group=arwd/dbowner}
(1 row)
=> \z foo_id_seq;
Access privileges for database "db"
Schema |Name| Type | Access privileges
++--+
public | foo_id_seq | sequence |
{dbowner=arwdRxt/dbowner,db_group=rw/dbowner}
(1 row)
=> \dg;
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member
of
--+---+-+---+-+-
dbowner | no| no | no| no limit|
{db_group}
appuser | no| no | no| no limit|
{db_group}
Based on appuser being in the group role db_group, and db_group having
select, insert, update on the table foo, and select, update on its sequence,
foo_seq_id, I would expect the insert to succeed.
If I specifically grant select, insert, update for the user appuser to the
table foo like:
GRANT SELECT, INSERT, UPDATE ON foo TO appuser;
Then try my INSERT:
=> insert into foo (data1) values ('some stuff');
ERROR: permission denied for sequence foo_id_seq
This continues to make me wonder if the membership of appuser in the group
role db_group is having any effect.
Adding grants to the sequence like:
GRANT SELECT, UPDATE ON foo_id_seq TO appuser;
And then trying the INSERT again:
=> insert into foo (data1) values ('some stuff');
INSERT 0 1
SUCCESS!
Please feel free to direct me to some additional documentation that
addresses this issue, if it is not a bug. I've read over the GRANT manpage
a few times, and I *think* I'm doing it right.
The fact that if I add the specific user rights, it works, makes me hope
it's broken and not me. :)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[BUGS] BUG #2515: Full Scan with constant column
The following bug has been logged online:
Bug reference: 2515
Logged by: Daniel Naschenweng
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.7
Operating system: Red Hat Linux 3.2.3-47.3
Description:Full Scan with constant column
Details:
--- BEGIN CREATE CONTEXT ---
drop table tab1 cascade;
drop table tab2 cascade;
CREATE TABLE TAB1 (
TAB1_ID SERIAL CONSTRAINT PK_TAB1_ID PRIMARY KEY,
VALOR INTEGER
);
CREATE TABLE TAB2 (
TAB2_ID SERIAL CONSTRAINT PK_TAB2_ID PRIMARY KEY,
TAB1_ID INTEGER,
CONSTRAINT FK_TAB1_TAB2 FOREIGN KEY (TAB1_ID) REFERENCES TAB1 (TAB1_ID)
);
CREATE OR REPLACE FUNCTION POPULA_TAB ()
RETURNS NAME AS '
DECLARE
I INTEGER;
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO TAB1 (TAB1_ID,VALOR) VALUES (I,I);
INSERT INTO TAB2 (TAB1_ID) VALUES (I);
END LOOP;
RETURN ''OK'';
END;
' language 'plpgsql';
SELECT POPULA_TAB();
--- END CREATE CONTEXT ---
/* Select Seq Scan on tab2: */
explain
select t2.*
FROM tab1 t1 LEFT OUTER JOIN (select tab2.*
, 1 as coluna
from tab2
) t2 on t1.tab1_id=t2.tab2_id
WHERE t1.tab1_id=200;
QUERY PLAN
Nested Loop Left Join (cost=0.00..3958.01 rows=1 width=12)
Join Filter: ("outer".tab1_id = "inner".tab2_id)
-> Index Scan using pk_tab1_id on tab1 t1 (cost=0.00..6.01 rows=1
width=4)
Index Cond: (tab1_id = 200)
-> Subquery Scan t2 (cost=0.00..2640.08 rows=104954 width=12)
-> Seq Scan on tab2 (cost=0.00..1590.54 rows=104954 width=8)
(6 rows)
/* Correct plain on tab2: */
explain
select t2.*
FROM tab1 t1 LEFT OUTER JOIN (select tab2.*
--, 1 as coluna
from tab2
) t2 on t1.tab1_id=t2.tab2_id
WHERE t1.tab1_id=200;
QUERY PLAN
Nested Loop Left Join (cost=0.00..12.03 rows=1 width=8)
-> Index Scan using pk_tab1_id on tab1 t1 (cost=0.00..6.01 rows=1
width=4)
Index Cond: (tab1_id = 200)
-> Index Scan using pk_tab2_id on tab2 (cost=0.00..6.01 rows=1
width=8)
Index Cond: ("outer".tab1_id = tab2.tab2_id)
(5 rows)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #2517: Trouble with cx_Oracle and Plpython
The following bug has been logged online:
Bug reference: 2517
Logged by: Sergey Konoplev
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.4
Operating system: SUSE LINUX Enterprise Server 9
Description:Trouble with cx_Oracle and Plpython
Details:
Good day.
Iâve got a trouble with cx_Oracle and Plpython (PostgreSQL).
The function DDL:
CREATE OR REPLACE FUNCTION "public"."function1" () RETURNS varchar AS
$body$
import cx_Oracle
connection = cx_Oracle.connect('xxx', 'xxx', 'xxx')
$body$
LANGUAGE 'plpythonu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
The function has executed successfully via psql interactive tool with local
transport.
pgdb:/ # /opt/PostgreSQL/bin/psql -U postgres -d transport
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
transport=# select * from function1();
function1
---
(1 row)
transport=#
But ORA-12154 error has been raised via same tool with remote transport.
pgdb:/ # /opt/PostgreSQL/bin/psql -U postgres -d transport -h
192.168.101.181
Password for user postgres:
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
transport=# select * from function1();
ERROR: plpython: function "function1" failed
DETAIL: cx_Oracle.DatabaseError: ORA-12154: TNS:could not resolve the
connect identifier specified
transport=#
How can I solve this problem?
Regards,
Konoplev Sergey <[EMAIL PROTECTED]>
Sen. software dveloper, IT department
Tander JSC
-
Levanevskogo st. 185, Krasnodar city, RUSSIA
Phone +7 861 210-98-10 (add.233)
Icq 29353802
---(end of broadcast)---
TIP 6: explain analyze is your friend
[BUGS] BUG #2514: (jdbc driver) Multiple inlined statements with mixed updates and queries return wrong results
The following bug has been logged online:
Bug reference: 2514
Logged by: Gilles
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.7
Operating system: linux
Description:(jdbc driver) Multiple inlined statements with mixed
updates and queries return wrong results
Details:
jdbc driver: 8.1.405
When issuing this:
statement.execute("SELECT 2;UPDATE foo SET d='babar' where i=1; SELECT 3");
The 2 result returned is wrong. This seems to be a ResultSet containing the
same values as the first RS (it should be an update count)
Here is code to reproduce:
stmt.executeUpdate("CREATE TABLE foo(i integer, d varchar(5))");
stmt.executeUpdate("INSERT INTO foo values(1,'bar')");
boolean isRS = stmt.execute("SELECT 2;UPDATE foo SET d='babar' where
i=1; SELECT 3");
assertTrue(isRS);
ResultSet rs = stmt.getResultSet();
assertTrue(rs.next());
assertEquals(rs.getInt(1), 2);
rs.close();
assertFalse(stmt.getMoreResults()); // <= This fails !
assertEquals(stmt.getUpdateCount(), 1);
assertTrue(stmt.getMoreResults());
rs = stmt.getResultSet();
assertTrue(rs.next());
assertEquals(rs.getInt(1), 3);
rs.close();
stmt.close();
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
