Re: [HACKERS] Partitioning vs ON CONFLICT
Hello, I tried this feature using most recently snapshot. In case of added constraint PRIMARY KEY for partition table, INSERT ON CONFLICT DO NOTHING statement failed with segmentaion fault. If the primary key constraint was not created on the partition, this statement executed successfully. - Test postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY RANGE (c1) ; CREATE TABLE postgres=> CREATE TABLE part1p1 PARTITION OF part1 FOR VALUES FROM (100) TO (200) ; CREATE TABLE postgres=> ALTER TABLE part1p1 ADD CONSTRAINT pk_part1p1 PRIMARY KEY (c1) ; ALTER TABLE postgres=> INSERT INTO part1 VALUES (100, 'init') ON CONFLICT DO NOTHING ; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !> \q - Part of data/log/postgresql.log file 2017-03-30 10:20:09.161 JST [12323] LOG: server process (PID 12337) was terminated by signal 11: Segmentation fault 2017-03-30 10:20:09.161 JST [12323] DETAIL: Failed process was running: INSERT INTO part1 VALUES (100, 'init') ON CONFLICT DO NOTHING ; 2017-03-30 10:20:09.161 JST [12323] LOG: terminating any other active server processes 2017-03-30 10:20:09.163 JST [12345] FATAL: the database system is in recovery mode 2017-03-30 10:20:09.164 JST [12329] WARNING: terminating connection because of crash of another server process 2017-03-30 10:20:09.164 JST [12329] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. - Environment OS: Red Hat Enterprise Linux 7 Update 1 (x86-64) Snapshot: 2017-03-29 20:30:05 with default configure. Best Regards, -- Noriyoshi Shinoda -Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Amit Langote Sent: Tuesday, March 28, 2017 9:56 AM To: Robert Haas Cc: Peter Geoghegan ; Simon Riggs ; PostgreSQL Hackers ; Thom Brown Subject: Re: [HACKERS] Partitioning vs ON CONFLICT On 2017/03/27 23:40, Robert Haas wrote: > On Thu, Mar 9, 2017 at 7:20 PM, Amit Langote > wrote: >> On 2017/03/10 9:10, Amit Langote wrote: >>> On 2017/03/09 23:25, Robert Haas wrote: On Fri, Feb 17, 2017 at 1:47 AM, Amit Langote wrote: > I updated the patch. Now it's reduced to simply removing the > check in > transformInsertStmt() that prevented using *any* ON CONFLICT on > partitioned tables at all. This patch no longer applies. >>> >>> Rebased patch is attached. >> >> Oops, really attached this time, > > Committed with a bit of wordsmithing of the documentation. Thanks. Regards, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Logical Replication and Character encoding
>From: Peter Eisentraut [mailto:peter.eisentr...@2ndquadrant.com] >Sent: Friday, February 24, 2017 1:32 AM >To: Petr Jelinek ; Kyotaro HORIGUCHI > >Cc: cr...@2ndquadrant.com; Shinoda, Noriyoshi ; >pgsql-hackers@postgresql.org >Subject: Re: [HACKERS] Logical Replication and Character encoding > >On 2/17/17 10:14, Peter Eisentraut wrote: >> Well, it is sort of a libpq connection, and a proper libpq client >> should set the client encoding, and a proper libpq server should do >> encoding conversion accordingly. If we just play along with this, it >> all works correctly. >> >> Other output plugins are free to ignore the encoding settings (just >> like libpq can send binary data in some cases). >> >> The attached patch puts it all together. > >committed Hi, Thank you very much for making a new patch. I tried a new committed version. In the case of PUBLICATION(EUC_JP) and SUBSCRIPTION(UTF-8) environment, it worked as expected. Great!. However, in the case of PUBLICATION(UTF-8) and SUBSCRIOTION(EUC_JP) environment, the following error was output and the process went down. - PUBLICATION (UTF-8) postgres=> INSERT INTO encode1 VALUES (1, 'ascii') ; INSERT 0 1 postgres=> INSERT INTO encode1 VALUES (2, '漢') ; -- Expect UTF-8 Character 0xE6BCA2 will be convert EUC_JP 0xB4C1 INSERT 0 1 - SUBSCRIPTION (EUC_JP) postgres=> SELECT * FROM encode1; c1 | c2 +--- 1 | ascii (1 row) $ tail data.euc/pg_log/postgresql.log LOG: starting logical replication worker for subscription "sub1" LOG: logical replication apply for subscription "sub1" has started ERROR: insufficient data left in message LOG: worker process: logical replication worker for subscription 16439 (PID 22583) exited with exit code 1 Snapshot: https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.gz 2017-02-24 00:28:58 Operating System: Red Hat Enterprise Linux 7 Update 2 (x86-64) Regards. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_sequences bug ?
Hi hackers, I tried a committed pg_sequences for PostgreSQL 10dev (https://commitfest.postgresql.org/12/771/). I found that when multiple users create SEQUENCE, I cannot see the pg_sequences catalog. I think that should work just like pg_tables. $ psql -U user1 postgres=> CREATE SEQUENCE seq1 ; CREATE SEQUENCE $ psql -U user2 postgres=> CREATE SEQUENCE seq2 ; CREATE SEQUENCE postgres=> SELECT * FROM pg_sequences ; ERROR: permission denied for sequence seq1 Apparently it seems that the pg_sequence_last_value function included in the pg_sequences view definition cannot be executed. Is this behavior supposed? Snapshot: https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.gz 2017-02-04 00:29:04 Operating System: Red Hat Enterprise Linux 7 Update 2 (x86-64) Regards. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Logical Replication and Character encoding
Thank you for creating patches. I strongly hope that your patch will be merged into the new version. Since all databases are not yet based on UTF - 8, I think conversion of character codes is still necessary. -Original Message- From: Kyotaro HORIGUCHI [mailto:horiguchi.kyot...@lab.ntt.co.jp] Sent: Wednesday, February 01, 2017 3:31 PM To: Shinoda, Noriyoshi Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Logical Replication and Character encoding At Wed, 01 Feb 2017 12:13:04 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20170201.121304.267734380.horiguchi.kyot...@lab.ntt.co.jp> > > > I tried a committed Logical Replication environment. I found > > > that replication between databases of different encodings did not > > > convert encodings in character type columns. Is this behavior > > > correct? > > > > The output plugin for subscription is pgoutput and it currently > > doesn't consider encoding but would easiliy be added if desired > > encoding is informed. > > > > The easiest (but somewhat seems fragile) way I can guess is, > > > > - Subscriber connects with client_encoding specification and the > > output plugin pgoutput decide whether it accepts the encoding > > or not. If the subscriber doesn't, pgoutput send data without > > conversion. > > > > The attached small patch does this and works with the following > > CREATE SUBSCRIPTION. > > Oops. It forgets to care conversion failure. It is amended in the > attached patch. > > > CREATE SUBSCRIPTION sub1 CONNECTION 'host=/tmp port=5432 > > dbname=postgres client_encoding=EUC_JP' PUBLICATION pub1; > > > > > > Also we may have explicit negotiation on, for example, > > CREATE_REPLICATION_SLOT. > > > > 'CREATE_REPLICATION_SLOT sub1 LOGICAL pgoutput ENCODING EUC_JP' > > > > Or output plugin may take options. > > > > 'CREATE_REPLICATION_SLOT sub1 LOGICAL pgoutput OPTIONS(encoding EUC_JP)' > > > > > > Any opinions? This patch chokes replication when the publisher finds an inconvertible character in a tuple to be sent. For the case, dropping-then-recreating subscription is necessary to go forward. regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Logical Replication and Character encoding
Hi hackers,
I tried a committed Logical Replication environment. I found that replication
between databases of different encodings did not convert encodings in character
type columns. Is this behavior correct?
I expected that the character 0xe6bca2 (UTF-8) would be converted to the same
character 0xb4c1 (EUC_JP). The example below replicates from the encoding UTF-8
database to the encoding EUC_JP database. You can see that the character
0xe6bca2 (UTF-8) is stored intact in the SUBSCRIPTION side database.
- PUBLICATION side (encode=UTF-8)
postgres=> \l postgres
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--+--+--+-+---+---
postgres | postgres | UTF8 | C | C |
(1 row)
postgres=> CREATE TABLE encode1(col1 NUMERIC PRIMARY KEY, col2 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE PUBLICATION pub1 FOR TABLE encode1 ;
CREATE PUBLICATION
postgres=> INSERT INTO encode1 VALUES (1, '漢') ; -- UTF-8 Character 0xe6bca2
INSERT 0 1
- SUBSCRIPTION side (encode=EUC_JP)
postgres=> \l postgres
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--+--+--+-+---+---
postgres | postgres | EUC_JP | C | C |
(1 row)
postgres=> CREATE TABLE encode1(col1 NUMERIC PRIMARY KEY, col2 VARCHAR(10)) ;
CREATE TABLE
postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=postgres host=localhost
port=5432' PUBLICATION pub1 ;
NOTICE: created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
postgres=# SELECT * FROM encode1 ;
ERROR: invalid byte sequence for encoding "EUC_JP": 0xa2
postgres=# SELECT heap_page_items(get_raw_page('encode1', 0)) ;
heap_page_items
---
(1,8152,1,33,565,0,0,"(0,1)",2,2306,24,,,"\\x0b0080010009e6bca2") <-
stored UTF-8 char 0xe6bca2
(1 row)
Snapshot:
https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.gz
2017-01-31 00:29:07
Operating System:
Red Hat Enterprise Linux 7 Update 2 (x86-64)
Regards.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
