[GENERAL] actual SQL statement
Hello, can I find out, what SQL statement the PostgreSQL-server is executing? Thanks Michaela
Re: [GENERAL] actual SQL statement
am 23.02.2006, um 10:58:12 +0100 mailte MG folgendes: Hello, can I find out, what SQL statement the PostgreSQL-server is executing? select * from pg_stat_activity; You should enable 'stats_command_string' in postgresql.conf. Btw.: please, no HTML HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Disable FK constarints
Hi, I try to load an entire database ( from Oracle via ora2pg ) create table is OK there are some FK's in the database I did insert's with : BEGIN TRANSACTION; SET CONSTRAINTS ALL DEFERRED; insert ... END TRANSACTION; I get: ERROR: insert or update on table transactions violates foreign key constraint How can I get rid off the FK CONSTRAINTS during the load ??? Thanks in advance... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How do I use the backend APIs
On Tue, Feb 21, 2006 at 02:41:13AM -0800, Chad wrote: Thanks Martijn/Alban, This look interesting. I'll make some time to try this problem out using your approach. I have a few questions like: -Could I skip the FETCH FORWARD and go straight to the FETCH BACKWARD i.e. declare cursor to be at Mal and go backwards from there or is the cursor limited to going backward only as far as Mal? Only as far back as Mal. However, if you set your original query to ORDER BY blah DESC, then FETCH FORWARD would scan backward through the index. -Does the DB avoid transferring the data until the FETCH command? Of course. It would kind of defeat the purpose to do otherwise. -When rows change in between opening the cursor and fetching the changed rows, will the FETCH retrieve the new data or is a snapshot taken when the cursor is declared ? Standard visibility rules apply. READ COMMITTED shows anything committed, even after you've started. SERIALIZABLE gives you a consistant snapshot. -What about concurrency? If a cursor is kept open while other transactions change the same table or does it cause those writer transactions to block? Perhaps this is configurable. Some as normal. PostgreSQL doesn't acquire any locks for plain SELECTs so no risk there... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [GENERAL] now() time off
On Wed, Feb 22, 2006 at 06:35:55PM -0600, Jim C. Nasby wrote: On Wed, Feb 22, 2006 at 04:46:35PM -0600, Scott Marlowe wrote: Sounds like a time zone issue. I'd start looking there. I've been bitten by this before as well. I'd be in favor of adding an option such that postmaster would refuse to start if TZ was something other than UTC; I'd much rather that then have a bunch of data get screwed up... Alternativly you could just set the timezone parameter in the postgresql configuration... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
[GENERAL] Is there a way to check which indexes are being used for a table
We have a 50 GB database (currently using postgresql 8.1.1) with a few hundred tables. There are a few larger (2-5 million rows) tables with multiple indexes on them, some being unique, some not. Now, I am pretty sure some of the indexes are pretty useless and are never used but is there a way to see which indexes have been used on a table (and how many times) and which haven't? Statistics is turned on for a database, I can see number of sequential scans and index scans for instance, but I would like to know which indexes have been used and how many times. Tnx in advance Dragan Matic ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is there a way to check which indexes are being used
On fim, 2006-02-23 at 14:15 +0100, Dragan Matic wrote: We have a 50 GB database (currently using postgresql 8.1.1) with a few hundred tables. There are a few larger (2-5 million rows) tables with multiple indexes on them, some being unique, some not. Now, I am pretty sure some of the indexes are pretty useless and are never used but is there a way to see which indexes have been used on a table (and how many times) and which haven't? Statistics is turned on for a database, I can see number of sequential scans and index scans for instance, but I would like to know which indexes have been used and how many times. select * from pg_stat_user_indexes ; Tnx in advance Dragan Matic ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] ExclusiveLock without a relation in pg_locks
Would connections to a database require crating an extra ExclusiveLock? We have some connections to the database that happen to be idle in transaction and their pids have a granted Exclusive Lock in pg_locks. I cannot discern the tables where the ExclusiveLock is being held because the relation field is blank. Moreover, there are other connections to the database coming from the same ip address as that of the connection with the ExclusiveLock. Some of the pids of these other connections seem to have different kinds of locks (AccessShareL0ck) so I am not quite sure why the pids with the ExclusiveLocks are necessary. How could I find out the tables that are being locked when I see an ExclusiveLock in pg_locks.
[GENERAL] upgrade PostgreSQL 8.x on production FreeBSD
Could anybody point to an on-line resource about the steps involved with upgrading of PostgreSQL 8.x on a production FreeBSD 5.4 with minimum downtime (i.e. 1 - stop db so that no changes happen 2 - dump 3 - upgrade (ports) 4 - import 5- start db). Thanks, Iv Ray ---(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] ExclusiveLock without a relation in pg_locks
On Thu, Feb 23, 2006 at 08:54:36AM -0500, Carlos Oliva wrote: Would connections to a database require crating an extra ExclusiveLock? We have some connections to the database that happen to be idle in transaction and their pids have a granted Exclusive Lock in pg_locks. I cannot discern the tables where the ExclusiveLock is being held because the relation field is blank. AIUI each backend has an exclusive lock on its own transaction. If you're idle in transaction you've acquired a lock on your transaction so other people can wait on you if necessary. That's why there's nothing in the relation field, because it's not a table lock. Moreover, there are other connections to the database coming from the same ip address as that of the connection with the ExclusiveLock. Some of the pids of these other connections seem to have different kinds of locks (AccessShareL0ck) so I am not quite sure why the pids with the ExclusiveLock's are necessary. AccessShareLock is the normal lock you acquire when selecting data. It's doesn't really do much other than say I'm using this table, don't delete it. See the documentation for all the details. How could I find out the tables that are being locked when I see an ExclusiveLock in pg_locks. It's rare to see exclusive locks on tables except for things like VACUUM FULL and CLUSTER and other such admin commands... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [GENERAL] ExclusiveLock without a relation in pg_locks
On Thu, Feb 23, 2006 at 08:54:36AM -0500, Carlos Oliva wrote: Would connections to a database require crating an extra ExclusiveLock? We have some connections to the database that happen to be idle in transaction and their pids have a granted Exclusive Lock in pg_locks. I cannot discern the tables where the ExclusiveLock is being held because the relation field is blank. http://www.postgresql.org/docs/8.1/interactive/view-pg-locks.html Every transaction holds an exclusive lock on its transaction ID for its entire duration. If one transaction finds it necessary to wait specifically for another transaction, it does so by attempting to acquire share lock on the other transaction ID. That will succeed only when the other transaction terminates and releases its locks. If the relation column is null then you're probably seeing these transaction ID locks. How could I find out the tables that are being locked when I see an ExclusiveLock in pg_locks. An easy way to convert a relation's oid to its name is to cast it to regclass: SELECT relation::regclass AS relname, * FROM pg_locks; -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc
Hello, May I know where I can find some online documents about mapping the integer values to the following SQL types please? For example, if I have value 1 , so that I know 1 is mapped to SQL_CHAR; if I have value 3, so that I know 3 is mapped to SQL_NUMERIC. Thanks a lot, Emi Data Types The following data types are supported: SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_NUMERIC SQL_DECIMAL SQL_SMALLINT SQL_INTEGER SQL_REAL SQL_FLOAT SQL_DOUBLE SQL_BIT SQL_TINYINT SQL_BIGINT SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_INTERVALS (all types) ---(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] Is there a way to check which indexes are being used
On Feb 23, 2006, at 8:47 AM, Ragnar wrote: select * from pg_stat_user_indexes ; which level of stats do I need to enable this? block level or row level or both? thanks. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] upgrade PostgreSQL 8.x on production FreeBSD
On Feb 23, 2006, at 9:21 AM, [EMAIL PROTECTED] wrote: Could anybody point to an on-line resource about the steps involved with upgrading of PostgreSQL 8.x on a production FreeBSD 5.4 with minimum downtime (i.e. 1 - stop db so that no changes happen 2 - dump 3 - upgrade (ports) 4 - import 5- start db). Upgrade from what to what? If you're upgrading from 8.0.x to 8.0.y then there is no dump/reload. If youre going from 8.1 to 8.1 then there is dump/reload OR you can use some sort of replication such as slony to copy the data, then have a few seconds of downtime while you turn off the older DB and put the 8.1 in its place. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ExclusiveLock without a relation in pg_locks
Thank you very much for your answer. I think that I am seeing those self transaction id locks as ExclusiveLocks Would you expect to see an ExclusiveLock with a query of type Select (not Select Update or Update or Insert)? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Fuhr Sent: Thursday, February 23, 2006 10:05 AM To: Carlos Oliva Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] ExclusiveLock without a relation in pg_locks On Thu, Feb 23, 2006 at 08:54:36AM -0500, Carlos Oliva wrote: Would connections to a database require crating an extra ExclusiveLock? We have some connections to the database that happen to be idle in transaction and their pids have a granted Exclusive Lock in pg_locks. I cannot discern the tables where the ExclusiveLock is being held because the relation field is blank. http://www.postgresql.org/docs/8.1/interactive/view-pg-locks.html Every transaction holds an exclusive lock on its transaction ID for its entire duration. If one transaction finds it necessary to wait specifically for another transaction, it does so by attempting to acquire share lock on the other transaction ID. That will succeed only when the other transaction terminates and releases its locks. If the relation column is null then you're probably seeing these transaction ID locks. How could I find out the tables that are being locked when I see an ExclusiveLock in pg_locks. An easy way to convert a relation's oid to its name is to cast it to regclass: SELECT relation::regclass AS relname, * FROM pg_locks; -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ExclusiveLock without a relation in pg_locks
Thank you very much for your answer. I think that I am seeing those self transaction id locks as ExclusiveLock Would you expect to see an ExclusiveLock with a query of type Select (not Select Update or Update or Insert)? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Martijn van Oosterhout Sent: Thursday, February 23, 2006 10:04 AM To: Carlos Oliva Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] ExclusiveLock without a relation in pg_locks On Thu, Feb 23, 2006 at 08:54:36AM -0500, Carlos Oliva wrote: Would connections to a database require crating an extra ExclusiveLock? We have some connections to the database that happen to be idle in transaction and their pids have a granted Exclusive Lock in pg_locks. I cannot discern the tables where the ExclusiveLock is being held because the relation field is blank. AIUI each backend has an exclusive lock on its own transaction. If you're idle in transaction you've acquired a lock on your transaction so other people can wait on you if necessary. That's why there's nothing in the relation field, because it's not a table lock. Moreover, there are other connections to the database coming from the same ip address as that of the connection with the ExclusiveLock. Some of the pids of these other connections seem to have different kinds of locks (AccessShareL0ck) so I am not quite sure why the pids with the ExclusiveLock's are necessary. AccessShareLock is the normal lock you acquire when selecting data. It's doesn't really do much other than say I'm using this table, don't delete it. See the documentation for all the details. How could I find out the tables that are being locked when I see an ExclusiveLock in pg_locks. It's rare to see exclusive locks on tables except for things like VACUUM FULL and CLUSTER and other such admin commands... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Cannot load number rows
Hi, I try to insert 55000 rows into a table The firsts insert seems ok (few rows are OK ) then after a while I et the following erorrs psql:c:/temp/usrweb/transactions.sql:55420: ERROR: current transaction is abort ed, commands ignored until end of transaction block What may be the problem ? Something I have to tune tio successfully load my data ? Thanks in advance ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Cannot load number rows
On Thu, 2006-02-23 at 10:15, [EMAIL PROTECTED] wrote: Hi, I try to insert 55000 rows into a table The firsts insert seems ok (few rows are OK ) then after a while I et the following erorrs psql:c:/temp/usrweb/transactions.sql:55420: ERROR: current transaction is abort ed, commands ignored until end of transaction block What may be the problem ? Something I have to tune tio successfully load my data ? When you see that error, it means there was a previous error, and postgresql isn't processing any more of your transaction. We need to see the FIRST error you got. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] upgrade PostgreSQL 8.x on production FreeBSD
[EMAIL PROTECTED] wrote: Could anybody point to an on-line resource about the steps involved with upgrading of PostgreSQL 8.x on a production FreeBSD 5.4 with minimum downtime (i.e. 1 - stop db so that no changes happen 2 - dump 3 - upgrade (ports) 4 - import 5- start db). You want to make your dump with the pg_dump of the new version. I'm not sure you can build the tools seperately (that'd be excellent, but my FreeBSD boxes are at home)... I think it should be: 1 - make postgresql, 2 - make install postgresql-tools 3 - dump 4 - stop db 5 - make install postgresql 6 - restore 7 - start db -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Disable FK constarints
[EMAIL PROTECTED] writes: I did insert's with : BEGIN TRANSACTION; SET CONSTRAINTS ALL DEFERRED; insert ... END TRANSACTION; I get: ERROR: insert or update on table transactions violates foreign key constraint SET CONSTRAINTS only affects those constraints that are marked DEFERRABLE, which I believe is not the default. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to specify infinity for intervals ?
Thanks to all for the suggestions. For the time being I will stay with using NULL. I will also stay with the hope that one day before long we will have 'infinite'::interval. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How do I use the backend APIs
Martijn van Oosterhout kleptog@svana.org writes: On Tue, Feb 21, 2006 at 02:41:13AM -0800, Chad wrote: -When rows change in between opening the cursor and fetching the changed rows, will the FETCH retrieve the new data or is a snapshot taken when the cursor is declared ? Standard visibility rules apply. READ COMMITTED shows anything committed, even after you've started. SERIALIZABLE gives you a consistant snapshot. I believe that a cursor always shows a snapshot --- whether the transaction is READ COMMITTED or SERIALIZABLE only affects whether the snapshot is current as of the DECLARE CURSOR command or the transaction's BEGIN command. In either case you won't see changes occurring after the cursor is opened. 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] Cannot load number rows
[EMAIL PROTECTED] wrote: Hi, I try to insert 55000 rows into a table The firsts insert seems ok (few rows are OK ) then after a while I et the following erorrs psql:c:/temp/usrweb/transactions.sql:55420: ERROR: current transaction is abort ed, commands ignored until end of transaction block Before this message is the actual error message. Once you have an error in a transaction all further commands will be ignored until you roll back that transaction. -- Richard Huxton Archonet Ltd ---(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] Limitations : Number of ...
Two quick questions : What is the max number of databases on a PostgreSQL server? What is the max number of tables in a database ? Thanks Jon D Cruz
Re: [GENERAL] ExclusiveLock without a relation in pg_locks
On Thu, Feb 23, 2006 at 11:08:07AM -0500, Carlos Oliva wrote: Thank you very much for your answer. I think that I am seeing those self transaction id locks as ExclusiveLocks Would you expect to see an ExclusiveLock with a query of type Select (not Select Update or Update or Insert)? Not in general, unless perhaps the select called a function that acquired such a lock. The Concurrency Control chapter in the documentation has a section on lock types and the commands that acquire them: http://www.postgresql.org/docs/8.1/interactive/explicit-locking.html Are you just curious or are you seeing such a situation? -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Limitations : Number of ...
am 23.02.2006, um 10:00:01 -0800 mailte Jon Cruz folgendes: Two quick questions : What is the max number of databases on a PostgreSQL server? What is the max number of tables in a database ? Please, read our FAQ. http://www.postgresql.org/docs/faqs.FAQ.html And yes: we don't have limitations ;-) Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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] ExclusiveLock without a relation in pg_locks
Yes. I am seeing that situation often in our database. The query field of pg_stat_activity is SELECT ..., not SLECT UPDATE or UPDATE or INSERT or DELETE. I was expecting the query to say something like SLECT UPDATE or something like that. Also the query seems to have just columns in the select statement; not functions. I will look further into these queries in case that they are using functions. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Fuhr Sent: Thursday, February 23, 2006 1:09 PM To: Carlos Oliva Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] ExclusiveLock without a relation in pg_locks On Thu, Feb 23, 2006 at 11:08:07AM -0500, Carlos Oliva wrote: Thank you very much for your answer. I think that I am seeing those self transaction id locks as ExclusiveLocks Would you expect to see an ExclusiveLock with a query of type Select (not Select Update or Update or Insert)? Not in general, unless perhaps the select called a function that acquired such a lock. The Concurrency Control chapter in the documentation has a section on lock types and the commands that acquire them: http://www.postgresql.org/docs/8.1/interactive/explicit-locking.html Are you just curious or are you seeing such a situation? -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Temporal Databases
Hi everyone, I'm focus on temporal databases (not temporary), and I want to know if anyone here is studying this tecnologies too. So, we can exchange knowlegment. Specifically, anyone who is trying to implement on postgresql the aspect of time (temporal). These researches are lead by Richard Snodgrass. So, anyone who have something to share, please contact me! Thanks!!!
[GENERAL] Error correction to FAQ
Within the FAQ, Point 4.5 there is:[]The size of the PostgreSQL database file containing this data can be estimated as 6.4 MB: []685 database pages * 8192 bytes per page = 5,611,520 bytes (5.6 MB)So, the detailed calculation results in 5,6MB, surrounding text is 6,4 MB. Please somebody with access change the first 6.4Thanks,Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-When I visit a mosque, I show my respect by taking off my shoes. I follow the customs, just as I do in a church, synagogue or other holy place. But if a believer demands that I, as a nonbeliever, observe his taboos in the public domain, he is not asking for my respect, but for my submission. And that is incompatible with a secular democracy.
Re: [GENERAL] Temporal Databases
Maybe: http://www.codeforpeople.com/lib/ruby/btpgsql/btpgsql-0.2.4/ Possibly useful (non-PG specific): ftp://ftp.cs.arizona.edu/tsql/tsql2/sql3/ Very interesting paper: http://www.navigators.di.fc.ul.pt/archive/Tacid-Hotdep05.pdf From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Rodrigo Sakai Sent: Thursday, February 23, 2006 10:35 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Temporal Databases Hi everyone, I'm focus on temporal databases (not temporary), and I want to know if anyone here is studying this tecnologies too. So, we can exchange knowlegment. Specifically, anyone who is trying to implement on postgresql the aspect of time (temporal). These researches are lead by Richard Snodgrass. So, anyone who have something to share, please contact me! Thanks!!!
Re: [GENERAL] ExclusiveLock without a relation in pg_locks
The ExclusiveLock seems to be granted on the transaction id instead of tables. So I am guessing that, for a connection, the first lock is granted to the transaction id and later other locks are granted on specific tables. I am running the following from the console: psql -d emrprod -c select pg_stat_activity.datname,pg_class.relname,pg_locks. transaction, pg_locks.mode, pg_locks.granted,pg_stat_activity.usename,substr(pg_ stat_activity.current_query,1,30) as query, pg_stat_activity.query_start, age( now(),pg_stat_activity.query_start) as age, pg_stat_activity.procpid from pg_s tat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class. oid) where pg_locks.pid=pg_stat_activity.procpid order by query_start;|grep -v IDLE Typical outputs are the following: 1) First example emrprod | | 950 | ExclusiveLock| t | emruser | SELECT PrtNbr, BilGurID, BilGu | 2006-02-23 14:02:28.369815-05 | 00:00:03.001737 | 6193 emrprod | mr0011 | | AccessShareLock | t | emruser | SELECT PrtNbr, BilGurID, BilGu | 2006-02-23 14:02:28.369815-05 | 00:00:03.001737 | 6193 emrprod | sy0001a | | AccessShareLock | t | emruser | SELEC T PrtNbr, BilGurID, BilGu | 2006-02-23 14:02:28.369815-05 | 00:00:03.001737 | 6193 emrprod | mr0050 | | AccessShareLock | t | emruser | SELEC T PrtNbr, BilGurID, BilGu | 2006-02-23 14:02:28.369815-05 | 00:00:03.001737 | 2) Second Example emrprod | | 9509136 | ExclusiveLock| t | emruser | SELEC T SYTmpWks, SYTmpDir, SYT | 2006-02-23 14:04:49.498836-05 | 00:00:00.558588 | 9667 emrprod | sy0001 | | AccessShareLock | t | emruser | SELEC T SYTmpWks, SYTmpDir, SYT | 2006-02-23 14:04:49.498836-05 | 00:00:00.558588 | 9667 emrprod | sy0001a | | AccessShareLock | t | emruser | SELEC T SYTmpWks, SYTmpDir, SYT | 2006-02-23 14:04:49.498836-05 | 00:00:00.558588 | 9667 emrprod | sy0004 | | AccessShareLock | t | emruser | SELEC T SYTmpWks, SYTmpDir, SYT | 2006-02-23 14:04:49.498836-05 | 00:00:00.558588 | 9667 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Fuhr Sent: Thursday, February 23, 2006 1:36 PM To: Carlos Oliva Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] ExclusiveLock without a relation in pg_locks On Thu, Feb 23, 2006 at 01:23:36PM -0500, Carlos Oliva wrote: Yes. I am seeing that situation often in our database. The query field of pg_stat_activity is SELECT ..., not SLECT UPDATE or UPDATE or INSERT or DELETE. I was expecting the query to say something like SLECT UPDATE or something like that. Also the query seems to have just columns in the select statement; not functions. I will look further into these queries in case that they are using functions. Are the ExclusiveLock locks for relations or for transaction IDs? Also, once a lock is acquired it's held until the transaction completes, so if the transaction ever acquired that lock then the transaction would still be holding it. If you can't figure out what's happening then it might be useful to see the output of SELECT relation::regclass, * FROM pg_locks; A self-contained test case might also be useful. If you show what commands you're running and what pg_locks output you don't understand, then somebody might be able to explain what's happening. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] DBD::Pg 1.44 released
If someone can make a ppm I would appreciate it. I have VS 2005 not 2003. Mike On Thu, Feb 23, 2006 at 02:11:55AM -, Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am pleased to announce that version 1.44 of DBD::Pg has been released. You can find it on CPAN: http://search.cpan.org/~dbdpg/DBD-Pg-1.44/ This will be one of the last versions that supports PostgreSQL 7.3. If you are using that, you are strongly encouraged to upgrade to the 8.x series. This version has many changes and bug fixes. Some of the more notable enhancements include: * Allow data types to be specified in $dbh-quote() calls. * Full support for dollar-sign quoting. * Complete rewrite and optimization of the do() method, which now runs much quicker if placeholders are not used. The complete list can be found in the Changes file linked to from the above URL. This version also uses a SIGNATURE file, which requires the use of Module::Signature. It is highly recommended that you verify your installation this way. Otherwise: MD5 checksum: 7593ce2e07c0959ef62d9628229d2d1a DBD-Pg-1.44.tar.gz SHA1 checksum: f5027985ec5d275853f0a727f85a2678b57e719b DBD-Pg-1.44.tar.gz - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 20060101 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFD/RecvJuQZxSWSsgRAkh0AJ9wI04+f1MizPM50JdbamYxPCEYawCgt5Zs mFxZk1e1qRt4md2ZRpxyh94= =oEvi -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Limitations : Number of ...
Thanks. Yeah, I actually *did* do a search of the archives, as well as Google, but I'm only finding the size limitations (and everything else). I'm looking for the number of actual tables a server can handle. And the number of databases. My gut feeling is unlimited (like everything else)... Jon D Cruz -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of A. Kretschmer Sent: Thursday, February 23, 2006 10:15 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Limitations : Number of ... Importance: High am 23.02.2006, um 10:00:01 -0800 mailte Jon Cruz folgendes: Two quick questions : What is the max number of databases on a PostgreSQL server? What is the max number of tables in a database ? Please, read our FAQ. http://www.postgresql.org/docs/faqs.FAQ.html And yes: we don't have limitations ;-) Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] pg_autovacuum on Windows triggers string warning
Hi, I have the postgresql.conf escape_string_warning active on my Win32 machine. I have a couple databases on it which are static. When pg_autovacuum checks these static databases it is triggering the warning: non standard use of \\ in a string literal at character 128 or character 355 depending upon which database it is checking. Maybe it can be fixed in 8.2 if it hasn't been addressed already. Mike ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Limitations : Number of ...
On Thu, Feb 23, 2006 at 11:21:33AM -0800, Jon Cruz wrote: Thanks. Yeah, I actually *did* do a search of the archives, as well as Google, but I'm only finding the size limitations (and everything else). I'm looking for the number of actual tables a server can handle. And the number of databases. My gut feeling is unlimited (like everything else)... Logically, unlimited. Practically, because tables are stored as files, at some point you might run out of inodes on your disk. You're more likely to run out of disk-space first though, unless your tables are small. More directly, as the number of tables grow, so does the size of the system catalogs. So this will show up as increased planning time. Databases are just a way of dividing up tables. No strict limit, but the number-of-files thing applies. Actually, it's tables-per-database that's the relevent to planning time, as the backend doesn't need to worry about tables in other databases. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
[GENERAL] Is the pg_locks been used?
Hi,When the pg_locks view is used the internal lock manager data structures are momentarily locked and that is why I would like to know if some application is reading the pg_locks view and how many times.Is there a way to discover it?Thanks in advance!Reimer Yahoo! Acesso Grátis Internet rápida e grátis. Instale o discador agora!
[GENERAL] subtracting minutes from date
What is the best way to store a timestamp if all I need to do is select rows where this timestamp is less than 60 minutes prior to the current time? If I have a column called date with data type timestamp without time zone I know I can use SELECT * FROM table WHERE date (now()::DATE - 7)::TIMESTAMP; to select rows where date is older than seven days. I'm not quite sure how to interpret now()::DATE::TIMESTAMP since I'm not able to answer my own question. I've tried things like: SELECT * FROM table WHERE date now()::DATE::(TIMESTAMP - 60); but this gives a syntax error. Thanks. -- Brandon ---(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] How I changed the encoding of template1 after the fact
I wanted to change the encoding of the template1 database, but when I tried to drop template1, I get the message, ERROR: cannot drop a template database. The docs (http://www.postgresql.org/docs/8.1/interactive/manage-ag-templatedbs.html) say that this is possible, but a user comment on that page says you can't. Actually, you *can* drop a template database, if you first convert it into a non-template database, as per http://techdocs.postgresql.org/techdocs/pgsqladventuresep1.php Here's a condensed example, in which template1 is recreated to change its default encoding: -- Connect as the postgres superuser, e.g.: -- psql -U postgres template1 -- Then run: UPDATE pg_database SET datallowconn = TRUE where datname = 'template0'; \c template0 UPDATE pg_database SET datistemplate = FALSE where datname = 'template1'; drop database template1; create database template1 with template = template0 encoding = 'UNICODE'; UPDATE pg_database SET datistemplate = TRUE where datname = 'template1'; \c template1 UPDATE pg_database SET datallowconn = FALSE where datname = 'template0'; If you just wanted to clone template0, you would leave out the encoding = 'UNICODE' clause. Gurus, any corrections or safety advisories you care to make? Kevin Murphy ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc
I am waiting for your clues. - Emi May I know where I can find some online documents about mapping the integer values to the following SQL types please? For example, if I have value 1 , so that I know 1 is mapped to SQL_CHAR; if I have value 3, so that I know 3 is mapped to SQL_NUMERIC. Data Types The following data types are supported: SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_NUMERIC SQL_DECIMAL SQL_SMALLINT SQL_INTEGER SQL_REAL SQL_FLOAT SQL_DOUBLE SQL_BIT SQL_TINYINT SQL_BIGINT SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_INTERVALS (all types) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] subtracting minutes from date
You can just save it as timestamp and try the following query. select * from table where date (now() - interval '1 hour'); Regards Chandra Sekhar Surapaneni -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brandon Metcalf Sent: Thursday, February 23, 2006 1:56 PM To: pgsql-general@postgresql.org Subject: [GENERAL] subtracting minutes from date What is the best way to store a timestamp if all I need to do is select rows where this timestamp is less than 60 minutes prior to the current time? If I have a column called date with data type timestamp without time zone I know I can use SELECT * FROM table WHERE date (now()::DATE - 7)::TIMESTAMP; to select rows where date is older than seven days. I'm not quite sure how to interpret now()::DATE::TIMESTAMP since I'm not able to answer my own question. I've tried things like: SELECT * FROM table WHERE date now()::DATE::(TIMESTAMP - 60); but this gives a syntax error. Thanks. -- Brandon ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] subtracting minutes from date
On Thu, 2006-02-23 at 13:55, Brandon Metcalf wrote: What is the best way to store a timestamp if all I need to do is select rows where this timestamp is less than 60 minutes prior to the current time? If I have a column called date with data type timestamp without time zone I know I can use SELECT * FROM table WHERE date (now()::DATE - 7)::TIMESTAMP; to select rows where date is older than seven days. I'm not quite sure how to interpret now()::DATE::TIMESTAMP since I'm not able to answer my own question. I've tried things like: SELECT * FROM table WHERE date now()::DATE::(TIMESTAMP - 60); Tell it you're subracting a minute: select now() - interval '13 minutes'; That kind of thing ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc
On fim, 2006-02-23 at 15:45 -0500, Emi Lu wrote: I am waiting for your clues. Maybe it is us that need some clues from you. gnari May I know where I can find some online documents about mapping the integer values to the following SQL types please? For example, if I have value 1 , so that I know 1 is mapped to SQL_CHAR; if I have value 3, so that I know 3 is mapped to SQL_NUMERIC. Data Types The following data types are supported: SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_NUMERIC SQL_DECIMAL SQL_SMALLINT SQL_INTEGER SQL_REAL SQL_FLOAT SQL_DOUBLE SQL_BIT SQL_TINYINT SQL_BIGINT SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_INTERVALS (all types) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(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] subtracting minutes from date
c == [EMAIL PROTECTED] writes: c You can just save it as timestamp and try the following query. c select * from table where date (now() - interval '1 hour'); Thanks. -- Brandon ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] subtracting minutes from date
s == [EMAIL PROTECTED] writes: s On Thu, 2006-02-23 at 13:55, Brandon Metcalf wrote: s What is the best way to store a timestamp if all I need to do is s select rows where this timestamp is less than 60 minutes prior to the s current time? s s If I have a column called date with data type timestamp without time s zone I know I can use s sSELECT * FROM table WHERE date (now()::DATE - 7)::TIMESTAMP; s s to select rows where date is older than seven days. I'm not quite s sure how to interpret now()::DATE::TIMESTAMP since I'm not able to s answer my own question. I've tried things like: s sSELECT * FROM table WHERE date now()::DATE::(TIMESTAMP - 60); s Tell it you're subracting a minute: s select now() - interval '13 minutes'; Thanks. -- Brandon ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc
Maybe it is us that need some clues from you. We use perl DBI to read table names, column names, and column types from Oracle rdb 7.3 through ODBC, and then try to create tables into postgresql. Through perl DBI, we got: Column Name Type Precision Scale Nullable? -- - - - col1 1 4 0 Yes col2 1 4 0 Yes col3 1 2 0 Yes col44 11 0 Yes col53 4 2 Yes col693 13 0 Yes ... ... I'd like to know how to map the integer type value 1, 3, 4, 93, etc to SQL_type? May I know where I can find some online documents about mapping the integer values to the following SQL types please? For example, if I have value 1 , so that I know 1 is mapped to SQL_CHAR; if I have value 3, so that I know 3 is mapped to SQL_NUMERIC. Data Types The following data types are supported: SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_NUMERIC SQL_DECIMAL SQL_SMALLINT SQL_INTEGER SQL_REAL SQL_FLOAT SQL_DOUBLE SQL_BIT SQL_TINYINT SQL_BIGINT SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_INTERVALS (all types) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc
On Thu, 2006-02-23 at 15:31, Emi Lu wrote: Maybe it is us that need some clues from you. We use perl DBI to read table names, column names, and column types from Oracle rdb 7.3 through ODBC, and then try to create tables into postgresql. Through perl DBI, we got: Column Name Type Precision Scale Nullable? -- - - - col1 1 4 0 Yes col2 1 4 0 Yes col3 1 2 0 Yes col44 11 0 Yes col53 4 2 Yes col693 13 0 Yes ... ... I'd like to know how to map the integer type value 1, 3, 4, 93, etc to SQL_type? Assuming that those type numbers come from Oracle, you got me. Can you use some oracle tool to look at the table structure and compare it to the numbers you get and make a map? If they're numbers from Oracle, you likely won't get much help here. Only a few folks here are all that intimate with oracle's inner workings. Heck, I use it every day, and I still don't know this kind of stuff, and hope I never have to. :) ---(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] Limitations : Number of ...
Martijn van Oosterhout kleptog@svana.org writes: Logically, unlimited. Practically, because tables are stored as files, at some point you might run out of inodes on your disk. You're more likely to run out of disk-space first though, unless your tables are small. Another constraint is that many filesystems don't behave real well with lots and lots of files in a single filesystem directory (where lots and lots usually translates to trouble in the 10K-100K range). You could work around this to some extent by splitting the database into multiple tablespaces, but most people are going to tell you that a schema with that many tables needs reconsideration anyway. 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] Temporal Databases
On Thu, Feb 23, 2006 at 03:34:36PM -0300, Rodrigo Sakai [EMAIL PROTECTED] wrote: I'm focus on temporal databases (not temporary), and I want to know if anyone here is studying this tecnologies too. So, we can exchange knowlegment. Specifically, anyone who is trying to implement on postgresql the aspect of time (temporal). These researches are lead by Richard Snodgrass. So, anyone who have something to share, please contact me! Not sure if I understand the problem correctly, but the contrib/spi/timetravel module does something which I think may be what you are talking about. http://developer.postgresql.org/cvsweb.cgi/~checkout~/pgsql/contrib/spi/README.timetravel The docs are a bit cryptic but you should be able to grasp if it suits your needs. Basically you can go back to any point in tabe for a timetravel table and make date based comparisons. Hope this helps, forgive my ignorance if if doesn't. bkw ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How I changed the encoding of template1 after the fact
Kevin Murphy [EMAIL PROTECTED] writes: Here's a condensed example, in which template1 is recreated to change its default encoding: If you're willing to fool around at that level, why not just UPDATE pg_database SET datencoding = ... The restriction against dropping template databases is specifically intended to make it difficult for those who don't know what they are doing to shoot themselves in the foot. So I'm disinclined to make the manual include easy-to-follow directions for bypassing the safety interlock --- if you can't be bothered to find out how, you shouldn't be doing it. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] now() time off
On Thu, Feb 23, 2006 at 12:41:38PM +0100, Martijn van Oosterhout wrote: On Wed, Feb 22, 2006 at 06:35:55PM -0600, Jim C. Nasby wrote: On Wed, Feb 22, 2006 at 04:46:35PM -0600, Scott Marlowe wrote: Sounds like a time zone issue. I'd start looking there. I've been bitten by this before as well. I'd be in favor of adding an option such that postmaster would refuse to start if TZ was something other than UTC; I'd much rather that then have a bunch of data get screwed up... Alternativly you could just set the timezone parameter in the postgresql configuration... Wow, didn't know that was there... was it added fairly recently or have I just been blind? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] ExclusiveLock without a relation in pg_locks
On Thu, Feb 23, 2006 at 02:10:22PM -0500, Carlos Oliva wrote: The ExclusiveLock seems to be granted on the transaction id instead of tables. So I am guessing that, for a connection, the first lock is granted to the transaction id and later other locks are granted on specific tables. Right. Your output shows that the relation locks are AccessShareLock, which is what the documentation says an ordinary SELECT acquires. Other operations acquire stronger locks, depending on what they need to prevent other transactions from doing until this transaction completes. Most of the time you don't need to worry about any of this unless a transaction appears to be blocked (stuck); then you can query pg_locks and look for locks where the granted column is false and diagnose from there. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pg_autovacuum on Windows triggers string warning
Mike G. [EMAIL PROTECTED] writes: I have the postgresql.conf escape_string_warning active on my Win32 machine. I have a couple databases on it which are static. When pg_autovacuum checks these static databases it is triggering the warning: non standard use of \\ in a string literal at character 128 or character 355 depending upon which database it is checking. This is a tad hard to believe, as the 8.1 version of pg_autovacuum doesn't generate any commands that need to be parsed. Nor can I reproduce this while testing. Can you provide more context? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc
On fim, 2006-02-23 at 16:31 -0500, Emi Lu wrote: We use perl DBI to read table names, column names, and column types from Oracle rdb 7.3 through ODBC, and then try to create tables into postgresql. Through perl DBI, we got: Column Name Type Precision Scale Nullable? -- - - - col1 1 4 0 Yes col2 1 4 0 Yes col3 1 2 0 Yes col44 11 0 Yes col53 4 2 Yes col693 13 0 Yes ... ... I'd like to know how to map the integer type value 1, 3, 4, 93, etc to SQL_type? For example, if I have value 1 , so that I know 1 is mapped to SQL_CHAR; if I have value 3, so that I know 3 is mapped to SQL_NUMERIC. Data Types The following data types are supported: SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_NUMERIC these are not really Oracle type names so I guess these are ODBC type names, and the mapping you talk of is maybe some ODBC thing. in DBD::Oracle found on CPAN I find this nice little SQL to generate columns listing, which might give you some clues: my $Sql = SQL; SELECT * FROM ( SELECT /*+ RULE*/ to_char( NULL ) TABLE_CAT , tc.OWNERTABLE_SCHEM , tc.TABLE_NAME TABLE_NAME , tc.COLUMN_NAME COLUMN_NAME , $typecase decode( tc.DATA_TYPE , 'MLSLABEL' , -9106 , 'ROWID', -9104 , 'UROWID' , -9104 , 'BFILE',-4 -- 31? , 'LONG RAW' ,-4 , 'RAW' ,-3 , 'LONG' ,-1 , 'UNDEFINED', 0 , 'CHAR' , 1 , 'NCHAR', 1 , 'NUMBER' , decode( tc.DATA_SCALE, NULL, 8, 3 ) , 'FLOAT', 8 , 'VARCHAR2' ,12 , 'NVARCHAR2',12 , 'BLOB' ,30 , 'CLOB' ,40 , 'NCLOB',40 , 'DATE' ,93 , NULL ) $typecaseend DATA_TYPE -- ... , tc.DATA_TYPETYPE_NAME -- std.? , decode( tc.DATA_TYPE , 'LONG RAW' , 2147483647 , 'LONG' , 2147483647 , 'CLOB' , 2147483647 , 'NCLOB', 2147483647 , 'BLOB' , 2147483647 , 'BFILE', 2147483647 , 'NUMBER' , decode( tc.DATA_SCALE , NULL, 126 , nvl( tc.DATA_PRECISION, 38 ) ) , 'FLOAT', tc.DATA_PRECISION , 'DATE' , 19 , tc.DATA_LENGTH ) COLUMN_SIZE , decode( tc.DATA_TYPE , 'LONG RAW' , 2147483647 , 'LONG' , 2147483647 , 'CLOB' , 2147483647 , 'NCLOB', 2147483647 , 'BLOB' , 2147483647 , 'BFILE', 2147483647 , 'NUMBER' , nvl( tc.DATA_PRECISION, 38 ) + 2 , 'FLOAT', 8 -- ? , 'DATE' , 16 , tc.DATA_LENGTH ) BUFFER_LENGTH , decode( tc.DATA_TYPE , 'DATE' , 0 , tc.DATA_SCALE ) DECIMAL_DIGITS -- ... , decode( tc.DATA_TYPE , 'FLOAT', 2 , 'NUMBER' , decode( tc.DATA_SCALE, NULL, 2, 10 ) , NULL ) NUM_PREC_RADIX , decode( tc.NULLABLE , 'Y', 1 , 'N', 0 , NULL ) NULLABLE , cc.COMMENTS REMARKS , tc.DATA_DEFAULT COLUMN_DEF -- Column is LONG! , decode( tc.DATA_TYPE , 'MLSLABEL' , -9106 , 'ROWID', -9104 , 'UROWID' , -9104 , 'BFILE',-4 -- 31? , 'LONG RAW' ,-4 , 'RAW' ,-3 , 'LONG' ,-1 , 'UNDEFINED', 0 , 'CHAR' , 1 , 'NCHAR', 1 , 'NUMBER' , decode( tc.DATA_SCALE, NULL, 8, 3 ) , 'FLOAT', 8 , 'VARCHAR2' ,12 , 'NVARCHAR2',12 , 'BLOB' ,30 , 'CLOB' ,40 , 'NCLOB',40 , 'DATE' , 9 -- not 93! , NULL ) SQL_DATA_TYPE -- ... , decode( tc.DATA_TYPE , 'DATE' , 3 , NULL ) SQL_DATETIME_SUB -- ... , to_number( NULL ) CHAR_OCTET_LENGTH -- TODO , tc.COLUMN_IDORDINAL_POSITION , decode( tc.NULLABLE , 'Y', 'YES' , 'N', 'NO' , NULL ) IS_NULLABLE FROM ALL_TAB_COLUMNS tc , ALL_COL_COMMENTS cc WHERE tc.OWNER =
Re: [GENERAL] now() time off
On Thu, Feb 23, 2006 at 04:21:19PM -0600, Jim C. Nasby wrote: On Thu, Feb 23, 2006 at 12:41:38PM +0100, Martijn van Oosterhout wrote: Alternativly you could just set the timezone parameter in the postgresql configuration... Wow, didn't know that was there... was it added fairly recently or have I just been blind? http://www.postgresql.org/docs/7.3/interactive/release-7-3.html Add configuration variables datestyle and timezone (Tom) -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Is there a way to check which indexes are being used
On Thu, Feb 23, 2006 at 10:44:38AM -0500, Vivek Khera wrote: On Feb 23, 2006, at 8:47 AM, Ragnar wrote: select * from pg_stat_user_indexes ; which level of stats do I need to enable this? block level or row level or both? Either-or, AFAIK. Block level will present less load on the system. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] subtracting minutes from date
On Thu, Feb 23, 2006 at 03:08:32PM -0600, Scott Marlowe wrote: SELECT * FROM table WHERE date now()::DATE::(TIMESTAMP - 60); Tell it you're subracting a minute: select now() - interval '13 minutes'; Or, better yet if you're feeding in a variable: date now() - ( 13 * '1 minute'::interval ) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] now() time off
On Thu, Feb 23, 2006 at 03:49:50PM -0700, Michael Fuhr wrote: On Thu, Feb 23, 2006 at 04:21:19PM -0600, Jim C. Nasby wrote: On Thu, Feb 23, 2006 at 12:41:38PM +0100, Martijn van Oosterhout wrote: Alternativly you could just set the timezone parameter in the postgresql configuration... Wow, didn't know that was there... was it added fairly recently or have I just been blind? http://www.postgresql.org/docs/7.3/interactive/release-7-3.html Add configuration variables datestyle and timezone (Tom) Whew. I started on 7.2, so I have a bit of an excuse. :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Error correction to FAQ
FWIW, I believe the FAQ is in with the rest of the documentation, so submitting a patch might be the best way to get it fixed. Also, -docs is a better place to post about this, so I'm moving the thread over there. On Thu, Feb 23, 2006 at 07:45:49PM +0100, Harald Armin Massa wrote: Within the FAQ, Point 4.5 there is: []The size of the PostgreSQL database file containing this data can be estimated as 6.4 MB: [] 685 database pages * 8192 bytes per page = 5,611,520 bytes (5.6 MB) So, the detailed calculation results in 5,6MB, surrounding text is 6,4 MB. Please somebody with access change the first 6.4 Thanks, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstra??e 202b 70197 Stuttgart 0173/9409607 - When I visit a mosque, I show my respect by taking off my shoes. I follow the customs, just as I do in a church, synagogue or other holy place. But if a believer demands that I, as a nonbeliever, observe his taboos in the public domain, he is not asking for my respect, but for my submission. And that is incompatible with a secular democracy. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Temporal Databases
There is also a description of an implementation for timetravel at http://www.varlena.com/GeneralBits/122.php If you would like to discuss this further, please let me know. I've know postgres's and illustra's timetravel implementations. --elein [EMAIL PROTECTED] On Thu, Feb 23, 2006 at 10:52:13PM +0100, Bernhard Weisshuhn wrote: On Thu, Feb 23, 2006 at 03:34:36PM -0300, Rodrigo Sakai [EMAIL PROTECTED] wrote: I'm focus on temporal databases (not temporary), and I want to know if anyone here is studying this tecnologies too. So, we can exchange knowlegment. Specifically, anyone who is trying to implement on postgresql the aspect of time (temporal). These researches are lead by Richard Snodgrass. So, anyone who have something to share, please contact me! Not sure if I understand the problem correctly, but the contrib/spi/timetravel module does something which I think may be what you are talking about. http://developer.postgresql.org/cvsweb.cgi/~checkout~/pgsql/contrib/spi/README.timetravel The docs are a bit cryptic but you should be able to grasp if it suits your needs. Basically you can go back to any point in tabe for a timetravel table and make date based comparisons. Hope this helps, forgive my ignorance if if doesn't. bkw ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Temporal Databases
On Feb 24, 2006, at 3:34 , Rodrigo Sakai wrote: I'm focus on temporal databases (not temporary), and I want to know if anyone here is studying this tecnologies too. So, we can exchange knowlegment. Specifically, anyone who is trying to implement on postgresql the aspect of time (temporal). These researches are lead by Richard Snodgrass. So, anyone who have something to share, please contact me! Nice to meet you, Rodrigo. This is an area that I hope to do some work on as well. Another resource that you may find useful is Date, Darwen, and Lorentzos' Temporal Data and the Relational Model which starts from temporal data in particular and proposes a more generalized interval data type constructor. Lorentzos has also published some articles on IXSQL[2] which you may find interesting in its extensions to SQL in particular, rather than the relational model. You may also be interested to know there is an no-longer documented tinterval type in Postgres. I don't believe it's even been tested in recent years, but you may want to look at its implementation. Michael Glaesemann grzm myrealbox com [1](http://www.amazon.com/gp/product/1558608559/) [2] Nikos A. Lorentzos and Yannis G. Mitsopoulos, SQL Extension for Interval Data, IEEE Transactions On Knowledge And Data Engineering, Vol. 9, No. 3, May/June 1997 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Temporal Databases
Thanks for all people. And Michael, I think your researches are very close to my researches. I'm looking for partnership (I hope this is the correct word). Although, this area is very old (about 20 years), there is not much implementation. I saw the architecture of BTPGSQL, and is a usual implementation where it was developed a API to do the interface between the aplication and the data. It's a good solution, but not what I'm looking for. I'm looking for something implemented inside the database, like the flashback functionality of oracle 10g. And thanks Michael, I will have a look on the Date's article. - Original Message - From: Michael Glaesemann [EMAIL PROTECTED] To: Rodrigo Sakai [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Thursday, February 23, 2006 10:43 PM Subject: Re: [GENERAL] Temporal Databases On Feb 24, 2006, at 3:34 , Rodrigo Sakai wrote: I'm focus on temporal databases (not temporary), and I want to know if anyone here is studying this tecnologies too. So, we can exchange knowlegment. Specifically, anyone who is trying to implement on postgresql the aspect of time (temporal). These researches are lead by Richard Snodgrass. So, anyone who have something to share, please contact me! Nice to meet you, Rodrigo. This is an area that I hope to do some work on as well. Another resource that you may find useful is Date, Darwen, and Lorentzos' Temporal Data and the Relational Model which starts from temporal data in particular and proposes a more generalized interval data type constructor. Lorentzos has also published some articles on IXSQL[2] which you may find interesting in its extensions to SQL in particular, rather than the relational model. You may also be interested to know there is an no-longer documented tinterval type in Postgres. I don't believe it's even been tested in recent years, but you may want to look at its implementation. Michael Glaesemann grzm myrealbox com [1](http://www.amazon.com/gp/product/1558608559/) [2] Nikos A. Lorentzos and Yannis G. Mitsopoulos, SQL Extension for Interval Data, IEEE Transactions On Knowledge And Data Engineering, Vol. 9, No. 3, May/June 1997 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_autovacuum on Windows triggers string warning
For the ones triggered after hours the only other events close by would be pg_dump kicking off a backup (full database dump). I do see now that quite a few of the warnings are triggered right after the pg_dump process ends. The ones during the day (specific schemas or individual table) would be triggering before pg_dump ends although they are multi-step backups. If only one of the requested backups were triggering the warning it might explain why it happens before the end. Any suggestions on which when to log settings and levels should be changed in postgresql.conf to get better info? Currently they are client_min_messages = warning log_min_messages = warning log_min_error_statement = warning Mike On Thu, 2006-02-23 at 17:27 -0500, Tom Lane wrote Mike G. [EMAIL PROTECTED] writes: I have the postgresql.conf escape_string_warning active on my Win32 machine. I have a couple databases on it which are static. When pg_autovacuum checks these static databases it is triggering the warning: non standard use of \\ in a string literal at character 128 or character 355 depending upon which database it is checking. This is a tad hard to believe, as the 8.1 version of pg_autovacuum doesn't generate any commands that need to be parsed. Nor can I reproduce this while testing. Can you provide more context? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] ECPG and COPY
I found this in the archives: http://archives.postgresql.org/pgsql-interfaces/2004-04/msg5.php With 8.x, is it still true that with ECPG, it is not possible to use COPY inline - I'd have to write the data to a file, then 'COPY FROM filename' (or use direct libpq API calls instead of ECPG)? Wes ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] subtracting minutes from date
On Thu, Feb 23, 2006 at 13:55:34 -0600, Brandon Metcalf [EMAIL PROTECTED] wrote: If I have a column called date with data type timestamp without time zone I know I can use SELECT * FROM table WHERE date (now()::DATE - 7)::TIMESTAMP; You can do this without converting to timestamp: SELECT * FROM table WHERE date current_date - 7; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ECPG and COPY
On Thu, Feb 23, 2006 at 10:26:35PM -0600, Wes wrote:
I found this in the archives:
http://archives.postgresql.org/pgsql-interfaces/2004-04/msg5.php
With 8.x, is it still true that with ECPG, it is not possible to use COPY
inline - I'd have to write the data to a file, then 'COPY FROM filename' (or
use direct libpq API calls instead of ECPG)?
Looks that way. The ECPGexecute() function in ecpglib/execute.c
has the following code:
case PGRES_COPY_OUT:
ECPGlog(ECPGexecute line %d: Got PGRES_COPY_OUT ... tossing.\n,
stmt-lineno);
PQendcopy(stmt-connection-connection);
break;
case PGRES_COPY_IN:
ECPGlog(ECPGexecute line %d: Got PGRES_COPY_IN ... tossing.\n,
stmt-lineno);
PQendcopy(stmt-connection-connection);
break;
You can see this code being executed if you enable debugging:
% cat foo.pgc
int
main(void)
{
ECPGdebug(1, stdout);
EXEC SQL CONNECT TO test;
EXEC SQL COPY foo FROM stdin;
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
return 0;
}
% ./foo
[33044]: ECPGdebug: set to 1
[33044]: ECPGconnect: opening database test on DEFAULT port DEFAULT
[33044]: ECPGexecute line 6: QUERY: copy foo from stdinon connection test
[33044]: ECPGexecute line 6: Got PGRES_COPY_IN ... tossing.
[33044]: ECPGtrans line 7 action = commit connection = test
[33044]: ecpg_finish: Connection test closed.
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
