Re: Is it possible to disable insert/update/delete triggers for one transaction and not another?

On Wed, Jun 28, 2006 at 11:59:36AM -0700, Karen Hill wrote:
> I have an insert/update/delete trigger on all my tables which add data
> to a log table.
> 
> I would like to be able to disable them when the tables are called from
> one stored proceedure I have.  Yet I would still like those triggers to
> fire on any other operation that is happening concurrently.  Is this
> even possible?

Best bet would be to have the procedure only execute as a given user
(probably via security definer) and detect that in the trigger.
--

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby <at> pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

Re: Notes on converting from MySQL 5.0.x to PostgreSQL

On Fri, Jun 30, 2006 at 11:01:19AM -0700, David Fetter wrote:
> On Fri, Jun 30, 2006 at 01:41:53PM -0400, Tom Lane wrote:
> > David Fetter <david <at> fetter.org> writes:
> > > On Fri, Jun 30, 2006 at 11:39:04AM -0400, Tom Lane wrote:
> > >> It might be worth pointing out that mysql's replication falls
> > >> over if you so much as look at it crosseyed.  I have not had to
> > >> use it for production purposes, but I can tell you that the mysql
> > >> replication regression tests fail ... irreproducibly of course
> > >> ...  almost one time in two in Red Hat's build environment.
> > 
> > > Are those tests, or at least descriptions of them, available?
> > 
> > Sure, it's just the standard "make test" sequence in mysql's source.
> 
> Uh oh.  I'm a little worried about writing tests based on GPLed code
> for Slony-I or other replication systems.  Might these need to be
> clean-roomed?

Is there actually a lack of ideas for our regression tests, or a lack of
people/motivation to work on them?
--

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby <at> pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Notes on converting from MySQL 5.0.x to PostgreSQL

On Fri, Jun 30, 2006 at 11:39:04AM -0400, Tom Lane wrote:
> Scott Marlowe <smarlowe <at> g2switchworks.com> writes:
> > On Fri, 2006-06-30 at 08:17, Jason McManus wrote:
> >> * Replication support still rudimentary.
> 
> > Hmmmm.  I think that's an overly simplistic evaluation.  The slony
> > replication engine is actually VERY advanced, but the administrative
> > tools consist mostly of "your brain".  hehe.  That said, once you've
> > learned how to drive it, it's quite amazing.  Keep in mind, slony can be
> > applied to a living database while it's running, and can run between
> > different major versions of postgresql.  That's a pretty advanced
> > feature.  Plus, if the replication daemons die (kill -9ed or whatever)
> > you can restart replication and slony will come right back where it was
> > and catch up.
> 
> It might be worth pointing out that mysql's replication falls over
> if you so much as look at it crosseyed.  I have not had to use it
> for production purposes, but I can tell you that the mysql replication
> regression tests fail ... irreproducibly of course ... almost one time
> in two in Red Hat's build environment.  I've been able to trace a few of
> these failures to quirks of the build environment, like trying to build
> x86 and x86_64 at the same time in different chroots of the same machine
> (must take care not to use same TCP port numbers for tests), but it
> still seems flaky as hell.

I attended a talk about MySQL and High Availability once and was pretty
unimpressed. Lots of 'now you take the database down and copy files
around' and the like. Nothing remotely close to the abilities of Slony.
--

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby <at> pervasive.com

Re: Notes on converting from MySQL 5.0.x to PostgreSQL

On Fri, Jun 30, 2006 at 05:16:46PM -0500, Scott Marlowe wrote:
> This is essentially correct.  Note that I can use pgpool with postgresql
> and get about the same behaviour as mysql's replication, with the same
> basic draw backs, that it's best to copy the database between shutdown
> machines, and things that are dynamically evaluated can cause issues. 
> With pgpool I get synchronous replication with automatic failover, and
> it's dead simple to build and install.

Maybe we should be promoting pgpool's replication in that light. "If
you're looking something that works like MySQL's replication, use
pgpool. If you want a much more sophisticated and complex mechanism, use
Slony".

> Which kind of shows off the difference in philosophy between the two
> development camps.  The postgresql folks are very very picky about what
> gets put into the main package, and let's face it, pgpool, while neat,
> is not really ready for integration into the backend.  Meanwhile, a
> nearly identical replication system IS integrated into the backend of
> MySQL, warts and all for the sake of convenience of the users, and
> possibly marketing.
> 
> It's not that one way is so much better than the other, it's just
> indicative of how the two camps operate.

I'd say google:'mysql gotchas' is a pretty good indicator of that. ;)

MySQL tries desperatly hard to make databases 'easy', but the reality is
that unless it's a pretty trivial embedded database, databases (both
RDBMSes and database design and use) aren't easy; they're probably one
of the most complex pieces of IT in commmon use today. IMO, in trying to

Re: pgsql vs mysql

On Fri, Jun 30, 2006 at 01:07:32PM -0400, Merlin Moncure wrote:
> * mysql has decent out of the box replication that is easy to set up
> (one day I hope pg get hot PITR which is analagous feature)

Actually, PITR is in no way analagous. Try replicating something like
'INSERT INTO table SELECT random();' on MySQL and note how all the data
is different.

pgpool replication is equivalent to MySQL's replication. Or if you want
more sophisticated replication, use Slony.

BTW, anyone curious about the differences should take a look at
google:'mysql gotchas' (there's also a PostgreSQL section on that site).
See also the MySQL/PostgreSQL thread that was on this list yesterday.
--

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby <at> pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

Re: Notes on converting from MySQL 5.0.x to PostgreSQL

Chris Browne wrote:
> ron.l.johnson <at> cox.net (Ron Johnson) writes:
> 
>> Scott Marlowe wrote:
>> [snip]
>>> However, the more interesting thing here, is that every
>>> statement, including DDL is transactable, except for a couple of
>>> big odd ones, like create database. So, in postgresql, you can do:
>>>
>>> begin;
>>> create table xyz...
>>> alter table abc...
>>> insert into abc select * from iii
>>> update iii...;
>>> drop table iii;
>>> (oops, I messed up something)
>>> rollback;
>> But isn't that what it means to be "transactional"?  Or am I spoiled
>>  by my "big, expensive enterprise database"?
> 
> DDL commonly hasn't been "able to be rolled back," even in "big,
> expensive" databases...

I guess I'm just fortunate...

--
Ron Johnson, Jr.
Jefferson LA  USA

Re: Notes on converting from MySQL 5.0.x to PostgreSQL

"Jim C. Nasby" <jnasby <at> pervasive.com> writes:
> Is there actually a lack of ideas for our regression tests, or a lack of
> people/motivation to work on them?

Certainly there are plenty of ideas in the archives ... but writing
regression tests is so *boring* .  This is definitely a weak spot
for a mostly-volunteer project --- it's hard to get anyone to do
that kind of work.

Something that would actually hold some intellectual interest is to
improve the testing infrastructure.  The current setup is pretty limited
as to its ability to deal with varying outputs, and even more limited
in its ability to test concurrent behavior.  Again, see the archives.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

stored procedure which return a select result

Re: stored procedure which return a select result

am  01.07.2006, um  7:47:09 +0200 mailte Alain Roger folgendes:
[ Problems with set returning functions ]

> Could you give me some piece of code for such use ?

http://www.varlena.com/GeneralBits/26.php

There are any examples.

HTH, Andreas
--

-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    === 

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Dynamic technical graphics generation inside plperl query?

Hi,

Is there a way to use a graphics drawing library (like GD for example) inside a plperl function, and "stream"
the result image (bytea column?) without storing anything to the disk or database? Has anyone done that before?

Thanks,

---------------
Philippe Lang
Attik System