Re: [GENERAL] select sql slow inside function
Hi, On 15 September 2010 01:56, Gary Fu g...@sigmaspace.com wrote: I have a function proc_TaskComplete that inserts a record to table TaskHistory and then calls another function proc_ExportTaskComplete, that will retrieve (select) the record just inserted based on an index column (TaskId) in that table TaskHistory. I noticed that the select sql (inside proc_ExportTaskComplete) will take 3 seconds. Under normal condition (psql) the select sql is fast enough with the index. Can anyone explain why and how to fix the problem ? My postgresql version is 8.4.4 Could you please provide a use-case? Thanks, Gary -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Force ARE in regexp string
Hi! I'm trying to do an advanced regexp match but postgres doesn't seem to let me. I've set regex_flavor to ARE, and I've tried prefixing my strings, i.e. ~* E'***:abc' but for some reason postgres treats all my regexps as BRE's. Common newbie gotchas? I'm trying it directly from psql.exe btw, running postgres 8.4 on a professionally set up Linux machine, and I've also tried it locally on a Windows Bitnami machine with the same results. Thank /J -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] configure: error: header file libxml/parser.h is required for XML support
Hi there, I try to configure postgres: /configure --prefix=/usr/local/postgresql-8.4.4/ \ --without-docdir \ --with-openssl \ --with-libxml \ --with-libxslt \ --with-libraries=/usr/local/openssl:/lib:/lib64:/usr/local/readline/lib:/usr/local/zlib/lib:/usr/local/openssl/lib64:/usr/local/libxml2/lib:/usr/local/libxslt/lib \ --with-includes=/usr/local/readline/include:/usr/local/zlib/include:/usr/local/openssl/include: */usr/local/libxml2/include/libxml2/libxml*:/usr/local/libxslt/include Now even though parser.h can be found in the includes path it claims: configure: error: header file libxml/parser.h is required for XML support Any idea ? Thanks Peter
Re: [GENERAL] configure: error: header file libxml/parser.h is required for XML support
On Wed, Sep 15, 2010 at 10:57 AM, Peter Roethlisberger peter.roethlisber...@gmail.com wrote: Hi there, I try to configure postgres: /configure --prefix=/usr/local/postgresql-8.4.4/ \ --without-docdir \ --with-openssl \ --with-libxml \ --with-libxslt \ --with-libraries=/usr/local/openssl:/lib:/lib64:/usr/local/readline/lib:/usr/local/zlib/lib:/usr/local/openssl/lib64:/usr/local/libxml2/lib:/usr/local/libxslt/lib \ --with-includes=/usr/local/readline/include:/usr/local/zlib/include:/usr/local/openssl/include:/usr/local/libxml2/include/libxml2/libxml:/usr/local/libxslt/include Now even though parser.h can be found in the includes path it claims: configure: error: header file libxml/parser.h is required for XML support Your include path contains /usr/local/libxml2/include/libxml2/libxml, so Postgres is probably looking for /usr/local/libxml2/include/libxml2/libxml/libxml/parser.h, which I suspect is wrong. Try removing the extra /libxml from the include path. -- 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] configure: error: header file libxml/parser.h is required for XML support
Hey Dave, Thanks for the quick reply. The path is correct: # ls -l /usr/local/libxml2/include/libxml2/libxml/parser.h -rw-r--r-- 1 root root 39556 Sep 15 10:07 /usr/local/libxml2/include/libxml2/libxml/parser.h I first used /usr/local/libxml2/include/ which did not work. Doing a find for parser.h made me change the include path. Could possibly the version of libxml2 be an issue: 2-2.7.7 ??? Thx: Peter On Wed, Sep 15, 2010 at 12:18 PM, Dave Page dp...@pgadmin.org wrote: On Wed, Sep 15, 2010 at 10:57 AM, Peter Roethlisberger peter.roethlisber...@gmail.com wrote: Hi there, I try to configure postgres: /configure --prefix=/usr/local/postgresql-8.4.4/ \ --without-docdir \ --with-openssl \ --with-libxml \ --with-libxslt \ --with-libraries=/usr/local/openssl:/lib:/lib64:/usr/local/readline/lib:/usr/local/zlib/lib:/usr/local/openssl/lib64:/usr/local/libxml2/lib:/usr/local/libxslt/lib \ --with-includes=/usr/local/readline/include:/usr/local/zlib/include:/usr/local/openssl/include:/usr/local/libxml2/include/libxml2/libxml:/usr/local/libxslt/include Now even though parser.h can be found in the includes path it claims: configure: error: header file libxml/parser.h is required for XML support Your include path contains /usr/local/libxml2/include/libxml2/libxml, so Postgres is probably looking for /usr/local/libxml2/include/libxml2/libxml/libxml/parser.h, which I suspect is wrong. Try removing the extra /libxml from the include path. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- ___ Peter Roethlisberger Suldhaltenstrasse 4 3703 Aeschi Switzerland 46°39'14 N 7°41'47 E Mobile: +41 (0)79 785 79 35 Fix: +41 (0)33 654 04 39 Skype:PeterRoethlisberger peter.roethlisber...@gmail.com
[GENERAL] Monitoring object usage?
Hi, I'm trying to get a grip on a new body of code and a Postgres database that I'm working with. In particular I've been tasked with cleaning up the database. Is there a way in Postgres to enable monitoring on tables and columns to determine what's not actively being used? This would then form input for a more thorough check into the code base to see what's really not being used. Thanks cheers, Colin
Re: [GENERAL] configure: error: header file libxml/parser.h is required for XML support
Hi Peter On Wed, Sep 15, 2010 at 12:46 PM, Peter Roethlisberger peter.roethlisber...@gmail.com wrote: Hey Dave, Thanks for the quick reply. The path is correct: # ls -l /usr/local/libxml2/include/libxml2/libxml/parser.h -rw-r--r-- 1 root root 39556 Sep 15 10:07 /usr/local/libxml2/include/libxml2/libxml/parser.h But the path you have is: /usr/local/libxml2/include/libxml2/libxml and the included file is libxml/parser.h Put those together and you get: /usr/local/libxml2/include/libxml2/libxml/libxml/parser.h *not* /usr/local/libxml2/include/libxml2/libxml/parser.h Regards, Dave I first used /usr/local/libxml2/include/ which did not work. Doing a find for parser.h made me change the include path. Could possibly the version of libxml2 be an issue: 2-2.7.7 ??? Thx: Peter On Wed, Sep 15, 2010 at 12:18 PM, Dave Page dp...@pgadmin.org wrote: On Wed, Sep 15, 2010 at 10:57 AM, Peter Roethlisberger peter.roethlisber...@gmail.com wrote: Hi there, I try to configure postgres: /configure --prefix=/usr/local/postgresql-8.4.4/ \ --without-docdir \ --with-openssl \ --with-libxml \ --with-libxslt \ --with-libraries=/usr/local/openssl:/lib:/lib64:/usr/local/readline/lib:/usr/local/zlib/lib:/usr/local/openssl/lib64:/usr/local/libxml2/lib:/usr/local/libxslt/lib \ --with-includes=/usr/local/readline/include:/usr/local/zlib/include:/usr/local/openssl/include:/usr/local/libxml2/include/libxml2/libxml:/usr/local/libxslt/include Now even though parser.h can be found in the includes path it claims: configure: error: header file libxml/parser.h is required for XML support Your include path contains /usr/local/libxml2/include/libxml2/libxml, so Postgres is probably looking for /usr/local/libxml2/include/libxml2/libxml/libxml/parser.h, which I suspect is wrong. Try removing the extra /libxml from the include path. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- ___ Peter Roethlisberger Suldhaltenstrasse 4 3703 Aeschi Switzerland 46°39'14 N 7°41'47 E Mobile: +41 (0)79 785 79 35 Fix: +41 (0)33 654 04 39 Skype:PeterRoethlisberger peter.roethlisber...@gmail.com -- 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] Monitoring object usage?
Hi, From: Colin 't Hart colinth...@gmail.com To: pgsql-general@postgresql.org Date: 15/09/2010 17:37 Subject: [GENERAL] Monitoring object usage? Sent by: pgsql-general-ow...@postgresql.org Hi, I'm trying to get a grip on a new body of code and a Postgres database that I'm working with. In particular I've been tasked with cleaning up the database. Is there a way in Postgres to enable monitoring on tables and columns to determine what's not actively being used? This would then form input for a more thorough check into the code base to see what's really not being used. Would the log_statement mentioned here help? http://www.postgresql.org/docs/current/static/runtime-config-logging.html I guess it will result in huge log files if it is a 'busy' database. Probably this has a performance penalty too. Regards, Jayadevan DISCLAIMER: The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect. -- 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] Monitoring object usage?
Well, you can see usage statistics for tables, indexes, functions and sequences ... but AFAIK it's not possible to do that for columns. See this http://www.postgresql.org/docs/8.4/static/runtime-config-statistics.html and then use pg_stat_ and pg_statio_ catalogs (http://www.postgresql.org/docs/8.4/static/monitoring-stats.html) to find objects that were not accessed recently (numbers are 0 or do not change anymore, etc.). But be careful as this may backfire quite easily - imagine an index that's used only in a batch processing the end of month. You could easily decide it's not necessary and drop it, causing a disaster. Tomas Hi, I'm trying to get a grip on a new body of code and a Postgres database that I'm working with. In particular I've been tasked with cleaning up the database. Is there a way in Postgres to enable monitoring on tables and columns to determine what's not actively being used? This would then form input for a more thorough check into the code base to see what's really not being used. Thanks cheers, Colin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting FATAL: terminating connection due to administrator command
Hi Since some days we are getting the above message. The system is a current CentOS 5.5, x86_64, Postgresql 8.4 as it comes with the packages postgresql84, postgresql84-libs etc. PostGIS is enabled, as it comes from http://www.argeo.org/linux/argeo-el. The error message appears from time to time. The exact same request, coming from a PHP applications, sometimes works, sometimes fails. This happens in different points of our applications, tipically, but not only, when large data portions are queried, as in geometric queries, using PostGIS. The server is only slightly loaded. Also in the PostgreSQL logs we get: FATAL: terminating connection due to administrator command repeated multiple times. The server is from Dell, Dell's hardware monitoring, OpenManage, says that the hardware, in particular memory and disk, are ok. We do have a nearly identical server, with the identical version of PostgreSQL/PostGIS, but that was last updated one or two months ago and which is intensly used as our testing and development server, which never gave us the same error message. Where could I start to troubleshoot this problem. Peter Hopfgartner -- 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] Force ARE in regexp string
=?ISO-8859-1?Q?Johannes_=D6berg?= johannes.ob...@proactivegaming.com writes: Hi! I'm trying to do an advanced regexp match but postgres doesn't seem to let me. I've set regex_flavor to ARE, and I've tried prefixing my strings, i.e. ~* E'***:abc' but for some reason postgres treats all my regexps as BRE's. Common newbie gotchas? Well, the symptom as described seems pretty improbable. You didn't show an exact example, but I'm suspecting the real problem is that you're not allowing for backslashes in a string literal getting eaten by string parsing. Do the cases that don't work for you involve backslashes in the regex? 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] workaround steps for autovaccum problem
Thanks Scott for your reply ... -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Wednesday, September 15, 2010 7:36 AM To: tamanna madaan Cc: David Fetter; pgsql-general@postgresql.org Subject: Re: [GENERAL] workaround steps for autovaccum problem On Tue, Sep 14, 2010 at 6:00 PM, tamanna madaan tamanna.ma...@globallogic.com wrote: I know upgrading postgres will resolve the problem permanently . But I wanted some workaround for now before I actually upgrade. But let me know if I really need to execute `vacuum freeze` In the scenario given in my previous update or I can skip this step. For your reference I am again updating the scenario : Autovacuum is getting invoked after every 5 mins and vacuums all the database turn by turn and every database is getting its turn of autovaccum after every 20 mins as there are 4 databases (template0 , template1, postgres and abc(my database) ). Now suppose , this autovacuum problem occurs and through some script its detected immediately at the very onset of the problem and below mentioned workaround steps are executed 1. Stop postgres 2. create 256K zero filled 0C01 file in /var/lib/pgsql/data/pg_clog folder. 3. Start postgres Then still , do I need to execute 'vacuum freeze' on all databases ?? Vacuum freeze is primarily intended for template databases that never get updated. If you have to allow conn to template0 to copy it, then yes maybe. This whole exercise smacks of doing more work to avoid upgrading than how much work the upgrade will be. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Upload latest JDBC driver releases to Maven Central
Hi, I just subscribed to this list. Does anybody know, if the latest JDBC drivers (8.4-702, 8.3-606, 8.2-5.11) can be found on Maven Central soon? Currently I can only find 8.4-701, 8.3-603 and 8.2-507. BR Gerd Bremer
Re: [GENERAL] Getting FATAL: terminating connection due to administrator command
On Wed, Sep 15, 2010 at 02:55:39PM +0200, Peter Hopfgartner wrote: Where could I start to troubleshoot this problem. First with staff, then with unauthorized access, then with failover software. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Getting FATAL: terminating connection due to administrator command
Peter Hopfgartner peter.hopfgart...@r3-gis.com writes: Since some days we are getting the above message. Also in the PostgreSQL logs we get: FATAL: terminating connection due to administrator command This is a result of something sending SIGTERM to the backend process. I have heard reports of load management software that SIGTERM's processes more or less at random whenever it decides the system is overloaded. If you have any such junkware installed on your server, try disabling it. The server is from Dell, Dell's hardware monitoring, OpenManage, says that the hardware, in particular memory and disk, are ok. Never dealt with OpenManage before, but you should cast a wary eye upon any Dell-specific software on the machine. This behavior is definitely not normal for Unix systems, so you need to look for nonstandard software (and what's more, nonstandard software running with root privileges, else it couldn't SIGTERM postgres processes). 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] select sql slow inside function
On 09/15/2010 02:28 AM, Sergey Konoplev wrote: Hi, On 15 September 2010 01:56, Gary Fug...@sigmaspace.com wrote I have a function proc_TaskComplete that inserts a record to table TaskHistory and then calls another function proc_ExportTaskComplete, that will retrieve (select) the record just inserted based on an index column (TaskId) in that table TaskHistory. I noticed that the select sql (inside proc_ExportTaskComplete) will take 3 seconds. Under normal condition (psql) the select sql is fast enough with the index. Can anyone explain why and how to fix the problem ? My postgresql version is 8.4.4 Could you please provide a use-case? Thanks, Gary -- We found out the problem and it was caused by the type used in the 2nd function for the key column (argument passed in from the main function) is numerical (12) instead of T_TaskId type, even though the T_TaskId is defined as 'numerical(12), 'not null'. Thanks, Gary -- 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] Replacing characters in a string
Sergey Konoplev gray...@gmail.com writes:
Just specify 'g' as the flags parameter (the 4th one). It means 'globally'.
SELECT regexp_replace('xaxx', 'x', 'e', 'g');
There are more of this flags described here:
http://www.postgresql.org/docs/8.4/interactive/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE
p.s. The question is for hackers - BTW I did not find 'g' in this
table, is it a docs bug?
No. That table is referenced for numerous cases where 'g' is not an
allowed flag. Instead, 'g' is documented in-line in the description
of regexp_replace.
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] Getting FATAL: terminating connection due to administrator command
On 15/09/2010 10:07 PM, Tom Lane wrote: The server is from Dell, Dell's hardware monitoring, OpenManage, says that the hardware, in particular memory and disk, are ok. Never dealt with OpenManage before, but you should cast a wary eye upon any Dell-specific software on the machine. (A bit of a digression, but): Personally I'd suggest being wary of any software supplied by the entity that will be responsible for the costs of any warranty work. They won't be at *all* sad if their software deflects blame and you don't discover a fault until your server is out of warranty. I've seen enough HDD vendor utilities report that a disk is just peachy, thanks, when it's developing and reallocating bad sectors at a rate of one every few minutes. (Hey, you didn't need that boot block, I've allocated you a shiny new one full of zeroes that's just as good.) The S.M.A.R.T. health check tends to say everything's fine, too ... but if you examine the fine print in the vendor attributes you see very high reallocated sector counts, ECC error levels, and other signs of a dying disk. I see this with so-called enterprise disks, not just consumer SATA drives. HDD vendors are certainly a particularly bad case, but nonetheless - don't trust vendor diagnostic software in general. If it says the device is broken I'll believe it because I trust them to make sure it won't report expensive false positives - but if it says it's OK I'll merely consider it not proven broken yet. False negatives work in their favour. Find 3rd party diagnostic tools where possible, and where not possible don't trust the overall health assessment provided by the vendor tools, dig into the fine print in the diagnostics and see what the details are like. For hard disks, smartctl from smartmontools is a lifesaver. Your issue doesn't sound HDD related, but it's worth mentioning for the future. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.3.8 question about backup/recovery behavior
We take nightly backups using the start backup, copying the data directory and archived logs, then stop backup method. Today I tested the recoverability of the backup by mounting this backup directory on a different server, copying the 3 hours of transactions logs from after last nights backup up until this morning to the different server, and starting up postgres there from the backup, and I want to verify what I experienced was normal behavior: My first attempt was to see if I could start the postmaster without setting up recovery.conf. It worked to my surprise, pg looked for the pg_xlog folder, and in the log noted the last transaction was at the time of the last available transaction log (915) and opened the database for connections. I guess I was surprised that it automatically rolled forward through all the logs up to the last one, and opened instead of just opening the database at the time of the stop backup command which was about 3 hours prior to the last available transaction log. Here is what I saw in the log: 2010-09-14 10:48:43.626 CDT @ [6848] LOG: 0: database system was not properly shut down; automatic recovery in progress 2010-09-14 10:48:43.626 CDT @ [6848] LOCATION: StartupXLOG, xlog.c:5006 2010-09-14 10:48:43.679 CDT @ [6848] LOG: 0: redo starts at 15A/4E01A5D8 2010-09-14 10:48:43.679 CDT @ [6848] LOCATION: StartupXLOG, xlog.c:5068 2010-09-14 10:49:01.059 CDT @ [6848] LOG: 58P01: could not open file pg_xlog/0001015A00C6 (log file 346, segment 198): No such file or directory 2010-09-14 10:49:01.059 CDT @ [6848] LOCATION: XLogFileRead, xlog.c:2365 2010-09-14 10:49:01.059 CDT @ [6848] LOG: 0: redo done at 15A/C5FFF1F8 2010-09-14 10:49:01.059 CDT @ [6848] LOCATION: StartupXLOG, xlog.c:5138 2010-09-14 10:49:01.097 CDT @ [6848] LOG: 0: last completed transaction was at log time 2010-09-14 09:15:07.450314-05 2010-09-14 10:49:01.097 CDT @ [6848] LOCATION: StartupXLOG, xlog.c:5142 The database was open, I could log in, but I couldnt determine a way to see if the database was brought up to 9:15am or some earlier time. My second attempt was to shut down, set the recovery.conf, and start the database in recovery. This worked as well as expected. Can anyone verify that the first attempt is valid or expected behavior? I guess I'm so used to oracles very specific and strict backup/recovery methods that this shocked me a little. Thanks Mike
[GENERAL] value
Hi there, I have a somewhat peculiar problem. To begin with, here are links to my schema and rules: my_table and associated rules http://postgresql.pastebin.com/0eCSuvkU and my_table_history http://postgresql.pastebin.com/cGm617Cp Scene: I'm implementing a pretty standard history keeping mechanism for some tables by attaching the following Rule to them. --INSERT CREATE OR REPLACE RULE on_insert AS ON INSERT TO my_table DO INSERT INTO history.my_table_history select new.*, now(), CURRENT_USER, 'INSERT'::character varying; --UPDATE CREATE OR REPLACE RULE on_update AS ON UPDATE TO my_table DO INSERT INTO history.my_table_history select new.*, now(), CURRENT_USER, 'UPDATE'::character varying; The table in the history schema is a duplicate of the original table except that my serial is now just a plain integer and I've added 4 columns (timestamp, username and change_type and a history_id serial ). The issue that I'm having is the following: I have a serial field named *my_table_id** *in my_table that is tied to a sequence, while the history table has the same column - but as a simple integer without the nextval. Let's, for argument's sake, say that my sequence has a currval() of 2000 and that I run an INSERT on my_table and my serial (correctly) gets set to 2001. This prompts the on_insert RULE to fire off its own INSERT command, EXCEPT that when I look into history.my_table_history I see that the my_table_history.*my_table_id **is set to 2002.* * * Going back to my_table, I can verify that its *my_table_id *is still set to 2001. Taking a look at the sequence I can verify that curval() is set to 2002 Being utterly perplexed I try and run an UPDATE command on the same row - and much to my relief (or distress) that rule seems to work just as it should, with the correct *my_table_id** *propagating down into the history table. Am I wrong in my thinking? Is there something faulty with the above RULE? I've been over and over my schemas and can't for the life of me figure out what the issue is. Does anybody have any idea what is going on? Kind regards from Iceland, Gissur Þórhallsson Loftmyndir ehf. Laugavegur 13 IS 101 Reykjavík - Iceland sími (tel): (+354) 540 2500 tölvupóstur (email): gis...@loftmyndir.is
Re: [GENERAL] Getting FATAL: terminating connection due to administrator command
Tom Lane t...@sss.pgh.pa.us wrote Subject: Re: [GENERAL] Getting FATAL: terminating connection due to administrator command Date: 15.09.2010 16:07 Peter Hopfgartner peter.hopfgart...@r3-gis.com writes: Since some days we are getting the above message. Also in the PostgreSQL logs we get: FATAL: terminating connection due to administrator command This is a result of something sending SIGTERM to the backend process. I have heard reports of load management software that SIGTERM's processes more or less at random whenever it decides the system is overloaded. If you have any such junkware installed on your server, try disabling it. The server is a rather bare bone server for web mapping, so basically PostgreSQL/PostGIS, Apache, PHP, Tomcat and little other stuff. The Dell software was the only which did not come from CentOS/EPEL/argeo/in-house RPM packages. I've removed the Dell stuff completely, but the problem is still there. The server is from Dell, Dell's hardware monitoring, OpenManage, says that the hardware, in particular memory and disk, are ok. Never dealt with OpenManage before, but you should cast a wary eye upon any Dell-specific software on the machine. This behavior is definitely not normal for Unix systems, so you need to look for nonstandard software (and what's more, nonstandard software running with root privileges, else it couldn't SIGTERM postgres processes). Other informations: disks are costly SAS drives in a RAID 1 array, memory is with ECC. Security level is disabled SELinux is Permissive. The server acts as a XEN host Is it reasonable to restrict the problem to kernel/hardware and/or PostgreSQL/PostGIS itself? Can I trace where the SIGTERM comes from? regards, tom lane 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] Getting FATAL: terminating connection due to administrator command
Peter Hopfgartner peter.hopfgart...@r3-gis.com writes: Tom Lane t...@sss.pgh.pa.us wrote This is a result of something sending SIGTERM to the backend process. Can I trace where the SIGTERM comes from? If this is a recent Red-Hat-based release, I think that systemtap could probably be used to determine that. There's a script here that solves a related problem: http://sourceware.org/systemtap/examples/process/sigmon.stp 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] select sql slow inside function
On 09/15/2010 09:46 AM, Gary Fu wrote: On 09/15/2010 02:28 AM, Sergey Konoplev wrote: Hi, On 15 September 2010 01:56, Gary Fug...@sigmaspace.com wrote I have a function proc_TaskComplete that inserts a record to table TaskHistory and then calls another function proc_ExportTaskComplete, that will retrieve (select) the record just inserted based on an index column (TaskId) in that table TaskHistory. I noticed that the select sql (inside proc_ExportTaskComplete) will take 3 seconds. Under normal condition (psql) the select sql is fast enough with the index. Can anyone explain why and how to fix the problem ? My postgresql version is 8.4.4 Could you please provide a use-case? Thanks, Gary -- We found out the problem and it was caused by the type used in the 2nd function for the key column (argument passed in from the main function) is numerical (12) instead of T_TaskId type, even though the T_TaskId is defined as 'numerical(12), 'not null'. Thanks, Gary Sorry, I made mistake again, the T_TaskId is Integer, not Numerical(12) and I think due to the type difference, the table scan, instead of the index is used. Thanks, Gary -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table Comments
I saw in the documentation for PostgreSQL that I can add 'comments' to table entries when creating columns: http://www.postgresql.org/docs/8.1/static/tutorial-table.html CREATE TABLE weather ( cityvarchar(80), temp_lo int, -- low temperature temp_hi int, -- high temperature prcpreal, -- precipitation datedate ); I did a search and don't understand in what aspect are the 'comments' relevant / visible? I don't see the comments when I attempt to list / describe the table with \d weather; Any suggestions? -- 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] Search then Delete Performance
On Wed, Sep 15, 2010 at 5:15 AM, Dann Corbit dcor...@connx.com wrote: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of John R Pierce Sent: Tuesday, September 14, 2010 8:41 PM To: Michael Hull Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Search then Delete Performance On 09/14/10 5:55 PM, Michael Hull wrote: So fairly simply, I have a daemon running on a machine, which accesses this DB. Clients connect and request the details for say 1000 simulations, at which point the daemon takes 1000 entries from the unassigned table and moves them to the assigned table. The once the client is finished with those jobs, it signals this to the daemon, which then move those jobs from 'assigned' to 'complete'. So this is fairly simple to implement, but my problem is that it is very slow. instead of moving data from one table to another, it might be better to just have a table of simulations, then another table which just contains the PK of each simulation, and a flag that says its assigned or unassigned (and maybe the client its assigned to? and anything else thats related to this assignment?)... so instead of moving your big table rows, which involves deleting them from one table and inserting them into another, you just update the row of this small table. if you create this small table with a fillfactor like 75%, the updates likely will easily be handled by HOT Or just a status integer in the main table along the lines of: 1 = unassigned 2 = assigned 3 = running 4 = completed Etc. And then update the status as appropriate and check the status as needed. If you want until a batch is done, you would also be able to update like this: UPDATE jobs SET status = 4 WHERE status = 3 As you like, with a single statement. There are lots of job schedulers on SOURCEFORGE. http://sourceforge.net/search/?words=scheduler+workflowtype_of_search=softsort=latest_file_datesortdir=desclimit=100 Thanks everyone for the input - I will have an investigate Mike -- 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] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working
On Tue, Sep 14, 2010 at 6:55 PM, Tatsuo Ishii is...@sraoss.co.jp wrote: Sorry for not enough description about pool_passwd. It's located under the same directory as pgpool.conf. So the default is /usr/local/etc/pool_passwd. You need to create /usr/local/etc/pool_passwd if the uid to run pgpool server does not have the right to create a new file under /usr/local/etc, you need to create an empty /usr/local/etc/pool_passwd owned by the same uid as pgpool. After that you should be able to register your password as decribed in the doc. Authentication / Access Controls -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp Thanks, I should have thought to try that location. :) I put the pool_passwd file in the expected location (custom build, /opt/postgres/etc/pool_passwd and /etc/pool_passwd) and reran my test. I'm still having the same connection problem. I think it would be good to add an additional command line parameter to pgpool to allow overriding the location of that file (the same way the location of pcp.conf, pgpool.conf, and pgpool_hba.conf can be overriden). direct connection: psql -h 10.192.165.125 -p 5432 template1 test Password for user test: psql (9.0rc1) Type help for help. template1=# proxied connection: psql -h 127.0.0.1 -p template1 test Password for user test: psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. pgpool log: 2010-09-15 15:48:36 LOG: pid 2324: connection received: host=127.0.0.1 port=46999 2010-09-15 15:48:36 DEBUG: pid 2324: Protocol Major: 3 Minor: 0 database: template1 user: test 2010-09-15 15:48:36 DEBUG: pid 2324: new_connection: connecting 0 backend 2010-09-15 15:48:36 DEBUG: pid 2324: pool_ssl: SSL requested but SSL support is not available 2010-09-15 15:48:36 DEBUG: pid 2324: pool_read_message_length: slot: 0 length: 12 2010-09-15 15:48:36 DEBUG: pid 2324: trying md5 authentication 2010-09-15 15:48:36 DEBUG: pid 2324: DB node id: 0 salt: 458c816a *2010-09-15 15:48:36 DEBUG: pid 2324: read_password_packet_password: failed to read password packet p* 2010-09-15 15:48:36 ERROR: pid 2324: do_md5: read_password_packet failed 2010-09-15 15:48:36 DEBUG: pid 2324: do_md5failed in slot 0 2010-09-15 15:48:38 DEBUG: pid 2323: I am 2323 accept fd 6 2010-09-15 15:48:38 LOG: pid 2323: connection received: host=127.0.0.1 port=47001 2010-09-15 15:48:38 DEBUG: pid 2323: Protocol Major: 3 Minor: 0 database: template1 user: test 2010-09-15 15:48:38 DEBUG: pid 2323: new_connection: connecting 0 backend 2010-09-15 15:48:38 DEBUG: pid 2323: pool_ssl: SSL requested but SSL support is not available 2010-09-15 15:48:38 DEBUG: pid 2323: pool_read_message_length: slot: 0 length: 12 2010-09-15 15:48:38 DEBUG: pid 2323: trying md5 authentication 2010-09-15 15:48:38 DEBUG: pid 2323: DB node id: 0 salt: 6d1da26 2010-09-15 15:48:38 DEBUG: pid 2260: reap_handler called 2010-09-15 15:48:38 DEBUG: pid 2260: reap_handler: call wait3 2010-09-15 15:48:38 ERROR: pid 2260: Child process 2323 was terminated by segmentation fault 2010-09-15 15:48:38 DEBUG: pid 2260: child 2323 exits with status 11 by signal 11 2010-09-15 15:48:38 DEBUG: pid 2260: fork a new child pid 2328 2010-09-15 15:48:38 DEBUG: pid 2260: reap_handler: normally exited 2010-09-15 15:48:38 DEBUG: pid 2328: I am 2328 postgres log: LOG: invalid password packet size LOG: unexpected EOF on client connection psql --version psql (PostgreSQL) 9.0rc1 contains support for command-line editing pgpool --version pgpool-II version 3.0 (umiyameboshi)
Re: [GENERAL] Table Comments
In response to Carlos Mennens carlos.menn...@gmail.com: I saw in the documentation for PostgreSQL that I can add 'comments' to table entries when creating columns: http://www.postgresql.org/docs/8.1/static/tutorial-table.html CREATE TABLE weather ( cityvarchar(80), temp_lo int, -- low temperature temp_hi int, -- high temperature prcpreal, -- precipitation datedate ); I did a search and don't understand in what aspect are the 'comments' relevant / visible? I don't see the comments when I attempt to list / describe the table with \d weather; You're confusing table comments with SQL comments. SQL comments (which you show above) comment the SQL and are lost if the SQL code is not preserved. i.e., they do not affect what the SQL does, thus they are not stored anywhere in the database. They're a great help for documenting queries in you application code, though. To add comments to a table (which it seems that you want) use the COMMENT ON command to add them: http://www.postgresql.org/docs/8.4/static/sql-comment.html -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] Table Comments
On Wed, 15 Sep 2010 08:53:16 -0700, Carlos Mennens carlos.menn...@gmail.com wrote: CREATE TABLE weather ( temp_lo int, -- low temperature ); I did a search and don't understand in what aspect are the 'comments' relevant / visible? I don't see the comments when I attempt to list / describe the table with \d weather; Those are comments to the SQL code, not comments to the columns. Comments to SQL code are fully ignored by PostgreSQL on execution. Those are useful if you have an SQL script and you want to document something there. For column comments you might want to take a look at the COMMENT command. -- 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] Table Comments
On 09/15/2010 08:53 AM, Carlos Mennens wrote: I saw in the documentation for PostgreSQL that I can add 'comments' to table entries when creating columns: http://www.postgresql.org/docs/8.1/static/tutorial-table.html CREATE TABLE weather ( cityvarchar(80), temp_lo int, -- low temperature temp_hi int, -- high temperature prcpreal, -- precipitation datedate ); I did a search and don't understand in what aspect are the 'comments' relevant / visible? I don't see the comments when I attempt to list / describe the table with \d weather; Any suggestions? Don't confuse -- with COMMENT ON. A -- is SQL's comment character, like # in Bash, // in C, etc. so you can comment the SQL statements in your code. The COMMENT ON statement lets you add descriptions to database objects (tables, individual columns, etc.). See http://www.postgresql.org/docs/8.1/static/sql-comment.html for more on the COMMENT statement. 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
[GENERAL] Data Model Advice
All, My data modeling and SQL are oftentimes woefully inadequate, and I am seeking advice on how to implement a particular database design. My database (so far) is comprised of the following types of tables: The parts table contains parts of a particular type primary key is an alphanumeric part number, some with dashes. Table public.parts Column | Type |Modifiers +---+- partno | character varying(64) | not null partno_raw | character varying(64) | default NULL::character varying boxno | integer | not null slotno | integer | not null Indexes: pk_parts PRIMARY KEY, btree (partno) The parts_subs table contains part numbers different manufacturers that are identical to something that already exists in the parts table Table public.parts_subs Column| Type | Modifiers -+---+ partno | character varying(64) | not null partsub | character varying(64) | not null partsub_raw | character varying(64) | not null boxno | integer | not null slotno | integer | not null Indexes: pk_parts_subs PRIMARY KEY, btree (partno, partsub) I'm trying to determine, and I'm seeking advice on: How to maintain the original part number format but create the primary key with only alphanumeric. I currently have a 'before insert or update' trigger to strip the non-alphanumeric characters, but it doesn't permit the insertion of a record because it is null on insert (for some reason). The trigger function, which I'm trying to make as flexible as possible, is: CREATE OR REPLACE FUNCTION fixup_partnumbers() RETURNS TRIGGER AS $fixup_partnumbers$ BEGIN IF (STRPOS(TG_TABLE_NAME, '_subs') 0) THEN NEW.partsub := REGEXP_REPLACE(NEW.partsub_raw, E'(\\W|_)', '', 'g'); ELSE NEW.partno := REGEXP_REPLACE(NEW.partno_raw, E'(\\W|_)', '', 'g'); END IF; RETURN NEW; END; $fixup_partnumbers$ LANGUAGE plpgsql; Thank you in advance for any advice and assistance you can provide. -- Gary Chambers -- 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] Table Comments
Thank you all for that information. -- 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] value
On Wed, Sep 15, 2010 at 03:16:55PM +, Gissur Þórhallsson wrote: Hi there, I have a somewhat peculiar problem. To begin with, here are links to my schema and rules: my_table and associated rules http://postgresql.pastebin.com/0eCSuvkU and my_table_history http://postgresql.pastebin.com/cGm617Cp [etc.] Does anybody have any idea what is going on? Yes. You're using RULEs where TRIGGERs would do. Change to TRIGGERs. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] help with error unexpected pageaddr
Hey everyone, We have a PG 8.3.7 server that is doing WAL log shipping to 2 other servers that are remote mirrors. This has been working well for almost two years. Last night we did some massive data and structure changes to one of our databases. Since then I get these errors on the two mirrors: 2010-09-15 08:35:05 EDT: LOG: restored log file 0001030100D9 from archive 2010-09-15 08:35:27 EDT: LOG: restored log file 0001030100DA from archive 2010-09-15 08:35:40 EDT: LOG: restored log file 0001030100DB from archive 2010-09-15 08:35:40 EDT: LOG: unexpected pageaddr 301/4700 in log file 769, segment 219, offset 0 2010-09-15 08:35:40 EDT: LOG: redo done at 301/DA370780 2010-09-15 08:35:40 EDT: LOG: last completed transaction was at log time 2010-09-15 08:30:01.24936-04 2010-09-15 08:35:40 EDT: LOG: restored log file 0001030100DA from archive 2010-09-15 08:36:26 EDT: LOG: selected new timeline ID: 2 2010-09-15 08:37:11 EDT: LOG: archive recovery complete I've taken two separate file level backups and tried to restart the mirrors, and every time on both servers I get a similar error message. I seem to recall reading that it may have something to do with corruption in the timeline, which is why it's jumping to a new timeline ID. 1. Can anyone tell me what this means? 2. Is there some corruption in the database? 3. If so, is there an easy way to fix it? Also, one additional question. I don't have a 1.history file which makes the PITRTools complain constantly. Is there any way to regenerate this file? Any help would be much appreciated. I'm rather worried that I've got corruption, and not having the mirrors running puts us at risk for data loss.
Re: [GENERAL] help with error unexpected pageaddr
Scot Kreienkamp skre...@la-z-boy.com writes: We have a PG 8.3.7 server that is doing WAL log shipping to 2 other servers that are remote mirrors. This has been working well for almost two years. Last night we did some massive data and structure changes to one of our databases. Since then I get these errors on the two mirrors: 2010-09-15 08:35:05 EDT: LOG: restored log file 0001030100D9 from archive 2010-09-15 08:35:27 EDT: LOG: restored log file 0001030100DA from archive 2010-09-15 08:35:40 EDT: LOG: restored log file 0001030100DB from archive 2010-09-15 08:35:40 EDT: LOG: unexpected pageaddr 301/4700 in log file 769, segment 219, offset 0 This appears to indicate that you archived the wrong contents of log file 0001030100DB. If you don't still have the correct contents on the master, I think the only way to recover is to take a fresh base backup so you can make the slaves roll forward from a point later than this log segment. There's no reason to suppose that there's data corruption on the master, just bad data in the WAL archive. You'd probably be well advised to look closely at your WAL archiving script to see if it has any race conditions that might be triggered by very fast generation of WAL. Also, one additional question. I don't have a 1.history file which makes the PITRTools complain constantly. Is there any way to regenerate this file? Just ignore that, it's cosmetic (the file isn't supposed to exist). 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] Using libpq, floats and binary data
Hi Guys,
I have jsut started to use the libpq interface to postgre, and was
wondering if someone with some experience could explain the 'best
practices' :)
I am preparing a query, then submitting it with binary data. 6 values
are ints, and one is a float.
For the ints, I understand that since I need network byte ordering, I
have to pump them through htonl()
The same is also true for the floats I assume, so I have dones some
dirty,dirty casting, but this feels like a source for problems later
on.
It does the correct thing, but is there a better way of doing it??
Mike
char qString[] = INSERT INTO raw_results (ca,kf,ks,lk,na,iinj,
AP1height) VALUES( $1::int4, $2::int4, $3::int4, $4::int4, $5::int4,
$6::int4, $7::float4);
PGresult* pPreparedQuery = PQprepare(pDB, InsertQ,qString, nParams, NULL);
float AP1Height = 3.141;
/* TROUBLE AHEAD! */
if( sizeof( float) != sizeof(int32_t) ) throw MyException();
int32_t AP_int = * ( (int32_t*) AP1Height );
int32_t AP_intSwapped = htonl(AP_int);
float AP1HeightSwapped = *( (float*) AP_intSwapped);
/* TROUBLE OVER */
int32_t ca= htonl(100);
int32_t kf= htonl(200);
int32_t ks= htonl(300);
int32_t lk= htonl(400);
int32_t na= htonl(500);
int32_t iinj= htonl(600);
const char* values[nParams] = {
(char*)ca,
(char*)kf,
(char*)ks,
(char*)lk,
(char*)lk,
(char*)iinj,
(char*)AP1HeightSwapped,
};
int lengths[nParams] = {
sizeof(ca),
sizeof(kf),
sizeof(ks),
sizeof(lk),
sizeof(na),
sizeof(iinj),
sizeof(AP1Height),
};
int binary[nParams]={1,1,1,1,1, 1,1};
PGresult* res = PQexecPrepared( pDB, InsertQ, nParams, values,
lengths,binary,0);
--
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] Post Install / Secure PostgreSQL
Thanks all for the help! I have a much better understanding now of how user accounts are managed via ident authentication. The only thing I have yet to figure out or understand is how to login to PostgreSQL as my user account but not to any specific database. I understand that when I am logged in as my user account, I can simply login using: psql -U cmennens database_name (I know I can omit the '-U cmennens' if I am 'cmennens' via Linux shell) But when if 'cmennens' wants to login to PostgreSQL but not connect to any specific database? I know in MySQL you can login to the MySQL server CLI but not be attached to any specific database if you want to just peek around and do basic administrative tasks. Is this possible in PostgreSQL? When I do the following, it works but I think only because it's using ident credentials and then connects automatically to the 'postgres' database. I could be wrong however... [postg...@db1 ~]$ psql psql (8.4.4) Type help for help. postgres=# \d No relations found. postgres=# \dt No relations found. postgres=# \c postgres psql (8.4.4) You are now connected to database postgres. postgres=# \dt No relations found. postgres=# \d From the above I login as 'postgres' but don't specify a specific database to connect to. Is this possible to do as my user 'cmennens' who is listed as a super user? Also from above, if I connect to the 'postgres' database as 'postgres' user, why can I not list any tables above? I keep getting No relations found.? -- 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] Post Install / Secure PostgreSQL
On 09/15/10 10:00 AM, Carlos Mennens wrote: But when if 'cmennens' wants to login to PostgreSQL but not connect to any specific database? I know in MySQL you can login to the MySQL server CLI but not be attached to any specific database if you want to just peek around and do basic administrative tasks. Is this possible in PostgreSQL? no, there is no such state in postgres. you connect and log into a database. connecting to a different database requires closing that connection and opening a new one (which is what the \c command does in psql). -- 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] Post Install / Secure PostgreSQL
On Wed, Sep 15, 2010 at 1:34 PM, John R Pierce pie...@hogranch.com wrote: no, there is no such state in postgres. you connect and log into a database. connecting to a different database requires closing that connection and opening a new one (which is what the \c command does in psql). Thanks but then I am confused why I am getting the following: postgres=# \d No relations found. postgres=# \dt No relations found. postgres=# \c postgres psql (8.4.4) You are now connected to database postgres. postgres=# \dt No relations found. postgres=# \d -- 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] Post Install / Secure PostgreSQL
On 09/15/10 10:36 AM, Carlos Mennens wrote: On Wed, Sep 15, 2010 at 1:34 PM, John R Piercepie...@hogranch.com wrote: no, there is no such state in postgres. you connect and log into a database. connecting to a different database requires closing that connection and opening a new one (which is what the \c command does in psql). Thanks but then I am confused why I am getting the following: postgres=# \d No relations found. postgres=# \dt No relations found. postgres=# \c postgres psql (8.4.4) You are now connected to database postgres. postgres=# \dt No relations found. postgres=# \d the 'postgres' database on your system is empty. this is quite typical, as that database is simply a convenience for the postgres user to have something to log into while doing his administrative duties. -- 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] Post Install / Secure PostgreSQL
On Wed, Sep 15, 2010 at 1:36 PM, Carlos Mennens carlos.menn...@gmail.comwrote: Thanks but then I am confused why I am getting the following: You initially connected to the postgres database because you were logging in as the postgres user... postgres=# \d No relations found. postgres=# \dt No relations found. ... and then you *reconnected* to the same database by requesting to do so. postgres=# \c postgres psql (8.4.4) You are now connected to database postgres. postgres=# \dt No relations found. postgres=# \d -- - David T. Wilson david.t.wil...@gmail.com
Re: [GENERAL] Post Install / Secure PostgreSQL
On Wed, Sep 15, 2010 at 10:36 AM, Carlos Mennens carlos.menn...@gmail.com wrote: postgres=# \d No relations found. postgres=# \dt No relations found. postgres=# \c postgres psql (8.4.4) You are now connected to database postgres. postgres=# \dt No relations found. postgres=# \d try: select current_database(); -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] Post Install / Secure PostgreSQL
John R Pierce pie...@hogranch.com writes: On 09/15/10 10:00 AM, Carlos Mennens wrote: But when if 'cmennens' wants to login to PostgreSQL but not connect to any specific database? I know in MySQL you can login to the MySQL server CLI but not be attached to any specific database if you want to just peek around and do basic administrative tasks. Is this possible in PostgreSQL? no, there is no such state in postgres. you connect and log into a database. connecting to a different database requires closing that connection and opening a new one (which is what the \c command does in psql). It might be worth pointing out that what mysql calls a database is more or less what we call a schema; there isn't any close equivalent in mysql to a Postgres installation with multiple databases. The initial state in mysql is about like having an empty search_path in PG: you can get at all tables in the database, you just have to qualify their names explicitly. And USE database corresponds to a SET search_path operation. 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] Post Install / Secure PostgreSQL
On Wed, Sep 15, 2010 at 1:43 PM, John R Pierce pie...@hogranch.com wrote: the 'postgres' database on your system is empty. this is quite typical, as that database is simply a convenience for the postgres user to have something to log into while doing his administrative duties. OK this makes sense and I couldn't find in the docs or any reading that by default the 'postgres' database is empty and there for just a space for the 'postgres' user to login to. That explains a lot but when I run: postgres=# SELECT * FROM pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig --+--+-+--+---+--+--+--- postgres | 10 | t | t| t | | | webmail |16384 | f | f| f | | | carlos |16385 | t | t| t | | | (3 rows) Doesn't that show I'm connected to the 'postgres' database and there is a table called 'pg_user' which holds all my PostgreSQL user info? That doesn't make sense to me if the database is empty unless I am missing something here. The only way I knew 'pg_user' was available was because I ran the command '\dS'. -- 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] Post Install / Secure PostgreSQL
On September 15, 2010 11:10:45 am Carlos Mennens wrote: Doesn't that show I'm connected to the 'postgres' database and there is a table called 'pg_user' which holds all my PostgreSQL user info? That doesn't make sense to me if the database is empty unless I am missing something here. The only way I knew 'pg_user' was available was because I ran the command '\dS'. system tables don't show up in normal views. I don't think they really belong to particular databases either, but someone might correct me on that. -- 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] Post Install / Secure PostgreSQL
On 09/15/10 11:10 AM, Carlos Mennens wrote: On Wed, Sep 15, 2010 at 1:43 PM, John R Piercepie...@hogranch.com wrote: the 'postgres' database on your system is empty. this is quite typical, as that database is simply a convenience for the postgres user to have something to log into while doing his administrative duties. OK this makes sense and I couldn't find in the docs or any reading that by default the 'postgres' database is empty and there for just a space for the 'postgres' user to login to. That explains a lot but when I run: postgres=# SELECT * FROM pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig --+--+-+--+---+--+--+--- postgres | 10 | t | t| t | | | webmail |16384 | f | f| f | | | carlos |16385 | t | t| t | | | (3 rows) Doesn't that show I'm connected to the 'postgres' database and there is a table called 'pg_user' which holds all my PostgreSQL user info? That doesn't make sense to me if the database is empty unless I am missing something here. The only way I knew 'pg_user' was available was because I ran the command '\dS'. there is an extensive pg_catalog schema containing the system tables which are shared by all databases in the cluster.pg_catalog.pg_user is the same view in all databases. -- 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] Post Install / Secure PostgreSQL
Alan Hodgson ahodg...@simkin.ca writes: On September 15, 2010 11:10:45 am Carlos Mennens wrote: Doesn't that show I'm connected to the 'postgres' database and there is a table called 'pg_user' which holds all my PostgreSQL user info? That doesn't make sense to me if the database is empty unless I am missing something here. The only way I knew 'pg_user' was available was because I ran the command '\dS'. system tables don't show up in normal views. I don't think they really belong to particular databases either, but someone might correct me on that. There are a small number of shared catalogs, in particular the list of users and the list of databases, that are visible in all databases. Most of the catalogs are per-database, though, so that they can have different contents in different databases. 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] Using libpq, floats and binary data
zz
On Wed, Sep 15, 2010 at 12:02 PM, Michael Hull
mikehul...@googlemail.com wrote:
Hi Guys,
I have jsut started to use the libpq interface to postgre, and was
wondering if someone with some experience could explain the 'best
practices' :)
I am preparing a query, then submitting it with binary data. 6 values
are ints, and one is a float.
For the ints, I understand that since I need network byte ordering, I
have to pump them through htonl()
The same is also true for the floats I assume, so I have dones some
dirty,dirty casting, but this feels like a source for problems later
on.
It does the correct thing, but is there a better way of doing it??
Mike
char qString[] = INSERT INTO raw_results (ca,kf,ks,lk,na,iinj,
AP1height) VALUES( $1::int4, $2::int4, $3::int4, $4::int4, $5::int4,
$6::int4, $7::float4);
PGresult* pPreparedQuery = PQprepare(pDB, InsertQ,qString, nParams, NULL);
float AP1Height = 3.141;
/* TROUBLE AHEAD! */
if( sizeof( float) != sizeof(int32_t) ) throw MyException();
int32_t AP_int = * ( (int32_t*) AP1Height );
int32_t AP_intSwapped = htonl(AP_int);
float AP1HeightSwapped = *( (float*) AP_intSwapped);
/* TROUBLE OVER */
int32_t ca= htonl(100);
int32_t kf= htonl(200);
int32_t ks= htonl(300);
int32_t lk= htonl(400);
int32_t na= htonl(500);
int32_t iinj= htonl(600);
const char* values[nParams] = {
(char*)ca,
(char*)kf,
(char*)ks,
(char*)lk,
(char*)lk,
(char*)iinj,
(char*)AP1HeightSwapped,
};
int lengths[nParams] = {
sizeof(ca),
sizeof(kf),
sizeof(ks),
sizeof(lk),
sizeof(na),
sizeof(iinj),
sizeof(AP1Height),
};
int binary[nParams]={1,1,1,1,1, 1,1};
PGresult* res = PQexecPrepared( pDB, InsertQ, nParams, values,
lengths,binary,0);
there is a much better way -- libpqtypes:
http://libpqtypes.esilo.com/
char qString[] = INSERT INTO raw_results
(ca,kf,ks,lk,na,iinj,AP1height) VALUES( %int4, %int4, %int4, %int4,
%int4, %int4, %float4);
res = PQexecf(conn, qString, ca, kf, ks, lk, na, iinj, AP1Height);
getting data out is similarly easy.
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] Getting FATAL: terminating connection due to administrator command
On Wednesday 15 September 2010, Peter Hopfgartner elucidated thus: The server is a rather bare bone server for web mapping, so basically PostgreSQL/PostGIS, Apache, PHP, Tomcat and little other stuff. The Dell software was the only which did not come from CentOS/EPEL/argeo/in-house RPM packages. I've removed the Dell stuff completely, but the problem is still there. Are you running out of memory and getting killed by the OOM killer? j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com - Fairbanks, AK PGP Key: http://pgp.mit.edu/ ID 0x73B13B6A -- 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] help with error unexpected pageaddr
Scot Kreienkamp skre...@la-z-boy.com writes: I tried to take a new base backup about 45 minutes ago. The master has rolled forward a number of WAL files since I last tried, but it still fails. LOG: restored log file 0001030100FE from archive LOG: restored log file 00010302 from archive LOG: restored log file 000103020001 from archive LOG: restored log file 000103020002 from archive LOG: restored log file 000103020003 from archive LOG: unexpected pageaddr 301/5000 in log file 770, segment 3, offset 0 Hmmm ... is it possible that your WAL archive contains log files numbered higher than where your master is? 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] Getting FATAL: terminating connection due to administrator command
Joshua J. Kugler jos...@eeinternet.com writes: On Wednesday 15 September 2010, Peter Hopfgartner elucidated thus: The server is a rather bare bone server for web mapping, so basically PostgreSQL/PostGIS, Apache, PHP, Tomcat and little other stuff. The Dell software was the only which did not come from CentOS/EPEL/argeo/in-house RPM packages. I've removed the Dell stuff completely, but the problem is still there. Are you running out of memory and getting killed by the OOM killer? The OOM killer hits its victims with SIGKILL (kill -9), so we can rule that out as not matching Peter's symptoms. This is definitely an unwanted SIGTERM not SIGKILL. 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] help with error unexpected pageaddr
Scot Kreienkamp skre...@la-z-boy.com writes: We have a PG 8.3.7 server that is doing WAL log shipping to 2 other servers that are remote mirrors. This has been working well for almost two years. Last night we did some massive data and structure changes to one of our databases. Since then I get these errors on the two mirrors: 2010-09-15 08:35:05 EDT: LOG: restored log file 0001030100D9 from archive 2010-09-15 08:35:27 EDT: LOG: restored log file 0001030100DA from archive 2010-09-15 08:35:40 EDT: LOG: restored log file 0001030100DB from archive 2010-09-15 08:35:40 EDT: LOG: unexpected pageaddr 301/4700 in log file 769, segment 219, offset 0 This appears to indicate that you archived the wrong contents of log file 0001030100DB. If you don't still have the correct contents on the master, I think the only way to recover is to take a fresh base backup so you can make the slaves roll forward from a point later than this log segment. There's no reason to suppose that there's data corruption on the master, just bad data in the WAL archive. You'd probably be well advised to look closely at your WAL archiving script to see if it has any race conditions that might be triggered by very fast generation of WAL. Also, one additional question. I don't have a 1.history file which makes the PITRTools complain constantly. Is there any way to regenerate this file? Just ignore that, it's cosmetic (the file isn't supposed to exist). regards, tom lane Tom, I tried to take a new base backup about 45 minutes ago. The master has rolled forward a number of WAL files since I last tried, but it still fails. LOG: restored log file 0001030100FE from archive LOG: restored log file 00010302 from archive LOG: restored log file 000103020001 from archive LOG: restored log file 000103020002 from archive LOG: restored log file 000103020003 from archive LOG: unexpected pageaddr 301/5000 in log file 770, segment 3, offset 0 LOG: redo done at 302/2BCE828 LOG: last completed transaction was at log time 2010-09-15 15:07:01.040854-04 LOG: restored log file 000103020002 from archive LOG: selected new timeline ID: 2 My entire WAL archiving script is 4 cp %p %f commands. It's so short I don't even have a script, it's directly in the postgresql.conf archive command. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Data Model Advice
Subject: Data Model Advice All, My data modeling and SQL are oftentimes woefully inadequate, and I am seeking advice on how to implement a particular database design. My database (so far) is comprised of the following types of tables: The parts table contains parts of a particular type primary key is an alphanumeric part number, some with dashes. Table public.parts Column | Type |Modifiers +---+- partno | character varying(64) | not null partno_raw | character varying(64) | default NULL::character varying boxno | integer | not null slotno | integer | not null Indexes: pk_parts PRIMARY KEY, btree (partno) The parts_subs table contains part numbers different manufacturers that are identical to something that already exists in the parts table Table public.parts_subs Column| Type | Modifiers -+---+ partno | character varying(64) | not null partsub | character varying(64) | not null partsub_raw | character varying(64) | not null boxno | integer | not null slotno | integer | not null Indexes: pk_parts_subs PRIMARY KEY, btree (partno, partsub) I'm trying to determine, and I'm seeking advice on: How to maintain the original part number format but create the primary key with only alphanumeric. I currently have a 'before insert or update' trigger to strip the non-alphanumeric characters, but it doesn't permit the insertion of a record because it is null on insert (for some reason). The trigger function, which I'm trying to make as flexible as possible, is: CREATE OR REPLACE FUNCTION fixup_partnumbers() RETURNS TRIGGER AS $fixup_partnumbers$ BEGIN IF (STRPOS(TG_TABLE_NAME, '_subs') 0) THEN NEW.partsub := REGEXP_REPLACE(NEW.partsub_raw, E'(\\W|_)', '', 'g'); ELSE NEW.partno := REGEXP_REPLACE(NEW.partno_raw, E'(\\W|_)', '', 'g'); END IF; RETURN NEW; END; $fixup_partnumbers$ LANGUAGE plpgsql; Thank you in advance for any advice and assistance you can provide. -- Gary Chambers -- 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] Data Model Advice
On Wed, Sep 15, 2010 at 12:04 PM, Gary Chambers gwch...@gmail.com wrote: All, My data modeling and SQL are oftentimes woefully inadequate, and I am seeking advice on how to implement a particular database design. My database (so far) is comprised of the following types of tables: The parts table contains parts of a particular type primary key is an alphanumeric part number, some with dashes. Table public.parts Column | Type | Modifiers +---+- partno | character varying(64) | not null partno_raw | character varying(64) | default NULL::character varying boxno | integer | not null slotno | integer | not null Indexes: pk_parts PRIMARY KEY, btree (partno) The parts_subs table contains part numbers different manufacturers that are identical to something that already exists in the parts table Table public.parts_subs Column | Type | Modifiers -+---+ partno | character varying(64) | not null partsub | character varying(64) | not null partsub_raw | character varying(64) | not null boxno | integer | not null slotno | integer | not null Indexes: pk_parts_subs PRIMARY KEY, btree (partno, partsub) I'm trying to determine, and I'm seeking advice on: How to maintain the original part number format but create the primary key with only alphanumeric. I currently have a 'before insert or update' trigger to strip the non-alphanumeric characters, but it doesn't permit the insertion of a record because it is null on insert (for some reason). The trigger function, which I'm trying to make as flexible as possible, is: CREATE OR REPLACE FUNCTION fixup_partnumbers() RETURNS TRIGGER AS $fixup_partnumbers$ BEGIN IF (STRPOS(TG_TABLE_NAME, '_subs') 0) THEN NEW.partsub := REGEXP_REPLACE(NEW.partsub_raw, E'(\\W|_)', '', 'g'); ELSE NEW.partno := REGEXP_REPLACE(NEW.partno_raw, E'(\\W|_)', '', 'g'); END IF; RETURN NEW; END; $fixup_partnumbers$ LANGUAGE plpgsql; Thank you in advance for any advice and assistance you can provide. hm. let me make a general rule of thumb suggestion: generic behaviors, generic trigger function, specific behaviors, specific trigger function. setting a field that is only on a particular table is specific and I'd prefer to make two trigger functions, which both call something like: create or replace function fixup_partno(text) returns text as $$ SELECT REGEXP_REPLACE(NEW.partno_raw, E'(\\W|_)', '', 'g'); $$ language sql immutable; You should in theory be able to write a before trigger to intercept and modify any field before it hits the table, including the primary key. The following works for me: create table foo(t text primary key); create or replace function fixup_foo() returns trigger as $$ begin new.t := random()::text; return new; end; $$ language plpgsql; create trigger on_foo_insert before insert on foo for each row execute procedure fixup_foo(); insert into foo values (null); insert into foo values (null); insert into foo values (null); -- 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] Getting FATAL: terminating connection due to administrator command
Tom Lane t...@sss.pgh.pa.us wrote Subject: Re: [GENERAL] Getting FATAL: terminating connection due to administrator command Date: 15.09.2010 17:40 Peter Hopfgartner peter.hopfgart...@r3-gis.com writes: Tom Lane t...@sss.pgh.pa.us wrote This is a result of something sending SIGTERM to the backend process. Can I trace where the SIGTERM comes from? If this is a recent Red-Hat-based release, I think that systemtap could probably be used to determine that. There's a script here that solves a related problem: http://sourceware.org/systemtap/examples/process/sigmon.stp regards, tom lane As it seems I need to recompile the kernel in order to have the debug infos, http://sourceware.org/systemtap/wiki/SystemTapOnCentOS. Tomorrow I will start the probes. Thanks, Peter -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] searchpath reverts to default after each server restart
When i run the command SET search_path TO custom,idsystems, clientdata, configs, replication, structure, archive; then when I run SHOW search_path; it does show those schemas as the search_path. however when i restart the postgresql service, the search_path has reverted to $User, public. when i put the SET search_path function in the postgresql.conf file (as below) the service wouldn't restart. i think because it doesn't like the public schema (which i don't use). #[postgresql.conf file:] # the postgresql service wouldn't start with this setting. # CLIENT CONNECTION DEFAULTS #--- --- # - Statement Behavior - search_path = custom,idsystems,clientdata,configs,replication,structure,archive,public; #schema names #default_tablespace = ''# a tablespace name, '' uses the default #temp_tablespaces = ''# a list of tablespace names, '' uses # only -- 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] searchpath reverts to default after each server restart
On Wed, Sep 15, 2010 at 4:12 PM, Jonathan Brinkman jonathanbrink...@yahoo.com wrote: When i run the command SET search_path TO custom,idsystems, clientdata, configs, replication, structure, archive; then when I run SHOW search_path; it does show those schemas as the search_path. however when i restart the postgresql service, the search_path has reverted to $User, public. when i put the SET search_path function in the postgresql.conf file (as below) the service wouldn't restart. i think because it doesn't like the public schema (which i don't use). #[postgresql.conf file:] # the postgresql service wouldn't start with this setting. # CLIENT CONNECTION DEFAULTS #--- --- # - Statement Behavior - search_path = custom,idsystems,clientdata,configs,replication,structure,archive,public; the path needs to be single quoted. 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] searchpath reverts to default after each server restart
alter database MYDATABASE SET search_path TO custom, clientdata, configs, replication, structure, archive; that seems to fix it. Thank you! -Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: Wednesday, September 15, 2010 4:40 PM To: Jonathan Brinkman Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] searchpath reverts to default after each server restart On Wed, Sep 15, 2010 at 4:12 PM, Jonathan Brinkman jonathanbrink...@yahoo.com wrote: When i run the command SET search_path TO custom,idsystems, clientdata, configs, replication, structure, archive; then when I run SHOW search_path; it does show those schemas as the search_path. however when i restart the postgresql service, the search_path has reverted to $User, public. when i put the SET search_path function in the postgresql.conf file (as below) the service wouldn't restart. i think because it doesn't like the public schema (which i don't use). #[postgresql.conf file:] # the postgresql service wouldn't start with this setting. # CLIENT CONNECTION DEFAULTS #--- --- # - Statement Behavior - search_path = custom,idsystems,clientdata,configs,replication,structure,archive,public; the path needs to be single quoted. 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] Using libpq, floats and binary data
Hi,
Inserting many of rows is almost always IO bound. Converting ints and floats
to text is CPU bound and really fast anyway. To speed things up first look
at things like indexes, how often you need to COMMIT or using COPY. Only
then look at prepared statements and binary transfer modes. Else it's simply
not worth the headache.
Groeten, Arjen
On Wed, Sep 15, 2010 at 6:02 PM, Michael Hull mikehul...@googlemail.comwrote:
Hi Guys,
I have jsut started to use the libpq interface to postgre, and was
wondering if someone with some experience could explain the 'best
practices' :)
I am preparing a query, then submitting it with binary data. 6 values
are ints, and one is a float.
For the ints, I understand that since I need network byte ordering, I
have to pump them through htonl()
The same is also true for the floats I assume, so I have dones some
dirty,dirty casting, but this feels like a source for problems later
on.
It does the correct thing, but is there a better way of doing it??
Mike
char qString[] = INSERT INTO raw_results (ca,kf,ks,lk,na,iinj,
AP1height) VALUES( $1::int4, $2::int4, $3::int4, $4::int4, $5::int4,
$6::int4, $7::float4);
PGresult* pPreparedQuery = PQprepare(pDB, InsertQ,qString, nParams,
NULL);
float AP1Height = 3.141;
/* TROUBLE AHEAD! */
if( sizeof( float) != sizeof(int32_t) ) throw MyException();
int32_t AP_int = * ( (int32_t*) AP1Height );
int32_t AP_intSwapped = htonl(AP_int);
float AP1HeightSwapped = *( (float*) AP_intSwapped);
/* TROUBLE OVER */
int32_t ca= htonl(100);
int32_t kf= htonl(200);
int32_t ks= htonl(300);
int32_t lk= htonl(400);
int32_t na= htonl(500);
int32_t iinj= htonl(600);
const char* values[nParams] = {
(char*)ca,
(char*)kf,
(char*)ks,
(char*)lk,
(char*)lk,
(char*)iinj,
(char*)AP1HeightSwapped,
};
int lengths[nParams] = {
sizeof(ca),
sizeof(kf),
sizeof(ks),
sizeof(lk),
sizeof(na),
sizeof(iinj),
sizeof(AP1Height),
};
int binary[nParams]={1,1,1,1,1, 1,1};
PGresult* res = PQexecPrepared( pDB, InsertQ, nParams, values,
lengths,binary,0);
--
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] Using libpq, floats and binary data
On Wed, Sep 15, 2010 at 6:18 PM, Arjen Nienhuis a.g.nienh...@gmail.com wrote: Hi, Inserting many of rows is almost always IO bound. Converting ints and floats to text is CPU bound and really fast anyway. To speed things up first look at things like indexes, how often you need to COMMIT or using COPY. Only then look at prepared statements and binary transfer modes. Else it's simply not worth the headache. That's an awfully broad statement, and untrue...many applications are cpu bound. It's easier to scale storage than cpu after a point. Also, solid state storage is going to become increasingly common moving forwards. Not all type receiving parsing is trivial as you claim; timestamps and bytea for example are significantly cheaper to send in binary wire format. Anyways, libpqtypes gives you all the advantages without all the fuss. If you are really looking to shave cycles we allow you to prepare the format string as well as prepare the statement before sending it. We wrote this interface for a reason: I'd say on average it cuts down query time around 20% on average in addition to the other advantages it provides. 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] Using libpq, floats and binary data
On Thu, Sep 16, 2010 at 12:37 AM, Merlin Moncure mmonc...@gmail.com wrote: On Wed, Sep 15, 2010 at 6:18 PM, Arjen Nienhuis a.g.nienh...@gmail.com wrote: Hi, Inserting many of rows is almost always IO bound. Converting ints and floats to text is CPU bound and really fast anyway. To speed things up first look at things like indexes, how often you need to COMMIT or using COPY. Only then look at prepared statements and binary transfer modes. Else it's simply not worth the headache. That's an awfully broad statement, and untrue...many applications are cpu bound. It's easier to scale storage than cpu after a point. Also, solid state storage is going to become increasingly common moving forwards. Not all type receiving parsing is trivial as you claim; timestamps and bytea for example are significantly cheaper to send in binary wire format. Anyways, libpqtypes gives you all the advantages without all the fuss. If you are really looking to shave cycles we allow you to prepare the format string as well as prepare the statement before sending it. We wrote this interface for a reason: I'd say on average it cuts down query time around 20% on average in addition to the other advantages it provides. merlin I think we can agree on one thing: trying to do it without libpqtypes is a bad idea. Groeten, Arjen
Re: [GENERAL] help with error unexpected pageaddr
Shouldn't have, the only thing we did to the server was restart it and run our database queries. Clearing out all the wal files from pg_xlog along with a new base backup did fix it though. Thanks for the help Tom! Scot Kreienkamp skre...@la-z-boy.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] Post Install / Secure PostgreSQL
On 16/09/2010 2:10 AM, Carlos Mennens wrote: postgres=# SELECT * FROM pg_user; Doesn't that show I'm connected to the 'postgres' database and there is a table called 'pg_user' which holds all my PostgreSQL user info? That doesn't make sense to me if the database is empty unless I am missing something here. The only way I knew 'pg_user' was available was because I ran the command '\dS'. pg_user is in the pg_catalog schema. The pg_catalog schema is not on the default search_path so it is not shown by default in \dt etc. Again, see the manual: http://www.postgresql.org/docs/current/interactive/ddl-schemas.html Since the pg_catalog schema is in all databases automatically, when we say a database is empty what we really mean is that it contains only the pg_catalog schema, a default public schema with no relations in it, and no other schema. It might help to think about the pg_catalog schema as somewhat like the mysql database in MySQL in that it contains system information and is used to control the database system's operation. However, unlike the mysql database, you should never, EVER alter or edit it directly unless you *really* know what you are doing. Always use the built-in ALTER/CREATE/DROP commands. I cannot recommend reading the PostgreSQL manual enough. It's pretty comprehensive, well written (IMO) and will teach you about SQL in general as well as PostgreSQL in particular. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Getting FATAL: terminating connection due to administrator command
Hi - peter.hopfgartner wrote: http://sourceware.org/systemtap/examples/process/sigmon.stp As it seems I need to recompile the kernel in order to have the debug infos, http://sourceware.org/systemtap/wiki/SystemTapOnCentOS. Tomorrow I will start the probes. Check first on http://debuginfo.centos.org/; they may have saved the goods for your particular kernel build. (version/architecture?) It may be also possible to craft a similar script that doesn't rely on debuginfo. - FChE -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need magic for identifieing double adresses
Hi, I need to clean up a lot of contact data because of a merge of customer lists that used to be kept separate. I allready know that there are double entries within the lists and they do overlap, too. Relevant fields could be name, street, zip, city, phone Is there a way to do something like this with postgresql ? I fear this will need still a lot of manual sorting and searching even when potential peers get automatically identified. Regards Andreas -- 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] Need magic for identifieing double adresses
Andreas wrote: I need to clean up a lot of contact data because of a merge of customer lists that used to be kept separate. I allready know that there are double entries within the lists and they do overlap, too. Relevant fields could be name, street, zip, city, phone Is there a way to do something like this with postgresql ? Yes, yes there is. GROUP BY is your friend and there are other friends too. -- Darren Duncan -- 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] Need magic for identifieing double adresses
Andreas, Relevant fields could be name, street, zip, city, phone Is there a way to do something like this with postgresql ? I fear this will need still a lot of manual sorting and searching even when potential peers get automatically identified. One of the techniques I use to increase the odds of detecting duplicates is to trim each column, remove all internal whitespace, coalesce it into a single string, and calculate an MD5 (some other hash function may be better) hash. It's not perfect (we are dealing with humans, after all), but it helps. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
