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

[hmzha2]

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
after they've been inserted).





--
View this message in context: 
http://postgresql.nabble.com/Fast-way-to-delete-big-table-tp5903787p5903923.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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?

[Jeff Janes]

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 = ... takes 2.9 hours to
> finish.

Perhaps you have a foreign constraint pointing to your tableA from
another table, and the other table is un-indexed on those columns (in
production).  So every delete from tableA leads to a full scan of that
other table to make sure no offending rows exist.

If that is not the case, then I'd try to do a "strace -ttt -T -y -p
" on the process which is taking so long, and see what it is
doing.  Unfortunately, you have to do that on production, as that is
the only system where the problem appears.

Cheers,

Jeff


-- 
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?

[Charles Clavadetscher]

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 reduce the number of rows to be processed, even I process 1 
> row in production machine, it takes 24
> hours to finish.

This is quite weird... It there any other process locking the record or the 
table?
Bye
Charles

> 
> Regards,
> Haiming
> 
> 
> 
> --
> View this message in context: 
> http://postgresql.nabble.com/Fast-way-to-delete-big-table-tp5903787p5903917.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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?

[hmzha2]

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
deletions until the>table is empty.  Given that, why not just summarize the
entire table and>then truncate the whole thing?Yeah, that's my aim. Go
through every row and delete the entire table. The problem of summarizing
first then truncate is I cant guarantee the database and system keep running
during the summarizing as the customer sometimes stops the application and
postgres manually. Plus there are new data goes into the table so I need to
process chunk by chunk until it finishes. >You could try to rewrite this
into a single SQL query, using a CTE with>"with t as (delete  returning
* )".  The hardest part is that>PostgreSQL alas doesn't allow LIMIT on
DELETE statements, so you have to go>through some ugly contortions with a
sub-select, or a where clause on your>delete using, for example, a date to
do the limiting, and have your script>increment the date on each invocation. 
I've not done this with>summarization, but have done it with partitioning
where I had to move>tuples from the parent table to their new partition (or
the reverse, moving>from an ill-conceived partition back to the parent) with
a minimum of>disruption.worth to try, eg. delete the oldest 100 rows at a
time. But wont the sorting take more time than just select from the fly?>Why
is this not fast enough?  So, it would take 4 days.  How fast do you>need it
to be?Works ok in my testing environment but not on the production
machine.Select * from tableA limit 1; takes milliseconds to finishupdate
summary table from the previous select result, takes millisecondsdelete from
tableA where primaryKey = ... takes 2.9 hours to finish. Regards,Haiming



--
View this message in context: 
http://postgresql.nabble.com/Fast-way-to-delete-big-table-tp5903787p5903918.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

[hmzha2]

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 to reduce the number of rows to be processed, even I process 1
row in production machine, it takes 24 hours to finish. 

Regards,
Haiming



--
View this message in context: 
http://postgresql.nabble.com/Fast-way-to-delete-big-table-tp5903787p5903917.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Ascii Elephant for text based protocols - Final

[Charles Clavadetscher]

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 that you should
remove the mention of the 20th anniversary.



Thank you for the feedbacks.

Good idea. I will write a function. The most basic would be:

CREATE OR REPLACE FUNCTION pg_logo()
RETURNS SETOF TEXT
AS $$
BEGIN
  RETURN QUERY SELECT '    __  ___  '::TEXT;
  RETURN QUERY SELECT ' /)/  \/   \ '::TEXT;
  RETURN QUERY SELECT '( / ___\)'::TEXT;
  RETURN QUERY SELECT ' \(/ o)  ( o)   )'::TEXT;
  RETURN QUERY SELECT '  \_  (_  )   \ ) _/ '::TEXT;
  RETURN QUERY SELECT '\  /\_/\)/   '::TEXT;
  RETURN QUERY SELECT ' \/'::TEXT;
  RETURN QUERY SELECT '  _|  |  '::TEXT;
  RETURN QUERY SELECT '  \|_/   '::TEXT;
  RETURN;
END;
$$ LANGUAGE plpgsql;

charles@charles.[local]=# select pg_logo();
pg_logo
---
     __  ___
  /)/  \/   \
 ( / ___\)
  \(/ o)  ( o)   )
   \_  (_  )   \ ) _/
 \  /\_/\)/
  \/ 
   _|  |
   \|_/
(9 rows)

But I'd like to add arguments so that it is possible to add a frame and 
a text as TEXT[]. Additional arguments for the text would be the 
position (bottom or right) and the alignments (top, bottom, left, right, 
center). All arguments should have default values so that calling the 
function without arguments delivers the logo as in the above basic function.


A question to the naming. I find pg_logo() also a good name, but is the 
prefix pg_* not reserved for system functions? Of course I could use the 
name I want, but was wondering if there is a policy or a best practice 
in this area.


And what schema would be appropriate? I could also create the function 
without defining a schema and let users decide where they want to put it.


An alternative for the name could be logo(), in analogy to version().

Bye
Charles


--
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] Increased I/O / Writes

[Lucas Possamai]

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 :)


Anyway...
I was thinking about decrease the shared_buffer to something like 1/8 of
total RAM = 16GB

What do u guys think about it?

Cheers
Lucas

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

[Guyren Howe]

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 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 BY 
> clause or be used in an aggregate function".
> 
> Huh?
> 
> The larger query would help…

SELECT
  o.id,
  os.status AS status,
  o.status_updated_at,
  o.should_hold_at_airlines,
  (SELECT drs2.id FROM delivery_route_segments drs2 WHERE drs2.order_id = o.id 
AND NOT drs2.completed ORDER BY drs2.position LIMIT 1) AS current_drs_id,


FROM
  orders o JOIN
  order_statuses os ON (o.status = os.id) JOIN
  delivery_route_segments drs ON (drs.order_id = o.id) JOIN
  pick_up_addresses pua ON (pua.order_id = o.id)
GROUP BY
  o.id, os.status
I would prefer to do the subquery as a window function, both because that is 
cleaner to read and also because I believe it is likely to be more efficient.

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

[David G. Johnston]

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 value.
>
> But I get "column "drs.id" must appear in the GROUP BY clause or be used
> in an aggregate function".
>
> Huh?
>

The larger query would help...

First_value as shown is in a window expression.  It's surrounding query
must also have a group by on it.  Fields in a windowed function are
not considered aggregated for purposes of group by.

Select day_of_month, sum(sales), sum(sum(sales)) over ()
>From ...
Group by day_of_month;

For each day of month give me total sales.  I also want to see the total
sales over the whole query.  I need to sum together the individual daily
sums.  You seem to have a similar situation in your query.  The reasoning
of first_value is not easy to speculate upon though.

David J.

[GENERAL] FIRST_VALUE argument must appear in group by?

[Guyren Howe]

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 BY clause or be used in an 
aggregate function".

Huh?

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

[Steve Kehlet]

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 place earlier).


Thank you very much. I will adjust those settings. Is there a way,
something similar to keeping an eye on `age(relfrozenxid)`, that I can
watch this and keep an eye on it before it becomes a problem?

Re: [GENERAL] WAL files not being recycled

[Vik Fearing]

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
> database | omitted
> active   | f
> xmin |
> catalog_xmin | 84884044
> restart_lsn  | 1B3/4300CA60
> 
> Can an inactive replication slot cause WALs to be retained?

Absolutely!  That's what they're for.

If you don't expect this slot to re-become active, you should drop it.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Elusai Soares]

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 procedures. I need to convert them into
PostgreSQL functions. My question is: is there any tool that could do this
conversion? I have tried some tools such as DBTools Manager Professional,
Full Convert and PostgreSQL Database Converter, but none of them is able to
convert stored procedures.


I would like to find a tool because I have a little time to finish this
activity manually, and I not even reached triggers...
Thank you all and best regards from Brazil :)

Re: [GENERAL] WAL files not being recycled

[Scott Moynes]

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 |
catalog_xmin | 84884044
restart_lsn  | 1B3/4300CA60

Can an inactive replication slot cause WALs to be retained?

On Mon, May 16, 2016 at 3:44 PM, Alvaro Herrera 
wrote:

> 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 enabled and does not record any logs being
> recycled:
>
> See the wal_keep_segments option, as well as existance of any
> replication slot (select * from pg_replication_slots).
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>



-- 
Scott Moynes
VM Farms

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

[Alvaro Herrera]

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:
> autovacuum_vacuum_cost_delay: 10ms
> autovacuum_vacuum_cost_limit: 2000
> 
> We'll be updating those settings as soon as possible.
> 
> Just looking for some expert eyes on this problem. Are we on the track
> track? I.e. is making the autovacuumer run more aggressively our best bet
> to avoid this issue?

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 place earlier).  You may need to decrease the cost_delay too
(and/or increase the cost_limit) in order for autovac to be able to keep
up, but really that's more a side effect because of a possible need for
autovac to do more in the same period of time.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Steve Kehlet]

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 "members"
limit exceeded
>  Detail: This command would create a multixact with 2 members, but the
remaining space is only enough for 0 members.
>  Hint: Execute a database-wide VACUUM in database with OID 33554 with
reduced vacuum_multixact_freeze_min_age and
vacuum_multixact_freeze_table_age settings.

We followed those VACUUM instructions, and now everything's back to normal.

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:
autovacuum_vacuum_cost_delay: 10ms
autovacuum_vacuum_cost_limit: 2000

We'll be updating those settings as soon as possible.

Just looking for some expert eyes on this problem. Are we on the track
track? I.e. is making the autovacuumer run more aggressively our best bet
to avoid this issue?

Thank you,

Steve

Re: [GENERAL] WAL files not being recycled

[Adrian Klaver]

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 logging is enabled and does not record any logs being recycled:

2016-05-16 00:05:37 EDT -  LOG:  checkpoint starting: xlog
2016-05-16 00:09:51 EDT -  LOG:  checkpoint complete: wrote 38574
buffers (29.4%); 0 transaction log file(s) added, 0 removed, 0 recycled;
write=252.921 s, sync=1.340 s, total=254.312 s; sync files=48,
longest=1.101 s, average=0.027 s
2016-05-16 00:10:37 EDT -  LOG:  checkpoint starting: time
2016-05-16 00:14:11 EDT -  LOG:  checkpoint complete: wrote 43786
buffers (33.4%); 0 transaction log file(s) added, 0 removed, 0 recycled;
write=206.271 s, sync=7.428 s, total=213.765 s; sync files=82,
longest=3.821 s, average=0.090 s
...
2016-05-16 07:32:27 EDT -  LOG:  checkpoint complete: wrote 7617
buffers (5.8%); 1 transaction log file(s) added, 0 removed, 0 recycled;
write=269.3

Control data records:

pg_control last modified: Mon 16 May 2016 06:23:07 PM EDT
Latest checkpoint location:   1EB/1F008FE8
Prior checkpoint location:1EB/1E0009E0
Latest checkpoint's REDO location:1EB/1F008FB0

There is a single replication client:

pid  | 23287
usesysid | 10
usename  | postgres
application_name | walreceiver
client_addr  | 172.16.56.246
client_hostname  |
client_port  | 58070
backend_start| 2016-05-14 03:27:33.523611+00
backend_xmin |
state| streaming
sent_location| 1EB/1F042760
write_location   | 1EB/1F042760
flush_location   | 1EB/1F042760
replay_location  | 1EB/1F037F40
sync_priority| 0
sync_state   | async


Does anyone have suggestions what else to check? What can cause WALs not
to be recycled or removed?


http://www.postgresql.org/docs/9.4/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-MASTER

"wal_keep_segments (integer)

Specifies the minimum number of past log file segments kept in the 
pg_xlog directory, in case a standby server needs to fetch them for 
streaming replication. Each segment is normally 16 megabytes. If a 
standby server connected to the sending server falls behind by more than 
wal_keep_segments segments, the sending server might remove a WAL 
segment still needed by the standby, in which case the replication 
connection will be terminated. Downstream connections will also 
eventually fail as a result. (However, the standby server can recover by 
fetching the segment from archive, if WAL archiving is in use.)


This sets only the minimum number of segments retained in pg_xlog; 
the system might need to retain more segments for WAL archival or to 
recover from a checkpoint. If wal_keep_segments is zero (the default), 
the system doesn't keep any extra segments for standby purposes, so the 
number of old WAL segments available to standby servers is a function of 
the location of the previous checkpoint and status of WAL archiving. 
This parameter can only be set in the postgresql.conf file or on the 
server command line.

"



Thanks in advance.

--
Scott Moynes
VM Farms



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] WAL files not being recycled

[Alvaro Herrera]

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 enabled and does not record any logs being recycled:

See the wal_keep_segments option, as well as existance of any
replication slot (select * from pg_replication_slots).

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] WAL files not being recycled

[Scott Moynes]

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 being recycled:

2016-05-16 00:05:37 EDT -  LOG:  checkpoint starting: xlog
2016-05-16 00:09:51 EDT -  LOG:  checkpoint complete: wrote 38574
buffers (29.4%); 0 transaction log file(s) added, 0 removed, 0 recycled;
write=252.921 s, sync=1.340 s, total=254.312 s; sync files=48,
longest=1.101 s, average=0.027 s
2016-05-16 00:10:37 EDT -  LOG:  checkpoint starting: time
2016-05-16 00:14:11 EDT -  LOG:  checkpoint complete: wrote 43786
buffers (33.4%); 0 transaction log file(s) added, 0 removed, 0 recycled;
write=206.271 s, sync=7.428 s, total=213.765 s; sync files=82,
longest=3.821 s, average=0.090 s
...
2016-05-16 07:32:27 EDT -  LOG:  checkpoint complete: wrote 7617
buffers (5.8%); 1 transaction log file(s) added, 0 removed, 0 recycled;
write=269.3

Control data records:

pg_control last modified: Mon 16 May 2016 06:23:07 PM EDT
Latest checkpoint location:   1EB/1F008FE8
Prior checkpoint location:1EB/1E0009E0
Latest checkpoint's REDO location:1EB/1F008FB0

There is a single replication client:

pid  | 23287
usesysid | 10
usename  | postgres
application_name | walreceiver
client_addr  | 172.16.56.246
client_hostname  |
client_port  | 58070
backend_start| 2016-05-14 03:27:33.523611+00
backend_xmin |
state| streaming
sent_location| 1EB/1F042760
write_location   | 1EB/1F042760
flush_location   | 1EB/1F042760
replay_location  | 1EB/1F037F40
sync_priority| 0
sync_state   | async


Does anyone have suggestions what else to check? What can cause WALs not to
be recycled or removed?

Thanks in advance.

-- 
Scott Moynes
VM Farms

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

[David G. Johnston]

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 never any "real" tables.
>> Then the script outputs, either to stdout or via \copy, the results.
>>
>> Does it matter whether I issue a ROLLBACK or a COMMIT at the of the
>> transaction?  More basically: does it matter whether I issue a BEGIN?
>>
>
> I would say that depends on how you are creating the temp table:
>
>
​I'm presently using them without an "ON COMMIT" clause - they live until
the end of the script/session/transaction (which here are all the same).

So within-transaction storage usage would be controllable ​making the use
of BEGIN at least potentially meaningful - as long rollbacks or commits are
used and more than one transaction is used to isolate the different parts.

The lack of auto-analyze is something to keep in mind (but haven't been bit
by it yet so getting it to stick in memory is more difficult) in either
case...though present usage involves sequential scans anyway.

Thanks!

David J.

Re: [GENERAL] Connections - Postgres 9.2

[Adrian Klaver]

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 an issue.


I note that MRTG style graph showed max 16, average 5 idle
connections.   thats a rather small number to be concerned with.  if it
was 100s, then I'd be worrying about it.


Yeah, I failed to look at the scale of the y axis and just looked at the 
relative numbers.






--
john r pierce, recycling bits in santa cruz




--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Connections - Postgres 9.2

[John R Pierce]

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 showed max 16, average 5 idle 
connections.   thats a rather small number to be concerned with. if it 
was 100s, then I'd be worrying about it.




--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Connections - Postgres 9.2

[Adrian Klaver]

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 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.




--
john r pierce, recycling bits in santa cruz




--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Connections - Postgres 9.2

[Lucas Possamai]

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 connection, right?
>>
>> Would the pool connection solve that IDLE connections? But more important
>> than that, are the IDLE connections using the machine's resources ?
>>
>
> Yes. There is no straight way to terminate the IDLE connections from the
> database end. You would need a connection pooler like pgBouncer which can
> help you terminate IDLE connections after a certain time.
>
> It would be much better if your Application can terminate connections to
> the database.
>
> Regards,
> Venkata B N
>
> Fujitsu Australia
>
>


Ok awesome.. thanks a lot!

Lucas

Re: [GENERAL] Connections - Postgres 9.2

[John R Pierce]

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 
network socket.   its not using CPU or disk IO.



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Connections - Postgres 9.2

[Venkata Balaji N]

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 connections? But more important
> than that, are the IDLE connections using the machine's resources ?
>

Yes. There is no straight way to terminate the IDLE connections from the
database end. You would need a connection pooler like pgBouncer which can
help you terminate IDLE connections after a certain time.

It would be much better if your Application can terminate connections to
the database.

Regards,
Venkata B N

Fujitsu Australia

Re: [GENERAL] Connections - Postgres 9.2

[Adrian Klaver]

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 that 
are not being closed then putting a pooler between the client and the 
database just moves the problem to the pooler. In other words if the 
client is asking for a connection from the pooler and then does not 
close it then the pooler is going to have to add connections to deal 
with subsequent connections. Now you can set a max number of connections 
and reject new ones after that number, but that then means you may be 
shutting out connections that need to get through. The first step in 
dealing with this would be to determine what code is generating 
connections and then not closing them. A good place to start would be:


http://www.postgresql.org/docs/9.5/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

Table 27-3. pg_stat_activity View

That should give you an idea of what is creating the connections.




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.



cheers



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Ascii Elephant for text based protocols - Final

[Michael Paquier]

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 anniversary.
-- 
Michael


-- 
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] Connections - Postgres 9.2

[Lucas Possamai]

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 the machine's resources ?


cheers

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

[Karsten Hilbert]

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)
> VALUES
> ( 1,'++'),
> ( 2,'|     __  ___|'),
> ( 3,'|  /)/  \/   \   |'),
> ( 4,'| ( / ___\)  |'),
> ( 5,'|  \(/ o)  ( o)   )  |'),
> ( 6,'|   \_  (_  )   \ ) _/   |'),
> ( 7,'| \  /\_/\)/ |'),
> ( 8,'|  \/  |'),
> ( 9,'|   _|  ||'),
> (10,'|   \|_/ |'),
> (11,'||'),
> (12,'|  PostgreSQL 1996-2016  |'),
> (13,'|  20 Years of success   |'),
> (14,'++');
> 
> SELECT row_dat FROM elephant ORDER BY row_num;

-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] Any difference between commit/rollback when only temp tables and \copy are used

[Adrian Klaver]

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 or via \copy, the results.

Does it matter whether I issue a ROLLBACK or a COMMIT at the of the
transaction?  More basically: does it matter whether I issue a BEGIN?


I would say that depends on how you are creating the temp table:

http://www.postgresql.org/docs/9.5/interactive/sql-createtable.html
"
TEMPORARY or TEMP

If specified, the table is created as a temporary table. Temporary 
tables are automatically dropped at the end of a session, or optionally 
at the end of the current transaction (see ON COMMIT below). Existing 
permanent tables with the same name are not visible to the current 
session while the temporary table exists, unless they are referenced 
with schema-qualified names. Any indexes created on a temporary table 
are automatically temporary as well.


The autovacuum daemon cannot access and therefore cannot vacuum or 
analyze temporary tables. For this reason, appropriate vacuum and 
analyze operations should be performed via session SQL commands. For 
example, if a temporary table is going to be used in complex queries, it 
is wise to run ANALYZE on the temporary table after it is populated.


Optionally, GLOBAL or LOCAL can be written before TEMPORARY or 
TEMP. This presently makes no difference in PostgreSQL and is 
deprecated; see Compatibility.

"

"ON COMMIT

The behavior of temporary tables at the end of a transaction block 
can be controlled using ON COMMIT. The three options are:


PRESERVE ROWS

No special action is taken at the ends of transactions. This is 
the default behavior.

DELETE ROWS

All rows in the temporary table will be deleted at the end of 
each transaction block. Essentially, an automatic TRUNCATE is done at 
each commit.

DROP

The temporary table will be dropped at the end of the current 
transaction block.


"


The script runs on Ubuntu inside a bash shell's heredoc.

Thanks!

David J.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Any difference between commit/rollback when only temp tables and \copy are used

[David G. Johnston]

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 tables but never any "real" tables.  Then the script
> > outputs, either to stdout or via \copy, the results.
> >
> > Does it matter whether I issue a ROLLBACK or a COMMIT at the of the
> > transaction?  More basically: does it matter whether I issue a BEGIN?
> >
> > The script runs on Ubuntu inside a bash shell's heredoc.
> >
>
> Some things will complete faster if you use BEGIN to start, as PostgreSQL
> will
> otherwise issue an implicit BEGIN and COMMIT before and after every
> statement.
>
>
​Non-zero but minimal compared to the I/O hit.​  The slightly more
meaningful savings would be in lesser consumption of transaction id
values.  Though both would probably only matter at scale.


> If you don't need anything saved at the end it probably doesn't matter if
> you
> use ROLLBACK or COMMIT.
>

​Yeah, without the front end of this being optimized for once you get to
the end you've already consumed the relevant resources and I suspect that
as far at the catalogs go those temp tables are just as good as rolledback
on session end.

David J.

Re: [GENERAL] Connections - Postgres 9.2

[Adrian Klaver]

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/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 are the ones
which can block things ). Plain 'Idle' are normally connections
between transactions, totally normal if you use poolers, or if your
app keeps connection opens while it does other things ( like preparing
for a transaction ).



There really is a state 'Idle in transaction'? Good to learn.


http://www.postgresql.org/docs/9.5/static/monitoring-stats.html
"
state   textCurrent overall state of this backend. Possible values are:

active: The backend is executing a query.

idle: The backend is waiting for a new client command.

idle in transaction: The backend is in a transaction, but is not 
currently executing a query.


idle in transaction (aborted): This state is similar to idle in 
transaction, except one of the statements in the transaction caused an 
error.


fastpath function call: The backend is executing a fast-path function.

disabled: This state is reported if track_activities is disabled in 
this backend.

"


Thank you and bye
Charles


Francisco Olarte.






--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Any difference between commit/rollback when only temp tables and \copy are used

[Alan Hodgson]

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, either to stdout or via \copy, the results.
> 
> Does it matter whether I issue a ROLLBACK or a COMMIT at the of the
> transaction?  More basically: does it matter whether I issue a BEGIN?
> 
> The script runs on Ubuntu inside a bash shell's heredoc.
> 

Some things will complete faster if you use BEGIN to start, as PostgreSQL will 
otherwise issue an implicit BEGIN and COMMIT before and after every statement.

If you don't need anything saved at the end it probably doesn't matter if you 
use ROLLBACK or COMMIT.




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David G. Johnston]

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 I issue a ROLLBACK or a COMMIT at the of the
transaction?  More basically: does it matter whether I issue a BEGIN?

The script runs on Ubuntu inside a bash shell's heredoc.

Thanks!

David J.

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

[Jeff Janes]

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 new table for backup purpose. So
> that mean I need to go through each row and add the columns' value to the
> summary table (the corresponding category).
>

Is the performance slow-down an emergency?  If not, then delete and
summarize the rows in bite size chunks, over weeks or months.  It took a
long time to get into the situation, spend some time calmly getting out of
it.


>
>
> The table has indexes (multicoumn indexes) before, I removed the indexes
> because I read on forum says the indexes *heavily* reduce the speed of
> deleting.
>

That generally isn't true in PostgreSQL.  Deletes do not directly perform
any index maintenance (triggers might cause them to do some indirectly).
Doing the deletes does build up "vacuum debt" which must eventually be
paid, and more indexes does make that more expensive.  But that happens in
the background.



> That's true if I only delete from the table, but my case is I first SELECT
> this row, then add few values to the summary table and then DELETE the row
> according to its multiple primary key. This seems to be a bad idea now as
> it takes very long time to DELETE the row (as delete needs to find the row
> first).
>

This doesn't match your example, as in your examples there are no WHERE
clauses on the main table select, so no index could be used to find the
rows.

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 deletions until the
table is empty.  Given that, why not just summarize the entire table and
then truncate the whole thing?

...
>

You could try to rewrite this into a single SQL query, using a CTE with
"with t as (delete  returning * )".  The hardest part is that
PostgreSQL alas doesn't allow LIMIT on DELETE statements, so you have to go
through some ugly contortions with a sub-select, or a where clause on your
delete using, for example, a date to do the limiting, and have your script
increment the date on each invocation.  I've not done this with
summarization, but have done it with partitioning where I had to move
tuples from the parent table to their new partition (or the reverse, moving
from an ill-conceived partition back to the parent) with a minimum of
disruption.

It should be faster, but I don't know how much.  It would depend a lot on
whether your bottleneck is CPU or IO, amongst other things.


>
>
>
>
> Method 2 is bit faster but not much, the delete speed is 2478 rows/s for
> method 2 and 2008 rows/s for method 1.
>
> Any suggestions are welcomed.
>

Why is this not fast enough?  So, it would take 4 days.  How fast do you
need it to be?

But there is the possibility that it will slow down over time, as each
execution might have to scan over the debris of all of the previously
deleted rows before it gets to the ones you want to work on.

Cheers,

Jeff

Re: [GENERAL] Connections - Postgres 9.2

[Francisco Olarte]

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.


-- 
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] Connections - Postgres 9.2

[Charles Clavadetscher]

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 normally bad if 
> numerous, unless your app has a reason for them, as they are the ones which 
> can block things ). Plain 'Idle' are normally connections between 
> transactions, totally normal if you use poolers, or if your app keeps 
> connection opens while it does other things ( like preparing for a 
> transaction ).
> 

There really is a state 'Idle in transaction'? Good to learn.
Thank you and bye
Charles

> Francisco Olarte.
> 
>  
> 

Re: [GENERAL] Connections - Postgres 9.2

[Francisco Olarte]

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 are the ones which
can block things ). Plain 'Idle' are normally connections between
transactions, totally normal if you use poolers, or if your app keeps
connection opens while it does other things ( like preparing for a
transaction ).

Francisco Olarte.

Re: [GENERAL] Connections - Postgres 9.2

[Charles Clavadetscher]

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 without a commit, since they do 
nothing, but I am not sure that you can assume that all connections are 
uncommitted transactions.

Bye
Charles


-- 
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] Toolchain for extensions (language C, Visual Studio 2013 / 2015)

[Pavel Stehule]

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 enough.

Pavel


>
>
> Thanks Klaus
>
>
>

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

[Klaus P. Pieper]

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 !

[Allan Harvey]

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 scripts and cURL the executable to get the 
headers etc correct, then our own C executable using cURL as a library, then 
wrapped Postgres C function call around our C code to have Postgres function.

SAP transactions are fully integrated into the "SQL" of the system.

Allan


Arrium Limited and associated entities are currently in Voluntary 
Administration. A list of all associated entities in Voluntary Administration 
may be obtained from: "http://www.arrium.com/~/media/Arrium Mining and 
Materials/Files/Corporate documents/Arrium Administration_Groups List.pdf". The 
material contained in this email is confidential and may be subject to legal 
privilege and/or copyright. Please do not re-transmit, distribute, copy or 
commercialise any of the material in this message unless you are authorised to 
do so. If you are not the intended recipient, use, disclosure or copying of 
this information is prohibited. If you have received this document in error, 
please advise the sender and delete the document. None of Arrium Limited, its 
related bodies corporate or the sender accept responsibility for any viruses 
contained in this email or any attachments. All and any rights as to 
confidentiality, legal professional privilege and copyright are expressly 
reserved.

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

[Oleg Bartunov]

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?

[Adrian Klaver]

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 gathered you did not do what Gavin suggested. 
In that you dropped the indexes first and the combined the UPDATE 
summary and DELETE row into one operation. So I am little confused on 
what you are asking. Are you look for suggestions on what to do in the 
future or how to make the existing condition(no indexes on the big 
table) work better or both?




Truncate does not work for my purpose.

Regards,
Haiming






*Haiming Zhang* | Engineer | *Redflex Group*
*T* +61 3 9674 1868 | *F* +61 3 9699 3566 | *E*
haiming.zh...@redflex.com.au
31 Market Street, (PO Box 720), South Melbourne VIC 3205, Australia

If you are not an authorised recipient of this e-mail, please contact me
at Redflex immediately by return phone call or by email. In this case,
you should not read, print, retransmit, store or act in reliance on this
e-mail or any attachments, and should destroy all copies of them. This
e-mail and any attachments are confidential and may contain privileged
information and/or copyright material of Redflex or third parties. You
should only retransmit, distribute or commercialise the material if you
are authorised to do so. This notice should not be removed.






-Original Message-
From: Gavin Flower [mailto:gavinflo...@archidevsys.co.nz]
Sent: Monday, 16 May 2016 3:24 PM
To: Haiming Zhang ;
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Fast way to delete big table?

On 16/05/16 17:09, Haiming Zhang 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 new table for
backup purpose. So that mean I need to go through each row and add the
columns' value to the summary table (the corresponding category).

The table has indexes (multicoumn indexes) before, I removed the
indexes because I read on forum says the indexes *heavily* reduce the
speed of deleting. That's true if I only delete from the table, but my
case is I first SELECT this row, then add few values to the summary
table and then DELETE the row according to its multiple primary key.
This seems to be a bad idea now as it takes very long time to DELETE
the row (as delete needs to find the row first).

Here are the two version of the delete functions, please help to point
out how can I speed it up.

1.

CREATE OR REPLACE FUNCTION summary_delete_table()

RETURNS integer AS

$BODY$

DECLARE

rec RECORD;

subrec  RECORD;

BEGIN

FOR rec IN SELECT * FROM tableA limit 100 LOOP

BEGIN

UPDATE summaryTable set count1 = rec.count1 + rec.count1, count2 =...
where category match;

delete from tableA where tableA.primaryKeyA=rec.primaryKeyA and
tableA.primaryKeyB=rec.primaryKeyB;

END;

END LOOP;

return 1;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

And then I have a .bat script to loop the above function million times.

2.

CREATE OR REPLACE FUNCTION summary_delete_table()

RETURNS integer AS

$BODY$

DECLARE

rec RECORD;

td_cursorCURSOR FOR SELECT * FROM tableA limit 100;

BEGIN

FOR rec IN td_cursor LOOP

BEGIN

UPDATE summaryTable set count1 = rec.count1 + rec.count1, count2 =...
where category match;

delete from tableA WHERE CURRENT OF td_cursor;

END;

END LOOP;

return 1;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

Method 2 is bit faster but not much, the delete speed is 2478 rows/s
for method 2 and 2008 rows/s for method 1.

Any suggestions are welcomed.

BTW, I guess if reindex, it may take few days to finish.

Also, I tried change delete 100 rows at a time and 1000, 2000. The
result showed 1000 is faster than 100 and 2000 a time.

Thanks and Regards,

Haiming


[...]

In one transaction:

 1. populate the summary table
 2. drop indexes on tableA
 3. delete selected entries in tableA
 4. recreate indexes for tableA

If deleting all entries, then simply truncate it!

N.B. I have NOT checked the fine print in the documentation, nor tested
this - so be warned!  :-)


Cheers,
Gavin





--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Ascii Elephant for text based protocols - Final

[Charles Clavadetscher]

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)   )  |
|   \_  (_  )   \ ) _/   |
| \  /\_/\)/ |
|  \/  |
|   _|  ||
|   \|_/ |
||
|  PostgreSQL 1996-2016  |
|  20 years of success   |
++

It suggests that the trunk is bent behind, like while feeding and I 
assume that associations with phallic symbols doesn't apply anymore.


Thank you to all that provided feedbacks, suggestions and compliments.

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)
VALUES
( 1,'++'),
( 2,'|     __  ___|'),
( 3,'|  /)/  \/   \   |'),
( 4,'| ( / ___\)  |'),
( 5,'|  \(/ o)  ( o)   )  |'),
( 6,'|   \_  (_  )   \ ) _/   |'),
( 7,'| \  /\_/\)/ |'),
( 8,'|  \/  |'),
( 9,'|   _|  ||'),
(10,'|   \|_/ |'),
(11,'||'),
(12,'|  PostgreSQL 1996-2016  |'),
(13,'|  20 Years of success   |'),
(14,'++');

SELECT row_dat FROM elephant ORDER BY row_num;

Regards
Charles


--
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] Ascii Elephant for text based protocols

[Charles Clavadetscher]

I think I found a better representation for the end of the trunk:

++
|     __  ___|
|  /)/  \/   \   |
| ( / ___\)  |
|  \(/ o)  ( o)   )  |
|   \_  (_  )   \ ) _/   |
| \  /\_/\)/ |
|  \/  |
||  ||
||_/ |
||
|  PostgreSQL 1996-2016  |
|  20 years of success   |
++

And in Melvin's SQL:

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)
VALUES
( 1,'++'),
( 2,'|     __  ___|'),
( 3,'|  /)/  \/   \   |'),
( 4,'| ( / ___\)  |'),
( 5,'|  \(/ o)  ( o)   )  |'),
( 6,'|   \_  (_  )   \ ) _/   |'),
( 7,'| \  /\_/\)/ |'),
( 8,'|  \/  |'),
( 9,'||  ||'),
(10,'||_/ |'),
(11,'||'),
(12,'|  PostgreSQL 1996-2016  |'),
(13,'|  20 Years of success   |'),
(14,'++');

SELECT row_dat FROM elephant ORDER BY row_num;

Bye
Charles

Annex: Versions

Original:

    __  ___
 /)/  \/   \
( / ___\)
 \(/ o)  ( o)   )
  \_  (_  )   \ )  /
\  /\_/\)_/
 \/  //|  |\\
 v |  | v
   \__/

V2 (feedback David Bennett):

    __  ___
 /)/  \/   \
( / ___\)
 \(/ o)  ( o)   )
  \_  (_  )   \ ) _/<--
\  /\_/\)/  <--
 \/   <--
   |  |
   \__/

V3 (feedback Tom Lane):
    __  ___
 /)/  \/   \
( / ___\)
 \(/ o)  ( o)   )
  \_  (_  )   \ ) _/
\  /\_/\)/
 \/ 
   |  |
   |/\| <--
Or
    __  ___
 /)/  \/   \
( / ___\)
 \(/ o)  ( o)   )
  \_  (_  )   \ ) _/
\  /\_/\)/
 \/ 
   |  |
   |__| <--

V4 (myself)

    __  ___
 /)/  \/   \
( / ___\)
 \(/ o)  ( o)   )
  \_  (_  )   \ ) _/
\  /\_/\)/
 \/ 
   |  |
   |_/ <--



--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org


--
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] View not allowing to drop column (Bug or Feature enhancement )

[Francisco Olarte]

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 replace then what is use of "Create
> OR Replace " syntax?

That is the use case. Create or replace  is for doing
backwards compatible changes without having to worry about what other
things is running concurrently with you. If you make non-backwards
compatible changes the system forces you to drop, so you can see
dependencies and who is using the things before dropping ( unless you
go trigger happy with cascade ).

>>> If its not a bug and a limitation kindly guide me towards any
>>> documentation where it is mentioned.
>> CREATE OR REPLACE VIEW is similar, but if a view of the same name already
>> exists, it is replaced. The new query must generate the same columns that

> If this is the limitation. Is community is planning update this or add this
> feature soon?

IMO you've got it backwards. The limitation IS the feature. In my
case, as an example, I can code every view ( and functions ) in a
script file using create or replace. And when I find a bug / want to
make a backwards compatible improvement I can just edit the script and
fire it again, and this feature insures I do not impact other code if
I inadvertently rename a column, or delete it.

If the feature were to be removed, and backwards-incompatible changes
were allowed, a lot of people will be unhappy.

Francisco Olarte.


-- 
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] Build postgresql

[Charles Clavadetscher]

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 get static libs?

Thanks


--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org


--
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] Build postgresql

[Roman Khalupa]

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 )

[Sachin Kotwal]

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 orgdata ;
>> *ERROR:  cannot drop columns from view*
>>
>
> 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 then what is use of "Create
OR Replace " syntax?



> If its not a bug and a limitation kindly guide me towards any
>> documentation where it is mentioned.
>>
>
> http://www.postgresql.org/docs/current/static/sql-createview.html
>
> CREATE OR REPLACE VIEW is similar, but if a view of the same name already
> exists, it is replaced. The new query must generate the same columns that
> were generated by the existing view query (that is, the same column names
> in the same order and with the same data types), but it may add additional
> columns to the end of the list. The calculations giving rise to the output
> columns may be completely different.
>
>
If this is the limitation. Is community is planning update this or add this
feature soon?



On Mon, May 16, 2016 at 12:50 PM, Francisco Olarte 
wrote:

> 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 you are using create or replace, which is
> normally used to switch things in place and so it needs them to be
> compatible with the old ones. A view with an extra column can be used
> instead of the old one, but a view with less columns can not. I do not
> see the 'not dropping' part as unusual, and the 'can add columns',
> well, I see them as a little unusual on a create or replace but I see
> the point in hallowing it, so just a little.
>
> > Alter command do not have any option to drop column
> > postgres=# alter view vi1
> > ALTER COLUMN  OWNER TO  RENAME TO SET SCHEMA
>
> Well, it is a view, not a table. They are basically shorthands for
> queries and places to attach triggers, so its normal they do not have
> as much management options.
>
> > If its not a bug and a limitation kindly guide me towards any
> documentation
> > where it is mentioned.
>
> Right at the top of create view? :
>
> 
> Description
>
> CREATE VIEW defines a view of a query. The view is not physically
> materialized. Instead, the query is run every time the view is
> referenced in a query.
>
> CREATE OR REPLACE VIEW is similar, but if a view of the same name
> already exists, it is replaced. The new query must generate the same
> columns that were generated by the existing view query (that is, the
> same column names in the same order and with the same data types), but
> it may add additional columns to the end of the list. The calculations
> giving rise to the output columns may be completely different.
> <<
>
> Francisco Olarte.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 

Thanks and Regards,
Sachin Kotwal

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

[Charles Clavadetscher]

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 then what is use of
"Create OR Replace " syntax?


I am not sure about the concrete rationale behind it, but the 
replacement of a view is useful when you make changes in the way how you 
select data from different tables without changing the structure of the 
record returned by the view. In our company views are used a.o. as 
interfaces to applications. They may change during development, but 
later on they become stable. If the design of the tables behind the view 
changes you may need to change the body of the view leaving the 
interface the same. In this way you can optimize performance in the 
database without forcing application developers to make changes to their 
code.


Besides, dropping and creating a view costs nothing in terms of performance.


If its not a bug and a limitation kindly guide me towards any
documentation where it is mentioned.


http://www.postgresql.org/docs/current/static/sql-createview.html

CREATE OR REPLACE VIEW is similar, but if a view of the same name
already exists, it is replaced. The new query must generate the same
columns that were generated by the existing view query (that is, the
same column names in the same order and with the same data types),
but it may add additional columns to the end of the list. The
calculations giving rise to the output columns may be completely
different.


If this is the limitation. Is community is planning update this or add
this feature soon?


Unfortunately I have no answer to that, but somebody else may.
Regards
Charles




--

Thanks and Regards,
Sachin Kotwal


--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org


--
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?

[Charles Clavadetscher]

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 you use for selecting which rows must be deleted.


CREATE INDEX ... ON tablea (...) WHERE ...;

This would not make deletion quicker, but operations should have a gain.

What I also see is that you don't have any condition when selecting from 
the loop, i.e. you actually go through all rows and decide on the fly 
which you must delete. Maybe reducing the number of rows to be processed 
by the function helps.


Finally I don't know the structure of the table, but you may select only 
those columns that you need for your summary instead of *. If you have 
many columns this could have an impact on the performance of the select 
statement.


Regards
Charles

On 05/16/2016 07:09 AM, Haiming Zhang 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 new table for backup purpose.
So that mean I need to go through each row and add the columns' value to
the summary table (the corresponding category).

The table has indexes (multicoumn indexes) before, I removed the indexes
because I read on forum says the indexes *heavily* reduce the speed of
deleting. That's true if I only delete from the table, but my case is I
first SELECT this row, then add few values to the summary table and then
DELETE the row according to its multiple primary key. This seems to be a
bad idea now as it takes very long time to DELETE the row (as delete
needs to find the row first).

Here are the two version of the delete functions, please help to point
out how can I speed it up.

1.

CREATE OR REPLACE FUNCTION summary_delete_table()

RETURNS integer AS

$BODY$

DECLARE


rec RECORD;


subrec  RECORD;

BEGIN

 FOR rec
IN SELECT * FROM tableA limit 100 LOOP


 BEGIN


 UPDATE 
summaryTable set count1 = rec.count1 + rec.count1, count2 =... where category 
match;


 delete from 
tableA where tableA.primaryKeyA=rec.primaryKeyA and 
tableA.primaryKeyB=rec.primaryKeyB;


 END;

 END LOOP;

 return 1;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

And then I have a .bat script to loop the above function million times.

2.

CREATE OR REPLACE FUNCTION summary_delete_table()

RETURNS integer AS

$BODY$

DECLARE


rec RECORD;


td_cursorCURSOR FOR SELECT * FROM tableA limit 100;

BEGIN

 FOR rec
IN td_cursor LOOP


 BEGIN


 UPDATE summaryTable set count1 = rec.count1 + 
rec.count1, count2 =... where category match;


 delete from tableA WHERE CURRENT OF td_cursor;


 END;

 END LOOP;

 return 1;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

Method 2 is bit faster but not much, the delete speed is 2478 rows/s for
method 2 and 2008 rows/s for method 1.

Any suggestions are welcomed.

BTW, I guess if reindex, it may take few days to finish.

Also, I tried change delete 100 rows at a time and 1000, 2000. The
result showed 1000 is faster than 100 and 2000 a time.

Thanks and Regards,

Haiming



*Haiming Zhang* | Engineer | *Redflex Group*
*T* +61 3 9674 1868 | *F* +61 3 9699 3566 | *E*
haiming.zh...@redflex.com.au
31 Market Street, (PO Box 720), South Melbourne VIC 3205, Australia

If you are not an authorised recipient of this e-mail, please contact me
at Redflex immediately by return phone call or by email. 

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

[Francisco Olarte]

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 you are using create or replace, which is
normally used to switch things in place and so it needs them to be
compatible with the old ones. A view with an extra column can be used
instead of the old one, but a view with less columns can not. I do not
see the 'not dropping' part as unusual, and the 'can add columns',
well, I see them as a little unusual on a create or replace but I see
the point in hallowing it, so just a little.

> Alter command do not have any option to drop column
> postgres=# alter view vi1
> ALTER COLUMN  OWNER TO  RENAME TO SET SCHEMA

Well, it is a view, not a table. They are basically shorthands for
queries and places to attach triggers, so its normal they do not have
as much management options.

> If its not a bug and a limitation kindly guide me towards any documentation
> where it is mentioned.

Right at the top of create view? :


Description

CREATE VIEW defines a view of a query. The view is not physically
materialized. Instead, the query is run every time the view is
referenced in a query.

CREATE OR REPLACE VIEW is similar, but if a view of the same name
already exists, it is replaced. The new query must generate the same
columns that were generated by the existing view query (that is, the
same column names in the same order and with the same data types), but
it may add additional columns to the end of the list. The calculations
giving rise to the output columns may be completely different.
<<

Francisco Olarte.


-- 
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] View not allowing to drop column (Bug or Feature enhancement )

[Charles Clavadetscher]

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 for the same.

*
*
*Version info *
*===*
postgres=# select version();
  version
--
  PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-55), 64-bit
(1 row)
\d+ orgdata
 Table "public.orgdata"
  Column  | Type  | Modifiers | Storage  | Stats target
| Description
-+---+---+--+--+-
  id  | integer| not null  | plain|  |
  name| character varying(20) | not null  | extended |  |
  address | character varying(20) |   | extended |  |
  age | integer| not null  | plain|  |
  salary  | numeric(10,0)|   | main |  |
Indexes:
 "orgdata_pkey" PRIMARY KEY, btree (id)
Triggers:
 example_trigger AFTER INSERT ON orgdata FOR EACH ROW EXECUTE
PROCEDURE auditlogfunc()

*Creating view *
postgres=# create or replace view vi1  as
select id , name from orgdata ;
CREATE VIEW

*Alter command do not have any option to drop column*
postgres=# alter view vi1
ALTER COLUMN  OWNER TO  RENAME TO SET SCHEMA


Neither there is an option to add a column.


*
*
*To add columns it will work.*
**
postgres=# create or replace view vi1 as
postgres-# select id, name, age from orgdata ;
CREATE VIEW


What you are doing is actually a CREATE OR REPLACE VIEW and not a ALTER 
VIEW. In this case it is allowed to add columns *at the end of the list* 
(see below).



*
*
*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 orgdata ;
*ERROR:  cannot drop columns from view*


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.



If its not a bug and a limitation kindly guide me towards any
documentation where it is mentioned.


http://www.postgresql.org/docs/current/static/sql-createview.html

CREATE OR REPLACE VIEW is similar, but if a view of the same name 
already exists, it is replaced. The new query must generate the same 
columns that were generated by the existing view query (that is, the 
same column names in the same order and with the same data types), but 
it may add additional columns to the end of the list. The calculations 
giving rise to the output columns may be completely different.


Regards,
Charles



Thanks.
--
Shrikant Bhende
+91-9975543712


--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general