Thanks Tom ...
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Tuesday, January 12, 2010 1:35 AM
To: tamanna madaan
Cc: pgsql-general@postgresql.org; Gaurav Katiyar
Subject: Re: [GENERAL] does autovacuum in postgres-8.1.2 use prepared
transactions ??
"tamanna madaan"
Very nice. Thanks!
Thanks and Regards
Yan Cheng CHEOK
--- On Tue, 1/12/10, Pavel Stehule wrote:
> From: Pavel Stehule
> Subject: Re: [GENERAL] Best Practice when Encounter Invalid Stored Procedure
> Parameters
> To: "Yan Cheng Cheok"
> Cc: pgsql-general@postgresql.org
> Date: Tuesday, Janu
2010/1/11 Aleksey Onopriyenko :
> Hello.
>
> We are trying to migrate from Informix 9.4 to PostgreSQL. As part of
> migration we are porting our client application.
>
> So we need reimplement such functionality:
> 1. Declare a cursor using to _parameterized_ SELECT statement. It should be
> possibl
Hi,
For years now I've simply backed up my databases by doing a nightly
pg_dump, but since we added the ability for users to import binary files
in to our application, which are stored in a bytea fields, the dump
sizes have gone through the roof — even with gzip compression, they're
significantly
On Friday 08 January 2010 11.28:15 Ivan Sergio Borgonovo wrote:
> It would be enough just knowing which part of the file is being
> restored (without causing too much extra IO, that will definitively
> put my notebook on its knee).
Highly platform dependent, but has helped me a lot recently with v
hello
2010/1/12 Yan Cheng Cheok :
> In c++, whenever we encounter an unexpected parameters, here is what we
> usually did :
>
> bool fun(int i) {
> if (i < 0) {
> return false;
> }
> }
>
> void fun(int i) {
> if (i < 0) {
> throw std::exception("Invalid parameter");
> }
2010/1/12 Omar Mehmood :
> Is there any way to enable transaction logging in the format of SQL
> statements for committed transactions only ? In other words, a way to log
> all the SQL statements (including START TRANSACTION and COMMIT statements)
> for all committed mod type statements (INSERT
On Tue, Jan 12, 2010 at 7:17 AM, Greg Smith wrote:
> AI Rumman wrote:
>
>> I used the followings:
>> create tablespace mytabspc location '/var/lib/pgsql/data/pg_tblspc';
>> create database mydb with tablespace=mytabspc;
>> drop database mydb;
>> drop tablespace mytabspc;
>> ERROR: tablspace '
AI Rumman wrote:
I used the followings:
create tablespace mytabspc location '/var/lib/pgsql/data/pg_tblspc';
create database mydb with tablespace=mytabspc;
drop database mydb;
drop tablespace mytabspc;
ERROR: tablspace 'mytabspc' is not empty
Please tell me why?
You don't put things
I used the followings:
create tablespace mytabspc location '/var/lib/pgsql/data/pg_tblspc';
create database mydb with tablespace=mytabspc;
drop database mydb;
drop tablespace mytabspc;
ERROR: tablspace 'mytabspc' is not empty
Please tell me why?
On Tue, Jan 12, 2010 at 11:50 AM, Greg Smith
In response to Scott Marlowe :
> On Sat, Jan 9, 2010 at 2:46 PM, Andreas Kretschmer
> wrote:
> > Stefan Kaltenbrunner wrote:
> >
> >> Andreas Kretschmer wrote:
> >>> zxo102 ouyang wrote:
> >>>
> Hi everyone, I am using postgresql 8.3-beta3. I have a table
> 'test' with three fields:
AI Rumman wrote:
But actually I want to know that why the value in spclocation is null
is pg_tablespace for pg_default.
Moreover, $PGDATA/pg_tblspc has no file.
Could you please tell me why?
PostgreSQL ships with a blank tablespace setting, which it interprets as
meaning you want to put the d
Thanks.
But actually I want to know that why the value in spclocation is null is
pg_tablespace for pg_default.
Moreover, $PGDATA/pg_tblspc has no file.
Could you please tell me why?
On Tue, Jan 12, 2010 at 11:26 AM, John R Pierce wrote:
> AI Rumman wrote:
>
>> I am new at Postgresql. Previously
AI Rumman wrote:
I am new at Postgresql. Previously I used to work with Oracle.
I am surprised to see that the location for pg_default tablespace in
my database for a Postgresql cluster is null.
Could anyone please tell me what is the location of default tablespace
in postgresql and how could
I am new at Postgresql. Previously I used to work with Oracle.
I am surprised to see that the location for pg_default tablespace in my
database for a Postgresql cluster is null.
Could anyone please tell me what is the location of default tablespace in
postgresql and how could I find it?
Craig Ringer writes:
Possible workaround: Instead of your table creation, renaming and
dropping, use TRUNCATE.
Have to support both 8.1 and 8.4.
If I recall correctly 8.1 did not support truncate inside of a transaction.
We are in the process of upgrading everything to 8.4, but until then.. h
Use
log_min_duration_statement=0
at postgresql.conf file to log every statement.
On Tue, Jan 12, 2010 at 7:50 AM, Omar Mehmood wrote:
> Is there any way to enable transaction logging in the format of SQL
> statements for committed transactions only ? In other words, a way to log
> all the SQL
Is there any way to enable transaction logging in the format of SQL statements
for committed transactions only ? In other words, a way to log all the SQL
statements (including START TRANSACTION and COMMIT statements) for all
committed mod type statements (INSERT UPDATE DELETE etc).
Thanks,
Oma
In c++, whenever we encounter an unexpected parameters, here is what we usually
did :
bool fun(int i) {
if (i < 0) {
return false;
}
}
void fun(int i) {
if (i < 0) {
throw std::exception("Invalid parameter");
}
}
void fun(int i) {
assert (i >= 0);
}
How ab
Omar Mehmood wrote:
> Would it be possible to use PostgreSQL PITR feature to support this
> functionality ? All of the data created/updated/deleted per server
> is unique to that server, so replaying the log to the slave should
> technically be safe and the replaying logs from multiple servers
> s
Greg Smith wrote:
> If the old system had a write caching card, and the new one doesn't
> that's certainly your most likely suspect for the source of the
> slowdown.
Note that it's even possible that the old system had a card with write
caching enabled, but *no* battery backed cache. That's crazi
Thanks for the suggestions.
I really don't want to use separate schemas for each master to logically
partition the data. I ensure that the data on each master will not clash with
each other (in terms of any DB level contraints such as PK), so I'd much prefer
they all reside in a single schema.
On Mon, Jan 11, 2010 at 03:02:18PM -0800, Omar Mehmood wrote:
> I'm wondering if it's possible to have a setup with multiple
> "master" servers replicating to a single slave. I can guarantee
> that each server will generate unique PK values for all tables and
> all the data is partitioned (logical
I'm wondering if it's possible to have a setup with multiple "master" servers
replicating to a single slave. I can guarantee that each server will generate
unique PK values for all tables and all the data is partitioned (logically by
server) across the servers. I would simply like to have a re
On 12/01/2010 2:00 AM, Leonardo M. Ramé wrote:
A customer of mine asked me to periodically delete old, unneeded records
containing ByteA fields, because he think it will reduce the database
size on disk. Is this true?. For example, in Firebird, the database size
is not reduced until you do a Back
On 12/01/2010 2:04 AM, Francisco Reyes wrote:
I need to replace a table with a new one.
Example..
I create a script that continously does selects like
select count(*) from tmp_deleteme_francisco;
enough selects to last the duration of second script
select count(*) from tmp_deleteme_francisco
On 11/01/2010 9:44 PM, A. Kretschmer wrote:
In response to Andrus :
How to get localized date for single conversion inside SELECT statement so
that it works in different server lc_time settings ?
As Tom said, you can use to_char():
It looks like the OP wants a localized date, just one differ
On Mon, Jan 11, 2010 at 2:59 PM, Greg Smith wrote:
> Scott Marlowe wrote:
> If you can shoehorn one more drive, you could run RAID-10 and get much
> better performance.
>
>
> And throwing drives at the problem may not help. I've see a system with a
> 48 disk software RAID-10 that only got 100 TP
Scott Marlowe wrote:
On Mon, Jan 11, 2010 at 3:53 AM, Anton Belyaev wrote:
Old RAID-1 has "hardware" LSI controller.
I still have access to old server.
The old RAID card likely had a battery backed cache, which would make
the fsyncs much faster, as long as you hadn't run out of cache.
On 1/11/2010 8:16 AM, Aleksey Onopriyenko wrote:
Hello.
We are trying to migrate from Informix 9.4 to PostgreSQL. As part of
migration we are porting our client application.
So we need reimplement such functionality:
1. Declare a cursor using to _parameterized_ SELECT statement. It should
be po
On Mon, 11 Jan 2010 18:36:18 +
Sam Mason wrote:
> On Fri, Jan 08, 2010 at 11:28:15AM +0100, Ivan Sergio Borgonovo
> wrote:
> > Is there a way to know/estimate how much is left to complete a
> > restore?
> maybe something like "pv" would help?
> http://www.ivarch.com/programs/pv.shtml
Nic
"tamanna madaan" writes:
> Can anyone please let me know if autovacuum in postgres-8.1.2 uses
> prepared transactions.
Nope, it does not. Any prepared transactions you see hanging around
were created by some external client.
regards, tom lane
--
Sent via pgsql-general
Leonardo M. Ramé wrote:
A customer of mine asked me to periodically delete old, unneeded records
containing ByteA fields, because he think it will reduce the database
size on disk. Is this true?. For example, in Firebird, the database size
is not reduced until you do a Backup-Restore of the datab
On 11/01/2010 18:00, Leonardo M. Ramé wrote:
> A customer of mine asked me to periodically delete old, unneeded records
> containing ByteA fields, because he think it will reduce the database
> size on disk. Is this true?. For example, in Firebird, the database size
> is not reduced until you do a
Francisco Reyes wrote:
I say "don't believe" because I don't recall if byteA was stored in
the table itself or was stored outside using TOAST.. so I am not sure
about how/when space is released for it.
like all other data, that depends on the size of the data.if the
entire row (tuple) is
Sam Mason writes:
maybe something like "pv" would help?
http://www.ivarch.com/programs/pv.shtml
I think it may help the OP, but indexes are still going to be a rough spot..
if large table has several indexes the output from pv will be missleading.
--
Sent via pgsql-general mailing list
Leonardo M. Ramé writes:
A customer of mine asked me to periodically delete old, unneeded records
containing ByteA fields, because he think it will reduce the database
size on disk. Is this true?.
No.
For example, in Firebird, the database size
is not reduced until you do a Backup-Restore of
On Fri, Jan 08, 2010 at 11:28:15AM +0100, Ivan Sergio Borgonovo wrote:
> Is there a way to know/estimate how much is left to complete a
> restore?
maybe something like "pv" would help?
http://www.ivarch.com/programs/pv.shtml
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing
Ivan Sergio Borgonovo writes:
It get a bit better but even knowing what are the largest tables it
is hard to get an estimate of how much is missing before complete
restore.
Agree. Also building indexes can also take quite some time.
I'm really looking at rough figures... even a: I've read 40
Leonardo M. Ramé wrote:
A customer of mine asked me to periodically delete old, unneeded records
containing ByteA fields, because he think it will reduce the database
size on disk. Is this true?. For example, in Firebird, the database size
is not reduced until you do a Backup-Restore of the datab
2010/1/11 Merlin Moncure :
> On Mon, Jan 11, 2010 at 11:08 AM, Vincenzo Romano > SELECT * FROM master_tab
>> WHERE col1>='a date':timestamp AND col1<'another date'::timestamp
>> AND col2=42 AND col3='the answer';
>
> queries of this sort are optimally handled via row constructor for 8.2
> onwards
On Mon, Jan 11, 2010 at 11:08 AM, Vincenzo Romano > SELECT * FROM master_tab
> WHERE col1>='a date':timestamp AND col1<'another date'::timestamp
> AND col2=42 AND col3='the answer';
queries of this sort are optimally handled via row constructor for 8.2 onwards:
create index col231_idx on master
I need to replace a table with a new one.
Example..
I create a script that continously does selects like
select count(*) from tmp_deleteme_francisco;
enough selects to last the duration of second script
select count(*) from tmp_deleteme_francisco;
Another script then does
begin;
select * in
A customer of mine asked me to periodically delete old, unneeded records
containing ByteA fields, because he think it will reduce the database
size on disk. Is this true?. For example, in Firebird, the database size
is not reduced until you do a Backup-Restore of the database.
Thanks in advance,
L
On Mon, 11 Jan 2010 12:30:45 -0500
Francisco Reyes wrote:
> Ivan Sergio Borgonovo writes:
>
> > Is there a way to know/estimate how much is left to complete a
> > restore?
>
> Not sure on plain ASCII files but if your pg_dump used Fc then at
> restore you can pass the -v flag.
It get a bit be
Ivan Sergio Borgonovo writes:
Is there a way to know/estimate how much is left to complete a
restore?
Not sure on plain ASCII files but if your pg_dump used Fc then at restore
you can pass the -v flag.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes t
Dear list,
I solved my own problem - as so often, once you write it down and press the
send button you get the idea.
The problem was:
> Currently I am working on a user C-Function which should create a cache
> object on the first call and afterwards return a set of computed values for
> each
Hi All
I am using postgres-8.1.2.
Can anyone please let me know if autovacuum in postgres-8.1.2 uses
prepared transactions.
Thanks a lot in advance
Regards
Tamanna
I have compiled postgres for AIX and tested installation on a fresh
installation of AIX 6.1.3. I am interested in feedback on the package and
shall make improvements in the packaging as needed.
for enhanced portability readline and zlib were not included in the build.
I'll be looking into that at
2010/1/11 Merlin Moncure :
> On Mon, Jan 11, 2010 at 5:14 AM, Vincenzo Romano
> wrote:
>> Hi all.
>>
>> It's not clear to me how composite values are used in conditions
>> (WHERE/CHECK).
>> In my case I have something like this:
>>
>> -- begin snippet
>>
>> CREATE TABLE test_tab (
>> col1 times
=?UTF-8?Q?Filip_Rembia=C5=82kowski?= writes:
> Full test case, reproduced in 8.4.2 on two different hosts
> create table test (id serial primary key, t1 text, t2 text);
> create function myhash(test) returns text as 'select md5($1::text)' language
> sql immutable;
> create index myhash on test( m
On Mon, Jan 11, 2010 at 5:14 AM, Vincenzo Romano
wrote:
> Hi all.
>
> It's not clear to me how composite values are used in conditions
> (WHERE/CHECK).
> In my case I have something like this:
>
> -- begin snippet
>
> CREATE TABLE test_tab (
> col1 timestamp not null,
> col2 int8 not null,
>
On Mon, Jan 11, 2010 at 2:45 AM, Thilo Schneider
wrote:
> Dear list,
>
> Currently I am working on a user C-Function which should create a cache
> object on the first call and afterwards return a set of computed values for
> each argument combination it is called with.
>
> My Problem is how to g
On Monday 11 January 2010 3:08:27 am Fernando Morgenstern wrote:
> Em 11/01/2010, às 09:04, hubert depesz lubaczewski escreveu:
> >> Hi,
> >>
> >> I have done:
> >>
> >> # psql -U postgres -p 4000 -l | hexdump -C
> >>
> >> And got the two databases: http://pastebin.ca/1746711
> >>
> >> I couldn't f
Hello.
We are trying to migrate from Informix 9.4 to PostgreSQL. As part of
migration we are porting our client application.
So we need reimplement such functionality:
1. Declare a cursor using to _parameterized_ SELECT statement. It should be
possible to specify cursor's name (and, perhaps, the
In response to Andrus :
> >You might be able to get what you want with the to_char() function,
> >if setting datestyle doesn't do the trick for you.
>
> setting datestyle changes style for whole sql statement.
>
> How to get this in a single conversion in sql statement so that other
> expression
You might be able to get what you want with the to_char() function,
if setting datestyle doesn't do the trick for you.
setting datestyle changes style for whole sql statement.
How to get this in a single conversion in sql statement so that other
expressions in same sql statement are not affect
On Mon, Jan 11, 2010 at 3:53 AM, Anton Belyaev wrote:
> 2010/1/9 Scott Marlowe :
>> On Fri, Jan 8, 2010 at 3:07 PM, Greg Smith wrote:
>>> Basically, you have a couple of standard issues here:
>>>
>>> 1) You're using RAID-5, which is not known for good write performance. Are
>>> you sure the disk
"Andrus" writes:
> How to get date in server locale format ?
You might be able to get what you want with the to_char() function,
if setting datestyle doesn't do the trick for you.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
Alvaro,
I followed your advice, but using PL/Python. I succeeded, but only with
great difficulty.
To close this off, I'll write these down, together with the work-arounds.
Some of this info would be of use if it were in the documentation.
Problems
1) (documentation)
The doc says P
On Mon, Jan 11, 2010 at 1:16 AM, Yan Cheng Cheok wrote:
> I know I can convert SQL timestamp to unix timestamp, using the following way.
>
> SELECT extract(epoch FROM now());
>
> Now, I have a stored procedure function, which will directly return a table
> row to the caller. One of the row field
On 11 Jan 2010, at 7:16, Yan Cheng Cheok wrote:
> I know I can convert SQL timestamp to unix timestamp, using the following way.
>
> SELECT extract(epoch FROM now());
>
> Now, I have a stored procedure function, which will directly return a table
> row to the caller. One of the row field is "ti
On Sun, 10 Jan 2010 10:49:48 +0100
Vincenzo Romano wrote:
> Try using inheritance.
One of the things I didn't mention is: I've to join these tables
with other tables that may or may not (public) belong to the same
schema.
select sum(i.qty) from s1.list_items li
join public.item i on i.itemid=
2010/1/8 Alban Hertroys :
> Did you turn off seqscans in the postgres.conf?
Seq scan is enabled.
> Could you try a "REINDEX TABLE attachment" again in case you somehow
> reindexed the wrong index or table?
How about this test?
On a dump from before the rows were gone:
# select count(*) from
On Mon, Jan 11, 2010 at 09:08:27AM -0200, Fernando Morgenstern wrote:
> Same result: http://pastebin.ca/1746714
It looks like there is problem with system catalogs. I would suggest to
pg_dump what you can, rm $PGDATA, initdb, and load from backup.
Best regards,
depesz
--
Linkedin: http://www.l
Em 11/01/2010, às 09:04, hubert depesz lubaczewski escreveu:
>> Hi,
>>
>> I have done:
>>
>> # psql -U postgres -p 4000 -l | hexdump -C
>>
>> And got the two databases: http://pastebin.ca/1746711
>>
>> I couldn't find any difference here.
>
> Could you add -qAt to psql options and rerun the
On Mon, Jan 11, 2010 at 08:58:57AM -0200, Fernando Morgenstern wrote:
> Em 09/01/2010, às 19:40, hubert depesz lubaczewski escreveu:
>
> > On Fri, Jan 08, 2010 at 02:39:03PM -0200, Fernando Morgenstern wrote:
> >> postgres=# drop database skynet;
> >> ERROR: database "skynet" does not exist
> >
Em 09/01/2010, às 19:40, hubert depesz lubaczewski escreveu:
> On Fri, Jan 08, 2010 at 02:39:03PM -0200, Fernando Morgenstern wrote:
>> postgres=# drop database skynet;
>> ERROR: database "skynet" does not exist
>
> do:
>
> psql -l | hexump -C
> and examine output.
>
> Best regards,
>
> depes
Em 08/01/2010, às 15:58, Adrian Klaver escreveu:
>
> Actually what is strange is that your previous listing :
> postgres=# select '"' || datname || '"' from pg_database;
> ?column?
> -
> "template1"
> "template0"
> "t1"
> "skynet"
>
> is not the same as the one above:
>
> post
2010/1/9 Scott Marlowe :
> On Fri, Jan 8, 2010 at 3:07 PM, Greg Smith wrote:
>> Basically, you have a couple of standard issues here:
>>
>> 1) You're using RAID-5, which is not known for good write performance. Are
>> you sure the disk array performs well on writes? And if you didn't
>> benchmar
Hello Greg,
Thanks for you extensive reply.
2010/1/9 Greg Smith :
> Anton Belyaev wrote:
>>
>> I think all the IOwait comes during sync time, which is 80 s,
>> according to the log entry.
>>
>
> I believe you are correctly diagnosing the issue. The "sync time" entry in
> the log was added there
Hi all.
It's not clear to me how composite values are used in conditions (WHERE/CHECK).
In my case I have something like this:
-- begin snippet
CREATE TABLE test_tab (
col1 timestamp not null,
col2 int8 not null,
col3 text not null
);
CREATE INDEX i_test_tab_col1 ON test_tab( col1 );
SE
In response to Yan Cheng Cheok :
> I have a "lot" table with 2 columns, with one of the column is current
> timestamp.
>
> I try to return another custom fields, which its calculation is based on
> timestamp.
>
> (For simplicity, I include only 1 field in following example)
>
> CREATE OR REPLA
On Sunday 10. January 2010 22.57.38 Andrus wrote:
> Server lc_times contains non-US locale.
>
> SELECT CURRENT_DATE::TEXT
>
> still returns date in format -MM-DD
>
> How to get date in server locale format ?
Pg doesn't care about lc_times.
http://www.postgresql.org/docs/current/static/data
I have a "lot" table with 2 columns, with one of the column is current
timestamp.
I try to return another custom fields, which its calculation is based on
timestamp.
(For simplicity, I include only 1 field in following example)
CREATE OR REPLACE FUNCTION create_lot(text)
RETURNS lot AS
$BODY
Server lc_times contains non-US locale.
SELECT CURRENT_DATE::TEXT
still returns date in format -MM-DD
How to get date in server locale format ?
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mai
On 11/01/2010 2:16 PM, Yan Cheng Cheok wrote:
I know I can convert SQL timestamp to unix timestamp, using the following way.
SELECT extract(epoch FROM now());
Now, I have a stored procedure function, which will directly return a table row to the
caller. One of the row field is "timestamp" type
Dear list,
Currently I am working on a user C-Function which should create a cache object
on the first call and afterwards return a set of computed values for each
argument combination it is called with.
My Problem is how to get the cache object saved over multiple calls. Without
the SRF I cou
Not working. strftime is use to convert date and time to a string.
Thanks and Regards
Yan Cheng CHEOK
--- On Mon, 1/11/10, Vincenzo Romano wrote:
> From: Vincenzo Romano
> Subject: Re: [GENERAL] Get Unix timestamp from SQL timestamp through libpq
> To: "Yan Cheng Cheok"
> Cc: pgsql-general@p
79 matches
Mail list logo
