Re: [GENERAL] UPDATE syntax change

Adam Brusselback writes: > --works > UPDATE tst_table > SET (b, c) = ('help me', 'please') > WHERE a = 0; > --does not work > UPDATE tst_table > SET (b) = ('help me') > WHERE a = 0; > So there was a change made, and you now cannot use the multi-column > syntax if you're only updating a single col

Re: [GENERAL] UPDATE syntax change (column-list UPDATE syntax fails with single column)

Appreciate the link, didn't come up when I was googling the issue. As you said, a mention in the release notes would have been helpful. Thanks, -Adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/p

Re: [GENERAL] UPDATE syntax change (column-list UPDATE syntax fails with single column)

On Mon, Oct 30, 2017 at 12:29:03PM -0400, Adam Brusselback wrote: > I have some queries that were working in 9.6 which suddenly broke when > moving to 10. > > Digging in, the error i'm getting is: ERROR: source for a > multiple-column UPDATE item must be a sub-SELECT or ROW() expression > So the

[GENERAL] UPDATE syntax change

Hey all, just getting around to updating my development environment to Postgres 10, and there was something I found while in testing. Version info: PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit I have some queries that were working in 9.6 which s

Re: [GENERAL] UPDATE column without FK fires other FK triggers constraint check

Thanks for the explanation! Can these checks be implemented or the data needed is not there and adding it will only add an overhead for the majority of use cases? 2017-07-19 20:42 GMT+02:00 Tom Lane : > Luca Looz writes: > > After some tests it seems that this happens when the same row is covere

Re: [GENERAL] UPDATE column without FK fires other FK triggers constraint check

Luca Looz writes: > Thanks for the explanation! > Can these checks be implemented or the data needed is not there and adding > it will only add an overhead for the majority of use cases? It's hard to see how to do much better than we're doing without storing more data on-disk than is there now.

Re: [GENERAL] UPDATE column without FK fires other FK triggers constraint check

Luca Looz writes: > After some tests it seems that this happens when the same row is covered by > more than 1 update in the same transaction even without any change. > Is this an expected behavior? Why it happens? Yes, see comment in RI_FKey_fk_upd_check_required: * If the original

[GENERAL] UPDATE column without FK fires other FK triggers constraint check

I was analyzing an update function and i have noticed "Trigger for constraint" entries for foreign keys that i wasn't using or referring. After some tests it seems that this happens when the same row is covered by more than 1 update in the same transaction even without any change. Here is a dbfidd

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Yasin, thank you for this suggestion, but - On Tue, Mar 14, 2017 at 12:07 PM, Yasin Sari wrote: ​​ >> Have you tried with inner block and do nothing on exception; >> > > >> BEGIN >> >> >> .. >> BEGIN >> >> UPDATE words_social >> SET uid = out_uid

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

The convention on these lists is to inline or bottom-post. On Tue, Mar 14, 2017 at 12:07 PM, Yasin Sari wrote: > ​​ > Have you tried with inner block and do nothing on exception; > > ​I suppose that would work - though using an exception path for expected logic is messy - and considerably slower

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Have you tried with inner block and do nothing on exception; BEGIN .. BEGIN UPDATE words_social SET uid = out_uid WHERE uid = ANY(_uids); EXCEPTION WHEN OTHERS THEN --do nothing or write NULL means do nothing

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On Tue, Mar 14, 2017 at 4:55 AM, Alexander Farber wrote: > I wish there would be possibility to add ON CONFLICT DO NOTHING /* and drop > the review */ to the both UPDATE's above, but there is no such thing > described at https://www.postgresql.org/docs/9.5/static/sql-update.html Heikki wanted to

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On Tue, Mar 14, 2017 at 1:25 PM, Alexander Farber < alexander.far...@gmail.com> wrote: > > But this might give me conflicts, because there might be such a PK > already... > > You need to remove the "might" and figure out which ones will and which will not. You can update the ones will not and del

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 01:25 PM, Alexander Farber wrote: Hi Adrian - On Tue, Mar 14, 2017 at 7:18 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 03/14/2017 09:00 AM, Alexander Farber wrote: My initial idea has been not to use ON CONFLICT at all, but instead only

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Hi Adrian - On Tue, Mar 14, 2017 at 7:18 PM, Adrian Klaver wrote: > On 03/14/2017 09:00 AM, Alexander Farber wrote: > >> My initial idea has been not to use ON CONFLICT at all, but instead only >> UPDATE the words_reviews records (set "uid" or "author" to out_uid) for >> which NO EXISTS already

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 09:00 AM, Alexander Farber wrote: My initial idea has been not to use ON CONFLICT at all, but instead only UPDATE the words_reviews records (set "uid" or "author" to out_uid) for which NO EXISTS already such a record with PK (uid, author)... and then I am afraid the logic is escap

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

My initial idea has been not to use ON CONFLICT at all, but instead only UPDATE the words_reviews records (set "uid" or "author" to out_uid) for which NO EXISTS already such a record with PK (uid, author)... and then DELETE the rest (as I can't update them without a conflict) but I haven't figured

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 08:38 AM, Alexander Farber wrote: Yes, Adrian - On Tue, Mar 14, 2017 at 3:57 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 03/14/2017 07:23 AM, Alexander Farber wrote: in _uids array I have all user ids of player. I want to merge his or her

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Yes, Adrian - On Tue, Mar 14, 2017 at 3:57 PM, Adrian Klaver wrote: > On 03/14/2017 07:23 AM, Alexander Farber wrote: > >> >> in _uids array I have all user ids of player. >> >> I want to merge his or her data, including reviews, to a single user id: >> out_uid. >> >> So I make a copy of related

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 07:23 AM, Alexander Farber wrote: Hi Adrian, in _uids array I have all user ids of player. I want to merge his or her data, including reviews, to a single user id: out_uid. So I make a copy of related words_reviews records (where this user has Alright I see that you are setting

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Hi Adrian, in _uids array I have all user ids of player. I want to merge his or her data, including reviews, to a single user id: out_uid. So I make a copy of related words_reviews records (where this user has been rated or this user has rated someone) and then re-INSERT ON CONFLICT DO NOTHING t

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 06:52 AM, Alexander Farber wrote: I have come up with the following (when trying to merge array _uids to a single out_uid): -- try to copy as many reviews of this user as possible INSERT INTO words_reviews ( uid,

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Sorry, missed the last DELETE: DELETE FROM words_reviews WHERE author <> out_uid AND author = ANY(_uids);

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

I have come up with the following (when trying to merge array _uids to a single out_uid): -- try to copy as many reviews of this user as possible INSERT INTO words_reviews ( uid, author, nice,

[GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Good afternoon, I am working on a small game, where the mobile app initially sends social network user data (name, city, gender) to PostgreSQL 9.5 backend and I store that data in a table: CREATE TABLE words_social ( sid varchar(255) NOT NULL, social integer NOT NULL CHECK (0 <=

Re: [GENERAL] update error with serializable

On Fri, Jan 20, 2017 at 11:27 AM, Rob Sargent wrote: > On 01/20/2017 10:05 AM, Kevin Grittner wrote: >> https://www.postgresql.org/message-id/flat/d8joa0eh9yw@dalvik.ping.uio.no#d8joa0eh9yw@dalvik.ping.uio.no > Configurable or dynamic? Wouldn't something related to tuples per page (and

Re: [GENERAL] update error with serializable

On 01/20/2017 10:05 AM, Kevin Grittner wrote: On Fri, Jan 20, 2017 at 4:44 AM, Tom DalPozzo wrote: I've two threads countinuously updataing rows in the same table. Each one does: BEGIN, UPDATE,UPDATECOMMIT There can't be two active transactions updating the same row (my bug apart but I d

Re: [GENERAL] update error with serializable

On Fri, Jan 20, 2017 at 4:44 AM, Tom DalPozzo wrote: > I've two threads countinuously updataing rows in the same table. > Each one does: BEGIN, UPDATE,UPDATECOMMIT > There can't be two active transactions updating the same row (my > bug apart but I don't think so). > I'm using default_transac

[GENERAL] update error with serializable

Hi, I've two threads countinuously updataing rows in the same table. Each one does: BEGIN, UPDATE,UPDATECOMMIT There can't be two active transactions updating the same row (my bug apart but I don't think so). I'm using default_transaction_isolation = 'serializable' I get "could not serialize ac

Re: [GENERAL] update records to have occurance number

HI: You can use windows functions. https://www.postgresql.org/ docs/9.5/static/tutorial-window.html specifically row_number(). https://www.postgresql.org/ docs/9.5/static/tutorial-window.html I'm assuming that your ordering is based on lname and then fname. The query for getting 'number' is san

Re: [GENERAL] update records to have occurance number

> On 25 Oct 2016, at 17:06, Mark Lybarger wrote: > > I want to update a table to have the value of the occurrence number. For > instance, I have the below table. I want to update the number column to > increment the count of last name occurrences, so that it looks like this: > > first last

[GENERAL] update records to have occurance number

I want to update a table to have the value of the occurrence number. For instance, I have the below table. I want to update the number column to increment the count of last name occurrences, so that it looks like this: first last 1 second last 2 third last 3 first other 1 next other 2 Here's my

Re: [GENERAL] Update two tables returning id from insert CTE Query

On Tue, Sep 27, 2016 at 2:31 PM, Patrick B wrote: > 2016-09-28 10:25 GMT+13:00 Patrick B : > >> >> Actually I can't use name_last or name_first because some of the rows > have name_last/name_first = null > > I'm inserting more columns that I shown: > > CREATE TABLE >> public.not_monthly >>

Re: [GENERAL] Update two tables returning id from insert CTE Query

2016-09-28 10:25 GMT+13:00 Patrick B : > > > 2016-09-28 10:11 GMT+13:00 Kevin Grittner : > >> On Tue, Sep 27, 2016 at 3:33 PM, Patrick B >> wrote: >> >> > 4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into >> table_2.c_id >> > - This is the problem.. how can I get the inserted id fro

Re: [GENERAL] Update two tables returning id from insert CTE Query

2016-09-28 10:11 GMT+13:00 Kevin Grittner : > On Tue, Sep 27, 2016 at 3:33 PM, Patrick B > wrote: > > > 4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into > table_2.c_id > > - This is the problem.. how can I get the inserted id from STEP2 and put > it > > into c_id respecting the ord

Re: [GENERAL] Update two tables returning id from insert CTE Query

On Tue, Sep 27, 2016 at 3:33 PM, Patrick B wrote: > 4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into table_2.c_id > - This is the problem.. how can I get the inserted id from STEP2 and put it > into c_id respecting the order? For DML you need to think of the data as being unordere

Re: [GENERAL] Update two tables returning id from insert CTE Query

2016-09-28 9:23 GMT+13:00 Kevin Grittner : > On Tue, Sep 27, 2016 at 2:59 PM, Patrick B > wrote: > > [sel is a relation which can have multiple rows; the fact that it > is being generated in a CTE isn't relevant for purposes of the > error.] > > UPDATE table_2 SET c_id = > ( > SE

Re: [GENERAL] Update two tables returning id from insert CTE Query

On Tue, Sep 27, 2016 at 2:59 PM, Patrick B wrote: [sel is a relation which can have multiple rows; the fact that it is being generated in a CTE isn't relevant for purposes of the error.] UPDATE table_2 SET c_id = ( SELECT c_id FROM sel ORDER BY c_id ) WHE

Re: [GENERAL] Update two tables returning id from insert CTE Query

On Tue, Sep 27, 2016 at 1:10 PM, Igor Neyman wrote: > > > Patrick, > > > > You need to explain your problems in more “coherent” way, David suggested > one. > > If you aren’t willing, people will stop responding to your request, they > are not obligated to read your mind. > > > ​I'll put it this

Re: [GENERAL] Update two tables returning id from insert CTE Query

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Patrick B Sent: Tuesday, September 27, 2016 4:00 PM To: David G. Johnston Cc: pgsql-general Subject: Re: [GENERAL] Update two tables returning id from insert CTE Query 2016-09-28 8:54 GMT+13:00

Re: [GENERAL] Update two tables returning id from insert CTE Query

2016-09-28 8:54 GMT+13:00 David G. Johnston : > On Mon, Sep 26, 2016 at 9:06 PM, Patrick B > wrote: > >> >> I'm doing this now: >> >> >> sel AS ( >>> SELECT i.id AS c_id >>> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) >>> i >>> JOIN rows s USING (rn) >>> ) >>> UPDATE

Re: [GENERAL] Update two tables returning id from insert CTE Query

On Mon, Sep 26, 2016 at 9:06 PM, Patrick B wrote: > > I'm doing this now: > > > sel AS ( >> SELECT i.id AS c_id >> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i >> JOIN rows s USING (rn) >> ) >> UPDATE table_2 SET c_id = >> ( >> SELECT c_id >> FROM sel >> ORDER

Re: [GENERAL] Update two tables returning id from insert CTE Query

2016-09-28 0:29 GMT+13:00 Vitaly Burovoy : > On 9/26/16, Patrick B wrote: > > 2016-09-27 16:22 GMT+13:00 Patrick B : > > I'm doing this now: > > > > sel AS ( > >> SELECT i.id AS c_id > >> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) > >> i > >> JOIN rows s USING (rn) >

Re: [GENERAL] Update two tables returning id from insert CTE Query

On 9/26/16, Patrick B wrote: > 2016-09-27 16:22 GMT+13:00 Patrick B : > I'm doing this now: > > sel AS ( >> SELECT i.id AS c_id >> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) >> i >> JOIN rows s USING (rn) >> ) >> UPDATE table_2 SET c_id = >> ( >> SELECT c_id >> FR

Re: [GENERAL] Update two tables returning id from insert CTE Query

On 9/26/16, Patrick B wrote: > Hi guys, > > I've got 2k rows in a table: ... > So I'd imagine now I would do the update? How can I update table_2.c_id > with the ins_table_1.id value? > I'm using Postgres 9.2 > > Thanks > Patrick > Hello, It is not possible to change one row more than once by

Re: [GENERAL] Update two tables returning id from insert CTE Query

2016-09-27 16:22 GMT+13:00 Patrick B : > Hi guys, > > I've got 2k rows in a table: > >> CREATE TABLE >> public.not_monthly >> ( >> id BIGINT DEFAULT "nextval"('"id_seq"'::"regclass") NOT NULL, >> clientid BIGINT, >> name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING

[GENERAL] Update two tables returning id from insert CTE Query

Hi guys, I've got 2k rows in a table: > CREATE TABLE > public.not_monthly > ( > id BIGINT DEFAULT "nextval"('"id_seq"'::"regclass") NOT NULL, > clientid BIGINT, > name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING, > name_last CHARACTER VARYING(80) DEFAULT ''::

Re: [GENERAL] UPDATE OR REPLACE?

> >>> In my particular situation the case I care about is when the > result > >> of an UPDATE is two identical rows. All I really want is a DISTINCT > >> option. > >> > >> Assuming I am following correctly what you want is that the result > of > >> an UPDATE not be two identical rows. > > > > Corre

Re: [GENERAL] UPDATE OR REPLACE?

On 09/01/2016 05:08 PM, dandl wrote: In my particular situation the case I care about is when the result of an UPDATE is two identical rows. All I really want is a DISTINCT option. Assuming I am following correctly what you want is that the result of an UPDATE not be two identical rows. Corre

Re: [GENERAL] UPDATE OR REPLACE?

> > In my particular situation the case I care about is when the result > of an UPDATE is two identical rows. All I really want is a DISTINCT > option. > > Assuming I am following correctly what you want is that the result of > an UPDATE not be two identical rows. Correct. In practice I don't car

Re: [GENERAL] UPDATE OR REPLACE?

On 09/01/2016 07:37 AM, dandl wrote: Sqlite has options to handle an update that causes a duplicate key. Is there anything similar in Postgres? This is not an UPSERT. The scenario is an UPDATE that changes some key field so that there is now a duplicate key. In Sqlite this handled as: UPDAT

Re: [GENERAL] UPDATE OR REPLACE?

> > Sqlite has options to handle an update that causes a duplicate key. > Is > > there anything similar in Postgres? > > This is not an UPSERT. The scenario is an UPDATE that changes some > key > > field so that there is now a duplicate key. In Sqlite this handled > as: > > UPDATE OR IGNORE table S

Re: [GENERAL] UPDATE OR REPLACE?

On Thu, Sep 1, 2016 at 12:10 PM, dandl wrote: > Sqlite has options to handle an update that causes a duplicate key. Is > there anything similar in Postgres? > This is not an UPSERT. The scenario is an UPDATE that changes some key > field so that there is now a duplicate key. In Sqlite this handl

Re: [GENERAL] UPDATE OR REPLACE?

On Thu, Sep 1, 2016 at 12:10 PM, dandl wrote: > Sqlite has options to handle an update that causes a duplicate key. Is there > anything similar in Postgres? > This is not an UPSERT. The scenario is an UPDATE that changes some key field > so that there is now a duplicate key. In Sqlite this handled

[GENERAL] UPDATE OR REPLACE?

Sqlite has options to handle an update that causes a duplicate key. Is there anything similar in Postgres? This is not an UPSERT. The scenario is an UPDATE that changes some key field so that there is now a duplicate key. In Sqlite this handled as: UPDATE OR IGNORE table SET UPDATE OR R

Re: [GENERAL] Update statement results in Out of memory

Thanks for all for the good feedback, I realize that there's no problem with the configuration, and I assume that the full update statement is a bad decision by itself, so I made a batch process with single updates statements which commits every 50 single operation. What I can discover now is tha

Re: [GENERAL] Update statement results in Out of memory

You could check the max number of points in your geometries : SELECT max(ST_NumPoints(geom)) FROM ... Of course you could still have invalid / abberant geometry, which you could also check (ST_IsValid, St_IsSimple). You could solve both those hypotheses if you could perform your buffer by batch.

Re: [GENERAL] Update statement results in Out of memory

Running a multi-million row update will take a long time. It's possible you've exposed a memory leak in ST_Buffer (the older your version of GEOS, the more likely that is) but it's also possible you're just running a really long update. I find for batch processing purposes that creating fresh table

Re: [GENERAL] Update statement results in Out of memory

On 07/06/2016 02:13 AM, Ivan Bianchi wrote: Hello, I am trying to update a column using a PostGIS ST_Buffer function into a table of 4.257.769 rows, but after 6 hours, an /Out of memory/ error appears and the kernel starts killing processes until a /Kerne

[GENERAL] Update statement results in Out of memory

Hello, I am trying to update a column using a PostGIS ST_Buffer function into a table of 4.257.769 rows, but after 6 hours, an *Out of memory* error appears and the kernel starts killing processes until a *Kernel Panic* shows up. I have simplified the buff

Re: [GENERAL] Update multiple rows in a table with different values

Hi Tom, Thanks for your suggestion. I got it working: CREATE OR REPLACE FUNCTION s.updatefunc1(BigInt[], BigInt[]) RETURNS void as $$ BEGIN FOR i IN array_lower($1, 1) .. array_upper($1, 1) LOOP update s.t1 SET c3 = $2[i] WHERE c2 = $1[i]; END LOOP; END; $$ LAN

Re: [GENERAL] Update multiple rows in a table with different values

shankha writes: > PREPARE updatearrayplan(BigInt[], BigInt[]) AS > for i in size($1) > DO > update s.t1 > SET c3 = $2[$i] > WHERE c2 = $1[$i] > END FOR > In this prepared statement I am just trying to explain the algorithm. > I do not kn

Re: [GENERAL] Update multiple rows in a table with different values

Hi Adrian, I am using Postgres version 9.3. PREPARE updatearrayplan(BigInt[], BigInt[]) AS for i in size($1) DO update s.t1 SET c3 = $2[$i] WHERE c2 = $1[$i] END FOR In this prepared statement I am just trying to explain the algorithm. I

Re: [GENERAL] Update multiple rows in a table with different values

On Fri, Jul 1, 2016 at 10:26 AM, shankha wrote: > > PREPARE updatearrayplan(BigInt[], BigInt[]) AS > for i in size($1) > DO > update s.t1 > SET c3 = $2[$i] > WHERE c2 = $1[$i] > END FOR > > EXECUTE updatearrayplan({20, 30}, {275,

Re: [GENERAL] Update multiple rows in a table with different values

On 07/01/2016 07:26 AM, shankha wrote: Greetings, I have the following schema: CREATE TABLE "s"."t1" ( "c1" BigSerial PRIMARY KEY, "c2" BigInt NOT NULL, "c3" BigInt ) WITH (OIDS=FALSE); Unless you have a very old version of Postgres, OIDS=FALSE is the de

[GENERAL] Update multiple rows in a table with different values

Greetings, I have the following schema: CREATE TABLE "s"."t1" ( "c1" BigSerial PRIMARY KEY, "c2" BigInt NOT NULL, "c3" BigInt ) WITH (OIDS=FALSE); INSERT INTO s.t1 (c2, c3) VALUES (10, 100); INSERT INTO s.t1 (c2, c3) VALUES (20, 200); INSERT INT

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

Hi, What if we have a chain of slaves (master->slave->sec slave)? And if queries would run on each of them? or a config like master ->slave1 ->slave2 Is it ok to enable feedback on both slaves ? -- View this message in context: http://postgresql.nabble.com/Upd

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

On Thu, May 12, 2016 at 9:22 PM, Viswanath wrote: > Hi, > Thank you for the explanation. > The slave queries are mostly reporting queries,which sometimes would take > 30+ running time due to complex joins and criteria. We haven't tried running > these queries on master before splitting, and it is

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

Hi, Thank you for the explanation. The slave queries are mostly reporting queries,which sometimes would take 30+ running time due to complex joins and criteria. We haven't tried running these queries on master before splitting, and it is possible that there will be heavy updates and deletes on mast

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

On Thu, May 12, 2016 at 11:14 AM, Viswanath wrote: > Hi Jeff, > Yes I am turning off autovacuum for experimental purpose only. I was > curious to know what is causing the queries to be killed when the autovacuum > is not running. I guess it had to be the pruning of HOT update chains like > you ha

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

Hi Jeff, Yes I am turning off autovacuum for experimental purpose only. I was curious to know what is causing the queries to be killed when the autovacuum is not running. I guess it had to be the pruning of HOT update chains like you have mentioned. Also I have already tried changing the parameter

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

Hi, I am using postgres 9.5 Yes,I restarted the server after changing the autovacuum to off. Also verified that autovacuum process is not running. standby db configurations: (mostly default conf only) hot_standby = on max_standby_archive_delay = 30s max_standby_streaming_delay = 30s wal_receiver_s

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

On Thu, May 12, 2016 at 6:37 AM, Viswanath wrote: > Hi, > I have the following configurations. > > On master: > autovacuum=off > vacuum_defer_cleanup_age=0 > > On slave: > hot_standby_feedback = off > max_standby_streaming_delay = 30s > > Now when I try to run a long query on slave and do some up

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

On Thu, 12 May 2016 21:56 Viswanath, wrote: > Hi, > I have the following configurations. > > On master: > autovacuum=off > Have you changed it recently or it has always been off? In case you changed it recently, a restart would be needed for this setting to take effect. vacuum_defer_cleanup_age

[GENERAL] Update or Delete causes canceling of long running slave queries

Hi, I have the following configurations. On master: autovacuum=off vacuum_defer_cleanup_age=0 On slave: hot_standby_feedback = off max_standby_streaming_delay = 30s Now when I try to run a long query on slave and do some update or delete on the table on master I am getting the following error.

Re: [GENERAL] Update field to a column from another table

The problem was a trigger in my DB, when I disabled it the data started to be updated. Lucas

Re: [GENERAL] Update field to a column from another table

On 04/21/2016 11:52 AM, drum.lu...@gmail.com wrote: So when I run: UPDATE ja_jobs t2 SET time_job = t1.time_job FROM junk.ja_test t1 WHERE t2.id = t1.id AND t2.time_job IS DISTINCT FROM t1.time_job; I get: UPDATE 2202 So I check the data

Re: [GENERAL] Update field to a column from another table

On Thursday, April 21, 2016, drum.lu...@gmail.com wrote: > So when I run: > > UPDATE ja_jobs t2 >> SET time_job = t1.time_job >> FROM junk.ja_test t1 >> WHERE t2.id = t1.id >> AND t2.time_job IS DISTINCT FROM t1.time_job; > > > I get: > > UPDATE 2202 > > So I check the data by doing: > > select *

Re: [GENERAL] Update field to a column from another table

So when I run: UPDATE ja_jobs t2 > SET time_job = t1.time_job > FROM junk.ja_test t1 > WHERE t2.id = t1.id > AND t2.time_job IS DISTINCT FROM t1.time_job; I get: UPDATE 2202 So I check the data by doing: select * FROM public.ja_jobs WHERE id = 14574527 And the "time_job" field is null

Re: [GENERAL] Update field to a column from another table

Please don't top-post. > > -Original Message- > > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of > > drum.lu...@gmail.com > > Sent: Donnerstag, 21. April 2016 07:10 > > To: Postgres General &g

Re: [GENERAL] Update field to a column from another table

Donnerstag, 21. April 2016 07:10 > To: Postgres General > Subject: [GENERAL] Update field to a column from another table > > I've got two tables: > > - ja_jobs > - junk.ja_jobs_23856 > > I need to update the null column ja_jobs.time_job with the data from the

[GENERAL] Update field to a column from another table

I've got two tables: - ja_jobs - junk.ja_jobs_23856 I need to update the null column ja_jobs.time_job with the data from the table junk.ja_jobs_23856 So I'm doing: > > UPDATE public.ja_jobs AS b > SET time_job = a.time_job > FROM junk.ja_jobs_23856 AS a > WHERE a.id = b.id > AND a.clientid =

Re: [GENERAL] Update foreign table with trigger

El 26/02/16 a las 17:11, s d escribió: On 26 February 2016 at 21:02, Leonardo M. Ramé mailto:l.r...@griensu.com>> wrote: El 26/02/16 a las 16:49, s d escribió: On 26 February 2016 at 20:42, Leonardo M. Ramé mailto:l.r...@griensu.com>

Re: [GENERAL] Update foreign table with trigger

On 26 February 2016 at 21:02, Leonardo M. Ramé wrote: > > > El 26/02/16 a las 16:49, s d escribió: > >> On 26 February 2016 at 20:42, Leonardo M. Ramé > > wrote: >> >> >> >> >> Then try to do the update on the remote db directly. >> >> In the meantime co

Re: [GENERAL] Update foreign table with trigger

El 26/02/16 a las 16:49, s d escribió: On 26 February 2016 at 20:42, Leonardo M. Ramé mailto:l.r...@griensu.com>> wrote: Then try to do the update on the remote db directly. In the meantime could you provide the table and trigger definitions? I don't understand why th

Re: [GENERAL] Update foreign table with trigger

On 26 February 2016 at 20:42, Leonardo M. Ramé wrote: > > > El 26/02/16 a las 16:33, s d escribió: > >> On 26 February 2016 at 20:19, Leonardo M. Ramé > > wrote: >> >> >> >> El 26/02/16 a las 16:18, s d escribió: >> >> >> On 26 February 2016 at 20:02, Leonar

Re: [GENERAL] Update foreign table with trigger

El 26/02/16 a las 16:33, s d escribió: On 26 February 2016 at 20:19, Leonardo M. Ramé mailto:l.r...@griensu.com>> wrote: El 26/02/16 a las 16:18, s d escribió: On 26 February 2016 at 20:02, Leonardo M. Ramé mailto:l.r...@griensu.com>

Re: [GENERAL] Update foreign table with trigger

On 26 February 2016 at 20:19, Leonardo M. Ramé wrote: > > > El 26/02/16 a las 16:18, s d escribió: > >> >> On 26 February 2016 at 20:02, Leonardo M. Ramé > > wrote: >> >> >> El 26/02/16 a las 15:55, John R Pierce escribió: >> >> On 2/26/2016 10:29 AM, Leonar

Re: [GENERAL] Update foreign table with trigger

El 26/02/16 a las 16:18, s d escribió: On 26 February 2016 at 20:02, Leonardo M. Ramé mailto:l.r...@griensu.com>> wrote: El 26/02/16 a las 15:55, John R Pierce escribió: On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote: Hi, I created a Postgres_FDW table (TABLE_A) and

Re: [GENERAL] Update foreign table with trigger

On 26 February 2016 at 20:02, Leonardo M. Ramé wrote: > > El 26/02/16 a las 15:55, John R Pierce escribió: > >> On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote: >> >>> Hi, I created a Postgres_FDW table (TABLE_A) and need to do an update on >>> that table. >>> >>> As TABLE_A has a trigger, and the

Re: [GENERAL] Update foreign table with trigger

El 26/02/16 a las 15:55, John R Pierce escribió: On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote: Hi, I created a Postgres_FDW table (TABLE_A) and need to do an update on that table. As TABLE_A has a trigger, and the trigger does an insert on another table (TABLE_B), I had to create another fo

Re: [GENERAL] Update foreign table with trigger

On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote: Hi, I created a Postgres_FDW table (TABLE_A) and need to do an update on that table. As TABLE_A has a trigger, and the trigger does an insert on another table (TABLE_B), I had to create another foreign table called TABLE_B, that's ok. that tr

[GENERAL] Update foreign table with trigger

Hi, I created a Postgres_FDW table (TABLE_A) and need to do an update on that table. As TABLE_A has a trigger, and the trigger does an insert on another table (TABLE_B), I had to create another foreign table called TABLE_B, that's ok. At this point I have two foreign tables, TABLE_A and TABL

Re: [GENERAL] update inside function does not use the index

That solves my problem. Thanks!! Best regards Johannes Am 16.11.2015 um 18:19 schrieb Tom Lane: > Adrian Klaver writes: >> On 11/16/2015 08:03 AM, Johannes wrote: In every loop I execute an update with a where LIKE condition, which relates to my current cursor position: FOR i IN S

Re: [GENERAL] update inside function does not use the index

A function seams to be atomic for the analyze command (or?) EXPLAIN ANALYZE select my_function(); returns no inner query plan, just the costs, rows and width Am 16.11.2015 um 17:57 schrieb Adrian Klaver: > EXPLAIN ANALYZE select ... your_function(...); signature.asc Description: OpenPGP d

Re: [GENERAL] update inside function does not use the index

No, i did a mistake while simplifying it. It should be FOR i IN SELECT id, level, path_names||'%' as path_names from x LOOP update x set path_ids[i.level] = i.id where path_names like i.path_names; Sorry. Best regards Johannes Am 16.11.2015 um 15:10 schrieb Adrian Klaver: > On 11/16/2015 05:56

Re: [GENERAL] update inside function does not use the index

This helps me to understand for these common table expressions better. Thanks. This looks more elegant than the cursor variant. Limiting the cte to 10 records the update query needs 1.8 seconds. But the cursor variant ( 10 records ) was finished in 0.7 seconds. I guess it is faster, because behind

Re: [GENERAL] update inside function does not use the index

Adrian Klaver writes: > On 11/16/2015 08:03 AM, Johannes wrote: >>> In every loop I execute an update with a where LIKE condition, which >>> relates to my current cursor position: >>> FOR i IN SELECT id, level_ids, path_names||'%' as path_names from x LOOP >>> update x set path_ids[i.level] = id w

Re: [GENERAL] update inside function does not use the index

On 11/16/2015 08:03 AM, Johannes wrote: Ccing list No, i did a mistake while simplifying it. It should be FOR i IN SELECT id, level, path_names||'%' as path_names from x LOOP update x set path_ids[i.level] = i.id where path_names like i.path_names; So what do you see if you do?: EXPLAIN AN

Re: [GENERAL] update inside function does not use the index

Johannes schrieb am 16.11.2015 um 14:56: > I have problems with a self written function, which does not use the > index, which takes very long (500 ms per update). > > The pl/pgsql function iterates over a select resultset with a cursor. > In every loop I execute an update with a where LIKE condit

  1   2   3   4   5   6   7   >