Re: [GENERAL] could not write to log - PANIC - System down
My understanding of VSS is that only one non-VSS aware app can access the data at any one time. All I meant was that if their NetBackup version was old that they probably cannot benefit from VSS since I doubt the Win32 PG port knows about it either. Brandon Aiken -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Thursday, December 14, 2006 5:23 PM To: Brandon Aiken Cc: Merlin Moncure; pgsql-general@postgresql.org Subject: Re: [GENERAL] could not write to log - PANIC - System down On Thu, Dec 14, 2006 at 05:13:30PM -0500, Brandon Aiken wrote: Of course, it's Windows. Should not is often a suggestion, it seems. As a port, postmaster.exe was presumably not written with VSS in mind, so it might object to the shadow copy instantiation (which, again, it *should* not be able to do). Any backup system that is not transparent to processes running on the system seems to be flawed by design. No idea on the frequent autovacuuming. Do you do a lot of deletes? In those messages processing just means it woke up to see if there was anything to do. It probably didn't do anything. Waking up every minute is not that big a deal... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] a question for the way-back machine
On 12/14/06, Martijn van Oosterhout kleptog@svana.org wrote: On Thu, Dec 14, 2006 at 09:35:47AM -0800, Ben wrote: Interesting. Is that plan cached for the life of the session doing the inserts, the life of the trigger, or until the database is restarted? Duration of a session, there is no support to cache a plan for any other period. Is it session level !!??? I think it is query level; don't we discard the plan after each query? AFAIK, only the plpgsql and other pl/ sisters cache the plan for the queries inside their code-blocks. I am under the impression that if a query is fired multiple times, it will be planned every time, unless we user PREPAREd stetements. Please correct me if I am wrong. I guess I'm trying to figure out how to get the plan to re-cache, without making it entirely dynamic. I don't think you can. Restarting the connection should be enough. Have a ncie day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFFgZWEIB7bNG8LQkwRAkh9AJ9F1YmGTnmBt4iiKNUnkmlM+Xp9/QCffZpl x4OxRMtBHmcWnTyl/bDFtbo= =zs38 -END PGP SIGNATURE- -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [GENERAL] could not write to log - PANIC - System down
On Fri, Dec 15, 2006 at 03:54:01AM -0500, Brandon Aiken wrote: My understanding of VSS is that only one non-VSS aware app can access the data at any one time. All I meant was that if their NetBackup version was old that they probably cannot benefit from VSS since I doubt the Win32 PG port knows about it either. VSS in general can back up files that are open, *regardless* of wether the application that holds it open knows about it. It works the same way as a SAN snapshot. Also, most pre-VSS software have special modules to deal with open files. But they were usually an addon, so if they're on an old enough version of NetBackup not to have VSS, it's certainly possible that there's an addon module required that's not present. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] a question for the way-back machine
On Fri, Dec 15, 2006 at 02:29:48PM +0530, Gurjeet Singh wrote: Is it session level !!??? I think it is query level; don't we discard the plan after each query? AFAIK, only the plpgsql and other pl/ sisters cache the plan for the queries inside their code-blocks. I am under the impression that if a query is fired multiple times, it will be planned every time, unless we user PREPAREd stetements. In the OP's case, he's talking about query fired from a trigger written in pl/pgsql, so it's cached for the session. Normal queries sent by the client are not cached ofcourse. In his example it's not the INSERT that's being cached, it's a query in the trigger. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[GENERAL] Needed files - embedded postgres
Hello list, I'm working on a project which tries to run postgre as a embedded database. I'm know looking at start up scripts and initdb scripts. What are the necessary files for running initdb and running postgres as an embedded database? All binary files and their deps are already fixed so the question is more related to files like postgres.bki and such. Regards, Henrik ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] b-tree index performance
Hi all, I was wondering does the b-tree index performance change when it's implemented on different data types fields? is it better to use one of them instead of the other for (=) comparisons? I'm especially interested between INT8 and TEXT data types. Thanks a lot in advance, Ben-Nes Yonatan
Re: [GENERAL] b-tree index performance
On Fri, Dec 15, 2006 at 01:13:00PM +0200, Yonatan Ben-Nes wrote: Hi all, I was wondering does the b-tree index performance change when it's implemented on different data types fields? is it better to use one of them instead of the other for (=) comparisons? I'm especially interested between INT8 and TEXT data types. The difference in performence will be determined by the cost of comparison. The cost of comparing strings is much higher than for integers, so it will be slower. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org 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] Needed files - embedded postgres
On fös, 2006-12-15 at 11:41 +0100, Henrik Zagerholm wrote: Hello list, I'm working on a project which tries to run postgre as a embedded database. I'm know looking at start up scripts and initdb scripts. What are the necessary files for running initdb and running postgres as an embedded database? All binary files and their deps are already fixed so the question is more related to files like postgres.bki and such. I think you should first figure out if postgres is suitable as an embedded database. (depends on what exactly you mean by an embedded database of course). Postgres has been designed as a server, and lots of implementation details might not make sense in an embedded context. you might be better served by SQLite, or some other such library. gnari ---(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] free space?
Is there a way to see how free space is distributed across tables after running vacuum (without looking through the verbose output)? Thanks, Greg Automated Trading Desk, LLC (ATD) is the sole owner of Automated Trading Desk Brokerage Services, LLC (ATDB) and Automated Trading Desk Financial Services, LLC (AUTO), both NASD members and Members SIPC. ATD does not offer any brokerage services and is not a NASD member. All brokerage services, trading functions, execution of order flow and related matters are performed through ATDB and AUTO utilizing ATD's proprietary technology and software. Any reference to ATD trading, ATD trading services, ATD trading performance, ATD orders, we, us, our or other such usage refers to the services and trading activities of ATDB and AUTO utilizing ATD's proprietary technology and software. Periods of market volatility or other system delays may adversely affect trade execution and related services. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] b-tree index performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/15/06 05:41, Martijn van Oosterhout wrote: On Fri, Dec 15, 2006 at 01:13:00PM +0200, Yonatan Ben-Nes wrote: Hi all, I was wondering does the b-tree index performance change when it's implemented on different data types fields? is it better to use one of them instead of the other for (=) comparisons? I'm especially interested between INT8 and TEXT data types. The difference in performence will be determined by the cost of comparison. The cost of comparing strings is much higher than for integers, so it will be slower. And comparing INT8 is more expensive on a 32-bit system. Since TEXT is totally variable, is there a big difference in TEXT vs CHAR(8)? - -- Ron Johnson, Jr. Jefferson LA USA Is common sense really valid? For example, it is common sense to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that common sense is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFgqawS9HxQb37XmcRAte5AKC984ov7nwW9XfDHGU/75tfmNkeFQCfdrD8 2O8Ia4/Luo3RbVsIW1ImBx4= =E2uZ -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
Re: [GENERAL] Needed files - embedded postgres
Thats true and we have. With the amount of data we need to handle and the queries we need to execute the postgresql database is way better suited for our needs. SQLite do not have the functions we need anyways. It is also quite crash resistant with the WAL implementation. We still create the datafiles on raid devices and only keep the binaries on a DiskOnModule device. Cheers, Henrik 15 dec 2006 kl. 13:49 skrev Ragnar: On fös, 2006-12-15 at 11:41 +0100, Henrik Zagerholm wrote: Hello list, I'm working on a project which tries to run postgre as a embedded database. I'm know looking at start up scripts and initdb scripts. What are the necessary files for running initdb and running postgres as an embedded database? All binary files and their deps are already fixed so the question is more related to files like postgres.bki and such. I think you should first figure out if postgres is suitable as an embedded database. (depends on what exactly you mean by an embedded database of course). Postgres has been designed as a server, and lots of implementation details might not make sense in an embedded context. you might be better served by SQLite, or some other such library. gnari ---(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 ---(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] b-tree index performance
On Fri, Dec 15, 2006 at 07:44:16AM -0600, Ron Johnson wrote: The difference in performence will be determined by the cost of comparison. The cost of comparing strings is much higher than for integers, so it will be slower. And comparing INT8 is more expensive on a 32-bit system. The difference between int4 and int8 is probably negligable. Since TEXT is totally variable, is there a big difference in TEXT vs CHAR(8)? Nothing measurable I'd think. It's probably the same code. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org 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] b-tree index performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/15/06 07:50, Martijn van Oosterhout wrote: On Fri, Dec 15, 2006 at 07:44:16AM -0600, Ron Johnson wrote: The difference in performence will be determined by the cost of comparison. The cost of comparing strings is much higher than for integers, so it will be slower. And comparing INT8 is more expensive on a 32-bit system. The difference between int4 and int8 is probably negligable. Probably compiler-dependent... Since TEXT is totally variable, is there a big difference in TEXT vs CHAR(8)? Nothing measurable I'd think. It's probably the same code. Have a nice day, - -- Ron Johnson, Jr. Jefferson LA USA Is common sense really valid? For example, it is common sense to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that common sense is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFgq6KS9HxQb37XmcRAlE6AJ9LxzCHq95wggefa0Q4a6/sBAw7aACgyasp t8qCwkdarDlhc2N8PKkprrY= =BBBp -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Performing backup from VB.NET
Original Message -From: RPK [EMAIL PROTECTED] I want to perform database backup (pgdump) from within VB.NET. Can it be done? I have an application developed using VB.NET that backs up the database every 12 hours using a small batch file containing both a vacuum and pg_dump backup script. I execute the batch file using the SHELL function (Microsoft.VisualBasic namespace). In my case I run the command in a separate thread in the background. I also provide the user with the ability to initiate this backup process via the menu system so that they can make a backup at any time (e.g. thunder storm approaching...). I timestamp the backup file name, and have the process keep only the last three backups - deleting the oldest backup each time a backup is successfully completed. HTH. Regards, George ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] about the RULE system
Rafal Pietrak [EMAIL PROTECTED] writes: Looks like this thread have died away. No, it moved to the appropriate mailing list: http://archives.postgresql.org/pgsql-hackers/2006-12/msg00564.php regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Needed files - embedded postgres
Henrik Zagerholm [EMAIL PROTECTED] writes: Postgres has been designed as a server, and lots of implementation details might not make sense in an embedded context. you might be better served by SQLite, or some other such library. ... It is also quite crash resistant with the WAL implementation. One of the reasons it's crash resistant is exactly that it's *not* embedded, and thus not subject to corruption by application-side bugs. That concern is what has caused the developers to have zero interest in creating an embeddable variant. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL doesn't accept connections when Windows
- Original Message From: Thomas H., etc. Thank you all for your replies and suggestions. I apologize for not replying earlier but the client is located several hours away and I have not been back to the site until yesterday. Here's an update of the situation (PostgreSQL 8.1.5 on Windows 2000 Server system). The server is not in a domain, but I followed Thomas' suggestion just the same (make sure you manually set log on as a service priviledges on the domain controller for the local postgres user). Upon reboot of the Windows 2000 server: 1. PostgeSQL service starts without problem. 2. Server is listening on port 5435 as per postgresql.conf file (verified by netstat). 3. No other application using port 5435. 3. (PostgreSQL 7.3 version listening on port 5432 - no problems) 4. Attempts to connect to PostgreSQL 8.1 result in the following error message: C:\Program Files\PostgreSQL\8.1\binpsql -h name.local -p 5435 feb01-06-01 psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. 5.There are no related entries in pg_log 6.The following error is in the Windows Event log: Event Type: Error Event Source: PostgreSQL Event Category: None Event ID: 0 Date: 14/12/2006 Time: 1:06:13 PM User: N/A Computer: SERVER Description: FATAL: could not reattach to shared memory (key=5435001, addr=0159): Invalid argument 7. If PostgreSQL Service is restarted via Control Panel Administrative Tools Component Services, everything runs normally and there are no problems connecting. Any further thoughts? Thanks in advance, George ---(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] Needed files - embedded postgres
I think I need to specify what I mean with embedded. Its not that we try to embed it into an application. It is just run from a flash disk and the datafiles are put on standard raid attached disks. Its an embedded device not an embedded application. :) Cheers, Henrik 15 dec 2006 kl. 16:30 skrev Tom Lane: Henrik Zagerholm [EMAIL PROTECTED] writes: Postgres has been designed as a server, and lots of implementation details might not make sense in an embedded context. you might be better served by SQLite, or some other such library. ... It is also quite crash resistant with the WAL implementation. One of the reasons it's crash resistant is exactly that it's *not* embedded, and thus not subject to corruption by application-side bugs. That concern is what has caused the developers to have zero interest in creating an embeddable variant. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] Needed files - embedded postgres
On fös, 2006-12-15 at 16:59 +0100, Henrik Zagerholm wrote: I think I need to specify what I mean with embedded. Its not that we try to embed it into an application. It is just run from a flash disk and the datafiles are put on standard raid attached disks. Its an embedded device not an embedded application. :) will you have concurrent connections? gnari ---(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
Re: [GENERAL] Needed files - embedded postgres
15 dec 2006 kl. 17:15 skrev Ragnar: On fös, 2006-12-15 at 16:59 +0100, Henrik Zagerholm wrote: I think I need to specify what I mean with embedded. Its not that we try to embed it into an application. It is just run from a flash disk and the datafiles are put on standard raid attached disks. Its an embedded device not an embedded application. :) will you have concurrent connections? Yes, but maximum 5 connections. ---(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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Needed files - embedded postgres
[EMAIL PROTECTED] (Tom Lane) writes: Henrik Zagerholm [EMAIL PROTECTED] writes: Postgres has been designed as a server, and lots of implementation details might not make sense in an embedded context. you might be better served by SQLite, or some other such library. ... It is also quite crash resistant with the WAL implementation. One of the reasons it's crash resistant is exactly that it's *not* embedded, and thus not subject to corruption by application-side bugs. That concern is what has caused the developers to have zero interest in creating an embeddable variant. Except, it appears to me that Henrik is intending to embed it not in the sense of being a shared library with application, but rather in being mostly hidden from users. It seems pretty reasonable to me to try to figure out a near-minimal footprint for PostgreSQL where, for instance, you might: a) Restrict TZ to GMT, and thereby eliminate most timezone data b) Restrict character encodings to C/SQL_ASCII, so that most of the encoding libraries in $PGHOME/lib/ could go away c) Drop pgxs (it's embedded - no further components are to be added) and #include files I'm finding my make install of PG 8.2 is ~15.5MB in size on Linux; if I trimmed out the above, that would probably cut the size of the install roughly in half. -- (reverse (concatenate 'string ofni.secnanifxunil @ enworbbc)) http://cbbrowne.com/info/rdbms.html No matter how much money you spend, you can't make a racehorse out of a pig. You can, however, make an awfully fast pig. -- An old saying about program efficiency ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Needed files - embedded postgres
15 dec 2006 kl. 17:40 skrev Chris Browne: [EMAIL PROTECTED] (Tom Lane) writes: Henrik Zagerholm [EMAIL PROTECTED] writes: Postgres has been designed as a server, and lots of implementation details might not make sense in an embedded context. you might be better served by SQLite, or some other such library. ... It is also quite crash resistant with the WAL implementation. One of the reasons it's crash resistant is exactly that it's *not* embedded, and thus not subject to corruption by application-side bugs. That concern is what has caused the developers to have zero interest in creating an embeddable variant. Except, it appears to me that Henrik is intending to embed it not in the sense of being a shared library with application, but rather in being mostly hidden from users. Now we are talking :) It seems pretty reasonable to me to try to figure out a near-minimal footprint for PostgreSQL where, for instance, you might: a) Restrict TZ to GMT, and thereby eliminate most timezone data b) Restrict character encodings to C/SQL_ASCII, so that most of the encoding libraries in $PGHOME/lib/ could go away I'm using UNICODE but thats a good thing right :) c) Drop pgxs (it's embedded - no further components are to be added) and #include files I'm finding my make install of PG 8.2 is ~15.5MB in size on Linux; if I trimmed out the above, that would probably cut the size of the install roughly in half. Even though size matters(!) its not that much of an issue on my system as I'm using 256 MB memory modules :) I've got it running OK. Now I just have to figure out how I easily can get libs working. I'm having some problems with plsql.so and such. When I'm done I'll probably post it on the wiki somewhere if it wuld interest anyone. Cheers, -- (reverse (concatenate 'string ofni.secnanifxunil @ enworbbc)) http://cbbrowne.com/info/rdbms.html No matter how much money you spend, you can't make a racehorse out of a pig. You can, however, make an awfully fast pig. -- An old saying about program efficiency ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] query on table name to return columns and data types?
How can I run a query based on a table name and get the column names and data types returned? Thanks!
Re: [GENERAL] query on table name to return columns and data types?
On Fri, 2006-12-15 at 12:26 -0500, blackwater dev wrote: How can I run a query based on a table name and get the column names and data types returned? Usually your language API will give you what you need for that. Sincerely, Joshua D. Drake Thanks! -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] query on table name to return columns and data types?
blackwater dev wrote: How can I run a query based on a table name and get the column names and data types returned? Thanks! Check out the columns view in the information_schema. -- erik jones [EMAIL PROTECTED] software development emma(r) ---(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
[GENERAL] FreeBSD shared memory settings
Hi Guys. I have a bit of a peculiar situation. I have t move a PostgreSQL 7.3 installation from a freeBSD 4.8 to a FreeBSD 6.1. I have PG 7.3 installed in a single directory, have moved the directory from one server to another. After instaling all the compat 4x and 5x libraries everything seems to be ok, except when i start postmaster, the following error comes up: IpcSemaphoreCreate: semget(key=5433001, num=17, 03600) failed: No space left on device This error does *not* mean that you have run out of disk space. It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exceeded. You need to raise the respective kernel parameter. Alternatively, reduce PostgreSQL's consumption of semaphores by reducing its max_connections parameter (currently 32). The PostgreSQL Administrator's Guide contains more information about configuring your system for PostgreSQL. I have adjusted kernel settings for shared memory, but no go. We have a native PG 8.1 on same machine (different port) working just fine. Also the 7.3 install (FreeBSD 4.8 binary) has all default values in postgres.conf for FreeBSD 4.8. Any ideas ? Thanx Paul
Re: [GENERAL] FreeBSD shared memory settings
On Fri, 2006-12-15 at 13:54 -0500, Paul Khavkine wrote: IpcSemaphoreCreate: semget(key=5433001, num=17, 03600) failed: No space left on device I have adjusted kernel settings for shared memory, but no go. We have a native PG 8.1 on same machine (different port) working just fine. Adjust the settings for semaphores. You probably want a high shared memory setting as well, but that is not what is causing this particular error. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] temp tables and function performance
Hi, I am trying to move up in the world with my sql and need to do the following... I have a subscribers table and I need to export to csv (semi-colon separated) certain fields - that is fine, but I also need to export a multi-select field from another table as one string (0 to n values separated by commas) per line. The problem being that to get the actual string I have to go via 4 other relations... and as I have 200k+ subscribers this takes a while. My idea (which seems to work, though I haven't tested fully as it takes too damn long!), was to do the following. I would REALLY appreciate any pointers as my sql has never been this challenged! CREATE OR REPLACE FUNCTION mytest() RETURNS integer AS $BODY$DECLARE kindy INTEGER; BEGIN create temporary table tmp_interests( id bigint, value character varying(100) ) WITHOUT OIDS ON COMMIT DROP; insert into tmp_interests select distinct si.subid, rbi.value from restem rbi, cats cc, trm_terms tt, subrest si where rbi.key = cc.name and cc.catid = tt.modcid and tt.tid = si.themeid; create temporary table tmp_subscribers( email character varying(200), format character varying(4), interests character varying(1000), ) WITHOUT OIDS ON COMMIT DROP; insert into tmp_subscribers Select email, format, my_interests(id) as interests from subscriber; GET DIAGNOSTICS kindy = ROW_COUNT; copy tmp_subscribers to '/home/myname/subs.csv' WITH CSV DELIMITER AS ';' NULL AS ''; GET DIAGNOSTICS kindy = ROW_COUNT; return kindy; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; ... CREATE OR REPLACE FUNCTION my_interests(bigint) RETURNS character varying AS $BODY$DECLARE subid ALIAS FOR $1; interests character varying; myinterest RECORD; BEGIN interests := ''; FOR myinterest IN execute 'select value from tmp_interests where id = ' || subid LOOP if interests = '' then interests := myinterest.value; else interests := interests || ',' || myinterest.value; end if; END LOOP; RETURN interests; END$BODY$ LANGUAGE 'plpgsql' VOLATILE; ... select mytest(); If there are errors then please just focus on the logic, as I have cut back on the number of columns (for clarity) and changed a lot of the real table/names... Am I going about it the right way? Is there a better way? Thanks heaps, Antoine ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] FreeBSD shared memory settings
In response to Paul Khavkine [EMAIL PROTECTED]: I have a bit of a peculiar situation. I have t move a PostgreSQL 7.3 installation from a freeBSD 4.8 to a FreeBSD 6.1. I have PG 7.3 installed in a single directory, have moved the directory from one server to another. After instaling all the compat 4x and 5x libraries everything seems to be ok, except when i start postmaster, the following error comes up: IpcSemaphoreCreate: semget(key=5433001, num=17, 03600) failed: No space left on device This error does *not* mean that you have run out of disk space. It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exceeded. You need to raise the respective kernel parameter. Alternatively, reduce PostgreSQL's consumption of semaphores by reducing its max_connections parameter (currently 32). The PostgreSQL Administrator's Guide contains more information about configuring your system for PostgreSQL. I have adjusted kernel settings for shared memory, but no go. We have a native PG 8.1 on same machine (different port) working just fine. Did you add the memory requirements for the two installations together when you set the sysctls? Use the ipcs command to see what you've got. -- Bill Moran Collaborative Fusion Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] TIMESTAMP WITHOUT TIME ZONE
Hi Tom, Randy, et al, I'm not fully caught up with my Readings In Postgres, but this post caught my eye and raised a concern... Tom Lane [EMAIL PROTECTED] wrote: Randy Shelley [EMAIL PROTECTED] writes: The java.sql.Timestamp does not store any timezone info, just nano seconds from a date. One would hope that it's implicitly referenced to GMT, though, not some free-floating value that means who-knows-what. I think your fundamental error is in using timestamp without time zone in the database. Try with-time-zone if you want consistent results across clients in different zones. I sure hope there's no issue with using timestamp without timezone _anywhere_ in the PG world because, quite frankly, timezone just doesn't cut it. There are so many issues that I don't think I have time to justice to them here in a short email, but, just so we've all got some idea: First, you need at least minute, if not second offset from GMT to have anything like a comprehensive shot at timezone. Hour-based time zones are simply insufficient. There are lots of places in the world with non-hour offsets from GMT. The second biggest issue is probably the plethora of daylight savings time schemes - and they change over time: notably within the last year, a U.S. community muffed handling such a chnage with their Canadian neighbors. And there are the timings of changes, too - do automated daemons know when the time changes? It's quite different in various parts of the world. Do you blindly follow your system clock? LOTS of questions here that are none of PG's business, but are vital to a production system always getting it right. Third, any presumption about when which version of a time should be valid is bound to cause major errors at some point or another. One can't just always hand the user a timestamp in local time on client ends because you don't know what kind of local processing they might wish to do outside of the database engine, not the least of which is the type of question, was it after their business hours? - a local-to-local question! Therefore, as a minimum, you _must_ provide transform functions, one to the other, and let the caller ask for what they wanted. This is particularly tricky when it comes to database join statements - did you give the query the GMT version, or local version?! -oy- The headaches this can cause, even among experts. We at Science Tools use timestamp without timezone as the basis of our handling our customer's data correctly. It's configurable, but by default all data going into a database is converted to GMT by our software, outside the database engine, unless explicitly directed otherwise. To handle the optionality of this, all join operations happen using what we call database time, so if a db doesn't store in GMT for some reason, we still know what to do (for example, converting to the equivalent local time of the server). We track client's GMT offsets - stored in the db, of course - so we've got every client's offset data when needed, etc, etc, etc. ...I PRESUME there's nothing broken about timestamp without timezone within either the engine or the JDBC drivers, but I'd also caution to always punt on the question of whether or not someone should or shouldn't use Postgres' time zone feature. Perhaps a for most people qualifier, or, when every user is in an hour-offset from GMT timezone, etc., but even then, multi-time-zone applications need to be VERY warry. Respectfully, Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 [EMAIL PROTECTED], http://ScienceTools.com/ ---(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
Re: [GENERAL] TIMESTAMP WITHOUT TIME ZONE
Richard Troy [EMAIL PROTECTED] writes: I'm not fully caught up with my Readings In Postgres, but this post caught my eye and raised a concern... AFAIK, all the reasons you enumerate are good reasons to delegate the problem to a timestamp WITH time zone column. First, you need at least minute, if not second offset from GMT to have anything like a comprehensive shot at timezone. Got that. The second biggest issue is probably the plethora of daylight savings time schemes - and they change over time: Got that, if you keep your zic data files up to date. Third, any presumption about when which version of a time should be valid is bound to cause major errors at some point or another. No doubt, but doing conversions outside the database is surely no safer than doing them inside. We at Science Tools use timestamp without timezone as the basis of our handling our customer's data correctly. It's configurable, but by default all data going into a database is converted to GMT by our software, outside the database engine, unless explicitly directed otherwise. I think you're just reinventing timestamp with time zone. Maybe if you need to work with other DBs besides Postgres, you'll have to program to the lowest common denominator, but PG gets all these things right. regards, tom lane ---(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] TIMESTAMP WITHOUT TIME ZONE
On Wed, 13 Dec 2006, Richard Huxton wrote: Randy Shelley wrote: I get different result if I query it from my workstation(US/Easter timezone) and from the server (GMT timezone). A data type of timestamp without time zone should not do any conversions. The java.sql.Timestamp does not store any timezone info, just nano seconds from a date. Some where there is a timezone conversion happening. Why and how do I prevent it? Tom's stated the problem, but to expand a little. Your java.sql.Timestamp is an absolute point in time (presumably measured from midnight 1970-01-01 GMT). Note that without the GMT there, it would not be an absolute point in time since midnight in London was different from midnight in New York. The timestamp without time zone is NOT an absolute point in time, it is only meaningful for a single time zone. WRONG. It's a point in time that's meaningful to ME even if YOU can't tell where in the universe it's supposed to represent relative to any other point. The timestamp with time zone IS an absolute time, but it DOES NOT record the timezone you enter. Rather, it is equivalent to your java.sql.Timestamp. If you have a client in London and another in New OHMYGODYOUJUSTHAVETOBEWRONG!!! Let me get this straight; You're saying that you SUPPORT the idea of conversion being performed by the database (or it's cohort, the JDBC library in this case) when I, the author of an application using the data, depend on my database to give me back the data I gave it?! HORRIBLY BROKEN IF SO. York, both will display the same absolute time but in their local time zone. So, I might see 14:00+00 whereas a New-Yorker might see 08:00+05 (if that's the right time-zone). You can ask for a specific time-zone too (with AT TIME ZONE '...'). I think the biggest problem is that with time zone sounds like it's storing a fixed time-zone when you insert a value. ...I missed the start of this thread but the CORRECT behavior for Postgres regarding TIMESTAMP WITHOUT TIMEZONE is to take a timestamp in whatever form _I_ care to give it and return it _exactly,_ unmodified in any way. See my post from a few minutes ago, but simply put, time/date is at least as challenging as money or multibyte character. And, simply put, the Postgres implementation of timezone is INSUFFICIENT. PLEASE tell me that when I give a PostgreSql server, through JDBC, a timestamp, stored in a TIMESTAMP WITHOUT TIMEZONE attribute, that it'll always give me back the _same_exact_bits_ as what I gave it! Anything else is horribly broken and is, to quote Tom Lane, about as good a definition of corrupted data as I can think of. - with appologies to Tom, of course. Regards, Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 [EMAIL PROTECTED], http://ScienceTools.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] A major rewrite of the Postgres OLE DB Provider.
Hi all, Because of various things, I have needed to move some stuff that I've been working on onto a Postgres DB. It makes very heavy use of ADO from Excel, and so I was running into a number of little annoyances with the existing OLE DB provider... Especially I could not get parameterised queries to run, nor could I run multiple commands in one block (which I need for speed to load big chunks of data). So, I started hacking on the Provider, and ended up almost completely rewriting it. I have placed a copy at: http://people.freebsd.org/~reg/pgoledb-20061215.zip Things that it has gained: - Support for the IColumnsRowset interface, which means that ADO's client cursor engine doesn't need to parse the SQL to make up UPDATE and INSERT queries. - Support for IDBInfo and IDBSchemaRowset interfaces, which also help ADO CCE. - Runs multiple queries (using PQexec) if you pass a command with no parameters and IID_NULL for the return record set. - Proper type handling. - Passing all of the connection string options (hostaddr, ssl, etc.) - Lots of bug fixes. - Much cleaner code (IMHO), no more STL or exceptions. Things it has lost: - IMultipleRowsets. libpq can't return multiple results. What was there was a complete hack. - Parsing of parameters on stored procedures. This was a hack, which was being done at the wrong place in the code. - Support for some types (_text, utinyint, varcharci) - TIMESTAMP WITH TIMEZONE is reported in GMT not local, since there is no OLE DB type for this (so you can't go backwards). - Probably some other things... I've been testing the code some, and it's working with my application, but there are probably still many bugs. I would love it if people could also test it and report errors! I'm trying to get the OLE DB conformance tests up and running, and hopefully I will find more bugs that way. I still need to: - redo some of the Schema support (realised my design was bad...) - tidy up some of the tracing. - prepared queries. - there are a number of properties where I'm not sure of the right value. - add more support for pg_types (especially the array/vector types). - check threading and locking. - check error reporting. - probably a few other things... I hope this is useful to someone. I would welcome any patches to add other features. I'm managing this in a local CVS repository at the moment... Not quite sure what I'm going to do about this and the PgFoundry project yet. Regards, -Jeremy ---(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] TIMESTAMP WITHOUT TIME ZONE
On Fri, Dec 15, 2006 at 12:10:24PM -0800, Richard Troy wrote: I think your fundamental error is in using timestamp without time zone in the database. Try with-time-zone if you want consistent results across clients in different zones. I sure hope there's no issue with using timestamp without timezone _anywhere_ in the PG world because, quite frankly, timezone just doesn't cut it. There are so many issues that I don't think I have time to justice to them here in a short email, but, just so we've all got some idea: First, you need at least minute, if not second offset from GMT to have anything like a comprehensive shot at timezone. Hour-based time zones are simply insufficient. There are lots of places in the world with non-hour offsets from GMT. I don't think you understand the meaning of timestamp with timezone. No timezone is stored. What is stored it number of seconds since epoch and that is rotated to the client's timezone on display. The second biggest issue is probably the plethora of daylight savings time schemes - and they change over time: notably within the last year, a U.S. community muffed handling such a chnage with their Canadian neighbors. And there are the timings of changes, too - do automated daemons know when the time changes? It's quite different in various parts of the world. Do you blindly follow your system clock? LOTS of questions here that are none of PG's business, but are vital to a production system always getting it right. Quite, which is why the timezone is not stored, too ambiguous. Automated daemons only use seconds since epoch. hour/minutes/seconds are for people, not computers. Third, any presumption about when which version of a time should be valid is bound to cause major errors at some point or another. One can't just always hand the user a timestamp in local time on client ends because you don't know what kind of local processing they might wish to do outside of the database engine, not the least of which is the type of question, was it after their business hours? - a local-to-local question! Therefore, as a minimum, you _must_ provide transform functions, one to the other, and let the caller ask for what they wanted. This is particularly tricky when it comes to database join statements - did you give the query the GMT version, or local version?! -oy- The headaches this can cause, even among experts. There are transform functions, the AT TIME ZONE 'blah' construct will convert between timestamp with timezone and timestamp without timezone. For join statements, postgres doesn't let you compare the with and without timezone variants. You have to specify the timezone you wish to compare in, or the comparison has no meaning. We at Science Tools use timestamp without timezone as the basis of our handling our customer's data correctly. It's configurable, but by default all data going into a database is converted to GMT by our software, outside the database engine, unless explicitly directed otherwise. To handle the optionality of this, all join operations happen using what we call database time, so if a db doesn't store in GMT for some reason, we still know what to do (for example, converting to the equivalent local time of the server). We track client's GMT offsets - stored in the db, of course - so we've got every client's offset data when needed, etc, etc, etc. You can do it that way. Alternatively, you can give the timezone to postgres and let it do the conversion to GMT. That at least has the advantage that you know all clients are using the same timezone definitions. Internally, postgres only uses GMT. ...I PRESUME there's nothing broken about timestamp without timezone within either the engine or the JDBC drivers, but I'd also caution to always punt on the question of whether or not someone should or shouldn't use Postgres' time zone feature. Perhaps a for most people qualifier, or, when every user is in an hour-offset from GMT timezone, etc., but even then, multi-time-zone applications need to be VERY warry. It looks like you understand the issues, so if you're using it correctly, fine. Most people do not, and many try to use timestamp without timezone to store local times, which does not work. The good rule of thumb is basically: - timestamp with timezone is for recording an instant in time (seconds since epoch). - timestamp without timezone is for recording what appears on a clock face, that will be constant wherever the client is. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org 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] TIMESTAMP WITHOUT TIME ZONE
Richard Troy [EMAIL PROTECTED] writes: See my post from a few minutes ago, but simply put, time/date is at least as challenging as money or multibyte character. And, simply put, the Postgres implementation of timezone is INSUFFICIENT. Really? We do all the things you have listed, and more. AFAICS what you have described is an outside-the-database reinvention of PG's semantics for timestamp with time zone. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] TIMESTAMP WITHOUT TIME ZONE
Richard Troy [EMAIL PROTECTED] writes: See my post from a few minutes ago, but simply put, time/date is at least as challenging as money or multibyte character. And, simply put, the Postgres implementation of timezone is INSUFFICIENT. Really? We do all the things you have listed, and more. AFAICS what you have described is an outside-the-database reinvention of PG's semantics for timestamp with time zone. regards, tom lane Hi Tom, thanks for the prompt reply... Not much time - just a few moments to reply and then I have to get on with my customer's deliverables... ...ISTM I took the meaning TIMESTAMP WITH TIMEZONE literally, while in reality the PG team has implemented the concept but without timezone in the database as a part of user data. I confess I never double checked the implementation details thereof as it sounds obvious you're including time zone data in the data stored by the server. Also, of the two RDBMSes in which I personally know the internal implementations of date/time, and of the ones I've talked with the engineers about, none of them get it right or even begin to get it right, so it never occured to me that Postgres would do so much better. Sounds like the PG team has once again thought about the problem from a different perspective and came up with a better answer. That said, nobody has yet assured me that when I give a timestamp I get it back unmolested. As you correctly recall, yes, Science Tools supports five RDBMSes and need to do so as cleanly and consistently as we can, and yes, it's pretty hard to do all the testing, given all the permutations. And, we're in the process of certifying both Ingres (which will make it, I'm sure) and ANTS (which might not). So, seven RDBMS choices... -shrug- I'd appreciate a clean yes/no; From a Java application, throught PG in both directions, the same timestamp comes back that was handed to the JDBC driver so long as it's stored in a timestamp without time zone attribute, nomatter neither where on earth the insert/update originates, nor where the select originates? Same bits, yes? Otherwise, Houston, we've got a problem. Thanks again, Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 [EMAIL PROTECTED], http://ScienceTools.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] TIMESTAMP WITHOUT TIME ZONE
Richard Troy wrote: I'd appreciate a clean yes/no; From a Java application, throught PG in both directions, the same timestamp comes back that was handed to the JDBC driver so long as it's stored in a timestamp without time zone attribute, nomatter neither where on earth the insert/update originates, nor where the select originates? Same bits, yes? Otherwise, Houston, we've got a problem. If you pass a timestamp without time zone, the given timestamp will be given back to you on request, no changes applied, whatever the timezone either the inserter or the extracter are on. If you pass a timestamp with time zone, the time will be rotated to UTC on insert depending on the inserter's timezone (thus it's stored as UTC), and will be rotated back to the extracter's timezone when you extract it. Note that both timezones may be different, so the numbers you get may be different than the numbers you put in, but they will signal exactly the same instant in time (in the appropriate time zone). If you want to know what time zone the inserter used, you would store that in a separate column. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] TIMESTAMP WITHOUT TIME ZONE
Richard Troy [EMAIL PROTECTED] writes: That said, nobody has yet assured me that when I give a timestamp I get it back unmolested. Well, as far as the backend is concerned you do get it back unmolested (up to the limits of float roundoff error, if you use float timestamps): * unconditionally for timestamp without timezone; * if your TimeZone is the same as it was on insert, for timestamp with timezone. (If you change the timezone setting you'll get a suitably rotated value, and no that's not a bug.) Also you have to be careful not to pass in a nonexistent or ambiguous value during DST changes, else you might get an adjusted value back. If you always run with TimeZone = GMT then there's effectively no difference between timestamp with and without time zone. Now the original context of this thread was what happens with the JDBC driver, and that I'm not sure about --- they have a problem because they have to map both types to the same Java type, and it doesn't fit real well. But you'd be better off asking on pgsql-jdbc if you want the gory details about that. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] TIMESTAMP WITHOUT TIME ZONE
You asked:
I'd appreciate a clean yes/no; From a Java application, throught PG in
both directions, the same timestamp comes back that was handed to the
JDBC
driver so long as it's stored in a timestamp without time zone
attribute, nomatter neither where on earth the insert/update originates,
nor where the select originates?
No. It returns the number of seconds since epoch, which is 1970-01-01
00:00 GMT. If you insert '2006-12-15 20:00 EST', it basically inserts
the result of EXTRACT('epoch' FROM '2006-12-15 20:00 EST'), which is
1166230800. It is a normal Universal Time Coordinate (UTC).
TIMESTAMP WITH TIME ZONE will precisely identify any point in time. It
does not store the time zone information from the client. When you the
later select the field, it returns a properly formatted string with the
time zone the server is configured for in postgresql.conf. You can also
use AT TIME ZONE to specify a different zone if you wish.
If you want to store the time zone information the client used when it
stored the time (which is generally useless data) I suggest a varchar
field that stores the ISO acronymn for the relevant time zone or a
numeric field that stores the time adjustment in hours.
Examples (this server is EST, or GMT -05):
postgres=# SELECT '2006-12-15 20:00 PST'::TIMESTAMP WITH TIME ZONE;
timestamptz
2006-12-15 23:00:00-05
(1 row)
postgres=# select '2006-12-15 20:00 PST'::TIMESTAMP WITH TIME ZONE AT
TIME ZONE
'GMT';
timezone
-
2006-12-16 04:00:00
(1 row)
--
Brandon Aiken
CS/IT Systems Engineer
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Richard Troy
Sent: Friday, December 15, 2006 5:18 PM
To: Tom Lane
Cc: Richard Huxton; Randy Shelley; pgsql-general@postgresql.org
Subject: Re: [GENERAL] TIMESTAMP WITHOUT TIME ZONE
Richard Troy [EMAIL PROTECTED] writes:
See my post from a few minutes ago, but simply put, time/date is at
least
as challenging as money or multibyte character. And, simply put, the
Postgres implementation of timezone is INSUFFICIENT.
Really? We do all the things you have listed, and more. AFAICS what
you have described is an outside-the-database reinvention of PG's
semantics for timestamp with time zone.
regards, tom lane
Hi Tom,
thanks for the prompt reply... Not much time - just a few moments to
reply
and then I have to get on with my customer's deliverables... ...ISTM I
took the meaning TIMESTAMP WITH TIMEZONE literally, while in reality
the
PG team has implemented the concept but without timezone in the
database
as a part of user data. I confess I never double checked the
implementation details thereof as it sounds obvious you're including
time
zone data in the data stored by the server. Also, of the two RDBMSes in
which I personally know the internal implementations of date/time, and
of
the ones I've talked with the engineers about, none of them get it right
or even begin to get it right, so it never occured to me that Postgres
would do so much better. Sounds like the PG team has once again thought
about the problem from a different perspective and came up with a better
answer.
That said, nobody has yet assured me that when I give a timestamp I get
it
back unmolested. As you correctly recall, yes, Science Tools supports
five
RDBMSes and need to do so as cleanly and consistently as we can, and
yes,
it's pretty hard to do all the testing, given all the permutations. And,
we're in the process of certifying both Ingres (which will make it, I'm
sure) and ANTS (which might not). So, seven RDBMS choices... -shrug-
I'd appreciate a clean yes/no; From a Java application, throught PG in
both directions, the same timestamp comes back that was handed to the
JDBC
driver so long as it's stored in a timestamp without time zone
attribute, nomatter neither where on earth the insert/update originates,
nor where the select originates? Same bits, yes? Otherwise, Houston,
we've got a problem.
Thanks again,
Richard
--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
[EMAIL PROTECTED], http://ScienceTools.com/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(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] updating a view
ya'da man tom! applied the patch, rebuilt, and postgres doesn't crash anymore. fixed my syntax error, and all is working properly. phew, my first experience with sql thanks! matt On Thu, Dec 14, 2006 at 09:23:40PM -0500, Tom Lane wrote: [EMAIL PROTECTED] writes: so maybe i'm using the word crash too liberally. this is the error message i get: server closed the connection unexpectedly Looks like a crash to me. version is 8.2.0, just downloaded a few days ago. There's a known bug in 8.2.0 having to do with failing out of a subtransaction, which would include a plperlu spi_exec() to a query that gets an error. So I'm thinking that might be what you're hitting. If you built from source, you could apply this patch: http://archives.postgresql.org/pgsql-committers/2006-12/msg00063.php regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
