[GENERAL] pg_xlog content
Hi, When I initialize database, pg_xlog direcory takes almost 17 MB size Why pg_xlog/WAL takes 17 MB of size while there is no transaction occurred ? What is the content of pg_xlog , If it's only wal file it's must be blank after on write of transaction on database. regards, Siddharth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Executing a user created function twice give an error
Hi, PostgreSQL 8.1.11 I have created a function that works fine when run for the first time after that it gives an error until I open another Query window. The function is as follows /// CREATE OR REPLACE FUNCTION func_snappointtonetwork(p_pointtobesnapped varchar, p_currentboundingbox varchar) RETURNS text AS $BODY$ DECLARE v_geom bytea; v_snappedPoint varchar; v_HAPMSSection varchar; v_road varchar; v_area varchar; v_cWay varchar; v_cWayDirection varchar; BEGIN SELECT hapms2.sect_label as link_Id,distance(hapms2.geom,ST_GeomFromEWKT(p_pointToBeSnapped)) as distance INTO TEMPORARY TABLE __distances__temp FROM public.hapms_road hapms2 WHERE (hapms2.geom ST_box2d(ST_GeomFromEWKT(p_currentBoundingBox))); SELECT INTO v_geom,v_HAPMSSection,v_road,v_area,v_cWay,v_cWayDirection asEWKB(hapms1.geom),hapms1.sect_label,hapms1.roa_number,hapms1.area_name,hapms1.funct_name,hapms1.direc_code FROM public.hapms_road hapms1 WHERE (hapms1.geom ST_box2d(GeomFromEWKT(p_currentBoundingBox))) AND distance(hapms1.geom, GeomFromEWKT(p_pointToBeSnapped)) all (SELECT distance FROM __distances__temp WHERE public.hapms1.sect_label link_Id); SELECT ST_AsEWKT(ST_line_interpolate_point(ST_LineMerge(ST_GeomFromEWKB(v_geom)),ST_line_locate_point(ST_LineMerge(ST_GeomFromEWKB(v_geom)),ST_GeomFromEWKT(p_pointToBeSnapped INTO v_snappedPoint; DROP TABLE __distances__temp; RETURN v_snappedPoint || '|' || v_HAPMSSection || '|' || v_road || '|' || v_area || '|' || v_cWay || ' ' || v_cWayDirection; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION func_snappointtonetwork(p_pointtobesnapped varchar, p_currentboundingbox varchar) OWNER TO postgres; /// Error: ERROR: relation with OID 100412 does not exist CONTEXT: SQL statement SELECT asEWKB(hapms1.geom),hapms1.sect_label,hapms1.roa_number,hapms1.area_name,hapms1.funct_name,hapms1.direc_code FROM hapms_road hapms1 WHERE (hapms1.geom ST_box2d(GeomFromEWKT( $1 ))) AND distance(hapms1.geom, GeomFromEWKT( $2 )) all (SELECT distance FROM __distances__temp WHERE hapms1.sect_label link_Id) PL/pgSQL function func_snappointtonetwork line 14 at select into variables Any help in this matter would be highly appreciated. regards, W Khattak -- 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] Executing a user created function twice give an error
am Tue, dem 02.12.2008, um 11:11:44 + mailte Wajid Khattak folgendes: Hi, PostgreSQL 8.1.11 I have created a function that works fine when run for the first time after that it gives an error until I open another Query window. Use EXECUTE for DDL-Statements, for instance, create a temp-table. 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
Re: [GENERAL] How do I index to speed up OVERLAPS?
Matthew Wilson wrote: I have a table shift with a start_time column and a stop_time column and I do a lot of queries like: select * from shift where (start_time, stop_time) overlaps ($A, $B); $A and $B are user-submitted values. Anyhow, how do I put indexes on my table to make these queries run faster? Also, is there a better data type to use for ranges of time? There's not really a good solution for this. A btree or hash index is pretty much useless for ranges like this (although for certain simple queries two btree searches can give you a useful subset). If performance here is really important, you'll need to use some of PG's geometric functions. You can treat the range as a box with coordinates (start,0,end, 1) and then use GiST indexing on the overlaps operation. -- Richard Huxton Archonet Ltd -- 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] psql verbose mode
Grzegorz Jaśkiewicz wrote: hey folks, browsing psql source,I came across one of the variations of \list / -l option, that could also print database size, which would be quite nice. But that only seem to work in 'verbose' mode. Now, how can I turn that mode on on command line, psql -lv doesn't do ; Is \l+ what you mean? -- Richard Huxton Archonet Ltd -- 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] pg_xlog content
Richard Huxton wrote: Siddharth Shah wrote: Hi, When I initialize database, pg_xlog direcory takes almost 17 MB size Why pg_xlog/WAL takes 17 MB of size while there is no transaction occurred ? What is the content of pg_xlog , If it's only wal file it's must be blank after on write of transaction on database. It's the WAL. Files will be 16MB in size and you will have several (I think 3 is the minimum). If you have a large update that can increase. See the checkpoint_segments setting if you think that's something you'll do frequently. If you are using WAL-based replication you could have lots if the archive command keeps failing. I am not using wall based replication ? I no not have frequent long transaction , need of checkpoints Just want to get what data resides in pg_xlog that it takes 16MB without any transactions. -Siddharth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Job scheduling in Postgre
I have certain jobsto be executed automatically at a given interval of time in the postgre SQL database. Is their any utility/feature available in Postgre to do so.with regards, I.P.S. Sethi
Re: [GENERAL] Favorite Tom Lane quotes
On Mon, 2008-12-01 at 22:51 -0500, justin wrote: On idiotic benchmark comparisons Try to carry 500 people from Los Angeles to Tokyo in an F-15. No? Try to win a dogfight in a 747. No? But they both fly, so it must be useful to compare them... especially on the basis of the most simplistic test case you can think of. For extra points, use *only one* test case. Perhaps this paper can be described as comparing an F-15 to a 747 on the basis of required runway length We used that analogy for comparing database benchmarks as far back as 1989-90 at Teradata. My memory is it was invented to counter claims that DB2 was faster after some disastrous initial benchmark results while attempting a straight database migration. The contrast was that the client/server overhead of each request *was* higher, though the parallel database could perform actions much faster when it eventually got started. The original analogy was a comparison of the passenger carrying capacity, since a jet fighter could only carry ~1 person while the airliner could carry 100s, yet the jet fighter could obviously deliver 1 person much faster to a destination. (At the time, the concept of client/server was widely laughed at). Joke - Threat - Obvious. That thought led to the development at BA of a system specifically designed to offload large SQL queries from the mainframe DB2 system. BA knew how to judge database systems and use them for what they were good at. (They continued to use TPF also, because of its speed of hash index implementation, amongst other optimisations). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Favorite Tom Lane quotes
On Tue, Dec 2, 2008 at 9:51 AM, Picavet Vincent [EMAIL PROTECTED] wrote: That seems to be kind of a joke, but I heard that the french CNES (spatial agency) is doing some huge database replication between two sites thanks to big trucks carrying the data once a month, because current network devices transfer rates do not allow them to do remote replication through the network. Or maybe the reasons to this method are not only technical but economical. I have no detailed information on this, and it's more a rumour than a verified information, but should somebody know more on the subject, i'd be interested in hearing it. but than you have to add cost of backing up and restoring labour time, and that's going to be a massive latency - if you ask me. -- GJ
Re: [GENERAL] How to design for cheap reliability with PostgreSQL?
Michael Meier wrote: Hi, I am in the process of designing a distributed application (Java EE 5) which has demand for database functionality at several points. Some of these databases need to provide really mission critical reliability, i.e. it would be the end of our world, if we would suffer from data loss or silent data corruption. Availabilty or servicability are nice but not that important. This means that it isn't that important that a transaction is committed, but if the transaction is committed, the data should be 100% safe - without any chance of data loss or corruption. I'm assuming you mean 99.99..9% safe. On the other hand, real proper reliability is expensive and we do not have the money for serious hardware or unbreakable database licences. I assume a DB size of 50GB with moderate write I/O and less moderate read I/O, so I would like to get a machine with 64 GB Ram for in-memory caching. An unbreakable database isn't necessarily going to help you with the sort of errors you're thinking of. I am thinking of PostgreSQL 8.3.n with n=5 on top of Solaris 10 with ZFS. Not sure if it's of interest, but there's been discussion of block-level checksums for 8.4. You might want to check the archives for the -hackers mailing list. I've never used ZFS, but from what I've read it would make sense. So an extremely cheap low end hardware platform for the problem might look like this: You and I are at different scales of cheap :-) Dual Quad Core Xeon Machine 64 GB FB-DIMM ECC-Ram Two 8-Port SAS Controller Internal 16 x 73 GB 15k hard Drives, partitioned as follows: - 4 drives: 3-way raid-1 zfs mirror with one additional hot spare for Solaris - 6 drives: 4-way raid-1 zfs mirror with two additional hot spares for Postgresql Tables - 6 drives: 4-way raid-1 zfs mirror with two additional hot spares for Postgresql Logging That's a lot of disks for a 50GB database, unless you've got a *lot* of writes. ZFS-Scrubbing every night. approx. 24.000$ list price from Sun (x4250) with Sun Platinum support for the hardware and Solaris, or approx. 12.000$ self-assembled from newegg. Well, several of either must be preferable to your expensive version. And an extremely expensive high-end solution for the problem might look like this: Sun SPARC Enterprise M4000 Server with two SPARC64 VII CPUs 64GB registered ECC-Ram Sun M-Series RAS-Features (Checksumming of CPU-Registers, etc.) An external Drive Array (e.g. J4400) with 16 drives. Partioned like the internal drives above. ZFS-Scrubbing every night. approx. 160.000$ This insane price difference would get us data integrity beyond the ZFS checksumming feature: It would protect the data even in the CPU registers. So the questions are: 1) Is this necessary? Do bit errors happen with configurations like the cheap xeon one above? You'll probably want to estimate the odds of earthquake/volcano/nuclear war and see how much effort it's worth. 2) If this is necessary, is there any (software) way around it? Is there a PostgreSQL clustering solution available, where the cluster nodes check each others data integrity? Hmm - there are commercial variants of PG that do clustering/replication (Greenplum and EnterpriseDB for a start) but I'm not sure if it's quite what you're after. I don't know what sort of queries you're running, but you could do something like: - pg-pool to send queries to two front-end servers - both replicate to a third server over slony, which compares the replicated copies (synchronisation issues here though) - you keep the replication stream or keep separate WAL archives so if a problem is found you stop everything and rewind until you get to a known good point. Of course, all this just introduces more code with more chances of a bug. It would *look* more reliable, but I'm not sure it would be. HTH -- Richard Huxton Archonet Ltd -- 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] pg_xlog content
Siddharth Shah wrote: I am not using wall based replication ? I no not have frequent long transaction , need of checkpoints Just want to get what data resides in pg_xlog that it takes 16MB without any transactions. The files are created 16MB long, it is faster to overwrite that than to keep extending a shorter file. Since the WAL is written to with every transaction it needs to be fast. -- Richard Huxton Archonet Ltd -- 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] Job scheduling in Postgre
am Tue, dem 02.12.2008, um 16:45:16 +0500 mailte IPS folgendes: I have certain jobs to be executed automatically at a given interval of time in the postgre SQL database. Is their any utility/feature available in Postgre to do so. No, use the scheduler from the OS, CRON for example (UNIX). 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
Re: [GENERAL] Executing a user created function twice give an error
am Tue, dem 02.12.2008, um 12:36:26 + mailte Wajid Khattak folgendes: Thank for your reply. Could you please elaborate it a little bit further by referring to the fucntion as I am quite new to Postgres. Sure, read the doku: http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN PG cached the plan and the also the OID for affected tables. To avoid this, use EXECUTE 'insert your query here' for create or delete tables within plpgsql. 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
Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
Sebastian Tennant wrote: Quoth Alvaro Herrera [EMAIL PROTECTED]: Sebastian Tennant wrote: P.S. Emacs users of PostgreSQL might like to know that there's a texinfo version of the manual (version 8.3.3) available for download from here: http://www.emacswiki.org/PostGreSQL Hmm, we did have a patch to add a texinfo target to the docs Makefile ... apparently it was never applied. Maybe that's a good idea? It's a very good idea IMHO. Hmm, actually now that I look closer, it is there (make postgres.info does the trick). The build process throws a worrying number of warnings though. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] [TextSearch] syntax error while parsing affix file
iconv -f windows-1251 -t utf-8 bulgarian.dic bulgarian_utf8.dict iconv -f windows-1251 -t utf-8 bulgarian.aff bulgarian_utf8.affix The locale of the database is fr_FR, and its encoding is UTF8. I believe that characters 'И', 'А' (non-ascii) and other cyrillic ones are not acceptable for french locale :( -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ -- 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] psql verbose mode
On Tue, Dec 2, 2008 at 11:40 AM, Richard Huxton [EMAIL PROTECTED] wrote: Is \l+ what you mean? same thing but from command line ? not the shell -- GJ -- 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] [TextSearch] syntax error while parsing affix file
Teodor Sigaev a écrit : iconv -f windows-1251 -t utf-8 bulgarian.dic bulgarian_utf8.dict iconv -f windows-1251 -t utf-8 bulgarian.aff bulgarian_utf8.affix The locale of the database is fr_FR, and its encoding is UTF8. I believe that characters 'И', 'А' (non-ascii) and other cyrillic ones are not acceptable for french locale :( I was able to install a thailandese dictionary - why would such dictionary be ok and not a bulgarian one? Which locale should I use to enable my database to be multi-language compatible? I would never have suspected a locale problem... Ouch! Daniel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] Cannot open include file: 'nodes/nodes.h'
elekis escribió: rcqpgsql.c c:\program files\postgresql\8.3\include\server\pg_config.h(116) : warning C4005: 'HAVE_FINITE' : macro redefinition C:\Program Files\MySQL\MySQL Server 5.0\include\config-win.h(286) : see previous definition of 'HAVE_FINITE' You're including a MySQL header too? I don't think that's expected to work. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Time without seconds
Hello,
is it possible to get the time without the seconds and without the usage
of a formatting function?
Example:
CREATE TABLE test.testtime
(
thetime time without time zone
);
INSERT INTO test.testtime (thetime) VALUES ('12:34:56');
INSERT INTO test.testtime (thetime) VALUES ('23:45');
SELECT thetime from test.testtime;
The result should be without the seconds.
I know that there are formatting functions for date/time. But I am
searching for a way to get a formatted time with a simple SQL query.
Thanks
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] Favorite Tom Lane quotes
Klint Gore írta: Scott Marlowe wrote: On Mon, Dec 1, 2008 at 10:42 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: On Tue, 2008-12-02 at 00:37 -0500, Greg Smith wrote: Tom on things that might ruin his tape backups: Then of course there are the *other* risks, such as the place burning to the ground, or getting drowned by a break in the city reservoir that's a couple hundred yards up the hill...Or maybe being burgled by Oracle employees who are specifically after my backup tapes. What is a tape? Apparently something to do with backups. But I don't know what those are either... :-P Never underestimate the bandwidth of a station wagon full of tapes hurtling down the highway. —Tanenbaum, Andrew S. (1996). Computer Networks. New Jersey: Prentice-Hall, 83. ISBN 0-13-349945-6. But the latency of a truck is awful :-) A modern chrysler town and country(1) has a cargo capacity of 140.1 cubic feet(2) letting it carry 17163 LTO4 (3) tapes at 800GB each. Thats 13730TB. Say it has to get from San Francisco to LA (about 6 hrs according to google maps directions(4)), that gives 2288TB/hour, or 5.1 terabit/second. klint. 1. its more of a minivan than a station wagon these days but close enough. halve the number of tapes if you're thinking of the classic woodie http://www.allpar.com/old/townandcountry.html 2. http://www.chrysler.com/hostc/vsmc/vehicleSpecModels.do?modelYearCode=CUC200908 3. dimensions from http://en.wikipedia.org/wiki/Linear_Tape-Open 4. http://maps.google.com/maps?f=dsaddr=san+franciscodaddr=los+angeleshl=engeocode=mra=lssll=37.0625,-95.677068sspn=58.598104,116.542969ie=UTF8ll=35.939855,-120.330885spn=7.601811,14.567871z=7 -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] dblink can't query a password with a space
I'm using the following dblink query to connect to another database on
the server. Query itself works fine, however I have the problem that if
the password contains a space character like in hello world, then
dblink can't connect to that database.
Is there any possibility to get that working with dblink? I have to
dynamically create this view, so I have no choice if a user uses a
password like that. I suppose the same problem occurs when the user name
contains a space character.
CREATE VIEW MyView AS
SELECT * from dblink('host=TestHost port=5432 dbname=TestDB
user=postgres password=hello world','SELECT Name FROM TestTable') AS
(Name text);
Thank you,
H.Muster
--
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] pg_xlog content
Siddharth Shah wrote: I am not using wall based replication ? I no not have frequent long transaction , need of checkpoints Just want to get what data resides in pg_xlog that it takes 16MB without any transactions. It contains the transaction log files. The files are precreated with a size of 16MB and filled with log entries. There will normally be several of those files around. Since your main objective is to keep size small, you can change the default of 16MB by setting XLOG_SEG_SIZE in src/include/pg_config_manual.h to a smaller value. Yours, Laurenz Albe -- 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] Job scheduling in Postgre
On 02/12/2008 11:45, IPS wrote: I have certain jobs to be executed automatically at a given interval of time in the postgre SQL database. Is their any utility/feature available in Postgre to do so. cron? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Executing a user created function twice give an error
On 02/12/2008 12:50, A. Kretschmer wrote: PG cached the plan and the also the OID for affected tables. To avoid this, use EXECUTE 'insert your query here' for create or delete tables within plpgsql. This has been fixed in 8.3 - you no longer have to construct dynamically any statements which touch temporary tables. If you have to do a lot of this, it would be worth your while upgradingnot to mention the performance improvements also. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] pg_xlog content
Siddharth Shah wrote: Hi, When I initialize database, pg_xlog direcory takes almost 17 MB size Why pg_xlog/WAL takes 17 MB of size while there is no transaction occurred ? What is the content of pg_xlog , If it's only wal file it's must be blank after on write of transaction on database. It's the WAL. Files will be 16MB in size and you will have several (I think 3 is the minimum). If you have a large update that can increase. See the checkpoint_segments setting if you think that's something you'll do frequently. If you are using WAL-based replication you could have lots if the archive command keeps failing. -- Richard Huxton Archonet Ltd -- 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] [TextSearch] syntax error while parsing affix file
I am using Postrges 8.3.5, and I am trying to install a bulgarian ISpell dictionary (the OpenOffice one) for Textsearch features. flag *A: . А (this is line 24) . АТА . И . ИТЕ OpenOffice or ISpell? Pls, provide: - link to download of dictionary - Locale and encoding setting of your db -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ -- 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] Executing a user created function twice give an error
Thank for your reply. Could you please elaborate it a little bit further by referring to the fucntion as I am quite new to Postgres. regards, - Original Message - From: A. Kretschmer [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: 02 December 2008 11:23:20 o'clock (GMT) Europe/London Subject: Re: [GENERAL] Executing a user created function twice give an error am Tue, dem 02.12.2008, um 11:11:44 + mailte Wajid Khattak folgendes: Hi, PostgreSQL 8.1.11 I have created a function that works fine when run for the first time after that it gives an error until I open another Query window. Use EXECUTE for DDL-Statements, for instance, create a temp-table. 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 -- 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] Time without seconds
On Tue, Dec 2, 2008 at 10:37 AM, Andreas Kraftl [EMAIL PROTECTED] wrote: Hello, is it possible to get the time without the seconds and without the usage of a formatting function? regexp and/or date_trunc -- GJ -- 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] Trigger before delete does fire before, but delete doesn't not happen
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane a écrit : Adrian Klaver [EMAIL PROTECTED] writes: Thanks for the explanation. Just so I am clear,the act of updating the row in p_commandeligne_ad creates a new tuple for the row with id of 1. This means the original statement delete from commande where id=1 runs against a version of the row that no longer exists and becomes a no-op statement. This happens because the trigger was run as BEFORE and changed the row from under the original statement. Right. regards, tom lane Thanks for having helped me understand better why it couldn't be a logical way of acting. Best regards, - -- Stéphane Schildknecht PostgreSQLFr - http://www.postgresql.fr Dalibo - http://www.dalibo.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJNRmvA+REPKWGI0ERAkeIAKCMucAjbCS8tw5kXJqyCuNWS7pMjQCgu2MU U4rECUpyOm5rqnr0FRmBT6o= =b7ow -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
[GENERAL] What will happen to PostgreSQL server (on Linux) if CMOS battery dies?
Hi! I wonder what the consequences would be for a PostgreSQL server running on Linux on a machine where the CMOS battery that gives life to the local system (time) clock dies. Note that the machine is not connected to a network, so synchronizing with an external time server is not an option. The machine is usually turned off every night, and turned on every morning, so I guess that with a dead battery, the local time will be set to 1970 every morning. Of course the battery should be changed as soon as possible if it dies, but I just want to know if PostgreSQL will be able to run at all in these conditions. Best regards, Fredrik Israelsson _ Beställ bläck före 19 för leverans nästa vardag http://www.inkclub.com/msn8
[GENERAL] Multi Lingual problem
I am using the psqlodbc driver to connect to postgresql... But this is not supporting all the languages that UTF should support.. Database is of the correct encoding that is UTF... can you please give a solution so that i can psqlodbc supports multiple languages. Right now there is support for russian characters from my application.. thanks in advance for any replies
Re: [GENERAL] Favorite Tom Lane quotes
Hi, Never underestimate the bandwidth of a station wagon full of tapes hurtling down the highway. -Tanenbaum, Andrew S. (1996). Computer Networks. New Jersey: Prentice-Hall, 83. ISBN 0-13-349945-6. A modern chrysler town and country(1) has a cargo capacity of 140.1 cubic feet(2) letting it carry 17163 LTO4 (3) tapes at 800GB each. Thats 13730TB. Say it has to get from San Francisco to LA (about 6 hrs according to google maps directions(4)), that gives 2288TB/hour, or 5.1 terabit/second. That seems to be kind of a joke, but I heard that the french CNES (spatial agency) is doing some huge database replication between two sites thanks to big trucks carrying the data once a month, because current network devices transfer rates do not allow them to do remote replication through the network. Or maybe the reasons to this method are not only technical but economical. I have no detailed information on this, and it's more a rumour than a verified information, but should somebody know more on the subject, i'd be interested in hearing it. Vincent -- 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] [TextSearch] syntax error while parsing affix file
Teodor Sigaev a écrit : I am using Postrges 8.3.5, and I am trying to install a bulgarian ISpell dictionary (the OpenOffice one) for Textsearch features. flag *A: . А (this is line 24) . АТА . И . ИТЕ OpenOffice or ISpell? Pls, provide: - link to download of dictionary - Locale and encoding setting of your db The dictionary is the ISpell one I got from http://wiki.services.openoffice.org/wiki/Dictionaries list. Here is a direct link for it: http://heanet.dl.sourceforge.net/sourceforge/bgoffice/ispell-bg-4.1.tar.gz I converted its encoding from windows-1251 to UTF-8 before running the CREATE TEXT SEARCH DICTIONARY: iconv -f windows-1251 -t utf-8 bulgarian.dic bulgarian_utf8.dict iconv -f windows-1251 -t utf-8 bulgarian.aff bulgarian_utf8.affix The locale of the database is fr_FR, and its encoding is UTF8. Thanks! Daniel -- 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] Favorite Tom Lane quotes
Scott Marlowe wrote: What is a tape? Apparently something to do with backups. But I don't know what those are either... :-P From The Top Of My Head Dictionary - Backup Tapes noun Thin flimsy strip of plastic with a fine coating of iron oxide or similar compound which is then wound up around a small plastic wheel, stretched over a little head and onto another small plastic wheel. Normally used as a means of translating digital computer talk into an ear piercing noise that humans can then listen to to ensure that the computers aren't plotting to overthrow the earth. Legend has it that computers can use these tapes to reconstruct some piece of information as it was at some historic moment. While it has often been attempted to prove that this process is possible, the mixed reports about it's success leave most in doubt as to whether it can really be done and believing it is all just mythology. Of course the true believers will argue endlessly about the wonders of what can be done and how important it is, but they tend to have a low conversion rate among the listeners. ;-) -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] Trigger before delete does fire before, but delete doesn't not happen
On Tuesday 02 December 2008 3:19:11 am Stéphane A. Schildknecht wrote: Tom Lane a écrit : Adrian Klaver [EMAIL PROTECTED] writes: Thanks for the explanation. Just so I am clear,the act of updating the row in p_commandeligne_ad creates a new tuple for the row with id of 1. This means the original statement delete from commande where id=1 runs against a version of the row that no longer exists and becomes a no-op statement. This happens because the trigger was run as BEFORE and changed the row from under the original statement. Right. regards, tom lane Thanks for having helped me understand better why it couldn't be a logical way of acting. Best regards, Actually there is a logic to it once you realize that an UPDATE in Postgres is really a DELETE and INSERT operation. It is a concept that still catches me on a regular basis. To see what is going on substitute ctid for oid in your test case. This will show that the ctid(current tuple id) is changing for the row you are deleting in commande. -- Adrian Klaver [EMAIL PROTECTED] -- 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] Detecting changes to certain fields in 'before update' trigger functions
Quoth Alvaro Herrera [EMAIL PROTECTED]: Sebastian Tennant wrote: Quoth Alvaro Herrera [EMAIL PROTECTED]: Sebastian Tennant wrote: P.S. Emacs users of PostgreSQL might like to know that there's a texinfo version of the manual (version 8.3.3) available for download from here: http://www.emacswiki.org/PostGreSQL Hmm, we did have a patch to add a texinfo target to the docs Makefile ... apparently it was never applied. Maybe that's a good idea? It's a very good idea IMHO. Hmm, actually now that I look closer, it is there (make postgres.info does the trick). The build process throws a worrying number of warnings though. Warnings are better than errors :-) I'll download the source and have a go myself. Many thanks Alvaro. Sebastian -- 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] Favorite Tom Lane quotes
[EMAIL PROTECTED] (Tom Lane) writes: Scott Marlowe [EMAIL PROTECTED] writes: We really need a favorite Tom Lane quotes thread. Mine is (roughly): We don't support that, but you're free to try it, you just get to keep both pieces if it breaks. Hate to disillusion you, but that's a standard comment around Red Hat. I have no idea who said it first, but twasn't me. There's a license that has that as its main clause. See the end of the document.. http://man.he.net/man8/chat An authoritative source for the original seems to have evaporated, but here's a direct inheritor implemented in Expect: http://expect.nist.gov/scripts/chat A quick web search indicates that ucLinux seems to include old style chat as also does DragonflyBSD... -- select 'cbbrowne' || '@' || 'linuxdatabases.info'; http://linuxfinances.info/info/linux.html The chat program is in public domain. This is not the GNU public license. If it breaks then you get to keep both pieces. (Copyright notice for the chat program) -- 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] Favorite Tom Lane quotes
On Tue, 2008-12-02 at 10:40 +, Grzegorz Jaśkiewicz wrote: but than you have to add cost of backing up and restoring labour time, and that's going to be a massive latency - if you ask me. Of course it is, but really latency probably isn't the key issue - more that the data itself isn't lost. -Mark -- 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] Nested Loop Left Join always shows rows=1
Yeah, this is a bug: it's effectively double-counting the selectivity of the index clause. The error isn't enormously critical, since the join size estimate is right; but it could perhaps lead to choosing a plain indexscan when a bitmap scan would be better. I've applied a patch. Thank you. I'll try that. I was able to change the PgBackendStatus struct to hold a percentage of completion field, which shows up when calling the pg_stat_get_activity function. As I said in a previous mail, the progress indicator gives very good estimates for the simple queries I need. If I can come up with something that is good in general I'll post it. I'm reading some papers about the argument (mostly the ones listed in http://wiki.postgresql.org/wiki/Query_progress_indication). -- 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] Favorite Tom Lane quotes
2008/12/1 Scott Marlowe [EMAIL PROTECTED]: On Mon, Dec 1, 2008 at 7:49 PM, Grzegorz Jaśkiewicz [EMAIL PROTECTED] wrote: which reminds me, of my favourite recent quote: Think I'll go fix this while I'm watching the football game ... We really need a favorite Tom Lane quotes thread. Mine is (roughly): We have a little joke here in the office. Really, really bad code is 'horrid'. :-) 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] Postgres mail list traffic over time
Magnus Hagander wrote: 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. Yea, this is the graph I was looking for; unfortunately it does not shed any insight on why things seems busier; 'old age' is starting to look plausible. ;-) -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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
On Tue, 2008-12-02 at 15:47 -0500, Bruce Momjian wrote: Magnus Hagander wrote: 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. Yea, this is the graph I was looking for; unfortunately it does not shed any insight on why things seems busier; 'old age' is starting to look plausible. ;-) It could also be that a lot of work is happening off channel. I know that many contributors are having the first 50 replies of the email on jabber, irc or directly and then posting to various lists at any given point. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Corruption in postgres 7.4 on solaris 2.9.
I am running postgres 7.4 and solaris 2.9 and I fear I have a corruption. The database runs fine for a while and then a command will receive the following: psql: FATAL: xlog flush request 8/20D70938 is not satisfied -- flushed only to 6/FDBA6540 CONTEXT:writing block 180816 of relation 17143/17477 This occurs many times with the flushed only to value never changing. Eventually the server must be restarted. I tried using the pgfsck tool and get the following: -- Detected database format 7.4 -- Loading pg_class structure -- Scanning table pg_class (1259) -- Table pg_class(1259):Page 0: Incorrect value in header (incorrect blocksize?) -- 00 00 00 00 00 66 A6 70 00 00 00 0B 00 E4 01 50 20 00 20 01 -- Table pg_class(1259):Page 0: Incorrect value in header (incorrect blocksize?) -- 00 00 00 00 00 66 D9 F0 00 00 00 0B 00 E4 01 90 20 00 20 01 -- Table pg_class(1259):Page 0: Incorrect value in header (incorrect blocksize?) -- 00 00 00 00 00 67 6D F0 00 00 00 0B 00 E0 01 90 20 00 20 01 -- Table pg_class(1259):Page 0: Incorrect value in header (incorrect blocksize?) -- 00 00 00 00 00 67 B4 A0 00 00 00 0B 00 D4 02 B0 20 00 20 01 Couldn't find class 'pg_attribute' I believe the block size is 8192 and I have tried others to no avail. Is there a way to determine the corrupt area and remove it? I have read pg_filedump can help but I have not been able to find a Solaris version. 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] returns numbers of record
Hi all, does it exists a way to know how many records a query returns? I thought sometime like DECLARE curs1 CURSOR FOR select * from table ; I thought if I can write MOVE LAST Is there any way to return the number of row for select * from table without execute a count(*) ? Thanks :) Enrico -- That's one small step for man; one giant leap for mankind www.enricopirozzi.info [EMAIL PROTECTED] Skype sscotty71 -- 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] Corruption in postgres 7.4 on solaris 2.9.
On Tue, Dec 2, 2008 at 2:46 PM, Gary Schlachter [EMAIL PROTECTED] wrote: I am running postgres 7.4 and solaris 2.9 and I fear I have a corruption. The database runs fine for a while and then a command will receive the following: 7.4.what? A lot of older 7,4 versions had known data corrupting bugs in them. Maybe you're running one of them? -- When fascism comes to America, it will be draped in a flag and carrying a cross - Sinclair Lewis -- 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] returns numbers of record
Select Count(*) from (query) is what i believe you are looking for see http://www.postgresql.org/docs/8.3/interactive/tutorial-agg.html Enrico Pirozzi wrote: Hi all, does it exists a way to know how many records a query returns? I thought sometime like DECLARE curs1 CURSOR FOR select * from table ; I thought if I can write MOVE LAST Is there any way to return the number of row for select * from table without execute a count(*) ? Thanks :) Enrico -- 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] pg_xlog content
Albe Laurenz wrote: Siddharth Shah wrote: I am not using wall based replication ? I no not have frequent long transaction , need of checkpoints Just want to get what data resides in pg_xlog that it takes 16MB without any transactions. It contains the transaction log files. The files are precreated with a size of 16MB and filled with log entries. There will normally be several of those files around. Since your main objective is to keep size small, you can change the default of 16MB by setting XLOG_SEG_SIZE in src/include/pg_config_manual.h to a smaller value. Yours, Laurenz Albe Thanks for info, will help me. Yes, Objective is to reduce the size, Does setting XLOG_SEG_SIZE to lower size make any difference in performance. Is there any content other than log that that postgres refers from pg_xlog regards, Siddharth -- 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: [GENERAL] Cannot open include file: 'nodes/nodes.h'
ho f***, that must be multi db. something say to me that will be funny :D thanks for all a++ On Tue, Dec 2, 2008 at 3:15 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: elekis escribió: rcqpgsql.c c:\program files\postgresql\8.3\include\server\pg_config.h(116) : warning C4005: 'HAVE_FINITE' : macro redefinition C:\Program Files\MySQL\MySQL Server 5.0\include\config-win.h(286) : see previous definition of 'HAVE_FINITE' You're including a MySQL header too? I don't think that's expected to work. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- http://deathboater.blogspot.com/ -- http://deathboater.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
