[BUGS] BUG #8288: 9.3.0Beta2 - Windows Installer bug #7738 still open
The following bug has been logged on the website: Bug reference: 8288 Logged by: Thomas Gauss Email address: tga...@wolfsysteme.de PostgreSQL version: Unsupported/Unknown Operating system: Windows 7 64bit Description: Having data directory on drive d:\database\data-9.3 it takes about 45 minutes to initialise cluster. Most of the time icacls is run by the installer, seems like bug#7738 is still active in 9.3.0b2's installer. -- 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 #8289: pg_stat_statements incorrect query text when executing multiple queries in a single PQexec
The following bug has been logged on the website: Bug reference: 8289 Logged by: Dmitriy Potapov Email address: potapov.dmi...@gmail.com PostgreSQL version: 9.2.4 Operating system: Scientific Linux 6.3 Description: pg_stat_statements view contains incorrectly normalized query texts when executing multiple queries in a single PQexec call. How to reproduce: 1. Create a database. 2. Populate it with pss_mq_testcase.sql http://pgsql.privatepaste.com/e834c641d6 3. Compile pss_mq.c http://pgsql.privatepaste.com/88421cfdf7 with following command: gcc -o pss_mq -I`/usr/pgsql-9.2/bin/pg_config --includedir` -L `/usr/pgsql-9.2/bin/pg_config --libdir` -lpq pss_mq.c 4. Execute select pg_stat_statements_reset(); 5. Run pss_mq with connection string to database as command line parameter (for example: ./pss_mq 'dbname=psstest port=5433') 6. Check pg_stat_statements view. I get the following results: http://pgsql.privatepaste.com/6908db7e80 The testcase runs five UPDATE commands with two PQexec calls. First PQexec call runs: UPDATE t1 SET a=101 WHERE id=0; UPDATE t1 SET a=102 WHERE id=1; UPDATE t1 SET a=103 WHERE id=2 Second PQexec call runs: UPDATE t2 SET b=1 where a=0; UPDATE t2 SET a=2 WHERE b=3 I expect pg_stat_statements to contain three records with following query texts: 1) UPDATE t1 SET a=? WHERE id=? 2) UPDATE t2 SET b=? where a=? 3) UPDATE t2 SET a=? WHERE b=? Instead it contains three records with following query texts: 1) UPDATE t2 SET b=1 where a=0; UPDATE t2 SET a=? WHERE b=? 2) UPDATE t1 SET a=? WHERE id=?; UPDATE t1 SET a=102 WHERE id=1; UPDATE t1 SET a=103 WHERE id=2 3) UPDATE t2 SET b=? where a=?; UPDATE t2 SET a=2 WHERE b=3 PostgreSQL version: PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit OS version: Scientific Linux release 6.3 (Carbon) PostgreSQL packages (from pgdg yum repo): postgresql92-9.2.4-1PGDG.rhel6.x86_64, postgresql92-contrib-9.2.4-1PGDG.rhel6.x86_64, postgresql92-libs-9.2.4-1PGDG.rhel6.x86_64, postgresql92-server-9.2.4-1PGDG.rhel6.x86_64, postgresql92-devel-9.2.4-1PGDG.rhel6.x86_64 Testcase archive can be downloaded here: http://yadi.sk/d/rRcN9GTO6e7Me This bug was discovered when I checked pg_stat_statements on a database of zabbix monitoring system. -- 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 #8290: broken/unexpected locking behavior
On Tue, Jul 9, 2013 at 9:02 AM, pgn...@gmail.com wrote: The following bug has been logged on the website: Bug reference: 8290 Logged by: pgnoob Email address: pgn...@gmail.com PostgreSQL version: 8.4.13 Operating system: CentOS Linux Description: I experienced a db deadlock. After tracking down the problem I attributed it to some unusual locking behavior in postgresql where it acquires locks in an unexpected way that contributed to the deadlock. ALTER TABLE B ADD CONSTRAINT fkrefa FOREIGN KEY (a_id) REFERENCES A(id) MATCH FULL; Try those steps again with the FK DEFERRABLE INITIALLY DEFERRED Eg. ALTER TABLE B ADD CONSTRAINT fkrefa FOREIGN KEY (a_id) REFERENCES A(id) MATCH FULL deferrable initially deferred;
Re: [BUGS] BUG #8290: broken/unexpected locking behavior
Doing that just moves the problem from the time of the UPDATE to the time of the COMMIT. It is still possible to get a deadlock and I'm not sure how making it deferrable helps in this case. You can still end up with a deadlock like this: CON1: BEGIN; CON1: SELECT * FROM A WHERE id = 1 FOR UPDATE; CON2: BEGIN; CON2: UPDATE B SET blah1 = 42 WHERE id = 1; -- OK, UPDATE1 CON1: UPDATE B SET blah3 = 42 WHERE id = 1; -- blocks because of the transaction in CON2 CON2: UPDATE B SET blah2 = 42 WHERE id = 1; -- OK, UPDATE1 CON2: COMMIT; -- causes deadlock ERROR: deadlock detected On Tue, Jul 9, 2013 at 12:57 PM, bricklen brick...@gmail.com wrote: On Tue, Jul 9, 2013 at 9:02 AM, pgn...@gmail.com wrote: The following bug has been logged on the website: Bug reference: 8290 Logged by: pgnoob Email address: pgn...@gmail.com PostgreSQL version: 8.4.13 Operating system: CentOS Linux Description: I experienced a db deadlock. After tracking down the problem I attributed it to some unusual locking behavior in postgresql where it acquires locks in an unexpected way that contributed to the deadlock. ALTER TABLE B ADD CONSTRAINT fkrefa FOREIGN KEY (a_id) REFERENCES A(id) MATCH FULL; Try those steps again with the FK DEFERRABLE INITIALLY DEFERRED Eg. ALTER TABLE B ADD CONSTRAINT fkrefa FOREIGN KEY (a_id) REFERENCES A(id) MATCH FULL deferrable initially deferred;
Re: [BUGS] BUG #8290: broken/unexpected locking behavior
Thank you Alvaro. Yes, this explains it. It doesn't help to fix it but at least I know now that it's a known feature. I'll have to see about coming up with a work-around as we likely won't get to 9.3 anytime soon. Thank you! -Original Message- From: pgsql-bugs-ow...@postgresql.org [mailto:pgsql-bugs-ow...@postgresql.org] On Behalf Of Alvaro Herrera Sent: Tuesday, July 09, 2013 2:59 PM To: pgn...@gmail.com Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #8290: broken/unexpected locking behavior pgn...@gmail.com escribió: I sent the following information to pgsql-general to ask if it is expected locking behavior. The only responses that I got said that the behavior is reproducible on 9.1 and 9.3 beta 2. Nobody said that this is expected locking behavior and I believe it to be a bug, so I am filing this bug report. The exact steps on how to reproduce the problem are shown below. Thank you for putting together a great DB and for working on this bug report. See here: http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/ There are further posts on the same topic in that blog. The patch dealing with it was finally committed for the 9.3 version, due to be released later this year: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0ac5ad5134f2769ccbaefec73844f8504c4d6182 At the bottom of the commit messages there are some message-ids on (rather long) discussions about that patch. You can search for them at http://www.postgresql.org/list/ (just enter the msgid in the box and click search). If you try a 9.3 snapshot, you should be able to replace the FOR UPDATE in your queries with FOR NO KEY UPDATE and there should be no deadlock. Even if it does not, I hope the aforementioned posts explain what is going on. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- 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 #8289: pg_stat_statements incorrect query text when executing multiple queries in a single PQexec
potapov.dmi...@gmail.com writes: The testcase runs five UPDATE commands with two PQexec calls. First PQexec call runs: UPDATE t1 SET a=101 WHERE id=0; UPDATE t1 SET a=102 WHERE id=1; UPDATE t1 SET a=103 WHERE id=2 Second PQexec call runs: UPDATE t2 SET b=1 where a=0; UPDATE t2 SET a=2 WHERE b=3 I expect pg_stat_statements to contain three records with following query texts: 1) UPDATE t1 SET a=? WHERE id=? 2) UPDATE t2 SET b=? where a=? 3) UPDATE t2 SET a=? WHERE b=? Instead it contains three records with following query texts: 1) UPDATE t2 SET b=1 where a=0; UPDATE t2 SET a=? WHERE b=? 2) UPDATE t1 SET a=? WHERE id=?; UPDATE t1 SET a=102 WHERE id=1; UPDATE t1 SET a=103 WHERE id=2 3) UPDATE t2 SET b=? where a=?; UPDATE t2 SET a=2 WHERE b=3 Yeah, that's what's going to happen, because there is no infrastructure for determining which portion of the source text string belongs to which query. I suspect there are some other infelicities in pg_stat_statements' behavior for multi-query strings, too. At least for now, that combination is best avoided. 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] BUG #8290: broken/unexpected locking behavior
Jamey Poirier escribió: Thank you Alvaro. Yes, this explains it. It doesn't help to fix it but at least I know now that it's a known feature. I'll have to see about coming up with a work-around as we likely won't get to 9.3 anytime soon. Perhaps you can use FOR SHARE instead of FOR UPDATE in the first connection, for instance .. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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 #8291: postgres_fdw does not re-read USER MAPING after change.
The following bug has been logged on the website:
Bug reference: 8291
Logged by: Lloyd Albin
Email address: lal...@fhcrc.org
PostgreSQL version: Unsupported/Unknown
Operating system: Windows 7 (64-bit)
Description:
Tested on
Windows 7 (64-bit) Postgres 9.3.0 Beta 2 - Windows 7 (64-bit) Postgres
9.3.0 Beta 2
Windows 7 (64-bit) Postgres 9.3.0 Beta 1 - SUSE Linux (64-bit) Postgres
9.0
I have found that if you change the password in the USER MAPPING, that
postgres_fdw will not use it unless the current password fails or you close
and re-open your postgres connection. I found this while testing to see if
the USER MAPPING's supports MD5 passwords and they appeared to until the
next day when I found that they no longer worked because I had closed and
re-opened my connection.
The second error that I found is in the documentation of ALTER USER MAPPING.
It incorrectly says how to update a users password.
CREATE DATABASE db1
WITH ENCODING='UTF8'
OWNER=postgres
CONNECTION LIMIT=-1;
CREATE DATABASE db2
WITH ENCODING='UTF8'
OWNER=postgres
CONNECTION LIMIT=-1;
-- LOG INTO db1
CREATE TABLE public.tbl_test
(
field character varying,
CONSTRAINT tbl_test_field_pkey PRIMARY KEY (field)
)
WITH (
OIDS = FALSE
)
;
ALTER TABLE public.tbl_test
OWNER TO postgres;
INSERT INTO public.tbl_test VALUES('Test Value');
-- LOG INTO db2
CREATE EXTENSION postgres_fdw;
CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
'localhost', dbname 'db1', port '5432');
CREATE USER MAPPING FOR postgres SERVER myserver OPTIONS (user 'postgres',
password 'password');
CREATE FOREIGN TABLE tbl_test (
field character varying
)
SERVER myserver;
SELECT * FROM tbl_test;
-- This works, we should see the 'Test Value' returned.
ALTER USER MAPPING FOR postgres SERVER myserver OPTIONS (user 'postgres',
password 'badpass');
ERROR: option user provided more than once
** Error **
ERROR: option user provided more than once
SQL state: 42710
-- http://www.postgresql.org/docs/9.3/static/sql-alterusermapping.html
-- Documentation is not correct, needs to be updated to show updating user
password as:
ALTER USER MAPPING FOR postgres SERVER myserver OPTIONS (SET password
'badpass');
SELECT * FROM pg_catalog.pg_user_mapping;
-- Verified that password was properly changed.
SELECT * FROM tbl_test;
Total query runtime: 1970 ms.
1 row retrieved.
-- This should have failed due to the bad password.
-- If you log out of the database and then reconnect, the query will then
fail.
Lloyd Albin
Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Vaccine and Infectious Disease Division (VIDD)
Fred Hutchinson Cancer Research Center (FHCRC)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Fwd: plpgsql plan caching allowing invalid data to enter table?
It's looking like I can use a plpgsql function to insert data into a table
that violates a domain constraint. Is this a known problem?
Session 1:
create domain my_domain text check (length(value) 2);
create table my_table (name my_domain);
create function f(text) returns void as $$
declare my_var my_domain := $1;
begin
insert into my_table values (my_var);
end $$ language plpgsql;
Session 2:
select f('test');
delete from my_table;
-- Keep session open!
Session 1:
alter domain my_domain drop constraint my_domain_check;
alter domain my_domain add constraint my_domain_check check (length(value)
5);
Session 2:
select f('test');
-- This works, but it should fail.
-- I have a constraint of more than 5 characters on the domain.
-- But I can insert a row with 4 characters.
As you can see below, I have data in my_table that's violating the domain's
constraint of being longer than 5 characters.
# select * from my_table;
name
──
test
(1 row)
# \d+ my_table
Table public.my_table
Column │ Type│ Modifiers │ Storage │ Stats target │ Description
┼───┼───┼──┼──┼─
name │ my_domain │ │ extended │ │
Has OIDs: no
# \dD my_domain
List of domains
Schema │ Name│ Type │ Modifier │ Check
┼───┼──┼──┼───
public │ my_domain │ text │ │ CHECK (length(VALUE) 5)
(1 row)
