[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
[BUGS] BUG #8290: broken/unexpected locking behavior
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. 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. I'm using Postgres 8.4.13 I have two tables, call them A & B for example purposes. Table A, with column id Table B - foreign key reference a_id matches A.id FULL - some other columns blah1, blah2, blah3 I do this: db1: begin db2: begin db1: select A FOR UPDATE db2: update B set blah1 = 42; --- OK, UPDATE 1 db2: update B set blah2 = 42; --- This blocks waiting for a lock on A!! Here are the exact steps to reproduce: CREATE TABLE A (id bigint NOT NULL); CREATE TABLE B (id bigint NOT NULL, a_id bigint NOT NULL, blah1 bigint, blah2 bigint, blah3 bigint); ALTER TABLE ONLY A ADD CONSTRAINT a__pkey PRIMARY KEY (id); ALTER TABLE B ADD CONSTRAINT fkrefa FOREIGN KEY (a_id) REFERENCES A(id) MATCH FULL; INSERT INTO A VALUES (1); INSERT INTO B VALUES (1, 1, 1, 2, 3); Now, in two DB connections, CON1 and CON2. CON1: BEGIN; SELECT * FROM A WHERE id = 1 FOR UPDATE; CON2: BEGIN; UPDATE B SET blah1 = 42 WHERE id = 1; UPDATE B SET blah2 = 42 WHERE id = 1; -- this blocks I have verified that if I drop the foreign key constraint requiring B.a_id match A.id that this behaviour does not happen and both updates succeed without blocking. I can perhaps understand why it acquires a shared lock on A when updating B because of the foreign key reference, even though it doesn't seem like it should require it because the columns being updated are not relevant to the foreign key constraint. That behaviour would be less than ideal but at least it would be understandable. However, why does it only try to acquire the lock on the second update If I do a ROLLBACK in CON1, then I see CON2 finish the UPDATE and it acquires a lock on table A. Why? -- 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, 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 wrote: > > On Tue, Jul 9, 2013 at 9:02 AM, 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
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
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)
