Partition: use triggers instead of rules

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 28 Nov 2007 12:26:15 -0800
David Fetter <david@fetter.org> wrote:

Folks,

Best practices for partitioning so far have shown that TRIGGERs are
better than RULEs for most cases. Please find attached a patch which
reflects this.

Thanks to Robert Treat for help putting this together :)

Cheers,
David.

+1

Joshua D. Drake

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHTdHoATb/zqfZUUQRAnpWAJ9xHqMnNorANuS9r8Hm90tn2bmZIwCglwlq
D7Q1C5vcbvQ/JiqVcv0wCho=
=NcWZ
-----END PGP SIGNATURE-----

On Wed, Nov 28, 2007 at 12:39:04PM -0800, Joshua D. Drake wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 28 Nov 2007 12:26:15 -0800
David Fetter <david@fetter.org> wrote:

Folks,

Best practices for partitioning so far have shown that TRIGGERs are
better than RULEs for most cases. Please find attached a patch which
reflects this.

Thanks to Robert Treat for help putting this together :)

Cheers,
David.

+1

Joshua D. Drake

Per Robert, I've also dropped the UNION partitioning suggestion as
it's pretty useless.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Wed, Nov 28, 2007 at 12:41:20PM -0800, David Fetter wrote:

On Wed, Nov 28, 2007 at 12:39:04PM -0800, Joshua D. Drake wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 28 Nov 2007 12:26:15 -0800
David Fetter <david@fetter.org> wrote:

Folks,

Best practices for partitioning so far have shown that TRIGGERs are
better than RULEs for most cases. Please find attached a patch which
reflects this.

Thanks to Robert Treat for help putting this together :)

Cheers,
David.

+1

Joshua D. Drake

Per Robert, I've also dropped the UNION partitioning suggestion as
it's pretty useless.

Oops. Patch including *both* changes attached this time.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david@fetter.org> writes:

Best practices for partitioning so far have shown that TRIGGERs are
better than RULEs for most cases. Please find attached a patch which
reflects this.

Entirely removing the example of how to do it with rules doesn't
seem like a good idea.

regards, tom lane

On Wed, Nov 28, 2007 at 03:53:04PM -0500, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

Best practices for partitioning so far have shown that TRIGGERs
are better than RULEs for most cases. Please find attached a
patch which reflects this.

Entirely removing the example of how to do it with rules doesn't
seem like a good idea.

It does to me. I haven't found a case yet where rules worked even as
well as triggers.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 28 Nov 2007 12:57:10 -0800
David Fetter <david@fetter.org> wrote:

On Wed, Nov 28, 2007 at 03:53:04PM -0500, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

Best practices for partitioning so far have shown that TRIGGERs
are better than RULEs for most cases. Please find attached a
patch which reflects this.

Entirely removing the example of how to do it with rules doesn't
seem like a good idea.

It does to me. I haven't found a case yet where rules worked even as
well as triggers.

I have yet to see a place that rules are appropriate at all with
partitioning. The only half reasonable argument to their existence with
it is that they are easier for someone with an extremely simple
partitioning configuration.

However rules are so deficient on anything except the absolute most
basic example of partitioning that I think we are doing our new
partitioning users a disservice by stating anything about rules except
in passing.

Sincerely,

Joshua D. Drake

Cheers,
David.

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHTdeuATb/zqfZUUQRAvf3AJ96e4YynZ/rYv6egG+w+85Ms+ihogCgjIlh
6xWR0nzceoJYCiQl+ffFTK8=
=XREP
-----END PGP SIGNATURE-----

David Fetter <david@fetter.org> writes:

On Wed, Nov 28, 2007 at 03:53:04PM -0500, Tom Lane wrote:

Entirely removing the example of how to do it with rules doesn't
seem like a good idea.

It does to me. I haven't found a case yet where rules worked even as
well as triggers.

I don't have a problem with emphasizing triggers as the preferred
solution, but we should keep the older example, if only because people
are going to see DB schemas that use that approach, and they won't
understand what's going on (or realize they could convert) if they've
not seen an example.

regards, tom lane

On Wed, Nov 28, 2007 at 05:17:38PM -0500, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

On Wed, Nov 28, 2007 at 03:53:04PM -0500, Tom Lane wrote:

Entirely removing the example of how to do it with rules doesn't
seem like a good idea.

It does to me. I haven't found a case yet where rules worked even
as well as triggers.

I don't have a problem with emphasizing triggers as the preferred
solution, but we should keep the older example, if only because
people are going to see DB schemas that use that approach, and they
won't understand what's going on (or realize they could convert) if
they've not seen an example.

Greg Sabino Mullane managed to contrive an example where RULEs might
conceivably be the least-bad way to do this, that being a machine
where no PLs may be installed.

Telling people how to do this is not *quite* as bad as describing how
to do EAV, but it's pretty close.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter wrote:

Greg Sabino Mullane managed to contrive an example where RULEs might
conceivably be the least-bad way to do this, that being a machine
where no PLs may be installed.

Perhaps this just means we should consider installing plpgsql by
default.

--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"There was no reply" (Kernel Traffic)

On Wed, Nov 28, 2007 at 10:06:01PM -0300, Alvaro Herrera wrote:

David Fetter wrote:

Greg Sabino Mullane managed to contrive an example where RULEs
might conceivably be the least-bad way to do this, that being a
machine where no PLs may be installed.

Perhaps this just means we should consider installing plpgsql by
default.

I'm all for that :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Nov 28, 2007 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Entirely removing the example of how to do it with rules doesn't
seem like a good idea.

Agreed.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/

On Wed, Nov 28, 2007 at 09:58:26PM -0500, Jonah H. Harris wrote:

On Nov 28, 2007 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Entirely removing the example of how to do it with rules doesn't
seem like a good idea.

Agreed.

Do you have an example of one use case where using RULEs rather than
TRIGGERs is a good idea?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david@fetter.org> writes:

On Wed, Nov 28, 2007 at 09:58:26PM -0500, Jonah H. Harris wrote:

On Nov 28, 2007 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Entirely removing the example of how to do it with rules doesn't
seem like a good idea.

Agreed.

Do you have an example of one use case where using RULEs rather than
TRIGGERs is a good idea?

The argument I made for keeping the example around is not dependent on
the assumption that using a rule is a good idea. It's dependent on the
established fact that we have recommended that in prior releases, and
therefore people are going to be seeing that construct in real
databases.

regards, tom lane

On Wed, 28 Nov 2007, Alvaro Herrera wrote:

David Fetter wrote:

Greg Sabino Mullane managed to contrive an example where RULEs might
conceivably be the least-bad way to do this, that being a machine
where no PLs may be installed.

Perhaps this just means we should consider installing plpgsql by
default.

I have run into this myself, and a patch that I contributed (which made it
in to 8.3) made it possible for a database owner to create trusted
languages from templates in the default configuration. Which means that
if an admin wants to prevent usage of the language, they can revoke the
right to create it, but db owners still "opt-in" to any languages they
want.

On Thu, Nov 29, 2007 at 12:55:53AM -0500, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

On Wed, Nov 28, 2007 at 09:58:26PM -0500, Jonah H. Harris wrote:

On Nov 28, 2007 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Entirely removing the example of how to do it with rules doesn't
seem like a good idea.

Agreed.

Do you have an example of one use case where using RULEs rather
than TRIGGERs is a good idea?

The argument I made for keeping the example around is not dependent
on the assumption that using a rule is a good idea. It's dependent
on the established fact that we have recommended that in prior
releases, and therefore people are going to be seeing that construct
in real databases.

We've corrected mistakes in the manual before :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 29 Nov 2007 00:55:53 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Fetter <david@fetter.org> writes:

On Wed, Nov 28, 2007 at 09:58:26PM -0500, Jonah H. Harris wrote:

On Nov 28, 2007 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Entirely removing the example of how to do it with rules doesn't
seem like a good idea.

Agreed.

Do you have an example of one use case where using RULEs rather than
TRIGGERs is a good idea?

The argument I made for keeping the example around is not dependent on
the assumption that using a rule is a good idea. It's dependent on
the established fact that we have recommended that in prior releases,
and therefore people are going to be seeing that construct in real
databases.

And they could refer back to the older version of the documentation for
it. In fact, we should mention that in the patch:

<note><para>If you have an partitioning setup that uses rules please
refer to the 8.2 documentation on partitioning</para></note>

Sincerely,

Joshua D. Drake

regards, tom lane

---------------------------(end of
broadcast)--------------------------- TIP 4: Have you searched our
list archives?

http://archives.postgresql.org

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHTl84ATb/zqfZUUQRAtscAJ9h77nDNJ3ZggWgocXDQaFE/S998gCcDtiV
wKH3BejsoL0fR5D3KHhKaEc=
=zpPv
-----END PGP SIGNATURE-----

The argument I made for keeping the example around is not dependent on
the assumption that using a rule is a good idea. It's dependent on
the established fact that we have recommended that in prior releases,
and therefore people are going to be seeing that construct in real
databases.

And they could refer back to the older version of the documentation for
it. In fact, we should mention that in the patch:

<note><para>If you have a partitioning setup that uses rules please
refer to the 8.2 documentation on partitioning</para></note>

+1

Regards,
Nikhils
--
EnterpriseDB http://www.enterprisedb.com

NikhilS wrote:

The argument I made for keeping the example around is not

dependent on

the assumption that using a rule is a good idea. It's dependent on
the established fact that we have recommended that in prior
releases, and therefore people are going to be seeing that
construct in real databases.

And they could refer back to the older version of the documentation
for it. In fact, we should mention that in the patch:

<note><para>If you have a partitioning setup that uses rules please
refer to the 8.2 documentation on partitioning</para></note>

+1

I would also add another sentence about *why* the recommendation was
changed. We have one rule-based setup here, and it has been working
flawlessly for us,... so personally I don't even know the reasons.

Best Regards
Michael Paesold

i would say have both, but just mention that for performance reasons its
better to use triggers.

On Nov 29, 2007 8:29 PM, Michael Paesold <mpaesold@gmx.at> wrote:

NikhilS wrote:

The argument I made for keeping the example around is not

dependent on

the assumption that using a rule is a good idea. It's dependent on
the established fact that we have recommended that in prior
releases, and therefore people are going to be seeing that
construct in real databases.

And they could refer back to the older version of the documentation
for it. In fact, we should mention that in the patch:

<note><para>If you have a partitioning setup that uses rules please
refer to the 8.2 documentation on partitioning</para></note>

+1

I would also add another sentence about *why* the recommendation was
changed. We have one rule-based setup here, and it has been working
flawlessly for us,... so personally I don't even know the reasons.

Best Regards
Michael Paesold

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Usama Munir Dar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar