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

On Mon, May 22, 2006 at 03:14:18PM -0400, Alvaro Herrera wrote:
> Martijn van Oosterhout wrote:
> > On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > > > 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.
> >
> > Is this on the TODO list? It doesn't seem too difficult to create a
> > tuplestore and store the NEW and OLD tuples there and pass the whole
> > set to the trigger.
>
> OTOH it would be nice to be able to save only the TIDs of the tuples,
> not the tuples themselves ... maybe create a "TIDstore"?

That could become a performance issue, though, since you'd need all the
modified pages to still be in cache for this to perform well. A
tuplestore doesn't have that constraint, and I think it was recently
realized that it also shouldn't be saving any MVCC info either (see the
compressed sort thread).

If there was more information than the tuplestore could keep in memory,
then a TIDstore might be faster, but only if it resulted in reading from
the heap sequentially, or very near it.
--
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