Re: [GENERAL] Improving performance of merging data between tables

Andy, thanks for looking into this. On Mon, Dec 29, 2014 at 9:00 AM, Andy Colson wrote: > On 12/28/2014 3:49 PM, Pawel Veselov wrote: > >> Hi. >> >> I was wondering if anybody would have any ideas on how to improve >> certain operations that we are having. >> >> > >> >> Besides "can somebody

Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

On Tue, Dec 30, 2014 at 12:53:42AM +, Mike Cardwell wrote: > > Hmm. How did you get the original, then? > > The "original" in my case, is the hostname which the end user supplied. > Essentially, when I display it back to them, I want to make sure it is > displayed the same way that it was whe

Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

* on the Mon, Dec 29, 2014 at 07:25:59PM -0500, Andrew Sullivan wrote: >> This is exactly the same method that we commonly use for performing case >> insensitive text searches using lower() indexes. > > Hmm. How did you get the original, then? The "original" in my case, is the hostname which the

Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

* on the Mon, Dec 29, 2014 at 07:22:21PM -0500, Andrew Sullivan wrote: >> can't just encode it with punycode and then store the ascii result. For >> example, >> these two are the same hostnames thanks to unicode case folding [1]: >> >> tesst.ëxämplé.com >> teßt.ëxämplé.com > > Well, in IDNA

Re: [GENERAL] Rollback on include error in psql

On 12/29/2014 04:26 PM, David Johnston wrote: On Mon, Dec 29, 2014 at 5:09 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>>wrote: On 12/29/2014 03:56 PM, David Johnston wrote: ​So you think psql should issue "COMMIT;" even if it is exiting due to "ON_ERROR_STOP"

Re: [GENERAL] Rollback on include error in psql

On Mon, Dec 29, 2014 at 5:09 PM, Adrian Klaver wrote: > On 12/29/2014 03:56 PM, David Johnston wrote: > >> > ​So you think psql should issue "COMMIT;" even if it is exiting due to >> > "ON_ERROR_STOP"? >> > > I say yes, if it is a non-SQL error. As Viktor stated, SQL errors abort > the transacti

Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

* on the Mon, Dec 29, 2014 at 05:01:44PM -0700, David G Johnston wrote: >> CREATE UNIQUE INDEX hostnames_hostname_key ON hostnames >> (lower(punycode_encode(hostname))); >> >> That would prevent adding more than one representation for the same >> hostname >> to the column. > > Except two differen

Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

On Tue, Dec 30, 2014 at 12:18:58AM +, Mike Cardwell wrote: > > This is exactly the same method that we commonly use for performing case > insensitive text searches using lower() indexes. Hmm. How did you get the original, then? If you have the original Unicode version, why don't you switch

Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

[resending, because somehow this got routed through my work address the first time.] Hi, I didn't have time to write a short note, so I wrote a long one instead. Sorry. On Mon, Dec 29, 2014 at 10:36:42PM +, Mike Cardwell wrote: > can't just encode it with punycode and then store the ascii

Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

* on the Mon, Dec 29, 2014 at 07:00:05PM -0500, Andrew Sullivan wrote: >> CREATE UNIQUE INDEX hostnames_hostname_key ON hostnames >> (lower(punycode_encode(hostname))); > > This wouldn't work to get the original back if oyu have any IDNA200 > data, because puncode-encoding the UTF-8 under IDNA200

Re: [GENERAL] Rollback on include error in psql

On 12/29/2014 03:56 PM, David Johnston wrote: ​So you think psql should issue "COMMIT;" even if it is exiting due to "ON_ERROR_STOP"? I say yes, if it is a non-SQL error. As Viktor stated, SQL errors abort the transaction. Whether you do or don't can you show me where in the documen

Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

David G Johnston writes: > It was also mentioned that using the Perl encoding function was > non-performant; which is why caching the data into a memoization table has > value. I find it hard to believe that the standards folk would have chosen a hostname encoding method that was so inefficient t

Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

On Mon, Dec 29, 2014 at 4:51 PM, Mike Cardwell [via PostgreSQL] < ml-node+s1045698n583236...@n5.nabble.com> wrote: > * on the Mon, Dec 29, 2014 at 04:23:22PM -0700, David G Johnston wrote: > > >>>WHERE hostname='nißan.com ' > >>> > >> > >> _IF_ Postgres had a punycode functi

Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

On Mon, Dec 29, 2014 at 11:50:54PM +, Mike Cardwell wrote: > > CREATE UNIQUE INDEX hostnames_hostname_key ON hostnames > (lower(punycode_encode(hostname))); This wouldn't work to get the original back if oyu have any IDNA2003 data, because puncode-encoding the UTF-8 under IDNA2003 and then p

Re: [GENERAL] Rollback on include error in psql

On Mon, Dec 29, 2014 at 4:38 PM, Adrian Klaver wrote: > On 12/29/2014 02:55 PM, David Johnston wrote: > >> On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver >> mailto:adrian.kla...@aklaver.com>>wrote: >> >> On 12/29/2014 02:28 PM, David Johnston wrote: >> >> On Mon, Dec 29, 2014 at 3:07

Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

* on the Mon, Dec 29, 2014 at 04:23:22PM -0700, David G Johnston wrote: >>>WHERE hostname='nißan.com' >>> >> >> _IF_ Postgres had a punycode function, then you could use: >> WHERE punycode(hostname) = punycode('nißan.com') > > If the OP wraps what he is doing up into a function that is what

Re: [GENERAL] Rollback on include error in psql

On 12/29/2014 02:55 PM, David Johnston wrote: On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>>wrote: On 12/29/2014 02:28 PM, David Johnston wrote: On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>

Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

Andy Colson wrote > On 12/29/2014 4:36 PM, Mike Cardwell wrote: >> I'd like to store hostnames in a postgres database and I want to fully >> support >> IDNs (Internationalised Domain Names) >> >> I want to be able to recover the original representation of the hostname, >> so I >> can't just encode

Re: [GENERAL] Rollback on include error in psql

On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver wrote: > On 12/29/2014 02:28 PM, David Johnston wrote: > >> On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver >> mailto:adrian.kla...@aklaver.com>>wrote: >> >> On 12/29/2014 09:38 AM, David Johnston wrote: >> >> >> This is one of those

Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

On 12/29/2014 4:36 PM, Mike Cardwell wrote: I'd like to store hostnames in a postgres database and I want to fully support IDNs (Internationalised Domain Names) I want to be able to recover the original representation of the hostname, so I can't just encode it with punycode and then store the as

Re: [GENERAL] Rollback on include error in psql

On 12/29/2014 02:28 PM, David Johnston wrote: On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>>wrote: On 12/29/2014 09:38 AM, David Johnston wrote: This is one of those glass half full/empty situations, where it is down to t

[GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

I'd like to store hostnames in a postgres database and I want to fully support IDNs (Internationalised Domain Names) I want to be able to recover the original representation of the hostname, so I can't just encode it with punycode and then store the ascii result. For example, these two are the sam

Re: [GENERAL] Rollback on include error in psql

On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver wrote: > On 12/29/2014 09:38 AM, David Johnston wrote: > >> >> This is one of those glass half full/empty situations, where it is >> down to the eye of the beholder. I would also say this a perfect >> example of why tests are written, to

Re: [GENERAL] Rollback on include error in psql

On 12/29/2014 09:38 AM, David Johnston wrote: Copying -bugs to gain broader attention and opinions. On Mon, Dec 29, 2014 at 10:06 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>>wrote: On 12/29/2014 08:49 AM, David Johnston wrote: On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver

Re: [GENERAL] ON_ERROR_ROLLBACK

On 12/29/2014 09:55 AM, Tom Lane wrote: Adrian Klaver writes: So it seems you can turn ON_ERROR_ROLLBACK on with either 1 or 'on', but you can only turn it off with 'off'. With ON_ERROR_STOP 1/on and 0/off both seem to work. Is this expected? Given the lack of previous complaints, thi

Re: [GENERAL] "ERROR: could not read block 4459549 in file "base/16384/16956.34": Result too large"

wetter wetterana wrote: > Re: [GENERAL] "ERROR: could not read block 4459549 in file > "base/16384/16956.34": Result too large" > I got a huge database which I am populating in batches. One of the > tables seemed to got 'corrupted.' I cannot query it anymore. I'm > pretty sure that I could iden

Re: [GENERAL] 9.3.6 release?

Joe Van Dyk writes: > Any estimates on when 9.3.6 will be released? We've been running off > 9-3-stable for the past five months, as there's some fixes in there that we > need. Yeah, we're overdue for update releases. Nothing's been formally set yet, but probably mid-to-late January.

[GENERAL] 9.3.6 release?

Hi, Any estimates on when 9.3.6 will be released? We've been running off 9-3-stable for the past five months, as there's some fixes in there that we need. Thanks, Joe

Re: [GENERAL] localtime(0)

"Raymond O'Donnell" writes: > On 29/12/2014 17:25, Suresh Raja wrote: >> clock_timestamp() gives more than I need. i just need time, - no date, >> or msec or timezone. How can i get to print the current time alone. > Please don't top-post; the convention on this list is to bottom-post, as > it m

Re: [GENERAL] localtime(0)

On 29/12/2014 17:25, Suresh Raja wrote: > clock_timestamp() gives more than I need. i just need time, - no date, > or msec or timezone. How can i get to print the current time alone. > Please don't top-post; the convention on this list is to bottom-post, as it makes discussions easier to follow.

Re: [GENERAL] ON_ERROR_ROLLBACK

Adrian Klaver writes: > So it seems you can turn ON_ERROR_ROLLBACK on with either 1 or 'on', but you > can only turn it off with 'off'. > With ON_ERROR_STOP 1/on and 0/off both seem to work. > Is this expected? on_error_stop_hook() uses ParseVariableBool, while on_error_rollback_hook() uses so

Re: [GENERAL] Rollback on include error in psql

Copying -bugs to gain broader attention and opinions. On Mon, Dec 29, 2014 at 10:06 AM, Adrian Klaver wrote: > On 12/29/2014 08:49 AM, David Johnston wrote: > >> On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver >> mailto:adrian.kla...@aklaver.com>>wrote: >> >> >> On 12/29/2014 07:59 AM, David

Re: [GENERAL] localtime(0)

clock_timestamp() gives more than I need. i just need time, - no date, or msec or timezone. How can i get to print the current time alone. Thanks, On Mon, Dec 29, 2014 at 10:41 AM, Adrian Klaver wrote: > On 12/29/2014 08:32 AM, Suresh Raja wrote: > >> Hi: >> >> I'm using localtime(0) in vario

[GENERAL] "ERROR: could not read block 4459549 in file "base/16384/16956.34": Result too large"

Hey, I got a huge database which I am populating in batches. One of the tables seemed to got 'corrupted.' I cannot query it anymore. I'm pretty sure that I could identify the batch of rows where the mistake must be, so if I could somehow revert it to an earlier state or temporary query it, I could

Re: [GENERAL] ON_ERROR_ROLLBACK

On 12/29/2014 08:51 AM, Adrian Klaver wrote: While working on the thread 'Rollback on include error in psql' I ran across something I am not sure with regards to ON_ERROR_ROLLBACK: So it seems you can turn ON_ERROR_ROLLBACK on with either 1 or 'on', but you can only turn it off with 'off'.

Re: [GENERAL] pg_base_backup limit bandwidth possible?

Andy Colson wrote: > On 12/29/2014 10:08 AM, Edson Carlos Ericksson Richter wrote: > >Hi! > > > >I could not find in docs, is there any way to limit pg_base_backup > >bandwidth usage? > > There is not. You can however run the base backup on the server side and > use ssh/rsync/etc to copy w/limits

Re: [GENERAL] Rollback on include error in psql

On 12/29/2014 08:49 AM, David Johnston wrote: On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>>wrote: On 12/29/2014 07:59 AM, David Johnston wrote: Anyway, the third undocumented bug is that --single-transactions gets to send its COMM

Re: [GENERAL] pg_base_backup limit bandwidth possible?

On 12/29/2014 10:08 AM, Edson Carlos Ericksson Richter wrote: Hi! I could not find in docs, is there any way to limit pg_base_backup bandwidth usage? Thanks, Edson There is not. You can however run the base backup on the server side and use ssh/rsync/etc to copy w/limits to the slave.

Re: [GENERAL] Improving performance of merging data between tables

On 12/28/2014 3:49 PM, Pawel Veselov wrote: Hi. I was wondering if anybody would have any ideas on how to improve certain operations that we are having. Besides "can somebody please look at this and let me know if I'm doing something utterly stupid", here are my questions. 1) How do I find

[GENERAL] vacuum vs pg_repack vs pg_reorg

Hi, I have a database of size 1.5 TB. The attachments are stored in bytea. The attachment table is consuming maximum space. The database version is 9.1.x and Streaming Replication is set. Now, I have to removed old records to make way for new records without increasing SAN Space. Which will

[GENERAL] extra function calls from query returning composite type

I added a 'raise notice' to a plpgsql function I was working on recently, and noticed that my notification was being raised more often than I'd expect. The notification is raised in a function ('getone' in my example below) that returns a single composite value. This function is then called by an

Re: [GENERAL] Rollback on include error in psql

On Mon, Dec 29, 2014 at 9:49 AM, David Johnston wrote: > On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver > wrote: > >> On 12/29/2014 07:59 AM, David Johnston wrote: >> >>> >>> Anyway, the third undocumented bug is that --single-transactions gets to >>> send its COMMIT even if ON_ERROR_STOP​ >>> ​

[GENERAL] ON_ERROR_ROLLBACK

While working on the thread 'Rollback on include error in psql' I ran across something I am not sure with regards to ON_ERROR_ROLLBACK: aklaver@panda:~> psql -d test -U aklaver -p 5452 --single-transaction --set ON_ERROR_STOP=on --set AUTOCOMMIT=off -f test_script.sql UPDATE 1 psql:test_script

Re: [GENERAL] Rollback on include error in psql

On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver wrote: > On 12/29/2014 07:59 AM, David Johnston wrote: > >> >> Anyway, the third undocumented bug is that --single-transactions gets to >> send its COMMIT even if ON_ERROR_STOP​ >> ​takes hold before the end of the script. I imagined it such that on

Re: [GENERAL] localtime(0)

On 12/29/2014 08:32 AM, Suresh Raja wrote: Hi: I'm using localtime(0) in various part of the function, and updating table with the time. I use localtime at places like the start of the function and at the end of the function. The function takes about 5 mins to run,. The issue is that the functi

Re: [GENERAL] Rollback on include error in psql

On 12/29/2014 07:59 AM, David Johnston wrote: On Mon, Dec 29, 2014 at 8:49 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>>wrote: On 12/28/2014 05:04 PM, David G Johnston wrote: > Adrian Klaver-4 wrote >> On 12/28/2014 10:06 AM, Viktor Shitkovskiy wrote: >>> I include my o

Re: [GENERAL] localtime(0)

On 12/29/2014 09:32 AM, Suresh Raja wrote: Hi: I'm using localtime(0) in various part of the function, and updating table with the time. I use localtime at places like the start of the function and at the end of the function. The function takes about 5 mins to run,. The issue is that the fun

[GENERAL] localtime(0)

Hi: I'm using localtime(0) in various part of the function, and updating table with the time. I use localtime at places like the start of the function and at the end of the function. The function takes about 5 mins to run,. The issue is that the function always print the same time for both start

[GENERAL] pg_base_backup limit bandwidth possible?

Hi! I could not find in docs, is there any way to limit pg_base_backup bandwidth usage? Thanks, Edson -- 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] Rollback on include error in psql

On Mon, Dec 29, 2014 at 8:49 AM, Adrian Klaver wrote: > On 12/28/2014 05:04 PM, David G Johnston wrote: > > Adrian Klaver-4 wrote > >> On 12/28/2014 10:06 AM, Viktor Shitkovskiy wrote: > >>> I include my own scripts. Each of them creates some table or makes some > >>> changes to existing tables.

Re: [GENERAL] extra function calls from query returning composite type

Ronald Peterson writes: > I added a 'raise notice' to a plpgsql function I was working on > recently, and noticed that my notification was being raised more often > than I expected. The notification is raised in a function ('getone' > in my example below) that returns a single composite value. T

Re: [GENERAL] extra function calls from query returning composite type

Ronald Peterson wrote > I added a 'raise notice' to a plpgsql function I was working on > recently, and noticed that my notification was being raised more often > than I expected. The notification is raised in a function ('getone' > in my example below) that returns a single composite value. This

Re: [GENERAL] Rollback on include error in psql

On 12/28/2014 05:04 PM, David G Johnston wrote: > Adrian Klaver-4 wrote >> On 12/28/2014 10:06 AM, Viktor Shitkovskiy wrote: >>> I include my own scripts. Each of them creates some table or makes some >>> changes to existing tables. >> >> It is hard to say where to go from here without more informa

[GENERAL] extra function calls from query returning composite type

I added a 'raise notice' to a plpgsql function I was working on recently, and noticed that my notification was being raised more often than I expected. The notification is raised in a function ('getone' in my example below) that returns a single composite value. This function is then called by an

Re: [GENERAL] help troubleshooting invalid page header error

Cory Zue wrote: > I was able to get the database back to a normal functional state > using the zero_damaged_pages flag. However, after getting > everything working and starting to use the database again, I am > again getting "invalid page header" errors on a certain table. > > Does this imply the