Re: [BUGS] BUG #8434: Why does dead lock occur many times ?
Hi, Thanks to your info, otsuka-san. It seems to be related to some changes in the [Improve concurrency of foreign key locking] commit(*). (*) commitid : 0ac5ad5134f2769ccbaefec73844f8504c4d6182 Because I could not reproduce "dead lock" before the commit. And I'm thinking that also this commit is related to BUG#8470. If so, I hope Alvaro will find the solution for this problem. Maybe I can't take enough time, but I'll investigate this continuously. If someone could reproduce this, please share information. regards, --- NTT Software Corporation Tomonari Katsumata 2013/9/19 OTSUKA Kenji > Hi, > > This issue occurred even on 9.3.0. > I took the following information, and I attached them. > > pg_locks.txt ... pg_locks during deadlock > pg_stat_activity.txt ... pg_stat_activity during deadlock > postgresql.log... PostgreSQL log (including LOCK_DEBUG log) > > The OID of the table is 16459. > > I run 3 transactions. > One transaction of them executes SELECT FOR UPDATE and UPDATE a row. > And two transactions of them execute only UPDATE the same line. > > The results is that all of UPDATE is waiting. > > > I changed a little bit how to reproduce. > It is as follows. > > - Compiling PostgreSQL > Add -DLOCK_DEBUG to CFLAGS > > - Changing postgresql.conf following parameters > log_lock_waits = on > deadlock_timeout = 1min # for getting information during deadlock > debug_deadlocks = on > trace_lock_table = 16459 > > log_line_prefix = '%t [%p] %q(%a) ' > logging_collector = on > log_filename = 'postgresql.log' > log_min_messages = info > log_error_verbosity = verbose > > - Testing > 1. Initializing data > Executing createdb.sh > This creates a table with 2 columns, and insert 1 row. > > 2. Running the transactions > Executing test.sh > This runs 3 transactions. > > 2 transactions of them (tx1) are > BEGIN; > UPDATE t SET col2 = 'A' WHERE col1 = 1; > COMMIT; > > 1 transaction of them (tx2) is > BEGIN; > SELECT * FROM t WHERE col1 = 1 FOR UPDATE; > UPDATE t SET col2 = 'A' WHERE col1 = 1; > COMMIT; > > regards, > > > > 2013/9/4 > > The following bug has been logged on the website: >> >> Bug reference: 8434 >> Logged by: Tomonari Katsumata >> Email address: katsumata.tomon...@po.ntts.co.jp >> PostgreSQL version: 9.3rc1 >> Operating system: RedHatEnterpriseLinux 6.4(x86_64) >> Description: >> >> Hi, >> >> >> I'm testing PostgreSQL 9.3rc1. >> Many times updates and selects for update become dead lock situation. >> >> >> The reproduce is: >> 1. initializing data >> createdb testdb >> psql testdb -c "create table t (col1 int, col2 int, col3 text);" >> psql testdb -c "insert into t values (1, 4, 'A');" >> psql testdb -c "insert into t values (2, 5, 'B');" >> psql testdb -c "insert into t values (3, 6, 'C');" >> >> >> 2. executing updates and selects for update >> (run below script) >> >> #!/bin/sh >> >> >> ./tx1 > /dev/null & >> ./tx2 > /dev/null & >> ./tx3 > /dev/null & >> >> >> wait >> >> >> >> tx1 is: >> >> #!/bin/sh >> >> >> while : >> do >> psql testdb << EOF >> BEGIN; >> UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6; >> COMMIT; >> \q >> EOF >> done >> >> >> >> tx2 is: >> >> #!/bin/sh >> >> >> while : >> do >> psql testdb << EOF >> BEGIN; >> SELECT col1, col2, col3 FROM t WHERE col1 = 3 AND col2 = 6 FOR UPDATE; >> UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6; >> COMMIT; >> \q >> EOF >> done >> >> >> >> tx3 is: >> >> #!/bin/sh >> >> >> while : >> do >> psql testdb << EOF >> BEGIN; >> UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6; >> COMMIT; >> \q >> EOF >> done >> >> >> >> Then, I got below messages. >> >> 2013-09-04 15:25:25 JST 29630 5226d254.73be-1 659102 (pgsql, testdb, >> [local], psql) LOG: 0: process 29630 d
Re: [BUGS] BUG #7803: Replication Problem(no master is there)
Hi, (2013/01/16 4:03), Fujii Masao wrote: > On Tue, Jan 15, 2013 at 5:25 PM, Heikki Linnakangas > wrote: >> On 15.01.2013 10:14, Simon Riggs wrote: >>> >>> On 15 January 2013 05:12, Tomonari Katsumata >>> wrote: >>> >>>>> We added a REPLICATION privelge onto user accounts to control access. >>>>> >>>>> Perhaps we should add a CASCADE privilege as well, so that we can >>>>> control whether we can connect to a master and/or a standby. >>>>> >>>>> Syntax would be >>>>> >>>>> ALTER USER foo >>>>> [MASTER | CASCADE] REPLICATION >>>>> >>>>> REPLICATION allows both master and cascaded replication (same as now) >>>>> MASTER REPLICATION allows master only >>>>> CASCADE REPLICATION allows cascaded replication only >>>>> NOREPLICATION allows neither option >>>>> >>> >>>> Someone is working for it already ? >>>> If not yet, may I try to implement it ? >>> >>> >>> Please do. It looks fairly short. >> >> >> To me, permissions doesn't feel like the right vehicle for controlling this. >> Not sure what to suggest instead, a new GUC perhaps. >> If this is before releasing 9.2.0, it is not problem to have a new GUC. But 9.2 has released already. I'm thinking about this change is for 9.3, right ? And I'm thinking about compatibility when version up too. If we control this with permissions, I think it is easy to upgrade from 9.1 and 9.2 to 9.3 using pg_upgrade/pg_dumpall. Type of pg_authid.rolreplication is boolean. - 9.1 false(f=0) true(t=1) 9.2 false(f=0) true(t=1) - If I add permissions for cascading replication, it will become integer and represent each permission like this: - 9.3 noreplication(0) replication(1) master-only(2) cascade-only(3) - If pg_upgrade/pg_dumpall handle like bellow, user would never mind about difference between versions. from 9.1 to 9.3. false(f=0) --> noreplication(0) true(t=1) --> master-only(2) from 9.2 to 9.3. false(f=0) --> noreplication(0) true(t=1) --> replication(1) >> BTW, is there any reason to not allow streaming replication when >> hot_standby=off? A streaming replication connection doesn't execute any >> queries, so it doesn't need the system to be consistent. > > I was thinking that the system must be consistent since streaming replication > connection reads the system catalog (e.g., ROLE information). > And I think it's because replication connection is established by same way with another backend connection. >> Another thing to consider is that "pg_basebackup -X stream" also uses >> streaming replication, so if you forbid cascade replication, you also forbid >> using "pg_basebackup -X stream" on the standby. At the protocol level, >> pg_basebackup streams the WAL just like a standby server does, so we cannot >> distinguish those two cases in the server. The client could tell the server >> which one it is, but using permissions to allow/forbid based on that would >> make no sense as the client could lie which one it is. > > Probably I'm missing something, but the standby server only has to reject the > replication connection if cascade replication is disabled, whether it's from > another standby or pg_basebackup. ISTM there is no need to distinguish > those connections. No? > > When "pg_basebackup -X stream" fails to establish the replication connection, > it only has to just fail or automatically switch to "pg_basebackup -X fetch". > I think so too. If user who does not have right permission executes "pg_basebackup -X stream", it would make fail. regards, NTT Software Corporation Tomonari Katsumata -- 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 #7803: Replication Problem(no master is there)
Hi,
Sorry for late response.
(2013/01/11 23:11), Simon Riggs wrote:
> On 11 January 2013 12:18, Tomonari Katsumata
> wrote:
>
>> I'm not sure but what about adding the parameter("cascade_mode") on
>> recovery.conf ?
>> The parameter represents a will to connect to standby server when
starting
>> as standby.
>> If the parameter is set to on, connect to a server forcely like
PostgreSQL
>> 9.2,
>> and if the parameter is set to off, connect to the another standby
server is
>> refused like PostgreSQL 9.1.
>
> We added a REPLICATION privelge onto user accounts to control access.
>
> Perhaps we should add a CASCADE privilege as well, so that we can
> control whether we can connect to a master and/or a standby.
>
> Syntax would be
>
> ALTER USER foo
> [MASTER | CASCADE] REPLICATION
>
> REPLICATION allows both master and cascaded replication (same as now)
> MASTER REPLICATION allows master only
> CASCADE REPLICATION allows cascaded replication only
> NOREPLICATION allows neither option
>
This idea seems better than mine.
Someone is working for it already ?
If not yet, may I try to implement it ?
regards,
NTT Software Corporation
Tomonari Katsumata
--
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 #7803: Replication Problem(no master is there)
Hi,
hi, I'm playing with Synchronous Replication on PostgreSQL 9.2.2.
And I saw a strange behavior.
Unless you left out something, the configuration you described
actually sets up asynchronous replication.
Thank you for the comment.
I was thinking to promote one of them and set
synchronous_standby_names = 'sby' and reload configure file.
Before that, I noticed the issue.
=
[issues]
two standbys are connected on each other, but
no master is there.
...
=
I did not see the situation like above on PostgreSQL 9.1.7.
Is this intended change?
In 9.1, this scenario was impossible because you could not connect a
standby to another standby. In 9.2, that's allowed. It's a new feature
called "cascading replication", see
http://www.postgresql.org/docs/9.2/static/warm-standby.html#CASCADING-REPLICATION.
With that feature, it's indeed possible to form a cycle of standby
servers connected to each other. There was just a long discussion on
pgsql-hackers on whether we should try to detect that scenario [1],
but the consensus seems to be that we should not. It would be
difficult to implement such detection, and sometimes it's useful to
have such a cycle, as a transient state at a failover, for example.
Sorry, I had not read the discussion and have reported it again.
And I understand it's hard to detect cyclic situation.
So the bottom line is that this is an intended change, and the admin
will just have to avoid doing that.
OK.
This situation was made by playing, so it's not big problem for now.
This makes me wonder if there should be a GUC to forbid cascading
replication, though. If you don't want to do cascading replication
(which is quite rare, I'd say), you could just disable it to avoid a
situation like this.
I'm not sure but what about adding the parameter("cascade_mode") on
recovery.conf ?
The parameter represents a will to connect to standby server when
starting as standby.
If the parameter is set to on, connect to a server forcely like
PostgreSQL 9.2,
and if the parameter is set to off, connect to the another standby server is
refused like PostgreSQL 9.1.
[1] http://archives.postgresql.org/pgsql-hackers/2012-12/msg01134.php
- Heikki
regards,
NTT Software Corporation
Tomonari Katsumata
--
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 #6676: checkpointer does not work by SIGINT.
Hi, Thank you for explanation about it. I've understood that it is intended thing. Sorry to bother you. regards, (2012/06/07 2:05), Tom Lane wrote: > katsumata.tomon...@po.ntts.co.jp writes: >> Now I'm testing the behavior of checkpointer, >> and I found a difference with PostgreSQL9.1 behavior. >> When I send SIGINT signal to writer process on PostgreSQL9.1, >> writer process starts checkpoint. >> But, when I send SIGINT signal to checkpointer on PostgreSQL9.2beta2, >> checkpointer doesn't start checkpoint. > AFAICT, the checkpointer does consider starting a checkpoint, but it > decides not to do one, because of this recent change: > http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=18fb9d8d21a28caddb72c7ffbdd7b96d52ff9724 > > If you had done enough updates since the last checkpoint, it would do > one. So this isn't a bug, but an intentional reduction in checkpoint > frequency. (Note that sending SIGINT is not, and never has been, > equivalent to forcing a checkpoint.) > > 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 #6643: [PostgreSQL9.2beta1] COPY after changing fillfactor gets a PANIC.
Hi, Heikki I'm sorry, forgotten attach files. I've tryed to send mail with files, but I could not... (I think this is my mail server problem.) Thank you very much for fixing it! (2012/05/16 20:14), Heikki Linnakangas wrote: On 16.05.2012 13:47, Heikki Linnakangas wrote: This sounds like a bug in the new page-at-a-time behavior in COPY. Can you send me a self-contained test, including the test data? Never mind. After staring at the code for a while, I spotted the bug, and was able to reproduce with a simpler case. It's quite easy to reproduce when you set fillfactor even lower, like 10. The problem is with this line in heap_multi_insert function: if (PageGetHeapFreeSpace(page) - saveFreeSpace < MAXALIGN(heaptup->t_len)) That doesn't work as intended, because the return value of PageGetHeapFreeSpace and saveFreeSpace are unsigned. When saveFreeSpace is larger than the amount of free space on the page, the left hand side of that comparison is supposed to go negative, but it wraps around to a highly positive number because it's unsigned. Fixed, thanks for the report! -- 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 #6042: unlogged table with Streaming Replication
Hi, Jaime thank you for your answer. I understand it. I turned synchronous_commit to "local", I get desirable behavior. I've thought that if there are no standby, the primary would behave like stand-alone... sorry, this is my misunderstanding. regards, (2011/05/27 14:53), Jaime Casanova wrote: On Fri, May 27, 2011 at 12:26 AM, Tomonari Katsumata wrote: I've checked "unlogged table" and "Streaming Replication". I'm thinking about using unlogged tables as work-tables on Primary. 1) construct Streaming Replication Environment. Primary and Standby are same server with different database cluster and port number. 2) create unlogged table on Primary. =# CREATE UNLOGGED TABLE tbl1(i int); This table is available on primary only. because streaming replication works shipping WAL records (the records of the transactional log) to the standby but because UNLOGGED tables are not logged to WAL i guess those always will be empty on standby, but the table should appear on the standby, i guess 4) create unlogged table on Primary again. =# CREATE UNLOGGED TABLE tbl2(i int); when I executed 4), any response is not back to my psql. and I canceled the query, I got messages bellow. --- Cancel request sent WARNING: canceling wait for synchronous replication due to user request DETAIL: The transaction has already committed locally, but may not have been replicated to the standby. CREATE TABLE --- and the table has been created. I think It's strange behavior(a canceled table has been created). actually, you're not cancelling the creation... the table has been created and the wal is being sent to the standby (because the standby is a synchronous standby, it keeps waiting until the standby aknlowdge to have received the wal), so what you are cancelling now is the primary waiting for the standby... btw, i guess we should be defaulting to asynchronous standbys (ie: synchronous_commit=local) -- 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 #6042: unlogged table with Streaming Replication
The following bug has been logged online: Bug reference: 6042 Logged by: Tomonari Katsumata Email address: katsumata.tomon...@po.ntts.co.jp PostgreSQL version: 9.1beta1 Operating system: RHEL5.3 x86_64 Description:unlogged table with Streaming Replication Details: Hi, I've checked "unlogged table" and "Streaming Replication". I'm thinking about using unlogged tables as work-tables on Primary. 1) construct Streaming Replication Environment. Primary and Standby are same server with different database cluster and port number. 2) create unlogged table on Primary. =# CREATE UNLOGGED TABLE tbl1(i int); This table is available on primary only. 3) Stop Standby. [standby]$ pg_ctl stop 4) create unlogged table on Primary again. =# CREATE UNLOGGED TABLE tbl2(i int); when I executed 4), any response is not back to my psql. and I canceled the query, I got messages bellow. --- Cancel request sent WARNING: canceling wait for synchronous replication due to user request DETAIL: The transaction has already committed locally, but may not have been replicated to the standby. CREATE TABLE --- and the table has been created. I think It's strange behavior(a canceled table has been created). Is this the same problem with "BUG #6041"? regards, Tomonari Katsumata -- 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 #5064: not-null constraints is not inherited
The following bug has been logged online: Bug reference: 5064 Logged by: Tomonari Katsumata Email address: katsumata.tomon...@oss.ntt.co.jp PostgreSQL version: 8.5dev, 8.4.1 Operating system: Windows XP, RHEL 5.2 Description:not-null constraints is not inherited Details: Hi, I found a bug about inheritance. The user manual says, "All check constraints and not-null constraints on a parent table are automatically inherited by its children." But, the behavier is not true. The sample is like below. postgres=# CREATE TABLE parent (id integer); postgres=# CREATE TABLE child () inherits (parent); postgres=# ALTER TABLE parent ADD PRIMARY KEY (id); postgres=# \d+ parent Table "public.parent" Column | Type | Modifiers | Storage | Description +-+---+-+- id | integer | not null | plain | Indexes: "parent_pkey" PRIMARY KEY, btree (id) Child tables: child Has OIDs: no postgres=# \d+ child Table "public.child" Column | Type | Modifiers | Storage | Description +-+---+-+- id | integer | | plain | Inherits: parent Has OIDs: no child table does not have a "not null" constraints. I think it's not desirable behavier. On the other hand, if I set a constraints using "ALTER COLUMN SET NOT NULL", the constraints is inherited by its child. postgres=# DROP TABLE parent, child; postgres=# CREATE TABLE parent (id integer); postgres=# CREATE TABLE child () inherits (parent); postgres=# ALTER TABLE parent ALTER COLUMN id SET NOT NULL; postgres=# \d+ parent Table "public.parent" Column | Type | Modifiers | Storage | Description +-+---+-+- id | integer | not null | plain | Child tables: child Has OIDs: no postgres=# \d+ child Table "public.child" Column | Type | Modifiers | Storage | Description +-+---+-+- id | integer | not null | plain | Inherits: parent Has OIDs: no regards, -- Tomonari Katsumata katsumata.tomon...@oss.ntt.co.jp -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
