Re: [GENERAL] Fast way to delete big table?

Charles Clavadetscher wrote > Hello > > This is quite weird... It there any other process locking the record or > the table? > Bye > Charles Yes, weird. The table is using by other process (keep inserting rows to the table) at the same time but no one should lock the row as we dont touch rows aft

Re: [GENERAL] Fast way to delete big table?

On Mon, May 16, 2016 at 10:22 PM, hmzha2 wrote: > Works ok in my testing environment but not > on the production machine. Select * from tableA limit 1; takes milliseconds > to finish update summary table from the previous select result, takes > milliseconds delete from tableA where primaryKey =

Re: [GENERAL] Fast way to delete big table?

Hello > The reason I dont have condition when selecting is it's faster than having. > Because my aim is to go through every row, find values of few columns and > append them to a summary table. Then > delete the row from the table. So find the rows on the fly is fine for me. > > I have tried to

Re: [GENERAL] Fast way to delete big table?

Hi Jeff,Thank you very much.>Your examples also have no selection criteria or stopping criteria (other>than when your outer driving script stops calling the function). That>means you might be deleting any arbitrary rows in the master table (not>just the oldest ones) and might proceed with these de

Re: [GENERAL] Fast way to delete big table?

Hi Charles, Thanks. The reason I dont have condition when selecting is it's faster than having. Because my aim is to go through every row, find values of few columns and append them to a summary table. Then delete the row from the table. So find the rows on the fly is fine for me. I have tried t

Re: [GENERAL] Ascii Elephant for text based protocols - Final

Hello On 05/16/2016 10:50 PM, Michael Paquier wrote: On Tue, May 17, 2016 at 5:13 AM, Karsten Hilbert wrote: select pg_logo(); seems like a good idea to me :-) If you propose a patch with a function that returns a setof text, I am sure it would get some attention. Though I think th

Re: [GENERAL] Increased I/O / Writes

This is my postgresql.conf at the moment: shared_buffer(51605MB) + effective_cache_size(96760MB) + work_mem(32MB) + max_connections(200) *= 148397.08 MB* My server has 128GB of RAM So, I'm using more RAM that I have. (not good at all) I'm glad that it wasn't me who put those confs in there :)

Re: [GENERAL] FIRST_VALUE argument must appear in group by?

On May 16, 2016, at 20:48 , David G. Johnston wrote: > > On Monday, May 16, 2016, Guyren Howe > wrote: > I have this SELECT clause as part of a larger query: > FIRST_VALUE(drs.id) OVER (PARTITION BY drs.order_ID ORDER BY drs.position > ASC) AS current_drs_id > Seems re

Re: [GENERAL] FIRST_VALUE argument must appear in group by?

On Monday, May 16, 2016, Guyren Howe wrote: > I have this SELECT clause as part of a larger query: > > FIRST_VALUE(drs.id) OVER (PARTITION BY drs.order_ID ORDER BY drs.position > ASC) AS current_drs_id > > Seems reasonable to me: group and sort the fields in this table and give > me the first va

[GENERAL] FIRST_VALUE argument must appear in group by?

I have this SELECT clause as part of a larger query: FIRST_VALUE(drs.id) OVER (PARTITION BY drs.order_ID ORDER BY drs.position ASC) AS current_drs_id Seems reasonable to me: group and sort the fields in this table and give me the first value. But I get "column "drs.id" must appear in the GROUP B

Re: [GENERAL] preventing ERROR: multixact "members" limit exceeded

On Mon, May 16, 2016 at 6:18 PM Alvaro Herrera wrote: > Not really. Your best bet is to reduce the > autovacuum_multixact_freeze_min_age limit, so that vacuums are able to > get rid of multixacts sooner (and/or reduce > autovacuum_multixact_freeze_table_age, so that whole-table vacuuming > takes

Re: [GENERAL] WAL files not being recycled

On 17/05/16 00:54, Scott Moynes wrote: > wal_keep_segments is set to 32. > > Here is the replication slot: > > slot_name| > n6lbb2vmohwuxoyk_00018732_f58b5354_79ad_4e6e_b18b_47acb1d7ce1f > plugin | test_decoding > slot_type| logical > datoid | 18732 > datab

[GENERAL] How to convert firebird stored procedures into postgresql functions

Hi there :) I have an internship project that consists in migrate a Firebird structure and data to a PostgreSQL one. I have already gotten the migration of tables, primary and foreign keys (and other constraints), indexes and views to a PostgreSQL database. But now I have 140 Firebird stored proced

Re: [GENERAL] WAL files not being recycled

wal_keep_segments is set to 32. Here is the replication slot: slot_name| n6lbb2vmohwuxoyk_00018732_f58b5354_79ad_4e6e_b18b_47acb1d7ce1f plugin | test_decoding slot_type| logical datoid | 18732 database | omitted active | f xmin |

Re: [GENERAL] preventing ERROR: multixact "members" limit exceeded

Steve Kehlet wrote: > Now it's just about preventing this. Our best guess at this point is the > autovacuums aren't working fast enough. Sure enough this instance has our > old values for: > autovacuum_vacuum_cost_delay: 20ms > autovacuum_vacuum_cost_limit: 200 > > We've since started using: > au

[GENERAL] preventing ERROR: multixact "members" limit exceeded

This is Postgres 9.4.4. Custom settings are [in this gist]( https://gist.github.com/skehlet/47c7f92daa0bd3d1a3aee2bb001da140). This is a new one for me, one of our bigger (~2.2TB) databases started having the following error: > Caused by: org.postgresql.util.PSQLException: ERROR: multixact "membe

Re: [GENERAL] WAL files not being recycled

On 05/16/2016 03:33 PM, Scott Moynes wrote: I have a PostgreSQL server that is not recycling WAL files. Log files are continually created and no old log files are ever removed. Running PostgreSQL v 9.4.8 with archive settings: archive_mode = on archive_command = /bin/true Checkpoint lo

Re: [GENERAL] WAL files not being recycled

Scott Moynes wrote: > I have a PostgreSQL server that is not recycling WAL files. Log files are > continually created and no old log files are ever removed. > > Running PostgreSQL v 9.4.8 with archive settings: > > archive_mode = on > archive_command = /bin/true > > Checkpoint logging is

[GENERAL] WAL files not being recycled

I have a PostgreSQL server that is not recycling WAL files. Log files are continually created and no old log files are ever removed. Running PostgreSQL v 9.4.8 with archive settings: archive_mode = on archive_command = /bin/true Checkpoint logging is enabled and does not record any logs

Re: [GENERAL] Any difference between commit/rollback when only temp tables and \copy are used

On Mon, May 16, 2016 at 4:08 PM, Adrian Klaver wrote: > On 05/16/2016 12:41 PM, David G. Johnston wrote: > >> I have a psql script that obtains data via the \copy command and loads >> it into a temporary table. Additional work is performed possibly >> generating additional temporary tables but n

Re: [GENERAL] Connections - Postgres 9.2

On 05/16/2016 02:25 PM, John R Pierce wrote: On 5/16/2016 2:11 PM, Adrian Klaver wrote: Yes a connection consumes resources. an idle connection consumes some memory, a process context, and a network socket. its not using CPU or disk IO. True, but the existence of poolers says that can be a

Re: [GENERAL] Connections - Postgres 9.2

On 5/16/2016 2:11 PM, Adrian Klaver wrote: Yes a connection consumes resources. an idle connection consumes some memory, a process context, and a network socket. its not using CPU or disk IO. True, but the existence of poolers says that can be an issue. I note that MRTG style graph showe

Re: [GENERAL] Connections - Postgres 9.2

On 05/16/2016 02:00 PM, John R Pierce wrote: On 5/16/2016 1:55 PM, Adrian Klaver wrote: Would the pool connection solve that IDLE connections? But more important than that, are the IDLE connections using the machine's resources ? Yes a connection consumes resources. an idle connection consum

Re: [GENERAL] Connections - Postgres 9.2

On 17 May 2016 at 08:56, Venkata Balaji N wrote: > > On Tue, May 17, 2016 at 6:28 AM, Lucas Possamai > wrote: > >> hmm.. thanks for all the answers guys... >> >> >> One more question: Those IDLE connections.. are using the server's >> resources? >> To solve that problem I would need a Pool conne

Re: [GENERAL] Connections - Postgres 9.2

On 5/16/2016 1:55 PM, Adrian Klaver wrote: Would the pool connection solve that IDLE connections? But more important than that, are the IDLE connections using the machine's resources ? Yes a connection consumes resources. an idle connection consumes some memory, a process context, and a net

Re: [GENERAL] Connections - Postgres 9.2

On Tue, May 17, 2016 at 6:28 AM, Lucas Possamai wrote: > hmm.. thanks for all the answers guys... > > > One more question: Those IDLE connections.. are using the server's > resources? > To solve that problem I would need a Pool connection, right? > > Would the pool connection solve that IDLE conn

Re: [GENERAL] Connections - Postgres 9.2

On 05/16/2016 01:28 PM, Lucas Possamai wrote: hmm.. thanks for all the answers guys... One more question: Those IDLE connections.. are using the server's resources? To solve that problem I would need a Pool connection, right? Yes and no. If your application/clients are generating connections

Re: [GENERAL] Ascii Elephant for text based protocols - Final

On Tue, May 17, 2016 at 5:13 AM, Karsten Hilbert wrote: > select pg_logo(); > > seems like a good idea to me :-) If you propose a patch with a function that returns a setof text, I am sure it would get some attention. Though I think that you should remove the mention of the 20th anniversa

Re: [GENERAL] Connections - Postgres 9.2

hmm.. thanks for all the answers guys... One more question: Those IDLE connections.. are using the server's resources? To solve that problem I would need a Pool connection, right? Would the pool connection solve that IDLE connections? But more important than that, are the IDLE connections using

Re: [GENERAL] Ascii Elephant for text based protocols - Final

select pg_logo(); seems like a good idea to me :-) Karsten > SQL version by Melvin Davidson: > > CREATE TABLE elephant > (row_num integer NOT NULL, > row_dat varchar(30) NOT NULL, > CONSTRAINT elephant_pk PRIMARY KEY (row_num) > ); > > INSERT INTO elephant > (row_num, row_dat) > VALU

Re: [GENERAL] Any difference between commit/rollback when only temp tables and \copy are used

On 05/16/2016 12:41 PM, David G. Johnston wrote: I have a psql script that obtains data via the \copy command and loads it into a temporary table. Additional work is performed possibly generating additional temporary tables but never any "real" tables. Then the script outputs, either to stdout o

Re: [GENERAL] Any difference between commit/rollback when only temp tables and \copy are used

On Mon, May 16, 2016 at 3:56 PM, Alan Hodgson wrote: > On Monday, May 16, 2016 03:41:23 PM David G. Johnston wrote: > > I have a psql script that obtains data via the \copy command and loads it > > into a temporary table. Additional work is performed possibly generating > > additional temporary

Re: [GENERAL] Connections - Postgres 9.2

On 05/16/2016 09:56 AM, Charles Clavadetscher wrote: Hello On 16.05.2016, at 18:32, Francisco Olarte mailto:fola...@peoplecall.com>> wrote: Hi Lucas On Mon, May 16, 2016 at 4:10 AM, Lucas Possamai mailto:drum.lu...@gmail.com>> wrote: Those IDLE connections, might be because the user/app

Re: [GENERAL] Any difference between commit/rollback when only temp tables and \copy are used

On Monday, May 16, 2016 03:41:23 PM David G. Johnston wrote: > I have a psql script that obtains data via the \copy command and loads it > into a temporary table. Additional work is performed possibly generating > additional temporary tables but never any "real" tables. Then the script > outputs,

[GENERAL] Any difference between commit/rollback when only temp tables and \copy are used

I have a psql script that obtains data via the \copy command and loads it into a temporary table. Additional work is performed possibly generating additional temporary tables but never any "real" tables. Then the script outputs, either to stdout or via \copy, the results. Does it matter whether

Re: [GENERAL] Fast way to delete big table?

On Sun, May 15, 2016 at 10:09 PM, Haiming Zhang < haiming.zh...@redflex.com.au> wrote: > Hi All, > > > > I have a big table (200G with 728 million records), the table slows down > lots of things. It's time to clean the data up. The requirement is when I > delete I must summarise few columns to a n

Re: [GENERAL] Connections - Postgres 9.2

Charles: On Mon, May 16, 2016 at 6:56 PM, Charles Clavadetscher wrote: > There really is a state 'Idle in transaction'? Good to learn. Again, IIRC, it was there in the graph legend, orange was Idle, yellow was Idle in transaction ( not in the data, just in the legend ). Francisco Olarte. --

Re: [GENERAL] Connections - Postgres 9.2

Hello > On 16.05.2016, at 18:32, Francisco Olarte wrote: > > Hi Lucas > >> On Mon, May 16, 2016 at 4:10 AM, Lucas Possamai wrote: >> >> Those IDLE connections, might be because the user/application didn't commit >> the transaction? > > ​IIRC Those would be 'Idle in transaction' ( which are

Re: [GENERAL] Connections - Postgres 9.2

Hi Lucas On Mon, May 16, 2016 at 4:10 AM, Lucas Possamai wrote: > > Those IDLE connections, might be because the user/application didn't > commit the transaction? > ​IIRC Those would be 'Idle in transaction' ( which are normally bad if numerous, unless your app has a reason for them, as they ar

Re: [GENERAL] Connections - Postgres 9.2

Hello > On 16.05.2016, at 04:10, Lucas Possamai wrote: > > Hi guys, > > > > Those IDLE connections, might be because the user/application didn't commit > the transaction? I think that idle means that a client is connected but is doing nothing. Possibly It includes terminated processes with

Re: [GENERAL] Toolchain for extensions (language C, Visual Studio 2013 / 2015)

2016-05-16 18:27 GMT+02:00 Klaus P. Pieper : > I’m using the setting “Toolchain: Visual Studio 2010” for C language > extensions. > > Is this still correct for the current versions? > I had to off some warnings on vs2015, but I was able to compile extension without stronger problems. Google was e

[GENERAL] Toolchain for extensions (language C, Visual Studio 2013 / 2015)

I'm using the setting "Toolchain: Visual Studio 2010" for C language extensions. Is this still correct for the current versions? Thanks Klaus

Re: [GENERAL] Share my experience and Thank you !

Chingyuan, >I also use some PERL scripts to transfer data and JCO RFC to perform SAP's >transaction. FYI. We also "mate" Postgres with SAP. Our transactions are exposed on the SAP system via a SOAP interface ( some "switches" your SAP people need to throw ). Tested and developed with bash scr

Re: [GENERAL] Ascii Elephant for text based protocols - Final

On Mon, May 16, 2016 at 2:47 PM, Charles Clavadetscher wrote: > SELECT row_dat FROM elephant ORDER BY row_num; Very good ! Thanks ! -- 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] Fast way to delete big table?

On 05/15/2016 10:33 PM, Haiming Zhang wrote: Hi Gavin, Thanks for the suggestion. What you said was what I tried to do (except the last reindexes, planned to do it when the table is cleaned up), however it was too slow. I have run for two days, nothing much happened. From your original post I

Re: [GENERAL] Ascii Elephant for text based protocols - Final

Hi all OK. I must come to an end with this task. This is what I find a decent final version and I plan to use it in my SwissPUG signature until the end of the year. ++ | __ ___| | /)/ \/ \ | | ( / ___\) | | \(/ o) ( o)

Re: [GENERAL] Ascii Elephant for text based protocols

I think I found a better representation for the end of the trunk: ++ | __ ___| | /)/ \/ \ | | ( / ___\) | | \(/ o) ( o) ) | | \_ (_ ) \ ) _/ | | \ /\_/\)/ | | \/ | || |

Re: [GENERAL] View not allowing to drop column (Bug or Feature enhancement )

Hi: On Mon, May 16, 2016 at 9:46 AM, Sachin Kotwal wrote: >> You need to drop the view before recreating it. Then it works. If you >> changed the access to the view with grants or revokes, you also neet to >> recreate them. They are dropped with the view. > Sorry to say but If we need to drop and

Re: [GENERAL] Build postgresql

Hello You can find instructions here: http://www.postgresql.org/docs/current/static/install-windows.html Bye Charles On 05/16/2016 10:29 AM, Roman Khalupa wrote: Hello postgresql team! I have question about building postgresql. And here it is: how to build postgresql statically on windows to

Re: [GENERAL] Build postgresql

Hello postgresql team! I have question about building postgresql. And here it is: how to build postgresql statically on windows to get static libs? Thanks

Re: [GENERAL] View not allowing to drop column (Bug or Feature enhancement )

Hi, > > * >> * >> *While trying to drop a column by replacing view definition from view it >> throws an error saying cannot drop column from view.* >> *=* >> postgres=# create or replace view vi1 as select >> id , name from orgda

Re: [GENERAL] View not allowing to drop column (Bug or Feature enhancement )

Hello Please post your answers to the list. You need to drop the view before recreating it. Then it works. If you changed the access to the view with grants or revokes, you also neet to recreate them. They are dropped with the view. Sorry to say but If we need to drop and replace

Re: [GENERAL] Fast way to delete big table?

Hello I am not sure if this is an option, but would partial indexes be of help? You could create your indexes in such a way that you exclude the rows that are not needed for your current queries, i.e. those that you would summarize in a separate table and delete using the same condition that

Re: [GENERAL] View not allowing to drop column (Bug or Feature enhancement )

On Mon, May 16, 2016 at 8:49 AM, Shrikant Bhende wrote: > While working on the view I came across an unusual behaviour of the view, > PostgreSQL do not allows to drop a column from the view, whereas same > pattern of Create and Replace view works while adding a column. This is probably because yo

Re: [GENERAL] View not allowing to drop column (Bug or Feature enhancement )

Hello On 05/16/2016 08:49 AM, Shrikant Bhende wrote: Hi all, While working on the view I came across an unusual behaviour of the view, PostgreSQL do not allows to drop a column from the view, whereas same pattern of Create and Replace view works while adding a column. Please find below test fo