On 18/12/2009 1:18 PM, Daniel Popowich wrote:
For example, it was at first surprising to discover in my before-row
trigger that foreign key constraints had not yet been checked (which
I assumed I could take for granted in my trigger since I had defined
the column constraint).
Nope. CHECK const
2009/12/18 Scott Marlowe :
> The main reason to avoid rules is that they're much less efficient
> than triggers. We use partitioning at work for our stats db, and
> partition by day, and we have about 2 years worth of stats data, so
> our insert trigger has about 700 if / elseif / else conditions
I am designing a DB where column/table constraints are not sufficient
for data integrity (e.g., guaranteeing non-recursive tree graphs) so
I'm writing my first complicated triggers and I have been searching
docs and archives of this list for detailed information on when
triggers are fired relative
On Thu, Dec 17, 2009 at 7:51 PM, David Boreham wrote:
> Scott Marlowe wrote:
>>
>> I would recommend using a traffic shaping router (like the one built
>> into the linux kernel and controlled by tc / iptables) to simulate a
>> long distance connection and testing this yourself to see which
>> repl
Scott Marlowe wrote:
I would recommend using a traffic shaping router (like the one built
into the linux kernel and controlled by tc / iptables) to simulate a
long distance connection and testing this yourself to see which
replication engine will work best for you.
Netem :
http://www.linuxfo
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
> One concern I have about these trigger based replication systems is
> that I fear it may ping the slave for each and every DML statement
> separately in time and in a transaction. My slave will literall
I would recommend using a traffic shaping router (like the one built
into the linux kernel and controlled by tc / iptables) to simulate a
long distance connection and testing this yourself to see which
replication engine will work best for you.
On Thu, Dec 17, 2009 at 7:35 PM, Gauthier, Dave wrot
--- On Thu, 12/17/09, Adrian Klaver wrote:
>
> Would it be possible to see the table schemas and indices
> ?
>
> >
Sure (you asked for it!!) :
CREATE TABLE packet
(
id integer NOT NULL DEFAULT nextval('packet_id_seq'::regclass),
packet_uuid uniqueidentifier NOT NULL DEFAULT newid(),
One concern I have about these trigger based replication systems is that I fear
it may ping the slave for each and every DML statement separately in time and
in a transaction. My slave will literally be 1400 miles away and all
replication communications will be over the net. If I have a transa
On Wed, Dec 16, 2009 at 4:21 PM, Christine Penner
wrote:
> Hi,
>
> If we have clients that are going to buy new computers or upgrade current
> ones, what we can recommend to them for optimal system performance to run
> Postgres. These can be servers or desktop PCs. We can have from 1-10 users
> in
On Thu, Dec 17, 2009 at 12:35 PM, Madison Kelly wrote:
> Gauthier, Dave wrote:
>>
>> Hi Everyone:
>>
>>
>> Tomorrow, I will need to present to a group of managers (who know nothing
>> about DBs) why I chose to use PG over MySQL in a project, MySQL being the
>> more popular DB choice with other eng
On Dec 17, 2009, at 11:13 AM, Raimon Fernandez wrote:
> I'm trying to integrate the extended query protocol with my libraries.
>
> I'm sending a simple SELECT to validate the method, but I'm getting an
> Invalid Message Format.
I did not add up your byte count, but maybe this will help:
(wri
Op 17-12-09 23:46, Tom Lane schreef:
This is just one of many many things that could be improved by getting
off of 7.3. In general, complaining about performance (or features)
of a seven-year-old, long since EOL'd release is not a productive use of
anybody's time.
I'm sorry, didn't mean to
On Thu, Dec 17, 2009 at 8:29 AM, Vincenzo Romano
wrote:
> 2009/12/17 Sam Jas
>>
>> Rule is not advisable, Trigger is the best solution.
>
> Does the trigger solution need the TABLE CHECK constraint?
> It looks to me it won't.
The table check constraint is used during selects to pick just the
chi
Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian writes:
> > > I installed PL/pgSQL by default via initdb with the attached patch. The
> > > only problem is that pg_dump still dumps out the language creation:
> > > CREATE PROCEDURAL LANGUAGE plpgsql;
> > > ALTER PROCEDURAL LANGUAGE pl
Thomas Kellerer writes:
> What's wrong with:
> alter table person add column address varchar(64) not null default '';
This:
regression=# alter table person add column address varchar(64) not null default
'';
ERROR: Adding columns with defaults is not implemented.
Add the column, then u
writes:
> , unique ( a_id , ( case when actual then 0 else id end ) )
> Why can i not define the unique constraint in the
> create table?
The syntax for a unique constraint in CREATE TABLE is defined by the SQL
standard, and it doesn't include expressions. More than the syntax, a
unique const
- "CG" wrote:
> --- On Tue, 12/15/09, Adrian Klaver wrote:
>
> > From: Adrian Klaver
> > Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
> > To: cgg...@yahoo.com
> > Cc: "postgresql listserv" , "Craig
> Ringer" , "Scott Marlowe"
>
> > Date: Tuesday, December 15, 2009, 6:53
Antonio Goméz Soto wrote on 17.12.2009 22:26:
Hi,
I am regularly altering tables, adding columns setting default values etc.
This very often takes a very long time and is very disk intensive, and this
gets pretty annoying.
Things are hampered by the fact that some of our servers run PG 7.3
Sup
On Thursday 17 December 2009, Antonio Goméz Soto
wrote:
> Hi,
>
> I am regularly altering tables, adding columns setting default values
> etc. This very often takes a very long time and is very disk intensive,
> and this gets pretty annoying.
>
> Things are hampered by the fact that some of our s
begin ;
create table a (
id serial primary key
, info text not null -- not interested here
) ;
create table b (
id serial primary key
, a_id int not null references a
, info text not null -- not interested here
, actual bool not null
) ;
create unique index b_actual on b
( a_id , (
Hi,
I am regularly altering tables, adding columns setting default values etc.
This very often takes a very long time and is very disk intensive, and this
gets pretty annoying.
Things are hampered by the fact that some of our servers run PG 7.3
Suppose I have a table and I want to add a non NUL
Christine Penner wrote:
Hi,
If we have clients that are going to buy new computers or upgrade
current ones, what we can recommend to them for optimal system
performance to run Postgres. These can be servers or desktop PCs. We can
have from 1-10 users in at a time. At this point all of our dat
Gauthier, Dave wrote:
Hi Everyone:
Tomorrow, I will need to present to a group of managers (who know
nothing about DBs) why I chose to use PG over MySQL in a project, MySQL
being the more popular DB choice with other engineers, and managers
fearing things that are “different” (risk). I ha
Bruce Momjian wrote:
> pg_migrator (not pg_upgrade) has been used by many people to migrate
> from 8.3 to 8.4. I just helped someone yesterday with a migration.
> pg_migrator threw an error because they had reindexed pg_largeobject,
> and pg_migrator was not prepared to handle that. They also ha
Tom Lane wrote:
> "Raymond O'Donnell" writes:
> > On 16/12/2009 15:01, Richard Broersma wrote:
> >> It looks like the future 8.5 release will be able to
> >> preform an in-place upgrade on 8.4.
>
> > Really? That would be *wonderful*. I know it's impossible to be
> > definitive, but how likely wo
Tom Lane wrote:
> "Raymond O'Donnell" writes:
> > On 16/12/2009 15:01, Richard Broersma wrote:
> >> It looks like the future 8.5 release will be able to
> >> preform an in-place upgrade on 8.4.
>
> > Really? That would be *wonderful*. I know it's impossible to be
> > definitive, but how likely wo
Abraham, Danny wrote:
“postgres” cores on SUN 2.9. Disk is in 100% IO.
(dbx) where
[1] 0x65eacdd4(0x7fffa36c, 0x400, 0x10032d4e8, 0x1,
0x48, 0xd6), at 0x65eacdd4
[2] XLogFileInit(0x48, 0xd6, 0x7fffa85d, 0x1, 0x488000, 0x0),
at 0x10007955c
...
Any idea?
Vincenzo Romano writes:
> Is there any performance difference in writing trigger function in SQL
> rather than PL/PgSQL?
SQL functions don't support being called as triggers, so performance
questions are a bit beside the point ...
regards, tom lane
--
Sent via pgsql-gen
Gauthier, Dave wrote:
Hi Everyone:
Tomorrow, I will need to present to a group of managers (who know
nothing about DBs) why I chose to use PG over MySQL in a project,
MySQL being the more popular DB choice with other engineers, and
managers fearing things that are “different” (risk). I have
Hi all.
Is there any performance difference in writing trigger function in SQL
rather than PL/PgSQL?
In my case the body would actually be 1 line.
--
Vincenzo Romano
NON QVIETIS MARIBVS NAVTA PERITVS
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your s
--- On Tue, 12/15/09, Adrian Klaver wrote:
> From: Adrian Klaver
> Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
> To: cgg...@yahoo.com
> Cc: "postgresql listserv" , "Craig Ringer"
> , "Scott Marlowe"
> Date: Tuesday, December 15, 2009, 6:53 PM
> On Tuesday 15 December 2009 2:
On Thursday 17 December 2009 09:51:19 Richard Broersma wrote:
> On Thu, Dec 17, 2009 at 8:23 AM, Gauthier, Dave
wrote:
> > How difficult is it to switch the master's hat from one DB instance to
> > another? Let's say the master in a master-slave scenario goes down but
> > the slave is fine. Can
"Gauthier, Dave" writes:
> I am more concerned with getting a robust DB replication system up and
> running. Bucardo looks pretty good, but I've just started looking at
> the options. Any suggestions?
Master Slave replication? Meaning no writes on the "sister site".
If yes, consider Londiste
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
> How difficult is it to switch the master's hat from one DB instance
> to another? Let's say the master in a master-slave scenario goes
> down
On Thu, Dec 17, 2009 at 8:23 AM, Gauthier, Dave wrote:
> How difficult is it to switch the master's hat from one DB instance to
> another? Let's say the master in a master-slave scenario goes down but the
> slave is fine. Can I designate the slave as being the new master, use it for
> read/wr
On Dec 16, 2009, at 11:19 AM, Erwin Brandstetter wrote:
> Hello,
>
> I need a long text form from a file in my plpgsql variable.
> Can anyone think of a more straightforward way to read the file than
> the following:
>
>
> CREATE FUNCTION test() RETURNS void AS
> $BODY$
> DECLARE
>
How difficult is it to switch the master's hat from one DB instance to another?
Let's say the master in a master-slave scenario goes down but the slave is
fine. Can I designate the slave as being the new master, use it for
read/write, and then just call the broken master the new slave once it
Actually, the DB I'm working on is rather small but has a somewhat complex
system of constraints and triggers that maintain the data. Queries will
outnumber writes (20x at least). And the DB has to be mirrorred at a sister
site a couple thousand miles away, so I'm looking for a robust DB repli
On Thu, Dec 17, 2009 at 3:55 PM, Gauthier, Dave wrote:
> They just called the meeting, or at least that part of it. There seems to be
> a battle brewing, some MySQL advocates are angry, concerned, fearful, ... I
> dont know why for sure.
in places like that it is inevitable. there's always goi
Hello again,
I'm trying to integrate the extended query protocol with my libraries.
I'm sending a simple SELECT to validate the method, but I'm getting an Invalid
Message Format.
50 => P
00 00 00 29 => length
6D7973746174656D656E74 00 => mystatement + null
73656C656374202A2066726F6D206D797461
They just called the meeting, or at least that part of it. There seems to be a
battle brewing, some MySQL advocates are angry, concerned, fearful, ... I dont
know why for sure. My managers, who advocate my position and PG are preparing,
but the decision will be made by higher-ups who really do
2009/12/17 Dimitri Fontaine :
> Vincenzo Romano writes:
>> Is there any performance study for the trigger-based implementation?
>
> Consider that if you use RULE to partition, when you DROP a partition
> the INSERTs are locked out because the query depends on the table being
> droped.
>
> That alo
On Wednesday 16 December 2009 5:05:19 pm Justin Bailey wrote:
> Greetings! I am trying to avoid the old problem of inserting a 40
> character string into a 20 character field. However, I'd like to avoid
> hard-coding the acceptable length (20). Is there a way to say "cast to
> the same type as a gi
On Thu, Dec 17, 2009 at 10:24:28AM -0500, Tom Lane wrote:
> Justin Bailey writes:
> > If s was automatically truncated, the insert would succeed, but it
> > fails with a "value too long" error.
>
> Oh, I thought the failure was the behavior you wanted. There's
> no automatic truncation here ---
Vincenzo Romano writes:
> Is there any performance study for the trigger-based implementation?
Consider that if you use RULE to partition, when you DROP a partition
the INSERTs are locked out because the query depends on the table being
droped.
That alone could lead you to stop considering RULEs
2009/12/17 Sam Jas
>
> Rule is not advisable, Trigger is the best solution.
Does the trigger solution need the TABLE CHECK constraint?
It looks to me it won't.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/m
Justin Bailey writes:
> On Wed, Dec 16, 2009 at 7:20 PM, Tom Lane wrote:
>> Really? Works for me, in everything back to 7.3.
> If s was automatically truncated, the insert would succeed, but it
> fails with a "value too long" error.
Oh, I thought the failure was the behavior you wanted. There
On Wed, Dec 16, 2009 at 7:20 PM, Tom Lane wrote:
> Really? Works for me, in everything back to 7.3.
>
I must be missing something, because this function fails:
CREATE OR REPLACE FUNCTION insertShort()
RETURNS VOID AS
$BODY$
DECLARE
s Short.shortCol%TYPE;
BEGIN
SELECT longCol I
2009/12/17 David Fetter :
>> You really think that calling and running a trigger function for every
>> line is the best solution?
>
> Yes. The trigger function is choosing from a small subset of the
> tables, or you know which tables exactly the rows are going into and
> insert them there.
So I u
On Thu, Dec 17, 2009 at 02:41:40PM +0100, Vincenzo Romano wrote:
> > 2009/12/17 Sam Jas
> > Rule is not advisable, Trigger is the best solution.
> If you have to choose among a couple hundred partition tables, the
> trigger function body is far from trivial!
It's possible to generate such code a
On Wed, Dec 16, 2009 at 4:02 PM, Gauthier, Dave wrote:
> Hi Everyone:
>
> Tomorrow, I will need to present to a group of managers (who know nothing
> about DBs) why I chose to use PG over MySQL in a project, MySQL being the
> more popular DB choice with other engineers, and managers fearing things
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
> - MySQL is horizontally scalable via clustering and multi-master
> replication (though you must beware of numerous gotchas). PostgreSQL can
> be
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
NotDashEscaped: You need GnuPG to verify this message
> I need a long text form from a file in my plpgsql variable.
> Can anyone think
Why?
If you have to choose among a couple hundred partition tables, the
trigger function body is far from trivial!
You really think that calling and running a trigger function for every
line is the best solution?
2009/12/17 Sam Jas
>
> Rule is not advisable, Trigger is the best solution.
>
> ---
Rule is not advisable, Trigger is the best solution.
---
Thanks
Sam Jas
--- On Thu, 17/12/09, Vincenzo Romano wrote:
From: Vincenzo Romano
Subject: [GENERAL] Table Partitioning Advice Request
To: pgsql-general@postgresql.org
Date: Thursday, 17 December, 2009, 11:05 AM
Hi all.
I'm planning
Hi all.
I'm planning to implement table partitioning as "suggested" (among
other sources) in the official documentation.
I'm using v8.4.2 at the moment.
My case is far from the general one as:
1. I'll never UPDATE or DELETE rows from partitioned tables (only INSERTs)
2. Rows will be inserted one-
On Wed, Dec 16, 2009 at 05:18:12PM -0800, yuliada wrote:
> Sam Mason wrote:
> > How about combining all 1000 selects into one?
>
> I can't combine these selects into one, I need to run them one after
> another.
Hum, difficult. What other information is in the row that you need
back? Can you tur
Hi,
"postgres" cores on SUN 2.9. Disk is in 100% IO.
(dbx) where
[1] 0x65eacdd4(0x7fffa36c, 0x400, 0x10032d4e8, 0x1, 0x48,
0xd6), at 0x65eacdd4
[2] XLogFileInit(0x48, 0xd6, 0x7fffa85d, 0x1, 0x488000, 0x0), at
0x10007955c
[3] XLogWrite(0x48, 0x6, 0x1, 0x1004
Hi,
"postgres" cores on SUN 2.9. Disk is in 100% IO.
(dbx) where
[1] 0x65eacdd4(0x7fffa36c, 0x400, 0x10032d4e8, 0x1, 0x48,
0xd6), at 0x65eacdd4
[2] XLogFileInit(0x48, 0xd6, 0x7fffa85d, 0x1, 0x488000, 0x0), at
0x10007955c
[3] XLogWrite(0x48, 0x6, 0x1, 0x1004
On Thu, 17 Dec 2009 10:38:32 +0100
"A. Kretschmer" wrote:
> In response to Ivan Sergio Borgonovo :
> > I've a web application.
> >
> > I'm logging data related to the landing page and subsequent
> > selected hits.
> >
> > create table track_hit (
> > hitid serial not null,
> > /* pk? I don'
On Dec 16, 2009, at 10:30 PM, Craig Ringer wrote:
> - If you don't care about your data, MySQL used with MyISAM is *crazy* fast
> for lots of small simple queries.
This one causes me no end of grief as too often it's simply touted as "MyISAM
is fast(er)" while leaving of the bit about "for lot
In response to Ivan Sergio Borgonovo :
> I've a web application.
>
> I'm logging data related to the landing page and subsequent selected
> hits.
>
> create table track_hit (
> hitid serial not null,
> /* pk? I don't mind if serial wrap around
> pk could be (hitid, tracking_time) */
> esi
I've a web application.
I'm logging data related to the landing page and subsequent selected
hits.
create table track_hit (
hitid serial not null,
/* pk? I don't mind if serial wrap around
pk could be (hitid, tracking_time) */
esid varchar(32), -- related to session
tracking_time timest
Hi,
Would one day the Talend people understand that Astroturfing is definitly not a
good marketing strategy ?
Please stop your lame posting once and for all, we are all tired of reading
your useless babbling.
Vincent
> -Message d'origine-
> De : pgsql-general-ow...@postgresql.org
> [mai
Dave,
please also check out the licence and costs terms in detail.
Especially: is it given that the planned usage willl continue to be
within the allowed bounds for MySQL-GPL? Are otherwise the costs for
MySQL-commercial budgeted or a reserve founded?
PostgreSQL has here a GIANT advantage with a
66 matches
Mail list logo
