Re: [SQL] Concurrent Reindex on Primary Key for large table
> -Original Message- > From: rverghese [mailto:ri...@hotmail.com] > Sent: Thursday, February 09, 2012 12:49 PM > To: pgsql-sql@postgresql.org > Subject: Re: Concurrent Reindex on Primary Key for large table > > Thanks! That worked. > > Any thoughts about containing index bloat. I thought the autovac would > clean it up a bit more. Would any tweaks to my settings improve autovac > performance? I am still doing a couple of concurrent reindexes per week > otherwise performance degrades over a couple of days. > > Thanks > RV > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Concurrent-Reindex-on-Primary- > Key-for-large-table-tp5467243p5470216.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. Did you try to make autovacuum "more aggressive" like lowering autovacuum_vacuum_threshold from 2? Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] problem with nested dblink function
Hello everyone !
I have a problem with dblink function.
I need to insert data using pl / pgsql function from one server to
another.
The data from server FS4 to server FS3.
This code does not work:
"select dblink ('dbname = ant_trendy port = 5433 host = FS3 user =
postgres password = postgres ', 'insert into rap.5_7_fil (select *
from temp.fil_packet_template)')"
-->temp.fil_packet_template<-- is table from server FS4
Code with nested function dblink works with the Query window but when
it invokes the function inside the pl / pgsql I get the message all
right but the data are not inserted.
Code work with Query window / doesn't work with pl/pgsql function:
"select dblink
(' dbname=ant_trendy port=5433 host=FS3 user=postgres
password=postgres ',
' insert into rap.5_7_fil (select * from dblink ('' dbname=ant_trendy
port=5432 host=FS4 user=postgres password=postgres '', '' select *
from temp.fil_packet_template '')
as (
"time" timestamp without time
zone,
tzewn double precision,
tzas_msc double precision,
tpow_msc double precision,
vflow double precision,
pwr double precision,
tpwymco double precision,
tpwymco_obl double precision,
tzas double precision,
tzas_obl double precision,
tpow double precision,
tcwu double precision,
tcwu_obl double precision,
tcwu_cyrk double precision,
pstat double precision,
pwr_proc double precision,
tpow_diff double precision,
x_rez_1 double precision,
x_rez_2 double precision,
x_rez_3 double precision,
x_rez_4 double precision
))
')"
Does call the nested function dblink from pl / pgsql functions has
there any restrictions?
How can I in a different way to pass data between servers?
Thanks in advance.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] update multiple columns with multiple values
Dear list,
I am trying to update all rows of a column ('parent') based on a subquery
that returns me the first four digits of another column ('id'):
After executing, I get the following error-message:
I am not quite sure about that, but maybe I need to construct a function to
fulfill this task?
thanks for help.
Stefan
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/update-multiple-columns-with-multiple-values-tp5465796p5465796.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] problem with nested dblink function
I'm sorry, everything is in order. Today I looked with fresh eyes, a slight adjustment in the calling function and it works. Thanks for your attention. Best regards. Ps. recently I deal with postgresql and I wanted to consult with you. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] update column
Dear list,
sorry, I already posted this, but it did not seem to have been accepted by
the mailing list. So here's my second try:
I need to update all rows of a column ('parent') based on a subquery that
returns me the first four digits of another column ('id'):
UPDATE admin SET parent=(SELECT SUBSTR(id,1,4) FROM admin);
After executing, I get the following error-message:
"more than one row returned by a subquery used as an expressionmore than one
row returned by a subquery used as an expression"
I am not quite sure about that, but maybe I need to construct a
function/loop to fulfill this task?
thanks for help.
Stefan
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/update-column-tp5468330p5468330.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] update column with multiple values
Dear list,
sorry, I already posted this, but it did not seem to have been accepted by
the mailing list. So here's my second try:
I need to update all rows of a column ('parent') based on a subquery that
returns me the first four digits of another column ('id'):
UPDATE admin SET parent=(SELECT SUBSTR(id,1,4) FROM admin);
After executing, I get the following error-message:
"more than one row returned by a subquery used as an expressionmore than one
row returned by a subquery used as an expression"
I am not quite sure about that, but maybe I need to construct a
function/loop to fulfill this task?
thanks for help.
Stefan
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/update-column-with-multiple-values-tp5468395p5468395.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] update column
UPDATE admin
SET parent = SUBSTR(id,1,4);
Doesn't it do what you want?
Best,
Oliveiros
- Original Message -
From: "ssylla"
To:
Sent: Thursday, February 09, 2012 1:58 AM
Subject: [SQL] update column
Dear list,
sorry, I already posted this, but it did not seem to have been accepted by
the mailing list. So here's my second try:
I need to update all rows of a column ('parent') based on a subquery that
returns me the first four digits of another column ('id'):
UPDATE admin SET parent=(SELECT SUBSTR(id,1,4) FROM admin);
After executing, I get the following error-message:
"more than one row returned by a subquery used as an expressionmore than
one
row returned by a subquery used as an expression"
I am not quite sure about that, but maybe I need to construct a
function/loop to fulfill this task?
thanks for help.
Stefan
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/update-column-tp5468330p5468330.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] update column with multiple values
-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
On Behalf Of ssylla
Sent: Wednesday, February 08, 2012 9:31 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] update column with multiple values
Dear list,
sorry, I already posted this, but it did not seem to have been accepted by
the mailing list. So here's my second try:
I need to update all rows of a column ('parent') based on a subquery that
returns me the first four digits of another column ('id'):
UPDATE admin SET parent=(SELECT SUBSTR(id,1,4) FROM admin);
After executing, I get the following error-message:
"more than one row returned by a subquery used as an expressionmore than one
row returned by a subquery used as an expression"
I am not quite sure about that, but maybe I need to construct a
function/loop to fulfill this task?
thanks for help.
Stefan
---
No sub-select required, just use the function:
UPDATE admin SET parent = substr(id,1,4);
This will, for each record, set the value of parent to the first four
characters of its ID.
When you use the sub-select there is not inherent linkage between the
"UPDATE" table and the "FROM" table. You can make a correlated sub-query
but in this case the is necessary.
David J.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
