Re: [GENERAL] Running postgresql as a VMware ESx client
Glen Eustace wrote: Hi all, I was wondering whether anyone has had any experience running postgresql in a vm under ESx. VMware provides significant HA/DR oppurtunities and we would like to use it if we can. The DBase would be on a EMC SAN hosted LUN and the ESx servers would be dual Quad CPU HP DL-380/G5s. At this stage we would use iSCSI for SAN connectivity as our testing with MS-SQL has not indicated that FC is needed. We are getting a bit of push back from the external support agency who seem more than a little bit nervous about the environment. I would appreciate any comments. I've done that a number of times, never had any problems. As has been mentioned elsewhere in the thread, for low or medium load databases of course. Just be sure to mount the PostgreSQL filesystem(s) (both xlog and data, if they are separate) directly to the SAN, and *don't* use the vmfs files on the host disk. You *can* use vmfs files, but they need to be specially configured IIRC to disable write caching, and will not perform very well. But if you use a directly mounted SAN volume, that issue goes away. And obviously you need someone in your organization that knows vmware ESX well (not just the point-click-installed level) to make sure things are set up in a reliable way for databases. //Magnus -- 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] Password and Installation
Hi Andrew, On Mon, Nov 24, 2008 at 12:10 AM, Andrew Maeng [EMAIL PROTECTED] wrote: Thanks Dave. I can't seem to find the SQL user in the user accounts though. All i can see is the asp.net machine account. Look for a user called 'postgres', not SQL. I'm guessing that this means that PostgreSQL is uninstalled, but I'm still unable to install PostgreSQL because I'm putting in the wrong password. The uninstaller doesn't remove the postgres user account because it doesn't have any way of knowing if you're using it for other tools or different versions of PostgreSQL. If the installer is reporting that the password is incorrect, that's because there's an existing account and Windows is telling us the password is wrong. If you can't find the account for whatever reason, another way of removing it is to use the command line tools. From a command prompt with administrator privileges, try: net user postgres /delete It *should* be shown in the computer management applet though - but the user accounts tool in Control Panel will hide service accounts (I assume that applies to Vista as well as XP). Regards, Dave. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] Postgres mail list traffic over time
Bruce Momjian wrote: Magnus Hagander wrote: Bruce Momjian wrote: Ron Mayer wrote: Joshua D. Drake wrote: On Fri, 2008-11-21 at 08:18 -0800, Ron Mayer wrote: Bruce Momjian wrote: Tom Lane wrote: ... harder to keep up with the list traffic; so something is happening that a simple volume count doesn't capture. If measured in bytes of the gzipped mbox it ... Its because we eliminated the -patches mailing list. That's part of it. I've added -patches to the graph at http://0ape.com/postgres_mailinglist_size/ as well as a graph of hackers+patches combined; and it still looks like hackers+patches is quite high in the past 3 months. With hackers+patches it looks like 2002-08 was the biggest month; but the past 3 months still look roughly twice late 2007's numbers. Can someoone graph CVS traffic, showing the historical number of commits and number of changed lines? Ohloh has some graphs, are they detailed enough? http://www.ohloh.net/projects/postgres/analyses/latest I saw that but that only shows total lines, not the number of lines changed, or commits per hour, etc. I've got a database of all our commits with info like: timestamp, author, number of rows added/deleted, number of files modified, which files modified, rows modified in each file. Basically it's data quickly parsed from a git log --stat of HEAD (because it was a whole lot easier to parse the git stuff). It's got about 27,500 commits in it - only the stuff that happened on HEAD, nothing for backbranches. So, if you can be a bit more specific in what you want :) Attached is for example commits per month and lines per month. //Magnus inline: commit1.png -- 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 mail list traffic over time
Magnus Hagander wrote: Bruce Momjian wrote: Magnus Hagander wrote: Bruce Momjian wrote: Ron Mayer wrote: Joshua D. Drake wrote: On Fri, 2008-11-21 at 08:18 -0800, Ron Mayer wrote: Bruce Momjian wrote: Tom Lane wrote: ... harder to keep up with the list traffic; so something is happening that a simple volume count doesn't capture. If measured in bytes of the gzipped mbox it ... Its because we eliminated the -patches mailing list. That's part of it. I've added -patches to the graph at http://0ape.com/postgres_mailinglist_size/ as well as a graph of hackers+patches combined; and it still looks like hackers+patches is quite high in the past 3 months. With hackers+patches it looks like 2002-08 was the biggest month; but the past 3 months still look roughly twice late 2007's numbers. Can someoone graph CVS traffic, showing the historical number of commits and number of changed lines? Ohloh has some graphs, are they detailed enough? http://www.ohloh.net/projects/postgres/analyses/latest I saw that but that only shows total lines, not the number of lines changed, or commits per hour, etc. I've got a database of all our commits with info like: timestamp, author, number of rows added/deleted, number of files modified, which files modified, rows modified in each file. Basically it's data quickly parsed from a git log --stat of HEAD (because it was a whole lot easier to parse the git stuff). It's got about 27,500 commits in it - only the stuff that happened on HEAD, nothing for backbranches. So, if you can be a bit more specific in what you want :) Attached is for example commits per month and lines per month. Here's another one that crudely shows the amount of code vs docs commits (just looking at docs/* vs src/* - clearly very crude) Sent as a separate mail since -general won't accept large mails. //Magnus inline: commit2.png -- 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] Running postgresql as a VMware ESx client
Glen Eustace escribió: Generally speaking, virtualization allows you to take a bunch of low powered servers and make them live in one big box saving money on electricity and management. Generally speaking, database sers are big powerful boxes with lots of hard disks and gigs upon gigs of ram to handle terabytes of data. Those two things seem at odds to me. If one is handling databases with Terabytes of data and 1000s of connections, I would agree. We will be looking at 100s of Megabytes max and possible several hundred connections. A much smaller workload. You're not gonna get several hundred connections on a resource-starved machine. Consider using a pooler (pgbouncer, pgpool), and reducing the number of actual connections to the DB to a very low number of dozens. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Returning schema name with table name
my standard query (adapted to 1mb size) is: Thank you very much. This query shows toast files in a cryptic way: db_owner pg_toast pg_toast_40552_index 1352 kB How to change it so that it shows also relation name whose data pg_toast_40552_index contains? It is not possible to determine from this query output which data is contained in pg_toast_40552_index file. Andrus. -- 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] Returning schema name with table name
it shows all except toast entries. for included values see http://www.postgresql.org/docs/8.3/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE function |pg_total_relation_size|(oid) Andrus schrieb: my standard query (adapted to 1mb size) is: Thank you very much. This query shows toast files in a cryptic way: db_owner pg_toast pg_toast_40552_index 1352 kB How to change it so that it shows also relation name whose data pg_toast_40552_index contains? It is not possible to determine from this query output which data is contained in pg_toast_40552_index file. Andrus. -- Thomas Markus proventis GmbH | Zimmerstr. 79-81 | D-10117 Berlin | Tel +49 (0)30 2936399-22 | Fax -50 | [EMAIL PROTECTED] - Geschäftsführer: Norman Frischmuth | Sitz: Berlin Handelsregister: AG Berlin-Charlottenburg, HR 82917 - Blue Ant-webbasiertes Projektmanagement - aktuelle Termine 2008: http://www.proventis.net/website/live/blueant/veranstaltungen.html begin:vcard fn:Thomas Markus n:Markus;Thomas org:proventis GmbH adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany email;internet:[EMAIL PROTECTED] tel;work:+49 30 29 36 399 22 x-mozilla-html:FALSE url:http://www.proventis.net version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] literal limits in 8.3
On Sun, Nov 23, 2008 at 12:08:30PM -0700, Scott Marlowe wrote: There are no character limits for sql statements in pgsql That's what I thought! However, I've just tried today and am getting some strange results. The strange results are that above a certain length PG says that it's put a string in OK but there's nothing there when I look back afterward. The code I'm tickling this with is: #include stdio.h #include stdlib.h int main(int argc, char ** argv) { int i = 0, x = atoi(argv[1]); char letters[] = 0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef; printf(INSERT INTO test (col) VALUES ('); while (i x*1024*1024) { int n = printf (%s,letters); if (n == EOF) return 1; i += n; } printf (');\n); return 0; } I ran the following in psql first: CREATE TABLE test (col TEXT); Then a series of: ./test 32 | psql ./test 64 | psql ./test 128 | psql the test is a simple: SELECT length(col) FROM test; in psql. I get a count of zero back (and the string equals '') for the strange rows. The execution of test also completes far too quickly when things go strange. One computer (still 8.3.3 I think) goes strange at 256MB and another (8.3.4) goes strange at 512MB. Any idea what's going on? Sam -- 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] Returning schema name with table name
Thomas, it shows all except toast entries. for included values see http://www.postgresql.org/docs/8.3/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE function |pg_total_relation_size|(oid) I'm sorry I was not clear. For my db your query returns row like db_owner pg_toast pg_toast_40552_index 1352 kB It would be nice if query output allows to find which relation contains 1.3 MB toast data. How to change this query by adding column parent which shows parent table name for toast enties? Currently we must find this relation manually from OID (40552) contained in name. Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] hola mundo
hola, soy nuevo en esto de postgre, pero ya tengo bastantes problemas, empecemos por partes, tengo una base e datos que almacena diariamente alrededor de 10 registros , (las trazas de los servicios de la red , ), y tengo una consulta que cuando la mando a ejecutar con php , me dice que excede el tiempo de ejecucion , le cambieese tiempo y ahora explota por la memoria,m lo que necesito es contar cuantas veces aparece cada elemento de la consulta en el resultado Ej: supongan que la consulta devuelve 4,5,8,3,2,4,8 yo quiero obtener cuantas veces aparece el 4, el 5 ... asi hasta el 8, no se si se puede hacer en postgre , creo que haciendolo desde alli ya no explotaria. Muchas gracias por la atencion Saludos Servicio del Grupo de Redes Universidad de Cienfuegos Contacto: [EMAIL PROTECTED]
Re: [GENERAL] literal limits in 8.3
Sam Mason [EMAIL PROTECTED] writes: However, I've just tried today and am getting some strange results. The strange results are that above a certain length PG says that it's put a string in OK but there's nothing there when I look back afterward. I get out of memory complaints from psql when I try your test case. 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] hola mundo
[EMAIL PROTECTED] wrote: hola, soy nuevo en esto de postgre, pero ya tengo bastantes problemas, empecemos por partes, tengo una base e datos que almacena diariamente alrededor de 10 registros , (las trazas de los servicios de la red , ), y tengo una consulta que cuando la mando a ejecutar con php , me dice que excede el tiempo de ejecucion , le cambieese tiempo y ahora explota por la memoria,m lo que necesito es contar cuantas veces aparece cada elemento de la consulta en el resultado Ej: supongan que la consulta devuelve 4,5,8,3,2,4,8 yo quiero obtener cuantas veces aparece el 4, el 5 ... asi hasta el 8, no se si se puede hacer en postgre , creo que haciendolo desde alli ya no explotaria. Hola Para consultas en español utiliza [EMAIL PROTECTED] El idioma en pgsql-general@postgresql.org es el ingles. Contestando a tu pregunta No necesitas seleccionar todos los valores para despues calcular en php cuantas veces aparece cada uno. Utiliza la funcion agregada count() junto con group by (mucho mas rapido). [1] ejemplo: SELECT columna_con_valores, count(*) as cnt FROM tabla GROUP BY columna_con_valores ORDER BY cnt. [1]: http://www.postgresql.org/docs/8.3/interactive/functions-aggregate.html -- Rafael Martinez, [EMAIL PROTECTED] Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -- 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] literal limits in 8.3
On Mon, Nov 24, 2008 at 09:06:14AM -0500, Tom Lane wrote: Sam Mason [EMAIL PROTECTED] writes: However, I've just tried today and am getting some strange results. The strange results are that above a certain length PG says that it's put a string in OK but there's nothing there when I look back afterward. I get out of memory complaints from psql when I try your test case. Hum, strange. It's a normal 32bit Intel Debian system, nothing much special done to increase the kernel/user split or anything like that as far as I remember on this box. If I try with larger sizes it falls over with out of memory, but up until around 755MB (760MB fails) it gives back INSERT 0 1 which I've always read as inserting a row. A select on the table gives this inserted row containing a zero length string. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[Fwd: [Fwd: Re: [GENERAL] return MAX and when it happened]]
I don't understand: is my question not clear, stupid, or you guys just don't like me? ;) Original Message Subject:[Fwd: Re: [GENERAL] return MAX and when it happened] Date: Fri, 21 Nov 2008 08:48:44 -0600 From: Scara Maccai [EMAIL PROTECTED] To: postgresql pgsql-general@postgresql.org Can someone answer me? Or do I have to ask this in the hackers list? I don't get from the docs: do I have to call get_call_result_type(fcinfo, NULL, tupdesc) every time? I mean: the only example I've found about returning Composite Types talks about returning sets as well (34.9.10. Returning Sets). In that example the get_call_result_type call is done once: if (SRF_IS_FIRSTCALL()) { [...] if (get_call_result_type(fcinfo, NULL, tupdesc) != TYPEFUNC_COMPOSITE) [...] } Should I do something like that in my function or that only applies to function returning sets? I'm calling it every time now but I don't know if it's right... -- 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] mail list traffic
Alvaro Herrera wrote: When I saw the manitou-mail.org stuff some days ago I was curious -- how feasible would it be to host our web archives using a database of some sort, instead of the current mbox-based Mhonarc installation we use, which is so full of problems and limitations? One problem I've noticed on archives.postgresql.org is that threads don't cross month boundaries. For example if I'm looking at: http://archives.postgresql.org/pgsql-general/2008-09/msg01003.php , according to the webpage, this message doesn't has references nor follow-up. But actually it's a reply to this one: http://archives.postgresql.org/pgsql-general/2008-05/msg00404.php and it has this followup: http://archives.postgresql.org/pgsql-general/2008-10/msg00466.php In fact it looks like all threads are cut at the end of each month, and that everything is partitioned by month anyway. I guess it's because mhonarc operates only on the current month by design, which makes sense if its storage doesn't scale. What manitou-mail could provide here is the database structure and the scripts that feed the live archive, and it wouldn't have these limitations of mhonarc. As a bonus, it opens up the data to SQL interfaces, so you can think of querying messages using complex criteria, or producing statistics, reports... But it doesn't provide the generation of webpages that is after all the whole point of this web archive. I assume that the idea is to generate everything in static pages like mhonarc seems to do rather than live-querying the database. Anyway that HTML generation part would need to be recreated or changed to deal with a different data source and a different partitioning of data, if it's modular enough that such a thing is possible. How hard would that be? Personally I have no idea, anyone who is familiar with that code? Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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] mail list traffic
On Sun, Nov 23, 2008 at 11:31 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: Daniel Verite wrote: Gregory Stark wrote: I would be curious to see the average lifespan of threads over time. I happen to have the mail archives stored in a database, [...] When I saw the manitou-mail.org stuff some days ago I was curious -- how feasible would it be to host our web archives using a database of some sort, instead of the current mbox-based Mhonarc installation we use, which is so full of problems and limitations? Didn't I send you a copy of the prototype code I'd written to do that? The biggest issue for third party code is that we need to preserve our existing URLs. /D -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] literal limits in 8.3
Sam Mason [EMAIL PROTECTED] writes: It's a normal 32bit Intel Debian system, nothing much special done to increase the kernel/user split or anything like that as far as I remember on this box. If I try with larger sizes it falls over with out of memory, but up until around 755MB (760MB fails) it gives back INSERT 0 1 which I've always read as inserting a row. A select on the table gives this inserted row containing a zero length string. Well, I can't reproduce that here. Something strange about your configuration maybe? 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] filter statements in logs
Hi, A client has a web system that uses ADODB for php, and that driver is executing select version(), SET DATESTYLE TO 'ISO' and at least one or two more statements a *lot* of times (almost 100 times in 3 hours, and this is just select version()), i tried to understand why but it seems it is for knowing the correct way of looking in catalogs. But this is just noise in the logs. And when i try to use pgFouine to analyze logs it shouts because of the size of them, almost 450Mb in a few hours. My question: is there a way to avoid logging some predefined statements? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] literal limits in 8.3
On Mon, Nov 24, 2008 at 10:45:42AM -0500, Tom Lane wrote: Sam Mason [EMAIL PROTECTED] writes: It's a normal 32bit Intel Debian system, nothing much special done to increase the kernel/user split or anything like that as far as I remember on this box. If I try with larger sizes it falls over with out of memory, but up until around 755MB (760MB fails) it gives back INSERT 0 1 which I've always read as inserting a row. A select on the table gives this inserted row containing a zero length string. Well, I can't reproduce that here. Something strange about your configuration maybe? Not that I know of. I've just created a test cluster to make sure and I get the same behaviour. Minimal set of commands are: LANG=C /usr/lib/postgresql/8.3/bin/initdb pg83 vi pg83/postgresql.conf changed port to 5444 to prevent conflicts, everything else default. /usr/lib/postgresql/8.3/bin/postgres -D pg83 -k /tmp in a seperate shell: echo create database smason; | psql -p 5444 -h /tmp template1 echo create table test ( col text ); | psql -p 5444 -h /tmp ./test 64 | psql -p 5444 -h /tmp results in: INSERT 0 1 Time: 3354.269 ms ./test 512 | psql -p 5444 -h /tmp results in: INSERT 0 1 Time: 50.452 ms echo select length(col) from test; | psql -p 5444 -h /tmp results in: length -- 67108864 0 Anything else to try? All PG packages are from debian backports with version 8.3.4-2~bpo40+1, anybody else getting this? Hum, maybe I should try building from source and see if it's something to do with the Debian packages. [/me twiddles thumbs while code builds] ... done ... I get the same result for both 8.3.4 and 8.3.5. I'm kind of stuck for ideas now! Sam -- 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] filter statements in logs
On Mon, Nov 24, 2008 at 5:04 PM, Scott Marlowe [EMAIL PROTECTED]wrote: postgres -D ... | grep -v things I don't wanna see no more| grep -v another thing I don't wanna see no more| rotatelogs filename 86400 or: grep -Ev I don't wanna see you no more|and you too|and your cat too ;) -- GJ
Re: [GENERAL] literal limits in 8.3
Sam Mason [EMAIL PROTECTED] writes: On Mon, Nov 24, 2008 at 10:45:42AM -0500, Tom Lane wrote: Well, I can't reproduce that here. Something strange about your configuration maybe? Not that I know of. I've just created a test cluster to make sure and I get the same behaviour. Hmm ... the third machine I tried was able to reproduce the problem. What it boils down to is lack of error checking in psql (not the backend). Specifically, we fail to enlarge the output buffer for psqlscan.l, which causes appendBinaryPQExpBuffer to silently not insert the chunk it's currently being passed. Which you might think would be some random subset of the input string, leading to a parse error on the backend side --- but no, this is the output of a lexical scan which means what is dropped is exactly the contents of the multi-megabyte string literal, not less or more. And then later insertions work fine since *they* aren't provoking an out-of-memory problem. So eventually the backend receives INSERT INTO test (col) VALUES (''); which of course it finds nothing wrong with. This is sort of a PITA to fix :-(. The easiest solution from the point of view of psql would be to have realloc failure just print out of memory and exit(1), but pqexpbuffer.c is part of libpq and so it's not too reasonable for it to do that. And we have also got to think about the prospect of similarly non-random lossage in other uses of PQexpbuffer, anyhow. The least API-damaging solution I can think of is to add an error indicator flag to PQexpbuffer, comparable to ferror() on stdio files. Callers would have to check this after loading up a PQexpbuffer if they wanted to be sure there was no memory overrun. But that seems pretty fragile, and it wouldn't be back-patchable. A variant on that is to clear the buffer and insert out of memory in this scenario, but that's not too pleasant either. Better ideas anyone? 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] literal limits in 8.3
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Hmm ... the third machine I tried was able to reproduce the problem. What it boils down to is lack of error checking in psql (not the backend). What is it about certain boxes that causes the failure, but not on others? - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200811241506 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkkrCZsACgkQvJuQZxSWSsgHuwCeJmMj9oRxKP5uQ+DA5KNvCnzO QbIAoJtEzOpT8Bi63Z/yvoAMtHpJdcfF =Hh+m -END PGP SIGNATURE- -- 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] filter statements in logs
On Mon, Nov 24, 2008 at 9:43 AM, Jaime Casanova [EMAIL PROTECTED] wrote: Hi, A client has a web system that uses ADODB for php, and that driver is executing select version(), SET DATESTYLE TO 'ISO' and at least one or two more statements a *lot* of times (almost 100 times in 3 hours, and this is just select version()), i tried to understand why but it seems it is for knowing the correct way of looking in catalogs. But this is just noise in the logs. And when i try to use pgFouine to analyze logs it shouts because of the size of them, almost 450Mb in a few hours. My question: is there a way to avoid logging some predefined statements? The only way I can think of is to use apache's log rotator and when you setup the rotation do something like: postgres -D ... | grep -v things I don't wanna see no more| grep -v another thing I don't wanna see no more| rotatelogs filename 86400 -- 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] literal limits in 8.3
Greg Sabino Mullane [EMAIL PROTECTED] writes: What it boils down to is lack of error checking in psql (not the backend). What is it about certain boxes that causes the failure, but not on others? It's a matter of having the out-of-memory condition occur just at the wrong step, ie, the output from psql's lexical scan (as opposed to the input, or when trying to construct the Query message to send to the backend). So it would depend on factors like 32-vs-64-bit and what you had ulimit set to. 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] [ADMIN] PgAgent Job Scehduler is NOT running
What is the error message you are getting. What is the interval you have set for scheduling the job. Date: Sun, 23 Nov 2008 23:51:46 -0800From: [EMAIL PROTECTED]: [ADMIN] PgAgent Job Scehduler is NOT runningTo: [EMAIL PROTECTED]: [EMAIL PROTECTED] Dear all,I installed PgAgent and started its service and successfully scheduled a backup and got 100% result. Now the same Job is not working even I reinstalled PgAgent but failed to get result. Regards,Abdul Rehman. _ Register once and play all contests. Increase your scores with bonus credits for logging in daily on MSN. http://specials.msn.co.in/msncontest/index.aspx
Re: [GENERAL] [ADMIN] PgAgent Job Scehduler is NOT running
No error message appeared. Because statistics is available for the job. It reflects that it is not running. From: Vishal Arora [EMAIL PROTECTED] To: [EMAIL PROTECTED]; pgsql-general@postgresql.org Cc: [EMAIL PROTECTED] Sent: Tuesday, November 25, 2008 11:47:33 AM Subject: RE: [ADMIN] PgAgent Job Scehduler is NOT running What is the error message you are getting. What is the interval you have set for scheduling the job. Date: Sun, 23 Nov 2008 23:51:46 -0800 From: [EMAIL PROTECTED] Subject: [ADMIN] PgAgent Job Scehduler is NOT running To: pgsql-general@postgresql.org CC: [EMAIL PROTECTED] Dear all, I installed PgAgent and started its service and successfully scheduled a backup and got 100% result. Now the same Job is not working even I reinstalled PgAgent but failed to get result. Regards, Abdul Rehman. Team India gets set to thwart Australia's quest for the final frontier. Catch the action on MSN Try it now!
[GENERAL] Serial/sequence problem
I have just imported 3636 rows into a PG database table (PG 8.1 on CentOS 5.2 ... the default). The rows were imported using separate INSERT statements for each row. All OK so far. After having had a few attempts at inserting new test rows (which all inserted OK), I notice that the last_value count in the sequence for this table is only 9. I was expecting something above 3636 of course. So now I'm getting duplicate serial numbers in my SERIAL id field. It appears to be not possible to manually update the last_count value in the sequence table (currently 9, though the table contains 3636+9 rows), so I'm wondering: - what did I do wrong to arrive in this situation? - how can I rectify the situation? Thanks Mick -- 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] [ADMIN] PgAgent Job Scehduler is NOT running
No error message appeared. Because NOstatistics is available for the job. It reflects that it is not running. - Forwarded Message From: Vishal Arora [EMAIL PROTECTED] To: [EMAIL PROTECTED]; pgsql-general@postgresql.org Cc: [EMAIL PROTECTED] Sent: Tuesday, November 25, 2008 11:47:33 AM Subject: RE: [ADMIN] PgAgent Job Scehduler is NOT running What is the error message you are getting. What is the interval you have set for scheduling the job. Date: Sun, 23 Nov 2008 23:51:46 -0800 From: [EMAIL PROTECTED] Subject: [ADMIN] PgAgent Job Scehduler is NOT running To: pgsql-general@postgresql.org CC: [EMAIL PROTECTED] Dear all, I installed PgAgent and started its service and successfully scheduled a backup and got 100% result. Now the same Job is not working even I reinstalled PgAgent but failed to get result. Regards, Abdul Rehman. Team India gets set to thwart Australia's quest for the final frontier. Catch the action on MSN Try it now!
Re: [GENERAL] Serial/sequence problem
am Tue, dem 25.11.2008, um 16:41:43 +0930 mailte Mike Hall folgendes: I have just imported 3636 rows into a PG database table (PG 8.1 on CentOS 5.2 ... the default). The rows were imported using separate INSERT statements for each row. All OK so far. After having had a few attempts at inserting new test rows (which all inserted OK), I notice that the last_value count in the sequence for this table is only 9. I was expecting something above 3636 of course. So now I'm getting duplicate serial numbers in my SERIAL id field. It appears to be not possible to manually update the last_count value in the sequence table (currently 9, though the table contains 3636+9 rows), so I'm wondering: - what did I do wrong to arrive in this situation? The 3636 INSERT-Statements contains a fix value for the ID-Column, right? Thats wrong, omit the id-column and value or use simply 'default' for it. - how can I rectify the situation? Set the sequence to the new value, 3636+9, via setval('your_sequence', 3636+9) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Place of subselect
Hi dear Postgres users. I have performance issues if I do the following pseudo-query: SELECT a, b, (SELECT count(*) FROM t2 WHERE something) AS c FROM t1 ORDER BY a LIMIT 10; After some tests, it seems to me that the subquery on t2 is computed for all rows of t1. As I don't ORDER BY c, there is no need to compute c for every row. I know I can (or should ?) work with joins or with a subquery in the from clause, but I'd like to make sure there is no other way before changing my sqls. A subjective reason for me to prefer subqueries in fields instead of joins of sub tables is that, when it only relates to the text displayed, it is easyer to read and to change, and I mess less with agregate functions. Thanks.
Re: [GENERAL] Place of subselect
am Tue, dem 25.11.2008, um 15:34:57 +0800 mailte Guillaume Bog folgendes: Hi dear Postgres users. I have performance issues if I do the following pseudo-query: SELECT a, b, (SELECT count(*) FROM t2 WHERE something) AS c FROM t1 ORDER BY a LIMIT 10; After some tests, it seems to me that the subquery on t2 is computed for all rows of t1. As I don't ORDER BY c, there is no need to compute c for every row. I know I can (or should ?) work with joins or with a subquery in the from clause, but I'd like to make sure there is no other way before changing my sqls. Please check your presumption with explain analyse your query. For example: test=*# explain analyse select t1.*, (select count(1) from t2) from t1 order by 1 limit 5; QUERY PLAN -- Limit (cost=186.54..186.55 rows=5 width=4) (actual time=0.087..0.104 rows=3 loops=1) InitPlan - Aggregate (cost=36.75..36.76 rows=1 width=0) (actual time=0.022..0.024 rows=1 loops=1) - Seq Scan on t2 (cost=0.00..31.40 rows=2140 width=0) (actual time=0.004..0.008 rows=1 loops=1) - Sort (cost=149.78..155.13 rows=2140 width=4) (actual time=0.082..0.088 rows=3 loops=1) Sort Key: i - Seq Scan on t1 (cost=0.00..31.40 rows=2140 width=4) (actual time=0.046..0.056 rows=3 loops=1) Total runtime: 0.197 ms (8 rows) Both tables executes only one scan. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general