Re: [GENERAL] Very slow update / hash join

On Wed, May 4, 2016 at 3:22 PM, Kurt Roeckx wrote: > Hi, > > I have an update query that's been running for 48 hours now. > Since it started it used about 2.5% CPU, and is writing to the > disk at about 3 MB/s, and reading at about 2 MB/s. It's mostly > waiting for the disks. The easiest way to

Re: [GENERAL] Debian and Postgres

Hello Adrian,On Thu, 2016-05-05 at 13:47 -0700, Adrian Klaver wrote: >  > Exactly. Showing the list the error you get when you cannot connect > help  > may with solving that problem and save you a great of time. What > have  > you got to lose? > I have nothing to "lose". There is NO error, per se

[GENERAL] Trying to create array of enum to array of text for exclusion constraint

Using Pg 9.5.2 on linux. Trying to create an exclusion constraint on an array of enums. Ultimate goal is having a constraint that excludes records with overlapping elements. This must have been done before, I just cannot find any examples. I realize there isn't a q&d way to convert enums to int

Re: [GENERAL] How to manually force a transaction wraparound

On Fri, Apr 29, 2016 at 10:16 PM Thomas Munro wrote: > On Sat, Apr 30, 2016 at 10:48 AM, Eric Ridge wrote: > > I want to force my database to wraparound, just to see what happens. How > > can I do this without consuming a few billion transactions? > > Take a look at the script repro-bogus-subtr

Re: [GENERAL] CREATE OR REPLACE AGGREGATE -- NOT!

Thanks. Much as I expected. No, I don’t think it would be hard to do. Maybe if I ever feel a pressing need to learn how to submit a patch it might be something to start with. Regards David M Bennett FACS _ Andl - A New Database Language - andl.org From: pgsql-general-ow..

Re: [GENERAL] Function PostgreSQL 9.2

It's working now... Final code: ALTER TABLE public.companies ADD COLUMN client_code_increment integer; > ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT > NULL; > ALTER TABLE public.companies ALTER COLUMN client_code_increment SET > DEFAULT 1000; > COMMIT TRANSACTION; > >

Re: [GENERAL] Function PostgreSQL 9.2

David G. Johnston wrote: ​Berend already identified the problem for you. Thank you. -- 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] Function PostgreSQL 9.2

On Thu, May 5, 2016 at 6:17 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > > If I change that to company_id, I get the error: column "company_id" does >> not exist, because that column is inside USERS and not COMPANIES. >> >> > ​change that what is "that" > > ​Provide the actua

Re: [GENERAL] Function PostgreSQL 9.2

> If I change that to company_id, I get the error: column "company_id" does > not exist, because that column is inside USERS and not COMPANIES. > > ​change that what is "that" ​Provide the actual code you ran that resulted in "column "company_id" does not exist"​ Your attempts at brevity ar

Re: [GENERAL] Function PostgreSQL 9.2

> > > > 2) You have a where clause: company_id = NEW.id >> 3) NEW refers to users >> 4) NEW.id is obstensibly a USER ID >> > > > No... > > ​Which one of the three do you disagree with? ​

Re: [GENERAL] Function PostgreSQL 9.2

> > > 1) You attached users_code_seq() to a trigger on the users table. > yes > 2) You have a where clause: company_id = NEW.id > 3) NEW refers to users > 4) NEW.id is obstensibly a USER ID > No... CREATE OR REPLACE FUNCTION users_code_seq() >RETURNS "trigger" AS $$ > DECLARE code charac

Re: [GENERAL] Debian and Postgres

On 05/05/2016 01:40 PM, rob stone wrote: Hello John,On Wed, 2016-05-04 at 21:43 -0700, John R Pierce wrote: On 5/4/2016 1:55 PM, rob stone wrote: I can connect via psql and issue queries without any problems. Trying to connect via JDBC fails. Trying to connect by an application fails. one pot

Re: [GENERAL] Function PostgreSQL 9.2

On Thu, May 5, 2016 at 1:22 PM, drum.lu...@gmail.com wrote: > > > On 6 May 2016 at 02:29, David G. Johnston > wrote: > >> On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys >> wrote: >> >>> >>> > On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote: >>> >>> > The final function code is: >>> > >>> >

Re: [GENERAL] Debian and Postgres

Hello John,On Wed, 2016-05-04 at 21:43 -0700, John R Pierce wrote: > On 5/4/2016 1:55 PM, rob stone wrote: > > I can connect via psql and issue queries without any problems. > > Trying > > to connect via JDBC fails. Trying to connect by an application > > fails. >   > one potential difference, psql

Re: [GENERAL] Debian and Postgres

On 05/05/2016 01:29 PM, rob stone wrote: Hello Adrian,On Wed, 2016-05-04 at 21:08 -0700, Adrian Klaver wrote: So Debian does not rotate the logs into history.log..gz? Yes, it does! Didn't realise it. You learn something every day. 23 packages removed and 31 purged. Going thru the list slow

Re: [GENERAL] Debian and Postgres

Hello Adrian,On Wed, 2016-05-04 at 21:08 -0700, Adrian Klaver wrote: >  > So Debian does not rotate the logs into history.log..gz? > Yes, it does! Didn't realise it. You learn something every day. 23 packages removed and 31 purged. Going thru the list slowly. Thanks, Rob -- Sent via pgsql-

Re: [GENERAL] Function PostgreSQL 9.2

On 6 May 2016 at 02:29, David G. Johnston wrote: > On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys wrote: > >> >> > On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote: >> >> > The final function code is: >> > >> > CREATE OR REPLACE FUNCTION users_code_seq() >> >RETURNS "trigger" AS $$ >> > D

Re: [GENERAL] psql color hostname prompt

BTW, I just noticed that as of 9.5 there is an optional GUC called cluster_name. Unfortunately I don't see a way to reference it in the prompt string. I'll suggest that as a feature. My earlier hack will work but in 9.5 use cluster_name instead of making up a fake extension variable. Cheers, Steve

Re: [GENERAL] PostgreSQL and Windows 10 exception 0xC0000018

On 5/5/2016 1:17 PM, Moreno Andreo wrote: Il 05/05/2016 18:40, George Neuner ha scritto: Otherwise: if Postgresql is loading any non-standard extensions, I would try to check those DLLs. If you have a recent Visual Studio handy, run "link /dump /headers " on the DLLs and look for any that say "

Re: [GENERAL] PostgreSQL and Windows 10 exception 0xC0000018

Disclaimer: I do not run Postgresql on Windows. On Thu, 5 May 2016 14:39:25 +0200, Moreno Andreo wrote: >a strange error is happening to some of our customers. >They all have a Windows 10 installation on their machines with >our application and, of course, PostgreSQL 9.1 installed >(migrat

Re: [GENERAL] Possible causes for "tuple concurrently updated" error

Arg, should have included this in my initial email :-( 9.3.11 On Thu, May 5, 2016 at 7:46 AM, Alvaro Herrera wrote: > On Wed, May 04, 2016 at 11:52:47PM -0700, Jacob Scott wrote: > > Hi, > > > > I'm seeing a "tuple concurrently updated" error thrown while executing > > UPDATE statements. I've a

Re: [GENERAL] Possible causes for "tuple concurrently updated" error

Pinpoint updates of the form UPDATE $TABLE set field1=$FOO, field2=$BAR ... WHERE pk=$ID - All fields are specified - Table has no foreign keys (but does have a unmber of indexes) - executed as a prepared statement with bind params & data for multiple rows provided On Thu, May 5,

Re: [GENERAL] Possible causes for "tuple concurrently updated" error

Jacob Scott wrote: > Arg, should have included this in my initial email :-( > > 9.3.11 OK. So what are the updates doing? Are there any FKs involved? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent v

Re: [GENERAL] Possible causes for "tuple concurrently updated" error

On Wed, May 04, 2016 at 11:52:47PM -0700, Jacob Scott wrote: > Hi, > > I'm seeing a "tuple concurrently updated" error thrown while executing > UPDATE statements. I've attempted to diligently review previous threads on > this error (e.g., > https://www.google.com/webhp?ie=UTF-8#q=tuple+concurrentl

[GENERAL] Possible causes for "tuple concurrently updated" error

Hi, I'm seeing a "tuple concurrently updated" error thrown while executing UPDATE statements. I've attempted to diligently review previous threads on this error (e.g., https://www.google.com/webhp?ie=UTF-8#q=tuple+concurrently+updated+update+site:postgresql.org) but am confused about what classes

Re: [GENERAL] Function PostgreSQL 9.2

On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys wrote: > > > On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote: > > > The final function code is: > > > > CREATE OR REPLACE FUNCTION users_code_seq() > >RETURNS "trigger" AS $$ > > DECLARE code character varying; > > BEGIN > > IF NEW.co

[GENERAL] PostgreSQL and Windows 10 exception 0xC0000018

Hi all,     a strange error is happening to some of our customers. They all have a Windows 10 installation on their machines with our application and, of course, PostgreSQL 9.1 installed (migration to 9.5 upcoming in late summer/fall, but not applicable by now) W

Re: [GENERAL] Thoughts on "Love Your Database"

On Thu, May 5, 2016 at 12:34 AM, Vincent Veyron wrote: > I like this quote from Fred Brooks : > > `Show me your code and conceal your data structures, and I shall continue to > be mystified. Show me your data structures, and I won't usually need your > code; it'll be obvious.' I remembered it a

Re: [GENERAL] Function PostgreSQL 9.2

> On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote: > The final function code is: > > CREATE OR REPLACE FUNCTION users_code_seq() >RETURNS "trigger" AS $$ > DECLARE code character varying; > BEGIN > IF NEW.code IS NULL THEN > SELECT client_code_increment INTO STRICT NEW.co

Re: [GENERAL] Function PostgreSQL 9.2

> > >> SELECT client_code_increment INTO STRICT NEW.code FROM >> public.companies WHERE id = >> NEW.id ORDER BY client_code_increment DESC; >> > > > > I am pretty sure the above line is wrong. NEW.id refers to users.id, not > the companies.id. Also, the implementation presents a po

Re: [GENERAL] Function PostgreSQL 9.2

drum.lu...@gmail.com wrote: I'm just having some problem when doing: INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,company_id) VALUES (66,'tes...@test.com ','password','0','2016-05-03 00:01:01','2016-05-03 00: