Re: [SQL] Concurrent Reindex on Primary Key for large table
> -Original Message- > From: rverghese [mailto:[email protected]] > Sent: Thursday, February 09, 2012 12:49 PM > To: [email protected] > 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 ([email protected]) 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 ([email protected])
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 ([email protected])
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 ([email protected]) 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 ([email protected])
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 ([email protected])
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] update column with multiple values
-Original Message- From: [email protected] [mailto:[email protected]] On Behalf Of ssylla Sent: Wednesday, February 08, 2012 9:31 PM To: [email protected] 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
