Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

On Mon, May 22, 2006 at 05:06:47PM +0200, Martijn van Oosterhout wrote:
> On Mon, May 22, 2006 at 10:00:22AM -0500, Jim C. Nasby wrote:
> > > T-SQL has statement-level triggers, and they get used a lot (some big apps
> > > ONLY put code in triggers). Statement-level triggers are very efficient for
> > > maintaining aggregates; the closest PG has are rewrite rules.
> >
> > Yeah, I wish PostgreSQL had them. I've got clients that could certainly
> > make use of them.
>
> What are you referring to that is not supported currently?
>
> CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> ON table FOR EACH STATEMENT
> EXECUTE PROCEDURE funcname ( arguments )

And that doesn't give you any information on the rows that were
modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
you can select from inside the trigger as if they were real tables.

> > > For high-end MSSQL shops, a high value is being able to trace and profile
> > > (EXPLAIN) every client SQL command from the server side ... with plenty of
> > > options for selective trace.
> >
> > This would also be highly valuable to have in PostgreSQL.
>
> Are we talking EXPLAIN (which is cheap) or EXPLAIN ANALYZE (which is
> less so)?

It's not so much about which case, it's about being able to control the
them from another connection. IE:

Show EXPLAIN for every query run on PID blah
Show EXPLAIN ANALYZE for every query run on PID blah where the command
string matches this regex
etc. Having the ability to do random sampling in there could be very
handy as well. As would firing on queries that hit certain tables
(though the regex functionality could handle that).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461