Hello,
can I find out, what SQL statement the PostgreSQL-server is
executing?
Thanks
Michaela
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
[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
[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,
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
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
[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
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
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
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
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
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
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
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]
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
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
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)...
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
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
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
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 -
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
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.
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,
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
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
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
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
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?
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
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
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
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
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
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.
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
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
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
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
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
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...
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
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
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
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
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
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)?
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:
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
65 matches
Mail list logo
