Re: [GENERAL] Urgent Order
On Saturday 07 May 2011, John R Pierce elucidated thus: > On 05/07/11 6:08 AM, Bob Wilson wrote: > > Hello > > This is Bob and I will like to order ( Indexing Table )Do get back > > to me with the types and cost for the ones you do carry and let me > > know if there is an extra cost when using visa or master > > Card.Kindly get back to me with your name Are you the sales > > manager or the Owner? > > * smallint - $2 > * integer - $4 > * bigint - $8 > * varchar - $1/character > * boolean - $1 each > * bytea - $1/byte > * date - $6 > * timestamp - $8 > * ... http://www.google.com/search?q=bob+wilson+urgent+order Makes for some amusing reading. 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] postgre on virtual machine
On Wednesday 20 October 2010, John R Pierce elucidated thus: > On 10/20/10 3:46 AM, Georgi Ivanov wrote: > > Hi, > > Is there any specific reason one should/should not run postgre on > > virtual machine for production ? > > Is there any special tuning for virtual environment ? > > Client of mine is running 8.4 on virtual machine (VMWare ) so I'm > > wandering if there is anything special I can tune to speed up a > > bit. Also if I must choose between more RAM and more virtual cores > > what should I choose ? > > the biggest issue tends to be disk IO performance, especially in a > transactional intensive database. virtualized storage is just not as > fast as native storage. the big iron folks deal with this by > dedicating fiberchannel cards to each virtual machine that needs IO > performance. of course, this gets expensive. > > as far as memory vs cores goes, that depends a lot on the nature of > your access patterns. larger databases benefit from more memory for > caching & buffers, while higher levels of concurrent connections > benefit from more CPU cores (*and* more memory). Would using a raw partition in the VM alleviate some of the issues here? 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] Strange phenomenon
On Friday 15 October 2010, Scott Marlowe elucidated thus: > On Fri, Oct 15, 2010 at 12:37 PM, Tom Lane wrote: > > I'd look around for a cron job or some other periodic task that > > thinks it's supposed to reload the database or something like that. > > Postgres doesn't forget stuff that easily ... unless it's told to. > > Had a search engine eat an entire database one night by clicking on > the all the "delete" links. And that was the night you learned about this part of RFC 2616 (HTTP 1.1), right? :) "In particular, the convention has been established that the GET and HEAD methods SHOULD NOT have the significance of taking an action other than retrieval...Methods can also have the property of "idempotence" in that (aside from error or expiration issues) the side-effects of N > 0 identical requests is the same as for a single request. The methods GET, HEAD, PUT and DELETE share this property." :) -- 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] Kudos on the 64 bit PostgreSQL for Windows
On Thursday 30 September 2010, Dann Corbit elucidated thus: > 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! This is a bit OT, but were you using transactions in SQLite? SQLite can deliver several thousand inserts per second if inside of a transaction. If you were only getting a few inserts per second, then you were not using transactions, thus SQLite was on "autocommit" mode, and thus committing after every insert, thus the performance penalty. 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] versioned pl/pgsql functions
On Tuesday 21 September 2010, Dmitriy Igrishin elucidated thus: > Hey all, > > After ten years with stored procedures I am thinking so this is not > > > too well technique. Much better is writing stored procedures to a > > file and using usual tools for file's versioning. We did some tools > > for storing a versions inside database, but still we prefer a > > standard developer tools - vi or emacs like editor and plain text. > > The main problem of procedures maintained inside database is loss > > of outer formatting and outer comments. And you cannot to group > > procedures to modules. On the other hand - some history can be > > useful for all database objects - not only for stored procedures. > > Agree with Pavel. At the danger of sounding like a "me too:" Agree with Pavel. Keep version control in version control. Make creation/replacement of stored procedures part of your deployment system. 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] trying to use libpq in Ubuntu
On Tuesday 21 September 2010, Henri De Feraudy elucidated thus:
> Hello,
> I have developed an application in C++ under Qt in Windows XP, now I
> would like to port it to Ubuntu GNU/Linux.
> Choosing the libraries in Windows was a piece of cake: the bottom of
> my Qt qmake project file was
> win32 {
> LIBS += "C:\Progra~1\PostgreSQL\8.4\lib\libpq.lib"
> INCLUDEPATH += "C:\Progra~1\PostgreSQL\8.4\include"
> }
> linux-g++ {
> LIBS += /usr/lib/libpq.a
> INCLUDEPATH += /usr/include/postgresql
> }
>
> This works fine in Windows, but in Ubuntu there are more than a
> hundred undefined symbols, like references to ldap
> Does anyone have a sample project file or suggestions?
If you are using Qt, why not use the Qt database abstraction classes?
That will save you having to compile directly against the Pg libraries.
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] missing chunk number 497 for toast value 504723663
On Friday 17 September 2010, Tom Lane elucidated thus: > "Joshua J. Kugler" writes: > > On Thursday 16 September 2010, Tom Lane elucidated thus: > >> Update. Whatever reasons you might have for running 7.3.2 are bad > >> ones. > > > > Disclaimer: I agree with Tom; running 7.3.2 is a bad idea. > > > > That said: like he said, he can't. He's running RHEL 4.0. > > Presumably he is on a support contract, so moving to non-system > > software means he no longer has vendor support and upgrades for the > > packages installed on his system. Pg 7.3.x is what came with RHEL > > 4. > > No, it wasn't. Red Hat shipped 7.4.x on RHEL-4, and the current > package there is 7.4.29. Red Hat did ship 7.3.x on RHEL-3, and the > current package there is 7.3.21 + several back-ported patches. 7.3.2 > hasn't been current on any Red Hat distro since 2003. I know because > I do the work. > > If he is depending on a third party vendor that can't be bothered to > update past 7.3.2, he needs to find a less incompetent vendor. > Pronto, before he loses more data to their incompetence. I apologize. I went to look at packages, and must have seen the '7' and it didn't click that it was 7.4. I must have scanned too fast. So yes, that is *VERY* weird that he is running RHEL 4, but only Pg 7.3. 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] missing chunk number 497 for toast value 504723663
On Thursday 16 September 2010, Tom Lane elucidated thus: > "Utsav Turray" writes: > > I am using postgres 7.3.2 on RHEL 4.0. > > Egad. > > > Secondly what are probable reasons behind corruption and what can > > we do to prevent this error. > > Update. Whatever reasons you might have for running 7.3.2 are bad > ones. Disclaimer: I agree with Tom; running 7.3.2 is a bad idea. That said: like he said, he can't. He's running RHEL 4.0. Presumably he is on a support contract, so moving to non-system software means he no longer has vendor support and upgrades for the packages installed on his system. Pg 7.3.x is what came with RHEL 4. Considering RHEL 3 will EOL (finally) at the end of October, RH is going to be supporting Pg 7.3 for quite a while. Hopefully they'll back port security fixes. 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] 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] "private" installation of postgres
On Tuesday 07 September 2010, Scott Ribe elucidated thus: > On Sep 7, 2010, at 7:09 PM, Tom Lane wrote: > > Personally, though, I think it's easier to change the default port > > number and not worry about sharing /tmp. Changing the port is a > > good idea anyway to avoid any possible conflicts on shared memory > > IDs. > > I was planning on changing the default port number (I forgot to state > that in my email), but also moving the socket dir just to be really > sure. If you do a private socket dir, and connect via that, you don't even have to listen on a TCP port at all. 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] "private" installation of postgres
On Tuesday 07 September 2010, John R Pierce elucidated thus: > On 09/07/10 5:15 PM, Scott Ribe wrote: > > Assume I want to install an instance of postgres such that it > > cannot interfere with another install, or even be visible to other > > apps. Is this all I need to do: > > > > - install pg somewhere within my own directory > > - init the db somewhere within my own directory > > - disallow IP connections > > - set the socket directory somewhere within my own directory > > > > Or is there a doc on this somewhere? > > I'm pretty sure the socket directory is hard coded in the source, I > don't think you can override it even with ./configure postgresql.conf: unix_socket_directory = '/var/run/postgresql' 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] MySQL versus Postgres
On Monday 09 August 2010, Joshua D. Drake elucidated thus: > On Mon, 2010-08-09 at 10:09 -0600, Scott Ribe wrote: > > It's not a requirement, just a reasonable default. > > The actual requirement is: > > Thou shall not use a privelaged user, e.g; Administrator or UID = 0. > > Not only is that a reasonable default, MySQL is broken because of > theirs. > > Joshua D. Drake Hmm...I've always seen MySQL run under the user mysql. Of course, mysqld_safe (the script that restarts mysql if it crashes) starts as root, but the actually binary runs as mysql. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com 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] MySQL versus Postgres
On Monday 09 August 2010, Randal L. Schwartz elucidated thus: > >>>>> "Joshua" == Joshua J Kugler writes: > > Joshua> I'll add in a "me too" only to say that I am someone that > learns Joshua> best by example. > > Keep in mind though that there are three primary learning modes: > - example > - concept > - structure > > Do not overemphasize the example mode at the cost of presenting > concepts or structure. You need all three. Oh, definitely. I like reading the concepts and structure of how, say, an API works, and use it for reference, but what really cements *how* to use it is those examples. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com 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] MySQL versus Postgres
On Friday 06 August 2010, Scott Marlowe elucidated thus: > >> Everyone of my trainees want such thing - for databases, for other > >> programming-languages etc. It's the worst thing you can give them. > >> The< will copy, they will paste and they will understand nothing. > >> Learning is the way to understanding, not copying. > > > > I couldn't disagree more. Presenting working code (at least > > snippets) should continue to be a fundamental part of any > > documentation project. > > I agree. It's especially useful if you're dealing with folks who > already have a clue, but may not be 100% familiar with how SQL or a > particular language. I had a Perl cookbook back in the day that was > priceless when I was switching from C to Perl. I didn't just copy > and paste, but I did certainly learn a lot looking at other people's > code. I'll add in a "me too" only to say that I am someone that learns best by example. Reading about an API or function call is great, but I'll pick it up faster, and understand it better, if you show me an example of it in use (preferably multiple examples). So, a "getting started" guide and/or cookbook would be great. Another cool idea: a MySQL -> PostgreSQL migration guide (I'm sure there's already one out there) that would show "To do *this* MySQL function in PostgreSQL, use this function/feature/etc." A good example is auto_increment -> serial. A comparison/contrast of permission would be good too. I'm sure others can think of more examples. So, that's my 2c. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com 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] Moving database cluster
On Thursday 14 January 2010, Fernando Hevia elucidated thus: > > The easiest way is to shut down Pg, move the 'main' directory > > somewhere else, and then point a symlink to the new location. > > Thanks for your reply. > I had considered this first but then I wasn't sure if there would be > any performance penalty. > The current main directory sits on a 'slow' RAID 1 volume while the > new one will sit on a 'fast' 12 disk RAID 10 volume. > I guess the symlink shouldn't be troublesome but I don't know if some > PG process will continually be reading this link encumbering somehow > the RAID 1 disks. I'm not sure how Pg does it, but I remember reading several years ago that MySQL would, upon startup, "resolve" the symlink, and use the real path name for the rest of its execution. I would assume Pg does something similar. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0x14EA086E -- 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] Moving database cluster
On Thursday 14 January 2010, Fernando Hevia elucidated thus: > An Ubuntu install creates a postgres cluster automatically on > /var/lib/postgresql/8.4/main > Whats the best procedure for moving this cluster to an other > location? Should I just rerun initdb? What happens then with the > default cluster or how could I delete it? The easiest way is to shut down Pg, move the 'main' directory somewhere else, and then point a symlink to the new location. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0x14EA086E -- 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] Postgres Clustering Options
On Wednesday 11 November 2009, David Kerr said something like: > I'm trying to meet a very high uptime requirement in a high > performance environment. If you don't mind Xen, have you considered: http://dsg.cs.ubc.ca/remus/ System mirroring/hot standby, with instant failover, complete with any open TCP/IP connections, etc. With paravirtualization, the performance hit shouldn't be very big. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0x14EA086E -- 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] Database schema dumper
On Monday 29 June 2009, fe...@crowfix.com said something like: > I'd like to dump a database schema to a file, probably XML but > anything reasonable is good enough. By "schema", I don't mean the > narrow postgres keyword, but rather the table names, columns, > foreignkeys, triggers, constraints, etc. > > I'd really like something that could work for other databases too, > including O-, M, etc. But that might be asking too much. > > A quick google for variations on "dump database schema" didn't find > much. > > Whether it be a CPAN module, Java class, etc, or a standalone > program, none of that matters much. You mentioned CPAN, so I assume you're comfortable with Perl. It's Pg specific, but it's a GREAT utility: http://www.rbt.ca/autodoc/ Does output in HTML, Dot, Dia,, and XML. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE -- 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] Syntax error with select statement
On Wednesday 17 December 2008, justin said something like:
> aravind chandu wrote:
> > Hello,
> > I have problem with select statement in c++ program I am
> > using pqxx library to connect to postgresql database.My query is
> >
> > result R(T.exec(" select * from dbtable where username =
> > ' "+user+" ' and password = ' "+st+" ' "));
> >
> > here st is in encrypted format and the string is st =
> > M^fuo|`sjyo|`so|>-?z this is the string i stored in the table .
> >
> > The error I was encountered is "terminate called after
> > throwing an instance of 'pqxx::syntax_error'
> > what(): ERROR: unterminated quoted string at or near
> > "'M^fuo|`sjyo|`so|>-?z"
> > LINE 1: ...table where username = 'achandana' and password =
> > 'M^fuo|`sj...
> >
> > ^"
> >
> > I am not able to identify what the actual problem is can you guys
> > please help to solve this problem?Your help is greatly appreciated.
> >
> > Thank You,
> > Aravind
>
> Well its telling you in the error the quotes are flaky. It apears
> that the password portion contains another sing quote.
>
> I would move to double dollar quoting when dealing with strings that
> contain special characters
>
> example
> R(T.exec(" select * from dbtable where username = $UserName$ " +
> user + " $Username$ and password = $Password$ " + st + " $Password$
> "));
>
> see http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html
> on dollar quoting
Better yet, use placeholders and let the driver do the quoting.
j
--
Joshua Kugler
Part-Time System Admin/Programmer
http://www.eeinternet.com
PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE
--
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] Other queries locked out during long insert
On Wednesday 17 December 2008, Peter Eisentraut said something like: > Because the TRUNCATE commands are taking out an exclusive lock on the > tables. > > > My understanding of MVCC is that I should be able to query against > > those tables while these insert loops are in their transaction. > > You get the MVCC behavior if you use DELETE instead of TRUNCATE. > TRUNCATE is specifically designed for better speed and less > concurrency. It's your choice. OK, that makes sense. There was nothing on the TRUNCATE page to suggest that TRUNCATE would lock the tables. Maybe an addition to the documentation is in order? Where do I go to suggest that? Thanks again! j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Other queries locked out during long insert
I've read the Pg docs about MVCC and possible locks that indexes can create. Even so, I can't figure out why my code is causing all other queries to block while it is running. I'm reading data in from a file (line by line, its CSV), doing a little pre-processing, and inserting it into a table. I'm dropping the indexes for both speed and to eliminate sources of possible locks. Here is, effectively, what I'm doing: Connect to database MYDATA Begin Drop a couple indexes and constraints Commit Begin Truncate table1, table2 for row in file1: insert into table1 sleep(0.001) # see note below for row in file2: insert into table2 sleep(0.001) # see note below Commit Begin create index on table1 create constraint on table2 Commit During the time where the insert loops are running, I cannot do any queries against table1 and table2. At first I thought it was a performance thing, as without the sleeps postmaster was taking 60% of a CPU (this is an eight core system). After putting in the sleeps, I still can't query against table1 and table2 without the query blocking. Other information: The query that is blocking is a stored procedure in MYDATA, which is nothing but a couple selects. The second insert loop uses a couple ip4r functions (as does the aforementioned stored procedure). My understanding of MVCC is that I should be able to query against those tables while these insert loops are in their transaction. Can someone show me (or point me to docs that show me) the error of my thinking? Thank you! j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
