Re: [GENERAL] Somewhat odd messages being logged on replicated server
On Wed, 2010-09-29 at 21:57 -0500, Karl Denninger wrote: That's actually ok - the update itself is a legitimate statement on the master, posted to that table on a reply, and is part of a transaction. Even if it's part of a transaction, you still have a race condition, unless you're using a serializable transcation isolation level. I thought logshipping like this over TCP would present the actual WAL changes (e.g. page changes) and was quite surprised when I saw what look very much like actual statements in the logfile. A process status also shows no postgres processes. 9.0-style replication only ships WAL. Those UPDATE statements are _not_ coming from the 9.0-style log shipping replication. All of the real application connections are on a machine 1200 miles away and behind pgpool, so if there WAS a connection it would be persistent. It's not there (never mind that the clients don't have an entry in pg_hba that would work, nor could they cross the firewall since there is no port forwarding in the firewall to allow it.) Aha, you're using pgpool. The statements are probably coming from there -- it's got a lot of options and it may be misconfigured. Turn on some extended logging information that includes the client IP address. That will probably give you some better insight. Regards, Jeff Davis -- 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] PostgreSQL server not starting.
Can you send the installation log (%TEMP%\install-postgresql.log)? In case, Have you used the uninstaller to uninstall the previous instance or manually removed the directories? On Sep 30, 2010, at 12:53 PM, Vishnu S. wrote: Hi, I have uninstalled the PostgreSQL 8.4 and removed the related directories from the system. Again I have installed the PostgreSQL 8.4 on the same folder. Now the PostgreSQL server is not starting. When I tried to start the postgreSQL service from the Windows Service manager, the following message is shown. “The PostgreSQL Server 8.4 service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service”. Host :- localhost Port :- 5432 Database :- postgres Thanks Regards, Vishnu S -- Regards, Sachin Srivastava EnterpriseDB, the Enterprise Postgres company.
Re: [GENERAL] Missing path in pg_config
On Thu, Sep 30, 2010 at 00:55, Dave Page dp...@pgadmin.org wrote: On Wed, Sep 29, 2010 at 7:23 PM, Turner, John J jjtur...@statestreet.com wrote: Hello, I’ve installed the PostgreSQL 9.0 binary package for Windows XP 32-bit and I’m trying to install the temporal extension module available in pg_Foundry. One problem I can see that I’m running into is that the path defined for PGXS in pg_config does not exist: PGXS = c:/program files/PostgreSQL/9.0/lib/pgxs/src/makefiles/pgxs.mk I have a path up to the “…/lib” but nothing after that. Do I need to re-install something or run a script to flesh things out? It seems when I follow the instructions for the temporal module install (as found here), it bombs out when it tries to find this path while running make install PGXS doesn't work with the VC++ build we use for Windows. I wonder if that output from pg_config should be #ifdef'd out until such time as we can figure out a way to make it work - if that's even possible. I don't know pgxs internals well enough, but would it work if we just shipped the pgxs file - for users of mingw? I have a feeling it won't - the thing uses files generated by the postgresql ./configure-script, doesn't it? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] PostgreSQL 9.0 (x86-64) and Windows 7 (x86-64) - Unable to install
Hi Peter, We tried to reproduce this issue but could not do so. We have tried both the cases but both were not reproducible. Can you please provide more information which can help us in reproducing the issue, Thanks, Dharmendra From: Dr. Peter Voigt pvo...@uos.de Date: Tue, Sep 28, 2010 at 11:53 PM Subject: [GENERAL] PostgreSQL 9.0 (x86-64) and Windows 7 (x86-64) - Unable to install To: pgsql-general@postgresql.org I cannot install PostgreSQL 9.0 (x86-64) under Windows 7 (x86-64). The installer fails right after starting the installation process with the message: An error occurred executing the Microsoft VC++ runtime installer. I am using the installer from EnterpriseDB http://www.enterprisedb.com/products/pgdownload.do. Installation file is postgresql-9.0.0-1-windows_x64.exe. Unfortunately there is no %TEMP%\install-postgresql.log. When scanning the mailing lists under http://www.postgresql.org/community/lists/ and under http://forums.enterprisedb.com/forums/show/9.page I can see that this error has been described for several times with PostgreSQL 8.3 and 8.4 under different Windows variants. A common hint was to activate the Windos Scripting Host (WSH) allthough it obviously does not help in all cases. On my machine the WSH is activated and working. Under http://www.enterprisedb.com/learning/pginst_guide.do#troubleshooting you can read about the command line options of the EnterpriseDB PostgreSQL Installer. An attempt with --install_runtimes 0 fails again but with the different error message: Unknown error while running C:\Users\Administrator\Lokale Einstellungen\postgres_installer\getlocales.exe Again there is no %TEMP%\install-postgresql.log. As the second message is suggesting I am working as local Administrator while installing PostgreSQL. Maybe it is worth to be mentioned that I have installed Microsoft Visual Studio 2008 Pro DE. Therefore the installation of the VC++ runtime should not be neccessary. I am using now MySQL for serveral years and would like to compare it with a current PostgreSQL version. The installation of PostgreSQL under Windows is really disappointing but the same worked without problems under Linux x86-64 (openSUSE 11.0). Under Linux I have used the EnterpriseDB Installer of PostgreSQL 9.0 (x86-64) as well. The installation file is postgresql-9.0.0-1-linux-x64.bin. Is this problem already known and is there a solution for it? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Dharmendra Goyal Senior Software Engineer EnterpriseDB Corporation The Enterprise Postgres Company Phone: +91-20-30589493 Mobile: +91-9552103323 Website: http://www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
Re: [GENERAL] PostgreSQL server not starting.
[Please keep the thread on the mailing list] From the logs, the initdb is successful but there were some error while creating the postgres service. I believe you are not using the latest 8.4.4 installer as that installer has some detailed logging which is helpful in realizing where exactly is the problem. Though since you have the initdb successful, you can manually create the service using pg_ctl command (See: http://www.postgresql.org/docs/8.4/static/app-pg-ctl.html) On Sep 30, 2010, at 2:10 PM, Vishnu S. wrote: Hi, Please find the attached PostgreSQL installation log file. I have used Uninstaller to uninstall the PostgreSQL and then the data directory is removed manually. Thanks Regards, Vishnu S From: Sachin Srivastava [mailto:sachin.srivast...@enterprisedb.com] Sent: Thursday, September 30, 2010 1:41 PM To: Vishnu S. Cc: pgsql-general@postgresql.org Subject: Re: PostgreSQL server not starting. Can you send the installation log (%TEMP%\install-postgresql.log)? In case, Have you used the uninstaller to uninstall the previous instance or manually removed the directories? On Sep 30, 2010, at 12:53 PM, Vishnu S. wrote: Hi, I have uninstalled the PostgreSQL 8.4 and removed the related directories from the system. Again I have installed the PostgreSQL 8.4 on the same folder. Now the PostgreSQL server is not starting. When I tried to start the postgreSQL service from the Windows Service manager, the following message is shown. “The PostgreSQL Server 8.4 service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service”. Host :- localhost Port :- 5432 Database :- postgres Thanks Regards, Vishnu S -- Regards, Sachin Srivastava EnterpriseDB, the Enterprise Postgres company. install-postgresql.log -- Regards, Sachin Srivastava EnterpriseDB, the Enterprise Postgres company.
[GENERAL] [9.0] On temporary tables
Hi all. This is my case: -- begin snippet -- reset search_path; drop table if exists session cascade; create table session ( name text primary key, valu text not null ); create or replace function session_init() returns void language plpgsql as $body$ declare t text; begin select valu into t from session where name='SESSION_ID'; if not found then create temporary table session ( like public.session including all ); insert into session values ( 'SESSION_ID',current_user ); end if; end; $body$; SELECT * from session; SELECT * from session_init(); SELECT * from session; SELECT * from session_init(); -- end snippet -- The output from the last four queries is: -- tmp2=# SELECT * from session; name | valu --+-- (0 rows) tmp2=# SELECT * from session_init(); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index session_pkey for table session CONTEXT: SQL statement create temporary table session ( like public.session including all ) PL/pgSQL function session_init line 6 at istruzione SQL session_init -- (1 row) tmp2=# SELECT * from session; name| valu +-- SESSION_ID | enzo (1 row) tmp2=# SELECT * from session_init(); ERROR: relation session already exists CONTEXT: SQL statement create temporary table session ( like public.session including all ) PL/pgSQL function session_init line 6 at istruzione SQL -- This means that the if not found then in the function body didn't work well. The idea is to create a temporary table to store session variables only of there's no temporary table with that name. Any hint on this? -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] rotate psql output
I have a query that returns many columns but few rows. I would like to display output horizontally instead of vertically, i.e. rotating by 90 degress, so column headings appear in the left margin, and the output is not 'wrapped'. Is this possible? I have had no luck searching for this as rotate usually means log rotation. BC
Re: [GENERAL] rotate psql output
Ben Carbery wrote: I have a query that returns many columns but few rows. I would like to display output horizontally instead of vertically, i.e. rotating by 90 degress, so column headings appear in the left margin, and the output is not 'wrapped'. Is this possible? I have had no luck searching for this as rotate usually means log rotation. BC Have you tried \x ? -- Tommy Gildseth DBA, Gruppe for databasedrift Universitetet i Oslo, USIT m: +47 45 86 38 50 t: +47 22 85 29 39 -- 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] rotate psql output
On Thu, 2010-09-30 at 21:17 +1000, Ben Carbery wrote: I have a query that returns many columns but few rows. I would like to display output horizontally instead of vertically, i.e. rotating by 90 degress, so column headings appear in the left margin, and the output is not 'wrapped'. Is this possible? psql -x ? Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] rotate psql output
On Thu, Sep 30, 2010 at 9:19 PM, Tommy Gildseth tommy.gilds...@usit.uio.nowrote: Ben Carbery wrote: I have a query that returns many columns but few rows. I would like to display output horizontally instead of vertically, i.e. rotating by 90 degress, so column headings appear in the left margin, and the output is not 'wrapped'. Is this possible? I have had no luck searching for this as rotate usually means log rotation. BC Have you tried \x ? -- Tommy Gildseth Hadn't tried that. Actually it's much better, but each record is displayed separately, so visually comparing 2 or 3 records is not possible. Strange if this can't be done, I would have thought it a common request!
Re: [GENERAL] rotate psql output
2010/9/30 Ben Carbery ben.carb...@gmail.com: Strange if this can't be done, I would have thought it a common request! Just curiosity. Is there any other DB capable of such a thing? -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- 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] rotate psql output
On 30/09/2010 12:17, Ben Carbery wrote: I have a query that returns many columns but few rows. I would like to display output horizontally instead of vertically, i.e. rotating by 90 degress, so column headings appear in the left margin, and the output is not 'wrapped'. Is this possible? I have had no luck searching for this as rotate usually means log rotation. Googling on sql swap rows columns found this: http://stackoverflow.com/questions/584232/t-sql-how-to-swap-rows-and-columns ...which you'll be able to adapt. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] rotate psql output
2010/9/30 Raymond O'Donnell r...@iol.ie: On 30/09/2010 12:17, Ben Carbery wrote: Googling on sql swap rows columns found this: http://stackoverflow.com/questions/584232/t-sql-how-to-swap-rows-and-columns ...which you'll be able to adapt. We also have the fantastic crosstab in tablefunc module (see chapter F.36.1.4 for v9.0.0) -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- 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] rotate psql output
On Thu, Sep 30, 2010 at 9:36 PM, Vincenzo Romano vincenzo.rom...@notorand.it wrote: 2010/9/30 Raymond O'Donnell r...@iol.ie: On 30/09/2010 12:17, Ben Carbery wrote: Googling on sql swap rows columns found this: http://stackoverflow.com/questions/584232/t-sql-how-to-swap-rows-and-columns ...which you'll be able to adapt. We also have the fantastic crosstab in tablefunc module (see chapter F.36.1.4 for v9.0.0) -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS ..but these seem to be more pivot table functions that alter the data, rather than displaying it differently. Not sure if this method is adaptable but I may have a go. To me swapping would be better suited as an option to psql. After all, calling columns 'columns' in sql is aribtrary - they could just as easily have been called rows or something else, so there's no reason for the output to be fixed that way. Although that would chew a lot of memory if you have a lot of records as you would need to read every record before formatting..and I guess that explains why it hasn't been done. No idea if this can be done in other clients!
Re: [GENERAL] rotate psql output
2010/9/30 Ben Carbery ben.carb...@gmail.com: On Thu, Sep 30, 2010 at 9:36 PM, Vincenzo Romano vincenzo.rom...@notorand.it wrote: We also have the fantastic crosstab in tablefunc module (see chapter F.36.1.4 for v9.0.0) ..but these seem to be more pivot table functions that alter the data, rather than displaying it differently. Not sure if this method is adaptable but I may have a go. Alter? crosstab doesn't alter anything if you don't ask to. To me swapping would be better suited as an option to psql. After all, calling columns 'columns' in sql is aribtrary - they could just as easily have been called rows or something else, so there's no reason for the output to be fixed that way. Although that would chew a lot of memory if you have a lot of records as you would need to read every record before formatting..and I guess that explains why it hasn't been done. No idea if this can be done in other clients! Actually none, AFAIK. -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [9.0] hot standby plus streaming replication questions
Hi list, I'm trying the new features into a test environment for see how all works and I'm finding first problems with setup. After reading the various Streaming_Replication, Warm_Standby and What's_new_in_PostgreSQL_9.0 on the wiki, one first question born: both server (master and standby) need a common directory where read and write the wal files? I don't find a reference where it's specified or said. On my tests it's needed only for the first startup of the standby server. After I can disable it on the master (archive_mode=off) and the replication is performed however, although the client warn me about a no such file or directory wal_archive/0001000N+1 where N is the last file wal archive that I have on the master, before disable the archive_mode. Also on the my tests, I see that the replication happen also if I stop the slave, add a 1k records to the master and re-start the slave. After some seconds, on the master I see the connection and querying the slave I see that all the record (previous inserted on the master) are there! Just another question about replication: there is the possibility to build a sync between a 32 and a 64 bit (on linux)? Thanks, Michele -- 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] PostgreSQL 9.0 (x86-64) and Windows 7 (x86-64) - Unable to install
Hi Dharmendra, thanks for your reply. This kind of errors, which cannot be reproduced on other machines are bad and leave no chance for developers to solve them. Unfortunately the installer does not leave any log files. The Windows event log has no entries about the installation attempt as well. I do not know what further information could be helpful. My first idea was that my installed MS Visual Studio 2008 could be the problem. Therefore I tried the --install_runtimes 0 option - unfortunately with no success. My Visual Studio installation works as expected. I conclude it from various projects which all built fine. I am running a Windows 7 x86-64 for about 3 months and it runs without problems. I cleanly installed the system onto an empty, e.g. formated harddrive. As the first described error An error occurred executing the Microsoft VC++ runtime installer has been discussed with previous releases of PostgreSQL, e.g. http://forums.enterprisedb.com/posts/list/2328.page I think that it is a known issue. Moreover, exactly the same error has been described in the EnterpriseDB forum under http://forums.enterprisedb.com/posts/list/2303.page with PostgreSQL 9.0 and Windows 7 x86-64. However, both posts remain un-replied until today. If there are no other users out there with comparable problems I could give the ZIP-installer a try under: http://www.enterprisedb.com/products/pgbindownload.do There is a file postgresql-9.0.0-1-windows_x64-binaries.zip. I did not yet try this because I am new to PostgreSQL. I first have to figure - how to start the database from the command line, - how to setup the PostgreSQL service from the command line, - what registry entries are required. If you can answer the above three questions (each with one sentence), I will immediately start installation and tests, because I hope - from my short but good PostgreSQL 9.0 experiences under Linux - that just the installer fails on my system but not the database system itself. If you need any other information that might help, please let me know. I would really like to get some more knowledge about PostgreSQL. Regards, Peter -- 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] PostgreSQL 9.0 (x86-64) and Windows 7 (x86-64) - Unable to install
On Thu, Sep 30, 2010 at 1:42 PM, Dr. Peter Voigt pvo...@uos.de wrote: Hi Dharmendra, thanks for your reply. This kind of errors, which cannot be reproduced on other machines are bad and leave no chance for developers to solve them. Unfortunately the installer does not leave any log files. Please look for any logfiles in %TEMP% starting with bitrock. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] rotate psql output
On 30/09/2010 12:31, Vincenzo Romano wrote: 2010/9/30 Ben Carbery ben.carb...@gmail.com: Strange if this can't be done, I would have thought it a common request! Just curiosity. Is there any other DB capable of such a thing? MySQL does this using an alternate end-of-statement character: ';' or '\g' gives the usual orientation, '\G' gives the rotated orientation. Cheers, Matthew -- Dr Matthew J Seaman MA, D.Phil. 7 Priory Courtyard Flat 3 PGP: http://www.infracaninophile.co.uk/pgpkey Ramsgate JID: matt...@infracaninophile.co.uk Kent, CT11 9PW signature.asc Description: OpenPGP digital signature
[GENERAL] PostgreSQL server not starting.
Hi, I have uninstalled the PostgreSQL 8.4 and removed the related directories from the system. Again I have installed the PostgreSQL 8.4 on the same folder. Now the PostgreSQL server is not starting. When I tried to start the postgreSQL service from the Windows Service manager, the following message is shown. The PostgreSQL Server 8.4 service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service. Host :- localhost Port :- 5432 Database :- postgres Thanks Regards, Vishnu S
Re: [GENERAL] PostgreSQL server not starting.
Hi, On starting the server using pg_ctl command the following message is shown. server starting C:\Program Files\PostgreSQL\8.4\bin2010-09-30 14:36:07 ISTLOG: could not create file postmaster.opts: Permission denied. Thanks Regards, Vishnu S From: Sachin Srivastava [mailto:sachin.srivast...@enterprisedb.com] Sent: Thursday, September 30, 2010 2:30 PM To: Vishnu S. Cc: pgsql-general@postgresql.org Subject: Re: PostgreSQL server not starting. [Please keep the thread on the mailing list] From the logs, the initdb is successful but there were some error while creating the postgres service. I believe you are not using the latest 8.4.4 installer as that installer has some detailed logging which is helpful in realizing where exactly is the problem. Though since you have the initdb successful, you can manually create the service using pg_ctl command (See: http://www.postgresql.org/docs/8.4/static/app-pg-ctl.html http://www.postgresql.org/docs/8.4/static/app-pg-ctl.html ) On Sep 30, 2010, at 2:10 PM, Vishnu S. wrote: Hi, Please find the attached PostgreSQL installation log file. I have used Uninstaller to uninstall the PostgreSQL and then the data directory is removed manually. Thanks Regards, Vishnu S From: Sachin Srivastava [mailto:sachin.srivast...@enterprisedb.com] Sent: Thursday, September 30, 2010 1:41 PM To: Vishnu S. Cc: pgsql-general@postgresql.org Subject: Re: PostgreSQL server not starting. Can you send the installation log (%TEMP%\install-postgresql.log)? In case, Have you used the uninstaller to uninstall the previous instance or manually removed the directories? On Sep 30, 2010, at 12:53 PM, Vishnu S. wrote: Hi, I have uninstalled the PostgreSQL 8.4 and removed the related directories from the system. Again I have installed the PostgreSQL 8.4 on the same folder. Now the PostgreSQL server is not starting. When I tried to start the postgreSQL service from the Windows Service manager, the following message is shown. The PostgreSQL Server 8.4 service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service. Host :- localhost Port :- 5432 Database :- postgres Thanks Regards, Vishnu S -- Regards, Sachin Srivastava EnterpriseDB http://www.enterprisedb.com , the Enterprise Postgres http://www.enterprisedb.com company. install-postgresql.log -- Regards, Sachin Srivastava EnterpriseDB http://www.enterprisedb.com , the Enterprise Postgres http://www.enterprisedb.com company.
Re: [GENERAL] Prepared statements and unknown types
-Original Message- From: Thom Brown [mailto:t...@linux.com] Sent: Wednesday, September 29, 2010 2:08 PM To: PGSQL Mailing List Subject: Prepared statements and unknown types Could someone explain why the following doesn't work? test=# PREPARE meow(unknown) AS test-# SELECT $1 as meow; ERROR: could not determine data type of parameter $1 The problem is that using PDO in PHP, prepared statements aren't possible if values are used instead of columns in the select list. This appears to be allowed for MySQL and SQL Server. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 Sorry, for being late to this conversation. Possibly it works for SQL Server, because SS has SQL_VARIANT data type (kind of anytype). Regards, Igor Neyman -- 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] [9.0] On temporary tables
Vincenzo Romano vincenzo.rom...@notorand.it writes: create or replace function session_init() returns void language plpgsql as $body$ declare t text; begin select valu into t from session where name='SESSION_ID'; if not found then create temporary table session ( like public.session including all ); insert into session values ( 'SESSION_ID',current_user ); end if; end; $body$; The idea is to create a temporary table to store session variables only of there's no temporary table with that name. That isn't going to work tremendously well. plpgsql will cache a plan for that SELECT on first use, and creation of the temp table is not an event that will cause replanning of a select that doesn't already use the temp table. If you're dead set on this design (which frankly doesn't seem like a terribly great idea to me), try doing the initial probe with an EXECUTE so it'll be replanned each time. Or you might try examining the system catalogs directly rather than relying on an attempted table access, eg if not exists (select 1 from pg_catalog where relname = 'session' and pg_table_is_visible(oid)) then ... create it ... That approach would work best if you *didn't* have any permanent table that the temp tables were masking, which on the whole seems like a smarter plan to me. regards, tom lane -- 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] [9.0] On temporary tables
2010/9/30 Tom Lane t...@sss.pgh.pa.us: Vincenzo Romano vincenzo.rom...@notorand.it writes: create or replace function session_init() returns void language plpgsql as $body$ declare t text; begin select valu into t from session where name='SESSION_ID'; if not found then create temporary table session ( like public.session including all ); insert into session values ( 'SESSION_ID',current_user ); end if; end; $body$; The idea is to create a temporary table to store session variables only of there's no temporary table with that name. That isn't going to work tremendously well. plpgsql will cache a plan for that SELECT on first use, and creation of the temp table is not an event that will cause replanning of a select that doesn't already use the temp table. If you're dead set on this design (which frankly doesn't seem like a terribly great idea to me), try doing the initial probe with an EXECUTE so it'll be replanned each time. Or you might try examining the system catalogs directly rather than relying on an attempted table access, eg if not exists (select 1 from pg_catalog where relname = 'session' and pg_table_is_visible(oid)) then ... create it ... That approach would work best if you *didn't* have any permanent table that the temp tables were masking, which on the whole seems like a smarter plan to me. Thanks for the feedback. Is the planner caching the plan even in case of VOLATILE functions? The DO construct executes perfectly with no apparent caching so I was excluding any plan caching. I was also thinking about using the catalog, but it looked to me easier my way. And, of course, if you have better advises for a session variables solution, my ears are open. Thanks again, Tom. -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- 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] [9.0] On temporary tables
Hello 2010/9/30 Tom Lane t...@sss.pgh.pa.us: Vincenzo Romano vincenzo.rom...@notorand.it writes: create or replace function session_init() returns void language plpgsql as $body$ declare t text; begin select valu into t from session where name='SESSION_ID'; if not found then create temporary table session ( like public.session including all ); insert into session values ( 'SESSION_ID',current_user ); end if; end; $body$; The idea is to create a temporary table to store session variables only of there's no temporary table with that name. That isn't going to work tremendously well. plpgsql will cache a plan for that SELECT on first use, and creation of the temp table is not an event that will cause replanning of a select that doesn't already use the temp table. I found a little bit faster solution a catching a exception. http://okbob.blogspot.com/2008/11/plpgsql-and-temp-tables.html but if you need a session variables, then you can use a plperl http://www.postgresql.org/docs/9.0/static/plperl-global.html Regards Pavel Stehule If you're dead set on this design (which frankly doesn't seem like a terribly great idea to me), try doing the initial probe with an EXECUTE so it'll be replanned each time. Or you might try examining the system catalogs directly rather than relying on an attempted table access, eg if not exists (select 1 from pg_catalog where relname = 'session' and pg_table_is_visible(oid)) then ... create it ... That approach would work best if you *didn't* have any permanent table that the temp tables were masking, which on the whole seems like a smarter plan to me. regards, tom lane -- 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] [9.0] On temporary tables
2010/9/30 Pavel Stehule pavel.steh...@gmail.com: Hello 2010/9/30 Tom Lane t...@sss.pgh.pa.us: Vincenzo Romano vincenzo.rom...@notorand.it writes: create or replace function session_init() returns void language plpgsql as $body$ declare t text; begin select valu into t from session where name='SESSION_ID'; if not found then create temporary table session ( like public.session including all ); insert into session values ( 'SESSION_ID',current_user ); end if; end; $body$; The idea is to create a temporary table to store session variables only of there's no temporary table with that name. That isn't going to work tremendously well. plpgsql will cache a plan for that SELECT on first use, and creation of the temp table is not an event that will cause replanning of a select that doesn't already use the temp table. I found a little bit faster solution a catching a exception. http://okbob.blogspot.com/2008/11/plpgsql-and-temp-tables.html Hmmm ... do you think the performance would be the same in v9? but if you need a session variables, then you can use a plperl http://www.postgresql.org/docs/9.0/static/plperl-global.html I will look into this. What I need is a set of variable for each connection. Regards Pavel Stehule Anyway, I'm quite puzzled by the fact that an EXCEPTION WHEN can be faster than an IF .. THEN .. ELSE with a rather simple test. Unless the pg_table_is_visible() is really bad. Thanks a lot. -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- 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] [9.0] On temporary tables
On 9/30/2010 8:52 AM, Vincenzo Romano wrote: I was also thinking about using the catalog, but it looked to me easier my way. And, of course, if you have better advises for a session variables solution, my ears are open. More of a question: why use temp tables at all? What does that offer that a single, regular, session table does not? -Andy -- 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] PostgreSQL server not starting.
On Thu, Sep 30, 2010 at 5:09 AM, Vishnu S. vishn...@nestgroup.net wrote: On starting the server using pg_ctl command the following message is shown. server starting C:\Program Files\PostgreSQL\8.4\bin2010-09-30 14:36:07 ISTLOG: could not create file postmaster.opts: Permission denied. Are you running pg_ctl from the postgres account? If not, this could be unrelated to the core issue. Typically to attempt manually pg_ctl start you runas a console into the postgres account. If the database starts w/o error from postgres account, my money is on user/password issue which is endless headache on windows pg servers (at least, they were for me when I was on windows). merlin -- 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] [9.0] On temporary tables
you can pass in/out very large set of data inside a transaction by using temp tables. Temporary tables are one of the greatest features of SQL dbs. Here's one fact, it most often takes as long to transfer data from/to a query/function as it takes to execute it. By storing data on the server side, you shave that cost off. This is something most people don't think about when dealing with large sets of data. -- 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] [9.0] On temporary tables
2010/9/30 Andy Colson a...@squeakycode.net: On 9/30/2010 8:52 AM, Vincenzo Romano wrote: I was also thinking about using the catalog, but it looked to me easier my way. And, of course, if you have better advises for a session variables solution, my ears are open. More of a question: why use temp tables at all? What does that offer that a single, regular, session table does not? In my mind it can be a good and simple solution when I have more than 1 user needing a set of personal variables for each connection/session. Better advises are welcome, of course. -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- 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] [9.0] On temporary tables
Hello 2010/9/30 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/9/30 Pavel Stehule pavel.steh...@gmail.com: Hello 2010/9/30 Tom Lane t...@sss.pgh.pa.us: Vincenzo Romano vincenzo.rom...@notorand.it writes: create or replace function session_init() returns void language plpgsql as $body$ declare t text; begin select valu into t from session where name='SESSION_ID'; if not found then create temporary table session ( like public.session including all ); insert into session values ( 'SESSION_ID',current_user ); end if; end; $body$; The idea is to create a temporary table to store session variables only of there's no temporary table with that name. That isn't going to work tremendously well. plpgsql will cache a plan for that SELECT on first use, and creation of the temp table is not an event that will cause replanning of a select that doesn't already use the temp table. I found a little bit faster solution a catching a exception. http://okbob.blogspot.com/2008/11/plpgsql-and-temp-tables.html Hmmm ... do you think the performance would be the same in v9? yes I think but if you need a session variables, then you can use a plperl http://www.postgresql.org/docs/9.0/static/plperl-global.html I will look into this. What I need is a set of variable for each connection. understand - attention - session variables are nice but problematic when you use some form of connection pooling Regards Pavel Stehule Anyway, I'm quite puzzled by the fact that an EXCEPTION WHEN can be faster than an IF .. THEN .. ELSE with a rather simple test. Unless the pg_table_is_visible() is really bad. can be - the reason is relative simple - exception is raised by planner - so this doesn't do any real query - and lot of data for planner are in cache. Pavel Thanks a lot. -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- 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] [9.0] On temporary tables
2010/9/30 Pavel Stehule pavel.steh...@gmail.com: Hello but if you need a session variables, then you can use a plperl http://www.postgresql.org/docs/9.0/static/plperl-global.html I will look into this. What I need is a set of variable for each connection. understand - attention - session variables are nice but problematic when you use some form of connection pooling I do know I'll need to be careful, even without connection pooling. What'd be a different solution to implement session variables? Just PLPERL? -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Working around in-statement with temporary tables
Hello,
I'm using phpBB 3.0.7-PL1 with postgresql-server-8.1.21-1.el5_5.1 with
CentOS Linux 5.5, both under 32-bit (dev. VM) and 64-bit (prod. server)
One of the phpBB sub-forums grows quickly every day and I have problems
cleaning old messages there, because its phpBB's admin-script bails out with:
SQL ERROR [ postgres ]
ERROR: stack depth limit exceeded HINT: Increase the configuration
parameter max_stack_depth. []
SQL
DELETE FROM phpbb_posts WHERE post_id IN (334767, skipped.., 382871)
I've read on max_stack_depth and it seems that changing it is a bad idea.
So I would like to write own SQL-function and run it as a cronjob.
There are 2 tables from which I should delete the too old records:
# \d phpbb_topics
Table public.phpbb_topics
Column | Type |
Modifiers
---++
topic_id | integer| not null default
nextval('phpbb_topics_seq'::regclass)
forum_id | integer| not null default 0
topic_poster | integer| not null default 0
topic_time| integer| not null default 0
.
# \d phpbb_posts
Table public.phpbb_posts
Column | Type | Modifiers
--++---
post_id | integer| not null default
nextval('phpbb_posts_seq'::regclass)
topic_id | integer| not null default 0
forum_id | integer| not null default 0
poster_id| integer| not null default 0
post_time| integer| not null default 0
.
Also I should update the total post and topics numbers in the
phpbb_config and phpbb_users.
I can select the too old topics with:
select topic_id from phpbb_topics
where forum_id=5 and topic_poster=1 and
age(to_timestamp(topic_time))interval '14 days'
this works ok and now I'd like to put the results into a temp. table
old_topic_ids (how?) and then I'm going to create another temp.table
for the old_post_ids and put the results of the following into it:
select post_id from phpbb_posts p, old_topics_id o
where p.forum_id=5 and p.poster_id=1 and p.topic_id=o.topic_id;
Then I'm going to update the stats (will the following work?):
update phpbb_config set config_value = (select count(*) from old_topic_ids)
where config_name = 'num_topics';
update phpbb_config set config_value = (select count(*) from old_post_ids)
where config_name = 'num_posts';
Then I'm going to delete the records:
delete from phpbb_topics where (... I don't know how to workarund the
in-statement here -
I'd like to delete all topic_id's which are contained in the temp.
table old_topic_ids).
delete from phpbb_posts where (... I don't know how to workarund the
in-statement here -
I'd like to delete all post_id's which are contained in the temp.
table old_post_ids).
And finally I update 1 more stat:
update phpbb_users set user_posts =
(select count(*) from phpbb_posts where user_id=1) where user_id = 1;
Could you please help me with my SQL statements above,
my SQL knowledge is very rusty. And I wonder how to create the temporary
tables for the old ids and if I have to clean up them later or
if they will be removed automatically when session closes
or my SQL function returns?
And finally, if I put everything into an SQL function:
create or replace function remove_old(num_days varchar) as $$
.
$$ language sql;
then how can I use the num_days argument in my statement
where and age(to_timestamp(topic_time))interval '14 days'
?
Thank you
Alex
--
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] [9.0] On temporary tables
2010/9/30 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/9/30 Pavel Stehule pavel.steh...@gmail.com: Hello but if you need a session variables, then you can use a plperl http://www.postgresql.org/docs/9.0/static/plperl-global.html I will look into this. What I need is a set of variable for each connection. understand - attention - session variables are nice but problematic when you use some form of connection pooling I do know I'll need to be careful, even without connection pooling. What'd be a different solution to implement session variables? Just PLPERL? plperl or C or custom guc .. the using plperl is probably most simple and fast Pavel http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Any_other_session_variables I don't know what do you do - but some times better is don't use a session variables on server - just use a application variables together with stored procedures. What I remember my last bigger project, we did a some problems with session variables from PHP, where PHP sessions sometimes recycled a db session, so probably better to don't use it. Pavel -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- 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] [9.0] On temporary tables
2010/9/30 Pavel Stehule pavel.steh...@gmail.com: 2010/9/30 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/9/30 Pavel Stehule pavel.steh...@gmail.com: Hello but if you need a session variables, then you can use a plperl http://www.postgresql.org/docs/9.0/static/plperl-global.html I will look into this. What I need is a set of variable for each connection. understand - attention - session variables are nice but problematic when you use some form of connection pooling I do know I'll need to be careful, even without connection pooling. What'd be a different solution to implement session variables? Just PLPERL? plperl or C or custom guc .. the using plperl is probably most simple and fast Pavel http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Any_other_session_variables I don't know what do you do - but some times better is don't use a session variables on server - just use a application variables together with stored procedures. What I remember my last bigger project, we did a some problems with session variables from PHP, where PHP sessions sometimes recycled a db session, so probably better to don't use it. Pavel A few of those session variables are needed by functions and views to change their output (and behavior). For example, one session variable is the reference time I use to select into history tables. A user can be using a reference time in the future (to see how projectons are), while another one is using the current_timestamp to work on current data. I don't see any better way than temp tables. So far. -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- 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] Prepared statements and unknown types
On 30 September 2010 14:36, Igor Neyman iney...@perceptron.com wrote: -Original Message- From: Thom Brown [mailto:t...@linux.com] Sent: Wednesday, September 29, 2010 2:08 PM To: PGSQL Mailing List Subject: Prepared statements and unknown types Could someone explain why the following doesn't work? test=# PREPARE meow(unknown) AS test-# SELECT $1 as meow; ERROR: could not determine data type of parameter $1 The problem is that using PDO in PHP, prepared statements aren't possible if values are used instead of columns in the select list. This appears to be allowed for MySQL and SQL Server. Sorry, for being late to this conversation. Possibly it works for SQL Server, because SS has SQL_VARIANT data type (kind of anytype). Thanks for the info Igor. It's not really going to be a big issue, just something we'll have to bear in mind. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] [9.0] On temporary tables
2010/9/30 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/9/30 Pavel Stehule pavel.steh...@gmail.com: 2010/9/30 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/9/30 Pavel Stehule pavel.steh...@gmail.com: Hello but if you need a session variables, then you can use a plperl http://www.postgresql.org/docs/9.0/static/plperl-global.html I will look into this. What I need is a set of variable for each connection. understand - attention - session variables are nice but problematic when you use some form of connection pooling I do know I'll need to be careful, even without connection pooling. What'd be a different solution to implement session variables? Just PLPERL? plperl or C or custom guc .. the using plperl is probably most simple and fast Pavel http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Any_other_session_variables I don't know what do you do - but some times better is don't use a session variables on server - just use a application variables together with stored procedures. What I remember my last bigger project, we did a some problems with session variables from PHP, where PHP sessions sometimes recycled a db session, so probably better to don't use it. Pavel A few of those session variables are needed by functions and views to change their output (and behavior). For example, one session variable is the reference time I use to select into history tables. A user can be using a reference time in the future (to see how projectons are), while another one is using the current_timestamp to work on current data. I don't see any better way than temp tables. So far. we did same design - it works well from single client application and not too much well from some application servers. Our solution was to use a special parameter for every function - like some session handle - and without session depending views we used a parametrized SRF functions with handle parameter. But this project was started about 2005, so now a situation can be different. Regards Pavel -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] libpq (C++) - Insert binary data
Hi there, I'm sitting for days now, and I can't get this to work: I want to insert binary data (bytea) into my postgres DB via the c++ libpq. What I have is a char* (actually a QByteArray) and I want to insert it into the DB and the retrieve it from there again. I can't find any good examples or tutorials online, so can anyone help me (or direct met to a tutorial) on how to insert binary data and retrieve it again? Thank you very much Christoph -- 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] libpq (C++) - Insert binary data
2010/9/30 GOO Creations goocreati...@gmail.com: Hi there, I'm sitting for days now, and I can't get this to work: I want to insert binary data (bytea) into my postgres DB via the c++ libpq. What I have is a char* (actually a QByteArray) and I want to insert it into the DB and the retrieve it from there again. I can't find any good examples or tutorials online, so can anyone help me (or direct met to a tutorial) on how to insert binary data and retrieve it again? Thank you very much Christoph Try chapter 31.3.4 from the v9 documentation. http://www.postgresql.org/docs/9.0/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-STRING -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- 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] libpq (C++) - Insert binary data
If you use QT, it has PG connector classes I believe (it had in 3.x). -- 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] libpq (C++) - Insert binary data
2010/9/30 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/9/30 GOO Creations goocreati...@gmail.com: Hi there, I'm sitting for days now, and I can't get this to work: I want to insert binary data (bytea) into my postgres DB via the c++ libpq. What I have is a char* (actually a QByteArray) and I want to insert it into the DB and the retrieve it from there again. I can't find any good examples or tutorials online, so can anyone help me (or direct met to a tutorial) on how to insert binary data and retrieve it again? Thank you very much Christoph Try chapter 31.3.4 from the v9 documentation. http://www.postgresql.org/docs/9.0/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-STRING Sorry, I meant 30.3.5 in v8: http://www.postgresql.org/docs/8.4/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-BYTEA It looks like there's no such a thing in v9, though! -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- 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] libpq (C++) - Insert binary data
I've worked through that already, without any success.
This is what I have until now to insert data
char *query = insert into table1 (bytes) values ($1);
QByteArray chip = assignment of bytes;
const char *data = chip-data();
const char* params[]={data};
const int params_length[]={chip-length()};
const int params_format[]={1};
result = PQexecParams(mDatabase, query, 1, in_oid, params,
params_length, params_format, 0);
The first problem I have is that I'm not sure if const int
params_length[]={chip-length()}; is the correct way to provide the
length. Second of all, is this actually the correct way of doing it,
isn't there a beter way?
Christoph
On 2010/09/30 05:30 PM, Vincenzo Romano wrote:
2010/9/30 GOO Creationsgoocreati...@gmail.com:
Hi there,
I'm sitting for days now, and I can't get this to work:
I want to insert binary data (bytea) into my postgres DB via the c++ libpq.
What I have is a char* (actually a QByteArray) and I want to insert it into
the DB and the retrieve it from there again.
I can't find any good examples or tutorials online, so can anyone help me
(or direct met to a tutorial) on how to insert binary data and retrieve it
again?
Thank you very much
Christoph
Try chapter 31.3.4 from the v9 documentation.
http://www.postgresql.org/docs/9.0/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-STRING
--
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] libpq (C++) - Insert binary data
On Sep 30, 2010, at 8:26 AM, GOO Creations wrote: Hi there, I'm sitting for days now, and I can't get this to work: I want to insert binary data (bytea) into my postgres DB via the c++ libpq. What I have is a char* (actually a QByteArray) and I want to insert it into the DB and the retrieve it from there again. I can't find any good examples or tutorials online, so can anyone help me (or direct met to a tutorial) on how to insert binary data and retrieve it again? Is there any reason you're not using the Qt database API? It'll map onto what you're doing much more easily than using libpq. (Also, libpq isn't a C++ API, so maybe you're thinking of something else, like libpqxx?) Cheers, Steve -- 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] libpq (C++) - Insert binary data
Yes there is a reason I'm not using Qt's libraries. Qt doesn't come out with PSQL as default driver (meaning you have to manually download the driver for Qt postgres). I'm developing a plugin for an app that restricts Qt, no extra depedncies are allowed. But the app has libpq as dependcy, so I'm able to use that. Chris On 2010/09/30 05:38 PM, Steve Atkins wrote: On Sep 30, 2010, at 8:26 AM, GOO Creations wrote: Hi there, I'm sitting for days now, and I can't get this to work: I want to insert binary data (bytea) into my postgres DB via the c++ libpq. What I have is a char* (actually a QByteArray) and I want to insert it into the DB and the retrieve it from there again. I can't find any good examples or tutorials online, so can anyone help me (or direct met to a tutorial) on how to insert binary data and retrieve it again? Is there any reason you're not using the Qt database API? It'll map onto what you're doing much more easily than using libpq. (Also, libpq isn't a C++ API, so maybe you're thinking of something else, like libpqxx?) Cheers, Steve -- 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] Get next OID
On Sep 29, 2010, at 7:56 PM, Tom Lane wrote: pg_controldata would tell you approximately where the OID counter is. Just what I needed. Thank you so much Tom. Dianne -- 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] [9.0] On temporary tables
--- On Thu, 9/30/10, Vincenzo Romano vincenzo.rom...@notorand.it wrote: From: Vincenzo Romano vincenzo.rom...@notorand.it Subject: [GENERAL] [9.0] On temporary tables To: PostgreSQL General pgsql-general@postgresql.org Date: Thursday, September 30, 2010, 11:09 AM Hi all. This is my case: -- begin snippet -- reset search_path; drop table if exists session cascade; create table session ( name text primary key, valu text not null ); create or replace function session_init() returns void language plpgsql as $body$ declare t text; begin select valu into t from session where name='SESSION_ID'; if not found then create temporary table session ( like public.session including all ); insert into session values ( 'SESSION_ID',current_user ); end if; end; $body$; SELECT * from session; SELECT * from session_init(); SELECT * from session; SELECT * from session_init(); -- end snippet -- The output from the last four queries is: -- tmp2=# SELECT * from session; name | valu --+-- (0 rows) tmp2=# SELECT * from session_init(); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index session_pkey for table session CONTEXT: SQL statement create temporary table session ( like public.session including all ) PL/pgSQL function session_init line 6 at istruzione SQL session_init -- (1 row) tmp2=# SELECT * from session; name | valu +-- SESSION_ID | enzo (1 row) tmp2=# SELECT * from session_init(); ERROR: relation session already exists CONTEXT: SQL statement create temporary table session ( like public.session including all ) PL/pgSQL function session_init line 6 at istruzione SQL -- This means that the if not found then in the function body didn't work well. The idea is to create a temporary table to store session variables only of there's no temporary table with that name. Any hint on this? -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general check if the temp_table alredy exist select 1 from pg_class where relname = 'prueba3'
[GENERAL] ossp-uuid missing from one click windows x64 builds
I just installed a copy of PostgreSQL 9.0 on Windows 7 x64 using the one click installer, and found that the uuid-ossp contrib module is missing. I took a look at the zip archives, and the uuid-ossp files are in the 32 bit build, but not the 64 bit build. Is this an oversight, or is there some problem with the library on Windows x64?
Re: [GENERAL] ossp-uuid missing from one click windows x64 builds
On Thu, Sep 30, 2010 at 5:57 PM, Cheetah fast...@gmail.com wrote: I just installed a copy of PostgreSQL 9.0 on Windows 7 x64 using the one click installer, and found that the uuid-ossp contrib module is missing. I took a look at the zip archives, and the uuid-ossp files are in the 32 bit build, but not the 64 bit build. Is this an oversight, or is there some problem with the library on Windows x64? The underlying uuid-ossp library isn't supported on Win64, so we can't build the contrib module unfortunately. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PgWest 2010 talk descriptions are up
Hey, Just an FYI -- the talk descriptions for PostgreSQL Conference West are now up: https://www.postgresqlconference.org/2010/west/talks JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] where does postgres keep the query result until it is returned?
One thing that i think works is to create a table from your results. That is, if your result is not one big chunk in one row that won't fit in memory, but lots of rows. like this: create table foo as select humongous query If you use screen (on linux) you can log out while your humongous query runs on the server. Cheers, WBL -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] custom analyze function
Are there any examples of a custom analyze function for a table? Thanks in advance, Mike Norman
Re: [GENERAL] Missing path in pg_config
It sounds like I'm in quite a fix here. If PGXS is currently a no-go in Windows, then that renders the temporal extension incompatible with Windows since it uses PGXS to install... OTOH, if there's some remote possibility of some workaround solution for Windows to get PGXS and/or the temporal extension installed, I'd be grateful if someone could help me along with it (further to your below suggestions, since I'm floundering around in unfamiliar territory here). -Original Message- From: Magnus Hagander [mailto:mag...@hagander.net] Sent: Thursday, September 30, 2010 4:35 AM To: Dave Page Cc: Turner, John J; pgsql-general@postgresql.org Subject: Re: [GENERAL] Missing path in pg_config On Thu, Sep 30, 2010 at 00:55, Dave Page dp...@pgadmin.org wrote: On Wed, Sep 29, 2010 at 7:23 PM, Turner, John J jjtur...@statestreet.com wrote: Hello, I've installed the PostgreSQL 9.0 binary package for Windows XP 32-bit and I'm trying to install the temporal extension module available in pg_Foundry. One problem I can see that I'm running into is that the path defined for PGXS in pg_config does not exist: PGXS = c:/program files/PostgreSQL/9.0/lib/pgxs/src/makefiles/pgxs.mk I have a path up to the .../lib but nothing after that. Do I need to re-install something or run a script to flesh things out? It seems when I follow the instructions for the temporal module install (as found here), it bombs out when it tries to find this path while running make install PGXS doesn't work with the VC++ build we use for Windows. I wonder if that output from pg_config should be #ifdef'd out until such time as we can figure out a way to make it work - if that's even possible. I don't know pgxs internals well enough, but would it work if we just shipped the pgxs file - for users of mingw? I have a feeling it won't - the thing uses files generated by the postgresql ./configure-script, doesn't it? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] PostgreSQL 9.0 (x86-64) and Windows 7 (x86-64) - Unable to install
Dave Page dp...@pgadmin.org writes: On Thu, Sep 30, 2010 at 1:42 PM, Dr. Peter Voigt pvo...@uos.de wrote: Hi Dharmendra, thanks for your reply. This kind of errors, which cannot be reproduced on other machines are bad and leave no chance for developers to solve them. Unfortunately the installer does not leave any log files. Please look for any logfiles in %TEMP% starting with bitrock. Well, the installer does not leave any file starting with bitrock in my %TEMP% directory. I am using the default %TEMP% value as created during system installation. Its value is (German operating system) C:\Users\Administrator\Lokale Einstellungen\Temp or in 8.3 notation C:\Users\ADMINI~1\LOKALE~1\Temp. The subdir Lokale Einstellungen is a link to C:\Users\Administrator\AppData\Local: administra...@tiger2008:C:\Users\Administrator dir /a |grep -i lokale 28.08.2010 15:22VERBINDUNG Lokale Einstellungen [C:\Users\Administrator \AppData\Local] However, a scan of my whole system partition reveales a file bitrock.log under C:\Users\Administrator\AppData\Local. I have just re-created it with a fresh installation attempt. Please find it attached. I have tried to interpret the error in the log. The installer complains about not finding file C:\Users\Administrator\Lokale Einstellungen\postgresql_installer\installruntimes.vbs. I suppose it is important for you to know that this file installruntimes.vbs is present - but under C:\Users\Administrator\AppData\Local. This is the same directory where I finally found the log. Log started 09/30/10 at 19:42:57 Preferred installation mode : qt Trying to init installer in mode qt Mode qt successfully initialized Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.0 Data Directory. Setting variable iDataDirectory to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.0 Base Directory. Setting variable iBaseDirectory to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.0 Service ID. Setting variable iServiceName to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.0 Service Account. Setting variable iServiceAccount to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.0 Super User. Setting variable iSuperuser to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.0 Branding. Setting variable iBranding to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.0 Version. Setting variable brandingVer to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.0 Shortcuts. Setting variable iShortcut to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.0 DisableStackBuilder. Setting variable iDisableStackBuilder to empty value [19:43:01] Existing base directory: [19:43:01] Existing data directory: [19:43:01] Using branding: PostgreSQL 9.0 [19:43:01] Using Super User: postgres and Service Account: postgres [19:43:01] Using Service Name: postgresql-x64-9.0 Executing cscript //NoLogo "C:\Users\Administrator\Lokale Einstellungen\postgresql_installer\installruntimes.vbs" "C:\Users\Administrator\Lokale Einstellungen\postgresql_installer\vcredist_x64.exe" Script exit code: 1 Script output: Eingabefehler: Die Skriptdatei "C:\Users\Administrator\Lokale Einstellungen\postgresql_installer\installruntimes.vbs" wurde nicht gefunden. Script stderr: Program ended with an error exit code Error running cscript //NoLogo "C:\Users\Administrator\Lokale Einstellungen\postgresql_installer\installruntimes.vbs" "C:\Users\Administrator\Lokale Einstellungen\postgresql_installer\vcredist_x64.exe" : Program ended with an error exit code -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Hosting options?
Looking for a good host for a project on Win2008 and PostgreSQL. I found these 2 sites but they support older versions of PostgreSQL http://www.jodohost.com/http://www.jodohost.com/windows.asp http://www.gbehost.com/ Does anyone have any good suggestions? Donny Velazquez | Civil Solutions, a division of ARH | Lead Software Developer 850 S. White Horse Pike, Hammonton, NJ 08037 ': 800.924.0482 ext 215| Fax: 609.704.8011 * dv...@arh-us.commailto:dv...@arh-us.com www.civilsolutions.bizhttp://www.civilsolutions.biz/
Re: [GENERAL] custom analyze function
Michael Norman mwnor...@gmail.com writes: Are there any examples of a custom analyze function for a table? Um ... there's no such thing as a per-table custom analyze function. Analyze functions are per-data-type. tsvector has a custom analyze function in 8.4 and up (ts_typanalyze); you could look at that. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] No Relations Found Error
I created today a new database and user for my Wiki. Now I created both the db user and actual db as 'carlos' even though the owner of the db 'wiki' is a user named 'wiki'. My user 'carlos' is a superuser however. So I then wanted to view and change some data but I keep getting this error: car...@wiki]:~$ psql -d wiki psql (8.4.4) Type help for help. wiki=# \d No relations found. Anyone know what I am doing wrong? -- 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] No Relations Found Error
On Thu, Sep 30, 2010 at 3:31 PM, Raymond O'Donnell r...@iol.ie wrote: Are the tables in a schema other than public? Enter \dn at the psql prompt to see what schemas are there. Perhaps not. I wasn't aware of that. When I created the database before I created and tables, I used the following command: createlang plpgsql wiki Could that be why I don't see them? wiki=# \dn List of schemas Name| Owner +-- information_schema | postgres mediawiki | wiki pg_catalog | postgres pg_toast | postgres pg_toast_temp_1| postgres public | postgres (6 rows) -- 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] No Relations Found Error
On 30/09/2010 20:33, Carlos Mennens wrote: On Thu, Sep 30, 2010 at 3:31 PM, Raymond O'Donnellr...@iol.ie wrote: Are the tables in a schema other than public? Enter \dn at the psql prompt to see what schemas are there. Perhaps not. I wasn't aware of that. When I created the database before I created and tables, I used the following command: createlang plpgsql wiki Could that be why I don't see them? No, not at all - that just installs the pl/pgsql language in the database. However wiki=# \dn List of schemas Name| Owner +-- information_schema | postgres mediawiki | wiki ...it looks as if MediaWiki has created a schema for itself during the installation procedure, and that's where the tables and stuff are. If you're not familiar with schemas, here's the section of the docs you need to read: http://www.postgresql.org/docs/9.0/static/ddl-schemas.html Have a look at section 5.3.7 on the search path in particular, as this will make your life much easier when using psql. :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] No Relations Found Error
On 30/09/2010 20:43, Raymond O'Donnell wrote: http://www.postgresql.org/docs/9.0/static/ddl-schemas.html Have a look at section 5.3.7 on the search path in particular, as this Whoops, sorry - that's 5.7.3. Ray.. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] No Relations Found Error
On Thu, Sep 30, 2010 at 3:44 PM, Raymond O'Donnell r...@iol.ie wrote: On 30/09/2010 20:43, Raymond O'Donnell wrote: http://www.postgresql.org/docs/9.0/static/ddl-schemas.html Have a look at section 5.3.7 on the search path in particular, as this Whoops, sorry - that's 5.7.3. Thank you. I am reading the schema section you provided and it gives a dry sense of what it does however I don't understand who I can access the tables for the schema 'mediawiki'? wiki=# \dn mediawiki List of schemas Name| Owner ---+--- mediawiki | wiki (1 row) I can see the schema name and owner but what if I want to look inside? Normally I would use the \d to view all the table info in the connected database but now I can't do this in my 'wiki' database for whatever reason. Perhaps because it was not created in the 'public' schema rather than it's own custom schema. wiki=# \c webmail psql (8.4.4) You are now connected to database webmail. webmail=# \d List of relations Schema |Name | Type | Owner +-+--+- public | cache | table| webmail public | cache_ids | sequence | webmail public | contact_ids | sequence | webmail public | contactgroupmembers | table| webmail public | contactgroups | table| webmail public | contactgroups_ids | sequence | webmail public | contacts| table| webmail public | identities | table| webmail public | identity_ids| sequence | webmail public | message_ids | sequence | webmail public | messages| table| webmail public | session | table| webmail public | user_ids| sequence | webmail public | users | table| webmail (14 rows) How would I achieve this on my 'wiki' database? -- 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] No Relations Found Error
On 30/09/2010 20:59, Carlos Mennens wrote: On Thu, Sep 30, 2010 at 3:44 PM, Raymond O'Donnellr...@iol.ie wrote: On 30/09/2010 20:43, Raymond O'Donnell wrote: http://www.postgresql.org/docs/9.0/static/ddl-schemas.html Have a look at section 5.3.7 on the search path in particular, as this Whoops, sorry - that's 5.7.3. Thank you. I am reading the schema section you provided and it gives a dry sense of what it does however I don't understand who I can access the tables for the schema 'mediawiki'? You can use a pattern in your \d commands, thus: To see tables: \dt mediawiki.* To see sequences: \ds mediawiki.* etc You can also set the search path - see that bit in the docs. \? is your friend here also. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] [9.0] hot standby plus streaming replication
Ciao Michele, both server (master and standby) need a common directory where read and write the wal files? Not necessarily. You can use for instance scp to ship the WAL file from the master to the standby using the network. Just another question about replication: there is the possibility to build a sync between a 32 and a 64 bit (on linux)? As stated in the documentation (http://www.postgresql.org/docs/9.0/static/warm-standby.html), the hardware architecture must be the same. Hope this helps. Ciao, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- 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] PostgreSQL 9.0 (x86-64) and Windows 7 (x86-64) - Unable to install
On Thu, Sep 30, 2010 at 7:41 PM, Dr. Peter Voigt pvo...@uos.de wrote: Dave Page dp...@pgadmin.org writes: On Thu, Sep 30, 2010 at 1:42 PM, Dr. Peter Voigt pvo...@uos.de wrote: Hi Dharmendra, thanks for your reply. This kind of errors, which cannot be reproduced on other machines are bad and leave no chance for developers to solve them. Unfortunately the installer does not leave any log files. Please look for any logfiles in %TEMP% starting with bitrock. Well, the installer does not leave any file starting with bitrock in my %TEMP% directory. I am using the default %TEMP% value as created during system installation. Its value is (German operating system) C:\Users\Administrator\Lokale Einstellungen\Temp or in 8.3 notation C:\Users\ADMINI~1\LOKALE~1\Temp. The subdir Lokale Einstellungen is a link to C:\Users\Administrator\AppData\Local: administra...@tiger2008:C:\Users\Administrator dir /a |grep -i lokale 28.08.2010 15:22 VERBINDUNG Lokale Einstellungen [C:\Users\Administrator \AppData\Local] However, a scan of my whole system partition reveales a file bitrock.log under C:\Users\Administrator\AppData\Local. I have just re-created it with a fresh installation attempt. Please find it attached. I have tried to interpret the error in the log. The installer complains about not finding file C:\Users\Administrator\Lokale Einstellungen\postgresql_installer\installruntimes.vbs. I suppose it is important for you to know that this file installruntimes.vbs is present - but under C:\Users\Administrator\AppData\Local. This is the same directory where I finally found the log. Thats very odd, but it explains why things are going wrong - essentially, the prerequisites are being unpacked to: C:\Users\Administrator\AppData\Local But the installer expects to find them in: C:\Users\Administrator\Lokale Einstellungen\ Which is a link to the first folder. I (as the guy the wrote the original version of the installer) expect them to be in: C:\Users\Administrator\Lokale Einstellungen\Temp\ So, it sounds like there are two questions for me to figure out - why is the installer not able to follow the link and find the files (which is probably a question for BitRock), and why isn't it using the actual Temp subdirectory as it's supposed to. A couple of questions for you Peter (and thanks for bearing with us while we figure this out): - How are you running the installer? Are you logged in as Administrator, or are you using Run As Administrator or something similar? - What's the output from the SET command when run in the same user environment as the installer (ie. from an Administrator command prompt, or one launched however you've escalated your privileges). -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] PostgreSQL 9.0 (x86-64) and Windows 7 (x86-64) - Unable to install
Dave Page dp...@pgadmin.org writes: A couple of questions for you Peter (and thanks for bearing with us while we figure this out): - How are you running the installer? Are you logged in as Administrator, or are you using Run As Administrator or something similar? I am logged in as Administrator when running the installer. - What's the output from the SET command when run in the same user environment as the installer (ie. from an Administrator command prompt, or one launched however you've escalated your privileges). Please find the environment of user Administrator attached: ACR_BIN=C:\Program Files (x86)\Adobe\Reader 9.0\Reader ALLUSERSPROFILE=C:\ProgramData APACHE2_HOME=C:\Program Files\Apache Group\Apache22 APACHESRC=C:\Programme\Apache Group\Apache22 APPDATA=C:\Users\Administrator\AppData\Roaming asl.log=Destination=file;OnFirstLog=command,environment,parent BIBINPUTS=D:\home\pvoigt\tex\texsty BSTINPUTS=D:\home\pvoigt\tex\texsty CATALINA_HOME=C:\Program Files\tomcat60 CLASSPATH=.;C:\Programme\mysql-connector-java-5.1.10\mysql-connector-java-5.1.10-bin.jar;C:\Programme\sqljdbc_1.1_deu\sqljdbc.jar;C:\Program Files\tomcat60\lib\servlet-api.jar;d:\home\pvoigt\java\vog-libs\VogSystem.jar;d:\home\pvoigt\java\vog-libs\VogIO.jar;d:\home\pvoigt\java\vog-libs\MyIO.jar;d:\home\pvoigt\java\vog-libs\VogDbUtil.jar;d:\home\pvoigt\java\vog-libs\VogTime.jar CommonProgramFiles=C:\Program Files\Common Files CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files CommonProgramW6432=C:\Program Files\Common Files COMPUTERNAME=TIGER2008 ComSpec=C:\Windows\system32\cmd.exe CURL_CA_BUNDLE=D:\temp\ca-bundle.crt EASY_INSTALL_BIN=C:\Programme\Python26\Scripts EDITOR=C:\Program Files\vim\vim73\gvim.exe EMACS_OPTS=-geometry 80x45 --reverse-video FOP_HOME=C:\Program Files\fop FP_NO_HOST_CHECK=NO FRD_BIN=D:\local\bin FTPROOT=E:\srv\ftp GNUPGHOME=D:\home\pvoigt\.gnupg GNUWIN32_BIN=D:\local\GnuWin32\bin GSV_BIN=C:\Program Files\ghostgum\gsview GS_BIN=C:\Program Files\gs\gs8.64\bin GS_LIB=C:\Program Files\gs\gs8.64\lib;C:\Program Files\gs\fonts HEISE=D:\home\pvoigt\ct-ix\inhalt.frm HOME=D:\home\pvoigt HOMEDIR=D:\home\pvoigt\.gnupg HOMEDRIVE=C: HOMEPATH=\Users\Administrator INFODIR=D:\local\Emacs\emacs\info;D:\local\Emacs\emacs\site-info INFOPATH=D:\local\Emacs\emacs\info;D:\local\Emacs\emacs\site-info JAVA_HOME=C:\Program Files\Java\jdk KLEOPATRA_LOGDIR=D:\home\pvoigt\.gnupg\kleopatra LANG=DE LESS=-I -N -M -S LOCALAPPDATA=C:\Users\Administrator\AppData\Local LOCAL_BIN=D:\local\bin LOGONSERVER=\\TIGER2008 LYNX_CFG=C:\Program Files (x86)\Lynx\lynx.cfg LYNX_LSS=C:\Program Files (x86)\Lynx\lynx.lss MIKTEX_BIN=D:\local\MiKTeX\miktex\bin MINGW_HOME=D:\local\MinGW MSYS_HOME=D:\local\msys\1.0 MYSQL_JDBC_HOME=C:\Program Files\mysql-connector-java-5.1.10 NUMBER_OF_PROCESSORS=2 OPENSSL=C:\Program Files (x86)\openssl OPENSSL_CONF=D:\home\pvoigt\certs\ca\my_openssl.config OPENSSL_INC=C:\Program Files (x86)\openssl\include OPENSSL_LIB=C:\Program Files (x86)\openssl\lib OS=Windows_NT Os2LibPath=%Os2LibPath% PAGER=D:/local/gnuwin32/bin/less.exe Path=C:\Program Files\Common Files\Microsoft Shared\Windows Live;C:\Program Files (x86)\PC Connectivity Solution\;C:\Program Files\ImageMagick;D:\local\MiKTeX\miktex\bin;c:\Program Files (x86)\NVIDIA Corporation\PhysX\Common;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\GNU\GnuPG\pub;C:\Program Files (x86)\Common Files\Acronis\SnapAPI\;C:\Program Files (x86)\QuickTime\QTSystem\;C:\Program Files\Common Files\Microsoft Shared\Windows Live;D:\local\tex4ht\bin\win32;D:\local\tex4ht\bin\ht\win32;C:\Program Files (x86)\openssl\bin;C:\Programme\gnutls\bin;d:\local\bin;d:\local\gnuwin32\bin;D:\home\pvoigt\ct-ix;C:\Program Files (x86)\Emacs\emacs\bin;C:\Program Files (x86)\GNU\GnuPG;C:\Program Files\Java\jdk\bin;C:\Program Files\Python27;C:\Program Files\Python27\Scripts;C:\Program Files\vim\vim73;C:\Program Files\perl\bin;C:\Program Files (x86)\lynx;C:\Program Files (x86)\Adobe\Reader 9.0\Reader;C:\Program Files (x86)\php;D:\home\pvoigt\python;C:\Program Files\ghostgum\gsview;C:\Program Files\MySQL\MySQL Server 5.1\bin;C:\Program Files\7-Zip;C:\Programme\Python26\Scripts;C:\Program Files (x86)\wget;C:\Program Files (x86)\curl;C:\Program Files\curl;C:\Program Files\ant\bin;C:\Program Files (x86)\NTP\bin;C:\Program Files (x86)\Mozilla Firefox PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC PROCESSOR_ARCHITECTURE=AMD64 PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 15 Stepping 11, GenuineIntel PROCESSOR_LEVEL=6 PROCESSOR_REVISION=0f0b ProgramData=C:\ProgramData ProgramFiles=C:\Program Files ProgramFiles(x86)=C:\Program Files (x86) ProgramW6432=C:\Program Files prompt=administra...@tiger2008:$p$g PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\ PUBLIC=C:\Users\Public PYTHONPATH=D:\home\pvoigt\python PYTHONSRC=C:\Program Files\Python26 PYTHONSTARTUP=D:\home\pvoigt\python_startup.py
Re: [GENERAL] libpq (C++) - Insert binary data
On Thu, Sep 30, 2010 at 11:42 AM, GOO Creations goocreati...@gmail.com wrote:
Yes there is a reason I'm not using Qt's libraries. Qt doesn't come out
with PSQL as default driver (meaning you have to manually download the
driver for Qt postgres). I'm developing a plugin for an app that restricts
Qt, no extra depedncies are allowed. But the app has libpq as dependcy, so
I'm able to use that.
here is my response from the previous time I answered the question.
There a number of ways to deal with this (my favorite by far is
libpqtypes, but I'm quite biased!):
On Wed, Jul 21, 2010 at 9:27 AM, Merlin Moncure mmonc...@gmail.com wrote:
On Mon, Jul 19, 2010 at 8:14 PM, vinicius_bra vinicius...@yahoo.com.br
wrote:
Hi All,
I'm developing a system in C and I have a unsigned char pointer that
represents a struct and I like to store it in a bytea column in postgreSQL.
How can I do it?
Example:
you have several options:
*) encode the memory for the structure PQescapeStringConn and send to
PQexec (my least favorite method)
*) set up a call to PQexecParams (more work, but faster and no escaping)
*) make a composite type on the server and send your structure in a
more classical SQL way
*) use libpqtypes (this is the most set up work, but the best in the
long run) :-)
w/libpqtypes:
PGbytea b = {sizeof(s), s};
PGresult *res = PQexecf(conn, insert into t values (%bytea), b);
PQclear(res);
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Kudos on the 64 bit PostgreSQL for Windows
I installed the 64 bit PostgreSQL 9.0 on Windows Server 2008 and it seems to perform wonderfully. I moved 1.5 million records that are 3K wide from SQL*Server into PostgreSQL and rebuilt 6 indexes all in less than 6 minutes. I was thinking of using SQLite for the project, but I calculate the same data movement would have taken about one month! The memory footprint is also very nice for the amount of performance delivered. -- 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] Hosting options?
On Thu, Sep 30, 2010 at 11:53 AM, Donny Velazquez dv...@arh-us.com wrote: Looking for a good host for a project on Win2008 and PostgreSQL. Does anyone have any good suggestions? IIRC, hub.org is owned by one of the pg core team members: http://hub.org/services/database-services -- 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] libpq (C++) - Insert binary data
* GOO Creations wrote:
This is what I have until now to insert data
char *query = insert into table1 (bytes) values ($1);
QByteArray chip = assignment of bytes;
const char *data = chip-data();
const char* params[]={data};
const int params_length[]={chip-length()};
const int params_format[]={1};
result = PQexecParams(mDatabase, query, 1, in_oid, params,
params_length, params_format, 0);
The first problem I have is that I'm not sure if const int
params_length[]={chip-length()}; is the correct way to provide the
length. Second of all, is this actually the correct way of doing it,
isn't there a beter way?
It is certainly the simplest way of doing it in plain libpq, as long as
you're using the binary format (which you are doing here). According to
the documentation, QByteArray::length() returns the number of bytes in
the array, so it is the correct size.
I'm not sure if the (internal) binary format of bytea is guaranteed not
to change in future versions of PostgreSQL. Currently, as you obviously
found out yourself, it's pretty simple -- no transformation at all, the
binary format is just the data.
To make sure that your application supports any changes in future
versions, you should consider using the hex text format instead. See
section 8.4.1 of the manual for version 9.0.
--
Christian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
