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
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
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
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
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
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
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
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
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
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
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
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
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 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 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
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 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=
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 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
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
"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
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
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
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
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
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
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 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,
>
=?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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
"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
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
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
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 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
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 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 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
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 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
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.
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
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
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
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
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
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
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?
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
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:
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
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:
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
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
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 '
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
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");
> }
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
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
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
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
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"
79 matches
Mail list logo
