[GENERAL] looking for a globally unique row ID

Hello everybody, Can anybody help me find a way to implement an ID which: 1. guarantees being unique across multiple tables. 2. guarantees its uniqueness not only during INSERT, but also during the lifetime of the database/application (e.i. during future UPDATES). 3. guarantees persistence of v

Re: [GENERAL] looking for a globally unique row ID

On 9/14/2017 12:45 AM, Rafal Pietrak wrote: Can anybody help me find a way to implement an ID which: 1. guarantees being unique across multiple tables. 2. guarantees its uniqueness not only during INSERT, but also during the lifetime of the database/application (e.i. during future UPDATES). 3.

Re: [GENERAL] looking for a globally unique row ID

On Thu, Sep 14, 2017 at 4:45 PM, Rafal Pietrak wrote: > Can anybody help me find a way to implement an ID which: > > 1. guarantees being unique across multiple tables. > > 2. guarantees its uniqueness not only during INSERT, but also during the > lifetime of the database/application (e.i. during f

[GENERAL] a JOIN to a VIEW seems slow

Hi all This is a follow-up to a recent question I posted regarding a slow query. I thought that the slowness was caused by the number of JOINs in the query, but with your assistance I have found the true reason. I said in the previous thread that the question had become academic, but now that I

Re: [GENERAL] looking for a globally unique row ID

On Thu, 14 Sep 2017 09:45:59 +0200, Rafal Pietrak wrote: >Hello everybody, > >Can anybody help me find a way to implement an ID which: > >1. guarantees being unique across multiple tables. > >2. guarantees its uniqueness not only during INSERT, but also during the >lifetime of the database/applic

[GENERAL] How to add new Collation language

How can I add a collation language to a Postgres server? Specifically, I want to create a new database with collation of English_United Kingdom.1252 but the only options are C, Posix and United States. Even if I select United Kingdom as the locale when installing Postgres I still only have United

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-14 10:14 GMT+02:00 Frank Millman : > Hi all > > This is a follow-up to a recent question I posted regarding a slow query. > I thought that the slowness was caused by the number of JOINs in the query, > but with your assistance I have found the true reason. I said in the > previous thread t

[GENERAL] Major Version Upgradation from 9.4 to 9.6

[Quipment Logo] Hello, We have a windows setup for PostgreSQL(Version 9.4). Now we are upgrading Major version 9.4 to 9.6. Total Database size - 421 GB RAM - 16 GB Core - 8 Size of Data Directory Drive - 500 GB We have 3 database in 421 GB. 1)X database size - 12 GB 2)Y database siz

[GENERAL] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start

I support an existing product that utilizes postgreSQL in a Windows environment. Recently we've been asked to make a series of security/auditing changes to the product that require pgaudit. We built pgaudit.dll and have made the majority of the configuration changes and those seem to be workin

Re: [GENERAL] How to add new Collation language

On Thu, 2017-09-14 at 11:30 +, Rob Northcott wrote: > How can I add a collation language to a Postgres server? > Specifically, I want to create a new database with collation of > English_United Kingdom.1252 but the only options are C, Posix and > United States. > Even if I select United Kingd

Re: [GENERAL] a JOIN to a VIEW seems slow

Pavel Stehule wrote: 2017-09-14 10:14 GMT+02:00 Frank Millman : Hi all This is a follow-up to a recent question I posted regarding a slow query. I thought that the slowness was caused by the number of JOINs in the query, but with your assistance I have found the true reason. I said in the

Re: [GENERAL] looking for a globally unique row ID

W dniu 14.09.2017 o 10:57, George Neuner pisze: > On Thu, 14 Sep 2017 09:45:59 +0200, Rafal Pietrak > wrote: > >> Hello everybody, >> >> Can anybody help me find a way to implement an ID which: >> >> 1. guarantees being unique across multiple tables. >> >> 2. guarantees its uniqueness not only d

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-14 14:59 GMT+02:00 Frank Millman : > Pavel Stehule wrote: > > 2017-09-14 10:14 GMT+02:00 Frank Millman : > >> Hi all >> >> This is a follow-up to a recent question I posted regarding a slow query. >> I thought that the slowness was caused by the number of JOINs in the query, >> but with yo

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-14 15:09 GMT+02:00 Pavel Stehule : > > > 2017-09-14 14:59 GMT+02:00 Frank Millman : > >> Pavel Stehule wrote: >> >> 2017-09-14 10:14 GMT+02:00 Frank Millman : >> >>> Hi all >>> >>> This is a follow-up to a recent question I posted regarding a slow >>> query. I thought that the slowness was

Re: [GENERAL] looking for a globally unique row ID

On 2017-09-14 15:06, Rafal Pietrak wrote: W dniu 14.09.2017 o 10:57, George Neuner pisze: On Thu, 14 Sep 2017 09:45:59 +0200, Rafal Pietrak wrote: Hello everybody, Can anybody help me find a way to implement an ID which: 1. guarantees being unique across multiple tables. 2. guarantees its

Re: [GENERAL] How to add new Collation language

-Original Message- From: rob stone [mailto:floripa...@gmail.com] Sent: 14 September 2017 13:38 To: Rob Northcott ; pgsql-general@postgresql.org Subject: Re: [GENERAL] How to add new Collation language On Thu, 2017-09-14 at 11:30 +, Rob Northcott wrote: > How can I add a co

Re: [GENERAL] How to add new Collation language

Rob Northcott writes: > From: rob stone [mailto:floripa...@gmail.com] >> On Thu, 2017-09-14 at 11:30 +, Rob Northcott wrote: >>> How can I add a collation language to a Postgres server? >>> Specifically, I want to create a new database with collation of >>> English_United Kingdom.1252 but th

Re: [GENERAL] looking for a globally unique row ID

On Thu, Sep 14, 2017 at 2:45 AM, Rafal Pietrak wrote: > Hello everybody, > > Can anybody help me find a way to implement an ID which: > > 1. guarantees being unique across multiple tables. > > 2. guarantees its uniqueness not only during INSERT, but also during the > lifetime of the database/appli

Re: [GENERAL] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start

On Wed, Sep 13, 2017 at 02:42:18PM +, Troy Hardin wrote: > Putting either of these two lines in the .conf file cause it to fail to start. Can you show error messages from logs? -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company -- Sent via pgsql-g

Re: [GENERAL] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start

On Sep 14, 2017 7:07 AM, "Arthur Zakirov" wrote: On Wed, Sep 13, 2017 at 02:42:18PM +, Troy Hardin wrote: > Putting either of these two lines in the .conf file cause it to fail to start. Can you show error messages from logs? And the version.

Re: [GENERAL] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start

Jeff/Arthur, Thanks. I am testing on version 9.5.7 with pgaudit 1.0.6. The production system is currently 9.5.7 but will receive updates to 9.5.9 and beyond as updates are made available. I will paste in here info from the postgreSQL logs and then a couple entries from Event Viewer. Sadly,

Re: [GENERAL] looking for a globally unique row ID

On Sep 14, vinny modulated: > If it is only one database, on one server, then couldn't you just > use one sequence? > If oyu prefix the value with some identifier of the current table > then you cannot get duplicates > across tables even if you reset the sequence. > I didn't follow the whole thr

Re: [GENERAL] looking for a globally unique row ID

> On Sep 14, 2017, at 8:38 AM, Karl Czajkowski wrote: > > On Sep 14, vinny modulated: > >> If it is only one database, on one server, then couldn't you just >> use one sequence? >> If oyu prefix the value with some identifier of the current table >> then you cannot get duplicates >> across tabl

Re: [GENERAL] How to add new Collation language

-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: 14 September 2017 14:53 To: Rob Northcott Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to add new Collation language Rob Northcott writes: > From: rob stone [mailto:floripa...@gmail.com] >> On Thu, 2017

Re: [GENERAL] looking for a globally unique row ID

W dniu 14.09.2017 o 15:54, Merlin Moncure pisze: > On Thu, Sep 14, 2017 at 2:45 AM, Rafal Pietrak wrote: >> Hello everybody, >> >> Can anybody help me find a way to implement an ID which: >> >> 1. guarantees being unique across multiple tables. >> >> 2. guarantees its uniqueness not only during

Re: [GENERAL] looking for a globally unique row ID

On 09/14/2017 11:11 AM, Rafal Pietrak wrote: Not really. As I said, I'm not looking for performance or "fair probability" of planetary-wide uniqueness. My main objective is the "guarantee". Which I've tried to indicate referring to "future UPDATEs". What I mean here is functionality similar

Re: [GENERAL] looking for a globally unique row ID

On Thu, Sep 14, 2017 at 07:11:19PM +0200, Rafal Pietrak wrote: > > As I said, I'm not looking for performance or "fair probability" of > planetary-wide uniqueness. > > My main objective is the "guarantee". Which I've tried to indicate > referring to "future UPDATEs". > > What I mean here is func

[GENERAL] COMMIT TRIGGER implementation using CONSTRAINT TRIGGERs

I've written an approximation of "commit triggers" for PostgreSQL using CONSTRAINT TRIGGERs (as users have often been told to do who want this feature). Semantics (and a warning) are included in commentary in the source: https://github.com/twosigma/postgresql-contrib/ https://github.com/twosigma/

Re: [GENERAL] looking for a globally unique row ID

W dniu 14.09.2017 o 19:30, Rob Sargent pisze: > > > On 09/14/2017 11:11 AM, Rafal Pietrak wrote: >> [--] >> So I'm stuck with seriously incomplete solution. >> >> that's why I have an impression, that I'm going into entirely wrong >> direction here. >> >> > So you care if the s

[GENERAL] Table partition - parent table use

Hello everybody! I have searched quite a bit, but haven't found a setup like the ours, so I decided to ask if I'm missing something: We have a database with data being inserted for almost 10 years and no policy defined to get rid of old records, even though we mostly use only the current and last

Re: [GENERAL] looking for a globally unique row ID

On 09/14/2017 02:39 PM, Rafal Pietrak wrote: W dniu 14.09.2017 o 19:30, Rob Sargent pisze: On 09/14/2017 11:11 AM, Rafal Pietrak wrote: [--] So I'm stuck with seriously incomplete solution. that's why I have an impression, that I'm going into entirely wrong direction here.

Re: [GENERAL] looking for a globally unique row ID

On Sep 14, Rafal Pietrak modulated: > My main objective is the "guarantee". Which I've tried to indicate > referring to "future UPDATEs". > With a well-behaved application, it is sufficient to define each ID column as: id int8 UNIQUE NOT NULL DEFAULT nextval('sharedsequence') and ensure tha

Re: [GENERAL] looking for a globally unique row ID

On 15/09/17 06:15, Kenneth Marshall wrote: On Thu, Sep 14, 2017 at 07:11:19PM +0200, Rafal Pietrak wrote: As I said, I'm not looking for performance or "fair probability" of planetary-wide uniqueness. My main objective is the "guarantee". Which I've tried to indicate referring to "future UPDATE

Re: [GENERAL] looking for a globally unique row ID

On Thu, Sep 14, 2017 at 12:11 PM, Rafal Pietrak wrote: > W dniu 14.09.2017 o 15:54, Merlin Moncure pisze: >> On Thu, Sep 14, 2017 at 2:45 AM, Rafal Pietrak wrote: >>> Hello everybody, >>> >>> Can anybody help me find a way to implement an ID which: >>> >>> 1. guarantees being unique across multip

Re: [GENERAL] looking for a globally unique row ID

On Thu, Sep 14, 2017 at 12:45 AM, Rafal Pietrak wrote: > Hello everybody, > > Can anybody help me find a way to implement an ID which: > > 1. guarantees being unique across multiple tables. > > 2. guarantees its uniqueness not only during INSERT, but also during the > lifetime of the database/app

Re: [GENERAL] looking for a globally unique row ID

On 09/14/2017 03:27 PM, David G. Johnston wrote: On Thu, Sep 14, 2017 at 12:45 AM, Rafal Pietrak >wrote: Hello everybody, Can anybody help me find a way to implement an ID which: 1. guarantees being unique across multiple tables. 2. guarantees its uniqu

Re: [GENERAL] BDR, near xid wraparound, a lot of files in pg_subtrans directory

On 14 September 2017 at 13:35, milist ujang wrote: > HI list, > > I have a database with bdr environment which keep alerting these messages > in log file: > > HINT: Close open transactions soon to avoid wraparound problems. > WARNING: oldest xmin is far in the past > Do you have any idle/old r

Re: [GENERAL] looking for a globally unique row ID

On Thu, 14 Sep 2017 17:02:05 -0500, Merlin Moncure wrote: >... With sequences, the database *guarantees* that the >identifier is unique with no exceptions; there never will be a unique >value. Can you give a hypothetical example of how you think they >wouldn't work? Jumping in here, but a month

Re: [GENERAL] BDR, near xid wraparound, a lot of files in pg_subtrans directory

Hi Craig, Thanks again for pointing to inactive replication slot. After inactive replication slot been dropped, the relfrozenxid now moving. I wonder if replication identifier will have some issue if left un-chained? since at other side there are inactive replication identifier. On Fri, Sep 1

Re: [GENERAL] BDR, near xid wraparound, a lot of files in pg_subtrans directory

On 15 September 2017 at 11:46, milist ujang wrote: > Hi Craig, > > Thanks again for pointing to inactive replication slot. > After inactive replication slot been dropped, the relfrozenxid now moving. > > I wonder if replication identifier will have some issue if left > un-chained? since at other

Re: [GENERAL] pg_rewind copy so much data

I use ver 9.5.3. I have just run again and get the debug log. It seems to be that I cannot send such big file to postgresql mail so I copy some parts here: fetched file "global/pg_control", length 8192 fetched file "pg_xlog/000D.history", length 475 servers diverged at WAL position 2/D69820C8

Re: [GENERAL] pg_rewind copy so much data

On Fri, Sep 15, 2017 at 2:57 PM, Hung Phan wrote: > [...] Please do not top-post. This breaks the logic of the thread. > I use ver 9.5.3. You should update to the latest minor version available, there have been quite a couple of bug fixes in Postgres since this 9.5.3. > I have just run again a