Обсуждение: CLUSTER not in multi-command string?
ERROR: CLUSTER cannot be executed from a function or multi-command string I got this error from a script which tried: psql -U postgres db1 -c 'cluster ; analyze' In the docs there's no word that it's not possible, and I wonder why this is not allowed. Anybody knows? It's stupid, as I want to cluster lots of tables, followed by analyze, and now I have to psql -U postgres db1 -c 'cluster' psql -U postgres db1 -c 'analyze' mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4
Вложения
On Mittwoch 29 April 2009 Alvaro Herrera wrote: > Michael Monnerie wrote: > > ERROR: CLUSTER cannot be executed from a function or multi-command > > string > > > > I got this error from a script which tried: > > psql -U postgres db1 -c 'cluster ; analyze' > > > > In the docs there's no word that it's not possible, and I wonder > > why this is not allowed. Anybody knows? It's stupid, as I want to > > cluster lots of tables, followed by analyze, and now I have to > > psql -U postgres db1 -c 'cluster' > > psql -U postgres db1 -c 'analyze' > > Multi-table cluster (i.e. the one where you don't specify one table > to cluster) cannot be used in a transaction block, function, etc, and > the FM does mention it: > > CLUSTER without any parameter reclusters all the > previously-clustered tables in the current database that the calling > user owns, or all such tables if called by a superuser. This form of > CLUSTER cannot be executed inside a transaction block. Ah, found this now, in the docs 8.1-8.3. But strange thing is.. [I sent this mail this morning at 07:08 local time but it didn't make it through, without me getting any note:] I looked further now: Above error was from Postgres 8.3.5 on openSUSE 11.1, but it works on Postgres 8.1.11 from openSUSE 10.2. Why did it become worse? According to docs this shouldn't be possible even in 8.1. Is there any change I can make a single db call with cluster & analyze, like: cluster ; begin ; analyze ; commit mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4
On Wed, Apr 29, 2009 at 10:24:51PM +0200, Michael Monnerie wrote: > On Mittwoch 29 April 2009 Alvaro Herrera wrote: > > Michael Monnerie wrote: > > > ERROR: CLUSTER cannot be executed from a function or multi-command > > > string > > > > > > I got this error from a script which tried: > > > psql -U postgres db1 -c 'cluster ; analyze' > > > > > > In the docs there's no word that it's not possible, and I wonder > > > why this is not allowed. Anybody knows? It's stupid, as I want to > > > cluster lots of tables, followed by analyze, and now I have to > > > psql -U postgres db1 -c 'cluster' > > > psql -U postgres db1 -c 'analyze' > > > > Multi-table cluster (i.e. the one where you don't specify one table > > to cluster) cannot be used in a transaction block, function, etc, and > > the FM does mention it: > > > > CLUSTER without any parameter reclusters all the > > previously-clustered tables in the current database that the calling > > user owns, or all such tables if called by a superuser. This form of > > CLUSTER cannot be executed inside a transaction block. > > Ah, found this now, in the docs 8.1-8.3. But strange thing is.. > > [I sent this mail this morning at 07:08 local time but it didn't make it > through, without me getting any note:] > > I looked further now: Above error was from Postgres 8.3.5 on openSUSE > 11.1, but it works on Postgres 8.1.11 from openSUSE 10.2. Why did > it become worse? According to docs this shouldn't be possible even in > 8.1. Is there any change I can make a single db call with cluster & > analyze, like: > cluster ; begin ; analyze ; commit > > mfg zmi Issue a cluster command for each table individually in the command stream. Cheers, Ken
Michael Monnerie wrote:
> On Mittwoch 29 April 2009 Alvaro Herrera wrote:
> > Multi-table cluster (i.e. the one where you don't specify one table
> > to cluster) cannot be used in a transaction block, function, etc, and
> > the FM does mention it:
> Ah, found this now, in the docs 8.1-8.3. But strange thing is..
>
> [I sent this mail this morning at 07:08 local time but it didn't make it
> through, without me getting any note:]
The lists have been dead slow today; I think we have a problem with the
antispam setup, and the admin is off in a trip. (This is why it's a
good idea to CC people on replies.)
> I looked further now: Above error was from Postgres 8.3.5 on openSUSE
> 11.1, but it works on Postgres 8.1.11 from openSUSE 10.2. Why did
> it become worse?
Well, it became more consistent with what happens when you submit stuff
on several requests versus a single request (IIRC psql -c always submit the
string as a single request). The 8.3 release notes have:
# Commands rejected in transaction blocks are now also rejected in
multiple-statement query strings (Tom)
For example, "BEGIN; DROP DATABASE; COMMIT" will now be rejected
even if submitted as a single query message.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Michael Monnerie wrote:
> ERROR: CLUSTER cannot be executed from a function or multi-command
> string
>
> I got this error from a script which tried:
> psql -U postgres db1 -c 'cluster ; analyze'
>
> In the docs there's no word that it's not possible, and I wonder why
> this is not allowed. Anybody knows? It's stupid, as I want to cluster
> lots of tables, followed by analyze, and now I have to
> psql -U postgres db1 -c 'cluster'
> psql -U postgres db1 -c 'analyze'
Multi-table cluster (i.e. the one where you don't specify one table to
cluster) cannot be used in a transaction block, function, etc, and the
FM does mention it:
CLUSTER without any parameter reclusters all the
previously-clustered tables in the current database that the calling
user owns, or all such tables if called by a superuser. This form of
CLUSTER cannot be executed inside a transaction block.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Mittwoch 29 April 2009 Michael Monnerie wrote: > ERROR: CLUSTER cannot be executed from a function or multi-command > string > > I got this error from a script which tried: > psql -U postgres db1 -c 'cluster ; analyze' > > In the docs there's no word that it's not possible, and I wonder why > this is not allowed. Anybody knows? It's stupid, as I want to cluster > lots of tables, followed by analyze, and now I have to > psql -U postgres db1 -c 'cluster' > psql -U postgres db1 -c 'analyze' I looked further now: Above error was from Postgres 8.3.5 on openSUSE 11.1, but it used to work on Postgres 8.1.11 from openSUSE 10.2. Why did it become worse? mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4
Вложения
On Mittwoch 29 April 2009 Michael Monnerie wrote: > Is there any change I can make a single db call with cluster & > analyze, like: > cluster ; begin ; analyze ; commit From the man page of psql I found this: echo 'cluster; analyze'|psql -U postgres dbname Works both on 8.1 and 8.3. From a user pov this is a bit strange, but I found a workaround and that's enough for me. Thanks, Alvaro, for the hint. mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4