Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

[Steven Chang]

Dear Michael,

I know what you mean. We also mail the issue to EDB.
   Post here is just to reply Timokhin's case and see could anyone give a
solution.
   EDB's strength is just a orafce moudule enhancement to me,
and I don't think they could adapt a lot of kernel module codes.

Regards,
Steven

2017-07-03 9:18 GMT+08:00 Michael Paquier :

> On Mon, Jul 3, 2017 at 10:08 AM, Steven Chang 
> wrote:
> > Hello :
>
> Please avoid top-posting.
>
> >PG  VERSION : PPAS 9.3 , enterprisedb
> >os   version :  2.6.32-358.el6.x86_64
>
> This is EnterpriseDB's fork of Postgres. Until it can be proved that a
> corruption has happened using the community code, it is going to be
> hard to say if the problem comes from PostgreSQL itself or from
> something that has been changed there.
> --
> Michael
>

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

[Michael Paquier]

On Mon, Jul 3, 2017 at 10:08 AM, Steven Chang  wrote:
> Hello :

Please avoid top-posting.

>PG  VERSION : PPAS 9.3 , enterprisedb
>os   version :  2.6.32-358.el6.x86_64

This is EnterpriseDB's fork of Postgres. Until it can be proved that a
corruption has happened using the community code, it is going to be
hard to say if the problem comes from PostgreSQL itself or from
something that has been changed there.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

[Steven Chang]

Hello :

   PG  VERSION : PPAS 9.3 , enterprisedb
   os   version :  2.6.32-358.el6.x86_64


  pg_basebackup job was not performed by me. But I think it was executed
regularly.
  Any switch or parameter would cause this issue ???

   Why I don't think not a index curruption issue ?
  1. I found this document :

https://www.postgresql.org/message-id/20140811083748.2536.10437%40wrigleys.postgresql.org
   2. Return only on row if  query data using where equal condition, but
got 2 rows by like  condition

Steven

2017-07-01 22:05 GMT+08:00 Adrian Klaver :

> On 06/30/2017 09:42 PM, Steven Chang wrote:
>
>> Uh...we also met duplicate rows with primary key column through
>>  restoring database by pg_basebackup.
>> H.
>> I don't think its an issue with primary key index corruption.
>>
>
> That is interesting, more information would be helpful though:
>
> Postgres version?
>
> OS and version?
>
> The pg_basebackup command line invocation?
>
> Why you don't think it is index corruption?
>
>
>
>>
>>
>> 2017-07-01 7:30 GMT+08:00 Adrian Klaver > >:
>>
>> On 06/30/2017 07:33 AM, Timokhin Maxim wrote:
>>
>> Sure, here it is.
>>
>> pg_basebackup -h servername -R -P -D /data/upgrade/94 -U pgsql
>> -v —xlog-method=stream —checkpoint=fast
>>
>> /usr/pgsql-9.5/bin/initdb -D /data/upgrade/95/ —encoding=utf8
>> —locale=ru_RU.utf8 —lc-collate=ru_RU.utf8 —lc-ctype=ru_RU.utf8
>> —lc-messages=en_US.utf8
>>
>> Then updating:
>> /usr/pgsql-9.5/bin/pg_upgrade -b /usr/pgsql-9.4/bin/ -d
>> /data/upgrade/94 -B /usr/pgsql-9.5/bin/ -D /data/upgrade/95 -k
>>
>> and so on to 9.6
>>
>>
>> The original 9.4 database has the same encoding setup?
>>
>> FYI, you can use pg_upgrade to go straight from 9.4 to 9.6.
>>
>> https://www.postgresql.org/docs/9.6/static/pgupgrade.html
>> 
>>
>> "pg_upgrade supports upgrades from 8.4.X and later to the current
>> major release of PostgreSQL, including snapshot and alpha releases."
>>
>>
>>
>> after that server starts normally.
>>
>>
>> -- Timokhin 'maf' Maxim
>>
>>
>>
>>
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Config for fast huge cascaded updates

[Craig de Stigter]

Thanks everyone. Sorry for the late reply.


Do you have indexes on all the referencing columns?


I had thought so, but it turns out no, and this appears to be the main
cause of the slowness. After adding a couple of extra indexes in the bigger
tables, things are going much more smoothly.


write the whole thing into a new SQL schema


This is a really interesting approach I hadn't thought of! We can currently
afford a little bit of downtime, but it's helpful to keep this in mind if
we ever do this kind of thing again in future.

The two changes we've made are:

   - Add a few indexes so that the cascades operate more efficiently
   - Move some of the tables (whose ID values don't matter so much to our
   app) into a separate migration, which can be run before we take down the
   site. Then only the tables whose IDs matter to the app/user are done while
   the site is down.

With those changes it looks like we can fit the downtime into the window we
have. Thanks for all the advice, much appreciated!


On 28 June 2017 at 01:28, Andrew Sullivan  wrote:

> On Mon, Jun 26, 2017 at 07:26:08PM -0700, Joshua D. Drake wrote:
>
> > Alternatively, and ONLY do this if you take a backup right before hand,
> you
> > can set the table unlogged, make the changes and assuming success, make
> the
> > table logged again. That will great increase the write speed and reduce
> wal
> > segment churn.
>
> Note that this is not for just that table, but for all of the
> implicated ones because of the CASCADE statements.  It sounds like the
> OP is basically rewriting a significant chunk of the entire database,
> so nothing is going to be super fast: all those CASCADEs have to fire
> and all those other tables need to be updated too.
>
> > However, if that fails, the table is dead. You will have to reload it
> from
> > backup.
>
> Right, and that goes for all the affected tables.
>
> Best regards,
>
> A
>
> --
> Andrew Sullivan
> a...@crankycanuck.ca
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Regards,
Craig

Developer
Koordinates

+64 21 256 9488 <+64%2021%20256%209488> / koordinates.com / @koordinates

[GENERAL] pglogical trouble on BDR cluster

[Alvaro Aguayo Garcia-Rada]

Hi. I currently have a two-server BDR replication setup. Apart from the two
master nodes, I have another two servers which I want to have read only
replication for some tables only.

 

I first had to deal with some trouble regarding CREATE EXTENSION being
replicated by BDR, and that causing the schema creation for the extension to
be run on both servers, thus locking the entire cluster. I solved that by
creating the schema, tables and function manually, and replacing the
extension SQL file with an empty one.

 

Now, after joining the subscriber to the provider, I keep getting the
following log in the provider:

 

< 2017-07-02 13:30:38.776 -05 >ERROR:  missing required parameter
"startup_params_format"

< 2017-07-02 13:30:38.776 -05 >CONTEXT:  slot
"pgl_ocs_test_dayana_jimena_dayana", output plugin "pglogical_output", in
the startup callback

 

And in the subscriber:

 

< 2017-07-02 13:29:39.824 CEST >LOG:  registering background worker
"pglogical apply 192166:2763588059"

< 2017-07-02 13:29:39.824 CEST >LOG:  starting background worker process
"pglogical apply 192166:2763588059"

< 2017-07-02 13:29:39.827 CEST >LOG:  starting apply for subscription
jimena_dayana

< 2017-07-02 13:29:39.833 CEST >ERROR:  data stream ended

< 2017-07-02 13:29:39.834 CEST >LOG:  apply worker [5632] at slot 2
generation 127 crashed

< 2017-07-02 13:29:39.835 CEST >LOG:  worker process: pglogical apply
192166:2763588059 (PID 5632) exited with exit code 1

< 2017-07-02 13:29:39.835 CEST >LOG:  unregistering background worker
"pglogical apply 192166:2763588059"

 

I've searched all around the source code & document5ation, but I see no way
to set that parameter, so I'm stucked here. Any help would be appreciated.

 

Regards,

 

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: (+51) (#)  995540103  | RPC: (+51) 954183248
Website: www.ocs.pe  

 

Re: [GENERAL] Text search dictionary vs. the C locale

[twoflower]

Tom Lane-2 wrote
> Presumably the problem is that the dictionary file parsing functionsreject
> anything that doesn't satisfy t_isalpha() (unless it matchest_isspace())
> and in C locale that's not going to accept very much.

That's what I also guessed and the fact that setting lc-ctype=en_US.UTF-8
makes it work confirms it, I think.



--
View this message in context: 
http://www.postgresql-archive.org/Text-search-dictionary-vs-the-C-locale-tp5969677p5969703.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

[Jeff Janes]

On Sat, Jul 1, 2017 at 8:55 PM, rajan  wrote:

> Thanks, Jeff. That helps understanding it 50%.
>
> *Session 2* fails to UPDATE the record which is in *(0,2)* and this tuple
> is
> marked for deletion. It means that *(0,2) never exists* when Session 2 is
> trying to perform the update.
>

That it never exists is an appearance presented to the user.  The database
system works hard to maintain that illusion but the database system itself
sees through the illusion.  It blocks on (0,2) waiting for session 1 to
commit, and then once that happens session 2 goes and finds the new version
of that row ((0,4) in this case) and locks it.  If you use pageinspect, you
can see that (0,2) has left a pointer behind pointing to (0,4) to make it
easy to find.

Cheers,

Jeff

Re: [GENERAL] Text search dictionary vs. the C locale

[Gmail]

> On Jul 2, 2017, at 10:06 AM, Tom Lane  wrote:
> 
> twoflower  writes:
>> I am having problems creating an Ispell-based text search dictionary for
>> Czech language.
> 
>> Issuing the following command:
> 
>> create text search dictionary czech_ispell (
>>  template = ispell,
>>  dictfile = czech_ispell,
>>  affFile = czech_ispell
>> );
> 
>> ends with
> 
>> ERROR:  syntax error
>> CONTEXT:  line 252 of configuration file
>> "/usr/share/postgresql/9.6/tsearch_data/czech_ispell.affix": " . > TŘIA
> 
>> The dictionary files are in UTF-8. The database cluster was initialized with
>> initdb --locale=C --encoding=UTF8
> 
> Presumably the problem is that the dictionary file parsing functions
> reject anything that doesn't satisfy t_isalpha() (unless it matches
> t_isspace()) and in C locale that's not going to accept very much.
> 
> I wonder why we're doing it like that.  It seems like it'd often be
> useful to load dictionary files that don't match the database's
> prevailing locale.  Do we really need the t_isalpha tests, or would
> it be good enough to assume that anything that isn't t_isspace is
> part of a word?
> 
>regards, tom lane
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Apologies for truncating entire body of replied-to post

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Need help on compiling postgres source code from cloned repo

[rajan]

Thanks, Adrian. Able to install 10beta1 successfully.



-
--
Thanks,
Rajan.
--
View this message in context: 
http://www.postgresql-archive.org/Need-help-on-compiling-postgres-source-code-from-cloned-repo-tp5969667p5969696.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search dictionary vs. the C locale

[Gmail]

Sent from my iPad

> On Jul 2, 2017, at 10:06 AM, Tom Lane  wrote:
> 
> twoflower  writes:
>> I am having problems creating an Ispell-based text search dictionary for
>> Czech language.
> 
>> Issuing the following command:
> 
>> create text search dictionary czech_ispell (
>>  template = ispell,
>>  dictfile = czech_ispell,
>>  affFile = czech_ispell
>> );
> 
>> ends with
> 
>> ERROR:  syntax error
>> CONTEXT:  line 252 of configuration file
>> "/usr/share/postgresql/9.6/tsearch_data/czech_ispell.affix": " . > TŘIA
> 
>> The dictionary files are in UTF-8. The database cluster was initialized with
>> initdb --locale=C --encoding=UTF8
> 
> Presumably the problem is that the dictionary file parsing functions
> reject anything that doesn't satisfy t_isalpha() (unless it matches
> t_isspace()) and in C locale that's not going to accept very much.
> 
> I wonder why we're doing it like that.  It seems like it'd often be
> useful to load dictionary files that don't match the database's
> prevailing locale.  Do we really need the t_isalpha tests, or would
> it be good enough to assume that anything that isn't t_isspace is
> part of a word?
> 
>regards, tom lane
> 
What about punctuation?
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search dictionary vs. the C locale

[Tom Lane]

twoflower  writes:
> I am having problems creating an Ispell-based text search dictionary for
> Czech language.

> Issuing the following command:

> create text search dictionary czech_ispell (
>   template = ispell,
>   dictfile = czech_ispell,
>   affFile = czech_ispell
> );

> ends with

> ERROR:  syntax error
> CONTEXT:  line 252 of configuration file
> "/usr/share/postgresql/9.6/tsearch_data/czech_ispell.affix": " . > TŘIA

> The dictionary files are in UTF-8. The database cluster was initialized with
> initdb --locale=C --encoding=UTF8

Presumably the problem is that the dictionary file parsing functions
reject anything that doesn't satisfy t_isalpha() (unless it matches
t_isspace()) and in C locale that's not going to accept very much.

I wonder why we're doing it like that.  It seems like it'd often be
useful to load dictionary files that don't match the database's
prevailing locale.  Do we really need the t_isalpha tests, or would
it be good enough to assume that anything that isn't t_isspace is
part of a word?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Need help on compiling postgres source code from cloned repo

[Adrian Klaver]

On 07/01/2017 11:31 PM, rajan wrote:

Hi,

I have a cloned repository of postgres and I want to compile the source for
*REL_10_BETA1* alone.


Or just go here and grab the tarball:

https://www.postgresql.org/ftp/source/v10beta1/



Following are the steps I am planning to do,
-> git checkout -b rel10beta1 REL_10_BETA1 (and verifying current branch is
newly created one)


Is there a reason you want to start a new branch and not just checkout 
the existing branch?



-> Execute ./configure --prefix=/opt/PostgreSQL/10beta --with-pgport=6432
--enable-integer-datetimes --enable-thread-safety --enable-debug
-> And then make and make install




--enable-integer-datetimes --enable-thread-safety are the default so 
they do not need to be specified. In fact in 10 you can't create float 
datetimes:


https://www.postgresql.org/docs/10/static/release-10.html
"

Remove support for floating-point datetimes/timestamps (Tom Lane)

This removes configure's --disable-integer-datetimes option. 
Floating-point datetimes/timestamps have not been the default since 
Postgres 8.3 and have few advantages.

"



Will the above steps work? Please assist.



-
--
Thanks,
Rajan.
--
View this message in context: 
http://www.postgresql-archive.org/Need-help-on-compiling-postgres-source-code-from-cloned-repo-tp5969667.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search dictionary vs. the C locale

[twoflower]

Initializing the cluster with 

initdb
--locale=C
--lc-ctype=en_US.UTF-8
--lc-messages=en_US.UTF-8
--lc-monetary=en_US.UTF-8
--lc-numeric=en_US.UTF-8
--lc-time=en_US.UTF-8
--encoding=UTF8

allows me to use my text search dictionary. Now it only remains to see
whether index creation will be still fast (I suspect it should) and if it
doesn't have any other unintended consequences (e.g. in pattern matching
which we use a lot).



--
View this message in context: 
http://www.postgresql-archive.org/Text-search-dictionary-vs-the-C-locale-tp5969677p5969678.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Text search dictionary vs. the C locale

[twoflower]

I am having problems creating an Ispell-based text search dictionary for
Czech language.

Issuing the following command:

create text search dictionary czech_ispell (
  template = ispell,
  dictfile = czech_ispell,
  affFile = czech_ispell
);

ends with

ERROR:  syntax error
CONTEXT:  line 252 of configuration file
"/usr/share/postgresql/9.6/tsearch_data/czech_ispell.affix": " . > TŘIA

The dictionary files are in UTF-8. The database cluster was initialized with

initdb --locale=C --encoding=UTF8

When, on the other hand, I initialize it with

initdb --locale=en_US.UTF8

it works.

I was hoping I could have the C locale with the UTF-8 encoding but it seems
non-ASCII text search dictionaries are not supported in that case. This is a
shame as restoring the dumps goes from 1.5 hour (with the C locale) to 9.5
hours (with en_US.UTF8).



--
View this message in context: 
http://www.postgresql-archive.org/Text-search-dictionary-vs-the-C-locale-tp5969677.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Need help on compiling postgres source code from cloned repo

[rajan]

Hi,

I have a cloned repository of postgres and I want to compile the source for
*REL_10_BETA1* alone.

Following are the steps I am planning to do,
-> git checkout -b rel10beta1 REL_10_BETA1 (and verifying current branch is
newly created one)
-> Execute ./configure --prefix=/opt/PostgreSQL/10beta --with-pgport=6432
--enable-integer-datetimes --enable-thread-safety --enable-debug
-> And then make and make install

Will the above steps work? Please assist.



-
--
Thanks,
Rajan.
--
View this message in context: 
http://www.postgresql-archive.org/Need-help-on-compiling-postgres-source-code-from-cloned-repo-tp5969667.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general