Re: [GENERAL] Merge rows based on Levenshtein distance

On Tuesday, December 2, 2014, mongoose [via PostgreSQL] < ml-node+s1045698n5829030...@n5.nabble.com> wrote: > David, > > Thank you for your prompt reply. I believe your answer helped a lot but it > seems I was not clear enough on my description. Basically I want a counter > (id) to show if two or

Re: [GENERAL] Is "WITH () UPDATE" Thread Safe ?

Albe Laurenz *EXTERN* wrote > Paul GOERGLER wrote: >> I have a lot of tickets, i need to take a batch of tickets and process >> them. >> So the process is : >> SELECT ONLY 100 tickets >> PROCESS ticket >> MARK THEM AS « done » >> >> I’m selecting the tickets with : >> >> WITH t0 AS ( >> SELEC

Re: [GENERAL] Is "WITH () UPDATE" Thread Safe ?

Ok thanks. --  Paul GOERGLER De: Albe Laurenz Répondre: Albe Laurenz > Date: 1 décembre 2014 at 13:21:07 À: Paul GOERGLER *EXTERN* >, pgsql-general@postgresql.org > Sujet:  RE: [GENERAL] Is "WITH () UPDATE" Thread Safe ? Paul GOERGLER wrote: > I have a lot of tickets, i need to take a batch

Re: [GENERAL] Merge rows based on Levenshtein distance

David, Thank you for your prompt reply. I believe your answer helped a lot but it seems I was not clear enough on my description. Basically I want a counter (id) to show if two or more names are similar (i.e. levenshtein distance less than 3) So in the previous example: >From this table: Name,

Re: [GENERAL] Programmatic access to interval units

On Tue, Dec 2, 2014 at 3:48 PM, Merlin Moncure wrote: > On Tue, Dec 2, 2014 at 12:40 PM, Nelson Green > wrote: > > In the line: IF LOWER(_unit) NOT IN (SELECT interval_unit ..., I would > > rather > > query a catalog table for the interval unit names if possible. That would > > then > > compensa

Re: [GENERAL] Postgres 9.2 PITR testing to before DROP DATABASE ends up removing file system files

On 11/28/2014 02:29 PM, Joshua Boyd wrote: I am testing out point in time recovery from a hot physical backup in a disaster recovery situation - I turned on archiving of files, created a hot physical backup, How did you take the backup? Archiving how and to where? then (after letting it run f

Re: [GENERAL] Problem with pg_dump and decimal mark

On 11/29/2014 12:25 AM, Eric Svenson wrote: Seems you have a locale mismatch issue. The dump is coming from a locale where a '.' is the decimal mark and is being restored to a locale where ',' is the mark. Look at what the locales are

Re: [GENERAL] Programmatic access to interval units

On Tue, Dec 2, 2014 at 12:40 PM, Nelson Green wrote: > In the line: IF LOWER(_unit) NOT IN (SELECT interval_unit ..., I would > rather > query a catalog table for the interval unit names if possible. That would > then > compensate for any changes to those values in the future. > > When I meant do

Re: [GENERAL] [Solved] Programmatic access to interval units

On Tue, Dec 2, 2014 at 2:25 PM, Adrian Klaver wrote: > On 12/02/2014 10:40 AM, Nelson Green wrote: > >> On Tue, Dec 2, 2014 at 11:57 AM, Merlin Moncure > > wrote: >> > > >> Hi Merlin, >> >> I'm afraid I'm only confusing things, so let me give an example of what I >> am

Re: [GENERAL] Trying to get SSPI/JDBC working

On 12/02/2014 08:10 AM, Dave Rosckes wrote: I am new to Postgresql. I have a postgres server running on a windows platform. The DBs will not have any remote access, only users logged onto the localhost will have access. SSPI seems like a great authentication protocol to use in this case. I be

Re: [GENERAL] JSON_AGG produces extra square brakets

Thank you! Glad to have helped! On Tue, Dec 2, 2014 at 7:40 PM, Tom Lane wrote: > Davide S writes: > > This is a small testcase that reproduces the problem on my machine. > > Ah, I see it: json_agg_finalfn is violating the rule that an aggregate > final function can't scribble on the aggregate

Re: [GENERAL] Programmatic access to interval units

On 12/02/2014 10:40 AM, Nelson Green wrote: On Tue, Dec 2, 2014 at 11:57 AM, Merlin Moncure mailto:mmonc...@gmail.com>> wrote: Hi Merlin, I'm afraid I'm only confusing things, so let me give an example of what I am trying to do: -- Example ---

[GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ?

We are developing on and running Postgres 9.3.5 on FreeBsd 10.0-p12. We have been experiencing a intermittent postgres core dump which Seems primarily to be associated with the the 2 functions below. The area of interest is based on the content of the postgres log file which often indicates

Re: [GENERAL] Programmatic access to interval units

On Tue, Dec 2, 2014 at 11:57 AM, Merlin Moncure wrote: > On Tue, Dec 2, 2014 at 9:48 AM, Nelson Green > wrote: > > On Mon, Dec 1, 2014 at 2:14 PM, Merlin Moncure > wrote: > >> On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green > >> wrote: > >> > Good morning list, > >> > > >> > According to the doc

Re: [GENERAL] JSON_AGG produces extra square brakets

Davide S writes: > This is a small testcase that reproduces the problem on my machine. Ah, I see it: json_agg_finalfn is violating the rule that an aggregate final function can't scribble on the aggregate state. Will fix, thanks for the report! regards, tom lane -- Se

Re: [GENERAL] JSON_AGG produces extra square brakets

This is a small testcase that reproduces the problem on my machine. DB SETUP createdb --username=myuser --owner=myuser --encoding=UTF8 testcase CREATE TABLE thing_template ( id serial PRIMARY KEY ); INSERT INTO thing_template VALUES ( 1 );

Re: [GENERAL] Programmatic access to interval units

On Tue, Dec 2, 2014 at 10:16 AM, Melvin Davidson wrote: > > > > > > > > > > > > > > > > > > > > > > *I'm pretty sure the interval values are buried in the code, but there is > nothing to prevent you from creating your own reference table. :) CREATE > TABLE time_intervals( time_interval_name var

Re: [GENERAL] Programmatic access to interval units

On Tue, Dec 2, 2014 at 9:48 AM, Nelson Green wrote: > On Mon, Dec 1, 2014 at 2:14 PM, Merlin Moncure wrote: >> On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green >> wrote: >> > Good morning list, >> > >> > According to the documentation for interval data type inputs, the unit >> > can >> > be one of

Re: [GENERAL] update several columns from function returning several values

=?UTF-8?Q?R=C3=A9mi_Cura?= writes: > IF I define a function returning several values , I can't use it to update > mutliple columns of a table at once. > ... > UPDATE test_update_m_values SET (gid_plus_1,gid_minus_1) = > (rc_test_update_m_values(gid)); --doesn't work > Somebody now if this is poss

Re: [GENERAL] Programmatic access to interval units

*I'm pretty sure the interval values are buried in the code, but there is nothing to prevent you from creating your own reference table. :) CREATE TABLE time_intervals( time_interval_name varchar(15) NOT NULL, CONSTRAINT time_intervals_pk PRIMARY KEY (time_interval_name));INSERT INTO time_interva

[GENERAL] Trying to get SSPI/JDBC working

I am new to Postgresql. I have a postgres server running on a windows platform. The DBs will not have any remote access, only users logged onto the localhost will have access. SSPI seems like a great authentication protocol to use in this case. I believe I have followed all the instructions on

[GENERAL] update several columns from function returning several values

Hey, a trivial question I guess, can't make it work. IF I define a function returning several values , I can't use it to update mutliple columns of a table at once. i __don't__ want to use CTE or subquerry, and of course I don't wan tto compute the function several time. CREATE TABLE test_update_

Re: [GENERAL] Problem with pg_dump and decimal mark

Seems you have a locale mismatch issue. The dump is coming from a locale >>> where a '.' is the decimal mark and is being restored to a locale where >>> ',' is the mark. Look at what the locales are the machines that work and >>> the one that does not. >>> >> >> I have already done that and found s

[GENERAL] Postgres 9.2 PITR testing to before DROP DATABASE ends up removing file system files

I am testing out point in time recovery from a hot physical backup in a disaster recovery situation - I turned on archiving of files, created a hot physical backup, then (after letting it run for a few days) issued a "DROP DATABASE". The pg_log file shows the DROP DATABASE command was issued at '2

Re: [GENERAL] Auto vacuum not running -- Could not bind socket for statistics collector

=?UTF-8?Q?Tim_Sch=C3=A4fer?= writes: > After starting the server with pg_ctl start, I get the following entries in > the > logs: > 2014-12-02 15:27:36 CET LOG: could not bind socket for statistics > collector: Cannot assign requested address > 2014-12-02 15:27:36 CET LOG: disabling sta

Re: [GENERAL] Programmatic access to interval units

On Mon, Dec 1, 2014 at 2:14 PM, Merlin Moncure wrote: > On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green > wrote: > > Good morning list, > > > > According to the documentation for interval data type inputs, the unit > can > > be one of microsecond, millisecond, second, minute, hour, day, week, > mo

[GENERAL] Auto vacuum not running -- Could not bind socket for statistics collector

Dear list, I am having trouble running PostgreSQL 9.3 under OpenSuSE because auto vacuum does not seem to work. Here are the details on my version: # select version(); PostgreSQL 9.3.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.8.1 20130909 [gcc-4_8-branch revision 202388], 64-bit

Re: [GENERAL] Partitioning of a dependent table not based on date

On 01/12/2014, at 19:26, Andy Colson wrote: > On 12/1/2014 11:14 AM, Herouth Maoz wrote: >> I am currently in the process of creating a huge archive database that >> contains data from all of our systems, going back for almost a decade. >> >> Most of the tables fall into one of two categories: >

Re: [GENERAL] Serialization exception : Who else was involved?

Olivier MATROT wrote: > Serialization conflict detection is done in > src/backend/storage/lmgr/predicate.c, where transactions > that are doomed to fail are marked as such with the SXACT_FLAG_DOOMED flag. > > I simply added elog(NOTIFY,...) calls with the DEBUG1 level, each time the > flag is se

Re: [GENERAL] Serialization exception : Who else was involved?

Hello, I pushed the logs to the DEBUG5 level with no luck. So I decided to take a look at the code. Serialization conflict detection is done in src/backend/storage/lmgr/predicate.c, where transactions that are doomed to fail are marked as such with the SXACT_FLAG_DOOMED flag. I simp