Re: [GENERAL] Printing values on pgadmin tool window using plperlu
On Sun, May 13, 2007 at 11:51:55PM -0400, Harpreet Dhaliwal wrote: > I have a function written in language plpelu. > Normally, values in perl code are printed using > print statement. > Then same when used in postgres environment won't print messages in > 'Messages' tab to pgadmin query tool window. > How and where can I print these values while running the plperlu function. Use elog(). http://www.postgresql.org/docs/8.2/interactive/plperl-database.html -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] EXCEPTION clause not identified
Hi, In one of my trigger functions, i'm trying to catch invalid ip address exception CREATE OR REPLACE FUNCTION func_client_socket() RETURNS "trigger" AS $BODY$ DECLARE ip_address_present int4; BEGIN ip_address_present = 1; SELECT inet(NEW.canonical_name); EXCEPTION WHEN invalid_text_representation THEN ip_address=0; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; when i run this function, it gives me the followin error ERROR: syntax error at or near "EXCEPTION" at character 1343 which is the line where I have the EXCEPTION clause. Can anyone please tell me whats going wrong here? Thanks, ~Jas
Re: [GENERAL] Postgres Printed Manuals
Robert James wrote: > The Postgres docs are great. Unfortunatelty, at 1600 pages, my > printer is a little weary. Are bound & printed editions available? > If not, has anyone had any good experience printing them via any of > the online services? (The ones I contacted said it was too big for > them to print...) You do have a point, but remember: 1600 pages is 1600 pages, no matter whether you, or somebody else prints them out. That cost would have to be translated out somewhere. For instance, I have an old copy of Linux Programming (2nd edition) that at the time was priced at $40. I'd think reprinting all of those pages would cost a little more. And you also have to ask if there is an audience for that in the first place. I do understand wanting to have a printed manual. In as good as ebooks are, sometimes they don't compete with having the printed version in front of you, so you are not constantly switching between screens. What I would suggest is, rather than hoping somebody has the bankroll for one LARGE document, somebody might take a close look at a PostgreSQL Documentation Project to break down those 1600 pages into three or four "manuals" that would be more reasonable to print one at a time. -- The NCP Revue -- http://www.ncprevue.com/blog ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Printing values on pgadmin tool window using plperlu
Hi I have a function written in language plpelu. Normally, values in perl code are printed using print statement. Then same when used in postgres environment won't print messages in 'Messages' tab to pgadmin query tool window. How and where can I print these values while running the plperlu function. Thanks, ~Harpreet
Re: [GENERAL] Postgres Printed Manuals
The Postgres docs are great. Unfortunatelty, at 1600 pages, my printer is a little weary. Are bound & printed editions available? If not, has anyone had any good experience printing them via any of the online services? (The ones I contacted said it was too big for them to print...) You're right, the page count is just unfeasible. You could try mpage-ing it to 4 pages per sheet and print it out double-sided, you would then have a smaller document that could be bound- the only problem is the text would be so small to be virtually unreadable. I did this with a Common Lisp manual years ago and never refer to it for that reason. A good compromise would be to invest in a PostgreSQL book (best: PostgreSQL by Korry Douglas also good: Practical PostgreSQL by Command Prompt, PostgreSQL: Introduction and Concepts by Momjian) and refer to the Postgres docs for the definitive reference. Cheers, Stuart. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Postgres Printed Manuals
"Robert James" <[EMAIL PROTECTED]> writes: > Also: wouldn't it be a good way for the Postgres project to raise some > money, by publishing and selling bound and printed versions? It's hard to make any money that way :-(. Rich Morin used to run a business called "Prime Time Freeware" that published hardcopy versions of our manuals along with much other open-source documentation. He gave up on it some years ago, though, and I doubt that the market has improved. > I believe MySQL has done that for years. MySQL's situation is a bit different, because they own their documentation lock-stock-and-barrel and have never distributed it freely (check the terms sometime, they're not very liberal). Even so, I'll bet they're not making any noticeable amount of money from selling hardcopy. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Streaming large data into postgres [WORM like applications]
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/12/07 19:49, Dhaval Shah wrote: > Consolidating my responses in one email. > > 1. The total data that is expected is some 1 - 1.5 Tb a day. 75% of > the data comes in a period of 10 hours. Rest 25% comes in the 14 > hours. Of course there are ways to smooth the load patterns, however > the current scenario is as explained. > > 2 I do expect that the customer rolls in something like a NAS/SAN with > Tb of disk space. The idea is to retain the data for a duration and > offload it to tape. 45TB per month Wow. The archival process *must* be considered when designing the system. PostgreSQL's ability to use tablespaces and partitioned tables will make that much easier. Otherwise, you'd have to be deleting from one "side" of the table while inserting into the other "side". Partitioning will also let you divide the table into multiple "active" segments, so that multiple inserters can run simultaneously without stepping on each other while spreading the load across multiple controllers and RAID-sets. If it's a SAN/NAS that is organized into RAID-5 groups, make *sure* that it has *lots* of batter-backed write-back cache. Regarding compression: if the columns are integers or short VARCHAR fields, I do not see how compression can help you, unless you use block-layer compression. Which Linux doesn't do. Does FreeBSD have block-level compression? - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGR8mtS9HxQb37XmcRAjXQAJ9TN2FqU1Wo4PZmS6MAhxaJgCm6/wCfXIl8 wZOYG7vWxwODNaRwDGSJxYQ= =Qh2r -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Postgres Printed Manuals
The Postgres docs are great. Unfortunatelty, at 1600 pages, my printer is a little weary. Are bound & printed editions available? If not, has anyone had any good experience printing them via any of the online services? (The ones I contacted said it was too big for them to print...) Also: wouldn't it be a good way for the Postgres project to raise some money, by publishing and selling bound and printed versions? I believe MySQL has done that for years. It would be a great service to some newcomers also: online is great for reference / lookup, but when I want to read the tutorials/internal docs/etc., there's a limit to how many pages I can read on the screen.
Re: [GENERAL] How access table by tableoid
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > You could possibly do this with a SRF; you'd have to grab the name of > the table and then build a dynamic query off of that. If you cast the > oid to regclass you'll get the name of the table, though you should > probably query pg_class and pg_namespace to build a fully-qualified > table name (schemaname.tablename). The regclass cast will take care of that for you. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How access table by tableoid
On Sun, May 13, 2007 at 09:25:37PM +0200, Felix Kater wrote: > can I use a given tableoid (instead of the tablename) to select > columns from that table somehow? > > SELECT * FROM ??tableoid?? > So, I worked around that by peforming two queries: The first to retrieve > the table's name from pg_class via its OID, the second to select the > wanted columns from that table using the table's name as usual. > > Can I do it in one go using the table??s OID? You could possibly do this with a SRF; you'd have to grab the name of the table and then build a dynamic query off of that. If you cast the oid to regclass you'll get the name of the table, though you should probably query pg_class and pg_namespace to build a fully-qualified table name (schemaname.tablename). -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Internals of PostgreSQL - Documentation or presentations
On Sun, May 13, 2007 at 08:44:48PM +0200, Gerhard Wiesinger wrote: > Are there some presentations or documents of the internals of PostgreSQL > available? > > Especially I'm looking for the concepts and detailed internals of general > transaction handling, internals of commit log, transaction logs, > pg_multixact, pg_subtrans, pg_tblspc and pg_twophase. > > Also some comments about concurrent access with multiple processes, > locking and shared memory concepts (or other communication systems used) > would be nice. > > I already found > http://www.postgresql.org/files/developer/internalpics.pdf > but some comments are missing to understand it well. Best bet is to use the source... src/backend/access/transam/README should get you started. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PITR and tar
Moving to -docs... Does anyone know what the history of the docs saying that GNU tar had issues with files changing underneath it? According to this report it's actually BSD tar that has the issue. On Wed, May 09, 2007 at 10:19:05AM -0700, Jeff Davis wrote: > On Wed, 2007-05-09 at 11:40 -0500, Jim Nasby wrote: > > Actually, looking at the docs, the problem is with some versions of > > GNU tar. AFAIK bsdtar is perfectly happy to archive files that have > > changed from underneath it. > > > > $ tar --version > bsdtar 1.2.53 - libarchive 1.3.1 > > That fails to create a file in proper gzip format when the files are > concurrently modified. > > However, > > $ tar --version > tar (GNU tar) 1.14 > Copyright (C) 2004 Free Software Foundation, Inc. > This program comes with NO WARRANTY, to the extent permitted by law. > You may redistribute it under the terms of the GNU General Public > License; > see the file named COPYING for details. > Written by John Gilmore and Jay Fenlason. > > That _appears_ to work. > > Perhaps FreeBSD users should take notice of this problem. It's certainly > not a postgresql problem, but I know there are a lot of freebsd users > here, and using tar on fast-changing data may be rare outside of > postgresql. > > Regards, > Jeff Davis > -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [UNSURE] Re: [GENERAL] autovacuum
From what you described, I am running with autovacuum. Makes sense to make a good idea default in the distro builds... On May 13, 2007, at 9:49 AM, Michael Fuhr wrote: On Sat, May 12, 2007 at 03:48:14PM -0400, Tom Allison wrote: I noticed that under 8.2 the autovacuum isn't running (logging) every 60s like I'm used to seeing. See the 8.2 Release Notes: http://www.postgresql.org/docs/8.2/interactive/release-8-2.html * Remove routine autovacuum server log entries (Bruce) pg_stat_activity now shows autovacuum activity. In 8.2 the "autovacuum: processing database" messages are logged at DEBUG1; in 8.1 they were logged at LOG. I pretty much just took the defaults in the postgresql.conf file since that's always seemed to work before. Autovacuum was first incorporated into the backend in 8.1 and it's disabled by default in 8.1 and 8.2, at least in source builds (it might be enabled by default in some pre-packaged distributions). What do you have in postgresql.conf for the following settings? autovacuum autovacuum_naptime stats_start_collector stats_row_level log_min_messages Do you see any warnings like the following in the server logs? WARNING: autovacuum not started because of misconfiguration HINT: Enable options "stats_start_collector" and "stats_row_level". I'm not making a lot of changes to the database right now (insert/ update/delete) but I thought I would still get the logging. If you have autovacuum and row-level statistics enabled then autovacuum should be running. I'd guess you aren't seeing the routine messages because they're logged at DEBUG1 and you have log_min_messages at a level that doesn't show debug messages. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to implement GOMONTH function
On Sun, May 13, 2007 at 02:26:09PM -0700, Rodrigo De León wrote: > CREATE OR REPLACE FUNCTION > PUBLIC.GOMONTH(DATE, INTEGER, OUT DATE) IMMUTABLE AS > $_$ > SELECT ($1 + ($2 || 'MONTHS')::INTERVAL)::DATE; > $_$ LANGUAGE SQL It would probably be better to use: SELECT ($1 + ($2 * '1 MONTH'::INTERVAL))::DATE; Less string parsing. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] How to implement GOMONTH function
Andrus ha escrito: > I need to create function GOMONTH which returns date by given number of > month before or forward using sql or pgsql in 8.1+ > For example, > GOMONTH( DATE '20070513', 1 ) should return date '20070613' > GOMONTH( DATE '20070513', -2 ) should return date '20070313' > > I tried > > CREATE OR REPLACE FUNCTION public.gomonth(date, integer, > out date) IMMUTABLE AS > $_$ > SELECT $1 + $2'months'; > $_$ language sql > > but got error > > ERROR: syntax error at or near "'months'" > > How to implement this ? > > Andrus. CREATE OR REPLACE FUNCTION PUBLIC.GOMONTH(DATE, INTEGER, OUT DATE) IMMUTABLE AS $_$ SELECT ($1 + ($2 || 'MONTHS')::INTERVAL)::DATE; $_$ LANGUAGE SQL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] How access table by tableoid
Hi, can I use a given tableoid (instead of the tablename) to select columns from that table somehow? SELECT * FROM ??tableoid?? My first approach was to lauch a subquery of the information_schema like this: SELECT * FROM (SELECT relname FROM pg_class WHERE oid=999) AS tablename However, it turned out that the subquery returns the refered *column* which is the 'relname' column but not the table's name. (With other words: The final result is a single 'relname' column instead of all columns from the tablename inside the relname column.) This is not was I intended. So, I worked around that by peforming two queries: The first to retrieve the table's name from pg_class via its OID, the second to select the wanted columns from that table using the table's name as usual. Can I do it in one go using the table´s OID? Thank You Felix ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Internals of PostgreSQL - Documentation or presentations
Hello! Are there some presentations or documents of the internals of PostgreSQL available? Especially I'm looking for the concepts and detailed internals of general transaction handling, internals of commit log, transaction logs, pg_multixact, pg_subtrans, pg_tblspc and pg_twophase. Also some comments about concurrent access with multiple processes, locking and shared memory concepts (or other communication systems used) would be nice. I already found http://www.postgresql.org/files/developer/internalpics.pdf but some comments are missing to understand it well. Thanx. Ciao, Gerhard -- http://www.wiesinger.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[Re] Re: [GENERAL] Winsock error 10035 while trying to upgrade from 8.0 to 8.2
> [EMAIL PROTECTED] wrote : > Cyril VELTER wrote: > > > > I'm trying to upgrade a pretty big database (60G) from postgres 8.0 to > > postgres 8.2 on windows 2000 Server (both version running on the same machine > > on different ports). During the migration process, I always get an error at > > some point (never the same) : > > Interesting. 10035 is "A non-blocking socket operation could not be > completed immediatly". > Question: Does this error come fromthe 8.0 or the 8.2 server? It comes from the 8.2 server message log > > Also, do you use SSL? No I'm not. It's not even complied in the server nor in the pg_dump binary. The server is built on windows using MSYS simply with ./configure && make all && make install I've been able to reproduce the problem 6 times (at random points in the process, but it never complete successfully). Is there any test I can do to help investigate the problem ? cyril > > //Magnus > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Winsock error 10035 while trying to upgrade from 8.0 to 8.2
Cyril VELTER wrote: > > I'm trying to upgrade a pretty big database (60G) from postgres 8.0 to > postgres 8.2 on windows 2000 Server (both version running on the same machine > on different ports). During the migration process, I always get an error at > some point (never the same) : Interesting. 10035 is "A non-blocking socket operation could not be completed immediatly". Question: Does this error come fromthe 8.0 or the 8.2 server? Also, do you use SSL? //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] autovacuum
On Sat, May 12, 2007 at 03:48:14PM -0400, Tom Allison wrote: > I noticed that under 8.2 the autovacuum isn't running (logging) every > 60s like I'm used to seeing. See the 8.2 Release Notes: http://www.postgresql.org/docs/8.2/interactive/release-8-2.html * Remove routine autovacuum server log entries (Bruce) pg_stat_activity now shows autovacuum activity. In 8.2 the "autovacuum: processing database" messages are logged at DEBUG1; in 8.1 they were logged at LOG. > I pretty much just took the defaults in the postgresql.conf file > since that's always seemed to work before. Autovacuum was first incorporated into the backend in 8.1 and it's disabled by default in 8.1 and 8.2, at least in source builds (it might be enabled by default in some pre-packaged distributions). What do you have in postgresql.conf for the following settings? autovacuum autovacuum_naptime stats_start_collector stats_row_level log_min_messages Do you see any warnings like the following in the server logs? WARNING: autovacuum not started because of misconfiguration HINT: Enable options "stats_start_collector" and "stats_row_level". > I'm not making a lot of changes to the database right now (insert/ > update/delete) but I thought I would still get the logging. If you have autovacuum and row-level statistics enabled then autovacuum should be running. I'd guess you aren't seeing the routine messages because they're logged at DEBUG1 and you have log_min_messages at a level that doesn't show debug messages. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Indice en Date
Arturo Munive ha escrito:
> tengo un indice sobre una columna date.
>
> cuando hago una consulta ...
> *Select *
> id
> *from *
> ventas
> *WHERE *
> fecha = date('12-JAN-2007')
>
> el planificador usa el indice
>
> pero cuando la restriccion es WHERE fecha < date('12-JAN-2007')
>
> se efectua un barrido secuencial.
>
> ni e indice ni la tabla ni la consulta son nada complejos
>
> que me olvido o que debo hacer para que se utilize el indice cuando uso el
> operador menor
Depende de la distribución de información en (y estadísticas de) la
tabla en cuestión.
1. ¿Haz ejecutado ANALYZE? El planificador depende de estadísticas
recientes para tomar una mejor decisión.
2. ¿En la tabla, la mayoría de los datos son menor que la fecha de
ejemplo? Si es así, y haz analizado, de igual forma el planificador
puede juzgar que el barrido secuencial es lo más indicado, ya que se
visitan la mayoría de las páginas de datos.
3. Si haz analizado y la distribución no es acorde a (2), entonces
puedes jugar con los parámetros de costo del archivo de configuración,
o incrementar el target de estadísticas de la columna en cuestión.
Consulta la documentación para ver estos casos.
Suerte.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] TWO SAME TABLES, ONE UPDATED. HOW TO SYNC THE OTHER?
On May 11, 11:06 pm, "L. Berger" <[EMAIL PROTECTED]> wrote: > Hello > > I havetwotables-- A and B. The structure of both is thesame. Only, > B has many indexes and is used for heavy duty SELECTs. On theother > hand, A only accepts heavy duty INSERTs, so has onlyoneprimary key > index. > > So my DB design is such that A is only an INSERT table. Periodically, > say every 20 minutes or so, I would like to take all the new INSERTs > from table A and put them into B. > > Is there any clever command to accomplish this? I'd rather not write a > PHP script with SQL to take every single new record, and update every > column of a new row in table B. For instance, can I do a replication > of onlytables, not databases? > > Thanks for any pointers!! > > LB Assuming ID is PK: INSERT INTO b SELECT * FROM a WHERE NOT EXISTS ( SELECT 1 FROM b WHERE b.ID = a.ID ) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
