Thread: strange behaviour with sub-select and pl/pgSQL
Hi, I found PostgreSQL have strange behaviour with sub-select and pl/pgSQL, see the case study at: http://www.bobson.pl/pgsql/pgsql_sb1.html I know my version is quite old. Its most new in gentoo (hope 8.2 will be soon in gentoo portage). test=# select version(); version --------------------------------------------------------------------------------------------------------- PostgreSQL 8.1.5 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 4.1.1 (Gentoo 4.1.1) regards, Robert bobson@bobson.pl
Robert Partyka <rpartyka@wdg.pl> writes: > http://www.bobson.pl/pgsql/pgsql_sb1.html 8.2 won't flatten sub-selects that contain volatile functions in their targetlists... regards, tom lane
Hi!
This is a known problem on systems, where you try to install
PostgreSQL via remote Terminal Service Session. You can install server
without initiating database, and later run initdb under non privileged
user using cmd. You should specify the same datadir which postgresql
service would expect as a data folder. You can do:
runas /user:NON_PRIVILEGED_USER_NAME cmd
[in the newly opened window run initdb:]
initdb -D "POSTGRESQL_INSTALL_PATH/pgdata" -U SUPER_USER_NAME -W
For additional info read PostgreSQL manual or type "initdb --help"
Best regards,
Andrei Kovalevski
-- Original
message------------------------------------------------------------------------
I am installing on a Cisco Media Server 7800 running Win2k Server and am
noticing that the installer fails every time with the error dialog
saying "Failed to create process for initdb: Access is denied". It looks
like that I need to change some permission in the registry allowing
other users spawn processes. Any pointer in this respect is highly
appreciated.
On Tue, Feb 27, 2007 at 12:15:03PM +0100, Robert Partyka wrote: > Hi, > > I found PostgreSQL have strange behaviour with sub-select and pl/pgSQL, > see the case study at: > http://www.bobson.pl/pgsql/pgsql_sb1.html The PostgreSQL optimiser is pretty smart and will pull up simple sub-selects like yours. If you put your subselect into a view, you'd also want the optimiser to find the optimal plan, right? If you want to stop it pulling up the subquery, you can add an OFFSET 0 or ORDER BY or anything that makes it a not-simple subquery. That tells the planner to leave it alone. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.