On 03/28/2012 10:38 AM, Gregg Jaskiewicz wrote:
They seem to claim up to 70% speed gain.
Did anyone proved it, tested it - with PostgreSQL in particular ?
RedHat's RHEL5 kernel is 2.6.18 with a bunch of backported features.
Oracle just yanks that out and puts a closer to stock 2.6.32 based
k
I work a lot with Unix times as integers, but would like to store them
in Postgres as 'timestamp(0) with time zone' for convenience and
readability. Unfortunately the syntax to translate between the two is
a little cumbersome, so I'm looking at hiding it away behind a
function - or a cast. However,
2012/4/3 Alban Hertroys
> On 2 Apr 2012, at 22:28, Bartosz Dmytrak wrote:
>
> > That is right, there is no sense to use cursors here...
>
> I think you're wrong there: The OP is querying a system table for tables
> of a certain name, which I expect can contain multiple rows for tables of
> the sa
I think You can use epoch
there is an example:
http://www.postgresql.org/docs/9.1/static/functions-datetime.html
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';
Regards,
Bartek
2012/4/3 Chris Angelico
> I work a lot with Unix times as integers, but would like to
On Tue, Apr 3, 2012 at 7:11 PM, Bartosz Dmytrak wrote:
> I think You can use epoch
> there is an
> example: http://www.postgresql.org/docs/9.1/static/functions-datetime.html
>
>
> SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1
> second';
Yep, but when you do that a lot, your
There is a build in function which encapsulates that statement:
SELECT to_timestamp (982384720);
EXPLAIN ANALYZE shows:
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003 rows=1
loops=1)
so this looks cheap
Regards,
Bartek
2012/4/3 Chris Angelico
> On Tue, Apr 3, 2012 at 7:1
On 02/04/2012 18:06, Tom Lane wrote:
> Ronan Dunklau writes:
>> I'm trying to define a "weighted mean" aggregate using postgresql create
>> aggregate feature.
>
>> I've been able to quickly write the required pgsql code to get it
>> working, but after testing it on a sample 1 rows table, it se
***
CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Tab
Bartek, Thanks. The reason I use the cursor is that I want to check the table
is in the pg_tables or not,
If it exists, the function will execute successfully, if not, it will raise
the message that the table doesn't exist. For the schema part, I assume the
people has set the search_path to that
Alban, Thanks.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615244.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-g
On 04/03/2012 07:01 AM, leaf_yxj wrote:
***
CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table
Hi,
Is possible change default COLLATE/CTYPE to Portugese_Brazil.1252 ? I am
using Centos6 with Postgres 9.1 in this installation of Postgres has only these
locales installed.
pt_BR
pt_BR.iso88591
pt_BR.utf8
pt_PT
pt_PT@euro
pt_PT.iso88591
pt_PT.iso885915@euro
pt_PT.utf8
Are there some di
Adrian, Thanks. Even I try use '' to quote the character. I still get the
error as follows :
rrp=> truncate table t1;
TRUNCATE TABLE
rrp=> select truncate_t('t1');
ERROR: table "t1" does not exist
Thanks.
Grace
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Please-he
leaf_yxj writes:
> ***
> CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
> RETURNS VOID
> AS
> $$
> BEGIN
> EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
I think you need a space there:
EXECUTE '
Tom,
Thanks. I found out the key issue it. It's because the truncate command
can't have the "cascade".
For the other people reference. The right funcitons are :
***
CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
thanks for the suggestions. the light has gone on and i have it working as of
about 15 minutes ago. i'm going to revisit the documentation and possibly make
suggestions about making things a little clearer, or else issue a mea culpa
about my reading comprehension. don't know which just yet.
ric
I have a situation that I'd like some help resolving.
Using PostgreSQL 8.4. on Linux, I have three things
coming together that cause me pain. I have a VIEW used by a bunch of
queries. Usually, these queries are fairly short (subsecond) but
sometimes they can be very long (days). I also update this
I think you need a space there:
>
>EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ' CASCADE;';
>
indeed, that is my fault - sorry
> > EXCEPTION
> > WHEN undefined_table THEN
> > RAISE EXCEPTION 'Table "%" does not exists', tablename;
>
> It's really a pretty b
One more thing:
TRUNCATE has option CASCASE:
http://www.postgresql.org/docs/9.1/static/sql-truncate.html
I don't remember since when, but 9.X has this option.
Another thing: Do You really need this function.
AFAIK since 8.4 postgres has TRUNCATE privilage on Table
http://www.postgresql.org/do
On Tue, Apr 3, 2012 at 12:01 PM, Jon Nelson wrote:
> I have a situation that I'd like some help resolving.
> Using PostgreSQL 8.4. on Linux, I have three things
> coming together that cause me pain. I have a VIEW used by a bunch of
> queries. Usually, these queries are fairly short (subsecond) but
On Tue, Apr 3, 2012 at 12:16 PM, Merlin Moncure wrote:
> On Tue, Apr 3, 2012 at 12:01 PM, Jon Nelson wrote:
>> I have a situation that I'd like some help resolving.
>> Using PostgreSQL 8.4. on Linux, I have three things
>> coming together that cause me pain. I have a VIEW used by a bunch of
>> qu
Jon Nelson wrote on 03.04.2012 19:01:
I also update this view with CREATE OR REPLACE VIEW every 15-30 minutes
That is a highly questionable approach.
What real problem are you trying to solve with that?
Maybe there is a better solution that does not require changing the view.
--
Sent via p
Bartek,
Thanks for your reminding. I don't know why CASCASE doesn't work in my
greenplum postgresql database (version 8.2.14). I can create the function
successfully without any errors. But when i call it, I alwasy got errors if I
include the CASCADE. If I delete the CASCADE, it will works. I d
Hi Bartek
One more question, In oracle, when you create table using the default option,
the parent table can't be delete if there is any child table exist. Usually, I
won't use the cascade option. I will truncate or delete one by one. what is
postgresql default for these???
Thanks.
Regard
On 04/03/12 10:49 AM, leaf_yxj wrote:
--- I amn't sure what's differences between truncate and delete in
postgresql. Could you do me a favour to tell me about this.
delete has to go through and flag each tuple for deletion so vacuum can
eventually go through and reclaim them for reuse. trunc
Hi John, Thanks for your reply. Just to confirm : so truncate table means the
space will be reclaim for reuse ???
Thanks.
Grace
At 2012-04-04 02:01:59,"John R Pierce [via PostgreSQL]"
wrote:
On 04/03/12 10:49 AM, leaf_yxj wrote:
> --- I amn't sure what's differences between truncate and
On Tue, Apr 3, 2012 at 12:30 PM, Jon Nelson wrote:
> On Tue, Apr 3, 2012 at 12:16 PM, Merlin Moncure wrote:
>> On Tue, Apr 3, 2012 at 12:01 PM, Jon Nelson
>> wrote:
>>> I have a situation that I'd like some help resolving.
>>> Using PostgreSQL 8.4. on Linux, I have three things
>>> coming toget
On Tue, Apr 3, 2012 at 1:36 PM, Merlin Moncure wrote:
> On Tue, Apr 3, 2012 at 12:30 PM, Jon Nelson wrote:
>> On Tue, Apr 3, 2012 at 12:16 PM, Merlin Moncure wrote:
>>> On Tue, Apr 3, 2012 at 12:01 PM, Jon Nelson
>>> wrote:
I have a situation that I'd like some help resolving.
Using
If You mean parent and child tables as connected by relation (primery key -
foreign key) then child table will be truncated regardless the relation
type, if CASCADE exists.
This applies to PG 9.1.3 (I've got only this version).
Regards,
Bartek
2012/4/3 leaf_yxj
> Hi Bartek
> One more question,
On Tue, Apr 3, 2012 at 1:41 PM, Jon Nelson wrote:
> On Tue, Apr 3, 2012 at 1:36 PM, Merlin Moncure wrote:
>> Generally speaking, in SQL, locks are held until the transaction
>> commits; there are tons of reasons why things have to work that way.
>> Anyways, I'm betting your requirement to have to
Jon Nelson wrote on 03.04.2012 20:41:
Close, but not quite. It's not rotation but every N minutes a
newly-built table appears. I'd like that table to appear as part of
the view as soon as possible.
Can't you use table inheritance for that?
--
Sent via pgsql-general mailing list (pgsql-gener
Hello Everyone
I facing a big problem ,when pg_dump start .CPU load become 100%.
DB Size 35 GB running with e commerce web site. Insert transaction record
successfully but Update transaction is not going through.
Could you please help to figure out where is the problem ?
Thanks
Prashant
On Tue, Apr 3, 2012 at 1:45 PM, Thomas Kellerer wrote:
> Jon Nelson wrote on 03.04.2012 20:41:
>
>> Close, but not quite. It's not rotation but every N minutes a
>> newly-built table appears. I'd like that table to appear as part of
>> the view as soon as possible.
>
>
> Can't you use table inheri
On 04/03/12 11:13 AM, leaf_yxj wrote:
Hi John, Thanks for your reply. Just to confirm : so truncate table
means the space will be reclaim for reuse ???
yes, all the tablespace is immediately returned to the file system when
the transaction with the TRUNCATE statement commits.
--
john r pi
On Tue, Apr 3, 2012 at 2:45 PM, Thomas Kellerer wrote:
> Jon Nelson wrote on 03.04.2012 20:41:
>
>> Close, but not quite. It's not rotation but every N minutes a
>> newly-built table appears. I'd like that table to appear as part of
>> the view as soon as possible.
>
>
> Can't you use table inheri
On Tue, Apr 3, 2012 at 7:21 PM, Jon Nelson wrote:
> On Tue, Apr 3, 2012 at 2:45 PM, Thomas Kellerer wrote:
>> Jon Nelson wrote on 03.04.2012 20:41:
>>
>>> Close, but not quite. It's not rotation but every N minutes a
>>> newly-built table appears. I'd like that table to appear as part of
>>> the
Can you not nice the dump process to free up resources during the dump? Of
course this will not free up any locks, and will make them hang around longer
as the dump is slowed down.
Brent Wood
GIS/DBA consultant
NIWA
+64 (4) 4 386-0300
From: pgsql-general
i read documents,i find it that concurrent index create don't lock write.but
need scan table twice.it explain is following as:
It scans the table once to initially build the index, then makes a second
pass to look for things added after the first pass.
please explain 1. what happens when concu
Hi all,
I'm looking into Postgres' internals, and had two quick questions that
are related to each other.
(1) What's the difference between advancing the command counter and
updating an active snapshot? For example, I see that DefineRelation()
increments the command counter, but explain.c / copy.
I am having a problem trying to create a language in a new install of Postgres
- I have installed postgres 9.1 on a minimal (i.e. no GUI) CentOS 6.2 system.
- Psql is running and I am able to connect to the database from pgAdmin
running on a Windows box.
- Postgres was installed in /opt/po
Hi;
Anyone have any idea where to find RPMs for IBM Power architecture
chips for SUSE Enterprise 11, PostgreSQL 9.1?
I checked the OpenSuSE build site and couldn't even find SRPMS for
postgresql-server. Any other options?
Best Wishes,
Chris Travers
--
Sent via pgsql-general mailing list (pgsq
On Tue, Apr 3, 2012 at 9:50 PM, leo xu wrote:
> i read documents,i find it that concurrent index create don't lock
> write.but
> need scan table twice.it explain is following as:
> It scans the table once to initially build the index, then makes a second
> pass to look for things added after th
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
> Now my problem is importing the data in bulk, and to keep the version
> of the record that has the longest interval value (the third field in
> the CSV below). Refer to the entries of 03/29 of the *.gs files. The
...
> Any advice/ideas as to t
On Tue, Apr 3, 2012 at 8:26 PM, Tom Harkaway wrote:
> I am having a problem trying to create a language in a new install of
> Postgres
> - I have installed postgres 9.1 on a minimal (i.e. no GUI) CentOS 6.2
> system.
> - Psql is running and I am able to connect to the database from pgAdmin
> ru
44 matches
Mail list logo
